Excel в среднем и если массив - упорядочение ввода

Question

У меня есть набор данных, который объединяет несколько компаний и категорий. Мне нужно объединить разные компании вместе и усреднить по разным категориям. Я знаю, что решение - это формула среднего значения массива, но мне интересно, есть ли способ сделать вещи немного быстрее. Вот (очень упрощенный) пример того, с чем я работаю.

Допустим, мне нужно объединить компании A и B вместе для каждой классификации в отдельности. Я знаю, что формула выглядит так:

{= СРЗНАЧ (ЕСЛИ (($ $ 5:$ A $ 13 = { "А", "Б"})* ($ B $ 5:$ B $ 13 = "Н"), $ C $ 5:$ C $ 13))}

Но тогда мне нужно было бы выполнить это для каждой из классификаций (и в действительности может быть несколько комбинаций компании / классификации, чтобы измениться), поэтому может быть 5-10 формул для обновления. Затем мне нужно объединить разные компании, скажем, B, C и D. Я знаю, что это выглядит так:

{= СРЕДНИЙ (ЕСЛИ (($ A $ 5:$ A $ 13 = {"B", "C", "D"}) * * ($ B $ 5:$ B $ 13 = "H"), $ C $ 5:$ C $ 13))} {= СРЕДНИЙ (ЕСЛИ (($ A $ 5:$ A $ 13 = {"B", "C", "D"}) * ($ B $ 5:$ B $ 13 = "N"), $ C $ 5:$ C $ 13))} и т.д.

У меня вопрос, есть ли способ, как я могу поместить часть {"B", "C", "D"} в другую ячейку, которую я могу быстро изменить, а затем сослаться на эту ячейку, чтобы я не мог каждый раз менять все вручную?

Answer 1

Возможно, однако, вам может понадобиться использовать функцию Simple User Defined для разделения списка компаний, разделенных запятыми, в массив. В этом примере A1: список компаний A5. B1: B5 как "H" и C1: C5 усредняются на основе вашей формулы. F2 возвращает среднее значение & Список компаний упоминается в H1 (с разделителями-запятыми)

Нажмите клавиши ALT + F11, чтобы открыть редактор VBA и вставить модуль из меню вставки. Откройте модуль и вставьте в него следующий код.

Public Function MyArray(strTest As String)
   MyArray = Split(strTest, ",")
End Function

Теперь в F2 у вас есть формула, которая использует эту функцию MyArray.

=AVERAGE(IF((A1:A5=MyArray(H1))*(B1:B5="H"),C1:C5))

Нажмите CTRL + SHIFT + ВВОД, чтобы создать формулу массива, и вы получите желаемые результаты.

Answer 2

Вы можете поместить нужные поиски в непрерывном диапазоне и использовать:

=AVERAGE(AVERAGEIFS(C:C,B:B,"H",A:A,E1:INDEX(E:E,MATCH("zzz",E:E))))

И используйте Ctrl-Shift-Enter вместо Enter при выходе из режима редактирования. Если все сделано правильно, Excel поместит {} вокруг формулы.

Затем вы можете положить нужные поиски, начиная с E1 вниз.