В эксель в обратном порядке
Обратный порядок в excel — это не что иное, как переворачивание данных, где нижнее значение идет вверх, а верхнее значение идет вниз.
Например, посмотрите на изображение ниже.
Как мы видим, нижнее значение находится наверху в обратном порядке, и то же самое относится и к верхнему значению. Итак, как нам изменить порядок данных в Excel — вот вопрос.
Как изменить порядок строк данных в Excel?
В Excel мы можем выполнять сортировку, используя несколько методов; здесь мы покажем вам все возможные способы изменить порядок в Excel.
Метод №1 — Простой метод сортировки
Вы, должно быть, уже задаетесь вопросом, можно ли обратить данные, просто используя опцию сортировки. Мы не можем отменить, просто отсортировав данные, но с помощью некоторого вспомогательного столбца мы действительно можем это сделать.
Шаг 1 — Рассмотрим нижеприведенные данные для этого примера.
Шаг 2 — Рядом с этим создайте столбец под названием «ПОМОЩЬ» и вставьте серийные номера.
Шаг 3 — Теперь выберите все данные и откройте опцию сортировки, нажав ALT + D + S.
Шаг 4 — В разделе «Сортировать по» выберите «Помощник».
Шаг 5 — Затем в разделе «Порядок» выберите «От наибольшего к наименьшему».
Шаг 6 — Теперь нажмите ОК, наши данные будут перевернуты.
Метод № 2 — Использование формулы Excel
Мы также можем изменить порядок, используя формулы. Несмотря на то, что у нас нет встроенной функции для этого, мы можем использовать другие формулы для изменения порядка.
Чтобы сделать данные в обратном порядке, мы будем использовать две формулы: INDEX и ROWS. Функция ИНДЕКС может получить результат из указанного номера строки в выбранном диапазоне, а функция СТРОКИ в Excel выдаст количество выбранных строк.
Шаг 1 — Рассмотрим нижеприведенные данные для этого примера.
Шаг 2 — Сначала откройте функцию ИНДЕКС.
Шаг 3 — За Массив, выберите названия городов из A2: A9 и сделайте их абсолютной ссылкой, нажав клавишу F4.
Шаг 4 — Далее, чтобы вставить Row Num открытый РЯДЫ внутри функции ИНДЕКС.
Шаг 5 — Для функции СТРОКИ выберите тот же диапазон ячеек, который мы выбрали для функции ИНДЕКС, но на этот раз в качестве абсолютной ссылки будет использоваться только последняя ячейка.
Шаг 6 — Закройте скобку и нажмите клавишу ввода, чтобы получить результат.
Шаг 7 — Перетащите формулу, чтобы получить полный результат.
Метод № 3 — Обратный порядок с использованием кодирования VBA
Изменение порядка данных в Excel также возможно с помощью кодирования VBA. Если вы хорошо знакомы с VBA, то код ниже для вас.
Код:
Скопируйте этот код в свой модуль.
Теперь запустите код, чтобы получить список в обратном порядке на листе Excel.
Позвольте мне объяснить вам, как этот код работает для вас. Сначала я объявил две переменные, «K» и «LR» как тип данных LONG.
«K» используется для перебора ячеек, а «LR» — для поиска последней использованной строки на листе.
Затем я использовал метод поиска последней использованной строки, чтобы найти последнее значение в строке.
Далее я нанял НА СЛЕДУЮЩИЙ цикл, чтобы перебрать ячейки, чтобы изменить порядок.
Банальная, на первый взгляд, задача, периодически встречающаяся в работе почти любого пользователя 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) , то список развернется в обратном порядке прямо в тех же ячейках, т.е. на месте.
Хотим, упрощенно говоря, повернуть таблицу на бок, т.е. то, что располагалось в строке - пустить по столбцу и наоборот:
Способ 1. Специальная вставка
Выделяем и копируем исходную таблицу (правой кнопкой мыши - Копировать). Затем щелкаем правой по пустой ячейке, куда хотим поместить повернутую таблицу и выбираем из контекстного меню команду Специальная вставка (Paste Special) . В открывшемся диалоговом окне ставим галочку Транспонировать (Transpose) и жмем ОК.
Минусы : не всегда корректно копируются ячейки с формулами, нет связи между таблицами (изменение данных в первой таблице не повлияет на вторую).
Плюсы : в транспонированной таблице сохраняется исходное форматирование ячеек.
Способ 2. Функция ТРАНСП
Выделяем нужное количество пустых ячеек (т.е. если, например, исходная таблица была из 3 строк и 5 столбцов, то выделить обязательно нужно диапазон из 5 строк и 3 столбцов) и вводим в первую ячейку функцию ТРАНСП (TRANSPOSE) из категории Ссылки и массивы (Lookup and Reference):
После ввода функции необходимо нажать не Enter, а Ctrl+Shift+Enter, чтобы ввести ее сразу во все выделенные ячейки как формулу массива . Если раньше не сталкивались с формулами массивов, то советую почитать тут - это весьма экзотический, но очень мощный инструмент в Excel.
Плюсы : между таблицами сохраняется связь, т.е. изменения в первой таблице тут же отражаются во второй.
Минусы : не сохраняется форматирование, пустые ячейки из первой таблицы отображаются в виде нулей во второй, нельзя редактировать отдельные ячейки во второй таблице, поскольку формулу массива можно менять только целиком.
Способ 3. Формируем адрес сами
Этот способ отчасти похож не предыдущий, но позволяет свободно редактировать значения во второй таблице и вносить в нее любые правки при необходимости. Для создания ссылок на строки и столбцы нам понадобятся четыре функции из категории Ссылки и массивы:
- Функция АДРЕС(номер_строки; номер_столбца) - выдает адрес ячейки по номеру строки и столбца на листе, т.е. АДРЕС(2;3) выдаст, например, ссылку на ячейку C2.
- Функция ДВССЫЛ(ссылка_в_виде_текста) - преобразует текстовую строку, например, "F3" в настоящую ссылку на ячейку F3.
- Функции СТРОКА(ячейка) и СТОЛБЕЦ(ячейка) - выдают номер строки и столбца для заданной ячейки, например =СТРОКА(F1) выдаст 1, а =СТОЛБЕЦ(А3) выдаст 3.
Теперь соединяем эти функции, чтобы получить нужную нам ссылку, т.е. вводим в любую свободную ячейку вот такую формулу:
=ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1)))
в английской версии Excel это будет =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))
А затем копируем (протягиваем) формулу на соседние ячейки как обычно черным крестом. В итоге должно получиться примерно следующее:
Т.е. при копировании формулы вниз по столбцу, она выдает ссылку, которая уходит вправо по строке и наоборот. Чего и требовалось.
Плюсы : сохраняются связи между таблицами, можно легко вносить изменения во вторую таблицу.
Минусы : форматирование не сохраняется, но его можно легко воспроизвести Специальной вставкой (вставить только Формат с флажком Транспонировать
Скопируйте и вставьте список столбцов или строк в обратном порядке с формулой
Скопируйте и вставьте список столбцов в обратном порядке по вертикали
Если вы хотите перевернуть список ячеек столбца, вы можете применить следующую формулу:
Введите или скопируйте следующую формулу в пустую ячейку, в которой вы хотите изменить порядок столбцов:
Внимание: В приведенной выше формуле 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.
Заполните серию в обратном или убывающем порядке с помощью маркера заполнения
Функция Fill Handle может помочь вам только для серий заполнения чисел в обратном или убывающем порядке, например серий 100, 99, 98 .
1. Введите первое число серии в ячейку (здесь я ввожу число 100 в ячейку A1).
2. Выберите ячейку A1, поместите курсор в правый нижний угол ячейки, чтобы отобразить крестик, нажмите и удерживайте правую кнопку мыши, а затем перетащите маркер заполнения вниз к столбцу, пока он не достигнет нужной ячейки. Отпустите кнопку мыши и щелкните Серии из всплывающего меню. Смотрите скриншот:
3. в Серии диалоговом окне введите убывающее число в Значение шага box (здесь мы вводим -1 в поле). А затем щелкните OK кнопка. Смотрите скриншот:
Теперь указанный столбец заполняется номерами серий по убыванию.
Заметки:
1. Кроме описанных выше шагов, вы также можете ввести первые два числа в порядке убывания, например 100, 99 в ячейках A1 и A2, выбрать данные и перетащить маркер заполнения вниз в нужные ячейки. .
2. Этот метод работает только с числами. Если вы хотите создать номера серий в обратном порядке с текстом, попробуйте следующий метод.
Заполните ряд в обратном или убывающем порядке с помощью Kutools for Excel
В этом разделе мы представим Вставить порядковый номер полезности Kutools for Excel. С помощью этой утилиты вы можете легко создать убывающий ряд и заполнить указанный диапазон по своему усмотрению.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Нажмите Кутулс > Вставить > Вставить порядковый номер. Смотрите скриншот:
2. в Вставить порядковый номер диалогового окна, нажмите Новое кнопку, чтобы развернуть диалоговое окно, а затем выполните следующую настройку.
3. Теперь серия создана. Для заполнения этой серии выберите диапазон и щелкните созданную серию в Вставить порядковый номер диалоговое окно, затем щелкните Диапазон заполнения кнопка. Затем номер серии (с текстом) сразу заполняется в выбранном диапазоне.
4. После заполнения серии закройте диалоговое окно.
Внимание: Все числа в этой убывающей серии уникальны. В следующий раз, когда вы повторно используете эту серию в диалоговом окне «Вставить порядковый номер», начальный номер будет уменьшаться на единицу по сравнению с последним номером предыдущей вставленной серии.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Читайте также: