Презентация по информатике на тему Использование функций в табличном процессоре MS EXCEL

ГБООУ «Гимназия «ДИАЛОГ»1Презентация по дисциплине «Информационные технологии»на тему: «Использование функций в табличном процессоре MS EXCEL»Учитель Касаева И.И. Использование функций в табличном процессоре MS EXCEL2Цели: Изучение информационной технологии организации расчетов с использованием встроенных функций в таблицах MS Excel, построение графиков и диаграмм, закрепление и проверка навыков создания расчетных таблиц и графиков в MS Excel.Задачи: Развитие творческого мышления;развитие интереса к изученным темам; демонстрация возможностей прикладного программного обеспечения при решении прикладных задач. Основные понятия и правила записи функцийДля облегчения расчетов в табличном процессоре Excel есть встроенные функции.Каждая стандартная встроенная функция имеет свое имя.Для удобства выбора и обращения к ним, все функции объединены в группы, называемые категориями: математические, статистические, финансовые, функции даты и времени, логические, текстовые и т.д.Использование всех функций в формулах происходит по совершенно одинаковым правилам:Каждая функция имеет свое неповторимое (уникальное) имя;При обращении к функции после ее имени в круглых скобках указывается список аргументов, разделенных точкой с запятой;Ввод функции в ячейку надо начинать со знака «=», а затем указать ее имя.3 Математические функцииНазвание и обозначение функцииИмя функцииПример записи фунцииПримечаниеСинус – 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 Статистические функцииМаксимальное значение - максМАКС(…)МАКС(А1:А9)Поиск максимального среди аргументовМинимальное значение - минМИН(…)МИН(С1:С23)Поиск минимального среди аргументовСреднее значение - срзначСРЗНАЧ(…)СРЗНАЧ(А1:В5)Находит среднее арифметическое значение среди чисел, содержащихся в диапазоне ячеек от А1 до В55 Текстовые функцииНазвание и обозначение функцииИмя функцииПример записи функцииПримечаниеОбъединяет несколько текстовых элементов в один - сцепитьСЦЕПИТЬ(…)СЦЕПИТЬ(В11;В14)Чтобы добавить пробел между сцепленными словами, в аргументе указать пробел в кавычках, напримерСЦЕПИТЬ(В11;” “;В14)Повторяет текст заданное число раз - повторПОВТОР(…)ПОВТОР(В4;5)Повторяет текст, содержащийся в ячейке В4 пять разНаходит крайние левые символы строки - левсимвЛЕВСИМВ(…)ЛЕВСИМВ(А1;1)Отображает только первую букву текста, содержащегося в ячейке А1.Делает все буквы в тексте строчными - строчнСТРОЧН(…)СТРОЧН(А2:А9)Все слова, содержащиеся в диапазоне ячеек от А2 до А9 будут написаны строчными (маленькими буквами)6 Использование математических, статистических, текстовых функцийПостроение графиков и диаграммАбсолютные и смешанные ссылкиИспользование логических функцийПримерыПрактические задания7 Задание 1Создать таблицу своих расходов в течение недели, аналогичную таблице на рис. 1.Вычислить итоговое значение по каждой статье расходов и по каждому дню недели (применить автосуммирование ).Формат ячеек, в которые вводятся числа, выбрать денежный. Отформатировать таблицу по собственному усмотрению, используя различные цвета заливки, границ, шрифта. рис. 1Ввод данных в ячейки, определение формата группы ячеек, использование авто суммирования, форматирование таблицы8 Пон.Вт.Ср.Чт.Пт.Сб.Вс.Кол-во поездок за неделюСтоимость одной поездкиВсего за неделюТроллейбусАвтобусМаршрутное таксиСуммаВ MS Excel подготовьте таблицу для расчета ежедневных трат на поездки в транспорте. Внесите самостоятельно в таблицу количество поездок за день и стоимость одной поездки. Количество поездок за неделю рассчитайте по формуле. Подчитайте траты за неделю по каждому виду транспорта и общую сумму денег, потраченных за неделю.Задание 29 Введите список предметов из набора первоклассника.Установите денежный формат данных в диапазоне ячеек В3:В8 и введите цену на каждый предмет из набора первоклассника.Введите количество предметов.Используя формулу (подумайте какую) рассчитайте стоимость всех тетрадей, всех ручек, всех карандашей и т.п.Используя математическую функцию суммы, рассчитайте общую сумму, затраченную на покупку набора для первоклассника.Отформатируйте таблицу по образцу.Задание 310 В таблицу занесены адреса учащихся таким образом, что фамилия, город, улица, номер дома и номер квартиры находятся в отдельных столбцах. Необходимо разослать всем учащимся письма. Чтобы распечатать адреса на конвертах на принтере, необходимо получить полный адрес в одной ячейке. Для этого:Заполните таблицу по образцу, кроме столбца «Наклейка на конверт».Используя текстовую функцию СЦЕПИТЬ получите наклейку на конверте. Чтобы слова были разделены пробелами и запятыми, пробелы и запятые вносят в функцию в кавычках (например вот так “, “). Задание 411 МатериалПоверхностьДвериПодоконникикг на м2ПлощадьРасходкг на м2ПлощадьРасходОлифа7,66,6Белила тертые6,06,5Пигмент1,50,6В MS Excel оформите таблицу, позволяющую рассчитывать расход материалов для покраски в зависимости от площади поверхностей. Введите произвольную площадь. Введите формулы в столбцы «Расход». Расход материалов для окраскиЗадание 512 {ED083AE6-46FA-4A59-8FB0-9F97EB10719F} ABCDEFGHIJKLMN126.09.132              325-610-8218-1103015-310-5822 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 соответствующими формулами. Отформатируйте таблицу по образцу. Задание 613 Задание 7Вычисление по формулам, копирование формул, вставка рисунков в таблицу 1. Создать таблицу расчёта строительных материалов для ремонта квартиры, подобную той, какая изображена на рис. 1. 2. Ввести в соответствующие ячейки рисунки (сканированные или стандартные из коллекции). рис. 114 Абсолютные и смешанные ссылки15 Задание 8Технология выполнения работы:1. Создайте лист Excel.Мой компьютер – Практика – Ваш класс – Ваша фамилия – Создать Лист Excel.2. Создайте таблицу по образцу:Укажите формат данных для каждой ячейки3. Рассчитайте премию по формуле: Премия = (Оклад * Процент премии) 4. Рассчитайте итоговую сумму заработной платы по формуле: Итого = Оклад + Премия.Рассчитайте премию для каждого сотрудника в размере 20% оклада, имея в виду, что процент премии может измениться, и тогда потребуется перерасчет. При начислении премии используйте абсолютный адрес и прием копирования.16 Всего порцийПродуктРаскладка на1 порцию (г)Всего (г)Кальмары48Лук репчатый17Морковь9Рис12Масло растительное8В MS Excel подготовьте таблицу для расчета количества граммов каждого продукта для приготовления плова, в зависимости от количества порций. Учитывать то, что количество порций может изменяться.Задание 917 Курс доллара 29,90Наименование товараЭквивалент $USЦена в рубляхКресло рабочее39Стеллаж35Стойка компьютерная60Стол рабочий42Тумба выкатная65Шкаф офисный82В MS Excel подготовьте таблицу для расчета цены товара в рублях по данной цене в долларах, учитывая то, что курс доллара может изменяться. Задание 1018 1234567Рожок4,50 =Эскимо6,00Батончик7,50В стаканчике4,00С вафлями5,00Торт-мороженое30,00В MS Excel подготовьте шпаргалку для продавца мороженым, по которой можно быстро определить стоимость нескольких порций.Задайте формулу в первой ячейке столбца 2 и распространите ее на остальные с помощью маркера заполнения.Выполните подгон ширины для соответствующих столбцов.Задание 1119 Использую смешанные ссылки, постройте таблицу умножения от 2 до 10. Выполните форматирование таблицы.Задание 1220 Построение диаграмм и графиковДиаграмма – это средство графического представления количества информации. Предназначенное для сравнения значений величин или нескольких значений одной величины, слежения за изменением их значений.Диаграммы в Excel могут быть различных типов (линейчатые, круговые, гистограммы, графики и т.д.), которые представляют данные в различной форме. В каждом конкретном случае важно правильно подобрать тип создаваемой диаграммы.21 Задание 13Построить с помощью Мастера диаграмм круговую диаграмму и гистограмму для своей таблицы расходов, подобные тем, что изображены на рис. 1. Для выделения двух несмежных диапазонов ячеек удерживать нажатой клавишу <Ctrl>. Отформатировать диаграммы по собственному усмотрению, используя различные цвета заливки, границ, размеры шрифта. Выбор диапазона для построения круговых диаграмм и гистограмм, использование для построения Мастера диаграмм, форматирование области диаграммыРис.122 Задание 14Создать таблицу Вычислить итоговое значение в B13 (применить автосуммирование).Ввести в D8 формулу для вычисления доли подоходного налога в общей сумме налогов.Скопировать формулу в D8 на ячейки D9–D12. (Замечание: во избежание ошибки применить там, где нужно, абсолютныессылки.)Построить круговую диаграмму и гистограмму (рис. 2).Ввод табличных данных, форматирование таблицы, вставка икопирование формул, абсолютные и относительные ссылки, построение диаграмм (рис. 2)23 Введите фамилии и рост учеников класса.Используя статистические функции нахождения максимального и минимального значений, найдите рост самого высокого и самого низкого ученика в классе.Отформатируйте таблицу.Постройте гистограмму и по ее данным определите рост самого высокого и самого низкого ученика в классе. Сравните полученные результаты.Задание 1524 1. Открыть MS Excel и заполнить таблицу значений Х от –5 до 5.2. Результат функции y=x^2 рассчитать, используя математическую функцию степень (см. рисунок).3. Скопировать формулу с использованием функции на все ячейки, в которых будет рассчитано значение Y.4. Построить график зависимости y=x^2, используя точечную диаграмму.Задание 1625 Задание 17Решение задачи, ввод и копирование формул, вычисление значений искомой величины на заданном интервале и построение её графика с помощью Мастера диаграмм, форматирование области графикаЗаписать условие задачи из любого раздела физики, подобной той, что приведена на рис. 1. Ввести в таблицу известные значения величин.Ввести формулу в первую ячейку столбца для неизвестной величины. Скопировать эту формулу на остальные ячейки этого столбца. Выделив в таблице нужный для построения диапазон ячеек, построить с помощью Мастера диаграмм график изменения этой величины, подобный тому, какой изображён на рис. 1. Отформатировать область графика по собственному усмотрению, используя различные цвета заливки, границ, размеры шрифта.26 Задание 18Ввод и копирование формул, вычисление значений функции на заданном интервале и построение её графика с помощью Мастера диаграмм, форматирование области графикаВвести в таблицу значения аргументов функции на заданном интервале. Ввести формулу в первую ячейку столбца для соответствующих значений функции. Скопировать эту формулу на остальные ячейки этого столбца. Выделив в таблице нужный для построения диапазон ячеек, построить с помощью Мастера диаграмм график функции, подобный тому, какой изображён на рис. Отформатировать область графика по собственному смотрению, используя различные цвета заливки, границ, размеры шрифта. Создать таблицы и построить графики пяти различных функций.27  В MS Excel cоставьте таблицу значений функции у = для целых значений аргумента х от -6 до 6.    Задание 19х-6-5-4-3-2-10123456уОткрыть MS Excel и заполнить таблицу значений Х от –6 до 6.Результат функции y= рассчитать.3. Скопировать формулу с использованием функции на все ячейки, в которых будет рассчитано значение Y.4. Построить график зависимости y=f(x), используя точечную диаграмму.28 В ячейках электронной таблицы Excel А1:А5 находятся значения аргумента х. В ячейку В1 внесли формулу для расчета значений функции F(x), а затем «протянули» по диапазону В1:В5 с помощью маркера заполнения. Записать какие формулы будут в ячейках В1:В5F(x)=Построить таблицу значений. Задание 2029 В ячейках электронной таблицы Excel А1:А5 находятся значения аргумента х. 2. В ячейку В1 внесли формулу для расчета значений функции F(x), а затем «протянули» по диапазону В1:В5 с помощью маркера заполнения. Записать какие формулы будут в ячейках В1:В5. F(x)=3. Построить таблицу значений функции..Задание 2130 Задание 22Для построения окружности составить таблицу значений sin φ и cos φ в интервале (0;2∏) c шагом =0,05*ПИ()Выполнить построение окружности с помощью мастера диаграмм. Построить графики тригонометрических функций y= sin φ , y= cos φ 31 № вариантаФункцияДиапозон измерения φ/βШаг изменения φ/βВид диаграммы2ρ=Cos(5φ)oт 0 до ¶0,1571  =0,05*ПИ()Задание 23Для уплотнение земляной площадки каток перемещается по траектории, заданной функцией «роза»32 № Вариантафункциядиапазон изменения φшаг изменения φвид диаграммы13456100.2π до 8.2π0,62831853  =0,2*ПИ()Задание 24При вывозе грунта из карьера, машина движется по траектории, заданной функцией "гиперболическая спираль» . Построить график параметрической ф-ции ,где а = 333 Использование логических функцийВ электронных таблицах имеются базовые логические операции (умножения, сложения, отрицания), с помощью которых можно построить таблицы истинности.Для проверки условия используют функцию Если, содержащую параметры: логическое выражение; выражение, если условие истинно; выражение, если условие ложно.34 35Задание 25Постройте таблицы истинности логических операций В ячейках электронной таблицы Excel находятся пары значений аргументов логической операции (0,0), (0,1), (1,0), (1,1) 2. В результирующую ячейку внесли формулу логического умножения: = И(А2;В2), а затем «протянуть» по диапазону С2:С5 с помощью маркера заполнения. 3. Повторить шаги 1, 2 для операций логического сложения и отрицания. Создать таблицу (рис. 1). Ввести в E5 формулу для определения, удовлетворяют ли полученные на экзаменах оценки условиям поступления в 10 класс соответствующего направления, т.е. сумма оценок за первые два профилирующих предмета должна быть больше или равна 9, а за два вторых предмета больше или равна 7. Если оба условия выполняются, то в столбце Результат должно появиться сообщение «прошёл», иначе – «не прошёл». Скрыть ячейки с вариантами результата, ссылки на которые есть в формуле. Скопировать формулу в E9, E13, E17. (Замечание: во избежание ошибки примените там, где нужно абсолютные ссылки.) Вводя в столбец D оценки, проверить, что результат им соответствует. Ввод табличных данных, форматирование таблицы, ввод икопирование сложных формул с использованием логических функций, абсолютных и относительных ссылокРис. 1Задание 2636 •      Правило 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 Задание 28Использование логических функций в формулахСоставить тест, добавив несколько вопросов по предложенной теме, подобный тому, какой приведён на рис. 1.В ячейку, в которой должен будет выводиться результат, ввести формулу его вычисления с использованием логических функций. Протестировать одноклассников и при необходимости отладить тест.38