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

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

جملة choocoCols و chooserows

بمجرد أن تعمل على كيفية استخدام واحدة من هاتين وظيفتين في أوراق Google، سيكون لديك فهم جيد لكيفية عمل الآخر.

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

=CHOOSECOLS(a,b,c)

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

=CHOOSEROWS(a,b,c)

إليك ما تمثله كل حجة:

إذا كانت أي من أرقام الفهرس صفرًا أو تتجاوز عدد الأعمدة أو الصفوف في الصفيف ، فستقوم Google Sheets بإرجاع #Value! خطأ.

متعلق ب

دليل المبتدئين لأوراق Google

تعرف على كيفية القيام بكل شيء من مشاركة الأوراق إلى الأتمتة في المهام مع وحدات الماكرو.

مثال 1: استخراج الأعمدة أو الصفوف الأولى والأخيرة

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

استخراج الأعمدة الأولى والأخيرة

في هذا المثال ، دعنا نتخيل أنك مسؤول أعمال ، وتريد إنشاء تقرير يلخص عدد المنتجات التي يباع من قبل كل متجر في أول ربعين من العام.

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

=CHOOSECOLS(Sales(#ALL),1,-1)

أين

• choosecols هي الوظيفة التي تحتاج إلى استخدامها لاستخراج البيانات من المتجر وأعمدة TOT ،
• مبيعات هو اسم الجدول الذي يحتوي على الصفيف ، و (#الجميع) يخبر Google Sheets أنك تريد أن يتم تضمين صف الرأس في النتيجة ،
• 1 يشير إلى أنك تريد استخراج العمود الأول (في هذه الحالة ، عمود المتجر) ، و
• -1 يشير إلى أنك تريد استخراج العمود الأخير (في هذه الحالة ، عمود TOT).

إليك النتيجة عند الضغط على Enter:

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

أيضًا ، نظرًا لأن بيانات المصدر موجودة في جدول منظم ، إذا تغيرت البيانات ، فإن نتيجة وظيفة choosecols. هذا صحيح أيضًا إذا تمت إضافة المزيد من الأعمدة أو الصفوف إلى مجموعة البيانات.

الآن ، يمكنك تكرار بيانات الملخص على جدول بيانات Google Sheets آخر (مثل علامة تبويب لوحة القيادة) أو أ مستندات جوجل وثيقة.

استخراج الصفوف الأولى والأخيرة

على نفس المنوال ، يكون سحب الصفوف الأولى والأخيرة مفيدة لتوليد ملخصات أفقية لبياناتك.

باستخدام نفس المثال كما كان من قبل ، بما في ذلك المتجر المضافة K ، دعنا نقول أنك تريد الآن تلخيص العدد الإجمالي للمنتجات المباعة كل شهر.

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

=chooserows(Sales(#ALL),1,-1)

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

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

في المثال 1 ، استخرجنا أعمدة وصفوف محددة من النطاقات الفردية. ومع ذلك ، يمكنك الجمع بين choosecols مع وظيفة VStack لدمج عمليات الاستخراج من أكثر من نطاق في نفس الوقت.

هنا ، تخطط لإنشاء قائمة من إجمالي الدرجات لكل فريق في الدوري 1 (الأحمر) والدوري 2 (الأخضر).

أول شيء يجب ملاحظته هو أنك لا ترغب في استخراج رؤوس الأعمدة ، نظرًا لأنك ترغب في تكديس البيانات من كل جدول ، واحدة مباشرة فوق الأخرى. إذا قمت بتضمين رؤوس الأعمدة ، فستظهر في الجزء العلوي من مجموعات البيانات في النتيجة ، وبالتالي إزالة الإحساس بأنك تبحث في مجموعة بيانات مجتمعة.

نتيجة لذلك ، في الخلايا J1 و K1 ، اكتب رؤوس العمود يدويًا.

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

=CHOOSECOLS(VSTACK(League1,League2),1,-1)

أين

• choosecols يخبر أوراق Google أنك تريد استخراج أعمدة معينة ،
• vstack يجمع بين النتيجة رأسياً (وبالتالي “V” في اسم الوظيفة) ،
• League1 ، League2 هل المصفوفات التي تحتوي على الأعمدة ، و (#) لا يتم تضمينها في الصيغة تخبر أوراق Google بعدم تضمين صفوف الرأس ،
• 1 يستخلص العمود الأول (فريق) من كل صفيف ، و
• -1 يستخلص العمود الأخير (TOT) من كل صفيف.

عند الضغط على Enter ، إليك النتيجة التي تحصل عليها:

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

متعلق ب

9 وظائف أوراق Google الأساسية التي يجب أن تعرفها

تعرف على الوظائف الأساسية التي تحتاجها لجدول البيانات الخاص بك.

للقيام بذلك ، انقر نقرًا مزدوجًا فوق الخلية حيث قمت سابقًا بكتابة مجموعة ChooSecols-Vstack ، وأضف وسيطات الفرز التالية:

=SORT(CHOOSECOLS(VSTACK(League1,League2),1,-1),2,FALSE)

أين

• نوع هي الوظيفة التي ترسل نطاقًا استنادًا إلى عمود محدد ،
• 2 يخبر Google Sheets بفرز النطاق حسب العمود الثاني ، و
• خطأ شنيع يشير إلى أنه ينبغي فرز البيانات بترتيب تنازلي.

بشكل مثير للإعجاب ، إذا كانت أي قيم في مجموعة البيانات الأصلية تتغير ، فإن جدول الملخص يضبط وفقًا لذلك. هنا ، فإن تغيير فريق Team H 6 من 51 إلى 89 يروج لهم من المركز التاسع إلى المركز الرابع.

مثال 3: جعل وظائف choosecols و chooserows ديناميكية

حتى الآن ، لقد أوضحت لك كيفية استخدام choosecols و chooserows لاستخراج الأعمدة والصفوف الأولى والأخيرة من صفيف في أوراق Google. ومع ذلك ، في هذا المثال الأخير ، سأوضح لك كيفية استخراج الأعمدة أو الصفوف الأخرى ديناميكيًا عن طريق المرجع-بدلاً من استخدام أرقام الفهرس المرمزة-في الوسيطات.

يعرض جدول Google Sheets هذا عشرات الفرق على ست مباريات ، بالإضافة إلى المجموع المشترك لكل لعبة ، وهدفك هو إنتاج ملخص وفقًا لرقم اللعبة الذي تحدده في الخلية B9. أولاً ، اكتب رقم لعبة في هذه الخلية بحيث يكون لديك شيء للعمل معه عند إنشاء صيغة ChooSecols الخاصة بك.

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

=CHOOSECOLS(TeamScores,1,B9+1)

أين

• choosecols يخبر Google أوراق أنك ستستخرج أعمدة معينة ،
• Teamscores هو اسم الجدول الذي يحتوي على البيانات التي تريد استخراجها ،
• 1 هو رقم الفهرس لعمود الفريق ، و
• B9+1 يخبر Google Sheets أن وسيطة الفهرس الثانية يتم حسابها بإضافة 1 إلى القيمة في الخلية B9. سبب حاجتك لتضمين +1 هنا لأن أرقام اللعبة تبدأ في العمود الثاني من الصف 1 ، وليس الأول. نتيجة لذلك ، الكتابة 4 في الخلية B9 يستخلص البيانات من العمود الخامس من الجدول ، وهو اللعبة 4.

إليك ما تحصل عليه عند الضغط على Enter:

الآن ، اكتب رقم لعبة مختلف في الخلية B9 لمعرفة تحديث النتيجة وفقًا لذلك.

نصيحة للمحترفين: أضف قاعدة التحقق من البيانات

على الرغم من أن كتابة رقم اللعبة في الخلية B9 تعمل تمامًا كما هو متوقع في جعل وظيفة choosecols ديناميكية ، إذا تم إدخال رقم غير صالح ، فسوف تقابله فجأة مع قيمة #! خطأ. لذلك ، لمنع حدوث ذلك ، يمكنك استخدام التحقق من صحة البيانات للتحكم في معلمات إدخال الخلية.

متعلق ب

كيفية تقييد البيانات في أوراق Google مع التحقق من صحة البيانات

الحد من مدخلات البيانات في جدول بيانات Google Sheets.

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

ثم ، في جزء قواعد التحقق من صحة البيانات على الجانب الأيمن ، انقر فوق “إضافة القاعدة”.

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

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

تنتج وظائف choosecols و chooserows في أوراق Google صفائف ديناميكية ، مما يعني أن النتيجة تتسرب من الخلية التي أدخلت فيها الصيغة. نتيجة لذلك ، يجب عليك التأكد من وجود مساحة كافية في جدول البيانات الخاص بك قبل الدخول إلى صيغتك. خلاف ذلك ، سترى #REF! خطأ.