Коллеги, добрый день. В этом видео мы продолжим обсуждать функции суммирования и подсчета, с которыми начали знакомиться ранее. Но будем говорить об их немного более сложных вариациях, позволяющих производить расчеты с учетом заданных нами условий, и исходя из своего опыта и опыта мои слушателей, моих клиентов, могу сказать, что это одни из самых полезных и чаще всего применяемых функций как в Google таблицах, так и в Excel, они совпадают в обеих программах. Их можно использовать для решения множества задач. Давайте озвучу лишь несколько примеров: рассчитать сумму платежей только по одному контрагенту или только по одной статье расходов; вычислить сумму всех платежей, выплаченных только по одной статье расходов и только за определенный период, или вычислить среднее значение по выбранному каналу продаж и типу товаров; найти средние остатки по одной из товарных категорий; вычислить количество сделок выше определенной суммы и закрытых после определенной даты и так далее. Примеров можно приводить очень много. Если у вас есть одно или несколько условий, вам нужно рассчитать сумму, среднее значение или количество по этим условиям. Вам нужна одна из этих функций, которые мы будем обсуждать в этом видео. Давайте посмотрим на эти функции вот в таком наглядном разрезе. Ранее мы с вами уже познакомились с функциями: СЧЕТ, СУММ с СРЗНАЧ. У каждой из них есть версии со словом If на английском или ЕСЛИ на конце. Это слово означает, что расчет ведется не по всем аргументам, а только по тем, которые соответствуют заданному условию. Если вспоминать наш пример с продажами книг, то можно было рассчитать сумму продаж не всех книг, а только по одному издательству, это условие. Ну а функция, оканчивающаяся на ЕСЛИМН или IFS, позволяет вести расчет по двум и более условиям, например, сумма остатков товаров только одной категории и только на одном складе. Итак, давайте перейдем к практике и посмотрим, как работают эти функции на примере нашей таблицы. В таблице у нас есть: способ оплаты, категория расхода, дата, вид платежа и сумма платежа. Вот такие поля, поработаем с функциями, которые мы перечислили с условиями. Начнем мы с функции СУММЕСЛИ по одному условию. Эта функция аналогична СУММ в том смысле, что она рассчитывает сумму всех своих аргументов, но накладывается одно условие. В нашем примере давайте рассчитаем сумму всех расходов на рекламу и маркетинг. Начнем вводить функцию СУММЕСЛИ или SUMIF, если бы у нас были английские формулы. У нее несколько аргументов, давайте посмотрим: диапазон, условия и сумма диапазона. И так столбец, как правило, это столбец Диапазон — это тот диапазон, в котором находится значение с нашим условием, то есть раз у нас задача рассчитать сумму по рекламе и маркетингу, то диапазон — это столбец B, столбец категории расходов. Выделяем его, ставим точку с запятой, и далее нам нужно указать условия, наши условия — это расходы на рекламу и маркетинг. Условия указываются двумя способами: можно открыть кавычки и написать: "реклама и маркетинг". Ровно так, как это условие указано в нашей исходной таблице, это один из вариантов. Второй вариант — сослаться на ячейку в которой это указано, например на B32, где написано "реклама и маркетинг", оба условия одинаково работают. Наконец третий аргумент функции СУММЕСЛИ — суммы диапазона, а что собственно суммируется, то есть столбец B — это тот столбец, по которому мы фильтруем, что называется, мы в нем ищем только рекламу и маркетинг, нужные нам платежи. Суммировать мы будем столбец E с числовыми данными. Нажмем Enter и получим результат: 583 450 — это сумма всех расходов только на рекламу и маркетинг. Условия мы взяли из ячейки B12, но, как я говорил, мы можем его указывать, также в кавычках ввести текст условия "реклама и маркетинг", и соответственно результат не изменится. И так, давайте теперь перейдем к функции СЧЕТЕСЛИ, какие задачи она может решать? Например, посчитать количество платежей, уже не сумму платежей, а количество платежей на сумму более 4000. То есть условия, могут быть не только текстовыми — реклама и маркетинг, но, например, условие может быть и числовым. Сумма платежа более 4000, давайте посмотрим: функция СЧЕТЕСЛИ или COUNTIF, если бы была английская версия. У нее два аргумента, так как суммировать нечего, то диапазон суммирования отсутствует. Есть только диапазон и условия. Если мы хотим посчитать количество платежей на сумму более 4000, выделяем столбец с суммами платежей — это столбец E. И далее указываем условие, которое записывается в кавычках и с арифметическими знаками, с математическим знаком «больше» и числом: > 4000, то есть это условие читается как «больше 4000», а формула, которую мы видим на экране, означает, что нужно посчитать количество ячеек в столбце E, в которых числа больше 4000. Нажимаем Enter, получаем результат: у нас 222 платежа в таблице на суммы более 4000 рублей. Так, давайте теперь попробуем решить более сложную задачу. Например, рассчитать среднюю величину платежей за рекламу и маркетинг, совершенных после первого января 2018 года. Что видим в этой задаче? Во-первых, средняя величина, то есть это не СЧЕТ и не СУММ, а СРЗНАЧ. Во-вторых, условий два, обратите внимание: маркетинг и реклама после первого января 2018 года, соответственно это СРЗНАЧ ЕСЛИМН, то есть среднее значение по двум и более условиям. И так, введем эту функцию и посмотрим на ее синтаксис. Первым идет усредняемый диапазон. То есть диапазон с числами, то что мы собственно усредняем. Ну, усреднять здесь можно только сумму, столбец E, выделяем его и далее, через точку с запятой, идут диапазоны, на которые накладываются условия и сами эти условия попарно, то есть столбец с условием, условие, еще один столбец с условием и еще одно условие. И так попарно может быть два, три и более условий. У нас, напомню, их два: это расходы на рекламу и маркетинг — первое условие; после первого января 2018 — это второе условие. Первое: на столбец B — категория, я выделяю этот столбец и через точку с запятой укажу рекламу и маркетинг, то есть наше условие. Далее, через точку с запятой нужно выделить еще один диапазон с условием. Напомню, мы решили усреднять по дате так же, то есть, посчитать только платежи после первого января 2018 года, по этому я выделяю столбец C и через точку с запятой укажу условия. После такого-то числа — это тоже знак «больше», а если бы было до какого-то числа, то, наоборот, был бы знак «меньше». Если нам нужно с первого января включительно, то нужно также поставить знак «равно», то есть больше либо равно с первого января. Указываем дату в таком формате 01.01.2018 и закрываем кавычки, в которых указываются наши условия. Закрываем скобку и давайте еще раз посмотрим на эту функцию. Ее первый аргумент — это диапазон усреднения, то есть суммы платежей, по которым нужно получить среднее, и далее попарно идут условия. Первое условие: столбец с категорией расходов, и после него идет через точку с запятой аргумент, название категорий — реклама и маркетинг. Далее, следующий аргумент — это столбец с датой, и после него через точку с запятой идет условие: больше либо равно такой-то даты, то есть начиная с первого января 2018 года включительно. Вот эта формула рассчитывает среднее столбца E по двум этим условиям, на дату и на категорию. Нажимаем Enter, получаем в ячейке результат 3505 — это соответственно среднее значение по платежам по нашим условиям. Обращу ваше внимание, что условия, еще раз, можно брать не только из, не только указывать в функции, но и брать из ячеек. То есть, например, можно в ячейке указать условие 3500 и, например, подсчитывать сумму платежей больше этого числа, то есть мы указываем в СУММЕСЛИ диапазон E и указываем на него условия. Открываем кавычки, ставим знак >. И чтобы брать число из ячейки, нужно закрыть кавычку и поставить знак амперсанд, знак И, который соединяет текст в кавычках и ячейку. То есть, мы объединяем знак > и число из ячейки I3, и это позволяет нам динамически менять условия. То есть смотрите, теперь, если я передумал и хочу посчитать сумму платежей от 5000, я просто меняю в ячейке условие, и сумма пересчиталась автоматически, она стала меньше таких платежей. Это добавляет интерактивности таблицам, то есть в моей практике было много случаев, когда вот таким простым методом, когда значение условий задаются в ячейках, создаются такие простенькие панели показателей, в которых можно быстро поменять условия и получить результат. И так, коллеги, в этом видео мы с вами обсудили как использовать функцию СРЗНАЧ ЕСЛИ, СЧЕТЕСЛИ и СУММЕСЛИ для ведения расчетов в тех случаях, когда нужно получить сумму средней или количества, по тем или иным критериям, по одному или более условиям.
