Rešitve za formule - nasveti za Excel

Opomba

To je eden iz serije člankov, ki podrobno opisujejo rešitve, poslane za izziv Podcast 2316.

Medtem ko sem pričakoval večinoma rešitve problema Power Query ali VBA, je bilo nekaj odličnih formularnih rešitev.

Hussein Korish je poslal rešitev s 7 edinstvenimi formulami, vključno z dinamično formulo matrike.

7 edinstvenih formul

Formule celic
Doseg	Formula
K13: K36	K13	= INDEX (FILTER (IF (LEN (TRANSOSE (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))))>> 2, PREGLED (FILTER ($ H $ 3: $ AA $ 3, H3 : AA3> LEN (H3: AA3))), ""), ČE (LEN (TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))))>> 2, PROZOR ( FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))), "") ""), UTEK (SEKVENCA (TOČKA ($ 13 $: $ J $ 36), 1,1) , SEKVENCA (KONTA ($ 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, TRANZOZIRAJ (UTEK (K13, $ H $ 3: $ AA $ 3,0) ) + STOLPCI ($ 12 L $: $ P $ 12) -STOLPCI (L $ 12: $ P $ 12))
M13: M36	M13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANZOZIRAJ (UTEK (K13, $ H $ 3: $ AA $ 3,0) ) + STOLPCI ($ 12 L: $ P $ 12) -STOLPCI (M $ 12: $ P $ 12))
N13: N36	N13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANZOZIRAJ (UTEK (K13, $ H $ 3: $ AA $ 3,0) ) + STOLPCI ($ 12 L $: $ P $ 12) -STOLPCI (N $ 12: $ P $ 12))
O13: O36	O13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANZOZIRAJ (UTEK (K13, $ H $ 3: $ AA $ 3,0) ) + STOLPCI ($ 12 L $: $ P $ 12) -STOLPCI (O $ 12: $ P $ 12))
P13: P36	P13	= SUM (L13: O13)
J13: J36	J13	= INDEKS ($ B $ 4: $ B $ 9, UTEK (MOD (COUNTA ($ J $ 12: J12) -1, TOČKA ($ B $ 4: $ B $ 9)) + 1, SEKVENCA (TOČKA ($ B $ 4: $ B 9 dolarjev), 1,1), 0))
Formule dinamičnega polja.

Prashanth Sambaraju je poslal drugo rešitev za formule, ki uporablja pet formul.

5 rešitev za formule

Zgoraj uporabljene formule:

Formule celic
Doseg	Formula
J15: J38	J15	= ČE (MOD (VRTNICE ($ J $ 15: J15), 6) = 0,6, MOD (VRSTE ($ 15 $: J15), 6))
K15: K38	K15	= OFFSET ($ A $ 3, J15, J $ 15,1,1)
L15: L38	L15	= CONCATENATE ("Zaposleni", "", ROUNDUP (VRSTICE (15 J $: 15 J15) / 6,0))
M15: P38	M15	= OFFSET ($ A $ 3, $ J15, UTEK ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (STOLPCI ($ A: A), 5))
V15: V38	Q15	= SUM (M15: P15)

René Martin je v tej raztopini formule poslal tri edinstvene formule:

3 raztopina formul

Formule, uporabljene v zgornjem:

Formule celic
Doseg	Formula
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Zaposleni" in ROUNDUP (ROW (A1) / 6, 0), IF (STOLPEC () = 15, SUM (E13: H13), OFFSET ($ G $ 3, MOD (ROW (A6), 6) + 1, ROUNDUP (ROW (A1) / 6,0) * 5- 7 + STOLPEC (A1)))))
I14: N36	I14	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, "Zaposleni" in ROUNDUP (ROW (A2) / 6, 0), OFFSET ($ G $ 3, MOD (ROW (A7), 6) + 1, ROUNDUP (ROW (A2) / 6,0) * 5-7 + STOLPEC (A2))))

Alternativna rešitev podjetja René Martin:

Formule celic
Doseg	Formula
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Zaposleni" in ROUNDUP (ROW (A1) / 6, 0), IF (STOLPEC () = 15, SUM (E13: H13), OFFSET ($ G $ 3, MOD (ROW (A6), 6) + 1, ROUNDUP (ROW (A1) / 6,0) * 5- 7 + STOLPEC (A1)))))
I14: N36	I14	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, "Zaposleni" in ROUNDUP (ROW (A2) / 6, 0), OFFSET ($ G $ 3, MOD (ROW (A7), 6) + 1, ROUNDUP (ROW (A2) / 6,0) * 5-7 + STOLPEC (A2))))

Excel MVP Roger Govier je poslal rešitev za formulo. Najprej je Roger iz prvotnih podatkov izbrisal nepotrebne stolpce. Roger poudarja, da bi jih lahko pustili tam, potem pa morate ustrezno prilagoditi indeksne številke stolpcev.

Roger je uporabil tri imenovane obsege. Ta slika prikazuje izbrane vrstic.