Практическое занятие № 8

ТЕМА: Использование стандартных функций для нахождения суммы, среднего арифметического, поиска минимального (максимального) значения и др.

ЦЕЛЬ: Сформировать умения обрабатывать данные в электронных таблицах с использованием стандартных функций. 

 

1. Последовательность выполнения практического занятия.

1.1. Внеаудиторная подготовка

а. повторить технику безопасности при работе на ПК.

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

1.2. Работа на учебном занятии

а. Выполнить практические задания.

б. Убрать рабочее место.

в. Оформить отчёт.

2. Методические указания

 

Теоретические сведения для выполнения Задания 1.

 

 

 

Повторим!

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

Формулы можно вводить с использованием клавиатуры и мыши.С использованием клавиатуры вводят операторы (знаки действий), константы, скобки и, иногда, функции.

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

 

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

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

 

http://gatk.by/dotd/inform/pz13/index.8.gifВ отличие от копирования текстов и чисел, которые при копировании не изменяются, формулы при копировании МЕНЯЮТСЯ.

 

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

http://gatk.by/dotd/inform/pz13/index.8.gifСсылки, которые изменяются при копировании формул, называются относительными.

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

 

Задание 1. Создать на Лист 1 электронной книги MS Excel таблицу по образцу:

http://gatk.by/dotd/inform/pz12/pz12.files/image015.jpg

 

Методика выполнения Задания 1.

- выделим диапазон ячеек А1:F1, объединим их, введём с клавиатуры текст Накопительная ведомость по переоценке основных средств;

- нажмём клавишу Enter, курсор переместится в ячейку А2;

- выделим диапазон ячеек А2:F2 и вызовем контекстное меню правой клавишей мыши, выберем команду Формат ячеек и на вкладке Выравнивание поставим галочку напротив переносить по словам;

- в ячейку А2 введём текст Наименование объекта и переведём с помощью левой клавиши мыши курсор в ячейку В2, введём текст Балансовая стоимость и т.д.;

- курсор переместим в ячейку А3 и введём текст Заводоуправление, нажмём клавишу Enter, курсор переместится в ячейкуА4 введём текст Диспетчерская и т.д. до ячейки А8;

- в ячейку В3 введём число 12 556,4, нажмём клавишу Enter, курсор переместится в ячейку В4 введём число 186 и т.д. до ячейки В8;

- выполним ту же последовательность действий и для столбца С;

- формат ячеек для диапазона В3:С8  финансовый;

- формулы для вычислений ввести в ячейки D3, E3 и F3 соответственно =В3-С3, =В3*2,3 и =D3*3,1;

- установим курсор в ячейку D3 и скопируем любым способом в диапазон D4:D8 формулу из ячейки D3;

 перейдём в режим формул (смотрим практическое занятие № 9) и проверим изменение формул в диапазоне D4:D8;

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

- объединим ячейки А9, В9 и С9, введём текст Итого;

- в ячейках D9, E9 и F9 проведём суммирование по столбцам.

 

 Теоретические сведения для выполнения Задания 2.

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

http://gatk.by/dotd/inform/pz13/index.8.gifСсылки, остающиеся неизменными при копировании, называются абсолютными.

Для создания абсолютной ссылки нужно поставить знак $ в формуле перед именем столбца и номером строки, например $D$2.

 

Задание 2. Созданную на Лист 1 таблицу изменить по образцу:

Продолжить заполнение формулами

 
http://gatk.by/dotd/inform/pz12/pz12.files/image016.jpg

 

Методика выполнения Задания 2.

- создадим копию Лист 1 для того, чтобы проследить изменения при работе с абсолютными и относительными ссылками: подведём курсор к низу рабочей книги к вкладке с именем Лист 1 нажмём правую клавишу  мыши и в контекстно-зависимом меню выберем команду Переместить/копировать (1 шаг), во втором меню установим курсор на Лист 1, поставим галочку напротив команды Сделать копию и нажмём Ок (2 шаг) и перейдём на созданную копию листа 1, которая появилась рядом с  Лист 1 и называется Лист 1(2) (3 шаг):

 

http://gatk.by/dotd/inform/pz12/pz12.files/image017.jpg

 

- установим курсор в ячейку А2 и вызовем правой клавишей мыши контекстно-зависимое меню, выберем команду Вставитьдля строк и столбцов;

- отредактируем появившуюся сроку в соответствии с образцом;

- формулы столбца D автоматически пересчитались;

- в ячейку Е4 введём новую формулу =B4*$B$2, где $B$2 абсолютная ссылка на ячейку, в которой находится числовое значение коэффициента ВПС;

- скопируем формулу =B4*$B$2 в диапазон Е5:Е9 и обратим внимание на то, что абсолютная ссылка $B$2 не измениласьпри копировании формулы;

- аналогичным образом заполним столбец F;

- сравним формулы, а затем результаты вычислений с Лист 1 и Лист 1(2), переходя из режима формул в режим результатов вычислений.

 

Теоретические сведения для выполнения Задания 3.

Кроме относительных и  абсолютных ссылок есть смешанные ссылки. В таких ссылках ограничения накладываются либо только на столбец, либо только на строку.

Изменить тип ссылки в MS Excel  удобно нажатием клавиши F4. Сначала вводим в формулу адрес ячейки, например В7. По умолчанию он является относительной ссылкой. Затем устанавливаем на него курсор. При нажатии F4 ссылка меняется на абсолютную $7. Последующие два нажатия F4 дают смешанные ссылки: В$7 и 7, Затем снова появляется исходная относительная ссылка В7.  

  

Задание 3. Создать на Лист 2 таблицу вычисления значений функции y=x2 - 3·x + 7 на промежутке [-6,6] с шагом 1:

http://gatk.by/dotd/inform/pz12/pz12.files/image019.jpg

 

Методика выполнения Задания 3.

- перейдём на Лист 2;

- выделим диапазон ячеек А11, объединим их, введём с клавиатуры текст Таблица значений функции y=x²-3x+7  на промежутке [-6,6] c шагом 1;

- нажмём клавишу Enter, курсор переместится в ячейку А2;

- введём букву х и переведём с помощью левой клавиши мыши курсор в ячейку В2, введём букву y;

- в ячейку А3 введём число -6, нажмём клавишу Enter, курсор переместится в ячейку А4, в неё введём число -5 и, используя приём автозаполнения (практическое занятие № 11) заполним диапазон ячеек до значения х, равного 6;

- в ячейку В3 введём формулу =A3^2-3*A3+7 (знак ^ вводится одновременным нажатием клавиши shift и клавиши с цифрой 6 при английской раскладке клавиатуры);

- скопируем формулу =A3^2-3*A3+7 на диапазон столбца В до последнего значения х, т.е. до числа 6;

- перейдём в режим формул, убедимся в правильности копирования формул с относительными ссылками.

 

Теоретические сведения для выполнения Задания 4.

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

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

http://gatk.by/dotd/inform/pz13/Prim.GIFФункция вызывается в формуле по имени, после которого в скобках следуют аргументы. Аргументами функции могут быть числа, тексты, ссылки на ячейки или диапазоны ячеек.

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

 

Функция

Запись в Excel

 

Сумма чисел

СУММ(В2:В7)

 

Среднее значение

СРЗНАЧ(ВЗ:С8; D3:D8)

 

Максимальное значение

MAKC(B3:D7;   F4; F6)

 

Минимальное значение

МИН(ВЗ:F9)

 

Сумма квадратов чисел

СУММКВ (D2: G2)

 

Квадратный корень

КОРЕНЬ (3)

Синус sin х

SIN(A5)

Косинус cos X

COS(A6)

 

Количество стандартных функций в MS Excel очень велико. Для облегчения поиска они сгруппированы по категориям. Список всех категорий можно вызвать либо нажатием кнопки http://gatk.by/dotd/inform/pz12/pz12.files/image020.jpg в строке формул, либо с помощью меню ВставкаФункция.

 

http://gatk.by/dotd/inform/pz12/pz12.files/image021.jpg 

 

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

 

 

 

 

 

 

 

 

 

 

Избежать ошибок при вводе имени функции и адресов ячеек позволяет использования Мастера функций. Работа с Мастером функций включает два шага. А первом шаге выбирают нужную функцию, а на втором открывается новое окно, в котором вводят аргументы выбранной функции. Ссылки лучше не вводить вручную, а указывать мышью ячейку или диапазон.

  

http://gatk.by/dotd/inform/pz12/pz12.files/image022.jpg

 

Разберём, как вычислить значение выражения http://gatk.by/dotd/inform/pz12/pz12.files/image025.gif.

Поместим значение переменной а в ячейку А2, а значение переменной b в - В2. Значение переменной с будем вычислять в ячейке С2.

http://gatk.by/dotd/inform/pz12/pz12.files/image026.jpg

 

 

http://gatk.by/dotd/inform/pz12/pz12.files/image032.jpgВыделим ячейку С2, нажмём кнопку http://gatk.by/dotd/inform/pz12/pz12.files/image034.jpg в строке формул, появится Мастер функций. Среди категорий выберем Математические, а среди функций КОРЕНЬ и нажмём кнопку ОК.

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

 

 

 

 

 

 

 

 

 

http://gatk.by/dotd/inform/pz12/pz12.files/image036.jpg 

Правильному вводу помогает появившаяся подсказка. Аргумент функции, в нашем случае, http://gatk.by/dotd/inform/pz12/pz12.files/image040.gif можно набрать с клавиатуры А2^2+В2^2, но лучше ввести ссылкиА2 и В2 выделением этих ячеек мышью на поле рабочей книги, просто щёлкнув по ним указателем мыши. Нажать кнопку ОК.

 

 

 

 

 

 

http://gatk.by/dotd/inform/pz12/pz12.files/image041.jpgРезультат вычислений сразу отобразится в ячейке С2 – это число 5, а формула отображается в строке формул.

 

 

 

 

 

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

Для этого на панели инструментов имеется кнопка   Автосумма.

 

http://gatk.by/dotd/inform/pz13/pz13.files/image027.jpg

 

 

 

 

http://gatk.by/dotd/inform/pz13/pz13.files/image029.jpgРядом с ней находится значок выпадающего списка, в котором можно выбрать еще несколько часто встречающихся функций: Среднее, Максимум, Минимум, а также перейти к спискам других функций.

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

 

 

 

Задание 4. Создать на Лист 3 таблицу по образцу и вычислить среднее, минимальное и максимальное значения ночных и дневных температур  за первые дни марта.

http://gatk.by/dotd/inform/pz12/pz12.files/image046.jpg

 

Методика выполнения Задания 4.

- перейдём на Лист 3;

- выделим диапазон ячеек А12, объединим их, введём с клавиатуры текст Дата;

- нажмём клавишу Enter, курсор переместится в ячейку А3;

- введём текст 1 марта и нажмём клавишу Enter, курсор переместится в ячейку А4 введём текст 2 марта и с помощьюАвтозаполнения заполним столбец А до ячейки А11;

- выделим диапазон ячеек В12, объединим их, введём с клавиатуры текст Температура;

- нажмём клавишу Enter, курсор переместится в ячейку В2;

- введём текст день и нажмём клавишу Enter, курсор переместится в ячейку В3;

- заполним столбец В до ячейки В11 числами, обозначающими температуру воздуха днём в соответствующий день;

- переведём с помощью левой клавиши мыши курсор в ячейку С2, введём текст ночь, нажмём клавишу Enter, курсор переместится в ячейку С3;

- заполним столбец С до ячейки С11 числами, обозначающими температуру воздуха ночью в соответствующий день;

- переведём с помощью левой клавиши мыши курсор в ячейку В12 и нажмем кнопку ;

- из выпадающего списка выберем функцию Среднее, если нужно изменим диапазон ячеек и нажмем клавишу Enter, вячейке В10 будет отображен результат выполнения функции =СРЗНАЧ(ВЗ:В9);

- аналогичными действиями заполним оставшиеся пустыми ячейки.

 

Самостоятельная работа

Отметка 5-6:

Создайте таблицу для перевода градусов в радианы, аргумент (переменная х) изменяется от 0 до 90° с шагом 10°.

Вычислите значения тригонометрических функций SIN Х и COS Х.

 

http://gatk.by/dotd/inform/pz13/pz13.files/image036.jpg

 

Отметка 6-7:

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

http://gatk.by/dotd/inform/pz12/pz12.files/image050.jpg

 

Отметка 8-9:

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

http://gatk.by/dotd/inform/pz12/pz12.files/image055.jpg

 

3. Содержание отчета

а. Титульный лист

б. Цель работы

в. Дидактическое и методическое обеспечение

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

д. Заключение в виде анализа полученных результатов и выводов

 

 Контрольные вопросы

1.  Что понимают под ссылкой?

2.  Какие виды ссылок, используются в MS Excel?

3.  В каких случаях применяются относительные ссылки, а в каких абсолютные?

4.  В каких из приведённых формул использованы относительные, абсолютные, смешанные ссылки?

=$A$2+B2

=$D$2*C6

=B$7/E2

=B3-C$2

=A2+B2

=$A$2*B2-3

=$B$4/$D$4

=$D$3*$B$5

=D2+B2+5

 

Заполните таблицу по образцу.

относительные

абсолютные

смешанные

 

 

 

 

5.  Какая последовательность команд вызовет Мастер функций?

6.  Для чего предназначена кнопка Σ?

7.  Что может быть аргументом функции в MS Excel?

8.  По каким основным категориям сгруппированы функции в Мастере функций?

9.  К какой категории функций относится СРЗНАЧ, СУММ?