Хорошо, ограничение в отношении нескольких продаж одного и того же типа в данный день, о котором говорил Скотт, сделало это возможным. Но это все еще довольно сложно. Использование VBA может быть намного проще, и, возможно, кто-то здесь опубликует процедуру.
Я собираюсь начать с использования таблицы «помощник», потому что полезно видеть промежуточные данные и дважды проверять, что все рассчитывается правильно, но я также покажу формулы, которые не используют таблицу помощника. Таблица помощников показана ниже вместе с результатами.
Я использовал случайные цены и даты, но соблюдали вышеупомянутое ограничение на множественные продажи данного типа за дату. Теперь давайте построим формулу, которая идет в F2.
Во-первых, нам нужен массив из семи самых последних цен для каждого типа. Как только это станет доступно, легко рассчитать три показателя в таблице результатов из этого массива.
Начнем с этого выражения: ($C$2:$C$55=ROW()-1) . Это часть формулы в F2, поэтому ROW()-1 равно 1, и это выражение дает массив значений True/False, причем True везде, где Type равен 1, и False везде. По мере заполнения ROW() увеличивается, поэтому в следующей строке он дает массив с True, где Type равен 2 и т.д.
Теперь мы умножим этот массив на столбец дат: ($A$2:$A$55)*($C$2:$C$55=ROW()-1) . Это дает массив, содержащий дату, где Type равен 1, и False везде.
Теперь нам нужны самые последние 7 из этих дат, и мы получаем те, которые используют функцию LARGE() . Из-за вышеуказанного ограничения (на каждую дату есть только одна продажа Типа 1), это дает даты 7 самых последних продаж Типа 1:
LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})
Ряд чисел в фигурных скобках указывает LARGE() возвращать значения с 1-го по 7-е по величине.
Теперь мы используем IF() чтобы получить цены, соответствующие этим датам:IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-1),$B$2:$B$55)
Первая часть логического теста IF() возвращает массив, где дата в столбце A является одной из 7 дат выше, и этот массив умножается на массив, где type = 1 снова, потому что другие типы были проданы на тех 7 свиданий. Таким образом, IF() проверяет, равна ли дата в столбце A одному из массива из 7 самых последних дат, и была ли конкретная продажа в эту дату для Type = 1. Если оба эти условия выполняются, IF() возвращает продажную цену из столбца B и False в противном случае.
Теперь, чтобы перечислить цены в вспомогательной таблице от высокой к низкой, мы используем LARGE():(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-1),$B$2:$B$55),COLUMN()-5)
Эта формула находится в столбце F, поэтому COLUMN()-5 равно 1, и формула возвращает первое по величине значение массива в этом столбце. По мере заполнения COLUMN() увеличивается, поэтому в следующем столбце он дает второе по величине значение и т.д.
Наконец, формула обернута в IFERROR() поэтому она будет возвращать пустое место, где имеется менее 7 продаж данного типа. Окончательная формула:
=IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-1),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-1),$B$2:$B$55),COLUMN()-5),"")
вводится в F2. Поскольку это формула массива, ее необходимо вводить с помощью клавиши CTRL Shift Enter, а не просто Enter. Если введено правильно, Excel окружит формулу фигурными скобками {} в строке формул. После ввода выберите F2 и заполните формулу вниз, а затем поперек, чтобы получить таблицу помощников выше.
Теперь легко заполнить таблицу результатов. Эти две формулы
=SMALL(F2:L2,3) and
=LARGE(F2:L2,2)
вычислите 3-е наименьшее и 2-е наибольшие значения в первой строке вспомогательной таблицы. И эта формула
=AVERAGE(IFERROR(LARGE(F2:L2,{2,3,4,5,6}),""))
вычисляет среднее из средних 5 значений. Это также формула массива, поэтому она должна быть введена, как указано выше. Заполнение этих формул приводит к таблице результатов.
Чтобы заполнить таблицу результатов без использования вспомогательной таблицы, используйте эти три формулы массива в F, G и H12 и заполните:
=SMALL(IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-11),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-11),$B$2:$B$55),{1,2,3,4,5,6,7}),""),3)
=LARGE(IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-11),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-11),$B$2:$B$55),{1,2,3,4,5,6,7}),""),2)
=AVERAGE(IFERROR(LARGE(IFERROR(LARGE(IF($A$2:$A$55=LARGE(($A$2:$A$55)*($C$2:$C$55=ROW()-11),{1,2,3,4,5,6,7})*($C$2:$C$55=ROW()-11),$B$2:$B$55),{1,2,3,4,5,6,7}),""),{2,3,4,5,6}),""))
Я надеюсь, что это поможет вам, и я уверен, что вы можете получить график, который вы хотите, как только у вас есть эти данные.
Любые комментарии или предложения приветствуются.
