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

Вход

Регистрация

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

 

= Мир MS Excel/Сортировка списка по числу вхождений - Мир MS Excel

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

Excel 2016
Добрый день!
Пробовал решить задачу с помощью собственных знаний, но получилось лишь частично
Есть таблица с перечнем повторяющихся значений (со временем будут добавляться новые значения).
Есть вторая таблица где нужно вывести в порядке убывания эти значения по их количеству.
К сообщению приложен файл: 4279147.xlsx (10.0 Kb)
 
Ответить
СообщениеДобрый день!
Пробовал решить задачу с помощью собственных знаний, но получилось лишь частично
Есть таблица с перечнем повторяющихся значений (со временем будут добавляться новые значения).
Есть вторая таблица где нужно вывести в порядке убывания эти значения по их количеству.

Автор - TSC
Дата добавления - 12.01.2021 в 09:40
Serge_007 Дата: Вторник, 12.01.2021, 09:45 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

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

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


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

Сводной таблицей (см. вложение)

Автор - Serge_007
Дата добавления - 12.01.2021 в 09:45
TSC Дата: Вторник, 12.01.2021, 09:56 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Сводной таблицей


К сожалению, сводная таблица не подходит по техническим ограничениям )
Необходимо решение именно формулой без встроенных опций сортировки


Сообщение отредактировал TSC - Вторник, 12.01.2021, 09:56
 
Ответить
Сообщение
Сводной таблицей


К сожалению, сводная таблица не подходит по техническим ограничениям )
Необходимо решение именно формулой без встроенных опций сортировки

Автор - TSC
Дата добавления - 12.01.2021 в 09:56
TSC Дата: Вторник, 12.01.2021, 15:04 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Видимо, напрямую без костылей это сделать невозможно
 
Ответить
СообщениеВидимо, напрямую без костылей это сделать невозможно

Автор - TSC
Дата добавления - 12.01.2021 в 15:04
Serge_007 Дата: Вторник, 12.01.2021, 15:17 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

Excel 2016
без костылей это сделать невозможно
Почти
Формула получается довольно сложная
Например так:
Код
=ИНДЕКС(A$2:A$54;ПОИСКПОЗ(НАИБОЛЬШИЙ(ЕСЛИ(ПОИСКПОЗ(A$2:A$54;A$2:A$54;)=СТРОКА($1:$53);СЧЁТЕСЛИ(A$2:A$54;A$2:A$54));СТРОКА(A1));ЕСЛИ(ПОИСКПОЗ(A$2:A$54;A$2:A$54;)=СТРОКА($1:$53);СЧЁТЕСЛИ(A$2:A$54;A$2:A$54));))

Но, если есть более одного города с одинаковым кол-вом записей, то будет повторяться первый

сводная таблица не подходит по техническим ограничениям
Что это за ограничения?
К сообщению приложен файл: 20210112_TSCv2.xls (26.0 Kb)


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
без костылей это сделать невозможно
Почти
Формула получается довольно сложная
Например так:
Код
=ИНДЕКС(A$2:A$54;ПОИСКПОЗ(НАИБОЛЬШИЙ(ЕСЛИ(ПОИСКПОЗ(A$2:A$54;A$2:A$54;)=СТРОКА($1:$53);СЧЁТЕСЛИ(A$2:A$54;A$2:A$54));СТРОКА(A1));ЕСЛИ(ПОИСКПОЗ(A$2:A$54;A$2:A$54;)=СТРОКА($1:$53);СЧЁТЕСЛИ(A$2:A$54;A$2:A$54));))

Но, если есть более одного города с одинаковым кол-вом записей, то будет повторяться первый

сводная таблица не подходит по техническим ограничениям
Что это за ограничения?

Автор - Serge_007
Дата добавления - 12.01.2021 в 15:17
TSC Дата: Пятница, 15.01.2021, 15:55 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Формула получается довольно сложная

Для меня уж тем более сложная, плохо лажу с формулами массива

Но, если есть более одного города с одинаковым кол-вом записей, то будет повторяться первый


Да, именно этого я и боялся (

Что это за ограничения?


Ограничения по вёрстке листа. Это отчёт, он идёт на печать, и кроме этой таблицы там много чего ещё. Сводная таблица рушит всю вёрстку



На другом листе были служебные таблицы, поэтому создал там ещё одну и буду просто сортировать. Хоть и костыль, но пока лучше не придумал
В любом случае, спасибо за помощь!
 
Ответить
Сообщение
Формула получается довольно сложная

Для меня уж тем более сложная, плохо лажу с формулами массива

Но, если есть более одного города с одинаковым кол-вом записей, то будет повторяться первый


Да, именно этого я и боялся (

Что это за ограничения?


Ограничения по вёрстке листа. Это отчёт, он идёт на печать, и кроме этой таблицы там много чего ещё. Сводная таблица рушит всю вёрстку



На другом листе были служебные таблицы, поэтому создал там ещё одну и буду просто сортировать. Хоть и костыль, но пока лучше не придумал
В любом случае, спасибо за помощь!

Автор - TSC
Дата добавления - 15.01.2021 в 15:55
Serge_007 Дата: Пятница, 15.01.2021, 16:36 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

Excel 2016
Сводная таблица рушит всю вёрстку
Не понял, как она может рушить?..
Сводная статична, меняется только кол-во строк. Если их стало меньше - будут пустые (их можно убрать), если больше - можно скрыть. Так же можно зафиксировать верхнюю границу, например "Первые 21", как у Вас на скрине

Более того, весь Ваш слайд можно сделать из одной сводной таблицы и двух сводных диаграмм (гистограммы и круговой), которые построены на данных сводной таблицы
В данном случае "костыль" - это попытка использовать формулы, там, где они не нужны :D


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Сводная таблица рушит всю вёрстку
Не понял, как она может рушить?..
Сводная статична, меняется только кол-во строк. Если их стало меньше - будут пустые (их можно убрать), если больше - можно скрыть. Так же можно зафиксировать верхнюю границу, например "Первые 21", как у Вас на скрине

Более того, весь Ваш слайд можно сделать из одной сводной таблицы и двух сводных диаграмм (гистограммы и круговой), которые построены на данных сводной таблицы
В данном случае "костыль" - это попытка использовать формулы, там, где они не нужны :D

Автор - Serge_007
Дата добавления - 15.01.2021 в 16:36
bmv98rus Дата: Пятница, 15.01.2021, 20:29 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4098
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Если позволите, медведь на уши наступит с
Код
=INDEX(Города[ГОРОДА];MATCH(MAX(COUNTIF(Города[ГОРОДА];IF(ISNA(MATCH(Города[ГОРОДА];$E$1:E1;));Города[ГОРОДА])));COUNTIF(Города[ГОРОДА];IF(ISNA(MATCH(Города[ГОРОДА];$E$1:E1;));Города[ГОРОДА]));))
К сообщению приложен файл: example2061.xlsx (10.7 Kb)


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеЕсли позволите, медведь на уши наступит с
Код
=INDEX(Города[ГОРОДА];MATCH(MAX(COUNTIF(Города[ГОРОДА];IF(ISNA(MATCH(Города[ГОРОДА];$E$1:E1;));Города[ГОРОДА])));COUNTIF(Города[ГОРОДА];IF(ISNA(MATCH(Города[ГОРОДА];$E$1:E1;));Города[ГОРОДА]));))

Автор - bmv98rus
Дата добавления - 15.01.2021 в 20:29
Светлый Дата: Понедельник, 18.01.2021, 11:55 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Ещё вариант массивной формулы:
Код
=ИНДЕКС(A:A;ПРАВБ(МАКС(СЧЁТЕСЛИ(A$2:A$54;A$2:A$54)*(СЧЁТЕСЛИ(E$1:E1;A$2:A$54)=0)*1000+СТРОКА($2:$54));3))
*Или с умной таблицей:
Код
=ИНДЕКС(A:A;ПРАВБ(МАКС(СЧЁТЕСЛИ(Города[ГОРОДА];Города[ГОРОДА])*(СЧЁТЕСЛИ(E$1:E1;Города[ГОРОДА])=0)*1000+СТРОКА(Города[ГОРОДА]));3))


Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Понедельник, 18.01.2021, 12:06
 
Ответить
СообщениеЕщё вариант массивной формулы:
Код
=ИНДЕКС(A:A;ПРАВБ(МАКС(СЧЁТЕСЛИ(A$2:A$54;A$2:A$54)*(СЧЁТЕСЛИ(E$1:E1;A$2:A$54)=0)*1000+СТРОКА($2:$54));3))
*Или с умной таблицей:
Код
=ИНДЕКС(A:A;ПРАВБ(МАКС(СЧЁТЕСЛИ(Города[ГОРОДА];Города[ГОРОДА])*(СЧЁТЕСЛИ(E$1:E1;Города[ГОРОДА])=0)*1000+СТРОКА(Города[ГОРОДА]));3))

Автор - Светлый
Дата добавления - 18.01.2021 в 11:55
TSC Дата: Вторник, 19.01.2021, 16:42 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Не понял, как она может рушить?..

Согласен с вами, но... отчёт намного больше, чем тот фрагмент, который я показал и там разные таблицы выводятся, с разной вёрсткой, так сказать (вывод на 2 листа А4). Поэтому сам лист, если вы заметите, со столбцами шириной 20px. Просто так вставить сводную таблицу не получается, данные не видны. А если вставлять сводную и расширять столбцы, то херятся другие отчётные таблицы и возникает много головной боли ) Так что я рассматривал свой случай именно таким, когда формулы очень кстати.
Возможно и тут вы меня поправите )

Светлый, bmv98rus, спасибо ) поиграю с вашими решениями чуть позже, когда время чуть освобожу от дел )


Сообщение отредактировал TSC - Вторник, 19.01.2021, 16:44
 
Ответить
Сообщение
Не понял, как она может рушить?..

Согласен с вами, но... отчёт намного больше, чем тот фрагмент, который я показал и там разные таблицы выводятся, с разной вёрсткой, так сказать (вывод на 2 листа А4). Поэтому сам лист, если вы заметите, со столбцами шириной 20px. Просто так вставить сводную таблицу не получается, данные не видны. А если вставлять сводную и расширять столбцы, то херятся другие отчётные таблицы и возникает много головной боли ) Так что я рассматривал свой случай именно таким, когда формулы очень кстати.
Возможно и тут вы меня поправите )

Светлый, bmv98rus, спасибо ) поиграю с вашими решениями чуть позже, когда время чуть освобожу от дел )

Автор - TSC
Дата добавления - 19.01.2021 в 16:42
Serge_007 Дата: Вторник, 19.01.2021, 17:02 | Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

Excel 2016
Просто так вставить сводную таблицу не получается, данные не видны
У Вас на скрине слева таблица, её можно заменить сводной...


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Просто так вставить сводную таблицу не получается, данные не видны
У Вас на скрине слева таблица, её можно заменить сводной...

Автор - Serge_007
Дата добавления - 19.01.2021 в 17:02
TSC Дата: Четверг, 28.01.2021, 13:05 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Дошли руки посмотреть формулы... Формулы массива нельзя вставлять в объединенные ячейки. Печалька :(
Придётся по старинке
Спасибо всем откликнувшимся в теме
 
Ответить
СообщениеДошли руки посмотреть формулы... Формулы массива нельзя вставлять в объединенные ячейки. Печалька :(
Придётся по старинке
Спасибо всем откликнувшимся в теме

Автор - TSC
Дата добавления - 28.01.2021 в 13:05
Serge_007 Дата: Четверг, 28.01.2021, 13:10 | Сообщение № 13
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

Excel 2016
Формулы массива нельзя вставлять в объединенные ячейки
Можно. Не верьте алерту в Excel
К сообщению приложен файл: 20210128_TSC.xls (26.5 Kb)


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Формулы массива нельзя вставлять в объединенные ячейки
Можно. Не верьте алерту в Excel

Автор - Serge_007
Дата добавления - 28.01.2021 в 13:10
TSC Дата: Вторник, 02.02.2021, 10:27 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 22
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Можно. Не верьте алерту в Excel


Спасибо, буду знать)

Проверил формулы, они работают, но всё тяжко в плане производительности. У меня сейчас 25 городов и 5000 строк в таблице, таблица будет расти. При открытии файла Excel задумывается на минуту, в процессе работы тоже бывают подвисания. При том, что комп не слабый (Core i5-9400F, 32Gb RAM). Понимаю, что алгоритм расчетов через формулу не такой, как через сводную таблицу. Так что тут никаких претензий нет, ибо Excel это всё же не СУБД.

Пошёл старым путём. Т.к. я не знаток Excel, то просто использовал служебную таблицу с городами (она уже была для других нужд), где добавился столбец подсчета количества анкет по каждому городу через СЧЕТЕСЛИМН.
В отчёте вывел количество анкет по убыванию через НАИБОЛЬШИЙ, а названия городов вывел через ВПР к количеству анкет. А проблему задвоения города при одинаковом количестве анкет я решил через костыль: в служебной таблице я к значению количества анкет прибавил постоянное (но разное для разных городов) число малого порядка СЧЕТЕСЛИМН(...)+СТРОКА()/1000000, поэтому числа никогда не совпадут и задвоения городов при одинаковом количестве анкет не будет. Не знаю, пойдёт ли кровь из глаз у гуру от такого решения ^_^

Конечно, в будущем этот костыль планирую заменить, я даже нашёл на просторах интернетов решение, но там надо напрягать мозг, чего в данный момент времени не могу себе позволить по уважительным причинам %)
 
Ответить
Сообщение
Можно. Не верьте алерту в Excel


Спасибо, буду знать)

Проверил формулы, они работают, но всё тяжко в плане производительности. У меня сейчас 25 городов и 5000 строк в таблице, таблица будет расти. При открытии файла Excel задумывается на минуту, в процессе работы тоже бывают подвисания. При том, что комп не слабый (Core i5-9400F, 32Gb RAM). Понимаю, что алгоритм расчетов через формулу не такой, как через сводную таблицу. Так что тут никаких претензий нет, ибо Excel это всё же не СУБД.

Пошёл старым путём. Т.к. я не знаток Excel, то просто использовал служебную таблицу с городами (она уже была для других нужд), где добавился столбец подсчета количества анкет по каждому городу через СЧЕТЕСЛИМН.
В отчёте вывел количество анкет по убыванию через НАИБОЛЬШИЙ, а названия городов вывел через ВПР к количеству анкет. А проблему задвоения города при одинаковом количестве анкет я решил через костыль: в служебной таблице я к значению количества анкет прибавил постоянное (но разное для разных городов) число малого порядка СЧЕТЕСЛИМН(...)+СТРОКА()/1000000, поэтому числа никогда не совпадут и задвоения городов при одинаковом количестве анкет не будет. Не знаю, пойдёт ли кровь из глаз у гуру от такого решения ^_^

Конечно, в будущем этот костыль планирую заменить, я даже нашёл на просторах интернетов решение, но там надо напрягать мозг, чего в данный момент времени не могу себе позволить по уважительным причинам %)

Автор - TSC
Дата добавления - 02.02.2021 в 10:27
Serge_007 Дата: Вторник, 02.02.2021, 10:53 | Сообщение № 15
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

Excel 2016
TSC, можно исходные данные поместить в БД (Access, файл Excel CSV, блокнот и т.п) через Power Qwery возвращать в рабочий файл уже обработанные данные, раз уж решение сводной Вы не хотите)


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеTSC, можно исходные данные поместить в БД (Access, файл Excel CSV, блокнот и т.п) через Power Qwery возвращать в рабочий файл уже обработанные данные, раз уж решение сводной Вы не хотите)

Автор - Serge_007
Дата добавления - 02.02.2021 в 10:53
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сортировка списка по числу вхождений (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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