Итак, для контекста: эта ссылка помогла мне начать, но теперь я застрял, пытаясь применить ее к своему варианту использования: Excel — суммирование значений на основе столбца, который соответствует другому столбцу в другой таблице.
У меня есть почасовой журнал, куда я вношу данные о своей работе, включая дату, информацию о проекте и задаче, по которой она будет выставлена, а также время начала и окончания отработанных часов, что позволяет подсчитать, сколько часов я отработал с введенного времени (округляя до ближайших 15 минут).
Вот (приблизительно) как выглядит журнал («timeLog») с примерами данных:
| А | Б | С | Д | Э | |
|---|---|---|---|---|---|
| 1 | Дата | Проект и задача | Время начала | Время окончания | Часы |
| 2 | 01.01.2023 | Проект 1:Задача 1 | 8:00 УТРА | 10:00 УТРА | 2 |
| 3 | 01.01.2023 | Проект 2:Задача 1 | 10:00 УТРА | 4:00 ВЕЧЕРА | 6 |
| 4 | 06.01.2023 | Часы работы в праздничные дни | 8:00 УТРА | 4:00 ВЕЧЕРА | 8 |
| 5 | 07.01.2023 | Больничный | 8:00 УТРА | 12:00 PM | 4 |
| 6 | 06.02.2023 | Проект 1:Задача 1 | 8:00 УТРА | 4:00 ВЕЧЕРА | 8 |
[Транскрибировано изэто изображение.]
На другом листе у меня есть таблица, в которой перечислены все проекты и связанные с ними задачи (столбец «ID проекта и задачи»). Каждый проект имеет связанный с ним тип (а именно, отработанные часы, праздник, больничный или отпуск).
Вот как выглядит таблица информации о проекте («projectTable»):
| А | Б | |
|---|---|---|
| 1 | Идентификатор проекта и задачи | Тип идентификатора |
| 2 | Проект 1:Задача 1 | Работал |
| 3 | Проект 2:Задача 1 | Работал |
| 4 | Часы работы в праздничные дни | Праздничный день |
| 5 | Больничный | Больной |
[Транскрибировано изэто изображение.]
Для расчета того, сколько я получаю больничных/отпускных, мне нужно вытащить, сколько часов я отработал в данном расчетном периоде. Однако мне также нужно, чтобы общее количество часов, которые я отправляю, включая больничные/отпускные/праздничные, оставалось в почасовом журнале, поскольку я использую их для общих расчетов FTE.
Вот что я сейчас использую для суммирования всех часов за расчетный период (26.12.22-25.01.23), связанных с проектом с идентификатором типа «worked»:
=SUM(IFERROR(IF(COUNTIFS(projectTable[Project & Task ID],FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0),projectTable[Type ID],"Worked")>0,timeLog[Hours],0),0))
Сначала я использую FILTER, чтобы получить все записи таблицы за указанный период.
FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0)
Затем функция COUNTIFS проверяет, есть ли какие-либо проекты с TypeID «Worked»:
COUNTIFS(projectTable[Project & Task ID],FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0),projectTable[Type ID],"Worked")
Затем ЕСЛИ возвращает значение часов для каждой из этих строк, ЕСЛИОШИБКА «исправляет» ошибку, которую я постоянно получал, а СУММ вычисляет общее количество «отработанных» часов за период.
Однако это не кажется лучшим способом сделать это — он хрупкий, возвращает ошибки, которых я не ожидаю, и в целом я чувствую, что иду не тем путем. Как лучше это сделать?
решение1
Я не изучал ваше решение, но, на первый взгляд, я согласен, что оно кажется излишне сложным. Я предлагаю вам добавить шестой столбец в ваш почасовой журнал, содержащий
=VLOOKUP(B2, projectTable!$A$2:$B$5, 2, FALSE)
Это переведет «Проект и задачу» из почасового журнала в «Идентификатор типа» из таблицы информации о проекте следующим образом:
| А | Б | С | Д | Э | Ф | |
|---|---|---|---|---|---|---|
| 1 | Дата | Проект и задача | Время начала | Время окончания | Часы | Тип идентификатора |
| 2 | 01.01.2023 | Проект 1:Задача 1 | 8:00 УТРА | 10:00 УТРА | 2 | Работал |
| 3 | 01.01.2023 | Проект 2:Задача 1 | 10:00 УТРА | 4:00 ВЕЧЕРА | 6 | Работал |
| 4 | 06.01.2023 | Часы работы в праздничные дни | 8:00 УТРА | 4:00 ВЕЧЕРА | 8 | Праздничный день |
| 5 | 07.01.2023 | Больничный | 8:00 УТРА | 12:00 PM | 4 | Больной |
| 6 | 06.02.2023 | Проект 1:Задача 1 | 8:00 УТРА | 4:00 ВЕЧЕРА | 8 | Работал |
Тогда это становится простым вопросом использования COUNTIFSи SUMIFS. Например, где-нибудь еще (например, A8:B10), введите типы в A8:A10, введите
=SUMIFS($E$2:$E$6, $A$2:$A$6,">"&DATE(2022,12,25), $A$2:$A$6,"<"&DATE(2023,1,23), $F$2:$F$6,A8)
в B8 и перетащите/заполните вниз:
| А | Б | ||
|---|---|---|---|
| 8 | Работал | 8 | |
| 9 | Праздничный день | 8 | |
| 10 | Больной | 4 |
Конечно, жестко заданные даты можно заменить ссылками на ячейки.
Обратите внимание, что я ответил на вопрос, немного похожий, но более сложный:вопрос восемь лет назад.