Учебная работа. Лабораторная работа: Встроенные функции Excel

имени ЯРОСЛАВА МУДРОГО

ИНСТИТУТ ЭКОНОМИКИ И УПРАВЛЕНИЯ

КАФЕДРА СЭММ

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

ВСТРОЕННЫЕ ФУНКЦИИ
EXCEL
.

Выполнила:

Студентка гр. 2873

Иванова К.В.

Проверила:

Челпанова М.Б.

Величавый Новгород

2008

1. Цели работы:

1. Исследование главных функций в ЭТ.

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

2. Ход работы:

1.
Заполнили приведенную таблицу.

Фамилия
Имя
Дата рождения
№ группы
Математика
История
Информатика
Ср. балл

Жукова
Екатерина
16.02.1986
4569
3
2
4
3,0

Сухов
Андрей
25.10.1987
5433
3
2
4
3,0

Самойлов
Дмитрий
20.11.1987
4569
5
5
5
5,0

Данилов
Александр
12.12.1987
5433
5
5
5
5,0

Валеев
Даниэль
19.02.1988
5433
4
4,4
5
4,5

Андреева
Юлия
12.04.1988
4785
3
2
5
3,3

Рахний
Ира
27.04.1988
5433
4
5
5
4,7

Стречень
Ира
26.12.1988
5433
5
4
5
4,7

Волкова
Анна
17.06.1989
4569
4
4
4
4,0

2.
Отсортировали данные таблицы по номерам групп, и в алфавитном порядке по фамилиям в каждой группе.

Данные – Сортировка – Сортировать по № группы
, потом по Фамилия
, в последнюю очередь по имя
– ОК

Фамилия
Имя
Дата рождения
№ группы
Математика
История
Информатика
Ср. балл

Волкова
Анна
17.06.1989
4569
4
4
4
4,0

Жукова
Екатерина
16.02.1986
4569
3
2
4
3,0

Самойлов
Дмитрий
20.11.1987
4569
5
5
5
5,0

Андреева
Юлия
12.04.1988
4785
3
2
5
3,3

Валеев
Даниэль
19.02.1988
5433
4
4,4
5
4,5

Данилов
Александр
12.12.1987
5433
5
5
5
5,0

Рахний
Ира
27.04.1988
5433
4
5
5
4,7

Стречень
Ира
26.12.1988
5433
5
4
5
4,7

Сухов
Андрей
25.10.1987
5433
3
2
4
3,0

3.
Сделали поле возраст
(опосля Даты рождения
) – Вставка – Столбец. Считаем возраст студентов:

=СЕГОДНЯ()-Е3.

Приобретенный итог представляем в формате Год – формат ячейки – избираем подходящий формат (ГГ) – ОК.

4.
Определяем самого юного студента при помощи мастера функций: =МИН(E3:E11)

5.
Добавляем к списку с данными о студентах столбец «Стипендия» — Вставка – Столбец.

6.
Назначаем дифференцированную стипендию: если средний балл студента равен 5, завышенная стипендия, (50% от 600 руб.), средний балл от 4 до 5 и все экзамены сданы без троек – стипендия назначается в размере 600 руб., остальным студентам стипендия не назначается:

=ЕСЛИ(J3=5;600*0,5+600;ЕСЛИ(И(И(J3>=4;J3<5);И(G3>3;H3>3;I3>3));600;0))

7.
Расчеты с внедрением функций баз данных:

Ср. балл
Кол-во студентов

>4,5
=БСЧЁТ(B2:J11;J3;A17:A18)

а.
Задаем аспект: копируем заглавия таблицы Ср. балл
и № группы
, в ячейке под ср. баллом условие >4.5. Выбираю функцию БСЧЕТ, задаем базу данных, поле, аспект – ОК.

Ср. балл
Кол-во студентов

>4,5
4


№ группы
Ср.балл по матем.

5433
=ДСРЗНАЧ(B2:J11;G2;A21:A22)

b
.
Задаем аспект: копируем заглавия таблицы № группы
, в ячейке под № группы
условие – 5433. Избираем функцию ДРСРЗНАЧ, задаем базу данных, поле, аспект – ОК.

Стипендия
Ср. балл
Кол-во студентов
сумма

900
5
2
1800


№ группы
Ср.балл по матем.

5433
4,2

с.
Задаем аспект: копируем заглавия таблицы № группы, Математика, История, Информатика
; под предметами вводим оценки – 4. Избираем функцию БСЧЕТ, задаем базу данных, поле, аспект – ОК.

Матем
История
Информатика
Кол-во студентов

4
4
4
1


Матем
История
Информ
Кол-во студентов

4
4
4
=БСЧЁТ(A2:J11;H2;A25:C26)


Математика
История
Информатика
Кол-во студентов

4
4
4
1

d
.
Задаем аспект: копируем заглавия таблицы Математика, История, Информатика
и № группы
, в ячейках под Математика, История, Информатика
условие 5, а под № группы – 5433. Избираем функцию БСЧЕТ, задаем базу данных, поле, аспект – ОК.

Матем
История
Информ
Кол-во студентов

4
4
4
=БСЧЁТ(A2:J11;H2;A25:C26)

е.
Задаем аспект: копируем заглавия таблицы Стипендия
и Средний балл, количество отличников.
Избираем функцию БДСУММ, задаем базу данных, поле, аспект – ОК.

Стипендия
Ср. балл
Кол-во студентов
сумма

900
5
2
=БДСУММ(A2:J11;C2;F14:H15)

Итог под ячейкой Сумма.

f
. Задаем аспект: копируем заглавия таблицы Дата рождения
дважды. Под ними пишем интервал от 01.01.1987 до 31.12.1987. В ячейке

Дата рождения
Дата рождения
Кол-во студентов

>=01.01.1987
<=31.12.1987
=БСЧЁТ(A2:J11;D2;F17:G18)

количество студентов
вводим функцию БСЧЕТ, задаем базу данных, поле, аспект – ОК.

Дата рождения
Дата рождения
Кол-во студентов

>=01.01.1987
<=31.12.1987
3

g
.
Задаем аспект: копируем заглавия таблицы № группы, Математика, История, Информатика
, в первой строке под арифметикой вводим 2, потом на последующей строке под историей – 2 и на третьей под информатикой – 2 поначалу считаем неуспевающих в группе 5433, потому под заголовком № группы ввожу- 5433. Избираем функцию БСЧЕТ, задаем базу данных, поле, аспект — ОК. Подобные операции производятся при подсчете неуспевающих в иной группе.

Матем
История
Информ
№ группы
Кол.студентов

2
5433
1

2
5433

2
5433


Матем
История
Информ
№ группы
Кол.студентов

2
5433
=БСЧЁТ(B2:J11;G2;F21:I24)

2
5433

2
5433

9. Исполняем задания, используя форму данных:

а.
Чтоб просмотреть данные о студентах, фамилия которых начинается с буковкы А:

Меню – Данные – Форма – Аспекты – вводим в ячейку Фамилия – А* — Дальше — просматриваем данные.

b
.
Чтоб просмотреть данные о студентах, получающих стипендию в размере 600 руб.:

Данные – Форма – Аспекты – вводим в ячейку Стипендия – 600 – Дальше – просматриваем данные.

c
.
Чтоб просмотреть данные о студентах, имеющих средний балл >4:

Данные – Форма – Аспекты – вводим в ячейку Ср. балл условие — >4 — Дальше — просматриваем данные.

10.Исполняем задания, используя фильтрацию данных:

а.
Чтоб вывести на экран о студентах, получающих завышенную стипендию, выполняю последующие операции:

Задаем аспект – копируем заглавия столбцов Стипендия
и №группы,
в ячейке под стипендией вводим – 900 – Меню – Данные – Фильтр – Расширенный фильтр – задаем спектр критерий – ОК.

имя
Стипендия
Дата рождения
Возраст
№ группы
Математика
История
Информатика
Ср. балл

Дмитрий
900
20.11.1987
18
4569
5
5
5
5,0

Александр
900
12.12.1987
18
5433
5
5
5
5,0

b
.
Чтоб вывести на экран сведения о отличниках по информатике и арифметике – задаем аспект – копируем заглавия столбцов Математика, Информатика и №группы,
в ячейке под арифметикой и информатикой вводим 5 – Данные – Фильтр – Расширенный фильтр – задаем спектр критерий – ОК.

Математика
Информатика

5
5


имя
Стипендия
Дата рождения
Возраст
№ группы
Математика
История
Информатика
Ср. балл

Дмитрий
900
20.11.1987
18
4569
5
5
5
5,0

Александр
900
12.12.1987
18
5433
5
5
5
5,0

Ира
600
26.12.1988
16
5433
5
4
5
4,7

с.
Чтоб вывести на экран сведения о всех студентах, неуспевающих по какому- или предмету — задаем аспект – копируем заглавия столбцов Математика, История, Информатика и №группы
в первой строке под арифметикой вводим 2, потом на последующей строке под историей – 2 и на третьей под информатикой – 2 – Данные – Фильтр — Расширенный фильтр — задаем спектр критерий – ОК

Математика
История
Информатика

2

2

2


Фамилия
имя
Ст.
Д.Р.
Возраст
№
Математика
История
Информатика
Ср. балл

Жукова
Екатерина
0
16.02.1986
19
4569
3
2
4
3,0

Андреева
Юлия
0
12.04.1988
17
4785
3
2
5
3,3

Сухов
Андрей
0
25.10.1987
18
5433
3
2
4
3,0

d
.
Чтоб вывести на экран сведения о всех студентах одной из групп, родившихся в 1987 году задаем аспект – копируем заглавия столбцов Дата рождения
дважды и № группы
. Под ними пишем интервал от 01.01.1987 до 31.12.1987 и номер группы 4569. Меню —
Данные – Фильтр — Расширенный фильтр — задаем спектр критерий – ОК

Дата рождения
Дата рождения
№ группы

>=01.01.1987
<=31.12.1987
4569


Фамилия
имя
Ст.
Дата рождения
Возраст
№
Математика
История
Информатика
Ср.

Самойлов
Дмитрий
900
20.11.1987
18
4569
5
5
5
5,0

3. Вывод:

Исследовала главные функции в ЭТ.

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