Innehåll
Vi har två tabeller (till exempel den gamla och nya versionen av prislistan), som vi behöver jämföra och snabbt hitta skillnaderna:
Det är omedelbart klart att något har lagts till i den nya prislistan (dadlar, vitlök ...), något har försvunnit (björnbär, hallon ...), priserna har ändrats för vissa varor (fikon, meloner ...). Du måste snabbt hitta och visa alla dessa ändringar.
För varje uppgift i Excel finns det nästan alltid mer än en lösning (vanligtvis 4-5). För vårt problem kan många olika tillvägagångssätt användas:
- fungera VPR (VISNING) — leta efter produktnamn från den nya prislistan i den gamla och visa det gamla priset bredvid den nya, och se sedan skillnaderna
- slå samman två listor till en och bygg sedan en pivottabell utifrån den, där skillnaderna kommer att synas tydligt
- använd Power Query-tillägget för Excel
Låt oss ta dem alla i ordning.
Metod 1. Jämföra tabeller med funktionen VLOOKUP
Om du är helt obekant med denna underbara funktion, titta först här och läs eller titta på en videohandledning om den – spara dig själv ett par år av livet.
Vanligtvis används denna funktion för att dra data från en tabell till en annan genom att matcha någon vanlig parameter. I det här fallet kommer vi att använda det för att trycka in de gamla priserna i det nya priset:
De produkter, mot vilka #N/A-felet visade sig, finns inte i den gamla listan, dvs lades till. Prisförändringar syns också tydligt.
Fördelar denna metod: enkel och tydlig, "genrens klassiska", som man säger. Fungerar i alla versioner av Excel.
Nackdelar finns också där. För att söka efter produkter som lagts till i den nya prislistan måste du göra samma procedur i motsatt riktning, dvs dra upp nya priser till det gamla priset med hjälp av VLOOKUP. Om storleken på tabellerna ändras i morgon måste formlerna justeras. Tja, och på riktigt stora bord (> 100 tusen rader), kommer all denna lycka att sakta ner ordentligt.
Metod 2: Jämföra tabeller med en pivot
Låt oss kopiera våra tabeller under varandra och lägga till en kolumn med namnet på prislistan, så att du senare kan förstå från vilken lista vilken rad:
Nu, baserat på den skapade tabellen, kommer vi att skapa en sammanfattning genom Infoga – Pivottabell (Infoga — pivottabell). Låt oss kasta ett fält Produkt till området för linjer, fält Pris till kolumnområde och fält ЦENA i intervallet:
Som du kan se kommer pivottabellen automatiskt att generera en allmän lista över alla produkter från de gamla och nya prislistorna (inga upprepningar!) och sortera produkterna i alfabetisk ordning. Du kan tydligt se de tillagda produkterna (de har inte det gamla priset), de borttagna produkterna (de har inte det nya priset) och prisändringar, om några.
Totalsummor i en sådan tabell är inte meningsfulla, och de kan inaktiveras på fliken Konstruktör – Totalsummor – Inaktivera för rader och kolumner (Design – Grand Totals).
Om priserna ändras (men inte mängden varor!), räcker det med att helt enkelt uppdatera den skapade sammanfattningen genom att högerklicka på den – refresh.
Fördelar: Detta tillvägagångssätt är en storleksordning snabbare med stora tabeller än VLOOKUP.
Nackdelar: du måste manuellt kopiera data under varandra och lägga till en kolumn med namnet på prislistan. Om storleken på borden ändras måste du göra allt om igen.
Metod 3: Jämföra tabeller med Power Query
Power Query är ett gratis tillägg för Microsoft Excel som låter dig ladda data till Excel från nästan vilken källa som helst och sedan omvandla dessa data på vilket sätt som helst. I Excel 2016 är detta tillägg redan inbyggt som standard på fliken Data (Data), och för Excel 2010-2013 måste du ladda ner det separat från Microsofts webbplats och installera det – skaffa en ny flik Strömfråga.
Innan vi laddar in våra prislistor i Power Query måste de först konverteras till smarta tabeller. För att göra detta, välj intervallet med data och tryck på kombinationen på tangentbordet ctrl+T eller välj fliken på menyfliksområdet Hem – Formatera som en tabell (Hem — Formatera som tabell). Namnen på de skapade tabellerna kan korrigeras på fliken Konstruktör (Jag lämnar standarden Tabell 1 и Tabell 2, som erhålls som standard).
Ladda det gamla priset i Power Query med knappen Från tabell/sortiment (Från tabell/intervall) från fliken Data (Datum) eller från fliken Strömfråga (beroende på version av Excel). Efter inläsning kommer vi tillbaka till Excel från Power Query med kommandot Stäng och ladda – Stäng och ladda in... (Stäng & ladda — Stäng & ladda till...):
… och välj sedan i fönstret som visas Skapa bara en anslutning (Endast anslutning).
Upprepa samma sak med den nya prislistan.
Låt oss nu skapa en tredje fråga som kommer att kombinera och jämföra data från de två föregående. För att göra detta, välj i Excel på fliken Data – Hämta data – Kombinera förfrågningar – Kombinera (Data — Hämta data — Sammanfoga frågor — Sammanfoga) eller tryck på knappen Kombinera (Sammanfoga) fliken Strömfråga.
I anslutningsfönstret väljer du våra tabeller i rullgardinslistorna, väljer kolumnerna med varornas namn i dem, och längst ner ställer du in sammanfogningsmetoden – Komplett extern (Fullständig yttre):
Efter att ha klickat på OK en tabell med tre kolumner bör visas, där i den tredje kolumnen måste du utöka innehållet i kapslade tabeller med dubbelpilen i rubriken:
Som ett resultat får vi sammanslagning av data från båda tabellerna:
Det är naturligtvis bättre att byta namn på kolumnnamnen i rubriken genom att dubbelklicka på mer begripliga:
Och nu det mest intressanta. Gå till flik Lägg till kolumn (Lägg till kolumn) och klicka på knappen Villkorlig kolumn (Villkorlig kolumn). Och sedan i fönstret som öppnas anger du flera testvillkor med motsvarande utdatavärden:
Det återstår att klicka på OK och ladda upp den resulterande rapporten till Excel med samma knapp stäng och ladda ner (Stäng & ladda) fliken Hem (Hem):
Skönhet.
Dessutom, om några förändringar inträffar i prislistorna i framtiden (rader läggs till eller tas bort, priserna ändras, etc.), kommer det att räcka att bara uppdatera våra förfrågningar med en kortkommando ctrl+andra+F5 eller med knappen Uppdatera alla (Uppdatera alla) fliken Data (Datum).
Fördelar: Kanske det vackraste och bekvämaste sättet av alla. Fungerar smart med stora bord. Kräver inga manuella redigeringar när du ändrar storlek på tabeller.
Nackdelar: Kräver att Power Query-tillägget (i Excel 2010-2013) eller Excel 2016 installeras. Kolumnnamnen i källdata får inte ändras, annars får vi felet "Kolumnen sådan och sådan hittades inte!" när du försöker uppdatera frågan.
- Hur man samlar in data från alla Excel-filer i en given mapp med hjälp av Power Query
- Hur man hittar matchningar mellan två listor i Excel
- Slår ihop två listor utan dubbletter