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