Представете си, че имате няколко еднакви листа в работна книга, която съдържа идентични таблици (като записи за посещаемостта на всеки месец в отделен лист). Сега искате да създадете табло, което да ви показва общата посещаемост за месеца. Сега да имаш данни за всеки месец на таблото за управление едновременно не е добър вариант. Искаме падащо меню, за да изберем месеца. Нуждаем се от формула VLOOKUP, която да разглеждаме от листа на избрания месец.
С прости думи, имаме нужда от формула за търсене, за да търсим от променливи листове.
Както показва GIF по -горе, ще използваме функцията VLOOKUP и INDIRECT заедно, за да търсим от няколко листа, въз основа на техните имена.
Обща формула за търсене на няколко листа
| =VLOOKUP(lookup_value,НЕПРЯК(""&справка_име_лист&"! lookup_table "), col_index, 0) |
Lookup_value: Това е стойността, която търсите при търсенето маса.
Справка за име_лист: Това е препратката към клетката, която съдържа името на листа.
Таблица за справки: Това е препратката към таблицата, в която искате да потърсите lookup_value. Това може да бъде именован диапазон, таблица или абсолютна справка. Тя трябва да е еднаква във всички листове.
Col_Index: Това е номерът на колоната в таблицата, от която искате да извлечете стойност. Ако сте запознати с функцията VLOOKUP, знаете какво представлява тя.
Така че нека се спрем на един пример.
Пример: Извличане на присъствие за избран месец
Така че имаме работна книга, която поддържа посещаемостта на моите студенти по Excel. Данните за всеки месец се записват отделно в различни листове. Името на листа е зададено на името на месеците. Засега имам тримесечни данни, но разбира се ще има още.
Искам да създам отчет, който показва посещаемостта на избрания месец. Месецът може да бъде избран от падащ списък. Формулата трябва да може да търси автоматично от този лист, дори ако към него е добавен нов лист.
И така, подготвяме горната таблица. Клетката G3, която създадохме падащ списък, използвайки падащ списък.
Стойността за търсене е в В4. Референцията за име_на_лист е в G3. Таблицата за търсене във всички листове е B3: AZ100. Бихме искали да извлечем стойност от 2 колони. Затова записваме тази формула в C4 и я плъзгаме надолу. По подобен начин за липсващи стойности променяме индекса на колоната.
| =VLOOKUP(В4,НЕПРЯК("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
Как работи?
Формулата е решена отвътре навън. Първо нека видим как се решава стъпка по стъпка.
Ако приемем, че G3 съдържа Ян.
| =VLOOKUP(В4,НЕПРЯК("" & $ G $ 3 & "! $ B $ 3: $ Az $ 100"), 2,0) |
| = VLOOKUP (B4, ИНДИРЕКТНО ("Ян! $ B $ 3: $ Az $ 100"),2,0) |
| = VLOOKUP (B4,Ян! $ B $ 3: $ Az $ 100,2,0) |
| =7 |
Така че първо изявлението "" & $ G $ 3 & "! $ B $ 3: $ Az $ 100" се разрешава в низ "Ян! $ B $ 3: $ Az $ 100". След това функцията INDIRECT преобразува този низ в действителна справка. И накрая получихме формулата VLOOKUP (B4, Jan! $ B $ 3: $ Az $ 100,2,0). И това най -накрая се решава на 7. Сега, ако промените името на листа в G3, текстът на справочната стойност ще бъде променен. И ето как търсите от променливи листове в Excel.
Надявам се това да ви е било полезно. Ако имате въпроси или трябва да извършите различно търсене, уведомете ме в секцията за коментари по -долу. Ще се радвам да ви помогна. Дотогава продължавай да се отличаваш.
Използвайте VLOOKUP от две или повече таблици за търсене | За търсене от множество таблици можем да използваме подход IFERROR. Търсенето от няколко таблици приема грешката като превключвател за следващата таблица. Друг метод може да бъде подход If.
Как да извършите търсене с чувствителност към регистъра в Excel | функцията VLOOKUP на excel не е чувствителна към регистъра и ще върне първата съвпадаща стойност от списъка. INDEX-MATCH не прави изключение, но може да бъде променен, за да стане чувствителен към регистър. Да видим как…
Търсене на често срещан текст с критерии в Excel | Търсенето най-често се появява в текст в диапазон, който използваме функцията INDEX-MATCH с MODE. Ето метода.
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност | Бъдете по -бързи в задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате функцията Excel VLOOKUP| Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате Excel COUNTIF функция| Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel | Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.