В Excel'е есть таблица вида

в столбце L0 пропусков нет, в столбцах Lpi могут быть пропуски

по этой таблице надо вычислить формулы {=ЛИНЕЙН(A2:A5;B2:B5)}, {=ЛИНЕЙН(A2:A5;C2:C5)}, {=ЛИНЕЙН(A2:A5;D2:D5)}, ...

проблема в том, что функция ЛИНЕЙН() принимает только непрерывные массивы, без пропусков

как сформировать необходимые непрерывные массивы?

Это сообщение отредактировал(а) GoldFinch - 26.12.2008, 12:43

Функция Линейн (как и её собрат ЛГРФПРИБЛ) для нахождения коэффициентов регрессионного уравнения с оптимальной аппроксимацией  использует в своей работе метод наименьших квадратов, данные для которого берутся как раз из аргументов X
Соответственно, никаких пропусков быть не должно

Ручками, батенька, ручками:
CTRL+А(англ) - на любом элементе таблицы
CTRL+G
"Выделить"
"Пустые ячейки"
нажать 0
CTRL+Enter




Это сообщение отредактировал(а) FINANSIST - 27.12.2008, 16:40

ручками - не вариант, надо формулами\макросами\ещечемто


почему НОЛЬ? если координата точки отсутствует, это не значит что ее координата равна  нулю

С этим согласен, но вот тогда рождается встречный вопрос:
Почему именно ЛИНЕЙН? (видимо нет понимания - для чего он нужен)

Это сообщение отредактировал(а) FINANSIST - 12.1.2009, 13:33

контекст задачи такой
проводится эксперимент, в ходе которого для заданных значений L0 измеряются Lpi (Lp1...Lp8)
часть точек (L0,Lpi) измерить невозможно, эти точки отбрасываются
получается таблица как указано в посте #1, в ней практически всегда есть пустые ячейки
по этим точкам в таблице надо для каждого Lpi вычислить коэффициенты аппроксимирующей их прямой, функцией ЛИНЕЙН

т.е. на входе - таблица с 8 столбцами значений Lpi
на выходе - 8 пар коэффициентов прямых для каждого столбца Lpi

То есть необходима банальная интерполяция с помощью регрессионного анализа 
Тогда всё элементарно, Ватсон!
Функция линейн для нахождения коэффициентов регрессии в данном случае действительно необходима
Движемся по пути анализа статистических выбросов :
В данном конкретном случае заменяем статистические выбросы на пустые значения
Для упрощения, в роли ряда независимых переменных примем арифметическую прогрессию , а в ряде зависимых переменных "продырявим" некоторые значения

Далее делается очень нехитрая махинация- формируется вторая таблица где тупо удаляются (либо смещаются вниз) все подозрительные выбросы

Находятся коэфициенты регерссионного уравнения (КСТАТИ, ГДЕ ОНО?) по первым 7 парам,  и если достоверность аппроксимации R квадрат, стандартная ошибка регрессии, абсолютные и относительные показатели качества по остаткам модели устраивают, и эти остатки не имеют значительной корреляции с рядом независимых переменых, формируются математические ожидания по неполным парам путём подстановки значений независимых переменных в регрессионное уравнение

 НЕ чуствуешь системной ошибки в расстановке аргументов своей формулы ? (я не про коэф b0 и вывод статистики)
Сглаживать собираешься ряд зависимых переменных Li по ряду независимых переменных L0 а в первый аргумент функции засадил  как раз ряд независимых переменных L0, а надо то что собираешься апроксимировать ( какой смысл в интерполяции ряда L0 
в котором и так :

???

Вот набросал пример (он же во вложении)   



Присоединённый файл ( Кол-во скачиваний: 3 )
 _____Microsoft_Excel.rar 4,70 Kb

Идея с сортировкой конечно замечательная, только проблема в том, что формулы которая сортировала бы массивы вроде как нету
можно написать макрос, который сформирует эти непрерывные массивы для ЛИНЕЙН(), может даже сортируя таблицу на месте и копируя результаты ЛИНЕЙН(), но это как-то через ж***

вобщем я думаю проще написать макрос который вычислит коэфициенты прямых вместо ЛИНЕЙН(), благо формулы по которым она считает приведены в справке Excel'а

насчет ошибки в расстановке аргументов формулы - я было думал что эта функция получает коэфициенты прямой по точкам считая что обе координаты точки содержат ошибку, а получается что она считает что ошибка только в y? 

странное дело... я сравнил то как расстояние между точками и прямой рассчитанной по трем "разным" методам, оно получается разное %) хотя аналитически все методы тождественны
в первых двух вариантах используются формулы Excel'a , где расстояние расчитывается как d=m*x+b-y
там я менял x и y местами
в третьем варианте я считал расстояние как d=a*x+b*y+1

каждый раз - разные результаты

мистика какая-то...


upd: действительно, формула m*x+b-y дает расстояние только по оси y,
а формула d=a*x+b*y+c дает "реальное" расстояние до прямой
так что неудивительно что все значения не совпадают

Это сообщение отредактировал(а) GoldFinch - 15.1.2009, 16:22

Я бы не стал называть это сортировкой, это скорее исключение из модели тестовых данных (т.к.оставшиеся данные не сортируются)


Далеко не проще - метод наименьших квадратов для разных кривых  использует разные уравнения нахождения коэфициентов



Функции вообще плевать - есть в данных теоретическая ошибка или нет, координаты точек в аргументах или отгрузки продукции со склада
Есть ряд зависимых переменных Y который необходимо аппроксимировать, и есть ряд независимых  факторов X1,Х2...Хi- которые в той или иной степени влияют на Y (перед началом анализа очень желательно протестировать независимость факторов X1,Х2...Хi иначе появится эффект мультиколлинеарности)
Функция сама подбирает параметры работа метода наименьших квадратов и выводит коэфициенты. Естественно все аргументы должны быть рассатвлены корректно.


А почему именно с прямой - это обязательное условие анализа или незнание функционала функции?
Уравнение регресии не обязательно должно быть вида Y=kx+b (прямая),  
Линейн прекрасно работает и с полиномом степени m (Y=k0+k1X1+k2X2^2+....kmXm^m) , и с логарифмической функцией Y = b0+b1Ln(X) и  гиперболическая Y = b0+b1/X+b2/X2 и степенная, и бог знает ещё какая ( главное, чтобы коэфициенты были линейны относительно Y) А уж само уравнение выбирается взависимости от формы ряда фактических данных Yi
 

прямая, потому что надо найти прямую, потому что истинные значения точек лежат на прямой
и формула которую надо найти это уравнение прямой для точек на плоскости (x и y)

в экселе используется одна формула расчета для этого случая, она приведена в справке
эта формула рассчитывает расстояния как d=mx+b-y, т.е. расстояние считается только вдоль оси y, что соответствует предположению что координаты x точны, а y нет

в моем же случае, обе координаты точек содержат ошибки, поэтому ЛИНЕЙН() как в нее параметры не засовывай, мне не подходит, мне надо считать расстояние по формуле вида d=ax+by-c
(я еще хз как лучше оценивать расстояние, т.к. оси имеют разные единицы измерения, возможно нужно считать с весами)

GoldFinch, А идея оставить в исходном массиве только небитые пары не нравится? (Програмно это сделать вполне реально)
Вообще, в ходе обсуждения, смысл поставленной задачи становится для меня всё более туманный

задача простая - есть n точек на плоскости, по ним надо построить (рассчитать) прямую
обе координаты точек измерены с некоторой ошибкой

нда.... хотя различие в точности между разными методами невелико (1-2%), метод с правильным расчетом квадратов расстояний (d=ax+by+c) проигрывает "неправильным методам" на разных выборках... 
нипанимаю %) он же должен давать минимальное значение суммы квадратов расстояний, а оказывается есть еще меньшие значения суммы квадратов
бред какой-то %)