Фильтр xml excel что это
Я уже неоднократно разбирал способы импорта данных в Excel из интернета с последующим автоматическим обновлением. В частности:
- В старых версиях Excel 2007-2013 это можно было сделать с помощью прямого веб-запроса.
- Начиная с 2010 года это можно очень удобно делать с помощью надстройки Power Query.
К этим способам в последних версиях Microsoft Excel теперь можно добавить ещё один - импорт данных из интернета в формате XML с помощью встроенных функций.
Начиная с версии 2013 в Excel появились две функции для прямой загрузки XML-данных из интернета в ячейки листа: ВЕБСЛУЖБА (WEBSERVICE) и ФИЛЬТР.XML (FILTERXML) . Работают они в паре - сначала функция ВЕБСЛУЖБА выполняет запрос к нужному сайту и возвращает его ответ в формате XML, а затем с помощью функции ФИЛЬТР.XML мы «разбираем» этот ответ на составляющие, извлекая из него нужные нам данные.
Давайте рассмотрим работу этих функций на классическом примере – импорте курса любой нужной нам валюты на заданный интервал дат с сайта Центробанка России. В качестве заготовки будем использовать вот такую конструкцию:
- В жёлтых ячейках находятся даты начала и окончания интересующего нас периода.
- В синей сделан выпадающий список валют при помощи команды Данные - Проверка - Список (Data - Validation - List) .
- В зеленых ячейках мы будем использовать наши функции, чтобы создать строку запроса и получить ответ сервера.
- Таблица справа - справочник по кодам валют (потребуется нам чуть позже).
Шаг 1. Формируем строку запроса
Как видно из примера, строка запроса должна содержать даты начала (date_req1) и окончания (date_req2) интересующего нас периода и код валюты (VAL_NM_RQ), курс которой мы хотим получить. Коды основных валют вы можете найти в таблице ниже:
The FILTERXML function returns specific data from XML content by using the specified xpath.
The FILTERXML function is not available in Excel for the web and Excel for Mac.
This function may appear in the function gallery in Excel for Mac, but it relies on features of the Windows operating system, so it will not return results on Mac.
Syntax
FILTERXML(xml, xpath)
The FILTERXML function syntax has the following arguments.
Description
A string in valid XML format
A string in standard XPath format
Examples
This example uses the FILTERXML function on XML data returned in cell B2, which is provided by the results of the WEBSERVICE function on the web query in cell B1.
The data from FILTERXML is returned in cells B3:B5 and C3:C5, and shows the three most recently updated query results on Wikipedia and the time of their updates (in UTC, "Coordinated Universal Time").
Cells B3:B5 contain the formula =FILTERXML(B3,"//rc/@title").
Cells C3:C5 contain the formula =FILTERXML(B3,"//rc/@timestamp").
Note: If you have a current version of Microsoft 365, then you can simply enter the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by first selecting the output range, entering the formula in the top-left-cell of the output range, and then pressing CTRL+SHIFT+ENTER to confirm it. Excel inserts curly brackets at the beginning and end of the formula for you. For more information on array formulas, see Guidelines and examples of array formulas.
=WEBSERVICE("http://dev.markitondemand.com/MODApis/Api/Quote/xml?symbol ocpLegacyBold">=FILTERXML(WEBSERVICE("http://dev.markitondemand.com/MODApis/Api/Quote/xml?symbol="&ENCODEURL(C2)),"//QuoteApiModel/Data/LastPrice")
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
Функция FILTERXML возвращает определенные данные из XML-содержимого с помощью указанного xpath.
Функция FILTERXML недоступна в Excel в Интернете и Excel для Mac.
Эта функция может отображаться в коллекции функций Excel для Mac, но она использует функции операционной системы Windows, поэтому она не возвращает результаты на компьютере Mac.
Синтаксис
ФИЛЬТР.XML(xml; xpath)
Аргументы функции ФИЛЬТР.XML указаны ниже.
Строка в допустимом формате XML
Строка в стандартном формате XPath
Примеры
В этом примере функция FILTERXML используется для данных XML, возвращенных в ячейку B2, которые предоставляются результатами функции ВЕБСЛУЖБА в веб-запросе в ячейке B1.
Данные из фильтраXML возвращаются в ячейки B3:B5 и C3:C5 и показывают три последних обновленных результата запроса по Википедии и время их обновления (в UTC, "Время в согласованном универсальном времени").
Ячейки B3:B5 содержат формулу =ФИЛЬТРXML(B3;"//rc/@title").
Ячейки C3:C5 содержат формулу =ФИЛЬТРXML(B3;"//rc/@timestamp").
Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
=ВЕБСЛУЖБА("http://dev.markitondemand.com/MODApis/Api/Quote/xml?symbol Последняя цена акций" в ячейке C2, можно использовать:
=FILTERXML(WEBSERVICE("http://dev.markitondemand.com/MODApis/Api/Quote/xml?symbol="&ENCODEURL(C2)),"//QuoteApiModel/Data/LastPrice")
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Все говорят, что XML - это самая важная новая функция в Office 2003. Но если вы не работаете в компании, где много распространенных XML-схем, вы, вероятно, не смогли бы придумать, как скопировать эту классную демонстрацию, которую вы видел, как это сделала Microsoft во время выпуска Office 2003. Те из вас, кто подписывается на информационный бюллетень Вуди, теперь понимают, что Вуди собирается сериализовать объяснение XML, заставляя нас ждать 4 недели (или больше), чтобы получить всю историю. Итак, прежде чем Вуди сможет перейти к сути, я дам длинный совет, объясняющий, как в полной мере использовать XML в Office 2003.
Великое обещание
Microsoft заявляет, что теперь вы можете хранить данные в формате XML и легко изменять их назначение. Откройте тот же файл данных, и он будет выглядеть в Excel в одном направлении, откройте файл данных и в Word он будет выглядеть иначе. Это очень мощно. У нас также есть тот факт, что, поскольку Office 2003 считает XML собственным форматом файлов, любой, кто владеет любым языком программирования, может писать собственные файлы XML. Если у вас есть QBasic или Rexx или что-то еще, теперь вы можете создавать файлы XML Excel (ExcelML?) На лету.
Только в Excel Professional
Теперь мы столкнулись с системой классов в Office 2003. Полная поддержка XML недоступна в этих выпусках: (Коды, выделенные курсивом, обозначают, где вы можете купить каждый: R = Розничная торговля, P = Предустановлен с новым компьютером, V = Корпоративное лицензирование , A = академическое лицензирование, S = школьные реселлеры).
- Microsoft Office Small Business Edition 2003 (R, P, V, A)
- Microsoft Office Standard Edition 2003 (R, V, A, S)
- Microsoft Office Basic Edition 2003 (P)
- Microsoft Office для студентов и преподавателей, выпуск 2003 (R, S)
Полная поддержка XML доступна в следующих выпусках:
- Microsoft Office Professional Edition 2003 (R, P, V, A, S)
- Microsoft Office Professional Enterprise Edition 2003 (включает InfoPath) (V)
- Microsoft Excel 2003 (R) (Автономный ящик Excel считается профессиональным)
Если вам не повезло с одной из «меньших» версий Office 2003, самым дешевым вариантом обновления может быть покупка розничной коробочной версии Excel. Если вы не работаете в корпоративной среде, единственный способ получить InfoPath (новый инструмент, позволяющий создавать формы и схемы XML) - это купить коробочную версию для розничной продажи.
Что такое XML? Это как HTML?
XML означает расширяемый язык разметки. Если вы когда-нибудь просматривали представление «Просмотр исходного кода» веб-страницы в Блокноте, вы знакомы со структурой XML. В то время как HTML допускает определенные теги, такие как TABLE, BODY, TR, TD, XML допускает любые теги. Вы можете создать любой тег для описания ваших данных. Вот скриншот некоторых XML-данных, которые я ввел в блокнот:
Вот несколько простых правил XML:
- Каждый бит данных должен начинаться и заканчиваться идентичным тегом: Data
- Имена тегов чувствительны к регистру. и НЕ являются допустимыми тегами, потому что использование заглавных букв в конечном теге не совпадает с начальным тегом.
- XML-файл должен начинаться и заканчиваться корневым тегом. В файле может быть только один корневой тег. В приведенном выше примере корневой тег -.
- У вас может быть пустой тег - поставьте косую черту в конце тега вместо начала:
- Если вы вкладываете теги, вы должны закрыть внутренний тег перед закрытием внешнего тега. Хотя HTML допускает такую структуру, нажмите «Отмена», это недопустимо в XML. данные будут работать, а данные - нет.
Алфавитный суп: три типа файлов
Вот три файла, с которыми мы столкнемся при работе с XML.
- .XML - это файл данных, показанный выше
- .XSD - это определение схемы XML. Это очень важный файл. Здесь определяются отношения данных. Здесь также определяется проверка данных. Для того, чтобы фактически выполнить демонстрацию Microsoft, вам понадобится файл схемы XML. Хотя любой может ввести XML в блокнот, нам нужна схема, чтобы делать что-нибудь крутое. Я покажу вам, как его создать ниже.
- .XSL - это файл XML StyleSheet Language, который позволяет вам преобразовывать данные из одного формата в другой.
Безусловно, самая большая проблема - это как создать файл схемы. Вы можете открыть XML-файл в Excel, но вы не можете выполнять преобразования без схемы. К счастью, Excel создаст для вас схему по умолчанию, но НЕ очевидно, как ее использовать. Пройдем по ступеням.
Откройте пустой Блокнот. Скопируйте эти данные снизу и вставьте в Блокнот. Сохраните файл как test.xml.
Сначала вам будет представлено диалоговое окно Open XML. Позже мы захотим использовать мощную панель задач «Источник XML», но мы не сможем этого сделать, пока у нас не будет действующей схемы. Прямо сейчас выберите открытие в виде списка XML.
Вот наши данные в Excel. Это довольно круто. Они включают замечательную новую функцию списка Excel 2003 (обычное сочетание клавиш: Ctrl + L). С включенной функцией List у нас есть автофильтры для каждого столбца и строка со звездочкой в стиле Access для добавления новых данных.
А теперь - недокументированный трюк. Достоинства гуру Microsoft Excel XML Чада Ротшиллера за то, что он показал нам этот крутой трюк. Запустите редактор Visual Basic с помощью Alt + F11. В редакторе Visual Basic нажмите Ctrl + G, чтобы сразу открыть панель. На непосредственной панели введите:
Немедленное окно в VBE
Схема на непосредственной панели
Откройте пустой блокнот, скопируйте данные из непосредственного окна и вставьте в пустой блокнот. Теперь вы можете увидеть полную схему нашего простого набора данных.
Сохранить файл схемы
Теперь вы можете сохранить файл блокнота как TodaysOrders.xsd.
В Excel снова откройте test.xml. На этот раз укажите, что вы хотите использовать область задач «Источник XML», а затем нажмите кнопку «ОК» в поле сведений о схеме.
Теперь у вас есть пустой лист, но на панели «Источник XML» справа перечислены все доступные поля.
Выберите раскрывающийся список «Параметры» и выберите «Предварительный просмотр данных», чтобы просмотреть образец каждого элемента на панели задач.
Добавьте красиво отформатированный заголовок в пустую таблицу. Перетащите элемент «Заказ на продажу» в ячейку B6.
На панели инструментов выберите Обновить XML-данные.
Образец XML-данных, который мы ввели выше, добавляется в электронную таблицу.
Обновление данных ежедневно
Если вы можете заставить свою систему ежедневно записывать ежедневные заказы в Test.XML, то все готово. Откройте OrderReport.xls, обновите XML-данные, и каждый день вы будете получать хорошо отформатированный отчет о ваших XML-заказах. Это довольно мощно - я настраиваю форматирование один раз, открываю файл каждый день, нажимаю «Обновить», и у меня есть красиво отформатированный отчет с данными из набора XML.
Ежедневно обновлять XML-данные
Использование Excel для создания XML-данных
Можем ли мы использовать Excel для создания новых данных XML? Теперь, когда у нас есть определенная схема, да, мы можем! В нашу существующую электронную таблицу мы можем попросить наших заказчиков по телефону добавлять новые данные в файл Excel в течение дня. В конце дня используйте File - SaveAs. Выберите для сохранения как тип: XML-данные.
Сохранить как данные XML
Подтвердите, какую карту использовать:
Диалоговое окно экспорта XML
Переделка данных в Word
Теперь, когда мы создали схему и данные, давайте откроем данные в Microsoft Word 2003. После открытия TestAsData.xml в Word 2003 мы получаем представление данных по умолчанию в Word.
Открыть файл данных XML в Microsoft Word
Вы можете отформатировать этот документ:
Нажмите Ctrl + Shift + X, чтобы отключить поля:
Отключить поля XML в Word
Использование представлений XSL для перепрофилирования данных
В приведенном выше примере с использованием перетаскивания фактически не создается преобразование. Если вы создаете файлы преобразования, вы можете легко преобразовать данные XML в различные представления в Word. Вот где проявляется реальная сила. Прежде чем я покажу вам, как создать собственное преобразование, давайте рассмотрим, как Microsoft настраивала демонстрацию в выпуске Office 2003.
Чтобы попробовать это на своей копии Word 2003, загрузите XML Sample Package. ZIP-файл содержит:
- Один файл XML: SampleMemo.xml
- Одна схема: Memo.xsd
- Три файла преобразования: Elegant.xsl, professional.xsl, Contemporary.xsl
Следуй этим шагам:
В меню выберите Инструменты - Шаблоны и надстройки…
Щелкните вкладку Схема XML и выберите Добавить схему…
Перейдите туда, куда вы разархивировали файлы примеров. Выберите Memo.xsd и нажмите «Открыть».
В диалоговом окне «Параметры схемы» дайте схеме понятное имя в поле «Псевдоним». Может, что-то вроде «Памятка». Щелкните ОК.
Добавить псевдоним в схему
На этом этапе Word теперь знает о схеме памятки. Затем нам нужно сделать так, чтобы Word 2003 знал о преобразованиях. Обратите внимание, что Microsoft называет это «решениями».
Вы по-прежнему должны быть в диалоговом окне «Шаблоны и надстройки». Щелкните Библиотека схем…
Перейдите в папку с вашими файлами .xsl. Выберите Contemporary.xsl и нажмите «Открыть».
В диалоговом окне «Параметры решения» дайте файлу преобразования понятное имя, например «Contemporary». Щелкните ОК.
Повторите шаги с 8 по 10, чтобы добавить Elegant.xsl и Professional.xsl. Щелкните OK, чтобы закрыть библиотеку схем. Нажмите ОК, чтобы закрыть шаблоны и надстройки.
Завершить библиотеку схем
В демонстрации Microsoft все эти 11 шагов были сделаны за кулисами. Вошла публика, и мы наблюдали, как они показывают нам эти шаги.
Мы собираемся открыть SampleMemo.xml в Word. Вот вид этого файла в блокноте. Это простые данные XML без какого-либо форматирования.
В Word откройте SampleMemo.xml. Незаметно для себя Microsoft просматривает XML-файл, чтобы обнаружить, что это данные, используя схему Memo. Он смотрит в библиотеку схем, чтобы увидеть, есть ли совпадение в схеме «памятки». Когда есть, он находит все добавленные нами решения XSL. Поскольку у нас их три, Word выбирает один для отображения, а затем предлагает нам остальные в области задач XML-документа. Это потрясающе.
Вот панель просмотра данных XML:
Панель представлений данных XML
Вот представление данных по умолчанию (используется Elegant xsl).
Просмотр данных по умолчанию
Нажмите «Только данные», и вы получите только данные без форматирования.
Просмотр только данных
Нажмите Contemporary, и через несколько секунд вы увидите данные в совершенно другом формате:
Вы можете показать / скрыть фиолетовые теги XML с помощью Ctrl + Shift + X.
Создание собственных файлов преобразования XSL
Я вижу силу. Но пока мы использовали только загруженные файлы XSL для чужих данных. Нам действительно нужен способ создания XSL-файла для наших собственных данных. Опять же, если вы работаете в компании, которая поддерживает XML, возможно, кто-то уже настроил эти преобразования. Однако, если вы первый человек в своей компании, попробовавший XML, то вам придется пройти относительно болезненную задачу по настройке собственных файлов преобразования.
Вот как мы это сделаем. Во-первых, я собираюсь создать необычный документ в Word, который покажет, как я хочу отображать XML-данные. Я вставлю удобные для поиска заполнители там, где я хочу, чтобы поля XML помещались. Я сохраню этот документ как Word XML. Затем, используя Блокнот, я собираюсь отредактировать документ, вставить некоторый код XSL и сохранить файл. Это должно создать настраиваемый файл преобразования XSL.
В Word создайте хорошо отформатированный образец. Ниже я построил подтверждение заказа. Здесь есть логотип, вводный текст, поля из файла XML и закрывающий текст. Я использовал разные шрифты и цвета. Условие использования *** для выделения полей просто помогает мне при просмотре XML-файла Word в Блокноте.
Совсем недавно мы разбирали применение функции ФИЛЬТР.XML для импорта XML-данных из интернета - основной задачи, для которой эта функция, собственно, и предназначена. Попутно, однако, всплыло ещё одно неожиданное и красивое применение этой функции - для быстрого деления слипшегося текста по ячейкам.
Предположим, что у нас имеется вот такой столбец с данными:
Само-собой, для удобства хотелось бы разделить его на отдельные колонки: название компании, город, улица, дом. Сделать это можно кучей разных способов:
- Использовать Текст по столбцам с вкладки Данные (Data - Text to columns) и пройти три шага Мастера разбора текстов. Но если завтра данные поменяются, то придётся повторить весь процесс заново.
- Загрузить эти данные в Power Query и поделить там, а затем выгрузить обратно на лист, а потом при изменении данных обновлять запрос (что уже проще).
- Если нужно обновление "на лету", то можно написать несколько весьма сложных формул для поиска запятых и извлечения текста между ними.
А можно поступить изящнее и использовать функцию ФИЛЬТР.XML, но причем тут она?
Функция ФИЛЬТР.XML получает в качестве исходного аргумента XML-код - текст, размеченный специальными тегами и атрибутами, и затем разбирает его на составляющие, извлекая нужные нам фрагменты данных. Обычно XML-код выглядит как-то так:
В XML каждый элемент данных должен быть заключен в теги. Тег - это некий текст (в примере выше это manager, name, profit), заключенный в угловые скобки. Теги всегда идут в паре - открывающий и закрывающий (с добавленным в начало слэшем).
Функция ФИЛЬТР.XML может легко извлечь содержимое всех нужных нам тегов, например, имена всех менеджеров и (самое главное) выдать их сразу всех одним списком. Так что наша задача сводится к тому, чтобы добавить в исходный текст теги, превратив его в XML-код, пригодный для последующего анализа функцией ФИЛЬТР.XML.
Если взять для примера первый адрес из нашего списка, то превратить его нужно будет вот в такую конструкцию:
Глобальный открывающий и закрывающий весь текст тег я назвал t, а теги обрамляющие каждый элемент - s., но можно использовать и любые другие обозначения - это не принципиально.
Если убрать из этого кода отступы и переносы строк - совершенно, кстати, необязательные и добавленные только для наглядности, то всё это превратится в строку:
А её уже можно относительно легко получить из исходного адреса, заменив в нём запятые на пару тегов с помощью функции ПОДСТАВИТЬ (SUBSTITUTE) и приклеив с помощью символа & в начало и конец открывающие и закрывающие теги:
Чтобы развернуть полученный диапазон горизонтально, используем стандартную функцию ТРАНСП (TRANSPOSE) , завернув в неё нашу формулу:
Важной особенностью всей этой конструкции является то, что в новой версии Office 2021 и Office 365 с поддержкой динамических массивов никаких специальных телодвижений для ввода не требуется - просто вводим и жмём на Enter - формула сама занимает нужное ей количество ячеек и всё работает "на ура". В предыдущих же версиях, где динамических массивов ещё не было, потребуется перед вводом формулы сначала выделить достаточное количество пустых ячеек (можно с запасом), а после создания формулы - нажать на сочетание клавиш Ctrl + Shift + Enter , чтобы ввести её как формулу массива.
Похожий трюк можно использовать и при разделении текста слипшегося в одну ячейку через перенос строки:
Разница с предыдущим примером только в том, что вместо запятой мы заменяем здесь невидимый символ переноса строки Alt+Enter, который в формуле можно задать с помощью функции СИМВОЛ (CHAR) с кодом 10.
Читайте также: