Formelløsninger - Excel-tips

Merk

Dette er en av en serie artikler som beskriver løsninger som er sendt inn for Podcast 2316-utfordringen.

Mens jeg forventet mest Power Query eller VBA-løsninger på problemet, var det noen kule formelløsninger.

Hussein Korish sendte inn en løsning med 7 unike formler, inkludert en dynamisk matriseformel.

7 unike formler

Celleformler
Område	Formel
K13: K36	K13	= INDEKS (FILTER (HVIS (LENN (TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3)))))> 2, TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3 : AA3> LENN (H3: AA3))), ""), IF (LENN (TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LENN (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) -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) -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) -KOLONN (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 matriseformler.

Prashanth Sambaraju sendte inn en annen formelløsning som bruker fem formler.

5 formler løsning

Formlene brukt ovenfor:

Celleformler
Område	Formel
J15: J38	J15	= HVIS (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 ("Ansatt", "", RUNDUP (RADER ($ 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 inn denne formelløsningen med tre unike formler:

3 formler løsning

Formlene brukt ovenfor:

Celleformler
Område	Formel
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= HVIS (KOLONN () = 9, OFFSET ($ A $ 2, MOD (RAD (A1), 6) +1,0), HVIS (KOLONN () = 10, "Ansatt" & RUNDUP (RAD (A1) / 6, 0), IF (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	= HVIS (KOLONN () = 9, OFFSET ($ A $ 2, MOD (RAD (A2), 6) +1,0), HVIS (KOLONN () = 10, "Ansatt" & 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 fra René Martin:

Celleformler
Område	Formel
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= HVIS (KOLONN () = 9, OFFSET ($ A $ 2, MOD (RAD (A1), 6) +1,0), HVIS (KOLONN () = 10, "Ansatt" & RUNDUP (RAD (A1) / 6, 0), IF (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	= HVIS (KOLONN () = 9, OFFSET ($ A $ 2, MOD (RAD (A2), 6) +1,0), HVIS (KOLONN () = 10, "Ansatt" & 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 sendte inn en formelløsning. For det første slettet Roger de unødvendige kolonnene fra de opprinnelige dataene. Roger påpeker at du kan la dem være der, men da må du justere kolonneindeksnumrene riktig.

Roger brukte tre navngitte områder. Denne figuren viser _rader valgt.