ПРАКТИЧЕСКОЕ ЗАНЯТИЕ N 5

ТЕМА: Решение практических задач с использованием табличного редактора. Математические расчеты в таблице.

ЦЕЛЬ: Уметь производить математические расчеты с использованием табличного процессора.

ВРЕМЯ НА ВЫПОЛНЕНИЕ: 2 часа.

МЕСТО ВЫПОЛНЕНИЯ РАБОТЫ  -  лаборатория «Информационных технологий»

ДИДАКТИЧЕСКОЕ И МЕТОДИЧЕСКОЕ ОБЕСПЕЧЕНИЕ: Персональный компьютер. Электронные методические указания.

 

I. Внеурочная подготовка

1.1. Подготовить титульный лист отчета и разделы основной части, включающие тему, цель работы.

1.2. Повторить способы создания формул в электронных таблицах.

 

II. Работа в кабинете

 

Работа с формулами в Excel. Ссылки в таблице Excel.

Формулы — это выражения, с помощью которых можно выполнять вычисления, манипулировать содержимым других ячеек, проверять условия и пр.

Формула всегда начинается со знака равенства (=).

В Excel обычно задан режим показа результатов вычислений поформулам, но можно задать и режим показа формул (вкладка Формулы -Показать формулы в группе Зависимости формул).

Формулы в системе Excel могут состоять из постоянных значений(числа, текст, даты/время), символов математических операций (+, -, *, /,Л), круглых скобок для изменения порядка действий (система знает прио-ритет вычислений в математических формулах, принятый в математике),функций разного типа, ссылки на ячейки и имена ячеек.

Часто при вычислениях одинаковую операцию нужно выполнить с рядом данных. Простейший пример показан на рисунке:

Как видно из рисунка, одинаковую операцию сложения нужно выполнить с парами чисел колонок А и В в строках 1-5, затем с парами чисел колонок В и С.

Для организации подобных вычислений используется одна из глав- особенностей электронных таблиц - изменение относительной адресации при копировании формул. Формула в колонке С была написана один
раз в строке 1, затем за маркер копирования/автозаполнения в правом

нижнем углу скопирована в остальные строки путем расширения выборана строки 2-5, затем получившаяся выделенная колонка была скопирована в столбец D

 

Как копируем?

Выделяем ячейку, содержащую формулу (С1).

Ставим курсор на ее правый нижний угол. Видим, что в углу появился крестик.

Нажимаем на него левой клавишей мыши и, не отпуская ее, протягиваем до ячейки.

Отпускаем клавишу

 

Как видно из примера, при копировании формул ссылки изменяются:

при копировании в низ формулы строки 1 =А1+В1, для строки 2 формула будет иметь вид =А2+В2 и т. д., при копировании вправо - формула для столбца С - =А1+В1, для столбца D - =В1+С1. Такие ссылки (A1, B1, С1) называются относительными.

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

1) $А$1 при копировании не будут изменяться обозначение столбца  строки;

2) $А1 при копировании не будет изменяться обозначение столбца;

3) А$1 при копировании не будет изменяться обозначение строки.

Пример использования абсолютной и относительной адресации показан на рисунке :

 

Задание 1.

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

1. Запустите редактор электронных таблиц Microsoft Excel и создайте
новую на первом листе электронную книгу.

2. Создайте таблицу расчета заработной платы по образцу:

Введите исходные данные — Табельный номер, ФИО и Оклад, % Премии =27%, % Удержания = 13 %.

При расчете Премии используется формула

Премия = Оклад * % Премии,

 в ячейке D5 наберите формулу = $D$4 * С5 (ячейка D4 используется в виде абсолютной адресации) и скопируйте автозаполнением.

Рекомендации. Для удобства работы и формирования навыков работы
с абсолютным видом адресации рекомендуется при оформлении констант
окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда
при вводе формул в расчетную окрашенная ячейка (т.е. ячейка с константой) будет вам напоминанием, что следует установить абсолютную адресацию (набором символов $ с клавиатуры или нажатием клавиши [F4]).

Формула для расчета «Всего начислено»:

Всего начислено = Оклад + Премия.

При расчете Удержания используется формула

Удержание = Всего начислено * % Удержания,

 для этого в ячейке F5 наберите формулу = $F$4 * Е5 и скопируйте автозаполнением.

Формула для расчета столбца «К выдаче»:

К выдаче = Всего начислено — Удержания.

3. Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы поданным колонки «К выдаче» (Формулы —Библиотека функций — Статистические функции).

4. Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку с названием листа и наберите новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого щелчком правой кнопкой мыши

Краткая справка. Каждая рабочая книга Excel может содержать до 255 рабочих листов. Это позволяет, используя несколько листов, создавать понятные и четко структурированные документы, вместо того, чтобы хранить большие последовательные наборы данных на одном листе.

5. Скопируйте содержимое листа «Зарплата октябрь» на новый лист с помощью команды Переместить/Скопировать контекстного меню ярлычка с названием листа. Не забудьте для копирования поставить галочку в окошке Создавать копию

Краткая справка. Перемешать и копировать листы можно, перетаскивая их корешки (для копирования удерживайте нажатой клавишу [Ctrl]).

6. Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените значение премии на 32%. Убедитесь, что программа произвела пересчет формул.

7. Между колонками «Премия» и «Всего начислено» вставьте новую
колонку «Доплата» (Вставка/Столбец) и рассчитайте значение доплаты по  формуле Доплата = Оклад * % Доплаты.

8. Скопируйте формулу автозаполнением.

 Значение доплаты примите равным 8 %.

В результате вы должны получить вот такую таблицу:

8. Измените формулу для расчета значений колонки «Всего начислено»:

Всего начислено = Оклад + Премия + Доплата.

10. Проведите сортировку по фамилиям в алфавитном порядке по возрастанию

Ø  выделите фрагмент таблицы с 5 по 18 строки);

Ø  выполните команду Данные/Сортировка, Сортировать по — Столбец В

Таблицу сохранить в своей папке на диске с именем Расчет зарплаты.

 

 

Построение и настрока диаграмм в Microsoft Excel 2007.

 

В качестве примера, рассмотрим построение круговой диаграммы.

Для начала откройте программу Excel 2007. И создайте таблицу:

Таблица исходных данных

Все построение диаграммы сводится к следующему:

1.   Чтобы построить круговую диаграмму по данным о купленных и проданных ягодах, необходимо выделить необходимый диапазон значений (столбец с наименование ягод и столбец с купленным количеством этих ягод), смотреть ниже. Также стоит «захватить» шапку данной таблицы.

2.   Установите курсор на ячейку, где хотите расположить график

3.   Следующим шаг – переход во вкладку Вставка | Диаграммы

4.   Из раздела диаграмм выбираем круговую.

5.   Далее выбираем необходимый тип диаграммы, в данном случае мы выберем объемную круговую, расположенную внизу справа.

Добавление диаграммы

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

Круговая объемная диаграмма с разделением долей

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

Для того чтобы на диаграмме отобразить значения таблицы необходимы кликнуть правой кнопкой мыши по самому кругу, после появления меню необходимо выбрать следующий пункт: «Добавить подписи данных».

Добавление подписей данных

 

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

Чтобы выделить два, не расположенных рядом друг с другом столбца, необходимо зажать кнопку «Ctrl» на клавиатуре и выделить по очереди две области.

Принцип построения не отличается ничем от предыдущего, необходимо проследовать во вкладку Вставка/Диаграмма/Круговая диаграмма, следующим шагом, для разнообразия, можно выбрать другой тип диаграммы – «объемная диаграмма».

Если необходимо, можно также добавить подписи данных.

Оформление графика можно изменить, выделяя нужную его часть, кликая правой клавишей мыши и выбирая «Формат области диаграммы», либо изменить тип диаграммы.

Контекстное меню областей графика

Параметры оформления графика, такие как название, подписи осей, сетка и т.п, можно настроить, если мышкой выделить сам график и в главном меню переключиться на пункт "Макет". Например чтобы включить подпись оси по вертикали, нужно выбрать "Макет"->"Названия осей"->"Название основной вертикальной" и один из появившихся вариантов расположения подписи, например "Вертикальное название".

 

Круговая объемная диаграмма

Также можно менять название диаграммы, по умолчанию, после построения диаграммы, её название выбирается автоматически по наименованию столбца. Для того чтобы поменять её название необходимо два раза щелкнуть по имеющейся надписи, в данном случае по надписи «Продано. Кг», правой кнопкой мыши.

Новое название диаграммы

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

Смена типа диаграммы

После нажатия пункта «Формат области построения...», можно задавать дополнительные визуальные параметры таблицы: заливка, цвет границы, стили границ, тень, формат объемной фигуры, поворот объемной фигуры.

Формат области диаграммы

Можно задавать тень, объемность рамки, изменять положение отдельных секторов диаграммы и т.д. Для примера попробуем изменить расположение нашей диаграммы. Для этого необходимо выбрать в данном меню пункт «Поворот объемной фигуры».

Поворот объемной фигуры

Как показано на рисунке, с изменением координат x, y и перспективы можно изменять положение диаграммы.

Также после создания диаграммы, если необходимо можно поменять её тип, для этого необходимо нажать по диаграмме правой кнопкой мыши и выбрать пункт «Изменить тип диаграммы…». После нажатия появится следующее меню:

Изменение типа диаграммы

 

Задание 2.

По заданию преподавателя, постройте диаграмму для таблицы созданной в первом задании.

 

Задание № 4. Функции табличного процессора MS Excel.

1.1.Создайте таблицу, приведенную на рисунке:

http://5fan.ru/files/8/5fan_ru_41893_3faf9cc7f7287722b43de1af5e8517e8.html_files/rId17.png

1.2. Введите в столбец B функции, указанные в столбце А (столбец А заполнять не надо) и сравните полученные результаты с данными, приведенными в столбце В на рисунке.

 

III. СОДЕРЖАНИЕ ОТЧЕТА

3.1. Номер практического занятия, тема и цель работы.

3.2. Макет созданных электронных таблиц с формулами и результатами расчета.

3.2. Протокол выполненных действий.