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 из них. В дальнейшем сортировка по более старшим колонкам не имела значения, потому что там сортируемые поддиапазоны состояли всего из одной строки.

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

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

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

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

Сортировка диапазона без сортировки в электронной таблице.

1 Alex [2016-05-15 16:53:00]

Вопрос о сортировке данных в VBA. Предположим, что у меня есть Range(«A1:A10») , который я хочу сортировать в порядке возрастания. Тем не менее, я не хочу никаких изменений в моей таблице (так что все вычисления производятся в коде VBA). Выходной сигнал операции должен быть NewRange , где отсортированы все числа.

Есть ли у кого-то идеи об этой проблеме?

sorting vba excel-vba excel

4 Решение Gary’s Student [2016-05-15 17:21:00]

Это всего лишь образец, который вы можете адаптировать к вашим потребностям, он использует B11: B20 как NewRange :

Исходные ячейки сортируются не, они просто копируются в другое место, которое сортируется .

EDIT # 1:

В этой версии NewRange не диапазон ячеек, а внутренний массив:

6 Ralph [2016-05-15 17:21:00]

Вот очень простая небольшая процедура для сортировки двумерного массива, такого как диапазон:

Как использовать эту функцию сортировки:

• Диапазон, отсортированный здесь, находится на Worksheet(1) в Range(«C3:D9») , и выход идет на том же листе в Range(«G3:H9»)
• Диапазон будет отсортирован в порядке возрастания.
• Диапазон будет отсортирован в соответствии с первым столбцом (здесь столбец C). Если вы хотите сортировать другой столбец, вам просто нужно изменить все теги aryToSort(i, 1) и aryToSort(j, 1) , которые вы хотите отсортировать. Например, в столбце 2: aryToSort(i, 2) и aryToSort(j, 2) .

Если вы предпочитаете использовать вышеуказанное как функцию, это также возможно:

И вот как вы будете использовать функцию:

2 skkakkar [2016-05-15 19:06:00]

Здесь я представляю немного другую процедуру сортировки. Он сортирует второй столбец сначала, а затем 1-й столбец.

Результаты, полученные из этой процедуры, показаны ниже.

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

Вот эта строка сортирует у меня столбец, но не так как надо.

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

Неправильная сортировка диапазона
Range(Cells(1, 1), Cells(1500, 30)).Sort Key1:=Range("C1"), Order1:=xlAscending, Header:= _ .

Не проходит сортировка по текстовому полю выделенного диапазона
не проходит сортировка по текстовому полю выделенного диапазона начиная от количества строк.

График без диапазона данных на листе
Есть ли какой-нить метод в VBA, который позволяет построить график так, чтобы данные для этого.

Копирование диапазона, исходя из данных в ячейке
Хелп! Задача вот какая: Есть сформированный список, посредством "проверки данных".

Ограничение диапазона данных, доступных для просмотра
Здравствуйте. Есть книга Эксель, в которой два листа. Из первого данные в виде таблицы копируются.

вернее не так, всё напутал, ссори, сами-то формулы остаются на месте

Формулы в столбце А берут значения из столбца В (в нем просто числа или пусто) и выдают либо какое-то число, либо «» (пусто)

И при сортировке столбца А с параметром Order1:=xlDescending все эти ячейки со значением «» перескакивают вверх, а числа оказываются внизу (по убыванию)

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

Получается, что значение «» (пусто) задаваемое с помощью формулы это совсем не пусто, хоть и выглядет также как совсем пустая ячейка (например, очищенная кнопкой Delete).

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

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

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

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

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

Выделяем диапазон A1:G13, содержащий записи базы данных о туристах, которые должны быть отсортированы (рисунок 1).

Рис.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), то выводятся названия столбцов.

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

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

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

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

673 просмотра

3 ответа

16 Репутация автора

Я боролся с этой проблемой в последние 3 дня, поэтому, пожалуйста, помогите .

То, что я хочу сделать, это когда я запускаю macro1 (ради аргумента):

1. Окно будет всплывать, чтобы выбрать диапазон, в котором ячейки должны быть отсортированы
2. Сортируйте их по последнему выбранному столбцу (или 5-му) (от минимального до максимального числа)

Проблема здесь в том, что выбранная область будет меняться в любое время (я создаю что-то вроде дерева в Excel), поэтому это не может быть конкретный столбец, который должен быть отсортирован по последнему (или в данном случае 5-му) выбранного код ниже я не знаю, как изменить I11: I15)

Что я получил и это не работает

плюса

33951 Репутация автора

Попробуйте получить диапазон, по которому вы сортируете ( I11:I15 ), как отдельную переменную. Чтобы сделать это, вам нужен последний столбец вашего целевого диапазона и последний ряд.

В приведенном ниже коде диапазон, который вы сортируете, rngSort определен через

Чтобы получить последний столбец и последний ряд, вам нужно:

Когда вы будете готовы с этим, rngSort вы просто изменяете I11:I15 часть кода в ней:

Автор: Vityata Размещён: 23.01.2018 09:51

1 плюс

6759 Репутация автора

Вы можете получить конечный столбец rngStart диапазона как:

Используя, With чтобы привести это в порядок, вы можете сделать следующее:

Вы также можете привести в порядок ActiveWorkbook.Worksheets , взяв Parent из rngStart .

Наконец, вы хотите перехватить ошибку, которая возникнет, если пользователь нажмет «Отмена» вместо выбора диапазона. Есть несколько способов сделать это, но первым, кто пришел на ум, было использование On Error.. ловушки.

Автор: CLR Размещён: 23.01.2018 09:57

плюса

Репутация автора

Вернитесь на шаг назад от записанной сортировки VBA к тому, что действительно требуется, и используйте transpose, чтобы изменить диапазон ввода в одномерный массив.

ps Если вы собираетесь выполнить команду сортировки VBA, вы должны знать, есть ли у вас строка заголовка или нет.

Перед подпроцедурой с выбранным локальным E2: E9.