Сортировка двумерного массива excel
Сортировка двумерного массива по столбцу
Помогите пожалуйста! У меня есть двумерный массив, Пример 1 5 6 6 3 2 7 2 2 2 Мне нужно.
Сортировка двумерного массива по второму столбцу
Здравствуйте, есть двумерный массив, его надо отсортировать от большего к меньшему по второму.
Сортировка двумерного массива по конкретному столбцу
Необходимо отсортировать двумерный массив по конкретному столбцу в порядке возрастания/убывания.
КостяФедореев, спасибо! сравню скорость отпишусь
Вот такое еще в закромах завалялось:
art1289, что-то по быстрой сортировке выкладывал bedvit
Добавлено через 9 минут
fever brain то же что-то делал по быстрой сортировке, например тут Ускорить работу макроса
Zeag, спасибо!, но это такой же код как у меня.
Добавлено через 4 минуты
пока самый быстрый способ без сторонних библиотек, это выгрузка на лист Excel, создание фильтра и сортировка и обратная загрузка в массив
а откуда берется массив?
Добавлено через 54 секунды
если с листа Excel
то быстрее будет ресордсет помучать
подпилил для двухмерного
Сортировка двумерного массива по одному столбцу
здравствуйте. подскажите как можно отсортировать двумерный массив по одному из столбцов так, чтобы.
Сортировка двумерного массива по второму столбцу
Всем привет.Подскажите как можно отсортировать четырехмерный массив, например по второму столбцу.
Сортировка строк двумерного массива по второму столбцу
Есть исходный массив размерностью . Необходимо отсортировать строки этого массива по возрастанию.
Сортировка строк двумерного массива по первому столбцу
Здравствуйте. Мне нужно отсортировать строки двумерного массива по первому столбцу по возрастанию.
Чем отличается заполнение двумерного массива по столбцу от заполнения по строке?
вопросы: 1.Чем отличается заполнение двумерного массива по столбцу от заполнения по строке.
Та же функция, только с возможностью выбора столбца для сортировки двумерного массива:
Сортировка двумерного массива на листе Excel, по первым 3 столбцам по возрастанию
(создаётся временная книга из 1 листа для сортировки массива, после сортировки книга закрывается)
Ну и обычная пузырьковая сортировка одномерного массива
то же самое, но внутри макроса (arr - одномерный массив)
Комментарии
Игорь, предлагаю на Ваш суд такой вариант сортировки (через временный лист). При сортировке 400к+ текстовых значений время сортировки составляет несколько секунд (для меньших объемов возможно и не требуется такой подход). На вход подается двумерный массив с двумя столбцами, сортировка по первому столбцу, заголовки - опционально.
P.S. табуляция с отступами в коде пропали во время вставки кода на данной страничке. так что извиняйте за такое форматирование
Sub SortValue(ByRef rInitArr As Variant)
Dim sTMPSh$
sTMPSh = "Sortir"
On Error Resume Next
If Sheets(sTMPSh) Is Nothing Then
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Select
Sheets(Sheets.Count).Name = sTMPSh
Else
With Sheets(sTMPSh)
.Select
.Cells.ClearContents
End With
End If
On Error GoTo 0
Application.DisplayAlerts = False
Application.EnableEvents = False
With ActiveWorkbook.Worksheets(sTMPSh)
.Range(.Cells(2, 1), .Cells(UBound(rInitArr), 2)) = rInitArr
.Cells(1, 1) = "Сортируемый столбец"
.Cells(1, 2) = "Второй строки"
With .Sort
.SortFields.Clear
.SortFields.Add Key:=Cells(1, 1), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SetRange Range(Cells(1, 1), Cells(UBound(rInitArr), 2))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
rInitArr = .Range(Cells(2, 1), Cells(UBound(rInitArr), 2))
.Delete
End With
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
При сортировке "скромного" массива на 400к+ строк через предложеннух функцию (первая по счету) комп ушел в "несознанку" на несколько десятков минут (терпение закончилось раньше). Если-то же самое выполнить средствами Worksheet (т.е. стандартный фильтр и в нем сортировка) - сортировка занимает около 2,5 минут (без учета выгрузки содержимого массива на временный лист (код примерно такой range(cells(1,1), cells(ubound(sourcearr), 2)) = sourcearr (цикл по строкам массива в данном случае не требуется)) и повторного считывания в массив).
В связи с чем вопрос: при каких размерах массивов есть смысл использовать тот или иной метод сортировки (безусловно, если есть какая то статистика)? Значения текстовые, массив двумерный.
Спасибо за труды!
nCount не нужен.
Вместо
ReDim tmpArr(UBound(SourceArr, 2)) As Variant
достаточно
dim vTmp as Variant
Если вам нужно отсортировать список, то к вашим услугам куча способов, самый простой из которых - кнопки сортировки на вкладке или в меню Данные (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 копируем нашу формулу:
=ИНДЕКС(List; ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(List; "<"&List); СТРОКА(1:1)); СЧЁТЕСЛИ(List; "<"&List); 0))
Или в англоязычной версии:
=INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0))
и нажимаем Ctrl+Shift+Enter, чтобы ввести ее как формулу массива. Потом полученную формулу можно скопировать вниз на всю длину списка.
Если нужно, чтобы формула учитывала не фиксированный диапазон, а могла подстраиваться при дописывании новых элементов к списку, то нужно будет слегка изменить стратегию.
Во-первых, диапазон List нужно будет задать динамически. Для этого при создании нужно указать не фиксированный диапазон C3:C10, а специальную формулу, которая будет ссылаться на все имеющиеся значения независимо от их количества. Нажмите Alt+F3 или откройте вкладку Формулы - Диспетчер имен (Formulas - Name Manager) , создайте новое имя и в поле Ссылка (Reference) впишите вот такую формулу (я предполагаю, что диапазон сортируемых данных начинается с ячейки C1):
= ЕСЛИОШИБКА( ИНДЕКС(List; ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(List; "<"&List); СТРОКА(1:1)); СЧЁТЕСЛИ(List; "<"&List); 0)) ;"")
=IFERROR(NDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0));"")
Массив функций Excel позволяет решать сложные задачи в автоматическом режиме одновременно. Те, которые выполнить посредством обычных функций невозможно.
Фактически это группа функций, которые одновременно обрабатывают группу данных и сразу выдают результат. Рассмотрим подробно работу с массивами функций в Excel.
Виды массивов функций Excel
Массив – данные, объединенные в группу. В данном случае группой является массив функций в Excel. Любую таблицу, которую мы составим и заполним в Excel, можно назвать массивом. Пример:
В зависимости от расположения элементов различают массивы:
- одномерные (данные находятся в ОДНОЙ строке или в ОДНОМ столбце);
- двумерные (НЕСКОЛЬКО строк и столбцов, матрица).
Одномерные массивы бывают:
- горизонтальными (данные – в строке);
- вертикальными (данные – в столбце).
Примечание. Двумерные массивы Excel могут занимать сразу несколько листов (это сотни и тысячи данных).
Формула массива – позволяет обработать данные из этого массива. Она может возвращать одно значение либо давать в результате массив (набор) значений.
С помощью формул массива реально:
- подсчитать количество знаков в определенном диапазоне;
- суммировать только те числа, которые соответствуют заданному условию;
- суммировать все n-ные значения в определенном диапазоне.
Когда мы используем формулы массива, Excel видит диапазон значений не как отдельные ячейки, а как единый блок данных.
Синтаксис формулы массива
Используем формулу массива с диапазоном ячеек и с отдельной ячейкой. В первом случае найдем промежуточные итоги для столбца «К оплате». Во втором – итоговую сумму коммунальных платежей.
Формула после нажатия Ctrl + Shift + Enter оказалась в фигурных скобках. Она подставилась автоматически в каждую ячейку выделенного диапазона.
Если попытаться изменить данные в какой-либо ячейке столбца «К оплате» - ничего не выйдет. Формула в массиве защищает значения диапазона от изменений. На экране появляется соответствующая запись:
Рассмотрим другие примеры использования функций массива Excel – рассчитаем итоговую сумму коммунальных платежей с помощью одной формулы.
- Выделяем ячейку Е9 (напротив «Итого»).
- Вводим формулу вида: =СУММ(C3:C8*D3:D8).
- Нажимаем сочетание клавиш: Ctrl + Shift + Enter. Результат:
Формула массива в данном случае заменила две простые формулы. Это сокращенный вариант, вместивший всю необходимую информацию для решения сложной задачи.
Аргументы для функции – одномерные массивы. Формула просматривает каждый из них по отдельности, совершает заданные пользователем операции и генерирует единый результат.
Рассмотрим ее синтаксис:
Функции работы с массивами Excel
Предположим, в следующем месяце планируется увеличение коммунальных платежей на 10%. Если мы введем обычную формулу для итога =СУММ((C3:C8*D3:D8)+10%), то вряд ли получим ожидаемый результат. Нам нужно, чтобы каждый аргумент увеличился на 10%. Чтобы программа поняла это, мы используем функцию как массив.
- Посмотрим, как работает оператор «И» в функции массива . Нам нужно узнать, сколько мы платим за воду, горячую и холодную. Функция: . Итого – 346 руб.
- Функция «Сортировки» в формуле массива. Отсортируем суммы к оплате в порядке возрастания. Для списка отсортированных данных создадим диапазон. Выделим его. В строке формул вводим . Жмем сочетание Ctrl + Shift + Enter.
- Транспонированная матрица. Специальная функция Excel для работы с двумерными массивами. Функция «ТРАНСП» возвращает сразу несколько значений. Преобразует горизонтальную матрицу в вертикальную и наоборот. Выделяем диапазон ячеек, где количество строк = числу столбцов в таблице с исходными данными. А количество столбцов = числу строк в исходном массиве. Вводим формулу: . Получается «перевернутый» массив данных.
- Поиск среднего значения без учета нулей. Если мы воспользуемся стандартной функцией «СРЗНАЧ», то получим в результате «0». И это будет правильно. Поэтому вставляем в формулу дополнительное условие: 0;A1:A8))' >. Получаем:
Распространенная ошибка при работе с массивами функций – НЕ нажатие кодового сочетания «Ctrl + Shift + Enter» (никогда не забывайте эту комбинацию клавиш). Это самое главное, что нужно запомнить при обработке больших объемов информации. Правильно введенная функция выполняет сложнейшие задачи.
Читайте также: