Vba excel сортировка таблицы

Сортировка диапазона

Процедура для сортировки диапазона по любому количеству ключевых полей (строк или столбцов).

sh — лист, на котором происходит сортировка. Если это текущий лист, то укажите ActiveSheet.
fisrt_row, first_col — координаты первой ячейки (в верхнем левом углу диапазона)
last_row, last_col — координаты последней ячейки (в нижнем правом углу диапазона)

Header — содержит ли диапазон заголовки? False/True
Column — сортировать по столбцам (True) или по строкам (False)?

fields — массив, содержащий номера столбцов или строк (в стиле нумерации R1C1) по которым происходит сортировка. Порядок следования в массиве отражает приоритет ключевого поля — сначала сортируется по первому указанному в массиве полю, потом по второму, затем по третьему и т.д. Игнорируются нули и нечисловые элементы, а также ключевые поля не содержащиеся в диапазоне. Дробные числа приводятся к целым.
Значения полей можно указывать отрицательными! В этом случае номер ключевого поля — модуль элемента, данное поле будет сортироваться по убыванию.

Теперь покажу, как это чудовище применяется. Допустим, есть диапазон 15 на 15, заполненный в случайном порядке нулями и единицами. Нужно отсортировать его в первую очередь по первому столбцу диапазона, потом по второму, потом по третьему и так до самого последнего столбца.

Чтобы было нагляднее, добавим строку и столбец с вертикальной и горизонтальной нумерациями. Теперь будет легко отследить, как именно отсортировался массив. Они не будут ключевыми полями, но при этом войдут в сортируемый диапазон.

Отсортируем по столбцам. Для этого заполним массив числами от 2 до 16 (номера столбцов на листе) и передадим этот массив в функцию. Укажем что диапазон с заголовками — таковые находятся в строке с нумерацией столбцов.

Как видите, в первом столбце диапазона (не забывайте, что первый столбец диапазона это не первый столбец листа) нули и единицы упорядочены. Это в свою очередь разбивает весь диапазон на два горизонтальных поддиапазона: обратите внимание в первой колонке на нули со 2-й строки по 6-ю и на единицы с 7-й по 16-ю. Оба поддиапазона фактически сортируются по следующему столбцу, в их пределах в следующем столбце тоже наблюдаем сортировку по нулям и единицам. Это в свою очередь разбивает поддиапазоны на ещё более мелкие поддиапазоны, которые сортируются по следующему столбцу и так далее. При этом по нумерации строк диапазона можно проверить, что в строках элементы находятся на своих местах.

Также стоит отметить, что хотя мы честно применили сортировку по всем 15 столбцам, на самом деле применилось только первые 7 или 8 из них. В дальнейшем сортировка по более старшим колонкам не имела значения, потому что там сортируемые поддиапазоны состояли всего из одной строки.

Отсортируем по строкам. Нам нужно отследить как перемешались столбцы, поэтому строку с нумерацией колонок мы как содержащую заголовки не указываем. А чтобы нам не путалась нумерация строк, мы не станем вносить первую колонку в сортируемый диапазон.

Итак, что мы видим, товарищи? Нули и единицы красиво выстраиваются в ряд, на каждой следующей строке словно в матрёшке начинаются вертикальные поддиапазоны поменьше, у которых в ряду тоже нули идут впереди единиц. Пользуясь нумерацией колонок, можете сравнить с оригиналом. Для проверки сравниваются нули/единицы не построчно, а «постолбцово».

Ну и на сладенькое покажу как сортировать по убыванию. Давайте отсортируем таким причудливым образом — если это нечётный по счёту столбец диапазона, то сортируем по убыванию, а если чётный — то по возрастанию.

Те столбцы, которые нужно сортировать по убыванию, мы заносили в массив ключевиков со знаком минус. Если внимательно проверите результат, то увидите что с каждой следующей колонкой направление сортировки меняется на противоположное.

Vba excel сортировка таблицы

На этом шаге мы рассмотрим параметры этого метода и пример его использования.

Сортировка позволяет выстраивать данные в лексикографическом порядке по возрастанию или убыванию. Метод Sort осуществляет сортировку строк списков и баз данных, а также столбцов рабочих листов с учетом до трех критериев, по которым производится сортировка. Сортировка данных вручную совершается с использованием команды Данные | Сортировка (Data | Sort).

Читать еще: Vba excel возведение в степень

Например, диапазон А1:С20 рабочего листа Лист1 сортируется следующей командой в порядке возрастания так, что первоначальная сортировка происходит по первому столбцу этого диапазона, а второстепенная — по второму.

Приведем соответствие между аргументами метода Sort и сортировкой данных на рабочем листе вручную при помощи команды Данные | Сортировка(Data | Sort).

A1:G13

Рис.1. Сортируемый список

В методе Sort за диапазон с записями, подлежащими сортировке, отвечает объект, к которому применяется метод. В данном случае метод Sort надо применить к диапазону Range(«A1:G13»).

Выберем команду Данные | Сортировка(Data | Sort). В результате появится диалоговое окно Сортировка диапазона(Sort) (рисунок 2).

Рис.2. Диалоговое окно Сортировка диапазона

Используя это окно, можно установить до трех критериев, по которым производится сортировка.

Выбор в списках Сортировать по(Sort by), Затем по (Then by) и В последнюю очередь, по(Then by) определяют поля, используемые для фильтрации списка.
Переключатели по возрастанию(Ascending) и по убыванию(Descending), расположенные рядом с каждым раскрывающимся списком, определяют порядок сортировки.
Записи для идентификации полей, выводимые в раскрывающихся списках, определяются группой Идентифицировать диапазон данных по(My list has). Если в этой группе выбран переключатель подписям (первая строка диапазона)(Header row), то в раскрывающихся списках выводятся тексты из первых строк диапазонов. Если выбран переключатель обозначениям столбцов листа(No header row), то выводятся названия столбцов.

Например, отсортируем базу данных о туристах в порядке возрастания полей, выбранных в качестве критериев сортировки, установив первоначальным критерием поле Направление тура, вторичным — Оплачено. Сортировку по третьему критерию производить не будем. Так как был выбран диапазон A1:G13, содержащий названия полей, то для идентификации полей выберем в группе Идентифицировать поля по(My list has) переключатель подписям (первая строка диапазона)(Header row). В методе Sort за выбор поля, по которому производится первоначальная сортировка, отвечает аргумент key1. В данном случае для выбора поля Направление тура аргументу key1 надо присвоить значение Range («D2»). Порядок сортировки по первому критерию устанавливается аргументом order1. В данном случае сортируем по возрастанию, поэтому аргументу order1 присваиваем xlAscending. Вторичная сортировка происходит по полю Оплачено по возрастанию, поэтому аргументам key2 и order2 присваиваем Range («E2») и xlAscending соответственно. Параметром, отвечающим за идентификацию полей, является header. В данном случае в группе Идентифицировать поля по(My list has) выбран переключатель подписям (первая строка диапазона)(Header row), поэтому параметру header присвоим значение xlYes. Таким образом, имеем:

Нажатие кнопки OK приведет к сортировке записей по указанным критериям (рисунок 3).

Рис.3. Результат сортировки

На следующем шаге мы рассмотрим метод Subtotal.

Сортировка диапазона

Процедура для сортировки диапазона по любому количеству ключевых полей (строк или столбцов).

Header — содержит ли диапазон заголовки? False/True
Column — сортировать по столбцам (True) или по строкам (False)?

ШКОЛА ПРОГРАММИРОВАНИЯ

Вы здесь

Сообщение об ошибке

Сортировка листов в Excel с помощью макроса

В этом уроке разберем один из способов сортировки листов по алфавиту.

Максимальное количество листов в книге Excel ограничено размером доступной оперативной памяти. Когда количество листов в книге превышает двух десятков, то удобнее искать нужный лист, если их имена отсортированы по возрастанию.

В MS Excel нет встроенного средства для такой сортировки, создадим простой макрос для упорядочивания листов.

Добавим в нашу рабочую книгу лист и в первом столбце этого листа запишем все названия листов данной книги. Отсортируем диапазон с именами листов, переместим листы согласно сортировке и готово!

Теперь подробнее разберем используемые методы в алгоритме

синтаксис Worksheets.Add (Before, After, Count, Type) — добавление листов в книгу Excel

After— указывает лист, после которого будет добавлен рабочий лист

Before— указывает лист, перед которым будет добавлен рабочий лист

Count — количество добавляемых листов, по умолчанию 1

Type — тип рабочего листа, по умолчанию xlWorkSheet

Если Before и After опущены, то по умолчанию лист размещается после активного листа

синтаксис Worksheets.Move (Before|After) — перемещение рабочего листа в другое место рабочей книги Excel

After— указывает лист, после которого будет перемещен рабочий лист

Before— указывает лист, перед которым будет перемещен рабочий лист

Одновременно может быть указан только один аргумент (After или Before)

Воспользуемся встроенной возможностью Excel – сортировкой диапазона.

ДиапазонТаблицы.Sort ([Key1], [Order1 As XlSortOrder = xlAscending], [Key2], [Type], [Order2 As XlSortOrder = xlAscending], [Key3], [Order3 As XlSortOrder = xlAscending], [Header As XlYesNoGuess = xlNo], [OrderCustom], [MatchCase], [Orientation As XlSortOrientation = xlSortRows], [SortMethod As XlSortMethod = xlPinYin], [DataOption1 As XlSortDataOption = xlSortNormal], [DataOption2 As XlSortDataOption = xlSortNormal], [DataOption3 As XlSortDataOption = xlSortNormal])

ДиапазонТаблицы — диапазон Range для сортировки

Key1 — первое упорядочиваемое поле

Order1 — порядок сортировки, xlAscending- по возрастанию, xlDescending- по убыванию

Header — заголовок (xlNo — отсутствует, xlYes — есть, xlGuess — Excel определяет сам)

OrderCustom — пользовательский порядок сортировки, по умолчанию Normal

MatchCase — True или False (учитывается или нет регистр)

Orientation — направление сортировки, xlSortRows — по строкам, xlSortColumns — по столбцам

Диапазон до сортировки

Диапазон после сортировки

Application.DisplayAlerts = False отключение оповещений

Без этой команды при выполнении макроса перед удалением листа появится сообщение

Сортировка данных в Excel

Если данные текстовые, их можно отсортировать по алфавиту («от А до Я» или «от Я до А»). Если данные числовые, их можно отсортировать в порядке возрастания или убывания. Если в диапазоне данных есть строка или столбец, в которых содержатся данные типа время или дата, их можно отсортировать в прямом или обратном хронологическом порядке. Имеется также возможность сортировки предварительно отформатированных данных по элементам этого форматирования.

Сортировать данные можно по одному условию (например, сортировка списка сотрудников по фамилии) или нескольким (например, сортировка списка сотрудников по занимаемой должности, а внутри каждой должности фамилии отсортировать в алфавитном порядке). Данные можно сортировать по столбцу (или нескольким столбцам) или по строке.

Сортировка по одному критерию

В столбце, по которому должна быть выполнена сортировка, нужно выделить любую ячейку (весь столбец выделять не надо).
На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter].

Выбрать нужную кнопку: – сортировка по возрастанию или сортировка по убыванию.

Отметим, что буквы на этой кнопке указывают только на направление сортировки, а вид кнопки остается один и тот же и при текстовых, и при числовых данных.

Существует и другой удобный способ сортировки данных: щелкнув правой кнопкой мыши по ячейке столбца, по которому будет выполняться сортировка, в контекстном меню выбрать пункт Сортировка [Sort], а далее – требуемый вариант сортировки.

Многоуровневая сортировка

Выделить одну ячейку из сортируемого массива данных.

Если диапазоне данных имеются пустые столбцы или строкой, то Excel автоматически воспринимает их как границы сортируемого массива данных. В таком случае следует выделить все данные, подлежащие сортировке.

На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter] и на ней выбрать команду Сортировка [Sort].
Последовательно задать уровни сортировки (определяемые именем столбца).

Нажимая на стрелку возле трех полей (Столбец, Сортировка, Порядок) необходимо выбрать:

Имя столбца для сортировки.
Тип критерия (в зависимости от того, будет ли вестись сортировка по значениям данных в столбце, или по оформлению ячейки, или по значку ячейки).
Порядок сортировки (по убыванию или по возрастанию).

Если выбранный для сортировки столбец содержит названия месяцев или дней недели, то в списке поля Порядок можно выбрать опцию Настраиваемый список и в новом окне отметить один из предлагаемых вариантов сортировки.

Сортировка по форматированию

Часто для анализа данных делается заливка ячеек (или шрифта) цветом. С помощью сортировки можно также упорядочивать данные на основе их форматирования.

Пошаговый порядок действий:

Щелкнуть по любой ячейки из столбца, по которому будет выполняться сортировка.
На вкладке Данные [Data] выбрать группу Сортировка и фильтр [Sort&Filter], а затем выбрать команду Сортировка [Sort].
В поле Столбец [Column] укажите столбец по которому будет проводиться сортировка.
В поле Сортировка [Sort On] из всплывающего меню выбрать критерий сортировки: цвет ячейки, цвет шрифта или значок ячейки.
Поле Порядок [Order] содержит два выпадающих списка. В первом нужно выбрать тип критерия, а во втором – размещение ячеек, отсортированных по данному критерию (строку Сверху [On Top] или Снизу [On Bottom]).
При необходимости добавить еще один критерий сортировки, в окне Сортировка нужно выбрать кнопку Добавить уровень.

Можно также воспользоваться командой «Копировать уровень» [Copy Level], заменив в поле «Порядок» прежнее значение на новое.

После выбора сортировки нажать кнопку ОК.

0 0 голоса

Рейтинг статьи