Графические возможности Excel

Построение диаграмм

Диаграммы служат для наглядного представления табличных данных.

Рассмотрим построение диаграммы на примере по данным таблицы рис.11.

Пример

Построить круговую диаграмму по данным столбца «Количество» относительно данных столбца «Наименование товара». Легенда – данные столбца «Наименование». Значения областей диаграммы должны быть выражены в процентах.

Для построения диаграммы необходимо:

1) Выделить диапазон ячеек, по которым будет построена диаграмма. В данном случае необходимо выделить ячейки В3:В10, а затем, удерживая нажатой клавишу <CTRL>, выделить F3:F10.

2) Щелкнуть в панели инструментов Стандартная кнопку или вызвать команду Вставка => Диаграмма.

3) В открывшемся окне Мастер диаграмм (шаг 1 из 4) на вкладке Стандартные выбрать тип диаграммы Круговая и понравившийся вид; щелкнуть по кнопке окна Далее> (рис. 12).

4) В открывшемся окне Мастер диаграмм (шаг 2 из 4) проверить, правильно ли указан Диапазон (должен быть В3:В10; F3:F10); переключатель Ряды в: должен находиться в положении столбцах. Щелкнуть по кнопке окна Далее> (рис. 13).

5) В открывшемся окне Мастер диаграмм (шаг 3 из 4):

- на вкладке Заголовки в поле «Название диаграммы» можно ввести название диаграммы (в примере введено «Круговая диаграмма»);

- на вкладке Легенда можно задать параметры легенды (пояснений по обозначениям);

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

Щелкнуть по кнопке окна Далее>.

 

 

Рис. 12. Окно Мастер диаграмм (шаг 1 из 4)

Рис. 13. Окно Мастер диаграмм (шаг 2 из 4)

Рис. 14. Окно Мастер диаграмм (шаг 3 из 4)

 

Рис. 15. Окно Мастер диаграмм (шаг 4 из 4)

6) 6. В открывшемся окне Мастер диаграмм (шаг 4 из 4): установить переключатель Поместить диаграмму на листе: в нужное положение и щелкнуть по кнопке окна Готово (рис. 15).

 

Редактирование диаграммы

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

- либо щелкнуть по ней правой кнопкой мыши (например, на заголовке – рис. 16). В открывшемся контекстном меню щелкнуть левой кнопкой мыши команду Формат... Открывается окно форматирования выбранной области;

- либо дважды щелкнуть левой кнопкой мыши объект, который необходимо отредактировать.

Рис. 16. Форматирование области заголовка

 

ЗАДАНИЯ К ЛАБОРАТОРНОЙ РАБОТЕ

Задание №1 к лабораторной работе

Задача 1

1) Составить таблицу (рис. 17) с указанными ниже параметрами:

- наименование таблицы выполнить методом объединения ячеек по центру;

- шапка таблицы и итоговая строка должны быть выделены жирным обрамлением;

- заголовки Наименование товара, Реквизиты продавца и Расчеты должны быть оформлены методом объединения ячеек с центрированием по горизонтали;

- ячейки под заголовками столбцов Цена, Доход, Расход, Прибыль должны иметь денежный формат с числом знаков после запятой 2 и обозначением грн.;

- ячейки под заголовками столбцов № счета и Кол-во должны иметь числовой формат;

- остальные ячейки должны иметь текстовый формат.

Рис. 17. Вид таблицы

2) Рассчитать ячейки под заголовками столбцов Доход, Расход, Прибыль и итоговую строку:

- доход рассчитывается как произведение Цены и Количества;

- Расход равен 42% от Дохода;

- Прибыль рассчитывается как разность Дохода и Расхода;

- итоговая строка рассчитывается по столбцам Доход, Расход и Прибыль.

3) Построить кольцевую диаграмму по данным столбца Прибыль относительно данных столбца Наименование товара. Легенда – данные столбца Наименование товара. Значения областей диаграммы должны быть выражены в процентах.

Задача 2

Рассчитать заработную плату десяти работников отдела, исходя из следующих данных: почасовая ставка для работников в зависимости от категорий следующая – 3-я категория – 5,9 грн./час; 2-я категория – 12,4 грн./час; 1-я категория – 25,5 грн./час.

В отделе работают пять работников третьей категории, три работника второй категории и два работника первой категории. Один из работников первой категории (Павлов В.В.) отработал 80 часов, а другой (Заморин А.Р.) – 100 часов. Два работника второй категории (Иванов Ю.П. и Петров А.А.) отработали в месяц 120 часов каждый, а третий – Ступин А.В. – 50 часов. Работники третьей категории отработали соответственно: Сидоров П.Л. – 140, Дюков А.А. – 105, Панин Р.А. – 105, Козлов С.П. – 80, Салин Е.Л. – 110 часов.

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

Задача 3

Рассчитать среднюю плату за пребывание ребенка в детском саду в 2001, 2002, 2003 гг., учитывая, что по месяцам она составила: 2001 г. – с 1.01 по 30.04 – 8 грн. за месяц, с 1.05 по 31.08 – 15 грн. за месяц, с 1.09 по 31.12 – 40 грн. за месяц; 2002 г. – с 1.01 по 31.08 – 55 грн. за месяц, с 1.09 по 31.12 – 70 грн. за месяц; 2003 г. – с 1.01 по 31.07 – 90 грн. за месяц; с 1.08 по 31.12 – 125 грн. за месяц.

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

Задача 4

Рассчитать плату за электроэнергию в 2003 и в 2004 годах, если норма в 2003 г. составила 0,11 грн. за киловатт, а в 2004 г. – 0,156 грн. за киловатт.

Расход электроэнергии жильцами восьмиквартирного дома составил: квартира 1 – 120 Квт/мес., квартира 2 – 80 Квт/мес., квартира 3 – 150 Квт/мес., квартира 4 – 200 Квт/мес., квартира 5 – 120 Квт/мес., квартира 6 – 75 Квт/мес., квартира 7 – 160 Квт/мес., квартира 8 – 270 Квт/мес.

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

Задача 5

Рассчитать зарплату работников предприятия в зависимости от отработанного объема часов, если общая сумма выделенная на зарплату составила 25 тысяч грн., подоходный налог составляет 13% от начисленной суммы, 1% составляют выплаты в пенсионный фонд, 2% составляют прочие выплаты.

В отделе 7 работников, отработавшие соответственно: Петров А.Л., – 180 часов, Петров Л.Д. – 20 часов, Сидоров Е.Н. – 80 часов, Павлов А.А. – 120 часов, Лаптев П.Л. – 90 часов, Ванин П.П. – 120 часов, Калмыков М.И. – 100 часов.

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

Задача 6

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

 

№ п/п	Статьи затрат	Примечание
	Стоимость вычислительной техники
	Стоимость прочего оборудования	10% от п.1
	Монтаж и наладка ВТ	10% от п.1
	Монтаж и пусконаладочные работы вспомогательного оборудования	5% от п.1
	Среднегодовая заработная плата,
в том числе: основная
дополнительная	8% от осн. з/п
	Амортизацонные отчисления на ВТ	12% от п.1
	Стоимость ремонта оборудования	2,5% от п.1
	Возмещение износа быстроизнашиваемых предметов	2% от п.1
	Стоимость электроэнергии
	Транспортные расходы	4% от п.1
	Прочие расходы	2% от п.1

Представить в виде объемной гистограммы соотношение затрат по указанным пунктам.

Задача 7

Составить таблицу, определяющую размер прибыли предприятия в зависимости от объема реализованной партии товаров. Объем можно задать от 50 единиц через равные интервалы. Отпускаемая цена за единицу изделия стабильна, имеются фиксированные издержки, не зависящие от размера партии товаров, и переменные издержки за единицу реализованной продукции, которые зависят от объема партии.

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

Форма таблицы

ПРИБЫЛЬ В ЗАВИСИМОСТИ ОТ ОБЪЕМА РЕАЛИЗАЦИИ

Отпускная цена за единицу товара $7,5

Переменные издержки на единицу продукции $2,5

Размер партии, щтук	Постоянные издержки, $US	Переменные издержки, $US	Общие издержки, $US	Доход от продаж, $US	Прибыли и убытки, $US

Задача 8

Составить таблицу, по которой можно производить расчеты расходов на служебную машину. Таблица должна через фиксированные промежутки времени (например, через 5 дней) фиксировать отсчет спидометра машины, рассчитывать пройденный километраж за период, учитывать количество купленного бензина за этот период и добавочные покупки (ремонт и т.п.), а также рассчитывать стоимость горючего и общую стоимость издержек за соответствующий период.

Таблица должна рассчитывать итоговые издержки за месяц и средний пробег (в км/литр).

Форма таблицы

РАСХОДЫ НА СЛУЖЕБНЫЕ МАШИНЫ

Месяц июль

Число месяца	Отсчет спидометра, км	Куплено бензина, л	Цена за 1 л	Стоимость бензина	Допол. расходы	Общая стоимость	Пробег, км
			9,8
			9,8
			9,8
			9,8
			9,8
			9,8
			9,8

 

Итого к оплате:___________

Средний пробег, км/л:_____

Задача 9

Составить таблицу прейскурантных цен. Она должна содержать сведения о коде изделия, цене нетто, НДС (рассчитывается по ставке НДС), цене с НДС, цене изделия с учетом 10% и 15% скидок постоянным клиентам фирмы.

Форма таблицы

ПРЕЙСКУРАНТНЫЕ ЦЕНЫ С УЧЕТОМ НДС И СКИДОК

ПОСТОЯННЫМ КЛИЕНТАМ ФИРМЫ

Ставка НДС 20%

Код изделия	Цена нетто	Общая стоимость	Скидка 10%	Цена со скидкой 10%	Скидка 15%	Цена со скидкой 15%
Y340	$20
Y341	$31
Y342	$14
Y343	$22
Y344	$25
Y345	$31
Y346	$39
Y347	$28

Задание №2 к лабораторной работе

1) Составить таблицу (приложение 1), параметры таблицы должны быть максимально приближены к указанным на рисунке.

2) Рассчитать сумму прихода, сумму продажи, прибыль и рентабельность по всем строкам.

3) Вычислить среднюю рентабельность и среднюю прибыль.

4) Вычислить максимальную и минимальную рентабельность.

5) Сколько фактов реализации сыпучих материалов отмечено?

6) На какую сумму реализовано цемента?

7) На какую сумму реализовано цемента белого?

8) Какова суммарная прибыль товаров, продававшихся по цене >200грн. за единицу?

9) Какова суммарная прибыль, полученная при реализации сыпучих, отделочных, лакокрасочных материалов и пиломатериалов?

10) Построить график по результатам выполнения задания. Вид графика – гистограмма. Оформление графика выбрать самостоятельно.

11) Рассчитать среднюю рентабельность, только тогда, когда сумма продажи была больше 100 грн.

12) Рассчитать количество фактов реализации товаров, пришедших в количестве более 100 единиц.

13) Вычислить суммарную прибыль товаров, продававшихся в ноябре.

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

КОНТРОЛЬНЫЕ ВОПРОСЫ

1) Как запустить табличный процессор Excel?

2) Опишите последовательность действий, необходимых для выделения блока, нескольких несмежных блоков.

3) Какие данные могут заноситься в ячейки таблицы?

4) Как задать для ячейки (блока ячеек) формат числовых данных?

5) Как выполняется ввод формул в ячейки?

6) Как выполняется заполнение (копирование вправо или влево, вверх или вниз)?

7) Назовите способы адресации в Excel. Приведите примеры использования абсолютной, относительной и смешанной адресации.

8) Как выполнить печать таблицы на экране (предварительный просмотр) и на принтере?

9) Опишите последовательность построения диаграмм.

10) Из каких элементов состоит диаграмма, как выполняется редактирование отдельных элементов диаграммы?

11) Синтаксис функций Excel.

12) Работа с мастером функций Excel.

13) Для чего применяется функция EСЛИ и какой ее синтаксис? Приведите примеры использования функции ЕСЛИ.

14) Для чего применяют функции МАКС и МИН и каков их синтаксис? Приведите примеры использования функций МАКС и МИН.

15) Для чего применяют функции СУММ и СУММЕСЛИ? В чем отличие функции СУММЕСЛИ от функции СУММ. Какой синтаксис у функции СУММЕСЛИ? Приведите примеры использования функции СУММЕСЛИ.

16) Для чего применяют функции СЧЁТ и СЧЁТЕСЛИ? В чем отличие функции СЧЁТЕСЛИ от функции СЧЁТ. Какой синтаксис у функции СЧЁТЕСЛИ? Приведите примеры использования функции СЧЁТЕСЛИ.