การวิเคราะห์ไฟล์ csv หลายไฟล์ ด้วย Power Query ของ Excel

 ไฟล์ CSV (commar seperated value) เป็นไฟล์ชนิดข้อความที่คั่นด้วยเครื่องหมายคอมม่า เราไม่สามารถเปิดได้โดยตรงจาก Excel ต้องนำเข้า ถ้ามีหลาย ๆ ไฟล์ ก็สามารถนำมาต่อกันวิเคราะห์คราวเดียวได้เลย แต่มีข้อแม้ว่า โครงสร้างต้องเหมือนกันด้วย

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

การนำเข้าข้อมูลจาก ไฟล์ CSV และปรับข้อมูลด้วย Power Query

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

  1. เปิดโปรแกรม Microsoft Excel 
  2.  ไปที่  ข้อมูล > จากไฟล์ > จากโฟลเดอร์ เราไม่ใช้ จากข้อความ/CSV เพราะเราจะนำเข้าหลายไฟล์ทีเดียวเลย
  3. ไปที่ห้องที่เก็บไฟล์ และเลือก จะมีหน้าจอเกิดขึ้นให้ระบุ โดยมีชื่อไฟล์ csv ในห้องทั้งหมด พร้อมทั้งมีปุ่มด้านล่างให้กำหนดลักษณะการเปิดที่ต้องการ 


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


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


  8. ที่ไอคอนหัวคอลัมน์ Transaction Date ให้คลิก (คลิกธรรมดา ปุ่มเมาส์ซ้าย) จะเปิดตัวเลือก และเลือกวันที่ 


  9. ทีนี้ ต้องดูว่าเป็นข้อมูลมาจากที่ไหนด้วย เพราะวันที่ นอกจากรูปแบบไม่เหมือนกันแล้ว (เช่น 2/5/2022 ของอเมริกา คือ วันที่ 5 เดือนกุมภาพันธ์ ปี 2565 แต่ถ้าเป็นของประเทศอังกฤษ จะหมายถึงวันที่ 2 เดือน พฤษภาคม ปี 2565 เป็นต้น) ปี ก็ไม่เหมือนของไทย เพราะเราใช้ พ.ศ. แต่ของฝรั่งใช้ ปี ค.ศ. ส่วนใหญ่ก็จะไม่มีปัญหา แต่ จะเกิดปัญหาตรงปีอธิกสุรทิน หรือ Leap year ซึ่งเดือนกุมภาพันธ์ มี 29 วัน อันนี้ก็จะไม่ตรงกัน เราจึงต้องเปลี่ยน วัน เดือนปี ให้ตรงกับของไทย
  10. Excel ใช้วันเดือนปี ที่กำหนดในเครื่องคอมของเรา ซึ่งส่วนใหญ่ก็จะตั้งเป็นประเทศไทย ข้อมูลที่อยู่ในไฟล์ Excel ก็จะถือว่าเป็น วันเดือนปีของไทย ตัวเลขปี ก็จะเป็น ปี พ.ศ. ซึ่งถ้าในไฟล์ csv เป็น 2022 Excel ก็จะนึกว่าเป็น พ.ศ. 2022 ซึ่งไม่ถูกต้อง
  11. แต่ Excel ก็คงเข้าใจปัญหานี้ จึงมีการกำหนดให้เราระบุว่า ข้อมูลมาจากที่ไหน ก็จะได้คิดวันเดือนปีให้ถูกต้อง 
  12. จะเห็นว่าเมื่อเราคลิกที่ไอคอน จะมีตัวเลือก กำลังใช้ภาษา (Using Locale) ซึ่งเป็นตัวเลือกที่ให้เราบอกว่า ข้อมูลจากไฟล์ CSV ที่จะนำมาใช้นี้ ภาษาอะไร มาจากที่ไหน 
  13. ให้คลิกเลือก ชนิดข้อมูล เป็นวันที่ และระบุตำแหน่งของข้อมูล ไฟล์นี้เป็นของ Google เป็นภาษาอังกฤษ มาจาก สหรัฐอเมริกา


  14.   Excel จะแสดงตัวอย่าง  รูปแบบวันที่ ให้คลิก ตกลง 


  15.  จะเห็นว่า ไอคอนหน้าคอลัมน์เปลี่ยนเป็นแบบวันที่ และจัดชิดขวา แสดงว่ามีข้อมูลเป็นตัวเลขเรียบร้อยแล้ว
  16. คลิก ปิด&โหลด เพื่อกลับ Excel และวิเคราะห์ต่อไป

การวิเคราะห์ด้วย PivotTable
  1. เมื่อคลิกปิด&โหลด จะได้ข้อมูลใน Sheet ดังภาพ


  2. ไปที่เมนู แทรก > PivotTable เลือก จากตาราง


  3. กำหนดให้ไปสรุปข้อมูลในอีก Sheet แล้วกด ตกลง จะเปิดหน้าต่างให้กำหนดค่าต่าง ๆ ตามต้องการ

  4. ในตัวอย่างไม่เห็นเขตข้อมูลมากนัก ผมจะปิดเมนูเพื่อให้มีพื้นที่มากขึ้น จะได้เห็นเขตข้อมูลที่จะนำมากำหนดการวเคราะห์ 

  5. ในกรณีนี้ จะรวมเงิน เป็นรายเดือน โดยจะใช้เขตวันที่คือ Transaction Date และ Amount ให้คลิกที่ Transaction Date โปรแกรม Excel ฉลาดมาก เขารู้ว่า จะต้องเอามาไว้ในช่องแถว เพื่อให้ข้อมูลวันที่เรียงลงมาตามแถว



  6. ต่อไปให้ Scroll หาเขตข้อมูล Amount ซึ่งเป็นจำนวนเงิน แล้วคลิกเลือก Excel ฉลาดมาก รู้ว่าเป็นตัวเลข ต้องเอามาคำนวณรวมกัน จึงเอาไปไว้ในช่อง การหาค่า 

  7. จะเห็นว่า Excel คำนวณให้เราเสร็จเรียบร้อยแล้ว จัดเป็นรายไตรมาส ถ้าเราไม่ต้องการรายไตรมาส แต่ให้แสดงเป็นเดือน ก็คลิกเลือกเอาไตรมาสออกไป


  8. ก็จะได้ สรุปข้อมูลเป็นรายเดือน ดังภาพ


  9. เป็นอันเสร็จการวิเคราะห์ข้อมูล

ความคิดเห็น

โพสต์ยอดนิยมจากบล็อกนี้

อุปมา อุปไมย สำนวนการเปรียบเทียบ ของไทย

ความสามารถทั่วไปด้านเหตุผล การหาความสัมพันธ์จาก ภาพ สัญลักษณ์

แนวข้อสอบ เงื่อนไขสัญลักษณ์