Az üzleti adatok elemzése gyakran megköveteli, hogy az Excelben dátumértékekkel dolgozzon, hogy megválaszolja az olyan kérdéseket, mint például: „mennyi pénzt kerestünk ma” vagy „milyen arány a múlt hét azonos napjához képest?” Ez pedig nehéz lehet, ha az Excel nem ismeri fel dátumként az értékeket.
Sajnos ez nem szokatlan, különösen akkor, ha több felhasználó írja be ezeket az információkat, másol és illeszt be más rendszerekből, valamint adatbázisokból importál.
Ebben a cikkben négy különböző forgatókönyvet és a szöveg dátumértékekké alakításának megoldásait ismertetjük.
Tartalomjegyzék
Pontot/pontot tartalmazó dátumok
Valószínűleg az egyik leggyakoribb hiba, amit a kezdők elkövetnek, amikor dátumokat írnak be az Excelbe, hogy ezt a pont karakterrel teszik a nap, a hónap és az év elválasztására.
Az Excel nem ismeri fel ezt dátumértékként, és továbbmegy, és szövegként tárolja. Ezt a problémát azonban megoldhatja a Keresés és csere eszközzel. Ha a pontokat perjelekre (/) cseréli, az Excel automatikusan dátumként azonosítja az értékeket.
Jelölje ki azokat az oszlopokat, amelyeken a keresést és a cserét szeretné végrehajtani.
Kattintson a Kezdőlap > Keresés és kijelölés > Csere elemre, vagy nyomja meg a Ctrl+H billentyűkombinációt.
A Keresés és csere ablakban írjon be egy pontot (.) a „Mit keres” mezőbe, és egy perjelet (/) a „Csere erre” mezőbe. Ezután kattintson az „Összes cseréje” gombra.
Az összes pontot perjellé alakítja, és az Excel dátumként ismeri fel az új formátumot.
Ha a táblázat adatai rendszeresen változnak, és automatizált megoldást szeretne erre a forgatókönyvre, használhatja a SUBSTITUTE funkciót.
=VALUE(SUBSTITUTE(A2,".","https://www.wdzwdz.com/"))
A SUBSTITUTE függvény szöveges függvény, így önmagában nem tudja dátummá alakítani. Az ÉRTÉK függvény a szöveges értéket számértékké alakítja.
Az eredményeket az alábbiakban mutatjuk be. Az értéket dátumként kell formázni.
Ezt a „Kezdőlap” lap „Számformátum” listája segítségével teheti meg.
Tipikus a ponthatároló példája. De ugyanazt a technikát használhatja bármely határoló karakter helyettesítésére vagy helyettesítésére.
ééééhhnn formátum konvertálása
Ha az alábbi formátumban kapja meg a dátumokat, akkor más megközelítést igényel.
Ez a formátum meglehetősen szabványos a technológiában, mivel megszünteti a kétértelműséget azzal kapcsolatban, hogy a különböző országok hogyan tárolják a dátumértékeiket. Az Excel azonban kezdetben nem fogja megérteni.
A gyors kézi megoldáshoz használhatja a Szöveg oszlopokba funkciót.
Válassza ki a konvertálni kívánt értéktartományt, majd kattintson az Adatok > Szöveg oszlopokká menüpontra.
Megjelenik a Szöveg oszlopokba varázsló. Kattintson a „Tovább” gombra az első két lépésnél, hogy a harmadik lépésnél legyen, amint az az alábbi képen látható. Válassza a Dátum lehetőséget, majd válassza ki a listából a cellákban használt dátumformátumot. Ebben a példában YMD formátummal van dolgunk.
Ha képlet megoldást szeretne, akkor a dátum függvényt használhatja a dátum összeállításához.
Ezt a Left, Mid és Right szövegfüggvények mellett használnák a dátum három részének (nap, hónap, év) cellatartalomból való kivonására.
Az alábbi képlet ezt a képletet mutatja a mintaadataink alapján.
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
Ezen technikák bármelyikével bármilyen nyolcjegyű számértéket konvertálhat. Például előfordulhat, hogy a dátumot nnpphééé vagy hhnnéé formátumban kapja meg.
DATEVALUE és VALUE függvények
Néha a problémát nem egy határoló karakter okozza, hanem kényelmetlen dátumszerkezettel rendelkezik, egyszerűen azért, mert szövegként van tárolva.
Az alábbiakban a dátumok listája látható különféle szerkezetekben, de számunkra mindegyik dátumként felismerhető. Sajnos ezeket szövegként tárolták, és konvertálni kell.
Ezeknél a forgatókönyveknél könnyen konvertálható különféle technikákkal.
Ebben a cikkben két funkciót szerettem volna megemlíteni ezeknek a forgatókönyveknek a kezelésére. Ezek DATEVALUE és VALUE.
A DATEVALUE függvény a szöveget dátumértékké alakítja (valószínűleg látta, hogy jön), míg a VALUE függvény a szöveget általános számértékké alakítja. A köztük lévő különbségek minimálisak.
A fenti képen az egyik érték időinformációt is tartalmaz. Ez pedig a funkciók kisebb különbségeinek bemutatása lesz.
Az alábbi DATEVALUE képlet mindegyiket dátumértékké konvertálja.
=DATEVALUE(A2)
Figyelje meg, hogyan lett eltávolítva az idő a 4. sor eredményéből. Ez a képlet szigorúan csak a dátum értékét adja vissza. Az eredményt továbbra is dátumként kell formázni.
A következő képlet az ÉRTÉK függvényt használja.
=VALUE(A2)
Ez a képlet ugyanazokat az eredményeket adja, kivéve a 4. sort, ahol az időérték is megmarad.
Az eredmények ezután formázhatók dátumként és időként, vagy dátumként az időérték elrejtéséhez (de nem távolíthatók el).