Исправление чисел, превратившихся в даты
Вставка
- Опубліковано 3 жов 2024
- 3 способа исправить числа, случайно превратившиеся в даты в Microsoft Excel.
Скачать пример www.planetaexc...
Заходите в гости ↓↓↓
Мои книги planetaexcel.ru...
Мои онлайн-курсы www.planetaexc...
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru...
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel
Я-зайду на ютуб,музыку послушаю, видосы веселые погляжу. Тоже я-опаньки,новое видео про Еxcel от Николая Павлова. Очень даже надо!
Спасибо за урок, он пригодится тем, кто уже перенёс данные. Я делаю немного проще. Сначала перебрасываю данные в редактор типа блокнот (который не понимает форматов), далее делаю поиск и замену всех "." на ",". А затем этот массив вставляю в Excel. Если при этом активно пользоваться горячими клавишами Ctrl+A, Ctrl+F, Ctrl+C, Ctrl+V, Altl+Tab... то всё делается быстро и удобно.
Я для этого использовал Word. Мне часто приходится перекидывать данные для анализа с инет-страниц в Эксель. Открыааю сохраненную инет-страницу в Worde, там выделяю столбики в таблице, жму сtrl+H (Замена) и меняю сразу все запятые на точки, а их может быть тысячи. А потом выделяю эту таблицу и в Эксель. Но макрос навеняка удобнее использовать.
Есть проще способ - использовать импорт данных. Для этого открываем excel, Данные - Внешние данные - Из текста, далее указываем файл с данными, в пошаговом конструкторе указываем параметры импорта и на последнем шаге нажимаем "Дополнительно" и указываем десятичный разделитель точку вместо запятой. Это инструкция актуальна для mac os, думаю на виндовс аналогично
👍
Лайк не глядя! Очень актуально. Вроде всё прозрачно, но уверен, что взгляну на тему, благодаря Николаю, по-новому! Ну а теперь к просмотру)
Николай, спасибо за отличный урок. Изучаю не только Excel, но и способы наглядного объяснения )
Дмитрий, у вас с этим все в порядке :) Мне самому у вас есть, чему поучиться :)
@@planetaexcel О, программа взаимного обмена опытом получается. )) !
Спасибо огромное Автору канала ! Информативно и полезно ! Благодарю .
Николай, огромное вам спасибо! Как обычно кратко, лаконично и максимально эффективно.
Как всегда спасибо, Николай! Что меня больше всего удивляет, то что на нужные ролики я попадаю как раз при возникновении вопросов! )
Дружественная Вселенная в беде не оставит :)
Помог первый самый простой способ - поменять параметры Excel - разделитель с точки на запятую. Затем просто закрыл файл, открыл заново и все числа стали нормально отображаться! Спасибо большое за Ваш труд!
Как приятно вас слушать 🤩🤗🤩 всё сразу понятно🔥
Благодарю, Николай!!! Как всегда в новом видео открыл для себя много нового об Excel !!!!
Сталкивался неоднократно с проблемой. Решал макросом, кем-то написанным. Николай, как обычно, все супер.
Лучший, просто лучший, свечку за здравия за вас поставлю!
Большое спасибо, как всегда, максимум пользы и никакой воды. 😜😜😜
Как раз недавно столкнулся с этой проблемой, сам догадался только поменять запятую на точку в настройках. Спасибо за информативный ролик!
Спасибо Николай! Канал отличный, помогает развивать скилл в MS Excel!
Спасибо! Когда-то сталкивался с этой проблемой, ну а теперь при встрече буду решать её более элегантно ;-)
Полезная инфа, это самый лучший канал по экселю)
Огромное подспорье!!! Автору респект!
30 минут искал решение, пока не наткнулся на видео Николая. Спасибо большое
Спасибо за видео урок !
Спасибо за хорошую идею с макросом. После некоторьіх преобразований и корректировок у меня заработало.
Название видео простое, а как оказывается бывает сложно это сделать. Нет, программистом я не стану, но как дальше работать в эксель без макросов, уже не представляю. Благодарю Вас Николай
Почему нельзя просто отключить это дурацкое преобразование чисел в даты?! Я например не работаю с датами никогда. Ужасно достал этот баг.
Благодарю! Очень доступно объяснили. 👍
спасибо большой. Очень помогло видео
Super, viruchil! podpisalsa na tebya!
Блин, раз-два и готово! Спасибо, до встречи на тренингах)
Вы волшебник.
Посмотрел, освоил, лайкнул!
Супер, спасибо!
Спасибо, огромное!!!!
Четко и по делу. Спасибо.
Отлично! Помню, как-то переводил в 1994 году международный классификатор хирургических операций. Замучился полувручную исправлять коды. И сейчас нет-нет встречаются подобные задачки.
Как всегда на высоте!))
огромное спасибо!!
Спасибо, полезная информация.
На mac os нет настроек типа Разделителя, но есть другой простой способ - использовать импорт данных. Для этого открываем excel, Данные - Внешние данные - Из текста, далее указываем файл с данными, в пошаговом конструкторе указываем параметры импорта и на последнем шаге нажимаем "Дополнительно" и указываем десятичный разделитель точку вместо запятой. Это инструкция актуальна для mac os, думаю на виндовс аналогично
Спасибо) Супер
Спасибо за уроки..👏👏👏
Столкнулся с такой проблемой, копируя прайс с иностранного сайта. Решил просто. Перенёс инфу в вордовский файл, далее функцией найти/заменить поменял все точки на запятые, потом перенес данные в эксель.
Николай доброго времени. В формуле ошибка. Там, где указан формат даты стоит запятая, поэтому числовые значения выводятся неправильно. Должна стоять точка. "М.ГГГГ" - такой формат, тогда числа будут верные. За ролик огромное спасибо и за весь сайт, созданный вами, просто снимаю шляпу. Тысячи раз Вы выручали наверное не только меня. Благодарю миллион раз еще раз!
Спасибо маэстро!
Спасибо большое за такое полезное видео. В настройках изменила на "." и сработало, но в случае когда появляются 1.1.1 например, далее происходит замена на дату
Есть нюанс. Если дата записана в формате 11.июн например, то нужно будет в формуле заменить "М,ГГ" на "Д,М". \
А так спасибо, сэкономили время!
Здравствуйте, отличный канал очень помогает. В данном случае у меня не получилось даже при использовании макроса так как был еще вариант когда в ячейке не как у вас месяц.число (авг.8), а число. месяц (8.авг)
Спасибо давно с этим выяснял, пока не понял, насчет точки и запятой. Часто это происходит, когда страницу HTML скачанную из инета пробую открыть в Экселе. У меня другой способ. Я сначала открываю в Worde, там выделяю колонки с числами, делаю им автоматическую замену "," на ".", потом копирую таблицу и вставляю в Эксель. Попробую воспользоваться макросом, так должно быть быстрее.
Мужик, ты гений!
Спасибо!
Спасибо, очень полезно. У меня такая проблема была при импорте csv файла, там цены с точкой у поставщика. Всегда использовал первый способ, о других не знал.
Но, действительно, может быть проблема, когда после точки разное количество знаков (2 и 4, например) преобразуются одинаково, в комментариях подробно рассмотрели.
Рекомендую всё же первый способ, либо, если возможно, заранее в другой программе заменить точки на запятые.
За видео спасибо.
Часто это происходит, когда страницу HTML скачанную из инета пробую открыть в Экселе. А в ней таблицы с ценами. У меня другой способ. Я сначала открываю в Worde, там выделяю колонки с числами, делаю им автоматическую замену "," на ".", потом копирую таблицу и вставляю в Эксель. Попробую воспользоваться макросом, так должно быть быстрее.
Николай, видео выпустили видимо после моего письма о добавки в настройку Plex, я знаю все перечисленные методы, половина из них не подходит в моем случае, треть не работает с таблицей полученной из FineReader (видимо эта прога как-то не правильно собирает файл), подходит только функция ПОДСТАВИТЬ, но мое предложение было на счет добавление функции в библиотеку функций Plex (чтобы удобнее было пользоваться). Функция типа ИзмДробРазделитель(ByRef Num As Range, ByRef Separator As String, Optional Format as String = "text"), на самом деле решать вам, надстройка то ваша. Благодарю за обзор
Способ не работает для некоторых чисел. Например, 5.87 преобразуется в 5,1987, а 17.11 в 11,2020. Что делать в таком случае?
Максим, попробуйте способ с функцией ЯЧЕЙКА("формат";A1). В ролике Николая вариант преобразования "месяц.год" (5.87) эта функция возвращает код D3, а в случае, когда данные преобразуются в 17.ноя (день.месяц) функция вернёт код D2. Дальше включайте логику
Виктор Косенков вот только с 5.87 это не сработает)
Ничего не сделаешь, если Вы введете 5.87, система изменит на май.87
(01.05.1987), если введете 5.1987 система преобразует в туже дату май.87
(01.05.1987), т.е получится, что 5.87=5.1987. Реальное число скрываемое за датой, неизвестно. Когда сталкивался с такой проблемой в системе ORACLE (грубо говоря аналог 1С в США), выгрузка сначала открывается блокнотом, происходит замена точки на запятую, далее копируется. Если выгрузка открывается в блокноте не понятном формате, тогда меняется системно (первый способ автора видео), зависит от формата отчета.
Да забыл, еще попробуйте ввести 3/4 )
@@V3ntur0 Как вариант, можно оценить другие данные из диапазона, которые не подверглись преобразованию в формат даты. Если после точки у всех по 2 цифры, логично предположить, что в случае с май.87 также изначально были 2 цифры - 87, а если у других данных по 4 цифры, тогда, скорее всего были цифры 1987 или 2087, в таком случае Excel вернёт именно то, что было до преобразования.
Конечно, есть случаи, когда точно определить, что за цифры были до преобразования, невозможно. Например 7.6 и 7.06 преобразуются в 07.июн
Спасибо👏👏👏👏👏👍👍👍👍👍👍👍
Спасибо.
Можно ещё попросить записать видео по защите VBA.
Как защитить, какими программами Вы пользуетесь и т.д.
+1, защита и обновление макроса или надстройки у нескольких пользователей было бы очень классно посмотреть.
Нет смысла записывать такое видео (во всяком случае по экселю точно), так как вся запрашиваемая вами информация уместится в одно предложение: защищайте стандартными средствами, но тогда сохраняйте все свои файлы в бинарном формате.
Спасибо большое 💖👍
Хороший способ, но есть проблема с датами 2020 года. Например, 20.05.2020 это или 20,05 или 20,5. Тут хорошо, если есть откуда посчитать. Поэтому подгружаю теперь данные через импорт. Числовым столбцам присваиваю формат текст, а потом уже меняю точку на запятую и перевожу в числа
Спасибо.
Спасибо! С Вами в XL как рыбка в воде)
какое полезное видео
Частенько при загрузке csv-файла Excel подменяет номер углового дома на дату :( И вообще кучу всего может подпортить. Потому я блокнотом в исходный файл добавляю первой строкой "Sep=~", а после в Excel функцией "Текст по столбцам" явно указываю тип "Текст". А далее можно использовать поиск и замену (CTRL+H) для замены точки на запятую и т.д. и т.п.
Решение с макросом работает тоже не без изъяна. У меня допустим на числе 4.56 которое Excel воспринимает как "апр.56" выдает после обработки 4,1956. Так что в макросе по ходу нужна более сложная логика. Как вариант решения для себя. Повесил на кнопку на ленте переключение разделителя чисел с , на . Когда данные нужно вставить переключаю на точку. После вставки и интерпретации данных включаю опять запятую.
Имхо, тут надо следить за значениями. Например: если даны цены в разных валютах, то ГГГГ в формуле даст неверные данные для долларов и евро.
Спасибо! Круть!
большое спасибо, очень актуально. Скопировала макрос, но в нем какая-то ошибка. Данные не меняет и в самом макросе строка
num = CDbl(Replace(cell, ".", ","))
выделена желтым цветом
подскажите в чем может быть проблема?
Молодец! Вам бы в университетах преподавать. Очень доступно.
У меня тут свой маленький университет :)
@@planetaexcel Это да))
Очень полезно и доступно. Но можно с точностью но на оборот. В ячейках стоит формат дата( и написано датой), но при запросе через Power Query меняется на числовой формат, почему и как это исправить ? В Power Query меняешь с числового на дату формат это не помогает.
здравствуйте! а можно сделать выбор даты в одной ячейке, чтоб можно было выбрать с всплывающего календаря? но, в этой одной этой ячейке, должно быть начало и концу даты. Мы пытаемся сделать в excel’e управление проектами, где на каждом этапе есть: начало и завершение процесса.
Полезно!
Помогите. Есть список предприятий и 3 показателя(сотрудники, вал доход и оборот в год). И ещё таблица где показатели разбиты по интервалам и того 9 интервалов. Маленькие предприятие если людей до 5 и вал доход до 10 и оборот до 10. И ещё бывают средние предприятия и больше. Как узнать какое куда попало предприятие. Заранее спасибо!
Про решение через макрос
Региональные различия это не проблема пользователя!
Микрософт давно уже обязан был сделать кнопку преобразования региональных настроек в любой ячейке.
Повторюсь: и убрать региональные названия функций
Получилось сов случайно. 6:50 Теперь вопрос как заменить значения, если оригинальные были гиперссылками.
А как будет выглядить код макроса, если эксель меняет диапазон чисел скажем 1-5 на 5-Jan
Написал так:
Sub Fix_Numbers_From_Dates()
Dim num As Double, cell As Range
For Each cell In Selection
If Not IsEmpty(cell) Then
If cell.NumberFormat = "General" Then
num = CDbl(Replace(cell, "/", "-"))
Else
num = CDbl(Format(cell, "m,d"))
End If
cell.Clear
cell.Value = num
End If
Next cell
End Sub
Но вместо 1-5 выдает 15
гениально
Спасибо большое за Ваши уроки. Подскажите, пожалуйста, после выгрузки с программы, меняется формат, к примеру такое число изначально в программе 4.7, но после экспорта в эксель, число преображается в дату, формат ячеек преобразует в одно число 44746, как автоматически поменять настройку числа до 4.7 т.к таких цифр очень много и в ручной метод - не вариант. Помогите, пожалуйста, заранее Спасибо!
А какой формат вставить, если нужно не дату и время, а просто число. Которое потом можно проссумировать?
А как же подключение к выгрузке с помощью PQ. Особенно, если это преобразование нужно делать регулярно, или собирать эти выгрузки
Это само-собой. Здесь же речь про то, как исправить уже испорченные данные.
Человек профи. Кроме спасибо сказать больше нечего.
🙏
Еще прикол импорта из FineReader заметил, есть текст "[1)", форматирован как текст, если воспользоваться поиск/замена, и попробовать заменить "[" на "(" то в результате получишь "-1" в ячейке с форматом текстовый
Когда знаешь, всё просто)
Было бы весьма желательно, чтобы кто-нибудь из "гуру" описал алгоритм распределения строк внутри ячейки по столбцам. В стандартной функции Excel нет возможности ввести в поле "своего разделителя" возврат каретки
Николай, очень хорошо и понятно обьясняите, но почему же плекс такой не доработанный? Очень обидно, зря выброшенные деньги(( Я работая с большими объемами данных и например функция "парная подсветка дубликатов" просто ужас. Так же много других косяков именно при работе с большими объемами!
Гоша, парная подсветка дубликатов не предназначена в PLEX для работы с большими объемами :) На больших объемах любой макрос будет нещадно тормозить, кстати :)
Спасибо за информацию. Но как быть, если в дату превращаются не числа, а, например, инвентарные номера? У меня именно такая проблема регулярно. Они и с / и с точкой и заменять их нельзя.
Делайте импортом. Через "Данные" => "Из текста". И при импорте для столбца с инвентарниками ставьте текстовый формат. Не имеет смысла обрабатывать инвентарные номера как числа.
Если же при вводе у вас инвентарники заменяются на даты, то есть два варианта. Либо для ячейки(столбца/строки) с инвентарным номером поставить до ввода данных текстовый формат. Либо перед инвентарным номером ставить ' (апостроф).
Спасибо. Примерно так и делала. Просто хотелось магии
Не получилось. Excel 2016 пишет, что ошибка в формуле, если это формула, и выделяет скобки. У меня числа это гиперссылки (курсовая цена) после вставки некоторые превратились в даты
По поводу двойного умножения на -1 есть информация, что происходит с точностью чисел с плавающей точкой? Как известно, в форматах с плавающей точкой -1 это не целое число, а что-то вроде -1,0000...001, получается, что совершая умножение на это число мы меняем исходные данные. Это верно для Экселя или здесь это как-то обходится?
Николай, добрый день. Предположим только одно число имеет такой "плохой" вид. Если я запишу вручную число и поставлю перед ним апостроф, то таблица будет понимать его как число (математическую цифру), которую я могу прибавлять, вычитать и др.?
Здравствуйте, у меня вопрос можно ли ячейку разделить на две при этом чтобы вся таблица не сдвинулась?
Супер!
Но у меня не рядовые случаи ) бывает разный набор цифр после точки.
к примеру 9.31 макрос возвращает 9.1931, что уже после конвертации не соответствует.
Если дробная часть по длине разная, то надо формулу и макрос ощутимо корректировать, к сожалению.
Когда редактируешь ячейку с формулой, то все адреса других ячеек в данной формуле окрашиваются разными цветами, а сами ячейки по этим адресам приобретают цветные рамочки соответствующего цвета, чтобы их было хорошо видно на листе. Очень удобно.
А если адреса в формуле ссылаются на другой лист, то на другом листе ячейки уже не имеют цветных рамок. Очень неудобно!
Можно ли сделать так, чтобы и на другом листе исподьзуемые в формуле ячейки тоже выделялись цветом?
Николай, спасибо! а если в столбце цифры без всяких разделителей, а 8-10 превращенные в даты выглядят как 8-oct вместо нужных 8-10? можно ли использовать макрос из данного видео или тут нужен другой?
Николай, нашла в вашей книге большую формулу в работе с датами, чтобы сделать календарь в эксель. Все супер, но очень хотелось бы в этом календаре добавить подсветку дней 2 через 2. Подскажите, как дополнить вашу формулу? Или можно сделать условное форматирование - но тогда как прописать формулу там?
А наоборот можно? У меня в столбце дат одна ячейка постоянно выводится как число, представляющее дату. Все время приходится изменять ее на формат даты. Все данные внутренние, никакого импорта нет.
С каждого видео польза: сегодня подумал и из макроса сделал замену формул значениями (что бы не копировать и вставлять значения вручную).
здравствуйте, мне надо что то типа такого :=ЕСЛИ(F37="ДДДД ММММ ГГГГ";"Устранено";"15.12.2023"), но она не работает, я не знаю как внести формат даты (любой даты). пыталась это указать в правилах форматируемых ячеек. но тоже ничего не получилось.
Сможете помочь, если это возможно, буду благодарна.
Добрый день! Расскажите, пожалуйста, как написать макрос, если есть необходимость разделить таблицу на несколько колонок для печати, т.е. чтобы умещалось на листе 2,3 или 4 таблицы. Спасибо.
Поправлю. В экселе (по крайней мере в 2016) даты начинаются с нулевого января 1900 года))
у меня был подвох с форматом даты видимо, изменение строки num = CDbl(Format(cell, "m,yyyy")) на num = CDbl(Format(cell, "d,m")) решилось
подскажите, пожалуйста, вариант кода при формате в одном столбце и Д.М и М.ГГГГ ?
Николай, здравствуйте!
Первый вариант супер, а вот два последующих не совсем.
Например, если исходное "число" было 5.87 то после преобразования в дату и далее в число получим 5,1987.
Для операций, где требуется только целая часть подойдёт, а вот для точных не совсем.
Да, такие формулы расчитаны только на стандартные для финансов 4 знака после запятой. Если количество знаков разное, то придется значительно все усложнять - и формулу, и макрос.
@@planetaexcel тут скорее даже не возможно будет узнать истинные значения, если данные уже преобразованы Excel в дату и отсутствуют исходные данные.
разве не так?
А если число стало датой по типу дата.месяц.год? Как быть в этой ситуации? Макрос в этом случае не сработал.
Очень была бы Вам благодарна, если бы Вы формулы говорили или писали английской версией.
ничего не понятно. живу в Латвии, русских версий нет ни на рабочем компьютере, ни дома.
Ваши видео очень нравится смотреть, многому именно от Вас научилась, но вот названия по русски....
Так всегда же пишется на экране английский аналог команды.
+1!
Вспоминаю своё собеседование на одну вакансию. Пришли значит за комп, говорит, вытяните мне этот список из той таблицы. Я "ок". Пишу =vlookup, а реакции ноль, говорю у вас что, русский стоит, можно переключить на английский? Нет, говорит, у нас весь офис на русском. Ну, я не растерялся, ввёл впр, нашёл ему тот список. Он сказал, сделать что-то ещё, и тут я понял, что совершенно не знаю русского офиса, во всяком случае, что касается формул... В общем на ту работу меня, слава Богу, не взяли. Сейчас без проблем работаю в АНГЛИЙСКОМ офисе, в котором проработал всю свою трудовую жизнь.
Татьяна, пересмотрите видео еще раз - когда пишу формулы, то в правом нижнем углу специально накладываю её английский вариант названия.
@@shamratik Я бы скорее писал наоборот. Русский офис - весьма узкое направление, тогда как русскоязычных людей работающих по всему миру на АНГЛИЙСКОМ офисе гораздо больше.
@@planetaexcel Ой, да, увидела, :)) спасибо огромное. Удивляюсь сама себе, как могла не заметить.
Заранее столбец форматитуешь в текст, вставляешь, заминяешь точку на запятую. Все! Но формула мне понравилось.
А если данные уже кто-то вставил до вас? И не в текстовом формате, т.е. они уже испорчены?
@@planetaexcel нет. Если сам вставляешь, То проще заранее столбец в текст. Если готовые данные то 2, 3 вариант. Я к тому что 1й вариант через параметры дольше и не удобнее чем просто переформулировать столбец.
наконец-то спасение, а то присылают нам данные с приборов учета и половина чисел преобразованы в даты...