Стиль работы с таблицами в экселе и VBA

Программирование на Visual Basic for Applications
@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Стиль работы с таблицами в экселе и VBA

Сообщение @Nik » 15.05.2007 (Вт) 16:21

Всем привет! Хочу несколько приобщиться к коллективу этого форума. Я уже 3 года занимаюсь экселем и программированием в ВБА. Меня всегда приятно поражали возможности экселя. Совмещение формул экселя и подпрограмм на ВБА даёт невероятно гибкий механиз работы с данными, не так ли ;) Однако, я столкнулся с одной проблемкой - несколько неудобно обращаться к данным таблиц как из самого экселя, так и вижуал бейсика. Постараюсь объяснить по подробнее.
Род моей работы в экселе заключается в том, что из ВБА идёт SQL-запрос к базе. После получения данных идёт дальнейшая их обработка, но уже при помощи формул экселя... Т.е. задача закл. в том, чтобы перед выполнением запроса очистить старые данные, получить новые, определить их количество и на это количество записей распространить строку с формулами для дальнейшей обработки данных. Поскольку данных может быть довольно много, а формулы довольно сложные, то пересчёт листа занимает некоторое время, что создаёт неудобство для дальнейшей работы. Поэтому формулы приходится заменять значениями (оставив лишь одну строку с формулами). И всё это приходится делать каждый раз, при получении новых данных.
Далее, при обращении к данным из ВБА требуется обеспечить относительную адресацию к данным, чтобы при перемещении столбцов не надо было каждый раз делать замену адресов во всём проекте ВБА. К тому же, кто с этим сталкивался, знает всю "корявость" (неудобство) синтаксиса при обращении к данным таблиц.

А теперь вопрос: есть ли такие, у кого примерно такой же род работы в экселе и кто как для себя пытался автоматизировать работу, найти способ для более простого обращения к данным таблиц и уйти от абсолютной привязки в адресах ?
Благодарю за внимание.

Димитрий
Продвинутый пользователь
Продвинутый пользователь
 
Сообщения: 194
Зарегистрирован: 26.01.2005 (Ср) 22:47
Откуда: Волгоград

Сообщение Димитрий » 15.05.2007 (Вт) 19:32

После столь великолепного объяснения вопроса :wink: , сразу вспоминается:
1. А может не вываливать все данные сразу, а переложить часть функций на SQL (довольно шустрый механизм).
2. И на листе и в VBA можно обращатся к именованым диапазонам.

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 16.05.2007 (Ср) 10:40

Полностью с вами согласен.... Так я и делаю, но меня ужасно напрягает для любой мелочи писать строку типа этой:
Range(Cells(Range("name1").Row+var1, Range("name2").Column:Cells(Range("name1").Row+Range("..._Count"), Range("name2").Column).ClearContents
Вот я и спрашиваю кто как для себя пытался оптимизировать синтаксис ?

alibek
Большой Человек
Большой Человек
 
Сообщения: 14205
Зарегистрирован: 19.04.2002 (Пт) 11:40
Откуда: Russia

Сообщение alibek » 16.05.2007 (Ср) 12:28

А кто мешает тебе создать функцию RectRange(LeftTop,RightBottom) As Range и делать RectRange("A1","C4").ClearContents?
Lasciate ogni speranza, voi ch'entrate.

KL
Microsoft MVP
 
Сообщения: 483
Зарегистрирован: 30.10.2005 (Вс) 0:31
Откуда: Madrid

Сообщение KL » 16.05.2007 (Ср) 14:52

можно еще использовать именованные формулы [меню Вставка-Имя-Присвоить...] для создания динамических рангов (см. приложение)
Вложения
Book1.xls
(20.5 Кб) Скачиваний: 844
Привет,
KL

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 17.05.2007 (Чт) 9:01

Да, интересно... ! Идея понятна.... Это новое поле деятельности для меня ;) Похоже, что это как раз то, что я искал... Спасибо большое...

Не зря я всегда восхищался возможностями экселя..... ;)

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Интересный стиль работы с таблицами

Сообщение @Nik » 05.06.2007 (Вт) 13:50

Ну вот, сделал более-менее законченный вариант, который можно предложить. Ещё раз поясню идею.
Преследуется следующие цели:
1. Упрощение синтаксиса при обращении к данным таблиц переменной длины как из формул экселя так и ВБА.
2. Уход от зависимости местоположения таблиц в книге, их размера (в длину, и в ширину) при обращении к ним из ВБА.
3. Увеличение скорости пересчёта книги, содержащей таблицы с долговычисляемыми формулами.

Для этого предлагаю принять следующие меры: ввести две доп. ячейки, расположение которых будет обозначать левую, правую и верхнюю границы данных таблицы и указывать на расположение строки формул таблицы. Содержимое одной ячейки – будет количество строк таблицы, а другой – расстояние от строки формул до начала данных таблицы.
Строка формул выполняет 2 функции – содержит имена столбцов таблицы и служит для возможности повышения скорости пересчёта книги. Если на листе имеются долгопересчитываемые таблицы данных, то можно заменить все значения таблицы формулами, оставив лишь одну строку с формулами. Предполагается, что эта строка может располагаться либо в качестве первой строки таблицы, либо находиться на несколько строк выше таблицы. Потом эту строку всегда можно скрыть. После этого с таблицей можно делать всё, что угодно (удалять строки (первую, средние, последнюю), добавлять/удалять столбцы на этапе разработке (в т.ч. первый и последний)) и всё это не вызовет никаких проблем при обращении к данным таблицы из экселя и VBA. В дальнейшем, при добавлении строк таблицы либо при необходимости обновления данных таблицы, нужно будет только вызвать одну процедуру, которая распространит строку формул на всю таблицу, пересчитает её заново и опять заменит формулы значениями.
Если необходимости заменять формулы значениями нет – можно этого не делать. В таком случае в качестве строки формул удобно использовать первую строку таблицы.
Упрощение синтаксиса предполагается произвести за счёт использования имён. Каждая таблица должна иметь своё имя. Каждый столбец состоит из имени таблицы, знак "_" и, собственно, имя столбца. (Напр., если Таблица называется "Test", а столбец "Count", то полное имя столбца будет "Test_Count "). Т.о. в каждой таблице может быть свой столбец "Count".
Поскольку длина таблицы динамическая, то имени столбца будет присвоена формула и для того, чтобы "вспомнить" правильное написание имени столбца необходимо открывать "Вставка/Имя/Присвоить..." и там его искать. Для того чтобы далеко не лезть и сразу "вспомнить" имя любого столбца, мы опять обращаемся к строке формул. Каждая её ячейка имеет такое же имя, как и весь столбец, но с дополнительным знаком "_" в конце. Т.о. при написании формул в случае обращения к какому-либо столбцу нужно всего лишь ткнуть мышкой в соотв. ячейку строки формул таблицы и удалить посл. знак "_" !
Во вложении на листах "Пример1" и "Пример2" вы видите пример таблиц и способ обращения к данным их столбцов. Можете открыть "Вставка/Имя/Присвоить..." и просмотреть созданные имена, их диапазоны и формулы их задающие.

Теперь пару слов о процедурах, облегчающих работу с таблицами. В модуле Kernel имеется 2 типа процедур. Одни направлены на облегчение создания таблиц, имён столбцов, их переименования и изменения адреса, другие предназначены для обновления данных таблиц.
Рассмотрим первую группу процедур. При открытии файла "Kernel.xls" появится панель инструментов "Мои таблицы". Первая кнопка – "Создать таблицу". Как я уже говорил, подразумевается, что таблица состоит из строки формул и данных, располагающихся либо с самой строки формул, либо на несколько строк ниже. Для создания таблицы выделите строку формул таблицы (либо пустое место, где вы её планируете разместить), затем удерживая "Ctrl" щёлкните на любую ячейку из той строки, где начинаются (будут начинаться) данные и нажмите "Создать таблицу" на панели инструментов "Мои таблицы" (см. лист "Шаг1" во вложении). После задания имени таблицы и нажатия "Ок" создадутся вспомогательные граничные ячейки и некоторые имена. Однако это ещё не все. Не задано имя счётного столбца (см. лист "Шаг2" вложения").
Для присвоения столбцу таблицы имени необходимо выделить ячейку в строке формул таблицы и нажать кнопку "Создать именованый столбец". Если будет выделена ячейка не в строке формул таблицы, то будет создан простой динамический диапазон. Имя созданного столбца для удобства будет отличаться от его первой ячейки лишь одним символом "_".
Далее на панели инструментов идёт кнопка "Изменить адрес имени". Иногда бывает необходимость изменить адрес псевдонима ячейки. Для этого нужно просто сначала выделить эту ячейку, а затем с контролом выделить ту ячейку, куда вы хотите переместить этот псевдоним. При этом, если перемещаемая ячейка была началом динамического диапазона или заголовком столбца таблицы (в строке формул), то вместе с ней переместиться и весь диапазон (столбец) ! :-)
Следующая, довольно полезная кнопка – "Переименовать". Выделяете ячейку, псевдоним которой вы хотите переименовать на нажимаете кнопку "Переименовать". После переименования псевдонима на всём листе в области формул будет произведён поиск старого имени и замена его новым. Затем подобный поиск и замена будет произведена в области формул имён книги :-).
Наконец, последняя кнопка – удаление имён. Выделите область и нажмите "Удалить имя". Будут удалены все имена для ячеек, попадающих в выделенную область (имена, адреса которых вычисляются формулами и попадают в выд. область удалены не будут).

Далее, рассмотрим процедуры, предназначенные для облегчения обновления таблиц. Их всего 4: TblClr, TblFillF, TblFillV и TblValues.
TblClr (TblName As String, Optional FromCol As String, Optional ToCol As String) – очистить таблицу, оставив лишь строку формул.
TblName – имя таблицы – то, которое задавалось при нажатии на кнопку "Создать таблицу";
FromCol и ToCol – имена столбцов, с которого по который очищать таблицу. Если опущен один из них или оба – выбирается первый столбец таблицы, последний либо вся таблица соответственно. Суть этих аргументов одинаковая для всех нижеописанных процедур.
TblFillF (TblName As String, Rows As Variant, Optional FromCol As String, Optional ToCol As String) – распространить строку формул на Rows строк вниз начиная со строки начала данных таблицы. Аргумент "Rows" может быть задан как обычным числом, так и именем ячейки, в которой находится число.
TblFillV (TblName As String, Rows As Variant, Optional FromCol As String, Optional ToCol As String) - распространить строку формул на Rows строк вниз начиная со строки начала данных таблицы, а затем заменить формулы значениями (оставив лишь строку формул)
TblValues (TblName As String, Optional FromCol As String, Optional ToCol As String) – заменить значениями все данные таблицы (оставив лишь строку формул). Предполагается, что таблица заполнена и количество строк известно (оно хранится в ячейке "TblName_Rows").

Во вложении имеется лист "Пример1", в коде которого сравнивается синтаксис использования вышеперечисленных процедур и как можно обойтись без них.
На листах "Пример1" и "Пример2" я привёл пару демонстрационных таблиц, которые созданы по вышеизложенной методике. Предлагаю обратить внимание на лаконичность синтаксиса в формулах. Возможно некоторые откроют для себя некоторые интересные моменты. В частности, предлагаю взглянуть на, на мой взгляд, интересную формулу в столбце "Tbl3_Sum" таблицы "Tbl3" на листе "Пример2". Она подсчитывает общую сумму чисел в столбцах Tbl1_Col1:Tbl1_Col2 таблицы "Tbl1" для заданного кода, во всех строках, где он встретился:
{=СУММ((D7=Tbl1_Code)*Tbl1_Col1:Tbl1_Col2)}

Хочу обратить особое внимание на то, что пользоваться данным методом работы с таблицами могут все, даже не владеющие ВБА! Просто, так намного удобнее – формулы становятся более удобочитаемыми, лаконичными и короткими.
У кого родились идеи по модернизации – высказывайте или делайте сами и дополняйте модуль.

Жду ваших отзывов, рекомендаций, советов или просьб, а так же с удовольствием отвечу на все ваши вопросы... Мне будет очень приятно узнать, если мой труд кто-то сочтёт полезным и для себя... ;-)

Да, и большое спасибо ув. KL !!! Некоторые моменты, используемые в данном методе работы с таблицами, я позаимствовал именно у него... :-)
Вложения
Kernel.xls
(118.5 Кб) Скачиваний: 778

Krasnaja Shapka
Обычный пользователь
Обычный пользователь
Аватара пользователя
 
Сообщения: 87
Зарегистрирован: 26.10.2006 (Чт) 12:13
Откуда: Киев

Сообщение Krasnaja Shapka » 05.06.2007 (Вт) 16:08

Выбранное приложение больше не существует

404 File Not Found: The File files/kernel_238.xls does not exist.
Если ясность вашего объяснения исключает ложное толкование, все равно кто-то поймет вас неправильно.

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 05.06.2007 (Вт) 17:49

пардон....
Вложения
Kernel.xls
(118.5 Кб) Скачиваний: 756

@Nik
Обычный пользователь
Обычный пользователь
 
Сообщения: 80
Зарегистрирован: 14.05.2007 (Пн) 12:11

Сообщение @Nik » 11.06.2007 (Пн) 17:40

Друзья, ну неужели никто не разобрался что это за "пургу" я тут нагнал ? :-) Посмотрите по синтаксису процедур, тогда то длинное описание вообще не пригодится...
Ну не выкладывал бы я весь этот "гон", если бы не видел в нём действительно ощутимой пользы при разработке формул и программ в экселе. Вникните и вы не пожалеете затраченного времени... ;-)
Спрашивайте, задавайте вопросы кому что непонятно......

СТГ
Начинающий
Начинающий
 
Сообщения: 1
Зарегистрирован: 27.06.2014 (Пт) 9:29

Re: Стиль работы с таблицами в экселе и VBA

Сообщение СТГ » 27.06.2014 (Пт) 9:37

@Nik хорошо поработал, похвально) жаль по времени много уже воды утекло)


Вернуться в VBA

Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 12

    TopList