Самое частое слово в столбце при условии из другого столбца

Дата: Понедельник, 04.02.2019, 13:48 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 4

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

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

Excel 2016

Ребята, помогите, пожалуйста, с формулой!

Задача такая: определить самое часто встречающееся слово из диапазона G17:G116, если напротив этого слова в M17:M116 заполнены данные.

Самое часто втреч. слово я находила по формуле

Код

=INDEX($G$17:$G$116,MATCH(MAX(COUNTIF($G$17:$G$116,$G$17:$G$116)),COUNTIF($G$17:$G$116,$G$17:$G$116),0))

Чтобы исключить из условия пустые ячейки из M17:M116, думала применить формулу:

Код

=IF(ISBLANK(M17:M116)," "INDEX($G$17:$G$116:$G$17:$G$116,MATCH(MAX(COUNTIF($G$17:$G$116,$G$17:$G$116)),COUNTIF($G$17:$G$116,$G$17:$G$116),0)))

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

Не получается(((

Я в excel новичок. Наверняка, что-то не так делаю.

_Boroda_

Дата: Понедельник, 04.02.2019, 13:53 | Сообщение № 2

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16674

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

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

2003; 2007; 2010; 2013 RUS

Да, кое-что действительно не так. Для того, чтобы с формулой поиграться, нужен файлик. Можно его, конечно, и сделать, но гораздо лучше будет, если, согласно п.3 Правил форума, Вы положите сюда свой

* И что делать, если наиболее частых не одно, а несколько слов? Судя по Вашей первой формуле, нужно только одно из них (верхнее) вывести?

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

китин

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

Группа: Модераторы

Ранг: Экселист

Сообщений: 7014

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

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

Excel 2007;2010;2016

с вашим файлом было бы гораздо проще вам помочь.

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Дата: Понедельник, 04.02.2019, 14:05 | Сообщение № 4

Группа: Пользователи

Ранг: Прохожий

Сообщений: 4

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

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

Excel 2016

Прикладываю файл. Надеюсь, в таком виде пойдет). Если причина увольн. не указана, значит, чел. еще работает.
Спасибо за помощь!

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

_Boroda_

Дата: Понедельник, 04.02.2019, 14:26 | Сообщение № 5

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16674

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

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

2003; 2007; 2010; 2013 RUS

Так нужно?
Формула массива

Код

=ИНДЕКС(A2:A12;МОДА(ЕСЛИ(B2:B12="";СТРОКА(B2:B12)+999;ПОИСКПОЗ(A2:A12;A2:A12;))))

При равенстве количеств даст того, кто первым попался

Или еще вариант. Немассивный

Код

=ТЕКСТ(СУММПРОИЗВ(СЧЁТЕСЛИМН(A:A;{"ж*";"м*"};B:B;"*")*{1;-1});"ж;\м;равно")

К сообщению приложен файл: Book2-4-1.xlsx (12.3 Kb)

Дата: Понедельник, 04.02.2019, 14:29 | Сообщение № 6

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

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

Сообщений: 1829

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

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

Excel 2013, 2016

Код

=ИНДЕКС(A2:A14;ПОИСКПОЗ(МАКС(СЧЁТЕСЛИМН(A2:A14;A2:A14;B2:B14;"<>"));СЧЁТЕСЛИМН(A2:A14;A2:A14;B2:B14;"<>");))

Исправил формулу.
*Исправил обратно. Формула массива.

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

Сообщение отредактировал Светлый - Понедельник, 04.02.2019, 15:04

Дата: Понедельник, 04.02.2019, 14:43 | Сообщение № 7

Группа: Пользователи

Ранг: Прохожий

Сообщений: 4

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

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

Excel 2016

_Boroda_, СПАСИБО вам огромное! получается). попробую разобраться с составляющими этой формулы)).

_Boroda_

Дата: Понедельник, 04.02.2019, 14:44 | Сообщение № 8

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16674

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

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

2003; 2007; 2010; 2013 RUS

Цитата Светлый, 04.02.2019 в 14:29, в сообщении № 6 ()

Исправил формулу

А сначала лучше была

Сергей, поставь в столбец А всех "м" и двух "б". У обеих "б" по причине, а у "м" пусть все пусто останется

Дата: Понедельник, 04.02.2019, 14:52 | Сообщение № 9

Группа: Пользователи

Ранг: Прохожий

Сообщений: 4

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

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

Excel 2016

Светлый, и вам спасибо большое!!! пробую. пока не получилось))

Дата: Понедельник, 04.02.2019, 14:59 | Сообщение № 10

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

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

Сообщений: 1829

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

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

Excel 2013, 2016

Цитата _Boroda_, 04.02.2019 в 14:44, в сообщении № 8 ()

пока не получилось

Формула массива.

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

Дата: Понедельник, 04.02.2019, 15:03 | Сообщение № 11

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

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

Сообщений: 1829

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

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

Excel 2013, 2016

Цитата _Boroda_, 04.02.2019 в 14:44, в сообщении № 8 ()

А сначала лучше была

Совершенно верно. Условие Уволили, совсем запутался.

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

китин

Дата: Понедельник, 04.02.2019, 15:22 | Сообщение № 12

Группа: Модераторы

Ранг: Экселист

Сообщений: 7014

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

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

Excel 2007;2010;2016

[offtop]

Цитата _Boroda_, 04.02.2019 в 14:44, в сообщении № 8 ()

всех "м" и двух "б"

хм. вторые кавычки как то смущают

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Дата: Понедельник, 04.02.2019, 17:04 | Сообщение № 13

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

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

Сообщений: 1829

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

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

Excel 2013, 2016

Покороче. Массивные:

Код

=ИНДЕКС(A:A;ОСТАТ(МАКС(СЧЁТЕСЛИМН(A2:A24;A2:A24;B2:B24;"<>")+СТРОКА(2:24)%);1)/1%)

Код

=ИНДЕКС(A:A;ОСТАТ(МАКС(СЧЁТЕСЛИМН(A2:A24;A2:A24;B2:B24;"<>")*100+СТРОКА(2:24));100))

Код

=СМЕЩ(A1;ОСТАТ(МАКС(СЧЁТЕСЛИМН(A2:A24;A2:A24;B2:B24;"<>")+СТРОКА(1:23)%);1)/1%;)

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

Сообщение отредактировал Светлый - Понедельник, 04.02.2019, 17:19