Лаб работы Excel

10

Таблица 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	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

2.Отсортировать данные таблицы по № группы, затем по полю Фамилия, в последнюю очередь по полю Имя.

3.Создать поле Возраст (после поля Дата рождения). Рассчитать возраст студентов по формуле: =(СЕГОДНЯ()-С2)/365. Полученный результат представить в формате Числовой без знаков после запятой. Результат представлен на рис. 10.

Рис. 10. Таблица сведений о студентах и оценках

11

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

Использование функций в Excel. Построение графиков

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

Автозаполнение

Для ввода последовательности данных, отличающихся друг от друга на определённый шаг, используется механизм автозаполнения.

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

Автозаполнение действует для всех типов данных. При этом числа и даты – увеличиваются (или уменьшаются), а текст – повторяется. Например, можно быстро создать список товаров с номером (рис. 11).

Рис. 11. Окно Excel с автозаполнением ячеек

12

Функции

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

2.Функции даты и времени. При вводе даты или времени пользователь вводит последовательность символов, которая не является числом, но с этими символами можно производить вычисления: сравнивать, складывать, вычитать.

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

ABS(X)	–	возвращает модуль числа;
COS(X), SIN(X),TAN(X)	–	возвращает косинус, синус и тангенс
		угла;
ACOS(X), ASIN(X), ATAN(X)	– возвращает		арккосинус,	арксинус	и
		арктангенс числа;
EXP(X)	– возвращает		число e, возведённое		в
		степень X;
КОРЕНЬ(X)	–	возвращает	значение	квадратного
		корня;
ПИ()	–	возвращает число π с точностью до
		15-го знака;
СУММ(A;B;C)	–	возвращает сумму всех чисел, входя-
		щих в список аргументов.

4. Статистические функции предназначены для проведения различного рода статистических вычислений: максимальное и минимальное значение диапазона, значения вероятностей, распределения случайных величин, средние, дисперсии и т.д.

ДИСП(число1;число2; ...) – оценивает дисперсию по выборке;

МИН(число1;число2; ...) – возвращает наименьшее значение в списке аргументов;

МАКС(число1;число2; ...) – возвращает наибольшее значение в списке аргументов;

СРЗНАЧ(число1;число2; ...) – возвращает среднее значение в списке аргументов;

13

5.Текстовые функции позволяют выполнять различные действия по обработке текстовой информации: определение количества символов в строке, преобразование текста в числовое значение, изменение регистра и т.д.

6.Логические функции. Всякий раз, когда необходимо реализовать те или иные действия, в зависимости от выполнения ка- ких-либо условий, следует использовать логические функции.

Имеются следующие логические функции: ЕСЛИ, И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ. Результатом работы логических функций И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ является логическое значение ИСТИНА или ЛОЖЬ, а результатом работы логической функции ЕСЛИ может быть число, текст или ссылка на выполнение какихлибо действий. Функция НЕ(Аргумент) изменяет логическое значение своего аргумента на противоположное. Функция ЕСЛИ(арг_лог;арг1;арг2) возвращает значение арг1, если арг_лог при вычислении приобретает значение ИСТИНА, и арг2, если арг_лог принимает значение ЛОЖЬ.

Например, пусть в ячейку B1 введена формула

=ЕСЛИ(А1<0;šотрицательноŸ;šположительноŸ)

То есть, если в ячейке А1 будет отрицательное число, то в ячейке В1 появится слово šотрицательноŸ и наоборот.

Таким образом, функции в Excel используются для выполнения стандартных вычислений. Значения, которые употребляются для вычисления функций, называются аргументами, а значения, возвращаемые функциями в качестве ответа, называются результатами. Чтобы использовать какую-либо функцию, следует ввести её как часть формулы в ячейку.

Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом š;Ÿ. Между именем функции и скобками не допускается вставлять пробел. Например, функция суммы двух аргументов запишется так:

=СУММА(A1;B2)

14

Excel содержит более 400 встроенных функций. Вводить в формулу названия функций и значений входных параметров с клавиатуры не всегда удобно, поэтому целесообразно использовать специальное средство для работы с функциями, которое называется Мастер функций, вызываемый нажатием на кнопку fx рядом со строкой формул (рис. 12).

Рис. 12. Окно Мастера функций

При работе с этим средством выбирается нужная категория (финансовые функции, математические, статистические, для работы с базой данных и др.), затем функция из списка. В следующем окне вводятся аргументы функции. Например, для нахождения максимального значения ряда чисел, записанных в ячейках А1:А10, используется функция МАКС. Окно ввода аргументов функции МАКС представлено на рис. 13.

Рис. 13. Окно ввода аргументов функции МАКС

15

Пример 1: Найти сумму ряда чисел, записанных в ячейках А1:А10 (рис. 14). Для ввода чисел от 1 до 10 использовать автозаполнение. Результат представить в ячейке А11.

Рис. 14. Окно расчёта суммы аргументов

спомощью Мастера функций

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

Функция ЕСЛИ используется при проверке условий для значений и формул. Синтаксис:

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь),

где логическое_выражение: условие, которое требуется проверить;

значение_если_истина: значение, возвращаемое, если условие истинно;

значение_если_ложь: значение, возвращаемое, если условие ложно.

16

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

Функция ЕСЛИ вызывается следующим образом:

1. В нужной ячейке после знака š=Ÿ следует нажать на кнопку fx – вставка функции.

2. Появится окно ввода аргументов функции, куда следует ввести условие и значения, если условие верно и если оно не верно.

Например, пусть в ячейку B2 выдаётся результат в зависимости от условия, которое следует проверить (рис. 15). Если число в ячейке А2 меньше либо равно числу 100, формула отображает строку šВнутри бюджетаŸ. В противном случае – строку šВне бюджетаŸ. Окно ввода аргументов показано на рис. 15. Результат представлен на рис. 16.

Рис. 15. Окно ввода аргументов функции ЕСЛИ

17

Рис. 16. Окно Excel с результатом вычисления

Пример 2: На промежутке x [ 5;5 ] вычислить значения функции: y = x3 2x 2 + x 1 при x≥0, и y = sin(x 1) в остальных случаях.

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

В столбец А с помощью автозаполнения вводится диапазон изменения аргумента х от –5 до 5. В столбце В должны содержаться значения функции y, соответствующие значениям x.

Расчёт значений функции производится также с помощью функции ЕСЛИ. Окно ввода аргументов функции ЕСЛИ представлено на рис. 17. Результат вычисления представлен на рис. 18.

Рис. 17. Окно ввода аргументов функции ЕСЛИ

18

Рис. 18. Окно Excel с результатом вычисления

Построим график полученной функции.

1.Выделить оба столбца данных (без названий x и y).

2.На вкладке Вставка выбрать пункт Точечная и в появившемся меню выбрать диаграмму с линиями.

Рис. 19. Окно Мастера диаграмм шаг 1

19

3.Чтобы добавить название диаграммы, нужно перейти на вкладку Макет, щёлкнуть по кнопке Название диаграммы и в появившемся меню выбрать пункт Над диаграммой. В области диаграммы появится надпись šНазвание диаграммыŸ. Для того чтобы эту надпись изменить, нужно щёлкнуть по ней правой кнопкой мыши и в появившемся меню выбрать пункт Изменить текст. Вместо текста šНазвание диаграммыŸ вводится общий вид функции y. Показатель степени следует пока ввести без указания регистра (y=x3-2x2+x-1, x≥0; y=sin(x-1), x<0). Затем следует выделить нужный показатель степени, нажать правую кнопку мыши, выбрать команду Шрифт. Должно появиться одноимённое окно (рис. 20). В разделе Видоизменение поставить галку –

надстрочный. ОК.

4.Для добавления названий осей нужно щёлкнуть по команде Названия осей. В появившемся меню выбрать сначала Название основной горизонтальной оси → Название под осью, а затем Название основной вертикальной оси → Горизонтальное название.

Результат представлен на рис. 21.

Рис. 20. Окно Формат названия диаграммы