Лабораторная работа №9 Тема : Сводные таблицы




Скачать 145.04 Kb.
НазваниеЛабораторная работа №9 Тема : Сводные таблицы
Дата публикации11.05.2014
Размер145.04 Kb.
ТипЛабораторная работа
litcey.ru > Право > Лабораторная работа

ЛАБОРАТОРНАЯ РАБОТА № 9


Тема: Сводные таблицы
ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ

СВОДНЫЕ ТАБЛИЦЫ


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

Мастер сводных таблиц осуществляет построение сводной таблицы в несколько этапов:

Этап 1. Указание вида источника сводной таблицы:

         использование списка (базы данных Excel);

         использование внешнего источника данных;

         использование нескольких диапазонов консолидации;

         использование данных из другой сводной таблицы.

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

Этап 2. Указание диапазона ячеек, содержащего исходные данные. Список (база данных Excel) должен обязательно содержать имена полей (столбцов). Полное имя диапазона ячеек записывается в виде
^

[имя_книги]имя_листа!диапазон ячеек


 

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

Этап 3 . Построение макета сводной таблицы. Структура сводной таблицы состоит из следующих областей, определяемых в макете (рис.3.47.):



Рис.3.47. Схема макета сводной таблицы

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

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

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

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

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

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

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



а



б
^

Рис. 3.48. Диалоговое окно «Вычисление поля сводной таблицы»


 

Для этого следует установить курсор на настраиваемое поле и дважды нажать левую кнопку мыши длявызова диалогового окна «Вычисление поля сводной таблицы» (рис. 3,48, а), в котором можно переименовать поле, изменить операцию, производимую с данными поля, или изменить формат представления числа.

Кнопка «Дополнительно» вызывает панель Дополнительные вычисления для выбо­ра функций, список которых приведен в табл. 3.5. При использовании функции сравнения (Отличие, Доля, Приведенное отличие) выбирается (рис. 3,48, б) Поле и Элемент, с которым будет производиться сравнение. Список Поле содержит поля сводной таблицы, с которым связаны базовые данные для пользовательского вычисления. Список Элемент содержит значения поля, участвующего в пользовательском вычислении.
^

Таблица 3.4. Виды дополнительных функций над полем в области данных


Функция

 

Результат

Отличие

 

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

Доля

 

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

Приведенное отличие

 

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

С нарастающим итогом в поле

 

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

Доля от суммы по строке

 

Значения ячеек области данных отображаются в процентах от итога строки

Доля от суммы по столбцу

Значения ячеек области данных отображаются в процентах от итога столбца

Доля от общей суммы

Значения ячеек области данных отображаются в процентах от общего итога сводной таблицы

Индекс 

При определении значений ячеек области данных используется следующий алгоритм: ((Значение в ячейке) * (Общий итог)) / ((Итог строки) * (Итог столбца))

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


^

Рис. 3.49. Диалоговое окно «Мастер сводных таблиц» на 4-м этапе


 

Кнопка <Параметры> в диалоговом окне 4-го шага вызывает диалоговое окно «Параметры сводной таблицы», в котором устанавливается вариант вывода информации в сводной таблице:

общая сумма по столбцам — внизу сводной таблицы выводятся, общие итоги по столбцам;

общая сумма по строкам — в сводной таблице формируется итоговый столбец;

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

автоформат — позволяет форматировать сводную таблицу с помощью команды ^ Формат, Автоформат и другие параметры.

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

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

Задание 1

Работа со справочниками



Справочник




  • Откройте новую рабочую книгу и сохраните ее в папке студента в папке Лаб9.

  • Создайте рабочий лист с именем “Нормы”, содержащий следующую справочную таблицу :




Примечание: при формировании справочных таблиц следует помнить:

  1. ключевой” реквизит (тот, по которому будет выбираться информация из справочника) всегда должен располагаться в первой графе (или первой строке*) справочной таблицы;

  2. строки (столбцы*) справочной таблицы должны быть упорядочены в порядке возрастания “ключевого” реквизита.


* справедливо для “вертикальных” справочников.













Расчетная

таблица

  • На другом рабочем листе с именем “Амортизация”, создайте следующую расчетную таблицу:








Расчет амортизационных отчислений

на 30/09/95 г.











Инв

Наименование

Шифр

Норма

Балансов.

Дата ввода в

Амортизац.

Остат-я







ном.







аморт

стоимость

эксплуатац.

за квартал

стоим-ть







4

45

Автомобиль

50418




40250000

12/03/95













5

523

Пиш.машинка

70000




852000

15/12/93













6

212

Набор мебели

70003




4200000

21/08/95













7

213

Набор мебели

70003




4200000

21/08/95













8

395

Кофеварка

70005




210000

03/12/94













9

11

Калькулятор

48008




265975

22/05/94













10

12

Калькулятор

48008




265975

22/05/94













11

13

Калькулятор

48008




265975

22//05/94













12

55

Холодильник

45800




1356264

30/11/94













13

123

Компьютер

48008




7215200

12/12/94













14

124

Принтер

48008




1105000

12/01/95



















  • Введите расчетные формулы.





Выбор из справочника

Примечание: нормы амортизации должны выбираться из справочной таблицы “Нормы” с помощью функции ВПР (группа функций “Просмотр и ссылки”). В общем виде функция можно записать так:
= ВПР(адрес ячейки, содержащей “ключевое” значение; диапазон справочной таблицы; порядковый номер графы справочной таблицы, содержащей искомое значение)
!!! Важно !!! При задании диапазона справочной таблицы следует использовать

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

Следует включать в диапазон только содержательную часть таблицы (т.е. только данные без “шапки”).

Включите в диапазон дополнительно пустую строку в конце справочной таблицы - это избавит Вас от необходимости корректировать расчетные формулы всякий раз, когда справочник будет пополняться. Вам останется только следить за тем, чтобы новые строки вставлялись перед пустой строкой.
Если, например, предварительно обозначить диапазон ‘Нормы‘ !А3:D14 как “справ“, то в нашем случае формула для выборки нормы амортизации из справочника будет иметь вид (например для ячейки D4) :
=ВПР(C4;справ;3) ,
где С4 - “ключевое” значение для которого выбирается соответствующая норма амортизации; 3 - номер графы справочной таблицы, содержащей годовую норму амортизации.
^ Вам остается только скопировать формулу в другие строки столбца D.








Расчет амортизации

­за квартал


Амортизация основных средств рассчитывается исходя из балансовой стоимости средства, срока его эксплуатации и годовой нормы амортизации этого основного средства. При этом под сроком эксплуатации понимают количество полных месяцев, прошедших со дня ввода его в эксплуатацию до даты, на которую выполняется расчет.
Вам будет значительно проще сформировать формулу, если Вы вставите дополнительную колонку перед графой “Амортизация за квартал”. Назовем ее “Срок эксплуатации, мес.”
Если дата, на которую выполняется расчет амортизации внесена в ячейку D2, а срок ввода в эксплуатацию содержится в ячейке F4, то количество полных месяцев эксплуатации основного средства можно определить так:
= ОТБР(ДНЕЙ360(F4;$D$2)/30),
где: функция ДНЕЙ360 ( группа функций “Дата и время”) позволяет определить период между двумя датами в расчете на условный год, состоящий из 360 дней, иначе говоря игнорирует 31 число для “длинных” месяцев;

функция ОТБР ( группа функций “Мат. и тригонометрия”) позволит нам отбросить остаток от деления на 30, получив в результате количество полных месяцев эксплуатации.
^ Формула расчета квартальной амортизации для первой строки таблицы будет иметь вид:
= ЕСЛИ(G4>2;Е4*D4/12*3/100;ЕСЛИ(G4>1;Е4*D4/12*2/100;Е4*D4/12/100))
Скопируйте готовую формулу в другие строки таблицы.






Расчет остаточной стоимости


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

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



Задание 2

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



Создание




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

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







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

- вызовите Мастер сводных таблиц, выполнив команду меню ДАННЫЕ, СВОДНАЯ ТАБЛИЦА;

- в средней части открывшегося диалога первого шага Мастера сводных таблиц укажите где находятся исходные данные, которые будут использованы для построения сводной таблицы Из четырех предложенных в диалоге вариантов источников данных следует выбрать первый - В СПИСКЕ ИЛИ БАЗА ДАННЫХ Microsoft Excel;

- перейдите к следующему шагу построения сводной таблицы щелкнув по кнопке “Шаг >“ ;

- выделите область исходных данных, которые будут использованы для построения сводной таблицы ( в нашем случае это таблица, содержащаяся на рабочем листе “Амортизация”). Выделение можно сделать одним из следующих способов:

- выделить любую ячейку внутри области исходных данных перед вызовом Мастера сводных таблиц;

- отметить область в то время, когда на экране открыт диалог “Шаг 2 из 4” Мастера сводных таблиц;

- ввести диапазон исходных данных в специальном окне ввода;

- перейдите к следующему шагу построения сводной таблицы щелкнув по кнопке “Шаг >“

- в открывшемся диалоге “Шаг 3 из 4” Мастера сводных таблиц определяется внешний вод сводной таблицы. В правой части диалога находятся кнопки с именами столбцов исходных данных. с помощью мыши “перетащите” имя “Шифр” в зону “Строка” макета будущей сводной таблицы. “Перетащите” имена полей “Остаточная стоимость” и “Балансовая стоимость” в зону “Данные” макета сводной таблицы.;

- перейдите к следующему шагу построения сводной таблицы щелкнув по кнопке “Шаг >“;

- в последнем диалоге можно задать ячейку ,с которой будет начинаться сводная таблица и ее имя. Перейдите на новый рабочий лист (например, Лист4, щелкнув мышью на его ярлычке. В поле ввода диалога “Шаг 4 из 4” Мастера сводных таблиц появится - “Лист4!А1” . Закройте диалог щелчком на кнопке “Закончить”.





  • Добавьте в сводную таблицу графу “Степень износа, %”.


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





  • Для лучшего восприятия данных сводной таблицы добавьте в нее еще одну графу - “Наименование группы основных средств”


Примечание эти наименования у Вас хранятся на рабочем листе “Нормы” активной рабочей таблицы. Для того, чтобы показать их в вашей сводной таблице можно воспользоваться уже знакомой Вам функцией работы со справочниками ВПР (группа функций “Просмотр и ссылка”) Однако будьте внимательны !!! В сводной таблице значения шифров основных средств будут показаны как текстовые данные, а следовательно, прежде чем использовать их в качестве параметра “искомое значение” функции ВПР шифры основных средств сводной таблицы необходимо обработать функцией ЗНАЧЕН из группы функций “Текстовые”.





Графическая

интерпретация

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


Примечание Вы можете формировать сводную таблицу выполняя уже знакомую Вам последовательность шагов. В зону “Данные” макета сводной таблицы “перетащите” название поля “Срок эксплуатации в месяцах”. Программа сформирует сводную таблицу, содержащую суммарный срок эксплуатации всех основных средств, имеющих одинаковые шифры. Теперь добейтесь, чтобы суммарный срок эксплуатации был заменен на средний. Активизируйте любую ячейку, содержащую суммарный срок эксплуатации основных средств. Далее в Вашем распоряжении команда меню “ДАННЫЕ. ПОЛЕ_СВОДНОЙ_ТАБЛИЦЫ. В появившемся диалоге в поле выбора “Суммирующая функция” выберите “Средн” и закройте диалог кнопкой ОК.






  • Проиллюстрируйте данные полученной сводной таблицы на графике.


Примечание в вашем распоряжении Мастер диаграмм.


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

КОНТРОЛЬНЫЕ ВОПРОСЫ

    1. ?



Похожие:

Лабораторная работа №9 Тема : Сводные таблицы iconЛабораторная работа n тема: Поиск и отбор данных по запросам пользователей...
Цель проведения. В результате выполнения лабораторной работы студент должен приобрести навыки работы с расширенным фильтром, научиться...
Лабораторная работа №9 Тема : Сводные таблицы iconЛабораторная работа №1. Тема «Таблицы и графики в статистике»
Вывод: Лабораторная работа №1 была проведена с целью освоения темы диаграммы и графики. В ходе лабораторной я научилась строить графики...
Лабораторная работа №9 Тема : Сводные таблицы iconЛабораторная работа №3. Создание таблиц и списков
Большинство документов имеют табличную структуру данных. Текстовый редактор Microsoft Word позволяет создавать стандартные или рисованные...
Лабораторная работа №9 Тема : Сводные таблицы iconЛабораторная работа №7-9 Тема
Тема: Создание в программе CodeGear rad studio (C++Builder) клиентского приложения по технологии dbExpress для клиент-серверной субд...
Лабораторная работа №9 Тема : Сводные таблицы iconЛабораторная работа №6 Тема: Двумерные массивы

Лабораторная работа №9 Тема : Сводные таблицы iconЛабораторная работа №1. Тема: Линейная структура: вычисления формулы

Лабораторная работа №9 Тема : Сводные таблицы iconЛабораторная работа №2 Тема : Условный оператор. Условные операции

Лабораторная работа №9 Тема : Сводные таблицы iconЛабораторная работа №5. Создание сносок, ссылок и оглавлений
Курсор автоматически перейдет в конец страницы. Напишите текст сноски: «Моя первая лабораторная работа word»
Лабораторная работа №9 Тема : Сводные таблицы iconЛабораторная работа №1 Тема : Постановка задачи
В. А. Куликова, студент гр. Ис-2о Проверил: Е. С. Вяткина, старший преподаватель
Лабораторная работа №9 Тема : Сводные таблицы iconЛабораторная работа №1 Тема: Работа с виртуальными машинами
Научиться устанавливать и работать с виртуальными машинами в среде Windows xp на основе таких программ как Vksaver Workstation 4...
Вы можете разместить ссылку на наш сайт:
Школьные материалы


При копировании материала укажите ссылку © 2013
контакты
litcey.ru
Главная страница