Riešenia vzorcov - tipy pre Excel

Poznámka

Toto je jeden zo série článkov s podrobnými riešeniami zaslanými k výzve Podcast 2316.

Aj keď som očakával väčšinou Power Query alebo VBA riešenia problému, boli tu nejaké super formule riešenia.

Husajn Korish poslal riešenie so 7 jedinečnými vzorcami vrátane dynamického poľa.

7 jedinečných vzorcov

Bunkové vzorce
Rozsah	Vzorec
K13: K36	K13	= INDEX (FILTER (AK (LEN (TRANSPÓZA (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3)))))> 2, TRANSPÓZA (FILTER ($ H $ 3: $ AA $ 3, H3 : AA3> LEN (H3: AA3))), ""), IF (LEN (TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))))> 2, TRANSPOSE ( FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))), "") "), ZÁPAS (POSTUP (POSTUP (COUNTA ($ J $ 13: $ J $ 36) ,, 1,1) , SEQUENCE (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, TRANSPÓZA (ZÁPAS (K13, $ H $ 3: $ AA $ 3,0) ) + STĹPCE ($ L $ 12: $ P $ 12) - STĹPCE (L $ 12: $ P $ 12))
M13: M36	M13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPÓZA (ZÁPAS (K13, $ H $ 3: $ AA $ 3,0) ) + STĹPCE ($ L $ 12: $ P $ 12) - STĹPCE (M $ 12: $ P $ 12))
N13: N36	N13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPÓZA (ZÁPAS (K13, $ H $ 3: $ AA $ 3,0) ) + STĹPCE ($ L $ 12: $ P $ 12) - STĹPCE (N $ 12: $ P $ 12))
O13: O36	O13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPÓZA (ZÁPAS (K13, $ H $ 3: $ AA $ 3,0) ) + STĹPCE ($ L $ 12: $ P $ 12) - STĹPCE (O $ 12: $ P $ 12))
P13: P36	P13	= SUMA (L13: O13)
J13: J36	J13	= INDEX ($ B $ 4: $ B $ 9, MATCH (MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9))) + 1, POSTUPNOSŤ (COUNTA ($ B $ 4: $ B) $ 9), 1,1), 0))
Vzorce dynamického poľa.

Prashanth Sambaraju poslal ďalšie riešenie vzorcov, ktoré používa päť vzorcov.

5 vzorcov riešenia

Vyššie uvedené vzorce:

Bunkové vzorce
Rozsah	Vzorec
J15: J38	J15	= IF (MOD (RIADKY ($ J $ 15: J15), 6) = 0,6, MOD (RIADKY ($ J $ 15: J15), 6)))
K15: K38	K15	= OFFSET ($ A $ 3, J15, J $ 15,1,1)
L15: L38	L15	= CONCATENATE ("Employee", "", ROUNDUP (RIADKY ($ J $ 15: J15) / 6,0))
M15: P38	M15	= OFFSET ($ A $ 3, $ J15, MATCH ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (STĹPCE ($ A: A), 5))
Q15: Q38	Q15	= SUMA (M15: P15)

René Martin poslal toto riešenie vzorca s tromi jedinečnými vzorcami:

Riešenie 3 vzorcov

Vzorce použité vo vyššie uvedenom:

Bunkové vzorce
Rozsah	Vzorec
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (RIADOK (A1), 6) +1,0), IF (COLUMN () = 10, „Zamestnanec“ & ROUNDUP (RIADOK (A1) / 6, 0), IF (COLUMN () = 15, SUMA (E13: H13), OFFSET ($ G $ 3, MOD (RIADOK (A6), 6) + 1, ROUNDUP (RIADOK (A1) / 6,0) * 5) 7 + STĹPEC (A1)))))
I14: N36	I14	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (RIADOK (A2), 6) +1,0), IF (COLUMN () = 10, „Zamestnanec“ & ROUNDUP (RIADOK (A2) / 6, 0), OFFSET ($ G $ 3, MOD (RIADOK (A7), 6) + 1, ROUNDUP (RIADOK (A2) / 6,0) * 5-7 + STĹPEC (A2))))

Alternatívne riešenie od Reného Martina:

Bunkové vzorce
Rozsah	Vzorec
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (RIADOK (A1), 6) +1,0), IF (COLUMN () = 10, „Zamestnanec“ & ROUNDUP (RIADOK (A1) / 6, 0), IF (COLUMN () = 15, SUMA (E13: H13), OFFSET ($ G $ 3, MOD (RIADOK (A6), 6) + 1, ROUNDUP (RIADOK (A1) / 6,0) * 5) 7 + STĹPEC (A1)))))
I14: N36	I14	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (RIADOK (A2), 6) +1,0), IF (COLUMN () = 10, „Zamestnanec“ & ROUNDUP (RIADOK (A2) / 6, 0), OFFSET ($ G $ 3, MOD (RIADOK (A7), 6) + 1, ROUNDUP (RIADOK (A2) / 6,0) * 5-7 + STĹPEC (A2))))

Excel MVP Roger Govier poslal formulné riešenie. Roger najskôr odstránil nepotrebné stĺpce z pôvodných údajov. Roger poukazuje na to, že by ste ich tam mohli nechať, ale potom musíte príslušne upraviť indexové čísla stĺpcov.

Roger použil tri pomenované rozsahy. Tento obrázok zobrazuje vybraté položky.