Formula Solutions - Excel-vinkit

Sisällysluettelo

Huomautus

Tämä on yksi artikkelisarjasta, joka sisältää yksityiskohtaiset ratkaisut, jotka lähetettiin Podcast 2316 -haasteeseen.

Odotin enimmäkseen Power Query- tai VBA-ratkaisuja ongelmaan, mutta siellä oli joitain hienoja kaavan ratkaisuja.

Hussein Korish lähetti ratkaisun 7 ainutlaatuisella kaavalla, mukaan lukien dynaaminen matriisikaava.

7 ainutlaatuista kaavaa

Solukaavat
Alue	Kaava
K13: K36	K13	= HAKEMISTO (SUODATIN (JOS (LEN (SIIRRÄ (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))))))> 2, TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3 : AA3> LEN (H3: AA3))), ""), JOS (LEN (SIIRRÄ (SUODATIN ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3)))))> 2, TRANSPOSE ( SUODATIN ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))), "") ""), MATCH (SEQUENCE (COUNTA ($ J $ 13: $ J $ 36) ,, 1,1) , SEKVENSI (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)) ) + SARAKKEET ($ L $ 12: $ P $ 12)-COLOLNS (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)) ) + SARAKKEET ($ L $ 12: $ P $ 12)-COLUMNS (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)) ) + SARAKKEET ($ L $ 12: $ P $ 12)-COLOLNS (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)) ) + SARAKKEET ($ L $ 12: $ P $ 12)-COLOLNS (O $ 12: $ P $ 12))
P13: P36	P13	= SUMMA (L13: O13)
J13: J36	J13	= HAKEMISTO ($ B $ 4: $ B $ 9, MATCH (MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, SEQUENCE (COUNTA ($ B $ 4: $ B) 9 dollaria), 1,1), 0))
Dynaamiset matriisikaavat.

Prashanth Sambaraju lähetti toisen kaavan ratkaisun, joka käyttää viittä kaavaa.

5 kaavan ratkaisu

Edellä käytetyt kaavat:

Solukaavat
Alue	Kaava
J15: J38	J15	= JOS (MOD (RIVIT ($ J $ 15: J15), 6) = 0,6, MOD (RIVIT ($ J $ 15: J15), 6))
K15: K38	K15	= OFFSET ($ A $ 3, J15, J $ 15,1,1)
L15: L38	L15	= CONCATENATE ("Työntekijä", "", PYÖRISTÄ (RIVIT ($ J $ 15: J15) / 6,0))
M15: P38	M15	= OFFSET ($ A $ 3, $ J15, MATCH ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (SARAKKEET ($ A: A), 5))
Q15: Q38	Q15	= SUMMA (M15: P15)

René Martin lähetti tämän kaavan ratkaisun kolmella ainutlaatuisella kaavalla:

3 kaavan ratkaisu

Edellä käytetyt kaavat:

Solukaavat
Alue	Kaava
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= JOS (SARAKKE () = 9, OFFSET ($ A $ 2, MOD (RIVI (A1), 6) +1,0), JOS (SARAKE () = 10, "Työntekijä" & PYÖRISTÄ (RIVI (A1) / 6, 0), JOS (SARAKKE () = 15, SUMMA (E13: H13), OFFSET ($ G $ 3, MOD (RIVI (A6), 6) + 1, PYÖRISTÄ (RIVI (A1) / 6,0) * 5- 7 + SARAKE (A1)))))
I14: N36	I14	= JOS (SARAKKE () = 9, OFFSET ($ A $ 2, MOD (RIVI (A2), 6) +1,0), JOS (SARAKE () = 10, "Työntekijä" & PYÖRISTÄ (RIVI (A2) / 6, 0), OFFSET ($ G $ 3, MOD (RIVI (A7), 6) + 1, PYÖRISTÄ (RIVI (A2) / 6,0) * 5-7 + SARAKE (A2))))

René Martinin vaihtoehtoinen ratkaisu:

Solukaavat
Alue	Kaava
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= JOS (SARAKKE () = 9, OFFSET ($ A $ 2, MOD (RIVI (A1), 6) +1,0), JOS (SARAKE () = 10, "Työntekijä" & PYÖRISTÄ (RIVI (A1) / 6, 0), JOS (SARAKKE () = 15, SUMMA (E13: H13), OFFSET ($ G $ 3, MOD (RIVI (A6), 6) + 1, PYÖRISTÄ (RIVI (A1) / 6,0) * 5- 7 + SARAKE (A1)))))
I14: N36	I14	= JOS (SARAKKE () = 9, OFFSET ($ A $ 2, MOD (RIVI (A2), 6) +1,0), JOS (SARAKE () = 10, "Työntekijä" & PYÖRISTÄ (RIVI (A2) / 6, 0), OFFSET ($ G $ 3, MOD (RIVI (A7), 6) + 1, PYÖRISTÄ (RIVI (A2) / 6,0) * 5-7 + SARAKE (A2))))

Excel MVP Roger Govier lähetti kaavan ratkaisun. Ensinnäkin Roger poisti tarpeettomat sarakkeet alkuperäisistä tiedoista. Roger huomauttaa, että voit jättää heidät sinne, mutta sinun on sitten mukautettava sarakeindeksinumerot asianmukaisesti.

Roger käytti kolmea nimettyä aluetta. Tässä kuvassa näkyvät _rivit valittu.