Тема 4.2 Возможности электронных таблиц. Математическая обработка числовых данных. Системы статистического учета
Практическая работа № 29
Способы создания формул. Реализация расчетных заданий на основе статистических данных
Цель. Научиться использовать формулы при проведении расчетов в электронных таблицах.
Порядок выполнения работы
Изучить теоретически материал
Составить конспект, изложив в нем вопросы:
Как выбрать и вставить формулу
Относительная и абсолютная адресация
Выполнить задание
Сохранить результат работы в файле
Отчетом о проделанной работе является сохраненный файл.
ТЕОРЕТИЧЕСКИЙ МАТЕРИАЛ
Формула в MSEXCEL начинается со знака равно. Затем идут различные математические действия.
Пример.
Арифметические операции.
Операция
|
Знак
|
Сложение
|
+
|
Вычитание
|
-
|
Умножение
|
*
|
Деление
|
/
|
Возведение в степень
|
^
|
Перемещение данных
Выделить перемещаемый блок (ячейку);
Поместить указатель мыши на рамку блока (указатель примет форму белой стрелки);
Перетянуть блок (ячейку) в новое место.
Копирование данных
Копирование данных выполняется аналогично перемещению, но при нажатой клавише CTRL.
Заполнение (копирование формулы)
Выделить ячейку или блок ячеек;
Поместить указатель в правый нижний угол ячейки или блока (указатель примет форму черного плюса );
Протянуть мышь до нужной ячейки.
Очистка ячеек
Выделить ячейку (блок ячеек) – кнопка DELETE
 Относительная и абсолютная адресация ячеек
Адреса ячеек, используемые в формулах, определены не абсолютно, а относительно места расположения формулы. Поэтому при всяком перемещении (копировании, переносе, вставке и т.д.) имена ячеек в формуле меняются. Такой адрес является относительным (А1, В4, С18).
В некоторых случаях необходимо, чтобы при переносе формулы адрес ячейки не изменялся. Тогда адрес этой ячейки определяют как абсолютный. Для этого перед номером строки или буквой столбца или перед тем и другим ставят знак $.
Примеры обозначения ячеек с абсолютным адресом:
$A4-изменяется цифра, обозначающая номер строки, буква, обозначающая столбец неизменна.
A$4 – изменяется буква, обозначающая столбец, цифра, обозначающая строку остается неизменной.
$A$4 – буква, обозначающая столбец и цифра, обозначающая номер строки остаются неизменными.
ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ
Задание.
Запустите табличный процессор EXCEL;
На листе 1 выполните задание:
Создайте таблицу «Расчет заработной платы», поместив заголовок к таблице в центре
При оформлении заголовков задайте перенос текста по словам
Заполните столбцы ФИО и оклад данными на 10 человек;\
В остальные столбцы введите формулы только для первого работника, для остальных.
Премия = Оклад*30%;
Сев.коэфф. = (Оклад + Премия)*15%
Итого начисл. = Оклад + премия + Сев.коэфф.;
Пенс.фонд = Итого начисл.*1%
Подох. Налог = (Итого начисл.- Пенс.фонд)*12%;
Итого отчисл. = Пенс.фонд+Подох.налог
Итого к выдаче = Итого начисл.- Итого отчисл.
Скопируйте таблицу ниже 2 раза для февраля и марта, измените название месяца на февраль и март, а также измените процент премии – на 20% и 40%.
На листе 2 оформите таблицу по образцу:
Оформите таблицу на листе 3и проведите вычисления по формулам
Вычисление по формулам
Фигура
|
Значение
|
Вычисление
|
Формула
|
Результат
вычисления
|
A
|
R
|
π
|
Круг
|
|
5
|
3,14
|
Площадь
|
S=πR2
|
|
Длина окружности
|
C=2 πR
|
|
Квадрат
|
8
|
|
Площадь
|
S= A2
|
|
Периметр
|
P=4A
|
|
На листе 4 оформите таблицу:
ГАЗЕТЫ И ЖУРНАЛЫ
|
№
п/п
|
Наименование
газеты, журнала
|
Цена на
01.09.2010г.
|
Наценка,
в %
|
Цена на
01.01.2011г.
|
1
|
Правда
|
5
|
0,01
|
|
2
|
Известия
|
7,4
|
0,01
|
|
3
|
Комсомольская правда
|
6
|
0,01
|
|
4
|
Комок
|
10
|
0,01
|
|
5
|
Телесемь
|
7
|
0,01
|
|
6
|
Проспект
|
8
|
0,01
|
|
7
|
Древо познания
|
35,50
|
0,01
|
|
8
|
Вокруг света
|
50,00
|
0,01
|
|
9
|
Из рук в руки
|
4,70
|
0,01
|
|
Создать заголовок шрифтом 22 жирным курсивом;
Заполните таблицу данными;
Ввести формулу для подсчета стоимости первой газеты после наценки, для остальных изданий формулу скопировать;
Оформить границы таблицу по образцу - м.Формат/п.Ячейки/Граница или (кнопка Границы/п.Нарисовать границы);
Скопируйте таблицу на лист 4;
В столбцах С и Е задать формат ячеек – денежный, два знака после запятой, в столбце D задать формат ячеек – процентный, без знаков после запятой (м.Формат/п.Ячейки/Число/Выбрать формат);
Между 7 и 8 изданиями вставить строку и заполнить ее данными для журнала «ПК просто!»;
9. На четвертом листе создать таблицу и провести вычисления
Примечание. 1Гб=1024 Мб
10. На пятом листе создайте таблицу и проведите вычисления
11. На 6 листе проведите вычисления в таблице.
Сохраните выполненное задание на диске в своей папке
ОБЕСПЕЧЕННОСТЬ ЗАНЯТИЯ
1.Персональный компьютер.
2. Программа MS EXCEL.
3. Методические рекомендации по выполнению практической работы.
КРИТЕРИИ ОЦЕНКИ
Минимальное количество баллов за практическую работу, которая подтверждает требования к освоению темы дисциплины, составляет 2 балла.
Максимальное количество баллов, которое может получить обучающийся за практическую работу – 7 баллов.
Задания выполнены полном объеме и в соответствии с образцом. Допускается один незначительный недочет.
|
6-7 баллов
|
Задания выполнены не в полном объеме (не менее 70%), но в соответствии с образцом, при задании наводящих вопросов просматривается тенденция, что невыполненная работа может быть завершена.
|
4-5 баллов
|
Задания выполнены не в полном объеме(менее 70%) и(или) не в соответствии с образцом
|
2-3 балла
|
Сделано менее 70% работы, с грубым ошибками, наводящие вопросы показывают, что задание не будет выполнено
|
0-1 балл
|
Далее ведется перевод набранных баллов в оценку работы.
Оценка по 5-ти бальной шкале
|
5
|
4
|
3
|
2
|
Набранные баллы
|
7 - 6
|
5 - 4
|
3 - 2
|
Менее 1
|
Раздел 4 ТЕХНОЛОГИИ СОЗДАНИЯ И ПРЕОБРАЗОВАНИЯ ИНФОРМАЦИОННЫХ ОБЪЕКТОВ
Тема 4.2 Возможности электронных таблиц. Математическая обработка числовых данных. Системы статистического учета
Практическая работа № 30
Мастер функций. Использование для расчетов статистических и математических функций
Цель. Научиться использовать математические и статистические функции при проведении расчетов в электронных таблицах.
ТЕОРЕТИЧЕСКИЙ МАТЕРИАЛ
Математические функции
1 способ
Выбрать команду Вставка.
Выбрать команду Формулы.
Выбрать нужную категорию.
Выбрать нужную формулу.
Нажать ОК.
Ввести необходимые данные для формулы и нажать Enter.
Пример.
2 способ
Выбрать команду Вставка.
Выбрать команду Формулы.
Выбрать 
Выбрать категорию и нажать ОК.
Выбрать нужную формулу.
Нажать ОК.
Ввести необходимые данные для формулы и нажать Enter.
Пример.
Основные математические функции.
СУММ (список аргументов) — суммирует все числа из списка аргументов.
КОРЕНЬ (число) — извлекает корень из числа.
ПРОИЗВЕД (список аргументов) — перемножает аргументы;
SIN (число) — подсчитывает синус числа; COS (число) — подсчитывает косинус числа.
Основные статистические функции
МАКС (список аргументов) — выбирает максимальное значение из списка аргументов;
МИН (список аргументов) — выбирает минимальное значение из списка аргументов;
СРЗНАЧ (список аргументов) — подсчитывает среднее значение чисел из списка аргументов;
Р   АНГ (адрес числа; адрес блока чисел; порядок) — определяет ранг (место) числа в списке чисел
имя ячейки, в которой записано число, ранг которого определяют
адрес блока чисел, среди которых определяется ранг числа
число, определяющее способ округления — необязательный параметр
Примеры статистических функций
МАКС (А1:А4) выбирает максимальное значение среди чисел А1,А2,А3,А4
СРЗНАЧ(В2:В6) подсчитывает среднее арифметическое значение чисел В2,В3,В4,В5,В6
РАНГ(А1;А1:А5) определяет место числа, находящегося в ячейке А1 по отношению к числам, находящимся в ячейках А1,А2,А3,А4,А5.
Пример.
Вычислить ранги чисел, записанных в таблице.
1.Установить курсор в ячейку В1.
2. Выбрать функцию РАНГ, указав блок ячеек А2:В4 , использовав абсолютный адрес.
3. Нажать ОК.
4. Скопировать формулу в оставшиеся ячейки.
Задание
Запустите табличный процессор EXCEL.
На первом листе оформите таблицу расчета оклада работника в зависимости от коэффициента
величину минимальной оплаты труда (4350) поместить в отдельной ячейке;
столбец Оклад считается по формуле с использованием абсолютной адресации:
Оклад = Минимальный размер оплаты труда * Коэффициент
для столбца Оклад задать формат ячеек – денежный с двумя знаками после запятой.(Адрес ячейки в формуле, в которой находится оклад должен иметь знак$ перед номером столбца и номера строки: $C$1)
На втором листе создать таблицу Расчет цены товаров
Расчет цены товаров
|
|
Размер скидки
|
20%
|
|
№
п/п
|
Наименование товара
|
Цена без скидки
|
Цена со скидкой
|
1
|
Флэш - память
|
500
|
|
2
|
Винчестер
|
2000
|
|
3
|
DWD - диск
|
55
|
|
4
|
CD -диск
|
20
|
|
рассчитать столбец Цена со скидкой с помощью формулы, используя абсолютную адресацию для неизменяемых ячеек(Цена со скидкой=Цена без скидки – Цена без скидки*на процент скидки).
На третьем листе вычислить значение функции У=Х2 для Х, изменяющегося с шагом 0,2 в интервале от -5 до 5. Использовать автозаполнение. Для этогозаписать в столбец Х два первых значения Х, выделить эти значения, установить курсор на угол последней выделенной ячейке и протянуть курсор, пока Х не примет значение -5.
На четвертом листе провести вычисления в таблице, используя функции МИН, МАКС,СУММ.
На пятом листе создать таблицу и заполнить ее 10 записями. Средний балл вычислять с помощью функции СРЗНАЧ (Статистическая функция) с одним знаком после запятой.
На шестом листе создать таблицу и проведите в ней расчеты
Определение скорости движения транспортного средства
|
Транспортное средство
|
Пройденное расстояние(км)
|
Время (ч)
|
Скорость (км/ч)
|
Велосипед
|
3
|
0,4
|
|
Трактор
|
10
|
0,5
|
|
Грузовик
|
70
|
1
|
|
Легковой Автомобиль
|
300
|
2,5
|
|
Поезд
|
600
|
6
|
|
Самолет
|
1200
|
2
|
|
Задание 8.
Создайте таблицу Территория и население по континентам, заполнив данными столбцы.
Подсчитайте с помощью формулы (Население / Площадь территории) столбец Плотность населения в 1970 и в 1989 годах.
Для чисел в столбцах Плотность населения установите формат – 4 знака после запятой.
Название континента
|
Площадь территории,
в млн.кв.км
|
Население, млн.чел.
|
Плотность населения,
чел. на кв.км
|
Население, млн. чел
|
Плотность населения,
чел. на кв.км
|
|
|
1970 год
|
1970 год
|
1989 год
|
1989 год
|
Африка
|
30,3
|
361
|
|
628
|
|
Сев. И Центр Америка
|
24,3
|
320
|
|
422
|
|
Южная Америка
|
17,8
|
190
|
|
291
|
|
Австралия и Океания
|
8,5
|
19
|
|
26
|
|
Европа
|
10,5
|
642
|
|
701
|
|
Азия
|
44,4
|
2161
|
|
3133
|
|
Создайте снизу обобщающую таблицу.
С помощью математической функции СУММ подсчитайте общую площадь территории, население мира для 1970 и 1989 годов.
С помощью статистических функций СРЗНАЧ, МИН, МАКС вычислить среднюю плотность населения в 1970 и 1989 годах, минимальную - в 1970 году и максимальную плотность населения в 1989 году.
Территория и население по континентам.
Общая площадь территории всего мира
|
|
Население всего мира в 1970 году
|
|
Население всего мира в 1989 году
|
|
Средняя плотность населения в 1970 году
|
|
Средняя плотность населения в 1989 году
|
|
Минимальная плотность населения в 1970 году
|
|
Максимальная плотность населения в 1989 году
|
|
Задание 9. На втором листе создать таблицу Результаты соревнований: заполнить столбец Место с помощью функции РАНГ, используя для неизменяемых ячеек абсолютную адресацию.
Результаты соревнований
|
№
п/п
|
Фамилия
|
Время
|
Место
|
1
|
Петров
|
12
|
|
2
|
Иванов
|
9
|
|
3
|
Сидоров
|
11
|
|
4
|
Смирнов
|
10,5
|
|
5
|
Галкин
|
8
|
|
6
|
Нестеров
|
7
|
|
7
|
Воронов
|
9
|
|
Задание 10. На третьем листе создать таблицу Результаты экзаменов:
Заполнить столбцы оценками (от 2 до 5).
С помощью математической функции СУММ подсчитать общую сумму оценок для первого человека, для остальных формулу скопировать.
С помощью статистической функции СРЗНАЧ подсчитать среднюю оценку первого человека, для остальных формулу скопировать.
С помощью статистической функции РАНГ подсчитать место для первого человека, отредактировать формулу с учетом абсолютной адресации для параметров, которые не должны меняться при копировании. Затем формулу скопировать.
Результаты экзаменов
|
Фамилия
|
Оценки по предметам
|
Сумма
|
Средний балл
|
Место
|
История
|
Химия
|
Физика
|
Матем.
|
Литер.
|
Акимов
|
|
|
|
|
|
|
|
|
Васильев
|
|
|
|
|
|
|
|
|
Громов
|
|
|
|
|
|
|
|
|
Грязнов
|
|
|
|
|
|
|
|
|
Дымов
|
|
|
|
|
|
|
|
|
Жуков
|
|
|
|
|
|
|
|
|
Зуев
|
|
|
|
|
|
|
|
|
Иволгин
|
|
|
|
|
|
|
|
|
Иванин
|
|
|
|
|
|
|
|
|
Кузнецов
|
|
|
|
|
|
|
|
|
Лимонов
|
|
|
|
|
|
|
|
|
ОБЕСПЕЧЕННОСТЬ ЗАНЯТИЯ
1.Персональный компьютер.
2. Программа MS EXCEL.
3. Методические рекомендации по выполнению практической работы.
КРИТЕРИИ ОЦЕНКИ
Минимальное количество баллов за практическую работу, которая подтверждает требования к освоению темы дисциплины, составляет 2 балла.
Максимальное количество баллов, которое может получить обучающийся за практическую работу – 7 баллов.
Задания выполнены полном объеме и в соответствии с образцом. Допускается один незначительный недочет.
|
6-7 баллов
|
Задания выполнены не в полном объеме (не менее 70%), но в соответствии с образцом, при задании наводящих вопросов просматривается тенденция, что невыполненная работа может быть завершена.
|
4-5 баллов
|
Задания выполнены не в полном объеме(менее 70%) и(или) не в соответствии с образцом
|
2-3 балла
|
Сделано менее 70% работы, с грубым ошибками, наводящие вопросы показывают, что задание не будет выполнено
|
0-1 балл
|
Далее ведется перевод набранных баллов в оценку работы.
Оценка по 5-ти бальной шкале
|
5
|
4
|
3
|
2
|
Набранные баллы
|
7 - 6
|
5 - 4
|
3 - 2
|
Менее 1
|
РАЗДЕЛ 4 ТЕХНОЛОГИИ СОЗДАНИЯ И ПРЕОБРАЗОВАНИЯ ИНФОРМАЦИОННЫХ ОБЪЕКТОВ
|
