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

Совет:  

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

Сортировка данных сводной таблицы

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

В Microsoft Excel предусмотрен следующий порядок для сортировки данных по возрастанию: числа, текст, логические значения, значения ошибок (например #ССЫЛКА! и #ЗНАЧ!) и пустые ячейки. Для сортировки по убыванию используется обратная очередность, за исключением пустых ячеек, которые всегда помещаются в конец отсортированного списка. Чтобы создать личный порядок сортировки или применить встроенный в Microsoft Excel, необходимо воспользоваться специальным порядком сортировки.

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

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

 В появившемся окне установите переключатель "Диапазон" в положение "по возрастанию" или "по убыванию". Проверьте, что переключатель "Сортировать" установлен в положение "Метки".

Примечание:

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

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

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

После выделения нажмите кнопку "Поле сводной таблицы"  на панели инструментов "Сводные таблицы". После чего нажмите кнопку "Далее".

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

Примечание: 

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

Использование в сводной таблице пользовательского порядка сортировки

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

В появившемся окне нажать   кнопку "Параметры".

Далее в новом окне выберите параметр сортировки в раскрывающемся списке "Порядок сортировки по первому ключу".

Примечания:

·     Пользовательский порядок сортировки нарушается при обновлении сводной таблицы. Чтобы восстановить порядок сортировки после обновления сводной таблицы, выполните сортировку повторно.

·     Если сводная таблица содержит элементы, которые обычно сортируются в хронологическом, а не в алфавитном порядке — например «Янв», «Фев», «Мар» — в Microsoft Excel автоматически используется пользовательский порядок сортировки. Если позднее элементы полей были перемещены или упорядочены иначе, исходный порядок можно восстановить сортировкой с использованием пользовательского порядка сортировки.

 

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

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

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

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

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

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

 

Использование нескольких итоговых функций для обработки данных сводной таблицы

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

После этого нажмите кнопку "Мастер сводных таблиц"   на панели инструментов "Сводные таблицы".

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

 И в заключении нажмите кнопку "OK", а затем — кнопку "Готово".

Использование дополнительных вычислений для поля данных сводной таблицы

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

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

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

Примечание: 

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

Создание вычисляемого поля в сводной таблице

Для создание вычисляемого поля в сводной таблице выделите ячейку в сводной таблице.

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

В появившемся окне введите имя вычисляемого поля в поле "Имя", а так же формулу в поле "Формула".

Чтобы использовать в формуле данные поля, выберите поле в списке "Поля", а затем нажмите кнопку "Добавить поле". Потом  нажмите кнопку "Добавить", а затем — кнопку "OK".

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

Примечание:

  Формулы для вычисляемых полей всегда воздействуют на все доступные данные сводной таблицы; использование отдельной части данных в формуле недопустимо.

Создание вычисляемого элемента поля в сводной таблице

Для создание вычисляемого элемента поля в сводной таблице необходимо:

Если элементы в поле, используемом для создания формулы, сгруппированы, разгруппируйте их. Далее нужно выделить поле или элемент поля. После выделения выберите команду "Формулы" в меню "Сводная таблица" на панели инструментов "Сводные таблицы", а затем  выберите  вычисляемый объект. Соответственно нужно ввести  имя вычисляемого элемента в поле с раскрывающимся списком "Имя", а так же формулу для этого же элемента в поле "Формула". Кроме того, чтобы использовать в формуле данные элемента поля, выберите поле в списке "Поля", а элемент — в списке "Элементы", а затем нажмите кнопку "Добавить элемент". Если элемент находится в другом поле, то его использовать нельзя. Далее нажмите кнопку "Добавить", а затем — кнопку "OK".

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

Примечания:

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

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

Использование общих и промежуточных итогов в сводной таблице

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

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

Отображение или скрытие общих итогов в сводной таблице

Для отображение или скрытие общих итогов в сводной таблице выделите ячейку сводной таблицы, в которой они будут отображены.

После этого на панели инструментов "Сводные таблицы" нужно выбрать команду "Параметры" в меню "Сводная таблица".

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

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

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

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

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

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

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

Примечания:

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

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

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

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

Потом выберите несколько итоговых функций в группе "Промежуточные итоги".

Примечание:

 Чтобы добавить несколько промежуточных итогов (например «Сумма» и «Среднее») для каждого элемента внутреннего поля или отдельного поля сводной таблицы, установите переключатель в положение другие.

Создание диаграммы для сводной таблицы

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

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

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

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

Примечания:

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

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

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

Список литературы:

1.    Встроенная справка Microsoft Excel.

2.    М. Хелворсон, М. Янг “Эффективная работа с Microsoft Office”

3.    http://www.microsoft.com/rus/ - Российская страница компании Microsoft.

4.    “ Microsoft Office 8.0 “ , Эд Ботт, БИНОМ, Москва , 1998 год.