Лабораторная работа №8. ТАБЛИЧНЫЙ ПРОЦЕССОР EXCEL. ТАБЛИЦЫ С ПОСТОЯННЫМИ ДАННЫМИ И ФОРМУЛАМИ
Цель работы – ознакомление с табличным процессором Microsoft Excel; выполнение расчетов с использованием формул и постоянных данных; построение простейших диаграмм.
Табличные процессоры – программные продукты, предназначенные для автоматизации обработки данных, представленных в табличной форме. Электронные таблицы используются в настоящее время также часто, как и текстовые документы. Среди табличных процессоров большим успехом пользуются различные версии Microsoft Excel, Lotus 1-2-3, Quattro Pro.
Рабочее поле Excel 2007 – электронная таблица, состоящая из столбцов и строк. Название столбцов – буква, две или три буквы латинского алфавита. Каждая строка таблицы пронумерована. Размер таблицы фиксирован: число столбцов – 16 000, строк – 1 048 576.
Пересечение конкретного столбца и строки образует ячейку. Местоположение ячейки задается адресом, образованным из имени столбца и номера строки, на пересечении которых находится эта ячейка, а также при необходимости в адресе указывается имя листа и имя книги:
В ячейки рабочего листа могут вводиться два вида данных: постоянные значения (константы) и формулы.
Постоянные значения – это числа, символы, текст. В ячейку константа записывается следующим образом: курсор щелчком мыши устанавливается в ячейку и с клавиатуры вводится значение. Редактирование введенного значения проводится после выделения нужной ячейки, а далее следует либо нажать клавишу <F2>, либо щелкнуть кнопкой мыши в строке ввода (редактирования) в нужном месте.
Рис. 52. Общий вид рабочего окна электронного процессора.
Под формулой в электронной таблице понимается выражение, состоящее из операндов и применимых к ним операций.
В качестве операндов выступают: числа; тексты; логические значения; ссылки на адреса ячеек; значения ошибки; встроенные функции Excel.
Используемые операции:
- арифметические – сложение, вычитание, деление, умножение;
- отношения – больше, меньше, не больше, не меньше, не равно.
Формулы строятся как выражение для вычисления нового значения. Тип значения, полученного в результате вычисления по формуле, определяется типом операндов выражения. Формула всегда начинается с символа равно (“=”).
В Excel для различных типов вычислений имеется большое число встроенных функций: математических, статистических, логических, текстовых, информационных и др., объединенных в библиотеку функций.
Функции вводятся обычным набором с клавиатуры или более предпочтительным способом – с помощью вкладки Формулы (выбрать из доступных на вкладке) или Мастера функций, диалоговое окно которого вызывается командой
Формулы®Библиотека функций®Вставить функцию®…
В Мастере функций все функции разделены на категории, каждая из которых включает в себя определенный набор функций. Состав каждой функции показан в правом окне. Сначала следует выбрать категорию функции (слева), имя функции (справа), внизу дается краткий синтаксис функции.
Рис. 53. Диалоговое окно Мастер функций для выбора категории и вида функции.
После нажатия кнопки <ОК> появляется следующее диалоговое окно, соответствующее выбранной функции и осуществляется построение функции – указание её аргументов.
Рис. 54. Пример диалогового окна для задания аргументов логической функции СУММ.
Формулу вводят в выбранную ячейку. Для вставки в формулу различных функций в строке ввода, которая находится в верхней части окна над рабочим полем, предусмотрена кнопка вызова функций. Часто при создании формул используют ссылки на другие ячейки.
Ссылка – элемент формулы, который используется, когда нужно сослаться на другую ячейку таблицы. В этом случае адрес используется в качестве ссылки. Например, формула С8=0,4*В5 означает, что в ячейке В5 расположены данные, используемые в формуле ячейки С8. Т.о., в формулу ячейки С8 будут введены не сами данные, а адрес ячейки В5.
Различают следующие типы ссылок:
- относительные ссылки (например, А34 или С2), которые всегда изменяются так, чтобы отобразить правило их вхождения в формулу относительно её нового местоположения;
- абсолютные ссылки, которые перед именем столбца или номером строки имеют символ $. Назначение ссылки абсолютной производится следующим образом: в строке ввода перед ссылкой устанавливается курсор и нажимается клавиша <F4>, например, $А$4. Можно сделать то же самое, вводя символ $ с клавиатуры. При копировании абсолютные ссылки остаются неизменными;
- частично абсолютные ссылки, которые при копировании корректируются частично. Символ $ стоит или перед именем столбца, или перед номером строки ($F4, D$6). Например, при копировании формулы, содержащей $G3, сохранится имя столбца G, а номер строки будет изменен;
- имена блоков, например ЦЕНА. Имя связывается с данными блока, а не с его местоположением. Можно блок перенести в другое место, что не повлияет на его имя.
Для достижения успеха следует уметь выполнять действия:
Ø вставить новый лист в документ
Главная®Ячейки®Вставить®Вставить лист
Рис. 55. Окно вставки объектов в книге.
Ø выровнять текст в ячейке
Главная®Выравнивание®…
(выбрать тип выравнивания текста в ячейке)
Ø изменить направление текста в ячейке
Главная®Выравнивание®Ориентация®…
(выбрать тип выравнивания текста в ячейке)
Рис. 56. Окно «Ориентация».
Ø объединить ячейки
Главная®Выравнивание®Объединить и поместить в центре
Рис. 57. Окно объединения ячеек таблицы.
Ø организовать перенос слов в ячейке
Формат®Ячейки…®Выравнивание®Переносить по словам
Рис. 58. Кнопка переноса текста в ячейке.
Ø задать тип данных в ячейке
Главная®Число®Числовой формат®…
(выбрать в списке необходимый формат®ОК)
Рис. 59. Окно «Формат ячейки».
Ø вставить функцию
Формулы®Библиотека функций®Вставить функцию®…
(выбрать из списка требуемую функцию®ОК)
Ø вставить диаграмму
Вставка®Диаграммы®…
(выбрать тип диаграммы и выполнить предлагаемые действия)
Ø создать примечание к ячейке
Рецензирование®Примечания®Создать примечание
Ø очистить содержимое ячейки
Главная®Редактирование®Очистить®Очистить все
Рис. 60. Окно очистки параметров ячейки.
Ø переименовать текущий лист
правой кнопкой мыши щелкнуть по имени листа, в появившемся контекстном меню выбрать Переименовать, ввести новое имя, нажать клавишу Enter
Рис. 61. Контекстное меню работы с листами книги.
Ø задать параметры страницы табличного документа
Разметка страницы®Параметры страницы®…
Ø просмотреть вид табличного документа после печати
Вид®Режимы просмотра книги®Разметка страницы
Ø подготовить табличный документ к печати
Разметка страницы®Параметры страницы®Область печати®…
(задать масштаб воспроизведения)
Задание 1.
Выполнить расчет цены продажи продукции по исходной информации (все показатели в таблице приведены в условных единицах).
| Наименование товара | Цена закупки | Коли-чество | Вес (кг) | Транспортные расходы | Торговая надбавка | Цена продажи | ||
| 1 ед. | всего | на 1 ед. | всего | |||||
| Товар 1 | 2,0 | |||||||
| Товар 2 | 3,0 | |||||||
| Товар 3 | 1,5 | |||||||
| Товар 4 | 2,1 | |||||||
| Товар 5 | 2,0 | |||||||
| Товар 6 | 0,5 | |||||||
| Товар 7 | 3,2 | |||||||
| Товар 8 | 1,0 |
Величина транспортных расходов по доставке всех товаров составляет 1030. Транспортные расходы распределяются пропорционально весу товаров.
Торговая надбавка (на 1 ед.) устанавливается в размере 22% от затрат: [Цена закупки]+[Транспортные расходы].
[Цена продажи]=[Цена закупки]+[Транспортные расходы]+[Торговая надбавка].
При выполнении вычислений все значения округлить (а не установить формат отображения) до двух знаков после запятой.
Построить диаграмму по ценам закупки и продажи товаров, количеству.
Задание 2.
Рассчитать заработную плату для сотрудников предприятия:
| Фамилия И.О. | Количество налоговых льгот | Категория | Начислено по видам оплат | Начислено всего | Облагаемая налогом сумма | Удержано и учтено | Сумма к выдаче | |||||
| начислено | районный коэффициент | премия | аванс | подоходный налог | пенсионный фонд | всего | ||||||
| Никифоров И.Л. | 5 700 | 2 500 | ||||||||||
| Петрова М.Р. | 8 400 | 4 200 | ||||||||||
| Самойлова А.О. | 3 500 | 1 700 | ||||||||||
| Певцов Н.А. | 4 860 | 1 700 | ||||||||||
| Кузбасов Е.Р. | 3 020 | 1 200 | ||||||||||
| Романова Н.Г. | 3 930 | 1 350 | ||||||||||
| Симонов С.П. | 3 700 | 1 200 | ||||||||||
| Зубова Е.А. | 8 500 | 3 500 | ||||||||||
| ВСЕГО |
При проведении расчета принять следующие правила и исходные данные:
- минимальная заработная плата – 140 руб.;
- районный коэффициент – 15% от [3];
- премия начисляется в размере:
o 15% от [3] для сотрудников категории 2 и 4,
o 10% от [3] для остальных сотрудников;
- начислено всего [6]=[3]+[4]+[5];
- облагаемая налогом сумма получается после вычитания из “Начислено всего” произведения “Количество налоговых льгот” на величину минимальной заработной платы: [7]=[6]-[1]´[Минимальная заработная плата]. Облагаемая налогом сумма принимается равной нулю, если в результате расчетов она будет отрицательной;
- отчисление в пенсионный фонд – 1% от [6];
- подоходный налог – 13% от [7];
- удержано всего – [11]=[8]+[9]+[10];
- сумма к выдаче – [12]=[6]–[11];
Для колонок [6], [11] и [12] вычислить среднее, минимальные и максимальные значения.