Сосчитать популярные числа в столбце, вывести его и название

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

stalber

Дата: Среда, 31.10.2018, 18:42 | Сообщение № 1

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

Ранг: Обитатель

Сообщений: 371

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

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

Excel 2021

Добрый день, помогите пожалуйста.

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

Проще на примере объяснить, файл приложил, я в нём тоже пояснил, что мне необходимо в итоге увидеть.

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

Ответить

Светлый

Дата: Четверг, 01.11.2018, 00:02 | Сообщение № 2

Группа: Друзья

Ранг: Старожил

Сообщений: 1868

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

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

Excel 2013, 2016

Решение не совсем корректное и не универсальное. Формулы массивов:

Код

=НАИБОЛЬШИЙ(ВЫБОР({1;2;3;4};СЧЁТЕСЛИ(Данные!H$2:H$19;Данные!H$2:H$19)*(ПОИСКПОЗ(Данные!H$2:H$19;Данные!H$2:H$19;)=СТРОКА($1:$18))*(Данные!H$2:H$19<>0);СЧЁТЕСЛИ(Данные!Q$2:Q$19;Данные!Q$2:Q$19)*(ПОИСКПОЗ(Данные!Q$2:Q$19;Данные!Q$2:Q$19;)=СТРОКА($1:$18))*(Данные!Q$2:Q$19<>0);СЧЁТЕСЛИ(Данные!Z$2:Z$19;Данные!Z$2:Z$19)*(ПОИСКПОЗ(Данные!Z$2:Z$19;Данные!Z$2:Z$19;)=СТРОКА($1:$18))*(Данные!Z$2:Z$19<>0);СЧЁТЕСЛИ(Данные!AI$2:AI$19;Данные!AI$2:AI$19)*(ПОИСКПОЗ(Данные!AI$2:AI$19;Данные!AI$2:AI$19;)=СТРОКА($1:$18))*(Данные!AI$2:AI$19<>0));СТРОКА(E1))

Код

=МАКС((МАКС(ВЫБОР({1;2;3;4};СЧЁТЕСЛИ(Данные!H$2:H$19;Данные!H$2:H$19)*(ПОИСКПОЗ(Данные!H$2:H$19;Данные!H$2:H$19;)=СТРОКА($1:$18))*(Данные!H$2:H$19<>0)*ЕНД(ПОИСКПОЗ(Данные!H$2:H$19+1%%%;F$9:F9;));СЧЁТЕСЛИ(Данные!Q$2:Q$19;Данные!Q$2:Q$19)*(ПОИСКПОЗ(Данные!Q$2:Q$19;Данные!Q$2:Q$19;)=СТРОКА($1:$18))*(Данные!Q$2:Q$19<>0)*ЕНД(ПОИСКПОЗ(Данные!Q$2:Q$19+2%%%;F$9:F9;));СЧЁТЕСЛИ(Данные!Z$2:Z$19;Данные!Z$2:Z$19)*(ПОИСКПОЗ(Данные!Z$2:Z$19;Данные!Z$2:Z$19;)=СТРОКА($1:$18))*(Данные!Z$2:Z$19<>0)*ЕНД(ПОИСКПОЗ(Данные!Z$2:Z$19+3%%%;F$9:F9;));СЧЁТЕСЛИ(Данные!AI$2:AI$19;Данные!AI$2:AI$19)*(ПОИСКПОЗ(Данные!AI$2:AI$19;Данные!AI$2:AI$19;)=СТРОКА($1:$18))*(Данные!AI$2:AI$19<>0)*ЕНД(ПОИСКПОЗ(Данные!AI$2:AI$19+4%%%;F$9:F9;))))=ВЫБОР({1;2;3;4};СЧЁТЕСЛИ(Данные!H$2:H$19;Данные!H$2:H$19)*(ПОИСКПОЗ(Данные!H$2:H$19;Данные!H$2:H$19;)=СТРОКА($1:$18))*(Данные!H$2:H$19<>0)*ЕНД(ПОИСКПОЗ(Данные!H$2:H$19+1%%%;F$9:F9;));СЧЁТЕСЛИ(Данные!Q$2:Q$19;Данные!Q$2:Q$19)*(ПОИСКПОЗ(Данные!Q$2:Q$19;Данные!Q$2:Q$19;)=СТРОКА($1:$18))*(Данные!Q$2:Q$19<>0)*ЕНД(ПОИСКПОЗ(Данные!Q$2:Q$19+2%%%;F$9:F9;));СЧЁТЕСЛИ(Данные!Z$2:Z$19;Данные!Z$2:Z$19)*(ПОИСКПОЗ(Данные!Z$2:Z$19;Данные!Z$2:Z$19;)=СТРОКА($1:$18))*(Данные!Z$2:Z$19<>0)*ЕНД(ПОИСКПОЗ(Данные!Z$2:Z$19+3%%%;F$9:F9;));СЧЁТЕСЛИ(Данные!AI$2:AI$19;Данные!AI$2:AI$19)*(ПОИСКПОЗ(Данные!AI$2:AI$19;Данные!AI$2:AI$19;)=СТРОКА($1:$18))*(Данные!AI$2:AI$19<>0)*ЕНД(ПОИСКПОЗ(Данные!AI$2:AI$19+4%%%;F$9:F9;))))*ВЫБОР({1;2;3;4};Данные!H$2:H$19+1%%%;Данные!Q$2:Q$19+2%%%;Данные!Z$2:Z$19+3%%%;Данные!AI$2:AI$19+4%%%))

И простая:

Код

=ВЫБОР(ПРАВБ(F10);Данные!C$2;Данные!L$2;Данные!U$2;Данные!AD$2)

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

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

Ответить

stalber

Дата: Понедельник, 05.11.2018, 13:51 | Сообщение № 3

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

Ранг: Обитатель

Сообщений: 371

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

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

Excel 2021

В примере 4 данных, в оригинальном файле данных за 100 в связи с чем формула очень больших размеров получится.
Предложите пожалуйста ещё какие нибудь решения данной задачи.

Ответить

InExSu

Дата: Среда, 07.11.2018, 07:24 | Сообщение № 4

Группа: Друзья

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

Сообщений: 650

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

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

Excel 2010, 365

Привет!

Цитата stalber, 05.11.2018 в 13:51, в сообщении № 3 ()

В примере 4 данных, в оригинальном файле данных за 100

Столбцов что ли?

Разработчик Битрикс24 php, Google Apps Script, VBA Excel Windows/Mac

Ответить

stalber

Дата: Среда, 07.11.2018, 12:06 | Сообщение № 5

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

Ранг: Обитатель

Сообщений: 371

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

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

Excel 2021

да столбцов больше 100

Ответить

InExSu

Дата: Среда, 07.11.2018, 13:09 | Сообщение № 6

Группа: Друзья

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

Сообщений: 650

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

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

Excel 2010, 365

stalber,
Сделал Вам прототип, который превращает Ваши данные в данные, которые понимает Excel. А из этих данных Вы сделаете нужную сводную.

К сообщению приложен файл: ___stalber.xlsb (28.7 Kb)

Разработчик Битрикс24 php, Google Apps Script, VBA Excel Windows/Mac

Ответить

Светлый

Дата: Четверг, 08.11.2018, 07:52 | Сообщение № 7

Группа: Друзья

Ранг: Старожил

Сообщений: 1868

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

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

Excel 2013, 2016

Сделал универсальные массивные формулы:

Код

=НАИБОЛЬШИЙ(МУМНОЖ(Ч(Ч(СМЕЩ(Данные!A$2;ОСТАТ(СТРОКА($20:$99);20);ОТБР(СТРОКА($20:$99)/20)*9-2))=ТРАНСП(Ч(СМЕЩ(Данные!A$2;ОСТАТ(СТРОКА($20:$99);20);ОТБР(СТРОКА($20:$99)/20)*9-2))));Ч(Ч(СМЕЩ(Данные!A$2;ОСТАТ(СТРОКА($20:$99);20);ОТБР(СТРОКА($20:$99)/20)*9-2))<>0))*(ПОИСКПОЗ(Ч(СМЕЩ(Данные!A$2;ОСТАТ(СТРОКА($20:$99);20);ОТБР(СТРОКА($20:$99)/20)*9-2));Ч(СМЕЩ(Данные!A$2;ОСТАТ(СТРОКА($20:$99);20);ОТБР(СТРОКА($20:$99)/20)*9-2));)=СТРОКА($1:$80))+СТРОКА($1:$80)%%%;СТРОКА(E1))

Код

=ИНДЕКС(Ч(СМЕЩ(Данные!A$2;ОСТАТ(СТРОКА($20:$99);20);ОТБР(СТРОКА($20:$99)/20)*9-2));ОСТАТ(E10;1)/1%%%+1%)

*Или эту формулу вместо первой:

Код

=НАИБОЛЬШИЙ(ЧАСТОТА(ПОИСКПОЗ(Ч(СМЕЩ(Данные!A$2;ОСТАТ(СТРОКА($20:$199);20);ОТБР(СТРОКА($20:$199)/20)*9-2));Ч(СМЕЩ(Данные!A$2;ОСТАТ(СТРОКА($20:$199);20);ОТБР(СТРОКА($20:$199)/20)*9-2)););СТРОКА($1:$180))+СТРОКА($1:$181)%%%;СТРОКА(E2))

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

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

Сообщение отредактировал Светлый - Четверг, 08.11.2018, 08:40

Ответить

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