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

Вход

Регистрация

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

 

= Мир MS Excel/Подсчет общего количества одинаковых ячеек внутри групп - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет общего количества одинаковых ячеек внутри групп (Формулы/Formulas)
Подсчет общего количества одинаковых ячеек внутри групп
akakak Дата: Среда, 05.12.2018, 18:39 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - akakak
Дата добавления - 05.12.2018 в 18:39
Светлый Дата: Среда, 05.12.2018, 19:01 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 749
Репутация: 199 ±
Замечаний: 0% ±

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


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

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

Автор - Светлый
Дата добавления - 05.12.2018 в 19:01
Светлый Дата: Среда, 05.12.2018, 19:54 | Сообщение № 3
Группа: Проверенные
Ранг: Ветеран
Сообщений: 749
Репутация: 199 ±
Замечаний: 0% ±

Excel 2010
Массивная формула в доп. столбце:
Код
=ЕСЛИ(ЛЕВБ(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)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеМассивная формула в доп. столбце:
Код
=ЕСЛИ(ЛЕВБ(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)

Автор - Светлый
Дата добавления - 05.12.2018 в 19:54
akakak Дата: Четверг, 06.12.2018, 10:58 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - akakak
Дата добавления - 06.12.2018 в 10:58
Pelena Дата: Четверг, 06.12.2018, 10:59 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 13300
Репутация: 2927 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Если "склад" и "полка" условные названия, то можно привязаться к уровням группировки(пользовательская функция Уровень), а затем уже использовать формулу от Светлого. Макросы должны быть разрешены
К сообщению приложен файл: 5352653.xlsm(18.1 Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЕсли "склад" и "полка" условные названия, то можно привязаться к уровням группировки(пользовательская функция Уровень), а затем уже использовать формулу от Светлого. Макросы должны быть разрешены

Автор - Pelena
Дата добавления - 06.12.2018 в 10:59
Светлый Дата: Четверг, 06.12.2018, 12:24 | Сообщение № 6
Группа: Проверенные
Ранг: Ветеран
Сообщений: 749
Репутация: 199 ±
Замечаний: 0% ±

Excel 2010
Покажите, что может быть в группах, или задайте признак уровня группы. Уровень склад 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
 
Ответить
СообщениеПокажите, что может быть в группах, или задайте признак уровня группы. Уровень склад 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))

Автор - Светлый
Дата добавления - 06.12.2018 в 12:24
akakak Дата: Четверг, 06.12.2018, 14:24 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - akakak
Дата добавления - 06.12.2018 в 14:24
Светлый Дата: Четверг, 06.12.2018, 16:09 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 749
Репутация: 199 ±
Замечаний: 0% ±

Excel 2010
Допустил ошибку в формуле. Исправленная:
Код
=СУММ((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)))


Программировать проще, чем писать стихи.
 
Ответить
СообщениеДопустил ошибку в формуле. Исправленная:
Код
=СУММ((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 в 16:09
akakak Дата: Четверг, 06.12.2018, 17:36 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - akakak
Дата добавления - 06.12.2018 в 17:36
Светлый Дата: Четверг, 06.12.2018, 22:39 | Сообщение № 10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 749
Репутация: 199 ±
Замечаний: 0% ±

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


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

Автор - Светлый
Дата добавления - 06.12.2018 в 22:39
Светлый Дата: Суббота, 08.12.2018, 11:18 | Сообщение № 11
Группа: Проверенные
Ранг: Ветеран
Сообщений: 749
Репутация: 199 ±
Замечаний: 0% ±

Excel 2010
Ещё немного доработал массивную формулу:
Код
=СУММ((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
 
Ответить
СообщениеЕщё немного доработал массивную формулу:
Код
=СУММ((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}))

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

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