Excel 2007: формули без проблем
 | На завершення теми роботи з формулами нам залишилося розібратися у трьох моментах: як швидко будувати довгі вирази, як використовувати функції Excel та як контролювати обчислення. Ці питання ми й розглянемо у статті. Причому головну увагу тепер приділимо не основам, а ефективним прийомам роботи з формулами. Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства |
При розробленні більш-менш складних формул нам, найімовірніше, знадобляться вбудовані функції Excel. Тому з них і почнемо. Тож порядок розгляду матеріалу я пропоную інший. Спочатку ми попрацюємо з функціями і лише потім перейдемо до прийомів створення складних формул.
Використання вбудованих функцій
Вбудовані функції — дуже важлива частина Excel. Вони потрібні для виконання різних обчислювальних операцій. За допомогою функцій можна, наприклад, підрахувати суму комірок у блоці, середнє арифметичне, округлити результат і багато що інше. Бібліотека Excel 2007 містить близько 400 вбудованих функцій. Усіх їх поділено на категорії: фінансові, математичні, текстові, логічні, дати й часу, статистичні, функції роботи з посиланнями та масивами, для роботи з базою даних, функції для перевірки властивостей та значень тощо. Можуть бути й інші категорії. Це зроблено для зручності пошуку функцій.
Будь-яка функція має унікальне ім’я. При зверненні до функції їй потрібно повідомити параметри. Наприклад, у формулі «=Сумм(A1:A9;B1:B5)» функції з ім’ям «Сумм» передаються два параметри: адреси блоків комірок «A1:A9» і «B1:B5», значення в яких потрібно підсумувати. Після звернення до неї функція повертає результат — число, текстовий рядок або логічне значення. Деякі функції повертають як результат посилання на комірку робочого листа.
Параметри функції можуть бути числовими, логічними, текстовими. Як параметри можна зазначати адреси елементів (чи блока) електронної таблиці. У цьому випадку функція обробляє вміст комірок. Наприклад, формула «=Сумм(А1:А5)» підсумовує вміст комірок «А1», «А2», «А3», «А4», «А5», формула «=СрЗнач(А1:А5)» знаходить середнє арифметичне значень у цих комірках. Результатом роботи обох функцій буде число.
В Excel можна використовувати вкладені функції. У цьому випадку першою обробляється функція, що стоїть усередині виразу. Її результат є параметром для зовнішньої функції. Зрозуміло, результат і параметри обох функцій мають бути погоджені: якщо для зовнішньої функції як параметр потрібно зазначити число, то і вкладена функція має повернути число. Наприклад, формула «=Округл(СрЗнач(H4:H8);2» є допустимою. Вона обчислює середнє арифметичне блока «H4:H8» (повертає число) та округлює отриманий результат до двох знаків після коми.
Кількість параметрів у різних функцій може відрізнятися. Якщо параметрів кілька, вони відділяються один від одного символом «;» (крапка з комою). Наприклад, функція округлення «Округл» має два параметри: число, яке потрібно округлити, і точність округлення. Формула «=Округл(10,125;2)» округлить число «10,125» до двох знаків, тобто до «10,13». Функція добування квадратного кореня «Корень» має один параметр — число. Формула «=Корень(9)» поверне до комірки значення «3».
Список параметрів функції може бути порожнім. Наприклад, функція «Сегодня()» повертає значення поточної дати. Виходячи з логіки її дій жодні параметри цієї функції не потрібні.
Робота з функціями виконується в розділі «Формулы» головного меню. У ньому є спеціальна група іконок з назвою «Библиотека функций» (рис. 1). Усього в цій групі десять іконок. Перша ліворуч називається «Вставить функцию». Клацання по цій іконці викликає спеціальний інструмент — Майстер функцій. Він дозволяє швидко та безпомилково впроваджувати функції до формул Excel: ви заповнюєте бланк із параметрами, а все інше Майстер зробить сам. Праворуч від іконки «Вставить функцию» розташовано ще дев’ять елементів: «Автосумма», «Недавно использовались», «Финансовые», «Логические», «Текстовые», «Дата и время», «Ссылки и массивы», «Математические», «Другие функции». Ці іконки пов’язані з певною категорією функцій. Кожна іконка являє собою список, що розкривається. Клацаємо лівою кнопкою по іконці «Автосумма». Розкриється список, як показано на рис. 2. У верхній частині списку є перелік функцій для вибраної категорії (у цьому випадку їх п’ять). Клацання по будь-якому рядку списку викликає вікно для заповнення параметрів конкретної функції. Останній елемент — «Другие функции…» — починає роботу з Майстром.
Майстер функцій — найуніверсальніший інструмент для роботи з ними. Користуватися ним дуже просто, а саме:
1) стаємо на комірку, куди потрібно ввести функцію;
2) клацаємо по іконці «
Вставить функцию» або натискуємо на кнопку «fx» (ліворуч від рядка формул);3) з’явиться вікно зі списком функцій (рис. 3). Знаходимо потрібну функцію (наприклад, «
Сумм ЕслиМн»). Подвійним клацанням викликаємо її або клацаємо по кнопці «ОК»; 
4) розкриється вікно з параметрами функції (рис. 4);
5) заповнюємо параметри, натискуємо на «
ОК» чи на клавішу «Enter».Вибір функції здійснюється зі списку «
Выберите функцию» в середній частині вікна на рис. 3. Склад цього списку залежить від категорії функції в однойменному полі «Категория». Наприклад, якщо в цьому полі вибрати значення «Финансовые», список буде обмежено лише функціями цього типу.У полі «
Поиск функции» можна вказати її приблизну назву та скористатися кнопкою «Найти» (або натиснути на клавішу «Enter»). У такий спосіб зручно знаходити функції схожої дії з різних категорій. Наприклад, для звичайного підсумовування (функція «Сумм()», категорія «Математические»), для підсумовування в базах даних (функція «БДСумм()», категорія «Работа с базой данных») тощо.Складається враження, що при такому пошуку Excel аналізує не лише імена, а і стислий опис функцій. Наприклад, якщо в полі «
Поиск функции» написати «Сумм», то до відібраного списку потрапить і функція «Счет», яка підраховує кількість значень у заданому списку. Зрозуміло, у списку наявні «Сумм()», «СуммЕсли()», «СуммПроизв()», «БДСумм()» та інші функції, що стосуються операцій підсумовування. При цьому Excel у полі категорії автоматично поставить значення «Рекомендуется».Важливо!
Усередині списку функцій зручно здійснювати пошук за першими літерами імені. Наприклад, у режимі перегляду повного списку достатньо набрати на клавіатурі російську літеру «С» — і курсор миттєво переміститися до функції «Сегодня()». Поєднання «Су» встановить курсор на функцію «Сумм()» тощо. Регістр символів може бути будь-яким.Після вибору функції Excel запропонує ввести її параметри. Їх заповнюють у спеціальному вікні (рис. 4), вид якого залежить від типу функції, тобто від кількості її параметрів. Розглянемо для прикладу роботу з функцією «
СуммЕслиМн()». Вона дозволяє вибірково підсумовувати значення з таблиці, аналізуючи при цьому кілька умов. У функції як мінімум три параметри: діапазон підсумовування (блок на робочому листі), діапазон для перевірки умови та власне значення для перевірки. Усі три параметри подано у вікні на рис. 4. Для кожного параметра передбачено своє поле. Обов’язкові параметри виділено напівжирним шрифтом. У нашому випадку — це перші три елементи. Заповнюємо їх значеннями та натискуємо на кнопку «ОК» або на клавішу «Enter». Excel побудує функцію та впровадить її до комірки чи до формули.Функція «
СуммЕслиМн()» може перевіряти не одну, а кілька умов. Тому кількість полів у вікні параметрів може динамічно змінюватися. Як тільки ми заповнимо «Диапазон_суммирования», «Диапазон_условия1» і «Условие1», Excel відразу ж додасть у вікні поля «Диапазон_условия2», «Условие2» тощо.У нижній частині вікна можна прочитати стислий опис роботи функції. Щоб отримати докладну довідку про призначення, параметри та приклад використання, потрібно клацнути по посиланню «
Справка по этой функции» (у лівій нижній частині вікна). Переміщаючись окремими полями з параметрами, у нижній частині вікна можна прочитати коментарі про їх призначення.Порада
Працюючи з Майстром функцій, підставляйте адреси комірок та блоків до поля параметрів клацанням миші по робочому листу — точно так, як це робиться при створенні формул.Використання Майстра функцій дає багато переваг. Ви ніколи не помилитеся в імені функції. Excel правильно сформує список параметрів та розставить дужки. Вибираючи адреси методом навігації по робочому листу, ви
гарантовано введете правильні значення. Є й інші переваги. Багато які функції в локалізованій версії Excel записуються російською мовою. Водночас адреси комірок та блоків потрібно вводити в англійській нотації. При використанні Майстра функцій вам не доведеться постійно перемикати розкладку клавіатури.Порада
Обов’язково ознайомтеся зі списком функцій Excel 2007. Багато які з них дуже корисні для бухгалтера. Чим ширшим буде ваш кругозір у цьому питанні, тим складніші завдання ви зможете вирішувати.У таблиці наведено список функцій, які, на мій погляд, заслуговують на першочергову увагу для бухгалтера. Багато з яких ми використовуватимемо в прикладах.
| Функція | Призначення |
| 1 | 2 |
| Сумм(арг1; арг2;…; арг n) | Підсумовує аргументи, якими можуть бути числа, адреси комірок та блоків |
| СуммЕсли(Блок1; Условие; Блок2) | Вибірково підсумовує комірки за умовою. Параметри «Блок1» та «Блок2» — це адреси блоків таблиці, параметр «Условие» — текстовий рядок з умовою. Обов’язковими є два перші параметри. Функція аналізує значення у блоці «Блок1» та перевіряє їх на умову «Условие». Якщо умова виконується, вона накопичує суму з відповідних значень «Блок2». Якщо параметр «Блок2» опущено, сума утворюється зі значень «Блок1». «СуммЕсли» — дуже цінна функція для бухгалтера. Вона дозволяє, наприклад, вибірково підсумувати частину комірок блока, позначених в окремій колонці певним символом (або словом) |
| СуммЕслиМн(БлокСумм; БлокУсл1; Условие1; БлокУсл2; Условие2…) | Це нова функція в Excel 2007, розширений варіант «СуммЕсли()». Вона накопичує суму з діапазону комірок, аналізуючи при цьому декілька умов. Їх може бути задано у вигляді числа, виразу, посилання на комірку чи текст. Обов’язковими є перші три параметри. У цьому випадку функція перевіряє комірки у блоці «БлокУсл1» та порівнює їх зі значенням «Условие1». Якщо результат перевірки «Истина» (умова виконується), вона накопичує суму з блока «БлокСумм». В «СуммЕслиМн()» можна зазначити більше трьох параметрів, увівши додаткові умови перевірки. Тоді функція перевірятиме комірки з «БлокУсл2» та порівнюватиме їх зі значенням «Условие2» тощо. Підсумовування виконується за умовою «И», тобто в тому випадку, коли ВСІ порівняння повертають результат «Истина». Excel 2007 дозволяє ввести 127 умов |
| СуммПроизв(Блок1;…; Блок n) | Знаходить суму добутків значень у зазначених блоках. Наприклад, формула «=СуммПроизв (D2:D4;E2:E4)» поелементно перемножить комірки в зазначених блоках і потім підсумує результат. Фактично отримаємо результат: «=D2*E2+D3*E3+D4*E4» |
| Счет(Блок1; Блок2;…; Блок n) | Підраховує кількість чисел (саме чисел!) у зазначених блоках |
| СчетЕсли(Блок1; Условие) | Підраховує кількість непорожніх комірок у блоці, що задовольняють умову «Условие». Дуже схожа на функцію «СуммЕсли» |
| СчетЗ(Блок1; Блок2;…; Блок n) | Підраховує кількість значень та непорожніх комірок у блоках |
| СчитатьПустоты(Блок) | Підраховує кількість порожніх значень у блоці |
| Если(Условие; Выражение1; Выражение2) | Аналізує значення виразу «Условие». Якщо воно істинне, функція повертає значення «Виражение1», інакше повертає значення «Виражение2». Це єдина функція, що дозволяє організувати розгалуження в обчисленнях. Широко застосовується для контролю даних. Розширити діапазон умов, що обробляються, можна логічними функціями «І()», «ИЛИ()», «НЕ()» |
| Макс(Блок1; Блок2;…; Блок n) | Знаходить максимальне значення в зазначених блоках комірок |
| Мин(Блок1; Блок2;…; Блок n) | Знаходить мінімальне значення в зазначених блоках комірок |
| СрЗнач(Число1, Число2…) | Обчислює середнє арифметичне аргументів. Параметри можуть бути числами, іменованими діапазонами, посиланнями на комірки. Додаткові можливості для визначення середнього дають функції «СрЗначА()» (параметрами можуть бути як числа, так і логічні вирази), «СрЗначЕсли()» (визначає середнє за умовою), «СрЗначЕслиМн()» (обчислює середнє за набором умов) |
| Округл(Число; КолРазр) | Округлює число до зазначеної кількості розрядів. Замість числа можна вказувати адресу комірки чи формулу. Додаткові способи округлення надають функції «ОкруглВверх()», «ОкруглВниз()», «ОКРУГЛТ()» |
| Порог(Значение; Предел) | Перевіряє, чи перевищує зазначене «Значение» заданий «Предел». Параметри можна вводити у вигляді посилань на комірки. Функція корисна при контролі значень та результатів обчислень у таблиці |
| Год(Дата) Месяц(Дата) День(Дата) | Ці функції виділяють із дати номер року, місяця чи дня. Наприклад, формула «=Год(“01/04/2009”)» видасть число «2009», формула «Месяц(“01/04/2009”)» поверне «4», формула «День(“01/04/2009”)» — число «1». Лапки в цьому випадку обов’язкові. Як параметр можна зазначити посилання на комірку, в якій записано дату |
| Остат(Число; Делитель) | Повертає залишок від ділення параметра «Число» на «Делитель» |
| Сегодня() | Повертає значення поточної дати |
| Римское(Число; Форма) | Перетворить арабське число на римське. Результат подається в текстовій формі. Функція корисна при оформленні деяких звітів. Перший параметр — число для перетворення. Другий параметр — спосіб запису (необов’язковий). Може набувати значень «1» — класична форма, «2» — наочна, «3» — розгорнута, «4» — спрощена тощо. Параметр можна залишати за умовчанням |
| Строчное(Текст) | Робить усі літери в рядку «Текст» маленькими. Функція корисна при оформленні звітів |
| Прописн(Текст) | Робить усі літери в рядку «Текст» великими |
| СжПробелы (Текст) | Видаляє з тексту пробіли, залишаючи одиночний пробіл між словами. Функцію зручно застосовувати при оформленні документів та при роботі з базами даних |
| Сцепить(Текст1; Текст2;…) | Об’єднує кілька текстових рядків в один. Зручна при формуванні складених заголовків та підписів у документах |
| Текст(Число; Формат) | Перетворює значення параметра «Число» на текст. Вид перетворення визначається рядком «Формат». Використовується при перетворенні дати в текстове зображення |
Докладні відомості про призначення параметрів функцій можна відшукати в довідковій системі.
Створення складних формул
Уміння працювати зі складними формулами — найважливіший показник рівня користувача Excel. Щоб швидко та безпомилково створити довгу формулу, слід пам’ятати чотири прості, але важливі правила:
1. Адреси до формул підставляйте, вибираючи їх на робочому листі.
2. Функції будуйте з використанням Майстра функцій. Це гарантовано позбавить синтаксичних помилок.
3. Формула — це звичайний текст, робота з формулами нічим не відрізняється від редагування текстового рядка. При написанні тексту формул можна (і потрібно!) використовувати буфер обміну.
4. Довгу формулу створюйте частинами в окремих комірках, а потім об’єднуйте їх в одну.
Розглянемо застосування цих правил на практиці.
Приклад 1. В
кладена «ЕСЛИ»Почнемо з дещо «шкільного», але показового прикладу. До комірки «
A1» я введу число в діапазоні від «1» до «5». До комірки «B1» потрібно ввести формулу, яка напише це число текстом. Для вирішення завдання потрібно сформувати формулу з п’яти вкладених функцій «Если()» по одній для кожного випадку. Швидко написати формулу можна так:1) стаємо на комірку «
B1», викликаємо меню «Формулы», клацаємо по іконці Майстра функцій;2) у вікні Майстра (рис. 3) вибираємо категорію «
Полный алфавитный перечень», переходимо до поля зі списком функцій, набираємо «Ес» (на російському регістрі);3) покажчик у списку автоматично зупиниться на функції «
Если()», клацаємо по ній;4) з’явиться вікно параметрів, заповнюємо їх для першого випадку, як показано на рис. 5. При цьому нічого не набираємо, усі адреси вибираємо з робочого листа! Наприклад, щоб побудувати умову перевірки (перший параметр), клацаємо по комірці «
A1» — її адреса з’явиться в полі «Лог_выражение». Додруковуємо рядок «=1» і переходимо до другого параметра; 
5) тут уводимо текст «
Один». Це результат роботи формули, коли в «А1» записано «1»;6) значення третього параметра ми поки що не знаємо, друкуємо будь-який символ, наприклад «
1»;7) закінчивши формування функції, натискуємо на «
ОК». Ми отримали вираз «=Если(A1=1;"Один";1)». Тепер потрібно розширити її для всіх п’яти випадків ;8) стаємо на комірку «
B1». Натискуємо на«F2», потрапляємо до режиму редагування формули;9) перебуваючи в режимі редагування, виділяємо текст(!)
формули без символу «=». Клацаємо по виділеному блоку правою кнопкою. Вибираємо з контекстного меню «Копировать». Текст формули потрапить до буфера обміну;10) видаляємо третій параметр у формулі (символ «
1»), вставляємо на його місце вміст буфера обміну. У результаті отримаємо «=Если(A1=1;"Один";Если(A1=1;"Один";1))». Коригуємо текст «Один» та умову перевірки, щоб отримати «=Если(A1=1;"Один";Если(A1=2;"Два";1))»;11) знову вставляємо на місце останнього параметра («
1») вміст буфера та коригуємо формулу. І так повторюємо п’ять разів, доки не отримаємо результат: «=Если(A1=1;"Один";Если(A1=2; "Два";Если(A1=3;"Три";Если(A1=4; "Четыре";Если(A1=5;"Пять";)))))».Проаналізуємо, що в нас вийшло. Я ввів текст усього з 26 символів: «
=1Один2Два3Три4Четыре5Пять». Сама формула зайняла 91 символ. Увівши формулу за допомогою Майстра та застосувавши буфер обміну, я скоротив роботу на 71 %.
Приклад 2. Розділяй та володарюй
На рис. 6 показано фрагмент докладного звіту про рух ТМЦ на складі в кількісному виразі. У колонці «
Дата» записано дату операції. Потім ідуть найменування товару та обсяг його реалізації. За цими даними потрібно отримати компактні відомості про динаміку реалізації ТМЦ за весь період. Таке завдання в Excel 2007 вирішити неважко, це забере півхвилини. Якби б не ключове слово «компактні»… Забігаючи наперед, скажу, що в Excel є чудові інструменти оброблення даних. Це і зведені таблиці, і підбиття підсумків, і консолідація. Але для їх ефективного застосування потрібно спочатку підготувати вихідні дані. Наприклад, якщо я побудую зведений звіт за базою, як вона є (рис. 6), розмістивши у рядках дати, а в колонках — найменування ТМЦ, то ступенем деталізації такого звіту буде день. Уявіть, що в таблиці зібрано дані за цілий рік і в ній фігурує сотня товарів. Тоді зведений звіт вийде розміром у 365 рядків і 100 стовпців. Проаналізувати таку таблицю абсолютно нереально, потрібно її скоротити. Наприклад, побудувати дані не за днями, а за місяцями. Щоб зробити це, потрібно до вихідної таблиці додати окрему колонку, до цієї колонки написати формулу, яка з повноцінної дати вибере тільки номер місяця та номер року. Узявши таку колонку за ознаку для групування, можна миттєво отримати потрібний результат стандартними засобами Excel. Ми ще неодноразово повернемося до цього прийому в наших прикладах. А поки що завдання таке. 
Додати до таблиці колонку «
МесГод» (рис. 7). До неї записати формулу, яка кожну дату покаже у вигляді номера місяця та номера року, розділивши їх символом «-». Крім того, якщо номер місяця складається з однієї цифри, його потрібно ліворуч доповнити нулем. Це забезпечить правильне сортування даних. Інакше може виявитися, що при сортуванні рядків в алфавітному порядку жовтень («10-200х») передуватиме лютому («2-200х»). Інакше кажучи, для дат з «01.01.200х» по «31.01.200х» формула має повернути значення « 01-200х», для лютого — « 02-200х», для грудня — « 12-200х». Ми складемо формулу в комірці «B2» та скопіюємо її вниз на всю таблицю. Формула в «B2» має бути такою: «=Если(ДлСтр(Месяц(A2))=1;"0"&Месяц(A2);Месяц(A2))&"-"&Год(A2)». 
У цій формулі функція «
Год(А2)» визначає номер року для дати, записаної в «А2», функція «Месяц(А2)» повертає номер місяця цієї дати. Функція «ДлСтр(Месяц(A2))» визначає кількість символів (довжину рядка) у виразі «Месяц(A2)». Для січня це значення буде «1», а починаючи з лютого воно дорівнюватиме «2». Функція «Если()» перевіряє кількість символів у місяці (вираз «ДлСтр(Месяц(A2))»). Якщо вона дорівнює одиниці, функція зчіплює два рядки: «0» та номер місяця, отриманий функцією «Месяц(A2)», тобто «"0"&Месяц(A2)». Інакше функція «Если()» просто повертає значення «Месяц(A2)». Результат роботи функції «Если()» за допомогою операції зчеплення («&») об’єднується з роздільником «-» та функцією «Год()».Формула виходить довгою. Вирішуватимемо завдання «по діях», як у початковій школі. Комірку «
B2» поки що залишаємо порожньою. До будь-яких вільних елементів таблиці (я вибрав «B3» — «B6») запишемо функції, що знадобляться для складання формули:1) до комірки «
В3» за допомогою Майстра функцій вводимо формулу «Год(A2)», адресу «А2» вибираємо на робочому листі;2) у комірці «
В4» за допомогою Майстра будуємо формулу «Месяц(A2)». Зауважте, що адресу для функції ми зазначили ту саму;3) у комірці «
В5» Майстром функцій формуємо вираз «ДлСтр(1)». Параметр функції «1» — фіктивний. Він не має жодного значення та потрібний лише для того, щоб завершити роботу з Майстром і отримати заготовку функції. Пізніше ми її відкоригуємо;4) у комірці «
В6» будуємо функцію «Если(1;1;0)». Параметри функції «Если()» поки що теж фіктивні, вони потрібні лише для того, щоб використовувати шаблон функції, а не набирати її вручну.До цього моменту в нас є всі складові майбутньої формули. Залишається зібрати її в одне ціле в комірці «
B2» та відкоригувати параметри. Тут доречно ще раз нагадати, що формула в Excel — це звичайний текст. Тому до неї застосовні всі операції роботи з текстом, у тому числі робота з буфером обміну. Дуже важливо зрозуміти різницю між копіюванням до буфера фрагмента електронної таблиці й тексту формули.Важливо!
Коли ви перебуваєте на робочому листі Excel, при копіюванні до буфера обміну потрапляє об’єкт Excel — комірка чи блок комірок. Разом з умістом комірки до буфера буде скопійовано й інші атрибути: дані форматування, параметри захисту, примітки. Увійшовши всередину комірки (двічі клацнувши по ній мишею або натиснувши на клавішу «F2»), ви залишаєте робочий лист. Уключається режим текстового редактора і відкривається для редагування вміст комірки. До буфера обміну копіюватимуться виділені вами символи. При цьому навігація по робочому листу стає недоступною: курсор переміщатиметься лише в межах умісту комірки, тобто в межах текстового рядка.Використовуючи клавішу «
Shift» у поєднанні зі стрілками (або виділяючи блок за допомогою миші), можна позначити фрагмент тексту і помістити його до буфера обміну звичайним способом (через значок на панелі інструментів, поєднання клавіш «Ctrl+C» або «Ctrl+Ins»). Таким чином, у буфері обміну зберігатиметься не об’єкт Excel, а текст формули (повністю або частково). Щоб перенести його до іншої формули, потрібно спочатку увійти до режиму її редагування. Для цього ми повертаємося на робочий лист, натискуємо на клавішу «Esc». Далі стаємо на формулу, куди хочемо вставити текст із буфера обміну, і натискуємо на «F2» (входимо до режиму редагування комірки). Знайшовши місце вставки тексту, витягуємо дані з буфера обміну (клавішами «Ctrl+V», «Shift+Ins» або через значок на панелі інструментів).Повернемося до прикладу і приступимо до побудови формули для комірки «
В2»:1) стаємо на комірку «
В4», натискуємо на «F2». Потрапляємо до режиму редагування комірки. У ній записано текст «=Месяц(A2)». Виділяємо рядок «Месяц(A2)» (без символу «=») та копіюємо до буфера («Ctrl+Ins»);2) натискуємо на клавішу «
Esc», повертаємося на робочий лист. Переходимо всередину комірки «В5». Тут записано текст «=ДЛСТР(1)». Видаляємо символ «1», замість нього вставляємо вміст буфера обміну. Отримаємо формулу «=ДлСтр(Месяц(A2))». Виділяємо текст формули без символу «=» та копіюємо його до буфера;3) натискуємо на «
Esc» та переходимо на «В6». Входимо до режиму редагування, замість першого параметра функції «Если(1;1;0)» вставляємо вміст буфера. Отримуємо «=Если(ДлСтр(Месяц(A2));1;0)». Завершуємо умову, дописавши текст «=1» після функції «Длстр()», щоб отримати формулу: «=Если(ДлСтр(Месяц(A2))=1;1;0)»;4) підходимо до комірки «
B4», запам’ятовуємо в буфері текст «Месяц(A2)». Переносимо текст до формули на місце другого і третього параметрів. Отримуємо формулу: «=Если(Длстр(Месяц (A2))=1;Месяц(A2);Месяц(A2))»;5) залишилося у функції «
Если()» виправити другий параметр, дописавши операцію зчеплення номера місяця та рядка «0». Остаточно отримаємо: «=Если(Длстр(Месяц(A2))=1; "0"&Месяц(A2);Месяц(A2))»;6) із комірки «
B3» копіюємо вміст «Год(A2)», вставляємо його до формули комірки «B6» та об’єднуємо операцією зчеплення так, щоб отримати вираз: «=Если(Длстр(Месяц(A2))=1;"0"&Месяц(A2);Месяц(A2))&"-"&Год(A2) ». У цьому виразі текст «&"-"&» доведеться набрати вручну, а «Год (A2)» вставляємо з буфера обміну.Формула для комірки «
B2» готова. Залишається виділити текст у комірці «B6» (саме текст, використовуючи режим редагування) і вставити його до комірки «B2». Далі діємо як завжди: виділяємо блок комірок, до яких потрібно помістити формули, і натискуємо на «Ctrl+D». Блок буде заповнено формулами за зразком з комірки «B2». Посилання Excel відкоригує автоматично.Проаналізуємо, скільки інформації нам довелося набрати з клавіатури при створенні формули:
— створюючи Майстром функцію «
Длстр()», ми ввели її параметр — «1»;— створюючи Майстром функцію «
Если()», ми ввели три фіктивні параметри: «1», «1» і «0»;— тричі набрали знак операції зчеплення «
&»;— увели текст «
=1», закінчуючи умову у функції «Если()»;— набрали роздільник «
-» та рядок «0».Нам довелося набрати лише 13 символів. Формула в кінцевому варіанті містить 61 символ.
Ми скоротили роботу на 75,4 %! Але головне — не в економії кількості натиснень на клавіши. Головне в тому, що, користуючись Майстром функцій та буфером обміну, ми мінімізували помилки при записуванні функцій та перенесенні з однієї комірки до іншої. Отже, працюючи над прикладом, ви самостійно розгадали ще один секрет.Секрет
Створення формули частинами полегшує перевірку правильності роботи кожної її складової. Як тільки чергова зміна призведе до помилкового результату, відразу стане зрозуміло, де криється помилка.
Приклад 3. Завершуючи прайс-лист
У документі «Прайс-лист» із попередньої статті* ми залишили поза увагою дві формули. В об’єднаній комірці «
F8» потрібно написати коментар стосовно дати дії знижок для дилерів (рис. 8). Крім того, у комірці «A13» потрібно вставити повідомлення, що попередить про дату останнього коригування курсів валют. Спираючись на вже відомі прийоми, зробити це зовсім не важко:*
М. Карпенко «Excel 2007: іменовані діапазони» // «Б & К», 2009, № 8. 
1) стаємо на будь-яку вільну комірку. Наприклад, на «
I8»;2) входимо у Майстер функцій, вибираємо категорію «
Полный алфавитный перечень»;3) набираємо комбінацію «
СЕ» і відразу потрапляємо на функцію «Сегодня()»;4) у цієї функції немає параметрів, натискуємо на «
ОК». У комірці «I8» з’явиться поточна дата — це результат роботи функції;5) входимо до режиму редагування комірки «
I8». Запам’ятовуємо текст «Сегодня()» у буфері обміну;6) переходимо до комірки «
I9». Майстром вставляємо функцію «Текст()». Її можна вибрати із загального списку чи розділу «Текстовые». У функції два параметри. Перший — число, яке потрібно обробити. У нашому випадку тут стоятиме поточна дата, інакше кажучи — функція «Сегодня()». Вставляємо вміст із буфера обміну безпосередньо до поля першого параметра;7) заповнюємо другий параметр. Тут знаходиться рядок форматування. У різних версіях Excel він може відрізнятися. У моїй редакції рядок виглядає так: «
ДД/ММ/ГГ». Інколи потрібно увести «dd/mm/yy». Можливо, доведеться поекспериментувати. Завершивши введення параметра, натискуємо на «ОК». Переконуємося, що формула працює;8) між знаком «
=» та початком формули вдруковуємо пояснюючий текст, щоб остаточно отримати «="Скидки для дилеров на день" & ТЕКСТ(СЕГОДНЯ();"ДД/ММ/ГГ")»;9) у режимі редагування виділяємо текст формули і запам’ятовуємо в буфері обміну. Це важливо, оскільки я збираюся копіювати формулу до іншої комірки та хочу, щоб Excel
не коригував адреси. Якщо до буфера записати текст формули (а не вміст комірки), коригувань не буде;10) переходимо до комірки «
F8» і вставляємо вміст буфера обміну;11) залишається розібратися з коміркою «
A13». Формула в цій комірці має порівняти поточну дату (функція «Сегодня()» або вміст комірки «B1» на листі «Спр») з датою коригування курсів валют. Якщо дати не рівні, потрібно вивести застережливе повідомлення і дату коригування курсів. Вона є на листі «Спр» у комірці «B2». Для цієї формули нам знадобиться спочатку побудувати функцію «Если()» з фіктивними параметрами. В окремій комірці побудувати вираз для перетворення дати коригування курсів на текстове значення функцією «Текст()». А потім об’єднати результат, щоб вийшла така формула: «=Если(Текст(Сегодня();"dd.mm.yyyy") <>Спр!B2;"Проверьте курсы валют. Последняя дата корректировки "&Текст(Спр!B2;"dd/mm/yyyy");"")». Думаю, спираючись на досвід попереднього прикладу, ви зможете виконати це самостійно.
Налагодження формул
Працюючи над формулою частинами, ми не лише спрощуємо створення складних виразів за рахунок залучення Майстра і роботи з буфером обміну. Найважливіша перевага цього методу — можливість контролювати кожний етап своєї роботи, своєчасно локалізувати ту частину майбутньої формули, де виникла помилка. Але це — не єдиний механізм контролю за побудовою формул. В Excel існують й інші засоби для вирішення цього завдання. Зокрема, вікно контрольних значень. Ознайомитися з ним ми просто зобов’язані: чим різноманітнішим буде ваш арсенал контролю за обчисленнями, тим продуктивнішою стане робота.
Переходимо до головного меню, вибираємо розділ «
Формулы» та знаходимо групу іконок «Зависимости формул» (рис. 9)». У ній — чотири елементи, нам потрібне «Окно контрольного значения». Клацаємо по цій іконці лівою кнопкою миші. З’явиться вікно, показане на рис. 10. У вікні лише дві кнопки: «Добавить контрольное значение…» і «Удалить контрольное значение…». От як ними скористатися: 
1) стаємо на комірку «
H2» листа «Прайс». У ній записано спеціальну ціну товару «Блокнот»;2) клацаємо по кнопці «
Добавить контрольное значение…». У вікні з’явиться рядок з назвою книги «Прайс.xls», ім’ям листа «Прайс», адресою комірки «H2». Далі можна побачити результат формули «4,48» і текст формули «=C2*(1-Спр!$B$9)» (четвертий рядок на рис. 10). Уміст вікна оновлюється при зміні даних, як і комірки робочого листа. У вікні контрольних значень можна зібрати дані про роботу формул з різних листів робочої книги. Це дуже зручно, оскільки позбавляє необхідності перегортати робочу книгу, щоб переконатися у правильності обчислень. Додатковою перевагою вікна контрольних значень є те, що в ньому можна побачити імена діапазонів, якщо їх було призначено коміркам робочого листа. 
Важливо!
Та все-таки вікно контрольних значень ніяк не зменшує важливості поетапної побудови формул. Це — основний, універсальний прийом. Спробуйте внести до вікна контрольних значень посилання на «F8» листа «Прайс». Усе, що вдасться побачити, — результат роботи всієї формули. Роботу кожної функції у формулі Excel показати не може.Решта іконок у групі «
Зависимости формул» дозволяє відстежувати взаємозв’язок між комірками робочого листа. Відкрийте документ прайс-лист. Клацніть по листу «Прайс» і зробіть поточною комірку «H2». У ній записано формулу «=C2*(1-Спр!$B$9)». Натисніть на кнопку «Влияющие ячейки». На листі з’являться стрілки, що показують взаємозв’язки між комірками «C2» і «H2». Окрема стрілка покаже взаємозв’язок між «H2» та робочим листом «Спр». Кнопку «Зависимые ячейки» слід використовувати, коли поточною є комірка з даними і потрібно з’ясувати, з якими формулами ці дані пов’язані. Перегляд взаємопов’язаних та впливаючих комірок зручний при перевірці логіки зв’язків в електронній таблиці.
Формули та примітки
Кожного разу, оновлюючи таблицю, Excel змушений наново розпізнавати і перераховувати кожну формулу. Коли таблиця невелика, усе працює миттєво. Але як тільки таблиця стане великою, обросте складними формулами, її оброблення може відверто «пригальмовувати».
Повернемося для прикладу до бази даних на рис. 7. У реальному житті вона налічувала близько 95000 рядків. Відповідно лише в колонці «
МесГод» формула «=Если(Длстр(Месяц(A2))=1;"0"&Месяц(A2);Месяц(A2))&"-"&Год(A2)» повторювалася 95000 разів. І це лише в одній колонці. Звичайне оновлення даних на робочому листі забирало кілька секунд. Коли до таблиці додався десяток стовпців із проміжними обчисленнями, комп’ютер став відверто «гальмувати». У такій ситуації можна було застосувати просте рішення: скопіювати таблицю до буфера обміну, а потім через спеціальну вставку перетворити її на значення. Інакше кажучи, «забути» формули і залишити тільки результат їх роботи. По суті, завдання це було цілком допустимим: безглуздо кожного разу перераховувати всі значення в колонці «МесГод» для всіх рядків вихідної таблиці. Дати в базі ми не змінюємо. Колонка «МесГод» має допоміжний характер і застосовується лише для групування даних. Тому достатньо однин раз обчислити її, запам’ятати отримані значення і потім користуватися ними. Та все-таки втрачати формули чомусь не хотілося — раптом ще знадобляться? Я застосував прийом, який раджу взяти на замітку.До будь-якої комірки Excel можна додати
примітку. Для цього потрібно натиснути на праву кнопку миші, з контекстного меню вибрати пункт «Вставить примечание». З’явиться невелике віконце, в якому можна набрати текст. Комірку з приміткою буде позначено червоним трикутником у правому верхньому куті (рис. 11, комірка «B2»). Слід навести покажчик миші на цей трикутник, і Excel автоматично покаже примітку у вигляді спливаючої підказки. 
Примітки можна коригувати. Для цього потрібно клацнути правою кнопкою миші по комірці з приміткою та вибрати з контекстного меню пункт «
Изменить примечание». З цього моменту можна працювати з приміткою як зі звичайним текстом. Як примітки стосуються спеціальної вставки? Найбезпосередніше.Секрет
У примітках можна зберігати тексти формул. У будь-який момент такий текст із примітки можна скопіювати до буфера обміну і повернути на робочий лист. Ось що це дає стосовно нашої бази на рис. 7. Ми можемо пройтися по одному рядку таблиці (у прикладі це рядок «2») і послідовно перенести тексти формул з комірок у примітки до комірок. Потім скопіювати всі дані до буфера обміну, повернути їх звідти як значення, цим видаливши формули з робочого листа. Але примітки до комірок при спеціальній вставці залишаться! Таблиця без формул оброблятиметься миттєво. Водночас у будь-який момент ми зможемо увійти всередину примітки, узяти звідти текст формули, вставити його на робочий лист. Потім скопіювати формулу по всій таблиці та відновити її первісний стан.Щоб зберегти текст формули всередині примітки, потрібно зробити так:
1) натиснути на «
F2», увійти до режиму редагування комірки. У прикладі це комірка «B2»;2) скопіювати
текст формули до буфера обміну;3) повернутися на робочий лист, натиснувши на «
Esc»;4) клацнути по комірці правою кнопкою миші, з контекстного меню вибрати «
Вставить примечание»;4) у вікні примітки вставити текст формули з буфера обміну.
Тепер формули можна видалити. Для цього виконуємо такі дії.
1) виділяємо всю таблицю (можна натиснути на «
Ctrl+А»);2) копіюємо її до буфера обміну («
Ctrl+С»);3) не знімаючи виділення, клацаємо по значку списку біля іконки «
Вставить» меню «Главная». Розкриється перелік можливих варіантів. Вибираємо «Специальная вставка…»;4) у вікні спеціальної вставки знаходимо перемикач «
Вставить» і ставимо його в положення «Значение»;5) натискуємо на «
ОК».Усе! Замість формул у таблиці залишилися значення. Тільки в одній комірці у вигляді примітки зберігається текст формули. У будь-який момент можна ввійти до режиму зміни примітки, скопіювати текст формули до буфера і повернути його до комірки робочого листа. Для великих таблиць економія місця на жорсткому диску і приріст швидкості оброблення просто колосальні.
Висновки
1. Формула в Excel — це текст, складений за певними правилами. Тому до формул застосовні всі операції роботи з текстом. При редагуванні та створенні формул можна використовувати буфер обміну. До формул можна застосовувати операції контекстної заміни.
2. Щоб прискорити створення посилань, не вводьте адреси з клавіатури. Адреси до формули можна підставити клацанням миші на робочому листі.
3. Створюючи формулу, контролюйте тип адресації. Здебільшого використовують відносні посилання, які автоматично змінюються при копіюванні формул. Абсолютні посилання при копіюванні формул не змінюються.
4. Щоб змінити тип посилання, у формулі потрібно відкрити комірку на редагування, установити курсор на адресу посилання і послідовно натискувати на клавішу «
F4». Кожне натиснення вставляє символ «$» перед різними складовими адреси.5. Елементам таблиці можна присвоїти імена. Використання імен спрощує читання формул та сприяє зниженню кількості помилок. За умовчанням імена комірок є посиланнями з абсолютною адресацією.
6. Excel 2007 містить близько 400 вбудованих функцій. Їх використання полегшує вирішення багатьох практичних завдань. Вставити функцію зручно за допомогою Майстра функцій. При роботі з Майстром адреси вводяться так само, як у формулах, — клацанням миші по робочому листу.
7. Майстер функцій, буфер обміну, формування посилань по робочому листу дозволяють побудувати формулу будь-якої складності швидко та без помилок.
8. Довгі формули потрібно будувати частинами в окремих комірках, а потім об’єднати, використовуючи буфер обміну.
9. Елементи електронної таблиці можуть містити примітки. Зазвичай примітки застосовуються для зберігання «нотаток на пам’ять». Але до приміток можна запам’ятати й тексти формул. Це дозволяє прискорити оброблення таблиці та водночас залишити можливість відновити формули.
На цьому ми завершуємо тему роботи з формулами і переходимо до проблеми форматування електронних таблиць в Excel 2007. І, звичайно ж, нас цікавитиме головним чином бухгалтерська специфіка в цьому питанні.
Чекаю ваших листів, пропозицій та зауважень на
bk@id.factor.ua, nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum . Успішної роботи!
