Поиск/Подстановка значений с разделителями

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск/Подстановка значений с разделителями (Формулы/Formulas)

exelunion

Дата: Среда, 22.07.2015, 11:47 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

Excel 2003

Добрый день уважаемые форумчане! Уже второй день бьюсь над решением как мне казалось тривиальной задачи.

Вводные. В ячейке, через запятую с пробелом перечислены свойства/комплектация товара, к примеру: "зеркало с антизапотевателем, зеркало с подсветкой, крепления". Всего свойств товара 14 и каждый товар имеет свой (не уникальный, т.е. товар1 и товар7 могут иметь схожие свойства/комплектацию) набор (комбинаций) свойств которым присвоены id, к примеру: "зеркало с антизапотевателем - #=3", "зеркало с подсветкой - #=1", "крепления - #=8".

Задача. При помощи формулы вместо свойств/комплектации подставить соответствующие им id, чтобы в ячейке вместо "зеркало с антизапотевателем, зеркало с подсветкой, крепления" получить "#=3, #=1, #=8".

К сообщению приложен файл: svoystva.xls (24.5 Kb)

Ответить

_Boroda_

Дата: Среда, 22.07.2015, 12:17 | Сообщение № 2

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16675

Репутация: 6481 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Для 2007 и больше можно так

Код

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВ ИТЬ(ПОДСТАВИТЬ(C2;E$2;F$2);$E$3;F$3);E$4;F$4);E$5;F$5);E$6;F$6);E$7;F$7);E$8;F$8);E$9;F$9);E$10;F$10);E$11;F$11);E$12;F$12);E$13;F$13);E$14;F$14);E$15;F$15)

Для 2003 придется разбить на 2 части (см. файл)
Но макросом было бы намнооооого проще.

К сообщению приложен файл: svoystva_1.xls (32.0 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Rioran

Дата: Среда, 22.07.2015, 12:17 | Сообщение № 3

Группа: Авторы

Ранг: Ветеран

Сообщений: 903

Репутация: 290 ±

Замечаний: 0% ±

Excel 2013

exelunion, здравствуйте!

Если допустимо использование пользовательской функции (суть макрос) - добро пожаловать во вложение. Макросы должны быть включены.

Как Вам такой вариант?

Формула прописывается как показано ниже. Первый аргумент - свойства текстом через запятую. Второй аргумент - свойства, которые будем отлавливать и заменять. Третий аргумент - на что будем заменять.

Код

=RioSwap(C2;F$2:F$15;G$2:G$15)

Для знатоков собственно код функции:

[vba]

Код

Function RioSwap$(StrX$, RngA As Range, RngB As Range)
       Dim ArrX, ArrA, ArrB, i&, j&
       ArrX = Split(StrX, ", "): ArrA = RngA: ArrB = RngB
       For i = 0 To UBound(ArrX)
           For j = 1 To UBound(ArrA, 1)
               If ArrX(i) = ArrA(j, 1) Then ArrX(i) = ArrB(j, 1)
           Next j
       Next i
       RioSwap = Join(ArrX, ", ")
End Function

[/vba]
[moder]Роман, макрофункция и ЮДФ - это разные вещи.[/moder]
Да, спасибо, опечатался =) В тексте поправил на "пользовательскую функцию".

К сообщению приложен файл: Rio_Svva.xlsm (17.8 Kb)

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279

Сообщение отредактировал Rioran - Среда, 22.07.2015, 12:24

Ответить

exelunion

Дата: Среда, 22.07.2015, 15:22 | Сообщение № 4

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

Excel 2003

_Boroda_, Ваше решение работает, спасибо огромное! А то на обработку вариантов цветов через ЕСЛИ (вложенность была больше 120 и вручную забивались вариации цветов с сопоставлением каждой вариации id ) ушло 2 рабочих дня. В общем ужас.

Правда, в силу того, что свойства совпадают (зеркало с подогревом/с подсветкой, зеркало) неправильно подставляются id свойства, решил проблему удалением пробелов, к примеру: было "зеркало с антизапотевателем, зеркало с подсветкой, крепления" стало "зеркалосантизапотевателем, зеркалосподсветкой, крепления". Видимо эксель обрабатывает/воспринимает первые совпадения (слово зеркало).

От всей души Вас благодарю!!!