Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Импорт данных из таблицы XML, неверный формат данных при импорте
 
#1
30.06.2025 16:39:28
Здравствуйте!

Я импортирую данные из файла XML в ячейки с помощью функций ФИЛЬТР.XML и ВЕБСЛУЖБА
Данные импортируются, но проблема в том, что импорт некоторых происходит в неверном формате

Таблица выглядит так:



1) Здесь должны отобразиться дата и время в виде 2025-06-30 14:02:38
2) Здесь нужно заменить точку на запятую, чтобы Excel воспринимал это значение как число. Пробовал через "ПОДСТАВИТЬ" с аргументом ;".";"," но не работает.
3) Здесь тоже нужно поставить запятую вместо точки чтобы получилось число. Нули, надеюсь, сами уберутся.
4) И здесь тоже нужно поставить запятую вместо точки чтобы получилось число. Нули, надеюсь, сами уберутся.
5) Должна быть дата в виде 2025-09-18, но вместо этого отображается число
6) Здесь вообще странно. В оригинале тут стоит 18.78, число, в котором потом нужно заменить точку на запятую.

Сделать это необходимо без изменения формата самих ячеек.

Буду признателен за помощь и наводки, заранее спасибо!
 
 
 
#2
30.06.2025 17:14:04
См. файл. Кроме п.6
Согласие есть продукт при полном непротивлении сторон
 
 
 
#3
30.06.2025 17:25:18
Цитата
Sanja написал:
См. файл. Кроме п.6
Table (2).xlsx  (11.29 КБ)

Спасибо!!
Заодно сообразил, как правильно работать с функцией ПОДСТАВИТЬ
 
 
 
#4
30.06.2025 18:48:03
Цитата
Verticalx написал: В оригинале тут стоит 18.78, число
В оригинале стоит значение 1.88, которое Excel интерпретирует как дату 01.01.1988, но т.к. формат ячейки Общий эта дата записывается как число, т.е. 32143.
Конкретно эту ситуацию можно поправить так
Код
=--ПОДСТАВИТЬ(ТЕКСТ(ФИЛЬТР.XML(ВЕБСЛУЖБА("iss.moex.com/iss/engines/futures/markets/forts/securities/" & B1 & ".xml?iss.meta=off&iss.only=securities&securities.columns=BUYSELLFEE");"//document//data//rows//@BUYSELLFEE");"М.ГГ");".";",")

но если значение не будет похоже на дату, по мнению Excel, то возможно получите некорректный результат или ошибку
Согласие есть продукт при полном непротивлении сторон
 
 
 
#5
30.06.2025 20:57:48
Цитата
Sanja написал:
В оригинале стоит значение 1.88, которое Excel интерпретирует как дату 01.01.1988, но т.к. формат ячейки Общий эта дата записывается как число, т.е. 32143.Конкретно эту ситуацию можно поправить так

Да, я перепутал с другим тикером. Там 1.88 было в оригинале

Спасибо за подсказку

Еще получилось обойти это с помощью двойного подставления функции ПОДСТАВИТЬ:

Код
=ФИЛЬТР.XML(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ВЕБСЛУЖБА("iss.moex.com/iss/engines/futures/markets/forts/securities/" & B1 & ".xml?iss.meta=off&iss.only=securities&securities.columns=BUYSELLFEE");".";",");"1,0";"1.0");"//document//data//rows//@BUYSELLFEE")
Изменено: Verticalx - 30.06.2025 21:01:28
 
 
 
#6
30.06.2025 20:59:25
Подскажите,

- первый раз столкнулся с бинарным отрицанием (два тире), какую роль оно выполняет в данном случае?

- возможно ли пункты 1) и 5) импортировать в ячейку как текст, не изменяя ее формат на "Дата" или "Все форматы"?
Изменено: Verticalx - 30.06.2025 21:06:10
 
 
 
#7
30.06.2025 21:17:41
Двойное отрицание
Если не менять формат ячейки, то в ячейке Вы получите именно ТЕКСТ похожий на дату, а не ДАТУ, с которой можно работать именно как с датой
Согласие есть продукт при полном непротивлении сторон
 
 
 
#8
01.07.2025 00:38:27
Цитата
Sanja написал:
Если не менять формат ячейки, то в ячейке Вы получите именно ТЕКСТ похожий на дату, а не ДАТУ, с которой можно работать именно как с датой

Да, то что с этими данными нельзя будет работать как с датой - понятно
Непонятно почему при импорте вместо даты в виде текста вставляется некое число, на дату не похожее
 
 
 
#9
01.07.2025 05:11:35
Цитата
Verticalx написал: вставляется некое число
Это число - количество дней от 01.01.1900 до указанной даты
Основные принципы работы с датами и временем в Excel
В формулах с датами уберите двойное отрицание и формат ячейки установите Общий
Согласие есть продукт при полном непротивлении сторон
 
 
 
#10
01.07.2025 12:55:35
Понятно, спасибо за разъяснение
 
 
 
#11
04.07.2025 01:20:01
Пишу еще раз, потому что заметил странную вещь.

Вот эта формула
Цитата
Sanja написал:
Конкретно эту ситуацию можно поправить такКод ? 1=--ПОДСТАВИТЬ(ТЕКСТ(ФИЛЬТР.XML(ВЕБСЛУЖБА("iss.moex.com/iss/engines/futures/markets/forts/securities/" & B1 & ".xml?iss.meta=off&iss.only=securities&securities.columns=BUYSELLFEE");"//document//data//rows//@BUYSELLFEE");"М.ГГ");".";",")

Сейчас выдает 12,25:


Откуда взялось это число - загадка. По идее, должно восприниматься как дата "07.1912".

И в то же время вот эта формула:
Цитата
Verticalx написал:
Еще получилось обойти это с помощью двойного подставления функции ПОДСТАВИТЬ:Код ? 1=ФИЛЬТР.XML(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ВЕБСЛУЖБА("iss.moex.com/iss/engines/futures/markets/forts/securities/" & B1 & ".xml?iss.meta=off&iss.only=securities&securities.columns=BUYSELLFEE");".";",");"1,0";"1.0");"//document//data//rows//@BUYSELLFEE")

Выдает правильное. Хотя позавчера и она барахлила.

Нет ли идей откуда берется 12,25, чтобы понять можно ли с этим что-то сделать?
Изменено: Verticalx - 04.07.2025 01:21:36
 
 
 
#12
04.07.2025 05:06:39
Цитата
Verticalx написал: Нет ли идей откуда берется 12,25, чтобы понять можно ли с этим что-то сделать?
Ну опять же 7.12 Excel воспринимает как дату 07.12.2025 (это можно увидеть, если в ячейке A22 применить формат Дата), шаблоном функции ТЕКСТ(), "М.ГГ", Вы из этой даты берете только месяц и год - 12.25, ну а потом точку меняете на запятую.
Я писал, что
Цитата
Sanja написал: возможно получите некорректный результат или ошибку
Согласие есть продукт при полном непротивлении сторон
 
 
 
#13
04.07.2025 11:01:46
Понятно.
А можно заставить эксель, чтобы он воспринимал данные XML строго как текст, а потом путем замены точки на запятую преобразовать данные в число?

Проще говоря, какой-нибудь железный способ, нет такого? А то получается хождение по минному полю. Если вручную каждый раз перепроверять, правильно ли импортированы данные из XML, тогда смысл автоматизации пропадает  :sceptic:
 
 
 
#14
04.07.2025 11:35:55
Задача конечно не понятна на перспективу... но почему не воспользоваться PQ? Мне кажется было бы надежнее
 
 
 
#15
06.07.2025 16:00:40
Цитата
txxt написал:
Задача конечно не понятна на перспективу... но почему не воспользоваться PQ? Мне кажется было бы надежнее

Я не знаю что такое PowerQuery и как оно работает.
PQ решает все эти проблемы, можно пример для текущей задачи?
 
 
 
#16
06.07.2025 16:52:37
Что такое Power Query / Pivot / Map / View / BI и зачем они пользователю Excel
Согласие есть продукт при полном непротивлении сторон
 
 
 
Страницы: 1
Читают тему
Наверх