#date #excel-formula #min
Вопрос:
У меня есть одна строка в час на каждый день. В течение часов с 06:00 до 21:00 мне нужно использовать самые низкие значения ( минимальное значение), найденные в колонке Цена часов с 22:00 (за день до этого) до 05:00, и хранить их в самой низкой колонке в каждой строке в течение часов с 06:00 до 21:00. В противном случае в столбце lowes будет отображаться то же значение, что и в цене.
Как должна выглядеть формула Excel в самом низком столбце, чтобы решить эту проблему?
Как это должно выглядеть (минимальное значение, выделенное жирным шрифтом)
| Дата и время | Часть даты | Час | Цена | Низший |
|---|---|---|---|---|
| 2018-01-01 00:00 | 2018-01-01 | 00:00 | 258,86 | 258,86 |
| 2018-01-01 01:00 | 2018-01-01 | 01:00 | 259,85 | 259,85 |
| 2018-01-01 02:00 | 2018-01-01 | 02:00 | 256,6 | 256,6 |
| 2018-01-01 03:00 | 2018-01-01 | 03:00 | 242,84 | 242,84 |
| 2018-01-01 04:00 | 2018-01-01 | 04:00 | 243,23 | 243,23 |
| 2018-01-01 05:00 | 2018-01-01 | 05:00 | 177,07 | 177,07 |
| 2018-01-01 06:00 | 2018-01-01 | 06:00 | 174,8 | 177,07 |
| 2018-01-01 07:00 | 2018-01-01 | 07:00 | 175 | 177,07 |
| 2018-01-01 08:00 | 2018-01-01 | 08:00 | 194,27 | 177,07 |
| 2018-01-01 09:00 | 2018-01-01 | 09:00 | 203,81 | 177,07 |
| 2018-01-01 10:00 | 2018-01-01 | 10:00 | 243,43 | 177,07 |
| 2018-01-01 11:00 | 2018-01-01 | 11:00 | 252,47 | 177,07 |
| 2018-01-01 12:00 | 2018-01-01 | 12:00 | 236,84 | 177,07 |
| 2018-01-01 13:00 | 2018-01-01 | 13:00 | 245,89 | 177,07 |
| 2018-01-01 14:00 | 2018-01-01 | 14:00 | 253,75 | 177,07 |
| 2018-01-01 15:00 | 2018-01-01 | 15:00 | 260,14 | 177,07 |
| 2018-01-01 16:00 | 2018-01-01 | 16:00 | 265,75 | 177,07 |
| 2018-01-01 17:00 | 2018-01-01 | 17:00 | 269,68 | 177,07 |
| 2018-01-01 18:00 | 2018-01-01 | 18:00 | 268,3 | 177,07 |
| 2018-01-01 19:00 | 2018-01-01 | 19:00 | 265,06 | 177,07 |
| 2018-01-01 20:00 | 2018-01-01 | 20:00 | 262,5 | 177,07 |
| 2018-01-01 21:00 | 2018-01-01 | 21:00 | 260,24 | 177,07 |
| 2018-01-01 22:00 | 2018-01-01 | 22:00 | 256,5 | 256,5 |
| 2018-01-01 23:00 | 2018-01-01 | 23:00 | 244,61 | 244,61 |
| 2018-01-02 00:00 | 2018-01-02 | 00:00 | 248,54 | 248,54 |
| 2018-01-02 01:00 | 2018-01-02 | 01:00 | 227,7 | 227,7 |
| 2018-01-02 02:00 | 2018-01-02 | 02:00 | 243,62 | 243,62 |
| 2018-01-02 03:00 | 2018-01-02 | 03:00 | 246,08 | 246,08 |
| 2018-01-02 04:00 | 2018-01-02 | 04:00 | 252,96 | 252,96 |
| 2018-01-02 05:00 | 2018-01-02 | 05:00 | 263,88 | 263,88 |
| 2018-01-02 06:00 | 2018-01-02 | 06:00 | 273,32 | 227,7 |
| 2018-01-02 07:00 | 2018-01-02 | 07:00 | 299,86 | 227,7 |
| 2018-01-02 08:00 | 2018-01-02 | 08:00 | 313,92 | 227,7 |
| 2018-01-02 09:00 | 2018-01-02 | 09:00 | 329,65 | 227,7 |
| 2018-01-02 10:00 | 2018-01-02 | 10:00 | 344,5 | 227,7 |
| 2018-01-02 11:00 | 2018-01-02 | 11:00 | 346,27 | 227,7 |
| 2018-01-02 12:00 | 2018-01-02 | 12:00 | 339,78 | 227,7 |
| 2018-01-02 13:00 | 2018-01-02 | 13:00 | 335,25 | 227,7 |
| 2018-01-02 14:00 | 2018-01-02 | 14:00 | 353,74 | 227,7 |
| 2018-01-02 15:00 | 2018-01-02 | 15:00 | 374,09 | 227,7 |
| 2018-01-02 16:00 | 2018-01-02 | 16:00 | 409,68 | 227,7 |
| 2018-01-02 17:00 | 2018-01-02 | 17:00 | 416,76 | 227,7 |
| 2018-01-02 18:00 | 2018-01-02 | 18:00 | 371,53 | 227,7 |
| 2018-01-02 19:00 | 2018-01-02 | 19:00 | 331,32 | 227,7 |
| 2018-01-02 20:00 | 2018-01-02 | 20:00 | 303,6 | 227,7 |
| 2018-01-02 21:00 | 2018-01-02 | 21:00 | 283,64 | 227,7 |
| 2018-01-02 22:00 | 2018-01-02 | 22:00 | 275,18 | 275,18 |
| 2018-01-02 23:00 | 2018-01-02 | 23:00 | 271,35 | 271,35 |
Комментарии:
1. У вас есть office 365? Вы могли бы использовать
MINIFSфункцию, указав критерии нижнего предела в качестве одного ограничения, а верхнего-в качестве другого, и иметь входные данные, на которых вы можете регулировать пределы.
Ответ №1:
Первый метод — изменчивый и ленивый
Вы можете использовать эту формулу в строке 2 вашего нижнего столбца (где Самый низкий-в столбце E) и скопировать ее вниз:
=IF(C2=TIME(6,0,0), MIN(OFFSET(D2,-MIN(8,ROW()-1),0,MIN(8,ROW()-1),1)),
IF( (C2 > TIME(6,0,0))*(C2 < TIME(22,0,0)), E1,
D2) )
Я положил свой сбоку от вашего и назвал его Низким, чтобы проверить, был ли получен правильный ответ, где у вас есть таблица, начинающаяся с A1, как это:
В таблице Excel (ctrl t) это более читабельно, как это:
=IF([@Hour]=TIME(6,0,0), MIN(OFFSET([@Price],-MIN(8,ROW()-1),0,MIN(8,ROW()-1),1)),
IF( ([@Hour] > TIME(6,0,0))*([@Hour] < TIME(22,0,0)), E1,
[@Price]) )
Способ кранера — энергонезависимый и не ленивый
Этот используемый ИНДЕКС вместо СМЕЩЕНИЯ, предложенного Скоттом Крейнером, должен сделать рабочий лист более отзывчивым.
=IF(C2=TIME(6,0,0), MIN(INDEX(D:D,MAX(1,ROW()-8)):INDEX(D:D,ROW()-1)),
IF( (C2 > TIME(6,0,0))*(C2 < TIME(22,0,0)), F1,
D2) )
или в таблице Excel:
=IF([@Hour]=TIME(6,0,0), MIN(INDEX(D:D,MAX(1,ROW()-8)):INDEX(D:D,ROW()-1)),
IF( ([@Hour] > TIME(6,0,0))*([@Hour] < TIME(22,0,0)), H1,
[@Price]) )
Комментарии:
1. ПРИМЕЧАНИЕ: сначала я опубликовал неправильный ответ, а затем удалил и исправил — извините, если вы выбрали промежуточный результат, но этот проверен.
2. Правда — я думал об индексе, но поленился.
3. Супер! Это действительно мило. ОК -будет редактироваться с помощью alt soln.
4. Во-первых, спасибо вам ! Метод Кранера работает как заклинание на обычном рабочем листе. Во-вторых, когда я использую его в таблице Excel, он будет выводить только FALSE и , следовательно, не будет работать. Есть какие-нибудь подсказки о том, почему возникает другая логика?
5. Круто! — Во-первых, @scott_craner, ты заслуживаешь похвалы здесь. Подумать только, что вы были так близки к 128 015 очкам — надеюсь, вы не копили на что-то. 😉 Единственный способ, которым я могу видеть, что он возвращает значение FALSE в Lowest , заключается в том, что он взял его либо из [@Price] , либо из H1 . H просто оказался столбцом, в который я поместил формулу в созданную мной таблицу Excel. Если вы скопировали формулу в свой нижний столбец и ваша таблица начинается с A1, то H1 следует изменить на E1. Но было бы действительно странно возвращать ЛОЖЬ. Вы можете сначала это проверить?
Ответ №2:
Если данные не отсортированы, используйте одну из этих формул. Если данные отсортированы, как показано в примере, то индексная версия формулы Марка будет быстрее для больших наборов данных.
Гнездовые МИНИФЫ в СЛУЧАЕ, если:
=IF(AND(C2>=TIME(6,0,0),C2<=TIME(21,0,0)),MINIFS(D:D,A:A,">="amp;B2-1 TIME(22,0,0),A:A,"<="amp;B2 TIME(5,0,0)),D2)
Если у кого-то нет минифонов, мы можем использовать AGGREGATE:
=IF(AND(C2>=TIME(6,0,0),C2<=TIME(21,0,0)),AGGREGATE(15,7,$D$2:$D$49/(($A$2:$A$49>=B2-1 TIME(22,0,0))*($A$2:$A$49<=B2 TIME(5,0,0))),1),D2)
обратите внимание, что нам нужно перейти от полных ссылок на столбцы только к набору данных.
