W tym artykule dowiemy się, jak wyszukiwać dokładne dopasowania za pomocą funkcji SUMPRODUCT w programie Excel.
Scenariusz:
Krótko mówiąc, podczas pracy z tabelami danych czasami musimy wyszukiwać wartości za pomocą liter z rozróżnianiem wielkości liter w programie Excel. Funkcje wyszukiwania, takie jak funkcje WYSZUKAJ.PIONOWO lub PODAJ.POZYCJĘ, nie znajdują dokładnego dopasowania, ponieważ nie są to funkcje rozróżniające wielkość liter. Załóżmy, że pracujesz na danych, w których 2 imiona są rozróżniane na podstawie rozróżniania wielkości liter, tzn. Jerry i JERRY to dwa różne imiona. Możesz wykonywać zadania, takie jak operacje na wielu zakresach, korzystając z formuły wyjaśnionej poniżej.
Jak rozwiązać problem?
W przypadku tego problemu będziemy musieli użyć funkcji SUMA PRODUKT i DOKŁADNIE. Teraz zrobimy formułę z funkcji. Tutaj otrzymujemy tabelę i musimy znaleźć wartości odpowiadające dokładnemu dopasowaniu w tabeli w Excelu. Funkcja SUMPRODUCT zwraca SUMA odpowiednich wartości PRAWDA (jako 1) i ignoruje wartości odpowiadające wartościom FAŁSZ (jako 0) w zwróconej tablicy
Wzór ogólny:
| = SUMPRODUCT ( -- ( DOKŁADNE ( szukana_wartość , szukana_tablica ) ) , (powrotna_tablica )) |
lookup_value : wartość do wyszukania.
lookup_array : tablica wyszukiwania dla wartości wyszukiwania.
return_array : tablica, zwracana wartość odpowiadająca tablicy wyszukiwania.
-- : Znak negacji (--) zmienia wartości, TRUE lub 1 na FAŁSZ lub 0 i FAŁSZ lub 0 na PRAWDA lub 1.
Przykład:
Wszystko to może być trudne do zrozumienia. Przetestujmy więc tę formułę, uruchamiając ją na poniższym przykładzie. Tutaj mamy dane o ilości i cenie produktów otrzymanych w terminach. Jeśli jakikolwiek produkt jest kupowany dwa razy, ma 2 nazwy. Tutaj musimy znaleźć liczbę przedmiotów dla wszystkich niezbędnych przedmiotów. W tym celu przypisaliśmy 2 listy jako otrzymaną listę i podstawowe informacje wymagane w kolumnie dla formuły. Teraz użyjemy poniższego wzoru, aby uzyskać ilość „mleka” z tabeli.
Użyj formuły:
| = PRODUKT SUMA ( -- ( DOKŁADNIE ( F5 , B3:B11 )) , ( C3:C11 ) ) |
F6 : wyszukaj wartość w komórce F6
B3:B11 : tablica wyszukiwania to Items
C3: C11: tablica zwrotów to Ilość
-- : Znak negacji (--) zmienia wartości, TRUE lub 1 na FAŁSZ lub 0 i FAŁSZ lub 0 na PRAWDA lub 1.
Tutaj zakres jest podany jako odwołanie do komórki. Naciśnij klawisz Enter, aby uzyskać ilość.
Jak widać, 58 to ilość odpowiadająca wartości „mleko” w komórce B5, a nie „mleko” w komórce B9. Będziesz musiał poszukać wartości "Mleko", jeśli potrzebujesz ilości "54" w komórce C9.
Możesz wyszukać cenę odpowiadającą wartości „mleko”, korzystając z poniższego wzoru.
Użyj formuły:
| = PRODUKT SUMA ( -- ( DOKŁADNIE ( F5 , B3:B11 )) , ( D3:D11 ) ) |
F6 : wyszukaj wartość w komórce F6
B3:B11 : tablica wyszukiwania to Items
D3: D11: tablica zwrotów to cena
Tutaj mamy 58 to Cena odpowiadająca wartości "mleko" w komórce B5 a nie "Mleko" w komórce B9. Będziesz musiał poszukać wartości "Mleko", jeśli potrzebujesz ceny "15,58" w komórce D9.
Unikalne wartości w tablicy wyszukiwania
Podobnie możesz znaleźć unikalne wartości za pomocą formuły. Tutaj jako przykład użyto wartości wyszukiwania „JAJA”.
Na powyższym obrazku otrzymaliśmy wartości dostosowujące tę samą formułę. Ale problem występuje, jeśli ma unikalną wartość i nie szukasz właściwej wartości wyszukiwania. Jak tutaj, używając wartości „Chleb” w formule, aby wyszukać w tabeli.
Formuła zwraca 0 jako ilość i cenę, ale nie oznacza to, że ilość i cena chleba wynoszą 0. Po prostu formuła zwraca 0 jako wartość, gdy nie znaleziono szukanej wartości. Więc użyj tej formuły tylko do wyszukania dokładnego dopasowania.
Możesz także wykonać wyszukiwanie dokładnych dopasowań za pomocą funkcji INDEKS i PODAJ.POZYCJĘ w programie Excel. Dowiedz się więcej o tym, jak wykonywać wyszukiwanie z uwzględnieniem wielkości liter za pomocą funkcji INDEKS I PODAJ.POZYCJĘ w programie Excel. Możesz również wyszukać częściowe dopasowania za pomocą symboli wieloznacznych w programie Excel .
Oto kilka uwag obserwacyjnych pokazanych poniżej.
Uwagi:
- Formuła działa tylko w celu wyszukania dokładnego dopasowania.
- Funkcja SUMPRODUCT traktuje wartości nieliczbowe jako 0s.
- Funkcja SUMPRODUCT traktuje wartość logiczną TRUE jako 1 i False jako 0.
- Argument tablica musi mieć ten sam rozmiar, w przeciwnym razie funkcja zwróci błąd.
- Funkcja SUMPRODUCT zwraca wartość odpowiadającą wartościom TRUE w zwracanej tablicy po pobraniu poszczególnych produktów w odpowiedniej tablicy.
- Operatory takie jak równa się ( = ), mniejszy niż równy ( <= ), Lepszy niż ( > ) lub nie równa się ( ) można wykonać w ramach zastosowanej formuły, wyłącznie z liczbami.
Mam nadzieję, że ten artykuł o tym, jak wyszukać dokładne dopasowanie za pomocą funkcji SUMPRODUCT w programie Excel, jest objaśniający. Więcej artykułów na temat formuł liczenia 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 stronie e-mail
Jak korzystać z funkcji SUMA PRODUKT w programie Excel: Zwraca SUMA po pomnożeniu wartości w wielu tablicach w programie Excel.
Użyj INDEKSU i DOPASUJ, aby wyszukać wartość : 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 OFFSET 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 programie Excel: 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.
Jak używać symboli wieloznacznych w programie Excel : Policz komórki pasujące do fraz za pomocą symboli wieloznacznych w programie Excel
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 w programie Excel.
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 w programie Excel. 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 z określonymi warunkami.