Formellösungen - Excel-Tipps

Hinweis

Dies ist einer aus einer Reihe von Artikeln, in denen Lösungen für die Podcast 2316-Herausforderung aufgeführt sind.

Während ich hauptsächlich Power Query- oder VBA-Lösungen für das Problem erwartet hatte, gab es einige coole Formellösungen.

Hussein Korish schickte eine Lösung mit 7 einzigartigen Formeln, einschließlich einer dynamischen Array-Formel.

7 einzigartige Formeln

Zellformeln
Angebot	Formel
K13: K36	K13	= INDEX (FILTER (WENN (LEN (TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))))> 2, TRANSPOSE (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 (SEQUENCE (COUNTA ($ J $ 13: $ J $ 36) ,, 1,1) , SEQUENCE (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) ) + SPALTEN ($ L $ 12: $ P $ 12) - SPALTEN (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) ) + SPALTEN ($ 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) ) + SPALTEN ($ L $ 12: $ P $ 12) -COLUMNS (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) ) + SPALTEN ($ L $ 12: $ P $ 12) -COLUMNS (O $ 12: $ P $ 12))
P13: P36	P13	= SUMME (L13: O13)
J13: J36	J13	= INDEX ($ B $ 4: $ B $ 9, MATCH (MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, SEQUENCE (COUNTA ($ B $ 4: $ B. $ 9), 1,1), 0))
Dynamische Array-Formeln.

Prashanth Sambaraju schickte eine andere Formellösung, die fünf Formeln verwendet.

5 Formeln Lösung

Die oben verwendeten Formeln:

Zellformeln
Angebot	Formel
J15: J38	J15	= IF (MOD (REIHEN ($ J $ 15: J15), 6) = 0,6, MOD (REIHEN ($ J $ 15: J15), 6))
K15: K38	K15	= OFFSET ($ A $ 3, J15, J $ 15,1,1)
L15: L38	L15	= CONCATENATE ("Mitarbeiter", "", ROUNDUP (ROWS ($ J $ 15: J15) / 6,0))
M15: P38	M15	= OFFSET ($ A $ 3, $ J15, MATCH ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (SPALTEN ($ A: A), 5))
Q15: Q38	Q15	= SUMME (M15: P15)

René Martin schickte diese Formellösung mit drei einzigartigen Formeln:

3 Formeln Lösung

Die oben verwendeten Formeln:

Zellformeln
Angebot	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, "Employee" & ROUNDUP (ROW (A1) / 6) 0), IF (COLUMN () = 15, SUMME (E13: H13), OFFSET ($ G $ 3, MOD (REIHE (A6), 6) + 1, ROUNDUP (REIHE (A1) / 6,0) * 5- 7 + SPALTE (A1)))))
I14: N36	I14	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, "Employee" & ROUNDUP (ROW (A2) / 6) 0), OFFSET ($ G $ 3, MOD (REIHE (A7), 6) + 1, RUNDUNG (REIHE (A2) / 6,0) * 5-7 + SPALTE (A2)))

Eine alternative Lösung von René Martin:

Zellformeln
Angebot	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, "Employee" & ROUNDUP (ROW (A1) / 6) 0), IF (COLUMN () = 15, SUMME (E13: H13), OFFSET ($ G $ 3, MOD (REIHE (A6), 6) + 1, ROUNDUP (REIHE (A1) / 6,0) * 5- 7 + SPALTE (A1)))))
I14: N36	I14	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, "Employee" & ROUNDUP (ROW (A2) / 6) 0), OFFSET ($ G $ 3, MOD (REIHE (A7), 6) + 1, RUNDUNG (REIHE (A2) / 6,0) * 5-7 + SPALTE (A2)))

Excel MVP Roger Govier schickte eine Formellösung. Zunächst löschte Roger die unnötigen Spalten aus den Originaldaten. Roger weist darauf hin, dass Sie sie dort lassen könnten, aber dann müssen Sie die Spaltenindexnummern entsprechend anpassen.

Roger verwendete drei benannte Bereiche. Diese Abbildung zeigt ausgewählte Zeilen.