Сортування даних у Excel VBA
Excel має чудовий засіб сортування діапазону табличних даних за допомогою стрічки на передньому кінці Excel, і в певний момент ви, ймовірно, захочете використовувати цю функціональність у своєму коді VBA. На щастя, це зробити дуже легко.
Переднє діалогове вікно можна знайти, натиснувши значок «Сортувати» у групі «Сортування та фільтр» на вкладці «Дані» на стрічці Excel. Спочатку потрібно вибрати діапазон табличних даних.
Ви також можете використовувати Alt-A-S-S, щоб показати діалогове вікно для користувацької сортування.
Метод сортування був значно покращений у пізніших версіях Excel. Раніше сортування обмежувалося трьома рівнями, але тепер ви можете ввести стільки рівнів, скільки вам потрібно, і це також стосується VBA.
Ви можете включити всі пропоновані функції сортування у діалоговому вікні Сортування Excel у свій код VBA. Функція сортування в Excel швидка і швидша за будь -що, що ви можете написати самостійно у VBA, тому скористайтеся перевагами цієї функціональності.
Зауважте, що коли ви виконуєте сортування у VBA, параметри сортування залишаються незмінними у діалоговому вікні сортування інтерфейсу. Вони також зберігаються при збереженні книги.
Якщо користувач вибирає той самий діапазон табличних даних і натискає на значок Сортування, він побачить усі ваші параметри, які були введені вашим кодом VBA. Якщо вони хочуть створити своєрідний дизайн, то їм доведеться спочатку видалити всі рівні сортування, що їх дуже дратує.
Крім того, якщо ви не змінюєте параметри у своєму коді та покладаєтесь на значення за замовчуванням, ви можете виявити, що користувач вніс зміни, які відображатимуться у вашому сортуванні VBA, і можуть дати несподівані результати, які можуть бути дуже важкими для налагодження .
На щастя, у VBA є метод Clear для повторного встановлення всіх параметрів сортування, щоб користувач побачив діалогове вікно чистої сортування
| 1 | Робочі аркуші ("Аркуш1"). Сортувати. Сортуватиполя. Очистити |
Рекомендується очищати параметри сортування у VBA до та після завершення сортування.
Практичне використання методу сортування у VBA
Коли табличні дані імпортуються в Excel, вони часто розташовуються у дуже випадковому порядку. Його можна імпортувати з файлу CSV (значення, розділені комами), або він міг надходити з посилання на базу даних або веб -сторінку. Ви не можете покладатися на те, що він буде в установленому порядку від одного імпорту до іншого.
Якщо ви представляєте ці дані користувачеві на своєму робочому аркуші, користувачеві може бути важко подивитися і зрозуміти величезну кількість даних, які з точки зору порядку є повсюдно. Вони можуть захотіти згрупувати дані або вирізати та вставити певні їх частини в іншу програму.
Вони також можуть побачити, наприклад, найвищооплачуваного працівника або працівника з найдовшим стажем роботи.
Використовуючи метод Сортування у VBA, ви можете запропонувати варіанти, що дозволяють легко сортувати для користувача.
Зразки даних для демонстрації сортування Excel за допомогою VBA
Спочатку нам потрібно, щоб деякі зразки даних були внесені до робочого аркуша, щоб код міг продемонструвати всі засоби, доступні у VBA.
Скопіюйте ці дані на робочий аркуш (так званий «аркуш1») точно так, як показано.
Зауважте, що були використані різні кольори фону клітинки та кольори шрифту, оскільки вони також можуть бути використані як параметри сортування. Сортування за кольорами комірок та шрифтів буде продемонстровано далі у статті. Також зауважте, що у клітинці E3 назва відділу - це нижній регістр.
Вам не потрібні інтер’єр комірки та кольори шрифту, якщо ви не хочете використовувати приклади сортування за коміркою та кольором шрифту.
Запис макросу для сортування VBA
Код VBA для сортування може бути досить складним, і іноді може бути гарною ідеєю виконати сортування на передньому кінці Excel і записати макрос, щоб показати, як працює код.
На жаль, функція запису може генерувати величезну кількість коду, оскільки вона встановлює практично всі доступні параметри, навіть якщо значення за замовчуванням для багатьох параметрів прийнятні для вашої операції сортування.
Однак це дає вам дуже гарне уявлення про те, що потрібно для написання коду сортування VBA, і одна з переваг полягає в тому, що записаний код завжди працюватиме для вас. Для правильної роботи вашого коду може знадобитися тестування та налагодження.
Пам’ятайте, що для операції, виконаної у VBA, немає функції скасування, тому перед початком написання коду сортування рекомендується скопіювати табличні дані на інший аркуш.
Наприклад, якби ви зробили просту сортування зразків даних вище, сортувавши їх за співробітником, запис генерує такий код:
| 123456789101112131415161718 | Додатковий макрос1 ()Діапазон ("A1: E6"). ВиберітьActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Діапазон ("A2: A6"), _SortOn: = xlSortOnValues, Порядок: = xlВисхідний, DataOption: = xlSortNormalЗа допомогою ActiveWorkbook.Worksheets ("Sheet1"). СортуватиДіапазон .SetRange ("A1: E6").Головка = xlТак.MatchCase = Неправда.Орієнтація = xlTopToBottom.SortMethod = xlPinYin.ЗастосуватиЗакінчити зEnd Sub |
Це досить великий шматок коду, і багато з нього непотрібне через використання параметрів за замовчуванням. Однак, якщо ви перебуваєте під тиском часу, щоб завершити проект, і вам швидко потрібен якийсь код, який працює, ви можете легко вставити його у власний код VBA.
Однак, якщо ви хочете зробити свій код зрозумілим та елегантнішим, тоді доступні інші варіанти.
Код VBA для сортування на одному рівні
Якщо ви хочете сортувати зразок коду на основі Employee, як і раніше, під час запису макросу, код дуже простий:
| 1234567 | Sub SingleLevelSort ()Робочі аркуші ("Аркуш1"). Сортувати. Сортуватиполя. ОчиститиДіапазон ("A1: E6"). Клавіша сортування1: = Діапазон ("A1"), Заголовок: = xlТакEnd Sub |
Це набагато простіше зрозуміти, ніж записаний код, оскільки він приймає значення за замовчуванням, наприклад, сортування за зростанням, тому немає необхідності встановлювати параметри як значення за замовчуванням. Це передбачає, що ви заздалегідь використовували твердження «Очистити».
Спочатку метод «Очистити» використовується для того, щоб усі параметри сортування для цього аркуша були повернуті до значень за замовчуванням. Можливо, користувач раніше встановлював параметри на різні значення, або раніше сортування у VBA могло їх змінити. Під час сортування важливо починати з положення за замовчуванням, інакше ви легко отримаєте неправильні результати.
Метод Clear не скидає параметр заголовка, і бажано включити його у свій код, інакше Excel може спробувати вгадати, чи є рядок заголовка чи ні.
Запустіть цей код проти зразкових даних, і ваш робочий аркуш буде виглядати так:
Код VBA для багаторівневої сортування
Ви можете додати стільки рівнів сортування, скільки потрібно у вашому коді. Припустимо, що ви хотіли спочатку відсортувати за відділом, а потім за датою початку, але в порядку зростання для відділу та спаданням за датою початку:
| 12345678 | Sub MultiLevelSort ()Робочі аркуші ("Аркуш1"). Сортувати. Сортуватиполя. ОчиститиДіапазон ("A1: E6"). Клавіша сортування1: = Діапазон ("E1"), Кнопка2: = Діапазон ("C1"), Заголовок: = xlТак, _Порядок1: = xlЗростаючий, Порядок2: = xlЗнижаєтьсяEnd Sub |
Зауважте, що тепер у операторі сортування є два ключі (Key1 та Key2). Ключ 1 (стовпець відділу E) перш за все сортується, а потім ключ 2 (стовпець «Дата початку») сортується на основі першого сортування.
Також є два параметри порядку. Порядок1 асоціюється з Ключем1 (Відділ), а Порядок2 - з Ключем2 (Дата початку). Важливо стежити за тим, щоб ключі та замовлення відповідали один одному.
Запустіть цей код проти зразкових даних, і ваш робочий аркуш буде виглядати так:
Стовпець "Відділ" (E) - у порядку зростання, а стовпець "Дата початку" (C) - у порядку спадання.
Ефект такого роду найбільш помітний, якщо подивитися на Джейн Хелфакр (ряд 3) та Джона Сазерленда (рядок 4). Вони обидва в галузі фінансів, але Джейн Хелфакр почала ще до Джона Сазерленда, і дати наведені в спадному порядку.
Якщо діапазон табличних даних може мати будь -яку довжину, можна використати об’єкт UsedRange для визначення діапазону сортування. Це спрацює, лише якщо на аркуші є лише табличні дані, оскільки будь -які значення поза даними дадуть неправильні результати щодо кількості рядків та стовпців.
| 1234567 | Sub MultiLevelSort ()Робочі аркуші ("Аркуш1"). Сортувати. Сортуватиполя. ОчиститиРобочі аркуші ("Аркуш1"). UsedRange.Sort Key1: = Діапазон ("E1"), Ключ2: = Діапазон ("C1"), Заголовок: = xlТак, _Порядок1: = xlЗростаючий, Порядок2: = xlЗнижаєтьсяEnd Sub |
Це запобігає виникненню проблеми, якщо ви використовуєте метод ‘End (xlDown)’ для визначення діапазону сортування. Якщо в середині даних є порожня клітинка, то все, що є після порожньої комірки, не буде включено, тоді як UsedRange опускається до останньої активної комірки на аркуші.
Сортування за кольором комірки
З Excel 2007 тепер можливо сортування за кольором фону клітинки, що забезпечує величезну гнучкість при розробці коду сортування у VBA.
| 123456789101112 | Sub SingleLevelSortByCellColor ()Робочі аркуші ("Аркуш1"). Сортувати. Сортуватиполя. ОчиститиActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Діапазон ("A2: A6"), _SortOn: = xlSortOnCellColor, Порядок: = xlВисхідний, DataOption: = xlSortNormalЗа допомогою ActiveWorkbook.Worksheets ("Sheet1"). СортуватиДіапазон .SetRange ("A1: E6").ЗастосуватиЗакінчити зEnd Sub |
Цей код буде сортувати діапазон вибіркових даних (A2: A6) на основі кольору фону комірки. Зауважте, що тепер існує додатковий параметр під назвою "SortOn", який має значення "xlSortOnCellColor".
Зауважте, що параметр "SortOn" може використовуватися лише об'єктом робочого листа, а не об'єктом діапазону.
Через це код є більш складним, ніж для сортування, що використовує значення клітинок.
Цей код використовує ключове значення для сортування, яке охоплює весь діапазон даних, але ви можете вказати окремі стовпці як ключ для сортування кольору фону та використовувати кілька рівнів, як показано раніше.
Після запуску цього коду ваш робочий лист тепер матиме такий вигляд:
Сортування за кольором шрифту
Функція сортування в Excel VBA пропонує ще більшу гнучкість, оскільки ви можете сортувати за кольорами шрифту:
| 1234567891011121314 | Sub SingleLevelSortByFontColor ()Робочі аркуші ("Аркуш1"). Сортувати. Сортуватиполя. ОчиститиActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add (Range ("A2: A6"), _xlSortOnFontColor, xlAscending, xlSortNormal) .SortOnValue.Color = RGB (0, 0, 0)За допомогою ActiveWorkbook.Worksheets ("Sheet1"). СортуватиДіапазон .SetRange ("A1: E6").Головка = xlТак.Орієнтація = xlTopToBottom.ЗастосуватиЗакінчити зEnd Sub |
Код для сортування за кольором шрифту набагато складніший, ніж для кольору фону комірки. Параметр "SortOn" тепер містить значення "xlSortOnFontColor".
Зауважте, що ви повинні вказати орієнтацію як "xlTopToBottom", а також вказати колір для сортування. Це вказується термінами RGB (червоний, зелений, чорний) зі значеннями від 0 до 255.
Після запуску цього коду проти зразкових даних ваш робочий аркуш тепер буде виглядати так:
Сортування за допомогою кольорів у VBA набагато складніше, ніж багаторівнева сортування, але якщо код сортування не працюватиме (що може статися, якщо параметр відсутній або ви неправильно ввели код), ви завжди можете повернутися до запису макрос та інтеграція записаного коду у ваш VBA.
Використання інших параметрів у сортуванні VBA
Існує ряд додаткових параметрів, які можна використовувати у коді VBA для налаштування сортування.
СОРТУВАТИ
SortOn вибирає, чи використовуватиме сортування значення клітинок, колір фону клітинки або колір шрифту комірки. Значенням за замовчуванням є значення комірки.
| 1 | SortOn = xlSortOnValues |
Замовлення
Порядок вибирає, чи буде сортування виконуватися в порядку зростання або спадання. За замовчуванням - Висхідний.
| 1 | Порядок = xlВисхідний |
Опція DataOption
DataOption вибирає спосіб сортування тексту та чисел. Параметр xlSortNormal сортує числові та текстові дані окремо. Параметр xlSortTextAsNumbers розглядає текст як числові дані для сортування. За замовчуванням xlSortNormal.
| 1 | DataOption = xlSortNormal |
Заголовок
Заголовок вибирає, чи має діапазон табличних даних рядок заголовка чи ні. Якщо є рядок заголовка, ви не хочете, щоб він включався до сортування.
Значення параметрів: xlYes, xlNo та xlYesNoGuess. xlYesNoGuess залишає за Excel, щоб визначити, чи є рядок заголовка, що легко може призвести до непослідовних результатів. Використання цього значення не рекомендується.
Значення за замовчуванням XNo (немає рядка заголовка в даних). З імпортованими даними зазвичай є рядок заголовка, тому переконайтеся, що для цього параметра встановлено значення xlТак.
| 1 | Заголовок = xlТак |
MatchCase
Цей параметр визначає, чи є сортування чутливим до регістру чи ні. Значення параметрів - істина або хибність. Якщо значення False, то малі значення вважаються такими ж, як і великі. Якщо значення True, то сортування покаже різницю між великими та малими значеннями в межах сортування. Значення за замовчуванням - False.
| 1 | MatchCase = False |
Орієнтація
Цей параметр визначає, чи буде сортування здійснюватися вниз по рядках або по всіх стовпцях. Значення за замовчуванням xlTopToBottom (сортування рядків). Ви можете використовувати xlLeftToRight, якщо хочете сортувати по горизонталі. Такі значення, як xlRows і xlColumns, не працюють для цього параметра.
| 1 | Орієнтація = xlTopToBottom |
SortMethod
Цей параметр використовується лише для сортування китайських мов. Він має два значення, xlPinYin та xlStroke. xlPinYin - це значення за замовчуванням.
xlPinYin сортує за допомогою фонетичного китайського порядку сортування символів. xlStroke сортує за кількістю штрихів у кожному символі.
Якщо ви записуєте макрос сортування, цей параметр завжди буде включений до коду, і вам, можливо, було цікаво, що він означає. Однак, якщо ви не маєте справу з даними китайською мовою, це мало користі.
| 1 | SortMethod = xlPinYin |
Використання події подвійного клацання для сортування табличних даних
У всіх функціональних можливостях, які Microsoft включила до методів сортування для VBA, вона не включала простих засобів подвійного клацання на заголовку стовпця та сортування всіх табличних даних на основі цього стовпця.
Це дійсно корисна функція, і для цього легко написати код.
| 12345678910111213141516171819202122232425262728293031323334 | Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean)'Припускається, що дані починаються з комірки А1'Створіть три змінні для відображення вибраного цільового стовпця та максимального стовпця та рядка _'табличні даніDim Col As Integer, RCol As Long, RRow As Long'Перевірте, чи користувач двічі клацнув рядок заголовка - рядок 1, інакше вийдіть із підзаголовкаЯкщо Target.Row 1 Тоді вийдіть із Sub"Захоплення максимальних рядків у діапазоні табличних даних за допомогою об'єкта" UsedRange "RCol = ActiveSheet.UsedRange.Columns.Count'Захоплення максимальних стовпців у діапазоні табличних даних за допомогою об'єкта' UsedRange 'RRow = ActiveSheet.UsedRange.Rows.Count'Перевірте, чи користувач двічі не клацнув стовпець за межами табличного діапазону данихЯкщо Target.Column> RCol, то вийдіть із Sub'Зафіксуйте стовпець, на якому користувач двічі клацнувCol = Target.Column'Очистити попередні параметри сортуванняActiveSheet.Sort.SortFields.Clear'Сортування табличного діапазону, визначеного максимальними рядками та стовпцями, з об'єкта' UsedRange ''Сортуйте табличні дані, використовуючи стовпець, двічі натиснутий користувачем, як ключ сортуванняActiveSheet.Range (Клітинки (1, 1), Клітинки (RCol, RRow)). Ключ сортування1: = Клітинки (1, Стовпець), Заголовок: = xlТак"Виберіть клітинку A1 - це для того, щоб користувач не залишився в режимі редагування після сортування _'завершеноActiveSheet.Range ("A1"). ВиберітьEnd Sub |
Цей код потрібно розмістити на події подвійного клацання на аркуші, що містить табличні дані. Для цього клацніть на назві робочого аркуша у вікні Провідника проектів (у верхньому лівому куті екрана VBE), а потім виберіть «Робочий лист» у першому спадному меню вікна коду. Виберіть "BeforeDoubleClick" у другому спадному меню, після чого ви зможете ввести свій код.
Зауважте, що жодні імена, діапазони або посилання на комірки не кодуються жорстко у цьому коді, за винятком переміщення курсору до комірки A1 в кінці коду. Код призначений для отримання всієї необхідної інформації з координат комірки, на які користувач двічі клацнув, та розміру діапазону табличних даних.
Не має значення, наскільки великий діапазон табличних даних. Код все одно збиратиме всю необхідну інформацію, і його можна використовувати для даних, що зберігаються в будь -якому місці вашої книги, без необхідності жорсткого кодування значень.
Єдине припущення полягає в тому, що в табличних даних є рядок заголовка, і що діапазон даних починається з комірки A1, але вихідне положення діапазону даних можна легко змінити в коді.
Будь -який користувач буде вражений цією новою функцією сортування!
Розширення функції сортування за допомогою VBA
Microsoft дозволила величезну гнучкість у сортуванні за допомогою широкого діапазону параметрів. Однак у рамках VBA ви можете продовжити це.
Припустимо, що ви хотіли відсортувати будь -які значення жирним шрифтом у верхній частині даних. У Excel немає способу зробити це, але ви можете написати код VBA, щоб це зробити:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | Sub SortByBold ()'Створіть змінні, щоб містити кількість рядків і стовпців для табличних данихDim RRow As Long, RCol As Long, N As Long'Вимкніть оновлення екрана, щоб користувач не бачив, що відбувається - він може бачити _'Цінність змінюється, і цікаво чомуApplication.ScreenUpdating = Неправда'Зафіксуйте кількість стовпців у діапазоні табличних данихRCol = ActiveSheet.UsedRange.Columns.Count'Зафіксуйте кількість рядків у діапазоні табличних данихRRow = ActiveSheet.UsedRange.Rows.Count'Ітерація через усі рядки в табличному діапазоні даних, ігноруючи рядок заголовкаДля N = 2 RRow"Якщо клітинка має жирний шрифт, то розмістіть перед початком значення 0 значення клітинкиЯкщо ActiveSheet.Cells (N, 1) .Font.Bold = ІстинаActiveSheet.Cells (N, 1) .Value = "0" & ActiveSheet.Cells (N, 1) .ValueЗакінчити ЯкщоДалі N'Очистити всі попередні параметри сортуванняActiveSheet.Sort.SortFields.Clear'Сортувати діапазон табличних даних. Усі значення з початковим значенням 0 перемістяться вгоруДіапазон активних аркушів (клітинки (1, 1), клітинки (RCol, RRow)). Ключ сортування1: = комірки (1, 1), заголовок: = xlТак'Ітерація через усі рядки в табличному діапазоні даних, ігноруючи рядок заголовкаДля N = 2 RRow"Якщо клітинка має жирний шрифт, видаліть початкове значення 0 зі значення клітинки на _'відновити початкові значенняЯкщо ActiveSheet.Cells (N, 1) .Font.Bold = ІстинаActiveSheet.Cells (N, 1) .Value = Mid (ActiveSheet.Cells (N, 1) .Value, 2)Закінчити ЯкщоДалі N'Увімкніть оновлення екранаApplication.ScreenUpdating = ІстинаEnd Sub |
Код визначає розмір діапазону табличних даних за допомогою об'єкта "UsedRange", а потім перебирає всі рядки всередині нього. Коли знайдено жирний шрифт, перед значенням комірки розміщується початковий нуль.
Потім відбувається сортування. Оскільки сортування відбувається у порядку зростання, все, що має нуль попереду, потрапить до верхньої частини списку.
Потім код повторює всі рядки і видаляє початкові нулі, відновлюючи дані до їх початкових значень.
Цей код сортує, використовуючи жирні шрифти як критерій, але ви можете легко так само використовувати інші характеристики клітинки, наприклад, курсив, розмір точки тексту, шрифт підкреслення, назву шрифту тощо.
