Практическая работа. MS Excel. Статистические функции

Группа С-11, 02 октября 2023 года

Занятие по рабочей программе №15

Дисциплина: ОП.03 «Информационные технологии в профессиональной деятельности»

Раздел 2. Использование в профессиональной деятельности специального программного обеспечения.

Тема: ПР №5 MS Excel. Статистические функции.

Цель занятия:

1. Дидактическая:

• сформулировать представление студентов об использовании в профессиональной деятельности специального программного обеспечения;

• ознакомление с понятием интерфейс программного продукта;

• раскрыть структуру MS Excel;

• выяснить принципы и методы работы с таблицами;

• систематизировать и обобщить знания о работе с встроенными функциями;

• углубить и закрепить знания по дисциплине «Информационные технологии в профессиональной деятельности».

Воспитательная:

• развивать коммуникативные способности;

• развивать аналитические способности;

• развивать творческий подход к процессу обучения.

• воспитывать самостоятельность, дисциплинированность;

• стимулировать студентов к изучению дисциплины;

• побуждать к формированию активной жизненной позиции;

• прививать уважение и любовь к будущей профессии.

Вид занятия: практическая работа.

Тип занятия: обобщение и систематизация знаний.

Форма проведения занятия: репродуктивная и эвристическая беседа.

Междисциплинарные связи:

Обеспечивающие ОГСЭ.04 Физическая культура, МДК.04.01 Основы управления работами спец. подразделения швейного производства.

Обеспечиваемые ОП.01 Инженерная графика, ОП.08 Общая технология отрасли, ОП.11 Экономика, организация и планирование производства, ОП.16 Маркетинг в отрасли.

Методическое обеспечение: опорный конспект.

Литература:

1. Филимонова, Е.В. Информационные технологии в профессиональной деятельности : учебник. – Изд - е 2 - е, доп. и перераб. – Ростов н / Д : Феникс, 2008. – 381. – (СПО), стр. 134 – 155, стр. 158-160

ХОД ЗАНЯТИЯ

1. Ознакомление с темой, целью и планом занятия.

Тема: ПР №5 MS Excel. Статистические функции.

Цель работы: Получить практические навыки работы с табличным процессором Microsoft Excel.

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

Научиться использовать Статистические функции.

ПЛАН

1. Работа с формулами.

2. Статистические функции Excel.

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

ЛИТЕРАТУРА: [3], стр. 154 – 160

1. Работа с формулами

Возможность использования формул и функций является одним из важнейших свойств программы обработки электронных таблиц. Вычисления задаются формулами. Текст формулы, которая вводится в ячейку таблицы, должен начинаться со знака равенства =, чтобы программа Excel могла отличить формулу от текста. После знака равенства в ячейку записывается математическое выражение, содержащее аргументы, арифметические операции и функции. В качества аргументов в формуле обычно используются числа и адреса ячеек. Для обозначения арифметических операций могут использоваться следующие символы: + (сложение); - (вычитание); * (умножение); / (деление);^ (возведение в степень).

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

Формула может содержать ссылки на ячейки, которые расположены на другом рабочем листе или даже в таблице другого файла. Однажды введенная формула может быть в любое время модифицирована. Встроенный Мастер функций помогает пользователю найти ошибку или неправильную ссылку в большой таблице. • Кроме этого программа Excel позволяет работать со сложными формулами, содержащими несколько операций. Для наглядности можно включить текстовый режим, тогда программа Excel будет выводить в ячейку не результат вычисления формулы, а собственно формулу.

Если набранная вами последовательность в представлении Excel не является ни числом, ни формулой, она считается текстом и не подвергается каким-либо преобразованиям. Например, последовательность -145,2 будет считаться числом, a -145s2 или v145.2- текстом. Последовательность =А1+А2 - это формула, а А1+А2 - текст.

1.1. Ввод формул

Предположим, что в ячейке А1 таблицы находится число 100, а в ячейке В1 - число 20. Чтобы разделить первое число на второе и результат поместить в ячейку С1, в ячейку С1 следует ввести соответствующую формулу =А1/В1 и нажать клавишу Enter.

Примечание. Если во введенной вами формуле имеется синтаксическая ошибка, то в ячейке выводится сообщение «#ИМЯ?». Самыми распространенными ошибками являются: ввод аргументов формулы в русском регистре, пропуск знака операции.

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

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

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

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

Щелкните мышью по названию нужной вам функции (например, СУММ), тогда название функции появится в отдельной строке вместе с кратким описанием этой функции. Здесь же указываются типы аргументов функции и их количество. Аргументы задаются на следующем шаге. Чтобы перейти к нему, щелкните по командной кнопке «ОК».

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

Рис. 2. Задание аргументов функции на втором шаге Мастера функций

Для того, чтобы задать аргумент функции, нужно либо ввести его адрес с клавиатуры, либо в таблице выделить область, где он расположен. Тогда адрес аргумента функции появится в соответствующем поле диалогового окна Мастера функций. Если все аргументы функции были указаны правильно, в нижней части окна появляется значение этой функции, которое будет помещено в таблицу. Щелкните по командной кнопке «ОК», чтобы закрыть окно Мастера функций.

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

Примечание. В русифицированной версии Microsoft Excel имена большинства функций, исключая небольшую часть (18) математических, записываются на русском языке, например, =СУММ(), =ЕСЛИ(), =ВЕРОЯТНОСТЬ(), и т.п. Распространенной ошибкой начинающих является ввод на русском не только имен функций, но и аргументов.

2. Статистические функции Excel

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

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

СРЗНАЧ()

Статистическая функция СРЗНАЧ возвращает среднее арифметическое своих аргументов.

Данная функция может принимать до 255 аргументов и находить среднее сразу в нескольких несмежных диапазонах и ячейках:

Если в рассчитываемом диапазоне встречаются пустые или содержащие текст ячейки, то они игнорируются. В примере ниже среднее ищется по четырем ячейкам, т.е. (4+15+11+22)/4 = 13

Если необходимо вычислить среднее, учитывая все ячейки диапазона, то можно воспользоваться статистической функцией СРЗНАЧА. В следующем примере среднее ищется уже по 6 ячейкам, т.е. (4+15+11+22)/6 = 8,6(6).

Статистическая функция СРЗНАЧ может использовать в качестве своих аргументов математические операторы и различные функции Excel:

СРЗНАЧЕСЛИ()

Если необходимо вернуть среднее арифметическое значений, которые удовлетворяют определенному условию, то можно воспользоваться статистической функцией СРЗНАЧЕСЛИ. Следующая формула вычисляет среднее чисел, которые больше нуля:

В данном примере для подсчета среднего и проверки условия используется один и тот же диапазон, что не всегда удобно. На этот случай у функции СРЗНАЧЕСЛИ существует третий необязательный аргумент, по которому можно вычислять среднее. Т.е. по первому аргументу проверяем условие, по третьему – находим среднее.

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

Если требуется соблюсти несколько условий, то всегда можно применить статистическую функцию СРЗНАЧЕСЛИМН, которая позволяет считать среднее арифметическое ячеек, удовлетворяющих двум и более критериям.

МАКС()

Статистическая функция МАКС возвращает наибольшее значение в диапазоне ячеек:

МИН()

Статистическая функция МИН возвращает наименьшее значение в диапазоне ячеек:

НАИБОЛЬШИЙ()

Возвращает n-ое по величине значение из массива числовых данных. Например, на рисунке ниже мы нашли пятое по величине значение из списка.

Чтобы убедиться в этом, можно отсортировать числа в порядке возрастания:

НАИМЕНЬШИЙ()

Возвращает n-ое наименьшее значение из массива числовых данных. Например, на рисунке ниже мы нашли четвертое наименьшее значение из списка.

Если отсортировать числа в порядке возрастания, то все станет гораздо очевидней:

МЕДИАНА()

Статистическая функция МЕДИАНА возвращает медиану из заданного массива числовых данных. Медианой называют число, которое является серединой числового множества. Если в списке нечетное количество значений, то функция возвращает то, что находится ровно по середине. Если же количество значений четное, то функция возвращает среднее для двух чисел.

Например, на рисунке ниже формула возвращает медиану для списка, состоящего из 14 чисел.

Если отсортировать значения в порядке возрастания, то все становится на много понятней:

МОДА()

Возвращает наиболее часто встречающееся значение в массиве числовых данных.

Если отсортировать числа в порядке возрастания, то все становится гораздо понятней:

Статистическая функция МОДА на данный момент устарела, точнее, устарела ее форма записи. Вместо нее теперь используется функция МОДА.ОДН. Форма записи МОДА также поддерживается в Excel для совместимости.

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

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

1. Что такое функция? Что такое Мастер функций и какие способы его запуска вы знаете?

2. В какой последовательности выполняются операции в арифметическом выражении?

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

4. В каких случаях применяются логические функции? Чем отличается функция ЕСЛИ от остальных функций?

5. Составьте примеры случаев, в которых необходимо использовать функцию ЕСЛИ и логические функции И, ИЛИ. Чем отличаются функции И и ИЛИ от функции ЕСЛИ? В каком формате записываются функции И, ИЛИ, ЕСЛИ?

6. При делении на нуль программа выдает сообщение об ошибке. Какой функцией можно воспользоваться, чтобы исключить появление такого сообщения? Запишите пример формулы.

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

8. Как установить (изменить) точность отображения числа и результата вычислений?

9. Зачем копируются формулы? Опишите способы копирования формул.

10. Каково назначение диаграмм? Опишите отличительные черты диаграмм различного типа.

11. Как выполнить обмен данными между Excel и другими приложениями Microsoft (например, Word)?

12. Что такое списки? Приведите примеры данных, организованных в списки. Какие операции обработки списков имеются в Excel?

13. Зачем применяется фильтр при обработке списков? Как задать фильтр? Как вернуться к исходному полному списку данных?

14. Опишите назначение и порядок выполнения сортировки списков.

ДОМАШНЕЕ ЗАДАНИЕ

1. Составить конспект практического занятия.

2. Ответить письменно на контрольные вопросы.

Перечень рекомендуемых учебных изданий, Интернет-ресурсов, дополнительной литературы.

Основные источники:

1. Гохберг, Г.С. Информационные технологии: учебник для студ. сред. проф. Образования / Гохберг, Г.С, Зафиевский, А.В., Короткин, А.А. - 5 -е изд., стер. – М. : Издательский центр «Академия», 2010. – 208 с.

2. Михеева, Е.В. Практикум по информационным технологиям в профессиональной деятельности: учебное пособие. - М. : Академия, 2008. - 8 – е изд. - 256 с.- (Среднее профессиональное образование).

3. Филимонова, Е.В. Информационные технологии в профессиональной деятельности : учебник. – Изд-е 2-е, доп. и перераб. – Ростов н/Д : Феникс, 2008. – 381. – (СПО).

Интернет - источники:

1. Электронный учебник по информатике и информационным технологиям - http://www.ctc.msiu.ru/

2. Тесты по информатике - http://www.ege.ru/

3. Каталог образовательных Интернет-ресурсов: учебно-методические пособия - www.edu/ru/modules.php

4. Дидактические материалы по информатике - http://comp-science.narod.ru/

5. Портал электронного обучения - http://www.e-learning.by/

6. Электронный учебник «Работа с MS Excel» - http://avanta.vvsu.ru/met_supply/381/Index.htm

7. Практикум по MS Excel - http://informatika.delayu.ru/Zadanija/Exceldannye

Преподаватель: Владимир Александрович Волков