Тема: Створення, редагування та форматування графіків та діаграм.

Мета: Перевірка рівня сформованості основних навичок роботи з електронними таблицями. Знайомство з загальними відомостями про управління аркушами робочої книги, видаленні, перейменування аркушів. формули, що мають посилання на клітинки іншого аркуша робочої книги. Майстер діаграм. Виділення елементів таблиці, які не є сусідніми.

Підготуємо відомість на видачу заробітної плати (звичайно, дещо спрощений варіант).

Зверніть увагу на те, що в нижній частині екрана горизонтальна смуга прокрутки складається з двох частин. Права частина служить для переміщення по таблиці (вправо, вліво), а ліва частина, яка містить ярлички листів, дозволяє переміщатися між листами.

За замовчуванням робоча книга відкривається з шістнадцятьма робочими листами, імена яких Аркуш1, Аркуш16. Імена аркушів виведені на ярличках в нижній частині вікна робочої книги.

Натискаючи по ярличках, можна переходити від аркуша до аркуша всередині робочої книги.

Ярличок активного аркуша виділяється кольором, до напису на ньому застосований напівжирний стиль.

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

Для виконання вправи нам знадобляться тільки чотири листа:

· на першому розмістимо відомості про нарахування,

· на другому - діаграму,.

· на третьому – відомість на видачу заробітної плати,

· а на четвертому – відомість на видачу компенсацій на дітей.

· Інші листи будуть тільки заважати, тому їх краще видалити.

· Перейдіть листи з 5 по 16. Для цього клацніть мишею по ярличку листа 5, потім, скориставшись кнопкою перейдіть до ярличку листа 16 і, утримуючи клавішу (Shift), клацніть по ньому мишею. Ярлички листів з 5 по 16 виділяться кольором.

· Видаліть виділені листи, викликавши команду контекстного меню Удалить або скориставшись командою горизонтального меню Правка_Удалить лист.

Тепер відображаються ярлички тільки чотирьох аркушів.

Активний (ярличок виділено кольором) Лист 1. Саме на нього ми і почнемо створювати таблицю.

Створення таблиці

Створіть таблиці самостійно, застосовуючи наступні операції:

• запуск Excel;

• форматування рядка заголовка. Тема розміщений у двох рядках таблиці, запропоновано напівжирний стиль накреслення шрифту, весь текст вирівняний по центру, а "Податки" – по центру виділення;

• зміна ширини стовпця (залежно від обсягу введеної інформації);

• обрамлення таблиці. У даному випадку використано складне обрамлення, коли знята частина рамок. Важливо за запропонованим зразком визначити реальний стан клітинок та виконати відповідне обрамлення, виділяючи різні блоки клітинок;

• встановлення формату числа "грошовий " для клітинок, які містять суми. Можна зробити це до введення даних у таблицю(виділити відповідні поля і встановити для них формат числа "денежный");

• заповнення клітинок стовпця послідовністю чисел 1, 2,...;

• введення формули у верхню клітинку стовпця;

• поширення формули вниз по стовпцю і в деяких випадках вправо по ряду;

• заповнення таблиці текстової та фіксованого числовою інформацією (стовпці "ПІБ", "Заробітня плата ", "Кількість дітей ");

• сортування рядків (спочатку відсортувати за прізвищами в алфавітному порядку, потім за сумами).

№	Прізвище, Ім’я По-батькові	Заробітня плата	Податок	Сума до видачі	К-сть дітей
профс.	пенс.	подох.

Для форматування формул вам напевно знадобиться додаткова інформація. Приймемо профспілковий і пенсійний податки, складовими по 1% від заробітної плати. Зручно ввести формулу в одну клітинку, а потім поширити її на обидва стовпця. Найважливіше не забути про абсолютні посилання, так як і профспілковий і пенсійний податки потрібно брати від заробітної плати, тобто посилатися тільки на стовпець "Заробітня плата". Приблизний вигляд формули:

= $СЗ *1% або = $СЗ*0,01 або = $СЗ*1/100. Після введення формули у комірку D3 її потрібно поширити вниз(простягнувши за маркер виділення) і потім вправо на один стовпчик.

Прибутковий податок підрахуємо за формулою: 12% від заробітної плати за вирахуванням мінімальної заробітної плати та пенсійного податку. Приблизний вигляд формули: = (СЗ-ЕЗ-86)*12% або =(СЗ-ЕЗ-86)*12/100 або =(СЗ-ЕЗ-86)*0,12. Після введення формули у комірку F3, її потрібно поширити вниз.

Для підрахунку Суми до видачі застосуйте формулу, яка обчислює різницю окладу та податків. Приблизний вигляд формули: =СЗ-D3-E3-F3, розміщеної у клітинці G3 і поширеною вниз.

Заповнюйте стовпці "Прізвище, ім'я, по батькові", " Заробітня плата", і "Кількість дітей" після того, як введені всі формули. Результат буде обчислюватися відразу ж після введення даних у клітинку. При бажанні можна скористатися режимом форми для заповнення таблиці.

Після введення всіх даних бажано виконати їх сортування (не забудьте перед сортуванням виділити всі рядки від прізвищ до відомостей про дітей).

В остаточному вигляді таблиця буде відповідати зразку:

№	Прізвище, ім’я, по-батькові	Заробітна плата	Податок	Сума до видачі	К-сть дітей
профс.	пенс.	подох.
	Иванов А-Ф.
	Иванова Е.П.	450 000				396 648
	Китов а В. К	430 000			41 976	379 424
	Котов И.П				35 798
S	Кругло ва АД				18 216
	Леонов И И	560 000	560D		57 420	491 380
	Петров М.В.	348 000
S	Сидоров И.В.					396 648
	Симонов К.Е	349 000			32 353
	Храмов А.К	430 000			41 Э76	379 424
	Чудов АН,	673 000				588 696

Можна ввести рядок для підрахунку загальної суми нарахувань і на цьому закінчити перевірочну роботу і приступити до спільних дій.

Оскільки ми збираємося надалі працювати відразу з декількома листами, має сенс перейменувати їх ярлички відповідно до вмісту. Перейменуємо активний в даний момент лист. Для цього виконайте команду Формат_Лист_Переименовать... і в полі вводу Имя листа введіть нову назву листа, наприклад, "Нарахування ".

Побудова діаграми на основі готової таблиці та розміщення її на новому аркуші робочої книги

Побудуємо діаграму, що відображає нарахування кожного співробітника. Зрозуміло, що потрібно виділити два стовпці таблиці: "Прізвище, ім'я, по-батькові " та "Сума до видачі ". Але ці стовпчики не розташовані поруч, і традиційним способом ми не зможемо їх виділити. Для Excel це не проблема.

Якщо утримувати натиснутою клавішу (Ctrl), то можна одночасно виділяти комірки в різних місцях таблиці.

• Виділіть заповнені даними клітинки таблиці, пов'язані з стовпцями "Прізвище, ім'я, по-батькові " та "Сума до видачі ".

• Запустіть Майстер діаграм одним із способів: або вибравши кнопку Мастер диаграмм панелі інструментів, або команду меню Вставка_Диаграмма….

• Пересуваючись по кроках з Майстром діаграм, виберіть тип діаграми - об'ємна кругова, підтип сьомий (з мітками даних). Приблизний вигляд наведений на малюнку.

• Перейменуйте лист, який містить діаграму з "Лист1 " в "Діаграма ".

• Для того, щоб перевірити, який зв'язок існує між таблицею нарахувань і діаграмою, перейдіть на лист "Нарахування ", в середину таблиці вставте новий рядок (виділіть рядок таблиці і виконайте команду Вставка_Строки). Розповсюдьте на новий рядок формули, заповніть дані на нового співробітника. Тепер перейдіть на лист "Діаграма" і перевірте, як нові дані відбилися на діаграмі - новий співробітник відразу ж внесено в діаграму.

Створення відомості на отримання компенсації на дітей на основі таблиці нарахувань. Посилання на клітинки іншого аркуша робочої книги

• Перейдіть до Лісту 3. Відразу ж перейменуйте його в "Дитячі".

П І Б	Сума	Підпис
Иванов А.Ф.	53 130
Иванова Е.П.
Кругло ва А.Д.
Леонов И.И.
Петров М.В.	53 130
Сидоров И.В.	53 130
Чудов А.Н.

• Ми хочемо підготувати відомість, тому в ній будуть три стовпці: "ПІБ", "Сума" і "Підпис ". Сформуйте заголовки таблиці.

• У графу "ПІБ" потрібно помістити список співробітників, який ми маємо на аркуші "Нарахування ". Можна скопіювати на одному аркуші і вставити на інший, але хотілося б встановити зв'язок між листами (як це виконується для діаграми та листа нарахувань). Для цього на аркуші "Дитячі" помістимо формулу, по якій дані будуть вставлятися з листа "Нарахування ".

• Виділіть клітинку А2 аркуша "Дитячі" і введіть формулу: = Нарахування! ВЗ, де ім'я аркуша визначається знаком оклику, а ВЗ - адреса клітинки, в якій розміщена перша прізвище співробітника на аркуші "Нарахування". Можна набрати формулу з клавіатури, а можна після набору знака рівності перейти на лист "Нарахування", виділити клітинку, яка містить перше прізвище і натиснути (Enter) (не повертаючись до аркушу "Дитячі").

• Перейдіть на лист "Дитячі", перевірте отриману формулу і розповсюдьте її вниз. Список прізвищ співробітників тепер є і на аркуші "Дитячі". Більше того, якщо внести нові дані в таблицю нарахувань, вони відіб'ються і на аркуші "Дитячі". (Треба буде тільки поширити формулу нижче, у випадку необхідності.)

• У графі "Сума"аналогічним чином потрібно розмістити формулу = Нарахування! НЗ*53130, де НЗ адресу першої клітинки на аркуші "Нарахування ", що містить число дітей. Заповніть цю формулу вниз і застосуйте грошовий формат числа.

• Виконайте обрамлення таблиці.

• Для того, щоб список складався тільки зі співробітників, що мають дітей, встановіть фільтр за наявністю дітей (Данные_фильтр_Автофильтр, в відкрившомусь списку"Сумма" виберіть "Настройка..." і встановіть критерій> 0). Приблизний вигляд відомості наведений нижче.

• Залишилося помістити вище таблиці заголовок відомості (для цього може знадобитися вставка додаткових рядків) і можна роздруковувати.