Формульные решения - Советы по Excel

Заметка

Это одна из серии статей, в которых подробно описаны решения, присланные для задачи Podcast 2316.

Хотя я ожидал в основном решения проблемы с помощью Power Query или VBA, было несколько классных решений с формулами.

Хусейн Кориш прислал решение с 7 уникальными формулами, включая формулу динамического массива.

7 уникальных формул

Формулы ячеек
Ассортимент	Формула
K13: K36	K13	= ИНДЕКС (ФИЛЬТР (ЕСЛИ (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 ( ФИЛЬТР ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))), "") ""), MATCH (SEQUENCE (COUNTA ($ J $ 13: $ J $ 36) ,, 1,1) , ПОСЛЕДОВАТЕЛЬНОСТЬ (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)) ) + КОЛОННЫ ($ L $ 12: $ P $ 12) -КОЛОННЫ (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)) ) + КОЛОННЫ ($ 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)) ) + КОЛОННЫ ($ L $ 12: $ P $ 12) -КОЛОННЫ (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)) ) + КОЛОННЫ ($ L $ 12: $ P $ 12) -КОЛОННЫ (O $ 12: $ P $ 12))
P13: P36	P13	= СУММ (L13: O13)
J13: J36	J13	= ИНДЕКС ($ B $ 4: $ B $ 9, ПОИСКПОЗ (МОД (СЧЁТ ($ J $ 12: J12) -1, СЧЁТ ($ B $ 4: $ B $ 9)) + 1, ПОСЛЕДОВАТЕЛЬНОСТЬ (СЧЁТ ($ B $ 4: $ B $ 9), 1,1), 0))
Формулы динамического массива.

Прашант Самбараджу прислал другой раствор формулы, в котором используются пять формул.

5 формул решения

Формулы, использованные выше:

Формулы ячеек
Ассортимент	Формула
J15: J38	J15	= ЕСЛИ (MOD (ROWS ($ J $ 15: J15); 6) = 0,6, MOD (ROWS ($ J $ 15: J15), 6))
K15: K38	K15	= СМЕЩЕНИЕ (A $ 3, J15, J $ 15,1,1)
L15: L38	L15	= СЦЕПИТЬ ("Сотрудник"; ""; ОКРУГЛ ВВЕРХ (СТРОКИ ($ J $ 15: J15) / 6,0))
M15: P38	M15	= СМЕЩЕНИЕ ($ A $ 3, $ J15; ПОИСКПОЗ ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (КОЛОННЫ ($ A: A), 5))
Q15: Q38	Q15	= СУММ (M15: P15)

Рене Мартин отправил решение этой формулы с тремя уникальными формулами:

3 формулы решения

Формулы, использованные выше:

Формулы ячеек
Ассортимент	Формула
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= ЕСЛИ (COLUMN () = 9, OFFSET ($ A $ 2; MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, «Сотрудник» & ROUNDUP (ROW (A1) / 6, 0), ЕСЛИ (COLUMN () = 15, SUM (E13: H13), OFFSET ($ G $ 3, MOD (ROW (A6), 6) + 1, ROUNDUP (ROW (A1) / 6,0) * 5- 7 + КОЛОНКА (A1)))))
I14: N36	I14	= ЕСЛИ (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, «Сотрудник» & ROUNDUP (ROW (A2) / 6, 0), OFFSET ($ G $ 3, MOD (ROW (A7), 6) + 1, ROUNDUP (ROW (A2) / 6,0) * 5-7 + COLUMN (A2))))

Альтернативное решение от Рене Мартина:

Формулы ячеек
Ассортимент	Формула
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= ЕСЛИ (COLUMN () = 9, OFFSET ($ A $ 2; MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, «Сотрудник» & ROUNDUP (ROW (A1) / 6, 0), ЕСЛИ (COLUMN () = 15, SUM (E13: H13), OFFSET ($ G $ 3, MOD (ROW (A6), 6) + 1, ROUNDUP (ROW (A1) / 6,0) * 5- 7 + КОЛОНКА (A1)))))
I14: N36	I14	= ЕСЛИ (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A2), 6) +1,0), IF (COLUMN () = 10, «Сотрудник» & ROUNDUP (ROW (A2) / 6, 0), OFFSET ($ G $ 3, MOD (ROW (A7), 6) + 1, ROUNDUP (ROW (A2) / 6,0) * 5-7 + COLUMN (A2))))

MVP по Excel Роджер Говье прислал решение по формуле. Во-первых, Роджер удалил ненужные столбцы из исходных данных. Роджер указывает, что вы можете оставить их там, но тогда вам нужно будет соответствующим образом отрегулировать порядковые номера столбцов.

Роджер использовал три именованных диапазона. На этом рисунке показаны выбранные строки.