Підсумовування з критерієм «АБО» в Excel може виявитися трохи складніше, ніж ви очікуєте. У цій статті ви знайдете кілька простих, але корисних прикладів.
Почнемо з простого. Припустимо, необхідно підсумувати осередки, які відповідають наступним критеріям: містять слова "Google" або "Facebook" (один стовпець з критеріями). З цим легко впорається наступна формула:
Якщо ж у нас буде 2 стовпці з критеріями, і ми захочемо також підсумувати осередки в рядках, які містять "Google" або "Stanford" (два діапазони критеріїв), то не можемо просто використовувати функцію SUMIF (СУММЕСЛИ) двічі (див. Малюнок нижче ), як в попередньому прикладі. Осередки зі значеннями в рядках 1 і 7 підсумовані два рази, хоча повинні обчислюватися тільки один раз. 10 - ось правильний результат!
Нам потрібна формула масиву. Використовуємо функцію IF (ЯКЩО), щоб перевірити, трапляються чи критерії "Google" або "Stanford" в рядку:
Для першого рядка, функція IF (ЯКЩО) повертає таке значення:
= IF (TRUE + TRUE, 1,0) * 3 ► = IF (2,1,0) * 3 ► 3
= ЕСЛИ (ІСТИНА + ІСТИНА; 1; 0) * 3 ► = ЕСЛИ (2; 1; 0) * 3 ► 3
Таким чином, значення 3 буде враховуватися.
Для другого рядка:
= IF (FALSE + FALSE, 1,0) * 5 ► = IF (0,1,0) * 5 ► 0
= ЕСЛИ (БРЕХНЯ + БРЕХНЯ; 1; 0) * 5 ► = ЕСЛИ (0; 1; 0) * 5 ► 0
Таким чином, значення 5 враховуватися не буде.
= IF (FALSE + TRUE, 1,0) * 2 ► = IF (1,1,0) * 2 ► 2
= ЕСЛИ (БРЕХНЯ + ІСТИНА; 1; 0) * 2 ► = ЕСЛИ (1; 1; 0) * 2 ► 2
Тобто, значення 2 враховуватися буде, і т.д.
Тепер все, чого нам не вистачає, це функції SUM (СУМ), яка підсумовує ці значення. Давайте додамо функцію SUM (СУМ) і замінимо А1 на A1: A8. B1 на B1: B8 і C1 на С1: C8.
Закінчимо, натиснувши Ctrl + Shift + Enter.
Примітка: Рядок формул показує, що це формула масиву, укладаючи її в фігурні дужки <>. Їх не потрібно вводити самостійно. Вони зникнуть, коли ви почнете редагувати формулу.
- Діапазон (масив констант), створений за допомогою функції IF (ЯКЩО), зберігається в пам'яті Excel, а не в клітинках аркуша.
- Масив констант виглядає наступним чином:.
- Він множиться на C1: C8 і це дає.
- Останній масив констант використовується як аргумент для функції SUM (СУМ), даючи результат 10.
Ми можемо зробити ще один крок вперед. Наприклад, підрахувати кількість рядків, які містять "Google" і "Stanford", або "Columbia".
