Один из распространенных вопросов на доске объявлений - как использовать функцию SumIf с двумя разными условиями. К сожалению, ответ состоит в том, что SumIf не может обрабатывать два разных состояния.
Чтобы выполнить два условия, вам нужно использовать довольно сложную формулу массива. Надстройка мастера условной суммы позволяет с легкостью вводить эти сложные формулы.
Вот таблица Excel со столбцами для продукта, торгового представителя и продаж. Данные находятся в ячейках A2: C29.
Если вы хотите подсчитать продажи, подойдет простая функция СУММ (). =SUM(C2:C29).
Многие мастера открывают для себя функцию SumIf. Используя эту функцию, довольно легко подсчитать общий объем продаж для продукта ABC.=SUMIF(A2:A29,E2,C2:C29)
Также легко подсчитать общий объем продаж торгового представителя Джо =SUMIF(B2:B29,E2,C2:C29).
Тогда вы предположите, что можно подсчитать общий объем продаж продукта ABC, произведенного Джо. Однако с помощью функции SumIf это невозможно сделать. Оказывается, нужно использовать достаточно сложный массив или формулу CSE.
Посмотрим правде в глаза - формула Sum - это Excel 101. Формула SumIf не сильно отстает по сложности. Однако формулы CSE для расчета общего объема продаж ABC, сделанных Джо, достаточно, чтобы даже у меня закружилась голова.
Хорошая новость - Microsoft предлагает мастер условной суммы, который позволяет даже новичку вводить сложные условные формулы на основе 1, 2 или более условий. Мастер условной суммы - это надстройка. Чтобы добавить эту функцию в Excel, перейдите в меню «Инструменты» и выберите «Надстройки». В диалоговом окне надстроек установите флажок рядом с мастером условной суммы и нажмите кнопку ОК. Возможно, на этом этапе вам может потребоваться установочный компакт-диск, поскольку Microsoft не включает мастер в установку по умолчанию.
После успешного включения надстройки в нижней части меню «Инструменты» появится вариант «Условная сумма…».
Выделите одну ячейку в наборе данных и выберите Инструменты - Условная сумма. Предполагая, что ваши данные красиво отформатированы с помощью одной строки заголовков, Excel правильно определит диапазон ваших данных. Выберите "Далее".
На шаге 2 выберите столбец для суммирования. В этом случае мастер уже догадался, что вы хотите просуммировать первый (и единственный) числовой столбец - Продажи. В середине диалогового окна находятся три раскрывающихся элемента управления. Они оказались верными для первого условия - продукт равен ABC, поэтому нажмите кнопку «Добавить условие».
Затем вы можете добавить свое второе условие. В этом случае вы хотите указать, что торговый представитель - Джо. Выберите стрелку для первого раскрывающегося списка. Excel предлагает алфавитный список доступных имен столбцов. Выберите Торговый представитель.
Центральное раскрывающееся меню правильно, но для полноты здесь вы можете видеть, что вы могли выбрать равный, меньший, больший, меньший или равный, больший или равный или не равный.
В третьем раскрывающемся списке выберите Джо.
Нажмите кнопку «Добавить условие».
Теперь вы готовы перейти к шагу 3. Нажмите кнопку «Далее».
На шаге 3 у вас есть два варианта. При первом выборе мастер введет единую формулу со значениями «ABC» и «Joe», жестко запрограммированными в формулу. Он даст вам ответ, но не будет возможности легко изменить формулу. При втором выборе Excel создаст новую ячейку со значением «ABC» и новую ячейку со значением «Joe». Третья ячейка будет содержать формулу, которая вычисляет условную сумму на основе этих двух значений. С помощью этой опции вы можете вводить новые значения в ячейки, чтобы увидеть общее количество XYZ, проданных Адамом.
Затем мастер спросит, где вы хотите установить значение ABC. Выберите ячейку и нажмите Далее. Повторите эти действия, пока мастер просит вас выбрать ячейку для Джо и формулы.
Если вы выберете «Готово» на последнем шаге, Excel создаст немного другую (но действительную) версию формулы CSE.
По этой формуле Джо продал ABC на 33 338 долларов.
Если вы измените ячейку ввода продукта с ABC на DEF, формула будет пересчитана, чтобы показать, что Джо продал DEF на 24 478 долларов.
Мастер условной суммы делает сложные формулы доступными для всех владельцев Excel.
Дополнительная информация:Если вы хотите создать таблицу, в которой будут отражены продажи каждого продукта каждым торговым представителем, вам необходимо знать об этих формулах особую «заботу и кормление». Введите каждого торгового представителя в верхней части диапазона. Введите каждый продукт в левый столбец диапазона. Отредактируйте формулу, предоставленную мастером. На изображении ниже формула указывает продукт в ячейке E6. Эта ссылка действительно должна быть $ E6. Если вы оставите ссылку как E6 и скопируете формулу в столбец G, формула будет смотреть на F6 вместо E6, и это будет неправильно. Добавление знака доллара перед E в E6 гарантирует, что формула всегда учитывает продукт в столбце E. Формула также указывает на торгового представителя в ячейке F5. Эта ссылка действительно должна быть 5 F $. Если вы оставили ссылку как F5 и скопировали до строки 7,ссылка F5 изменится на F6, и это неправильно. Добавление знака доллара перед номером строки заблокирует номер строки, и ссылка всегда будет указывать на строку 5.
В режиме редактирования (выберите ячейку и нажмите F2 для редактирования) введите $ перед E. Введите знак доллара перед 5 в F5. Пока не нажимайте Enter!
Эта формула представляет собой особый тип формул. Если вы нажмете Enter, вы получите 0, что неверно.
Вместо того, чтобы вводить Enter, удерживайте клавиши Ctrl и Shift при нажатии Enter. Эта волшебная комбинация C trl + S hift + E nter - вот почему я называю эти формулы CSE.
Прежде чем копировать формулу в остальную часть таблицы, необходимо учесть еще одно соображение. Вы можете скопировать F6 и вставить в F6: G8. Если вы попробуете это сделать, Excel выдаст загадочное сообщение «Вы не можете изменить часть массива». Excel жалуется, что вы не можете вставить формулу CSE в диапазон, содержащий исходную формулу CSE.
Это легко обойти. Скопируйте F6. Вставить в F7: F8.
Скопируйте F6: F8. Вставить в G6: G8. У вас будет таблица формул CSE, показывающая итоги на основе двух условий.
