За что я люблю EXCEL

За огромный запас прочности: всегда существует множество вариантов решения задачи, той или иной степени автоматизации. Хочешь – перебивай все ручками, хочешь – оптимизируй самую трудозатратную часть работ. А хочешь – найди решение, где от тебя потребуется просто нажать кнопку «ОК».

Типичный пример. Отдел кадров прислал данные по сотрудникам в табличном виде: ФИО и дата рождения в формате ДАТА (дд.ММ.гггг). Требуется из этой даты отсечь только день и месяц рождения, причем месяц должен быть написан буквами и в родительном падеже :«Родилась 11 октября» и т.п.

Скриншот EXCEL рис 1

Рис.1

Конечно, для 4 человек проще всего все написать ручками. А если человек (строк) over 1000?

  1. Будем решать задачу маленькими действиями.

Превратим содержимое столбца B в ТЕКСТ, вместо формата ДАТА. Для этого нам нужна формула:

=ТЕКСТ(B2; «дд.ММ.гггг»)

Получилось вроде бы тоже самое, но в текстовом виде.

Скриншот EXCEL рис 2

Рис.2

Возьмем от этой ячейки только первые 2 символа:

=ЛЕВСИМВ(D2;2)

Скриншот EXCEL рис 3

Рис.3

Запросим из исходного столбца месяц в текстовом виде:

=ТЕКСТ(B2; «ММММ»)

Скриншот EXCEL рис 4

Рис.4

Проблема: месяц в именительном падеже. Что мы хотим получить в итоге?

Скриншот EXCEL рис 5

Рис.5

Вроде ничего сложного, реально нам нужно только последняя буква месяца. Если месяц заканчивается на «ь» — нужно поменять его на «я». Если последняя буква «т» — добавить после нее «а», и, наконец, если «й» — тоже заменить на «я».

«Откусываем» последний символ в ячейки справа:

=ПРАВСИМВ(F2)

Скриншот EXCEL рис 6

Рис.6

Описываем условия замены (возможно не самая лаконичная формула, но и так сойдет):

=ЕСЛИ(G2= «ь»; «я»;ЕСЛИ(G2= «й»; «я»; «та»))

Скриншот EXCEL рис 7

Рис.7

Склеиваем наши месяца из столбца F с полученными последними буквами:

=ЛЕВСИМВ(F2;ДЛСТР(F2)-1)&H2

Скриншот EXCEL рис 8

Рис.8

Проблема: первая буква заглавная. Впрочем, решить ее элементарно:

=СТРОЧН(I2)

Скриншот EXCEL рис 9

Рис.9

Почти готово. Теперь просто собираем результаты из столбцов E и J, не забыв про пробел. Пишем в ячейке C2:

=E2& » » &J2

Скриншот EXCEL рис 10

Рис.10.

Получилось, но как-то сложно, да? Месяцев у нас всего 12, в отличие от человек на предприятии.

  1. Более простое решение.

Попробуем соотнести название месяца с тем, что нам надо получить в итоге. Достаточно всего одной формулы, причем сразу к исходной таблице (рис.1):

=ВЫБОР(МЕСЯЦ(B2); «января»; «февраля»; «марта»; «апреля»; «мая»; «июня»; «июля»; «августа»; «сентября»; «октября»; «ноября»; «декабря»)

Получаем те же самые «октября», «мая» и.т.д.

Круто? Круто и … Напрасно. Поступим еще проще:

  1. Изменим вручную формат ячеек

Пр.кнопкой на столбце B – Формат ячеек. Выбираем (все форматы)

Скриншот EXCEL рис 11

Рис.11

Находим нужный формат. Удаляем ГГГГ т.к. год нам не нужен, оставляем только [$-FC19]ДД ММММ;@. Жмем «ОК» (как я и обещал в самом начале).

И вот незадача, Excel все сделал за нас…

Share Button

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.