Як написати формулу Excel, яка шукатиме значення на іншому аркуші, виходячи з того, який продукт обрано. Як витягувати дані з іншого аркуша для кожного товару.
Переглянути відео
- Ронда з Цинциннаті: Як шукати як рядок, так і аркуш?
- Використовуйте стовпець Дата, щоб зрозуміти, який аркуш використовувати
- Крок 1: Створіть звичайний VLOOKUP і використовуйте FORMULATEXT, щоб побачити, як має виглядати посилання
- Крок 2: Використовуйте конкатенацію та функцію TEXT для побудови посилання, яке виглядає як посилання на масив таблиці у формулі
- Крок 3: Створіть свій VLOOKUP, але для масиву таблиці використовуйте INDIRECT (результати з кроку 2)
- Крок 4: Скопіюйте формулу з кроку 2 (без знака рівності) та вставте у формулу з кроку 3
Стенограма відео
Дізнайтеся Excel з подкасту, епізод 2173: Шукайте аркуш і рядок.
Привіт, ласкаво просимо назад до трансляції, я Білл Джелен. Минулого тижня я був у Цинциннаті, і у Ронди в Цинциннаті було це чудове запитання. Ронді потрібно шукати цей товар, але таблиця "Шукати" відрізняється, залежно від того, який місяць це. Дивіться, у нас є різні таблиці Look Up для січня по квітень, і, мабуть, також для інших місяців. Гаразд
Тож я збираюся використовувати INDIRECT для вирішення цього, але перед тим, як зробити INDIRECT, мені завжди стає простіше просто зробити прямий VLOOKUP. Отже, ми можемо побачити, як буде виглядати форма. Отже, ми шукаємо A1 у цій таблиці в січні, і ми хочемо, щоб сьомий стовпець, кома FALSE, усі VLOOKUP закінчувалися FALSE. (= ПЕРЕГЛЯД (A2, 'січень 2018'! A1: G13,7, FALSE)). Гаразд
Ну, ну, це правильна відповідь. Що мене насправді цікавить, це отримання тексту формули цього. Отже, це показує мені, як буде виглядати формула. І вся фішка тут полягає в тому, що я намагаюся створити стовпець Helper, який буде виглядати точно так, як це Посилання, так? Отож ця частина - саме та частина саме там. Гаразд Тож цей стовпець Helper повинен виглядати так, як виглядає ця річ. І перше, що я хочу зробити, це те, що ми будемо використовувати функцію TEXT дати - функцію TEXT дати - щоб отримати mmm, пробіл, yyyy-- так, "mmm yyyy", як це- - який повинен повернути для кожної клітинки той місяць, який ми шукаємо. Тепер мені потрібно обернути це апострофами. Якби там не було космічної назви, апострофи мені не потрібні, але мені потрібні. Отже, ми збираємося вперед,апостроф, отже, це цитата - апостроф, цитата - амперсанд, а потім сюди ще одна цитата, апостроф та знак оклику, А1: G13, заключна цитата, амперсанд там.
Гаразд Отже, те, що ми успішно зробили тут, у стовпці Helper, - це те, що ми створили щось, що виглядає точно як масив таблиці у VLOOKUP. Гаразд Отже, наша відповідь буде = VLOOKUP цієї комірки, A2, кома, а тоді, коли ми дійдемо до масиву таблиці, ми будемо використовувати INDIRECT. INDIRECT - це крута функція, яка говорить: "Гей, ось комірка, яка схожа на посилання на клітинку, і я хочу, щоб ти перейшов до F2, взяв те, що схоже на посилання на клітинку, а потім використовував усе, що є в цій посиланні на комірку, як відповідь, "кома, 7, кома, FALSE", така. (= VLOOKUP (A2, 'січень 2018'! A1: G13,7, FALSE)) Гаразд, отже, зараз ми на ходу вибираємо різні таблиці Шукати та повертати значення залежно від того, квітень це чи що.
Гаразд Отже, візьмемо це 24.04., Я його зміню на 17.2.2018, ось так, і ми повинні побачити, що 403 зміниться на 203-- ідеально. Це працює. Гаразд Зараз нам, звичайно, не потрібні ці дві колонки, і насправді, якщо ви задумаєтесь, нам не потрібна вся ця колонка. Ми могли б взяти цілу річ, крім знака рівності, Ctrl + C, щоб скопіювати її, а потім, де у нас є D2, просто вставити, як це. Ідеально Двічі клацніть, щоб збити це і позбутися цього. Є наша відповідь. Добре, ми скористаємось нашим текстом формули тут, щоб лише поглянути на остаточну відповідь.
Потрібно сказати вам, якби мені довелося будувати цю формулу з нуля, я б цього не робив. Я б не зміг цього зробити. Я б це напівпередив, точно. Ось чому я завжди будую її поетапно - я з’ясовую, як буде виглядати формула, а потім об’єднаю стовпець Helper, який буде використовуватися всередині INDIRECT, а потім, нарешті, можливо, ось в кінці, зберу все назад.
Гей, багато підказок, як ця підказка в книзі, Power Excel з. Це видання 2017 року з 617 розгаданою таємницею Excel. Клацніть на "Я" у верхньому правому куті для отримання додаткової інформації.
Добре, підсумок цього епізоду: Ронда з Цинциннаті - як шукати рядок і робочий аркуш. Я використовую стовпець Дата, щоб зрозуміти, який аркуш використовувати; тому я будую звичайний VLOOKUP і використовую текст формули, щоб побачити, як має виглядати посилання; а потім побудувати щось, що виглядає як це посилання, використовуючи текстову функцію для перетворення дати в місяць і рік; використовувати Concactenation для створення чогось, що виглядає як посилання; а потім, коли ви будуєте свій VLOOKUP для другого аргументу, масиву таблиці, використовуйте INDIRECT; а потім вкажіть на результати з кроку 2; а потім необов’язковий четвертий крок, скопіюйте формулу з кроку 2, без знака рівності, і вставте її у формулу з кроку 3, щоб ви отримали одну формулу.
Ну, я хочу подякувати Ронді за появу на моєму семінарі в Цинциннаті, і я хочу подякувати вам за заїзд. Побачимось наступного разу для чергової трансляції від.
Завантажити файл
Завантажте зразок файлу тут: Podcast2173.xlsm
