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 из них. В дальнейшем сортировка по более старшим колонкам не имела значения, потому что там сортируемые поддиапазоны состояли всего из одной строки.
Отсортируем по строкам. Нам нужно отследить как перемешались столбцы, поэтому строку с нумерацией колонок мы как содержащую заголовки не указываем. А чтобы нам не путалась нумерация строк, мы не станем вносить первую колонку в сортируемый диапазон.
Итак, что мы видим, товарищи? Нули и единицы красиво выстраиваются в ряд, на каждой следующей строке словно в матрёшке начинаются вертикальные поддиапазоны поменьше, у которых в ряду тоже нули идут впереди единиц. Пользуясь нумерацией колонок, можете сравнить с оригиналом. Для проверки сравниваются нули/единицы не построчно, а “постолбцово”.
Ну и на сладенькое покажу как сортировать по убыванию. Давайте отсортируем таким причудливым образом – если это нечётный по счёту столбец диапазона, то сортируем по убыванию, а если чётный – то по возрастанию.
Те столбцы, которые нужно сортировать по убыванию, мы заносили в массив ключевиков со знаком минус. Если внимательно проверите результат, то увидите что с каждой следующей колонкой направление сортировки меняется на противоположное.
Источник: www.vba.valemak.com
Vba excel сортировка диапазона
На этом шаге мы рассмотрим параметры этого метода и пример его использования .
Сортировка позволяет выстраивать данные в лексикографическом порядке по возрастанию или убыванию. Метод Sort осуществляет сортировку строк списков и баз данных, а также столбцов рабочих листов с учетом до трех критериев, по которым производится сортировка. Сортировка данных вручную совершается с использованием команды Данные | Сортировка (Data | Sort) .
| Аргумент | Назначение |
|---|---|
| Объект | Диапазон, который будет сортироваться |
| key1 | Ссылка на первое упорядочиваемое поле |
| order1 | Задает порядок упорядочивания. Допустимые значения:
|
| key2 | Ссылка на второе упорядочиваемое поле |
| order2 | Задает порядок упорядочивания. Допустимые значения:
|
| key3 | Ссылка на третье упорядочиваемое поле |
| order3 | Задает порядок упорядочивания. Допустимые значения:
|
| header | Допустимые значения:
|
| orderCustom | Пользовательский порядок сортировки. По умолчанию используется Normal |
| matchCase | Допустимые значения: True (учитывается регистр) и False (регистр не учитывается) |
| orientation | Допустимые значения:
|
Например, диапазон А1:С20 рабочего листа Лист1 сортируется следующей командой в порядке возрастания так, что первоначальная сортировка происходит по первому столбцу этого диапазона, а второстепенная — по второму.
Приведем соответствие между аргументами метода Sort и сортировкой данных на рабочем листе вручную при помощи команды Данные | Сортировка (Data | Sort) .
Выделяем диапазон A1:G13 , содержащий записи базы данных о туристах, которые должны быть отсортированы (рисунок 1).
Рис.1. Сортируемый список
В методе Sort за диапазон с записями, подлежащими сортировке, отвечает объект, к которому применяется метод. В данном случае метод Sort надо применить к диапазону R ange(“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 .
Источник: it.kgsu.ru
Метод Range. Sort (Excel) Range.Sort method (Excel)
Сортирует диапазон значений. Sorts a range of values.
Синтаксис Syntax
Expression. Sort (сортировка ) (Key1, Order1, key2, Type, Order2, Key3, Order3, Header, ордеркустом, MatchCase, Orientation, сортмесод, _ DataOption1_, DataOption2, DataOption3) expression.Sort (Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)
выражение: переменная, представляющая объект Range. expression A variable that represents a Range object.
Параметры Parameters
| Имя Name | Обязательный или необязательный Required/Optional | Тип данных Data type | Описание Description |
|---|---|---|---|
| Key1 Key1 | Необязательный Optional | Variant Variant | Задает первое поле сортировки в виде имени диапазона (String) или объекта Range ; Определяет значения, которые требуется отсортировать. Specifies the first sort field, either as a range name (String) or Range object; determines the values to be sorted. |
| Order1 Order1 | При необходимости Optional | XlSortOrder XlSortOrder | Определяет порядок сортировки значений, указанных в Key1. Determines the sort order for the values specified in Key1. |
| Key2 Key2 | Необязательный Optional | Variant Variant | Второе поле сортировки; не может использоваться при сортировке сводной таблицы. Second sort field; cannot be used when sorting a PivotTable. |
| Type Type | Необязательный Optional | Variant Variant | Указывает, какие элементы будут отсортированы. Specifies which elements are to be sorted. |
| Order2 Order2 | При необходимости Optional | XlSortOrder XlSortOrder | Определяет порядок сортировки значений, указанных в поле key2. Determines the sort order for the values specified in Key2. |
| Key3 Key3 | Необязательный Optional | Variant Variant | Третье поле сортировки; не может использоваться при сортировке сводной таблицы. Third sort field; cannot be used when sorting a PivotTable. |
| Order3 Order3 | При необходимости Optional | XlSortOrder XlSortOrder | Определяет порядок сортировки значений, указанных в Key3. Determines the sort order for the values specified in Key3. |
| Header Header | При необходимости Optional | XlYesNoGuess XlYesNoGuess | Указывает, содержит ли первая строка сведения о заголовке. Specifies whether the first row contains header information. кслно — значение по умолчанию; Укажите кслгуесс , если вы хотите, чтобы Excel пытался определить заголовок. xlNo is the default value; specify xlGuess if you want Excel to attempt to determine the header. |
| Ордеркустом OrderCustom | Необязательный Optional | Variant Variant | Указывает целочисленное смещение от единицы в списке настраиваемых порядков сортировки. Specifies a one-based integer offset into the list of custom sort orders. |
| MatchCase MatchCase | Необязательный Optional | Variant Variant | Установите значение true , чтобы выполнять сортировку с учетом регистра, и false для выполнения сортировки без учета регистра; не может использоваться со сводными таблицами. Set to True to perform a case-sensitive sort, False to perform a non-case-sensitive sort; cannot be used with PivotTables. |
| Orientation Orientation | При необходимости Optional | XlSortOrientation XlSortOrientation | Указывает, следует ли сортировать по строкам (по умолчанию) или по столбцу. Specifies if the sort should be by row (default) or column. Задайте для параметра кслсортколумнс значение 1, чтобы сортировать по столбцам. Set xlSortColumns value to 1 to sort by column. Задайте для параметра кслсортровс значение 2, чтобы сортировать по строкам (это значение по умолчанию). Set xlSortRows value to 2 to sort by row (this is the default value). |
| SortMethod SortMethod | При необходимости Optional | XlSortMethod XlSortMethod | Задает метод Sort. Specifies the sort method. |
| DataOption1 DataOption1 | При необходимости Optional | XlSortDataOption XlSortDataOption | Задает способ сортировки текста в диапазоне, указанном в Key1. не применяется к сортировке сводной таблицы. Specifies how to sort text in the range specified in Key1; does not apply to PivotTable sorting. |
| DataOption2 DataOption2 | При необходимости Optional | XlSortDataOption XlSortDataOption | Указывает, как сортировать текст в диапазоне, указанном в поле key2; не применяется к сортировке сводной таблицы. Specifies how to sort text in the range specified in Key2; does not apply to PivotTable sorting. |
| DataOption3 DataOption3 | При необходимости Optional | XlSortDataOption XlSortDataOption | Указывает, как сортировать текст в диапазоне, указанном в Key3; не применяется к сортировке сводной таблицы. Specifies how to sort text in the range specified in Key3; does not apply to PivotTable sorting. |
Возвращаемое значение Return value
Пример Example
В этом примере показано, как получить значение цвета ячейки в столбце A с помощью свойства ColorIndex , а затем использовать это значение для сортировки диапазона по цвету. This example gets the value of the color of a cell in column A by using the ColorIndex property, and then uses that value to sort the range by color.
Поддержка и обратная связь Support and feedback
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь. Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.
Источник: docs.microsoft.com
Vba excel сортировка диапазона
На этом шаге мы рассмотрим параметры этого метода и пример его использования .
Сортировка позволяет выстраивать данные в лексикографическом порядке по возрастанию или убыванию. Метод Sort осуществляет сортировку строк списков и баз данных, а также столбцов рабочих листов с учетом до трех критериев, по которым производится сортировка. Сортировка данных вручную совершается с использованием команды Данные | Сортировка (Data | Sort) .
| Аргумент | Назначение |
|---|---|
| Объект | Диапазон, который будет сортироваться |
| key1 | Ссылка на первое упорядочиваемое поле |
| order1 | Задает порядок упорядочивания. Допустимые значения:
|
| key2 | Ссылка на второе упорядочиваемое поле |
| order2 | Задает порядок упорядочивания. Допустимые значения:
|
| key3 | Ссылка на третье упорядочиваемое поле |
| order3 | Задает порядок упорядочивания. Допустимые значения:
|
| header | Допустимые значения:
|
| orderCustom | Пользовательский порядок сортировки. По умолчанию используется Normal |
| matchCase | Допустимые значения: True (учитывается регистр) и False (регистр не учитывается) |
| orientation | Допустимые значения:
|
Например, диапазон А1:С20 рабочего листа Лист1 сортируется следующей командой в порядке возрастания так, что первоначальная сортировка происходит по первому столбцу этого диапазона, а второстепенная — по второму.
Приведем соответствие между аргументами метода Sort и сортировкой данных на рабочем листе вручную при помощи команды Данные | Сортировка (Data | Sort) .
Выделяем диапазон A1:G13 , содержащий записи базы данных о туристах, которые должны быть отсортированы (рисунок 1).
Рис.1. Сортируемый список
В методе Sort за диапазон с записями, подлежащими сортировке, отвечает объект, к которому применяется метод. В данном случае метод Sort надо применить к диапазону R ange(“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 .
Источник: it.kgsu.ru
Канал в Telegram
Вы здесь
Сортировка листов в 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 отключение оповещений
Без этой команды при выполнении макроса перед удалением листа появится сообщение
Источник: www.programm-school.ru
Сортировка в Excel – основные сведения
Сортировка данных в Excel – это очень полезный инструмент, который позволяет улучшать восприятие информации, особенно при больших объемах. В данном уроке мы научимся применять сортировку, узнаем основные команды, а также познакомимся с типами сортировки в Excel.
При добавлении данных в Excel очень важно грамотно организовать информацию на рабочем листе. Одним из инструментов, который позволяет сделать это, является сортировка. С помощью сортировки Вы можете сформировать список контактной информации по фамилии, расположить содержимое таблицы в алфавитном порядке или же в порядке убывания.
Типы сортировки в Excel
При сортировке данных в Excel в первую очередь необходимо решить, как применять сортировку: ко всему листу (таблице) или только к определенному диапазону ячеек.
- Сортировка листа (таблицы) систематизирует все данные по одному столбцу. При применении сортировки к листу связанная информация в каждой строке сортируется совместно. В следующем примере столбец Contact name (столбец А) отсортирован в алфавитном порядке.
- Сортировка диапазона упорядочивает данные в диапазоне ячеек. Такая сортировка может быть полезной при работе с листами Excel, содержащими несколько таблиц с информацией, расположенных вплотную друг к другу. Сортировка, примененная к диапазону, не затрагивает другие данные на листе.
Как сделать сортировку листа (таблицы, списка) в Excel
В следующем примере мы отсортируем форму заказа футболок по Фамилиям (Столбец С) и расположим их в алфавитном порядке.
- Выделите ячейку в столбце, по которому необходимо выполнить сортировку. В нашем примере мы выделим ячейку C2.
- Откройте вкладку Данные на Ленте, затем нажмите команду Сортировка от А до Я, чтобы отсортировать по возрастанию, или команду Сортировка от Я до А, чтобы отсортировать по убыванию. В нашем примере мы выберем команду Сортировка от А до Я.
- Таблица будет отсортирована по выбранному столбцу, т.е. по фамилии.
При сортировке таблицы или списка в Excel необходимо, чтобы они были отделены от посторонних данных на листе как минимум одной строкой или столбцом. В противном случае в сортировке будут участвовать посторонние данные.
Как сделать сортировку диапазона в Excel
В следующем примере мы выберем отдельную небольшую таблицу на листе Excel, чтобы отсортировать количество футболок, заказанных в определенные дни.
- Выделите диапазон ячеек, который требуется отсортировать. В нашем примере мы выделим диапазон A13:B17.
- Откройте вкладку Данные на Ленте, затем нажмите команду Сортировка.
- Откроется диалоговое окно Сортировка. Выберите столбец, по которому необходимо выполнить сортировку. В данном примере мы хотим отсортировать данные по количеству заказов, поэтому выберем столбец Заказы.
- Задайте порядок сортировки (по возрастанию или по убыванию). В нашем примере мы выберем По возрастанию.
- Если все параметры заданы правильно, нажмите ОК.
- Диапазон будет отсортирован по столбцу Заказы от меньшего в большему. Обратите внимание, что остальное содержимое листа сортировке не подвергается.
Если сортировка в Excel выполняется неправильно, то в первую очередь проверьте верно ли введены значения. Даже небольшая опечатка может привести к проблемам при сортировке больших таблиц. В следующем примере мы забыли поставить дефис в ячейке A18, что привело к неточной сортировке.
Источник: office-guru.ru
