Как в эксель данные
В 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
Модель данных позволяет интегрировать данные из нескольких таблиц, эффективно построение источника реляционных данных Excel книге. В Excel данных модели данных применяются прозрачно, что обеспечивает табличные данные, используемые в сводных таблицах и на сводных таблицах. Модель данных визуализируются как набор таблиц в списке полей, и в большинстве моментов вы даже не узнаете, что она там.
Прежде чем приступить к работе с моделью данных, необходимо получить некоторые данные. Для этого мы используем power query & Transform ( Get & Transform), чтобы вы могли захотеть вернуться на шаг назад и посмотреть видео или воспользоваться учебным руководством по & Преобразование и Power Pivot.
Excel 2016 & Excel для Microsoft 365 — На ленте есть Power Pivot.
Excel 2013 — Power Pivot входит в выпуск Office профессиональный плюс версии Excel 2013, но по умолчанию не включен. Подробнее о запуске надстройки Power Pivot для Excel 2013.
Excel 2010 — скачайте надстройку Power Pivot, а затем установите надстройку Power Pivot,
Excel 2016 & Excel для Microsoft 365 — & Power Query интегрирован с Excel на вкладке Данные.
Excel 2013 : Power Query — это надстройка, которая входит в состав Excel, но ее необходимо активировать. Перейдите в > файлов > надстройки ,а затем в меню Управление в нижней части области выберите Надстройки COM > Перейти. Проверьте, нет ли в Microsoft Power Query Excel, а затем ОК, чтобы активировать его. На ленту будет добавлена вкладка Power Query.
Excel 2010— скачивание и установка надстройки Power Query.. После активации на ленту будет добавлена вкладка Power Query.
Начало работы
Во-первых, вам нужно получить некоторые данные.
В Excel 2016 и Excel для Microsoft 365 используйте data > Get & Transform Data > Get Data (Получить данные), чтобы импортировать данные из любого числа внешних источников данных, таких как текстовый файл, книга Excel, веб-сайт, Microsoft Access, SQL Server или другая реляционная база данных, которая содержит несколько связанных таблиц.
В Excel 2013 и 2010 перейдите в Power Query > Получитьвнешние данные и выберите источник данных.
Excel предложит выбрать таблицу. Если вы хотите получить несколько таблиц из одного источника данных, проверьте параметр Включить выбор нескольких таблиц. При выборе нескольких таблиц Excel автоматически создает модель данных.
Примечание: В этих примерах мы используем книгу Excel с вымышленными сведениями об учащихся в классах и оценках. Вы можете скачать образец книги модели данныхдля учащихся и следуйте этим командам. Вы также можете скачать версию с завершенной моделью данных..
Выберите одну или несколько таблиц и нажмите кнопку Загрузить.
Если вам нужно изменить исходные данные, можно выбрать параметр Изменить. Дополнительные сведения см. в обзоре редактора запросов (Power Query).
Теперь у вас есть модель данных, которая содержит все импортируемые таблицы, и они будут отображаться в списке полей таблицы.
Модели создаются неявно, когда вы импортируете в Excel несколько таблиц одновременно.
Модели создаются явно, если вы импортируете данные с помощью надстройки Power Pivot. В надстройке модель представлена в макете со вкладками так же, как Excel, где каждая вкладка содержит табличные данные. Сведения о том,как импортировать данные с помощью базы данных, см. в этой SQL Server Power Pivot.
Модель может содержать одну таблицу. Чтобы создать модель на основе только одной таблицы, выберите таблицу и нажмите кнопку Добавить в модель данных в Power Pivot. Это может понадобиться в том случае, если вы хотите использовать функции Power Pivot, например отфильтрованные наборы данных, вычисляемые столбцы, вычисляемые поля, ключевые показатели эффективности и иерархии.
Связи между таблицами могут создаваться автоматически при импорте связанных таблиц, у которых есть связи по первичному и внешнему ключу. Excel обычно может использовать импортированные данные о связях в качестве основы для связей между таблицами в модели данных.
Советы по уменьшению размера модели данных см. в Excel и Power Pivot.
Дополнительные сведения см. в учебнике Импорт данных в Excel и Создание модели данных.
Совет: Как узнать, есть ли в книге модель данных? Перейдите в Power Pivot > Управление. Если вы видите данные, похожие на таблицу, то модель существует. Дополнительные сведения см. в этой теме.
Создание связей между таблицами
Далее нужно создать связи между таблицами, чтобы можно было извлекть данные из любой из них. Каждая таблица должна иметь первичный ключ или уникальный идентификатор поля, например Код учащегося или номер класса. Проще всего перетащить эти поля, чтобы соединить их в представлении диаграммы Power Pivot.
Перейдите на > PowerPivot .
На вкладке Главная выберите представление диаграммы.
Будут показаны все импортируемые таблицы, и вам может потребоваться некоторое время, чтобы их можно было отобразить в зависимости от их количество.
Затем перетащите поле первичного ключа из одной таблицы в следующую. Ниже приведен пример представления диаграммы таблиц наших учащихся:
Мы создали следующие ссылки:
tbl_Students | ИД учащегося > tbl_Grades | ИД учащегося
Другими словами, перетащите поле "ИД учащегося" из таблицы "Учащиеся" в поле "ИД учащегося" в таблице "Оценки".
tbl_Semesters | ИД семестра > tbl_Grades | Семестр
tbl_Classes | Номер > tbl_Grades | Номер класса
Имена полей не должны быть одинаковыми для создания связи, но они должны быть одного типа данных.
Соединители в представлении диаграммы имеют 1 с одной стороны, а "*" — на другой. Это означает, что между таблицами существует связь "один-к-многим", которая определяет способ использования данных в таблицах. Дополнительные сведения см. в этой теме: Связи между таблицами в модели данных.
Соединитетели указывают только на связь между таблицами. Они не будут показывать, какие поля связаны друг с другом. Чтобы увидеть ссылки, перейдите в Power Pivot > Управление > конструктором > связи > Управление связями. В Excel вы можете перейти к data > Relationships (Отношения > данных).
Создание и создание с помощью модели данных для создания сводная диаграмма
Книга Excel может содержать только одну модель данных, но она содержит несколько таблиц, которые можно многократно использовать во всей книге. Вы можете добавить дополнительные таблицы в существующую модель данных в любое время.
В Power Pivot, перейдите вуправление .
На вкладке Главная выберите вкладку "PivotTable".
Выберите место для размещения таблицы: новый или текущий.
Затем создайте или создайтесетовую диаграмму. Если вы уже создали связи между таблицами, вы можете использовать любое из их полей в таблице. Мы уже создали связи в образце книги модели данных для учащихся.
Добавление имеющихся несвязанных данных в модель данных
Сначала выберите любую ячейку в данных, которые вы хотите добавить в модель. Это может быть любой диапазон данных, но лучше всего отформатированные как Excel таблицы.
Добавьте данные одним из следующих способов.
Щелкните Power Pivot > Добавить в модель данных.
Выберите Вставка > Сводная таблица и установите флажок Добавить эти данные в модель данных в диалоговом окне "Создание сводной таблицы".
Диапазон или таблица будут добавлены в модель как связанная таблица. Дополнительные сведения о работе со связанными таблицами в модели см. в статье Добавление данных с помощью связанных таблиц Excel в Power Pivot.
Добавление данных в Power Pivot таблицу
В Power Pivot невозможно добавить строку в таблицу, введя текст непосредственно в новой строке, как это можно сделать на листе Excel. Но вы можете добавить строки, скопируяи впав их или обновив исходные данные и обновив модель Power Pivot.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
С помощью OLE можно включать контент из других программ, таких как Word или Excel.
OLE поддерживается многими различными программами, а OLE используется для того, чтобы содержимое, созданное в одной программе, было доступно в другой программе. Например, можно вставить документ Office Word в книгу Office Excel. Чтобы узнать, какие типы содержимого можно вставить, на вкладке Вставка в группе Текст нажмитекнопку Объект. В поле Тип объекта отображаются только программы, которые установлены на компьютере и поддерживают объекты OLE.
При копировании данных между Excel или любой программой, которая поддерживает OLE, например Word, вы можете скопировать их как связанный объект или внедренный объект. Основные различия между связанными и внедренными объектами заключается в том, где хранятся данные и как обновляется объект после его конечный файл. Внедренные объекты хранятся в книге, в которую они вставляются, и не обновляются. Связанные объекты остаются отдельными файлами, и их можно обновлять.
Связанные и внедренные объекты в документе
1. Внедренный объект не имеет подключения к исходным файлам.
2. Связанный объект связан с исходным файлом.
3. Исходный файл обновляет связанный объект.
Когда использовать связанные объекты
Если вы хотите, чтобы данные в конечный файл обновлялись при исходный файл изменения, используйте связанные объекты.
При связываемом объекте исходные данные сохраняются в исходном файле. В конечном файле отображается представление связанных данных, но хранится только расположение исходных данных (и размер, если объект является объектом диаграммы Excel). Исходный файл должен оставаться доступным на компьютере или в сети, чтобы сохранить связь с исходными данными.
Связанные данные могут обновляться автоматически при изменении исходных данных в исходном файле. Например, если вы вы выбрали абзац в документе Word, а затем вклеили его как связанный объект в книгу Excel, то при изменении сведений в документе Word сведения могут обновляться в Excel.
Когда использовать внедренные объекты
Если вы не хотите обновлять скопированные данные при их внесении в исходный файл, используйте внедренный объект. Версия источника полностью внедрена в книгу. При копировании данных как внедренных объектов в файле назначения требуется больше места на диске, чем при связываи.
Когда пользователь открывает файл на другом компьютере, он может просмотреть внедренный объект, не имея доступа к исходным данным. Поскольку внедренный объект не имеет ссылок на исходный файл, он не обновляется при изменении исходных данных. Чтобы изменить внедренный объект, дважды щелкните его, чтобы открыть и изменить в программе-источнике. На компьютере должна быть установлена программа-источник (или другая программа, которая может редактировать объект).
Изменение способа отображения объекта OLE
Вы можете отобразить связанный объект или внедренный объект в книге точно так же, как в программа-источник или в качестве значка. Если книга будет просмотрена в Интернете и вы не собираетесь ее печатать, вы можете отобразить объект в виде значка. Таким образом уменьшается объем отображаемого пространства, занимаемого объектом. Пользователи, которые хотят отобразить сведения, могут дважды щелкнуть значок.
Аннотация. Это первый учебник из серии, который поможет ознакомиться с программой 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.
Разделы учебника
В конце учебника есть тест, с помощью которого можно проверить свои знания.
В этом учебном ряду используются данные об олимпийских медалях, странах, принимающих олимпийских играх, а также различных олимпийских соревнованиях. Мы рекомендуем вам перейти к каждому учебнику по порядку. Кроме того, в учебниках Excel 2013 с Power Pivot включена. Дополнительные сведения о Excel 2013 можно найти здесь. Чтобы получить инструкции по Power Pivot, щелкните здесь.
Импорт данных из базы данных
Начнем работу с учебником с пустой книги. В этом разделе вы узнаете, как подключиться к внешнему источнику данных и импортировать их в Excel для дальнейшего анализа.
Сначала загрузим данные из Интернета. Эти данные об олимпийских медалях являются базой данных Microsoft Access.
Чтобы скачать файлы, которые мы используем в этом ряду учебников, перейдите по следующим ссылкам: Скачайте каждый из четырех файлов в папку, которую легко найти, например "Загрузки" или "Мои документы", или в новую папку:
> OlympicMedals.accdb Access
>OlympicSports.xlsx Excel книги
> Population.xlsx Excel книги
>DiscImage_table.xlsx Excel книги
Откройте пустую книгу в Excel 2013.
Выберите пункт ДАННЫЕ > Получение внешних данных > Из Access. Лента динамически изменяется по ширине книги, поэтому команды на ленте могут выглядеть не так, как в представленных ниже окнах. В первом окне показана лента при развернутой книге, а во втором ширина книги изменена таким образом, что она занимает лишь часть окна.
Выберите файл ОлимпийскиеМедали.accdb и нажмите кнопку Открыть. Появится окно "Выбор таблицы", в котором отобразятся таблицы, найденные в базе данных. Таблицы в базе данных похожи на листы или таблицы в Excel. Установите флажок Разрешить выбор нескольких таблиц. Затем нажмите кнопку ОК.
Появится окно "Импорт данных".
Примечание: Обратите внимание на то, что в нижней части окна вы можете добавить эти данные в модель данных ,показанную на следующем экране. Модель данных создается автоматически при одновременном импорте или работе с двумя или более таблицами. Модель данных интегрирует таблицы, позволяя провести большой анализ с помощью с помощью Power Pivot, таблиц и Power View. При импорте таблиц из базы данных существующие связи между ними используются для создания модели данных в Excel. Модель данных в Excel, но ее можно просматривать и изменять непосредственно с помощью Power Pivot надстройки. Модель данных подробно рассмотрена далее в этом учебнике.
Выберите параметр Отчет таблицы, который импортирует таблицы в Excel и подготавливает таблицу для анализа импортируемых таблиц, и нажмите кнопку ОК.
После завершения импорта данных будет создана сводная таблица на основе импортированных таблиц.
Теперь, когда данные импортированы в Excel и автоматически создана модель данных, можно приступить к их просмотру.
Просмотр данных в сводной таблице
Просматривать импортированные данные удобнее всего с помощью сводной таблицы. В сводной таблице можно перетаскивать поля (похожие на столбцы в Excel) из таблиц (например, таблиц, импортированных из базы данных Access) в разные области, настраивая представление данных. Сводная таблица содержит четыре области: ФИЛЬТРЫ, СТОЛБЦЫ, СТРОКИ и ЗНАЧЕНИЯ.
Возможно, придется поэкспериментировать, чтобы определить, в какие области следует перетащить поле. Можно перетаскивать из таблиц любое количество полей, пока представление данных в сводной таблице не примет нужный вид. Не бойтесь перетаскивать поля в любые области сводной таблицы — это не повлияет на базовые данные.
Рассмотрим в сводной таблице данные об олимпийских медалях, начиная с призеров Олимпийских игр, упорядоченных по дисциплинам, типам медалей и странам или регионам.
В разделе Поля сводной таблицы разверните таблицу Medals, щелкнув расположенную рядом с ней стрелку. В развернутой таблице Medals найдите поле NOC_CountryRegion и перетащите его в область СТОЛБЦЫ. Аббревиатура NOC обозначает Национальный олимпийский комитет — организационную единицу уровня страны или региона.
Затем перетащите виды спорта из таблицы Disciplines в область СТРОКИ.
Давайте отфильтруем дисциплины, чтобы отображались только пять видов спорта: стрельба из лука (Archery), прыжки в воду (Diving), фехтование (Fencing), фигурное катание (Figure Skating) и конькобежный спорт (Speed Skating). Это можно сделать в области Поля сводной таблицы или в фильтре Метки строк в самой сводной таблице.
В разделе Поля сводной таблицы перетащите поле Medal из таблицы Medals в область ЗНАЧЕНИЯ. Поскольку значения должны быть числовыми, Excel автоматически изменит поле Medal на Count of Medal.
В таблице Medals снова выберите поле Medal и перетащите его в область ФИЛЬТРЫ.
Давайте отфильтруем сводную таблицу таким образом, чтобы отображались только страны или регионы, завоевавшие более 90 медалей. Вот как это сделать.
В сводной таблице щелкните стрелку раскрывающегося списка рядом с полем Метки столбцов.
Выберите Фильтры по значению, а затем — Больше.
Сводная таблица будет иметь следующий вид:
Не затрачивая особых усилий, вы создали сводную таблицу, которая содержит поля из трех разных таблиц. Эта задача оказалась настолько простой благодаря заранее созданным связям между таблицами. Поскольку связи между таблицами существовали в исходной базе данных и вы импортировали все таблицы сразу, приложение Excel смогло воссоздать эти связи в модели данных.
Но что делать, если данные происходят из разных источников или импортируются не одновременно? Обычно можно создать связи с новыми данными на основе совпадающих столбцов. На следующем этапе вы импортируете дополнительные таблицы и узнаете об этапах создания новых связей.
Импорт данных из таблицы
Теперь давайте импортируем данные из другого источника, на этот раз из существующей книги, а затем укажите связи между существующими и новыми данными. Связи по возможности анализировать наборы данных в Excel и создавать интересные и иммерсивные визуализации на личных данных, которые вы импортируете.
Начнем с создания пустого листа, а затем импортируем данные из книги Excel.
Вставьте новый лист Excel и назовите его Sports.
Перейдите к папке, в которой содержатся загруженные файлы образцов данных, и откройте файл OlympicSports.xlsx.
Выберите и скопируйте данные на листе Sheet1. При выборе ячейки с данными, например, ячейки А1, можно нажать клавиши Ctrl + A, чтобы выбрать все смежные данные. Закройте книгу OlympicSports.xlsx.
На листе Sports поместите курсор в ячейку А1 и вставьте данные.
Нажмите клавиши Ctrl + T, чтобы отформатировать выделенные данные в виде таблицы. Кроме того, можно отформатировать данные в виде таблицы через ленту, выбрав команду ГЛАВНАЯ > Форматировать как таблицу. Поскольку у данных есть заголовки, установите флажок Таблица с заголовками в окне Создание таблицы, как показано ниже.
Форматирование данных в виде таблицы имеет множество преимуществ. Таблице можно присвоить имя, чтобы ее было легче идентифицировать. Также можно установить связи между таблицами, позволяющие просматривать и анализировать данные в сводных таблицах, Power Pivot и Power View.
Присвойте таблице имя. В средстве > КОНСТРУКТОР > свойства, найдите поле Имя таблицы и введите Sports. Книга будет выглядеть так же, как на следующем экране.
Импорт данных с помощью копирования и вставки
Теперь, когда данные из книги Excel импортированы, давайте сделаем то же самое с данными из таблицы на веб-странице или из любого другого источника, дающего возможность копирования и вставки в Excel. На следующих этапах мы добавим из таблицы города, принимающие Олимпийские игры.
Анализ данных в Excel предполагает сама конструкция табличного процессора. Очень многие средства программы подходят для реализации этой задачи.
Excel позиционирует себя как лучший универсальный программный продукт в мире по обработке аналитической информации. От маленького предприятия до крупных корпораций, руководители тратят значительную часть своего рабочего времени для анализа жизнедеятельности их бизнеса. Рассмотрим основные аналитические инструменты в Excel и примеры применения их в практике.
Инструменты анализа Excel
Одним из самых привлекательных анализов данных является «Что-если». Он находится: «Данные»-«Работа с данными»-«Что-если».
Средства анализа «Что-если»:
- «Подбор параметра». Применяется, когда пользователю известен результат формулы, но неизвестны входные данные для этого результата.
- «Таблица данных». Используется в ситуациях, когда нужно показать в виде таблицы влияние переменных значений на формулы.
- «Диспетчер сценариев». Применяется для формирования, изменения и сохранения разных наборов входных данных и итогов вычислений по группе формул.
- «Поиск решения». Это надстройка программы Excel. Помогает найти наилучшее решение определенной задачи.
Практический пример использования «Что-если» для поиска оптимальных скидок по таблице данных.
Другие инструменты для анализа данных:
Анализировать данные в Excel можно с помощью встроенных функций (математических, финансовых, логических, статистических и т.д.).
Сводные таблицы в анализе данных
Чтобы упростить просмотр, обработку и обобщение данных, в Excel применяются сводные таблицы.
Программа будет воспринимать введенную/вводимую информацию как таблицу, а не простой набор данных, если списки со значениями отформатировать соответствующим образом:
- Перейти на вкладку «Вставка» и щелкнуть по кнопке «Таблица».
- Откроется диалоговое окно «Создание таблицы».
- Указать диапазон данных (если они уже внесены) или предполагаемый диапазон (в какие ячейки будет помещена таблица). Установить флажок напротив «Таблица с заголовками». Нажать Enter.
К указанному диапазону применится заданный по умолчанию стиль форматирования. Станет активным инструмент «Работа с таблицами» (вкладка «Конструктор»).
Составить отчет можно с помощью «Сводной таблицы».
- Активизируем любую из ячеек диапазона данных. Щелкаем кнопку «Сводная таблица» («Вставка» - «Таблицы» - «Сводная таблица»).
- В диалоговом окне прописываем диапазон и место, куда поместить сводный отчет (новый лист).
- Открывается «Мастер сводных таблиц». Левая часть листа – изображение отчета, правая часть – инструменты создания сводного отчета.
- Выбираем необходимые поля из списка. Определяемся со значениями для названий строк и столбцов. В левой части листа будет «строиться» отчет.
Создание сводной таблицы – это уже способ анализа данных. Более того, пользователь выбирает нужную ему в конкретный момент информацию для отображения. Он может в дальнейшем применять другие инструменты.
Анализ «Что-если» в Excel: «Таблица данных»
Мощное средство анализа данных. Рассмотрим организацию информации с помощью инструмента «Что-если» - «Таблица данных».
- данные должны находиться в одном столбце или одной строке;
- формула ссылается на одну входную ячейку.
Процедура создания «Таблицы данных»:
- Заносим входные значения в столбец, а формулу – в соседний столбец на одну строку выше.
- Выделяем диапазон значений, включающий столбец с входными данными и формулой. Переходим на вкладку «Данные». Открываем инструмент «Что-если». Щелкаем кнопку «Таблица данных».
- В открывшемся диалоговом окне есть два поля. Так как мы создаем таблицу с одним входом, то вводим адрес только в поле «Подставлять значения по строкам в». Если входные значения располагаются в строках (а не в столбцах), то адрес будем вписывать в поле «Подставлять значения по столбцам в» и нажимаем ОК.
Анализ предприятия в Excel: примеры
Для анализа деятельности предприятия берутся данные из бухгалтерского баланса, отчета о прибылях и убытках. Каждый пользователь создает свою форму, в которой отражаются особенности фирмы, важная для принятия решений информация.
Для примера предлагаем скачать финансовый анализ предприятий в таблицах и графиках составленные профессиональными специалистами в области финансово-экономической аналитике. Здесь используются формы бухгалтерской отчетности, формулы и таблицы для расчета и анализа платежеспособности, финансового состояния, рентабельности, деловой активности и т.д.
Читайте также: