Построение таблиц

В формулах Excel используются три вида ссылок на ячейки таблицы – относительные, абсолютные и комбинированные.

Относительные ссылки меняются (смещаются) при копировании или смещении формулы, записываются в виде B2, где B – индекс колонки, а 2 – номер строки.

Абсолютные ссылки не меняются при копировании формул, записываются в виде $B$2.

В комбинированных ссылках номер строки (или индекс столбца) является абсолютным, а индекс столбца (или номер строки) – относительным. Записываются они в виде B$2 (при копировании столбцы будут смещаться, а строка останется на месте) либо $B2 (при копировании столбец останется на месте, а строки будут смещаться).

Для изменения типа ссылки можно воспользоваться клавишей F4. При нескольких нажатиях тип ссылки меняется циклически.

2.10. В корзине покупателя, подошедшего к кассе продуктового магазина, лежат следующие продукты: 0, 7 кг мяса по 429 р за 1 кг, 0, 4 кг сыра по 512 р за 1 кг, два литровых пакета молока по 32, 6 р за пакет, две двухсотграммовых пачки масла по 91, 80 за пачку, 3 кг картофеля по 40 р 50 копеек за 1 кг, три банки рыбных консервов по 59 р за банку в 250 г и полкило пряников по 76 р за 1 кг. Требуется составить таблицу покупок, вычислить стоимость каждой покупки, общую стоимость и общий вес всех продуктов. Предусмотрите возможность быстро и удобно изменять числовые значения цен и объемов товара для мгновенного перерасчета результатов. Для удобства переименуйте лист рабочей книги с этой таблицей в «продукты». Оформите таблицу так, чтобы она хорошо читалась.

2.11. Подготовьте такую таблицу:

Вычислите содержимое пустых ячеек, учитывая, что в строке Всего значения столбцов Северное и Южное полушария равны. Для отображения данных, выраженных в процентах, используйте процентный формат ячеек. Оформите таблицу так, чтобы она хорошо читалась. Переименуйте лист рабочей книги с этой таблицей в «Земля».

2.12. Офис фирмы состоит из восьми помещений различных размеров. Для ремонта офиса нужно закупить материалы: линолеум на пол и панели на стены. Составьте таблицу по образцу:

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

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

2.14. Подготовьте такую таблицу из 7-8 строк. Населенные пункты выберите по указанию преподавателя, данные о поездах найдите в сети интернет. Последнюю колонку вычислите.

2.15. Подготовьте такую таблицу:

Заполните произвольными данными о 8 – 10 сотрудниках первые две колонки, остальные вычислите, исходя из следующих условий:

- аванс составляет 40 % оклада;

- районный коэффициент для Урала составляет 15 % оклада;

- пенсионный налог составляет 1 % оклада;

- подоходный налог составляет 13 % от величины (Оклад + Уральский коэффициент – МРОТ – Пенсионный налог), где МРОТ – минимальный размер оплаты труда (его следует хранить в отдельной ячейке);

- сумма к выдаче определяется как Оклад – Аванс + Уральский коэффициент – Подоходный налог – Пенсионный налог.

При создании таблицы используйте именованную константу для подоходного налога (см. упр. 2.8). Для остальных величин используйте именованные ячейки.

2.16. Выполните следующее задание:

1) создать таблицу рейтинга студентов;

2) заполнить 10-15 строк. Значения рейтинга выставлять в диапазоне от 0 до 1;

3) с помощью Условного форматирования (в меню Формат) оформить разными цветами и форматами текста результаты рейтинга по трем группам: больше 0, 90 – один формат, от 0, 90 до 0, 50 – второй, менее 0, 50 – третий.