Az üzleti adatok elemzése gyakran munkát igényela dátumértékekkel az Excelben, és olyan kérdések megválaszolására szolgálnak, mint „mennyit keresettünk ma” vagy „hogyan lehet összehasonlítani a múlt hét ugyanazon napjával?”, és ez nehéz lehet, amikor az Excel nem ismeri el az értékeket dátumként.
Sajnos ez nem szokatlan, különösen akkor, ha több felhasználó gépelte ezeket az információkat, másolja és beilleszti más rendszerekből, és importálja az adatbázisokból.
Ebben a cikkben négy különféle forgatókönyvet és megoldásokat írunk le a szöveg dátumértékre konvertálására.
Teljes leállítást / időszakot tartalmazó dátumok
A kezdők valószínűleg az egyik leggyakoribb hiba, amikor a dátumokat Excelbe gépelik, és a nap, a hónap és az év elválasztásakor a stop pontot használja.
Az Excel ezt nem ismeri fel dátumértékként ésmegy előre, és szövegesen tárolja. Ezt a problémát azonban a Megkeresés és csere eszköz segítségével oldhatja meg. A teljes pontok perjelzéssel (/) történő felváltásával az Excel automatikusan azonosítja az értékeket dátummal.
Válassza ki azokat az oszlopokat, amelyeken végrehajtja a keresést és a cserét.
Kattintson a Kezdőlap> Keresés és kiválasztás> Csere –re, vagy nyomja meg a Ctrl + H gombot.
A Keresés és cseréje ablakba írja be a pontot (.) A „Keresd meg” mezőbe, és perjel (/) a „Cserélje ki” mezőbe. Ezután kattintson az „Összes cseréje” elemre.
Az összes teljes pontot perjelzéssel konvertálják, és az Excel az új formátumot dátumként ismeri fel.
Ha a táblázati adatok rendszeresen megváltoznak, és ehhez a forgatókönyvhöz automatizált megoldást szeretne, használhatja a SUBSTITUTE funkciót.
=VALUE(SUBSTITUTE(A2,".","/"))
A SUBSTITUTE funkció szöveges függvény, így nem tudja konvertálni egy dátumra. Az ÉRTÉK funkció konvertálja a szöveg értékét numerikus értékre.
Az eredményeket az alábbiakban mutatjuk be. Az értéket dátumként kell formázni.
Ezt megteheti a „Kezdőlap” fül „Számformátum” listájával.
Jellemző a példa a pontmegszakítóra. De ugyanazt a technikát alkalmazhatja bármely elválasztó karakter cseréjére vagy helyettesítésére.
Az ééééhhh formátum konvertálása
Ha az alább látható formátumban kap dátumokat, akkor más megközelítésre lesz szükség.
Ez a formátum meglehetősen szabványos a technológiában, mivel kiküszöböli a kétértelműséget abban, hogy a különböző országok hogyan tárolják dátumértékeiket. Az Excel azonban kezdetben nem fogja megérteni.
A gyors kézi megoldáshoz használhatja a Szöveg oszlopokhoz lehetőséget.
Válassza ki a konvertálni kívánt értéktartományt, majd kattintson az Adatok> Szöveg oszlopokba elemre.
Megjelenik a Szöveg oszlopokhoz varázsló. Kattintson a „Next” elemre az első két lépésben, hogy a harmadik lépésben tartsa, amint az az alábbi képen látható. Válassza a Dátum lehetőséget, majd válassza ki a listában a cellákban használt dátumformátumot. Ebben a példában egy YMD formátummal foglalkozunk.
Ha képletmegoldást szeretne, akkor a Dátum függvényt használhatja a dátum felépítéséhez.
Ezt a Bal, Közép és Jobb szöveges funkciók mellett használnák a dátum három részének (nap, hónap, év) kivonására a cella tartalmából.
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ármely nyolc számjegyű számot konvertálhat. Például előfordulhat, hogy a dátum héééééé formátumban vagy hhhééééé formátumban érkezik.
DATEVALUE és VALUE függvények
Időnként a problémát nem egy elválasztó karakter okozza, hanem egy kellemetlen dátumszerkezetű, egyszerűen azért, mert szövegesen tárolja.
Az alábbiakban felsoroljuk a különböző struktúrákban található dátumokat, de mindannyian felismerhetők dátumként. Sajnos szöveges formátumban tárolódtak, és konvertálni kell.
Ezekre a forgatókönyvekre könnyen konvertálható különféle technikákkal.
A cikkhez két funkciót akartam megemlíteni e forgatókönyvek kezelésére. Ezek DATEVALUE és VALUE.
A DATEVALUE függvény konvertálja a szöveget dátumértékre (valószínűleg látta, hogy ez jön), míg az ÉRTÉK funkció konvertálja a szöveget általános számértékké. A különbségek közöttük minimálisak.
A fenti képen az egyik érték időinformációkat is tartalmaz. És ez demonstrálja a funkciók kisebb különbségeit.
Az alábbi DATEVALUE képlet mindegyikét dátumértékre konvertálja.
=DATEVALUE(A2)
Figyelje meg, hogyan távolították el az időt 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 formázni kell dátumként.
A következő képlet az ÉRTÉK funkciót használja.
=VALUE(A2)
Ez a képlet ugyanazokat az eredményeket fogja eredményezni, kivéve a 4. sort, ahol az időértéket is fenntartják.
Az eredmények 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).
