Wyobraź sobie, że masz wiele identycznych arkuszy w skoroszycie, który zawiera identyczne tabele (np. rekordy obecności z każdego miesiąca w osobnym arkuszu). Teraz chcesz utworzyć pulpit nawigacyjny, który pokaże łączną frekwencję w miesiącu. Teraz posiadanie danych z każdego miesiąca na pulpicie nawigacyjnym w tym samym czasie nie jest dobrym rozwiązaniem. Chcemy z listy rozwijanej wybrać miesiąc. Potrzebujemy formuły WYSZUKAJ.PIONOWO do obejrzenia z arkusza wybranego miesiąca.
Mówiąc prościej, potrzebujemy formuły wyszukiwania do wyszukiwania ze zmiennych arkuszy.
Jak pokazuje powyższy gif, będziemy używać razem funkcji WYSZUKAJ.PIONOWO i ADR.POŚR, aby wyszukiwać z wielu arkuszy na podstawie ich nazw.
Ogólny wzór do wyszukiwania wielu arkuszy
| =WYSZUKAJ.PIONOWO(wyszukiwana_wartość,POŚREDNI(""&nazwa_arkusza_odniesienie&"!lookup_table"),col_index,0) |
Szukana_wartość: To jest wartość, której szukasz w wyszukiwaniu Tabela.
Nazwa_arkusza_odniesienie: To jest odwołanie do komórki zawierającej nazwę arkusza.
Tabela wyszukiwania: To jest odniesienie do tabeli, w której chcesz szukać szukana_wartość. Może to być nazwany zakres, tabela lub odwołanie bezwzględne. Powinno być takie samo we wszystkich arkuszach.
Col_Index: Jest to numer kolumny w tabeli, z której chcesz pobrać wartość. Jeśli znasz funkcję WYSZUKAJ.PIONOWO, wiesz, co to jest.
Wskoczmy więc na przykład.
Przykład: pobierz obecność dla wybranego miesiąca
Mamy więc skoroszyt, który utrzymuje frekwencję moich studentów Excela. Dane każdego miesiąca są zapisywane osobno w różnych arkuszach. Nazwa arkusza jest ustawiona na nazwę miesięcy. Na razie mam dane z trzech miesięcy, ale oczywiście będzie ich więcej.
Chcę stworzyć raport pokazujący frekwencję w wybranym miesiącu. Miesiąc można wybrać z listy rozwijanej. Formuła powinna mieć możliwość automatycznego wyszukiwania z tego arkusza, nawet jeśli zostanie do niego dodany nowy arkusz.
Tak więc przygotowujemy powyższą tabelę. W komórce G3 stworzyliśmy listę rozwijaną za pomocą listy rozwijanej.
Wartość wyszukiwania jest w B4. Odniesienie_nazwa_arkusza znajduje się w G3. Tabela przeglądowa we wszystkich arkuszach to B3:AZ100. Chcielibyśmy pobrać wartość z 2 kolumn. Więc piszemy tę formułę w C4 i przeciągamy ją w dół. Podobnie dla brakujących wartości zmieniamy indeks kolumny.
| =WYSZUKAJ.PIONOWO(B4,POŚREDNI(""&$ G$3&"!$B$3:$Az$100"),2,0) |
Jak to działa?
Formuła jest rozwiązana na lewą stronę. Najpierw zobaczmy, jak to krok po kroku rozwiązać.
Zakładając, że G3 zawiera Jan.
| =WYSZUKAJ.PIONOWO(B4,POŚREDNI(""&$ G$3&"!$B$3:$Az$100"),2,0) |
| =WYSZUKAJ.PIONOWO(B4,ADR.POŚR(„Sty! $ B $ 3: $ Az 100 $”),2,0) |
| =WYSZUKAJ.PIONOWO(B4,Styczeń! $ B 3 $: $ Az 100 $,2,0) |
| =7 |
Tak więc najpierw instrukcja ""&$G$3&"!$B$3:$Az$100" jest rozwiązywana na łańcuch „Styczeń! $ B $ 3: $ Az 100 $”. Następnie funkcja ADR.POŚR konwertuje ten ciąg na rzeczywiste odwołanie. I w końcu otrzymaliśmy formułę WYSZUKAJ.PIONOWO(B4,sty!$B$3:$Az$100,2,0). I to ostatecznie kończy się na 7. Teraz, jeśli zmienisz nazwę arkusza w G3, tekst odniesienia wartości zostanie zmieniony. I tak wyglądasz z arkuszy zmiennych w Excelu.
Mam nadzieję, że to ci pomogło. Jeśli masz jakieś pytania lub chcesz przeprowadzić inne wyszukiwanie, daj mi znać w sekcji komentarzy poniżej. Chętnie Ci pomogę. Do tego czasu utrzymuj Doskonałość.
Użyj funkcji WYSZUKAJ.PIONOWO z co najmniej dwóch tabel przeglądowych | Aby wyszukać z wielu tabel, możemy zastosować podejście IFERROR. Wyszukiwanie z wielu tabel traktuje błąd jako przełącznik do następnej tabeli. Inną metodą może być podejście Jeśli.
Jak wykonać wyszukiwanie z uwzględnieniem wielkości liter w programie Excel? | funkcja WYSZUKAJ.PIONOWO programu Excel nie rozróżnia wielkości liter i zwróci pierwszą dopasowaną wartość z listy. INDEX-MATCH nie jest wyjątkiem, ale można go zmodyfikować, aby rozróżniać wielkość liter. Zobaczmy, jak…
Wyszukaj często pojawiający się tekst z kryteriami w programie Excel | Wyszukiwanie najczęściej pojawia się w tekście w zakresie, w którym używamy funkcji INDEX-MATCH z funkcją MODE. Oto metoda.
Popularne artykuły:
50 skrótów Excela, które zwiększą Twoją produktywność | Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO programu Excel| Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z Excela LICZ.JEŻELI Funkcja| Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMIF w programie Excel? | To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.