Формулы Excel с учетом определенного значения ячеек

Question

Мои данные выглядят так:

У меня 500 компаний, i - идентификатор для каждой компании, t - период времени (1-5000) для всех компаний, r - доход, а mktcap - рыночная капитализация.

Данные идут от t = 1-5000 для одной компании, затем от t = 1-5000 для следующей компании и т.д.

Как бы я сделал эти вещи:

• Суммируйте все возвраты (или сделайте некоторую другую простую арифметику) за любой определенный период времени t .

• Найдите квантиль 5% для возвратов в каждом периоде времени (т.е. доход, при котором 95% возвратов выше для этого периода времени t)

• создайте другую строку, которая = 1, если возвращение в этой строке ниже этого
  квантиль, и 0 в противном случае (поэтому = 0, если доход в этой строке находится в верхних 95% возвратов за период времени t)

Answer 1

У меня есть решение, которое требует использования 5000 ячеек, по одной на каждый возможный период времени:

Скопируйте эту формулу 5000 раз в один столбец (назовем его столбцом M)

=SUMIF(B$2:B, ROW(), C$2:C)

Эта формула вычисляет сумму всех элементов в столбце C (начиная со второго элемента), которые имеют значение в столбце B, равное строке, в которую помещается эта формула.

Другими словами, если вы скопируете эту формулу вдоль 5000 строк, для каждого возможного значения t будет одна ячейка. Это решает ваше первое требование.

Мы можем рассчитать процентиль для данного т:

=PERCENTILE(FILTER($C$2:$C, EQ($B$2:$B, T)), 0.05)

Теперь для вашей третьей задачи (1 если хуже 5-го процентиля, 0 если лучше), вы можете использовать эту формулу, начинающуюся со строки 2, и перетащите автозаполнение вниз:

=IF($C2 < PERCENTILE(FILTER($C$2:$C, EQ($B$2:$B, $B2)), 0.05), 1, 0)

Answer 2

Не зная вашей точной цели, но чтобы вы начали,

• =sumif(a:a, "1-5000", b:b)
• =percentile(a:a, .05)
• =if(a2< x1,1,0), where x1 is your percentile cell.

Тогда, чтобы сделать это умнее, я бы посоветовал изучить функции массива.