Bill stilte ukens spørsmål om overflødige Excel-data.
Jeg bygger en månedlig transaksjonsliste i Excel. På slutten av måneden må jeg eliminere de overflødige dataene og komme med en total per kontokode. Hver kontokode kan forekomme flere ganger. Bill beskrev deretter sin nåværende Excel-metode som ligner på metode 1 nedenfor for å komme opp med en unik liste over kontokoder, med planer om å bruke en matrise av CSE-formler for å få totalene. Han spør, er det en enklere måte å komme frem til en unik liste over kontokoder med summer for hver konto?
Dette er et perfekt feriespørsmål. Å være Lotus-bruker i 15 år, anerkjenner jeg Bills metode som den klassiske metoden for "rask og skitten" datamanipulering fra de gode gamle dagene med Lotus release 2.1. Dette er en sesong for å telle våre velsignelser. Når du tenker på dette spørsmålet, innser du at folkene hos Microsoft virkelig har gitt oss en rekke verktøy gjennom årene. Hvis du bruker Excel 97, er det minst fem metoder for å gjøre denne oppgaven, som alle er langt enklere enn den klassiske metoden beskrevet av Bill. Jeg vil tilby en veiledning om de fem metodene denne uken.
Mitt forenklede datasett har kontonumre i kolonne A og beløp i kolonne B. Dataene går fra A2: B100. Det er ikke sortert i begynnelsen.
Metode 1
Bruk kreative If-setninger i forbindelse med Lim inn spesielle verdier for å finne svaret.
Gitt de nyere verktøyene som tilbys av Excel, anbefaler jeg ikke lenger denne metoden. Jeg pleide å bruke dette mye før bedre ting kom, og det er fortsatt situasjoner der det kommer til nytte. Mitt alternative navn for dette er "The-Lotus-123-When-You-Was-Not-In-The-Mood-To-Use- @ DSUM" -metoden. Her er trinnene.
- Sorter dataene etter kolonne A.
- Oppfinn en formel i kolonne C som vil holde et løpende beløp etter konto. Celle C2 er
=IF(A2=A1,C1+B2,B2). - Oppfinn en formel i D som vil identifisere den siste oppføringen for en bestemt konto. Celle D2 er
=IF(A2=A3,FALSE,TRUE). - Kopier C2: D2 ned til alle radene dine.
- Kopi C2: D100. Gjør en Edit - PasteSpecial - Verdier tilbake til C2: D100 for å endre formlene til verdier.
- Sorter etter kolonne D synkende.
- For radene som har SANT i kolonne D, har du en unik liste over kontonumre i A, og den endelige løpende summen i C.
Fordeler: Det er raskt. Alt du trenger er en god følelse av å skrive IF-uttalelser.
Ulemper: Det er bedre måter.
Metode 2
Bruk datafilter - avansert filter for å få listen over unike kontoer.
Bills spørsmål var egentlig hvordan man fikk en unik liste over kontonumre slik at han kunne bruke CSE-formler for å få totalene. Dette er en metode for å få en liste over de unike kontonumrene.
- Fremhev A1: A100
- Velg Data, Filter, Advanced Filter fra menyen
- Klikk på alternativknappen for "Kopier til et annet sted".
- Klikk avkrysningsruten for "Kun unike poster".
- Velg en tom del av regnearket der du vil at den unike listen skal vises. Skriv dette inn i feltet "Kopier til:". (Merk dette feltet er nedtonet til du velger "Kopier til et annet sted".
- Klikk OK. De unike kontonumrene vises i F1.
- Skriv inn eventuelle downline-manipulasjoner, matriseformler osv. For å få resultatene dine.
Fordeler: Raskere enn metode 1. Ingen sortering kreves.
Ulemper: CSE-formlene som kreves etter dette, får hodet til å snurre.
Metode 3
Bruk Data Consolidate.
Livskvaliteten min ble bedre da Excel tilbød Data Consolidate. Dette var STORT! Det tar 30 sekunder å sette den opp, men det stavet død for DSUM og andre metoder. Kontonummeret ditt må være til venstre for de numeriske feltene du vil totalt. Du må ha overskrifter over hver kolonne. Du må tilordne et rekkevidden til den rektangulære celleblokken som inkluderer kontonumrene langs venstre kolonne og overskriftene øverst. I dette tilfellet er dette området A1: B100.
- Fremhev A1: B100
- Tilordne et områdenavn til dette området ved å klikke i navnefeltet (til venstre for formellinjen) og skrive et navn som "TotalMe". (Bruk alternativt Sett inn navn).
- Sett cellepekeren i en tom del av regnearket.
- Plukk data - konsolider
- I referansefeltet skriver du inn navnet på området (TotalMe).
- I delen Bruk etiketter i sjekker du både øverste rad og venstre kolonne.
- Klikk OK
Fordeler: Dette er min favorittmetode. Ingen sortering nødvendig. Snarvei er alt-D N (rangename) alt-T alt-L enter. Den er lett skalerbar. Hvis sortimentet ditt inneholder 12 månedlige kolonner, vil svaret ha totalsummer for hver måned.
Ulemper: Hvis du gjør en annen datakonsolidering på samme ark, må du fjerne det gamle områdetavnet fra feltet Alle referanser ved hjelp av Slett-knappen. Kontonummeret må være til venstre for numeriske data. Det er litt tregere enn pivottabeller som blir merkbare for datasett med 10.000+ poster.
Metode 4
Bruk datatotaler.
Dette er en kul funksjon. Fordi de resulterende dataene er rare å jobbe med, bruker jeg dem sjeldnere enn Data Consolidate.
- Sorter etter kolonne A stigende.
- Velg hvilken som helst celle i dataområdet.
- Velg Data - Delsummer fra menyen.
- Som standard tilbyr Excel å subtotale den siste kolonnen med dataene dine. Dette fungerer i dette eksemplet, men du må ofte bla gjennom listen "Legg til totalt til:" for å velge de riktige feltene.
- Klikk OK. Excel vil sette inn en ny rad ved hver endring av kontonummer med totalt.
Etter at du har delsummene i, vil du se en liten 123 vises under navnefeltet. Klikk på 2 for å se bare én linje per konto med totalene. Les Copy Excel Subtotals for en forklaring på de spesielle trinnene som trengs for å kopiere disse til et nytt sted. Klikk på 3 for å se alle linjene. Fordeler: Cool Feature. Flott for utskrift av rapporter med totalsummer og sidebrudd etter hver seksjon.
Ulemper: Dataene må sorteres først. Sakte for mange data. Du må bruke Goto-Special-VisbileCellsOnly for å få totalene andre steder. Du må bruke Data-Subtotals-RemoveAll for å komme tilbake til de opprinnelige dataene.
Metode 5
Bruk et pivottabell.
Pivottabeller er de mest allsidige av alle. Dataene dine trenger ikke å bli sortert. De numeriske kolonnene kan være til venstre eller høyre for kontonummeret. Du kan enkelt få kontonumrene ned eller på tvers av siden.
- Velg hvilken som helst celle i dataområdet.
- Velg Data - Pivottabell fra menyen.
- Godta standardinnstillingene i trinn 1
- Forsikre deg om at dataområdet i trinn 2 er riktig (det er vanligvis)
- Hvis du bruker Excel 2000, klikker du på Layout-knappen i trinn 3. Excel 95- og 97-brukere går automatisk til layout som trinn 3.
- I layoutdialogen drar du konto-knappen fra høyre side av dialogen og slipper den i radområdet.
- Dra beløpsknappen fra høyre side av dialogen og slipp den i dataområdet.
- Excel 2000-brukere klikker OK, Excel 95/97 brukere klikker Neste.
- Angi om du vil ha resultatene i et nytt ark eller i en bestemt del av et eksisterende ark. Les mer om pivottabeller i Excel Pivot Table Advanced Tricks.
- Pivottabeller tilbyr utrolig funksjonalitet og gjør denne oppgaven et øyeblikk. For å kopiere pivottabellresultatene, må du gjøre en Edit-PasteSpecial-Value, ellers lar ikke Excel deg sette inn rader osv.
Fordeler: Rask, fleksibel, kraftig. Rask, selv for mange data.
Ulemper: Noe skremmende.
Bill har nå fire nye metoder for å eliminere overflødige data. Selv om disse metodene ikke har vært tilgjengelige siden begynnelsen av tiden, har både Lotus og Excel vært gode innovatører for å gi oss raskere måter å utføre denne verdslige oppgaven på.
