Всем здравствуйте! Подскажите, пожалуйста, как сделать в Exel так, чтобы прямая линия прочерчивалась так, чтобы в получаемых областях сумма площадей "синих" и сумма площадей "красных" областей были равны. Для криволинейной и прямой линий нет графиков, только данные (цифры), т.е. готовая какая-то кривая. В общем, нужно найти угол между этой прямой линией и горизонтальной линией, да так чтобы при этом линия отсекала равные (суммарные) по площади зоны (синие и красные), т.е. сумма площади "синих" зон=сумма площади "красных" зон. Считайте "синий" - это "-", а "красный" - это "+". А сумма их равна, ну или стремится к, 0.
Говоря математическим языком, интеграл кривой и интеграл прямой должны быть равны. Если кривая задана таблично, то интеграл (в простейшем случае) - это сумма значений У, умноженная на разность крайних Х. Интеграл прямой - парабола. В общем, остается решить квадратное уравнение. Все в рамках школьного курса.
Там даже квадратного уравнения не получается, линейный расчет. Если прямая имеет уравнение y=a+bx, то ее интеграл на участке х от 0 до p S=(bp*p)/2+ap Отсюда b=2(S-ap)/(p*p)
В файле я воссоздал вашу кривую по точкам + линейный тренд (черная прямая), рассчитал наклон искомой прямой и построил ее (красная прямая).
Спасибо, Казанский. А, как находить уравнение прямой? Я имею ввиду то, что линия имеет одну (начало или конец) точку и все. И надо, чтобы из данной точки проходила линия, делящая кривую, так чтобы площади образуемые при этом, и сверху, и снизу (сумма синих и сумма красных) были равны. То есть, линия проходит автоматом так, пока сумма площадей отсекаемых и сверху и снизу не уравняются. Имею ввиду, что Exel сам будет (я так думаю, через команды цикла "ЕСЛИ", "ТО", "ИНАЧЕ") подбирать угол наклона (к вертикали, или к горизонтали) линии проходящей через одну, указанную нами точку (координаты) до того момента, пока суммы площадей синих и красных не уравняются. Нет ни одной функции, есть только набор координат. Или для реализации этой задачи придется обязательно находить функцию прямой, вручную. Спасибо.
Координат ЧЕГО? Выложите эти данные, а не картинку. Я исходил из того, что кривая построена по табличным данным, т.е. координатам, они в диапазоне А2:В16. Рассчитал угол наклона кривой, с учетом того, что она проходит через первую точку кривой, расчет в яч. В18, В19 (это для наглядности, можно сделать и в одной ячейке). Определил точки прямой в С2:С16 и построил график прямой (достаточно только двух точек, первой и последней, остальные для того, чтобы посчитать площадь под прямой и убедиться, что она равна площади под кривой). Как видите, уравнение прямой находится без всяких циклов и подборов. Убрал картинку из-под графика и линию тренда, возможно, это Вас смущает.
Казанский написал: Координат ЧЕГО? Выложите эти данные, а не картинку.
В файле Exel данные реальные, что есть. В скринах даны возможные варианты результата решения данной задачи (условные). Координаты (Х; У) (высота и расстояние) первой точки, (т.1) задаются, фиксированы. У второй точки (т.2) фиксирована координата Х (расстояние), а У (высота) может меняться (для уравнения (А1=А2) площадей синих и красных областей). Вторая точка (т.2) может совпадать с правой крайней точкой кривой, а может быть выше или ниже, но не правее или левее (Х2=const; У2=var) . Эту высоту Exel сам будет подбирать. Координата X первой точки (т.1) тоже фиксирована, строго совпадает с началом кривой (Х1=const; У1=var). А вот высоту, координату У, сами задаем, либо выше либо ниже начальной точки кривой. Или и вовсе совпадает с началом кривой. Вот, в зависимости от этих граничных условий и подбирается линия, вернее ее наклон к горизонтальной линии, или вертикальной. И, обратная задача, есть угол наклона к горизонтальной линии, есть координаты Х1 и Х2. Есть кривая, и ее координаты следовательно. Учитывая, что известен угол наклона, получается что У1 и У2 взаимосвязаны. Теперь задавая одну из них (У1 или У2, вторую автоматом вычислит, так как есть и длина проекции кривой на ось Х, то есть известны фиксированные границы - Х1 и Х2), перемещать эту линию, так, чтобы опять выполнялось условие равенства площадей.
Отличие от первого варианта в том, что шаг по Х неравномерный, и Х начинается не от 0. Поэтому площадь под кривой лучше вычислять методом трапеций, а в формулу войдет (Х-Хо) вместо Х.
Zizik написал: И, обратная задача, есть угол наклона к горизонтальной линии, есть координаты Х1 и Х2. Есть кривая, и ее координаты следовательно. Учитывая, что известен угол наклона, получается что У1 и У2 взаимосвязаны.
Более того, если известен угол наклона, то У1 и У2 определены. То есть из трех величин: угол наклона, У1, У2 - можно менять только одну, другие две определяются тем, что площадь трапеции под прямой постоянна (равна площади под кривой).
В общем, попытался реальный сделать, что то не особо получается. Приложил файл, того что получается.
Дано: 1. Значения в точках А2-А21 и В2-В21, которые постоянны. 2. Отметка начала кривой и начала прямой линий одна, общая, т.е. В2=С2=431.100 м.
Необходимо: Построить прямую линию, которая начинается в той же точке что и кривая [(А2;В2)=(А2;С2)=(0;431.100) м] и найти угол ее наклона к горизонтали (угол между прямой линией и горизонталью). При этом, необходимо придерживаться условий, что: 1. сумма площадей, отсекаемых прямой у кривой, сверху и снизу, были равны, то есть [ƩА(синяя область) м²=ƩА(красная область) м²]; 2. чтобы синяя линия была ровной прямой (отрезок).