Excel для просунутих 9 корисних трюків

Microsoft Excel, мабуть, найкраща офісна програма з коли-небудь створених. На ній тримаються цілі галузі економіки, так що, віртуозно опанувавши цим інструментом, Ви відразу помітите, що справи у Вашого бізнесу йдуть в гору.

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

1. Функція ВПР
Ця функція дозволяє швидко знайти потрібну Вам значення в таблиці. Наприклад, нам потрібно дізнатися фінальний бал Бетт, ми пишемо: = ВПР ( "Beth", A2: E6,5,0), де Beth - ім'я учня, A2: E6 - діапазон таблиці, 5 - номер стовпця, а 0 означає, що ми не шукаємо точної відповідності значенням.

Функція дуже зручна, однак потрібно знати деякі особливості її використання. По-перше, ВВР шукає тільки зліва направо, так що, якщо Вам знадобиться шукати в іншому порядку, доведеться міняти параметри сортування цілого листа. Також якщо Ви виберете занадто велику таблицю, пошук може зайняти багато часу.

2. Функція ІНДЕКС
Відображає значення або посилання на осередок на перетині конкретних рядки і стовпці в вибраних комірок. Наприклад, щоб подивитися, хто став четвертим в списку найбільш високооплачуваних топ-менеджерів Уолл-стріт, набираємо: = ІНДЕКС (А3: А11, 4).

3. Функція ПОИСКПОЗ
Функція ПОИСКПОЗ виконує пошук зазначеного елемента в діапазоні (Діапазон - дві або більше клітинок аркуша. Осередки діапазону можуть бути як суміжними, так і несуміжними) осередків і відображає відносну позицію цього елемента в діапазоні.

Окремо ІНДЕКС та ПОИСКПОЗ не особливо корисні. Але разом вони можуть замінити функцію ВПР.

Наприклад, щоб у великій таблиці знайти, хто є главою Wells Fargo, пишемо = ІНДЕКС (А3: А11, ПОИСКПОЗ ( "Wells Fargo", B3: B11,0).
За допомогою функції ВПР цього не зробити, тому що вона шукає тільки зліва направо. А поєднання двох останніх дозволяє зробити це з легкістю.

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

Тепер на вкладці TOTAL (РАЗОМ) нам потрібно побачити, скільки і в який день Ви витратили за цей період. Набираємо = СУММ ( 'Week1: Week7'! B2), і формула підсумовує всі значення в осередку B2 на всіх вкладках. Тепер, заповнивши всі осередки, ми з'ясували, в який день тижня витрачали найбільше, а також в результаті підбили всі свої витрати за ці 7 тижнів.

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

При цьому знак $ перед "А" не дає програмі змінювати формулу по горизонталі, а перед "1" - по вертикалі. Якщо ж написати "$ A $ 1", то значення скопійованої осередку буде однаковим в будь-якому напрямку. Дуже зручний прийом, коли доводиться працювати з великими базами даних.

6. &
Якщо Ви хочете зібрати всі значення з різних осередків в одну, Ви можете використовувати функцію СЦЕПИТЬ. Але навіщо набирати стільки букв, якщо можна замінити їх знаком "&".

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

Наприклад, давайте перемножимо дві матриці. Для цього використовуємо функцію МУМНОЖ (Масив 1, Масив 2). Головне, не забудьте закрити формулу круглою дужкою. Тепер натисніть клавіші Ctrl + Shift + Enter, і Excel покаже результат множення у вигляді матриці. Те ж саме стосується і інших функцій, що працюють з масивами, - замість простого натискання Enter для отримання результату використовуйте Ctrl + Shift + Enter.

8. Підбір параметра
Без цієї функції Excel цілим легіонам аналітиків, консультантів і прогнозистів довелося б туго. Запитайте кого завгодно зі сфери консалтингу або продажів, і Вам розкажуть, наскільки корисною буває ця можливість Excel.

Наприклад, Ви займаєтеся продажами нової відеогри, і Вам потрібно дізнатися, скільки примірників Ваші менеджери повинні продати в третьому місяці, щоб заробити 100 мільйонів доларів. Для цього в меню "Інструменти" виберіть функцію "Підбір параметра". Нам потрібно, щоб в осередку Total revenue (Загальна виручка) виявилося значення 100 мільйонів доларів. В поле set cell (Встановити в комірці) вказуємо осередок, в якій буде підсумкова сума, в поле to value (Значення) - бажану суму, а в by Changing cell (Змінюючи значення осередки) виберіть осередок, де буде відображатися кількість проданих в третьому місяці товарів. І - вуаля! - програма впоралася. Параметрами і значеннями в осередках можна легко маніпулювати, щоб отримати потрібний Вам результат.

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

Зведена таблиця - прекрасний спосіб перетворити величезний даремний обсяг інформації в зручні для роботи дані в рекордно короткі терміни.

Примітка: назва функцій і операцій російською мовою зазначено відповідно до русифікованої версією Microsoft Excel 2010.