Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Приемы суммирования в штатном расписании, результат должен находиться внутри диапазона
 
#1
12.03.2015 00:14:17
    Уважаемые, ГУРУ и СЕНСЕИ (без иронии), помогите с формулами суммирования в штатном расписании (EXCEL 2003).
Количество штатных единиц (строк) в подразделениях (диапазонах) всех уровней меняется чуть ли не ежемесячно и очень трудно искать и вносить изменения в формулы.
Попытки создания структуры и промежуточных итогов, использования формул массива только все запутывают.
Мне кажется наиболее простым для понимания решением было бы суммирование по нескольким (более двух) условиям.
Возможно СУММПРОИЗВ, но как их применить в каждой итоговой строке (они окрашены), катастрофически не хватает знаний.
Здесь приведен лишь фрагмент региональной структуры и показаны итоги, которые должны быть.
На самом деле количество строк около 6000.
14.03, по подсказке gling и Сергея, в примере убрал пробелы после Итого.
Изменено: СИДОР СИДОРОВ - 06.04.2015 11:24:14
 
 
 
#2
12.03.2015 00:38:08
А сводная таблица вам не подойдет?
 
 
 
#3
12.03.2015 00:45:41
Вариант сводной.
Изменено: gling - 12.03.2015 00:47:18
 
 
 
#4
12.03.2015 06:50:36
gling написал:
Вариант сводной.
Из этой исходной таблицы формируется целый ряд документов путем добавления столбцов справа, где суммируюся данные для решения других задач и суммирование завязано на данные первых четырех граф, поэтому их порядок менять не желательно.
Но ваша помощь мне очень пригодится для решения других, более мелких задач, и подсказала интересные идеи для решения других задач. Спасибо Вам добрый человек.
 
 
 
#5
12.03.2015 07:57:08
вот вариант формулами он конечно хуже чем сводной так как промежуточные итоги надо выносить в отдельный столбец
Лень двигатель прогресса, доказано!!!
 
 
 
#6
12.03.2015 22:45:55
Ваши формулы хороши и лучше подходят для моих задач, но находятся вне таблицы с данными. И тоже дали много интересных идей. Которые не появлялись у меня потому что я, как говорится, "варился в собственном соку" и пытался несколько лет решить задачу на основе результатов изучения форумов по EXCEL. У меня есть "Любимая" формула для решения таких задач, она находится в зеленых ячейках, приложенного примера "Ш_расп_е ФРАГМЕНТ ПРИМЕР с моей формулой.xls". А  как ее применить в ячейках окрашенных сиреневым цветом я никак не пойму.
 
 
 
#7
13.03.2015 00:26:26
Цитата
окрашенных сиреневым цветом
Попробуйте использовать такую
Код
=СУММПРОИЗВ(($B$3:$B11=$B11)*($D$3:$D11="Итого ")*(E$3:E11))
 
 
 
#8
13.03.2015 06:24:09
можно такую по графе всего копировать в ней ни чего менять не надо
Код
=СУММПРОИЗВ(($A$3:$A$158=$A12)*($B$3:$B$158=$B12)*($D$3:$D$158<>"Итого")*($D$3:$D$158<>"Всего")*E$3:E$158)
Лень двигатель прогресса, доказано!!!
 
 
 
#9
13.03.2015 06:50:05
Уважаемый gling не работает, если в строках выше нет "Итого" см.
E30,E33,E34,E96,E97, почемуто E61 ит.д.

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

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