Функция эксель поиск: ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)
Содержание
Сравнение текста в Excel и поиск символов в Эксель. Как сравнить строки?
Заголовки в Excel. Как закрепить области в Excel? Сквозные строки в Excel
Как вставить текст в Excel?
Чтобы сравнить две ячейки, в которых содержится текст, необходимо просто приравнять их в соседней ячейке. Но что делать, если вам нужно сравнить текст с учетом больших и маленьких букв? А если с учетом английских букв?
Появилась такая потребность, как сравнение текста в Excel? Читайте далее, и, уверен, статья вам поможет:
Содержание
- 1) С простым сравнением разобрались? Отлично 🙂
- 2) Сравнение текста в Excel по регистрам
- 3) Поиск чисел в ячейки с текстом
- 4) Поиск определенных символов в тексте
- 5) Удалить лишние пробелы в начале и конце строки
- 6) Поиск символов
- 7) Поиск символов с помощью условного форматирования или фильтра. Возможность «Содержит»
- Похожие статьи
1) С простым сравнением разобрались? Отлично 🙂
2) Сравнение текста в Excel по регистрам
Сравнение текстов происходит простой формулой в примере номер 1, на картинке.
Если необходимо сравнить прописные и заглавные буквы воспользуйтесь функцией =СОВПАД(). Пример 2.
3) Поиск чисел в ячейки с текстом
Самый простой способ найти числа в строке, воспользоваться формулой
=ИЛИ(ЕЧИСЛО(ПОИСК({"1";"2";"3";"4";"5";"6";"6";"8";"9";"0"};B5)))Самая распространенная ошибка — вместо буквы О пишут число 0 и наоборот
Но обязательно воспользуйтесь формулой массива — нажмите ctrl+shift+enter вместо enter после ввода формулы.
4) Поиск определенных символов в тексте
В примере показывается, как можно найти все заглавные буквы русского алфавита. Но можно использовать эту группу функций для поиска любых символов.
=ИЛИ(ЕЧИСЛО(ПОИСК({"A";"Б";"В";"Г";"Д";"Е";"Ж";"З";"И";"Й";"К";"Л";"М";"Н";"О";"П";"Р";"С";"Т";"У";"Ф";"Х";"Ц";"Ч";"Ш";"Щ";"Ъ";"Ы";"Ь";"Э";"Ю";"Я"};B8)))Опять же не забываем про формулы массивов!
5) Удалить лишние пробелы в начале и конце строки
В версии excel 2007 появилась замечательная функция =СЖПРОБЕЛЫ() — она удаляет все лишние пробелы в начале и конце текста, а так же все задвоенные пробелы.
Настоятельно рекомендую запомнить — использую ее чуть ли не каждый день.
6) Поиск символов
Для поиска символов можно использовать функцию =ПОИСК(), она возвращает (считает), на какой позиции находиться заданный вами символ. Подробнее здесь.
7) Поиск символов с помощью условного форматирования или фильтра. Возможность «Содержит»
Можно окрасить все ячейки цветом, которые содержат заданные символы или надписи в них. Для этого воспользуйтесь условным форматированием, как показано на рисунке.
Чтобы оставить только те ячейки, которые содержат нужные символы, воспользуйтесь фильтрами. В фильтрах есть возможность отобрать строки по заданным условиям
Удачной охоты за символами!
Заголовки в Excel. Как закрепить области в Excel? Сквозные строки в Excel
Как вставить текст в Excel?
Анализ данных в электронных таблицах — что это, определение и ответ
В рабочих и учебных задачах очень часто появляется необходимость в анализе большого количества данных.
В этом помогают электронные таблицы и встроенные в них математические, логические и статистические функции, а также возможность построения различных графиков и диаграмм.
В задании экзамена, посвящённом анализу большого количества данных в электронной таблице, необходимо хорошо уметь оперировать рядом математических, статистических и логических функций, также умением строить графики и диаграммы. Дальше в данной теории разберём с примерами каждый блок.
Использование функций на примере MS Exel.
Прежде чем перейти к описанию непосредственно функций, стоит посмотреть, как технически вызвать использование функции для ячейки.
Это можно сделать несколькими способами:
Перейти в ячейку и нажать на иконку функции вверху слева. Тогда откроется меню со списком всех функций для выбора. В данном списке можно осуществлять поиск и посмотреть краткое описание каждой функции.
Стоит отметить, что этот способ удобен для написания сложных функций, так как при работе с функцией также открывается удобное диалоговое окно для её поэтапного составления.
Написать в ячейке знак «=» и воспользоваться списком последних используемых функций.
Данный способ также вызывает удобное диалоговое окно для работы с функциями. В показанном удобном выпадающем списке находятся десять последних использовавшихся функций, если нужна функция, которой нет в списке, то нажав «Другие функции» появится полный перечень функций.
Прописать формулу вручную. Этот способ возможен, но не удобен, так как для него надо отлично помнить написания всех необходимых функций и самостоятельно отслеживать все необходимые скобки, запятые и точки с запятой.
Математические и статистические функции.
Для решения заданий экзамена необходимо уметь работать со следующими математическими функциями.
СУММ — Находит сумму всех аргументов.
Например, в ячейку С5 записали сумму значений ячеек А1:C3.
СРЗНАЧ — Считает среднее арифметическое аргументов.
Важно, что при расчёте среднего арифметического НЕ учитываются пустые ячейки.
Например, в ячейку С5 записали среднее значение ячеек А1:C3
СЧЁТ – Считает количество НЕПУСТЫХ ячеек в диапазоне.
Например, в ячейку С5 записали формулу =СЧЁТ(А1:C3)
Полученное значение в ячейке С5 равно именно 7, так как в выделенном диапазоне 7 заполненных ячеек, если мы запишем ещё в одну ячейку какое-то значение (даже 0), то значение в С5 увеличится на 1.
МАКС — Возвращает наибольшее значение в списке аргументов.
МИН — Возвращает наименьшее значение в списке аргументов.
Например, в ячейку С5 записали формулу =МАКС(А1:C3), а в ячейку B5 = МИН(А1:C3)
СУММЕСЛИ — Суммирует значения в ячейках, удовлетворяющие заданному условию
Например, в ячейку С5 необходимо сосчитать сумму значений, больших 1, в диапазоне А1:C3.
При вызове диалогового окна функции заполняем выбор диапазона и записываем условие.
Получаем в ячейке формулу =СУММЕСЛИ(A1:C3;»>1″), и результат равный 11 (2+3+3+3)
Важно, что при использовании данной функции мы можем проверять условие по одному диапазону, а суммировать значения в другом. В таком случае поле Диапазон будет отвечать за ячейки, в которых проверяется условие, а Диапазон_суммирования – это будут ячейки, значения которых будут суммироваться.
Например, в ячейку С5 запишем формулу =СУММЕСЛИ(A1:A3;»>1″;B1:B4)
Условие проверялось по столбцу А, а значения брались из советующих строк столбца B.
СЧЁТЕСЛИ — Подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию.
Например, в ячейку С5 необходимо сосчитать количество значений, не равных 2, в диапазоне А1:C3.
При вызове диалогового окна функции заполняем выбор диапазона и записываем условие.
Получаем в ячейке формулу =СЧЁТЕСЛИ(A1:C3;»<>2″), и результат равный 8.
В данном случае пустые ячейки учитываются, так как значение в них не равно двум.
Логические функции.
И – логические умножение, возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА.
ИЛИ – логические сложение, возвращает значение ИСТИНА, если хотя бы один аргумент имеет значение ИСТИНА.
НЕ – логическое отрицание, меняет логическое значение своего аргумента на противоположное
Стоит отметить, что для логических функций в качестве значения ИСТИНА может выступать как непосредственно запись «ИСТИНА» в ячейке, так и любое число, не равное 0. Для значения «ЛОЖЬ» также может использоваться само слово и 0.
Пример, в ячейку C3 записали формулу =И(A1:C1). Полученное значение – ИСТИНА, так как в диапазоне только положительный числа
ЕСЛИ – выполняет проверку условия и присваивает ячейке определённое значение в случае выполнения и/или невыполнения условия.
Для её использования обязательно надо указать логическое выражение для проверки условия. Если не заполнить поля Значение_если_истина, Значение_если_ложно, то в случае истинности функция вернёт значение ИСТИНА, в случае ложности — ЛОЖЬ
Для составления выражений в логических функциях и не только часто необходимо использовать операторы сравнения. В электронных таблицах они выглядят следующим образом
| Оператор сравнения | Значение | Пример |
|---|---|---|
| = | Равно | A1 = B1 |
| > | Больше | A1>B1 |
| < | Меньше | A1<B1 |
| >= | Больше или равно | A1>= B1 |
| <= | Меньше или равно | A1<= B1 |
| <> | Не равно | A1<>B1 |
Пример составления сложной функции на основе пункта задания экзамена.
Рассмотрим подробно, как составлять формулу для получения необходимых значений для решения одного из пунктов задания экзамена.
Условия:
В электронную таблицу занесли данные о тестировании учеников по выбранным ими предметам.
В столбце A записан код округа, в котором учится ученик; в столбце B – фамилия; в столбце C – выбранный учеником предмет; в столбце D – тестовый балл.
Всего в электронную таблицу были занесены данные по 1000 учеников.
Найдите средний тестовый балл учеников, которые проходили тестирование по информатике. Ответ запишите в ячейку h4 таблицы с точностью не менее двух знаков после запятой.
Решение.
1 способ.
Для определения среднего балла можно использовать функцию СРЗНАЧ, но для этого надо в таблице «выделить» учеников по информатике, что возможно за счёт функции ЕСЛИ, в которой будет условие на поле предмет «=информатика», а значение, которое будет записываться в один из соседних столбцов, будет равно значению в поле балл.
Составим функцию в ячейке E2.
Получим формулу =ЕСЛИ(C2=»информатика»;D2)
Растянем формулу на весь диапазон. Теперь везде где предмет «информатика», в столбце E стоят баллы, в других местах ЛОЖЬ.
Теперь в ячейку Н3 осталось записать функцию для нахождения среднего значения в столбце Е — =СРЗНАЧ(E2:E1001)
2 способ.
Среднее значение – это сумма аргументов, делённая на их количество.
Для начала надо аналогично первому способу воспользоваться функцией ЕСЛИ, затем посчитать сумму значений в столбце Е и при помощи СЧЁТЕСЛИ определить количество таких значений.
Для этого в ячейку G1 запишем формулу =СУММ(E2:E1001).
В ячейку G2 составим формулу СЧЁТЕСЛИ с проверкой значений столбца Е на то, что они не ЛОЖЬ.
Получившаяся формула =СЧЁТЕСЛИ(E2:E1001;»>0″)
Теперь остаётся просто разделить одно значение на другое.
Построение графиков и диаграмм.
Для построения графиков и диаграмм в электронных таблицах надо воспользоваться специальным меню Вставка полем Диаграммы.
При построении диаграммы необходимо выбрать диапазон, по которому она будет составляться. Это можно сделать сразу: выделяется диапазон – затем используется вставка диаграммы или через «выбрать данные» в меню уже готовой диаграммы.
При построении диаграммы автоматически формируется легенда и название.
Для изменения любых из параметров отображения можно пользоваться меню, вызываемым через нажатие правой кнопкой мыши или конструктором.
Для изменения подписей необходимо открыть диалоговое окно выбора источника данных.
Для добавления подписи данных на диаграмму можно воспользоваться полем из конструктора или нажать правой кнопкой мыши на диаграмму.
Функция ПОИСКА Excel
Главная » Встроенные функции Excel » Текстовые функции Excel » Функция поиска Excel
Связанная функция:
Функция НАЙТИ
Описание функции указанный символ или подстрока в предоставленной текстовой строке.
Функция не чувствительна к регистру. Если вы хотите выполнить поиск с учетом регистра, используйте вместо этого функцию Excel Find.
Синтаксис функции поиска:
ПОИСК( текст_поиска, внутри_текста, [начальный_номер] )
Где аргументы функции:
| текст_поиска | — | Символ или подстрока, которую вы хотите найти. |
| внутри_текста | — | Текстовая строка для поиска. |
| [start_num] | — | Необязательный аргумент, указывающий позицию в строке inside_text, с которой должен начинаться поиск. Если опущено, принимает значение по умолчанию 1 (т. е. поиск начинается с начала строки внутри_текста) |
Подстановочные знаки
Вы также можете использовать следующие подстановочные знаки в аргументе search_text:
? — соответствует любому одиночному символу
* — соответствует любой последовательности символов
, например.
условие «A*e» будет соответствовать любой подстроке, начинающейся с «A» и заканчивающейся на «e».
Если предоставленный текст_искания найден, функция поиска возвращает число, представляющее его позицию в пределах_текста. Если предоставленный search_text равен не найдено , функция возвращает Excel #ЗНАЧ! ошибка.
Примеры функции поиска
В столбце B следующей электронной таблицы показаны примеры функции поиска Excel, используемой для поиска различных символов в текстовой строке «Исходный текст».
Формулы:
| Результаты:
|
ячейки B1 и B2).| #ЗНАЧ! | — | Происходит, если:
|
Вернуться на страницу текстовых функций Excel
Вернуться на список всех встроенных функций Excel может помочь вам найти одну строку (или символ) внутри другой строки. Результатом функции поиска является число, равное начальной позиции подстроки внутри входной строки.
Функция поиска в сочетании с промежуточной функцией может использоваться как мощный метод подстроки.
Оглавление
Как Microsoft Excel определяет функцию поиска
Microsoft Excel определяет функцию поиска как «она возвращает номер символа, под которым впервые найден определенный символ или текстовая строка, читая слева направо».
Это означает, что формула поиска сначала просматривает входную строку слева направо, а затем сообщает о первом экземпляре искомого символа или подстроки.
Синтаксис функции поиска в Excel
Синтаксис функции поиска:
=ПОИСК(подстрока, текстовая_строка, [начальная_позиция])
Здесь «подстрока» — это строка, которую вы хотите найти.
‘text_string’ — это строка, в которой вы хотите выполнить поиск.
«start_position» — это позиция в «text_string», с которой начнется поиск. Это необязательный аргумент. Если его не указать, поиск начнется с позиции 1.
Некоторые важные факты о формуле поиска Excel
- Функция поиска нечувствительна к регистру. Это означает, что функция поиска не знает разницы между «D» и «d».
- Если «подстрока» не найдена внутри «текстовой_строки», функция поиска вернет #ЗНАЧ! ошибка.
- Функция поиска в Excel также поддерживает операторы подстановки. Мы увидим пример этого позже.
- Если start_position меньше 1 или больше длины text_string, то #ЗНАЧ! возвращается ошибка.
Пример формулы поиска в Excel
Ниже приведен пример функции поиска в Excel
В приведенном выше примере я использовал 5 различных функций поиска и буду объяснять их одну за другой:
1. В первая функция, т.е.
=ПОИСК("a",A1)
Формула ищет символ «a» внутри строки, присутствующей в позиции A1, т.е. «DateTime». Результат равен 2, потому что «а» присутствует во второй позиции в строке.
2. Во второй формуле, т.
е.
=ПОИСК("e",A2)
Аналогично первому примеру ищется символ «e» в строке «DateTime». Затем функция возвращает позицию первого экземпляра «e», которая равна 4.
3. В третьей формуле, т. е.
=ПОИСК("e",A3,5)
символ «e» в тексте «DateTime», но поиск начинается с позиции № 5, он встречает «e» на 8 -я позиция и, следовательно, результат равен 8.
4. В четвертом примере, т. е.
=ПОИСК("z",A4)
Строка «z» ищется в тексте «ДатаВремя», но поскольку «z» отсутствует в «DateTime», поэтому функция поиска показывает #VALUE! Ошибка.
5. В последнем примере, т. е.
=ПОИСК("a",A5,-1)
Поскольку начальная позиция меньше 1, функция возвращает #ЗНАЧ! Ошибка.
Использование подстановочного знака в функции поиска
Интересно знать, что вы также можете использовать подстановочные знаки в формуле поиска.
