Spis treści
- Szukaj w Excelu według wielu kryteriów
- Wyodrębniamy 2., 3. itd. wartości za pomocą funkcji WYSZUKAJ.PIONOWO
- Pobierz wszystkie powtórzenia żądanej wartości
- Wyszukiwanie XNUMXD według znanego wiersza i kolumny
- Korzystanie z wielu funkcji WYSZUKAJ.PIONOWO w jednej formule
- Dynamiczne podstawianie danych z różnych tabel za pomocą funkcji WYSZUKAJ.PIONOWO i POŚREDNI
W drugiej części naszego samouczka na temat funkcji VPR (VLOOKUP) w Excelu przeanalizujemy kilka przykładów, które pomogą Ci pokierować całą mocą VPR do rozwiązywania najbardziej ambitnych zadań Excela. W przykładach założono, że masz już podstawową wiedzę na temat działania tej funkcji. Jeśli nie, możesz zacząć od pierwszej części tego samouczka, która wyjaśnia składnię i podstawowe użycie. VPR. Cóż, zacznijmy.
Szukaj w Excelu według wielu kryteriów
Funkcjonować VPR w programie Excel to naprawdę potężne narzędzie do wyszukiwania określonej wartości w bazie danych. Istnieje jednak spore ograniczenie – jego składnia pozwala na wyszukiwanie tylko jednej wartości. Co zrobić, jeśli chcesz wyszukiwać według wielu warunków? Rozwiązanie znajdziesz poniżej.
Przykład 1: Wyszukiwanie według 2 różnych kryteriów
Załóżmy, że mamy listę zamówień i chcemy je znaleźć Ilość towaru (Ilość), na podstawie dwóch kryteriów – Nazwa klienta (Klient) nazwa produktu (Produkt). Sprawę komplikuje fakt, że każdy z kupujących zamówił kilka rodzajów towaru, co widać z poniższej tabeli:
regularna funkcja VPR nie zadziała w tym scenariuszu, ponieważ zwróci pierwszą znalezioną wartość, która pasuje do podanej wartości wyszukiwania. Na przykład, jeśli chcesz poznać ilość przedmiotu Słodycze'zamówiony przez kupującego Jeremy'ego Hilla, napisz następującą formułę:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– ta formuła zwróci wynik 15odpowiadający produktowi Jabłka, ponieważ jest to pierwsza zgodna wartość.
Istnieje proste obejście – stwórz dodatkową kolumnę, w której połączysz wszystkie pożądane kryteria. W naszym przykładzie są to kolumny Nazwa klienta (Klient) nazwa produktu (Produkt). Nie zapominaj, że połączona kolumna musi zawsze być pierwszą kolumną po lewej stronie w zakresie wyszukiwania, ponieważ jest to lewa kolumna, w której funkcja VPR wyszukuje podczas szukania wartości.
Dodajesz więc do tabeli kolumnę pomocniczą i kopiujesz następującą formułę do wszystkich jej komórek: =B2&C2. Jeśli chcesz, aby napis był bardziej czytelny, możesz oddzielić połączone wartości spacją: =B2&» «&C2. Następnie możesz użyć następującej formuły:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
Gdzie jest komórka B1 zawiera połączoną wartość argumentu lookup_value (wyszukiwana_wartość) i 4 - Argument col_index_num (numer_kolumny), tj. numer kolumny zawierającej dane do pobrania.
Przykład 2: WYSZUKAJ.PIONOWO według dwóch kryteriów z tabelą wyświetlaną na innym arkuszu
Jeśli chcesz zaktualizować tabelę główną (Tabela główna), dodając dane z drugiej tabeli (Tabela przeglądowa), która znajduje się w innym arkuszu lub w innym skoroszycie programu Excel, możesz zebrać żądaną wartość bezpośrednio we wstawianej formule do głównego stołu.
Podobnie jak w poprzednim przykładzie, będziesz potrzebować dodatkowej kolumny w tabeli przeglądowej z połączonymi wartościami. Ta kolumna musi być skrajnie lewą kolumną w zakresie wyszukiwania.
Więc formuła z VPR może wyglądać tak:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
W tym przypadku kolumny B i C zawierają odpowiednio nazwy klientów i nazwy produktów oraz link Zamówienia! $ A i $ 2: $ D $ 2 definiuje tabelę do wyszukania w innym arkuszu.
Aby formuła była bardziej czytelna, możesz nadać zakresowi widoków nazwę, dzięki czemu formuła będzie wyglądać znacznie prościej:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Aby formuła działała, wartości w skrajnej lewej kolumnie tabeli, na którą patrzysz, muszą zostać połączone dokładnie w taki sam sposób, jak w kryteriach wyszukiwania. Na powyższym rysunku połączyliśmy wartości u2bu2band wstawiając spację między nimi w taki sam sposób, jak w pierwszym argumencie funkcji (BXNUMX& “” & CXNUMX).
Zapamiętaj! Funkcjonować VPR ograniczona do 255 znaków, nie może wyszukiwać wartości o długości większej niż 255 znaków. Pamiętaj o tym i upewnij się, że długość pożądanej wartości nie przekracza tego limitu.
Zgadzam się, że dodanie kolumny pomocniczej nie jest najbardziej eleganckim i nie zawsze akceptowalnym rozwiązaniem. Możesz zrobić to samo bez kolumny pomocniczej, ale wymagałoby to znacznie bardziej złożonej formuły z kombinacją funkcji INDEKS (INDEKS) i MATCH (BARDZIEJ EKSPOZYCJA).
Wyodrębniamy 2., 3. itd. wartości za pomocą funkcji WYSZUKAJ.PIONOWO
Już to wiesz VPR może zwrócić tylko jedną pasującą wartość, a dokładniej pierwszą znalezioną. Ale co, jeśli ta wartość powtarza się kilka razy w przeglądanej tablicy, a chcesz wyodrębnić drugą lub trzecią z nich? A jeśli wszystkie wartości? Problem wydaje się skomplikowany, ale rozwiązanie istnieje!
Załóżmy, że jedna kolumna tabeli zawiera nazwy klientów (Nazwa klienta), a druga kolumna zawiera produkty (Produkt), które kupili. Spróbujmy znaleźć 2, 3 i 4 przedmioty zakupione przez danego klienta.
Najprostszym sposobem jest dodanie kolumny pomocniczej przed kolumną nazwa klienta i wypełnij go nazwiskami klientów z powtarzającym się numerem każdej nazwy, np. Jan Kowalski1, Jan Kowalski2 itp. Załatwimy sprawę z numeracją za pomocą funkcji COUNTIF (LICZ.JEŻELI), biorąc pod uwagę, że nazwy klientów znajdują się w kolumnie B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Następnie możesz użyć normalnej funkcji VPRaby znaleźć wymaganą kolejność. Na przykład:
- Znajdź 2-ty przedmiot zamówiony przez klienta Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Znajdź 3-ty przedmiot zamówiony przez klienta Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
W rzeczywistości możesz wprowadzić odwołanie do komórki jako wartość wyszukiwania zamiast tekstu, jak pokazano na poniższym rysunku:
Jeśli tylko szukasz 2-e powtórzenie, możesz to zrobić bez kolumny pomocniczej, tworząc bardziej złożoną formułę:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
W tej formule:
- $ F $ 2 – komórka zawierająca imię i nazwisko kupującego (nie zmienia się, uwaga – link jest bezwzględny);
- $B$ – kolumna nazwa klienta;
- Table4 – Twój stół (to miejsce może być również zwykłym asortymentem);
- $ C16 – komórka końcowa Twojej tabeli lub zakresu.
Ta formuła znajduje tylko drugą pasującą wartość. Jeśli chcesz wyodrębnić pozostałe powtórzenia, skorzystaj z poprzedniego rozwiązania.
Jeśli potrzebujesz listy wszystkich dopasowań – funkcja VPR nie jest to pomocnik, ponieważ zwraca tylko jedną wartość na raz – kropkę. Ale Excel ma funkcję INDEKS (INDEKS), który bez problemu poradzi sobie z tym zadaniem. Jak taka formuła będzie wyglądać, dowiesz się na poniższym przykładzie.
Pobierz wszystkie powtórzenia żądanej wartości
Jak wspomniano powyżej VPR nie może wyodrębnić wszystkich zduplikowanych wartości ze skanowanego zakresu. Aby to zrobić, potrzebujesz nieco bardziej złożonej formuły, składającej się z kilku funkcji Excela, takich jak INDEKS (INDEKS), MAŁY (MAŁY) i RZĄD (LINIA)
Na przykład poniższa formuła wyszukuje wszystkie powtórzenia wartości z komórki F2 w zakresie B2:B16 i zwraca wynik z tych samych wierszy w kolumnie C.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Wprowadź tę formułę tablicową do wielu sąsiednich komórek, takich jak komórki F4: F8jak pokazano na poniższym rysunku. Liczba komórek musi być równa lub większa niż maksymalna możliwa liczba powtórzeń szukanej wartości. Nie zapomnij kliknąć Ctrl + Shift + Enteraby poprawnie wprowadzić formułę tablicową.
Jeśli chcesz zrozumieć, jak to działa, zagłębmy się nieco w szczegóły formuły:
1 część:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$ F $ 2 = B2: B16 – porównaj wartość w komórce F2 z każdą z wartości z zakresu B2:B16. Jeśli zostanie znalezione dopasowanie, to wyrażenie ŁAŃCUCH(C2:C16)-1 zwraca numer odpowiedniej linii (wartość -1 pozwala nie uwzględniać wiersza nagłówka). Jeśli nie ma dopasowań, funkcja IF (JEŻELI) zwraca pusty ciąg.
Wynik funkcji IF (IF) będzie taka tablica pozioma: {1,"",3,"",5,"","","","","","",12,"","",""}
2 część:
ROW()-3
СТРОКА()-3
Tutaj funkcja RZĄD (LINE) działa jako dodatkowy licznik. Ponieważ formuła jest kopiowana do komórek F4:F9, odejmujemy liczbę 3 z wyniku funkcji, aby uzyskać wartość 1 w celi F4 (linia 4, odejmij 3), aby uzyskać 2 w celi F5 (wiersz 5, odejmij 3) i tak dalej.
3 część:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Funkcjonować MAŁY (MAŁY) zwroty nieoch najmniejsza wartość w tablicy danych. W naszym przypadku, która pozycja (od najmniejszej) do zwrócenia jest określana przez funkcję RZĄD (LINIA) (patrz część 2). Więc na komórkę F4 funkcjonować MAŁY({tablica},1) powraca 1-ty (najmniejszy) element tablicy, tj. 1. Do komórki F5 powraca 2-ty najmniejszy element tablicy, czyli 3, itp.
4 część:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Funkcjonować INDEKS (INDEX) po prostu zwraca wartość określonej komórki w tablicy C2:C16. Do komórki F4 funkcjonować INDEKS ($C$2:$C$16) wróci JabłkaW razie zamówieenia projektu F5 funkcjonować INDEKS ($C$2:$C$16) wróci Słodycze' i tak dalej.
5 część:
IFERROR()
ЕСЛИОШИБКА()
Na koniec umieszczamy formułę wewnątrz funkcji JEŻELI BŁĄD (JEŻELI BŁĄD), ponieważ prawdopodobnie nie będziesz zadowolony z komunikatu o błędzie #W (#N/A), jeśli liczba komórek, do których kopiowana jest formuła, jest mniejsza niż liczba zduplikowanych wartości w wyświetlanym zakresie.
Wyszukiwanie XNUMXD według znanego wiersza i kolumny
Wykonywanie wyszukiwania XNUMXD w programie Excel obejmuje wyszukiwanie wartości według znanego numeru wiersza i kolumny. Innymi słowy, wyodrębniasz wartość komórki na przecięciu określonego wiersza i kolumny.
Przejdźmy więc do naszej tabeli i napiszmy formułę z funkcją VPR, w którym znajdą się informacje o kosztach cytryn sprzedanych w marcu.
Istnieje kilka sposobów przeprowadzenia wyszukiwania XNUMXD. Sprawdź opcje i wybierz tę, która najbardziej Ci odpowiada.
Funkcje WYSZUKAJ.PIONOWO i PODAJ.POZYCJĘ
Możesz korzystać z wielu funkcji VPR (WYSZUKAJ.PIONOWO) i WIĘCEJ EKSPOZYCYJNE (MATCH), aby znaleźć wartość na przecięciu pól nazwa produktu (ciąg) i Miesiąc (kolumna) danej tablicy:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
Powyższa formuła jest zwykłą funkcją VPR, który szuka dokładnego dopasowania wartości „Cytryny” w komórkach od A2 do A9. Ale ponieważ nie wiesz, w której kolumnie znajdują się marcowe wyprzedaże, nie będziesz mógł ustawić numeru kolumny dla trzeciego argumentu funkcji. VPR. Zamiast tego używana jest funkcja WIĘCEJ EKSPOZYCYJNEaby zdefiniować tę kolumnę.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Przetłumaczona na ludzki język formuła ta oznacza:
- Szukamy postaci „Mar” – argument lookup_value (wyszukiwana_wartość);
- Przeglądanie komórek od A1 do I1 – argument wyszukiwana_tablica (wyszukiwana_tablica);
- Zwracanie dopasowania ścisłego – argument typ_dopasowania (typ_dopasowania).
Korzystanie z 0 w trzecim argumencie mówisz funkcje WIĘCEJ EKSPOZYCYJNE poszukaj pierwszej wartości, która dokładnie odpowiada szukanej wartości. Odpowiada to wartości FAŁSZYWY (FAŁSZ) dla czwartego argumentu VPR.
W ten sposób można utworzyć formułę wyszukiwania dwukierunkowego w programie Excel, znaną również jako wyszukiwanie dwuwymiarowe lub wyszukiwanie dwukierunkowe.
Funkcja SUMA PRODUKT
Funkcjonować SUMPRODUCT (SUMPRODUCT) zwraca sumę produktów wybranych tablic:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
Funkcje INDEKS i PODAJ.POZYCJĘ
W następnym artykule szczegółowo wyjaśnię te funkcje, więc na razie możesz po prostu skopiować tę formułę:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Nazwane zakresy i operator przecięcia
Jeśli nie lubisz tych wszystkich złożonych formuł programu Excel, możesz polubić ten wizualny i niezapomniany sposób:
- Wybierz tabelę, otwórz zakładkę Wzory (Formuły) i kliknij Utwórz z wyboru (Utwórz z zaznaczenia).
- Sprawdź pudełka Górny rząd (w linii powyżej) i Lewa kolumna (w kolumnie po lewej stronie). Microsoft Excel przypisze nazwy zakresom z wartości w górnym wierszu i lewej kolumnie arkusza kalkulacyjnego. Teraz możesz wyszukiwać przy użyciu tych nazw bezpośrednio, bez tworzenia formuł.
- W dowolnej pustej komórce napisz =nazwa_wiersza nazwa_kolumny, na przykład tak:
= Cytryny Mar
… lub odwrotnie:
=Mar Cytryny
Pamiętaj, że nazwy wierszy i kolumn muszą być oddzielone spacją, co w tym przypadku działa jak operator przecięcia.
Gdy wpiszesz nazwę, program Microsoft Excel wyświetli podpowiedź z listą pasujących nazw, tak jak podczas wprowadzania formuły.
- Naciśnij przycisk Wchodzę i sprawdź wynik
Ogólnie rzecz biorąc, niezależnie od wybranej z powyższych metod, wynik wyszukiwania dwuwymiarowego będzie taki sam:
Korzystanie z wielu funkcji WYSZUKAJ.PIONOWO w jednej formule
Zdarza się, że tabela główna i tabela przeglądowa nie mają wspólnej kolumny, co uniemożliwia korzystanie ze zwykłej funkcji VPR. Istnieje jednak inna tabela, która nie zawiera interesujących nas informacji, ale ma wspólną kolumnę z tabelą główną i tabelą przeglądową.
Spójrzmy na następujący przykład. Mamy główną tabelę z kolumną SKU (nowy), gdzie chcesz dodać kolumnę z odpowiednimi cenami z innej tabeli. Dodatkowo mamy 2 tabele przeglądowe. Pierwsza (Tabela przeglądowa 1) zawiera zaktualizowane numery SKU (nowy) i nazwy produktów, a drugi (Tabela przeglądowa 2) – nazwy produktów i stare numery SKU (stary).
Aby dodać ceny z drugiej tabeli przeglądowej do tabeli głównej, musisz wykonać akcję zwaną double VPR lub zagnieżdżone VPR.
- Napisz funkcję VPR, który znajduje nazwę produktu w tabeli Tabela przeglądowa 1za pomocą SKU, jako żądana wartość:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Tutaj Nowa_SKU – nazwany zakres $A: $B na stole Tabela przeglądowa 1, 2 – to jest kolumna B, która zawiera nazwy towarów (patrz zdjęcie powyżej)
- Napisz formułę, aby wstawić ceny z tabeli Tabela przeglądowa 2 na podstawie znanych nazw produktów. Aby to zrobić, wklej wcześniej utworzoną formułę jako wartość wyszukiwania nowej funkcji VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Tutaj Cena – nazwany zakres $ A: $ C na stole Tabela przeglądowa 2, 3 to kolumna C zawierająca ceny.
Poniższy rysunek pokazuje wynik zwrócony przez utworzoną przez nas formułę:
Dynamiczne podstawianie danych z różnych tabel za pomocą funkcji WYSZUKAJ.PIONOWO i POŚREDNI
Najpierw wyjaśnijmy, co rozumiemy przez wyrażenie „Dynamiczne zastępowanie danych z różnych tabel”, aby upewnić się, że dobrze się rozumiemy.
Zdarzają się sytuacje, gdy istnieje kilka arkuszy z danymi tego samego formatu i konieczne jest wydobycie niezbędnych informacji z określonego arkusza, w zależności od wartości wprowadzonej w danej komórce. Myślę, że łatwiej to wyjaśnić na przykładzie.
Wyobraź sobie, że masz raporty sprzedaży dla kilku regionów z tymi samymi produktami i w tym samym formacie. Chcesz znaleźć dane dotyczące sprzedaży dla określonego regionu:
Jeśli masz tylko dwa takie raporty, możesz użyć haniebnie prostej formuły z funkcjami VPR и IF (IF), aby wybrać żądany raport do przeszukania:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Gdzie:
- 2 USD to komórka zawierająca nazwę produktu. Zwróć uwagę, że używamy tutaj odwołań bezwzględnych, aby uniknąć zmiany wartości wyszukiwania podczas kopiowania formuły do innych komórek.
- $ D3 to komórka z nazwą regionu. Używamy bezwzględnego odwołania do kolumny i względnego odwołania do wiersza, ponieważ planujemy skopiować formułę do innych komórek w tej samej kolumnie.
- FL_Sales и CA_Sprzedaż – nazwy tabel (lub nazwanych zakresów), które zawierają odpowiednie raporty sprzedaży. Możesz oczywiście użyć zwykłych nazw arkuszy i odwołań do zakresu komórek, na przykład „Arkusz FL”! $ A $ 3: $ B $ 10, ale nazwane zakresy są znacznie wygodniejsze.
Jednak gdy takich tabel jest wiele, funkcja IF nie jest najlepszym rozwiązaniem. Zamiast tego możesz użyć funkcji POŚREDNI (POŚREDNIE), aby powrócić do żądanego zakresu wyszukiwania.
Jak zapewne wiesz, funkcja POŚREDNI służy do zwrócenia linku podanego przez ciąg tekstowy, który jest dokładnie tym, czego teraz potrzebujemy. Więc śmiało zastąp w powyższym wzorze wyrażenie funkcją IF połączyć z funkcją POŚREDNI. Oto kombinacja VPR и POŚREDNI świetnie współpracuje z:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Gdzie:
- 2 USD – jest to komórka z nazwą produktu, jest niezmienna ze względu na link bezwzględny.
- $ D3 to komórka zawierająca pierwszą część nazwy regionu. W naszym przykładzie to FL.
- _Sprzedaż – wspólna część nazwy wszystkich nazwanych zakresów lub tabel. W połączeniu z wartością w komórce D3 tworzy w pełni kwalifikowaną nazwę wymaganego zakresu. Poniżej znajduje się kilka szczegółów dla tych, którzy są nowicjuszami w tej funkcji POŚREDNI.
Jak działają funkcja POŚREDNIA i WYSZUKAJ.PIONOWO
Najpierw przypomnę składnię funkcji POŚREDNI (POŚREDNI):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Pierwszym argumentem może być odwołanie do komórki (styl A1 lub W1K1), nazwa zakresu lub ciąg tekstowy. Drugi argument określa, jaki styl linku jest zawarty w pierwszym argumencie:
- A1jeśli argumentem jest PRAWDZIWY KOD (PRAWDA) lub nie określono;
- R1C1Jeśli FJAK E (FAŁSZYWY).
W naszym przypadku link ma styl A1, więc możesz pominąć drugi argument i skupić się na pierwszym.
Wróćmy więc do naszych raportów sprzedaży. Jeśli pamiętasz, to każdy raport to osobna tabela umieszczona na osobnym arkuszu. Aby formuła działała poprawnie, musisz nazwać tabele (lub zakresy), a wszystkie nazwy muszą mieć wspólną część. Na przykład tak: CA_Sprzedaż, FL_Sprzedaż, TX_Sprzedaż i tak dalej. Jak widać, „_Sales” występuje we wszystkich nazwach.
Funkcjonować POŚREDNI łączy wartość w kolumnie D i ciąg tekstowy „_Sales”, tym samym mówiąc VPR w której tabeli szukać. Jeśli komórka D3 zawiera wartość „FL”, formuła przeszuka tabelę FL_Sprzedaż, jeśli „CA” – w tabeli CA_Sprzedaż i tak dalej.
Wynik funkcji VPR и POŚREDNI będą następujące:
Jeśli dane znajdują się w różnych książkach Excela, musisz dodać nazwę książki przed nazwanym zakresem, na przykład:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Jeśli funkcja POŚREDNI odnosi się do innego skoroszytu, skoroszyt ten musi być otwarty. Jeśli jest zamknięty, funkcja zgłosi błąd. #ADR! (#SSYL!).