Перевернуть текст задом наперед в excel
Обратный порядок в 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» — для поиска последней использованной строки на листе.
Затем я использовал метод поиска последней использованной строки, чтобы найти последнее значение в строке.
Далее я нанял НА СЛЕДУЮЩИЙ цикл, чтобы перебрать ячейки, чтобы изменить порядок.
Поговорим о пользовательских функциях, то есть о функциях, которые может создавать сам пользователь, оперируя переменными Excel.
Перед нами стоит задача - отобразить текст в ячейке (произвольный) в обратном порядке. Пример: "Привет!", - в соседней ячейке появится: "!тевирП".
В таком случае можно использовать макрос, но количество действий, его (макроса) постоянный вызов не улучшат, а затруднят работу с таблицей.
Воспользуемся возможностью создания своих функций. Благо для этого нам подойдёт алгоритм создания макроса.
Добавляем новый модуль в наш документ:
- Вкладка "Разработчик", блок кнопок "Код", кнопка "Visual Basic";
- Далее "Insert" - > "Module".
Добавим следующий текст в наш модуль:
Function dhReverseText(strText As String) As String
Dim i As Integer
For i = Len(strText) To 1 Step -1
dhReverseText = dhReverseText & Mid(strText, i, 1)
Next i
End Function
Разберём где у нас что:
- "Function dhReverseText(strText As String) As String" - название функции и объявление её типом строка;
- " Dim i As Integer" - объявление переменной i;
- "For i = Len(strText) To 1 Step -1" - для каждой переменной i типа строка устанавливаем обратный порядок расположения символов (Len - возвращает количество символов в ячейке);
- "dhReverseText = dhReverseText & Mid(strText, i, 1)" - указываем функции, что двигаться нужно от конца к началу;
- " Next i " - обработка следующего символа.
Прелестью такого подхода является работа с функцией, мы можем вводить её как простую формулу.
Не самая распространенная, но тем не менее встречающаяса задача: перевернуть слово. Т.е. расположить буквы слова в обратном порядке. Из "привет" сделать "тевирп". Если честно, то сейчас затрудняюсь озвучить конкретную ситуацию, в которой это может пригодиться. Сам использовал когда-то в случае, когда надо было перевернуть числовые значения с еще некоторыми манипуляциями. Но ситуации бывают разные, как и решения задачи, которых несколько.
Сначала я хотел бы описать способы переворачивания слова средствами VBA, т.к. именно они кажутся мне наиболее рациональными и наиболее удобными для большинства, несмотря на необходимость использовать VBA.
Способ 1:
Через встроенную функцию VBA StrReverse(). Быстрый и короткий. Самый удобный. Но работает только начиная от Excel 2000 и выше.
Sub Reverse_Word() On Error Resume Next ActiveCell.Value = StrReverse(ActiveCell.Value) End Sub
Чтобы применить этот код необходимо слегка погрузиться в мир Visual Basic for Applications: переходим в редактор VBA( Alt+F11 ) -Insert -Module. Вставляем туда приведенный выше код. Выделяем ячейку с любым словом - Alt + F8 -Reverse_Word -Выполнить. Слово в активной ячейке будет перевернуто.
Способ 2:
Более медленный, содержит больше строк кода, но работает во всех версиях:
Sub Reverse_Word() Dim sWord As String, sReverseWord As String Dim li As Long sWord = ActiveCell.Value For li = Len(sWord) To 1 Step -1 sReverseWord = sReverseWord & Mid(sWord, li, 1) Next li ActiveCell.Value = sReverseWord End Sub
Код применяется точно так же, как и первый.
Оба эти кода можно сделать функциями пользователя, чтобы вызывать их с листа Excel как любую другую функцию:
' Функция работает с версиями Excel, начиная с 2000 Function Reverse_Word(sWord As String) Reverse_Word = StrReverse(sWord) End Function ' Функция работает со всеми версиями Excel Function Reverse_Word_All(sWord As String) Dim sReverseWord As String Dim li As Long For li = Len(sWord) To 1 Step -1 sReverseWord = sReverseWord & Mid(sWord, li, 1) Next li Reverse_Word_All = sReverseWord End Function
Чтобы правильно использовать приведенные выше коды, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: необходимо скопировать текст кода выше, перейти в редактор VBA( Alt+F11 ) -создать стандартный модуль(Insert -Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций, отыскав её в категории Определенные пользователем (User Defined Functions) .
Способ 3: стандартными функциями в любой версии Excel
Сразу хочу оговориться - стандартными формулами сделать это хоть и можно, но не совсем просто и совершенно неудобно. Для этого потребуется гораздо больше манипуляций, чем через VBA. Хотя для кого-то, возможно, способ формулами будет более прост, чем через VBA. Для начала необходимо будет включить итеративные вычисления в функциях:
- Excel 2003: Сервис -Параметры -Вычисления -ставим галочку Итерации
- Excel 2007: Меню -Параметры Excel -Формулы -Включить итеративные вычисления
- Excel 2010 и выше: Файл -Параметры -Формулы -Включить итеративные вычисления
Функция для Excel 2016 и выше
А для счастливых обладателей Excel 2016, 365 и выше можно использовать простую и гибкую формулу массива(вводится в ячейки такая формула сочетанием трех клавиш Ctrl + Shift + Enter ):
=ОБЪЕДИНИТЬ("";0;ЕСЛИОШИБКА(ПСТР( A1 ;ДЛСТР( A1 )-СТРОКА( A1:A999 )+1;1);""))
=TEXTJOIN("",0,IFERROR(MID(A1,LEN(A1)-ROW(A1:A50000)+1,1),""))
Если текст слишком длинный необходимо лишь расширить диапазон A1:A999 с чуть большим запасом, например: A1:A50000 . 50 000 букв точно хватит :)
Никаких макросов и итераций - все же Microsoft думает о нас, о пользователях и внедряет всякие новые плюшки.
В этой статье объясняется формула, сочетающая функции TEXTJOIN и MID для изменения порядка текстовых строк в ячейке в Excel.
Как перевернуть текстовую строку в ячейке в Excel?
Общая формула
аргументы
Текстовая_строка: Текстовая строка, порядок которой нужно изменить. Это может быть:
- 1. Текст, заключенный в кавычки;
- 2. Или ссылку на ячейку, содержащую текст.
Как пользоваться этой формулой?
1. Выберите пустую ячейку, чтобы вывести обратный результат.
2. Введите в нее приведенную ниже формулу и нажмите Enter и перетащите маркер заполнения, чтобы применить формулу к другим ячейкам.
Советы: Вы можете заменить в формуле число 1 на ИСТИНА. Это означает, что пустые элементы в массиве будут проигнорированы.
Как работает эта формула?
- 1. MID(B5,,1) : Функция MID извлекает каждый символ текстовой строки в B5 в обратном порядке. Здесь результат: ;
- 2. TEXTJOIN("",1,) : Функция TEXTJOIN объединяет каждый символ в массиве и игнорирует пустые места. Конечным результатом будет «lecxE».
Примечание: Приведенная выше формула работает правильно, если длина текстовой строки равна или меньше 10 символов. Чтобы перевернуть текстовую строку, длина которой превышает 10 символов, примените следующую формулу массива:
=TEXTJOIN("",1,MID(A1,ABS(ROW(INDIRECT("1:"&LEN(A1)))-(LEN(A1)+1)),1))
Пожалуйста, не забудьте нажать Ctrl + Shift + Enter ключи вместе, чтобы получить правильный результат.
Связанные функции
Функция TEXTJOIN в Excel
Функция Excel TEXTJOIN объединяет несколько значений из строки, столбца или диапазона ячеек с определенным разделителем.
Функция Excel MID
Функция MID используется для поиска и возврата определенного количества символов из середины данной текстовой строки.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
Как быстро перевернуть данные в Excel?
В некоторых особых случаях при работе с Excel мы можем захотеть перевернуть наши данные вверх ногами, что означает изменить порядок данных столбца, как показано ниже. Если вы вводите их один за другим в новом порядке, это займет много времени. Здесь я расскажу о нескольких быстрых способах перевернуть их вверх дном за короткое время.
Переверните данные вверх ногами с помощью Kutools for Excel
Переверните данные вверх ногами с помощью столбца справки и выполните сортировку
Вы можете создать столбец справки помимо своих данных, а затем отсортировать столбец справки, чтобы помочь вам изменить данные.
1. Щелкните ячейку рядом с вашими первыми данными, введите в нее 1 и перейдите к следующему типу ячейки 2. См. Снимок экрана:
2. Затем выберите числовые ячейки и перетащите дескриптор автозаполнения вниз, пока количество ячеек не станет равным количеству ячеек данных. Смотрите скриншот:
3. Затем нажмите Данные > Сортировать от большего к меньшему. Смотрите скриншот:
4. в Предупреждение о сортировке диалог, проверьте Расширить выбори нажмите Сортировать. Смотрите скриншот:
Теперь столбец справки и столбец данных поменяны местами.
Совет: вы можете удалить столбец справки, если он вам больше не нужен.
Переверните данные с помощью формулы
Если вы знакомы с формулой, здесь я могу рассказать вам формулу для изменения порядка данных в столбце.
1. Выберите пустую ячейку и введите эту формулу. = ИНДЕКС ($ A $ 1: $ A $ 8; ROWS (A1: $ A $ 8)) в это нажмите Enter нажмите клавишу, затем перетащите маркер автозаполнения, чтобы заполнить эту формулу, пока не появятся повторяющиеся данные заказа. Смотрите скриншот:
Теперь данные перевернуты, и вы можете удалить повторяющиеся данные.
Переверните данные вверх ногами с помощью Kutools for Excel
С помощью вышеуказанных методов вы можете только изменить порядок значений. Если вы хотите отменить значения с их форматами ячеек, вы можете использовать Kutools for ExcelАвтора Отразить вертикальный диапазон утилита. С помощью утилиты «Отразить вертикальный диапазон» можно выбрать только значения зеркального отображения или значения зеркального отображения и формат ячейки вместе.
После бесплатная установка Kutools for Excel, вам просто нужно выбрать данные и нажать Кутулс > Диапазон > Отразить вертикальный диапазон, а затем укажите в подменю один тип переворачивания по своему усмотрению.
Если вы укажете Отразить все:
Если вы укажете Только перевернуть значения:
Работы С Нами Kutools for Excel, вы можете изменить порядок строки без форматирования или сохранения формата, вы можете поменять местами два непрерывных диапазона и так далее.
Читайте также: