ifempty

    Урок 6. Функция СУММПРОИЗВ

    Введение

    Функция СУММПРОИЗВ является по определению формулой массива. Но в отличие от классических формул массива при использовании СУММПРОИЗВ не нужно нажимать сочетание клавиш CTRL +Shift +Enter.

     В данном уроке будут рассмотрены следующие способы использования функции СУММПРОИЗВ:

    1. Стандартное использование;
    2. Подсчет количества выполненных условий;
    3. Проверка нескольких условий;
    4. Использование связок И и ИЛИ;
    5. Обращение к закрытому файлу.

    Стандартное использование функции СУММПРОИЗВ

     Скачайте файл СУММПРОИЗВ  - Приложение 1 по ссылке выше. Откройте лист Задача без решения. На дисте дана таблица с товарами, нужно в ячейке В10 определить общую сумму продаж.  Аналогичная задача рассматривалась в предыдущем уроке Формулы массива, однако, в этот раз решим эту задачу без сочетания клавиш CTRL + Shift + Enter.

    Становимся на ячейке В10 и вызываем функцию СУММПРОИЗВ. В качестве Массива 1 будет выступать графа Кол-во, а в качестве Массива 2  - графа Цена. смотрите рисунок 1:

     функция суммпроизв рис1

    Рисунок 1. Стандартное использование функции СУММПРОИЗВ - произведение массивов

     

    Подсчет количества выполненных условий 

    Скачайте файл Функция СУММПРОИЗВ - Приложение 1 по ссылке выше. Откройте лист Задача без решения.На листе дана информация по исполнению плана поставок по товарам по разным городам. В ячейке В19 нужно определить количество городов, по которым план и факт равны.

    Для этого нужно встать на ячейку В19 и написать следующую формулу:

    =СУММПРОИЗВ((C4:C17=D4:D17)*1)

    Смотрите рисунок 2:

     функция суммпроизв рис2

    Рисунок 2. Применение условия в функции СУММПРОИЗВ

    Умножение на 1 нужно, чтобы значения ИСТИНА или ЛОЖЬ быти 1 или 0 соответственно.

     

    Проверка нескольких условий

     

    Скачайте файл Функция СУММПРОИЗВ  - приложение 3 по ссылке выше. Откройте лист Задача без решения.  На листе дана таблица по исполнению плана поставок по городам и областям. В ячейке В22 нужно определить общую сумму фактических поставок по Карагандинской области, по городам, которые выполнили план.

    Для этого в ячейке В22 нужно вызвать функцию СУММПРОИЗВ. Смотрите рисунок 3:

     функция суммпроизв рис3

    Рисунок 3. Использование нескольких условий в функции СУММПРОИЗВ

    Формула:

    =СУММПРОИЗВ((A4:A20="Карагандинская область")*(D4:D20>=C4:C20)*D4:D20)

     Использование связок И и ИЛИ

     Знак звездочки * является в сути своей оператором И. В случае когда нужно использовать ИЛИ вместо звездочки, нужно ставить знак плюс +.

    Рассмотрим пример.

    Скачайте файл Функция СУММПРОИЗВ  - приложение 4. Откройте лист Задача без решения. На листе ткакая же таблица, как и в предыдущем примере. Но задача другая. Теперь в ячейке В24 нужно собрать сумму фактических поставок по городам, по которым выполнен план, и которые относятся к Алматинской и Карагандинской области.

    Решение представлено на рисунке 4:

     функция суммпроизв рис4

    Рисунок 4. Использование операторов И и ИЛИ в функции СУММПРОИЗВ

    Формула 4: 

    =СУММПРОИЗВ((((A4:A22="Карагандинская область")+(A4:A22="Алматинская область"))*(D4:D22>=C4:C22))*D4:D22)

     

    Обращение к закрытому файлу

    Функция СУММПРОИЗВ позволяет работать даже с закрытым файлом. Например:

    Скачайте файл по ссылке выше. Сохраните его в какую - нибудь папку. Затем откройте пустой лист Excel и в ячейку А1 напишите формулу наподобие этой:

    =СУММПРОИЗВ(('F:\e-school.kz\Advanced\СУММПРОИЗВ\[СУММПРОИЗВ - Приложение 5.xlsx]лист1'!A3:A21="Алматинская область")*1)

    Должно получиться 3.

     


    У вас недостаточно прав