الأخبار التكنولوجية والاستعراضات والنصائح!

كيفية استخدام وظيفة QUERY في جداول بيانات Google

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

استخدام وظيفة QUERY

ليس من الصعب إتقان وظيفة QUERY إذا كنت قد تفاعلت مع قاعدة بيانات باستخدام SQL. يشبه تنسيق دالة QUERY النموذجية تنسيق SQL ويجلب قوة عمليات البحث في قاعدة البيانات إلى جداول بيانات Google.

تنسيق الصيغة التي تستخدم الدالة QUERY هو = QUERY (بيانات ، استعلام ، رؤوس). استبدل “البيانات” بنطاق الخلايا (على سبيل المثال “A2: D12” أو “A: D”) و “الاستعلام” باستعلام البحث.

تحدد وسيطة الرؤوس الاختيارية عدد صفوف الرأس المراد تضمينها في الجزء العلوي من نطاق البيانات. إذا كان لديك رأس يمتد على خليتين ، مثل “الأولى” في A1 و “الاسم” في A2 ، فسيخبر ذلك QUERY باستخدام محتويات أول صفين كرأس مدمج.

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

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

للقيام بذلك باستخدام البيانات الموضحة أعلاه ، يمكنك كتابة = QUERY (“قائمة الموظفين”! A2: E12 ، “SELECT A ، B ، C ، E حيث E =” لا “”). هذا يطلب بيانات من النطاق A2 إلى E12 في ورقة “قائمة الموظفين”.

مثل استعلام SQL النموذجي ، تحدد وظيفة QUERY الأعمدة المراد عرضها (SELECT) وتحدد معلمات البحث (WHERE). تقوم بإرجاع الأعمدة A و B و C و E ، وتوفر قائمة بجميع الصفوف المتطابقة حيث تكون القيمة في العمود E (“تمرين مشارك”) عبارة عن سلسلة نصية تحتوي على “لا”.

كيفية استخدام وظيفة QUERY في جداول بيانات Google 1

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

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

الصيغة الصحيحة لذلك هي = QUERY (‘Staff List’! A2: E، “Select A، B، C، E WHERE E = ‘No’”). تتجاهل هذه الصيغة العنوان الأصلي “الموظفون” في الخلية A1.

إذا أضفت موظفًا رقم 11 لم يحضر التدريب إلى القائمة الأولى ، الموضحة أدناه (كريستين سميث) ، فسيتم أيضًا تحديث صيغة QUERY لإظهار الموظف الجديد.

كيفية استخدام وظيفة QUERY في جداول بيانات Google 2

صيغ QUERY المتقدمة

وظيفة QUERY متعددة الاستخدامات. يسمح لك باستخدام عمليات منطقية أخرى (مثل AND و OR) أو وظائف Google (مثل COUNT) كجزء من بحثك. يمكنك أيضًا استخدام عوامل المقارنة (أكبر من ، وأقل من ، وما إلى ذلك) للعثور على قيم بين رقمين.

استخدام عوامل المقارنة مع QUERY

يمكنك استخدام QUERY مع عوامل تشغيل المقارنة (مثل أصغر من أو أكبر من أو يساوي) لتقييد البيانات وتصفيتها. للقيام بذلك ، نضيف عمودًا آخر (F) إلى “قائمة الموظفين” مع عدد الجوائز التي فاز بها كل موظف.

باستخدام QUERY ، يمكننا البحث عن جميع الموظفين الذين فازوا بجائزة واحدة على الأقل. تنسيق هذه الصيغة هو = QUERY (‘Staff List’! A2: F12، “SELECT A، B، C، D، E، F WHERE F> 0”).

يستخدم هذا عامل تشغيل أكبر من المقارنة (>) للبحث عن القيم أعلى من الصفر في العمود F.

كيفية استخدام وظيفة QUERY في جداول بيانات Google 3

يوضح المثال أعلاه أن الدالة QUERY أعادت قائمة بثمانية موظفين فازوا بجائزة واحدة أو أكثر. من بين 11 موظفًا ، لم يفز ثلاثة منهم بأي جائزة.

استخدم “و” و “أو” مع QUERY

تعمل وظائف عامل التشغيل المنطقي المتداخلة مثل AND و OR بشكل جيد في صيغة QUERY أكبر لإضافة معايير بحث متعددة إلى صيغتك.

من الطرق الجيدة لاختبار AND البحث عن البيانات بين تاريخين. باستخدام مثال قائمة الموظفين لدينا ، يمكننا سرد جميع الموظفين الذين ولدوا من 1980 إلى 1989.

يستفيد هذا أيضًا من عوامل المقارنة ، مثل أكبر من أو يساوي (> =) وأقل من أو يساوي (

تنسيق هذه الصيغة هو = QUERY (‘Staff List’! A2: E12، “SELECT A، B، C، D، E WHERE D> = DATE ‘1/1/1980’ and D

كيفية استخدام وظيفة QUERY في جداول بيانات Google 4

كما يتضح أعلاه ، فإن ثلاثة موظفين من مواليد 1980 و 1986 و 1983 يستوفون هذه المتطلبات.

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

سيكون تنسيق هذه الصيغة = QUERY (‘قائمة الموظفين’! A2: E12 ، “SELECT A ، B ، C ، D ، E WHERE D> = DATE ’12 / 31/1989 ‘أو D

كيفية استخدام وظيفة QUERY في جداول بيانات Google 5

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

استخدام COUNT مع QUERY

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

للقيام بذلك ، يمكنك دمج QUERY مع COUNT مثل هذا = QUERY (“قائمة الموظفين”! A2: E12 ، “SELECT E ، COUNT (E) group by E”).

كيفية استخدام وظيفة QUERY في جداول بيانات Google 6

بالتركيز على العمود E (“التدريب المشترك”) ، استخدمت دالة QUERY العدد COUNT لحساب عدد المرات التي تم فيها العثور على كل نوع من أنواع القيمة (سلسلة نصية “نعم” أو “لا”). من قائمتنا ، ستة موظفين أكملوا التدريب وأربعة لم يفعلوا ذلك.

يمكنك بسهولة تعديل هذه الصيغة واستخدامها مع أنواع أخرى من وظائف Google ، مثل SUM.