СЕРВИС ПОИСКА И ПОДБОРА ПОСТАВЩИКОВ ДВУТАВРА STEEL-RADAR
VetCAD

Альтернатива ВПР ИНДЕКС ПОИСКПОЗ ПРОСМОТР ПРОСМОТРX (Excel vba VPRpa)

    1 оценка

Петр-и-Алекс

размещено: 24 Марта 2021
обновлено: 22 Апреля 2021
Альтернатива ВПР ИНДЕКС ПОИСКПОЗ ПРОСМОТР ПРОСМОТРX (Excel vba VPRpa)

Специальный релиз для инженеров, позволяет работать с таблицами Excel намного проще.
Лично мне в стандартном Excel не хватает простых средств поиска числа в таблице. Такую простую задачу, как «найти в произвольном столбце таблицы число, большее заданного, и получить соответствующий найденному из другого столбца» не решить без танцев с бубном. Так же не просто «найти в произвольном столбце таблицы последнее (а не первое) число, равное заданному». Есть еще проблема: какие числа считать «равными»? Числа 10 и 9.9998 равны?
Проблема Excel в том, что невозможно оперировать таблицей как единым и постоянным объектом, для обработки «таблицу» необходимо разодрать на самостоятельные лоскуты «векторов». А перед этим таблицу необходимо переформировывать то так, то этак, смотря по решаемой задаче. Например, для поиска «меньшего»надо перекроить «по убыванию», а для поиска «большего» - уже «по возрастанию». А еще значения в ячейках «векторов» не могут быть «любыми», а должны быть определенными: запись числа не должна содержать пробел, а в России еще не должна содержать «точку». Пустая ячейка скорее всего будет интерпретирована как «ноль». Все это сильно отвлекает от решения прикладной задачи.
А хочется, что б было так: вставил в Excel таблицу (хоть скопипастеннуюиз системы «кодекс») - и сразу можешь пользоваться ею, как единым и в дальнейшем неизменяемым объектом.
Того ради создан этот проект. Самые важные отличия от функций Excel:
- поиск значений ведется в «таблице». «Таблица» - единый, неизменяемый под сиюминутные задачи объект.
- столбцы и строки таблицы могут располагаться в любом произвольном порядке. Никаких требований (типа «по возрастанию») не предъявляется;
- пользователь указывает, в каком столбце таблицы внести поиск, из какого столбца брать «ответ». Столбец таблицы можно указать или его порядковым номером, или его «названием». Названием столбца считается содержание первой строки таблицы;
- проект интерпретирует содержимое ячейки как «число», если человек понимает содержимое ячейки «как число». Например это значит, что пробелы перед числом не препятствуют работе, а в качестве десятичного разделителя допустимы и «точка», и «запятая»;
- при поиске чисел текстовые и пустые ячейки игнорируется;
- пустая ячейка считается «не содержащей число», а не «нулем» и не «разделителем диапазона»;
- легко организовать поиск не пустой ячейки, ближайшей к найденной «пустой».
Эта версия проекта решает такие задачи:
а) Найти в указанном столбце таблицы последнее (или первое) число, равное заданному (с определенной точностью), и вернуть содержание соответствующей ячейки из другого столбца;
б) Найти в указанном столбце таблицы ближайшее большее заданному числу (или ближайшее меньшее) и вернуть …;
в) Найти в указанном столбце таблицы максимальное значение (или минимальное) и вернуть …;
г) Во всех вариантах поиска можно запросить возврат номера строки найденного числа (а не значение ячейки из столбца).
В особых ситуациях проект вернет диагностическое сообщение. Если задан поиск «ближайшего меньшего», а заданное число сравнения и так меньше меньшего в столбце, то функция вернет символ “”. Если в качестве значения числа сравненияошибочно подставлено«не число», то проект сообщит и об этом.
В «скачивании» находится книга Excel с модулем, содержащим функции проекта. В книге есть страница с детальной инструкцией и с реальным примером.
В качестве примера выбрана работа с таблицей 7 из ГОСТ 30055-93 «Канаты из полимерных материалов и комбинированные. Технические условия». Пример демонстрационный, но таблица реальная и может пригодится. Да и сам пример легко адаптируется под конкретную задачу.
Как этим воспользоваться:
Те, кто совсем не в теме vba Excel и побаивается наломать дров, поступаете так: 1) открываете мою книгу; 2) создаете новую страницу; 3) мою страницу удаляете; 4) сохраняете книгу со своим названиеми обязательно в варианте «книга Excel с поддержкой макросов». Новые функции будут доступны в созданной Вами книге наряду со штатными средствами. И вызываться будут штатно как родные.
Знатокам vba и рассказывать ничего не надо.
Остальные могут: 1) нажать одновременно Alt и F11; 2) в открывшемся редакторе vba в СВОЕЙ книге «создать модуль»; 3) скопипастить в него все содержимое единственного «модуля» моей книги: 4) сохранить книгу в правильном формате. Если с этим непонятности – смотрим интернет на тему «vba начало работы» или «vba редактор».
Автор будет рад отзывам и даже простому «спасибо».
---
добавлен пример поиска по нескольким столбцам

Комментарии

Комментарии могут оставлять только зарегистрированные участники
Авторизоваться
Комментарии 1-9 из 9
Петр-и-Алекс , 26 марта 2021 в 10:21
#1
Забыл отметить в инструкции:
В варианте с использованием "названия столбца", название столбца не должно быть "числом", должно содержать "буквы". Например так: "N1"
ДмитрийV , 30 марта 2021 в 05:47
#2
ишь ты, спасибо!!!
Петр-и-Алекс , 12 апреля 2021 в 10:45
#3
Немного статистики: сотня скачиваний за 19суток; ни одной оценки; всего одно "спасибо". Удивился столь низкому "спросу". В моей жизни "задача ВПР" встречается на порядок чаще, чем пресловутая "интерполяция". Сюрприз однако...
Grr , 21 апреля 2021 в 06:57
#4
Функции конечно интересные, но в примере приведена простая таблица (поиск ведется по одному столбцу). А как вести поиск в таблице если исходные данные находятся в 2-х или 3-х столбцах ?
В инструкцию по работе с функциями надо добавить, что вставленную таблицу нужно выделить (как диапазон) и дать ей имя.
Петр-и-Алекс , 21 апреля 2021 в 12:00
#5
"А как вести поиск в таблице если исходные данные находятся в 2-х или 3-х столбцах ?"
- ни разу не встречалась такая задача, потому и не решена. Но дописать нужное, имея этот шаблон, не проблема любому, знающему vba… Я не против этого, если что. Особенно если буду помянут в комментарии :)
---
"В инструкцию по работе с функциями надо добавить, что вставленную таблицу нужно выделить (как диапазон) и дать ей имя. "
Очень трудно писать инструкцию неопределенному кругу лиц с неопределенным опытом и знаниями. На этот случай я изначально собирался вести "блог", с методами практической работы в Excel, но не встретил ни поддержки админов, ни интереса публики. Тема про раздел, посвященный Excel, вообще не прошла премодерацию без объяснений причин. я так понял, двж - для двж, а не чего еще :).
Петр-и-Алекс , 21 апреля 2021 в 12:05
#6
Вдогонку: давать имя выделенному диапазону совсем не обязательно, диапазон можно указать и адресами ячеек. Просто удобней работать с именами.
Grr , 22 апреля 2021 в 07:54
#7
Петру-и-Алексу ).
Поиск по ИД в 3-х столбцах я и сам не помню, где видел ), может и нет таких.
Ну, а по двум столбцам встречается часто, даже ваша таблица такая, в первой колонке наименование каната, в следующей диаметр... Например таблицы с диаметрами и толщинами труб такие. Пусть нужна масса детали: здесь ИД -диаметр и толщина трубы.
vba - я знаю плохо. Даже не знал, что можно написать пользовательскую функцию, которую можно использовать как встроенную. В общем если бы Вы доработали функцию было бы неплохо.
"...Тема про раздел, посвященный Excel, вообще не прошла премодерацию...". В проектировании, на мой взгляд, Excel находится на втором плане. Из известных тем: спецификация материалов в Excel, и связь dwg + excel для каких либо целей. Большинство проектировщиков уже имеют свои программки, справочники. А Вы предложили какой то более менее универсальный инструмент (эти функции) вот я и заинтересовался.
Петр-и-Алекс , 22 апреля 2021 в 13:22
#8
Сочинил и добавил пример поиска по нескольким столбцам. Код функций не переписан, показано, как использовать то, что есть.
Grr , 26 апреля 2021 в 06:53
#9
спасибо
zip

0.04 МБ

СКАЧАТЬ