"Обработка табличной информации с помощью сводных таблиц средствами MicroSoft Excel

 

 

 

 

Краснодарский Государственный Университет Культуры и Искусств

 

 

 

Факультет

Экономики Управления и Рекламы

 

Дисциплина: Информатика

 

 

 

 

РЕФЕРАТ

ТЕМА: "Обработка табличной информации с помощью сводных таблиц средствами MicroSoft Excel"

 

 

 

 

 

 

 

 

 

Выполнила: Студентка 2-го курса

        Группы НЭК-99

        Никитина Т.

 

 

Проверил:____________________

 

 

 

 

 

 

 

 

 

 

 

ОГЛАВЛЕНИЕ

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ВВЕДЕНИЕ.

 

         Для начала всё-таки стоит прояснить некоторые вещи, например что такое "сводные таблицы" и для чего они вообще существуют в природе...

1.Сводная таблица - это таблица, которая используется для быстрого подведения итогов или объединения больших объемов данных. Меняя местами строки и столбцы, можно создать новые итоги исходных данных; отображая разные страницы можно осуществить фильтрацию данных, а также отобразить детально данные области.

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

2.Сводная таблица - это инструмент суммирования данных в программах визаулизации данных, таких как электронные таблицы (например, Mircosoft Excel, OpenOffice.org Calc и Lotus 1-2-3) или программы класса business intelligence/бизнес-аналитика (напр., Spotfire). Помимо прочих функций, сводные таблицы могут автоматически сортировать данные, подсчитывать количество данных (записей), рассчитывать итоги и промежуточные итоги на основании данных, которые хранятся в одной таблице (электронной, базе данных, в ином источнике), чтобы затем вывести результаты в другой таблице (которая собственно и называется "сводной"). Особенно сводные таблицы полезны при быстром создании кросс-таблиц.

Пользователь создает и изменяет струкуру сводной таблицы посредством захвата и перетаскивания графических элементов, изображающих поля сводной таблицы. Такая визуальная ротация графических элементов-полей стала основой для английского названия сводной таблицы - pivot table (глагол pivot в переводе означает вертеть, вращать, крутить). Понятие сводной таблицы стало общеупотребительным у многих производителей программного обеспечения. Но у корпорации Microsoft есть также и торговая марка PivotTable.

В своей книге "Обработка данных с помощью сводной таблицы" ее авторы Билл Джелен и Майк Алекзандер называют "отцом" сводной таблицы американского программиста Пито Саласа. Работая над концепцией новой программы, которая в дальнейшем вышла в свет под названием Lotus Improv, Салас осознал, что электронные таблицы представляют собой повторяющиеся массивы (patterns, паттерны) данных. Средство для распознавания этих паттернов могло бы помочь пользователю и в быстром построении более сложных моделей аггрегированных данных. С помощью Impov пользователи могли бы формировать и сохранять у себя категории данных, которые затем перемещались бы с помощью мышки. Эта базовая функция стала основной для создания модели сводной таблицы.

Компания Lotus Development выпустила первый релиз сводной таблицы в 1991 году на базе платформы NeXT. Несколько месяцев спустя компания Brio Technology опубликовала единственную в своем роде реализацию на базе MacOS сводной таблицы под названием PivotData (как технология запатентована в 1999 году). Компания Borland выкупила технологию PivotData в 1992 году и применила ее в собственном приложении электронных таблиц под названием Quattro Pro.

В 1993 году, когда появился аналог Improv на базе Windows, Microsoft Excel вот уже как 5 лет присутствовал на рынке с функцией под названием "PivotTable". Эта функция была в дальнейшем улучшена в более поздних версиях Excel:

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

Excel 2000 включал в себя "сводные  графики", которые позволялил  графически презентовать данные  сводной таблицы.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ГЛАВА 1. СОЗДАНИЕ СВОДНОЙ ТАБЛИЦЫ

 

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

Для случая, когда  сводную страницу создают из одного документа (листа Microsoft Excel), то наиболее рационально  пользоваться мастером сводной таблица, кнопка которого находится на панели "сводных таблиц":

 

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

• в списке или базе данных Microsoft Excel
• во-внешнем источнике данных
• в нескольких диапазонах консолидации

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

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

После завершения этого этапа появится окно третьего шага мастера (рис.1)

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

Следующее окно, которое появится, является последним. Здесь необходимо выбрать один из двух параметром, "куда будет помещена таблица": новый лист, существующий лист. Кроме этой опции в этом окне расположена также ещё и кнопка "параметров" для создаваемой таблицы, в которой можно:

• Выбрать имя таблицы
• В пункте "формат" выбрать опции: общая сумма по столбцам; общая сумма по строкам; автоформат; включать скрытые значения; объединять ячейки заголовков; сохранять форматирование.
• В том же пункте выбрать макет страницы: вниз затем поперёк; поперёк, затем вниз.
• Символы, которые будут выводится вместо пустых ячеек, а так же вместо ошибок.
• В пункте данные: сохранять данные вместе с таблицей, развертывание (Если флажок развертывание "разрешено" снят, то при двойном щелчке ячейки в области данных сводной таблицы детали не отображаются.); обновление данных из источника при каждом открытии.

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

 

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

Что же касается опции " в нескольких диапазонах консолидации" всё в том же первом окне мастера, то там будет несколько иной порядок выполнения. Второе окно предложит выбрать, как следует создавать поля на страничке: создать поля страница; создать одно поле.

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

 

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

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

 

1.1 Основные элементы сводных таблиц

 

 

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

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

 

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

 

 

Поля строки - это поля исходного списка или таблицы, помещенные в область строчной ориентации сводной таблицы. В этом примере «Продукты» и «Продавец» являются полями строки. Внутренние поля строки (например «Продавец») в точности соответствуют области данных; внешние поля строки (например «Продукты») группируют внутренние. Таким образом здесь прослеживается определённое правило, каждый элемент (поле) более высокого уровня как бы уконкречивают все поля более низкого уровня: параметр "продукт" ограничивает все данные под ним - "продавцы продуктов", "данные по продажи продуктов этими продавцами".

 

 

Поле столбца - это поле исходного списка или таблицы, помещенное в область столбцов. В этом примере «Кварталы» является полем столбца, включающим два элемента поля «КВ2» и «КВ3». Внутренние поля столбцов содержат элементы, соответствующие области данных; внешние поля столбцов располагаются выше внутренних (в примере показано только одно поле столбца).

 

Областью данных называется часть сводной таблицы, содержащая итоговые данные. В ячейках области данных отображаются итоги для элементов полей строки или столбца. Значения в каждой ячейке области данных соответствуют исходным данным. В этом примере в ячейке C6 суммируются все записи исходных данных, содержащие одинаковое название продукта, распространителя и определенный квартал («Мясо», «ТОО Мясторг» и «КВ2»).

 

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

 

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

 

 

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

 

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

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

 

 

 

 

 

 

 

 

 

 

 

 

 

ГЛАВА 2. ДЕТАЛЬНЫЕ ДАННЫЕ СВОДНОЙ ТАБЛИЦЫ И ВЫПОЛНЯЕМЫЕ НАД НИМИ ДЕЙСТВИЯ

 

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

 

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