Как вставить значения в обратном порядке excel

Как отсортировать данные в обратном порядке Excel

Бывают ситуации, когда вам нужно «перевернуть» вашу табличку. То есть сделать так, чтобы данные, которые находятся вверху — были внизу.

И да, это удивительно, но в программе нет специальной функции для этого.

Конечно же, мы можем сделать это сразу несколькими методами. Но именно своей функции для такой задачи в Excel нет.

С помощью функции «Сортировка»

Это, наверное, самый быстрый и удобный метод.

По вертикали

Допустим, нам нужно отсортировать данные в обратном порядке, для следующей таблички:

• Создайте столбик для расчетов (мы назовем его «Helper»);

• Выделите табличку и щелкните на «Данные»;

• Далее — «Сортировка»;

• В первом параметре сортировки выберите наш столбик для расчетов;

• А в третьем — «По убыванию»;

• Подтвердите.

Готово! Вот результат:

После сортировки можно удалить столбик для расчетов.

Мы рассмотрели табличку, в которой есть только один столбик с данными(не считая имен). Но его можно использовать независимо от того, сколько у вас столбиков с данными.

По горизонтали

Практически тоже самое с табличками по горизонтали.

Итак, давайте рассмотрим такой пример.

Допустим, у нас есть такая табличка:

• Создаем столбик для расчетов, только теперь это не столбик, а строка. Так как табличка у нас горизонтальная;

• Открываем «Сортировка»;
• Жмем «Параметры…»;

• И выберите «Столбцы диапазона»;

• Подтвердите;

• В первом параметре выберите ту строку, которая является строкой для расчетов.

• В третьем параметре — «По убыванию»;

• Подтвердите.

Готово! Вот результат:

После сортировки можете смело удалять строку для расчетов.

C помощью СОРТПО и ИНДЕКС

Функция ИНДЕКС доступна для всех, а вот функция СОРТПО доступна только по платной подписке Office 365.

С помощью СОРТПО

Допустим, у нас есть такая табличка:

Создадим еще два столбика, с теме же заголовками. Там будет наша обработанная табличка.

Формула функции примет такой вид:

Сортировка происходит по данным, полученным из функции СТРОКА.

Она создает массив с порядковыми номерами строк.

А последний аргумент в функции (“-1”) говорит Excel о том, что нам нужен порядок «По убыванию».

Вот, собственно, и все!

С помощью ИНДЕКС

Мало кто пользуется Microsoft 365 и платит за подписку, но не переживайте, если вы из таких людей, то для вас есть выход!

Допустим, у нас та же табличка:

Формула примет такой вид:

Что мы сделали?

Функция СТРОКА, в результате выполнения, отдает нам кол-во строчек в диапазоне ячеек.

И этот результат будет становиться меньше и меньше.

А ИНДЕКС, по номеру строки, отдает нам значение этой строки. Таким образом мы получаем порядок «По убыванию».

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

Допустим, у вас есть такая табличка:

Формула примет такой вид:

Все также, только мы дополнительно указали столбик, откуда функция ИНДЕКС должна брать данные и помещать в новые ячейки.

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

С помощью Visual Basic

И, как обычно, рассмотрим метод с Visual Basic.

Это решение отлично подойдет для тех, кто использует такую сортировку очень часто.

Вот нужный нам код Visual Basic:

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

Куда нужно поместить код?

• ALT + F11;

• Правой кнопкой мышки на любой лист -> «Insert» -> «Module»;

• В открывшееся окошко вставьте наш код и закройте Visual Basic;

А дальше, чтобы использовать функцию — выделите табличку и нажмите «Run Macro» (или F5).

В этой статье, я рассказал вам о том, как можно отсортировать данные в обратном порядке. Мы рассмотрели как сделать это с помощью встроенной функции “Сортировка”, с помощью СОРТПО и ИНДЕКС, а также Visual Basic.

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

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

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

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

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

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

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

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

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

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

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

Как легко заметить, это вариация первого способа, где диапазон взят «с запасом» сразу до сотой строки и номер строки последней заполненной ячейки задается не жестко, а вычисляется с помощью фрагмента МАКС(($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 и скопируйте туда текст макроса:

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

Как вставить значения в обратном порядке excel

Иногда вам может потребоваться скопировать и вставить список столбцов или строк в обратном порядке по вертикали или горизонтали, как показано ниже. Как можно быстро и легко справиться с этой работой в Excel?

Скопируйте и вставьте список столбцов или строк в обратном порядке с формулой

Скопируйте и вставьте список столбцов в обратном порядке по вертикали

Если вы хотите перевернуть список ячеек столбца, вы можете применить следующую формулу:

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

Внимание: В приведенной выше формуле A1 — первая ячейка в столбце, а A15 это последняя ячейка в столбце.

Затем перетащите маркер заполнения вниз к ячейкам, чтобы извлечь значения ячеек в обратном порядке, как показано ниже:

Скопируйте и вставьте список строк в обратном порядке по горизонтали

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

Введите или скопируйте эту формулу в пустую ячейку:

Внимание: В приведенной выше формуле A1 — первая ячейка в строке, а 1:1 — это номер строки, в которой расположены ваши данные. Если ваши данные в строке 10, вы должны изменить их на 10:10.

Затем перетащите маркер заполнения прямо к ячейкам, которые вы хотите применить к этой формуле, пока все значения не будут извлечены, и вы получите все значения, перевернутые по горизонтали, см. Снимок экрана:

Скопируйте и вставьте диапазон столбцов или строк в обратном порядке с кодом VBA

Если вам нужно скопировать и вставить ряд столбцов или строк в обратном порядке, здесь я представлю некоторые коды VBA для быстрого и простого решения этой проблемы. Пожалуйста, выполните следующие действия:

Скопируйте и вставьте список столбцов в обратном порядке по вертикали

1. Сначала вы должны скопировать и вставить свои данные в новое место, а затем удерживать ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.

Код VBA: скопируйте и вставьте диапазон ячеек в обратном порядке по вертикали

3, Затем нажмите F5 нажмите клавишу для запуска этого кода, и появится диалоговое окно, напоминающее о выборе диапазона данных, который вы хотите перевернуть по вертикали, см. снимок экрана:

4. А затем нажмите OK и диапазон данных был перевернут по вертикали, как показано на скриншотах ниже:

Скопируйте и вставьте диапазон ячеек в обратном порядке по горизонтали

Чтобы перевернуть диапазон данных в горизонтальном порядке, примените приведенный ниже код VBA:

Код VBA: скопируйте и вставьте диапазон ячеек в обратном порядке по горизонтали

После запуска этого кода вы получите следующие скриншоты по мере необходимости:

Скопируйте и вставьте диапазон столбцов или строк в обратном порядке с помощью замечательной функции

Если вы не знакомы с приведенными выше формулами и кодами VBA, здесь я порекомендую простой инструмент —Kutools for Excel, С его Отразить вертикальный диапазон и Отразить горизонтальный диапазон функции, вы можете изменить диапазон ячеек по вертикали и горизонтали одним щелчком мыши.

Советы: Чтобы применить это Отразить вертикальный диапазон и Отразить горизонтальный диапазон функции, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.

После установки Kutools for Excel, пожалуйста, сделайте так:

Скопируйте и вставьте диапазон ячеек в обратном порядке по горизонтали

1. Выделите диапазон ячеек и нажмите Кутулс > Диапазон > Отразить вертикальный диапазон > Все (только значения переворачивания), см. снимок экрана:

2. А потом сразу перевернули диапазон значений ячеек по вертикали, см. Скриншоты:

Скопируйте и вставьте диапазон ячеек в обратном порядке по горизонтали

1. Выделите диапазон ячеек и нажмите Кутулс > Диапазон > Отразить горизонтальный диапазон > Все (только значения переворачивания), см. снимок экрана:

2. И затем все значения ячеек в выделении были немедленно перевернуты по горизонтали, см. Скриншоты:

Обратный список в Excel

Эта статья научит вас создавать обратный список в Excel. Для примера расположим в обратном порядке значения в столбце А (см. рисунок ниже):

1. Введите значение 1 в ячейку B1 и значение 2 в ячейку B2.
2. Выделите диапазон B1:B2, зажмите его нижний правый угол и протяните вниз до ячейки В8.
3. Кликните по любому числу из списка в столбце B.
4. На вкладке Данные (Data) нажмите кнопку Я-А (Z-A).

Результат: Мы получили обратный список.

Примечание: Обратился не только список в столбце В, но и список в столбце А.