W tym artykule dowiemy się, jak WYSZUKAJ wartość między dwiema liczbami w programie Excel.
Scenariusz:
W tabeli można łatwo wyszukać wartość z jednym kryterium. Możemy po prostu użyć funkcji WYSZUKAJ.PIONOWO. Ale co moglibyśmy zrobić, jeśli kryteria wielu kolumn pasują do danych i musisz wyszukiwać w wielu kolumnach, aby dopasować wartość. Dowiedzmy się, jak ten problem można rozwiązać za pomocą różnych wersji wyszukiwania programu Excel
WYSZUKAJ wartość między dwiema liczbami
Korzystanie z formuły WYSZUKAJ.PIONOWO
W przypadku dopasowania przybliżonego VLOOKUP, jeśli wartość nie zostanie znaleziona, dopasowywana jest ostatnia wartość, która jest mniejsza niż wartość wyszukiwania, i zwracana jest wartość z danego indeksu kolumny.
Formuła ogólna do wartości WYSZUKAJ między dwiema liczbami:
| = WYSZUKAJ.PIONOWO (wartość, tabela, szukana_kol , 1 ) |
Jeszcze jedną rzeczą dotyczącą funkcji Vlookup jest to, że szuka wartości w kolumnie, a jeśli nie znajdzie wartości w tablicy kolumn, to dopasowuje i zwraca wartość mniejszą niż wartość w tablicy tabeli.
Uwaga : WYSZUKAJ.PIONOWO domyślnie wykonuje przybliżone dopasowanie, jeśli pominiemy zmienną wyszukiwania zakresu. Dopasowanie przybliżone jest przydatne, gdy chcesz wykonać dopasowanie przybliżone i gdy tablica tabeli jest posortowana w porządku rosnącym.
Korzystanie z formuły INDEKS i PODAJ.POZYCJĘ
Formuła INDEKS i PODAJ.POZYCJĘ działa tak samo jak powyżej, ale ma inną składnię. Ale jeśli powinieneś wybrać tylko tę, z którą czujesz się komfortowo?
Formuła ogólna do wartości WYSZUKAJ między dwiema liczbami
| =INDEKS(zakres_wyszukiwania, DOPASUJ(1,INDEKS((kryterium1=zakres1)*(kryteria2=zakres2),0,1),0)) |
lookup_range: 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 legitymację studencką i odpowiednie oceny z testu. Teraz musieliśmy uzyskać oceny każdego ucznia, sprawdzając w tabeli systemu ocen.
Aby to zrobić, użyjemy atrybutu funkcji WYSZUKAJ.PIONOWO, co oznacza, że jeśli funkcja WYSZUKAJ.PIONOWO nie znajduje dokładnego dopasowania, szuka dopasowania przybliżonego tylko w tabeli i tylko wtedy, gdy ostatnim argumentem funkcji jest albo PRAWDA lub 1.
Tabela ocen / wyników musi być w porządku rosnącym
Użyj wzoru:
| = WYSZUKAJ.PIONOWO ( B2 , tabela , 2 , 1 ) |
Jak to działa?
Funkcja Vlookup wyszukuje wartość 40 w kolumnie znaczników i zwraca odpowiednią wartość, jeśli jest dopasowana. Jeśli nie pasuje, funkcja szuka wartości mniejszej niż wartość wyszukiwania (np. 40) i zwraca wynik.
Tutaj tablica_tabeli nazywa się zakres jako Tabela we wzorze, a B2 podano jako odwołanie do komórki.
Jak widać otrzymaliśmy ocenę za pierwszego ucznia. Teraz, aby uzyskać wszystkie inne oceny, użyjemy skrótu Ctrl + D lub użyj opcji przeciągania komórki w programie Excel.
Oto wszystkie stopnie klasy korzystające z funkcji WYSZUKAJ.PIONOWO.
Wartość WYSZUKAJ między dwiema liczbami w tabeli Pracownik
Mamy tabelę, która zawiera dane wszystkich pracowników w organizacji na osobnym arkuszu. Pierwsza kolumna zawiera identyfikatory tych pracowników. Nazwałem tę tabelę jako emp_data.
Teraz mój arkusz wyszukiwania musi pobrać informacje o pracowniku, którego identyfikator jest zapisany w komórce B3. Nazwałem B3 jako ID.
Aby ułatwić zrozumienie, wszystkie nagłówki kolumn są dokładnie w tej samej kolejności, co tabela emp_data.
Teraz wpisz poniższą formułę w komórce C3, aby pobrać strefę identyfikatora pracownika zapisanego w B3.
| =WYSZUKAJ.PIONOWO(ID;Dane_pracy;2,0) |
Spowoduje to zwrócenie strefy pracownika o identyfikatorze 1-1830456593, ponieważ kolumna nr 2 w bazie danych zawiera strefę pracowników.
Skopiuj tę formułę w pozostałych komórkach i zmień numer kolumny w formule, aby uzyskać wszystkie informacje o pracownikach.
Możesz zobaczyć wszystkie informacje związane ze wspomnianym identyfikatorem w komórce B3. Niezależnie od tego, który identyfikator napiszesz w komórce B3, wszystkie informacje zostaną pobrane bez wprowadzania jakichkolwiek zmian w formule.
Jak to działa?
Nie ma nic trudnego. Po prostu używamy funkcji WYSZUKAJ.PIONOWO, aby wyszukać identyfikator, a następnie pobrać wspomnianą kolumnę. Przećwicz WYSZUKAJ.PIONOWO przy użyciu takich danych, aby lepiej zrozumieć funkcję WYSZUKAJ.PIONOWO.
Pobierz dane pracowników za pomocą nagłówków
W powyższym przykładzie wszystkie kolumny były zorganizowane w tej samej kolejności, ale będą chwile, kiedy będziesz mieć bazę danych zawierającą setki kolumn. W takich przypadkach ta metoda wyszukiwania informacji o pracownikach nie będzie dobra. Będzie lepiej, jeśli formuła będzie mogła spojrzeć na nagłówek kolumny i pobrać dane z tej kolumny z tabeli pracowników.
Tak więc, aby pobrać wartość z tabeli za pomocą nagłówka kolumny, użyjemy dwukierunkowej metody wyszukiwania lub powiedzmy dynamicznej kolumny WYSZUKAJ.PIONOWO.
Użyj tej formuły w komórce C3 i skopiuj w pozostałych komórkach. Nie musisz nic zmieniać w formule, wszystko zostanie pobrane z thd emp_data.
| = WYSZUKAJ.PIONOWO(ID;Dane_pracy,PODZIEL(C2,Nagłówki_danych_pracy;0),0) |
Ta formuła po prostu pobiera wszystkie informacje z dopasowanych kolumn. Możesz pomieszać nagłówki w raporcie, nie będzie to miało żadnego znaczenia. Niezależnie od tego, który nagłówek jest zapisany w powyższej komórce, odpowiednie dane zawierają.
Jak to działa?
To jest po prostu dynamiczna funkcja WYSZUKAJ.PIONOWO. Możesz o tym przeczytać tutaj. Jeśli wyjaśnię to tutaj, będzie to zbyt obszerny artykuł.
Odzyskaj identyfikator pracownika z częściowym dopasowaniem
Może się zdarzyć, że nie pamiętasz całego identyfikatora pracownika, ale nadal chcesz uzyskać informacje o jakimś identyfikatorze. W takich przypadkach najlepszym rozwiązaniem jest częściowe dopasowanie WYSZUKAJ.PIONOWO.
Na przykład, jeśli wiem, że jakiś identyfikator zawiera 2345, ale nie znam całego identyfikatora. Jeśli wprowadzę tę liczbę w komórce C3, wynik będzie podobny.
Nic nie dostajemy. Ponieważ nic nie pasuje do 2345 w tabeli. Zmodyfikuj powyższą formułę w ten sposób.
| =WYSZUKAJ.PIONOWO("*"&NS&"*",Emp_Data,MECZ(C2,Emp_Data_Headers,0),0) |
Skopiuj to w całym rzędzie. A teraz masz informacje o pierwszym pracowniku, który zawiera ten numer.
Pamiętaj, że otrzymamy pierwszy identyfikator, który zawiera pasujący numer w kolumnie Emp Id. Jeśli jakikolwiek inny identyfikator zawiera ten sam numer, ta formuła nie pobierze informacji o tym pracowniku.
Jeśli chcesz uzyskać wszystkie identyfikatory pracowników zawierające ten sam numer, użyj formuły, która wyszukuje wszystkie pasujące wartości.
Korzystanie z formuły INDEKS i PODAJ.POZYCJĘ
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)) |
Jak to działa:
Wiemy już, jak działają funkcje INDEX i PODAJ.POZYCJĘ w programie EXCEL, więc nie będę 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
| {FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ}*
{FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;PRAWDA;PRAWDA;PRAWDA;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA}* {FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ} |
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 pierwszego znalezionego w tablicy, czyli 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).
Oto wszystkie notatki obserwacyjne przy użyciu wzoru w Excelu
Uwagi:
- Kolumna tabeli ocen / wyników musi być w porządku rosnącym, w przeciwnym razie funkcja może dać zły wynik.
- Funkcja WYSZUKAJ.PIONOWO wyszukuje wartość w pierwszym wierszu tablicy_tabeli i wyodrębnia odpowiednie wartości tylko po prawej stronie zakresu_przeszukiwania.
- Ostatni argument funkcji WYSZUKAJ.PIONOWO musi być ustawiony na PRAWDA lub 1, aby uzyskać przybliżone dopasowanie.
- Funkcja WYSZUKAJ.PIONOWO zwraca błąd, jeśli adres skoroszytu jest nieprawidłowy lub nieprawidłowy.
- Funkcja WYSZUKAJ.PIONOWO zwraca błąd, jeśli wartość nie jest dopasowana.
Mam nadzieję, że ten artykuł o tym, jak WYSZUKAJ wartość między dwiema liczbami 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.
Sposób korzystania z funkcji Vlookup w walidacji danych : Ogranicz użytkownikom możliwość zezwalania na wartości z tabeli przeglądowej za pomocą pola Formuła sprawdzania poprawności danych w programie Excel. Pole formuły w walidacji danych pozwala wybrać rodzaj wymaganego ograniczenia.
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.