Переворот списка по вертикали с помощью VBA Excel. Копирование несортированного списка на другой столбец в обратном порядке с помощью цикла и без него.
Постановка задачи
Есть список каких-либо данных, расположенный в любом столбце листа, кроме последнего. Необходимо скопировать исходный список в соседний столбец справа, расположив данные в обратном порядке, то есть перевернув список по вертикали. Начать конечный список с той же строки, с которой начинается исходный.
Код предназначен для неотсортированного списка, так как отсортированный список легко перевернуть обратной сортировкой. Но при тестировании очень удобно использовать, для наглядности, вертикальный отсортированный числовой ряд.
Переворот списка с помощью цикла
Данные для копирования на другой столбец в обратном порядке выделяем на листе вручную и используем в коде VBA Excel выбранный диапазон как объект Selection.
Базовый пример
Переворот списка с помощью цикла и прямого чтения данных из ячеек.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Sub ReverseWithLoopDirect() Dim rng As Range Dim colSource As Long Dim firstRow As Long Dim rowCount As Long Dim i As Long Set rng = Selection ' Исходные параметры colSource = rng.Column ' номер исходного столбца (например, 3 для столбца C) firstRow = rng.Row ' первая строка диапазона rowCount = rng.Rows.Count ' количество строк в диапазоне ' Переворачиваем значения прямым чтением из ячеек For i = 1 To rowCount Cells(firstRow + i - 1, colSource + 1).Value = _ Cells(firstRow + rowCount - i, colSource).Value Next i End Sub |
Результат работы кода:
Если список, который надо записать в обратном порядке, очень большой, для ускорения работы кода можно использовать массивы.
Пример с защитой от ошибок
Этот код VBA Excel также обрабатывает диапазон, выделенный в любом столбце, циклом For... Next с прямым чтением ячеек.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
Sub ReverseSelectedWithLoop() Dim rng As Range Dim colSource As Long Dim firstRow As Long Dim rowCount As Long Dim i As Long ' Проверяем, что активен рабочий лист (а не лист диаграммы) и выделен диапазон If TypeOf ActiveSheet Is Worksheet And TypeName(Selection) = "Range" Then Set rng = Selection Else MsgBox "Ошибка: активен не рабочий лист или выделен не диапазон!" Exit Sub End If ' Если выделено несколько областей, работаем только с первой If rng.Areas.Count > 1 Then Set rng = rng.Areas(1) MsgBox "Выделено несколько областей. Будет обработана первая: " & rng.Address End If ' Проверяем, что выделен один столбец If rng.Columns.Count > 1 Then MsgBox "Выделите один столбец." Exit Sub End If ' Исходные параметры colSource = rng.Column ' номер исходного столбца (например, 3 для столбца C) firstRow = rng.Row ' первая строка диапазона rowCount = rng.Rows.Count ' количество строк в диапазоне ' Переворачиваем значения прямым чтением из ячеек For i = 1 To rowCount Cells(firstRow + i - 1, colSource + 1).Value = _ Cells(firstRow + rowCount - i, colSource).Value Next i End Sub |
Результат работы кода:
Переворот списка без цикла
Создание массива значений в обратном порядке с помощью метода Evaluate и конструкции N(IF(1,...)).
Код обращения списка без цикла
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
Sub ReverseListWithoutLoop() Dim rng As Range Dim revArray As Variant Dim addr As String Dim rowCount As Long Dim firstRow As Long Dim sourceCol As Long ' Проверяем, что активен рабочий лист (а не лист диаграммы) и выделен диапазон If TypeOf ActiveSheet Is Worksheet And TypeName(Selection) = "Range" Then Set rng = Selection Else MsgBox "Ошибка: активен не рабочий лист или выделен не диапазон!" Exit Sub End If ' Если выделено несколько областей, работаем с первой If rng.Areas.Count > 1 Then Set rng = rng.Areas(1) MsgBox "Выделено несколько областей. Будет обработана первая: " & rng.Address End If ' Проверяем, что выделен один столбец If rng.Columns.Count > 1 Then MsgBox "Выделите один столбец." Exit Sub End If ' Определяем параметры диапазона rowCount = rng.Rows.Count firstRow = rng.Row sourceCol = rng.Column addr = rng.Address ' Формируем массив значений в обратном порядке с помощью Evaluate и N(IF(1,...)) revArray = Evaluate("INDEX(" & addr & ", N(IF(1, " & rowCount + 1 & "-ROW(1:" & rowCount & "))))") ' Вставляем перевёрнутый массив в столбец справа, начиная с той же строки Range(Cells(firstRow, sourceCol + 1), Cells(firstRow + rowCount - 1, sourceCol + 1)).Value = revArray End Sub |
Коротко о том, как это работает:
N(IF(1, массив_индексов))— это известный приём в VBA Excel, который заставляет функциюINDEXвозвращать массив значений, а не одно значение.revArrayстановится двумерным массивом размером(rowCount, 1)с перевёрнутыми значениями.- Затем массив с перевёрнутыми значениями присваивается диапазону справа от исходного списка одной операцией.
Объяснение переворота списка
Подробное объяснение, как получить массив значений из диапазона ячеек в обратном порядке с помощью метода Evaluate, функций INDEX, N и IF без использования циклов.
Давайте разберем переворота списка без цикла по шагам, от сборки строки на уровне VBA до вычисления ее в движке Excel.
Шаг 1. Сборка строки на уровне VBA
Предположим, у нас есть следующие переменные:
|
1 2 |
addr = "$A$1:$A$5" (адрес диапазона) rowCount = 5 (количество строк в диапазоне) |
VBA собирает строку формулы, подставляя значения переменных:
|
1 |
"INDEX(" & addr & ", N(IF(1, " & rowCount + 1 & "-ROW(1:" & rowCount & "))))" |
В итоге в метод Evaluate передается следующая текстовая строка:
|
1 |
INDEX($A$1:$A$5, N(IF(1, 6-ROW(1:5)))) |
Шаг 2. Вычисление формулы внутри Excel
Метод Evaluate передает эту строку вычислительному движку Excel. Движок обрабатывает её как обычную формулу массива. Разберем формулу изнутри наружу:
А) ROW(1:5)
Функция ROW генерирует массив номеров строк от 1 до 5.
Результат: {1; 2; 3; 4; 5} (вертикальный массив).
Б) 6 - ROW(1:5)
Производится арифметическая операция над массивом:
|
1 2 3 4 5 |
6 - 1 = 5 6 - 2 = 4 6 - 3 = 3 6 - 4 = 2 6 - 5 = 1 |
Результат: {5; 4; 3; 2; 1}.
Смысл операции над массивом: мы получили индексы строк в обратном порядке.
В) IF(1, {5; 4; 3; 2; 1})
В обычных формулах (в старых версиях Excel) функция INDEX не принимает массив чисел как аргумент номера строки, если это не формула массива (Ctrl+Shift+Enter). Она могла бы вернуть только первое значение.
Конструкция IF(1, ...) заставляет движок Excel обработать содержимое как массив констант, а не как ссылку. Она как бы говорит: «Возьми этот массив чисел и передай его дальше целиком».
Результат: {5; 4; 3; 2; 1} (массив сохраняется).
Г) N(...)
Функция N преобразует результат в числа.
В данном контексте это «страховка». IF может вернуть логический тип или вариант, а INDEX ожидает только числа. N гарантирует, что в INDEX попадет чистый массив чисел {5; 4; 3; 2; 1}.
Д) INDEX($A$1:$A$5, {5; 4; 3; 2; 1})
Функция INDEX берет диапазон $A$1:$A$5 и извлекает значения по номерам строк из массива индексов.
|
1 2 3 4 5 |
Строка 5 диапазона (ячейка A5) Строка 4 диапазона (ячейка A4) Строка 3 диапазона (ячейка A3) Строка 2 диапазона (ячейка A2) Строка 1 диапазона (ячейка A1) |
Итоговый результат вычисления: вертикальный массив значений {ЗначениеA5; ЗначениеA4; ...; ЗначениеA1}.
Шаг 3. Возврат результата в VBA
Метод Evaluate возвращает результат вычисления формулы в переменную VBA revArray.
Так как формула вернула массив, revArray становится переменной массива, заполненной данными исходного диапазона в обратном порядке без чтения ячеек с помощью цикла.
Конструкция N(IF(1,...)) обеспечивает работу этого приёма в разных версиях Excel от 2010 года, где динамические массивы еще не работали так, как в Office 365. В VBA Excel 365 можно было бы написать проще (без IF и N), но опубликованный выше вариант более универсален.