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

Вход

Регистрация

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

 

= Мир MS Excel/Подсчет одной формулой значений без лишних столбцов. - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет одной формулой значений без лишних столбцов. (Формулы/Formulas)
Подсчет одной формулой значений без лишних столбцов.
Окся Дата: Среда, 06.10.2021, 16:25 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 69
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Доброго времени суток. прошу вашей профессиональной помощи . Табличка во вложении.
Есть поставленная задача просчитать сумму заказа не превышая квоты по торговой марке. Но при этом надо учитывать все остатки . пути , заказы. для этого есть дополнительные колонки и расчёт в ячейки I6 и K6. Так же расчёт должен быть произведен при фильтре по каждой торговой марке отдельно.

Вопрос. Есть ли такая формула которая просчитает в ячейке D2 данные и колонки I и K не понадобятся ? И если можно , покажите пожалуйста как. Заранее спасибо.
К сообщению приложен файл: 6253001.xls (34.5 Kb)

(*_*)

Сообщение отредактировал Окся - Среда, 06.10.2021, 16:26
 
Ответить
СообщениеДоброго времени суток. прошу вашей профессиональной помощи . Табличка во вложении.
Есть поставленная задача просчитать сумму заказа не превышая квоты по торговой марке. Но при этом надо учитывать все остатки . пути , заказы. для этого есть дополнительные колонки и расчёт в ячейки I6 и K6. Так же расчёт должен быть произведен при фильтре по каждой торговой марке отдельно.

Вопрос. Есть ли такая формула которая просчитает в ячейке D2 данные и колонки I и K не понадобятся ? И если можно , покажите пожалуйста как. Заранее спасибо.
Автор - Окся
Дата добавления - 06.10.2021 в 16:25
Светлый Дата: Среда, 06.10.2021, 17:06 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Доброго!
Так устроит?
Код
=G2-СУММПРОИЗВ((МУМНОЖ(Ч(+F8:H15);СТРОКА(1:3)^0)+J8:J15)*E8:E15*(D8:D15=D1))

Программировать проще, чем писать стихи.
 
Ответить
СообщениеДоброго!
Так устроит?
Код
=G2-СУММПРОИЗВ((МУМНОЖ(Ч(+F8:H15);СТРОКА(1:3)^0)+J8:J15)*E8:E15*(D8:D15=D1))
Автор - Светлый
Дата добавления - 06.10.2021 в 17:06
Egyptian Дата: Четверг, 07.10.2021, 01:35 | Сообщение № 3
Группа: Проверенные
Ранг: Ветеран
Сообщений: 514
Репутация: 185 ±
Замечаний: 0% ±

Excel 2013/2016
Наглядный вариант с выпадающим списком в ячейке D1
Код
=G2-(SUMPRODUCT($E$8:$E$15*($D$8:$D$15=D1)*N(+$F$8:$H$15))+SUMPRODUCT(($E$8:$E$15)*($J$8:$J$15)*($D$8:$D$15=D1)))

Если непременно с фильтром то формула массива:
Код
=G2-(SUM(IFNA(SUBTOTAL(109;OFFSET($E$7;ROW($E$8:$E$15)-ROW($E$8)+1;))*N(+$F$8:$H$15);))+SUM(IFNA(($J$8:$J$15)*SUBTOTAL(109;OFFSET($E$7;ROW($E$8:$E$15)-ROW($E$8)+1;));)))

...и небольшая правка компактной формулы от Светлый. Тоже для фильтра.
Код
=G2-SUM((MMULT(N(+$F$8:$H$15);ROW(1:3)^0)+$J$8:$J$15)*SUBTOTAL(109;OFFSET($E$7;ROW($E$8:$E$15)-ROW($E$8)+1;)))


P.S. У вас фильтр был в строке 8 а надо в строке 7, иначе расчеты будут неверными. В приложенном файле поправил.
К сообщению приложен файл: 2444923.xls (34.5 Kb)


Сообщение отредактировал Egyptian - Четверг, 07.10.2021, 02:36
 
Ответить
СообщениеНаглядный вариант с выпадающим списком в ячейке D1
Код
=G2-(SUMPRODUCT($E$8:$E$15*($D$8:$D$15=D1)*N(+$F$8:$H$15))+SUMPRODUCT(($E$8:$E$15)*($J$8:$J$15)*($D$8:$D$15=D1)))

Если непременно с фильтром то формула массива:
Код
=G2-(SUM(IFNA(SUBTOTAL(109;OFFSET($E$7;ROW($E$8:$E$15)-ROW($E$8)+1;))*N(+$F$8:$H$15);))+SUM(IFNA(($J$8:$J$15)*SUBTOTAL(109;OFFSET($E$7;ROW($E$8:$E$15)-ROW($E$8)+1;));)))

...и небольшая правка компактной формулы от Светлый. Тоже для фильтра.
Код
=G2-SUM((MMULT(N(+$F$8:$H$15);ROW(1:3)^0)+$J$8:$J$15)*SUBTOTAL(109;OFFSET($E$7;ROW($E$8:$E$15)-ROW($E$8)+1;)))


P.S. У вас фильтр был в строке 8 а надо в строке 7, иначе расчеты будут неверными. В приложенном файле поправил.
Автор - Egyptian
Дата добавления - 07.10.2021 в 01:35
Окся Дата: Четверг, 07.10.2021, 09:39 | Сообщение № 4
Группа: Пользователи
Ранг: Участник
Сообщений: 69
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Спасибо огромное !

Код
=G2-(СУММПРОИЗВ($E$8:$E$15*($D$8:$D$15=D1)*Ч(+$F$8:$H$15))+СУММПРОИЗВ(($E$8:$E$15)*($J$8:$J$15)*($D$8:$D$15=D1)))


Вот эта формула самая подходящая , остальные не удается подставить , выдает #ЗНАЧ! , скорее всего что-то делаю не так , но вроде что-то с функцией "СТРОКА"

Спасибо за помощь. hands

(*_*)

Сообщение отредактировал Окся - Четверг, 07.10.2021, 15:47
 
Ответить
СообщениеСпасибо огромное !

Код
=G2-(СУММПРОИЗВ($E$8:$E$15*($D$8:$D$15=D1)*Ч(+$F$8:$H$15))+СУММПРОИЗВ(($E$8:$E$15)*($J$8:$J$15)*($D$8:$D$15=D1)))


Вот эта формула самая подходящая , остальные не удается подставить , выдает #ЗНАЧ! , скорее всего что-то делаю не так , но вроде что-то с функцией "СТРОКА"

Спасибо за помощь. hands
Автор - Окся
Дата добавления - 07.10.2021 в 09:39
Egyptian Дата: Четверг, 07.10.2021, 16:12 | Сообщение № 5
Группа: Проверенные
Ранг: Ветеран
Сообщений: 514
Репутация: 185 ±
Замечаний: 0% ±

Excel 2013/2016
Остальные - это формулы массива. Их нужно вводить через CTRL+SHIFT+ENTER. Т.е, вначале жмете CTRL, затем не отпуская его жмете остальные.


Сообщение отредактировал Egyptian - Четверг, 07.10.2021, 16:12
 
Ответить
СообщениеОстальные - это формулы массива. Их нужно вводить через CTRL+SHIFT+ENTER. Т.е, вначале жмете CTRL, затем не отпуская его жмете остальные.
Автор - Egyptian
Дата добавления - 07.10.2021 в 16:12
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет одной формулой значений без лишних столбцов. (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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