У меня есть данные, которые мне крайне необходимо проанализировать, и я не знаю, как поступить. Данные находятся в трех столбцах; первый — дата. Даты не упорядочены (по разным причинам), и новые данные просто добавляются в конец списка. У меня также есть цена продажи продукта во втором столбце и тип продукта в третьем. Например:
Расчеты должны быть основаны на семи последних ценах любого типа. Третья самая низкая цена и вторая самая высокая цена попадают в таблицу, начиная с ячеек «A» ( F12) и «B» ( G12), соответственно.
Третья формула должна исключить (проигнорировать) самую высокую и самую низкую из семи последних цен и вычислить среднее значение оставшихся пяти цен. Это идет в последний столбец таблицы, начиная с ячейки «C» ( H12).
Затем мне нужна комбинированная столбчатая/линейная диаграмма с «типом» на горизонтальной оси и «ценой» на вертикальной. Мне нужна составная столбчатая диаграмма, показывающая нижний диапазон (ячейка «A») и верхний диапазон (ячейка «B»), а линейный график должен быть наложен на средние значения (ячейка «C»).
Я не волшебник Excel, и я перепробовал все возможные руководства по самопомощи, чтобы разобраться с этим. Я дошел до того, что подумал, что это VLOOKUPможет быть полезно, но теперь я в растерянности.
решение1
Хорошо, ограничение на множественные продажи одного и того же типа в определенный день, о котором говорил Скотт, сделало это возможным. Но это все еще довольно сложно. Использование 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 дат выше, и этот массив снова умножается на массив, где тип = 1, потому что другие типы были проданы в эти 7 дат. Таким образом, проверяется, IF()равна ли дата в столбце A одной из массива из 7 последних датибыла ли конкретная продажа в эту дату для Типа = 1. Если оба эти условия истинны, возвращается IF()цена продажи из Столбца B, и Ложь в противном случае.
Теперь, чтобы перечислить цены во вспомогательной таблице от больших к меньшим, мы используем 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, и формула возвращает 1-е наибольшее значение массива в этом столбце. По мере заполнения COLUMN()увеличивается, поэтому в следующем столбце она дает 2-е наибольшее значение и т. д.
Наконец, формула обернута 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. Поскольку это формула массива, ее нужно вводить с CTRLShiftEnter, а не просто 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}),""))
Надеюсь, это вам поможет, и я уверен, что вы сможете построить нужный вам график, получив эти данные.
Любые комментарии и предложения приветствуются.