Excel для расчета суммы с помощью списка исключений

Question

Скажем, у меня есть таблица, как показано ниже:

Task Time
a    1
b    2
c    4
d    8
--------
sum  15

Когда задача закончена, я бы хотел добавить линию удара в строку и исключить ее из суммирования. Добавить линию удара легко, просто выделите строку и нажмите Ctrl+5. Есть ли простой способ исключить время выполнения заданий в суммировании? Так что легко сказать оставшееся время.

Answer 1

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

Формулы:

• F1 =SUM(B:B)
• F2 =SUMIF(C:C,"Yes",B:B)
• F3 =SUMIF(C:C,"No",B:B)

Вы, наверное, уже знакомы с СУМ. Единственное, что я здесь делаю, отличается от вашего примера, перемещая его в отдельный столбец. Таким образом, вы можете настроить таргетинг на весь столбец B с помощью SUM, и вам не придется корректировать формулу каждый раз, когда вы добавляете больше данных.

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

• Диапазон - это диапазон, который должен быть проверен по критериям. Если третий параметр опущен, это также будет диапазон, из которого будут суммироваться значения. В нашем случае наша условная проверка должна проверить значения в столбце C.
• Критерии - это критерии, которые будут проверены, чтобы определить, входит ли значение в сумму. Текстовые соответствия, как мы сделали здесь, самые простые, но есть также способы использования формул.
• Сумма диапазона - это необязательный параметр. Если опущено, то предполагается, что Range такой же, как Sum Range. Этот параметр указывает, какой диапазон значений будет суммироваться. Для каждой ячейки в диапазоне сумм SUM проверит соответствующую ячейку в диапазоне против критериев, и если проверка вернет ИСТИНА, он добавит значение из диапазона сумм в общую сумму. Если проверка окажется ложной, это значение будет опущено из общей суммы.

Итак, для перевода формул:

• F1 Sum all of the numeric values in column B
• F2 Check for cells in column C which have value "Yes", and sum the corresponding numeric values from column B.
• F3 Check for cells in column C which have value "No", and sum the corresponding numeric values from column B.

Как я упоминал ранее, зачеркивание "Задания a" и "Задания d" может быть выполнено с помощью условного форматирования. Смотрите правило ниже.

Здесь условное форматирование было применено ко всем столбцам A и B с использованием формулы для его критериев. Формула ссылается на $C1 потому что она представляет относительную позицию проверяемой ячейки, начиная с A1, для каждой ячейки, которая должна быть отформатирована. Таким образом, для A1 условное форматирование проверяет, имеет ли C1 значение "Да". Поскольку это не так, A1 не форматируется. Для B2 условное форматирование проверяет C2. $ Перед C в формуле означает, что столбец для проверяемой ячейки всегда равен C. Поскольку $ не ставится перед 1 , строка проверяемой ячейки будет перемещаться в соответствии со строкой ячейки. форматируется (или не форматируется, в зависимости от обстоятельств).

Answer 2

Вы можете проверить, была ли пробита клетка, создав собственную формулу. Это может быть достигнуто с помощью инструментов разработчика и создания модуля.

Нажмите Alt + F11

Вставить модуль

Вставьте следующий код

Function strikethrough(r As Range)
  strikethrough = r.Font.Strikethrough
End Function

Вернитесь в электронную таблицу, и вы можете использовать формулу = зачеркивание (A1), чтобы проверить, была ли пробита ячейка.

Отсюда должно быть возможно выполнить то, что вам нужно, учитывая, что теперь вы знаете, была ли пробита клетка. Я думаю, что формула для достижения этой цели =SUMIFS()