Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Расчет стоимости салатов по цене их компонентов
 
#1
16.09.2011 21:17:49
Приветствую!  
 
К сожалению ответа на форуме не нашел :(  
 
Следующая ситуация.  
 
Имеем файл с тремя листами: "Рецепты", "Стоимость компонентов", "Стоимость салатов".    
 
На листе "Рецепты" даны названия компонентов (по сути - уникальные коды) и их необходимые объемы. при этом количество компонентов в салате может быть разное. В файле указал максимальное значение - 5.  
 
На листе "Стоимость компонентов" даны цены по годам каждого из компонентов. При этом есть компоненты, которые по тем  или иным причинам могут быть не указаны на листе "Рецепты".  
 
На листе "Стоимость салатов" нужно рассчитать динамику стоимости салатов на основании динамики цен их компонентов.  
 
Вопрос. Возможно ли сделать это как-то кратко и изящно, например, при использовании массивов?  
 
Пробовал вариант, когда вручную составляется формула, где каждый объем необходимого компонента перемножается на его цену в соответствующем году (цену "вытаскивал" через функцию ВПР)и суммируется с последующим произведением (аналогичным по своему принципу).  
 
Получается уж очень громоздко!    
В реальной ситуации компонентов может быть до 15 штук.
 
 
 
#2
16.09.2011 22:39:47
примерно так
 
 
 
#3
17.09.2011 10:44:16
Два варианта (через индекс  и смещ)
 
 
 
#4
20.09.2011 08:35:52
Чтобы не было громоздко, нужно создать другую структуру таблицы. Ведь рецепт, кол-во и цена постоянны.
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#5
20.09.2011 09:43:32
Володь, мне кажется, что ТС этот вопрос уже не интересен....
 
 
 
#6
20.09.2011 18:12:59
Он взял твою формулу и ушёл..  
 
----  
33312
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#7
20.09.2011 23:25:13
Сорри, сразу ответить не смог. Тема еще очень как даже интересна!  
 
{quote}{login=Владимир}{date=20.09.2011 08:35}{thema=}{post}Чтобы не было громоздко, нужно создать другую структуру таблицы. Ведь рецепт, кол-во и цена постоянны.{/post}{/quote}  
 
Боюсь, что нет.  
 
Там ситуация следующая.  
- Виды салатов могут добавляться.    
- Да, рецепт салата - стабильный.    
- Цены компонентов тоже добавляются за каждый новый временной период (там еще "хуже" - поквартально).  
 
Предложенные первые два варианта хороши! это все лучше, чем у меня с применением функции ВПР (минимум в 5-6 строк - формулы)  
 
Однако текущая ситуация имеет следующие условия:  
- уникальных наименований компонентов свыше 500  
- салат состоит из разных компонентов  
- салат состоит из разного количества компонентов (т.е. число компонентов в салатах м.б. разным)  
 
И получается, что недостаток предлагаемых методов в следующем:  
 
1. Когда в строку выстраиваем 500 компонентов, то очень ненаглядно просматривать состав каждого из салатов. Ведь, не каждая ячейка заполняется.  
 
2. Возникает вторая ситуация (но она отделена от 1й и про нее я тоже собирался интересоваться), когда компонент может повторятся, при это последовательность компонентов - обязательна.  
Имеем ситуацию, когда по столбцам у нас идет порядковый номер компонента.  
Т.е., например, "Морковка, Соль, Капуста, Соль"  
При этом важно, что Морковь идет первой, соль стоит на 2м и 4м местах, а Капуста - на третьем.  
 
ЗЫ  
и еще вопрос по описанной задаче  
А есть возможность ее решить именными диапазонами? И возможно ли в случае создания именного двумерного массива формулами вытаскивать из него нужные целые столбцы и строки?    
 
Работу с массивами данных через именные диапазоны открыл для себя недавно, еще многого не знаю. :(
 
 
 
#8
21.09.2011 00:07:27
1) Разве в салате может быть 500 компонентов?    
Все возможные компоненты (хоть 1000) у Вас на листе "Стоимость компонентов", а рецепты на листе "Рецепты" Правда, не уверен, что в таком виде ими (рецетами) удобно пользоваться, если рецептов более десятка, неговоря о сотне...  
2) Для расчета стоимости не имеет значенияЮ сколько раз и на каком месте стоит соль или что-то другое - все посчитается.  
3)Возможно, но не всегда это облегчает решение.
 
 
 
#9
21.09.2011 09:15:44
Михаил С.  
 
В предыдущем посте неточно выразился.  
 
Безусловно ваш вариант - оптимальный т.к. он:  
- не громоздкий (умещается в одну строку)  
- работает в описанной ситуации, когда не нужно все компоненты выстраивать по столбцам.  
 
Вариант Владимира интересен тем, что он использует формулу массива! :)  
 
ЗЫ  
>Разве в салате может быть 500 компонентов  
"Салат" - здесь используется как аналогия. В реальности - да. там не салат.
 
 
 
#10
21.09.2011 09:24:47
попробуй мою программку, есть возможность вести базу блюд и формировать автоматически меню  
 
http://www.fayloobmennik.net/994754]Калькуляция (version2).xlsm
 
 
 
#11
23.09.2011 11:01:46
Если так..
 
 
 
#12
04.10.2011 12:33:49
Еще раз спасибо всем за предложенные варианты.  
 
Применил на практике изящную формулу, предложенную Михаилом С., вместо сложной конструкции, состоящей из ВПР-функций.  
 
Решил продемонстрировать разницу "было - стало":  
 
Было:  
=ЕСЛИ('Рецепты'!$H1553="";0;'Рецепты'!$H1553*ВПР(ПРАВСИМВ('Рецепты'!$H9;10);Стоимость_Компонентов!$C$7:$AH$581;Стоимость_Компонентов!G$4;ЛОЖЬ))+ЕСЛИ('Рецепты'!$I1553="";0;'Рецепты'!$I1553*ВПР(ПРАВСИМВ('Рецепты'!$I9;10);Стоимость_Компонентов!$C$7:$AH$581;Стоимость_Компонентов!G$4;ЛОЖЬ))+ЕСЛИ('Рецепты'!$J1553="";0;'Рецепты'!$J1553*ВПР(ПРАВСИМВ('Рецепты'!$J9;10);Стоимость_Компонентов!$C$7:$AH$581;Стоимость_Компонентов!G$4;ЛОЖЬ))+ЕСЛИ('Рецепты'!$K1553="";0;'Рецепты'!$K1553*ВПР(ПРАВСИМВ('Рецепты'!$K9;10);Стоимость_Компонентов!$C$7:$AH$581;Стоимость_Компонентов!G$4;ЛОЖЬ))+ЕСЛИ('Рецепты'!$L1553="";0;'Рецепты'!$L1553*ВПР(ПРАВСИМВ('Рецепты'!$L9;10);Стоимость_Компонентов!$C$7:$AH$581;Стоимость_Компонентов!G$4;ЛОЖЬ))+ЕСЛИ('Рецепты'!$M1553="";0;'Рецепты'!$M1553*ВПР(ПРАВСИМВ('Рецепты'!$M9;10);Стоимость_Компонентов!$C$7:$AH$581;Стоимость_Компонентов!G$4;ЛОЖЬ))+ЕСЛИ('Рецепты'!$N1553="";0;'Рецепты'!$N1553*ВПР(ПРАВСИМВ('Рецепты'!$N9;10);Стоимость_Компонентов!$C$7:$AH$581;Стоимость_Компонентов!G$4;ЛОЖЬ))+ЕСЛИ('Рецепты'!$O1553="";0;'Рецепты'!$O1553*ВПР(ПРАВСИМВ('Рецепты'!$O9;10);Стоимость_Компонентов!$C$7:$AH$581;Стоимость_Компонентов!G$4;ЛОЖЬ))+ЕСЛИ('Рецепты'!$P1553="";0;'Рецепты'!$P1553*ВПР(ПРАВСИМВ('Рецепты'!$P9;10);Стоимость_Компонентов!$C$7:$AH$581;Стоимость_Компонентов!G$4;ЛОЖЬ))+ЕСЛИ('Рецепты'!$Q1553="";0;'Рецепты'!$Q1553*ВПР(ПРАВСИМВ('Рецепты'!$Q9;10);Стоимость_Компонентов!$C$7:$AH$581;Стоимость_Компонентов!G$4;ЛОЖЬ))+ЕСЛИ('Рецепты'!$R1553="";0;'Рецепты'!$R1553*ВПР(ПРАВСИМВ('Рецепты'!$R9;10);Стоимость_Компонентов!$C$7:$AH$581;Стоимость_Компонентов!G$4;ЛОЖЬ))+ЕСЛИ('Рецепты'!$S1553="";0;'Рецепты'!$S1553*ВПР(ПРАВСИМВ('Рецепты'!$S9;10);Стоимость_Компонентов!$C$7:$AH$581;Стоимость_Компонентов!G$4;ЛОЖЬ))+ЕСЛИ('Рецепты'!$T1553="";0;'Рецепты'!$T1553*ВПР(ПРАВСИМВ('Рецепты'!$T9;10);Стоимость_Компонентов!$C$7:$AH$581;Стоимость_Компонентов!G$4;ЛОЖЬ))+ЕСЛИ('Рецепты'!$U1553="";0;'Рецепты'!$U1553*ВПР(ПРАВСИМВ('Рецепты'!$U9;10);Стоимость_Компонентов!$C$7:$AH$581;Стоимость_Компонентов!G$4;ЛОЖЬ))+ЕСЛИ('Рецепты'!$V1553="";0;'Рецепты'!$V1553*ВПР(ПРАВСИМВ('Рецепты'!$V9;10);Стоимость_Компонентов!$C$7:$AH$581;Стоимость_Компонентов!G$4;ЛОЖЬ))  
 
Стало:  
=СУММПРОИЗВ((ИНДЕКС(Рецепты!$K$8:$Y$385;ПОИСКПОЗ($G12;Рецепты!$F$8:$F$385;0);0)='Стоимость_Компонентов'!$E$8:$E$2474)*ИНДЕКС(Рецепты!$AR$8:$BF$385/1000;ПОИСКПОЗ($G12;Рецепты!$F$8:$F$385;0);)*'Стоимость_Компонентов'!K$8:K$2474)  
 
Что называется, почувствуйте разницу! :)  
 
Еще раз - большое спасибо!
 
 
 
#13
04.10.2011 12:46:28
Представляю, что будет, если компонентами "салата" станут "салаты" или другими словами, стоимость компонентов придется определять так же, как стоимости салатов, по стоимости составляющих (эээ "...в доме, который построил Джек...")
 
 
 
#14
04.10.2011 12:49:01
Подразумевается, что названия салатов и компонентов уникальные. В реальности, - не салаты. Вместо названий салатов и компонентов - Id
 
 
 
#15
04.10.2011 12:54:41
Вы не увидели того, что я написал. Я про вложенность, а не про уникальность и не про салаты.
 
 
 
#16
04.10.2011 13:05:04
не совсем понимаю, где подводный камень?  
 
на одном листе -рецепты (перечни компонентов)  
на втором - цены компонентов  
на третье - рассчитывается стоимость как сумма произведении компонентов на соответствующие цен.  
 
что может случиться? прошу объяснить.
 
 
 
#17
04.10.2011 13:12:57
Да нет подводного камня. У Вас же все работает? Просто я бы организовал данные в виде списков, а не матриц. Я делал подобное, но там блюда формировались из продуктов и сырья. Продукты - это как в Вашем случае, компоненты. А сырье - это такие продукты, которые сами состояли из продуктов. Как в Проводнике. Папки могут содержать файлы и папки, которые тоже могут содержать файлы и папки и т.д. Вложенность. Просто сейчас у Вас компоненты - конечная составляющая. И слава богу.
 
 
 
#18
20.12.2011 18:49:02
Приветствую!  
 
Эх. Задача с салатами усложнилась. :(  
 
Теперь ситуация следующая (см. файл):  
 
Есть рецепты салатов с нормами компонентов для приготовления 1 кг того или иного салата. (Лист "Рецепты")  
 
Цены компонентов меняются по годам. (лист "Стоимость компонентов")  
 
Есть необходимые объемы салатов, кот. требуется приготовить ( Лист "Объем необходимых салатов")    
 
В Листе "Стоимость салатов" рассчитывается по годам стоимость произведенных объемов салатов на основании стоимости их компонентов и объемов производства.  
 
Как-то можно это рассчитать по изящной формуле Михаила С. с применением функции СУММПРОИЗВ(), модернизировав предыдущую формулу из обсуждения выше?  
 
Заранее благодарен.
 
 
 
#19
20.12.2011 21:38:48
забыл добавить, одно условие:  
данные из листа "Объем необходимых салатов" по каждому году, должны перемножаться на каждый компонент в отдельности.
 
 
 
#20
21.12.2011 13:25:15
Так?
 
 
 
#21
26.12.2011 11:41:05
Похоже, что так!  
 
Видимо, конструкция на базе функции "Смещ" позволяет больше, чем суммпроизв. Супер! Буду разбираться!
 
 
 
#22
18.04.2012 19:15:09
Есть ситуация (см. файл)  
 
существуют некие фрукты с определенными кодами (7 символов, включая первый пробел).  
 
также существуют соответствующие кодам коэффициенты и объем производства за год.  
 
вопрос    
как при помощи функции "Суммпроизв" перемножить коэффициенты фруктов, чьи коды имеют на конце "99", на соответствующий объем производства за год.  
 
суть вопроса - как просматривая матрицу кодов коэффициентов, вычленять из них последние 2 символа?  
 
Через дополнительную матрицу у меня это получилось. А как это сделать без дополнительной матрицы?  
 
также пробовал писать что-то типа "?????99" - не помогло.  
пытался через функции "Значен" и "правсимв" - тоже не помогло :((  
 
ЗЫ  
Автопересчет в файле выключен.
 
 
 
#23
18.04.2012 19:25:10
=СУММПРОИЗВ(--(ПРАВСИМВ(C6:E8;2)*1=99)*(G6:I8)*K6:K8)  
 
------------------  
Тему нужно новую открывать.
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#24
18.04.2012 19:38:46
Let, Вы теперь все свои вопросы будете задавать в одной теме?
 
 
 
Страницы: 1
Читают тему
Наверх