Сортировка по сумме сгруппированных значений - Мир MS Excel

Регистрация

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

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

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сортировка по сумме сгруппированных значений (Формулы/Formulas)

Сортировка по сумме сгруппированных значений

Дата: Четверг, 28.01.2016, 14:55 | Сообщение № 1

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

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

Сообщений: 9

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

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

Excel 2013

Уважаемые гуру, прошу научить как отсортировать сводную таблицу не просто по минимуму, а по минимальной сумме сгруппированных значений.

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

Сообщение отредактировал 333 - Четверг, 28.01.2016, 20:01

Ответить

Дата: Четверг, 28.01.2016, 15:30 | Сообщение № 2

Группа: Проверенные

Ранг: Ветеран

Сообщений: 589

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

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

Excel 2010

333, Попробуемс)
Для месяца с минимальным кол-вом съеденных

Код

=ЕСЛИ(ЕОШ(НАЙТИ("Итог";$F17;1)>0);B17;ИНДЕКС($G5:$G16;ПОИСКПОЗ(МИН(ЕСЛИ(($F$5:$F$109=ЛЕВБ($F17;НАЙТИ("И";$F17;1)-2))*$H$5:$H$109<>0;($F$5:$F$109=ЛЕВБ($F17;НАЙТИ("И";$F17;1)-2))*$H$5:$H$109));$H5:$H16;);))

Для минимума съеденных

Код

=ЕСЛИ(ЕОШ(НАЙТИ("Итог";$F17;1)>0);D17;МИН(ЕСЛИ(($F$5:$F$109=ЛЕВБ($F17;НАЙТИ("И";$F17;1)-2))*$H$5:$H$109<>0;($F$5:$F$109=ЛЕВБ($F17;НАЙТИ("И";$F17;1)-2))*$H$5:$H$109)))

[p.s.]Я далеко не гуру, но набрался смелости и написал...

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

Сообщение отредактировал YouGreed - Четверг, 28.01.2016, 15:31

Ответить

Дата: Четверг, 28.01.2016, 16:18 | Сообщение № 3

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

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

Сообщений: 19179

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

Замечаний: ±

Excel 365 & Mac Excel

Может устроит просто Условным форматированием отметить минимальные значения?
Или ещё вариант с доп. столбцом в исходной таблице и, соответственно, выводом в сводную

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

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

Ответить

Дата: Четверг, 28.01.2016, 16:40 | Сообщение № 4

Группа: Проверенные

Ранг: Ветеран

Сообщений: 931

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

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

Excel 2007

А я немного без сводной поизвращался. Недостаток, правда, есть - если минимальных значений больше одного, показывает первое. Можно нагромоздить и показать все. Также можно список "блюд" сделать динамичным. Не стал вышеуказанное делать, ибо не знаю надо ли)))
Но можно и формулами:
месяц (для D17)

Код

=ЕСЛИ(B17=0;СУММПРОИЗВ((МИН(C5:C16)=C5:C16)*B5:B16);"")

минимум (Е17)

Код

=ЕСЛИОШИБКА(ИНДЕКС(C5:C16;D17);"")

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

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Четверг, 28.01.2016, 16:53

Ответить

Дата: Четверг, 28.01.2016, 17:03 | Сообщение № 5

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

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

Сообщений: 9

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

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

Excel 2013

Pelena, YouGreed, спасибо за ответы!
Вариант с доп.столбцом для понимания сложнее, но по работе с ним, выходит, наверное, проще.

pabchek, начиная с пива в таблице что-то пошло не так. Может чего не понял. А по формату это, пожалуй, самый удобный вариант был бы.

P.S. Была надежда что вопрос решается свойствами самой таблицы, но, видать, не в этом выпуске офиса.

Ответить

Дата: Четверг, 28.01.2016, 17:05 | Сообщение № 6

Группа: Проверенные

Ранг: Ветеран

Сообщений: 931

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

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

Excel 2007

Цитата 333, 28.01.2016 в 17:03, в сообщении № 5

начиная с пива в таблице что-то пошло не так

Да, я уже увидел. Проблема в том, что там два минимума. Вопрос, нужно выводить оба или какой?

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Четверг, 28.01.2016, 17:05

Ответить

Дата: Четверг, 28.01.2016, 17:21 | Сообщение № 7

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

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

Сообщений: 9

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

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

Excel 2013

pabchek, ага, понятно. Можно только первый.

Ответить

Дата: Четверг, 28.01.2016, 17:33 | Сообщение № 8

Группа: Проверенные

Ранг: Ветеран

Сообщений: 931

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

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

Excel 2007

Ну тогда наверно так

Код

=ЕСЛИ(B17=0;НАИМЕНЬШИЙ(C5:C16;1);"")

Код

=ЕСЛИОШИБКА(ИНДЕКС(B5:B16;ПОИСКПОЗ(D17;C5:C16;0));"")

Колонки перепутал - перезалил

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

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Четверг, 28.01.2016, 17:35

Ответить

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

Группа: Проверенные

Ранг: Ветеран

Сообщений: 589

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

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

Excel 2010

333, Немного переделал вариант pabchek

Код

=ЕСЛИ(B43=0;ИНДЕКС(B31:B42;ПОИСКПОЗ(МИН(C31:C42);C31:C42;0););"")

для столбца D и все пойдет как нужно))

[p.s.]извините коллега, не спросился((
А вообще обидно, делаешь монстра на 204 символа, а оказывается, не оптимальное решение((

Сообщение отредактировал YouGreed - Четверг, 28.01.2016, 17:40

Ответить

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

Группа: Проверенные

Ранг: Ветеран

Сообщений: 931

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

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

Excel 2007

Цитата YouGreed, 28.01.2016 в 17:33, в сообщении № 9

Немного переделал

А я уже поправился

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Ответить

Дата: Четверг, 28.01.2016, 19:30 | Сообщение № 11

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

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

Сообщений: 9

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

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

Excel 2013

YouGreed, ваш вариант длиннее, зато, похоже, корректнее и универсальнее.
В последнем предложенном pabchek варианте формулы в первых строках дают ошибку, которая в моем случае на результате не сказывается )

Сообщение отредактировал 333 - Четверг, 28.01.2016, 19:30

Ответить

Дата: Четверг, 28.01.2016, 20:06 | Сообщение № 12

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

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

Сообщений: 9

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

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

Excel 2013

pabchek, прошу пардону, обнаружил, что файл с моим примером не вполне корректный. Приблизил к реальности ))
Удастся ли использовать ваше решение если пиво, зайчики, девочки и мальчики на столе бывают не круглый год? ))

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

Ответить

Дата: Четверг, 28.01.2016, 20:44 | Сообщение № 13

Группа: Модераторы

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

Сообщений: 16675

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

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

2003; 2007; 2010; 2013 RUS

А почему девочки на столе не круглый год? Безобразие!!!
Минимум

Код

=МИН(C14:ИНДЕКС(C$5:C14;ПОИСКПОЗ(ПОДСТАВИТЬ(A15;" Итог";);A$5:A14;)))

Месяц

Код

=ПРОСМОТР(;-1/(C$5:C14=E15);B$5:B14)

Обе формулы немассивные

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

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Дата: Пятница, 29.01.2016, 10:04 | Сообщение № 14

Группа: Проверенные

Ранг: Ветеран

Сообщений: 931

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

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

Excel 2007

Цитата 333, 28.01.2016 в 19:30, в сообщении № 11

в первых строках дают ошибку

Все верно, так и должно быть, поскольку в отличие от _Boroda_, я более ленив и писал формулу, протаскиваемую сверху вниз)))) (на ее функционале это не сказывается). Сейчас попробую адаптировать под Ваше последнее условие.

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Ответить

Дата: Пятница, 29.01.2016, 11:11 | Сообщение № 15

Группа: Проверенные

Ранг: Ветеран

Сообщений: 931

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

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

Excel 2007

Ну вот как-то так получилось
месяц
Код
=ЕСЛИОШИБКА(ИНДЕКС(B3:B14;ПОИСКПОЗ(F15;C3:C14;0));"")

еда (формула массива)

Код

=ЕСЛИ(B15=0;МИН(ИНДЕКС(C:C;МАКС(ЕТЕКСТ(A3:A14)*СТРОКА(A3:A14))):C14);"")

Хотя... если в предыдущем пункте может оказаться число равное минимуму из текущего, то формула месяца примет следующий вид (тоже массивная)
Код
=ЕСЛИОШИБКА(ПРОСМОТР(;-1/(ИНДЕКС(C:C;МАКС(ЕТЕКСТ(A3:A14)*СТРОКА(A3:A14))):C14=F15);ИНДЕКС(B:B;МАКС(ЕТЕКСТ(A3:A14)*СТРОКА(A3:A14))):B14);"")

да чтож такое((( эта формула показывает не первый, а последний минимум

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

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Пятница, 29.01.2016, 11:26

Ответить

Дата: Пятница, 29.01.2016, 11:31 | Сообщение № 16

Группа: Модераторы

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

Сообщений: 16675

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

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

2003; 2007; 2010; 2013 RUS

Цитата pabchek, 29.01.2016 в 11:11, в сообщении № 15

если в предыдущем пункте может оказаться число

Воот, насчет этого догадался. А теперь еще одно ЕСЛИ - если зайчат ели только в январе? Поменяй даты в календаре и обнови сводную - ссылка на 12 вверх полетит.
[p.s.]Мои формулы выше чем-то не устраивают? Или просто хочется самому написать?

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Дата: Пятница, 29.01.2016, 11:44 | Сообщение № 17

Группа: Проверенные

Ранг: Ветеран

Сообщений: 931

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

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

Excel 2007

Цитата _Boroda_, 29.01.2016 в 11:31, в сообщении № 16

чем-то не устраивают?

Написать, конечно, хочется самому и... У тебя также показывает последний минимум, а не первый (смотри мальчиков или девочек)

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Пятница, 29.01.2016, 11:47

Ответить

Дата: Пятница, 29.01.2016, 11:58 | Сообщение № 18

Группа: Проверенные

Ранг: Ветеран

Сообщений: 931

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

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

Excel 2007

И... раз уж допстолбцы, то почему бы и не допстроки :D
месяц

Код

=ЕСЛИОШИБКА(ИНДЕКС(ИНДЕКС(B:B;МАКС(ЕТЕКСТ(A2:A13)*СТРОКА(A2:A13))):B13;ПОИСКПОЗ(F14;ИНДЕКС(C:C;МАКС(ЕТЕКСТ(A2:A13)*СТРОКА(A2:A13))):C13;0));"")

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

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Пятница, 29.01.2016, 12:00

Ответить

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сортировка по сумме сгруппированных значений (Формулы/Formulas)

Страница 1 из 1
1

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