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

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


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

Новости:

К первому сообщению темы должен быть прикреплен файл примера в формате xls*.
Приложив пример, Вы избавите себя и других от вопросов типа "А какой критерий?", "А куда выводить результат?", "А сколько строк?" и все тех же просьб выложить файл. Рисовать за Вас Ваши же таблички с заданиями, а затем и решение к ним, никто желанием не горит. Да и, как показывает практика, в большинстве случаев без файла решения не найти.

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

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

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

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

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

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

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

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

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

94964065683211808
94964065683211829
94964065683211831
94964065683211832

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

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

а94964065683211808
а94964065683211829
а94964065683211831
а94964065683211832

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

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

boa

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

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

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

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

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

Serge 007

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

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

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

boa

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

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

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

runner

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

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

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

vikttur

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

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

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

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

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

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

boa

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

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

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

vikttur

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

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



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

30.09.2018 10:24 Расчет процентов за определенный период (месяц) с учетом изменений и платежей 380
22.05.2018 11:38 Скрипт написать который допишет данные в файл 1040
03.03.2018 00:00 Подсчет отработанного времени, за исключением заранее определенных перерывов 1176
14.02.2018 10:11 Подготовить читабельную отчетность по платежам 1168
23.01.2018 13:46 Найти вероятность повторной покупки 1061
12.01.2018 23:56 Сделать отчет на Power BI (Dashboard) 1496
06.09.2017 10:43 Solver VBA не решает гиперболическое уравнение, но при этом решает гармоническое 1357
17.08.2017 12:15 Гиперссылка и фильтр одновременно макрос 1703
23.05.2017 11:20 Копирование данных из одной таблицы в умную таблицу по условию 3440
15.03.2017 15:45 автозамена картинок PowerPoint 1954





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

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