Построение одной точки Y на уже установленном линейном графике

Question

В настоящее время я пытаюсь ускорить наши испытания противопожарной пены на пожарной станции. Мне удалось создать линейный график или график рассеяния XY для построения графика нашего теста на электропроводность (пена должна испытываться при концентрации 2%, концентрации 3% и концентрации 4%). Следующим этапом процесса является вытекание пены из любого разряда (например, бампера), и проверка проводимости пены. Это позволяет нам проверить грузовик, чтобы убедиться, что он производит достаточную концентрацию пены в соответствии с требованиями FAA.

Я изо всех сил пытаюсь найти способ построения конечной точки проводимости, координаты Y без определенной координаты x, где координата X будет получена из того, где координата Y нанесена на линию. Я смог нарисовать точку на графике, но не могу найти способ привязать эту точку к какой-либо линии проводимости при испытании пены, какой она должна быть. Я приложил нарисованный от руки график того, что мы пытаемся сделать для пояснения. По сути, это действительно длительный процесс, чтобы определить, какой процент пены течет в грузовике, что можно получить только из графика электропроводности (координата Y) на установленном линейном графике, а затем определения процентного содержания пены (координата X)

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

Answer 1

Excel сделает это легко для вас.

1. Добавьте свои контрольные данные. Как и в таблице в вашей форме, вам понадобятся два столбца Control, Value а затем их значения ниже.
2. Преобразуйте ваши контрольные данные в таблицу Excel:Insert>Tables>Table для ваших контрольных значений (не обязательно, но рекомендуется).
3. Добавьте свои тестовые данные. Чтобы получить его так, как вы представили в форме, вам понадобятся три столбца Test Label, Test X, Test Y
4. Преобразуйте ваши тестовые данные в таблицу Excel (аналогично шагу № 2).
5. Создайте пустой XY/Scatter Chart Insert>Charts>Scatter>Scatter with Straight Lines & Markers .
6. Добавьте свою серию контрольных данных , с
   Series X Values = Control column
Series Y Values = Value column
7. Добавьте каждое из ваших значений теста в отдельную серию
   Series Name = Test Label
Series X Values = Test X
Series Y Values = Test Y
   Это позволит вам пометить их самостоятельно. Это не самый эффективный метод, но для нескольких точек данных это не так уж плохо. Если вы хотите более эффективный метод, прокомментируйте ниже, и я дам вам другой вариант.
8. Отформатируйте все по вкусу. Если вы хотите воссоздать точный вид формы, это возможно, вам просто нужно отрегулировать значения по горизонтальной и вертикальной осям и отформатировать все в соответствии с оригиналом. Как только вы это освоите, потребуется около 10 минут, чтобы все выглядело так:

Обновить

Чтобы автоматически рассчитать значение концентрации (X), основываясь на предоставленной информации, я рекомендую добавить еще одну таблицу (ее будет легче отслеживать).

Мы будем использовать линейную формулу y=mx+b чтобы вычислить значение концентрации (X) для вашего известного значения проводимости (Y). Однако, поскольку исходная строка данных состоит из двух сегментов (один из 2% -3%, другой из 3% -4%), вам необходимо рассчитать наклон (m) и пересечение (b) для каждого сегмента. К счастью, в Excel есть формулы для этого.

1. Создать новую таблицу с тремя столбцами
   Segment Label, Slope (m), Intercept (b) .
2. Добавьте две строки, одну для вашего
   2% Segment и 3% Segment .
3. Рассчитайте наклон (м) для каждого сегмента, используя данные из исходной таблицы:
   2% формула =SLOPE({1.01,1.36},{0.02,0.03})
   3% формула =SLOPE({1.36,1.65},{0.03,0.04})
4. Рассчитайте точку пересечения (b) для каждого сегмента, используя данные из исходной таблицы:
   2% формула =INTERCEPT({1.01,1.36},{0.02,0.03})
   Формула 3% =INTERCEPT({1.36,1.65},{0.03,0.04})
5. В Таблице тестовых данных вы рассчитаете значение концентрации (тест X) на основе вашего известного значения проводимости (тест Y). Однако, поскольку у вас есть два линейных сегмента с разными уклонами и перехватами, вам нужно выбрать соответствующий уклон и перехват на основе вашей проводимости. Поэтому мы вставим оператор IF, чтобы определить, какой набор использовать:
   =IF([@[Test Y]]<1.36,([@[Test Y]]-'2% intercept')/'2% slope',([@[Test Y]]-'3% intercept')/'3% slope')
   где вы заменяете элементы в '' соответствующими ссылками на ячейки (или, что еще лучше, именованными диапазонами).

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

Кроме того, FWIW, не удивляйтесь, если Excel округляет или вычисляет немного отличается от второго или третьего десятичного знака.