Jedną z wielu możliwości programu Microsoft Excel jest możliwość porównywania dwóch list danych, identyfikowania dopasowań między listami i identyfikowania, które elementy znajdują się tylko na jednej liście. Jest to przydatne podczas porównywania danych finansowych lub sprawdzania, czy dana nazwa znajduje się w bazie danych. Możesz użyć funkcji PODAJ. POZYCJĘ, aby zidentyfikować i oznaczyć pasujące lub niepasujące rekordy lub użyć formatowania warunkowego z funkcją LICZ. JEŻELI. W poniższych krokach dowiesz się, jak używać każdego z nich, aby dopasować swoje dane.
Kroki
Metoda 1 z 2: Identyfikowanie rekordów za pomocą funkcji PODAJ. POZYCJĘ
Krok 1. Skopiuj listy danych do jednego arkusza roboczego
Program Excel może pracować z wieloma arkuszami w jednym skoroszycie lub z wieloma skoroszytami, ale porównywanie list będzie łatwiejsze, jeśli skopiujesz ich informacje do jednego arkusza.
Krok 2. Nadaj każdej pozycji listy unikalny identyfikator
Jeśli dwie listy nie mają wspólnego sposobu ich identyfikacji, może być konieczne dodanie dodatkowej kolumny do każdej listy danych, która identyfikuje ten element w programie Excel, aby mógł sprawdzić, czy element na danej liście jest powiązany z elementem na drugiej liście. Charakter tego identyfikatora będzie zależał od rodzaju danych, które próbujesz dopasować. Będziesz potrzebował identyfikatora dla każdej listy kolumn.
- W przypadku danych finansowych związanych z danym okresem, takich jak ewidencja podatkowa, może to być opis składnika aktywów, data nabycia składnika aktywów lub oba te elementy. W niektórych przypadkach wpis może być oznaczony numerem kodu; jednak jeśli ten sam system nie jest używany dla obu list, ten identyfikator może tworzyć dopasowania, w których nie ma żadnych lub ignorować dopasowania, które powinny zostać wykonane.
- W niektórych przypadkach możesz pobrać przedmioty z jednej listy i połączyć je z przedmiotami z innej listy, aby utworzyć identyfikator, taki jak opis zasobu fizycznego i rok zakupu. Aby utworzyć taki identyfikator, łączysz (dodajesz, łączysz) dane z dwóch lub więcej komórek za pomocą znaku ampersand (&). Aby połączyć opis elementu w komórce F3 z datą w komórce G3 oddzieloną spacją, należy wprowadzić formułę „=F3&” „&G3” w innej komórce w tym wierszu, na przykład E3. Jeśli chcesz uwzględnić w identyfikatorze tylko rok (ponieważ jedna lista używa pełnych dat, a druga tylko lat), możesz dołączyć funkcję ROK, wpisując '=F3&" "&ROK(G3)" w komórce E3. (Nie umieszczaj pojedynczych cudzysłowów; służą one jedynie do wskazania przykładu).
- Po utworzeniu formuły możesz skopiować ją do wszystkich pozostałych komórek kolumny identyfikatora, zaznaczając komórkę z formułą i przeciągając uchwyt wypełniania nad innymi komórkami kolumny, do której chcesz skopiować formułę. Po zwolnieniu przycisku myszy każda przeciągnięta komórka zostanie wypełniona formułą, a odwołania do komórek zostaną dostosowane do odpowiednich komórek w tym samym wierszu.
Krok 3. Standaryzuj dane tam, gdzie to możliwe
Podczas gdy umysł rozpoznaje, że "Inc." a „Incorporated” oznacza to samo, Excel nie, chyba że ponownie sformatujesz jedno lub drugie słowo. Podobnie można uznać wartości takie jak 11, 950 USD i 11 999,95 USD za wystarczająco zbliżone do dopasowania, ale program Excel nie zrobi tego, chyba że mu polecisz.
- Możesz poradzić sobie z niektórymi skrótami, takimi jak „Co” dla „Firmy” i „Inc” dla „Incorporated”, używając funkcji LEFT string do obcinania dodatkowych znaków. Inne skróty, takie jak „Assn” dla „Association”, mogą być rozwiązano poprzez ustanowienie przewodnika po stylu wprowadzania danych, a następnie napisanie programu do wyszukiwania i poprawiania niewłaściwych formatów.
- W przypadku ciągów cyfr, takich jak kody pocztowe, gdzie niektóre wpisy zawierają sufiks ZIP+4, a inne nie, możesz ponownie użyć funkcji ciągu LEWY, aby rozpoznać i dopasować tylko podstawowe kody pocztowe. Aby program Excel rozpoznawał wartości liczbowe, które są zbliżone, ale nie takie same, możesz użyć funkcji ZAOKR, aby zaokrąglić bliskie wartości do tej samej liczby i dopasować je.
- Dodatkowe spacje, takie jak wpisywanie dwóch spacji między słowami zamiast jednej, można usunąć za pomocą funkcji TRIM.
Krok 4. Utwórz kolumny dla formuły porównania
Tak jak musiałeś utworzyć kolumny dla identyfikatorów list, musisz utworzyć kolumny dla formuły, która dokona porównania za Ciebie. Potrzebujesz jednej kolumny dla każdej listy.
Będziesz chciał oznaczyć te kolumny etykietą typu „Brakujące?”
Krok 5. Wprowadź formułę porównania w każdej komórce
W przypadku formuły porównania użyjesz funkcji PODAJ. POZYCJĘ zagnieżdżonej w innej funkcji programu Excel, ISNA.
- Formuła ma postać „=ISNA(MATCH(G3, $L$3:$L$14, FALSE))”, gdzie komórka kolumny identyfikatora pierwszej listy jest porównywana z każdym identyfikatorem na drugiej liście do sprawdź, czy pasuje do jednego z nich. Jeśli nie pasuje, brakuje rekordu, a słowo „PRAWDA” zostanie wyświetlone w tej komórce. Jeśli się zgadza, rekord jest obecny i zostanie wyświetlone słowo „FAŁSZ”. (Wprowadzając formułę, nie umieszczaj załączających cudzysłowów.)
- Formułę można skopiować do pozostałych komórek kolumny w taki sam sposób, w jaki skopiowano formułę identyfikatora komórki. W takim przypadku zmienia się tylko odwołanie do komórki identyfikatora, ponieważ umieszczenie znaków dolara przed odwołaniami do wierszy i kolumn dla pierwszej i ostatniej komórki na liście identyfikatorów drugiej komórki czyni z nich odwołania bezwzględne.
- Możesz skopiować formułę porównania dla pierwszej listy do pierwszej komórki kolumny dla drugiej listy. Następnie będziesz musiał edytować odwołania do komórek, tak aby "G3" zostało zastąpione odwołaniem do pierwszej komórki identyfikatora drugiej listy, a "$L$3:$L$14" zostało zastąpione pierwszą i ostatnią komórką identyfikatora druga lista. (Pozostaw znaki dolara i dwukropek w spokoju). Następnie możesz skopiować edytowaną formułę do pozostałych komórek w wierszu porównania drugiej listy.
Krok 6. W razie potrzeby posortuj listy, aby łatwiej zobaczyć niepasujące wartości
Jeśli Twoje listy są duże, może być konieczne posortowanie ich, aby połączyć wszystkie niepasujące wartości. Instrukcje w poniższych krokach podrzędnych skonwertują formuły na wartości, aby uniknąć błędów ponownego obliczania, a jeśli listy są duże, pozwolą uniknąć długiego czasu ponownego obliczania.
- Przeciągnij myszą na wszystkie komórki na liście, aby je zaznaczyć.
- Wybierz Kopiuj z menu Edycja w programie Excel 2003 lub z grupy Schowek na wstążce Strona główna w programie Excel 2007 lub 2010.
- Wybierz polecenie Wklej specjalnie z menu Edycja w programie Excel 2003 lub przycisku rozwijanego Wklej w grupie Schowek wstążki Strona główna programu Excel 2007 lub 2010s.
- Wybierz „Wartości” z listy Wklej jako w oknie dialogowym Wklej specjalnie. Kliknij OK, aby zamknąć okno dialogowe.
- Wybierz pozycję Sortuj z menu Dane w programie Excel 2003 lub grupy Sortuj i filtruj na wstążce Dane w programie Excel 2007 lub 2010.
- Wybierz „Wiersz nagłówka” z listy „Mój zakres danych” w oknie dialogowym Sortuj według, wybierz „Brak?” (lub nazwę, którą faktycznie nadałeś nagłówkowi kolumny porównania) i kliknij OK.
- Powtórz te kroki dla drugiej listy.
Krok 7. Porównaj wizualnie niepasujące elementy, aby zobaczyć, dlaczego się nie zgadzają
Jak wspomniano wcześniej, program Excel jest przeznaczony do wyszukiwania dokładnych dopasowań danych, chyba że skonfigurujesz go do wyszukiwania przybliżonych. Twoje niedopasowanie może być tak proste, jak przypadkowa transpozycja liter lub cyfr. Może to być również coś, co wymaga niezależnej weryfikacji, takiej jak sprawdzenie, czy w pierwszej kolejności należy zgłosić wymienione aktywa.
Metoda 2 z 2: Formatowanie warunkowe z LICZ. JEŻELI
Krok 1. Skopiuj listy danych do jednego arkusza roboczego
Krok 2. Zdecyduj, na której liście chcesz wyróżnić pasujące lub niepasujące rekordy
Jeśli chcesz wyróżnić rekordy tylko na jednej liście, prawdopodobnie będziesz chciał wyróżnić rekordy unikalne dla tej listy; to znaczy rekordy, które nie pasują do rekordów na drugiej liście. Jeśli chcesz wyróżnić rekordy na obu listach, będziesz chciał wyróżnić rekordy, które pasują do siebie. Na potrzeby tego przykładu założymy, że pierwsza lista zajmuje komórki od G3 do G14, a druga lista zajmuje komórki od L3 do L14.
Krok 3. Wybierz elementy z listy, na których chcesz wyróżnić unikalne lub pasujące elementy
Jeśli chcesz wyróżnić pasujące elementy na obu listach, musisz wybrać listy pojedynczo i zastosować do każdej listy wzór porównania (opisany w następnym kroku).
Krok 4. Zastosuj odpowiednią formułę porównawczą
Aby to zrobić, musisz uzyskać dostęp do okna dialogowego Formatowanie warunkowe w swojej wersji programu Excel. W programie Excel 2003 można to zrobić, wybierając opcję Formatowanie warunkowe z menu Format, natomiast w programach Excel 2007 i 2010 kliknij przycisk Formatowanie warunkowe w grupie Style na wstążce Strona główna. Wybierz typ reguły jako „Formuła” i wprowadź formułę w polu Edytuj opis reguły.
- Jeśli chcesz wyróżnić rekordy unikalne dla pierwszej listy, formuła będzie wyglądać tak: „=COUNTIF($L$3:$L$14, G3=0)”, z zakresem komórek drugiej listy renderowanym jako wartości bezwzględne i odwołanie do pierwszej komórki pierwszej listy jako wartość względną. (Nie wpisuj bliskich cudzysłowów.)
- Jeśli chcesz wyróżnić rekordy unikalne dla drugiej listy, formuła będzie wyglądać tak: „=COUNTIF($G$3:$G$14,L3=0)", z zakresem komórek pierwszej listy renderowanym jako wartości bezwzględne i odwołanie do pierwszej komórki drugiej listy jako wartość względną. (Nie wpisuj bliskich cudzysłowów.)
- Jeśli chcesz wyróżnić rekordy na każdej liście, które znajdują się na drugiej liście, potrzebujesz dwóch formuł, jednej dla pierwszej listy i jednej dla drugiej. Formuła pierwszej listy to „= COUNTIF ($ L $ 3: $ L $ 14, G3> 0), podczas gdy formuła dla drugiej listy to COUNTIF ($ G $ 3: $ G $ 14, L3> 0). zauważono wcześniej, wybierasz pierwszą listę, aby zastosować jej formułę, a następnie wybierasz drugą listę, aby zastosować jej formułę.
- Zastosuj dowolne formatowanie, które chcesz wyróżnić oflagowane rekordy. Kliknij OK, aby zamknąć okno dialogowe.
Wideo - Korzystając z tej usługi, niektóre informacje mogą być udostępniane YouTube
Porady
- Zamiast używać odwołania do komórki z warunkową metodą formatowania LICZ. JEŻELI, możesz wprowadzić wartość do wyszukania i oflagować jedną lub więcej list dla wystąpień tej wartości.
- Aby uprościć formularze porównania, możesz utworzyć nazwy dla swojej listy, takie jak „Lista1” i „Lista2”. Następnie podczas pisania formuł te nazwy list mogą zastępować bezwzględne zakresy komórek użyte w powyższych przykładach.
