Formelløsninger - Excel-tip

Bemærk

Dette er en af en række artikler, der beskriver løsninger sendt til Podcast 2316-udfordringen.

Mens jeg for det meste forventede Power Query- eller VBA-løsninger på problemet, var der nogle seje formelløsninger.

Hussein Korish sendte en løsning med 7 unikke formler, inklusive en dynamisk matrixformel.

7 unikke formler

Celleformler
Rækkevidde	Formel
K13: K36	K13	= INDEX (FILTER (HVIS (LENGE (TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LENG (H3: AA3))))> 2, TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3 : AA3> LENN (H3: AA3))), ""), IF (LEN (TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3)))))> 2, TRANSPOSE ( FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LENN (H3: AA3))), "") ""), MATCH (SEKVENS (COUNTA ($ J $ 13: $ J $ 36) ,, 1,1) , SEKVENS (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, TRANSPOSE (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + KOLONNER ($ L $ 12: $ P $ 12) -KOLONNER (L $ 12: $ P $ 12))
M13: M36	M13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + KOLONNER ($ L $ 12: $ P $ 12) -KOLONNER (M $ 12: $ P $ 12))
N13: N36	N13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + KOLONNER ($ L $ 12: $ P $ 12) -KOLONNER (N $ 12: $ P $ 12))
O13: O36	O13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANSPOSE (MATCH (K13, $ H $ 3: $ AA $ 3,0) ) + KOLONNER ($ L $ 12: $ P $ 12) -KOLONNER (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, SEKVENS (COUNTA ($ B $ 4: $ B $ 9), 1,1), 0))
Dynamiske matrixformler.

Prashanth Sambaraju sendte en anden formelløsning, der bruger fem formler.

5 formler løsning

Ovenstående formler:

Celleformler
Rækkevidde	Formel
J15: J38	J15	= HVIS (MOD (RÆKER ($ J $ 15: J15), 6) = 0,6, MOD (RÆKER ($ J $ 15: J15), 6))
K15: K38	K15	= OFFSET ($ A $ 3, J15, J $ 15,1,1)
L15: L38	L15	= CONCATENATE ("Medarbejder", "", RUNDUP (RÆKER ($ J $ 15: J15) / 6,0))
M15: P38	M15	= OFFSET ($ A $ 3, $ J15, MATCH ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (KOLONNER ($ A: A), 5))
Q15: Q38	Q15	= SUM (M15: P15)

René Martin sendte denne formelløsning med tre unikke formler:

3 formler løsning

Formlerne anvendt i ovenstående:

Celleformler
Rækkevidde	Formel
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= HVIS (KOLONNE () = 9, OFFSET ($ A $ 2, MOD (RÆK (A1), 6) +1,0), HVIS (KOLONNE () = 10, "Medarbejder" & RUNDUP (RÆK (A1) / 6, 0), HVIS (KOLONNE () = 15, SUM (E13: H13), OFFSET ($ G $ 3, MOD (RÆDE (A6), 6) + 1, RUNDUP (Række (A1) / 6,0) * 5- 7 + KOLONNE (A1)))))
I14: N36	I14	= HVIS (KOLONNE () = 9, OFFSET ($ A $ 2, MOD (Række (A2), 6) +1,0), HVIS (KOLONNE () = 10, "Medarbejder" & RUNDUP (Række (A2) / 6, 0), OFFSET ($ G $ 3, MOD (Række (A7), 6) + 1, RUNDUP (Række (A2) / 6,0) * 5-7 + KOLONNE (A2))))

En alternativ løsning fra René Martin:

Celleformler
Rækkevidde	Formel
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= HVIS (KOLONNE () = 9, OFFSET ($ A $ 2, MOD (RÆK (A1), 6) +1,0), HVIS (KOLONNE () = 10, "Medarbejder" & RUNDUP (RÆK (A1) / 6, 0), HVIS (KOLONNE () = 15, SUM (E13: H13), OFFSET ($ G $ 3, MOD (RÆDE (A6), 6) + 1, RUNDUP (Række (A1) / 6,0) * 5- 7 + KOLONNE (A1)))))
I14: N36	I14	= HVIS (KOLONNE () = 9, OFFSET ($ A $ 2, MOD (Række (A2), 6) +1,0), HVIS (KOLONNE () = 10, "Medarbejder" & RUNDUP (Række (A2) / 6, 0), OFFSET ($ G $ 3, MOD (Række (A7), 6) + 1, RUNDUP (Række (A2) / 6,0) * 5-7 + KOLONNE (A2))))

Excel MVP Roger Govier sendte en formelløsning. For det første slettede Roger de unødvendige kolonner fra de originale data. Roger påpeger, at du kan lade dem være der, men så skal du justere kolonneindeksnumrene korrekt.

Roger brugte tre navngivne områder. Denne figur viser valgte rækker.