Сортирање података у Екцел ВБА
Екцел има одлично средство за сортирање низа табеларних података помоћу врпце на предњој страни програма Екцел, а у неком тренутку ћете вероватно желети да користите ову функционалност у свом ВБА коду. На срећу, ово је врло лако учинити.
Предњи дијалог се налази кликом на икону „Сортирај“ у групи „Сортирај и филтрирај“ на картици „Подаци“ на Екцел траци. Морате прво да изаберете опсег табеларних података.
Такође можете користити Алт-А-С-С за приказ дијалога за прилагођено сортирање.
Метода сортирања је знатно побољшана у каснијим верзијама програма Екцел. Сортирање је раније било ограничено на три нивоа, али сада можете унети онолико нивоа колико вам је потребно, а то се такође односи на ВБА.
Можете да укључите све понуђене функције сортирања у дијалогу Сортирање програма Екцел у свој ВБА код. Функција сортирања у Екцелу је брза и бржа од свега што бисте сами могли написати у ВБА, па искористите предности ове функције.
Имајте на уму да када вршите сортирање у ВБА, параметри сортирања остају исти у предњем дијалогу за сортирање. Такође се чувају када се сачува радна свеска.
Ако корисник одабере исти распон табеларних података и кликне на икону Сортирање, видеће све ваше параметре које је унео ваш ВБА код. Ако желе да направе неку врсту сопственог дизајна, мораће прво да избришу све нивое сортирања, што ће им бити јако досадно.
Такође, ако не промените параметре у свом коду и ослањате се на подразумеване вредности, можда ћете открити да је корисник унео промене које ће се одразити на вашу ВБА сортирање и могу дати неочекиване резултате, што може бити веома тешко отклонити .
Срећом, у ВБА постоји метода Цлеар за поновно постављање свих параметара сортирања тако да ће корисник видети дијалог за чисто сортирање
| 1 | Радни листови ("Лист1"). Сортирај.СортФиелдс.Цлеар |
Добра је пракса брисање параметара сортирања у ВБА -и пре и након завршетка сортирања.
Практична употреба методе сортирања у ВБА
Када се табеларни подаци увезу у Екцел, они су често насумичним редоследом. Може се увести из ЦСВ датотеке (вредности раздвојене зарезима) или може доћи из везе до базе података или веб странице. Не можете се ослонити на то да ће од једног увоза долазити у одређеном редоследу.
Ако презентирате ове податке кориснику на свом радном листу, кориснику ће можда бити тешко да погледа и разуме огромну количину података која је по редоследу свуда. Можда ће желети да групишу податке или да исеку и налепе одређене делове истих у другу апликацију.
Можда би такође желели да виде, на пример, најплаћенијег запосленог или запосленог са најдужим стажом.
Користећи методу Сортирање у ВБА, можете понудити опције које омогућавају лако сортирање за корисника.
Узорци података за демонстрирање Екцел сортирања помоћу ВБА
Прво нам је потребно неколико узорака података да се унесу у радни лист, тако да код може показати све могућности доступне у оквиру ВБА.
Копирајте ове податке у радни лист (назван „Лист 1“) тачно како је приказано.
Имајте на уму да су коришћене различите боје позадине ћелије и боје фонта, јер се оне могу користити и као параметри сортирања. Сортирање помоћу боја ћелија и фонтова бит ће приказано касније у чланку. Такође имајте на уму да је у ћелији Е3 назив одељења мала слова.
Унутрашњост ћелије и боје фонта вам не требају ако не желите да користите примере сортирања по ћелији и боји фонта.
Снимање макроа за ВБА сортирање
ВБА код за сортирање може бити прилично компликован, а понекад може бити добра идеја извршити сортирање на предњем крају програма Екцел и снимити макро да бисте видели како код функционише.
Нажалост, функција снимања може генерирати огромну количину кода јер поставља готово све доступне параметре, иако су задане вриједности за многе параметре прихватљиве за вашу операцију сортирања.
Међутим, даје вам добру идеју о томе шта је укључено у писање ВБА кода за сортирање, а једна предност је што ће снимљени код увек радити за вас. Можда ће вашем коду бити потребно тестирање и отклањање грешака како би исправно радио.
Запамтите да за операцију обављену у ВБА не постоји функција поништавања, па је добра идеја да направите копију табеларних података на другом радном листу пре него што почнете са писањем кода за сортирање.
На пример, ако сте извршили једноставно сортирање горе наведених примера података, сортирајући их по запосленом, снимак би генерисао следећи код:
| 123456789101112131415161718 | Подмакро1 ()Распон ("А1: Е6"). ИзаберитеАцтивеВоркбоок.Ворксхеетс ("Схеет1"). Сорт.СортФиелдс.ЦлеарАцтивеВоркбоок.Ворксхеетс ("Схеет1"). Сорт.СортФиелдс.Адд2 Кеи: = Ранге ("А2: А6"), _Сортирај: = клСортОнВалуес, Редослед: = клАсцендинг, ДатаОптион: = клСортНормалСа АцтивеВоркбоок.Ворксхеетс ("Схеет1"). Сортирај.СетРанге Ранге ("А1: Е6").Хеадер = клДа.МатцхЦасе = Нетачно.Оријентација = клТопТоБоттом.СортМетход = клПинИин.ПрименитиЗавршитиЕнд Суб |
Ово је прилично велики део кода, а много тога је непотребно због подразумеваних параметара који се користе. Међутим, ако сте под временским притиском да завршите пројекат и брзо вам је потребан неки код који функционише, можете га лако залепити у свој властити ВБА код.
Међутим, ако желите да ваш код буде разумљив и елегантнији, доступне су и друге опције.
ВБА код за сортирање на једном нивоу
Ако желите да сортирате узорак кода на основу запосленика само као и раније приликом снимања макроа, код је врло једноставан:
| 1234567 | Суб СинглеЛевелСорт ()Радни листови ("Схеет1"). Сорт.СортФиелдс.ЦлеарОпсег ("А1: Е6"). Тастер за сортирање1: = Опсег ("А1"), Заглавље: = клДаЕнд Суб |
Ово је далеко лакше разумети од снимљеног кода јер прихвата подразумеване вредности, нпр. Сортирање узлазно, па нема потребе да параметре постављате на подразумеване вредности. Ово претпоставља да сте претходно користили изјаву „Обриши“.
Метода „Обриши“ се у почетку користи да би се осигурало да се сваки параметар сортирања за тај радни лист врати на подразумеване вредности. Корисник је можда претходно поставио параметре на различите вредности или их је раније сортирање у ВБА променило. Приликом сортирања важно је да почнете са подразумеване позиције, у супротном бисте лако могли да добијете нетачне резултате.
Метод Цлеар не поништава параметар заглавља и препоручљиво је да то укључите у свој код, у противном Екцел може покушати да погоди да ли је ред заглавља присутан или не.
Покрените овај код са узорцима података и ваш радни лист ће изгледати овако:
ВБА код за сортирање на више нивоа
У свом коду можете додати онолико нивоа разврставања колико је потребно. Претпоставимо да желите прво сортирати према одељењу, а затим према датуму почетка, али узлазним редоследом за одељење и опадајућим редоследом за датум почетка:
| 12345678 | Суб МултиЛевелСорт ()Радни листови ("Схеет1"). Сорт.СортФиелдс.ЦлеарОпсег ("А1: Е6"). Тастер за сортирање1: = Опсег ("Е1"), Кључ2: = Опсег ("Ц1"), Заглавље: = клДа, _Редослед1: = клАсцендинг, Ордер2: = клДесцендингЕнд Суб |
Имајте на уму да сада постоје два кључа у наредби сортирања (Кеи1 и Кеи2). Кључ1 (колона Е одељења) се прво сортира, а затим се кључ2 (колона Датум почетка Ц) сортира на основу првог сортирања.
Постоје и два параметра наруџбе. Ордер1 је повезан са Кеи1 (Одељење) и Ордер2 је повезан са Кеи2 (Датум почетка). Важно је осигурати да се кључеви и наруџбе међусобно држе у корак.
Покрените овај код са узорцима података и ваш радни лист ће изгледати овако:
Колона Одељење (Е) је у растућем редоследу, а колона Датум почетка (Ц) у опадајућем редоследу.
Ефекат ове врсте је најуочљивији када се погледају Јане Халфацре (ред 3) и Јохн Сутхерланд (ред 4). Обоје су у финансијама, али Јане Халфацре је започела пре Јохна Сутхерланда и датуми су приказани опадајућим редоследом.
Ако распон табеларних података може бити било које дужине, можете користити објекат УседРанге за дефинисање опсега сортирања. Ово ће функционисати само ако на радном листу постоје само табеларни подаци јер ће све вредности изван података дати нетачне резултате за број редова и колона.
| 1234567 | Суб МултиЛевелСорт ()Радни листови ("Схеет1"). Сорт.СортФиелдс.ЦлеарРадни листови ("Лист1"). УседРанге.Сорт Кеи1: = Опсег ("Е1"), Кеи2: = Опсег ("Ц1"), Заглавље: = клДа, _Редослед1: = клАсцендинг, Ордер2: = клДесцендингЕнд Суб |
Ово спречава проблем ако користите методу „Крај (клДовн)“ за дефинисање опсега сортирања. Ако се у средини података налази празна ћелија, ништа после празне ћелије неће бити укључено, док се УседРанге спушта до последње активне ћелије на радном листу.
Сортирање према боји ћелије
Од Екцел 2007, сада је могуће сортирање према боји позадине ћелије, што пружа огромну флексибилност при дизајнирању кода за сортирање у ВБА.
| 123456789101112 | Суб СинглеЛевелСортБиЦеллЦолор ()Радни листови ("Лист1"). Сортирај.СортФиелдс.ЦлеарАцтивеВоркбоок.Ворксхеетс ("Схеет1"). Сорт.СортФиелдс.Адд2 Кеи: = Ранге ("А2: А6"), _Сортирај: = клСортОнЦеллЦолор, Редослед: = клАсцендинг, ДатаОптион: = клСортНормалСа АцтивеВоркбоок.Ворксхеетс ("Схеет1"). Сортирај.СетРанге Ранге ("А1: Е6").ПрименитиЗавршитиЕнд Суб |
Овај код ће сортирати опсег узорка података (А2: А6) на основу боје позадине ћелије. Имајте на уму да сада постоји додатни параметар под називом „СортОн“ који има вредност „клСортОнЦеллЦолор“.
Имајте на уму да параметар „СортОн“ може да користи само објекат радног листа, а не и објекат опсега.
Због тога је код сложенији него за сортирање које користи вредности ћелија.
Овај код користи кључну вредност за сортирање које покрива читав низ података, али можете навести појединачне колоне као кључ за сортирање боје позадине и користити више нивоа као што је приказано раније.
Након покретања овог кода, ваш радни лист ће сада изгледати овако:
Сортирање према боји фонта
Функција сортирања у програму Екцел ВБА нуди још већу флексибилност јер можете сортирати по бојама фонтова:
| 1234567891011121314 | Суб СинглеЛевелСортБиФонтЦолор ()Радни листови ("Схеет1"). Сорт.СортФиелдс.ЦлеарАцтивеВоркбоок.Ворксхеетс ("Схеет1"). Сорт.СортФиелдс.Адд (Ранге ("А2: А6"), _клСортОнФонтЦолор, клАсцендинг, клСортНормал) .СортОнВалуе.Цолор = РГБ (0, 0, 0)Са АцтивеВоркбоок.Ворксхеетс ("Схеет1"). Сортирај.СетРанге Ранге ("А1: Е6").Хеадер = клДа.Оријентација = клТопТоБоттом.ПрименитиЗавршитиЕнд Суб |
Код за сортирање по боји фонта је далеко сложенији него за боју позадине ћелије. Параметар „СортОн“ сада има вредност „клСортОнФонтЦолор“.
Имајте на уму да морате навести оријентацију као „клТопТоБоттом“ и морате навести боју за сортирање. Ово је наведено у терминима РГБ (црвено, зелено, црно) са вредностима од 0 до 255.
Након покретања овог кода према узорцима података, ваш радни лист ће сада изгледати овако:
Сортирање коришћењем боја у ВБА је далеко сложеније од сортирања на више нивоа, али ако ваш код за сортирање неће функционисати (што се може догодити ако недостаје параметар или нисте унели код исправно), увек можете да се вратите на снимање макро и интегрисање снимљеног кода у ваш ВБА.
Коришћење других параметара у ВБА сортирању
Постоји низ опционалних параметара које можете користити у свом ВБА коду за прилагођавање сортирања.
Сортирај
СортОн бира да ли ће сортирање користити вредности ћелија, боје позадине ћелије или боје фонта ћелије. Подразумевана поставка је Целл Валуес.
| 1 | СортОн = клСортОнВалуес |
Ордер
Редослед бира да ли ће се сортирање вршити у растућем или опадајућем редоследу. Подразумевано је растуће.
| 1 | Редослед = клАсцендинг |
ДатаОптион
ДатаОптион бира начин сортирања текста и бројева. Параметар клСортНормал нумеричке и текстуалне податке сортира одвојено. Параметар клСортТектАсНумберс третира текст као нумеричке податке за сортирање. Подразумевано је клСортНормал.
| 1 | ДатаОптион = клСортНормал |
Хеадер
Заглавље бира да ли распон табеларних података има ред заглавља или не. Ако постоји ред заглавља, не желите да се ово укључује у сортирање.
Вредности параметара су клИес, клНо и клИесНоГуесс. клИесНоГуесс препушта Екцел -у да утврди да ли постоји ред заглавља, што би лако могло довести до недоследних резултата. Употреба ове вредности се не препоручује.
Подразумевана вредност је КСНо (нема реда заглавља унутар података). Код увезених података обично постоји ред заглавља, па се побрините да овај параметар поставите на клДа.
| 1 | Заглавље = клДа |
МатцхЦасе
Овај параметар одређује да ли сортирање разликује велика или мала слова. Вредности опција су Труе или Фалсе. Ако је вредност Фалсе, мале вредности се сматрају истим као и велике. Ако је вредност Труе, сортирање ће показати разлику између великих и малих вредности унутар сортирања. Подразумевана вредност је Фалсе.
| 1 | МатцхЦасе = Фалсе |
Оријентација
Овај параметар одређује да ли ће се сортирање вршити надоле кроз редове или преко свих колона. Подразумевана вредност је клТопТоБоттом (сортирање по редовима). Можете користити клЛефтТоРигхт ако желите да сортирате хоризонтално. Вредности као што су клРовс и клЦолумнс не раде за овај параметар.
| 1 | Оријентација = клТопТоБоттом |
СортМетход
Овај параметар се користи само за сортирање кинеских језика. Има две вредности, клПинИин и клСтроке. клПинИин је подразумевана вредност.
клПинИин сортира помоћу фонетског кинеског редоследа сортирања знакова. клСтроке сортира по количини потеза у сваком знаку.
Ако снимите макро за сортирање, овај параметар ће увек бити укључен у код и можда сте се запитали шта то значи. Међутим, осим ако се не бавите подацима на кинеском, од њих нема никакве користи.
| 1 | СортМетход = клПинИин |
Коришћење догађаја двоструког клика за сортирање табеларних података
У свим функционалностима које је Мицрософт укључио у методе сортирања за ВБА, није укључено једноставно средство за двоструки клик на заглавље колоне и сортирање целих табеларних података на основу те колоне.
Ово је заиста корисна функција и лако је написати код за то.
| 12345678910111213141516171819202122232425262728293031323334 | Приватни под -радни лист_БефореДоублеЦлицк (БиВал Таргет Ас Ранге, Цанцел Ас Боолеан)Претпоставља се да подаци почињу у ћелији А1„Направите три променљиве да бисте ухватили изабрану циљну колону и максималну колону и ред од _'табеларни подациДим Цол Ас Интегер, РЦол Ас Лонг, РРов Ас Лонг„Проверите да ли је корисник двапут кликнуо на ред заглавља - ред 1, у супротном изађите из поднасловаИф Таргет.Ров 1 Затим изађите из подм'Снимите максималне редове у табеларном опсегу података помоћу објекта' УседРанге 'РЦол = АцтивеСхеет.УседРанге.Цолумнс.Цоунт'Снимите максималне колоне у табеларном опсегу података помоћу објекта' УседРанге 'РРов = АцтивеСхеет.УседРанге.Ровс.Цоунт„Проверите да ли корисник није двапут кликнуо на колону изван опсега табеларних податакаАко Таргет.Цолумн> РЦол Затим изађите из Суб'Снимите колону на коју је корисник двапут кликнуоЦол = Таргет.Цолумн'Обришите претходне параметре сортирањаАцтивеСхеет.Сорт.СортФиелдс.Цлеар'Сортирајте табеларни опсег према максималним редовима и колонама из објекта' УседРанге ''Сортирајте табеларне податке користећи колону коју корисник двапут кликне као кључ за сортирањеАцтивеСхеет.Ранге (ћелије (1, 1), ћелије (РЦол, РРов)). Кључ сортирања1: = ћелије (1, колона), заглавље: = клДа„Одаберите ћелију А1 - ово је да бисте осигурали да корисник не остане у режиму уређивања након сортирања _'завршеноАцтивеСхеет.Ранге ("А1"). ИзаберитеЕнд Суб |
Овај код треба поставити на догађај двоструког клика на листу који садржи табеларне податке. То можете учинити тако што ћете кликнути на назив радног листа у прозору Пројецт Екплорер (горњи леви угао екрана ВБЕ), а затим изабрати „Радни лист“ у првом падајућем менију у прозору кода. Изаберите „БефореДоублеЦлицк“ у другом падајућем менију, а затим можете да унесете свој код.
Имајте на уму да у овај код није тешко кодирано ниједно име, опсег или референца ћелије осим помицања курсора у ћелију А1 на крају кода. Код је осмишљен тако да добије све потребне информације из координата ћелије на које је корисник двапут кликнуо и величине распона табеларних података.
Није важно колики је опсег табеларних података. Код ће и даље прикупљати све потребне информације и може се користити на подацима који се чувају било где у вашој радној свесци без потребе за хард кодирањем вредности.
Једина претпоставка је да у табличним подацима постоји ред заглавља и да распон података почиње у ћелији А1, али се почетна позиција за распон података може лако промијенити унутар кода.
Сваки корисник ће бити импресиониран овом новом функцијом сортирања!
Проширење функције сортирања помоћу ВБА
Мицрософт је допустио огромну флексибилност у сортирању користећи широк спектар параметара. Међутим, у оквиру ВБА -е ово можете наставити даље.
Претпоставимо да желите да сортирате било које вредности подебљаним фонтом на врх података. Не постоји начин да то учините у Екцелу, али можете написати ВБА код да бисте то урадили:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | Под СортБиБолд ()'Креирајте променљиве које садрже број редова и колона за табеларне податкеДим РРов Ас Лонг, РЦол Ас Лонг, Н Ас Лонг„Искључите ажурирање екрана тако да корисник не може да види шта се дешава - можда ће видети _“вредности се мењају и питамо се заштоАпплицатион.СцреенУпдатинг = Нетачно'Снимите број колона у опсегу табеларних податакаРЦол = АцтивеСхеет.УседРанге.Цолумнс.Цоунт'Снимите број редова унутар опсега табеларних податакаРРов = АцтивеСхеет.УседРанге.Ровс.Цоунт„Поновите све редове у табеларном опсегу података занемарујући ред заглављаФор Н = 2 То РРов„Ако ћелија има подебљани фонт, поставите почетну вредност 0 насупрот вредности ћелијеАко је АцтивеСхеет.Целлс (Н, 1) .Фонт.Болд = ТачноАцтивеСхеет.Целлс (Н, 1) .Валуе = "0" & АцтивеСхеет.Целлс (Н, 1) .ВредностКрај АкоСледећи Н.„Обришите све претходне параметре сортирањаАцтивеСхеет.Сорт.СортФиелдс.Цлеар'Сортирајте опсег табеларних података. Све вредности са водећом 0 вредношћу ће се померити на врхАцтивеСхеет.Ранге (ћелије (1, 1), ћелије (РЦол, РРов)). Кључ сортирања1: = ћелије (1, 1), заглавље: = клДа„Поновите све редове у табеларном опсегу података занемарујући ред заглављаФор Н = 2 То РРов„Ако ћелија има подебљани фонт, уклоните почетну вредност 0 из вредности ћелије у _'вратити оригиналне вредностиАко је АцтивеСхеет.Целлс (Н, 1) .Фонт.Болд = ТачноАцтивеСхеет.Целлс (Н, 1) .Вредност = Средња (АцтивеСхеет.Целлс (Н, 1) .Вредност, 2)Крај АкоСледећи Н.„Поново укључите ажурирање екранаАпплицатион.СцреенУпдатинг = ТачноЕнд Суб |
Код утврђује величину опсега табеларних података помоћу објекта „УседРанге“, а затим понавља све редове унутар њега. Када се пронађе подебљани фонт, почетна нула се поставља испред вредности ћелије.
Тада се врши сортирање. Како је сортирање у растућем редоследу, све са нулом испред иде на врх листе.
Код затим понавља све редове и уклања почетне нуле, враћајући податке на њихове изворне вредности.
Овај код сортира користећи подебљане фонтове као критеријум, али можете лако користити и друге карактеристике ћелије на исти начин, на пример, курзив, величину тачке текста, подвлаку, назив фонта итд.
