A Nashville-i Ian minden nap adatokat kap egy rendszerletöltésből. A dátum oszlop tartalmazza a Dátum + Időt. Ezáltal az elfordulási táblázat napi több sorral rendelkezik, a napi egy cella összegzése helyett.
Ez a cikk a probléma megoldásának három módját mutatja be.
Az alábbi ábra a Dátum oszlopot mutatja a Dátum plusz idővel.
Az egyik megoldás az, hogy rögzíti a forgótáblában.
- Húzza a Dátum mezőt a Sorok területre. Megjelenik a Dátum + idő.
- Jelöljön ki bármely cellát, amely dátumot és időt tartalmaz.
- A Pivot Table Tools elemzése lapon válassza a Group Field lehetőséget.
- A Csoportosítás párbeszédpanelen válassza a Napok lehetőséget. Havi kiválasztása. Az OK gombra kattintva az elfordulási táblázat naponta egy sort jelenít meg.
Egy másik unalmas megoldás egy új oszlop felvétele a letöltés után. Amint ez az ábra mutatja, a J oszlop képlete az =INT(D2).
A dátum + idő egy egész nap, amelyet tizedes idő követ. Az INT függvény használatával megkapja a dátum + idő egész részét, elkülöníti a dátumot.
Ez jól fog működni, de most minden alkalommal, amikor letöltötte az adatokat, hozzá kell adnia az új oszlopot.
Az általam preferált megoldás a Power Query használata. A Power Query csak az Excel Windows kiadásaiban működik. Nem használhatja a Power Query alkalmazást Android, IOS vagy Mac rendszeren.
A Power Query be van építve az Excel 2016-ba. Az Excel 2010 és 2013 esetében letöltheti a Power Query alkalmazást a Microsoft oldaláról.
Először konvertálja a letöltött adatokat táblázattá a Ctrl + T gombbal. Ezután az Adatok fülön válassza a Táblázat / tartomány menüpontot.
A Power Query rácsban válassza a Dátum oszlopot. Lépjen az Átalakítás fülre. Nyissa meg a Dátum típusa legördülő menüt, és válassza a Dátum lehetőséget. Ha üzenetet kap a Csere lépésről vagy az Új lépésről, válassza a Csere lépés lehetőséget.
A Power Query Főoldal lapján válassza a Bezárás és a Betöltés parancsot.
Az eredmény új lapon jelenik meg. Hozza létre a pivot táblázatot abból a munkalapból.
A következő alkalommal, amikor új adatokat kap, kövesse az alábbi lépéseket:
- Illessze be az új adatokat az eredeti adathalmazra.
- Ugrás a lapra a Power Query eredményével. Szélesítse ki a Lekérdezés panelt, hogy láthassa a Frissítés lehetőséget. Kattintson a Frissítés gombra.
- Frissítse a forgatótáblát.
Videót nézni
Videó átirat
Tanulja meg az Excel programot a Podcast 2203-as epizódjából: A dátum és idő rövidítése csak dátumra.
Hé, üdvözlöm újra a neten, Bill Jelen vagyok. A mai kérdés Ian-től a Nashville Power Excel szemináriumomon. Nézd meg ezt: Ian minden nap letölt egy letöltést a rendszeréből, és - a Dátum oszlopban - benne van a Dátum és az Idő, és ez csavarja a dolgokat, mert amikor Ian létrehoz egy kimutatótáblát, és a Dátumot a bal oldalon, ahelyett, hogy dátumonként csak egy sort kapna, minden dátumra és időre egy sort kap.
Most már lehetne idejönni, és azt mondani: Csoport mező, és ezt csoportosítani a Napra, kattintson az OK gombra. És tudod, ez plusz néhány kattintás, amelyet nem hiszem, hogy meg kellene tennünk. Tehát ehhez a Podcasthoz, a következő néhány Podcasthoz, megnézzük a Power Query-t. Most itt van beépítve az Excel 2016-ba, az Adatok letöltése és átalakítása részben. Ha a 2010-es vagy a 2013-as rendszert futtatja a Windows rendszerben, nem pedig Mac-en, akkor ingyenesen letöltheti a Power Query programot az Excelből. A Power Query pedig egy asztalon vagy tartományon akar dolgozni.
Tehát egy adatsort választok, a Ctrl + T billentyűkombinációt megnyomva a Táblázat, tökéletes. Ezután a Power Query (Adatok, Táblázatból / Tartományból) fülemen a Dátum oszlopot és az Átalakítás lehetőséget választom, és azt mondom, hogy azt akarom, hogy ez ne Dátum és Idő legyen, hanem csak Dátum. Kiválasztom az Aktuális cseréje lehetőséget, majd a Főoldal, Bezárás és betöltés lehetőséget, és kapunk egy teljesen új űrlapot, amely az adatokat konvertálja. Innentől kezdve természetesen egy forgatással összefoglalhatunk, és tökéletes lesz.
Úgy gondolom, hogy ennek a módszernek, a Power Query-nek az az előnye, amikor Ian több adatot kap … Tehát itt új adataim vannak, ezeket az új adatokat átveszem, átmásolom az adatokat, és csak ide jövök a régi adataimhoz, és illessze be alatta. És vegye észre, hogy a táblázatjelző vége jelenleg a 474. sorban van. Amikor beillesztem, a táblázatjelző vége lefelé mozog az új adatok aljára. Rendben? És ezt a lekérdezést a táblázat alapján írják. Rendben? Tehát, ahogy az asztal növekszik.
Most egy gond, hogy ha először használja a Power Query-t, akkor a Power Query olyan szélesre indul - csak félig szélesre -, és nem látja a Frissítés ikont. Tehát ide kell húznod, és be kell szerezned azt a nagyszerű Frissítés ikont, és csak kattints a Frissítés gombra. Jelenleg 473 sor van betöltve, és most 563 sor van betöltve. Készítse el forgóasztalát, és az élet nagyszerű.
Hé, nézze, tudom, hogy új oszlopot lehetett volna ide felvenni: = INT (D2) - - a dátumtól - másolja le, másolja és illessze be a Különleges értékeket. De akkor azon vagytok, hogy ezt csináljátok minden egyes nap. Rendben? Tehát bár ebben a videóban legalább háromféleképpen beszéltünk a megoldásról, számomra a Power Query, és csak a Frissítés gombra kattintás lehetősége az út.
Power Query, erről szólok új könyvemben, a MrExcel LIVe-ben, Minden idők 54 legnagyobb Excel-tippjében.
A mai epizód összefoglalása: Ian Nashville-ben minden nap adatokat kap egy rendszerből, a Dátum oszlopban bosszantóan szerepel mind a Dátum, mind az Idő - csavarja a forgatótábláit, ezért meg kell tennie… Nos, három lehetséges dolog: Egy, felhasználás az INT függvény, értékek másolása, beillesztése; másodszor, csak hagyja Dátum és Idő néven, majd csoportosítsa a pivot táblázatot Dátum szerint; vagy egy jobb megoldás, a Power Query, a letöltött adatsort táblázattá teszi a Ctrl + T billentyűkombinációval, majd az Adatok fülön válassza a Táblázatból, válassza a Dátum és idő oszlopot, lépjen az Átalakítás fülre a Power Query-ben, konvertáljon dátumra, bezárás és betöltés, ebből építse fel a pivot táblázatot. Legközelebb, amikor adatokat kap, illessze be az eredeti táblába, lépjen a Lekérdezés panelre, kattintson a Frissítés gombra, és máris mehet.
Hé, a munkafüzet letöltéséhez a mai videóból keresse fel a YouTube leírásában található URL-t.
Szeretnék köszönetet mondani Ian-nek, hogy megjelent a nashville-i szemináriumomon, szeretném megköszönni, hogy betért. Találkozunk legközelebb egy másik netcast-re.
Töltse le az Excel fájlt
Az Excel fájl letöltése: date-time-to-date.xlsx
A Power Query folyamat további javítása:
- Győződjön meg arról, hogy a letöltési munkafüzet mindig ugyanabban a mappában van, ugyanazon a néven.
- Indítsa el egy üres munkafüzetből. Mentés másként egy olyan névvel, mint a DownloadedFileTransformed.xlsx
- Az üres munkafüzetből, Adatok, Fájlból, Munkafüzetből. Mutasson a fájlra az 1. lépésben.
- Válassza az Adatok tisztítása lehetőséget. Alakítsa át a Dátum / Idő dátumra. Bezárás és betöltés.
- Kattintson a jobb gombbal a Lekérdezés panelre, és válassza a Tulajdonságok parancsot. Válassza az Adatok frissítése a fájl megnyitásakor lehetőséget.
- Minden nap menjen dolgozni. Hozd a kávét. Ellenőrizze, hogy a # 1 új fájlja van-e a mappában.
- Nyissa meg a DownloadFileTransformed.xlsx munkafüzetet. Meg lesznek az új adatok, és a dátumok helyesek lesznek.
Excel gondolata a napnak
Az Excel Master barátaimtól kértem tanácsokat az Excel-mel kapcsolatban. Mai gondolat, hogy elgondolkodjak:
- Az érvényesítés a barátod.
Duane Aubin
