Мар 12
Люблю заниматься VBA for Excel. Последнее время пытаюсь автоматизировать свою работу по максимуму. На вещи на которые уходило раньше много времени теперь затрачиваю два-три клика. Сделал расчёт разных корреляций, конвертер единиц, есть кое-какие задумки, потихоньку всё выливается в надстройку с файлом помощи ). Очень удобно.
Кто ещё занимается разработкой профессиональных утилит, что создаёте в Excel? И что интересно было бы реализовать?
Делимся думками, идеями, обсуждаем.
Опубликовано
01 Мар 2012
Активность
41
ответ
7260
просмотров
13
участников
1
Рейтинг
Контекст
VBA x5
1. Сделайте удобный модуль к Excel для изменения значений в таблице, двигая точки на графике...
2. График с 3-мя и более осями Y
а зачем ? )))
1. С помощью макросов нереализуемо ИМХО.
2. Ну можно попробовать накладывать несколько графиков с одинаковыми размерами и прозрачным фоном друг на друга. :)
В большинстве прямолинейных рачетов в экселе можно обойтись без вба, эксель сам по себе очень гибкий. ВПР СЦЕПИТЬ ЕСЛИ КОРЕЛЛ и т.д. А вот когда нужны циклы тут без вба уже никак.
Предлагаю выкладывать свои творения ) А то "я вот тут написал, но никому не покажу" звучит неуместно )
Круто ))
1. Первый вопрос я знаю. Кто-то из больных на голову посчитал что эта функция ни к чему в Офисе 2007-2010 и исключил её. Беспорно очень удобная была фича, сам пользовался. Но вот можно ли её реализовать с помощью имеющегося функционала на VBA что-то сомневаюсь. Скорее это из области "обратитесь к разработчику". Попробую узнать на форумах.
2. Если первый вопрос когда-то и существовал - в прошлых версиях, то этот вопрос однозначно никогда не был реализован, и, по всей видимости, не будет сделан в ближайших версиях. Тоже испытывал потребность в таком в своё время. Можно решить данную проблему с помощью стороннего софта. Напр., мне очень нравится Origin (www.originlab.com). Очень крутой софт и полностью заменяет весь анализ и визуализацию Excel. Кроме того его можно внедрять в Excel и управлять им в VBA Excel, используя объектную модель Origin. Также в самом Origin можно подключать листы Excel, либо использовать свои собственные по типу электронных таблиц.
1. вставка - объект - "Диаграмма Microsoft Graph" - меняете тип диаграммы на точечную и таскаете точки как в 2003 экселе.
2. наверное в экселе нереализуемо
Если есть желание никто не запрещает можно и выкладывать, люди скажут спасибо ). Я могу делиться знаниями. И кое-что могу и сделать, если не времязатратно.
Не всегда удобно использовать громоздкие формулы с коэффициентами по 6 знаков после запятой. Проще сделать функцию в VBA которая просит ввести три параметра.
Есть у меня одна хотелка, но лень и некогда :) Хотелось бы иметь универсальную функцию такого плана:
На вход подается указатель на график (обычный точечный типа ABC-plot), на серию в нём и указатель на столбец. На выходе в указанный график вставляются подписи к точкам из указанного столбца. Опционально ещё хотелось бы чтоб цвет точек так же брался из отдельного столбца.
Разово я такое проделывал, для определенного формата таблицы и листов, а вот чтобы универсальную сделать, чтоб можно было переносить из книги в книгу и чтоб не зависила от формата и расположения, руки не доходят.
Один оформленный "как надо" график легко клонировать на любые данные. Для этого сначала строите изначальный красивый график, потом для любых других данных строите графики как попало (по умолчанию), выделяете донора, ктрл+с, выделяете неоформленного акцептора, специальная вставка (наверху в меню), форматы. Готово )
По поводу форматирования диаграмм есть ещё легче путь - создать шаблон диаграммы и использовать его как пользовательский формат диаграммы. Выделяем график /Вкладка Конструктор/Тип/Сохранить как шаблон рядом же кнопка Изменить тип диаграммы/Папка Шаблоны и применяете свой тип.
А по поводу выбирать пользовательские подписи к точкам и цвет точек - идея очень хорошая. Мне тоже пригодится. Я подумаю, может и сделаю. С генератором идеи поделюсь )) А цвет точек в ячейках как указывать? Цветом же ячеек? Тогда ещё дальше - изменяешь цвет ячейки, жмёшь обновить, вуаля - цвета точек изменяются. ))
А подписи и цвета-то как он заменит без макроса? :)
Ну можно не цветом ячеек (вычислять цвет ячейки формулой по-моему пока что нельзя), а шестнадцатеричным кодом, либо английским названием типа "red", "magenta", т.е. чтобы можно было логику тоже вносить типа если точка плохая то красным ее, если хорошая то зеленым.
На счёт "цвета ячейки = цвет точки" это можно сделать, хотя они и намутили в последних версиях с цветовыми темами, но можно. Я разбирался с этим вопросом. Там два свойства отвечают за цвет, на скока помню.
Ну а логику - это уж лучше юзверь сам пусть вносит, иначе Эксель с ума сойдёт от нашей логики ))
Юзверь не сможет назначить цвет ячейкам без помощи макросов: единственная функиця, которая работает с форматом ячеек, которую я знаю, это ЯЧЕЙКА, и она не может узнать цвет ячейки. А представьте если надо нанести 100 точек на график, раскрасив их в два-три цвета по определенному формализованному признаку, это что, каждую вручную раскрашивать?
нее. Вот смотри. Есть у тебя сто точек. За них напр. отвечает ячейки А1:А100. Юзер раскрашивает эти ячейки обычной заливкой, ему то виднее как нужно. Если они меняются в случайном порядке то конечно нужно каждую ячейку расскрасить, ну а если первая половина одним цветом, то ведь можно и протянуть форматирование. Макрос же после "Обновить" считает цветовые индексы с каждой ячейки и присвоит эти индексы цвету точек.
Принципиально 1)перетаскивание точек на графике сделать можно и не так трудно с VBA. Однако создание универсальной утилиты чтобы работала при всех конфигурациях может быть проблематично из-за глюков самого Excel-a. Я как то делал интерактивные графики для 2003 где среди прочего можно было выбирать точки и появлялось специальное меню. Проблема была в масштабировании - необходимо конвертировать аппаратное положение мыши на экране в систему координат Excel. И тут выяснилось что встроенные функции Excel для конвертации системы координат работают точно только для одного масштаба (zoom, кажется, 75%).
Вот как раз тут проблема в том что надо вручную раскрашивать ячейки заливкой. В общем, я склепал небольшой частный пример.
http://www.petroleumengineers.ru/node/7181
Красиво, да. Похожий пример с надписями есть в книгн Уокенбаха. Да, действительно раскрашивать ячейки дольше, чем ставить цифры. Но в последних версиях Офиса палитра стала намного богаче, и если нужно раскрасить точки не в два-три цвета, а напр все точки чтобы отличались друг от друга, то возможно и цветом будет лучше. А автоматизировать этот процесс думаю можно как придумать.
Ну и в чём собственно проблема? Если в лени, то похоже вы её уже перебороли и решили свою проблему )))
Спасибо за пример. Думаю он многим пригодится.
Проблема в том, что это надо для каждой книги, формата и размера таблицы и графиков переписывать почти всё, а хотелось бы просто копипастить функцию и вешать на кнопку :)
За решение через объект - "Диаграмма Microsoft Graph" — спасибо
Посмотрел OriginPro такого количества кнопочек я не видел уже давно ;-) бедлам название такой проге. Но функциональность чувствуется солидная.
Может кому попадалась прога с удобным интерфейсом построения графиков.
Подскажите, плиз, как задать Range ячейки, если и строка, и столбец - переменные?
Не совсем ясна проблема. Ну самый топорный вариант - собрать адрес ячейки конкатенацией строк, примерно так:
myLetter = "A"
myNumber = 1
address = Format(myLetter)+Format(myNumber)
А вообще, почему бы не воспользоваться Cells(i,j) вместо Range, если нужна одна ячейка?
Потому что Cells (i,j) не работает, если i, j - переменные. ну или у меня руки кривые :(
через address попробую.
Update
кривизна рук обнаружена, спасибо большое :)
Дайте угадаю, .Value забыли написать? :)
Неа, мимо :) но ручки-то кривоваты :)
Golden software Grapher - удобнее и мощнее не встречал - в природе существуют также и портативные версии
Не соглашусь. За некоторой на первый взгляд топорностью фейса скрывается наикрутейший анализатор. Там полиномы до 9-ой степени можно строить (в excel 6). Данные на графике которые Excel просто не знал как интерполировать, Origin такими функциями аппроксимирует, что дух захватывает )))
Немного сложноват в освоении, но он того стоит. Ну а тому у кого руки к программированию расположены - вообще цены нет! Я уважаю Origin.
microsoft выпустил такую утилиту, которая помогает перетаскивать точки на графиках. работало на Excel 2007.
И как она зовётся?
очень много разных служебных макросов написал. Потом стало выходить так что пишу два раза одно и то же, просто старый макрос где то потерялся - делал под конкретную задачу и забыл.
Раньше писал все в личную книгу макросов, после нескольких перестановок системы, частых скидываний эксела с последующим восстановлением, когда не знаешь где последняя версия макроса понял что надо как то упорядочивать хранение. В итоге сделал файл, в который написал все макросы какие у себя нашел, привинтил интерфейс, написал комментариев чтобы не забыть. Доволен как питон(удав, слон, бегемот). ПРи надобности файл запускаю и он висит фоновой задачей, макросы при этом все активны. И легко можно перенести - все в одном файле.
по "двиганию" точек
http://blogs.office.com/b/microsoft-excel/archive/2009/11/02/excel-add-in-for-manipulating-points-on-charts-mpoc.aspx
в общем совсем не то что в 2003
Ну да, все к этому приходят. Я тоже сейчас параллельно делаю фейс и функции пишу. Есть правда проблемка одна, которую я самолично решить не могу пока - красивые иконки на кнопочки. В Офисе 2010 кнопки есть большие, и там лучше графику тоже красивую иметь. Как "В человеке должно быть всё прекрасно и душа и тело" )) Так и здесь.
Придётся наверно либо стащить где, либо заказать платно, чтобы под один стиль было.
да, это просто редактор значений )) Раньше было лучше
Напишите макрос, который при построении тренда будет взвешивать точки по площади., т.е. если есть скопление не подтягивать тренд к ним, а воспринимать как одну точку.
Ну тут проще проредить сами исходные данные, а не мутить алгоритм построения тренда. Т.е. если две точки данных попадают в заданную окрестность (задать размеры по x и по y), слепить их в одну (взять среднее арифметическое координат).
Какой смысл тогда во всем это VBA ? посчитать a*b*c*d=? и сделать красивые кнопочки?
А задача пострения тренда как раз задача со множеством условий, циклами, сортировкой, массивами. Это то место где можно кратно сократить ручную работу.
Вы неправильно поняли. Я говорил про внутреннюю структуру предполагаемого макроса. Макрос должен внутри себя породить новый набор данных и по нему построить тренд стандартным способом. Это намного проще, чем решать задачу построения тренда с какими-то неформализованными весами по площади. А внешне все будет выглядеть так, как вы хотите и результат будет такой же.
тогда да,я неправильно понял. именно так)
А на каком основании точки слеплять друг с другом (меру расстояния если другую возьмем - может по другому слепятся)?
Это формулировка задачи кластерного анализа. Поищите реализацию алгоритма полного связывания, Уорда (Ward) или метода k-средних. В языке R наверняка найдется в виде открытого скрипта - вот и перепишите в VBA, если именно через ёксель критично это делать.
Есть готовые пакеты с макросами. Русский например PLEX, он попроще. Буржуйские посложнее, тоже можно найти на просторах интернета, например Spotfire.