Rješenja za formule - Excel savjeti

Sadržaj

Bilješka

Ovo je jedan od niza članaka koji detaljno opisuju rješenja poslana za izazov Podcast 2316.

Iako sam očekivao uglavnom Power Query ili VBA rješenja problema, bilo je nekih cool formula rješenja.

Hussein Korish poslao je rješenje sa 7 jedinstvenih formula, uključujući dinamičku formulu niza.

7 jedinstvenih formula

Formule stanica
Domet	Formula
K13: K36	K13	= INDEKS (FILTER (AKO (LEN (TRANSOSE (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3)))))> 2, PROZIR (FILTER ($ H $ 3: $ AA $ 3, H3 : AA3> LEN (H3: AA3))), ""), AKO (LEN (PROZIR (FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3)))))> 2, PROZIRI ( FILTER ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))), "") ""), UTAKMICA (SEKVENCA (KONTA ($ J $ 13: $ J $ 36) ,, 1,1) , SEKVENCA (TIJELA (USD $ 13: $ J $ 36) / KONTA ($ B $ 4: $ B $ 9) ,, 1, BROJ ($ 4 $: $ B $ 9)), 1))
L13: L36	L13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANZOZIRAJ (UTAKMICA (K13, $ H $ 3: $ AA $ 3,0) ) + KOLONE (12 L $: 12 P $ 12) -KOLUMNE (L 12 USD: 12 P $)))
M13: M36	M13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANZOZIRAJ (UTAKMICA (K13, $ H $ 3: $ AA $ 3,0) ) + KOLONE (12 USD: 12 USD: 12 USD) -STUPCI (12 USD: 12 USD)
N13: N36	N13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANZOZIRAJ (UTAKMICA (K13, $ H $ 3: $ AA $ 3,0) ) + KOLONE (12 L $: 12 P $ 12) -KOLONE (N $ 12: 12 P $)))
O13: O36	O13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, TRANZOZIRAJ (UTAKMICA (K13, $ H $ 3: $ AA $ 3,0) ) + KOLONE (12 L $: 12 P $ 12) -KOLUMNE (O $ 12: 12 P $)))
P13: P36	P13	= SUM (L13: O13)
J13: J36	J13	= INDEKS ($ B $ 4: $ B $ 9, UTAKMICA (MOD (COUNTA ($ J $ 12: J12) -1, BROJICA ($ B $ 4: $ B $ 9)) + 1, SEKVENCA (BROJA ($ B $ 4: $ B 9 USD, 1,1), 0))
Dinamičke formule niza.

Prashanth Sambaraju poslao je još jedno rješenje za formule koje koristi pet formula.

5 rješenja otopina

Gore korištene formule:

Formule stanica
Domet	Formula
J15: J38	J15	= AKO (MOD (REDOVI (REDOVI ($ J $ 15: J15), 6) = 0,6, MOD (REDOVI ($ J $ 15: J15), 6))
K15: K38	K15	= POKRET ($ A $ 3, J15, J $ 15,1,1)
L15: L38	L15	= CONCATENATE ("Zaposlenik", "", OKRUGLO (REDOVI (15 J $ 15: J15) / 6,0))
M15: P38	M15	= OFFSET ($ A $ 3, $ J15, UTAKMICA ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (KOLONE ($ A: A), 5))
P15: P38	P15	= SUM (M15: P15)

René Martin poslao je ovo rješenje za formule s tri jedinstvene formule:

3 formule rješenje

Formule korištene u gore navedenom:

Formule stanica
Domet	Formula
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), AKO (KOLONA () = 15, ZBIR (E13: H13), POKRET ($ G $ 3, MOD (RED (A6), 6) + 1, OKRUGLI (RED (A1) / 6,0) * 5- 7 + KOLONA (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 (RED (A7), 6) + 1, OKRUGLO (RED (A2) / 6,0) * 5-7 + KOLONA (A2))))

Alternativno rješenje tvrtke René Martin:

Formule stanica
Domet	Formula
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), AKO (KOLONA () = 15, ZBIR (E13: H13), POKRET ($ G $ 3, MOD (RED (A6), 6) + 1, OKRUGLI (RED (A1) / 6,0) * 5- 7 + KOLONA (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 (RED (A7), 6) + 1, OKRUGLO (RED (A2) / 6,0) * 5-7 + KOLONA (A2))))

Excel MVP Roger Govier poslao je rješenje formule. Prvo je Roger izbrisao nepotrebne stupce iz izvornih podataka. Roger ističe da biste ih mogli tamo ostaviti, ali tada morate na odgovarajući način prilagoditi brojeve indeksa stupaca.

Roger je koristio tri imenovana raspona. Ova slika prikazuje odabrane _brojeve.