W funkcji WYSZUKAJ.PIONOWO często definiujemy col_index_no static. Kodujemy go na stałe w formule WYSZUKAJ.PIONOWO, np. WYSZUKAJ.PIONOWO(id,data,3,0). Problem pojawia się, gdy wstawiamy lub usuwamy kolumnę w danych. Jeśli usuniemy lub dodamy kolumnę przed lub za trzecią kolumną, trzecia kolumna nie będzie już odnosić się do zamierzonej kolumny. To jest jeden problem. Innym jest, gdy masz wiele kolumn do wyszukania. Musisz edytować indeks kolumn w każdej formule. Proste kopiowanie i wklejanie nie pomoże.
Ale co powiesz na to, jeśli możesz powiedzieć VLOOKUP, aby przeglądał nagłówki i zwracał tylko pasujące wartości nagłówków. Nazywa się to dwukierunkową funkcją WYSZUKAJ.PIONOWO.
Na przykład, jeśli mam formułę WYSZUKAJ.PIONOWO dlaznaki kolumna, a następnie WYSZUKAJ.PIONOWO powinien szukać znaki kolumna w danych i zwraca wartość z tej kolumny. To rozwiąże nasz problem.
Hmm… Ok, więc jak to zrobimy? Korzystając z funkcji dopasowania w funkcji WYSZUKAJ.PIONOWO.
Formuła ogólna
=WYSZUKAJ.PIONOWO(wyszukiwana_wartość,tablica_tablica,MATCH(szukany_nagłówek,nagłówki_tabeli,0),0)
Szukana_wartość: wartość wyszukiwania w pierwszej kolumnie tablicy table_array.
Tablica_tabeli: zakres, w którym chcesz przeprowadzić wyszukiwanie. Np. A2, D10.
Wyszukaj_nagłówek: nagłówek, który chcesz wyszukać w nagłówkach table_array.
Nagłówki tabeli: Odniesienie do nagłówków w tablicy tabeli. Np. jeśli tabela to A2, D10 i nagłówki na górze każdej kolumny, to jej A1:D1.
Więc teraz wiemy, czego potrzebujemy do dynamicznego col_index, wyjaśnijmy wszystko na przykładzie.
Przykład dynamicznego WYSZUKAJ.PIONOWO
W tym przykładzie mamy tabelę zawierającą dane uczniów z zakresu A4:E16.
Używając rolki nr i nagłówka, chcę pobrać dane z tej tabeli. W tym przypadku w komórce H4 chcę uzyskać dane rolki nr zapisane w komórce G4 i nagłówka w H3. Jeśli zmienię nagłówek, dane z odpowiedniego zakresu powinny zostać pobrane w komórce H4.
Zapisz tę formułę w komórce H4
=WYSZUKAJ.PIONOWO(G4,B4:E16;PODSTAW(H3;B3:E3,0);0)
Ponieważ nasza tablica tabeli to B4:E16, nasza tablica nagłówków to B3:E3.
Notatka: Jeśli dane są dobrze ustrukturyzowane, nagłówki kolumn będą miały taką samą liczbę kolumn i będzie to pierwszy wiersz w tabeli.
Jak to działa:
Tak więc główna część automatycznie ocenia numer indeksu kolumny. W tym celu użyliśmy funkcji PODAJ.POZYCJĘ.
DOPASUJ(H3,B3:E3,0): Ponieważ H3 zawiera słowo „student”, MATCH zwróci 2. Gdyby H3 miał „Ocenę”, zwróciłby 4 i tak dalej. Formuła WYSZUKAJ.PIONOWO w końcu będzie miała col_index_num.
=WYSZUKAJ.PIONOWO(G4,B4:E16,2,0)
Jak wiemy funkcja PODAJ.POZYCJĘ zwraca numer indeksu danej wartości w podanym zakresie jednowymiarowym. Dlatego MATCH wyszuka dowolną wartość zapisaną w H3 w zakresie B3:E3 i zwróci jej numer indeksu.
Teraz za każdym razem, gdy zmienisz nagłówek w H3, jeśli znajduje się w nagłówkach, ta formuła zwróci wartość z odpowiedniej kolumny. W przeciwnym razie wystąpi błąd #N/D.
WYSZUKAJ.PIONOWO w wielu kolumnach szybko
W powyższym przykładzie potrzebowaliśmy odpowiedzi z jednej wartości kolumny. Ale co, jeśli chcesz uzyskać wiele kolumn naraz. Jeśli skopiujesz powyższą formułę, zwróci błędy. Musimy wprowadzić w nim kilka drobnych zmian, aby był przenośny.
Korzystanie z odwołań bezwzględnych z funkcją WYSZUKAJ.PIONOWO
Napisz poniższy wzór w komórce H2.
= WYSZUKAJ.PIONOWO($G2,$B$2:$E$14,DOPASUJ(H$1,$B$1:$E$1,0),0)
Teraz skopiuj H2 do wszystkich komórek w zakresie H2:J6, aby wypełnić je danymi.
Jak to działa:
Tutaj podałem bezwzględne odniesienie każdego zakresu z wyjątkiem wiersza w wartości wyszukiwania dla WYSZUKAJ.PIONOWO ($G2) i kolumnę w szukanej_wartości dla PODAJ.POZYCJĘ (H$1).
G2: Umożliwi to zmianę wiersza dla wartości wyszukiwania dla funkcji WYSZUKAJ.PIONOWO podczas kopiowania w dół, ale ograniczenie kolumny do zmiany po skopiowaniu w prawo. Co sprawi, że funkcja WYSZUKAJ.PIONOWO będzie szukać identyfikatora z kolumny G tylko ze względnym wierszem.
Podobnie, H$1 pozwoli na zmianę kolumny podczas kopiowania w poziomie i ograniczy wiersz podczas kopiowania w dół.
Korzystanie z nazwanych zakresów
Powyższy przykład działa dobrze, ale czytanie i pisanie tej formuły staje się trudne. A to wcale nie jest przenośne. Można to uprościć za pomocą nazwane zakresy.
Najpierw zajmiemy się nazewnictwem. W tym przykładzie nazwałem
B2 USD: 14 USD : jako dane
$B$1:$E$1 : jako nagłówki
H$1 : Nazwij go jako nagłówek. Uczyń kolumny względne. W tym celu wybierz H1. Naciśnij CTRL+F3, kliknij nowy, w Odnosi się do sekcji usuń '$' z początku H.
$G2: Podobnie, nazwij to jako RollNo. Tym razem wiersz staje się względny, usuwając „$” z początku 2.
Teraz, gdy masz już wszystkie nazwy na arkuszu, napisz tę formułę w dowolnym miejscu w pliku Excel. Zawsze dostanie poprawną odpowiedź.
=WYSZUKAJ.PIONOWO(RollNo;Dane; PODAJ.POZYCJĘ(Nagłówek;Nagłówki;0);0)
Widzisz, każdy może to przeczytać i zrozumieć.
Używając tych metod, możesz uczynić col_index_num dynamicznym. Daj mi znać, czy to było pomocne w sekcji komentarzy poniżej.
Jak używać tFunkcja WYSZUKAJ.PIONOWO w programie Excel
Odniesienie względne i bezwzględne w programie Excel
Nazwane zakresy w Excelu
Jak WYSZUKAJ.PIONOWO z innego arkusza Excel
WYSZUKAJ.PIONOWO Wiele wartości
popularne artykuły
50 skrótów Excela, aby zwiększyć produktywność : Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak używać tFunkcja WYSZUKAJ.PIONOWO w programie 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 funkcji LICZ.JEŻELI w programie Excel : 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 SUMA.JEŻELI w programie Excel : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.