สำรวจฟังก์ชัน Get & Transform ของ Excel

บทสรุปผู้บริหาร

ในยุคของ Data Lake และฐานข้อมูลขนาดเพตะไบต์ ทำให้ฉันยังคงได้รับข้อมูลในรูปแบบของไฟล์ CSV, ข้อความ และ Excel บ่อยครั้งจนน่าตกใจ แม้ว่าการวิเคราะห์ในยุคปัจจุบันจะเน้นที่ความก้าวหน้าล้ำสมัยในอัลกอริธึมการเรียนรู้ของเครื่อง แต่การวิเคราะห์ข้อมูลแบบน่าเบื่อในแต่ละวันยังคงเป็นกระบวนการแบบแมนนวลในการค้นหา รวบรวม และโต้แย้งประเภทข้อมูลที่แตกต่างกัน

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

Get & Transform ทำอะไรได้บ้าง?

จริงๆ แล้ว วิธีแก้ปัญหาสำหรับปัญหาทั่วไปนี้ค่อนข้างเข้าถึงได้: Excel และ Power BI มีชุดเครื่องมือการแปลงข้อมูลที่ผู้ใช้ไม่กี่คนรู้จัก ชื่อ Get & Transform (เดิมเรียกว่า Power Query) การใช้ฟังก์ชันการแยก การแปลง และโหลด (ETL) แบบฝังช่วยให้นักวิเคราะห์ทางการเงินสามารถเชื่อมโยงไปยังแหล่งข้อมูลของตนได้อย่างราบรื่นและเข้าถึงข้อมูลเชิงลึกได้รวดเร็วยิ่งขึ้น

ในขณะที่เราจัดเรียงข้อมูลเพื่อโหลดลงใน Excel หรือ Power BI เรามักจะต้องทำการแปลงข้อมูลบางส่วน ตัวอย่างของการจัดการข้อมูลอาจรวมถึง:

• การลบคอลัมน์
• การกรองข้อมูล
• การจัดกลุ่มข้อมูล
• การหมุน/ยกเลิกการหมุนข้อมูล
• การแยกสตริงออกเป็นสตริงย่อย
• การแยกคำหลักจากสตริง
• ต่อท้ายแถวจากตารางอื่น และ
• เข้าร่วมตารางสองมิติ

ในไดอะแกรมด้านล่าง เราเห็นว่า Get & Transform ทำหน้าที่ที่น่าเบื่อหน่ายในการประมวลผลข้อมูลล่วงหน้าก่อนที่จะโหลด

ทำไมคุณจึงควรใช้ Get & Transform?

ทำไมการเรียนรู้การใช้ Get & Transform จึงคุ้มค่า? เมื่อฉันดูสิ่งที่ฉันใช้ฟังก์ชันนี้เป็นการส่วนตัว ฉันได้เสนอชุดเครื่องมือที่ปรับเปลี่ยนได้สำหรับ:

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

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

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

อย่างไรก็ตาม หากลูกค้าของฉัน:

1. ต้องการใช้เครื่องมือสร้างภาพอื่น
2. มีผู้ใช้หลายคนที่จะรีเฟรชข้อมูลหรือ
3. ต้องใช้โมเดลแมชชีนเลิร์นนิง

จากนั้นฉันจะใช้ Get & Transform สำหรับการสำรวจข้อมูลเบื้องต้นเท่านั้น จากนั้นจึงย้ายการยกของหนักไปที่ R

วิธีเข้าถึง Get & Transform ใน Excel หรือ Power BI

ใน Excel เวอร์ชันก่อนหน้า Power Query เป็น Add-in ที่สามารถติดตั้งเพื่อช่วยเกี่ยวกับฟังก์ชัน ETL อย่างไรก็ตาม ใน Excel 2016 และ Power BI เครื่องมือเหล่านี้มีการผสานรวมอย่างแน่นหนายิ่งขึ้น ใน Excel 2016 พวกเขาสามารถเข้าถึงได้ผ่านทางแท็บ ข้อมูล จากนั้นจึงไปที่ส่วนรับ และแปลงข้อมูล

ใน Power BI ฟังก์ชันการทำงานจะมีอยู่บนแท็บ หน้าแรก ในส่วน ข้อมูลภายนอก

ในบทความนี้ ตัวอย่างของฉันเกิดขึ้นใน Power BI แต่อินเทอร์เฟซเกือบจะเหมือนกับของ Excel ฉันจะชี้ให้เห็นถึงความแตกต่างเมื่อเกิดขึ้น ดังนั้นบทแนะนำควรมีความเหมาะสมสำหรับผู้ใช้ทั้งสองประเภท

1. กำลังโหลดไฟล์ CSV

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

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

ด้านล่างนี้เป็นภาพหน้าจอของลักษณะของ CSV ดิบ:

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

สำหรับตอนนี้ สมมติว่าเราจำเป็นต้องดูข้อมูลเป็นโครงสร้างที่ "แคบและสูง" แทนที่จะเป็นโครงสร้างที่ "กว้างและสั้นกว่า" ขั้นตอนแรกคือการโหลด CSV; จากนั้นเราจะเริ่ม "unpivot" ข้อมูล

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

รับ & แปลงลักษณะและการทำงานที่คล้ายคลึงกันระหว่าง Power BI และ Excel ส่วนใหญ่ อย่างไรก็ตาม ใน Excel หลังจากคลิก Close and Load จะมีพรอมต์เพิ่มเติมหนึ่งรายการ ในรูปด้านล่าง เราสามารถสลับไปมาระหว่างว่าเราต้องการโหลดข้อมูลลงใน:

1. ตารางใน Excel,
2. PivotTable ที่สร้างขึ้นโดยเทียบกับข้อมูล
3. PivotChart ที่สร้างขึ้นโดยเทียบกับข้อมูล หรือ
4. “สร้างการเชื่อมต่อเท่านั้น”

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

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

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

• รวบรวมข้อมูลรายวันเป็นสัปดาห์ เดือน และไตรมาส
• การจัดกลุ่มพนักงานขายออกเป็นแผนกและภูมิภาค หรือ
• การแมป SKU กับประเภทผลิตภัณฑ์

แม้ว่าบทความนี้จะกล่าวถึง CSV และไฟล์ Excel อื่นๆ Get & Transform จะจัดการกับข้อมูลประเภทต่างๆ มากมาย เมื่อสร้างการสืบค้นแล้ว จะสามารถรีเฟรชได้ตลอดเวลาเมื่อข้อมูลเปลี่ยนแปลง

2. การจัดการสตริงข้อความ

เพื่อแสดงความสามารถของ Get & Transform ในการจัดการสตริง ฉันได้สร้างชุดข้อมูลอื่นที่เลียนแบบไฟล์ข้อความที่แสดงธุรกรรมทางบัญชีจากบัญชีแยกประเภททั่วไป (GL) ของบริษัท

สังเกตว่าหมายเลขบัญชีและชื่อปรากฏในสตริงเดียวกันอย่างไร? ใน Power BI เราสามารถแยกวิเคราะห์หมายเลขบัญชีและชื่อเป็นฟิลด์แยกกันได้อย่างง่ายดาย

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

ต่อไป เรานำข้อมูลเดียวกันมาและสร้างผังบัญชีพร้อมการจับคู่กับหมวดหมู่บัญชี

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

3. การทำงานกับแหล่งข้อมูลต่างๆ

Get & Transform รองรับแหล่งข้อมูลที่หลากหลาย แม้ว่าจะไม่ใช่รายการที่ครบถ้วนสมบูรณ์ แต่ด้านล่างนี้เป็นตัวอย่างบางส่วน:

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

4. ปรับแต่งรหัสด้วยภาษา M

ในเบื้องหลัง Get & Transform กำลังสร้างโค้ดทุกครั้งที่เราคลิกปุ่มในเครื่องมือหรือทำการเลือก ด้านล่างนี้คือตัวอย่างวิธีที่คุณจะเข้าถึงรหัสสำหรับการค้นหาการแมปบัญชีที่เราสร้างขึ้น:

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

ขีด จำกัด ของ Get & Transform

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

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