Как автоматически заполнить ячейки с другого листа в Excel
Обновлено: 07.07.2024
Иногда, когда мы работаем с большим файлом Excel, который имеет несколько листов, нам нужно автоматически заполнять данные с одного листа на другой лист автоматически. Недавно я работал над отчетом об инвентаризации списка SharePoint Online, там я видел эта потребность. Сначала я начал работать над исправлением этих сопоставлений или автозаполнением вручную, затем я подумал о том, чтобы сделать это как автоматизацию, используя функцию VLOOKUP Excel. В этой статье показано, как автоматически заполнять значения в Excel с другого листа с помощью функции Excel ВПР.
Основные моменты:
- Что такое функция vlookup в Excel?
- Как использовать функцию «ВПР» в Excel?
- Использование функции ВПР в режиме реального времени в отчете SharePoint
Что такое функция vlookup в Excel?
Функция ВПР — одна из самых популярных функций в Excel. ВПР расшифровывается как «Вертикальный просмотр». Это функция, которая заставляет Excel искать определенное значение в столбце (так называемый «табличный массив»), чтобы вернуть значение из другого столбца в той же строке с того же или другого листа.
В своей простейшей форме функция ВПР говорит:
Давайте начнем с этой реализации.
Для этого POC (доказательство концепции) я использовал образец Excel с таблицей сотрудников Excel, где у меня есть листы «Сведения о сотруднике» и «Зарплата сотрудника».
Вкладка «Сведения о сотруднике» содержит два следующих столбца:
Автоматическое получение значения с одного листа Excel на другой – Таблица сведений о сотрудникахКстати, структура листа «Зарплата сотрудника» выглядит следующим образом:
- Идентификатор почтового ящика
- Получить имя сотрудника из листа 1 (сведения о сотруднике)
- Зарплата
- Отдел
В этой демонстрации мы будем автоматически заполнять значения столбца «Имя сотрудника» на листе «Зарплата сотрудника», где значения столбца «Имя сотрудника» намеренно оставлены пустыми.
Теперь, используя функцию Excel «ВПР», мы заполним значения имени сотрудника из листа «Сведения о сотруднике». Ниже приведена формула для этого.
Автоматическое извлечение значения из одного листа Excel в другой с помощью функции vlookup
Как использовать функцию «ВПР» в Excel?
Поместите курсор туда, где вы хотите отобразить динамические данные, здесь я выбрал столбец «B» (получить имя сотрудника из листа 1 (сведения о сотруднике)), затем следуйте приведенному ниже синтаксису:
Объяснение функции ВПР с примером:
Использование функции ВПР в режиме реального времени в отчете SharePoint
Если вы работаете с сайтом SharePoint, списком/библиотекой, рабочим процессом и т. д., отчет Excel и хотите, чтобы все эти отчеты были в одном файле Excel с различными таблицами, мы можем использовать функцию ВПР.
Вывод:
Таким образом, в этой статье мы узнали о том, как использовать функцию ВПР в Excel и с помощью функции ВПР, как мы можем автоматически получать данные с другого листа, мы можем резюмировать следующим образом:
- Что такое функция vlookup в Excel?
- Как использовать функцию «ВПР» в Excel?
- Как извлечь данные из электронной таблицы с помощью функции ВПР?
- Использование функции ВПР в режиме реального времени в отчете SharePoint
См. также
Ищете руководство по PowerApps с нуля? Тогда вы попали по адресу. Ниже приведено несколько статей о PowerApps, которые могут вам понравиться:
При работе с несколькими листами в Excel очень часто возникает необходимость автоматического заполнения данных из другого листа. Есть несколько легких и простых способов достичь этой цели.В этой статье вы узнаете, как автоматически заполнять данные из другого листа с помощью правильных шагов и иллюстраций.
Загрузить практическую рабочую тетрадь
Вы можете скачать книгу Excel, которую мы использовали для подготовки этой статьи.
3 подходящих подхода к автоматическому заполнению данных из другого рабочего листа в Excel
1. Автоматическое заполнение путем связывания рабочих листов в Excel
На следующем рисунке Sheet1 представляет некоторые характеристики ряда моделей смартфонов.
А вот Лист2, из которого извлечены только три столбца из первого листа. Столбец «Цена» еще не скопирован, так как здесь мы покажем различные способы извлечения прайс-листа из первого листа. Мы должны поддерживать некоторые правила, которые будут автоматически обновлять столбец цен, если в соответствующий столбец на первом листе (Лист1) будут внесены какие-либо изменения.
Теперь давайте посмотрим, как мы можем связать эти два листа, чтобы данные на одном листе (Лист2) автоматически заполнялись на основе другого листа (Лист1).
📌 Шаг 1:
➤ На Листе 1 выберите диапазон ячеек (F5:F14), содержащих цены на смартфоны.
➤ Нажмите CTRL+C, чтобы скопировать выделенный диапазон ячеек.
📌 Шаг 2:
➤ Перейти к листу 2 сейчас.
➤ Выберите первую ячейку вывода в столбце "Цена".
➤ Щелкните правой кнопкой мыши и выберите параметр "Вставить ссылку", отмеченный красным квадратом на следующем снимке экрана.
Столбец «Цена» теперь заполнен извлеченными данными из первого листа (Лист1). Теперь мы увидим, как изменение данных на основном листе (Лист1) автоматически заполняет данные на втором листе (Лист2).
📌 Шаг 3:
➤ На листе 1 измените значение цены любой модели смартфона.
➤ Нажмите Enter и перейдите на Лист2.
И вы найдете обновленную цену соответствующего смартфона на Листе 2. Вот как мы можем легко связать два или несколько рабочих листов для автоматического заполнения.
2. Автоматическое обновление данных с использованием знака равенства для ссылки на ячейку (ячейки) из другого рабочего листа
Теперь мы применим другой метод, при котором нам не нужно ничего копировать и вставлять с одного рабочего листа на другой. Вместо этого мы будем использовать ссылки на ячейки из другого листа для автоматического заполнения данных.
📌 Шаг 1:
➤ На Листе2 выберите ячейку D5 и поставьте знак равенства (=).
📌 Шаг 2:
➤ Перейти к листу 1.
➤ Выберите диапазон ячеек (F5:F13), содержащий цены на все модели смартфонов.
➤ Нажмите Enter.
Теперь на Листе 2 вы найдете массив цен в столбце D в диапазоне от D5 до D14. Если вы измените какие-либо данные в столбце «Цена» на Листе 1, вы сразу же увидите обновленную цену соответствующего товара на Листе 2.
3.Использование формулы ИНДЕКС-ПОИСКПОЗ для автоматического заполнения из другого листа в Excel
Мы также можем комбинировать функции ИНДЕКС и ПОИСКПОЗ для автоматического обновления данных с одного листа на другой в Excel.
📌 Шаг 1:
➤ Выберите ячейку D5 на листе Sheet2 и введите следующую формулу:
➤ Нажмите Enter, и вы получите первую извлеченную цену смартфона из Sheet1.
📌 Шаг 2:
➤ Теперь используйте дескриптор заполнения, чтобы автоматически заполнить остальные ячейки в столбце D.
После извлечения прайс-листа для всех смартфонов на Листе 2 теперь можно легко автоматически обновлять любую цену соответствующего смартфона на Листе 2 в зависимости от изменения цены на Листе 1.
Заключительные слова
Я надеюсь, что все эти простые методы, упомянутые выше, теперь помогут вам применять их в своих электронных таблицах Excel, когда это необходимо. Если у вас есть какие-либо вопросы или отзывы, пожалуйста, дайте мне знать в разделе комментариев. Или вы можете ознакомиться с другими нашими статьями, посвященными функциям Excel, на этом веб-сайте.
Как автоматически заполнить одну и ту же ячейку с разных листов листом в Excel?
Вы когда-нибудь сталкивались с проблемой вывода данных из одних и тех же ячеек нескольких листов на один лист? Например, вы хотите вывести значения данных из ячейки A2 листов Sheet1, Sheet2, Sheet3 и так далее. Вы можете заполнить значение данных по одному вручную, но если листов сотни, это будет тяжелая работа. Теперь я представляю вам очень быстрый и простой способ решить эту задачу в Excel.
Автоматическое заполнение одной и той же ячейки с разных листов с помощью динамического обращения к рабочим листам Kutools for Excel
Возможно, вас это заинтересует:
Транспонировать диапазон![good idea2]()
Автоматически заполнять одну и ту же ячейку с разных листов с помощью Dynamicly Refer to Worksheets of Kutools for Excel
Например, ячейка A2 — это ячейка с общим объемом продаж каждого листа, и теперь я хочу извлечь общий объем продаж в каждой ячейке A2 из нескольких листов и заполнить их на новом листе.
После бесплатной установки Kutools for Excel сделайте следующее:
Используйте эти приемы автозаполнения электронных таблиц, чтобы выполнять задачи быстрее (и эффективнее) и тратить меньше времени на Microsoft Excel.
При заполнении электронных таблиц функции автозаполнения Excel — самый эффективный способ сэкономить время. Большинство людей не понимают, что многие действия, которые они делают вручную в Excel, можно автоматизировать.
Например, вы хотите применить формулу только к каждой второй или третьей строке при перетаскивании вниз для автозаполнения. Или, может быть, вы хотите заполнить все пробелы на листе. В этой статье мы познакомим вас с пятью наиболее эффективными способами автоматизации заполнения столбцов.
1. Автозаполнение каждой второй ячейки в Excel
Любой, кто когда-либо использовал Excel, знает, как использовать функцию автозаполнения для автозаполнения ячейки Excel на основе другой.
Просто нажмите и удерживайте указатель мыши в правом нижнем углу ячейки и перетащите ее вниз, чтобы применить формулу из этой ячейки ко всем расположенным под ней ячейкам (аналогично копированию формул в Excel).
В случае, если первая ячейка представляет собой просто число, а не формулу, Excel просто автоматически заполнит ячейки, считая вверх на единицу.
Но что, если вы не хотите применять формулу автозаполнения Excel к каждой отдельной ячейке под ней? Например, что, если вы хотите, чтобы каждая вторая ячейка заполнялась автоматически и объединяла имя и фамилию, но вы хотите оставить строки адреса нетронутыми?
Как автоматически заполнить каждую вторую ячейку в Excel
Этого можно добиться, немного изменив процедуру автозаполнения. Вместо того, чтобы щелкнуть первую ячейку, а затем перетащить ее вниз из правого нижнего угла, вместо этого вы выделите первые две ячейки. Затем поместите мышь в правый нижний угол двух ячеек, пока курсор не примет форму «+».
Теперь удерживайте и перетащите его вниз, как обычно.
Excel больше не заполняет автоматически каждую ячейку на основе первой ячейки, а теперь заполняет только каждую вторую ячейку в каждом блоке.
Как обрабатываются другие ячейки
Что делать, если эти вторые ячейки не пусты? Что ж, в этом случае Excel применит те же правила ко второй ячейке первого блока, который вы выделили, ко всем остальным ячейкам.
Например, если во второй ячейке есть "1", Excel автоматически заполнит каждую вторую ячейку, увеличив ее на 1.
Вы можете себе представить, насколько эта гибкость может значительно повысить эффективность автоматического заполнения данных на листах. Это один из многих способов, с помощью которых Excel помогает сэкономить время при работе с большим объемом данных.
2. Автозаполнение до конца данных в Microsoft Excel
При работе с таблицами Excel в корпоративной среде люди часто сталкиваются с большими таблицами.
Достаточно просто перетащить курсор мыши сверху вниз в наборе из 100–200 строк, чтобы автоматически заполнить этот столбец. Но что, если в электронной таблице на самом деле 10 000 или 20 000 строк? Перетаскивание курсора мыши вниз по 20 000 строк заняло бы много времени.
Есть простой способ сделать это более эффективным. Вот как можно автоматически заполнить большие области в Excel. Вместо того, чтобы перетаскивать весь столбец вниз, просто удерживайте нажатой клавишу Shift на клавиатуре.
Теперь, когда вы наводите указатель мыши на правый нижний угол ячейки, вместо значка плюса вы заметите значок с двумя горизонтальными параллельными линиями.
Теперь все, что вам нужно сделать, это дважды щелкнуть этот значок, и Excel автоматически заполнит весь столбец, но только до тех мест, где в соседнем столбце фактически есть данные.
Этот трюк может сэкономить бесчисленное количество часов, потраченных впустую на попытки провести мышью вниз по сотням или тысячам строк.
3. Заполните пробелы
Представьте, что вам поручили очистить электронную таблицу Excel, и ваш начальник хочет, чтобы вы применили определенную формулу к каждой пустой ячейке в столбце.
Вы не видите какой-либо предсказуемой закономерности, поэтому вы не можете использовать описанный выше прием автозаполнения "каждый второй x". Кроме того, такой подход сотрет все существующие данные в столбце. Что вы можете сделать?
Ну, есть еще один трюк, который вы можете использовать, чтобы заполнить только пустые ячейки тем, что вам нравится.
На приведенном выше листе ваш начальник хочет, чтобы вы заполнили любую пустую ячейку строкой "Н/Д". На листе всего с несколькими строками это будет простой ручной процесс. Но в таблице с тысячами строк это заняло бы у вас целый день.
Поэтому не делайте этого вручную. Просто выберите все данные в столбце. Затем перейдите в главное меню, выберите значок «Найти и выбрать», выберите «Перейти к специальному».
В следующем окне выберите Пробелы.
В следующем окне вы можете ввести формулу в первую пустую ячейку. В этом случае вы просто наберете N/A и затем нажмете Ctrl + Enter, чтобы то же самое применялось к каждой найденной пустой ячейке.
При желании вместо "Н/Д" вы можете ввести формулу в первую пустую ячейку (или щелкнуть предыдущее значение, чтобы использовать формулу из ячейки чуть выше пустой).
Когда вы нажмете Ctrl + Enter, эта же формула будет применена ко всем остальным пустым ячейкам. С помощью этой функции можно легко и быстро навести порядок в электронной таблице.
4. Заполнить макросом предыдущего значения
Этот последний трюк на самом деле требует нескольких шагов. Вам нужно щелкнуть кучу пунктов меню, и сокращение кликов — это то, что нужно для того, чтобы стать более эффективным, верно?
Итак, давайте продвинемся в этом последнем трюке еще на один шаг. Давайте автоматизируем это с помощью макроса. Следующий макрос в основном выполняет поиск по столбцу и проверяет наличие пустой ячейки. Если пусто, будет скопировано значение или формула из ячейки над ним.
Чтобы создать макрос, щелкните элемент меню "Разработчик" и щелкните значок "Макросы".
Назовите макрос и нажмите кнопку "Создать макрос". Это откроет окно редактора кода. Вставьте следующий код в новую функцию.
Подход в приведенном выше скрипте заключается в том, чтобы сделать его гибким, чтобы пользователь листа мог указать, с какого столбца и строки начинать. Итак, теперь, когда у вас есть лист, который выглядит так:
Заполнив пустую ячейку той же формулой, что и ячейка над ней, вы можете запустить свой макрос, чтобы заполнить пробелы в столбце G.
После того, как вы ответите на запросы о начальном столбце и строке, он заполнит все пробелы в этом столбце, не затрагивая существующие данные.
По сути, столбец заполняется автоматически, а существующие данные остаются без изменений. Это непросто сделать, просто перетащив мышь вниз по столбцу, но это возможно с помощью подхода на основе меню, описанного выше, или подхода с использованием макросов, описанного в этом разделе.
5. Макрос итерационных вычислений
Итеративные вычисления основаны на результатах предыдущих строк. Например, прибыль компании в следующем месяце может зависеть от прибыли предыдущего месяца.
В этом случае вы должны включить значение предыдущей ячейки в вычисление, которое включает данные со всего листа или книги. Это означает, что вы не можете просто скопировать и вставить ячейку, а вместо этого выполните расчет на основе фактических результатов внутри ячейки.
Давайте изменим предыдущий макрос, чтобы выполнить новый расчет на основе результатов из предыдущей ячейки.
В этом скрипте пользователь указывает как первый, так и последний номер строки. Поскольку в остальной части столбца нет данных, скрипт понятия не имеет, с чего начать. Как только скрипту будет предоставлен диапазон, он выполнит интерактивный расчет, используя предыдущее значение, и заполнит весь столбец новыми данными.
Имейте в виду, что это всего лишь альтернативный подход к итеративным вычислениям. Вы можете сделать то же самое, введя формулу непосредственно в следующую пустую ячейку и включив предыдущую ячейку в формулу. Затем, когда вы автоматически заполните этот столбец, предыдущее значение будет включено таким же образом.
Преимущество использования макроса заключается в том, что при желании вы можете добавить дополнительную логику к интерактивным вычислениям, чего нельзя было сделать в простой формуле ячейки.
Автозаполнение столбцов Excel — это очень просто
Как видите, выбранный вами подход к автозаполнению столбцов действительно может снизить вашу рабочую нагрузку. Это особенно важно, когда вы имеете дело с электронными таблицами с тысячами столбцов или строк. Немного терпения и практики, и вы без труда сможете автоматически заполнять формулы в Excel.
Читайте также: