Рабочая тетрадь содержит задания для самостоятельной работы обучающихся и разбивается на тематические разделы. Многие практические работы имеют инструкционные карты для самостоятельного выполнения. В теоретической части есть задания для проверки полученных знаний и умения. Обучающиеся могут самостоятельно освоить программу заполняя рабочую тетрадь и выполняя задания

КОМИТЕТ ОБРАЗОВАНИЯ И НАУКИ ВОЛГОГРАДСКОЙ ОБЛАСТИ

Государственное автономное профессиональное образовательное учреждение "Волжский промышленно - технологический техникум"

Рабочая тетрадь «Microsoft Excel 2007»

по учебной дисциплине ОП 01. «Основы информационных технологий»

Содержание

ВВЕДЕНИЕ...................................................................................................... 3

Интерфейс MS Excel........................................................................................ 4

Типы данных MS Excel.................................................................................... 7

Ввод формул.................................................................................................... 8

Вычисления на рабочем _листе......................................................................... 9

_{Формулы даты и времени}.............................................................................. 12

_{Математические формулы}............................................................................. 17

Виды функц_{ий
MS Excel}................................................................................ 19

Логические функции...................................................................................... 20

_{Функции даты и времени}............................................................................... 21

_{Финансовые функции: анализ
кредитов и вкладов}........................................ 26

_{Финансовые функции: расчет
амортизации}.................................................. 30

Графические средства MS Excel.................................................................... 32

_{Основные элементы диаграммы}.................................................................... 32

Типы диаграмм.............................................................................................. 33

_{ПРАКТИЧЕСКИЕ РАБОТЫ}.......................................................................... 35

_{Практическая работа №1
«Форматирование ячеек MS Excel»}...................... 35

Практическая работа №2 «Заполнение последовательностей MS Excel»...... 36

_{Практическая работ №3
«Форматирование таблицы»}................................... 37

_{Практическая работа №4
«Работа с формулами в MS Excel»}........................ 39

Практическая работа №5 «Применение мастера функций для составления .....

формул»......................................................................................................... 44

Практическая работа №6 «Работа с логическими функциями_»..................... 48

_{Практическая работа №7
«Вложенные функции»}......................................... 50

_{Практическая работа №8
«Сложные вычисления в MS Excel»}..................... 53

Практическая работа №9 _{«Построение
диаграмм»}....................................... 56

_{Практическая работа №10
«Составление интерактивного теста»}................. 58

ВВЕДЕНИЕ

Электронные таблицы Excel пользуются большой популярностью во всем мире, позволяя готовить достаточно профессиональные документы и проводить необходимые расчеты. Данное учебное пособие содержит в себе теоретические основы и ряд лабораторных работ, охватывающих основные возможности электронных таблиц Excel.

В рабочей тетради использованы рисунки–пиктограммы, указывающие на тип задания:

& - краткие теоретические знания

3 - заполнить таблицу

$ - задания выполняются в тетради

8 - выполнить задание, используя компьютер

Интерфейс MS Excel

$Назначение программы _________________________________________

__________________________________________________________________

$Возможности программы Microsoft Excel

1. _____________________________________________________________

2. _____________________________________________________________

3. _____________________________________________________________

4. _____________________________________________________________

5. _____________________________________________________________

6. _____________________________________________________________

7. _____________________________________________________________

8. _____________________________________________________________

$Алгоритм запуска программы Excel

Пуск\ ___________________ \ _____________________ \ Microsoft Office Excel 2007

$Объекты электронной таблицы:

Ячейка – __________________________________________________________

__________________________________________________________________

________________________________________________________________

Строка – ________________________________________________________

__________________________________________________________________

_______________________________________________________________

Столбец – _________________________________________________________

__________________________________________________________________

_______________________________________________________________

Диапазон ячеек – ___________________________________________________

__________________________________________________________________ __________________________________________________________________

_______________________________________________________________

& Панели инструментов процессора Excel

Главная	Работа с буфером обмена, Шрифт, Выравнивание, Формат числа, условное форматирование, Формат ячеек, автосумма, сортировка и фильтрация, поиск и замена
Вставка	Сводная таблица, рисунок, фигура, диаграмма, гиперссылка, надпись, колонтитулы, WordArt, объект
Разметка страницы	Темы, параметры страницы, масштаб, параметры листа
Формулы	Библиотека функций
Данные	Сортировка и фильтр, работа с данными, поиск решения
Рецензирование	Правописание, примечания, защита листа и книги
Вид	Режимы просмотра книги, масштаб, работа с окнами, работа с макросами

$Структура окна MS Excel

1._________________________________________________________________

2._________________________________________________________________

3._________________________________________________________________

4._________________________________________________________________

5._________________________________________________________________

6._________________________________________________________________

7._________________________________________________________________

8._________________________________________________________________

9._______________________________________________________________

$Основные термины

1. При запуске программы Excel открывается окно, содержащее __________________________________________________________________

2. Рабочая книга состоит из __________________________________________

3. Столбцы обозначаются _______________, строки _____________________

4. На пересечении ____________ и _____________ находятся _____________

5. Группа ячеек, образующая прямоугольник называется _________________ 3 Перемещение по рабочему листу

Клавиша	Перемещение
< >, < >, < >, < >
Tab
Enter
Shift + Tab
Ctrl + Home
Ctrl + End
Page Up
Page Down
Alt + Page Up
Alt + Page Down

$Выделение ячеек и интервалов:

Для выделения мышью указатель наводится в центр ячейки и выглядит Выделение нескольких ячеек: удерживать кнопку _______ и щелкать ЛМК по нужным ячейкам.

Выделения блока ячеек: щелкнуть по первой ячейке, нажать клавишу

________ и щелкнуть по последней; щелкнуть по первой ячейке и ___________________________________________________ до последней.

Типы данных MS Excel.

$ Данные, вносимые в ячейки таблицы Excel подразделяются на типы:

1. Число – вводятся в обычном и экспонециальном виде. Целая часть от дробной отделяется ________. С числовыми данными можно выполнять вычисления.

& В таблицах Excel число можно представить в формате:

Общий – как введен;

Числовой – с выбранным количеством десятичных знаков;

Денежный и Финансовый – с добавлением символа валюты

Дата и время – в выбираемом формате;

Процентный – число умножается на 100 и добавляется знак %

Дробный – десятичная часть числа дается в виде простой дроби

2. Текст – это любые _____________. Если текст начинается с цифры, то перед ним ставится ___________________. С символьными данными недопустимы математические операции.

3. Дата – число представленное в специальном ____________. Даты имеют два вида внутренний – количество дней от начала 1900 г. и внешний – дд.мм.гг. – 15.11.01.

4. Формула – _______________________________________________________

___________________________. Формула начинается со знака _________ ссылки на другие ячейки (применяются в составе формул); функции (подпрограмма, выполняющая часто встречающиеся операции, например вычисление sin(x), cos(x)).

3 Заполните таблицу:

Опера тор	Действие	Пример	Опера тор	Действие	Пример
+			/
-			%
*			^

& В работе с электронными таблицами можно выделить три основных типа данных: число, текст и формула. 3 Впишите формат указанного числа:

Формат	Результат
	1 234,567
	1 234,567р.
	123456,7%
	1,235Е+03
	12:34:56

Ввод формул

$ Если в ячейку ввести текст, то в ней отобразится

________________________________________________________

__________________________________________________________

$ Если в ячейку ввести число, то в ней отобразится

_____________________________________________________________

______________________________________________________________

& Ввод формулы отличается тем, что в ячейке отображается не сама формула (набор введенных символов), а результат вычислений по этой формуле.

!!! ВВОД ФОРМУЛЫ НАЧИНАЕТСЯ СО ЗНАКА РАВЕНСТВА !!!

& Алгоритм ввода формул:

1. Щелкните ячейку, в которую нужно ввести формулу

2. Введите знак равенства - обязательное начало формулы

3. Введите первый аргумент - число или ссылку на ячейку. Адрес можно ввести вручную или вставить автоматически, щелкнув нужную ячейку

4. Введите знак арифметического действия

5. Введите следующий аргумент

6. Повторяя пункты 4 и 5, закончите ввод формулы

7. Нажмите Enter. Обратите внимание, что в ячейке отображается результат вычислений, а в строке формул - сама формула.

$ Виды ссылок:

Относительная ссылка -____________________________________________

__________________________________________________________________

Абсолютная ссылка –______________________________________________

__________________________________________________________________

Смешанная ссылка –______________________________________________

__________________________________________________________________

Как сделать копию с листа и дать ей другое имя?________________________

__________________________________________________________________ __________________________________________________________________

______________________________________________________________

Вычисления на рабочем листе

$ Вычисление – это _______________________________________________

__________________________________________________________________

_________________________________________________________________

3 Значение сообщений об ошибках

Обозначение ошибки	Определение ошибки	Причина возникновения ошибки
####
#ДЕЛ/0!
#ИМЯ?
#ЗНАЧ!
#ССЫЛКА!
#Н/Д
#ЧИСЛО!
#ПУСТО!

$ Макрос - _______________________________________________________

__________________________________________________________________

________________________________________________________________

& Для создания и использования интерактивных тестов и кроссвордов с макросами должен быть установлен низкий уровень безопасности (вкладка Разработчик – группа Код – команда Безопасность макросов, в окне Центр управления безопасностью, в группе команд Параметры макросов установить флажок Включить все). Если отсутствует вкладка Разработчик необходимо зайти в основное меню, зайти в Параметры Excel, на вкладке Основные поставить флажок Показывать вкладку Разработчик на ленте.

3 Определить причину возникновения ошибки и указать способы ее устранения

Ошибка	Причина и способ устранения

Формулы даты и времени

$ Область применения формул дат и времени:

1. Сложение дат позволяет _______________________________________

__________________________________________________________________ __________________________________________________________________

__________________________________________________________________

=ДАТА(ГОД(А1)+Х;МЕСЯЦ(А1)+Y;ДЕНЬ(А1)+Z) где Х – количество прибавляемых лет, Y - количество прибавляемых месяцев, Z - количество прибавляемых дней

2. Добавление времени позволяет __________________________________

__________________________________________________________________

__________________________________________________________________ ________________________________________________________________

=А1+ВРЕМЯ(X;Y;Z) где Х – количество прибавляемых часов, Y - количество прибавляемых минут, Z - количество прибавляемых секунд

3. Вычисление разности двух дат позволяет _________________________

__________________________________________________________________

1. =ЧИСТРАБДНИ(А1;А2) – количество рабочих дней между двумя датами

2. =МЕСЯЦ(А1)-МЕСЯЦ(А2) – количество месяцев между двумя

датами (если года в ячейках совпадают)

3. =(ГОД(А2)-ГОД(А1))*12+МЕСЯЦ(А2)-МЕСЯЦ(А1) - количество месяцев между двумя датами (если года в ячейках не совпадают) где А1 и А2 – ячейки в которых введены даты

4. Вычисление разности во времени позволяет ____________________

__________________________________________________________________

_______________________________________________________________

1. =ТЕКСТ(А1-А2; "ч") – количество часов между двумя значениями

2. =ТЕКСТ(А1-А2; "ч:мм") – количество часов и минут между двумя значениями

3. =ТЕКСТ(А1-А2; "ч:мм:сс") – количество часов, минут и секунд между двумя значениями

где А1 и А2 – ячейки в которых введено время

позволяет _________________________________________________________ __________________________________________________________________

__________________________________________________________________

1. =ЧАС(А1-А2) – количество часов между двумя значениями, если разница не превышает 24часа

2. =МИНУТЫ(А1-А2) – количество часов между двумя значениями, если разница не превышает 60минут

3. =СЕКУНДЫ(А1-А2) – количество часов между двумя значениями, если разница не превышает 60секунд

где А1 и А2 – ячейки в которых введено время

5. Подсчет дней перед датой позволяет ____________________________

__________________________________________________________________

=А1-СЕГОДНЯ( ) где А1 – ячейка в которой введена дата

6. Отображение дат в виде дней недели позволяет ___________________

__________________________________________________________________

=ТЕКСТ(А1; "дддд") - определяет полное название дня недели =ТЕКСТ(А1; "ддд") - определяет сокращенно название дня недели где А1 – ячейка в которой введена дата

7. Вставка в ячейку текущей даты и времени позволяет ______________

__________________________________________________________________

= СЕГОДНЯ ( ) – вводит текущую дату в ячейку

=ТДАТА( ) – вводит текущую дату и времени в ячейку

$ Задание 1. В ячейку А1 введена дата 02.12.2015, в ячейку А2 введена дата 12.06.2016, в ячейку В1 введено время 18:15:54, в ячейку В2 введено время 23:18:55. Записать формулы для:

1. расчета количества дней до 12.06.2016

__________________________________________________________________

2. вычисления разности во времени (в часах)

__________________________________________________________________

3. вычисления разности во времени с точностью до секунд

__________________________________________________________________

4. определения количества месяцев между двумя датами

__________________________________________________________________

5. определения времени через 5часов 55сек от времени указанном в ячейке В2 __________________________________________________________________

6. определения количества рабочих между двумя датами

_________________________________________________________________

7. определения даты через 3месяца и 2дня после даты указанной в ячейке А2 __________________________________________________________________

8. определения даты через 1год и 3дня после даты указанной в ячейке А1

_________________________________________________________________

$ Задание 2. Записать формулы для:

1. расчета количества дней до 15.11.2016

__________________________________________________________________

2. вычисления разности во времени (в часах)

__________________________________________________________________

3. определения времени через 5часов 55сек от времени указанном в ячейке D2 __________________________________________________________________

4. определения количества рабочих между двумя датами

__________________________________________________________________

5. определения даты через 1год и 3дня после даты указанной в ячейке B3 __________________________________________________________________ $ Задание 3. Записать формулы для:

1. вычисления разности во времени с точностью до секунд

__________________________________________________________________

2. определения количества месяцев между двумя датами

__________________________________________________________________

3. определения времени через 8часов 45сек от времени указанном в ячейке

C2 _______________________________________________________________

4. определения количества рабочих между двумя датами

__________________________________________________________________

5. определения даты через 6месяцев и 12дней после даты указанной в ячейке

А2________________________________________________________________

$ Задание 4. Записать формулы для:

1. расчета количества дней до 05.09.2016

__________________________________________________________________

2. вычисления разности во времени (в часах)

__________________________________________________________________

3. определения количества месяцев между двумя датами

__________________________________________________________________

4. определения количества рабочих между двумя датами

__________________________________________________________________

5. определения даты через 2месяца и 12дней после даты указанной в ячейке C2 _______________________________________________________________ $ Задание 5. Записать формулы для:

1. вычисления разности во времени с точностью до секунд

__________________________________________________________________

2. определения количества месяцев между двумя датами

__________________________________________________________________

3. определения времени через 12часов 12сек от времени указанном в ячейке A2 ________________________________________________________________

4. определения количества рабочих между двумя датами

__________________________________________________________________

5. определения даты через 1месяц и 15дней после даты указанной в ячейке C2 _______________________________________________________________

Математические формулы

& Применение математических формул:

1. Сложение чисел

= А1 +А2 – удобно для сложения небольшого количества чисел =СУММ(А1:А2) – удобно для сложения большого количества чисел где А1 и А2 – ячейки с числами

2. Вычитание чисел

= А1 –А2, где А1 и А2 – ячейки с числами

3. Умножение чисел

= А1 *А2 – удобно для умножения небольшого количества чисел =ПРОИЗВЕД(А1:А2) – удобно для умножения большого количества чисел, где А1 и А2 – ячейки с числами

4. Деление чисел

= А1 /А2, где А1 и А2 – ячейки с числами

5. Вычисление процентных отношений

= А1*5% - нахождения 5% от заданного числа =А1*(1+5%) – увеличение заданного числа на 5% =А1*(1-5%) – уменьшение заданного числа на 5% где А1 – ячейка с числом

6. Возведение числа в степень

Возведение числа в А1 ячейке в степень х =А1^х

ИЛИ =СТЕПЕНЬ(А1;х)

вместо ячейки с числом можно использовать и само число, введенное с клавиатуры

$ Записать формулы для Excel:

1. y=3х²+5х-4, значение х находится в ячейке С1

__________________________________________________________________

2. y= (4:2+3)*6

_______________________________________________________________

y78

______________________________________________________________

4. вычислить сумму чисел в диапазоне ячеек В1:G11

__________________________________________________________________

5. возвести число, находящееся в ячейке С3 в 5 степень

________________________________________________________________

Виды функций MS Excel

$ Функция – это ________________________________________________

__________________________________________________________________

_______________________________________________________________

=<имя функции>(аргументы)

3 Категории функций:

Категория	Назначение функции
Финансовые
Дата и время
Математические
Статистические
Ссылки и массивы
Работа с базой данных
Текстовые
Логические
Информационные
Инженерные

Логические функции

& Логические функции позволяют решать с помощью табличного процессора логические задачи. В логической функции обязательно используется условие.

$ Условие представляет собой величины или выражения одного типа, связанные одним из знаков отношений:

1. равно ______________

2. не равно ________________

3. меньше _________________________

4. больше __________________________

5. меньше или равно ________________________

6. больше или равно ___________________________

Использование функции Если

$ Функция ЕСЛИ имеет следующий формат записи:

__________________________________________________________________

выражение_если_истина, если проверяемое условие _______________

выражение_если_ложно, если проверяемое условие ___________________ Использование функции СчетЕсли & Эта функция используется для подсчета количества ячеек, удовлетворяющим какому – нибудь условию в выбранном диапазоне.

8 Задание. В таблице рассчитать стоимость железнодорожных билетов для поездов разных категорий: пассажирские, скорые, фирменные. Цена билетов зависит от времени движения поезда к пункту назначения и категории поезда. Каждой категории поезда соответствует определенный коэффициент, на который следует умножать время в пути (в минутах) для расчета цены билета: пассажирский – 0,5 р., скорый – 1,5 р., фирменный – 1,7 р. Организовать таблицу так, чтобы перерасчет цен билета можно было бы осуществлять, изменяя значение ячейки Категория.

Функции даты и времени

$ Записать назначение функции и ее формат

Функция

Назначение

Формат функции

ВРЕМЯЗНАЧ

ВРЕМЯ
ГОД
ДАТА
ДАТАЗНАЧ
ДАТАМЕС

ДЕНЬ
ДЕНЬНЕД
ДНЕЙ360
ДОЛЯГОДА
КОНМЕСЯЦА

МЕСЯЦ
МИНУТЫ
НОМНЕДЕЛИ
РАБДЕНЬ
СЕГОДНЯ
СЕКУНДЫ
ТДАТА
ЧАС
ЧИСТРАБДНИ

Финансовые функции: анализ кредитов и вкладов

$ Записать назначение функции и ее формат

Функция	Назначение функции	Формат функции и значение аргументов
БЗРАСПИС
БС
ВСД
КПЕР

МВСД
НОМИНАЛ
ОБЩДОХОД
ОБЩПЛАТ

ОСПЛТ
ПЛТ
ПРОЦПЛАТ
ПРПЛТ

ПС
СТАВКА
ЧИСТВНДОХ
ЧИСТНЗ
ЧПС
ЭФФЕКТ

Финансовые функции: расчет амортизации

& Под амортизацией понимается уменьшение стоимости имущества в процессе эксплуатации.

Суть амортизации – отчисления, предназначенные для возмещения износа имущества

$ Методы амортизации основных средств:

1. _______________________________________________________________

2. _______________________________________________________________

3. _______________________________________________________________

4. _______________________________________________________________

5. _______________________________________________________________ $ Записать назначение функции и ее формат

Функция	Назначение функции	Формат функции и значение аргументов
АПЛ
АСЧ
ДДОБ

Графические средства MS Excel

$Основные элементы диаграммы

$ Основные понятия:

Исходные данные диаграммы – _______________________________________

__________________________________________________________________

Заголовок диаграммы – ______________________________________________

__________________________________________________________________

Оси диаграммы - ___________________________________________________

__________________________________________________________________

Линии сетки – _____________________________________________________

__________________________________________________________________

Легенда диаграммы – _______________________________________________

__________________________________________________________________

Заголовки значений – ________________________________________________

__________________________________________________________________

Подписи данных – __________________________________________________

__________________________________________________________________ Таблица данных – ___________________________________________________ __________________________________________________________________

Типы диаграмм.

$ Для наглядного представления данных необходимо подобрать Тип и Вид диаграммы. Excel предлагает следующие виды и типы диаграмм:

Гистограмма – _________________________________

_______________________________________________

Графики - _________________________________________________________

__________________________________________________

_________________________

_______________________

Круговая диаграмма - ______________________

__________________________________________

Линейчатые диаграммы - __________________

____________________________________________ ____________________________________________

___________________________________________

Диаграммы с областями - _________________

_________________________________________

Поверхностные диаграммы - _____________ _______________________________________

_______________________________________

__________________________________________________________________

Кольцевая диаграмма - ___________________

________________________________________

________________________________________ ________________________________________

________________________________________

Лепестковая диаграмма - __________________

_________________________________________

_______________________________________

ПРАКТИЧЕСКИЕ РАБОТЫ

Практическая работа №1 «Форматирование ячеек MS Excel»

Цель: изучить основные команды необходимые при форматировании ячеек электронной таблицы, приобрести и закрепить практические навыки по созданию и редактированию табличного документа

8 Задание 1. Изменение ширины столбцов и высоты сток

1) Навести мышь на: _______________________

2) Использовать команду ____________________

_____________________________________________________

3) Использование мыши для изменения высоты строк

____________________________________________

4) Использование команды для изменения высоты строк

____________________________________________________

8 Задание 2. «Расписание уроков».

Создай и заполни таблицу. Для объединения ячеек A1:M1 выдели их и нажми кнопку Объединить и поместить в центре . Также объедини ячейки B2:B7 и текст выровняй по центру по центру горизонтали. Для поворота текста используй кнопку Ориентация

текста . Ширину столбцов настрой самостоятельно по образцу.

Практическая работа №2 «Заполнение последовательностей MS Excel»

Цель: Приобрести и закрепить практические навыки по созданию электронной таблицы с использованием возможностей автозаполнения 8 Задание 1. Для заполнения интервала текстовой последовательностью необходимо:

1. Выбрать ____________________________

2. Навести указатель мыши на ______________ 3. Нажать левую кнопку мыши, и не отпуская ее, переместить мышь. 4. Отпустить кнопку мыши.

5. Продолжите последовательности вниз

8 Задание 2. Создание числовой последовательности

1) Ввести 2 числа

соседние ячейки и выделить их. 2) Навести мышь на_________ 3) Удерживая левую кнопку мыши переместить мышь в или

8 Задание №3 Составить

календарь на год по образцу, используя функцию автозаполнения

Практическая работ №3 «Форматирование таблицы»

Цель: изучить основные команды необходимые при форматировании электронной таблицы, приобрести и закрепить практические навыки по созданию и редактированию табличного документа

$ Задание 1. Изучить основные команды Форматирования данных

1. ПКМ по ячейке → Формат ячейки

2. Панель инструментов «Абзац»:

___________________________ ____________________________

__________________________ ___________________________

3. Применение вкладки «Шрифт» (заполнить ячейки)

4. Перенос слов внутри ячейки

Выбрать пункт меню ____________________ установить _____________ и нажать OK

5. Применение обрамлений с использованием кнопки__________________

6. Применение узоров и цветов для заполнения ячеек с использованием кнопок

- ________________________

8 Задание 2. Форматирование таблицы

Оформите фрагменты на разных листах книги. При предварительном просмотре фрагменты должны иметь вид, представленные на рисунках.

Фрагмент 1.

Фрагмент 3. Таблица шаблона для шахматного турнира.

Практическая работа №4 «Работа с формулами в MS Excel»

Цель: научиться вычислениям с использованием математических функций, познакомиться с некоторыми функциями Excel; научиться использовать Мастер функций 8 Задание 1. Копирование формул Создай таблицу по образцу.

В ячейку D3 для ввода десятичной дроби используй клавишу Del на числовой клавиатуре.

Названия месяцев и даты вводи с помощью маркера заполнения Данные показания счетчика вводить с клавиатуры.

Ячейки D6, E6 остаются пустые.

Формулы для диапазона D7:E18 достаточно ввести один раз, а затем ее распространить, используя маркер заполнения.

Для подсчета Итого используй функцию Сумма. Для этого: активизируй ячейку D20, нажми кнопку Автосумма на панели

Главная.

В ячейке появиться функция =СУММ(D7:D18) – ячейка D19 для нас лишняя, поэтому мышью выдели диапазон D7:D18 и нажми Enter.

	A	B	C	D	E
1	Плата за электроэнергию
2
3	Тариф на электроэнергию			5,2	тг/Квт*ч
4
5	Месяц	дата	Показания счетчика	Расход Квт*ч	надо оплатить
6	декабрь	31.12.2015	3010
7	январь	31.01.2016	3120	=C7-C6	=D7*5,2
8	…	28.02.2016	3260	=C8-C7	=D8*5,2
9		…	3490
10	…	…	3610
11	…	…	3798
12	…	…	3989
13	…	…	4123
14	…	…	4296
15	…	…	4450
16	…	…	4600
17	ноябрь	30.11.2016	5389
18	декабрь	31.12.2016	5514
19
20			Итого за год

8 Задание 2. Вычисление функций

Составьте таблицу положительных значений функции y=1/x.

3. Выделите столбцы от А до О и установите ширину столбца, равную 45 пиксель.

4. В ячейку В3 введите формулу =1/B2 (Enter).

5. Распространите эту формулу на остальные ячейки, используя маркер автозаполнения (черный крестик в нижнем правом углу ячейки).

6. Выделите все ячейки, содержащие данные и задайте обрамление: Внутренние – тонкая линия, а Внешние – двойная линия. Для назначения границ используйте кнопку Граница на ленте Шрифт или окно

Формат ячеек (вызывается через контекстное меню).

8 Задание 3. Составьте таблицу значений функции Y для заданных значений X (по номеру ПК)

№ варианта	Промежуток Х	Шаг изменения Х	Функция Y
1	От -3 до 3	0,5	Y= – 5X+3
2	От -10 до 10	2	Y= – 5/X
3	От -3 до 3	0,5	Y= X² – 1
4	От -3 до 3	0,5	Y= –( X²)
5	От -2 до 2	0,5	Y= X³
6	От 1 до 6	1	Y= X^-2
7	От -3 до 4	1,5	Y= 2X+1
8	От -1 до 8	2	Y= – Х/2
9	От -5 до 3	0,5	Y= X²
10	От -3 до 5	0,5	Y= –( X²)+1
11	От -7 до 2	1	Y= X³-2
12	От -4 до 4	1,5	Y= X²⁺⁴
16	От -5 до 5	2	Y= 3X-2

8 Задание 4. Создайте заготовку для пункта обмена валюты таким образом, чтобы оператор мог вести число - сумму обмениваемых долларов и немедленно получить ответ в виде суммы в рублях в соответствии с текущим курсом.

Ключ к заданию:

1. В ячейки В2, В3 и В4 введите соответствующий текст. Измените ширину столбца В таким образом, чтобы

текст в каждой ячейке помещался полностью.

2. В ячейку С2 введите значение текущего курса $.

3. В ячейку С3 введите значение обмениваемой суммы в $.

4. В ячейку С4 введите формулу "=С2*С3"

8 Задание 5. Подготовьте бланк заказа для мебельной фабрики по образцу.

Ключ к заданию:

1. В ячейки А1 - С3 введите заголовок таблицы. Выделите эти ячейки и примените к ним полужирный стиль начертания и выравнивание по центру.

2. В ячейки А4 - А8 введите перечень мебели.

3. В ячейки В3 - В8 введите цены и примените к ним денежный формат.

4. Задайте рамку, выделив блок А3- С8.

5. В ячейку В10 введите текст "Сумма заказа" и примените полужирный стиль начертания и выравнивание по правому краю.

6. Для ячейки С10 задайте рамку и примените денежный формат числа.

7. В ячейку А1 введите заголовок "Набор мебели". Примените к этой ячейке полужирный стиль начертания.

9. В столбец "Кол-во" проставьте количество каждого типа мебели.

10. В ячейку С10 введите формулу для вычисления суммы заказа.

8 Задание№6 Расход материалов для окраски

Рассчитать расход материалов для окраски дверей и подоконников, если известен расход материалов на каждые 10м². Площадь окрашиваемых поверхностей ввести самостоятельно. Рассчитать количество материала для ремонта. Составить таблицу следующей формы:

Расход материалов для окраски
Материалы	Поверхности
	Двери				Подоконники
	кг на 10м2	площадь	расход	кг на 10м2	площадь	расход
Олифа	7,6			6,6
Белила	6,0			6,5
Пигмент	1,5			0,6

8 Задание №7 Упрощенный расчет заработной платы

Заполнить графы таблицы: Фамилия И.О., заработная плата на 10человек. Вычислить величину подоходного налога 13% и величину выплаты.

Оформить таблицу следующим образом:

№ п/п	Фамилия И.О.	Заработная плата, руб	Налог 13%	На руки, руб
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

8 Задание №8 Вычисление процента продаж

Исходные данные: три друга – Андрей, Илья и Сергей – решили во время каникул поработать распространителями газет. Они работали в течение недели. Количество газет, проданных каждым из ребят за каждый день, занести самостоятельно и оформить таблицу в следующем виде:

№

п.п

Имя

Пн

Вт

Ср

Чт

Пт

Сб

Вс

всего

кол

Андрей

Илья

Сегрей

Всего

Вычислить сколько газет было продано всего каждым из ребят за неделю, вместе за каждый день, а также процент проданных газет каждым из друзей за день, за неделю.

Практическая работа №5 «Применение мастера функций для

составления формул»

Цель: приобрести и закрепить практические навыки по применению функций категории Статистические с использованием Мастера функций.

8 Задание1. Создать таблицу, показанную на рисунке.

	A	B	C	D	E	F	G	H
1	Продажа комплектующих к персональным компьютерам
2	*Месяц*	*Центр ЭВМ*	*ЭВМ-* *сервис*	*Дом бизнеса*	*Техноцентр*	*Среднее*	*Максимум*	*Минимум*
3	Январь	18420	10305	25420	15940
4	Февраль	18300	10370	25400	15880
5	Март
6	Апрель
7	Май
8	Июнь
9	Июль
10	Август
11	Сентябрь
12	Октябрь
13	Ноябрь
14	Декабрь
15	Итого:
16	Максимум
17	Минимум

Алгоритм выполнения задания.

1. Записать заголовок и шапочки таблицы (ячейкиА1:Н2).

2. Заполнить боковик таблицы, используя функцию Список.

2.1. В ячейку А3 записать Январь.

2.2. Выделить ячейку А3, подвести указатель мыши к правому нижнему углу ячейки, указатель примет вид тонкого чёрного креста ┼, протащить с нажатой левой кнопкой до ячейки А14 (операция Автозаполнения).

3. Заполнить четыре столбца цифровыми данными:

3.1. Заполнить две строки указанными на рисунке цифрами.

3.2. Выделить диапазон ячеек В3:Е4, выполнить операцию Автозаполнение до строки Итого.

4. Заполнить графу Итого, используя операции Автосумма и

Автозаполнение.

5. Рассчитать Среднее в ячейке F3, используя команду Вставка функции.

5.1. Выделить ячейку F3, щёлкнуть значок f_x в Строке формул или выполнить команду Формула/Вставить функцию.

5.2. В диалоговом окне Мастер функций в поле Категория выбрать Статистические, в поле Выберите функцию найти и выбрать СРЗНАЧ, нажать ОК.

5.3. Появится диалоговое окно функции СРЗНАЧ с автоматически подставленным диапазоном В3:F3 в поле Число1 и подсказками, нажать ОК.

6. Заполнить столбец Среднее по Декабрь, используя операцию Автозаполнение.

7. Рассчитать Максимум в ячейке G3, используя команду Вставка функции.

7.1. Выделить ячейку G3, щёлкнуть значок f_x в Строке формул или выполнить команду Формула/Вставить функцию.

7.2. В диалоговом окне Мастер функций в поле Категория выбрать Статистические, в поле Выберите функцию найти и выбрать МАКС, нажать ОК.

7.3. Появится диалоговое окно функции МАКС с автоматически подставленным диапазоном B3:F3в поле Число1, этот диапазон неверен, для его исправления:

7.3.1. Отодвинуть диалоговое окно, захватив его левой кнопкой мыши за любое место на сером поле так, чтобы была видна строка Январь.

7.3.2. Обвести диапазон В3:Е3 с нажатой левой кнопкой мыши, при этом в поле Число1 появятся нужные адреса (можно также ввести нужные адреса с клавиатуры), нажать ОК.

8. Заполнить столбец Максимум по Декабрь, используя операцию Автозаполнение.

9. Рассчитать Минимум в ячейкеН3, используя команду Вставка функции.

9.1. Выделить ячейку Н3, щёлкнуть значок в Строке формул или выполнить команду Формула/Вставить функцию.

9.2. В диалоговом окне Мастер функций в поле Категория выбрать Статистические, в поле Выберите функцию найти и выбрать МИН, нажать ОК.

9.3. Появится диалоговое окно функции МИН с автоматически подставленным диапазоном B3:G3 в поле Число1, этот диапазон неверен, для его исправления:

9.3.1. Отодвинуть диалоговое окно, захватив его левой кнопкой мыши за любое место на сером поле так, чтобы была видна строка Январь.

9.3.2. Обвести диапазон В3:Е3 с нажатой левой кнопкой мыши, при этом в поле Число1 появятся нужные адреса (можно также ввести нужные адреса с клавиатуры), нажать ОК.

10. Заполнить столбец Минимум по Декабрь, используя операцию Автозаполнение.

11. Рассчитать строку 16 Максимум с помощью мастера функций, исправляя диапазон адресов на В3:В14 и применяя операцию Автозаполнение.

12. Рассчитать строку 17 Минимум с помощью мастера функций, исправляя диапазон адресов на В3:В14 и применяя операцию Автозаполнение.

8 Задание №2 Использование формул ДАТА

A. Рассчитать количество дней до даты в ячейке А2

B. Определить количество месяцев между двумя датами

C. Определить количество рабочих дней между двумя датами

D. Определить дату через шаг1 после даты указанной в ячейке А1

E. Определить дату через шаг 2 после даты указанной в ячейке А2 Даты определить по номеру варианта (номер варианта соответствует номеру компьютера) и ввести в ячейки А1 и А2. Номер задания ввести в диапазон ячеек (С1:С5). Вычисления по заданиям произвести в диапазоне ячеек (D1:D5).

Номер варианта	Дата ячейки А1	Дата ячейки А2	Шаг 1	Шаг 2
1	4.11.2011	16.06.2014	2г 3дня	3г 5мес
2	5.05.2011	24.05.2013	4г 7мес 5дней	1г 17дней
3	16.06.2012	10.09.2013	2мес 3дня	3г 1мес 25дней
4	24.05.2011	3.06.2014	5л 3мес	1г 22дня
5	11.01.2012	12.03.2014	3г 15дней	1г 2мес 27дней
6	15.02.2012	14.07.2013	6л 11мес 15дней	7мес 22дня
7	10.09.2011	4.11.2013	7мес 13 дней	2г 4мес
8	3.06.2012	5.05.2013	3г 5мес	3мес 24дня
9	11.10.2011	11.01.2014	4г 24 дней	2г 6мес
10	5.09.2011	15.02.2014	1г 3мес 15дней	3г 22дня
11	12.03.2012	11.10.2013	9мес 13 дней	3г 2мес 25дней
12	14.07.2011	5.09.2013	3г 5мес	3г 24дня
13	17.08.2012	17.08.2014	1г 14 дней	4г 1мес 15дней

8 Задание №3 Использование формул ВРЕМЯ

A. Вычислить разность во времени с точностью до часов

B. Определить время через шаг 1 после указанного времени в ячейке А1

C. Вычислить разность во времени с точностью до минут

D. Определить время через шаг2 после указанного времени в ячейке А1

E. Определить время через шаг 2 после указанного времени в ячейке А2 Время определить по номеру варианта (номер варианта соответствует номеру компьютера) и ввести в ячейки А1 и А2. Номер задания ввести в диапазон ячеек (С1:С5). Вычисления по заданиям произвести в диапазоне ячеек (D1:D5).

Номер варианта	Время ячейки А1	Время ячейки А2	Шаг 1	Шаг 2
1	02:56:33	01:02:03	1час 2мин	56сек
2	05:05:52	04:05:06	56мин 33сек	34мин 2сек
3	03:02:56	07:08:09	12ч 23мин 3сек	2часа 45сек
4	13:32:12	10:11:12	12мин	3часа 4мин 3сек
5	16:21:15	13:14:15	5час23сек	1час 23мин 6сек
6	01:52:13	16:17:18	24мин 6сек	6мин 23сек
7	17:12:21	19:20:21	2часа 43мин	15мин 8сек
8	04:17:17	22:23:24	4часа 5сек	1час 2мин
9	12:12:12	14:16:18	56сек	56мин 33сек
10	13:13:13	20:22:24	34мин 2сек	12ч 23мин 3сек
11	14:14:14	15:17:19	2часа 45сек	12мин
12	23:24:16	21:23:25	3часа 4мин 3сек	5час23сек
13	20:20:20	02:11:24	1час 23мин 6сек	24мин 6сек

Практическая работа №6 «Работа с логическими функциями»

Цель: научиться вычислениям по сложным формулам с использованием логических функций, познакомиться с некоторыми встроенными функциями

Excel; научиться реализовывать разветвляющиеся алгоритмы

8 Задание №1 Расчет налога, в зависимости от заработной платы

Есть данные о начислении заработной платы за год. Определить налог для каждого работника, если известно, что при зарплате менее 20000 налог составляет 12%, а при зарплате более 20000 налог составляет 20%

№ п/п	ФИ	Заработная плата за год	Налог
1	Иванов С	26000
2	Воронин А	15000
3	Сергеева Л	18000
4	Аркадьева С	14523
5	Свиридов Н	36598
6	Николаев Ю	23647
7	Савинова Н	12356

8 Задание №2 Результат зачисления

Определить количество зачисленных абитуриентов. Если известно количество набранных баллов каждым абитуриентом и проходной балл равен 19. Фамилии И.О., набранные баллы для 10абтуриентов ввести в таблицу самостоятельно и оформить в виде таблицы:

№ п.п	Ф.И.О.	Набранный баллы	Отметка о зачислении


ВСЕГО ЗАЧИСЛЕННЫХ

Отметку о зачислении рассчитать с помощью функции ЕСЛИ, всего зачисленных с помощью функции СЧЕТЕСЛИ

8 Задание №3 Расчет городского налога

Муниципалитет города с кодом 10 ввел налог на лиц старше 18 лет в размере 10%. Определить величину налога для десяти человек. Составить таблицу следующей формы:

А	В	С	D	E	F
		Городской налог

№ п.п.	Фамилия И.О.	Код города	Возраст	Доход	Налог
1
2
3
4
5
6
7
8
9
10

Колонки В, С, D, E заполнить произвольными данными, а колонку F рассчитать с помощью функции «ЕСЛИ» и учета условий задания

8 Задание №4 Расчет заработной платы

Вычислить заработную плату токарей, если известны разряд, количество изготовленных деталей и стоимость одной детали в зависимости от разряда. Фамилия, инициалы, разряды и количество деталей токарей ввести самостоятельно, все остальные ячейки заполнить с помощью применения функций и формул. Результаты оформить в таблицу в следующем виде:

№ п.п.	Фамилия И.О.	разряд	Количество деталей	Заработная плата	Налог (13%)	На руки
1
2
3
4
5
6
7
8
9
10
Стоимость часа
	разряд	Стоимость
	3	4,34
	4	5,90
	5	6,02
	6	7,54

Практическая работа №7 «Вложенные функции»

Цель: научиться вычислениям по сложным формулам с использованием математических функций, познакомиться с некоторыми встроенными функциями Excel

8 Задание №1 Ввод и обработка данных в формате ДАТА - ВРЕМЯ Вычислить количество юбиляров, лиц старше 25лет и моложе 24лет в организации из 15человек на сегодняшний день, а также каждому юбиляру назначить премию 50$. Данные вести самостоятельно и результаты оформить в следующей форме:

Порядковый номер	Фамилия	Дата рождения	Возраст	Юбилей	Премия




Количество юбиляров
Количество лиц моложе 24лет
Количество лиц старше 25лет

ПОРЯДОК ВЫПОЛНЕНИЯ

1. В ячейки с А1 по F1 введите следующие данные («шапку» новой таблицы)

2. Столбец «Порядковый номер» заполнить числами с1 по 15

3. Столбец «Фамилия» заполнить произвольными фамилиями

4. Столбец «Дата рождения» заполнить числами в формате ДД.ММ.ГГГГ 5. В ячейках D2:D16 создайте формулу для вычисления возраста сотрудников, используя функцию СЕГОДНЯ и математическую функцию ЦЕЛОЕ. Чтобы вычислить возраст, достаточно вычесть из текущей даты дату рождения и затем полученный результат разделить на 365 (число дней в году). Деление на 365 необходимо, чтобы перевести результат в годы. Для того, чтобы округлить возраст до целых чисел, рекомендуется использовать в качестве внешней функции функцию ЦЕЛОЕ из категории

«Математические». Другими словами, в ячейке D2 необходимо создать следующую формулу: =ЦЕЛОЕ((СЕГОДНЯ()-C5)/365) Для этого нужно выполнить следующие действия:

Сделать активной ячейку D2

Нажать с клавиатуры знак «=»

Вызвать из списка функций в левой зоне строки формул функцию

ЦЕЛОЕ

Вызвать из списка функций в левой зоне строки формул функцию СЕГОДНЯ

Щелкнуть левой кнопкой мыши в строке формул перед последней закрывающейся скобкой и ввести с клавиатуры знак минус «-», а затем щелкнуть по ячейке С2

Добавить одну закрывающуюся скобку и затем набрать на клавиатуре символы /365 вставить одну открывающуюся скобку после слова

ЦЕЛОЕ

Нажать кнопку ОК в окне функции ЦЕЛОЕ. В ячейке отобразиться результат вычисления по формуле, а в строке формул – расчетная формула

6. Скопируйте формулу из ячейки D2 в ячейки D3:D16

7. В ячейках столбца «Юбилей» должен содержаться либо текст «Юбилей», либо символ «-» в зависимости от того, какое число содержится в соседней ячейке слева. Только если в ячейках D2:D16 содержится число кратное 5, будем считать возраст юбилейным. Чтобы создать формулу, определяющую юбилейный возраст, нужно воспользоваться логической функцией ЕСЛИ. Правильно созданная формула должна иметь следующий вид: =ЕСЛИ(ОСТАТ(D5;5)=0;"юбилей";"-") В логическом выражении приведенной формулы проверяется условие кратности 5числа в ячейке D2.

Для этого используется математическая функция ОСТАТ

8. Скопируйте формулу из ячеек Е2 в ячейки Е3:Е16

9. Заполните ячейки F2:F16 в соответствии со следующим правилом: премия начисляется только тем лицам, у которых юбилей. Сумма премии равна 50$. Для решения этой задачи используйте функцию ЕСЛИ. Правильно созданная формула в ячейке F2 должна иметь следующий вид: =ЕСЛИ(E5="юбилей";50;"-")

10. Примените любой из финансовых долларовых форматов для оформления ячеек F2:F16

11. Вычислите в ячейке D17 сколько лиц – юбиляров в вашей таблице. Используйте функции. СЧЕТЕСЛИ (в качестве диапазона выделить ячейки Е2:Е16, в качестве критерия слово «юбилей»). Правильно созданная формула

имеет вид: =СЧЁТЕСЛИ(E5:E19;"юбилей")

12. Вычислите в ячейке D18 сколько лиц в вашей таблице моложе 24лет. Используйте функции. СЧЕТЕСЛИ (в качестве диапазона выделить колонку «возраст», в качестве критерия <=24). Правильно созданная формула имеет

вид: =СЧЁТЕСЛИ(D5:D19;"<=24")

13. Вычислите в ячейке D19 сколько лиц в вашей таблице старше 25лет. Используйте функции. СЧЕТЕСЛИ (в качестве диапазона выделить колонку «возраст», в качестве критерия >=25). Правильно созданная формула имеет

вид: =СЧЁТЕСЛИ(D5:D19;">=25")

Практическая работа №8 «Сложные вычисления в MS Excel»

Цель: научиться вычислениям по сложным формулам с использованием финансовых функций, познакомиться с некоторыми встроенными функциями Excel.

8 Задание №1 Функция БЗРАСПИС

Ставка банка по депозиту составила 28% годовых на начало года, начисляемых ежемесячно. Определим величину депозита к концу года при следующих условиях: первоначальная сумма вклада составила

350 000рублей, в течении года ожидается снижение ставок на один процент ежемесячно.

8 Задание №2 Функция БС

Определить будущую величину вклада при следующих условиях: размер первоначального взноса – 150 000рублей, срок – 3года, годовая процентная ставка – 10,5%, проценты начисляются раз в год постнумерандно.

8 Задание №3 Функция ВСД

Инвестиции в строительство предприятия 100млн рублей. Планируемая ежегодная прибыль должна составить: в первый год – 30млн рублей, во второй – 50млн рублуй, на третий год – 60 млн рублей. Определить внутреннюю норму доходности проекта.

8 Задание №4 Функция КПЕР

Кредит на сумму 600 000рублей взят под 12% годовых, ежемесячный платеж 20 000рублей. Определить количество периодов, потребующихся для возврата кредита.

8 Задание №5 Функция МВСД

Размер предлагаемой инвестиции – 1млн рублей. Ожидаемые доходы от инвестиции: в первый год – 300 000рублей; во второй год – 400 000рублей; в третий год – 450 000рублей; в четвертый год – 380 000рублей. Размер уровня реинвестиций – 5,5%

8 Задание №6 Функция НОМИНАЛ

Реальная доходность вклада при условии ежемесячной выплаты процентов составила 15,46% годовых. Определить номинальную стоимость банка.

8 Задание №7 Функция ОБЩДОХОД

Кредит на сумму 100 000рублей взят на 3 года под 19% годовых. Погашение производится ежемесячно. Определить, какая сумма будет выплачена в счет основного долга к концу первого года.

8 Задание №8 Функция ОБЩПЛАТ

Кредит на сумму 500 000рублей взят на 5лет под 9,5%годовых. Погашение производится ежемесячно. Определить, какая сумма будет выплачена в счет основного долга к концу третьего года.

8 Задание №9 Функция ОСПЛТ

Кредит на сумму 30 000 рублей взят на год под 23% годовых. Погашение производится ежемесячно. Определить, какая часть ежемесячного платежа идет на погашение основного долга.

8 Задание №10 Функция ПЛТ

Кредит на сумму 2 000 000рублей взят на 15лет под 10% годовых.

Определить сумму ежемесячного платежа.

8 Задание №11 Функция ПРОЦПЛАТ

Банк предлагает 10,8% годовых по вкладу 100 000рублей сроком на 5лет. Выплата процентов происходит в конце срока. Определить сумму процентов, полученных по истечении срока.

8 Задание №12 Функция ПРПЛТ

Кредит на сумму 30 000рублей взят на год под 23% годовых. Погашение производится ежемесячно. Определить, какая часть ежемесячного платежа идет на погашение процентов.

8 Задание №13 Функция ПС

Сумма выплат по кредиту по истечении 5-летнего срока составила 560 000рублей. Определить первоначальную сумму займа, если ставка равна 10% годовых.

8 Задание №14 Функция СТАВКА

Кредит на сумму 80 000рублей взят на 5лет, ежемесячный платеж составил 2000рублей. Определить годовую процентную ставку.

8 Задание №15 Функция ЧИСТВНДОХ

Размер инвестиции составил 1млн рублей. Доходы от инвестиции: через год – 300 000рублей; через 3года – 400 000рублей; через 7лет – 450 000рублей; через 8лет – 380 000рублей. Определить внутреннюю доходность.

8 Задание №16 Функция ЧИСТНЗ

Размер инвестиции – 15млн рублей. Ожидаемая прибыль: через 2года – 5млн рублей, через 4года – 6млн рублей, через 5лет – 10млн рублей. Определить чистую совместимость инвестиции при условии, что процентная ставка за пользование заемными средствами составляет 6%годовых.

8 Задание №17 Функция ЧПС

Размер инвестиции – 20млн рублей. Ожидаемая прибыль: в первый год – 3млн рублей, на второй год – 5млн рублей. Определить чистую современную стоимость инвестиции при условии, что процентная ставка за пользование заемными средствами составляет 10%годовых.

8 Задание №18 Функция ЭФФЕКТ

Ставка банка по депозиту составляет 12% годовых. Срок вклада – 5лет. Определить эффективную ставку при условии ежегодной выплаты процентов.

8 Задание №19 Функция АПЛ

1июня 2007года было приобретено оборудование на сумму 100тыс. рублей. Остаточную стоимость принять нулевой, период амортизации – 5лет. Вычислить величину амортизационных отчислений в первом периоде линейным методом.

8 Задание №20 Функция АСЧ

8 Задание №21 Функция ДДОБ

1июня 2007года было приобретено оборудование на сумму 100тыс. рублей.

Остаточную стоимость принять нулевой, период амортизации – 5лет. Вычислить величину амортизационных отчислений в первом периоде методом двойного уменьшения остатка.

Практическая работа №9 «Построение диаграмм»

Цель: приобрести и закрепить практические навыки по вычислению данных и построению графиков и диаграмм

$ Действия при форматировании диаграммы:

1. _______________________________________________

2. ________________________________________________

3. _________________________________________________

4. _________________________________________________

5. _________________________________________________

6. __________________________________________________

8 Задание №1 Построение графика объема продаж за неделю

Построить в Excel следующую таблицу. Исходные данные: три друга – Андрей, Илья и Сергей – решили во время каникул поработать распространителями газет. Они работали в течение недели. Количество газет, проданных каждым из ребят за каждый день, занесено в электронную таблицу в следующем виде:

	Пн	Вт	Ср	Чт	Пт	Сб	Вс
Андрей	20	25	32	30	23	30	20
Илья	33	28	25	25	22	25	20
Сергей	15	20	22	29	34	35	30
Всего

Вычислить сколько газет было продано всего каждым из ребят за день, за неделю. Построить график, отражающий объем продаж газет всех ребят за неделю.

8 Задание №2 Построение диаграммы продаж газет каждым агентом за неделю

	Пн	Вт	Ср	Чт	Пт	Сб	Вс	Всего	%продаж
Андрей	20	25	32	30	23	30	20
Илья	33	28	25	25	22	25	20
Сергей	15	20	22	29	34	35	30
Всего

Вычислить сколько газет было продано всего каждым из ребят за день, за неделю и общее количество газет. Вычислить %продаж каждым из ребят за неделю и за каждый день. Построить множественную диаграмму, отражающую продажу газет всех ребят за неделю.

8 Задание №3 Построение графика по заданной функции

Построить график функции у = ax + b, если известны коэффициенты a и b, интервал и шаг известны. Данные в таблице. Номер варианта соответствует номеру компьютера

№ варианта	а	b	интервал	шаг
1.	-3	2	(-2;2)	0,1
2.	3	0	[-3;1]	0,2
3.	4	-11	(-2;2)	0,5
4.	5	-10	[-3;1]	0,1
5.	-1	-9	(-2;2)	0,2
6.	-2	-5	[-3;1]	0,5
7.	-3	1	(-2;2)	0,1
8.	-4	2	[-3;1]	0,2
9.	1	3	(-2;2)	0,5
10.	2	5	[-3;1]	0,1
11.	3	6	(-2;2)	0,2
12.	4	-8	[-3;1]	0,5
13.	5	-6	(-2;2)	0,1

Практическая работа №10 «Составление интерактивного теста»

Цель: Приобрести и закрепить практические навыки по применению функций и созданию гиперссылок

& Инструкционная карта №1 Создание теста используя гиперссылки

Порядок выполнения

Шаг 1. Подготовка шаблона

1. Открыть MS Excel (Пуск à Все программы à Microsoft Office à

Microsoft Office Excel 2007)

2. Добавить количество листов нажав на кнопку (количество листов должно быть на один больше чем вопросов)

3. Переименовать листы по порядку В1…. В10, ОЦЕНКА (нажать ПКМ и ввести необходимое имя)

4. На листе В1 выделить ячейки А1:J1 и объединить нажав на кнопку на вкладке «Главная», залить ячейку цветом нажав на кнопку и ввести:

ВОПРОС 1

5. Пропустить строку 2

6. Выделить ячейки А3:J3 и объединить

(см. пункт 4), нажать кнопку на вкладке «Главная»

для того чтобы текст в ячейке был написан в несколько строк. Введите текст вопроса. Если текст не помещается в ячейку расширьте её переведя курсор мыши между номерами строк и зажав ЛКМ увеличьте ширину строки

7. Строку 4 пропустить

8. В ячейках А5:А8 вводим номера ответов 1..4 соответственно

9. Выделить ячейки В5:J5, объединить, нажать на кнопку «Перенос текста» и ввести текст первого варианта ответа (при необходимости увеличить ширину строки)

10. Те же действия провести напротив 2,3,4 варианта ответов

11. Залить ячейки с вопросом и вариантами ответов по желанию

12. Пропустить строку 9 и 10

13. Выделить ячейки D11:Е11, объединить, нажать кнопку перенос текста и ввести: Правильный ответ

14. Выделить ячейку F11, залить ее цветом, установить границы ячейки нажав на кнопку и выбрать «Все границы» 15. Выделить ячейку Е13 и ввести слово ДАЛЕЕ

16. Выделить строки 1-16 и скопировать данный фрагмент

17. Перейти на лист В2 и нажать кнопку вставить или сочетание клавиш CTRL+V. Перейти на лист В3 и выполнить тоже действие и так далее до последнего вопроса (Главное: при вставке скопированного фрагмента необходимо чтобы активной была ячейка A1 иначе высвечивается ошибка)

Шаг 2. Внесение данных на каждом листе

Перемещаясь по ярлыкам листов, введите данные вопросов и вариантов ответа для каждого вопроса.

В1 – вопрос1

В2 – вопрос 2 и т.д

В10 – вопрос 10

Шаг 3. Создание гиперссылки на кнопку ДАЛЕЕ

1. Перейти на лист В1, выделить ячейку со словом ДАЛЕЕ и выполнить следующее действие Вставка à Гиперссылка à Место в документе.

2. В появившемся

диалоговом окне в строке Введите адрес ячейки указать адрес ячейки в которую необходимо ввести номер

правильного ответа (у нас ячейка F11) (т.е. вместо А1 вводим F11). В окошке «Или выберите место в документе» выбираем следующий лист, т.е. В2 и нажимаем ОК.

3. Проверяем правильность гиперссылки нажимая кнопку ДАЛЕЕ которая должна перейти на следующий вопрос и на ячейку в которой необходимо ввести правильный ответ

4. Выполняем аналогичное действие на всех листах с вопросами включая последний, где в качестве следующего листа будет лист ОЦЕНКА Шаг 4. Оформление листа ОЦЕНКА

3. В ячейке Е9 ввести: Баллы

порядку

меню функцию ЕСЛИ 6. В диалоговом

строке Лог_выражение выбрать лист соответствующий номеру вопроса и ячейку с правильным ответом (у нас F11), нажать = и Значение_если_истина и ввести 1, перейти на строку Значение_если_ложь и ввести 0, нажать кнопку ОК

7. В ячейках В2…В10 выполнить аналогичное действие (в результате напротив каждого вопроса появиться 0, так как мы на листах не вводили ответы)

8. Выделить ячейку F9 и ввести формулу выполнив следующие действия: нажать = и из списка выбрать функцию СУММ и в появившемся диалоговом окне выделить все ячейки с ответами (у нас В1:В10)

9. Определить критерии оценки. Например:

9-10 «5» 7-8 «4» 5-6 «3» 0-4 «2»

10. Выделить ячейку F11 и ввести формулу выполнив следующие действия: нажать = и из списка выбрать функцию ЕСЛИ, в появившемся диалоговом окне в строке Лог_выражение выбрать ячейку с количеством баллов (у нас F9) указать >= и ввести нижний предел оценки «5» (у нас это 9), перейти на строку Значение_если_истина и ввести 5, перейти на строку Значение_если_ложь и вызвать еще раз функцию ЕСЛИ из списка.

11. В появившемся диалоговом окне в строке Лог_выражение выбрать ячейку с количеством баллов (у нас F9) указать >= и ввести нижний предел оценки «4» (у нас это 7), перейти на строку Значение_если_истина и ввести 4, перейти на строку Значение_если_ложь и вызвать еще раз функцию ЕСЛИ из списка.

12. В появившемся диалоговом окне в строке Лог_выражение выбрать ячейку с количеством баллов (у нас F9) указать >= и ввести нижний предел оценки «3» (у нас это 5), перейти на строку Значение_если_истина и ввести 3, перейти на строку Значение_если_ложь и ввести 2, нажать кнопку ОК

13. Переходим на лист В1 выделяем ячейку где необходимо ввести номер правильного ответа и сохраняем документ.

Шаг 5. Проверка теста на ошибки

1. Открываем тест вводим вариант ответа и нажимаем ДАЛЕЕ и так до конца теста. В результате должны получить максимальное количество правильных ответов и оценку «5». Если получилось, то закрываем тест без сохранения и можем использовать.

2. Если не достигли максимального количества баллов, то запоминаем те номера вопросов где в таблице ответов Вы получили 0, закрываем тест без сохранения. Повторно открываем тест исправляем ошибки, т.е. проверяем правильность ввода варианта правильного ответа, исправляем ошибки, переходим на лист В1, выделяем ячейку с вариантом правильного ответа (у нас F11) и закрываем документ сохранив изменения.

3. Проводим повторную проверку

& Инструкционная карта №2 Создание теста с помощью стандартных команд и функций MS Excel

MS Excel содержит набор стандартных функций. Функции – это заранее определенные формулы, которые выполняют вычисления по заданным величинам в указанном порядке. Пользователю необходимо лишь задать имя функции (например, СУММ, ЕСЛИ) и аргументы функции – адреса тех ячеек, которые подлежат обработке (сложению, проверке).

Создадим тест, состоящий из 5 вопросов.

Для этого:

1. В ячейки В1, В3, В5, В7, В9 набираем текст вопросов.

2. Набираем варианты ответов

В столбец К набираем варианты ответов на вопрос №1 (ячейка В1)

В столбец L набираем варианты ответов на вопрос №2 (ячейка В3)

В столбец М – на вопрос №3 (ячейка В5)

В столбец N – на вопрос №4 (ячейка В7)

В столбец О – на вопрос №5 (ячейка В9)

3. Оформляем ячейки с ответами В ячейку В2 вносим проверку данных

Курсор устанавливаем в ячейку В2

Запускаем команду Проверка данных группы Работа с данными вкладки Данные

На вкладке Параметры (рис. 1) заполняем тип данных Список и источник данных – указываем столбец с вариантами ответов на вкладке Сообщение для ввода записываем сообщение

«Выберите ответ!» Щелкаем ОК

Аналогичные действия проводим с ячейками В4, В6, В8, В10.

Рис.1 Окно Проверки значений

4. Указываем правильные ответы

В ячейку К5 вводим формулу =ЕСЛИ(В2=К1;1;0) – в данном случае в ячейке К1 – правильный вариант ответа

аналогично заполняем ячейки L5, M5, N5, O5.

5. В ячейку Р5 вводим формулу считающую общий балл =СУММ(К5:О5) 6. В ячейку В11 вводим формулу, определяющую оценку выполненному тесту: =ЕСЛИ(Р5=5;5;ЕСЛИ(Р5=4;4;ЕСЛИ(Р5=3;3;2)))

Варианты ответов (столбцы К, L, M, N, O) лучше скрыть (вкладка Главная

группа Ячейки команда Формат-Видимость-Скрыть столбец), либо выбрать белый цвет шрифта.

& Инструкционная карта №3 Создание теста с помощью макросов Создадим тест, состоящий из 5 вопросов с помощью макросов.

Для этого:

1. Создадим 7 листов, используя команду Вставить лист из группы Ячейки вкладки Главная.

2. Заполняем листы надписями (вкладка Вставка, группа Текст, команда Надпись):

на первом листе одна фигура «Начать тест»; на последнем – две фигуры «Ваша оценка» и «Вернуться в начало теста?»;

на остальных – одна «содержание вопроса» и фигуры с вариантами ответов (сколько их будет – зависит от Вас).

3. Теперь необходимо создать макросы, заставляющие работать Ваш тест.

3.1. Макрос, запускающий тест

курсор устанавливаем на лист 1 в ячейку А1 запускаем команду Запись макроса в группе Код вкладки Разработчик

В окне задаем имя макросу Начало_теста в списке «Сохранить в» выбираем «Эта книга» щелкаем ОК (окно закрывается и идет запись макроса) щелкаем по фигуре «Начать тест», затем по ярлыку второго

листа

запускаем команду Остановить запись в группе Код щелкаем по фигуре правой кнопки мыши выбираем Назначить макрос в открывшемся окне выбираем Начало_теста щелкаем ОК

Для проверки можно, предварительно поставив курсор в любую ячейку (допустим А1), щелкнуть по фигуре. В результате Вы окажетесь на втором листе книги.

3.2. Макрос, работающий с правильным вариантом ответа курсор устанавливаем на лист 2 в ячейку А1

запускаем команду Запись макроса в группе Код вкладки Разработчик

В окне задаем имя макросу Лист_2_правильно в списке «Сохранить в» выбираем «Эта книга» щелкаем ОК (окно закрывается и идет запись макроса)

щелкаем по фигуре «правильный ответ», затем по ячейке А1, записываем в нее цифру 1, затем по ярлыку третьего листа

запускаем команду Остановить запись в группе Код щелкаем по фигуре правой кнопки мыши выбираем Назначить макрос

в открывшемся окне выбираем Лист_2_правильно щелкаем ОК

3.3. Макрос, работающий с неправильным вариантом ответа курсор устанавливаем на лист 2 в ячейку А1

запускаем команду Запись макроса в группе Код вкладки Разработчик

В окне задаем имя макросу Лист_2_неправильно в списке «Сохранить в» выбираем «Эта книга» щелкаем ОК (окно закрывается и идет запись макроса) щелкаем по фигуре «неправильный ответ», затем по ярлыку третьего

листа

запускаем команду Остановить запись в группе Код щелкаем по фигуре с неправильным ответом правой кнопки

мыши

выбираем Назначить макрос в открывшемся окне выбираем Лист_2_неправильно щелкаем ОК

последние 4 пункта повторяем с остальными фигурами с вариантами неправильного ответа

3.4. Далее проделываем операции пунктов 3.2 и 3.3 на остальных листах с вопросами, изменяя номера листов в названиях макросов

(Лист_3_правильно, Лист_4_правильно и т.п.)

3.5. Макрос, работающий с возвратом к началу теста курсор устанавливаем на лист 7 в ячейку А1

запускаем команду Запись макроса в группе Код вкладки Разработчик

В окне задаем имя макросу вернуться_к_началу_теста в списке «Сохранить в» выбираем «Эта книга» щелкаем ОК (окно закрывается и идет запись макроса)

щелкаем по фигуре «вернуться к началу теста?», затем на лист 6 по ячейке А1, на клавишу Del, затем на лист 5 по ячейке А1, на клавишу Del, затем на лист 4 по ячейке А1, на клавишу Del, затем на лист 3 по ячейке А1, на клавишу Del, затем на лист 2 по ячейке А1, на клавишу Del, затем по ярлыку первого листа

запускаем команду Остановить запись в группе Код щелкаем по фигуре «вернуться к началу теста?» (на 7 листе книги)

правой кнопки мыши

выбираем Назначить макрос в открывшемся окне выбираем вернуться_к_началу_теста щелкаем ОК

3.6. Теперь нужно посчитать оценку.

Для этого

в любую ячейку листа 7 (например, А1) вставляем формулу

=СУММ(Лист2!A1;Лист3!A1;Лист4!A1; Лист5!A1; Лист6!A1) теперь в ячейку, располагающуюся рядом с фигурой «Ваша

оценка» вставляем формулу подсчета оценки

=ЕСЛИ(А1=5;5;ЕСЛИ(А1=4;4; ЕСЛИ(А1=3;3;2)))

3.7. Для удобства контроля и невозможности со стороны учащихся изменить тест, воспользуйтесь командой Параметры Excel основного меню программы. На вкладке Дополнительно в группе Показать параметры для следующей книги уберите флажок Показывать ярлычки листов.

8 Задание 1. По предложенной теме (уточнить у преподавателя) создать интерактивный тест на 20 вопросов.

КОМИТЕТ ОБРАЗОВАНИЯ И НАУКИ ВОЛГОГРАДСКОЙ

Содержание