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

ЛАБОРАТОРНАЯ РАБОТА №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	ФО	РЕГИОНЫ	зарегистрировано преступлений за  2012г	зарегистрировано преступлений за   2013г
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)  Осуществите поиск регионов, где число зарегистрированных преступлений за 2012 год  не менее 10000, а за 2013 год – не более 30000 (Ростовская область).

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

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

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

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

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

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

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

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

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

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

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

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

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

Состав осужденных  (в процентах)
	2010г.	2011г.	2012г.	2013г.	Среднее значение
Осуждено – всего из них по возрасту, лет:
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. а) С помощью фильтра произведите поиск осужденных, где число их  за 2012 год  не менее 15 и не более 85, а за 2010 год – больше 50. (мужчины)

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