Deze tip is een uitbreiding op de Excel-tip van vorige week. U kunt de opdracht Ga naar speciaal gebruiken om de lege cellen uit een bereik te selecteren. Dit is een geweldige Excel-techniek voor het opschonen van gegevens in een draaitabel.
Draaitabellen zijn geweldige hulpmiddelen. Maar wat als uw draaitabel slechts een tussenstap is in uw datamanipulaties en u verder gebruik wilt maken van de samengevatte gegevens in de draaitabel? Ik ontving regelmatig 5000 rijen met gegevens van een ouder mainframesysteem. Deze gegevens hebben kolommen voor product, maand, regio, status en hoeveelheid. Ik moest de gegevens samenvatten in een draaitabel met maanden aan de bovenkant, product, regio en status langs de rijen. Met draaitabellen is dit een fluitje van een cent.
Van daaruit moest ik de gegevens uit de draaitabel halen en importeren in Access. Zoals u kunt zien, is de standaard draaitabel hiervoor niet geschikt. Excel gebruikt een overzichtsstijl, zodat elke regio maar één keer voorkomt, met lege cellen die erop volgen tot het subtotaal van de regio. Cellen in het gegevensgedeelte zonder gegevens zijn leeg in plaats van numeriek. Ik wil de subtotalen ook niet in de gegevens.
Vanaf Excel 97 zijn er opties voor de draaitabel om een deel van dit gedrag te beheersen.
Dubbelklik in het dialoogvenster Draaitabelindeling op de veldnaam voor regio. Klik in het gedeelte Subtotalen op het keuzerondje voor "Geen". Hierdoor worden de ongewenste subtotalen verwijderd.
In Excel 2000 brengt de optieknop u naar een dialoogvenster waarin u "Voor lege cellen, toon:" kunt specificeren en een nul kunt invullen zodat u geen tabel vol lege cellen heeft.
Om de draaitabel verder te manipuleren, moet u ervoor zorgen dat deze niet langer een draaitabel is. Als u cellen in een draaitabel probeert te wijzigen, zal Excel u vertellen dat u een deel van een draaitabel niet kunt wijzigen. Volg deze stappen om van een draaitabel naar alleen waarden te gaan:
- Verplaats de celaanwijzer buiten de draaitabel.
- Als de draaitabel begint op rij 1, voegt u een nieuwe rij 1 in.
- Begin met markeren in de nieuwe lege rij 1 en sleep naar beneden om de hele draaitabel te selecteren.
- Gebruik Ctrl + C (of Bewerken - Kopiëren) om dit bereik te kopiëren.
- Met hetzelfde gebied geselecteerd, doe Bewerken - Plakken speciaal - Waarden - OK om de draaitabel te wijzigen in een alleen statische waarde.
Maar hier is de echte tip voor deze week. Jennifer schrijft
Ik ben voortdurend het probleem tegengekomen van het gebruik van de draaitabeloptie om reeksen gegevens samen te vatten, maar het vult de rijen onder elke verandering in rijcategorie niet in. Weet jij hoe je de draaitabel kunt laten invullen onder elke verandering in categorie ?? Ik heb elke code naar beneden moeten slepen en kopiëren, zodat ik meer draaitabellen kan doen of kan sorteren. Ik heb geprobeerd de opties in de draaitabel te wijzigen, zonder resultaat.
Het antwoord is niet gemakkelijk te leren. Het is niet intuïtief. Maar als je er een hekel aan hebt om die cellen naar beneden te slepen, zul je het leuk vinden om de tijd te nemen om dit proces te leren! Volg mee - het lijkt lang en uitgesponnen, maar het werkt echt echt. Als je het eenmaal hebt, kun je dit in 20 seconden doen.
Er zijn hier eigenlijk 2 of 3 nieuwe tips. Stel dat u aan de linkerkant 2 kolommen heeft die in overzichtsformaat moeten worden ingevuld. Markeer vanaf cel A3 helemaal tot aan cel B999 (of wat uw laatste rij met gegevens ook is).
Truc # 1. Alle lege cellen in dat bereik selecteren.
Druk op Ctrl + G, alt = "" + S + K en voer dan in. he?
Ctrl + G roept het GoTo-dialoogvenster op
Alt + S kiest de knop "Speciaal" in het dialoogvenster
Het dialoogvenster Ga naar-speciaal is iets ontzagwekkends dat maar weinigen weten. Druk op "k" om "blanco's" te kiezen. Druk op Enter of klik op OK en je hebt nu alleen alle lege cellen in de overzichtskolommen van de draaitabel geselecteerd. Dit zijn alle cellen die u wilt invullen.
Truc 2. Kijk niet naar het scherm terwijl u dit doet - het is te eng en verwarrend.
Druk op de is gelijk aan toets. Druk op de pijl omhoog. Houd Ctrl ingedrukt en druk op Enter. Als je gelijkt aan en de pijl omhoog zegt: "Ik wil dat deze cel net zo is als de cel boven mij." Als u Ctrl ingedrukt houdt wanneer u op enter drukt, zegt u: "Voer dezelfde formule in elke geselecteerde cel in, wat dankzij truc # 1 alle lege cellen zijn die we wilden invullen.
Truc # 3. Die Jennifer al weet, maar is hier voor de volledigheid.
U moet nu al die formules in waarden wijzigen. Selecteer nogmaals alle cellen in A3: B999, niet alleen de lege cellen. Druk op Ctrl + C om dit bereik te kopiëren. Druk op alt = "" + E en vervolgens op sv (enter). om speciale waarden te plakken in deze formules.
Ta-da! U zult nooit meer een middag doorbrengen met het handmatig naar beneden trekken van kolomkoppen in een draaitabel.
