فرز حسب الصيغة

إذا كنت بحاجة إلى فرز القائمة ، فهناك العديد من الطرق في خدمتك ، وأسهلها هي أزرار الفرز في علامة التبويب أو في القائمة البيانات (بيانات - فرز). ومع ذلك ، هناك حالات يلزم فيها فرز القائمة تلقائيًا ، أي الصيغ. قد يكون هذا مطلوبًا ، على سبيل المثال ، عند إنشاء البيانات لقائمة منسدلة ، عند حساب البيانات للمخططات ، وما إلى ذلك. كيف يمكن فرز قائمة باستخدام صيغة سريعة؟

الطريقة 1. البيانات الرقمية

إذا كانت القائمة تحتوي على معلومات رقمية فقط ، فيمكن فرزها بسهولة باستخدام الوظائف الأقل (صغير) и خط (صف):

المسمى الوظيفي الأقل (صغير) يسحب من المصفوفة (العمود A) أصغر عنصر على التوالي. أولئك. صغير (أ: أ ؛ 1) هو أصغر رقم في العمود ، صغير (أ: أ ؛ 2) هو ثاني أصغر رقم ، وهكذا.

المسمى الوظيفي خط (صف) إرجاع رقم الصف للخلية المحددة ، أي ROW (A1) = 1 ، ROW (A2) = 2 إلخ. في هذه الحالة ، يتم استخدامه ببساطة كمولد لتسلسل من الأرقام n = 1,2,3،1,2,3،XNUMX ... قائمتنا المصنفة. وبنفس النجاح ، كان من الممكن عمل عمود إضافي ، وملئه يدويًا بالتسلسل الرقمي XNUMX ، XNUMX ، XNUMX ... والإشارة إليه بدلاً من الدالة ROW.

الطريقة الثانية. قائمة النص والصيغ العادية

إذا كانت القائمة لا تحتوي على أرقام ، بل نص ، فلن تعمل وظيفة SMALL بعد الآن ، لذلك عليك أن تسلك مسارًا مختلفًا أطول قليلاً.

أولاً ، دعنا نضيف عمود خدمة مع صيغة حيث سيتم حساب الرقم التسلسلي لكل اسم في القائمة التي تم فرزها في المستقبل باستخدام الوظيفة COUNTIF (كونتيف):

في النسخة الإنجليزية سيكون:

= COUNTIF (A: A، »<" & A1) + COUNTIF ($ A $ 1: A1، "=" & A1)

المصطلح الأول هو دالة لحساب عدد الخلايا الأقل من العدد الحالي. والثاني عبارة عن شبكة أمان في حالة ورود أي اسم أكثر من مرة. ثم لن يكون لديهم نفس الأعداد ، ولكن أعداد متزايدة بالتتابع.

الآن يجب ترتيب الأرقام المستلمة بالتسلسل بترتيب تصاعدي. لهذا يمكنك استخدام الوظيفة الأقل (صغير) من الطريق الأول:

حسنًا ، أخيرًا ، يبقى فقط سحب الأسماء من القائمة بأرقامها. للقيام بذلك ، يمكنك استخدام الصيغة التالية:

المسمى الوظيفي أكثر انكشافاً (مباراة) يبحث في العمود B عن الرقم التسلسلي المطلوب (1 ، 2 ، 3 ، وما إلى ذلك) ، وفي الواقع ، يُرجع رقم السطر الذي يوجد به هذا الرقم. دور INDEX (فهرس) يسحب الاسم الموجود في رقم السطر هذا من العمود A.

الطريقة 3: صيغة الصفيف

هذه الطريقة ، في الواقع ، هي نفس خوارزمية التنسيب كما في الطريقة 2 ، ولكن يتم تنفيذها بواسطة صيغة صفيف. لتبسيط الصيغة ، تم تسمية نطاق الخلايا C1: C10 بالاسم قائمة (حدد الخلايا ، اضغط CTRL + F3 والزر إنشاء):

في الخلية E1 ، انسخ الصيغة الخاصة بنا:

= INDEX (List؛ MATCH (SMALL (COUNTIF (List؛ "<" & List)؛ ROW (1: 1))؛ COUNTIF (List؛ "<" & List)؛ 0))

أو في النسخة الإنجليزية:

= INDEX (List، MATCH (SMALL (COUNTIF (List، «<" & List)، ROW (1: 1))، COUNTIF (List، "<" & List)، 0))

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

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

أولاً ، يجب تعيين نطاق القائمة ديناميكيًا. للقيام بذلك ، عند الإنشاء ، لا تحتاج إلى تحديد نطاق ثابت C3: C10 ، ولكن صيغة خاصة تشير إلى جميع القيم المتاحة ، بغض النظر عن عددها. انقر ALT + F3 أو افتح علامة التبويب الصيغ - مدير الاسم (الصيغ - مدير الاسم)، إنشاء اسم جديد وفي هذا المجال لينك (مرجع) أدخل الصيغة التالية (أفترض أن نطاق البيانات المراد فرزها يبدأ من الخلية C1):

=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)

= OFFSET (C1,0,0،1،1000، SCHÖTZ (C1: CXNUMX)، XNUMX)

ثانيًا ، ستحتاج صيغة الصفيف أعلاه إلى التمدد إلى أسفل بهامش - مع توقع إدخال بيانات إضافية في المستقبل. في هذه الحالة ، ستبدأ صيغة الصفيف بإعطاء الخطأ # NUMBER في الخلايا التي لم يتم تعبئتها بعد. لاعتراضه ، يمكنك استخدام الوظيفة خطأ مرجّح، والتي يجب إضافتها "حول" صيغة الصفيف الخاصة بنا:

=IFERROR (فهرس (قائمة ؛ تطابق (صغير (COUNTIF (قائمة ؛ "<" & قائمة) ؛ ROW (1: 1)) ؛ COUNTIF (قائمة ؛ "<" & قائمة) ؛ 0))؛ »»)

= IFERROR (NDEX (List، MATCH (SMALL (COUNTIF (List، «<" & List))، ROW (1: 1))، COUNTIF (List، "<" & List)، 0))؛ "")

يمسك الخطأ #NUMBER وينتج فراغ (علامات اقتباس فارغة) بدلاً من ذلك.

:

• نطاق الفرز حسب اللون
• ما هي صيغ الصفيف ولماذا تحتاج إليها
• الفرز SORT والمصفوفات الديناميكية في Office 365 الجديد