Excel копирование с заменой - Word и Excel - помощь в работе с программами

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 Еще…Меньше

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

При копировании ячеек, содержащих формулу, относительные ссылки на ячейки не корректируются. Таким образом, содержимое ячеек и всех ячеек, указывающих на них, может отображать #REF! (значение ошибки). В этом случае ссылки можно настроить вручную. Дополнительные сведения см. в статье Обнаружение ошибок в формулах.

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

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

Выделите ячейку, строку или столбец, которые нужно переместить или скопировать.
Выполните одно из указанных ниже действий.
- Чтобы переместить строки или столбцы, на вкладке Главная в группе Буфер обмена нажмите кнопку Вырезать или нажмите клавиши CTRL+X.
- Чтобы скопировать строки или столбцы, на вкладке Главная в группе Буфер обмена щелкните Копировать или нажмите клавиши CTRL+C.
Щелкните правой кнопкой мыши строку или столбец снизу или справа от того места, куда необходимо переместить или скопировать выделенный элемент, а затем выполните одно из указанных ниже действий.
- Чтобы переместить строки или столбцы, выберите в контекстном меню команду Вставить вырезанные ячейки.
- Чтобы скопировать строки или столбцы, выберите в контекстном меню команду Вставить скопированные ячейки.
Совет: Чтобы переместить или скопировать выделенный фрагмент на другой лист или в другую книгу, щелкните ярлычок другого листа или выберите другую книгу и выделите левую верхнюю ячейку области вставки.

Примечание: В Excel вырезанные или скопированные ячейки выделяются анимированной пунктирной рамкой. Чтобы снять это выделение, нажмите клавишу ESC.

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

Выделите строки или столбцы, которые вы хотите переместить или скопировать.
Выполните одно из указанных ниже действий.
- Вырезать и заменить
  Наведите указатель мыши на границу выделенной области. Когда указатель примет вид указателя перемещения , перетащите строки или столбцы в нужное место. Excel выдаст предупреждение, если вы собираетесь заменить столбец. Нажмите кнопку Отмена , чтобы избежать замены.
- Копирование и замена Удерживая нажатой клавишу CTRL, наведите указатель на границу выделенного фрагмента. Когда указатель мыши примет вид указателя копирования , перетащите строки или столбцы в нужное место. Excel не предупреждает вас, если вы собираетесь заменить столбец. Если вы не хотите заменять строку или столбец, нажмите клавиши CTRL+Z.
- Вырезать и вставить Удерживая нажатой клавишу SHIFT, наведите указатель на границу выделенного фрагмента. Когда указатель примет вид указателя перемещения , перетащите строки или столбцы в нужное место.
- Копирование и вставка Удерживая нажатой клавишу SHIFT и CTRL, наведите указатель на границу выделенного фрагмента. Когда указатель примет вид указателя перемещения , перетащите строки или столбцы в нужное место.
Примечание: Убедитесь, что во время операции перетаскивания удерживаете нажатой клавишу CTRL или SHIFT. Если вы отпустите клавиши CTRL или SHIFT перед нажатием кнопки мыши, вы будете перемещать строки или столбцы вместо их копирования.

Примечание: С помощью мыши невозможно переместить или скопировать несмежные строки или столбцы.

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

Выделите строки или столбцы, которые вы хотите переместить или скопировать.
На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить и выберите команду Выделить группу ячеек.
В группе Выбор щелкните элемент Только видимые ячейки и нажмите кнопку ОК.
На вкладке Главная в группе Буфер обмена щелкните Копировать или нажмите клавиши CTRL+C. .
Выделите левую верхнюю ячейку области вставки.

Совет: Чтобы переместить или скопировать выделенный фрагмент на другой лист или в другую книгу, щелкните ярлычок другого листа или выберите другую книгу и выделите левую верхнюю ячейку области вставки.
На вкладке Главная в группе Буфер обмена щелкните Вставить или нажмите клавиши CTRL+V.

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

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

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

Выделите строки или столбцы, которые вы хотите переместить или скопировать.
На вкладке Главная в группе Буфер обмена щелкните Копировать или нажмите клавиши CTRL+C.
Выделите левую верхнюю ячейку области вставки.
На вкладке Главная в группе Буфер обмена щелкните стрелку под кнопкой Вставить и выберите вариант Специальная вставка.
Установите флажок пропускать пустые ячейки.

Дважды щелкните ячейку с данными, которые нужно переместить или скопировать. Вы также можете изменять и выбирать данные ячейки в строка формул.
Выделите строки или столбцы, которые вы хотите переместить или скопировать.
На вкладке Главная в группе Буфер обмена выполните одно из указанных ниже действий.
- Чтобы переместить выделенный фрагмент, нажмите кнопку Вырезать или нажмите клавиши CTRL+X.
- Чтобы скопировать выделенный фрагмент, нажмите кнопку Копировать или нажмите клавиши CTRL+C.
Щелкните в том месте ячейки, куда нужно вставить символы, или дважды щелкните другую ячейку для перемещения или копирования в нее данных.
На вкладке Главная в группе Буфер обмена щелкните Вставить или нажмите клавиши CTRL+V.
Нажмите клавишу ВВОД.

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

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

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

Последовательность действий

Выделите строки или столбцы, которые вы хотите переместить или скопировать.
На вкладке Главная в группе Буфер обмена щелкните Копировать или нажмите клавиши CTRL+C.
Выделите левую верхнюю ячейку области вставки или ту ячейку, куда нужно вставить значение, формат ячейки или формулу.
На вкладке Главная в группе Буфер обмена щелкните стрелку под кнопкой Вставить и выполните одно из следующих действий:
- чтобы вставить только значения, выберите вариант значения;
- чтобы вставить только форматы ячеек, выберите вариант Форматирование;
- чтобы вставить только формулы, выберите вариант формулы.

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

Выделите строки или столбцы, которые вы хотите переместить или скопировать.
На вкладке Главная в группе Буфер обмена выполните одно из указанных ниже действий.
- Чтобы переместить ячейки, нажмите кнопку Вырезать или нажмите клавиши CTRL+X.
- Чтобы скопировать ячейки, щелкните Копировать или нажмите клавиши CTRL+C.
Выделите левую верхнюю ячейку области вставки.

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

Выделите ячейку, строку или столбец, которые нужно переместить или скопировать.
Выполните одно из указанных ниже действий.
- Чтобы переместить строки или столбцы, на вкладке Главная в группе Буфер обмена нажмите кнопку Вырезать или нажмите клавиши CTRL+X.
- Чтобы скопировать строки или столбцы, на вкладке Главная в группе Буфер обмена щелкните Копировать или нажмите клавиши CTRL+C.
Щелкните правой кнопкой мыши строку или столбец снизу или справа от того места, куда необходимо переместить или скопировать выделенный элемент, а затем выполните одно из указанных ниже действий.
- Чтобы переместить строки или столбцы, выберите в контекстном меню команду Вставить вырезанные ячейки.
- Чтобы скопировать строки или столбцы, выберите в контекстном меню команду Вставить скопированные ячейки.
Совет: Чтобы переместить или скопировать выделенный фрагмент на другой лист или в другую книгу, щелкните ярлычок другого листа или выберите другую книгу и выделите левую верхнюю ячейку области вставки.

Выделите строки или столбцы, которые вы хотите переместить или скопировать.
Выполните одно из указанных ниже действий.
- Вырезать и вставить
  Наведите указатель мыши на границу выделенной области. Когда указатель становится указателем руки , перетащите строку или столбец в другое расположение.
- Вырезать и заменить Удерживая нажатой клавишу SHIFT, наведите указатель на границу выделенного фрагмента. Когда указатель становится указателем перемещения , перетащите строку или столбец в другое место. Excel выдаст предупреждение, если вы собираетесь заменить строку или столбец. Нажмите кнопку Отмена , чтобы избежать замены.
- Копирование и вставка Удерживая нажатой клавишу CTRL, наведите указатель на границу выделенного фрагмента. Когда указатель становится указателем перемещения , перетащите строку или столбец в другое место.
- Копирование и замена Удерживая нажатой клавишу SHIFT и CTRL, наведите указатель на границу выделенного фрагмента. Когда указатель становится указателем перемещения , перетащите строку или столбец в другое место. Excel выдаст предупреждение, если вы собираетесь заменить строку или столбец. Нажмите кнопку Отмена , чтобы избежать замены.
Примечание: Убедитесь, что во время операции перетаскивания удерживаете нажатой клавишу CTRL или SHIFT. Если вы отпустите клавиши CTRL или SHIFT перед нажатием кнопки мыши, вы будете перемещать строки или столбцы вместо их копирования.

Примечание: С помощью мыши невозможно переместить или скопировать несмежные строки или столбцы.

Дважды щелкните ячейку с данными, которые нужно переместить или скопировать. Вы также можете изменять и выбирать данные ячейки в строка формул.
Выделите строки или столбцы, которые вы хотите переместить или скопировать.
На вкладке Главная в группе Буфер обмена выполните одно из указанных ниже действий.
- Чтобы переместить выделенный фрагмент, нажмите кнопку Вырезать или нажмите клавиши CTRL+X.
- Чтобы скопировать выделенный фрагмент, нажмите кнопку Копировать или нажмите клавиши CTRL+C.
Щелкните в том месте ячейки, куда нужно вставить символы, или дважды щелкните другую ячейку для перемещения или копирования в нее данных.
На вкладке Главная в группе Буфер обмена щелкните Вставить или нажмите клавиши CTRL+V.
Нажмите клавишу ВВОД.

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

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

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

Последовательность действий

Выделите строки или столбцы, которые вы хотите переместить или скопировать.
На вкладке Главная в группе Буфер обмена щелкните Копировать или нажмите клавиши CTRL+C.
Выделите левую верхнюю ячейку области вставки или ту ячейку, куда нужно вставить значение, формат ячейки или формулу.
На вкладке Главная в группе Буфер обмена щелкните стрелку под кнопкой Вставить и выполните одно из следующих действий:
- Чтобы вставить только значения, щелкните Вставить значения.
- Чтобы вставить только форматы ячеек, нажмите кнопку Вставить форматирование.
- Чтобы вставить только формулы, нажмите кнопку Вставить формулы.

Вы можете перемещать или копировать выбранные ячейки, строки и столбцы с помощью мыши и транспонирования.

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

Задача	Необходимые действия
Перемещение ячеек	Перетащите ячейки в другое место.
Копирование ячеек	Удерживая нажатой клавишу OPTION, перетащите ячейки в другое место.

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

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

Задача	Необходимые действия
Перемещение строк или столбцов	Перетащите строки или столбцы в другое место.
Копирование строк или столбцов	Удерживая нажатой клавишу OPTION, перетащите строки или столбцы в другое место.
Перемещение или копирование данных между существующими строками или столбцами	Удерживая нажатой клавишу SHIFT, перетащите строку или столбец между существующими строками или столбцами. Excel выделяет место для новой строки или столбца.

Скопируйте строки или столбцы, которые требуется транспонировать.
Выберите целевую ячейку (первую ячейку строки или столбца, в которую требуется вставить данные) для строк или столбцов, которые вы преобразуете.
На вкладке Главная в разделе Изменить щелкните стрелку рядом с полем Вставить и выберите пункт Транспонировать.

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

См. также

Вставка строк, столбцов или ячеек

Нужна дополнительная помощь?

Источник

Формула ЗАМЕНИТЬ и ПОДСТАВИТЬ для текста и чисел

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

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

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

Как работает функция ЗАМЕНИТЬ

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

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

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

Старый_текст — исходный текст (или ссылка на ячейку с исходным текстом), в котором вы хотите поменять некоторые символы.
Начальная_позиция — позиция первого символа в старый_текст, начиная с которого вы хотите сделать замену.
Число_знаков — количество символов, которые вы хотите заместить новыми.
Новый_текст – текст замены.

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

=ЗАМЕНИТЬ(«кит»;2;1;»о»)

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

=ЗАМЕНИТЬ(А2;2;1;»о»)

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

Использование функции ЗАМЕНИТЬ с числами

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

=ЗАМЕНИТЬ(A1; 9; 4; «2023»)

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

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

=ЗАМЕНИТЬ(A1;3;2;»23″)

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

Примечание. Формула ЗАМЕНИТЬ всегда возвращает текстовую строку, а не число. На скриншоте выше обратите внимание на выравнивание по левому краю возвращаемого текстового значения в ячейке B1 и сравните его с исходным числом, выровненным по правому краю в A1. А поскольку это текст, вы не сможете использовать его в других вычислениях, пока не преобразуете его обратно в число, например, умножив на 1 или используя любой другой метод, описанный в статье Как преобразовать текст в число.

Как заменить часть даты

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

Например, у вас есть дата в A3, скажем, 15 июля 1992г., и вы хотите изменить «июль» на «май». Итак, вы пишете формулу ЗАМЕНИТЬ(A3; 4; 3; «Май»), которая предписывает Excel поменять 3 символа в ячейке A3, начиная с четвертого. Мы получили следующий результат:

Почему так? Потому что «15-июл-92» — это только визуальное представление базового серийного номера (33800), представляющего дату. Итак, наша формула замены заменяет цифры начиная с четвертой (а это два нуля) в указанном выше числе на текст «Май» и возвращает в результате текстовую строку «338Май».

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

=ЗАМЕНИТЬ(ТЕКСТ(A3; «дд-ммм-гг»); 4; 3; «Май»)

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

=ДАТАЗНАЧ(ЗАМЕНИТЬ(ТЕКСТ(A3; «дд-ммм-гг»); 4; 3; «Май»))

Как заменить сразу несколько букв или слов

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

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

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

=ЗАМЕНИТЬ(A3;4;0;»-«)

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

А теперь нам нужно вставить еще один дефис в восьмую позицию. Для этого вы помещаете приведенную выше формулу в еще одну функцию Excel ЗАМЕНИТЬ. Точнее, вы встраиваете её в аргумент старый_текст другой функции, чтобы вторая функция ЗАМЕНИТЬ обрабатывала значение, возвращаемое первой формулой, а не первоначальное значение из ячейки А3:

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;4;0;»-«);8;0;»-«)

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

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

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;3;0;»/»);6;0;»/»)

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

=ДАТАЗНАЧ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;3;0;»/»);6;0;»/»))

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

Например, вы можете попробовать 3 вложенные функции ЗАМЕНИТЬ, чтобы число отображалось как дата и время:

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(ЗАМЕНИТЬ(A3;3;0;»/»);6;0;»/»);9;0;» «);12;0;»:»)

Как заменить текст в разных местах

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

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

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

=НАЙТИ(«@abc»; A3)

Затем вставьте указанную выше функцию НАЙТИ в аргумент начальная_позиция формулы ЗАМЕНИТЬ:

=ЗАМЕНИТЬ(A3; НАЙТИ(«@abc»;A3); 4; «@bca»)

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

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

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

=ЕСЛИОШИБКА(ЗАМЕНИТЬ(A3; НАЙТИ(«@abc»;A3); 4; «@bca»);A3)

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

Заменить заглавные буквы на строчные и наоборот

Еще один полезный пример – заменить первую строчную букву в ячейке на прописную (заглавную). Всякий раз, когда вы имеете дело со списком имен, товаров и т.п., вы можете использовать приведенную ниже формулу, чтобы изменить первую букву на ЗАГЛАВНУЮ. Ведь названия товаров могут быть записаны по-разному, а в списках важно единообразие.

Таким образом, нам нужно заменить первый символ в тексте на заглавную букву. Используем формулу

=ЗАМЕНИТЬ(СТРОЧН(A3);1;1;ПРОПИСН(ЛЕВСИМВ(A3;1)))

Как видите, эта формула сначала заменяет все буквы в тексте на строчные при помощи функции СТРОЧН, а затем первую строчную букву меняет на заглавную (прописную).

Быть может, это будет полезно.

Описание функции ПОДСТАВИТЬ

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

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

ПОДСТАВИТЬ(текст, старый_текст, новый_текст, [номер_вхождения])

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

Текст – исходный текст, в котором вы хотите заменить слова либо отдельные символы. Может быть тестовой строой, ссылкой на ячейку или же результатом вычисления другой формулы.
Старый_текст – что именно вы хотите заменить.
Новый_текст – новый символ или слово для замены старого_текста.
Номер_вхождения — какой по счёту экземпляр старый_текст вы хотите заменить. Если этот параметр опущен, все вхождения старого текста будут заменены новым текстом.

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

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

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

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

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

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

=ПОДСТАВИТЬ(A3;» «;»»)

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

=ПОДСТАВИТЬ(A3;»Х»;»Y»)

Замена нескольких значений одной формулой

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

Предположим, у вас есть текстовая строка типа « пр1, эт1, з1 » в ячейке A3, где «пр» означает «Проект», «эт» означает «этап», а «з» означает «задача». Вы хотите заместить три этих кода их полными эквивалентами. Для этого вы можете написать 3 разные формулы подстановки:

=ПОДСТАВИТЬ(A3;»пр»;»Проект «)

=ПОДСТАВИТЬ(A3;»эт»;»Этап «)

=ПОДСТАВИТЬ(A3;»з»;»Задача «)

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

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A3;»пр»;»Проект «); «эт»;»Этап «);»з»;»Задача «)

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

Другие полезные применения функции ПОДСТАВИТЬ:

Замена неразрывных пробелов в ячейке Excel обычными
Убрать пробелы в числах
Удалить перенос строки в ячейке
Подсчитать определенные символы в ячейке

Что лучше использовать – ЗАМЕНИТЬ или ПОДСТАВИТЬ?

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

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

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

Источник

VladKomp

Пользователь

Сообщений: 18
Регистрация: 25.12.2012

Добрый вечер, форумчане!
Прошу помощи.
Мы получаем результаты ЕГЭ в таком виде, что провести анализ результатов очень затруднительно. Где-то стоят плюсики, где-то минусики, а где-то баллы от 1 до 14. Для маленьких массивов можно сделать это по принципу «закат солнца вручную», но часто проскакивают ошибки.
Файлик с примером на Листах 1 и 2 прикрепляю.
Очень хотелось бы все преобразования сделать с помощью стандартных формул…

Igor67 Пользователь Сообщений: 3726 Регистрация: 21.12.2012	VladKomp, смотрел Ваш файл, постановка задачи не совпадает с примером А если пользовательская функция на примере Вашего макроса?

VladKomp Пользователь Сообщений: 18 Регистрация: 25.12.2012	Стар стал — плох стал… Спасибо. Прикрепляю нужный.

Nic70y Пользователь Сообщений: 3289 Регистрация: 22.12.2012 Win7 MSO 2013	Лист 1: =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(B3;»+»;»1 «);»-«;»0 «)) Лист 2: Ctrl+H Найти () Заменить на Пробел с Листом 1 можно поступить также* Изменено: Nic70y — 07.06.2013 00:03:20

VladKomp

Пользователь

Сообщений: 18
Регистрация: 25.12.2012

Спасибо, Nic70y. Формула работает хорошо, только проблема в том, что после разноски символов по ячейкам не определяется сумма баллов из-за того, что в ячейках появляются пробелы — я так думаю. Удаляю пробелы из ячеек — исчезает разбиение. Замкнутый круг.
Пояснее проблему видно в файле.

The_Prist Пользователь Сообщений: 14181 Регистрация: 15.09.2012 Профессиональная разработка приложений для MS Office	=—СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(» «&$C4;» «;ПОВТОР(» «;99));СТОЛБЕЦ(A1)*99;99)) Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

VladKomp Пользователь Сообщений: 18 Регистрация: 25.12.2012	Шик! Самый высший класс! Впервые встречаюсь с таким использованием символа! Спасибо! К формуле я еще добавил функцию ЕСЛИ и все стало чудненько.

Igor67 Пользователь Сообщений: 3726 Регистрация: 21.12.2012	VladKomp, еще можно было к значению функции прибавить ноль или умножить его на 1. Эксель при этом проведет преобразование типа данных.

Nic70y Пользователь Сообщений: 3289 Регистрация: 22.12.2012 Win7 MSO 2013	#9 08.06.2013 00:44:06 Зачем такие сложности (см. файл) или универсальная с D4 =ЕСЛИОШИБКА(—ЕСЛИ(ЕЧИСЛО(ПОИСК(«(«;$B4));ПСТР($B4;СТОЛБЕЦ(A1)*4-3;1);ЕСЛИ(ПСТР($B4;СТОЛБЕЦ(A1);1)=»+»;1;ЕСЛИ(ПСТР($B4;СТОЛБЕЦ(A1);1)=»-«;0;ПСТР($B4;СТОЛБЕЦ(A1);1))));»») Прикрепленные файлы 999778.xlsx (11.64 КБ) Изменено: Nic70y — 08.06.2013 01:04:40

Источник

Содержание

CopyFile method
Syntax
Remarks
See also
Support and feedback
Метод CopyFile
Синтаксис
Замечания
См. также
Поддержка и обратная связь
VBA Excel. Копирование и перемещение файлов
Копирование файлов
Метод CopyFile
Синтаксис
Параметры
Примеры
Метод CopyFolder
Синтаксис
Замечания
См. также
Поддержка и обратная связь
FileCopy в VBA — Копировать файл из одного места в другое с помощью VBA
Excel VBA FileCopy
Синтаксис FileCopy в Excel VBA
Как скопировать файл в Excel VBA?
Пример № 1 — FileCopy в VBA
Пример №2 — FileCopy в VBA
Плюсы FileCopy в VBA
То, что нужно запомнить
Рекомендуемые статьи

CopyFile method

Copies one or more files from one location to another.

Syntax

object.CopyFile source, destination, [ overwrite ]

The CopyFile method syntax has these parts:

Part	Description
object	Required. The object is always the name of a FileSystemObject.
source	Required. Character string file specification, which can include wildcard characters, for one or more files to be copied.
destination	Required. Character string destination where the file or files from source are to be copied. Wildcard characters are not allowed.
overwrite	Optional. Boolean value that indicates if existing files are to be overwritten. If True, files are overwritten; if False, they are not. The default is True. Note that CopyFile will fail if destination has the read-only attribute set, regardless of the value of overwrite.

Wildcard characters can only be used in the last path component of the source argument. For example, you can use:

But you can’t use:

If source contains wildcard characters, or destination ends with a path separator (****), it is assumed that destination is an existing folder in which to copy matching files. Otherwise, destination is assumed to be the name of a file to create. In either case, three things can happen when an individual file is copied:

If destination does not exist, source gets copied. This is the usual case.

If destination is an existing file, an error occurs if overwrite is False. Otherwise, an attempt is made to copy source over the existing file.

If destination is a directory, an error occurs.

An error also occurs if a source using wildcard characters doesn’t match any files. The CopyFile method stops on the first error it encounters. No attempt is made to roll back or undo any changes made before an error occurs.

Files copied to a new destination path will keep the same file name. To rename the copied file, simply include the new file name in the destination path. For example, this will copy the file to a new location and the file in the new location will have a different name:

Метод CopyFile

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

Синтаксис

object. Источник CopyFile , назначение, [ перезапись ]

Синтаксис метода CopyFile содержит следующие элементы:

Part	Описание
object	Обязательно. Объект всегда является именем Объекта FileSystemObject.
источник	Обязательно. Спецификация файла строки символов, которая может содержать подстановочные знаки (для одного или нескольких копируемых файлов).
destination	Обязательно. Символьное строковое обозначение конечного расположения, в которое необходимо скопировать файл или файлы из элемента source. Использование подстановочных знаков не допускается.
Перезаписать	Необязательный параметр. Логическое значение, которое указывает, требуется ли перезапись существующих файлов. Если установлено значение True, файлы будут перезаписаны; если задано значение False, перезапись не выполняется. По умолчанию используется значение True. Необходимо учитывать, что функция CopyFile не выполняется, если для элемента destination установлено свойство «только чтение», независимо от значения элемента overwrite.

Замечания

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

Однако нельзя использовать:

Если источник содержит подстановочные знаки или назначение заканчивается разделителем пути (****), предполагается, что назначение — это существующая папка, в которую копируются соответствующие файлы. В противном случае элемент, destination предположительно является именем создаваемого файла. В любом случае при копировании отдельного файла могут произойти три вещи:

Если элемент destination не существует, выполняется копирование элемента source. Это типичный случай.

Если элемент destination представляет собой существующий файл, возникает ошибка, если элементу overwrite присвоено значение False. В противном случае выполняется попытка копирования элемента source в существующий файл.

Если destination является каталогом, происходит ошибка.

Ошибка также возникает в том случае, если элемент source, который использует подстановочные знаки, не соответствует ни одному из файлов. Выполнение метода CopyFile останавливается при обнаружении первой ошибки. До возникновения ошибки попыток отката или отмены каких-либо изменений не выполняется.

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

См. также

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

Источник

VBA Excel. Копирование и перемещение файлов

Копирование и перемещение файлов в VBA Excel с помощью методов CopyFile и MoveFile объекта FileSystemObject. Синтаксис, параметры, примеры.

Копирование файлов

Метод CopyFile

Синтаксис

Параметры

Параметр	Описание
object	Переменная, возвращающая объект FileSystemObject. Обязательный параметр.
source	Строковое выражение, задающее полное имя файла, который требуется скопировать в другое расположение. Для копирования нескольких файлов используются подстановочные знаки. Обязательный параметр.
destination	Строковое выражение, задающее конечное расположение, куда требуется скопировать файл (файлы) из элемента source. Подстановочные знаки не допускаются. Обязательный параметр.
overwrite	Логическое значение, которое указывает, требуется ли перезаписывать существующие файлы в конечном расположении. True – файлы будут перезаписаны, False – перезапись не выполняется. Необязательный параметр, по умолчанию – True.

Если копируемый файл с полным именем source не существует, будет сгенерирована ошибка.

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

Примеры

Пример 1
Копирование одного файла в другое расположение с проверкой его существования:

Источник

Метод CopyFolder

Рекурсивно копирует папку из одного места в другое.

Синтаксис

object. Источник CopyFolder , назначение, [ перезапись ]

Синтаксис метода CopyFolder состоит из следующих частей:

Part	Описание
object	Обязательно. Всегда имя объекта FileSystemObject.
источник	Обязательно. Строка знаков, представляющая спецификацию папки, которая может содержать подстановочные знаки для одной или нескольких копируемых папок.
destination	Обязательно. Строка знаков, определяющая место назначения, в которое должны быть скопированы папка и вложенные папки из source. Использование подстановочных знаков запрещено.
Перезаписать	Необязательный параметр. Значение типа Boolean, показывающее, можно ли перезаписывать существующие папки. При значении True файлы перезаписываются, при значении False перезапись файлов не происходит. По умолчанию используется значение True.

Замечания

Однако нельзя использовать:

Если источник содержит подстановочные знаки или назначение заканчивается разделителем пути (), предполагается, что назначение — это существующая папка, в которую копируются соответствующие папки и вложенные папки. В противном случае предполагается, что destination — это имя создаваемой папки. В любом случае при копировании отдельной папки могут произойти четыре вещи:

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

Если destination является существующим файлом, происходит ошибка.

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

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

Ошибка также возникает, если значение source, заданное с использованием подстановочных знаков, не соответствует никаким папкам.

Метод CopyFolder останавливается при первой обнаруженной ошибке. Попытка отката изменений, выполненных до появления ошибки, не выполняется.

См. также

Поддержка и обратная связь

Источник

FileCopy в VBA — Копировать файл из одного места в другое с помощью VBA

Excel VBA FileCopy

Есть много способов скопировать или переместить файл из одной папки в другую. Мы можем сделать это вручную, скопировав и вырезав значок файла и вставив его в папку назначения. Но когда дело доходит до автоматизации этого процесса копирования файла, мы можем сделать это и в VBA. Чтобы скопировать файл, у нас есть функция, доступная в VBA, которая называется «Копирование файла».

Синтаксис FileCopy в Excel VBA

Применить File Copy в VBA очень легко. Синтаксис File Copy выглядит так, как показано ниже.

File Copy имеет только два компонента для подачи. Которые:

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

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

Как скопировать файл в Excel VBA?

Теперь давайте попробуем с некоторыми примерами копирования файлов VBA в Excel.

Вы можете скачать этот VBA Скопировать файл Excel шаблон здесь — VBA Скопировать файл Excel шаблон

Пример № 1 — FileCopy в VBA

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

Для этого нам понадобится файл любого вида или расширения. Здесь мы рассматриваем файл Excel с расширением xlsx . Как мы видим, мы поместили этот файл Excel в папку, которая легко доступна и называется Test .

Теперь мы скопируем этот файл с помощью FileCopy в указанную ниже папку назначения.

Шаг 1: Для этого перейдите в VBA и откройте раскрывающийся список «Модуль из вставки», как показано ниже.

Шаг 2: Теперь в открытом модуле напишите подкатегорию VBA FileCopy или любое другое имя по вашему выбору.

Код:

Шаг 3: Теперь откройте функцию FileCopy, как показано ниже.

Код:

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

Код:

Шаг 5: В качестве места назначения снова поместите путь в кавычки, затем файл и расширение.

Код:

Шаг 6: Теперь скомпилируйте код и запустите его, нажав на кнопку Play, которая находится под строкой меню. Мы увидим, что файл Excel с именем Test теперь копируется из исходного местоположения в местоположение назначения, как показано ниже. Поскольку в файле ничего не было, размер файла равен 0 КБ .

Пример №2 — FileCopy в VBA

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

Шаг 1: Теперь сначала откройте новый модуль и напишите подкатегорию VBA File Copy, как показано ниже.

Код:

Шаг 2: Сначала определите переменную, в которую мы будем помещать исходное местоположение файла того же файла Excel, который мы использовали в примере-1 в качестве String.

Код:

Шаг 3: Аналогичным образом, нам понадобится еще одна переменная для места назначения.

Код:

Шаг 4: Теперь поместите местоположение в первую определенную переменную, которая является « FirstLocation » вместе с именем файла и его расширением.

Код:

Шаг 5: Аналогичным образом сделайте то же самое для местоположения пункта назначения, используя переменную « SecondLocation», которую мы определили выше.

Код:

Шаг 6: Теперь пришло время использовать функцию FileCopy .

Код:

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

Шаг 7: Итак, здесь мы можем напрямую выбрать эти переменные. Сначала выберите переменную местоположения источника, которая является FirstLocation .

Код:

Шаг 8: Снова аналогичным образом выберите переменную места назначения, которая является SecondLocation, как показано ниже.

Код:

Шаг 9: После этого мы скомпилируем код, нажав функциональную клавишу F8. И если ошибки не найдены, запустите код. Мы увидим, что файл из исходного местоположения копируется и вставляется в папку назначения, как показано ниже.

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

Как мы видим, в файле нет данных, поэтому размер снова отображается как 0 КБ.

И целевой папкой будет папка «Расположение вывода», которая находится в папке «Расположение ввода». Если мы видим внутри файла, нет доступных данных.

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

Код:

Теперь запустите код.

Мы увидим, что файл Test word теперь копируется из папки Input Location в папку Output location с тем же размером, который равен 0 КБ.

Плюсы FileCopy в VBA

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

То, что нужно запомнить

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

Копирование листа в другой документ.

Произведем копирование таблицы в другой документ. Для этого необходимо открыть оба документа.

Далее в документе с таблицей кликнуть правой кнопкой мыши по ярлыку с названием листа и в появившемся контекстном меню выбрать позицию «Переместить или скопировать…».

В появившемся меню указать документ, в который будет скопирован лист. Нажать кнопку «ОК».

При копировании листа с расчетами в другую книгу (другой документ), формулы ссылаются на старый документ.

Как быстро избавится от ссылок на старый документ в формулах.

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

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

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

Вызвать поиск можно нажав сочетание клавиш «ctrl+F» (в русской раскладке «ctrl+А» ), либо воспользоваться пиктограммой «Найти и выделить…» на главной вкладке.

В появившейся форме для поиска нужно выбрать вкладку «Заменить». На вкладке «Заменить» есть 2 поля. Первое поле — то значение, которое ищем, второе поле – то, на что меняем.

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

Источник

Применение специальной вставки в Microsoft Excel

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

Работа со специальной вставкой

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

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

«Копировать»

Также, вместо вышеописанной процедуры можно, находясь во вкладке «Главная», нажать на значок «Копировать», который размещается на ленте в группе «Буфер обмена».

Вставка («Вставить», «Транспонировать», «Формулы», «Формулы и форматы чисел», «Без рамок», «Сохранить ширину столбцов оригинала» и «Сохранить исходное форматирование»);
Вставить значения («Значение и исходное форматирование», «Значения» и «Значения и форматы чисел»);
Другие параметры вставки («Форматирование», «Рисунок», «Вставить связь» и «Связанный рисунок»).

Способ 1: работа со значениями

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

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

Способ 2: копирование формул

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

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

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

«Формулы и форматы чисел»

Способ 3: перенос форматирования

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

Копируем исходную таблицу.

Способ 4: копирование таблицы с сохранением размера столбцов

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

Сначала любым из вышеназванных способов копируем исходную таблицу.
После запуска уже привычного нам меню выбираем значение «Сохранить ширину столбцов оригинала».

Способ 5: вставка рисунка

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

Копируем объект с помощью обычных инструментов копирования.

В окне специальной вставки подобную операцию выполнить нельзя.

Способ 6: копирование примечаний

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

Ctrl+C

Способ 7: транспонирование таблицы

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

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

Способ 8: использование арифметических действий

Посредством описываемого нами инструмента в Excel также можно выполнять распространенные арифметические действия:

Сложение;
Умножение;
Вычитание;
Деление.

Посмотрим, как применяется данный инструмент на примере умножения.

Ctrl+C

По такому же принципу можно выполнять деление, сложение и вычитание. Только для этого в окне нужно будет переставить переключатель соответственно в позицию «Разделить», «Сложить» или «Вычесть». В остальном все действия аналогичны вышеописанным манипуляциям.

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

Мы рады, что смогли помочь Вам в решении проблемы.

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

Функция ПОДСТАВИТЬ при условии подставляет значение

Пример 1. В результате расчетов, произведенных в некотором приложении, были получены некоторые значения, записанные в таблицу Excel. Некоторые величины рассчитать не удалось, и вместо числового представления была сгенерирована ошибка “NaN”. Необходимо заменить все значения “NaN” на число 0 в соответствующих строках.

Для замены и подстановки используем рассматриваемую формулу в качестве массива. Вначале выделим диапазон ячеек C2:C9, затем введем формулу через комбинацию Ctrl+Shift+Enter:

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

B2:B9 – диапазон ячеек, в которых требуется выполнить замену части строки;
“NaN” – фрагмент текста, который будет заменен;
0 – фрагмент, который будет вставлен на место заменяемого фрагмента.

Для подстановки значений во всех ячейках необходимо нажать Ctrl+Shift+Enter, чтобы функция была выполнена в массиве. Результат вычислений:

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

Автозамена значения в текстовых ячейках с помощью функции ПОДСТАВИТЬ

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

Для выполнения заданного условия используем формулу:

Примечание: в данном примере ПОДСТАВИТЬ также используется в массиве Ctrl+Shift+Enter.

В результате получим:

Формула с макросом регулярного выражения и функция ПОДСТАВИТЬ

Пример 3. При составлении таблицы из предыдущего примера была допущена ошибка: все номера домов на улице Никольская должны быть записаны как «№№-Н», где №№ — номер дома. Как быстро исправить ошибку?

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

Открыть редактор макросов (Ctrl+F11).
Вставить исходный код функции (приведен ниже).
Выполнить данный макрос и закрыть редактор кода.

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

Регулярные выражения могут быть различными. Например, для выделения любого символа из текстовой строки в качестве второго аргумента необходимо передать значение «w», а цифры – «d».

Для решения задачи данного Примера 3 используем следующую запись:

Функция ЕСЛИОШИБКА используется для возврата исходной строки текста (B2), поскольку результатом выполнения функции RegExpExtract(B2;»Никольская») будет код ошибки #ЗНАЧ!, если ей не удалось найти хотя бы одно вхождение подстроки «Никольская» в строке B2.
Если результат выполнения сравнения значений RegExpExtract(B2;»Никольская»)=»Никольская» является ИСТИНА, будет выполнена функция ПОДСТАВИТЬ(B2;RegExpExtract(B2;»d+»);RegExpExtract(B2;»d+»)&»-Н»), где:

a. B2 – исходный текст, содержащий полный адрес;
b. RegExpExtract(B2;»d+») – формула, выделяющая номер дома из строки с полным адресом;
c. RegExpExtract(B2;»d+»)&»-Н» – новый номер, содержащий исходное значение и символы «-Н».

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

Как в excel в текст вставить значение из другой ячейки

Вы можете скопировать и вкопировать определенное содержимое ячейки или атрибуты (например, формулы, форматы, при комментарии и проверку). По умолчанию при использовании значков Копировать и (или + C и +V) копируется все атрибуты. Чтобы выбрать определенный параметр вставки, можно воспользоваться меню Вставить либо выбрать команду Специальная вставка и указать нужный вариант в окне Специальная вставка. В этом случае при вставке содержимого исключаются все атрибуты, кроме выбранных.

Пункты меню «Вставить»

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

На вкладке Главная нажмите кнопку Копировать .

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

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

Что вставляется

Все содержимое и формат ячеек, включая связанные данные.

Формулы и форматы чисел

Только формулы и форматы чисел.

Сохранить исходное форматирование

Все содержимое и формат ячеек.

Все содержимое и формат ячеек, кроме границ ячеек.

Сохранить ширину столбцов оригинала

Только атрибуты ширины столбцов.

Транспонировать

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

Вставить значения

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

Значения и форматы чисел

Только значения и форматы чисел.

Значения и исходное форматирование

Только значения, цвет и размер шрифта.

Форматирование

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

Вставить связь

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

Вставить как рисунок

Скопированные данные как изображение.

Связанный рисунок

Скопированные данные как изображение со ссылкой на исходные ячейки (изменения, внесенные в исходных ячейках, отражаются и в ячейках, куда вставлено изображение).

ширины столбцов

Вставка ширины столбца или диапазона столбцов в другой столбец или диапазон столбцов.

Объединить условное форматирование

Условное форматирование из скопированных ячеек объединяется с условным форматированием в области вставки.

Параметры специальной вставки

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

На вкладке Главная нажмите кнопку Копировать .

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

На вкладке Главная щелкните стрелку рядом с кнопкой В таблицуи выберите специальную ветвь.

Выберите нужные параметры.

Параметры вставки

Что вставляется

Все содержимое и формат ячеек, включая связанные данные.

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

Содержимое и формат ячеек.

Вставка только примечаний к ячейкам.

Только правила проверки данных.

с исходной темой

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

Содержимое и формат ячеек, кроме границ ячеек.

ширины столбцов

Атрибуты ширины столбца или диапазона столбцов в другой столбец или диапазон столбцов.

формулы и форматы чисел

Только формулы и форматы чисел.

значения и форматы чисел

Только значения и форматы чисел из выделенных ячеек.

все, объединить условное форматирование

Условное форматирование из скопированных ячеек объединяется с условным форматированием в области вставки.

Параметры операций

Параметры операций позволяют выполнить математические действия со значениями из областей копирования и вставки.

Вставка содержимого скопированной области без математического действия.

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

Вычитание значений из области копирования из значений в области вставки.

Умножение значений из области вставки на значения из области копирования.

Деление значений из области вставки на значения из области копирования.

Доступны и другие параметры:

Пропускать пустые ячейки

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

Транспонировать

Вставить связь

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

Совет: Некоторые параметры доступны как в меню В paste, так и в диалоговом окне Специальная ветвь. Их названия могут слегка различаться, но результат оказывается одинаковым.

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

На панели инструментов Стандартная нажмите кнопку Копировать .

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

На вкладке Главная в области Правканажмите кнопку В таблицуи выберите специальную ветвь.

В диалоговом окке Специальная ветвь в области Ветвьсделайте следующее:

В этом формате включаем все содержимое и формат ячейки, включая связанные данные.

Вставка только формул в том виде, как они вводятся в строку формул.

В этом окте можно вировать только значения, отображаемые в ячейках.

В этом формате можно в нее в форматировать только ячейки.

Вставка только примечаний к ячейкам.

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

с исходной темой

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

В этом формате можно в нее в форматировать все содержимое и содержимое ячейки, кроме границ ячеев.

ширины столбцов

Вставка ширины столбца или диапазона столбцов в другой столбец или диапазон столбцов.

формулы и форматы чисел

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

значения и форматы чисел

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

Объединить условное форматирование

Условное форматирование из скопированных ячеек объединяется с условным форматированием в области вставки.

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

Вставка содержимого скопированной области без математического действия.

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

Вычитание значений из области копирования из значений в области вставки.

Умножение значений из области вставки на значения из области копирования.

Деление значений из области вставки на значения из области копирования.

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

Пропуск пустых страниц

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

Транспонировать

Измените столбцы скопированные данные на строки и наоборот.

Вставить связь

Примечание: Этот параметр доступен только при выборе параметра Все или Все, кроме границ в диалоговом окне «Специальная ветвь»

Совет: В Excel для Mac версии 16.33 или более высокой версии на панель быстрого доступа можно добавить действия «Вставку форматирования», «Вставку формул» и «Вставку значений» или пользовательские сочетания клавиш. Для сочетаний клавиш необходимо назначить сочетание клавиш, которое еще не используется для открытия диалогового окно Специальная вставку.

Источник

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

Содержание

Перенос с помощью опции «Специальная вставка»
Перенос с помощью опции «Специальная вставка» и «Замена»
Перенос с помощью функции «ТРАНСП»
Перенос с помощью преобразования данных в разных версиях Excel
В Excel 2016
В Excel 2013/2010

Перенос с помощью опции «Специальная вставка»

С помощью опции «Специальная вставка» можно делать разные вещи, к примеру, переносить данные.

Допустим, мы имеем такую таблицу:

так, предположим, нам нужно перенести эти данные. Сделаем это с помощью «Специальной вставки».

Выделим нужные ячейки;

Скопируем их (правой кнопкой мышки, «Копировать», либо CTRL + C);

И вставим их, правой кнопкой на ячейку, начиная от которой вы хотите начать вставку скопированной таблицы;

Обязательно отметьте опцию «транспонировать»;

Подтвердите.

Итак, мы перенесли таблицу с сохранением позиций (строк и столбцов).

Используя этот метод, мы также скопируем все функции и форматы ячеек. Но если вам необходимо перенести только содержимое ячеек, в окне «Специальная вставка», выберите соответствующую опцию.

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

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

Перенос с помощью опции «Специальная вставка» и «Замена»

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

Допустим, мы имеем ту же таблицу:

А теперь перенесем эти данные и выстроим связь:

Выделим ячейки;

Скопируем их (CTRL + C, либо правой кнопкой мышки);

Выберите место, куда вы хотите перенести нашу изначальную таблицу;

В открывшемся окошке, щелкните на «Вставить связь» (если есть галочка на опции «транспонировать» её необходимо снять);

Выделите ячейки с новой табличкой (которую мы только что сделали с помощью функции «Специальная вставка») и откройте функцию «Заменить» в «Найти и выделить»;

А теперь, нужно сделать следующее:

В поле «Найти» введите: «=»;

В поле «Заменить на» введите: «!@#» (мы используем «!@#», потому что это уместно в нашем конкретном случае, если для вас эта строка подойдет, это будет отлично, однако, обратите внимание, что вам может понадобиться «своя» строка).

Щелкаем на «Заменить все».

Копируем полученное в предыдущем шаге;

Выбираем удобное место для вставки и жмём правой кнопкой мыши, «Специальная вставка»;

В открывшемся окне, поставьте галочку на опции «транспонировать»;

ОК;

Опять открываем «Найти и заменить», в поле «Найти» пишем: «!@#», а в поле «Заменить на» пишем: «=»;

Таким образом, мы получили новую, связанную со старой, табличку.

Важная информация: Мы использовали, так называемый, «метод ссылок», поэтому в левой верхней ячейке мы получили «0», хотя в первоначальной табличке там ничего не было. Это происходит потому, что ссылка на пустую таблицу возвращает значение «0», вы можете просто удалить его вручную.

Перенос с помощью функции «ТРАНСП»

У этой функции есть как плюсы, так и минусы, рассмотрим их позже.

Допустим, мы имеем ту же табличку:

Сейчас будем вызывать функцию:

Выделите диапазон ячеек, куда будем переносить нашу табличку. Обратите внимание, что вам нужно выделить точно такой же диапазон (имеется в виду столько же строк и столько же столбцов) как и в первоначальной табличке;

Пропишите следующую формулу: «=ТРАНСП(A1:E5)» после того как впишете это, вам необходимо нажать не просто ENTER, а CTRL + SHIFT + ENTER. Это очень важно, так как мы используем диапазон ячеек.

Важная информация:

Так как мы работаем с диапазоном ячеек, чтобы подтвердить введение формулы, вам нужно обязательно нажать CTRL + SHIFT + ENTER;
После вставки, как в прошлом способе, вы не сможете редактировать отдельные части новой таблицы, так как это все результат одной функции «ТРАНСП»;
Эта функция переносит только значения из старых ячеек в новые, формат ячеек скопирован не будет.

Перенос с помощью преобразования данных в разных версиях Excel

Преобразование данных — хорошая функция Excel, ей довольно удобно пользоваться.

Эта функция по умолчанию есть в Excel 2016, но в более старых версиях (2013/2010) её еще не было, поэтому если вы хотите использовать её в старых версиях, нужно будет установить ее как дополнение.

Допустим, мы имеем все ту же таблицу:

Как выполнить перенос данных этим методом:

В Excel 2016

Выделите диапазон ячеек, который необходимо перенести;

В открывшемся окне поставьте галочку на опции «Таблица с заголовками» и нажмите ОК;

Открылся редактор, нам нужно щелкнуть на «Преобразование»;

На параметре «Использовать первую строку в качестве заголовков» щелкните на стрелочку, смотрящую вниз и выберите «Использовать заголовки как первую строку»;

Вернитесь во вкладку «Преобразование»;

Щелкните на опцию «Использовать первую строку в качестве заголовков»;

Щелкните на раздел «Файл» и, из списка, выберите «Закрыть и загрузить».

Левая верхняя ячейка, которая была пустой, получила название «Столбец1», но вы можете просто удалить её. В этом способе можно так сделать.

В Excel 2013/2010

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

Щелкните здесь чтобы установить его (инструкция по установке будет по ссылке).

После установки, перейдите во вкладку «Преобразование данных» -> «Данные Excel» -> «Из таблицы».

Откроется окно, такое же как и в случае использования Excel 2016.

Источник

Бывают ситуации, когда в документе нужно заменить один символ (или группу символов) на другой. Причин может быть множество, начиная от банальной ошибки, и, заканчивая переделкой шаблона или удалением пробелов. Давайте выясним, как быстро заменить символы в программе Microsoft Excel.

Способы замены символов в Excel

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

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

Поиск с заменой

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

«Найти и выделить»

«Главная»

«Редактирование»

«Заменить»

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

Как видим, в нижней части окна имеются кнопки замены – «Заменить всё» и «Заменить», и кнопки поиска — «Найти всё» и «Найти далее». Жмем на кнопку «Найти далее».

После этого производится поиск по документу искомого слова. По умолчанию, направление поиска производится построчно. Курсор останавливается на первом же результате, который совпал. Для замены содержимого ячейки жмем на кнопку «Заменить».

Чтобы продолжить поиск данных, опять жмем на кнопку «Найти далее». Тем же способом меняем следующий результат, и т.д.

Можно найти все удовлетворяющие запросу результаты сразу.

После ввода поискового запроса и заменяющих символов жмем на кнопку «Найти все».

Производится поиск всех релевантных ячеек. Их список, в котором указано значение и адрес каждой ячейки, открывается в нижней части окна. Теперь можно кликнуть по любой из ячеек, в которой мы хотим выполнить замену, и нажать на кнопку «Заменить».

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

Автоматическая замена

Можно выполнить автоматическую замену нажатием всего одной кнопки. Для этого после ввода заменяемых значений, и значений, на которые производится замена, жмем кнопку «Заменить все».

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

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

Дополнительные параметры

Кроме того, существует возможность расширенного поиска и замены по дополнительным параметрам.

Находясь во вкладке «Заменить», в окне «Найти и заменить» жмем на кнопку Параметры.

Открывается окно дополнительных параметров. Оно практически идентично окну расширенного поиска. Единственное отличие – присутствие блока настроек «Заменить на».

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

Также, можно указать среди ячеек какого формата будет производиться поиск. Для этого нужно кликнуть по кнопке «Формат» напротив параметра «Найти».

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

Единственной настройкой значения для вставки будет являться все тот же формат ячеек. Для выбора формата вставляемого значения жмем на одноименную кнопку напротив параметра «Заменить на…».

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

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

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

Не забываем в поля «Найти» и «Заменить на…» вписать соответствующие значения. Когда все настройки указаны, выбираем способ выполнения процедуры. Либо жмем на кнопку «Заменить все», и замена происходит автоматически, согласно введенным данным, или же жмем на кнопку «Найти все», и отдельно производим замену в каждой ячейке по тому алгоритму, о котором писалось уже выше.

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

Поиск или замена текста и чисел на листе

В этом курсе:

Поиск или замена текста и чисел на листе

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

Поиск

Чтобы найти какой-либо элемент, нажмите клавиши CTRL + Fили перейдите на вкладку главная > редактирование> найти & выберите > найти.

Примечание: В приведенном ниже примере мы настроили кнопку » параметры >> » для отображения всего диалогового окна «найти». По умолчанию отображаются скрытые параметры.

В поле найти: введите текст или числа, которые вы хотите найти, или щелкните стрелку в поле найти: и выберите в списке последний элемент поиска.

Советы: Вы можете использовать подстановочные знаки — вопросительный знак ( ?), звездочку ( *), тильду (

) — в критериях поиска.

Используйте вопросительный знак (?), чтобы найти любой одиночный символ (например, «s», «Кот» и «Set».

Используйте звездочку (*), чтобы найти любое количество символов (например, s * d — «Sad» и «запущено»).

, чтобы найти вопросительные знаки, звездочки или другие знаки, например ан 91

? Поиск слова «Ан 91?».

Чтобы выполнить поиск, нажмите кнопку найти все или Найти далее .

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

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

Вне Для поиска данных на листе или во всей книге выберите лист или Книга.

См Вы можете выбрать вариант поиска по строкам (по умолчанию) или по столбцам.

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

Примечание: Формулы, значения, заметки и Примечания доступны только на вкладке » Поиск «. на вкладке заменить доступны только формулы .

С учетом регистра . Установите этот флажок, если вы хотите искать данные с учетом регистра.

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

Если вы хотите найти текст или числа с определенным форматированием, нажмите кнопку Формати выберите необходимые параметры в диалоговом окне Найти формат .

Совет: Чтобы найти ячейки, точно соответствующие определенному формату, можно удалить все условия в поле Найти, а затем выбрать ячейку с нужным форматированием в качестве примера. Щелкните стрелку рядом с кнопкой Формат, выберите пункт Выбрать формат из ячейки, а затем щелкните ячейку с форматированием, которое требуется найти.

Заменить

Чтобы заменить текст или числа, нажмите клавиши CTRL + Hили перейдите на вкладку главная > редактирование> найти & выберите > заменить.

Советы: Вы можете использовать подстановочные знаки — вопросительный знак ( ?), звездочку ( *), тильду (

) — в критериях поиска.

Используйте вопросительный знак (?), чтобы найти любой одиночный символ (например, «s», «Кот» и «Set».

Используйте звездочку (*), чтобы найти любое количество символов (например, s * d — «Sad» и «запущено»).

, чтобы найти вопросительные знаки, звездочки или другие знаки, например ан 91

? Поиск слова «Ан 91?».

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

Нажмите Заменить все или Заменить.

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

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

Вне Для поиска данных на листе или во всей книге выберите лист или Книга.

См Вы можете выбрать вариант поиска по строкам (по умолчанию) или по столбцам.

С учетом регистра . Установите этот флажок, если вы хотите искать данные с учетом регистра.

Существует два различных способа поиска и замены текста или чисел на компьютере Mac. Первый — Использование диалогового окна найти & заменить . Вторая — использовать строку поиска на ленте.

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Инструмент Найти и заменить в Excel

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

Чтобы воспользоваться диалоговым окном Найти и заменить, выделите диапазон ячеек, в котором вы хотите произвести поиск (если будет выделена только одна ячейка, Excel будет искать во всем листе). Перейдите по вкладке Главная в группу Редактирование, выберите Найти и выделить -> Найти (или нажмите сочетание клавиш Ctrl + F).

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

Поиск информации

Введите искомую информацию в поле Найти и укажите требуемые параметры поиска:

Выберите место поиска в выпадающем меню Искать (на листе, в книге)
В выпадающем меню Просматривать, укажите Excel вариант просмотра по строкам или по столбцам.
Задайте Областьпоиска в формулах, значениях или примечаниях
С помощью опций Учитывать регистр и Ячейка целиком, вы можете указать, следует ли производить поиск с учетом регистра символов и ищется ли все содержимое ячейки или только фрагмент.

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

Замена информации

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

Щелкните по кнопке Найти далее, чтобы Excel выделил первую совпавшую ячейку, затем Заменить для замены. После щелчка, Excel выделит следующую совпавшую ячейку. Чтобы пропустить замещение, щелкните Найти далее. Для замены всех элементов без проверки, щелкните Заменить все. Если замещение пошло не по плану, вы можете отменить действие с помощью сочетания клавиш Ctrl + Z.

Поиск форматов

Помимо текста вы можете найти ячейки отформатированные определенным образом. Как вариант, вы также можете заменить форматирование ячейки на другое. Предположим, что необходимо определить все ячейки с жирным текстом и заменить их на жирный и курсив. Перейдите по вкладке Главная в группу Редактирование, выберите Найти и выделить -> Заменить (или нажмите сочетание клавиш Ctrl + H). В появившемся диалоговом окне перейдите во вкладку Заменить и щелкните по кнопке Параметры, чтобы отобразить расширенные опции замены. Щелкните по верхней кнопке Формат.

В появившемся диалоговом окне, перейдите во вкладку Шрифт и выберите Полужирное начертание. Щелкните ОК.

Те же шаги проделайте для замещающего формата (нижняя кнопка Формат), отличием будет только выбор начертания Полужирный курсив в диалоговом окне Найти формат.

Щелкните по кнопке Заменить все, Excel заменит текст с жирным начертанием на жирный курсив.

Хитрости использования Найти и заменить Excel

Замена переноса строки (Alt+Enter) в ячейке

Предположим, у вас имеется таблица с ячейками, данные в которых перенесены по строкам с помощью сочетания клавиш Alt+Enter и требуется заменить символ переноса строк (каретку) на пробел.

Для этого вызываем диалоговое окно Найти и заменить сочетанием клавиш Ctrl+H. В поле Найти вводим Ctrl+J (внешне поле останется пустым), в поле Заменить на вводим Пробел (или любой другой символ).

Щелкаем Заменить все и, о чудо, все наши каретки превратились в пробелы!

Вам также могут быть интересны следующие статьи

Поиск и выделение конкретных значений
Что если отобразить скрытые строки в Excel не работает
Седьмой урок обучающего курса — Основы Excel — Управление несколькими рабочими листами
Пятый урок курса по основам Excel — Печать в программе
Шестой урок онлайн курса по основам Excel — Управление рабочим листом
Четвертый урок курса по основам Excel — Изменение ячеек
Третий урок курса по основам Excel — Форматирование рабочих листов
Второй урок обучающего курса по Excel «Основы Excel»
Запуск новой рубрики — Школа Excel
Печать таблицы в Excel — настройка области и границ печати Excel

14 комментариев

Ренат, огромное спасибо за «Ctrl+J » — я так долго искала, как указать знак переноса в «Найти». Не поленилась написать сообщение, чтобы сказать еще раз спасибо ))

Согласен, хороший трюк, сам пользуюсь)

Спасибо огромное. Ctrl+j — это меша круто! !

Как найти и заменить масив данных к примеру из базы в 200 000 вычистить одно

Как найти и заменить массив данных, к примеру из базы в 200 000 вычистить одной операцией 2 000 контактов?

Спасибо большое!
Нигде больше не мог найти описание, что это именно Ctrl+J!

Ctrl+J спас меня.

Добрый день! А не подскажете ли, как найти (и выделить) пустой текст в ячейке?
Объясню, чего хочу. В одном файле формулой обрабатываются штрих-коды, в текстовом формате, так как код больше 13 символов. При копировании в другой файл на месте отсутствующих штрих-кодов вставляется пустой текст «». О не видим и в ячейке никак не отображается. Для выгрузки в 1С нужно такие ячейки очистить.

Всем привет, такие вопросы по Эксель:
1. ФИО в нашей базе записано в одну стороку, как разбить на три отдельных, не потеряв данные.
2. Телефон в столбце, имеет разные форматы, пример: 375 29 123456, 8029-223456, 375-29-123456. Как привести в один правильный порядок: +37529123456.
Спасибо за ответы.

Андрей, нужно выделить столбец с ФИО, нажать вкладку «данные» на ПИ, «текст по столбцам», «фиксированной ширины», несколько раз «далее», готово!!

Здравствуйте,
Я смотрел Ваше руководство по вопросу найти определенное слово в Excel. У меня возникла одна задача и есть к Вам вопрос. А можно это слово которое ищешь по тексту в Excel затем, как нашел все варианты, заменить их на такое же самое слово, но только что бы оно было полужирным курсивом выделено по всей таблице. Я пытался так сделать с помощью найти и заменить, но оно заменяет на жирный формать весь текст, а не заданое слово. Что Вы мне посоветуете? Спасибо Вам зарание

У меня нормально всё получается, специально проделала эту операцию ))

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

д.д. как переставитьместами, что бы не перепечатывать, заранее спасибо!
262-о на О-262 и.тд.
263-о
264-о
265-о
266-о
267-о
268-о
269-о
270-о
271-о
271-о-а
272-о
273-о
274-о
275-о
276-о
277-о
278-о
279-о
280-о
281-о
282-о
283-о
284-о
285-о
286-о
287-о
288-о
289-о
290-о
291-о
291-о-а
292/1-о
292-о
293-о
294-о
295-о
296-о
297-о
297/1-о
298-о
298-а-о
299-о
300-о
301-о
302-о
303-о
304-о
305-о
306-о
307-о
308-о
309-о
310-о
311-о
312-о
313-о
314-о
315-о
316-о
317-о
318-о
319-о
320-о
321-о
322-о
323-о
324-о
326-о
325-о
327-о
329-о
330-о
331-о
332-о
333-о
334-о
335-о
336-о
337-о
338-о
339-о
340-о
342-о
343-о
344-о
345-о
347-о
348-о
349-о

Подстановочные знаки (символы *, ? и

Рассмотрим применение подстановочных знаков в Excel (символы звездочки «*», тильды «

» и вопросительного знака «?») и их использование при поиске и замене текстовых значений.

Приветствую всех, дорогие читатели блога TutorExcel.Ru.

В начале предлагаю вспомнить определение подстановочных знаков и понять, что же это такое и для каких целей они применяются в Excel. А затем уже разберем применение на конкретных примерах.
Подстановочные знаки — это специальные символы, которые могут принимать вид любого произвольного количества символов, другими словами, являются определенными масками комбинаций символов.
Всего в Excel есть 3 типа подобных знаков:

* (звездочка); Обозначает любое произвольное количество символов. Например, поиск по фразе «*ник» найдет слова типа «понедельник», «всадник», «источник» и т.д.
? (вопросительный знак); Обозначает один произвольный символ. К примеру, поиск по фразе «ст?л» найдет «стол», «стул» и т.д.

(тильда) с последующими знаками *, ? или

. Обозначает конкретный символ *, ? или

. Например, поиск по фразе «хор*» найдет все фразы начинающиеся на «хор» («хоровод», «хорошо» и т.д.). Поэтому для точного поиска «хор*» нужно использовать символ «

» и искать по фразе «хор

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

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

Фильтрация данных

Рассмотрим пример. Предположим, что у нас имеется список сотрудников компании и мы хотим отфильтровать только тех сотрудников, у которых фамилии начинаются на конкретную букву (к примеру, на букву «п»):

Для начала добавляем фильтр на таблицу (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или нажимаем сочетание клавиш Ctrl + Shift + L).
Для фильтрации списка воспользуемся символом звездочки, а именно введем в поле для поиска «п*» (т.е. фамилия начинается на букву «п», после чего идет произвольный текст):

Фильтр определил 3 фамилии удовлетворяющих критерию (начинающиеся с буквы «п»), нажимаем ОК и получаем итоговый список из подходящих фамилий:

В общем случае при фильтрации данных мы можем использовать абсолютно любые критерии, никак не ограничивая себя в выборе маски поиска (произвольный текст, различные словоформы, числа и т.д.).
К примеру, чтобы показать все варианты фамилий, которые начинаются на букву «к» и содержат букву «в», то применим фильтр «к*в*» (т.е. фраза начинается на «к», затем идет произвольный текст, потом «в», а затем еще раз произвольный текст).
Или поиск по «п?т*» найдет фамилии с первой буквой «п» и третьей буквой «т» (т.е. фраза начинается на «п», затем идет один произвольный символ, затем «т», и в конце опять произвольный текст).

Применение в функциях

Как уже говорилось выше, подстановочные знаки в Excel могут использоваться в качестве критерия при сравнении текста в различных функциях Excel (например, СЧЁТЕСЛИ, СУММЕСЛИ, СУММЕСЛИМН, ГПР, ВПР и другие).

Повторим задачу из предыдущего примера и подсчитаем количество сотрудников компании, фамилии которых начинаются на букву «п».
Воспользуемся функцией СЧЁТЕСЛИ, которая позволяет посчитать количество ячеек соответствующих указанному критерию.
В качестве диапазона данных укажем диапазон с сотрудниками (A2:A20), а в качестве критерия укажем запись «п*» (т.е. любая фраза начинающаяся на букву «п»):

Как и в первом примере, в результате мы получили ровно 3 фамилии.

Однако не все функции поддерживают применение подстановочных знаков. Некоторые из них (к примеру, функция НАЙТИ) любой символ воспринимают как текст, даже несмотря на то, что он может быть служебным.
С помощью функции НАЙТИ найдем в тексте позицию вхождения вопросительного знака и звездочки:

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

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

Инструмент «Найти и заменить»

Подстановочные знаки в Excel также можно использовать для поиска и замены текстовых значений в инструменте «Найти и заменить» (комбинация клавиш Ctrl + F для поиска и Ctrl + H для замены).

Рассмотрим пример. Имеется список продукции магазина, в котором нам нужно найти продукт «молоко».
Предположим, что при вводе данных сделали ошибки из-за чего в списке появились продукты «малоко».

Чтобы несколько раз не искать данные по словам «молоко» или «малоко», при поиске воспользуемся критерием «м?локо» (т.е. вторая буква — произвольная):

При этом не стоит забывать, что с помощью данного инструмента можно не только искать текст, но и заменять его (к примеру, заменить «м?локо» на «молоко»).

Как заменить звездочку «*» в Excel?

Практически наверняка каждый сталкивался со следующей ситуацией — в тексте присутствует символ звездочки, который необходимо удалить или заменить на какой-либо другой текст.
Однако при попытке заменить звездочку возникают трудности — при замене меняются абсолютно весь текст, что естественно и логично, так как Excel воспринимает символ «*» как любой произвольный текст.
Но мы теперь уже знаем как с этим бороться, поэтому в поле Найти указываем текст «

*» (явно показываем, что звездочка является специальным символом), а в поле Заменить на указываем на что заменяем звездочку, либо оставляем поле пустым, если хотим удалить звездочку:

Аналогичная ситуация и при замене или удалении вопросительного знака и тильды.
Производя замену «

?» (для тильды — «

») мы также без проблем сможем заменить или удалить спецсимвол.

Найти и заменить в Excel

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

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

Далее вызываем Главная → Редактирование → Найти и выделить → Найти (кнопка с рисунка выше). Поиск также можно включить с клавиатуры комбинацией клавиш Сtrl+F. Откроется диалоговое окно под названием Найти и заменить.

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

После запуска поиска программа Excel быстро-быстро просматривает содержимое листа (или указанного диапазона) на предмет наличия искомой комбинации символов. Если такая комбинация обнаружена, то в случае нажатия кнопки Найти все Excel вываливает все найденные ячейки.

Если в нижней части окна выделить любое значение и затем нажать Ctrl+A, то в диапазоне поиска будут выделены все соответствующие ячейки.

Если же запуск поиска произведен кнопкой Найти далее, то Excel выделяет ближайшую ячейку, соответствующую поисковому запросу. При повторном нажатии клавиши Найти далее (либо Enter с клавиатуры) выделяется следующая ближайшая ячейка (подходящая под параметры поиска) и т.д. После выделения последней ячейки Excel перепрыгивает на самую верхнюю и начинается все заново. На этом познания о поиске данных в Excel у большинства пользователей заканчиваются.

Поиск нестрогого соответствия символов

Иногда пользователь не знает точного сочетания искомых символов что существенно затрудняет поиск. Данные также могут содержать различные опечатки, лишние пробелы, сокращения и пр., что еще больше вносит путаницы и делает поиск практически невозможным. А может случиться и обратная ситуация: заданной комбинации соответствует слишком много ячеек и цель поиска снова не достигается (кому нужны 100500+ найденных ячеек?).

Для решения этих проблем очень хорошо подходят джокеры (подстановочные символы), которые сообщают Excel о сомнительных местах. Под джокерами могут скрываться различные символы, и Excel видит лишь их относительное расположение в поисковой фразе. Таких джокеров два: звездочка «*» (любое количество неизвестных символов) и вопросительный знак «?» (один «?» – один неизвестный символ).

Так, если в большой базе клиентов нужно найти человека по фамилии Иванов, то поиск может выдать несколько десятков значений. Это явно не то, что вам нужно. К поиску можно добавить имя, но оно может быть внесено самым разным способом: И.Иванов, И. Иванов, Иван Иванов, И.И. Иванов и т.д. Используя джокеры, можно задать известную последовательно символов независимо от того, что находится между. В нашем примере достаточно ввести и*иванов и Excel отыщет все выше перечисленные варианты записи имени данного человека, проигнорировав всех П. Ивановых, А. Ивановых и проч. Секрет в том, что символ «*» сообщает Экселю, что под ним могут скрываться любые символы в любом количестве, но искать нужно то, что соответствует символам «и» + что-еще + «иванов». Этот прием значительно повышает эффективность поиска, т.к. позволяет оперировать не точными критериями.

Если с пониманием искомой информации совсем туго, то можно использовать сразу несколько звездочек. Так, в списке из 1000 позиций по поисковой фразе мол*с*м*уход я быстро нахожу позицию «Мол-ко д/сн мак. ГАРНЬЕР Осн.уход д/сух/чув.к. 200мл» (это сокращенное название от «Молочко для снятия макияжа Гараньер Основной уход….»). При этом очевидно, что по фразе «молочко» или «снятие макияжа» поиск ничего бы не дал. Часто достаточно ввести первые буквы искомых слов (которые наверняка присутствуют), разделяя их звездочками, чтобы Excel показал чудеса поиска. Главное, чтобы последовательность символов была правильной.

Есть еще один джокер – знак «?». Под ним может скрываться только один неизвестный символ. К примеру, указав для поиска критерий 1?6, Excel найдет все ячейки содержащие последовательность 106, 116, 126, 136 и т.д. А если указать 1??6, то будут найдены ячейки, содержащие 1006, 1016, 1106, 1236, 1486 и т.д. Таким образом, джокер «?» накладывает более жесткие ограничения на поиск, который учитывает количество пропущенных знаков (равный количеству проставленных вопросиков «?»).

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

Продвинутый поиск

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

С помощью дополнительных параметров поиск в Excel может заиграть новыми красками в прямом смысле слова. Так, искать можно не только заданное число или текст, но и формат ячейки (залитые определенным цветом, имеющие заданные границы и т.д.).

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

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

Поиск формата – это хорошо, но чаще искать приходится конкретные значения. И тут Excel предоставляет дополнительные возможности для расширения и уточнения параметров поиска.

Первый выпадающий список Искать предлагает ограничить поиск одним листом или расширить его до целой книги.

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

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

В следующем выпадающем списке находится замечательная возможность поиска по формулам, значениям, а также примечаниям. По умолчанию Excel производит поиск в формулах либо, если их нет, в содержимом ячейки. Например, если искать фамилию Иванов, а фамилия эта есть результат формулы (копируется из соседнего листа), то поиск нечего не даст, т.к. в ячейке нет искомого перечня символов. По той же причине не удастся отыскать число, являющееся результатом работы какой-либо функции. Поэтому бывает смотришь в упор на ячейку, видишь искомое значение, а Excel его почему-то не видит. Это не глюк, это настройка поиска. Измените данный параметр на Значения и поиск будет осуществляться по тому, что отражено в ячейке, независимо от содержимого. Например, если в ячейке содержится результат вычисления 1/6 (как значение, а не формула) и при этом формат отражает только 3 знака после запятой (т.е 0,167), то поиск символов «167» при выборе параметра Формулы эту ячейку не обнаружит (реальное содержимое ячейки — это 0,166666…), а при выборе Значения поиск увенчается успехом (искомые символы совпадают с тем, что отражается в ячейке). И последний пункт в данном списке – Примечания. Поиск осуществляется только в примечаниях. Очень может помочь, т.к. примечания часто скрыты.

В диалоговом окне поиска есть еще две галочки Учитывать регистр и Ячейка целиком. По умолчанию Excel игнорирует регистр, но можно сделать так, чтобы «иванов» и «Иванов» отличались. Галочка Ячейка целиком также может оказаться весьма полезной, если ищется ячейка не с указанным фрагментом, а полностью состоящая из искомых символов. К примеру, как найти ячейки, содержащие только 0? Обычный поиск не подойдет, т.к. будут выдаваться и 10, и 100. Зато, если установить галочку Ячейка целиком, то все пойдет, как по маслу.

Поиск и замена данных

Данные обычно ищутся не просто так, а для каких-то целей. Такой целью часто является замена искомой комбинации (или формата) на другую. Чтобы найти и заменить в выделенном диапазоне Excel одни значения на другие, в окне Найти и заменить необходимо выбрать вкладку Замена. Либо сразу выбрать на ленте команду Главная → Редактирование → Найти и выделить → Заменить.

Еще удобнее применить сочетание горячих клавиш найти и заменить в Excel – Ctrl+H.

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

По аналогии с простым поиском, менять можно и формат.

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

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

А как быстро удалить все разрывы строк? Обычно это делают вручную. Однако ловкое использование поиска и замены сэкономит много времени. Вызываем команду поиска и замены с помощью комбинации Ctrl+H. Теперь в строке поиска нажимаем Ctrl+J — это символ разрыва строки — на экране появится точка. В строке замены указываем, например, пробел.

Жмем Ok. Все переносы строк заменились пробелами.

Функция поиска и замены при правильном использовании заменяет часы работы неопытного пользователя. Настоятельно рекомендую использовать все вышеизложенное. Если что-то не ищется в ваших данных или наоборот, выдает слишком много лишних ячеек, то попробуйте уточнить поиск с помощью подстановочных символов «*» и «?» или настраиваемых параметров поиска. Важно понимать, что если вы ничего не нашли, это еще не значит, что там этого нет.

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

Источник

Функция ЗАМЕНИТЬ() в MS EXCEL

Функция ЗАМЕНИТЬ( ) , английский вариант REPLACE(), замещает указанную часть знаков текстовой строки другой строкой текста. «Указанную часть знаков» означает, что нужно указать начальную позицию и длину заменяемой части строки. Функция используется редко, но имеет плюс: позволяет легко вставить в указанную позицию строки новый текст.

Синтаксис функции

ЗАМЕНИТЬ(исходный_текст;нач_поз;число_знаков;новый_текст)

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

Функция ЗАМЕНИТЬ() vs ПОДСТАВИТЬ()

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

При замене определенного текста функцию ЗАМЕНИТЬ() использовать неудобно. Гораздо удобнее воспользоваться функцией ПОДСТАВИТЬ() .

Пусть в ячейке А2 введена строка Продажи (январь). Чтобы заменить слово январь, на февраль, запишем формулы:

=ЗАМЕНИТЬ(A2;10;6;»февраль»)
=ПОДСТАВИТЬ(A2; «январь»;»февраль»)

т.е. для функции ЗАМЕНИТЬ() потребовалось вычислить начальную позицию слова январь (10) и его длину (6). Это не удобно, функция ПОДСТАВИТЬ() справляется с задачей гораздо проще.

Кроме того, функция ЗАМЕНИТЬ() заменяет по понятным причинам только одно вхождение строки, функция ПОДСТАВИТЬ() может заменить все вхождения или только первое, только второе и т.д.
Поясним на примере. Пусть в ячейке А2 введена строка Продажи (январь), прибыль (январь). Запишем формулы:
=ЗАМЕНИТЬ(A2;10;6;»февраль»)
=ПОДСТАВИТЬ(A2; «январь»;»февраль»)
получим в первом случае строку Продажи (февраль), прибыль (январь), во втором — Продажи (февраль), прибыль (февраль).
Записав формулу =ПОДСТАВИТЬ(A2; «январь»;»февраль»;2) получим строку Продажи (январь), прибыль (февраль).

Кроме того, функция ПОДСТАВИТЬ() чувствительна к РЕгиСТру. Записав =ПОДСТАВИТЬ(A2; «ЯНВАРЬ»;»февраль») получим строку без изменений Продажи (январь), прибыль (январь), т.к. для функции ПОДСТАВИТЬ() «ЯНВАРЬ» не тоже самое, что «январь».

Использование функции для вставки нового текста в строку

Функцию ЗАМЕНИТЬ() удобно использовать для вставки в строку нового текста. Например, имеется перечень артикулов товаров вида «ID-567(ASD)«, необходимо перед текстом ASD вставить новый текст Micro, чтобы получилось «ID-567(MicroASD)«. Для этого напишем простую формулу:
=ЗАМЕНИТЬ(A2;8;0;»Micro»).

Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)

Замена одного текста на другой внутри заданной текстовой строки — весьма частая ситуация при работе с данными в Excel. Реализовать подобное можно двумя функциями: ПОДСТАВИТЬ (SUBSTITUTE) и ЗАМЕНИТЬ (REPLACE) . Эти функции во многом похожи, но имеют и несколько принципиальных отличий и плюсов-минусов в разных ситуациях. Давайте подробно и на примерах разберем сначала первую из них.

Её синтаксис таков:

=ПОДСТАВИТЬ( Ячейка ; Старый_текст ; Новый_текст ; Номер_вхождения )

Ячейка — ячейка с текстом, где производится замена
Старый_текст — текст, который надо найти и заменить
Новый_текст — текст, на который заменяем
Номер_вхождения — необязательный аргумент, задающий номер вхождения старого текста на замену

Обратите внимание, что:

Если не указывать последний аргумент Номер_вхождения, то будут заменены все вхождения старого текста (в ячейке С1 — обе «Маши» заменены на «Олю»).
Если нужно заменить только определенное вхождение, то его номер задается в последнем аргументе (в ячейке С2 только вторая «Маша» заменена на «Олю»).
Эта функция различает строчные и прописные буквы (в ячейке С3 замена не сработала, т.к. «маша» написана с маленькой буквы)

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

Замена или удаление неразрывных пробелов

При выгрузке данных из 1С, копировании информации с вебстраниц или из документов Word часто приходится иметь дело с неразрывным пробелом — спецсимволом, неотличимым от обычного пробела, но с другим внутренним кодом (160 вместо 32). Его не получается удалить стандартными средствами — заменой через диалоговое окно Ctrl + H или функцией удаления лишних пробелов СЖПРОБЕЛЫ (TRIM) . Поможет наша функция ПОДСТАВИТЬ, которой можно заменить неразрывный пробел на обычный или на пустую текстовую строку, т.е. удалить:

Подсчет количества слов в ячейке

Если нужно подсчитать количество слов в ячейке, то можно применить простую идею: слов на единицу больше, чем пробелов (при условии, что нет лишних пробелов). Соответственно, формула для расчета будет простой:

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

Извлечение первых двух слов

Если нужно вытащить из ячейки только первые два слова (например ФИ из ФИО), то можно применить формулу:

У нее простая логика:

заменяем второй пробел на какой-нибудь необычный символ (например #) функцией ПОДСТАВИТЬ (SUBSTITUTE)
ищем позицию символа # функцией НАЙТИ (FIND)
вырезаем все символы от начала строки до позиции # функцией ЛЕВСИМВ (LEFT)

Функции замены в Экселе: ПОДСТАВИТЬ, ЗАМЕНА

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

Функция ПОДСТАВИТЬ(Исходный текст; Что искать; На что заменить;[номер вхождения]).

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

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

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

Примеры применения функции ПОДСТАВИТЬ

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

Функция ЗАМЕНИТЬ(Исходный текст; Начальная позиция; Количество знаков; Новый текст)

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

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

Например, запишем формулу: =ЗАМЕНИТЬ(A1;79;4;«2016»)

Она заменит в тексте ячейки А1 символы №79-82 (4 шт) на строку «2016».

Функция ЗАМЕНИТЬ в Эксель

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

В следующем посте мы рассмотрим преобразование числа в текст и наоборот. А пока жду ваших вопросов и комментариев!

Примеры работы с текстовой функцией ЗАМЕНИТЬ в Excel

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

Как работает функция ЗАМЕНИТЬ в Excel?

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

Функция заменить в Excel и примеры ее использования

Создадим на листе рабочей книги табличного процессора Excel табличку со словами, как показано на рисунке:
Далее на листе рабочей книги подготовим область для размещения нашего результата – полученного слова «молоток», как показано ниже на рисунке. Установим курсор в ячейке А6 и вызовем функцию ЗАМЕНИТЬ:
Заполняем функцию аргументами, которые изображены на рисунке:

Выбор данных параметров поясним так: в качестве старого текста выбрали ячейку А2, в качестве нач_поз установили число 5, так как именно с пятой позиции слова «Молоко» мы символы не берем для нашего итогового слова, число_знаков установили равным 2, так как именно это число не учитывается в новом слове, в качестве нового текста установили функцию ПРАВСИМВ с параметрами ячейки А3 и взятием последних двух символов «ок».

Далее нажимаем на кнопку «ОК» и получаем результат:

Как заменить часть текста в ячейке Excel?

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

Создадим на листе рабочей книги Excel табличку со фамилией и именем, как показано на рисунке:
Далее на этом же листе рабочей книги подготовим область для размещения нашего результата – измененных фамилий и имен. Заполним ячейки двумя типами формул как показано на рисунке:

Обратите внимание! Во второй формуле мы используем оператор «&» для добавления символа «а» к мужской фамилии, чтобы преобразовать ее в женскую. Для решения данной задачи можно было бы использовать функцию =СЦЕПИТЬ(B3;»а») вместо формулы =B3&»а» – результат идентичный. Но сегодня настоятельно рекомендуется отказываться от данной функции так как она имеет свои ограничения и более требовательна к ресурсам в сравнении с простым и удобным оператором амперсанд.

Текстовые функции Excel

ФИО, номера банковских карт, адреса клиентов или сотрудников, комментарии и многое другое –все это является строками, с которыми многие сталкиваются, работая с приложением Excel. Поэтому полезно уметь обрабатывать информацию подобного типа. В данной статье будут рассмотрены текстовые функции в Excel, но не все, а те, которые, по мнению office-menu.ru, самые полезные и интересные:

Список всех текстовых функций Вы можете найти на вкладке «Формулы» => выпадающий список «Текстовые»:

Функция ЛЕВСИМВ

Возвращает подстроку из текста в порядке слева направо в заданном количестве символов.

Синтаксис: =ЛЕВСИМВ(текст; [количество_знаков])

текст – строка либо ссылка на ячейку, содержащую текст, из которого необходимо вернуть подстроку;
количество_знаков – необязательный аргумент. Целое число, указывающее, какое количество символов необходимо вернуть из текста. По умолчанию принимает значение 1.

Пример использования:

Формула: =ЛЕВСИМВ(«Произвольный текст»;8) – возвращенное значение «Произвол».

Функция ПРАВСИМВ

Данная функция аналогична функции «ЛЕВСИМВ», за исключением того, что знаки возвращаются с конца строки.

Пример использования:

Формула: =ПРАВСИМВ(«произвольный текст»;5) – возвращенное значение «текст».

Функция ДЛСТР

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

Синтаксис: =ДЛСТР(текст)

Пример использования:

Функция НАЙТИ

Возвращает число, являющееся вхождением первого символа подстроки, искомого текста. Если текст не найден, то возвращается ошибка «#ЗНАЧ!».

Синтаксис: =НАЙТИ(искомый_текст; текст_для_поиска; [нач_позиция])

искомый_текст – строка, которую необходимо найти;
текст_для_поиска – текст, в котором осуществляется поиск первого аргумента;
нач_позиция – необязательный элемент. Принимает целое число, которое указывает, с какого символа текст_для_поиска необходимо начинать просмотр. По умолчанию принимает значение 1.

Пример использования:

Из отрывка стихотворения великого поэта С.А.Есенина находим вхождение первого символа строки «птица». Поиск осуществляется с начала строки. Если в приведенном примере поиск осуществлялся бы с 40 символа, то функция в результате вернула ошибку, т.к. позиции вхождения не было найдено.

Функция ЗАМЕНИТЬ

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

Синтаксис: ЗАМЕНИТЬ(старый_текст; начальная_позиция; количество_знаков; новый_текст)

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

Пример использования:

Здесь в строке, содержащейся в ячейке A1, подменяется слово «старый», которое начинается с 19-го символа и имеет длину 6 символов, на слово «новый».

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

Аргумент «начальная_позиция» подменим функцией «НАЙТИ»;
В место аргумент «количество_знаков» вложим функцию «ДЛСТР».

В результате получим формулу: =ЗАМЕНИТЬ(A1;НАЙТИ(«старый»;A1);ДЛСТР(«старый»);»новый»)

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

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

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

Синтаксис: ПОДСТАВИТЬ(текст; старый_текст; новый_текст; [номер_вхождения])

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

Пример использования:

Строка в ячейке A1 содержит текст, в котором имеются 2 подстроки «старый». Нам необходимо подставить на место первого вхождения строку «новый». В результате часть текста «…старый-старый…», заменяется на «…новый-старый…».

Если ли бы последний аргумент был опущен, то результатом бы стала строка «строка, содержащая новый-новый текст».

Функция ПСТР

ПСТР возвращает из указанной строки часть текста в заданном количестве символов, начиная с указанного символа.

Синтаксис: ПСТР(текст; начальная_позиция; количество_знаков)

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

Пример использования:

Из текста, находящегося в ячейке A1 необходимо вернуть последние 2 слова, которые имеют общую длину 12 символов. Первый символ возвращаемой фразы имеет порядковый номер 12.

Аргумент количество_знаков может превышать допустимо возможную длину возвращаемых символов. Т.е. если в рассмотренном примере вместо количество_знаков = 12, было бы указано значение 15, то результат не изменился, и функция так же вернула строку «функции ПСТР».

Для удобства использования данной функции ее аргументы можно подменить функциями «НАЙТИ» и «ДЛСТР», как это было сделано в примере с функцией «ЗАМЕНИТЬ».

Функция СЖПРОБЕЛЫ

Данная функция удаляется все лишние пробелы: пробелы по краям и двойные пробелы между словами. После обработки строк функцией остаются только одиночные пробелы между словами.

Синтаксис: =СЖПРОБЕЛЫ(текст)

Пример использования:

=СЖПРОБЕЛЫ( » Текст с лишними пробелами между словами и по краям « )

Результатом выполнения функции будет строка: «Текст с лишними пробелами между словами и по краям» .

Функция СЦЕПИТЬ

С помощью функции «СЦЕПИТЬ» можно объединить несколько строк между собой. Максимальное количество строк для объединения – 255.

Синтаксис: =СЦЕПИТЬ(текст1; [текст2]; …)

Функция должна содержать не менее одного аргумента

Пример использования:

Функция возвратит строку: «Слово1 Слово2».

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

Вместо использования данной функции можно применять знак амперсанда «&». Он так же объединяет строки. Например: «=»Слово1″&» «&«Слово2″».

Источник

См. также

Нужна дополнительная помощь?

Как работает функция ЗАМЕНИТЬ

Использование функции ЗАМЕНИТЬ с числами

Как заменить часть даты

Как заменить сразу несколько букв или слов

Как заменить текст в разных местах

Заменить заглавные буквы на строчные и наоборот

Описание функции ПОДСТАВИТЬ

Замена нескольких значений одной формулой

Что лучше использовать – ЗАМЕНИТЬ или ПОДСТАВИТЬ?

CopyFile method

Syntax

See also

Support and feedback

Метод CopyFile

Синтаксис

Замечания

См. также

Поддержка и обратная связь

VBA Excel. Копирование и перемещение файлов

Копирование файлов

Метод CopyFile

Синтаксис

Параметры

Примеры

Метод CopyFolder

Синтаксис

Замечания

См. также

Поддержка и обратная связь

FileCopy в VBA — Копировать файл из одного места в другое с помощью VBA

Excel VBA FileCopy

Синтаксис FileCopy в Excel VBA

Как скопировать файл в Excel VBA?

Пример № 1 — FileCopy в VBA

Пример №2 — FileCopy в VBA

Плюсы FileCopy в VBA

То, что нужно запомнить

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

Копирование листа в другой документ.

Как быстро избавится от ссылок на старый документ в формулах.

Применение специальной вставки в Microsoft Excel

Работа со специальной вставкой

Способ 1: работа со значениями

Способ 2: копирование формул

Способ 3: перенос форматирования

Способ 4: копирование таблицы с сохранением размера столбцов

Способ 5: вставка рисунка

Способ 6: копирование примечаний

Способ 7: транспонирование таблицы

Способ 8: использование арифметических действий

Функция ПОДСТАВИТЬ при условии подставляет значение

Автозамена значения в текстовых ячейках с помощью функции ПОДСТАВИТЬ

Формула с макросом регулярного выражения и функция ПОДСТАВИТЬ

Как в excel в текст вставить значение из другой ячейки

Пункты меню «Вставить»

Параметры специальной вставки

Параметры вставки

Параметры операций

Доступны и другие параметры:

Перенос с помощью опции «Специальная вставка»

Перенос с помощью опции «Специальная вставка» и «Замена»

Перенос с помощью функции «ТРАНСП»

Перенос с помощью преобразования данных в разных версиях Excel

В Excel 2016

В Excel 2013/2010

Способы замены символов в Excel

Поиск с заменой

Автоматическая замена

Дополнительные параметры

Поиск или замена текста и чисел на листе

В этом курсе:

Поиск или замена текста и чисел на листе

Поиск

Заменить

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Инструмент Найти и заменить в Excel

Поиск информации