Tag: function

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

    สอนใช้ 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%
    สอนใช้ Python ใน Excel ตอนที่ 4 : สร้างฟังก์ชันใช้เอง 1

    ซึ่งแน่นอนว่าเรา link ค่า cell ใน Excel ให้กลายเป็น input ที่จะส่งเข้าฟังก์ชันของเราได้ ซึ่งมันก็จะสามารถทำงานเหมือนสูตร Excel เลย และสามารถเขียนสูตรช่องเดียวแล้ว Copy ลงไปข้างล่างได้ด้วย

    AfterDiscount(xl("A4"),xl("B4"))
    สอนใช้ Python ใน Excel ตอนที่ 4 : สร้างฟังก์ชันใช้เอง 2

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

    สมมติผมจะเอาความรู้ที่เราเรียนผ่านมา มาทำฟังก์ชันบ้าง

    สมมติว่าเราต้องการดึงเอาเฉพาะตัวเลขออกมาจากข้อความ แล้ว 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
    สอนใช้ Python ใน Excel ตอนที่ 4 : สร้างฟังก์ชันใช้เอง 3

    ขออีกซักตัวอย่างนึง เพื่อจะทำให้สามารถ 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
    สอนใช้ Python ใน Excel ตอนที่ 4 : สร้างฟังก์ชันใช้เอง 4

    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)
    สอนใช้ Python ใน Excel ตอนที่ 4 : สร้างฟังก์ชันใช้เอง 5

    แล้วเราสามารถทดสอบฟังก์ชันนี้ ด้วยการใส่วงเล็บต่อท้าย คล้ายๆ กับการทดสอบ LAMBDA ใน Excel เป๊ะๆ เลย เช่น

    (lambda OriginalPrice,DiscountRate=0.2 : OriginalPrice*(1-DiscountRate))(200,0.1)
    สอนใช้ Python ใน Excel ตอนที่ 4 : สร้างฟังก์ชันใช้เอง 6

    นอกจากนี้เราสามารถเก็บเจ้า function object เนี่ยเข้าไว้ในตัวแปร แล้วเรียกใช้ตัวแปรนั้นเหมือนเป็นฟังก์ชันได้ด้วย (คล้ายกับใน Excel ที่เอา LAMBDA ไปใส่ใน Name Manager) เช่น

    AfterDiscountLam=lambda OriginalPrice,DiscountRate=0.2 : OriginalPrice*(1-DiscountRate)
    สอนใช้ Python ใน Excel ตอนที่ 4 : สร้างฟังก์ชันใช้เอง 7

    แต่ปกติแล้วเรามักจะเรียกใช้ 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 ใน Excel ตอนที่ 4 : สร้างฟังก์ชันใช้เอง 8

    ตอนต่อไป

    เดี๋ยวตอนต่อไปจะพูดถึงเรื่องของการทำกราฟด้วย Python ด้วย Matplotlib แล้วค่อยไปต่อเรื่อง Machine Learning ครับ

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

    แนะนำ/วิธีใช้ 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 ของคนใช้งานด้วยครับ

    1. พอมีไฟล์ของผมแล้วทำดังนี้ ให้ Copy Query Folder ที่ชื่อ ThepExcel-Mfx
    2. จากนั้นเอาไป Paste ใส่ในไฟล์ Excel หรือ Power BI ของตัวเองได้เลย
    ThepExcel-Mfx

    เพียงเท่านี้คุณก็จะสามารถใช้ฟังก์ชันพิเศษที่ผมเขียนเอาไว้ได้แล้วครับ

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

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

    ให้ตารางข้างบนเป็น Query ที่ชื่อว่า MyTable ซึ่งจะใช้อ้างอิงกับหลายๆ ตัวอย่างนะครับ

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

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

    =ThepDatefromText(DateText as text,DateFormat,optional offsetYear as number)as date
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 10
    สามารถกำหนดได้ให้อ่าน format แบบไหน โดยมันจะสนใจแค่การเรียงของตัวเลขเท่านั้น ไม่สนตัวคั่น
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 11
    สามารถเลื่อนปีให้น้อยลง 543 ปีได้ เพื่อแก้ปัญหาการกรอก พ.ศ. <-> ค.ศ.

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

    =ThepExtractNumber(OriginalText as text,optional Keepdot as logical, optional Keepspace as logical) as text
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 12
    เก็บเฉพาะตัวเลข ถ้าไม่ระบุอะไรเพิ่ม คือจะเก็บ . และ space ไว้ด้วย

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

    =ThepGetColumnName(TableName as table,ColNumber as number) as text
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 13
    ดึงค่าว่าคอลัมน์ที่ 3 ของตาราง MyTable ชื่อว่าอะไร

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

    =ThepTrim(OriginalText as text,optional TrimChar as text) as text
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 14
    ถ้าไม่ระบุอะไรก็จะ Trim ข้อความที่เป็น Space ออกไป ทั้งหน้า หลัง และตรงกลางจะเหลือแค่เคาะเดียว
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 15
    แต่ถ้าระบุอักขระตัวไหนก็จะ Trim ตัวนั้นออกแทน space

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

    =ThepGetMultipleListItem(OriginalList as list,PosIndex as list) as list

    โดยระบุ PosIndex เป็น List ที่เก็บ index ของ item ที่ต้องการไว้ (index ของ list เริ่มต้นที่เลข 0)

    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 16
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 17
    สามารถระบุ Index ติดลบเพื่อเอาข้อมูลนับจากหลังสุดได้ (คล้าย Python)

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

    =ThepRenameColumn(TableName as table,ColNumber as any,NewName as any) as table
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 18
    แบบเปลี่ยนแค่คอลัมน์เดียว
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 19
    แบบเปลี่ยนคอลัมน์ที่ 1 3 และ 4 พร้อมกัน (ถ้าใส่ลำดับคอลัมน์เป็น -1 ก็จะหมายถึงคอลัมน์สุดท้าย)

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

    =ThepGenDateTableFromDate(StartDate as date,EndDate as date) as table

    สามารถกด Invoke ฟังก์ชันแล้วระบุวันที่จากปฏิทินได้เลย

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

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

    =ThepGenDateTableFromText(StartDateText as text,EndDateText as text) as table

    คล้ายๆ กับฟังก์ชันก่อนหน้า แต่คราวนี้ ระบุ Date ให้เป็น text รูปแบบ yyyymmdd ได้เลย

    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 22
    สร้างวันที่ตั้งแต่ 1 Jan 2020 ถึง 15 Jan 2020

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

    =ThepReplaceAllError(TableName as table,optional ReplaceWith as text) as table
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 23
    เดิมมี Error หลายจุด
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 24
    สามารถสั่งให้กลายเป็นค่าอะไรก็ได้ เช่น ค่าว่าง null

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

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

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

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

    =ThepOneHot(TableName as table,TargetColumnName as text) as table
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 25

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

    = ThepThaiNumbertoArabic(OriginalText as text) as text
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 26

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

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

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

    =ThepReplaceTextList(OriginalText as text,OldTextList as list,NewTextList as list) as text 
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 27

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

    =ThepFlattenHeader(TableName as table,NumRowHeader as number,optional Seperator as text, optional reverse as logical) as table
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 28
    หัวตาราง 2 ชั้น ช่างช้ำใจ…
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 29
    ถ้าไม่ระบุตัวคั่นก็จะต่อกันเลย และลำดับจะย้อนกลับจากหัวตารางล่างไปบนให้ด้วย
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 30
    สามารถใส่ตัวคั่น เช่น – และระบุเป็น false เพื่อไม่ต้องกลับลำดับบนล่างของหัวตาราง

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

    =ThepGetColumn(TableName as table,StartColumnNum as number,optional EndColumnNum as number) as table
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 31
    เอาเฉพาะคอลัมน์ 2 ถึง 4 จากตาราง MyTable มาได้เลย

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

    =ThepMoveColumn(TableName as table,ColumnName as any,optional MoveFirst as logical) as table
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 32
    ย้ายคอลัมน์ลูกค้ามาซ้ายสุดของตาราง โดยสูตรไม่อ้างอิงชื่อคอลัมน์อื่น
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 33
    ย้ายคอลัมน์ลูกค้ากับสินค้า (ระบุเป็น List ได้) ไปขวาสุดของตาราง ( ระบุ MoveFirst เป็น false) โดยสูตรไม่อ้างอิงชื่อคอลัมน์อื่น

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

    =ThepExpandAllColumn(TableName as table,optional prefix as text) as table

    ตารางที่จะ Expand ออกมาจะต้องอยู่ที่คอลัมน์ที่ชื่อว่า Data เท่านั้น เช่น มีข้อมูลก่อนจะ Expand ดังนี้

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

    เราสามารถเรียกฟังก์ชันมา Expand คอลัมน์ทั้งหมดได้ (โดยมันจะ Scan คอลัมน์ทั้งหมดใหม่เสมอ) ดังนี้

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

    หรือเราจะระบุ Prefix ของคอลัมน์ที่ Expand ออกมาก็ได้ เช่น

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

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

    =ThepMergeTableOffsetRow(TableName1 as table,TableName2 as table, RowOffset as number) as table
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 37
    สั่งให้ 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 : หาที่มีคำนั้นลงท้าย
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 38
    หาคอลัมน์ที่มีคำว่า ชิ้น อยู่
    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 39
    หาคอลัมน์ที่มีคำว่าชิ้น และปะคอลัมน์ TXID กับ สินค้าเข้าไปด้วย

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

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

    ปกติแล้ว การ Copy Paste Query ข้ามไฟล์ ถ้า Code ใน Query นั้นๆ ดันไม่ได้เริ่มด้วย let…in… ตัวโปรแกรมจะใส่ Code let…in…เพิ่มมาให้เองอัตโนมัติ

    ซึ่งมีข้อเสียคือ เวลาเรียกใช้ฟังก์ชันใน Formula Bar มันจะมองไม่เห็น ToolTips เช่นในรูป (แต่ถ้ากด Invoke Function จะเห็นปกตินะ)

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

    ซึ่งถ้าอยากให้ตอนเขียนสูตรใน Formula Bar เห็น Tooltips ตามปกติตามรูปข้างล่าง ก็มีอยู่ 3 way ที่จะแนะนำ คือ

    แนะนำ/วิธีใช้ ThepExcel-Mfx : M Code สำเร็จรูปจาก ThepExcel 41
    • 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
  • หัด Python สำหรับคนเป็น Excel : ตอนที่ 5 – การสร้างฟังก์ชันขึ้นมาใช้เอง (Function)

    หัด 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 ที่สร้างไว้
    หัด Python สำหรับคนเป็น Excel : ตอนที่ 5 – การสร้างฟังก์ชันขึ้นมาใช้เอง (Function) 42

    เลียนแบบ 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)
    
    หัด Python สำหรับคนเป็น Excel : ตอนที่ 5 – การสร้างฟังก์ชันขึ้นมาใช้เอง (Function) 43

    เลียนแบบ 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....","."))
    หัด Python สำหรับคนเป็น Excel : ตอนที่ 5 – การสร้างฟังก์ชันขึ้นมาใช้เอง (Function) 44

    เวลาใช้จริงก็ไม่ต้อง print step ระหว่างทางหรอก (อันนีั้นเราทำเพื่อตรวจสอบผลลัพธ์) ดังนั้นให้ comment ทิ้งไปเลยแบบนี้ก็ได้

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

    ซึ่งแน่นอนว่าเราเอาฟังก์ชันที่สร้างไปใช้ประโยชน์ได้มากมาย เช่น เอาไปวน 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)
    หัด Python สำหรับคนเป็น Excel : ตอนที่ 5 – การสร้างฟังก์ชันขึ้นมาใช้เอง (Function) 46

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

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

    มีค่าเท่ากับการทำแบบนี้เลย

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

    ตอนต่อไป

    ในที่สุดเราก็สามารถสร้างฟังก์ชันขึ้นมาใช้เองได้แล้ว แต่จะดีกว่ามั้ยถ้าเราจะใช้ฟังก์ชัน/ความสามารถเจ๋งๆ ที่คนอื่นได้สร้างไว้แล้วเต็ม internet เลย? ในตอนหน้าเราจะมาพูดถึงการใช้ Module/Packages กันครับ! นี่แหละสิ่งที่ทรงพลังที่สุดในความคิดของผม รอติดตามได้เลย เจ๋งแน่นอน

    สารบัญ Series Python

  • List ฟังก์ชันทั้งหมดของ Excel ในทุก Version

    List ฟังก์ชันทั้งหมดของ Excel ในทุก Version

    คุณสงสัยหรือไม่ว่า Excel แต่ละ Version มีฟังก์ชันอะไรให้ใช้แตกต่างกันบ้าง? แม้ว่าทาง Microsoft จะมีเว็บบอกไว้ แต่มันช่างใช้งานยากเหลือเกินว่าตกลงแล้ว Excel แต่ละ Version มี Function อะไรกันแน่

    ผมก็เลยทำรายงาน 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 ไหนได้บ้าง?

    List ฟังก์ชันทั้งหมดของ Excel ในทุก Version 50
  • คัมภีร์สรุป M Code ใน Power Query ตอนที่ 2 : Function และ each

    คัมภีร์สรุป 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*ฐาน*สูง
    Power Query M Custom Function

    เวลาเราจะใช้งาน ก็สามารถเรียกใช้ได้ใน M Code ได้เลย เช่น ให้สร้าง Blank Query อีกอันนึงในไฟล์เดิม แล้วใส่ M Code ว่า

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

    หรือจะใช้ผ่าน Invoke Custom Function ก็ได้ เช่น

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

    ซึ่งมันก็จะออกมาเป็นแบบนี้

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

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

    เคยเห็น each ที่โผล่มาตอนเรียกคำสั่งต่างๆ มั้ย?

    หลายๆ ครั้งเวลาที่เรากดคำสั่งเมนูมาตรฐานมันก็จะใส่ each ให้เราเอง เช่น ในตัวอย่างข้างบนก็มี each หรือว่าหากกด Add Column → Custom Column ก็จะมี each ซึ่งผมจะอธิบายโดยละเอียดให้เห็นชัดๆ ดังนี้ว่าจริงๆ แล้วมันคืออะไรกันแน่ครับ

    เช่น สมมติเรามีข้อมูล ราคาสินค้า และจำนวนอยู่ แล้วอยากจะหายอดขาย

    ปกติแล้วเราก็จะ Add Custom Column ได้แบบนี้

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

    ซึ่งสูตรจะออกมาเป็น = 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 ให้เหลือแค่ _ จะเห็นภาพชัดขึ้นครับ

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

    ซึ่งสูตรจะออกมาเป็น

    = Table.AddColumn(#"Changed Type", "ยอดขาย", each _ )

    แต่จะเห็นว่าในคอลัมน์ยอดขายจะได้ผลลัพธ์ออกมาเป็น Record ในบรรทัดนั้นๆ ซึ่งประกอบไปด้วย Field ทุกอันในตารางเดิมใน Step #”Changed Type”

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

    นั่นแปลว่า _ ใน 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 ได้เลย

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

    ทีนี้ถ้าเราอยากอ้างถึงอย่างอื่นบ้างล่ะ เช่น จะอ้างถึง Table ทั้งอันเลย คอลัมน์ที่ต้องการ หรือ Record ก่อนหน้า จะทำยังไง? เรามาดูทีละอันครับ

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

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

    Tips : ถ้าอยากได้ Step อื่นก็ใส่ชื่อ Step นั้นๆ ลงไป ถ้าอยากได้ตารางอื่นก็ใส่ชื่อตัวแปรที่เป็นตารางอื่น หรือชื่อ query อื่นลงไป

    ถ้าจะอ้างอิงคอลัมน์ที่ต้องการทั้งคอลัมน์ ก็ใส่ชื่อ Table ตามด้วย [ชื่อคอลัมน์] เช่น ใส่ว่า Source[ผลไม้] ผลลัพธ์ก็จะออกมาเป็น List

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

    ถ้าจะอ้างอิงถึงแถวก่อนหน้า แปลว่าเราต้องรู้แถวปัจจุบันของตัวเองก่อนถึงจะง่าย ซึ่งวิธีที่ง่ายที่สุดคือใส่ index column ให้เริ่มตั้งแต่เลข 0 เอาไว้ เช่น

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

    แล้วเราค่อยอ้างอิง Table แล้วใส่ row index ที่ต้องการ (index 0 คือ แถวแรกนะ อย่าลืม)

    ดังนั้นเราจะใส่ใน Custom Column ว่า =Source{[Index]-1} แบบนี้ได้ครับ

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

    ทีนี้แถวแรกมัน 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 แทน

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

    รวมถึงการอ้างอิงแถวก่อนหน้าที่ Error ในแถวแรกสุด (เพราะไม่มีแถวก่อนหน้า) เราอาจจะให้เป็น null ก็ได้ เช่น

    try Source{[Index]-1} otherwise null

    แค่นี้ก็จัดการ error ที่ไม่ต้องการได้แล้วครับ

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

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

    สำหรับการเขียนเงื่อนไขใน M Code เราก็ใช้ if…then…else… ประกอบกับการใช้ and or not ก็ได้ครับ

    อย่างตอนเขียนสูตรเพื่ออ้างอิงแถวก่อนหน้า ถ้าไม่ใช้ try…otherwise… เราจะใช้ if ก็ได้ ก็จะให้ผลลัพธ์แบบเดียวกัน

    if [Index]=0 then null else Source{[Index]-1}
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 2 : Function และ each 66

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

    สมมติผมมีข้อมูลคะแนนสอบอยู่แล้วต้องการจะตัดเกรด ดังนี้
    ผมเอาตารางซ้ายเข้าเป็น Query ชื่อ TestResult ส่วนตารางขวาชื่อ RefGrade

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

    ใน Query Test Result นั้นผมสร้าง Custom Column ด้วยสูตรว่า =RefGrade ก็จะได้ผลลัพธ์แบบนี้ ว่าในแต่ละบรรทัดของคะแนนสอบ เราได้ตาราง RefGrade กลับมาทั้งตารางเลย (ซึ่งเยอะไป!)

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

    สิ่งที่เราต้องการจริงๆ คือ อยากดูว่าคะแนนที่ได้นั้นอยู่ในช่วงไหน ซึ่ง logic ที่ใช้ได้คือ ต้องทำการเลือก RefGradeมาเฉพาะบรรทัดที่คะแนนต่ำสุด น้อยกว่าหรือเท่ากับ คะแนนสอบที่ได้ในแต่ละบรรทัดเท่านั้น และค่อนเลือกเอาบรรทัดสุดท้ายมาฃ

    ซึ่งเราจะพยายามเลือกให้เหลือเฉพาะบรรทัดที่ คะแนนต่ำสุด น้อยกว่าหรือเท่ากับ คะแนนสอบที่ได้ในแต่ละบรรทัดก่อน ด้วยฟังก์ชันที่ชื่อว่า Table.SelectRows ซึ่งมีวิธีการใช้ดังนี้

    Table.SelectRows(table as table, condition as function) as table

    จะเห็นว่า input ที่มันต้องการมี 2 ตัว คือ table ต้นฉบับ และ เงื่อนไข ซึ่ง table ต้นฉบับนี่ง่ายมาก ก็คือ RefGrade นั่นแหละ
    แต่ที่ยากก็คือเจ้า condition ที่ดันต้องใส่เป็นฟังก์ชันด้วยสิ

    เพื่อที่จะให้เข้าใจว่าปกติมันทำงานยังไง เราจะไปดู Query RefGrade แล้วไปลอง Filter มันเล่นๆ ดูก่อน ด้วยเงื่อนไขว่า คะแนนต่ำสุด <= 69 จะได้แบบนี้

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

    ซึ่งเราจะเห็นฟังก์ชัน Table.SelectRows ทำงานให้เราดูเลยว่าต้องเขียนยังไง

    = Table.SelectRows(#"Changed Type", each [คะแนนต่ำสุด] <= 69)

    เราก็เลยคิดว่าจะเอาคำสั่งนี้กลับไปเขียนใน TestResult ที่เราค้างไว้ได้ ซึ่งมันก็ใช้ได้จริงๆ (แค่เปลี่ยนชื่อ table จาก “Changed Type” เป็น RefGrade)

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

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

    เราอยากให้แต่ละบรรทัด มันใช้คะแนนสอบไปเป็นเงื่อนไขในการ Filter แทนการพิมพ์เลข 69 ลงไปเอง เราอาจเผลอคิดว่าจะใช้ [คะแนน] แทนได้เลย แต่มันไม่ง่ายแบบนั้นหรอก หึหึ เพราะมันจะบอกว่าหา Field ที่ชื่อว่า คะแนน ไม่เจอ

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

    ทำไมถึงหาไม่เจอ? ก็เป็นเพราะภายใต้ 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 แล้ว

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

    แต่จะเห็นว่ามันให้ผลลัพธ์กลับมาหลายบรรทัดอยู่ เราจะเอาแค่บรรทัดสุดท้าย ก็ใช้ Table.Last ได้ ดังนี้

    = Table.AddColumn(#"Changed Type", "Custom", (main) => Table.Last(Table.SelectRows(RefGrade, (sub)=> sub[คะแนนต่ำสุด] <= main[คะแนน])))
    คัมภีร์สรุป M Code ใน Power Query ตอนที่ 2 : Function และ each 73

    ผลลัพธ์ออกมาเป็น Record เดียวแล้ว ก็กด expand ที่มุมขวาบนของ Field ได้เลย จะได้ผลลัพธ์สุดท้ายที่สมบูรณ์ดังรูป

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

    ตอนต่อไป

    ตอนนี้เราก็ได้เรียนรู้การใช้ฟังก์ชันเบื้องต้นกันไปแล้ว เดี๋ยวในตอนต่อไปจะป็นเรื่องของ List แบบลึกซึ้งขึ้น ซึ่งมีประโยชน์มากด้วยแน่นอนครับ ติดตามต่อได้เลย

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

  • Function ที่ใช้งานบ่อยๆ ในชีวิตจริง

    Function ที่ใช้งานบ่อยๆ ในชีวิตจริง

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


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

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

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

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

    80-20-pareto

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

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

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

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

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

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

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

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

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

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

    (more…)

  • มาทำความรู้จัก Function กันเถอะ

    มาทำความรู้จัก Function กันเถอะ

    Function คืออะไร?

    ฟังก์ชั่น (Function) ถ้าจะพูดให้เข้าใจง่ายๆ ก็คล้ายๆ กับ สูตรสำเร็จรูป ที่มีหน้าที่ทำสิ่งต่างๆ ที่กำหนดไว้ตามแต่ฟังก์ชั่นนั้นๆ ซึ่งฟังก์ชั่นนั้นช่วยทำให้ชีวิตเราง่ายขึ้นมากมาย ยกตัวอย่างเช่น ถ้าเราอยากหาค่าเฉลี่ยของข้อมูลในช่อง A1 ถึง A5 หากไม่ใช้ Function มาช่วยเราก็ต้องเขียนสูตรแบบนี้ = (A1+A2+A3+A4+A5)/5 แต่ถ้ามีฟังก์ชั่น แล้ว เราก็สามารถเขียนแค่

    =AVERAGE(A1:A5)

    เท่านั้นเองก็จะได้คำตอบเดียวกัน แต่ง่ายกว่ามาก

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

    =PMT(ดอกเบี้ยต่องวด,จำนวนงวด,ยอดเงินกู้)

    ก็หาคำตอบได้อย่างง่ายดาย นี่แหละครับความสำเร็จรูปซึ่งเป็นข้อดีของฟังก์ชั่นล่ะ!

    pmt-formula

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

    function-concept

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

    (more…)
  • รวม 5 เทคนิคการเขียนสูตรใน Excel ที่คุณอาจยังไม่รู้มาก่อน!

    รวม 5 เทคนิคการเขียนสูตรใน Excel ที่คุณอาจยังไม่รู้มาก่อน!

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

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

    เวลาเขียนสูตรใน cell ด้วยเครื่องหมาย = แล้วเริ่มพิมพ์ชื่อสูตร เช่น จะพิมพ์สูตร VLOOKUP
    เมื่อเราเริ่มพิมพ์ว่า =V ไปเพียงตัวเดียว มันจะขึ้นรายชื่อสูตรทุกตัวที่ขึ้นต้นด้วยตัว V มาให้เลือก…

    • ตรงนี้จริงๆเราสามารถดับเบิ้ลคลิ๊กเลือกที่ VLOOKUP ได้ หรือจะกดลูกศรขึ้นลงไปหาคำว่า VLOOKUP แล้วก็สามารถ กด Tab เพื่อเลือกได้เลย โดยไม่ต้องพิมพ์ให้จบ
      Excel formula writing tips
    • แต่ถ้าพิมพ์ L เพิ่มลงไปอีกตัวด้วยหลังจากตัว V มันก็จะกรองให้เลือกแต่สูตรที่มีคำว่า VL นำหน้า สูตรที่แสดงก็อาจจะเหลือแค่ VLOOKUP ตัวเดียว ก็สามารถกด Tab เลือกได้เลยเช่นกัน
      Excel formula writing tips
    • การกดเลือกแบบนี้มันจะใส่วงเล็บเปิดให้เองด้วย ดีกว่าพิมพ์เองเป็นไหนๆ
    • อ่อ เกือยลืม ก่อนจะพิมพ์สูตรเสร็จ ตัววงเล็บปิดตัวสุดท้ายของสูตรไม่จำเป็นต้องพิมพ์ใส่ก็ได้นะ เสียเวลาครับ! (more…)
  • วิธีสุ่ม item จากรายการที่เราเตรียมไว้

    วิธีสุ่ม 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 ได้เลย
  • การทำงานเกี่ยวกับวันและเวลา (Date & Time) ใน Excel

    การทำงานเกี่ยวกับวันและเวลา (Date & Time) ใน Excel

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

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

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

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

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

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

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

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

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

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

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

    การทำงานเกี่ยวกับวันและเวลา (Date & Time) ใน Excel 76

     

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

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

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

    การทำงานเกี่ยวกับวันและเวลา (Date & Time) ใน Excel 77

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

     

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

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

    การทำงานเกี่ยวกับวันและเวลา (Date & Time) ใน Excel 78

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

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

    การทำงานเกี่ยวกับวันและเวลา (Date & Time) ใน Excel 79

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

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

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

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

    002

     

    เมื่อ Excel แทนวันที่ด้วยจำนวนเต็ม ดังนั้น เวลา (ซึ่งคือส่วนหนึ่งของวัน) ก็จะถูกแทนด้วยเลขทศนิยมนั่นเอง เช่น

    • วันที่ 16/10/2016 เวลา 0:00 คือเลข 42659

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

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

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

    explain-timedif

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

    แค่เพียงเรารู้หลักการของการแปลงหน่วยเท่านั้น นั่นคือ การทำให้หน่วยตัดกันจนให้ได้ผลลัพธ์เป็นหน่วยสุดท้ายที่ต้องการ  โดยการแทนเลข *1 ด้วยอัตราส่วนที่เป็นความจริง เช่น

    • 1 วัน = 24 ชม. หรือ 1 = 24 ชม./วัน
    • 1 ชม. = 60 นาที หรือ 1 = 60 นาที/ชม.

    ดังนั้น (B2-B1) วัน คือ

    • = (B2-B1) วัน * 1 * 1 (คูณ 1 ค่าไม่เปลี่ยน ถือว่าทำแล้วค่ายังคงถูกต้อง)
    • = (B2-B1) วัน * 24 ชม./วัน * 60 นาที/ชม.
      โดยที่จะเห็นว่าหน่วยสามารถตัดกันได้ จนเหลือแค่หน่วยนาที

    explain-timedif2

    • สุดท้ายจะได้ว่า เวลาที่ผ่านไปเป็นหน่วยนาที คำนวณได้จาก
    • = (B2-B1)*24*60 นาที

    เห็นมั้ยครับ? การคำนวณเรื่องวันที่และเวลาไม่ได้ยากอย่างที่คิดเลย แค่ต้องเข้าใจ Concept ของมันเท่านั้นเอง

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