Bill esitas selle nädala küsimuse üleliigsete Exceli andmete kohta.
Koostan Excelis igakuise tehingute loendi. Kuu lõpus pean üleliigsed andmed kõrvaldama ja pakkuma kokku konto koodi järgi. Iga konto koodi võib esineda mitu korda. Seejärel kirjeldas Bill oma praegust Exceli metoodikat, mis sarnaneb allpool toodud meetodiga 1, et koostada ainulaadne kontokoodide loend koos plaanidega kasutada CSE-valemite maatriksit kogusummade saamiseks. Ta küsib, kas on lihtsam viis unikaalse kontokoodide loendi saamiseks, kus on iga konto kogusummad?
See on ideaalne puhkuseküsimus. Olles 15 aastat Lotuse kasutaja, tunnistan Billi meetodit klassikalise meetodina "kiireks ja määrdunud" andmete manipuleerimiseks Lotuse väljalaske 2.1 vanadest headest aegadest. See on hooaeg meie õnnistuste lugemiseks. Selle küsimuse peale mõeldes mõistate, et Microsofti inimesed on meile aastate jooksul tõesti palju tööriistu andnud. Kui kasutate Exceli 97, on selle ülesande täitmiseks vähemalt viis meetodit, mis kõik on palju lihtsamad kui Billi kirjeldatud klassikaline meetod. Pakun sel nädalal viie meetodi õpetuse.
Minu lihtsustatud andmekogumis on kontonumbrid veerus A ja summad veerus B. Andmed jooksevad vahemikust A2: B100. Alguses ei sorteerita.
1. meetod
Vastuse leidmiseks kasutage loomingulisi If-lauseid koos spetsiaalsete väärtuste kleepimisega.
Arvestades Exceli pakutavaid uuemaid tööriistu, ei soovita ma seda meetodit enam. Ma kasutasin seda palju enne, kui paremad asjad tulid ja on endiselt olukordi, kus see on kasulik. Minu selle alternatiivne nimi on meetod "The-Lotus-123-When-You-Not-In-The-Mood-To-Use- @ DSUM". Siin on sammud.
- Sorteeri andmed veeru A järgi.
- Leiutage veerus C valem, mis hoiab jooksvat koguarvu konto järgi. Lahter C2 on
=IF(A2=A1,C1+B2,B2). - Leiutage D-s valem, mis tuvastab konkreetse konto viimase kande. Lahter D2 on
=IF(A2=A3,FALSE,TRUE). - Kopeerige C2: D2 kõikidesse oma ridadesse.
- Koopia C2: D100. Tehke Edit - PasteSpecial - väärtused tagasi C2: D100-le, et muuta valemid väärtusteks.
- Sorteeri veeru D järgi kahanevalt.
- Nende ridade jaoks, mille veerus D on TÕENE, on teil ainulaadne loend kontonumbritest A-s ja lõplik jooksev kogusumma C-s.
Plussid: see on kiire. Kõik, mida vajate, on terav mõte kirjutada IF-avaldusi.
Miinused: on paremaid viise.
2. meetod
Kasutage unikaalsete kontode loendi saamiseks andmefiltrit - täpsemat filtrit.
Billi küsimus oli tegelikult see, kuidas saada ainulaadne kontonumbrite loend, et ta saaks CSE-valemite abil kogusummade saamiseks kasutada. See on meetod ainulaadsete kontonumbrite loendi saamiseks.
- Esiletõstke A1: A100
- Valige menüüst Data, Filter, Advanced Filter
- Klõpsake raadionuppu "Kopeeri teise asukohta".
- Märkige ruut Ainult ainulaadsed kirjed.
- Valige töölehe tühi jaotis, kuhu soovite unikaalse loendi kuvada. Sisestage see väljale "Kopeeri:". (Pange tähele, et see väli on hall, kuni valite "Kopeeri teise asukohta".
- Klõpsake nuppu OK. Unikaalsed kontonumbrid kuvatakse F1-s.
- Tulemuste saamiseks sisestage kõik alamjoone manipulatsioonid, massiivi valemid jne.
Plussid: kiirem kui 1. meetod. Sortimist pole vaja.
Miinused: pärast seda nõutavad CSE-valemid panevad teie pea ringi käima.
3. meetod
Kasutage andmete konsolideerimist.
Minu elukvaliteet paranes, kui Excel pakkus Data Consolidate'i. See oli SUUR! Selle seadistamiseks kulub 30 sekundit, kuid DSUM-ide ja muude meetodite jaoks oli see surm. Teie kontonumber peab olema vasakult numbriväljadest, mille soovite kokku panna. Iga veeru kohal peavad olema pealkirjad. Peate määrama vahemiku nime ristkülikukujulisele lahtriplokile, mis sisaldab vasakus veerus kontonumbreid ja ülaosas pealkirju. Sel juhul on see vahemik A1: B100.
- Esiletõstke A1: B100
- Määrake sellele alale vahemiku nimi, klõpsates nimeväljal (valemiribast vasakul) ja tippides nime, näiteks "TotalMe". (Alternatiivina kasutage Insert - Name).
- Pange lahtri kursor töölehe tühja sektsiooni.
- Valige andmed - konsolideerige
- Sisestage võrdlusväljale vahemiku nimi (TotalMe).
- Jaotises Siltide kasutamine jaotises kontrollige nii ülemist rida kui ka vasakut veergu.
- Klõpsake nuppu OK
Plussid: see on minu lemmikmeetod. Sortimist pole vaja. Otsetee on alt-D N (vahemiku nimi) alt-T alt-L sisestage. See on hõlpsasti skaleeritav. Kui teie vahemik sisaldab 12 igakuist veergu, on vastuses iga kuu kogusummad.
Miinused: kui teete samal lehel teise andmete konsolideerimise, peate kustutama nupu Kustuta vana vahemiku nime väljal Kõik viited. Kontonumber peab olema arvandmetest vasakul. See on veidi aeglasem kui pöördtabelid, mis on märgatav enam kui 10 000 kirjega andmekogumite puhul.
4. meetod
Kasuta Andmete vahesummasid.
See on lahe omadus. Kuna saadud andmeid on kummaline töötada, kasutan neid harvemini kui Data Consolidate.
- Sorteeri veeru A järgi kasvavalt.
- Valige mis tahes lahter andmevahemikus.
- Valige menüüst Andmed - vahesummad.
- Vaikimisi pakub Excel teie andmete viimase veeru vahesummat. Selles näites see töötab, kuid õigete väljade valimiseks peate sageli sirvima loendit "Lisa vahesumma:".
- Klõpsake nuppu OK. Excel sisestab iga konto numbri muutmise korral uue rea koos kogusummaga.
Pärast vahesummade sisestamist näete nimekasti all väikest 123. Klõpsake nupul 2, et näha ainult ühte rida konto kohta koos kogusummadega. Lugege jaotist Kopeeri Exceli vahesummad, et selgitada nende uude asukohta kopeerimiseks vajalikke erijuhiseid. Kõigi joonte nägemiseks klõpsake nupul 3. Plussid: lahe funktsioon. Suurepärane kokkuvõtete ja lehemurdudega aruannete printimiseks pärast iga jaotist.
Miinused: andmed tuleb kõigepealt sorteerida. Aeglane paljude andmete jaoks. Kogusummade mujale saamiseks peate kasutama ainult Goto-Special-VisbileCellsOnly. Algandmete juurde naasmiseks peate kasutama Data-Subtotals-RemoveAll.
5. meetod
Kasutage pöördtabelit.
Liigtabelid on kõige mitmekülgsemad. Teie andmeid ei pea sortima. Numbriveerud võivad olla kontonumbrist vasakul või paremal. Saate kontonumbrid hõlpsalt allapoole või üle lehe lasta.
- Valige mis tahes lahter andmevahemikus.
- Valige menüüst Data - PivotTable.
- Nõustuge 1. sammu vaikeseadetega
- Veenduge, et 2. sammu andmevahemik oleks õige (tavaliselt on)
- Kui kasutate rakendust Excel 2000, klõpsake 3. sammu nupul Paigutus. Exceli 95 ja 97 kasutajad lähevad automaatselt 3. sammu paigutuseni.
- Paigutusdialoogis lohistage dialoogi paremast küljest nuppu Konto ja tilgutage see alale Rida.
- Lohistage dialoogi paremast küljest nuppu Kogus ja tilgutage see alale Andmed.
- Excel 2000 kasutajad klõpsavad nuppu OK, Exceli 95/97 kasutajad nuppu Edasi.
- Määrake, kas soovite tulemusi uuele lehele või olemasoleva lehe konkreetsesse jaotisse. Lisateavet pivot-tabelite kohta leiate Exceli Pivot-tabeli täpsematest trikkidest.
- Liigendtabelid pakuvad uskumatut funktsionaalsust ja muudavad selle ülesande hõlpsaks. Pivot-tabeli tulemuste kopeerimiseks peate tegema Edit-PasteSpecial-Values, vastasel juhul ei luba Excel teil ridu sisestada jne.
Plussid: kiire, paindlik, võimas. Kiire, isegi paljude andmete jaoks.
Miinused: Mõnevõrra hirmutav.
Billil on nüüd üleliigsete andmete kõrvaldamiseks neli uut meetodit. Kuigi need meetodid pole olnud kättesaadavad aegade algusest peale, on nii Lotus kui ka Excel olnud suurepärased innovaatorid, et tuua meile selle igapäevase ülesande täitmiseks kiiremaid viise.
