ma bemutatom, hogyan lehet az adatkészletet Excel-ben dátum szerint rendezni.
az Excel használata közben gyakran dátumokkal kell dolgoznunk. A beszúrástól a manipulációig, és mindentől, ami a kettő között van. Kapunk egy adatkészletet, ahol az adatokat a dátumok szerint kell rendeznünk, növekvő vagy csökkenő sorrendben.
Bővebben: Excel Rendezés dátum és idő szerint, és hogyan rendezheti a dátumokat az Excel-ben Év szerint (4 egyszerű módszer)
ma megmutatom, hogyan lehet ezt kijavítani.
gyakorlati munkafüzet letöltése
itt van egy adathalmaz, amely tartalmazza a Mars Group nevű vállalat egyes alkalmazottainak azonosítóit, nevét és csatlakozási dátumát.
célunk ma az lesz, hogy az alkalmazottakat növekvő sorrendben rendezzük csatlakozási dátumuk szerint.
Rendezés dátum szerint a rendezés & szűrés az Excel eszköztárból
először is a rendezés & szűrő szakasz az Excel eszköztárból.
válassza ki a teljes adatkészletet, és lépjen a Home>Rendezés & szűrő opcióra az Excel eszköztárban.
kattintson a legördülő menüre. Lesz néhány lehetőség. Válassza Az Egyéni Rendezés Lehetőséget.
megjelenik az egyéni Rendezés párbeszédpanel.
Olvass tovább: Egyéni rendezési lista létrehozása Excel-ben
A rendezés oszlop opció alatt válassza ki azt az oszlopot, amely szerint rendezni szeretné. A példa kedvéért válassza a Csatlakozás dátumát.
ezután a rendezés opció alatt válassza a cellaértékeket.
A rendelés opciónál válassza a növekvő vagy csökkenő sorrendet. A példa kedvéért válassza a rendezés növekvő lehetőséget.
majd kattintson az OK gombra.
A teljes adathalmazt a csatlakozási dátumok szerint növekvő sorrendben találja, így:
és persze, rendezheti őket csökkenő sorrendben is, ha szeretné.
kövesse ugyanezt az eljárást.
Rendezés dátum szerint a rendezési funkció használatával
az előző szakaszban már egyszer rendeztük a teljes adatkészletet.
de mi van akkor, ha valaki nem akarja megváltoztatni az eredeti adatkészletet, és csak egy rendezett adatkészlet másolatát akarja létrehozni egy másik helyen?
ezt az Excel rendezési funkciójával használhatja.
válasszon ki egy cellát, és illessze be ezt a képletet:
=SORT(B4:D19,3,1,FALSE)
rendezés funkcióval az adatkészlet rendezett másolatát kapja meg.
itt a csatlakozási dátumok szerint növekvő sorrendben rendeztük.
A
- képlet magyarázata a rendezési függvény négy argumentumot tartalmaz: array, sort_index, sort_order és by_col.
- a tömb a rendezni kívánt cellatartomány. Itt szeretnénk rendezni a teljes adathalmazt, tehát tömbünk B4: D19.
- Sort_index annak a sornak vagy oszlopnak a száma, amely szerint rendezni szeretné a tömböt.
itt szeretnénk rendezni a csatlakozási dátumok szerint, amely a tömb 3.oszlopa.
így a sort_indexünk 3.
- Sort_order határozza meg, hogy növekvő vagy csökkenő sorrendben szeretne rendezni. 1 a növekvő sorrendben, és -1 a csökkenő sorrendben.
itt növekvő sorrendben szeretnénk rendezni. 1-et használtunk. Használja a sajátját.
- By_col megmondja, hogy soronként vagy oszloponként szeretné-e rendezni. Igaz, ha oszloponként szeretné, hamis, ha soronként szeretné.
itt szeretnénk rendezni az adatokat soronként.
tehát hamis.
- ezért a teljes képlet
SORT(B4:D19,3,1,FALSE)lesz. A B4:D19 cellatartományt növekvő sorrendben rendezi a 3. oszlop szerint.
Rendezés dátum szerint a SORTBY függvény használatával
most felteheti a kérdést:
mi van, ha nem a teljes adatkészletet, csak az azonosítókat és neveket akarom rendezni, hanem a csatlakozási dátumok szerint?
ezt nem lehet elérni a rendezés funkcióval.
de ezt az Excel SORTBY függvényével is végrehajthatja.
válasszon ki egy cellát, és illessze be ezt a képletet:
=SORTBY(B4:C19,D4:D19,1)
SORTBY függvényével az alkalmazotti azonosítókat és az alkalmazottak nevét növekvő sorrendben rendezi, a csatlakozási dátumoknak megfelelően.
A képlet magyarázata
- a SORTBY függvény három argumentumot tartalmaz: array, by_array és sort_order.
- a tömb a rendezni kívánt cellák tartománya. Szeretnénk rendezni a két oszlopot, az alkalmazott azonosítóját és az alkalmazott nevét.
tehát tömbünk B4:C19.
- By_array az az oszlop, amely szerint rendezni szeretné. Lehet, hogy a tömbön belül vagy kívül van.
a csatlakozási dátumok szerint szeretnénk rendezni. Tehát a by_array D4: D19.
- Sort_order az a sorrend, amelyben rendezni szeretné. 1 növekvő sorrendben, -1 csökkenő sorrendben.
növekvő sorrendben szeretnénk rendezni. 1-et használtunk. Használja a sajátját.
- ezért a teljes képlet lesz
SORTBY(B4:C19,D4:D19,1). A B4:C19 cellatartományt a D4:D19 oszlop szerint növekvő sorrendben rendezi.
A teljes adatkészletet a SORTBY függvény segítségével is rendezhette volna.
rendezés hónapok szerint a SORTBY és a MONTH függvény használatával
eddig csak teljes dátumok szerint rendeztük.
az adatkészletet kifejezetten hónapok szerint is rendezhetjük az Excel SORTBY és MONTH függvényeivel.
ha az adatkészletet a csatlakozási dátumok hónapjai szerint szeretné rendezni, válasszon ki egy új cellát, majd illessze be ezt a képletet:
=SORTBY(B4:D19,MONTH(D4:D19),1)
SORTBY és MONTH függvény használatával az adathalmazt növekvő sorrendben találja a munkavállalók csatlakozási dátumainak hónapjai szerint rendezve.
A
- képlet magyarázata itt a rendezendő tömb a B4:D19 tartomány.
- a by_array argumentum a
MONTH(D4:D19)képlet által visszaadott tömb. Visszaadja a csatlakozási dátumok hónapszámát. - a sort_order 1, mert növekvő sorrendben rendezünk. A csökkenő sorrendben történő rendezéshez használja a -1-et.
- tehát a
SORTBY(B4:D19,MONTH(D4:D19),1)képlet a B4:D19 adatkészletet a D4 dátumok hónapjai szerint rendezi:D19, növekvő sorrendben.
Rendezés napok szerint a SORTBY és DAY függvények használatával
ha szeretné, használhatja a SORTBY és a DAY függvény kombinációját az adathalmaz rendezéséhez az összekapcsolási dátumok nap szakasza szerint.
tehát itt van, hogyan fogunk írni képlet:
=SORTBY(B4:D19,DAY(D4:D19),1)
rendezés évek szerint a SORTBY and YEAR függvény
az adatkészletet csak az összekapcsolási dátumok éve szerint rendezheti. A képlet a következő lesz:
=SORTBY(B4:D19,YEAR(D4:D19),1)
az adatkészletet az évek szerint növekvő sorrendben rendezzük.
Rendezés napok és hónapok szerint a SORTBY, DAY és MONTH függvények használatával
ezúttal új adathalmazunk van.
megvan a Napraforgó Óvoda nevű iskola néhány tanulójának személyazonossága, neve és születésnapja.
Most meg fogjuk rendezni a adatok beállítása szerint napok, hónapok, a születésnapok, a diákok, növekvő sorrendben.
ha például három Születésnap 10-Aug-2008, 5-Mar-2010 és 12-Dec-2009, a sorozat 5-Mar-2010, 10-Aug-2008 és 12-Dec-2009 lesz.
ehhez válasszuk az új cellába illessze be ezt a képletet:
=SORTBY(B4:D19,MONTH(D4:D19)+(DAY(D4:D19)/100))
az adatkészletet a növekvő születésnapok szerint rendezzük.
A képlet magyarázata
- itt a rendezendő tömb B4:D19.
- a by_array argumentum a
MONTH(D4:D19)+(DAY(D4:D19)/100)képlet által visszaadott tömb.
osztja a napokat 100-zal, majd hozzáadja azokat a hónapokkal.
például 2-Jan lesz 1 (január)+(2/100)=1.02
16-Jan lesz 1,16
25-Február lesz 2,25
9-December lesz 12,09
ily módon az év első napján lesz a legkisebb érték, még akkor is, ha két dátumnak ugyanaz a hónapja van.
és az év utolsó napja lesz a legnagyobb érték, még akkor is, ha két dátumnak ugyanaz a hónapja
- a sort_order 1, mert növekvő sorrendben rendezünk. A csökkenő sorrendben történő rendezéshez használja a -1-et.
- tehát a
SORTBY(B4:D19,MONTH(D4:D19)+(DAY(D4:D19)/100))képlet a B4:D19 adatkészletet csak a D4:D19 dátumok hónapjai és napjai szerint rendezi növekvő sorrendben.
Rendezés dátum szerint az INDEX-MATCH, ROW és RANK függvények használatával
most nézze meg ezt az új adatkészletet.
már majdnem ugyanaz, mint az első adathalmaz, de egy új oszlop fizetés került be a munkavállaló neve és a Csatlakozás dátuma.
most mi van, ha nem az összes oszlopot szeretnénk rendezni, csak az alkalmazott azonosítóját, az alkalmazott nevét és a Csatlakozás dátumát a Csatlakozás dátuma szerint?
ezt nem lehet végrehajtani az Excel rendezés vagy SORTBY funkciójával.
A SORT and SORTBY függvények használatával csak a szomszédos oszlopokat rendezheti, a nem szomszédos oszlopokat nem.
ez azt jelenti, hogy rendezheti a neveket, a fizetéseket és a csatlakozási dátumokat. Vagy az azonosítókat, neveket és fizetéseket. Vagy a neveket és a fizetéseket.
de nem az azonosítókat, neveket és a csatlakozás dátumát.
az Excel INDEX–MATCH, ROW és RANK függvényeinek kombinációjával rendezheti a nem szomszédos oszlopokat.
További információ: oszlop rendezése érték szerint Excel-ben
az azonosítók, nevek és csatlakozási dátumok rendezéséhez a csatlakozási dátumok szerint növekvő sorrendben válasszon ki egy új cellát, és illessze be ezt a képletet:
=INDEX(B4:E19,MATCH(ROW(A1:A16),RANK(E4:E19,E4:E19,1),0),{1,2,4})
A munkavállalói azonosítókat, az alkalmazottak nevét és a csatlakozási dátumokat növekvő sorrendben, a csatlakozási dátumok szerint rendezi.
A képlet magyarázata
-
ROW(A1:A16)egy tömböt ad vissza 1-től 16-ig terjedő számokkal, például {1, 2, 3, …, 16}. Vettem 1 hogy 16 Mert minden az én oszlopok 16 értékek (B4:B19).
használja a one-t.
-
RANK(E4:E19,E4:E19,1)egy tömböt ad vissza, amely növekvő sorrendben tartalmazza az E4:E19 oszlop egyes celláinak rangját. Ahhoz, hogy csökkenő sorrendben, használja 0 helyett 1. -
MATCH(ROW(A1:A16),RANK(E4:E19,E4:E19,1),0)visszaadja a tömb egyes számainak helyzetét {1, 2, 3, …, 16} A RANK függvény által visszaadott tömbben.
ez azt jelenti, hogy először növekvő sorrendben adja vissza az eredeti adatkészlet 1.dátumának sorszámát.
ezután visszaadja a 2. dátum sorszámát.
majd a 3.dátum.
így visszatér a 16. dátumig.
- végül a
INDEX(B4:E19,MATCH(ROW(A1:A16),RANK(E4:E19,E4:E19,1),0),{1,2,4})az 1, 2 és 4 oszlopot (azonosító, név és összekapcsolási dátumok) adja vissza a B4:E19 tartományból növekvő sorrendben.
bármely más oszlop rendezéséhez állítsa be a képletet ennek megfelelően.
Megjegyzés: A rendezés és a SORTBY funkciók csak az Office 365-ben érhetők el. Tehát, ha nincs Office 365-előfizetése, akkor ezt az eljárást kell használnia.
problémák elhárítása, amelyekkel szembesülhet a dátum szerinti rendezéshez az Excelben
miközben az Excelben a dátumokkal dolgozik, néhány gyakori problémával szembesülünk.
az Excel nem ismeri fel a dátumot dátumként
ez a leggyakoribb probléma. Néha beillesztünk egy dátumot az Excel cellájába a szokásos módon (HH-NN-ÉÉÉÉ vagy nn-hh-éééé), de az Excel nem ismeri fel dátumként.
A probléma megoldásához használja az Excel DÁTUMFÜGGVÉNYÉT a dátum manuális beszúrása helyett.
ha például 2-május-1996-ot szeretne beilleszteni egyes cellákba, használja a dátumot(1996,5,2), nem pedig 05-02-96.
és hogyan lehet tudni, hogy az Excel felismerte-e a dátumot dátumként vagy sem?
könnyű. Ha a dátum alapértelmezés szerint jobbra van igazítva a cellában, az Excel dátumként ismerte fel.
de ha balra van igazítva, az Excel nem.
az alábbi képen az Excel az elsőt dátumként ismeri fel, a másodikat azonban nem.
hiba a képletekkel, amelyek dátuma
ez szintén gyakori probléma. Amikor egy dátumot közvetlenül beillesztünk egy képletbe, az Excel hibát jelenít meg.
A probléma megoldásához ne használjon dátumot közvetlenül a képletben hagyományos módon. Használja a dátum függvényt dátum beszúrásához egy képletbe.
például ne használja a =MONTH(05-02-96) – et.
használat =MONTH(DATE(1996,5,2))
összezavarodunk, amikor egy dátumot Általános formátumban látunk
néha az Excel felismeri a dátumot dátumként, de összezavarodunk, ha általános formátumban látjuk (ami alapértelmezés szerint be van állítva).
például a dátum 2-május-1996 alapértelmezés szerint 35187 lesz (ha nem változtatja meg megfelelően a formátumot).
A probléma megoldásához válassza ki a cellát, és lépjen a Home> Számformátum az Excel eszköztárban. Ezután válassza a rövid Dátum lehetőséget.
következtetés
ezekkel a módszerekkel az Excel bármely adatkészletét dátum szerint rendezheti, mind növekvő, mind csökkenő sorrendben. Tudsz más módszert is? Vagy van kérdése? Nyugodtan kérdezzen tőlünk.
További Olvasmányok:
- Excel rendezze a dátumokat időrendi sorrendben (6 hatékony módszer)
- hogyan rendezzük vezetéknév szerint az Excelben (4 módszer)
- hogyan rendezhetünk több oszlopot az Excelben (5 Gyors megközelítés)
- hogyan rendezhetünk több oszlopot az Excelben (3 módszer)
- hogyan rendezhetünk két oszlopot az Excelben, hogy megfeleljenek (mind a pontos, mind a részleges egyezés)
- az adatok szín szerinti rendezése az Excelben (4 kritérium)
- hogyan lehet ábécé sorrendben rendezni az Excelben több oszloppal (4 módszer)