Bill položil tento týždeň otázku o nadbytočných údajoch programu Excel.
V programe Excel zostavujem zoznam mesačných transakcií. Na konci mesiaca potrebujem eliminovať nadbytočné dáta a prísť s celkovou podľa kódu účtu. Každý kód účtu sa môže vyskytnúť viackrát. Bill potom opísal svoju súčasnú metodiku programu Excel, ktorá je podobná metóde 1 nižšie, aby mohol prísť s jedinečným zoznamom kódov účtov s plánmi použitia matice vzorcov VVN na získanie súčtov. Pýta sa, existuje ľahší spôsob, ako dospieť k jedinečnému zoznamu kódov účtov s celkovými súčtami pre každý účet?
Toto je dokonalá otázka z dovolenky. Keďže som používateľom Lotusu už 15 rokov, uznávam Billovu metódu ako klasickú metódu pre „rýchlu a špinavú“ manipuláciu s dátami zo starých dobrých čias vydania Lotus 2.1. Toto je obdobie, v ktorom sa má počítať naše požehnanie. Keď sa zamyslíte nad touto otázkou, uvedomíte si, že nás ľudia v spoločnosti Microsoft za tie roky skutočne obdarili množstvom nástrojov. Ak používate program Excel 97, máte na vykonanie tejto úlohy najmenej päť metód, ktoré sú oveľa jednoduchšie ako klasická metóda opísaná Billom. Tento týždeň ponúknem výukový program k týmto piatim metódam.
Môj zjednodušený súbor údajov má v stĺpci A čísla účtov a sumy v stĺpci B. Údaje bežia od A2: B100. Nie je to zoradené na začiatku.
Metóda 1
Na nájdenie odpovede použite kreatívne výroky If v spojení s funkciou Prilepiť špeciálne hodnoty.
Vzhľadom na novšie nástroje ponúkané programom Excel túto metódu už neodporúčam. Často som to používal predtým, ako prišli lepšie veci, a stále existujú situácie, keď to príde užitočné. Moje alternatívne meno je "The-Lotus-123-When-You-Were-Not-In-The-Mood-To-Use- @ DSUM". Tu sú kroky.
- Zoraďte údaje podľa stĺpca A.
- Vymyslite vzorec v stĺpci C, ktorý udrží priebežný súčet podľa účtu. Bunka C2 je
=IF(A2=A1,C1+B2,B2). - Vymyslite vzorec v D, ktorý identifikuje posledný záznam pre konkrétny účet. Bunka D2 je
=IF(A2=A3,FALSE,TRUE). - Skopírujte C2: D2 do všetkých svojich riadkov.
- Kópia C2: D100. Vykonajte úpravy - PasteSpecial - Hodnoty späť na C2: D100, aby ste zmenili vzorce na hodnoty.
- Zoradiť podľa stĺpca D zostupne.
- Pre riadky, ktoré majú v stĺpci D hodnotu TRUE, máte jedinečný zoznam čísel účtov v A a konečný priebežný súčet v C.
Pros: Je to rýchle. Potrebujete len horlivý zmysel pre písanie príkazov IF.
Nevýhody: Existujú lepšie spôsoby.
Metóda 2
Zoznam jedinečných účtov získate pomocou filtra údajov - rozšírený filter.
Billova otázka bola v skutočnosti, ako získať jedinečný zoznam čísel účtov, aby mohol na získanie súčtov použiť vzorce CSE. Toto je spôsob získania zoznamu jedinečných čísel účtov.
- Zvýrazniť A1: A100
- V ponuke vyberte položku Údaje, filter, rozšírený filter
- Kliknite na prepínač „Kopírovať na iné miesto“.
- Začiarknite políčko „Iba jedinečné záznamy“.
- Vyberte prázdnu časť tabuľky, kde sa má zobraziť jedinečný zoznam. Zadajte to do poľa „Kopírovať do:“. (Toto pole je sivé, kým nevyberiete možnosť „Kopírovať na iné miesto“.)
- Kliknite na tlačidlo OK. Jedinečné čísla účtov sa zobrazia v F1.
- Ak chcete získať svoje výsledky, zadajte akékoľvek manipulácie podčiarknuté, vzorce polí atď.
Plusy: Rýchlejšie ako pri metóde 1. Nie je potrebné triedenie.
Nevýhody: Z nasledujúcich vzorcov CSE sa vám bude krútiť hlava.
Metóda 3
Použite konsolidáciu údajov.
Moja kvalita života sa zlepšila, keď program Excel ponúkol konsolidáciu údajov. Toto bolo VEĽKÉ! Jeho nastavenie trvá 30 sekúnd, pre DSUM a ďalšie metódy to však znamenalo smrť. Číslo vášho účtu musí byť vľavo od číselných polí, ktoré chcete sčítať. Nad každým stĺpcom musíte mať nadpis. K obdĺžnikovému bloku buniek, ktorý obsahuje čísla účtov v ľavom stĺpci a nadpisy v hornej časti, musíte priradiť názov rozsahu. V tomto prípade je týmto rozsahom A1: B100.
- Zvýraznite A1: B100
- Ak chcete tejto oblasti priradiť názov rozsahu, kliknite do poľa pre názov (naľavo od panela vzorcov) a zadajte názov, napríklad „TotalMe“. (Alternatívne použite Vložiť - Názov).
- Umiestnite ukazovateľ bunky do prázdnej časti tabuľky.
- Vyberte údaje - konsolidovať
- Do referenčného poľa zadajte názov rozsahu (TotalMe).
- V časti Použiť štítky v začiarknite Horný riadok a Ľavý stĺpec.
- Kliknite na tlačidlo OK
Pros: Toto je moja obľúbená metóda. Nie je potrebné triedenie. Skratka je alt-D N (názov rangen) alt-T alt-L vstúpiť. Je ľahko škálovateľný. Ak váš rozsah obsahuje 12 mesačných stĺpcov, odpoveď bude obsahovať celkové hodnoty za každý mesiac.
Nevýhody: Ak urobíte ďalšiu konsolidáciu údajov na rovnakom hárku, musíte pomocou tlačidla Odstrániť vymazať starý názov rozsahu z poľa Všetky odkazy. Číslo účtu musí byť naľavo od vašich číselných údajov. Je o niečo pomalší ako kontingenčné tabuľky, čo je viditeľné pre množiny údajov s viac ako 10 000 záznamami.
Metóda 4
Použite medzisúčty údajov.
Toto je skvelá vlastnosť. Pretože s výslednými údajmi sa pracuje čudne, používam ich menej často ako Data Consolidate.
- Zoradiť podľa stĺpca A vzostupne.
- Vyberte ľubovoľnú bunku v rozsahu údajov.
- Z ponuky vyberte Údaje - medzisúčty.
- Excel predvolene ponúka medzisúčet posledného stĺpca vašich údajov. V tomto príklade to funguje, ale aby ste vybrali správne polia, musíte často prechádzať zoznamom „Pridať medzisúčet do“.
- Kliknite na tlačidlo OK. Excel vloží nový riadok pri každej zmene čísla účtu s celkovým súčtom.
Keď budete mať medzisúčty, uvidíte pod políčkom s menom malú 123. Kliknutím na 2 zobrazíte iba jeden riadok na účet s celkovými súčtami. V časti Kopírovanie medzisúčtov programu Excel nájdete vysvetlenie osobitných krokov potrebných na ich kopírovanie na nové miesto. Kliknutím na 3 zobrazíte všetky riadky. Plusy: skvelá vlastnosť. Skvelé na tlač správ s celkovými súčetmi a zlommi strán po každej sekcii.
Nevýhody: Najskôr je potrebné zoradiť údaje. Pomaly pre veľa údajov. Ak chcete získať súčty inde, musíte použiť Goto-Special-VisbileCellsOnly. Ak sa chcete vrátiť k pôvodným údajom, musíte použiť Data-Subtotals-RemoveAll.
Metóda 5
Použite kontingenčnú tabuľku.
Kontingenčné tabuľky sú zo všetkých najuniverzálnejšie. Vaše údaje sa nemusia triediť. Číselné stĺpce môžu byť vľavo alebo vpravo od čísla účtu. Čísla účtov môžete jednoducho nechať ísť dole alebo cez stránku.
- Vyberte ľubovoľnú bunku v rozsahu údajov.
- Z ponuky vyberte možnosť Údaje - kontingenčná tabuľka.
- Prijmite predvolené hodnoty v kroku 1
- Skontrolujte, či je rozsah údajov v kroku 2 správny (zvyčajne to je)
- Ak používate program Excel 2000, kliknite na tlačidlo Rozloženie v kroku 3. Používatelia programov Excel 95 a 97 automaticky prejdú na rozloženie ako krok 3.
- V dialógovom okne rozloženia potiahnite tlačidlo Účet z pravej strany dialógového okna a umiestnite ho do oblasti Riadok.
- Presuňte tlačidlo Amount z pravej strany dialógového okna a umiestnite ho do oblasti Data.
- Používatelia programu Excel 2000 kliknú na OK, používatelia programu Excel 95/97 kliknú na Ďalej.
- Zadajte, či chcete výsledky do nového listu alebo do konkrétnej časti existujúceho listu. Prečítajte si viac o kontingenčných tabuľkách v Pokročilé triky kontingenčných tabuliek Excel.
- Kontingenčné tabuľky ponúkajú neuveriteľnú funkčnosť a robia túto úlohu hračkou. Ak chcete skopírovať výsledky kontingenčnej tabuľky, musíte urobiť hodnoty Edit-PasteSpecial-Values, inak vám Excel nedovolí vkladať riadky atď.
Plusy: Rýchle, flexibilné, výkonné. Rýchly, dokonca aj pre množstvo údajov.
Nevýhody: Trochu zastrašujúce.
Bill má teraz štyri nové spôsoby eliminácie nadbytočných údajov. Aj keď tieto metódy nie sú k dispozícii od začiatku, Lotus i Excel boli skvelými inovátormi, ktorí nám priniesli rýchlejšie spôsoby, ako splniť túto všednú úlohu.
