Лаб работы Excel

30

Пример 3: Отобрать с помощью автофильтра студентов, обучающихся в группе № 5433 с фамилией, начинающейся на букву С.

Последовательность действий

1.Скопировать базу данных (рис. 30) на Лист 3.

2.Открыть раскрывающийся список в столбце Фамилия.

3.Выбрать из списка пункт Текстовые фильтры → Настраиваемый фильтр. В появившемся окне Пользовательский автофильтр выбрать критерий отбора начинается с, в поле напротив ввести нужную букву (проверить, чтобы раскладка была русскоязычная). Нажать ОК.

4.Открыть раскрывающийся список в столбце № группы.

5.Выбрать нужный номер.

Фильтрация записей в базе данных с помощью расширенного фильтра

Расширенный фильтр позволяет отыскивать строки с помощью более сложных критериев, по сравнению с пользовательскими автофильтрами. Расширенный фильтр использует для фильтрации данных интервал критериев.

При использовании расширенного фильтра имена столбцов, по которым задаются условия, копируются ниже исходной таблицы. Под названиями столбцов вносятся критерии отбора. После применения фильтра на экране могут отображаться только те строки, которые удовлетворяют указанному критерию, а также отфильтрованные данные могут копироваться на другой лист или в другую область на том же рабочем листе.

Пример 4: Выбрать всех студентов из группы № 5433, у которых средний балл больше либо равен 4,5.

Последовательность действий

1.Скопировать базу данных (рис. 30) на Лист 4.

2.Скопировать названия столбцов № группы и средний балл

31

в область ниже исходной таблицы. Под названиями столбцов ввести нужные критерии отбора (рис. 32)

Рис. 32. Окно Excel с расширенным фильтром

2.На вкладке Данные на панели инструментов Сортировка

ифильтр выбрать пункт Дополнительно. Появится диалоговое окно (рис. 33), в котором указываются диапазоны данных.

Рис. 33. Окно расширенного фильтра

Вполе ввода Исходный диапазон указывается интервал, содержащий исходную базу данных. В нашем случае выделяется диапазон ячеек с А1 по I9.

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

Вполе ввода Поместить результат в диапазон указывается интервал, в который копируются строки, удовлетворяющие кри-

32

териям. В нашем случае указывается ячейка ниже области критериев, например А16. Это поле доступно только в том случае, когда выбран переключатель Скопировать результат в другое место.

Флажок Только уникальные записи предназначен для отображения только неповторяющихся строк.

Результирующая таблица, удовлетворяющая критериям фильтрации, представлена на рис. 34.

Рис. 34. Окно Excel с результатами фильтрации

Задания для самостоятельной работы

1.Создать свою базу данных, количество записей в которой должно быть не менее 15, а количество столбцов – не менее 6. Например, база данных Список клиентов (рис. 35).

2.К базе данных применить три автофильтра (на отдельных листах). Количество критериев должно быть не менее двух.

3.Применить три расширенных фильтра к записям базы данных, каждый из которых должен содержать не менее двух критериев. Все расширенные фильтры разместить на одном листе под исходной таблицей.

33

Рис. 35. Окно Excel с базой данных Список клиентов

34

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

Численное дифференцирование и простейший анализ функций

Цель работы: Исследовать функцию на экстремум, научиться определять критическую точку.

Из курса математики известно, что формула производной в общем виде выглядит так:

f ' (x)= lim	f x + Δx x	,
Δx 0	Δx

где Δx – приращение аргумента; x – число, стремящееся к нулю. С помощью производной можно определить критические точки функции – минимумы, максимумы или перегибы. Если значение производной функции при каком-либо значении x равно нулю, то при этом значении x функция имеет критическую точку.

Пример 1: Функция f x = x 2 + 2x 3 задана на интервале x 5;5 . Исследовать поведение функции f(x).

Последовательность действий

1.Пусть Δx = 0,00001. В ячейку A1 ввести: šDx=Ÿ (рис. 36). Выделить букву D, щёлкнуть правой кнопкой мыши по выделенной букве, выбрать Формат ячеек. На вкладке Шрифт выбрать шрифт Symbol. Буква D превратится в греческую букву ѓў. Выравнивание в ячейке можно сделать по правому краю. В ячейку B1 внести значение 0,00001.

2.В ячейках с А2 по F2 оформить šшапкуŸ таблицы, как показано на рис. 36.

3.В столбце A, начиная с третьей строки, будут содержаться значения x. В ячейки с A3 по A13 ввести значения от –5 до 5.

4.В ячейке B3 записать формулу =A3^2+2*A3-3 и растянуть её до конечного значения x (до 13-й строки).

5.Чтобы определить производную функции и вычислить её значения на заданном интервале, необходимо сделать промежу-

35

точные вычисления. В ячейку С3 ввести формулу суммы аргумента x и его приращения Δx . Формула имеет вид: =A3+$B$1. Растянуть её значение до конечного значения аргумента x.

Рис. 36. Окно Excel с исследованием поведения функции

6.В ячейку D3 записать формулу =C3^2+2*C3-3, по которой вычисляется значение функции f от аргумента x Δx . Растянуть получившееся значение до конечного значения аргумента.

7.В ячейку E3 записать формулу производной (1), учитывая, что значения f x находятся в B3, а значения f x + Δx в D3.

Формула будет иметь вид: =(D3-B3)/$B$1.

8. Определить поведение функции на заданном промежутке (возрастает, убывает или имеется критическая точка). Для этого необходимо в ячейку F3 самостоятельно записать формулу для определения поведения функции. Формула содержит три условия:

	если	f' (x) < 0	– функция убывает;
	если	f' (x) > 0	– функция возрастает;
	если	f' (x)= 0	– имеется критическая точка*.

9. Построить графики по значениям f x и f' (x). На графике (рис. 37) видно, что если значение производной функции равно нулю, то в этом месте у функции критическая точка.

*Из-за слишком большой погрешности вычислений, значение f'(x) может не быть равным 0. Но описать эту ситуацию всё равно необходимо.

36

Рис. 37. Диаграмма исследования поведения функции

Задания для самостоятельной работы

Функция f(x) задана на интервале x. Исследовать поведение функции f(x). Построить графики.

1.	f(x)=	x4	2x 2				9		, x [ 4;4 ]
	4		4		4
2.	f(x)=							, x [ 5;5 ]
		x2
			2x + 2
3.	f(x)= x3		3x 2		+ 2 , x [ 2;4 ]
4.	f(x)= x			4		, x [ 2;3]
				x 2 + 7

37

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

Построение касательной к графику функции

Цель работы: Освоить вычисление значений уравнения касательной к графику функции в точке x0.

Уравнение касательной к графику функции y = f(x) в точке

x0 имеет вид:
y = f(x0 )+ f' (x0 )(x x0 ) ,	(1)
где f' (x0 ) – угловой коэффициент к касательной.

Пример 1: Функция y = x 2 + 2x 3 задана на интервале x [ 5;5 ] . Построить касательную к графику этой функции в точке x0 = 1.

Последовательность действий:

1. Продифференцировать численно эту функцию (см. Лабораторную работу №5). Таблица исходных данных показана на рис. 38.

Рис. 38. Таблица исходных данных

2. Определить в таблице местоположение x , x0 , f(x0 ) и f' (x0 ) . Очевидно, что в качестве x будут выступать значения из

38

столбца A, начиная с третьей строки (рис. 38). Если x0 = 1, то в качестве x0 будет выступать ячейка A9. Соответственно, значение функции f в точке x0 находится в ячейке B9, а значение f' (x0 )

–в ячейке E9.

3.В столбце F рассчитывается уравнение касательной к графику функции f(x). При расчёте уравнения (1) необходимо, чтобы значения x0 , f(x0 ) и f' (x0 ) не изменялись. Поэтому в напи-

сании адреса ячеек A9, B9 и E9 нужно использовать абсолютные ссылки на эти ячейки. Фиксация ячеек производится с помощью знака š$Ÿ. Ячейки будут иметь вид: $A$9, $B$9 и $E$9.

Рассчитать значения в столбце F самостоятельно. График представлен на рис. 39.

Рис. 39. График функции f(x) и касательная к графику в точке x=1

Задания для самостоятельной работы

Функция f(x) определена на интервале x. Рассчитать уравнение касательной. Построить касательную к графику функции в заданной точке.

					39
1.	f(x)=	x4	2x 2				9		, x [ 4;4 ] , x0 = 1
	4		4		4
2.	f(x)=							, x [ 5;5 ] , x0		= 3
		x2
			2x + 2
3.	f(x)= x3		3x 2		+ 2 , x [ 2;4 ] , x0 = 0
4.	f(x)= x			4		, x [ 2;3] , x0				= 1
				x 2 + 7

Список рекомендуемой литературы

1.Веденеева, Е. А. Функции и формулы Excel 2007. Библиотека пользователя / Е. А. Веденеева. – СПб.: Питер, 2008. – 384 с.

2.Свиридова, М. Ю. Электронные таблицы Excel / М. Ю. Свиридова. – М.:Academia, 2008. – 144 с.

3.Серогодский, В. В. Графики, вычисления и анализ данных

вExcel 2007 / В. В. Серогодский, Р. Г. Прокди, Д. А. Козлов, А. Ю. Дружинин. – М.: Наука и техника, 2009. – 336 с.