Билл прислал на этой неделе вопрос Excel.
У меня есть база данных событий в Excel, и мой босс хочет, чтобы я построил графики частот по месяцам. Я читал ваш трюк по замене ежедневных дат на ежемесячные и о формулах Excel CSE. Я перепробовал все критерии, которые я мог придумать в приведенной ниже формуле Excel CountIf, чтобы получить результаты по двум критериям.
Вероятно, вашу ситуацию можно было бы легко решить с помощью сводной таблицы (XL95-XL2000) или сводной диаграммы (только XL2000). А пока давайте обратимся к заданному вами вопросу. Слева ваш рабочий лист. Похоже, вам нужно будет ввести формулы в ячейки B4406: D4415, чтобы рассчитать количество определенных событий каждый месяц.
Функция CountIf - это специализированная форма формулы массива, которая отлично подходит, когда у вас есть один критерий. Это не сработает, если у вас несколько критериев. Следующие примеры формул будут подсчитывать количество строк с Rain и количество событий в январе 97:
=COUNTIF(B2:B4403,"=Rain")
=COUNTIF(A2:A4403,"="&A4406)
Невозможно использовать CountIf для пересечения двух условий.
Для всех читателей, которые не знакомы с тем, как вводить формулы массива, я настоятельно рекомендую просмотреть Использование формул CSE для повышения эффективности 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 оператора if, поскольку функция AND () не работает в формуле массива. Для лучшего объяснения того, что происходит с функциональностью массива, еще раз прочтите «Использовать формулы CSE для перегрузки Excel», упомянутого выше.
