Прості, але ефективні прийоми для прискореної роботи в Excel

Список, що випадає в клітинки

Якщо в будь-яку клітинку передбачається введення певних значень з дозволеного набору (наприклад, тільки «так» і «ні» або тільки зі списку відділів компанії і т. д.), То це можна легко організувати за допомогою списку:

1. Виділіть клітинку (або діапазон комірок), в яких повинно бути таке обмеження.
2. Натисніть кнопку «Перевірка даних» на вкладці «Дані» (Data - Validation).
3. У випадаючому списку «Тип» (Allow) виберіть варіант «Список» (List).
4. В поле «Джерело» (Source) задайте діапазон, що містить еталонні варіанти елементів, які і будуть згодом випадати при введенні.

Просунуті трюки з тієї ж серії: список, що випадає з наповненням, пов'язані списки, що випадають, що випадає список з фотографією і т. д.

Швидке додавання нових даних в діаграму

Миттєве заповнення (Flash Fill)

Для Excel 2013 та новіших. Припустимо, що у вас є список повних ПІБ (Іванов Іван Іванович), які вам треба перетворити в скорочені (Іванов І.І.). Щоб виконати таке перетворення, потрібно просто почати писати бажаний текст в сусідньому стовпці вручну. Excel спробує вгадати наші дії і виконає подальшу обробку автоматично. Залишиться тільки натиснути клавішу Enter для підтвердження, і всі імена будуть перетворені миттєво.

Подібним чином можна отримувати імена з email'ів, склеювати ПІБ з фрагментів і т.д.

Копіювання без порушення форматів

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

Якщо вибрати опцію «Копіювати тільки значення» (Fill Without Formatting), то Microsoft Excel скопіює вашу формулу без формату і не буде псувати оформлення.

Відображення даних з таблиці Excel на мапі

Для цього потрібно перейти в «Магазин додатків» (Office Store) на вкладці «Вставка» (Insert) і встановити звідти плагін Bing Maps. Після додавання модуля його можна вибрати в списку, що випадає «Особисте» (My Apps) на вкладці «Вставка» (Insert) і помістити на ваш робочий лист. Залишиться виділити ваші дані і натиснути на кнопку Show Locations в модулі карти, щоб побачити все на ній.

При бажанні в налаштуваннях плагіна можна вибрати тип діаграми і колір для відображення.

Швидкий перехід до потрібного листа

Якщо у вашій книзі кількість робочих листів перевалило за 10, то орієнтуватися в них стає важкувато. Клацніть правою кнопкою миші по будь-якій з кнопок прокрутки ярличків листів в лівому нижньому кутку екрану.

З'явиться зміст, і на будь-який потрібний лист можна буде перейти миттєво.

Перетворення рядків у стовпці і назад

Якщо вам коли-небудь доводилося руками перекладати клітинки з рядків в стовпці, то ви оціните наступний трюк:

1. Виділіть діапазон.
2. Скопіюйте його (Ctrl + C) або, натиснувши на праву кнопку миші, виберіть «Копіювати» (Copy).
3. Клацніть правою кнопкою миші по комірці, куди хочете вставити дані, і виберіть в контекстному меню один з варіантів спеціальної вставки - значок «Транспонувати» (Transpose).

У старих версіях Excel немає такого значка, але можна вирішити проблему за допомогою спеціальної вставки (Ctrl + Alt + V) і виберіть пункт «Транспонувати» (Transpose).

«Розумна» таблиця

Якщо виділити діапазон з даними і на вкладці «Головна» натиснути «Форматувати як таблицю» (Home - Format as Table), то наш список буде перетворений в «розумну» таблицю, яка (крім модної смугастої розмальовки) вміє багато корисного:

1.  Автоматично розтягуватися при дописуванні до неї нових рядків або стовпців.

2.  Введені формули автоматом будуть копіюватися на весь стовпець.

3.  Шапка такої таблиці автоматично закріплюється при прокручуванні, і в ній включаються кнопки фільтра для відбору і сортування.

4.  На вкладці «Конструктор» (Design) в таку таблицю можна додати рядок підсумків з автоматичним обчисленням.

Спарлайний

Міні-діаграми - це намальовані прямо в осередках мініатюрні діаграми, наочно відображають динаміку наших даних. Щоб їх створити, натисніть кнопку «Графік» (Line) або «Гістограма» (Columns) в групі «Міні-діаграми» (Sparklines) на вкладці «Вставка» (Insert). У вікні, вкажіть діапазон з вихідними числовими даними і клітинки, куди ви хочете вивести Міні-діаграми.

Після натискання на кнопку «ОК» Microsoft Excel створить їх в зазначених клітинках. На вкладці «Конструктор» (Design) можна додатково налаштувати їх колір, тип, включити відображення мінімальних і максимальних значень і т. д.

Відновлення Незбережених файлів

П'ятниця. Вечір. Довгоочікуваний кінець ударної трудового тижня. Передчуваючи відпочинок, ви закриваєте звіт, з яким возилися останню половину дня, і в діалоговому вікні «Зберегти зміни у файлі?» Раптом чогось тиснете «Ні».

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

Насправді, є гарний шанс виправити ситуацію. Якщо у вас Excel 2010, то натисніть на «Файл» - «Останні» (File - Recent) і знайдіть у правому нижньому кутку екрана кнопку «Відновити незбережені книги» (Recover Unsaved Workbooks). В Excel 2013 шлях трохи інший: «Файл» - «Відомості» - «Управління версіями» - «Відновити незбережені книги» (File - Properties - Recover Unsaved Workbooks). Відкриється спеціальна папка з надр Microsoft Office, куди на такий випадок зберігаються тимчасові копії всіх створених або змінених, але Незбережені книг.

Порівняння двох діапазонів на відмінності і збіги

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

1. Виділіть обидва порівнюваних діапазони (утримуючи клавішу Ctrl).
2. Виберіть на вкладці «Головна» - «Умовне форматування» - «Правила виділення клітинок» - «Що повторюються значення» (Home - Conditional formatting - Highlight Cell Rules - Duplicate Values).
3. Виберіть варіант «Унікальні» (Unique) в списку.

Підбір (підгонка) результатів розрахунку під потрібні значення

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

Microsoft Excel зможе зробити таку підгонку за вас, причому швидше і точніше. Для цього натисніть на вкладці «Вставка» кнопку «Аналіз" що якщо "» і виберіть команду «Підбір параметра» (Insert - What If Analysis - Goal Seek). У вікні задайте клітинку, де хочете підібрати потрібне значення, бажаний результат і вхідну клітинку, яка повинна змінитися. Після натискання на «ОК» Excel виконає до 100 «пострілів», щоб підібрати необхідний вам підсумок з точністю до 0,001.

Секретами поділився Микола Павлов - автор проекту «Планета Excel».