Номер квартала по названию месяца
Уважаемые сотрудники «Б & К»! У меня вопрос по работе с программой 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.
