Formula megoldások - Excel tippek

jegyzet

Ez egy olyan cikksorozat, amely a Podcast 2316 kihíváshoz elküldött megoldásokat részletezi.

Míg leginkább Power Query vagy VBA megoldásokra számítottam a problémára, akadtak remek képletmegoldások.

Huszein Korish 7 egyedi képlettel, köztük egy dinamikus tömbképlettel küldött megoldást.

7 egyedi képlet

Sejtképletek
Hatótávolság	Képlet
K13: K36	K13	= Tárgymutató (SZŰRŐ (HA (LEN (TRANSPOSE (SZŰRŐ ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3)))))> 2, TRANSPOSE (SZŰRŐ ($ H $ 3: $ AA $ 3, H3) : AA3> LEN (H3: AA3))), ""), HA (LEN (TRANSPOSE (SZŰRŐ ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3)))))> 2, TRANSPOSE ( SZŰRŐ ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))), "") ""), MATCH (SEQUENCE (COUNTA ($ J $ 13: $ J $ 36) ,, 1,1) , SZekvencia (COUNTA ($ J $ 13: $ J $ 36) / COUNTA ($ B $ 4: $ B $ 9) ,, 1, COUNTA ($ B $ 4: $ B $ 9)), 1))
L13: L36	L13	= ELTÖLTÉS ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + OSZLOPOK ($ L $ 12: $ P $ 12)-OSZLOPOK (L $ 12: $ P $ 12))
M13: M36	M13	= ELTÖLTÉS ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + OSZLOPOK ($ L $ 12: $ P $ 12)-OSZLOPOK (M $ 12: $ P $ 12))
N13: N36	N13	= ELTÖLTÉS ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + OSZLOPOK ($ L $ 12: $ P $ 12)-OSZLOPOK (N $ 12: $ P $ 12))
O13: O36	O13	= ELTÖLTÉS ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + OSZLOPOK ($ L $ 12: $ P $ 12)-OSZLOPOK (O $ 12: $ P $ 12))
P13: P36	P13	= SUM (L13: O13)
J13: J36	J13	= MUTATÓ ($ B $ 4: $ B $ 9, MATCH (MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, SEQUENCE (COUNTA ($ B $ 4: $ B) $ 9), 1,1), 0))
Dinamikus tömbképletek.

Prashanth Sambaraju egy másik képletmegoldást küldött, amely öt képletet használ.

5 képlet megoldás

A fent használt képletek:

Sejtképletek
Hatótávolság	Képlet
J15: J38	J15	= HA (MOD (ROWS ($ J $ 15: J15), 6) = 0,6, MOD (ROWS ($ J $ 15: J15), 6))
K15: K38	K15	= ELTÉRÉS (3 A, 3 J15, 15 15,1, 1 J)
L15: L38	L15	= CONCATENATE ("Alkalmazott", "", KEREK (ROWS ($ J $ 15: J15) / 6,0))
M15: P38	M15	= ELTÖRLÉS ($ A $ 3, $ J15, MATCH ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (OSZLOPOK ($ A: A), 5))
15. kérdés: Q38	Q15	= SUM (M15: P15)

René Martin három egyedi képlettel küldte be ezt a képletoldatot:

3 képlet megoldás

A fentiekben használt képletek:

Sejtképletek
Hatótávolság	Képlet
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= HA (OSZLOP () = 9, ELTOLÁS ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Munkavállaló" & ROUNDUP (ROW (A1) / 6, 0), HA (OSZLOP () = 15, SUM (E13: H13), ELTÖLTÉS ($ G $ 3, MOD (ROW (A6), 6) + 1, ROUNDUP (ROW (A1) / 6,0) * 5- 7 + OSZLOP (A1)))))
I14: N36	I14	= HA (OSZLOP () = 9, ELTOLÁS ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, "Employee" & ROUNDUP (ROW (A2) / 6, 0), OFFSET ($ G $ 3, MOD (ROW (A7), 6) + 1, ROUNDUP (ROW (A2) / 6,0 * 5-7 + COLUMN (A2))))

René Martin alternatív megoldása:

Sejtképletek
Hatótávolság	Képlet
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= HA (OSZLOP () = 9, ELTOLÁS ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Munkavállaló" & ROUNDUP (ROW (A1) / 6, 0), HA (OSZLOP () = 15, SUM (E13: H13), ELTÖLTÉS ($ G $ 3, MOD (ROW (A6), 6) + 1, ROUNDUP (ROW (A1) / 6,0) * 5- 7 + OSZLOP (A1)))))
I14: N36	I14	= HA (OSZLOP () = 9, ELTOLÁS ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, "Employee" & ROUNDUP (ROW (A2) / 6, 0), OFFSET ($ G $ 3, MOD (ROW (A7), 6) + 1, ROUNDUP (ROW (A2) / 6,0 * 5-7 + COLUMN (A2))))

Roger Govier, az Excel MVP képletmegoldást küldött. Először is, Roger törölte a felesleges oszlopokat az eredeti adatokból. Roger rámutat, hogy ott hagyhatja őket, de akkor megfelelően be kell állítania az oszlopok indexszámait.

Roger három megnevezett tartományt használt. Ez az ábra a kijelölt _sorokat mutatja.