Inhoud
We hebben twee tabellen (bijvoorbeeld de oude en nieuwe versie van de prijslijst), die we moeten vergelijken en snel de verschillen moeten vinden:
Het is meteen duidelijk dat er iets is toegevoegd aan de nieuwe prijslijst (dadels, knoflook …), er iets is verdwenen (bramen, frambozen …), de prijzen zijn gewijzigd voor sommige goederen (vijgen, meloenen …). U moet al deze wijzigingen snel vinden en weergeven.
Voor elke taak in Excel is er bijna altijd meer dan één oplossing (meestal 4-5). Voor ons probleem kunnen veel verschillende benaderingen worden gebruikt:
- functie VPR (VERT.ZOEKEN) — zoek naar productnamen van de nieuwe prijslijst in de oude en toon de oude prijs naast de nieuwe, en vang dan de verschillen
- voeg twee lijsten samen tot één en bouw er vervolgens een draaitabel op, waar de verschillen duidelijk zichtbaar zijn
- gebruik de Power Query-invoegtoepassing voor Excel
Laten we ze allemaal op volgorde zetten.
Methode 1. Tabellen vergelijken met de functie VERT.ZOEKEN
Als je helemaal niet bekend bent met deze geweldige functie, kijk dan eerst hier en lees of bekijk er een video-tutorial over - bespaar jezelf een paar jaar leven.
Meestal wordt deze functie gebruikt om gegevens van de ene tabel naar de andere te halen door een gemeenschappelijke parameter te matchen. In dit geval gebruiken we het om de oude prijzen in de nieuwe prijs te duwen:
Die producten, waartegen de #N/A-fout bleek, staan niet in de oude lijst, dwz zijn toegevoegd. Prijswijzigingen zijn ook duidelijk zichtbaar.
VOORDELEN deze methode: eenvoudig en duidelijk, "klassieker van het genre", zoals ze zeggen. Werkt in elke versie van Excel.
NADELEN is er ook. Om producten te zoeken die aan de nieuwe prijslijst zijn toegevoegd, moet u dezelfde procedure in de tegenovergestelde richting uitvoeren, dwz nieuwe prijzen optrekken naar de oude prijs met behulp van VERT.ZOEKEN. Als morgen de afmetingen van de tabellen veranderen, dan zullen de formules aangepast moeten worden. Nou, en op echt grote tafels (> 100 duizend rijen), zal al dit geluk behoorlijk vertragen.
Methode 2: Tabellen vergelijken met een spil
Laten we onze tabellen onder elkaar kopiëren en een kolom toevoegen met de naam van de prijslijst, zodat u later uit welke lijst welke rij kunt begrijpen:
Nu, op basis van de gemaakte tabel, zullen we een samenvatting maken via Invoegen – draaitabel (Invoegen — draaitabel). Laten we een veld gooien Product naar het gebied van lijnen, veld Prijs naar kolomgebied en veld Цena in het assortiment:
Zoals je kunt zien, genereert de draaitabel automatisch een algemene lijst van alle producten uit de oude en nieuwe prijslijsten (geen herhalingen!) en sorteert de producten alfabetisch. U kunt duidelijk de toegevoegde producten zien (ze hebben niet de oude prijs), de verwijderde producten (ze hebben niet de nieuwe prijs) en eventuele prijswijzigingen.
Eindtotalen in zo'n tabel hebben geen zin, en ze kunnen worden uitgeschakeld op het tabblad Constructor – Eindtotalen – Uitschakelen voor rijen en kolommen (Ontwerp — Eindtotalen).
Als de prijzen veranderen (maar niet de hoeveelheid goederen!), dan volstaat het om het aangemaakte overzicht bij te werken door er met de rechtermuisknop op te klikken – verversen.
VOORDELEN: Deze aanpak is een orde van grootte sneller met grote tabellen dan VERT.ZOEKEN.
NADELEN: je moet de gegevens handmatig onder elkaar kopiëren en een kolom toevoegen met de naam van de prijslijst. Als de afmetingen van de tafels veranderen, moet je alles opnieuw doen.
Methode 3: Tabellen vergelijken met Power Query
Power Query is een gratis invoegtoepassing voor Microsoft Excel waarmee u gegevens vanuit bijna elke bron in Excel kunt laden en deze gegevens vervolgens op elke gewenste manier kunt transformeren. In Excel 2016 is deze invoegtoepassing al standaard ingebouwd op het tabblad Data (Gegevens), en voor Excel 2010-2013 moet je het apart downloaden van de Microsoft-website en installeren – een nieuw tabblad krijgen Power Query.
Voordat onze prijslijsten in Power Query worden geladen, moeten ze eerst worden omgezet in slimme tabellen. Selecteer hiervoor het bereik met gegevens en druk op de combinatie op het toetsenbord Ctrl+T of selecteer het tabblad op het lint Home – Opmaak als tabel (Home - Opmaken als tabel). De namen van de gemaakte tabellen kunnen worden gecorrigeerd op het tabblad Constructeur (Ik zal de standaard verlaten) Tabel 1 и Tabel 2, die standaard worden verkregen).
Laad de oude prijs in Power Query met de knop Van tafel/bereik (Van tafel/bereik) van het tabblad Data (Datum) of van het tabblad Power Query (afhankelijk van de versie van Excel). Na het laden keren we terug naar Excel vanuit Power Query met de opdracht Sluiten en laden – Sluiten en laden in… (Sluiten & laden — Sluiten & laden naar...):
… en in het venster dat verschijnt, selecteer dan Maak gewoon een verbinding (Alleen verbinding).
Herhaal hetzelfde met de nieuwe prijslijst.
Laten we nu een derde query maken die de gegevens van de vorige twee combineert en vergelijkt. Selecteer hiervoor in Excel op het tabblad Gegevens – Gegevens ophalen – Aanvragen combineren – Combineren (Gegevens — Gegevens ophalen — Query's samenvoegen — Samenvoegen) of druk op de knop Combineren (Samenvoegen) tab Power Query.
Selecteer in het join-venster onze tabellen in de vervolgkeuzelijsten, selecteer de kolommen met de namen van de goederen erin en stel onderaan de join-methode in - Compleet extern (Volledige buitenste):
Na het klikken op OK er zou een tabel met drie kolommen moeten verschijnen, waarbij u in de derde kolom de inhoud van geneste tabellen moet uitbreiden met behulp van de dubbele pijl in de koptekst:
Als resultaat krijgen we het samenvoegen van gegevens uit beide tabellen:
Het is natuurlijk beter om de kolomnamen in de kop te hernoemen door te dubbelklikken op begrijpelijkere namen:
En nu de meest interessante. Ga naar tabblad Kolom toevoegen (Kolom toevoegen) en klik op de knop Voorwaardelijke kolom (voorwaardelijke kolom). En voer vervolgens in het geopende venster verschillende testvoorwaarden in met de bijbehorende uitvoerwaarden:
Het blijft om op te klikken OK en upload het resulterende rapport naar Excel met dezelfde knop sluiten en downloaden (Sluiten & laden) tab Home (Home):
Schoonheid.
Bovendien, als er in de toekomst wijzigingen optreden in de prijslijsten (regels worden toegevoegd of verwijderd, prijzen veranderen, enz.), dan volstaat het om onze verzoeken bij te werken met een sneltoets Ctrl+anders+F5 of met de knop Ververs alles (Ververs alles) tab Data (Datum).
VOORDELEN: Misschien wel de mooiste en handigste manier van allemaal. Werkt slim met grote tafels. Vereist geen handmatige bewerkingen bij het wijzigen van het formaat van tabellen.
NADELEN: vereist dat de Power Query-invoegtoepassing (in Excel 2010-2013) of Excel 2016 is geïnstalleerd. De kolomnamen in de brongegevens mogen niet worden gewijzigd, anders krijgen we de foutmelding "Kolom die en die is niet gevonden!" wanneer u probeert de query bij te werken.
- Gegevens verzamelen uit alle Excel-bestanden in een bepaalde map met Power Query
- Hoe overeenkomsten tussen twee lijsten in Excel te vinden
- Twee lijsten samenvoegen zonder duplicaten