Встроенные функции 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 |
Данилов |
|
|
5433 |
5 |
5 |
5 |
5,0 |
Валеев |
Даниэль |
19. 02. 1988 |
5433 |
4 |
4,4 |
5 |
4,5 |
Андреева |
Юлия |
|
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 |
Андреева |
Юлия |
|
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.
Создали поле Возраст
(после Даты рождения
) – Вставка – Столбец. Считаем возраст студентов:
4.
Определяем самого молодого студента с помощью мастера функций: =МИН(E3:E11)
5.
Добавляем к списку с данными о студентах столбец «Стипендия» - Вставка – Столбец.
6. 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 – Далее – просматриваем данные.
c
.
Чтобы просмотреть данные о студентах, имеющих средний балл >4:
Данные – Форма – Критерии – вводим в ячейку Ср. балл условие - >4 - Далее - просматриваем данные.
10. Выполняем задания, используя фильтрацию данных:
а.
Чтобы вывести на экран о студентах, получающих повышенную стипендию, выполняю следующие операции:
Задаем критерий – копируем заголовки столбцов
Имя |
Стипендия |
|
Возраст |
№ группы |
Математика |
История |
Информатика |
Ср. балл |
Дмитрий |
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 |
Имя |
Стипендия |
|
|
|
Математика |
История |
Информатика |
Ср. балл |
Дмитрий |
900 |
|
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
. Дата рождения
два раза и № группы
. Под ними пишем интервал от 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. Вывод:
Изучила основные функции в ЭТ.
Научилась использовать встроенные функции для решения конкретных задач.
|