Rozwiązania formuł - porady dotyczące programu Excel

Spisie treści

Uwaga

Jest to jeden z serii artykułów opisujących szczegółowo rozwiązania przesłane w ramach wyzwania Podcast 2316.

Chociaż spodziewałem się rozwiązania problemu głównie z Power Query lub VBA, było kilka fajnych rozwiązań formuł.

Hussein Korish przesłał rozwiązanie z 7 unikalnymi formułami, w tym dynamiczną formułą tablicową.

7 unikalnych receptur

Formuły komórkowe
Zasięg	Formuła
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 ( FILTR ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))), "") ""), MATCH (SEKWENCJA (COUNTA ($ J $ 13: $ J $ 36) ,, 1,1) , SEQUENCE (COUNTA (13 $ J $: 36 $ J $) / COUNTA (4 $ B $: 9 $ B $) ,, 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) ) + KOLUMNY (12 L $: 12 $ P) -KOLUMNY (12 L $: 12 $ P $))
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) ) + KOLUMNY (12 USD: 12 USD) -KOLUMNY (12 USD: 12 USD))
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) ) + KOLUMNY (12 USD: 12 USD) -KOLUMNY (12 USD: 12 USD))
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) ) + KOLUMNY (12 USD: 12 USD) -KOLUMNY (12 USD: 12 USD))
P13: P36	P13	= SUMA (L13: O13)
J13: J36	J13	= INDEX ($ B $ 4: $ B 9 $, MATCH (MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, SEKWENCJA (COUNTA ($ B $ 4: $ B 9 USD), 1,1), 0))
Dynamiczne formuły tablicowe.

Prashanth Sambaraju przesłał kolejną formułę, która wykorzystuje pięć formuł.

Rozwiązanie 5 formuł

Wzory użyte powyżej:

Formuły komórkowe
Zasięg	Formuła
J15: J38	J15	= JEŻELI (MOD (ROWS ($ J 15 $: J15), 6) = 0,6, MOD (ROWS ($ J $ 15: J15), 6))
K15: K38	K15	= PRZESUNIĘCIE ($ A $ 3, J15, J $ 15,1,1)
L15: L38	L15	= CONCATENATE ("Pracownik"; ""; ZAOKR.GÓRA (ROWS (15 $ J $: J15) / 6,0))
M15: P38	M15	= OFFSET ($ A $ 3, $ J15, MATCH (L15 $, 3 $ B: $ AA 3,0 $) + MOD (KOLUMNY ($ A: A), 5))
P15: P38	P15	= SUMA (M15: P15)

René Martin przesłał to rozwiązanie z trzema unikalnymi formułami:

Rozwiązanie 3 formuł

Wzory użyte powyżej:

Formuły komórkowe
Zasięg	Formuła
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= JEŻELI (KOLUMNA () = 9, PRZESUNIĘCIE ($ A $ 2, MOD (WIERSZ (A1), 6) +1,0), JEŻELI (KOLUMNA () = 10, "Pracownik" & ZAOKR.GÓRA (WIERSZ (A1) / 6, 0), JEŻELI (KOLUMNA () = 15, SUMA (E13: H13), PRZESUNIĘCIE ($ G $ 3, MOD (ROW (A6), 6) + 1, ZAOKR.GÓRA (ROW (A1) / 6,0) * 5- 7 + KOLUMNA (A1)))))
I14: N36	I14	= JEŻELI (KOLUMNA () = 9, PRZESUNIĘCIE ($ A $ 2, MOD (WIERSZ (A2), 6) +1,0), JEŻELI (KOLUMNA () = 10, "Pracownik" & ZAOKR.GÓRA (WIERSZ (A2) / 6, 0), PRZESUNIĘCIE ($ G $ 3, MOD (WIERSZ (A7), 6) + 1, ZAOKR.GÓRA (RZĘD (A2) / 6,0) * 5-7 + KOLUMNA (A2))))

Alternatywne rozwiązanie od René Martin:

Formuły komórkowe
Zasięg	Formuła
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= JEŻELI (KOLUMNA () = 9, PRZESUNIĘCIE ($ A $ 2, MOD (WIERSZ (A1), 6) +1,0), JEŻELI (KOLUMNA () = 10, "Pracownik" & ZAOKR.GÓRA (WIERSZ (A1) / 6, 0), JEŻELI (KOLUMNA () = 15, SUMA (E13: H13), PRZESUNIĘCIE ($ G $ 3, MOD (ROW (A6), 6) + 1, ZAOKR.GÓRA (ROW (A1) / 6,0) * 5- 7 + KOLUMNA (A1)))))
I14: N36	I14	= JEŻELI (KOLUMNA () = 9, PRZESUNIĘCIE ($ A $ 2, MOD (WIERSZ (A2), 6) +1,0), JEŻELI (KOLUMNA () = 10, "Pracownik" & ZAOKR.GÓRA (WIERSZ (A2) / 6, 0), PRZESUNIĘCIE ($ G $ 3, MOD (WIERSZ (A7), 6) + 1, ZAOKR.GÓRA (RZĘD (A2) / 6,0) * 5-7 + KOLUMNA (A2))))

Excel MVP Roger Govier przesłał rozwiązanie formuły. Po pierwsze, Roger usunął niepotrzebne kolumny z oryginalnych danych. Roger zwraca uwagę, że możesz je tam zostawić, ale wtedy musisz odpowiednio dostosować numery indeksów kolumn.

Roger użył trzech nazwanych zakresów. Ten rysunek przedstawia wybrane wiersze.