Профессиональные приемы работы в Microsoft Excel

Обмен опытом => Microsoft Excel => Тема начата: Ярослав Николаев от 28.10.2018, 11:02

Название: Непонятная работа условного форматирования повторяющихся значений
Отправлено: Ярослав Николаев от 28.10.2018, 11:02
Коллеги, первый раз столкнулся с тем, что условное_форматирование (https://msexcel.ru/content/view/116/2/) одинаковых ячеек работает как-то некорректно.

Суть такая: у меня есть текстовый столбик, который состоит из цифр, например:
94964065683211808
94964065683211829
94964065683211831
94964065683211832

Я применяю к нему условное_форматирование (https://msexcel.ru/content/view/116/2/) (красным подсвечивать повторяющиеся значения) и почему-то все эти значения подсвечиваются, как будто в глазах экселя они одинаковые.

В начале подумал, что повторяющиеся значения проверяются не полностью, а частично. Мол, если первые n чисел в ячейке одинаковы, значит она считается повторной. Создал столбик из много повторяющихся начальных букв, типа:
абвгдеёжзиклмннннннн
абвгдеёжзиклмноооооо
абвгдеёжзиклмноппппп
абвгдеёжзиклмнопрррр

но нет, тут все работает корректно. Проблема только с числами.
Если мой столбик

94964065683211808
94964065683211829
94964065683211831
94964065683211832

перевести в числа, то он преобразуется вот так:
94964065683211800
94964065683211800
94964065683211800
94964065683211800

Видимо, как раз в этом причина. Если эксель видит, что в ячейке одни цифры (даже в виде текста), он их на каком-то уровне преобразует в числа и сравнивает их.
Я, конечно, могу обходным путем, допустим, создать столбик с буквой "а" и сцепить его со своим, тем самым получив,

а94964065683211808
а94964065683211829
а94964065683211831
а94964065683211832

которые уже будут корректно форматироваться, но это такое... Может есть более вменяемый вариант, чтобы условное_форматирование (https://msexcel.ru/content/view/116/2/) изначально рассматривало мои цифры как текст и не считало их одинаковыми?

Пример во вложении.
Название: Re: Непонятная работа условного форматирования повторяющихся значений
Отправлено: boa от 28.10.2018, 15:09
В Excel Точность представления чисел, разрядов, не более (https://support.office.com/ru-RU/article/%D0%A2%D0%B5%D1%85%D0%BD%D0%B8%D1%87%D0%B5%D1%81%D0%BA%D0%B8%D0%B5-%D1%85%D0%B0%D1%80%D0%B0%D0%BA%D1%82%D0%B5%D1%80%D0%B8%D1%81%D1%82%D0%B8%D0%BA%D0%B8-%D0%B8-%D0%BE%D0%B3%D1%80%D0%B0%D0%BD%D0%B8%D1%87%D0%B5%D0%BD%D0%B8%D1%8F-Microsoft-Excel-1672b34d-7043-467e-8e27-269d656771c3) - 15 разрядов
поэтому и обрабатывается первые 15 чисел. Остальные числа как 0.
Название: Re: Непонятная работа условного форматирования повторяющихся значений
Отправлено: Ярослав Николаев от 28.10.2018, 16:39
Спасибо, буду знать. У меня как раз в примере 15 первых цифр одинаковые.
Но все равно остается вопрос, почему же эксель рассматривает данные значения как числа со всеми вытекающими ограничениями, в то время как они сохранены как текст.
Получается какое-то половинчатое преобразование: это уже не числа, потому что формулы с ними работать не будут, но и не текст, потому что условное_форматирование (https://msexcel.ru/content/view/116/2/) видит к них числа :)
Название: Re: Непонятная работа условного форматирования повторяющихся значений
Отправлено: Serge 007 от 28.10.2018, 19:40
Цитата: Ярослав Николаев от 28.10.2018, 16:39
...почему же эксель рассматривает данные значения как числа со всеми вытекающими ограничениями, в то время как они сохранены как текст?
Excel, являясь 15-и разрядной электронной таблицей умеет "понимать" что в данном случае текст является числом

Цитата: Ярослав Николаев от 28.10.2018, 16:39
...это уже не числа, потому что формулы с ними работать не будут, но и не текст, потому что условное_форматирование видит в них числа
Формулы будут работать с такими числами. В пределах 15-и разрядов.
Название: Re: Непонятная работа условного форматирования повторяющихся значений
Отправлено: boa от 28.10.2018, 21:15
Цитата: Ярослав Николаев от 28.10.2018, 16:39
Получается какое-то половинчатое преобразование

Цитата: Вильям ШекспирЕсть многое на свете, друг Горацио, что и не снилось нашим мудрецам
Название: Re: Непонятная работа условного форматирования повторяющихся значений
Отправлено: runner от 29.10.2018, 09:55
Знакомая проблема с длинными артикулами  :)
К сожалению, полностью решить эту проблему внутри Excel мне не удалось, но решение есть.
Столбец из Excel копируете в блокнот, потом копируете его в блокноте, возвращаетесь в Excel, устанавливаете текстовый формат в столбце куда хотите поставить значения и специальной вставкой вставляете как текст.
Шаманские пляски с бубном, но работает :)

P.S. Правда, у меня этот путь бьёт кириллицу, но в артикулах её нет(не должно быть), а остальное не критично.
Название: Re: Непонятная работа условного форматирования повторяющихся значений
Отправлено: vikttur от 29.10.2018, 11:23
Решение есть, и намного проще.
Условное_форматирование-Применить_формулу
=СУММПРОИЗВ(--($A$1:$A$11=A1))>1
Название: Re: Непонятная работа условного форматирования повторяющихся значений
Отправлено: Ярослав Николаев от 29.10.2018, 19:41
Цитата: runner от 29.10.2018, 09:55
P.S. Правда, у меня этот путь бьёт кириллицу, но в артикулах её нет(не должно быть), а остальное не критично.
Ну вы описали не ту проблему, что я. По вашей я могу порекомендовать выделять ячейки с данными и переводить их в текстовый формат, а если не помогает, то выделяете фрагмент - данные - текст по столбцам - далее - далее - текстовый - готово.
А если при копировании у вас русские буквы превращаются в кракозябры, то во многих программах знаю такую фишку: это происходит, если вы копируете в то время, как у вас включена английская раскладка. Переключите на русскую и копируйте в этом режиме, тогда кириллица не будет теряться.

Цитата: vikttur от 29.10.2018, 11:23
Решение есть, и намного проще.
Условное_форматирование-Применить_формулу
=СУММПРОИЗВ(--($A$1:$A$11=A1))>1
А вот это шикарное решение, большое спасибо! С формулами в самом условном форматировании знаком пока плохо, но идею понял: для зафиксированного массива А1-А11 проверяется совпадение с незафиксированной ячейкой А1, т.е., по сути, с каждой ячейкой этого массива. А что дают две черточки в скобках?
Название: Re: Непонятная работа условного форматирования повторяющихся значений
Отправлено: boa от 29.10.2018, 19:59
Цитата: Ярослав Николаев от 29.10.2018, 19:41
А что дают две черточки в скобках?

"двойное отрицание" преобразует логический ответ от сравнения True/False(Истина/Ложь) в число 1 или 0 соответственно.
Название: Re: Непонятная работа условного форматирования повторяющихся значений
Отправлено: vikttur от 29.10.2018, 21:15
СУММПРОИЗВ не работает с логическим значениями, поэтому их нужно преобразовывать в числовые.
Можно было бы примерить функцию СЧЕТЕСЛИ, она шустрее, но... та же беда - функция видит число и в текстовой записи.