Řešení vzorců - Excel tipy

Poznámka

Toto je jeden z řady článků s podrobnými řešeními zaslanými pro výzvu Podcast 2316.

I když jsem očekával hlavně řešení Power Query nebo VBA řešení, byla tam nějaká skvělá formule řešení.

Hussein Korish poslal řešení se 7 jedinečnými vzorci, včetně vzorce s dynamickým polem.

7 jedinečných vzorců

Buněčné vzorce
Rozsah	Vzorec
K13: K36	K13	= INDEX (FILTER (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))), ""), 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))), "") "), MATCH (SEQUENCE (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, TRANSPOZICE (ZÁPAS (K13, $ H $ 3: $ AA $ 3,0) ) + SLOUPCE ($ L $ 12: $ P $ 12) - SLOUPCE (L $ 12: $ P $ 12))
M13: M36	M13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOZICE (ZÁPAS (K13, $ H $ 3: $ AA $ 3,0) ) + SLOUPCE ($ L $ 12: $ P $ 12) - SLOUPCE (M $ 12: $ P $ 12))
N13: N36	N13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOZICE (ZÁPAS (K13, $ H $ 3: $ AA $ 3,0) ) + SLOUPCE ($ L $ 12: $ P $ 12) - SLOUPCE (N $ 12: $ P $ 12))
O13: O36	O13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOZICE (ZÁPAS (K13, $ H $ 3: $ AA $ 3,0) ) + SLOUPCE ($ L $ 12: $ P $ 12) - SLOUPCE (O $ 12: $ P $ 12))
P13: P36	P13	= SUM (L13: O13)
J13: J36	J13	= INDEX ($ B $ 4: $ B $ 9, MATCH (MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9))) + 1, SEKVENCE (COUNTA ($ B $ 4: $ B) $ 9), 1,1), 0))
Dynamické maticové vzorce.

Prashanth Sambaraju poslal další řešení vzorců, které používá pět vzorců.

Řešení 5 vzorců

Výše uvedené vzorce:

Buněčné vzorce
Rozsah	Vzorec
J15: J38	J15	= IF (MOD (ŘÁDKY ($ J $ 15: J15), 6) = 0,6, MOD (ŘÁDKY ($ J $ 15: J15), 6))
K15: K38	K15	= OFFSET ($ A $ 3, J15, J $ 15,1,1)
L15: L38	L15	= CONCATENATE ("Zaměstnanec", "", ROUNDUP (ŘADY ($ J $ 15: J15) / 6,0))
M15: P38	M15	= OFFSET ($ A $ 3, $ J15, MATCH ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (SLOUPCE ($ A: A), 5))
Q15: Q38	Q15	= SUM (M15: P15)

René Martin poslal toto řešení vzorce se třemi jedinečnými vzorci:

Řešení 3 vzorců

Vzorce použité ve výše uvedeném:

Buněčné vzorce
Rozsah	Vzorec
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Zaměstnanec" & ROUNDUP (ROW (A1) / 6, 0), IF (COLUMN () = 15, SUMA (E13: H13), OFFSET ($ G $ 3, MOD (ŘÁDEK (A6), 6) + 1, ROUNDUP (ŘÁDEK (A1) / 6,0) * 5) 7 + SLOUPEC (A1)))))
I14: N36	I14	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, "Zaměstnanec" & ROUNDUP (ROW (A2) / 6, 0), OFFSET ($ G $ 3, MOD (ŘÁDEK (A7), 6) + 1, ROUNDUP (ŘÁDEK (A2) / 6,0) * 5-7 + SLOUPEC (A2))))

Alternativní řešení od Reného Martina:

Buněčné vzorce
Rozsah	Vzorec
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Zaměstnanec" & ROUNDUP (ROW (A1) / 6, 0), IF (COLUMN () = 15, SUMA (E13: H13), OFFSET ($ G $ 3, MOD (ŘÁDEK (A6), 6) + 1, ROUNDUP (ŘÁDEK (A1) / 6,0) * 5) 7 + SLOUPEC (A1)))))
I14: N36	I14	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, "Zaměstnanec" & ROUNDUP (ROW (A2) / 6, 0), OFFSET ($ G $ 3, MOD (ŘÁDEK (A7), 6) + 1, ROUNDUP (ŘÁDEK (A2) / 6,0) * 5-7 + SLOUPEC (A2))))

Excel MVP Roger Govier poslal formulační řešení. Roger nejprve odstranil zbytečné sloupce z původních dat. Roger poukazuje na to, že je tam můžete nechat, ale pak musíte odpovídajícím způsobem upravit čísla indexů sloupců.

Roger použil tři pojmenované rozsahy. Na tomto obrázku je _vybráno vybrané.