Bill stillede denne uges spørgsmål om overflødige Excel-data.
Jeg bygger en månedlig transaktionsliste i Excel. I slutningen af måneden er jeg nødt til at fjerne de overflødige data og komme med en total pr. Konto-kode. Hver kontokode kan forekomme flere gange. Bill beskrev derefter sin nuværende Excel-metode, der svarer til metode 1 nedenfor, for at komme med en unik liste over kontokoder med planer om at bruge en matrix af CSE-formler for at få totalerne. Han spørger, er der en lettere måde at nå frem til en unik liste over kontokoder med totaler for hver konto?
Dette er et perfekt feriespørgsmål. Da jeg var Lotus-bruger i 15 år, genkender jeg Bills metode som den klassiske metode til "hurtig og snavset" datamanipulation fra de gode gamle dage med Lotus release 2.1. Dette er en sæson til at tælle vores velsignelser. Når du tænker på dette spørgsmål, indser du, at folkene hos Microsoft virkelig har tildelt os en række værktøjer gennem årene. Hvis du bruger Excel 97, er der mindst fem metoder til at udføre denne opgave, som alle er langt lettere end den klassiske metode, der er beskrevet af Bill. Jeg vil tilbyde en tutorial om de fem metoder i denne uge.
Mit forenklede datasæt har kontonumre i kolonne A og beløb i kolonne B. Dataene løber fra A2: B100. Det er ikke sorteret i starten.
Metode 1
Brug kreative If-udsagn i forbindelse med Indsæt specielle værdier for at finde svaret.
I betragtning af de nyere værktøjer, der tilbydes af Excel, anbefaler jeg ikke længere denne metode. Jeg brugte dette meget, før der kom bedre ting, og der er stadig situationer, hvor det kommer nyttigt. Mit alternative navn på dette er "The-Lotus-123-When-You-Were-Not-In-The-Mood-To-Use- @ DSUM" -metoden. Her er trinene.
- Sorter dataene efter kolonne A.
- Opfind en formel i kolonne C, som holder et løbende total efter konto. Celle C2 er
=IF(A2=A1,C1+B2,B2). - Opfind en formel i D, som identificerer den sidste post for en bestemt konto. Celle D2 er
=IF(A2=A3,FALSE,TRUE). - Kopiér C2: D2 ned til alle dine rækker.
- Kopi C2: D100. Foretag en Edit - PasteSpecial - Værdier tilbage på C2: D100 for at ændre formlerne til værdier.
- Sorter efter kolonne D faldende.
- For de rækker, der har SAND i kolonne D, har du en unik liste over kontonumre i A og det endelige kørende total i C.
Fordele: Det er hurtigt. Alt hvad du behøver er en stærk fornemmelse af at skrive IF-udsagn.
Ulemper: Der er bedre måder.
Metode 2
Brug datafilter - Avanceret filter for at få listen over unikke konti.
Bills spørgsmål var virkelig, hvordan man får en unik liste over kontonumre, så han kunne bruge CSE-formler til at få totalerne. Dette er en metode til at hente en liste over de unikke kontonumre.
- Fremhæv A1: A100
- Vælg Data, Filter, Avanceret filter i menuen
- Klik på alternativknappen for "Kopier til en anden placering".
- Klik på afkrydsningsfeltet for "Kun unikke poster".
- Vælg et tomt afsnit på regnearket, hvor du vil have den unikke liste til at blive vist. Indtast dette i feltet "Kopier til:". (Bemærk dette felt er nedtonet, indtil du vælger "Kopier til en anden placering".
- Klik på OK. De unikke kontonumre vises i F1.
- Indtast eventuelle downline-manipulationer, matrixformler osv. For at få dine resultater.
Fordele: Hurtigere end metode 1. Ingen sortering påkrævet.
Ulemper: De CSE-formler, der kræves efter dette, får dit hoved til at dreje.
Metode 3
Brug datakonsolider.
Min livskvalitet blev bedre, da Excel tilbød Data Consolidate. Dette var STOR! Det tager 30 sekunder at konfigurere det, men det stavede død for DSUM'er og andre metoder. Dit kontonummer skal være til venstre for de numeriske felter, du vil sammenlægge. Du skal have overskrifter over hver kolonne. Du er nødt til at tildele et intervalnavn til den rektangulære celleblok, der inkluderer kontonumrene langs den venstre kolonne og overskrifterne øverst. I dette tilfælde er dette interval A1: B100.
- Fremhæv A1: B100
- Tildel et områdenavn til dette område ved at klikke i navnefeltet (til venstre for formellinjen) og skrive et navn såsom "TotalMe". (Alternativt skal du bruge Indsæt - Navn).
- Sæt cellemarkøren i et tomt afsnit af regnearket.
- Vælg data - konsolider
- Indtast områdets navn (TotalMe) i referencefeltet.
- I afsnittet Brug etiketter i skal du kontrollere både øverste række og venstre kolonne.
- Klik på OK
Fordele: Dette er min yndlingsmetode. Ingen sortering påkrævet. Genvej er alt-D N (rangename) alt-T alt-L enter. Det er let skalerbart. Hvis dit sortiment inkluderer 12 månedlige kolonner, vil svaret have totaler for hver måned.
Ulemper: Hvis du laver en anden datakonsolidering på det samme ark, skal du rydde det gamle rækkeviddenavn fra feltet Alle referencer ved hjælp af knappen Slet. Kontonummeret skal være til venstre for dine numeriske data. Det er lidt langsommere end pivottabeller, som bliver mærkbare for datasæt med 10.000+ poster.
Metode 4
Brug datatotaler.
Dette er en sej funktion. Fordi de resulterende data er underlige at arbejde med, bruger jeg dem sjældnere end Data Consolidate.
- Sorter efter kolonne A stigende.
- Vælg en hvilken som helst celle i dataområdet.
- Vælg Data - Delsummer i menuen.
- Som standard tilbyder Excel at subtotale den sidste kolonne med dine data. Dette fungerer i dette eksempel, men du skal ofte rulle gennem listen "Tilføj subtotal til:" for at vælge de rigtige felter.
- Klik på OK. Excel indsætter en ny række ved hver ændring af kontonummer med et samlet beløb.
Når du har subtotaler i, vil du se en lille 123 vises under navnefeltet. Klik på 2 for kun at se en linje pr. Konto med totalerne. Læs Copy Excel Subtotals for en forklaring på de specielle trin, der er nødvendige for at kopiere disse til en ny placering. Klik på 3 for at se alle linjer. Fordele: Cool Feature. Perfekt til udskrivning af rapporter med totaler og sidebryd efter hver sektion.
Ulemper: Dataene skal sorteres først. Langsom for mange data. Du skal bruge Goto-Special-VisbileCellsOnly for at få totalerne andetsteds. Du skal bruge Data-subtotaler-Fjern alt for at komme tilbage til dine originale data.
Metode 5
Brug en drejetabel.
Drejetabeller er de mest alsidige af alle. Dine data behøver ikke at blive sorteret. De numeriske kolonner kan være til venstre eller højre for kontonummeret. Du kan nemt få kontonumrene ned eller på tværs af siden.
- Vælg en hvilken som helst celle i dataområdet.
- Vælg Data - Pivottabel fra menuen.
- Accepter standardindstillingerne i trin 1
- Sørg for, at dataområdet i trin 2 er korrekt (det er normalt)
- Hvis du bruger Excel 2000, skal du klikke på knappen Layout i trin 3. Excel 95- og 97-brugere går automatisk til layout som trin 3.
- Træk knappen Konto i layoutdialogen fra højre side af dialogen, og slip den i rækkeområdet.
- Træk knappen Beløb fra højre side af dialogen, og slip den i dataområdet.
- Excel 2000-brugere klikker på OK, Excel 95/97-brugere klikker på Næste.
- Angiv, om du vil have resultaterne i et nyt ark eller i et specifikt afsnit af et eksisterende ark. Læs mere om pivottabeller i Excel Pivot Table Advanced Tricks.
- Pivottabeller tilbyder utrolig funktionalitet og gør denne opgave et øjeblik. For at kopiere pivottabellens resultater skal du lave en Edit-PasteSpecial-Værdier, ellers lader Excel dig ikke indsætte rækker osv.
Fordele: Hurtig, fleksibel, kraftig. Hurtigt, selv for masser af data.
Ulemper: Noget skræmmende.
Bill har nu fire nye metoder til at fjerne de overflødige data. Selvom disse metoder ikke har været tilgængelige siden tidens begyndelse, har både Lotus og Excel været gode innovatorer til at give os hurtigere måder at udføre denne verdslige opgave på.
