Макрос сортировки строк в excel по двум критериям
Если вам нужно отсортировать список, то к вашим услугам куча способов, самый простой из которых - кнопки сортировки на вкладке или в меню Данные (Data - Sort) . Бывают, однако, ситуации, когда сортировку списка нужно делать автоматически, т.е. формулами. Такое может потребоваться, например, при формировании данных для выпадающего списка, при вычислении данных для диаграмм и т.д. Как же "на лету" сортировать список формулой?
Способ 1. Числовые данные
Если список содержит только числовую информацию, то его сортировку можно легко сделать с помощью функций НАИМЕНЬШИЙ (SMALL) и СТРОКА (ROW) :
Функция НАИМЕНЬШИЙ (SMALL) выдергивает из массива (столбец А) n-й по счету наименьший элемент. Т.е. НАИМЕНЬШИЙ(A:A;1) - это самое маленькое число из столбца, НАИМЕНЬШИЙ(А:А;2) - второе по счету наименьшее и т.д.
Функция СТРОКА (ROW) выдает порядковый номер строки для указанной ячейки, т.е. СТРОКА(А1)=1, СТРОКА(A2)=2 и т.д. В данном случае она используется просто как генератор последовательности чисел n=1,2,3… для нашего отсортированного списка. С тем же успехом можно было сделать дополнительный столбец, заполнить его вручную числовой последовательностью 1,2,3… и ссылаться на него вместо функции СТРОКА.
Способ 2. Текстовый список и обычные формулы
Если в списке не числа, а текст, то функция НАИМЕНЬШИЙ (SMALL) уже не сработает, поэтому придется пойти другим, чуть более длинным, путем.
Сначала добавим служебный столбец с формулой, где будет вычисляться порядковый номер каждого имени в будущем отсортированном списке с помощью функции СЧЁТЕСЛИ (COUNTIF) :
В английской версии это будет:
Ну, и наконец, осталось просто вытащить из списка имена по их номерам. Для этого можно использовать такую формулу:
Функция ПОИСКПОЗ (MATCH) ищет в столбце В нужный порядковый номер (1, 2, 3 и т.д.) и выдает, по сути, номер строки, где находится это число. Функция ИНДЕКС (INDEX) вытаскивает из столбца А имя по этому номеру строки.
Способ 3. Формула массива
Этот способ представляет собой, по сути, тот же алгоритм расстановки, что и в Cпособе-2, но реализованный формулой массива. Для упрощения формулы диапазону ячеек С1:С10 было дано имя List (выделить ячейки, нажать Ctrl+F3 и кнопку Создать):
В ячейку Е1 копируем нашу формулу:
Или в англоязычной версии:
и нажимаем Ctrl+Shift+Enter, чтобы ввести ее как формулу массива. Потом полученную формулу можно скопировать вниз на всю длину списка.
Если нужно, чтобы формула учитывала не фиксированный диапазон, а могла подстраиваться при дописывании новых элементов к списку, то нужно будет слегка изменить стратегию.
Во-первых, диапазон List нужно будет задать динамически. Для этого при создании нужно указать не фиксированный диапазон C3:C10, а специальную формулу, которая будет ссылаться на все имеющиеся значения независимо от их количества. Нажмите Alt+F3 или откройте вкладку Формулы - Диспетчер имен (Formulas - Name Manager) , создайте новое имя и в поле Ссылка (Reference) впишите вот такую формулу (я предполагаю, что диапазон сортируемых данных начинается с ячейки C1):
Синтаксис полного кода VBA Excel, применяемого для сортировки данных в таблицах и диапазонах:
Синтаксис сокращенного кода VBA Excel, применяемого для сортировки данных с параметрами по умолчанию:
Expression – выражение, возвращающее объект Worksheet, например:
Расшифровка кода
1. Expression.Sort – метод Sort объекта Worksheet возвращает объект Sort.
2. .SortFields.Clear – метод SortFields объекта Sort возвращает коллекцию объектов SortFields. Метод Clear объекта SortFields удаляет все существующие объекты SortField.
3. .SortFields.Add Key, SortOn, Order, DataOption – метод Add объекта SortFields создает и возвращает новый экземпляр объекта SortField с заданными параметрами.
Параметры метода Add объекта SortFields:
Key – обязательный параметр, который задает значение ключа для сортировки. Тип данных – Range. Обычно указывается первая ячейка столбца при сортировке по строкам или первая ячейка строки при сортировке по столбцам. Сортировка диапазона будет осуществлена по данным столбца (строки), первая ячейка которого указана в качестве ключа.
SortOn – необязательный параметр, который задает критерий сортировки (по какому свойству ячеек производится сортировка).
Значения, которые может принимать SortOn:
Константа | Значение | Описание |
---|---|---|
SortOnValues | 0 | сортировка по значению (значение по умолчанию) |
SortOnCellColor | 1 | сортировка по цвету ячейки |
SortOnFontColor | 2 | сортировка по цвету шрифта |
SortOnIcon | 3 | сортировка по иконке* |
* Иконки (значки) могут быть заданы ячейкам при условном форматировании диапазона.
Order – необязательный параметр, задающий порядок сортировки (по возрастанию или по убыванию).
Значения, которые может принимать Order:
Константа | Значение | Описание |
---|---|---|
xlAscending | 1 | сортировка по возрастанию (значение по умолчанию) |
xlDescending | 2 | сортировка по убыванию |
DataOption – необязательный параметр, который задает способ сортировки текста.
Значения, которые может принимать DataOption:
Константа | Значение | Описание |
---|---|---|
xlSortNormal | 0 | числовые и текстовые данные сортируются отдельно (значение по умолчанию) |
xlSortTextAsNumbers | 1 | текстовые данные рассматриваются для сортировки как числовые |
4. .SetRange [Range] – метод SetRange объекта Sort задает диапазон (таблицу), в котором выполняется сортировка.
5. .Header = [xlGuess, xlYes, xlNo] – свойство Header объекта Sort указывает, является ли первая строка таблицы строкой заголовков (шапкой).
Значения, которые может принимать свойство Header:
Константа | Значение | Описание |
---|---|---|
xlGuess | 0 | Excel сам определяет, есть ли строка заголовков |
xlYes | 1 | строка заголовков есть, сортировка ее не затрагивает |
xlNo | 2 | строки заголовков нет (значение по умолчанию) |
6. .MatchCase = [True, False] – свойство MatchCase объекта Sort указывает, как учитывать регистр при сортировке.
Значения, которые может принимать свойство MatchCase:
Константа | Значение | Описание |
---|---|---|
False | 0 | регистр не учитывается (значение по умолчанию) |
True | 1 | сортировка с учетом регистра |
7. .Orientation = [xlTopToBottom, xlLeftToRight] – свойство Orientation объекта Sort задает ориентацию для сортировки.
Значения, которые может принимать свойство Orientation:
Константа | Значение | Описание |
---|---|---|
xlTopToBottom | 1 | сортировка по стокам (значение по умолчанию) |
xlLeftToRight | 2 | сортировка по столбцам |
8. .Apply – метод Apply объекта Sort выполняет сортировку диапазона в соответствии с примененными параметрами.
Примеры сортировки
Таблица для примеров
Сортировка по одному столбцу
Краткая запись кода VBA Excel для сортировки диапазона по первому столбцу с параметрами по умолчанию:
выражение: переменная, представляющая объект Range.
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
Ключ 1 | Необязательный | Variant | Указывает первое поле сортировки в виде имени диапазона (string) или объекта Range ; определяет значения для сортировки. |
Заказ 1 | Необязательный | XlSortOrder | Определяет порядок сортировки для значений, указанных в key1. |
Ключ 2 | Необязательный | Variant | Второе поле сортировки; не может использоваться при сортировке сводной таблицы. |
Type | Необязательный | Variant | Указывает тип элементов, которые должны быть отсортированы в сводной таблице. Укажите xLSortLabels для сортировки по меткам, на которые нацелен диапазон experession в областях меток строк и столбцов диапазона сводной таблицы, или xLSortValues , если Key1 дополнительно нацелен на ячейку в областях значений или вложенных или общих итогов с помощью нотации R1C1. |
Заказ 2 | Необязательна | XlSortOrder | Определяет порядок сортировки для значений, указанных в key2. |
Ключ 3 | Необязательный | Variant | Третье поле сортировки; не может использоваться при сортировке сводной таблицы. |
Заказ 3 | При необходимости | XlSortOrder | Определяет порядок сортировки для значений, указанных в key3. |
Header | При необходимости | XlYesNoGuess | Указывает, содержит ли первая строка сведения о заголовке. Значение по умолчанию — xlNo; укажите xlGuess, если Excel попытаться определить заголовок. |
OrderCustom | Необязательный | Variant | Задает однона основе целочисленного смещения в списке пользовательских заказов сортировки. |
MatchCase | Необязательный | Variant | Задайте значение True для выполнения сортировки с учетом регистра, False — для сортировки без учета регистра; не может использоваться со сводными таблицами. |
Orientation | При необходимости | XlSortOrientation | Указывает, должна ли сортировка быть по строке (по умолчанию) или столбцу. Для сортировки по столбцу задайте для значения xlSortColumns значение 1. Задайте значение xlSortRows 2 для сортировки по строкам (это значение по умолчанию). |
SortMethod | При необходимости | XlSortMethod | Указывает метод сортировки. |
DataOption1 | При необходимости | XlSortDataOption | Указывает, как сортировать текст в диапазоне, указанном в key1; не применяется к сортировке сводной таблицы. |
DataOption2 | Необязательна | XlSortDataOption | Указывает, как сортировать текст в диапазоне, указанном в key2; не применяется к сортировке сводной таблицы. |
DataOption3 | Необязательна | XlSortDataOption | Указывает способ сортировки текста в диапазоне, указанном в key3; не применяется к сортировке сводной таблицы. |
Возвращаемое значение
Пример
Этот пример получает значение цвета ячейки в столбце A с помощью свойства ColorIndex , а затем использует это значение для сортировки диапазона по цвету.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
В нем происходит зашита листа и автоматическое обновление данных(связи с другими документами). На снимке видны красные и зеленые столбцы. Пользователь вводит данные только в зеленый столбец. Мне нужно прописать макрос, что бы при открытие документа сортировалась вся таблица по столбцу D от максимального к минимальному.
Написал это, но не работает.
Макрос для автоматической вставки фото в лист Excel
Подскажите макрос вставить фото как фон к примечанию в Excel. Пример в файле. Фото в столбце 2.
Макрос для автоматической установки времени при заполнении ячейки
Здравствуйте! Подскажите как сделать чтобы при заполнении ячейки А1 в ячейке В1 устанавливалось.
Макрос сортировки для Excel
Дали срочное задание для работы, которая не соответствует моей должности 1. Запуск осуществляется.
Макрос для автоматической нумерации списка
Здравствуйте, подскажите пожалуйста, имеется макрос автоматической нумерации списка в таблице по.
Первым срабатывает Private Sub Workbook_Open() затем Sub Auto_open(). Вот только он ругается на код сортировки
Добавлено через 1 час 8 минут
Ругается конкретно на:
Все получилось да наоборот)Сортирует от меньшего к большему) Не пойму где косякнул
Добавлено через 2 минуты
запись макроса ещё раз
Добавлено через 1 минуту
Range("A6:EJ499") наверное нельзя было менять на Columns("AT")
Это мой недочет))У меня в тестовом файле не было заголовков, потом дошло только)))И все же, сортирует от менбшего, а мне нужно от большего
smeckoi77, спасибо. Работает. У меня к вам вопрос еще такой. Так же при открытии документа, но срабатывал фильтр если >0. И какие вы источники посоветуете для изучения vba excel. Заранее спасибо.
для фильтра нужно написать свой код
vba excel - полно всего в интернете
А ссылкой не поделитесь на простой пример фильтра макросом vba? Перерыл весь интернет, только огромные и сложные конструкции. С чего нибудь попроще начать бы)
Решение
Нужен макрос для автоматической замены переменных в формулах
Доброго дня всем! Пожалуйста, помогите написать макрос. В прикрепленном файле Пример макрос.
Макрос сортировки строк в Excel
Добрый вечер, помогите пожалуйста с макросом в excel 2010. Есть таблица шириной в 6 столбцев (A-F).
Открытие запороленного excel документа
Возникла проблема с вводом пароля при открытии excel в билдере. Ошибка возникает отсюда.
Отсортируем формулами таблицу, состоящую из 2-х столбцов. Сортировку будем производить по одному из столбцов таблицы (решим 2 задачи: сортировка таблицы по числовому и сортировка по текстовому столбцу). Формулы сортировки настроим так, чтобы при добавлении новых данных в исходную таблицу, сортированная таблица изменялась динамически. Это позволит всегда иметь отсортированную таблицу без вмешательства пользователя. Также сделаем двухуровневую сортировку: сначала по числовому, затем (для повторяющихся чисел) - по текстовому столбцу.
Пусть имеется таблица, состоящая из 2-х столбцов. Один столбец – текстовый: Список фруктов ; а второй - числовой Объем Продаж (см. файл примера ).
Задача1 (Сортировка таблицы по числовому столбцу)
Необходимо отсортировать строки таблицы по содержимому числового столбца (по Объему продаж). Предполагается, что пользователь постоянно заполняет строки таблицы, поэтому необходимо написать формулы с учетом добавляемых значений.
Для наглядности величины значений в столбце Объем Продаж выделены с помощью Условного форматирования ( Главная/ Стили/ Условное форматирование/ Гистограммы ). Также желтым выделены повторяющиеся значения.
Примечание : Задача сортировки отдельного столбца (списка) решена в статьях Сортированный список (ТЕКСТовые значения) и Сортированный список (ЧИСЛОвые значения) .
Решение1
Если числовой столбец гарантировано не содержит повторяющихся значений, то задача решается легко:
- Числовой столбец отсортировать функцией НАИБОЛЬШИЙ() (см. статью Сортированный список (ЧИСЛОвые значения) );
- Функцией ВПР() или связкой функций ИНДЕКС()+ПОИСКПОЗ() выбрать значения из текстового столбца по соответствующему ему числовому значению.
Однако, в реальных задачах числовой столбец может содержать повторы, а так как функция ВПР() в случае наличия повторов всегда выбирает только первое значение сверху (см. статью Функция ВПР() в MS EXCEL ), то этот подход не годится (названия Фруктов будут выведены неправильно).
Поэтому механизм сортировки придется реализовывать по другому.
Создадим для удобства 2 Динамических диапазона Фрукты и Продажи , которые будут ссылаться на диапазоны ячеек, содержащие значения в соответствующих столбцах исходной таблицы. При добавлении новых строк в таблицу, границы этих динамических диапазонов будут автоматически расширяться.
В столбцах D и E разместим таблицу, которая будет динамически сортироваться,
В ячейке Е7 запишем зубодробительную формулу массива :
Аналогичную формулу можно написать для вывода значений в столбец Фрукты =ИНДЕКС(Фрукты;ОКРУГЛ(. ))
В файле примера , из-за соображений скорости вычислений (см. ниже), однотипная часть формулы, т.е. все, что внутри функции ОКРУГЛ() , вынесена в отдельный столбец J . Поэтому итоговые формулы в сортированной таблице выглядят так: =ИНДЕКС(Фрукты;J7) и =ИНДЕКС(Продажи;J7)
Также, изменив в формуле массива функцию НАИБОЛЬШИЙ() на НАИМЕНЬШИЙ() получим сортировку по возрастанию.
Для наглядности, величины значений в столбце Объем Продаж выделены с помощью Условного форматирования ( Главная/ Стили/ Условное форматирование/ Гистограммы ). Как видно, сортировка работает.
Тестируем
Теперь добавим новую строку в исходную таблицу. В динамически сортируемых таблицах мы должны получить соответствующую сортировку.
1. В ячейку А15 исходной таблицы введите слово Морковь ; 2. В ячейку В15 введите Объем продаж Моркови = 25; 3. После ввода значений, в столбцах D и Е автоматически будет отображена отсортированная по убыванию таблица; 4. В сортированной таблице новая строка будет отображена предпоследней.
Скорость вычислений формул
На "среднем" по производительности компьютере пересчет пары таких формул массива, расположенных в 100 строках, практически не заметен. Для таблиц с 300 строками время пересчета занимает 2-3 секунды, что вызывает неудобства. Либо необходимо отключить автоматический пересчет листа ( Формулы/ Вычисления/ Параметры вычисления ) и периодически нажимать клавишу F9 , либо отказаться от использования формул массива, заменив их столбцами с соответствующими формулами, либо вообще отказаться от динамической сортировки в пользу использования стандартных подходов (см. следующий раздел).
Альтернативные подходы к сортировке таблиц
Отсортируем строки исходной таблицы с помощью стандартного фильтра (выделите заголовки исходной таблицы и нажмите CTRL+SHIFT+L ). В выпадающем списке выберите требуемую сортировку.
Получим идентичный нашему вариант таблицы, но при добавлении в таблицу новых значений придется применять фильтр заново.
Также можно воспользоваться инструментом Сортировка ( Данные/ Сортировка и Фильтр/ Сортировка ). Для этого нужно выделить все значения исходной таблицы не включая заголовок, вызвать инструмент Сортировка, выбрать столбец, по которому требуется сортировать и вариант сортировки.
Получим идентичный нашему вариант таблицы, но при добавлении новых значений также придется применять фильтр заново.
При использовании Таблиц в формате EXCEL2007 мы также не получим динамической сортировки. Новое значение (Картофель) останется последним в исходной таблице (до принудительной сортировки таблицы через фильтр), не смотря на его значение продаж (200).
Какой вариант предпочтительней - как всегда - выбирать разработчику.
Еще одна формула массива (+дополнительный столбец). Задача1.1
Сортировку таблицы можно сделать с помощью другой, более простой формулы массива , но нам понадобится дополнительный (служебный) столбец D (см. файл примера лист Пример2 ):
В столбце F содержится отсортированный столбец В (объем продаж). Формула возвращает позицию значения объема продаж. Например, число 86 находится в 5-й строке таблицы.
Для повторов выражение ЕСЛИ(F8=$B$7:$B$14;СТРОКА($B$7:$B$14)-СТРОКА($B$6);0) будет возвращать несколько значений: , т.е. число 74 находится в строках 2 и 8.
С помощью функции НАИБОЛЬШИЙ() сначала выводится 2, затем 8 (в разных строках).
Эта формула более наглядна, чем рассмотренная выше в начале статьи, но требует наличия дополнительного столбца.
Задача2 (Сортировка таблицы по текстовому столбцу)
Отсортируем строки таблицы по содержимому Текстового столбца (по Фруктам).
Примечание : Про сортировку списка текстовых значений можно прочитать в статье Сортированный список в MS EXCEL (ТЕКСТовые значения)
Как и в предыдущей задаче предположим, что в столбце, по которому ведется сортировка имеются повторы (названия Фруктов повторяются).
Для сортировки таблицы придется создать 2 служебных столбца (D и E).
Эта формула является аналогом ранга для текстовых значений (позиция значения относительно других значений списка). Текстовому значению, расположенному ниже по алфавиту, соответствует больший "ранг". Например, значению Яблоки соответствует максимальный "ранг" 7 (с учетом повторов).
В столбце E введем обычную формулу:
Эта формула учитывает повторы текстовых значений и корректирует "ранг". Теперь разным значениям Яблоки соответствуют разные "ранги" - 7 и 8. Это позволяет вывести список сортированных значений. Для этого используйте формулу (столбец G):
Аналогичная формула выведет соответствующий объем продаж (столбец Н).
Задача 2.1 (Двухуровневая сортировка)
Теперь снова отсортируем исходную таблицу по Объему продаж. Но теперь для повторяющихся значений (в столбце А три значения 74), соответствующие значения выведем в алфавитном порядке.
Читайте также: