Страницы: 1
RSS
Первое, второе и т.д. наиболее часто повторяющиеся значения определяются неверно
 
Доброго времени суток, ребята!
Подскажите пожалуйста, из таблицы в отдельные ячейки необходимо вывести наиболее часто повторяющиеся значения (в виде рейтинга), но я столкнулся с одной проблемой: Excel выводит некоторые из одинаковых значений, будто они разные. Убедился, что оно так и есть. 0,00002 и 0,00002 оказались не равны. А в ячейках необходим именно числовой формат с 5 знаками после запятой, иначе большинство значений окажутся уникальными.

Так вот, можно ли в ячейках результатов (рейтинг: 1е место, 2е и т.д. задать такую формулу, чтобы она искала самые часто повторяемые значения не по внутреннему содержимому, а по тем символам, которые отображаются снаружи. Например, опиралась бы не на 0,00002000001239004, а только на 0,00002?
Код
1
=ИНДЕКС(W$3:W$13050;ПОИСКПОЗ(МАКС(СЧЁТЕСЛИ(W$3:W$13050;ЕСЛИ(ЕНД(ПОИСКПОЗ(W$3:W$13050;W$13056:W13056;));W$3:W$13050)));СЧЁТЕСЛИ(W$3:W$13050;ЕСЛИ(ЕНД(ПОИСКПОЗ(W$3:W$13050;W$13056:W13056;));W$3:W$13050));)
Данная формула наверняка может кому-то пригодиться. Она находит 1е, 2е, 3е и т.д. наиболее часто встречающиеся в столбце таблицы значения, но в случае с числами сравнивает их до последнего возможного знака дробной части. Какие же нужно внести в неё изменения, чтобы она сравнивала значения в ячейках, основываясь лишь на внешнем отображении их значения? Много понаписал, знаю, зато осветил вопрос. :)

У меня две версии: либо уговорить формулу сравнивать в ячейках текст, а не числа, либо что-то придумать с округлением, чтобы ексель не залазил далеко и сравнивал числа только до 5 знаков после запятой. Самому это написать не получилось, к сожалению)
Изменено: Daost - 12.02.2017 18:14:13
 
Daost, художники тоже пишут, и сужя по всему вы предлагаете отфотошопить ваш пример.
Тема весьма приближенно дает понять что у вас за проблема. а ведь проблема не в поиске а в резкльтате сравнения.
Вы сами ответили свой вопрос,
Цитата
Daost написал:
сравнивать в ячейках текст, а не числа, либо что-то придумать с округлением
А вот нам, без реального примера и ваших данных, как то сложновато что-либо проверить  
По вопросам из тем форума, личку не читаю.
 
БМВ, простите, ексель сильно завис, вот пример. Будьте добры, помогите)
Изменено: Daost - 12.02.2017 18:40:15
 
В столбец "H"
Код
1
=ОКРУГЛ(G3;5)
"Н" =- копировать, в G - вставить значения и все в порядке  :)
 
_Igor_61,возможности добавлять новые столбцы или вносить изменения в сами значения, которые потом будут анализироваться на повторяемость, к сожалению нет. Да если бы можно было, данных очень много (сто тысяч строк), возможно это сильно замедлило бы быстродействие программы (в каждой ячейке формула на округление вместо одной формулы, извлекающей то, что нужно).
В данном случае нужна формула в одной ячейке, в которой и будет результат. (для 1го наиб. повторяющегося значения, аналогично для 2го, 3го и т.д.)
А что вы скажете по поводу того, что в моём случае 0,00002 не равно 0,00002 (см. пример, всё равно что 2 не равно 2). Видно же, что не может такого быть, тогда что происходит с числами? Можете мне объяснить такую простую вещь...
Изменено: Daost - 12.02.2017 20:17:30
 
В Вашем файле:
В G5 значение: 0,000020000000000131
в G6 значение: 0,000019999999999909
Что Вас удивляет? Округляем и получаем 0,00002 и там и там, но исходные-то значения разные. Исходные откуда-то выгружаются или как-то откуда-то вычисляются?
 
_Igor_61, G5 и G6 - просто числа для решения задачи. Подобные им и они тоже берутся от вычисления экселя, один пример я привёл в файле: см. ячейка E7. В ней написано ЛОЖЬ, т.е. говориться, что F3 не равно F4. Да, они и правда не равны, так как производится вычисление, мне пока что не понятное.

Почему C3 минус D3 равно не 0,00002, а 0,000020000000000131?
Ведь в этом то и зародилась проблема определения наиболее часто встречающихся значений...
Изменено: Daost - 12.02.2017 20:32:06
 
2<>2 Не может, а вот число с плавающей точкой может быть неравно аналогичному. Полно инфы по этому, поищите. например Это  
Одно из правил работы с данными - незачем считать с точностью превышающую точность измерений.
Судя по всему формула  - это переработанная из более раннего вопроса . Просто переработать не получается. Нужно что-то иное.
Изменено: БМВ - 12.02.2017 21:01:17
По вопросам из тем форума, личку не читаю.
 
Здравствуйте. Вариант формулами с доп. столбцом. В параметрах задал точность как на экране теперь Е3=Е4.
 
Цитата
БМВ написал:
Да если бы можно было, данных очень много (сто тысяч строк), возможно это сильно замедлило бы быстродействие программы (в каждой ячейке формула на округление вместо одной формулы, извлекающей то, что нужно).
При таких объемах, дополнительный столбец будет в разы эффективнее, чем любые операции в одой формуле, но все равно будет медленно.

Я б рекомендовал обратить внимание на PowerQuery.
Изменено: БМВ - 12.02.2017 21:51:17
По вопросам из тем форума, личку не читаю.
 
БМВ, gling, _Igor_61, спасибо за помощь, и за ссылку на статью об арифметических операциях с плавающей точкой, и за практические решения. Возьму на ум ваши приёмы, может когда-нибудь сам помогу кому-то, у кого возникнет такой вопрос. Воспользуюсь либо тем либо другим вариантом решения, они оба рабочие. Спасибо Вам! )
Изменено: Daost - 12.02.2017 22:16:01
 
Можно конечно и на MSQuery сварганить, Но там SQL запрос и если синтаксис загадка для вас, то потом изменения вносить крайне сложно. а так все примитивно,
запрос , округление, группировка со счетом строк, сортровка = результат.
По вопросам из тем форума, личку не читаю.
 
БМВ, и все, кто разбирается в екселе и ещё не лёг спать, Здравствуйте! Подскажите, а можно как-то подсчитать значения, которые занимают 1е, 2е, 3е места по повторяемости, но без добавления ещё одного столбца? Т.е. для каждого значения (справа от него, допустим) определить количество повторений, используя только одну ячейку, в которой будет и формула и результат? Намекните хотя бы, или формулой.. мучаюсь, не вышло со СЧЁТЕСЛИ.
Изменено: Daost - 14.02.2017 23:07:06
 
Код
1
=СЧЁТЕСЛИ(D$3:D$17;D24)
У меня работает, кол-во повторений считает
 
_Igor_61, у меня вместо D24 формула массива, которая определяет наиболее частые значения (1е, 2е и т.д.). Как аргумент формулы СЧЁТЕСЛИ эту ячейку использовать к сожалению не удалось.
Код
1
=СЧЁТЕСЛИ(T3:T13050;"=ИНДЕКС(T$3:T$13050;ПОИСКПОЗ(МАКС(СЧЁТЕСЛИ(T$3:T$13050;ЕСЛИ(ЕНД(ПОИСКПОЗ(T$3:T$13050;T$13055:T13055;));T$3:T$13050)));СЧЁТЕСЛИ(T$3:T$13050;ЕСЛИ(ЕНД(ПОИСКПОЗ(T$3:T$13050;T$13055:T13055;));T$3:T$13050));))")
 
_Igor_61,вот что получается..
 
А для чего ее вставлять вместо D24? Впрочем, она и так работает, если и вставить
 
_Igor_61,Спасибо! Извините, что потревожил из-за такой мелочи в этот раз. Неправильно ввёл значение аргумента в замечательной формуле СЧЁТЕСЛИ.) Доброй ночи!)
Страницы: 1
Читают тему
Loading...