Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
Обход поиска только первого значения функции ВПР в MS EXCEL., заставить функцию ВПР работать с неуникальными именами
 
Пришлось столкнутся с ограничениями функции ВПР в MS EXCEL.  
Задача стояла следующая!

Вводные данные:

1.       Есть бланки в формате DOCX и XLS в которые надо постоянно вносить изменения, дату, номер, имена и фамилии, паспортные данные, и данные других документов.

2.       Так же есть бланки на всевозможную технику в которые так же надо вносить множественные данные.

Задача:

1.       Пересоздать все бланки в формате MS EXCEL.

2.       Создать таблицы с данными сотрудников, техники, и вспомогательные таблицы.

3.       Создать единое окно ввода и выбора данных с визуальной проверкой.

4.       Обойтись без макросов!

Решение:

1.    Создана форма заказов пропусков, с выпадающими списками выбора ФИО, и транспортных средств, а также внесения произвольных данных типа дата, номер заявки, номер приказа, итд.

2.       Созданы таблицы «Сотрудники» и «Автотранспорт»

А также таблицы «Организации» и «Вспомогательная»

И если при работе с таблицей «Сотрудники» проблем не возникало, ибо полные тёзки и однофамильцы встречаются редко, то при работе с таблицей «Автотранспорт» возникли проблемы.

Функция ВПР находит только первое значение, и исключительно справа от столбца, в котором идёт поиск!

К примеру, имеется несколько единиц техники одинаковой марки, в форме «Заказа пропусков» есть по три выпадающих списка для сотрудников и для транспорта, с сотрудниками как я уже упоминал проблем не возникает, а вот с транспортом пришлось придумать костыли…

В столбце к каждой повторяющейся единице техники я по началу добавил по пробелу, то есть если у меня было четыре автомобиля Татра Т158-8Р5R46 8х81R, то у первой было название без пробела в конце, у второй один пробел, у третьей два пробела, и у четвёртой три пробела, и так со всеми дубликатами названий.

И понимая, что эти костыли конечно работают, но это не решение проблемы, я начал искать альтернативу функции ВПР и решение этой задачи.

Перепробовав всё что можно и нельзя, не добившись результата, не найдя ответов на форумах, я вспомнил о структуре баз данных и о том, что в отличие от EXCEL в них у каждой строки есть индивидуальный цифровой идентификатор строки, который и позволяет идентифицировать даже одинаковые названия как разные.

Помня, что функция ВПР ищет значения только справа от столбца, в котором искомое значение, мною была изменена структура таблицы «Автотранспорт».

Слева от столбца «Марка машины» были созданы ещё два столбца «№» и «Сцепить № и ТС»

В результате получилось, что данные для выпадающего списка на форме «Заказа пропусков» мы берём из столбца «А».

И каждое значение для функции ВПР теперь стало уникальным, приобретя вид НОМЕР+МАРКА МАШИНЫ.

А в шаблоны документов автоматически вносим значения из визуальной формы простым копирование и вставкой ссылки на лист и ячейку откуда берутся данные =Пропуска!$N$5.

В ячейке N5 на листе «Пропуска»  стоит формула =ВПР(H5;Автотранспорт!A2:P56;3;0)

Вот как бы и всё. Таким образом создав два дополнительных столбца и поле отображения данных, мы научили MS EXCEL и в частности его функцию ВПР, находить одинаковые данные используя номерной идентификатор и функцию =СЦЕПИТЬ([@№];" ";[@[Марка машины]]).

Задание выполнено! Мы обошлись без макросов и программирования на VBA.

Если нужен пример, могу выложить. Пишите в комментариях.

Изменено: Qvazarius - 12 Ноя 2018 12:00:58
 
Круто. Правда все это конечно не открытие (составной индекс) , а есть варианты и без него и очень много текста бесполезного, но за терпение и труд и желание ставлю отлично.
По вопросам из тем форума, личку не читаю.
 
Цитата
Qvazarius написал:
Функция ВПР находит только первое значение, и исключительно справа от столбца, в котором идёт поиск!
Верно, только зачем восклицательный знак - это возмущение? ))
А в раздел "Приемы" Вы заглядывали? Всё уже давно сделано: и поиск слева, и не только первое значение.
 
Цитата
Qvazarius написал: И понимая, что эти костыли конечно работают, но это не решение проблемы, я начал искать альтернативу функции ВПР и решение этой задачи.
Это понимание, ИМХО, должно было привести к нормализации Вашей базы данных, тогда бы никакие костыли и не понадобились.

Цитата
Qvazarius написал: ибо полные тёзки и однофамильцы встречаются редко
Для этого "умные люди" придумали неповторяющиеся данные вроде табельного номера сотрудника.
Опять-же нормализация, нормализация и ещё раз нормализация.
 
Юрий М, Э! Э! Я такое не писал :-)
По вопросам из тем форума, личку не читаю.
 
Опять движок барахлит )) Исправил.
 
Цитата
Юрий М написал:
движок барахлит ))
движок лап? :-)
По вопросам из тем форума, личку не читаю.
 
Хвоста. С лапами порядок )
 
Цитата
Юрий М написал:
Хвоста
Рыбьего? :)
Страницы: 1
Читают тему (гостей: 1)
Наверх