Вопрос или проблема
Функция SUMIF – Служба поддержки Office говорит только:
критерий Обязательный. Критерий в форме числа, выражения,
ссылки на ячейку, текста или функции, который определяет, какие ячейки будут
суммироваться. Например, критерий может быть выражен как 32, “>32”, B5, “32”,
“яблоки” или TODAY().
Что совершенно не дает представления о том, какие входные данные возможны и какова их синтаксис и семантика. (Например, если это функция или выражение, как отметить точку, где должно быть вставлено значение ячейки?)
Сайты третьих сторон, например https://exceljet.net/excel-functions/excel-sumif-function и https://www.myonlinetraininghub.com/excel-wildcards-in-your-sumif-countif-and-vlookup, дают некоторые примеры возможных входных данных — которые, как оказалось, включают подстановочные знаки (с нестандартной разметкой, к тому же), о которых в документации ничего не сказано. Но они не могут гарантировать, что их список полон, и к каким версиям Excel применим каждый пункт.
Доступна ли более исчерпывающая документация?
Уточнение: я спрашиваю конкретно об официальных источниках (только они могут называться “документацией”, но, видимо, это было непонятно). Мне нужно быть уверенным в том, что у меня полная и точная информация, и ни один источник третьей стороны не может этого гарантировать (если только это не результат обратной разработки соответствующей функциональности Excel или что-то подобное).
Например, я подозреваю, что эта функция “критерий” является общей для нескольких функций Excel, и существует какая-то специальная статья об этом, которую я мог бы легко найти, если бы только знал ключевые слова для поиска (ничего подобного не найдено по “criteria”).
Похоже, что полная спецификация для критериев SUMIF, включая синтаксис, следующая:
- Числовое значение (которое может быть целым числом, десятичным, датой, временем или логическим значением) (например, 10, 01/01/2018, TRUE) или
- Ссылка на ячейку или
- Текстовая строка (например, “Текст”, “Четверг”) или
- Функция (например,
TODAY()) или - Выражение (например, “>12”, “<>0”).
- Любые текстовые критерии или любые критерии, включающие логические или математические символы, должны быть заключены в двойные кавычки (“). Если критерий является числовым значением, двойные кавычки не обязательны.
- Вы можете использовать подстановочные символы — вопросительный знак (?) и звездочку (*) — в аргументе критериев. Вопросительный знак соответствует любому одиночному символу; звездочка соответствует любой последовательности символов. Если вы хотите найти фактический вопросительный знак или звездочку, введите тильду (~) перед символом.
Похоже, это полное исчерпывающее описание. Некоторые источники более описательные, чем другие, для конкретных пунктов. Я сравнил многочисленные источники с документацией Microsoft. Это содержит, по-видимому, самые описательные версии компонентов, которые в основном пришли из Служба поддержки Microsoft и Институт корпоративных финансов. Ссылка на службу поддержки Microsoft действительно содержит все, включительно с информацией о подстановочных знаках.
Что касается того, к каким версиям Excel это относится, я не полагаю, что что-либо из этого изменилось, по крайней мере, в любой версии, которая, вероятно, все еще используется (т.е. по крайней мере, начиная с версии 2007).
Есть некоторые вещи, которые нельзя сделать с SUMIF. Они не включены в список того, что можно сделать, но для полноты:
- SUMIF не является одной из функций Excel, для которых критерии могут использовать константы массивов.
- Вы не можете использовать SUMIF с логическими булевыми операторами, такими как OR и AND.
- Когда вы хотите использовать несколько условий, константы массивов, вложенные операторы IF или булевые операторы; или хотите получить одну сумму для нескольких условий (критериев); используйте две функции SUM и IF вместе. Например, используйте SUM(IF()) вместо функции SUMIF().
.
Ответ или решение
Для прояснения работы с критериями функций Excel COUNTIF и SUMIF, давайте более детально разберем особенности использования данных функций и критериев ввода.
Теоретическая основа
Функции COUNTIF и SUMIF являются частью мощных инструментов Excel, предназначенных для работы с данными, под-считывая или суммируя их на основании заданных критериев. Эти функции значительно упрощают процесс анализа данных, позволяя пользователю поймать условные расчёты.
COUNTIF и SUMIF: что это?
- COUNTIF: Функция, используемая для подсчета числа ячеек, которые удовлетворяют определенному критерию в диапазоне.
- SUMIF: Функция, которая суммирует значения в диапазоне, если заданные для них критерии выполняются.
Критерии для использования в функциях
Есть несколько форматов данных, которые можно применять в качестве критериев:
-
Числовые значения (например, 10, 01/01/2018, TRUE): Простые числа, дата или время могут быть использованы напрямую.
-
Ссылки на ячейки (например, B5): это позволяет сделать оценку на основе данных, лежащих в другой ячейке.
-
Текстовые строки (например, "Text", "Thursday"): текст должен находиться в двойных кавычках.
-
Функции (например,
TODAY()): используются для динамического вычисления данных. -
Выражения (например, ">12", "<>0"): применяются для построения логических или математических условий.
-
Подстановочные знаки: Ваша функция станет гибче за счет использования знаков вопроса (?) и звездочек (*), обозначающих соответственно любой один символ или любую последовательность символов. Для поиска этих подстановочных знаков используются тильды (~).
Примеры критериев
Пример использования числового значения:
=COUNTIF(A1:A10, 10)Этот пример считывает количество раз, когда значение "10" встречается в диапазоне A1:A10.
Пример с условием больше чем:
=SUMIF(A1:A10, ">20", B1:B10)Эта формула суммирует значения из диапазона B1:B10, если соответствующие ячейки в A1:A10 больше 20.
Применение на практике
Почему важно правильное понимание критериев в COUNTIF/SUMIF?
-
Эффективность анализа: точное задавание условий позволяет избежать ошибок в анализе, повышая точность итоговых данных.
-
Гибкость расчетов: использование функций и выражений позволяет адаптировать формулы под динамические условия, такие как изменение дат или значений.
-
Универсальность решений: возможность использования подстановочных символов позволяет строить универсальные решения при обработке данных с достаточно сложной структурой.
Документация и ограничения
Несмотря на то, что официальная документация Microsoft может показаться неполной в отношении критериев, понимание базовых принципов и эксперименты с парой функций приведут к более глубинному пониманию их возможностей и ограничений.
Что SUMIF и COUNTIF не могут сделать:
-
Массивные константы. Эти функции не поддерживают массивные константы в критериях.
-
Логические операторы: Такие операторы, как AND и OR, не могут быть использованы напрямую в SUMIF или COUNTIF.
-
Множественные условия одновременно: В случаях, когда необходимо применять несколько условий одновременно, рекомендуется использовать совместно функции SUM и IF, используя массивы.
Для более сложных случаев более подходящей может быть комбинация SUM(IF()), где вложенная формула IF() помогает управлять несколькими сложными условиями.
Заключение
Подытоживая, можно с уверенностью сказать, что функции COUNTIF и SUMIF в Excel предоставляют мощные возможности для анализа данных, предусматривающие гибкость задавания критериев. Изучение и оптимизация использования этих функций формируют надежный фундамент для более расширенной аналитики данных. Также важно регулярно обращаться к официальной документации и получать актуальные обновления возможностей функций, чтобы оставаться в курсе новаций программного обеспечения. Это помогает значительно повысить вашу эффективность и упростить множество рутинных задач при работе с большими объемами данных.