Как игнорировать пустые ячейки для формулы

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

Сейчас у меня есть =(D3-C3)+(F3-E3)+(H3-G3)+(J3-I3)+(L3-K3)+(N3-M3)+(P3-O3)

Но он включает все пустые ячейки (для еще не отработанных дней) как ноль.

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

Я не могу просто использовать функцию СУММЕСЛИ>0, потому что мне нужно подсчитать количество часов, в течение которых сотрудники ПРОПУСТИЛИ (т.е. запланировано 12 часов, фактически отработано 0).


excel formula sum cells
person Beckie Prince    schedule 29.07.2015    source источник
comment
Определите дни, которые еще не были отработаны. У вас, например, есть строка заголовка, которая говорит что-то вроде 1 января 2015 года, 2 января 2015 года и т. д.? Если это так, вы можете создать формулу для добавления только тех дат, которые меньше, чем СЕГОДНЯ(). Дайте мне знать, если вам нужны идеи о том, как реализовать.   -  person Grade 'Eh' Bacon    schedule 29.07.2015
comment
Я работаю над формулой, подобной этой, для запланированных часов, но просто уйду, поэтому мне придется вернуться к вам после того, как я ее проверю =СУММ(D3:P3*ISEVEN(COLUMN(D3:P3))*( E3:Q3‹›))   -  person Tom Sharpe    schedule 29.07.2015
comment
Кстати, я предполагаю, что D3, F3 и т. д. содержат запланированные часы, а C3, E3 и т. д. содержат отработанные часы, и проблема в том, что C3, E3 и т. д. могут быть пустыми (если бы и D3, и E3 были пустыми, тогда не было бы проблема, потому что вы просто получите ноль). Пожалуйста, дайте нам знать, если это не правильное предположение.   -  person Tom Sharpe    schedule 29.07.2015
comment
Да, это правильное предположение. D3 — это фактически отработанные часы, а C3 — запланированные часы.   -  person Beckie Prince    schedule 29.07.2015

Ответы (2)


arrow_upward
0
arrow_downward

В его нынешнем виде либо очень длинная серия ЕСЛИ и ИЛИ, либо формула массива: -

=SUM(IF(OR(D3="",C3=""),0,D3-C3),IF(OR(F3="",E3=""),0,F3-E3),IF(OR(H3="",G3=""),0,H3-G3),IF(OR(J3="",I3=""),0,J3-I3),IF(OR(L3="",K3=""),0,L3-K3),IF(OR(N3="",M3=""),0,N3-M3),IF(OR(P3="",O3=""),0,P3-O3))

=SUM(D3:P3*ISEVEN(COLUMN(D3:P3))*(C3:O3<>""))-SUM(C3:O3*ISODD(COLUMN((C3:O3))*(D3:P3<>"")))

Второй нужно ввести с помощью CtrlShiftEnter

Обратите внимание, что его можно легко сломать, вставив/удалив столбцы.

person Tom Sharpe    schedule 29.07.2015

arrow_upward
1
arrow_downward

Вот альтернативный подход к @Tom's, хотя он работает на тех же принципах. Это, однако, зависит от вашей способности добавить пару строк «ПОМОЩЬ» над вашими текущими данными.

Я предполагаю, что в строке 1 будут чередоваться слова «ПРОГНОЗ» и «АКТУАЛЬНЫЙ». Я предполагаю, что строка 2 будет датами этой недели в формате даты. Таким образом, A2 будет 1 января 2015 года, B2 будет 1 февраля 2015 года, C3 будет 1 февраля 2015 года, или, как бы часто ни было, временные блоки увеличиваются. Ключевым моментом здесь является то, что в столбцах PROJECTED и ACTUAL потребуется дата.

Формула для проверки разницы между ПРОГНОЗИРУЕМОЙ суммой этой строки и ФАКТИЧЕСКОЙ суммой этой строки только для дат до сегодняшнего дня (для строки 3 и скопированной вниз, и при условии, что данные попадают в столбец Z):

=SUMIFS(A3:Z3,$A$1:$Z$1,"PROJECTED",$A$2:$Z$2,"<"&TODAY())-SUMIFS(A3:Z3,$A$1:$Z$1,"ACTUAL",$A$2:$Z$2,"<"&TODAY())

При этом проверяется значение столбцов PROJECTED для этой строки, где дата меньше сегодняшней, и вычитается значение столбцов ACTUAL для той строки, где дата меньше сегодняшней.

Если вы хотите сравнить не с СЕГОДНЯ(), а с чем-то другим, вы можете настроить ячейку в качестве «точки сравнения». Вручную введите интересующий вас период перерыва в эту ячейку и замените «& СЕГОДНЯ ()», скажем, на «& AA1» [при условии, что ваша точка разрыва введена в ячейку AA1].

person Grade 'Eh' Bacon    schedule 29.07.2015