Как написать формулу Excel, которая будет искать значение на другом листе в зависимости от того, какой продукт выбран. Как извлечь данные из разных листов для каждого продукта.
Смотреть видео
- Ронда из Цинциннати: как найти строку и лист?
- Используйте столбец Дата, чтобы выяснить, какой лист использовать
- Шаг 1. Создайте обычный VLOOKUP и используйте FORMULATEXT, чтобы увидеть, как должна выглядеть ссылка.
- Шаг 2. Используйте объединение и функцию ТЕКСТ, чтобы создать ссылку, которая выглядит как ссылка на массив таблицы в формуле.
- Шаг 3. Создайте ВПР, но для массива таблиц используйте КОСВЕННЫЙ (результаты из шага 2)
- Шаг 4. Скопируйте формулу из шага 2 (без знака равенства) и вставьте в формулу из шага 3.
Стенограмма видео
Изучите Excel из подкаста, эпизод 2173: Найдите лист и строку.
Привет, добро пожаловать обратно в сеть, я Билл Джелен. На прошлой неделе я был в Цинциннати, и у Ронды из Цинциннати возник этот интересный вопрос. Ронде необходимо найти этот продукт, но таблица поиска может быть разной в зависимости от того, какой месяц сейчас. Видите ли, у нас здесь разные таблицы Look Up с января по апрель, и, предположительно, для других месяцев тоже. Хорошо.
Так что я собираюсь использовать КОСВЕННЫЙ, чтобы решить эту проблему, но прежде чем я сделаю КОСВЕННЫЙ, мне всегда легче просто выполнить прямую ВПР. Итак, мы можем увидеть, как будет выглядеть форма. Итак, мы ищем A1 в этой таблице за январь и хотим, чтобы седьмой столбец, запятая FALSE, все ВПР заканчивались на FALSE. (= ВПР (A2, 'январь 2018'! A1: G13,7, FALSE)). Хорошо.
И, что ж, это правильный ответ. Что меня действительно интересует, так это получить текст формулы этого. Это показывает мне, как будет выглядеть формула. И весь фокус здесь в том, что я пытаюсь создать столбец Helper, который будет выглядеть точно так же, как этот справочник, верно? Итак, эта часть … только эта часть прямо здесь. Хорошо. Итак, эта колонка Helper должна выглядеть так, как выглядит эта штука. И первое, что я хочу сделать, это использовать функцию ТЕКСТ даты - функцию ТЕКСТ даты - чтобы получить ммм, пробел, гггг - итак, «ммм гггг» вот так - - который должен возвращать для каждой ячейки, какой месяц мы ищем. Теперь мне нужно обернуть это апострофами. Если бы там не было имени пространства, мне бы не понадобились апострофы, но они мне нужны. Итак, мы собираемся Concactenate впереди,апостроф, вот цитата - апостроф, цитата - амперсанд, а затем здесь еще одна цитата, апостроф и восклицательный знак, A1: G13, заключительная цитата, там амперсанд.
Хорошо. Итак, то, что мы успешно сделали здесь, в столбце Helper, - это построение чего-то, что выглядит в точности как массив таблиц в VLOOKUP. Хорошо. Итак, наш ответ будет = ВПР этой ячейки, A2, запятой, а затем, когда мы дойдем до массива таблицы, мы будем использовать КОСВЕННЫЙ. INDIRECT - это классная функция, которая говорит: «Эй, вот ячейка, которая выглядит как ссылка на ячейку, и я хочу, чтобы вы перешли к F2, возьмите то, что выглядит как ссылка на ячейку, а затем используйте все, что есть в этой ссылке на ячейку, как ответ: «запятая, 7, запятая, ЛОЖЬ» (= VLOOKUP (A2, 'Jan 2018'! A1: G13,7, FALSE)) Хорошо, теперь, на ходу, мы выбираем различная таблица Look Up и возвращает значения в зависимости от того, апрель это или что.
Хорошо. Итак, давайте возьмем это 24 апреля, я заменю его на 17 февраля 2018 года, вот так, и мы должны увидеть, что 403 изменилось на 203 - идеально. Это работает. Хорошо. Конечно, нам здесь не нужны эти два столбца, и на самом деле, если подумать, нам не нужен весь столбец. Мы могли бы взять все это, за исключением знака равенства, Ctrl + C, чтобы скопировать его, а затем, где у нас есть D2, просто вставить, вот так. Отлично. Дважды щелкните, чтобы сбить это и избавиться от этого. Вот наш ответ. Хорошо, мы будем использовать здесь текст нашей формулы, просто чтобы взглянуть на окончательный ответ.
Я должен сказать вам, что если бы мне пришлось построить эту формулу с нуля, я бы этого не сделал. Я бы не смог этого сделать. Я бы наверняка облажался. Вот почему я всегда строю его поэтапно - я выясняю, как будет выглядеть формула, затем объединяю столбец Helper, который будет использоваться внутри INDIRECT, а затем, наконец, может быть здесь, в конце, собираю все вместе.
Привет, много советов, подобных этому совету в книге Power Excel с. Это издание 2017 года, в котором разгадана тайна 617 Excel. Щелкните "I" в правом верхнем углу для получения дополнительной информации.
Хорошо, подведем итоги этого Эпизода: Ронда из Цинциннати - как найти и строку, и лист. Я использую столбец «Дата», чтобы определить, какой лист использовать; поэтому я создаю обычную ВПР и использую текст формулы, чтобы увидеть, как должна выглядеть ссылка; а затем создайте что-то похожее на эту ссылку, используя текстовую функцию для преобразования даты в месяц и год; используйте Concactenation, чтобы создать что-то похожее на эталон; а затем, когда вы создаете свою ВПР для второго аргумента, массива таблиц, используйте INDIRECT; а затем укажите на результаты шага 2; а затем необязательный четвертый шаг, скопируйте формулу из шага 2 без знака равенства и вставьте ее в формулу из шага 3, так что вы получите единственную формулу.
Что ж, я хочу поблагодарить Ронду за то, что она пришла на мой семинар в Цинциннати, и я хочу поблагодарить вас за то, что заглянули. Увидимся в следующий раз для другого сетевого вещания от.
Скачать файл
Загрузите образец файла здесь: Podcast2173.xlsm
