Решения за формули - Съвети за Excel

Забележка

Това е една от поредицата статии, описващи подробно решения, изпратени за предизвикателството на Podcast 2316.

Въпреки че очаквах предимно Power Query или VBA решения на проблема, имаше някои готини решения за формули.

Хюсеин Кориш изпрати решение със 7 уникални формули, включително формула за динамичен масив.

7 уникални формули

Клетъчни формули
Обхват	Формула
K13: K36	K13	= ИНДЕКС (ФИЛТЪР (АКО (ЛЕЧА (ПРОЗЕРВАНЕ (ФИЛТЪР ($ H $ 3: $ AA $ 3, H3: AA3> ЛЕЩА (H3: AA3))))>> 2, ПРОЗРАСТ (ФИЛТЪР ($ H $ 3: $ AA $ 3, H3 : AA3> LEN (H3: AA3))), ""), АКО (LEN (ПРОЗЕРВАНЕ (ФИЛТЪР ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))))> 2, ПРОЗРЕЗ ( ФИЛТЪР ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))), "") ""), СЪВПАДАНИЕ (ПОСЛЕДОВАТЕЛНОСТ (КОНТА ($ J $ 13: $ J $ 36), 1,1) , ПОСЛЕДОВАТЕЛНОСТ (COUNTA ($ J $ 13: $ J $ 36) / COUNTA ($ B $ 4: $ B $ 9) ,, 1, COUNTA ($ B $ 4: $ B $ 9)), 1))
L13: L36	L13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, ТРАНЗОЗИРАНЕ (СЪВПЕЧЕНИЕ (K13, $ H $ 3: $ AA $ 3,0) ) + КОЛОНИ ($ L $ 12: $ P $ 12) -КОЛОНИ (L $ 12: $ P $ 12))
М13: М36	М13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, ТРАНЗОЗИРАНЕ (СЪВПЕЧЕНИЕ (K13, $ H $ 3: $ AA $ 3,0) ) + КОЛОНИ ($ L $ 12: $ P $ 12) -КОЛОНИ (M $ 12: $ P $ 12))
N13: N36	N13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, ТРАНЗОЗИРАНЕ (СЪВПЕЧЕНИЕ (K13, $ H $ 3: $ AA $ 3,0) ) + КОЛОНИ ($ L $ 12: $ P $ 12) -КОЛОНИ (N $ 12: $ P $ 12))
O13: O36	O13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, ТРАНЗОЗИРАНЕ (СЪВПЕЧЕНИЕ (K13, $ H $ 3: $ AA $ 3,0) ) + КОЛОНИ ($ L $ 12: $ P $ 12) -КОЛОНИ (O $ 12: $ P $ 12))
P13: P36	Р13	= SUM (L13: O13)
J13: J36	J13	= ИНДЕКС ($ B $ 4: $ B $ 9, съвпадение (MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, ПОСЛЕДОВАТЕЛНОСТ (COUNTA ($ B $ 4: $ B $ 9), 1,1), 0))
Формули на динамичен масив.

Prashanth Sambaraju изпрати друго решение за формули, което използва пет формули.

5 формули решение

Формулите, използвани по-горе:

Клетъчни формули
Обхват	Формула
J15: J38	J15	= АКО (MOD (РЯДОВЕ ($ J $ 15: J15), 6) = 0,6, MOD (РЕДОВЕ ($ J $ 15: J15), 6))
K15: K38	K15	= OFFSET ($ A $ 3, J15, J $ 15,1,1)
L15: L38	L15	= КОНКАТЕНАТ ("Служител", "", КРЪГ (РЯДОВЕ ($ J $ 15: J15) / 6,0))
M15: P38	М15	= OFFSET ($ A $ 3, $ J15, MATCH ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (КОЛОНИ ($ A: A), 5))
Q15: Q38	Q15	= SUM (M15: P15)

Рене Мартин изпрати това решение за формули с три уникални формули:

3 формула решение

Формулите, използвани в горното:

Клетъчни формули
Обхват	Формула
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Служител" & ROUNDUP (ROW (A1) / 6, 0), АКО (КОЛОНА) = 15, СУММА (E13: H13), ОФСЕТ ($ G $ 3, MOD (РЯД (A6), 6) + 1, КРЪГ (РЯД (A1) / 6,0) * 5- 7 + КОЛОНА (A1)))))
I14: N36	I14	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, "Служител" & ROUNDUP (ROW (A2) / 6, 0), OFFSET ($ G $ 3, MOD (ROW (A7), 6) + 1, ROUNDUP (ROW (A2) / 6,0) * 5-7 + КОЛОНА (A2))))

Алтернативно решение от René Martin:

Клетъчни формули
Обхват	Формула
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Служител" & ROUNDUP (ROW (A1) / 6, 0), АКО (КОЛОНА) = 15, СУММА (E13: H13), ОФСЕТ ($ G $ 3, MOD (РЯД (A6), 6) + 1, КРЪГ (РЯД (A1) / 6,0) * 5- 7 + КОЛОНА (A1)))))
I14: N36	I14	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, "Служител" & ROUNDUP (ROW (A2) / 6, 0), OFFSET ($ G $ 3, MOD (ROW (A7), 6) + 1, ROUNDUP (ROW (A2) / 6,0) * 5-7 + КОЛОНА (A2))))

Excel MVP Roger Govier изпрати решение за формула. Първо, Роджър изтри ненужните колони от първоначалните данни. Роджър посочва, че бихте могли да ги оставите там, но след това трябва да коригирате по подходящ начин индексните номера на колоните.

Роджър използва три имена. Тази фигура показва избраните _row.