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

Вход

Регистрация

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

 

= Мир MS Excel/суммирование по ставкам в разрезе категорий - Мир MS Excel

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

2016
Приятного всем лета!
Надо заполнить форму, распределяющий начисление по ставкам и категориям. Сводной добился только отображение ставок по критериям. А вот силами сводной сумму этих ставок не смог. В другом листе добился тоже самого и не добился того же)). Прошу, если есть идеи, подскажите. Желательно без макросов. Заранее благодарствую.
К сообщению приложен файл: 2024219.xlsb (76.0 Kb)
 
Ответить
СообщениеПриятного всем лета!
Надо заполнить форму, распределяющий начисление по ставкам и категориям. Сводной добился только отображение ставок по критериям. А вот силами сводной сумму этих ставок не смог. В другом листе добился тоже самого и не добился того же)). Прошу, если есть идеи, подскажите. Желательно без макросов. Заранее благодарствую.
Автор - nikitan95
Дата добавления - 03.06.2019 в 08:29
Светлый Дата: Понедельник, 03.06.2019, 08:55 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Добрый день!
По Вашему примеру:
Код
=СУММЕСЛИМН(МАОШ!$AW$2:$AW$70;МАОШ!$D$2:$D$70;$A10;МАОШ!$E$2:$E$70;B$3)
Непонятно, для чего ставка, может, на неё ещё умножить надо?
Код
=СУММЕСЛИМН(МАОШ!$AW$2:$AW$70;МАОШ!$D$2:$D$70;$A10;МАОШ!$E$2:$E$70;B$3)*B10

Программировать проще, чем писать стихи.
 
Ответить
СообщениеДобрый день!
По Вашему примеру:
Код
=СУММЕСЛИМН(МАОШ!$AW$2:$AW$70;МАОШ!$D$2:$D$70;$A10;МАОШ!$E$2:$E$70;B$3)
Непонятно, для чего ставка, может, на неё ещё умножить надо?
Код
=СУММЕСЛИМН(МАОШ!$AW$2:$AW$70;МАОШ!$D$2:$D$70;$A10;МАОШ!$E$2:$E$70;B$3)*B10
Автор - Светлый
Дата добавления - 03.06.2019 в 08:55
nikitan95 Дата: Понедельник, 03.06.2019, 09:55 | Сообщение № 3
Группа: Проверенные
Ранг: Обитатель
Сообщений: 410
Репутация: 0 ±
Замечаний: 0% ±

2016
Светлый, спасибо за решение. Но она недостаточно точно распределяет по критериям. Я красными отметил. И на примере одного желтым отметил как должно быть. Файл прикрепил.
К сообщению приложен файл: _-1.xlsb (87.0 Kb)
 
Ответить
СообщениеСветлый, спасибо за решение. Но она недостаточно точно распределяет по критериям. Я красными отметил. И на примере одного желтым отметил как должно быть. Файл прикрепил.
Автор - nikitan95
Дата добавления - 03.06.2019 в 09:55
nikitan95 Дата: Понедельник, 03.06.2019, 09:58 | Сообщение № 4
Группа: Проверенные
Ранг: Обитатель
Сообщений: 410
Репутация: 0 ±
Замечаний: 0% ±

2016
Цитата Светлый, 03.06.2019 в 08:55, в сообщении № 2 ()
Непонятно, для чего ставка

Ставка просто как данные, чтобы знать кто на скольких ставках сидит и по каким категориям сидит.
 
Ответить
Сообщение
Цитата Светлый, 03.06.2019 в 08:55, в сообщении № 2 ()
Непонятно, для чего ставка

Ставка просто как данные, чтобы знать кто на скольких ставках сидит и по каким категориям сидит.
Автор - nikitan95
Дата добавления - 03.06.2019 в 09:58
Светлый Дата: Понедельник, 03.06.2019, 10:37 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Для C6:
Код
=СУММПРОИЗВ(МАОШ!$AF$2:$AF$70+МАОШ!$AO$2:$AO$70;(МАОШ!$D$2:$D$70=$A6)*(МАОШ!$E$2:$E$70=B$3))
для E6:
Код
=СУММПРОИЗВ(МАОШ!$AG$2:$AG$70+МАОШ!$AP$2:$AP$70;(МАОШ!$D$2:$D$70=$A6)*(МАОШ!$H$2:$H$70=D$3))
Угадал?

Программировать проще, чем писать стихи.
 
Ответить
СообщениеДля C6:
Код
=СУММПРОИЗВ(МАОШ!$AF$2:$AF$70+МАОШ!$AO$2:$AO$70;(МАОШ!$D$2:$D$70=$A6)*(МАОШ!$E$2:$E$70=B$3))
для E6:
Код
=СУММПРОИЗВ(МАОШ!$AG$2:$AG$70+МАОШ!$AP$2:$AP$70;(МАОШ!$D$2:$D$70=$A6)*(МАОШ!$H$2:$H$70=D$3))
Угадал?
Автор - Светлый
Дата добавления - 03.06.2019 в 10:37
nikitan95 Дата: Понедельник, 03.06.2019, 10:48 | Сообщение № 6
Группа: Проверенные
Ранг: Обитатель
Сообщений: 410
Репутация: 0 ±
Замечаний: 0% ±

2016
Цитата Светлый, 03.06.2019 в 10:37, в сообщении № 5 ()
Угадал?

почти, мой друг..
у ячеек C5, E5, E10, E14, E19, E26 нулевые значения, хотя рядом ставки имеются..
 
Ответить
Сообщение
Цитата Светлый, 03.06.2019 в 10:37, в сообщении № 5 ()
Угадал?

почти, мой друг..
у ячеек C5, E5, E10, E14, E19, E26 нулевые значения, хотя рядом ставки имеются..
Автор - nikitan95
Дата добавления - 03.06.2019 в 10:48
nikitan95 Дата: Понедельник, 03.06.2019, 12:31 | Сообщение № 7
Группа: Проверенные
Ранг: Обитатель
Сообщений: 410
Репутация: 0 ±
Замечаний: 0% ±

2016
есть идея просто ставку каждую умножить на соответствующий тариф (там в скрытом листе) и расположить соответственно. Но потом как приплюсовать туда оставшуюся суммы?((
 
Ответить
Сообщениеесть идея просто ставку каждую умножить на соответствующий тариф (там в скрытом листе) и расположить соответственно. Но потом как приплюсовать туда оставшуюся суммы?((
Автор - nikitan95
Дата добавления - 03.06.2019 в 12:31
Светлый Дата: Понедельник, 03.06.2019, 12:43 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Объяснение непонятно и мало правильных данных. Для нескольких ячеек и нескольких фамилий напишите формулу простого сложения. Что нужно складывать. А то в угадайку играем.

Программировать проще, чем писать стихи.
 
Ответить
СообщениеОбъяснение непонятно и мало правильных данных. Для нескольких ячеек и нескольких фамилий напишите формулу простого сложения. Что нужно складывать. А то в угадайку играем.
Автор - Светлый
Дата добавления - 03.06.2019 в 12:43
nikitan95 Дата: Понедельник, 03.06.2019, 13:18 | Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 410
Репутация: 0 ±
Замечаний: 0% ±

2016
вот сделал для троих...
К сообщению приложен файл: _-03.xlsb (78.0 Kb)
 
Ответить
Сообщениевот сделал для троих...
Автор - nikitan95
Дата добавления - 03.06.2019 в 13:18
Светлый Дата: Понедельник, 03.06.2019, 15:17 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Для L5:
Код
=ИНДЕКС(МАОШ!$G:$G;ПОИСКПОЗ($A5;МАОШ!$D:$D;))*СУММПРОИЗВ(ИНДЕКС(Тарифы!$E:$F;ПОИСКПОЗ(ИНДЕКС(МАОШ!$F:$F;ПОИСКПОЗ($A5;МАОШ!$D:$D;));Тарифы!$A:$A;);))+СУММПРОИЗВ(($A5=МАОШ!$D$2:$D$70)*МАОШ!$AI$2:$AP$70)
Для M5:
Код
=ИНДЕКС(МАОШ!$J:$J;ПОИСКПОЗ($A5;МАОШ!$D:$D;))*СУММПРОИЗВ(ИНДЕКС(Тарифы!$E:$F;ПОИСКПОЗ(ИНДЕКС(МАОШ!$I:$I;ПОИСКПОЗ($A5;МАОШ!$D:$D;));Тарифы!$A:$A;);))
Так лучше?

Программировать проще, чем писать стихи.
 
Ответить
СообщениеДля L5:
Код
=ИНДЕКС(МАОШ!$G:$G;ПОИСКПОЗ($A5;МАОШ!$D:$D;))*СУММПРОИЗВ(ИНДЕКС(Тарифы!$E:$F;ПОИСКПОЗ(ИНДЕКС(МАОШ!$F:$F;ПОИСКПОЗ($A5;МАОШ!$D:$D;));Тарифы!$A:$A;);))+СУММПРОИЗВ(($A5=МАОШ!$D$2:$D$70)*МАОШ!$AI$2:$AP$70)
Для M5:
Код
=ИНДЕКС(МАОШ!$J:$J;ПОИСКПОЗ($A5;МАОШ!$D:$D;))*СУММПРОИЗВ(ИНДЕКС(Тарифы!$E:$F;ПОИСКПОЗ(ИНДЕКС(МАОШ!$I:$I;ПОИСКПОЗ($A5;МАОШ!$D:$D;));Тарифы!$A:$A;);))
Так лучше?
Автор - Светлый
Дата добавления - 03.06.2019 в 15:17
nikitan95 Дата: Вторник, 04.06.2019, 01:44 | Сообщение № 11
Группа: Проверенные
Ранг: Обитатель
Сообщений: 410
Репутация: 0 ±
Замечаний: 0% ±

2016
Бодрого всем дня!
Ночью капитально засел за задачку. Из всех вариантов более близкое к решению функцией СУММПРОИЗВ. Может я не смог ясно сформулировать вопрос, потому и такой результат. Оттого, в файле, через дополнительную таблицу и сводную от него показал готовый вид таблицы. Извиняюсь, за формат файла. Он чуток перевысил налагаемый сайтом лимит, потому и завернул его в архив. Думаю, такое разрешено. И очень надеюсь на помощь знатоков.
К сообщению приложен файл: _-01.rar (92.8 Kb)
 
Ответить
СообщениеБодрого всем дня!
Ночью капитально засел за задачку. Из всех вариантов более близкое к решению функцией СУММПРОИЗВ. Может я не смог ясно сформулировать вопрос, потому и такой результат. Оттого, в файле, через дополнительную таблицу и сводную от него показал готовый вид таблицы. Извиняюсь, за формат файла. Он чуток перевысил налагаемый сайтом лимит, потому и завернул его в архив. Думаю, такое разрешено. И очень надеюсь на помощь знатоков.
Автор - nikitan95
Дата добавления - 04.06.2019 в 01:44
_Boroda_ Дата: Вторник, 04.06.2019, 10:23 | Сообщение № 12
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16672
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
На самом деле все равно нифига непонятно. Мне - так точно
Может, так?
Код
=ПРОСМОТР(;-1/($A5=МАОШ!D$2:D$70);МАОШ!G$2:G$70)*СУММПРОИЗВ((авзоф=ПРОСМОТР(;-1/($A5=МАОШ!D$2:D$70);МАОШ!F$2:F$70))*Тарифы!E$2:F$170)+СУММПРОИЗВ(МАОШ!AI$2:AP$70*(МАОШ!D$2:D$70=$A5))

Код
=ПРОСМОТР(;-1/($A5=МАОШ!D$2:D$70);МАОШ!J$2:J$70)*СУММПРОИЗВ((авзоф=ПРОСМОТР(;-1/($A5=МАОШ!D$2:D$70);МАОШ!I$2:I$70))*Тарифы!E$2:F$170)+ПРОСМОТР(;-1/($A5=МАОШ!D$2:D$70);МАОШ!M$2:M$70)*СУММПРОИЗВ((авзоф=ПРОСМОТР(;-1/($A5=МАОШ!D$2:D$70);МАОШ!L$2:L$70))*Тарифы!E$2:F$170)

Вообще ни разу не оптимизировано
К сообщению приложен файл: -03_1.xlsb (77.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеНа самом деле все равно нифига непонятно. Мне - так точно
Может, так?
Код
=ПРОСМОТР(;-1/($A5=МАОШ!D$2:D$70);МАОШ!G$2:G$70)*СУММПРОИЗВ((авзоф=ПРОСМОТР(;-1/($A5=МАОШ!D$2:D$70);МАОШ!F$2:F$70))*Тарифы!E$2:F$170)+СУММПРОИЗВ(МАОШ!AI$2:AP$70*(МАОШ!D$2:D$70=$A5))

Код
=ПРОСМОТР(;-1/($A5=МАОШ!D$2:D$70);МАОШ!J$2:J$70)*СУММПРОИЗВ((авзоф=ПРОСМОТР(;-1/($A5=МАОШ!D$2:D$70);МАОШ!I$2:I$70))*Тарифы!E$2:F$170)+ПРОСМОТР(;-1/($A5=МАОШ!D$2:D$70);МАОШ!M$2:M$70)*СУММПРОИЗВ((авзоф=ПРОСМОТР(;-1/($A5=МАОШ!D$2:D$70);МАОШ!L$2:L$70))*Тарифы!E$2:F$170)

Вообще ни разу не оптимизировано
Автор - _Boroda_
Дата добавления - 04.06.2019 в 10:23
bmv98rus Дата: Вторник, 04.06.2019, 10:28 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
[offtop]
Цитата _Boroda_, 04.06.2019 в 10:23, в сообщении № 12 ()

На самом деле все равно нифига непонятно. Мне - так точно
даже Светлый потемнел :D . Я вот тоже открыл, посмотрел вчера , закрыл. Сегодня открыл новый файл, посмотрел, закрыл. [/offtop]

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

Сообщение отредактировал bmv98rus - Вторник, 04.06.2019, 10:29
 
Ответить
Сообщение[offtop]
Цитата _Boroda_, 04.06.2019 в 10:23, в сообщении № 12 ()

На самом деле все равно нифига непонятно. Мне - так точно
даже Светлый потемнел :D . Я вот тоже открыл, посмотрел вчера , закрыл. Сегодня открыл новый файл, посмотрел, закрыл. [/offtop]
Автор - bmv98rus
Дата добавления - 04.06.2019 в 10:28
Светлый Дата: Вторник, 04.06.2019, 12:10 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата
Почему в желтых ячейках суммы, если слева от них ставки равны нулю?
Потому что МАОШ!G30 не равно 0. Как написали, что складывать, то и сделано в формуле. Я догадываюсь, что где-то надо учитывать категорию и брать с листа МАОШ из разных ст.1 ст.2 или ст.3, но непонятно, в каких случаях откуда.
[offtop]Люблю всякие головоломки, но эта мне не по зубам пока.

Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Цитата
Почему в желтых ячейках суммы, если слева от них ставки равны нулю?
Потому что МАОШ!G30 не равно 0. Как написали, что складывать, то и сделано в формуле. Я догадываюсь, что где-то надо учитывать категорию и брать с листа МАОШ из разных ст.1 ст.2 или ст.3, но непонятно, в каких случаях откуда.
[offtop]Люблю всякие головоломки, но эта мне не по зубам пока.
Автор - Светлый
Дата добавления - 04.06.2019 в 12:10
nikitan95 Дата: Вторник, 04.06.2019, 15:17 | Сообщение № 15
Группа: Проверенные
Ранг: Обитатель
Сообщений: 410
Репутация: 0 ±
Замечаний: 0% ±

2016
Я возьму тайм-аут, чтоб потом внятнее объяснить... Как раз проблемы сейчас с интернетом у нас. Надеюсь, тему осилим...
 
Ответить
СообщениеЯ возьму тайм-аут, чтоб потом внятнее объяснить... Как раз проблемы сейчас с интернетом у нас. Надеюсь, тему осилим...
Автор - nikitan95
Дата добавления - 04.06.2019 в 15:17
nikitan95 Дата: Среда, 05.06.2019, 18:51 | Сообщение № 16
Группа: Проверенные
Ранг: Обитатель
Сообщений: 410
Репутация: 0 ±
Замечаний: 0% ±

2016
Итак.. Минимизировал файл оставив лишь комбинации вариантов. В листе Задача описал механизм начисления по категориям, а в листе Перестановка, формулами. Надеюсь, это должно помочь. Благодарю за терпение.
К сообщению приложен файл: 8848461.xlsb (86.4 Kb)


Сообщение отредактировал nikitan95 - Среда, 05.06.2019, 18:53
 
Ответить
СообщениеИтак.. Минимизировал файл оставив лишь комбинации вариантов. В листе Задача описал механизм начисления по категориям, а в листе Перестановка, формулами. Надеюсь, это должно помочь. Благодарю за терпение.
Автор - nikitan95
Дата добавления - 05.06.2019 в 18:51
nikitan95 Дата: Пятница, 07.06.2019, 07:21 | Сообщение № 17
Группа: Проверенные
Ранг: Обитатель
Сообщений: 410
Репутация: 0 ±
Замечаний: 0% ±

2016
Позавчера поздно выложил обновленный файл и пост. Оттого другие темы "перескачили" эту тему. Обновляю пост, чтобы тему заметили.
 
Ответить
СообщениеПозавчера поздно выложил обновленный файл и пост. Оттого другие темы "перескачили" эту тему. Обновляю пост, чтобы тему заметили.
Автор - nikitan95
Дата добавления - 07.06.2019 в 07:21
Светлый Дата: Пятница, 07.06.2019, 10:19 | Сообщение № 18
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
На листе "3" в зелёных две протягиваемые формулы. В первом столбце выбор уникальных фамилий в порядке встречаемости.
К сообщению приложен файл: 8848461-1.xlsb (89.4 Kb)

Программировать проще, чем писать стихи.
 
Ответить
СообщениеНа листе "3" в зелёных две протягиваемые формулы. В первом столбце выбор уникальных фамилий в порядке встречаемости.
Автор - Светлый
Дата добавления - 07.06.2019 в 10:19
nikitan95 Дата: Суббота, 08.06.2019, 19:56 | Сообщение № 19
Группа: Проверенные
Ранг: Обитатель
Сообщений: 410
Репутация: 0 ±
Замечаний: 0% ±

2016
Светлый, спасибо! В самое яблочко. Только один вопрос: почему заменили формулу в ставках (столбцы B,D,F,H) с обычного на массивную? Ведь вроде СУММПРОИЗВ охватывает тех же условиях что и написаны вами. Или СУММПРОИЗВ может недостаточно универсален чем формула массива?Признаться это моя первая более сложная формула с СУММПРОИЗВ и имею право цепляться за него. Буду весьма признателен, если просветите.
 
Ответить
СообщениеСветлый, спасибо! В самое яблочко. Только один вопрос: почему заменили формулу в ставках (столбцы B,D,F,H) с обычного на массивную? Ведь вроде СУММПРОИЗВ охватывает тех же условиях что и написаны вами. Или СУММПРОИЗВ может недостаточно универсален чем формула массива?Признаться это моя первая более сложная формула с СУММПРОИЗВ и имею право цепляться за него. Буду весьма признателен, если просветите.
Автор - nikitan95
Дата добавления - 08.06.2019 в 19:56
Светлый Дата: Суббота, 08.06.2019, 22:50 | Сообщение № 20
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
nikitan95, формула уже Ваша. Делайте с ней, что хотите. Лишь бы результат Вас устраивал.
Цитата nikitan95, 08.06.2019 в 19:56, в сообщении № 19 ()
почему заменили формулу в ставках (столбцы B,D,F,H) с обычного на массивную?
Возможно, какие-то промежуточные решения не сработали, и решил укоротить формулу. Или новую формулу сделал с просто СУММ и не проверил на СУММПРОИЗВ. Утомился, глаз замылился, с другими проблемами бился, на поезд опаздывал.
Цитата nikitan95, 08.06.2019 в 19:56, в сообщении № 19 ()
имею право цепляться за него
Цепляться за что-то не всегда стОит. Иногда это отвлекает от лучших решений, а иногда даже вредит делу. Но простые проверенные решения бывают лучше навороченных. И ещё есть зависимость от сроков и периодичности применения решения. Для разового решения задачи можно состряпать кривую медленную формулу, получить результат и забыть про неё. А для задач, которые несколько раз в день решаются, можно сделать наиболее удобную и быструю формулу, пусть на это уйдёт несколько дней.

Программировать проще, чем писать стихи.
 
Ответить
Сообщениеnikitan95, формула уже Ваша. Делайте с ней, что хотите. Лишь бы результат Вас устраивал.
Цитата nikitan95, 08.06.2019 в 19:56, в сообщении № 19 ()
почему заменили формулу в ставках (столбцы B,D,F,H) с обычного на массивную?
Возможно, какие-то промежуточные решения не сработали, и решил укоротить формулу. Или новую формулу сделал с просто СУММ и не проверил на СУММПРОИЗВ. Утомился, глаз замылился, с другими проблемами бился, на поезд опаздывал.
Цитата nikitan95, 08.06.2019 в 19:56, в сообщении № 19 ()
имею право цепляться за него
Цепляться за что-то не всегда стОит. Иногда это отвлекает от лучших решений, а иногда даже вредит делу. Но простые проверенные решения бывают лучше навороченных. И ещё есть зависимость от сроков и периодичности применения решения. Для разового решения задачи можно состряпать кривую медленную формулу, получить результат и забыть про неё. А для задач, которые несколько раз в день решаются, можно сделать наиболее удобную и быструю формулу, пусть на это уйдёт несколько дней.
Автор - Светлый
Дата добавления - 08.06.2019 в 22:50
Мир MS Excel » Вопросы и решения » Вопросы по Excel » суммирование по ставкам в разрезе категорий (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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