Встроенные функции 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
|
|
Андреева
|
Юлия
|
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
|
|
Самойлов
|
Дмитрий
|
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
|
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. Вывод:
Изучила основные функции
в ЭТ.
Научилась
использовать встроенные функции для решения конкретных задач.
Похожие работы на - Встроенные функции Excel
|