Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Необходимо посчитать объем по типам грузов и по годам.
 
#1
21.07.2022 12:56:56
Мужики помогите сделать формулу, заранее благодарен
 
 
 
#2
21.07.2022 13:18:58
в С7
Код
=СУММЕСЛИМН('Реестр материалов'!B:B;'Реестр материалов'!C:C;$B7;'Реестр материалов'!F:F;C$6)
(хотя.... можно написать любую формулу, которая вернет 0, результат будеьт одинаков)
а правильно вот так:
Код
=СУММЕСЛИМН('Реестр материалов'!$B:$B;'Реестр материалов'!$C:$C;$B7;'Реестр материалов'!$F:$F;C$6)
и тянуть вправо, вниз
а потом в В7:В10 впишите:
Габаритные   легковесные грузы
Сыпучие материалы (песок, щебень, инертные грузы)
Железобетонные изделия
ГСМ (Бензин, ДТ) наливом
Изменено: Ігор Гончаренко - 22.07.2022 21:17:22
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#3
21.07.2022 13:24:13
у меня результат получается 0, можно как-то сделать формулу чтобы она брата из справочники типа грузов какие к ней относятся МТР, так как в реестре есть только наименования МТР
 
 
 
#4
21.07.2022 13:30:01
сформулируете задачу - можно будет
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#5
21.07.2022 13:41:04
На основании данных в таблице Реестр материалов определить объем груза в разрезе типа груза и по годам.

Для определения типа груза используется информацию с вкладки "Справка" , тоесть в этой вкладке есть тип Грузов к нему относятся определенный МТР.
Задача состоит в том чтобы формула на основании справочники посчитала какой объём МТР относящийся Типу Грузов  был по годам.
Группа   МТРТип груза
Трубы обсадные и насосно-компрессорныеГенеральный   груз
 
 
 
#6
21.07.2022 13:52:26
[offtop] А что за дискриминация по половому принципу :( [/offtop]

Массивная =SUM(IF((VLOOKUP('Реестр материалов'.$C$2:$C$5103;Справка.$A$2:$B$15;2;0)=$B7)*('Реестр материалов'.$F$2:$F$5103=C$6);'Реестр материалов'.$B$2:$B$5103;0)), но не уверен, что она в excel будет работать
 
 
 
#7
21.07.2022 13:58:56
к сожалению не работает эта формула((
 
 
 
#8
21.07.2022 14:14:45
=СУММ(СУММЕСЛИМН('Реестр материалов'!$B$2:$B$5103;'Реестр материалов'!$F$2:$F$5103;C$6;'Реестр материалов'!$C$2:$C$5103;ЕСЛИ($B7=Справка!$B$2:$B$15;Справка!$A$2:$A$15)))
Формула массива
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#9
21.07.2022 14:22:46
Спасибо большое!
 
 
 
#10
21.07.2022 16:28:07
Power Query
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Группа МТР"}, {{"Count", each List.Sum([#"Вес (тн) "]), type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows",{"Группа МТР"},Table4,{"Группа МТР"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Тип груза"}, {"NewColumn.Тип груза"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn",{"Группа МТР"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"NewColumn.Тип груза", "Тип груза"}}),
    #"Grouped Rows1" = Table.Group(#"Renamed Columns", {"Тип груза"}, {{"Вес (тн) ", each List.Sum([Count]), type number}})
in
    #"Grouped Rows1"
Изменено: jakim - 21.07.2022 16:28:40
 
 
 
#11
22.07.2022 12:48:47
Задача не сложная и уж точно не требует использования массивов или чего-то сложного.

P. S. Единственное, данное мной решение работает только в том случае, если в исходных данных нет таких типов грузов, которые не должны присутствовать в отчёте, потому что "Генеральный груз" считается банальным вычитанием из общей суммы за год остальных типов грузов, которые уже занесены в отчёт (я сначала писал формулы для тех типов, которые включали в себя по одной группе МТР, ибо это проще).
Изменено: Владислав Головков - 22.07.2022 12:50:00
 
 
 
#12
22.07.2022 13:48:56
Цитата
Владислав Головков написал:
Задача не сложная и уж точно не требует использования массивов или чего-то сложного
Ага. Согласен, задача совсем несложная. И решение в файле выше прикольное. На один раз. А теперь представим, что в каждом типе груза у нас по 5 групп? Или что завтра на листе Справка изменится порядок расположения строк? Или соответствие группы типу изменится?

Я принял бы на работу соискателя даже в том случае, если бы он сначала сделал допстолбец в Реестре, куда ВПР-ом вывел бы значения типов груза. А потом да, СУММЕСЛИМН. Так видно, что человек понимает принцип, у него просто немного не хватает знаний, но это вопрос времени и желания

А вот при таком решении теста, как выше, кандидат точно идет отдыхать, он не понимает основного предназначения Excel - автоматизация. И это значит, что каждый раз при изменении в исходнике НЕ нужно будет проверять результат и переписывать свою формулу. Нужно написать один раз так, чтобы потом вообще к этому не возвращаться
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#13
22.07.2022 15:22:12
_Boroda_, если позволите, спрошу Вас :)
Задача легко решается сводной таблицей (ТЫК). Почему работодатели просят решить задачу именно формулами? Зачастую такое решение даже более сложное и менее универсальное. Почему бы не использовать хорошо подходящие под это инструменты?
 
 
 
#14
22.07.2022 15:40:42
К сожалению, ТЫК у меня с работы не открывается )))
Но нисколько не сомневаюсь, что там действительно все шикарно.
Если конкретно про эту задачу - то проще и быстрее, чем формула, здесь, думаю, не придумать. Любое другое решение займет больше времени, чем полминуты-минута на ту простенькую формулку, что я выше написал
А если вообще - то, думаю, часто так потому, что работодатели сами в основном с Excel не сильно-то и дружат. Что такое формулы - ну, хоть как-то, но понимают (как мне один раз сказали - это такая же СУММ, но только подлиннее )))) А вот остальное все - это из разряда волшебства.
Я обычно, если тесты провожу, то указываю способ решения или наоборот, говорю, что "Любым доступным способом, который будет работать при изменении исходных данных".
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#15
22.07.2022 16:03:00
Благодарю за ответ!
Цитата
_Boroda_ написал:
Если конкретно про эту задачу - то проще и быстрее, чем формула, здесь, думаю, не придумать. Любое другое решение займет больше времени, чем полминуты-минута на ту простенькую формулку, что я выше написал
Так она "простенькая", если понимать, что такое формула массива :).
Я имел в виду, что для сводной: загрузил 2 таблицы в PP, сделал связь по полю Группа МТР, вставил сводную с нужными столбцами и даже без мер суммой по столбцу объем получил результат. Вроде как интуитивно понятно, если есть хотя бы 10-и минутное знакомство с PP. Соглашусь, что по времени больше. Зато при добавлении новых типов груза ничего ручками добавлять не нужно :)
Цитата
_Boroda_ написал:
Я обычно, если тесты провожу, то указываю способ решения или наоборот, говорю, что "Любым доступным способом, который будет работать при изменении исходных данных".
А вот такой подход прямо поддерживаю :)
А вот ограничение в тесте ТС и вызвало негодование :). Хотя, может это к тому, чтобы кандидат руками не вносил значения?
Изменено: surkenny - 22.07.2022 16:03:20
 
 
 
#16
22.07.2022 16:42:44
Цитата
surkenny написал:
может это к тому, чтобы кандидат руками не вносил значения?
Видел такое.
Более того, видел, как копируют значения ячейки и вставляют в формулу. Получается типа
=325+664+8941285+1+2+88+... и так раз двести. Соискатель, у которого в резюме написано "Отличное владение Excel"
Вообще - я предпочитаю просто смотреть, как человек пишет формулу, макрос или что там еще делает. Сразу понятно все. Пусть даже не совсем верно сделает, но важно КАК сделает
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#17
22.07.2022 20:55:22
Вариант с массивной функцией, не требующей массивного ввода, без понятия, как там с производительностью:)
Код
=SUMPRODUCT('Реестр материалов'!$B$2:$B$5103;('Реестр материалов'!$F$2:$F$5103=C$6)*COUNTIFS(Справка!$B$2:$B$5103;$B7;Справка!$A$2:$A$5103;'Реестр материалов'!$C$2:$C$5103))
 
 
 
#18
22.07.2022 21:19:54
или внимательно посмотреть сообщение 2
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
Страницы: 1
Наверх