Как да напиша формула на Excel, която ще търси стойност на различен лист въз основа на това кой продукт е избран. Как да изтеглите данни от различен работен лист за всеки продукт.
Гледам видео
- Ронда от Синсинати: Как да търсите както реда, така и работния лист?
- Използвайте колоната Дата, за да разберете кой лист да използвате
- Стъпка 1: Създайте редовен VLOOKUP и използвайте FORMULATEXT, за да видите как трябва да изглежда референцията
- Стъпка 2: Използвайте Concatenation и функцията TEXT, за да изградите референция, която прилича на референцията на масива на таблицата във формулата
- Стъпка 3: Създайте своя VLOOKUP, но за масива на таблицата използвайте INDIRECT (резултати от стъпка 2)
- Стъпка 4: Копирайте формулата от стъпка 2 (без знака за равенство) и поставете във формулата от стъпка 3
Видео транскрипция
Научете Excel от Podcast, епизод 2173: Потърсете листа и реда.
Хей, добре дошъл в мрежата, аз съм Бил Джелен. Бях в Синсинати миналата седмица и Ронда в Синсинати имаше този страхотен въпрос. Rhonda трябва да потърси този продукт, но таблицата Look Up е различна, в зависимост от това кой месец е. Вижте, имаме различни таблици за търсене тук за януари до април, а вероятно и за останалите месеци. Добре.
Така че ще използвам INDIRECT, за да реша това, но преди да направя INDIRECT, винаги ми е по-лесно просто да направя прав VLOOKUP. И така, можем да видим как ще изглежда формата. Така че търсим A1 в тази таблица на януари и искаме седмата колона, запетая FALSE, всички VLOOKUP завършват на FALSE. (= VLOOKUP (A2, 'януари 2018'! A1: G13,7, FALSE)). Добре.
И, добре, това е правилният отговор. Това, което наистина ме интересува, е да получа текста на формулата на това. Така ми показва как ще изглежда формулата. И целият трик тук е, опитвам се да създам колона Helper, която ще изглежда точно като тази Справка, нали? Така че тази част - точно тази част точно там. Добре. Така че тази колона Helper трябва да изглежда така, както изглежда това нещо. И първото нещо, което искам да направя, е да използваме функцията TEXT на Date - функцията TEXT на датата--, за да получим mmm, интервал, yyyy-- така, "mmm yyyy" като това- - което трябва да върне за всеки от клетките кой месец търсим. Сега трябва да го опаковам с апострофи. Ако там нямаше космическо име, нямаше да имам нужда от апострофите, но ми трябва. И така, ние ще отидем отпред,апостроф, така че това е цитат - апостроф, цитат - амперсанд, а след това тук, друг цитат, апостроф и удивителен знак, A1: G13, заключителен цитат, амперсанд там.
Добре. И така, това, което успешно направихме тук в колоната Helper, е, че изградихме нещо, което изглежда точно като масива на таблицата във VLOOKUP. Добре. Така че отговорът ни ще бъде = VLOOKUP на тази клетка, A2, запетая и след това, когато стигнем до масива на таблицата, ще използваме INDIRECT. INDIRECT е тази страхотна функция, която казва: „Хей, ето клетка, която прилича на препратка към клетка, и искам да отидете на F2, да вземете нещо, което прилича на препратка на клетка, и след това да използвате каквото и да е в тази препратка на отговорът, "запетая, 7, запетая, FALSE," подобен. (= VLOOKUP (A2, 'Jan 2018'! A1: G13,7, FALSE)) Добре, така че сега, в движение, избираме различна таблица Look Up и връщане на стойностите в зависимост от това дали е април или какво.
Добре. Така че нека вземем това 4/24, ще го променя на 17/02/2018, така, и трябва да видим, че 403 се променя на 203 - перфектно. Работи. Добре. Сега, разбира се, нямаме нужда от тези две колони и наистина, ако се замислите, нямаме нужда от цялата тази колона. Можем да вземем цялото това нещо, с изключение на знака за равенство, Ctrl + C, за да го копираме, а след това там, където имаме D2, просто поставете така. Перфектно. Щракнете двукратно, за да го свалите и да се отървете от това. Това е нашият отговор. Добре, ще използваме нашия текст от формулата тук, само за да разгледаме този окончателен отговор.
Трябва да ви кажа, че ако трябваше да изградя тази формула само от нулата, нямаше да го направя. Не бих могъл да го направя. Бих го прецакал, със сигурност. Ето защо винаги го изграждам на стъпки - измислям как ще изглежда формулата и след това обединявам колоната Helper, която ще се използва вътре в INDIRECT, и накрая, може би тук в края, ще събера всичко обратно.
Хей, много съвети като този съвет в книгата, Power Excel с. Това е изданието за 2017 г. с 617 разгадана мистерия на Excel. Щракнете върху това „I“ в горния десен ъгъл за повече информация.
Добре, заключение от този епизод: Ронда от Синсинати - как да търсите реда и работния лист. Използвам колоната Date, за да разбера кой лист да използвам; затова изграждам обикновен VLOOKUP и използвам текст на формула, за да видя как трябва да изглежда препратката; и след това изградете нещо, което изглежда като тази препратка, използвайки текстовата функция, за да конвертирате датата в месец и година; използвайте Concactenation, за да изградите нещо, което прилича на референтната; и след това, когато изграждате своя VLOOKUP за втория аргумент, масива на таблицата, използвайте INDIRECT; и след това посочете резултатите от стъпка 2; и след това незадължителната четвърта стъпка там, копирайте формулата от стъпка 2, без знака за равенство, и я поставете във формулата от стъпка 3, така че в крайна сметка ще получите една формула.
Е, искам да благодаря на Ронда, че се появи на семинара ми в Синсинати, и искам да ви благодаря, че отбихте. Ще се видим следващия път за поредното излъчване от.
Свали файл
Изтеглете примерния файл тук: Podcast2173.xlsm
