Использование стандартных функций

⇐ ПредыдущаяСтр 6 из 13Следующая ⇒

Функция в составе формулы вызывается по имени, после которого в круглых скобках указываются один или несколько аргументов, разделенных точкой с запятой. В случае отсутствия у функции аргументов, скобки после ее имени остаются пустыми. Аргументами функции могут быть константы, ссылки или выражения. Зная синтаксис функции можно ввести ее в формулу вручную. Но, как правило, для вставки функции используют Мастер функций. Для его вызова можно выполнить команду Вставка | Функция или щелкнуть по кнопке в строке формул с надписью f_x. После этого появляется диалоговое окно 1-го шага Мастера функций, в котором необходимо выбрать нужную категорию (математические, финансовые, логические и т.д.) и требуемую функцию в этой категории. Причем если активная ячейка не содержала в себе формулу, то знак равенства вставляется в нее автоматически. После выбора функции и нажатия кнопки ОК, появляется диалоговое окно, в котором необходимо ввести аргументы функции. Закончив ввод аргументов, нужно нажать кнопку ОК и вставка функции будет завершена.

Пример 2: вычислить значения функции на отрезке xÎ [0; 0, 5] с шагом h = 0, 1 и определить среди найденных величин количество отрицательных и сумму положительных значений функции.

В ячейках А1 и В1 укажем заголовки столбцов с данными. Значения аргумента функции введем с помощью автозаполнения (рис 3, а). Далее в ячейку B2 введем формулу для вычисления первого значения заданной функции. Для этого сделаем ячейку активной и выполним команду Вставка | Функция. В категории Математические выберем функцию TAN и нажмем ОК. В следующем окне Мастера функций необходимо ввести аргумент функции. В поле Число введем выражение: A2^2-1, причем для получения ссылки на ячейку используем щелчок левой кнопкой мыши. Нажав ОК, получим формулу, состоящую из одной функции тангенс. Для ввода второго слагаемого установим курсор в строку формул, введем плюс и вновь вызовем Мастер функций. В категории Математические выберем функцию СТЕПЕНЬ и нажмем ОК. Далее в поле Число введем выражение A2+1, а в поле Степень – показатель 1/3. После нажатия кнопки ОК получим окончательную формулу, которую скопируем в ячейки B3: B7 с помощью автозаполнения (рис 3, б).

Рис. 4.3. Ввод исходных данных: аргументов функции (а) и ее значений (б)

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

Рис. 4. Вычисление количества отрицательных (а) и суммы положительных (б) значений функции

Установить требуемые значения в ячейках столбца можно с помощью функции ЕСЛИ. Эта функция возвращает одно из двух значений, в зависимости от истинности логического выражения. Установим указатель в ячейку C2 и вызовем Мастер функций. В категории Логические выберем функцию ЕСЛИ. На следующем шаге в поле Лог_выражение введем B2< 0, а в поля Значение_если_истина и Значение_если_ложь значения 1 и 0 соответственно. Заполним созданной формулой диапазон C3: C7. Далее нажмем кнопку с символом “∑ ” (автосумма) и в ячейке C8 получим искомое значение (рис. 4.4, а).

Для вычисления суммы положительных значений будем использовать функцию СУММЕСЛИ, которая суммирует содержимое тех ячеек заданного диапазона, которые удовлетворяют некоторому критерию. Сделаем активной ячейку C9 и вызовем Мастер функций. В категории Математические выберем функцию СУММЕСЛИ. Далее в поле Диапазон введем B2: B7, а в поле Критерий выражение > 0. После ввода аргументов нажмем ОК и получим требуемое значение (рис. 4, б).

Построение диаграмм

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

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

Если диаграмма расположена на листе данных, то после построения ее размеры можно изменять, как и у любого графического объекта. Также можно изменять любые параметры диаграммы, установленные при ее создании: тип, диапазон исходных данных, параметры и место размещения. Для этого необходимо вызвать контекстное меню диаграммы и выбрать соответствующую команду.

Работа со списками данных

Большинство таблиц, создаваемых в Excel, являются какими-либо списками. Для большей эффективности при работе со списком, его структура должна соответствовать следующим правилам:

- верхняя строка таблицы должна содержать уникальные заголовки столбцов, содержимое которых располагается ниже;

- каждый столбец должен содержать однородные данные;

- каждая строка имеет одинаковую структуру;

- в таблице не должно быть пустых строк и столбцов.

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

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

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

В Excel существует два варианта наложения фильтра: автофильтрация и расширенный фильтр. Первый обычно используется для установки простых условий отбора, второй – для фильтрации с применением более сложных критериев. Наиболее просто в Excel производится автофильтрация. Для ее применения необходимо сделать активной любую ячейку списка и выполнить команду Данные | Фильтр| Автофильтр. В заголовке каждого столбца появится значок раскрывающегося списка (рис. 5).

Рис. 4.5. Вид списка после включения режима автофильтрации

Щелкнув на значке в том столбце, по содержимому которого будет производиться фильтрация, можно выбрать один из нескольких вариантов отбора. Для того чтобы оставить видимыми строки с одним конкретным значением, его необходимо выбрать из списка вариантов фильтрации. Выбрав команду Первые 10 можно указать сколько наибольших (наименьших) значений останется после наложения фильтра. Более сложные критерии отбора можно установить, выбрав команду Условие. В диалоговом окне можно установить два условия, объединив их логической связкой «И» или «ИЛИ». Для примера произведем отбор тех записей, у которых должностной оклад больше 2000 р. и меньше 6000 р. (рис. 4.6, а). Введем каждое из условий в соответствующие строки диалогового окна (рис. 6, а) и нажмем ОК. Результат фильтрации показан на рис. 6, б.

Рис. 6. Диалоговое окно ввода условий фильтрации (а) и ее результаты (б)

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

Контрольные задания.

1. Запустите приложение Microsoft Excel и создайте с его помощью файл с именем Lab.xls.

2. Переименуйте листы книги MS Excel: первый в «Задание», второй лист в «Данные», третий лист – «График».

3. Выберите из таблицы вариант задания соответственно номеру в журнале группы.

Варианты задания

Вари-ант	Функция f(x, a)	Интервал изменения х	Шаг прира-щения х	Значения параметра а	Критерий отбора значений функции
1-е	2-е	3-е
	arccos(a+ 6 x²)	[-0, 8; 0, 8]	0, 08	-0, 75	-0, 9	-1	больше 2
	sin(a²-0, 5*x)	[-3; 6]	0, 45	-0, 9			больше 0, 25
	(sin x + a)³	[-1; 5]	0, 3		-5		меньше 200

	│ a^x -15 x│	[-3; 3]	0, 3			3, 5	больше 10
	(x + cos a)²	[-5; 4]	0, 45		-1	5, 3	больше 10
	sin(a+ 10 x²)	[-1; 1]	0, 1		-2		больше 0, 9
	(a + cos x)²	[-4; 4]	0, 4	-3		-1, 5	меньше 5
	arctg(x)*a³	[-10; 10]		-1			меньше 0
	(2x + sin a)³	[-2; 1]	0, 15			-5	меньше -3
	ln(x²+a)	[-10; 10]					больше 2
	lg(x+a)²	[-9; 0]	0, 45		4, 4		больше 1
	e^3a+x	[0; 5]	0, 25	-2			меньше 3
	arcsin(a-0, 4^x)	[-0, 5; 1, 2]	0, 085	0, 6		1, 3	меньше 0
	tg(a - e^x)	[-5; 0]	0, 25	-2	0, 1	1, 5	больше 7
	(a + e^x)^0.5	[-3; 3]	0, 3				больше 3

4. На листе «Задание» объедините несколько ячеек. Используя шрифт Times New Roman, размер шрифта 12, горизонтальное и вертикальное выравнивание в ячейке по центру, наберите следующую информацию:

Вариант №__ (укажите номер варианта выполняемого задания)

Выполнили студенты группы (укажите группу)

ФИО (перечислите фамилии студентов, выполняющих данное задание)

Функция (приведите вид функции у = f(x, a) для Вашего варианта)

Значение параметраа (в отдельной ячейке укажите первое значение параметра а )

Начальное значение аргумента (в отдельной ячейке укажите начальное значение аргумента)

Шаг приращения аргумента (в отдельной ячейке укажите шаг приращения аргумента).

5. На листе «Данные» для табулирования функции y = f(x, a) создайте таблицу №1 по шаблону, приведенному на рис. 4.7.

6. Заполните столбец значений аргумента х. Обязательно используйте ссылку на ячейки с начальным значением аргумента х и шагом приращения аргумента х, расположенные на листе «Задание».

7. Заполните столбец значений функции y. Обязательно используйте ссылку на ячейку со значением параметра а, расположенную на листе «Задание».

8. В четвертом столбце укажите критерий отбора значений функции для вашего варианта: например, y > 2 – для первого варианта,
y > 0, 25 – для второго и т.д. В ячейку, находящуюся ниже, введите формулу с использованием функции ЕСЛИ, которая выводит в данной ячейке 1, если значение функции у в соседней ячейке соответствует критерию отбораи 0 в противном случае (см. рис. 7). Произвести автозаполнение ячеек данного столбца.

Рис. 7. Пример оформления таблицы

9. Под табл. 1 выведите количество значений функции, удовлетворяющих критерию отбора (см. рис. 7). Для этого используйте автосуммирование (кнопка с символом “∑ ”).

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

11. Вставьте в рабочую книгу новый лист и назовите его «Сортировка». Выделите табл. 1 на листе «Данные» и скопируйте ее в буфер обмена. Перейдите на лист «Сортировка», вызовите диалоговое окно команды Правка | Специальная вставка, поставьте переключатель в положение «Вставить значения». Произведите вставку значений табл. 1. Произведите сортировку значений функции упо убыванию.

12. Скопируйте лист «Данные» в конец книги. Назовите появившийся лист «Фильтрация», а скопированную таблицу - «Таблица № 2». Используя автофильтрацию, оставьте в табл. 2 только те строки, значения функции ув которых удовлетворяют критерию отбора.

13. Замените первое значение параметра а на листе «Задание» сначала вторым его значением, а затем третьим (см. седьмой и восьмой столбцы в таблице вариантов задания). Проследите, изменяются ли значения функции, вид графика, представление данных на листах «Сортировка» и «Фильтрация».

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

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

16. Продемонстрируйте результаты выполнения работы и отчет преподавателю.

ЛАБОРАТОРНАЯ РАБОТА №8.

⇐ Предыдущая 1 2 3 4 567 8 9 10 Следующая ⇒