Różne metody dopasowywania danych w programie Excel
Istnieją różne metody dopasowywania danych w programie Excel, jeśli chcemy dopasować dane w tej samej kolumnie, powiedzmy, że chcemy sprawdzić pod kątem duplikatów, możemy użyć formatowania warunkowego z karty głównej lub jeśli chcemy dopasować dane w dwóch lub więcej różnych kolumn możemy użyć funkcji warunkowych, takich jak if function.
- Metoda nr 1 - użycie funkcji Vlookup
- Metoda nr 2 - użycie indeksu + funkcji dopasowania
- Metoda nr 3 - Utwórz własną wartość wyszukiwania
Omówmy teraz szczegółowo każdą z metod
Możesz pobrać ten szablon programu Excel z danymi dopasowania - szablon programu Excel z danymi dopasowania# 1 - Dopasuj dane za pomocą funkcji WYSZUKAJ.PIONOWO
WYSZUKAJ.PIONOWO służy nie tylko do uzyskania wymaganych informacji z tabeli danych, ale może również służyć jako narzędzie uzgadniania. Jeśli chodzi o uzgodnienie lub dopasowanie danych, formuła WYSZUKAJ.PIONOWO prowadzi tabelę.
Jako przykład spójrz na poniższą tabelę.
Mamy tutaj dwie tabele danych, pierwsza to Dane 1, a druga to Dane 2.
Teraz musimy uzgodnić, czy dane w dwóch tabelach są zgodne, czy nie. Pierwszym sposobem dopasowania danych jest funkcja SUMA w programie Excel do dwóch tabel w celu uzyskania całkowitej sprzedaży.
Dane 1 - Tabela
Dane 2 - Tabela
Zastosowałem funkcję SUMA zarówno dla kolumny Kwota sprzedaży tabeli. Na samym początku dostaliśmy różnicę w wartościach. Tabela danych 1 pokazująca sprzedaż ogółem w wysokości 2,16 214 oraz tabela danych 2 pokazująca sprzedaż ogółem w wysokości 2,10 214 .
Teraz musimy to szczegółowo zbadać. Zastosujmy więc funkcję WYSZUKAJ.PIONOWO dla każdej daty.
Wybierz tablicę tabeli jako zakres danych 1 .
Potrzebujemy danych z drugiej kolumny, a zakres wyszukiwania to FALSE, czyli dokładne dopasowanie.
Wynik podano poniżej:
W następnej komórce odejmij pierwotną wartość z wartością docelową.
Po odjęciu otrzymujemy wynik jako zero.
Teraz skopiuj i wklej formułę do wszystkich komórek, aby uzyskać wartości wariancji.
W komórce G6 i G12 mamy różnice.
W Danych 1 mamy 12104 dla daty 4 marca 2019 r., Aw Danych 2 mamy 15104 dla tej samej daty, więc jest różnica 3000.
Podobnie, dla daty 18-marzec-2019 w danych 1 mamy 19351, aw danych 2 mamy 10351, więc różnica wynosi 9000.
# 2 - Dopasuj dane za pomocą funkcji INDEX + MATCH
Dla tych samych danych możemy użyć funkcji INDEKS + PODAJ.POZYCJĘ. Możemy użyć tego jako alternatywy dla funkcji WYSZUKAJ.PIONOWO.
Funkcja INDEKS służy do pobierania wartości z wybranej kolumny na podstawie podanego numeru wiersza. Aby podać numer wiersza, musimy użyć funkcji PODAJ.POZYCJĘ opartej na wartości WYSZUKAJ.
Otwórz funkcję INDEKS w komórce F3.
Wybierz tablicę jako zakres kolumn wynikowych, np. B2 do B14.
Aby uzyskać numer wiersza, otwórz teraz funkcję MATCH jako następny argument.
Wybierz wartość wyszukiwania jako komórkę D3.
Następnie wybierz tablicę przeglądową jako kolumnę Data sprzedaży w Dane 1.
W typie dopasowania wybierz „0 - Dokładne dopasowanie”.
Zamknij dwa nawiasy i naciśnij klawisz Enter, aby uzyskać wynik.
Daje to również ten sam wynik, co tylko WYSZUKAJ.PIONOWO. Ponieważ użyliśmy tych samych danych, otrzymaliśmy liczby bez zmian
# 3 - Utwórz własną wartość wyszukiwania
Teraz widzieliśmy, jak dopasować dane za pomocą funkcji programu Excel. Teraz zobaczymy inny scenariusz czasu rzeczywistego. W tym przykładzie spójrz na poniższe dane.
W powyższych danych mamy dane dotyczące sprzedaży według stref i daty, jak pokazano powyżej. Musimy ponownie przeprowadzić proces dopasowywania danych. Zastosujmy funkcję WYSZUKAJ.PIONOWO zgodnie z poprzednim przykładem.
Mamy wiele różnic. Przeanalizujmy każdy przypadek osobno.
W komórce I5 otrzymaliśmy wariancję 8300. Spójrzmy na główną tabelę.
Mimo że w głównej tabeli wartość wynosi 12104, otrzymaliśmy wartość 20404 z funkcji WYSZUKAJ.PIONOWO. Powodem tego jest to, że WYSZUKAJ.PIONOWO może zwrócić wartość pierwszej znalezionej wartości wyszukiwania.
W tym przypadku naszą wartością wyszukiwania jest data, tj. 20-Mar-2019. W powyższej komórce dla strefy północnej dla tej samej daty mamy wartość 20404, więc funkcja WYSZUKAJ.PIONOWO zwróciła również tę wartość dla strefy wschodniej.
Aby rozwiązać ten problem, musimy stworzyć unikalne wartości wyszukiwania. Połącz strefę, datę i kwotę sprzedaży w danych 1 i danych 2.
Dane 1 - Tabela
Dane 2 - Tabela
Teraz stworzyliśmy unikalną wartość dla każdej strefy z połączoną wartością strefy, daty sprzedaży i kwoty sprzedaży.
Korzystanie z tych unikatowych wartości pozwala zastosować funkcję WYSZUKAJ.PIONOWO.
Zastosuj formułę do wszystkich komórek, uzyskamy wariancję zerową we wszystkich komórkach.
W ten sposób za pomocą funkcji programu Excel możemy dopasować dane i znaleźć wariancje. Przed zastosowaniem wzoru musimy spojrzeć na duplikaty w wartości wyszukiwania, aby uzyskać dokładne uzgodnienie. Powyższy przykład najlepiej ilustruje zduplikowane wartości w wartości wyszukiwania. W takich scenariuszach musimy stworzyć własne unikalne wartości wyszukiwania i otrzymać wynik.
