Az Excelnek már van néhány módja az adatok gyors rendezésére.
Könnyedén rendezheti az adathalmazt a szalag rendezési ikonjaival vagy a rendezés párbeszédpanelen.
Akkor miért kell tudnia, hogyan kell ezt csinálni a VBA segítségével?
Az adatok rendezésének ismerete a VBA használatával hasznos lehet, ha a kód részeként szerepel. Tegyük fel például, hogy naponta/hetente kap egy adathalmazt, amelyet meghatározott sorrendben kell formáznia és rendeznie.
Létrehozhat egy makrót, hogy mindezt egyetlen kattintással elvégezze. Ez sok időt és erőfeszítést takarít meg minden alkalommal, amikor ezt megteszi.
Továbbá, ha Excel irányítópultokat hoz létre, új szintre emelheti az Excel rendezési képességét, ha lehetővé teszi a felhasználó számára, hogy csak a fejlécre duplán kattintva rendezze az adatokat (az alábbiak szerint).
Ebben az oktatóanyagban később kitérek arra, hogyan lehet ezt létrehozni. Először gyorsan tisztázzuk az alapokat.
A tartomány megértése. Rendezési módszer az Excel VBA -ban
A VBA használatával történő válogatás során a Range.Sort módszert kell használnia a kódban.
A „Tartomány” azok az adatok, amelyeket rendezni próbál. Például, ha az adatokat A1: A10 formátumban rendezi, akkor a „Tartomány” tartomány lesz („A1: A10”).
Létrehozhat egy elnevezett tartományt is, és a cellahivatkozások helyett használhatja. Például, ha létrehozok egy „DataRange” elnevezésű tartományt az A1: A10 cellákhoz, akkor használhatom a tartományt („DataRange”)
A rendezési módszerrel további információkat kell megadnia a paramétereken keresztül. Az alábbiakban ismertetjük a legfontosabb paramétereket:
- Kulcs - itt meg kell adnia a rendezni kívánt oszlopot. Például, ha rendezni szeretné az A oszlopot, akkor a következő kulcsot kell használnia: = Tartomány („A1”)
- Rendelés - itt adja meg, hogy növekvő vagy csökkenő sorrendben kívánja -e rendezni. Például, ha növekvő sorrendben szeretné rendezni a sorrendet, akkor a Rendelés: = xlNövekvő sorrendet használja
- Fejléc - itt adja meg, hogy az adathalmaz fejlécekkel rendelkezik -e vagy sem. Ha fejlécei vannak, a rendezés az adatkészlet második sorából indul, különben az első sorból indul. Annak megadásához, hogy az adatai fejléceket tartalmazzanak, használja a Fejléc: = xlIgen lehetőséget
Bár az esetek többségében ez a három elég, a paraméterekről ebben a cikkben olvashat bővebben.
Most nézzük meg, hogyan kell használni a Range.Sort módszert a VBA -ban az adatok rendezéséhez Excelben.
Egyetlen oszlop rendezése fejléc nélkül
Tegyük fel, hogy egyetlen oszlopa van fejléc nélkül (az alábbiak szerint).
Az alábbi kód segítségével növekvő sorrendbe rendezheti.
Sub SortDataWithoutHeader () tartomány ("A1: A12"). Rendezési kulcs1: = tartomány ("A1"), sorrend1: = xl növekvő, fejléc: = xl Nincs vége al
Vegye figyelembe, hogy az adattartományt manuálisan adtam meg tartományként („A1: A12”).
Abban az esetben, ha változások történnek az adatokban, és az értékek hozzáadhatók/törölhetők, használhatja az alábbi kódot, amely automatikusan kiigazodik az adathalmaz kitöltött cellái alapján.
Sub SortDataWithoutHeader () tartomány ("A1", tartomány ("A1"). End (xlDown)). Rendezési kulcs1: = Tartomány ("A1"), sorrend1: = xl növekvő, fejléc: = xl Nincs vége al
Vegye figyelembe, hogy a Range („A1: A12”) helyett a Range („A1”, Range („A1”). End (xlDown)) parancsot használtam.
Ez ellenőrzi az oszlop utolsó, egymást követően kitöltött celláját, és felveszi a rendezésbe. Ha üres helyek vannak, akkor csak az első üres celláig veszi figyelembe az adatokat.
Létrehozhat egy elnevezett tartományt is, és a nevezett tartományt használhatja a cellahivatkozások helyett. Például, ha a megnevezett tartomány DataSet, akkor a kód az alábbiak szerint lesz.
Sub SortDataWithoutHeader () tartomány ("DataRange"). Rendezési kulcs1: = tartomány ("A1"), sorrend1: = xl növekvő, fejléc: = xl Nincs vége al
Most hadd magyarázzam el gyorsan a fenti példákban használt paramétereket:
- 1. kulcs: = Tartomány („A1”) - Az A1 -t úgy adta meg, hogy a kód tudja, melyik oszlopot kell rendezni.
- Order1: = xlAscending - xlAscendingként adta meg a sorrendet. Ha azt szeretné, hogy csökkenő sorrendben legyen, használja az xlDescending parancsot.
- Fejléc: = xlNo - Megadta, hogy nincsenek fejlécek. Ez az alapértelmezett érték is. Tehát még akkor is, ha ezt kihagyja, az adatok rendezésre kerülnek, tekintettel arra, hogy nincsenek fejlécek.
Vajon hova tegye ezt a VBA kódot, és hogyan futtassa a makrót? Olvassa el ezt az oktatóanyagot!
Egyetlen oszlop rendezése fejléccel
Az előző példában az adathalmaznak nem volt fejléce.
Ha az adatok fejlécekkel rendelkeznek, akkor ezt meg kell adni a kódban, hogy a rendezés az adatkészlet második sorától kezdődhessen.
Tegyük fel, hogy rendelkezik az alább látható adatkészlettel:
Az alábbiakban az a kód található, amely csökkenő sorrendbe rendezi az adatokat az üzletek eladásai alapján.
Sub SortDataWithHeader () tartomány ("DataRange"). Sort Key1: = Range ("C1"), Order1: = xlDescending End Sub
Vegye figyelembe, hogy létrehoztam egy elnevezett tartományt - „DataRange”, és ezt a megnevezett tartományt használtam a kódban.
Több oszlop rendezése fejlécekkel
Ebben az oktatóanyagban eddig láttuk, hogyan lehet rendezni egyetlen oszlopot (fejlécekkel és anélkül).
Mi van akkor, ha több oszlop alapján szeretne rendezni.
Például az alábbi adatkészletben mi van, ha először az állapotkód, majd az áruház szerint szeretnék rendezni.
Itt található a kód, amely egyszerre több oszlopot rendez.
Sub SortMultipleColumns () ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 "). Fejléc = xlIgen. Alkalmazza a végét a végponttal
Az alábbiakban az eredményt kapja.
A fenti példában az adatokat először az állapotkód szerint rendezzük (A oszlop). Ezután az állapotkód adatokon belül ismét az Áruház rendezése történik (B oszlop). Ezt a sorrendet az a kód határozza meg, amelyben megemlíti.
Adatok rendezése dupla kattintással a fejlécre
Ha irányítópultot hoz létre, vagy könnyebben szeretné használni a jelentéseit, írhat egy VBA -kódot, amely rendezni fogja az adatokat, ha duplán kattint a fejlécekre.
Valami az alábbiak szerint:
Az alábbiakban látható a kód, amely lehetővé teszi ezt:
Private Sub Worksheet_BeforeDoubleClick (ByVal Target as Range, Cancel as Boolean) Dim KeyRange as Range Dim ColumnCount as Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Mégse = True Set KeyRange = Range (Target.Address) Range ("DataRange"). Rendezési kulcs1: = KeyRange, Header: = xl Igen End End End End
Ne feledje, hogy létrehoztam egy elnevezett tartományt („DataRange”), és ezt használtam a kódban a cellahivatkozások használata helyett.
Amint duplán kattint valamelyik fejlécre, a kód letiltja a szokásos dupla kattintási funkciót (amely a szerkesztési módba való belépést jelenti), és ezt a cellát használja kulcsként az adatok rendezése során.
Azt is vegye figyelembe, hogy mostantól ez a kód csak növekvő sorrendben rendezi az összes oszlopot.
Ne feledje, hogy a dupla kattintás egy trigger, amely lehetővé teszi az Excel számára a megadott kód futtatását. Ezeket az aktiválási szabályokat, mint például a dupla kattintás, a munkafüzet megnyitása, új munkalap hozzáadása, cella módosítása stb., Eseményeknek nevezzük, és makrók futtatására használhatók Excelben. Az Excel VBA eseményeiről itt olvashat bővebben.Hova tegyem ezt a kódot?
Ezt a kódot be kell illesztenie annak a lapnak a kódablakába, amelyben ezt a duplakattintásos rendezési funkciót szeretné használni.
Ezt csináld meg:
- Kattintson a jobb gombbal a lap fülre.
- Kattintson a Kód megtekintése elemre.
- Illessze be a kódot annak a lapnak a kódablakába, amelyben az adatai találhatók.
Mi van akkor, ha az első két oszlopot („Állapot” és „Áruház”) növekvő sorrendbe szeretné rendezni, de az „Értékesítés” oszlopot csökkenő sorrendbe.
Itt van a kód, ami megcsinálja:
Private Sub Worksheet_BeforeDoubleClick (ByVal Target as Range, Cancel as Boolean) Dim KeyRange as Range Dim ColumnCount as Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Mégse = True Set KeyRange = Range (Target.Address) If Target.Value = "Sales" then SortOrder = xlDescending Else SortOrder = xlNövekvő vége, ha a tartomány ("DataRange"). Rendezési kulcs1: = KeyRange, Header: = xlYes, Order1: = Rendezés vége, ha vége al
A fenti kódban ellenőrzi, hogy a duplán kattintott cella az Értékesítési fejléc-e vagy sem. Ha igen, akkor az xlDescending értéket rendeli hozzá a SortOrder változóhoz, ellenkező esetben xlAscending értékűvé teszi.
Most tegyük ezt tovább, és mutassunk egy vizuális jelzőt (nyíl és színes cella) a fejlécben, amikor rendezett.
Valami az alábbiak szerint:
Ennek eléréséhez új munkalapot adtam hozzá, és a következő módosításokat végeztem el rajta (letöltheti a példafájlt, és követheti):
- Az új lap nevét „BackEnd” -re változtatta.
- A B2 cellába írja be a nyíl szimbólumát (ehhez lépjen a Beszúrás menüpontra, és kattintson a „Szimbólum” lehetőségre).
- Másolja és illessze be a fejléceket az adatkészletből a „Háttér” lap A3: C3 cellájába.
- Használja a következő funkciót az A4 cellában: AC4:
= HA (A3 = $ C $ 1, A3 & "" & $ B $ 1, A3)
- A többi cellát automatikusan kitölti a VBA kód, ha duplán kattint a fejlécekre az oszlop rendezéséhez.
A háttérlapja az alábbiak szerint néz ki:
Most az alábbi kód segítségével rendezheti az adatokat a fejlécekre való dupla kattintással. Ha duplán kattint egy fejlécre, akkor automatikusan megjelenik a nyíl a fejléc szövegében. Vegye figyelembe, hogy feltételes formázást is használtam a cella kiemelésére.
Private Sub Worksheet_BeforeDoubleClick (ByVal Target as Range, Cancel as Boolean) Dim KeyRange as Range Dim ColumnCount as Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False If Target.Row = 1 And Target.Column <= ColumnCount Then Mégse = Igaz munkalapok ("Háttér"). Tartomány ("C1") = Target.Value Set KeyRange = Range (Target.Address) Range ("DataRange"). Rendezési kulcs1: = KeyRange, Header: = xlYes Worksheets ("BackEnd" "). Tartomány (" A1 ") = Cél.Oszlop Az i = 1 -től az Oszlopszám -tartományig (" Adattartomány "). Cellak (1, i) .Érték = Munkalapok (" Háttér "). Tartomány (" A4 "). Eltolás (0, i - 1). Érték Következő i Vége Ha vége Al
Ne feledje, hogy ez a kód jól működik az adataim és a munkafüzetem felépítésében. Ha megváltoztatja az adatok szerkezetét, akkor ennek megfelelően módosítania kell a kódot.
Töltse le a példa fájlt
