22. تقريب بيانات Excel

عند تجميع البيانات لتقرير أو عرض تقديمي في PowerPoint، يكون تقريب الإجماليات في Excel مشكلة متكررة. يكون من المفضل غالبًا مطابقة الإجماليات المقربة لإجمالي القيم المضافة المقربة تمامًا، ولكن يصعب تحقيق هذا. على سبيل المثال، ادرس الجدول التالي:

عند تقريب القيم إلى أعداد صحيحة باستخدام دالة تنسيق الخلايا في Excel، نحصل على الجدول التالي. تكون الإجماليات التي تظهر "غير محسوبة بشكل صحيح" بخط غامق:

بالمثل، عند استخدام دوال تقريب Excel القياسية، يتم حساب إجماليات القيم المقربة على نحو صحيح ولكن تتراكم أخطاء التقريب وغالبًا ما تنحرف النتائج إلى حد كبير عن الإجماليات الفعلية للقيم الأصلية. يعرض الجدول التالي النتيجة =ROUND(x,0) للمثال السابق. تظهر الإجماليات التي تنحرف عن القيمة الأصلية بمقدار 1 أو أكثر بخط غامق:

باستخدام وظيفة تقريب think-cell، يمكنك الوصول إلى إجماليات مقربة بصورة متسقة بأدنى حد من "الأخطاء": في حين يتم تقريب معظم القيم إلى أقرب عدد صحيح، يتم تقريب قيم قليلة في الاتجاه المعاكس، لذلك يتم الحفاظ على الحسابات الصحيحة دون تراكم خطأ التقريب. نظرًا لوجود العديد من الاحتمالات للوصول إلى الإجماليات المقربة بشكل صحيح بواسطة القيم المتغيرة، يختار البرنامج حلاً يتطلب الحد الأدنى من عدد القيم المتغيرة والحد الأدنى للانحراف عن القيم الدقيقة. على سبيل المثال، يُفضل تقريب 10.5 إلى 10 عن تقريب 3.7 إلى 3. يوضح الجدول التالي الحل الأمثل للمثال الموضح أعلاه، مع تمييز القيم "الخاطئة" بخط غامق:

للوصول إلى هذه النتيجة في عمليتك الحسابية، حدد النطاق المعني من خلايا Excel. بعد ذلك، انقر فوق الزر في علامة التبويب الصيغ، وإذا لزم الأمر، فاضبط دقة التقريب باستخدام المربع المنسدل في شريط الأدوات.

22.1

استخدام وظيفة التقريب في think-cell

22.2

قيود وظيفة التقريب في think-cell

22.3

استكشاف أخطاء صيغ TCROUND وإصلاحها

22.1 استخدام وظيفة التقريب في think-cell

تتكامل وظيفة تقريب think-cell بسلاسة في برنامج Microsoft Excel لتوفر مجموعة من الدوال المماثلة لدوال التقريب القياسية في Excel. يمكنك تطبيق هذه الدوال بسهولة على بياناتك الخاصة باستخدام مجموعة أزرار شريط أدوات تقريب think-cell في علامة التبويب الصيغ.

22.1.1 معلمات التقريب

على غرار دوال Excel، تحتوي دوال تقريب think-cell على معلمتين:

x

القيمة المطلوب تقريبها. قد تكون قيمة ثابتة أو صيغة أو مرجعًا لخلية أخرى.

n

دقة التقريب. يعتمد معنى هذه المعلمة على الدالة التي تستخدمها. المعلمات الخاصة بدوال think-cell هي نفسها الخاصة بدوال Excel المكافئة. راجع الجدول أدناه للاطلاع على أمثلة.

لا تستطيع وظيفة تقريب think-cell التقريب إلى قيم صحيحة فحسب، بل يمكنها التقريب أيضًا إلى أي مضاعف. على سبيل المثال، إذا كنت تريد تمثيل بياناتك على أساس 5-10-15-... درجة، فقرّب ببساطة إلى مضاعفات العدد 5. باستخدام المربع المنسدل في شريط تقريب think-cell، يمكنك ببساطة كتابة دقة التقريب المطلوبة أو تحديدها. تختار وظيفة تقريب think-cell الدالة والمعلمات المناسبة لك. يُقدم الجدول التالي بعض الأمثلة لتقريب قيم x- معينة باستخدام شريط الأدوات مع معلمة n- الخاصة بها.

x = n =	100	50	2	1	0.01
1.018	0	0	2	1	1.02
17	0	0	18	17	17.00
54.6	100	50	54	55	54.60
1234.1234	1200	1250	1234	1234	1234.12
8776.54321	8800	8800	8776	8777	8776.54

في حالة عرض القيم بطريقة غير متوقعة، تأكد من تعيين تنسيق خلايا Excel على عام واتساع الأعمدة بما يكفي لعرض جميع المنازل العشرية.

الزر	الصيغة	الوصف
	TCROUND(x, n)	اترك لوظيفة تقريب think-cell مهمة تحديد لأي من أقرب المضاعفين سيتم التقريب من أجل تقليل أخطاء التقريب إلى أدنى حد.
	TCROUNDUP(x, n)	افرض تقريب x بعيدًا عن الصفر.
	TCROUNDDOWN(x, n)	افرض تقريب x نحو الصفر.
	TCROUNDNEAR(x, n)	افرض تقريب x لأقرب مضاعف من الدقة المطلوبة.
		قم بإزالة جميع دوال تقريب think-cell من الخلايا المحددة.
		حدد مضاعف التقريب المطلوب أو اكتبه.
		قم بتمييز جميع الخلايا التي قرر think-cell تقريبها إلى المضاعف الأبعد من أقرب مضاعفين بدلاً من التقريب إلى المضاعف الأقرب منهما.
		تشير العجلة الدوارة إلى أن وظيفة تقريب think-cell مشغولة.

للحصول على أفضل النتائج بأقل قدر ممكن من الانحراف عن القيم الأساسية، ينبغي استخدام TCROUND كلما أمكن ذلك. لا تستخدم سوى الدوال الأكثر تقييدًا TCROUNDDOWN، أو TCROUNDUP، أو TCROUNDNEAR إذا دعت الضرورة لذلك.

تنبيه: يجب عدم استخدام دوال غير حتمية مثل RAND() داخل أي من صيغ TCROUND. إذا أعادت الدوال قيمة مختلفة في كل مرة يتم فيها تقييمها، ستقوم وظيفة تقريب think-cell بارتكاب أخطاء عند حساب القيم.

22.1.2 تخطيط عملية الحساب

تخطيط المستطيل في المثال أعلاه هو لغرض العرض فقط. يمكنك استخدام الدوال TCROUND لتحديد عرض تجميعات عشوائية منتشرة في ورقة Excel لديك. تعمل المراجع ثلاثية الأبعاد إلى أوراق أخرى والارتباطات بملفات أخرى في Excel أيضًا.

22.1.3 تعيين موضع دوال TCROUND

نظرًا لأن الغرض من دوال TCROUND هو التحكم في ناتج الخلية، فإنها يجب أن تكون الدوال الأكثر بعدًا:

خاطئة:	=TCROUND(A1, 1)+TCROUND(SUM(B1:E1), 1)
صحيحة:	=TCROUND(A1+SUM(B1:E1), 1)
خاطئة:	=3*TCROUNDDOWN(A1, 1)
صحيحة:	=TCROUNDDOWN(3*A1, 1)

إذا حدث وأدخلت عنصرًا بطول أسطر الأمثلة الخاطئة، فستقوم وظيفة تقريب think-cell بإخطارك بقيمة خطأ Excel #VALUE!.

22.2 قيود وظيفة تقريب think-cell

دائمًا ما تجد وظيفة تقريب think-cell حلاً للتجميعات العشوائية باستخدام الإجماليات الفرعية والإجماليات. كما تُقدم وظيفة تقريب think-cell حلولاً معقولة لبعض الحسابات الأخرى تتضمن الضرب والدوال الرقمية. ولكن، لأسباب حسابية، لا يمكن ضمان وجود حل مقرب بصورة متسقة بمجرد استخدام عوامل بخلاف +، و-، وSUM.

22.2.1 الضرب باستخدام عامل ثابت

في كثير من الحالات، ينتج عن وظيفة تقريب think-cell نتائج جيدة عند تضمين عملية ضرب ثابتة، أي يكون أحد المعاملات على الأكثر مشتقًا من نتيجة دالة TCROUND أخرى. ادرس المثال التالي:

الحساب الدقيق للخلية C1 هو 3×1.3+1.4=5.3. يمكن الوصول إلى هذه النتيجة من خلال تقريب القيمة 1.4 إلى 2:

لكن، لا يمكن لوظيفة تقريب think-cell "الخطأ" سوى بالتقريب للأعلى أو للأدنى. لا يدعم البرنامج الانحراف بصورة أكبر عن القيم الأصلية. لذلك، بالنسبة لمجموعات معينة من قيم الإدخال، لا يمكن إيجاد حل مقرب بصورة متسقة. في هذه الحالة، تجري الدالة TCROUND تقييمًا لقيمة خطأ Excel #NUM!. يوضح المثال التالي مسألة غير قابلة للحل:

الحساب الدقيق للخلية C1 هو 6×1.3+1.4=9.2. نتيجة تقريب الخلايا A1 وB1 هي 6×1+2=8 أو 6×2+1=13. يتعذر تقريب النتيجة الفعلية 9.2 إلى 8 أو 13، وتبدو النتيجة المستمدة من وظيفة تقريب think-cell على النحو التالي:

ملاحظة: يتم تفسير دالة Excel AVERAGE بواسطة وظيفة تقريب think-cell بوصفها مجموعة من عمليات الجمع والضرب الثابتة. أيضًا، يكون التجميع الذي تظهر فيه القيمة المضافة نفسها أكثر من مرة مقابلاً من الناحية الحسابية لعملية ضرب ثابتة، ولا يوجد ضمان للوصول إلى حل.

22.2.2 الضرب العام والدوال الأخرى

طالما يتم استخدام الدوال TCROUND لجميع الخلايا ذات الصلة وترتبط النتائج الوسيطة فقط بـ +، و-، وSUM، وAVERAGE، يتم دمج القيم المضافة والإجماليات (الوسيطة) أيضًا في مسألة تقريب واحدة. في هذه الحالات، سوف تجد وظيفة تقريب think-cell حلاً يوفر اتساقًا على مستوى جميع الخلايا المعنية، في حالة وجود هذا الحل.

نظرًا لأن TCROUND هي دالة Excel عادية، فإنه يمكن جمعها مع دوال وعوامل عشوائية. ولكن عند استخدام دوال أخرى بخلاف المذكورة سابقًا لربط نتائج من عبارات TCROUND، لا يمكن لوظيفة تقريب think-cell دمج المكونات في مسألة واحدة مترابطة داخليًا. بدلاً من ذلك، سيتم التعامل مع مكونات الصيغة بوصفها مسائل منفصلة ليتم حلها بشكل مستقل. ستُستخدم النتائج عندئذٍ كإدخال للصيغ الأخرى.

في كثير من الحالات، سيظل الناتج المستمد من وظيفة تقريب think-cell معقولاً. ولكن، هناك حالات، يؤدي فيها استخدام عوامل غير +، و-، وSUM، وAVERAGE إلى نتائج مقربة بعيدة عن نتائج الحسابات غير المقربة. ادرس المثال التالي:

في هذه الحالة، سيكون الحساب الدقيق للخلية C1 هو 8.7×1.7=14.79. نظرًا لارتباط الخلية A1 والخلية B1 بالضرب، لا يمكن لوظيفة تقريب think-cell دمج الصيغ من هذه الخلايا في مسألة مشتركة. بدلاً من ذلك، بعد اكتشاف الخلية A1 باعتبارها إدخالاً صالحًا، يتم تقييم الخلية B1 بشكل مستقل، ويستخدم الناتج كثابت في المسألة المتبقية. نظرًا لعدم وجود قيود أخرى، يتم تقريب القيمة 1.7 من الخلية B1 إلى أقرب عدد صحيح، وهو 2.

في هذه المرحلة، يكون الحساب "الدقيق" للخلية C1 هو 8.7×2=17.4. هذه هي المسألة التي تحاول وظيفة تقريب think-cell حلها الآن. يوجد حل متسق يتطلب تقريب 17.4 للأعلى أي إلى 18. تبدو النتيجة كما يلي:

لاحظ أن القيمة المُقربة في الخلية C1، التي تبلغ 18، تختلف إلى حد كبير عن القيمة الأصلية 14.79.

22.3 استكشاف أخطاء صيغ TCROUND وإصلاحها

يوجد نوعان من نتائج الخطأ المُحتملة قد تواجهها عند استخدام وظيفة تقريب think-cell: #VALUE! و#NUM!.

22.3.1 #VALUE!

يشير الخطأ #VALUE! إلى مشكلات بنائية مثل الصيغ المكتوبة على نحو خاطئ أو المعلمات الخاطئة. كما يجب الانتباه لاستخدام محددات صحيحة: على سبيل المثال، عند استخدام Excel العالمي، تبدو الصيغة كما يلي: =TCROUND(1.7, 0)، في إصدار Excel الألماني المحلي، يجب كتابتها كما يلي =TCROUND(1,7; 0).

هناك خطأ آخر بخصوص وظيفة تقريب think-cell يتمثل في تحديد موضع استدعاء الدالة TCROUND: يتعذر عليك استخدام دالة TCROUND داخل صيغة أخرى. يرجى التأكد من أن TCROUND تمثل الدالة الأبعد لصيغة الخلية. (راجع تعيين موضع دوال TCROUND)

22.3.2 #NUM!

ينتج الخطأ #NUM! من المسائل العددية. عندما يكون ناتج دالة TCROUND هو #NUM!، يعني هذا أن المسألة غير قابلة للحل حسابيًا كما أوضحت مجموعة الصيغ المتاحة. (راجع قيود وظيفة التقريب في think-cell)

طالما أن الصيغ المحاطة بالدالة TCROUND تحتوي على +، و-، وSUM فقط، وتشترك جميع عبارات TCROUND في دقة واحدة (معلمة ثانية)، يتم ضمان وجود حل وسيتم العثور عليه بواسطة وظيفة تقريب think-cell. ولكن، في الحالات التالية لا يوجد ضمان بوجود حل مُقرب بصورة متسقة:

• تتضمن الصيغ عمليات أخرى مثل الضرب أو دوال عددية. أيضًا، تكافئ التجميعات - التي تتكون عندما تظهر القيم المضافة نفسها أكثر من مرة - عملية الضرب حسابيًا.
• يمكنك استخدام قيم دقة مختلفة في المعلمة الثانية من الدالة TCROUND.
• يمكنك استخدام الدوال المحددة TCROUNDDOWN، وTCROUNDUP، وTCROUNDNEAR بشكل متكرر.

يمكنك محاولة إعادة صياغة المسألة للحصول على حل متسق. جرب ما يلي:

• استخدم قيمة دقة أفضل لبعض عبارات TCROUND أو كلها.
• لا تستخدم TCROUND مع دوال الضرب أو الدوال العددية بخلاف +، و-، وSUM.
• استخدم قيمة الدقة (المعلمة الثانية) نفسها مع جميع عبارات TCROUND.
• استخدم TCROUND بدلاً من الدوال الأكثر تحديدًا TCROUNDDOWN، وTCROUNDUP، وTCROUNDNEAR كلما أمكن.

ألست مستخدمًا بعد؟