Обратный порядок элементов в списке

Банальная, на первый взгляд, задача, периодически встречающаяся в работе почти любого пользователя Microsoft Excel – расположить элементы списка в обратном порядке. При всей кажущейся простоте, здесь есть свои "фишки" - давайте разберем несколько вариантов ее решения.

Способ 1. Ручная сортировка по доп.столбцу

Это обычно первое, что приходит в голову. Добавляем рядом с нашим списком еще один столбец с порядковыми номерами и сортируем по этому столбцу по убыванию:

 reverse1.png    reverse2.png

Очевидный плюс такого подхода в простоте. Очевидный же минус в том, что нужно руками проделать энное количество операций. Если это разовая задача - ОК, но если данные меняются каждый день, то сортировать список постоянно вручную уже напрягает. Выходом может стать использование формул.

Способ 2. Обратный порядок формулой

Поскольку формулы в Excel пересчитываются автоматически (если включен ручной режим пересчета), то и сортировка, реализованная формулами, будет происходить "на лету", без какого либо участия пользователя.

Нужная нам формула, размещающая элементы списка в обратном порядке может выглядеть так:

reverse3.png

Недостаток этой формулы в том, что в ней должны жестко задаваться начало и конец списка (ячейки A2 и A9 в нашем случае). Если заранее точно не известно, сколько именно элементов будет в списке, то лучше использовать другой подход:

reverse4.png

В этой формуле номер последней занятой ячейки подсчитывается с помощью функции СЧЁТЗ (COUNTA), т.е. количество элементов в исходном списке может впоследствии меняться.

Минус этого варианта - в исходном списке не должно быть пустых ячеек, т.к. функция СЧЁТЗ тогда неправильно вычислит номер строки последнего элемента. Выходом может стать использование динамического именованного диапазона с автоподстройкой размеров либо хитрой формулы массива:

reverse5.png

Как легко заметить, это вариация первого способа, где диапазон взят «с запасом» сразу до сотой строки и номер строки последней заполненной ячейки задается не жестко, а вычисляется с помощью фрагмента МАКС(($A$2:$A$100<>"")*СТРОКА($A$2:$A$100))

Каждая ячейка в диапазоне A2:A100 проверяется на заполненность с помощью выражения ($A$2:$A$100<>""), что даст на выходе массив значений ИСТИНА и ЛОЖЬ. Затем этот массив поэлементно умножается на массив номеров строк, получаемый с помощью функции СТРОКА($A$2:$A$100). Поскольку логическую ИСТИНУ Excel интерпретирует как 1, а ЛОЖЬ – как 0, то после умножения мы получим массив номеров заполненных ячеек. А уже из него функция МАКС (MAX) выбирает самое большое число, т.е. номер последней заполненной строки.

И, само-собой, не забудьте после ввода этой формулы нажать не обычный Enter, а сочетание Ctrl+Shift+Enter, чтобы ввести ее как формулу массива.

Способ 3. Макрос

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

Нажмите сочетание Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer). Вставьте новый пустой модуль через меню Insert - Module и скопируйте туда текст макроса:

Sub Reverse()
    Dim arrData(), n As Long

    arrData = Selection
    For Each cell In Selection
        cell.Value = arrData(UBound(arrData) - n, 1)
        n = n + 1
    Next cell
End Sub

Теперь, если выделить столбец-список с данными и запустить наш макрос с помощью сочетания Alt+F8 или команды Разработчик - Макросы (Developer - Macros), то список развернется в обратном порядке прямо в тех же ячейках, т.е. на месте.

Ссылки по теме



24.05.2016 04:42:47
Как вариант , вроде можно и так сделать, без формул массива и именованного диапазона:
=ИНДЕКС(A:A;СЧЁТЗ(A:A)-СТРОКА(A1)+ПОИСКПОЗ(ПОВТОР("я";255);A:A)-(СЧИТАТЬПУСТОТЫ(B:B)-СЧИТАТЬПУСТОТЫ(A:A))+1)
21.10.2016 12:04:33
А есть какой-то способ перемножить две строки в обратном порядке?
19.09.2017 08:28:53
Еще вариант обратной сортировки формулой:

=СМЕЩ($A$9;СТРОКА($A$2)-СТРОКА();)