Встроенные функции Excel

МИНИСТЕРСТВО  ОБРАЗОВАНИЯ РФ

НОВГОРОДСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ

ИМЕНИ ЯРОСЛАВА МУДРОГО

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



КАФЕДРА СЭММ











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

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





Выполнила:

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

Иванова К.В.

Проверила:

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















Великий Новгород

2008


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



1. Изучение основных функций в ЭТ.

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



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



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

	Фамилия

	Имя

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

	№ группы

	Математика

	История

	Информатика

	Ср. балл

	Жукова

	Екатерина

	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

	

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

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



Фамилия

Имя

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

№ группы

Математика

История

Информатика

Ср. балл

Волкова

Анна

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.

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

	

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

	

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

	

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

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



		Расчеты с использованием функций баз данных:



Ср. балл

Кол-во студентов

>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. Вывод: 

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

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