Суммирование по критерию без учёта скрытых ячеек

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Суммирование по критерию без учёта скрытых ячеек (Формулы/Formulas)

Leariska

Дата: Понедельник, 20.11.2017, 12:28 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 7

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

Добрый день!
Пытаюсь создать таблицу, где производилось бы суммирование по критерию из другого столбца. Это легко сделать с помощью СУММЕСЛИ. Но не могу сделать, если часть строк скрывается. Необходимо, чтобы скрытые строки в расчёте не участвовали. Понимаю, что там должны быть СУММПРОИЗВ и ПРОМЕЖУТОЧНЫЕ.ИТОГИ, но не знаю, как написать формулу.
Пожалуйста, помогите.

К сообщению приложен файл: 0779538.xlsx (11.7 Kb)

Сообщение отредактировал Leariska - Понедельник, 20.11.2017, 12:28

Ответить

_Boroda_

Дата: Понедельник, 20.11.2017, 13:25 | Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 16711

Репутация: 6502 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Так нужно?

Код

=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;ДВССЫЛ("E"&СТРОКА(Таблица3[ПОСТУПЛЕНИЕ
МАТЕРИАЛА])))*(Таблица3[ТИП
СДЕЛКИ]=[@[ТИП СДЕЛКИ]]))

[vba]

Код

[/vba]
Или Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter
[vba]

Код

=СУММ(ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ(E$1;СТРОКА(Таблица3[КОНТРАГЕНТ])-1;))*(Таблица3[ТИП
СДЕЛКИ]=[@[ТИП СДЕЛКИ]]);Таблица3[ПОСТУПЛЕНИЕ
МАТЕРИАЛА]))

[/vba]

К сообщению приложен файл: 0779538_2.xlsx (12.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Leariska

Дата: Понедельник, 20.11.2017, 16:27 | Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 7

Репутация: 0 ±

Замечаний: 0% ±

Excel 2010

_Boroda_, спасибо! Это именно то, что требовалось.

Ответить

HardPutin

Дата: Четверг, 24.01.2019, 14:21 | Сообщение № 4

Группа: Пользователи

Ранг: Прохожий

Сообщений: 2

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Всем добрый день, ситуация схожая. Только в лист на котором фильтруются данные (так называемая умная таблица) ежедневно обновляется путем подключения к БД Acсess, и соответственно добавляются новые строки. Дак вот данная формула постоянно смещается при обновлении данных и добавлении новых строк. Подскажите пожалуйста можно ли придумать какую то формулу которая не будет смещатся.

Код

=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(Таблица_БД.accdb11[[#Заголовки];[Расход]];СТРОКА('Прочие расходы'!$1:$587);0))*('Прочие расходы'!$H$2:$H$588=A3))

Конкретно смещаются вот эти цифры:

Код

*('Прочие расходы'!$H$2:$H$588=A3))

Т.е. если добавится в таблицу 5 строк то будет

Код

*('Прочие расходы'!$H$2:$H$593=A3))

и формула соответственно не работает.

К сообщению приложен файл: 8130410.xlsx (55.5 Kb)

Сообщение отредактировал HardPutin - Четверг, 24.01.2019, 14:23

Ответить

_Boroda_

Дата: Четверг, 24.01.2019, 14:59 | Сообщение № 5

Группа: Админы

Ранг: Местный житель

Сообщений: 16711

Репутация: 6502 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Так нужно?
[vba]

Код

=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(Таблица_БД.accdb11[[#Заголовки];[Расход]];СТРОКА(Таблица_БД.accdb11[Наименование])-1;0))*(Таблица_БД.accdb11[Объект]=A3))

[/vba]

К сообщению приложен файл: 8130410_1.xlsx (55.6 Kb)

Ответить

HardPutin

Дата: Четверг, 24.01.2019, 15:11 | Сообщение № 6

Группа: Пользователи

Ранг: Прохожий

Сообщений: 2

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Цитата

Так нужно?

Это гениально! Спасибо огромное!)

Сообщение отредактировал HardPutin - Четверг, 24.01.2019, 15:31

Ответить

phenonim

Дата: Четверг, 21.04.2022, 14:43 | Сообщение № 7

Группа: Пользователи

Ранг: Прохожий

Сообщений: 2

Репутация: 0 ±

Замечаний: 0% ±

Срисовал у Вас тут формулу, но не понимаю почему она не хочет правильно работать - врет зараза и считает что то свое, а не мое.... Файлик прикреплю с примером. Что нужно:
при фильтрации или скрытии ненужных строк во второй таблице (Основа), суммировалась сумма количества и цены в первой таблице (Подсчет) по подразделению исполнителю.
Подскажите пожалуйста что я не правильно сделал. Спасибо огромное заранее.

К сообщению приложен файл: ___.xls (487.0 Kb)

Ответить

_Boroda_

Дата: Четверг, 21.04.2022, 15:04 | Сообщение № 8

Группа: Админы

Ранг: Местный житель

Сообщений: 16711

Репутация: 6502 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Почти все правильно

Код

=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;СМЕЩ($H$15;СТРОКА($C$1:$C$67)-1;0))*($A$15:$A$81=$A2))

Ответить

phenonim

Дата: Четверг, 21.04.2022, 15:31 | Сообщение № 9

Группа: Пользователи

Ранг: Прохожий

Сообщений: 2

Репутация: 0 ±

Замечаний: 0% ±

_Boroda_, СПАСИБО ВАМ ОГРОМНОЕ!!!

Ответить

Bup0H

Дата: Суббота, 23.04.2022, 13:55 | Сообщение № 10

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

Добрый день! Удачно наткнулся на данную тему. Exel функционально стал осваивать не давно, и большей частью стараюсь оптимизировать рабочее время. Возник аналогичный вопрос, как произвести подсчет с учетом фильтра, к примеру формула такакая:

Код

=СУММЕСЛИ(R[-155]C[-1]:R[-3]C[-1];"Пушистых слонов";R[-155]C:R[-3]C)

Ответить

Pelena

Дата: Суббота, 23.04.2022, 17:38 | Сообщение № 11

Группа: Админы

Ранг: Местный житель

Сообщений: 19401

Репутация: 4549 ±

Замечаний: ±

Excel 365 & Mac Excel

Здравствуйте.
Без файла с примером только общий ответ. Посмотрите готовое решение
Подсчёт суммы и количества отфильтрованных ячеек с учётом условия

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Bup0H

Дата: Суббота, 23.04.2022, 18:53 | Сообщение № 12

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

Спасибо! Вижу что в примере все отлично работает, не могу понять только формулу начиная со "Смещ". Попробую разобраться.

upd. Что то не получается. Подсчет внизу, где итог, фильтр по номеру "Блока", пустые пробелы тоже учитывал.

К сообщению приложен файл: 4179866.xlsx (19.4 Kb)

Сообщение отредактировал Bup0H - Суббота, 23.04.2022, 19:06

Ответить

Pelena

Дата: Суббота, 23.04.2022, 19:39 | Сообщение № 13

Группа: Админы

Ранг: Местный житель

Сообщений: 19401

Репутация: 4549 ±

Замечаний: ±

Excel 365 & Mac Excel

[vba]

Код

=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(Таблица1417[[#Заголовки];[Кол-во единиц]];СТРОКА(Таблица1417)-СТРОКА(Таблица1417[[#Заголовки];[1.]]);0))*ЕЧИСЛО(ПОИСК(E176;Таблица1417[Техническое обслуживание шкафов защит 1Т, 2Т, 3Т, ТСН-1, ТСН-2, Т-10 на базе микропроцессорных устройств RET 670 производства фирмы ABB:])))

[/vba]

К сообщению приложен файл: 2808707.xlsx (20.6 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

Bup0H

Дата: Воскресенье, 24.04.2022, 11:18 | Сообщение № 14

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

Спасибо большое, что помогаете другим людям!

Если у Вас еще найдется время, не могли бы вы объяснить некоторые части формулы, я хочу понять ее а не слепо переписать.

Ответить

Суммирование по критерию без учёта скрытых ячеек (Формулы/Formulas)

Страница 1 из 1
1