Заняття 8 з 12
Заняття 8 Робота з текстовими та фінансовими функціями
Розглядаються можливості Excel щодо роботи з текстовою інформацією, помилки в Excel та функції для розрахунку фінансових показників.
Презентація
Розглядаються можливості Excel щодо роботи з текстовою інформацією, а також вирішуються деякі поширені фінансові задачі силами функцій Excel. На завершення продемонстровано можливості табличного процесора щодо пошуку і усунення помилок на аркуші.
Навички MOS 77-420
| Назва навички | Номер навички |
|---|---|
| Демонстрація використання функцій ПРАВСИМВ, ЛЕВСИМВ, ПСТР | 4.4.1 |
| Демонстрація використання функції СЖПРОБЕЛЫ | 4.4.2 |
| Демонстрація використання функцій ПРОПИСН і СТРОЧН | 4.4.3 |
| Демонстрація роботи функції СЦЕПИТЬ | 4.4.4 |
Теорія:
1. Створення формул для обробки тексту
Відеоверсія
Текстова версія
Попри те, що в основному робота в Excel, як і в будь-якому іншому табличному процесорі, ведеться з числовими даними, користувачам часто доводиться обробляти й текстову інформацію. Сценарії такої обробки найрізноманітніші:
- скопійований текст з іншого джерела (наприклад, Word) потрібно розподілити по стовпцях на аркуші Excel;
- в масиві текстової інформації потрібно знайти користувача за прізвищем; кілька рядків об’єднати в один;
- розбити рядок на кілька;
- видалити зайві пробіли;
- вирізати підрядок із існуючого рядка тощо.
В Excel для роботи з текстовою інформацією передбачено кілька різних інструментів і функцій. Якщо говорити про інструменти, то варто згадати розглянуті раніше автозаповнення та миттєве заповнення, які відмінно поєднуються й із текстовим типом даних.
Розподіл тексту по стовпцях
Розподілити текст по стовпцях можна за допомогою миттєвого заповнення (розглядали в питанні "Введення різних типів даних"), однак для простої розбивки краще використовувати інструмент «Розділити по стовпцях», який дозволяє в кілька кліків розбити текст на кілька стовпців за роздільником.
Інструмент «Текст по стовпцях» знаходиться на вкладці «Дані», група «Робота з даними».

Найяскравішим прикладом роботи цього інструмента можна назвати класичну ситуацію, коли записані ПІП в одній клітинці розбивають на окремі стовпці. Текст по стовпцях працює в режимі покрокового майстра.

На першому кроці вибирається, чи буде текст розбиватися за роздільником, або за фіксованою кількістю знаків.
На другому кроці, у разі вибору роздільника, обирається сам роздільник; якщо на першому кроці була вибрана опція «фіксована ширина», то на другому кроці потрібно буде встановити роздільники вручну (наприклад, перший через 10 знаків, другий через 4, третій через 15 тощо).
На останньому кроці потрібно визначитися, куди слід перемістити оброблений текст, оскільки за замовчуванням він замінить вихідний, а також обрати формат для кожного зі стовпців — це зручно, якщо таким чином відокремлюються числові дані від текстових.

Безумовно, усі перелічені інструменти є незамінними, якщо треба вирішити проблему тут і зараз. Якщо в нас є стовпець, який слід розділити, інструмент «Текст по стовпцях» виручить, але що робити, якщо вихідних даних ще немає, а розбивку слід зробити, як тільки вони з’являться, або вихідні дані зміняться і потрібно буде повторити операцію?
У цьому випадку слід скористатися функціями для роботи з текстом, тим більше, що в Excel для роботи з текстовою інформацією є ціла група функцій.
Функція ЛЕВСИМВ (LEFT)
Функція ЛЕВСИМВ повертає вказану кількість перших символів текстового рядка. Дуже проста функція, але в деяких випадках виявляється надзвичайно корисною; як правило, використовується спільно з іншими функціями при побудові формул для роботи з текстом.

Функція ПРАВСИМВ (RIGHT)
Функція ПРАВСИМВ є повною копією попередньої, за тим лиш винятком, що повертає вказану в другому параметрі кількість символів з кінця рядка.

Як і функція ЛЕВСИМВ, в основному використовується для побудови більш складних формул.
Функція ПСТР (MID)
Функція ПСТР є більш просунутою версією функції ЛЕВСИМВ, оскільки повертає вказану кількість символів не з початку рядка, а з вказаної позиції; відповідно, якщо початкова позиція дорівнює 1, то функція ПСТР працюватиме так само, як ЛЕВСИМВ.

Функція СЖПРОБЕЛЫ (TRIM)
Чудова функція для обробки текстів, отриманих з зовнішніх джерел. Функція СЖПРОБЕЛЫ видалить зайві пробіли між словами, а також пробіли на початку і в кінці рядка в клітинці, за винятком одиночних пробілів між словами. При цьому знак нерозривного пробілу " ", який зазвичай використовується на HTML-сторінках, не буде видалений.

Функції ПРОПНАЧ (PROPER), ПРОПИСН (UPPER), СТРОЧН (LOWER)
Три прості функції для керування регістром текстових даних в Excel. Функція ПРОПНАЧ робить першу букву кожного слова в клітинці прописною, а інші — строчними. Функція ПРОПИСН робить усі букви заголовними, а функція СТРОЧН робить усі букви тексту строчними.

Функція СЦЕПИТЬ (CONCATENATE), для Office 365 з Excel версії 2016 — СЦЕП (CONCAT)
Якщо потрібно швидко з’єднати пару рядків, то функції використовувати не обов’язково — можна скористатися знаком "&".

Функція СЦЕПИТЬ об’єднує кілька текстових рядків (клітинок) в один. В одній функції можна одночасно об’єднати до 255 рядків. Підсумковий рядок має містити менше ніж 32767 знаків, інакше буде повернено помилку в обчисленнях.
У функцію можна вводити текст, а можна використовувати текстову інформацію з клітинок.

Як можна помітити, у функцію СЦЕПИТЬ кожну окрему клітинку потрібно вводити індивідуально, не можна просто виділити діапазон зі з’єднуваними клітинками. Цю проблему вирішує нова функція СЦЕП (CONCAT), яка доступна з Excel 2016 і повністю замінює функцію СЦЕПИТЬ; остання в Excel 2016 позначена знак оклику, що означає, що функція застаріла і залишена для сумісності з попередніми версіями табличного процесора.

Як видно, з’єднувані рядки вставляються без роздільних знаків; якщо потрібно додати пробіл або інший символ, у разі використання нової функції СЦЕП можна просто додати пару стовпців, клітинки яких містять цей роздільний символ — саму формулу навіть чіпати не доведеться.

Нова функція ОБЪЕДИНИТЬ (TEXTJOIN) — тільки Office 365 (Excel 2016)
Це нова функція: на відміну від СЦЕП, у неї немає аналогів у попередніх версіях Excel, однак за функціональним призначенням ця функція є логічним розвитком СЦЕП. Коли ми розглядали з’єднання кількох рядків, основним незручністю було те, що рядки з’єднувалися без роздільних знаків — наприклад, банальний пробіл між прізвищем, іменем і по батькові доводилося прописувати щоразу вручну або створювати спеціальний роздільний стовпець. Функція ОБЪЕДИНИТЬ вирішує цю проблему.

Синтаксис функції ОБЪЕДИНИТЬ:
=ОБЪЕДИНИТЬ(роздільник; пропускати_пусті; текст1; [текст2];…)
Роздільник — знак між з’єднуваними рядками; знак слід записати в лапках і саме так, як він буде вставлятися після рядка, наприклад: "," — означає, що пробілів між рядками не буде; більш правильно ", ", тобто додати пробіл після знака пунктуації.
Пропускати_пусті — логічний параметр (ІСТИНА або ЛОЖЬ), який вказує, чи слід враховувати пусті клітинки і відокремлювати їх роздільником, або просто ігнорувати.
Текст1…текстN — власне з’єднувані рядки; як і з функцією СЦЕП, можна вказувати діапазон.
Функція НАЙТИ (FIND)
Функція НАЙТИ (FIND) шукає входження одного текстового рядка в інший і повертає початкову позицію входження. Зазвичай використовується під час побудови формул разом з іншими функціями. Наприклад, за допомогою зв'язки функцій ПСТР (MID) і НАЙТИ можна гнучко розділяти текстовий рядок за певним ознакою і не бути скованим лише кількістю символів з початку або кінця рядка.

У першому випадку, замість ПСТР, можна було б використати ЛЕВСИМВ (LEFT), оскільки рядок витягувався спочатку, а в другому — завдяки ПСТР було витягнуто лише назву ролика на YouTube без нумерації на початку і посилання на сам ролик у кінці.
Як видно, ми не шукали точного входження, а лише частину рядка — відмінний ознака, за яким можна зробити потрібне розмежування.
Функція ПОСТАВИТЬ (SUBSTITUTE)
Остання функція, яку ми розглянемо в рамках питання роботи з текстовими функціями ПОДСТАВИТЬ (SUBSTITUTE) — виконує заміну певного тексту в текстовому рядку.
=ПОДСТАВИТЬ(текст; стар_текст; нов_текст; [номер_вхождения])
Функція доволі проста, пояснення необхідне лише для параметра «номер_вхождения». За його допомогою можна вибрати, яке входження підстроки слід замінити; за замовчуванням, якщо параметр не вказано, замінюються всі входження. Різницю найкраще зрозуміти на прикладі:

Поле для застосування функції дуже широке: номери паспортів, якщо відбулась заміна, прізвище тощо.
На завершення слід зазначити, що відкриття книги Excel з новими функціями (СЦЕП (CONCAT), ОБЪЕДИНИТЬ (TEXTJOIN), УСЛОВИЯ (IFS), ПЕРЕКЛЮЧ (SWITCH), МАКСЕСЛИ (MAXIFS), МИНЕСЛИ (MINIFS)) у більш старих версіях табличного процесора призведе до помилки. Тому використовувати нові функції слід тільки в разі впевненості, що файл не знадобиться відкривати у старих версіях Excel.
2. Використання фінансових функцій
Відеоверсія
Текстова версія
Microsoft Excel — багатофункціональний програмний продукт: з одного боку, програма відносно легка в опануванні, а з іншого — при глибшому вивченні користувач отримує практично безмежні можливості у обчисленнях. Можливо саме завдяки такій гнучкості Excel здобув популярність у фінансовому аналізі, і вже давно серед обов’язкових вимог на посаду фінансового аналітика є вміння користуватися табличним процесором від Microsoft.
Розглянути всі сценарії фінансового застосування Excel в рамках одного курсу неможливо. Тут ми розглянемо основні прийоми, які використовуються в Excel для фінансових розрахунків: тимчасова вартість грошей, обчислення параметрів позики, розрахунок параметрів інвестицій.
ТимчАсові значення грошей
Читається зі наголосом на «А», оскільки номінальна вартість грошей тісно пов’язана з часом. За прикладами далеко ходити не треба: найпростіший приклад — індекс інфляції або індекс споживчих цін. Якщо індекс інфляції за рік рівний 125%, це означає, що 1000 рублів на початку року дорівнюють 1250 рублям наприкінці. Порівняння не зовсім точне, але ідея зрозуміла.
Фінансові розрахунки часто переслідують мету — отримати максимальну вигоду: що краще — 100 гривень зараз чи 150 наприкінці року? А може краще отримувати по 10 гривень щомісяця?
З терміном тимчасової вартості грошей пов’язані кілька понять:
- Приведена вартість. Це поточна сума, яка може бути як додатною, так і від’ємною. Наприклад, депозит у банку або позика.
- Майбутня вартість. Це приведена вартість і нараховані на неї відсотки до кінця періоду, також може бути додатною чи від’ємною. Наприклад, депозит у 50000 рублів під 6% на 5 років матиме майбутню вартість 66 911,28 руб. (при нарахуванні відсотків раз на рік).
- Внесок. Це основна сума, яка вкладається кожного періоду, наприклад, щомісячні внески на депозитний рахунок або погашення позики (тут основна сума + відсотки) за користування кредитом.
- Відсоткова ставка. Частина основної суми, що нараховується за певний період, наприклад, 5% або 0,05. Тут важливо правильно співвідносити періоди і відсотки, оскільки відсотки, як правило, вказуються за рік, а періоди — за місяць; у цьому випадку, для коректного обчислення, відсотки потрібно поділити на 12.
- Період. Інтервал часу, після якого виплачуються або нараховуються відсотки.
- Термін. Проміжок часу, на який вкладаються або беруться гроші. Не слід забувати вказувати відсотки й термін у однакових одиницях часу.
Функції Excel для розрахунків параметрів позики
Фінансові функції Excel, які використовуються для розрахунку позики і на яких ми зупинимось, такі: ПЛТ, ОСПЛТ, ПРПЛТ, СТАВКА, КПЕР, ПС, БС.
Аргументи цих функцій наступні (в деяких функціях деякі з них можуть бути відсутні):
- Ставка. Відсоткова ставка за один період. Важливо вказувати правильну ставку для періодів: наприклад, якщо одиниця періоду — місяць, а відсоткова ставка — річна, то ставку слід поділити на 12.
- Кпер. Загальна кількість виплат (кількість періодів).
- ПС. Приведена до поточного моменту вартість або загальна сума, яка на теперішній момент еквівалентна ряду майбутніх платежів, також називається основною сумою.
- Період. Вказаний період, який має бути меншим або рівним значенню параметра КПЕР.
- Плт. Внесок, який виплачується кожен період.
- Бс. Майбутня вартість після останньої виплати.
- Тип. Коли має відбуватися виплата: 0 — в кінці періоду, 1 — на початку.
Детально ознайомитись з усіма наведеними функціями можна в нашому довіднику, тут же розглянемо пару прикладів їх застосування.
Приклад роботи функцій ПЛТ (PMT), ОСПЛТ (PPMT), ПРПЛТ (IPMT)
У банку береться позика в 100000 руб. під 9,5% річних на 36 місяців; потрібно розрахувати суму періодичних (щомісячних) платежів, яка частина періодичного платежу припадає на основну суму, а яка — на відсотки по кредиту.
Для розрахунку періодичних платежів використовується функція ПЛТ (PMT):

Розрахунок основної частини платежу для конкретного періоду проводиться за допомогою функції ОСПЛТ (PPMT):

Виплата основної частини позики змінюється разом з періодом, а, оскільки щомісячний платіж постійний, то й відсоткова частина підпадає під зміни з часом. Відсоткова частина розраховується за допомогою функції ПРПЛТ (IPMT):

Сума основної частини виплати і відсоткової частини за окремо взятий період дорівнює щомісячному платежу.
Таблиця даних
Прийняття рішення про взяття позики часто супроводжується роздумами «що якби»: якими будуть значення загальної суми виплат при тій чи іншій відсотковій ставці. Користувач у такій ситуації, зазвичай, вводить формулу для розрахунку, а потім змінює певний показник і дивиться на зміну залежних значень.
Безперечно, такий підхід має право на існування, однак у Excel є дуже потужний інструмент для автоматизації подібних дій — це «Таблиця даних» (Вкладка "Дані"/ група "Прогноз"/ Аналіз "що якщо"/ Таблиця даних).

За допомогою «Таблиці даних» розрахуємо параметри щомісячного платежу, загальної суми виплат і відсотків при різних значеннях відсоткової ставки. Для роботи інструмента «Таблиця даних» потрібно підготувати розрахунки, у нашому випадку це: щомісячний платіж, загальна сума виплат (сума щомісячних платежів), відсотки (загальна сума без суми позики). Усі зазначені показники прямо (щомісячний платіж) або опосередковано (загальна сума і відсотки) залежать від відсоткової ставки по кредиту.
Далі потрібно повністю виділити таблицю з вихідними даними, виконати команду Вкладка "Дані"/ Аналіз "що якщо"/ Таблиця даних, а в полі «Підставляти значення по стовпцях у» вказати значення відсотка, який використовується в стартових розрахунках. У нашому випадку це клітинка B2.

У результаті отримаємо миттєвий розрахунок значень для різних параметрів відсоткової ставки.

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

Обчислення складного відсотка
Для визначення розміру інвестицій на кінець періоду з використанням складного відсотка в Excel, коли нарахування відсотків наступного періоду здійснюється на основну суму разом із нарахованими відсотками, призначена функція БС (FV); її можна розшифрувати як «майбутня вартість».
Цю функцію можна використовувати в кількох режимах:
По-перше, як для розрахунку виплат по кредиту, так і для розрахунку відсотків по депозиту (в цьому випадку суму інвестицій слід вказувати зі знаком мінус);
По-друге, можна розрахувати, якою буде сума інвестицій на кінець періоду при одноразовому внесенні, або порахувати, скільки накопичиться, якщо накопичувальний рахунок поповнювати регулярними платежами (як певний аналог накопичення пенсії, але з однаковими сумами внесків).
Розглянемо кілька прикладів роботи формули.
Приклад 1. До банку під 11,5% річних на депозит покладено суму 100 000 руб. Скільки клієнт отримає через 3 роки, якщо нарахування відсотків відбувається щомісяця?

Оскільки розрахунковим періодом для нас є 1 місяць, річний відсоток слід поділити на 12, щоб дізнатися місячний відсоток. Всього буде зроблено 36 нарахувань (3 роки по 12 місяців). Третій параметр функції «Плт» опущено, оскільки за умовою задачі щомісячних виплат не передбачено. А четвертий параметр «Пс», тобто приведена вартість грошей (або основна сума зараз), введено зі знаком мінус, оскільки в нас накопичувальний рахунок. Останній параметр «Тип» опущено — він не впливає на результат, бо в нас немає періодичних виплат.
Приклад 2. Якою буде сума у клієнта банку через 2 роки, якщо він відкриває накопичувальний рахунок і вносить на рахунок по 600 рублів щомісячно під 11,5% річних?

На відміну від попереднього прикладу, у другому прикладі параметр «Плт» — щомісячний платіж заповнений, а параметр «Пс» відсутній, оскільки клієнт банку поповнює рахунок, а не робить одноразовий внесок. Другий важливий момент — це те, що параметр «Плт» вказано зі знаком мінус, оскільки клієнт поповнює рахунок, а не банк здійснює виплату. Останній необов'язковий параметр «Тип» уже впливає на результат: якщо вказати «1 — на початку періоду», то нарахування відсотків буде здійснюватися з урахуванням суми, внесеної в поточному періоді, відповідно загальна сума виплат по відсотках буде дещо більшою.

Складний відсоток навпаки або функція ПС (PV)
Якщо виникає зворотна ситуація, коли на певну дату потрібно накопичити визначену суму за відомої ставки відсотка. Наприклад, через рік купити автомобіль за 700 000 рублів — за допомогою Excel можна швидко розрахувати суму, яку потрібно покласти в банк під відомий відсоток, скажімо, 12%.

Як легко помітити, синтаксис функції дуже схожий на синтаксис БС; фактично це взаємно обернені функції. БС шукає майбутню вартість, а ПС — поточну при відомій майбутній.
Інші параметри розрахунку інвестицій
Окрім розрахунку самих сум інвестицій — як поточної, так і майбутньої, Excel дозволяє за допомогою функції СТАВКА (RATE) швидко відповісти на такі питання:
- Яка процентна ставка має бути, щоб, маючи 621 000 руб., через рік отримати 700 000 рублів?
- При якій ставці я отримаю 700 000 через рік, якщо щомісяця буду вносити по 50 000 рублів?
А функція КПЕР (NPER) дозволить відповісти на аналогічні питання, тільки невідомою вже буде кількість платежів.
Розглянемо кілька прикладів роботи цих функцій; в якості вихідних даних використаємо умову останньої задачі для функції БС.
Для функції СТАВКА (RATE):

Як бачимо, оскільки в формулі розраховується сума депозиту, вона вказана як від’ємна величина. І першу, і другу формулу в кінці ми множимо на 12, оскільки розраховується ставка для одного платіжного періоду, тобто місяця.
Для функції КПЕР (NPER):
Випадок 1 — скільки місяців потрібно, щоб накопичити 700 тис., маючи 621 тис. і ставку 12% річних. Випадок 2 — скільки місяців необхідно, щоб накопичити 700 тис. при 12% річних, поповнюючи свій рахунок на 50 тис. щомісяця.

У практичній частині заняття розглянемо ще приклади роботи з фінансовими функціями.
3. Функція ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Відеоверсія
Текстова версія
Функція ПРОМЕЖУТОЧНЫЕ.ИТОГИ, в англійській версії SUBTOTAL, може бути додана на робочий лист стандартними способами, як і будь-яка інша функція, або за допомогою команди «Проміжний підсумок» групи «Структура» вкладки «Дані». У цьому розділі заняття ми розглянемо синтаксис цієї функції та роботу з нею як самостійною функцією, а в 10-му занятті розглянемо команду «Проміжний підсумок» на вкладці «Дані».
Незважаючи на те, що функція ПРОМЕЖУТОЧНЫЕ.ИТОГИ сама по собі не виконує обчислень, а є контейнером для функцій СРЗНАЧ, СЧЕТ, СУММ, МАКС тощо, вона надзвичайно популярна серед користувачів, які використовують Excel для аналізу даних у своїй професійній діяльності.
Найкраще зрозуміти корисність цієї функції на простому прикладі з фільтрацією даних.
Є умовні суми продаж по днях, потрібно швидко подивитися виручку по місяцях. Для підсумкової виручки використовується проста функція СУММА, проте, змінюючи вхідні дані або фільтруючи їх за певною ознакою, користувачеві доводиться щоразу виконувати перерахунок, вводячи нові вхідні дані для функції, оскільки функції за замовчуванням не «викидають» з підрахунків приховані рядки.

Синтаксис функції ПРОМЕЖУТОЧНЫЕ.ИТОГИ
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции;ссылка1;[ссылка2];…)
Всього ПРОМЕЖУТОЧНЫЕ.ИТОГИ може працювати з 11 функціями, у двох режимах: з відображенням прихованих рядків і з їх виключенням.
| Номер_функции (с включением скрытых значений) | Номер_функции (с исключением скрытых значений) | Функция |
| 1 | 101 | СРЗНАЧ |
| 2 | 102 | СЧЕТ |
| 3 | 102 | СЧЕТЗ |
| 4 | 104 | МАКС |
| 5 | 105 | МИН |
| 6 | 106 | ПРОИЗВЕД |
| 7 | 107 | СТАНДОТКЛОН |
| 8 | 108 | СТАНДОТКЛОНП |
| 9 | 109 | СУММ |
| 10 | 110 | ДИСП |
| 11 | 111 | ДИСПР |
Якщо користуватися лише фільтрацією, то різниці в номерах функцій немає; різниця стосується лише прихованих рядків, схованих командою «Сховати рядки». Сховання рядків ми розглядали в п'ятому занятті.
Також слід зазначити, що розглянута функція працює лише з прихованими рядками, але не зі стовпцями.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ в якості аргументів може приймати як посилання на клітинки, так і на діапазони, зокрема іменовані.
4. Обнаружение и устранение ошибок
Відеоверсія
Текстова версія
Під час створення обчислень появлення помилок неминуче. В Excel існують різні типи помилок, які мають відповідні позначення. А для боротьби з помилками та відстеження етапів обчислення представлені кілька інструментів.
В Excel помилки можна розділити на кілька категорій:
- Синтаксичні. Помилка в самому синтаксисі формули, наприклад, введені не всі аргументи або дужки.
- Логічні. Помилки як такої немає, просто формула неправильна, тому повертає хибний результат.
- Семантичні помилки. Неправильне написання імені функції — у такому випадку Excel поверне помилку "#ИМЯ".
- Циклічні посилання. Коли формула в клітинці посилається на саму себе безпосередньо або через кілька інших клітинок. Сама по собі циклічна посилання не є помилкою, однак зазвичай вони вводяться ненавмисно, тому за замовчуванням в Excel ці посилання вимкнені, а користувач отримує повідомлення про циклічне посилання.
- Помилки введення формул масивів. Завершувати введення формул масивів слід поєднанням клавіш Ctrl+Shift+Enter, інакше можна отримати неправильний результат.
- Помилки незавершених обчислень. При наявності великої кількості формул іноді, якщо у вас слабкий комп'ютер, має сенс вимкнути автоматичне обчислення формул. Щоб бути впевненим, що всі обчислення були виконані, слід натиснути поєднання клавіш Ctrl+Alt+Shift+F9.
Крім усього іншого, працюючи в Excel, можна зіткнутися із ситуацією, коли клітинка заповнена знаками ґратки (#) — таке трапляється, коли ширини стовпця недостатньо, щоб вмістити відформатоване числове значення. Виправляється це простим зміненням ширини стовпця, раніше ми про це говорили. На обчисленнях така помилка не позначається.
Синтаксичні помилки
Несумісність дужок
Якщо формула складається з однієї функції і користувач не закрив дужку, то Excel без попередження її закриє; якщо формула складніша, з кількома дужками, у цьому випадку Excel запропонує виправити помилку у діалоговому вікні.

Проте неправильний ввід дужок може призвести до ситуації, коли Excel не зможе правильно визначити причину помилки. Наприклад, та сама формула, але для функції ЕСЛИ (IF) введено дві відкриваючі дужки.

Під час створення формул Excel підсвічує відкривну і закривну для неї дужку однаковим кольором, також якщо поставити курсор поруч із однією з дужок, а потім стрілками клавіатури зрушити його на один символ у бік дужки, тобто на саму дужку, то вона на кілька секунд виділиться напівжирним (див. рисунок).

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

Коли така клітинка одна, великий проміжок між цифрами добре помітний, але коли на аркуші багато даних, пробіл може і не помітитися. У той же час варто пам'ятати, що текстова інформація вирівнюється за лівим краєм, а числова — за правим. Якщо ширина стовпця буде достатньою, то помітити таку виділену клітинку буде просто. Цей спосіб виявлення справедливий лише якщо користувач самостійно не налаштовував вирівнювання в клітинці.
З зайвими пробілами в клітинках із числовою інформацією більш-менш розібралися, а ось як бути із зайвими пробілами в текстових даних. Це може бути важливо, якщо текст використовується не просто як мітки, а в обчисленнях, де важлива кількість символів у рядку. Наприклад, Excel ніяк не підкоригує текстовий рядок, якщо він почався з пробілу, і помітити таку помилку буває дуже непросто.
Для видалення зайвих пробілів із текстового рядка в Excel є функція СЖПРОБЕЛЫ (TRIM). Вона видалить подвійні пробіли, пробіли на початку і в кінці рядка, залишаться тільки поодинокі пробіли між словами, а за допомогою конструкції:
=СЖПРОБЕЛЫ(B1)=B1
Можна перевірити, чи є в клітинці зайві пробіли (результат ЛОЖЬ) або ні (результат ИСТИНА).

Помилки, які повертають формули
Під час виконання обчислень користувач може зіткнутися з такими помилками; важливо розуміти, що означає та чи інша помилка, щоб правильно вирішити проблему
- #ДЕЛ/0!
- #ЗНАЧ!
- #ИМЯ?
- #Н/Д
- #ПУСТО!
- #ССЫЛКА!
- #ЧИСЛО!
Помилка #ДЕЛ/0!
Ця помилка виникає при діленні на нуль. У Excel ділення на нуль недопустиме, і, оскільки порожні клітинки в обчисленнях сприймаються як нуль, така помилка досить часто може виникнути, якщо формула створена, а дані ще не введені.

Після введення вихідних даних формула буде автоматично перерахована, але якщо є бажання позбутися помилок, можна переробити формулу, помістивши її всередину функції ЕСЛИ (IF):
=ЕСЛИ(N2=0;"";N3/N2)

Таким чином перевіряється дільник і, якщо він дорівнює нулю або клітинка порожня, обчислення не виконується; у протилежному випадку обчислення проводиться.
Однак для перехоплення цієї та інших помилок краще використовувати спеціальну функцію ЕСЛИОШИБКА (IFERROR). У версіях Excel до 2007 року ця функція називалася ЕОШ().

Детально функцію ЕСЛИОШИБКА ми розглядали в першому питанні сьомого заняття.
Помилка #ЗНАЧ!
Часто виникаюча помилка, оскільки причин її появи може бути кілька:
В аргумент передаються дані недопустимого типу або формула виконує операцію з недопустимими даними, наприклад, до числових даних додають текст. Користувачі часто стикаються з цим типом помилки, коли на аркуш були скопійовані дані з іншого джерела й вони можуть містити зайві пробіли в числах.

Помилка #ЗНАЧ! може виникати, коли аргумент функції має бути діапазоном, а передане лише значення однієї клітинки або навпаки.
При введенні масивної формули натиснули Enter, а не поєднання Ctrl+Shift+Enter.
Проблема часто виникає при некоректній роботі користувацької функції або якщо вона не обчислюється.
Помилка #ИМЯ?
Цей тип помилки пов'язаний із неправильними іменами:
- Неправильне ім'я формули.
- Ім'я користувацької функції з додаткової надбудови, наприклад якщо відкрити файл в Excel без цієї надбудови.
- Формула містить іменований діапазон, який пізніше було видалено.

Помилка #Н/Д
Помилка #Н/Д повертається формулами й функціями, якщо їм бракує даних для обчислень. Також ця помилка з'являється в клітинках, які містять посилання на клітинку з формулою, яка повернула значення #Н/Д.
Дослівно цю помилку можна інтерпретувати як «немає даних». Функції пошуку (ВПР, ГПР, ПОИСКПОЗ або ПРОСМОТР) повертають цю помилку, якщо не знаходять дані.
Іноді, наприклад у статистичних дослідженнях, важливо розуміти, коли даних немає, а коли їх забули ввести, тому в Excel є спеціальна функція НД() (NA), яка поверне значення помилки #Н/Д. У цьому випадку можна буде однозначно визначити, що даних за той чи інший період немає.
Помилка #ПУСТО!
#ПУСТО! виникає, коли функція посилається на неперетинні діапазони.

У другому прикладі число 9 знаходиться на перетині вказаних діапазонів.
Помилка #ССЫЛКА!
Також доволі «частий гість» на аркушах користувачів, коли відбуваються операції перестановки даних. Усі перераховані нижче випадки призведуть до виникнення помилки #ССЫЛКА!:
- Було видалено клітинку, яка використовується у формулі.
- Після копіювання (або автозаповнення) формули з відносними посиланнями формула може посилатися на неіснуюче місце. Наприклад, якщо формула посилалася на клітинки стовпця А, її скопіювали лівіше, то виникне помилка, оскільки стовпця лівіше А не існує.
- Було видалено аркуш із клітинкою, на яку посилається формула.
- Клітинку вирізали, а потім вставили на місце клітинки, яка використовується у формулі.

Помилка #ЧИСЛО!
Формула поверне цей тип помилки у таких випадках:
- Аргумент має бути числом, але передається значення іншого типу.
- У функції використовується недопустимий аргумент, наприклад обчислення квадратного кореня з від'ємного числа =КОРЕНЬ(-12) (КОРЕНЬ — SQRT).
- Деякі функції, які використовують у обчисленнях метод ітерацій, наприклад ВСД і ВСТАВКА, повернуть цю помилку, якщо не зможуть знайти результат.
- Повернене формулою значення знаходиться поза робочими межами Excel.
Засоби Excel для виявлення та усунення помилок
Знайти помилку, особливо якщо її повертає сама формула, не найскладніше завдання — складніше знайти причину і усунути її. У Excel є низка інструментів для аналізу помилок обчислень і їх виправлення.
Виділення клітинок певного типу
Інструмент «Знайти та виділити»/«Виділити групу клітинок» ми вже розглядали для пошуку певних значень на аркуші, однак він може виявитися надзвичайно корисним і при пошуку причини помилки.

За допомогою діалогового вікна можна виділити як клітинки з формулами, так і задати додаткові умови, наприклад виділити лише формули, що повертають помилки, або лише текстові значення тощо.
Також іноді зручно виділити ті клітинки, які впливають на обчислення цільової, або навпаки — виділити ті клітинки, що залежать від цільової.
На замітку
Будьте уважні: якщо перед запуском діалогового вікна «Виділити групу клітинок» у вас виділено діапазон, то пошук відбуватиметься в рамках виділення, а не всього аркуша.
Відстеження зв'язків між клітинками
На вкладці "Формули" в групі "Залежності формул" безпосередньо представлені інструменти для налагодження помилок у формулах.

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

За допомогою команди "Прибрати стрілки" можна видалити як усі стрілки, так і окремо для впливаючих і залежних клітинок.
Команда "Показати формули" замість результату обчислення відображає самі формули в клітинках, це ж можна зробити й гарячим сполученням клавіш: Ctrl+` (тильда або російська ё).
Якщо ви переключили аркуш у режим відображення формул, не забувайте, що під час виведення інформації на друк у такому режимі буде надруковано саме формули, а не результати обчислення, а також те, що при відображенні формул зміняться розміри стовпців і замість двох–трьох сторінок при друку може вийти 5–6. Основні налаштування при друку розглянуті в 3-му занятті, а додаткові параметри — у 9-му.
Цікава команда "Перевірка наявності помилок", яка підрозділяється на 3 команди.

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

Джерело помилки
Ця команда фактично покаже стрілками впливаючі клітинки, єдина відмінність від команди "Впливаючі клітинки" полягає в тому, що вона працює лише якщо формула видає помилку.

Циклічні посилання
Покажe всі циклічні посилання на аркуші; якщо їх немає, команда "Циклічні посилання" неактивна.
Команда "Обчислити формулу"
Ця команда дозволяє поетапно виконати обчислення певної формули і аналогічна команді "Показати етапи обчислення" за тим винятком, що застосовна до будь-якої формули, а не тільки до тієї, яка повертає помилку в результаті своєї роботи.

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

За замовчуванням ця опція увімкнена і саме вона відповідає за появу невеликого трикутника у верхньому лівому куті клітинки, у якій або є, або може бути помилка (під "може бути" підходить, наприклад, сумування частини діапазону).

При виділенні такої клітинки з’явиться смарт-тег, натискання на який призводить до появи меню; пункти меню залежать від типу самої помилки — команди в цьому меню перегукуються з розглянутими раніше інструментами з групи "Залежності формул".
Флешкартки
Так звані карти пам’яті: дивитеся на карту і намагаєтеся відповісти, при натисканні на карту відобразиться правильна відповідь. Карти пам’яті хороші для запам’ятовування ключових позицій заняття. Усі заняття цього курсу супроводжені картами пам’яті.
Практика
У практичній частині ви знайдете завдання за пройденим заняттям. Після їх виконання у вас є можливість порівняти свій варіант із підготовленою лектором відповіддю. Рекомендується дивитися розв'язок лише після того, як ви виконали завдання самостійно. Для деяких завдань є невеликі підказки.
Завдання 1. Набуття навичок роботи з текстовими функціями Excel
- Відкрити файл "Заняття 8.1 старт" — список із ПІБ співробітників очистити від зайвих пропусків і зробити початкові букви прізвища, імені та по батькові великими, а всі інші — маленькими.
- У окремий стовпець вивести лише прізвища співробітників.
- Створити ще один стовпець, який складатиметься зі слова "Співробітник №", порядкового номера, двокрапки і прізвища співробітника. Наприклад: Співробітник № 5: Петров. Цей запис створити за допомогою функцій Excel.
- Зберегти файл під іменем "Заняття 8.1 виконано".
Переглянути розв'язок
Завдання 2. Набуття навичок роботи з фінансовими функціями Excel. Частина 1
- Створити порожню книгу Excel.
- За допомогою табличного процесора вирішити задачу: "У банку береться кредит 250 000 руб, під 27% річних на 3 роки. Розрахувати суму щомісячного платежу, основну і процентну частини платежу для всього періоду погашення позики."
- Оформити розв'язок таким чином, щоб можна було змінити будь-яку з трьох складових на аркуші й перерахунок відбувався автоматично.
- Зберегти книгу під іменем "Завдання 8 фінансові задачі".
Підказка
Йдеться про те, щоб усі числові дані розміщувати в клітинках, а в формулах використовувати посилання на ці клітинки.
Переглянути розв'язок
Завдання 3. Набуття навичок роботи з фінансовими функціями Excel. Частина 2
- Відкрити книгу з попереднього завдання "Завдання 8 фінансові задачі".
- Перейменувати аркуш книги на "Задача 1".
- Створити новий аркуш і назвати його "Задача 2".
- За допомогою табличного процесора вирішити задачу: "Через рік вам потрібна сума в 150 000 рублів. Банк пропонує розміщення річних депозитів під 17% річних з нарахуванням відсотків щомісячно на початку періоду. Яку суму потрібно внести зараз, щоб через рік отримати можливість забрати 150 тис. рублів?"
- Для наведеної вище задачі зробити аналогічні розрахунки для таких процентних ставок банку: 15,5%, 16,5%, 18%, 19%, 20%, 21,5%.
- Зберегти книгу під іменем "Завдання 8 фінансові задачі частина 2".
Переглянути розв'язок
Завдання 4. Набуття навичок використання функції ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL)
- Відкрити книгу "Завдання 8.4 старт".
- Між стовпцями "Основна частина виплати" і "Період" додати стовпець "Виплати за відсотками" і виконати розрахунок виплат відсотків по позиці, використовуючи відповідну функцію.
- Додати стовпець "Рік", у який записати рік погашення позики: 1, 2 або 3.
- За допомогою функції ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL) переглянути суму основної частини виплати і виплат за відсотками, приховуючи непотрібні рядки.
- Зберегти книгу під іменем "Завдання 8.4 виконано".