Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Суммирование по критерию без учёта скрытых ячеек - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Суммирование по критерию без учёта скрытых ячеек (Формулы/Formulas)
Суммирование по критерию без учёта скрытых ячеек
Leariska Дата: Понедельник, 20.11.2017, 12:28 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

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


Сообщение отредактировал Leariska - Понедельник, 20.11.2017, 12:28
 
Ответить
СообщениеДобрый день!
Пытаюсь создать таблицу, где производилось бы суммирование по критерию из другого столбца. Это легко сделать с помощью СУММЕСЛИ. Но не могу сделать, если часть строк скрывается. Необходимо, чтобы скрытые строки в расчёте не участвовали. Понимаю, что там должны быть СУММПРОИЗВ и ПРОМЕЖУТОЧНЫЕ.ИТОГИ, но не знаю, как написать формулу.
Пожалуйста, помогите.
Автор - Leariska
Дата добавления - 20.11.2017 в 12:28
_Boroda_ Дата: Понедельник, 20.11.2017, 13:25 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
Код
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;ДВССЫЛ("E"&СТРОКА(Таблица3[ПОСТУПЛЕНИЕ
МАТЕРИАЛА])))*(Таблица3[ТИП
СДЕЛКИ]=[@[ТИП СДЕЛКИ]]))

[vba]
Код
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;ДВССЫЛ("E"&СТРОКА(Таблица3[ПОСТУПЛЕНИЕ
МАТЕРИАЛА])))*(Таблица3[ТИП
СДЕЛКИ]=[@[ТИП СДЕЛКИ]]))
[/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
 
Ответить
СообщениеТак нужно?
Код
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;ДВССЫЛ("E"&СТРОКА(Таблица3[ПОСТУПЛЕНИЕ
МАТЕРИАЛА])))*(Таблица3[ТИП
СДЕЛКИ]=[@[ТИП СДЕЛКИ]]))

[vba]
Код
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;ДВССЫЛ("E"&СТРОКА(Таблица3[ПОСТУПЛЕНИЕ
МАТЕРИАЛА])))*(Таблица3[ТИП
СДЕЛКИ]=[@[ТИП СДЕЛКИ]]))
[/vba]
Или Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter
[vba]
Код
=СУММ(ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;СМЕЩ(E$1;СТРОКА(Таблица3[КОНТРАГЕНТ])-1;))*(Таблица3[ТИП
СДЕЛКИ]=[@[ТИП СДЕЛКИ]]);Таблица3[ПОСТУПЛЕНИЕ
МАТЕРИАЛА]))
[/vba]
Автор - _Boroda_
Дата добавления - 20.11.2017 в 13:25
Leariska Дата: Понедельник, 20.11.2017, 16:27 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, спасибо! Это именно то, что требовалось.
 
Ответить
Сообщение_Boroda_, спасибо! Это именно то, что требовалось.
Автор - Leariska
Дата добавления - 20.11.2017 в 16:27
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
 
Ответить
СообщениеВсем добрый день, ситуация схожая. Только в лист на котором фильтруются данные (так называемая умная таблица) ежедневно обновляется путем подключения к БД Acсess, и соответственно добавляются новые строки. Дак вот данная формула постоянно смещается при обновлении данных и добавлении новых строк. Подскажите пожалуйста можно ли придумать какую то формулу которая не будет смещатся.
Код
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(Таблица_БД.accdb11[[#Заголовки];[Расход]];СТРОКА('Прочие расходы'!$1:$587);0))*('Прочие расходы'!$H$2:$H$588=A3))

Конкретно смещаются вот эти цифры:
Код
*('Прочие расходы'!$H$2:$H$588=A3))

Т.е. если добавится в таблицу 5 строк то будет
Код
*('Прочие расходы'!$H$2:$H$593=A3))
и формула соответственно не работает.
Автор - HardPutin
Дата добавления - 24.01.2019 в 14:21
_Boroda_ Дата: Четверг, 24.01.2019, 14:59 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
[vba]
Код
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(Таблица_БД.accdb11[[#Заголовки];[Расход]];СТРОКА(Таблица_БД.accdb11[Наименование])-1;0))*(Таблица_БД.accdb11[Объект]=A3))
[/vba]
К сообщению приложен файл: 8130410_1.xlsx (55.6 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?
[vba]
Код
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(Таблица_БД.accdb11[[#Заголовки];[Расход]];СТРОКА(Таблица_БД.accdb11[Наименование])-1;0))*(Таблица_БД.accdb11[Объект]=A3))
[/vba]
Автор - _Boroda_
Дата добавления - 24.01.2019 в 14:59
HardPutin Дата: Четверг, 24.01.2019, 15:11 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Цитата
Так нужно?

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


Сообщение отредактировал HardPutin - Четверг, 24.01.2019, 15:31
 
Ответить
Сообщение
Цитата
Так нужно?

Это гениально! Спасибо огромное!)
Автор - HardPutin
Дата добавления - 24.01.2019 в 15:11
phenonim Дата: Четверг, 21.04.2022, 14:43 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Срисовал у Вас тут формулу, но не понимаю почему она не хочет правильно работать - врет зараза и считает что то свое, а не мое.... Файлик прикреплю с примером. Что нужно:
при фильтрации или скрытии ненужных строк во второй таблице (Основа), суммировалась сумма количества и цены в первой таблице (Подсчет) по подразделению исполнителю.
Подскажите пожалуйста что я не правильно сделал. Спасибо огромное заранее.
К сообщению приложен файл: ___.xls (487.0 Kb)
 
Ответить
СообщениеСрисовал у Вас тут формулу, но не понимаю почему она не хочет правильно работать - врет зараза и считает что то свое, а не мое.... Файлик прикреплю с примером. Что нужно:
при фильтрации или скрытии ненужных строк во второй таблице (Основа), суммировалась сумма количества и цены в первой таблице (Подсчет) по подразделению исполнителю.
Подскажите пожалуйста что я не правильно сделал. Спасибо огромное заранее.
Автор - phenonim
Дата добавления - 21.04.2022 в 14:43
_Boroda_ Дата: Четверг, 21.04.2022, 15:04 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Почти все правильно
Код
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;СМЕЩ($H$15;СТРОКА($C$1:$C$67)-1;0))*($A$15:$A$81=$A2))

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПочти все правильно
Код
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;СМЕЩ($H$15;СТРОКА($C$1:$C$67)-1;0))*($A$15:$A$81=$A2))
Автор - _Boroda_
Дата добавления - 21.04.2022 в 15:04
phenonim Дата: Четверг, 21.04.2022, 15:31 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

_Boroda_, СПАСИБО ВАМ ОГРОМНОЕ!!!
 
Ответить
Сообщение_Boroda_, СПАСИБО ВАМ ОГРОМНОЕ!!!
Автор - phenonim
Дата добавления - 21.04.2022 в 15:31
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)
 
Ответить
СообщениеДобрый день! Удачно наткнулся на данную тему. Exel функционально стал осваивать не давно, и большей частью стараюсь оптимизировать рабочее время. Возник аналогичный вопрос, как произвести подсчет с учетом фильтра, к примеру формула такакая:
Код
=СУММЕСЛИ(R[-155]C[-1]:R[-3]C[-1];"Пушистых слонов";R[-155]C:R[-3]C)
Автор - Bup0H
Дата добавления - 23.04.2022 в 13:55
Pelena Дата: Суббота, 23.04.2022, 17:38 | Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 19188
Репутация: 4421 ±
Замечаний: ±

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

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Без файла с примером только общий ответ. Посмотрите готовое решение
Подсчёт суммы и количества отфильтрованных ячеек с учётом условия
Автор - Pelena
Дата добавления - 23.04.2022 в 17:38
Bup0H Дата: Суббота, 23.04.2022, 18:53 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

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

upd. Что то не получается. Подсчет внизу, где итог, фильтр по номеру "Блока", пустые пробелы тоже учитывал.
К сообщению приложен файл: 4179866.xlsx (19.4 Kb)


Сообщение отредактировал Bup0H - Суббота, 23.04.2022, 19:06
 
Ответить
СообщениеСпасибо! Вижу что в примере все отлично работает, не могу понять только формулу начиная со "Смещ". Попробую разобраться.

upd. Что то не получается. Подсчет внизу, где итог, фильтр по номеру "Блока", пустые пробелы тоже учитывал.
Автор - Bup0H
Дата добавления - 23.04.2022 в 18:53
Pelena Дата: Суббота, 23.04.2022, 19:39 | Сообщение № 13
Группа: Админы
Ранг: Местный житель
Сообщений: 19188
Репутация: 4421 ±
Замечаний: ±

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
 
Ответить
Сообщение[vba]
Код
=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(Таблица1417[[#Заголовки];[Кол-во единиц]];СТРОКА(Таблица1417)-СТРОКА(Таблица1417[[#Заголовки];[1.]]);0))*ЕЧИСЛО(ПОИСК(E176;Таблица1417[Техническое обслуживание шкафов защит 1Т, 2Т, 3Т, ТСН-1, ТСН-2, Т-10 на базе микропроцессорных устройств RET 670 производства фирмы ABB:])))
[/vba]
Автор - Pelena
Дата добавления - 23.04.2022 в 19:39
Bup0H Дата: Воскресенье, 24.04.2022, 11:18 | Сообщение № 14
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

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

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

Если у Вас еще найдется время, не могли бы вы объяснить некоторые части формулы, я хочу понять ее а не слепо переписать.
Автор - Bup0H
Дата добавления - 24.04.2022 в 11:18
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Суммирование по критерию без учёта скрытых ячеек (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!