Екцел већ има неколико начина за брзо сортирање података.
Можете једноставно сортирати скуп података помоћу икона сортирања на врпци или дијалошког оквира за сортирање.
Зашто онда морате знати како то учинити помоћу ВБА?
Знање о сортирању података помоћу ВБА може бити корисно ако је укључено као део вашег кода. На пример, претпоставимо да дневно/недељно добијате скуп података који морате да форматирате и сортирате по одређеном редоследу.
Можете креирати макро да бисте све ово урадили уместо вас једним кликом. То ће вам уштедети много времена и труда сваки пут када то учините.
Такође, ако креирате Екцел контролне табле, можете подићи Екцел сортирање на нови ниво тако што ћете омогућити кориснику да сортира податке само двоструким кликом на заглавље (као што је приказано испод).
Касније ћу у овом водичу описати како то створити. Хајде прво да разјаснимо основе.
Разумевање методе Ранге.Сорт у програму Екцел ВБА
Када сортирате помоћу ВБА, морате да користите метод Ранге.Сорт у свом коду.
„Распон“ би били подаци које покушавате да сортирате. На пример, ако сортирате податке у А1: А10, онда би „Опсег“ био Распон („А1: А10“).
Такође можете креирати именовани опсег и користити га уместо референци ћелије. На пример, ако креирам именовани опсег „ДатаРанге“ за ћелије А1: А10, онда могу да користим и опсег („ДатаРанге“)
Помоћу методе сортирања морате пружити неке додатне информације кроз параметре. Испод су кључни параметри које морате знати:
- Кључ - овде морате да наведете колону коју желите да сортирате. На пример, ако желите да сортирате колону А, морате да користите кеи: = Ранге („А1“)
- Ордер - овде одређујете да ли желите сортирање по растућем или опадајућем редоследу. На пример, ако желите сортирање у растућем редоследу, користићете редослед: = клАсцендинг
- Хеадер - овде наводите да ли ваш скуп података има заглавља или не. Ако има заглавља, сортирање почиње од другог реда скупа података, иначе почиње од првог реда. Да бисте навели да ваши подаци имају заглавља, користићете заглавље: = клДа
Иако су ова три у већини случајева довољна, у овом чланку можете прочитати више о параметрима.
Хајде сада да видимо како користити методу Ранге.Сорт у ВБА за сортирање података у Екцелу.
Сортирање једне колоне без заглавља
Претпоставимо да имате једну колону без заглавља (као што је приказано испод).
Помоћу доњег кода можете га сортирати по растућем редоследу.
Под СортДатаВитхоутХеадер () Распон ("А1: А12"). Тастер за сортирање1: = Опсег ("А1"), Ред1: = клАсцендинг, Заглавље: = клНе Енд Суб
Имајте на уму да сам ручно одредио опсег података као Распон („А1: А12“).
У случају да може доћи до промена у подацима и да се вредности могу додати/избрисати, можете користити доњи код који се аутоматски прилагођава на основу попуњених ћелија у скупу података.
Под СортДатаВитхоутХеадер () Распон ("А1", Опсег ("А1"). Крај (клДовн)). Тастер за сортирање1: = Опсег ("А1"), Ред1: = клАсцендинг, Заглавље: = клНо Крај Суб
Имајте на уму да сам уместо Ранге (“А1: А12”) користио, Ранге (“А1”, Ранге (“А1”). Енд (клДовн)).
Ово ће проверити последњу узастопно попуњену ћелију у колони и укључити је у сортирање. У случају да постоје празна поља, подаци ће се разматрати само до прве празне ћелије.
Такође можете креирати именовани опсег и користити тај именовани опсег уместо референци ћелије. На пример, ако је именовани опсег ДатаСет, ваш код би сада био као што је приказано испод.
Под СортДатаВитхоутХеадер () Распон ("ДатаРанге"). Сорт Кеи1: = Ранге ("А1"), Ордер1: = клАсцендинг, Хеадер: = клНо Енд Суб
Дозволите ми да брзо објасним параметре који се користе у горњим примерима:
- Кључ1: = Опсег („А1“) - Наведен А1 тако да код зна коју колону да сортира.
- Ордер1: = клАсцендинг - Одређено је да је клАсцендинг. Ако желите да буде у опадајућем редоследу, користите клДесцендинг.
- Заглавље: = клНо - Одређено је да нема заглавља. Ово је такође подразумевана вредност. Дакле, чак и ако ово изоставите, ваши подаци ће бити сортирани с обзиром да немају заглавља.
Питате се где ставити овај ВБА код и како покренути макро? Прочитајте овај водич!
Сортирање једне колоне са заглављем
У претходном примеру, скуп података није имао заглавље.
Када ваши подаци имају заглавља, морате то навести у коду како би сортирање могло почети од другог реда скупа података.
Претпоставимо да имате скуп података као што је приказано испод:
Испод је код који ће сортирати податке у опадајућем редоследу на основу продаје продавница.
Под СортДатаВитхХеадер () Распон ("ДатаРанге"). Сорт Кеи1: = Опсег ("Ц1"), Ордер1: = клДесцендинг Енд Суб
Имајте на уму да сам креирао именовани опсег - „ДатаРанге“ и користио овај именовани опсег у коду.
Сортирање више колона са заглављима
До сада смо у овом водичу видели како сортирати једну колону (са и без заглавља).
Шта ако желите да сортирате на основу више колона.
На пример, у доњем скупу података шта ако желим прво да сортирам према коду државе, а затим према продавници.
Ево кода који ће сортирати више колона одједном.
Подразврстајте више ступаца () са АцтивеСхеет.Сорт .СортФиелдс.Адд Кеи: = Ранге ("А1"), Ордер: = клАсцендинг .СортФиелдс.Адд Кеи: = Ранге ("Б1"), Ордер: = клАсцендинг .СетРанге Ранге ("А1 : Ц13 "). Хеадер = клДа. Примијени Енд Витх Енд Суб
Испод је резултат који ћете добити.
У горњем примеру, подаци се прво сортирају према коду државе (колона А). Затим се унутар података кода државе поново сортира по продавници (колона Б). Овај редослед је одређен кодом у коме га помињете.
Сортирање података помоћу двоструког клика на заглавље
Ако креирате контролну таблу или желите лакшу употребу у извештајима, можете написати ВБА код који ће сортирати податке када двапут кликнете на заглавља.
Нешто као што је приказано испод:
Испод је код који ће вам то омогућити:
Привате Суб Ворксхеет_БефореДоублеЦлицк (БиВал Таргет Ас Ранге, Цанцел Ас Боолеан) Дим КеиРанге Ас Ранге Дим ЦолумнЦоунт Ас Интегер ЦолумнЦоунт = Ранге ("ДатаРанге"). Цолумнс.Цоунт Цанцел = Фалсе Ако је Таргет.Ров = 1 И Таргет.Цолумн <= ЦолумнЦоунт Цанцел = Труе Сет КеиРанге = Опсег (Таргет.Аддресс) Опсег ("ДатаРанге"). Сорт Кеи1: = КеиРанге, Хеадер: = клДа Енд Иф Енд Суб
Имајте на уму да сам креирао именовани опсег („ДатаРанге“) и да сам га користио у коду уместо да користим референце ћелија.
Чим двапут кликнете на било које од заглавља, код онемогућава уобичајену функцију двоструког клика (која је за улазак у режим уређивања) и користи ту ћелију као кључ приликом сортирања података.
Такође имајте на уму да ће од сада овај код сортирати све колоне само у растућем редоследу.
Имајте на уму да је двоструки клик окидач који Екцел-у омогућава да покрене наведени код. Ови покретачи, као што су двоструки клик, отварање радне свеске, додавање новог радног листа, промена ћелије итд. Називају се догађаји и могу се користити за покретање макроа у Екцелу. Више о Екцел ВБА догађајима можете прочитати овде.Где ставити овај код?
Овај код морате да залепите у кодни прозор листа у коме желите ову функцију сортирања двоструким кликом.
Да уради ово:
- Кликните десним тастером миша на картицу листа.
- Кликните на Виев Цоде.
- Залепите код у кодни прозор листа на коме се налазе ваши подаци.
Шта ако желите да прве две колоне („Стање“ и „Продавница“) сортирате узлазним редоследом, али колону „Продаја“ опадајућим редоследом.
Ево кода који ће то учинити:
Привате Суб Ворксхеет_БефореДоублеЦлицк (БиВал Таргет Ас Ранге, Цанцел Ас Боолеан) Дим КеиРанге Ас Ранге Дим ЦолумнЦоунт Ас Интегер ЦолумнЦоунт = Ранге ("ДатаРанге"). Цолумнс.Цоунт Цанцел = Фалсе Ако је Таргет.Ров = 1 и Таргет.Цолумн <= ЦолумнЦоунт Цанцел = Труе Сет КеиРанге = Ранге (Таргет.Аддресс) Иф Таргет.Валуе = "Салес" тхен СортОрдер = клДесцендинг Елсе СортОрдер = клАсцендинг Енд Иф Ранге ("ДатаРанге"). Сорт Кеи1: = КеиРанге, Хеадер: = клИес, Ордер1: = СортОрдер Енд Иф Енд Суб
У горњем коду проверава да ли је ћелија која је двапут кликнута заглавље продаје или није. Ако је одговор да, тада додељује вредност клДесцендинг променљивој СортОрдер, иначе је чини клАсцендинг.
Сада идемо даље овим зарезом и покажимо визуелни маркер (стрелица и обојена ћелија) у заглављу када се сортира.
Нешто као што је приказано испод:
Да бих ово добио, додао сам нови радни лист и унео следеће измене у њега (можете преузети датотеку примера и пратити је даље):
- Име новог листа је промењено у „БацкЕнд“.
- У ћелију Б2 унесите симбол стрелице (да бисте то урадили, идите на Уметни и кликните на опцију „Симбол“).
- Копирајте и залепите заглавља из скупа података у ћелију А3: Ц3 у листу „Позадина“.
- Користите следећу функцију у ћелији А4: АЦ4:
= ИФ (А3 = $ Ц $ 1, А3 & "" & $ Б $ 1, А3)
- Остатак ћелија ће се аутоматски попунити ВБА кодом када двапут кликнете на заглавља да бисте сортирали колону.
Ваш позадински лист би изгледао овако како је приказано испод:
Сада можете користити доњи код за сортирање података двоструким кликом на заглавља. Када двапут кликнете на заглавље, оно ће аутоматски добити стрелицу у тексту заглавља. Имајте на уму да сам такође користио условно обликовање да истакнем ћелију.
Привате Суб Ворксхеет_БефореДоублеЦлицк (БиВал Таргет Ас Ранге, Цанцел Ас Боолеан) Дим КеиРанге Ас Ранге Дим ЦолумнЦоунт Ас Интегер ЦолумнЦоунт = Ранге ("ДатаРанге"). Цолумнс.Цоунт Цанцел = Фалсе Ако је Таргет.Ров = 1 И Таргет.Цолумн <= ЦолумнЦоунт Цанцел = Труе Ворксхеетс ("Бацкенд"). Ранге ("Ц1") = Таргет.Валуе Сет КеиРанге = Опсег (Таргет.Аддресс) Опсег ("ДатаРанге"). Сорт Кеи1: = КеиРанге, Хеадер: = клИес Ворксхеетс ("БацкЕнд ") .Ранге (" А1 ") = Таргет.Цолумн Фор и = 1 То ЦолумнЦоунт Ранге (" ДатаРанге "). Целлс (1, и) .Валуе = Ворксхеетс (" Бацкенд "). Ранге (" А4 "). Оффсет (0, и - 1) .Вредност Нект и Енд Иф Енд Суб
Имајте на уму да овај код добро функционира за начин на који су конструирани моји подаци и радна књига. Ако промените структуру података, мораћете према томе да измените код.
Преузмите датотеку примера
