Заполнение форм данными из электронной таблицы Excel

Обновлено: 21.11.2024

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

Подведем итоги. На данный момент наш робот умеет:

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

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

Чтение файла Excel

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

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

Добавим библиотеку RPA.Excel.Files в раздел ***Настройки***:

Давайте изменим шаг заполнения и отправки формы в разделе *** Задачи *** на Заполнить форму, используя данные из файла Excel, чтобы лучше описать, что будет делать робот:

Затем добавим наше новое ключевое слово, начиная с его названия:

Теперь, когда у нас есть библиотека RPA.Excel.Files, наш робот может открыть файл Excel, используя ключевое слово Open Workbook. Нам просто нужно передать ему имя файла:

Давайте еще раз посмотрим на файл Excel:

Первая строка содержит заголовки таблицы ( Имя , Фамилия , Продажи и Цели продаж ). Затем у нас есть строки для каждого из торговых представителей. Нашему роботу необходимо прочитать эти данные в формате, который он понимает. Мы можем сделать это с помощью ключевого слова Read Worksheet As Table. Мы поместим данные в переменную, которую назовем $. Думайте о переменной как о контейнере для вещей. Например, торговые представители! Кроме того, поскольку наш Excel содержит заголовки в первой строке, мы будем передавать параметр заголовка как True. После этого нам больше не нужно держать файл Excel открытым. Мы можем использовать ключевое слово Close Workbook, чтобы закрыть ее.

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

Наше ключевое слово выглядит следующим образом:

Вы еще с нами? Здорово! Как видно из реализации ключевого слова, робот только читает данные Excel. Он еще ничего не делает с данными. Мы делаем маленькие шаги!

Вот как наш робот выглядит на данный момент:

Запустите робота из панели управления.

Никаких видимых отличий от предыдущих, но журнал показывает, что теперь у робота есть данные, которые были в файле Excel:

Отлично! Идем дальше!

Заполнение формы для каждой строки в файле Excel

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

Помните ключевое слово «Заполнить и отправить форму»? Пару глав назад мы установили его для заполнения формы жестко заданными данными:

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

Мы ожидаем, что аргумент $ будет списком именованных элементов. Если, например, мы хотим получить доступ к свойству имени нашей переменной, мы должны написать: $[First Name] .

Мы можем попрощаться с нашим другом Джоном Смитом и заменить наши жестко заданные значения следующим образом:

Заполнение формы для каждой строки

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

Ключевое слово «Заполнить и отправить форму для одного человека» готово к приему аргументов. Мы можем перебирать строки таблицы (переменная $) и вызывать ее каждый раз, передавая ей отдельную строку:

Автодополнение может помочь вам в написании синтаксиса цикла for. Введите for и выберите FOR IN из списка предложений. Это создаст для вас заполнитель для синтаксиса цикла. Затем вы можете пойти и заменить значения, как показано во фрагменте. Нет необходимости тратить на это больше времени, но ознакомьтесь со статьей о цикле for, чтобы узнать больше о циклах, когда вам захочется.

Отлично! Наш робот выглядит так:

Полюбуйтесь роботом, выполняющим тяжелую работу!

Вау! Это было много! Но теперь наш робот должен быть готов к работе:

Теперь наш робот знает, как открыть файл Excel, получить данные, просмотреть их и заполнить форму за нас! Давайте запустим его и посмотрим, как он творит чудеса.

🕺 Мария свободна от копипаст-ада! Она будет так рада этому! 👍🏻

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

Деловой мир полон форм и документов, созданных из электронных таблиц Excel. Несмотря на то, что программа не предназначена для этой задачи, преимущество возможности расчета сумм, итогов и т. д. в форме делает ее стандартом де-факто для счетов-фактур, квитанций, отгрузочных манифестов и заказов на покупку повсюду. Часто вы будете использовать одни и те же формы снова и снова, поэтому имеет смысл максимально автоматизировать процесс их заполнения. Я покажу вам, как автоматически заполнять адреса и контактную информацию в шаблоне декларации об отгрузке…

Изучение формы и данных

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

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

Очистка (очистка) адресных данных

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

Создание раскрывающегося списка

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

Выберите ячейку ввода данных для компании рядом с пунктом «Отправить»:

На вкладке меню "Данные" выберите "Проверка данных", а затем нажмите "Проверка данных".

В появившемся диалоговом окне "Проверка данных" в разделе "Разрешить" выберите "Список". Затем щелкните поле данных рядом с источником.

Перейдите на вкладку "Адреса доставки" и выберите столбец A. Затем нажмите "ОК".

Если вы вернетесь к листу манифеста, вы можете посмотреть на поле данных «Отправить:» и найти раскрывающийся список. Он покажет вам доступные варианты.

Автоматическое заполнение полей данных

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

Чтобы найти правильную адресную информацию, мы воспользуемся функцией Excel под названием ВПР(). Синтаксис функции ВПР следующий:

lookup_value — это то, что мы ищем в таблице. В данном случае это название компании, которое находится в ячейке F4.

table_array — это набор строк и столбцов, которые функция ВПР должна сканировать на предмет соответствия. В первом столбце должно быть lookup_value. В нашем случае значением являются столбцы вкладки Адрес отправителя.

col_index_num — это столбец table_array, который мы хотим видеть в ячейке. В данном случае это столбец адреса, который является вторым столбцом (столбец B) нашего table_array.

Наконец, range_lookup сообщает функции ВПР, согласны ли мы с близкими совпадениями, а не с точными. Нам нужно точное совпадение, поэтому значение будет FALSE.

Если функция ВПР не находит соответствия в таблице, она выдает ошибку, что не очень хорошо, если нам нужно ввести адрес, которого нет в списке адресов отправителя. Чтобы обработать ошибку и сохранить форму в чистоте, мы поместим функцию ВПР в другую функцию с именем ЕСЛИОШИБКА(). Синтаксис IFERROR следующий:

значение будет функцией ВПР.

value_if_error будет просто пустой строкой (""), поэтому форма останется пустой, если компания не выбрана.

Окончательная формула для поля адреса выглядит следующим образом:

Процесс повторится для имени контакта и номера телефона. Можно использовать ту же формулу, но col_index_num соответственно изменится.

Формула для имени контактного лица находится в ячейке F9 и выглядит следующим образом:

Формула для номера телефона находится в ячейке F9 и выглядит следующим образом:

Автоматическое заполнение города, штата и почтового индекса

Данные о городе, штате и почтовом индексе хранятся в отдельных ячейках, поэтому нам нужно сшить их вместе, используя несколько функций ВПР. В остальном процесс тот же. Чтобы сшить их вместе, мы можем использовать функцию CONCATENATE(), которая превращает несколько строк в одну длинную строку. Синтаксис оператора СЦЕПИТЬ следующий:

Например, =СЦЕПИТЬ("чтобы","получить","ее") вычисляется как "вместе".

Формула для города выглядит следующим образом:

Формула для состояния выглядит следующим образом:

Формула для почтового индекса выглядит следующим образом:

Нам нужно соединить их вместе с помощью СЦЕПИТЬ и добавить нужные запятые и пробелы. Окончательная формула для поля данных ZIP «Город, штат» находится в ячейке F6 и выглядит следующим образом (в одну строку):

Пример автоматически заполняемого манифеста

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

Получите последние советы и рекомендации по Excel, подписавшись на информационный бюллетень!

Эндрю Робертс уже более десяти лет решает бизнес-задачи с помощью Microsoft Excel. Excel Tactics призван помочь вам в этом.

Подпишитесь на информационный бюллетень, чтобы быть в курсе последних статей. Зарегистрируйтесь, и вы получите бесплатное руководство с 10 быстрыми клавишами для экономии времени!

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

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

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

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

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

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

Таблицы Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Формы Microsoft

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выводы

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

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

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

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

Об авторе

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

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

У нас есть большая электронная таблица с данными о клиентах, которые мы хотели бы автоматически импортировать в заполняемую форму PDF. Можно ли это сделать с помощью VBA для заполнения каждой отдельной формы каждой строкой данных в Excel?

2 ответа

Ник Баллард

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

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

Похоже, это решение больше подходит вам:-

Надеюсь, один из них подойдет вам.

В форме PDF каждое поле имеет имя. Если автор специально не создал их с определенным именем, Adobe назначает их для форм, созданных в Adobe Acrobat. Если форма представляет собой просто отсканированное изображение, возможно, вам придется отредактировать и наложить поля на изображение.

Войдите или зарегистрируйтесь, чтобы ответить в этой теме.

Не нашли то, что искали? Поищите похожие вопросы на форумах или посетите форум ИТ-программистов.

Эргономичное оборудование

Кто в США должен нести ответственность за предоставление эргономичного оборудования по запросу сотрудника? Это ИТ, поскольку ИТ предоставляет клавиатуры и мыши? Должен ли это быть HR, поскольку он эргономичен и несет потенциальную ответственность, если НЕ предоставляется? Должен ли это быть тот отдел.

Приветствие Xfinity (личный домашний Интернет)

Во-первых, мне больно. Я мог бы произнести речь «Он ставит передо мной задачу», как Хан в «Звездном пути 2: Гнев Хана». Просто замените «Они» на «Он».Но они сделали то, чего я желал годами (десятилетиями?), так что, думаю, должно быть признание. Ю.

Щелкни! SATCOM Threat, IE End of Life, Mac с кирпичами, Planet 9, Lego Delorean

Ваша ежедневная доза технических новостей. Вы должны это услышать. ФБР и CISA предупреждают об угрозах для сетей спутниковой связи Согласно новому предупреждению ФБР и CISA спутниковые сети находятся в зоне высокого риска. Согласно ZDNet.

Какими сверхспособностями вы хотели бы обладать?

Что может сделать ИТ-специалист со сверхспособностями? В каких ИТ-задачах вы бы их использовали и как?

Можно ли подключить интерфейс управления коммутатора к одному из его собственных портов коммутатора?

Недавно я понял, что у меня есть конфигурация коммутатора с непреднамеренным потенциальным побочным эффектом. У меня есть Aruba 6300F с несколькими виртуальными локальными сетями. Он работает в режиме уровня 3. Это работает следующим образом: я просто «включаю» функции маршрутизатора, а затем.

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