Excel-Korzystanie z VLOOKUP do porównania dwóch list

Krok 1: Istnieje kilka zasad, więc sprawdziliśmy te pierwsze

Klient wysłał bardzo podstawowy arkusz Excel, który pokazał listę swoich rekordów płatności według zamówienia zakupu, numeru faktury, kwoty i daty.

administrator (nazwijmy go Guy) następnie wygenerował raport Excel z systemu kont swojej firmy. Następnie przystąpiliśmy do korzystania z obu arkuszy Excel, aby przeprowadzić nasze pojednanie.

Zanim jednak zaczęliśmy korzystać z funkcji VLOOKUP, wyjaśniłem zawiłości uzyskiwania danych do formatu, który zapewni działanie VLOOKUP.

Excel potrzebował następujących:

• w skoroszycie klienta i skoroszycie faceta musiało znajdować się co najmniej jedna pasująca informacja dla każdego rekordu. Na przykład oba skoroszyty miały numer faktury i numer zamówienia zakupu dla każdego rekordu transakcji. Zdecydowaliśmy się użyć numeru faktury w skoroszycie Guy ’ a, aby znaleźć dopasowanie do tego samego numeru faktury w skoroszycie klienta. Ta informacja jest nazywana „wartością wyszukiwania”.
• wartość Wyszukiwania musi być unikalna, tzn. nie może być wielokrotnie powtarzana na liście klienta. Na szczęście dla Guya był tylko jeden rekord dla każdej faktury. W przypadku faktur biznesowych i numerów PO są prawie zawsze unikalne dla pojedynczej transakcji, więc są doskonałe do wykorzystania jako wartości Wyszukiwania. Innymi przykładami są kody produktów, numery klientów lub kont, numery pracowników lub kody działów. Zawsze dobrą praktyką jest sprawdzenie, czy nie ma duplikatów przed wykonaniem VLOOKUP.
• wartość Wyszukiwania musi znajdować się w pierwszej kolumnie listy w skoroszycie klienta. Niestety nie. znajdował się w 2.kolumnie (kolumna B), więc wycinaliśmy i wklejaliśmy kolumnę przed P/O nr. kolumna. Jest to ważna zasada podczas korzystania z funkcji VLOOKUP. Jeśli wartość wyszukiwania nie znajduje się w pierwszej kolumnie, wyszukiwanie nie będzie działać.

gdy już zdecydowaliśmy się na naszą wartość wyszukiwania i zreorganizowaliśmy listę w skoroszycie klienta, byliśmy gotowi stworzyć naszą VLOOKUP.

Krok 2: Wstawianie funkcji VLOOKUP

„V”w VLOOKUP oznacza „pionowy”. Wyszukiwanie pionowe służy do wyszukiwania określonych danych w pierwszej kolumnie tabeli danych. Po znalezieniu wiersza, który zawiera dane, których szukasz, odbija się do innej kolumny w tej samej tabeli danych i zwraca z niej informacje.

zdecydowaliśmy się wstawić funkcję VLOOKUP do skoroszytu faceta. W ten sposób możemy użyć funkcji, aby odwołać się do numeru faktury z jego listy i sprawdzić ten sam numer faktury w pierwszej kolumnie listy klienta. Jeśli znajdzie dopasowanie, zwróci do komórki kwotę, którą klient miał w stosunku do numeru faktury. Wtedy skończylibyśmy z kwotą Guy ’ a i kwotą klienta w tym samym arkuszu. Następnie mogliśmy łatwo porównać te dwa i ustalić, które transakcje nie pasują.

stworzyliśmy nowy nagłówek kolumny o nazwie „rekord klienta”, umieszczając go na końcu istniejącej listy. Umieszczając selektor komórek na komórce E5 wstawiliśmy funkcję VLOOKUP.

aby wstawić funkcję, kliknęliśmy kartę formuły, a następnie z grupy biblioteki funkcji kliknęliśmy przycisk Wstaw funkcję.

Wskazówka: funkcję VLOOKUP można również wstawić, klikając przycisk Insert Function na pasku formuły.

w polu Wybierz kategorię wybraliśmy opcję Wyszukaj i odsyłaj. Z listy wybierz funkcję wybraliśmy VLOOKUP, a następnie kliknęliśmy OK.

w tym momencie musieliśmy wprowadzić odpowiednie wartości do pól argumentów funkcji. Przeprowadzę Cię przez każdą z kłótni.

• Lookup_value – był to numer faktury przechowywany w komórce C5. Po wpisaniu tego w polu wartości Wyszukiwania Excel trzymał go w pamięci i czekał, aż uzupełnimy pozostałe argumenty.

• Table_array-tablica tabeli jest listą, która zawiera szczegóły transakcji w arkuszu klienta. VLOOKUP będzie szukał dopasowania do „wartości wyszukiwania” w pierwszej kolumnie tablicy tabeli. Guy przeszedł do arkusza klienta, a następnie kliknął i przeciągnął po całym obszarze, w którym znajdowały się wszystkie szczegóły transakcji. Uwzględnił kolumnę „Data”, ale A4:C26 też byłoby w porządku, ponieważ kolumna „kwota” znajduje się w tym zakresie. Proszę zauważyć, że Guy uczynił zakres absolutnym, aby wzór mógł być łatwo skopiowany.

• Col_index_num – jest to kolumna, z której facet chciał wyciągnąć kwotę faktury klienta. Była to kolumna 3 na liście transakcji klienta. Kolumny są numerowane od lewej do prawej, przy czym pierwszą kolumną w zakresie tablic tablic jest kolumna 1. Uważaj, aby nie pomylić numerów kolumn i liter kolumn. Nawet jeśli kwota faktury jest przechowywana w kolumnie „C”, Excel chce tylko lokalizacji numeru indeksu kolumny, a nie litery kolumny.

• Range_lookup-Wyszukiwanie zakresu kontroluje, czy szukasz dokładnego dopasowania do 'wartości Wyszukiwania’. Wyjaśniłem facetowi, że jeśli wprowadzi TRUE lub pozostawi puste pole Excel wyszuka jego numer faktury, ale jeśli nie może znaleźć dopasowania, zwróci następną największą wartość, która jest mniejsza niż numer faktury. Na przykład, jeśli nie może znaleźć faktury 2466, znajdzie rekord dla 2465 i zwróci za to KWOTĘ. Aby to zadziałało, najpierw trzeba było posortować listę klienta według numeru faktury. Jeśli jednak w polu „FALSE” wpisana zostanie wartość „FALSE”, program Excel będzie szukał dokładnego dopasowania do wartości Wyszukiwania. Facet chciał dokładnie dopasować numer faktury, więc wpisał FALSE do pudełka. Przekazałem również wskazówkę, że możesz wpisać 0 (zero) w miejsce słowa „FALSE” i Excel zrobi to samo.

przy wszystkich wprowadzonych argumentach Guy kliknął OK.

Excel następnie przeszedł przez każdą z wartości podanych w polu argumentów. Funkcja użyła numeru faktury (Wartość wyszukiwania), aby wyszukać pasujący numer faktury w lewej kolumnie rekordów klienta (tablica tabeli). Po znalezieniu numeru faktury przeskoczył on określoną liczbę kolumn (Numer indeksu kolumny) w tym samym wierszu, aby zwrócić kwotę faktury.

na szczęście, jak widać poniżej, klient miał taką samą wartość faktury jak Guy.

Guy następnie skopiował funkcję VLOOKUP dla wszystkich transakcji faktur.
w ciągu kilku sekund mógł zobaczyć trzy błędy # N / A (value not available errors). To pokazało, gdzie klient nie miał w rzeczywistości zapisów tych faktur. Następnie szybko odkrył różnice w kwotach faktur swoich i klienta. Od razu zauważył, że faktura 2464 była krótka $100.00, najprawdopodobniej błąd klucza.

Krok 3: double-check

teraz z powodzeniem porównaliśmy nasze zapisy z danymi klienta. jednak jako podwójne sprawdzenie zdecydowaliśmy się na porównanie z danymi klienta, na wypadek, gdybyśmy coś przeoczyli.

tym razem stworzyliśmy VLOOKUP w arkuszu klienta. Pierwszą rzeczą, jaką musieliśmy zrobić, to przesunąć kolumnę faktury tak, aby była to pierwsza kolumna na naszej liście transakcji (Table Array). Po wykonaniu tej czynności wstawiliśmy funkcję VLOOKUP do nowej kolumny o nazwie „nasz rekord”.

Guy skopiował funkcję, a następnie utworzył kolumnę „porównanie”. Nasze podwójne sprawdzenie potwierdziło, że klient zapłacił poniżej 100 USD na fakturze 2464. Nasza kontrola wykazała również, że klient miał całkiem możliwe zarejestrowanie numeru faktury dla faktury 2466 jako 2646.

wniosek

facet mógł teraz wrócić do klienta, aby omówić informacje, które mogły być błędnie zakleszczone i dostarczyć kopie faktur, których Klient nie miał zapisu otrzymania.

nie trzeba dodawać, że funkcja VLOOKUP zaoszczędziła facetowi i jego kolegom z pracy ogromną ilość czasu. Kierownik ds. szkoleń i rozwoju skontaktował się ze mną po szkoleniu, aby powiedzieć, że poprawa wydajności w dziale uratowała ich firmę 10s z 1000s dolarów.

obejmujemy funkcję VLOOKUP w naszych kursach Excel Stage 3. Jeśli masz do czynienia z dużymi danymi to szkolenie jest zdecydowanie dla ciebie.