Увеличиваем производительность труда с помощью VBA

Последнее сообщение
visual73 2142 14
Мар 12

Люблю заниматься VBA for Excel. Последнее время пытаюсь автоматизировать свою работу по максимуму. На вещи на которые уходило раньше много времени теперь затрачиваю два-три клика. Сделал расчёт разных корреляций, конвертер единиц, есть кое-какие задумки, потихоньку всё выливается в надстройку с файлом помощи ). Очень удобно.

Кто ещё занимается разработкой профессиональных утилит, что создаёте в Excel? И что интересно было бы реализовать?

Делимся думками, идеями, обсуждаем.

softland 279 12
Мар 12 #1

1. Сделайте удобный модуль к Excel для изменения значений в таблице, двигая точки на графике...

2. График с 3-мя и более осями Y

AlNikS 878 13
Мар 12 #3

1. С помощью макросов нереализуемо ИМХО.

2. Ну можно попробовать накладывать несколько графиков с одинаковыми размерами и прозрачным фоном друг на друга. :)

FullChaos 875 13
Мар 12 #4

В большинстве прямолинейных рачетов в экселе можно обойтись без вба, эксель сам по себе очень гибкий. ВПР СЦЕПИТЬ ЕСЛИ КОРЕЛЛ и т.д. А вот когда нужны циклы тут без вба уже никак.

Предлагаю выкладывать свои творения ) А то "я вот тут написал, но никому не покажу" звучит неуместно )

visual73 2142 14
Мар 12 #5

Круто ))

1. Первый вопрос я знаю. Кто-то из больных на голову посчитал что эта функция ни к чему в Офисе 2007-2010 и исключил её. Беспорно очень удобная была фича, сам пользовался. Но вот можно ли её реализовать с помощью имеющегося функционала на VBA что-то сомневаюсь. Скорее это из области "обратитесь к разработчику". Попробую узнать на форумах.

2. Если первый вопрос когда-то и существовал - в прошлых версиях, то этот вопрос однозначно никогда не был реализован, и, по всей видимости, не будет сделан в ближайших версиях. Тоже испытывал потребность в таком в своё время. Можно решить данную проблему с помощью стороннего софта. Напр., мне очень нравится Origin (www.originlab.com). Очень крутой софт и полностью заменяет весь анализ и визуализацию Excel. Кроме того его можно внедрять в Excel и управлять им в VBA Excel, используя объектную модель Origin. Также в самом Origin можно подключать листы Excel, либо использовать свои собственные по типу электронных таблиц.

FullChaos 875 13
Мар 12 #6

1. вставка - объект - "Диаграмма Microsoft Graph" -  меняете тип диаграммы на точечную и таскаете точки как в 2003 экселе.

2. наверное в экселе нереализуемо

visual73 2142 14
Мар 12 #7

FullChaos пишет:

Предлагаю выкладывать свои творения ) А то "я вот тут написал, но никому не покажу" звучит неуместно )

Если есть желание никто не запрещает можно и выкладывать, люди скажут спасибо ). Я могу делиться знаниями. И кое-что могу и сделать, если не времязатратно.

Не всегда удобно использовать громоздкие формулы с коэффициентами по 6 знаков после запятой. Проще сделать функцию в VBA которая просит ввести три параметра.

AlNikS 878 13
Мар 12 #8

Есть у меня одна хотелка, но лень и некогда :) Хотелось бы иметь универсальную функцию такого плана:

На вход подается указатель на график (обычный точечный типа ABC-plot), на серию в нём и указатель на столбец. На выходе в указанный график вставляются подписи к точкам из указанного столбца. Опционально ещё хотелось бы чтоб цвет точек так же брался из отдельного столбца.

Разово я такое проделывал, для определенного формата таблицы и листов, а вот чтобы универсальную сделать, чтоб можно было переносить из книги в книгу и чтоб не зависила от формата и расположения, руки не доходят.

FullChaos 875 13
Мар 12 #9

Один оформленный "как надо" график легко клонировать на любые данные. Для этого сначала строите изначальный красивый график, потом для любых других данных строите графики как попало (по умолчанию), выделяете донора, ктрл+с, выделяете неоформленного акцептора, специальная вставка (наверху в меню), форматы. Готово )

visual73 2142 14
Мар 12 #10

Wasteland Rat пишет:

Есть у меня одна хотелка, ...

По поводу форматирования диаграмм есть ещё легче путь - создать шаблон диаграммы и использовать его как пользовательский формат диаграммы. Выделяем график /Вкладка Конструктор/Тип/Сохранить как шаблон рядом же кнопка Изменить тип диаграммы/Папка Шаблоны и применяете свой тип.

А по поводу выбирать пользовательские подписи к точкам и цвет точек - идея очень хорошая. Мне тоже пригодится. Я подумаю, может и сделаю. С генератором идеи поделюсь )) А цвет точек в ячейках как указывать? Цветом же ячеек? Тогда ещё дальше - изменяешь цвет ячейки, жмёшь обновить, вуаля - цвета точек изменяются. ))

AlNikS 878 13
Мар 12 #11

FullChaos пишет:

Один оформленный "как надо" график легко клонировать на любые данные. Для этого сначала строите изначальный красивый график, потом для любых других данных строите графики как попало (по умолчанию), выделяете донора, ктрл+с, выделяете неоформленного акцептора, специальная вставка (наверху в меню), форматы. Готово )

А подписи и цвета-то как он заменит без макроса? :)

AlNikS 878 13
Мар 12 #12

Ну можно не цветом ячеек (вычислять цвет ячейки формулой по-моему пока что нельзя), а шестнадцатеричным кодом, либо английским названием типа "red", "magenta", т.е. чтобы можно было логику тоже вносить типа если точка плохая то красным ее, если хорошая то зеленым.

visual73 2142 14
Мар 12 #13

На счёт "цвета ячейки = цвет точки" это можно сделать, хотя они и намутили в последних версиях с цветовыми темами, но можно. Я разбирался с этим вопросом. Там два свойства отвечают за цвет, на скока помню.

Ну а логику - это уж лучше юзверь сам пусть вносит, иначе Эксель с ума сойдёт от нашей логики ))

AlNikS 878 13
Мар 12 #14

Юзверь не сможет назначить цвет ячейкам без помощи макросов: единственная функиця, которая работает с форматом ячеек, которую я знаю, это ЯЧЕЙКА, и она не может узнать цвет ячейки. А представьте если надо нанести 100 точек на график, раскрасив их в два-три цвета по определенному формализованному признаку, это что, каждую вручную раскрашивать?

visual73 2142 14
Мар 12 #15

нее. Вот смотри. Есть у тебя сто точек. За них напр. отвечает ячейки А1:А100. Юзер раскрашивает эти ячейки обычной заливкой, ему то виднее как нужно. Если они меняются в случайном порядке то конечно нужно каждую ячейку расскрасить, ну а если первая половина одним цветом, то ведь можно и протянуть форматирование. Макрос же после "Обновить" считает цветовые индексы с каждой ячейки и присвоит эти индексы цвету точек.

VIT 1124 14
Мар 12 #16

Принципиально 1)перетаскивание точек на графике сделать можно и не так трудно с VBA. Однако создание универсальной утилиты чтобы работала при всех конфигурациях может быть проблематично из-за глюков самого Excel-a. Я как то делал интерактивные графики для 2003 где среди прочего можно было выбирать точки и появлялось специальное меню. Проблема была в масштабировании - необходимо конвертировать аппаратное положение мыши на экране в систему координат Excel. И тут выяснилось что встроенные функции Excel для конвертации системы координат работают точно только для одного масштаба (zoom, кажется, 75%).

AlNikS 878 13
Мар 12 #17

Вот как раз тут проблема в том что надо вручную раскрашивать ячейки заливкой. В общем, я склепал небольшой частный пример.

http://www.petroleumengineers.ru/node/7181

visual73 2142 14
Мар 12 #18

Wasteland Rat пишет:

Вот как раз тут проблема в том что надо вручную раскрашивать ячейки заливкой. В общем, я склепал небольшой частный пример.

Красиво, да. Похожий пример с надписями есть в книгн Уокенбаха. Да, действительно раскрашивать ячейки дольше, чем ставить цифры. Но в последних версиях Офиса палитра стала намного богаче, и если нужно раскрасить точки не в два-три цвета, а напр все точки чтобы отличались друг от друга, то возможно и цветом будет лучше. А автоматизировать этот процесс думаю можно как придумать.

Ну и в чём собственно проблема? Если в лени, то похоже вы её уже перебороли и решили свою проблему )))

Спасибо за пример. Думаю он многим пригодится.

AlNikS 878 13
Мар 12 #19

visual73 пишет:

Ну и в чём собственно проблема? Если в лени, то похоже вы её уже перебороли и решили свою проблему )))

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

softland 279 12
Мар 12 #20

За решение через объект - "Диаграмма Microsoft Graph" — спасибо

softland 279 12
Мар 12 #21

Посмотрел OriginPro такого количества кнопочек я не видел уже давно ;-) бедлам название такой проге. Но функциональность чувствуется солидная.
Может кому попадалась прога с удобным интерфейсом построения графиков.

Eliza 114 14
Мар 12 #22

Подскажите, плиз, как задать Range ячейки, если и строка, и столбец - переменные?

AlNikS 878 13
Мар 12 #23

Не совсем ясна проблема. Ну самый топорный вариант - собрать адрес ячейки конкатенацией строк, примерно так:

myLetter = "A"

myNumber = 1

address = Format(myLetter)+Format(myNumber)

А вообще, почему бы не воспользоваться Cells(i,j) вместо Range, если нужна одна ячейка?

Eliza 114 14
Мар 12 #24

Потому что Cells (i,j) не работает, если i, j - переменные. ну или у меня руки кривые :(

через address попробую. 

 

Update

кривизна рук обнаружена, спасибо большое :)

AlNikS 878 13
Мар 12 #25

Дайте угадаю, .Value забыли написать? :)

Eliza 114 14
Мар 12 #26

Неа, мимо :) но ручки-то кривоваты :)

gold01 148 12
Мар 12 #27

Golden software Grapher - удобнее и мощнее не встречал - в природе существуют также и портативные версии

visual73 2142 14
Мар 12 #28

softland пишет:
...бедлам название такой проге. Но функциональность чувствуется солидная. Может кому попадалась прога с удобным интерфейсом построения графиков.

Не соглашусь. За некоторой на первый взгляд топорностью фейса скрывается наикрутейший анализатор. Там полиномы до 9-ой степени можно строить (в excel 6). Данные на графике которые Excel просто не знал как интерполировать, Origin такими функциями аппроксимирует, что дух захватывает )))

Немного сложноват в освоении, но он того стоит. Ну а тому у кого руки к программированию расположены - вообще цены нет! Я уважаю Origin.

denfromufa 89 9
Мар 12 #29

microsoft выпустил такую утилиту, которая помогает перетаскивать точки на графиках. работало на Excel 2007. 

visual73 2142 14
Мар 12 #30

denfromufa пишет:

microsoft выпустил такую утилиту, которая помогает перетаскивать точки на графиках. работало на Excel 2007. 

И как она зовётся?

Myp3uJIKA 237 14
Мар 12 #31

visual73 пишет:

Люблю заниматься VBA for Excel. Последнее время пытаюсь автоматизировать свою работу по максимуму. На вещи на которые уходило раньше много времени теперь затрачиваю два-три клика. Сделал расчёт разных корреляций, конвертер единиц, есть кое-какие задумки, потихоньку всё выливается в надстройку с файлом помощи ). Очень удобно.

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

Раньше писал все в личную книгу макросов, после нескольких перестановок системы, частых скидываний эксела с последующим восстановлением, когда не знаешь где последняя версия макроса понял что надо как то упорядочивать хранение. В итоге сделал файл, в который написал все макросы какие у себя нашел, привинтил интерфейс, написал комментариев чтобы не забыть. Доволен как питон(удав, слон, бегемот). ПРи надобности файл запускаю и он висит фоновой задачей, макросы при этом все активны. И легко можно перенести - все в одном файле.

visual73 2142 14
Мар 12 #33

Myp3uJIKA пишет:

Доволен как питон(удав, слон, бегемот). ПРи надобности файл запускаю и он висит фоновой задачей, макросы при этом все активны. И легко можно перенести - все в одном файле.

Ну да, все к этому приходят. Я тоже сейчас параллельно делаю фейс и функции пишу. Есть правда проблемка одна, которую я самолично решить не могу пока - красивые иконки на кнопочки. В Офисе 2010 кнопки есть большие, и там лучше графику тоже красивую иметь. Как "В человеке должно быть всё прекрасно и душа и тело" )) Так и здесь.

Придётся наверно либо стащить где, либо заказать платно, чтобы под один стиль было.

FullChaos 875 13
Мар 12 #35

Напишите макрос, который при построении тренда будет взвешивать точки по площади., т.е. если есть скопление не подтягивать тренд к ним, а воспринимать как одну точку.

AlNikS 878 13
Мар 12 #36

Ну тут проще проредить сами исходные данные, а не мутить алгоритм построения тренда. Т.е. если две точки данных попадают в заданную окрестность (задать размеры по x и по y), слепить их в одну (взять среднее арифметическое координат).

FullChaos 875 13
Мар 12 #37

Какой смысл тогда во всем это VBA ? посчитать a*b*c*d=? и сделать красивые кнопочки?

А задача пострения тренда как раз задача со множеством условий, циклами, сортировкой, массивами. Это то место где можно кратно сократить ручную работу.

AlNikS 878 13
Мар 12 #38

Вы неправильно поняли. Я говорил про внутреннюю структуру предполагаемого макроса. Макрос должен внутри себя породить новый набор данных и по нему построить тренд стандартным способом. Это намного проще, чем решать задачу построения тренда с какими-то неформализованными весами по площади. А внешне все будет выглядеть так, как вы хотите и результат будет такой же.

FullChaos 875 13
Мар 12 #39

тогда да,я неправильно понял. именно так)

Гоша 1197 14
Мар 12 #40

FullChaos пишет:

Напишите макрос, который при построении тренда будет взвешивать точки по площади., т.е. если есть скопление не подтягивать тренд к ним, а воспринимать как одну точку.

А на каком основании точки слеплять друг с другом (меру расстояния если другую возьмем - может по другому слепятся)?

 

Это формулировка задачи кластерного анализа. Поищите реализацию алгоритма полного связывания, Уорда (Ward) или метода k-средних. В языке R наверняка найдется в виде открытого скрипта - вот и перепишите в VBA, если именно через ёксель критично это делать.

Fess 41 13
Апр 12 #41

Есть готовые пакеты с макросами. Русский например PLEX, он попроще. Буржуйские посложнее, тоже можно найти на просторах интернета, например Spotfire.

Go to top