Структурирование рабочих листов

 

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

 

пример – файл Списки – лист Полный ассортимент – 4 уровня (товар – название – сумма – количество)

 

«минус» – скрыть уровень, «плюс» - раскрыть уровень

В группе структура кнопки «Скрыть детали» и «Отобразить детали» позволяют скрыть / раскрыть столбцы данных, по которым не подведены итоги.

Команда «Группировать» позволяет вручную создать структуру

Команда «Разгруппировать» позволяет удалить структуру.

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

 

пример – файл Списки – лист Полный ассортимент

диаграмма по первому уровню (Товар – итог), круговая диаграмма для одного товара

 

Создание консолидированных отчетов

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

команда «Консолидация» действует на все строки, содержащие данные, как на элементы списка, поэтому промежуточные итоги нужно удалить.

 

Для создания консолидированного отчета следует:

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

2. установить курсор в месте размещения отчета, на листе отчет и выполнить команду «Консолидация»

3. выбрать функцию

4. выбрать список диапазонов (выделять вместе с заголовками)

5. установить флажки «Подписи верхней строки», «Значение левого столбца»

6. ОК

 

Будет получен отчет по функции, которая выбрана

 

Экспорт, импорт данных

 

Обмен данными между приложениями Excel и Access

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

· Вкладка «Данные» группа «Получить данные» - «из Access»

· Выбираем базу данных

· «Выделить таблицу» выбираем таблицу

· В окне диалога «Выбор данных» указываем способ представления данных (таблица, отчет сводной таблицы, сводная диаграмма) – ок

· Данные будут импортированы

 

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

· Открыть базу данных – внешние данные – Excel

· Выбрать где сохранять данные (в новой базе, в новой таблице существующей базы, добавлять в конец существующей таблицы)

· Далее по шагам мастера (выбрать тип или диапазон, указать первую строку как строку заголовка, если необходимо изменить описание полей, имя таблицы) – ОК

· Данные будут внесены (или добавлены) в таблицу Access

 

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

Текстовый файл формата TXT содержит однотипные строки с разделением запятой или клавишей «Таб»

· Вкладка «Данные» группа «Получить данные» - «из текста»

· Выбрать текстовый файл

· Указать разделитель - ОК

Данные будут перенесены на рабочий лист Excel

 

Импорт данных из баз данных выполняется аналогично

 

Сводные таблицы

 

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

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

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

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

Сводная таблица строится по данным списка.

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

· Данные. В полях этого типа содержатся значения. Например, поле Счет, Количество (вычисляется по формулам)

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

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

 

Для построения сводной таблицы следует:

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

2. выполнить команду «Сводная таблица» - группа таблицы – вкладка Вставка

3. в окне диалога «Создание сводной таблицы» указать источник для построения таблицы:

· выбрать диапазон данных рабочего листа Excel, в том числе и на другом рабочем листе

· другая сводная таблица

· база данных во внешнем источнике (Access, Paradox и т.д.)

4. указать место, где будет размещена сводная таблица

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

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

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

 

Пример построение сводной таблицы по данным файла «Данные для отчетов1». Строки – наименование товара, столбцы – поставщик (менеджер), поле данных – объем, цена, затраты.

 

Форматирование проводиться стандартными средствами, вкладка «Конструктор» имеет стандартные стили оформления таблицы.

 

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

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

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

 

Окно диалога «Параметры сводной таблицы»содержит следующие опции:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

Опции этого диалогового окна «Параметры поля значений»:

· Имя. Изменяет название, отображаемое на кнопке поля. Это название можно также изменить, если непосредственно отредактировать содержимое ячейки, содержащей кнопку поля.

· Операция – выбор операции, которую следует использовать для сведения данных в выбранном поле

· Числовой формат – позволяет установить формат данных

Если щелкнуть по названию строки или столбца, откроется окно диалога «Параметры поля»

 

пример файл «данные для отчетов1»

 

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

Чтобы создать группу, выделите ячейки, которые будут сгруппированы. Затем выберите команду Данные | Группа и структура | Группировать. В результате Excel создаст новое поле. Если элементы поля содержат числа, даты или время, то можно разрешить программе сгруппировать их автоматически, с указанием шага группировки.

 

пример таблицы с полем дата, группировка по дате

 

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