Nedávno jsem byl na několika seminářích Excel Power. Když dostanete 150 účetních do místnosti na ráno plné rad Excel tipů a triků, vždy se naučím něco nového. Někdo v publiku je schopen sdílet skvělý trik se zbytkem místnosti.
V dnešní epizodě mám sbírku nových triků. Ve skutečnosti jde o triky, které jsou lepší nebo odlišné od ekvivalentní metody popsané v knize. Určitě budou v příští revizi knihy.
Mimochodem, rád bych přišel do vašeho města udělat seminář Power Excel. Pokud patříte do profesionální skupiny, jako je místní kapitola Institutu manažerských účetních, Institut interních auditorů, AICPA, SME atd., Proč mi nenavrhnout, aby si mě rezervovali na jeden z jejich nadcházejících dnů CPE? Zašlete na tuto stránku předsedu svého programu kapitoly.
Najděte rozdíl mezi dvěma daty
I obvykle mluví o metodách pro použití =YEAR(), =MONTH(), =DAY()funkce, ale tam je cool stará funkce skrývá v Excelu.
Funkce DATEDIF zbyla z Lotus. Přestože pomoc Excel o této funkci nemluví, je to skvělý způsob, jak najít rozdíl mezi dvěma daty.
Syntaxe je =DATEDIF(EarlierDate,LaterDate,Code)
Zde jsou platné hodnoty, které můžete použít pro kód.
- Y - řekne vám počet celých let mezi těmito dvěma daty.
- YM - řekne vám počet celých měsíců, s výjimkou let, mezi těmito dvěma daty.
- MD - řekne vám počet celých dnů, s výjimkou celých měsíců, mezi těmito dvěma daty.
- M - řekne vám počet celých měsíců. Například jsem naživu 495 měsíců
- D - řekne vám počet dní. Například jsem naživu 15 115 dní. Toto je triviální použití, protože můžete jednoduše odečíst jedno datum od druhého a formátovat jako číslo, abyste tento kód duplikovali.
Užitečné kódy jsou první tři kódy. Na výstavě jsem předvedl tento list. Stejné vzorce ve sloupcích D, E a F vypočítají DATEDIF v letech, měsících a dnech.
Vzorec ve sloupci G to spojí dohromady a vytvoří text s délkou času v letech, měsících a dnech.
Můžete to zkombinovat do jednoho vzorce. Pokud buňka A2 obsahuje datum připojení, použijte následující vzorec v B2:
=DATEDIF($A2,TODAY(),"Y")&" years, "&DATEDIF($A2,TODAY(),"YM")&" months & "&DATEDIF($A2,TODAY(),"MD")&" days"
Součet viditelných buněk
Přidejte funkci SUM pod databázi a poté použijte automatický filtr k filtrování databáze. Excel nepříjemně zahrne do součtu skryté řádky!
Místo toho postupujte takto:
- Pomocí rozbalovací nabídky Automatický filtr Data - Filtr - Automatický filtr.
- Vyberte filtr pro jedno pole
- Přejděte do prázdné buňky pod jedním z číselných sloupců v databázi.
- Klikněte na řecké písmeno E (Sigma) na standardním panelu nástrojů. Místo zadávání
=SUM()bude Excel zadávat=SUBTOTAL()a používat kódy, aby zabránil zahrnutí skrytých řádků.
Klávesová zkratka pro opakování posledního příkazu
Klávesa F4 zopakuje poslední příkaz, který jste provedli.
Vyberte například buňku a kliknutím na ikonu B buňku zvýrazněte.
Nyní vyberte jinou buňku a stiskněte klávesu F4. Excel tuto buňku zvýrazní.
F4 si zapamatuje poslední příkaz. Můžete tedy vytvořit buňku kurzívou a potom použít F4 k vytvoření mnoha buněk kurzívou.
Předběžně vyberte rozsah buněk, které chcete zadat
V knize vám ukážu, jak používat Nástroje - Možnosti - Upravit - Přesunout výběr po zadání směru - Právo vynutit Excel, aby se po stisknutí klávesy Enter posunul doprava. To je dobré, když musíte zadávat data probíhající napříč řádkem.
To je zvláště užitečné, pokud zadáváte čísla na numerické klávesnici. Trik vám umožňuje zadat 123 Enter a skončit v další buňce. Když budete mít ruce na numerické klávesnici, můžete zadávat čísla rychleji.
Někdo navrhl vylepšení této techniky. Předběžně vyberte rozsah, ve kterém budete zadávat data. Výhodou je, že když se dostanete do posledního sloupce a stisknete Enter, Excel přeskočí na začátek dalšího řádku.
Na obrázku níže se stisknutím klávesy Enter přesunete do buňky B6.
Ctrl + přetáhněte rukojeť výplně
V show jsem mnohokrát ukázal trik Fill Handle. Zadejte pondělí do A1. Pokud vyberete buňku A1, bude v pravém dolním rohu buňky čtvercová tečka. Tato tečka je rukojeť výplně. Klikněte na úchyt výplně a táhněte dolů nebo doprava. Excel vyplní úterý, středa, čtvrtek, pátek, sobotu, neděli. Pokud přetáhnete více než 7 buněk, Excel se v pondělí začne znovu.
Excel je opravdu dobrý. Může automaticky rozšířit všechny tyto řady:
- Pondělí - úterý, středa, čtvrtek, pátek atd.
- Leden - únor, březen, duben atd.
- Leden - únor, březen atd.
- Q1 - Q2, Q3, Q4 atd.
- Qtr 1 - Qtr 2, Qtr 3, Qtr 4, Qtr 1 atd.
- 1. období - 2. období, 3. období, 4. období atd.
- 23. října 2006 - 24. října 2006, 25. října 2006 atd.
Protože Excel dokáže VŠECHNY tyto úžasné řady, co byste očekávali, kdybyste zadali 1 a přetáhli úchyt výplně?
Můžete očekávat, že dostanete 1, 2, 3,…
Ale opravdu dostanete 1, 1, 1, 1, 1, …
Kniha hovoří o spletité metodě. Zadejte 1 do A1. Zadejte 2 do A2. Vyberte A1: A2. Přetáhněte úchyt výplně. Existuje lepší způsob.
Jednoduše zadejte 1 do A1. Ctrl + Přetáhněte úchyt výplně. Excel vyplní 1, 2, 3. Zdá se, že když držíte Ctrl, přepíše normální chování popisovače výplně.
Někdo na semináři řekl, že by chtěl zadat datum, přetáhnout datum a nechat Excel zachovat stejné datum. Pokud podržíte Ctrl při tažení úchytu výplně, přepíše Excel normální chování (s přírůstkem data) a ve všech buňkách vám dá stejné datum.
