تتيح لك وظيفة QUERY سحب المعلومات من نطاق أو ورقة كاملة من البيانات باستخدام أوامر استعلام مرنة. يمنحك تعلم كيفية استخدام وظيفة الاستعلام في جداول بيانات Google الوصول إلى أداة بحث قوية.
إذا سبق لك كتابة استعلامات SQL للحصول على بيانات من قاعدة بيانات ، فستتعرف على وظيفة QUERY. إذا لم تكن لديك خبرة في استخدام قاعدة البيانات ، فلا يزال من السهل جدًا تعلم وظيفة QUERY.
ما هي وظيفة الاستعلام؟
تحتوي الوظيفة على ثلاث معلمات رئيسية:
=QUERY (بيانات ، استعلام ، رؤوس)
هذه المعلمات واضحة إلى حد ما.
- Data: نطاق الخلايا التي تحتوي على بيانات المصدر
- Query: بيان بحث يصف كيفية استخراج ما تريده من البيانات المصدر
- Headers: وسيطة اختيارية تتيح لك دمج عدة رؤوس في نطاق المصدر في رأس واحد في الورقة الوجهة
تأتي مرونة وقوة وظيفة QUERY من وسيطة Query ، كما سترى أدناه.
كيفية إنشاء صيغة استعلام بسيطة
تكون صيغة QUERY مفيدة بشكل خاص عندما يكون لديك مجموعة بيانات كبيرة جدًا تحتاج منها لاستخراج البيانات وتصفيتها.
تستخدم الأمثلة التالية إحصائيات أداء المدارس الثانوية الأمريكية SAT. في هذا المثال الأول ، ستتعلم كيفية كتابة صيغة QUERY بسيطة تُرجع جميع المدارس الثانوية وبياناتها حيث توجد "نيويورك" باسم المدرسة.
-
قم بإنشاء ورقة جديدة لوضع نتائج الاستعلام. في الخلية العلوية اليسرى اكتب =استعلام (. عند القيام بذلك ، سترى نافذة منبثقة تحتوي على وسيطات مطلوبة ، ومثال ، ومعلومات مفيدة حول الوظيفة.
Image -
بعد ذلك ، بافتراض أن لديك بيانات المصدر في الورقة 1 ، املأ الوظيفة على النحو التالي:
=استعلام (ورقة 1! A1: F460، "SELECT B، C، D، E، F حيث B مثل '٪ New York٪'")
تتضمن هذه الصيغة الوسيطات التالية:
- نطاق الخلايا: نطاق البيانات في A1 إلى F460 في الورقة 1
- SELECT Statement: عبارة SELECT التي تستدعي أي بيانات في الأعمدة B و C و D و E و F حيث يحتوي العمود B على نص يحتوي على الكلمة "New York بداخله
Image الحرف "٪" هو حرف بدل يمكنك استخدامه للبحث عن أجزاء من السلاسل أو الأرقام في أي مجموعة بيانات. سيؤدي ترك "٪" من مقدمة السلسلة إلى إرجاع أي اسم مدرسة يبدأ بالنص "New York".
-
إذا أردت العثور على اسم مدرسة بعينها من القائمة ، فيمكنك كتابة الاستعلام:
=الاستعلام (الورقة 1! A1: F460، "SELECT B، C، D، E، F حيث B='New York Harbour High School'")
يعثر استخدام عامل التشغيل =على تطابق تام ويمكن استخدامه للعثور على نص أو أرقام مطابقة في أي عمود.
Image
نظرًا لسهولة فهم وظيفة الاستعلام في جداول بيانات Google واستخدامها ، يمكنك سحب أي بيانات من أي مجموعة بيانات كبيرة باستخدام عبارات استعلام بسيطة مثل تلك المذكورة أعلاه.
استخدم وظيفة QUERY مع عامل مقارنة
تتيح لك عوامل المقارنة استخدام وظيفة QUERY لتصفية البيانات التي لا تفي بالشرط.
لديك حق الوصول إلى جميع عوامل التشغيل التالية في دالة QUERY:
- =: تطابق القيم قيمة البحث
- <: القيم أقل من قيمة البحث
- >: القيم أكبر من قيمة البحث
- <=: القيم أقل من أو تساوي قيمة البحث
- >=: القيم أكبر من أو تساوي قيمة البحث
- و!=: قيمة البحث وقيم المصدر غير متساوية
باستخدام نفس مجموعة بيانات نموذج SAT أعلاه ، دعنا نلقي نظرة على كيفية معرفة المدارس التي لديها متوسط رياضيات أعلى من 500 نقطة.
-
في الخلية اليسرى العلوية من الورقة الفارغة ، املأ دالة QUERY على النحو التالي:
=الاستعلام (الورقة 1! A1: F460، "SELECT B، C، D، E، F حيث E >500")
تستدعي هذه الصيغة أي بيانات حيث يحتوي العمود E على قيمة أكبر من 500.
Image -
يمكنك أيضًا تضمين عوامل تشغيل منطقية مثل AND و OR للبحث عن شروط متعددة. على سبيل المثال ، لسحب الدرجات فقط للمدارس التي لديها أكثر من 600 من المتقدمين للاختبار ومتوسط قراءة حرجة بين 400 و 600 ، يمكنك كتابة دالة QUERY التالية:
=الاستعلام (Sheet1! A1: F460، "SELECT B، C، D، E، F حيث C >600 و D >400 و D <600")
Image - توفر لك المقارنة والعوامل المنطقية العديد من الطرق المختلفة لسحب البيانات من جدول البيانات المصدر. إنها تتيح لك تصفية أجزاء مهمة من المعلومات حتى من مجموعات البيانات الكبيرة جدًا.
الاستخدامات المتقدمة لوظيفة الاستعلام
هناك بعض الميزات الأخرى التي يمكنك إضافتها إلى وظيفة QUERY ببعض الأوامر الإضافية. تتيح لك هذه الأوامر تجميع القيم وقيم العد وبيانات الطلب والعثور على القيم القصوى.
-
يسمح لك استخدام GROUP في دالة QUERY بتجميع القيم في صفوف متعددة. على سبيل المثال ، يمكنك متوسط درجات الاختبار لكل طالب باستخدام وظيفة GROUP. للقيام بذلك ، اكتب:
=الاستعلام (الورقة 1! A1: B24، "SELECT A، AVG (B) GROUP BY A")
Image -
باستخدام COUNT في دالة QUERY ، يمكنك حساب عدد المدارس التي يزيد متوسط درجات الكتابة فيها عن 500 باستخدام وظيفة QUERY التالية:
=QUERY (ورقة 1! A2: F460، "SELECT B، COUNT (F) GROUP BY B")
Image -
باستخدام ORDER BY في دالة QUERY ، يمكنك العثور على المدارس ذات الحد الأقصى من متوسط درجات الرياضيات وترتيب القائمة بهذه الدرجات.
=QUERY (ورقة 1! A2: F460، "SELECT B، MAX (E) GROUP BY B ORDER BY MAX (E)")
Image
