كيفية استخدام وظائف choosecols و chooserows في Excel لاستخراج البيانات

تعتبر وظائف Microsoft Excel من choosecols و chooserows هي مغير اللعبة إذا كنت ترغب في استخراج أعمدة أو صفوف محددة بسرعة من بياناتك دون تعشيش عدة وظائف في صيغتك. والأكثر من ذلك ، إنها وظائف ديناميكية ، مما يعني أنها تتكيف مع التغييرات في مجموعة البيانات الخاصة بك.

جملة choocoCols و chooserows

تشبه وظائف Excel’s ChooseCols و Chooserows التوائم: DNAs متشابهة جدًا ، لكنها مفصولة باختلافات دقيقة. يمكن قول الشيء نفسه عن بناء الجملة.

إليك بناء الجملة لـ ChooseCols:

=CHOOSECOLS(a,b,c,…)

وهنا بناء الجملة لـ chooserows:

=CHOOSEROWS(a,b,c,…)

في كلتا الحالتين ،

• أ (مطلوب) هو الصفيف الأصلي الذي يحتوي على الأعمدة (choosecols) أو الصفوف (chooserows) التي تريد استخراجها ،
• ب (مطلوب) هو رقم فهرس العمود الأول (choosecols) أو الصف (chooserows) ليتم استخلاصه ،
• ج (اختياري) يمثل أرقام الفهرس لأي أعمدة إضافية (chooseCols) أو الصفوف (choloSerows) المراد استخلاصها ، يجب فصل كل منها عن طريق الفواصل.

قد تفكر في أن هذه الوظائف تبدو مشابهة جدًا لوظيفة Take. ومع ذلك ، حيث تتيح لك خذ استخراج الأول أو الأخير x تتيح لك الأعمدة أو الصفوف ، أو عمودًا أو صفًا واحدًا من مكان آخر في الجدول المسماة ، و choosecols و chooserows استخراج أي عدد من الأعمدة بأي أمر من أي مكان في بياناتك.

متعلق ب

كيفية استخدام Take in Excel لاستخراج البيانات

استخراج بيانات محددة من جدول Excel الخاص بك.

مثال 1: استخراج العمود الأول أو الأخير من جدول

لقد فقدت بالفعل عدد المرات التي استخدمت فيها choosecols و chooserows لاستخراج العمود الأول أو الأخير من جدول. هذا مفيد بشكل خاص إذا كان العمود أو الصف الأول رأسًا ، ويحتوي العمود أو الصف الأخير على مجاميع.

تخيل أنك مسؤول عن دوري رياضي محلي ، وتنتج تقريرًا يلخص النقاط التي سجلها خمس فرق في خمس مباريات.

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

=CHOOSECOLS(T_Games(#All),1,-1)

أين

• choosecols هل الوظيفة التي تريد استخدامها ، نظرًا لأنك تستخرج البيانات من الفريق والأعمدة الكلية ،
• t_games هو اسم الجدول حيث يتم تخزين الصفيف ، و (#الجميع) يخبر Excel أنك تريد تضمين الرأس والصفوف الكلية في النتيجة ،
• 1 هو العمود الأول (في هذه الحالة ، العمود المسمى “فريق”) ، و
• -1 هو العمود الأخير (في هذه الحالة ، العمود المسمى “Total”)

واضغط على إدخال.

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

الآن ، حتى إذا تمت إضافة المزيد من البيانات ، فإن النتيجة – بما في ذلك الصف الإجمالي – تتقدم وفقًا لذلك.

سيعرض التقرير التالي الذي تريد إنشاءه عدد النقاط المسجلة في كل لعبة (الصفوف 1 و 8).

لذلك ، في خلية فارغة ، اكتب:

=CHOOSEROWS(T_Games(#All),1,-1)

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

مثال 2: استخراج الأعمدة من أكثر من نطاق واحد

الآن ، دعنا نقول أن لديك جدول البيانات هذا ، وهدفك هو إنتاج قائمة تحتوي على إجمالي الدرجات لكل فريق عبر بطولات الدوري 1 (الأخضر) ، 2 (أزرق) ، و 3 (رمادي).

هذه المرة ، لا تريد أن تستخرج Excel رؤوس الأعمدة وأجسام الصفوف ، نظرًا لأنك ستستمر في وظيفة VStack داخل صيغة Choosecols لتكديس الجداول الثلاثة مباشرة فوق بعضها البعض. لذلك ، في الخلايا I1 و J1 ، قم بإنشاء رؤوس العمود يدويًا.

متعلق ب

كيفية الجمع بين المصفوفات وإعادة تشكيلها وتغيير حجمها في Excel

تحكم في المصفوفات في جدول البيانات الخاص بك وترتيبها كما يحلو لك.

ثم ، في الخلية i2 ، اكتب:

=CHOOSECOLS(VSTACK(League_1,League_2,League_3),1,-1)

أين

• choosecols هل الوظيفة التي ستستخدمها لاستخراج الأعمدة ،
• vstack يتيح لك الجمع بين النتائج رأسياً ،
• League_1 ، League_2 ، League3 هل أسماء الجداول التي تمثل المصفوفات ، وغياب (#ALL) بعد أن تخبر أسماء الجدول Excel بعدم تضمين الرأس والصفوف الكلية في النتيجة ،
• 1 يطلب من Excel استخراج العمود الأول (“Team”) من كل صفيف ، و
• -1 يخبر Excel باستخراج العمود الأخير من كل صفيف (“Total”)

واضغط على إدخال.

في هذه المرحلة ، يمكنك الذهاب خطوة واحدة إلى الأمام وفرز النتيجة في ترتيب تنازلي عن طريق الكتابة:

=SORTBY(I2#,J2:J16,-1)

في الخلية L2 ، والضغط على إدخال.

متعلق ب

تحتاج إلى معرفة ما تفعله علامة التجزئة في صيغ Excel

تحطيم التجزئة في فلاش!

مثال 3: استخدام choosecols مع التحقق من صحة البيانات والتنسيق الشرطي

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

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

أولاً ، اكتب رقم لعبة في الخلية B9 ، بحيث يكون لديك شيء للعمل معه عند إنشاء صيغة Choosecols الخاصة بك. ثم ، في الخلية A11 ، اكتب:

=CHOOSECOLS(T_Scores((#Data),(#Totals)),1,B9+1)

أين

• choosecols هي الصيغة المستخدمة لاستخراج الأعمدة ،
• T_SCORES هو اسم الجدول ، و ((#Data) ، (#المجاميع)) يخبر Excel بتضمين البيانات والمجموعات في النتيجة ، ولكن ليس صف الرأس ،
• 1 يمثل العمود الأول (“المجاميع”) ، و
• B9+1 يخبر Excel أن وسيطة الفهرس الثانية ممثلة بالقيمة في الخلية B9 ، بالإضافة إلى واحدة. سبب حاجتك لتضمين +1 هنا لأن أرقام اللعبة تبدأ في العمود الثاني من الجدول. نتيجة لذلك ، الكتابة 3 في الخلية B9 يستخلص البيانات من العمود الرابع من الجدول ، وهو اللعبة 3.

على الرغم من أن كتابة رقم اللعبة في الخلية B9 تعمل بشكل جيد ، إذا قمت أنت أو شخص آخر بإدخال رقم غير صالح بطريق الخطأ ، فإن وظيفة ChooseCols ستعيد خطأ #value.

هذه حالة جيدة لاستخدام التحقق من صحة البيانات في الخلية B9 لإنشاء قائمة منسدلة لأرقام اللعبة المتاحة.

متعلق ب

كيفية إضافة قائمة منسدلة إلى خلية في Excel

يتفوق على الكتابة في نفس الخيارات 200 مرة مختلفة يدويًا.

تتمثل طريقتي المفضلة للقيام بذلك في صف رأس في جدول Excel أولاً في تحديد جميع الخلايا ذات الصلة ، بالإضافة إلى عدد إضافي للسماح بالنمو ، وتسمية النطاق في مربع الاسم في الزاوية العلوية اليسرى من نافذة Excel. لاحظ كيف لم أحدد رأس “فريق” العمود ، لأنني لا أريد أن يظهر هذا في القائمة المنسدلة في اللعبة في الخلية B9.

متعلق ب

أنا دائما اسم النطاقات في Excel ، ويجب عليك أيضا

ترتيب مصنف Excel الخاص بك.

بعد ذلك ، حدد الخلية B9 ، وانقر فوق النصف العلوي من زر “التحقق من صحة البيانات” في علامة تبويب البيانات على الشريط.

الآن ، في مربع حوار التحقق من صحة البيانات ، حدد “قائمة” في حقل السماح ، تأكد من فحص “تجاهل فارغ” ، اكتب رمزًا متساويًا (=) تليها الاسم الذي أعطيته للتو إلى نطاق رأس العمود ، وانقر فوق “موافق”.

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

أخيرًا ، لتصور بياناتك بشكل أكبر ، حدد الخلايا B11 إلى B15 ، وفي علامة التبويب المنزل على الشريط ، انقر فوق “التنسيق الشرطي ،” تحوم فوق “أشرطة البيانات” ، واختر لون ملء صلب.

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

تنتج Choosecols و chooserows صفائف ديناميكية ، مما يعني أن النتيجة تنطلق من الخلية التي دخلت فيها الصيغة. نتيجة لذلك ، قبل دخولك الصيغة الخاصة بك ، تأكد من وجود مساحة كافية في جدول البيانات الخاص بك – وإلا ، ستحصل على #spill اللعين! خطأ. أيضًا ، نظرًا لأن المصفوفات الديناميكية وطاولات Excel المنسقة لا تختلط ، إذا كنت ترغب في تنسيق النتيجة ، فسيتعين عليك القيام بذلك يدويًا أو باستخدام التنسيق الشرطي.