Microsoft Excel 2007. Статистические функции Т.Ф. Лебедева

Разделы: Информатика

Цели урока:

  • Закрепить на практике работу с мастером функций;
  • Научиться использовать статистические функции Excel.

Программное обеспечение:

Ms Office: Ms Excel 2007.

Ход урока

1. Организационная часть

1.1. Проверить присутствующих на уроке.
1.2. Проверить готовность учащихся к уроку (спецодежда, конспект лекций).

2. Вводный инструктаж

2.1. Проверка техники безопасности и охраны труда;
2.2. Проверка знаний пройденного материала (фронтальный опрос)

Вопросы:

Что такое функция?

Сколько функций входит в Excel, и на какие категории распределяются?

Какие существуют способы вызова окна “Мастера функций”?

Математические функции и их синтаксис?

В процессе повторения, на экране отображается презентация по прошлому уроку при изучении темы “Математические функции”.

2.3. Сообщить тему и цели нового урока;
2.4. Объяснение нового материала;
2.4.1. Объяснить и показать при помощи проектора синтаксис статистических функций в Excel(МАКС, МИН, СРЗНАЧ, РАНГ, СЧЁТЕСЛИ).

3. Текущий инструктаж.

3.1. Выдать раздаточный материал;
3.2. Целевой обход;
3.3. Проверка правильной организации рабочих мест;
3.4. Проверка правильности выполнения приемов;
3.5. Работа с отстающими;
3.6. Контроль знаний.

4. Заключительный инструктаж.

4.1. Провести анализ выполненного задания;
4.2. Разобрать ошибки;
4.3. Дать краткую характеристику работы каждого учащегося и сообщить оценки;
4.4. Сообщить тему следующего урока и выдать домашние задание.

Конспект вводного инструктажа

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

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

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

Рис. 1. Вкладка Формулы

  1. Выделите ячейку, в которую требуется ввести формулу;
  2. Щелкните по кнопке нужной категории функций в группе Библиотека функций и выберите нужную функцию;
  3. В окне Аргументы функции в соответствующем поле (полях) введите аргументы функции. Ссылки на ячейки можно вводить с клавиатуры, но удобнее пользоваться выделением ячеек мышью. Для этого поставьте курсор в соответствующее поле и на листе выделите необходимую ячейку или диапазон ячеек. Для удобства выделения ячеек окно Аргументы функции можно сдвинуть или свернуть. Текст, числа и логические выражения в качестве аргументов обычно вводят с клавиатуры. В качестве подсказки в окне отображается назначение функции, а в нижней части окна отображается описание аргумента, в поле которого в данный момент находится курсор. Следует иметь в виду, что некоторые функции не имеют аргументов;
  4. В окне Аргументы функции нажмите кнопку ОК.

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

  1. Выделите ячейку Е14;
  2. Щелкните по кнопке категории Другие функции в группе Библиотека функций и выберите категорию Статистические функцию МИН (рис.2);

Рис. 2.  Выбор функции

  1. В окне Аргументы функции (рис.3) установите курсор в поле Число1 и на листе выделите ячейку Е14, затем введите диапазон аргументов для расчета минимальной стоимости заказа;

    2. img3.jpg (61860 bytes)

    Рис. 3.   Ввод аргументов функции

  2. Нажмите кнопку ОК.

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

  1. Нажмите кнопку Вставить функцию в строке формул;
  2. В окне Мастер функций: шаг 1 из 2 (рис.4) в раскрывающемся списке Категория выберите категорию функции, затем в списке Выберите функцию выберите функцию;
  3. Нажмите кнопку ОК или дважды щелкните мышью по названию выбранной функции;

    Рис. 4.  Выбор функции в Мастере функций

  1. В появившемся окне Аргументы функции так же, как и в предыдущем случае (см. рис.3) введите аргументы функции. Нажмите кнопку ОК.

Имена функций при создании формул можно вводить с клавиатуры. Для упрощения процесса создания и снижения количества опечаток используйте автозавершение формул.

  1. В ячейку или в строку формул введите знак "=" (знак равенства) и первые буквы используемой функции. По мере ввода список прокрутки возможных элементов отображает наиболее близкие значения. Значки указывают типы вводимых данных, такие как функция или ссылка на таблицу (рис. 5);

Рис. 5.  Ввод формулы с использованием автозавершения

  1. Выберите нужную функцию, для чего дважды щелкните по ней мышью;
  2. С использованием клавиатуры и мыши введите аргументы функции. Подтвердите ввод формулы.

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

Изучаем синтаксис функций МИН, МАКС, СРЗНАЧ, РАНГ, СЧЕТЕСЛИ.

I)

=МИН (число1; число2;…; число n)

Функция возвращает наименьшее значение из списка аргументов. Логические и текстовые значение игнорируются;

Например:

Ячейки А1:А5 содержать значения 10,7,9,27 и 2

Чему будет равно значение функции?

=МИН(А1:А5) 2

=МИН(А1:А5;0) 0

II)

=МАКС(число1; число2;…; число n)

Функция возвращает наибольшие значение из списка аргументов. Логические и текстовые значение игнорируются;

Например:

Ячейки А1:А5 содержать значения 10,7,9,27 и 2

Чему будет равно значение функции?

=МАКС(А1:А5) 27

=МАКС(А1:А5;30) 30

III)

=СРЗНАЧ (число1; число2;…; число n)

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

Например:

Ячейки А1:А5 содержат значения 10,7,9,27 и 2

Чему будет равно значение функции?

=СРЗНАЧ(А1:А5) 11

Мы могли бы этот пример решить по иному, не используя функцию СРЗНАЧ:

=(А1+А2+А3+А4+А5)/5 или =СУММ(А1:А5)/5

А если написать такую функцию =СРЗНАЧ(А1:А5;5), то чему будет равно значение этой функции? 10

IV)

Функция возвращает ранг числа в списке чисел относительно других чисел в списке.

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

=РАНГ(число; ссылка на список; порядок)

Где:

Число – это число, для которого определяется ранг (порядок);

Ссылка на список – которому принадлежит число, нечисловые значения в ссылке игнорируются (ссылка на список должна быть абсолютной);

Порядок – способ упорядочения значений списка:

  • 0 или ничего – определяет ранг числа, так как если бы список сортировался в порядке убывания (т.е. максимальному значению присваивается ранг равный 1, чуть меньшему числу ранг 2 и т.д.);
  • Число не равное 0 – определяет ранг числа, так как если бы список сортировался в порядке возрастания (т.е. минимальному числу присваивается ранг равный 1, чуть большему числу ранг 2 и т.д.).

Разберем примеры:

  1. Ячейки А1:А5 содержат числа 7 3,5 4 1 2

    2. Чему будет равно значение функции для ячейки А2:
    =РАНГ(А1;$А$1:$А$5;1)
    Обратите внимание, что порядок (последняя цифра) в этой функции равен 1, число не равное 0.
    Значит, иными словами я хочу узнать, какой порядок занимает число А2 равное 3,5 в списке чисел, отсортированном в порядке возрастания.
    Согласно определения, функция присвоит наименьшему числу ранг 1.

    Ответ нашего примера 3

  2. Ячейки А1:А5 содержат числа 7 3,5 4 1 2

Чему будет равно значение функции для ячейки А1:

=РАНГ(А1;$А$1:$А$5)

Обратите внимание, что порядок в этой функции отсутствует. Значит, иными словами я хочу узнать какой порядок занимает число А1, равное 7 в списке чисел, отсортированному в порядке убывания.

Ответ нашего примера 1

ПРИМЕЧАНИЕ: Функция РАНГ присваивает повторяющимся числам одинаковый ранг. Однако, наличие повторяющихся чисел влияет на ранг последующих чисел.

Например, ячейки А1:А7 содержат числа 1 2 3 4 5 5 6. Нужно узнать чему будет равен ранг для ячеек А5 и А6.

= РАНГ (А1;$А$1:$А$7)

Согласно определению и примечанию, функция присвоит наибольшему числу ранг 1

Как мы видим, число 5 повторяется дважды и имеет ранг 2

Число 4 имеет ранг 4 и нет чисел, имеющего ранг 3

V)

=СЧЕТЕСЛИ (диапазон; критерий)

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

Например:

В диапазоне А1:А6, нужно подсчитать количество ячеек со значением 35

Ответ примера 1

В диапазоне А1:А6, нужно подсчитать количество ячеек со значением книга

Ответ примера 2

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

Текущий инструктаж

Практическая работа 1

  1. В ячейках выделенных зелёным цветом произведите соответствующие вычисления, используя математические функции;
  2. В ячейках выделенных желтым цветом произведите соответствующие вычисления, используя статистические функции.

Приложение 1

Практическая работа 2

  1. В выделенных ячейках произведите соответствующие вычисления:
    1. в столбце D рассчитайте налог 13% от начисленной заработной платы;
    2. в столбце Е рассчитайте заработную плату к выдаче без учета НДФЛ;
    3. в столбце F рассчитайте РАНГ числа ЗП по возрастанию;
    4. в ячейках С12, С13, С14 воспользуйтесь статистическими функциями.

Приложение 2

Практическая работа 3

  1. Продажи в день найдите с помощью умножения объёма продаж на цену;
  2. В выделенных ячейках произведите соответствующие вычисления с помощью функции СЧЕТЕСЛИ.

Приложение 3

Контроль знаний

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

  1. В выделенных ячейках произведите соответствующие вычисления;
  2. Ранжирование рассчитайте по убыванию.

Приложение 4

Тест

Приложение 5

Заключительный инструктаж

  1. Провести анализ выполненного задания;
  2. Разобрать ошибки;
  3. Дать краткую характеристику работы каждого учащегося и сообщить оценки.