Задача стояла следующая!
Вводные данные:
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.
Если нужен пример, могу выложить. Пишите в комментариях.