Thep Excel

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

ดังนั้นผมจึงคิดว่า ทำไมเราไม่เขียนฟังก์ชันที่ตอบโจทย์ขึ้นมาเองซะเลยล่ะ! จะได้แก้ Pain Point และให้การใช้งาน Power Query ง่ายขึ้นด้วย

ในบทความนี้ผมก็จะมาแนะนำให้รู้จักว่าผมเขียนฟังก์ชันอะไรเจ๋งๆ ออกมาใน Package ThepExcel-Mfx บ้าง และความสามารถของมันคืออะไร? ซึ่งจะใช้ได้ทั้งกับ Excel และ Power BI เลยครับ สะดวกมากๆ ยกตัวอย่างสิ่งที่ผมทำไว้ เช่น

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

และยังมีอีกมากมาย ลองอ่านดูได้เลยครับ ว่ามีตัวไหนน่าสนใจบ้าง (จะมีเพิ่มขึ้นเรื่อยๆ ด้วยนะ)

วิธีโหลด ThepExcel-MCode ฟังก์ชันสำเสร็จรูป

แต่ก่อนจะใช้ฟังก์ชันที่ผมเขียนได้ ก็ต้องมีฟังก์ชันที่ผมเขียนก่อนนะครับ

ใครสนใจไฟล์ Package ThepExcel-Mfx ที่ผมทำขึ้นมานี้สามารถโหลดไฟล์ได้ครับ
มีทั้งแบบฟรี และแบบ Premium (ใครที่อยากได้ฟังก์ชันเจ๋งๆ เยอะกว่า หรือ อยากสนับสนุนผมก็ซื้อแบบ Premium ได้ครับ)
ซึ่งทั้ง 2 แบบผมจะมีการเพิ่มฟังก์ชันให้เรื่อยๆ ตาม Feedback ของคนใช้งานด้วยครับ

• Version ฟรี สามารถดู code ใน GitHub ที่นี่
• Version Premium : สามารถโหลดได้จาก Facebook Group สำหรับ ThepExcel Subscriber (เดือนละ 35 บาท)

วิธีการใช้งาน

พอมี MCode ที่ต้องการแล้ว ให้ทำแบบนี้

1. กดสร้าง Blank Query ขึ้นมา
2. เปิด Advanced Editor
3. ลบ code เดิมแล้วแปะ Code ของผมลงไป (แทนอันเดิมทั้งหมด) จากนั้นกด Done
4. ตั้งชื่อฟังก์ชันก็ใช้ได้เลย

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

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

จัดการวันที่ผิดรูปแบบ ด้วย ThepConvertDate (Premium)

วิธีใช้งาน

• ThepConvertDate([Text Input])
  กรณีไม่กรอก locale, ใช้วันที่ที่ใกล้วันปัจจุบันที่สุด
• ThepConvertDate([Text Input],”uk”) พยายามมองเป็น ว ด ป
• ThepConvertDate([Text Input],”us”) พยายามมองเป็น ด ว ป
• ThepConvertDate([Text Input],”th”) พยายามมองเป็นปีไทย

(DateText as text, optional locale as nullable text) as nullable date =>
let
    // 0. ตัด ordinal suffixes st/nd/rd/th ที่มาต่อท้ายตัวเลข (กรณีตามด้วย space)
    OrdinalDelims = {"st ", "nd ", "rd ", "th "},
    RemoveOrdinal = List.Accumulate(
        OrdinalDelims,
        DateText,
        (state, s) => Text.Replace(state, s, " ")
    ),

    // 1. ตาราง mapping ไทย→อังกฤษ (full name + abbr. มี/ไม่มีจุด)
    MonthTH2EN = {
        {"มกราคม", "Jan"}, {"ม.ค.", "Jan"}, {"ม.ค", "Jan"}, {"มค", "Jan"},
        {"กุมภาพันธ์", "Feb"}, {"ก.พ.", "Feb"}, {"ก.พ", "Feb"}, {"กพ", "Feb"},
        {"มีนาคม", "Mar"},    {"มี.ค.", "Mar"}, {"มี.ค", "Mar"}, {"มีค", "Mar"},
        {"เมษายน", "Apr"},    {"เม.ย.", "Apr"}, {"เม.ย", "Apr"}, {"เมย", "Apr"},
        {"พฤษภาคม", "May"},  {"พ.ค.", "May"}, {"พ.ค", "May"}, {"พค", "May"},
        {"มิถุนายน", "Jun"},  {"มิ.ย.", "Jun"}, {"มิ.ย", "Jun"}, {"มิย", "Jun"},
        {"กรกฎาคม", "Jul"},  {"ก.ค.", "Jul"}, {"ก.ค", "Jul"}, {"กค", "Jul"},
        {"สิงหาคม", "Aug"},   {"ส.ค.", "Aug"}, {"ส.ค", "Aug"}, {"สค", "Aug"},
        {"กันยายน", "Sep"},   {"ก.ย.", "Sep"}, {"ก.ย", "Sep"}, {"กย", "Sep"},
        {"ตุลาคม", "Oct"},    {"ต.ค.", "Oct"}, {"ต.ค", "Oct"}, {"ตค", "Oct"},
        {"พฤศจิกายน", "Nov"}, {"พ.ย.", "Nov"}, {"พ.ย", "Nov"}, {"พย", "Nov"},
        {"ธันวาคม", "Dec"},   {"ธ.ค.", "Dec"}, {"ธ.ค", "Dec"}, {"ธค", "Dec"}
    },

    // 2. แปลงชื่อเดือนไทย → อังกฤษ (ใช้ RemoveOrdinal แทน DateText)
    ReplaceMonthTH = List.Accumulate(
        MonthTH2EN,
        RemoveOrdinal,
        (state, m) => Text.Replace(state, m{0}, m{1})
    ),

    // 3. แทนตัวคั่นทุกชนิด (รวม . , space, -, _ , /) เป็น "/"
    delimiters = {"-", ",", "_", " ", "/", "."},
    NormalizeDelimiter = List.Accumulate(
        delimiters,
        ReplaceMonthTH,
        (state, d) => Text.Replace(state, d, "/")
    ),

    // 3.1 รายชื่อวัน (ไทย/อังกฤษ) ที่จะตัดทิ้ง (ทั้งหมดเป็นตัวพิมพ์เล็ก เพราะเดี๋ยวเราจะ Text.Lower ก่อน)
    WeekdayTokens = {
        // ไทยเต็ม
        "วันอาทิตย์", "วันจันทร์", "วันอังคาร", "วันพุธ",
        "วันพฤหัส", "วันพฤหัสบดี", "วันศุกร์", "วันเสาร์",
        "อาทิตย์", "จันทร์", "อังคาร", "พุธ", "พฤหัส", "พฤหัสบดี", "ศุกร์", "เสาร์",

        // ไทยย่อ (มี/ไม่มีจุด)
        "อา", "จ", "อ", "พ", "พฤ", "ศ", "ส",
        "อา.", "จ.", "อ.", "พ.", "พฤ.", "ศ.", "ส.",

        // อังกฤษเต็ม
        "monday", "tuesday", "wednesday", "thursday",
        "friday", "saturday", "sunday",

        // อังกฤษย่อ (มี/ไม่มีจุด)
        "mon", "tue", "tues", "wed", "thu", "thur", "thurs", "fri", "sat", "sun",
        "mon.", "tue.", "tues.", "wed.", "thu.", "thur.", "thurs.", "fri.", "sat.", "sun."
    },

    // 4. split แล้วเอาค่าว่าง/null + ชื่อวัน ออก
    RawTokens =
        List.Transform(
            Text.Split(Text.Lower(NormalizeDelimiter), "/"),
            Text.Trim
        ),

    TokenList =
        List.Select(
            RawTokens,
            each _ <> "" and _ <> null and not List.Contains(WeekdayTokens, _)
        ),

    PreprocessedText = Text.Combine(TokenList, "/"),

    // 5. ลอง parse ด้วย locale ต่างๆ
    TryTH = try Date.FromText(PreprocessedText, "th-TH") otherwise null,
    TryUK = try Date.FromText(PreprocessedText, "en-GB") otherwise null,
    TryUS = try Date.FromText(PreprocessedText, "en-US") otherwise null,

    // 6. สร้าง MakeSenseDate: เลือกวันที่ห่างจากวันนี้น้อยสุด
    CurrentDate = DateTime.Date(DateTime.LocalNow()),
    CandidateDates = List.RemoveNulls({TryTH, TryUK, TryUS}),
    DateWithDiff = List.Transform(
        CandidateDates,
        each {_, Number.Abs(Duration.Days(_ - CurrentDate))}
    ),
    SortedByDiff = List.Sort(
        DateWithDiff,
        (a, b) => if a{1} < b{1} then -1 else if a{1} > b{1} then 1 else 0
    ),
    MakeSenseDate = if List.Count(SortedByDiff) > 0 then SortedByDiff{0}{0} else null,

    // 7. เลือกผลลัพธ์สุดท้ายตาม localeValue หรือ fallback เป็น MakeSenseDate
    localeValue = if locale = null then "auto" else Text.Lower(locale),
    Result =
        if localeValue = "th" then TryTH
        else if localeValue = "uk" then TryUK
        else if localeValue = "us" then TryUS
        else MakeSenseDate
in
    Result

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

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

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

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

=ThepDatefromText(DateText as text,DateFormat,optional offsetYear as number)as date

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

=ThepExtractNumber(OriginalText as text,optional Keepdot as logical, optional Keepspace as logical) as text

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

=ThepGetColumnName(TableName as table,ColNumber as number) as text

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

=ThepTrim(OriginalText as text,optional TrimChar as text) as text

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

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

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

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

=ThepRenameColumn(TableName as table,ColNumber as any,NewName as any) as table

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

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

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

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

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

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

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

=ThepReplaceAllError(TableName as table,optional ReplaceWith as text) as table

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

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

= ThepThaiNumbertoArabic(OriginalText as text) as text

ThepNETWORKDAYS : คำนวณจำนวนวันทำการระหว่างวันเริ่มและวันจบ

=ThepNETWORKDAYS(startDate as date, endDate as date, optional weekendPattern as text, optional holidays as nullable list) as number

กลุ่มที่อยู่ใน 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

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

=ThepGetColumn(TableName as table,StartColumnNum as number,optional EndColumnNum as number) as table

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

=ThepMoveColumn(TableName as table,ColumnName as any,optional MoveFirst as logical) as table

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

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 : หาที่มีคำนั้นลงท้าย

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