Суммировать, если ячейка содержит определенный текст с использованием подстановочных знаков — Excel и Google Таблицы
В этом руководстве будет показано, как использовать функцию СУММЕСЛИМН и подстановочные знаки для суммирования данных, соответствующих ячейкам, содержащим определенный текст в Excel и Google Таблицах.
Сначала мы продемонстрируем, как суммировать данные, относящиеся к ячейкам, содержащим определенный текст, с помощью функции СУММЕСЛИМН.
Функция СУММЕСЛИМН суммирует строки данных, которые соответствуют определенным критериям. Его синтаксис:
Этот пример суммирует все Очки с Название штата который содержит «Дакота» с использованием функции СУММЕСЛИМН и подстановочного знака *.
Символ * позволяет использовать любое количество (включая ноль) других символов.
В этом примере он используется для поиска всех ячеек, содержащих текст «Дакота». Этот поиск не чувствителен к регистру, поэтому «дакота» считается тем же, что и «Дакота» или «ДАКОТА». Северная Дакота а также Северная Дакота содержат «Дакота» и поэтому включены в сумму.
Сумма, если текст начинается с
Символ * также можно использовать для поиска ячеек, которые начинаются с указанного текста:
Нью-Йорк, Нью-Джерси, а также Нью-Мексико начинаются с «Новый» и поэтому включаются в сумму. Обратите внимание, что при поисковом запросе «Новый *» текстовая ячейка должна начинаться с «Новый»; просто содержать эти символы недостаточно.
Сумма, если текст заканчивается
Точно так же мы можем просуммировать все Очки для состояния оканчивается на «o», используя:
Нью-Мексико а также Огайо оканчиваются на «о» и поэтому включаются в сумму.
Как посчитать слова в Excel
Обратите внимание, что * и? Подстановочные знаки можно комбинировать, если требуется для создания очень конкретных команд поиска. Следующий пример находит Имена штатов которые начинаются с «N» и содержат «о» перед последним символом строки. Это исключает Нью-Мексико; он начинается с «N», но не имеет «о» перед последним символом.
Расширенный поиск
Предположим, что требуется найти все значения в диапазоне от 3000 до 3999. В этом случае в строке поиска следует набрать 3. Подстановочный знак «?» заменяет собой любой другой.
Анализируя результаты произведённого поиска, можно отметить, что, наряду с правильными 9 результатами, программа также выдала неожиданные, подчёркнутые красным. Они связаны с наличием в ячейке или формуле цифры 3.
Можно удовольствоваться большинством полученных результатов, игнорируя неправильные. Но функция поиска в эксель 2010 способна работать гораздо точнее. Для этого предназначен инструмент «Параметры» в диалоговом окне.
Щёлкнув «Параметры», пользователь получает возможность осуществлять расширенный поиск. Прежде всего, обратим внимание на пункт «Область поиска», в котором по умолчанию выставлено значение «Формулы».
Это означает, что поиск производился, в том числе и в тех ячейках, где находится не значение, а формула. Наличие в них цифры 3 дало три неправильных результата. Если в качестве области поиска выбрать «Значения», то будет производиться только поиск данных и неправильные результаты, связанные с ячейками формул, исчезнут.
Для того чтобы избавиться от единственного оставшегося неправильного результата на первой строчке, в окне расширенного поиска нужно выбрать пункт «Ячейка целиком». После этого результат поиска становимся точным на 100%.
Такой результат можно было бы обеспечить, сразу выбрав пункт «Ячейка целиком» (даже оставив в «Области поиска» значение «Формулы»).
Если вместо установленного по умолчанию «На листе» выбрать значение «В книге», то нет необходимости находиться на листе искомых ячеек. На скриншоте видно, что пользователь инициировал поиск, находясь на пустом листе 2.
Следующий пункт окна расширенного поиска – «Просматривать», имеющий два значения. По умолчанию установлено «по строкам», что означает последовательность сканирования ячеек по строкам. Выбор другого значения – «по столбцам», поменяет только направление поиска и последовательность выдачи результатов.
При поиске в документах Microsoft Excel, можно использовать и другой подстановочный знак – «*». Если рассмотренный «?» означал любой символ, то «*» заменяет собой не один, а любое количество символов. Ниже представлен скриншот поиска по слову Louisiana.
Иногда при поиске необходимо учитывать регистр символов. Если слово louisiana будет написано с маленькой буквы, то результаты поиска не изменятся. Но если в окне расширенного поиска выбрать «Учитывать регистр», то поиск окажется безуспешным. Программа станет считать слова Louisiana и louisiana разными, и, естественно, не найдёт первое из них.
Поиск в Экселе – комбинация клавиш, какая формула соответствует, как искать по нужному слову, фразе, одному или нескольким значениям в Excel
Позиции с единицами показывают, какие из строк содержат фрукты, начинающиеся на букву а. Как видите, остальные цифры также указывают на местоположение искомой буквы в остальных позициях диапазона. Однако, одна ячейка содержит ошибку ЗНАЧ!. Эта проблема возникает в двух случаях, при использовании функции ПОИСК:
Работа с вкладками
То же самое окно можно открыть без клавиатуры. Для этого пройдите на вкладку — главная и справа в шапке увидите — найти и выделить.
Нажав на данный блок, вы откроете список и выбирайте нужное значение, в данном случае — найти. откроется то же самое окно, для поиска и замены слов и фраз.
Поисковые параметры
Например указав в поиске цифру 3, вы получите в результате все ячейки, где содержится 3, но при этом в ячейке будут присутствовать и другие цифры, слова и значения (35, 38 и так далее). А установив галочку вы получите в результатах только ячейки с цифрой 3.