Форматування таблиць в Excel

Лабораторна робота № 9

Тема: Форматування таблиць в Excel.

Мета: сформувати навички з форматування таблиць: форматування комірок, рядків і стовпців, форматування книги; розвивати інформаційну компетентність.

Обладнання: ПЕОМ IBM PC; ОС Windows; Microsoft Office 2013; Microsoft Excel.

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

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

Дії, що стосуються оформлення документа, називаються форматуванням. Необхідні засоби для таких дій знаходяться на панелі Форматування.

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

При форматуванні комірок виконуються такі дії:

1. Встановлення формату чисел.
2. Вирівнювання вмісту комірок.
3. Зміна виду, розміру, кольору і стилю шрифту.
4. Встановлення границь однієї або діапазону комірок.
5. Виділення кольором однієї або діапазону комірок.
6. Встановлення захисту від випадкового пошкодження або зміни вмісту таблиці.
7. Встановлення стилів.

При форматуванні рядків і стовпчиків виконуються такі дії:

•    Зміна висоти рядків і ширини стовпчиків.
•    Розміщення тексту в робочому листі.
•    Сортування рядків. / Фільтрація даних.

При форматуванні робочих листів виконуються такі дії: 

•    Закріплення заголовків робочих листів. 
•    Розбивка вікна робочого листа на області.
•    Групування робочих листів.
•    Розміщення робочих листів в окремих вікнах. 

При форматуванні книги виконуються такі дії:

•    Створення колонтитулів.
•    Встановлення розривів аркушів.
•    Попередній перегляд перед друкуванням.

Форматування клітинок

Щоб встановити потрібний формат, треба виділити область клітинок (окрема клітинка, стовпчик, кілька стовпчиків, рядок, кілька рядків, всю таблиця) і виконати команду «Формат» «Комірки...». З\'явиться діалогове вікно «Формат комірок», в якому натиснути потрібну вкладку: «Число», «Вирівнювання», «Шрифт», «Межа», «Вид» або «Захист». Далі, залежно від формату, який потрібно встановити, у відповідній вкладці виконують команди.

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

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

Для вставки нового стовпчика треба:

1. Клацнути по заголовку того стовпчика, перед яким потрібно вставити новий. (Якщо треба вставити декілька стовпчиків, то треба й виділити декілька стовпчиків).
2. Із меню Вставка вибрати команду «Стовпці».
3. У вибране місце вставиться новий стовпчик. Всі посилання у формулах будуть автоматично від коректовані з урахуванням нових координат.

Аналогічно вставляють і рядки: виділяється рядок, перед яким потрібно вставити новий, після цього із меню Вставка вибирають команду «Рядки».

Для вилучення зайвих стовпчиків чи рядків їх треба виділити і з контекстного меню вибрати команду «Видалити». Крім того, можна з меню Правка вибрати команду «Видалити».

При оформленні даних в таблиці важливо правильно вибрати шрифт – це може значно покращити зовнішній вигляд таблиці.

В Excel можна задати шрифт як для всього робочого аркуша, так і для будь-якого його фрагмента (діапазону, клітинки).

Щоб змінити шрифт потрібного діапазону, треба його виділити (якщо змінюєте шрифт на всьому аркуші, його теж треба виділити) і з меню Формат вибрати команду «Комірки». Відкриється діалогове вікно «Формат комірок» із такими вкладками: «Число», «Вирівнювання», «Шрифт», «Межа», «Вид» та «Захист».

На вкладці «Шрифт» можна встановити такі параметри шрифтів:

Шрифт – тип шрифту. В Excel можна використовувати пропорційні і непропорційні шрифти. У пропорційних шрифтах (Ariel Cyr, Times New Roman) кожному символу відводиться стільки місця, скільки йому дійсно необхідно. В непропорційних шрифтах (Courier New) всі символи мають однакову ширину. Крім того, шрифт може бути із засічками і без них.

Нарис шрифту може бути звичайним, курсивом, жирним та жирним і курсивом.

Розмір шрифту можна вибрати зі списку, або ж ввести з клавіатури у поле « Розмір » навіть проміжні розміри (ті, яких немає у списку, наприклад 11,5 пункту).

Крім того, можна вибрати тип підкреслювання (одинарною чи подвійною лінією), колір та ефекти (верхній чи нижній індекси або закреслення) шрифту.

Можна змінювати колір символів. Наприклад, для тексту, який не треба виводити на друк, можна встановити білий колір.

На вкладці «Межа» можна встановлювати межі клітинки або виділеного діапазону. Межі – це лінії, які утворюють контури клітинки і дозволяють відокремлювати текст або числа. Тип лінії, як і її товщину та колір, можна вибрати із поля «Лінії»: пунктирна, штрихова, одинарна, подвійна і т.д.

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

На вкладці «Вирівнювання» можна вибрати параметри вирівнювання даних по горизонталі (деякі елементи цього вирівнювання винесені у вигляді кнопок на панель форматування) і по вертикалі (по верхньому краю, по нижньому краю, по центру та по висоті). Крім того, у полі «Орієнтація» можна вибрати будь-який кут нахилу відображення даних у клітинці. У полі «Відображення» можна встановити прапорці «Переносити по словам» - для розміщення тексту у декілька рядків однієї клітинки при досягненні правої межі стовпчика.

На вкладці «Вид» можна вибрати фон виділених клітинок (заливку) відтінками сірого кольору, будь-яким кольором та узорами.

Зазвичай таблиця на екрані має сітку, якщо увімкнуто режим відображення сітки, однак під час друкування на папері вона не відображається. Щоб таблиця була відповідним чином розграфлена на папері, треба задати параметри на вкладці «Межі» діалогового вікна «Формат клітинок»: «Формат» «Клітинки» «Межі» або скористатися відповідними кнопками на панелі форматування.

Розглянемо інші вкладки вікна «Формат клітинок». На вкладці «Вирівнювання» задають спосіб написання тексту в клітинці: горизонтально, вертикально, під кутом і режим перенесення тексту по словах (коли потрібно, щоб великий текст був у вузькій клітинці).

На вкладці «Вигляд» можна задати колір клітинок і візерунок. На вкладці «Захист» можна зняти чи задати режими захисту клітинок від несанкціонованих змін і ховання формул. Щоб унеможливити внесення змін до захищених клітинок, захист з зазначенням паролю або без нього треба задати ще командою Сервіс - Захист – Захистити лист. 

Закріплення заголовків. Робочі листи часто мають велику кількість стрічок і стовпців, тому при перегляді нижньої частини листа, заголовок зникає з екрана.

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

Застосування умовного форматування в Excel

Умовне форматування дає змогу застосовувати до клітинок кольори, якщо виконується певна умова, як-от наявність повторюваних значень, значень, які відповідають конкретним критеріям (наприклад, більше 100), або рівних указаному доходу. Це можна зробити, налаштувавши правила виділення клітинок і визначення перших і останніх елементів. Умовне форматування застосовується динамічно й змінюється автоматично разом зі значенням.

Хід роботи

Створити Книгу1-Excel збережіть з назвою Lab_9_KZ. xlsx (каталог Student/…власна папка студента). Кожне завдання виконувати на окремому аркуші книги.

Частина 1. Форматування комірок

1. Скопіювати таблицю з виконаної лабораторної роботи № 8, завдання 1.
2. Вставити її до першого аркуша книги Lab_9_KZ. xlsx
3. Для комірок стовпчика Товар встановити наступний формат клітинок: числовий формат – Общий; вирівнювання по горизонталі – по левому краю, по вертикалі – по центру, відображення – перенос по словам (рис.1).

Рисунок 1 – Форматування комірок

4. Для комірок, що мають числа встановити наступний формат: числовий формат – Денежный, украинский ; вирівнювання по горизонталі та по вертикалі – по центру.

Рисунок 2 – Грошовий формат комірок

5. Змінити формат тексту таблиці: вид тексту, що був за умовчанням на вид GOST type B, розмір на 14 пт, колір – темносиній, стиль шрифту - К.

6. Для діапазону комірок всієї таблиці встановити межі за зразком (рис.3).

7. Виділити жовтим кольором діапазону комірок верхнього рядка таблиці.

8. Встановлення захисту від випадкового пошкодження або зміни вмісту таблиці: меню Рецензирование → вкладка Изменения → Защитить лист → Встановити пароль.

Частина 2. Форматування рядків і стовпчиків

Завдання 1. Змінення ширини стовпця до вказаного значення:

1. Виділіть стовпці {В:D}, які слід змінити.
2. У вкладці Главная → Ячейки → Формат (рис.4).

Рисунок 4 – Форматування ширини стовпчиків

3. У розділі Размер ячейки виберіть пункт Ширина столбца.
4. У полі Ширина столбца введіть значення 20. Натисніть кнопку OK.
5. Аналогічним способом встановити Висоту рядків – 40.

Порада: Щоб швидко встановити ширину стовпця (рядка), клацніть виділений стовпець (або рядок) ПКМ, виберіть пункт Ширина столбца (высота строки), введіть потрібне значення та натисніть кнопку OK.

Або використовуйте Автоподбор.

Або змінюйте ширину (висоту) стовпців (рядків) за допомогою миші: перетягуйте межу стовпця (рядка) доки стовпець (рядок) не набуде потрібної ширини.

Завдання 2. Розміщення тексту в робочому листі. Сортування даних.

1. Створити таблицю за зразком.

Рисунок 5 – Таблиця до завдання 2 (частина 2).

Порада: формат для комірки G2 – Дата. Розміщення тексту в комірці А2 – Формат ячеек → Выравнивание → Ориентация → 90^о. Для даних стовпчика «Кількість» встановіть формат Числовой целый.

2. Виділіть числові дані в стовпці F та натисніть кнопку Увеличить разрядность на панелі інструментів Форматирование. Виконайте ці ж дії за допомогою кнопки Уменьшить разрядность . Обмежтесь двома цифрами після десятичної крапки.
3. Застосуйте вирівнювання тексту за центром у таблиці. Скористайтесь кнопкою Объединение по центру для об’єднання клітин заголовка таблиці.
4. Приховати стовпці: виберіть стовпчик С і натисніть клавішу Ctrl, щоб вибрати додаткові стовпці G та Н, які не суміжні. Клацніть ПКМ вибрані стовпці та натисніть кнопку Скрыть.

Примітка: Подвійна лінія між двома стовпцями – це показник, приховані стовпці. Відобразити приховані стовпці: виділіть суміжні стовпці для прихованих стовпців. Клацніть ПКМ миші вибрані стовпці та натисніть кнопку Відобразити. Або двічі клацніть лінію між двома стовпцями, де є приховані стовпці.

• Щоб швидко відсортувати дані за зростанням, натисніть кнопку  (Сортування від А до Я).
• Щоб швидко відсортувати дані за спаданням, натисніть кнопку  (Сортування від Я до А).

Частина 3. Форматування робочих листів, книги

1. Закріпити заголовки в таблиці 1 (Лист 1).  Вид→Закрепить области→Закрепить верхнюю строку.
2. До таблиці Лист 2 додати колонтитул, я якому зазначити Групу, ПІБ студента, дату: Вставка → Текст →Колонтитули.
3. Перейменувати Лист 1, Лист 2 в Таблиця 1, Таблиця 2.
4. Скопіювати ці таблиці на інші листи та відформатувати на свій смак.
5. Захистити лист 2 та книгу: Рецензирование → Изменения.

Частина 4. Умовне форматування

Позначення повторюваних значень кольором.

На Лист 5 створити таблицю (рис.6).

1. Виберіть діапазон, у якому потрібно відформатувати повторювані значення кольором – стовпець "Ім’я працівника".
2. На вкладці Главная послідовно виберіть елементи Условное форматирование → Правила виделения ячеек → Повторяющиеся значения…(рис.7).

Рисунок 7

3. Виберіть формат із розкривного списку параметрів, а потім натисніть кнопку OK.

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

1. Що таке форматування робочого аркуша?
2. Наведіть необхідні команди (послідовність дій) для того щоб відобразити на екрані вікно «Формат ячеек» (наведіть декілька варіантів).
3. Які параметри форматування можливо застосувати з використанням наступних вкладок вікна «Формат ячеек»: Число, Выравнивание, Шрифт, Границы, Вид?
4. У яких форматах можливо представити числові дані, календарну дату або час?
5. Що таке умовне форматування?
6. Як приховати стовпчики, рядки або робочий лист?
7. Як захистити комірки та робочий лист, як дозволити внесення змін в визначений діапазон комірок робочого аркуша, що захищається?
8. Як додати колонтитули в аркуш MS Excel?
9. Які маніпуляції можна робити з аркушами робочої книги MS Excel?
10. Як приховати (відобразити) аркуш робочої книги MS Excel?
11. Які числові формати застосовуються в MS Excel?
12. Як очищати вміст комірок і як видаляти комірки?
13. Як можна розділити аркуш робочої книги на частини?
14. Як можна вирівняти інформацію в комірках?
15. Як об'єднати (розділити комірки)?

Висновок ______________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

Відповіді на контрольні запитання:

___________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________