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

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

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

Найти и заменить текст и числа в Excel

При работе с большими таблицами часто возникает необходимость найти конкретное значение. К счастью, найти и заменить его несложно.

Щелкнув любую ячейку, вы можете выполнить поиск во всей электронной таблице или выбрать любой столбец или диапазон ячеек, которые вы хотите проанализировать. Можно щелкнуть Главная > Найти и выделить > Найти или нажать Ctrl + F.

Введите текст или число, которое вы хотите найти, в текстовое поле «Найти».

Нажмите «Найти далее», чтобы найти первое вхождение значения в области поиска; нажмите «Найти далее» еще раз, чтобы найти второе вхождение, и так далее.

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

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

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

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

Изучите дополнительные параметры

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

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

Щелкните стрелку списка «Внутри», чтобы изменить его на «Рабочая книга».

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

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

Изменить форматирование значений

Также возможны поиск и замена формата значений.

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

Щелкните Главная> Найти и выбрать> Заменить, чтобы открыть диалоговое окно «Найти и заменить».

Нажмите кнопку «Параметры», чтобы развернуть параметры поиска и замены.

Нет необходимости вводить текст или цифры, которые вы хотите заменить, если вы этого не хотите.

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

Укажите форматирование, которое нужно найти или заменить.

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

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

Использование подстановочных знаков

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

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

Звездочка ( *) заменяет любое количество символов. Например, y * найдет «да», «да», «да» и «ура».

В данном примере в столбце A нашей электронной таблицы находится список имен, за которым следует идентификатор. Они работают в следующем формате: Алан Мюррей — 5367.

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

Щелкните Главная> Найти и выбрать> Заменить, чтобы открыть диалоговое окно «Найти и заменить».

Введите «- *» в текстовое поле «Найти» (перед дефисом и после него есть пробелы). Оставьте текстовое поле «Заменить на» пустым.

Exceltip

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

Изучаем формулу ЕСЛИ — 5 приемов Excel, о которых вы не знали

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

1. Сумма альтернативных столбцов/строк

Это происходит, когда нам нужно просуммировать каждую вторую строку в столбце (не спрашивайте меня, почему, это не редкость). Каждую ячейку можно было бы просуммировать отдельно, но мы не можем этого сделать. Вместо этого мы сделаем себе перерыв на кофе, пока Excel делает за нас всю грязную работу. В этом нам поможет функция СУММЕСЛИ, которая суммирует ячейки при соблюдении определенного критерия. В нашем случае критерием будет «если элемент находится в нечетных строках».

Все, что нам нужно сделать, это добавить дополнительный столбец и заполнить его единицами и нулями (мы напишем 1 и 0 в двух строках, пометим обе ячейки и перетащим их в конец таблицы). Теперь мы можем использовать наш столбец для определения условия с помощью функции =SUMA(conditionarea; 1; sumarea).

2. Подсчет количества повторений в листе А элементов из листа Б

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

В приведенном выше примере я использовал функцию CALCULATE для подсчета количества клиентов в данном городе (список клиентов находится на листе 2, а список городов — на листе 1). Формула выглядит как =CALCULATE(диапазон условий; условие). Например, формула CALCULATE($ E 6:$ E 16; «Волгоград») скажет мне, сколько клиентов в Волгограде.

3. Быстрый подсчет данных с помощью СЧЁТЕСЛИ и СУММЕСЛИ

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

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

4. Поиск второго, третьего … n -ного вхождения элемента в списке EXCEL

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

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

При поиске информации о Кристине Агилере мы будем использовать измененные данные о клиенте. FPR(«Christina Aguilera3», search range, 2, FALSE) даст нам, например, третий номер телефона Кристины Агилеры. Эта формула заканчивается на False, поскольку наш список не алфавитный, и нам нужно точное соответствие поисковому значению.

5. Сокращаем количество вложений функции ЕСЛИ

В предыдущих версиях Excel допускалось только 7 уровней функций IF. Начиная с Excel 2007 это ограничение было снято. К счастью, большинство из нас никогда не опускались ниже уровня 3 или 4. Но зачем писать такие многоуровневые формулы, если можно использовать функцию SELECT, которая выбирает значение или действие из списка значений на основе индексного номера. Синтаксис функции — SELECT(index_number; value_by_index_equal_1; value_by_index_equal_2; value_by_index_equal_2…). Например, следующая функция SELECT(3; «Отлично»; «Хорошо»; «Плохо») при вводе в ячейку возвращает «Плохо». Существует одно ограничение, а именно: значение номера индекса должно быть задано в числовом формате, поэтому требуется определенный творческий подход (как в примере ниже), когда значение индекса не может быть указано в явном виде:

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

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

24 комментария

К примеру 5:
Функция КОДСИМВ() возвращает значения от букв A, B, C, D (англ.) — 65, 66, 67, 68 соответственно вычитая из этих значений 64, получаем 1, 2, 3, 4 для первого аргумента функции ВЫБОР().

Браво, Рустем, вот это да!

В 4-м пункте (дабы было удобнее пользоваться этим приемом) лучше в ячейках B6:B9 сделать пользовательский формат 0″-й», а в формуле ссылаться на соседнюю ячейку, то есть так (формула для ячейки C6):
=ВПР($C$5&B6;$F$6:$G$24;2;ЛОЖЬ)

В этом случае вам не придется писать число в формуле вручную (в формуле, которая показана сейчас на скриншоте)

А как найти клиента в огромной базе данных по номеру телефона? Т.е. в таблице куча параметров о клиентах и контактная информация в том числе (это как уникальный номер, только по нему можно найти нужного клиента). Таких рисков у меня 95-100 штук. Я требую, чтобы он не вводил одного и того же клиента повторно.