Как поменять падеж в excel
Не сложно догадаться, для чего необходима функция =СКЛОНЕНИЕ(ТЕКСТ;ПАДЕЖ). Практически любое существительное или ФИО она переведет из именительного падежа в нужный падеж.
Подчеркну слово практически, так как имеются фамилии, которые без программы то сложно просклонять, но в 90% случаев макрос отрабатывает достойно. Проверяйте сложные или необычные фамилии дополнительно.
Формула имеет всего 2 аргумента:
Вот так выглядит стандартное окно ввода параметров функции СКЛОНЕНИЕ
Функция СКЛОНЕНИЕУКР
Аналогичная функция, которая склоняет ФИО и существительные, но только на украинском языке. Синтаксис у нее также не отличается =СКЛОНЕНИЕУКР(ТЕКСТ;ПАДЕЖ).
Далее приводятся примеры, наглядно демонстрирующие работу функции.
Обращаю ваше внимание, что для корректной работы данных функций необходимо стабильное подключение к интернету, а точнее к сайту Морфер.ру. Имеется также суточное ограничение на использование функций, которое в настоящий момент составляет 1000 склонений.
Склонение простой фамилии по всем падежам.
Склонение сразу фамилии имени и отчества по всем падежам.
Склонение сложных фамилий и имен в родительный падеж.
Перевод в родительный падеж в excel
Функция позволяет выводить фамилию, имя и отчество, заданные в именительном падеже, в форме любого другого падежа.
Область применения:
Функцию удобно использовать для автоматизации формирования документов в MS Excel (или облегчения создания их вручную), где ФИО необходимо указывать не в именительном падеже: различного рода договоры, кассовые ордера, квитанции, акты сверок, приказы, протоколы, доверенности и т.д.
Функция также незаменима, если средствами автоматизации реализовано формирование перечисленных выше документов в приложении MS Word на основании данных, находящихся в таблицах MS Excel.
Примеры использования:
Что реализовано:
Как подключить?
Программа представляет собой один файл с названием bdgFIOPropisyu.bas. Для того, чтобы использовать ее в том или ином файле, функцию нужно импортировать в этот файл. Для этого необходимо:
Все, программный код импортирован и его можно использовать.
Как использовать?
Ситуации, когда работа функции может быть некорректной:
Правила склонения фамилий и имен использованы в соответствии с разделами 13.1 и 13.2 работы Н. А. Еськовой «Трудности словоизменения существительных. Учебно-методические материалы к практическим занятиям по курсу «Язык современной печати» (Госкомитет печати СССР. Всесоюзный институт повышения квалификации работников печати. М., 1990).
Функция перевода ФИО в родительный падеж
Пользовательская функция (UDF) для перевода ФИО (фамилии, имя, отчества) в родительный падеж.
Новые версии функций (изменения от 2019 года) доступны в надстройке FIO + Propis
Эту функцию можно использовать как в коде программы, так и как формулу в ячейках листа Excel
(см. пример в прикреплённом файле)
PS: Функция является переделкой аналогичной UDF для склонения в дательном падеже.
Тестировал склонение на списке разнообразных ФИО (см. первый столбец в прикреплённом файле), и заведомо корректных результатах склонения (третий столбец)
Если вы используете функции склонения для формирования документов,
обратите внимание на специализированную надстройку FillDocuments,
которая позволит вам одним нажатием кнопки создать документы Word и Excel по шаблонам, а также выполнить рассылку писем.
В указанную надстройку включены самые последние версии функций склонения.
Код функции GenitiveCase (версия от 29 января 2013 года):
Читать еще: Форматирование ячейки по значению другой ячейки excelСклонение по падежам — формулы и макросы
Периодически специалистам, чья деятельность связана с написанием большого количества текстов — SEO/PPC специалистам, контент-менеджерам, администраторам баз данных и каталогов, копирайтерам и т.д. — бывает нужна возможность просклонять слова по падежам.
Мечта каждого — сделать это прямо в Excel с помощью простых функций.
Надстройка !SEMTools дает такую возможность! Причем как платная, так и бесплатная версия. По ссылке можно скачать файл-пример, в котором использованы все функции склонения по падежам — для существительных и прилагательных. Функции будут работать автоматически у любого, кто скачал и установил надстройку !SEMTools.
Отличие бесплатной версии — в ней это возможно сделать только формулами.
Макросы применить тоже получится, но присутствует стандартное ограничение — обрабатываются только часть исходных данных, треть данных заменяется дисклеймером «Доступно в полной версии».
Активированная версия надстройки поддерживает и формулы, и макросы. Макросы можно вызывать с помощью меню «Слова» в группе «ИЗМЕНИТЬ«.
ВАЖНО: При склонении существительных функции могут допускать ошибки, просьба сообщать автору о таковых. Очень хочется довести функционал до совершенства, но склонение существительных практически не поддается простым алгоритмам — существуют тысячи алгоритмических правил и такие же тысячи исключений из них.
Дата и месяц прописью в Excel
Узнаем как написать дату и месяц прописью в Excel (в том числе в именительном и родительном падежах).
Подписывая какие-либо документы мы помимо собственного автографа очень часто вписываем туда и текущую дату в виде числа, месяца и года. При этом мы почти никогда там не встречаем месяц записанный в численном формате. Ведь, например, дата записанная как 12.11.2016 куда сложнее воспринимается и читается чем 12 ноября 2016 г.
Вот и в Excel иногда требуется указать дату в таком виде, поэтому давайте подробно разберемся какие у нас есть варианты реализации.
Запись с помощью настройки формата ячейки
В Excel существует достаточно большое количество форматов отображения даты.
Давайте запишем дату в произвольную ячейку и перейдем в ее формат (щелкаем по ячейке правой кнопкой мыши и выбираем Формат ячейки, или просто нажимаем сочетание клавиш Ctrl + 1).
В открывшемся окне нас интересует вкладка Число:
Среди форматов дат выбираем запись месяца прописью и получаем:
Можно выбрать запись как со звездочкой (символ «*»), так и без, при этом различие отображения будет заключаться в изменении вида записи при смене настроек времени и даты операционной системы.
Идем дальше и перейдем к формульному решению задачи.
Запись с помощью формул
Как мы уже разбирали в примере визуализации половозрастной пирамиды, формат любой ячейки записывается с помощью маски отображения. В случае с датой наиболее популярный вид записи (например, для 12.11.2016) выглядит как ДД.ММ.ГГГГ, где Д — день, М — месяц, Г — год.
Поэтому такого же результата мы сможем добиться воспользовавшись стандартной функцией ТЕКСТ (в английской версии TEXT), которая преобразует заданный текст в определяемый нами формат записи.
В качестве формата записи в данном случае указываем [$-FC19]Д ММММ ГГГГ г.;@, применяем функцию для даты и получаем:
Чуть подробнее остановимся на формате.
В записи формата [$-FC19] как раз и отвечает за корректный формат отображения даты в родительном падеже (можете попробовать убрать [$-FC19] и посмотреть что получится).
Если же нужно отобразить месяц не на русском, а, например, на украинском языке, то используйте [$-FC22] (для белорусского [$-FC23]):
Ок, с полной записью даты и месяца разобрались, но что если нам нужен только месяц?
Как записать месяц прописью?
Как и в примере выше воспользуемся функцией ТЕКСТ. Формат «ММММ» даст нам полную запись месяца (в именительном падеже):
Ещё одним способом является совместное применение функций МЕСЯЦ (в английской версии MONTH) и ВЫБОР (английский вариант CHOOSE).
Месяц вернёт нам порядковый номер месяца указанной даты (от 1 до 12), а ВЫБОР сопоставит числовому значению текстовое (где 1 — январь, 2 — февраль, …, 12 — декабрь):
Если же мы пишем число с месяцем и годом, то зачастую месяц нужно указать в родительном падеже (а не в именительном как в примере выше), например, 2 февраля 2015 года или 14 августа 2012 года и т.п.
Здесь нам опять поможет формат [$-FC19]ММММ, который записывает месяц в дате в родительном падеже, отличие от предыдущего варианта записи только в том, что мы убрали из записи день и год:
Есть и альтернативный вариант. На помощь опять придут уже знакомые функции МЕСЯЦ и ВЫБОР, только тут вместо именительного падежа для месяцев прописываем родительный (меняем окончания):
На этом все. Выбирайте наиболее понравившийся и удобный для вас способ.
Спасибо за внимание!
Если у вас остались вопросы по теме статьи — пишите в комментариях.
На данный момент в Microsoft Excel имеется почти пять сотен функций рабочего листа, доступных через окно Мастера функций - кнопка fx в строке формул. Это весьма приличный набор, но, тем не менее, почти каждый пользователь рано или поздно сталкивается с ситуацией, когда в этом списке не оказывается нужной ему функции - просто потому, что её нет в Excel.
До сих пор единственным способом решить эту проблему были макросы, т.е. написание своей пользовательской функции (UDF = User Defined Function) на Visual Basic, что требует соответствующей программистской квалификации и бывает, порой, совсем непросто. Однако, с последними обновлениями Office 365 ситуация изменилась в лучшую сторону - в Excel была добавлена специальная "обёрточная" функция LAMBDA. С её помощью задача по созданию собственных функций теперь решается легко и красиво.
Давайте рассмотрим принцип её использования на следующем примере.
Как вы, скорее всего, знаете в Excel есть несколько функции для разборки даты позволяющие определить номер дня, месяца, недели и года для указанной даты. Но почему-то нет функции, определяющий номер квартала, который тоже часто нужен, правда? Давайте исправим этот недостаток и создадим с помощью LAMBDA собственную новую функцию для решения этой задачи.
Шаг 1. Пишем формулу
Начём с того, что вручную привычным образом напишем формулу в ячейке листа, которая вычисляет то, что нам нужно. В случае с номером квартала это можно сделать, например, так:
Шаг 2. Заворачиваем в LAMBDA и тестируем
Теперь пришло время применить новую функцию LAMBDA и завернуть в неё нашу формулу. Синтаксис у функции следующий:
=LAMBDA( Переменная1 ; Переменная2 ; . ПеременнаяN ; Выражение )
где сначала перечисляются имена одной или нескольких переменных, а последним аргументом всегда идёт формула или вычисляемое выражение, которое их использует. Имена переменных не должны быть похожи на адреса ячеек и не должны содержать точки.
В нашем случае переменная будет только одна - дата, для которой мы считаем номер квартала. Переменную для неё давайте назовем, допустим, d. Тогда завернув нашу формулу в функцию LAMBDA и подменив адрес исходной ячейки A2 на придуманное имя переменной, мы получим:
Обратите внимание, что после такого преобразования наша формула (по сути - правильная!) стала выдавать ошибку, т.к. теперь в неё не передается исходная дата из ячейки А2. Для тестирования и уверенности, можно передать ей аргументы, дописав их после функции LAMBDA в круглых скобках:
Шаг 3. Создаем имя
Теперь самая легкая и приятная часть. Открываем Диспетчер имен на вкладке Формулы (Formulas - Name Manager) и создаём новое имя кнопкой Создать (Create) . Придумываем и вводим имя для нашей будущей функции (например, НОМКВАРТАЛА), а в поле Ссылка (Reference) аккуратно копируем из строки формул и вставляем нашу функцию LAMBDA, только без последнего аргумента (А2):
Всё. После нажатия на ОК созданную функцию можно использовать в любой ячейке на любом листе этой книги:
Использование в других книгах
Поскольку созданные с помощью LAMBDA пользовательские функции представляет из себя, по сути, именованные диапазоны, то можно легко сделать их доступными не только в текущей книге. Достаточно будет скопировать ячейку с функцией и вставить в любое место листа другого файла.
LAMBDA и динамические массивы
Пользовательские функции, создаваемые с помощью функции LAMBDA успешно поддерживают работу с новыми динамическими массивами и их функциями (ФИЛЬТР, УНИК, СОРТ), добавленными в Microsoft Excel в 2020 году.
Допустим, мы хотим создать новую пользовательскую функцию, которая сравнивала бы два списка и выдавала разницу между ними - те элементы из первого списка, которые отсутствуют во втором. Жизненная задача, не правда ли? Раньше для такого использовали либо функции а-ля ВПР (VLOOKUP) , либо сводные таблицы, либо запросы Power Query. Теперь же можно обойтись одной формулой:
В английской версии это будет:
Здесь функция СЧЁТЕСЛИ подсчитывает количество вхождений каждого элемента первого списка во второй, а затем функция ФИЛЬТР отбирает только те из них, у кого этих вхождений не оказалось. Завернув эту конструкцию в LAMBDA и создав на её основе именованный диапазон с названием, например, ПОИСКОТЛИЧ - мы получим удобную функцию, выдающую результат сравнения двух списков в виде динамического массива:
Если в качестве исходных данных будут не обычные, а "умные" таблицы - наша функция тоже справится без проблем:
Другой пример - динамическое разделение текста путём превращения его в XML и последующего разбора по ячейкам с помощью функции ФИЛЬТР.XML, которое мы недавно разбирали. Чтобы не воспроизводить эту сложную формулу каждый раз вручную, проще будет завернуть её в LAMBDA и создать на её основе динамический диапазон, т.е. новую компактную и удобную функцию, назвав её, например, РАЗДТЕКСТ:
Первым аргументом этой функции будет ячейка с исходным текстом, а вторым - символ-разделитель, в возвращать результат она будет в виде горизонтального динамического массива. Код функции при этом будет следующим:
Список примеров можно продолжать бесконечно - в любой ситуации, где вам часто приходится вводить одну и ту же длинную и громоздкую формулу функция LAMBDA сделает жизнь ощутимо проще.
Рекурсивный перебор символов
Все предыдущие примеры демонстрировали только одну, наиболее очевидную, сторону функции LAMBDA - её применение в качестве "обёртки" для заворачивания в неё длинных формул и упрощения их ввода. На самом деле, у LAMBDA есть ещё одна, гораздо более глубокая, сторона, превращающая её почти что в полноценный язык программирования.
Дело в том, что принципиально важной особенностью LAMBDA-функций является возможность реализации в них рекурсии - логики вычислений, когда в процессе расчета функция вызывает сама себя. С непривычки, возможно, звучит жутковато, но в программировании рекурсия - обычное дело. Даже в макросах на Visual Basic можно её реализовать, а теперь, вот, как видите, дошло и до Excel. Давайте попробуем разобраться в этой технике на практическом примере.
Предположим, нам требуется создать пользовательскую функцию, которая удаляла бы из исходного текста все заданные символы. Полезность такой функции, думаю, доказывать вам не нужно - с её помощью было бы очень удобно очищать замусоренные входные данные, правда?
Однако, по сравнению с предыдущими, нерекурсивными примерами, нас ожидают две сложности.
- Нам придётся придумать название для нашей функции до того, как мы начнём писать её код, т.к. в нём это название уже будет использоваться для вызова функцией самой себя.
- Ввести такую рекурсивную функцию в ячейку и отлаживать её, указав после LAMBDA аргументы в скобках (как мы делали ранее) не получится. Придётся создавать функцию сразу "с нуля" в Диспетчере Имен (Name Manager).
Назовём нашу функцию, допустим, ОЧИСТКА и хотелось бы, чтобы у неё было два аргумента - текст, который нужно почистить и список исключаемых символов в виде текстовой строки:
Создадим, как делали ранее, на вкладке Формулы в Диспетчере имён именованный диапазон, назовём его ОЧИСТКА и введём в поле Диапазон следующую конструкцию:
Здесь переменная t - это исходный очищаемый текст, а d - список символов на удаление.
Работает всё это следующим образом:
Итерация 1
Фрагмент ПОДСТАВИТЬ(t;ЛЕВСИМВ(d);""), как легко догадаться, заменяет в исходном тексте t первый слева символ из удаляемого набора d на пустую текстовую строку, т.е. удаляет букву "А". В качестве промежуточного результата получаем:
Вш зкз н 125 руб.
Итерация 2
Затем функция вызывает сама себя и в качестве входных данных (первый аргумент) получает уже то, что осталось после очистки на предыдущем шаге, а вторым аргументом задавая строку исключаемых символов начиная не с первого, а со второго символа, т.е. "БВГДЕЁЖЗИКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ.," без начальной "А" - это делает функция ПСТР. Как и до этого, функция берет первый слева символ из оставшихся (Б) и заменяет его в данном ей тексте (Зкз н 125 руб.) на пустую строку - получаем в качестве промежуточного результата:
Вш зкз н 125 ру.
Итерация 3
Функция опять вызывает сама себя, получая в качестве первого аргумента то, что осталось от зачищаемого текста на предыдущей итерации (Вш зкз н 125 ру.), а в качестве второго - урезанный слева ещё на один символ набор исключаемых знаков, т.е. "ВГДЕЁЖЗИКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ.," без начальной "Б". Затем опять берёт из этого набора первый символ слева (В) и удаляет его из текста - получаем:
И так далее - надеюсь, вы ухватили идею. С каждой итерацией список удаляемых символов будет обрезаться слева и мы будем искать и заменять на пустоту очередной символ из набора.
Когда все символы закончатся нам необходимо будет выйти из цикла - эту роль как раз и выполняет функция ЕСЛИ (IF) , в которую завернута наша конструкция. Если символов для удаления не осталось (d=""), то функция не должна больше вызывать саму себя, а просто должна вернуть зачищаемый текст (переменная t) в его финальном виде.
Рекурсивный перебор ячеек
Похожим образом можно реализовать и рекурсивный перебор ячеек в заданном диапазоне. Предположим, что мы хотим создать лямбда-функцию с именем ЗАМЕНАПОСПИСКУ для оптовой замены фрагментов в исходном тексте по заданному списку-справочнику. Выглядеть всё это в результате должно так:
Т.е. у нашей функции ЗАМЕНАПОСПИСКУ будет три аргумента:
- ячейка с текстом для обработки (исходный адрес)
- первая ячейка столбца со значениями для поиска из справочника
- первая ячейка столбца со значениями на замену из справочника
Функция должна проходить сверху-вниз по справочнику и заменять последовательно все варианты из левого столбца Найти на соответствующие варианты из правого столбца Заменить. Реализовать такое можно следующей рекурсивной лямбда-функцией:
Как и в предыдущем примере, сначала эта функция заменяет в исходном тексте с помощью функции ПОДСТАВИТЬ (SUBSTITUTE) данные по первой строке справочника (т.е. СПб на Санкт-Петербург ), а затем вызывает сама-себя, но со сдвигом по справочнику вниз на следующую строку (т.е. заменяет С-Пб на Санкт-Петербург ). Затем вызывает себя ещё раз со сдвигом вниз - и заменяет уже Питер на Санкт-Петербург и т.д.
Сдвиг вниз на каждой итериации реализован стандартной экселевской функцией СМЕЩ (OFFSET) , у которой в данном случае три аргумента - исходный диапазон, сдвиг по строкам (1) и сдвиг по столбцам (0).
Ну, и как только мы достигаем конца справочника (n=""), то должны закончить рекурсию - прекращаем вызывать сами себя и выводим то, что накопилось после всех выполненных замен в переменной исходного текста t.
Вот и всё. Никаких хитрых макросов или запросов Power Query - вся задача решается одной функцией.
Не сложно догадаться, для чего необходима функция =СКЛОНЕНИЕ(ТЕКСТ;ПАДЕЖ). Практически любое существительное или ФИО она переведет из именительного падежа в нужный падеж.
Подчеркну слово практически, так как имеются фамилии, которые без программы то сложно просклонять, но в 90% случаев макрос отрабатывает достойно. Проверяйте сложные или необычные фамилии дополнительно.
Формула имеет всего 2 аргумента:
Вот так выглядит стандартное окно ввода параметров функции СКЛОНЕНИЕ
Функция СКЛОНЕНИЕУКР
Аналогичная функция, которая склоняет ФИО и существительные, но только на украинском языке. Синтаксис у нее также не отличается =СКЛОНЕНИЕУКР(ТЕКСТ;ПАДЕЖ).
Далее приводятся примеры, наглядно демонстрирующие работу функции.
Обращаю ваше внимание, что для корректной работы данных функций необходимо стабильное подключение к интернету, а точнее к сайту Морфер.ру. Имеется также суточное ограничение на использование функций, которое в настоящий момент составляет 1000 склонений.
Пример 1
Склонение простой фамилии по всем падежам.
Пример 2
Склонение сразу фамилии имени и отчества по всем падежам.
Пример 3
Склонение сложных фамилий и имен в родительный падеж.
Склонение ФИО по падежам
Пользовательские функции (формулы, UDF) для склонения ФИО (фамилии, имени, отчества) по падежам, доступные при использовании надстройки FillDocuments
Поддерживаются 4 падежа:
- родительный падеж
- дательный падеж
- винительный падеж
- творительный падеж
Все эти функции, а также много других, можно приобрести отдельно от программы FillDocuments,
в виде другой надстройки для Excel с открытым кодом
| Функция (формула) | Пример получаемого значения |
|---|---|
| =Родительный(E2) | Андреевой Алёны Васильевны |
| =Дательный(E2) | Андреевой Алёне Васильевне |
| =Винительный(E2) | Андрееву Алёну Васильевну |
| =Творительный(E2) | Андреевой Алёной Васильевной |
| Исходное значение в ячейке E2 | Андреева Алёна Васильевна |
Программе, для правильного склонения ФИО, нужно определить пол. Пол определяется по отчеству.
Потому, склонять фамилии с инициалами категорически не рекомендуется (могут возникнуть ошибки)
ВАЖНО: Эти функции применимы только к ФИО. Склонять ими должности, и прочие слова и фразы, не получится.
ВНИМАНИЕ: Если вместе со склонением, вы используете другие функции для ФИО (например, Инициалы из ФИО), то сначала применяйте функцию склонения, а потом уже, после изменения падежа, другую функцию.
Не сложно догадаться, для чего необходима функция =СКЛОНЕНИЕ(ТЕКСТ;ПАДЕЖ). Практически любое существительное или ФИО она переведет из именительного падежа в нужный падеж.
Подчеркну слово практически, так как имеются фамилии, которые без программы то сложно просклонять, но в 90% случаев макрос отрабатывает достойно. Проверяйте сложные или необычные фамилии дополнительно.
Формула имеет всего 2 аргумента:
Вот так выглядит стандартное окно ввода параметров функции СКЛОНЕНИЕ
Функция СКЛОНЕНИЕУКР
Аналогичная функция, которая склоняет ФИО и существительные, но только на украинском языке. Синтаксис у нее также не отличается =СКЛОНЕНИЕУКР(ТЕКСТ;ПАДЕЖ).
Далее приводятся примеры, наглядно демонстрирующие работу функции.
Обращаю ваше внимание, что для корректной работы данных функций необходимо стабильное подключение к интернету, а точнее к сайту Морфер.ру. Имеется также суточное ограничение на использование функций, которое в настоящий момент составляет 1000 склонений.
Пример 1
Склонение простой фамилии по всем падежам.
Пример 2
Склонение сразу фамилии имени и отчества по всем падежам.
Пример 3
Склонение сложных фамилий и имен в родительный падеж.
Склонение ФИО по падежам
Пользовательские функции (формулы, UDF) для склонения ФИО (фамилии, имени, отчества) по падежам, доступные при использовании надстройки FillDocuments
Поддерживаются 4 падежа:
- родительный падеж
- дательный падеж
- винительный падеж
- творительный падеж
Все эти функции, а также много других, можно приобрести отдельно от программы FillDocuments,
в виде другой надстройки для Excel с открытым кодом
| Функция (формула) | Пример получаемого значения |
|---|---|
| =Родительный(E2) | Андреевой Алёны Васильевны |
| =Дательный(E2) | Андреевой Алёне Васильевне |
| =Винительный(E2) | Андрееву Алёну Васильевну |
| =Творительный(E2) | Андреевой Алёной Васильевной |
| Исходное значение в ячейке E2 | Андреева Алёна Васильевна |
Программе, для правильного склонения ФИО, нужно определить пол. Пол определяется по отчеству.
Потому, склонять фамилии с инициалами категорически не рекомендуется (могут возникнуть ошибки)
ВАЖНО: Эти функции применимы только к ФИО. Склонять ими должности, и прочие слова и фразы, не получится.
ВНИМАНИЕ: Если вместе со склонением, вы используете другие функции для ФИО (например, Инициалы из ФИО), то сначала применяйте функцию склонения, а потом уже, после изменения падежа, другую функцию.
Читайте также: