Короткі теоретичні відомості

Працюючи з Excel, завжди слід пам'ятати, що форматування інформації служить не тільки для надання їй більш красивого і зручного для сприйняття вигляду. Все, що ми бачимо в комірці, визначається не тільки вмістом комірок, але і їхнім форматом. Наприклад, число в комірці може виглядати як 6, хоча його дійсне значення дорівнює 5,72. Просто в даному форматі не передбачений виведення дробової частини числа, і ми бачимо результат округлення.

Наведемо типовий приклад проблем із форматом, що часто збиває з толку новачків. Припустимо, що при введенні числа 4,5 ви помилково використали крапку замість коми: 4.5. Таке значення сприймається Excel як дата - четверте травня. Зрозумівши це, ви вводите правильне значення 4,5, але на ваше здивування тепер замість 4,5 у цій комірці ви бачите 04.01.1900 12:00:00 ...

Пояснюється все дуже просто. Справа в тім, що коли ви помилково ввели 4.5, в комірці автоматично встановився формат дати. Після виправлення на 4,5 в комірці значення змінилося, але формат дати залишився. Значення що спостерігається - 04.01.1900 12:00:00 - це і є 4,5, але тільки у форматі дати (4,5 доби, відраховуючи від 0 годин 1 січня 1900 р.). Таким чином, усе, що потрібно для виправлення помилки - це замінити в цій комірці формат дати загальним або числовим форматом.

Самостійно вивчіть теми:

1. Форматування даних.

2. Умовне форматування.

3. Закріплення областей.

Контрольні питання

1. Як задається тип формату даних (загальний, числовий, грошовий, дата тощо)?

2. Як задати кількість відображуваних знаків у дробовій частині числа?

3. Як відображається число і текст, якщо розмір комірки для них є недостатнім?

Якими способами можна регулювати розміри комірок?

4. Як можна об'єднати декілька комірок в одну і скасувати це об'єднання?

5. Як можна задати вирівнювання й орієнтацію тексту?

6. Як задати спосіб обрамлення комірок (сітку)?

7. Що таке копіювання формату і як воно здійснюється?

8. Що таке умовне форматування і як воно здійснюється?

9. Що таке закріплення областей, як і для чого воно виконується?

Завдання для виконання

1. Заповніть робочий аркуш відповідно до рис. 3, обравши приблизно наступні значення: Ч1 = 2, Ч2=20%, Ч3=15%, Ч4=10%, Ч8 =10%, Ч9=20. Інші числові значення і найменування товарів (не менше 5 позицій) виберіть на свій розсуд.

2. Надайте ім'я Авіатариф комірці, що містить число Ч1.

3. Розрахункові значення обчисліть по таких формулах: Ф1=Ч5*Ч7; Ф2=Ч6*Ч7; Ф3=Ф2*Ч1; Ф4=(Ф1+Ф3)*Ч8; Ф5=(Ф1+Ф3+Ф4)*20%; Ф6=Ч9*Ф5/Ф14; Ф7=Ч10*Ф2/Ф11; Ф8=(Ф1+Ф3+Ф4+Ф5+Ф6+Ф7)/ Ч7.

Ф9 обчислюється як значення Ф8 у поточному рядку, збільшене у (1+Ч2), (1+Ч3) або (1+Ч4) разом, у залежності від стовпця, у якому знаходиться формула Ф9. Формула Ф9 обов'язково повинна містити змішані посилання. Це дозволить увести формулу Ф9 тільки в одну комірку, а потім за допомогою маркера заповнення, скопіювати її в інші рядки і стовпці.

Формули Ф10 - Ф14 обчислюються як суми значень у відповідних стовпцях.

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

5. Виконайте закріплення областей так, щоб при прокрутці таблиці її шапка і найменування товарів завжди залишалися в полі зору.

6. Виділіть комірки таблиці і виберіть у меню Формат опцію Автоформат, а потім один із варіантів форматування таблиці.

7. Завершіть форматування таблиці вручну, виділивши жирним шрифтом рядок з підсумковими даними й відобразивши сітку таблиці.

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

9. Збережіть файл з даним робочим аркушем на диску.

10. Виконаєте аналогічні дії з форматуванню над робочим аркушем, створеним у лабораторній роботі №1.

Лабораторна робота № 3
 Дані у форматі дати і часу. Використання функцій у формулах

Короткі теоретичні відомості

У Excel усі дати, починаючи з 1 січня 1900 р., пронумеровані послідовністю натуральних чисел:  1.01.1900 = 1, 2.01.1900 = 2, 1.02.1900 = 32, 01.01.2000 = 36526,  і т.д. Тому додавання до дати числа Х означає збільшення дати на Х днів, а різниця дат дає число днів між цими датами, наприклад: 29.08.97 + 4 = 2.09.97, 2.01.98 - 30.12.97 = 3.

Якщо цілим числам відповідають дата, то дробам - час доби. Оскільки година - це 1/24 доби, то: 0,5 = 12:00, 0,25 = 6:00, 2,75 = 2.01.1900 18:00.  Таким чином, дата і час розглядаються в Excel як звичайне число, тільки подані в специфічному форматі. Отже, арифметичні операції можуть виконуватись також з даними типу дати і часу, наприклад: 1:14:30 + 2:50:30 = 4:05:00. Не слід забувати, що якщо результат цього додавання перевести в числовий формат, то одержимо час, виражений у добах: 4:05:00 = 0,170138889 доби. Результат у годинах одержимо, примноживши останнє число на 24: 0,170138889*24 = 4,083333333 години.

Від’ємні значення не можуть бути подані у форматі дати і часу.

Самостійно вивчіть тему:

Використання функцій у формулах.

Контрольні питання

1. Як у Excel представляються дати і час?

2. Які дії можна виконувати з даними у форматі дати і часу?

3. Як використовуються функції у формулах?

4. Які функції Excel ви знаєте?

Завдання для виконання

1. Відповідно до рис. 5 складіть таблицю для розрахунку дат "1000-денних ювілеїв":

· в комірку Д1 введіть дату вашого народження;

· в одну з комірок уведіть формулу Ф1, що обчислюється як вміст розташованої вище комірки плюс 1000;

· за допомогою маркера заповнення скопіюйте формулу Ф1 у всі інші комірки таблиці;

· встановіть в лівому стовпці числовий формат, а в правому - формат дати.

2. Відповідно до рис. 4 заповніть робочий аркуш розрахунку вартості прокату устаткування, де:

· Т1, Ч1, ДЧ1, ДЧ2 - довільні найменування (не менше 6 позицій), числові значення і значення дати і часу[2] .

· Розрахункові формули: Ф1=(ДЧ2-ДЧ1)*24; Ф2=Ч1*Ф1;

· Ф3 розраховується як округлене до цілого числа значення ДЧ2 плюс 10. Тут передбачається, що оплата за прокат повинна надійти не пізніше 10 днів після повернення устаткування. Округлення здійснюється для того, щоб при нарахуванні пені враховувати тільки дні, а не години прострочення платежу.

· Дату фактичної оплати Д1 у деяких позиціях уведіть із перевищенням терміну, розрахованого в графі "Сплатити до", в інших - без.

· Для розрахунку пені ви повинні застосувати у формулі Ф4 функцію ЕСЛИ() так, щоб при своєчасній оплаті пеня дорівнювала нулю, а у випадку прострочення - Ф2*(Д1-Ф3)*1%.

· Ф5=Ф2+Ф4, а Ф6, Ф7 і Ф8 розраховуються як суми значень у відповідних стовпцях.

3. Виконайте закріплення областей так, щоб шапка таблиці і найменування устаткування завжди залишалися в поле зору.

4. Робітники листи обох таблиць отформатируйте і збережете у файлі на диску.

Лабораторна робота № 4
 Фільтрація даних