Как Поиск от переменных листов в Excel
Imagine you have multiple identical sheets in a workbook that contains identical tables (like attendance records of each month in a separate sheet). Now you want to create a dashboard that shows you the total attendance for the month. Now having each month’s data on the dashboard at the same time is not a good option. We want a drop down to select the month. We need a VLOOKUP formula to look at from the chosen month’s sheet.
Проще говоря, нам нужна формула поиска для поиска в таблицах переменных.
Как показано на гифке выше, мы будем использовать функции ВПР и КОСВЕННО вместе для поиска на нескольких листах по их именам.
Общая формула поиска нескольких листов
|
= |
Lookup_value: это значение, которое вы ищете при поиске
таблица.
Sheet_name_reference: это ссылка на ячейку, содержащую имя листа.
Lookup_table: это ссылка на таблицу, в которой вы хотите найти lookup_value. * Это может быть именованный диапазон, таблица или абсолютная ссылка. Он должен быть одинаковым на всех листах.
Col_Index: это номер столбца в таблице, из которого вы хотите получить значение. Если вы знакомы с link: / formulas-and-functions-Introduction-of-vlookup-function [Функция VLOOKUP], вы знаете, что это такое.
Итак, давайте рассмотрим пример.
Пример: получение данных о посещаемости за выбранный месяц Итак, у нас есть рабочая тетрадь, которая поддерживает посещаемость моих студентов Excel. Данные за каждый месяц сохраняются отдельно на разных листах. В названии листа устанавливается название месяцев. На данный момент у меня есть данные за три месяца, но, конечно, их будет больше.
Я хочу создать отчет, который показывает посещаемость за выбранный месяц. Месяц можно выбрать из выпадающего списка. Формула должна иметь возможность искать на этом листе автоматически, даже если к ней добавляется новый лист.
Итак, готовим приведенную выше таблицу. В ячейке G3 мы создали ссылку: / tips-how-to-create-drop-down-lists-in-excel-sheet [раскрывающийся список с использованием раскрывающегося списка] `.
Значение поиска находится в B4. Sheet_name_reference находится в G3 *.
Таблица поиска на всех листах — B3: AZ100. Мы хотим получить значение из 2 столбцов. Итак, мы записываем эту формулу в C4 и перетаскиваем ее вниз. Аналогично для отсутствующих значений мы меняем индекс столбца.
Как это работает?
Формула решена наизнанку. Сначала посмотрим, как она решается пошагово.
Предполагая, что G3 содержит Jan *.
|
=VLOOKUP(B4,INDIRECT(«Jan!$B$3:$Az$100″*),2,0) |
|
=VLOOKUP(B4,Jan!$B$3:$Az$100*,2,0) |
|
=7 |
Итак, сначала выражение «» & $ G $ 3 & «! $ B $ 3: $ Az $ 100» преобразуется в строку «Jan! $ B $ 3: $ Az $ 100». * Затем `link: / lookup-formulas- excel-Indirect-function [INDIRECT function] `преобразует эту строку в фактическую ссылку. И наконец мы получили формулу ВПР (B4, январь! $ B $ 3: $ Az $ 100,2,0). И это, наконец, сводится к 7. Теперь, если вы измените имя листа в G3, текст ссылки на значение будет изменен. Вот как вы смотрите на таблицы переменных в Excel.
Надеюсь, это было вам полезно. Если у вас есть какие-либо вопросы или вам нужно выполнить другой поиск, дайте мне знать в разделе комментариев ниже. Я буду рад помочь вам. А пока продолжайте Excelling.
Статьи по теме:
link: / lookup-formulas-use-vlookup-from-two-or-more-lookup-tables [Использовать ВПР из двух или более таблиц поиска] | Для поиска из нескольких таблиц мы можем использовать подход ЕСЛИОШИБКА. При поиске из нескольких таблиц ошибка используется как переход к следующей таблице. Другой метод может быть подходом If.
link: / lookup-Formulas-how-to-do-case-sensitive-lookup-in-excel [Как делать поиск с учетом регистра в Excel] | функция ВПР в Excel не чувствительна к регистру и возвращает первое совпадающее значение из списка. INDEX-MATCH не является исключением, но его можно изменить, чтобы сделать его чувствительным к регистру. Давайте посмотрим, как… link: / lookup-formulas-lookup-часто-появляющийся-текст-с-критериями-в-excel [Поиск часто появляющегося текста с критериями в Excel] | Поиск наиболее часто появляется в тексте в диапазоне, который мы используем ИНДЕКС-ПОИСКПОЗ с функцией РЕЖИМ. Вот способ.
Популярные статьи:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-вашу-продуктивность [50 комбинаций клавиш Excel для повышения вашей продуктивности] | Выполняйте свою задачу быстрее. Эти 50 ярлыков заставят вас работать в Excel еще быстрее.
link: / формулы-и-функции-введение-функции-vlookup [Как использовать функцию Excel VLOOKUP] | Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листов. link: / tips-countif-in-microsoft-excel [Как использовать]
link: / введение-формулы-и-функции-функции-vlookup [Excel]
link: / tips-countif-in-microsoft-excel [Функция СЧЁТЕСЛИ] | Подсчитайте значения с условиями, используя эту удивительную функцию.
Вам не нужно фильтровать данные для подсчета определенных значений. Функция Countif важна для подготовки вашей приборной панели.
link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel] | Это еще одна важная функция приборной панели. Это поможет вам суммировать значения для конкретных условий.