>>> การสร้างแบบสอบถามออนไลน์ด้วย Google Forms พร้อมสร้าง QR Code <<< การจัดการข้อมูลจาก Google Forms สู่ Excel
[ 1 / 14 ]
1. กดที่แบบฟอร์ม
[ 2 / 14 ]
1. กดที่ "การตอบกลับ" ( หมายเลขด้านหลังคือจำนวนของผู้ตอบแบบสอบถาม)
[ 3 / 14 ]
1. กดที่ปุ่มนี้ เพื่อสร้างสเปรตชีต
[ 4 / 14 ]
1. กดปุ่มนี้ เพื่อสร้างสเปรตชีตใหม่
2. กดปุ่ม "สร้าง"
[ 5 / 14 ]
1. กดปุ่มนี้เพื่อเข้าสู่การดาวน์โหลด
[ 6 / 14 ]
1. กดปุ่มนี้เพื่อดาวน์โหลดไฟล์ .csv
[ 7 / 14 ]
1. ไปยังที่อยู่ในการจัดเก็บไฟล์ที่ดาวน์โหลดมา
2. คลิ๊กขวาที่ไฟล์
3. เลือก Extract Here เพื่อแตกไฟล์จาก Winrar ( ในกรณีนี้ใช้ตัวบีบอัดด้วยโปรแกรม Winrar )
[ 8 / 14 ]
1. เมื่อทำการ Extract Here ออกมาแล้ว จะได้ไฟล์ดังภาพ
[ 9 / 14 ]
1. เปิดโปรแกรม Excel และไปที่ Tab DATA
2. กดที่นี้ ค้นหาที่เก็บไฟล์
3. เลือกไฟล์
4. กดปุ่มนี้ เพื่อนำเข้าข้อมูล
[ 10 / 14 ]
1. กดปุ่มนี้ เพื่อจัดการข้อมูล
2. กดปุ่ม "Next"
[ 11 / 14 ]
1. กดปุ่มนี้ เพื่อเลือกตัวคั่นข้อมูลแบบ Tab
2. กดปุ่มนี้ เพื่อเลือกตัวคั่นข้อมูลแบบ Comma
3. กดปุ่ม "Next"
[ 12 / 14 ]
1. กดปุ่ม "Finish"
[ 13 / 14 ]
1. กดปุ่ม "OK"
[ 14 / 14 ]
1. จะได้ข้อมูลการตอบแบบสอบทั้งหมดในรูปแบบไฟล์ Excel จากนั้นก็สามารถนำข้อมูลไปจัดการตามที่เราต้องการ
การหาผลลัพธ์ด้วยสูตร COUNTIFS
สูตร COUNTIFS คือการระบุเงื่อนไข เพื่อนำไปค้นหาภายในเซลล์และนับจำนวนในเซลล์นั้นที่ตรงกับเงื่อนไข
1. พิมพ์สูตร =COUNTIFS(D2:D5,"ปานกลาง") แล้วกดปุ่ม Enter ที่คีย์บอร์ด
2. อธิบายสูตร D2 และ D5 ตัว D คือ แถว D
3. อธิบายสูตร D2 และ D5 เลข 2 และ 5 คือ คอลั่มที่ 2 ถึง คอลั่มที่ 5
4.
ผลลัพธ์จากการใช้สูตร
บทความนี้จะอธิบายวิธีการใช้ Power Query เชื่อมกับ Google Form/Google Sheets โดยที่เราสามารถกด Refresh ที่ Excel/Power BI เพื่อดูดข้อมูลจาก Google Form/Google Sheets ณ ตอนนั้นได้เลย
ซึ่งเป็นเรื่องที่ผมคิดว่ามีประโยชน์มากๆ วิธีทำจะเป็นยังไงมาดูกัน (อันนี้ไม่มีในหนังสือนะ แต่ผมทำบทความให้อ่านกันฟรีๆ เลยครับ 555)
ก่อนอื่นก็ไปสร้างฟอร์มใน Google Form ซะก่อน อันนี้แล้วแต่คุณเลย แต่ผมจะลองสร้างฟอร์มใหม่ให้กรอกเล่นๆ ละกัน
- Step หลัก 1 : เตรียม Google Form และหา URL
- สร้างฟอร์มที่ Google Form
- สร้าง Link + ลองกรอกข้อมูล
- สร้าง Google Sheet ไว้บันทึกข้อมูล
- วิธีที่เอา URL มี 2 วิธี
- วิธีที่ 1 (วิธีใหม่ ง่ายกว่า) : ใช้วิธี Publish to Web
- วิธีที่ 2 (วิธีเก่า) : เอาจาก link download
- เปิด Link Sharing ก่อน
- หา URL ของไฟล์จาก Section Download
- Step หลัก 2 : เอาข้อมูลเข้า Power Query
- กลับไปกรอกฟอร์มเพิ่ม แล้วกลับมา Refresh
Step หลัก 1 : เตรียม Google Form และหา URL
สร้างฟอร์มที่ Google Form
ผมก็ใส่คำถามไปประมาณนี้
สร้าง Link + ลองกรอกข้อมูล
เราก็จะมาลองกรอกข้อมูลมั่วๆ ลงไปซัก 2 อันละกัน
ก่อนอื่นก็ไปสร้าง Link เพื่อให้คนเข้ามากรอกฟอร์มได้ ดังนี้
แล้ว Copy Link ไปเปิดใน Tab ใหม่ แล้วลองกรอกข้อมูลดู
เสร็จแล้วแล้ว Submit ซะ
สร้าง Google Sheet ไว้บันทึกข้อมูล
จากนั้นกลับไปที่หน้าสร้างฟอร์ม แล้วกดที่ Responses จากนั้นกด icon รูป Sheet เขียวๆ ตามรูป เพื่อให้ Form บันทึกข้อมูลลง Google Sheets
จากนั้นมันจะเปิดหน้าต่าง Google Sheet ขึ้นมาโดยมีคำถามเป็นหัวตารางข้อมูล และมีข้อมูลที่เราลองกรอกลงไป
วิธีที่เอา URL มี 2 วิธี
วิธีที่ 1 (วิธีใหม่ ง่ายกว่า) : ใช้วิธี Publish to Web
*** วิธีนี้แนะนำมาโดย คุณ Bo แห่งเพจ Excel Wizard ครับ***
ให้ไปที่ File -> Publish to Web
จากนั้นเลือกรูปแบบที่ต้องการ ในที่นี้ผมเลือกเป็น csv
จากนั้นจะได้ URL มาให้ Ctrl+C เพื่อ Copy เก็บไว้เพื่อเอาไว้ใส่ใน Power Query ครับ
วิธีที่ 2 (วิธีเก่า) : เอาจาก link download
เปิด Link Sharing ก่อน
ให้กดปุ่ม Share เขียวๆ ที่มุมบนขวา แล้วเปิดให้ anyone with link can view
จากนั้นให้ Download ไฟล์ออกมาเป็น excel หรือ csv ก็ได้ ในที่นี้ผมเป็น csv ละกัน โดยให้ไปที่ File –> Download –> Comma Separate Value
แล้ว Save ไว้ที่ไหนก็ได้ (ตรงนี้ไม่สำคัญ เพราะเราไม่ได้จะ link กับไฟล์ที่ save มาหรอก)
หา URL ของไฟล์จาก Section Download
ให้ไปที่ Section Download (ถ้าเป็น chrome กด Ctrl+J ได้)
แล้วคลิ๊กขวา –> Copy Link Address ของไฟล์ที่เพิ่งโหลดมาซะ สิ่งที่ Copy มานี่แหละ จะเป็น URL ที่จะเอามาใช้ใน Power Query ครับ
Step หลัก 2 : เอาข้อมูลเข้า Power Query
เปิด Power Query ซะ แล้ว Get Data จาก Web จากนั้นใส่ URL ที่ Copy ไว้จาก Step ที่แล้วลงไป (ไม่ว่าจะวิธีไหนก็ตาม) แล้ว ok
จากนั้นมันจะ preview ผลลัพธ์มาให้ ถ้าอ่านไม่ออกให้เปลี่ยน Encoding เป็น UTF8 นะ
จากนั้นกด Transform เพื่อจัดการข้อมูลที่อาจจะผิด เช่นวันที่
อย่างของผมเนี่ย มัน convert วันที่ผิดปี ถ้า control panel ผม set Region เป็น Thai วันที่ที่ถูกต้องตอน Preview จะต้องเป็น พ.ศ. แต่อันนี้มันดันเป็น ค.ศ. แถมเป็น เดือน/วัน/ปี อีก…
ดังนั้นผมต้องกด Convert วันที่แบบ Using Locale ใหม่ แต่ก่อนจะทำ ผมต้องเปลี่ยน Format วันที่กลับเป็น Text ก่อน แล้วกด Replace Current
จากนั้นค่อย กด Convert วันที่แบบ Using Locale ใหม่
จากนั้นเลือก Date/Time รูปแบบเป็น English (United States) เพราะ Format มาเป็น เดือน/วัน/ปี ค.ศ.
พอแก้ Locale เสร็จ ผลลัพธ์เป็นแบบนี้ถึงจะถูกต้องครับ (กรณีใน Control Panel เป็น Thai จะต้องเห็นเป็น พ.ศ.)
ที่นี้ก็กด Home –> Close & Load เพื่อให้ออกไปเป็น Table ดู
จะเห็นว่าผลลัพธ์ใช้ได้เลยล่ะ
กลับไปกรอกฟอร์มเพิ่ม แล้วกลับมา Refresh
ลองไปกรอกเพิ่มดูอีกซักรายการแล้วกด Submit ฟอร์ม
จากนั้นกลับมาที่ตารางผลลัพธ์ใน Excel แล้วคลิ๊กขวา Refresh (โดยไม่ต้องไป Download หรือ Copy Link อะไรอีกแล้ว)
จะเห็นว่าผลลัพธ์ถูกดึงมาใน Excel อย่างง่ายดายเลย!!
นี่แหละครับ ความสุดยอดของ Power Query ซึ่งใช้ได้ทั้งใน Excel และ Power BI เลยนะ ใครอยากลองกรอกฟอร์มของผมเล่นดู ก็ไปกรอกได้ที่ //forms.gle/2wAbkYeJdP6oHwEHA นะครับ
ใครมีคำถามหรือติดอะไรตรงไหนก็สามารถ Comment ไว้ได้นะครับ ส่วนคนที่อยากเรียนรู้เรื่อง Power Query เพิ่มเติม สามารถอ่านได้ที่ Category Power Query
credit knowledge :
ผมศึกษาวิธีเก่ามาจากเว็บนี้ครับ //www.excelinppc.com/using-google-sheets-as-data-source-in-power-query/
ส่วนวิธี Publish to Web จาก Google Sheets คุณ Bo แห่งเพจ Excel Wizard แนะนำมาครับ
แชร์ความรู้ให้เพื่อนๆ ของคุณ