MS Excel
методическая разработка по информатике и икт на тему

Слайд 1

Государственное автономное профессиональное образовательное учреждение среднего профессионального образования «Новороссийский колледж строительства и экономики» Краснодарского края 1 Презентация по дисциплине «Информационные технологии» на тему: « Использование функций в табличном процессоре MS EXCEL » для специальностей 230113 «Компьютерные системы и комплексы» Преподаватель: Бежан О.Т.

Слайд 2

Использование функций в табличном процессоре MS EXCEL 2 Цели : Изучение информационной технологии организации расчетов с использованием встроенных функций в таблицах MS Excel , построение графиков и диаграмм , закрепление и проверка навыков создания расчетных таблиц и графиков в MS Excel. Задачи : Развитие творческого мышления; развитие интереса к изученным темам; демонстрация возможностей прикладного программного обеспечения при решении прикладных задач.

Слайд 3

Основные понятия и правила записи функций Для облегчения расчетов в табличном процессоре Excel есть встроенные функции. Каждая стандартная встроенная функция имеет свое имя. Для удобства выбора и обращения к ним, все функции объединены в группы, называемые категориями : математические, статистические, финансовые, функции даты и времени, логические, текстовые и т.д. Использование всех функций в формулах происходит по совершенно одинаковым правилам : Каждая функция имеет свое неповторимое (уникальное) имя; При обращении к функции после ее имени в круглых скобках указывается список аргументов, разделенных точкой с запятой; Ввод функции в ячейку надо начинать со знака «=», а затем указать ее имя. 3

Слайд 4

Математические функции Название и обозначение функции Имя функции Пример записи фунции Примечание Синус – sin(x) SIN (…) SIN ( А5 ) Содержимое ячеек А5 в радианах Косинус – cos (x) COS(…) COS(B2) Содержимое ячейки В2 в радианах Тангенс tan(x) - TAN(…) TAN( B5 ) C одержимое ячейки В5 в радианах Квадратный корень - корень КОРЕНЬ (…) КОРЕНЬ( D12) Содержимое ячейки D12>0 Преобразует радианы в градусы - градусы ГРАДУСЫ (…) ГРАДУСЫ (С8) Содержимое ячейки С8 в градусах Сумма - сумм СУММ(…) СУММ(А1;В9) Сложение двух чисел, содержащихся в ячейках А1 и В9 СУММ(А1:А20) Сложение всех чисел, содержащихся в диапазоне ячеек от А1 до А20 Число  - Пи ПИ () ПИ() Функция не содержит аргументов 4

Слайд 5

Статистические функции Максимальное значение - макс МАКС(…) МАКС( А1:А9 ) Поиск максимального среди аргументов Минимальное значение - мин МИН(…) МИН( С1:С23 ) Поиск минимального среди аргументов Среднее значение - срзнач СРЗНАЧ (…) СРЗНАЧ ( А1:В5 ) Находит среднее арифметическое значение среди чисел, содержащихся в диапазоне ячеек от А1 до В5 5

Слайд 6

Текстовые функции Название и обозначение функции Имя функции Пример записи функции Примечание Объединяет несколько текстовых элементов в один - сцепить СЦЕПИТЬ(…) СЦЕПИТЬ(В11;В14) Чтобы добавить пробел между сцепленными словами, в аргументе указать пробел в кавычках, например СЦЕПИТЬ(В11; ” “ ;В14) Повторяет текст заданное число раз - повтор ПОВТОР(…) ПОВТОР(В4;5) Повторяет текст, содержащийся в ячейке В4 пять раз Находит крайние левые символы строки - левсимв ЛЕВСИМВ (…) ЛЕВСИМВ(А1;1) Отображает только первую букву текста, содержащегося в ячейке А1. Делает все буквы в тексте строчными - строчн СТРОЧН (…) СТРОЧН(А2:А9) Все слова, содержащиеся в диапазоне ячеек от А2 до А9 будут написаны строчными (маленькими буквами) 6

Слайд 7

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

Слайд 8

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

Слайд 9

Пон . Вт. Ср. Чт. Пт. Сб. Вс. Кол-во поездок за неделю Стоимость одной поездки Всего за неделю Троллейбус Автобус Маршрутное такси Сумма В MS Excel подготовьте таблицу для расчета ежедневных трат на поездки в транспорте. Внесите самостоятельно в таблицу количество поездок за день и стоимость одной поездки. Количество поездок за неделю рассчитайте по формуле. Подчитайте траты за неделю по каждому виду транспорта и общую сумму денег, потраченных за неделю. Задание 2 9

Слайд 10

Введите список предметов из набора первоклассника. Установите денежный формат данных в диапазоне ячеек В3:В8 и введите цену на каждый предмет из набора первоклассника. Введите количество предметов. Используя формулу (подумайте какую) рассчитайте стоимость всех тетрадей, всех ручек, всех карандашей и т.п. Используя математическую функцию суммы, рассчитайте общую сумму, затраченную на покупку набора для первоклассника. Отформатируйте таблицу по образцу. Задание 3 10

Слайд 11

В таблицу занесены адреса учащихся таким образом, что фамилия, город, улица, номер дома и номер квартиры находятся в отдельных столбцах. Необходимо разослать всем учащимся письма. Чтобы распечатать адреса на конвертах на принтере, необходимо получить полный адрес в одной ячейке. Для этого: Заполните таблицу по образцу, кроме столбца «Наклейка на конверт». Используя текстовую функцию СЦЕПИТЬ получите наклейку на конверте. Чтобы слова были разделены пробелами и запятыми, пробелы и запятые вносят в функцию в кавычках (например вот так “, “) . Задание 4 11

Слайд 12

Материал Поверхность Двери Подоконники кг на м 2 Площадь Расход кг на м 2 Площадь Расход Олифа 7,6 6,6 Белила тертые 6,0 6,5 Пигмент 1,5 0,6 В MS Excel оформите таблицу, позволяющую рассчитывать расход материалов для покраски в зависимости от площади поверхностей. Введите произвольную площадь. Введите формулы в столбцы «Расход». Расход материалов для окраски Задание 5 12

Слайд 13

A B C D E F G H I J K L M N 1 26.09.13 2 3 25 -61 0 -82 18 -11 0 30 15 -31 0 -58 22 4 5 Общее количество чисел 6 Количество положительных чисел 7 Количество отрицательных чисел 8 Количество нулей 9 Максимальное значение 10 Минимальное значение 11 Среднее значение 12 Сумма всех чисел 13 Сумма положительных чисел 14 Сумма отрицательных чисел Дана последовательность чисел: 25; -61; 0; -82; 18; -11; 0; 30; 15; -31; 0; -58; 22. В ячейку А1 введите текущую дату, используя мастер функций (категория функции Дата и время). Числа вводите в ячейки третьей строки. Заполните ячейки К5:К14 соответствующими формулами. Отформатируйте таблицу по образцу. Задание 6 13

Слайд 14

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

Слайд 15

Абсолютные и смешанные ссылки 15

Слайд 16

Задание 8 Технология выполнения работы: 1. Создайте лист Excel . Мой компьютер – Практика – Ваш класс – Ваша фамилия – Создать Лист Excel . 2. Создайте таблицу по образцу: Укажите формат данных для каждой ячейки 3. Рассчитайте премию по формуле: Премия = (Оклад * Процент премии ) 4. Рассчитайте итоговую сумму заработной платы по формуле: Итого = Оклад + Премия . Рассчитайте премию для каждого сотрудника в размере 20% оклада, имея в виду, что процент премии может измениться, и тогда потребуется перерасчет . При начислении премии используйте абсолютный адрес и прием копирования. 16

Слайд 17

Всего порций Продукт Раскладка на 1 порцию (г) Всего (г) Кальмары 48 Лук репчатый 17 Морковь 9 Рис 12 Масло растительное 8 В MS Excel подготовьте таблицу для расчета количества граммов каждого продукта для приготовления плова, в зависимости от количества порций. Учитывать то, что количество порций может изменяться. Задание 9 17

Слайд 18

Курс доллара 29 , 90 Наименование товара Эквивалент $US Цена в рублях Кресло рабочее 39 Стеллаж 35 Стойка компьютерная 60 Стол рабочий 42 Тумба выкатная 65 Шкаф офисный 82 В MS Excel подготовьте таблицу для расчета цены товара в рублях по данной цене в долларах, учитывая то, что курс доллара может изменяться. Задание 10 18

Слайд 19

1 2 3 4 5 6 7 Рожок 4,50 = Эскимо 6,00 Батончик 7,50 В стаканчике 4,00 С вафлями 5,00 Торт-мороженое 30,00 В MS Excel подготовьте шпаргалку для продавца мороженым, по которой можно быстро определить стоимость нескольких порций. Задайте формулу в первой ячейке столбца 2 и распространите ее на остальные с помощью маркера заполнения. Выполните подгон ширины для соответствующих столбцов. Задание 1 1 19

Слайд 20

Использую смешанные ссылки, постройте таблицу умножения от 2 до 10. Выполните форматирование таблицы. Задание 12 20

Слайд 21

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

Слайд 22

Задание 13 Построить с помощью Мастера диаграмм круговую диаграмму и гистограмму для своей таблицы расходов, подобные тем, что изображены на рис. 1. Для выделения двух несмежных диапазонов ячеек удерживать нажатой клавишу < Ctrl >. Отформатировать диаграммы по собственному усмотрению, используя различные цвета заливки, границ, размеры шрифта. Выбор диапазона для построения круговых диаграмм и гистограмм, использование для построения Мастера диаграмм, форматирование области диаграммы Рис.1 22

Слайд 23

Задание 14 Создать таблицу Вычислить итоговое значение в B13 (применить автосуммирование ). Ввести в D8 формулу для вычисления доли подоходного налога в общей сумме налогов. Скопировать формулу в D8 на ячейки D9 – D12 . (Замечание: во избежание ошибки применить там, где нужно, абсолютныессылки .) Построить круговую диаграмму и гистограмму (рис. 2). Ввод табличных данных, форматирование таблицы, вставка и копирование формул, абсолютные и относительные ссылки, построение диаграмм (рис. 2) 23

Слайд 24

Введите фамилии и рост учеников класса. Используя статистические функции нахождения максимального и минимального значений, найдите рост самого высокого и самого низкого ученика в классе. Отформатируйте таблицу. Постройте гистограмму и по ее данным определите рост самого высокого и самого низкого ученика в классе. Сравните полученные результаты. Задание 15 24

Слайд 25

1. Открыть MS Excel и заполнить таблицу значений Х от –5 до 5. 2. Результат функции y=x^2 рассчитать, используя математическую функцию степень (см. рисунок). 3. Скопировать формулу с использованием функции на все ячейки, в которых будет рассчитано значение Y . 4. Построить график зависимости y=x^2 , используя точечную диаграмму. Задание 16 25

Слайд 26

Задание 17 Решение задачи, ввод и копирование формул, вычисление значений искомой величины на заданном интервале и построение её графика с помощью Мастера диаграмм, форматирование области графика Записать условие задачи из любого раздела физики, подобной той, что приведена на рис. 1. Ввести в таблицу известные значения величин. Ввести формулу в первую ячейку столбца для неизвестной величины. Скопировать эту формулу на остальные ячейки этого столбца. Выделив в таблице нужный для построения диапазон ячеек, построить с помощью Мастера диаграмм график изменения этой величины, подобный тому, какой изображён на рис. 1. Отформатировать область графика по собственному усмотрению, используя различные цвета заливки, границ, размеры шрифта. 26

Слайд 27

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

Слайд 28

В MS Excel c оставьте таблицу значений функции у = для целых значений аргумента х от -6 до 6. Задание 19 х -6 -5 -4 -3 -2 -1 0 1 2 3 4 5 6 у Открыть MS Excel и заполнить таблицу значений Х от – 6 до 6 . Результат функции y= рассчитать. 3. Скопировать формулу с использованием функции на все ячейки, в которых будет рассчитано значение Y . 4. Построить график зависимости y=f(x), используя точечную диаграмму. 28

Слайд 29

В ячейках электронной таблицы Excel А1:А5 находятся значения аргумента х. В ячейку В1 внесли формулу для расчета значений функции F ( x ), а затем «протянули» по диапазону В1:В5 с помощью маркера заполнения. Записать какие формулы будут в ячейках В1:В5 F ( x )= Построить таблицу значений. Задание 20 29

Слайд 30

В ячейках электронной таблицы Excel А1:А5 находятся значения аргумента х. 2. В ячейку В1 внесли формулу для расчета значений функции F ( x ), а затем «протянули» по диапазону В1:В5 с помощью маркера заполнения. Записать какие формулы будут в ячейках В1:В5. F ( x )= 3. Построить таблицу значений функции. . Задание 21 30

Слайд 31

Задание 22 Для построения окружности составить таблицу значений sin φ и cos φ в интервале (0;2 ∏ ) c шагом =0,05 *ПИ() Выполнить построение окружности с помощью мастера диаграмм. Построить графики тригонометрических функций y= sin φ , y= cos φ 31

Слайд 32

№ варианта Функция Диапозон измерения φ/β Шаг изменения φ/β Вид диаграммы 2 ρ= Cos(5 φ) o т 0 до ¶ 0,1571 =0,05 *ПИ() Задание 2 3 Для уплотнение земляной площадки каток перемещается по траектории, заданной функци ей «роза» 32

Слайд 33

№ Варианта функция диапазон изменения φ шаг изменения φ вид диаграммы 1 3 4 5 6 10 0.2 π до 8.2 π 0,62831853 =0,2 *ПИ() Задание 24 При вывозе грунта из карьера, машина движется по траектории, заданной функцией "гиперболическая спираль» . Построить график параметрической ф-ции , где а = 3 33

Слайд 34

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

Слайд 35

35 Задание 25 Постройте таблицы истинности логических операций В ячейках электронной таблицы Excel находятся пары значений аргументов логической операции (0,0), (0,1), (1,0), (1,1) 2. В результирующую ячейку внесли формулу логического умножения: = И(А2;В2), а затем « протянуть» по диапазону С2:С5 с помощью маркера заполнения. 3. Повторить шаги 1, 2 для операций логического сложения и отрицания.

Слайд 36

Создать таблицу (рис. 1). Ввести в E5 формулу для определения, удовлетворяют ли полученные на экзаменах оценки условиям поступления в 10 класс соответствующего направления, т.е. сумма оценок за первые два профилирующих предмета должна быть больше или равна 9, а за два вторых предмета больше или равна 7. Если оба условия выполняются, то в столбце Результат должно появиться сообщение «прошёл», иначе – «не прошёл» . Скрыть ячейки с вариантами результата, ссылки на которые есть в формуле. Скопировать формулу в E9 , E13 , E17 . ( Замечание : во избежание ошибки примените там , где нужно абсолютные ссылки .) В водя в столбец D оценки, проверить, что результат им соответствует. Ввод табличных данных, форматирование таблицы, ввод и копирование сложных формул с использованием логических функций, абсолютных и относительных ссылок Рис. 1 Задание 2 6 36

Слайд 37

• Правило 1 . Если объем продаж меньше 20000, то комиссионные составляют 10% от его объема, а если не меньше 20000, то 20%. Для расчетов комиссионных по первому правилу в ячейку С2 введите формулу =ЕСЛИ ( В2 < 20000;В2 * 0,1;B2 *0,2) • Правило 2 . Если объем продаж меньше 20000, то комиссионные составляют 10% от его объема, если больше 20000, но меньше 30000, то 20%, а если больше 30000, то 30%. Для расчетов комиссионных по второму правилу в ячейку D2 введите формулу =ЕСЛИ ( В2 < 20000;В2 * 0,1;ЕСЛИ (И( В2 > =20000;В2 <30000); В2 *0,2; ЕСЛИ( В2 > =30000;В2 *0,3))), Можно упростить ввод: =ЕСЛИ ( В2 < 20000;В2 *0,1;0)+ЕСЛИ(И( В2 > =20000;В2 <30000); В2 *0,2;0)+ ЕСЛИ( В2 > 30000;В2 *0,3;0) . В заключение отберем тех менеджеров, которые по результатам продаж добились лучших результатов. С этой целью в ячейку F2 введите формулу =ЕСЛИ ( В2=МАКС ($2:$6);"Лучший";"") , а затем скопируйте ее в диапазон F2:F6 . Задание 27 Рассчитать количество комиссионных на основе использования логических функций (см. рис.) 37

Слайд 38

Задание 2 8 Использование логических функций в формулах Составить тест, добавив несколько вопросов по предложенной теме, подобный тому, какой приведён на рис. 1. В ячейку, в которой должен будет выводиться результат, ввести формулу его вычисления с использованием логических функций. Протестировать одноклассников и при необходимости отладить тест. 38