Excel มีตัวดึงข้อมูลเว็บในตัวอยู่แล้ว นี่คือสิ่งที่ฉันใช้มันทำ

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

แม้ว่าจะฟังดูซับซ้อน แต่คุณไม่จำเป็นต้องใช้ Python ส่วนขยายของเบราว์เซอร์ หรือความรู้ด้านการเขียนโปรแกรมใดๆ ตราบใดที่คุณสามารถเข้าถึง Power Query ในแอป Excel ของคุณได้ ซึ่งคุณควรสามารถทำได้หากคุณใช้ Excel 2016 หรือรุ่นที่ใหม่กว่า คุณสามารถดึงข้อมูลแทบทุกอย่างได้ ตั้งแต่ตารางสินค้าที่เรียบง่ายไปจนถึงฟีด API แบบสด

ใช้ Power Query เพื่อดึงตาราง ไฟล์ และ API เข้าสู่สเปรดชีตของคุณ

ไม่ต้องใช้โค้ดหรือเครื่องมือเก็บข้อมูลจากภายนอก

จุดเริ่มต้นที่ดีที่สุดสำหรับการดึงข้อมูลจากเว็บใน Excel คือ ตัวเลือก From Web ใน Power Query ซึ่งคุณสามารถหาได้ในกลุ่ม Get & Transform Data บนแท็บ Data วาง URL ลงไป และ Excel จะพยายามตรวจจับตารางใด ๆ บนหน้าเว็บโดยอัตโนมัติ เมื่อมันทำงานได้ คุณสามารถดึงข้อมูลที่สะอาดและปรับปรุงได้เข้ามาในสเปรดชีตของคุณได้เพียงไม่กี่คลิก แน่นอนว่า เว็บไซต์ไม่ได้เป็นมิตรแบบนั้นเสมอไป

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

กระบวนการเริ่มต้นในลักษณะเดียวกับการนำเข้าจากเว็บแบบมาตรฐาน ในกลุ่ม Get & Transform Data บนแท็บ Data ให้เลือก From Web ใส่ URL ของเว็บเพจในกล่องโต้ตอบที่ปรากฏขึ้น และคลิก OK หาก Excel เตือนให้คุณเลือกวิธีการยืนยันตัวตน Anonymous มักจะเป็นตัวเลือกเริ่มต้นและเพียงแค่ต้องคลิก Connect เท่านั้น

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

ฉันได้ทดสอบสิ่งนี้กับหน้าประวัติรุ่นของวิกิพีเดียเพราะฉันต้องการให้มีคอลัมน์หนึ่งที่แสดงรุ่นต่าง ๆ และอีกคอลัมน์หนึ่งที่มีช่วงปีและคำอธิบาย หลังจากเพิ่มคอลัมน์เพิ่มเติมแล้ว ฉันพิมพ์ว่า “Lost Generation” และ “1883 ถึง 1900” จากนั้นเลือกคำแนะนำที่ตรงกันจากเมนูแบบเลื่อนลง ฉันทำกระบวนการเดียวกันสำหรับ Greatest Generation และ Excel ก็กรอกข้อมูลรุ่นที่เหลือให้โดยอัตโนมัติ สิ่งที่ฉันต้องทำหลังจากนั้นคือแทรกตารางเข้าสู่เวิร์กชีตของฉัน

Power Query ยังไปไกลกว่าการดึงข้อมูลจากหน้า HTML มาตรฐาน คุณสามารถเชื่อมต่อโดยตรงกับ URL ของไฟล์ รวมถึงไฟล์ CSV ที่โฮสต์บน GitHub, การส่งออก JSON จากระบบภายใน หรือฟีด XML และ Excel จะทำการแยกวิเคราะห์เนื้อหาเป็นตารางที่มีโครงสร้างให้อัตโนมัติ นอกจากนี้ยังรองรับการเชื่อมต่อ API ที่ต้องมีการยืนยันตัวตน ซึ่งหมายความว่าคุณสามารถทำงานกับบริการที่ต้องใช้ API key ได้โดยไม่จำเป็นต้องใช้เครื่องมือดึงข้อมูลแยกต่างหาก

ภายใต้ระบบภายใน Power Query พึ่งพาฟังก์ชันสามแบบที่แตกต่างกัน ขึ้นอยู่กับประเภทของแหล่งข้อมูลที่คุณกำลังเชื่อมต่อ:

สำหรับเวิร์กโฟลว์สมัยใหม่ส่วนใหญ่ คุณจะทำงานส่วนใหญ่กับ Web.Contents และ Web.BrowserContents

สร้างคำค้นเว็บที่ชาญฉลาดยิ่งขึ้น

URL แบบไดนามิก พารามิเตอร์ที่ขับเคลื่อนด้วยสเปรดชีต และเทคนิคบางอย่างสำหรับเว็บไซต์ที่ไม่ทำงานอย่างราบรื่น

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

ในกล่องโต้ตอบ From Web ให้เลือกตัวเลือก Advanced แทน Basic จากนั้น ใช้ส่วนของ URL parts เพื่อสร้างที่อยู่ทีละชิ้น ในช่องข้อความแรก ให้ป้อนส่วนที่คงที่ของ URL เช่น scheme และ path:

https://en.wikipedia.org/wiki/

จากนั้นคุณสามารถเลือก เพิ่มส่วน เพื่อสร้างส่วนเพิ่มเติมสำหรับชิ้นส่วนคำถามหรือค่าตัวแปร เช่น รุ่นเฉพาะหรือประเทศ ตัวอย่างเช่น คุณสามารถตั้งค่า URL ส่วนที่ 2 เป็นพารามิเตอร์ที่ชื่อว่า Topic โดยมีค่าเป็น Generation จากนั้นตั้งค่า ส่วนที่ 3 เป็น #Social_generation ขณะที่คุณสร้างคำถาม Excel จะสร้างตัวอย่างแบบสดของ URL สุดท้ายให้

หลังจากเลือก Transform Data ในหน้าต่าง Navigator คุณจะเข้าสู่ Power Query Editor ซึ่งเป็นที่ที่คุณสามารถจัดการพารามิเตอร์เหล่านั้นได้อย่างมีประสิทธิภาพมากขึ้น ภายใต้แท็บ Home ให้เลือก Manage Parameters เพื่อสร้างค่าที่แก้ไขได้ซึ่งสามารถนำกลับมาใช้ซ้ำในคำสืบค้นของคุณได้ ซึ่งหมายความว่าคุณสามารถเปลี่ยนพารามิเตอร์ Topic จาก Generation เป็น Zillennials ในภายหลัง และตารางในเวิร์กชีตของคุณจะรีเฟรชโดยอัตโนมัติด้วยข้อมูลจากหน้านั้นแทน

คุณสามารถขยายสิ่งนี้ต่อไปได้โดยการเชื่อมต่อคำสืบค้นโดยตรงกับค่าที่เก็บไว้ในสเปรดชีตของคุณ วิธีนี้ทำงานได้ดีเป็นพิเศษเมื่อคุณมีรายการระบุยาว ๆ เช่น หมายเลข NPI ของผู้ให้บริการด้านสุขภาพ รหัสลูกค้า หรือรหัสสินค้า ภายใน Power Query Editor ให้ไปที่แท็บ Add Column และเลือก Custom Column ที่นั่นคุณสามารถเขียนสูตรที่รวม URL พื้นฐานกับค่าที่ดึงมาจากคอลัมน์ในแผ่นงานของคุณได้

ตัวอย่างเช่น ถ้าคุณมีคอลัมน์ที่มีคำศัพท์ทางสังคมวิทยาเช่น Demographic_cohort หรือ Cultural_generation คุณสามารถใช้สูตรแบบนี้:

=Web.Page(Web.Contents("https://en.wikipedia.org/wiki/" & Text.Replace(Text.Trim([Column1]), " ", "_"))){0}[Data] 

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

หากคุณยังไม่สามารถดึงข้อมูลที่คุณต้องการจากเว็บเพจได้ เครื่องมือสำหรับนักพัฒนาของ Chrome (หรือตัวอื่นใด) ของคุณสามารถช่วยได้ เปิดแท็บ Network, โหลดหน้าใหม่ และสังเกตคำขอที่โหลดอยู่เบื้องหลัง เว็บไซต์สมัยใหม่จำนวนมากเติมเนื้อหาของหน้าผ่าน API แบบ JSON ภายใน ซึ่งมักจะง่ายต่อการใช้งานมากกว่าหน้าเว็บเอง คุณอาจพบ endpoint ที่ดูเหมือนกับสิ่งนี้:

https://en.wikipedia.org/w/api.php?action=parse&page=Generation&format=json

การเชื่อมต่อ Power Query โดยตรงกับ API endpoint แบบนั้น แทนที่จะดึงข้อมูลจากหน้าเว็บที่มองเห็น มักจะให้ผลลัพธ์ที่เร็วขึ้น สะอาดขึ้น และเชื่อถือได้มากขึ้น

ควรค่าแก่การเรียนรู้ แม้ว่าคุณจะไม่เคยขูดอะไรเลย

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

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