Bill stelde deze week een vraag over overtollige Excel-gegevens.
Ik bouw een maandelijkse transactielijst in Excel. Aan het einde van de maand moet ik de overtollige gegevens verwijderen en een totaal per accountcode bedenken. Elke accountcode kan meerdere keren voorkomen. Bill beschreef vervolgens zijn huidige Excel-methodologie die vergelijkbaar is met methode 1 hieronder om een unieke lijst met rekeningcodes te bedenken, met plannen om een matrix van CSE-formules te gebruiken om de totalen te krijgen. Hij vraagt: is er een gemakkelijkere manier om te komen tot een unieke lijst met rekeningcodes met totalen voor elke rekening?
Dit is een perfecte vakantievraag. Omdat ik al 15 jaar Lotus-gebruiker ben, herken ik de methode van Bill als de klassieke methode voor "snelle en vuile" datamanipulatie uit de goede oude tijd van Lotus release 2.1. Dit is een seizoen om onze zegeningen te tellen. Als je over deze vraag nadenkt, besef je dat de mensen bij Microsoft ons in de loop der jaren echt een aantal tools hebben geschonken. Als u Excel 97 gebruikt, zijn er minstens vijf methoden om deze taak uit te voeren, die allemaal veel eenvoudiger zijn dan de klassieke methode die wordt beschreven door Bill. Ik zal deze week een tutorial over de vijf methoden aanbieden.
Mijn vereenvoudigde dataset heeft rekeningnummers in kolom A en bedragen in kolom B. De gegevens lopen van A2: B100. Het is aan het begin niet gesorteerd.
Methode 1
Gebruik creatieve If-uitspraken in combinatie met Speciale waarden plakken om het antwoord te vinden.
Gezien de nieuwere tools die Excel biedt, raad ik deze methode niet langer aan. Ik gebruikte dit veel voordat er betere dingen kwamen en er zijn nog steeds situaties waarin het van pas komt. Mijn alternatieve naam hiervoor is "The-Lotus-123-When-You-Were-Not-In-The-Mood-To-Use- @ DSUM" -methode. Hier zijn de stappen.
- Sorteer de gegevens op kolom A.
- Bedenk een formule in kolom C die een lopend totaal per account bijhoudt. Cel C2 is
=IF(A2=A1,C1+B2,B2). - Bedenk een formule in D die de laatste invoer voor een bepaalde rekening identificeert. Cel D2 is
=IF(A2=A3,FALSE,TRUE). - Kopieer C2: D2 naar al uw rijen.
- Kopieer C2: D100. Doe een Bewerken - PlakSpeciaal - Waarden terug naar C2: D100 om de formules in waarden te veranderen.
- Sorteer op kolom D aflopend.
- Voor de rijen met WAAR in kolom D, hebt u een unieke lijst met rekeningnummers in A en het laatste lopende totaal in C.
Voordelen: It is quick. Het enige wat je nodig hebt, is een scherp gevoel voor het schrijven van IF-statements.
Nadelen: er zijn betere manieren.
Methode 2
Gebruik Gegevensfilter - Geavanceerd filter om de lijst met unieke accounts op te halen.
Bill's vraag was eigenlijk hoe hij een unieke lijst met rekeningnummers kon krijgen, zodat hij CSE-formules kon gebruiken om de totalen te krijgen. Dit is een methode om een lijst met de unieke rekeningnummers te krijgen.
- Markeer A1: A100
- Kies Gegevens, Filter, Geavanceerd filter in het menu
- Klik op het keuzerondje voor "Kopiëren naar een andere locatie".
- Klik op het selectievakje voor "Alleen unieke records".
- Kies een leeg gedeelte van het werkblad waar u de unieke lijst wilt laten verschijnen. Typ dit in het veld "Kopiëren naar:". (Merk op dat dit veld grijs wordt weergegeven totdat u "Kopiëren naar een andere locatie" kiest.
- Klik OK. De unieke rekeningnummers verschijnen in F1.
- Voer downline-manipulaties, matrixformules, enz. In om uw resultaten te krijgen.
Voordelen: sneller dan methode 1. Geen sortering vereist.
Nadelen: de CSE-formules die hierna nodig zijn, zullen uw hoofd doen draaien.
Methode 3
Gebruik Data Consolidate.
Mijn kwaliteit van leven verbeterde toen Excel Data Consolidate aanbood. Dit was GROOT! Het duurt 30 seconden om het op te zetten, maar het spelde dood voor DSUM's en andere methoden. Uw accountnummer moet links van de numerieke velden staan die u wilt optellen. U moet kopteksten boven elke kolom hebben. U moet een bereiknaam toewijzen aan het rechthoekige blok cellen met de rekeningnummers in de linkerkolom en de koppen bovenaan. In dit geval is dat bereik A1: B100.
- Markeer A1: B100
- Wijs een bereiknaam toe aan dit gebied door in het naamvak (links van de formulebalk) te klikken en een naam te typen zoals "TotalMe". (Of gebruik Invoegen - Naam).
- Plaats de celaanwijzer in een leeg gedeelte van het werkblad.
- Kies gegevens - consolideren
- Typ de bereiknaam (TotalMe) in het referentieveld.
- Controleer in het gedeelte Labels gebruiken in zowel de bovenste rij als de linkerkolom.
- Klik OK
Voordelen: dit is mijn favoriete methode. Geen sortering vereist. Snelkoppeling is alt-D N (rangenaam) alt-T alt-L enter. Het is gemakkelijk schaalbaar. Als uw bereik 12 maandelijkse kolommen bevat, heeft het antwoord totalen voor elke maand.
Nadelen: als u nog een Data Consolidate op hetzelfde blad doet, moet u de oude bereiknaam uit het veld Alle verwijzingen wissen met de knop Verwijderen. Het rekeningnummer moet links van uw numerieke gegevens staan. Het is iets langzamer dan draaitabellen, wat merkbaar wordt voor datasets met meer dan 10.000 records.
Methode 4
Gebruik gegevenssubtotalen.
Dit is een coole functie. Omdat de resulterende gegevens vreemd zijn om mee te werken, gebruik ik ze minder vaak dan Data Consolidate.
- Sorteer op kolom A oplopend.
- Selecteer een cel in het gegevensbereik.
- Kies Gegevens - Subtotalen in het menu.
- Standaard biedt Excel aan om de laatste kolom van uw gegevens te subtotalen. Dit werkt in dit voorbeeld, maar je moet vaak door de lijst "Subtotaal toevoegen aan:" bladeren om de juiste velden te kiezen.
- Klik OK. Excel zal bij elke wijziging van het rekeningnummer een nieuwe rij met een totaal invoegen.
Nadat u de subtotalen heeft ingevoerd, ziet u een kleine 123 verschijnen onder het naamvak. Klik op 2 om slechts één regel per rekening met de totalen te zien. Lees Excel-subtotalen kopiëren voor uitleg over de speciale stappen die nodig zijn om deze naar een nieuwe locatie te kopiëren. Klik op 3 om alle lijnen te zien. Voordelen: Cool Feature. Geweldig voor het afdrukken van rapporten met totalen en pagina-einden na elke sectie.
Nadelen: de gegevens moeten eerst worden gesorteerd. Langzaam voor veel gegevens. U moet Goto-Special-VisbileCellsOnly gebruiken om de totalen ergens anders op te halen. U moet Data-Subtotals-RemoveAll gebruiken om terug te gaan naar uw oorspronkelijke gegevens.
Methode 5
Gebruik een draaitabel.
Draaitabellen zijn de meest veelzijdige van allemaal. Uw gegevens hoeven niet te worden gesorteerd. De numerieke kolommen kunnen links of rechts van het rekeningnummer staan. U kunt de rekeningnummers eenvoudig naar beneden of over de pagina laten gaan.
- Selecteer een cel in het gegevensbereik.
- Kies Gegevens - Draaitabel in het menu.
- Accepteer de standaardinstellingen in stap 1
- Zorg ervoor dat het gegevensbereik in stap 2 correct is (meestal is dit het geval)
- Als u Excel 2000 gebruikt, klikt u op de knop Lay-out in stap 3. Gebruikers van Excel 95 & 97 gaan automatisch naar lay-out als stap 3.
- Sleep in het lay-outvenster de knop Account vanaf de rechterkant van het dialoogvenster en zet deze neer in het gebied Rij.
- Sleep de knop Bedrag vanaf de rechterkant van het dialoogvenster en zet deze neer in het gegevensgebied.
- Gebruikers van Excel 2000 klikken op OK, gebruikers van Excel 95/97 klikken op Volgende.
- Geef aan of u de resultaten in een nieuw blad of in een specifieke sectie van een bestaand blad wilt. Lees meer over draaitabellen in Excel Pivot Table Advanced Tricks.
- Draaitabellen bieden ongelooflijke functionaliteit en maken deze taak in een handomdraai. Om de resultaten van de draaitabel te kopiëren, moet u een Edit-PasteSpecial-Values uitvoeren, anders laat Excel u geen rijen invoegen, enz.
Voordelen: snel, flexibel, krachtig. Snel, zelfs voor veel gegevens.
Nadelen: enigszins intimiderend.
Bill heeft nu vier nieuwe methoden om de overtollige gegevens te verwijderen. Hoewel deze methoden sinds het begin der tijden niet beschikbaar zijn, zijn zowel Lotus als Excel geweldige vernieuwers geweest om ons snellere manieren te bieden om deze alledaagse taak te volbrengen.
