สอนใช้ Python ใน Excel ตอนที่ 1 : ลองใช้ครั้งแรก

ในบทความนี้ผมจะสอนใช้ Python ใน Excel ตั้งแต่ต้น แบบที่คนไม่รู้จัก Python มาก่อน ก็พอจะอ่านได้ครับ และแม้ว่าผมจะเพิ่งได้ใช้ Python for Excel เมื่อวานซืนเป็นครั้งแรก (เมื่อ 8 Sep 2023 ) แต่ผมพอจะมีประสบการณ์ในการใช้ Python มาบ้างแล้ว ถึงจะไม่ได้เชี่ยวชาญ แต่ก็น่าจะพอถ่ายทอดในมุมมองของคนที่เคยใช้ทั้ง 2 โปรแกรมมาก่อนได้ครับ

การที่ Microsoft เอา Python มาอยู่ใน Excel โดยตรง สำหรับผมแล้วเป็นเรื่องที่น่าตื่นเต้นมากๆ พอๆ กับตอนใช้ Power Query ครั้งแรกเลย เพราะมันจะเพิ่มความสามารถอย่างมหาศาลในการวิเคราะห์และนำเสนอข้อมูลและมันทำให้ผมรู้สึกกลับมาสนุกกับ “การเรียนรู้ครั้งใหม่” อันนี้ ^^

Tips : เราสามารถให้ AI เช่น ChatGPT ช่วยเขียน Code Python ให้เราได้ เนื่องจาก Python เป็นภาษาคอมพิวเตอร์ที่ ChatGPT ถนัดที่สุด และตอบแม่นกว่าภาษาอื่นมาก (แม่นกว่าสูตร Excel เยอะ)

วิธีการสมัครใช้ Python ใน Excel

ณ ปัจจุบัน คุณต้องมี Excel 365 แล้วต้องกดสมัครเป็น แบบ Excel 365 Insider (กดได้ฟรีเลย ในหน้า Account น่ะ) และเลือกแบบ Beta Channel เท่านั้น จึงจะใช้ Python ใน Excel ได้ และอาจต้องรอนิดหน่อยด้วย บางคนหลังจากสมัครก็ยังใช้ไม่ได้ทันที

หากใช้ได้แล้วจะมีเครื่องมือโผล่มาที่ Ribbon ของ Formula แบบนี้

วิธีการเรียกใช้ Python ใน Excel

ไปใน Cell ไหนก็ได้ แล้วเขียนสูตร =PY แล้วกด Tab โปรแกรมจะเข้าสู่ “โหมดการเขียน Code Python” ทันที (หรือกด Ctrl+Alt+Shift+P ก็ได้นะ แต่ผมชอบ =PY มากกว่า)

การรัน Code Python

เมื่อเขียน code python หากกด Enter เฉยๆ จะเป็นการขึ้นบรรทัดใหม่ (เพราะปกติ Code มักจะเขียนหลายบรรทัด) ถ้าอยากจะให้ Code Run ให้กด Ctrl+Enter แทน

และการเขียน Code Python นั้น ไม่ต้องขึ้นด้วยเครื่องหมายเท่ากับ นะ เราสามารถพิมพ์ Code ที่ต้องการได้เลย เช่น

(5+2)*10

แล้วกด Ctrl+Enter เพื่อ Run Python ซึ่งมันจะทำการส่งข้อมูลไป Run บน Cloud นะครับ (แปลว่าต้องต่อ Internet!)

ผลลัพธ์ของ Python ใน Excel

ผลลัพธ์ของ Python ใน Excel จะเลือกได้ 2 แบบ คือ Python Object และ Excel Value

โดยที่ค่า Default คือ Python Object ซึ่งจะเกือบข้อมูลหลายค่าอยู่ใน Object เดียวได้

โดยถ้าผลลัพธ์เป็น Value ที่มีค่าเดียวจะแสดงออกมาเลย (เช่น เลข 70)

แต่ถ้าผลลัพธ์เป็นโครงสร้างข้อมูลที่มีหลายค่า อาจยังไม่แสดงออกมา แต่จะเห็นเป็น Object เช่น list, record, DataFrame แทน

เช่น เราสามารถสร้าง list ใน Python ได้ด้วย [ item1 , item2, item3 ]
ป.ล. คล้ายๆ M Code ของ Power Query แต่ Power Query ใช้ { } แทนที่จะเป็น [ ]

[5,20,"cat","สาว"]

เราสามารถกดเปลี่ยนให้เป็น Excel Value ได้โดยผลลัพธ์จะแสดงออกมาใน Cell ของ Excel ตามปกติ และถ้ามีหลายค่าจะออกมาเป็น Dynamic Array (ใช้ได้กับ list และ DataFrame) ดังรูป

ป.ล. ผมใช้ฟังก์ชัน FORMULATEXT ของ Excel ในการแสดงสูตร Python ออกมา

• ,1 ด้านท้ายคือการบอกว่าผลลัพธ์ออกมาเป็น Python Object
• ,0 ด้านท้ายคือการบอกว่าผลลัพธ์ออกมาเป็น Excel value

การประกาศตัวแปรใน Python

เราสามารถประกาศตัวแปรใน Python ได้ (ประกาศได้หลายตัวไปเรื่อยๆ) ด้วยการเขียนว่า

ตัวแปร1 = expression บางอย่าง
ตัวแปร2 = expression บางอย่าง
...

ซึ่ง python จะเอาค่าฝั่งขวา มาเก็บในตัวแปรฝั่งซ้าย

width=5
height=10
area=width*height

และโดยปกติมันจะแสดงค่าบรรทัดสุดท้ายออกมา ทำให้ในตัวอย่างนี้ เราเลยเห็นค่าเป็น 50 ของตัวแปร area นั่นเอง

อ้างอิงตัวแปรที่เคยประกาศแล้วใน Cell อื่น

เราสามารถอ้างอิงตัวแปรที่ประกาศไว้ใน Cell อื่นได้นะ แต่มัน “ต้องเป็น Cell ที่อยู่ก่อนหน้า”

โดย Python จะอ่านค่าจาก Row1 จนครบก่อน จากซ้ายไปขวาทุกคอลัมน์ แล้วค่อยอ่าน Row2 (ทุกคอลัมน์จากซ้ายไปขวาอีกที)

ดังนั้นแบบนี้ ค่าใน B4 มันอ่านหาตัวแปร width ได้ เพราะเคยประกาศไว้ใน B2

แต่แบบนี้ค่าใน A1 มันอ่านหา width ได้ไม่ได้ เพราะว่ามันดันเรียกค่า width ทั้งๆ ที่การประกาศยังมาไม่ถึง เพราะการประกาศทำไว้ใน B2

และถ้าแบบนี้ มันจะอ่านค่าได้ 7 เพราะมีการประกาศตัวแปร width ซ้ำอีกทีใน Cell D2 มันเลยทับค่า width ที่เคยประกาศใน B2 ไปเลย

วิธีการอ้างอิงข้อมูลจาก Excel เข้าไปใน Python

เราสามารถอ้างอิง Cell Reference หรือจะ Defined Name ต่างๆ หรือแม้แต่ชื่อ Query ก็ได้

1.อ้างอิง Cell เดี่ยวๆ

วิธีแรก คือ จิ้มที่ Cell ที่ต้องการ เพื่ออ้างอิงค่าได้เลย มันจะขึ้นฟังก์ชัน xl ขึ้นมาเอง (ซึ่งคือ ฟังก์ชันพิเศษที่เอาไว้อ่านค่าจาก Excel) พร้อมกับ Cell Reference ของสิ่งที่เราจิ้ม เช่น

แล้วเราสามารถ Copy สูตรลงมาเพื่อให้ Cell Reference เลื่อนได้ตามปกติเลย (หรือจะ Lock $ ก็ทำได้ตามปกติเลย)

เรายังสามารถใช้สูตรหรือฟังก์ชัน Excel คำนวณค่าที่มาจาก Python ได้ตามปกติด้วย

แต่เอาจริงๆ การอ้างอิง Cell เดียวดูแล้วไม่ค่อยมีประโยชน์เท่าไหร่ ไม่ค่อยมีใครเค้าทำกันหรอกครับ เราไปดูวิธีที่เข้าท่าขึ้นดีกว่า

2.อ้างอิง Range

Python ใน Excel จะเริ่มมีประโยชน์ขึ้น ถ้าเราอ้างอิงข้อมูลเป็น Range เข้าไปให้มัน

ตัวอย่างเช่น ถ้าเดิมทีผมมีแค่ สินค้า Unit Price และ Qty ผมสามารถอ้างอิง Range ทั้งยวงได้เลย (B2:D5) แบบนี้ (รวมหัวตาราง) มันจะฉลาดพอที่จะใส่ว่าข้อมูลเรามีหัวตารางอยู่ด้วย (headers= True)

ถ้ามีการใส่ข้อมูลเป็นชุดเข้าไปหลายตัว ผลลัพธ์จะออกมาเป็นสิ่งที่เรียกว่า DataFrame (เป็น Object แบบนึงของ Pandas Library ของ Python ซึ่งให้มองว่าคล้ายๆ Array หรือ Table ของ Excel ก็ได้ ซึ่งเป็น Object ที่ดีมากๆ)

3.อ้างอิง Dynamic Array

สมมติเรามี Dynamic Array อยู่ เราก็ใช้วิธีการจิ้มที่ช่องซ้ายบน แล้วใช้เครื่องหมาย # เข้าไปหลัง Cell Reference นั้นๆ ตามปกติได้เลย

4.อ้างอิง Table

เราสามารถเลือก Table ทั้งอันเข้าไปใน DataFrame ก็ได้ เลือกให้ได้หัวตารางด้วยนะ ซึ่งดีกว่า Range ตรงที่ Table มันงอกออกมารองรับข้อมูลใหม่เองได้

สูตรใน Python มันจะออกมาประมาณนี้ แบบนี้จะได้ทั้งตาราง รวมหัวตารางด้วย

xl("TableName[#All]", headers=True)

5.เอาผลลัพธ์จาก Power Query

กรณีจะเอาข้อมูลจาก Power Query เข้าสู่ Python ณ ตอนนี้ จะต้องเป็นการ Get Data จากแหล่งอื่นที่ไม่ใช่ CurrentWorkbook นะครับ เช่น ผมเอาจาก csv ในเว็บอันนี้

https://raw.githubusercontent.com/ThepExcel/YouTube/main/SampleData.csv

ลอง Get Data from Web แล้วใส่ url เข้าไป แล้วให้ Load to ออกมาเป็น Connection Only

เวลาเราเรียกใช้ใน Python สามารถพิมพ์ว่า xl(” แล้วมันจะมีชื่อ Query ทั้งหมดเด้งขึ้นมาให้เลือกเลย

พอเลือกปุ๊ปเราก็ได้ข้อมูลนั้นเข้า DataFrame จบ

ลองเล่นกับ Python DataFrame

ซึ่งพอสิ่งที่เราได้มันเป็น DataFrame เราสามารถจัดการมันได้หลากหลายมากขึ้นเยอะมาก เช่น

ทำการสรุปเบื้องต้น

DataFrome นั้นมีสิ่งที่เรียกว่า Method (คือฟังก์ชันสำเร็จรูปที่ติดมากับ Object ประเภทนั้นๆ) ที่ชื่อว่า describe ซึ่งสามารถสรุปข้อมูลจากคอลัมน์ที่เป็นตัวเลขทั้งหมดได้แบบอัตโนมัติสบายๆ เลย ซึ่งดีมาก เช่น

df=xl("B2") #อ้างอิง Data Frame จากอีกช่องก็ได้นะ
df.describe() #สรุปผล

ป.ล. เราสามารถ Comment code ใน python ได้ด้วยเครื่องหมาย # นะครับ

ถ้าอยากให้ผลลัพธ์ออกมาใน Cell ไปเลย เราต้องเปลี่ยนวิธีแสดงผลลัพธ์จาก Python Object เป็น Excel Value

ผลลัพธ์ก็จะแสดงออกมาเป็น Dynamic Array ใน Excel เลย

การดูรายชื่อคอลัมน์ทั้งหมด

ใช้คำสั่งประมาณนี้ก็ได้ เพื่อเอารายชื่อคอลัมน์ออกมาเป็น list

list(df.columns)

การคัดเลือกเอาเฉพาะบางคอลัมน์

สามารถทำได้โดยการใช้คำสั่ง

DataFrameName[ List Column Name ] เช่น

df[["OrderID","สินค้า","ยอดขาย"]]

จะได้แบบนี้

ถ้าเราแค่เรียก df[[“OrderID”,”สินค้า”,”ยอดขาย”]] มาดู โดยไม่ได้มีการ assign ค่ากลับเข้าไปในตัวแปร df อันเดิม ก็จะไม่ได้ทำให้ df เดิมมีการเปลี่ยนแปลงแต่อย่างใด

ยกเว้นการเพิ่มคอลัมน์ที่จะเห็นถัดไป ว่ามันเพิ่มเข้าของเดิมจริงๆ

ทำการ Add Column เพิ่ม

ผมสามารถทำการสร้างคอลัมน์เพิ่ม ในรูปแบบของ

df["Col Name"] = สูตร

ดังนั้นสูตรจะเป็นแบบนี้

df["ยอดขายลด10%"]=df["ยอดขาย"]*0.9 #สร้างคอลัมน์ใหม่
df  #เรียกตัวแปร df ที่เก็บ dataframe ออกมาดู

ลองดูรายชื่อคอลัมน์อีกที จะเห็นว่าใน df มีคอลัมน์ใหม่เพิ่มมาแล้วจริงๆ นะ

ทำการ Filter DataFrame

การ Filter DataFrame นั้นทำได้หลายแบบมากๆ แต่ผมจะใช้วิธีสร้าง Series True False (หลักการคล้ายฟังก์ชัน FILTER)

ปกติแล้วถ้าเราอ้างอิงคอลัมน์เดียวใน DataFrame มันจะได้สิ่งที่เรียกว่า Series เช่น

ซึ่งจะเป็นชุดข้อมูลที่มี Index อยู่ด้านซ้าย เช่น

df["วิธีการชำระเงิน"]

ซึ่งเราสามารถใช้ Series มาคำนวณ หรือจะใช้ร่วมกับการเปรียบเทียบ เพื่อสร้าง Series ที่มีค่า True False ได้ เช่น

สมมติว่าผมสนใจเฉพาะข้อมูลที่วิธีการชำระเงินเป็นเงินสด ก็ทำได้ดังนี้

df["วิธีการชำระเงิน"]=="เงินสด"

(ใน python ใช้ == เป็นการเปรียบเทียบนะ ไม่ใช่แค่ =)

แล้วถ้าผมเอา DataFrame Series True False นี้ไปเรียกใช้กับ df หลัก เช่น

df[df["วิธีการชำระเงิน"]=="เงินสด"]

ผมก็จะสามารถ Filter ข้อมูลได้ (เห็นมะว่ามียอดขายลด10% อยู่)

ถ้าจะใส่หลายเงื่อนไข ให้ใช้ & หรือ | มาเชื่อม ซึ่งต้องใส่วงเล็บคั่นแต่ละก้อนด้วย เช่น

df[(df["วิธีการชำระเงิน"]=="เงินสด") & (df["จำนวนชิ้น"]>3)]

ซึ่งเราสามารถเก็บค่าที่ Filter ได้นี้ไว้ในตัวแปรใหม่ หรือจะเก็บทับตัวแปรเดิมก็ได้ แต่ในที่นี่ผมคิดว่าเก็บไว้ในตัวแปรใหม่ดีกว่า เช่น เก็บไว้ใน cashGT3_DF

ป.ล. ในตัวแปร df ก็ยังคงมีครบทุกแถวอยู่นะ

cashGT3_DF=df[(df["วิธีการชำระเงิน"]=="เงินสด") & (df["จำนวนชิ้น"]>3)]

ผสมผสานกับการใช้ Dropdown List

แน่นอนว่าเรื่องพวกนี้ เราผสมผสานกับการใช้ Dropdown List เพื่อให้ข้อมูลที่ Filter นั้น Dynamic ได้นะ (ถ้าต้องการ)

เลือกเงินสด

เลือกเครดิต

ทำการสรุปเบื้องต้น

ผมสามารถทำการสรุปด้วยการ Group by แล้วหาผลรวม นับจำนวน ค่ามากสุด น้อยสุด ได้เช่นกัน ดังนี้

df.groupby(ชื่อคอลัมน์ที่จะ group)

ซึ่งจะได้ Object พิเศษคือการ Group ตามคอลัมน์ที่เลือก แต่ว่า Object นี้เราไม่สามารถแสดงผลออกมาดูได้ เราจะต้องทำอย่างอื่นต่อด้วย เช่น

df.groupby(ชื่อคอลัมน์ที่จะ group)[คอลัมน์ที่จะสรุป].วิธีสรุป

#หรือ

df.groupby(ชื่อคอลัมน์ที่จะ group)[ list คอลัมน์ที่จะสรุป ].วิธีสรุป

เช่น

df.groupby("สินค้า")[["จำนวนชิ้น","ยอดขาย"]].sum()

วิธีการสรุปอื่นๆ เช่น

• size() = จำนวนแถว
• count() = นับที่ไม่ว่าง
• first() = ตัวแรก
• last() = ตัวสุดท้าย
• sum() = หาผลรวม
• min() = ค่าน้อยสุด
• max() = ค่ามากสุด
• median() = ค่ามัธยฐาน

หรือจะสรุปๆหลายแบบพร้อมกันก็ได้ โดยใช้

df.groupby(ชื่อคอลัมน์ที่จะ group)[ list คอลัมน์ที่จะสรุป].aggregate(listวิธีสรุป)

เช่น

df.groupby("สินค้า")[["จำนวนชิ้น","ยอดขาย"]].aggregate([sum,max])

#หรือ เขียน agg สั้นๆ ก็พอ

df.groupby("สินค้า")[["จำนวนชิ้น","ยอดขาย"]].agg([sum,max])

ลองสร้างกราฟดูบ้าง

กราฟพื้นฐาน ที่ไม่ต้องใช้ library พิเศษ

แบบนี้คือใช้ .plot ได้เลย แล้วกำหนดประเภทกราฟที่ต้องการได้อีกที

df["ยอดขาย"].plot.hist()

แปลว่า ให้เอาคอลัมน์ยอดขายมาทำ histogram

ผลลัพธที่ได้จะเป็นรูปเล็กๆ ใน Cell

วิธีทำให้เห็นรูปใหญ่ มีหลายแบบ

1. ขยาย Cell ให้ใหญ่
2. Merge Cell ให้ใหญ่
3. ให้เราคลิ๊กขวาแล้วเลือก Picture In Cell -> Create Reference (แนะนำ)

จะมีการสร้างภาพใหญ่ขึ้นมาอีกอัน ที่ link ค่าจาก cell ผลลัพธ์อีกที

ประเภทกราฟอื่นๆ เช่น area , bar, barh , box , density , hexbin , hist , kde , line , pie , scatter

สมมติว่าจะ Plot จากตัวที่ Groupby แล้ว เช่น ยอดขายรวมของสินค้าแต่ละตัวเป็นกราฟแท่ง ก็อาจทำประมาณนี้ได้

dfProductSales=df.groupby("สินค้า")["ยอดขาย"].sum()
dfProductSales.plot.bar()

ผลลัพธ์จะออกมาแบบนี้ คือ ข้อความที่เป็นภาษาไทยนั้นเน่าสนิท…

ปกติแล้ว วิธีแก้คือเราต้องประกาศคำสั่งแก้ตระกูล Font ให้เป็นตัวที่อ่านภาษาไทยออกก่อน มันถึงจะแก้ได้

plt.rcParams['font.family']='Sarabun-Regular.ttf'
plt.rcParams['font.size']=18

dfProductSales=df.groupby("สินค้า")["ยอดขาย"].sum()
dfProductSales.plot.bar()

และถ้าตัวระบบที่รัน Python อยู่ ไม่มี Font นั้นๆ ก็ต้องหาทาง install ก่อน แต่ปัญหาคือ ผมเองก็ยังหาวิธี install font ไทยเข้าไปใน Python ของ Excel ที่มันรันบน cloud ของ Anaconda ที่ร่วมกับ Microsoft ไม่ได้แฮะ ใครเจอวิธีแล้วบอกด้วยนะครับ…

ตอนนี้ช่างมันเรื่องอ่านภาษาไทยไม่ออกไปก่อนละกันนะ

ทำกราฟขั้นสูงขึ้น ด้วย Seaborn

seaborn คือ Library ของ Python ที่สามารถสร้างกราฟสวยๆ customize ได้ง่าย และสามารถ set theme ต่างๆ ได้ง่ายกว่ากราฟแบบทั่วไป

แต่เราจะต้องมีการ import library เข้ามาก่อน (ทำทีเดียวก็พอ)

ใช้คำว่า

import seaborn as sns

การใช้ as sns คือการตั้งชื่อเล่นให้กับมัน จะได้ไม่ต้องพิมพ์ยาว

เช่น

import seaborn as sns
sns.set_theme(style="dark", palette="pastel")
boxplot = sns.boxplot(x="วิธีการชำระเงิน", y="ยอดขาย", data=df)

และยังรองรับกราฟแปลกๆ อีกมากมาย เช่น JointPlot

ใครอยากลองดู Data ที่ Plot แล้วน่าสนใจขึ้น ลองดู Sample Dataset ที่ทาง Microsoft เตรียมมาก็ได้ครับ กดตามรูปได้เลย

โดยมีการใช้ Pairplot เพื่อดู Scatter Plot แบบ Cross กันระหว่างหลายคอลัมน์ด้วย

ถ้ายังไม่จุใจ ลองดูตัวอย่างกราฟอื่นๆ ได้ที่นี่ https://seaborn.pydata.org/examples/index.html

สรุปความเห็นของผม

เท่าที่ทดลองใช้งาน ผมคิดว่า Python สำหรับ Excel นั้นเกิดมาเพื่อช่วยในการวิเคราะห์ข้อมูล เช่น Data Analysis ด้วย Pandas , หรือทำ Machine Learning ด้วย Scikit Learn และทำ Visualization เจ๋งๆ (ถ้าไม่ติดปัญหาภาษาไทย) มากกว่าที่จะทำงานด้านอื่นๆ ที่สามารถทำได้ด้วย Python แบบปกติที่เรา install ลงในเครื่องตัวเอง

ข้อเสีย

• ข้อจำกัดสำคัญคือการที่ Python ตัวนี้มันดัน Run บน Cloud ทำให้เราต้องต่อ Net และรันได้ค่อนข้างช้า และรับข้อมูลมากๆ ไม่ไหว
• จุกจิกเรื่อง Security ที่ให้ไม่สามารถใช้ Library บางอย่าง เช่น Scrape Data จากเว็บไซต์ก็ไม่ได้
• แถมในอนาคตไม่รู้จะมีการเก็บตังเพิ่มเพื่อให้ใช้ Feature นี้ได้เต็มที่อีกรึเปล่าด้วยนะ…

ข้อดี

อย่างไรก็ตาม การใช้ Python ใน Excel มีข้อดีคือ

• สามารถเห็นภาพของข้อมูลได้ง่าย
• ผสมผสานกับเครื่องมือต่างๆ ของ Excel ได้ดี
• เหมาะกับคนที่ใช้ Excel ได้แล้วระดับนึง แล้วอยากลองหัดใช้ Python แต่จะให้ไปเขียนโปรแกรมใน Editor ล้วนๆ หรือเขียนใน Colab เลยอาจจะรู้สึกไม่ชิน ดังนั้นมาหัดใน Excel ที่ถนัดก่อนอาจเป็นวิธีเริ่มที่ดีก็ได้นะครับ
• Python เองก็มีความสามารถหลายอย่างที่ทำได้ยากใน Excel
  • การ Manipulate ข้อความก็เป็นจุดแข็งของ Python เมื่อเทียบกับ Excel
    • Python นั้นใช้ Regular Expression ได้ แต่ Excel ปกติทำไม่ได้
    • Python สามารถใช้ Machine Learning ทำความเข้าใจประโยคได้
  • ใน Python เรายังสั่งวน Loop ทำเรื่องต่างๆ ได้ง่ายกว่า Excel มาก (ใน Excel วน Loop ด้วย LAMBDA Helper ยากกว่าเยอะ)
  • การทำ Data Analysis ขั้นสูง เช่น การ Predict ด้วย Machine Learning นั้น Excel สู้ไม่ได้เลย

ตอนต่อไป

ดังนั้นในตอนถัดไป ผมจะพูดถึงเรื่องการวน Loop การจัดการ List และการเขียน Condition ใน Python ครับ ไปอ่านต่อได้เลย

สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition