Как Values просмотра Использование Excel OFFSET-MATCH функции
VLOOKUP, HLOOKUP и INDEX-MATCH — известные и распространенные способы поиска значений в таблице Excel. Но это не единственные способы поиска значений в Excel. В этой статье мы узнаем, как использовать функцию СМЕЩЕНИЕ вместе с функцией ПОИСКПОЗ в Excel. Да, вы все правильно прочитали, мы будем использовать печально известную функцию СМЕЩЕНИЯ Excel для поиска определенного значения в таблице Excel.
Общая формула,
|
= |
Прочтите внимательно:
StartCell: * Это начальная ячейка таблицы поиска. Скажем, если вы хотите найти в диапазоне A2: A10, тогда StartCell будет A1.
RowLookupValue: это значение поиска, которое вы хотите найти в строках нижеStartCell. RowLookupRange: это диапазон, в котором вы хотите найти RowLookupValue. Это диапазон ниже StartCell (A2: A10). ColLookupValue: * Это значение поиска, которое вы хотите найти в столбцах (заголовках).
ColLookupRange: * Это диапазон, в котором вы хотите найти ColLookupValue. Это диапазон в правой части StartCell (например, B1: D1).
Итак, достаточно теории. Начнем с примера.
Пример: поиск продаж с помощью функции СМЕЩЕНИЕ и ПОИСКПОЗ из таблицы Excel Здесь у нас есть примерная таблица продаж, выполненных разными сотрудниками в разные месяцы.
Теперь наш босс просит нас предоставить данные о продажах по Id 1004 за июнь. Есть много способов сделать это, но поскольку мы изучаем формулу OFFSET-MATCH, мы будем использовать их для поиска желаемого значения.
У нас уже есть общая формула выше. Нам просто нужно указать эти переменные в этой таблице.
StartCell здесь B1. RowLookupValue — M1. RowLookupRange — это B2: B1o (диапазон ниже начальной ячейки).
ColLookupValue находится в ячейке M2. ColLookupRange — это C1: I1 (диапазон заголовков справа от StartCell).
Мы определили все переменные. Поместим их в формулу Excel.
Запишите эту формулу в ячейку M3 и нажмите кнопку ввода.
Как только вы нажмете кнопку ввода, вы быстро получите результат. Сделано ID 1004 в июне Месяц составляет 177.
Как это работает?
Работа link: / counting-the-offset-function-in-excel [OFFSET function] заключается в перемещении от заданной начальной ячейки к заданной строке и столбцам, а затем возвращению значения из этой ячейки. Например, если я напишу OFFSET (B1,1,1), функция OFFSET перейдет в ячейку C2 (1 ячейка вниз, 1 ячейка вправо) и вернет ей значение.
Здесь у нас есть формула OFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)). Первая link: / lookup-formulas-excel-match-function [MATCH function] возвращает индекс M1 (1004) в диапазоне B2: B10, который равен 4.
Теперь формула: OFFSET (B1,4, MATCH (M2, C1: I1,0)). Следующая функция ПОИСКПОЗ возвращает индекс M2 (‘Jun’) в диапазоне C1: I1, который равен i 7. Теперь формула —OFFSET (B1,4,7). Теперь функция СМЕЩЕНИЕ просто перемещает 4 ячейки вниз в ячейку B1, которая переводит их в ячейку B5. Затем функция СМЕЩЕНИЕ переместится на 7 влево до B5, что приведет к I5. Вот и все. Функция СМЕЩЕНИЕ возвращает значение из ячейки I5, которое составляет 177.
Преимущества этого метода поиска?
Это быстро. Единственное преимущество этого метода в том, что он быстрее, чем другие методы. То же самое можно сделать с помощью функции ИНДЕКС-ПОИСКПОЗ или функции ВПР. Но неплохо знать альтернативы.
Когда-нибудь это может пригодиться.
Так что да, ребята, вот как вы можете использовать функции СМЕЩЕНИЕ и ПОИСКПОЗ для ПРОСМОТРА значений в таблицах Excel. Надеюсь, это было достаточно объяснительным. Если у вас есть какие-либо сомнения относительно этой статьи или любой другой темы, связанной с Excel / VBA, спросите меня в разделе комментариев ниже.
Статьи по теме:
link: / lookup-formulas-use-index-and-match-to-lookup-value [Использовать ИНДЕКС и ПОИСКПОЗ для поиска значения]: * Формула ИНДЕКС-ПОИСКПОЗ используется для динамического и точного поиска значения в данной таблице . Это альтернатива функции VLOOKUP, которая устраняет недостатки функции VLOOKUP.
link: / tips-sum-by-offset-groups-in-rows-and-columns [Сумма по группам OFFSET в строках и столбцах]: Функция OFFSET может использоваться для динамического суммирования группы ячеек. Эти группы могут находиться в любом месте листа.
link: / lookup-formulas-retrieving-every-nth-value-in-a-range [Как получить каждое N-е значение в диапазоне в Excel]: Используя функцию СМЕЩЕНИЕ в Excel, мы можем получать значения из чередующихся строк или столбцы. Эта формула использует функцию СТРОКА для чередования строк и функцию COLUMN для чередования столбцов.
link: / counting-the-offset-function-in-excel [Как использовать функцию OFFSET в Excel]: Функция OFFSET — это мощная функция 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 [Функция СЧЁТЕСЛИ] | Подсчитайте значения с условиями, используя эту удивительную функцию.
Вам не нужно фильтровать данные для подсчета определенных значений. Функция СЧЁТЕСЛИ необходима для подготовки вашей информационной панели.
link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel] | Это еще одна важная функция приборной панели. Это поможет вам суммировать значения для конкретных условий.