الترتيب باستخدام RANK وPERCENTILE

غالبًا لا تريدون معرفة القيمة فحسب، بل تريدون معرفة موضعها داخل المجموعة. هل مندوب المبيعات هذا في المرتبة الأولى أم الثانية عشرة؟ وهل تقع هذه الدرجة ضمن أعلى 10%؟

تجيب عائلتان من الدوال عن ذلك: تخبركم RANK بالترتيب العددي، بينما تخبركم PERCENTILE بالقيمة عند حد فاصل معيّن. وتحولان معًا الأرقام الخام إلى ترتيبات ومعايير مرجعية.

تُعطي RANK.EQ موضع قيمة داخل نطاق. وتمررون إليها القيمة والنطاق وراية الترتيب.

• الترتيب 0 (أو المحذوف) يرتب من الأكبر إلى الأصغر (تنازليًا).
• الترتيب 1 يرتب من الأصغر إلى الأكبر (تصاعديًا).

تحصل أعلى قيمة للمبيعات هنا على المرتبة 1.

لاحظوا علامتي الدولار: $B$2:$B$20. يجب أن يظل النطاق ثابتًا عند نسخ الصيغة إلى الأسفل، بينما يتحرك مرجع القيمة B2 إلى B3 ثم B4 وهكذا.

إذا نسيتم علامتي الدولار، فسيتحرك النطاق مع كل صف، مما ينتج ترتيبات غير منطقية. وتُعد المراجع المطلقة للنطاق ضرورية هنا.

تمنح RANK.EQ القيم المتساوية المرتبة نفسها، ثم تتجاوز المرتبة التالية. فإذا تعادلت قيمتان على المرتبة الثانية، فستظهر لهما 2، وتحصل القيمة التالية على 4 (من دون مرتبة ثالثة).

أما إذا أردتم متوسط المرتبة للقيم المتساوية، فاستخدموا RANK.AVG. فستظهر القيمة 2.5 لكل قيمتين متعادلتين في المرتبتين الثانية والثالثة.

لفرض مراتب فريدة حتى عند وجود تعادل، أضيفوا عامل كسر التعادل COUNTIF. فهو يحسب عدد القيم المساوية التي تظهر فوق الصف الحالي، ثم يضيف ذلك الإزاحة.

وهذا يضمن تسلسلًا واضحًا من 1 و2 و3 و4 من دون تكرارات، وهو مفيد في لوحات المتصدرين.

تجيب PERCENTILE.INC عن السؤال: ما القيمة الموجودة عند نقطة مئوية معينة؟ مرروا إليها النطاق وكسرًا بين 0 و1.

تُرجع الصيغة أدناه المئين التسعين، أي القيمة التي تقع 90% من البيانات دونها. وهي مثالية لتحديد حدود مثل "أفضل 10% من أصحاب الأداء".

تقسم الربيعات البيانات إلى أربعة أجزاء. وتُعد الدالة QUARTILE.INC اختصارًا: فالربيع الأول يساوي المئين الخامس والعشرين، والثاني يساوي المئين الخمسين (الوسيط)، والثالث يساوي المئين الخامس والسبعين.

تُرجع الصيغة أدناه الربيع الثالث، وهو مطابق للصيغة PERCENTILE.INC(A2:A101,0.75).

هناك نوعان: PERCENTILE.INC (الشامل، ويقبل 0 و1) وPERCENTILE.EXC (الاستثنائي، ويقبل القيم الواقعة بين 0 و1 فقط، وليس القيمتين الطرفيتين).

في معظم التقارير اليومية، يكون .INC هو الخيار القياسي. استخدموا .EXC عند اتباع الأعراف الإحصائية التي تستبعد نقاط الحدود.

تُعطي RANK موضعًا، بينما تُعطي PERCENTILE قيمة عند حد فاصل. أما PERCENTRANK.INC فتفعل عكس المئين: عند إعطائها قيمة، ما المئين الذي تنتمي إليه؟

إذا حصل طالب على 88 وأرجعت PERCENTRANK القيمة 0.92، فهذا يعني أن الطالب تفوق على 92% من المجموعة. اضربوا القيمة في 100 لعرضها كنسبة مئوية.

تعمل الدالتان RANK وPERCENTILE على الأرقام فقط. ويتم تجاهل التسميات النصية والخلايا الفارغة داخل النطاق، لذلك لن يؤثر وجود عنوان غير مقصود في الترتيب.

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

لديكم 20 مندوب مبيعات، ومبيعاتهم موجودة في العمود B. وتريدون معرفة ترتيب كل مندوب وما إذا كان ضمن الربع الأعلى.

• الترتيب: =RANK.EQ(B2,$B$2:$B$21,0)
• تقارن علامة الربع الأعلى مبيعات المندوب بالنسبة المئوية الخامسة والسبعين.

تعرض الصيغة أدناه القيمة Top أو Standard لكل مندوب استنادًا إلى الحد الفاصل.

اختبروا مدى فهمكم لدوال الترتيب.

تعلمتم كيفية تحديد موضع القيم داخل مجموعة:

• تعرض RANK.EQ الموضع الترتيبي؛ إذ يعني الترتيب 0 البدء بالأكبر، ويعني 1 البدء بالأصغر. ثبّتوا النطاق باستخدام $.
• تحسب RANK.AVG متوسط الرتب المتساوية؛ وأضيفوا COUNTIF للحصول على رتب فريدة.
• تعرض PERCENTILE.INC القيمة عند حد فاصل معين؛ بينما تمثل QUARTILE.INC اختصارًا للقيم 25/50/75.
• تعكس PERCENTRANK.INC العملية: فهي تعرض النسبة المئوية لقيمة محددة.