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

Вход

Регистрация

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

 

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

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

Excel 2013
Уважаемые гуру, прошу научить как отсортировать сводную таблицу не просто по минимуму, а по минимальной сумме сгруппированных значений.
К сообщению приложен файл: _-.xlsx(29Kb)


Сообщение отредактировал 333 - Четверг, 28.01.2016, 20:01
 
Ответить
СообщениеУважаемые гуру, прошу научить как отсортировать сводную таблицу не просто по минимуму, а по минимальной сумме сгруппированных значений.

Автор - 333
Дата добавления - 28.01.2016 в 14:55
YouGreed Дата: Четверг, 28.01.2016, 15:30 | Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 429
Репутация: 93 ±
Замечаний: 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(41Kb)


Сообщение отредактировал YouGreed - Четверг, 28.01.2016, 15:31
 
Ответить
Сообщение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.]Я далеко не гуру, но набрался смелости и написал...

Автор - YouGreed
Дата добавления - 28.01.2016 в 15:30
Pelena Дата: Четверг, 28.01.2016, 16:18 | Сообщение № 3
Группа: Модераторы
Ранг: Экселист
Сообщений: 10431
Репутация: 2359 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Может устроит просто Условным форматированием отметить минимальные значения?
Или ещё вариант с доп. столбцом в исходной таблице и, соответственно, выводом в сводную
К сообщению приложен файл: -1-.xlsx(39Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеМожет устроит просто Условным форматированием отметить минимальные значения?
Или ещё вариант с доп. столбцом в исходной таблице и, соответственно, выводом в сводную

Автор - Pelena
Дата добавления - 28.01.2016 в 16:18
pabchek Дата: Четверг, 28.01.2016, 16:40 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 664
Репутация: 147 ±
Замечаний: 0% ±

Excel 2007
А я немного без сводной поизвращался. Недостаток, правда, есть - если минимальных значений больше одного, показывает первое. Можно нагромоздить и показать все. Также можно список "блюд" сделать динамичным. Не стал вышеуказанное делать, ибо не знаю надо ли)))
Но можно и формулами:
месяц (для D17)
Код
=ЕСЛИ(B17=0;СУММПРОИЗВ((МИН(C5:C16)=C5:C16)*B5:B16);"")

минимум (Е17)
Код
=ЕСЛИОШИБКА(ИНДЕКС(C5:C16;D17);"")
К сообщению приложен файл: 2907183.xlsx(33Kb)


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


Сообщение отредактировал pabchek - Четверг, 28.01.2016, 16:53
 
Ответить
СообщениеА я немного без сводной поизвращался. Недостаток, правда, есть - если минимальных значений больше одного, показывает первое. Можно нагромоздить и показать все. Также можно список "блюд" сделать динамичным. Не стал вышеуказанное делать, ибо не знаю надо ли)))
Но можно и формулами:
месяц (для D17)
Код
=ЕСЛИ(B17=0;СУММПРОИЗВ((МИН(C5:C16)=C5:C16)*B5:B16);"")

минимум (Е17)
Код
=ЕСЛИОШИБКА(ИНДЕКС(C5:C16;D17);"")

Автор - pabchek
Дата добавления - 28.01.2016 в 16:40
333 Дата: Четверг, 28.01.2016, 17:03 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

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

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

P.S. Была надежда что вопрос решается свойствами самой таблицы, но, видать, не в этом выпуске офиса.
 
Ответить
СообщениеPelena, YouGreed, спасибо за ответы!
Вариант с доп.столбцом для понимания сложнее, но по работе с ним, выходит, наверное, проще.

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

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

Автор - 333
Дата добавления - 28.01.2016 в 17:03
pabchek Дата: Четверг, 28.01.2016, 17:05 | Сообщение № 6
Группа: Проверенные
Ранг: Ветеран
Сообщений: 664
Репутация: 147 ±
Замечаний: 0% ±

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

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


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


Сообщение отредактировал pabchek - Четверг, 28.01.2016, 17:05
 
Ответить
Сообщение
начиная с пива в таблице что-то пошло не так

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

Автор - pabchek
Дата добавления - 28.01.2016 в 17:05
333 Дата: Четверг, 28.01.2016, 17:21 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
pabchek, ага, понятно. Можно только первый.
 
Ответить
Сообщениеpabchek, ага, понятно. Можно только первый.

Автор - 333
Дата добавления - 28.01.2016 в 17:21
pabchek Дата: Четверг, 28.01.2016, 17:33 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 664
Репутация: 147 ±
Замечаний: 0% ±

Excel 2007
Ну тогда наверно так
Код
=ЕСЛИ(B17=0;НАИМЕНЬШИЙ(C5:C16;1);"")

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

Колонки перепутал - перезалил
К сообщению приложен файл: 9547678.xlsx(35Kb)


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


Сообщение отредактировал pabchek - Четверг, 28.01.2016, 17:35
 
Ответить
СообщениеНу тогда наверно так
Код
=ЕСЛИ(B17=0;НАИМЕНЬШИЙ(C5:C16;1);"")

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

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

Автор - pabchek
Дата добавления - 28.01.2016 в 17:33
YouGreed Дата: Четверг, 28.01.2016, 17:33 | Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 429
Репутация: 93 ±
Замечаний: 0% ±

Excel 2010
333, Немного переделал вариант pabchek
Код
=ЕСЛИ(B43=0;ИНДЕКС(B31:B42;ПОИСКПОЗ(МИН(C31:C42);C31:C42;0););"")
для столбца D и все пойдет как нужно))

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


Сообщение отредактировал YouGreed - Четверг, 28.01.2016, 17:40
 
Ответить
Сообщение333, Немного переделал вариант pabchek
Код
=ЕСЛИ(B43=0;ИНДЕКС(B31:B42;ПОИСКПОЗ(МИН(C31:C42);C31:C42;0););"")
для столбца D и все пойдет как нужно))

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

Автор - YouGreed
Дата добавления - 28.01.2016 в 17:33
pabchek Дата: Четверг, 28.01.2016, 17:36 | Сообщение № 10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 664
Репутация: 147 ±
Замечаний: 0% ±

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

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


"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
Сообщение
Немного переделал

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

Автор - pabchek
Дата добавления - 28.01.2016 в 17:36
333 Дата: Четверг, 28.01.2016, 19:30 | Сообщение № 11
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

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


Сообщение отредактировал 333 - Четверг, 28.01.2016, 19:30
 
Ответить
СообщениеYouGreed, ваш вариант длиннее, зато, похоже, корректнее и универсальнее.
В последнем предложенном pabchek варианте формулы в первых строках дают ошибку, которая в моем случае на результате не сказывается )

Автор - 333
Дата добавления - 28.01.2016 в 19:30
333 Дата: Четверг, 28.01.2016, 20:06 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
pabchek, прошу пардону, обнаружил, что файл с моим примером не вполне корректный. Приблизил к реальности ))
Удастся ли использовать ваше решение если пиво, зайчики, девочки и мальчики на столе бывают не круглый год? ))
К сообщению приложен файл: 1855310.xlsx(29Kb)
 
Ответить
Сообщениеpabchek, прошу пардону, обнаружил, что файл с моим примером не вполне корректный. Приблизил к реальности ))
Удастся ли использовать ваше решение если пиво, зайчики, девочки и мальчики на столе бывают не круглый год? ))

Автор - 333
Дата добавления - 28.01.2016 в 20:06
_Boroda_ Дата: Четверг, 28.01.2016, 20:44 | Сообщение № 13
Группа: Модераторы
Ранг: Экселист
Сообщений: 9828
Репутация: 4151 ±
Замечаний: 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(31Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА почему девочки на столе не круглый год? Безобразие!!!
Минимум
Код
=МИН(C14:ИНДЕКС(C$5:C14;ПОИСКПОЗ(ПОДСТАВИТЬ(A15;" Итог";);A$5:A14;)))

Месяц
Код
=ПРОСМОТР(;-1/(C$5:C14=E15);B$5:B14)

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

Автор - _Boroda_
Дата добавления - 28.01.2016 в 20:44
pabchek Дата: Пятница, 29.01.2016, 10:04 | Сообщение № 14
Группа: Проверенные
Ранг: Ветеран
Сообщений: 664
Репутация: 147 ±
Замечаний: 0% ±

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

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


"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
Сообщение
в первых строках дают ошибку

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

Автор - pabchek
Дата добавления - 29.01.2016 в 10:04
pabchek Дата: Пятница, 29.01.2016, 11:11 | Сообщение № 15
Группа: Проверенные
Ранг: Ветеран
Сообщений: 664
Репутация: 147 ±
Замечаний: 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(35Kb)


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


Сообщение отредактировал pabchek - Пятница, 29.01.2016, 11:26
 
Ответить
СообщениеНу вот как-то так получилось
месяц
Код
=ЕСЛИОШИБКА(ИНДЕКС(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);"")

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

Автор - pabchek
Дата добавления - 29.01.2016 в 11:11
_Boroda_ Дата: Пятница, 29.01.2016, 11:31 | Сообщение № 16
Группа: Модераторы
Ранг: Экселист
Сообщений: 9828
Репутация: 4151 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
если в предыдущем пункте может оказаться число

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


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

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

Автор - _Boroda_
Дата добавления - 29.01.2016 в 11:31
pabchek Дата: Пятница, 29.01.2016, 11:44 | Сообщение № 17
Группа: Проверенные
Ранг: Ветеран
Сообщений: 664
Репутация: 147 ±
Замечаний: 0% ±

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

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


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


Сообщение отредактировал pabchek - Пятница, 29.01.2016, 11:47
 
Ответить
Сообщение
чем-то не устраивают?

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

Автор - pabchek
Дата добавления - 29.01.2016 в 11:44
pabchek Дата: Пятница, 29.01.2016, 11:58 | Сообщение № 18
Группа: Проверенные
Ранг: Ветеран
Сообщений: 664
Репутация: 147 ±
Замечаний: 0% ±

Excel 2007
И... раз уж допстолбцы, то почему бы и не допстроки :D
месяц
Код
=ЕСЛИОШИБКА(ИНДЕКС(ИНДЕКС(B:B;МАКС(ЕТЕКСТ(A2:A13)*СТРОКА(A2:A13))):B13;ПОИСКПОЗ(F14;ИНДЕКС(C:C;МАКС(ЕТЕКСТ(A2:A13)*СТРОКА(A2:A13))):C13;0));"")
К сообщению приложен файл: 1361884.xlsx(33Kb)


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


Сообщение отредактировал pabchek - Пятница, 29.01.2016, 12:00
 
Ответить
СообщениеИ... раз уж допстолбцы, то почему бы и не допстроки :D
месяц
Код
=ЕСЛИОШИБКА(ИНДЕКС(ИНДЕКС(B:B;МАКС(ЕТЕКСТ(A2:A13)*СТРОКА(A2:A13))):B13;ПОИСКПОЗ(F14;ИНДЕКС(C:C;МАКС(ЕТЕКСТ(A2:A13)*СТРОКА(A2:A13))):C13;0));"")

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

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