Комплексный вложенный IFS в Excel

Question

Таким образом, у меня есть этот вывод для отслеживания моей системы заявок, которая имеет следующие столбцы:

1. create date 
2. resolved date
3. location (A,B,C,D)
4. item (1,2,3,4)

Как мне создать формулу, которая может дать мне вывод средних дней открытия элемента в неделю?

Например, на 32-й неделе 2015 года, сколько в среднем дней открытых позиций, отнесенных к категории "1" в местоположении "A"?

Это должно быть равно (общее количество дней, открытых для всех билетов "1") / (общее количество билетов, открытых для позиции "1"), при условии, что я уже знаю, что общее количество билетов открывается.

И (resolved_date) - (create_date) if (последний день недели)> (resolved_date), это означает, что заявка разрешена в течение отчетной недели; = (последний день отчетной недели) - (create_date) if (последний день отчетной недели) <(resolved_date), этот указательный билет разрешается после отчетной недели; = (последний день отчетной недели) - (create_date), если resolved_date = null, это означает, что заявка на данный момент не разрешена.

Я имею в виду вложенную формулу ifs, подобную этой IF(условие1, value_if_true1, IF(условие2, value_if_true2, value_if_false2)), но недостаточно знакома с Excel для ее создания.

Спасибо!

Answer 1

Учитывая то, что вы сказали, по моему мнению, было бы проще разбить данные на понятные шаги. Смотрите прикрепленный. Я создал несколько случайных данных и нашел способ определения местоположения и метрики, которую вы описываете в начале любой рабочей недели. Я бы порекомендовал, чтобы полковник A - D был предметом, который вы описываете как 1-4, а затем делал столбцы смежными E - H.

A = Create Date
B = Resolved Date
C = Location
D = Item


Formulas in each columns, row 2:
E:  =IF($K$8>B2,"Closed",IF(A2<$K$8,"Open","Not Yet Open"))
F:  =C2&D2
G:  =IF(AND($F2=$K$5,$K$8>A2),1,0)
H:  =IF(AND($F2=$K$5,$E2="Open"),$K$8-A2,0)

Сделать вкладку меню. В столбце A поместите Locations в ячейку A1, затем все местоположения под ней. Выберите местоположения, включая заголовок, и нажмите Ctrl + Shift + F3. Это назовет диапазон местоположений (Меню!A2:An), Местоположения. Сделайте то же самое с Элементами в столбце B. Теперь мы можем сделать некоторые проверки данных. Теперь сделайте вкладку календаря. Поместите первый день каждого WW в один столбец и метку WW в следующем столбце. Например (28.12.2014 может быть первый день Первой мировой войны). Добавьте 7 первую ячейку даты и перетащите ее вниз. Перетащите ячейку WW1 вниз, чтобы сделать ее соответствующей. Назовите диапазон WW "WW". Выберите даты и соответствующие метки WW и назовите этот 2-колоночный диапазон "Календарь"

Вернитесь на страницу с вашими данными. И сделайте следующее:

Location: (K3) Data Validation, List, Range =Locations
Item: (K4) Data Validation, List, Range =Items
Location + Item: (K5) =K3&K4

Work Week: (K7) Data Validation, List, Range =WW
First Day: (K8) =INDEX(Calendar,MATCH(K7,WW,0),1)

Total Days Open: (K10) =SUM(H:H)
Total Tickets:  (K11) =SUM(G:G)
Avg Days Open:  (K12) =K10/K11

Теперь вы сможете выбрать местоположение и элемент с выпадающими списками, а затем рабочую неделю и получить свой показатель.