استخدم إمكانات الاستيراد في Google Sheet لتكون أكثر إنتاجية

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

مع الازدهار الرياضي ، أعطت الصناعات المنزلية مثل الرياضات الخيالية و DFS ومواقع المراهنات الدعائية للمشجعين سببًا آخر للمشاهدة (وزيادة تفاعلهم). تسمح هذه الألعاب للمشاهدين الأذكياء بكسب دولارات كبيرة من خلال التفوق على اللاعبين الآخرين (وكتّاب الرياضة) للفوز بمسابقات مربحة عبر الإنترنت.

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

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

معرف wpchtmlp = 15056

1. تحديد مصدر البيانات الهدف الخاص بك على الإنترنت

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

● الإصابات المحتملة

● معارك التمركز

● إسقاطات فانتسي بوينت

● قواعد التهديف الدوري

● إلخ

في هذا المثال ، دعنا نتخيل أننا ننشئ تصنيفات للاعبين لمركز قورتربك في دوري كرة قدم خيالي. لقد وجدت البيانات اللازمة منشورة على موقع الرابطة الوطنية لكرة القدم.

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

2. استيراد البيانات الجدولية إلى جداول بيانات Google

الوظيفة التي سنستخدمها لأداء استيراد البيانات هي IMPORTHTML. توضح وثائق جداول بيانات Google لهذه الوظيفة أننا سنحتاج إلى تقديم ثلاثة مدخلات:

1. عنوان url للصفحة المراد فحصها
2. الاستعلام الذي نريد تنفيذه (إما "قائمة" أو "جدول" ، اعتمادًا على العلامة الهيكلية التي نستهدفها).
3. فهرس (يبدأ من 1) يشير إلى الجدول الذي يجب استيراده من مصدر HTML.

لقد حددنا بالفعل عنوان url للصفحة التي نستهدفها. الاستعلام الذي نريد تحديده هو السلسلة "جدول" حيث يتم تخزين المعلومات في جدول HTML.

ملاحظة: إذا كنت تريد استيراد قائمة ذات تعداد نقطي (أو رقمي) ، فحدد "قائمة" بدلاً من "جدول" كمعامل ثانوي.

أخيرًا ، نحتاج إلى تحديد مكان وجود جدولنا المستهدف (بالتتابع) في شفرة مصدر HTML للصفحة نفسها. للقيام بذلك ، انقر بزر الماوس الأيمن في أي مكان في نافذة المتصفح وحدد "عرض المصدر". ثم يمكنك البحث عن السلسلة "<table" لتحديد الفهرس الصحيح.

نظرًا لأن جدولنا المستهدف هو الجدول الأول (والوحيد) في هذه الصفحة ، فسيكون فهرسنا 1.

ها هي صيغتنا الأولية:

= IMPORTHTML (“https://www.nfl.com/stats/player-stats/”، “table”، 1)

بعد إدخال الصيغة في A1 ، إليك كيفية ظهور البيانات:

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

1. لا نحتاج إلى جميع الأعمدة من الجدول المصدر. أريد أن أقصر بياناتي على الحقول التي أحتاجها فقط لإنشاء تصنيفاتي.
2. قد أرغب في تحديد بعض المعايير الدنيا التي يجب أن يفي بها اللاعب قبل أن أقوم باستيرادها (لدي معايير!).
3. أريد عمودًا إضافيًا واحدًا يلخص مدى جودة أداء اللاعب خلال الموسم بأكمله.
4. نظرًا لأنني أقوم بترتيب هؤلاء اللاعبين ، يجب أن يكون أفضل اللاعبين في القمة.

دعونا نرى كيف يمكننا تحقيق هذه الأهداف.

3. حدد الأعمدة التي تريد استيرادها

ليس هناك فائدة من ازدحام جدول البيانات لدينا ببيانات غير ضرورية. لحسن الحظ ، توفر جداول بيانات Google طريقة لاستيراد الأعمدة التي نريدها فقط.

بالنظر إلى الأعمدة الموجودة في جدولنا المستهدف ، فأنا مهتم فقط بعدد قليل من الأعمدة.

1. اللاعب (العمود 1)

2. تمرير ياردات (العمود 2)

3. TD (العمود 7)

4. INT (العمود 8)

هذه الأعمدة هي كل البيانات التي أحتاجها لمساعدتي في ترتيب هؤلاء اللاعبين للموسم القادم.

للحد من الأعمدة المستوردة ، نحتاج إلى التفاف دالة IMPORTHTML الخاصة بنا في دالة استعلام. تأخذ وظيفة الاستعلام معلمتين:

1. البيانات التي تمثل نطاق الخلايا المطلوب إجراء الاستعلام عليها.
2. استعلام مطلوب إجراؤه ، مكتوبًا بلغة Google Visualization API Query Language.

بالنسبة لمعلمة البيانات ، سنستخدم وظيفة IMPORTHTML بأكملها التي أنشأناها بالفعل. بالنسبة إلى الاستعلام ، سنقدم استعلامًا يستهدف الأعمدة التي نريد تضمينها في الاستيراد.

= استعلام (IMPORTHTML (“https://www.nfl.com/stats/player-stats/”، “table”، 1)، “select Col1، Col2، Col7، Col8”)

الآن تبدو بياناتنا المستوردة هكذا ، بدون أعمدة غير ضرورية!

لذا يمكننا الآن استيراد مجموعة البيانات المطلوبة بنجاح مع الحد أيضًا من الأعمدة التي ستظهر في أوراقنا النهائية. ولكن لا يزال هناك عمل يتعين القيام به.

4. تصفية الصفوف المستوردة على أساس المعايير المخصصة

بالنسبة لتصنيفاتي ، أريد فقط أن أفكر في قورتربك الذين رموا ما لا يقل عن 3000 ياردة. بشكل عام ، أريد تصفية الصفوف التي تم استيرادها بناءً على المعايير الموجودة في عمود معين.

لإنشاء عامل تصفية ، سنقوم بتعديل وظيفة الاستعلام الموجودة لدينا لتشمل جملة WHERE. على وجه التحديد ، سنشير إلى أننا نريد فقط استيراد الصفوف حيث تكون القيمة في Col2 أكبر من أو تساوي 3000.

= استعلام (IMPORTHTML (“https://www.nfl.com/stats/player-stats/”، “table”، 1)، “حدد Col1، Col2، Col7، Col8 حيث Col2> = 3000”)

الآن ، عندما نفحص مجموعة النتائج ، يمكننا أن نرى أن لامار جاكسون (اللاعب الوحيد الذي يمرر أقل من 3000 ياردة) قد تم حذفه. حظ أفضل العام المقبل!

ملاحظة: لامار جاكسون هو خيول ، ونحن عادة ما ندرجه في تصنيفاتنا. هذا نظري يا رفاق!

5. قم بتوليد مخرجات فانتسي بوينت باستخدام حقل محسوب

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

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

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

تطوير معادلة لحساب نقاط الخيال

إذن ما هي الصيغة التي نستخدمها لحساب ناتج النقطة الخيالية للوسطاء؟

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

نقاط الخيال = (تمرير ياردة / 25) + (TDs * 6) - (ذكاء * 2)

ضبط الاستعلام لتضمين الحقل المحسوب

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

= استعلام (IMPORTHTML (“https://www.nfl.com/stats/player-stats/”، “table”، 1)، “حدد Col1، Col2، Col7، Col8، (Col2 / 25) + (Col7 * 6) - (Col8 * 2) حيث Col2> = 3000 بوصة)

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

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

ما يتعين علينا القيام به هو إضافة المزيد من البيانات إلى نهاية الاستعلام الخاص بنا:

1. الكلمة الرئيسية "التصنيف"

2. كرر الحقل المحسوب

3. رأس العمود المطلوب كسلسلة

= استعلام (IMPORTHTML (“https://www.nfl.com/stats/player-stats/”، “table”، 1)، “حدد Col1، Col2، Col7، Col8، (Col2 / 25) + (Col7 * 6) - (Col8 * 2) حيث Col2> = 3000 ملصق (Col2 / 25) + (Col7 * 6) - (Col8 * 2) "Fantasy Points")

الآن هذا هو أكثر من ذلك!

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

بالنسبة لي ، تضيف الأرقام العشرية ضوضاء خلفية غير ضرورية. فلنرَ ما إذا كان بإمكاننا تقريب هذه الأعداد.

لتحقيق ذلك ، سنطبق تنسيقًا مخصصًا على استعلامنا. على وجه التحديد ، نحتاج إلى تحديد:

1. الكلمة الرئيسية "FORMAT"

2. كرر الحقل المحسوب

3. تنسيق الأرقام المخصص ("#" في حالتنا)

= استعلام (IMPORTHTML (“https://www.nfl.com/stats/player-stats/”، “table”، 1)، “حدد Col1، Col2، Col7، Col8، (Col2 / 25) + (Col7 * 6) - (Col8 * 2) حيث Col2> = 3000 ملصق (Col2 / 25) + (Col7 * 6) - (Col8 * 2) شكل "Fantasy Points" (Col2 / 25) + (Col7 * 6) - (Col8 * 2) "#" ")

الآن تبدو طاولتنا أنظف.

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

نظرًا لأن هذا التمرين يهدف إلى إنشاء تصنيفات لهؤلاء اللاعبين ، فسيكون من المفيد فرزهم عند استيراد البيانات.

دعونا نعمل على ذلك بعد ذلك.

6. ترتيب الصفوف على أساس العمود المحسوب

سنعتمد مرة أخرى على استعلامنا لتحديد ترتيب صف أولي لبياناتنا. نريد فرز صفوفنا حسب العمود المحسوب ("نقاط الخيال"). لكننا نريد القيام بذلك بترتيب تنازلي.

يمكن إجراء الفرز من خلال جملة ORDER BY DESC. مرة أخرى (ولسوء الحظ) ، سنحتاج إلى الرجوع إلى الحقل المحسوب بالكامل.

ملاحظة: لا يمكنك استخدام رأس عمود الحقل المحسوب في جملة ORDER BY لأنه في ترتيب SQL للعمليات لا يتم تطبيق الاسم المستعار إلا بعد ORDER BY.

ها هي صيغتنا النهائية:

= استعلام (IMPORTHTML (“https://www.nfl.com/stats/player-stats/”، “table”، 1)، “حدد Col1، Col2، Col7، Col8، (Col2 / 25) + (Col7 * 6) - (Col8 * 2) حيث Col2> = 3000 ترتيب حسب (Col2 / 25) + (Col7 * 6) - (Col8 * 2) ملصق DESC (Col2 / 25) + (Col7 * 6) - (Col8 * 2 ) تنسيق "نقاط الخيال" (عمود 2/25) + (عمود 7 * 6) - (عمود 8 * 2) "#" ")

تم الآن ترتيب مجموعة البيانات النهائية لدينا مع أفضل اللاعبين أولاً (وفقًا لقواعد التسجيل الخاصة بدورينا).

ميزات استيراد البيانات في جداول بيانات Google بسيطة ولكنها فعالة

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

ومع وجود أكثر من 6 مليارات صفحة ويب (وأكثر) ، يبدو أنك ستكون مشغولاً للغاية.