Remkomplekty.ru

IT Новости из мира ПК
7 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba excel sort описание

Vba excel sort описание

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

Сортировка позволяет выстраивать данные в лексикографическом порядке по возрастанию или убыванию. Метод 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), то выводятся названия столбцов.

Например, отсортируем базу данных о туристах в порядке возрастания полей, выбранных в качестве критериев сортировки, установив первоначальным критерием поле Направление тура, вторичным — Оплачено. Сортировку по третьему критерию производить не будем. Так как был выбран диапазон 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.

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

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

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 Quicksort macro to sort arrays fast

The VBA Tutorials Blog

Introduction — VBA Quicksort

The VBA quicksort macro is a fast way to sort VBA arrays. The VBA Quicksort macro in this tutorial is much more efficient than the VBA bubble sort macro I showed last week, so it can be used to quickly sort both small and large one dimensional VBA arrays .

Near the bottom of this tutorial, I’ll show you the results of my benchmark comparing the quicksort and bubble sort macros in Excel VBA. The results are incredible!

Example — VBA Quicksort

Super Fast Quicksort Macro

Make powerful macros with our free VBA Developer Kit

There’s a lot to unpack here. To save time and become really good at VBA, make sure you get our free VBA Developer Kit below. It’s full of tips and pre-built macros to make writing VBA easier.

Tutorial — VBA Quicksort

About Quicksort

The quicksort algorithm is what’s known as a “divide and conquer” algorithm. First, it identifies a pivot value, which is nothing more than a random element inside your array. The example macro assumes the very center element, but it could be any element. After that, it partitions the array by moving elements that are larger than your pivot value to one side of your array and moving elements that are smaller than your pivot to the other side. This is the divide part of “divide and conquer.”

After dividing and partitioning around the pivot, the quicksort algorithm conquers by recursively calling itself to process the two halves, or subarrays, of the original array.

Sorting arrays with the quicksort algorithm is super fast, but how do you handle your array after it’s sorted? To become really good at using arrays, you’ll need to grab a copy of our comprehensive VBA Arrays Cheat Sheet with over 20 pre-built macros and dozens of tips designed to make it easy for you to handle arrays.

Although the worst case performance of the quicksort algorithm is proportional to N^2 (where N is the number of elements in the array), just like bubble sort, it hardly ever takes this long. As a matter of fact, the average performance is classified as a complexity on the order of N*log(N) instead of N^2. That’s much faster than bubble sort! I’ll prove it in a little while.

Like I said earlier, the example macro pivots around the middle element. This is technically a little slower than pivoting around the far left or far right element, but it reduces the likelihood of the algorithm degrading to an N^2 number of iterations if you accidentally try to sort an array that’s already nearly sorted.

Why use the VBA Quicksort algorithm

Because the VBA quicksort algorithm is typically ridiculously faster than the bubble sort algorithm! What other reasons do you need? True, it’s a little harder to understand and the recursive nature makes it more challenging to program, but finding yourself a good quicksort algorithm, like the one in this tutorial, can save you and your code a tremendous amount of time.

To demonstrate what I mean, let’s compare the quicksort and bubble sort algorithms using VBA.

VBA Quicksort vs VBA Bubble Sort

I created a one dimensional VBA array with 25,000 elements populated with random numbers between 0 and 10,000. First, I sorted the array using the VBA bubble sort algorithm from last week’s tutorial. On my machine, bubble sort took a whopping 22.64 seconds.

I then took the exact same array and sorted it using the VBA quicksort algorithm presented in this tutorial. The differences were astonishing. The quicksort method only took 0.07 seconds.

The VBA quicksort macro was 323x faster than the VBA bubble sort macro!

It’s worth noting that the smaller your array size gets, the closer the average performance of the two macros becomes. Even so, it’s clear running the quicksort macro is much faster than using a sluggish bubble sort algorithm.

Here’s my benchmark comparing the performance (in seconds) of the VBA quicksort and VBA bubble sort macros as a function of array size using identical arrays for each comparison:

VBA Quicksort vs Bubble Sort Benchmark

Функция сортировки массива VBA?

Я ищу достойную реализацию сортировки для массивов в VBA. Ртуть была бы предпочтительнее. Или любой другой алгоритм сортировки, кроме bubble или merge, будет достаточно.

Обратите внимание, что это должно работать с MS Project 2003, поэтому следует избегать любых собственных функций Excel и всего, что связано с .net.

10 ответов

Посмотрите здесь:
изменить: источник ссылки (allexperts.com) с тех пор закрыта, но вот соответствующие комментарии автора:

Существует много алгоритмов, доступных в интернете для сортировки. Наиболее универсальным и, как правило, наиболее быстрым является алгоритм Quicksort . Ниже приведена функция для него.

Вызовите его, просто передав массив значений (строковых или числовых; это не имеет значения) с нижней границей массива (обычно 0 ) и верхней границей массива ( UBound(myArray) т. е.)

Пример: Call QuickSort(myArray, 0, UBound(myArray))

Когда это будет сделано, myArray будет отсортирован, и вы можете делать то, что вы хотите с ним.
(Источник: archive.org)

Обратите внимание, что это работает только с одномерным (он же «нормальный»?) матрицы. (Здесь работает многомерный Arraye QuickSort .)

Объяснение на немецком языке, но код является хорошо проверенной на месте реализации:

Вызывается следующим образом:

Я преобразовал алгоритм ‘fast quick sort’ в VBA, если кто-то еще хочет его.

Я оптимизировал его для работы с массивом Int/Longs, но он должен быть простым для преобразования в тот, который работает на произвольных сопоставимых элементах.

Я опубликовал некоторый код в ответ на связанный вопрос о StackOverflow:

Примеры кода в этом потоке включают:

  1. Векторный массив Quicksort;
  2. Многоколонный массив QuickSort;
  3. Пузырчатка.

Оптимизированный Quicksort Алена очень блестящий: я только что сделал базовое разделение и рекурсию, но пример кода выше имеет функцию «стробирования», которая сокращает избыточные сравнения дублированных значений. С другой стороны, я кодирую для Excel, и есть немного больше на пути защитного кодирования — будьте предупреждены, вам это понадобится, если Ваш массив содержит пагубный вариант ‘Empty ()’, который нарушит ваше время… Операторы сравнения Wend и ловушка ваш код в бесконечном цикле.

Обратите внимание, что quicksort алгоритмы — и любой рекурсивный алгоритм — могут заполнить стек и аварийно завершить работу Excel. Если Ваш массив имеет менее 1024 членов, Я бы использовал рудиментарный BubbleSort.

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

  • 2-мерные массивы;
  • не более 3 столбцов в качестве ключей сортировки;
  • зависит от Excel;

Протестирован вызов Excel 2010 из Visio 2010

Это пример тестирования функции:

Если кто-то проверяет это, используя другие версии office, пожалуйста, напишите здесь, если есть какие-либо проблемы.

Натуральное Число (Строки) Быстрая Сортировка

Просто кучи на тему.
Обычно, если вы сортируете строки с числами, вы получите что-то вроде этого:

Но вы действительно хотите, чтобы он распознавал числовые значения и сортировался, как

Вот как это сделать…

  • Я украл быстрый вид из интернета давно, не знаю, где сейчас…
  • Я перевел функцию CompareNaturalNum, которая изначально была написана на C из интернета.
  • Отличие от других Q-сортов: я не меняю значения, если BottomTemp = TopTemp

Натуральное Число Быстрая Сортировка

Сравнение натуральных чисел(используется для быстрой сортировки)

isDigit (используется в CompareNaturalNum)

Интересно, что бы вы сказали об этом коде сортировки массива. Это быстро для реализации и делает свою работу … еще не тестировались для больших массивов. Это работает для одномерных массивов, для многомерных дополнительных значений матрица повторного размещения должна быть построена (с одним меньшим измерением, чем исходный массив).

Это то, что я использую для сортировки в памяти — его можно легко развернуть для сортировки массива.

Я думаю, что мой код (проверенный) более «образован», предполагая, что чем проще, тем лучше .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. После выбора сортировки нажать кнопку ОК.
Читать еще:  C open excel file
Ссылка на основную публикацию
Adblock
detector