 |
Относительные и абсолютные адреса
|
|
|
|
В. М. Паклина, Е. М. Паклина
Подготовка документов средствами Microsoft Office 2007
Методические указания к лабораторным работам
Учебное электронное текстовое издание
Подготовлено кафедрой «Интеллектуальные информационные технологии»
Научный редактор: доц., канд. техн. наук И.Н. Обабков
Методические указания по дисциплине «Информатика» предназначены для студентов всех форм обучения всех специальностей.
© ГОУ ВПО УГТУ−УПИ
Екатеринбург
Содержание
Введение. 3
Лабораторная работа № 1 Формулы, функции и диаграммы в процессоре Microsoft Office Excel 2007. 9
Лабораторная работа №2 Адресация ячеек. 16
Лабораторная работа №3. 20
Индивидуальные задания. 24
Лабораторная работа № 4 Построение графиков функций. 28
Индивидуальные задания. 31
Лабораторная работа № 5 Решение систем линейных уравнений. 33
Индивидуальные задания. 39
Введение
В предлагаемом пособии рассматриваются два приложения – текстовый процессор MS Word 2007 и табличный процессор MS Excel 2007.
Табличный процессорExcel используется для организации табличных данных и управления ими.
Рабочее поле Excel представляет собой таблицу, состоящую из строк и столбцов: строки обозначаются числами 1, 2, 3..., а столбцы - латинскими буквами A, B, C..., AA, AB и т.д. Пересечение строки и столбца электронной таблицы образует ячейку таблицы, имеющую свой уникальный адрес.
Адрес ячейки складывается из буквы столбца и номера строки, на пересечении которых находится данная ячейка, например: А7, B12, AB3. Активная ячейка выделена контурным прямоугольником. Ввод данных в активную ячейку можно производить либо в самой ячейке, либо в строке формул, находящейся над заголовками столбцов.
|
|
|
Диапазон ячеек - это группа смежных ячеек. Диапазон ячеек определяют указанием адреса первой и последней ячейки, например, А7:B10 означает ссылку на прямоугольный блок, состоящий из восьми ячеек.
Для выделения одного столбца щелкните по букве заголовка столбца
Для выделения одной строки щелкните по цифре заголовка строки
Для выделения нескольких смежных столбцов или строк:
1. Щелкните по первому столбцу или строке группы.
2. Удерживайте нажатой клавишу Shift.
3. Щелкните по последнему столбцу или строке группы.
Для выделения нескольких несмежных столбцов или строк:
1. Щелкните по первому столбцу или строке группы.
2. Удерживайте нажатой клавишу Control.
3. Щелкайте по всем остальным столбцам или строкам, удерживая нажатой клавишу Control.
Режимы курсора
При наведении курсора на правый нижний край ячейки (маленький черный квадратик) он может принимать разный вид.
Когда курсор имеет вид 
это значит, что он работает в режиме выделения ячеек:
Если курсор имеет вид это означает, что курсор работает в режиме автозаполнения.
В режиме автозаполнения, например, можно заполнить ячейки числами в некотором диапазоне, для этого в первую ячейку диапазона пишется число, во вторую второе число (тем самым задается шаг изменения числа), выделяются обе ячейки (курсор в режиме выделения) и далее курсором (в режиме заполнения) заполняется числами нужный диапазон.
Таким образом заполняются формулы для того, чтобы одну и ту же формулу не писать несколько раз:
Конечный результат будет выглядеть так
Несколько листов образуют книгу. Книга - это единый документ, созданный пользователем и хранящий таблицы, диаграммы, макросы и пр. Книги хранятся в отдельном файле и имеют временные имена: Книга1, Книга2 и т.д. Пользователь должен присвоить книге свое оригинальное имя. Количество листов в книге формируется пользователем.
|
|
|
В дальнейшей работе исправляйте сообщения об ошибках в соответствии с нижеследующей справкой:
#ССЫЛКА! – формула неправильно ссылается на ячейку;
#ДЕЛ/0! - деление на ноль;
#ЧИСЛО! - нарушено правило задания данных;
#ИМЯ! - нарушен синтаксис имени функции;
#Н/Д! - нет данных для вычисления.
ОТНОСИТЕЛЬНЫЕ И АБСОЛЮТНЫЕ АДРЕСА
Относительный адрес изменяется (настраивается) при копировании формулы, содержащей этот адрес, в другие ячейки. Абсолютный адрес не изменяется при копировании формулы, содержащей данный адрес. При ссылке на ячейку с абсолютным адресом используется знак $. Например, если адрес в формуле содержит ссылку $D$18, то это означает, что при копировании формулы в адресе не меняется ни имя столбца, ни номер строки. Если адрес в формуле имеет вид D$18, то при копировании не меняется номер строки, если $D18 – не меняется строка.
ФОРМАТИРОВАНИЕ ТАБЛИЦ
Под форматированием таблицы понимается задание способа представления данных в таблицах. Сервисные функции Excel включают выбор формата числа, выбор шрифта, выравнивание содержимого ячеек,установка ширины столбцов, высоты строк и т.д.
При вводе данных в таблицу содержимое ячеек автоматически выравнивается: текст «прижимается» к левому краю, а числа - к правому. Производить вычисления можно только над числами!
Изменение выравнивания можно выполнить с помощью команды Формат / Ячейки / Выравнивание.
Формат числа, назначаемый ячейке, определяет способ просмотра числа в ячейке. По умолчанию при вводе числа в ячейку ей назначается формат Общий. Этот формат отображает в точности то, что введено в ячейку.
· Формат Числовой позволяет выводить числовые значения в виде целых чисел, чисел с фиксированной запятой, а также выделять отрицательные значения.
· Формат Денежный изображает число со знаком валюты и разделением группы разрядов числа пробелом.
· Финансовый формат отличается от денежного вынесением знака минуса в крайнюю левую позицию ячейки.
Для изменения формата ячейки выполняют последовательность команд Формат / Ячейки / Число. В поле списка Числовые форматы выбирается подходящий формат.
При выборе конкретного формата может оказаться, что ширины столбца не хватает для отображения числа в ячейке. Признаком такой ситуации является сообщение вида #####. Для устранения ошибки необходимо увеличить ширину столбца, в котором находится неправильно отображаемая ячейка.
|
|
|
Для изменения ширины столбца указатель мыши позиционируется на границе двух столбцов в области заголовков. При этом он принимает вид двунаправленной стрелки, с помощью которой следует «зацепить» границу столбца и перетащить ее в нужное место.
Установить оптимальную ширину столбца можно с помощью команды Формат / Столбец / Автоподбор ширины.
Высота строк устанавливается по аналогичной схеме.
В пакете предусмотрена возможность оформления таблиц посредством предопределенных форматов. Для этого существует команда Формат / Автоформат.
Лабораторная работа № 1
Формулы, функции и диаграммы в процессоре
Microsoft Office Excel 2007
1. Откройте табличный процессор Microsoft Excel 2007 и создайте рабочую книгу с именем Лабораторная работа №1.
2. Необходимо создать таблицу расчета заработной платы сотрудников предприятия.
3. Для упрощения ввода данных в таблицу создайте раскрывающийся список (рис. 22), содержащий ФИО сотрудников предприятия.
Рис. 22. Раскрывающийся список
4. Вставьте еще один лист в рабочую книгу Excel, используя ярлычок 
в строке Ярлычок листа.
5. На новом листе создайте список сотрудников (рис. 23).
Рис. 23. Список сотрудников предприятия
6. Для сортировки ФИО по алфавиту выполните команду: вкладка ленты Данные ► группа Сортировка и фильтр ► кнопка 
.
7. Выделите диапазон ячеек А1:А10 и щелкните поле Имя у левого края строки формул. Введите имя для ячеек, например Сотрудники 
. Нажмите клавишу Enter.
8. Чтобы запретить другим пользователям просмотр и изменение полученного списка, защитите и скройте лист, на котором он находится.
9. Правой кнопкой мыши щелкните по ярлычку листа. В контекстном меню выберите команду 
.
10. В диалоговом окне Защита листа (рис. 24) введите пароль для отключения защиты листа. В разделе Разрешить всем пользователям этого листа снимите флажки со всех элементов. Нажмите кнопку ОК.
|
|
|
Рис. 24. Диалоговое окно Защита листа
11. В диалоговом окне Подтверждение пароля введите пароль еще раз.
12. Правой кнопкой мыши щелкните по ярлычку листа и в контекстном меню выберите команду Скрыть.
13. Перейдите на Лист 1 и создайте таблицу Расчет заработной платы
(рис. 25). Столбец ФИО заполните, используя раскрывающийся список.
Рис. 25. Структура таблицы
14. Выделите диапазон ячеек, в который требуется поместить раскрывающийся список.
15. На вкладке Данные в группе Работа с данными выберите команду Проверка данных.
16. В диалоговом окне Проверка данных укажите тип и источник данных (рис. 26).
17. Откройте вкладку Сообщение для ввода (рис. 27). Заполните пустые поля.
Рис. 26. Диалоговое окно Проверка данных
Рис. 27. Сообщение при вводе данных
18. Перейдите на вкладку Сообщение об ошибке (рис. 28). Заполните поля Вид, Заголовок и Сообщение.
Рис. 28. Сообщение при ошибке ввода данных
19. Для заголовков таблицы установите перенос текста (кнопка 
, расположенная на панели инструментов Выравнивание вкладки ленты Главная).
20. Закрепите два первых столбца и строку заголовков таблицы. Для этого выделите диапазон ячеек C5:I20 и выполните команду: вкладка ленты Вид ► группа Окно ► кнопка 
.
21. Столбец Оклад заполните произвольными данными и установите денежный формат ячеек, используя команду:
вкладка ленты Главная ► панель инструментов Число ► в раскрывающемся списке форматов выберите Денежный формат.
22. Составим формулу для вычисления премии, которая составляет 20% от оклада. Любая формула начинается со знака =, поэтому переходим в ячейку F5 ивводим формулу = E5*20% (или = Е5*0,2).
23. С помощью маркера автозаполнения (черный крестик возле правого нижнего угла выделенной ячейки) скопируйте формулу в область
F6: F11.
24. Между столбцами Премия и Подоходный налог вставьте столбец Итогоначислено, в котором посчитайте сумму Оклад + Премия.
25. Заполните остальные столбцы таблицы, учитывая, что подоходный налог составляет 13% от начисленной суммы.
26. Посчитайте сумму к выдаче в долларах, для этого задайте текущий курс доллара, например 32, и в ячейку J5 введите формулу: = I5/$C$14. Знак $ используется в формуле для того, чтобы при копировании с помощью маркера автозаполнения, адресация ячейки не изменялась.
27. Для ячеек, в которых содержатся денежные данные, установите соответствующий формат.
28. Используя функцию СУММ, посчитайте общую сумму подоходного налога. Для этого:
· установите курсор в ячейку Н12;
· поставьте знак =;
· в строке формул нажмите кнопку 
;
|
|
|
· в появившемся диалоговом окне мастера функций (рис. 29) выберите категорию Математические, функцию СУММ;
· в качестве аргумента функции СУММ выделите диапазон суммирования Н5:Н11;
· нажмите кнопку ОК.
29. Аналогичным образом посчитайте общую сумму к выдаче в долларах и общую сумму к выдаче в рублях.
Рис. 29. Мастер функций
30. Найдите среднюю (СРЗНАЧ), минимальную (MИН) и максимальную (MAКС) заработные платы.
31. Используя условное форматирование, обозначьте красным цветом Суммы к выдаче, менее 5 500 руб. Выполните команду: вкладка ленты Главная ► группа Стили ► раскрывающийся список Условное форматирование ► Правила выделения ячеек.
32. Постройте диаграмму Заработная плата сотрудников предприятия
(рис. 30). Выделите одновременно столбцы Ф.И.О. и Сумма к выдаче (удерживая клавишу Сtrl), и на вкладке ленты Вставка на панели инструментов Диаграммы выберите вид Гистограмма.
33. Используя вкладку ленты Макет, вставьте подписи осей и название диаграммы.
Рис. 30. Пример оформления диаграммы
34. Постройте круговую диаграмму, показывающую соотношение между общей суммой к выдаче и суммарным подоходным налогом (рис. 31).
Рис. 31. Пример оформления круговой диаграммы
|
|
|
