پیش‌بینی نتایج فوتبال با استفاده از اکسل

بسیاری از افراد در زمینه‌های کاری گوناگون از نرم‌افزار اکسل (Excel) استفاده می‌کنند. انجام امور حسابداری، دسته‌بندی داده‌ها و ترسیم نمودار جهت تحلیل، فرمول نویسی، ایجاد بانک اطلاعاتی و گزارش‌گیری تنها برخی از کاربردهای این نرم‌افزار هستند. در این مطلب، هدف پرداختن به روشی است که با بهره‌گیری از آن و نرم‌افزار اکسل بتوان نتایج مسابقات فوتبال را «پیش‌بینی» کرد. احتمالا وقتی سخن از پیش‌بینی به میان می‌آید تصور عموم، استفاده از روش‌های یادگیری ماشین و داده‌کاوی و پیاده‌سازی آن‌ها با استفاده از زبان‌های برنامه‌نویسی R، پایتون و دیگر موارد است. این در حالیست که در این مطلب به کاربرد جالبی از اکسل جهت پیش‌بینی پرداخته خواهد شد که ممکن است بسیاری از افراد را شگفت زده کند.

مدل‌های متعددی برای پیش‌بینی مسابقات فوتبال توسط پژوهشگران گوناگون ارائه شده. پیاده‌سازی اغلب این روش‌ها وابسته به کتابخانه‌ها و  فایل‌هایی است که احتمال دارد افراد زیادی به آن‌ها دسترسی نداشته باشند. از همین رو، هدف این مطلب ساخت یک مدل برای پیش‌بینی نتایج مسابقات فوتبال با استفاده از تعدادی فرمول و نرم‌افزار اکسل است. جزئیات دقیق این روش در ادامه ارائه شده و کلیه افراد می‌توانند با استفاده از آن‌ها، مدل خودشان را بسازند.

«لازم به ذکر است که نسخه فارسی‌سازی شده فایل اکسل اصلی حاوی مدل و داده‌ها، توسط فرادرس آماده شده و از اینجا در دسترس است.»

شکل ۱: نمای اولین شیت (مقدمه)

خلاصه مدل

مدل پیشنهادی با استفاده از نرم‌افزار اکسل و بهره‌گیری از محاسبات گوناگون ساخته شده است. در محاسبات مذکور، اساسا دو ورودی از کاربر دریافت می‌شود و دیگر داده‌ها از مجموعه داده‌های از پیش آماده مرتبط تامین شده‌اند. پس از باز کردن فایل اکسل مربوط به این مدل، کاربر با نُه شیت مواجه خواهد شد. شیت اول (شکل ۱) توضیح مقدماتی از چیستی مدل ارائه می‌کند. یکی از دکمه‌های تعبیه شده در این شیت برای مطالعه بیشتر درباره چگونگی عملکرد مدل و دکمه دوم برای ورود به شیت بعدی یعنی Round Robin Predictions است.

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

لازم به ذکر است که این مدل بر اساس تابعی که به آن ()RAND= گفته می‌شود ساخته شده است. این تابع اعداد تصادفی حقیقی بزرگ‌تر یا مساوی صفر و کوچکتر یا مساوی با یک را که به صورت یکنواخت توزیع شده‌اند تولید می‌کند. هر بار که تابع فراخوانی شود، یک عدد حقیقی تصادفی جدید تولید می‌شود. به بیان دیگر، تابع RAND یک عدد حقیقی تصادفی در بازه a و b (با استفاده از رابطه (RAND()*(b-a)+a=) تولید می‌کند. این تابع هیچ آرگومانی ندارد. همچنین، در این مدل با هر بار تغییر سلول‌ها، یک عدد تصادفی جدید ایجاد می‌شود. این یعنی مدل یک فرد، هرگز مشابه مدل شخص دیگری نخواهد بود. مشکلی در این راستا وجود ندارد زیرا مدل برای دفعات زیاد تکرار شده و پایدار خواهد شد.

شیت Round Robin Predictions

چنانکه در شکل دو مشهود است، ۱۳ ستون - غیر مخفی - در جدول موجود در این شیت وجود دارد. ستون (Groups)، گروه هر تیم را مشخص می‌کند، کلیه تیم‌ها متعلق به یکی از گروه‌های G، F، E، D، C، B، A و H هستند. ستون (Teams) اسامی تیم‌ها است (در واقع نام کشوری که هر تیم نماینده آن است). ستون (Matches Played) تعداد بازی‌های انجام شده، (Wins) تعداد بردها، (Draws) تعداد مساوی‌ها، (Losses) تعداد باخت‌ها، (Goals For) گل‌های زده و (Goals Against) گل‌های خورده را برای هر تیم نشان می‌دهد. ستون (-/+) تفاضل تعداد گل‌های زده و خورده شده توسط یک تیم را محاسبه می‌کند، (Points) امتیار هر تیم از بازی‌های انجام شده، و در ستون (Diff Rank) رتبه مربوط به تفاضل محاسبه شده در ستون پیشین را با استفاده از روش رده‌بندی اِلو (Elo) محاسبه کرده ¹ و در نهایت در ستون آخر (Rank in Group) رتبه یک تیم در گروه خودش مشخص می‌شود. نکته قابل توجه آن است که از هر گروه فقط تیم‌های اول و دوم برای حضور در مرحله بعد احراز صلاحیت می‌شوند.

اگر کاربر فکر می‌کند که رتبه اختصاص داده شده به تیمش (یا دیگر تیم‌ها) منصفانه نیست، می‌تواند درصد مورد نظر خود برای آن تیم را در ستون E با عنوان «Overall Performance Adjustment Column» (شاخص عملکرد) وارد کند. این کار موجب افزایش یا کاهش کارایی مورد انتظار تیم مذکور می‌شود. همچنین، کاربر می‌تواند به سربرگ «This Year’s Matches» رفته و گل‌های به ثمر رسیده بازی مورد نظرش را (بر اساس پیش‌بینی خودش) کاهش یا افزایش دهد. انجام این کار توسط کاربر موجب می‌شود پیش‌بینی مدل نادیده گرفته شود.

در واقع در ستون E، انسان می‌تواند حدسی که از شیوه عملکرد هر تیم دارد را به‌عنوان ورودی مدل وارد کند. برای مثال، اگر فرد فکر می‌کند که هلند بهتر از رتبه‌ای که به آن داده شده بازی می‌کند، می‌تواند درصد رتبه آن را افزایش داده و به‌طور بالعکس، اگر باور دارد که آلمان ضعیف‌تر از رتبه تخصیص داده شده به آن بازی می‌کند، می‌تواند درصد رتبه آن را کاهش دهد.

شکل ۲: نمای دومین شیت (Round Robin Predictions)

شیت This Year's Matches

شکل ۳: نمای سومین شیت (This Year's Matches)

در شیت سوم، ستون اول (Date) تاریخ بازی انجام شده، ستون (Groups) گروه یک تیم، ستون (Team A) نام تیم A، ستون چهارم (Predicted Score A) امتیاز پیش‌بینی شده برای تیم A، ستون پنجم (Team B) نام تیم B، ستون ششم (Predicted Score B) امتیاز پیش‌بینی شده برای تیم B، و دو ستون آخر تنظیمات ارائه شده توسط کاربر به ترتیب برای تیم‌های A و B است. در واقع، اگر کاربر فکر می‌کند که تعداد گل‌های پیش‌بینی شده توسط ماشین نادرست هستند، می‌تواند پیش‌بینی مورد نظر خود را در ستون‌های J و K وارد کند. این کار موجب می‌شود که مدل امتیاز پیش‌بینی کرده خود را دست‌خوش تغییر کند.

لازم به ذکر است با توجه به شیوه خاصی که مدل طراحی شده، هر بار پس از تغییر محتوای یک سلول توسط کاربر، کلیه محاسبات از نو انجام می‌شوند. اما اگر کاربر پیش‌بینی خود را در ستون K و J وارد کند، این مقادیر همواره به قوت خود باقی می‌مانند. در فایل موجود، بسیاری از ستون‌هایی که منجر به عملکرد مدل می‌شوند، مخفی شده‌اند. برای مشاهده این ستون‌ها می‌توان آن‌ها را غیر مخفی (انتخاب ستون‌های L تا DN، کلیک راست روی این ناحیه و انتخاب گزینه unhide) کرد.

شیت Playoff Predictions

پس از ارائه پیش‌بینی توسط کاربر در شیت‌های پیشین، مدل دو تیم برتر هر گروه را تعیین کرده و آن‌ها را در سربرگ «Playoff Predictions» (پیش‌بینی مرحله حذفی) قرار می‌دهد. کاربر می‌تواند در این سربرگ برنده (احتمالی) هر بازی را مشاهده کند.

شکل ۴: نمای چهارمین شیت (Playoff Predictions)

سایر شیت‌ها

در شیت (Prediction Summary) دکمه‌ای برای مشاهده مجموعه داده مورد استفاده در مدل وجود دارد. شیت «Appendix 1 - Team Data» جدول مربوط به مجموعه داده مورد استفاده در مدل، «Appendix 2 - Past Results» نتایج پیشین کسب شده توسط هر یک از تیم‌ها و «Appendix 3 - Goals Per Group» تعداد گل‌های زده شده در هر گروه است. در شیت آخر یعنی «Appendix 4 - Odds»، احتمال برد هر تیم در صورت بازی با سایر تیم‌ها (که توسط مدل پیش‌بینی شده) نمایش داده می‌شود.

جزئیات مدل

کار با استفاده از دو مجموعه داده آغاز می‌شود. مجموعه داده اول حاوی کلیه بازی‌های بین‌المللی فوتبال انجام شده طی ۱۴۶ سال گذشته است. در این مطلب مجموعه داده به نوعی کوتاه شده که تنها اطلاعات بازی‌های انجام شده طی ۴۰ سال اخیر را در بر می‌گیرد. مجموعه داده اصلی از اینجا برای عموم در دسترس است. دومین مجموعه داده مورد استفاده، ۱۰۰ تیم برتر بر اساسی امتیازدهی اِلو (Elo) را در بر می‌گیرد.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

با استفاده از امتیازهای Elo، صد تیم برتر را به ده گروه مختلف شکسته و به ده تیم برتر اول رتبه ۹۰ تخصیص داده می‌شود. به ده تیم برتر دوم (۱۱ الی ۲۰) رتبه ۸۰ داده و برای سایر تیم‌ها نیز به همین صورت رتبه‌دهی انجام می‌شود. این کار با این هدف انجام می‌شود که تصور خوبی از عملکرد تیم‌های قرار گرفته در هر گروه حاصل شود. سپس این داده‌ها را با داده‌های بازی‌های ۴۰ سال گذشته تاکنون، تلاقی داده و بازی‌هایی که این ۱۰۰ تیم در مقابل یکدیگر نبوده‌اند حذف می‌شوند. همچنین بازی‌هایی که در آن تیمی با هفت یا تعداد بیشتری گل برنده شده (حدود ۲٪ بازی‌ها) نیز حذف می‌شوند. دلیل این کار حذف دورافتادگی‌ها به منظور افزایش صحت نتایج پیش‌بینی است. پس از انجام این کارها، داده‌های ۱۲،۰۰۰ مسابقه باقی می‌ماند.

سپس، تعداد بازی‌های انجام شده هر گروه در مقابل گروه‌های دیگر و تعداد گل‌های رد و بدل شده در هر مسابقه محاسبه می‌شوند. عدد حاصل شده در نهایت تقسیم می‌شود تا میانگینی از گل‌های هر گروه برپایه اینکه در کدام تیم بازی انجام شده فراهم شود. این مورد در شیت Appendix3 – Goals Per Group نشان داده می‌شود.

اکنون فرآیند پیش‌بینی آغاز می‌شود. این کار نیازمند بهره‌گیری از آمار است. مسابقات فوتبال به پیروی از «توزیع پواسون» ² (Poisson Distribution) معروف هستند. این یعنی گل امتیاز گرفته در یک بازی از سایر گل‌های امتیاز گرفته در همان بازی مستقل هست. این یک فرض مهم و جالب توجه است زیرا موجب چشم‌پوشی از مفاهیمی مانند بایستگی، تکانه و دیگر موارد می‌شود. برای انجام هرگونه پیش‌بینی باید در ابتدا فرضیاتی وجود داشته باشد.

فرمول توزیع پواسون به صورت زیر است:

عناصر این معادله عبارتند از:

• (P(k احتمال امتیازدهی K گل است که در آن K می‌تواند هر عددی باشد. در مدل ارائه شده در این مطلب K = ۱، ۲، ۳، ...، ۹
• دلتا یا δ، میانگین گل‌های به ثمر رسیده توسط هر تیم است (که برای هر دسته متفاوت است. زیرا پیش‌تر، تیم‌ها به گروه‌های گوناگون شکسته شده‌اند). مقدار دلتا در فرمول به توان K می‌رسد که برابر با ۰، ۱، ۲، ۳، ...، ۹ است.
• e مقداری ثابت (به آن عدد اویلری گفته می‌شود) و برابر با ۲.۷۱۸۲۸ است و در فرمول به توان منفی دلتا می‌رسد.
• همه این موارد بر فاکتوریل K تقسیم می‌شود. (فاکتوریل یعنی یک عدد بر کلیه اعداد طبیعی کوچک‌تر از خودش به جز صفر ضرب شود)

کلیه خروجی‌های این مدل اعداد زیر یک خواهند بود، زیرا این مقادیر نشانگر احتمال هستند. نمودار زیر درک این مطلب را آسان‌تر می‌سازد.

در محور Y، احتمال به ثمر رسیدن گل برای تیم‌های هر گروه و در محور X اندیس آن گروه (گروه‌ها هشت تا است و اندیس‌گذاری از صفر آغاز شده) مشخص شده است. δ (میانگین گل‌های به ثمر رسیده) استفاده شده در اینجا ۱.۲ است. این مقدار برابر با میانگین ستون‌های E و F در شیت Appendix2- Past Results است. تیم‌های زیادی باید دستکم یک گل بزنند، در غیر این‌صورت اغلب بازی‌ها با نتیجه صفر-صفر تمام می‌شود.

با تولید اعداد تصادفی (با استفاده از تابع ()RAND=) و تطبیق دادن آن با نتایج توزیع پواسون، تعداد گل‌های به ثمر رسیده پیش‌بینی شده برای هر مسابقه به عنوان خروجی ارائه می‌شود. این فرآیند باید ۵۰ بار برای هر تیم طی هر بازی تکرار شود. از آنجا که ابزار مورد استفاده در این مطلب اکسل است، کلیه این کارها به سادگی کپی کردن یک فرمول در سلول انجام می‌شود.

انجام این کار به پیش‌بینی در فاز round robin مسابقات کمک می‌کند. دو تیم برتر از هر گروه برای مرحله حذفی تایید می‌شوند. اما باید توانایی تعیین اینکه تیم‌ها چه کسانی هستند به‌منظور پیش‌بینی تعداد گل‌ها که دومین متریک پس از برد، باخت و تساوی است برای انتخاب دو تیم برتر وجود داشته باشد.

اکنون که چگونگی تایید تیم‌ها برای مرحله حذفی مشخص شد، دیگر به تعداد گل‌های به ثمر رسیده توجهی نمی‌شود. اهمیتی ندارد، یک تیم در هر مرحله بازی‌های حذفی با حداقل یک گل برنده خواهد بود. بنابراین، هیچ گره‌ای وجود نخواهد داشت.

اکنون، درصد شانسی که هر تیم برای پیشرفت در مرحله حذفی دارد پیش‌بینی می‌شود. این کار با استفاده از فرمول امتیازدهی Elo برای محاسبه انتظار برد هر تیم صورت می‌پذیرد. دلیل انجام این کار آن است که به رتبه آینده تیم‌ها اهمیتی داده نمی‌شود و تنها چگونگی عملکرد فعلی آن‌ها حائز اهمیت است.

در فرمول Elo، متغیر W_e انتظار برد و dr تفاوت در رتبه‌بندی بین دو تیم (به علاوه ۱۰۰ برای تیم خانگی) است. در شرایطی که فقط یک تیم میزبان وجود داشته باشد این کار ساده خواهد بود. به همین دلیل است که یک خطای فرمول ناسازگار در شیت Appendix 4 – Odds وجود دارد.

مدل ارائه شده در اینجا، برزیل را به عنوان برنده مسابقات پیش‌بینی می‌کند که البته چیز عجیبی هم نیست. اگرچه، این نتیجه در حال حاضر و در واقعیت کمی بعید به نظر می‌رسد. پیش‌بینی می‌شود که مسابقه فینال برای دریافت جام طلا بین برزیل و آلمان برگزار شود. در این شرایط، آلمان ۴۹.۴۲٪ و برزیل ۵۰.۵۸٪ شانس برد دارد. این مقدار برابر با احتمال آمدن هر یک از دو روی یک سکه طبیعی طی یک پرتاب است (احتمال آمدن هر رو ۵۰ به ۵۰ است). سناریو دیگر آن است که برزیل با آلمان در مرحله حذفی دیدار کند. در این راستا، آلمان باید دومین تیم در گروه خود و برزیل اولین باشد.

فیلم آموزش ترفندهای کاربردی در اکسل Excel در فرادرس

کلیک کنید

این مدل کامل نیست، در نهایت هر چه باشد فقط یک مدل است. دیگر پژوهشگران، برای پیش‌بینی نتایج فوتبال، مدت زمان سفر از نقطه A به B، میزبان بودن یا نبودن یک تیم و موارد دیگری را نیز برای ارائه پیش‌بینی‌های بهتر به‌عنوان شاخص در نظر گرفته‌اند. چنین ویژگی‌هایی منطقی به نظر می‌رسند. هرچه تیمی نزدیک‌تر به کشور خانگی خود بازی کند، تعداد تماشاچی‌ها و هواداران بیشتری در ورزشگاه حضور پیدا می‌کنند و بازیکنان با قوت قلب بیشتری بازی خواهند کرد. البته همه این حرف‌ها تا زمانی معنا دارد که به خاطر نیاورید آلمان، برزیل را در کشور خودش با نتیجه ۷ بر ۱ برد. شاید به همین دلیل است که به جای پیش‌بینی بازی‌ها توسط مدل، مسابقات به‌صورت واقعی انجام می‌شوند تا نتایج نهایی مشخص شود.

¹ این متریک توسط دکتر آرپد الو برای محاسبه رتبه بازیکنان شطرنج ساخته و توسعه داده شده است. اما در همه زمینه‌ها از فوتبال گرفته تا NHL و E-Sports استفاده می‌شود. اگرچه Elo به‌عنوان یک راهکار رسمی برای رتبه‌بندی تیم‌ها توسط فیفا استفاده نمی‌شود، اما پژوهشی که در سال ۲۰۰۹ انجام شده حاکی از آن است که این رتبه‌بندی به درستی عمل می‌کند. Elo بهترین عملکرد را در پیش‌بینی برندگان یک بازی فوتبال دارد.

² توزیع پواسون «احتمال آنکه یک رویداد (گل) به تعداد مشخصی در یک بازه زمانی یا فضایی ثابت اتفاق بیفتند را شرح می‌دهد. بدین شرط که رویدادها با نرخ میانگین مشخصی و به صورت مستقل از زمان رخ دهند» است.