W tym artykule dowiemy się, jak pobrać cenę z listy, która odpowiada kryteriom kategorii i pozycji w programie Excel.
Scenariusz:
Łatwo jest znaleźć wartość z jednym kryterium w tabeli, w tym celu możemy po prostu użyć funkcji WYSZUKAJ.PIONOWO. Ale jeśli nie masz w danych kryteriów pojedynczej kolumny i musisz znaleźć kryteria wielu kolumn, aby dopasować wartość, funkcja WYSZUKAJ.PIONOWO nie pomaga.
Formuła ogólna dla kategorii i przedmiotu
| =INDEX(przeszukiwany_zakres,MATCH(1,INDEX((element1=element_zakres)) * (kategoria2=kategoria_zakres),0,1),0)) |
lookup_range: Jest to zakres, z którego chcesz pobrać wartość.
Kryteria1, Kryteria2, Kryteria N: Są to kryteria, które chcesz dopasować w zakresie1, zakres2 i Zakres N. Możesz mieć do 270 kryteriów - par zakresów.
Zakres1, zakres2, zakresN : Są to zakresy, w których będziesz spełniał swoje kryteria
Przykład :
Wszystko to może być trudne do zrozumienia. Rozumiem, jak korzystać z funkcji na przykładzie. Tutaj mamy tabelę danych. Chcę pobrać Nazwę klienta za pomocą Daty Rezerwacji, Konstruktora i Obszaru. Więc tutaj mam trzy kryteria i jeden zakres wyszukiwania.
Napisz tę formułę w komórce I4 naciśnij enter.
| =INDEKS(E2:E16,MATCH(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0)) |
Wiemy już, jak działają funkcje INDEX i PODAJ.POZYCJĘ w programie EXCEL, więc nie będziemy tego tutaj wyjaśniać. Porozmawiamy o sztuczce, której tutaj użyliśmy.
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16): Główna część jest taka. Każda część tej instrukcji zwraca tablicę wartości prawda fałsz.
Gdy wartości logiczne są mnożone, zwracają tablicę 0 i 1. Mnożenie działa jak operator AND. Zatem, gdy wszystkie wartości są prawdziwe tylko wtedy zwraca 1 w przeciwnym razie 0
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16) To w sumie zwróci
Co przełoży się na
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): INDEX Funkcja zwróci tę samą tablicę ({0;0;0;0;0; 0;0;1;0;0;0;0;0;0;0}) do funkcji PODAJ.POZYCJĘ jako tablica wyszukiwania.
MATCH(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): Funkcja MATCH będzie szukać 1 w tablicy {0;0;0; 0;0;0;0;1;0;0;0;0;0;0;0}. I zwróci numer indeksu pierwszej 1 znalezionej w tablicy. Tu jest 8.
INDEX(E2:E16,MATCH(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0)): Na koniec zwróci INDEX wartość z danego zakresu (E2:E16) przy znalezionym indeksie (8).
Jeśli możesz konsekwentnie nacisnąć CTRL + SHIFT + ENTER, możesz wyeliminować wewnętrzną funkcję INDEX. Po prostu napisz tę formułę i naciśnij CTRL + SHIFT + ENTER.
Formuła
| =INDEKS(E2:E16,PODZIEL(1,(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0)) |
Ogólna formuła tablicowa do wyszukiwania wielu kryteriów
| =INDEKS(przeszukiwany_zakres,MATCH(1,(kryterium1 =zakres1)*(kryterium2=zakres2)*(kryteriumN=zakresN),0)) |
Oto wszystkie notatki obserwacyjne przy użyciu wzoru w Excelu
Uwagi:
- Użyj Vlookup, jeśli masz jedno kryterium do dopasowania, jest to powszechna praktyka.
- Do tablicy wyszukiwania można dodać więcej wierszy i kolumn.
- Argumenty tekstowe muszą być podane w cudzysłowie ("").
- Tabela WYSZUKAJ.PIONOWO musi zawierać tablicę_wyszukiwania w skrajnej lewej lub pierwszej kolumnie.
- Indeks col nie może mieć wartości 1, ponieważ jest to tablica wyszukiwania
- Argument 0 jest używany dla dokładnej wartości dopasowania. Użyj 1 jako przybliżonej wartości dopasowania.
- Funkcja zwraca błąd #N/D, jeśli wyszukana wartość nie zostanie znaleziona w tablicy wyszukiwania. Więc złap błąd, jeśli to konieczne.
Mam nadzieję, że ten artykuł o tym, jak pobrać cenę z listy, która pasuje zarówno do kryteriów kategorii, jak i pozycji w programie Excel, jest objaśniający. Więcej artykułów na temat obliczania wartości i powiązanych formuł programu Excel znajdziesz tutaj. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na e-mail.
Jak pobrać najnowszą cenę w Excelu? : Aktualizacja cen w każdej firmie jest powszechna i korzystanie z najnowszych cen przy każdym zakupie lub sprzedaży jest koniecznością. Aby pobrać najnowszą cenę z listy w Excelu, korzystamy z funkcji WYSZUKAJ. Funkcja WYSZUKAJ pobiera najnowszą cenę.
Funkcja WYSZUKAJ.PIONOWO do obliczania oceny w programie Excel : Aby obliczyć oceny IF i IFS nie są jedynymi funkcjami, których możesz użyć. Funkcja WYSZUKAJ.PIONOWO jest bardziej wydajna i dynamiczna dla takich obliczeń warunkowych.Aby obliczyć oceny za pomocą funkcji WYSZUKAJ.PIONOWO, możemy użyć tego wzoru.
17 rzeczy o Excelu WYSZUKAJ.PIONOWO : WYSZUKAJ.PIONOWO jest najczęściej używany do pobierania dopasowanych wartości, ale WYSZUKAJ.PIONOWO może zrobić o wiele więcej. Oto 17 rzeczy dotyczących funkcji WYSZUKAJ.PIONOWO, o których powinieneś wiedzieć, aby skutecznie używać.
WYSZUKAJ pierwszy tekst z listy w programie Excel : Funkcja WYSZUKAJ.PIONOWO działa dobrze ze znakami wieloznacznymi. Możemy to wykorzystać do wyodrębnienia pierwszej wartości tekstowej z podanej listy w programie Excel. Oto ogólna formuła.
WYSZUKAJ datę z ostatnią wartością na liście : Aby pobrać datę, która zawiera ostatnią wartość, używamy funkcji WYSZUKAJ. Ta funkcja sprawdza komórkę, która zawiera ostatnią wartość w wektorze, a następnie używa tego odwołania do zwrócenia daty.
Popularne artykuły :
50 skrótów Excela, które zwiększą Twoją produktywność : Szybsze wykonywanie zadań w programie Excel. Te skróty pomogą Ci zwiększyć wydajność pracy w programie Excel.
Jak korzystać z funkcji 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 IF w programie Excel? : Instrukcja IF w programie Excel sprawdza warunek i zwraca określoną wartość, jeśli warunek jest PRAWDA, lub zwraca inną określoną wartość, jeśli jest FAŁSZ.
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.
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.