การวิเคราะห์ไฟล์ csv หลายไฟล์ ด้วย Power Query ของ Excel
ไฟล์ CSV (commar seperated value) เป็นไฟล์ชนิดข้อความที่คั่นด้วยเครื่องหมายคอมม่า เราไม่สามารถเปิดได้โดยตรงจาก Excel ต้องนำเข้า ถ้ามีหลาย ๆ ไฟล์ ก็สามารถนำมาต่อกันวิเคราะห์คราวเดียวได้เลย แต่มีข้อแม้ว่า โครงสร้างต้องเหมือนกันด้วย
ต่อไปนี้ จะนำไฟล์ข้อมูลจากอินเทอร์เน็ตมาวิเคราะห์ โดยมีหลายไฟล์ ซึ่งแต่ละไฟล์จะมีโครงสร้างเหมือนกัน และเก็บไว้ในห้องเดียวกัน ซึ่ง จะขอแบ่งออกเป็น 2 ตอน คือ นำเข้าและปรับข้อมูลด้วย Power Query และ วิเคราะห์ด้วย PivotTable ซึ่งมีวิธีการดังนี้
การนำเข้าข้อมูลจาก ไฟล์ CSV และปรับข้อมูลด้วย Power Query
Microsoft Excel ตั้งแต่รุ่น 2016 เป็นต้นมา และ Excel 365 จะมี Power Query เพิ่มเข้ามาด้วย ทำให้การวิเคราะห์ข้อมูลทำได้สะดวกดีขึ้นมาก เรานำมาใช้ได้ ดังนี้
- เปิดโปรแกรม Microsoft Excel
- ไปที่ ข้อมูล > จากไฟล์ > จากโฟลเดอร์ เราไม่ใช้ จากข้อความ/CSV เพราะเราจะนำเข้าหลายไฟล์ทีเดียวเลย
- ไปที่ห้องที่เก็บไฟล์ และเลือก จะมีหน้าจอเกิดขึ้นให้ระบุ โดยมีชื่อไฟล์ csv ในห้องทั้งหมด พร้อมทั้งมีปุ่มด้านล่างให้กำหนดลักษณะการเปิดที่ต้องการ
- ให้เลือก > รวม > แปลงข้อมูล เพราะถ้าหากต้องการแก้ไข เข่น ปรับรูปแบบ เปลี่ยนข้อความ หรือ อื่น ๆ ก็จะสามารทำได้
- Excel จะเอาตัวอย่างลักษณะตามที่กำหนดมาแสดงให้ดูว่า OK หรือไม่ ถ้าเป็นไปตามที่คิดก็กด ตกลง ถ้าไม่ใช่ก็กด ยกเลิก ในตัวอย่างนี้จะกด ตกลง
- Excel จะแสดงหน้าต่างของ Power Query ซึ่งเราจะสามารถแก้ไขเปลี่ยนแปลง เพิ่มคอลัมน์ คำนวณ บวกลบตัวเลข หรือ เปลี่ยนแปลงวันที่ และอื่น ๆ ได้จากที่นี่
- ในคอลัมน์วันที่ (Transaction date) จะเห็นว่า ข้อมูลมีลักษณะเป็นข้อความ เพราะมาจากไฟล์ CSV จะเห็นได้จากข้อความที่จัดชิดขวา และไอคอนแสดงว่าเป็นตัวอักษร เราควรแปลงเป็น ข้อมูลแบบวันที่ เพื่อว่า ถ้าต้องการคำนวณ บวกลบหาจำนวนวัน หรืออื่น ๆ ก็จะสามารถทำได้
- ที่ไอคอนหัวคอลัมน์ Transaction Date ให้คลิก (คลิกธรรมดา ปุ่มเมาส์ซ้าย) จะเปิดตัวเลือก และเลือกวันที่
- ทีนี้ ต้องดูว่าเป็นข้อมูลมาจากที่ไหนด้วย เพราะวันที่ นอกจากรูปแบบไม่เหมือนกันแล้ว (เช่น 2/5/2022 ของอเมริกา คือ วันที่ 5 เดือนกุมภาพันธ์ ปี 2565 แต่ถ้าเป็นของประเทศอังกฤษ จะหมายถึงวันที่ 2 เดือน พฤษภาคม ปี 2565 เป็นต้น) ปี ก็ไม่เหมือนของไทย เพราะเราใช้ พ.ศ. แต่ของฝรั่งใช้ ปี ค.ศ. ส่วนใหญ่ก็จะไม่มีปัญหา แต่ จะเกิดปัญหาตรงปีอธิกสุรทิน หรือ Leap year ซึ่งเดือนกุมภาพันธ์ มี 29 วัน อันนี้ก็จะไม่ตรงกัน เราจึงต้องเปลี่ยน วัน เดือนปี ให้ตรงกับของไทย
- Excel ใช้วันเดือนปี ที่กำหนดในเครื่องคอมของเรา ซึ่งส่วนใหญ่ก็จะตั้งเป็นประเทศไทย ข้อมูลที่อยู่ในไฟล์ Excel ก็จะถือว่าเป็น วันเดือนปีของไทย ตัวเลขปี ก็จะเป็น ปี พ.ศ. ซึ่งถ้าในไฟล์ csv เป็น 2022 Excel ก็จะนึกว่าเป็น พ.ศ. 2022 ซึ่งไม่ถูกต้อง
- แต่ Excel ก็คงเข้าใจปัญหานี้ จึงมีการกำหนดให้เราระบุว่า ข้อมูลมาจากที่ไหน ก็จะได้คิดวันเดือนปีให้ถูกต้อง
- จะเห็นว่าเมื่อเราคลิกที่ไอคอน จะมีตัวเลือก กำลังใช้ภาษา (Using Locale) ซึ่งเป็นตัวเลือกที่ให้เราบอกว่า ข้อมูลจากไฟล์ CSV ที่จะนำมาใช้นี้ ภาษาอะไร มาจากที่ไหน
- ให้คลิกเลือก ชนิดข้อมูล เป็นวันที่ และระบุตำแหน่งของข้อมูล ไฟล์นี้เป็นของ Google เป็นภาษาอังกฤษ มาจาก สหรัฐอเมริกา
- Excel จะแสดงตัวอย่าง รูปแบบวันที่ ให้คลิก ตกลง
- จะเห็นว่า ไอคอนหน้าคอลัมน์เปลี่ยนเป็นแบบวันที่ และจัดชิดขวา แสดงว่ามีข้อมูลเป็นตัวเลขเรียบร้อยแล้ว
- คลิก ปิด&โหลด เพื่อกลับ Excel และวิเคราะห์ต่อไป
การวิเคราะห์ด้วย PivotTable
- เมื่อคลิกปิด&โหลด จะได้ข้อมูลใน Sheet ดังภาพ
- ไปที่เมนู แทรก > PivotTable เลือก จากตาราง
- กำหนดให้ไปสรุปข้อมูลในอีก Sheet แล้วกด ตกลง จะเปิดหน้าต่างให้กำหนดค่าต่าง ๆ ตามต้องการ
- ในตัวอย่างไม่เห็นเขตข้อมูลมากนัก ผมจะปิดเมนูเพื่อให้มีพื้นที่มากขึ้น จะได้เห็นเขตข้อมูลที่จะนำมากำหนดการวเคราะห์
- ในกรณีนี้ จะรวมเงิน เป็นรายเดือน โดยจะใช้เขตวันที่คือ Transaction Date และ Amount ให้คลิกที่ Transaction Date โปรแกรม Excel ฉลาดมาก เขารู้ว่า จะต้องเอามาไว้ในช่องแถว เพื่อให้ข้อมูลวันที่เรียงลงมาตามแถว
- ต่อไปให้ Scroll หาเขตข้อมูล Amount ซึ่งเป็นจำนวนเงิน แล้วคลิกเลือก Excel ฉลาดมาก รู้ว่าเป็นตัวเลข ต้องเอามาคำนวณรวมกัน จึงเอาไปไว้ในช่อง การหาค่า
- จะเห็นว่า Excel คำนวณให้เราเสร็จเรียบร้อยแล้ว จัดเป็นรายไตรมาส ถ้าเราไม่ต้องการรายไตรมาส แต่ให้แสดงเป็นเดือน ก็คลิกเลือกเอาไตรมาสออกไป
- ก็จะได้ สรุปข้อมูลเป็นรายเดือน ดังภาพ
- เป็นอันเสร็จการวิเคราะห์ข้อมูล
ความคิดเห็น
แสดงความคิดเห็น