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

Вход

Регистрация

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

 

= Мир MS Excel/Поиск часто встречающихся значений (текста) по заданному усл - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск часто встречающихся значений (текста) по заданному усл (Формулы/Formulas)
Поиск часто встречающихся значений (текста) по заданному усл
Terion Дата: Суббота, 06.01.2018, 19:38 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Здравствуйте.
У меня есть таблица в первом столбе которой написана дата, он может повторятся, то есть может быть несколько строк с одинаковой датой. Во втором столбце этой таблицы написано название фрукта.
Также есть вторая таблица в первом столбце которой также написана дата, но они уже не повторяются.
Так вот, во втором столбце второй таблицы мне необходимо вывести название фрукта который чаще всего встречается за эту дату.
Так-же интересует вопрос, возможно ли выводить не только который чаще всего встречается, но и который на 2ом и 3ем месте по популярности.

Постарался объяснить как можно понятнее.
К сообщению приложен файл: 3873019.xlsx(12.2 Kb)
 
Ответить
СообщениеЗдравствуйте.
У меня есть таблица в первом столбе которой написана дата, он может повторятся, то есть может быть несколько строк с одинаковой датой. Во втором столбце этой таблицы написано название фрукта.
Также есть вторая таблица в первом столбце которой также написана дата, но они уже не повторяются.
Так вот, во втором столбце второй таблицы мне необходимо вывести название фрукта который чаще всего встречается за эту дату.
Так-же интересует вопрос, возможно ли выводить не только который чаще всего встречается, но и который на 2ом и 3ем месте по популярности.

Постарался объяснить как можно понятнее.

Автор - Terion
Дата добавления - 06.01.2018 в 19:38
Pelena Дата: Суббота, 06.01.2018, 20:26 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 13140
Репутация: 2895 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Здравствуйте.
Сводная не вариант?
К сообщению приложен файл: 9256906.xlsx(15.8 Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Сводная не вариант?

Автор - Pelena
Дата добавления - 06.01.2018 в 20:26
Karataev Дата: Суббота, 06.01.2018, 22:09 | Сообщение № 3
Группа: Проверенные
Ранг: Старожил
Сообщений: 1256
Репутация: 485 ±
Замечаний: 0% ±

Excel
Вариант с помощью допстолбца "C".
В столбце "A" даты должны быть отсортированы (как в Вашем примере).
Формула в столбце "C" работает для количества строк до 100 тысяч.
В "C2" своя формула, в остальных ячейках другая формула.
В таблицах 2 и 3 используются формулы массива - завершайте ввод формулы не клавишей Enter (как Вы это обычно делаете), а сочетанием клавиш Ctrl+Shift+Enter.
К сообщению приложен файл: 3873019_kar.xlsx(11.9 Kb)


Киви-кошелек: 9166309108
Яндекс-деньги: 410014131888288


Сообщение отредактировал Karataev - Суббота, 06.01.2018, 22:12
 
Ответить
СообщениеВариант с помощью допстолбца "C".
В столбце "A" даты должны быть отсортированы (как в Вашем примере).
Формула в столбце "C" работает для количества строк до 100 тысяч.
В "C2" своя формула, в остальных ячейках другая формула.
В таблицах 2 и 3 используются формулы массива - завершайте ввод формулы не клавишей Enter (как Вы это обычно делаете), а сочетанием клавиш Ctrl+Shift+Enter.

Автор - Karataev
Дата добавления - 06.01.2018 в 22:09
Terion Дата: Суббота, 06.01.2018, 23:52 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Karataev, Karataev, Karataev, Karataev, Karataev,
Формула в столбце "C" работает для количества строк до 100 тысяч.


Спасибо, это то что надо.
Только мне не понятно для чего вот это +(СТРОКА()-1)/100000 ?
 
Ответить
СообщениеKarataev, Karataev, Karataev, Karataev, Karataev,
Формула в столбце "C" работает для количества строк до 100 тысяч.


Спасибо, это то что надо.
Только мне не понятно для чего вот это +(СТРОКА()-1)/100000 ?

Автор - Terion
Дата добавления - 06.01.2018 в 23:52
bmv98rus Дата: Воскресенье, 07.01.2018, 01:07 | Сообщение № 5
Группа: Проверенные
Ранг: Старожил
Сообщений: 1322
Репутация: 223 ±
Замечаний: 0% ±

Excel 2013/2016
Terion,
иначе не определить 1,2,3 .... максимальный . в дробную часть загнаны номера строк в таблице. эти 100000 можно заменить вот таким
Код
10^ROUNDUP(LOG(COUNTA(Таблица16[Столбец1]));0)

Код
=10^(INT(LOG(COUNTA(Таблица16[Столбец1])))+1)

тогда зависимость от количества строк снимется.

Karataev, прошу простить за паразтизм на файле, я шел по томуже пути но пытался запрятать это в один массив, пока не удачно :-(
К сообщению приложен файл: Copy_of_3873019.xlsx(12.0 Kb)
 
Ответить
СообщениеTerion,
иначе не определить 1,2,3 .... максимальный . в дробную часть загнаны номера строк в таблице. эти 100000 можно заменить вот таким
Код
10^ROUNDUP(LOG(COUNTA(Таблица16[Столбец1]));0)

Код
=10^(INT(LOG(COUNTA(Таблица16[Столбец1])))+1)

тогда зависимость от количества строк снимется.

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

Автор - bmv98rus
Дата добавления - 07.01.2018 в 01:07
Terion Дата: Воскресенье, 07.01.2018, 01:42 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
bmv98rus, Понял, спасибо
 
Ответить
Сообщениеbmv98rus, Понял, спасибо

Автор - Terion
Дата добавления - 07.01.2018 в 01:42
Karataev Дата: Воскресенье, 07.01.2018, 08:59 | Сообщение № 7
Группа: Проверенные
Ранг: Старожил
Сообщений: 1256
Репутация: 485 ±
Замечаний: 0% ±

Excel
Только мне не понятно для чего вот это +(СТРОКА()-1)/100000 ?

Это добавляет к количеству дробную часть. В дробную часть записывается номер строки. Но этот номер строки не используется для извлечения данных из нужной строки.
Это делается, чтобы число стало уникальным, т.к. у меня формула затем ищет число не в заданной дате, а во всей таблице и если будет два раза число 3, то формула возьмет первое число 3. А если ест дробная часть, то уже возьмет нужное число.
Ну и также это нужно и в пределах одной даты. Если будет два фрукта с одинаковым количеством, то дробная часть позволяет взять второй и третий по встречаемости.


Киви-кошелек: 9166309108
Яндекс-деньги: 410014131888288
 
Ответить
Сообщение
Только мне не понятно для чего вот это +(СТРОКА()-1)/100000 ?

Это добавляет к количеству дробную часть. В дробную часть записывается номер строки. Но этот номер строки не используется для извлечения данных из нужной строки.
Это делается, чтобы число стало уникальным, т.к. у меня формула затем ищет число не в заданной дате, а во всей таблице и если будет два раза число 3, то формула возьмет первое число 3. А если ест дробная часть, то уже возьмет нужное число.
Ну и также это нужно и в пределах одной даты. Если будет два фрукта с одинаковым количеством, то дробная часть позволяет взять второй и третий по встречаемости.

Автор - Karataev
Дата добавления - 07.01.2018 в 08:59
Karataev Дата: Воскресенье, 07.01.2018, 09:23 | Сообщение № 8
Группа: Проверенные
Ранг: Старожил
Сообщений: 1256
Репутация: 485 ±
Замечаний: 0% ±

Excel
Изменил формулы в таблицах 2 и 3. Теперь номер строки, записанный в дробную часть, используется для обращения к нужной строке. Эта формула должна быстрее работать.
И 100 тыс заменил на 10^5 во всех таблицах, так короче, а может еще и удобнее.
Или посмотрите способ из поста 5 от БМВ, как Вам будет удобнее.
В столбце C сделал одну формулу вместо двух.
Когда я делал формулу, то изначально не представлял, что и как будет работать. Сейчас подумал и увидел другой способ решения, который будет быстрее.
К сообщению приложен файл: 2396136.xlsb(10.3 Kb)


Киви-кошелек: 9166309108
Яндекс-деньги: 410014131888288


Сообщение отредактировал Karataev - Воскресенье, 07.01.2018, 11:42
 
Ответить
СообщениеИзменил формулы в таблицах 2 и 3. Теперь номер строки, записанный в дробную часть, используется для обращения к нужной строке. Эта формула должна быстрее работать.
И 100 тыс заменил на 10^5 во всех таблицах, так короче, а может еще и удобнее.
Или посмотрите способ из поста 5 от БМВ, как Вам будет удобнее.
В столбце C сделал одну формулу вместо двух.
Когда я делал формулу, то изначально не представлял, что и как будет работать. Сейчас подумал и увидел другой способ решения, который будет быстрее.

Автор - Karataev
Дата добавления - 07.01.2018 в 09:23
bmv98rus Дата: Воскресенье, 07.01.2018, 10:57 | Сообщение № 9
Группа: Проверенные
Ранг: Старожил
Сообщений: 1322
Репутация: 223 ±
Замечаний: 0% ±

Excel 2013/2016
С Рождеством
Утро вечера мудрЁнее. Упихнулось
Код
=IFERROR(INDEX(Таблица16[Столбец2];SMALL(ABS(COUNTIFS(Таблица16[Столбец1];Таблица16[Столбец1];Таблица16[Столбец2];Таблица16[Столбец2];Таблица16[Столбец1];[@Столбец1])+(ROW(Таблица16[Столбец1])-1)/10^ROUNDUP(LOG(COUNTA(Таблица16[Столбец1]));0)-(LARGE(COUNTIFS(Таблица16[Столбец2];Таблица16[Столбец2];Таблица16[Столбец1];[@Столбец1]);1)+COLUMN($I:$I)-COLUMN()));1)*10^ROUNDUP(LOG(COUNTA(Таблица16[Столбец1]));0));"")
К сообщению приложен файл: 0372821.xlsx(11.9 Kb)


Сообщение отредактировал bmv98rus - Воскресенье, 07.01.2018, 10:58
 
Ответить
СообщениеС Рождеством
Утро вечера мудрЁнее. Упихнулось
Код
=IFERROR(INDEX(Таблица16[Столбец2];SMALL(ABS(COUNTIFS(Таблица16[Столбец1];Таблица16[Столбец1];Таблица16[Столбец2];Таблица16[Столбец2];Таблица16[Столбец1];[@Столбец1])+(ROW(Таблица16[Столбец1])-1)/10^ROUNDUP(LOG(COUNTA(Таблица16[Столбец1]));0)-(LARGE(COUNTIFS(Таблица16[Столбец2];Таблица16[Столбец2];Таблица16[Столбец1];[@Столбец1]);1)+COLUMN($I:$I)-COLUMN()));1)*10^ROUNDUP(LOG(COUNTA(Таблица16[Столбец1]));0));"")

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

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