الأعلى والأدنى باستخدام LARGE وSMALL

تعرض MAX أكبر قيمة منفردة، بينما تعرض MIN أصغر قيمة منفردة. لكن ماذا لو أردتم الحصول على القيمة الثانية من حيث الارتفاع أو الثالثة من حيث الانخفاض؟

هذا تحديدًا ما تفعله الدالتان LARGE وSMALL. إذ تتيحان استخراج القيمة الأكبر أو الأصغر رقم n من نطاق، وهو الأساس لإنشاء تقارير أعلى N وأدنى N.

تعرض LARGE(range, k) القيمة الأكبر رقم k. وتحدد الوسيطة الثانية k الموضع: إذ تمثل 1 القيمة الأكبر، وتمثل 2 ثاني أكبر قيمة، وهكذا.

لذلك تساوي LARGE(A2:A20,1) القيمة MAX(A2:A20)، بينما تعرض LARGE(A2:A20,2) صاحب المركز الثاني.

تحاكي SMALL(range, k) الدالة LARGE بدءًا من الطرف الأصغر. إذ تساوي SMALL(A2:A20,1) القيمة MIN(A2:A20)، بينما تعرض SMALL(A2:A20,3) ثالث أصغر قيمة.

استخدموا SMALL لإظهار أصحاب الأداء الأضعف، أو الخيارات الأرخص، أو أقدم التواريخ في مجموعة بيانات.

لإدراج أفضل ثلاث قيم، ضعوا ثلاث صيغ LARGE متتالية باستخدام k = 1 و2 و3. والطريقة الأنظف هي الإشارة إلى رقم مساعد حتى تتمكنوا من تعبئة الصيغة إلى أسفل.

إذا كانت الخلية D2 تحتوي على 1، وD3 على 2، وD4 على 3، فإن تعبئة هذه الصيغة الوحيدة إلى أسفل تُنتج قائمة أفضل ثلاث قيم كاملة.

يمكنكم إنشاء k تلقائيًا باستخدام الدالة ROW، فلا تحتاجون إلى عمود مساعد. إذ تُنتج ROW()-1 القيمة 1 في الصف الأول، و2 في الصف التالي، وهكذا.

وعند تعبئتها إلى أسفل، تنشئ قائمة مرتبة دون أي إعداد إضافي. اضبطوا قيمة الإزاحة لتتوافق مع موضع بدء قائمتكم.

في الإصدارات الحديثة من Excel وGoogle Sheets، يمكنكم إظهار أفضل 5 قيم دفعة واحدة في صيغة واحدة، وذلك بتمرير SEQUENCE باعتبارها الوسيطة k. إذ تنتج SEQUENCE(5) القيم 1 و2 و3 و4 و5.

بعد ذلك تعرض LARGE خمس قيم دفعة واحدة، وتُظهرها تلقائيًا في صفوف متتالية. صيغة واحدة وقائمة مرتبة كاملة.

من الأسئلة الشائعة في الأعمال: ما المساهمة التي يقدمها أفضل 3 عملاء لديكم؟ ضعوا LARGE داخل SUM مع SEQUENCE (أو ثابت صفيف) لجمع أفضل القيم.

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

إذا كانت k أقل من 1 أو أكبر من عدد الأرقام في النطاق، تُرجع LARGE وSMALL الخطأ #NUM!.

فعلى سبيل المثال، ستفشل محاولة طلب القيمة الثلاثين من حيث الكِبَر في قائمة لا تحتوي إلا على 19 رقمًا. ولتجنب ذلك في التقارير، ضعوا الاستدعاء داخل IFERROR.

تكون معرفة من حصل على القيمة أكثر فائدة من عرض القيمة وحدها. ادمجوا LARGE مع INDEX وMATCH لجلب الاسم المرتبط بأعلى قيمة.

تبحث هذه الصيغة عن أكبر رقم مبيعات، وتحدد صفه، ثم تُرجع الاسم المطابق من العمود A، بحيث يعرض تقريركم العبارة "أفضل بائع: Maria."

مثل الدوال الإحصائية الأخرى، تحسب LARGE وSMALL الأرقام فقط. ويتم تخطي النصوص والخلايا الفارغة في النطاق، لذلك لا تؤثر في تحديد القيمة الأكبر رقم n.

وهذا يعني أنه يمكنكم توجيههما بأمان إلى عمود يتضمن صف عناوين. لكن انتبهوا إلى الأرقام المخزنة بالخطأ كنصوص، إذ يتم تجاهلها وقد تغيّر القيمة التي تُرجعها LARGE أو SMALL.

لديكم مبيعات شهرية في B2:B20. وتريد الإدارة معرفة أفضل وأسوأ ثلاثة أشهر.

• أفضل ثلاثة: =LARGE($B$2:$B$20,{1,2,3})
• أسوأ ثلاثة: =SMALL($B$2:$B$20,{1,2,3})

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

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

أصبح بإمكانكم الآن تجاوز MAX وMIN:

• تعرض LARGE(range,k) القيمة الأكبر رقم k، بينما تعرض SMALL(range,k) القيمة الأصغر رقم k.
• تطابق k = 1 الدالتين MAX وMIN؛ ارفعوا قيمة k للوصول إلى أصحاب المراكز التالية.
• مرّروا SEQUENCE أو صفيفًا مثل {1,2,3} لإنشاء قائمة أفضل N قيم أو جمعها في صيغة واحدة.
• أقرنوها بـ INDEX-MATCH لتسمية القيمة، وضعوها داخل IFERROR لمعالجة قيم k الخارجة عن النطاق.