ЛАБОРАТОРНАЯ РАБОТА №3 Информационные технологии  создания и обработки табличных документов

Теоретическая справка

Часть 1. Создание таблицы с исходными данными, выполнение расчетов и построение диаграммы

Часть 2. Работа с встроенными функциями и с электронной таблицей как с базой данных

Часть 3. Создание и работа со сводной таблицей

Контрольные задания к лабораторной работе № 3

Контрольные вопросы к лабораторной работе №3

 

Теоретическая справка

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

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

Концепция электронной таблицы впервые была реализована в системе Visi Calс (в 1979 г.), а затем нашла свое дальнейшее развитие в ТП Super Calс,  Lotus 1-2-3, Quattro Pro, Excel (1987 г.).

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

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

Рабочая область электронной таблицы состоит из строк и столбцов, на пересечении которых находятся ячейки. Строки в таблицах обозначены цифрами (от 1 до 65536), столбцы – латинскими буквами (256 столбцов).

Адрес ячейки определяется названием (номером) столбца и номером строки (А1, С15 и т.п.).

Блок ячеек – группа последовательных ячеек. Блок ячеек может состоять из одной ячейки, строки (или ее части), столбца (или его части), а также последовательности строк или столбцов (или их частей). Адрес блока ячеек задается адресом верхней левой и правой нижней клеток, разделенных двоеточием. (C8: D22).

В каждую ячейку пользователь может ввести данные одного из следующих возможных типов:

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

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

3) функции, представляющие собой программу с уникальным именем, для которой пользователь должен задать конкретные значения аргументов функции, стоящих в скобках после ее имени. Функцию (так же, как и число) можно считать частным случаем формулы. В ЭТ могут быть представлены следующие виды функций: математические, статистические, текстовые, логические, финансовые, функции даты и времени и т.п;

4) формулы, состоящие из операндов, соединенных знаками арифметических действий (+, -, *, /, **). Операндами могут быть: числа, адреса клеток, функции. В табличном процессоре Excel  ввод формулы начинается со знака =;

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

 

Существуют следующие виды адресации ячеек:

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

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

 

Табличный процессор может иметь несколько режимов работы, наиболее важные из них:

1)      режим готовности – это режим, в котором происходит выбор ячейки или блока ячеек для корректировки или выполнения какой-либо операции. В этом режиме текстового курсора нет, а есть выделение активной ячейки (подсвечивание);

2)     режим ввода данных – это режим, в который переходят, как только начинают вводить данные в  определенную ячейку. Закончив ввод данных в ячейку,  покидают режим ввода данных и переходят в режим готовности, т.е. происходит циклическая смена режимов готовности, и ввод данных до тех пор, пока электронная таблица не будет  полностью сформирована;

3)     командный режим – это режим, когда необходимо выполнить над созданной ЭТ какие-нибудь действия (сохранить, отсортировать, оптимизировать и т.п.), для чего используются команды меню. К командам меню можно обратится следующими способами: а) нажав функциональную клавишу F10; б) используя специальные комбинации клавиш;  в) выбрать пункты (команды) меню с помощью мыши;

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

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

1) гистограмма показывает изменение данных за определенный период времени и иллюстрирует соотношение отдельных значений данных. Категории располагаются по горизонтали, а значения по вертикали. Таким образом,  уделяется  большое внимание изменениям во времени;

2) линейчатая диаграмма отражает соотношение отдельных компонентов. Категории расположены по горизонтали, а значения по вертикали. Таким образом, уделяется большее внимание сопоставлению значений и меньшее – изменениям во времени;

3) график отражает тенденции изменения данных за равные промежутки времени;

4) круговая диаграмма показывает как абсолютную величину каждого элемента ряда данных, так и его вклад в общую  сумму. На круговой диаграмме может быть представлен только один ряд данных. Такую диаграмму рекомендуется использовать, когда необходимо подчеркнуть какой-либо  значительный элемент.

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

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

 

Структура электронной таблицы

Основными объектами действий пользователя при проектировании и эксплуатации электронной таблицы является: ячейка, столбец, строка, блок ячеек, файл таблицы. Совокупность блоков ячеек электронной таблицы, для которых характерны общее назначение и единая форма представления и заполнения – называется полем.

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

В таблице можно выделить четыре поля:

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

2)   поле исходных данных включает ячейки с числовой информацией. Ввод исходных данных осуществляется в ручном режиме;

3)   поле расчетных формул или промежуточных результатов, содержащее «зависимые» ячейки с формулами, в которых  операндами являются адреса ячеек с исходными данными и параметрами. Заполнение поля формул осуществляется в  автоматическом режиме командой копирования;

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

 

 

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

 

Часть 1. Создание таблицы с исходными данными, выполнение расчетов и построение диаграммы

 

1.     Загрузите ТП Excel и подготовьте  таблицу с исходными данными, согласно рисунку 1 (шрифт -Times New Roman; размер шрифта – 13):

 

 

А

В

С

D

E

F

G

H

1

Лабораторная работа №3

 

2

Фамилия студента, выполняющего работу

 

3

ЧИСЛО ЗАРЕГИСТРИРОВАННЫХ ПРЕСТУПЛЕНИЙ ПО ВИДАМ
(тысяч)

4

 

2009

2010

2011

2012

2013

2014

2015

5

Зарегистрировано преступлений - всего

 

 

 

 

 

 

 

6

        в том числе:

 

 

 

 

 

 

 

7

убийство и покушение на убийство

31,6

31,6

30,8

27,5

22,2

20,1

17,7

8

умышленное причинение тяжкого вреда здоровью

57,1

57,4

57,9

51,4

47,3

45,4

43,1

9

Грабеж

 

198,0

251,4

344,4

357,3

295,1

244,0

205,4

10

Разбой

 

48,7

55,4

63,7

59,8

45,3

35,4

30,1

11

Кража

 

1150,8

1276,9

1573,0

1677,0

1567,0

1326,3

1188,6

12

терроризм, единиц

 

561

265

203

112

48

10

15

13

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

181,7

150,1

175,2

212,0

231,2

232,6

238,5

14

нарушения правил дорожного движения
и эксплуатации транспортных средств

53,6

26,5

26,6

26,3

25,6

24,3

27,5

15

из них повлекшие по неосторожности смерть человека, двух или более лиц

17,6

16,0

15,7

15,8

15,5

13,6

10,6

16

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

 

 

 

 

 

 

 

 

Рис.1

Примечание:

Копирование выполняется следующими способами:

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

б) используйте команды пункта меню Главная или Контекстно-зависимого меню.

Объединение ячеек производится следующими способами:

а) предварительно выделите нужное количество ячеек, затем выполните команды: Главная, Выравнивание, Выравнивание, Объединение ячеек, ОК.

б) через Панель инструментов, нажав кнопку  Объединить и поместить в центре.

Форматирование текста в ячейках производится следующим образом:

Предварительно выделите необходимое количество ячеек и выполните следующие команды: Главная, Выравнивание,   Выравнивание, По верхнему краю, Переносить по словам, ОК.

 

2.    Произведите расчеты:

а) Рассчитайте общее число зарегистрированных преступлений за 2009 год, для чего внесите в ячейку В5 следующую формулу: =B7+B8+В9+B10+B11+В12+B13+B14+В15. Скопируйте ее в блок ячеек C5:Н5.

б) Рассчитайте среднее  число зарегистрированных преступлений за 2009 год, для чего внесите в ячейку B16 следующую формулу: =СРЗНАЧ(В7:В15). Скопируйте данную формулу  в соответствующие  ячейки расчетов.

в) Выделите блоки ячеек B5:Н5 и B16:Н16 с помощью мыши и клавиши CTRL и установите разрядность чисел – два знака после запятой (Главная – Число)

г) Подведите курсор мыши к  Лист1, дважды щелкните по нему и переименуйте его как «Статистические данные».

 

3.    а) Создайте линейчатую диаграмму, отображающую динамику зарегистрированных преступлений по грабежу за 2009 -2015 гг., выполнив следующие действия:

- Вставка - Диаграммы; тип диаграммы» выберите «Линейчатая с   группировкой», ОК;

 - Откроется окно Конструктора  -  выберите  команду Выбрать данные, чтобы выбрать диапазон данных для диаграмм: в поле Диапазон данных для диаграммы выделите следующий диапазон ячеек: В9:Н9;

   - в поле Элементы легенды выберите вкладку Изменить в появившемся окне в области «Имя ряда» - укажите грабеж (т.е выделите ячейку А9), Ок;

   - в поле  Подписи горизонтальной оси выберите вкладку Изменить в появившемся окне в области «Диапазон подписей оси» - укажите года (т.е. выделите блок ячеек В4:Н4), Ок.

- Выберите Макет диаграммы – Макет 1 введите в области диаграммы название диаграммы «Динамика преступлений за период с 2009 по 2015 гг.» (Конструктор).

 - Установите Подписи данных – В центре (Макет).

- Размещение диаграммы выберите «на отдельном» и рядом введите «Грабеж» (Конструктор – Расположение).

 

б) Создайте диаграмму – Гистограмму на отдельном листе, отображающую динамику зарегистрированных преступлений по краже за 2011 – 2013 годы.

4.     Перейдите на Лист 2 и подготовьте таблицу (см. рис. 2) для расчета «Таблицы подстановки».

 

A

B

C

D

E

F

G

H

I

J

K

L

1

Таблица подстановки для расчета суммы чисел

2

Слагаемое 1

5

 

 

 

 

 

 

 

 

 

 

3

Слагаемое 2

10

Слагаемое 1

4

Результат

 

1

2

3

4

5

6

7

8

9

10

5

 

 

 

Слагаемое 2

1

 

 

 

 

 

 

 

 

 

 

6

2

 

 

 

 

 

 

 

 

 

 

7

3

 

 

 

 

 

 

 

 

 

 

8

4

 

 

 

 

 

 

 

 

 

 

9

5

 

 

 

 

 

 

 

 

 

 

10

6

 

 

 

 

 

 

 

 

 

 

11

7

 

 

 

 

 

 

 

 

 

 

12

8

 

 

 

 

 

 

 

 

 

 

13

9

 

 

 

 

 

 

 

 

 

 

14

10

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Рис. 2

 

б) Рассчитайте ячейку В4, используя следующую формулу: =СУММ(В2:В3).

в) Выделите блок В4:L14, выполните команды Данные, Работа с данными, Анализ «что- если», Таблица данных,  в появившемся окне в области Подставлять значения по столбцам в выделите В2, а в Подставлять значения по строкам в выделите В3, ОК.

г) Переименуйте Лист 2 в «Таблица подстановки».

5.    а) Подготовьте  таблицу с исходными данными на Листе 3, согласно рисунку 3:

Предварительно выделите блоки ячеек В2:В11 и D2:D11 и выполните команды: Главная, Число, Дата, 14.03.01, ОК

 

 

А

В

С

D

E

1

Фамилия

Дата рождения

Возраст

Начало учебы

Количество обучающихся лет

2

Алиев А.

09.05.95

 

01.09.11

 

3

Ахмедова Р.

10.03.94

 

01.09.10

 

4

Гаджиев С.

28.02.94

 

01.09.11

 

5

Ибрагимова О.

30.04.92

 

01.09.09

 

6

Магомедов М.

06.06.94

 

01.09.10

 

7

Газиева П.

12.11.93

 

01.09.10

 

8

 Рашидов Э.

08.09.90

 

01.09.08

 

9

Ханова Л.

21.12.91

 

01.09.08

 

10

Батурин Р.

03.10.92

 

01.09.09

 

11

Гамидов А.

04.01.94

 

01.09.10

 

Рис.3

 

б) Выделите блок ячеек С2:С11 и Е2:Е11 и выполните команды: Главная, Число, (все форматы), Тип: ГГ, ОК.

Произведите расчеты:

 - В ячейку С2 введите формулу: =СЕГОДНЯ()-В2. Скопируйте формулу из ячейки С2 в блок ячеек С3:С12.

 - В ячейку Е2 введите формулу: =СЕГОДНЯ()-D2. Затем скопируйте ее в блок ячеек Е3:Е11.

в) Выделите созданную таблицу и выполните условное форматирование, выбрав формат Цветовые шкалы – Зеленый-желтый.

     г) Переименуйте Лист 3 в «Даты».

 

 

 

Часть 2. Работа с встроенными функциями и с электронной таблицей как с базой данных

1. а) Загрузите ТП Excel и подготовьте  таблицу для расчета математических и статистических функций, представленную на рисунке 1 :

 

 

A

B

C

D

E

F

G

 

Н

1

Название функции

Аргументы

результат

2

Математические функции

3

сумма чисел

2,5

3

5

6

9

0,5

 

4

произведение чисел

5

7

8

3

11

15

 

5

корень из числа

 

 

 

 

 

 

 

6

степень числа

 

 

 

 

 

 

 

7

римское число

 

 

 

 

 

 

 

8

сумма квадратов

 

 

 

 

 

 

 

9

Статистические функции

10

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

10

14

27

39

3

2

 

11

минимальное число

4

7

9

13

15

17

 

12

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

1

2

3

7

11

15

 

13

количество чисел

2,3

4

5,5

6

27

30

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Рис.1

 

 

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

 

б) Рассчитайте ячейки:

a)   H3, используя следующую функцию: = СУММ(B3:G3);

b)  H4, используя следующую функцию: = ПРОИЗВЕД(B4:G4);

c)   В5, используя следующую функцию: = КОРЕНЬ(B4), а затем скопируйте ее в блок ячеек C5:Н5;

d)  В6, используя следующую функцию: =СТЕПЕНЬ(B3;B4), а затем скопируйте ее в блок ячеек C6:Н6;

e)  В7, используя следующую функцию: =РИМСКОЕ(B4), а затем скопируйте ее в блок ячеек C7:Н7;

f)    В8, используя следующую функцию: =СУММКВ(B3:B4), а затем скопируйте ее в блок ячеек C8:Н8;

g)  H10, используя следующую функцию: = МАКС(B10:G10);

h)  H11, используя следующую функцию: = МИН(B11:G11);

i)    H12, используя следующую функцию: = СРЗНАЧ(B12:G12);

j)    H13, используя следующую функцию: =СЧЁТ(B13:G13).

в) Переименуйте Лист 1 в «Встроенные функции».

 

1.     а) Перейдите на Лист 2 и заполните таблицу согласно рисунку 2.

 

 

 

А

В

С

D

1

СТАТИСТИКА ПРЕСТУПЛЕНИЙ ПО СЕВЕРО-КАВКАЗСКОМУ И ЮЖНОМУ ФО

2

ФО

РЕГИОНЫ

зарегистрировано преступлений

за  2015г

зарегистрировано преступлений

за   2016г

3

Северо-Кавказский ФО

Ставропольский край

7292

17849

4

Республика Ингушетия

518

1113

5

Республика Дагестан

2565

6906

6

Кабардино-Балкарская Республика

2023

4427

7

Республика Северная Осетия - Алания

1371

3772

8

Карачаево-Черкесская Республика

867

2168

9

Чеченская Республика

994

2443

10

Южный ФО

Краснодарский край

10965

33724

11

Астраханская область

3499

12458

12

Волгоградская область

8280

20277

13

Ростовская область

11030

29763

14

Республика Адыгея

941

2524

15

Республика Калмыкия

607

2204

Рис. 2

 

Примечание. Область таблицы А2:D15 можно рассматривать как базу данных. Столбцы А,В,С,D этой таблицы называются полями, строки 3-15 называются записями. Область данных А2:D2 содержит имена полей.

 

б) Выделите блок ячеек А2:D2 и выполните команды: Главная, Редактирование, Сортировка и фильтр,  Фильтр. На полях должны появиться кнопки.

a) Нажмите кнопку на поле Регионы. Выберите пункт Текстовые фильтры – Настраиваемый фильтр. В  диалоговом окне  «Пользовательский автофильтр» задайте критерии согласно рисунку 3 и нажмите ОК. Проверьте! В базе данных остались Ставропольский край и Астраханская область.

 

 

Рис. 3

 

      Отобразите все данные, нажав на кнопку фильтра на поле Регионы и выбрав пункт Снять фильтр с Регионы.

b) Осуществите поиск регионов, имеющих число зарегистрированных  преступлений  за 2012 год меньше  900. Нажмите кнопку на поле Зарегистрировано преступлений за 2012 г. Выберите пункт Числовые фильтры и задайте необходимое условие (В базе данных  должны остаться - Республика Ингушетия, Карачаево-Черкесская Республика, Республика Калмыкия).

  Отобразите все данные.

c)  Осуществите поиск регионов, где число зарегистрированных преступлений за 2015 год  не менее 10000, а за 2016 год – не более 30000 (Ростовская область).

 Отобразить все данные.

d) Осуществите поиск регионов, имеющих число зарегистрированных преступлений  за 2016 год в диапазоне от 2400 до 3500 (Чеченская Республика, Республика Адыгея).

  Отобразить все данные.

e)  Установите фильтр по следующим полям:

-        осуществите поиск регионов, которые начинаются с буквы Р и буквы К.

-        осуществите поиск регионов, имеющих число зарегистрированных преступлений  за 2012 год в диапазоне от 1000 до 3000.

-        осуществите поиск регионов, где число зарегистрированных преступлений за 2016 год не менее 4000 (Республика Дагестан, Кабардино-Балкарская Республика).

 

3. На основе таблицы «Статистика преступлений по Серекокавказскому и Южному ФО» создайте следующие диаграммы:

а) Создайте круговую диаграмму на отдельном листе, отобразив на ней число зарегистрированных  преступлений за 2016 год, дав ему название «Динамика преступлений»

б) Создайте линейчатую диаграмму на отдельном листе, отображающую динамику зарегистрированных преступлений по Республике Дагестан за 2015 -2016 гг.

в) Создайте диаграмму – Гистограмму на отдельном листе, отображающую динамику зарегистрированных преступлений по Кабардино-Балкарской Республике  за 2015 – 2016 годы.

в) Переименуйте Лист 2 в «Регионы».

 

4.Перейдите на Лист 3 и заполните таблицу согласно рисунку 4.

Состав осужденных  (в процентах)

 

2013г.

2014г.

2015г.

2016г.

Среднее

значение

Осуждено – всего из них по возрасту, лет:

 

 

 

 

 

14-17

9,0

9,1

7,9

6,3

 

18-24

29,8

30,0

29,2

28,1

 

25-29

18,9

18,8

19,0

19,5

 

30-49

35,9

35,4

36,9

38,8

 

50 и старше

6,4

6,7

7,0

7,3

 

Осуждено – всего из них по полу:

 

 

 

 

 

женщины

13,1

14,3

15,1

15,3

 

мужчины

86,9

85,7

84,9

84,7

 

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

 

 

 

 

 

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

 

 

 

 

 

Рис.4

 

 Рассчитайте следующие значения, используя соответствующие функции:

 

а) Осуждено – всего из них по возрасту, лет;

б) Осуждено – всего из них по полу;

в) Минимальное значение (по полу);

г) Максимальное значение (по возрасту);

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

 

5. а) С помощью фильтра произведите поиск осужденных, где число их  за 2015 год  не менее 15 и не более 85, а за 2014 год – больше 50. (мужчины)

б) Создайте линейчатую диаграмму на отдельном листе, отобразив на ней осужденных мужчин за 2014 - 2016 годы. Переименуйте Диаграмму1 в «Осужденные мужчины», а Лист 3– «Состав осужденных».

 

Часть 3. Создание и работа со сводной таблицей

 

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

1.      Загрузите табличный процессор Excel и на Листе 1 подготовьте таблицу с исходными данными (см. рис. 1). Для заполнения столбцов с данными фамилий и видов договоров необходимо использовать справочные данные. Например, в ячейку В11 ввести  = (знак равенства), а затем щелкнуть по ячейке В7.

Выравнивание в ячейках произведите согласно рисунку 1.

 

 

A

B

C

D

E

F

1

 

Справочные данные

 

 

 

2

 

Ф.И.О. сотрудников

Виды договоров

 

 

 

3

 

Ахмедов А.О.

договор аренды

 

 

 

4

 

Алиев М.Т.

договор купли-продажи

 

 

 

5

 

Эмирбеков Б.Ю.

договор оказания услуг

 

 

 

6

 

Кадиев Р.Т.

инвестиционный контракт

 

 

 

7

 

Шапиев Р.Л.

 

 

 

 

8

 

Умаров П.Н.

 

 

 

 

9

 

январь

Курс доллара

29

 

10

№

Ф.И.О. сотрудника

Виды договоров, составленных для клиентов

Сумма (в УЕ), полученная фирмой

Сумма (в УЕ), полученная сотрудником

Сумма (в руб.), полученная сотрудником

11

1

Шапиев Р.Л.

договор купли-продажи

220

 

 

12

2

Эмирбеков Б.Ю.

договор аренды

120

 

 

13

3

Ахмедов А.О.

договор купли-продажи

500

 

 

14

4

Алиев М.Т.

инвестиционный контракт

260

 

 

15

5

Шапиев Р.Л.

инвестиционный контракт

160

 

 

16

6

Умаров П.Н.

договор аренды

250

 

 

17

7

Эмирбеков Б.Ю.

инвестиционный контракт

220

 

 

18

8

Кадиев Р.Т.

договор купли-продажи

180

 

 

19

9

Алиев М.Т.

договор оказания услуг

250

 

 

20

10

Умаров П.Н.

договор аренды

400

 

 

21

 

 

 

Максимум

 

 

22

 

 

 

Минимум

 

 

23

 

 

 

Среднее

 

 

Рис. 1

 

а) Рассчитайте сумму (в УЕ), полученную сотрудником, следующим образом: если сумма полученная фирмой превышает 200 УЕ, то размер вознаграждения, получаемого сотрудником за выполненную работу, составляет 70% от суммы (в УЕ) полученной фирмой, иначе 50%. Для проведения расчета используйте встроенную логическую функцию ЕСЛИ (=ЕСЛИ(D11>200; D11*70%; D11*50%).

б) Рассчитайте сумму (в руб), полученную сотрудником: сумму (в УЕ), полученную сотрудником умножить на курса доллара, при этом используйте абсолютную и относительную адресацию ячеек (=Е11*F$9).

в) В соответствующих ячейках рассчитайте максимальный, минимальный и средний размер вознаграждений для сотрудников, для чего используйте встроенные статистические функции МАКС, МИН, СРЗНАЧ.

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

д) Переименуйте Лист1 в Январь.

 

2.    а) Скопируйте лист Январь, для чего выполните команды: Нажмите по названию листа Январь правой кнопкой мыши, Переместить/скопировать лист…, Создать копию, ОК.

б) Переименуйте созданный лист Январь(2) в Февраль.

На листе Февраль измените название месяца и задайте новое произвольное значение курса доллара.

в) Удалите данные в блоке ячеек (B11:D20).

г) Введите произвольно фамилии и виды договоров из справочных данных.

д) Введите произвольно суммы (в УЕ), полученные фирмой. Проверьте результаты пересчета сумм вознаграждений сотрудников.

е) Аналогично создайте лист Март.

 

3.    а) Перейдите на Лист 2 (или на любой другой новый лист) и переименуйте его в Январь-Март.

б) В ячейку А1 введите: ДАННЫЕ ЗА I КВАРТАЛ.

В ячейку А2 введите: Месяц.

В ячейку В2 введите: =Январь!В10. Обратите внимание на то, что отобразилось в ячейке В2 и почему.

Скопируйте содержимое ячейки В2 в блок ячеек С2:F2.

В ячейку В3 введите: = Январь!В11. Скопируйте содержимое ячейки В3 в блок ячеек В3:F12.

В ячейку А3 введите: = Январь!В$9. Скопируйте содержимое ячейки А3 в блок ячеек А3:А12.

в) Аналогичным образом добавьте в таблицу данные с листов Февраль и Март.

 

4.    а) На основе таблицы ДАННЫЕ ЗА I КВАРТАЛ необходимо построить сводную таблицу с помощью Мастера сводных таблиц и диаграмм, для чего выполните команды: Вставка, Сводная таблица.

В появившемся окне укажите таблица или диапазон данных: $А$2:$F$32. Обычно диапазон указывается автоматически, если перед запуском Мастера сводных таблиц и диаграмм курсор установить в начало таблицы, а именно в ячейку А2. Укажите местоположение сводной таблицы: новый лист, Ок.

б) В появившемся Макете сводной таблицы сформируйте содержание таблицы. Для этого выполните следующие операции:  в окне Список полей сводной таблицы выберите поля для добавления в отчет:

-  в поле Фильтр отчета перетащите кнопку поля Месяц;

-  в поле Названия строк – Ф.И.О. сотрудников;

-  в поле Названия столбцов – Виды договоров;

-  в поле Значения - Сумма (в УЕ), полученная фирмой;

в) Переименуйте Лист 4 в «Сводная таблица за 1 квартал».

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

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

Переименуйте Диаграмма1 в «Сводная диаграмма за 1 квартал».

 

 

5.    а) Перейдите на Лист 5 и подготовьте таблицу (см. рис. 2) для расчета показателей по структуре преступности с использованием встроенных функций.

 

A

B

C

D

E

F

G

H

I

J

1

Структура преступлений (2008-2014 гг.)

2

Виды преступлений

2008

2009

2010

2011

2012

2013

2014

Средний показатель за 2008-2014 год

Абсолютное отклонение от среднего за 2008-2014 год

3

Хулиганство

191001

181284

129505

131082

128701

132858

133577

 

 

4

Разбой

37651

34584

34318

38513

41138

38550

44525

 

 

5

Кража

1367866

1207478

1053972

1143364

1413810

1297696

1270444

 

 

6

Грабеж

140597

121356

112051

122366

138973

126312

142471

 

 

7

Присвоение или растрата

36518

39389

43423

44399

48516

41334

41557

 

 

8

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

79819

96645

185832

190127

216364

243617

241589

 

 

9

Иные преступления

791376

845181

747879

822550

920268

908802

933876

 

 

10

Годовой уровень преступлений

 

 

 

 

 

 

 

 

 

11

Максимальное число преступлений

 

 

 

 

 

 

 

 

 

12

Минимальное число преступлений

 

 

 

 

 

 

 

 

 

Рис. 2

 

б) Рассчитайте:

 - ячейку B10, используя  следующую функцию: =СУММ(B3:B9), а затем скопируйте ее в блок ячеек С10:Н10.

-  ячейку В11, используя следующую функцию: =МАКС(В3:В9), а затем скопируйте ее в блок ячеек С11:Н11.

- ячейку B12, используя  следующую функцию: =МИН(B3:B9), а затем скопируйте ее в блок ячеек С12:Н12.

- ячейку I3, используя  следующую функцию: =СРЗНАЧ(B3:H3), а затем скопируйте ее в блок ячеек I4:I10.

- ячейку J3 с  помощью  логической функции  ЕСЛИ следующим образом: если годовой уровень преступлений меньше среднего за период с 2008 года  по  2014 год, то отклонение определяется как разность среднего и годового уровней, иначе – как разность годового уровня и среднего за период Для этого введите  следующую функцию: =ЕСЛИ(H3>I3;H3-I3;I3-H3), а затем скопируйте ее в блок ячеек J4:J10.

 

в) Постройте диаграмму  (Гистограмму), отображающую структуру преступности за период 2008-2014 годов по хулиганству и расположите ее на отдельном листе, дав имя  «Хулиганство».

     г) Переименуйте Лист 5 в  «Структура преступности 2008-2014гг.».

 

 

Контрольные задания к лабораторной работе № 3

 

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

1.     Загрузите ТП Excel. Подготовьте таблицу с исходными данными:

 

ЧИСЛО ЗАРЕГИСТРИРОВАННЫХ ПРЕСТУПЛЕНИЙ ПО РОССИИ

 за 2013-2016гг

Год

Зарегистрировано преступлений- всего, тыс

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

Преступления, совершённые гражданами СНГ в России

Количество, в тыс

Количество, в тыс

2009

 

3582,5

50,1

45,3

2010

 

3209,9

53,9

48,8

2011

 

2994,8

58,0

53,1

2012

 

2628,8

49,0

44,6

первая половина 2013

1246,9

24,5

22,1

Минимальное

значение

 

 

 

ВСЕГО

 

 

 

 

2.    Рассчитайте минимальное значение и графу ВСЕГО, используя соответствующие функции.

3.    Выделите созданную таблицу, произведите автоформатирование таблицы, используя понравившейся формат.

4.    Выделите значения графы ВСЕГО и установите разрядность чисел – два знака после запятой.

5.    С помощью фильтра произведите поиск:

 а) общего числа зарегистрированных преступлений, где их число не превышает 3500 преступлений;

б) преступлений, совершённых иностранными гражданами и лицами без гражданства в России, у которых число зарегистрированных преступлений  не менее 40;

в)  преступлений, совершённых гражданами СНГ в России, находящихся в диапазоне от 43 до 50 преступлений.

6.    Создайте круговую диаграмму на отдельном листе, отображающую динамику преступлений, совершенных гражданами СНГ в России, дав ему название «Динамика преступлений»

7.    Переименуйте Лист 1 как «Фамилия студента».

8.    Перейдите на Лист 2 и с помощью «Таблицы подстановки» создайте таблицу подстановки для расчета произведения чисел(любых пяти чисел).

9.    Для созданной таблицы установите цвет шрифта текста – зеленый; размер шрифта – 15.

10.          Переименуйте Лист 2 в «Таблицу подстановки» и сохраните документ в папку Мои документы под именем «Кр2+ Фамилия» и покажите результаты выполнения задания преподавателю.

 

 

 

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

 

1.     Загрузите ТП Excel. Подготовьте таблицу с исходными данными:

 

ДИНАМИКА ЧИСЛА  ЗАРЕГИСТРИРОВАННЫХ ПРЕСТУПЛЕНИЙ, ХАРАКТЕРНЫХ ДЛЯ ОРГАНИЗОВАННЫХ ФОРМИРОВАНИЙ, В РОССИИ

 в 2014-2016гг.

Преступления

2014г.

2015 г.

2016 г.

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

Убийство по найму (ст. 105, ч. 2, п. «з»)

132

152

155

 

Похищение человека (ст. 126)

1140

1415

1554

 

Незаконное лишение свободы (ст. 127)

1010

1278

1417

 

Изготовление или сбыт поддельных кредитных либо расчетных карт или иных платежных документов (ст. 187)

130

150

309

 

Общее число зарегистрированных преступлений

 

 

 

 

 

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

3.    Выделите созданную таблицу, произведите автоформатирование таблицы, используя формат – Цветной3.

4.    Выделите среднее значение и установите разрядность чисел – три знака после запятой.

5.    Используя «Пользовательский автофильтр», произведите поиск зарегистрированных преступлений:

     а) за 2011 год, находящихся в диапазоне от 120 до 1100 преступлений;

     б) где их число за 2012 год не менее 152, а за 2016 год – не более 155 преступлений.(правильно – убийство по найму (ст. 105, ч. 2, п. «з»)).

6.    Создайте линейчатую диаграмму на отдельном листе, отображающую динамику преступлений за 2014-2016 года по виду преступления «Убийство по найму», дав ему название «Убийство по найму».

7.    Переименуйте Лист 1 как «Фамилия студента».

8.    Перейдите на Лист 2 и с помощью «Таблицы подстановки» создайте таблицу подстановки для расчета степени числа (любых пяти чисел).

9.    Для созданной таблицы установите цвет шрифта текста – голубой; размер шрифта – 13.

10.          Переименуйте Лист 2 в «Таблицу подстановки» и сохраните документ в папку Мои документы под именем «Кр2+ Фамилия» и покажите результаты выполнения задания преподавателю.

 

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

 

1.               Загрузите ТП Excel. Подготовьте таблицу с исходными данными:

 

Коэффициенты зарегистрированной преступности на 100 тыс. человек по различным видам преступлений в России и в других странах мира

Вид преступлений

Россия 2016г.

США 2016 г.

Великобритания 2016 г.

Франция 2016 г.

Германия 2016 г.

Убийство

14,1

5,4

1,4

1,7

1,1

Хулиганство

34,4

89,0

14,2

13,9

9,1

Разбой

24,9

145,3

157,4

40,1

72,1

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

 

 

 

 

 

Зарегистрировано - всего

 

 

 

 

 

 

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

3.    Выделите созданную таблицу, произведите автоформатирование таблицы, используя формат – Объемный2.

4.    Выделите максимальное значение и установите разрядность чисел – два знака после запятой.

5.    Осуществите поиск преступлений с помощью фильтра:

     а)  по России, имеющих число зарегистрированных преступлений   в диапазоне от 14 до 34;

     б) у которых число преступлений по США превышает 145 (правильно - разбой)

6.    Создайте диаграмму (Гистограмму) на отдельном листе, отображающую динамику преступлений всех стран по виду преступления «Разбой», дав ему название «Динамика преступлений по разбою».

7.    Переименуйте Лист 1 как «Фамилия студента».

8.    Перейдите на Лист 2 и с помощью «Таблицы подстановки» создайте таблицу подстановки для расчета разности чисел (любых пяти чисел).

9.    Для созданной таблицы установите цвет шрифта текста – бирюзовый; размер шрифта – 17.

10.          Переименуйте Лист 2 в «Таблицу подстановки» и сохраните документ в папку Мои документы под именем «Кр2+ Фамилия» и покажите результаты выполнения задания преподавателю.

 

 

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

 

1.               Загрузите ТП Excel. Подготовьте таблицу с исходными данными:

 

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

в 2014—2016 гг.

Вид преступления

2014г.

2015 г.

2016 г.

Контрабанда (ст. 188)

3375

3525

3982

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

1785

1645

1730

Бандитизм (ст. 209)

374

513

523

Организация преступного сообщества (преступной организации) (ст. 210)

48

84

162

Подделка или уничтожение идентификационного номера транспортного средства (ст. 326)

571

1029

1231

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

 

 

 

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

 

 

 

 

2.    Рассчитайте минимальное и среднее значения, используя соответствующие функции.

3.    Выделите созданную таблицу, произведите автоформатирование таблицы, используя формат – Классический3.

4.    Выделите среднее значение и установите разрядность чисел – один знак после запятой.

5.    Используйте «Пользовательский автофильтр» для нахождения тех видов преступлений, для которых число зарегистрированных в 2014 году  не менее 370 и в 2008 году меньше 1700 преступлений, а в 2016 году – находящихся в диапазоне от 523 до 1500 преступлений. (правильно – бандитизм и подделка или уничтожение идентификационного номера транспортного средства  )

6.    Создайте круговую диаграмму  на отдельном листе, отображающую динамику зарегистрированных преступлений по России за 2016 год, дав ему название «Динамика преступлений».

7.    Переименуйте Лист 1 как «Фамилия студента».

8.    Перейдите на Лист 2 и с помощью «Таблицы подстановки» создайте таблицу подстановки для расчета суммы чисел (любых пяти чисел).

9.    Для созданной таблицы установите цвет шрифта текста – сиреневый; размер шрифта – 11.

10.          Переименуйте Лист 2 в «Таблицу подстановки» и сохраните документ в папку Мои документы под именем «Кр2+ Фамилия» и покажите результаты выполнения задания преподавателю.

 

 

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

 

1.     Загрузите ТП Excel. Подготовьте таблицу с исходными данными:

 

Динамика зарегистрированных преступлений по Республике Дагестан

 

Вид

Преступления

2014

2015

2016

Хулиганство

970

983

974

Вымогательство

65

79

97

Мошенничество

206

400

477

Угон   автомобилей

81

74

82

Разбой

97

58

184

Грабеж

311

289

314

Кража

3552

3600

3222

Общее число преступлений

 

 

 

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

 

 

 

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

 

 

 

 

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

3.    Выделите все рассчитанные значения и установите разрядность чисел – два знака после запятой.

4.    Выделите созданную таблицу, произведите автоформатирование таблицы, используя понравившейся формат.

5.    С помощью фильтра произведите поиск зарегистрированных преступлений:

 а) за 2014 год, где их число не превышает 3000 преступлений;

б) за 2015 год, у которых их число больше 80;

в) за 2016 год, находящихся в диапазоне от 350 до 975 преступлений (правильно – хулиганство и мошенничество).

6.    Создайте линейчатую диаграмму на отдельном листе, отображающую динамику зарегистрированных преступлений по хулиганству, дав ему название «Хулиганство»

7.    Переименуйте Лист 1 как «Фамилия студента».

8.    Перейдите на Лист 2 и с помощью «Таблицы подстановки» создайте таблицу подстановки для расчета степени числа(любых пяти чисел).

9.    Для созданной таблицы установите цвет шрифта текста – желтый; размер шрифта – 13.

10.          Переименуйте Лист 2 в «Таблицу подстановки» и сохраните документ в папку Мои документы под именем «Кр2+ Фамилия» и покажите результаты выполнения задания преподавателю.

 

Контрольные вопросы к лабораторной работе №3

 

1.     Определение электронной таблицы, табличного процессора (ТП) и их основные представители на рынке программного обеспечения.

2.    Определение рабочей области, адреса ячейки, блока ячеек в ТП.

3.    Типы входных данных в ТП.

4.    Относительная и абсолютная адресация в ТП.

5.    Режимы работы ТП.

6.    Назначение и виды диаграмм в ТП.

7.    Структура электронной таблицы.

8.    Опишите технологию создания диаграмм в ТП.

9.    Характеристика параметров выравнивания содержимого ячеек электронной таблицы.

10.          Характеристика параметров форматирования чисел электронной таблицы.

11.  Характеристика параметров стилей электронной таблицы.

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

13. Назначение «Таблицы подстановки», технология ее создания.

14.Каким образом выполняется копирование содержимого ячейки?