Зіставлення елементів XML із клітинками в карті XML

Застосовується до
Excel для Microsoft 365 Excel 2024 Excel 2021

Щоб імпортувати та експортувати XML-дані в програмі Excel, використовуйте карту XML, яка пов'язує елементи XML із даними у клітинках. Це допоможе отримати потрібні результати. Щоб створити карту XML, знадобляться файл XML-схеми (XSD) і файл даних XML (.xml). Створивши карту XML, можна зіставляти елементи XML належним чином.

Порада.

Додаткові відомості про використання даних XML у програмі Excel див. в огляді функції XML у програмі Excel.

Примітка.

Excel для настільних комп'ютерів із Windows – це єдина версія, яка пропонує функції зіставлення XML. Ці інструменти недоступні в Excel для Mac і вебпрограма Excel.

Карти XML – це застаріла функція. Імпортувати й перетворювати XML-дані, особливо для складних або вкладених структур, можна за допомогою Power Query у програмі Excel.

Пошук і створення XML-схеми та файлів даних XML

Якщо іншу базу даних або програму створено на основі XML-схеми або файлу даних XML, можливо, ця схема й файл уже доступні. Вони, наприклад, можуть використовуватися, щоб створити бізнес-програму, яка експортує дані в ці формати файлів XML для комерційного веб-сайту або веб-служби, які надають ці файли XML, або розробленої на замовлення ІТ-відділом програми, яка автоматично створює ці файли XML.

Якщо у вас немає необхідних файлів XML, їх можна створити, зберігши потрібні дані як текстовий файл. Потім можна скористатися програмами Access і Excel, щоб перетворити цей текстовий файл на потрібний файл XML. Ось як це зробити:

Access

  1. Імпорт текстового файлу, який потрібно перетворити та зв’язати з новою таблицею.

    1. Виберіть "Відкрити файл>".
    2. У діалоговому вікні Відкрити виберіть і відкрийте базу даних, у якій потрібно створити нову таблицю.
    3. Виберітьтекстовий файліз зовнішніми даними>, виконуйте інструкції для кожного кроку, переконавшись, що таблицю зв'язано з текстовим файлом.
      Створюється нова таблиця, яка відображається в області переходів.

  2. Експортуйте дані зі зв’язаної таблиці у файл даних XML і файл XML-схеми.

    1. Вибір зовнішніх даних>More>XML File (у групі "Експорт ").
    2. У діалоговому вікні Експорт – XML-файл укажіть ім'я файлу та формат, а потім натисніть кнопку ОК.

  3. Вийдіть із програми Access.

Excel

  1. Створіть карту XML на основі файлу XML-схеми, експортованого з програми Access.
    Якщо з’явиться діалогове вікно Декілька коренів, переконайтеся, що вибрано елемент dataroot. Це дасть можливість створити таблицю XML.
  2. Створіть XML-таблицю, зіставивши кореневий елемент даних . Докладні відомості див. в розділі "Зіставлення елементів XML ".
  3. Імпортуйте XML-файл, експортований із програми Access.

Крім того, за допомогою Power Query можна імпортувати XML-дані безпосередньо, не створюючи файл XML-схеми.

Примітка.

  • Програма Excel не підтримує кілька типів конструкцій елементів в XML-схемах. Ці конструкції XML-схем не можна імпортувати до програми Excel:
  • <будь-який> Цей елемент дає змогу включати елементи, не оголошені у схемі.
  • <anyAttribute> Цей елемент дає змогу включати атрибути, не оголошені у схемі.
  • Рекурсивні структури. Прикладом такої структури може бути ієрархія співробітників і керівників, у якій ті самі елементи XML містять кілька вкладених рівнів. Програма Excel не підтримує рекурсивні структури, які містять більше одного рівня.
  • Абстрактні елементи. Ці елементи можна оголошувати у схемі, але вони ніколи не використовуються як елементи. Абстрактні елементи залежать від інших елементів, які заміщують абстрактний елемент.
  • Групи заміщення. Ці групи дають змогу заміщати елемент іншим елементом, де б він не розташовувався. Елемент указує, що він є членом групи заміщення іншого елемента за допомогою атрибута <substitutionGroup> .
  • Змішаний вміст. Цей вміст оголошується під час використання елемента mixed="true" у визначенні складного типу. Програма Excel не підтримує простий вміст складного типу, але підтримує дочірні позначки та атрибути, визначені в такому складному типі.

Використання XML-схеми та файлів даних XML

Наведений нижче зразок даних містить основні елементи та структури XML, за допомогою яких можна перевірити XML-карти, якщо немає XML-файлів або текстових файлів, з яких ці XML-файли можна створити. Ось як можна зберегти цей зразок даних у файлі на комп'ютері:

  1. Виділіть у файлі зразок тексту, який потрібно скопіювати, і натисніть клавіші Ctrl+C.
  2. Запустіть програму "Блокнот" і натисніть клавіші Ctrl+V, щоб вставити зразок тексту.
  3. Натисніть клавіші Ctrl+S, щоб зберегти файл з ім’ям і розширенням файлу, що відповідає скопійованому зразку даних.
  4. Натисніть клавіші Ctrl+N у Блокноті та повторіть кроки 1–3, щоб створити файл для другого зразка тексту.
  5. Закрийте Блокнот.

Зразок XML-даних (Витрати.xml)


<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<Root>
  <EmployeeInfo>
    <Name>Jane Winston</Name>
    <Date>2001-01-01</Date>
    <Code>0001</Code>
  </EmployeeInfo>
  <ExpenseItem>
    <Date>2001-01-01</Date>
    <Description>Airfare</Description>
    <Amount>500.34</Amount>
  </ExpenseItem>
  <ExpenseItem>
    <Date>2001-01-01</Date>
    <Description>Hotel</Description>
    <Amount>200</Amount>
  </ExpenseItem>
  <ExpenseItem>
    <Date>2001-01-01</Date>
    <Description>Taxi Fare</Description>
    <Amount>100.00</Amount>
  </ExpenseItem>
  <ExpenseItem>
    <Date>2001-01-01</Date>
    <Description>Long Distance Phone Charges</Description>
    <Amount>57.89</Amount>
  </ExpenseItem>
  <ExpenseItem>
    <Date>2001-01-01</Date>
    <Description>Food</Description>
    <Amount>82.19</Amount>
  </ExpenseItem>
  <ExpenseItem>
    <Date>2001-01-02</Date>
    <Description>Food</Description>
    <Amount>17.89</Amount>
  </ExpenseItem>
  <ExpenseItem>
    <Date>2001-01-02</Date>
    <Description>Personal Items</Description>
    <Amount>32.54</Amount>
  </ExpenseItem>
  <ExpenseItem>
    <Date>2001-01-03</Date>
    <Description>Taxi Fare</Description>
    <Amount>75.00</Amount>
  </ExpenseItem>
  <ExpenseItem>
    <Date>2001-01-03</Date>
    <Description>Food</Description>
    <Amount>36.45</Amount>
  </ExpenseItem>
  <ExpenseItem>
    <Date>2001-01-03</Date>
    <Description>New Suit</Description>
    <Amount>750.00</Amount>
  </ExpenseItem>
</Root>

Зразок XML-схеми (Витрати.xsd)


<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="Root">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element minOccurs="0" maxOccurs="1" name="EmployeeInfo">
          <xsd:complexType>
            <xsd:all>
              <xsd:element minOccurs="0" maxOccurs="1" name="Name" />
              <xsd:element minOccurs="0" maxOccurs="1" name="Date" />
              <xsd:element minOccurs="0" maxOccurs="1" name="Code" />
            </xsd:all>
          </xsd:complexType>
        </xsd:element>
        <xsd:element minOccurs="0" maxOccurs="unbounded" name="ExpenseItem">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="Date" type="xsd:date"/>
              <xsd:element name="Description" type="xsd:string"/>
              <xsd:element name="Amount" type="xsd:decimal" />
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Створення карти XML

Створіть карту XML, додавши XML-схему до книги. Можна скопіювати схему з файлу XML-схеми (XSD), або програма Excel може спробувати створити її на основі файлу даних XML (.xml).

  1. Виберіть джерело для розробників>.
    Команди XML на вкладці
    Якщо вкладка Розробник не відображається, див. статтю Відображення вкладки "Розробник".

  2. В області завдань "Джерело XML " виберіть пункт "Карти XML", а потім натисніть кнопку "Додати".

  3. У списку "Пошук в " виберіть диск, папку або розташування в Інтернеті, де міститься файл, який потрібно відкрити.

  4. Виберіть файл і натисніть кнопку "Відкрити".

    • Для файлу XML-схеми в програмі Excel створюється карта XML на основі XML-схеми. Якщо відображається діалогове вікно Декілька коренів, виберіть один із кореневих вузлів, визначених у файлі XML-схеми.
    • Для файлу XML-даних програма Excel намагається отримати XML-схему з даних XML, а потім створити карту XML.

  5. Натисніть кнопку OK.
    Карта XML відобразиться в області завдань " Джерело XML ".

Зіставлення елементів XML

Зіставте елементи XML з окремо зіставленими клітинками та повторюваними клітинками в XML-таблицях, щоб можна було створити зв'язок між клітинкою та елементом даних XML в XML-схемі.

  1. Виберіть джерело для розробників>.
    Команди XML на вкладці
    Якщо вкладка Розробник не відображається, див. статтю Відображення вкладки "Розробник".

  2. В області завдань Джерело XML виберіть елементи, які потрібно зіставити.
    Щоб вибрати несуміжні елементи, виділіть один елемент, а потім, утримуючи натиснутою клавішу Ctrl, виберіть усі елементи, які потрібно зіставити.

  3. Щоб зіставити елементи, виконайте такі дії:

    1. Клацніть правою кнопкою миші вибрані елементи та виберіть пункт "Зіставити елемент".

    2. У діалоговому вікні "Зіставлення елементів XML" виберіть клітинку й натисніть кнопку "OK".

      Порада.

      Можна також перетягувати вибрані елементи до того місця аркуша, у якому їх потрібно відобразити.

      Кожен елемент відображається жирним шрифтом в області завдань Джерело XML, указуючи на те, що елемент зіставлено.

  4. Вирішіть, як потрібно обробляти підписи та заголовки стовпців.

    • Під час перетягування на аркуш елемента XML, який не повторюється, щоб створити окремо зіставлену клітинку, відображається дія із трьома наведеними нижче командами, за допомогою яких можна контролювати розташування заголовка або підпису.
      Дані вже містять заголовок. Виберіть цей параметр, щоб ігнорувати заголовок елемента XML, тому що клітинка вже має заголовок (ліворуч від даних або над ними).
      Заголовок XML зліва. Установіть цей прапорець, щоб використовувати заголовок елемента XML як підпис клітинки (ліворуч від даних).
      Заголовок XML згори. Установіть цей прапорець, щоб використати заголовок елемента XML як заголовок клітинки (над даними).

    • Під час перетягування на аркуш елемента XML, який повторюється, щоб створити повторювані клітинки в XML-таблиці, імена елементів XML автоматично використовуються як заголовки стовпців для таблиці. Однак, можна змінити заголовки стовпців на будь-які потрібні заголовки, відредагувавши клітинки заголовка стовпця.
      В області завдань " Джерело XML " виберіть " Параметри ", щоб отримати додаткові можливості керування поведінкою XML-таблиці:
      Автоматично об'єднувати елементи під час зіставлення. Коли цей прапорець установлено, програма Excel автоматично розширює XML-таблиці під час перетягування елемента до клітинки, суміжної з XML-таблицею.
      Дані із заголовками. Коли цей прапорець установлено, наявні дані можна використати як заголовки стовпців під час зіставлення повторюваних елементів в аркуші.

      Примітка.

      • Якщо всі команди XML неактивні й вам не вдалося зіставити елементи XML із жодними клітинками, можливо, до книги надано спільний доступ. Натисніть кнопку "Перевірити> спільнийдоступ", щоб перевірити це та, за потреби, вилучити її зі спільного використання.

Якщо потрібно зіставити елементи XML у книзі, до якої необхідно надати спільний доступ, зіставте елементи XML із потрібними клітинками, імпортуйте дані XML, видаліть усі карти XML і надайте спільний доступ до книги. > - Якщо не вдалося скопіювати таблицю XML, що містить дані, до іншої книги, можливо, з нею пов'язано карту XML, яка визначає структуру даних. Ця карта XML зберігається в книзі, але якщо скопіювати таблицю XML до нової книги, карту XML не буде додано автоматично. Замість того, щоб копіювати таблицю XML, програма Excel створить таблицю Excel, яка містить такі ж дані. Щоб перетворити нову таблицю на таблицю XML, зробіть ось що: > 1. Додайте карту XML до нової книги, скориставшись файлом XML або XSD, за допомогою якого ви створили вихідну карту XML. Слід зберегти ці файли, якщо вам потрібно додати карти XML до інших книг. > 1. Зіставте елементи XML у таблиці, щоб створити XML-таблицю. > > - Під час зіставлення повторюваного елемента XML в об'єднаній клітинці програма Excel скасовує об'єднання. Це очікувана поведінка, адже повторювані елементи не призначені для роботи з об’єднаними клітинками.

Одиночні (неповторювані) елементи XML можна зіставити з об’єднаною клітинкою, але зіставлення повторюваного елемента XML (або елемента, який містить повторюваний елемент) з об’єднаною клітинкою не підтримується. Клітинки буде роз'єднано, а цей елемент буде зіставлено з клітинкою, де розташовано вказівник миші.

Порада.

  • Можна скасувати зіставлення елементів XML, які не потрібно використовувати, або заборонити перезаписування вмісту клітинок під час імпорту XML-даних. Наприклад, можна тимчасово скасувати зіставлення елемента XML з однією клітинкою або повторюваними клітинками, що містять формули, які не потрібно перезаписувати під час імпорту XML-файлу. Коли імпорт буде завершено, можна зіставити елемент XML із клітинками формул знову, щоб експортувати результати формул до файлу даних XML.
  • Щоб скасувати зіставлення елементів XML, клацніть правою кнопкою миші їх імена в області завдань " Джерело XML " і виберіть команду "Видалити елемент".

Відображення вкладки "Розробник"

Якщо вкладка "Розробник " не відображається, виконайте такі дії, щоб її відобразити:

  1. Виберіть"Параметрифайлів>".
  2. Виберіть категорію "Настроювання стрічки ".
  3. На вкладках основних вкладок установіть прапорець біля поля Розробник і натисніть кнопку OK.

Додаткові відомості

Видалення відомостей карти XML із книги

Додавання або перезаписування зіставлених XML-даних

Огляд мови XML у програмі Excel

Імпорт XML-даних

Експорт XML-даних