Bill je ta teden postavil vprašanje o odvečnih Excelovih podatkih.
V Excelu sestavim mesečni seznam transakcij. Konec meseca moram odpraviti odvečne podatke in najti skupno številko po kodi računa. Vsaka koda računa se lahko pojavi večkrat. Bill je nato opisal svojo trenutno metodologijo Excela, ki je podobna spodnji metodi 1, da bi pripravil edinstven seznam kod z računi, pri čemer načrtuje uporabo matrike formul CSE za seštevanje. Vpraša se, ali obstaja lažji način, da pridete do edinstvenega seznama kod s skupnimi vsotami za vsak račun?
To je popolno počitniško vprašanje. Kot uporabnik Lotusa že petnajst let prepoznam Billovo metodo kot klasično metodo za "hitro in umazano" obdelavo podatkov iz dobrih starih časov izdaje Lotusa 2.1. To je sezona za štetje naših blagoslovov. Ko pomislite na to vprašanje, se zavedate, da so nam ljudje v Microsoftu v teh letih resnično podarili številna orodja. Če uporabljate Excel 97, obstaja vsaj pet načinov za izvedbo te naloge, ki so veliko lažji od klasične metode, ki jo opisuje Bill. Ta teden bom ponudil vadnico o petih metodah.
Moj poenostavljeni nabor podatkov vsebuje številke računov v stolpcu A in zneske v stolpcu B. Podatki tečejo od A2: B100. Na začetku ni razvrščeno.
1. metoda
Za iskanje odgovorov uporabite izjave Creative if v povezavi s Prilepi posebne vrednosti.
Glede na novejša orodja, ki jih ponuja Excel, te metode ne priporočam več. Včasih sem to veliko uporabljal, preden so se pojavile boljše stvari, in še vedno obstajajo situacije, ko je to koristno. Moje nadomestno ime za to je metoda "The-Lotus-123-When-You-We-Not-In-The-Mood-To-Use-@ DSUM". Tu so koraki.
- Razvrstite podatke po stolpcu A.
- V stolpcu C izumite formulo, ki bo ohranila tekočo vsoto po računih. Celica C2 je
=IF(A2=A1,C1+B2,B2). - Izumite formulo v D, ki bo opredelila zadnji vnos za določen račun. Celica D2 je
=IF(A2=A3,FALSE,TRUE). - Kopirajte C2: D2 v vse vrstice.
- Kopija C2: D100. Naredite Edit - PasteSpecial - Vrednosti nazaj na C2: D100, da spremenite formule na vrednosti.
- Razvrsti po stolpcu D padajoče.
- Za vrstice, ki imajo v stolpcu D TRUE, imate edinstven seznam številk računov v A in končno skupno število v C.
Pros: Hitro je. Vse kar potrebujete je izostren občutek za pisanje izjav IF.
Proti: Obstajajo boljši načini.
2. metoda
Uporabite podatkovni filter - napredni filter, da dobite seznam edinstvenih računov.
Billovo vprašanje je bilo res, kako dobiti edinstven seznam številk računov, da bo lahko uporabil formule CSE za seštevanje. To je metoda za pridobivanje seznama enoličnih številk računov.
- Označite A1: A100
- V meniju izberite Podatki, Filter, Napredni filter
- Kliknite izbirni gumb za "Kopiraj na drugo mesto".
- Potrdite polje »Samo edinstveni zapisi«.
- Izberite prazen del delovnega lista, kjer želite, da se prikaže edinstven seznam. To vnesite v polje "Kopiraj v:". (Upoštevajte, da je to polje sivo, dokler ne izberete »Kopiraj na drugo mesto«.
- Kliknite V redu. Edinstvene številke računov bodo prikazane v F1.
- Vnesite kakršne koli manipulacije po spodnji liniji, formule matric itd., Da boste dobili rezultate.
Prednosti: Hitrejši od metode 1. Razvrščanje ni potrebno.
Proti: Po zahtevanih formulah iskalnikov iskalnikov se vam bo zavrtelo v glavi.
3. metoda
Uporabite Data Consolidate.
Moja kakovost življenja se je izboljšala, ko je Excel ponudil Data Consolidate. To je bilo VELIKO! Nastavitev traja 30 sekund, vendar se za DSUM in druge metode piše smrt. Številka vašega računa mora biti levo od številskih polj, ki jih želite sešteti. Nad vsakim stolpcem morate imeti naslove. Pravokotnemu bloku celic, ki vključujejo številke računov vzdolž levega stolpca in naslove na vrhu, morate dodeliti ime obsega. V tem primeru je to območje A1: B100.
- Označite A1: B100
- Temu območju dodelite ime obsega, tako da kliknete polje z imenom (levo od vrstice s formulami) in vnesete ime, na primer "TotalMe". (Lahko uporabite tudi Vstavi - Ime).
- Kazalec celice postavite v prazen del delovnega lista.
- Izberi podatke - konsolidiraj
- V referenčno polje vnesite ime obsega (TotalMe).
- V razdelku Uporabi nalepke v označite zgornjo vrstico in levi stolpec.
- Kliknite V redu
Pros: To je moja najljubša metoda. Razvrščanje ni potrebno. Bližnjica je alt-D N (ime obsega) alt-T alt-L enter. Je enostavno prilagodljiv. Če vaš obseg vključuje 12 mesečnih stolpcev, bo odgovor imel vsote za vsak mesec.
Proti: Če na istem listu izvedete še eno konsolidacijo podatkov, morate s pomočjo gumba Delete izbrisati staro ime obsega iz polja Vsi sklici. Številka računa mora biti levo od številčnih podatkov. Je nekoliko počasnejši od vrtilnih tabel, kar postane opazno pri naborih podatkov z več kot 10.000 zapisi.
4. metoda
Uporabite vmesne seštevke podatkov.
To je kul lastnost. Ker je s pridobljenimi podatki nenavadno delati, jih uporabljam redkeje kot Data Consolidate.
- Razvrsti po stolpcu A naraščajoče.
- Izberite katero koli celico v obsegu podatkov.
- V meniju izberite Podatki - Vmesni seštevki.
- Excel privzeto ponuja vmesni seštevek zadnjega stolpca vaših podatkov. V tem primeru to deluje, vendar se morate pogosto pomakniti po seznamu "Dodaj vmesni seštevek k:", da izberete pravilna polja.
- Kliknite V redu. Excel bo ob vsaki spremembi številke računa z vsoto vstavil novo vrstico.
Ko vnesete vmesne seštevke, se pod poljem z imenom prikaže majhen 123. Kliknite 2, da si ogledate samo eno vrstico na račun s skupnimi vsotami. Za razlago posebnih korakov, potrebnih za kopiranje na novo mesto, preberite Kopiraj vmesne vsote Excel. Kliknite 3, da si ogledate vse vrstice. Za: Cool Feature. Odlično za tiskanje poročil s skupnimi vsotami in prelomi strani po vsakem odseku.
Proti: Podatke je treba najprej razvrstiti. Počasi za veliko podatkov. Morate uporabiti Goto-Special-VisbileCellsOnly, da dobite seštevek drugje. Če se želite vrniti na prvotne podatke, morate uporabiti Data-Subtotals-RemoveAll.
5. metoda
Uporabite vrtilno tabelo.
Vrtilne tabele so najbolj vsestranske od vseh. Vaših podatkov ni treba razvrščati. Številski stolpci so lahko levo ali desno od številke računa. Številke računov lahko enostavno položite navzdol ali čez stran.
- Izberite katero koli celico v obsegu podatkov.
- V meniju izberite Podatki - vrtilna tabela.
- Sprejmite privzete vrednosti v 1. koraku
- Prepričajte se, da je obseg podatkov v 2. koraku pravilen (običajno je)
- Če uporabljate Excel 2000, v 3. koraku kliknite gumb Postavitev. Uporabniki Excela 95 in 97 samodejno preidejo na postavitev kot 3. korak.
- V pogovornem oknu postavitve povlecite gumb Račun na desni strani pogovornega okna in ga spustite v območje Vrstica.
- Povlecite gumb Količina na desni strani pogovornega okna in ga spustite v področje Podatki.
- Uporabniki Excela 2000 kliknejo V redu, uporabniki programa Excel 95/97 pa Naprej.
- Določite, ali želite rezultate na novem listu ali v določenem delu obstoječega lista. Preberite več o vrtilnih tabelah v naprednih trikih v Excelovi vrtilni tabeli.
- Vrtilne tabele ponujajo neverjetno funkcionalnost in nalogo naredijo hitro. Če želite kopirati rezultate vrtilne tabele, morate narediti Edit-PasteSpecial-Values, sicer vam Excel ne bo dovolil vstavljanja vrstic itd.
Prednosti: hiter, prilagodljiv, zmogljiv. Hitro, tudi za veliko podatkov.
Proti: nekoliko zastrašujoče.
Bill ima zdaj štiri nove metode za odstranjevanje odvečnih podatkov. Čeprav te metode niso bile na voljo od začetka časa, sta bila Lotus in Excel izjemna inovatorja, ki sta nam ponudila hitrejše načine za uresničitev te vsakdanje naloge.
