Тема: Создание конкретных электронных таблиц. Форматирование электронной таблицы.
Цель: освоить порядок создания таблиц, приёмы их форматирования.
ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ
1. Изучите краткие теоретические сведения. Выполните краткий конспект в тетради.
Статистика – наука о сборе, измерении и анализе массовых количественных данных.
Функция, которая удовлетворяет требованиям:
является простой для использования ее в дальнейших вычислениях;
график этой функции проходит вблизи экспериментальных точек и отклонения этих точек от графика функции минимальны и равномерны,
называется регрессионной моделью.
Получение регрессионной модели происходит в два этапа:
подбор вида функции;
вычисление параметров функции.
Чаще всего выбор производится среди следующих функций:
y=аx+b - линейная функция;
y=аx2+bx+c -квадратичная функция;
y=аln(x)+b - логарифмическая функция;
y=aebx - экспоненциальная функция;
y=axb -степенная функция.
Во всех этих формулах x – аргумент, y – значение функции, а, b, c – параметры функций.
При выборе одной из функций нужно подобрать параметры так, чтобы Функция располагалась как можно ближе к экспериментальным точкам.
Существует метод наименьших квадратов (МНК). Его суть – искомая функция должна быть построена так, чтобы сумма квадратов отклонений y-координат всех экспериментальных точек от y-координат графика Функции была бы минимальна.
Графики регрессионной модели называются трендами. (английское слово trend переводиться как общее направление или тенденция).
Опишем алгоритм получения с помощью MS Еxcel регрессионных моделей по МНК с построением тренда.
вводим табличные данные;
строим точечную диаграмму, где в качестве подписи к оси Ox выбрать текст «линейный тренд» (остальные надписи и легенду можно игнорировать);
щелкнуть мышью по полю диаграммы; выполнить команду диаграмма – добавить линию тренда;
в открывшемся окне на закладке «тип» выбрать «линейный тренд»;
перейти к закладке «параметры» и установит галочки на флажках «показать уравнения на диаграмме» и «поместить на диаграмме величину достоверности аппроксимации R^2» и щелкнуть OK;
аналогично получаем и другие тренды.
Раздел математической статистики, который исследует такие зависимости, называется корреляционным анализом. корреляционный анализ изучает усредненный закон поведения каждой из величин в зависимости от значений другой величины, а также меру такой зависимости.
Оценку корреляции величин начинают с высказывания гипотезы о возможном характере зависимости между их значениями. чаще всего допускают наличие линейной зависимости. в таком случае мерой корреляционной зависимости является величина, которая называется коэффициентом корреляции. как и прежде, мы не будем писать формулы, по которым он вычисляется; их написать нетрудно, гораздо труднее понять, почему они именно такие. На данном этапе вам достаточно знать следующее:
коэффициент корреляции (обычно обозначаемый греческой буквой r) есть число, заключенное в диапазоне от -1 до +1;
если это число по модулю близко к 1, то имеет место сильная корреляция, если к 0, то слабая;
близость r к +1 означает, что возрастанию одного набора значений соответствует возрастание другого набора, близость к -1 означает обратное;
значение r легко найти с помощью Excel (встроенные статистические функции).
В Еxcel функция вычисления коэффициента корреляции называется Коррел и входит в группу статистических функций.
2.Практическая часть. Задание
Постройте регрессионную модель зависимости объема продаж от численности населения по данным таблицы.
Выполните расчеты корреляционной зависимости между объемом продаж в каждом отделении сети магазинов фасонной одежды и численностью населения, проживающего в радиусе 30-минутной езды от каждого из отделений.
Отделение магазина
|
Объём продаж, тыс. руб.
|
Численность населения
|
1
|
24
|
287
|
2
|
15
|
161
|
3
|
18
|
75
|
4
|
22
|
191
|
5
|
43
|
450
|
6
|
35
|
323
|
7
|
32
|
256
|
8
|
25
|
312
|
9
|
19
|
142
|
10
|
23
|
210
|
Исходные данные и результаты расчетов сохраните в Файл ПР22.хls.
3. Содержание отчета
Отчет должен содержать:
Название работы.
Цель работы.
Задание и его решение.
Вывод по работе.
4.Контрольные вопросы
В чем сущность и назначение регрессионного анализа?
В чем сущность и назначение корреляционного анализа?
Укажите порядок проведения корреляционного анализа средствами электронных таблиц.
Что такое тренд?
Как построить линию тренда?
|
|
Практическая работа №20
Тема: Использование условной функции при решении задач в электронной таблице
Цель: научиться правильно записывать условную функцию и строить условие в ней, применять условную функцию для решения задач.
ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ
1. Изучите краткие теоретические сведения. Выполните краткий конспект в тетради.
Краткие теоретические сведения. Примеры решения заданий.
Общий вид условной функции:
ЕСЛИ(<условие>; <выражение1>;<выражение2>)
Условная функция – это команда машине выполнить определенное действие в зависимости от условия.
Условие – это логическое выражение, которое может принимать значение ИСТИНА или ЛОЖЬ.
<выражение1> и <выражение2> могут быть числами, формулами или текстами.
Условная функция, записанная в ячейку таблицы, выполняется так:
если условие истинно, то значение данной ячейки определит <выражение1>, в противном случае – <выражение2>.
Логические выражения.
Логические выражения строятся с помощью операций отношения (<, <= (меньше или равно), >, >= (больше или равно), =, <> (не равно)) и логических операций (логическое И, логическое ИЛИ, логическое отрицание НЕ).
Результатом вычисления логических операций являются логические величины ИСТИНА или ЛОЖЬ.
Особенности записи логических операций:
сначала записывается имя логической операции (И, ИЛИ, НЕ), а затем в круглых скобках перечисляются логические операнды.
ЕСЛИ(И(Е5>С1;В3<10); «ДА»; «НЕТ»)
содержимое ячейки Е5 больше содержимого ячейки С1 И содержимое ячейки В3 меньше 10. Если условие выполняется, то в выбранной клетке будет отображаться «ДА», в противном случае – «НЕТ».
2.Практическая часть. Задание
Задача 1.
Разработать таблицу, содержащую следующие сведения об абитуриентах: фамилия, оценки за экзамены по математике, русскому и информатике, сумма баллов за три экзамена и информацию о зачислении: если сумма баллов больше или равна проходному баллу -13 и оценка за экзамен по математике – 4 или 5, то абитуриент зачислен в учебное заведение, в противном случае – нет.
В таблице, составленной для решения этой задачи, будут присутствовать столбцы: фамилия, оценки за экзамен по математике, оценки за экзамен по русскому и оценки за экзамен по информатике, сумма баллов за три экзамена и информация о зачислении. В ячейках столбца «Зачисление» будет высвечиваться “да”, если абитуриент поступил и “нет”, если не поступил.
Чтобы определить зачислен абитуриент или нет, нужно использовать условную функцию. Если проходной балл 13 поместить в ячейку D1, наименования столбцов таблицы разместить в ячейках А3:F3, то функцию в ячейке F3 надо записать так:
=ЕСЛИ(И(E4>=D1;B4>=4);"ДА";"НЕТ")
А какие необходимо внести изменения, чтобы можно было определить количество поступивших? (=ЕСЛИ(И(E4>=D1;B4>=4);1;0); внести доп. строку- количество поступивших. Определить через автосуммирование).
Для решения более сложных задач используют вложенные логические функции ЕСЛИ.
Формат записи:
=ЕСЛИ(усл.1; выражение В; ЕСЛИ(усл.2; выражение С; ЕСЛИ(…)))
Может быть вложено до семи условий ЕСЛИ
Задача №2
Найти работников, у которых имеются одновременно задолженности по двум видам кредита, и удержать от ЗП- 20% в счет погашения кредитов. С остальных работников, имеющих задолженность по какому-либо одному виду кредита, удержать 10% от ЗП. Работникам, не имеющим задолженности по кредиту, проставить в графе “Удержано”- 0.
Необходимо сформировать таблицу и записать функцию.
В этом примере логическая функция будет иметь следующий вид:
=ЕСЛИ(И(С2>0;D2>0);B2*0,2;ЕСЛИ(И(С2=0;D2=0);0;B2*0,1))
Данная логическая функция означает, что если одновременно задолженности по двум видам кредита больше нуля, то необходимо удержать 20% с начисленной суммы, если обе задолженности одновременно равны нулю, то необходимо вывести 0, в противном случае необходимо удержать 10% от ЗП.
|
А
|
B
|
C
|
D
|
E
|
1
|
Фамилия
|
ЗП
|
Кредит 1
|
Кредит 2
|
Удержать из ЗП
|
2
|
Воронов
|
50000
|
20000
|
56000
|
=ЕСЛИ(И(С2>0;D2>0);B2*0,2;ЕСЛИ(И(С2=0;D2=0);0;B2*0,1))
|
3
|
Скворцов
|
55000
|
15000
|
0
|
|
4
|
Сомов
|
65000
|
0
|
0
|
|
Задание №3
Предлагается «переведите» условные выражения, записанные по правилам электронных таблиц в форму обычных предложений русского языка («Если…, то…, иначе…»).
а) =ЕСЛИ (В1> C1; D2^2; D2*2)
б) =ЕСЛИ (И(С5 >= C4; C4<2; D1=0); 1; -1)
в) =ЕСЛИ (С1=5; 1)
Ответ.
а) если В1>C1, то D2 возвести в квадрат, иначе D2 умножить на 2;
б) если С5³С4 и при этом С4<2 и при этом D1 = 0, то поставить 1, иначе поставить -1;
в) если С1=5, то поставить 1, иначе ничего не делать.
3. Задание
Задание 1. «Переведите» условные выражения, записанные по правилам электронных таблиц в форму обычных предложений русского языка («Если…, то…, иначе…»).
а) =ЕСЛИ (В1> C1; D2^2; D2*2)
б) =ЕСЛИ (И(С5 >= C4; C4<2; D1=0); 1; -1)
в) =ЕСЛИ (С1=5; 1)
Ответы запишите в тетрадь.
Задание 2. Решите задачи с помощью Электронной таблицы:
Задача 1.
Таблица содержит следующие данные об учениках школы: фамилия, возраст и рост ученика. Сколько учеников могут заниматься в баскетбольной секции, если в секцию принимают детей с ростом не менее 165 см? Возраст не должен превышать 13 лет.
Задача 2.
Каждому пушному зверьку в возрасте от 1-го до 2-х месяцев полагается дополнительный стакан молока в день, если его вес меньше 3 кг. Количество зверьков, возраст и вес каждого известны. Выяснить, сколько литров молока в месяц необходимо для зверофермы. Один стакан молока составляет 0,2 литра.
Задача 3.
Сотрудникам компании начислена ЗП. Рассчитать ЗП выданную на руки, учитывая процент выполнения плана, который влияет на размер премии (если план выполнен на 100% и более- премия составит 15% от начисленной ЗП, иначе- 0) и задолженности по кредитам ( сотрудникам, у которых имеются одновременно задолженности по двум видам кредита удержать от ЗП- 20% в счет погашения кредитов, с сотрудников, имеющих задолженность по какому-либо одному виду кредита, удержать 10% от ЗП. Сотрудникам, не имеющим задолженности по кредиту, проставить в графе “Удержано”- 0).
3.Содержание отчета.
Отчет должен содержать:
Название работы.
Цель работы.
Задачи и формулы для их решения.
Вывод по работе.
4. Контрольные вопросы
Когда следует применять условную функцию при решении задач?
Каков общий вид условной функции?
Что такое условие, каким может быть условие?
Когда в результирующей ячейке будет отображаться значение Выражения1, а когда – значение выражения2?
Как записывается вложенная условная функция??
|
|
Практическая работа №21
Тема: Построение и форматирование диаграмм в электронной таблице.
Цель: познакомиться с основными видами диаграмм и их элементами, освоить основные приемы форматирования диаграмм, научиться строить диаграммы и графики на основе табличных данных.
ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ
1. Изучите краткие теоретические сведения. Выполните краткий конспект в тетради.
Основные типы диаграмм: - круговая диаграмма, кольцевая диаграмма, гистограмма, точечная диаграмма, линейчатая диаграмма, график.
Элементы диаграммы
Диаграмма состоит из множества элементов. Некоторые из них отображаются по умолчанию, а другие можно добавлять при необходимости. Отображение элементов диаграммы можно изменить путем их перемещения в другое место диаграммы, изменения их размера или формата. Кроме того, ненужные элементы можно удалить.
1- Область диаграммы(Область диаграммы. Область размещения диаграммы и всех ее элементов.).
2- Область построения(Область построения. На плоских диаграммах — ограниченная осями область, содержащая все ряды данных. На объемных диаграммах — ограниченная осями область, содержащая все ряды данных, имена категорий, подписи делений и названия осей.) диаграммы.
3- Элементы данных(Элементы данных. Отдельные значения, отображаемые на диаграмме в виде полос, столбцов, линий, секторов, точек или других объектов, называемых маркерами данных. Маркеры данных одного цвета образуют ряд данных.) в рядах данных(Ряд данных. Набор связанных между собой элементов данных, отображаемых на диаграмме. Каждому ряду данных на диаграмме соответствует отдельный цвет или способ обозначения, указанный на легенде диаграммы. Диаграммы всех типов, кроме круговой, могут содержать несколько рядов данных.), которые используются для построения диаграммы.
4- Горизонтальная (ось категорий) и вертикальная (ось значений) оси(Ось. Линия, ограничивающая область построения диаграммы, используемая в системе координат в выбранных единицах измерения. Ось Y обычно расположена вертикально, а вдоль нее строятся данные. Ось X обычно расположена горизонтально, а вдоль нее строятся категории.), по которым выполняется построение диаграммы.
5- Легенда(Легенда. Рамка, в которой определяются узоры или цвета рядов или категорий данных на диаграмме.) диаграммы.
6- Диаграмма и названия(Названия на диаграммах. Описательный текст, располагающийся вдоль осей или по центру в верхней части диаграммы.) осей, которые можно использовать на диаграмме.
7- Метки данных(Подпись данных. Подпись с дополнительными сведениями о маркере данных, представляющем одну точку данных или значение ячейки листа.), которые можно использовать для подписи точек данных в рядах данных.
После создания диаграммы можно изменить любой из ее элементов. Например, можно изменить отображение осей, добавить название диаграммы, переместить либо скрыть легенду или отобразить дополнительные элементы диаграммы.
Технология построения диаграмм и графиков.
Этап первый. При создании диаграммы в электронных таблицах прежде всего необходимо выделить диапазон ячеек, содержащий исходные данные для ее построения. Затем на панели быстрого доступа открыть вкладку/команду «Вставка» и выбрать соответствующую диаграмму или график.
Выделенный диапазон исходных данных включает в себя ряды данных и категории.
Ряды данных – это множество значений, которые необходимо отобразить на диаграмме.
На линейчатой диаграмме значения ряда данных отображаются с помощью столбцов, на круговой - с помощью секторов, на графике – точками, имеющимися заданные координаты Y.
  
Категории задают положение значений ряда данных на диаграмме. На линейчатой диаграмме категории являются подписями под столбцами, на круговой диаграмме – названиями секторов, а на графике категории используются для обозначения делений на оси Х.
Ряды данных и категории могут размещаться как в столбцах, так и в строках электронной таблицы.
Этап второй. Оформление диаграммы.
Диаграммы могут располагаться как на отдельных листах, так и на листе с данными. Область диаграммы может содержать названия оси категорий и оси значений, заголовок диаграммы и легенду. Для того, чтобы подписать диаграмму, данные, название осей, а также определить местоположение легенды, необходимо выбрать вкладку/команду «Макет» на панели быстрого доступа.
На этом этапе подбираются все необходимые параметры диаграммы, т.е. ее внешний вид. Можно подписать вертикальную и горизонтальную ось (при необходимости), подписать данные, название диаграммы, изменить местоположение легенды.
Подписи данных, название осей не всегда бывают уместны – иногда это перегружает диаграмму информацией, делает ее тяжелой для восприятия. Оформление диаграммы каждый раз решается индивидуально, в зависимости от решаемой задачи. Надо помнить, что диаграмма или график должны быть информативны и эстетичны.
2.Практическая часть. Задание
Задание 1
1.1 Создать и заполнить таблицу продаж, показанную на рисунке:
|
A
|
B
|
C
|
D
|
E
|
1
|
Продажа автомобилей ВАЗ
|
2
|
Модель
|
Квартал 1
|
Квартал 2
|
Квартал 3
|
Квартал 4
|
3
|
ВАЗ 2101
|
3130
|
3020
|
2910
|
2800
|
4
|
ВАЗ 2102
|
2480
|
2100
|
1720
|
1340
|
5
|
ВАЗ 2103
|
1760
|
1760
|
1760
|
1760
|
6
|
ВАЗ 2104
|
1040
|
1040
|
1040
|
1040
|
7
|
ВАЗ 2105
|
320
|
320
|
320
|
320
|
8
|
ВАЗ 2106
|
4200
|
4150
|
4100
|
4050
|
9
|
ВАЗ 2107
|
6215
|
6150
|
6085
|
6020
|
10
|
ВАЗ 2108
|
8230
|
8150
|
8070
|
7990
|
11
|
ВАЗ 2109
|
10245
|
10150
|
10055
|
9960
|
12
|
ВАЗ 2110
|
12260
|
12150
|
12040
|
11930
|
13
|
ВАЗ 2111
|
14275
|
14150
|
14025
|
13900
|
1.2 Построить диаграмму по всем продажам всех автомобилей, для этого:
выделить всю таблицу (диапазон А1:Е13);
щёлкнуть кнопку Мастер диаграмм на панели инструментов Стандартная или выполнить команду Вставка/Диаграмма;
в диалоговом окне Тип диаграммы выбрать Тип Гистограммы и Вид 1, щёлкнуть кнопку Далее;
в диалоговом окне Мастер Диаграмм: Источник данных диаграммы посмотреть на образец диаграммы, щёлкнуть кнопку Далее;
в диалоговом окне Мастер Диаграмм: Параметры диаграммы ввести в поле Название диаграммы текст Продажа автомобилей, щёлкнуть кнопку Далее;
в диалоговом окне Мастер Диаграмм: Размещение диаграммы установить переключатель «отдельном», чтобы получить диаграмму большего размера на отдельном листе, щёлкнуть кнопку Готово.
1.3 Изменить фон диаграммы, для этого:
щёлкнуть правой кнопкой мыши по серому фону диаграммы (не попадая на сетку линий и на другие объекты диаграммы);
в появившемся контекстном меню выбрать пункт Формат области построения;
в диалоговом окне Формат области построения выбрать цвет фона, например, бледно-голубой, щёлкнув по соответствующему образцу цвета;
щёлкнуть по кнопке Способы заливки.
в диалоговом окне Заливка установить переключатель «два цвета», выбрать из списка Цвет2 бледно-жёлтый цвет, проверить установку Типа штриховки «горизонтальная», щёлкнуть ОК, ОК;
1.4 Отформатировать Легенду диаграммы, для этого:
щёлкнуть левой кнопкой мыши по области Легенды (внутри прямоугольника с надписями), на её рамке появятся маркеры выделения;
с нажатой левой кнопкой передвинуть область Легенды на свободное место на фоне диаграммы;
увеличить размер шрифта Легенды, для этого:
- щёлкнуть правой кнопкой мыши внутри области Легенды;
- выбрать в контекстном меню пункт Формат легенды;
- на вкладке Шрифт выбрать размер шрифта 16, на вкладке Вид выбрать желаемый цвет фона Легенды, ОК.
увеличить размер области Легенды, для этого подвести указатель мыши к маркерам выделения области Легенды, указатель примет вид ↔ двунаправленной стрелки, с нажатой левой кнопкой раздвинуть область;
увеличить размер шрифта и фон заголовка Продажа автомобилей.
1.5 Добавить подписи осей диаграммы, для этого:
щёлкнуть правой кнопкой мыши по фону диаграммы, выбрать пункт Параметры диаграммы, вкладку Заголовки;
щёлкнуть левой кнопкой мыши в поле Ось Х (категорий), набрать Тип автомобилей;
щёлкнуть левой кнопкой мыши в поле Ось Y (значений), набрать Количество, шт.
увеличить размер шрифта подписей.
1.6 Задание для самостоятельного выполнения: построить круговую диаграмму, отражающую глубину озер:
Задание 2.
Построить график функции
|
A
|
B
|
C
|
1
|
График функции Sinx
|
2
|
Х, град
|
Х, радиан
|
Sinx
|
3
|
0
|
=(А3*3,14159)/180
|
=SIN(В3)
|
4
|
15
|
|
|
5
|
|
|
|
Записать заголовок таблицы.
Записать в ячейки А3:А4 значения 0 и 15, в ячейках B3:D3 указанные формулы.
Выделить ячейки А3:А4, заполнить диапазон А5:А75 значениями угла 0÷360 град.
Выделить ячейки В3:D3, выполнить автозаполнение в тех же пределах.
Выделить нужный диапазон в столбце С, щёлкнуть кнопку Мастер диаграмм, выбрать Тип График, щёлкнуть Готово, увеличить размер диаграммы за угловые маркеры выделения.
Установить подписи оси ОХ:
Щёлкнуть правой кнопкой мыши по фону диаграммы, выбрать пункт Исходные данные, выбрать вкладку Ряд.
Щёлкнуть в поле Подписи оси Х, обвести с нажатой левой кнопкой значения углов 0÷360 град в столбце А, ОК.
Задание для самостоятельного выполнения: построить график функции
3. Содержание отчета
Отчет должен содержать:
Название работы.
Цель работы.
Задание и его решение.
Вывод по работе.
4.Контрольные вопросы
Какова функция мастера диаграмм, как его вызвать?
Какие типы диаграмм вы знаете?
В каких случаях используются различные типы диаграмм?
Какие параметры можно устанавливать при построении диаграмм?
Практическая работа №22
|
Скачать 6.69 Mb.