Подсчет общего количества одинаковых ячеек внутри групп

Дата: Среда, 05.12.2018, 18:39 | Сообщение № 1

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

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

Сообщений: 4

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

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

Excel 2016

Есть задача автоматизированно считать "Общее количество" изделий. Изделия находятся в группах. Одно и то же изделие может находится в разных группах и в разных количествах. Этих групп также может быть несколько, и они тоже могут входить в различные более крупные группы.
Приложен файл с примером таблицы (содержание примера только отражает суть задачи, таблица в которую необходимо внедрить решение в разы больше и с другими данными). В этом файле вручную (через формулу) посчитано количество каждого изделия которое необходимо получить в столбце "Общее количество".
Подскажите, пожалуйста возможно ли написать формулу в столбце "Общее количество" которая будет выводить нужное значение?

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

Дата: Среда, 05.12.2018, 19:01 | Сообщение № 2

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

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

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

Excel 2013, 2016

И Вам, здравствуйте!
Кроме "СКЛАД" и "ПОЛКА" есть какие-то другие группирующие признаки?
*Ассортимент для полка 1 и полка 2 может отличаться?

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

Сообщение отредактировал Светлый - Среда, 05.12.2018, 19:13

Дата: Среда, 05.12.2018, 19:54 | Сообщение № 3

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

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

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

Excel 2013, 2016

Массивная формула в доп. столбце:

Код

=ЕСЛИ(ЛЕВБ(G4;5)="склад";1;ЕСЛИ(ЛЕВБ(G4;5)="полка";ИНДЕКС(H:H;МАКС((ЛЕВБ(G$1:G3;5)="склад")*СТРОКА($1:3)));ИНДЕКС(H:H;МАКС((ЛЕВБ(G$1:G3;5)="полка")*СТРОКА($1:3)))*ИНДЕКС(H:H;МАКС((ЛЕВБ(G$1:G3;5)="склад")*СТРОКА($1:3)))))*H4

и основная формула:

Код

=СУММЕСЛИ(G$4:G$21;G4;J$4:J$21)

К сообщению приложен файл: 5352653-1.xlsx (14.0 Kb)

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

Дата: Четверг, 06.12.2018, 10:58 | Сообщение № 4

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

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

Сообщений: 4

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

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

Excel 2016

Светлый, Здравствуйте.
Спасибо за помощь.
Пример, приведенный мной, оказался не очень удачным. Группирующие признаки могут быть абсолютно разными.
То есть помимо "Полка" и "Склад" могут быть абсолютно любые группирующие признаки.

Pelena

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

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

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

Сообщений: 19174

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

Замечаний: ±

Excel 365 & Mac Excel

Если "склад" и "полка" условные названия, то можно привязаться к уровням группировки(пользовательская функция Уровень), а затем уже использовать формулу от Светлого. Макросы должны быть разрешены

К сообщению приложен файл: 5352653.xlsm (18.1 Kb)

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

Дата: Четверг, 06.12.2018, 12:24 | Сообщение № 6

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

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

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

Excel 2013, 2016

Покажите, что может быть в группах, или задайте признак уровня группы. Уровень склад 1_, Уровень полка 2_. А пока массивная (Ctrl+Shift+Enter) формула без оптимизации:Код
=СУММ((A$4:A$21=A4)*Ч(СМЕЩ(B$3;LOG(МУМНОЖ(--(СТРОКА($4:$21)>=ТРАНСП(СТРОКА($4:$21)));2^СТРОКА($1:$18)*(ЛЕВБ(A$4:A$21;5)="склад"));2);))*Ч(СМЕЩ(B$3;LOG(МУМНОЖ(--(СТРОКА($4:$21)>=ТРАНСП(СТРОКА($4:$21)));2^СТРОКА($1:$18)*(ЛЕВБ(A$4:A$21;5)="полка"))+(ЛЕВБ(A$4:A$21;5)="склад")*2;2);))*ЕСЛИ((ЛЕВБ(A$4:A$21;5)="склад")+(ЛЕВБ(A$4:A$21;5)="полка");1;B$4:B$21))
На примере работает нормально.
С группировкой:Код
=СУММ((A$4:A$21=A4)*Ч(СМЕЩ(B$3;LOG(МУМНОЖ(--(СТРОКА($4:$21)>=ТРАНСП(СТРОКА($4:$21)));2^СТРОКА($1:$18)*(ЛЕВБ(A$4:A$21)="1"));2);))*Ч(СМЕЩ(B$3;LOG(МУМНОЖ(--(СТРОКА($4:$21)>=ТРАНСП(СТРОКА($4:$21)));2^СТРОКА($1:$18)*(ЛЕВБ(A$4:A$21)="2"))+(ЛЕВБ(A$4:A$21)="1")*2;2);))*ЕСЛИ((ЛЕВБ(A$4:A$21)="1")+(ЛЕВБ(A$4:A$21)="2");1;B$4:B$21))

К сообщению приложен файл: 5352653-2.xlsx (15.9 Kb)

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

Сообщение отредактировал Светлый - Четверг, 06.12.2018, 16:12

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

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

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

Сообщений: 4

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

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

Excel 2016

Спасибо за помощь.
Буду разбираться в полученной информации.

Дата: Четверг, 06.12.2018, 16:09 | Сообщение № 8

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

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

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

Excel 2013, 2016

Допустил ошибку в формуле. Исправленная:

Код

=СУММ((A$4:A$21=A4)*Ч(СМЕЩ(B$3;LOG(МУМНОЖ(Ч(СТРОКА($4:$21)>=ТРАНСП(СТРОКА($4:$21)));2^СТРОКА($1:$18)*(ЛЕВБ(A$4:A$21)="1"));2);))*ЕСЛИ(ЛЕВБ(A$4:A$21)="1";1;Ч(СМЕЩ(B$3;LOG(МУМНОЖ(Ч(СТРОКА($4:$21)>=ТРАНСП(СТРОКА($4:$21)));2^СТРОКА($1:$18)*(ЛЕВБ(A$4:A$21)="2"));2);))*ЕСЛИ(ЛЕВБ(A$4:A$21)="2";1;B$4:B$21)))

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

Дата: Четверг, 06.12.2018, 17:36 | Сообщение № 9

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

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

Сообщений: 4

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

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

Excel 2016

Светлый, подскажите, пожалуйста, в чем может быть проблема.
Если я захожу в строку формул, не редактируя формулу, нажатием "Enter", выхожу из нее, то формула перестает работать, выдает - #ЗНАЧ.
Если выхожу из строки формул нажатием "Esc", то все в порядке.

Дата: Четверг, 06.12.2018, 22:39 | Сообщение № 10

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

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

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

Excel 2013, 2016

Скорее всего, это формула массива. Выходить после редактирования надо одновременным нажатием Ctrl+Shift+Enter

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

Дата: Суббота, 08.12.2018, 11:18 | Сообщение № 11

Группа: Друзья

Ранг: Старожил

Сообщений: 1829

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

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

Excel 2013, 2016

Ещё немного доработал массивную формулу:

Код

=СУММ((A$4:A$21=A4)*10^МУМНОЖ(LOG(Ч(СМЕЩ(B$3;ЕСЛИ(ЛЕВБ(A$4:A$21)<{"1";"2";"3"};1;LOG(МУМНОЖ(Ч(СТРОКА($4:$21)>ТРАНСП(СТРОКА($3:$20)));2^СТРОКА($1:$18)*(ЕСЛИОШИБКА(--ЛЕВБ(A$4:A$21);)={1;2;0}));2));)));{1:1:1}))

*Чуточку иначе:

Код

=СУММ((A$4:A$21=A4)*10^МУМНОЖ(ЕСЛИ(ЛЕВБ(A$4:A$21)<{"1";"2";"3"};;LOG(Ч(СМЕЩ(B$1;LOG(МУМНОЖ(Ч(СТРОКА($4:$21)>ТРАНСП(СТРОКА($3:$20)));2^СТРОКА($3:$20)*(ЕСЛИОШИБКА(--ЛЕВБ(A$4:A$21);)={1;2;0}));2);))));{1:1:1}))

Недостаток в том, что названия не могут начинаться с пробелов и других символов с кодом меньше 48 - "0"
**

Код

=СУММ((A$4:A$21=A4)*10^МУМНОЖ(ЕСЛИ(ЛЕВБ(A$4:A$21)<""&{1;2;3};;LOG(Ч(СМЕЩ(B$1;LOG(МУМНОЖ(Ч(СТРОКА($4:$21)>ТРАНСП(СТРОКА($3:$20)));2^СТРОКА($3:$20)*(ЕСЛИОШИБКА(--ЛЕВБ(A$4:A$21);)={1;2;0}));2);))));{1:1:1}))

***

Код

=СУММ((A$4:A$21=A4)*10^МУМНОЖ(ЕСЛИ(ЛЕВБ(A$4:A$21)<""&{1;2;3};;LOG(Ч(СМЕЩ(B$1;LOG(МУМНОЖ(Ч(СТРОКА($4:$21)>ТРАНСП(СТРОКА($3:$20)));2^СТРОКА($3:$20)*(ЗНАК(50-КОДСИМВ(A$4:A$21))={1;0;-1}));2);))));{1:1:1}))

К сообщению приложен файл: 5352653-3.xlsx (15.0 Kb)

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

Сообщение отредактировал Светлый - Суббота, 08.12.2018, 18:16