كيفية عمل منحنى معايرة خطي في Excel
يحتوي Excel على ميزات مضمنة يمكنك استخدامها لعرض بيانات المعايرة وحساب أفضل خط ملائم. يمكن أن يكون هذا مفيدًا عند كتابة تقرير مختبر كيميائي أو برمجة عامل تصحيح في قطعة من المعدات.
في هذه المقالة ، سننظر في كيفية استخدام Excel لإنشاء مخطط ، ورسم منحنى معايرة خطي ، وعرض صيغة منحنى المعايرة ، ثم إعداد صيغ بسيطة باستخدام الدالتين SLOPE و INTERCEPT لاستخدام معادلة المعايرة في Excel.
ما هو منحنى المعايرة وكيف يكون Excel مفيدًا عند إنشاء واحد؟
لإجراء معايرة ، يمكنك مقارنة قراءات الجهاز (مثل درجة الحرارة التي يعرضها مقياس الحرارة) بقيم معروفة تسمى المعايير (مثل نقاط التجمد وغليان الماء). يتيح لك هذا إنشاء سلسلة من أزواج البيانات التي ستستخدمها بعد ذلك لتطوير منحنى معايرة.
تحتوي المعايرة ذات النقطتين لميزان الحرارة باستخدام نقطتي التجميد والغليان للماء على زوجين من البيانات: أحدهما عند وضع مقياس الحرارة في ماء مثلج (32 درجة فهرنهايت أو 0 درجة مئوية) والآخر في الماء المغلي (212 درجة فهرنهايت) أو 100 درجة مئوية). عندما ترسم هذين الزوجين من البيانات كنقاط وترسم خطًا بينهما (منحنى المعايرة) ، ثم بافتراض أن استجابة مقياس الحرارة خطية ، يمكنك اختيار أي نقطة على الخط تتوافق مع القيمة التي يعرضها مقياس الحرارة ، وأنت يمكن العثور على درجة الحرارة “الحقيقية” المقابلة.
لذلك ، فإن الخط يملأ بشكل أساسي المعلومات بين النقطتين المعروفتين بالنسبة لك بحيث يمكنك أن تكون متأكدًا بشكل معقول عند تقدير درجة الحرارة الفعلية عندما يقرأ مقياس الحرارة 57.2 درجة ، ولكن عندما لا تقيس أبدًا “قياسيًا” يتوافق مع تلك القراءة.
يحتوي Excel على ميزات تسمح لك برسم أزواج البيانات بيانياً في مخطط وإضافة خط اتجاه (منحنى معايرة) وعرض معادلة منحنى المعايرة على الرسم البياني. يعد هذا مفيدًا للعرض المرئي ، ولكن يمكنك أيضًا حساب صيغة الخط باستخدام وظائف Excel SLOPE و INTERCEPT. عند إدخال هذه القيم في صيغ بسيطة ، ستتمكن من حساب القيمة “الحقيقية” تلقائيًا بناءً على أي قياس.
لنلقي نظرة على مثال
في هذا المثال ، سنطور منحنى معايرة من سلسلة من عشرة أزواج بيانات ، يتكون كل منها من قيمة X وقيمة Y. ستكون قيم X هي “معاييرنا” ، ويمكن أن تمثل أي شيء بدءًا من تركيز محلول كيميائي نقيسه باستخدام أداة علمية إلى متغير الإدخال لبرنامج يتحكم في آلة إطلاق الرخام.
ستكون قيم Y هي “الاستجابات” ، وستمثل قراءة الأداة المقدمة عند قياس كل محلول كيميائي أو المسافة المقاسة لمدى البعد عن المشغل الذي هبط فيه الرخام باستخدام كل قيمة إدخال.
بعد أن نصور منحنى المعايرة بيانياً ، سنستخدم وظيفتي SLOPE و INTERCEPT لحساب صيغة خط المعايرة وتحديد تركيز محلول كيميائي “غير معروف” بناءً على قراءة الأداة أو تحديد المدخلات التي يجب أن نعطيها للبرنامج بحيث يهبط الرخام على مسافة معينة من المشغل.
الخطوة الأولى: قم بإنشاء الرسم البياني الخاص بك
يتكون جدول البيانات النموذجي البسيط الخاص بنا من عمودين: X-Value و Y-Value.
لنبدأ بتحديد البيانات المراد رسمها في المخطط.
أولاً ، حدد خلايا العمود “X-Value”.
الآن اضغط على مفتاح Ctrl ثم انقر فوق خلايا العمود Y-Value.
انتقل إلى علامة التبويب “إدراج”.
انتقل إلى قائمة “الرسوم البيانية” وحدد الخيار الأول في القائمة المنسدلة “مبعثر”.
حدد السلسلة بالنقر فوق إحدى النقاط الزرقاء. بمجرد التحديد ، يحدد Excel الخطوط العريضة للنقاط التي سيتم تحديدها.
انقر بزر الماوس الأيمن فوق إحدى النقاط ثم حدد خيار “إضافة خط اتجاه”.
سيظهر خط مستقيم على الرسم البياني.
على الجانب الأيمن من الشاشة ، ستظهر قائمة “تنسيق خط الاتجاه”. حدد المربعات بجوار “عرض المعادلة على الرسم البياني” و “عرض قيمة مربع R على الرسم البياني”. قيمة R التربيعية هي إحصائية تخبرك بمدى ملاءمة الخط للبيانات. أفضل قيمة لمربع R هي 1.000 ، مما يعني أن كل نقطة بيانات تلامس الخط. مع نمو الفروق بين نقاط البيانات والخط ، تنخفض قيمة r التربيعية ، مع كون 0.000 أقل قيمة ممكنة.
ستظهر المعادلة وإحصاء R-squared لخط الاتجاه على الرسم البياني. لاحظ أن ارتباط البيانات جيد جدًا في مثالنا ، حيث تبلغ قيمة R التربيعية 0.988.
تكون المعادلة على شكل “Y = Mx + B” ، حيث M هو المنحدر و B هو تقاطع المحور y للخط المستقيم.
الآن وقد اكتملت المعايرة ، فلنعمل على تخصيص المخطط عن طريق تحرير العنوان وإضافة عناوين المحاور.
لتغيير عنوان المخطط ، انقر فوقه لتحديد النص.
اكتب الآن عنوانًا جديدًا يصف المخطط.
لإضافة عناوين إلى المحور س والمحور ص ، أولاً ، انتقل إلى أدوات المخطط> التصميم.
الآن ، انتقل إلى Axis Titles> Primary Horizontal.
لإعادة تسمية عنوان المحور ، حدد النص أولاً ، ثم اكتب عنوانًا جديدًا.
الآن ، توجه إلى Axis Titles> Primary Vertical.
سيظهر عنوان المحور.
أعد تسمية هذا العنوان بتحديد النص وكتابة عنوان جديد.
المخطط الخاص بك اكتمل الآن.
الخطوة الثانية: حساب معادلة الخط وإحصاء R-Squared
الآن دعنا نحسب معادلة الخط وإحصاء R-squared باستخدام وظائف Excel و SLOPE و INTERCEPT و CORREL المضمنة في Excel.
أضفنا إلى ورقتنا (في الصف 14) عناوين لتلك الوظائف الثلاث. سنجري العمليات الحسابية الفعلية في الخلايا الموجودة أسفل تلك العناوين.
أولاً ، سنحسب الميل. حدد الخلية A15.
انتقل إلى الصيغ> المزيد من الوظائف> الإحصاء> ميل.
في الحقل “Known_xs” ، حدد خلايا العمود X-Value أو اكتبها. ترتيب الحقول “Known_ys” و “Known_xs” مهم في دالة SLOPE.
انقر فوق موافق.” يجب أن تبدو الصيغة النهائية في شريط الصيغة كما يلي:
= ميل (C3: C12، B3: B12)
لاحظ أن القيمة التي تُرجعها الدالة SLOPE في الخلية A15 تطابق القيمة المعروضة في المخطط.
بعد ذلك ، حدد الخلية B15 ثم انتقل إلى الصيغ> المزيد من الوظائف> الإحصاء> التقاطع.
حدد أو اكتب خلايا العمود X-Value للحقل “Known_xs”. ترتيب الحقلين “Known_ys” و “Known_xs” مهم أيضًا في دالة INTERCEPT.
انقر فوق موافق.” يجب أن تبدو الصيغة النهائية في شريط الصيغة كما يلي:
= INTERCEPT (C3: C12، B3: B12)
لاحظ أن القيمة التي تُرجعها الدالة INTERCEPT تطابق تقاطع y المعروض في الرسم البياني.
بعد ذلك ، حدد الخلية C15 وانتقل إلى الصيغ> المزيد من الوظائف> الإحصائية> CORREL.
حدد أو اكتب النطاق الآخر من نطاقي الخلايا للحقل “Array2”.
انقر فوق موافق.” يجب أن تبدو الصيغة كما يلي في شريط الصيغة:
= CORREL (B3: B12، C3: C12)
لاحظ أن القيمة التي تُرجعها الدالة CORREL لا تتطابق مع قيمة “r-squared” على الرسم البياني. تعرض دالة CORREL “R” ، لذا يجب علينا تربيعها لحساب “R-squared.”
انقر داخل شريط الوظائف وأضف “^ 2” إلى نهاية الصيغة لتربيع القيمة التي تم إرجاعها بواسطة دالة CORREL. يجب أن تبدو الصيغة المكتملة الآن كما يلي:
= CORREL (B3: B12، C3: C12) ^ 2
اضغط دخول.
بعد تغيير الصيغة ، أصبحت قيمة “R-squared” مطابقة للقيمة المعروضة في الرسم البياني.
الخطوة الثالثة: إعداد الصيغ لحساب القيم بسرعة
يمكننا الآن استخدام هذه القيم في صيغ بسيطة لتحديد تركيز هذا الحل “غير المعروف” أو ما هي المدخلات التي يجب إدخالها في الكود بحيث تطير الكرة مسافة معينة.
ستعمل هذه الخطوات على إعداد الصيغ المطلوبة لتتمكن من إدخال قيمة X أو قيمة Y والحصول على القيمة المقابلة بناءً على منحنى المعايرة.
تكون معادلة السطر الأفضل ملاءمة بالصيغة “Y-value = SLOPE * X-value + INTERCEPT” ، لذا فإن حل “قيمة Y” يتم بضرب قيمة X و SLOPE ثم مضيفا التقاطع.
كمثال ، نضع صفرًا كقيمة س. يجب أن تكون القيمة Y التي تم إرجاعها مساوية لـ INTERCEPT الخاص بالسطر الأفضل ملاءمة. إنها تتطابق ، لذلك نعرف أن الصيغة تعمل بشكل صحيح.
يتم حل قيمة X بناءً على قيمة Y عن طريق طرح INTERCEPT من القيمة Y وقسمة النتيجة على الميل:
X-value=(Y-value-INTERCEPT)/SLOPE
كمثال ، استخدمنا INTERCEPT كقيمة ص. يجب أن تكون القيمة X التي تم إرجاعها مساوية للصفر ، لكن القيمة التي تم إرجاعها هي 3.14934E-06. القيمة التي تم إرجاعها ليست صفراً لأننا قطعنا نتيجة INTERCEPT عن غير قصد عند كتابة القيمة. تعمل الصيغة بشكل صحيح ، على الرغم من ذلك ، لأن نتيجة الصيغة هي 0.00000314934 ، والتي هي في الأساس صفر.
يمكنك إدخال أي قيمة X تريدها في الخلية الأولى ذات الحدود السميكة وسيقوم Excel بحساب القيمة Y المقابلة تلقائيًا.
إدخال أي قيمة Y في الخلية الثانية ذات الحدود السميكة سيعطي القيمة X المقابلة. هذه الصيغة هي ما ستستخدمه لحساب تركيز هذا المحلول أو ما هو الإدخال المطلوب لإطلاق الرخام لمسافة معينة.
في هذه الحالة ، تقرأ الأداة الرقم “5” لذا تقترح المعايرة تركيزًا قدره 4.94 أو نريد أن يسافر الرخام لخمس وحدات من المسافة ، لذا تقترح المعايرة إدخال 4.94 كمتغير إدخال للبرنامج الذي يتحكم في قاذفة الرخام. يمكننا أن نكون واثقين بشكل معقول من هذه النتائج بسبب ارتفاع قيمة R في هذا المثال.
