Номер кварталу за назвою місяця
Шановні працівники «Б & К»! У мене запитання щодо роботи з програмою Excel. Є база даних про рух ТМЦ. В окремій колонці цієї бази вказано місяць постачання або отримання товару, причому записано його як текст — «січень», «лютий», «березень» тощо. За наявною базою мені потрібно сформувати поквартальний звіт про рух товарів. Побудувати такий звіт нескладно, достатньо задіяти інструмент зведених таблиць. Але для цього потрібно в окремій колонці проставити номер кварталу за назвою місяця. Підкажіть, як вирішити цю проблему? Серед вбудованих функцій Excel я відповідного варіанта не знайшов, а коригувати величезний файл уручну не хотілося б. Наперед завдячую.
Олександр Соколов, бухгалтер, м. Харків
Відповідає Микола КАРПЕНКО, канд. техн. наук, доцент кафедри прикладної математики та інформаційних технологій Харківської національної академії міського господарства
У програмі Excel визначити номер кварталу за назвою місяця можна різними способами. Але в будь-якому разі це будуть формули, оскільки готової функції в бібліотеці Excel для вирішення такого завдання немає. Я пропоную обмежитися двома варіантами. Перший ґрунтується на функціях «ИЛИ()» та «ЕСЛИ()», а другий — використовує специфічний прийом роботи з текстом програми Excel. Обидва ці способи я покажу на прикладі таблиці, яку наведено на рис. 1. У ній усього дві колонки: «Дата» — тут записана назва місяця та «НомКварт» — поки в ній нічого немає, до цього стовпця ми запишемо формулу для визначення номера кварталу. Розпочнемо.
СПОСІБ 1. Виглядає громіздким, зате простий та працює в будь-якому табличному процесорі — чи то Excel, чи то безоплатний Calc. Робимо так:
1. Стаємо в комірку «B2».
2. Вводимо формулу
«=ЕСЛИ(ИЛИ(A2="Январь";A2="Февраль";A2="Март");1;ЕСЛИ(ИЛИ(A2="апрель";A2="май";A2="июнь");2;ЕСЛИ(ИЛИ(A2="июль";A2="август";A2="сентябрь");3;ЕСЛИ(ИЛИ(A2="октябрь";A2="ноябрь";A2="декабрь");4;""))))».
3. Копіюємо формулу на всю висоту таблиці. Результат показано на рис. 2.
Кілька слів скажемо про саму формулу. Вона складається з чотирьох вкладених функцій «ЕСЛИ()». Кожна з них перевіряє належність місяця до одного конкретного кварталу, використовуючи для цього логічну функцію «ИЛИ()». Беремо перший варіант. Вираз «ИЛИ(A2="Январь";A2="Февраль";A2="Март")» буде істинним у випадку, якщо в комірці «A2» буде записано одне із трьох значень, — «Январь», «Февраль» або «Март». І тоді перша функція «ЕСЛИ()» поверне «1», тобто номер кварталу для зазначених місяців. В іншому разі почне працювати друга вкладена функція «ЕСЛИ()», яка перевірить місяці «Апрель», «Май», «Июнь» і так далі.
Тепер з приводу введення цієї формули. Про техніку створення складних виразів ми неодноразово писали на сторінках нашого видання (див., наприклад, «Б & К», 2006, № 1). Тому тут я нагадаю тільки основні моменти, як це зробити швидко і без помилок.
Отже, наша формула, по суті, складається з двох функцій — «ИЛИ()» та «ЕСЛИ()». Більше того, кожна функція «ЕСЛИ()» фактично повторюється чотири рази. Перша функція обробляє ситуацію для першого кварталу (назва місяця «Январь», «Февраль» або «Март»). Друга вкладена функція аналізує ситуацію, коли місяць потрапляє до другого кварталу і так далі. Така повторюваність дозволяє істотно скоротити роботу при створенні формули. Для цього досить пригадати, що формула — це звичайний текст. Тому при її створенні можна задіяти буфер обміну, а окремі компоненти формули побудувати Майстром функцій. І тоді сам процес створення виглядатиме так:
1. Стаємо на вільну комірку (наприклад, на «C2»).
2. Викликаємо Майстра функцій (клацаємо по значку «fx» в області формул). Відкриється вікно «Мастер функций — шаг 1 из 2» (рис. 3).
3. У цьому вікні параметр «Категория:» встановлюємо в положення «Полный алфавитный перечень», після чого в списку «Выберите функцию:» клацаємо по елементу «ИЛИ()». Відкриється вікно з параметрами логічної функції «ИЛИ()» (рис. 4).
4. У цьому вікні заповнюємо параметри, як показано на рис. 4. При цьому адресу «A2» підставляємо у бланк клацанням миші по комірці робочого листа.
5. У вікні Майстра функцій натискуємо «ОК». У комірці «C2» отримаємо формулу «=ИЛИ(A2="Январь";A2="Февраль";A2="Март")». Для прикладу на рис. 1 її значення буде «ИСТИНА».
Переходимо до створення функції «ЕСЛИ()». Робимо так:
1. Стаємо на комірку «D2», викликаємо Майстра функцій.
2. У списку «Выберите функцию:» знаходимо елемент «ЕСЛИ()», клацаємо по ньому лівою кнопкою миші.
3. У параметрах функції заповнюємо фіктивні значення, наприклад: «1», «1», «0» (рис. 5).
4. У вікні Майстра функцій натискуємо «ОК». У комірці «D2» отримуємо результат «=ЕСЛИ(1;1;0)».
Настав час підставити формулу з комірки «С2» у функцію «ЕСЛИ()». Робимо так:
1. Стаємо на комірку «B2», натискуємо клавішу «F2» (переходимо в режим редагування змісту комірки).
2. Виділяємо текст формули без знака «=».
3. Копіюємо його в буфер обміну (комбінація «Ctrl+C»).
4. Натискуємо «Esc» (виходимо з режиму редагування формули).
5. Стаємо на комірку «D2», натискуємо клавішу «F2».
6. Стираємо перший параметр у функції «ЕСЛИ()» (у цей момент він дорівнює «1») і замість нього вставляємо вміст буфера обміну (комбінація «Ctrl+V»).
7. Натискуємо «Enter». У комірці «D2» вийде формула «=ЕСЛИ(ИЛИ(A2="Январь";A2="Февраль";A2="Март");1;0)».
Зараз ця формула обробляє найменування перших трьох місяців року. Якщо назва місяця в комірці «A2» збігатиметься з переліченими у формулі, функція «ЕСЛИ()» поверне значення «1» (другий параметр). В іншому разі результат її роботи дорівнюватиме «0».
Усе, що нам залишається зробити, — це розширити функцію для обробки другого, третього і четвертого кварталів. Для цього потрібно вираз з функцією «ЕСЛИ()» скопіювати у формулі три рази. Причому кожен наступний примірник функції вставляти замість третього параметра попередньої функції «ЕСЛИ()». Робимо так:
1. Стаємо на комірку «D2», натискуємо клавішу «F2».
2. Виділяємо текст формули без знака «=».
3. Копіюємо його в буфер обміну (натисканням «Ctrl+V»).
4. У формулі видаляємо третій параметр функції «ЕСЛИ()» (зараз він дорівнює «0») і замість нього вставляємо вміст буфера обміну. У результаті ми отримаємо формулу: «=ЕСЛИ(ИЛИ(A2="Январь"; A2="Февраль";A2="Март");1;ЕСЛИ(ИЛИ(A2= "Январь";A2="Февраль";A2="Март");1;0))».
5. У вкладеній функції «ЕСЛИ()» коригуємо назви місяців та змінюємо номер кварталу (він має дорівнювати «2»). У результаті формула повинна виглядати так (зміни в ній виділено напівжирним зображенням): «=ЕСЛИ(ИЛИ(A2="Январь";A2="Февраль";A2="Март");1; ЕСЛИ(ИЛИ(A2="Апрель";A2="Май";A2="Июнь");2;0))».
6. Таким же прийомом вставляємо у формулу фрагменти для обробки третього і четвертого кварталів.
У цілому формула готова. Єдиний її недолік: якщо найменування місяця написане з помилкою та не відповідає жодному кварталу, то результат роботи нашого виразу буде «0». Щоб виправити цю ситуацію, потрібно останній параметр функції «ЕСЛИ()» змінити, і тоді формула остаточно виглядатиме так: «=ЕСЛИ(ИЛИ(A2="Январь";A2="Февраль";A2="Март");1; ЕСЛИ(ИЛИ(A2="апрель";A2="май";A2="июнь");2;ЕСЛИ(ИЛИ (A2="июль";A2="август";A2="сентябрь");3;ЕСЛИ(ИЛИ(A2="октябрь";A2="ноябрь";A2=
"декабрь");4; "Ошибка"))))» (зміни виділено напівжирним зображенням).
Як я говорив, описаний спосіб перетворення назви місяця в номер кварталу універсальний. Наприклад, у програмі Calc пакета OpenOffice формула виглядатиме так:
«=IF(OR(A2="Январь";A2="Февраль";A2="Март");1;IF(OR(A2="апрель";A2="май";A2="июнь");2;IF(OR(A2="июль";A2="август";A2="сентябрь");3;IF(OR(A2="октябрь";A2="ноябрь";A2="декабрь");4; «Ошибка"))))».
Як бачите, незважаючи на страхітливий зовнішній вигляд, побудувати формулу зовсім нескладно. При відповідних навичках на цю роботу знадобиться щонайбільше одна-дві хвилини.
СПОСІБ 2. Витончений, але працює тільки в MS Excel (будь-якій версії). У програмі Calc застосувати його, на жаль, не можна. Робимо так:
1. Стаємо в комірку «B2».
2. Вводимо формулу «=ЦЕЛОЕ((МЕСЯЦ(1&A2)-1)/3)+1».
3. Копіюємо формулу на всю висоту таблиці. Результат показано на рис. 2.
Коментар з приводу формули. Первісно в комірці «A2» у нас записана назва місяця літерами (у прикладі на рис. 1 це «Январь»). Вираз «1&A2» поверне результат «1Январь». До речі, замість «1&E2» можна написати і вираз із зазначенням року — «1&A2&2012», «1&A2&2011», «1&A2&1999» тощо.
У програмі Excel працює потужний аналізатор даних. Він автоматично перетворить значення «1Январь» на дату «01/01». Оскільки рік у формулі явно не вказано, Excel візьме його поточне значення та додасть до проміжного результату. І тоді вираз «1Январь» буде перетворено на «01/01/2012». Після цього функція «МЕСЯЦ(1&А2)» поверне номер місяця для вказаної дати, і стосовно комірки «А2» ми отримаємо «1». А в загальному випадку це буде число в діапазоні від «1» до «12». Із цього числа віднімаємо одиницю, щоб отримати значення в діапазоні від «0» до «11». Потім результат ділимо на «3» та застосовуємо до частки функцію «ЦЕЛОЕ()». У результаті для перших трьох місяців року формула поверне значення «0», для місяців з квітня по червень це буде значення «1» і так далі. Усе, що залишається зробити, — це додати до результату «1» і тим самим отримати номер кварталу. От, власне, і все.
Успішної роботи! Чекаю ваші запитання, зауваження та пропозиції на bk@id.factor.ua, nictomkar@rambler.ru або на форумі редакції www.bk.factor.ua/forum .
