Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Определить расход ресурсов по количеству продукции и нормам расхода
 
#1
29.11.2016 17:03:30
Здравствуйте, словами не смогу объяснить, только на примере:
есть файлик, для примера удалил с него всё лишнее.

необходимо в тот участок, который я выделил красным вставить формулу, аналогичную соседнему(левому) столбику из зелёного блока. Там она очень громоздкая, но однотипная.
берётся кол-во какой-то продукции из жёлтого блока, умножается на норму определённого сырья этой продукции со второго листа.
вся сложность в том, что на втором листе наименования стоят в хаотичном порядке. и приходится их искать.

понимаю, что нужна какая-то формула массива, но я с ними плохо очень разбираюсь.
 
 
 
#2
29.11.2016 17:42:23
Как-то так можно...
 
 
 
#3
29.11.2016 17:48:25
Цитата
Александр Меркулов написал:
Там она очень громоздкая, но однотипная.
Действительно так. А не могли бы объяснить, что там происходит или, что Вы хотите получить в результате. Разбираться со всеми условиями формулы, нет интереса. Что с чем сравнить и что хотите получить? Только подробнее.
 
 
 
#4
29.11.2016 18:18:08
если на листе Расчет сырья список в колонке 2 (Тип) привести в соответствие со списком колонки 1 (наименование продукции)
в списках должны быть одни и те же названия в одинаковом порядке
в списке Наименование продукции уйти от ссылок на внешний файл, а оставить просто значения
то все решается простой функцией СУММПРОИЗВ длиной в 100 символов
в том виде, что данные сейчас - все правильно - только километровыми формулами
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#5
29.11.2016 18:21:07
Название темы должно отражать суть задачи. Предложите новое. Модераторы переименуют тему.
Иначе решения с другими функциями не принимаются. Вы сами не хотите.
 
 
 
#6
29.11.2016 18:29:27
Ігор Гончаренко, Извините, я не понял, Вы объясняете за автора темы, или это Ваше понятие решения?
 
 
 
#7
29.11.2016 18:40:57
gling, это советы автору темы как можно реально автоматизировать расчет
Название возможно такое:
"Определить расход ресурсов по количеству продукции и нормам расхода ресурсов на единицу продукции"
это мое представление о сути задачи, но автору, конечно, виднее, сейчас он придет и скажет свое веское слово, а не скажет - то тема может быть просто закрыта, как не соответствующая правилам
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#8
30.11.2016 08:31:48
Всех прошу меня извинить, после создания темы пришлось срочно отъехать до конца дня. Не было возможности мониторить.

В посте №2 от Влада. решение подошло.
Код
=СУММПРОИЗВ(('Расчёт расхода на продукцию'!$A$4:$A$76=ТРАНСП($B$9:$B$28))*ТРАНСП(J$9:J$28)*ИНДЕКС('Расчёт расхода на продукцию'!$C$4:$CP$76;;$C61))
Не совсем понимаю как работает эта функция.
•Суммпроизв - перемножает между собой массивы
•создаётся первый критерий отбора массива, в котором создаётся условие, где значения вертикального столбика должно быть равно точно такому же столбику с другого листа, но почему-то транспонированному.
•второе условие - берётся само кол-во(которое нужно умножить)
•потом в индексе не задаётся строчка в которой нужно искать(наверное какая-то особенность работы с массивами), но берётся номер нужного столбца, с нужным сырьём, которое я до этого вынес отдельно, для улучшения быстродействия.

Уважаемые форумчане, подскажите, пожалуйста, по поводу первого условия в этой формуле, где вертикальный столбик должен быть равен транспонированной версии такого же столбца.

P.S.Пример в посте №2.
 
 
 
#9
30.11.2016 08:34:55
Цитата
Ігор Гончаренко написал:
если на листе Расчет сырья список в колонке 2 (Тип) привести в соответствие со списком колонки 1 (наименование продукции)
в списках должны быть одни и те же названия в одинаковом порядке
К сожалению нет такой возможности. Если бы было всё в правильном порядке - не было бы подобных проблем.
Дело в том, что данный порядок необходимо оставить в неизменном виде из-за предыдущего работника, у которого около десятка разных отчётов и в каждом из них разное количество и порядок этих стрчоек
 
 
 
#10
30.11.2016 10:59:04
Цитата
Александр Меркулов написал: Не совсем понимаю как работает эта функция
Работает просто: аргументы в функции представляют собой своего рода виртуальную матрицу, на которую наложен двойной фильтр условий (по количеству и по нормам). Первый критерий как раз и создает рабочую матрицу, состоящую из результатов сравнения каждого названия из списка наименований сырья на одном листе с каждым названием на другом листе.
Изменено: Влад - 30.11.2016 11:07:09 (Уточнил описание)
 
 
 
#11
30.11.2016 11:11:42
Влад,вот я как за-таки не совсем понимаю именно это условие.

В формуле СУММПРОИЗВ перемножаются несколько массивов, ИЛИ она используется(как в данном случае) для нахождения пересечений(как ИНДЕКС) по каким-то условиям. все эти условия перечисляются знаком звёздочки, который в экселе заменяет оператор " И " это я всё понимаю.

НО:
Код
'Расчёт расхода на продукцию'!$A$4:$A$76=ТРАНСП($B$9:$B$28)
эта часть мне непонятна, что тут происходит?
с одного листа из диапазона берутся данные(с наименованием изделий) и они должны быть равны данным из точно такого же столбика с другого листа, но почему "ТРАНС"? я боюсь ошибится, но ТРАНС - преобразование горизонтальной строки в вертикальный столбик и наоборот. В данном случае оба столбика вертикальные. И поэтому не понимаю этого, но если убрать "ТРАНС", то перестаёт работать.
 
 
 
#12
30.11.2016 11:42:36
Возможно, алгоритм формирования матрицы по указанному критерию Вам будет более понятным в визуальном представлении (см. файл)
 
 
 
#13
30.11.2016 11:45:29
Влад,Да, так понятнее, спасибо большое за старания и за помощь, я представлял это условие несколько иначе.
 
 
 
#14
30.11.2016 11:48:50
'Расчёт расхода на продукцию'!$A$4:$A$76=ТРАНСП($B$9:$B$28)
тут формируется матрица размером КоличествоНаименованийвРасход х КоличествоНаименованийвРасчет (строк Х столбцов) с значениями Истина там, где наименования совпали и Ложь, где - нет
затем каждая строка ЭТОЙ матрицы умножается на количество наименований - получаем такую же матрицу, но уже везде вместо ИСТИНА стало количество, а в остальных ячейках 0, потом каждая колонка этой матрицы последовательно умножается на нормы расхода соотв. ресурса на данное изделие и все эти произведения суммируются. Все!
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
Страницы: 1
Читают тему
Наверх