WYSZUKAJ.PIONOWO, WYSZUKAJ.POZIOMO i INDEX-MATCH to znane i popularne sposoby wyszukiwania wartości w tabeli programu Excel. Ale to nie jedyne sposoby wyszukiwania wartości w programie Excel. W tym artykule dowiemy się, jak korzystać z funkcji PRZESUNIĘCIE wraz z funkcją PODAJ.POZYCJĘ w programie Excel. Tak, dobrze to przeczytałeś, użyjemy niesławnej funkcji PRZESUNIĘCIE programu Excel, aby wyszukać określoną wartość w tabeli programu Excel.
Formuła ogólna,
| = PRZESUNIĘCIE(Komórka początkowa, PODAJ.POZYCJĘ(RowLookupValue;RowLookupRange;0);MATCH(ColLookupValue;ColLookupRange;0)) |
Przeczytaj to uważnie:
Komórka początkowa:To jest początkowa komórka tabeli przeglądowej. Powiedzmy, że jeśli chcesz wyszukać w zakresie A2:A10, to StartCell będzie miał wartość A1.
WierszWartośćWyszukiwania: To jest wartość wyszukiwania, którą chcesz znaleźć w wierszach poniżejKomórka początkowa.
Zakres wyszukiwania wierszy:Jest to zakres, w którym chcesz wyszukać RowLookupValue. Jest to zakres poniżej Komórka początkowa (A2:A10).
ColLookupValue: Jest to wartość wyszukiwania, którą chcesz znaleźć w kolumnach (nagłówkach).
Zakres wyszukiwania Col:Jest to zakres, w którym chcesz wyszukać ColLookupValue. Jest to zakres po prawej stronie StartCell (jak B1:D1).
Dość więc teorii. Zacznijmy od przykładu.
Przykład: Wyszukaj sprzedaż za pomocą funkcji PRZESUNIĘCIE i PODAJ.POZYCJĘ Z tabeli Excel
Tutaj mamy przykładową tabelę sprzedaży dokonaną przez różnych pracowników w różnych miesiącach.
Teraz nasz szef prosi nas o podanie sprzedaży wykonanej przez Id 1004 w miesiącu czerwcu. Jest na to wiele sposobów, ale ponieważ uczymy się formuły PRZESUNIĘCIE-DOPASOWANIE, użyjemy ich do wyszukania pożądanej wartości.
Mamy już powyższą formułę generyczną. Musimy tylko zidentyfikować te zmienne w tej tabeli.
Komórka początkowa jest tutaj B1. RowLookupValue to M1. Zakres wyszukiwania wierszy to B2:B1o (zakres poniżej komórki startowej).
ColLookupValue znajduje się w komórce M2. ColLookupRange to C1:I1 (Zakres nagłówków po prawej stronie StartCell) .
Zidentyfikowaliśmy wszystkie zmienne. Umieśćmy je w formule Excela.
Napisz tę formułę w komórce M3 i naciśnij przycisk Enter.
| =PRZESUNIĘCIE(B1,PODZIEL(M1,B2:B10,0)),POZYCJA(M2,C1:I1,0)) |
Gdy naciśniesz przycisk Enter, natychmiast otrzymasz wynik. Sprzedaż dokonana ID 1004 w miesiącu czerwcu to 177.
Jak to działa?
Zadaniem funkcji PRZESUNIĘCIE jest odejście od danej komórki początkowej do danego wiersza i kolumny, a następnie zwrócenie wartości z tej komórki. Na przykład, jeśli napiszę PRZESUNIĘCIE(B1,1,1), funkcja PRZESUNIĘCIE przejdzie do komórki C2 (1 komórka w dół, 1 komórka w prawo) i zwróci jej wartość.
Tutaj mamy formułęPRZESUNIĘCIE(B1,POZYCJA(M1,B2:B10,0),POZYCJA(M2,C1:I1,0)).
Pierwsza funkcja PODAJ.POZYCJĘ zwraca indeks M1 (1004) w zakresie B2:B10, czyli 4. Teraz formuła to:PRZESUNIĘCIE(B1,4,PODZIEL(M2,C1:I1,0)).
Następna funkcja PODAJ.POZYCJĘ zwraca indeks M2 ('Jun') z zakresu C1:I1, który i 7. Teraz formuła toPRZESUNIĘCIE(B1,4,7).
Teraz funkcja OFFSET po prostu przesuwa 4 komórki w dół do komórki B1, która przenosi je do B5. Następnie funkcja OFFSET przesuwa się do 7 w lewo do B5, co przenosi ją do I5. To jest to. Funkcja PRZESUNIĘCIE zwraca wartość z komórki I5, która 177.
Zalety tej techniki wyszukiwania?
To jest szybkie. Jedyną zaletą tej metody jest to, że jest szybsza niż inne metody. To samo można zrobić za pomocą funkcji INDEX-MATCH lub funkcji WYSZUKAJ.PIONOWO. Ale dobrze jest znać kilka alternatyw. Może się to kiedyś przydać.
Tak więc, w ten sposób możesz użyć funkcji PRZESUNIĘCIE i DOPASUJ do WYSZUKAJ wartości w tabelach Excela. Mam nadzieję, że było to wystarczająco wyjaśniające. Jeśli masz jakiekolwiek wątpliwości dotyczące tego artykułu lub jakiegokolwiek innego tematu związanego z excelem/VBA, zapytaj mnie w sekcji komentarzy poniżej.
Użyj indeksu i dopasowania do wartości wyszukiwania:Formuła INDEKS-POZYCJA służy do dynamicznego i precyzyjnego wyszukiwania wartości w danej tabeli. Jest to alternatywa dla funkcji WYSZUKAJ.PIONOWO i eliminuje wady funkcji WYSZUKAJ.PIONOWO.
Suma według grup PRZESUNIĘCIA w wierszach i kolumnach: Funkcja OFFSET służy do dynamicznego sumowania grupy komórek. Grupy te mogą znajdować się w dowolnym miejscu arkusza.
Jak pobrać każdą N-tą wartość w zakresie w Excelu?: Korzystając z funkcji PRZESUNIĘCIE programu Excel, możemy pobierać wartości z naprzemiennych wierszy lub kolumn. Ta formuła korzysta z funkcji WIERSZ do przechodzenia między wierszami i funkcji KOLUMNA do przechodzenia w kolumnach.
Jak korzystać z funkcji PRZESUNIĘCIA w programie Excel: Funkcja PRZESUNIĘCIE to potężna funkcja programu Excel, która jest niedoceniana przez wielu użytkowników. Ale eksperci znają moc funkcji PRZESUNIĘCIE i jak możemy wykorzystać tę funkcję do wykonywania magicznych zadań w formule Excela. Oto podstawy funkcji OFFSET.
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 LICZ.JEŻELI jest niezbędna do przygotowania pulpitu nawigacyjnego.
Jak korzystać z funkcji SUMIF w programie Excel? | To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.