Извлечение максимумов и минимумов ежемесячно из набора данных Excel

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

введите описание изображения здесь

Формула, с которой вы столкнулись, выглядит так, будто вы на правильном пути, но я бы передумал:

= MAX(IF((MONTH($B$2:$B$100)=J2)*(YEAR($B$2:$B$100)=I2),$C$2:$C$100)) 

А затем перетащите эту формулу, насколько это необходимо.

(Аналогично, чтобы найти минимум за каждый месяц, просто замените MAX в приведенном выше уравнении MIN ).

Обратите внимание, что это формула массива, поэтому вы должны нажать Ctrl + Shift + Enter после ввода этой формулы, а не просто нажать Enter .

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

Или, если вы используете Pivot Tables, решение @ QHarr также будет работать.


РЕДАКТИРОВАТЬ

В соответствии с запросом, вот что происходит в этой формуле:

Оба (MONTH($B$2:$B$100)=J2) и (YEAR($B$2:$B$100)=I2) возвращают вертикальные массивы (одного размера) TRUE и FALSE . Когда вы умножаете их вместе, вы получаете массив из 1 и 0. (Индексы этого выходного массива будут равны 1, где соответствующие индексы входного массива равны TRUE .) Вы фактически не умножаете MONTH и YEAR вместе. Это было бы бессмысленно, потому что, например, в феврале 2017 года вы получили бы значение 4034, что бессмысленно.

Простой пример объединения двух логических массивов: {TRUE;TRUE;FALSE}*{TRUE;FALSE;TRUE} возвращает {1;0;0} . Вы можете ожидать, что он должен вернуться {TRUE;FALSE;FALSE} но при умножении двух логических массивов он автоматически преобразуется в массив из 1 и 0. Однако это не проблема, потому что оператор IF обрабатывает массив из 1 и 0 так же, как обрабатывает массив TRUE и FALSE , поэтому нет необходимости преобразовывать его обратно в логические значения.

Причина AND не работает, потому что AND всегда возвращает единственный результат (а не массив). Например, AND({TRUE;TRUE;FALSE},{TRUE;FALSE;TRUE}) просто вернет FALSE . Из-за этого AND как правило, не очень полезен в формулах массивов, поскольку он, ну, не возвращает массивы ха-ха.


Что касается возврата +1, -1 или 0 в зависимости от того, как он сравнивается с предыдущим месяцем, это довольно легко выполнить.

Поскольку вывод находится в столбце K , я опишу его.

В ячейке L2 введите формулу (и при необходимости перетащите ее):

 = IF(K2>K1,"+1",IF(K2<K1,"-1","0")) 

Эта формула отлично работает, если вы не вставляете строки, тогда она может испортить формулу, поэтому я обычно предпочитаю делать что-то вроде этого ниже. (Он добавляет немного больше к формуле, но «более безопасен»):

 = IF(K2>INDEX(K:K,ROW()-1),"+1",IF(K2<INDEX(K:K,ROW()-1),"-1","0")) 

Вы заметите, что эти формулы идентичны, за исключением того, что в приведенной выше формуле K1 был заменен INDEX(K:K,ROW()-1) . Проблема с просто набором K1 заключается в том, что если строка вставлена ​​между первой и второй строкой, ссылка на K1 не изменится, хотя вы хотите, чтобы она фактически увеличивалась до K2 . Это разрешено с помощью INDEX(K:K,ROW()-1) который всегда просто ссылается на ячейку в столбце K непосредственно над ней, даже если строки вставлены в лист.

почему бы просто не использовать pivottable и изменить метод агрегата сводного поля на максимальный или минимальный для полей High и Low.

Вот пример изображения (простить использование Mac!) Добавления полей Изображение добавленных полей

И результат

Примеры полей результата

Вам не нужно удваивать Макс и Мин для каждого поля. Я просто хотел показать, что это возможно.

  • Формула Excel возвращает числовые значения, когда я хочу, чтобы он возвращался "-"
  • Необходима формула автозаполнения
  • Excel: вернуть все значения, имеющие ошибку в одной строке
  • Открыть книгу Excel в полноэкранном режиме
  • Графики со сложными критериями
  • Вычитание вычетов в формате Excel
  • в excel как изменить значение ячейки, если она превышает указанную сумму
  • Существуют ли такие переменные в формуле Excel?
  • HOw, чтобы получить динамический диапазон массивов в excel
  • Преодоление ошибки компиляции «else without if» в моем коде
  • Мне нужна формула, которая заявляет, что если столбец D не равен «Неограничен», то вычитает столбец I из столбца D. Получил «= IF (D8 <>« Unlimited », I8-D8,0)».
  • Давайте будем гением компьютера.