Чем заменить или расширить функцию ВПР(_)

Автор mmet, 10.10.2014, 17:22

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

mmet

Есть список книг с кодовыми обозначениями тематики, например:

Книга1 11.3
Книга2 12.02
Книга3 22.0146

Есть список грубого (основного) соответствия кодов категориям
Химия 12
Математика 12.0
Физика 12.1
Машиностроение 22.01

т.е. по началу коду книги "12.0" мы поймем, что это математика, а остальное уже не интересует

Нужно автоматически сопоставить по полным кодам книг их примерную тематику.
Задача минимум сделать так, чтобы сработало что-то типа ВПР(_) с поиском по таблице значений в виде (12.0* - Математика) и сюда бы подошли все коды от 12.0 и до 12.4455ЭЮЯ
Но звездочка как подстановочный знак работает только искомом коде, чего в данном случае совершенно не нужно

Крайне желательно также иметь возможность в таблице кодов держать одновременно и 12.0*, и 12.1* и 12*. При этом, если ничего лучшего не подошло, то использовать 12*

cheshiki1

#1
файл пример соорудите и в нем покажите чего хотите.

mmet


Pelena

Посмотрите такой вариант.
Ограничение (условие) коды в зеленой таблице должны быть отсортированы по убыванию

zs

Цитата: mmet от 10.10.2014, 17:22
и 12.0*, и 12.1* и 12*. При этом, если ничего лучшего не подошло, то использовать 12*
Здравствуйте, mmet (Leonid). Формула уже написана, так в чём вопрос ;)

mmet

всем огромное спасибо!
на данный момент взял на вооружение формулу zs
единственное неудобство возникает "не удается ввести указанную формулу поскольку она использует больше уровней вложенности" - я уже задействовал 5 уровней одних только ЕСЛИОШИБКА() для перебора 6-5-4-2-1 знаков, а ИНДЕКС() уже в отдельной ячейке

в примере Pelena не совсем понял, как работает: ПОИСКПОЗ(1=1;ЕЧИСЛО(ПОИСК($F$1:$F$4;B5));0), разъясните по-возможности

и еще всплыл небольшой вопросик
его теоретически можно и руками решить изредка, но если автоматизировать, будет чудесно
а именно:
если в коде книги присутствует определенная комбинация, например, "АБВГД" тогда тематика будет "ABCD", а не та, которая выходит из цифрового кода, т.е.:
22,3 - физика
22,3+30(АБВГД) - ABCD

Pelena

ПОИСКПОЗ(1=1;ЕЧИСЛО(ПОИСК($F$1:$F$4;B5));0)

1=1 - это ИСТИНА
ЕЧИСЛО(ПОИСК($F$1:$F$4;B5)) - возвращает массив из элементов ИСТИНА и ЛОЖЬ, причем ИСТИНА стоит на том месте, в котором найдено вхождение $F$1:$F$4 в В5
ПОИСКПОЗ() ищет значение ИСТИНА в этом массиве.
Таким образом, формула возвращает позицию найденного элемента

Как разобраться в работе сложной формулы
http://www.excelworld.ru/publ/hacks/tricks/f9_evaluate_formulas/17-1-0-142

Pelena

А по дополнительному вопросу: делайте таблицу соответствий АБВГД -- ABCD (можно на другом листе) и используйте ВПР(_)
Более конкретный ответ при наличии файла с примером