Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Поиск последних 3 значений и возврат их среднего, нечто вроде VLOOKUP
 
#1
07.05.2015 20:43:59
Добрый день.

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

Приложил файл. Заранее благодарен за помощь.

P.S. Эта тема опубликована еще на 2 форумах, пардон за кросспостинг - но момент рабочий, надо решить.
 
 
 
#2
07.05.2015 20:44:17
Я упростил задачу:

в листе DB в разделе Себес просто должна показываться цифра за посл. 3 позиции из листа Закупка.
 
 
 
#3
07.05.2015 21:09:33
Кросс
 
 
 
#4
07.05.2015 22:40:18
используйТЕ формулу наименьший и срзнач
хм...случаем не CVAr считаеТЕ ?
Изменено: Slow - 07.05.2015 22:59:19
 
 
 
#5
08.05.2015 00:12:58
Здравствуйте!
Прошу прощения, сейчвс мало времени, но, в своё время мне помогли с UDFкой, которую немного подпилил под себя. Сможете разобраться? Выкладываю пример.
То что Вам нужно в столбце G.
Удачи!
 
 
 
#6
08.05.2015 01:04:20
Здравствуйте. Что то нарисовал, посмотрите может подойдет, только длинные формулы получились.
Изменено: gling - 08.05.2015 18:13:19
 
 
 
#7
08.05.2015 05:06:53
Пример неправильно составлен.
Изменено: Владимир - 08.05.2015 05:08:53
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#8
12.05.2015 18:31:10
Цитата
Владимир написал: Пример неправильно составлен.
Похоже на правду.

Для Open Office это будет также выглядеть?
 
 
 
#9
12.05.2015 18:40:51
Цитата
Route66 написал: Для Open Office это будет также выглядеть?
off
А своими силами проверить слабо?
Форум, однако - по XL, а открытый и свободный офис - это другие планеты на других орбитах... ;)
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#10
12.05.2015 18:42:01
да вот уже засел проверять своими руками  ...  
 
 
 
#11
12.05.2015 19:09:26
Код
=AVERAGE(INDEX(D2:D13;N(INDEX(MATCH(LARGE(IF(F6=B2:B13;A2:A13);{1;2;3});A2:A13;);))))

Ошибка: переменная отсутствует
Изменено: Route66 - 12.05.2015 19:10:02
 
 
 
#12
12.05.2015 19:43:01
Route66, а в сообщении #6 файл смотрели? Или там ваще ни то?
 
 
 
#13
12.05.2015 19:45:29
Признаюсь честно , там такие длинные формулы, что я ничего не понял толком.
 
 
 
#14
12.05.2015 19:52:03
А результат соответствует требуемому?. Формулы может быть и упростить можно. Сама логика подсчета правильная или нужно три последних посчитать по максимальным датам без сортировки столбца?
Изменено: gling - 12.05.2015 19:54:34
 
 
 
#15
12.05.2015 20:00:58
Цитата
gling написал:... Сама логика подсчета правильная или нужно три последних посчитать по максимальным датам без сортировки столбца?
Off
Учтем, однако, что данная заморочка - для калька ОО/LibO... ;)
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
 
 
#16
12.05.2015 20:01:58
Gling, спасибо за то , что пытаетесь помочь.
Я вот только не понимаю, почему там столбцы "себес" "себес" "среднее" ...
В DB должно быть только среднее за посл. 3 месяца, поскольку она служит базой для расчета прибыли.
 
 
 
#17
12.05.2015 20:11:06
Первая "себес" это то что было в примере, Вы писали
Цитата
Я упростил задачу:  в листе DB в разделе Себес просто должна показываться цифра за посл. 3 позиции из листа Закупка.
я создал "себес"-закрашен зеленым. Так как в первом сообщении результат должен быть среднее значение, то добавил еще столбец "среднее". Что то не так? Или желания изменились?
 
 
 
#18
12.05.2015 20:13:24
Результат верный , в графе среднее. Но как считает формула - там же в "закупке" товары сгруппированы. А в реальной жизни они все в разнобой.
 
 
 
#19
12.05.2015 20:17:33
Цитата
заморочка - для калька ОО/LibO..
Что это такое, пока не знаю, но посмотрю. О том что это не для Excel нигде не написано, и форум не про ОО/LibO.. Может поняв логику, можно будет использовать и в ОО/LibO..
Цитата
в реальной жизни они все в разнобой.
А что мешает их отсортировать?
Изменено: gling - 12.05.2015 20:21:03
 
 
 
#20
12.05.2015 20:19:48
да я и пытаюсь.

формулы те же , судя по спискам, но почему-то дает ошибку.
 
 
 
#21
12.05.2015 20:23:20
Цитата
но почему-то дает ошибку.
Здесь я не помощник. А может потому, что это формулы массива?
Изменено: gling - 12.05.2015 20:25:10
 
 
 
#22
12.05.2015 20:23:43
а вы представьте - идет постоянный приход товаров - каждый день, строк по 20.
идет постоянный учет продаж, и БД есть основа всему.
формула выдает в БД актуальный себес товара за посл. 3 месяца - соотв. в листе продаж корректно рассчитывается каждая сделка.
слишком геморно все это вручную делать, а крутых программ вроде 1С нам не надо.  
 
 
 
#23
12.05.2015 20:26:37
Цитата
:но почему-то дает ошибку.
Цитата
Здесь я не помощник. А может потому, что это формулы массива?
не знаю. на форумах опенофиса народ не очень дружелюбный. надеюсь, заграница нам поможет
 
 
 
#24
12.05.2015 20:41:12
У Вас то даты отсортированы. Главное чтобы даты были отсортированы, а между одинаковым товаром может быть другой товар и не важно сколько строк между будет занято, формула найдет три последних, не найдет три выдаст 2 и среднее двух, найдет один его и выдаст, не найдет- значит 0.  
 
 
 
#25
12.05.2015 20:42:00
даты всегда по порядку.
 
 
 
#26
12.05.2015 22:10:38
вы себестоимость за месяц и раз в месяц считаете?
данные в таблице только за один месяц?
стоимость начального остатка учли?
при усреднении учли количество? просто цену усреднить неверно для себестоимости!

проще всего сделать запрос SQL (файл должен быть в папке C:\1\, на таблице правая кнопка мыши-Обновить)
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#27
13.05.2015 09:49:41
Цитата
TheBestOfTheBest написал: при усреднении учли количество? проще всего сделать запрос SQL
как в запросе SQL  прописать условие "за последние 3 (по дате) поставки"?.. - думаю в этом вопрос ТС... но ответа не знаю - возможно ли такое вообще через SQL?..
p/s/
и думается мне, тут не в количестве дело, а в том, что цена без НДС - не есть себестоимость в принципе... т к в цену включаются и др издержки (не только НДС), о которых в таблице не сказано... если хотя бы знать, уплаченный НДС, то можно прикинуть саму Добавленную Стоимость, чтобы её вычесть из цены, тем самым получить Себестоимость... имхо - задача неполная
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#28
13.05.2015 10:20:37
Может, см. оператор TOP.

В торговых организация практически всегда себестоимость = цене закупки без НДС, если нет таможни и доставки за счет покупателя при покупке товаров. А вот количество в партиях может быть существенно разное (1000 шт по 30 руб + 10 шт по 33 руб => средняя не будет =(30+33)/2=31,50 !).
Цитата
JeyCi написал:
можно прикинуть саму Добавленную Стоимость
Как? По НДС не получится, т.к. есть расходы без НДС (зарплата, например).  По наценке? Можно, но это будет неточно, это будет как раз типа 31,50. Раньше в розничной торговле так и считали брали выручку и среднюю наценку, из них получали нечто, вроде валовой прибыли (Добавленной стоимости). Работает только если товар имеет единую %-ю наценку, что теперь редко встретишь в связи с применением маркетинговых технологий.
Проще взять закупочные цены и усреднить их на каком-либо периоде с учетом остатка на начало периода.
То, что предлагает автор работает только на последний день периода, т.е. в текущем учете себестоимость этим способом рассчитывать некорректно, т.к. есть опасность, что после расчета С/С (не в последний день периода), будет поступление товара по другой цене, и С/С за 20 дней не будет равна С/С за 30 или 31 день. Надо принять какую-нибудь учетную политику.
В 1С считают по партиям поступления - самый точный метод.

Все же, вопросы #26 остаются открытыми.
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#29
13.05.2015 10:53:59
Цитата
TheBestOfTheBest написал: Как? По НДС не получится, т.к. есть расходы без НДС (зарплата, например).  
тут речь о ценах, а не о з/п... НДС - это всегда какой-то процент (надо знать это число - определяется в налоговом кодексе конкретной страны - не знаю, откуда ТС)... через этот показатель (кол-во руб или др) найти саму Добавленную Стоимость, которую и вычитать из Цены без НДС...
Цитата
TheBestOfTheBest написал: То, что предлагает автор работает только на последний день периода, т.е. в текущем учете себестоимость этим способом рассчитывать некорректно, т.к. есть опасность, что...
есть вероятность (иного он не уточнял), что ему просто так надо прикинуть по текущим вопросам, как описал... последние 3 дня - среднее по себестоимости в таблице
Цитата
TheBestOfTheBest написал: В торговых организация практически всегда себестоимость = цене закупки без НДС
ДА, если цена закупки, а не цена реализации - то вы правы - я погорячилась (задумавшись о цифрах без названия листа файла и длинного описания в первом посте)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#30
15.05.2015 15:29:09
Ребят, как-то сложно все  у вас пошло.
Цитата
TheBestOfTheBest написал: вы себестоимость за месяц и раз в месяц считаете?
Я не считаю себес раз в мес.
Цитата
данные в таблице только за один месяц?
Таблица длится весь год - и формула берет название товара - поле цена - и просто выдает среднее за последние три даты
Цитата
стоимость начального остатка учли?
Стоимость нач. остатка не нужна.
Цитата
при усреднении учли количество? просто цену усреднить неверно для себестоимости!
Просто цену усреднить неверно - но мне надо именно так.
 
 
 
Страницы: 1 2 След.
Наверх