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

Вход

Регистрация

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

 

= Мир MS Excel/Сортировка списка - по наибольшему суммарному числу - Мир MS Excel

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

Excel 2019
Здравствуйте.
Помогите решить непростой вопрос.

На листе находятся два столбца исходных данных.
В одном идут числа, в другом указаны города.

Как отсортировать этот список по наибольшему суммарному числу для каждого города и выписать в таблицу EN4:EO ?
(Изначально таблица EN4:EO - пустая).
К сообщению приложен файл: 1798088.xlsx (13.2 Kb)
 
Ответить
СообщениеЗдравствуйте.
Помогите решить непростой вопрос.

На листе находятся два столбца исходных данных.
В одном идут числа, в другом указаны города.

Как отсортировать этот список по наибольшему суммарному числу для каждого города и выписать в таблицу EN4:EO ?
(Изначально таблица EN4:EO - пустая).
Автор - kNNeR
Дата добавления - 31.03.2021 в 19:16
Serge_007 Дата: Четверг, 01.04.2021, 09:04 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 16420
Репутация: 2740 ±
Замечаний: ±

Excel 2016
Здравствуйте

Сводной таблицей (см. вложение)
К сообщению приложен файл: 20210401_kNNeR.xls (42.0 Kb)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеЗдравствуйте

Сводной таблицей (см. вложение)
Автор - Serge_007
Дата добавления - 01.04.2021 в 09:04
kNNeR Дата: Четверг, 01.04.2021, 13:24 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 9 ±
Замечаний: 0% ±

Excel 2019
Serge_007, Спасибо.
Я меняю числа в списке - сортировочная таблица никак не реагирует.
Как это сделать сортировку - без сводной таблицы - одними формулами ?


Сообщение отредактировал kNNeR - Четверг, 01.04.2021, 13:45
 
Ответить
СообщениеSerge_007, Спасибо.
Я меняю числа в списке - сортировочная таблица никак не реагирует.
Как это сделать сортировку - без сводной таблицы - одними формулами ?
Автор - kNNeR
Дата добавления - 01.04.2021 в 13:24
Serge_007 Дата: Четверг, 01.04.2021, 14:15 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 16420
Репутация: 2740 ±
Замечаний: ±

Excel 2016
Цитата kNNeR, 01.04.2021 в 13:24, в сообщении № 3 ()
сортировочная таблица никак не реагирует
Нажмите кнопку "Обновить"

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата kNNeR, 01.04.2021 в 13:24, в сообщении № 3 ()
сортировочная таблица никак не реагирует
Нажмите кнопку "Обновить"
Автор - Serge_007
Дата добавления - 01.04.2021 в 14:15
kNNeR Дата: Четверг, 01.04.2021, 14:26 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 9 ±
Замечаний: 0% ±

Excel 2019
Serge_007, как это сделать без кнопок - обычными формулами ?
 
Ответить
СообщениеSerge_007, как это сделать без кнопок - обычными формулами ?
Автор - kNNeR
Дата добавления - 01.04.2021 в 14:26
Светлый Дата: Четверг, 01.04.2021, 19:07 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1797
Репутация: 497 ±
Замечаний: 0% ±

Excel 2013, 2016
Массивные формулы. Для города:
Код
=ЕСЛИОШИБКА(ИНДЕКС(EH:EH;ОСТАТ(АГРЕГАТ(14;6;ЕСЛИ((СЧЁТЕСЛИ(EN$3:EN3;EH$7:EH$156)=0)*(EH$7:EH$156>0);DX$7:DX$156+СТРОКА($7:$156)%%);1);1)/1%%);"")
Для величины:
Код
=ЕСЛИОШИБКА(АГРЕГАТ(14;6;ЕСЛИ((СЧЁТЕСЛИ(EN$3:EN3;EH$7:EH$156)=0)*(EH$7:EH$156>0);DX$7:DX$156);1);"")
К сообщению приложен файл: 1798088-1.xlsx (14.7 Kb)

Программировать проще, чем писать стихи.
 
Ответить
СообщениеМассивные формулы. Для города:
Код
=ЕСЛИОШИБКА(ИНДЕКС(EH:EH;ОСТАТ(АГРЕГАТ(14;6;ЕСЛИ((СЧЁТЕСЛИ(EN$3:EN3;EH$7:EH$156)=0)*(EH$7:EH$156>0);DX$7:DX$156+СТРОКА($7:$156)%%);1);1)/1%%);"")
Для величины:
Код
=ЕСЛИОШИБКА(АГРЕГАТ(14;6;ЕСЛИ((СЧЁТЕСЛИ(EN$3:EN3;EH$7:EH$156)=0)*(EH$7:EH$156>0);DX$7:DX$156);1);"")
Автор - Светлый
Дата добавления - 01.04.2021 в 19:07
kNNeR Дата: Четверг, 01.04.2021, 20:12 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 9 ±
Замечаний: 0% ±

Excel 2019
Светлый, спасибо большое.
Я имел ввиду сортировку по суммарному числу, а не просто по числу, которое напротив города стоит.

Вот там для параметра "Яр" в итоговой таблице - стоит число 170.
Хотя суммарно значение для города "Яр" должно быть показано 255.
(то есть это 170+50+35=255. Сумма трех городов из столбца, со значением "Яр")
 
Ответить
СообщениеСветлый, спасибо большое.
Я имел ввиду сортировку по суммарному числу, а не просто по числу, которое напротив города стоит.

Вот там для параметра "Яр" в итоговой таблице - стоит число 170.
Хотя суммарно значение для города "Яр" должно быть показано 255.
(то есть это 170+50+35=255. Сумма трех городов из столбца, со значением "Яр")
Автор - kNNeR
Дата добавления - 01.04.2021 в 20:12
Egyptian Дата: Четверг, 01.04.2021, 21:41 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 510
Репутация: 183 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата kNNeR, 01.04.2021 в 20:12, в сообщении № 7 ()
Я имел ввиду сортировку по суммарному числу

Думаю, это довольно таки нетривиальная задачка. Ведь формуле сначала надо найти города в массиве, затем просуммировать соответствующие им значения и только потом выводить их отсортированными по наибольшей сумме. Как вариант можно вывести города без повторений, и в соседнем столбце их общую сумму. Сортировка при этом по сравению со сводной будет неполной.
К сообщению приложен файл: 0913148.xls (45.5 Kb)
 
Ответить
Сообщение
Цитата kNNeR, 01.04.2021 в 20:12, в сообщении № 7 ()
Я имел ввиду сортировку по суммарному числу

Думаю, это довольно таки нетривиальная задачка. Ведь формуле сначала надо найти города в массиве, затем просуммировать соответствующие им значения и только потом выводить их отсортированными по наибольшей сумме. Как вариант можно вывести города без повторений, и в соседнем столбце их общую сумму. Сортировка при этом по сравению со сводной будет неполной.
Автор - Egyptian
Дата добавления - 01.04.2021 в 21:41
kNNeR Дата: Четверг, 01.04.2021, 22:16 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 9 ±
Замечаний: 0% ±

Excel 2019
Egyptian, в вашем файле - значения в таблице не рассортированы по-убыванию.
 
Ответить
СообщениеEgyptian, в вашем файле - значения в таблице не рассортированы по-убыванию.
Автор - kNNeR
Дата добавления - 01.04.2021 в 22:16
Egyptian Дата: Четверг, 01.04.2021, 22:18 | Сообщение № 10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 510
Репутация: 183 ±
Замечаний: 0% ±

Excel 2013/2016
Я писал об этом.
Цитата Egyptian, 01.04.2021 в 21:41, в сообщении № 8 ()
Сортировка при этом по сравению со сводной будет неполной.
 
Ответить
СообщениеЯ писал об этом.
Цитата Egyptian, 01.04.2021 в 21:41, в сообщении № 8 ()
Сортировка при этом по сравению со сводной будет неполной.
Автор - Egyptian
Дата добавления - 01.04.2021 в 22:18
kNNeR Дата: Четверг, 01.04.2021, 22:22 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 9 ±
Замечаний: 0% ±

Excel 2019
Egyptian, понятно.
Но я спрашивал именно про сортировку.
 
Ответить
СообщениеEgyptian, понятно.
Но я спрашивал именно про сортировку.
Автор - kNNeR
Дата добавления - 01.04.2021 в 22:22
Светлый Дата: Четверг, 01.04.2021, 23:02 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1797
Репутация: 497 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата kNNeR, 01.04.2021 в 20:12, в сообщении № 7 ()
сортировку по суммарному числу
Тогда так (массивная):
Код
=ЕСЛИ(EN3>0;ИНДЕКС(EH:EH;ОСТАТ(МАКС(ЕСЛИ(СЧЁТЕСЛИ(EN$3:EN3;EH$7:EH$156)=0;СУММЕСЛИ(EH:EH;ЕСЛИ((СЧЁТЕСЛИ(EN$3:EN3;EH$7:EH$156)=0)*(EH$7:EH$156<>"");EH$7:EH$156);DX:DX)+СТРОКА($7:$156)%%));1)/1%%);)
Код
=СУММЕСЛИ(EH:EH;EN4;DX:DX)

Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Цитата kNNeR, 01.04.2021 в 20:12, в сообщении № 7 ()
сортировку по суммарному числу
Тогда так (массивная):
Код
=ЕСЛИ(EN3>0;ИНДЕКС(EH:EH;ОСТАТ(МАКС(ЕСЛИ(СЧЁТЕСЛИ(EN$3:EN3;EH$7:EH$156)=0;СУММЕСЛИ(EH:EH;ЕСЛИ((СЧЁТЕСЛИ(EN$3:EN3;EH$7:EH$156)=0)*(EH$7:EH$156<>"");EH$7:EH$156);DX:DX)+СТРОКА($7:$156)%%));1)/1%%);)
Код
=СУММЕСЛИ(EH:EH;EN4;DX:DX)
Автор - Светлый
Дата добавления - 01.04.2021 в 23:02
Egyptian Дата: Четверг, 01.04.2021, 23:06 | Сообщение № 13
Группа: Проверенные
Ранг: Ветеран
Сообщений: 510
Репутация: 183 ±
Замечаний: 0% ±

Excel 2013/2016
Вот, кажется получилось. Проверяйте.
Код
=INDEX($EH$7:$EH$156;MATCH(1;(COUNTIF($EN$3:EN3;$EH$7:$EH$156)=0)*($EH$7:$EH$156>0)*(LARGE(IF(($EH$7:$EH$156>0);SUMIF($EH$7:$EH$156;$EH$7:$EH$156;$DX$7:$DX$156)*(MATCH($EH$7:$EH$156;$EH$7:$EH$156;0)=ROW($EH$7:$EH$156)-ROW(EH$7)+1));ROW(A1))=SUMIF($EH$7:$EH$156;$EH$7:$EH$156;$DX$7:$DX$156));0))
К сообщению приложен файл: 4614996.xls (50.0 Kb)


Сообщение отредактировал Egyptian - Четверг, 01.04.2021, 23:07
 
Ответить
СообщениеВот, кажется получилось. Проверяйте.
Код
=INDEX($EH$7:$EH$156;MATCH(1;(COUNTIF($EN$3:EN3;$EH$7:$EH$156)=0)*($EH$7:$EH$156>0)*(LARGE(IF(($EH$7:$EH$156>0);SUMIF($EH$7:$EH$156;$EH$7:$EH$156;$DX$7:$DX$156)*(MATCH($EH$7:$EH$156;$EH$7:$EH$156;0)=ROW($EH$7:$EH$156)-ROW(EH$7)+1));ROW(A1))=SUMIF($EH$7:$EH$156;$EH$7:$EH$156;$DX$7:$DX$156));0))
Автор - Egyptian
Дата добавления - 01.04.2021 в 23:06
kNNeR Дата: Четверг, 01.04.2021, 23:29 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 9 ±
Замечаний: 0% ±

Excel 2019
kNNeR, Светлый, Egyptian, спасибо.
Вроде все работает.
 
Ответить
СообщениеkNNeR, Светлый, Egyptian, спасибо.
Вроде все работает.
Автор - kNNeR
Дата добавления - 01.04.2021 в 23:29
Светлый Дата: Пятница, 02.04.2021, 08:12 | Сообщение № 15
Группа: Друзья
Ранг: Старожил
Сообщений: 1797
Репутация: 497 ±
Замечаний: 0% ±

Excel 2013, 2016
Ещё поколдовал немного. Массивная:
Код
=ЕСЛИ(EN3>0;ИНДЕКС(EH:EH;ОСТАТ(НАИБОЛЬШИЙ(СУММЕСЛИ(EH$7:EH$156;EH$7:EH$156;DX$7:DX$156)+СТРОКА($7:$156)%%;СУММ(СЧЁТЕСЛИ(EN$3:EN3;EH$7:EH$156))+1);1)/1%%);)

Программировать проще, чем писать стихи.
 
Ответить
СообщениеЕщё поколдовал немного. Массивная:
Код
=ЕСЛИ(EN3>0;ИНДЕКС(EH:EH;ОСТАТ(НАИБОЛЬШИЙ(СУММЕСЛИ(EH$7:EH$156;EH$7:EH$156;DX$7:DX$156)+СТРОКА($7:$156)%%;СУММ(СЧЁТЕСЛИ(EN$3:EN3;EH$7:EH$156))+1);1)/1%%);)
Автор - Светлый
Дата добавления - 02.04.2021 в 08:12
Egyptian Дата: Пятница, 02.04.2021, 09:49 | Сообщение № 16
Группа: Проверенные
Ранг: Ветеран
Сообщений: 510
Репутация: 183 ±
Замечаний: 0% ±

Excel 2013/2016
Светлый, Это отличный вариант! Думаю, надо бы добавить проверку
Код
LARGE(IF(EH$7:EH$156>0.....
иначе не выводится Оренбургская область с нулевым показателем.
 
Ответить
СообщениеСветлый, Это отличный вариант! Думаю, надо бы добавить проверку
Код
LARGE(IF(EH$7:EH$156>0.....
иначе не выводится Оренбургская область с нулевым показателем.
Автор - Egyptian
Дата добавления - 02.04.2021 в 09:49
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сортировка списка - по наибольшему суммарному числу (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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