Check-moscow.ru

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

Как выполнить vlookup значения на нескольких листах, чтобы вернуть имена листов в Excel?

Excel VLOOKUP с другого листа

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

Функция VLOOKUP состоит из 4 параметров: lookup_value, table_array, col_index_num и range_lookup. Рассмотрим подробнее, что определяют эти 4 параметра в приведенном выше синтаксисе.

  • Поиск_значения — Здесь пользователь должен определить значение, которое будет искаться. Это может быть значение, состоящее из числа, даты или текста, или ссылка на ячейку.
  • Table_array — Здесь пользователь определяет диапазон ячеек для поиска данных. Кроме того, значения для поиска чувствительны к регистру, т.е. вы можете искать значения в текстовом виде, которые могут быть в верхнем или нижнем регистре, и вы сможете их найти.
  • Col_index — здесь введенное значение относится к столбцу в таблице, в котором пользователи должны найти значение.
  • Range_lookup — этот параметр необязателен, но его важно учитывать, поскольку он возвращает приблизительное соответствие, если значение поиска диапазона равно TRUE, и возвращает точное соответствие, если значение поиска диапазона равно FALSE.

Пример Vlookup с другого листа в Excel

Посмотрите на пример в примерах ниже, где у нас есть 2 листа, где на листе1 есть имена игроков с номерами их джерси, но столбцы набранных пробежек отсутствуют. Из листа2 нужно извлечь этот столбец в лист1.

Не могли бы вы скачать этот Vlookup из другого листа Excel? Что за шаблон здесь — Vlookup из другого листа Excel? Шаблон здесь

Читайте так же:
Как выбрать каждую вторую или n-ю строку в Excel?

Vlookup с другого листа — пример № 1

Vlookup с другого листа — пример № 2

Как мы видим в примере 2 выше, по сравнению с листом 2 появились дополнительные столбцы, такие как C и D. Нам нужно извлечь данные из столбца C листа 2 на лист 1. Ниже перечислены шаги для выполнения функции VLOOKUP.

  1. Сначала введите формулу = VLOOKUP (A2, Sheet2 ! $ A $ 2 : $ D $ 7, 3, FALSE) в ячейку C2 и нажмите Enter.
  2. Мы можем написать формулу в каждой ячейке, но копирование будет намного проще и эффективнее.

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

Из шага 1 видно, что формула, введенная в ячейку C2 на Листе1, имеет вид = VLOOKUP (A2, Sheet2! $ A $ 2: $ C $ 7, 3, FALSE), где,

  • A2 представляет поисковое значение, которое является номером футболки, и является одним и тем же значением на обоих листах.
  • Sheet2! — это имя листа.
  • $ A $ 2 : $ C $ 7 — это диапазон данных листа 2, где мы ищем данные номера футболки, чтобы получить данные рас, отмеченных на листе 2. А также знак «$» используется для фиксации диапазона данных, что облегчает копирование и вставку в другие ячейки.
  • 3 — это индексный номер столбца, содержащего данные из третьего столбца листа 2, и извлекает данные из диапазона A2: C7.
  • False возвращает точное совпадение, если поиск находится в диапазоне.

Шаг 2

Формула применяется ко всем ячейкам ниже, если щелкнуть и перетащить ячейку C2 вниз от угла, как показано на рисунке ниже.

Как видно из приведенного выше изображения, которое представляет собой снимок листа sheet1, где столбец C 'Выполнено баллов' заполнен с использованием формулы VLOOKUP.

Функцию VLOOK также можно использовать в одном рабочем листе, как показано ниже.

Шаг 3

Как вы можете видеть в шаге 3, мы ввели формулу в ячейку E2, чтобы найти значение в ячейке C4. Здесь в формуле мы не указали имя рабочего листа, потому что мы ищем значение на том же рабочем листе. A4 — искомое значение; A2: C7 — искомый диапазон, который является массивом array_array, а 3 возвращает соответствующее значение из столбца C.

Читайте так же:
Как дополнить ячейки до фиксированной длины в Excel?

Шаг — 4

На рисунке ниже мы видим результат запроса.

Преимущества Vlookup с другого листа

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

Недостатки Vlookup с другого листа

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

Что нужно помнить об Excel Vlookup с другого листа

  • Функция VLOOKUP никогда не ищет данные слева. Поскольку он всегда принимает значение для поискового значения в крайнем левом верхнем столбце и начинает поиск слева направо.
  • Всегда помните, что когда мы введем значение меньше 1 в параметре col_index_num, по формуле будет возвращена ошибка.
  • Для эффективного использования используйте символ '#39; (абсолютные ссылки на ячейки) для параметра table_array, чтобы пользователь мог скопировать и вставить формулу в другие ячейки без каких-либо ошибок.
  • Кроме того, нет необходимости вводить всю формулу, так как, когда дело доходит до ввода имени листа, вы можете переключать лист и выбирать диапазон с помощью мыши.
Читайте так же:
Как выбрать наибольшее или наименьшее значение в Excel?

Рекомендуемые статьи

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

Загрузка данных

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

Теперь мы воспользуемся методом .head() рамки данных sales, чтобы отобразить элементы в начале рамки данных:

Сравните то, что будет выведено, с тем, что вы можете видеть в Excel.

Сравнение внешнего вида данных, выводимых в Excel, с внешним видом данных, выводимых из фрейма данных pandas

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

  • Нумерация строк в Excel начинается с 1, а номер (индекс) первой строки в pandas равен 0.
  • В Excel столбцы имеют алфавитные названия, начинающиеся с буквы A , в то время как столбцы в pandas соответствуют названиям соответствующих переменных.

Поиск и возврат нескольких значений в одной ячейке (с использованием VBA)

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

Для достижения этой цели можно использовать следующий код:

Куда мне следует поместить этот код?

  1. Откройте книгу и нажмите Alt + F11 (откроется окно редактора VBA).
  2. В этом окне редактора VBA слева есть проводник проекта (в котором перечислены все книги и рабочие листы). Щелкните правой кнопкой мыши любой объект в книге, где вы хотите, чтобы этот код работал, и выберите Вставить -> Модуль.
  3. В окне модуля (которое появится справа) скопируйте и вставьте приведенный выше код.
  4. Теперь все готово. Перейдите в любую ячейку книги и введите = SingleCellExtract и вставьте необходимые входные аргументы (например, LookupValue, LookupRange, ColumnNumber).
Читайте так же:
Как выделить ячейки между двумя датами в Excel?

Как действует эта формула?

Функция F FT работает аналогично этой функции.

Принимает на вход 3 аргумента:

1. Lookupvalue — Строка, которую нам нужно найти в диапазоне ячеек.
2. LookupRange — Массив ячеек, из которых нам нужно получить данные (в данном случае $ B3: $ C18).
3. ColumnNumber — Это номер столбца таблицы / массива, из которого должно быть возвращено совпадающее значение (в данном случае 2).

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

Напоминание: сохраните рабочую книгу как рабочую книгу с поддержкой макросов (.xlsm или .xls), чтобы повторно использовать эту формулу. Кроме того, эта функция будет доступна только в данной рабочей книге, а не во всех рабочих книгах.

Нажмите здесь для загрузки файла примера

Узнайте, как автоматизировать скучные и повторяющиеся задачи с помощью VBA в Excel. Присоединяйтесь к курсу Excel VBA

Получить несколько значений поиска в одной ячейке (без повторения)

Данные могут содержать повторения.

Использование приведенного выше кода также приведет к повторам.

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

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

Функция MultipleLookupNoRep может быть вызвана после того, как вы поместите этот код в VB-редактор (как показано в руководстве пользователя).

Результат использования этой функции MultipleLookupNoRept можно увидеть на следующем снимке.

Нажмите здесь, чтобы загрузить файл примера

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

Читайте так же:
Как выбрать пустые / непустые ячейки из диапазона в Excel?

Хотя это легко сделать с помощью простой формулы, если вы используете Excel с подпиской Microsoft 365, если вы используете более старые версии и не имеете доступа к функциям типа TEXTJOIN, вы все равно можете сделать это с помощью VBA, создав собственную пользовательскую функцию.

Используемые синтаксисы

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

Search_key- Это значение для поиска, также известное как уникальный идентификатор.

Чтобы выполнить поиск, выберите два или более столбцов.

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

Sorted — здесь есть только два значения, по умолчанию FALSE.

Используйте TRUE, если столбцы должны быть отсортированы от наименьшего к наибольшему или от A к Z. Таким образом, формула Vlookup может не дать точного совпадения. Вместо этого вы получите приблизительный результат, который будет меньше, чем search_key. Если это не так, отображается сообщение об ошибке.

Используйте FALSE, если вам не нужна классификация. Vlookup ищет только точные совпадения и возвращает ошибку, если ничего не найдено. Если два или более значений одинаковы, Vlookup использует первое из них.

Используйте Vlookup из другой книги в Google Таблицах

Ввод формулы

Чтобы ввести формулу, перейдите на рабочий лист «Итоги продаж» и щелкните ячейку B5.

Нажмите «Ввод».

entering the vlookup functionentering the vlookup functionentering the vlookup functionВвод функции VLOOKUP

Результат должен быть равен 40. Чтобы заполнить значения под столбцом, при необходимости снова щелкните на B5. Установите курсор мыши на точку автозаполнения в правом нижнем углу ячейки. Курсор превратится в перекрестие.

mouse pointer on the autofill dotmouse pointer on the autofill dotmouse pointer on the autofill dotКогда вы поместите курсор мыши на точку в правом нижнем углу ячейки, он превратится в перекрестие Автозаполнения.

Дважды щелкните, чтобы заполнить значения под столбцом.

worksheet with data after using autofillworksheet with data after using autofillworksheet with data after using autofillДважды кликните перекрестием Автозаполнения, чтобы скопировать формулу ниже по столбцу

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector