Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как использовать функцию замены для замены и добавления строки в Excel?

Заменить данные с помощью функции SUBSTITUTE в Excel

Функция ЗАМЕНА заменяет существующие слова, текст или символы новыми данными. Используйте функцию ЗАМЕНИТЬ, чтобы удалить непечатаемые символы из импортированных данных, заменить ненужные символы пробелами и создать разные версии одной и той же таблицы.

Примечание. Инструкции в этой статье применимы к Excel 2019, 2016, 2013, 2010, 2007; Excel для Office 365, Excel Online, Excel для Mac, Excel для iPad, Excel для iPhone и Excel для Android.

ЗАМЕНЯЮЩИЙ Синтаксис функции

Примером синтаксиса функции является схема функции, которая включает в себя имя функции, скобки и аргументы.

Синтаксис для функции ЗАМЕНА:

Аргументы для функции:

  • Текст (обязательно). Данные, содержащие заменяемый текст. Этот аргумент может содержать фактические данные, окруженные инвертированными запятыми (см. строку 2 на рисунке выше) или ссылку на ячейку, содержащую текстовые данные (см. строки 3 и 4).
  • Старый_текст (обязательно): текст для замены.
  • Новый_текст (обязательно): текст для замены старого_текста.
  • Instance_num (необязательно): число. Если это число опущено, каждый экземпляр old_text будет заменен на new_text. Если это число включено, то экземпляры заменяются на заданный old_text (см. строку 5).

Аргументы для функции SUBSTITUTE чувствительны к регистру. Если данные, введенные для аргумента Old_text, не совпадают с регистром данных в ячейке аргумента Text, подстановка не происходит.

Используйте функцию ЗАМЕНИТЕЛЯ

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

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

Введите данные для учебника в пустой лист Excel.

Активируйте ячейку B3, щелкнув по ней мышью.

Выберите Формулы .

Выберите Текст, чтобы открыть выпадающий список функций текста.

Нажмите ЗАМЕНА из списка, чтобы открыть диалоговое окно Аргументы функции. В Excel для Mac откроется построитель формул.

Читайте так же:
Как конвертировать сразу несколько книг или листов в файлы PDF в Excel?

Установите курсор в текстовое окно.

Выберите ячейку А3 на листе, чтобы ввести ссылку на эту ячейку.

Поместите курсор в текстовое поле Old_text .

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

Поместите курсор в текстовое поле New_text .

Введите рецепты. Этот текст будет заменен на «Продажи».

В ячейке A3 имеется только один экземпляр слова Sales, поэтому номер экземпляра оставлен пустым.

Выберите OK . За исключением Excel для Mac, где выбрано значение Готово.

Текст отчета о доходах отображается в ячейке B3.

ЗАМЕНА ПРОТИВ ЗАМЕНЫ Функции

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

Статья Как в Excel сделать перенос текста в ячейке

Часто текст необходимо перенести на новую строку в пределах одной ячейки Excel. Это означает перемещение текста по строкам в пределах одной ячейки, как показано на рисунке. После ввода первой части текста, если после нее нажать ENTER, курсор переместится на следующую строку, но в другую ячейку, а нам нужно переместить в пределах одной ячейки.

Это очень распространенная проблема, которую очень легко решить — чтобы перенести текст на новую строку в ячейке Excel, нажмите ALT+ENTER (удерживайте клавишу ALT и нажмите ENTER, удерживая ее нажатой).

Как переместить текст на новую строку в Excel с помощью формулы

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

Первое, что нам нужно сделать, это связать тексты в ячейках A1 и B1 (A1&B1), A2 и B2 (A2&B2), и A3 и B3 (A3&B3).

После этого объединим все эти пары, но так же нам необходимо между этими парами поставить символ (код) переноса строки. Есть специальная таблица знаков (таблица есть в конце данной статьи), которые можно вывести в Excel с помощью специальной функции СИМВОЛ(число), где число это число от 1 до 255, определяющее определенный знак.
Например, если прописать =СИМВОЛ(169), то мы получим знак копирайта ©

Читайте так же:
Как использовать ярлык для удаления дубликатов в столбце или строке Excel?

Нам же требуется знак переноса строки, он соответствует порядковому номеру 10 — это надо запомнить.
Код (символ) переноса строки — 10
Следовательно перенос строки в Excel в виде функции будет выглядеть вот так СИМВОЛ(10)

Примечание: В VBA Excel перевод строки вводится с помощью функции Chr и выглядит как Chr(10)

Таким образом, в ячейке A6 мы пишем формулу

В итоге мы должны получить нужный нам результат.
Обратите внимание! Чтобы перенос строки корректно отображался необходимо включить "перенос по строкам" в свойствах ячейки.
Для этого выделите нужную нам ячейку (ячейки), нажмите на правую кнопку мыши и выберите "Формат ячеек. "

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

Как в Excel заменить знак переноса на другой символ и обратно с помощью формулы

Вы можете изменить символ дефиса на любой другой символ, например пробел, с помощью функции Excel «ПРОБЕЛ В ТЕКСТ».

В качестве примера рассмотрим изображение выше. Итак, мы пишем функцию SUBSTITUTE в ячейке B1:

A1 — это наш текст с переносом строки;
СИМВОЛ(10) — это перенос строки (мы рассматривали это чуть выше в данной статье);
" " — это пробел, так как мы меняем перенос строки на пробел

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

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

Как изменить дефис на пробел и обратно в Excel с помощью ПОИСК — ПЕРЕЗАПИСЬ

Читайте так же:
Как легко отменить выбор выбранных диапазонов в Excel?

Иногда формулы громоздки в использовании, и вам нужно быстро сделать замену. Для этого используйте функцию «Найти и заменить». Выделите наш текст и нажмите CTRL+H, чтобы вызвать следующее окно.

Если нам необходимо поменять перенос строки на пробел, то в строке "Найти" необходимо ввести перенос строки, для этого встаньте в поле "Найти", затем нажмите на клавишу ALT, не отпуская ее наберите на клавиатуре 010 — это код переноса строки, он не будет виден в данном поле.

После этого в поле "Заменить на" введите пробел или любой другой символ на который вам необходимо поменять и нажмите "Заменить" или "Заменить все".

Кстати, в Word это реализовано более наглядно.

Если вам необходимо поменять символ переноса строки на пробел, то в поле "Найти" вам необходимо указать специальный код "Разрыва строки", который обозначается как ^l
В поле "Заменить на:" необходимо сделать просто пробел и нажать на "Заменить" или "Заменить все".

Вы можете менять не только перенос строки, но и другие специальные символы, чтобы получить их соответствующий код, необходимо нажать на кнопку "Больше >>", "Специальные" и выбрать необходимый вам код. Напоминаю, что данная функция есть только в Word, в Excel эти символы не будут работать.

Изменение перевода строки в Excel на пробел или наоборот с помощью VBA

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

Функция СМЕЩ в Excel

Функция СМЕЩ на первый взгляд является довольно сложной для понимания, что зачастую отталкивает пользователя от ее использования и поэтому незаслуженно редко используется.
Тем не менее она может быть очень полезна (например, при создании динамических диапазонов) и при грамотном использовании существенно упрощает работу.

Описание функции СМЕЩ

СМЕЩ(ссылка; смещ_по_строкам; смещ_по_столбцам; [высота]; [ширина])
Возвращает ссылку на диапазон, смещенный относительно заданной ссылки на указанное количество строк и столбцов.

  • Ссылка(обязательный аргумент) — ссылка на ячейку или диапазон смежных ячеек;
  • Смещение по строкам(обязательный аргумент) и по столбцам(обязательный аргумент) — число строк и столбцов, на которое результирующий диапазон смещен относительно исходной ссылки; Например, аргументы 4; 3 сместят ссылку на 4 строчки вниз и 3 столбца вправо. При этом оба параметра могут принимать различные значения — положительные (смещение вниз по строкам/вправо по столбцам), нулевые или отрицательные (смещение вверх по строкам/влево по столбцам).
  • Высота(необязательный аргумент) и ширина(необязательный аргумент) — высота (в строках) и ширина (в столбцах) возвращаемого диапазона, по умолчанию высота и ширина совпадают с размерами исходной ссылки; Например, аргументы 5; 2 расширят ссылку до диапазона в 5 ячеек высотой и 2 ячейки шириной.
Читайте так же:
Как массово удалить определенное слово в Excel?

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

Принцип работы функции СМЕЩ

К примеру, формула =СМЕЩ(A1;0;0;5;4) (на рисунке выделена красным цветом) сдвигает ячейку A1 (аргумент функции №1) на (№2) вниз, на (№3) вправо, получаем диапазон A1 (состоящий из одной ячейки), а затем расширяет его до размера 5 (№4) на 4 (№5), т.е. возвращаемая ссылка принимает вид A1:D5 (на рисунке область также выделена красным цветом).
Аналогично, формула =СМЕЩ(A1;1;2;8;3) (выделена синим цветом) сдвигает ячейку A1 на 1 вниз, на 2 вправо, получаем диапазон C2 и расширяет его до размера 8 на 3, т.е. в результате получаем ссылку C2:E9.

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

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

Пример использования функции СМЕЩ

Функция СМЕЩ возвращает ссылку, поэтому может использоваться с другими функциями, в которых среди аргументов есть ссылки.
Поэтому теперь рассмотрим как пользоваться данной формулой вместе с другими на примере стандартных типовых задач.

Пример 1. Функция ПОИСКПОЗ

Предположим, у нас есть ежедневные данные о продажах компании, и мы хотим определить продажи за определенную дату.

Таблица с данными
Воспользуемся функцией ПОИСКПОЗ для поиска указанной даты (ячейка D2) в диапазоне с датами (A2:A10).
После чего сместим начальную ячейку (в данном случае B2) на рассчитанную величину вниз за вычетом единицы.
Мы дополнительно вычитаем единицу так как показываем именно смещение относительно начальной ячейки, например, чтобы перейти с первой строки на шестую мы смещаемся ровно на пять строк.
В итоге получаем следующий результат:

Читайте так же:
Как извлечь часть текстовой строки из ячейки в Excel?

Пример формул СМЕЩ и ПОИСКПОЗ

Идентичного результата можно добиться и с помощью функции ИНДЕКС — формула =ИНДЕКС(B2:B10;ПОИСКПОЗ(D2;A2:A10;0)) вернет точно такой же результат.

Пример 2. Функция СУММ

Возьмем начальные условия как в предыдущем примере, однако теперь мы посчитаем сумму продаж за последние 7 дней.
Можно воспользоваться стандартной формулой СУММ(B4:B10), но при добавлении новых строчек расчет становится неверным и нам придется каждый раз изменять формулу, поэтому мы пойдем по другому пути.
С помощью функции СЧЁТЗ находим последнюю введенную дату (указываем достаточно большой диапазон A2:A100, чтобы была возможность добавлять новые данные).
Из полученного результата вычитаем 7, чтобы найти первую дату искомого диапазона, поэтому производя сдвиг начальной ячейки (B2) на найденную величину и расширяя диапазон до размеров 7 на 1, мы получим данные за 7 последних дней.
Просуммируем их воспользовавшись функцией СУММ:

Пример формул СМЕЩ и СУММ

При добавлении новых данных в таблицу результат автоматически пересчитывается:

Пример формул СМЕЩ и СУММ

Особенности применения

Функция СМЕЩ обладает еще одной отличительной особенностью — она является волатильной (пересчитываемой).
В отличие от большинства других функций, которые пересчитываются только в том случае, когда меняются ячейки являющиеся их аргументами, СМЕЩ пересчитывается при изменении любой ячейки.
В связи с этим данная особенность может приводить к замедлению работы книги, поэтому следует применять формулу с осторожностью.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector