Оглавление

 

Лабораторная работа № 7. 7

Организация межтабличных связей. 7

Лабораторная работа № 8. 16

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

Лабораторная работа № 9. 31

Консолидация данных. 31

Лабораторная работа № 10. 39

Создание и обработка таблицы «Расчёт стоимости тура». 39

Лабораторная работа № 11. 43

Создание и обработка таблицы «Расчёт первоначального взноса при покупке автомобиля в кредит. 43

Лабораторная работа № 12. 46

Создание и обработка таблицы.. 46

«Плата за проживание в гостинице». 46

 

 

 

 

 

 

Лабораторная работа № 6.

Разработка и расчёт таблиц

«Ведомость поступления материалов на склад»,

«Обмен валюты».

Практическое задание

I.        Создать таблицу «Ведомость поступления материалов на склад»

 

Ведомость поступления материалов на склад

за февраль 2010 г.

№ п/п

Дата

поступления

Наимено-

вание

Единица измерения

Номер счета

Кол.

Цена,

руб.

Сумма

1

03.02.03

электр.лампы

шт.

10-1

20

5

?

2

03.02.03

веники

шт.

10-9

3

60

?

3

05.02.03

чистящ.ср-ва

шт.

10-9

13

28

?

4

07.02.03

электр.лампы -500вт.

шт.

10-1

8

11

?

5

07.02.03

изолента

шт.

10-9

6

7

?

6

08.02.03

шайба Ф50

шт.

10-1

189

1

?

7

08.02.03

шпильки М24

шт.

10-1

10

1,2

?

8

10.02.03

выключатель

шт.

10-1

3

58

?

9

11.02.03

рукавицы

пар

10-9

30

67

?

 

ИТОГО

 

 

 

 

 

?

 

1.      Выполните сортировку таблицы по полю Номер счета.

2.      Получитепромежуточные итоги по счетам.

·          Просмотреть все уровни детализации списка данных, щелкнув по символу строки уровня 3.

·          Просмотреть только промежуточные и общие итоги.

·          Просмотреть только общие итоги.

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

4.      Защитите от изменений вычисляемые ячейки.

5.      Сохраните рабочую книгу под именем Оборотная Ведомость в своей папке  в папке «Мои документы».

6.       Завершите работу с Excel.

 

II.    Создать таблицу «Обмен валют»

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

Обмен валют

 

Номер банка

Адрес банка

Устав-ной

капитал

(т. руб.)

Курс покупки валюты

Курс продажи валют

Разница (продажа/

покупка)

Отклонение от среднего (покупка)

Отклонение от среднего (продажа)

Число клиен-тов

1

Банк 1

Москва

3000000

23,5

25

 

 

 

500

2

Банк 2

Астрахань

3500000

23

25,5

 

 

 

120

3

Банк 3

Москва

3700000

23,6

24,9

 

 

 

600

4

Банк 4

Ярославль

4000000

23,4

25,3

 

 

 

30

5

Банк 5

Ростов/на-Дону

2900000

23,5

25

 

 

 

185

6

Банк 6

Ростов/на-Дону

3100000

23,5

25,1

 

 

 

60

7

Банк 7

Минск

4000000

23,7

25,3

 

 

 

100

8

Банк 8

Ростов/на-Дону

4000000

23,7

25,2

 

 

 

500

 

 

 

 

 

 

 

 

 

Итого

 

 

 

 

 

 

 

 

Среднее значение

 

 

 

 

 

 

 

 

Максимальное значение

 

 

 

 

 

 

 

 

Минимальное значение

 

 

 

 

 

 

 

 

 

1.      Постройте диаграммы, используя «Мастер диаграмм» (не менее трёх).

2.      Защитите от изменений вычисляемые ячейки.

3.      Сохраните рабочую книгу под именем Обмен валюты в своей папке  в папке «Мои документы».

4.       Завершите работу с Excel.

 

ОБЪЕДИНЕНИЕ ДАННЫХ.

При работе с электронными таблицами часто возникает необходимость их объединения. Среди инструментов объединения электронных таблицотметим:

·         организацию межтабличных связей;

·         создание сводных таблиц;

·         консолидацию электронных таблиц или их частей.

 

Лабораторная работа № 7.

Организация межтабличных связей.

Краткие теоретические сведения.

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

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

 

Практическое задание.

На листе 1 книги Excelзанести данные об объёме продаж по группам товаров для некоторого магазина игрушек.

На листе 2 представить данные об издержках продаж по заданным группам товаров.

На листе 3 необходимо получить данные о величине прибыли го группам товаров.

Работу сопроводить диаграммами.

 

1.  Переименуйте Лист1 в Объём продаж.

2.  Создайте таблицу «Объёмы продаж».

Объёмы продаж (руб.)

 

Группы товаров

январь

февраль

март

1 квартал

апрель

май

июнь

2 квартал

1-е

полугод.

доля,

%

деревянные

 

 

 

?

 

 

 

?

?

?

куклы

 

 

 

?

 

 

 

?

?

?

игры

 

 

 

?

 

 

 

?

?

?

конструкторы

 

 

 

?

 

 

 

?

?

?

мягкие

 

 

 

?

 

 

 

?

?

?

итого

 

 

 

?

 

 

 

?

?

100

 

Примечание. ? - ячейки, значения которых необходимо рассчитать.

Значения объёмов продаж для отдельных месяцев представлены на рис. 6.

 

 

 

 

 

 

 

Рис.6 Объёмы продаж для отдельных месяцев

 

3.      Представьте в графическом виде доли отдельных групп товаров в общем объеме продаж за 1-е полугодие:

 

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

При копировании листа в копии дублируются все данные, пара­метры форматирования и формулы. Объясним выполнение опе­рации копирования листа на примере.

4. Щелкните на ярлычке листа Объем продаж правой кнопкой мыши и выберите команду Переместить/скопировать.

5. На экране будет открыто окно диалога Переместить или ско­пировать. В поле Перед листом щелкните на элементе Лист2.

6. Установите флажок параметра Создавать копию.

7. Закройте окно диалога, нажав кнопку ОК.

В рабочую книгу будет вставлен лист Объем продаж (2)          

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

8. Щелкните дважды на ярлычке листа Объем продаж(2) для присвоения листу другого имени.

9. Введите имя Издержки.

10. После изменения имени листа замените также название таблицы на Издержки продаж (руб.).

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

12. Введите значения издержек (рис.7)

 

 

 

 

 

 

 

Рис. 7. Значения издержек

 

13. Представьте в графическом виде доли отдельных групп товаров в общем объеме издержек за 1-е полугодие:

 

14. Снова скопируйте рабочий лист Объем продаж и присвойте копии имя Прибыль. Замените название таблицы на Прибыль продаж (руб.) Удалите в листе Прибыль исходные (входные) данные – формулы не удалять.

15. В этом листе данные вводить не нужно — мы определим значения прибыли, вычтя из объема про­даж издержки. Поместите указатель ячейки на ячейке В4 (деревянные-январь)и введите знак равенства.

16.  Перейдите на лист Объем продаж и щелкните на ячейке В4 (деревянные-январь).

17. Введите знак  «минус»  и перейдите теперь в рабочий лист Издержки. Щелкните в нем также на ячейке В4 (деревянные-январь).

18.  Нажмите клавишу [Enter]

В ячейке В4 рабочего листа Прибыль будет отображен резуль­тат — 2466. В строке формул отображена формула:

='Объем продаж'!В4-Издержки!В4

19. Для определения результатов в других ячейках скопируйте формулу. 

20. Представьте в графическом виде доли отдельных групп товаров в общем объеме прибыли за 1-е полугодие.

21. Сохраните рабочую книгу под именем Магазинигрушек в своей папке  в папке «Мои документы».

22.Завершите работу с Excel.

 

Контрольное задание.

Транспортное агентство «Быстрее ветра» предоставляет услуги по перевозке грузов по различным маршрутам.

Имеются справочные данные:

а) технические характеристики каждого автомобиля

 

п/п

Марка

автомобиля

Расход

топлива на

100 км (л)

Грузоподъёмность

(т)

1

ЗИЛ

42

7

2

КАМАЗ

45

16

3

МАЗ

53

12

 

б) маршрут (номер рейса), протяженность рейса

 

п/п

Номер рейса

Протяжённость маршрута (км)

1

А 112

420

2

В 447

310

3

М 023

225

4

С 431

250

 

в) данные о выполненных маршрутах по каждому водителю за неделю с указанием количества рейсов

 

п/п

ФИО водителя

Марка автомобиля

Номер рейса

Количество выполненных рейсов

1

Иванов И.И

КАМАЗ

А 112

4

2

Петров П.П

ЗИЛ

С 431

3

3

Сидоров С.С

МАЗ

А 112

5

4

Волков В.В

МАЗ

М 023

7

5

Михайлов М.М

ЗИЛ

В 447

2

6

Яковлев Я.Я

КАМАЗ

С 431

8

7

Зверев З.З

КАМАЗ

В 447

4

8

Окунев О.О

МАЗ

С 431

5

9

Смирнов С.С

ЗИЛ

А 112

6

10

Лебедев Л.Л

КАМАЗ

М 023

7

 

Рассчитать:

·     количество израсходованного топлива каждым водителем за неделю;

·     всего перевезенного груза каждым водителем за неделю;

·     визуализировать полученные расчеты с помощью диаграмм. 

 

Алгоритм решения задачи

1. Запустить табличный процессор Excel

2. Создать книгу с именем «Быстрый ветер»

3. Лист 1 переименовать в лист с названием Характеристики Автомобилей (Хар.авт.).

На этом листе создать таблицу, которая будет отражать сведения о марке автомобиля, расходе топлива на 100 км и грузоподъемности этого автомобиля (таблица из справочных данных под буквой а)

4. Лист 2 переименовать в лист с названием Рейсы.

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

5. Лист 3 переименовать в лист с названием Сведения.

На этом листе создать таблицу вида:

Данные о выполненных маршрутах по каждому водителю за неделю

 

№ п/п

ФИО

водителя

Марка

авто-

мобиля

Номер

рейса

Коли-

чество

выпол-

ненных

рейсов

Протя-

женность

маршрута

(км)

Расход

топлива

на

100 км

(л)

Израсхо-

довано

топлива

за неделю

(л)

Грузо-

подъемность

автомашины

(т)

Вес

перевезенного

груза (т)

1

Иванов И.И

КАМАЗ

А 112

4

 

 

 

 

 

2

Петров П.П

ЗИЛ

С 431

3

 

 

 

 

 

3

Сидоров С.С

МАЗ

А 112

5

 

 

 

 

 

4

Волков В.В

МАЗ

М 023

7

 

 

 

 

 

5

Михайлов М.М

ЗИЛ

В 447

2

 

 

 

 

 

6

Яковлев Я.Я

КАМАЗ

С 431

8

 

 

 

 

 

7

Зверев З.З

КАМАЗ

В 447

4

 

 

 

 

 

8

Окунев О.О

МАЗ

С 431

5

 

 

 

 

 

9

Смирнов С.С

ЗИЛ

А 112

6

 

 

 

 

 

10

Лебедев Л.Л

КАМАЗ

М 023

7

 

 

 

 

 

Итого

 

 

 

 

 

 

 

 

Среднее значение

 

 

 

 

 

 

 

 

 

Заполнить таблицу:

·       в колонке Протяженность маршрута (км) связать данные листа Рейсы в зависимости от номера рейса, выполненного каждым водителем

·       в колонке Расход топлива на 100 км (л) связать данные листа Характеристики Автомобилей в зависимости от марки автомобиля

·       в колонке Израсходовано топлива за неделю (л) произвести расчет в зависимости от всего пройденного расстояния каждым водителем с учетом расхода топлива на 100 км

·       в колонке Грузоподъемность автомашины (т) связать данные листа Характеристики Автомобилей в зависимости от марки автомобиля по каждому водителю

·       в колонке Вес перевезенного груза (т) произвести расчет в зависимости от количества выполненных рейсов каждым водителем с учетом грузоподъемности автомашины

6. Создать Лист 4 с названием Ведомость, который будет содержать таблицу с данными по расходу горючего и весом перевезенного груза за отчетный период по каждому водителю, т.е. таблицу вида:

Ведомость расхода горючего и веса перевезенного груза за неделю

 

№ п/п

ФИО

водителя

Номер

рейса

Выполнено рейсов

Израсходовано топлива (л)

Вес перевезенного груза (т)

1

Иванов И.И

А 112

 

 

 

2

Петров П.П

С 431

 

 

 

3

Сидоров С.С

А 112

 

 

 

4

Волков В.В

М 023

 

 

 

5

Михайлов М.М

В 447

 

 

 

6

Яковлев Я.Я

С 431

 

 

 

7

Зверев З.З

В 447

 

 

 

8

Окунев О.О

С 431

 

 

 

9

Смирнов С.С

А 112

 

 

 

10

Лебедев Л.Л

М 023

 

 

 

Итого

 

 

 

 

 

Заполнить таблицу:

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

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

·         в колонке Израсходовано топлива (л) связать данные с листом Сведения по каждому водителю

·         в колонке Вес перевезенного груза (т) связать данные с листом Сведения по каждому водителю

7. Построить две диаграммы на отдельных листах:

а) Гистограмму Расход топлива за неделю, в которой отражаются данные колонки Израсходовано топлива (л) по каждому водителю таблицы листа Ведомость

 

 

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

 

 

Лабораторная работа № 8.

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

Краткие теоретические сведения.

Отчёт сводной таблицы используется для суммирования, анализа, исследования и представления сводных данных.

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

1.      Выберите ячейку из диапазона ячеек или укажите точку вставки внутри таблицы MicrosoftOfficeExcel.

Убедитесь, что в диапазон ячеек попадает заголовок столбца.

2.      Выполните одно из следующих действий.

·     Чтобы создать сводную таблицу, на вкладке Вставка в группе Таблицы выберите раздел Сводная таблица, а затем пункт Сводная таблица.

На экран будет выведено диалоговое окно Создание сводной таблицы.

·     Чтобы создать сводную таблицу и сводную диаграмму, на вкладке Вставкав группе Таблицы выберите раздел Сводная таблица, а затем пункт Сводная диаграмма.

На экран будет выведено диалоговое окно Создать сводную таблицу и сводную диаграмму.

3.  Выберите источник данных.

·     Выберите пункт Выбрать таблицу или диапазон.

·     Введите в поле Таблица или диапазондиапазон ячеек или ссылку на таблицу, например «=ПрибыльЗаКвартал».

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

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

Примечание.

Если диапазон ячеек находится на другом листе той же книги или в другой книге, введите имя книги и листа, используя следующий синтаксис: ([имякниги]имялиста!диапазон).

4.  Определите местоположение. Выполните одно из следующих действий.

·     Чтобы поместить отчет сводной таблицы на новый лист, начиная с ячейки A1, щелкните пункт На новый лист.

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

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

5.      Нажмите кнопку ОК.

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

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

 

Практическое задание

v Загрузите ранее созданный файл kadr2. xlsх

1.      Получить информацию о суммарных окладах по отделам.

     1.1  Установите курсор на одну из ячеек списка.

     1.2  В меню выберитеВставка, на ленте в разделе Таблицы – Сводная таблица.

ЗапускаетсяМастер сводных таблиц.

I шаг – указание диапазона, содержащего исходные данные.

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

II шагразмещение сводной таблицы на новом листе или на существующем.НажмитеОК. Выберете на новом листе.

III шаг – определение структуры таблицы

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

 

Фильтр отчета

Названия столбцов

Название строк

Значения

 

рис.8

 

Перетащите мышью поле Отдел в область Название строк.

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

Получите таблицу, представленную на  рис.9.

 

Названия строк

Сумма по полю Оклад

ОТД

23024

ОНК

19288

АПС

32808

ТКБ

22504

Общий итог

97624

 

рис.9. Сводная таблица «Суммарные оклады по отделам»

 

2.      Форматирование таблицы

2.1Поменяйте название в ячейке Названия строк на Отдел, а название в ячейке Сумма по полю Оклад на Суммарный оклад.

2.2 Выровняйте по центру содержимое столбцов.

2.3 Наложите на числа  формат с двумя цифрами после точки.

В результате получится таблица, представленная на рис.10

 

Отдел

Суммарный

оклад

ОТД

23024,00

ОНК

19288,00

АПС

32808,00

ТКБ

22504,00

Общий итог

97624,00

 

Рис.10. Сводная таблица «Суммарные оклады по отделам»

после форматирования

 

2.4.        Переименуйте лист, на котором расположена сводная таблица в Сводная таблица.

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

ВызовитеМастер сводных таблиц, щёлкнув по любой ячейке сводной таблицы. Автоматически открывается 3-й шаг, и можно изменить макет сводной таблицы: поле Отдел перетащите в область Названия столбцов. В результате получится таблица, представленная на рис.11.

 

Названия столбцов

ОТД

ОНК

АПС

ТКБ

Общий итог

Суммарный оклад

23024,00

19288,00

32808,00

22504,00

97624,00

 

рис.11 Сводная таблица «Суммарные оклады по отделам»

после изменения вида структуры

 

4.  Изменение порядка значений полей строк (столбцов).

Пусть требуется установить такой порядок расположения отделов: ОНК, АПС, ОТД, ТКБ. Можно непосредственно переставить ячейки: курсор мыши установите на границу ячейки АПС так, чтобы он принял форму четырехсторонней стрелки, после чего, не отпуская левую клавишу мыши, перетащите ячейкуАПС в ячейку ОНК и т.д.

5.  Обновление сводной таблицы.

Измените один из окладов в исходном списке на Листе kadr. Для этого просто поменяйте Разряд для любого сотрудника. Сводная таблица не изменится. Чтобы изменения в исходном списке отразились на сводной таблице, нужно

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

или

2)     выделить одну из ячеек сводной таблицы; нажать на кнопку Работа со сводными таблицами, расположенную в верхней строке экрана и подсвеченную красным цветом; на ленте в области Данные щёлкните по кнопке  Обновить

Сводная таблица реализована так. В оперативной памяти создается точная копия исходного списка, так называемый КЭШ. Именно из КЭШа берет информацию сводная таблица. По команде Данные – Обновить информация из списка копируется в КЭШ, а сводная таблица, непосредственно с ним связанная, обновляется.

6.  Изменение итоговой функции.

Мастер сводных таблиц выбрал по умолчанию Суммирование окладов по отделам. Но можно выбрать и другие итоговые функции

6.1     Выполните п.1, расположив новую сводную таблицу под существующей.

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

6.3      Из контекстно - зависимого меню выберите пункт Параметры полей значений.  Будет выведено диалоговое окно Параметры поля значений. В поле Пользовательское имя введите Максимум по полю Оклад.

6.4      В поле Операция выберите Максимум.

6.5      Щёлкните по кнопке ОК.

Получите таблицу, представленную на рис 12.

 

Названия

столбцов

ОТД

ОНК

АПС

ТКБ

Общий итог

Максимум по полю Оклад

7256

6536

8056

5888

8056

или

Отдел

Максимум по полю Оклад

ОТД

7256

ОНК

6536

АПС

8056

ТКБ

5888

Общий итог

8056

 

рис.12 Сводная таблица « Максимум по полю Оклад»

 

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

7.1 Выполните п.1, расположив новую сводную таблицу под существующими.

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

7.3  Из контекстно - зависимого меню выберите пункт Параметры полей значений.  Будет выведено диалоговое окно Параметры поля значений.

7.4 Щёлкните кнопку Дополнительные вычисления

7.5 В списке Дополнительные вычисления выберите Доля от суммы по столбцу.

7.6 Нажмите кнопкуЧисловой формат. Появится диалоговое окно для выбора числового формата.

Получите таблицу, представленную на рис.13.

 

Названия строк

Сумма по полю Оклад

ОТД

23,35%

ОНК

19,56%

АПС

34,28%

ТКБ

22,82%

Общий итог

100,00%

 

Рис. 13 Сводная таблица «Доли суммарных окладов по отделам»

 

7.7 Получите таблицу, следующего вида:

 

Названия столбцов

ОТД

ОНК

АПС

ТКБ

Общий итог

Сумма по полю Оклад

23,35%

19,56%

34,28%

22,82%

100,00%

 

1.      Получить информацию о количестве мужчин и женщин в отделах.

8.1  Установите курсор на одну из ячеек списка.

8.2 ЗапуститеМастер сводных таблиц. (В меню выберитеВставка, на ленте в разделе Таблицы – Сводная таблица). Расположите создаваемую сводную таблицу под существующими на листе Сводная таблица.

8.3 На 3-ем шаге сформируйте макет : в область строк поместите поле Отдел, в область столбцов – Пол, в области данных можно разместить любое текстовое поле, например, тот же Отдел  Появится строкаКоличество по полю Отдел (если бы вы поместили числовое поле, то Мастер предложил бы вам сумму по этому полю и пришлось бы менять “сумму” на “количество”).

8.4 Щёлкните мышкой в любом свободном пространстве листа.

Получите таблицу, представленную на рис.14.

 

Количество по полю

Отдел

Названия столбцов

Названия строк

ж

М

муж

Общий итог

ОТД

3

2

5

ОНК

1

4

5

АПС

3

2

2

7

ТКБ

1

4

5

Общий итог

8

12

2

22

 

рис.14 Сводная таблица «Количество мужчин и женщин в отделах»

8.1           Получите таблицу, следующего вида:

 

Количество по полю Отдел

Названия столбцов

Пол

ОТД

ОНК

АПС

ТКБ

Общий итог

ж

3

1

3

1

8

М

2

4

2

4

12

муж

2

2

Общий итог

5

5

7

5

22

 

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

9.1  Получите сводную таблицу, вид которой представлен на рис. 15, расположив её под существующими.

 

Сумма по полю

Оклад

Названия

столбцов

Пол

ОТД

ОНК

АПС

ТКБ

Общий итог

ж

17704

2496

18672

5888

44760

М

5320

16792

7704

16616

46432

муж

7432

7432

Общий итог

23024

19288

33808

22504

98624

 

рис.15 Сводная таблица «Суммарные оклады по отделам»

 

9.2  Выделите какую-либо ячейку в сводной таблице.

9.3  В контекстном меню выберите Параметры сводной таблицы…

9.4  В появившемся диалоговом окне Параметры сводной таблицы перейдите на вкладку Итоги и фильтры и  снимите флажки в окнах Показывать общие итоги для строк и Показывать общие итоги для столбцов.

9.5  Нажмите кнопку ОК.

9.6  Переместите поле Отдел в Область строк.

Получите следующую таблицу:

 

Пол

Сумма по полю Оклад

ж

44760

М

46432

муж

7432

 

или

 

Пол

Сумма по полю Оклад

ж

44760

ОТД

17704

ОНК

2496

АПС

18672

ТКБ

5888

М

46432

ОТД

5320

ОНК

16792

АПС

7704

ТКБ

16616

муж

7432

АПС

7432

 

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

10.1.   Выделите ячейку в списке.

10.2.   ВыберитеВставка - Таблицы – Сводная таблица. Расположите создаваемую сводную таблицу под существующими на листе Сводная таблица.

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

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

 

Количество

по полю Дети

Названия столбцов

Названия строк

0

1

2

3

6

8

9

(пусто)

Общий итог

ОТД

2

1

2

5

ж

2

1

3

М

1

1

2

ОНК

3

1

1

5

ж

1

1

М

2

1

1

4

АПС

1

2

3

6

ж

1

1

1

3

М

1

1

2

муж

1

1

ТКБ

2

1

1

1

5

ж

1

1

М

2

1

1

4

Общий итог

4

7

5

2

1

1

1

21

 

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

В результате получите таблицу следующего вида:

 

Количество по полю Дети

Названия

столбцов

Названия строк

1

Общий итог

ОНК

1

1

М

1

1

Общий итог

1

1

 

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

 

v Сохраните рабочую книгу под именем kadr8 в своей папке  в папке «Мои документы».

v Завершите работу с Excel.

 

Контрольные задания

Задание 1

Дана исходная таблица “Учет количества деревьев”

 

 

Учет количества деревьев

N п.п. делянки

Порода

Возраст, лет

Высота,м

Площадь учета,м2

Количество деревьев, шт.

1

дуб

2

2,25

25

35

1

дуб

3

0,35

25

109

1

клен

2

0,5

25

6

1

липа

10

2,5

50

3

1

ель

5

0,3

50

5

2

дуб

3

0,35

25

98

2

дуб

5

0,6

10

56

2

дуб

7

0,78

10

22

2

дуб

10

1,1

25

2

2

липа

3

0,41

25

14

3

дуб

10

1,2

25

1

3

ель

7

0,85

15

1

3

клен

15

3

25

2

3

липа

10

2,5

10

4

3

липа

15

3,1

25

4

4

ель

6

0,54

25

3

4

ель

7

0,9

25

2

4

клен

5

1,55

25