Экспорт данных из эксель в эксель

Офисное пространство Офисное пространство

официальный блог о Microsoft Office

Импорт и экспорт данных в Excel 2010

Вы ведь помните о том, что весной у нас проходил конкурс статей? И о том, что я обещала опубликовать то, что сразу не получилось? И сегодня я вытаскиваю из архивов очень полезную статью от Михаила Чернякова.

Статья будет про импорт и экспорт данных в Excel 2010.

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

Если у вас имеются данные в совместимом формате (например, в виде текстового файла, таблиц баз данных, Интернет и других источников), можно импортировать эти данные в Ехсеl 2010 (рис.1).

1. Чтобы импортировать данные из текстового файла, на вкладке Данные в разделе Получить внешние данные щелкните на кнопке Из текста (рис.2).

2. В результате отобра­зиться диалоговое окно Импорт текстового файла (рис.3).

3. С помощью этого диалогового окна (рис.3) можно перейти в папку, со­держащую файл, который нужно импортировать. Двойной щел­чок на файле или по кнопке Импорт запускает Мастер импорта текста (рис.4).

4. Первая страница мастера позволяет указать формат данных: с разделителем или с фиксированной шириной. Фиксированная ширина означает, что поля имеют заданную ширину. Необходимо также указать место начала импорта и Формат файла, например, Кириллица. Если щелк­нуть на кнопке Далее, мастер примет заданный по умолча­нию параметр и перейдет к следующей странице (рис.5).

5. На второй странице мастера (рис.5) можно выбрать разделитель (в данном случае Ехсеl обнаружил в файле символ табуляции и вы­делил соответствующий флажок) и просмотреть образец разбора данных. При предварительном просмотре в качестве символа разделения была использована запятая (рис.4), поэтому необходимо вы­делить флажком символ-разделителя Запятая. Щелкнув на кнопке Далее, можно перейти к пос­ледней странице мастера (рис.6).

6. 3-я страница позволяет указать формат данных для каждого столбца. Поскольку числовые форматы и экспресс-стили можно присвоить после создания сводной таблицы, можно щелкнуть на кнопке Готово, чтобы указать место куда импортировать данные в рабочий лист и настройке свойств внешнего диапазона (рис.7).

7. Нажав кнопку ОК завершается процесс импорта данных и они появляются на рабочем столе (рис.8).

Аналогично можно импортировать эти данные в Ехсеl 2010 и из других источников (рис.9).

Экспорт данных в файл

Чтобы осуществить Экспорт данных в файл необходимо перейти на вкладку Файл и воспользоваться командой Сохранить и отправить.

Команда Изменить тип файла позволяет изменять тип файла на другие Типы файлов книг и Другие типы файлов, включая текстовые, а также воспользоваться командой Сохранить как.

1. Выполните команду: Файл ðСохранить и отправить ð Изменить тип файла ð Другие типы файлов ð Текстовой формат с разделителями табуляции (рис.11).

2. В диалоговом окне Сохранение документа нажмите кнопку Сохранить (рис.11). Нажмите ОК, чтобы подтвердить, что желаете сохранить текущий лист (рис.12).

3. В результате будет создан текстовой файл, в котором данные книги будет разделены знаками табуляции (рис.13).

Аналогично можно импортировать эти данные из Ехсеl 2010 и в другие источники, воспользоваться командой Сохранить как, например, как MHTML-документ (рис.14).

Выводы и рекомендации

1. Импортировать данные в Ехсеl 2010 можно из совместимых форматов в виде текстового файла, таблиц баз данных, Интернет и других источников.

2. Экспорт данных в файл необходимо осуществлять с вкладки Файл, воспользоваться командами Сохранить и отправить или Сохранить как.

Учебник: импорт данных в Excel и создание модели данных

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Аннотация. Это первый учебник из серии, который поможет ознакомиться с программой Excel и ее возможностями объединения и анализа данных, а также научиться легко использовать их. С помощью этой серии учебников вы научитесь создавать с нуля и совершенствовать рабочие книги Excel, строить модели данных и создавать удивительные интерактивные отчеты с использованием надстройки Power View. В этих учебниках приводится описание возможностей средств бизнес-аналитики Майкрософт в Excel, сводных таблиц, Power Pivot и Power View.

Примечание: В этой статье описаны модели данных в Excel 2013. Однако те же функции моделирования данных и Power PIVOT, представленные в Excel 2013, также применимы к Excel 2016.

Вы узнаете, как импортировать и просматривать данные в Excel, строить и совершенствовать модели данных с использованием Power Pivot, а также создавать с помощью надстройки Power View интерактивные отчеты с возможностью публикации, защиты и предоставления общего доступа.

Учебники этой серии

Импорт данных в Excel 2013 и создание модели данных

В этом учебнике вы начнете работу с пустой книги Excel.

Разделы учебника

В конце учебника есть тест, с помощью которого можно проверить свои знания.

В этой серии учебников используются данные, описывающие спортивных medals, страны размещения и различные спортивных спортивные мероприятия. Мы рекомендуем вам пройти каждый из этих учебников по порядку. Учебники также используют Excel 2013 с Power Pivot. Для получения дополнительных сведений о Excel 2013 щелкните здесь. Для получения инструкций по включению Power Pivot щелкните здесь.

Импорт данных из базы данных

Начнем работу с учебником с пустой книги. В этом разделе вы узнаете, как подключиться к внешнему источнику данных и импортировать их в Excel для дальнейшего анализа.

Сначала загрузим данные из Интернета. Эти данные об олимпийских медалях являются базой данных Microsoft Access.

Щелкните следующие ссылки, чтобы скачать файлы, используемые во время этого ряда учебников. Скачайте каждый из четырех файлов в нужное место, например загружаемЫе файлы или Мои документы, или новую созданную папку.
_Гт_ олимпикмедалс. accdb Access

Откройте пустую книгу в Excel 2013.

Щелкните данные _Гт_ получить внешние данные _Гт_ из Access. Лента настраивается динамически в зависимости от ширины книги, поэтому команды на ленте могут немного отличаться от следующих экранов. На первом экране отображается лента, если книга является широкой, а на втором — книга, размер которой был изменен, чтобы занимать только часть экрана.

Выберите скачанный файл Олимпикмедалс. accdb и нажмите кнопку Открыть. Откроется следующее окно Выбор таблицы, в котором отображаются таблицы, найденные в базе данных. Таблицы в базе данных похожи на листы и таблицы в Excel. Установите флажок Разрешить выбор нескольких таблиц и выберите все таблицы. Нажмите кнопку ОК.

Появится окно «Импорт данных».

Примечание: Обратите внимание на флажок в нижней части окна, с помощью которого вы можете добавить эти данные в модель данных, как показано на следующем экране. Модель данных создается автоматически при одновременном импорте двух или нескольких таблиц. Модель данных интегрирует таблицы, обеспечивая расширенный анализ с помощью сводных таблиц, Power Pivot и Power View. При импорте таблиц из базы данных существующие связи между этими таблицами используются для создания модели данных в Excel. Модель данных является прозрачной в Excel, но ее можно просматривать и изменять непосредственно с помощью надстройки Power Pivot. Модель данных обсуждается более подробно далее в этом учебнике.

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

После импорта данных создается сводная таблица с использованием импортированных таблиц.

Теперь, когда данные импортированы в Excel и автоматически создана модель данных, можно приступить к их просмотру.

Просмотр данных в сводной таблице

Просматривать импортированные данные удобнее всего с помощью сводной таблицы. В сводной таблице можно перетаскивать поля (похожие на столбцы в Excel) из таблиц (например, таблиц, импортированных из базы данных Access) в разные области, настраивая представление данных. Сводная таблица содержит четыре области: ФИЛЬТРЫ, СТОЛБЦЫ, СТРОКИ и ЗНАЧЕНИЯ.

Возможно, придется поэкспериментировать, чтобы определить, в какие области следует перетащить поле. Можно перетаскивать из таблиц любое количество полей, пока представление данных в сводной таблице не примет нужный вид. Не бойтесь перетаскивать поля в любые области сводной таблицы — это не повлияет на базовые данные.

Рассмотрим в сводной таблице данные об олимпийских медалях, начиная с призеров Олимпийских игр, упорядоченных по дисциплинам, типам медалей и странам или регионам.

В полях сводной таблицы разверните таблицу medals , щелкнув стрелку рядом с ней. Найдите поле Нок_каунтрирегион в развернутой таблице medals и перетащите его в область столбцы. В NOC используется National спортивных комитетов, который является организационным подразделением для страны или региона.

Затем перетащите виды спорта из таблицы Disciplines в область СТРОКИ.

Давайте отфильтруем дисциплины, чтобы отображались только пять видов спорта: стрельба из лука (Archery), прыжки в воду (Diving), фехтование (Fencing), фигурное катание (Figure Skating) и конькобежный спорт (Speed Skating). Это можно сделать в области Поля сводной таблицы или в фильтре Метки строк в самой сводной таблице.

Щелкните в любом месте сводной таблицы, чтобы убедиться, что сводная таблица Excel выбрана. В списке Поля сводной таблицы, где развернута таблица Disciplines, наведите указатель на поле Discipline, и в его правой части появится стрелка раскрывающегося списка. Щелкните эту стрелку, нажмите кнопку (Выбрать все), чтобы снять отметку со всех выбранных параметров, а затем прокрутите вниз и выберите пункты Archery, Diving, Fencing, Figure Skating и Speed Skating. Нажмите кнопку ОК.

Либо щелкните в разделе сводной таблицы Метки строк стрелку раскрывающегося списка рядом с полем Метки строк, нажмите кнопку (Выбрать все), чтобы снять отметку со всех выбранных параметров, а затем прокрутите вниз и выберите пункты Archery, Diving, Fencing, Figure Skating и Speed Skating. Нажмите кнопку ОК.

В разделе Поля сводной таблицы перетащите поле Medal из таблицы Medals в область ЗНАЧЕНИЯ. Поскольку значения должны быть числовыми, Excel автоматически изменит поле Medal на Count of Medal.

В таблице Medals снова выберите поле Medal и перетащите его в область ФИЛЬТРЫ.

Давайте отфильтруем сводную таблицу таким образом, чтобы отображались только страны или регионы, завоевавшие более 90 медалей. Вот как это сделать.

В сводной таблице щелкните стрелку раскрывающегося списка рядом с полем Метки столбцов.

Выберите Фильтры по значению, а затем — Больше.

Введите 90 в последнем поле (справа). Нажмите кнопку ОК.

Сводная таблица будет иметь следующий вид:

Не затрачивая особых усилий, вы создали сводную таблицу, которая содержит поля из трех разных таблиц. Эта задача оказалась настолько простой благодаря заранее созданным связям между таблицами. Поскольку связи между таблицами существовали в исходной базе данных и вы импортировали все таблицы сразу, приложение Excel смогло воссоздать эти связи в модели данных.

Но что делать, если данные происходят из разных источников или импортируются не одновременно? Обычно можно создать связи с новыми данными на основе совпадающих столбцов. На следующем этапе вы импортируете дополнительные таблицы и узнаете об этапах создания новых связей.

Импорт данных из таблицы

Теперь давайте импортируем данные из другого источника, из существующей книги. Затем укажем связи между существующими и новыми данными. Связи позволяют анализировать наборы данных в Excel и создавать интересные и эффектные визуализации импортированных данных.

Начнем с создания пустого листа, а затем импортируем данные из книги Excel.

Вставьте новый лист Excel и назовите его Sports.

Перейдите к папке, в которой содержатся загруженные файлы образцов данных, и откройте файл OlympicSports.xlsx.

Выберите и скопируйте данные на листе Sheet1. При выборе ячейки с данными, например, ячейки А1, можно нажать клавиши Ctrl + A, чтобы выбрать все смежные данные. Закройте книгу OlympicSports.xlsx.

На листе Sports поместите курсор в ячейку А1 и вставьте данные.

Выделив данные, нажмите клавиши CTRL + T, чтобы отформатировать данные как таблицу. Вы также можете отформатировать данные в виде таблицы на ленте, выбрав в _Гт_ формат таблицы. Так как у данных есть заголовки, в появившемся окне Создание таблицы выберите пункт таблица с заголовками, как показано ниже.

Форматирование данных в виде таблицы имеет много преимуществ. Можно назначить имя таблице, что упрощает ее определение. Кроме того, вы можете устанавливать связи между таблицами, позволяя исследовать и анализировать в сводных таблицах, Power Pivot и в Power View.

ПриСвойте таблице имя. В разделе Работа с таблицаМи _Гт_ свойства _ГТ_ макета найдите поле имя таблицы и введите Спорт. Книга будет выглядеть так, как показано на следующем экране.

Импорт данных с помощью копирования и вставки

Теперь, когда данные из книги Excel импортированы, давайте сделаем то же самое с данными из таблицы на веб-странице или из любого другого источника, дающего возможность копирования и вставки в Excel. На следующих этапах мы добавим из таблицы города, принимающие Олимпийские игры.

Вставьте новый лист Excel и назовите его Hosts.

Выделите и скопируйте приведенную ниже таблицу вместе с заголовками.

Экспорт данных из Excel 2007

Как файл Excel преобразовать csv-файл?

Для экспорта таблицы Excel в csv-файл нажмите кнопку Microsoft Office (в левом верхнем углу) и выберите команду Сохранить как. В поле Тип файла укажите нужный вам тип файла csv.

Excel проведит конвертацию (экспорт) только текущего листа.

Как экспортировать таблицу из Excel в Access с сохранением связей между ними?

Экспорт из Excel’a в Access осуществляется путем импорта в Access. Для этого необходимо открыть базу данных Access и на вкладке Внешние данные группы Ипорт нажать на кнопку Excel.

В диалоговом окне Внешние данные — Электронная таблица Excel нажмите на кнопку Обзор… и укажите путь к нужному вам файлу.

Отметьте пункт Создать связанную таблицу для связи с источником данных. Благодаря этому пункту обе таблицы будут связаны, так что изменения в исходной таблице будут автоматически отображаться в Access. Внесение изменений в таблицу Access не будут влиять на данные исходной таблице.

Нажмите ОК. И у вас откроется второе диалоговое окно — Связь с электронной таблицей.

Здесь вы можете выбрать лист, который содержит необходимую таблицу или имя диапазона, если всей таблице оно присвоено. Нажимаем кнопку Далее.

Здесь необходимо указать, содержит ли первая строчка названия столбцов.

На последнем шаге вам предложат указать Имя связанной таблицы. Нажимаем Готово.

После этого Access предупредит о завершении связывания таблиц — экспорт из Excel’a/импорт в Access завершен.

В начало страницы

В начало страницы

Экспорт данных в Excel

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

В этой статье

Экспорт данных в Excel: основы

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

Стандартные сценарии экспорта данных в Excel

В отделе или рабочей группе для работы с данными используется как Access, так и Excel. Данные хранятся в базах данных Access, но для анализа и распространения его результатов используется Excel. Группа выполняет экспорт данных в Excel по мере необходимости, но этот процесс требуется сделать более эффективным.

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

Об экспорте данных в Excel

В приложении Access нет команды «Сохранить как” с возможностью сохранения данных в формате Excel. Чтобы скопировать данные в Excel, необходимо воспользоваться функций экспорта, описанной в этой статье, либо скопировать данные Access в буфер обмена, а затем вставить их в таблицу Excel.

Можно экспортировать таблицу, запрос, форму или отчет, а также отдельные записи в представлении.

В приложении Microsoft Excel есть команда импорта данных из базы данных Access. Ее можно использовать вместо команды экспорта Access, однако с помощью команды импорта в Excel можно импортировать только таблицы и запросы. Дополнительные сведения см. в разделе справки Excel Подключение к внешним данных и их импорт.

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

В ходе операции экспорта можно экспортировать лишь один объект базы данных. Однако после нескольких операций экспорта можно выполнить в Excel слияние нескольких листов.

Подготовка к экспорту

Прежде чем выполнять операцию экспорта, следует проверить данные, которые будут экспортироваться, на предмет сообщений об ошибках и ошибочных значений. Ошибки рекомендуется устранить до экспорта данных в Excel. В противном случае в ходе экспорта могут возникнуть неполадки, а в ячейки листа Excel могут быть вставлены пустые значения. Дополнительные сведения о проблемах, которые могут возникнуть при экспорте данных в Excel, см. в разделе Решение проблем, связанных с отсутствующими и неверными значениями.

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

Импорт данных в Excel из Интернета

В Excel есть множество встроенных инструментов для импорта данных из самых разных внешних источников. Есть среди них, разумеется, и инструмент для выгрузки информации из Интернета.

Все инструменты для импорта данных расположены на ленте на вкладке «Данные».

В новых версиях Excel по умолчанию встроена надстройка Power Query, которая и занимается импортом всех данных из внешних источников (пример показывали тут ). В более ранних версиях использовался специальный мастер импорта (пример был тут ). Если Вы привыкли именно к нему, то включить его в новой версии Excel можно пройдя по пути «Файл»-«Параметры»-«Данные» и поставив галочку на пункте «Из интернета (прежних версий)»

Как Вы уже догадались, для импорта данных из мировой паутины нужно найти команду «Из интернета», после чего перед вами откроется окно старого мастера импорта или новое окно Power Query. Разберем оба способа.

Старый мастер импорта данных из интернета

Если у Вас Excel 2013 или более старый, то этот мастер откроется по умолчанию при выборе команды «Из интернета» на вкладке «Данные». Если у Вас Excel 2016, то после того, как Вы включили старый мастер в настройках, как показано выше, он будет доступен по следующему пути: «Данные»-«Получить данные»-«Устаревшие мастеры»-«Из интернета (прежних версий)».

После запуска откроется окно «Создание веб-запроса». Оно похоже на стандартное окно браузера. В адресную строку вверху нужно ввести URL страницы, с которой необходимо выгрузить данные. После того, как страница будет загружена, все данные, которые Excel может импортировать, будут помечены небольшой желтой стрелкой. Кликните по ней и нажмите кнопку «Импорт» в правом нижнем углу. Если на странице нет желтых стрелок или они расположены не там, где нужно, то данным способом выгрузить данные в Excel не получится.

До нажатия кнопки импорт, можете задать некоторые настройки, кликнув на кнопке «Параметры» в правом верхнем углу. Наиболее полезной здесь является настройка «Отключить распознавание дат». Она позволяет избежать частой ошибки, вызванной особенностями региональных настроек системы, когда число в формате «2.15» выгружается в Excel как «фев.15».

После нажатия кнопки «Импорт» программа спросит у Вас, куда именно нужно выгрузить данные. После указания листа и диапазона, таблица из интернета будет выгружена в файл Excel. При этом, будет создан именованный диапазон, содержащий выгруженные ячейки.

После выгрузки с данными можно работать так же, как и с обычной информацией на листе (применять формулы, изменять, удалять, строить графики, сводные таблицы и т.д.).

Если поставить курсор в любую ячейку выгруженной таблицы и кликнуть правой кнопкой мыши, то в контекстном меню будет доступна команда «Обновить», по нажатию которой произойдет обновление источника данных (повторная выгрузка информации из интернета на лист). Там же (в контекстном меню) есть команда «Свойства диапазона данных. «. Вызвав ее можно задать, например, настройки обновления или изменить имя диапазона.

На данный момент, этот способ импорта данных несколько устарел. Всё тяжелее найти сайты и страницы с «желтыми стрелочками». Поэтому рекомендуем обновить Excel и прибегнуть к использованию второго способа импорта данных.

2. Импорт данных с помощью Power Query

После нажатия кнопки «Из интернета» в Excel 2016 Вы увидите не окно «Создание веб-запроса», а маленькое окошко, в котором нужно будет указать адрес страницы.

После нажатия кнопки «ОК» нужно подождать, пока Power Query подключится к источнику. Когда это произойдет, Вы увидите окно «Навигатор». В левой части будет представлен список всех таблиц, которые программа смогла распознать на странице сайта. После клика на любой из них в правой части окна появится предпросмотр данных (будут отображены те, которые Power Query выгрузит на лист при выборе данной таблицы). Если в правой части переключить вкладку вверху с «Представление таблицы» на «Веб-представление», то можно увидеть, как выглядит выбранная Вами таблица на странице сайта (и понять, это ли вы хотите выгрузить).

Если результат предпросмотра Вас устраивает, жмите кнопку «Загрузить». Если нет, то можно нажать кнопку «Правка». Тогда Вы попадете в окно редактора Power Query. Здесь можно настроить обработку данных после получения их из Интернета и перед выгрузкой на лист. Например, удалить лишние столбцы, изменить заголовки или поменять регистр текста.

После того, как данные примут нужный вид, нажмите кнопку «Закрыть и загрузить» на вкладке «Главная» в редакторе запросов.

Данные будут выгружены на новый лист. Обратите внимание, что импортированная таблица будет автоматически преобразована в «умную таблицу», а в списке запросов («Данные» — «Запросы и подключения») появится новый запрос.

Обновить его можно кликнув правой кнопкой мыши и выбрав команду «Обновить», либо нажав на иконку в правом верхнем углу рядом с названием запроса. Там же (в контекстном меню по правой кнопке мыши) есть команда «Свойства», с помощью которой можно, например, настроить автообновление запроса.

В отличие от старого мастера импорта, Power Query имеет ряд преимуществ:

1) Может работать с гораздо большим числом сайтов и страниц;

2) Может осуществлять промежуточную обработку данных перед тем, как они будут выгружены на лист;

3) Выгружает данные из Интернета гораздо быстрее;

4) Автоматически создает «Умную таблицу».

Учитывая всё вышесказанное, настоятельно рекомендуем Вам пользоваться новыми версиями Excel в целом и Power Query в частности (в Excel 2016 он встроен по умолчанию, в Excel 2010 и 2013 может быть установлен как бесплатная надстройка).

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot