De essentie van een selectiequery is het selecteren van rijen uit de brontabel die aan bepaalde criteria voldoen (vergelijkbaar met het gebruik van een standaardcriterium). Laten we waarden uit de brontabel selecteren met behulp van . In tegenstelling tot het gebruik ( CTRL+SHIFT+L of Gegevens/ Sorteren en filteren/ Filteren) worden de geselecteerde rijen in een aparte tabel geplaatst.
In dit artikel zullen we kijken naar de meest voorkomende vragen, bijvoorbeeld: het selecteren van tabelrijen waarvan de waarde uit een numerieke kolom binnen een bepaald bereik (interval) valt; het selecteren van rijen waarvan de datum tot een specifieke periode behoort; taken met 2 tekstcriteria en andere. Laten we beginnen met eenvoudige vragen.
1. Eén numeriek criterium (selecteer de producten waarvan de prijs hoger is dan het minimum)
voorbeeldbestand, blad Eén criterium - nummer ).
Het is noodzakelijk om in een aparte tabel alleen die records (rijen) uit de Brontabel weer te geven waarvan de prijs hoger is dan 25.
U kunt dit en de daaropvolgende problemen eenvoudig oplossen met . Om dit te doen, selecteert u de kopteksten van de Brontabel en klikt u op CTRL+SHIFT+L. Maak een keuze uit de vervolgkeuzelijst naast de kop Prijzen Numerieke filters..., stel vervolgens de vereiste filtervoorwaarden in en klik op OK.
Records die aan de selectievoorwaarden voldoen, worden weergegeven.
Een andere aanpak is het gebruik van . De geselecteerde rijen worden daarentegen in een aparte tabel geplaatst - een unieke tabel, die bijvoorbeeld kan worden opgemaakt in een andere stijl dan de Brontabel of er kunnen andere wijzigingen worden aangebracht.
We plaatsen het criterium (minimumprijs) in de cel E6 , tabel voor gefilterde gegevens - binnen bereik D10:E19 .
Laten we nu het bereik selecteren D11:D19 (Kolom Product) en voer in:
INDEX(A11:A19;
KLEIN(ALS($E$6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-RIJ($B$10))
In plaats van BINNENKOMEN druk op de sneltoets CTRL+SHIFT+ENTER(de matrixformule zal zijn).
E11:E19 (kolom Prijs) waar we een soortgelijke zullen invoeren:
INDEX(B11:B19;
KLEIN(ALS($E$6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-RIJ($B$10))
Als gevolg hiervan krijgen we een nieuwe tabel die alleen producten bevat waarvan de prijzen niet lager zijn dan die aangegeven in de cel E6 .
Om de dynamiek van de ontvangen Sample Request te laten zien, doen we mee E6 waarde 55. Er worden slechts 2 records in de nieuwe tabel opgenomen.
Als u een nieuw product met een Prijs van 80 aan de Brontabel toevoegt, wordt er automatisch een nieuw record aan de nieuwe tabel toegevoegd.
Opmerking. U kunt ook en gebruiken om gefilterde gegevens weer te geven. De keuze voor een specifiek hulpmiddel hangt af van de taak waarmee de gebruiker wordt geconfronteerd.
Als u zich niet op uw gemak voelt bij het gebruik matrixformule, die meerdere waarden retourneert, kunt u een andere aanpak gebruiken, die wordt besproken in de onderstaande paragrafen: 5.a, 7, 10 en 11. In deze gevallen is .
2. Twee numerieke criteria (selecteer de producten waarvan de prijs binnen het bereik valt)
Laat er een begintabel zijn met een lijst met producten en prijzen (zie. voorbeeldbestand, bladNummerbereik).
We plaatsen de criteria (onder- en bovengrens voor de prijs) in het assortiment E5:E6 .
Die. als de productprijs binnen het opgegeven interval valt, verschijnt een dergelijk record in de nieuwe tabel met gefilterde gegevens.
In tegenstelling tot de vorige taak zullen we er twee maken: Producten en Prijzen (je kunt het zonder doen, maar ze zijn handig bij het schrijven van formules). De overeenkomstige formules moeten er uitzien als in Name Manager ( Formules/Gedefinieerde namen/Naambeheer) als volgt (zie onderstaande afbeelding).
Laten we nu het bereik selecteren D11:D19 en daarin voeren we in:
INDEX(Producten;
MINST(
ALS(($E$5<=Цены)*($E$6>=Prijzen);ROW(Prijzen);"");
In plaats van BINNENKOMEN druk op de sneltoets CTRL+SHIFT+ENTER.
We zullen dezelfde manipulaties uitvoeren met het bereik E11:E19 waar we een soortgelijke zullen introduceren:
INDEX(Prijzen;
MINST(
ALS(($E$5<=Цены)*($E$6>=Prijzen);ROW(Prijzen);"");
LIJN(Prijzen)-RIJ($B$10))-RIJ($B$10))
Als gevolg hiervan krijgen we een nieuwe tabel die alleen producten bevat waarvan de prijzen binnen het interval vallen dat in de cellen is opgegeven E5 En E6 .
Om de dynamiek van het ontvangen Rapport (Sampling Request) te laten zien, gaan we in E6 waarde 65. Een ander record uit de Brontabel dat aan het nieuwe criterium voldoet, wordt aan de nieuwe tabel toegevoegd.
Als u een nieuw product met een prijs in het bereik van 25 tot 65 toevoegt aan de brontabel, wordt er een nieuw record aan de nieuwe tabel toegevoegd.
Het voorbeeldbestand bevat ook matrixformules met foutafhandeling wanneer de kolom Prijs een foutwaarde bevat, bijvoorbeeld #DIV/0! (zie blad Foutafhandeling).
De volgende problemen worden op een vergelijkbare manier opgelost, dus we zullen ze niet zo gedetailleerd bekijken.
3. Eén criteriumdatum (selecteer de producten waarvan de leveringsdatum overeenkomt met de opgegeven datum)
voorbeeldbestand, bladEén criterium: datum).
Om rijen te selecteren, worden matrixformules gebruikt die vergelijkbaar zijn met Taak 1 (in plaats van het criterium<= используется =):
=INDEX(A12:A20,KLEIN(IF($E$6=B12:B20,RIJ(B12:B20),"");RIJ(B12:B20)-RIJ($B$11))-RIJ($B$11) )
INDEX(B12:B20,KLEIN(IF($E$6=B12:B20,RIJ(B12:B20),"");RIJ(B12:B20)-RIJ($B$11))-RIJ($B$11) )
4. Twee datumcriteria (selecteer de producten waarvan de leveringsdatum binnen het bereik valt)
Laat er een Brontabel zijn met een lijst van Producten en Leveringsdata (zie. voorbeeldbestand, bladDatumbereik).
Houd er rekening mee dat de kolom Datum NIET GESORTEERD is.
Oplossing 1: Je kunt gebruiken.
Typ cel D12 matrixformule:
INDEX(€12:€20;
GROOTSTE(($E$6<=$B$12:$B$20)*($E$7>=$B$12:$B$20)*(RIJ($B$12:$B$20)-RIJ($B$11));
$J$12-RIJ(A12)+RIJ($B$11)+1))
Opmerking: Na het invoeren van de formule moet u in plaats van de ENTER-toets op de toetsencombinatie CTRL+SHIFT+ENTER drukken. Deze sneltoets wordt gebruikt om matrixformules in te voeren.
Kopieer de matrixformule naar het gewenste aantal cellen. De formule retourneert alleen die waarden voor producten die binnen het opgegeven datumbereik zijn geleverd. De overige cellen bevatten #NUM fouten. Fouten in voorbeeldbestand (Blad 4. Datumbereik) .
Voor datums in kolom E moet een soortgelijke formule worden ingevoerd.
In een cel J12 Het aantal rijen van de brontabel dat aan de criteria voldoet, wordt berekend:
AANTAL.ALS(B12:B20;">="&$E$6;B12:B20;"<="&$E$7)
De rijen in de brontabel die aan de criteria voldoen zijn .
Oplossing2: Om rijen te selecteren, kunt u matrixformules gebruiken die vergelijkbaar zijn met Taak 2 (d.w.z.):
=INDEX(A12:A20,SMALL(IF(($E$6<=B12:B20)*($E$7>=B12:B20);RIJ(B12:B20);"");RIJ(B12:B20)-RIJ($B$11))-RIJ($B$11))
INDEX(B12:B20,SMALL(IF(($E$6<=B12:B20)*($E$7>=B12:B20);RIJ(B12:B20);"");RIJ(B12:B20)-RIJ($B$11))-RIJ($B$11))
Om de eerste formule in te voeren, selecteert u het celbereik G12:G20 . Na het invoeren van de formule moet u in plaats van de ENTER-toets op de toetsencombinatie CTRL+SHIFT+ENTER drukken.
Oplossing3: Als de kolom Datum GESORTEERD is, hoeft u geen matrixformules te gebruiken.
Eerst moet u de eerste en laatste posities berekenen van de rijen die aan de criteria voldoen. Voer vervolgens de regels uit.
Dit voorbeeld laat nogmaals duidelijk zien hoe gemakkelijk het is om formules te schrijven.
5. Eén criteriumdatum (selecteer de producten waarvan de leveringsdatum niet eerder/niet later is dan de opgegeven datum)
Laat er een Brontabel zijn met een lijst van Producten en Leveringsdata (zie. voorbeeldbestand, blad Eén criterium - Datum (niet later) ).
Om rijen te selecteren waarvan de datum niet eerder is (inclusief de datum zelf), wordt een matrixformule gebruikt:
=INDEX(A12:A20,KLEIN(IF($E$7<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))
Het voorbeeldbestand bevat ook formules voor de voorwaarden: Not before (exclusief); Niet later (inclusief); Niet later (exclusief).
7. Eén tekstcriterium (selecteer producten van een bepaald type)
Laat er een begintabel zijn met een lijst met producten en prijzen (zie. voorbeeldbestand, bladEén criterium: tekst).
8. Twee tekstcriteria (selecteer producten van een bepaald type, geleverd in een bepaalde maand)
Laat er een begintabel zijn met een lijst met producten en prijzen (zie. voorbeeldbestand, blad 2 criteria - tekst (I) ).
INDEX($A$11:$A$19;
KLEIN(IF(($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19);RIJ($A$11:$A$19)-RIJ($A$10); 30);RIJ(INDIRECT("A1:A"&RIJ($A$11:$A$19)))))
Uitdrukking ($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19) specificeert beide voorwaarden (Product en Maand).
Uitdrukking RIJ(INDIRECT("A1:A"&RIJ($A$11:$A$19))) vormen (1:2:3:4:5:6:7:8:9), d.w.z. rijnummers in de tabel.
9. Twee tekstcriteria (selecteer producten van bepaalde typen)
Laat er een begintabel zijn met een lijst met producten en prijzen (zie. voorbeeldbestand, blad2 criteria - tekst (OF)).
In tegenstelling tot probleem 7 selecteren we rijen met 2 soorten goederen ().
Er wordt een matrixformule gebruikt om rijen te selecteren:
INDEX(€11:€19;
GROOT((($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19))*(RIJ($A$11:$A$19)-RIJ($A$10) COUNTIF($A$11:$A$19,$E$6)+COUNTIF($A$11:$A$19,$E$7)-RIJ($A$11:A11)+1))
Voorwaarde ($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19) garandeert dat uit de gele cellen (Product2 en Product3) alleen producten van de opgegeven typen worden geselecteerd. Het + teken (optelling) wordt gebruikt om te specificeren (er moet aan minimaal 1 criterium worden voldaan).
De bovenstaande expressie retourneert de array (0:0:0:0:1:1:1:0:0). Vermenigvuldig het met de uitdrukking RIJ($A$11:$A$19)-RIJ($A$10), d.w.z. tot (1:2:3:4:5:6:7:8:9), krijgen we een array van posities (tabelrijnummers) die aan de criteria voldoen. In ons geval zal het een array zijn (0:0:0:0:5:6:7:0:0).
Als voorbeeld geven we oplossingen voor het volgende probleem: Selecteer Producten waarvan de prijs binnen een bepaald bereik ligt en een bepaald aantal keren of vaker wordt herhaald.
Laten we de tabel met goederenzendingen als eerste nemen.
Laten we aannemen dat we geïnteresseerd zijn in hoeveel en welke partijen goederen zijn geleverd tegen een prijs van 1000 roebel. tot 2000 wrijven. (criterium 1). Bovendien moeten er minimaal 3 kavels zijn met dezelfde prijs (criterium 2).
De oplossing is een matrixformule:
KLEIN(RIJ($A$14:$A$27)*($C$14:$C$27>=$B$7)*($C$14:$C$27<=$C$7)*($D$14:$D$27>=$B$10);F14+($G$8-$G$9))
Deze formule retourneert de rijnummers die aan beide criteria voldoen.
Formule =SOMPRODUCT(($C$14:$C$27>=$B$7)*($C$14:$C$27<=$C$7)*($D$14:$D$27>=$B$10)) telt het aantal rijen dat aan de criteria voldoet.
11. Gebruik de criteriumwaarde (Any) of (All)
IN voorbeeldbestand op blad "11. Criterium Elke of (Alle)" Deze versie van het criterium is geïmplementeerd.
De formule moet in dit geval de functie IF() bevatten. Als de waarde (Alle) is geselecteerd, wordt een formule gebruikt om waarden weer te geven zonder rekening te houden met dit criterium. Als een andere waarde wordt geselecteerd, werkt het criterium zoals gewoonlijk.
IF($C$8="(Alles)";
KLEIN((RIJ($B$13:$B$26)-RIJ($B$12))*($D$13:$D$26>=$D$8);F13+($G$6-$G$7));
KLEIN((RIJ($B$13:$B$26)-RIJ($B$12))*($D$13:$D$26>=$D$8)*($C$13:$C$26=$C$8) ;F13+($G$6-$G$7)))
De rest van de formule is vergelijkbaar met die hierboven besproken.
Methode één: Een geavanceerd autofilter gebruiken
Op een Excel-werkblad moet u een gebied selecteren tussen de gegevens die u wilt selecteren. Klik op het tabblad “Home” op “Sorteren en filteren” (bevindt zich in het instellingenblok “Bewerken”). Klik vervolgens op het filter.
U kunt het anders doen: ga na het selecteren van het gebied naar het tabblad "Gegevens" en klik op "Filter", gelegen in de groep "Sorteren en filteren".
Wanneer deze acties zijn voltooid, moeten pictogrammen in de tabelkop verschijnen om te beginnen met filteren. Ze worden met de punt naar beneden weergegeven in kleine driehoekjes aan de rechterrand van de cellen. Klik op dit icoon aan het begin van de kolom waarvoor u een selectie gaat maken. Open het menu, ga naar “Tekstfilters” en selecteer “Aangepast filter...”.
Het venster Aangepast filteren zou nu geactiveerd moeten zijn. Daarin stelt u de beperking in waarmee de selectie wordt uitgevoerd. U kunt een van de vijf voorgestelde typen voorwaarden kiezen: gelijk, niet gelijk, groter dan, groter dan of gelijk aan, kleiner dan.
Na het filteren blijven alleen die regels over waarin het omzetbedrag groter is dan 10.000 (als voorbeeld).
In dezelfde kolom kunt u een tweede voorwaarde toevoegen. U moet opnieuw terugkeren naar het aangepaste filtervenster en een andere selectielimiet instellen in het onderste gedeelte. Zet de schakelaar op “Minder” en voer “15000” in het veld aan de rechterkant in.
De tabel bevat alleen die rijen waarin het omzetbedrag niet minder is dan 10.000, maar niet meer dan 15.000.
In andere kolommen is de selectie op een vergelijkbare manier geconfigureerd. Klik in de gewenste kolom op het filterpictogram en klik vervolgens achtereenvolgens op de lijstitems “Filteren op datum” en “Aangepast filter”.
Het venster Aangepast AutoFilter zou moeten openen. Voer bijvoorbeeld een selectie van resultaten uit in de tabel van 4 mei tot en met 6 mei 2016. Klik op “Na of gelijk” en voer in het rechterveld de waarde “05/04/2016” in. Zet in het onderste blok de schakelaar in de stand “Before or equal” en voer in het rechterveld “05/06/2016” in. Laat de voorin de standaardpositie staan, dat wil zeggen “AND”. Klik op OK om filtering toe te passen.
De lijst zou nu nog verder ingekort moeten worden, omdat er alleen rijen overblijven waarin het omzetbedrag varieert van 10.000 tot 15.000, en dit voor de periode van 05/04 tot en met 05/06/2016.
In een van de kolommen kunt u desgewenst de filtering opnieuw instellen. U kunt dit bijvoorbeeld doen voor opbrengstwaarden. Klik op het autofilterpictogram in de overeenkomstige kolom. Selecteer "Verwijder filter".
Selectie op omzetbedrag wordt uitgeschakeld en alleen selectie op datums blijft bestaan (van 05/04/2016 tot 05/06/2016).
Er is nog een kolom in de tabel met de naam "Naam". Het bevat gegevens in tekstformaat. Met deze waarden kunt u ook een steekproef vormen. Klik in de kolomnaam op het filterpictogram. Ga naar “Tekstfilters” en vervolgens naar “Aangepast filter...”.
Het aangepaste filtervenster wordt weer geopend, waarin u een selectie kunt maken op bijvoorbeeld de namen “Vlees” en “Aardappelen”. In het eerste blok moet je de schakelaar op “Gelijk” zetten en “Aardappelen” in het veld rechts ervan invoeren. Zet de schakelaar van het onderste blok in de positie "Gelijk", en in het veld er tegenover - "Vlees". Nu moet u de voorop de “OR”-positie zetten. Klik op OK.
In het nieuwe voorbeeld zijn de beperkingen ingesteld op datum (van 05/04/2016 tot 05/06/2016) en op naam (Vlees en Aardappelen). Er zijn geen beperkingen alleen op de hoogte van de inkomsten.
U kunt het filter volledig verwijderen en dit gebeurt op dezelfde manier als waarmee u het filter hebt ingesteld. Om de filtering opnieuw in te stellen, klikt u op het tabblad “Gegevens” op “Filter” in de groep “Sorteren en filteren”.
Bij de tweede optie kunt u naar het tabblad “Home” gaan en bij “Bewerken” op “Sorteren en filteren” klikken. Klik vervolgens op “Filter”.
Als u een van deze methoden gebruikt, wordt de tabel verwijderd en worden de selectieresultaten gewist. Dat wil zeggen dat de tabel alle eerder ingevoerde gegevens weergeeft.
Methode twee: Een matrixformule gebruiken
Maak op hetzelfde Excel-werkblad een lege tabel met dezelfde kolomnamen in de koptekst als in de bron.
Alle lege cellen moeten in de eerste kolom van de nieuwe tabel worden geselecteerd. Plaats de cursor in de formulebalk om de formule in te voeren - =INDEX(A2:A29,SMALL(IF(15000)
Om de formule toe te passen, drukt u op de toetsen Ctrl+Shift+Enter.
Selecteer de tweede kolom met datums en plaats de cursor in de formulebalk om - =INDEX(B2:B29,SMALL(IF(15000)) in te voeren
Voer op dezelfde manier de volgende formule in de omzetkolom in: =INDEX(C2:C29,SMALL(IF(15000
Er wordt een opmaakvenster geopend waarin u het tabblad "Nummer" moet selecteren. In "Getalformaten" selecteert u "Datum". In het rechterdeel van het venster kunt u desgewenst het type datum selecteren dat wordt weergegeven en als alle instellingen zijn uitgevoerd, klikt u op OK.
Nu zal alles mooi zijn en zal de datum correct worden weergegeven. Als de cellen de waarde “#NUMBER!” weergeven, moet u voorwaardelijke opmaak toepassen. Alle tabelcellen moeten worden geselecteerd (behalve de koptekst) en klik in het tabblad "Home" op "Voorwaardelijke opmaak" (in het gereedschapsblok "Stijlen"). Er verschijnt een lijst waarin u “Een regel maken...” selecteert.
Selecteer de regels "Formateer alleen cellen die bevatten", en selecteer in het eerste veld, gelegen onder de regel "Formateer alleen cellen waarvoor aan de volgende voorwaarde is voldaan", "Fouten" en klik op "Formatteren...".
Er wordt een opmaakvenster geopend, waarin u naar "Lettertype" gaat en de witte kleur selecteert. Klik op OK.
Een kant-en-klaar voorbeeld volgens de opgegeven limiet verschijnt voor u, en het staat allemaal in een aparte tabel.
Derde methode: Bemonstering op basis van verschillende omstandigheden met behulp van een formule
Randvoorwaarden voor het monster moeten in een aparte kolom worden ingevuld.
Selecteer één voor één de lege kolommen van de nieuwe tabel om de vereiste drie formules erin in te voeren. In de eerste kolom typt u - =INDEX(A2:A29,SMALL(IF(($D$2=C2:C29),ROW(C2:C29);"");ROW(C2:C29)-ROW($C$1 ) )-RIJ($C$1)). Voer vervolgens dezelfde formules in de kolommen in, wijzig alleen de coördinaten na de naam van de INDEX-operator in de coördinaten die nodig zijn en overeenkomen met bepaalde kolommen. Alles is vergelijkbaar met de vorige methode. Vergeet niet elke keer dat u invoert de toetsencombinatie Ctrl+Shift+Enter in te drukken.
Als u de bemonsteringsgrenzen moet wijzigen, kunt u eenvoudigweg de grensgetallen in de kolom Voorwaarden wijzigen. Het selectieresultaat wordt dan automatisch gewijzigd.
Vierde methode: willekeurige bemonstering
Aan de linkerkant van de tabel moet u één kolom overslaan en in de cel van de volgende kolom de formule - =RAND() invoeren om een willekeurig getal weer te geven. Om het te activeren, drukt u op ENTER.
Als u een hele kolom met willekeurige getallen wilt maken, plaatst u de cursor in de rechter benedenhoek van de cel die de formule bevat. Er zou een vulmarkering moeten verschijnen, die naar beneden moet worden gesleept door de linkermuisknop ingedrukt te houden. Dit gebeurt parallel aan de tabel met de gegevens en tot aan het einde.
Het celbereik bevat de RAND-formule, maar u hoeft niet met pure waarden te werken. Kopieer naar de lege kolom aan de rechterkant en selecteer een celbereik met willekeurige getallen. Klik op het tabblad “Start” op “Kopiëren”.
Selecteer een lege kolom en klik met de rechtermuisknop om het contextmenu te openen. Selecteer in de gereedschapsgroep “Invoegopties” “Waarden” (weergegeven als een pictogram met cijfers).
Klik op het tabblad “Start” op “Sorteren en filteren” en vervolgens op “Aangepast sorteren”.
Vink het vakje aan naast de optie 'Mijn gegevens bevatten headers'. Geef in de regel "Sorteren op" de naam aan van de kolom waarin de gekopieerde willekeurige getalwaarden zich bevinden. In de regel “Sorteren” blijven de instellingen op hun standaardwaarden staan. Selecteer in de regel "Order" de optie "Oplopend" of "Aflopend". Klik op OK.
De tabelwaarden moeten in oplopende of aflopende volgorde van willekeurige getallen worden gerangschikt. Een willekeurig aantal eerste rijen uit de tabel kan worden genomen en als resultaat van een willekeurige steekproef worden beschouwd.
Een macro is een reeks acties die wordt vastgelegd en opgeslagen voor later gebruik. Een opgeslagen macro kan worden afgespeeld met een speciaal commando. Met andere woorden: u kunt uw acties in een macro opnemen, deze opslaan en andere gebruikers vervolgens met een simpele toetsaanslag de acties laten afspelen die in de macro zijn opgeslagen. Dit is vooral handig bij het distribueren van draaitabelrapporten.
Stel dat u uw klanten de mogelijkheid wilt geven draaitabelrapporten te groeperen op maand, kwartaal en jaar. Technisch gezien kan iedereen het groeperingsproces uitvoeren, maar sommige van uw klanten zullen niet de behoefte voelen om het te begrijpen. In dat geval kunt u één groeperingsmacro per maand, een andere per kwartaal en een derde per jaar opnemen. Maak vervolgens drie knoppen: één voor elke macro. Uw klanten die nieuw zijn bij draaitabellen hoeven dan alleen maar op een knop te klikken om het draaitabelrapport correct te groeperen.
Het belangrijkste voordeel van het gebruik van macro's in draaitabelrapporten is dat clients snel bewerkingen op draaitabellen kunnen uitvoeren die ze normaal gesproken niet zouden kunnen uitvoeren. Hierdoor neemt de efficiëntie van het analyseren van de verstrekte gegevens aanzienlijk toe.
Download de notitie in of formaat, download met voorbeelden (binnenin bevindt zich een Excel-bestand met macro's; het beleid van de provider staat het rechtstreeks uploaden van een bestand van dit formaat naar de site niet toe).
Neem een macro op
Kijk eens naar de samenvattende tabel in Fig. 1. U kunt deze draaitabel vernieuwen door er met de rechtermuisknop in te klikken en te selecteren Update. Als u acties als macro hebt vastgelegd tijdens het bijwerken van de draaitabel, kunt u of iemand anders deze acties opnieuw afspelen en de draaitabel bijwerken als gevolg van het uitvoeren van de macro.
Rijst. 1. Door de acties vast te leggen tijdens het bijwerken van deze draaitabel, kunt u de gegevens in de toekomst bijwerken als gevolg van het uitvoeren van de macro
De eerste stap bij het opnemen van een macro is het oproepen van het dialoogvenster Neem een macro op. Ga naar het tabblad Ontwikkelaar lint en klik op de knop Neem een macro op. (Als u het tabblad op het lint niet kunt vinden Ontwikkelaar, selecteer tabblad Bestand en klik op de knop Opties. In het dialoogvenster dat verschijnt Excel-opties categorie selecteren Pas het lint aan en vink het vakje aan in de lijst aan de rechterkant Ontwikkelaar. Als gevolg hiervan verschijnt er een tabblad op het lint Ontwikkelaar.) Een alternatieve manier om te beginnen met het opnemen van een macro is door op de knop te klikken (Fig. 2).
In het dialoogvenster Neem een macro op Voer de volgende macro-informatie in (Figuur 3):
Naammacro. De naam moet de acties beschrijven die door de macro worden uitgevoerd. De naam moet beginnen met een letter of een onderstrepingsteken; mag geen spatie of andere illegale tekens bevatten; mag niet hetzelfde zijn als de ingebouwde naam van Excel of de naam van een ander object in de werkmap.
Combinatiesleutels. In dit veld kunt u elke letter invoeren. Het wordt onderdeel van de sneltoets die wordt gebruikt om de macro af te spelen. Het is niet nodig om een toetscombinatie op te geven. Standaard wordt alleen Ctrl aangeboden als begin van een combinatie. Als je wilt dat de combinatie ook Shift bevat, typ je de letter in het venster terwijl je de Shift-toets ingedrukt houdt
ReddenV. Dit is waar de macro wordt opgeslagen. Als u het draaitabelrapport naar andere gebruikers wilt distribueren, selecteert u de optie Ditboek. Met Excel kunt u ook een macro opslaan Nieuw boek of binnen Persoonlijk macroboek.
Beschrijving. In dit veld wordt een beschrijving van de aan te maken macro ingevoerd.
Rijst. 3. Vensteraanpassing Neem een macro op
Omdat de macro de draaitabel bijwerkt, selecteert u de naam Gegevensupdate. U kunt ook de sneltoets Ctrl+Shift+Q aan een macro toewijzen. Houd er rekening mee dat zodra u een macro hebt gemaakt, u deze sneltoets zult gebruiken om deze uit te voeren. Selecteer de optie voor de macro-opslaglocatie Dit boek en klik OK.
Nadat u in het dialoogvenster hebt geklikt Neem een macro op op de knop OK De macro-opname begint. Op dit punt wordt alles wat u in Excel doet, vastgelegd.
Klik met de rechtermuisknop in het draaitabelgebied en selecteer Update(zoals in figuur 1, maar in de macro-opnamemodus). Na het bijwerken van de draaitabel kunt u het macro-opnameproces stoppen met behulp van de knop Stop met opnemen tabbladen Ontwikkelaar. Of klik opnieuw op de knop zoals weergegeven in Afb. 2.
Je hebt zojuist je eerste macro opgenomen. Nu kunt u de macro uitvoeren met de toetsencombinatie Ctrl+Shift+Q.
Macro-beveiligingswaarschuwing. Opgemerkt moet worden dat als macro's door de gebruiker worden vastgelegd, deze zonder enige beperking van het beveiligingssubsysteem zullen worden uitgevoerd. Als u echter een werkmap distribueert die macro's bevat, moet u andere gebruikers de mogelijkheid bieden om ervoor te zorgen dat er geen risico bestaat bij het openen van de werkbestanden en dat het uitvoeren van de macro's het systeem niet met virussen infecteert. In het bijzonder zult u onmiddellijk merken dat het voorbeeldbestand dat in dit hoofdstuk wordt gebruikt niet volledig zal functioneren, tenzij u Excel specifiek toestaat er macro's in uit te voeren.
De eenvoudigste manier om macrobeveiliging te garanderen is door een vertrouwde locatie te maken: een map waarin alleen "vertrouwde" werkmappen worden geplaatst die geen virussen bevatten. Met een vertrouwde locatie kunnen u en uw clients macro's uitvoeren op werkmappen zonder enige beveiligingsbeperkingen (dit gedrag blijft bestaan zolang de werkmappen zich op een vertrouwde locatie bevinden).
Volg deze stappen om een vertrouwde locatie in te stellen:
Selecteer een linttabblad Ontwikkelaar en klik op de knop Macro-beveiliging. Er verschijnt een dialoogvenster op het scherm Vertrouwenscentrum.
Klik op de knop Voeg een nieuwe locatie toe.
Klik op de knop Beoordeling om een map op te geven voor werkbestanden die u vertrouwt.
Zodra u een vertrouwde locatie opgeeft, zullen alle werkmappen op die locatie standaard willekeurige macro's uitvoeren.
In Excel 2013 is het beveiligingsmodel verbeterd. Nu worden de werkmapbestanden onthouden die voorheen “vertrouwd” waren, d.w.z. nadat u de Excel-werkmap hebt geopend en op de knop hebt geklikt Inclusief inhoud Excel onthoudt deze status. Hierdoor valt dit boek in de categorie van vertrouwde en worden er bij de volgende keer openen geen onnodige vragen gesteld.
Een gebruikersinterface maken met behulp van formulierbesturingselementen
Het uitvoeren van een macro met de toetsencombinatie Ctrl+Shift+Q is handig als er slechts één macro in een draaitabelrapport staat. (Gebruikers moeten deze combinatie ook kennen.) Maar stel dat u uw klanten wilt voorzien van verschillende macro's die verschillende acties uitvoeren. In dit geval moet u klanten een duidelijke en eenvoudige manier bieden om elke macro uit te voeren, zonder toetscombinaties te hoeven onthouden. De ideale oplossing is een eenvoudige gebruikersinterface met een reeks bedieningselementen, zoals knoppen, schuifbalken en andere bedieningselementen waarmee u macro's kunt uitvoeren met muisklikken.
Excel biedt u een reeks hulpmiddelen die zijn ontworpen om gebruikersinterfaces rechtstreeks in uw spreadsheet te creëren. Deze hulpmiddelen worden formulierbesturingselementen genoemd. Het basisidee is dat u een formulierbesturingselement in een spreadsheet kunt plaatsen en daaraan een macro kunt toewijzen die eerder is opgenomen. Eenmaal toegewezen aan een besturingselement, wordt de macro gestart door op dat element te klikken.
Formulierbesturingselementen zijn te vinden in de groep Formulierbesturingselementen lint tabbladen Ontwikkelaar. Om het controlepalet te openen, klikt u op de knop in deze groep Invoegen(Afb. 4).
Rijst. 4. Formuliercontrole Knop
Let op: naast de formulierbesturingselementen bevat het palet ook ActiveX-besturingselementen. Hoewel ze op elkaar lijken, zijn het programmatisch totaal verschillende objecten. Formulierbesturingselementen met hun beperkte mogelijkheden en eenvoudige instellingen zijn ze speciaal ontworpen om op werkbladen te worden geplaatst. Tegelijkertijd ActiveX-besturingselementen voornamelijk gebruikt in aangepaste formulieren. Maak er een gewoonte van om alleen formulierbesturingselementen op uw werkbladen te plaatsen.
U moet de bedieningselementen selecteren die het beste bij de taak passen. In dit voorbeeld moeten clients de draaitabel kunnen bijwerken door op een knop te klikken. Klik op het besturingselement Knop, beweeg de muisaanwijzer naar de plaats op het werkblad waar u de knop wilt plaatsen en klik.
Zodra u de knop in de tabel plaatst, wordt een dialoogvenster geopend Macro toewijzen voorwerp(Afb. 5). Selecteer de gewenste macro (in ons geval - Gegevensupdate, eerder opgenomen) en klik op de knop OK.
Rijst. 5. Selecteer de macro die u aan de knop wilt toewijzen en klik op de knop OK. In dit geval moet u de macro gebruiken Gegevensupdate
Zodra u alle noodzakelijke besturingselementen in uw draaitabelrapport hebt geplaatst, kunt u de tabel opmaken om een basisinterface te maken. In afb. Figuur 6 toont het draaitabelrapport na opmaak.
Een opgenomen macro bewerken
Wanneer u een macro opneemt, maakt Excel een module waarin de acties die u hebt uitgevoerd, worden opgeslagen. Alle opgenomen acties worden weergegeven door regels VBA-code waaruit de macro bestaat. U kunt allerlei functionaliteit aan uw draaitabelrapporten toevoegen door uw VBA-code aan te passen zodat u de gewenste resultaten krijgt. Om het gemakkelijker te maken om te begrijpen hoe dit allemaal werkt, gaan we een nieuwe macro maken die de eerste vijf klantrecords weergeeft. Ga naar het tabblad Ontwikkelaar en klik op de knop Neem een macro op. Het dialoogvenster weergegeven in figuur wordt geopend. 7. Geef de macro die u maakt een naam EerstNklanten en geef de opslaglocatie aan Dit boek. Klik OK om te beginnen met het opnemen van de macro.
Zodra u begint met opnemen, klikt u op de pijl naast het veld Klantnaam, selecteer Filter op waarde en optie Eerste 10(Afb. 8a). In het dialoogvenster dat verschijnt, stelt u de instellingen in zoals weergegeven in Afb. 8b. Deze instellingen geven aan dat de gegevens van de vijf klanten met de beste verkoopvolumes moeten worden weergegeven. Klik OK.
Rijst. 8. Selecteer filter (a) en pas de opties aan (b) om de vijf grootste klanten op basis van omzet weer te geven
Nadat u met succes alle stappen heeft vastgelegd die nodig zijn om de top vijf verkoopleads te extraheren, gaat u naar het tabblad Ontwikkelaar en klik op de knop Stop met opnemen.
U hebt nu een macro die de draaitabel filtert om de top vijf verkoopaccounts te extraheren. Het is noodzakelijk om de macro te laten reageren op de status van de schuifbalk, d.w.z. Met behulp van de schuifbalk moet u de macro kunnen vertellen hoeveel klanten van wie de gegevens in het draaitabelrapport worden weergegeven. Met behulp van de schuifbalk kan de gebruiker dus naar eigen goeddunken de top vijf, top acht of top tweeëndertig klanten ophalen.
Om een schuifbalk aan uw spreadsheet toe te voegen, klikt u op de Ontwikkelaar, klik op de knop Invoegen selecteert u een besturingselement in het palet Schuifbalk en plaats deze op je werkblad. Klik met de rechtermuisknop op het besturingselement Schuifbalk Objectformaat. Er wordt een dialoogvenster geopend Controleformaat(Afb. 9). Breng daarin de volgende wijzigingen aan in de instellingen: parameter Minimale waarde wijs waarde 1 toe aan parameter Maximale waarde- waarde 200, en in het veld Celcommunicatie Voer $M$2 in om de schuifbalkwaarde in cel M2 weer te geven. Klik op de knop OK om de eerder opgegeven instellingen toe te passen.
Nu moet je de recent opgenomen macro matchen EerstNklanten met bedieningselement Schuifbalk bevindt zich op het werkblad. Klik met de rechtermuisknop op het besturingselement Schuifbalk en selecteer in het contextmenu de opdracht Macro toewijzen om het dialoogvenster voor macrotoewijzing te openen. Wijs een opgenomen macro toe aan de schuifbalk FirstN-klanten. De macro wordt uitgevoerd telkens wanneer u op de schuifbalk klikt. Test de gemaakte schuifbalk. Als u op de balk klikt, wordt de macro gestart FirstN-klanten en het getal in cel M2 verandert om de status van de schuifbalk weer te geven. Het getal in cel M2 is belangrijk omdat het wordt gebruikt om de macro aan de schuifbalk te binden.
Het enige dat u nog hoeft te doen, is ervoor zorgen dat de macro het getal in cel M2 verwerkt door dit aan de schuifbalk te koppelen. Om dit te doen, moet je naar de VBA-code van de macro gaan. Ga hiervoor naar het tabblad Ontwikkelaar en klik op de knop Macro's. Er wordt een dialoogvenster geopend Macro(Afb. 10). Daarin kunt u de geselecteerde macro uitvoeren, verwijderen en bewerken. Om de VBA-code van een macro op het scherm weer te geven, selecteert u de macro en klikt u op de knop Wijziging.
Rijst. 10. Om toegang te krijgen tot de VBA-code van de macro EerstNklanten, selecteer de macro en klik op de knop Wijziging
Er verschijnt een Visual Basic-editorvenster met de VBA-macrocode op het scherm (Fig. 11). Uw doel is om het hardgecodeerde getal 5, dat wordt ingesteld wanneer de macro wordt opgenomen, te vervangen door de waarde in cel M2, die aan de schuifbalk is gekoppeld. In eerste instantie werd een macro opgenomen om de eerste vijf klanten met het hoogste inkomen weer te geven.
Verwijder het getal 5 uit de code en voer in plaats daarvan de volgende uitdrukking in:
ActiveSheet.Range(" M2 ").Waarde
Voeg twee regels toe aan het begin van de macro om de filters te wissen:
Bereik(" A4 ").Selecteer
ActiveSheet.PivotTables(" Draaitabel1 ").PivotFields(" Klantnaam ").ClearAllFilters
Nu zou de macrocode eruit moeten zien zoals getoond in Fig. 12.
Sluit de Visual Basic Editor en ga terug naar het draaitabelrapport. Test de schuifbalk door de schuifregelaar naar 11 te slepen. De macro zou moeten worden uitgevoerd en de top 11 verkoopaccounts moeten filteren.
Synchroniseer twee draaitabellen met behulp van één vervolgkeuzelijst
Het rapport getoond in afb. 13 bevat twee samenvattende tabellen. Elk van hen heeft een paginaveld waarmee u een afzetmarkt kunt selecteren. Het probleem is dat elke keer dat u een markt selecteert in het paginaveld van de ene draaitabel, u dezelfde markt moet selecteren in het paginaveld van een andere draaitabel. Het synchroniseren van filters tussen twee tabellen tijdens de data-analysefase is geen groot probleem, maar de kans bestaat dat u of uw klanten dit zullen vergeten.
Rijst. 13. Twee draaitabellen bevatten paginavelden die gegevens filteren op markt. Om gegevens voor één enkele markt te analyseren, moet u beide draaitabellen synchroniseren
Eén manier om deze draaitabellen gesynchroniseerd te houden, is door een vervolgkeuzelijst te gebruiken. Het idee is om een macro op te nemen die de gewenste markt uit het veld selecteert Verkoop markt in beide tabellen. Vervolgens moet u een vervolgkeuzelijst maken en deze vullen met de namen van de markten uit de twee draaitabellen. Ten slotte moet de opgenomen macro worden aangepast om beide draaitabellen te filteren met behulp van de waarden uit de vervolgkeuzelijst. Om dit probleem op te lossen, moet je het volgende doen:
1. Maak een nieuwe macro en geef deze een naam Synchroniseer markten. Wanneer de opname begint, selecteert u in het veld Afzetmarkt voor beide overzichtstabellen voor de verkoopmarkten Californië en stop met het opnemen van de macro.
2. Geef het palet Formulierbesturingselementen weer en voeg een vervolgkeuzelijst toe aan het werkblad.
3. Maak een hardgecodeerde lijst van alle markten in de draaitabel. Houd er rekening mee dat het eerste element van de lijst de waarde (All) is. U moet dit element inschakelen als u alle markten uit de vervolgkeuzelijst wilt kunnen selecteren.
4. Op dit punt zou het draaitabelrapport er moeten uitzien zoals weergegeven in de afbeelding. 14.
Rijst. 14. Je hebt alle benodigde tools tot je beschikking: een macro die het veld verandert Verkoop markt beide draaitabellen, een vervolgkeuzelijst en een lijst met alle afzetmarkten in de draaitabel
5. Klik met de rechtermuisknop op de vervolgkeuzelijst en selecteer de opdracht in het contextmenu Objectformaat om de besturing te configureren.
6. Stel eerst het initiële bereik van waarden in dat wordt gebruikt om de vervolgkeuzelijst te vullen, zoals weergegeven in figuur. 15. In dit geval hebben we het over de lijst met afzetmarkten die u in stap 3 heeft aangemaakt. Geef vervolgens de cel aan waarin het serienummer van het geselecteerde element wordt weergegeven (in dit voorbeeld is dit cel H1). Parameter Aantal lijstregels bepaalt hoeveel rijen tegelijkertijd in de vervolgkeuzelijst worden weergegeven. Klik op de knop OK.
Rijst. 15. De instellingen van de vervolgkeuzelijst moeten verwijzen naar de lijst met afzetmarkten als het initiële bereik van waarden, en cel H1 definiëren als het ankerpunt
7. Nu hebt u de mogelijkheid om een afzetmarkt in de vervolgkeuzelijst te selecteren en het bijbehorende serienummer in cel H1 te bepalen (Fig. 16). De vraag rijst: waarom wordt de indexwaarde gebruikt in plaats van de echte naam van de markt? Omdat de vervolgkeuzelijst geen naam retourneert, maar een nummer. Als u bijvoorbeeld Californië selecteert in de vervolgkeuzelijst, wordt de waarde 5 weergegeven in cel H1. Dit betekent dat Californië het vijfde item in de lijst is.
Rijst. 16. De vervolgkeuzelijst is nu gevuld met de namen van de markten en het serienummer van de geselecteerde markt wordt weergegeven in cel H1
8. Om een volgnummer te gebruiken in plaats van een marktnaam, moet u dit doorgeven met behulp van de INDEX-functie.
9. Voer de INDEX-functie in, die het serienummer uit cel H1 omzet in een betekenisvolle waarde.
10. De INDEX-functie heeft twee argumenten nodig. Het eerste argument vertegenwoordigt het waardenbereik van de lijst. In de meeste gevallen gebruikt u hetzelfde bereik dat in het vervolgkeuzemenu staat. Het tweede argument is het volgnummer. Als het serienummer in een cel wordt ingevoerd (bijvoorbeeld in cel H1, zoals in figuur 17), dan kunt u eenvoudig naar deze cel verwijzen.
Rijst. 17. De INDEX-functie in cel I1 converteert het volgnummer dat is opgeslagen in cel H1 naar een waarde. U gebruikt de waarde in cel I1 om de macro te wijzigen
11. Bewerk de macro Synchroniseer markten, waarbij de waarde in cel I1 wordt gebruikt in plaats van de hardgecodeerde waarde. Ga naar het tabblad Ontwikkelaar en klik op de knop Macro's. Het dialoogvenster uit figuur 1 verschijnt op het scherm. 18. Selecteer er een macro in Synchroniseer markten en klik op de knop Wijzigen.
Rijst. 18. Om toegang te krijgen tot de VBA-code van de macro, selecteert u de macro Synchroniseer markten en klik Wijziging
12. Bij het vastleggen van de macro heeft u in beide draaitabellen de afzetmarkt Californië uit het veld geselecteerd Verkoop markt. Zoals blijkt uit Fig. Vanaf 19 september is de Californische markt nu hardgecodeerd in de VBA-macrocode.
13. Vervang de waarde "California" door de uitdrukking Activesheet.Range("I1").Value, die verwijst naar de waarde in cel I1. In dit stadium zou de macrocode eruit moeten zien zoals getoond in Fig. 20. Nadat u de macro hebt gewijzigd, sluit u de Visual Basic Editor en keert u terug naar het werkblad.
Rijst. 20. Vervang de waarde "California" door ActiveSheet.Range("I1").Value en sluit de Visual Basic Editor
14. Het enige dat overblijft is ervoor te zorgen dat de macro wordt uitgevoerd bij het selecteren van een afzetmarkt in de vervolgkeuzelijst. Klik met de rechtermuisknop op de vervolgkeuzelijst en selecteer de optie Macro toewijzen. Selecteer macro Synchronisatiemarkt en klik op de knop OK.
15. Verberg de paginaveldrijen en -kolommen in de draaitabellen, evenals de lijst met markten en indexformules die u hebt gemaakt.
In afb. Figuur 21 toont het eindresultaat. U beschikt nu over een gebruikersinterface waarmee klanten in beide draaitabellen een markt kunnen selecteren met behulp van één vervolgkeuzelijst.
Wanneer u een nieuw item in de vervolgkeuzelijst selecteert, wordt het formaat van de kolommen automatisch aangepast aan alle weergegeven gegevens. Dit gedrag van het programma is behoorlijk vervelend bij het opmaken van een werkbladsjabloon. U kunt dit voorkomen door met de rechtermuisknop op de draaitabel te klikken en te selecteren Draaitabelopties. Er verschijnt een dialoogvenster met dezelfde naam op het scherm, waarin u het selectievakje opnieuw moet instellen Wijzig automatisch de kolombreedte bij update.
Het briefje is geschreven op basis van het boek van Jelen, Alexander. . Hoofdstuk 12.
Om dit te doen, moet u de Visual Basic Editor openen (toetsencombinatie "Alt+F11" of met de rechtermuisknop op de snelkoppeling van een willekeurig blad klikken en "Brontekst" selecteren of in de groep "Code" van het menutabblad "Ontwikkelaar" , klik op het item "Visual Basic") ) en voeg een standaardmodule in het project in. En voeg twee programmacodes toe aan deze module (zie figuur 8.) - en .
|
Afm blad als werkblad Cel dimmen als bereik Met ActiveWorkbook Voor elk blad in ActiveWorkbook.Worksheets Cel instellen = Werkbladen(1).Cellen(blad.Index, 1) Werkbladen(1).Hyperlinks.Anker toevoegen:=cel, Adres:="", cel.Formule = blad.Naam |
Hoe u de selectie van meerdere zoekwaarden uit verschillende tabellen in Excel kunt optimaliseren
U kunt de selectie van verschillende gewenste waarden (betalingsbedragen voor verschillende leveranciers) optimaliseren volgens één parameter (bijvoorbeeld op datum) uit een klein aantal tabellen met vrijwel hetzelfde schema als met het enige verschil dat het blad "Inhoudsopgave boek" een lijst zal weergeven met de namen van de tabellen die deelnemen aan de selectie en de resultaten ervan, en bovendien zal een iets andere tabelzoekformule worden gebruikt:
“=VLOOKUP($C$1,INDIRECT(A2),2,FALSE)”, waarbij:
- cel C1 (kwartaalnummer) – stelt de parameterwaarde in;
- “INDIRECT(A2)” – definieert een tekstlink naar een benoemd bereik, waarvan de naam in cel A2 staat;
- “2” is het kolomnummer van de brontabellen van leveranciers, waarin de betalingsbedragen staan die wij nodig hebben;
- “FALSE” (kan worden vervangen door 0) – geeft aan de functie VERT.ZOEKEN aan dat een exacte overeenkomst vereist is.
