Поиск последних 3 значений и возврат их среднего

Автор coldrex, 12 мая 2015, 19:00

0 Пользователи и 1 гость просматривают эту тему.

coldrex

Добрый день.

Есть список товаров, цена закупки которых иногда меняется. Это зависит от даты.
Например:
05/01/15 - зеркало 4 мм, цена за 1 м2 - 6,00
05/02/15 - зеркало 4 мм, цена за 1 м2 - 6,50
04/05/15 - зеркало 4 мм, цена за 1 м2 - 5,50

Товары разные, идут списком по датам.
Это по сути есть меняющяся себестоимость сырья.
При записи продаж, формула VLOOKUP возвращает значние цены, и далее идет подсчет прибыли и т.д.

Так вот, поскольку себестоимость постоянно меняется, то формула должна выбирать последние 3 значения и выдавать их AVERAGE.
Т.е. процесс такой:
1) Выбираю товар из списка
2) Формула ищет по всему большому списку этот товар
3) Берет три последних значения (допустим, по датам: 05/01 + 05/02 + 04/05
4) Возвращает их среднее.

В приложении кусок файла. 

Заранее спасибо за ответы.

TimSha

Цитата: coldrex от 12 мая 2015, 17:003) Берет три последних значения
А если оно - одно, или же ни разу не было? Какое-то задание, простите, оторванное от жизни.
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)

coldrex

Если одно - то одно. Если ни разу - то ни разу. Какой вопрос, такой ответ.

TimSha

Цитата: coldrex от 12 мая 2015, 17:28Если одно - то одно. Если ни разу - то ни разу...
Тогда зачем морочить голову и себе и другим? - Стройте сводную и считайте среднее.
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)

coldrex

1) Выбираю товар из списка
2) Формула ищет по всему большому списку этот товар
3) Берет три последних значения (допустим, по датам: 05/01 + 05/02 + 04/05
4) Возвращает их среднее.

JohnSUN

#5
Цитата: coldrex от 12 мая 2015, 19:58
Формула ищет по всему большому списку этот товар
Не забудь переопределить диапазоны Даты, Товары и Цены. Сейчас они короткие, по размеру твоего образца.
Формула =ТутСреднееДолжноБыть ориентируется на название товара из соседней клетки слева.
Возможно, это всё можно было бы записать лаконичнее, но "если плохое решение выдаёт правильный результат, значит не такое оно и плохое"
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

coldrex

Спасибо, тезка. Только я ни хрена не понял, если честно =)

Т.е. там нет никакой формулы. Просто надпись про среднее.

JohnSUN

Да зачем она там нужна! Работает, да и ладно...  ;D Шучу, конечно
Она как тот суслик - ты её не видишь, а она там есть.
Я же в комментарии написал "Не забудь переопределить диапазоны..." Рассчитывал, что ты нажмёшь Ctrl+F3 и сам всё увидишь.
Смотри картинку.
Зелёная рамка - окончательное вычисление, тот самый AVERAGE от трёх цен, о котором ты и спрашивал.
Он получает для расчета три числа: ПоследняяЦена, ПредпоследняяЦена и ПерваяЦена (нужно было обозвать её ПредПредПоследняяЦена, но слишком много писать)
Формулы для этих трёх обведены оранжевой рамкой.
В принципе, все три очень похожи, отличаются только двумя нюансами. По шагам. ПоследняяЦена:
IF(SUMPRODUCT(ROW(Даты);Даты=LARGE(Даты *(Товары=C7);1))=0;
   NA();INDEX(Цены;SUMPRODUCT(ROW(Даты);Даты=LARGE(Даты *(Товары=C7);1))-1;0))

Условие в IF хоть и длинно записано, но не очень сложное: номер каждой строки диапазона Даты умножается на условие "дата в этой строке совпадает с самой большой датой для этого товара". Второй параметр в LARGE, единичка, как раз это и обозначает - "самый большой". Двоечка обозначает "второй по порядку" и так далее.
Это и есть первое отличие - в ПредпоследняяЦена и в ПерваяЦена в этих местах стоят 2 и 3.
Если нет ни одной даты с указанным товаром, SUMPRODUCT вместо номера строки вернёт 0. Если это случилось, для ПоследняяЦена, то возвращаем значение специальной функции NA(), она выдаст #Н/Д вместо результата.
Тут спорный момент: кое-кто не любит непонятных букв с решётками, предпочитают возвращать 0. Это опасно. Если список товаров длинный, мы можем просто не заметить опечатку и какая-нибудь позиция "стекло хрустальное 1,5 метра" ценой по стопятнадцатьтысяч уйдет клиенту за 0,00. И виноват, конечно, будет программист.
Для ПредпоследняяЦена вместо NA() в этом случае выводится ПоследняяЦена. Ну, среднее значение от двух одинаковых чисел и будет это число.
А для ПерваяЦена - среднее от двух последних цен.
Так обрабатывается случай, на который намекал TimSha - когда последних цен меньше трёх или вообще нет.
Ну, а окончание формулы понятно - SUMPRODUCT отдал нам номер строки, а INDEX выдернул по этому номеру число из диапазона Цены...

Так понятнее?
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне