© Сорокин Б.Б. 2016 г.

Как найти несвоевременную обработку данных

Добрый день! Хотел к Вам обратиться за консультацией по работе в Excel. Пожалуй, приведу сразу пример и Вы мне ответите, возможно ли это сделать или нет с помощью формул или "секретов" программы. Итак, у меня есть таблица на N-количество строк (30000), в которой я должен найти все просрочки по времени обработки данных, а именно, несвоевременное создание счетов-фактур.

Сроки следующие:

  1. По поставкам, созданным до 10:00ч текущего рабочего дня исполнитель должен создать СФ до 14:00ч/если позже, то просрочка
  2. По поставкам, созданным до 10:00ч в 1й день после выходных / праздничных дней исполнитель должен создать СФ до 15:00ч/ если позже, то просрочка
  3. По поставкам, созданным после 10:00ч текущего раб. дня исполнитель должен создать СФ до 14.00ч следующего дня.
  4. По поставкам, созданным после 10:00ч текущего раб. дня (перед выходными, праздниками)исполнитель должен создать СФ в 1й день после выходных / праздничных дней до 14.00ч.

Пример с расшифровками ниже.

таблица примера

И так начнем по порядку.

К присланному примеру я добавил еще два листа.

Лист Условия, где напротив временных условий прописал числовые значения времени, которые используются при анализе.

таблица условий

Например:

10 часов = 0,416666666666667

14 часов = 0,583333333333333

15 часов = 0,625

Кстати, на мой взгляд, чтобы предусмотреть все варианты, к описанным выше условиям надо добавить еще два:

1. По поставкам, созданным после 10:00 в 1-й день после выходных / праздничных дней ???

2. По поставкам, созданным до 10:00 в день перед выходными ???

Однако продолжим работать с тем, что есть.

Для того что бы можно оперировать понятием "выходной день" его надо где то явно прописать. Поэтому я создал Лист Календарь, где прописал календарь на 2016 год и пометил все выходные дни в году с учетом праздничных, т.к. ни один Excel не знает всех наших праздников.

Календарь создать совсем не сложно и достаточно быстро. Выделим на свободном листе две ячейки и введем в них даты например 01.01.2016 и 01.02.2016 после этого наводим курсор мышки на правый нижний угол выделенной области с маленьким квадратиком и тянем в низ до нужного положения. При этом даты будут заполняться автоматически.

таблица условий

День недели проставляем при помощи функции

=ДЕНЬНЕД(A2;2)
день недели

Выходной или рабочий день проставляются по формуле

формула выходной рабочий день

Шестой и седьмой день недели выходной, остальные рабочие.

После этого, руками, можно прописать праздничные дни по календарю как выходные дни. Вот наш лист Календарь готов.

На самом листе с таблицей добавил четыре столбца: "Просрочка", "Условие2 После выходного", "Условие3 Перед выходным", "Условие4 после выходного".

условия отбора

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

Условие2 после выходного - функция ВПР возвращает значение "Выходной" если дата последнего изменения поставки первый рабочий день после выходного.

формула

Условие3 перед выходным - функция ВПР возвращает значение "Выходной" если дата последнего изменения поставки последний рабочий день перед выходным.

формула

Условие4 после выходного - функция ВПР возвращает значение "Выходной" если дата создания счета фактуры первый рабочий день после выходных.

формула

Во всех трех условиях функция ВПР находит на листе Календарь дату последнего изменения поставки или создания счета фактуры и определяет был ли это минус/ плюс 1 день выходной и возвращает это значение

В столбце Просрочка введена формула анализирующая условия.

формула

Рассмотрим формулу по частям

G3<D3, дата создания счета-фактуры более ранняя, чем последние изменения поставки, формула возвращает "ОшибкаДаты".

И(D3=G3;E3< Условия!$C$2;H3< Условия!$D$2) = Условие 1, см. лист Условия. Последние изменения поставки и счет фактура сделаны в один день. Последние изменения поставки сделаны до 10:00. Счет-фактура создан до 14:00. Если это условие выполняется т.е. функция И() возвращает ИСТИНА, то формула возвращает значение "Норма1", если не выполняется т.е. функция И() возвращает ЛОЖЬ то проверяется следующее условие.

И((G3-D3)< =1;E3>=Условия!$C$2;H3< Условия!$D$2+1) = Условие 2, см. лист Условия. Последние изменения поставки и счет фактура сделаны в разные дни но промежуток времени между этими событиями менее 1 суток. Последние изменения поставки сделаны после 10:00. Счет-фактура создан до следующего дня 14:00. Если это условие выполняется то формула возвращает значение "Норма2", если не выполняется то проверяется следующее условие.

И(L3="Выходной";E3< Условия!$C$2;H3< Условия!$E$4) = Условие 3, см. лист Условия. Последние изменения поставки сделаны до 10:00 в первый день после выходных. Счет-фактура создан до 15:00 в этот же день. Если это условие выполняется то формула возвращает значение "Норма3", если не выполняется то проверяется следующее условие.

И(G3-D3>1; K3="Выходной"; L3="Выходной"; E3>=Условия!C2; H3<Условия!$D$2) = Условие 4, см. лист Условия.

Последние изменения поставки сделаны после 10:00 в последний рабочий день перед выходными. Счет-фактура создан в первый рабочий день до 14:00 после выходных. Если это условие выполняется то формула возвращает значение "Норма4", если не выполняется то это означает, что мы проверили последовательно все четыре заданные условия, ни одно из них не выполняется, формула возвращает значение "Просрочка".

Файл с примером можно скачать здесь.