Ian v Nashvillu získává data každý den ze stahování systému. Sloupec data obsahuje Datum + Čas. Díky tomu má kontingenční tabulka několik řádků denně namísto souhrnu jedné buňky denně.
Tento článek ukazuje tři způsoby, jak problém vyřešit.
Na následujícím obrázku je sloupec Datum s Datum a čas.
Jedním z řešení je opravit to v kontingenční tabulce.
- Přetáhněte pole Datum do oblasti Řádky. Zobrazí se s datem + čas.
- Vyberte libovolnou buňku, která obsahuje datum a čas.
- Na kartě Analýza nástrojů kontingenční tabulky zvolte Skupinové pole.
- V dialogu Seskupení vyberte Dny. Zrušte výběr měsíců. Když kliknete na OK, v kontingenční tabulce se zobrazí jeden řádek denně.
Dalším zdlouhavým řešením je přidání nového sloupce po stažení. Jak je znázorněno na tomto obrázku, vzorec ve sloupci J je =INT(D2).
Datum + čas je celočíselný den následovaný desetinným časem. Pomocí funkce INT získáte celočíselnou část data + času a izolujete datum.
To bude fungovat dobře, ale nyní musíte při každém stahování dat přidat nový sloupec.
Mým preferovaným řešením je použít Power Query. Power Query funguje pouze v edicích Windows pro Excel. Power Query nelze používat v systémech Android, IOS nebo Mac.
Power Query je integrován do Excelu 2016. Pro Excel 2010 a 2013 si můžete stáhnout Power Query od Microsoftu.
Nejprve převeďte stažená data na tabulku pomocí Ctrl + T. Poté na kartě Data vyberte možnost Z tabulky / rozsahu.
V mřížce Power Query zvolte sloupec Datum. Přejděte na kartu Transformace. Otevřete rozevírací seznam Typ data a vyberte Datum. Pokud se zobrazí zpráva o nahrazení kroku nebo o novém kroku, zvolte možnost Nahradit krok.
Na kartě Domů v Power Query zvolte Zavřít a načíst.
Výsledek se zobrazí na novém listu. Z tohoto listu vytvořte kontingenční tabulku.
Až příště získáte nová data, postupujte takto:
- Vložte nová data přes původní sadu dat.
- Přejít na list s výsledkem Power Query. Rozšířte panel Dotaz, abyste viděli Obnovit. Klikněte na Obnovit.
- Obnovte kontingenční tabulku.
Sledovat video
Přepis videa
Naučte se Excel z Podcastu, epizoda 2203: Zkrátit datum a čas pouze na datum.
Hej, vítej zpět na netcastu, jsem Bill Jelen. Dnešní otázka od Iana na mém semináři Nashville Power Excel. Podívejte se na toto: Ian stahuje ze svého systému každý den a má - ve sloupci Datum - má Datum a čas, a to dělá věci dokola, protože když Ian vytvoří kontingenční tabulku a umístí spolu data na levé straně, místo toho, aby dostal pouze jeden řádek za Datum, získává jeden řádek za každé Datum a Čas.
Nyní by bylo možné přijít sem a říct, seskupit pole a seskupit to do dne, klikněte na OK. A víte, je to pár kliknutí navíc, o kterých si nemyslím, že to musíme udělat. A tak se u tohoto Podcastu, několika příštích Podcastů, podíváme na Power Query. Nyní je integrována do aplikace Excel 2016 zde, v části Získat a transformovat data. Pokud máte 2010 nebo 2013 spuštěný ve Windows, ne na Macu, můžete si Power Query stáhnout zdarma z Excelu. A Power Query chce pracovat na tabulce nebo rozsahu.
Takže si vyberu jednu buňku dat, stiskněte Ctrl + T pro Table, perfektní. A pak na kartě Power Query, Data, Z tabulky / rozsahu, vyberu sloupec Datum a Transformovat a řeknu, že chci, aby to nebylo Datum a čas, ale jen Datum. Vyberu Nahradit aktuální a poté Domů, Zavřít a načíst a dostaneme zcela nový formulář listu, který má převedená data. Nyní odtud samozřejmě můžeme shrnout pomocí otočného čepu a bude to perfektní.
Výhodou této metody, Power Query, je, když Ian získá více dat … Takže tady mám nová data, vezmu si tato nová data, zkopíruji data a prostě sem přijdu ke svým starým datům a vložte hned níže. A všimněte si, že konec značky tabulky právě teď je v řádku 474. Když vložím, konec značky tabulky se posune dolů do spodní části nových dat. V pořádku? A tento dotaz je napsán na základě tabulky. V pořádku? Jak tedy stůl roste.
Nyní je zde jeden problém, pokud používáte Power Query poprvé, Power Query začíná tak široký - pouze napůl široký - a nevidíte ikonu Obnovit. Musíte to sem takto přetáhnout a získat tu skvělou ikonu Obnovit a jednoduše kliknout na Obnovit. Právě teď máme načteno 473 řádků a nyní načteno 563 řádků. Vytvořte si kontingenční tabulku a život je skvělý.
Hej, podívej, vím, že by bylo možné přidat nový sloupec sem, = INT (D2) - - dne - zkopírovat to dolů, zkopírovat a vložit speciální hodnoty. Ale pak jste na háku, že to děláte každý den. V pořádku? Takže i když v tomto videu o řešení jsme mluvili alespoň o třech různých způsobech, pro mě je cesta Power Query a právě možnost kliknout na Obnovit.
Power Query, mluvím o tom ve své nové knize MrExcel LIVe, 54 největších excelových tipů všech dob.
Shrnutí dnešní epizody: Ian v Nashvillu získává data ze systému každý den, sloupec Datum mrzutě obsahuje jak Datum, tak Čas - zkracuje jeho kontingenční tabulky, takže musí… No, tři možné věci: Jeden, použít funkce INT, kopírování, vkládání hodnot; za druhé, nechte to jako datum a čas a poté seskupte kontingenční tabulku podle data; nebo, lepší řešení, Power Query, vytvoří staženou datovou sadu do tabulky pomocí Ctrl + T a poté na kartě Data zvolte Z tabulky, vyberte sloupec Datum a čas, přejděte na kartu Transformace v Power Query, převeďte to to a Date, Close & Load, build the pivot table from that. Až příště získáte data, vložte je do původní tabulky, přejděte na panel Dotazy, klikněte na Obnovit a můžete začít.
Chcete-li sešit stáhnout z dnešního videa, přejděte na adresu URL v popisu YouTube.
Chci poděkovat Ianovi za účast na mém semináři v Nashvillu, chci vám poděkovat za návštěvu. Uvidíme se příště na dalším netcastu z.
Stáhněte si soubor Excel
Stažení souboru aplikace Excel: date-time-to-date.xlsx
Chcete-li dále vylepšit proces Power Query:
- Ujistěte se, že sešit pro stahování je vždy uložen ve stejné složce se stejným názvem.
- Začněte z prázdného sešitu. Uložit jako s názvem jako DownloadedFileTransformed.xlsx
- Z prázdného sešitu Data, ze souboru, ze sešitu. Ukažte na soubor z kroku 1.
- Zvolte Vyčistit data. Transformujte datum a čas na datum. Zavřít a načíst.
- Pravým tlačítkem klikněte na Dotazovací panel a vyberte Vlastnosti. Zvolte Obnovit data při otevírání souboru.
- Každý den jděte do práce. Vezměte si kávu. Ujistěte se, že je ve složce nový soubor pro # 1.
- Otevřete svůj sešit DownloadFileTransformed.xlsx. Bude mít nová data a data budou správná.
Excel myšlenka dne
Požádal jsem své přátele Excel Master o radu ohledně Excelu. Dnešní myšlenka k zamyšlení:
„Ověření je tvůj přítel.“
Duane Aubin
