Государственное автономное профессиональное образовательное учреждение среднего профессионального образования «Новороссийский колледж строительства и экономики» Краснодарского края 1

2 Цели: Изучение информационной технологии организации расчетов с использованием встроенных функций в таблицах 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 до В5 5

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

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

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

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

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

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

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

ABCDEFGHIJKLMN Общее количество чисел 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

φ xy 010 0,15710,987690, ,31420,951040, ,47130,890980, ,62840,808970, ,78550,707030, ,94260,587690, ,09970,453860, ,25680,308860, ,41390,156250, ,5710-0, ,7281-0,15670, ,8852-0,30920, ,0423-0,45420, ,1994-0,5880, ,3565-0,70730, ,5136-0,80920, ,6707-0,89120, ,8278-0,95120, ,9849-0,98770, ,1420-0,0004 3,2991-0,9876-0,1569 3,4562-0,9509-0,3094 3,6133-0,8908-0,4544 3,7704-0,8087-0,5882 3,9275-0,7067-0,7075 4,0846-0,5874-0,8093 4,2417-0,4535-0,8913 4,3988-0,3085-0,9512 4,5559-0,1559-0,9878 4,71300, ,87010, ,9876 5,02720, ,9509 5,18430, ,8907 5,34140, ,8086 5,49850, ,7066 5,65560, ,5872 5,81270, ,4533 5,96980,9513-0,3083 6,12690, ,1556 6,284010,00081 Задание 22 Для построения окружности составить таблицу значений sin φ и cos φ в интервале (0;2) c шагом =0,05*ПИ() Выполнить построение окружности с помощью мастера диаграмм. Построить графики тригонометрических функций y= sin φ, y= cos φ 31

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

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

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

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

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

Правило 1. Если объем продаж меньше 20000, то комиссионные составляют 10% от его объема, а если не меньше 20000, то 20%. Для расчетов комиссионных по первому правилу в ячейку С2 введите формулу =ЕСЛИ(В2

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

39 Пример 1 Пример 2 Пример 3

Задание: 1. Ввести вопросы теста. Ввести в ячейку, в которой будет выводиться результат, формулу с алгоритмом анализа введённых ответов. (Рис.1) 2.Протестироваться. 3. Придумать свой тест, составить формулу определения результата. Ввести вопросы и формулу. Протестироваться и убедиться, что программа выдаёт верный результат. Подготовка простого электронного теста. (Использование логических функций.) Рис.1

Задание: 1. Ввести вопросы теста. Вести варианты ответов. Выделить цветом ячейки для ввода ответов. Ввести в ячейку, в которой будет выводиться результат, формулу с алгоритмом анализа введённых ответов. (Рис.1) 2.Протестироваться. 3. Придумать свой тест, составить формулу определения результата. Ввести вопросы и формулу. 4. Протестироваться и убедиться, что программа выдаёт верный результат. Подготовка электронного теста с несколькими вариантами ответа. (Использование логических функций.) Рис.1