Редактор электронных таблиц MS Excel.
Занятие №4.
Ссылки и формулы. Имена.
Присваивание имен. Использование заголовков в формулах. Присвоение и использование имен. Присвоение ячейкам имени при помощи существующих заголовков строк и столбцов.
Имена и заголовки.
Для упрощения просмотра и запоминания формул одной или нескольким ячейкам можно присвоить имя. Если в листе присутствуют заголовки строк и столбцов, то на них можно сослаться в формуле или использовать как имена, связанные с ячейками. Кроме того, можно создать имя, которое представляет одинаковые ячейки или группу ячеек на нескольких листах.
Использование заголовков в формулах.
Использование в формуле ссылок на данные листа при помощи заголовков строк и столбцов делает ее более наглядной. Например, если таблица содержит количество проданной продукции в столбце “Продажа”, а отдел, выполнивший торговую операцию, находится в строке “Отдел”, то формула “=(Продажа Отдел)” будет задавать количество проданной продукции заданным отделом. Между названием столбца и строки обязательно должен идти пробел, а всю ссылку лучше взять в скобки.
Осторожно! При использовании одного заголовка два раза могут возникнуть проблемы.
Присвоение и использование имен.
Выделите ячейку, группу ячеек или несмежный диапазон, которому необходимо присвоить имя.
Укажите поле имени, которое расположено слева в строке формул.
Введите имя ячеек.
Нажмите клавишу ENTER.
Либо: Выделить группу ячеек и воспользоваться пунктом Меню=>Вставка=>Имя=>Присвоить.
В этом же пункте меню можно удалить ошибочно присвоенное имя.
Ссылка по имени является абсолютной.
Например, в некоторой ячейке записан курс доллара. Назовем эту ячейку “Бакс”. Предположим, что в таблице столбце “Стоимость” идут стоимости фруктов в долларах от “Ананас” до “Яблоко”. Чтобы посчитать сумму в рублях, надо использовать формулу “=сумм((Стоимость Ананас):(Стоимость Яблоко))*Бакс”.
Присваивание ячейкам имени при помощи существующих заголовков строк и столбцов
Выделите область, в которой следует присвоить имена строкам или столбцам. Выделенная область должна содержать строку или столбец заголовков.
Выберите команду Имя в меню Вставка, а затем — команду Создать.
Установите флажки В строке выше, В столбце слева, В строке ниже или В столбце справа в группе флажков По тексту, чтобы указать расположение заголовков, из которых следует создать имена.
Примечание. Созданное таким образом имя ссылается только на ячейки, содержащие значения, и не включает в себя существующих заголовков строк и столбцов.
Практическое задание №4
Файл task4.xls , файл ex4.xls .
В своей рабочей папке создайте папку Excel, если ее там нет. (На сервер заходить не обязательно).
Возьмите файл task4.xls.
Не забудьте заполнить свойства документа -
именно по ним определяется авторство.
Красиво оформите книгу task4.xls. Это значит: во-первых, заполнить все пропуски. Во-вторых, сделать так, чтобы текст соответствовал ячейкам по размерам (чтобы текст не выпирал за границы ячейки). В-третьих, нарисовать красивые границы и заливку -
на ваш вкус, но чтобы было.
Перед вами -
документы некоего предприятия. На предприятии работают 22 работников. Они работают уже 12 месяцев. Все работники -
разных категорий. Категории приписаны на листе “Категория”. Все работники работают по-разному хорошо. Насколько хорошо работают работники -
написано на листе “Показатель производительности”. 0 баллов означает, что работник на работу не ходит, 4 балла и больше означает, что работник работает великолепно. 1, 2, 3 балла -
какие-то промежуточные оценки. За работу начисляется зарплата. Кроме того, есть поощрения тем, кто ходит на работу и тем, кто работает хорошо. Эти суммы каждый месяц меняются. (см.лист “Денежные начисления”). Кроме того, с работников взимаются налоги. Как рассчитываются налоги -
написано внизу листа “Денежные начисления”. Вам надо:
Создайте новый лист. Назовите его “З_п_январь”. На этом листе вы должны составить ведомость для начисления зарплаты за январь.
Образец оформления смотрите в файле ex4.xls.
Cтолбец “зарплата” заполняется в зависимости от категории трудящегося. Столбцы “премия за …” заполняется в зависимости от таблицы “производительность труда”. В столбце “Итого начислено” стоит сумма трех чисел: зарплаты и двух премий. Налоги вычисляются так, как написано на листе “Денежные начисления”. Итого к выдаче: Начисленная сумма минус налоги.
Подсказка: Как вычислить зарплату: Надо сделать так: Если категория работника 1 -
дать ему зарплату, положенную для категории 1. Если же она не 1, проверить снова: если она 2, дать ему зарплату как у второй категории, если же она и не 2, то дать ему зарплату как у третьей категории.
Не забывайте об абсолютной и относительной адресации.
Все должно быть сделано формулами, должно автоматически меняться при изменении исходных таблиц.
Создайте лист “Зарплата”. Теперь вам надо создать ведомость с зарплатами за все время работы предприятия. Она должна получиться примерно как в файле ex4.xls: Естественно, все должно быть вычислено формулами.
Подсказка: создайте два вспомогательных листа: доход, налог. На листе “Доход” вычислите суммарный доход каждого работника. На листе “Налог” вычислите налог. Тогда легко будет вычислить значения на листе “Ведомость”.
ВСЕ книги заархивируйте и отправьте по почте (katpop@yandex.ru). Размер письма не должен превышать 50 Kb (для тех, кто без звездочки), 100 Kb (для тех, кто со звездочкой). Письма больше 100 Kb получать не буду.
Задание на звездочку.На отдельном листе в ячейке А1 написано некоторое число. (Вы напишите там число 1000 -
чтобы сдавать). В ячейке B1 еще одно число (Вы напишите там число 2). Создайте таблицу 50´
50 (прямо на этом же листе), содержащюю случайные числа от 1 до числа, написанного в ячейке А1, после запятой должно быть столько знаков, какое число написано в ячейке В1. (Т.е. при А1=1000 и В1=2 должны быть случайные числа от 1 до 1000 с 2 знаками после запятой.) (2 знака означает не формат отображения данных, а реальные данные, т.е. при увеличении знаковости должны приписываться нули, а сами эти 2 знака должны быть разнообразными). Конечно же, при изменении чисел в ячейках А1 и В1 все должно автоматически поменяться.
, файл ex4.xls