Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Суммирование ячеек нескольких объединенных столбцов
 
#1
09.06.2015 14:16:11
Добрый день коллеги!
Столкнулся с проблемой при использовании функции СУММЕСЛИ. При работе с горизонтальными массивами, функция "видит" и соотвественно суммирует только крайнюю левую ячейку объединенных столбцов заданного диапазона. Каким образом можно просуммировать все ячейки объединенной области по установленому критерию? Транспонирование данных в моей ситуации не выход. Таблица должна быть именно в таком виде. Пример в приложенном файле. Спасибо!
Изменено: Rustam_ - 09.06.2015 14:17:14
 
 
 
#2
09.06.2015 14:24:49
Мой вариант:

=СУММЕСЛИ($C$2:$J$2;A6;$C$4:$J$4)+СУММЕСЛИ($C$2:$J$2;A6;$D$4:$J$4)
 
 
 
#3
09.06.2015 14:30:17
Цитата
Rustam_ написал:
функция "видит" и соотвественно суммирует только крайнюю левую ячейку объединенных столбцов заданного диапазона
Она все видит. Просто все остальные ячейки, кроме левой верхней - пустые. Это фишка объединенных ячеек, причем документированная и не раз обсуждаемая. И даже Excel-ем выдаваемая, когда пытаетесь объединить несколько ячеек с данными.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#4
09.06.2015 14:35:56
Спасибо! Практичное решение, считает!  
 
 
 
#5
09.06.2015 14:54:39
Формула массива:
=СУММ((A6=ПРОСМОТР(СТОЛБЕЦ($C$2:$J$2);ЕСЛИ($C$2:$J$2>0;СТОЛБЕЦ($C$2:$J$2));$C$2:$J$2))*$C$4:$J$4)
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#6
09.06.2015 14:59:13
Владимир, спасибо! Это более универсальное решение с учетом объема данных в моем случае. Правда не совсем разобрался в алгоритме еще.
 
 
 
#7
09.06.2015 15:01:19
Это универсальное решение производит намного больше вычислений. Хотя, если число столбцов у каждого месяца разное, то хорошо.
Учитесь оптимальнее использовать ресурсы.

Вот Вам еще "универсальная":
=СУММ(СМЕЩ($C$4;;ПОИСКПОЗ(A6;$C$2:$J$2;)-1;1;2))
Не советую. Формула содержит функцию СМЕЩ, которая пересчитывается при любом изменении листа.

Самое оптимальное - решение с двумя СУММЕСЛИ
 
 
 
#8
09.06.2015 15:06:32
Цитата
vikttur написал: Самое оптимальное - решение с двумя СУММЕСЛИ
Самое оптимальное - разъединить ячейки и заполнить. Тогда одной СУММЕСЛИ за глаза хватит...
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#9
09.06.2015 15:09:03
Правильно!

Цитата
с учетом объема данных в моем случае
Если подразумевается большее количество столбцов (одинаковое для каждого месяца):
=СУММ(ИНДЕКС($C$4:$K$4;ПОИСКПОЗ(A6;$C$2:$K$2;)):ИНДЕКС($C$4:$K$4;ПОИСКПОЗ(A6;$C$2:$K$2;)+2))
Число в конце формулы - количество столбцов (Поиск+2)
 
 
 
#10
09.06.2015 15:11:12
Ребята, всем большое спасибо за доброе сердце и оперативность.
Все советы полезны и применимы, буду подстраиваться под условия, в т.ч. при использовании ресурсов, т.к. еще только начал познавать возможности и хитрости Excel.
Думаю для читающих форум, тема раскрыта. Особенно для тех, кому часто приходиться "выгружать" данные из "1С" с последующей трансформацией бухгалтерского учета в управленческий.

Всем удачи!
 
 
 
#11
09.06.2015 15:19:27
Цитата
The_Prist написал: Самое оптимальное - разъединить ячейки и заполнить.
Это трудоемкий процесс. Таблицы с которыми приходиться работать оооочень массивные. Иначе их формировать не позволяет существующая настройка "программы-донора" данных.  
 
 
 
#12
09.06.2015 15:34:43
Для полноты освещения вопроса - не всегда все остальные ячейки, кроме левой верхней - пустые. Есть способ заполнить их все, любыми значениями. Хотя видно будет только одну, но формулы вытянут все значения.
Да и в Вашем файле можно сделать, ничем не меняя вид таблицы - сделал, Ваши формулы считают. Вот только файл показать сейчас не могу.
И кстати то, что файлы огромные и вручную преобразовать их долго - можно пробовать решить макросом (в зависимости от конкретного случая конечно).
Изменено: Hugo - 09.06.2015 15:42:54
 
 
 
#13
25.06.2015 15:58:12
Цитата
vikttur написал:
=СУММ(ИНДЕКС($C$4:$K$4;ПОИСКПОЗ(A6;$C$2:$K$2;)):ИНДЕКС($C$4:$K$4;ПОИСКПОЗ(A6;$C$2:$K$2;)+2))
Добрый день коллеги!
Вышеуказаная формула более универсальная, однако, если в массиве есть пустые ячейки, сотвественно формула возвращает значение "Н/Д", которое не суммируется обычной функцией, поэтому немного усовершенствовал формулу, с учетом инструментов по обходу этого недостатка:

=СУММ(ЕСЛИ(ЕНД(ИНДЕКС($C$4:$K$4;ПОИСКПОЗ(A6;$C$2:$K$2;)):ИНДЕКС($C$4:$K$4;ПОИСКПОЗ(A6;$C$2:$K$2;)+2));0;СУММ(ИНДЕКС($C$4:$K$4;ПОИСКПОЗ(A6;$C$2:$K$2;)):ИНДЕКС($C$4:$K$4;ПОИСКПОЗ(A6;$C$2:$K$2;)+2))))
 
 
 
#14
25.06.2015 16:03:19
Для младших версий Excel (от 2007-го), можно проще
Код
=ЕСЛИОШИБКА(СУММ(ИНДЕКС($C$4:$K$4;ПОИСКПОЗ(A6;$C$2:$K$2;)):ИНДЕКС($C$4:$K$4;ПОИСКПОЗ(A6;$C$2:$K$2;)+2));0)
Согласие есть продукт при полном непротивлении сторон
 
 
 
#15
25.06.2015 16:09:12
Цитата
если в массиве есть пустые ячейки, сотвественно формула возвращает значение "Н/Д"
Сомневаюсь я, однако. Похоже, там не пусто, но пустая текстовая строка - ""
 
 
 
Страницы: 1
Читают тему
Наверх