ใช้ความสามารถในการนำเข้าของ Google ชีตเพื่อให้เกิดประสิทธิผลมากขึ้น

ในสหรัฐอเมริกา กีฬาเป็น ธุรกิจขนาดใหญ่ ผู้คนประมาณ 154 ล้านคนดูกีฬาสดอย่างน้อยเดือนละครั้ง และคาดว่าตัวเลขเหล่านี้จะเพิ่มขึ้นทุกปี

ด้วยการเฟื่องฟูของกีฬา อุตสาหกรรมกระท่อมเช่นกีฬาแฟนตาซี DFS และไซต์เดิมพันอุปกรณ์ประกอบฉากทำให้แฟน ๆ อีกเหตุผลในการรับชม (และมีส่วนร่วมมากขึ้น) เกมเหล่านี้ช่วยให้ผู้ชมที่ฉลาดได้รับเงินก้อนโตด้วยการเอาชนะผู้เล่นอื่น (และหนังสือกีฬา) เพื่อชนะการแข่งขันออนไลน์ที่ร่ำรวย

วิธีหลักที่นักเล่นเกมแฟนตาซีจะได้เปรียบคือการใช้อัลกอริธึมที่เป็นกรรมสิทธิ์เพื่อฉายภาพการแสดงของผู้เล่น การนวดสถิติผู้เล่นช่วยให้นักเล่นเกมค้นหาอัญมณีทางสถิติที่คลุมเครือซึ่งพวกเขาสามารถใช้ประโยชน์ได้เพื่อชนะการแข่งขันมากขึ้น

วันนี้ เราจะมาดูวิธีการทำสิ่งนี้ให้สำเร็จโดยใช้ Google ชีต เราจะเตรียมร่างแฟนตาซีสมมุติโดยการนำเข้าสถิติผู้เล่นจากเว็บไซต์สาธารณะ จากนั้น เราจะใช้พลังของฟังก์ชันในการแปลง จัดการ กรอง และจัดเรียงข้อมูลนี้ตามความชอบของเรา

wpchtmlp id=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/”, “ตาราง”, 1)

หลังจากที่เราป้อนสูตรลงใน A1 แล้ว ข้อมูลจะปรากฏดังนี้

เยี่ยมมาก ง่ายมาก! แต่เราสามารถใช้คุณลักษณะของ Google ชีตได้มากขึ้นเพื่อปรับปรุงกระบวนการนี้ ฉันเห็นข้อบกพร่องหลายประการกับกระบวนการปัจจุบันของเรา:

1. เราไม่ต้องการคอลัมน์ทั้งหมดจากตารางต้นฉบับ ฉันต้องการจำกัดข้อมูลของฉันไว้เฉพาะฟิลด์ที่ฉันต้องการเพื่อสร้างการจัดอันดับของฉัน
2. ฉันอาจต้องการระบุเกณฑ์ขั้นต่ำที่ผู้เล่นต้องปฏิบัติตามก่อนที่จะนำเข้า (ฉันมีมาตรฐาน!)
3. ฉันต้องการคอลัมน์เพิ่มเติมเพียงคอลัมน์เดียวที่สรุปว่าผู้เล่นทำได้ดีเพียงใดตลอดทั้งฤดูกาล
4. เนื่องจากผมจัดอันดับผู้เล่นเหล่านี้ ผู้เล่นที่ดีที่สุดควรอยู่ในอันดับต้นๆ

มาดูกันว่าเราจะบรรลุเป้าหมายเหล่านี้ได้อย่างไร

3. จำกัดคอลัมน์ที่คุณต้องการนำเข้า

ไม่มีเหตุผลที่จะทำให้สเปรดชีตของเรายุ่งเหยิงด้วยข้อมูลที่ไม่จำเป็น โชคดีที่ Google ชีตมีวิธีนำเข้าเฉพาะคอลัมน์ที่เราต้องการเท่านั้น

เมื่อดูคอลัมน์ในตารางเป้าหมายแล้ว ฉันสนใจเพียงไม่กี่คอลัมน์เท่านั้น

1. ผู้เล่น (คอลัมน์ 1)

2. ผ่านหลา (คอลัมน์ 2)

3. ทีดี (คอลัมน์ 7)

4. INT (คอลัมน์ 8)

คอลัมน์เหล่านี้เป็นข้อมูลทั้งหมดที่ฉันต้องการเพื่อช่วยจัดอันดับผู้เล่นเหล่านี้สำหรับฤดูกาลที่จะมาถึง

ในการจำกัดคอลัมน์ที่นำเข้า เราจำเป็นต้องรวมฟังก์ชัน IMPORTHTML ของเราไว้ในฟังก์ชันการสืบค้น ฟังก์ชันแบบสอบถามใช้สองพารามิเตอร์:

1. ข้อมูลที่แสดงถึงช่วงของเซลล์ที่จะทำแบบสอบถาม
2. คิวรีที่จะดำเนินการ เขียนด้วย Google Visualization API Query Language

สำหรับพารามิเตอร์ข้อมูล เราจะใช้ฟังก์ชัน IMPORTHTML ทั้งหมดที่เราสร้างไว้แล้ว สำหรับข้อความค้นหา เราจะจัดเตรียมข้อความค้นหาที่กำหนดเป้าหมายคอลัมน์ที่เราต้องการรวมไว้ในการนำเข้าของเรา

=query(IMPORTHTML(“https://www.nfl.com/stats/player-stats/”, “table”, 1), “select Col1, Col2, Col7, Col8”)

ตอนนี้ข้อมูลที่นำเข้าของเรามีลักษณะเช่นนี้ โดยไม่มีคอลัมน์ที่ไม่จำเป็น!

ดังนั้นตอนนี้เราสามารถนำเข้าชุดข้อมูลที่ต้องการได้สำเร็จพร้อมทั้งจำกัดคอลัมน์ที่จะปรากฏในแผ่นงานสุดท้ายของเรา แต่ก็ยังมีงานต้องทำ

4. กรองแถวที่นำเข้าตามเกณฑ์ที่กำหนดเอง

สำหรับอันดับของฉัน ฉันต้องการพิจารณาแค่ควอเตอร์แบ็คที่ขว้างได้อย่างน้อย 3000 หลา โดยทั่วไป ฉันต้องการกรองแถวที่นำเข้าตามเกณฑ์ในคอลัมน์เฉพาะ

ในการสร้างตัวกรอง เราจะปรับฟังก์ชันการสืบค้นที่มีอยู่เพื่อรวมส่วนคำสั่ง WHERE โดยเฉพาะอย่างยิ่ง เราจะระบุว่าเราต้องการนำเข้าเฉพาะแถวที่ค่าใน Col2 มากกว่าหรือเท่ากับ 3000

=query(IMPORTHTML(“https://www.nfl.com/stats/player-stats/”, “table”, 1), “select Col1, Col2, Col7, Col8 where Col2 >= 3000”)

เมื่อเราตรวจสอบชุดผลการแข่งขัน เราจะเห็นว่าลามาร์ แจ็คสัน (ผู้เล่นเพียงคนเดียวที่ผ่านน้อยกว่า 3000 หลา) ถูกละเว้น ปีหน้าโชคดีกว่า!

หมายเหตุ: Lamar Jackson เป็นสตั๊ด และโดยปกติเราจะรวมเขาไว้ในการจัดอันดับของเรา นี่เป็นทฤษฎีคน!

5. สร้างผลลัพธ์ Fantasy Point โดยใช้ฟิลด์ที่คำนวณ

เรานำเข้าสถิติกองหลังดิบของเราได้สำเร็จ ตัดแต่งคอลัมน์ตามความชอบของเรา และกรองผู้เล่นที่ไม่ต้องการออก นั่นเป็นจุดเริ่มต้นที่ดีในการสร้างการจัดอันดับที่กำหนดเองของเรา

แต่สถิติดิบไม่เป็นประโยชน์เมื่อพูดถึงกีฬาแฟนตาซี สิ่งที่เราต้องทำคือคำนวณเอาท์พุตแฟนตาซีสำหรับผู้เล่นแต่ละคน นั่นเป็นตัวบ่งชี้ความสำเร็จที่ดีกว่ามาก

และแน่นอนว่า เราสามารถเพิ่มคอลัมน์อื่นด้วยตนเอง โดยดำเนินการคำนวณนี้ด้วยตนเองภายในชีตของเรา แต่เรามาปรับปรุงสิ่งต่าง ๆ ด้วยการคำนวณระหว่างกระบวนการนำเข้าแทน

พัฒนาสูตรคำนวณแต้มแฟนตาซี

แล้วสูตรที่เราใช้ในการคำนวณเอาท์พุตแฟนตาซีสำหรับควอเตอร์แบ็คคืออะไร?

ปรากฎว่าสูตรนี้จะแตกต่างกันไปในแต่ละลีก โดยขึ้นอยู่กับการกำหนดค่าคะแนนเฉพาะของคุณ แต่สำหรับบทช่วยสอนนี้ เราจะใช้สูตรที่คล้ายกับระบบการให้คะแนนมาตรฐาน:

Fantasy Points = (ผ่าน Yds / 25) + (TDs * 6) – (INTs * 2)

การปรับคิวรีเพื่อรวมเขตข้อมูลจากการคำนวณ

ในการรวมเขตข้อมูลจากการคำนวณ เราจะปรับการสืบค้นที่มีอยู่ของเราให้รวมคอลัมน์จากการคำนวณ โดยเฉพาะอย่างยิ่ง เราต้องการเพิ่มการคำนวณคะแนนแฟนตาซีของเราในพารามิเตอร์การสืบค้นของฟังก์ชันการสืบค้น

=query(IMPORTHTML(“https://www.nfl.com/stats/player-stats/”, “table”, 1), “select Col1, Col2, Col7, Col8, (Col2/25)+(Col7*) 6)-(Col8*2) โดยที่ Col2 >= 3000”)

ตอนนี้สเปรดชีตของเรามีเอาต์พุตแฟนตาซีทั้งหมดสำหรับผู้เล่นแต่ละคน (เมตริกที่เป็นประโยชน์มากขึ้น)

แต่ส่วนหัวของคอลัมน์ใหม่นั้นดูแปลกจริงๆ มันจะสะอาดกว่านี้มากถ้าเราสามารถติดป้ายกำกับที่กำหนดเองกับคอลัมน์ (เช่น 'คะแนนแฟนตาซี') l ปรากฎว่าเป็นไปได้แม้ว่าไวยากรณ์จะค่อนข้างแปลก

สิ่งที่เราต้องทำคือเพิ่มข้อมูลเพิ่มเติมต่อท้ายแบบสอบถามของเรา:

1. คีย์เวิร์ด 'label'

2. ทำซ้ำช่องที่คำนวณได้

3. ส่วนหัวของคอลัมน์ที่ต้องการเป็นสตริง

=query(IMPORTHTML(“https://www.nfl.com/stats/player-stats/”, “table”, 1), “select Col1, Col2, Col7, Col8, (Col2/25)+(Col7*) 6)-(Col8*2) โดยที่ Col2 >= 3000 label (Col2/25)+(Col7*6)-(Col8*2) 'Fantasy Points'”)

ตอนนี้เป็นเหมือนมากขึ้น!

คอลัมน์ Fantasy Points นั้นดูดีขึ้นมาก! แต่ฉันไม่คิดว่าฉันต้องการทศนิยมของตัวเลขเหล่านั้น

สำหรับฉัน ตัวเลขทศนิยมจะเพิ่มเสียงพื้นหลังที่ไม่จำเป็น ลองดูว่าเราสามารถปัดเศษตัวเลขเหล่านั้นได้หรือไม่

เพื่อให้บรรลุสิ่งนี้ เราจะใช้รูปแบบที่กำหนดเองกับข้อความค้นหาของเรา โดยเฉพาะอย่างยิ่ง เราต้องระบุ:

1. คีย์เวิร์ด 'FORMAT'

2. ทำซ้ำช่องที่คำนวณได้

3. รูปแบบตัวเลขที่กำหนดเอง (“#” ในกรณีของเรา)

=query(IMPORTHTML(“https://www.nfl.com/stats/player-stats/”, “table”, 1), “select Col1, Col2, Col7, Col8, (Col2/25)+(Col7*) 6)-(Col8*2) โดยที่ Col2 >= 3000 label (Col2/25)+(Col7*6)-(Col8*2) 'Fantasy Points' FORMAT (Col2/25)+(Col7*6)-(Col8 *2) '#'”)

ตอนนี้โต๊ะของเราดูสะอาดขึ้น

เรามาไกลและทำให้กระบวนการรวบรวมข้อมูลของเราคล่องตัวขึ้น แต่เรามีอีกหนึ่งขั้นตอนในการทำให้บทแนะนำนี้เสร็จสมบูรณ์

เนื่องจากแบบฝึกหัดนี้มีจุดมุ่งหมายเพื่อสร้างการจัดอันดับสำหรับผู้เล่นเหล่านี้ การจัดเรียงข้อมูลเหล่านี้เมื่อเรานำเข้าข้อมูลจะเป็นประโยชน์

มาทำงานกันต่อไป

6. สั่งซื้อแถวตามคอลัมน์จากการคำนวณ

เราจะใช้การสืบค้นข้อมูลอีกครั้งเพื่อระบุลำดับแถวเริ่มต้นสำหรับข้อมูลของเรา เราต้องการจัดเรียงแถวตามคอลัมน์จากการคำนวณ ('คะแนนแฟนตาซี') แต่เราต้องการทำสิ่งนี้ในลำดับจากมากไปน้อย

การเรียงลำดับสามารถทำได้โดยใช้คำสั่งย่อย ORDER BY DESC อีกครั้ง (และน่าเสียดาย) เราจะต้องอ้างอิงฟิลด์ที่คำนวณทั้งหมดของเรา

หมายเหตุ: คุณไม่สามารถใช้ส่วนหัวของคอลัมน์ของฟิลด์ที่คำนวณได้ในส่วนคำสั่ง ORDER BY เนื่องจากในลำดับการดำเนินการของ SQL นามแฝงจะไม่ถูกนำมาใช้จนกว่าจะถึง ORDER BY

นี่คือสูตรสุดท้ายของเรา:

=query(IMPORTHTML(“https://www.nfl.com/stats/player-stats/”, “table”, 1), “select Col1, Col2, Col7, Col8, (Col2/25)+(Col7*) 6)-(Col8*2) โดยที่ Col2 >= 3000 ORDER BY (Col2/25)+(Col7*6)-(Col8*2) DESC label (Col2/25)+(Col7*6)-(Col8*2 ) 'คะแนนแฟนตาซี' (Col2/25)+(Col7*6)-(Col8*2) '#'”)

ตอนนี้ชุดข้อมูลสุดท้ายของเราได้รับคำสั่งจากผู้เล่นที่ดีที่สุดก่อน (ตามกฎการให้คะแนนเฉพาะของลีกของเรา)

คุณลักษณะการนำเข้าข้อมูลของ Google ชีตนั้นเรียบง่ายแต่มีประสิทธิภาพ

ไม่ว่าคุณจะเป็นพวกคลั่งไคล้สถิติ นักเรียน หรือนักวิจัย ความเก่งกาจของคุณลักษณะการนำเข้าข้อมูลใน Google ชีตช่วยประหยัดเวลาได้มาก เมื่อใช้ฟังก์ชันที่มีรายละเอียดในบทความนี้ คุณจะมีอำนาจในการนำเข้า กรอง จัดเรียง และจัดการข้อมูลใดๆ ที่คุณพบบนอินเทอร์เน็ตเพิ่มเติม

และด้วยหน้าเว็บมากกว่า 6 พันล้านหน้า (และเพิ่มขึ้นเรื่อยๆ) ดูเหมือนว่าคุณจะยุ่งมาก