Загальна формула
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Резюме
Для підрахунку видимих рядків лише за критеріями ви можете використовувати досить складну формулу, засновану на SUMPRODUCT, SUBTOTAL та OFFSET. У наведеному прикладі формула в C12 має вигляд:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Передмова
Функція SUBTOTAL може легко генерувати суми та підрахунки для прихованих та не прихованих рядків. Однак він не може обробляти такі критерії, як COUNTIF або SUMIF, без певної допомоги. Одним із рішень є використання SUMPRODUCT для застосування як функції SUBTOTAL (через OFFSET), так і критеріїв. Деталі цього підходу описані нижче.
Пояснення
По суті, ця формула працює, встановлюючи два масиви всередині SUMPRODUCT. Перший масив застосовує критерії, а другий масив обробляє видимість:
=SUMPRODUCT(criteria*visibility)
Критерії застосовуються з частиною формули:
=(C5:C8=C10)
Що генерує такий масив:
(FALSE;TRUE;FALSE;TRUE)
Де TRUE означає "відповідає критеріям". Зверніть увагу, оскільки ми використовуємо множення (*) для цього масиву, значення TRUE FALSE автоматично перетворюються на одиниці 1 і 0 за допомогою математичної операції, тому в результаті ми отримуємо:
(0;1;0;1)
Фільтр видимості застосовується за допомогою SUBTOTAL з функцією номер 103.
SUBTOTAL може виключати приховані рядки під час запуску обчислень, тому ми можемо використовувати його в цьому випадку для створення "фільтра" для виключення прихованих рядків усередині SUMPRODUCT. Проблема в тому, що SUBTOTAL повертає одне число, тоді як нам потрібен масив результатів, щоб успішно використовувати його всередині SUMPRODUCT. Фокус полягає у використанні OFFSET для подачі SUBTOTAL одного посилання на рядок, так що OFFSET поверне по одному результату на рядок.
Звичайно, для цього потрібен ще один фокус, який полягає у наданні OFFSET масиву, який містить одне число на рядок, починаючи з нуля. Ми робимо це за допомогою виразу, побудованого на функції ROW:
=ROW(C5:C8)-MIN(ROW(C5:C8)
який генерує такий масив:
(0;1;2;3)
Таким чином, другий масив (який обробляє видимість за допомогою SUBTOTAL) генерується таким чином:
=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0)) =SUBTOTAL(103,OFFSET(C5,(0;1;2;3),0)) =SUBTOTAL(103,("East";"West";"Midwest";"West")) =(1;0;1;1)
І, нарешті, ми маємо:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Що повертає 1.
Кілька критеріїв
Ви можете розширити формулу, щоб обробляти кілька таких критеріїв:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Підбиття підсумків
Щоб повернути суму значень замість підрахунку, ви можете адаптувати формулу, щоб включити діапазон сум:
=SUMPRODUCT(criteria*visibility*sumrange)
Критерії та масиви видимості працюють так само, як описано вище, за винятком комірок, які не видно. Якщо вам потрібна часткова відповідність, ви можете створити вираз, використовуючи ISNUMBER + SEARCH, як пояснено тут.
