000818.ru

Финансовые и Бух. услуги
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как объединить два листа с помощью ВПР в Excel?

Функция ВПР в Excel – примеры использования и советы

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

При работе с программами пользователям часто требуется быстро найти информацию в таблице и переместить ее в другой объект рабочего листа.

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

впр в excel примеры

Содержание:

VLOOKUP (Vertical Lookup) — еще одно название функции, встречающееся в английской версии табличного процессора.

Сама аббревиатура WRP означает «вертикальная ревизия».

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

В Excel также есть противоположная функция, называемая HLOOKUP или HPR — горизонтальный просмотр.

Только PEA при поиске в таблице ищет столбцы, а не строки.

Пользователи чаще предпочитают функцию БПФ, поскольку в таблицах обычно больше строк, чем столбцов.

Как объединить файлы и таблицы Excel

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

  1. Объединение таблиц из нового или существующего файла
  2. Объединение нескольких файлов Excel.

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

Объединить таблицы в новый или существующий файл

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

  • Откройте исходный файл Excel и переключитесь на лист, который вы хотите скопировать.
  • Щелкните вкладку «Главная»> раздел «Ячейки»> «Формат»> «Переместить или скопировать лист».
  • Откроется всплывающее окно, в котором вы можете выбрать существующий файл Excel или создать новый файл на ходу.
    • При выборе нового файла новый файл создается немедленно, но не сохраняется.
    • Когда вы выбираете существующий файл, у вас есть возможность выбрать, куда вставить лист, т.е. до или после существующих листов или в конце всех листов.

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

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

    Объединить несколько файлов Excel

    Макрос MergeExcel

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

    • Создайте новую электронную таблицу Excel и нажмите ALT + F11, чтобы открыть раздел разработчика.
    • Щелкните меню «Вставка», а затем «Модуль»
    • Вставьте код, указанный ниже. Назовите модуль MergeExcel

    Затем нажмите Alt + F8, чтобы открыть диалоговое окно Макрос. Это отобразит все макросы на листе. Выберите MergeExcel и нажмите Run. Вам будет предложено сохранить файлы, поэтому обязательно сделайте это. Тогда все листы из разных файлов Excel будут доступны в файле Excel, в котором вы запустили макрос. Многие из этих макросов можно найти на сайте ExtendOffice, и я настоятельно рекомендую посетить его по адресу extendoffice.com.

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

    Мы надеемся, что это руководство было легким и вы сможете объединять файлы и таблицы Excel.

    Использование ВПР в Google Таблицах

    Теперь, когда вы знаете, как работает ВПР, давайте рассмотрим несколько примеров.

    Пример 1: простой поиск информации

    Рассмотрим случай, когда у вас есть список сотрудников и их контактные данные.

    данные о персонале в таблицах Google

    Продажи ваших сотрудников, вероятно, регистрируются в листе Google. Чтобы рассчитать их комиссионные, необходимо использовать UPP, чтобы извлечь их из поля Start Date.

    Для этого вы начнете вводить функцию VLOOKUP, набрав «= VLOOKUP (» в первом поле «Age».

    набрав функцию vlookup

    Первое, что вы заметите, — это появление окна справки. Если оно не появилось, нажмите на синий значок «?» слева от ячейки.

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

    Окно помощи исчезнет, но когда вы введете следующую запятую, оно снова появится.

    ключ поиска

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

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

    выбор диапазона для vlookup

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

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

    Следующий параметр — это индекс. Мы знаем, что дата начала для сотрудника — это третий столбец в выбранном диапазоне, поэтому вы можете просто ввести 3 для этого параметра.

    Введите «FALSE» для параметра sorted, поскольку первый столбец не сортируется. Наконец, введите закрывающую скобку и нажмите Enter.

    Убедитесь, что дата начала работы сотрудника введена в поле сейчас.

    Вам остается только заполнить поля под ним!

    Когда использовать ВПР?

    Ниже приведены два варианта применения VLOOKUP.

    Первый вариант VLOOKUP подходит для следующих случаев:

    • Когда вам нужно разделить значения в объекте табличного процессора по диапазонам;
    • Для тех таблиц, где параметр WHERE может содержать несколько одинаковых значений. В этом случае формула возвращает только то значение, которое находится в последней строке относительно таблицы;
    • Когда вам нужно найти значения, которые больше тех, которые могут содержаться в первом столбце. Это позволит почти сразу найти последнюю строку в массиве.

    Если вы не ищете значение между 0 и 1, первая версия VLOOKUP не сможет найти элемент. В ячейке для результата будет возвращено только «N/A».

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

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

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

    В объектах электронных таблиц с различными типами значений VRP помогает искать текстовые строки.

    Рисунок 6 — Пример поиска текстовых значений

    Использование V PR может быть полезно, когда нужно удалить много лишних пробелов. Эта функция быстро находит имена с пробелами и позволяет их удалить. Пример:

    Рисунок 7 — UPGRAD с помощью удаления пробела

    Составляем диаграммы из итоговых данных в таблице

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

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

    Если бы все таблицы в нашем примере были разными, формула выглядела бы следующим образом

    Это и будет результатом:

    С итоговой таблицей и работать проще

    После этого просто выделите полученную таблицу. Найдите Диаграммы на вкладке Вставка и выберите нужный вид.

    Почему не работает функция ВПР

    Если вы не можете получить желаемый результат, то либо вторая таблица (аргумент 2) не соответствует, либо вы неправильно устанавливаете параметры VPR. Вот несколько распространенных ошибок при использовании этой формулы на практике

    • Не указали символ доллара в диапазоне (аргумент 2).В этом случае первая формула, введённая вручную, будет верной, а скопированные в другие ячейки — нет, поскольку диапазон поиска смещается в соответствии с правилами Эксель при копировании формул.
    • Неверно указан диапазон поиска.Опять же это ошибка в аргументе 2. Диапазон ячеек нужно указывать так, чтобы столбец, в котором ищется совпадение, был первым в выделенном диапазоне. Иначе конечно ничего найдено не будет.
    • Неверно указан номер столбца (аргумент 3).Например, можно указать столбец с таким номером, которого нет в выделенном диапазоне. Или номер столбца указан так, что в этом столбце находятся не те данные (напоминаем, что столбцы нумеруются с единицы).

    Если у Вас не работает функция ВПР, то значит скорее всего Вы неверно задали её параметры. Это бывает чаще всего, а не «глючит Excel».

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

    голоса
    Рейтинг статьи
    Читайте так же:
    Как объединить сохранение форматирования числа / даты в Excel?
Ссылка на основную публикацию
Adblock
detector