Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Оцифровка графика для расчёта
 
#1
31.08.2013 16:43:57
Здравствуйте!

Многим из нас приходится использовать графики для расчётов. Я использую такой алгоритм:
1. Оцифровываю скан
2. Подбираю формулу зависимости
3. Строю график на основе пересчитанных данных
4. Составляю формулу для расчёта с возможностью интерполяции и экстраполяции данных

Пока я рассмотрел:
- 3 метода оцифровки (вручную, MSGraph + Trend line, Graph Digitizer + Advanced Grapher)
- 5 методов реализации формулы (Excel, VBA-код С.М. и Подольского)
Результаты - в файле.

Нужна помощь по двум направлениям:
1. Доработать VBA-экстраполяцию
Хотелось бы получить:
- UDF вида =udf(Ряд_Х; Ряд_Y; Искомый_Х; 1/0 - исп./не исп. экстраполяцию; [Метод_прогнозирования])
- сквозные диапазоны X,Y с автоматической подрезкой по Y, исключающей не числовые данные
- высокая скорость пересчёта
2. Ускорить и уточнить оцифровку скана
Хотелось бы реализовать оцифровку в Excel по алгоритму:
- вставляем скан в UserForm, задаём координаты Xmin-Ymin, Xmax-Ymax
- проводим мышкой по линии графика
- получаем сглаженную линию, построенную макросом путём анализа цвета пикселей в заданном фрагменте
- корректируем линию (если это необходимо)
- получаем координаты точек с интервалом 1 пиксель

Помогите, пожалуйста!
Изменено: Acid Burn - 01.09.2013 22:06:52
 
 
 
#2
01.09.2013 19:02:14
Спасибо С.М. и Подольскому за предоставленный код.
Ребят, помогите кто-нибудь всё это доработать - второй день бьюсь над реализацией!
 
 
 
#3
01.09.2013 19:15:39
Нашёл код ZVI - 22 строки, всё работает!
Function Y(RngX As Range, RngY As Range, X As Double) As Double
 Dim i&, xmin#, xmax#, x1#, x2#, y1#, y2#, v
 i = RngX.Count
 xmin = RngX(1)
 xmax = RngX(i)
 If X <= xmin Then
   x1 = RngX(2):  x2 = xmin
   y1 = RngY(2):  y2 = RngY(1)
   ElseIf X >= xmax Then
     x1 = RngX(i - 1):  x2 = xmax
     y1 = RngY(i - 1):  y2 = RngY(i)
    Else
     i = 0
     For Each v In RngX.Value
       i = i + 1
       If X < v Then Exit For
     Next
     x1 = RngX(i - 1):  x2 = v
     y1 = RngY(i - 1):  y2 = RngY(i)
 End If
 Y = y1 + (X - x1) * (y2 - y1) / (x2 - x1)
End Function
 
 
 
#4
01.09.2013 19:52:15
Цитата
Нашёл код ZVI
На всякий случай, информация для размышления:
 
 
 
#5
01.09.2013 21:38:26
С.М., добавил Вашу версию в файл.
Среднее отклонение соответственно для θ=1,0 и θ=0,8:
- моим методом (A-grapher): -1,93561546528851E-06 и -0,000206967.
- Вашим методом (формула): -0,161846065887557 и 0,0362574808321124.

Мне нужно получить универсальный метод прогнозирования данных.
Сейчас есть код линейной (3 шт.), нелинейной, квадратичной и сплайн интерполяции. Какие ещё есть методы?
Как их все собрать в один код, отвечающий требованиям сообщения #1, чтобы использовать по ситуации?
Данная тема широко обсуждается на разных форумах. Предлагаю объединить наработки и в копилку!
Изменено: Acid Burn - 01.09.2013 22:10:59
 
 
 
#6
01.09.2013 22:27:45
Я так понимаю, ещё есть Conic extrapolation & French curve extrapolation.
Завтра вечером попытаюсь найти их реализацию на VBA.
 
 
 
#7
01.09.2013 23:59:40
"Где мои семнадцать лет ?" Burn, это я Вашей скорости.
(Повторю, что Вы уже узнали из Wiki)
Качество Экстраполяции, скорее, определяется не методом Интерполяции,
которая предназначена для вычисления значений между узловыми точками,
а Аппроксимацией, т.е. насколько хорошо вы подобрали (угадали) формулу приближения экспериментальных
(табличных) данных.
Полиномы высоких степеней плохо подходят для экстраполяции - их значения за пределами табличного диапазона
могут непредсказуемо намного отличаться от предполагаемого вами.
В Вашем случае может быть ограничиться вот такой: y = exp(a + b*x + c*x^2 + d*x^3),
или ещё короче y = exp(a + b*x + c*x^2, или ... эээ, не знаю.
Функцией ЛИНЕЙН() находите коэффициенты параболы логарифма игреков.
Возможно, внутри диапазона это несколько ухудшит предполагаемые показатели точности, но зато
за пределами диапазона известных данных (при экстраполяции), это не приведёт к "катастрофе".
И (!) не надо ничего интерполировать: y = f(x, a, b, ...)
Изменено: С.М. - 02.09.2013 00:04:05
 
 
 
#8
02.09.2013 10:43:18
С.М., анализировать каждый график Вашим методом неприемлемо по следующим критериям:
а) время (многие графики имеют причудливую форму)
б) точность (Вы сами сказали: "внутри диапазона это ухудшит предполагаемые показатели точности")  
в) задача (формулой получим автоэкстраполяцию - не применимо, если зависимость за границами не изучена)
г) квалификация (не каждый специалсит сможет выявить и проанализировать тренд)
д) юзабилити (формула, учитывающая почти всё, что надо, у меня заняла 1812 символов!)

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

Помощь нужна по следующим направлениям:
- выбрать наилучшие реализации каждого метода (критерии - скорость работы и читаемость кода)
- собрать всё воедино и доработать по требованиям #1

Если Вы или кто-то ещё готов с этим помочь, буду очень признателен!
У самого возможно не хватит времени и мозгов...
Изменено: Acid Burn - 02.09.2013 14:07:18
 
 
 
#9
02.09.2013 14:55:58
Цитата
а) время (многие графики имеют причудливую форму)
б) точность (Вы сами сказали: "внутри диапазона это ухудшит предполагаемые показатели точности")
в) задача (формулой получим автоэкстраполяцию - не применимо, если зависимость за границами не изучена)
г) квалификация (не каждый специалсит сможет выявить и проанализировать тренд)
д) юзабилити (формула, учитывающая почти всё, что надо, у меня заняла 1812 символов!)
Всё должно быть рассчитано на обычного юзера - функционально, быстро, просто и наглядно.
Тогда я (I personally only) посоветовал бы попробовать использовать функцию
ApproximationLine() [тьфу, название неудачное]
моей надстройки - там можно тупо "поиграть" параметрами-аргументами этой функции и выбрать из
вариантов что-нибудь "красивое", копипастом сохранить "проглаженные" значения и потом,
по этим новым точкам интерполировать и, наверное, если не очень далеко от крайних точек,
экстраполировать (интерполяционными функциями).
 
 
 
#10
02.09.2013 15:48:59
ApproximationLine видел - вещь!
Но "проглаженные" значения уже есть.
Вот теперь я и хочу
Цитата
по этим новым точкам интерполировать и, наверное, если не очень далеко от крайних точек,
экстраполировать (интерполяционными функциями).

Застрял с разными реализациями на VBA.
Новая версия файла.
 
 
 
#11
02.09.2013 17:56:47
See also:
Можно, наверное, исходный диапазон данных разбить на куски, каждый кусок аппроксимировать
полиномом небольшой степени (от 1 до 3) или двухпараметрической функцией (y=a*x^b; y=a*exp(b*x); y=a+b/x; ...)
так, чтобы значение конца предыдущего было бы равно значению начала следующего куска.
Тогда, при экстраполяции, есть шанс не "попасть пальцем в небо".
(да есть это, наверное, в Mathlab'ах и Mathcad'ах).
 
 
 
#12
02.09.2013 20:13:20
Отличная мысль! Тоже об этом думал.
Было бы отлично реализовать в UDF (как основной метод, но и остальные прикрутить - графики то разные).

Можно ли рассчитывать на Вашу помощь?
Изменено: Acid Burn - 02.09.2013 20:15:18
 
 
 
#13
02.09.2013 22:21:58
Цитата
Можно ли рассчитывать на Вашу помощь?
Burn, я бы с радостью, но времени нет: в Microsoft сейчас свободно место генерального директора,
вот, веду переговоры.
 
 
 
Страницы: 1
Читают тему
Наверх