Білл надіслав запитання в Excel на цьому тижні.
У мене є база даних подій в Excel, і мій бос хоче, щоб я складав графіки частоти по місяцях. Я прочитав трюк щодо зміни щоденних дат на щомісячні, а також про формули Excel CSE. Я перепробував усі критерії, які я можу придумати, у формулі Excel CountIf нижче, щоб змусити його розглянути 2 критерії.
Можливо, вашу ситуацію легко вирішити за допомогою зведеної таблиці (XL95-XL2000) або зведеної діаграми (лише XL2000). Поки що, давайте розглянемо питання, яке ви задали. Зліва ваш робочий аркуш. Схоже, вам потрібно буде ввести формули в клітинки B4406: D4415 для обчислення кількості певних подій щомісяця.
Функція CountIf - це спеціалізована форма формули масиву, яка чудова, коли у вас є один критерій. Це не працює добре, якщо у вас є кілька критеріїв. Наступні зразки формул підраховуватимуть кількість рядків з дощем та кількість подій у січні 97:
=COUNTIF(B2:B4403,"=Rain")
=COUNTIF(A2:A4403,"="&A4406)
Немає можливості використовувати CountIf, щоб отримати перетин двох умов.
Будь-якому читачеві, який не знайомий із введенням формул масивів, настійно рекомендую переглянути "Використання формул ПСЕ" для надбавки Excel.
Білл не зазначив цього у своєму питанні, але я хочу створити формулу, яку він може ввести лише один раз у клітинку B4406, яку можна легко скопіювати в інші комірки свого діапазону. Використовуючи абсолютні та змішані посилання у формулі, ви можете заощадити клопоту з введенням нової формули для кожного перетину.
Ось короткий огляд абсолютних, відносних та змішаних формул. Зазвичай, якщо ви вводите формулу, як =SUM(A2:A4403)у D1, а потім копіюєте формулу в E2, ваша формула в E2 зміниться на =SUM(B3:C4403). Це класна особливість робочих аркушів, що називається "відносна адресація", але іноді ми не хочемо, щоб це сталося. У цьому випадку ми хочемо, щоб кожна формула посилалася на діапазон A2: B4403. Коли ми копіюємо формулу з комірки в клітинку, вона завжди повинна вказувати на A2: B4403. Під час введення формули натисніть клавішу F4 один раз після введення діапазону, і ваша формула зміниться на=SUM($A$2:$A$4403). Знак долара означає, що ця частина посилання не зміниться під час копіювання формули. Це називається абсолютною адресацією. Можна зафіксувати лише стовпець з $ і дозволити рядку бути відносним. Це називається змішаним посиланням і вводиться як =$A4406. Щоб заблокувати рядок, але дозволити стовпцю бути відносним, використовуйте =B$4405. Під час введення формули використовуйте клавішу F4 для перемикання між чотирма варіантами відносного, абсолютного та змішаного посилань.
Ось формула для комірки B4406:
=SUM(IF($C$2:$C$4403=$A4406,IF($B$2:$B$4403=B$4405,1,0),0))
Введіть формулу. Закінчивши формулу, натисніть і утримуйте Ctrl, Shift, а потім введіть. Тепер ви можете скопіювати формулу в C4406: D4406, а потім скопіювати ці три клітинки в кожен рядок у вашій таблиці результатів.
Формула використовує всі три форми змішаних та абсолютних посилань. Він вкладає 2, якщо оператори, оскільки функція AND (), здається, не працюють у формулі масиву. Для кращого пояснення того, що відбувається з функціоналом масиву, перечитайте Використовуйте формули ПС для перезарядки Excel, згаданих вище.
