Gunakan Kemampuan Mengimpor Google Sheet agar Lebih Produktif

Di Amerika Serikat, olahraga adalah bisnis besar . Sekitar 154 juta orang menonton siaran langsung olahraga setidaknya sebulan sekali. Dan angka tersebut diperkirakan akan meningkat setiap tahun.

Dengan ledakan olahraga, industri rumahan seperti olahraga fantasi, DFS, dan situs taruhan prop telah memberi penggemar alasan lain untuk menonton (dan menjadi lebih terlibat). Game-game ini memungkinkan pemirsa yang cerdas untuk mendapatkan banyak uang dengan mengakali pemain lain (dan sportsbook) untuk memenangkan kontes online yang menguntungkan.

Cara utama agar gamer fantasi mendapatkan keuntungan adalah dengan menggunakan algoritme kepemilikan untuk memproyeksikan kinerja pemain. Memijat statistik pemain memungkinkan pemain untuk menemukan permata statistik yang tidak jelas yang dapat mereka manfaatkan untuk memenangkan lebih banyak kontes.

Hari ini, kita akan melihat bagaimana Anda dapat melakukannya menggunakan Google Spreadsheet. Kami akan mempersiapkan draf fantasi hipotetis dengan mengimpor statistik pemain dari situs web publik. Kemudian, kita akan menggunakan kekuatan fungsi untuk mengubah, memanipulasi, memfilter, dan mengurutkan data ini sesuai keinginan kita.

wpchtmlp id=15056

1. Identitas Sumber Data Target Anda Secara Online

Peserta liga hadiah sepak bola fantasi tahu betapa pentingnya mengevaluasi dan memberi peringkat pemain sebelum draft liga. Pemain fantasi biasanya memulai dengan statistik dari musim sebelumnya ketika mengembangkan peringkat pemain mereka. Kemudian, mereka menyesuaikan peringkat pemain mereka berdasarkan berbagai faktor:

● Potensi Cedera

● Pertempuran Posisi

● Proyeksi Titik Fantasi

● Aturan Skor Liga

● dll

Untuk contoh ini, bayangkan kita membuat peringkat pemain untuk posisi quarterback di liga sepak bola fantasi. Saya menemukan data yang diperlukan yang dipublikasikan di situs web National Football League.

Tujuan kami adalah mengimpor data statistik ini ke dalam lembar Google untuk membuat peringkat khusus kami untuk musim saat ini.

2. Impor Data Tabular ke Google Spreadsheet

Fungsi yang akan kita gunakan untuk melakukan import data adalah IMPORTHTML. Dokumentasi Google Sheets untuk fungsi ini menunjukkan bahwa kita perlu memberikan tiga input:

1. URL halaman yang akan diperiksa
2. Kueri yang ingin kami lakukan (baik "daftar" atau "tabel", tergantung pada mark-up struktural yang kami targetkan).
3. Indeks (mulai dari 1) yang menunjukkan tabel mana dari sumber HTML yang harus diimpor.

Kami telah mengidentifikasi url halaman yang kami targetkan. Kueri yang ingin kami tentukan adalah string “tabel” karena informasinya disimpan dalam tabel HTML.

Catatan: Jika Anda ingin mengimpor daftar berpoin (atau bernomor), tentukan "daftar" alih-alih "tabel" sebagai parameter sekunder.

Terakhir, kita perlu menentukan di mana tabel target kita berada (secara berurutan) dalam kode sumber HTML untuk halaman itu sendiri. Untuk melakukannya, klik kanan di mana saja di jendela browser dan pilih 'View Source'. Kemudian Anda dapat mencari string “<table” untuk menentukan indeks yang benar.

Karena tabel target kami adalah tabel pertama (dan satu-satunya) di halaman ini, indeks kami akan menjadi 1.

Berikut adalah rumus awal kami:

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

Setelah kita masukkan rumus ke A1, berikut tampilan datanya :

Bagus, itu mudah! Tetapi kami dapat menggunakan lebih banyak fitur Google Spreadsheet untuk meningkatkan proses ini. Saya melihat beberapa kekurangan dengan proses kami saat ini:

1. Kami tidak membutuhkan SEMUA kolom dari tabel sumber. Saya ingin membatasi data saya hanya pada bidang yang saya perlukan untuk membuat peringkat saya.
2. Saya mungkin ingin menentukan beberapa kriteria minimum yang harus dipenuhi pemain sebelum saya mengimpornya (saya punya standar!).
3. Saya ingin satu kolom tambahan yang merangkum seberapa baik performa pemain sepanjang musim.
4. Karena saya memberi peringkat pemain ini, pemain terbaik harus berada di atas.

Mari kita lihat bagaimana kita dapat mencapai tujuan-tujuan ini.

3. Batasi Kolom yang Ingin Anda Impor

Tidak ada gunanya mengacaukan spreadsheet kami dengan data yang tidak perlu. Untungnya, Google Spreadsheet menyediakan cara untuk hanya mengimpor kolom yang kita inginkan.

Melihat kolom pada tabel target kami, saya hanya tertarik pada beberapa kolom.

1. Pemain (Kolom 1)

2. Pass Yard (Kolom 2)

3. TD (Kolom 7)

4. INT (Kolom 8)

Kolom-kolom ini adalah semua data yang saya perlukan untuk membantu saya menentukan peringkat para pemain ini untuk musim mendatang.

Untuk membatasi kolom yang diimpor, kita perlu membungkus fungsi IMPORTHTML dalam fungsi kueri. Fungsi kueri mengambil dua parameter:

1. Data yang mewakili rentang sel untuk melakukan kueri.
2. Kueri untuk dijalankan, ditulis dalam Bahasa Kueri Google Visualization API.

Untuk parameter data, kami akan menggunakan seluruh fungsi IMPORTHTML yang telah kami buat. Untuk kueri, kami akan memberikan kueri yang menargetkan kolom yang ingin kami sertakan dalam impor kami.

=query(IMPORTHTML(“https://www.nfl.com/stats/player-stats/”, “tabel”, 1), “pilih Kol1, Kol2, Kol7, Kol8”)

Sekarang data yang kami impor terlihat seperti ini, tanpa kolom yang tidak perlu!

Jadi sekarang kita berhasil mengimpor kumpulan data yang diinginkan sambil juga membatasi kolom yang akan muncul di lembar akhir kita. Tapi masih ada pekerjaan yang harus dilakukan.

4. Filter Baris yang Diimpor Berdasarkan Kriteria Kustom

Untuk peringkat saya, saya hanya ingin mempertimbangkan quarterback yang melempar setidaknya 3000 yard. Secara umum, saya ingin memfilter baris yang diimpor berdasarkan kriteria di kolom tertentu.

Untuk membuat filter, kami akan menyesuaikan fungsi kueri yang ada untuk menyertakan klausa WHERE. Secara khusus, kami akan menunjukkan bahwa kami hanya ingin mengimpor baris di mana nilai dalam Col2 lebih besar dari atau sama dengan 3000.

=query(IMPORTHTML(“https://www.nfl.com/stats/player-stats/”, “tabel”, 1), “pilih Col1, Col2, Col7, Col8 di mana Col2 >= 3000”)

Sekarang, ketika kita memeriksa kumpulan hasil, kita dapat melihat bahwa Lamar Jackson (satu-satunya pemain dengan passing kurang dari 3000 yard) dihilangkan. Semoga lebih beruntung tahun depan!

Catatan: Lamar Jackson adalah pejantan dan kami biasanya memasukkannya ke dalam peringkat kami. Ini teori, guys!

5. Hasilkan Output Poin Fantasi Menggunakan Bidang Terhitung

Kami telah berhasil mengimpor statistik quarterback mentah kami, memangkas kolom sesuai keinginan kami, dan menyaring beberapa pemain yang tidak diinginkan. Itu adalah awal yang baik untuk membuat peringkat kustom kami.

Tetapi statistik mentah tidak terlalu membantu dalam hal olahraga fantasi. Yang benar-benar perlu kita lakukan adalah menghitung output poin fantasi untuk setiap pemain. Itu adalah indikator kesuksesan yang jauh lebih baik.

Dan tentu saja, kami dapat menambahkan kolom lain secara manual, melakukan perhitungan ini secara manual di dalam lembar kami. Tapi mari kita merampingkan hal-hal dengan melakukan perhitungan selama proses impor.

Mengembangkan formula untuk perhitungan poin fantasi

Jadi apa rumus yang kami gunakan untuk menghitung output poin fantasi untuk quarterback?

Ternyata formula ini akan bervariasi dari liga ke liga, berdasarkan konfigurasi penilaian spesifik Anda. Namun untuk tutorial ini, kita akan menggunakan rumus yang mirip dengan sistem penilaian standar:

Poin Fantasi = (Lulus Yds / 25) + (TD * 6) – (INT * 2)

Menyesuaikan kueri untuk menyertakan bidang terhitung

Untuk mengintegrasikan bidang terhitung, kami akan menyesuaikan kueri yang ada untuk menyertakan kolom terhitung. Secara khusus, kami ingin menambahkan penghitungan poin fantasi kami ke parameter kueri dari fungsi kueri.

=query(IMPORTHTML(“https://www.nfl.com/stats/player-stats/”, “tabel”, 1), “pilih Kol1, Kol2, Kol7, Kol8, (Kol2/25)+(Kol7* 6)-(Kol8*2) di mana Kol2 >= 3000”)

Sekarang spreadsheet kami berisi total output fantasi untuk setiap pemain (metrik yang jauh lebih membantu).

Tapi tajuk kolom baru itu terlihat sangat aneh. Akan jauh lebih bersih jika kita dapat menerapkan label khusus pada kolom ('Fantasy Points', misalnya) l. Ternyata ini mungkin, meskipun sintaksnya agak aneh.

Yang perlu kita lakukan adalah menambahkan lebih banyak data ke akhir kueri kita:

1. Kata kunci 'label'

2. Ulangi bidang yang dihitung

3. Header kolom yang diinginkan sebagai string

=query(IMPORTHTML(“https://www.nfl.com/stats/player-stats/”, “tabel”, 1), “pilih Kol1, Kol2, Kol7, Kol8, (Kol2/25)+(Kol7* 6)-(Col8*2) di mana Col2 >= 3000 label (Col2/25)+(Col7*6)-(Col8*2) 'Poin Fantasi'”)

Sekarang lebih seperti itu!

Kolom Poin Fantasi itu terlihat jauh lebih baik! Tapi saya rasa saya tidak membutuhkan bagian desimal dari angka-angka itu.

Bagi saya, angka desimal menambahkan kebisingan latar belakang yang tidak perlu. Jadi mari kita lihat apakah kita bisa mengumpulkan angka-angka itu.

Untuk mencapai ini, kami akan menerapkan format khusus untuk kueri kami. Secara khusus, kita perlu menentukan:

1. Kata kunci 'FORMAT'

2. Ulangi bidang yang dihitung

3. Format nomor khusus (“#” dalam kasus kami)

=query(IMPORTHTML(“https://www.nfl.com/stats/player-stats/”, “tabel”, 1), “pilih Kol1, Kol2, Kol7, Kol8, (Kol2/25)+(Kol7* 6)-(Kol8*2) di mana Kol2 >= 3000 label (Kol2/25)+(Kol7*6)-(Kol8*2) FORMAT 'Poin Fantasi' (Kol2/25)+(Kol7*6)-(Kol8 *2) '#'")

Sekarang meja kami terlihat lebih bersih.

Kami telah menempuh perjalanan panjang dan telah merampingkan proses pengumpulan data kami. Tapi kami memiliki satu langkah lagi untuk menyelesaikan tutorial ini.

Karena latihan ini bertujuan untuk membuat peringkat untuk para pemain ini, akan berguna untuk mengurutkannya saat kita mengimpor data.

Mari kita kerjakan selanjutnya.

6. Urutan Baris Berdasarkan Kolom Terhitung

Kami akan kembali bersandar pada kueri kami untuk menentukan urutan baris awal untuk data kami. Kami ingin mengurutkan baris kami berdasarkan kolom yang dihitung ('Poin Fantasi'). Tetapi kami ingin melakukan ini dalam urutan menurun.

Penyortiran dapat dilakukan melalui klausa ORDER BY DESC. Sekali lagi (dan sayangnya), kita harus merujuk ke kolom kalkulasi lengkap kita.

CATATAN: Anda tidak dapat menggunakan tajuk kolom bidang terhitung dalam klausa ORDER BY karena dalam urutan operasi SQL alias tidak diterapkan hingga setelah ORDER BY.

Inilah rumus terakhir kami:

=query(IMPORTHTML(“https://www.nfl.com/stats/player-stats/”, “tabel”, 1), “pilih Kol1, Kol2, Kol7, Kol8, (Kol2/25)+(Kol7* 6)-(Col8*2) dimana Col2 >= 3000 ORDER BY (Col2/25)+(Col7*6)-(Col8*2) label DESC (Col2/25)+(Col7*6)-(Col8*2 ) FORMAT 'Poin Fantasi' (Kol2/25)+(Kol7*6)-(Kol8*2) '#'”)

Kumpulan data terakhir kami sekarang dipesan dengan pemain terbaik terlebih dahulu (sesuai dengan aturan penilaian khusus liga kami).

Fitur Impor Data Google Spreadsheet Sederhana namun Efektif

Baik Anda seorang geek statistik, pelajar, atau peneliti, keserbagunaan fitur impor data di Google Spreadsheet sangat menghemat waktu. Dengan menggunakan fungsi yang dirinci dalam artikel ini, Anda sekarang memiliki kekuatan untuk mengimpor, memfilter, mengurutkan, dan memanipulasi lebih lanjut data apa pun yang dapat Anda temukan di Internet.

Dan dengan lebih dari 6 miliar halaman web (dan terus bertambah), sepertinya Anda akan sangat sibuk.