Публичные константы или Именованные диапазоны. Как лучше обращаться к полям при их меняющихся именах и положении, Public constants or Name Ranges. How to Request the Fields better when Names and Address can be change?
Дано: 5 листов, в каждом по умной таблице 30-50 столбцов. Нужно: зафиксировать положения столбцов, чтобы обращаться к ним внутри кода.
Варианты: • публичные константы типа Long для каждого столбца • именованные диапазоны для заголовка каждого столбца (одна ячейка)
Вопрос на опыт разработчика: что лучше?
Составлять и то, и то медленно, но нужно. Именованные диапазоны лучше констант, т.к. при любом сдвиге ничего менять не нужно.
Какое количество именованных диапазонов начинает "грузить" файл? Имеет ли значение, что каждый именованный диапазон это сего одна ячейка? Есть ли другие более удобные способы?
Дополнительно: Столбцы могут менять положение - это нормально. Нужно просто постоянно знать, где находится известное поле. Названия тоже могут меняться - постоянно какие-то новые правки. Поиск по имени отпадает.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
1. Все зависит от того, важна ли структура таблицы или важно лишь наличие столбцов. Если важна - проще скрытый лист с шаблоном шапки, с которым сравнивать. 2. Надо понимать, могут ли удаляться столбцы. Если да и при этом хочется использовать имена - надо в обязательном порядке проверять наличие каждого имени, а если при этом важна структура - см.п.1.(но тогда лучше вообще отказаться от имен. Почему? см.п.3 ). 3. Надо точно понимать, можно ли опираться на сами Заголовки(т.е. текст названия). Если можно - проще сделать функцию поиска по названию - и быстро и практично. Ни перемещение, ни удаление столбцов не скажется на работе или это всегда можно обойти хотя бы выдачей сообщения, что такой-то столбец не найден. И даже простое добавление пользователем столбца не приведет к отказу работы программы. Чем п.3 удобен: если есть именованный столбец и пользователь его удалил и получил сообщение "Не найден столбец такой-то", то первое желание будет добавить этот столбец. Но ведь имя-то безвозвратно удалено и программа все равно его не найдет. А это значит, что даже добавив столбец пользователь будет получить сообщение об его отсутствии, что, мягко говоря, его ввергнет его в легкое уныние
В своей практике применял все варианты. В зависимости от ситуации выбирается способ. Т.к. если важна структура таблицы(порядок столбцов) - то константы + шаблон на скрытом листе, с которым идет сравнение перед обработкой. Если порядок столбцов не важен и может меняться - либо именованные диапазоны по одной ячейке, либо поиск по наименованию(если имена не должны меняться). Есть и другие варианты(хранение списка заголовков на отдельном листе, например), все перечислять не вижу смысла. И да: если имен будет пара десятков - никак не отразится на производительности. Тем более если они будут "один диапазон - одна ячейка" и без всяких вычислений. Грузят как правило именованные диапазоны с вычислениями.
Дмитрий(The_Prist) Щербаков: если имен будет пара десятков - никак не отразится на производительности. Тем более если они будут "один диапазон - одна ячейка" и без всяких вычислений
вот это самое важное - именованные диапазоны отлично подходят для моей задачи, боялся только за падение производительности. Большое спасибо!
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
разумеется Любые вмешательства, кроме непосредственно заполнения таблицы данными, будут производится исключительно мной - учесть всего невозможно, так что разработка и любые правки алгоритмов или структуры остаются целиком на моей стороне
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Публичные константы или Именованные диапазоны. Как лучше обращаться к полям при их меняющихся именах и положении Public constants or Name Ranges. How to Request the Fields better when Names and Address can be change
Кириллическое название изменено, а латинское — нет
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Дмитрий(The_Prist) Щербаков: если имён будет пара десятков - никак не отразится на производительности. Тем более если они будут "один диапазон - одна ячейка" и без всяких вычислений. Грузят как правило именованные диапазоны с вычислениями
а, если сравнивать около сотни имён: в одном случае, каждое — это ссылка на одну ячейку шапки умной таблицы, а в другом случае, каждый — это ссылка на DataBodyRangeстолбца (10 тыс строк) умной таблицы? Будет ощутимая разница? В случае с ячейками шапки clHead нужно будет диапазон отдельно получать через
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Сложно сказать. Тестировать не стал, времени сейчас нет, но что-то подсказывает, что диапазон проиграет одной ячейке. Применительно к умным таблицам диапазоны(не единичные ячейки) могут "тупить" в большом количестве, т.к. имеют свойства растягиваться вместе с таблицей в последних версиях, даже если заданы статично. Типа таблица на 10 строк была, создал именованный диапазон A1:A10 со ссылкой на эту таблицу, потом таблицу расширил на 20 строк и диапазон автоматом расширится тоже до 20-ти строк. Что наводит на мысль, что перевычисление размера диапазона все же происходит, а это время и ресурсы. Поэтому я бы делал диапазона просто на ячейку заголовка на всякий случай. Такие адреса хранятся в книге просто как адрес ячейки, без привязки к умной таблице.
Дмитрий(The_Prist) Щербаков, и я продублирую Полностью согласен — лучше вычислять диапазон из ячейки, когда это нужно, а не тратить ресурсы на постоянную поддержку. . Спасибо большое!
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Почему вместо именованного диапазона не использовать сервисный лист с набором ссылок? Получаем 5 строк с 30-50 столбцами, которые очень просто создать. в коде взять нужное например
Jack Famous: Модераторам: прошу изменить название: Публичные константы или Именованные диапазоны. Как лучше обращаться к полям при их меняющихся именах и положенииPublic constants or Name Ranges. How to Request the Fields better when Names and Address can be change
БМВ, забавно, что совет ты дал, а название с "смешного" не поменял Юру что-ли просить? ))
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
БМВ: Почему вместо именованного диапазона не использовать сервисный лист с набором ссылок?
я, может, не понял идею, но это не выглядит удобным… Задача состоит в том, чтобы всегда иметь или вычислять текущее положение столбца с известными данными. Если бы название было неизменным, то можно было бы искать по нему, однако и это ещё не утряслось и может меняться. Заголовки столбцов в именах — отличное решение. Мало того, что они сдвигаются вместе со столбцами (а хранение десятков ячеек в именах не должно заметно отразиться на скорости работы книги), но есть и кое-что ещё, что мне пригодилось…
Как я сейчас использую имена в автоматизации
Для хранения данных в Excel я использую умные таблицы. При большом количестве столбцов в ней, возникает проблема контроля корректности заполнения. Чтобы в полях даты были только даты, чтобы пустых не было, где нельзя, чтобы ключи были уникальными, чтобы суммы не были отрицательными и соответствовали заданному округлению, чтобы в полях выбора из списка были значения только из этого списка и так далее… И нигде не было ошибок, разумеется. Для этого первым столбцом в каждой таблице идёт столбец проверки, в который выводится и подсвечивается зелёным УФ "ПОРЯДОК", если все проверки для строки успешно пройдены или "ОШИБКИ/ПУСТЫЕ/НеЧисло/НеДата/ДУБЛИ" и прочие критерии недопуска (подсвечены красным УФ) — если какое-либо поле заданную проверку не прошло. Сначала проверки производились формулами. Как только появилась необходимость проверять ДУБЛИ, то сразу ушёл к макросам. После создания этой темы, перешёл на систему проверки любой таблицы универсальной макрофункцией с контролем критериев проверки в именах (не надо лезть в код).
В этой задаче мне очень пригодились заголовки в именах: • для заголовков, столбцы которых нуждаются в проверке, используем уникальный префикс, который в других именах не используем. У меня это символ параграфа «§». • в комментариях к каждому имени указываем ключи проверки через заданный разделитель. Так, например, ключи «full•uniq•rng -5:20» означают, что это поле не может быть пустым, значения должны быть уникальными и находиться в диапазоне от -5 до 20 (обе границы попадают в диапазон). • пишем функцию с единственным параметром - лист. Так как у меня на листе не более 1ой умной таблицы, можно передать только его, функция переберёт все имена книги, определит, какие из них относятся к переданному листу (к умной таблице на нём), определит по префиксу столбцы, нуждающиеся в проверке и по ключам в комментариях к имени определит, какие именно проверки необходимо выполнить • выполнит все проверки в порядке очерёдности. Если очередная проверка не пройдена, то выведет в критерий проверки строки ТИП ошибки, НОМЕР поля, в которой она была найдена и текущее ИМЯ поля на момент запуска макроса. Если все проверки пройдены, то выведет "ПОРЯДОК". • в конце выведет массив-столбец в 1ый столбец проверки таблицы и выведет сообщение, всё-ли прошло успешно или присутствуют ошибки
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
БМВ, я предоставил текст названия. Нужно было просто поменять. Шутка невероятно хороша, но не когда она затянулась…
Если ты правда не понял, то Кириллица и Латиница - это про символы, а не про язык, в котором они используются. Можно было докопаться, что это не английский, а, например, американский или канадский…
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous, Алексей, я не понимаю на кой черт это второе название. Популяризировать планету для иностранцев? А если нет, что то что ты написал, что то на что я заменил - ничего не меняет.
БМВ: Популяризировать планету для иностранцев? А если нет, что то что ты написал, что то на что я заменил - ничего не меняет.
ну вообще да - продвигать Планету, как ресурс с информацией по Excel. Миш, а вот просто поменять нельзя - без размышлений? Нарушений же нет никаких - в чём проблема-то?
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous написал: ... продвигать Планету, как ресурс с информацией по Excel
Добрый день, Алексей. Англоязычным проще искать помощь на известных англоязычных сайтах по Excel и VBA, которые им выдаст первая страница их поисковиков. Впрочем, может, и дождемся прецедента По-английски немного дословный перевод получился, вряд ли сходу поймут даже то, что это про VBA. По крайней мере, там опечатка - Address должен быть во мн.числе. Непринципиально, но можно было бы и так: VBA - лучший способ ссылаться на поля таблицы независимо от их имен и порядка. VBA - best way to refer table fields regardless of their names and order.
Dear ZVI, Thank you very much for your post. We had long discussion with Jack yesterday evening about it and we didn't come to an agreement. However the thread name have been changed by Юрий М.
ZVI, приветствую! Спасибо, Владимир! Не знаю, почему, но БМВ почему-то таки не смог отпустить ситуацию — целую неделю от него добивался простой смены пояснения к теме — вариант на английском отображается маленьким шрифтом и не должен никому мешать, однако не тут-то было.
Цитата
ZVI: По-английски немного дословный перевод получился, вряд ли сходу поймут даже то, что это про VBA.
а это только лишний раз подтверждает, то, что я не собирался "выпендриваться", как об этом говорит медведь Написал, как смог…
Цитата
ZVI: Англоязычным проще искать помощь на известных англоязычных сайтах по Excel и VBA, которые им выдаст первая страница их поисковиков.
обычно да и с этой темой так скорее всего и будет, но, возможно, какая-то тема из-за своей узкой специфики у них и высветиться. Я исхожу из того, что правил это не нарушает, визуально искать тему не мешает, а значит зачем это вообще может быть нужно удалять…
Цитата
ZVI: VBA - лучший способ ссылаться на поля таблицы независимо от их имён и порядка VBA - best way to refer table fields regardless of their names and order.
если модераторы не будут против , то я прошу взять эти названия для главного и побочного полей - соответственно
P.S.: ну и в целом, с точки зрения модерации, совести и морали: учитывая оформление (его отсутствие) большинства всех остальных тем, докопаться до моего дублирования названия на английском — это какое-то предвзятое отношение и двойные стандарты. Как бы наш друг "звёздную болезнь" не подхватил — переживаю
UPD: вспомнил! Я часто использую "английские" термины в поиске своих тем. Вполне возможно, кстати, это и стало первопричиной дублирования названий — чтобы не мешать в одну кучу русское и английское.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄