Вопрос или проблема
Я импортировал табличные данные с веб-сайта, используя в Excel “Данные > Из Интернета”. Проблема в том, что время, например 47:38:00, автоматически переводится. Если я на него нажму, 01/01/1900 23:38:00 будет показано в строке формул, и при преобразовании данных в текст или общий, они преобразуются во float, 1.984722222.
Преобразование в float очень раздражает, так как это то, что отображается при извлечении данных из электронной таблицы. Есть ли способ заставить Excel не изменять данные и просто обрабатывать всё как исходное?
Данные не изменяются, изменяется только формат отображения.
Вы можете установить предпочитаемый формат отображения; например, для показанного 47:38:00 используйте (пользовательский) формат [чч]:мм:сс.
Квадратные скобки [] говорят Excel не переходить к следующей более высокой единице измерения – дням, а оставаться с часами, независимо от того, насколько велико число; без скобок вы бы увидели 1д 23:38:00 или что-то подобное. Например, использование [мм]:сс даст вам 2858:00 в минутах и секундах.
Другой ответ не содержал решения, которое я искал. Я столкнулся с двумя отдельными проблемами при работе с датами из CSV-файла. Я решил поделиться своими результатами здесь для других, у кого такие же проблемы.
1/2) Использование дат в формуле
Проблема в том, что даты преобразуются во float, как показано ниже:
[A2]
9/19/2020 3:38
[Формула]
=CONCAT("Дата: ", A2)
[Вывод]
Дата: 44093.1506365741
Нам просто нужно отформатировать дату в формуле. Это работает, если данные хранятся в виде даты ИЛИ float:
[A2]
9/19/2020 3:38
[Формула]
=CONCAT("Дата: ", TEXT(A2,"гггг-мм-дд чч:мм:сс"))
[Вывод]
Дата: 2020-09-19 03:38:05
Функция TEXT() — это простой способ форматирования даты. Вышеуказанное основано на формате даты ISO 8601.
Вы можете альтернативно использовать другие функции даты, такие как: DAY(A2) = 19
2/2) Преобразование ячеек даты в текстовые ячейки (для CSV-файлов)
Это обычно легко сделать. Обычно вы можете щелкнуть правой кнопкой мыши на диапазоне ячеек и выбрать Формат ячеек, затем изменить тип на “Текст”. Это отобразит дату как исходный текст, вместо попытки интерпретировать вещественное значение.
Однако если у вас есть CSV-файл, открытие его напрямую в Excel приведет к тому, что изменение типа на “Текст” покажет вещественное значение вместо даты. Я считаю, это потому что, хотя Excel “не изменяет данные” для обычной электронной таблицы, он, похоже, изменяет значения, если открыть CSV напрямую в Excel. Я также попробовал копировать ячейки и “вставлять только значения”, но это также привело к вещественным значениям.
Чтобы исправить это, мы просто должны загрузить CSV по-другому:
- В Excel перейдите на вкладку “Данные”, затем нажмите “Из текста/CSV”.
- В всплывающем окне выберите “Преобразование данных”.
- В “Редакторе Power Query” щелкните правой кнопкой мыши на своих столбцах даты и выберите “Изменить тип” -> Текст -> Заменить текущее
- Закройте редактор Power Query и нажмите “Сохранить [изменения]”
- CSV будет добавлено в качестве новой вкладки
Итоговый результат выглядит так же. Однако если вы форматируете столбец как “Текст”, он останется отформатированным как дата, а не как вещественное число. Это ключевое отличие по сравнению с открытием CSV напрямую.
Мне интересно, есть ли у кого-то более простые решения для этого, если есть, оставьте комментарий!
Ответ или решение
Чтобы избежать преобразования временных данных в десятичные дроби в Microsoft Excel, особенно когда вы импортируете данные с веб-сайтов или из CSV-файлов, необходимо принимать во внимание, как Excel интерпретирует время и дату. Проблема, описанная вами, связана с тем, что Excel интерпретирует временные значения как дату и время сначального дня, и при изменении формата ячейки на текстовый формат он отображает время в формате числа с плавающей запятой. Давайте рассмотрим теоретическую часть, приведем пример и разберем, как можно применять это на практике.
Теория:
При вводе временного значения, такого как 47:38:00, Excel отображает его как дата и время (например, 01/01/1900 23:38:00). Такое преобразование связано с тем, что Excel хранит даты и времена как числа, где целая часть означает количество дней с начальной даты (обычно 1 января 1900 года), а дробная часть — долю суток. Так, значение 47:38:00 будет интерпретировано как 1 день и 23 часа 38 минут, что соответствует числу 1.984722222.
Пример:
Предположим, у вас есть датасет с временными показателями, которые вам нужно обработать:
- Исходные данные:
47:38:00 - Интерпретация Excel:
01/01/1900 23:38:00, что эквивалентно1.984722222в числе с плавающей запятой.
Применение:
Чтобы избежать такой интерпретации и последующего преобразования времени в число, можно использовать несколько методов:
-
Использование форматирования:
- Форматирование ячейки: Установите пользовательский формат
[hh]:mm:ss. Это заставит Excel воспринимать любое значение как часы и минуты, игнорируя дни. - Использование функции
TEXT(): При необходимости использования временных данных в формулах, например,=CONCAT("Time: ", TEXT(A2,"[hh]:mm:ss")). Это позволит отобразить время в нужном формате в результате формулы.
- Форматирование ячейки: Установите пользовательский формат
-
Импорт CSV-файлов с использованием Мастера импорта:
- Если у вас есть CSV-файл, запустите Мастер импорта Excel. Выберите вкладку "Данные", затем воспользуйтесь опцией "Из текста/CSV".
- В окне Мастера импорта выберите "Трансформация данных".
- В редакторе Power Query щелкните правой кнопкой по колонке с временем и измените тип с "Дата/время" на "Текст". Это гарантирует, что при последующем импорте данные будут оставаться в текстовом формате.
Несмотря на кажущуюся простоту описанных действий, они требуют внимательного выполнения, особенно при работе с большими наборами данных. Несоблюдение правильной последовательности шагов может привести к нежелательным изменениям данных или увеличению времени обработки данных.
Есть и другие подходы, такие как программирование макросов в VBA, которые могут автоматизировать подобные задачи. Однако такие методики требуют дополнительного опыта и знаний в разработке.
Если у вас есть другие идеи или предложения по упрощению данного процесса, не стесняйтесь делиться ими с сообществом — обмен опытом помогает находить новые, более эффективные решения.