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

Вход

Регистрация

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

 

= Мир MS Excel/Подсчет суммы ячеек без id - Мир MS Excel

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

Excel 2013
Добрый день!
Помогите, пожалуйста, посчитать сумму формулой. Что-то нет идей(
Есть таблица вида:

Группа 1
-А
--1
--2
-В
--1
--2

Группа 2
-А
--1
--2
-В
--1
--2
И так далее. Нужно посчитать все продажи по подгруппам А и В в отдельной ячейке.
Проблема в том, что в самих подгруппах А и В нет сумм, а значения 1 и 2 есть и в той и другой подгруппе (таблицу прилагаю)
К сообщению приложен файл: 5736575.xls (27.5 Kb)
 
Ответить
СообщениеДобрый день!
Помогите, пожалуйста, посчитать сумму формулой. Что-то нет идей(
Есть таблица вида:

Группа 1
-А
--1
--2
-В
--1
--2

Группа 2
-А
--1
--2
-В
--1
--2
И так далее. Нужно посчитать все продажи по подгруппам А и В в отдельной ячейке.
Проблема в том, что в самих подгруппах А и В нет сумм, а значения 1 и 2 есть и в той и другой подгруппе (таблицу прилагаю)
Автор - Aurum
Дата добавления - 26.04.2019 в 08:36
Pelena Дата: Пятница, 26.04.2019, 08:47 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19176
Репутация: 4413 ±
Замечаний: ±

Excel 365 & Mac Excel
Здравствуйте.
Вариант с доп. столбцом
К сообщению приложен файл: 4295541.xls (29.5 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Вариант с доп. столбцом
Автор - Pelena
Дата добавления - 26.04.2019 в 08:47
Aurum Дата: Воскресенье, 05.05.2019, 08:56 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Спасибо за совет!
 
Ответить
СообщениеСпасибо за совет!
Автор - Aurum
Дата добавления - 05.05.2019 в 08:56
Светлый Дата: Воскресенье, 05.05.2019, 10:39 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Вариант без доп. ячеек. Для A:
Код
=СУММПРОИЗВ((СЧЁТЕСЛИ(СМЕЩ(A$1;;;СТРОКА($1:$98));E$3)>СЧЁТЕСЛИ(СМЕЩ(A$1;;;СТРОКА($1:$98));E$4))*B$2:B$99)
Для B:
Код
=СУММПРОИЗВ((СЧЁТЕСЛИ(СМЕЩ(A$1;;;СТРОКА($1:$98));E$3)=СЧЁТЕСЛИ(СМЕЩ(A$1;;;СТРОКА($1:$98));E$4))*B$2:B$99)
*Или протягиваемая формула:
Код
=СУММПРОИЗВ((СЧЁТЕСЛИ(СМЕЩ(A$1;;;СТРОКА($1:$98));E$3)-СЧЁТЕСЛИ(СМЕЩ(A$1;;;СТРОКА($1:$98));E$4)=2-СТРОКА(E1))*B$2:B$99)
**В каждой группе обязательно должны быть A и B именно в таком порядке. Если будут другие подгруппы (C, D, E) формула работать не будет.

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Воскресенье, 05.05.2019, 11:05
 
Ответить
СообщениеВариант без доп. ячеек. Для A:
Код
=СУММПРОИЗВ((СЧЁТЕСЛИ(СМЕЩ(A$1;;;СТРОКА($1:$98));E$3)>СЧЁТЕСЛИ(СМЕЩ(A$1;;;СТРОКА($1:$98));E$4))*B$2:B$99)
Для B:
Код
=СУММПРОИЗВ((СЧЁТЕСЛИ(СМЕЩ(A$1;;;СТРОКА($1:$98));E$3)=СЧЁТЕСЛИ(СМЕЩ(A$1;;;СТРОКА($1:$98));E$4))*B$2:B$99)
*Или протягиваемая формула:
Код
=СУММПРОИЗВ((СЧЁТЕСЛИ(СМЕЩ(A$1;;;СТРОКА($1:$98));E$3)-СЧЁТЕСЛИ(СМЕЩ(A$1;;;СТРОКА($1:$98));E$4)=2-СТРОКА(E1))*B$2:B$99)
**В каждой группе обязательно должны быть A и B именно в таком порядке. Если будут другие подгруппы (C, D, E) формула работать не будет.
Автор - Светлый
Дата добавления - 05.05.2019 в 10:39
bmv98rus Дата: Воскресенье, 05.05.2019, 12:11 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
массивно-летучий
Код
=SUM(IFERROR(SUBTOTAL(9;OFFSET($B$1;IF($A$1:$A$99=E3;ROW($A$1:$A$99));;IFERROR(-LOOKUP(IF($A$1:$A$99=E3;-ROW($A$1:$A$99)-1);SMALL(IF(ISTEXT($A$1:$A$99);-ROW($A$1:$A$99));ROW($A$1:$A$99)))-1 -IF($A$1:$A$99=E3;ROW($A$1:$A$99));)));))

Массивно и не летуче, да и без ошибки, которая была выше допущена
Код
=SUM(IFERROR((MATCH(ROW($B$1:$B$99);IF($A$1:$A$99=E3;ROW($A$1:$A$99)))=MATCH(ROW($B$1:$B$99);IF(ISTEXT($A$1:$A$99);ROW($A$1:$A$99)) ))*$B$1:$B$99;))
К сообщению приложен файл: Copy_of_904.xlsx (9.0 Kb)

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Воскресенье, 05.05.2019, 14:59
 
Ответить
Сообщениемассивно-летучий
Код
=SUM(IFERROR(SUBTOTAL(9;OFFSET($B$1;IF($A$1:$A$99=E3;ROW($A$1:$A$99));;IFERROR(-LOOKUP(IF($A$1:$A$99=E3;-ROW($A$1:$A$99)-1);SMALL(IF(ISTEXT($A$1:$A$99);-ROW($A$1:$A$99));ROW($A$1:$A$99)))-1 -IF($A$1:$A$99=E3;ROW($A$1:$A$99));)));))

Массивно и не летуче, да и без ошибки, которая была выше допущена
Код
=SUM(IFERROR((MATCH(ROW($B$1:$B$99);IF($A$1:$A$99=E3;ROW($A$1:$A$99)))=MATCH(ROW($B$1:$B$99);IF(ISTEXT($A$1:$A$99);ROW($A$1:$A$99)) ))*$B$1:$B$99;))
Автор - bmv98rus
Дата добавления - 05.05.2019 в 12:11
Светлый Дата: Понедельник, 06.05.2019, 23:07 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Немного поколдовал над формулой bmv98rus. Неожиданное для меня решение. Взял на заметку. Массивная:
Код
=СУММ(ЕСЛИОШИБКА(ПОИСКПОЗ(СТРОКА(B2:B99);ЕСЛИ(A2:A99=E3;СТРОКА(2:99)))=ПОИСКПОЗ(СТРОКА(2:99);ЕСЛИ(B2:B99="";СТРОКА(2:99)));)*B2:B99)

Программировать проще, чем писать стихи.
 
Ответить
СообщениеНемного поколдовал над формулой bmv98rus. Неожиданное для меня решение. Взял на заметку. Массивная:
Код
=СУММ(ЕСЛИОШИБКА(ПОИСКПОЗ(СТРОКА(B2:B99);ЕСЛИ(A2:A99=E3;СТРОКА(2:99)))=ПОИСКПОЗ(СТРОКА(2:99);ЕСЛИ(B2:B99="";СТРОКА(2:99)));)*B2:B99)
Автор - Светлый
Дата добавления - 06.05.2019 в 23:07
bmv98rus Дата: Вторник, 07.05.2019, 10:23 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
[offtop]
Цитата Светлый, 06.05.2019 в 23:07, в сообщении № 6 ()
Неожиданное для меня решение
Сам удивился :-) . Часто замечаю что формульные алгоритмы мне мешают при работе с VBA, и наоборот, а к задаче часто надо вернутся с чистого листа. Как в этот раз, посмотрел на Вашу летучую и с ограничением, убрал ограничения, но летучесть осталась. Пошел по делам, вернулся и сделал второе, совершенно на другом принципе, да еще и ошибку свою выявил в первой.
[/offtop]

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение[offtop]
Цитата Светлый, 06.05.2019 в 23:07, в сообщении № 6 ()
Неожиданное для меня решение
Сам удивился :-) . Часто замечаю что формульные алгоритмы мне мешают при работе с VBA, и наоборот, а к задаче часто надо вернутся с чистого листа. Как в этот раз, посмотрел на Вашу летучую и с ограничением, убрал ограничения, но летучесть осталась. Пошел по делам, вернулся и сделал второе, совершенно на другом принципе, да еще и ошибку свою выявил в первой.
[/offtop]
Автор - bmv98rus
Дата добавления - 07.05.2019 в 10:23
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет суммы ячеек без id (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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