Нужна помощь Excel Formula

Question

Мне нужна помощь, чтобы составить формулу в Excel. В основном картина ниже объясняет, что мне нужно, это всего лишь образец. В колонке Е много разных записей. Время от времени мне нужно SUM в столбце G из некоторых ячеек столбца E, в зависимости от положения № "1" в столбце B. № "1" всегда является отметкой, когда мне нужно SUM, и расстояние нет "1" может варьироваться.

Или это может быть как на следующей картинке. В столбце A указано "дата и время", в 12:00 нужно СУММУ из ячеек в столбце E до 12:00 предыдущего дня в столбце A. Надеюсь, понятно, что мне нужно.

Answer 1

Самое простое решение для первого сценария, то есть с использованием вспомогательного столбца B:

Введите следующую формулу в G4 и ctrl-enter/copy-paste/fill-down в столбце:

=IF(B4="","",SUM(INDEX(E$3:E4,1+IFERROR(MATCH(1,B$3:B3,1),1)):E4))

Обратите внимание, что часть OR(ROW()=ROW(B$3)+1,…) требуется, чтобы избежать ошибки в первой ячейке столбца (ячейка G4).

Формула для второго сценария немного сложнее:

Введите следующую формулу в G4 и ctrl-enter/copy-paste/fill-down в столбце:

=IF(A4-INT(A4)<>0.5,"",SUM(INDEX(E:E,1+LARGE(INDEX((A$3:A3-INT(A$3:A3)=0.5)*(ROW(A$3:A3)),),1)):E4))

Answer 2

Способ 1. Использование условного форматирования (без вспомогательного столбца)

1. Вход в G5 и копирование в G6:G18

   =IF(B4=1, E4, G4+E4)              <-- for the easy scenario
   
   =IF(MOD(A4, 1)=0.5, E4, G4+E4)    <-- for the complicated scenario
   

2. Условное форматирование

   • Выделите G5:G18
   • Главная> Условное форматирование> Новое правило
   • Используйте формулу, чтобы определить, какие форматы ячеек
   • Формула: =B5<>1
   • Формат: font color = white (скрыть ненужные значения)

---

Способ 2: использование вспомогательного столбца

1. Вход в H5 и копирование в H6:H18

   =IF(B4=1, E4, H4+E4)             <-- for the easy scenario
   
   =IF(MOD(A4, 1)=0.5, E4, H4+E4)   <-- for the complicated scenario
   

2. Вход в G5 и копирование в G6:G18

   =IF(B5=1, H5, "")
   

---

Поскольку дата / время хранятся в Excel как десятичное значение,

• 20/06/2018 00:00:00 = 43271
• 20/06/2018 12:00:00 = 43271.5

Мы можем использовать MOD для проверки времени 12:00.