Soluții de formulă - Sfaturi Excel

Notă

Acesta este unul dintr-o serie de articole care detaliază soluțiile trimise pentru provocarea Podcast 2316.

În timp ce mă așteptam în principal la soluții Power Query sau VBA la problemă, au existat câteva soluții de formulare interesante.

Hussein Korish a trimis o soluție cu 7 formule unice, inclusiv o formulă cu matrice dinamică.

7 formule unice

Formule celulare
Gamă	Formulă
K13: K36	K13	= INDEX (FILTER (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))), ""), IF (LEN (TRANSPOSE (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3)))))> 2, TRANSPOSE ( FILTRU ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))), "") ""), MATCH (SECVENȚĂ (COUNTA ($ J $ 13: $ J $ 36)) ,, 1,1) , SECVENȚĂ (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) ) + CULOANE ($ L $ 12: $ P $ 12) -COLUMNE (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) ) + CULOANE ($ L $ 12: $ P $ 12) -COLUMNE (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) ) + CULOANE ($ L $ 12: $ P $ 12) -COLUMNE (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) ) + CULOANE ($ L $ 12: $ P $ 12) -COLUMNE (O $ 12: $ P $ 12))
P13: P36	P13	= SUM (L13: O13)
J13: J36	J13	= INDEX ($ B $ 4: $ B $ 9, MATCH (MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, SECVENȚĂ (COUNTA ($ B $ 4: $ B 9 USD), 1,1), 0))
Formule matrice dinamice.

Prashanth Sambaraju a trimis o altă soluție de formulă care utilizează cinci formule.

5 formule soluție

Formulele utilizate mai sus:

Formule celulare
Gamă	Formulă
J15: J38	J15	= IF (MOD (ROWS ($ J $ 15: J15), 6) = 0,6, MOD (ROWS ($ J $ 15: J15), 6))
K15: K38	K15	= OFFSET ($ A $ 3, J15, J $ 15,1,1)
L15: L38	L15	= CONCATENATE („Angajat”, „„, ROUNDUP (ROWS ($ J $ 15: J15) / 6,0))
M15: P38	M15	= OFFSET ($ A $ 3, $ J15, MATCH ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (COLUMNE ($ A: A), 5))
Q15: Q38	Q15	= SUMĂ (M15: P15)

René Martin a trimis această soluție de formulă cu trei formule unice:

3 formule soluție

Formulele utilizate în cele de mai sus:

Formule celulare
Gamă	Formulă
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Angajat" & ROUNDUP (ROW (A1) / 6, 0), IF (COLUMN () = 15, SUM (E13: H13), OFFSET ($ G $ 3, MOD (ROW (A6), 6) + 1, ROUNDUP (ROW (A1) / 6,0) * 5- 7 + CULOANĂ (A1)))))
I14: N36	I14	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, "Angajat" & ROUNDUP (ROW (A2) / 6, 0), OFFSET ($ G $ 3, MOD (ROW (A7), 6) + 1, ROUNDUP (ROW (A2) / 6,0) * 5-7 + COLUMN (A2))))

O soluție alternativă de la René Martin:

Formule celulare
Gamă	Formulă
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Angajat" & ROUNDUP (ROW (A1) / 6, 0), IF (COLUMN () = 15, SUM (E13: H13), OFFSET ($ G $ 3, MOD (ROW (A6), 6) + 1, ROUNDUP (ROW (A1) / 6,0) * 5- 7 + CULOANĂ (A1)))))
I14: N36	I14	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, "Angajat" & ROUNDUP (ROW (A2) / 6, 0), OFFSET ($ G $ 3, MOD (ROW (A7), 6) + 1, ROUNDUP (ROW (A2) / 6,0) * 5-7 + COLUMN (A2))))

MVP Excel Roger Govier a trimis o soluție de formulă. În primul rând, Roger a șters coloanele inutile din datele originale. Roger subliniază că le-ai putea lăsa acolo, dar trebuie să ajustezi corespunzător numerele indexului coloanei.

Roger a folosit trei game numite. Această figură arată _rowsuri selectate.