абсолютного адреса. Повторение приемов форматирования таблиц, изменения

Форматов ячейки, построения диаграмм

1. Назовите новый лист именем «Налог».

2. Создайте таблицу, приведенную ниже:

2.1 При оформлении первых трех строк используйте параметр горизонтального выравнивания

«По центру выделения» (Главная – Ячейки – Формат – Формат ячеек – Выравнивание)

2.2 Для заполнения ячеек с заголовками столбцов используйте автоподстановку маркером заполнения.

 

 

 

2.3 Ячейки с заголовками столбцов таблицы выровняйте по горизонтали и вертикали «По центру» и примените отображение «Переносить по словам» или для более точного переноса слов используйте Alt - Enter

 

2.4 Ширина столбца B= 30 символов, для остальных– 12 символов (Главная – Ячейки – Формат – Ширина столбца …).

2.5 Параметры шрифта для всей таблицы: Times New Roman, обычный, 12.

2.6 Введите данные и установите внешние и внутренние границы созданной таблицы

(Главная – Ячейки -Формат – Формат ячеек – Граница)

1. Произведите расчет показателей:

1.1. Итоговые суммы рассчитайте с помощью функцииАвтосумма - кнопка ∑

1.2. Средние значения рассчитайте с помощью Мастера функций (Вставка – Функция):

1.2.1. Шаг 1. Категория - Статистические, Функция – СРЗНАЧ.

1.2.2. Шаг 2. Укажите мышью диапазон ячеек для расчета среднего:

· C6:E6 для подсчета средней суммы по району

 

 

 

· C6:E13 для расчета среднего значения по городу

1.3. При расчете доли необходимо:

· В столбце F получены итоговые результаты по каждому из районов и в целом по городу.

· Всегопо городу составляет 100%. Определяем долю каждого района составлением пропорции:

2768,5 – 100%

420 - Х(%)

Следовательно

· При применении процентного формата ячеек MS Excel автоматически производит умножение содержимого ячейки на 100 и добавляет знак %.

· Поэтому в ячейке G6устанавливаем процентный формат

(Главная – Ячейки – Формат – Формат ячеек .. - Число – Процентный);

И вводим формулу =F6/F14

· Полученную формулу нельзя распространить вниз в диапазоне G6:G14. т.к. в ней должны меняться цифры по районам (числитель), а знаменатель (Всего по городу) должен оставаться без изменений. Поэтому необходимо использовать абсолютный адрес. В ссылке на адрес ячейки F14 нужно закрепить позицию строки =F6/F$14

· Вернитесь в ячейку G6, возьмитесь за маркер заполнения и заполните полученной формулой диапазон G6:G14.

· Определите влияющие на формулу ячейки

В результате таблица примет следующий вид:

2. Постройте диаграмму:

· круговую,

· отображающую итоговые данные сбора налогов по районам города

(Исходными данными являются ячейки столбца Bи столбца G.)

· снабженную заголовком, легендой и подписями данных (доля)

· размещенную на отдельном листе с именем «Всего налогов».

 

 

Диаграмма должна выглядеть примерно так:

 

ЗАДАНИЕ 12. Повторение применения абсолютных ссылок, автозаполнение диапазона ячеек.

1. Назовите новый лист именем «Сводка продаж».

2. Создайте следующую таблицу:

 

Указания по оформлению таблицы:

· Слово «Дата» введите в ячейку А2 и выровняйте по центру диапазона ячеек А2:В2

· В ячейку С2 введите дату в формате 10 февраля 2008 г. Выровняйте дату по центру диапазона ячеек С2:Е2

· Ячейки В4:В6 содержат цены в денежном формате и обозначениями $ Английский (США)

· К содержимому ячеек D4:D8 примените денежный формат и обозначение р.

· К содержимому ячеек Е4:Е6 примените процентный формат в 2-мя десятичными знаками

· Для ячеек А2:А6, В2:Е2, С7 примените отображение «Переносить по словам».

· Для всей таблицы настройте внешние и внутренние границы

· Ячейку D7 залейте произвольным цветом.

 

3. Определите сумму в руб. для одного дня.:

· В ячейке D4 постройте формулу, подсчитывающую произведение цены в у.е., количества проданного за день и курса у.е.

· Т.к. курс у.е. един на данный день для каждого вида товара, то в формуле необходимо зафиксировать ссылку на строку 8.

· Т.к. цена в у.е. одинакова для любого дня продажи, то закрепите ссылку на столбец В.

 

Предъявите преподавателю конструкцию формулы в ячейке D4. Если формула построена правильно, то:

· Распространите ее с помощью маркера заполнения в ячейки D5:D6.

· Дневную выручку получите в ячейке D7 с помощью автосуммирования.

4. Рассчитайте % от дневной выручки в ячейке Е4 (см. расчет доли налогов по району в задании 11.). При построении формулы учитывайте необходимость закрепленияпозициистроки 7.

· Если формула верна, распространите ее в ячейки Е5:Е6 с помощьюмаркера заполнения.

После проведения вычислений таблица должна иметь следующий вид:

5. Получите сводку продаж на 11 и 12 февраля. Для этого:

· Выделите диапазон ячеек С2:Е8

· Распространите диапазон с помощью маркера заполненияна 6 столбцов вправо.

Обратите внимание, что:

· Дата продажи автоматически увеличивается на 1 день

· Курс у.е. автоматически увеличивается ежедневно на 1 рубль.

· Произвольно измените курс у.е., прибавив или убавив несколько копеек.

· Произвольно измените данные за 11 и 12 февраля в столбцах «Продано за день»

 

В результате таблица должна иметь примерно такой вид:

Проверьте влияющие ячейки (в менюФормулы).

 

Результаты работы предъявите преподавателю