Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

21 Март 2019, 01:07 *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

Войти
Новости: Часто задаваемые вопросы по LibreOffice и Apache OpenOffice.org
 
   Начало   Помощь Поиск Войти Регистрация    задать вопрос  
Страниц: 1   Вниз
  Печать  
Автор Тема: Поиск последних 3 значений и возврат их среднего  (Прочитано 2789 раз)
0 Пользователей и 1 Гость смотрят эту тему.
coldrex
Участник
**
Offline Offline

Сообщений: 12


« Стартовое сообщение: 12 Май 2015, 19:00 »

Добрый день.

Есть список товаров, цена закупки которых иногда меняется. Это зависит от даты.
Например:
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) Возвращает их среднее.

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

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

* calcforum.ods (12.74 Кб - загружено 9 раз.)
Записан
TimSha
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 72


« Ответ #1: 12 Май 2015, 19:22 »

3) Берет три последних значения
А если оно - одно, или же ни разу не было? Какое-то задание, простите, оторванное от жизни.
Записан

"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. Подмигивающий
coldrex
Участник
**
Offline Offline

Сообщений: 12


« Ответ #2: 12 Май 2015, 19:28 »

Если одно - то одно. Если ни разу - то ни разу. Какой вопрос, такой ответ.
Записан
TimSha
Форумчанин
***
Offline Offline

Пол: Мужской
Сообщений: 72


« Ответ #3: 12 Май 2015, 19:52 »

Если одно - то одно. Если ни разу - то ни разу...
Тогда зачем морочить голову и себе и другим? - Стройте сводную и считайте среднее.
Записан

"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. Подмигивающий
coldrex
Участник
**
Offline Offline

Сообщений: 12


« Ответ #4: 12 Май 2015, 19:58 »

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

Пол: Мужской
Расположение: Киев
Сообщений: 2 727


Помогаю людям и компьютерам понимать друг друга


WWW
« Ответ #5: 13 Май 2015, 12:12 »

Формула ищет по всему большому списку этот товар
Не забудь переопределить диапазоны Даты, Товары и Цены. Сейчас они короткие, по размеру твоего образца.
Формула =ТутСреднееДолжноБыть ориентируется на название товара из соседней клетки слева.
Возможно, это всё можно было бы записать лаконичнее, но "если плохое решение выдаёт правильный результат, значит не такое оно и плохое"

* Last2IsLarge.ods (46.46 Кб - загружено 15 раз.)
« Последнее редактирование: 13 Май 2015, 16:02 от JohnSUN » Записан

Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне
coldrex
Участник
**
Offline Offline

Сообщений: 12


« Ответ #6: 15 Май 2015, 15:23 »

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

Т.е. там нет никакой формулы. Просто надпись про среднее.
Записан
JohnSUN
Капитана в тот день называли на "ты"
Гуру
*******
Offline Offline

Пол: Мужской
Расположение: Киев
Сообщений: 2 727


Помогаю людям и компьютерам понимать друг друга


WWW
« Ответ #7: 15 Май 2015, 16:27 »

Да зачем она там нужна! Работает, да и ладно...  Смеющийся Шучу, конечно
Она как тот суслик - ты её не видишь, а она там есть.
Я же в комментарии написал "Не забудь переопределить диапазоны..." Рассчитывал, что ты нажмёшь 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
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне
Страниц: 1   Вверх
  Печать  
 
Перейти в:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.21 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!