Расчет средних значений по дате (Microsoft Excel)

Предположим, у вас есть огромный рабочий лист, который содержит все значения осадков для данного региона за последние сто лет или около того. В ячейках A2: A37987 указаны даты с 1 января 1903 года по 31 декабря 2006 года.

В ячейках B2: B37987 у вас есть измерения для каждой даты. Кроме того, некоторые измерения могут быть нулевыми (если в этот день нет дождя) или пустыми (если в этот день не было снято никаких показаний). Имея всю эту информацию, вы хотите рассчитать среднее историческое количество осадков для любого дня в году.

Одно из решений включает использование формул массива, как подробно описано здесь:

В ячейке F2 введите следующую формулу массива (завершите формулу, нажав Shift + Ctrl + Enter). Результатом формулы является количество ячеек в диапазоне осадков для даты в ячейке D2, в которых есть значение.

В ячейке G2 введите следующую обычную формулу. Это ваше среднее значение для даты в ячейке D2.

Выберите диапазон E2: G2 и скопируйте все даты, указанные в столбце D.

Этот подход работает, но его расчет занимает довольно много времени. Это потому, что вы фактически ввели 730 формул массива, каждая из которых проверяет более 37 000 ячеек. Это большая работа, и, следовательно, может показаться, что ваша машина «зависла» после выполнения шага 7. Она не зависла; просто потребуется время, чтобы завершить вычисления.

Чтобы уменьшить количество вычислений, которые необходимо выполнить, вы можете использовать вариант вышеперечисленных шагов. Выполните шаги с 1 по 3, как указано, а затем поместите следующую формулу массива в ячейку E2:

Затем вы можете скопировать формулу вниз для всех дат, указанных в столбце D.

Результатом этой формулы является фактическое среднее количество осадков, такое же, как было показано в столбце G в предыдущем подходе.

Эта формула работает из-за того, как логическая арифметика работает в Excel. Функция ISNUMBER возвращает True или False, а сравнения (MONTH и DAY) возвращают True или False. Все эти результаты умножаются друг на друга, в результате получается Истина, только если все отдельные тесты истинны. Только в том случае, если все они истинны, будет вычислено среднее количество осадков для этой конкретной даты.

Вы можете еще больше сократить накладные расходы на вычисления, просто избавившись от всех таблиц, в которых вычисляются средние значения для каждого дня в году. Указав даты и количество осадков в столбцах A и B, выполните следующие действия:

Вот и все. Теперь вы можете изменить дату в ячейке D2 по своему желанию, а ячейка E2 всегда будет указывать среднее количество осадков для этой даты. Формула в ячейке E2 такая же, как и в предыдущем подходе; разница в том, что вы не рассчитываете его для всех дней в году, и поэтому расчет выполняется намного быстрее.

Другой подход предполагает использование возможностей фильтрации Excel.

Однако, прежде чем вы сможете использовать их должным образом, вы должны создать столбец, в котором будут отображаться только месяц и день для каждой даты в ваших данных. Используйте эту формулу в ячейке C2:

Теперь включите автофильтрацию (Данные | Фильтр | Автофильтр) и используйте раскрывающийся список в верхней части нового столбца, чтобы выбрать дату, для которой вы хотите получить среднее значение. Затем вы используете следующую формулу, помещенную в любую желаемую ячейку, чтобы показать среднее количество осадков за выбранную дату:

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (2350) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь:

link: / excelribbon-Calculating_Averages_by_Date [Расчет средних по дате].