Tento tip je rozšířením tipu Excel z minulého týdne. Pomocí příkazu Přejít na speciální můžete vybrat prázdné buňky z rozsahu. To je skvělá technika aplikace Excel pro čištění dat v kontingenční tabulce.
Kontingenční tabulky jsou skvělé nástroje. Ale co když je vaše kontingenční tabulka jen přechodným krokem při manipulaci s daty a chcete dále využívat souhrnná data v kontingenční tabulce? Pravidelně jsem dostával 5000 řádků dat ze staršího systému sálových počítačů. Tato data mají sloupce pro produkt, měsíc, region, stav a množství. Potřeboval jsem shrnout data v kontingenční tabulce, přičemž měsíce se pohybovaly napříč, produkt, region a stav klesaly v řádcích. Díky kontingenčním tabulkám je to hračka.
Odtamtud jsem potřeboval vzít data z kontingenční tabulky a importovat do aplikace Access. Jak vidíte, výchozí kontingenční tabulka pro to není vhodná. Excel používá styl osnovy tak, aby se každá oblast objevila pouze jednou a za ní byly prázdné buňky až do mezisoučtu oblasti. Buňky v datové sekci bez dat jsou prázdné místo číselných. Také nechci mezisoučty v datech.
Počínaje Excel 97 existují možnosti pro kontingenční tabulku pro ovládání některých z těchto chování.
V dialogovém okně Rozložení kontingenční tabulky dvakrát klikněte na název pole pro oblast. V části Mezisoučty klikněte na přepínač „Žádný“. Tím se zbavíte nežádoucích mezisoučtů.
V aplikaci Excel 2000 se pomocí tlačítka možností dostanete do dialogového okna, kde můžete zadat „U prázdných buněk zobrazit:“ a vyplnit nulu, abyste neměli tabulku plnou prázdných buněk.
Chcete-li dále manipulovat s kontingenční tabulkou, budete ji muset přestat používat jako kontingenční tabulku. Pokud se pokusíte změnit buňky v kontingenční tabulce, Excel vám řekne, že nemůžete změnit část kontingenční tabulky. Chcete-li změnit kontingenční tabulku pouze na hodnoty, postupujte takto:
- Přesuňte ukazatel buňky mimo kontingenční tabulku.
- Pokud kontingenční tabulka začíná na řádku 1, vložte nový řádek 1.
- Začněte zvýrazňovat nový prázdný řádek 1 a tažením dolů vyberte celou kontingenční tabulku.
- Ctrl + C (nebo Upravit - Kopírovat) zkopírujte tento rozsah.
- Se stejnou vybranou oblastí proveďte Upravit - Vložit jinak - Hodnoty - OK, abyste změnili kontingenční tabulku pouze na statickou hodnotu.
Ale tady je skutečný tip na tento týden. Jennifer píše
Neustále jsem narazil na problém použití možnosti kontingenční tabulky, abych shrnul hromady dat, ale nevyplňuje řádky pod každou změnou v kategorii řádků. Víte, jak zajistit, aby se kontingenční tabulka vyplňovala pod každou změnou v kategorii ?? Musel jsem přetahovat a kopírovat každý kód dolů, abych mohl udělat více kontingenčních tabulek nebo třídění. Pokusil jsem se změnit možnosti v kontingenční tabulce, ale marně.
Odpověď není snadné se naučit. Není to intuitivní. Ale pokud nenávidíte přetahování těchto buněk dolů, budete rádi, když si uděláte čas na to, abyste se tento proces naučili! Následujte - zdá se být dlouhý a natažený, ale opravdu to funguje. Jakmile to získáte, můžete to udělat za 20 sekund.
Ve skutečnosti jsou zde 2 nebo 3 nové tipy. Řekněme, že máte nalevo 2 sloupce, které jsou ve formátu osnovy a které je třeba vyplnit. Zvýrazněte z buňky A3 úplně dolů do buňky B999 (nebo jakýkoli váš poslední řádek dat.)
Trik č. 1. Výběr všech prázdných buněk v tomto rozsahu.
Stiskněte Ctrl + G, alt = "" + S + K a poté zadejte. co?
Ctrl + G vyvolá dialogové okno GoTo
Alt + S vybere v dialogovém okně tlačítko „Speciální“
Dialog Přejít na zvláštní je úžasná věc, o které málokdo ví. Stisknutím klávesy „k“ vyberete „mezery“. Stiskněte klávesu Enter nebo klikněte na OK a nyní budete mít vybrané pouze všechny prázdné buňky ve sloupcích osnovy kontingenční tabulky. Toto jsou všechny buňky, které chcete vyplnit.
Trik č. 2. Nedívejte se přitom na obrazovku - je to příliš děsivé a matoucí.
Stiskněte klávesu rovná se. Stiskněte šipku nahoru. Podržte Ctrl a stiskněte Enter. Bít se rovná a šipka nahoru říká: „Chci, aby tato buňka byla jako buňka nade mnou.“ Když stisknete klávesu Ctrl a stisknete klávesu Enter, říká: „Zadejte stejný vzorec do každé vybrané buňky, což je díky triku č. 1 všechny prázdné buňky, které jsme chtěli vyplnit.
Trik č. 3. Což Jennifer už ví, ale je tu pro úplnost.
Nyní musíte změnit všechny tyto vzorce na hodnoty. Znovu vyberte všechny buňky v A3: B999, nejen prázdné mezery. Stisknutím Ctrl + C zkopírujte tento rozsah. Stiskněte alt = "" + E a poté sv (zadejte). vložit speciální hodnoty těchto vzorců.
Ta-da! Už nikdy nebudete trávit odpoledne ručním stahováním záhlaví sloupců v kontingenční tabulce.
