Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Оптимизация формулы склонения слов, склонение названий на основе умной таблицы
 
#1
10.09.2020 10:35:04
Добрый день, уважаемые форумчане!
Стоит задача: названия образовательных учреждений просклонять (вывести названия в родительном и дательном падежах). Для этого сформирован "словарь" наиболее употребительных в названиях слов (в примере таблица I2:K32). Склоняться должны названия, НЕ включенные в кавычки.
Те формулы, которые сейчас используются (ячейки А5 и А6), работают, но если необходимо пополнить словарь, то нужно долго пополнять эти формулы, дописывая недостающие значения.
Вопрос: можно ли оптимизировать эти формулы таким образом, чтобы они обращались к "умной таблице" независимо от ее размера?
Спасибо.
 
 
 
#2
10.09.2020 10:40:45
Здравствуйте, kalle,
Знакома ли Вам эта статья в Приемах?
Массовая замена текста формулами
В ней рассмотрен случай, похожий на Ваш
 
 
 
#3
10.09.2020 10:44:20
я бы макросом делал. Формулы для таких задач не очень приспособлены. При этом в данном случае можно просто написать одну UDF, которую использовать как функцию - и не нужно тогда всех предыдущих шагов с удалением пробелов и вытаскиванием текста между кавычек.
Изменено: Дмитрий(The_Prist) Щербаков - 10.09.2020 10:45:35
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#4
10.09.2020 10:54:43
Цитата
Дмитрий(The_Prist) Щербаков написал:
я бы макросом делал
Увы, макросы запрещены %((
 
 
 
#5
10.09.2020 12:32:09
Цитата
kalle написал:
Увы, макросы запрещены
Версия Excel какая.  Есть разумное ограничение по количеству слов в названии? А то для 10 слов и оптимизацией это назвать сложно , разве что словарь пополняйте сколь угодно.

=SUBSTITUTE(SUBSTITUTE(IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));1*LEN(A1);LEN(A1)));словарь;2;);TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));1*LEN(A1);LEN(A1))))&" "&
IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));2*LEN(A1);LEN(A1)));словарь;2;);TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));2*LEN(A1);LEN(A1))))&" "&
IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));3*LEN(A1);LEN(A1)));словарь;2;);TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));3*LEN(A1);LEN(A1))))&" "&
IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));4*LEN(A1);LEN(A1)));словарь;2;);TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));4*LEN(A1);LEN(A1))))&" "&
IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));5*LEN(A1);LEN(A1)));словарь;2;);TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));5*LEN(A1);LEN(A1))))&" "&
IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));6*LEN(A1);LEN(A1)));словарь;2;);TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));6*LEN(A1);LEN(A1))))&" "&
IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));7*LEN(A1);LEN(A1)));словарь;2;);TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));7*LEN(A1);LEN(A1))))&" "&
IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));8*LEN(A1);LEN(A1)));словарь;2;);TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));8*LEN(A1);LEN(A1))))&" "&
IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));9*LEN(A1);LEN(A1)));словарь;2;);TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));9*LEN(A1);LEN(A1))))&" "&
IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));10*LEN(A1);LEN(A1)));словарь;2;);TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1;FIND("«";A1)-1));"(";"( ");")";" )");" ";REPT(" ";LEN(A1)));10*LEN(A1);LEN(A1))));" )";")");"( ";"(")&IFERROR(" "&MID(A2;FIND(" «";A2;1)+1;200);"")
Изменено: БМВ - 10.09.2020 12:43:52
По вопросам из тем форума, личку не читаю.
 
 
 
#6
10.09.2020 13:02:00
Цитата
БМВ написал:
Версия Excel какая.
Добрый день, Михаил! Excel 2013. На всякий случай, предупреждаю, что максимальное число знаков в формуле - 8192 (#5 - 3205) :)  
Владимир
 
 
 
#7
10.09.2020 13:48:43
Цитата
sokol92 написал:
8192 (#5 - 3205)  
Владимир, ну можно в имена часть положить,  да и запас еще есть :-)
Можно Len заменить на константу, и сама формула сократится и расчет полегчает.

_SplWords =SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(Лист1!A1;FIND("«";Лист1!A1)-1));"(";"( ");")";" )");" ";REPT(" ";500))
и
=SUBSTITUTE(SUBSTITUTE(
IFERROR(VLOOKUP(TRIM(MID(_SplWords;1*500;500));словарь;2;);TRIM(MID(_SplWords;1*500;500)))&" "&
IFERROR(VLOOKUP(TRIM(MID(_SplWords;2*500;500));словарь;2;);TRIM(MID(_SplWords;2*500;500)))&" "&
IFERROR(VLOOKUP(TRIM(MID(_SplWords;3*500;500));словарь;2;);TRIM(MID(_SplWords;3*500;500)))&" "&
IFERROR(VLOOKUP(TRIM(MID(_SplWords;4*500;500));словарь;2;);TRIM(MID(_SplWords;4*500;500)))&" "&
IFERROR(VLOOKUP(TRIM(MID(_SplWords;5*500;500));словарь;2;);TRIM(MID(_SplWords;5*500;500)))&" "&
IFERROR(VLOOKUP(TRIM(MID(_SplWords;6*500;500));словарь;2;);TRIM(MID(_SplWords;6*500;500)))&" "&
IFERROR(VLOOKUP(TRIM(MID(_SplWords;7*500;500));словарь;2;);TRIM(MID(_SplWords;7*500;500)))&" "&
IFERROR(VLOOKUP(TRIM(MID(_SplWords;8*500;500));словарь;2;);TRIM(MID(_SplWords;8*500;500)))&" "&
IFERROR(VLOOKUP(TRIM(MID(_SplWords;9*500;500));словарь;2;);TRIM(MID(_SplWords;9*500;500)))&" "&
IFERROR(VLOOKUP(TRIM(MID(_SplWords;10*500;500));словарь;2;);TRIM(MID(_SplWords;10*500;500)))
;" )";")");"( ";"(")&IFERROR(" "&MID(A2;FIND(" «";A2;1)+1;200);"")
Изменено: БМВ - 10.09.2020 14:07:56
По вопросам из тем форума, личку не читаю.
 
 
 
#8
10.09.2020 13:54:34
Цитата
БМВ написал:
можно в имена часть положить
Так еще есть ограничение:
Цитата
Внутренняя длина формулы, байт, не более 16 384
Точно не знаю, что это.
Владимир
 
 
 
#9
11.09.2020 07:07:58
Всем спасибо за помощь!
 
 
 
Страницы: 1
Наверх