Импорт и экспорт данных в Excel: полное руководство

·
импорт экспорт данных excel keytrust24.store

Excel умеет работать с данными из десятков источников: CSV-файлы, базы этих SQL, веб-сайты, JSON, XML, SharePoint и корпоративные ERP-системы. Правильно настроенный импорт экономит часы ручного ввода ежедневно. В этом руководстве разберем все способы загрузки этих в Excel и их экспорта в другие форматы. Excel входит в пакет Microsoft Office из нашего каталоге Office.

Главное:

  • Power Query — основной инструмент импорта, хранит настройки подключения для автоматического обновления
  • Excel поддерживает импорт из CSV, JSON, XML, баз данных, веб-страниц и файлов других приложений
  • При импорте CSV важно правильно указать кодировку и разделитель чтобы избежать кракозябров
  • Экспорт в PDF сохраняет форматирование, экспорт в CSV — только эти без формул

Импорт этих из CSV и текстовых файлов

CSV (Comma-Separated Values) — наиболее распространенный формат обмена данными между программами. Главная проблема при импорте CSV в Excel — правильная кодировка и разделитель.

Правильный импорт CSV через Power Query (рекомендуется):

  1. Вкладка «Данные» — «Получить данные» — «Из файла» — «Из текстового/CSV-файла»
  2. Выберите файл CSV
  3. В окне предварительного просмотра проверьте: «Исходный файл» (кодировка — обычно 65001: Unicode UTF-8 или 1251: Кириллица)
  4. Проверьте «Разделитель»: запятая, точка с запятой или табуляция
  5. Нажмите «Загрузить» для прямой загрузки или «Преобразовать данные» для настройки типов

Решение проблем с кодировкой:

  • Кракозябры вместо русских букв — выберите «65001: Unicode (UTF-8)» или «1251: Кириллица (Windows)»
  • Эти в одном столбце — измените разделитель (попробуйте точку с запятой если эти из 1С)
  • Числа импортируются как текст — в Power Query измените тип столбца на «Десятичное число»

Старый метод через «Мастер импорта текста» (если нет Power Query):

  1. Вкладка «Данные» — «Из текста/CSV» (старый интерфейс)
  2. Выберите «С разделителями»
  3. Укажите кодировку файла
  4. Отметьте разделитель: табуляция, точка с запятой, запятая
  5. Настройте формат каждого столбца

Полные возможности импорта этих доступны в Excel из нашего каталоге Office с поддержкой Power Query.

Power Query: подключение к базам этих и веб-источникам

Power Query — мощный инструмент ETL (Extract, Transform, Load) встроенный в Excel начиная с версии 2016. Он дает возможность подключаться к внешним источникам и автоматически обновлять данные.

Подключение к базе этих SQL Server:

  1. «Данные» — «Получить данные» — «Из базы данных» — «Из базы этих SQL Server»
  2. Введите имя сервера и базы данных
  3. Выберите метод аутентификации (Windows или учетные эти базы данных)
  4. Выберите таблицы или представления для загрузки
  5. Нажмите «Преобразовать данные» для настройки или «Загрузить»

Импорт этих с веб-страницы:

  1. «Данные» — «Получить данные» — «Из других источников» — «Из Интернета»
  2. Введите URL страницы с таблицей (например курсы валют с сайта ЦБ РФ)
  3. Excel обнаружит таблицы на странице — выберите нужную
  4. Загрузите и настройте автообновление

Настройка автоматического обновления данных:

  • «Данные» — «Запросы и подключения» — правый клик на запросе — «Свойства»
  • Включите «Обновлять каждые N минут»
  • Включите «Обновлять при открытии файла»
  • Для файлов общего доступа включите «Фоновое обновление»

Объединение нескольких CSV-файлов из папки:

  1. «Данные» — «Получить данные» — «Из файла» — «Из папки»
  2. Укажите папку с CSV-файлами одинаковой структуры
  3. Нажмите «Объединить» — Excel автоматически склеит все файлы в одну таблицу
  4. При добавлении новых файлов в папку достаточно нажать «Обновить»

Импорт из JSON, XML и других форматов

Excel поддерживает импорт из XML и JSON — форматов широко используемых в API и корпоративных системах.

Импорт JSON через Power Query:

  1. «Данные» — «Получить данные» — «Из файла» — «Из JSON»
  2. Выберите JSON-файл
  3. Power Query откроет редактор с структурой данных
  4. Нажмите «В таблицу» для преобразования корневого объекта
  5. Используйте кнопку расширения (двойная стрелка) для развертывания вложенных объектов

Типичные преобразования JSON в Power Query:

// Развернуть список объектов
= Table.ExpandListColumn(Source, "items")

// Развернуть объект в столбцы
= Table.ExpandRecordColumn(#"Expanded items", "record", {"id","name","value"})

Импорт XML:

  1. «Данные» — «Получить данные» — «Из файла» — «Из XML»
  2. Выберите XML-файл
  3. В навигаторе выберите нужный узел дерева данных
  4. Загрузите как таблицу

Импорт из файлов других форматов:

  • ODS (OpenDocument) — открывается напрямую через «Файл» — «Открыть»
  • Access (.accdb) — «Получить данные» — «Из базы данных» — «Из Microsoft Access»
  • SharePoint список — «Получить данные» — «Из онлайн-служб» — «Из списка SharePoint Online»
  • Excel из OneDrive — «Получить данные» — «Из онлайн-служб» — «Из Excel»

Экспорт этих в разные форматы

Excel поддерживает экспорт в десятки форматов. Выбор формата зависит от цели: передача этих в другую программу, публикация или архивирование.

Экспорт в CSV (для передачи в другие системы):

  1. Файл — Сохранить как — выберите «CSV (разделители — запятые)» или «CSV UTF-8»
  2. При сохранении Excel предупредит что будет сохранен только активный лист
  3. Нажмите «ОК»

Важно: для корректной передачи в системы работающие с кириллицей используйте «CSV UTF-8 (с разделителем — запятой)». Для 1С обычно требуется кодировка Windows-1251 — сохраните как «CSV (разделители — запятые)» (без пометки UTF-8).

Экспорт в PDF:

  1. Файл — Экспорт — Создать PDF/XPS
  2. Нажмите «Параметры» для настройки: печать выделенного диапазона, листа или всей книги
  3. Выберите «Стандарт» (высокое качество) или «Минимальный размер» для публикации в интернете

Настройка области печати перед экспортом в PDF:

  • Выделите нужный диапазон
  • Вкладка «Разметка страницы» — «Область печати» — «Задать»
  • Настройте ориентацию, поля и масштаб
  • Используйте «Предварительный просмотр» для проверки

Экспорт в XML:

  1. Для экспорта в XML нужно сначала привязать XML-схему: Разработчик — Источник — Карты XML
  2. После привязки: Разработчик — Экспорт
  3. Без схемы можно использовать «Файл» — «Сохранить как» — «XML-данные» (упрощенный формат)

Автоматизация импорта и экспорта через VBA и Power Automate

Для регулярно повторяющихся операций импорта и экспорта используйте автоматизацию через VBA-макросы или Power Automate.

VBA-макрос для автоматического импорта CSV:

Sub ImportCSV()
    Dim filePath As String
    filePath = "C:Datareport.csv"

    With ActiveSheet.QueryTables.Add( _
        Connection:="TEXT;" & filePath, _
        Destination:=Range("A1"))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFileOtherDelimiter = ";"
        .TextFileColumnDataTypes = Array(1, 1, 1)
        .Refresh BackgroundQuery:=False
    End With
End Sub

VBA-макрос для экспорта активного листа в CSV:

Sub ExportToCSV()
    Dim savePath As String
    savePath = "C:Dataexport_" & Format(Now(), "yyyymmdd") & ".csv"

    ActiveSheet.Copy
    ActiveWorkbook.SaveAs Filename:=savePath, _
        FileFormat:=xlCSVUTF8, _
        CreateBackup:=False
    ActiveWorkbook.Close False
    MsgBox "Файл сохранен: " & savePath
End Sub

Power Automate для автоматической обработки:

  • Создайте поток в Power Automate для мониторинга папки
  • При появлении нового CSV-файла — автоматически импортировать в Excel на SharePoint
  • После обработки — отправить результат по email или сохранить в базу данных

Ознакомьтесь с официальной документацией по импорту этих через Power Query на сайте Microsoft Support.

Полезные ссылки и материалы

Для решения задач, описанных в этой статье, могут пригодиться:

Официальная документация: Microsoft Learn: устранение неполадок.

Часто задаваемые вопросы

Почему при импорте CSV в Excel русские буквы отображаются как кракозябры?

Проблема в кодировке. Выберите кодировку 65001 UTF-8 или 1251 Кириллица при импорте через Power Query. Если открываете двойным кликом — используйте Эти — Из текста/CSV и укажите кодировку вручную.

Как импортировать эти из нескольких CSV-файлов одновременно?

Используйте Power Query: Эти — Получить эти — Из файла — Из папки. Укажите папку с файлами и выберите Объединить. Excel автоматически склеит все файлы одинаковой структуры в одну таблицу.

Сохраняются ли формулы при экспорте в CSV?

Нет. CSV хранит только значения. При экспорте Excel вычислит все формулы и сохранит результаты как числа и текст. Формулы сохраняются только в форматах XLSX, XLSM и XLS.

Как настроить автоматическое обновление этих из внешнего источника?

В Power Query настройте подключение, затем в разделе Эти — Запросы и подключения откройте свойства запроса. Включите обновление каждые N минут или при открытии файла. Для фоновой работы включите фоновое обновление.

Частые вопросы

Почему при импорте CSV в Excel русские буквы отображаются как кракозябры?

Проблема в кодировке. Выберите кодировку 65001 UTF-8 или 1251 Кириллица при импорте через Power Query. Если открываете двойным кликом - используйте Эти - Из текста/CSV и укажите кодировку вручную.

Как импортировать эти из нескольких CSV-файлов одновременно?

Используйте Power Query: Эти - Получить эти - Из файла - Из папки. Укажите папку с файлами и выберите Объединить. Excel автоматически склеит все файлы одинаковой структуры в одну таблицу.

Сохраняются ли формулы при экспорте в CSV?

Нет. CSV хранит только значения. При экспорте Excel вычислит все формулы и сохранит результаты как числа и текст. Формулы сохраняются только в форматах XLSX, XLSM и XLS.

Как настроить автоматическое обновление этих из внешнего источника?

В Power Query настройте подключение, затем в разделе Эти - Запросы и подключения откройте свойства запроса. Включите обновление каждые N минут или при открытии файла. Для фоновой работы включите фоновое обновление.

Читайте также