Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
Приемы суммирования в штатном расписании, результат должен находиться внутри диапазона
 
    Уважаемые, ГУРУ и СЕНСЕИ (без иронии), помогите с формулами суммирования в штатном расписании (EXCEL 2003).
Количество штатных единиц (строк) в подразделениях (диапазонах) всех уровней меняется чуть ли не ежемесячно и очень трудно искать и вносить изменения в формулы.
Попытки создания структуры и промежуточных итогов, использования формул массива только все запутывают.
Мне кажется наиболее простым для понимания решением было бы суммирование по нескольким (более двух) условиям.
Возможно СУММПРОИЗВ, но как их применить в каждой итоговой строке (они окрашены), катастрофически не хватает знаний.
Здесь приведен лишь фрагмент региональной структуры и показаны итоги, которые должны быть.
На самом деле количество строк около 6000.
14.03, по подсказке gling и Сергея, в примере убрал пробелы после Итого.
Изменено: СИДОР СИДОРОВ - 6 Апр 2015 11:24:14
 
А сводная таблица вам не подойдет?
 
Вариант сводной.
Изменено: gling - 12 Мар 2015 00:47:18
 
gling написал:
Вариант сводной.
Из этой исходной таблицы формируется целый ряд документов путем добавления столбцов справа, где суммируюся данные для решения других задач и суммирование завязано на данные первых четырех граф, поэтому их порядок менять не желательно.
Но ваша помощь мне очень пригодится для решения других, более мелких задач, и подсказала интересные идеи для решения других задач. Спасибо Вам добрый человек.
 
вот вариант формулами он конечно хуже чем сводной так как промежуточные итоги надо выносить в отдельный столбец
Лень двигатель прогресса, доказано!!!
 
Ваши формулы хороши и лучше подходят для моих задач, но находятся вне таблицы с данными. И тоже дали много интересных идей. Которые не появлялись у меня потому что я, как говорится, "варился в собственном соку" и пытался несколько лет решить задачу на основе результатов изучения форумов по EXCEL. У меня есть "Любимая" формула для решения таких задач, она находится в зеленых ячейках, приложенного примера "Ш_расп_е ФРАГМЕНТ ПРИМЕР с моей формулой.xls". А  как ее применить в ячейках окрашенных сиреневым цветом я никак не пойму.
 
Цитата
окрашенных сиреневым цветом
Попробуйте использовать такую
Код
=СУММПРОИЗВ(($B$3:$B11=$B11)*($D$3:$D11="Итого ")*(E$3:E11))
 
можно такую по графе всего копировать в ней ни чего менять не надо
Код
=СУММПРОИЗВ(($A$3:$A$158=$A12)*($B$3:$B$158=$B12)*($D$3:$D$158<>"Итого")*($D$3:$D$158<>"Всего")*E$3:E$158)
Лень двигатель прогресса, доказано!!!
 
Уважаемый gling не работает, если в строках выше нет "Итого" см.
E30,E33,E34,E96,E97, почемуто E61 ит.д.

14.03, по подсказке Gling и Сергей в примере убрал
пробелы после Итого и все заработало:idea:
Изменено: СИДОР СИДОРОВ - 14 Мар 2015 11:35:25
 
Да забыл уточнить формула будет работать корректно только если вы приведете к единообразию графы Итого у вас в одном месте "Итого " в других "Итого" т.е. гдето пробел на конце есть где то нет
Лень двигатель прогресса, доказано!!!
 
Цитата
СИДОР СИДОРОВ написал:
Из этой исходной таблицы формируется целый ряд документов путем добавления столбцов справа, где суммируюся данные для решения других задач и суммирование завязано на данные первых четырех граф, поэтому
Непонятно, каким образом написанное Вами можно расценивать как аргумент ПРОТИВ сводной таблицы. Добавляйте, сколько хотите и куда хотите доп. столбцы в свою исходную таблицу. На Сводную это никак не повлияет, она (Сводная) просто берет данные из вашей таблицы и компонует в нужный Вам вид, в т.ч. добавляя промежуточные итоги в нужные места.
Исходя из того, что Вы написали и приложили в виде примера, Сводная - это лучшее решение Вашей задачи.
 
Второй самый простой способ в Вашем случае - это функция =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;[диапазон] )
Изменено: Михаил Лебедев - 13 Мар 2015 10:46:54
 
Тогда так, долго не думая
Код
=СУММПРОИЗВ(($B$3:$B11=$B11)*(E$3:E11))-СУММПРОИЗВ(($B$3:$B11=$B11)*($D$3:$D11="Итого ")*(E$3:E11))
и исправьте ошибки описанные в сообщении #10.
Изменено: gling - 13 Мар 2015 13:23:07
 
Уважаемые Gling и Сергей ваша помощь стала для меня и, думаю многих форумчан, которые делают много всяких списков, той самой библейской «удочкой», которая поможет «ловить рыбу» всю жизнь. Это происходит потому, что все описания и руководства по использованию функций (в частности СУММПРОИЗВ) пишутся для банковских клерков и менеджеров, а не для обычных пользователей, без привязки к другим, не «экономическим», примерам (это идея для хорошей книги!!!, которая  бы значительно увеличила бы количество пользователей EXCEL, попробуйте). Выкладываю пример выполненный с помощью формулы показанной gling 13.03.2015 в сообщении № 7. Это то чего я хотел добиться, но не знал как.
Позвольте поблагодарить и других моих Учителей, в первую очередь конечно же создателя этого сайта Николая Павлова, а так же Vikttur, The_Prist, Serge 007, Михаил С., Казанский, Dophin, Alex_ST, именно их советы и приемы работы я изучаю чаще и больше всего не только здесь, но и на других сайтах.

Тему пока не закрываю – хотелось бы разобраться еще с вариантом
использования ПРОМЕЖУТОЧНЫЕ.ИТОГИ, предложенным Михаилом Лебедевым, почему то
при размножении формулы ниже, она не реагирует на ячейки с промежуточными
итогами, расположенные выше.
В формуле =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;[диапазон]), можно ли кроме аргумента [диапазон] вставить условие, например С3:С7="РуководствоУ", или с использованием СУММПРОИЗВ ?
Изменено: СИДОР СИДОРОВ - 14 Мар 2015 11:21:24
Страницы: 1
Читают тему (гостей: 1)