Подстановка данных из excel в word: Как вставить или перенести таблицу из Excel в Word

Содержание

Как вставить или перенести таблицу из Excel в Word

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

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

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

Способ 1: Простое копирование таблицы из Excel в Word

Простое копирование – самый удобный метод переноса таблицы, если она уже создана в Эксель и должна стать частью текстового документа. Для этого вам понадобится параллельно открыть две программы и использовать стандартные сочетания клавиш.

  1. В Excel выделите всю таблицу, после чего зажмите комбинацию клавиш Ctrl + C. Если таблица не скомпонована или в ней нет выделения границ, лучше заранее решить эту задачу, иначе содержимое в Ворде отобразится без разделения на ячейки. Проще всего будет перейти в меню «‎Границы» и выбрать вариант «‎Все границы».

  2. Как только таблица будет скопирована, откройте необходимый вам документ в текстовом редакторе, активируйте курсор в месте для вставки и используйте комбинацию клавиш Ctrl + V.

  3. Вы увидите, что таблица вставилась корректно, но отображается слева, если не растянута на весь лист. Зажмите кнопку для ее перемещения и передвиньте в удобное место.

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Подписаться

Способ 2: Вставка таблицы из файла

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

  1. Откройте документ в Word, поставьте курсор на место расположения будущей таблицы, перейдите на вкладку «‎Вставка» и выберите тип «‎Объект».

  2. В новом окне вас интересует вкладка «‎Создание из файла» и переход к «‎Проводнику» для выбора подходящей Excel-книги.

  3. Отыщите ее и щелкните дважды для открытия.

  4. Она будет добавлена на страницу и доступна для редактирования.

  5. Что касается редактирования, то осуществляется оно в отдельном модуле, открывающемся после двойного клика ЛКМ по таблице. Так вы можете без проблем редактировать ячейки и использовать функции.

Дополнительно обращаю ваше внимание на то, что в окне «‎Вставка объекта» есть и другая вкладка, называющаяся «‎Создание». В ней вы можете выбрать вариант «‎Microsoft Excel Worksheet», вставив тем самым на страницу пустую таблицу. Редактируйте ее точно так же, как и обычный лист в Экселе, добавляя различные вычисления и функции.

Способ 3: Создание таблицы в Word

Этот метод не совсем относится к рассматриваемой сегодня задаче, но может пригодиться в некоторых случаях. На вкладке «‎Вставка» вы видите меню «‎Таблица», из которого можно сформировать таблицу на определенное количество ячеек, вставить ее по заданным параметрам, использовать шаблоны или выбрать тип «‎Таблица Excel». В последнем случае произойдет такая же вставка, о которой я рассказал в завершении предыдущего метода. Используйте любой из предложенных вариантов, когда понадобится оформить таблицу в простом текстовом документе.

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

Подготовка источника данных Excel к слиянию в Word

Вы создали список контактов и других данных, который хотите использовать для слияния в Word. Если в качестве источника данных используется таблица Excel, то достаточно подготовить данные для слияния. Но если источником данных является файл со значениями, разделенными табуляциями (TXT) или запятыми (CSV), сначала следует импортировать данные в Excel, а затем подготовить их для слияния.

Шаг 1. Настройка источника данных в Excel


Если вы используете электронную таблицу Excel в качестве источника данных для слияния в Word, пропустите этот шаг. Если источником данных является TXT- или CSV-файл, мастер импорта текста поможет вам отформатировать данные в Excel.

После импорта TXT- или CSV-файла перейдите к шагу 2.



  1. Откройте Excel.

  2. Щелкните Данные > Из текстового/CSV-файла.

  3. org/ListItem»>

    Выберите нужный TXT- или CSV-файл и нажмите кнопку Импорт.

  4. В окне предварительного просмотра нажмите кнопкуПреобразование данных.

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

  6. Выберите Преобразование > Тип данных: > Текст.

  7. org/ListItem»>

    Нажмите кнопку Заменить текущий.

  8. При необходимости повторите шаги 5–7.

  9. Выберите Закрыть и загрузить.

  10. Выберите Файл > Сохранить как и сохраните файл с другим именем.

  1. org/ListItem»>

    Откройте Excel.


  2. Excel 2016    Выберите Данные > Получение внешних данных > Из текста.

  3. Выберите нужный TXT- или CSV-файл и нажмите кнопку Импорт.

  4. В мастере импорта текста в области Тип исходных данных выберите С разделителями.

  5. При необходимости выберите элемент Мои данные содержат заголовки над областью Предварительный просмотр, а затем нажмите кнопку Далее.

  6. В области Разделители установите нужный флажок (например, «знак табуляции» или «запятая») и нажмите кнопку Далее.

  7. В области Образец разбора данных выберите столбец, содержащий почтовые индексы, а затем в списке Формат данных столбца выберите Текстовый.

    Примечание: Каждый раз, когда вы применяете формат («общий», «текстовый» или «дата») к столбцу, в его заголовке отображается имя формата.

  8. При необходимости повторите действие 7, выбрав столбец, который вы хотите изменить, и нужный формат данных.

  9. Нажмите кнопку Finish (Готово).

  10. В диалоговом окне Импорт данных примите значение по умолчанию (имеющийся лист), а затем нажмите кнопку ОК.

    Внимание: В диалоговом окне Импорт данных указывается адрес выбранной ячейки. Данные импортируются, начиная с этой ячейки.

  11. Сохраните электронную таблицу с новым именем файла.


Шаг 2. Подготовка источника данных


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

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

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

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

  2. На вкладке Главная в группе Число щелкните стрелку раскрывающегося списка Числовой формат и выберите нужное значение (например, «Текст»).

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

Если добавить символы, числа будут иметь смысл.

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


См. также



  • Создание и отправка массовой рассылки, наклеек и конвертов с помощью слияния

  • org/ListItem»>


    Слияние почты — бесплатное 10-минутное обучающее видео

Важным шагом при настройке слияния в Word является подготовка источника данных. Вы можете использовать существующий источник данных Excel или создать новый, импортировав файл, разделенный табуляциями (TXT) или запятыми (CSV). После настройки и подготовки источника данных можно выполнить слияние почты с помощью динамический обмен данными (DDE) с мастером слияния или вручную.

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

  1. Откройте Excel.

  2. На вкладке Данные выберите элемент Из тестового/CSV-файла.

  3. Выберите нужный TXT- или CSV-файл и нажмите кнопку Импорт.

  4. В мастере импорта текста в области Тип исходных данных выберите С разделителями.

  5. При необходимости установите флажок Мои данные содержат заголовки над областью Предварительный просмотр файла, а затем нажмите кнопку Далее.

  6. В разделе Разделители установите нужный флажок (например, «знак табуляции» или «запятая») и нажмите кнопку Далее.

    Совет: В области Образец разбора данных вы можете увидеть, как результаты будут выглядеть в виде таблицы.

  7. В области Образец разбора данных выберите столбец, содержащий почтовые индексы, а затем в списке Формат данных столбца выберите Текстовый.

    Примечания: 

    • org/ListItem»>

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

    • Столбец, содержащий числовые данные, например процентные или денежные значения, можно отформатировать только после импорта файла. См. раздел «Форматирование числовых данных в Excel» ниже.

  8. При необходимости повторите действие 7, выбрав столбец, который вы хотите изменить, и нужный формат данных.

  9. org/ListItem»>

    Нажмите кнопку Finish (Готово).

  10. В диалоговом окне Импорт данных примите значение по умолчанию (имеющийся лист), а затем нажмите кнопку ОК.

    Внимание: В диалоговом окне Импорт данных указывается адрес выбранной ячейки. Данные импортируются, начиная с этой ячейки.

  11. Добавив данные, сохраните источник с новым именем файла.


Форматирование числовых данных в Excel

Отформатируйте все числовые данные, например процентные или денежные значения, в новых и существующих источниках данных Excel, которые предполагается использовать при слиянии в Word. Чтобы сохранить числовые данные, которые вы отформатировали как процентные или денежные значения, при слиянии, следуйте инструкциям в разделе «Шаг 2. Слияние с помощью динамического обмена данными (DDE)».

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

  1. Откройте источник данных Excel.

  2. Выберите столбец, который вы хотите отформатировать.

  3. org/ListItem»>

    На вкладке Главная в группе Число в щелкните стрелку раскрывающегося списка Числовой формат (Общий) и выберите нужное значение.

  4. При необходимости повторите шаги 2 и 3.

  5. После завершения нажмите кнопку Сохранить.

Если в источнике данных Excel есть числа, отформатированные как процентные или денежные значения, они могут потерять форматирование во время слияния. Чтобы получить необходимый результат при слиянии и сохранить формат числовых данных в Excel, используйте DDE.

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

  1. Откройте Word и выберите Файл > Параметры > Дополнительно.

  2. В разделе Общие установите флажок Подтверждать преобразование формата файла при открытии.

  3. Нажмите кнопку ОК.

  4. На вкладке Рассылки щелкните Начать слияние > Пошаговый мастер слияния.

  5. В области Слияние в разделе Выбор получателей щелкните Использовать существующий список.

  6. В разделе Использовать существующий список нажмите кнопку Обзор и откройте отформатированную электронную таблицу.

  7. В диалоговом окне Преобразование источника данных установите флажок Отобразить все, а затем выберите элемент Листы MS Excel с помощью DDE (*.xls) и нажмите кнопку ОК.

  8. В диалоговом окне Microsoft Excel выберите Весь лист и нажмите кнопку ОК.

  9. В диалоговом окне Получатели слияния нажмите кнопку ОК.

  10. Выполните слияние для создания писем, сообщений электронной почты, наклеек или конвертов.

Совет: Можно сделать так, чтобы при каждом открытии файла данных в Word не выводились запросы. После подключения к списку рассылки выберите Файл > Параметры > Дополнительно. В разделе Общие снимите флажок Подтверждать преобразование формата файла при открытии.

Если вы создали список контактов в электронной таблице Excel, отформатируйте все почтовые индексы как текст во избежание потери данных. Если вы импортируете контакты из TXT- или CSV-файла в новую электронную таблицу, мастер импорта текста поможет вам отформатировать данные.

Шаг 1.

Настройка источника данных в Excel


Если вы уже используете электронную таблицу Excel в качестве источника данных для слияния в Word, перейдите к шагу 2 в этой статье. Если вы импортируете контакты, например контакты Gmail, из TXT- или CSV-файла, мастер импорта текста поможет вам отформатировать данные в Excel.

  1. Откройте Excel, а затем на вкладке Данные выберите Из текста.

  2. Щелкните нужный файл в формате CSV или TXT и выберите команду Получить данные.

  3. org/ListItem»>

    В мастере импорта текста нажмите кнопку Далее.

  4. В разделе Разделители установите флажок для соответствующего элемента, разделяющего данные (например, знака табуляции или запятой). Нажмите кнопку Далее.

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


     

  5. В области Предварительный просмотр выбранных данных выберите столбец с почтовыми индексами. В разделе Формат данных столбца выберите значение Текст.




     

  6. При необходимости повторите действие 5, выбрав столбец, который вы хотите изменить, и нужный формат данных.

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

  7. Нажмите кнопку Готово.

  8. В диалоговом окне Импорт данных укажите, куда Excel следует поместить данные, и нажмите кнопку ОК.

    Примечание: Сохраните электронную таблицу с новым именем файла.

Шаг 2. Настройка формата числовых данных в электронной таблице Excel


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

  1. Откройте электронную таблицу и выберите столбец с почтовыми индексами.

  2. На вкладке Главная выберите в списке Формат пункт Текстовый.

Теперь вы можете использовать эти данные для слияния.




Функция ПОДСТАВИТЬ — служба поддержки Майкрософт

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Дополнительно… Меньше

В этой статье описаны синтаксис формулы и использование функции ПОДСТАВИТЬ в Microsoft Excel.

Описание

Заменяет новый_текст на старый_текст в текстовой строке. Используйте ПОДСТАВИТЬ, если вы хотите заменить определенный текст в текстовой строке; используйте REPLACE, если вы хотите заменить любой текст, который встречается в определенном месте в текстовой строке.

Синтаксис

ПОДСТАВИТЬ(текст, старый_текст, новый_текст, [номер_экземпляра])

Синтаксис функции ПОДСТАВИТЬ имеет следующие аргументы:

  • org/ListItem»>

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

  • Старый_текст     Обязательный. Текст, который вы хотите заменить.

  • New_text     Обязательный. Текст, которым вы хотите заменить old_text.

  • Номер_экземпляра     Необязательно. Указывает, какое вхождение old_text вы хотите заменить новым_текстом. Если вы укажете instance_num, будет заменен только этот экземпляр old_text. В противном случае каждое вхождение old_text в тексте заменяется на new_text.

Пример

Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового рабочего листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете настроить ширину столбцов, чтобы увидеть все данные.

Данные

Данные о продажах

<

1 квартал 2008 г.

<

1 квартал 2011 г.

<

Формула

Описание (Результат)

Результат

=ЗАМЕНИТЬ(A2, «Продажи», «Стоимость»)

Заменяет себестоимость продаж (данные о затратах)

Данные о затратах

=ЗАМЕНИТЬ(A3, «1», «2», 1)

Заменяет первый экземпляр «1» на «2» (2-й квартал 2008 г. )

2 квартал 2008 г.

=ЗАМЕНИТЬ(A4, «1», «2», 3)

Заменяет третий экземпляр «1» на «2» (1 квартал 2012 г.)

1 квартал 2012 г.

См. также

ЗАМЕНИТЬ, ЗАМЕНИТЬB функции

Функция ТРИМ

Использование функций Excel ЗАМЕНИТЬ и ПОДСТАВИТЬ

В учебнике объясняются функции Excel ЗАМЕНИТЬ и ПОДСТАВИТЬ с примерами использования. Узнайте, как использовать функцию ЗАМЕНИТЬ с текстовыми строками, числами и датами, а также как вложить несколько функций ЗАМЕНИТЬ или ПОДСТАВИТЬ в одну формулу.

На прошлой неделе мы обсудили различные способы использования функций НАЙТИ и ПОИСК на листах Excel. Сегодня мы более подробно рассмотрим две другие функции для замены текста в ячейке в зависимости от ее местоположения или замены одной текстовой строки другой в зависимости от содержимого. Как вы уже догадались, речь идет о функциях Excel ЗАМЕНИТЬ и ПОДСТАВИТЬ.

Excel функция ЗАМЕНА

Функция ЗАМЕНИТЬ в Excel позволяет заменить один или несколько символов в текстовой строке другим символом или набором символов.

ЗАМЕНИТЬ (старый_текст, начальный_номер, число_символов, новый_текст)

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

  • Старый_текст — исходный текст (или ссылка на ячейку с исходным текстом), в котором необходимо заменить некоторые символы.
  • Start_num — позиция первого символа в old_text, который вы хотите заменить.
  • Num_chars — количество символов, которые вы хотите заменить.
  • New_text — текст замены.

Например, чтобы изменить слово « солнце » на « сын «, вы можете использовать следующую формулу:

=ЗАМЕНИТЬ("солнце", 2, 1, "о")

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

=ЗАМЕНИТЬ(A2, 2, 1, "о")

Примечание. Если аргумент start_num или num_chars отрицательный или нечисловой, формула замены Excel возвращает ошибку #ЗНАЧ! ошибка.

Использование функции Excel REPLACE с числовыми значениями

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

=ЗАМЕНИТЬ(A2, 7, 4, "2016")

Обратите внимание, что мы заключаем «2016» в двойные кавычки, как вы обычно делаете с текстовыми значениями.

Аналогичным образом можно заменить одну или несколько цифр в номере. Например:

=ЗАМЕНИТЬ(A4, 4, 4,"6")

И снова нужно заключить значение замены в двойные кавычки («6»).

Примечание. Формула Excel REPLACE всегда возвращает текстовую строку , а не число. На снимке экрана выше обратите внимание на выравнивание по левому краю возвращаемого текстового значения в ячейке B2 и сравните его с исходным числом, выровненным по правому краю в ячейке A2. А поскольку это текстовое значение, вы не сможете использовать его в других вычислениях, пока не преобразуете его обратно в число, например, умножив на 1 или используя любой другой метод, описанный в разделе Как преобразовать текст в число.

Использование функции Excel REPLACE с датами

Как вы только что видели, функция ЗАМЕНА прекрасно работает с числами, за исключением того, что она возвращает текстовую строку 🙂 Помня, что во внутренней системе Excel даты хранятся в виде чисел, вы можете попробовать использовать некоторые формулы замены дат. Результаты будут весьма смущающими.

Например, у вас есть дата в A2, скажем, 1 октября 14, и вы хотите изменить « окт » на « ноябрь ». Итак, вы пишете формулу REPLACE(A2, 4, 3, «Nov»), которая указывает Excel заменить 3 символа в ячейках A2, начиная с 4 -й символ… и получил следующий результат:

Почему? Потому что «01-Oct-14» — это только визуальное представление базового серийного номера (41913), представляющего дату. Итак, наша формула замены изменяет последние 3 цифры в указанном выше серийном номере на « Nov » и возвращает текстовую строку «419Nov».

Чтобы заставить функцию Excel REPLACE правильно работать с датами, вы можете сначала преобразовать даты в текстовые строки, используя функцию ТЕКСТ или любой другой метод, показанный в разделе Как преобразовать дату в текст в Excel. Кроме того, вы можете встроить функцию ТЕКСТ непосредственно в аргумент old_text функции ЗАМЕНА:

=ЗАМЕНИТЬ(ТЕКСТ(A2, "дд-ммм-гг"), 4, 3, "ноябрь")

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

=ДАТАЗНАЧ(ЗАМЕНИТЬ(ТЕКСТ(A2, "дд-ммм-гг"), 4, 3, "ноябрь"))

Вложенные функции REPLACE для выполнения множественных замен в ячейке

Довольно часто может потребоваться выполнить более одной замены в одной и той же ячейке. Конечно, можно было сделать одну замену, вывести промежуточный результат в дополнительный столбец, а затем снова использовать функцию ЗАМЕНИТЬ. Однако лучшим и более профессиональным способом является использование вложенных функций REPLACE , которые позволяют выполнять несколько замен с помощью одной формулы. В этом контексте «вложение» означает размещение одной функции внутри другой.

Рассмотрим следующий пример. Предположим, у вас есть список телефонных номеров в столбце A, отформатированный как «123456789», и вы хотите сделать их более похожими на телефонные номера, добавив дефисы. Другими словами, ваша цель — превратить «123456789» в «123-456-789».

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

=ЗАМЕНИТЬ(A2,4,0,"-")

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

Хорошо, а теперь нам нужно вставить еще один дефис в позицию 8 th . Для этого вы помещаете приведенную выше формулу в другую функцию Excel REPLACE. Точнее, вы встраиваете его в аргумент old_text другой функции, чтобы вторая функция REPLACE обрабатывала значение, возвращаемое первой REPLACE, а не значение в ячейке A2:

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(A2,4,0,"-"),8,0,"-")

В результате вы получаете номера телефонов в нужном формате:

Аналогичным образом вы можете использовать вложенные функции REPLACE, чтобы текстовые строки выглядели как даты, добавляя косую черту (/), где это необходимо:

=(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A2,3,0,"/"),6,0,"/"))

Кроме того, вы можете преобразовать текстовые строки в реальные даты, обернув приведенную выше формулу ЗАМЕНИТЬ функцией ДАТАЗНАЧ:

=ДАТАЗНАЧ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A2,3,0,"/"),6,0,"/"))

И, естественно, вы не ограничены в количестве функций, которые вы можете вложить в одну формулу (современные версии Excel 2010, 2013 и 2016 позволяют использовать до 8192 символов и до 64 вложенных функций в формулу).

Например, вы можете использовать 3 вложенные функции REPLACE, чтобы число в A2 отображалось как дата и время:

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A2,3,0,"/") ,6,0,"/"), 9,0, " "), 12,0, ":")

Замена строки, которая появляется в разных позициях в каждой ячейке

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

Предположим, у вас есть список адресов электронной почты в столбце A. И название одной компании изменилось с «ABC» на, скажем, «BCA». Таким образом, вы должны соответствующим образом обновить адреса электронной почты всех клиентов.

Но проблема в том, что имена клиентов имеют разную длину, и поэтому нельзя указать, с чего именно начинается название компании. Другими словами, вы не знаете, какое значение указать в аргументе start_num функции Excel REPLACE. Чтобы узнать это, используйте функцию Excel НАЙТИ, чтобы определить позицию первого символа в строке «@abc»:

= НАЙТИ ("@abc", A2)

Затем укажите указанную выше функцию НАЙТИ в аргументе start_num формулы ЗАМЕНА:

=ЗАМЕНИТЬ(A2, НАЙТИ("@abc",A2), 4, "@bca")

Совет. Мы включаем «@» в нашу формулу поиска и замены Excel, чтобы избежать случайных замен в части имени адресов электронной почты. Конечно, вероятность того, что такие совпадения произойдут, очень мала, и все же вы можете перестраховаться.

Как видно на следующем снимке экрана, формула без проблем находит и заменяет старый текст новым. Однако если заменяемая текстовая строка не найдена, формула возвращает ошибку #ЗНАЧ! ошибка:

И мы хотим, чтобы формула возвращала исходный адрес электронной почты вместо ошибки. Итак, давайте заключим нашу формулу НАЙТИ И ЗАМЕНИТЬ в функцию ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(ЗАМЕНИТЬ(A2, НАЙТИ("@abc",A2), 4, "@bca"),A2)

И эта улучшенная формула прекрасно работает, не так ли?

Другое практическое применение функции ЗАМЕНИТЬ — сделать первую букву в ячейке прописной. Всякий раз, когда вы имеете дело со списком имен, продуктов и т.п., вы можете использовать приведенную выше формулу, чтобы изменить первую букву на ЗАГЛАВНУЮ.

Совет. Если вы хотите произвести замену в исходных данных, проще использовать диалог Excel НАЙТИ и ЗАМЕНИТЬ.

Excel Функция ПОДСТАВИТЬ

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

Синтаксис функции ПОДСТАВИТЬ в Excel:

ПОДСТАВИТЬ(текст, старый_текст, новый_текст, [номер_экземпляра])

Первые три аргумента обязательны, а последний необязателен.

  • Текст — исходный текст, в который необходимо заменить символы. Может быть предоставлен в виде тестовой строки, ссылки на ячейку или результата другой формулы.
  • Old_text — символы, которые вы хотите заменить.
  • New_text — новые символы для замены old_text.
  • Instance_num — вхождение старого_текста, который вы хотите заменить. Если этот параметр опущен, каждое вхождение старого текста будет заменено новым текстом.

Например, все приведенные ниже формулы заменяют «1» на «2» в ячейке A2, но возвращают разные результаты в зависимости от того, какое число указано в последнем аргументе:

=ПОДСТАВИТЬ(A2, "1", "2", 1) — Заменяет первое вхождение «1» на «2».

=ПОДСТАВИТЬ(A2, "1", "2", 2) — Заменяет второе вхождение «1» на «2».

=ПОДСТАВИТЬ(A2, "1", "2") — Заменяет все вхождения «1» на «2».

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

  • Как удалить символы или слова из строки
  • Как удалить ненужные символы из ячеек

Примечание. Функция ПОДСТАВИТЬ в Excel с учетом регистра . Например, следующая формула заменяет все вхождения буквы «X» в верхнем регистре на «Y» в ячейке A2, но не заменяет ни одной буквы «x» в нижнем регистре.

Замена нескольких значений одной формулой (вложенная ПОДСТАВКА)

Как и в случае с функцией ЗАМЕНА Excel, вы можете вложить несколько функций ПОДСТАВИТЬ в одну формулу, чтобы сделать несколько подстановок одновременно, т. е. заменить несколько символов или подстрок одной формулой.

Предположим, у вас есть текстовая строка типа « PR1, ML1, T1 » в ячейке A2, где «PR» означает «Проект», «ML» означает «Веха», а «T» означает «Задание». Что вы хотите состоит в том, чтобы заменить три кода полными именами.Для этого вы можете написать 3 разные формулы ПОДСТАВКИ:

=ЗАМЕНИТЬ(A2,"PR", "Проект")

=ЗАМЕНИТЬ(A2, "ML", "Веха")

=ЗАМЕНИТЬ(A2, "Т", "Задание")

А затем вложить их друг в друга:

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A2,"PR","Проект"),"ML","Веха"),"T","Задача")

Обратите внимание, что мы добавили пробел в конце каждого аргумента new_text для удобства чтения.

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

Excel ЗАМЕНИТЬ по сравнению с Excel ЗАМЕНИТЬ

Функции Excel REPLACE и SUBSTITUTE очень похожи друг на друга в том смысле, что обе предназначены для замены текстовых строк. Различия между двумя функциями следующие:

  • ЗАМЕНА заменяет один или несколько экземпляров заданного символа или текстовой строки. Итак, если вы знаете текст, который нужно заменить, используйте функцию Excel ПОДСТАВИТЬ.
  • REPLACE изменяет символы в указанной позиции текстовой строки. Итак, если вы знаете положение заменяемых символов, используйте функцию Excel REPLACE.
  • Функция ПОДСТАВИТЬ в Excel позволяет добавить необязательный параметр (instance_num), указывающий, какое вхождение старого_текста следует заменить на новый_текст.

Вот как вы используете функции ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel.