Вопрос или проблема
VBA и PowerQuery / Power BI не являются вариантом по ряду причин. Учитывая это, я знаю, что то, что мне нужно, возможно, я просто испытываю трудности с тем, как это сделать.
У меня есть 4 листа. Один лист содержит сотрудников и даты (разбиты по неделям, например, 10/13, 10/14 и т.д.) (примеры будут приведены позже, если я смогу разобраться). Лист 2 содержит данные о входе/выходе, которые используются для заполнения чисел для этого сотрудника в указанную дату на Листе 1. Эта часть работает вполне нормально. Лист 4 содержит праздники, поэтому мы переопределяем отработанное время с помощью кода для праздников. Эта часть также работает нормально. Лист 3 и отпускное время — вот где возникает проблема. Лист 3 содержит список сотрудников и отдельные записи для каждого периода отпуска. Проблема в том, что там указано общее количество часов, количество и дата начала и окончания, и мне нужно преобразовать это в часы на конкретный день, чтобы я мог заполнить эту дату на листе 1 кодом отпуска и количеством часов, взятых в этот день.
Пример (и я буду использовать коды сотрудников, а не имена для конфиденциальности, да и потому что это наш индекс).
Лист 1:
| ID сотрудника | 10/8 | 10/9 | 10/10 |
|---|---|---|---|
| 80332 | 8.00 | VAC24 | 8.00 |
Лист 4:
| ID сотрудника | Дата начала | Дата окончания | Часы |
|---|---|---|---|
| 80332 | 10/9/24 | 10/10/24 | 16 |
| 80332 | 10/16/24 | 10/20/24 | 12 |
Итак, я хочу, чтобы на Листе 1 было написано VAC8 на 10/9 и 10/10.
Формула, которая сейчас используется, ставит “Vac24” на 10/9, но ничего не делает для 10/10 и далее, и вот она (да, она делает много других вещей, тоже):
=IF(AND(RIGHT($F80, 3)="(S)", COUNTIFS(Holidays!$A:$A, K$2)>0), "HOL8", IF(SUMIFS(VacationImport!$F:$F, VacationImport!$B:$B, $E80, VacationImport!$D:$D, K$2, VacationImport!$H:$H, "Taken")>0, CONCAT("VAC", SUMIFS(VacationImport!$F:$F, VacationImport!$B:$B, $E80, VacationImport!$D:$D, K$2)), IF(AND(RIGHT($F80, 3)="(S)", MOD(K$2, 7)>1), 8, IF(H80="","", IF(SUMIFS(HJImport!$F:$F, HJImport!$C:$C, K$2, HJImport!$A:$A, $E80)>0, SUMIFS(HJImport!$F:$F, HJImport!$C:$C, K$2, HJImport!$A:$A, $E80), IF(SUM(SUMIFS(TLImport!$G:$G, TLImport!$B:$B, $E80, TLImport!$L:$L, K$2), SUMIFS(TLImport!$H:$H, TLImport!$B:$B, $E80, TLImport!$L:$L, K$2))>0, SUM(SUMIFS(TLImport!$G:$G, TLImport!$B:$B, $E80, TLImport!$L:$L, K$2), SUMIFS(TLImport!$H:$H, TLImport!$B:$B, $E80, TLImport!$L:$L, K$2)), 0))))))
Может кто-нибудь помочь мне разобраться с этим?
Редактировать: Итак… у меня есть что-то, что работает, но это большая неуклюжесть. Может кто-нибудь помочь мне это упростить? Это также тянет лишний день в начале, так что… не идеально. Вот оно:
=IF(AND(RIGHT($F189, 3)="(S)", COUNTIFS(Holidays!$A:$A, H$2)>0, $G189<(TODAY()-365)), "HOL8", IF(SUM(SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$N:$N, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$O:$O, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$P:$P, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$Q:$Q, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$R:$R, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$S:$S, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$T:$T, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$Q:$Q, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$R:$R, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$S:$S, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$T:$T, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$U:$U, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$V:$V, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$W:$W, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$X:$X, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$Y:$Y, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$Z:$Z, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$AA:$AA, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$AB:$AB, H$2, VacationImport!$AC:$AC, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$AD:$AD, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$AE:$AE, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$AF:$AF, H$2, VacationImport!$AG:$AG, H$2))>0, CONCAT("VAC", SUM(SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$N:$N, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$O:$O, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$P:$P, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$Q:$Q, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$R:$R, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$S:$S, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$T:$T, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$Q:$Q, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$R:$R, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$S:$S, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$T:$T, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$U:$U, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$V:$V, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$W:$W, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$X:$X, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$Y:$Y, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$Z:$Z, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$AA:$AA, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$AB:$AB, H$2, VacationImport!$AC:$AC, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$AD:$AD, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$AE:$AE, H$2), SUMIFS(VacationImport!$M:$M, VacationImport!$B:$B, $E189, VacationImport!$H:$H, "Taken", VacationImport!$AF:$AF, H$2, VacationImport!$AG:$AG, H$2))), IF(AND(RIGHT($F189, 3)="(S)", MOD(H$2, 7)>1), 8, IF(E189="","", IF(SUMIFS(HJImport!$F:$F, HJImport!$C:$C, H$2, HJImport!$A:$A, $E189)>0, SUMIFS(HJImport!$F:$F, HJImport!$C:$C, H$2, HJImport!$A:$A, $E189), IF(SUM(SUMIFS(TLImport!$G:$G, TLImport!$B:$B, $E189, TLImport!$L:$L, H$2), SUMIFS(TLImport!$H:$H, TLImport!$B:$B, $E189, TLImport!$L:$L, H$2))>0, SUM(SUMIFS(TLImport!$G:$G, TLImport!$B:$B, $E189, TLImport!$L:$L, H$2), SUMIFS(TLImport!$H:$H, TLImport!$B:$B, $E189, TLImport!$L:$L, H$2)), 0))))))
Ответ или решение
Конечно! Давайте разберем вашу задачу и постараемся упростить вашу формулу для расчета количества часов, которые работник взял в виде отпуска.
Описание вашей задачи:
Вы работаете с несколькими листами Excel, которые содержат информацию о работниках, их отпусках и праздниках. Ваша конечная цель — правильно отображать часы отпуска в определённые даты на листе 1, учитывая данные из листа 3, который содержит информацию о периодах отпуска (начальная и конечная дата и общее количество часов).
Структура ваших листов:
- Лист 1: Содержит идентификаторы работников и даты.
- Лист 2: Содержит данные о времени прихода и ухода работников.
- Лист 3: Содержит информацию о отпусках (идентификатор работника, начальная дата, конечная дата и количество часов).
- Лист 4: Содержит информацию о праздниках.
Задача
На листе 1, для каждой даты необходимо показать код отпуска и количество часов, если эта дата попадает в период отпуска, который зафиксирован на листе 3.
Возможное решение
Для упрощения формулы, которая у вас уже есть, можно использовать SUMIFS для суммирования часов, взятых в отпуск, и применить логику, чтобы избежать дублирования и учитывать только даты в заданном диапазоне.
Вот улучшенная формула:
=IF(AND(RIGHT($F2, 3)="(S)", COUNTIFS(Holidays!$A:$A, K$2)>0), "HOL8",
IF(AND($G2<TODAY()-365, COUNTIFS(VacationImport!$B:$B,$E2, VacationImport!$C:$C, "<="&K$2, VacationImport!$D:$D, ">="&K$2)>0),
CONCAT("VAC", SUMIFS(VacationImport!$H:$H, VacationImport!$B:$B, $E2, VacationImport!$C:$C, "<="&K$2, VacationImport!$D:$D, ">="&K$2)),
IF(SUMIFS(HJImport!$F:$F, HJImport!$C:$C, K$2, HJImport!$A:$A, $E2)>0,
SUMIFS(HJImport!$F:$F, HJImport!$C:$C, K$2, HJImport!$A:$A, $E2),
IF(SUM(SUMIFS(TLImport!$G:$G, TLImport!$B:$B, $E2, TLImport!$L:$L, K$2), SUMIFS(TLImport!$H:$H, TLImport!$B:$B, $E2, TLImport!$L:$L, K$2))>0, SUM(SUMIFS(TLImport!$G:$G, TLImport!$B:$B, $E2, TLImport!$L:$L, K$2), SUMIFS(TLImport!$H:$H, TLImport!$B:$B, $E2, TLImport!$L:$L, K$2)), 0)))))Объяснение формулы:
- COUNTIFS для проверки наличия выходного дня: Проверяет, есть ли соответствующий выходной.
- SUMIFS для подсчета часов отпуска: Подсчитывает количество часов на данный день, если дата попадает в диапазон отпуска.
- Код в формате VACXX: Формируется только если есть часы, относящиеся к отпуску.
Рекомендации по дальнейшему улучшению
-
Удаление дублирующих
SUMIFS: Попробуйте уменьшить количествоSUMIFS, оборачивая их в другие функции, где это возможно. -
Проверка границ: Убедитесь, что вы обрабатываете даты корректно, чтобы не показать лишние дни.
-
Оптимизация производительности: Если у вас много данных, формулы могут замедлить работу Excel. Подумайте о дополнительных методах оптимизации, таких как использование вспомогательных столбцов для промежуточных вычислений.
Заключение
Управление данными о времени и отпусках может быть сложным, но с правильной формулой и подходом вы сможете сделать его более удобным для пользователей. Если у вас есть другие аспекты работы, которые нуждаются в улучшении, не стесняйтесь делиться ими!