Bill položil tento týden otázku nadbytečných dat Excel.
Stavím měsíční seznam transakcí v aplikaci Excel. Na konci měsíce musím odstranit nadbytečná data a přijít s celkem podle kódu účtu. Každý kód účtu se může vyskytnout několikrát. Bill poté popsal svou současnou metodiku aplikace Excel, která je podobná metodě 1 níže, aby přišel s jedinečným seznamem kódů účtů, s plány použít matici vzorců CSE k získání součtů. Ptá se, existuje snadnější způsob, jak dosáhnout jedinečného seznamu kódů účtů s celkovými součty pro každý účet?
To je perfektní prázdninová otázka. Jako uživatel Lotusu po dobu 15 let uznávám Billovu metodu jako klasickou metodu pro „rychlou a špinavou“ manipulaci s daty ze starých dobrých časů vydání Lotus 2.1. Toto je období pro počítání našich požehnání. Když se zamyslíte nad touto otázkou, uvědomíte si, že lidé z Microsoftu nám za ta léta skutečně propůjčili řadu nástrojů. Pokud používáte Excel 97, existuje nejméně pět metod k provedení tohoto úkolu, které jsou mnohem jednodušší než klasická metoda popsaná Billem. Tento týden nabídnu výukový program o pěti metodách.
Moje zjednodušená datová sada má čísla účtů ve sloupci A a částky ve sloupci B. Data běží od A2: B100. Na začátku to není tříděno.
Metoda 1
K nalezení odpovědi použijte kreativní výroky If ve spojení s Paste Special Values.
Vzhledem k novějším nástrojům, které nabízí Excel, již tuto metodu nedoporučuji. Často jsem to používal, než se objevily lepší věci, a stále existují situace, kdy to přijde užitečné. Moje alternativní název pro tuto metodu je metoda „The-Lotus-123-When-You-Were-Not-In-The-Mood-To-Use- @ DSUM“. Tady jsou kroky.
- Seřadit data podle sloupce A.
- Vymyslete vzorec ve sloupci C, který udrží průběžný součet podle účtu. Buňka C2 je
=IF(A2=A1,C1+B2,B2). - Vymyslete vzorec v D, který identifikuje poslední položku pro konkrétní účet. Buňka D2 je
=IF(A2=A3,FALSE,TRUE). - Zkopírujte C2: D2 do všech svých řádků.
- Zkopírujte C2: D100. Proveďte úpravy - PasteSpecial - Hodnoty zpět na C2: D100 a změňte vzorce na hodnoty.
- Řadit podle sloupce D sestupně.
- U řádků, které mají ve sloupci D hodnotu PRAVDA, máte jedinečný seznam čísel účtů v A a konečný průběžný součet v C.
Pros: Je to rychlé. Vše, co potřebujete, je vášnivý smysl pro psaní příkazů IF.
Nevýhody: Existují lepší způsoby.
Metoda 2
Seznam jedinečných účtů získáte pomocí filtru dat - pokročilý filtr.
Billova otázka zněla, jak získat jedinečný seznam čísel účtů, aby mohl použít vzorce CSE k získání součtů. Toto je metoda pro získání seznamu jedinečných čísel účtů.
- Zvýrazněte A1: A100
- Z nabídky vyberte Data, Filtr, Pokročilý filtr
- Klikněte na přepínač „Kopírovat do jiného umístění“.
- Zaškrtněte políčko „Pouze jedinečné záznamy“.
- Vyberte prázdnou část listu, kde chcete zobrazit jedinečný seznam. Zadejte to do pole „Kopírovat do:“. (Toto pole je šedé, dokud nevyberete možnost „Kopírovat na jiné místo“.
- Klikněte na OK. Jedinečná čísla účtů se zobrazí v F1.
- Chcete-li získat výsledky, zadejte jakékoli manipulace s podřízením, maticové vzorce atd.
Pros: Rychlejší než metoda 1. Není nutné žádné třídění.
Nevýhody: Z následujících vzorců CSE se vám zatočí hlava.
Metoda 3
Použijte Data Consolidate.
Moje kvalita života se zlepšila, když Excel nabídl Data Consolidate. To bylo VELKÉ! Nastavení trvá 30 sekund, ale pro DSUM a další metody to znamenalo smrt. Číslo vašeho účtu musí být nalevo od číselných polí, která chcete sečíst. Musíte mít nadpisy nad každým sloupcem. K obdélníkovému bloku buněk, který obsahuje čísla účtů v levém sloupci a nadpisy v horní části, musíte přiřadit název rozsahu. V tomto případě je tento rozsah A1: B100.
- Zvýrazněte A1: B100
- Přiřaďte této oblasti název rozsahu kliknutím do pole pro název (nalevo od řádku vzorců) a zadáním názvu, například „TotalMe“. (Alternativně použijte Vložit - Název).
- Umístěte ukazatel buňky do prázdné části listu.
- Vyberte data - konsolidujte
- Do referenčního pole zadejte název rozsahu (TotalMe).
- V části Použít štítky v zaškrtněte horní řádek i levý sloupec.
- Klikněte na OK
Pros: Toto je moje oblíbená metoda. Není nutné třídění. Klávesová zkratka je alt-D N (název rangen) alt-T alt-L vstoupit. Je snadno škálovatelný. Pokud váš rozsah zahrnuje 12 měsíčních sloupců, odpověď bude mít součty za každý měsíc.
Nevýhody: Pokud provádíte další konsolidaci dat na stejném listu, musíte vymazat starý název rozsahu z pole Všechny odkazy pomocí tlačítka Odstranit. Číslo účtu musí být nalevo od vašich číselných údajů. Je o něco pomalejší než kontingenční tabulky, což je patrné u datových sad s více než 10 000 záznamy.
Metoda 4
Použijte mezisoučty dat.
To je skvělá funkce. Protože s výslednými daty je zvláštní pracovat, používám je méně často než Data Consolidate.
- Řadit podle sloupce A vzestupně.
- Vyberte libovolnou buňku v rozsahu dat.
- Z nabídky vyberte Data - Mezisoučty.
- Ve výchozím nastavení nabízí Excel mezisoučet za poslední sloupec vašich dat. V tomto příkladu to funguje, ale abyste vybrali správná pole, musíte často procházet seznamem „Přidat mezisoučet do:“.
- Klikněte na OK. Excel vloží nový řádek při každé změně čísla účtu s celkovým součtem.
Jakmile budete mít mezisoučty, pod polem se jménem se objeví malá 123. Kliknutím na 2 zobrazíte pouze jeden řádek na účet s celkovými součty. V části Kopírovat mezisoučty aplikace Excel naleznete vysvětlení zvláštních kroků potřebných k jejich zkopírování do nového umístění. Kliknutím na 3 zobrazíte všechny řádky. Pros: Skvělá funkce. Skvělé pro tisk zpráv s celkovými součty a zalomením stránky po každé sekci.
Nevýhody: Data musí být nejprve tříděna. Pomalu pro spoustu dat. Chcete-li získat součty jinde, musíte použít Goto-Special-VisbileCellsOnly. Chcete-li se vrátit k původním datům, musíte použít Data-Subtotals-RemoveAll.
Metoda 5
Použijte kontingenční tabulku.
Kontingenční tabulky jsou nejuniverzálnější ze všech. Vaše data nemusí být tříděna. Číselné sloupce mohou být nalevo nebo napravo od čísla účtu. Můžete snadno nechat čísla účtů jít dolů nebo přes stránku.
- Vyberte libovolnou buňku v rozsahu dat.
- Z nabídky vyberte Data - kontingenční tabulka.
- Přijměte výchozí nastavení v kroku 1
- Ujistěte se, že rozsah dat v kroku 2 je správný (obvykle je)
- Pokud používáte Excel 2000, klikněte na tlačítko Rozvržení v kroku 3. Uživatelé Excel 95 a 97 automaticky přejdou na rozvržení jako krok 3.
- V dialogovém okně rozložení přetáhněte tlačítko Účet z pravé strany dialogového okna a umístěte jej do oblasti Řádek.
- Přetáhněte tlačítko Částka z pravé strany dialogového okna a umístěte jej do oblasti Data.
- Uživatelé aplikace Excel 2000 klepněte na OK, uživatelé aplikace Excel 95/97 klepněte na Další.
- Určete, zda chcete výsledky na novém listu nebo v konkrétní části existujícího listu. Přečtěte si více o kontingenčních tabulkách v Pokročilé triky kontingenčních tabulek aplikace Excel.
- Kontingenční tabulky nabízejí neuvěřitelnou funkčnost a dělají tento úkol hračkou. Chcete-li zkopírovat výsledky kontingenční tabulky, musíte provést Edit-PasteSpecial-Values, jinak vám Excel nedovolí vložit řádky atd.
Klady: Rychlé, flexibilní, výkonné. Rychlé, dokonce i pro velké množství dat.
Nevýhody: Trochu zastrašující.
Bill má nyní čtyři nové metody pro eliminaci nadbytečných dat. I když tyto metody nejsou k dispozici od počátku věků, Lotus i Excel jsou skvělými inovátory, kteří nám přinášejí rychlejší způsoby, jak splnit tento světský úkol.
