У Джона есть огромный рабочий лист, который включает ежедневные данные за шестьдесят лет. Он хочет придумать формулу, которая будет вычислять медианные значения для каждого месяца в период времени, охватываемый данными.

Прежде чем предлагать решение, лучше всего изложить некоторые предположения. В целях этого совета предположим, что ежедневные данные находятся в столбцах A и B. В столбце A указаны даты, связанные с данными, а в столбце B — фактические значения данных для каждой из этих дат. Кроме того, ячейки A1 и B1 содержат заголовки для каждого столбца. Это означает, что фактические данные находятся примерно в диапазоне A2: B22000.

Чтобы упростить использование формул, вы должны определить имена для данных в обоих столбцах A и B. Выберите диапазон дат (например, A2: A22000) и присвойте ему имя, например AllDates. Выберите диапазон соответствующих данных (например, B2: B22000) и используйте тот же процесс для создания имени, например AllData.

Вы можете использовать формулы массива для вычисления фактических медианных значений. Это включает настройку другой таблицы данных, содержащей медианы. Поместите заголовки, такие как «Месяц» в ячейку E1 и «Медиана» в ячейку F1. В ячейку E2 поместите первый день первого месяца вашего набора данных, например 1/1/1940. В ячейке E3 укажите дату на месяц позже, например, 01.02.1940. Выделите эти две ячейки (E2: E3) и перетащите маркер заполнения вниз на нужное количество месяцев в таблице данных.

Если в ваших данных за шестьдесят лет нет пробелов, введите следующую формулу в ячейку F2:

=MEDIAN(IF(DATE(YEAR(AllDates),MONTH(AllDates),1)=E2,AllData))

Завершите формулу, нажав Ctrl + Shift + Enter, что сообщает Excel, что это формула массива. Затем вы можете скопировать формулу в F2 в каждую из ячеек в столбце F, для которой есть соответствующий месяц в столбце E. Формула анализирует даты в столбце B, и если год и месяц равны любой дате, которую вы поместили в ячейку E2 , то медиана рассчитывается по всем соответствующим точкам данных.

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

=MEDIAN(IF((DATE(YEAR(AllDates),MONTH(AllDates),1)=E2)*ISNUMBER(AllData),AllData))

Есть одно предостережение при использовании формул массива таким образом. Если у вас есть данные за шестьдесят лет с примерно 22 000 индивидуальных значений, то это все равно много месяцев: около 720 из них. Это означает, что вы создаете 720 формул массива, каждая из которых анализирует 22 000 значений данных для получения ответа. Это требует большого количества вычислений, поэтому вы заметите замедление реакции Excel при каждом пересчете рабочего листа.

Если медлительность становится проблемой, вы можете подумать о реорганизации исходных данных, чтобы каждая строка рабочего листа представляла отдельный месяц. Столбец A может содержать месяц для строки (01.01.1940, 01.02.1940, 01.03.1940 и т. Д.), А столбцы B: AF будут содержать дни с 1 по 31 для каждого месяца. Затем пересекающиеся ячейки в таблице могут содержать точки данных для каждого дня месяца, и вы можете использовать функцию MEDIAN в столбце AG для вычисления медианы для каждого месяца. Это по-прежнему приводит к 720 формулам, но это обычные формулы, каждая из которых должна обрабатывать данные только за месяц, а не формулы массива, которые нужны для каждой обработки данных за шестьдесят лет. Результат — гораздо более быстрые вычисления.

Конечно, для большинства людей идеи реорганизации такого огромного количества данных достаточно, чтобы не заснуть по ночам. Вместо этого вы можете использовать совершенно другой подход к анализу данных. Такой подход возможен, потому что медиана — это очень простая статистическая функция для вычисления вручную. Вы просто сортируете свой набор данных и, если количество элементов в наборе данных нечетное, выбираете средний элемент. Если количество элементов четное, вы берете среднее значение двух средних элементов.

Чтобы подготовиться к анализу данных, нужно сделать несколько вещей.

Во-первых, будет удобно иметь способ однозначно идентифицировать месяц каждой точки данных. В ячейку C2 поместите следующую формулу:

=100*Year(A2)+Month(A2)

В результате в столбце C сохраняется такое значение, как 194001, 194002, 194003 и т. Д.. Это уникальное значение месяца. Теперь вы должны отсортировать данные по столбцу C, а затем по столбцу B. Обе сортировки должны быть в порядке возрастания, чтобы в итоге вы получили данные, сначала отсортированные по году / месяцу, а затем по значению в течение года / месяца.

Затем вам нужно добавить промежуточные итоги к вашим данным. Выберите «Промежуточные итоги» в меню «Данные», в котором отображается диалоговое окно «Промежуточные итоги». Вы хотите добавить промежуточный итог при каждом изменении в столбце C. Используемая функция — Count, и вы хотите добавить промежуточный итог в столбец B. Когда вы нажимаете OK, вы получаете 720 промежуточных итогов, по одному для каждого месяца в данных. диапазон, каждый из которых показывает количество элементов данных в этом месяце.

Чтобы получить медианы, добавьте в ячейку D2 формулу:

IF(RIGHT(B2,5)="Count", IF(MOD(C2,2)=1, INDIRECT("B"&(ROW()-1)-C2/2+1/2), (INDIRECT("B"&(ROW()-1)-C2/2)+INDIRECT("B"&(ROW()-1)-C2/2+1))/2), "")

Формула проверяет, что находится в ячейке B2, и если она содержит слово «Count», то она знает, что это строка промежуточных итогов. В этом случае он проверяет, является ли счетчик в ячейке C2 нечетным или четным. Если он нечетный, то функция ДВССЫЛ используется для получения любого среднего значения за месяц. Если он четный, то два средних значения месяца складываются и делятся пополам.

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

так что вы показываете только эти промежуточные строки.

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

Этот совет (12727) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Deriving_Monthly_Median_Values ​​[Получение среднемесячных значений].