КраснодарскийГосударственный Университет Культуры и Искусств
Факультет
ЭкономикиУправления и Рекламы
Дисциплина:Информатика
РЕФЕРАТ
ТЕМА:«Обработка табличной информации с помощью сводных таблиц средствами MicroSoftExcel»
Выполнила:Студентка 2-го курса
Группы НЭК-99
Никитина Т.
Проверил:____________________
Краснодар 2001
Оглавление
TOCo «1-3» Оглавление… PAGEREF_Toc502011200 h 2
Введение.… PAGEREF_Toc502011201 h 3
Созданиесводной таблицы… PAGEREF_Toc502011202 h 3
Основныеэлементы сводных таблиц… PAGEREF_Toc502011203 h 4
Изменениеструктуры сводной таблицы… PAGEREF_Toc502011204 h 5
Детальныеданные сводной таблицы и выполняемые над ними действия… PAGEREF_Toc502011205 h 6
Отображениеили скрытие детальных данных сводной таблицы… PAGEREF_Toc502011206 h 6
Отображениеили скрытие элементов поля сводной таблицы… PAGEREF_Toc502011207 h 7
Отображениемаксимальных или минимальных элементов в поле сводной таблицыPAGEREF_Toc502011208 h 7
Отключениедоступа к детальным сведениям сводной таблицы… PAGEREF_Toc502011209 h 7
Группировка иразгруппировка данных в сводной таблице… PAGEREF_Toc502011210 h 8
Группировка иразгруппировка элементов поля сводной таблицы… PAGEREF_Toc502011211 h 8
Группировка иразгруппировка чисел в сводной таблице… PAGEREF_Toc502011212 h 8
Группировка иразгруппировка дат или времен в сводной таблице… PAGEREF_Toc502011213 h 9
Группировка иразгруппировка элементов поля страницы сводной таблицы… PAGEREF_Toc502011214 h 9
Обновлениеили изменение источника данных сводной таблицы… PAGEREF_Toc502011215 h 9
Обновлениеданных в сводной таблице… PAGEREF_Toc502011216 h 10
Отключениеобновления сводной таблицы при открытии файла книги… PAGEREF_Toc502011217 h 11
Сортировкаданных сводной таблицы… PAGEREF_Toc502011218 h 11
Сортировкаэлементов сводной таблицы по названиям… PAGEREF_Toc502011219 h 11
Сортировкаэлементов сводной таблицы по значениям ячеек области данных… PAGEREF_Toc502011220 h 12
Использованиев сводной таблице пользовательского порядка сортировки… PAGEREF_Toc502011221 h 12
Подведениеитогов и обработка данных сводной таблицы… PAGEREF_Toc502011222 h 12
Изменениеитоговой функции в области данных сводной таблицы… PAGEREF_Toc502011223 h 13
Использованиенескольких итоговых функций для обработки данных сводной таблицы PAGEREF_Toc502011224 h 13
Использованиедополнительных вычислений для поля данных сводной таблицыPAGEREF_Toc502011225 h 13
Созданиевычисляемого поля в сводной таблице… PAGEREF_Toc502011226 h 14
Созданиевычисляемого элемента поля в сводной таблице… PAGEREF_Toc502011227 h 14
Использованиеобщих и промежуточных итогов в сводной таблице… PAGEREF_Toc502011228 h 15
Отображениеили скрытие общих итогов в сводной таблице… PAGEREF_Toc502011229 h 15
Вставка илиудаление промежуточных итогов в сводной таблице… PAGEREF_Toc502011230 h 15
Изменениеитоговой функции, используемой в вычислениях промежуточных итогов своднойтаблицы… PAGEREF_Toc502011231 h 16
Созданиедиаграммы для сводной таблицы… PAGEREF_Toc502011232 h 16
Контрольныевопросы:… PAGEREF_Toc502011233 h 16
Списоклитературы:… PAGEREF_Toc502011234 h 17
Введение.
Дляначала всё-таки стоит прояснить некоторые вещи, например что такое«сводные таблицы» и для чего они вообще существуют в природе...
Своднаятаблица — это таблица, которая используется для быстрого подведения итогов илиобъединения больших объемов данных. Меняя местами строки и столбцы, можносоздать новые итоги исходных данных; отображая разные страницы можноосуществить фильтрацию данных, а также отобразить детально данные области.
Другимисловами эти таблицы позволяют объединить данные с разных источников, такихкак базы данных, таблицы, а так же внешних источников данных, напримерИнтернет.
Создание сводной таблицы
Передтем как создать сводную таблицу необходимо сначала задать данные для этойтаблицы, выделив часть книги, страницы,а в случае если это база данных или же список, то необходимо выделить ячейкусписка или базы данных.
Для случая, когда сводную страницу создают из одного документа(листа Microsoft Excel), то наиболее рационально пользоваться мастером сводной таблица, кнопкакоторого находится на панели «сводных таблиц»:
После нажатия на эту кнопку пользователюоткроется окно, в котором ему будет предложен выбор (в данном случае не обязательно выделять область до запуска мастера)источника данных:
· всписке или базе данных Microsoft Excel
· во-внешнемисточнике данных
· внескольких диапазонах консолидации
думаюс первым и вторым пунктом всё относительно понятно, их названия говорят сами засебя, а что касается третьего пункта, то здесь подразумевается, что существуетнесколько источников данных, например, насколько листов книги.
При выборе первого пункта меню, пользовательперейдет в окно, в котором потребуется непосредственно указать источник данныхв документе, выделив их (если конечно это не было сделано до старта мастера).
Послезавершения этого этапа появится окно третьего шага мастера (рис.1)
Здесь предстоит непосредственноразместить поля, находящиеся справа на рисунке, зажав каждое из них мышкой ипередвинув в нужное поле: столбец, строка, данные, страница. Важно чтобы всеполя были заполнены, иначе продолжение работы будет невозможно.
Следующееокно, которое появится, является последним. Здесь необходимо выбрать один издвух параметром, «куда будет помещена таблица»: новый лист, существующийлист. Кроме этой опции в этом окне расположена также ещё и кнопка«параметров» для создаваемой таблицы, в которой можно:
F Выбрать имя таблицы
F В пункте «формат» выбратьопции: общая сумма по столбцам; общая сумма по строкам; автоформат; включать скрытыезначения; объединять ячейки заголовков; сохранять форматирование.
F В том же пункте выбрать макет страницы:вниз затем поперёк; поперёк, затем вниз.
F Символы, которые будут выводится вместопустых ячеек, а так же вместо ошибок.
F В пункте данные: сохранять данныевместе с таблицей, развертывание (Еслифлажок развертывание «разрешено» снят, то при двойном щелчке ячейки вобласти данных сводной таблицы детали не отображаются.); обновление данныхиз источника при каждом открытии.
Послезавершения всех настроек и нажатия на кнопку готово, будет выведена своднаятаблица с выбранными полями.
Еслипри создании сводной таблицы использовался пункт " во-внешнем источникеданных", в первом окне базы, то при продолжении появится окно, в которомнужно будет открыть или создать новыйфайл запроса (является средством для извлечение информации из базы данных,причем данные могут быть распределены среди нескольких таблиц) на данные, нажав кнопку «получитьданные». После этого момента мастер продолжит свою работу идентичнопредыдущему пункту.
Что жекасается опции " в нескольких диапазонах консолидации" всё в том жепервом окне мастера, то там будет несколько иной порядок выполнения. Второеокно предложит выбрать, как следует создавать поля на страничке: создать полястраница; создать одно поле.
Привыборе первого пункта далее будет необходимо выбрать источники (несколько)данных, а также указать количество полей на странице в интервале от 0 до 4, апосле этого в окошках выбрать диапазоны значений.
Послечего продолжится стандартный выбор значений для полей. Если же будет выбранпункт «создать одно поле», то появится такое же окно, как и впредыдущем случае, но без полей для выбора диапазона.
Послезавершения работы мастера по одному из вышеперечисленных путей, в книгу будетвставлена сводная таблица, с выбранными значениями строк истолбцов с просуммированными значениями полей, внесенных в область«данные» на рисунке 1.
Основные элементы сводных таблиц
Преждечем продолжить эту работу, я думаю, что будет не лишним рассказать из какихвсе-таки элементов состоят сводные таблицы, и для этого рассмотрим пример на рисунке 2.
Поле страницы — это поле исходного списка илитаблицы, помещенное в область страничной ориентации сводной таблицы. В этомпримере «Область» является полем страницы, которое можно использовать дляподведения итогов по регионам. При указании другого элемента поля страницыпроисходит пересчет сводной таблицы для отображения итогов, связанных с этимэлементом.
Значение поля страницы — элементы поля страницы объединяютзаписи или значения поля или столбца исходного списка (таблицы). Другимисловами это переключением этого поля, фактически каждый раз происходит переходк новому источнику данных по«новому региону».
Поля строки — это поля исходного списка илитаблицы, помещенные в область строчной ориентации сводной таблицы. В этомпримере «Продукты» и «Продавец» являются полями строки. Внутренние поля строки(например «Продавец») в точности соответствуют области данных; внешние полястроки (например «Продукты») группируют внутренние. Таким образом здесьпрослеживается определённое правило, каждый элемент (поле) более высокогоуровня как бы уконкречивают все поля более низкого уровня: параметр«продукт» ограничивает все данные под ним — «продавцы продуктов»,«данные по продажи продуктов этими продавцами».
Поле столбца - это поле исходного списка илитаблицы, помещенное в область столбцов. В этом примере «Кварталы» являетсяполем столбца, включающим два элемента поля «КВ2» и «КВ3». Внутренние поля столбцовсодержат элементы, соответствующие области данных; внешние поля столбцоврасполагаются выше внутренних (в примере показано только одно поле столбца).
Областью данных называется часть сводной таблицы,содержащая итоговые данные. В ячейках области данных отображаются итоги дляэлементов полей строки или столбца. Значения в каждой ячейке области данныхсоответствуют исходным данным. В этом примере в ячейке C6 суммируются всезаписи исходных данных, содержащие одинаковое название продукта, распространителяи определенный квартал («Мясо», «ТОО Мясторг» и «КВ2»).
Элементы поля — это подкатегории поля своднойтаблицы. В данном примере значения «Мясо» и «Дары моря» являются элементамиполя в поле «Продукты». Элементы поля представляют записи в поле или столбцеисходных данных. Элементы поля появляются в виде заголовков строк или столбцов,а также в раскрывающемся списке для полей страниц.
Поле данных — это поле исходного списка илитаблицы, содержащее данные. В этом примере поле «Сумма по Заказ» является полемданных, подводящим итоги исходных данных в поле или столбце «Сумма заказа». Вполе данных обычно подводятся итоги группы чисел (например, статистика иликоличество сбыта), хотя текущие данные могут быть и текстовыми. По умолчанию всводной таблице подведение итогов текстовых данных производится с помощьюитоговой функции «Кол-во значений», а числовых данных — с помощью итоговойфункции «Сумма».
Изменение структуры сводной таблицы
Внешний вид сводной таблицы можноизменить непосредственно на листе, перетаскивая названия кнопок полей илиэлементов полей. Так же можно поменять порядок расположения элементов в поле,для чего необходимо выделить название элемента, а затем установить указатель награницу ячейки. Когда указатель примет вид стрелки, перетащить ячейку поля нановое место. Чтобы удалить поле, перетащите кнопку поля за пределы областисведения. Удаление поля приведет к скрытию в сводной таблице всех зависимых отнего величин, но не повлияет на исходные данные. Если же необходимоиспользовать все предусмотренные средства структурирования сводной таблицы, илиесли в текущую таблицу не были ранее включены все поля исходных данных, следуетвоспользоваться мастер сводных таблиц. Если же сводная таблица содержит большуюгруппу полей страницы, то их можно разместить в строках или столбцах.
Кроме того, нужно отметить, что изменениеструктуры сводной таблицы не затрагивает исходные данные.Детальные данные сводной таблицы и выполняемые над ними действия
Детальныеданные являются подкатегорией сводной таблицы.Эти элементы сводной таблицы являютсяуникальными элементами таблицы или списка. Например, поле «Месяц» можетсодержать названия месяцев: «Январь», «Февраль» и т.д. Над детальными даннымисводной таблицы можно выполнить следующие действия:
· отобразитьили скрыть текущие детали элемента поля или ячейки в области данных своднойтаблицы;
· выделитьэлементы, которые необходимо отобразить или скрыть в поле сводной таблицы;
· отобразитьмаксимальные или минимальные элементы в поле сводной таблицы. Например, 10крупных сделок или пять мелких сделок.
· Крометого, можно ограничить число отображаемых ячеек в области данных для текущихданных.Отображение или скрытие детальных данных сводной таблицы
Длятого чтобы отобразить детальные данные,которые были скрыты ранее, или же скрытьимеющиеся нужно выделить элемент поля, детальные данные которого необходимоскрыть или показать.
Далее необходимо нажать на соответствующуюкнопку «Отобразить детали» или «Скрыть детали» (см. рисуноксправа), которые расположены на панелиинструментов Сводные таблицы, или же в меню «Данные» в пункте«группа и структура». При появлении диалогового окна, выберите поле,для которого необходимо показать детали.
Примечание:
Еслидля ячейки сводной таблицы необходимо отобразить детали текущего источникаданных, на панели инструментов «Сводные таблицы» выберите команду«Выделить» в меню «Сводная таблица» и проверьте, что кнопка«Разрешить выделение» ненажата. Далее необходимо установить указатель на ячейку и дваждынажать кнопку мыши. Детальные данные будут размещены на отдельном листе книги. Если был указанэлемент, не имеющий детальных данных, появляется возможность вставить в своднуютаблицу поле строки или поле столбца из тех полей исходных данных, которые ещене включены в сводную таблицу. В появившемся списке выберите необходимое поле.Отображение или скрытие элементов поля сводной таблицы
Если же необходимо отобразить илискрыть элемент поля, то для этого необходимо, во-первых, выделить поле, в котором необходимо отобразить или скрытьэлементы. Во-вторых, нужно нажать кнопку«Поле» сводной таблицы на панели инструментов «Сводныетаблицы» (см. рисунок). Далее, впоявившемся окне, нужно выбиратьэлементы, которые необходимо скрыть или отобразить, в списке «Скрыть элементы».
Чтобыотобразить или скрыть пустые элементы поля исходных данных, нужно в окне«вычисление поле сводной таблицы» установить или снять флажок«Отображать пустые элементы».
Примечание:
Основныеи промежуточные итоги сводной таблицы будут пересчитаны автоматически, и, крометого, скрытие данных сводной таблицы неповлияет на исходные данные.Отображение максимальных или минимальных элементов в полесводной таблицы
Дляотображения максимальных или минимальных элементов необходимо выделить поле, вкотором в дальнейшем будут показаны элементы.
Послеэтого нужно нажать кнопку «Поле сводной таблицы» на панели инструментов «Сводныетаблицы». В появившемся окне всписке необходимо выбрать пункт «минимуму» или «максимум»после чего нажать кнопку «Далее».
После этого, в новом окне установитьпереключатель «Параметры отображения» в положение«вручную», а так же выберите варианты наибольших или наименьших враскрывающемся списке отображать, а также число отображаемых элементов.
Послечего выберите поле, содержащее данные, которое необходимо использовать вкачестве критерия для определения максимальных или минимальных элементов, враскрывающемся списке с помощью поля.
Примечания:
· Приобновлении сводной таблицы, изменении ее структуры или отображении другихэлементов поля страницы происходит пересчет отображения максимальных илиминимальных элементов.
· Еслизадано отображение максимальных или минимальных элементов поля, остальныеэлементы поля скрываются. Чтобы отобразить все элементы поля, необходимо установитьпереключатель Параметры отображения в положение вручную.Отключение доступа к детальным сведениям сводной таблицы
Еслиустановить указатель на ячейку области данных сводной таблицы и дважды нажатькнопку мыши, отобразится итоговый список исходных данных для этой ячейки. Иногда бывает полезно ограничить доступк подробным сведениям, и для выполнение этой процедуры необходимо проделатьследующее:
· Нужно выделить ячейку в сводной таблице, длякоторой будут введены ограничения.
· Выберитекоманду «Параметры» находящуюся в меню «Сводная таблица»,панель инструментов «Сводные таблицы».
· Снимитефлажок «развертывание разрешено» в группе «Данные».Группировка и разгруппировка данных в сводной таблице
Всводной таблице можно группировать даты, времена, числа и выделенные элементы.Например, можно объединить месяцы, чтобы подвести итоги за квартал. Есливременно изменить ориентацию поля страницы на ориентацию по строке или постолбцу, можно сгруппировать элементы этого поля и затем снова изменить ориентациюна ориентацию по странице.
Привыполнении операции группировки и разгруппировки данных сводной таблицыавтоматически пересчитываются все сводные таблицы, созданные на основе текущей.Если элементы поля сгруппированы, добавление вычисляемых элементов в поленевозможно.Группировка и разгруппировка элементов поля сводной таблицы
Дляосуществления группировки иразгруппировки элементов нужно выделить группируемые элементы.
Далеенажать кнопку «Сгруппировать» на панели инструментов «Сводные таблицы»или в меню «Данные» в пункте «Группа и структура» чтобы создать новый элемент, объединяющийвыделенные элементы. Новый элемент добавится в группируемое поле своднойтаблицы.
Примечания:
· Чтобыразгруппировать элементы, выделите группу, а затем нажмите кнопку«Разгруппировать» на панели инструментов «Сводныетаблицы» или в меню «Данные» в пункте «Группа и структура». Привыполнении команды разгруппировки чисел, дат или времен расформировывается всягруппа; расформировать отдельный элемент группы невозможно.
· Еслиразгруппировывается последний элемент в поле строки или в поле столбца,групповое поле удаляется из сводной таблицы.Группировка и разгруппировка чисел в сводной таблице
Дляосуществления группировки иразгруппировки чисел выделите число в поле, в котором следует сгруппироватьэлементы. Далее нажмите кнопку «Сгруппировать» на панели инструментов Сводные таблицы или вменю «Данные» в пункте «Группаи структура». В поле «Начиная с» введите начальное значение,если это необходимо, а в поле «по» введите конечное значение, еслиэто необходимо. В поле с шагом необходимо ввести значение периода, по которомубудут создаваться группы чисел.
Примечание:
Чтобы разгруппировать числа, выделите группу,а затем нажмите кнопку «Разгруппировать» на панели инструментов «Сводныетаблицы». При выполнении команды разгруппировки чисел, дат или временразгруппировывается вся группа; разгруппировать отдельный элемент группыневозможно.Группировка и разгруппировка дат или времен в сводной таблице
Для осуществления группировки и разгруппировки даты иливремени, выделите дату или время в поле, в котором следует сгруппироватьэлементы. Далее нажмите кнопку «Сгруппировать» на панели инструментов Сводные таблицы или вменю «Данные» в пункте «Группа и структура». В поле «начиная с» введитеначальное значение, а в поле «по» конечное значение, если этонеобходимо. В списке «По» выберите один или несколько временныхинтервалов для задания шага, с которым будут создаваться группы.
Если вполе «По» выделен единственный интервал «Дни», введите шаггруппировки в поле «Количество дней». Например, введите 7 в поле«Количество дней», чтобы сгруппировать элементы по неделям.
Примечание:
Чтобыразгруппировать даты и времена, выделите группу, а затем нажмите кнопку «Разгруппировать» на панели инструментов Сводные таблицы. Привыполнении команды разгруппировки поля со значениями даты или времениразгруппировываются все группы поля; разгруппировать один элемент группы невозможно.Группировка и разгруппировка элементов поля страницы своднойтаблицы
Еслисводная таблица создана из внешнего источника данных, проверьте, что в полестраницы осуществляется выборка внешних данных для всех элементов поляодновременно.
Еслиэто происходит, то перетащите поле страницы в область ориентации по строкам илистолбцам, чтобы элементы поля страницы стали заголовками в поле строки илистолбца. Далее следует выделить группируемые элементы.
Послеэтого нажмите кнопку" Сгруппировать" на панели инструментов «Сводные таблицы», или в меню«Данные» в пункте «Группаи структура». И, наконец, перетащите поле назад в область ориентации постраницам.
Примечание:
Чтобыразгруппировать элементы поля страницы, перетащите поле страницы в областьориентации по строкам или столбцам, выделите группу, а затем нажмите кнопку«Разгруппировать» на панелиинструментов Сводные таблицы.Обновление или изменение источника данных сводной таблицы
Впроцессе работы иногда возникает такая ситуация, когда появляются новые данные,которые необходимо вставить в создаваемый документ, и поэтому существует несколько способов управленияпроцессом обновления и изменения данных сво