таблицы

создать таблицы в EXEL
нужно сделать в точности, как в файле- то есть, данные должны быть одни и те-же!

ТЕХНОЛОГИИ ЦИФРОВОГО ОБРАЗОВАНИЯ

Практическая работа №5 (4 ч)

Работа в электронных таблицах MS EXCEL

ФИО студента, курс, шифр группы:

____________________________________________________________

Цель работы. Создание электронной книги. Относительная и абсолютная адресация в MS Excel. Расчет заработной платы, оформление ведомости заработной платы. Построение диаграммы в таблицах MS Excel. Нахождение значения функции y = f(x) при заданных значениях x, построение графика функции y = f(x).

Оборудование: персональный компьютер, MS Excel.

Практическая работа содержит краткие теоретические сведения.

Абсолютная и относительная адресация

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

Относительная ссылка – это изменяющийся при копировании или перемещении формулы адрес ячейки, содержащей исходные данные (например: B5, G11).

Абсолютная ссылка – это не изменяющийся при копировании и перемещении адрес ячейки, содержащей исходное значение. Для указания абсолютной адресации вводится символ $. Различают два типа абсолютной ссылки – полная и частичная.

Полная абсолютная ссылка указывается, если при копировании или перемещении адрес ячейки, содержащей исходное значение, не меняется. Для этого ставится символ $ и перед именем столбца и перед номером строки. Например: $B$5, $R$23.

Частичная абсолютная ссылка указывается, если при копировании и перемещении не меняется номер строки или наименование столбца.

B$5, G$11 – частичные абсолютные ссылки, не меняется номер строки,

$B5, $G11 – частичные абсолютные ссылки, не меняется имя столбца.

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

Задание № 1. Создание электронной книги. Относительная и абсолютная адресация в MS Excel. Расчет заработной платы, оформление ведомости заработной платы.

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

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

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

Создайте таблицу расчета заработной платы по образцу (рис. 52). Введите исходные данные – Табельный номер, ФИО и Оклад, % Премии =27%, % Удержания = 13 %.

Рисунок 52

Произведите расчеты во всех столбцах таблицы.

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

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

Рисунок 53

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

Всего начислено = Оклад + Премия (=С5+D5) (рис. 54).

Рисунок 54

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

Удержание = Всего начислено × % Удержания, для этого в ячейке F5 наберите формулу: =$F$4*Е5 (рис. 55).

Рисунок 55

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

К выдаче = Всего начислено – Удержания (=E5-F5) (рис. 56).

Рисунок 56

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

Рисунок 57

Рисунок 58

Рисунок 59

Рисунок 60

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

Рисунок 61

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

Скопируйте содержимое листа «Зарплата март» на новый лист (Главная/Ячейки/Формат/Переместить или Скопировать лист). Можно воспользоваться командой Переместить/Скопировать контекстного меню ярлычка листа. Не забудьте для копирования поставить галочку в окошке «Создать копию» (рис.62).

Рисунок 62

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

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

Рисунок 63

Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» (Главная/Ячейки/Вставить/Вставить столбцы на лист или воспользовавшись контекстным меню ячейки, слева от которой нужно вставить столбец). Значение доплаты примите 5%.

Рассчитайте значение доплаты по формуле: Доплата = Оклад × % Доплаты

Рисунок 64

Измените формулу для расчета значений колонки «Всего начислено»: Всего начислено = Оклад + Премия + Доплата (=C5+D5+E5).

Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 7000 и 10 000 – зеленым цветом шрифта; меньше 7000 – красным; больше или равно 10 000 – синим цветом шрифта (Главная/Условное форматирование/Правила выделения ячеек).

При выборе цветов текста возможно использование «Пользовательского формата» (рис. 65, 66, 67).

Рисунок 65

Рисунок 66

Рисунок 67

Поставьте к ячейке D3 комментарии «Премия пропорциональна окладу»

Комментарий (примечание) можно добавить к ячейке через контекстное меню или с помощью горячих клавиш. 1) Через контекстное меню

Алгоритм действий:

Выбрать ячейку, к которой нужно добавить комментарий.

Щёлкнуть по ячейке правой кнопкой мыши.

В появившемся списке нажать «Вставить заметку (примечание)».

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

В качестве комментария можно добавить любую текстовую информацию.

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

2) С помощью горячих клавиш

Сочетание клавиш: Shift + F2 - добавляет комментарий к ячейке.

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

3) Рецензирование/Примечания/Создать примечание.

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

Конечный вид заработной платы за апрель приведен на рис.68.

Рисунок 68

Защитите лист «Зарплата апрель» от изменений (Рецензирование/Изменения/Защитить лист). Задайте пароль на лист (рис.69), сделайте подтверждение пароля (рис.70).

Рисунок 69

Рисунок 70

Убедитесь, что лист защищен и невозможно удаление данных. Снимите защиту листа (Рецензирование/Изменения/Снять защиту листа).

Сохраните созданную электронную книгу в своей папке.

Задание № 2. Построение диаграммы в таблицах MS Excel.

Рассчитать зарплату за май и построить диаграмму.

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

Запустите редактор электронных таблиц Microsoft Excel и откройте созданный файл «Зарплата».

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

Присвойте скопированному листу название «Зарплата май». Исправьте название месяца в ведомости на май.

Измените значение Премии на 46%, Доплаты – на 8%. Убедитесь, что программа произвела пересчет формулы (рис. 71).

Рисунок 71

По данным таблицы «Зарплата за май» постройте диаграмму доходов сотрудников. В качестве подписей оси Х выберите фамилии сотрудников. Произведите форматирование диаграммы. Конечный вид гистограммы приведен на рис. 72.

Рисунок 72

Задание № 3. Нахождение значений функции y= f(x) при заданных X.

Задание №3-1. Найти значение и построить y=x2, как показано на рис.73.

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

В ячейку В2 выполнить запись =A2^2.

Важно: для возведения в степень используется знак ^, который можно получить с помощью комбинации клавиш Shift+6 на английской раскладке клавиатуры.

Нужно построить график функции y=x2 на промежутке [-5;5] с шагом равным 1.

Создание таблицы

Создадим таблицу, первый столбец назовем переменная X (ячейка А1), второй — переменная Y (ячейка В1). Для удобства в ячейку В1 запишем саму функцию, чтобы было понятно, какой график будем строить. Введем значения -5, - 4 в ячейки А2 и А3 соответственно, выделим обе ячейки и скопируем вниз. Получим последовательность от -5 до 5 с шагом 1.

Вычисление значений функции

Нужно вычислить значения функции в данных точках. Для этого в ячейке В2 создадим формулу, соответствующую заданной функции, только вместо X будем вводить значение переменной Х, находящееся в ячейке слева (-5).

Важно: обязательно между коэффициентами и переменной нужно ставить знак умножения * (Shift+8).

Ввод формулы завершаем нажатием клавиши Enter. Мы получим значение функции в точке X=-5. Скопируем полученную формулу вниз.

Мы получили последовательность значений функции в точках на промежутке

[-5;5] с шагом 1.

Выделить диапазон значений и вставить гистограмму (график функции) – рис. 73.

Рисунок 73

Задание № 3-2. Построение графика функции y = x3 – 3x2 + 2x – 1.

Используя вкладки Конструктор / Макет / Формат, можно изменить параметры графика. Выделим диапазон значений переменной X и функции Y.

Рисунок 74

Перейдем на вкладку Вставка и в группе Диаграммы выберем Точечная (можно выбрать любую из точечных диаграмм, но лучше использовать вид с гладкими кривыми) (рис. 75).

Рисунок 75

Мы получили график данной функции.

Сделайте вывод о проделанной практической работе: