Check-moscow.ru

Финансы и учет
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как заменить тильду и подстановочные знаки в Excel?

S EARCH и SEARCHB можно использовать для поиска позиции текстовой строки внутри другой похожей строки. Вычисление производится от первого символа анализируемой ячейки. Например, если задать функцию ПОИСКПОЗ «l» для слова «orange», то получим значение 4, так как это номер указанной буквы в текстовом выражении.

Функция ПОИСК работает не только для поиска местоположения отдельных букв в тексте, но и для всей комбинации. Например, если вы используете эту команду для «book», «notebook», вы получите значение 5, потому что слово «book» начинается с этого символа.

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

  • Find (поиск строчных и заглавных букв);
  • PSTR (возврат текста);
  • REPLACE (замена символов).

Важно помнить, что команда SEARCH, о которой идет речь, не чувствительна к регистру. Если использовать его для поиска позиции буквы «а» в слове «Александр», ячейка отобразит 1, поскольку это первый символ в анализируемой информации. Если мы воспользуемся ПОИСКОМ для буквы «а» в том же сегменте текста, мы получим 6, потому что это 6-я позиция строчной буквы «а» в слове «Alejandro».

Кроме того, функция ПОИСК работает не на всех языках. Она отличается от команды SEARCH тем, что считает 1 байт на символ, в то время как команда SEARCH считает два байта на символ.

Для использования этой функции введите следующую формулу:

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

  1. Текст, который вы ищете. Это числовая и буквенная комбинация, положение которой необходимо найти.
  2. Текст, который необходимо проанализировать. Это фрагмент текстовой информации, из которого вы хотите извлечь искомую букву или комбинацию и вернуть позицию.
  3. Исходное положение. Этот фрагмент не является обязательным. Но если вы хотите найти, например, букву «а» в строке со значением «A015487.menswear», то в конце формулы нужно указать аргумент 8, чтобы разобрать этот фрагмент с восьмой позиции, то есть после артикля. Если этот аргумент не указан, значение по умолчанию равно 1. Если указана начальная позиция, то позиция искомого фрагмента будет продолжать отсчитываться от первого символа, даже если в анализе опущена начальная 8. То есть в данном примере букве «a» в строке «A015487.Menswear» будет присвоено значение 14.

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

  1. Вопросительный знак (?). Он соответствует любому символу.
  2. Звездочка (*). Этот символ соответствует любой комбинации символов.

Чтобы найти похожие символы в строке, поставьте перед ними тильду в аргументе «текст_поиска» (

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

Если искомый_текст не может быть найден, возвращается значение ошибки #HEAVEN.

Читайте так же:
Как вставить комментарии в защищенный лист?

Использование в функциях

Представьте, что задача состоит в том, чтобы подсчитать все склонения слова hammer (например, hammer, hammer, hammer, hammer и т.д.). Самый простой способ решить эту задачу — использовать критерии выбора с подстановочным знаком * (звездочка). В этом случае можно выбрать значение, используя только его часть (в нашем случае молоток); остальное заменяется подстановочными знаками. В качестве примера мы используем молоток в качестве критерия.

Предполагая, что значения в тексте (варианты слова hammer) находятся в диапазоне A2:A10 , пишем формулу =CALCULATE(A2:A10 ; «hammer*»)). В результате будут вычислены все вариации слова hammer в диапазоне A2:A10 .

Следующие функции поддерживают подстановочные знаки:

  • СЧЁТЕСЛИ() (см. статью Подсчет текстовых значений с единственным критерием ) и СЧЁТЕСЛИМН()
  • СУММЕСЛИ() и СУММЕСЛИМН()
  • СРЗНАЧЕСЛИ()
  • В таблице условий функций БСЧЁТ() (см. статью Подсчет значений с множественными критериями ), а также функций БСЧЁТА() , БИЗВЛЕЧЬ() , ДМАКС() , ДМИН() , БДСУММ() , ДСРЗНАЧ()
  • ПОИСК()
  • ВПР() и ГПР()
  • ПОИСКПОЗ()

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

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

Как упоминалось выше, подстановочные знаки в Excel могут использоваться в качестве критериев при сравнении текста в различных функциях Excel (например, CALCUL, SUMMESLY, SUMMESLYN, GPR, VPR и других).

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


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

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


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


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

Тильда (

): используйте этот символ для замены вышеуказанных специальных символов. Например, если вы действительно хотите найти слово «пользователь? (включая вопросительный знак) вы можете набрать user

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

Использование функции replace в Excel

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

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

Читайте так же:
Как добавить или вычесть часы из поля даты / времени в Excel?

Все функции поиска на вкладке «Дополнительно» работают одинаково. Разница лишь в том, что теперь вы увидите поле «Заменить».

  1. В этом поле вводится текст или число, которым вы хотите заменить текст, введенный в поле Найти. Затем выполните следующие действия для поиска и замены всех экземпляров искомого элемента.
  2. Выберите Find Next, чтобы выбрать первое место, содержащее искомый текст или номер.
  3. Выберите Заменить, чтобы заменить этот экземпляр. Если вы нашли вхождение, которое не хотите заменять, просто нажмите кнопку Найти далее, чтобы продолжить.

Продолжайте циклы Find Next и Replace, пока все найденные объекты не будут найдены и замещены.

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

Поиск ячеек, в которых есть текстовая информация

Подробная инструкция приведена ниже:

  1. Выберите нужный диапазон ячеек для сканирования. Чтобы выполнить поиск во всей электронной таблице, щелкните любую ячейку.
  2. Перейдите в подраздел «Главная». В командном блоке с названием «Правка» найдите пункт «Найти и выделить» и щелкните левой кнопкой мыши по пункту «Найти».
  1. В строке «Поиск» введите текстовую информацию, которую нам нужно найти. Обратите внимание, что в поиске можно использовать подстановочные знаки.
  2. Чтобы задать формат поиска, нажмите левой кнопкой мыши на кнопку «Формат», а затем внесите необходимые изменения в появившемся окне под названием «Формат поиска».
  3. Нажмите «Искать далее» или «Искать все».
  4. Вот так! Мы искали необходимую информацию.

Примечание: Чтобы закрыть поиск, используйте кнопку «ESC» на клавиатуре.

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

  1. «*» — абсолютно любое число знаков.
  2. «?» — любой один знак.

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

«. Если вы хотите найти квадраты, введите «

excel-poisk-teksta-v-yachejke

2

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

  1. Начните с главных редакторов.
  2. Откройте электронную таблицу, нажав левую кнопку мыши.
  3. Нажмите Ctrl + F на клавиатуре.
  4. Появится окно, в котором вы можете искать текстовую информацию в редакторе таблиц. В строке «Найти» введите слово, необходимое для поиска, и используйте символы («? «, «*»), если вы сомневаетесь в точности информации.
  5. Снимите флажок напротив «Целая ячейка».
  6. Щелкните левой кнопкой мыши на «Найти все».
  7. Готово! Все текстовые данные, соответствующие параметрам поиска, выделяются на экране.

Поиск нескольких слов в документе

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

  1. Запустите редактор таблиц.
  2. Откройте электронную таблицу, нажав левую кнопку мыши.
  3. Перейдите к подразделу «Редактировать». Найдите здесь пункт «Поиск и выбор» и нажмите левую кнопку мыши.
  4. Если вы хотите найти фразу, введите ее в пустое поле, а затем щелкните левой кнопкой мыши на «Search All». Результаты появятся на экране, где будет отображена точная фраза без использования других склонений.
  5. Если нужная фраза разделена другими словами, необходимо дополнительно поставить звездочку «*» между каждым введенным словом. Результаты будут отображены, даже если между словами есть другие знаки.
Читайте так же:
Как добавить знак плюса к нескольким положительным числам в Excel?

Проверка содержимого, на наличие текстовой информации

Чтобы выполнить эту простую процедуру, используйте специальный оператор ETEXT. На этой иллюстрации показан пример использования оператора:

excel-poisk-teksta-v-yachejke

5

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

Чтобы выполнить эту простую процедуру, используйте специальный оператор IF. Пример использования оператора показан на следующем рисунке:

excel-poisk-teksta-v-yachejke

6

Проверка соответствия наполнения фрагмента поля конкретной текстовой информации

Для выполнения этой процедуры необходимо применить специальные операторы ЧИСЛО, ЕСЛИ и ПОИСК. Обратите внимание, что оператор SEARCH не чувствителен к регистру. На этом изображении показан пример использования :

excel-poisk-teksta-v-yachejke

7

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

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

  1. Начните с главных редакторов.
  2. Откройте электронную таблицу, щелкнув по ней левой кнопкой мыши.
  3. Выберите заполненные ячейки.
  4. Перейдите в раздел «Главная». Здесь найдите элемент «Black» и щелкните его левой кнопкой мыши.
  5. Щелкните левой кнопкой мыши на «Фильтр».
  6. На экране появится небольшое специальное контекстное меню.
  7. Здесь введите значение, которое должно появиться в документе редактора таблиц.
  8. Нажмите левой кнопкой мыши кнопку «OK», когда все будет сделано.
  9. Готово! В результате нашей манипуляции в столбце выделяются ячейки, содержащие текстовую информацию, найденную программой. Если в выпадающем списке нажать «Выбрать все», результаты поиска будут сброшены. Если мы снова щелкнем левой кнопкой мыши на «Сортировать», примененный фильтр будет деактивирован.

COUNTIF Формула в Excel

Формула COUNTIF в excel — это встроенная или инлайновая функция, которая относится к группе статистических формул.

Формула COUNTIF в Excel подсчитывает количество ячеек в заданном массиве или диапазоне на основании определенного критерия или условия.

Ниже представлен синтаксис формулы COUNTIF в Excel:

Формула COUNTIF в Excel имеет два аргумента, а именно область и критерии.

  1. Диапазон: (необходимый и обязательный аргумент) Это диапазон или массив ячеек, к которым будут применены критерии. Массив или диапазон должен иметь имя, например, C1: C12.
  2. Критерии: (необходимый и обязательный аргумент) Это специальное условие, которое будет применено к значениям ячеек, представленных диапазоном ячеек. Это указывает в формуле COUNTIF, какие ячейки должны быть подсчитаны. Формула COUNTIF имеет ограничение с аргументом критерия, т.е. если текстовая строка в аргументе критерия имеет длину более 255 символов или содержит более 255 символов. Затем возвращается # ЗНАЧИТ!
Читайте так же:
Как добавить процент столбца общего / промежуточного итога в сводную таблицу Excel?

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

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

= Равно
<Меньше чем
<= Меньше или равно
> Больше чем
> = Больше или равно
Меньше или больше чем

Как использовать формулу COUNTIF в Excel?

В Excel формула КОУНТИФ очень проста в использовании. Мы рассмотрим несколько примеров того, как работает формула COUNTIF в Excel.

Вы можете скачать этот шаблон формулы Excel COUNTIF здесь — Шаблон формулы Excel COUNTIF

Excel COUNTIF Formula — Пример № 1

Таблица A содержит список сотрудников компании (от A2 до A15) с колонкой B, содержащей их статус перевода (от B2 до B15). Это предполагает подсчет количества переведенных и непереведенных средств в диапазоне, охватываемом набором данных (от B2 до B15).

  • Используем функцию COUNTIF в ячейке D4 . Выберите ячейку D4, в которой вы хотите применить формулу COUNTIF.

  • Нажмите или выберите кнопку «Вставить функцию» (например) на панели инструментов формул, и появится диалоговое окно с формулой COUNTIF в поле «Найти функцию», или введите ключевое слово countif, и формула COUNTIF появится в поле «Выбрать функцию». Дважды щелкните формулу COUNTIF.

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

  • Диапазон: это диапазон ячеек, в которых вы хотите вести подсчет. Здесь диапазон или матрица от B2 до B15, поэтому выберите диапазон столбцов, т.е. B2 : B15.

  • Критерии: это условие или критерий, в который мы помещаем функцию COUNTIF, ячейки которой мы хотим подсчитать, т.е. «Y» (здесь мы хотим узнать количество сотрудников, которые получили определенную сумму или средства). После ввода аргумента «диапазон и критерии» нажмите на кнопку «OK». = COUNTIF (B2: B15, «Y»)

  • Формула COUNTIF возвращает количество сотрудников, получивших фонд, т.е. 8 в диапазоне набора данных ( B2 : B15 ). Здесь формула COUNTIF возвращает число 8 в определенном диапазоне. Это означает, что 8 сотрудников получили средства.

  • Предположим, я хочу узнать количество работников, которые не получили деньги. Мне просто нужно изменить аргумент критерия, т.е. вместо «Y» использовать «N» = COUNTIF (B2: B15, «N»)

  • Формула COUNTIF возвращает количество сотрудников, которые не получили средства, т.е. 6 в диапазоне набора данных (B2: B15). Здесь формула COUNTIF возвращает число 6 в заданном диапазоне. Это означает, что шесть сотрудников не получили никаких средств.
Читайте так же:
Как выделить все ячейки с данными в Excel?

Excel COUNTIF Formula — Пример № 2

С арифметическим оператором в аргументе критерий

Во втором примере таблица содержит имя студента в столбце A (A2 — A15), общее количество набранных баллов в столбце B (B2 — B15) и их процент в столбце C (C2 — C15), здесь мне нужно узнать число или количество студентов, набравших 90% и более в диапазоне набора данных (C2 — C15).

  • Теперь мы можем применить формулу COUNTIF с арифметическим оператором в ячейке E3, т.е. = COUNTIF (диапазон, критерий)

  • Диапазон: это диапазон ячеек, в которых вы хотите вести подсчет. Здесь диапазон или матрица от C2 до C15, поэтому выберите диапазон столбцов, т.е. C2 : C15.

  • Критерий: это условие или критерий, который указывает функции подсчета, какие ячейки считать, например, «> = 90%» (Здесь мне нужно найти число (числа) студентов, набравших 90% или больше в диапазоне набора данных (C2 — C15)).

  • В данном случае формула = COUNTIF (C2 : C15, «> = 90%») возвращает значение 3, то есть количество студентов, набравших 90% и более в интервале набора данных (C2 — C15).

Что нужно помнить о формуле COUNTIF в Excel

Вы также можете использовать подстановочные знаки в формуле Excel COUNTIF (в аргументе критериев) для получения нужного результата.

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

  • Звездочка (*): для соответствия любой последовательности концевых или ведущих символов. Например, если вы хотите сопоставить все ячейки диапазона, содержащие текстовую строку, начинающуюся с «T» и заканчивающуюся «e», вы можете ввести условие или аргумент критерия в виде «T * e».

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

) используется в аргументе условия или критерия как «*

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

Примеры использования звездочек (*)

TOR * Поиск текстов, содержащих или начинающихся с TOR

T OR — поисковая система, которая находит текст, включающий или заканчивающийся TOR

* TOR * Поиск текста, содержащего слово TOR

  • Вопросительный знак (? ): используется для идентификации или следования за отдельным персонажем. Например, если вы хотите проследить за словом «shirt» или словами, заканчивающимися на «S» в примере ниже, вы должны использовать «shirt?» в аргументе «criteria», что приведет к значению 1.

Рекомендуемые статьи

Учебное пособие по формуле COUNTIF в Excel. Здесь мы показываем, как использовать формулу COUNTIF в Excel, а также приводим примеры и загружаемый шаблон Excel. Также смотрите другие предлагаемые нами статьи —

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector