Vermijd VERT.ZOEKEN met behulp van het gegevensmodel. U hebt dus twee tabellen die moeten worden samengevoegd met VERT.ZOEKEN voordat u een draaitabel kunt maken. Als u Excel 2013 of nieuwer op een Windows-pc heeft, kunt u dit nu eenvoudig en gemakkelijk doen.
Stel dat u een dataset heeft met product-, klant- en verkoopinformatie.
De IT-afdeling vergat sector erin te plaatsen. Hier is een opzoektabel die klant naar sector in kaart brengt. Tijd voor VERT.ZOEKEN, toch?
Het is niet nodig om VERT.ZOEKEN uit te voeren om deze gegevenssets samen te voegen als u Excel 2013 of Excel 2016 heeft. Beide versies van Excel hebben de Power Pivot-engine in de kern van Excel geïntegreerd. (U kunt dit ook doen met de Power Pivot-invoegtoepassing voor Excel 2010, maar er zijn een paar extra stappen.)
Gebruik in zowel de originele gegevensset als de opzoektabel Home, Formaat als tabel. Wijzig op het tabblad Hulpmiddelen voor tabellen de naam van de tabel van Tabel1 in iets zinvols. Ik heb gegevens en sectoren gebruikt.
Selecteer een cel in de gegevenstabel. Kies Invoegen, draaitabel. Vanaf Excel 2013 is er een extra vak Deze gegevens toevoegen aan het gegevensmodel dat u moet selecteren voordat u op OK klikt.
De lijst met draaitabelvelden wordt weergegeven met de velden uit de gegevenstabel. Kies Opbrengst. Omdat u het gegevensmodel gebruikt, verschijnt er een nieuwe regel bovenaan de lijst met Actief of Alles. Klik op Alles.
Verrassend genoeg biedt de lijst met draaitabelvelden alle andere tabellen in de werkmap. Dit is baanbrekend. U heeft nog geen VERT.ZOEKEN gedaan. Vouw de tabel Sectoren uit en kies Sector. Er gebeuren twee dingen om u te waarschuwen dat er een probleem is.
Ten eerste verschijnt de draaitabel met hetzelfde nummer in alle cellen.
Misschien is de meer subtiele waarschuwing dat er een geel vak verschijnt bovenaan de lijst met draaitabelvelden om aan te geven dat u een relatie moet maken. Kies Maken. (Als u zich in Excel 2010 of 2016 bevindt, neem dan uw geluk met Auto-Detect.)
In het dialoogvenster Relatie maken heb je vier vervolgkeuzemenu's. Kies Gegevens onder Tabel, Klant onder Kolom (buitenlands) en Sectoren onder Gerelateerde tabel. Power Pivot vult automatisch de overeenkomende kolom onder de Gerelateerde kolom (primair) in. Klik OK.
De resulterende draaitabel is een mashup van de originele gegevens en de opzoektabel. Geen VERT.ZOEKEN vereist.
Bekijk video
- Vanaf Excel 2013 biedt het dialoogvenster Draaitabel het gegevensmodel
- Dit is het codewoord voor Power Pivot Engine
- Om het gegevensmodel te gebruiken, maakt u een Ctrl + T-tabel van elke tabel in de werkmap
- Bouw een draaitafel vanaf de eerste tafel
- Wijzig in de lijst met draaitabelvelden van Actief in Alles
- Kies een veld uit de opzoektabel
- Maak de relatie of Auto-Detect
- Auto-Detect was er in 2013 niet
- Met dank aan Colin Michael en Alejandro Quiceno voor het suggereren van Power Pivot in het algemeen.
Videotranscriptie
Leer Excel van podcast, aflevering 2014 - Elimineer VERT.ZOEKEN!
Podcast dit hele boek, klik op de "i" in de rechterbovenhoek voor de afspeellijst!
Hallo, welkom terug bij de netcast, ik ben Bill Jelen, dit heet eigenlijk VERT.ZOEKEN elimineren met het datamodel! Nu verontschuldig ik me, dit is Excel 2013 en nieuwer, als u weer in Excel 2010 bent, moet u de Power Pivot-invoegtoepassing downloaden, die natuurlijk gratis is in 2010. Dus wat we hier hebben, is dat we onze hoofddataset, er is een klantveld hier, en dan heb ik een kleine tabel die klant naar sector in kaart brengt, ik moet de totale omzet per sector creëren, toch? Dit is een VERT.ZOEKEN, doe gewoon een VERT.ZOEKEN, maar goed, dankzij Excel 2013 hoeven we geen VERT.ZOEKEN te doen! Ik heb van beide een tabel gemaakt en in de Hulpmiddelen voor tabellen, Ontwerp, hernoem ik de tabellen, ik noem deze Sectors, en ik noem deze Data, om er een tabel van te maken, kies je gewoon een cel, druk je op Ctrl + T. Dus als we een aantal koppen en enkele cijfers hebben, als u op Ctrl + T drukt,ze vragen "Waar zijn de gegevens voor je tabel?", Mijn tabel heeft kopteksten, en dan noemen ze het Tabel3, jij noemt het iets anders. Oké, zo heb ik die twee tafels gemaakt, ik ga van deze tafel af, oké.
Dus om deze truc te laten werken, moeten alle gegevens in tabellen staan. We gaan naar het tabblad Invoegen, kiezen Draaitabel en hier onderaan, Voeg deze gegevens toe aan het gegevensmodel. Dit klinkt erg onschadelijk, toch? Er gaat niets boven een knipperend punt dat zegt: "Hé, je kunt er geweldige dingen mee doen!" En wat ze hier zeggen, wat ze niet proberen te zeggen, is dat … Oh, tussen haakjes, elk exemplaar van Excel 2013 heeft de Power Pivot-engine erachter. Weet je, als je Office 365 gebruikt, betaal je $ 10 per maand, en ze willen dat je $ 12 of $ 15 per maand betaalt om Power Pivot te krijgen, de extra twee of vijf dollar. Nou, hey, shh, vertel het niet, je hebt eigenlijk het grootste deel van Power Pivot al in Excel 2013. Oké, dus ik klik op OK, het duurt iets langer om het datamodel te laden, oké, maar dat is oké, en meteen weer hier,in de draaitabelvelden heb ik een lijst met alle velden. Dus ik wil zeker de omzet laten zien, maar wat er anders is, is hier met Actief en Alles. Als ik Alles kies, krijg ik alle tabellen in de werkmap. Oké, dus ik ga naar de Sectoren en ik zei dat ik sector in het gebied Rijen wilde plaatsen. Nu, in eerste instantie, zal het rapport verkeerd zijn, zie de 6,7 miljoen helemaal naar beneden, en deze gele waarschuwing hier zal zeggen dat je een relatie moet opbouwen.en deze gele waarschuwing hier zal zeggen dat je een relatie moet opbouwen.en deze gele waarschuwing hier zal zeggen dat je een relatie moet opbouwen.
Oké, in 2010 met Power Pivot, zou het gewoon, het bood AutoDetect aan, in 2013 haalden ze AutoDetect eruit en in 2016 brachten ze AutoDetect terug, oké? Ik zou je moeten laten zien hoe CREATE eruitziet, maar als ik op deze CREATE-knop klik, oh ja, dat is het, oké, goed. Dus uit onze eerste tabel Data heb ik een veld met de naam Klant, uit de gerelateerde tabel Sectoren heb ik een veld met de naam Klant, en dan klik je op OK, oké. Maar laat me je gewoon laten zien hoe cool AutoDetect is, als je toevallig in 2016 bent, dan hebben ze het door, hoe geweldig is dat, toch? U hoeft zich geen zorgen te maken over VERT.ZOEKEN en de komma valt aan het einde, als VERT.ZOEKEN uw hoofd pijn doet, zult u het datamodel geweldig vinden. Ik nam die twee tafels, voegde ze samen, zoals Access zou doen, denk ik, en creëerde een draaitabel, absoluut geweldig.Controleer dus het datamodel de volgende keer dat u een VERT.ZOEKEN tussen twee tabellen moet uitvoeren. Nou, deze en alle andere 40 tips staan in het boek. Klik op die "i" in de rechterbovenhoek. Je kunt het boek kopen, een volledige kruisverwijzing hebben naar deze hele reeks video's, heel augustus, heel september, ach, we kunnen zelfs overgaan naar oktober om het hele ding gedaan te krijgen.
Oké, samenvattend vandaag: vanaf Excel 2013 biedt het dialoogvenster Draaitabel iets dat het gegevensmodel wordt genoemd, het is het codewoord voor de Power Pivot-engine. Voordat u uw draaitabellen maakt, doet u Ctrl + T om een tabel te maken van elke werkmap, ik heb de extra tijd genomen om ze een naam te geven. Bouw een draaitabel van de eerste tabel en ga dan in de lijst met velden naar boven en verander van Actief naar Alles. Kies een veld uit de opzoektabel en het zal u waarschuwen dat u ofwel een relatie moet aanmaken, of AutoDetect, in 2013 moet u op AANMAKEN klikken. Maar het is wat, 4 klikken om het te maken, 5 als je de OK-knop meetelt, dus echt heel gemakkelijk te doen.
Oké, Colin, Michael en Alejandro Quiceno suggereerden Power Pivot in het algemeen voor de boeken, dankzij hen, bedankt voor het langskomen, we zien je de volgende keer voor een nieuwe netcast van!
Download bestand
Download het voorbeeldbestand hier: Podcast2014.xlsx
