Багато людей, роками використовуючи Microsoft Excel, навіть не підозрюють про можливості програми, що спрощують роботу з таблицями. У цій статті ми розглянемо кілька особливо примітних функцій:
угруповання даних
Коли ви готуєте каталог товарів з цінами, було б непогано потурбуватися про зручність його використання. Велика кількість позицій на одному аркуші змушує використовувати пошук, але що, якщо користувач тільки робить вибір і не має уявлення про найменування? В Інтернет-каталогах проблема вирішується створенням груп товарів. Так чому б і в книзі 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).
- Виділяємо комірки з даними. Виділяються саме осередку, а не рядки і стовпці, інакше нічого не вийде.
- Копіюємо їх в буфер обміну, натисніть сполучення клавіш
або будь-яким іншим способом. - Переходимо до порожнього аркушу або вільного простору поточного листа. Важливе зауваження: вставляти поверх поточних даних не можна!
- Вставляємо дані комбінацією клавіш
і в меню параметрів вставки вибираємо варіант Транспонувати (див. рис. 11). Як варіант, ви можете використовувати меню Вставити зі вкладки Головна (див. Рис. 12).
Малюнок 11 - Вставка з Транспонированием
Малюнок 12 - Транспонування з головного меню
Ось і все, поворот таблиці виконаний (див. Рис. 13). При цьому форматування зберігається, а формули змінюються відповідно до нового положення осередків - ніякої рутинної роботи не буде потрібно.
Малюнок 13 - Результат після повороту
показ формул
Іноді виникає ситуація, коли ви не можете знайти потрібну формулу серед великої кількості осередків, або просто не знаєте, що і де слід шукати. В цьому випадку вам знадобиться можливість вивести на лист не результат обчислень, а вихідні формули.
Натисніть кнопку Показати формули на вкладці Формули (див. Рис. 14), щоб змінити уявлення даних на аркуші (див. Рис. 15).
Малюнок 14 - Кнопка «Показати формули»
Малюнок 15 - Тепер на аркуші видно формули, а не результати розрахунку
Малюнок 16 - Залежності осередку показані стрілками
Ховаються залежності натисканням кнопки Прибрати стрілки.
Перенесення рядків в осередках
Досить часто в книгах Excel зустрічаються довгі написи, що не поміщаються в комірку по ширині (див. Рис. 17). Можна, звичайно, розсунути стовпець, але не завжди цей варіант прийнятний.
Малюнок 17 - Написи не поміщаються в осередку
Виділіть комірки з довгими написами і натисніть кнопку Перенесення тексту на Головній вкладці (див. Рис. 18), щоб перейти до багаторядкового режиму (див. Рис. 19).
Малюнок 18 - Кнопка «Перенесення тексту»
Малюнок 19 - багаторядкова відображення тексту
Поворот тексту в осередку
В залежності від своїх уподобань ви можете піти двома шляхами:
- Спочатку створити напис, а потім її повернути.
- Налаштувати поворот написи в осередку, а потім ввести текст.
Варіанти різняться незначно, тому розглянемо тільки один з них. Для початку я об'єднав шість рядків в одну за допомогою кнопки Об'єднати і помістити в центрі на Головній вкладці (див. Рис. 20) і ввів узагальнюючу напис (див. Рис. 21).
Малюнок 20 - Кнопка об'єднання осередків
Малюнок 21 - Спочатку створюємо горизонтальну підпис
Далі знову ж на Головній вкладці клацаємо по кнопці напрямку тексту і в списку, що розкривається вибираємо пункт Повернути текст вгору (див. Рис. 22).
Малюнок 22 - Кнопка повороту тексту
Можна додатково зменшити ширину шпальти (див. Рис. 23). Готово!
Малюнок 23 - Вертикальний текст комірки
Якщо є таке бажання, кут повороту тексту ви можете задати вручну. У тому ж списку (див. Рис. 22) виберіть пункт Формат вирівнювання комірки і в віконці задайте довільний кут і вирівнювання (див. Рис. 24).
Малюнок 24 - Задаємо довільний кут повороту тексту
Форматування осередків за умовою
Перше, що потрібно зробити - виділити комірки. Далі, на Головній вкладці натисніть кнопку Умовне форматування і виберіть одну із заготовок (див. Рис. 25). Результат буде відображатися на аркуші відразу, тому вам не доведеться довго перебирати варіанти.
Малюнок 25 - Вибираємо заготівлю умовного форматування
Гістограми виглядають досить цікаво і добре відображають суть інформації про ціну - чим вона вища, тим довше відрізок.
Кольорові шкали і набори значків можна використовувати для індикації різних станів, наприклад, переходи від критичних витрат до допустимим (див. Рис. 26).
Малюнок 26 - Колірна шкала від червоного до зеленого з проміжним жовтим
Ви можете комбінувати гістограми, шкали і значки в одному діапазоні осередків. Наприклад, гістограми і значки на малюнку 27 показують допустиму і надмірно низьку продуктивність пристроїв.
Малюнок 27 - Гістограма і набір значків відображають продуктивність деяких умовних пристроїв
Щоб видалити умовне форматування осередків, виділіть їх і в меню умовного форматування виберіть пункт Видалити правила з виділених осередків (див. Рис. 28).
Малюнок 28 - Видаляємо правила умовного форматування
Використання фільтрів
Фільтри дозволяють швидко знаходити потрібну інформацію у великій таблиці і представляти її в компактному вигляді. Наприклад, з довгого списку книг можна вибрати твори Гоголя, а з прайс-листа комп'ютерного магазину - процесори Intel.
Як і більшість інших операцій, фільтр вимагає виділення осередків. Однак виділяти всю таблицю з даними не потрібно, достатньо зазначити рядки над потрібними стовпцями даних. Це значно збільшує зручність від використання фільтрів.
Після того, як осередки виділені, на вкладці Основне натисніть кнопку Сортування і фільтр і виберіть пункт Фільтр (див. Рис. 29).
Малюнок 29 - Створюємо фільтри
Тепер осередки перетворяться в списки, що розкриваються, де ви можете задати параметри вибірки. Наприклад, ми шукаємо всі згадки про Intel в стовпці Найменування товару. Для цього вибираємо текстовий фільтр Містить (див. Рис. 30).
Малюнок 30 - Створюємо текстовий фільтр
Далі, вписуємо шукане слово у відповідне поле вікна (див. Рис. 31) і натискаємо кнопку ОК. У таблиці будуть показані тільки ті рядки, в яких міститься потрібна нам інформація.
Малюнок 31 - Створюємо фільтр по слову
Втім, набагато швидше домогтися того ж ефекту, вписавши слово в поле Пошук контекстного меню, показаного на малюнку 30. Навіщо тоді викликати додаткове вікно? Воно стане в нагоді в тому випадку, якщо ви хочете вказати декілька умов вибірки або вибрати інші параметри фільтрації (не містить. Починається з ..., закінчується на ...).
Для числових даних доступні інші параметри (див. Рис. 32). Наприклад, можна вибрати 10 найбільших або 7 найменших значень (кількість налаштовується).
Малюнок 32 - Числові фільтри
Фільтри Excel надають досить багаті можливості, які можна порівняти з вибіркою запитом SELECT в системах управління базами даних (СКБД).
Відображення інформаційних кривих
Щоб створити інфокрівую, натисніть на одну з кнопок в блоці Інфокрівие на вкладці Вставка (див. Рис. 34), а потім задайте діапазон комірок для побудови.
Малюнок 34 - Вставка інфокрівой