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

Пожалуйста, войдите или зарегистрируйтесь.


Расширенный поиск  

Новости:

Новая редакция правил форума: 2.4. Если вопрос или ответ содержится во вложенном файле, все-равно кратко описывайте в сообщении вопрос или суть решения. Это необходимо, чтобы тему можно было найти через поиск.

Автор Тема: Непонятная работа условного форматирования повторяющихся значений  (Прочитано 460 раз)

0 Пользователей и 1 Гость просматривают эту тему.

Ярослав Николаев

  • Новичок
  • *
  • Уважение: +1/-0
  • Оффлайн Оффлайн
  • Сообщений: 6

Коллеги, первый раз столкнулся с тем, что условное_форматирование одинаковых ячеек работает как-то некорректно.

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

Я применяю к нему условное_форматирование (красным подсвечивать повторяющиеся значения) и почему-то все эти значения подсвечиваются, как будто в глазах экселя они одинаковые.

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

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

94964065683211808
94964065683211829
94964065683211831
94964065683211832

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

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

а94964065683211808
а94964065683211829
а94964065683211831
а94964065683211832

которые уже будут корректно форматироваться, но это такое... Может есть более вменяемый вариант, чтобы условное_форматирование изначально рассматривало мои цифры как текст и не считало их одинаковыми?

Пример во вложении.
Записан

boa

  • Глобальный модератор
  • Старожил
  • *****
  • Уважение: +32/-0
  • Оффлайн Оффлайн
  • Сообщений: 582
  • Доброта спасет мир...

В Excel Точность представления чисел, разрядов, не более - 15 разрядов
поэтому и обрабатывается первые 15 чисел. Остальные числа как 0.
Записан
Ничто не обходится нам так дешево и не ценится так дорого, как вежливость...  Мигель Сервантес де Сааведра

Ярослав Николаев

  • Новичок
  • *
  • Уважение: +1/-0
  • Оффлайн Оффлайн
  • Сообщений: 6

Спасибо, буду знать. У меня как раз в примере 15 первых цифр одинаковые.
Но все равно остается вопрос, почему же эксель рассматривает данные значения как числа со всеми вытекающими ограничениями, в то время как они сохранены как текст.
Получается какое-то половинчатое преобразование: это уже не числа, потому что формулы с ними работать не будут, но и не текст, потому что условное_форматирование видит к них числа :)
Записан

Serge 007

  • Администратор
  • Ветеран
  • *****
  • Уважение: +340/-0
  • Оффлайн Оффлайн
  • Сообщений: 2 951
    • Мир Excel

...почему же эксель рассматривает данные значения как числа со всеми вытекающими ограничениями, в то время как они сохранены как текст?
Excel, являясь 15-и разрядной электронной таблицей умеет "понимать" что в данном случае текст является числом

...это уже не числа, потому что формулы с ними работать не будут, но и не текст, потому что условное_форматирование видит в них числа
Формулы будут работать с такими числами. В пределах 15-и разрядов.
Записан
Бесплатная помощь: www.excelworld.ru
Платная помощь: sergeyizotov@excelworld.ru
Яндекс-деньги: 41001419691823 | WMR:126292472390

boa

  • Глобальный модератор
  • Старожил
  • *****
  • Уважение: +32/-0
  • Оффлайн Оффлайн
  • Сообщений: 582
  • Доброта спасет мир...

Получается какое-то половинчатое преобразование

Цитата: Вильям Шекспир
Есть многое на свете, друг Горацио, что и не снилось нашим мудрецам
Записан
Ничто не обходится нам так дешево и не ценится так дорого, как вежливость...  Мигель Сервантес де Сааведра

runner

  • Постоялец
  • ***
  • Уважение: +7/-0
  • Оффлайн Оффлайн
  • Сообщений: 153

Знакомая проблема с длинными артикулами  :)
К сожалению, полностью решить эту проблему внутри Excel мне не удалось, но решение есть.
Столбец из Excel копируете в блокнот, потом копируете его в блокноте, возвращаетесь в Excel, устанавливаете текстовый формат в столбце куда хотите поставить значения и специальной вставкой вставляете как текст.
Шаманские пляски с бубном, но работает :)

P.S. Правда, у меня этот путь бьёт кириллицу, но в артикулах её нет(не должно быть), а остальное не критично.
« Последнее редактирование: 29.10.2018, 10:02:26 от runner »
Записан

vikttur

  • Глобальный модератор
  • Ветеран
  • *****
  • Уважение: +49/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 005

Решение есть, и намного проще.
Условное_форматирование-Применить_формулу
=СУММПРОИЗВ(--($A$1:$A$11=A1))>1
Записан

Ярослав Николаев

  • Новичок
  • *
  • Уважение: +1/-0
  • Оффлайн Оффлайн
  • Сообщений: 6

P.S. Правда, у меня этот путь бьёт кириллицу, но в артикулах её нет(не должно быть), а остальное не критично.
Ну вы описали не ту проблему, что я. По вашей я могу порекомендовать выделять ячейки с данными и переводить их в текстовый формат, а если не помогает, то выделяете фрагмент - данные - текст по столбцам - далее - далее - текстовый - готово.
А если при копировании у вас русские буквы превращаются в кракозябры, то во многих программах знаю такую фишку: это происходит, если вы копируете в то время, как у вас включена английская раскладка. Переключите на русскую и копируйте в этом режиме, тогда кириллица не будет теряться.

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

boa

  • Глобальный модератор
  • Старожил
  • *****
  • Уважение: +32/-0
  • Оффлайн Оффлайн
  • Сообщений: 582
  • Доброта спасет мир...

А что дают две черточки в скобках?

"двойное отрицание" преобразует логический ответ от сравнения True/False(Истина/Ложь) в число 1 или 0 соответственно.
Записан
Ничто не обходится нам так дешево и не ценится так дорого, как вежливость...  Мигель Сервантес де Сааведра

vikttur

  • Глобальный модератор
  • Ветеран
  • *****
  • Уважение: +49/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 005

СУММПРОИЗВ не работает с логическим значениями, поэтому их нужно преобразовывать в числовые.
Можно было бы примерить функцию СЧЕТЕСЛИ, она шустрее, но... та же беда - функция видит число и в текстовой записи.
Записан
 



Темы без ответов

18.07.2019 16:02 Рассылка почты из Excel при помощи почтовой программы TheBAT! 35
09.07.2019 20:39 Кредит с уменьшением периода выплат 62
28.05.2019 21:09 Сделать несколько скриптов для рабочей таблицы 348
05.03.2019 17:00 Последовательный вывод таблиц Excel в один документ Word без шаблона 659
05.03.2019 09:29 Нежелательные изменение размеров колонтитула при редактировании 470
07.02.2019 01:36 Как удалить дубликаты из выпадающего связанного списка? 607
20.01.2019 12:38 Все варианты частичного суммирования 751
13.01.2019 12:24 Заполнение диапазона числами - в виде кластеров 615
30.09.2018 10:24 Расчет процентов за определенный период (месяц) с учетом изменений и платежей 1039
03.03.2018 00:00 Подсчет отработанного времени, за исключением заранее определенных перерывов 1831





Яндекс цитирования msexcel.ru Яндекс.Метрика

Страница сгенерирована за 0.12 секунд. Запросов: 119.