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

Вход

Регистрация

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

 

= Мир MS Excel/Сосчитать популярные числа в столбце, вывести его и название - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сосчитать популярные числа в столбце, вывести его и название (Формулы/Formulas)
Сосчитать популярные числа в столбце, вывести его и название
stalber Дата: Среда, 31.10.2018, 18:42 | Сообщение № 1
Группа: Проверенные
Ранг: Обитатель
Сообщений: 369
Репутация: 7 ±
Замечаний: 40% ±

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

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

Проще на примере объяснить, файл приложил, я в нём тоже пояснил, что мне необходимо в итоге увидеть.
К сообщению приложен файл: zadacha_3110201.xlsx (14.5 Kb)
 
Ответить
СообщениеДобрый день, помогите пожалуйста.

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

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

Автор - stalber
Дата добавления - 31.10.2018 в 18:42
Светлый Дата: Четверг, 01.11.2018, 00:02 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 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)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеРешение не совсем корректное и не универсальное. Формулы массивов:
Код
=НАИБОЛЬШИЙ(ВЫБОР({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)

Автор - Светлый
Дата добавления - 01.11.2018 в 00:02
stalber Дата: Понедельник, 05.11.2018, 13:51 | Сообщение № 3
Группа: Проверенные
Ранг: Обитатель
Сообщений: 369
Репутация: 7 ±
Замечаний: 40% ±

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

Автор - stalber
Дата добавления - 05.11.2018 в 13:51
InExSu Дата: Среда, 07.11.2018, 07:24 | Сообщение № 4
Группа: Друзья
Ранг: Ветеран
Сообщений: 648
Репутация: 96 ±
Замечаний: 0% ±

Excel 2010, 365
Привет!
В примере 4 данных, в оригинальном файле данных за 100

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


Разработчик Битрикс24 php, Google Apps Script, VBA Excel Windows/Mac
 
Ответить
СообщениеПривет!
В примере 4 данных, в оригинальном файле данных за 100

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

Автор - InExSu
Дата добавления - 07.11.2018 в 07:24
stalber Дата: Среда, 07.11.2018, 12:06 | Сообщение № 5
Группа: Проверенные
Ранг: Обитатель
Сообщений: 369
Репутация: 7 ±
Замечаний: 40% ±

Excel 2007
да столбцов больше 100
 
Ответить
Сообщениеда столбцов больше 100

Автор - stalber
Дата добавления - 07.11.2018 в 12:06
InExSu Дата: Среда, 07.11.2018, 13:09 | Сообщение № 6
Группа: Друзья
Ранг: Ветеран
Сообщений: 648
Репутация: 96 ±
Замечаний: 0% ±

Excel 2010, 365
stalber,
Сделал Вам прототип, который превращает Ваши данные в данные, которые понимает Excel. А из этих данных Вы сделаете нужную сводную.
К сообщению приложен файл: ___stalber.xlsb (28.7 Kb)


Разработчик Битрикс24 php, Google Apps Script, VBA Excel Windows/Mac
 
Ответить
Сообщениеstalber,
Сделал Вам прототип, который превращает Ваши данные в данные, которые понимает Excel. А из этих данных Вы сделаете нужную сводную.

Автор - InExSu
Дата добавления - 07.11.2018 в 13:09
Светлый Дата: Четверг, 08.11.2018, 07:52 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 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
 
Ответить
СообщениеСделал универсальные массивные формулы:
Код
=НАИБОЛЬШИЙ(МУМНОЖ(Ч(Ч(СМЕЩ(Данные!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))

Автор - Светлый
Дата добавления - 08.11.2018 в 07:52
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сосчитать популярные числа в столбце, вывести его и название (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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