Главная Обратная связь

Дисциплины:






Спосіб 2. Функція СУММЕСЛІМН, коли умов багато



Спосіб 1. ФункціяСУММЕСЛИ, коли одна умова


Якби в нашій задачі було тільки одна умова (всі замовлення Петрова або всі замовлення в "Копійку", наприклад), то задача вирішувалася б досить легко за допомогою вбудованої функції Excel Excel СУММЕСЛИ (SUMIF) з категорії Математичні (Math & Trig). Виділяємо порожню комірку для результату, тиснемо кнопку fx в рядку формул, знаходимо функцію СУММЕСЛИ в списку:

 

 

Тиснемо ОК і вводимо її аргументи:

 

Діапазон - це ті комірки, які ми перевіряємо на виконання Критерію. У нашому випадку - це діапазон з прізвищами менеджерів продажів.

Критерій - це те, що ми шукаємо в попередньому зазначеному діапазоні. Дозволяється використовувати символи * (зірочка) та ? (знак питання) як маски або символи підстановки. Зірочка підміняє собою будь-яку кількість будь-яких символів, знак питання - один будь-який символ. Так, наприклад, щоб знайти всі продажі у менеджерів з прізвищем з п'яти букв, можна використовувати критерій ?????. А щоб знайти всі продажі менеджерів, у яких прізвище починається на букву "П", а закінчується на "В" - критерій П * В. Малі та великі літери не розрізняються.

Діапазон_сумування - це ті комірки, значення яких ми хочемо скласти, тобто нашому випадку - вартість замовлень.

 

Спосіб 2. Функція СУММЕСЛІМН, коли умов багато

Якщо умов більше одного (наприклад, потрібно знайти суму всіх замовлень Григор'єва для "Копійки"), то функція СУММЕСЛИ(SUMIF) не допоможе, тому не вміє перевіряти більше одного критерію. Тому починаючи з версії Excel 2007 в набір функцій була додана функція СУММЕСЛИМН(SUMIFS) - в ній кількість умов перевірки збільшено аж до 127! Функція знаходиться в тій же категорії Математические та працює схожим чином, але має більше аргументів:

 

За допомогою смуги прокрутки в правій частині вікна можна задати і третю пару (Диапазон_условия3-Условие3),, і четверту, і т.д. - При необхідності.

Якщо ж у вас поки ще стара версія Excel 2003, але завдання з декількома умовами вирішити потрібно, то доведеться перекручуватися - див. наступні способи.

 

Спосіб 3. Стовпець-індикатор
Додамо до нашої таблиці ще один стовпець, який буде служити своєрідним індикатором: якщо замовлення було в "Копійку" та від Григор'єва, то в комірці цього стовпця буде значення 1, інакше - 0. Формула, яку треба ввести в цей стовпець дуже проста:
=(A2="Копейка")*(B2="Григорьев")

Логічні рівності в дужках дають значення (ИСТИНА или ЛОЖЬ) TRUE або FALSE, що для Excel рівносильно 1 і 0. Таким чином, оскільки ми перемножуємо ці вирази, одиниця в кінцевому рахунку вийде тільки якщо обидві умови виконуються. Тепер вартості продажів залишилося помножити на значення отриманого стовпця і підсумувати відібране в зеленій комірці:



 

Спосіб 4. Чарівна формула масиву
Якщо ви раніше не стикалися з такою чудовою можливістю Excel як формули масиву, то раджу почитати попередньо про них багато хорошого тут. Ну, а в нашому випадку завдання вирішується однією формулою: =СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)

 

Після введення цієї формули необхідно натиснути не Enter, як зазвичай, а Ctrl + Shift + Enter - тоді Excel сприйме її як формулу массиву і сам додасть фігурні дужки. Вводити дужки з клавіатури не треба. Легко здогадатися, що цей спосіб (як і попередній) легко масштабується на три, чотири і т.д. умов без яких-небудь обмежень.

 





sdamzavas.net - 2020 год. Все права принадлежат их авторам! В случае нарушение авторского права, обращайтесь по форме обратной связи...