Formellösningar - Excel-tips

Notera

Detta är en av en serie artiklar som beskriver lösningar som skickats in för Podcast 2316-utmaningen.

Medan jag förväntade mig mest Power Query- eller VBA-lösningar på problemet fanns det några häftiga formellösningar.

Hussein Korish skickade in en lösning med 7 unika formler, inklusive en dynamisk matrisformel.

7 unika formler

Cellformler
Räckvidd	Formel
K13: K36	K13	= INDEX (FILTER (OM (LEN (TRANSPOSERA (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3)))))> 2, TRANSPOS (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 (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) -KOLONN (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) -KOLONN (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) ) + KOLONN ($ L $ 12: $ P $ 12) -KOLONN (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) ) + KOLONN ($ L $ 12: $ P $ 12) -KOLONN (O $ 12: $ P $ 12))
P13: P36	P13	= SUMMA (L13: O13)
J13: J36	J13	= INDEX ($ 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))
Formler för dynamisk matris.

Prashanth Sambaraju skickade in en annan formellösning som använder fem formler.

5 formler lösning

Formlerna som används ovan:

Cellformler
Räckvidd	Formel
J15: J38	J15	= OM (MOD (Rader ($ J $ 15: J15), 6) = 0,6, MOD (Rader ($ J $ 15: J15), 6))
K15: K38	K15	= OFFSET ($ A $ 3, J15, J $ 15,1,1)
L15: L38	L15	= CONCATENATE ("Anställd", "", RUNDUP (RADER ($ J $ 15: J15) / 6,0))
M15: P38	M15	= OFFSET ($ A $ 3, $ J15, MATCH ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (KOLONN ($ A: A), 5))
F15: F38	Q15	= SUMMA (M15: P15)

René Martin skickade in denna formellösning med tre unika formler:

3 formler lösning

Formlerna som används i ovanstående:

Cellformler
Räckvidd	Formel
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Anställd" & ROUNDUP (ROW (A1) / 6, 0), OM (COLUMN () = 15, SUM (E13: H13), OFFSET ($ G $ 3, MOD (ROW (A6), 6) + 1, ROUNDUP (ROW (A1) / 6,0) * 5- 7 + KOLONN (A1)))))
I14: N36	I14	= OM (KOLONN () = 9, OFFSET ($ A $ 2, MOD (RAD (A2), 6) +1,0), OM (KOLONN () = 10, "Anställd" & RUNDUP (RAD (A2) / 6, 0), OFFSET ($ G $ 3, MOD (RAD (A7), 6) + 1, RUNDUP (RAD (A2) / 6,0) * 5-7 + KOLONN (A2))))

En alternativ lösning från René Martin:

Cellformler
Räckvidd	Formel
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Anställd" & ROUNDUP (ROW (A1) / 6, 0), OM (COLUMN () = 15, SUM (E13: H13), OFFSET ($ G $ 3, MOD (ROW (A6), 6) + 1, ROUNDUP (ROW (A1) / 6,0) * 5- 7 + KOLONN (A1)))))
I14: N36	I14	= OM (KOLONN () = 9, OFFSET ($ A $ 2, MOD (RAD (A2), 6) +1,0), OM (KOLONN () = 10, "Anställd" & RUNDUP (RAD (A2) / 6, 0), OFFSET ($ G $ 3, MOD (RAD (A7), 6) + 1, RUNDUP (RAD (A2) / 6,0) * 5-7 + KOLONN (A2))))

Excel MVP Roger Govier skickade in en formellösning. För det första raderade Roger de onödiga kolumnerna från originaldata. Roger påpekar att du kan lämna dem där, men då måste du justera kolumnindexnumren på rätt sätt.

Roger använde tre namngivna områden. Denna figur visar valda rader.