Обчислення за формулами - мабуть, найкорисніша функція програми Excel. Вона не тільки економить час користувача, але і виключає можливість арифметичних помилок, які допускає людина. Звичайно, ці плюси досяжні тільки тоді, коли формули створені і працюють правильно. Як і в будь-якій справі, вчитися грамотно складати формули в Ексель потрібно з найпростіших і планомірно рухатися до більш складних обчислень.
У цьому уроці ми надамо розширене практичне керівництво з того, як правильно створювати формули в Microsoft Excel.
Складні формули excel
Ця стаття буде цікава тим, хто намагається зрозуміти роботу складних формул.
У Excel є інструменти, які дозволяють відстежити роботу формули за кроками. Перший з них називається Обчислити формулу і знаходиться на вкладці Формули - Залежності формул у версіях Excel, починаючи з Excel 2007, і в меню Сервіс - Залежності формул у більш ранніх версіях. Другий, менш відомий, але від цього не менш зручний, - функціональна клавіша F9.
Розберемо роботу цих інструментів на декількох прикладах.
Приклад 1. Дана таблиця, що містить відомості про персонал підприємства. Потрібно за введеним табельним номером визначити прізвище співробітника.
Для вирішення цього завдання в комірку H3 ведемо табельний номер, а в комірку I3 формулу = ІНДЕКС ($ B $2:$ У $25; ПОШУК (H3; $ E $2:$E$25;0))
Щоб відстежити формули, поставимо курсор у комірку з формулою і натиснемо кнопку Обчислити формулу. Відкриває діалогове вікно Обчислення формули
У цьому вікні ми бачимо саму формулу, а також кнопку Обчислити, за допомогою якої ми будемо відстежувати покрокове виконання формули. Якщо ви натиснете кнопку Обчислити, буде обчислено підкреслений фрагмент. На наступних зображеннях ми бачимо результат формули
Тепер подивимося, як з цією ж формулою допоможе розібратися клавіша F9.
Виділимо у рядку формул посилання I3, натиснемо F9, виділимо фрагмент $ E $2:$ E $37 і знову натиснемо F9. Клавіша F9 обчислює виділені фрагменти формули, і ми можемо бачити не тільки результат функції, але й аргументи у вигляді масивів. Погодьтеся, що при такому підході формула стає «прозорою», і стає очевидним результат функції ПОШУКПОЗ ()
Щоб привести формулу в початковий вигляд, натиснемо ESC.
Ще трохи потренуємося: виділимо фрагмент $ B $2:$ B $37, натиснемо F9, потім виділимо функцію пошуку (I3; $ E $2:$ E $37; 0) і знову F9. Бачимо масив прізвищ, серед яких буде обрана третя за рахунком
ВАЖЛИВО. Коли ви виділяєте фрагмент формули, слід стежити за його коректністю з точки зору правил побудови виразів: кількістю дужок, що відкриваються і закриваються, цілісністю функцій тощо.
Після аналізу формули не забувайте натискати ESC для повернення до вихідного вигляду.
ВИСНОВОК. Обидва інструменти виконують одне завдання, але клавіша F9, на мій погляд, більш гнучкий інструмент, оскільки дозволяє обчислити довільний фрагмент формули незалежно від того, знаходиться він на початку або в середині формули, а так само розбити формулу на довільні фрагменти на відміну від інструмента Обчислити формулу. Тому наступні приклади будуть присвячені саме клавіші F9
Приклад 2. На основі таблиці з Прикладу 1 створити список табельних номерів і прізвищ співробітників одного з відділів, зазначеного в окремій комірці. Формула, що вирішує цю задачу, виглядає так: = ІНДЕКС ($ E $2:$ Е $25; НАЙМЕНШИЙ (ЯКЩО ($ A $2:$ А $25 = $ Н $7; РЯДОК ($ A $1:$ А $24)); РЯДОК (A1))), це формула масиву, яку слід вводити комбінацією клавіш++
Основою цієї формули є функція ІНДЕКС (), яка дозволяє вивести елемент масиву за вказаним індексом (порядковим номером). Першим аргументом цієї функції вказується діапазон комірок з табельними номерами. Вибрати фрагмент $ E $2 у формулі:$ E $25 і натиснувши F9, ми побачимо значення вихідного масиву
Порядковий номер для вибору елемента масиву обчислюється за допомогою функції НАЙМЕНШИЙ (ЯКЩО (.
Розіб'ємо цю частину формули на складові. Виділіть фрагмент $ A $2:$ A $25 = $ H $7 і натиснемо F9. Цей логічний вираз дає значення ІСТИНА, якщо значення комірки діапазону $ A $2:$ A $25 дорівнює вибраній назві відділу $ H $7, і БРЕХНЯ, якщо не дорівнює.
Виділіть фрагмент РЯДОК ($ A $1:$ A $24) і натиснемо F9, отримаємо масив чисел, що йдуть по порядку від 1 до 24.
Тепер передбачаємо результат функції ЯКЩО () - це масив, в якому значення ІСТИНА заміняться на порядкові номери, а значення БРЕХНЯ залишаться на місці. Побачити це можна, виділивши функцію ЯКЩО повністю з закриваючою дужкою і натиснувши F9
Далі в дію вступає функція НАЙМЕНШИЙ (), яка першим аргументом має вищевказаний масив, а другим - функцію РЯДОК (A1). Звертаємо увагу, що у всій формулі це єдине відносне посилання, яке буде змінюватися при копіюванні формули за рядками, а саме в першому випадку дасть 1, на наступному рядку 2 тощо за порядком. У підсумку в комірці I7 формула, «розшифрована» за допомогою клавіші F9, матиме вигляд
А скопійована в комірку I8
Якщо ви бажаєте застосувати цю формулу для іншого діапазону, буде змінено початковий діапазон функції індексу (), а також змінено верхню межу діапазону функції РЯДОК (), тоді як нижня межа залишається завжди $ A $1. Важливо, щоб кількість рядків початкового діапазону збігалася з кількістю рядків у функції РЯДОК ().
Прізвища в стовпчик J можна вставити за допомогою формули, розібраної в Прикладі 1.
Значення помилки, отримані за допомогою формули, сховані Умовним форматуванням. Докладніше про це можна дізнатися з статті Сховати або замінити значення помилки
Формули Ексель: Дії з формулами
Копіювання/вставлення формули Ексель
Якщо вам потрібно скопіювати формулу з однієї комірки в іншу достатньо скористатися всім відомою комбінацією клавіш (копіювати) і (вставити). Для цього виділіть потрібну комірку, клацнувши по ній курсором миші, натисніть комбінацію клавіш Ctrl + C, при цьому контури комірки будуть виділені пунктирною лінією. Потім виділіть ту комірку, в яку потрібно вставити значення з першої комірки і натисніть комбінацію клавіш Ctrl + V. Весь вміст з першої комірки копіюється до другої комірки.
Скасування операцій
Перш ніж починати редагувати тільки що відкриту робочу книгу, слід дізнатися про функції скасування операцій і про те, як вона може врятувати випадково віддалені дані. Кнопка Скасувати (Undo) панелі швидкого доступу - справжній «хамелеон»: вона пристосовується до виконаних вами дій. Наприклад, якщо ви випадково вилучили вміст групи комірок, натиснувши клавішу, екранна підказка цієї кнопки буде свідчити «Скасувати очищення (Ctrl + Z)». Якщо ви перетягнули діапазон комірок в іншу частину робочого аркуша, підказка зміниться на «Скасувати перетягування».
Для використання цієї команди можна не тільки клацати на кнопці панелі швидкого доступу, але й натискати комбінацію клавіш.
Команда «Скасувати панелі швидкого доступу» постійно змінюється у відповідь на виконані вами дії і зберігає їх порядок. Якщо ви забули її натиснути після будь-якої виконаної дії і вже встигли виконати кілька інших дій, то відкрийте прикріплене до неї контекстне меню і знайдіть там саме ту дію, яка потребує скасування. У результаті буде скасовано і цю дію, і всі дії, виконані після неї (вони автоматично виділяються).
Повторення дій
Після виконання команди Скасувати програма активує кнопку Повернути (Redo), що знаходиться безпосередньо праворуч від неї. Якщо ви вилучили вміст комірки за допомогою клавіші, а потім натиснули кнопку Скасувати (або натиснули клавішу Повернути, буде показано: «Повернути очищення (Ctrl + Y)».
Якщо тепер натиснути кнопку Повернути або натиснути комбінацію клавіш, Excel повторить тільки що скасовану операцію. Насправді все звучить набагато складніше, ніж є насправді. Просто клавіші Скасувати і Повернути є перемикачами між станом робочої книги до дії і після неї (як вмикання і вимикання лампочки).
Що робити, якщо неможливо скасувати операцію
Якщо ви вважаєте, що спокійно можете до невпізнання змінити важливу робочу книгу, то хочу вас попередити: команда скасування операції працює не завжди. Ви можете скасувати останнє невдале видалення вмісту комірки, пересування даних або неправильне копіювання, але неможливо скасувати збереження робочої книги. (Природно, якщо ви зберігали книгу під іншою назвою за допомогою команди Зберегти як, обраної на вкладці Файл, то вихідна книга залишиться незмінною. Але якщо ви користувалися звичайною командою збереження, всі внесені зміни стають частиною вихідної робочої книги.)
На жаль, Excel не попереджає про крок, після якого зворотного шляху немає. Ви дізнаєтеся про це, коли буде вже занадто пізно. Після того як буде виконано незворотну дію, екранна підказка кнопки Скасувати замість очікуваного "" Скасувати "... повідомить: «Неможливо скасувати».
Єдиним винятком з цього правила є випадки, коли програма сама попередньо попереджає про неможливість скасування операції. Коли ви вибираєте команду, яка при нормальних умовах обратима, але в даний момент (за браком пам'яті або тому, що змінюється занадто велика частина робочого аркуша) програма знає, що скасування зробити не зможе, вона попередить вас і запитає, чи хочете ви все-таки її виконати. Якщо ви погодитеся і виконаєте дію редагування, пам'ятайте, що потім доведеться в усьому звинувачувати тільки себе. Наприклад, якщо ви виявите, що помилково вилучили цілий ряд важливих формул (про які забули, тому що в комірках вони не відображаються), то не зможете їх відновити. У такому випадку єдине, що залишається, - закрити файл (команда Файл Закрити) і у відповідь на запит вказати, що зміни зберігати не слід.
Старе добре перетягування
Першою методикою редагування, яку слід освоїти, є перетягування (drag-and-drop). Як випливає з назви, ця методика передбачає використання покажчика миші, який переносить виділення комірок і залишає його в іншому місці робочого аркуша. Незважаючи на те, що перетягування в основному використовується для переміщення вмісту комірок в межах робочого аркуша, його можна застосовувати і для копіювання даних.
Щоб використовувати перетягування для переміщення діапазону комірок (за один раз можна перемістити лише один діапазон), виконайте наступні дії:
- Виділіть діапазон комірок.
- Помістіть курсор миші (або палець або стилус під час роботи з сенсорним екраном) над однією з меж виділеного діапазону.
Як тільки вказівник миші прийме вигляд чотириспрямованої стрілки, можна починати перетягування діапазону в інше місце.