Tag: function

สอนใช้ Python ใน Excel ตอนที่ 4 : สร้างฟังก์ชันใช้เอง
บทความนี้ผมจะพูดถึงเรื่องการสร้างฟังก์ชันขึ้นมาใช้เองใน Python ซึ่งเป็นพื้นฐานที่สำคัญต่อจากที่ได้อธิบายเรื่อง Loop และ RegEx ไปแล้วในบทความก่อน
เดี๋ยวเรามาดูกันครับว่า ฟังก์ชันคืออะไร? ต่างจาก library หรือ method ยังไง? แล้วทำไมเราต้องสร้างฟังก์ชันชึ้นมาเองด้วย?
ฟังก์ชันคืออะไร?
ฟังก์ชัน มันคือคล้ายๆ กับ “กล่องดำ” ที่คุณเอา input เข้าไป แล้วมีการประมลผลอะไรบางอย่างในกล่องดำนั้น แล้วเราก็จะได้ output ออกมา
ซึ่งถ้าเราใช้ Excel เราก็จะคุ้นเคยกับฟังก์ชันอยู่แล้วล่ะ เช่น ฟังก์ชัน ROUND เอาไว้ปัดตัวเลขตามหลักคณิตศาสตร์ LEN เอาไว้นับจำนวนอักขระ หรือ LEFT เอาไว้สกัดเอาข้อความจากด้านซ้าย
ส่วนใหญ่แล้วสิ่งที่คนทั่วไปทำงานกับฟังก์ชันใน Excel คืออยู่ในฐานะ “ผู้ใช้ฟังก์ชัน” มากกว่าผู้สร้าง ซึ่งถ้าเราเป็นผู้สร้างฟังก์ขันขึ้นมาเองได้ มันจะเจ๋งกว่าเดิมมากเลยนะ
แล้ว library หรือ method ล่ะ?
- Library: คือห้องสมุดที่รวบรวมฟังก์ชันและโค้ดอื่นๆ ที่มีคนทำไว้ให้แล้ว อย่าง numpy หรือ pandas คือ library ที่ช่วยในการจัดการข้อมูล ซึ่งเต็มไปด้วยฟังก์ชันมากมาย
- Method: มันก็คือฟังก์ชัน ที่ “ผูกติดไว้กับ object” นั่นเอง เช่น string.upper() เราเรียกฟังก์ชัน upper ที่ผูกกับ string ว่า Method ซึ่งถ้าจะสร้าง Method ขึ้นมาเองเราจะต้องไปเรียนรู้เรื่อง OOP ซะก่อน ซึ่งผมจะขอพูดถึงในอนาคต นะครับ
ทำไมต้องสร้างฟังก์ชันขึ้นมาเอง?
การเขียนฟังก์ชันขึ้นมาเองนั้นมีประโยชน์หลายอย่าง ฟังก์ชันนั้นจะเพิ่มความสะดวกให้เรา และทำให้เราไม่ต้องเขียนโค้ดเดิมๆ ซ้ำๆ เพราะเราสามารถเขียนขึ้นมาครั้งเดียวแล้วสามารถเรียกใช้ฟังก์ชันได้เรื่อยๆ ตามต้องการ
รวมถึงลดความซ้ำซ้อน นั่นคือ แก้ไขการทำงานที่เดียว มันก็จะปรับปรุงทุกที่ที่มีการเรียกใช้ฟังก์ชัน แถมยังสามารถเรียกใช้ฟังก์ชันนึงในอีกฟังก์ชันนึงได้ด้วย ซึ่งจะยิ่งเพิ่มพลังให้การเขียน Code เรามหาศาลเลย
และเวลาจะหาจุดที่ผิดพลาด ก็สามารถทำได้ง่าย สะดวกกว่าที่จะไปควานหาใน Code ที่ทุกอย่างถูกเขียนปนกันไว้
เห็นประโยชน์มากมายแล้ว ลองไปดูวิธีสร้างฟังก์ชันดีกว่า
สร้างฟังก์ชันใน Python ยังไง?
การสร้างฟังก์ชันใน Python เนี่ย ทำได้ง่ายที่สุดแล้วเมื่อเทียบกับวิธีการอื่นๆ ใน Excel ทั่วไป (เช่น LAMBDA Function , VBA, หรือ Power Query )
ซึ่งการสร้างฟังก์ชันใน Python สามารถทำได้โดยการใส่คำสั่ง def ดังนี้
```
def function_name(arguments):
    function body
```
หรือถ้าฟังก์ชันนั้นสามารถให้ค่าผลลัพธ์คืนกลับมาได้ด้วย ก็ใช้คำสั่ง return เพิ่มเข้าไป เช่น
```
def function_name(arguments):
    function body 

    return xxx
```
ซึ่งฟังก์ชันใน Python นั้นต่างจาก LAMBDA Function หรือ ฟังก์ชันใน Power Query อย่างนึงก็คือ มันจะ return ค่าออกมา หรือไม่ return ก็ได้นะ แต่ปกติแล้วถ้าเราใช้ใน Excel เราจะมักใช้แบบ return ค่ากลับมาด้วย
ผมขอยกตัวอย่างฟังก์ชันแบบง่ายๆ ก่อนนะ เดี๋ยวตัวซับซ้อนมาทีหลัง
สมมติเราจะสร้างฟังก์ชันที่สามารถคำนวณ ราคาสินค้าหลังหักส่วนลด โดยที่รับ input 2 ตัวคือ ราคาก่อนลด และ %ส่วนลด เราอาจทำดังนี้
```
def AfterDiscount(OriginalPrice,DiscountRate):
    DiscountAmt=OriginalPrice*DiscountRate
    AfterDiscountAmt=OriginalPrice-DiscountAmt
    return AfterDiscountAmt
```
เราสามารถกำหนดค่า default ของ input ในฟังก์ชันได้ด้วย การใส่เครื่องหมาย = เช่น
```
def AfterDiscount(OriginalPrice,DiscountRate=0.2):
    DiscountAmt=OriginalPrice*DiscountRate
    AfterDiscountAmt=OriginalPrice-DiscountAmt
    return AfterDiscountAmt
```
สรุปความหมาย
- ฟังก์ชันทีเราเขียน ชื่อว่า AfterDiscount
- ซึ่งรับค่า input 2 ค่า คือ OriginalPrice กับ DiscountRate
  - OriginalPrice จำเป็นต้องระบุ
  - DiscountRate ถ้าไม่ระบุ จะให้ถือว่าเป็น 0.2 หรือ 20% (แต่ถ้ามีการระบุชัดเจนก็จะเชื่อที่เราระบุ)
- วิธีการทำงาน จะเอา 2 ค่านี้ไปคำนวณประมวลผลตามวิธีที่เราระบุ
- แล้วสุดท้ายก็ return ค่าราคาหลังลดแล้วออกมาเป็นผลลัพธ์ของฟังก์ชัน
เรียกใช้ฟังก์ชัน
เวลาจะเรียกใช้งานฟังก์ชัน ก็สามารถพิมพ์เรียกใช้ได้เลยตรงๆ คล้ายๆ ตอนเรียกใช้ฟังก์ชัน Excel นี่แหละ
```
ชื่อฟังก์ชัน(พารามิเตอร์)
```
เนื่องจากเรากำหนดฟังก์ชันไว้ว่า AfterDiscount(OriginalPrice,DiscountRate) ดังนั้นเวลาเรียกใช้ฟังก์ชัน ก็ต้องส่งค่าเข้าไป 2 ตัว โดย ตัวแรกคือ OriginalPrice และตัวที่สองคือ DiscountRate นั่นเอง (แต่ DiscountRate จะไม่ใส่ก็ได้)
สมมติผมใส่ไปแบบนี้
```
AfterDiscount(200,0.1)
#แปลว่า ราคาเริ่มต้น 200 แล้วลดราคา 10% 
```
แต่ถ้าระบุแค่
```
AfterDiscount(200)
#แปลว่า ราคาเริ่มต้น 200 แล้วตามค่า default ซึ่งก็คือ 0.2 หรือ 20%
```
ซึ่งแน่นอนว่าเรา link ค่า cell ใน Excel ให้กลายเป็น input ที่จะส่งเข้าฟังก์ชันของเราได้ ซึ่งมันก็จะสามารถทำงานเหมือนสูตร Excel เลย และสามารถเขียนสูตรช่องเดียวแล้ว Copy ลงไปข้างล่างได้ด้วย
```
AfterDiscount(xl("A4"),xl("B4"))
```
ลองใช้ร่วมกับความรู้ที่เรียนผ่านมาแล้ว
สมมติผมจะเอาความรู้ที่เราเรียนผ่านมา มาทำฟังก์ชันบ้าง
สมมติว่าเราต้องการดึงเอาเฉพาะตัวเลขออกมาจากข้อความ แล้ว convert แต่ละ item ให้เป็นตัวเลขด้วย เราอาจใช้ความรู้เรื่อง RegEx กับ List Comprehension มาช่วยก็ได้ เช่น
```
def ExtractNum(OriginalText):
    import re
    REresult = re.findall('[0-9]+', OriginalText)
    result=[int(i) for i in REresult]
    return result
```
ป.ล. เราสามารถ Import Library ในฟังก์ชันก็ได้นะ (แต่อาจจะดูแปลกๆ นิดหน่อย เพราะว่ามันอาจจะมีการเรียกใช้หลายรอบได้)
ถ้าจะให้ดีกว่า คือ import ทีเดียวไปเลย เอาไว้บนสุด แบบนี้
```
import re

def ExtractNum(OriginalText):
    REresult = re.findall('[0-9]+', OriginalText)
    result=[int(i) for i in REresult]
    return result
```
ขออีกซักตัวอย่างนึง เพื่อจะทำให้สามารถ TRIM ข้อมูลได้ง่ายๆ แบบ Excel เราอาจสร้างฟังก์ชันแบบนี้ขึ้มาได้
```
def TrimLikeExcel(OriginalText):
    StripText=OriginalText.strip()
    TrimText=re.sub(' +', ' ', StripText)
    return TrimText
```
แล้วเราก็สามารถเรียกใช้ฟังก์ชันพวกนี้ อาจจะทำใน DataFrame ก็ได้ โดยที่เราจะใช้ฟังก์ชัน TrimLikeExcel ของเราตรงๆ กับ Series ของ DataFrame ไม่ได้ เพราะเราตั้งใจจะรับค่ามาเป็น Text ธรรมดา
เราต้องใช้ฟังก์ชันเรากับแต่ละ item ใน Series นั้นต่างหาก ซึ่งเราสามารถใช้ method apply หรือ map มาช่วยได้ เช่น
```
df=xl("CharacterTable[#All]", headers=True)
df["Name"]=df["Name"].map(TrimLikeExcel)
df
```
Lambda Function
นอกจากการสร้างฟังก์ชันด้วย def ตามที่อธิบายไปข้างต้นทั้งหมดแล้ว มันยังมีวิธีการสร้างฟังก์ชันอีกแบบนึงที่เรียกว่า Lambda Function ด้วย ซึ่งจะเหมาะกับการทำฟังก์ชันเร็วๆ ที่อาจจะใช้แค่ครั้งเดียวใน Code ของเรา
ซึ่งลักษะของ Lambda Function คือ จะมีกี่ argument (input) ก็ได้ แต่มีได้แค่ expression เดียว คือบอกเลยว่าจะทำอะไร โดยไม่ต้องเขียน return เพราะว่ามันจะ return ค่าของ expression ออกมานั่นแหละ
```
lambda argument(s) : expression 
```
เช่น สมมติว่าจะทำกับฟังก์ชันคำนวณการลดราคา ก็ทำแบบนี้ได้ ซึ่งจะได้ผลกลับมาเป็น function object
```
lambda OriginalPrice,DiscountRate=0.2 : OriginalPrice*(1-DiscountRate)
```
แล้วเราสามารถทดสอบฟังก์ชันนี้ ด้วยการใส่วงเล็บต่อท้าย คล้ายๆ กับการทดสอบ LAMBDA ใน Excel เป๊ะๆ เลย เช่น
```
(lambda OriginalPrice,DiscountRate=0.2 : OriginalPrice*(1-DiscountRate))(200,0.1)
```
นอกจากนี้เราสามารถเก็บเจ้า function object เนี่ยเข้าไว้ในตัวแปร แล้วเรียกใช้ตัวแปรนั้นเหมือนเป็นฟังก์ชันได้ด้วย (คล้ายกับใน Excel ที่เอา LAMBDA ไปใส่ใน Name Manager) เช่น
```
AfterDiscountLam=lambda OriginalPrice,DiscountRate=0.2 : OriginalPrice*(1-DiscountRate)
```
แต่ปกติแล้วเรามักจะเรียกใช้ Lambda Function นี้แบบเร็วๆ ในฟังก์ชันอื่นอีกที หรือ เรียกใช้ในการ Transform อะไรซักอย่าง เช่น ทำใน List Comprehension, Filter, Map, Apply อะไรแบบนี้ซะมากกว่า
เช่น ผมจะเพิ่มคอลัมน์ชื่อที่เป็นพิมพ์ใหญ่ ผมจะใช้ upper ตรงๆ กับ series ของ DataFrame ไม่ได้นะ เพราะ upper มันเป็น String Method แต่ผมอาจจะเขียนแบบโดยใช้ apply หรือ map มาช่วยอีกทีได้
```
df["NameUpper"]=df["Name"].apply(lambda x:x.upper())
df
```
ตอนต่อไป
เดี๋ยวตอนต่อไปจะพูดถึงเรื่องของการทำกราฟด้วย Python ด้วย Matplotlib แล้วค่อยไปต่อเรื่อง Machine Learning ครับ
October 1, 2023
แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel
หากใครได้ลองใช้ Power Query คงจะพอรู้ว่ามันช่วยลดเวลาในการทำงานของเราได้มหาศาลแค่ไหน (บอกเลยว่างานเตรียมข้อมูลนี่ผมใช้แต่ Power Query ล้วนๆ ไม่ได้แตะสูตร Excel ปกติมานานมากแล้ว ) แต่ก็ปฏิเสธไม่ได้เหมือนกันว่าเครื่องมือและฟังก์ชันสำเร็จรูปที่มาพร้อมกับตัวโปรแกรมนั้นก็ยังไม่สามารถตอบโจทย์การทำงานของเราได้เต็มที่
ดังนั้นผมจึงคิดว่า ทำไมเราไม่เขียนฟังก์ชันที่ตอบโจทย์ขึ้นมาเองซะเลยล่ะ! จะได้แก้ Pain Point และให้การใช้งาน Power Query ง่ายขึ้นด้วย
ในบทความนี้ผมก็จะมาแนะนำให้รู้จักว่าผมเขียนฟังก์ชันอะไรเจ๋งๆ ออกมาใน Package ThepExcel-Mfx บ้าง และความสามารถของมันคืออะไร? ซึ่งจะใช้ได้ทั้งกับ Excel และ Power BI เลยครับ สะดวกมากๆ ยกตัวอย่างสิ่งที่ผมทำไว้ เช่น
- ดึงเฉพาะตัวเลขออกมาจากข้อความ
- แปลงหัวตารางหลายชั้นให้เป็นหัวตารางชั้นเดียวแบบสูตรเดียวจบ
- เปลี่ยนชื่อคอลัมน์ได้ด้วยลำดับคอลัมน์ แทนที่จะต้องอ้างอิงชื่อเดิม
- ย้านคอลัมน์ไปหน้าสุดได้ โดยไม่ต้องอ้างอิงชื่อคอลัมน์อื่นที่ไม่เกี่ยวข้อง
- แทนที่ข้อความทีเดียวหลายๆ ค่า
และยังมีอีกมากมาย ลองอ่านดูได้เลยครับ ว่ามีตัวไหนน่าสนใจบ้าง (จะมีเพิ่มขึ้นเรื่อยๆ ด้วยนะ)
Trim แบบเดียวกับ Excel ด้วย ThepTrim
ใน Power Query นั้นมี Trim แต่ว่ามันดันไม่ตัดเว้นวรรคตรงกลาง วิธีแก้ปัญหาที่ค่อนข้างดีคือสร้างฟังก์ชันขึ้นมาให้มันทำแบบเดียวกับ Excel ซะเลย
คุณสามารถเอา M code นี้ไปใช้เป็นฟังก์ชันของตัวเองได้ เช่น ตั้งชื่อว่า ThepTrim ก็ได้
```
(OriginalText as text,optional TrimChar as text) as text=>
let
    // MCode created by ThepExcel.com
    TrimCharFix = if TrimChar is null then " " else TrimChar,
    SplitStep = Text.Split(OriginalText,TrimCharFix),
    RemoveBlankStep = List.Select(SplitStep, each _ <> ""),
    CombineStep = Text.Combine(RemoveBlankStep,TrimCharFix)
in
CombineStep
```
วิธีโหลด Package ThepExcel-Mfx
แต่ก่อนจะใช้ฟังก์ชันที่ผมเขียนได้ ก็ต้องมีฟังก์ชันที่ผมเขียนก่อนนะครับ
ใครสนใจไฟล์ Package ThepExcel-Mfx ที่ผมทำขึ้นมานี้สามารถโหลดไฟล์ได้ครับ
มีทั้งแบบฟรี และแบบ Premium (ใครที่อยากได้ฟังก์ชันเจ๋งๆ เยอะกว่า หรือ อยากสนับสนุนผมก็ซื้อแบบ Premium ได้ครับ)
ซึ่งทั้ง 2 แบบผมจะมีการเพิ่มฟังก์ชันให้เรื่อยๆ ตาม Feedback ของคนใช้งานด้วยครับ
- Version ฟรี สามารถโหลดไฟล์ได้ที่นี่ / หรือดู code ใน GitHub ที่นี่
- Version Premium สั่งซื้อได้ที่นี่ (ใช้ได้ตลอดชีวิต ในราคา 590 บาท )
  (หลังจากซื้อและได้รับการตรวจสอบแล้ว ผมจะ Add คุณเข้า Google Group ที่จะมีสิทธิ์โหลดไฟล์แบบ Premium ได้ครับ)
1. พอมีไฟล์ของผมแล้วทำดังนี้ ให้ Copy Query Folder ที่ชื่อ ThepExcel-Mfx
2. จากนั้นเอาไป Paste ใส่ในไฟล์ Excel หรือ Power BI ของตัวเองได้เลย
เพียงเท่านี้คุณก็จะสามารถใช้ฟังก์ชันพิเศษที่ผมเขียนเอาไว้ได้แล้วครับ
แนะนำฟังก์ชันใน Package ThepExcel-Mfx
ให้ตารางข้างบนเป็น Query ที่ชื่อว่า MyTable ซึ่งจะใช้อ้างอิงกับหลายๆ ตัวอย่างนะครับ
กลุ่มที่อยู่ใน Package Free (ใน Premium ก็มี)
ThepDatefromText : เปลี่ยนข้อความ ให้เป็นวันที่ แถมเลื่อนปีได้
```
=ThepDatefromText(DateText as text,DateFormat,optional offsetYear as number)as date
```
สามารถกำหนดได้ให้อ่าน format แบบไหน โดยมันจะสนใจแค่การเรียงของตัวเลขเท่านั้น ไม่สนตัวคั่น
สามารถเลื่อนปีให้น้อยลง 543 ปีได้ เพื่อแก้ปัญหาการกรอก พ.ศ. <-> ค.ศ.
ThepExtractNumber : ดึงเฉพาะตัวเลขจากข้อความ เลือกได้ว่าจะเอาจุดกับ space หรือไม่
```
=ThepExtractNumber(OriginalText as text,optional Keepdot as logical, optional Keepspace as logical) as text
```
เก็บเฉพาะตัวเลข ถ้าไม่ระบุอะไรเพิ่ม คือจะเก็บ . และ space ไว้ด้วย
ThepGetColumnName : ดึงเอาชื่อคอลัมน์จากลำดับตัวเลข
```
=ThepGetColumnName(TableName as table,ColNumber as number) as text
```
ดึงค่าว่าคอลัมน์ที่ 3 ของตาราง MyTable ชื่อว่าอะไร
ThepTrim : Trim แบบเดียวกับ Excel ที่เราคุ้นเคย แต่เลือกอักขระที่จะ Trim ได้ด้วย
```
=ThepTrim(OriginalText as text,optional TrimChar as text) as text
```
ถ้าไม่ระบุอะไรก็จะ Trim ข้อความที่เป็น Space ออกไป ทั้งหน้า หลัง และตรงกลางจะเหลือแค่เคาะเดียว
แต่ถ้าระบุอักขระตัวไหนก็จะ Trim ตัวนั้นออกแทน space
ThepGetMultipleListItem : ดึงข้อมูลจาก List หลายๆ ไอเท็มพร้อมกันได้
```
=ThepGetMultipleListItem(OriginalList as list,PosIndex as list) as list
```
โดยระบุ PosIndex เป็น List ที่เก็บ index ของ item ที่ต้องการไว้ (index ของ list เริ่มต้นที่เลข 0)
สามารถระบุ Index ติดลบเพื่อเอาข้อมูลนับจากหลังสุดได้ (คล้าย Python)
ThepRenameColumn : เปลี่ยนชื่อคอลัมน์โดยระบุตำแหน่ง (ระบุเป็น list ได้ )
```
=ThepRenameColumn(TableName as table,ColNumber as any,NewName as any) as table
```
แบบเปลี่ยนแค่คอลัมน์เดียว
แบบเปลี่ยนคอลัมน์ที่ 1 3 และ 4 พร้อมกัน (ถ้าใส่ลำดับคอลัมน์เป็น -1 ก็จะหมายถึงคอลัมน์สุดท้าย)
ThepGenDateTableFromDate : สร้างตารางจากวันที่เริ่มต้นและสิ้นสุดที่กำหนด
```
=ThepGenDateTableFromDate(StartDate as date,EndDate as date) as table
```
สามารถกด Invoke ฟังก์ชันแล้วระบุวันที่จากปฏิทินได้เลย
ThepGenDateTableFromText : สร้างตารางจากข้อความวันที่เริ่มต้นและสิ้นสุดที่กำหนด
```
=ThepGenDateTableFromText(StartDateText as text,EndDateText as text) as table
```
คล้ายๆ กับฟังก์ชันก่อนหน้า แต่คราวนี้ ระบุ Date ให้เป็น text รูปแบบ yyyymmdd ได้เลย
สร้างวันที่ตั้งแต่ 1 Jan 2020 ถึง 15 Jan 2020
ThepReplaceAllError : เปลี่ยน Error ทั้งตารางให้กลายเป็นตัวที่ต้องการ
```
=ThepReplaceAllError(TableName as table,optional ReplaceWith as text) as table
```
เดิมมี Error หลายจุด
สามารถสั่งให้กลายเป็นค่าอะไรก็ได้ เช่น ค่าว่าง null
ThepRegExExtract : ดึงข้อความที่ตรงกับ RegEx Pattern ที่ระบุออกมา
ThepRegExMatchCount : นับได้ว่าเจอผลลัพธ์ตาม Pattern กี่ชุด (ไม่สนใจ capturing Group)
ThepRegExReplace : แทนที่ข้อความที่ตรงกับ Pattern ที่ระบุ

วิธีใช้ Regular Expression (RegEx) ใน Power Query

ThepOneHot : แปลงคอลัมน์ที่มีหลายๆ item เป็น Category ให้กลายเป็นคอลัมน์ที่มีค่าเป็น 1 กับ 0 (One Hot Encoding)
```
=ThepOneHot(TableName as table,TargetColumnName as text) as table
```
ThepThaiNumbertoArabic : แปลงเลขไทยเป็นเลขอารบิก
```
= ThepThaiNumbertoArabic(OriginalText as text) as text
```
กลุ่มที่อยู่ใน Package Version Premium เท่านั้น
ThepReplaceTextList : แทนค่าในข้อความโดยใส่ค่าทีเดียว
หลายๆ ตัวโดยใช้ List ได้
```
=ThepReplaceTextList(OriginalText as text,OldTextList as list,NewTextList as list) as text 
```
ThepFlattenHeader : เปลี่ยนหัวตารางหลายชั้นให้กลายเป็นชั้นเดียว แถมใส่ตัวคั่นได้
```
=ThepFlattenHeader(TableName as table,NumRowHeader as number,optional Seperator as text, optional reverse as logical) as table
```
หัวตาราง 2 ชั้น ช่างช้ำใจ…
ถ้าไม่ระบุตัวคั่นก็จะต่อกันเลย และลำดับจะย้อนกลับจากหัวตารางล่างไปบนให้ด้วย
สามารถใส่ตัวคั่น เช่น – และระบุเป็น false เพื่อไม่ต้องกลับลำดับบนล่างของหัวตาราง
ThepGetColumn : ดึงเอาคอลัมน์จากลำดับตัวเลขเริ่มและสิ้นสุด
```
=ThepGetColumn(TableName as table,StartColumnNum as number,optional EndColumnNum as number) as table
```
เอาเฉพาะคอลัมน์ 2 ถึง 4 จากตาราง MyTable มาได้เลย
ThepMoveColumn : ย้ายคอลัมน์แบบไม่อ้างอิงชื่อคอลัมน์อื่นที่ไม่เกี่ยวข้อง
```
=ThepMoveColumn(TableName as table,ColumnName as any,optional MoveFirst as logical) as table
```
ย้ายคอลัมน์ลูกค้ามาซ้ายสุดของตาราง โดยสูตรไม่อ้างอิงชื่อคอลัมน์อื่น
ย้ายคอลัมน์ลูกค้ากับสินค้า (ระบุเป็น List ได้) ไปขวาสุดของตาราง ( ระบุ MoveFirst เป็น false) โดยสูตรไม่อ้างอิงชื่อคอลัมน์อื่น
ThepExpandAllColumn : แตกคอลัมน์ออกมาจากทุกตารางแบบไม่ฝังชื่อคอลัมน์ กำหนด prefix ได้
```
=ThepExpandAllColumn(TableName as table,optional prefix as text) as table
```
ตารางที่จะ Expand ออกมาจะต้องอยู่ที่คอลัมน์ที่ชื่อว่า Data เท่านั้น เช่น มีข้อมูลก่อนจะ Expand ดังนี้
เราสามารถเรียกฟังก์ชันมา Expand คอลัมน์ทั้งหมดได้ (โดยมันจะ Scan คอลัมน์ทั้งหมดใหม่เสมอ) ดังนี้
หรือเราจะระบุ Prefix ของคอลัมน์ที่ Expand ออกมาก็ได้ เช่น
ThepMergeTableOffsetRow : ดึงข้อมูล 2 ตารางมาประกบกัน สามารถเหลื่อมแถวได้
```
=ThepMergeTableOffsetRow(TableName1 as table,TableName2 as table, RowOffset as number) as table
```
สั่งให้ Merge กับตารางตัวเอง โดยเลื่อนแถว -1 คือ เอาข้อมูลของแถวก่อนหน้ามาได้
ThepSearchColumn : เลือกเอาเฉพาะคอลัมน์ที่มีคำที่กำหนด แถมปะเพิ่มคอลัมน์ที่ต้องการได้
```
=ThepSearchColumn(TableName as table,FindColText as text,optional MatchType as text,optional ColtoKeep as list) as table
```
ตัว parameter MatchType สามารถระบุ option เป็นข้อความได้ดังนี้
- contain : หาที่มีคำนั้นอยู่ข้างใน (ค่า Default กรณีไม่ระบุ MatchType)
- start: หาที่มีคำนั้นนำหน้า
- end : หาที่มีคำนั้นลงท้าย
หาคอลัมน์ที่มีคำว่า ชิ้น อยู่
หาคอลัมน์ที่มีคำว่าชิ้น และปะคอลัมน์ TXID กับ สินค้าเข้าไปด้วย
ThepRegExExtractAll : ดึงผลลัพธ์ทุกตัวทุก SubGroup ออกมาเป็น List

วิธีใช้ Regular Expression (RegEx) ใน Power Query

เทคนิคที่ทำให้ใช้สูตรของผมได้ง่ายขึ้น (ไม่จำเป็นต้องทำก็ได้)
ปกติแล้ว การ Copy Paste Query ข้ามไฟล์ ถ้า Code ใน Query นั้นๆ ดันไม่ได้เริ่มด้วย let…in… ตัวโปรแกรมจะใส่ Code let…in…เพิ่มมาให้เองอัตโนมัติ
ซึ่งมีข้อเสียคือ เวลาเรียกใช้ฟังก์ชันใน Formula Bar มันจะมองไม่เห็น ToolTips เช่นในรูป (แต่ถ้ากด Invoke Function จะเห็นปกตินะ)
ซึ่งถ้าอยากให้ตอนเขียนสูตรใน Formula Bar เห็น Tooltips ตามปกติตามรูปข้างล่าง ก็มีอยู่ 3 way ที่จะแนะนำ คือ
- Way 1: ให้เอาไฟล์ของผม copy paste เป็นไฟล์ตั้งต้นของคุณเวลาจะเขียน Query ไปเลย (โดยไม่ต้องใช้การ copy query)
- Way 2 : เปิด Advanced Editor แล้ว copy M code จากไฟล์ผมไป Paste ใน Blank Query ของคุณตรงๆ
- Way 3 : ใช้การ Copy Query นี่แหละ แต่ให้เข้า Advanced Editor แล้วเอาคำว่า let…in… source ที่ มาครอบอยู่ออกไปซะครับ
เดี๋ยวขออธิบาย Way3 เพิ่มเติม… เช่น สูตรในฟังก์ชัน ThepTrim หลังจาก Copy Query มาจะเป็นแบบนี้ (มีสีแดงๆ เพิ่มมาโดยอัตโนมัติ ให้คุณเอามันออกไปซะ)
ดู code แบบนี้ได้ใน View -> Advanced Editor นะครับ
```
let
     Source = (OriginalText as text,optional TrimChar as text) as text=>
 let
     TrimCharFix = if TrimChar is null then " " else TrimChar,
     SplitStep = Text.Split(OriginalText,TrimCharFix),
     RemoveBlankStep = List.Select(SplitStep, each _ <> ""),
     CombineStep = Text.Combine(RemoveBlankStep,TrimCharFix)
 in
 CombineStep
 in
     Source
```
พอเอาสีแดงๆ ออกไปจะเหลือเป็นแบบนี้ ซึ่งจะใช้สูตรได้ดีขึ้น
```
(OriginalText as text,optional TrimChar as text) as text=>
 let
     TrimCharFix = if TrimChar is null then " " else TrimChar,
     SplitStep = Text.Split(OriginalText,TrimCharFix),
     RemoveBlankStep = List.Select(SplitStep, each _ <> ""),
     CombineStep = Text.Combine(RemoveBlankStep,TrimCharFix)
 in
 CombineStep
```
June 22, 2021
หัด Python สำหรับคนเป็น Excel : ตอนที่ 5 – การสร้างฟังก์ชันขึ้นมาใช้เอง (Function)
เวลาเราเขียนโปรแกรม เราสามารถสั่งให้โปรแกรมทำงานกับข้อมูลด้วยคำสั่งต่างๆ อย่างที่เราได้เรียนรู้ไปในตอนที่แล้ว อย่างไรก็ตาม คำสั่งมาตรฐานอาจจะไม่เพียงพอต่อความต้องการของเรา ดังนั้นวิธีการที่จะช่วยได้คือ เราจะสร้างฟังก์ชันที่ทำงานได้ตรงใจเราขึ้นมาเอง
ลองคิดดูว่าใน Excel version เก่า ยังไม่มีฟังก์ชัน UNIQUE การจะตัดให้เหลือข้อมูลที่ไม่ซ้ำกันมันก็จะยุ่งยาก ต้องเขียนสูตรซ้อนกันหลายบรรทัด แต่พอมีคนสร้างฟังก์ชัน UNIQUE ให้ใช้ใน Excel365 หรือ Excel Online ปุ๊ป ชีวิตก็จะง่ายขึ้นทันที อารมณ์ของการมีฟังก์ชันเจ๋งๆ มันก็คล้ายๆ แบบนี้แหละครับ
การที่มีฟังก์ชันที่ทำงานได้ดั่งใจ ช่วยให้เราสามารถทำงานได้ง่ายขึ้นมากๆ ดังนั้นบทความนี้เราจะมาสร้างฟังก์ชันใช้เองกันครับ
Function คืออะไร?
ฟังก์ชันคือชุดคำสั่งที่จะทำงานเมื่อเราสั่งมัน โดยที่เราสามารถใส่ input เข้าไปใน Function แล้วมันจะทำงานแล้วสามารถคืนค่าผลลัพธ์เป็น Output ออกมาได้ด้วย (หรือจะทำงานเฉยๆ ไม่คืนค่าก็ได้)
แน่นอนว่าคนที่ใช้ Excel มาอย่างพวกเราน่าจะคุ้นเคยกับฟังก์ชันเป็นอย่างดี ซึ่งใน Excel ก็มีฟังก์ชันให้ใช้หลากหลาย เช่น
- ฟังก์ชัน LEN (text) สามารถนับจำนวนอักขระใน text ได้ว่ามีกี่ตัว
- ฟังก์ชัน LEFT (text, [num_chars]) เอาไว้ดึงข้อความ text จากด้านซ้าย ตามจำนวนตัวอักษร num_chars ที่เราต้องการ
- ฟังก์ชัน EDATE (start_date, months) เอาไว้เลื่อนวันจาก start_date ไปตามจำนวนเดือน months ที่ระบุ
- ฟังก์ชัน NOW() เอาไว้แสดงวันที่และเวลาปัจจุบัน
ฟังก์ชันแต่ละอันต้องการจำนวน input ไม่เท่ากัน เช่น EDATE กับ LEFT ต้องการ 2 ตัว, LEN ต้องการ 1 ตัว, ส่วน NOW ไม่ต้องการเลย นอกจากนั้น input บางตัวก็เป็นค่า optional คือจะระบุหรือไม่ก็ได้ เช่น [num_chars] ใน LEFT ถ้าไม่ระบุแปลว่าเอา 1 ตัวเป็นต้น
ซึ่งใน Python เราก็สามารถสร้างฟังก์ชันที่ต้องการ input เข้าไปประมวลผลได้เหมือนกัน และทำให้ input เป็น optional หรือไม่ก็ได้ด้วย
วิธีการสร้างฟังก์ชันใน Python
เราสามารถสร้างฟังก์ชันได้ด้วยคำสั่งรูปแบบนี้ (ย่อหน้าข้างใน def สำคัญมาก เป็นตัวระบุว่าบรรทัดไหนคือส่วนของฟังก์ชัน)
```
def ชื่อฟังก์ชัน():
  คำสั่งที่ต้องการให้ทำในฟังก์ชันนั้นๆ
```
โดยที่สามารถระบุ input ได้ด้วยแบบนี้
```
def ชื่อฟังก์ชัน(input1,input2):
  คำสั่งที่ต้องการให้ทำในฟังก์ชันนั้นๆ
```
และสามารถกำหนดค่า default ของ input แต่ละตัวได้ด้วยการสั่งแบบนี้
```
def ชื่อฟังก์ชัน(input1=def1,input2):
  คำสั่งที่ต้องการให้ทำในฟังก์ชันนั้นๆ
```
นอกจากนั้นยังสามารถให้มันคืนค่าผลลัพธ์ออกมาได้เช่นกัน ด้วยคำสั่ง return แบบนี้
```
def ชื่อฟังก์ชัน(input1=def1,input2):
  คำสั่งที่ต้องการให้ทำในฟังก์ชันนั้นๆ
  return ผลลัพธ์
```
และเราสามารถเรียกใช้ฟังก์ชันนั้นๆ ได้โดยเรียกชื่อฟังก์ชันตามด้วย input ในวงเล็บ ซึ่งเหมือนกับการเรียกใช้สูตร Excel เลย
มาดูตัวอย่างกันดีกว่าครับ
ตัวอย่างการเขียนฟังก์ชัน
Print ตามจำนวนครั้ง
เริ่มจากตัวอย่างพื้นฐาน คือให้ print คำที่ระบุ ตามจำนวนครั้งที่ต้องการ โดยถ้าไม่ระบุจำนวนครั้งจะให้ print ครั้งเดียว ซึ่งผมจะตั้งชื่อฟังก์ชันว่า printX (เท่มะ?) สามารถทำได้ดังนี้
```
def printX(text,num_time=1):  #สร้างฟังก์ชัน printX
  for i in range(num_time): #วน loop ตามจำนวนครั้งที่ระบุ
    print(text)

printX("เทพมาก",3)  #เรียกใช้ฟังก์ชัน printX ที่สร้างไว้
```
เลียนแบบ LEFT ใน Excel
ใน Python ไม่มีฟังก์ชัน LEFT เหมือน Excel แต่เราจะลองสร้างมันขึ้นมาใช้งานบ้าง ซึ่งทำได้ดังนี้
```
def LEFT(text,num_chars=1): #สร้างฟังก์ชัน LEFT
  return text[:num_chars]

print(LEFT("ThepExcel",4)) #เรียกใช้ฟังก์ชัน LEFT ที่สร้างไว้ แบบระบุ 4 ตัว
print(LEFT("ThepExcel")) #เรียกใช้ฟังก์ชัน LEFT ที่สร้างไว้ แบบไม่ระบุจำนวน (default คือ 1)
```
เลียนแบบ TRIM ใน Excel
TRIM ใน Excel สามารถ TRIM ช่องว่างตรงกลางได้ด้วย แต่ใน Python เอาตรงกลางไม่ออก (เอาออกแต่หน้าหลัง) เราก็สามารถสร้าง TRIM มาใช้เองใน Pythonได้ แต่จะทำให้เจ๋งขึ้นอีกคือระบุตัวอักขระที่จะ Trim ออกได้ด้วย และเอาตรงกลางออกได้ด้วย ดังนี้
```
def TRIM(text,trim_char=" "): #สร้างฟังก์ชัน TRIM โดยให้ตัวที่จะ Trim เป็นเครื่องหมาย space
  list1=text.split(trim_char) #split แตก item ออกมาตามตัวคั่น
  print(list1)
  list2=[x for x in list1 if x!='']  #คัดเลือกเอา item ที่ไม่ใช่ช่องว่าง
  print(list2)

  #เอาข้อความใน list2 มาต่อกันด้วย trim_char เหมือนเดิมด้วย ตัวคั่น.join(list)
  outputText=trim_char.join(list2)  
  return outputText

print(TRIM("  Thep    Excel   "))
print(TRIM("...Thep.....Excel....","."))
```
เวลาใช้จริงก็ไม่ต้อง print step ระหว่างทางหรอก (อันนีั้นเราทำเพื่อตรวจสอบผลลัพธ์) ดังนั้นให้ comment ทิ้งไปเลยแบบนี้ก็ได้
ซึ่งแน่นอนว่าเราเอาฟังก์ชันที่สร้างไปใช้ประโยชน์ได้มากมาย เช่น เอาไปวน Loop ใน List เพื่อ clean ข้อมูลที่ต้องการก็ได้
```
def TRIM(text,trim_char=" "): #สร้างฟังก์ชัน TRIM 
  list1=text.split(trim_char) 
  list2=[x for x in list1 if x!='']  

  outputText=trim_char.join(list2)  
  return outputText

OriginalList=["  Thep    Excel   ","  Sira  Ekabut     "," Eren  Yeager "]
FixedList=[TRIM(x) for x in OriginalList] #เรียกใช้ฟังก์ชันใน List Comprehension ก็ได้
print(FixedList)
```
Recursion
Python นั้นรองรับการสร้างฟังก์ชันที่เรียกใช้งานตัวเองได้ด้วย
ยกตัวอย่างเช่น การสร้างการคำนวณ Factorial นั่นคือเอาเลขคูณกับตัวที่น้อยกว่าไปเรื่อยๆ จนถึง 1 (โดยที่ Factorial 0 มีค่าเป็น 1)
```
def FACT(num): #สร้างฟังก์ชัน Factorial
  if num==0:
    return 1   #ถ้า FACT(0) ให้เป็น 1
  else:
    return num*FACT(num-1)  #นอกนั้นให้เอาเลขนั้น*FACT(เลขน้อยลง)

print(FACT(0))  #คือ 1
print(FACT(1))  #คือ 1*1
print(FACT(2))  #คือ 2*1*1
print(FACT(3))  #คือ 3*2*1*1
print(FACT(4))  #คือ 4*3*2*1*1
print(FACT(10))  #คือ 10*9*8*7*6*5*4*3*2*1*1
```
Lambda Function
นอกจากการสร้างฟังก์ชันขึ้นมาแบบจริงจังด้วย def แล้ว ใน Python ยังยอมให้เราสร้างสิ่งที่เรียกว่า Lambda Function (เป็น Anonymous Function หรือฟังก์ชันที่ไม่จำเป็นต้องมีชื่อ ) ขึ้นมาด้วย ซึ่งมันเป็นเหมือนการสร้างฟังก์ชันย่อมๆ ที่มี expression ได้แค่อันเดียว แต่จะมี input กี่ตัวก็ได้ ในรูปแบบ
```
lambda input1,input2,input3,... : expression
```
เช่น
```
myFunc=lambda a,b,c : (a*b)+c
print(myFunc(2,3,4))
```
มีค่าเท่ากับการทำแบบนี้เลย
ตอนต่อไป
ในที่สุดเราก็สามารถสร้างฟังก์ชันขึ้นมาใช้เองได้แล้ว แต่จะดีกว่ามั้ยถ้าเราจะใช้ฟังก์ชัน/ความสามารถเจ๋งๆ ที่คนอื่นได้สร้างไว้แล้วเต็ม internet เลย? ในตอนหน้าเราจะมาพูดถึงการใช้ Module/Packages กันครับ! นี่แหละสิ่งที่ทรงพลังที่สุดในความคิดของผม รอติดตามได้เลย เจ๋งแน่นอน
สารบัญ Series Python

หัดเขียนโปรแกรม Python สำหรับคนเป็น Excel มาก่อน : ตอนที่ 1

หัด Python สำหรับคนเป็น Excel : ตอนที่ 2 – ประเภทข้อมูล (Data Types)

หัด Python สำหรับคนเป็น Excel : ตอนที่ 3 – การวน Loop และ เงื่อนไข if

หัด Python สำหรับคนเป็น Excel : ตอนที่ 4 – การทำงานกับ String และ List

หัด Python สำหรับคนเป็น Excel : ตอนที่ 5 – การสร้างฟังก์ชันขึ้นมาใช้เอง (Function)

หัด Python สำหรับคนเป็น Excel : ตอนที่ 6 – การเรียกใช้ Module / Packages เจ๋งๆ ที่มีคนสร้างไว้แล้ว

หัด Python สำหรับคนเป็น Excel : ตอนที่ 7 – Web Scraping ด้วย Beautiful Soup

แนวทางการใช้ Python ใน Power BI

หัด Python สำหรับคนเป็น Excel : ตอนที่ 8 – การสร้างกราฟด้วย Matplotlib

รวม Link สอน Python / Programming / AI/ Machine Learning แบบฟรีๆ

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

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

สอนใช้ Python ใน Excel ตอนที่ 3 : Regular Expression (RegEx)

สอนใช้ Python ใน Excel ตอนที่ 4 : สร้างฟังก์ชันใช้เอง

สอนใช้ Python ใน Excel ตอนที่ 5 : สร้างกราฟ Visualization เบื้องต้นด้วย Matplotlib

การวิเคราะห์ข้อมูลเบื้องต้นด้วย Python: เริ่มต้นด้วย Pandas และ Matplotlib

เพิ่มยอดขายด้วย Market Basket Analysis : วิเคราะห์คู่สินค้าขายดีด้วย Python
November 25, 2020
List ฟังก์ชันทั้งหมดของ Excel ในทุก Version
คุณสงสัยหรือไม่ว่า Excel แต่ละ Version มีฟังก์ชันอะไรให้ใช้แตกต่างกันบ้าง? แม้ว่าทาง Microsoft จะมีเว็บบอกไว้ แต่มันช่างใช้งานยากเหลือเกินว่าตกลงแล้ว Excel แต่ละ Version มี Function อะไรกันแน่
- ตอนนี้ผมได้ทำการสร้าง “หน้าอธิบายวิธีการใช้ฟังก์ชัน Excel ทั้งหมด 500 กว่าฟังก์ชัน” เอาไว้ในนี้ครับ
  - ฟังก์ชันทั้งหมด (มีให้ AI ช่วยเขียน ยังไม่สมบูรณ์): https://www.thepexcel.com/category/references/excel-functions/
  - ฟังก์ชันที่ผมตรวจแล้ว (Approved):
    https://www.thepexcel.com/tag/approved/
ผมก็เลยทำรายงาน Power BI และไฟล์ Excel ขึ้นมารวบรวมไว้ให้เผื่อคนที่อยากจะรู้นะครับ
- 2022/08/22 Update Function ใหม่ๆ และใส่ Link
- 2022/11/15 ทำเป็น Report Power BI
- 2024/11/06 ทำให้ Report Power BI อัปเดทอัตโนมัติ
แล้วเครื่องมืออื่นๆ ล่ะ?
สำหรับเรื่องที่ Excel แต่ละ Version มีฟังก์ชันไม่เท่ากันนั้น จริงๆ แล้วนอกจาก Version แล้วก็ยังมีเรื่องของเครื่องมืออื่นๆ ด้วย ดังนั้นผมจึงสรุปออกมาเป็น Diagram ให้ด้วยดังนี้ครับ เพราะหลายคนก็สับสนอยู่ว่าอะไรใช้กับ Version ไหนได้บ้าง?
September 7, 2020
คัมภีร์สรุป M Code ใน Power Query ตอนที่ 2 : Function และ each
ในตอนที่แล้วเราได้เรียนรู้พื้นฐานของการใช้ M Code กันไปแล้ว คราวนี้เราจะมาเรียนรู้ถึงสิ่งที่ทรงพลังที่สุดของ M Code นั่นก็คือเรื่องของหลักการของการใช้ Function นั่นเอง
เมื่อคุณใช้งาน Power Query ไปเรื่อยๆ จุดที่บ่งบอกว่า คุณกำลังจะก้าวไปสู่ความสามารถอีกขึ้นหนึ่งของ Power Query ก็คือ ความสามารถในการใช้งาน Custom Function นี่แหละครับ ซึ่งหากใช้เป็น คุณจะสามารถแก้ไขปัญหาที่ซับซ้อนได้มากขึ้นกว่าการใช้เครื่องมือมาตรฐานมากมายหลายเท่าเลย
Function คือ ขุมพลังที่แท้จริงของ M Code
Function (ฟังก์ชัน) คือ สิ่งที่สามารถรับค่า input เข้ามาคำนวณประมวลผล แล้วส่งผลลัพธ์ output ออกมาได้
รูปแบบ
```
(input1,input2,...) => expression วิธีคำนวณหรือสูตรของฟังก์ชันนั้นๆ
```
เช่น จะสร้างฟังก์ชันของตัวเองขึ้นมา (เรียกว่า Custom Function) เอาไว้หาพื้นที่สามเหลี่ยม
```
(ฐาน,สูง) => 0.5*ฐาน*สูง   //เราตั้งชื่อ input เป็นภาษาไทยก็ได้นะ แต่ผมแนะนำให้ตั้งเป็น eng แหละดีแล้ว
```
การจะเข้าใจเรื่องนี้ได้ดีขึ้น ผมอยากให้นึกถึงฟังก์ชันของ Excel เป็นตัวเปรียบเทียบครับ ยกตัวอย่างเช่น ฟังก์ชัน LEFT ใน Excel มีรูปแบบดังนี้
```
=LEFT(text,num_chars) //จะเห็นว่ามี input 2 ตัวคือ text และ num_chars
```
ซึ่งถ้าเราใส่ input 2 ตัวนี้เข้าไป LEFT ก็จะทำการดึงบางส่วนของข้อความ text ออกมาจากทางซ้าย ด้วยจำนวนตัวอักษระที่ระบุใน num_chars เช่น =LEFT(“abcde”,2) จะได้ผลลัพธ์เป็น 2 ตัวซ้าย นั่นคือ “ab” เป็นต้น
หน้าที่ของฟังก์ชัน
```
รับค่า input →  เอาไปทำอะไรซักอย่าง (processing) → แล้วคายค่าผลลัพธ์ output ออกมา
```
สามารถระบุประเภท Data Type ได้
```
(input1 as text ,input2 as number) as text => expression
```
สามารถกำหนด input เป็น optional ได้
```
(input1 as text ,optional input2 as number) as text => expression 
```
ซึ่งตัวที่เป็น optional หากไม่ใส่ค่ามาจะมีค่าเป็น null ดังนั้นเราอาจต้องกำหนดว่า ถ้าค่า input2 เป็น null จะให้ทำอะไรแทน เป็นต้น
การเรียกใช้ Custom Function
สมมติว่าเราสร้าง Query ชื่อ TriangleArea ขึ้นมาด้วย M Code ว่า
```
(ฐาน,สูง) => 0.5*ฐาน*สูง
```
เวลาเราจะใช้งาน ก็สามารถเรียกใช้ได้ใน M Code ได้เลย เช่น ให้สร้าง Blank Query อีกอันนึงในไฟล์เดิม แล้วใส่ M Code ว่า
หรือจะใช้ผ่าน Invoke Custom Function ก็ได้ เช่น
ซึ่งมันก็จะออกมาเป็นแบบนี้
การใช้ each หรือ ฟังก์ชันแบบย่อๆ
เคยเห็น each ที่โผล่มาตอนเรียกคำสั่งต่างๆ มั้ย?
หลายๆ ครั้งเวลาที่เรากดคำสั่งเมนูมาตรฐานมันก็จะใส่ each ให้เราเอง เช่น ในตัวอย่างข้างบนก็มี each หรือว่าหากกด Add Column → Custom Column ก็จะมี each ซึ่งผมจะอธิบายโดยละเอียดให้เห็นชัดๆ ดังนี้ว่าจริงๆ แล้วมันคืออะไรกันแน่ครับ
เช่น สมมติเรามีข้อมูล ราคาสินค้า และจำนวนอยู่ แล้วอยากจะหายอดขาย
ปกติแล้วเราก็จะ Add Custom Column ได้แบบนี้
ซึ่งสูตรจะออกมาเป็น = Table.AddColumn(#”Changed Type”, “ยอดขาย”, each [ราคา]*[จำนวน])
แล้วเจ้า each มันคืออะไรล่ะ? เดี๋ยวผมจะอธิบายให้ฟัง
each คือการย่อวิธีเขียนฟังก์ชัน
จากที่เรารู้แล้วว่าเราสามารถใช้ฟังก์ชันลักษณะนี้ได้
```
(x) => x+1
```
ดังนั้นเราสามารถเปลี่ยนชื่อตัวแปรจาก x เป็น _ ได้ จะได้ว่า
```
(_) => _+1
```
ซึ่งหากใช้ชื่อตัวแปรเป็น _ เราจะสามารถย่อ Code ส่วน (_) => ได้เป็น each ดังนี้
```
each _+1
```
และถ้ามีการอ้างควบคู่กับ [ ] ที่เป็น Record lookup operator เช่น จริงๆ แล้ว _ ที่กำลังอ้างถึง คือตัว table หรือ record จะสามารถละตัว _ ทิ้งไปได้เลย เพื่อให้อ่านสูตรแล้วดูง่ายขึ้น เช่น
```
Table.SelectRows ( Source, (_) => _[Qty] > 100 )
```
จะย่อได้เป็น
```
Table.SelectRows ( Source, each _[Qty] > 100 )
```
และย่อได้อีกว่า
```
Table.SelectRows ( Source, each [Qty] > 100 )
```
each ที่โผล่มาเมื่อกด Custom Column
เดี๋ยวผมจะค่อยๆ อธิบายให้ฟังถึงเหตุผลที่ว่า ทำไมเราถึงสามารถอ้างอิงข้อมูลใน Field ที่ต้องการด้วยการใส่ [ ] ครอบลงไปได้เลย แล้วมันรู้ได้ไงว่าควรเอาข้อมูลในบรรทัดไหน
ก่อนอื่น ให้เราลองเปลี่ยนสูตรใน Custom Column ให้เหลือแค่ _ จะเห็นภาพชัดขึ้นครับ
ซึ่งสูตรจะออกมาเป็น
```
= Table.AddColumn(#"Changed Type", "ยอดขาย", each _ )
```
แต่จะเห็นว่าในคอลัมน์ยอดขายจะได้ผลลัพธ์ออกมาเป็น Record ในบรรทัดนั้นๆ ซึ่งประกอบไปด้วย Field ทุกอันในตารางเดิมใน Step #”Changed Type”
นั่นแปลว่า _ ใน Step การ AddColumn จะแทน Record ของแต่ละบรรทัดนั่นเอง
และการอ้างอิงแต่ละ Field ของ Record จึงทำด้วยวิธี _[ชื่อ Field]
และการที่เราใช้ _ เป็นชื่อของ input ทำให้สามารถละ _ ตอนอ้างอิงชื่อ Field ได้ ทำให้แทนที่จะเขียนเต็มๆ ว่า _[ชื่อ Field] จึงสามารถเขียนแค่ [ชื่อ Field] ได้เลย
แปลว่า ถ้าเราไม่ย่ออะไรเลย สูตรเต็มๆ จะเป็นแบบนี้ครับ
```
= Table.AddColumn(#"Changed Type", "ยอดขาย", (_)=>_[ราคา]*_[จำนวน]) 
```
โดยที่ _ ที่เป็น Input ของฟังก์ชันก็คือ Record ในแต่ละบรรทัดนั่นเอง
พอย่อ (_)=> เป็น each จะได้แบบนี้
```
= Table.AddColumn(#"Changed Type", "ยอดขาย", each _[ราคา]*_[จำนวน]) 
```
แต่พอเขียนแบบย่อสุดๆ โดยละ _ หน้า [ ] ทิ้ง เราเลยเขียนสูตรได้ว่า
```
= Table.AddColumn(#"Changed Type", "ยอดขาย", each [ราคา]*[จำนวน])
```
สรุปวิธีการอ้างอิง Record (แถว), List (ข้อมูลในคอลัมน์) และ Table (ตาราง)
ถ้าจะอ้างอิง Record ปัจจุบันที่เลือกอยู่ เรารู้แล้วว่าสามารถใส่ _ หลัง each ได้เลย
ทีนี้ถ้าเราอยากอ้างถึงอย่างอื่นบ้างล่ะ เช่น จะอ้างถึง Table ทั้งอันเลย คอลัมน์ที่ต้องการ หรือ Record ก่อนหน้า จะทำยังไง? เรามาดูทีละอันครับ
ถ้าจะอ้างอิงตารางทั้งอัน วิธีที่ง่ายที่สุด คือ ใส่ชื่อตัวแปรที่ให้ผลลัพธ์เป็นตารางลงไปได้เลยครับ เช่น ใส่ว่า Source ก็จะเอาตารางใน Step แรกสุดมา (จริงๆ ชื่อ Step แต่ละอันก็ให้ผลลัพธ์เป็นตารางอยู่แล้ว)
Tips : ถ้าอยากได้ Step อื่นก็ใส่ชื่อ Step นั้นๆ ลงไป ถ้าอยากได้ตารางอื่นก็ใส่ชื่อตัวแปรที่เป็นตารางอื่น หรือชื่อ query อื่นลงไป
ถ้าจะอ้างอิงคอลัมน์ที่ต้องการทั้งคอลัมน์ ก็ใส่ชื่อ Table ตามด้วย [ชื่อคอลัมน์] เช่น ใส่ว่า Source[ผลไม้] ผลลัพธ์ก็จะออกมาเป็น List
ถ้าจะอ้างอิงถึงแถวก่อนหน้า แปลว่าเราต้องรู้แถวปัจจุบันของตัวเองก่อนถึงจะง่าย ซึ่งวิธีที่ง่ายที่สุดคือใส่ index column ให้เริ่มตั้งแต่เลข 0 เอาไว้ เช่น
แล้วเราค่อยอ้างอิง Table แล้วใส่ row index ที่ต้องการ (index 0 คือ แถวแรกนะ อย่าลืม)
ดังนั้นเราจะใส่ใน Custom Column ว่า =Source{[Index]-1} แบบนี้ได้ครับ
ทีนี้แถวแรกมัน Error เราจะจัดการยังไงดี?
การจัดการ Error ด้วย try…otherwise…
หากเราอยากจะจัดการ Error ไม่ให้แสดงออกมา เราสามารถใช้คำสั่ง
```
try...x... otherwise ...y... 
```
มาช่วยได้ครับ
คำสั่งนี้มีวิธีทำงาน คือ หาก try x แล้วสำเร็จก็จะทำ x ไป แต่ถ้าลอง x แล้ว Error ก็จะทำ y แทน ซึ่งเราอาจจะทำให้ y เป็นอะไรก็ได้ เช่น 0 หรือปล่อยเป็น null ก็นิยมครับ
เช่น ใน Custom Column เราเพิ่มสูตรส่วนของ try และ otherwise ว่า
```
=try [ราคา]*[จำนวน] otherwise 0
```
แบบนี้มันจะเอาคอลัมน์ [ราคา]*[จำนวน] หาก error ให้แสดงค่า 0 แทน
รวมถึงการอ้างอิงแถวก่อนหน้าที่ Error ในแถวแรกสุด (เพราะไม่มีแถวก่อนหน้า) เราอาจจะให้เป็น null ก็ได้ เช่น
```
try Source{[Index]-1} otherwise null
```
แค่นี้ก็จัดการ error ที่ไม่ต้องการได้แล้วครับ
การเขียนเงื่อนไขด้วย if
สำหรับการเขียนเงื่อนไขใน M Code เราก็ใช้ if…then…else… ประกอบกับการใช้ and or not ก็ได้ครับ
อย่างตอนเขียนสูตรเพื่ออ้างอิงแถวก่อนหน้า ถ้าไม่ใช้ try…otherwise… เราจะใช้ if ก็ได้ ก็จะให้ผลลัพธ์แบบเดียวกัน
```
if [Index]=0 then null else Source{[Index]-1}
```
เจาะลึก each ด้วยการใช้ M Code เลียนแบบ VLOOKUP Approximate Match
สมมติผมมีข้อมูลคะแนนสอบอยู่แล้วต้องการจะตัดเกรด ดังนี้
ผมเอาตารางซ้ายเข้าเป็น Query ชื่อ TestResult ส่วนตารางขวาชื่อ RefGrade
ใน Query Test Result นั้นผมสร้าง Custom Column ด้วยสูตรว่า =RefGrade ก็จะได้ผลลัพธ์แบบนี้ ว่าในแต่ละบรรทัดของคะแนนสอบ เราได้ตาราง RefGrade กลับมาทั้งตารางเลย (ซึ่งเยอะไป!)
สิ่งที่เราต้องการจริงๆ คือ อยากดูว่าคะแนนที่ได้นั้นอยู่ในช่วงไหน ซึ่ง logic ที่ใช้ได้คือ ต้องทำการเลือก RefGradeมาเฉพาะบรรทัดที่คะแนนต่ำสุด น้อยกว่าหรือเท่ากับ คะแนนสอบที่ได้ในแต่ละบรรทัดเท่านั้น และค่อนเลือกเอาบรรทัดสุดท้ายมาฃ
ซึ่งเราจะพยายามเลือกให้เหลือเฉพาะบรรทัดที่ คะแนนต่ำสุด น้อยกว่าหรือเท่ากับ คะแนนสอบที่ได้ในแต่ละบรรทัดก่อน ด้วยฟังก์ชันที่ชื่อว่า Table.SelectRows ซึ่งมีวิธีการใช้ดังนี้
```
Table.SelectRows(table as table, condition as function) as table
```
จะเห็นว่า input ที่มันต้องการมี 2 ตัว คือ table ต้นฉบับ และ เงื่อนไข ซึ่ง table ต้นฉบับนี่ง่ายมาก ก็คือ RefGrade นั่นแหละ
แต่ที่ยากก็คือเจ้า condition ที่ดันต้องใส่เป็นฟังก์ชันด้วยสิ
เพื่อที่จะให้เข้าใจว่าปกติมันทำงานยังไง เราจะไปดู Query RefGrade แล้วไปลอง Filter มันเล่นๆ ดูก่อน ด้วยเงื่อนไขว่า คะแนนต่ำสุด <= 69 จะได้แบบนี้
ซึ่งเราจะเห็นฟังก์ชัน Table.SelectRows ทำงานให้เราดูเลยว่าต้องเขียนยังไง
```
= Table.SelectRows(#"Changed Type", each [คะแนนต่ำสุด] <= 69)
```
เราก็เลยคิดว่าจะเอาคำสั่งนี้กลับไปเขียนใน TestResult ที่เราค้างไว้ได้ ซึ่งมันก็ใช้ได้จริงๆ (แค่เปลี่ยนชื่อ table จาก “Changed Type” เป็น RefGrade)
และถ้าเราไม่อยากจะ hardcode เลข 69 ล่ะ?
เราอยากให้แต่ละบรรทัด มันใช้คะแนนสอบไปเป็นเงื่อนไขในการ Filter แทนการพิมพ์เลข 69 ลงไปเอง เราอาจเผลอคิดว่าจะใช้ [คะแนน] แทนได้เลย แต่มันไม่ง่ายแบบนั้นหรอก หึหึ เพราะมันจะบอกว่าหา Field ที่ชื่อว่า คะแนน ไม่เจอ
ทำไมถึงหาไม่เจอ? ก็เป็นเพราะภายใต้ each อันที่สอง มันกำลังมอง Record ของตาราง RefGrade อยู่น่ะสิ ซึ่งมันไม่มีคะแนนไง เพราะคะแนนอยู่ในตาราง TestResult ต่างหาก!!
แล้วเราจะไปอ้างอิง Field ที่อยู่ใน Current Record ของ TestResult ได้ยังไง? คราวนี้แหละเราจะไม่ใช้การย่อด้วย each แล้วเพราะมันทำให้เกิดการอ้างอิงที่ซ้ำกันแล้วโปรแกรมมันก็งง เราจะตั้งชื่อ input ด้วยตัวเราเองให้ชัดเจนไปเลยจะได้ไม่งง โดย Current Record ของ TestResult ผมจะตั้งชื่อว่า main และแต่ละ row ของ RefGrade ผมจะตั้งชื่อว่า sub ซึ่งจะแปลงสูตรได้ดังนี้
จากเดิมที่เขียนแล้วไม่ชัดเจน จึงมีปัญหา
```
= Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(RefGrade, each [คะแนนต่ำสุด] <= [คะแนน]))
```
เปลี่ยนใหม่ ให้ชัดเจนขึ้นว่าอ้างถึง Field ของ Record ไหน
```
= Table.AddColumn(#"Changed Type", "Custom",  (main) => Table.SelectRows(RefGrade, (sub)=> sub[คะแนนต่ำสุด] <= main[คะแนน]))
```
คราวนี้ผลลัพธ์ไม่ Error แล้ว
แต่จะเห็นว่ามันให้ผลลัพธ์กลับมาหลายบรรทัดอยู่ เราจะเอาแค่บรรทัดสุดท้าย ก็ใช้ Table.Last ได้ ดังนี้
```
= Table.AddColumn(#"Changed Type", "Custom", (main) => Table.Last(Table.SelectRows(RefGrade, (sub)=> sub[คะแนนต่ำสุด] <= main[คะแนน])))
```
ผลลัพธ์ออกมาเป็น Record เดียวแล้ว ก็กด expand ที่มุมขวาบนของ Field ได้เลย จะได้ผลลัพธ์สุดท้ายที่สมบูรณ์ดังรูป
ตอนต่อไป
ตอนนี้เราก็ได้เรียนรู้การใช้ฟังก์ชันเบื้องต้นกันไปแล้ว เดี๋ยวในตอนต่อไปจะป็นเรื่องของ List แบบลึกซึ้งขึ้น ซึ่งมีประโยชน์มากด้วยแน่นอนครับ ติดตามต่อได้เลย
สารบัญซีรีส์ M Code

คัมภีร์สรุป M Code ใน Power Query ตอนที่ 1

คัมภีร์สรุป M Code ใน Power Query ตอนที่ 2 : Function และ each

คัมภีร์สรุป M Code ใน Power Query ตอนที่ 3: เจาะลึกเรื่องของ List

คัมภีร์สรุป M Code ใน Power Query ตอนที่ 4: เทคนิค M Code ที่ใช้บ่อย

Power Query Speed Up Tips : รวมเทคนิคแก้ปัญหา Power Query ช้า ให้ทำงานเร็วขึ้น

สารพัดวิธีกำจัดอักขระที่ไม่ต้องการออกจากข้อความ (Clean Bad Characters)

วิธีเขียนสูตรย้อนเวลาแบบ TENET ให้ตัวอักษรกลับหน้าหลัง

เทคนิคแก้สูตร M Code ให้ Power Query ทำงานได้ดั่งใจ

วิธีใช้ Power Query ดึงข้อมูลจาก Web API : ตอนที่ 2

วิธีใช้ Power Query ดึงข้อมูลจาก Web API : ตอนที่ 3

แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel

วิธีใช้ Regular Expression (RegEx) ใน Power Query

วิธีจัดการข้อมูลแย่ๆ ด้วย Power Query ทั้งข้อมูลปนกัน ข้อมูลอยู่บนหัวตาราง

แก้ปัญหา Power Query มองไม่เห็นการเพิ่มคอลัมน์ใหม่ในไฟล์ CSV

วิธีแก้ปัญหาเวลาเขียน M Code แบบ each ซ้อนกันหลายอัน

วิธีใช้ Power Query ดึงข้อมูล EMS Tracking จากไปรษณีย์ไทย ผ่าน Web API

บันได 10 ขั้น เริ่มเรียนรู้ M Code ขุมพลังของ Power Query

การใช้ Power Query M Code จัดการข้อมูลที่อยู่ในตำแหน่งไม่แน่นอน

Round ใน Power Query ได้ผลลัพท์ไม่เหมือนใน Excel!!
June 23, 2020
Function ที่ใช้งานบ่อยๆ ในชีวิตจริง

บทความนี้คือส่วนหนึ่งของโครงการ ร่วมสร้าง “หนังสือคู่มือ Excel ที่เจ๋งที่สุด” ใครที่มี comment เพื่อแนะนำ ปรับปรุงหนังสือได้ คุณจะได้เครดิตในฐานะผู้ร่วมเขียน ลงในหนังสือที่จะพิมพ์จริงๆ ด้วย! อ่านรายละเอียด และดูสารบัญหนังสือ คลิ๊กที่นี่

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

แต่ไม่ต้องห่วงเพราะฟังก์ชั่นที่ใช้บ่อยจริงๆ ไม่ได้มีเยอะเลย และมันก็เป็นไปตาม กฎ 80/20 ที่บอกว่า

“ผลลัพธ์หรือ ผลกระทบส่วนใหญ่ (80%) มาจากสาเหตุที่เป็นส่วนน้อย (20%)”

เพราะใน Excel การใช้งานส่วนใหญ่มาจาก ฟังก์ชั่นแค่ไม่ถึง 20% เท่านั้น!!

เท่าที่ผมค้นคว้ามา Excel มีฟังก์ชั่นทั้งหมดประมาณ 450 ฟังก์ชั่น (ประมาณนะครับ…) ซึ่งฟังก์ชั่นที่ผมคิดว่าใช้บ่อยมากๆ เลย มีอยู่ประมาณ 45 ตัวด้วยกัน และมีค่อนข้างบ่อยอีก 25 ตัว รวมเป็น 70 ตัว ซึ่งคิดเป็น 15% ของฟังก์ชั่นทั้งหมด

แหม.. กฎ 80/20 นี้แม่นจริงๆ สินะครับ !

ต้องเลือกใช้ฟังก์ชั่นให้เหมาะกับสถานการณ์

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

บางเครื่องมือ (ฟังก์ชั่น) อาจทำงานได้เหมือนกันกับอีกฟังก์ชั่นเลย และอาจทำงานได้มากกว่าด้วย แต่ก็มักจะเขียนสั่งงานยากกว่าด้วยเช่นกัน เช่น =MAX จะเท่ากับ =LARGE ที่ระบุว่าเอาอันดับ1

ถ้าเราไม่ได้จะทำอะไรซับซ้อนมาก ก็ไม่จำเป็นจะต้องไปขี่ช้างจับตั๊กแตน (ใช้ฟังก์ชั่นยากๆ ) ก็ได้ครับ

เน้นความเข้าใจหน้าที่และความสามารถของฟังก์ชั่น

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

เพื่อความสะดวกของคุณ ผมได้ทำการคัดเลือก Function ที่ใช้กันบ่อยๆ มาให้แล้ว!!

โดยผมจะแบ่งออกเป็นหมวดต่างๆ เช่นเดียวกับเมนูที่อยู่บน Ribbon ใน [Formulas] –> Function Library นะครับ

(more…)

April 3, 2015
มาทำความรู้จัก Function กันเถอะ
Function คืออะไร?
ฟังก์ชั่น (Function) ถ้าจะพูดให้เข้าใจง่ายๆ ก็คล้ายๆ กับ สูตรสำเร็จรูป ที่มีหน้าที่ทำสิ่งต่างๆ ที่กำหนดไว้ตามแต่ฟังก์ชั่นนั้นๆ ซึ่งฟังก์ชั่นนั้นช่วยทำให้ชีวิตเราง่ายขึ้นมากมาย ยกตัวอย่างเช่น ถ้าเราอยากหาค่าเฉลี่ยของข้อมูลในช่อง A1 ถึง A5 หากไม่ใช้ Function มาช่วยเราก็ต้องเขียนสูตรแบบนี้ = (A1+A2+A3+A4+A5)/5 แต่ถ้ามีฟังก์ชั่น แล้ว เราก็สามารถเขียนแค่
```
=AVERAGE(A1:A5)
```
เท่านั้นเองก็จะได้คำตอบเดียวกัน แต่ง่ายกว่ามาก
แต่ถ้าเป็นการคำนวณที่ซับซ้อนกว่านี้ เช่น หาค่าผ่อนบ้านแบบตัวอย่างที่แล้ว หากคุณไปนั่งเขียนสูตรเองนี่เหนื่อยตาย แต่ถ้าเปลี่ยนมาใช้ฟังก์ชั่นแทน คุณแค่เขียนสูตร
```
=PMT(ดอกเบี้ยต่องวด,จำนวนงวด,ยอดเงินกู้)
```
ก็หาคำตอบได้อย่างง่ายดาย นี่แหละครับความสำเร็จรูปซึ่งเป็นข้อดีของฟังก์ชั่นล่ะ!
การทำงานของฟังก์ชั่น (Input –> Function –> Output)
ฟังก์ชั่น ทำงานคล้ายๆ โรงงานการผลิตสินค้า ที่ต้องการ Input เข้าไปเป็นวัตถุดิบ แล้วมันจะผลิตผลลัพธ์ที่เป็น Output ออกมาให้ตามหน้าที่ของมัน โดยที่ Function แต่ละตัว สามารถมี Input (เรียกอีกชื่อ ว่า Argument) ได้หลายตัว และอาจเป็นข้อมูลคนประเภทกันก็ได้
(more…)
April 3, 2015
รวม 5 เทคนิคการเขียนสูตรใน Excel ที่คุณอาจยังไม่รู้มาก่อน!
ครั้งก่อนผมได้แนะนำ รวม 10 เทคนิค Excel ง่ายๆ ที่ทำให้คุณทำงานเร็วขึ้น ไปแล้ว และได้การตอบรับดีมากๆ (ขอบคุณมากเลยครับ) วันนี้ผมเลยจะขอนำเสนอ รวม 5 เทคนิคการเขียนสูตรใน Excel ที่คุณอาจยังไม่รู้มาก่อน! ซึ่งเป็นเทคนิคการทำงานเกี่ยวกับการเขียนสูตรใน Excel ที่จะช่วยให้เพื่อนๆ เขียนสูตรได้เร็ว ถูกต้อง และตรวจสอบสูตรที่คนอื่นเขียนได้อย่างรวดเร็วอีกด้วย

1. หากเจอสูตรที่ใช่ ให้กด Tab เลย เพื่อเลือกใช้

เวลาเขียนสูตรใน cell ด้วยเครื่องหมาย = แล้วเริ่มพิมพ์ชื่อสูตร เช่น จะพิมพ์สูตร VLOOKUP
เมื่อเราเริ่มพิมพ์ว่า =V ไปเพียงตัวเดียว มันจะขึ้นรายชื่อสูตรทุกตัวที่ขึ้นต้นด้วยตัว V มาให้เลือก…
- ตรงนี้จริงๆเราสามารถดับเบิ้ลคลิ๊กเลือกที่ VLOOKUP ได้ หรือจะกดลูกศรขึ้นลงไปหาคำว่า VLOOKUP แล้วก็สามารถ กด Tab เพื่อเลือกได้เลย โดยไม่ต้องพิมพ์ให้จบ
- แต่ถ้าพิมพ์ L เพิ่มลงไปอีกตัวด้วยหลังจากตัว V มันก็จะกรองให้เลือกแต่สูตรที่มีคำว่า VL นำหน้า สูตรที่แสดงก็อาจจะเหลือแค่ VLOOKUP ตัวเดียว ก็สามารถกด Tab เลือกได้เลยเช่นกัน
- การกดเลือกแบบนี้มันจะใส่วงเล็บเปิดให้เองด้วย ดีกว่าพิมพ์เองเป็นไหนๆ
- อ่อ เกือยลืม ก่อนจะพิมพ์สูตรเสร็จ ตัววงเล็บปิดตัวสุดท้ายของสูตรไม่จำเป็นต้องพิมพ์ใส่ก็ได้นะ เสียเวลาครับ! (more…)
February 9, 2014
วิธีสุ่ม item จากรายการที่เราเตรียมไว้
บางครั้งเพื่อนๆ อาจต้องการสุ่ม item ซึ่งอาจจะเป็นคำบางคำ หรือ เลขบางเลข ออกมาจากรายการที่เราเตรียมไว้ แล้วเราจะทำอย่างนั้นด้วย Excel ได้ยังไง มาดูกันครับ
แน่นอนว่าการจะบรรลุวัตถุประสงค์อย่างหนึ่ง ไม่จำเป็นต้องมีการผสมสูตรเพียงแบบเดียว บางทีเพื่อนๆ อาจเจอหรือค้นพบการผสมสูตรที่มีประสิทธิภาพกว่าผมก็ได้ ถ้าเจอแบบนั้นแล้วอย่าลืมมาแชร์กันบ้างนะครับ!
สุ่ม item แบบที่ 1 : เขียนรายการที่จะสุ่มลงไปในสูตรเลย
ส่วนประกอบ
- RANDBETWEEN (bottom,top) = ตัวสุ่มจากช่วงของเลขที่กำหนด
- CHOOSE (index_num,value1,value2,…) = กำหนดว่าจะเอาผลลัพธ์ที่เท่าไหร่มาแสดง
Concept การผสมสูตร
- ถ้าจะใช้วิธีนี้ ก่อนอื่นเราต้องรู้ว่าจะมีรายการที่จะให้สุ่มทั้งหมดกี่รายการ สมมุติว่า 5 อัน
- ใช้ RANDBETWEEN สุ่มเลขระหว่าง 1-5 ออกมาก่อน
- จากนั้นได้ผลลัพธ์อะไร (เลขสุ่มระหว่าง 1-5) ก็เอาไปใส่ใน index_num ของฟังก์ชั่น CHOOSE อีกทีซึ่งเตรียมเขียนรายการที่จะสุ่มทั้ง 5 รายการไว้แล้ว
ขั้นตอนการผสมสูตร
- เขียนสูตรในช่องไหนก็ได้ ว่า
```
=RANDBETWEEN(1,5)
```
ได้เลขสุ่ม ระหว่าง 1-5
```
=CHOOSE (เลขสุ่ม,รายการที่1,รายการที่2,รายการที่3,รายการที่4,รายการที่5)
```
ผสม!!
```
 =CHOOSE (RANDBETWEEN(1,5),รายการที่1,รายการที่2,รายการที่3,รายการที่4,รายการที่5)
```
หมายเหตุ : รายการที่ xxx ถ้าเป็นตัวหนังสือต้องใส่ในเครื่องหมายคำพูด (“”) ด้วย แต่ถ้าเป็นตัวเลข หรือ เป็นสูตรก็ใส่ลงไปตรงๆ ได้เลย
- จากนั้นกด F9 เพื่อ Random ได้เลย
สุ่ม item แบบที่ 2 : มีรายการที่จะสุ่มอยู่ในตาราง Excel เป็น Range คอลัมน์นึง
ส่วนประกอบ
- RANDBETWEEN (bottom,top) = ตัวสุ่มจากช่วงของเลขที่กำหนด
- INDEX (reference,row_num,[column_num]) = ดึงค่ามาแสดงจากพิกัดที่กำหนด
- COUNTA (value1,value2,…) (Optional) = นับว่ามีกี่ช่องที่ไม่ว่างเปล่า
Concept การผสมสูตร
- ถ้าจะใช้วิธีนี้ ต้องเขียนรายการที่จะสุ่มลงไปในคอลัมน์ซักอันนึงก่อน สมมุติเขียนใน Column B (เพื่อให้ง่าย ให้เขียนโดยไม่ต้องมีหัวตารางนะครับ และให้เขียนต่อกันลงไปเรื่อยๆ อย่าเว้นช่อง)
- วิธีนี้ ถ้าจะกำหนดจำนวนรายการแน่นอนจะง่ายมาก แต่ในที่นี้เราจะทำแบบเผื่อให้มาเพิ่มรายการทีหลังก็ได้ (Dynamic Range) โดยไม่ต้องแก้สูตรใหม่ด้วย
- ใช้ COUNTA เพื่อนับว่าที่เราใส่ลงไปใน Column B มีกี่รายการ (ที่ไม่ใช่ค่าว่างเปล่า) สมมติว่านับได้ bbb รายการ
- ใช้ RANDBETWEEN สุ่มเลขระหว่าง 1 ถึง bbb ออกมาก่อน
- ใช้ INDEX ลากคลุม Column B เอาไว้ เป็น Reference จากนั้นให้เลขสุ่มเป็น row_num
ขั้นตอนการผสมสูตร
- เขียนรายการที่จะสุ่มลงไปในคอลัมน์ B จำนวน bbb รายการ
- เขียนสูตรในช่องไหนก็ได้ ที่ไม่ใช่คอลัมน์ B ว่า
```
=COUNTA(B:B)
```
สมมติว่านับได้ bbb รายการ
```
=RANDBETWEEN(1,เลขที่นับได้)
```
ผสมครั้งที่ 1 !
```
= RANDBETWEEN(1,COUNTA(B:B))
```
ได้เลขสุ่ม
จากนั้นใช้ = INDEX (reference,row_num) โดยให้ คอลัมน์ B (หรือ B:B) เป็น reference และค่าที่สุ่มได้เป็น row_num
```
=INDEX(B:B,เลขสุ่ม)
```
ผสมครั้งที่ 2 !!
```
=INDEX(B:B,RANDBETWEEN(1,COUNTA(B:B)))
```
- จากนั้นกด F9 เพื่อ Random ได้เลย
January 1, 2014
การทำงานเกี่ยวกับวันและเวลา (Date & Time) ใน Excel
เรื่องของวันและเวลา

วันที่และเวลาเป็นสิ่งที่เราอาจต้องใช้มันในการทำงานอยู่บ่อยๆ ใน Excel
เช่น หากว่าเราเอาวันที่สองวันมาลบกัน เราก็จะรู้ว่าทั้งสองวันห่างกันกี่วัน???
แต่จริงๆแล้ว Excel มันทำงานยังไงกันแน่?? ทำไมเอาวันเวลามาลบกันได้นะ??

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

รูปแบบวันที่ ที่ Excel รู้จัก

กรณีที่ตั้งค่า Regional Setting ใน Control Panel เป็น Thai จะทำให้กรอกเป็น วัน/เดือน/ปี ได้แบบนี้ (ถ้าตั้งค่าเป็นอย่างอื่น อาจต้องกรอกเป็น เดือน/วัน/ปี ซึ่งอาจทำให้เรากรอกผิด เพราะคนไทยไม่คุ้นกับการกรอกแบบนี้)

Excel มองวันและเวลาเป็นตัวเลขธรรมดาๆ

จริงๆ แล้ว Excel ทำงานเกี่ยวกับวันเวลาด้วยแนวคิดที่ง่ายมากๆ ครับ นั่นคือ

วันที่คือจำนวนเต็ม

Excel จะแทนวันที่ 1/1/1900 (1 มกราคม คศ. 1900) ด้วยเลข 1
แล้วมันก็แทนวันที่ 2/1/1900 ( 2 มกราคม คศ. 1900) ด้วยเลข 2..

ทำอย่างนี้ไปเรื่อยๆ (วันเพิ่ม 1 วัน เลขเพิ่ม 1 หน่วย)

จนถึงวันในยุคปัจจุบัน จะได้ตัวเลขประมาณ 4 หมื่นกว่าๆ

เวลา คือ ทศนิยม

ทำนองเดียวกัน ถ้าเลขนั้นลงละเอียดถึงระดับของเวลา การแทนเลขจะแทนด้วยทศนิยมแทน
ถ้าหากเวลาผ่านไป 1 วัน เลขจะเพิ่มขึ้น 1 หน่วย
เมื่อเวลาผ่านไป ครึ่งวัน (เช่น ตอนเที่ยงตรง) เลขก็จะเพิ่มขึ้น 0.5 หน่วย นั่นเอง

ดังนั้น หากเราเห็น เลข 1.5 มันคือวันที่ 1/1/1900 เวลา 12:00:00

พอเริ่มเห็นภาพแล้วใช่มั๊ยครับ เรามาลุยต่อกันเลยครับ

อยากรู้ว่าวันที่/เวลา มีค่าที่แท้จริงคือเลขอะไร?

ให้ลองเปลี่ยน Format จาก Date => General หรือ Number ดูก็จะรู้ครับ

การคำนวณเกี่ยวกับวันที่

เมื่อเรารู้อย่างนี้แล้ว หากเราเอาวันที่ 2 ช่องลบกัน เราก็จะรู้ระยะห่างของวันทั้งสองได้เลย โดยที่หน่วยออกมาเป็น วัน เช่น

การคำนวณเวลา และการแปลงหน่วย

สมมติเรามีข้อมูลว่า…

เริ่มทำงาน ด้วยวันที่/เวลาในช่อง B1 เสร็จงาน ด้วยวันที่/เวลาในช่อง B2

อยากรู้ว่า ใช้เวลาทั้งหมดกี่นาที ในช่อง B3 ยังไง?

เมื่อ Excel แทนวันที่ด้วยจำนวนเต็ม ดังนั้น เวลา (ซึ่งคือส่วนหนึ่งของวัน) ก็จะถูกแทนด้วยเลขทศนิยมนั่นเอง เช่น
- วันที่ 16/10/2016 เวลา 0:00 คือเลข 42659
ทีนี้ถ้าเรากรอกเป็น 16/10/2016 13:25:00 แล้วลองเปลี่ยนเป็น Number จะได้เป็นเลข 42659.5590277778 (ลองเพิ่มทศนิยมดู) ซึ่งจริงๆ แล้วมันก็คือการแปลงวัน+เวลา ชั่วโมงและนาที ให้มีหน่วยเป็น “วัน” นั่นเอง

ดังนั้นถ้าเรามีเวลาเริ่มและจบแล้ว เราจะหาเวลาที่ผ่านไปได้ง่ายมากๆ โดยเอาค่าทั้งสองมาลบกัน

เช่น เอา B2-B1 จะได้ 1.784722222 แบบนี้ ซึ่งจะมีหน่วยเป็นวัน

ถ้าหากโจทย์ต้องการคำตอบที่หน่วยเป็นนาที เราก็แค่ต้องแปลงหน่วยจากวันเป็นนาที ซึ่งง่ายมากๆ

แค่เพียงเรารู้หลักการของการแปลงหน่วยเท่านั้น นั่นคือ การทำให้หน่วยตัดกันจนให้ได้ผลลัพธ์เป็นหน่วยสุดท้ายที่ต้องการ โดยการแทนเลข *1 ด้วยอัตราส่วนที่เป็นความจริง เช่น
- 1 วัน = 24 ชม. หรือ 1 = 24 ชม./วัน
- 1 ชม. = 60 นาที หรือ 1 = 60 นาที/ชม.
ดังนั้น (B2-B1) วัน คือ
- = (B2-B1) วัน * 1 * 1 (คูณ 1 ค่าไม่เปลี่ยน ถือว่าทำแล้วค่ายังคงถูกต้อง)
- = (B2-B1) วัน * 24 ชม./วัน * 60 นาที/ชม.
  โดยที่จะเห็นว่าหน่วยสามารถตัดกันได้ จนเหลือแค่หน่วยนาที
- สุดท้ายจะได้ว่า เวลาที่ผ่านไปเป็นหน่วยนาที คำนวณได้จาก
- = (B2-B1)*24*60 นาที
เห็นมั้ยครับ? การคำนวณเรื่องวันที่และเวลาไม่ได้ยากอย่างที่คิดเลย แค่ต้องเข้าใจ Concept ของมันเท่านั้นเอง

ใครสนใจเรื่องวันที่และเวลา อย่าลืมอ่านต่อในบทความเรื่อง ฟังก์ชันเกี่ยวกับวันที่และเวลา นะครับ
November 24, 2013

Cookie	Duration	Description
cookielawinfo-checkbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

Tag: function

ฟังก์ชันคืออะไร?

แล้ว library หรือ method ล่ะ?

ทำไมต้องสร้างฟังก์ชันขึ้นมาเอง?

สร้างฟังก์ชันใน Python ยังไง?

สรุปความหมาย

เรียกใช้ฟังก์ชัน

ลองใช้ร่วมกับความรู้ที่เรียนผ่านมาแล้ว

Lambda Function

ตอนต่อไป

Trim แบบเดียวกับ Excel ด้วย ThepTrim

วิธีโหลด Package ThepExcel-Mfx

แนะนำฟังก์ชันใน Package ThepExcel-Mfx

กลุ่มที่อยู่ใน Package Free (ใน Premium ก็มี)

ThepDatefromText : เปลี่ยนข้อความ ให้เป็นวันที่ แถมเลื่อนปีได้

ThepExtractNumber : ดึงเฉพาะตัวเลขจากข้อความ เลือกได้ว่าจะเอาจุดกับ space หรือไม่

ThepGetColumnName : ดึงเอาชื่อคอลัมน์จากลำดับตัวเลข

ThepTrim : Trim แบบเดียวกับ Excel ที่เราคุ้นเคย แต่เลือกอักขระที่จะ Trim ได้ด้วย

ThepGetMultipleListItem : ดึงข้อมูลจาก List หลายๆ ไอเท็มพร้อมกันได้

ThepRenameColumn : เปลี่ยนชื่อคอลัมน์โดยระบุตำแหน่ง (ระบุเป็น list ได้ )

ThepGenDateTableFromDate : สร้างตารางจากวันที่เริ่มต้นและสิ้นสุดที่กำหนด

ThepGenDateTableFromText : สร้างตารางจากข้อความวันที่เริ่มต้นและสิ้นสุดที่กำหนด

ThepReplaceAllError : เปลี่ยน Error ทั้งตารางให้กลายเป็นตัวที่ต้องการ

ThepRegExExtract : ดึงข้อความที่ตรงกับ RegEx Pattern ที่ระบุออกมา

ThepRegExMatchCount : นับได้ว่าเจอผลลัพธ์ตาม Pattern กี่ชุด (ไม่สนใจ capturing Group)

ThepRegExReplace : แทนที่ข้อความที่ตรงกับ Pattern ที่ระบุ

ThepOneHot : แปลงคอลัมน์ที่มีหลายๆ item เป็น Category ให้กลายเป็นคอลัมน์ที่มีค่าเป็น 1 กับ 0 (One Hot Encoding)

ThepThaiNumbertoArabic : แปลงเลขไทยเป็นเลขอารบิก

กลุ่มที่อยู่ใน Package Version Premium เท่านั้น

ThepReplaceTextList : แทนค่าในข้อความโดยใส่ค่าทีเดียว

หลายๆ ตัวโดยใช้ List ได้

ThepFlattenHeader : เปลี่ยนหัวตารางหลายชั้นให้กลายเป็นชั้นเดียว แถมใส่ตัวคั่นได้

ThepGetColumn : ดึงเอาคอลัมน์จากลำดับตัวเลขเริ่มและสิ้นสุด

ThepMoveColumn : ย้ายคอลัมน์แบบไม่อ้างอิงชื่อคอลัมน์อื่นที่ไม่เกี่ยวข้อง

ThepExpandAllColumn : แตกคอลัมน์ออกมาจากทุกตารางแบบไม่ฝังชื่อคอลัมน์ กำหนด prefix ได้

ThepMergeTableOffsetRow : ดึงข้อมูล 2 ตารางมาประกบกัน สามารถเหลื่อมแถวได้

ThepSearchColumn : เลือกเอาเฉพาะคอลัมน์ที่มีคำที่กำหนด แถมปะเพิ่มคอลัมน์ที่ต้องการได้

ThepRegExExtractAll : ดึงผลลัพธ์ทุกตัวทุก SubGroup ออกมาเป็น List

เทคนิคที่ทำให้ใช้สูตรของผมได้ง่ายขึ้น (ไม่จำเป็นต้องทำก็ได้)

Function คืออะไร?

วิธีการสร้างฟังก์ชันใน Python

ตัวอย่างการเขียนฟังก์ชัน

Print ตามจำนวนครั้ง

เลียนแบบ LEFT ใน Excel

เลียนแบบ TRIM ใน Excel

Recursion

Lambda Function

ตอนต่อไป

สารบัญ Series Python

แล้วเครื่องมืออื่นๆ ล่ะ?

Function คือ ขุมพลังที่แท้จริงของ M Code

การเรียกใช้ Custom Function

การใช้ each หรือ ฟังก์ชันแบบย่อๆ

each คือการย่อวิธีเขียนฟังก์ชัน

each ที่โผล่มาเมื่อกด Custom Column

สรุปวิธีการอ้างอิง Record (แถว), List (ข้อมูลในคอลัมน์) และ Table (ตาราง)

การจัดการ Error ด้วย try…otherwise…

การเขียนเงื่อนไขด้วย if

เจาะลึก each ด้วยการใช้ M Code เลียนแบบ VLOOKUP Approximate Match

และถ้าเราไม่อยากจะ hardcode เลข 69 ล่ะ?

จากเดิมที่เขียนแล้วไม่ชัดเจน จึงมีปัญหา

เปลี่ยนใหม่ ให้ชัดเจนขึ้นว่าอ้างถึง Field ของ Record ไหน

ตอนต่อไป

สารบัญซีรีส์ M Code

ต้องเลือกใช้ฟังก์ชั่นให้เหมาะกับสถานการณ์

เน้นความเข้าใจหน้าที่และความสามารถของฟังก์ชั่น

Function คืออะไร?

การทำงานของฟังก์ชั่น (Input –> Function –> Output)

1. หากเจอสูตรที่ใช่ ให้กด Tab เลย เพื่อเลือกใช้

สุ่ม item แบบที่ 1 : เขียนรายการที่จะสุ่มลงไปในสูตรเลย

ส่วนประกอบ

Concept การผสมสูตร

ขั้นตอนการผสมสูตร

สุ่ม item แบบที่ 2 : มีรายการที่จะสุ่มอยู่ในตาราง Excel เป็น Range คอลัมน์นึง

ส่วนประกอบ

Concept การผสมสูตร

ขั้นตอนการผสมสูตร

เรื่องของวันและเวลา

รูปแบบวันที่ ที่ Excel รู้จัก

Excel มองวันและเวลาเป็นตัวเลขธรรมดาๆ