الصفحة الرئيسية>المتعلقة بالبرمجة>شفرة المصدر الأخرى
تنزيل

الاستعلام عن جداول بيانات جوجل

تعد لغة استعلام Google طريقة سريعة وقوية لتحليل البيانات في أوراق Google الخاصة بك والتي تستعير العديد من الأنماط المستخدمة في SQL. سيُطلعك هذا البرنامج التعليمي على أساسيات كيفية استخدام الاستعلامات للإجابة على أسئلة محددة من مجموعة بيانات نموذجية.

استيراد بيانات العينة

نحتاج أولاً إلى بعض نماذج البيانات للعب بها. سنستخدم وظيفة الاستيراد لسحب جدول بيانات سكان العالم من ويكيبيديا: 

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)", "table", 3)

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

انقر على أي خلية تحتوي على بيانات لتظليلها، ثم اضغط على CMD + A لتظليل الجدول بأكمله. بعد تمييز الجدول بأكمله، انقر فوق Data > Named Ranges لفتح لوحة Named Ranges - وسوف تتحرك للخارج من اليمين.

النطاق المسمى هو لقب يمكنك منحه لمجموعة من الخلايا. دعونا نسمي "بلداننا".

عظيم! نحن الآن جاهزون لبدء الاستعلام!

قم بتمييز الخلية J1 - سنستخدم هذه الخلية لكتابة جميع الاستعلامات أدناه:

أساسيات الاستعلام

عرض نسخة من مجموعة البيانات بأكملها: 

=QUERY(countries, "select *",1)

إظهار أعمدة البلدان والمنطقة فقط: 

=QUERY(countries, "select B, C",1)

الحصول على تحديد مع بند "أين".

تعد جملة WHERE طريقة رائعة لبدء طرح أسئلة أكثر تحديدًا من مجموعة البيانات الخاصة بك.

أرني الدول التي يزيد عدد سكانها عن 100 مليون أو يساوي: 

=QUERY(countries, "SELECT B, E where E >= 100000000")

أرني البلدان التي تحتوي على "es" في أسمائها: 

=QUERY(countries, "SELECT B where B contains 'es'")

أرني البلدان التي لا تحتوي على "es" في أسمائها: 

=QUERY(countries, "SELECT B where not B contains 'es'")

أرني البلدان التي تبدأ بـ "S": 

=QUERY(countries, "SELECT B where B STARTS WITH 'S'")

أرني البلدان التي تنتهي بـ "s" (تذكر أن تكون حساسًا لحالة الأحرف: 

=QUERY(countries, "SELECT B where B ENDS WITH 's'")

أرني الدول التي تحتوي على كلمة "United": 

=QUERY(countries, "SELECT B where B CONTAINS 'United'")

دعونا نحصل على إضافة أكثر تحديدًا في جملة AND!

أرني جميع البلدان في الأمريكتين التي يقل عدد سكانها أيضًا عن 100000: 

=QUERY(countries, "select B, F WHERE C = 'Americas' AND F <= 100000", 1)

يمكن ربط جمل AND معًا إلى ما لا نهاية.

يقوم هذا الاستعلام بإرجاع جميع البلدان في الأمريكتين التي يقل عدد سكانها عن 100000 وأكثر من 10000: 

=QUERY(countries, "select B, F WHERE C = 'Americas' AND F <= 100000 AND F >= 10000", 1)

القيم الإجمالية

ربما يكون أحد الأشياء الأولى التي تريد القيام بها عند استكشاف مجموعة البيانات هو رؤية بعض التجميعات البسيطة: القيم القصوى والدنيا والمتوسطة في حقول معينة. دعنا نعثر على الحد الأقصى والحد الأدنى والمتوسط ​​لعدد السكان لعام 2017 للبلدان الموجودة في مجموعة البيانات الخاصة بنا: 

=QUERY(countries, "select max(F), min(F), avg(F)", 1)

هذا بارد،

مع تعديل طفيف يمكننا أن نرى الحد الأقصى والحد الأدنى ومتوسط ​​عدد السكان في كل قارة: 

=QUERY(countries, "select C, max(F), min (F), avg(F) GROUP by C", 1)

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

دعونا نستخدم هاتين الجملتين لمعرفة عدد البلدان في قائمتنا الموجودة في كل منطقة: 

=QUERY(countries, "SELECT C, count(B) GROUP by C", 1)

دعونا نضيف عبارة "ORDER BY" لتظهر لنا قائمة بجميع المناطق، مرتبة تصاعديًا من الأعلى إلى الأدنى: 

=QUERY(countries, "SELECT C, count(B) GROUP by C ORDER by C asc", 1)

ستقوم جملة "PIVOT" بتجميع القيم في صف واحد.

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

=QUERY(countries, "SELECT sum(F) pivot C", 1)

القيام ببعض الرياضيات

يمكنك أيضًا تحليل بياناتك عن طريق إجراء عمليات حسابية كجزء من استفساراتك.

لنعرض الآن قائمة الدول التي بلغ عدد سكانها عام 2017 كنسبة مئوية من إجمالي سكان العالم: 

=QUERY(countries, "select B, C, (F / 7550262101) * 100", 1)

تبدو بيانات النسبة المئوية لدينا رائعة، ولكن رأس العمود سهل القراءة للغاية. يمكننا النقر داخل الخلية وإعادة تسميتها - ولكن يمكننا أيضًا تعيين الاسم كجزء من الاستعلام عن طريق إضافة معلمة "label": 

=QUERY(countries, "select B, C, (F / 7550262101) * 100 Label (F / 7550262101) * 100 'Percentage'", 1)

مراجع

توثيق لغة استعلام Google: https://developers.google.com/chart/interactive/docs/querylanguage

نظرة عامة على وظيفة الاستعلام في جداول بيانات Google عن طريق الترميز للخاسرين: https://codingisforlosers.com/google-sheets-query-function/#why

يوسع
معلومات إضافية
تطبيقات ذات صلة
نوصي لك
أخبار ذات صلة الكل