Aurum
Дата: Пятница, 26.04.2019, 08:36 |
Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация:
0
±
Замечаний:
0% ±
Excel 2013
Добрый день! Помогите, пожалуйста, посчитать сумму формулой. Что-то нет идей( Есть таблица вида: Группа 1 -А --1 --2 -В --1 --2 Группа 2 -А --1 --2 -В --1 --2 И так далее. Нужно посчитать все продажи по подгруппам А и В в отдельной ячейке. Проблема в том, что в самих подгруппах А и В нет сумм, а значения 1 и 2 есть и в той и другой подгруппе (таблицу прилагаю)
Добрый день! Помогите, пожалуйста, посчитать сумму формулой. Что-то нет идей( Есть таблица вида: Группа 1 -А --1 --2 -В --1 --2 Группа 2 -А --1 --2 -В --1 --2 И так далее. Нужно посчитать все продажи по подгруппам А и В в отдельной ячейке. Проблема в том, что в самих подгруппах А и В нет сумм, а значения 1 и 2 есть и в той и другой подгруппе (таблицу прилагаю) Aurum
Ответить
Сообщение Добрый день! Помогите, пожалуйста, посчитать сумму формулой. Что-то нет идей( Есть таблица вида: Группа 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
Группа: Админы
Ранг: Местный житель
Сообщений: 19188
Репутация:
4421
±
Замечаний:
±
Excel 365 & Mac Excel
Здравствуйте. Вариант с доп. столбцом
Здравствуйте. Вариант с доп. столбцом Pelena
"Черт возьми, Холмс! Но как??!!" Ю-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) формула работать не будет.
Вариант без доп. ячеек. Для 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4110
Репутация:
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;))
массивно-летучийКод
=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
Замечательный Временно просто медведь , процентов на 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)
Светлый
Программировать проще, чем писать стихи.
Ответить
Сообщение Немного поколдовал над формулой 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4110
Репутация:
768
±
Замечаний:
0% ±
Excel 2013/2016
[offtop]Неожиданное для меня решение
Сам удивился :-) . Часто замечаю что формульные алгоритмы мне мешают при работе с VBA, и наоборот, а к задаче часто надо вернутся с чистого листа. Как в этот раз, посмотрел на Вашу летучую и с ограничением, убрал ограничения, но летучесть осталась. Пошел по делам, вернулся и сделал второе, совершенно на другом принципе, да еще и ошибку свою выявил в первой. [/offtop]
[offtop]Неожиданное для меня решение
Сам удивился :-) . Часто замечаю что формульные алгоритмы мне мешают при работе с VBA, и наоборот, а к задаче часто надо вернутся с чистого листа. Как в этот раз, посмотрел на Вашу летучую и с ограничением, убрал ограничения, но летучесть осталась. Пошел по делам, вернулся и сделал второе, совершенно на другом принципе, да еще и ошибку свою выявил в первой. [/offtop] bmv98rus
Замечательный Временно просто медведь , процентов на 20 .
Ответить
Сообщение [offtop]Неожиданное для меня решение
Сам удивился :-) . Часто замечаю что формульные алгоритмы мне мешают при работе с VBA, и наоборот, а к задаче часто надо вернутся с чистого листа. Как в этот раз, посмотрел на Вашу летучую и с ограничением, убрал ограничения, но летучесть осталась. Пошел по делам, вернулся и сделал второе, совершенно на другом принципе, да еще и ошибку свою выявил в первой. [/offtop] Автор - bmv98rus Дата добавления - 07.05.2019 в 10:23