Автоматизация ввода данных в Excel

Обновлено: 20.11.2024

В основном это руководство по вводу данных, которое можно использовать и в других сценариях. Но поскольку данные о продажах и ценах легко понять, я использую то же самое. Дайте мне знать в комментариях ниже, как вы используете методы, обсуждаемые в следующем уроке. Итак, начнем!

Уровень владения Excel: от начального до среднего

Для этого руководства вам необходимо скачать этот файл Excel

Прибыль обеспечивается доходом, а доход — продаваемой организацией продукцией. Бухгалтеры часто ежедневно должны составлять отчет о продажах. Двумя важными составляющими каждого отчета о продажах являются само название продукта и цена за единицу. Для таких отчетов бухгалтерам часто приходится вводить названия продуктов и цены за единицу, чтобы заполнить ячейки Excel для выполнения запроса или других функций.

Наша жизнь станет очень легкой, если нам придется выбирать продукты из выпадающего списка, а не печатать, а цена за единицу будет отображаться автоматически при выборе продукта. Это можно сделать в Excel разными способами, но проще всего использовать проверку данных и соответствующую функцию ПРОСМОТР.

Что нам нужно:

  • легко выбрать название продукта из списка
  • Excel автоматически предоставляет цену за единицу выбранного продукта

Получив это, мы легко продолжим наши расчеты.

Раскрывающийся список — ВХОД!

Следующие шаги помогут вам создать раскрывающийся список продуктов.

Подготовка данных

Шаг 1. Убедитесь, что все товары находятся в одном столбце. Выберите все продукты, кроме заголовка, если он есть. В нашем случае данные находятся в ячейках от A14 до A20. Если продуктов в большом количестве, вы можете быстро выбрать их, нажав комбинацию клавиш CTRL+SHIFT+Стрелка вниз

Шаг 2. После выбора всех продуктов нажмите Ctrl+F3. Откроется диспетчер имен. Вы также можете получить доступ к диспетчеру имен с ленты Excel, щелкнув вкладку «Формула» и кнопку «Диспетчер имен» в группе определенных имен.

Шаг 3. Нажмите кнопку «Создать» в верхней части диалогового окна диспетчера имен. Появляется новый диалог. Введите продукты в поле имени и нажмите кнопку ОК.

Шаг 4. Теперь выберите названия продуктов с их ценами и перейдите в поле имени слева от строки формул, введите: productdata и нажмите Enter. Убедитесь, что в имени нет пробела. Это второй способ именования. Вы можете подтвердить, именован ли диапазон или нет, обратившись к диспетчеру имен. А также из поля имени, нажав стрелку раскрывающегося списка. Милая. Не правда ли!

Появление раскрывающегося списка

Шаг 1. Вы можете встроить раскрывающийся список в одну ячейку, несколько ячеек или целый столбец, как вам удобно. Чтобы встроить в одну ячейку, выберите только эту ячейку. Если вы хотите, чтобы раскрывающийся список отображался в нескольких ячейках, выберите все такие ячейки. Если вы хотите, чтобы раскрывающийся список отображался во всем столбце до центра земли, выберите весь столбец и перейдите к следующему шагу.

Шаг 2: В нашем случае мы выбрали ячейки от A5 до A9. После выбора области щелкните вкладку «Данные» и кнопку «Проверка данных» в группе «Инструменты данных». Для доступа к этому инструменту можно использовать клавишу Alt. Полная комбинация: Alt+A+V+V. Можно использовать сочетание клавиш версии 2003 или более ранней, это Alt+D+L

Шаг 3. Появится всплывающее окно проверки данных. В раскрывающемся списке выберите «Список»

Шаг 4. Убедитесь, что установлены флажки «игнорировать пробелы» и «раскрывающийся список внутри ячейки». Нажмите на поле Источник и:

любой тип: =продукты. Да, это то же имя, которое вы дали для выбора продукта на шаге 3 выше.

или нажмите кнопку F3 на клавиатуре. Появится диалоговое окно «Вставить имя», щелкните название продукта и нажмите «ОК».

Шаг 5. Теперь, если щелкнуть любую ячейку в столбце, появится стрелка раскрывающегося списка, и вы сможете легко выбрать нужный продукт.

Получение цен на товары

Шаг 6. Выберите ячейки с B5 по B9 и вставьте в строку формул следующую функцию:

Где productdata — это диапазон, в котором вы хотите найти значение в ячейке, и в основном это имя диапазона A14: B20.

После того, как вся формула будет введена, вместо нажатия Enter нажмите Ctrl+Enter, и формула будет помещена во все выбранные ячейки. Работа выполнена! 🙂

Дополнительный совет:

Эта формула просто проверяет, не вызывает ли функция ошибку по какой-либо причине, как это происходит в нашем случае, а затем отображает текст «Выберите продукт», иначе будет показан результат.

  1. используйте метод проверки данных, чтобы создать раскрывающийся список, а затем;
  2. соединил его с функцией ПРОСМОТР, чтобы автоматически получать цены из диапазона данных
  3. определить имена двумя способами
  4. использовать именованные диапазоны в формуле
  5. один быстрый способ ввести одну и ту же формулу в несколько ячеек с помощью Ctrl+Enter
  6. в разделе "Дополнительный совет" мы говорим о функции ЕСЛИОШИБКА, помогающей избежать сообщений об ошибках.

Одним из способов экономии времени и денег для бизнеса является использование электронных таблиц Excel в качестве таблиц данных, в которых можно хранить информацию о клиентах, сведения о запасах или другие важные данные. В Excel есть несколько функций, которые могут помочь автоматизировать ввод данных. Формы данных позволяют пользователям вводить необходимую информацию, не переходя на табуляцию и не прокручивая строки и столбцы. Списки предоставляют несколько вариантов выбора, проверка данных может управлять вводом, и вы также можете изменить движение курсора.

Формы данных

Формы данных — это маски, которые отображают только те поля, в которые вы хотите ввести данные. Человек, вводящий данные, просто вводит текст в текстовое поле, связанное с каждым столбцом, перемещаясь по полям с помощью клавиши Tab. Чтобы создать форму данных, необходимо добавить кнопку «Форма» на панель быстрого доступа. Щелкните любую ячейку в таблице. Щелкните правой кнопкой мыши панель быстрого доступа и выберите «Настроить панель быстрого доступа». Выберите «Все команды», выберите «Форма» и нажмите кнопку «Добавить». Нажмите кнопку "Форма" на панели инструментов, чтобы создать новую форму.

Пользовательские списки

Добавление настраиваемого раскрывающегося списка помогает автоматизировать процесс, предоставляя несколько вариантов, из которых пользователи могут легко выбирать данные. Чтобы создать пользовательский список, щелкните ячейку, в которой вы хотите, чтобы список отображался. Нажмите «Проверка данных» на вкладке «Данные» и нажмите «Проверка данных». Выберите «Список» в поле «Разрешить». Введите элементы списка в поле «Источник» с запятой между каждым элементом. Нажмите «ОК», чтобы добавить список. Используйте дескриптор заполнения, если хотите скопировать список вниз по столбцу.

Проверка данных

Ограничение определенных данных, вводимых в поле, помогает автоматизировать процесс, отображая полезное сообщение о вводе, а также специальное сообщение об ошибке, если введен неправильный тип или значение. Вы можете использовать настраиваемый список, как упоминалось ранее, или выбрать другой набор критериев. В диалоговом окне «Проверка данных» выберите критерии, которые вы хотите использовать, в полях на вкладке «Настройки». Перейдите на вкладку «Входное сообщение» и введите сообщение, которое вы хотите, чтобы пользователи видели, когда они щелкают в выбранной ячейке. На вкладке Оповещение об ошибке введите уникальное сообщение об ошибке.

Выбор ячейки

Настройка направления, в котором должен двигаться курсор после нажатия клавиши "Ввод", помогает автоматизировать ввод данных, уменьшая необходимость переходить от клавиатуры к мыши. Чтобы изменить этот параметр, щелкните вкладку «Файл» и выберите «Параметры». Перейдите в «Дополнительно» на левой панели окна «Параметры». В группе «Параметры редактирования» выберите направление, в котором вы хотите переместить выделение ячейки, в списке «После нажатия Enter, переместить выделение» и нажмите «ОК».

Ввод данных иногда может быть важной частью использования Excel.

С почти бесконечными ячейками человеку, вводящему данные, может быть трудно понять, куда поместить какие данные.

Форма ввода данных может решить эту проблему и помочь пользователю ввести правильные данные в нужном месте.

В Excel уже давно есть пользовательские формы VBA, но они сложны в настройке и не очень гибки для изменения.

В этой записи блога мы рассмотрим 5 простых способов создания формы ввода данных для Excel.

Видеоруководство

Таблицы Excel

У нас есть таблицы Excel, начиная с Excel 2007.

Они идеально подходят для хранения данных и могут использоваться как простая форма для ввода данных.

Создать таблицу очень просто.

  1. Выберите диапазон данных, включая заголовки столбцов.
  2. Перейдите на вкладку "Вставка" на ленте.
  3. Нажмите кнопку "Таблица" в разделе "Таблицы".

Мы также можем использовать сочетание клавиш для создания таблицы. Сочетание клавиш Ctrl + T сделает то же самое.

Убедитесь, что в диалоговом окне "Создать таблицу" установлен флажок "Моя таблица имеет заголовки", и нажмите кнопку "ОК".

Теперь у нас есть данные в таблице Excel, и мы можем использовать ее для ввода новых данных.

Чтобы добавить новые данные в нашу таблицу, мы можем начать вводить новую запись в ячейки непосредственно под таблицей, и таблица впитает новые данные.

Мы можем использовать клавишу Tab вместо Enter при вводе данных. Это заставит курсор активной ячейки двигаться вправо, а не вниз, чтобы мы могли добавить следующее значение в нашу запись.

Когда курсор активной ячейки находится в последней ячейке таблицы (нижняя правая ячейка), нажатие клавиши Tab создаст новую пустую строку в таблице, готовую для следующего ввода.

Это идеальная и простая форма для ввода данных.

Форма ввода данных

Excel на самом деле имеет скрытую форму ввода данных, и мы можем получить к ней доступ, добавив команду на панель быстрого доступа.

Добавьте команду формы на панель быстрого доступа.

  1. Щелкните правой кнопкой мыши в любом месте панели быстрого доступа.
  2. Выберите «Настроить панель быстрого доступа» в параметрах меню.

Откроется меню параметров Excel на вкладке панели быстрого доступа.

  1. Выберите команды не на ленте.
  2. Выберите Форма из списка доступных команд. Нажмите F, чтобы перейти к командам, начинающимся с F.
  3. Нажмите кнопку "Добавить", чтобы добавить команду на панель быстрого доступа.
  4. Нажмите кнопку ОК.

Затем мы можем открыть форму ввода данных для любого набора данных.

  1. Выберите ячейку внутри данных, с которыми мы хотим создать форму ввода данных.
  2. Нажмите значок формы в области панели быстрого доступа.

Откроется настраиваемая форма ввода данных на основе полей в наших данных.

Формы Microsoft

Если нам нужна простая форма для ввода данных, почему бы не использовать Microsoft Forms?

Для этого варианта формы потребуется, чтобы наша книга Excel была сохранена в SharePoint или OneDrive.

Форма будет в браузере, а не в Excel, но мы можем связать форму с книгой Excel, чтобы все данные попадали в нашу таблицу Excel.

Это отличный вариант, если несколько человек или людей за пределами нашей организации должны вводить данные в книгу Excel.

Нам нужно создать форму для Excel в SharePoint или OneDrive. Этот процесс одинаков как для SharePoint, так и для OneDrive.

  1. Перейдите в библиотеку документов SharePoint или папку OneDrive, где будет сохранена книга Excel.
  2. Нажмите «Создать», а затем выберите «Формы для Excel».

Это предложит нам назвать книгу Excel и открыть новую вкладку браузера, где мы можем создать нашу форму, добавляя различные типы вопросов.

Сначала нам нужно создать форму, и это создаст таблицу в нашей книге Excel, в которую будут внесены данные.

Затем мы можем поделиться формой с любым, кому мы хотим ввести данные в Excel.

Когда пользователь вводит данные в форму и нажимает кнопку отправки, эти данные автоматически отображаются в нашей книге Excel.

Мощные приложения

Power Apps – это гибкая платформа для создания приложений на основе формул перетаскивания от Microsoft.

Конечно, мы можем использовать его для создания записи данных для наших данных Excel.

Фактически, если у нас настроена таблица данных, Power Apps создаст для нас приложение на основе наших данных. Это не может быть проще.

Затем нам будет предложено войти в нашу учетную запись SharePoint или OneDrive, где сохранен наш файл Excel, чтобы выбрать книгу Excel и таблицу с нашими данными.

Это создаст полнофункциональное приложение для ввода данных с тремя экранами.

  1. Мы можем искать и просматривать все записи в нашей таблице Excel в прокручиваемой галерее.
  2. Мы можем просматривать отдельные записи в наших данных.
  3. Мы можем редактировать существующую запись или добавлять новые записи.

Все это связано с нашей таблицей Excel, поэтому любые изменения или дополнения из приложения будут отображаться в Excel.

Мощная автоматизация

Power Automate – это облачный инструмент для автоматизации задач между приложениями.

Но мы можем использовать триггер кнопки, чтобы автоматизировать ввод данных пользователем и добавлять данные в таблицу Excel.

Нам потребуется сохранить книгу Excel в OneDrive или SharePoint, а также подготовить таблицу с полями, которые мы хотим заполнить.

Чтобы создать форму ввода данных Power Automate.

Это откроет конструктор Power Automate, и мы сможем создать нашу автоматизацию.

  1. Нажмите на блок Запустить поток вручную, чтобы развернуть параметры триггера. Здесь мы найдем возможность добавлять поля ввода.
  2. Нажмите кнопку "Добавить ввод". Это даст нам возможность добавить несколько различных типов полей ввода, включая текст, да/нет, файлы, адрес электронной почты, число и даты.
  3. Переименуйте поле в описательное.Это поможет пользователю узнать, какой тип данных вводить при запуске этой автоматизации.
  4. Нажмите на три многоточия справа от каждого поля, чтобы изменить параметры ввода. Мы сможем добавить раскрывающийся список параметров, добавить список параметров с множественным выбором, сделать поле необязательным или удалить поле из этого меню.
  5. После того как мы добавили все поля ввода, мы можем добавить новый шаг к автоматизации.

Найдите коннектор Excel и добавьте действие Добавить строку в таблицу. Если вы используете бизнес-аккаунт Office 365, используйте соединители Excel Online (Business), в противном случае используйте соединители Excel Online (OneDrive).

Теперь мы можем настроить наш Excel. Шаг добавления строки в таблицу.

  1. Перейдите к файлу и таблице Excel, куда мы собираемся добавлять данные.
  2. После выбора таблицы поля в этой таблице отобразятся в списке, и мы можем добавить соответствующее динамическое содержимое на шаге Запуск триггера потока вручную.

Теперь мы можем запустить наш поток из службы Power Automate.

  1. Перейдите к Моим потокам на левой панели навигации.
  2. Перейдите на вкладку "Мои потоки".
  3. Найдите поток в списке доступных потоков и нажмите кнопку "Выполнить".
  4. Появится боковая панель с введенными данными, и мы сможем ввести наши данные.
  5. Нажмите "Запустить поток".

Мы также можем запустить это с нашего мобильного устройства с помощью приложений Power Automate.

  1. Перейдите в раздел "Кнопки" в приложении.
  2. Нажмите на поток, чтобы запустить его.
  3. Введите данные в форму.
  4. Нажмите кнопку "ГОТОВО" в правом верхнем углу.

Каким бы способом мы ни запускали поток, через несколько секунд данные появятся в нашей таблице Excel.

Выводы

Независимо от того, нужна ли нам простая форма или что-то более сложное и настраиваемое, у нас есть решение для ввода данных.

Мы можем быстро создать что-то внутри нашей книги или использовать внешнее решение, которое подключается к Excel и загружает данные в него.

Мы даже можем создавать формы, которые люди за пределами нашей организации могут использовать для заполнения наших электронных таблиц.

Сообщите мне в комментариях, какая форма ввода данных вам больше всего нравится.

Об авторе

Джон МакДугалл

Джон — Microsoft MVP, внештатный консультант и тренер, специализирующийся на Excel, Power BI, Power Automate, Power Apps и SharePoint. Другие интересные статьи Джона можно найти в его блоге или на канале YouTube.

Следуйте этому руководству, чтобы создать пользовательскую форму автоматического ввода данных с помощью VBA.

Visual Basic — отличный язык для автоматизации повторяющихся задач в Excel. Представьте, что вы поднимаете свою автоматизацию на новый уровень, создавая многофункциональные пользовательские формы, которые также выглядят аккуратно для конечных пользователей.

Пользовательские формы в VBA представляют собой чистый холст; вы можете создавать и упорядочивать формы в соответствии со своими потребностями в любой момент времени.

В этом руководстве вы научитесь создавать форму ввода данных для учащихся, которая собирает соответствующую информацию на связанных листах Excel.

Создание пользовательской формы с помощью Excel VBA

Откройте новую книгу Excel и выполните несколько предварительных шагов, прежде чем приступить к созданию формы ввода данных.

Сохраните книгу под нужным именем; не забудьте изменить тип файла на книгу Excel с поддержкой макросов.

Добавьте в эту книгу два листа со следующими именами:

Вы можете изменить эти имена в соответствии с вашими требованиями.

На главном листе добавьте кнопку для управления макросом пользовательской формы. Перейдите на вкладку «Разработчик» и нажмите кнопку «Кнопка» в раскрывающемся списке «Вставка». Поместите кнопку в любое место на листе.

После размещения кнопки переименуйте ее. Щелкните его правой кнопкой мыши и выберите «Создать», чтобы назначить новый макрос для отображения формы.

Введите следующий код в окно редактора:

После того, как листы баз данных для дома и учащихся готовы, пришло время разработать форму пользователя. Перейдите на вкладку «Разработчик» и щелкните Visual Basic, чтобы открыть редактор. Кроме того, вы можете нажать ALT+F11, чтобы открыть окно редактора.

Перейдите на вкладку "Вставка" и выберите "Пользовательская форма".

Пустая пользовательская форма готова к использованию; вместе с формой открывается соответствующий набор инструментов, в котором есть все необходимые инструменты для разработки макета.

На панели инструментов выберите параметр «Рамка». Перетащите его в форму пользователя и измените размер.

В опции (имя) вы можете изменить имя фрейма. Чтобы отобразить имя в интерфейсе, вы можете изменить имя в столбце Заголовок.

Далее выберите параметр «Метка» на панели инструментов и вставьте две метки в этот фрейм. Переименуйте первое в Номер заявки, а второе — в ID учащегося.

Применяется та же логика переименования; измените имена с помощью параметра «Заголовок» в окне «Свойства». Убедитесь, что вы выбрали соответствующий ярлык перед изменением его имени.

Далее вставьте два текстовых поля рядом с полями меток. Они будут использоваться для захвата ввода пользователя. Измените имена двух текстовых полей в столбце (Имя) в окне «Свойства». Имена следующие:

Проектирование рамки сведений об учащемся

Вставьте вертикальную рамку и добавьте 10 меток и 10 текстовых полей. Переименуйте каждый из них следующим образом:

Вставьте соответствующие текстовые поля рядом с этими метками; вставьте два (или более) поля optionbutton из панели инструментов пользовательской формы рядом с меткой пола. Переименуйте их в Male и Female (вместе с Custom) соответственно.

Проектирование рамки сведений о курсе

Добавьте еще одну вертикальную рамку и вставьте шесть меток и шесть текстовых полей, соответствующих каждой метке. Переименуйте ярлыки следующим образом:

Проектирование фрейма сведений о платеже

Вставить новый кадр; добавьте новую метку и переименуйте ее «Хотите обновить детали платежа?» Вставьте две опциональные кнопки; переименуйте их в Да и Нет.

Аналогичным образом добавьте новый фрейм, содержащий две дополнительные метки и два поля со списком. Переименуйте ярлыки следующим образом:

Проектирование панели навигации

В финальном кадре добавьте три кнопки из панели инструментов, которые будут содержать код для выполнения форм.

Переименуйте кнопки следующим образом:

Написание кода автоматической формы: кнопка «Сохранить детали»

Дважды щелкните кнопку Сохранить сведения. В следующем модуле вставьте следующий код:

Если вы не уверены, что означает часть кода или какой-либо его элемент, не беспокойтесь. Мы подробно объясним это в следующем разделе.

Объяснение кода автоматизированной формы

Текстовые поля будут содержать как текстовые, так и числовые значения, поэтому очень важно ограничить ввод данных пользователем. Номер заявки, идентификатор учащегося, возраст, номер телефона, идентификатор курса и продолжительность курса должны содержать только цифры, а все остальное будет содержать текст.

Используя оператор IF, код вызывает всплывающие окна с ошибками, если пользователь вводит символ или текстовое значение в любом из числовых полей.

Поскольку проверка ошибок выполняется, вам необходимо связать текстовые поля с ячейками листа.

Переменные lastrow вычисляют последнюю заполненную строку и сохраняют в них значения для динамического использования.

Наконец, значения из текстовых полей вставляются в связанный лист Excel.

Очистить форму и коды кнопок выхода

В кнопке очистки вам нужно написать код, чтобы очистить существующие значения из пользовательской формы. Это можно сделать следующим образом:

В кнопке выхода введите следующий код, чтобы закрыть форму пользователя.

В качестве последнего шага вам нужно ввести несколько завершающих фрагментов кода, чтобы создать раскрывающиеся значения для полей со списком (внутри платежных фреймов).

Автоматизация VBA упрощает работу

VBA — это многогранный язык, который служит многим целям. Пользовательские формы — это только один из аспектов VBA. Существует множество других применений, таких как объединение книг и листов, объединение нескольких листов Excel и другие удобные способы автоматизации.

Какой бы ни была цель автоматизации, VBA справится с этой задачей. Если вы продолжаете учиться и практиковаться, нет такого аспекта вашего рабочего процесса, который вы не могли бы улучшить.

Читайте также: