Приклад створення таблиці в excel. Робота з осередками. Заповнення і форматування. Закріплення областей листа

Якщо ви ніколи раніше не використовували табличний процесор для створення документів, радимо прочитати наше керівництво Ексель (Excel) для чайників.

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

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

До речі, щоб ефективніше працювати з таблицями Exel можете ознайомитися з нашим матеріалом.

Робота з осередками. Заповнення і форматування

Перш ніж приступати до конкретних дій, необхідно розібратися з базовим елементом будь-якого. Файл Ексель складається з одного або декількох листів, розграфлених на дрібні осередки.

Осередок - це базовий компонент будь-якого екселевскій звіту, таблиці або графіка. У кожній клітинці міститься один блок інформації. Це може бути число, дата, грошова сума, одиниця виміру або інший формат даних.

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

Мал. 1 - приклад заповнення осередків

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

Виберемо осередок «Частка акцій». Адреса її розташування - А3. Ця інформація вказана в панелі, властивостей. Також ми можемо побачити вміст. Формул у цій клітини немає, тому вони не показуються.

Більше властивостей комірки і функцій, які можна задіяти по відношенню до неї, доступні в контекстному меню. Натисніть на клітинку правою клавішею маніпулятора. Відкриється меню, за допомогою якого можна відформатувати осередок, провести аналіз вмісту, привласнити інше значення і інші дії.

Мал. 2 - контекстне меню комірки і її основні властивості

Сортування даних

Часто користувачі стикаються із завданням сортування даних на аркуші в Ексель. Ця функція допомагає швидко вибрати і переглянути тільки потрібні дані з усієї таблиці.

Перед вами вже (як її створити розберемося далі в статті). Уявіть, що вам потрібно впорядкувати дані за січень по зростанню. Як би ви це зробили? Банальне передрук таблиці - це зайва робота, до того ж, якщо вона об'ємна, робити цього ніхто не буде.

Для сортування в Excel є спеціально відведена функція. Від користувача потрібно тільки:

• Виділити таблицю або блок інформації;
• Відкрити кладку «Дані»;
• Клікнути на іконку «Сортування»;

Мал. 3 - вкладка «Дані»

• У віконці виберіть колонку таблиці, над якою будемо проводити дії (Січень).
• Далі тип сортування (ми виконуємо угруповання за значенням) і, нарешті, порядок - по зростанню.
• Підтвердіть дію, натиснувши на «ОК».

Мал. 4 - настройка параметрів сортування

Відбудеться автоматичне сортування даних:

Мал. 5 - результат сортування цифр в стовпці «Січ»

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

математичні розрахунки

Головна перевага Excel - можливість автоматичного проведення розрахунків в процесі заповнення таблиці. Наприклад, у нас є два відділення зі значеннями 2 і 17. Як в третій осередок вписати їх результат, не роблячи розрахунки самостійно?

Для цього, вам необхідно клікнути на третій осередок, в яку буде вписаний кінцевий результат обчислень. Потім натисніть на значок функції f (x), як показано на малюнку нижче. У віконці виберіть дію, яке хочете застосувати. СУМ - це сума, СРЗНАЧ - середнє значення і так далі. Повний список  функцій і їх найменувань в редакторі Excel можна знайти на офіційному сайті компанії Microsoft.

Нам потрібно знайти суму двох осередків, тому натискаємо на «СУМ».

Мал. 6 - вибір функції «СУМ»

У вікні аргументів функції є два поля: «Число 1» і «Число 2». Виберіть перше поле і клацніть мишкою на осередок з цифрою «2». Її адреса запишеться в рядок аргументу. Натисніть на «Число 2» і натисніть на клітинку з цифрою «17». Потім підтвердіть дію і закрийте вікно. Якщо необхідно виконати математичні дії з трьома або більшою кількістю клітинок, просто продовжуйте вводити значення аргументів в поля «Кількість 3», «Число 4» і так далі.

Якщо в подальшому значення сумміруемих осередків буде змінюватися, їх сума буде оновлюватися автоматично.

Мал. 7 - результат виконання підрахунків

створення таблиць

У екселевскій таблицях можна зберігати будь-які дані. За допомогою функції швидкого налаштування і форматування, в редакторі дуже просто організувати систему контролю особистого бюджету, список витрат, цифрові дані для звітності та інше.

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

Щоб створити таблицю, коректний:

• Відкрийте вкладку «Вставка». У лівій частині панелі опцій виберіть пункт «Таблиця». Якщо вам необхідно провести зведення будь-яких даних, вибирайте пункт « Зведена таблиця»;
• За допомогою мишки виділіть місце на аркуші, яке буде відведено для таблиці. А також ви можете ввести розташування даних у вікно створення елемента;
• Натисніть ОК, щоб підтвердити дію.

Мал. 8 - створення стандартної таблиці

Щоб відформатувати зовнішній вигляд вийшла таблички, відкрийте вміст конструктора і в поле «Стиль» клікніть на вподобаний шаблон. При бажанні, можна створити власний вид з іншого кольоровою гамою і виділенням осередків.

Мал. 9 - форматування таблиці

Результат заповнення таблиці даними:

Мал. 10 - заповнена таблиця

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

угруповання даних

Коли ви готуєте каталог товарів з цінами, було б непогано потурбуватися про зручність його використання. Велика кількість позицій на одному аркуші змушує використовувати пошук, але що, якщо користувач тільки робить вибір і не має уявлення про найменування? В Інтернет-каталогах проблема вирішується створенням груп товарів. Так чому б і в книзі Excel не зробити так само?

Організувати угруповання досить просто. Виділіть декілька рядків і натисніть кнопку групувати  на вкладці дані  (Див. Рис. 1).

Малюнок 1 - Кнопка угруповання

Потім вкажіть тип угруповання - по рядках  (Див. Рис. 2).

Малюнок 2 - Вибір типу угруповання

У підсумку ми отримуємо ... не то, що нам потрібно. Рядки товарів об'єдналися в групу, зазначену під ними (див. Рис. 3). У каталогах зазвичай спочатку йде заголовок, а потім вміст.

Малюнок 3 - Групування рядків «вниз»

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

Щоб групувати рядки «вгору» потрібно змінити одну настройку. на вкладці дані  натисніть на маленьку стрілку в нижньому правому куті розділу структура  (Див. Рис. 4).

Малюнок 4 - Кнопка, що відповідає за виведення вікна налаштувань структури

У вікні налаштувань зніміть прапорець з пункту Підсумки в рядках під даними(Див. Рис. 5) і натисніть кнопку ОК.

Малюнок 5 - Вікно налаштувань структури

Всі групи, які ви встигли створити, автоматично зміняться на «верхній» тип. Зрозуміло, встановлений параметр вплине і на подальшу поведінку програми. Однак знімати цей прапорець вам доведеться для кожного  нового аркуша і кожної нової книги Excel, тому що розробники не передбачили «глобальної» установки типу угруповання. Точно також можна використовувати різні типи груп в межах однієї сторінки.

Після того, як ви розподілили товари за категоріями, можна зібрати категорії в більш великі розділи. Всього передбачено до дев'яти рівнів угруповання.

Незручність при використанні даної функції полягає в необхідності натискати кнопку ОК  Тоді з'явиться вікно, а зібрати не пов'язані діапазони за один підхід не вдасться.

Малюнок 6 - Багаторівнева структура каталогу в Excel

Тепер ви зможете розкривати і закривати частини каталогу, клацаючи по плюсів і мінусів в лівій колонці (див. Рис. 6). Щоб розгорнути весь рівень, натисніть на одну з цифр у верхній частині.

Щоб вивести рядки на більш високий рівень  ієрархії, скористайтеся кнопкою розгрупувати  вкладки дані. Повністю позбавитися від угруповання можна за допомогою пункту меню видалити структуру  (Див. Рис. 7). Будьте уважні, скасувати дію неможливо!

Малюнок 7 - Знімаємо угруповання рядків

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

Досить часто при роботі з таблицями Excel виникає необхідність закріпити деякі області листа. Там можуть розташовуватися, наприклад, заголовки рядків / стовпців, логотип компанії або інша інформація.

Якщо ви закріплюєте перший рядок або перший стовпець, то все дуже просто. Відкрийте вкладку вид  і в спадному меню закріпити області  виберіть відповідно пункти Закріпити верхній рядок  або Закріпити перший стовпець  (Див. Рис. 8). Однак одночасно і рядок, і стовпець таким чином «заморозити» не вдасться.

Малюнок 8 - Закріплюємо рядок або стовпець

Щоб зняти закріплення, виберіть в тому ж меню пункт Зняти закріплення областей  (Пункт замінює собою рядок закріпити області, Якщо на сторінці застосована «заморозка»).

А ось закріплення декількох рядків або області з рядків і стовпців здійснюється не так прозоро. Ви виділяєте три рядки, клацаєте по пункту закріпити області, І ... Excel «заморожує» тільки дві. Чому так? Можливий ще більш поганий варіант, коли області закріплюються непередбачуваним чином (наприклад, ви виділяєте два рядки, а програма ставить кордону після п'ятнадцятої). Але не будемо списувати це на недогляд розробників, тому що єдиний правильний варіант використання даної функції виглядає по-іншому.

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

Малюнок 9 - Закріплюємо область з рядків і стовпців

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

  Поворот листа (заміна рядків на стовпці і навпаки)

Уявіть таку ситуацію: ви кілька годин працювали над набором таблиці в Excel і раптом зрозуміли, що невірно спроектували структуру - заголовки стовпців слід було б розписати по рядках або рядки по стовпчиках (це не має значення). Набирати все вручну заново? Нізащо! В Excel передбачена функція, що дозволяє здійснити «поворот» листа на 90 градусів, перемістивши таким чином вміст рядків в стовпці.

Малюнок 10 - Вихідна таблиця

Отже, ми маємо деяку таблицю, яку потрібно «повернути» (див. Рис. 10).

1. Виділяємо комірки з даними. Виділяються саме осередку, а не рядки і стовпці, інакше нічого не вийде.
2. Копіюємо їх в буфер обміну, натисніть сполучення клавіш   або будь-яким іншим способом.
3. Переходимо до порожнього аркушу або вільного простору поточного листа. Важливе зауваження:  вставляти поверх поточних даних не можна!
4. Вставляємо дані комбінацією клавіш   і в меню параметрів вставки вибираємо варіант транспонувати  (Див. Рис. 11). Як варіант, ви можете використовувати меню вставити  зі вкладки Головна  (Див. Рис. 12).

Малюнок 11 - Вставка з Транспонированием

Малюнок 12 - Транспонування з головного меню

Ось і все, поворот таблиці виконаний (див. Рис. 13). При цьому форматування зберігається, а формули змінюються відповідно до нового положення осередків - ніякої рутинної роботи не буде потрібно.

Малюнок 13 - Результат після повороту

  показ формул

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

Натисніть кнопку Показати формули  на вкладці формули  (Див. Рис. 14), щоб змінити уявлення даних на аркуші (див. Рис. 15).

Малюнок 14 - Кнопка «Показати формули»

Малюнок 15 - Тепер на аркуші видно формули, а не результати розрахунку

Якщо вам важко зорієнтуватися за адресами осередків, що відображається в рядку формул, натисніть кнопку впливають осередки  зі вкладки формули  (Див. Рис. 14). Залежно будуть показані стрілками (див. Рис. 16). Для використання цієї функції спочатку слід виділити одну  осередок.

Малюнок 16 - Залежності осередку показані стрілками

Ховаються залежності натисканням кнопки Прибрати стрілки.

  Перенесення рядків в осередках

Досить часто в книгах Excel зустрічаються довгі написи, що не поміщаються в комірку по ширині (див. Рис. 17). Можна, звичайно, розсунути стовпець, але не завжди цей варіант прийнятний.

Малюнок 17 - Написи не поміщаються в осередку

Виділіть комірки з довгими написами і натисніть кнопку перенесення тексту  на головною  вкладці (див. рис. 18), щоб перейти до багаторядкового режиму (див. рис. 19).

Малюнок 18 - Кнопка «Перенесення тексту»

Малюнок 19 - багаторядкова відображення тексту

  Поворот тексту в осередку

Напевно ви стикалися з ситуацією, коли текст в осередках потрібно було розташувати не по горизонталі, а по вертикалі. Наприклад, щоб підписати групу рядків або вузькі стовпчики. В Excel 2010 є засоби, що дозволяють повернути текст в осередках.

В залежності від своїх уподобань ви можете піти двома шляхами:

1. Спочатку створити напис, а потім її повернути.
2. Налаштувати поворот написи в осередку, а потім ввести текст.

Варіанти різняться незначно, тому розглянемо тільки один з них. Для початку я об'єднав шість рядків в одну за допомогою кнопки Об'єднати і помістити в центрі  на головною  вкладці (див. рис. 20) і ввів узагальнюючу напис (див. рис. 21).

Малюнок 20 - Кнопка об'єднання осередків

Малюнок 21 - Спочатку створюємо горизонтальну підпис

Малюнок 22 - Кнопка повороту тексту

Можна додатково зменшити ширину шпальти (див. Рис. 23). Готово!

Малюнок 23 - Вертикальний текст комірки

Якщо є таке бажання, кут повороту тексту ви можете задати вручну. У тому ж списку (див. Рис. 22) виберіть пункт Формат вирівнювання комірки  і в віконці задайте довільний кут і вирівнювання (див. рис. 24).

Малюнок 24 - Задаємо довільний кут повороту тексту

  Форматування осередків за умовою

Можливості умовного форматування з'явилися в Excel вже давно, але до версії 2010 року було значно вдосконалено. Можливо, вам навіть не доведеться розбиратися в тонкощах створення правил, тому що розробники передбачили безліч заготовок. Давайте подивимося, як користуватися умовним форматуванням в Excel 2010.

Перше, що потрібно зробити - виділити комірки. Далі, на головною  вкладці натисніть кнопку Умовне форматування  і виберіть одну із заготовок (див. рис. 25). Результат буде відображатися на аркуші відразу, тому вам не доведеться довго перебирати варіанти.

Малюнок 25 - Вибираємо заготівлю умовного форматування

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

Кольорові шкали і набори значків можна використовувати для індикації різних станів, наприклад, переходи від критичних витрат до допустимим (див. Рис. 26).

Малюнок 26 - Колірна шкала від червоного до зеленого з проміжним жовтим

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

Малюнок 27 - Гістограма і набір значків відображають продуктивність деяких умовних пристроїв

Щоб видалити умовне форматування осередків, виділіть їх і в меню умовного форматування виберіть пункт Видалити правила з виділених осередків  (Див. Рис. 28).

Малюнок 28 - Видаляємо правила умовного форматування

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

  Використання фільтрів

Фільтри дозволяють швидко знаходити потрібну інформацію у великій таблиці і представляти її в компактному вигляді. Наприклад, з довгого списку книг можна вибрати твори Гоголя, а з прайс-листа комп'ютерного магазину - процесори Intel.

Як і більшість інших операцій, фільтр вимагає виділення осередків. Однак виділяти всю таблицю з даними не потрібно, достатньо зазначити рядки над потрібними стовпцями даних. Це значно збільшує зручність від використання фільтрів.

Після того, як осередки виділені, на вкладці Головна  натисніть кнопку Сортування і фільтр  і виберіть пункт Фільтр  (Див. Рис. 29).

Малюнок 29 - Створюємо фільтри

Тепер осередки перетворяться в списки, що розкриваються, де ви можете задати параметри вибірки. Наприклад, ми шукаємо всі згадки про Intel в стовпці Найменування товару. Для цього вибираємо текстовий фільтр містить  (Див. Рис. 30).

Малюнок 30 - Створюємо текстовий фільтр

Малюнок 31 - Створюємо фільтр по слову

Втім, набагато швидше домогтися того ж ефекту, вписавши слово в поле Пошук контекстного меню, Показаного на малюнку 30. Навіщо тоді викликати додаткове вікно? Воно стане в нагоді в тому випадку, якщо ви хочете вказати декілька умов вибірки або вибрати інші параметри фільтрації ( не містить, починається з ..., закінчується на ...).

Для числових даних доступні інші параметри (див. Рис. 32). Наприклад, можна вибрати 10 найбільших або 7 найменших значень (кількість налаштовується).

Малюнок 32 - Числові фільтри

Фільтри Excel надають досить багаті можливості, які можна порівняти з вибіркою запитом SELECT в системах управління базами даних (СКБД).

  Відображення інформаційних кривих

Інформаційні криві (інфокрівие) - нововведення в Excel 2010. Ця функція дозволяє відобразити динаміку зміни числових параметрів прямо в осередку, не вдаючись до побудови діаграми. Зміни в числах будуть відразу ж показані на мікрографіке.

Малюнок 33 - Інфокрівая Excel 2010

Щоб створити інфокрівую, натисніть на одну з кнопок в блоці Інфокрівие  на вкладці вставка  (Див. Рис. 34), а потім задайте діапазон комірок для побудови.

Малюнок 34 - Вставка інфокрівой

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

  висновок

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