У самого злого людини розквітає обличчя, коли йому кажуть, що його люблять. Стало бути, в цьому щастя...

Лев Толстой

У Туркменістані є «Двері в пекло» - місце, яке безперервно горить вже 40 років

Собака відчуває.

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

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

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

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

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

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

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

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

Наприклад, щоб у великий таблиці знайти, хто є головою Wells Fargo, пишемо =ІНДЕКС(А3:А11, MATCH(«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.