Функция обратной цепочки в Excel

Обновлено: 07.07.2024

Можно ли запустить реверс при определенной длине символа (для определяемой пользователем функции или кода VBA)? Например, переверните значения ячейки A1, начиная с количества символов, равного значению B1. Таким образом, если B1 составляет 10 символов, реверс начнется с символов 11+ в A1..

Я обнаружил, что оригинальный Sub выдавал результаты с дополнительным знаком в конце всех выбранных ячеек. Поэтому я исправил эту часть.
For i = UBound(strList) To 0 Step -1
Если i > 0 Then xOut = xOut & strList(i) & Sigh Else xOut = xOut & strList(i)
Next < br />Rng.Value = xOut

Можно перевернуть текст с помощью формулы, она повторяется и ограничена тем, насколько далеко вы готовы зайти, и зависит от количества символов, которое Excel позволяет использовать в строке формул.

Максимальная длина текста – 30 символов, он находится в ячейке A1. Ячейка B1 будет выглядеть так:

=ОТРЕЗАТЬ(СРЕДНЯЯ(СЛЕВА(A1&ПОВТОР(" ",31),31),30,1)&СРЕДН(СЛЕВА(A1&ПОВТОР(" ",31),31),29,1)&СРЕДН(СЛЕВА(A1&ПОВТОР( " ",31),31),28,1)&СРЕДНИЙ(ЛЕВЫЙ(A1&ПОВТОР(" ",31),31),27,1)&СРЕДНИЙ(ЛЕВЫЙ(A1&ПОВТОР(" ",31),31),26,1 )&СРЕДНИЙ(ЛЕВЫЙ(A1&ПОВТОР(" ",31),31),25,1)&СРЕДНИЙ(ЛЕВЫЙ(A1&ПОВТОР(" ",31),31),24,1)&СРЕДНИЙ(ЛЕВЫЙ(A1&ПОВТОР(" ",31) ,31),23,1)&СРЕДН(ЛЕВО(A1&ПОВТОР(" ",31),31),22,1)&СРЕДН(ЛЕВО(A1&ПОВТОР(" ",31),31),21,1)&СРЕДН(ЛЕВО( A1&ПОВТОР(" ",31),31),20,1)&СРЕДНИЙ(ЛЕВЫЙ(A1&ПОВТОР(" ",31),31),19,1)&СРЕДНИЙ(ЛЕВЫЙ(A1&ПОВТОР(" ",31),31),18 ,1)&СРЕДНИЙ(ЛЕВЫЙ(A1&ПОВТОР(" ",31),31),17,1)&СРЕДНИЙ(ЛЕВЫЙ(A1&ПОВТОР(" ",31),31),16,1)&СРЕДНИЙ(ЛЕВЫЙ(A1&ПОВТОР(" ", 31),31),15,1)&СРЕДНИЙ(ЛЕВЫЙ(A1&ПОВТОР(" ",31),31),14,1)&СРЕДНИЙ(ЛЕВЫЙ(A1&ПОВТОР(" ",31),31),13,1)&СРЕДНИЙ( ЛЕВЫЙ(A1&ПОВТОР(" ",31),31),12,1)&СРЕДНИЙ(ЛЕВЫЙ(A1&ПОВТОР(" ",31),31),11,1)&СРЕДНИЙ(ЛЕВЫЙ(A1&ПОВТОР(" ",31),31) ,10,1)&СРЕДНЕЕ(ЛЕВО(A1&ПОВТОР(" ",31),31),9,1)&СРЕДНЕЕ(ЛЕВО(A1&ПОВТОР(" ",31),31),8,1)&СРЕДНЕЕ(ЛЕВО(A1&ПОВТОР(" ",31),31),7,1)&СРЕДНИЙ(ЛЕВЫЙ(A1&ПОВТОР(" ",31),31),6,1)&СРЕДНИЙ(ЛЕВЫЙ(A1&ПОВТОР(" ",31),31),5,1) &СРЕДНЯ(ЛЕВО(A1&ПОВТОР(" ",31),31),4,1)&СРЕДНЯ(ЛЕВО(A1&ПОВТОР(" ",31),31),3,1)&СРЕДНЯ(ЛЕВО(A1&ПОВТОР(" ",31),31),2,1)&СРЕДН(ЛЕВЫЙ(A1&ПОВТОР(" ",31),31),1,1))

Результат: A1: Дункан Салливан-Шоу B1: wahS-navilluS nacnuD

В основном ваш текст в ячейке A1 становится длиной 31 символ, если добавить достаточно пробелов, чтобы включить это, и каждый символ читается отдельно справа налево, пока вы не дойдете до первого символа. Это приведет к тому, что ваш перевернутый текст будет содержать начальные пробелы, которые команда TRIM удалит для вас. Чтобы уменьшить или увеличить длину, вы должны удалить или добавить каждое утверждение. Число в инструкции REPT должно быть на 1 больше максимальной длины, до которой вы работаете, и ваша первая инструкция MID начнется с максимальной длины, уменьшающейся до 1.

У меня нет Kutools для реверсирования символа текста, если у кого-то есть эти инструменты и вы хотите мне помочь, отправьте мне электронное письмо, чтобы отправить вам мой файл для реверса.

пожалуйста, ознакомьтесь с инструкциями, предоставленными на веб-сайте офиса расширения, после поиска в Google по обратной строке в Excel.

Инверсия строки может не иметь большого практического применения, особенно при работе с электронными таблицами. Поэтому в Excel вы не найдете для этого встроенной функции.

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

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

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

Оглавление

Обратное преобразование текстовой строки с помощью текстовых формул

Давайте сначала разберемся с основным процессом, связанным с обращением строки.

Допустим, у вас есть одна ячейка с текстом, который вы хотите инвертировать.

Чтобы перевернуть строку «Привет» в ячейке A1, выполните следующие действия:

Теперь вы должны увидеть перевернутую строку "Hello" в ячейке B6.

Что здесь произошло?

Теперь давайте посмотрим, что делает формула.

Функция ДЛСТР($A$1) возвращает длину строки в ячейке A1. Мы не хотим, чтобы эта ссылка на ячейку менялась при копировании в ячейки ниже B1, поэтому мы заблокировали ссылку на ячейку, добавив к ней знаки «$». Длина строки «Привет» равна 5, поэтому эта функция вернет значение «5».

Функция ROW(B1) просто возвращает номер строки ячейки B1.С тем же успехом мы могли бы ввести «1» вместо «СТРОКА (B1)», но хотели, чтобы это значение менялось каждый раз, когда оно копировалось в новую ячейку. Поэтому, когда он копируется в ячейку B2, он изменяется на значение «2». При копировании в ячейку B3 оно изменилось на значение «3» и т. д.

Теперь LEN($A$1)-ROW(B1)+1 дает нам значение «5», которое является индексом последнего символа в «Hello».

Функция MID() возвращает символы с заданным индексом. Он принимает три параметра: строку или ссылку на строку, индекс и количество возвращаемых символов, начиная с этого индекса.

Здесь формула =MID($A$1,LEN($A$1)-ROW(B1)+1,1) принимает ссылку на ячейку A1 в качестве первого параметра, индекс, 5 в качестве второго параметра и число 1 в качестве третьего параметра. Таким образом, формула возвращает символ с индексом 5 ячейки A1. Это означает, что мы получаем значение «o» — последний символ строки.

Вот что происходит, когда вы разбиваете формулу:

=MID($A$1,LEN($A$1)-СТРОКА(B1)+1,1)
=MID($A$1, 5 – 1 + 1, 1)
=MID($A$1, 5, 1)
="о"

Что происходит, когда формула копируется в ячейку B2?

В ячейке B2 формула изменится на:

Вот что происходит, когда вы разбиваете формулу:

=MID($A$1,LEN($A$1)-СТРОКА(B2)+1,1)
=MID($A$1, 5 – 2 + 1, 1)
=MID($A$1, 4, 1)
="л"

Что происходит, когда формула копируется в ячейку B5?

В ячейке B5 формула изменится на:

Вот что происходит, когда вы разбиваете формулу:

=MID($A$1,LEN($A$1)-СТРОКА(B5)+1,1)
=MID($A$1, 5 – 5 + 1, 1)
=MID($A$1, 1, 1)
="Н"

Таким образом, функция возвращает первый символ строки в A1.

Обратить текстовую строку в Excel с помощью формулы ТРАНСП

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

Вот укороченная и более практичная версия описанной выше техники:

В ячейке B1 введите формулу:
Выделите всю формулу в строке формул и нажмите F9 на клавиатуре.
Это должно отображать каждый символ вашей строки, перевернутый и разделенный запятыми. Все это также должно быть окружено фигурными скобками, что означает, что это массив: .
Удалите открывающую и закрывающую фигурные скобки.
Введите «СЦЕПИТЬ», а затем раскройте круглые скобки после знака равенства.
Закройте круглые скобки после буквы "Н".
Нажмите клавишу возврата.

Теперь вы должны увидеть обратную сторону строки «Hello» в ячейке B1. Этот расчет был сделан напрямую и в одной ячейке. Таким образом, это гораздо более эффективный способ использования формулы для обращения строки.

Однако есть несколько замечаний по поводу этой техники:

Перевернутая строка не изменится, если вы измените значение в ячейке A1. Вам нужно будет повторять этот процесс каждый раз при внесении изменений.
Этот метод удобен, если у вас есть несколько строк, которые вы хотите инвертировать, но если у вас есть целый список строк, которые нужно инвертировать, это может оказаться немного трудоемким. Это связано с тем, что вам нужно всегда повторять шаги со 2 по 7 для каждой строки.

Из-за указанных выше двух недостатков мы рекомендуем использовать сценарий VBA для переворачивания текстовой строки в Excel.

Перестановка символов текстовой строки в Excel с помощью макроса VBA

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

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

Выполните следующие действия:

На ленте меню Разработчик выберите Visual Basic.
Когда откроется окно VBA, нажмите Insert->Module. Теперь можно приступать к кодированию. Введите или скопируйте и вставьте приведенные выше строки кода в окно модуля. Теперь ваш код готов к запуску.
Выделите одну ячейку, содержащую текст, который вы хотите перевернуть. Убедитесь, что ячейка по горизонтали рядом с ней пуста, потому что именно здесь макрос будет отображать перевернутую строку.
Перейдите к Разработчик->Макросы->Reverse_String->Выполнить.

Теперь вы увидите перевернутую строку рядом с выбранной ячейкой.

Примечание. Если вы не видите ленту разработчика, в меню "Файл" выберите Параметры. Выберите Настроить ленту и установите флажок Разработчик на основных вкладках.

Наконец, нажмите OK.

Реверсирование списка строк в Excel с помощью VBScript

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

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

Выполните следующие действия:

На ленте меню Разработчик выберите Visual Basic.
Когда откроется окно VBA, нажмите Вставить->Модуль. Теперь можно приступать к кодированию. Введите или скопируйте и вставьте приведенные выше строки кода в окно модуля. Теперь ваш код готов к запуску.
Выделите диапазон ячеек, содержащих текст, который вы хотите перевернуть. Убедитесь, что столбец рядом с ним пуст, потому что именно здесь макрос будет отображать перевернутые строки.
Перейдите к Разработчик->Макросы->reverse_string_range->Выполнить.

Теперь вы увидите перевернутые строки рядом с выбранным диапазоном ячеек.

Не забудьте сохранить резервную копию своей таблицы, потому что результаты кода VBA обычно необратимы.

Подводя итог, мы рассмотрели два способа инвертирования строк в Excel. В одном методе используется формула, а в другом — код VBA.

Первый способ подойдет, если вам не очень удобно работать с кодом VBA. Но это полезно, только если у вас есть одна или несколько ячеек, которые вы хотите инвертировать.

Если необходимо обратить больше ячеек, это может занять много времени.

Вместо этого вы можете использовать второй метод (используя код VBA), так как он экономит время и всего несколькими щелчками мыши вы можете за один раз перевернуть весь список строк.

Несмотря на то, что в Excel нет простых способов инвертировать текстовую строку, я надеюсь, что показанные здесь методы помогут вам легко это сделать.

В этом сообщении блога мы создаем обратную формулу НАЙТИ для извлечения текста после последнего вхождения символа.

Многие из вас, читающие это, возможно, уже знакомы с функцией НАЙТИ. Вы, вероятно, использовали бы его с LEFT или MID, чтобы найти символ-разделитель и вернуть текст до или после этого символа.

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

Посмотрите видео – формула обратного поиска

Обратная формула FIND Excel

В этом примере мы работаем с данными ниже в столбце A. Мы хотели извлечь текст после последнего символа дефиса.

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

Заполненная формула Excel для этого поиска в обратном направлении показана ниже.

Эта формула чудовищна, поэтому подробное объяснение приведено ниже. Видео выше также объясняет это шаг за шагом.

Найти общее количество вхождений персонажа

Первая наша задача — вернуть общее количество вхождений символа дефиса.

Наша конечная цель – получить текст после последнего вхождения, поэтому нам нужно знать, сколько их всего.

В приведенной ниже формуле используется функция ПОДСТАВИТЬ, чтобы заменить все вхождения дефиса ничем. Функция ДЛСТР используется для подсчета количества символов, оставшихся после удаления дефисов.

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

Заменить последний символ уникальным маркером

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

В этом примере в качестве уникального маркера используется звездочка (*). Это мог быть любой текстовый символ.

Для этого используется великолепная функция ПОДСТАВИТЬ с небольшой помощью друзей.

Предыдущая часть формулы использовалась в аргументе номера экземпляра функции ПОДСТАВИТЬ для указания замены последнего дефиса.

Извлечь текст после последнего символа-разделителя

Теперь, когда мы вставили уникальный маркер в позицию последнего символа-разделителя, пришло время найти его и извлечь нужный текст.

Следующая формула была добавлена к тому, что мы получили на предыдущем шаге. .представляет часть формулы из предыдущего шага.

Функция ПРАВИЛЬНО используется для извлечения текста из конца ячейки A2.

Чтобы рассчитать количество символов для извлечения, функция ДЛСТР возвращает общее количество символов в ячейке. А затем функция НАЙТИ находит наш уникальный символ-маркер.

Позиция маркера вычитается из общего количества символов в ячейке.

Окончательная формула приведена ниже.

Это можно настроить так, чтобы возвращался текст из предпоследнего разделителя или чего угодно.

Теперь у вас есть способ поиска строки справа налево в дополнение к обычному поиску слева направо.

Обратный порядок в Excel — это не что иное, как переворачивание данных, при котором нижнее значение идет вверху, а верхнее значение — внизу.

Например, посмотрите на изображение ниже.

Как мы видим, нижнее значение находится вверху в обратном порядке, и то же самое относится и к верхнему значению. Итак, как нам изменить порядок данных в Excel на противоположный, это вопрос сейчас.

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

В Excel мы можем сортировать несколькими способами; здесь мы покажем вам все возможные способы изменить порядок в Excel.

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

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

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

Шаг 1. Рассмотрим приведенные ниже данные для этого примера.

Шаг 2. Сначала откройте функцию ИНДЕКС.

Шаг 3. Для массива выберите названия городов из A2: A9 и сделайте их абсолютными ссылками, нажав клавишу F4.

Шаг 4. Затем, чтобы вставить Row Num, откройте функцию ROWS внутри функции INDEX.

Шаг 6. Закройте скобку и нажмите клавишу ввода, чтобы получить результат.

Шаг 7. Перетащите формулу, чтобы получить полный результат.

Код:

Скопируйте этот код в свой модуль.

Теперь запустите код, чтобы получить список в обратном порядке на листе Excel.

Позвольте мне объяснить вам, как работает этот код. Во-первых, я объявил две переменные, «k» и «LR», как тип данных LONG.

«k» — для циклического просмотра ячеек, а «LR» — для поиска последней использованной строки на листе.

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

Затем я применил цикл FOR NEXT для перебора ячеек в обратном порядке.

Итак, когда мы запускаем этот код, мы получаем следующий результат.

Что нужно помнить

В Excel нет встроенной функции или инструмента для изменения порядка.
Комбинация INDEX + ROWS изменит порядок.
Из всех доступных методов сортировка является лучшим и простым способом сортировки.
Чтобы понимать код VBA, необходимо иметь представление о макросах VBA.

Функция обратной цепочки в Excel

Обратное преобразование текстовой строки с помощью текстовых формул

Что здесь произошло?

Что происходит, когда формула копируется в ячейку B2?

Что происходит, когда формула копируется в ячейку B5?

Обратить текстовую строку в Excel с помощью формулы ТРАНСП

Перестановка символов текстовой строки в Excel с помощью макроса VBA

Реверсирование списка строк в Excel с помощью VBScript

Посмотрите видео – формула обратного поиска

Обратная формула FIND Excel

Найти общее количество вхождений персонажа

Заменить последний символ уникальным маркером

Извлечь текст после последнего символа-разделителя

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

Что нужно помнить

Рекомендуемые статьи