Меню Рубрики

Как сделать анализ данных таблицы

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

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

Советы, которые следует прочитать до начала работы

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

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

Наносите данные непосредственно на интерактивную сводную диаграмму В Excel можно создать автономную (отдельную) сводную диаграмму, которая позволяет взаимодействовать с данными и фильтровать их прямо на диаграмме.

Выберите полную мощь Power Pivot и Power View Если у вас установлен Office профессиональный плюс, воспользуйтесь преимуществами этих полезных надстроек:

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

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

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

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

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

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

Создав сводную таблицу на основе данных листа, внешних данных или информации из нескольких таблиц, воспользуйтесь списком полей, который позволяет добавлять, упорядочивать и удалять поля в отчете сводной таблицы.

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

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

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

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

Сортировка помогает упорядочивать большие объемы данных в сводных таблицах, чтобы упростить поиск объектов анализа. Данные можно отсортировать в алфавитном порядке, по убыванию или возрастанию.

Чтобы провести более подробный анализ определенного подмножества исходных данных сводной таблицы, их можно отфильтровать. Сделать это можно несколькими способами. Например, можно добавить один или несколько срезов, которые позволяют быстро и эффективно фильтровать информацию.

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

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

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

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

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

Для сведения данных в сводных таблицах предназначены функции расчета суммы, количества и среднего значения. Функции сведения недоступны в сводных таблицах на базе источников данных OLAP.

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

Если сводная таблица подключена к внешним данным, ее необходимо периодически обновлять, чтобы информация в таблице оставалась актуальной.

Если у вас установлен Office профессиональный плюс, запустите надстройку Power Pivot , которая поставляется вместе с Excel, для выполнения мощного анализа данных. После этого вы сможете создавать сложные модели данных в окне Power Pivot.

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

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

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

Для решения задач, связанных с анализом и моделированием данных в Power Pivot, можно использовать возможности вычисления, такие как функция автосуммирования, вычисляемые столбцы и формулы вычисляемых полей, а также настраиваемые формулы на языке выражений анализа данных (DAX).

С помощью Power Pivot можно создавать ключевые показатели эффективности и добавлять их в сводные таблицы.

В этом учебнике показано, как вносить изменения в модель данных для улучшения отчетов Power View.

Надстройка Power View, которая входит в состав Office профессиональный плюс, позволяет создавать интерактивные диаграммы и другие наглядные объекты на отдельных листах Power View, напоминающих панели мониторинга, которые можно представить всем заинтересованным лицам.

Из этих видеороликов вы узнаете, каких результатов можно добиться с помощью надстройки Power View, функции которой дополняются возможностями Power Pivot.

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

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

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

Фильтрация данных в сводной таблице Чтобы провести более подробный анализ определенного подмножества исходных данных сводной таблицы, их можно отфильтровать. Сделать это можно несколькими способами. Например, можно добавить один или несколько срезов, которые позволяют быстро и эффективно фильтровать информацию.

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

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

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

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

Don’t have time to figure this out? Our expert partners at Excelchat can do it for you, 24/7.

источник

Тема 2 АНАЛИЗ ДАННЫХ С ПОМОЩЬЮ СВОДНЫХ ТАБЛИЦ

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

Сводная таблица — это динамическая таблица итоговых данных, извлеченных или

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

На рис. 1 представлены данные для создания сводной таблицы. Это пример базы данных, содержащей ежедневно обновляемую информацию по счетам трех отделений банка. База данных состоит из 350 записей и позволяет отслеживать следующую информацию (файл bank.xls):

• Дату открытия каждого счета и его сумму.

• Тип счета (депозит, текущий, срочный или на предъявителя).

• Отделение, в котором открыт счет.

• Информацию о том, кем открыт счет (кассиром или уполномоченным по новым

• Информацию о клиенте (новый или старый).

В описываемой базе данных содержится много информации, но вся она не систематизирована. Чтобы информация была действительно полезной, ее надо обобщить.

Подведение итогов — процесс, состоящий из поиска ответов на интересующие вопросы, скрытые в имеющихся данных.

1 Перекрестная таблица — это таблица, в которой информация группируется всоответствии со значениями одного или нескольких полей и фактическиприводится к виду, аналогичному электронной таблице.

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

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

На рис. 3 изображена другая сводная таблица, созданная на основе тех же банковских данных. Она использует область страницы для элемента Клиент . В этом случае сводная таблица отображает данные только для новых пользователей (значение Новый в поле Клиент ). Изменена ориентация таблицы (значение из поля Отделение показаны в строке, а из поля Тип — в столбце).

Рис. 3. Сводная таблица, которая использует поле страницы

Данные, по которым подводятся итоги, должны быть представлены в форме базы данных. База данных может находиться на рабочем листе (такую базу данных называют табличной или списком) или в файле внешней базы данных. Хотя Excel может преобразовать любую базу данных в сводную таблицу, подобная операция будет иметь смысл не для любой базы данных.

Поля в табличных базах данных (списках) могут относиться к одному из двух

• Данные. В полях этого типа содержатся значения. Например, поле Счет (см.

• Категория. Поля этого типа описывают данные. Например, поля Дата , Тип , Открыл и Клиент описывают данные поля Счет (см. рис. 1).

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

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

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

3. Создание сводных таблиц

Для создания сводной таблицы можно воспользоваться средством Мастер сводных

таблиц и диаграмм. Чтобы получить доступ к этому средству, необходимо выполнить команду Данные → Сводная таблица.

Если выполнить эту команду, то появится первое из трех диалоговых окон (рис. 6). На этом этапе определяется источник данных.

Рис. 6. Диалоговое окно Мастер сводных таблиц и диаграмм — шаг 1 из 3

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

1. Данные в списке Excel. Чаще всего анализируемые данные хранятся в базе данных рабочего листа, который называется списком . База данных, хранящаяся в рабочем листе, не может иметь больше 65 535 записей и 256 полей. В первой строке базы данных должны содержаться названия полей. Данные могут состоять из чисел, текста или формул.

2. Данные во внешнем источнике данных. Если для создания сводной таблицы используется внешняя база данных, то данные вводятся в рабочий лист с помощью отдельного приложения Microsoft Query , входящего в поставку Excel или Office . Можно использовать файлы dBASE , данные SQL -сервера или другие источники. Выбор источника данных осуществляется на втором шаге работы Мастера сводных таблиц и диаграмм .

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

4. Данные в другой сводной таблице. Excel позволяет создавать сводную таблицу из созданной ранее сводной таблицы. На самом деле это не совсем верно. Сводная таблица, которая создается, помогает анализировать исходные данные, использовавшиеся для построения первой таблицы (а не данные самой сводной таблицы).

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

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

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

Рис. 7. Диалоговое окно Мастер сводных таблиц и диаграмм — шаг 2 из 3

Если при выполнении команды Данные → Сводная таблица табличный курсор находился на одном из элементов списка, то Excel автоматически определит диапазон ячеек базы данных на втором этапе работы Мастера сводных таблиц и диаграмм . Чтобы открыть другой рабочий лист и выбрать диапазон ячеек, надо щелкнуть на кнопке Обзор . Чтобы перейти к третьему этапу, надо щелкнуть на кнопке Далее .

Последнее диалоговое окно Мастера сводных таблиц и диаграмм показано на рис. 8. Здесь необходимо указать местоположение создаваемой сводной таблицы.

Рис. 8. Диалоговое окно Мастер сводных таблиц и диаграмм — шаг 3 из 3

Если выбрать переключатель Новый лист , то Excel вставит новый рабочий лист для сводной таблицы в текущую рабочую книгу. Если выбран переключатель Существующий лист , то сводная таблица появится на текущем рабочем листе (можно указать ее положение на листе).

Если в этом окне мастера щелкнуть на кнопке Параметры , то откроется диалоговое окно Параметры сводной таблицы (рис. 9), в котором можно определить параметры отображения данных в сводной таблице. Щелкните на кнопке ОК для возврата в диалоговое окно Мастера сводных таблиц и диаграмм .

Читайте также:  Как выглядит анализ на маркеры гепатита

Рис. 9. Диалоговое окно Параметры сводной таблицы

Это диалоговое окно содержит следующие опции:

• Имя . Это поле позволяет определить название сводной таблицы. Excel по умолчанию назьшает их СводнаяТаблица1 , СводнаяТаблица2 и т.д.

• Общая сумма по столбцам . Установите флажок этой опции, если необходимо подвести общие итоги по столбцам.

• Общая сумма по строкам . Установите флажок этой опции, если необходимо подвести общие итоги по строкам.

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

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

• Объединить ячейки заголовков . Установите флажок этой опции, если необходимо объединить ячейки для внешних заголовков строк и столбцов. В результате таблицу будет легче читать.

• Сохранять форматирование . Установите флажок этой опции, если необходимо сохранять любое ваше форматирование при изменении сводной таблицы.

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

• Макет страницы . С помощью этой опции можно указать порядок следования полей страниц.

• Число полей в столбце . С помощью этой опции можно указать количество выводимых полей страницы в строке или столбце перед выводом следующей строки или столбца.

• Для ошибок отображать . Можно указать значение, которое будет выводиться

в ячейках сводной таблицы при возникновении ошибки.

• Для пустых ячеек отображать . Можно указать значение, которое будет выводиться в пустых ячейках.

• Печать заголовков . Установите флажок этой опции, если необходимо на каждой странице отчета сводной таблицы распечатывать заголовки столбцов.

• Сохранить данные вместе с таблицей . Если эта опция активизирована, то

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

• Развертывание разрешено . Если данная опция активизирована, то после двойного щелчка на ячейке сводной таблицы выводятся дополнительные сведения для этой ячейки.

• Обновить при открытии . Если эта опция активизирована, то после открытия книги сводная таблица будет обновлена автоматически.

• Сохранить пароль . При использовании внешней базы данных, которая требует пароля, эта опция позволяет сохранить пароль как часть запроса, после чего не требуется его ввод.

• Фоновый запрос . Если эта опция активизирована, то запрос к внешней базе данных будет выполняться в фоновом режиме, и вы сможете продолжить работу в Excel .

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

Для создания макета сводной таблицы Excel предоставляет два способа:

• с помощью мастера сводных таблиц и диаграмм;

• самостоятельно на рабочем листе с использованием инструментов панели

Рис. 10. Панель инструментов Сводные таблицы

Создание макета сводной таблицы с помощью мастера

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

Макет сводной таблицы имеет четыре области:

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

• Столбец . Значения поля, помещенного в эту область, используются в качестве заголовков столбцов в сводной таблице.

• Данные . Поле, для которого подводятся итоги в сводной таблице.

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

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

При перетаскивании кнопки поля в область данных Мастер сводных таблиц по умолчанию применит функцию СУММ , если поле содержит числовые значения, и функцию СЧЁТ — если поле содержит нечисловые значения.

Рис. 11. Диалоговое окно Мастер сводных таблиц и диаграмм — макет

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

Если случайно перетащили поле не в ту область, то необходимо выведите его за пределы макета, чтобы удалить из области. На рис. 12 показано диалоговое окно после перетаскивания некоторых кнопок полей в сводную таблицу. Эта сводная таблица будет отображать сумму по полю Счет в зависимости от типа счета (поле строки Тип ) и клиента (поле столбца Клиент ). А поле Отделение будет использоваться в качестве поля страницы. Чтобы вернуться к диалоговому окну Мастера сводных таблиц и диаграмм , нужно щелкнуть на кнопке ОК .

Рис. 12. Готовый макет сводной таблицы

Создание макета сводной таблицы с помощью панели инструментов

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

Выполните два первых шага Мастера сводных таблиц и диаграмм . Укажите местоположение сводной таблицы во втором диалоговом окне Мастера сводных таблиц и диаграмм и щелкните на кнопке Готово в этом окне. Excel отобразит в рабочем листе шаблон сводной таблицы, как показано на рис. 13.

Рис. 13. Готовый макет сводной таблицы

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

Если вы сделали ошибку и поместили какое-либо поле не в ту область сводной таблицы, которую запланировали, просто перетащите это поле в нужную область или за пределы шаблона. Все поля остаются на панели Список полей сводной таблицы , даже если вы перетащили их в шаблон таблицы.

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

4. Работа со сводными таблицами

Созданная сводная таблица не является статистическим объектом. Ее можно изменять и настраивать, пока она не будет выглядеть так, как нужно. При работе со сводными таблицами очень полезна панель инструментов Сводные таблицы . Эта панель инструментов появляется автоматически при активизации рабочего листа, содержащего сводную таблицу.

Изменение структуры сводной таблицы

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

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

Чтобы удалить поле из сводной таблицы, просто щелкните на элементе поля и перетащите его за пределы сводной таблицы. Кнопка поля будет перечеркнута символом x. Отпустите кнопку мыши, и из таблицы будет исключено выбранное поле.

Если Вам необходимо добавить новое поле в сводную таблицу, выберите элемент этого поля на панели инструментов Сводные таблицы и перетащите его в нужную область сводной таблицы. Excel изменит сводную таблицу в соответствии с новым полем. Для вставки нового поля можно также использовать Мастер сводных таблиц и диаграмм. Выполните команду Данные → Сводная таблица. На экране появится третье диалоговое окно средства Мастер сводных таблиц и диаграмм. Щелкните на кнопке Макет и внесите изменение в макет сводной таблицы.

Обновление сводной таблицы

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

• Выберите команду Данные→ Обновить данные .

• Щелкните правой кнопкой мыши на любой ячейке сводной таблицы и выберите из появившегося контекстного меню команду Обновить данные .

• Щелкните на кнопке Обновить данные , которая расположена на панели инструментов Сводные таблицы

5. Настройка параметров полей сводной таблицы

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

Вычисление поля сводной таблицы , показанное на рис. 14.

источник

Microsoft Excel предлагает средства для анализа статистических данных. Такие встроенные функции, как СРЗНАЧ (AVERAGE), МЕДИАНА (MEDIAN) и МОДА (MODE), могут использоваться для проведения анализа данных. Если встроенных статистических функций недостаточно, необходимо обратиться к пакету Анализ данных.

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

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

1. Выберите в меню Сервис команду Анализ данных. При первом выборе этой команды Excel загружает файл с диска. Затем на экране появится окно диалога Анализ данных

2. Чтобы использовать какой-либо из инструментов анализа, выберите его имя в списке и нажмите кнопку ОК.

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

Если команда Анализ данных отсутствуетв меню Сервис или формула, содержащая функцию из пакета анализа, возвращает ошибочное значение #ИMЯ?(#NAME?), выберите в меню Сервис команду Надстройки, затем Пакет анализа в списке надстроек, после чего нажмите кнопку ОК. Если Пакет анализа отсутствует в списке надстроек, вы должны установить его, запустив программу Setup.

При анализе данных часто возникает необходимость определения различных статистических характеристик или параметров распределения. С помощью Microsoft Excel можно анализировать распределение, используя несколько инструментов: встроенные статистические функции, функции для оценки разброса данных, инструмент Описательная статистика (Descriptive Statistics), который предоставляет удобные сводные таблицы основных параметров распределения, инструменты Гистограмма (Histogram), Ранг и персентиль (Rank and Percentile).

Встроенные статистические функции Microsoft Excel применяются при проведении статистического анализа данных. В данном разделе мы ограничимся обсуждением наиболее часто используемых статистических функций. Кроме них Excel также предлагает более сложные функции ЛИНЕЙН (LINEST), ЛГРФПРИБЛ (LOGEST), ТЕНДЕНЦИЯ (TREND) и РОСТ (GROWTH), которые работают с числовыми массивами.

Описательная статистика (Descriptive Statistics) позволяет создать таблицу основных статистических характеристик для одного или нескольких множеств входных значений. Выходной диапазон содержит таблицу со статистическими характеристиками для каждой переменной входного диапазона: среднее, стандартная ошибка, медиана, мода, стандартное отклонение и дисперсия выборки, коэффициент эксцесса, коэффициент асимметрии, размах, минимальное значение, максимальное значение, сумма, количество значений, k-е наибольшее и наименьшее значения (для любого заданного k) и доверительный интервал для среднего.

Для использования Описательная статистика в меню Сервис выберите команду Анализ данных, затем в спискеИнструменты анализа окна диалога Анализ данных выберите инструмент Описательная статистика и нажмите кнопку ОК. Появится окно диалога,

Инструмент Описательная статистика требует задания входного диапазона, который может содержать одну или несколько переменных, и выходного диапазона. Вы должны также указать, как расположены переменные в столбцах или в строках. Установите флажок Метки в первой строке, если первая строка во входном диапазоне содержит названия столбцов. Excel использует эти метки для создания заголовков в выходной таблице.

Чтобы получить представленную выше таблицу статистических характеристик, установите флажки в области Параметры вывода.

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

Рассмотрим построение графиков функций на примере функции у = sin x.

Вид данного графика хорошо известен вам по урокам математики, попробуем построить его средствами Excel.

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

Чтобы создать таблицу, нужно определить

· отрезок оси ОХ, на котором будет строиться график.

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

Задача 1.Построить график функции у = sin x на отрезке [– 2; 2] с шагом h = 0,5.

1. Заполним таблицу значений функции. В ячейку С4 введем первое значение отрезка: – 2
2. В ячейку D4 введем формулу, которая будет добавлять к лево-стоящей ячейки шаг: = В4 + $A$4
3. Маркером заполнения ячейки D4 заполним влево ячейки строки 4, до тех пор, пока получим значение другого конца отрезка: 2.
4. Выделим ячейку С5, вызовем Мастер функций, в категории математические выберем функцию SIN, в качестве аргумента функции выберем ячейку С4.
5. Маркером заполнения распространим эту формулу в ячейках строки 5 до конца таблицы.

Таким образом, мы получили таблицу аргументов (х) и значений (у) функции у = sin x на отрезке [-2;2] с шагом h = 0,5 :

x -2 -1,75 -1,5 -1,25 -1 -0,75 -0,5 -0,25 0,25 0,5 0,75 1,25 1,5 1,75
y -0,9092 -0,9839 -0,9974 -0,9489 -0,8414 -0,6816 -0,4794 -0,2474 0,2474 0,4794 0,6816 0,8414 0,9489 0,9974 0,9839 0,9092

6. Следующий шаг. Выделим таблицу и вызовем Мастер диаграмм. На первом шаге выберем во вкладке Нестандартные Гладкие графики.
7. На втором шаге во вкладке Ряд выполним:

В поле Ряд необходимо выделить ряд х и нажать на кнопку “Удалить” (график изменений х нам не нужен. График функции – это график изменения значений у)

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

8. На третьем шаге заполним вкладку Заголовки.

9. Пример полученного графика.

На самом деле пока это мало похоже на график функции в нашем привычном понимании.

Для форматирования графика:

· Вызовем контекстное меню оси ОУ. Затем, выберем пункт Формат оси…. Во вкладке Шкала установим: цена основного деления: 1. Во вкладке Шрифт установим размер шрифта 8пт.

· Вызовем контекстное меню оси ОХ. Выберем пункт Формат оси….

Во вкладке Шкала установим: пересечение с осью ОУ установите номер категории 5 (чтобы ось ОУ пересекала ось ОХ в категории с подписью 0, а это пятая по счету категория).

Во вкладке шрифт установите размер шрифта 8пт. Нажмите на кнопку ОК.

Остальные изменения выполняются аналогично.

Для закрепления рассмотрим еще одну задачу на построение графика функций. Эту задачу попробуйте решить самостоятельно, сверяясь с экраном проектора.

Создание презентации с помощью программы «PowePoint

Я думаю этому большого объяснения не надо)ты же у меня знаешь как создавать презентацию

Microsoft Office Publisherупрощает для деловых пользователей самостоятельное создание и распространение эффектных маркетинговых материалов.

Читайте также:  Хгч на каком сроке сдать анализ

«Один из ведущих теоретиков по проблемам управления, Питер Друкер, говорит об этом так: «Цель маркетинга — сделать усилия по сбыту ненужными. Его цель — так хорошо познать и понять клиента, что товар или услуга будут точно подходить последнему и продавать себя сами». — «Основы маркетинга», Филип Котлер.

Маркетинговые материалы — визитки, календари, открытки, приглашения, программы, конверты, наклейки и многое другое. Все это очень легко создается с помощью программы Publisher.

Поиск литературы по изучению Microsoft Office Publisher не увенчался успехом. Это были огромные книги либо справочные системы MicrosoftOffice Publisher.

Актуальность выбора данной темы проекта заключается в повышении информационной грамотности создания маркетинговых документов пользователей персональной техники. А какова осведомленность о существовании такого программного продукта как Microsoft OfficePublisher. Анкетирование по данному вопросу было проведено среди учащихся в сети Интернет (31 — Да, знаю., 29 — Нет, что это такое?), учеников 6-9 классов (30 — Да, знаю., 70 — Нет, что это такое?), учителей гимназии (10 — Да, знаю., 29 — Нет, что это такое?).

По результатам опроса стало понятно, что о возможностях и назначении Microsoft Office Publisherзнают не так уж и много пользователей ПК. Поэтому создание электронного пособия, которое бы знакомило с основными возможностями программы, и размещения его в сети Интернет, будет полезным делом.

Outlook Express — программа для работы с электронной почтой и группами новостейот компании Майкрософт.

Outlook Express поставляется в составе операционных систем Windows начиная сWindows 95 OSR 2.5, Windows NT, а также вместе с браузером Internet Explorer, начиная с версии 4.0. Существуют также варианты Outlook Express для «классических» версий Mac OS.

Название Outlook Express предполагает, что эта программа является «облегчённой» версией Microsoft Outlook — органайзера от Майкрософт, который также содержит функции работы с электронной почтой. На самом деле, между этими двумя программами мало общего. Кроме того, Outlook, в отличие от Outlook Express, до версии 2007 не имел функцию работы с группами новостей.

Microsoft Outlook (Ма́йкрософт Аутлу́к) — персональный информационный менеджер с функциями почтового клиента и Groupware компании Майкрософт, входящий в пакет офисных программ Microsoft Office.

Помимо функций почтового клиента для работы с электронной почтой, Microsoft Outlook является полноценным органайзером, предоставляющим функции календаря, планировщика задач, записной книжки и менеджера контактов. Кроме того, Outlook позволяет отслеживать работу с документами пакета Microsoft Officeдля автоматического составления дневника работы.

Не следует путать Outlook с продуктом Outlook Express. Outlook Express — это клиент электронной почты, встроенный в большинство версий Windows, выпущенных в период с 1998 по 2003 годы.

Тут на рисунках написано КАК ДОБАВЛЯТЬ КОНТАКТ

ПРИВЕСТИ ПРИМЕР АДРЕСА ИНТЕРНЕТ И МАЙЛ .

ПРИМЕР АДРЕСА ЭЛЕКТРОННОЙ ПОЧТЫ

Дата добавления: 2014-11-25 ; Просмотров: 6549 ; Нарушение авторских прав? ;

Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет

источник

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

При выполнении сложных аналитических задач по статистике (к примеру, корреляционного и дисперсионного анализа, расчетов по алгоритму Фурье, создания прогностической модели) пользователи часто интересуются, как добавить анализ данных в Excel. Обозначенный пакет функций предоставляет разносторонний аналитический инструментарий, полезный в ряде профессиональных сфер. Но он не относится к инструментам, включенным в Эксель по умолчанию и отображающимся на ленте. Выясним, как включить анализ данных в Excel 2007, 2010, 2013.

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

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

  1. зайдите во вкладку «Файл», расположенную в верхней части ленты интерфейса;
  2. с левой стороны открывающегося меню найдите раздел «Параметры Эксель» и кликните по нему;
  3. просмотрите левую часть окошка, откройте категорию надстроек (вторая снизу в списке), выберите соответствующий пункт;
  4. в выпавшем диалоговом меню найдите пункт «Управление», кликните по нему мышью;
  5. клик вызовет на экран диалоговое окно, выберите раздел надстроек, если выставлено значение, отличное от «Надстройки Excel», поменяйте его на обозначенное;
  6. нажмите на экранную кнопку «Перейти» в разделе надстроек. В правой части выпадет список надстроек, которые устанавливает программа.

Поиск пакета в надстройках Excel

Рассмотрим, как активировать аналитические функции, предоставляемые надстройкой пакета:

  1. В перечне надстроек, выпавшем после последовательного выполнения предыдущих операций, пользователю надлежит поставить знак птички напротив раздела «Пакет анализа».
  2. Выбрав активацию пакета, необходимо нажать клавишу «Ок», расположенную в верхней правой части диалогового окна.
  3. После нажатия кнопки пакет появляется на ленте функций. Для получения доступа к нему в интерфейсе программы выбирается вкладка «Данные». В правой части меню «Раздел анализа». Там пользователь найдет иконку опции «Анализ данных».

Выбор нужной надстройки

Аналитический пакет оперирует большим набором инструментов, оптимизирующих решение статистических задач. Некоторые из числа:

  • операции с выборками;
  • построение гистограммы – разновидности столбчатой диаграммы, демонстрирующей разброс разных значений некоторого параметра в виде столбцов, площади которых соотносятся друг с другом так же, как удельные веса разных групп в рассмотренной выборе;
  • генерация случайных чисел;
  • порядковое и процентное ранжирование;
  • вариации регрессионного, дисперсионного, корреляционного, ковариационного анализа;
  • анализ по алгоритму Фурье;
  • экспоненциальное сглаживание – метод математических преобразований, преследующих цель выявления некоторого тренда или тенденции во временном ряду. Метод применяется для построения прогнозов.

Расположение функции «Анализ данных» на вкладке «Данные»

Чтобы применить ту или иную опцию, действуют по нижеприведенному алгоритму:

  1. Нажать на кнопку анализа на ленте.
  2. Кликнуть по названию необходимой пользователю функции.
  3. Нажать клавишу «Ок», находящуюся рядом с правым верхним углом окошка.
  4. В диалоговом окне указать массивы данных, используемые для решения текущей задачи.

Как включить анализ данных в Excel 2010

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

Алгоритм, как включить анализ данных в Excel 2007, отличается от остальных тем, что в самом начале (для выхода на параметры Excel) вместо кнопки «Файл» пользователь нажимает четырехцветный символ Microsoft Office. В остальном же последовательность операций идентична приведенной для других версий.

Анализ данных в Excel 2007: как включить?

источник

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

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

Одним из самых привлекательных анализов данных является «Что-если». Он находится: «Данные»-«Работа с данными»-«Что-если».

  1. «Подбор параметра». Применяется, когда пользователю известен результат формулы, но неизвестны входные данные для этого результата.
  2. «Таблица данных». Используется в ситуациях, когда нужно показать в виде таблицы влияние переменных значений на формулы.
  3. «Диспетчер сценариев». Применяется для формирования, изменения и сохранения разных наборов входных данных и итогов вычислений по группе формул.
  4. «Поиск решения». Это надстройка программы Excel. Помогает найти наилучшее решение определенной задачи.

Практический пример использования «Что-если» для поиска оптимальных скидок по таблице данных.

Другие инструменты для анализа данных:

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

Анализировать данные в Excel можно с помощью встроенных функций (математических, финансовых, логических, статистических и т.д.).

Чтобы упростить просмотр, обработку и обобщение данных, в Excel применяются сводные таблицы.

Программа будет воспринимать введенную/вводимую информацию как таблицу, а не простой набор данных, если списки со значениями отформатировать соответствующим образом:

  1. Перейти на вкладку «Вставка» и щелкнуть по кнопке «Таблица».
  2. Откроется диалоговое окно «Создание таблицы».
  3. Указать диапазон данных (если они уже внесены) или предполагаемый диапазон (в какие ячейки будет помещена таблица). Установить флажок напротив «Таблица с заголовками». Нажать Enter.

К указанному диапазону применится заданный по умолчанию стиль форматирования. Станет активным инструмент «Работа с таблицами» (вкладка «Конструктор»).

Составить отчет можно с помощью «Сводной таблицы».

  1. Активизируем любую из ячеек диапазона данных. Щелкаем кнопку «Сводная таблица» («Вставка» — «Таблицы» — «Сводная таблица»).
  2. В диалоговом окне прописываем диапазон и место, куда поместить сводный отчет (новый лист).
  3. Открывается «Мастер сводных таблиц». Левая часть листа – изображение отчета, правая часть – инструменты создания сводного отчета.
  4. Выбираем необходимые поля из списка. Определяемся со значениями для названий строк и столбцов. В левой части листа будет «строиться» отчет.

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

Мощное средство анализа данных. Рассмотрим организацию информации с помощью инструмента «Что-если» — «Таблица данных».

  • данные должны находиться в одном столбце или одной строке;
  • формула ссылается на одну входную ячейку.

Процедура создания «Таблицы данных»:

  1. Заносим входные значения в столбец, а формулу – в соседний столбец на одну строку выше.
  2. Выделяем диапазон значений, включающий столбец с входными данными и формулой. Переходим на вкладку «Данные». Открываем инструмент «Что-если». Щелкаем кнопку «Таблица данных».
  3. В открывшемся диалоговом окне есть два поля. Так как мы создаем таблицу с одним входом, то вводим адрес только в поле «Подставлять значения по строкам в». Если входные значения располагаются в строках (а не в столбцах), то адрес будем вписывать в поле «Подставлять значения по столбцам в» и нажимаем ОК.

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

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

источник

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

Тренер Учебного центра Softline с 2008 года.

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

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

Исходные данные могут быть любыми: данные по продажам, отгрузкам, доставкам и так далее.

  1. Откройте файл с таблицей, данные которой надо проанализировать.
  2. Выделите диапазон данных для анализа.
  3. Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).
  4. Должно появиться диалоговое окно «Создание сводной таблицы».
  5. Настройте отображение данных, которые есть у вас в таблице.

Перед нами таблица с неструктурированными данными. Мы можем их систематизировать и настроить отображение тех данных, которые есть у нас в таблице. «Сумму заказов» отправляем в «Значения», а «Продавцов», «Дату продажи» — в «Строки». По данным разных продавцов за разные годы тут же посчитались суммы. При необходимости можно развернуть каждый год, квартал или месяц — получим более детальную информацию за конкретный период.

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

Можно её детализировать, например, по странам. Переносим «Страны».

Можно посмотреть результаты по продавцам. Меняем «Страну» на «Продавцов». По продавцам результаты будут такие.

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

Полезное дополнение. Координаты нигде прописывать не нужно — достаточно лишь корректно указать географическое название в таблице.

  1. Откройте файл с таблицей, данные которой нужно визуализировать. Например, с информацией по разным городам и странам.
  2. Подготовьте данные для отображения на карте: «Главная» → «Форматировать как таблицу».
  3. Выделите диапазон данных для анализа.
  4. На вкладке «Вставка» есть кнопка 3D-карта.

Точки на карте — это наши города. Но просто города нам не очень интересны — интересно увидеть информацию, привязанную к этим городам. Например, суммы, которые можно отобразить через высоту столбика. При наведении курсора на столбик показывается сумма.

Также достаточно информативной является круговая диаграмма по годам. Размер круга задаётся суммой.

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

Полезное дополнение. Для расчёта прогноза потребуются данные за более ранние периоды. Точность прогнозирования зависит от количества данных по периодам — лучше не меньше, чем за год. Вам требуются одинаковые интервалы между точками данных (например, месяц или равное количество дней).

  1. Откройте таблицу с данными за период и соответствующими ему показателями, например, от года.
  2. Выделите два ряда данных.
  3. На вкладке «Данные» в группе нажмите кнопку «Лист прогноза».
  4. В окне «Создание листа прогноза» выберите график или гистограмму для визуального представления прогноза.
  5. Выберите дату окончания прогноза.

В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).

Для прогноза на 2014 год вам потребуются два ряда данных: даты и соответствующие им значения показателей. Выделяем оба ряда данных.

На вкладке «Данные» в группе «Прогноз» нажимаем на «Лист прогноза». В появившемся окне «Создание листа прогноза» выбираем формат представления прогноза — график или гистограмму. В поле «Завершение прогноза» выбираем дату окончания, а затем нажимаем кнопку «Создать». Оранжевая линия — это и есть прогноз.

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

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

  1. Откройте таблицу с данными для анализа.
  2. Выделите нужный для анализа диапазон.
  3. При выделении диапазона внизу всегда появляется кнопка «Быстрый анализ». Она сразу предлагает совершить с данными несколько возможных действий. Например, найти итоги. Мы можем узнать суммы, они проставляются внизу.

В быстром анализе также есть несколько вариантов форматирования. Посмотреть, какие значения больше, а какие меньше, можно в самих ячейках гистограммы.

Также можно проставить в ячейках разноцветные значки: зелёные — наибольшие значения, красные — наименьшие.

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

источник

Тема 2 АНАЛИЗ ДАННЫХ С ПОМОЩЬЮ СВОДНЫХ ТАБЛИЦ

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

Сводная таблица — это динамическая таблица итоговых данных, извлеченных или

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

На рис. 1 представлены данные для создания сводной таблицы. Это пример базы данных, содержащей ежедневно обновляемую информацию по счетам трех отделений банка. База данных состоит из 350 записей и позволяет отслеживать следующую информацию (файл bank.xls):

Читайте также:  Какие анализы нужны для диагностики диабета

• Дату открытия каждого счета и его сумму.

• Тип счета (депозит, текущий, срочный или на предъявителя).

• Отделение, в котором открыт счет.

• Информацию о том, кем открыт счет (кассиром или уполномоченным по новым

• Информацию о клиенте (новый или старый).

В описываемой базе данных содержится много информации, но вся она не систематизирована. Чтобы информация была действительно полезной, ее надо обобщить.

Подведение итогов — процесс, состоящий из поиска ответов на интересующие вопросы, скрытые в имеющихся данных.

1 Перекрестная таблица — это таблица, в которой информация группируется всоответствии со значениями одного или нескольких полей и фактическиприводится к виду, аналогичному электронной таблице.

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

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

На рис. 3 изображена другая сводная таблица, созданная на основе тех же банковских данных. Она использует область страницы для элемента Клиент . В этом случае сводная таблица отображает данные только для новых пользователей (значение Новый в поле Клиент ). Изменена ориентация таблицы (значение из поля Отделение показаны в строке, а из поля Тип — в столбце).

Рис. 3. Сводная таблица, которая использует поле страницы

Данные, по которым подводятся итоги, должны быть представлены в форме базы данных. База данных может находиться на рабочем листе (такую базу данных называют табличной или списком) или в файле внешней базы данных. Хотя Excel может преобразовать любую базу данных в сводную таблицу, подобная операция будет иметь смысл не для любой базы данных.

Поля в табличных базах данных (списках) могут относиться к одному из двух

• Данные. В полях этого типа содержатся значения. Например, поле Счет (см.

• Категория. Поля этого типа описывают данные. Например, поля Дата , Тип , Открыл и Клиент описывают данные поля Счет (см. рис. 1).

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

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

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

3. Создание сводных таблиц

Для создания сводной таблицы можно воспользоваться средством Мастер сводных

таблиц и диаграмм. Чтобы получить доступ к этому средству, необходимо выполнить команду Данные → Сводная таблица.

Если выполнить эту команду, то появится первое из трех диалоговых окон (рис. 6). На этом этапе определяется источник данных.

Рис. 6. Диалоговое окно Мастер сводных таблиц и диаграмм — шаг 1 из 3

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

1. Данные в списке Excel. Чаще всего анализируемые данные хранятся в базе данных рабочего листа, который называется списком . База данных, хранящаяся в рабочем листе, не может иметь больше 65 535 записей и 256 полей. В первой строке базы данных должны содержаться названия полей. Данные могут состоять из чисел, текста или формул.

2. Данные во внешнем источнике данных. Если для создания сводной таблицы используется внешняя база данных, то данные вводятся в рабочий лист с помощью отдельного приложения Microsoft Query , входящего в поставку Excel или Office . Можно использовать файлы dBASE , данные SQL -сервера или другие источники. Выбор источника данных осуществляется на втором шаге работы Мастера сводных таблиц и диаграмм .

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

4. Данные в другой сводной таблице. Excel позволяет создавать сводную таблицу из созданной ранее сводной таблицы. На самом деле это не совсем верно. Сводная таблица, которая создается, помогает анализировать исходные данные, использовавшиеся для построения первой таблицы (а не данные самой сводной таблицы).

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

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

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

Рис. 7. Диалоговое окно Мастер сводных таблиц и диаграмм — шаг 2 из 3

Если при выполнении команды Данные → Сводная таблица табличный курсор находился на одном из элементов списка, то Excel автоматически определит диапазон ячеек базы данных на втором этапе работы Мастера сводных таблиц и диаграмм . Чтобы открыть другой рабочий лист и выбрать диапазон ячеек, надо щелкнуть на кнопке Обзор . Чтобы перейти к третьему этапу, надо щелкнуть на кнопке Далее .

Последнее диалоговое окно Мастера сводных таблиц и диаграмм показано на рис. 8. Здесь необходимо указать местоположение создаваемой сводной таблицы.

Рис. 8. Диалоговое окно Мастер сводных таблиц и диаграмм — шаг 3 из 3

Если выбрать переключатель Новый лист , то Excel вставит новый рабочий лист для сводной таблицы в текущую рабочую книгу. Если выбран переключатель Существующий лист , то сводная таблица появится на текущем рабочем листе (можно указать ее положение на листе).

Если в этом окне мастера щелкнуть на кнопке Параметры , то откроется диалоговое окно Параметры сводной таблицы (рис. 9), в котором можно определить параметры отображения данных в сводной таблице. Щелкните на кнопке ОК для возврата в диалоговое окно Мастера сводных таблиц и диаграмм .

Рис. 9. Диалоговое окно Параметры сводной таблицы

Это диалоговое окно содержит следующие опции:

• Имя . Это поле позволяет определить название сводной таблицы. Excel по умолчанию назьшает их СводнаяТаблица1 , СводнаяТаблица2 и т.д.

• Общая сумма по столбцам . Установите флажок этой опции, если необходимо подвести общие итоги по столбцам.

• Общая сумма по строкам . Установите флажок этой опции, если необходимо подвести общие итоги по строкам.

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

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

• Объединить ячейки заголовков . Установите флажок этой опции, если необходимо объединить ячейки для внешних заголовков строк и столбцов. В результате таблицу будет легче читать.

• Сохранять форматирование . Установите флажок этой опции, если необходимо сохранять любое ваше форматирование при изменении сводной таблицы.

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

• Макет страницы . С помощью этой опции можно указать порядок следования полей страниц.

• Число полей в столбце . С помощью этой опции можно указать количество выводимых полей страницы в строке или столбце перед выводом следующей строки или столбца.

• Для ошибок отображать . Можно указать значение, которое будет выводиться

в ячейках сводной таблицы при возникновении ошибки.

• Для пустых ячеек отображать . Можно указать значение, которое будет выводиться в пустых ячейках.

• Печать заголовков . Установите флажок этой опции, если необходимо на каждой странице отчета сводной таблицы распечатывать заголовки столбцов.

• Сохранить данные вместе с таблицей . Если эта опция активизирована, то

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

• Развертывание разрешено . Если данная опция активизирована, то после двойного щелчка на ячейке сводной таблицы выводятся дополнительные сведения для этой ячейки.

• Обновить при открытии . Если эта опция активизирована, то после открытия книги сводная таблица будет обновлена автоматически.

• Сохранить пароль . При использовании внешней базы данных, которая требует пароля, эта опция позволяет сохранить пароль как часть запроса, после чего не требуется его ввод.

• Фоновый запрос . Если эта опция активизирована, то запрос к внешней базе данных будет выполняться в фоновом режиме, и вы сможете продолжить работу в Excel .

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

Для создания макета сводной таблицы Excel предоставляет два способа:

• с помощью мастера сводных таблиц и диаграмм;

• самостоятельно на рабочем листе с использованием инструментов панели

Рис. 10. Панель инструментов Сводные таблицы

Создание макета сводной таблицы с помощью мастера

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

Макет сводной таблицы имеет четыре области:

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

• Столбец . Значения поля, помещенного в эту область, используются в качестве заголовков столбцов в сводной таблице.

• Данные . Поле, для которого подводятся итоги в сводной таблице.

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

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

При перетаскивании кнопки поля в область данных Мастер сводных таблиц по умолчанию применит функцию СУММ , если поле содержит числовые значения, и функцию СЧЁТ — если поле содержит нечисловые значения.

Рис. 11. Диалоговое окно Мастер сводных таблиц и диаграмм — макет

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

Если случайно перетащили поле не в ту область, то необходимо выведите его за пределы макета, чтобы удалить из области. На рис. 12 показано диалоговое окно после перетаскивания некоторых кнопок полей в сводную таблицу. Эта сводная таблица будет отображать сумму по полю Счет в зависимости от типа счета (поле строки Тип ) и клиента (поле столбца Клиент ). А поле Отделение будет использоваться в качестве поля страницы. Чтобы вернуться к диалоговому окну Мастера сводных таблиц и диаграмм , нужно щелкнуть на кнопке ОК .

Рис. 12. Готовый макет сводной таблицы

Создание макета сводной таблицы с помощью панели инструментов

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

Выполните два первых шага Мастера сводных таблиц и диаграмм . Укажите местоположение сводной таблицы во втором диалоговом окне Мастера сводных таблиц и диаграмм и щелкните на кнопке Готово в этом окне. Excel отобразит в рабочем листе шаблон сводной таблицы, как показано на рис. 13.

Рис. 13. Готовый макет сводной таблицы

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

Если вы сделали ошибку и поместили какое-либо поле не в ту область сводной таблицы, которую запланировали, просто перетащите это поле в нужную область или за пределы шаблона. Все поля остаются на панели Список полей сводной таблицы , даже если вы перетащили их в шаблон таблицы.

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

4. Работа со сводными таблицами

Созданная сводная таблица не является статистическим объектом. Ее можно изменять и настраивать, пока она не будет выглядеть так, как нужно. При работе со сводными таблицами очень полезна панель инструментов Сводные таблицы . Эта панель инструментов появляется автоматически при активизации рабочего листа, содержащего сводную таблицу.

Изменение структуры сводной таблицы

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

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

Чтобы удалить поле из сводной таблицы, просто щелкните на элементе поля и перетащите его за пределы сводной таблицы. Кнопка поля будет перечеркнута символом x. Отпустите кнопку мыши, и из таблицы будет исключено выбранное поле.

Если Вам необходимо добавить новое поле в сводную таблицу, выберите элемент этого поля на панели инструментов Сводные таблицы и перетащите его в нужную область сводной таблицы. Excel изменит сводную таблицу в соответствии с новым полем. Для вставки нового поля можно также использовать Мастер сводных таблиц и диаграмм. Выполните команду Данные → Сводная таблица. На экране появится третье диалоговое окно средства Мастер сводных таблиц и диаграмм. Щелкните на кнопке Макет и внесите изменение в макет сводной таблицы.

Обновление сводной таблицы

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

• Выберите команду Данные→ Обновить данные .

• Щелкните правой кнопкой мыши на любой ячейке сводной таблицы и выберите из появившегося контекстного меню команду Обновить данные .

• Щелкните на кнопке Обновить данные , которая расположена на панели инструментов Сводные таблицы

5. Настройка параметров полей сводной таблицы

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

Вычисление поля сводной таблицы , показанное на рис. 14.

источник