Vormelahendused - Exceli näpunäited

Märge

See on üks artiklite seeria, mis kirjeldab üksikasjalikult Podcast 2316 väljakutsele saadetud lahendusi.

Kuigi ma ootasin probleemile peamiselt Power Query või VBA lahendusi, oli ka lahedaid valemilahendusi.

Hussein Korish saatis lahenduse 7 unikaalse valemiga, sealhulgas dünaamilise massiivi valemiga.

7 unikaalset valemit

Lahtri valemid
Vahemik	Valem
K13: K36	K13	= INDEKS (FILTER (KUI (LEN (TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))))))> 2, TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3) : AA3> LEN (H3: AA3))), ""), KUI (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) , Järjestus (COUNTA ($ J $ 13: $ J $ 36) / COUNTA ($ B $ 4: $ B $ 9) ,, 1, COUNTA ($ B $ 4: $ B $ 9)), 1))
L13: L36	L13	= KOMPLEKT ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + VEERUD ($ L $ 12: $ P $ 12) -COLUMNS (L $ 12: $ P $ 12))
M13: M36	M13	= KOMPLEKT ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + VEERUD ($ L $ 12: $ P $ 12) -COLUMNS (M $ 12: $ P $ 12))
N13: N36	N13	= KOMPLEKT ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + VEERUD ($ L $ 12: $ P $ 12) -COLUMNS (N $ 12: $ P $ 12))
O13: O36	O13	= KOMPLEKT ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + VEERUD ($ L $ 12: $ P $ 12) -COLUMNS (O $ 12: $ P $ 12))
P13: P36	P13	= SUM (L13: O13)
J13: J36	J13	= INDEKS ($ B $ 4: $ B $ 9, MATCH (MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, järjestus (COUNTA ($ B $ 4: $ B 9 dollarit), 1,1), 0))
Dünaamilise massiivi valemid.

Prashanth Sambaraju saatis teise valemilahenduse, mis kasutab viit valemit.

5 valemi lahendus

Eespool kasutatud valemid:

Lahtri valemid
Vahemik	Valem
J15: J38	J15	= KUI (MOD (RIDAD ($ J $ 15: J15), 6) = 0,6, MOD (RIDAD ($ J $ 15: J15), 6))
K15: K38	K15	= KOMPLEKT ($ A $ 3, J15, J $ 15,1,1)
L15: L38	L15	= CONCATENATE ("Töötaja", "", ROUNDUP (RIDAD ($ J $ 15: J15) / 6,0))
M15: P38	M15	= KOMPLEKT ($ A $ 3, $ J15, MATCH ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (VEERUD ($ A: A), 5))
Q15: Q38	Q15	= SUMMA (M15: P15)

René Martin saatis selle valemilahuse kolme unikaalse valemiga:

3 valemi lahendus

Ülaltoodud valemid:

Lahtri valemid
Vahemik	Valem
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= KUI (VEERG () = 9, KORRALDUS ($ A $ 2, MOD (RIDA (A1), 6) +1,0), KUI (VEERG () = 10, "Töötaja" & RINGUP (RIDA (A1) / 6, 0), KUI (VEERG () = 15, SUMMA (E13: H13), NÕUETE ($ G $ 3, MOD (RIDA (A6), 6) + 1, ÜMBERKORRATUD (RIDA (A1) / 6,0) * 5- 7 + VEERG (A1)))))
I14: N36	I14	= KUI (VEERG () = 9, NÕUETE ($ A $ 2, MOD (RIDA (A2), 6) +1,0), KUI (VEERG () = 10, "Töötaja" & ROUNDUP (RIDA (A2) / 6, 0), OFFSET ($ G $ 3, MOD (RIDA (A7), 6) + 1, ÜMBERKORRALDAMINE (RIDA (A2) / 6,0) * 5-7 + VEERG (A2))))

René Martini alternatiivne lahendus:

Lahtri valemid
Vahemik	Valem
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= KUI (VEERG () = 9, KORRALDUS ($ A $ 2, MOD (RIDA (A1), 6) +1,0), KUI (VEERG () = 10, "Töötaja" & RINGUP (RIDA (A1) / 6, 0), KUI (VEERG () = 15, SUMMA (E13: H13), NÕUETE ($ G $ 3, MOD (RIDA (A6), 6) + 1, ÜMBERKORRATUD (RIDA (A1) / 6,0) * 5- 7 + VEERG (A1)))))
I14: N36	I14	= KUI (VEERG () = 9, NÕUETE ($ A $ 2, MOD (RIDA (A2), 6) +1,0), KUI (VEERG () = 10, "Töötaja" & ROUNDUP (RIDA (A2) / 6, 0), OFFSET ($ G $ 3, MOD (RIDA (A7), 6) + 1, ÜMBERKORRALDAMINE (RIDA (A2) / 6,0) * 5-7 + VEERG (A2))))

Exceli MVP Roger Govier saatis valemilahenduse. Esiteks kustutas Roger mittevajalikud veerud algandmetest. Roger juhib tähelepanu sellele, et võite need sinna jätta, kuid siis peate veeru indeksinumbreid sobivalt kohandama.

Roger kasutas kolme nimega vahemikku. See joonis näitab valitud _ridu.