Bill ställde veckans fråga om överflödiga Excel-data.
Jag bygger en månatlig transaktionslista i Excel. I slutet av månaden måste jag eliminera de överflödiga uppgifterna och komma med en summa per kontokod. Varje kontokod kan förekomma flera gånger. Bill beskrev sedan sin nuvarande Excel-metod som liknar metod 1 nedan för att komma fram till en unik lista över kontokoder, med planer på att använda en matris med CSE-formler för att få summan. Han frågar, finns det ett enklare sätt att nå en unik lista med kontokoder med totala för varje konto?
Det här är en perfekt semesterfråga. Eftersom jag var Lotus-användare i 15 år, känner jag igen Bills metod som den klassiska metoden för "snabb och smutsig" datahantering från de gamla goda dagarna av Lotus release 2.1. Det här är en säsong för att räkna våra välsignelser. När du tänker på den här frågan inser du att folket på Microsoft verkligen har gett oss ett antal verktyg genom åren. Om du använder Excel 97 finns det minst fem metoder för att utföra denna uppgift, som alla är mycket enklare än den klassiska metod som beskrivs av Bill. Jag kommer att erbjuda en handledning om de fem metoderna den här veckan.
Min förenklade datamängd har kontonummer i kolumn A och belopp i kolumn B. Data går från A2: B100. Det sorteras inte i början.
Metod 1
Använd creative If-uttalanden i kombination med Klistra in specialvärden för att hitta svaret.
Med tanke på de nyare verktygen som Excel erbjuder, rekommenderar jag inte längre den här metoden. Jag brukade använda det här mycket innan bättre saker kom och det finns fortfarande situationer där det kommer till nytta. Mitt alternativa namn för detta är "The-Lotus-123-When-You-Were-Not-In-The-Mood-To-Use- @ DSUM" -metoden. Här är stegen.
- Sortera data efter kolumn A.
- Uppfinna en formel i kolumn C som håller en löpande summa per konto. Cell C2 är
=IF(A2=A1,C1+B2,B2). - Uppfinna en formel i D som identifierar den sista posten för ett visst konto. Cell D2 är
=IF(A2=A3,FALSE,TRUE). - Kopiera C2: D2 ner till alla dina rader.
- Kopia C2: D100. Gör en Edit - PasteSpecial - Värden tillbaka till C2: D100 för att ändra formlerna till värden.
- Sortera efter kolumn D fallande.
- För raderna som har SANT i kolumn D har du en unik lista med kontonummer i A och den slutliga löpande summan i C.
Fördelar: Det är snabbt. Allt du behöver är en stark känsla av att skriva IF-uttalanden.
Nackdelar: Det finns bättre sätt.
Metod 2
Använd datafilter - avancerat filter för att få en lista över unika konton.
Bills fråga var verkligen hur man skaffar en unik lista med kontonummer så att han kan använda CSE-formler för att få summan. Detta är en metod för att få en lista över de unika kontonumren.
- Markera A1: A100
- Från menyn väljer du Data, Filter, Advanced Filter
- Klicka på alternativknappen för "Kopiera till en annan plats".
- Klicka på kryssrutan för "Endast unika poster".
- Välj ett tomt avsnitt i kalkylbladet där du vill att den unika listan ska visas. Ange detta i fältet "Kopiera till:". (Observera att detta fält är nedtonat tills du väljer "Kopiera till en annan plats".
- Klicka på OK. De unika kontonumren visas i F1.
- Ange eventuella downline-manipulationer, matrisformler etc. för att få dina resultat.
Fördelar: Snabbare än metod 1. Ingen sortering krävs.
Nackdelar: De CSE-formler som krävs efter detta får ditt huvud att snurra.
Metod 3
Använd Data Consolidate.
Min livskvalitet förbättrades när Excel erbjöd Data Consolidate. Det här var STOR! Det tar 30 sekunder att ställa in det, men det stavade död för DSUMs och andra metoder. Ditt kontonummer måste vara till vänster om de numeriska fälten du vill totalt. Du måste ha rubriker ovanför varje kolumn. Du måste tilldela ett intervallnamn till det rektangulära blocket av celler som innehåller kontonumren längs den vänstra kolumnen och rubrikerna längst upp. I det här fallet är intervallet A1: B100.
- Markera A1: B100
- Tilldela ett områdesnamn till detta område genom att klicka i namnrutan (till vänster om formelfältet) och skriva ett namn som "TotalMe". (Använd alternativt Infoga - Namn).
- Placera cellpekaren i ett tomt avsnitt i kalkylbladet.
- Välj data - konsolidera
- Skriv intervallnamnet (TotalMe) i referensfältet.
- I avsnittet Använd etiketter i kontrollerar du både översta raden och vänster kolumn.
- Klicka på OK
Fördelar: Detta är min favoritmetod. Ingen sortering krävs. Genväg är alt-D N (rangename) alt-T alt-L enter. Den är lätt skalbar. Om ditt intervall innehåller 12 kolumner per månad kommer svaret att ha totala för varje månad.
Nackdelar: Om du gör en annan datakonsolidering på samma ark, måste du rensa det gamla intervallnamnet från fältet Alla referenser med knappen Ta bort. Kontonumret måste vara till vänster om dina numeriska data. Det är något långsammare än pivottabeller som märks för datamängder med 10 000+ poster.
Metod 4
Använd delsummor.
Detta är en cool funktion. Eftersom den resulterande informationen är konstig att arbeta med använder jag den mindre ofta än Data Consolidate.
- Sortera efter kolumn A stigande.
- Välj vilken cell som helst i dataområdet.
- Välj Data - Delsummor från menyn.
- Som standard erbjuder Excel att delsumma den sista kolumnen med dina data. Detta fungerar i det här exemplet, men du måste ofta bläddra igenom listan "Lägg till delsumma till:" för att välja rätt fält.
- Klicka på OK. Excel infogar en ny rad vid varje ändring av kontonumret med totalt.
När du har delsummorna i visas en liten 123 under namnrutan. Klicka på 2 för att se bara en rad per konto med totalen. Läs Copy Excel Subtotals för en förklaring av de speciella steg som behövs för att kopiera dessa till en ny plats. Klicka på 3 för att se alla rader. Fördelar: Cool Feature. Perfekt för att skriva ut rapporter med totala och sidbrytningar efter varje avsnitt.
Nackdelar: Data måste sorteras först. Långsam för mycket data. Du måste använda Goto-Special-VisbileCellsOnly för att få summan någon annanstans. Du måste använda Data-delsummor-Ta bort alla för att komma tillbaka till dina ursprungliga data.
Metod 5
Använd ett pivottabell.
Pivottabeller är de mest mångsidiga av alla. Dina uppgifter behöver inte sorteras. De numeriska kolumnerna kan vara till vänster eller höger om kontonumret. Du kan enkelt låta kontonumren gå ner eller över sidan.
- Välj vilken cell som helst i dataområdet.
- Välj Data - Pivottabell från menyn.
- Acceptera standardvärdena i steg 1
- Se till att dataområdet i steg 2 är korrekt (det är vanligtvis)
- Om du använder Excel 2000 klickar du på knappen Layout i steg 3. Excel 95 & 97-användare går automatiskt till layout som steg 3.
- I layoutdialogrutan drar du konto-knappen från höger sida av dialogrutan och släpper den i radområdet.
- Dra Amount-knappen från höger sida av dialogrutan och släpp den i dataområdet.
- Excel 2000-användare klickar på OK, Excel 95/97-användare klickar på Nästa.
- Ange om du vill ha resultaten i ett nytt ark eller i ett specifikt avsnitt i ett befintligt ark. Läs mer om pivottabeller i Excel Pivot Table Advanced Tricks.
- Pivottabeller erbjuder otrolig funktionalitet och gör den här uppgiften till ett ögonblick. För att kopiera pivottabellresultaten måste du göra en Edit-PasteSpecial-värden, annars låter Excel dig inte infoga rader etc.
Fördelar: Snabb, flexibel, kraftfull. Snabbt, även för massor av data.
Nackdelar: Något skrämmande.
Bill har nu fyra nya metoder för att eliminera de överflödiga uppgifterna. Även om dessa metoder inte har varit tillgängliga sedan tidens början har både Lotus och Excel varit bra innovatörer för att ge oss snabbare sätt att utföra denna vardagliga uppgift.
