Функция ЗАМЕНИТЬ() , английский вариант REPLACE(), замещает указанную часть знаков текстовой строки другой строкой текста. "Указанную часть знаков" означает, что нужно указать начальную позицию и длину заменяемой части строки. Функция используется редко, но имеет плюс: позволяет легко вставить в указанную позицию строки новый текст.
Синтаксис функции
ЗАМЕНИТЬ (исходный_текст ;нач_поз ;число_знаков ;новый_текст )
Исходный_текст
- текст, в котором требуется заменить некоторые знаки.
Нач_поз
- позиция знака в Исходном_тексте
, начиная с которой знаки заменяются текстом новый_текст
.
Число_знаков
- число знаков в Исходном_тексте
, которые заменяются текстом новый_текст
.
Новый_текст
- текст, который заменяет знаки в Исходном_тексте
.
Функция ЗАМЕНИТЬ() vs ПОДСТАВИТЬ()
Функция ПОДСТАВИТЬ() используется, когда нужно заменить определенный текст в текстовой строке; функция ЗАМЕНИТЬ() используется, когда нужно заменить любой текст начиная с определенной позиции.
При замене определенного текста функцию ЗАМЕНИТЬ() использовать неудобно. Гораздо удобнее воспользоваться функцией ПОДСТАВИТЬ() .
Пусть в ячейке А2 введена строка Продажи (январь) . Чтобы заменить слово январь , на февраль , запишем формулы:
т.е. для функции ЗАМЕНИТЬ() потребовалось вычислить начальную позицию слова январь (10) и его длину (6). Это не удобно, функция ПОДСТАВИТЬ() справляется с задачей гораздо проще.
Кроме того, функция ЗАМЕНИТЬ()
заменяет по понятным причинам только одно вхождение строки, функция ПОДСТАВИТЬ()
может заменить все вхождения или только первое, только второе и т.д.
Поясним на примере. Пусть в ячейке А2
введена строка . Запишем формулы:
=ЗАМЕНИТЬ(A2;10;6;"февраль")
=ПОДСТАВИТЬ(A2; "январь";"февраль")
получим в первом случае строку Продажи (февраль), прибыль (январь)
, во втором - Продажи (февраль), прибыль (февраль)
.
Записав формулу =ПОДСТАВИТЬ(A2; "январь";"февраль";2)
получим строку Продажи (январь), прибыль (февраль)
.
Кроме того, функция ПОДСТАВИТЬ() чувствительна к РЕгиСТру. Записав =ПОДСТАВИТЬ(A2; "ЯНВАРЬ";"февраль") получим строку без изменений Продажи (январь), прибыль (январь) , т.к. для функции ПОДСТАВИТЬ() "ЯНВАРЬ" не тоже самое, что "январь".
Использование функции для вставки нового текста в строку
Функцию ЗАМЕНИТЬ()
удобно использовать для вставки в строку нового текста. Например, имеется перечень артикулов товаров вида "ID-567(ASD)
", необходимо перед текстом ASD
вставить новый текст Micro
, чтобы получилось "ID-567(MicroASD)
". Для этого напишем простую формулу:
=ЗАМЕНИТЬ(A2;8;0;"Micro").
Если вам прислали файл, а там вместо букв в столбцах цифры, а ячейки в формулах задаются странным сочетанием чисел и букв R и C, это несложно поправить, ведь это специальная возможность — стиль ссылок R1C1 в Excel. Бывает, что он устанавливается автоматически. Поправляется это в настройках. Как и для чего он пригодиться? Читайте ниже.
Стиль ссылок R1C1 в Excel. Когда вместо букв в столбцах появились цифры
Если вместо названия столбцов (A, B, C, D…) появляются числа (1, 2, 3 …), см. первую картинку — это тоже может вызвать недоумение и опытного пользователя. Так чаще всего бывает, когда файл вам присылают по почте. Формат автоматически устанавливается как R1C1, от R ow=строка, C olumn=столбец
Так называемый стиль ссылок R1C1 удобен для работы по программированию в VBA, т.е. для написания .
Как же изменить обратно на буквы? Зайдите в меню - Левый верхний угол - Параметры Excel - Формулы - раздел Работа с формулами - Стиль ссылок R1C1 - снимите галочку.
Для Excel 2003 Сервис — Параметры — вкладка Общие — Стиль ссылок R1C1.
R1C1 как можем использовать?
Для понимания приведем пример
Вторая удобная возможность записать адрес в зависимости от ячейки, в которой мы это запишем. Добавим квадратные скобки в формулу
Как мы видим, ячейка формулы отстаит от ячейки записи на 2 строки и 1 столбец
Согласитесь возможность может пригодиться.
Для чего это нужно?
Как мы сказали раньше, формат удобен для программирования. Например, при записи сложения двух ячеек в коде вы можете не видеть саму таблицу, удобнее будет записать формулу по номеру строки/столбца или отстаящую от этой ячейки (см пример выше).
Если ваша таблица настолько огромна, что количество столбцов перевалило за 100, то вам удобнее будет видеть номер столбца 131, чем буквы EA, как мне кажется.
Если привыкнуть к такому типу ссылок, то найти ошибку при включении режима просмотра найти ошибку будет значительно проще и нагляднее.
Добрый день, уважаемые читатели блога сайт.
Многие знают, что такое поиск в Excel и как им пользоваться. В то же время у новичков эта операция занимает долгие минуты и сопровождается кучей ненужных действий. Без слез смотреть невозможно. Что ж, эта заметка для всех категорий пользователей. Ниже написано о том, как быстро отыскать ячейку (-ки) с нужной информацией, даже если вы сами не четко представляете, что ищете.
При работе с большими таблицами рано или поздно наступает необходимость быстрого поиска данных (название товара, код, фамилию, число и проч.). Для эффективного (быстрого и корректного) поиска правильно будет задействовать специальную команду, для которой на ленте предусмотрена внушительных размеров кнопка.
Значком бинокля, кстати, обозначается поиск и в других программах. Поэтому, если вам нужно отыскать, например, какое-то словосочетание в Word, смело жмите на бинокль.
Команда поиска придумана для автоматического обнаружения ячеек, содержащих искомую комбинацию символов. Поиск данных может производиться в определенном диапазоне, целом листе или даже во всей книге. Поэтому первым делом нужно определить диапазон, где Excel будет производить поиск. Диапазон обычно выделяют мышкой, но если он очень большой, то соответствующие горячие клавиши сильно облегчают жизнь. Если же активна только одна ячейка, то по умолчанию поиск производится на всем листе Excel.
Далее вызываем нужную команду Главная → Редактирование → Найти и выделить → Найти (кнопка с рисунка выше). Поиск также можно включить с клавиатуры комбинацией клавиш Сtrl+F, что гораздо удобней. Перед нами открывается диалоговое окошко под названием «Найти и заменить» .
В единственном поле указывается информация (комбинация символов), которую требуется найти. Если не использовать т.н. джокеры (о которых речь пойдет чуть ниже), то Excel будет искать строгое совпадение заданных символов. Для вывода результатов поиска предлагается два варианта: выводить все результаты сразу – кнопка ; либо выводить по одному найденному значению – кнопка «Найти далее». На рисунке выше выделено красной рамкой.
После запуска поиска программа Excel быстро-быстро просматривает содержимое листа (или указанного диапазона) на предмет наличия присутствия искомой комбинации символов. Если такая комбинация обнаружена, то в случае нажатия кнопки Excel вываливает все найденные ячейки.
Если в нижней части окна выделить любое значение и затем нажать Ctrl+A, то в диапазоне поиска будут выделены все соответствующие ячейки.
Если же запуск поиска произведен кнопкой «Найти далее»
, то 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. Зато, если установить галочку «Ячейка целиком», то все пойдет, как по маслу.
Поиск и замена данных
Понятное дело, что данные ищутся не просто так, а для каких-то целей. Такой целью часто является замена искомой комбинации (или формата) на другую. Чтобы найти и заменить данные, в окошке «Найти и заменить» необходимо выбрать закладку «Замена» . Можно сразу выбрать на ленте соответствующую команду.
Либо нажать на горячую клавишу Ctrl+H.
Диалоговое окно увеличится на одно поле, в котором указываются новые символы, которые будут вставлены вместо найденных.
Известно, что в обычном состоянии заголовки столбцов в программе Excel обозначаются буквами латинского алфавита. Но, в один момент пользователь может обнаружить, что теперь столбцы обозначены цифрами. Это может случиться по нескольким причинам: различного рода сбои в работе программы, собственные неумышленные действия, умышленное переключение отображения другим пользователем и т.д. Но, каковы бы причины не были, при возникновении подобной ситуации актуальным становится вопрос возврата отображения наименований столбцов к стандартному состоянию. Давайте выясним, как поменять цифры на буквы в Экселе.
Существует два варианта приведения панели координат к привычному виду. Один из них осуществляется через интерфейс Эксель, а второй предполагает ввод команды вручную с помощью кода. Рассмотрим подробнее оба способа.
Способ 1: использования интерфейса программы
Самый простой способ сменить отображение наименований столбцов с чисел на буквы – это воспользоваться непосредственным инструментарием программы.
Теперь наименование столбцов на панели координат примет привычный для нас вид, то есть будет обозначаться буквами.
Способ 2: использование макроса
Второй вариант в качестве решения проблемы предполагает использования макроса.
После этих действий вернется буквенное отображение наименования столбцов листа, сменив числовой вариант.
Как видим, неожиданная смена наименования координат столбцов с буквенного на числовое не должна ставить в тупик пользователя. Все очень легко можно вернуть к прежнему состоянию с помощью изменения в параметрах Эксель. Вариант с использованием макроса есть смысл применять только в том случае, если по какой-то причине вы не можете воспользоваться стандартным способом. Например, из-за какого-то сбоя. Можно, конечно, применить данный вариант в целях эксперимента, чтобы просто посмотреть, как подобный вид переключения работает на практике.