Анализ текстовых значений

Автор kiselev, 29.08.2011, 15:34

« назад - далее »

kiselev

Всем здравствуйте!

Прошу помочь в решении следующей задачи:

Дано
Столбец А ячеек, заполненных текстовыми значениями в виде предложений различной длины. Контрольная таблица из 2 столбцов, где в каждой ячейке столбца В приведено контрольное слово, а в каждой соответствующей ей ячейке столбца С - текстовое значение (словосочетание из 2-5 слов).

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

Желательно обойтись без макроса.

_Boroda_

Можно так
=ИНДЕКС(C$2:C$4;СУММПРОИЗВ(ЕЧИСЛО(ПОИСК(B$2:B$4;A2))*(СТРОКА(B$2:B$4)-1)))
Скажи мне, кудесник, любимец ба'гов...



Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995

kiselev

Цитата: _Boroda_ от 29.08.2011, 16:08
Можно так
=ИНДЕКС(C$2:C$4;СУММПРОИЗВ(ЕЧИСЛО(ПОИСК(B$2:B$4;A2))*(СТРОКА(B$2:B$4)-1)))


Благодарю! Вы могли бы вкратце разложить действие каждой функции в данном случае?

_Boroda_

#3
=ИНДЕКС(C$2:C$4;СУММПРОИЗВ(ЕЧИСЛО(ПОИСК(B$2:B$4;A2))*(СТРОКА(B$2:B$4)-1)))
1. ПОИСК(B$2:B$4;A2) - ищем каждое значение из столбца В в ячейке столбца А, если нашли, то получаем позицию вхождения (число), если не нашли - то ошибку; итог - массив чисел и ошибок;
2. ЕЧИСЛО(ПОИСК(B$2:B$4;A2)) - если нашли в п.1, то получаем единицу, если нет - то 0; итог - массив ноликов и единичек;
3. (СТРОКА(B$2:B$4)-1) - массив 1;2;3
4. ЕЧИСЛО(ПОИСК(B$2:B$4;A2))*(СТРОКА(B$2:B$4)-1) - умножить п.2 на п.3; если нашли в п.1, то получаем номер строки того значения из столбца В, которое нашли, если не нашли - то 0;
5. СУММПРОИЗВ(ЕЧИСЛО(ПОИСК(B$2:B$4;A2))*(СТРОКА(B$2:B$4)-1)) - складываем массив из п.4 и получаем просто число, показывающее номер положения найденного в столбце В
6. ИНДЕКС(C$2:C$4;п.5) - выковыриваем из столбца С значение с таким же положением, как в п.5

Работает только если в ячейке столбца А может быть ОДНО слово из столбца В
Кстати, пока писал, ошибку нашел. Нужно так:
=ИНДЕКС(C$1:C$4;СУММПРОИЗВ(ЕЧИСЛО(ПОИСК(B$2:B$4;A2))*(СТРОКА(B$2:B$4)-1))+1)
По предыдущим объяснениям новую формулу сами разберете или подсказать?
Скажи мне, кудесник, любимец ба'гов...



Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995

kiselev

Спасибо, пытаюсь разобраться самостоятельно.

Беспокоит конструкция: (СТРОКА(B$2:B$4)-1). В отдельном столбце она возвращает только значение "1" для всех строк с 1й по 6ю. Получается, что без "-1", она возвращает только номер строки, с которой начинается диапазон B$2:B$4, т.е. "2". То есть интересно, как получается массив 1;2;3.

И кстати, ЕЧИСЛО(ПОИСК(B$2:B$4;A2)) возвращает ведь не "1" или "0", а "ЛОЖЬ" или "ИСТИНА". Как с этим разобрались?

_Boroda_

ЕЧИСЛО(ПОИСК(B$2:B$4;A2)) мы потом умножаем на другой массив чисел. При арифметических действиях ИСТИНА и ЛОЖЬ преобразуются в 1 и 0.

по поводу
ЦитироватьБеспокоит конструкция: (СТРОКА(B$2:B$4)-1). В отдельном столбце она возвращает только значение "1" для всех строк с 1й по 6ю. Получается, что без "-1", она возвращает только номер строки, с которой начинается диапазон B$2:B$4, т.е. "2". То есть интересно, как получается массив 1;2;3.
выделите в строке формул СТРОКА(B$2:B$4)-1 и нажмите F9 (показывает результат вычислений).
А 1 дает только если ввести эту формулу в ячейку - просто показывается первый элемент массива
Скажи мне, кудесник, любимец ба'гов...



Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995

MCH

#6
Еще пара вариантов:
=ПРОСМОТР(2;1/ПОИСК(B$2:B$4;A2);C$2:C$4)
или
=ПРОСМОТР(999;ПОИСК(B$2:B$4;A2);C$2:C$4)
где 999 - число превышающее максимальную длину текстовых строк
или формула массива
=ИНДЕКС(C$2:C$4;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(ПОИСК(B$2:B$4;A2));0))

MCH

Если нужна проверка на ошибку, то можно так (формула массива):
=ЕСЛИ(СЧЁТ(ПОИСК(B$2:B$4;A2))>0;ПРОСМОТР(2;1/ПОИСК(B$2:B$4;A2);C$2:C$4);"")

kiselev

Очень интересно!

Я читаю справку по ПРОСМОТР, но пока не могу понять смысла деления 1 на результат ПОИСК(B$2:B$4;A2).

Также интересно узнать про 999 и его смысл среди аргументов функции ПРОСМОТР.