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

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

Цель: Научится использовать различные встроенные функции

Краткие теоретические сведения

Функции Excel.

Для вычислений в Excel используются формулы. В формулах часто используются встроенные функции Excel. Всего имеется более 100 встроенных функций.

Функции разделены на категории. Полный список категорий функций и самих функций можно увидеть в окне мастера функций, которое вызывается с помощью инструмента f_x_. Инструмент находится на панели инструментов Стандартная. Окно мастера функций приведено на рисунке 2.1. В левом нижнем углу окна расположен знак «? », который вызывает подсказку по работе с функцией. Подсказка содержит подробное описание аргументов функции и результата и большое количество примеров использования функции.

Функции значительно упрощают вычисления. Например, если необходимо вычислить сумму значений, занесенных в ячейки от A1до H1, это можно сделать с помощью функции =СУММ(А1: Н1), а не с помощью длинной формулы =А1+В1+С1+...+Н1.

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

Рисунок 2.1 - Окно мастера функций

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

СРЗНАЧ(диапазон) - возвращает среднее (арифметическое) своих аргументов; например, =СРЗНАЧ(А2: А9). Функция относится к категории статистические.

МАКС(диапазон), МИН(диапазон) - возвращают максимальное и минимальное значение среди чисел указанного диапазона. Относятся к категории статистические.

ЕСЛИ(условие; выражение1; выражение2) - функция проверяет условие и вычисляет выражение1, если условие выполнилось; функция вычисляет выражение2, если условие не выполнилось. Например, результатом функции =ЕСЛИ(А2> 20; 1; 0)

будет число 1, если в ячейке А2 записано число больше 20, и 0, если в ячейке А2 записано число меньше либо равно 20.

Создание раскрывающегося списка значений.

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

° Создайте в свободном месте таблицы список возможных значений столбца;

° Выделите столбец, в который будете вводить значения из списка;

° Выберите пункт меню Данные/Проверка, появится окно диалога, в котором выберите Тип данных – Список и укажите в элементе Источник диапазон клеток, который содержит список возможных значений столбца.

Ход работы.

Задание 1.

1. Оформите расчётную таблицу по образцу:

A	B	C	D	E	F	G

Таблица учёта продажи молочных продуктов.

№ п/п	Продукт	Цена	Поставлено	Продано	Осталось	Выручка
	Молоко	3, 0 р.			=D5-E5	=C5*E5
	Сметана	4, 2 р.				294, 0 р.
	Творог	2, 5 р.				275, 0 р.
	Йогурт	2, 4 р.				540, 0 р.
	Сливки	3, 2 р.				144, 0 р.
					Итого:	1553, 0 р.

2. Для ввода заголовка таблицы Таблица учёта продажи молочных продуктов на несколько ячеек используйте операцию объединения ячеек . Для этого выделите ячейки, которые необходимо объединить (т.е. ячейки с A2 по F2 ), затем нажмите кнопку Объединить и поместить в центре , которая расположена на панели инструментов Выравнивание вкладки Главная меню.

3. Заполняя таблицу, обратите внимание, что в колонке B и строке номер 4 электронных таблиц информация представлена в видетекстовых данных, в столбцах A, C, D и Е – в виде числовых данных, а в столбцах F и G – в виде формул. При этом распознавание типа данных Excel выполняет автоматически!

4. Заметьте! В колонке C и G не нужно дописывать букву р. после указания стоимости товара в рублях. Необходимо, после заполнения колонки, изменитьформат числовых данных на Денежный. ( указанием одного знака после запятой, т.е. копеек).

Это можно сделать следующим образом: выделяем нужную колонку, щёлкая левой клавишей мыши по названию столбца, например C. Затем заходим на панель инструментов Число вкладки Главная меню, (также можно воспользоваться контекстным меню), там выбираем пункт меню Формат ячеек и команду Число (в меню раскрывшегося окна).Затем выбираем соответствующий формат числовых данных – Денежный. Не забудьте указать необходимое количество знаков после запятой.

5. При заполнении колонок F и G используются формулы! Колонка Осталось вычисляется по формуле: количество поставленного товара минус количество проданного товара (пример формулы приведён в ячейке F5 ). Колонка Выручка вычисляется по формуле: стоимость товара, умноженная на количество проданного товара (пример формулы также приведён в ячейке G5 ).

Обратите внимание! Чтобы не заполнять вручную формулу в каждой строке колонки Осталось и Выручка можно воспользоваться Маркером автозаполнения. Для этого введите формулу в ячейку F5, затем в правом нижнем углу текущей ячейки вы увидите черный квадратик - маркер автозаполнения . При наведении на него указатель мыши приобретает форму тонкого черного крестика. Перетащите этот «крестик» вниз, при помощи мыши, до ячейки F9 и вы увидите как формула скопируется в соответствующие ячейки, при этом Excel увидев закономерность, автоматически изменит адреса ячеек, используемых в формуле (т.е. если в ячейке F5 формула имеет следующий вид: =D5-E5, то в ячейке F6 она уже будет выглядеть так: =D6-E6)!

6. Для подсчёта итоговой выручки в ячейке G10 используйте функцию автоматического суммирования . Для этого установите текущую ячейку G10, затем нажмите кнопку Автосумма на панели инструментов Редактирование. Затем, при помощи мыши выделите диапазон ячеек, которые нужно сложить, т.е. ячейки с G5 по G9.

Задание 2.

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

2. Аналогично переименуйте Лист 2 в лист с названием Таблица умножения. Затем удалите ненужные листы ( Лист 3 ). Для этого воспользуйтесь контекстным меню ярлычков листа.

Заметьте! С принципом относительной адресации вы познакомились при заполнении колонок Осталось и Выручка Таблицы учёта продажи …, если использовали при этом Маркер автозаполнения.

3. Перейдите на Лист Таблица умножения. Вам предлагается создать Таблицу умножения по образцу.

4. Введите заголовок Таблица умножения в ячейки C1: H1. Для этого воспользуйтесь объединением ячеек.

5. Введите в ячейку В2 – 1, в С2 – 2, выделите эти две ячейки. В правом нижнем углу прямоугольника появился чёрныйквадратик. Подведите туда курсор мыши и он примет вид чёрногокрестика – это Маркер автозаполнения. Нажмите на этот крестик левой клавишей мыши и удерживая её протащите рамку вправо до ячейки J2. Вы увидите, как ячейки автоматически заполнятся нужными значениями.

6. Аналогично заполните столбец А Таблицы умножения.

7. Установите Указатель ячейки ( курсор ) в ячейку B3. Введите туда соответствующую формулу. Ячейку В2 умножить на А3, но при этом воспользуйтесь абсолютной адресацией.

8. Затем, при помощи Маркера автозаполнения заполните всю таблицу умножения соответствующими значениями.

Проверьте, введенная вами формула должна соответствовать формуле: =B$2*$A. Объясните почему.

Готовая таблица будет иметь следующий вид:

A	B	C	D	E	F	G	H	I	J	K
		Таблица умножения.

Задание 3.

Задача БИОРИТМЫ

I этап. Постановка задачи

ОПИСАНИЕ ЗАДАЧИ

Существует гипотеза, что жизнь человека подчиняется трем циклическим процессам, называемым биоритмами. Эти циклы описывают три стороны самочувствия человека: физическую, эмоциональную и интеллектуальную. Биоритмы характеризуют подъемы и спады нашего состояния. Считается, что «взлетам» графика, представляющего собой синусоидальную зависимость, соответствуют более благоприятные дни. дни, в которые график переходит через ось абсцисс, считаются неблагоприятными. Не все считают эту теорию строго научной, но многие верят в нее. Более того, в некоторых странах мира в критические дни, когда ось абсцисс пересекают одновременно две или три кривые, людям профессий с повышенным уровнем риска (летчикам, каскадерам и т. п.) предоставляются выходные дни.

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

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

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

Третий биоритм характеризует мыслительные способности, интеллектуальное состояние человека. Цикличность его — 33 дня.

Предлагается осуществить моделирование биоритмов для конкретного человека от указанной текущей даты (дня отсчета) на месяц вперед с целью дальнейшего анализа модели.

ЦЕЛЬ МОДЕЛИРОВАНИЯ

На основе анализа индивидуальных биоритмов прогнозировать неблагоприятные дни, выбирать благоприятные дни для разного рода деятельности.

ФОРМАЛИЗАЦИЯ ЗАДАЧИ

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

II этап. Разработка модели

Указанные циклы можно описать приведенными ниже выражениями, в которых переменная х — количество прожитых человеком дней:

КОМПЬЮТЕРНАЯ МОДЕЛЬ

Для моделирования выберем среду табличного процессора. В этой среде информационная и математическая модели объединяются в таблицу, которая содержит две области:

· исходные данные;

· расчетные данные (результаты).

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

Примечание. Обратите внимание! В каждую формулу входит выражение (А9—$В$4), которое вычисляет количество дней, прожитых человеком. И хотя это выражение содержит ссылки на ячейки, в которых записаны даты, среда табличного процессора автоматически вычисляет каждую дату как количество дней, прошедших с 1 января 1900 года, а затем определяет разность между ними. При записи формул использовать вставку стандартных функций SIN (…) и ПИ(...).

Дата заполняется по формату 00.00.0000. Если дата набрана правильно, то ячейке автоматически будет присвоен формат дата. Признаком правильного набора даты является выравнивание значения вправо.

III этап. Компьютерный эксперимент

ПЛАН ЭКСПЕРИМЕНТА

ТЕСТИРОВАНИЕ

Провести тестирование модели. По результатам расчетов построить общую диаграмму для трех биоритмов.

ЭКСПЕРИМЕНТ

Произвести расчеты для других исходных данных. Исследовать показания биоритмов (благоприятные и неблагоприятные дни для различных видов деятельности).

ПРОВЕДЕНИЕ ИССЛЕДОВАНИЯ

ТЕСТИРОВАНИЕ

1. Сравните результаты, полученные после ввода формул, с результатами, приведенными в примере расчета.

ПРИМЕР РАСЧЕТА

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

2. Постройте диаграмму (рисунок 3.2).

3. По диаграмме определите дни, в которых значение биоритма равно нулю.

4. Сохраните выполненную работу в файле Биоритмы.

Примерно с 18 по 21 апреля 2013 года неблагоприятные для сдачи зачета по физкультуре дни (плохое физическое состояние)

2. Выбрать день для похода в цирк, театр или на дискотеку (эмоциональное состояние хорошее).

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ

РЕКОМЕНДАЦИИ ПО ПОСТРОЕНИЮ КОМПЬЮТЕРНОЙ МОДЕЛИ

1. Открыть файл Биоритмы.

2. Выделить ранее рассчитанные столбцы своих биоритмов, скопировать их и вставить в столбцы Е, F, G, используя команду Правка Специальная вставка | Переключатель значения.

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

4. В столбцах Н, I, J провести расчет суммарных биоритмов по формулам.

5. По столбцам Н, I, J построить линейную диаграмму физической, эмоциональной и интеллектуальной совместимости. Пример суммарной диаграммы представлен на рисунке 3.3. Максимальные значения по оси у на диаграмме указывают на степень совместимости: если размер по у превышает 1, 5, то вы с другом в хорошем контакте.

6. Описать результаты анализа модели, ориентируясь на следующие вопросы:

• Что, на ваш взгляд, показывают суммарные графики биоритмов? Что можно по ним определить?

Попытка будет неудачной

Что-нибудь сделаем

Решим

• В какой области деятельности вы могли бы преуспеть в паре с другом?

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

Вывод: в ходе работы я научился использовать различные встроенные функции

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

Цель: Научится использовать различные встроенные функции