Рішення для формул - Поради Excel

Примітка

Це одна із серії статей, що детально описують рішення, спрямовані на виклик Podcast 2316.

Незважаючи на те, що я очікував здебільшого вирішення проблеми за допомогою Power Query або VBA, було кілька крутих рішень для формул.

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

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

Формули клітин
Діапазон	Формула
K13: K36	K13	= ІНДЕКС (ФІЛЬТР (IF (LEN (TRANSOSE (ФІЛЬТР ($ H $ 3: $ AA $ 3, H3: AA3> LEN (H3: AA3))))>> 2, ТРАНСПОЗ (ФІЛЬТР ($ 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))), "") ""), ЗБІГ (ПОСЛІДОВНІСТЬ (КОНТА ($ J $ 13: $ J $ 36) ,, 1,1) , ПОСЛІДОВНІСТЬ (КОНТА ($ J $ 13: $ J $ 36) / КОНТА ($ 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, ТРАНСПОРТУВАННЯ (МАТЧ (K13, $ H $ 3: $ AA $ 3,0) ) + КОЛОНКИ ($ L $ 12: $ P $ 12) -КОЛОНКИ (L $ 12: $ P $ 12))
М13: М36	М13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, ТРАНСПОРТУВАННЯ (МАТЧ (K13, $ H $ 3: $ AA $ 3,0) ) + КОЛОНКИ ($ L $ 12: $ P $ 12) -КОЛОНКИ (M $ 12: $ P $ 12))
N13: N36	N13	= OFFSET ($ H $ 3, MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, ТРАНСПОРТУВАННЯ (МАТЧ (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, ТРАНСПОРТУВАННЯ (МАТЧ (K13, $ H $ 3: $ AA $ 3,0) ) + КОЛОНКИ ($ L $ 12: $ P $ 12) -КОЛОНКИ (O $ 12: $ P $ 12))
P13: P36	P13	= SUM (L13: O13)
J13: J36	J13	= ІНДЕКС ($ B $ 4: $ B $ 9, МАТЧ (MOD (COUNTA ($ J $ 12: J12) -1, COUNTA ($ B $ 4: $ B $ 9)) + 1, ПОСЛІДОВНІСТЬ (COUNTA ($ B $ 4: $ B) $ 9), 1,1), 0))
Формули динамічного масиву.

Прашант Самбараджу надіслав інше рішення для формул, яке використовує п’ять формул.

5 розчинів формул

Формули, використані вище:

Формули клітин
Діапазон	Формула
J15: J38	J15	= ПІСЛЯ (MOD (РЯДКИ ($ J $ 15: J15), 6) = 0,6, MOD (РЯДКИ ($ J $ 15: J15), 6))
K15: K38	K15	= OFFSET ($ A $ 3, J15, J $ 15,1,1)
L15: L38	L15	= CONCATENATE ("Співробітник", "", ОБ'ЄМ (РЯДКИ ($ J $ 15: J15) / 6,0))
M15: P38	М15	= OFFSET ($ A $ 3, $ J15, MATCH ($ L15, $ B $ 3: $ AA $ 3,0) + MOD (КОЛОНКИ ($ A: A), 5))
Q15: Q38	Q15	= SUM (M15: P15)

Рене Мартін надіслав у це рішення формули три унікальні формули:

3 рішення формул

Формули, використані в наведеному вище:

Формули клітин
Діапазон	Формула
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Співробітник" & ROUNDUP (ROW (A1) / 6, 0), ЯКЩО (Стовпець () = 15, СУММА (E13: H13), ЗМІСТ ($ G $ 3, MOD (РЯД (A6), 6) + 1, ОБ'ЄМ (РЯД (A1) / 6,0) * 5- 7 + Стовпчик (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 (ROW (A7), 6) + 1, ROUNDUP (ROW (A2) / 6,0) * 5-7 + COLUMN (A2))))

Альтернативне рішення від Рене Мартіна:

Формули клітин
Діапазон	Формула
I12: N12	I12	= A3
I13: O13, O14: O36	I13	= IF (COLUMN () = 9, OFFSET ($ A $ 2, MOD (ROW (A1), 6) +1,0), IF (COLUMN () = 10, "Співробітник" & ROUNDUP (ROW (A1) / 6, 0), ЯКЩО (Стовпець () = 15, СУММА (E13: H13), ЗМІСТ ($ G $ 3, MOD (РЯД (A6), 6) + 1, ОБ'ЄМ (РЯД (A1) / 6,0) * 5- 7 + Стовпчик (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 (ROW (A7), 6) + 1, ROUNDUP (ROW (A2) / 6,0) * 5-7 + COLUMN (A2))))

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

Роджер використав три названі діапазони. На цьому малюнку показано вибрані рядки.