Innhold
Vi har to tabeller (for eksempel den gamle og nye versjonen av prislisten), som vi trenger for å sammenligne og raskt finne forskjellene:
Det er umiddelbart klart at noe har blitt lagt til i den nye prislisten (dadler, hvitløk ...), noe har forsvunnet (bjørnebær, bringebær ...), prisene har endret seg for noen varer (fiken, meloner ...). Du må raskt finne og vise alle disse endringene.
For enhver oppgave i Excel er det nesten alltid mer enn én løsning (vanligvis 4-5). For vårt problem kan mange forskjellige tilnærminger brukes:
- funksjon VPR (VISNING) — se etter produktnavn fra den nye prislisten i den gamle og vis den gamle prisen ved siden av den nye, og finn forskjellene
- slå sammen to lister til én og bygg deretter en pivottabell basert på den, hvor forskjellene vil være tydelig synlige
- bruk Power Query-tillegget for Excel
La oss ta dem alle i rekkefølge.
Metode 1. Sammenligne tabeller med funksjonen VLOOKUP
Hvis du er helt ukjent med denne fantastiske funksjonen, så se først her og les eller se en videoopplæring om den – spar deg selv for et par år av livet.
Vanligvis brukes denne funksjonen til å trekke data fra en tabell til en annen ved å matche en felles parameter. I dette tilfellet bruker vi den til å presse de gamle prisene inn i den nye prisen:
De produktene som #N/A-feilen viste seg mot, er ikke i den gamle listen, dvs. ble lagt til. Prisendringer er også godt synlige.
Pros denne metoden: enkel og tydelig, "klassiker av sjangeren", som de sier. Fungerer i alle versjoner av Excel.
Ulemper er der også. For å søke etter produkter lagt til i den nye prislisten, må du gjøre samme prosedyre i motsatt retning, dvs. trekke opp nye priser til den gamle prisen ved hjelp av VLOOKUP. Hvis størrelsen på tabellene endres i morgen, må formlene justeres. Vel, og på virkelig store bord (> 100 tusen rader), vil all denne lykken sakte anstendig.
Metode 2: Sammenligning av tabeller ved hjelp av en pivot
La oss kopiere tabellene våre under hverandre og legge til en kolonne med navnet på prislisten, slik at du senere kan forstå hvilken rad fra hvilken liste:
Nå, basert på den opprettede tabellen, vil vi lage et sammendrag gjennom Sett inn – Pivottabell (Sett inn – pivottabell). La oss kaste et felt Produkt til området av linjer, felt Pris til kolonneområde og felt Цena inn i området:
Som du kan se vil pivottabellen automatisk generere en generell liste over alle produkter fra den gamle og nye prislisten (ingen repetisjoner!) og sortere produktene alfabetisk. Du kan tydelig se de tilførte produktene (de har ikke den gamle prisen), de fjernede produktene (de har ikke den nye prisen) og prisendringer, hvis noen.
Grandtotaler i en slik tabell gir ikke mening, og de kan deaktiveres på fanen Konstruktør – Totalsummer – Deaktiver for rader og kolonner (Design – Grand Totals).
Hvis prisene endres (men ikke mengden varer!), er det nok å oppdatere det opprettede sammendraget ved å høyreklikke på det – Forfriske.
Pros: Denne tilnærmingen er en størrelsesorden raskere med store tabeller enn VLOOKUP.
Ulemper: du må manuelt kopiere dataene under hverandre og legge til en kolonne med navnet på prislisten. Hvis størrelsen på bordene endres, må du gjøre alt på nytt.
Metode 3: Sammenligning av tabeller med Power Query
Power Query er et gratis tillegg for Microsoft Excel som lar deg laste inn data til Excel fra nesten hvilken som helst kilde og deretter transformere disse dataene på hvilken som helst måte. I Excel 2016 er dette tillegget allerede innebygd som standard på fanen Data (Data), og for Excel 2010-2013 må du laste det ned separat fra Microsofts nettsted og installere det – få en ny fane Strømforespørsel.
Før du laster inn prislistene våre i Power Query, må de først konverteres til smarte tabeller. For å gjøre dette, velg området med data og trykk på kombinasjonen på tastaturet Ctrl+T eller velg fanen på båndet Hjem – Formater som en tabell (Hjem — Formater som tabell). Navnene på de opprettede tabellene kan korrigeres på fanen Constructor (Jeg forlater standarden Tabell 1 и Tabell 2, som er hentet som standard).
Last inn den gamle prisen i Power Query ved å bruke knappen Fra Tabell/Rekkevidde (Fra tabell/område) fra fanen Data (Dato) eller fra fanen Strømforespørsel (avhengig av versjonen av Excel). Etter lasting, vil vi gå tilbake til Excel fra Power Query med kommandoen Lukk og last – Lukk og last inn... (Lukk og last – Lukk og last til...):
… og velg i vinduet som vises Bare opprette en forbindelse (Kun tilkobling).
Gjenta det samme med den nye prislisten.
La oss nå lage en tredje spørring som vil kombinere og sammenligne dataene fra de to foregående. For å gjøre dette, velg i Excel på fanen Data – Hent data – Kombiner forespørsler – Kombiner (Data – Hent data – Slå sammen spørringer – Slå sammen) eller trykk på knappen Kombiner (Slå sammen) tab Strømforespørsel.
I sammenføyningsvinduet, velg våre tabeller i nedtrekkslistene, velg kolonnene med varenavnene i dem, og nederst sett inn sammenføyningsmetoden – Komplett ekstern (Full Ytre):
Etter å ha klikket på OK en tabell med tre kolonner skal vises, der i den tredje kolonnen må du utvide innholdet i nestede tabeller ved å bruke dobbeltpilen i overskriften:
Som et resultat får vi sammenslåing av data fra begge tabellene:
Det er selvfølgelig bedre å gi nytt navn til kolonnenavnene i overskriften ved å dobbeltklikke på de mer forståelige:
Og nå det mest interessante. Gå til fanen Legg til kolonne (Legg til kolonne) og klikk på knappen Betinget kolonne (Betinget kolonne). Og så i vinduet som åpnes, skriv inn flere testbetingelser med deres tilsvarende utgangsverdier:
Det gjenstår å klikke på OK og last opp den resulterende rapporten til Excel ved å bruke den samme knappen lukk og last ned (Lukk og last inn) tab Hjemprodukt (Hjem):
Skjønnhet.
Dessuten, hvis det oppstår endringer i prislistene i fremtiden (linjer legges til eller slettes, prisene endres osv.), vil det være nok bare å oppdatere forespørslene våre med en hurtigtast Ctrl+andre+F5 eller med knapp Oppdater alle (Oppdater alle) tab Data (Dato).
Pros: Kanskje den vakreste og mest praktiske måten av alle. Fungerer smart med store bord. Krever ikke manuelle endringer når du endrer størrelse på tabeller.
Ulemper: Krever Power Query-tillegget (i Excel 2010-2013) eller Excel 2016 for å være installert. Kolonnenavnene i kildedataene må ikke endres, ellers får vi feilen "Kolonne slik og slik ble ikke funnet!" når du prøver å oppdatere spørringen.
- Hvordan samle inn data fra alle Excel-filer i en gitt mappe ved hjelp av Power Query
- Hvordan finne samsvar mellom to lister i Excel
- Slå sammen to lister uten duplikater