Bill je ovotjedno pitanje postavio o suvišnim Excel podacima.
Mjesečni popis transakcija izrađujem u Excelu. Na kraju mjeseca moram ukloniti suvišne podatke i doći do ukupnog broja po kodu računa. Svaki kod računa može se pojaviti više puta. Bill je zatim opisao svoju trenutnu Excel metodologiju koja je slična metodi 1 u nastavku kako bi došao do jedinstvenog popisa kodova računa, s planovima da koristi matricu CSE formula za dobivanje ukupnih podataka. Pita, postoji li jednostavniji način doći do jedinstvenog popisa kodova računa sa ukupnim brojevima za svaki račun?
Ovo je savršeno pitanje za odmor. Budući da sam Lotusov korisnik već 15 godina, prepoznajem Billovu metodu kao klasičnu metodu za "brzu i prljavu" manipulaciju podacima iz dobrih starih vremena Lotusova izdanja 2.1. Ovo je sezona za računanje naših blagoslova. Kad razmislite o ovom pitanju, shvatite da su nam ljudi iz Microsofta tijekom godina zaista poklonili brojne alate. Ako koristite Excel 97, postoji najmanje pet metoda za izvršavanje ovog zadatka, a sve su daleko lakše od klasične metode koju je opisao Bill. Ovaj ću tjedan ponuditi tutorial o pet metoda.
Moj pojednostavljeni skup podataka ima brojeve računa u stupcu A i iznose u stupcu B. Podaci se kreću od A2: B100. Nije sortirano na početku.
Metoda 1
Da biste pronašli odgovor, upotrijebite kreativne izjave If u kombinaciji s Lijepljenjem posebnih vrijednosti.
S obzirom na novije alate koje nudi Excel, više ne preporučujem ovu metodu. Znao sam to puno koristiti prije nego što su se pojavile bolje stvari, ali još uvijek postoje situacije kada to dođe u obzir. Moje zamjensko ime za ovo je metoda "The-Lotus-123-Kad-niste-bili-u-raspoloženi za upotrebu-@ DSUM". Evo koraka.
- Razvrstajte podatke prema stupcu A.
- Izumite formulu u stupcu C koja će zadržati tekući zbroj po računu. Stanica C2 je
=IF(A2=A1,C1+B2,B2). - Izumite formulu u D koja će identificirati zadnji unos za određeni račun. Stanica D2 je
=IF(A2=A3,FALSE,TRUE). - Kopirajte C2: D2 dolje u sve vaše retke.
- Kopija C2: D100. Vratite Edit - PasteSpecial - Vrijednosti natrag na C2: D100 da biste promijenili formule u vrijednosti.
- Poredaj po stupcu D silazno.
- Za retke koji imaju ISTINU u stupcu D imate jedinstveni popis brojeva računa u A, a konačni ukupni ukupni broj u C.
Pros: Brzo je. Sve što vam treba je istančan osjećaj za pisanje IF izjava.
Protiv : Postoje bolji načini.
Metoda 2
Koristite filtar podataka - napredni filtar da biste dobili popis jedinstvenih računa.
Billovo pitanje zapravo je bilo kako doći do jedinstvenog popisa brojeva računa kako bi mogao koristiti CSE formule za dobivanje ukupnih iznosa. Ovo je metoda za dobivanje popisa jedinstvenih brojeva računa.
- Istaknite A1: A100
- Na izborniku odaberite Podaci, Filtriraj, Napredni filtar
- Kliknite radio gumb za "Kopiraj na drugo mjesto".
- Kliknite potvrdni okvir za "Samo jedinstveni zapisi".
- Odaberite prazan odjeljak radnog lista na kojem želite da se pojavi jedinstveni popis. Unesite ovo u polje "Kopiraj u:". (Imajte na umu da je ovo polje sivo dok ne odaberete "Kopiraj na drugo mjesto".
- Kliknite U redu. Jedinstveni brojevi računa pojavit će se u F1.
- Unesite bilo koju manipulaciju donje linije, formule niza itd. Da biste dobili svoje rezultate.
Pros: Brži od metode 1. Nije potrebno sortiranje.
Protiv : CSE formule potrebne nakon ovoga zavrtjet će vam glavu.
Metoda 3
Koristite Data Consolidate.
Moja kvaliteta života poboljšala se kad je Excel ponudio Data Consolidate. Ovo je bilo VELIKO! Postavljanje traje 30 sekundi, ali za DSUM-ove i druge metode piše se smrt. Broj vašeg računa mora biti s lijeve strane numeričkih polja koja želite ukupno zbrojiti. Iznad svakog stupca morate imati naslove. Pravokutnom bloku ćelija koji uključuje brojeve računa uz lijevi stupac i naslove uz vrh trebate dodijeliti naziv raspona. U ovom je slučaju taj raspon A1: B100.
- Istaknite A1: B100
- Dodijelite naziv raspona ovom području klikom na okvir s imenom (lijevo od trake s formulama) i upisivanjem imena kao što je "TotalMe". (Alternativno koristite Insert - Name).
- Stavite pokazivač ćelije u prazan dio radnog lista.
- Odaberite podatke - objedinite
- U polje reference upišite naziv raspona (TotalMe).
- U odjeljku Use Labels In označite i gornji redak i lijevi stupac.
- Kliknite U redu
Pros: Ovo je moja omiljena metoda. Nije potrebno sortiranje. Prečac je alt-D N (naziv dometa) alt-T alt-L enter. Lako je skalabilan. Ako vaš raspon uključuje 12 mjesečnih stupaca, odgovor će imati ukupne vrijednosti za svaki mjesec.
Protiv: Ako na istom listu napravite još jedno Konsolidiranje podataka, morate izbrisati staro ime raspona iz polja Sve reference pomoću gumba Izbriši. Broj računa mora biti lijevo od vaših numeričkih podataka. Nešto je sporiji od zaokretnih tablica što postaje uočljivo za skupove podataka s više od 10.000 zapisa.
Metoda 4
Koristite međuzbrojeve podataka.
Ovo je super značajka. Budući da je s dobivenim podacima neobično raditi, koristim ih rjeđe od Data Consolidate.
- Poredaj po stupcu A uzlazno.
- Odaberite bilo koju ćeliju u rasponu podataka.
- Na izborniku odaberite Podaci - Međuzbrojevi.
- Prema zadanim postavkama, Excel nudi zbroj zadnjeg stupca vaših podataka. To u ovom primjeru funkcionira, ali često morate pomicati kroz popis "Dodaj ukupan zbroj na:" da biste odabrali ispravna polja.
- Kliknite U redu. Excel će umetnuti novi redak pri svakoj promjeni broja računa sa ukupnim brojem.
Nakon što unesete međuzbrojeve, vidjet ćete mali 123 ispod okvira s imenom. Kliknite na 2 da biste vidjeli samo jedan redak po računu sa ukupnim brojevima. Pročitajte Kopiraj podzbrojeve programa Excel za objašnjenje posebnih koraka potrebnih za kopiranje na novo mjesto. Kliknite na 3 da biste vidjeli sve retke. Pros: Cool Feature. Izvrsno za ispis izvješća s ukupnim brojevima i prelomima stranica nakon svakog odjeljka.
Protiv : Prvo se moraju sortirati podaci. Usporite za puno podataka. Morate koristiti Goto-Special-VisbileCellsOnly da biste dobili ukupne podatke negdje drugdje. Morate koristiti Data-Subtotals-RemoveAll da biste se vratili izvornim podacima.
5. metoda
Koristite zaokretnu tablicu.
Pivot tablice su najsvestranije od svih. Vaši se podaci ne moraju sortirati. Numerički stupci mogu biti s lijeve ili desne strane broja računa. Brojevi računa lako se mogu spustiti ili preko stranice.
- Odaberite bilo koju ćeliju u rasponu podataka.
- Na izborniku odaberite Podaci - zaokretna tablica.
- Prihvatite zadane postavke u 1. koraku
- Provjerite je li raspon podataka u koraku 2 ispravan (obično jest)
- Ako koristite Excel 2000, kliknite gumb Izgled u koraku 3. Korisnici programa Excel 95 i 97 automatski prelaze na izgled kao korak 3.
- U dijaloškom rasporedu povucite gumb Račun s desne strane dijaloškog okvira i ispustite ga u područje Red.
- Povucite gumb Količina s desne strane dijaloškog okvira i ispustite ga u područje Podaci.
- Korisnici programa Excel 2000 kliknite U redu, korisnici programa Excel 95/97 kliknite Dalje.
- Navedite želite li rezultate u novom listu ili u određenom odjeljku postojećeg lista. Pročitajte više o zaokretnim tablicama u naprednim trikovima za zaokretnu tablicu programa Excel.
- Izvedene tablice nude nevjerojatnu funkcionalnost i čine ovaj zadatak brzim. Da biste kopirali rezultate pivot tablice, morate napraviti Edit-PasteSpecial-Values, u suprotnom Excel vam neće dopustiti umetanje redaka itd.
Pros: Brz, fleksibilan, moćan. Brzo, čak i za puno podataka.
Protiv: donekle zastrašujuće.
Bill sada ima četiri nove metode za uklanjanje suvišnih podataka. Iako ove metode nisu bile dostupne od početka vremena, i Lotus i Excel bili su sjajni inovatori koji su nam donijeli brže načine za ostvarenje ovog svakodnevnog zadatka.
