excelavista

Tag: Excel

  • การใช้อักขระตัวแทน (wildcard characters) ในการค้นหา (Find and Replace) ใน Excel

    wildcard หรืออักขระตัวแทน มักใช้ทดแทนอักขระ 1 ตัว หรือหลายตัว ในการค้นหาและแทนที่ (Find & Replace) ใน Excel รวมถึงใช้ในการทำ Conditional Formatting สำหรับเซลล์ที่มีข้อมูลเป็นข้อความ (text) ก็ได้

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

    อักขระตัวแทน (wildcards)

    อักขระตัวแทนคำอธิบาย
    ? (เครื่องหมายคำถาม)ใช้แทนอักขระ 1 ตัว อย่างเช่น ถ้าเราค้นหาด้วย s?t เราจะเจอทั้ง sit, set, sat แต่หากเราใช้ s??t เราก็จะได้ seat, slit, slot เป็นต้น
    * (ดอกจันทร์)แทนอักขระกี่อักขระก็ได้ เช่น ถ้าเราค้นหา *east เราจะได้ผลลัพธ์ทั้งที่มีคำว่า *northeast* และ southeast เป็นต้น
    ~ (ตัวหนอน)ใช้เป็น escape string ซึ่งหมายความว่า ถ้าเราจะค้นหาข้อความที่มีเครื่องหมาย ? หรือ * และ ~ ต้องพิ่มพ์เป็น ~? ~* และ ~~ ตามลำดับ เช่น ถ้าเราจะค้นหาข้อความที่มีเครื่องหมายคำถามลงท้ายจะต้องค้นหาด้วย ~? จะได้ผลลัพธ์ที่มีคำว่า help? และ option? เป็นต้น

    ตัวอย่างการใช้งาน

    รูปแบบลักษณะผลลัพธ์ตัวอย่างผลลัพธ์
    ?เซลล์ที่มีอักขระ 1 ตัว“a”, “b”, “c” เป็นต้น
    ??เซลล์ที่มีอักขระ 2 ตัว“กอ”, “ขอ”, “of” เป็นต้น
    ???เซลล์ที่อักขระ 3 ตัว“sea”, “saw”, “off” เป็นต้น
    *เซลล์ที่มีอักขระกี่ตัวก็ได้ รวมไปถึงตัวเลข และสูตร แต่ปกติเราจะไม่ใช้เดี่ยว ๆ เพราะมันจะได้ทุกเซลล์ที่ไม่ว่าง รวมทั้ง spacebar“a”, “abc”, “yes” เป็นต้น
    *.xlsเซลล์ที่มีข้อความลงท้ายด้วย .xlsbook1.xls, book2.xls เป็นต้น
    ch*เซลล์ที่ข้อความขึ้นต้นด้วย ch“check”, “choose” เป็นต้น
    ???-??เซลล์ที่มีอักขระ 3 ตัวหน้าขีด และตามดัวยอักขระ 2 ตัว“123-24”, “ABC-24” เป็นต้น
    *~?ข้อความที่ลงท้ายด้วยเครื่องหมายคำถาม“Anybody?”, “ทำไม?” เป็นต้น
    *xyz*ข้อความที่มี “xyz” อยู่ตรงกลางdef-xyz-fy25 และ xyz360 เป็นต้น

    การค้นหาข้อความหรือตัวเลข

    ฟีเจอร์ตัวค้นหาใน Excel ใช้สำหรับค้นหาอะไรบางอย่างในไฟล์ (workbook) ไม่ว่าจะเป็น “ข้อความ” หรือ “ตัวเลข” โดยเราอาจใช้ฟีเจอร์นี้ เพื่อดูข้อมูลอื่นที่เกี่ยวข้อง (reference) หรือแก้ไขโดยการ แทนที่ (replace) ก็ได้

    โดยเรียกใช้งานได้จาก Home > Edit > Find & Select หรือกด Ctrl + F

    และเราจะเห็นไดอะล็อก บ็อกซ์ (dialog box) ของ Find and Replace แสดงขึ้นมา และจากภาพเราขยายส่วนที่เป็น Option >> ออกมาด้วย

    จากกรณีด้านบน เราอาจจะค้นหาด้วยคำว่า “วิ*” และกด Find All จะเจอคนที่ชื่อขึ้นต้นด้วยคำว่า “วิ” มาทั้งหมด ดังภาพด้านล่าง

    หรือหากต้องการแทนที่ (replace) ด้วยข้อมูลที่เราต้องการ ก็สามารถเลือกที่แถบ Replace ดังภาพ ซึ่งก็จะมีช่อง Replace with: เพิ่มมาให้เราพิมพ์ข้อความที่ต้องการแทนที่เข้าไป

    ⚠️ อย่างไรก็ตาม หากจะกด Replace All (แทนที่ทั้งหมด) ให้ระวังมาก ๆ เพราะทุกคำว่า “วิ” ทั้งหมดใน Sheet จะถูกแทนที่ด้วยข้อความที่เราใส่เข้าไปทั้งหมด

    โดยใน Option >> จะมีตัวเลือกเพิ่มคือ

    Within: จะเลือกขอบเขตได้ว่า เป็น Sheet หรือเป็น Workbook
    Search: จะเลือกได้ว่าจะให้ค้นหาแบบ By Rows ซึ่งเป็นค่าตั้งต้น หรือ By Columns
    Look in: จะเลือกได้ว่าจะให้ค้นหาในส่วนใดบ้าง โดยค่าตั้งต้นจะเป็น Formulas ส่วนตัวเลือกอื่นจะเป็น

    • Values เฉพาะค่าในเซลล์
    • Comments เฉพาะในคอมเม้นต์
    • Notes เฉพาะในโน๊ต
      และ 3 แบบข้างบนนี้จะค้นหาได้อย่างเดียวนะ ไม่สามารถแทนที่ได้เหมือน Formulas

    ◻️Match case ถ้าเราต้องการให้แยกระหว่างตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ ให้เลือก
    ◻️Match entire cell contents ถ้าต้องการเฉพาะข้อมูลที่ตรงกับที่พิมพ์ไว้จริง ๆ เท่านั้น ไม่เอาแบบที่มีคำอื่นผสมรวมกันอยู่ ให้เลือก

    การค้นหาจากรูปแบบ (cell format)

    หรือบางทีคุณอาจจำเป็นต้องค้นหาจากการจัดรูปแบบ (format) ของเซลล์ ทำได้โดยการใช้ format... และกำหนดรูปแบบที่ต้องการค้นหา ดังภาพด้านล่าง

    โดยในตัวอย่างเราต้องการจะค้นหาเซลล์ที่มีใส่สีส้มไว้ ซึ่งก็จะมีเซลล์เดียวคือ A3

    ซึ่งเรากำหนดได้จากเลือกที่แถบ Fill และเลือกสีส้ม

    ⚠️ โดยการค้นหาแบบนี้ จะเป็นแบบ และ (AND) กับช่องข้อความ
    💡 หากเราต้องการค้นหาแค่รูปแแบบไม่ระบุข้อความ ก็เว้นว่างไว้ได้

    ฟังก์ชันที่ใช้ wildcards ได้

    ถึงใน Excel จะไม่สามารถใช้อักขระแทนได้ทุกฟังก์ชัน แต่ฟังก์ชันเหล่านี้จะใช้ได้

    การใช้อักขระแทน (wildcards) ใน Conditional Formatting

    อย่างที่พูดถึงไว้ด้านบน เราสามารถใช้ wildcards ใน Conditional Formatting ได้เช่นกัน โดยใช้ร่วมกับฟังก์ชันที่สนับสนุน wildcards ⬆️ ด้านบน อย่างเช่น เราต้องการให้ชื่ออำเภอที่ขึ้นต้นด้วยคำว่า “Ban “ หรือ “บ้าน” แสดงเป็นตัวหนาสีน้ำเงิน ดังภาพนี้

    จะทำได้โดย

    1. เลือก Range ที่ต้องการทำ Conditional Formatting
    2. ไปที่ Home > Conditional Formatting > New Rule…
    3. เลือก Use a formula to determine which cells to format
    4. ใส่สูตร =IF(COUNTIF(A2,"Ban *"),TRUE,FALSE)
    5. กดปุ่ม Format... และปรับรูปแบบที่ต้องการ

    และเราก็จะเห็นชื่ออำเภอที่ขึ้นต้นด้วยคำว่า “Ban ” เป็นตัวหนาเอียงสีน้ำเงิน

    ลองดูไฟล์ตัวอย่างนี้ จะทำให้เข้าใจได้ง่ายขึ้นครับ

    สรุป

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

    หรือในอีกทางคือ ช่วยให้ match สิ่งที่ค้นหาจากข้อมูลบางส่วนที่เหมือนกัน อย่างในตัวอย่างการทำ Conditional Formatting

  • การใช้ฟังก์ชัน XLOOKUP ใน Excel

    ภาพรวม

    XLOOKUP ใช้ค้นหาค่าที่เราสนใจใน range หรือ table จากอีกค่าหนึ่งที่เรารู้อยู่แล้ว (keyword) โดยเปรียบเทียบง่าย ๆ ก็เหมือนเลขในบาร์โค้ด เมื่อเราสแกนแล้วระบบจะดึงชื่อสินค้าและราคามาให้เราเห็นนั่นแหละ

    ถ้าใน Excel จะมีฟังก์ชันในกลุ่มนี้อยู่หลายตัว แต่ตัวใหม่ล่าสุดคือ XLOOKUP นี่แหละ ซึ่ง Microsoft บอกว่า ทำงานได้ไวกว่าด้วย

    ⚠️ โดยผู้ที่จะใช้งาน XLOOKUP ได้ จะต้องมี Microsoft 365 ใน Excel 2016 และ Excel 2019 ไม่มีให้ใช้ครับ

    รูปแบบการใช้งาน (Syntax)

    =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
    จำเป็น?อาร์กิวเมนต์คำอธิบาย
    จำเป็นlookup_valueค่าที่เรารู้ และจะใช้ค้นหาค่าอื่น
    จำเป็นlookup_arrayarray หรือ range ที่คาดว่าจะมีค่าที่เราค้นหา
    จำเป็นreturn_arrayarray หรือ range ที่เราต้องการข้อมูล
    ไม่ใส่ก็ได้[if_not_found]หากไม่พบ จะให้แสดงข้อความว่าอะไร? หากไม่ระบุจะได้เป็น #N/A
    ไม่ใส่ก็ได้[match_mode]ระบุรูปแบบผลลัพธ์ที่อยากได้ 0 — ต้องตรงกันเป๊ะ ๆ หากไม่พบค่าที่กำหนดไว้ จะได้ที่เราระบุไว้ใน [if_not_found] หรือ #N/A หากไม่ใส่ XLOOKUP จะค้นหาด้วยแบบนี้โดยปริยาย (default)-1 — ต้องตรงกันเป๊ะ ๆ แต่หากไม่พบค่าที่กำหนดไว้ จะได้ค่าใกล้เคียงที่น้อยกว่ามาแทน1 — ต้องตรงกันเป๊ะ ๆ แต่หากไม่พบค่าที่กำหนดไว้ จะได้ค่าใกล้เคียงที่มากกว่ามาแทน2 — ค้นหาแบบใช้อักขระแทน โดยที่ ? * และ ~ จะมีความหมายเฉพาะ
    ไม่ใส่ก็ได้[search_mode]ระบุรูปแบบการค้นหา โดยที่ 1 — ค้นหาเริ่มจากแถวแรกลงไป หากไม่ระบุจะใช้รูปแบบนี้โดยปริยาย (default)-1 — ค้นหาจากแถวสุดท้ายขึ้นมา2 — ค้นหาแบบทวิภาค 😂 อย่าไปสนใจคำนี้มากครับ เอาเป็นว่ามันจะเจอค่าได้เร็วกว่า หาก lookup_array เรามีข้อมูลเยอะ แต่การใช้วิธีนี้ lookup_array จะต้องเรียงลำดับจากน้อยไปหามากไว้แล้วเท่านั้น (ascending order)-2 — ค้นหาแบบทวิภาคเหมือนกัน เพียงแค่ lookup_array จะต้องเรียงลำดับจากมากไปหาน้อยไว้แล้วเท่านั้น (descending order)

    ตัวอย่างการใช้งาน

    เอาเหมือนที่เรายกตัวอย่างไว้ในตอนแรก สมมติว่าเรามีข้อมูลดังภาพนี้ โดยผมจะให้ข้อมูลนี้อยู่ในชีตที่ชื่อว่า products

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

    =XLOOKUP(A3,products!$A$2:$A$10,products!$B$2:$B$10,"Not found!")

    ในสูตรด้านบนนี้ ผมว่าข้อมูลสินค้าไว้ในชีตชื่อ products นะ เลยมีคำว่า products! อยู่หน้าตำแหน่งของเซลล์

    ดูและดาวน์โหลด (File > Save As > Download a Copy) ไฟล์ตัวอย่างมาดูก็ได้ครับ

    โดยบาร์โค้ดที่เราเลือกหรือป้อนเข้าไปจะอยู่ในเซลล์ A3 ส่วน lookup_array ที่เราจะเปรียบเทียบอยู่ชีตชื่อ products ตั้งแต่ A2 ถึง A10

    หากเจอ XLOOKUP จะนำค่าที่อยู่ในแถวเดียวกันในคอลัมภ์ B (return_array) มาแสดง

    ส่วนถ้าไม่พบ จะแสดงเป็นคำว่า Not found!

    สำหรับการดึงราคาแสดง โดย return_array จะอยู่ในคอลัมภ์ C สูตรก็จะเป็นดังนี้

    =XLOOKUP(A3,products!$A$2:$A$10,products!$C$2:$C$10,"Not found!")

    ซึ่งผลลัพธ์ก็จะเป็นตามภาพนี้

    ดูไว ๆ อาจดูเหมือนจะซับซ้อนกว่าการใช้ VLOOKUP อยู่พอสมควร นั่นเพราะ XLOOKUP จะยืดหยุ่นกว่าตรงที่จะหาค่าได้ทั้งด้านซ้ายและด้านขวาของคอลัมภ์ที่เราค้นหา (lookup_array)

    ซึ่ง VLOOKUP ทำไม่ได้ return_array ต้องอยู่ขวามือเท่านั้น และจะค้นหาค่าที่เราระบุในคอลัมภ์แรกของ table_array เท่านั้น ลองดูตัวอย่างนี้

    LOOKUP เอาข้อมูลที่อยู่ด้านซ้ายมือ

    ข้อมูลที่ใช้จะเป็นคะแนนสอบของนักเรียน โดยจะทำเป็นตาราง (Format as Table) ชื่อว่า tblStudentScore ตามภาพด้านล่างนี้

    แต่คราวนี้ เราบังเอิญอยากหาคะแนนจากชื่อเล่น (preferred_name) ของนักเรียน อย่างในภาพนี้

    ซึ่งจะเห็นว่า ชื่อเล่น (preferred_name) ของนักเรียนในตารางข้อมูล อยู่ในคอลัมภ์ที่ 4 เลย

    และสูตรที่เราใช้ในแต่ละคอลัมภ์จะเป็น

    • student_id
      =XLOOKUP(A4,tblStudentScore[preferred_name],tblStudentScore[student_id],"Not found!",0,1)
    • first_name
      =XLOOKUP(A4,tblStudentScore[preferred_name],tblStudentScore[first_name],"Not found!",0,1)
    • last_name
      =XLOOKUP(A4,tblStudentScore[preferred_name],tblStudentScore[last_name],"Not found!",0,1)
    • score
      =XLOOKUP(A4,tblStudentScore[preferred_name],tblStudentScore[score],"Not found!",0,1)

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

    หากใครอ่านสูตรข้างบนแล้วสงสัยว่า tblStudentScore คืออะไร ลองไปอ่านวิธีการ Format as Table เพิ่มนะครับ ผมชอบวิธีนี้ เพราะดูสตรที่เราเขียนแล้วมันเข้าใจง่ายดี

    อีกอย่างคือ ถ้ามีการเพิ่มข้อมูลเข้าไป เราไม่ต้องมาแก้ไขสูตร เพื่อเพิ่มแถวเพิ่มคอลัมภ์

    XLOOKUP ดึงข้อมูลล่าสุดจากตาราง

    เป็นอีกอย่างหนึ่งที่ผมชอบใน XLOOKUP คือ เพียงเราเปลี่ยนอาร์กิวเมนต์ตัวเดียว เราก็จะได้ข้อมูลล่าสุดที่ต่อลงมาในตาราง โดยเฉพาะกรณีที่ใช้งานร่วมกับ Microsoft Forms ในที่ทำงาน ซึ่งเราสามารถ Sync ข้อมูลกับ Excel ได้เลย (เหมือน Google Forms กับ Google Sheets แหละ แต่เหมือน Microsoft พึ่งจะเปิดให้ใช้ไม่นานมานี้เอง 😒)

    ในตัวอย่างนี้ ยกตัวอย่าง “รุ่ง” ซึ่งสอบได้ 56 คะแนน แล้วเราให้เขาสอบมาใหม่ สูตรที่จะใช้หาค่านี้ก็จะเป็น

    =XLOOKUP(A13,tblStudentScore[preferred_name],tblStudentScore[score],"Not found!",0,-1)

    ซึ่ง -1 หมายถึงให้ดูข้อมูลจากล่างสุดขึ้นมาตามที่อธิบายไว้ในรูปแบบการใช้งาน (syntax) ก็จะได้ผลดังภาพ

    โดยถ้าเราดูจากข้อมูลจะเห็นว่า “รุ่ง” สอบรอบที่ 2 ได้คะแนน 78 คะแนน

    LOOKUP ดึงข้อมูลที่มีค่ามากที่สุด หรือน้อยที่สุด

    พออ่านมาถึงตรงนี้ บางคนอาจสงสัยว่า เอ… ถ้าเราเป็นครู/อาจารย์ที่ใจดีแบบสุด ๆ จะเอาคะแนนมากที่สุดที่สอบได้ทำไง?

    คำตอบคือ เราจะไม่ใช้ทั้ง VLOOKUP และ XLOOKUP นะครับ

    เราจะใช้ MAXIFS หรือไม่ก็ MINIFS แทน อย่างในภาพนี้

    จากภาพด้านบน สูตรใน

    • E18
      =MAXIFS(tblStudentScore[score],tblStudentScore[preferred_name],A18)
    • E19
      =MINIFS(tblStudentScore[score],tblStudentScore[preferred_name],A19)

    ส่วนการใช้ MAXIFS กับ MINIFS จะเหมือนกับ COUNTIFS ที่เคยเขียนไว้ก่อนหน้านี้ครับ

    XLOOKUP แบบ binary search

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

    ซึ่งการจะใช้วิธีค้นหาแบบนี้ ข้อมูลเราต้องถูกจัดลำดับค่าคีย์ (key) ที่จะค้นหาก่อน ไม่ว่าจะจากน้อยไปหามาก หรือกลับกัน เช่นข้อมูลสินค้าในตัวอย่างแรก เมื่อนำ barcode มาเรียงลำดับ โดยในที่นี้ผมใช้ SORTBY ดึงและนำมาเรียงจากน้อยไปหามาก และสูตรอยู่ในคอลัมภ์ E ดังภาพ

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

    =XLOOKUP(A4,products!$E$2:$E$10,products!$F$2:$F$10,"Not found!",0,2)

    กลับหากเรียงจากมากไปหาน้อยก็จะต้องเป็นเลข -2 โดยผมทำไว้ให้ดูในไฟล์ตัวอย่างด้วย

    เขียนไปเขียมมาชักเริ่มยาว 😆 ด้วยความที่ XLOOKUP ใช้งานได้หลายแบบ ถ้าในอ่านแล้วงงงง ก็ comment ไว้ได้เลยนะครับ

    เอาไว้มาเขียนการใช้งานในแบบอื่นต่อในโพสต์ถัดไป

    สิ่งที่ควรทราบ

    • XLOOUP สามารถค้นหาได้ทั้งแบบแถว (Vertical) และคอลัมภ์ (Horizontal)
    • หากไม่ระบุอาร์กิวเมนต์ if_not_found หาก XLOOKUP ไม่พบข้อมูล จะแสดงผลเป็น #N/A
    • ⚠️ขนาดของ range หรือ array ของ lookup_array กับ return_array ต้องมีขนาดเท่ากัน ไม่เช่นนั้น XLOOPUP จะแสดงผลเป็น #VALUE!
    • ⚠️หากใช้ XLOOKUP ไปดูข้อมูลจากตารางในไฟล์ (workbook) อื่น ไฟล์นั้น ๆ จะต้องเปิดไว้ด้วย ไม่เช่นนั้นคุณจะพบ #REF!

    สรุป

    XLOOPUP มีไว้ช่วยเราหาข้อมูลจากค่าคีย์เวิร์ด (keyword) หรือคำค้นที่เรารู้อยู่แล้ว จากตารางข้อมูลไม่ว่าจะเป็น range หรือ array

  • การใช้ฟังก์ชัน RANDARRAY ใน Excel

    ภาพรวม

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

    รูปแบบการใช้งาน (Syntax)

    =RANDARRAY([rows],[columns],[min],[max],[whole_number])
    จำเป็น?อาร์กิวเมนต์คำอธิบาย
    ไม่ใส่ก็ได้[row]ตัวเลขจำนวนแถวที่เราต้องการ ถ้าไม่ระบุจะได้ 1 แถว
    ไม่ใส่ก็ได้[column]ตัวเลขจำนวนคอลัมภ์ที่เราต้องการ ถ้าไม่ระบุจะได้ 1 คอลัมภ์
    ไม่ใส่ก็ได้[min]ตัวเลขระบุจำนวนที่ค่าน้อยสุดที่ต้องการ ถ้าไม่ระบุจะได้เลยทศนิยม หรือ 0.xxxxxx
    ไม่ใส่ก็ได้[max]ตัวเลขระบุจำนวนที่ค่ามากสุดที่ต้องการ ถ้าไม่ระบุค่ามากสุดคือ 0.999999
    ไม่ใส่ก็ได้[whole_number]ตัวเลขระบุจะกำหนดให้สุ่มค่าเป็นจำนวนเต็มไหม? หากต้องการให้ระบุเป็น TRUE ถ้าไม่ระบุจะเป็น FALSE

    ตัวอย่างการใช้งาน

    ที่จริงแล้ว ฟังก์ชันนี้ใช้ได้อย่างตรงไปตรงมา คือ จะได้เลขที่สุ่มขึ้นมา โดยที่คุณไม่ต้องคิดเอง และไม่ต้องใช้ Autofill ลากเพิ่มเหมือนการใช้ฟังก์ชัน RAND และ RANDBETWEEN

    สำหรับไฟล์ตัวอย่างสามารถดูและดาวน์โหลด ได้เลยครับ (File > Save As > Download a Copy)

    ตามรูปแบบการใช้งานด้านบน ⬆️ หากเราอยากได้ข้อมูล 4 แถว 3 คอลัมภ์ สูตรก็จะเป็น

    =RANDARRAY(4,3)

    โดยผลลัพธ์จะได้ค่าระหว่าง 0 กับ 1 มาตามจำนวนแถวและคอลัมภ์ที่เราระบุไว้ดังภาพ

    ที่นี้สมมติว่าเราอยากได้ค่าระหว่าง 0 ถึง 10,000 สูตรเราก็จะต้องเพิ่มมาเป็น

    =RANDARRAY(4,3,0,10000)

    และผลก็จะออกมาเป็นดังภาพนี้

    ซึ่ง 2 ตัวอย่างด้านบนเราก็จะยังได้ค่าที่มีทศนิยมอยู่ด้วยเสมอ เพราะเราไม่ได้ระบุว่า จะเอาเป็นจำนวนเต็ม (whole number) โดยสูตรก็จะกลายเป็น

    =RANDARRAY(4,3,0,10000,TRUE)

    และผลจะเป็นดังนี้

    สิ่งที่ควรทราบ

    • หากคุณใช้แบบไม่ระบุจำนวนแถวและคอลัมภ์ให้ RANDARRAY() คุณจะได้ผลลัพธ์มาค่าเดียว เหมือนกับใช้ฟังก์ชัน RAND() โดยค่าที่ได้จะอยู่ระหว่าง 0 กับ 1
    • หากคุณระบุแถวและคอลัมภ์ แต่ไม่ระบุค่าต่ำสุดและค่าสูงสุด ผลลัพธ์ที่ได้จะมีค่าอยู่ระหว่าง 0 กับ 1
    • ⚠️ หากคุณค่าต่ำสุดที่คุณกำหนดนั้น มีค่ามากกว่าค่าสูงสุดที่คุณกำหนดไว้ จะแสดงเป็น #VALUE!
    • 💡หากอยากได้ค่าที่เป็นจำนวนเต็ม อย่าลืมระบุอาร์กิวเมนต์สุดท้ายเป็น TRUE
    • RANDARRAY เป็นฟังก์ชันแบบ dynamic array ดังนั้นผลลัพธ์ที่ได้จะเริ่มจากเซลล์ที่เราใส่สูตรไปและไหล (spill) ลงด้านล่างไปจนกว่าจะได้จำนวนแถวที่เราระบุไป รวมถึงไปด้านซ้ายไปจนกว่าจะถึงความกว้างของคอลัมภ์ที่เราระบุไว้ ⚠️หากมีข้อมูลอยู่แล้วจะแสดง error เป็น #SPILL!
    • RANDARRAY ทำงานเหมือนฟังก์ชัน RAND กับ RANDBETWEEN แต่คุณไม่จำเป็นต้อง copy ไปใส่เซลล์อื่น
    • ⚠️ ไม่แนะนำให้ใช้ฟังก์ชันนี้ เป็นค่าที่ไฟล์อื่นอ้างอิง หรือ LOOKUP กับค่าที่ได้จากสุ่มนี้ เพราะมีโอกาสสูงมากที่คุณจะเห็น #REF!

    สรุป

    ฟังก์ชันนี้ใช้สุ่มตัวเลขให้เรา ซึ่งจะเหมาะสำหรับการทำไฟล์ตัวอย่าง (mock-up) ให้คนในทีมงานหรือหัวหน้าเห็นภาพ report ว่าจะออกมาประมาณไหน ก่อนที่จะได้ report จากข้อมูลจริงซึ่งอาจจำเป็นต้องรอ

  • การใช้ฟังก์ชัน SEQUENCE ใน Excel

    ภาพรวม

    ฟังก์ชัน SEQUENCE ใช้สำหรับสร้างตัวเลขแบบเรียงลำดับ อย่างเช่น 1, 2, 3, … โดยเราสามารถกำหนดได้ว่าจะเอากี่แถวกี่คอลัมภ์ รวมไปจุดเริ่มต้นและขั้นละเท่าไหร่ด้วย ต้วอย่างเช่น เราต้องการเลขเรียงลำดับเริ่มจาก 1 เพิ่มทีละ 1 จำนวน 3 แถว 4 คอลัมภ์ ดังภาพด้านล่างนี้

    สูตรก็จะเป็น

    =SEQUENCE(3,4)

    รูปแบบการใช้งาน (Syntax)

    =SEQUENCE(rows,[columns],[start],[step])
    จำเป็น?อาร์กิวเมนต์คำอธิบาย
    จำเป็นrowตัวเลขจำนวนแถวที่เราต้องการ
    ไม่ใส่ก็ได้[column]ตัวเลขจำนวนคอลัมภ์ที่เราต้องการ ถ้าไม่ระบุจะได้ 1 คอลัมภ์
    ไม่ใส่ก็ได้[start]ตัวเลขระบุจำนวนที่เป็นเริ่มต้น ถ้าไม่ระบุจะเริ่มที่ 1
    ไม่ใส่ก็ได้[step]ตัวเลขระบุจำนวนที่จะเพิ่มขึ้นในแต่ละขั้น ถ้าไม่ระบุจะเพิ่มขึ้นที่ละ 1

    สิ่งที่ควรทราบ

    • 💡ถ้าเราไม่ใส่อาร์กิวเมนต์ที่ระบุว่า ไม่ใส่ก็ได้ Excel จะสมมติว่ามันคือ 1
    • ผลลัพธ์ที่ได้จะเริ่มจากเซลล์ที่เราใส่สูตรไปและไหล (spill) ลงด้านล่างไปจนกว่าจะได้จำนวนแถวที่เราระบุไป รวมถึงไปด้านซ้ายไปจนกว่าจะถึงความกว้างของคอลัมภ์ที่เราระบุไว้ ⚠️หากมีข้อมูลอยู่แล้วจะแสดง error เป็น #SPILL!
    • ⚠️ หากคุณอ้างอิงค่าที่ได้จากฟังก์ชันนี้ข้าม workbook (ไฟล์) หากไฟล์ที่อ้างถึงไม่ถูกเปิดใช้งาน คุณจะได้ error เป็น #REF! ดังนั้นไม่แนะนำให้ใช้ร่วมกับค่าที่ต้อง LOOKUP จากไฟล์อื่น

    ตัวอย่างการใช้งาน

    ที่จริงแล้วฟังก์ชันนี้ น่าจะมีจุดประสงค์เพื่อช่วยให้เราเตรียมข้อมูลบางอย่างไปทำต่อ โดยที่ไม่ต้องพิมพ์เองซ้ำ ๆ เช่น การสร้างเดือนเรียงจากมกราคมไปถึงธันวาคม โดยไม่ต้องใช้ Auto Fill ซึ่งก็ง่ายอยู่แล้วล่ะ 😂โดยไฟล์ตัวอย่างสามารถดูและดาวน์โหลดได้เลยครับ (File > Save As > Download a Copy)

    =TEXT(DATE(YEAR(TODAY()),SEQUENCE(1,12),1),"mmm")

    โดยผลลัพธ์ที่ได้ก็จะประมาณนี้

    หากต้องการเดือนเป็นคำเต็ม ๆ ก็ให้เพิ่ม m ไปอีกตัว จาก mmm เป็น mmmm

    สร้างลำดับเป็นเลขถอยหลัง

    หรือถ้าใครอยากใช้นับเลขแบบถอยหลัง ก็สามารถระบุตัว [step] .ให้ติดลบได้อย่างเช่น

    =SEQUENCE(10,1,9,-1)

    ผลลัพธ์

    สร้างลำดับจากเลขติดลบให้เพิ่มขึ้นเรื่อย ๆ

    สำหรับกรณีนี้ ให้ระบุ [start] ให้มีค่าติดลบ เช่น

    =SEQUENCE(10,1,-9,1)

    ผลลัพธ์

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

  • การใช้ฟังก์ชัน SORTBY ใน Excel

    ภาพรวม

    ฟังก์ชัน SORTBY ใช้ดึงพร้อมกับเรียงลำดับข้อมูลจาก array หรือ range อื่น โดยที่ข้อมูลที่ได้มา จะเปลี่ยนไปตามข้อมูลต้นฉบับและเรียงลำดับให้ใหม่ด้วย

    หากใครได้อ่านโพสต์ก่อนที่หน้านี้ ที่เราพูดถึงฟังก์ชัน SORT ไปแล้ว การใช้งานจะคล้ายกัน แต่ SORT จะเรียงลำดับตามข้อมูลใน array หรือ range โดยยึดที่คอลัมภ์ใดคอลัมภ์หนึ่งเท่านั้น หรือแถวใดแถวหนึ่งเท่านั้น ในกรณีที่เรากำหนดให้จัดลำดับข้อมูลที่วางแบบแนวคอลัมภ์

    แต่ใน SORTBY จะเรียงลำดับข้อมูลให้จาก 1 คอลัมภ์หรือมากกว่านั้นก็ได้ และคอลัมภ์ที่เป็นเงื่อนไขในการจัดเรียงก็ไม่จำเป็นต้องอยู่ในข้อมูลด้วย

    ตัวอย่างการใช้งาน

    ตัวอย่างข้อมูลด้านล่าง สมมติเราต้องการจัดเรียงข้อมูลพนักงานตามแผนกจากน้อยไปหามาก (A-Z)

    โดยข้อมูลนี้สามารถ ดูและดาวน์โหลด ได้เลย (File > Save As > Download a Copy)

    สูตรที่ใช้ก็จะเป็นดังนี้ (กรณีที่ข้อมูลทำเป็น table ไว้แล้ว)

    =SORTBY(tblEmployees[#Data],tblEmployees[dept])

    หรือสำหรับข้อมูลที่เป็น range สูตรจะเป็น

    =SORTBY($A$2:$F$21,$F$2:$F$21)

    💡โดยจะสังเกตว่า การใช้งานแบบนี้ก็แทบจะเหมือนกับฟังก์ชัน SORT เลย

    แต่ที่จริงแล้ว SORTBY มันยืดหยุ่นกว่านั้น มาดูกันครับ

    จัดเรียงข้อมูล โดยเงื่อนไขที่ไม่ได้อยู่ในข้อมูล

    ตัวอย่างจากข้อมูลชุดเดิม เราอยากได้ชื่อพนักงานเรียงตามอายุ แต่อายุอาจดูก้ำกึ่งเป็น sensitive data เราเลยไม่อยากให้มันแสดงในรายการ สูตรก็จะเป็น

    =SORTBY(tblEmployees[[emp_id]:[preferred_name]],tblEmployees[dept])

    และก็จะได้ผลลัพธ์ดังนี้

    จัดเรียงข้อมูลจากหลายเงื่อนไข (หลายคอลัมภ์)

    กรณีนี้จะคล้ายกับการเรียงลำดับข้อมูลหลายระดับที่เราคลิกเลือกเอาใน Excel เลย เพียงแค่เราเขียนในสูตรใส่ในเซลล

    เช่น หากเราอยากเรียงลำดับข้อมูลพนักงานตาม
    1) แผนก (จากน้อยไปหามาก)
    2) รหัสพนักงาน (จากมากไปหาน้อย)

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

    =SORTBY(tblEmployees[#Data],tblEmployees[dept],1,tblEmployees[emp_id],-1)

    ส่วนสำหรับ range ปกติจะเป็น

    =SORTBY($A$2:$F$21,$F$2:$F$21,1,$A$2:$A$21,-1)

    หวังว่าคงพอเห็นภาพการใช้งานฟังก์ชัน SORTBY และความแตกต่างกับ SORT ได้ดีขึ้นนะครับ

    รูปแบบการใช้งาน

    =SORTBY(array,by_array1,[sort_order1],[by_array2],[sort_order2],...)
    จำเป็น?อาร์กิวเมนต์คำอธิบาย
    จำเป็นarrayข้อมูลที่เราต้องการและจะนำมาเรียงลำดับ อาจจะเป็น range หรือ array ที่ได้มาจากพวกฟังก์ชัน dynamic array ทั้งหลาย
    จำเป็นby_array1range หรือ array ที่เป็นเงื่อนไขหรือรูปแบบการเรียงลำดับ
    ไม่ใส่ก็ได้[sort_order1]ตัวเลขใช้ระบุวิธีการจัดลำดับ โดย ใช้ 1 หากต้องการเรียงจากน้อยไปมากและให้ใช้ -1 เมื่อต้องการให้เรียงจากมากไปน้อย
    ไม่ใส่ก็ได้[by_array2]range หรือ array ลำดับที่ 2 ที่เป็นเงื่อนไขหรือรูปแบบการเรียงลำดับ
    ไม่ใส่ก็ได้[sort_order2]ตัวเลขใช้ระบุวิธีการจัดลำดับของ [by_array2] โดย ใช้ 1 หากต้องการเรียงจากน้อยไปมากและให้ใช้ -1 เมื่อต้องการให้เรียงจากมากไปน้อย

    และ ... นั้นหมายถึง คุณสามารถใส่คูุ array หรือ range กับตัวเลขระบุวิธีการจัดอันดับไปได้เรื่อย ๆ

    สิ่งที่ควรทราบ

    • หากขนาดของ array หรือ range ของ array กับ by_array1 ไม่เท่ากัน จะแสดง error เป็น #VALUE!
    • อาร์กิวเมนต์ by_array1 จะต้องเป็น 1 คอลัมภ์ หรือ 1 แถวเท่านั้น
    • คำว่าเรียงลำดับจากน้อยไปมาก หรือจากมากไปน้อย นั้นจะหมายถึงตามลำดับใน ASCII code และ มอก. 620 นะ 💡แต่ถ้าเป็นเลขไทยลำดับจะมาก่อน ก ถึงแม้ว่าลำดับใน มอก. 620 จะมากกว่าอักขระ และจะให้ค่ากลับมาเป็นเลขอารบิก 😂 ก็คือ Excel น่าจะมีฟังก์ชันแปลงให้อีกทีแหละ
    • ฟังก์ชัน SORT เป็นหนึ่งในฟังก์ชันแบบ dynamic array ดังนั้นคุณอาจเจอ #SPILL! ได้ อ่านข้อแนะนำการใช้งานได้จาก วิธีแก้ SPILL error ใน Excel

  • การใช้ฟังก์ชัน SORT ใน Excel

    การเรียงลับดับข้อมูลใน Excel ไม่ใช้เรื่องใหม่ 😊 แต่ที่อาจจะดูใหม่คงจะเป็นการมีฟังก์ชันเรียงลำดับข้อมูลให้เราเรียกใช้และให้ข้อมูลกลับมาแบบ dynamic (เปลี่ยนไปตามข้อมูลต้นฉบับ) นี่พึ่งจะมีให้ใช้ใน Excel 2021

    นั่นคือ ตั้งแต่ก่อนพวกเราต้องทำแบบ manual คือไปที่ Data > Sort หรือผ่าน VBA มาตลอด 😂

    ภาพรวม

    ฟังก์ชัน SORT ใช้ดึงเอาข้อมูลออกมาจาก range หรือ array ที่เรามีอยู่มาเรียงลำดับใหม่ในแบบที่เราต้องการ

    รูปแบบการใช้งาน (syntax)

    =SORT(array,[sort_index],[sort_order],[by_col])
    จำเป็น?อาร์กิวเมนต์คำอธิบาย
    ต้องใส่arrayข้อมูลที่เราต้องการและจะนำมาเรียงลำดับ อาจจะเป็น range หรือ array ที่ได้มาจากพวกฟังก์ชัน dynamic array ทั้งหลาย
    ไม่ใส่ก็ได้[sort_index]ตัวเลขใช้ระบุแถวหรือคอลัมภ์ที่เราจะให้เรียงลำดับ
    ไม่ใส่ก็ได้[sort_order]ตัวเลขใช้ระบุวิธีการจัดลำดับ โดย ใช้ 1 หากต้องการเรียงจากน้อยไปมาก และให้ใช้ -1 เมื่อต้องการให้เรียงจากมากไปน้อย
    ไม่ใส่ก็ได้[by_col]ใช้ระบุว่าให้ดูข้อมูลไปตามแนวคอลัมภ์หรือไม่? FALSE – ให้เรียงลำดับตามแถว หรือไม่ระบุก็จะได้วิธีการเรียงลำดับแบบนี้TRUE – ให้เรียงลำดับตามคอลัมภ์ กรณีนี้คือ ข้อมูลที่เราจะเรียงลำดับ กระจายไปตา่มแนวของคอลัมภ์

    ตัวอย่างการใช้งาน

    ในตัวอย่างของภาพด้านล่างนี้ เรามีข้อมูลบุคคลและคะแนนสอบ และเราอยากได้ข้อมูลอีกชุดหนึ่งที่เรียงลำดับตามคะแนน post test จากมากไปหาน้อย

    หากต้องการไฟล์ตัวอย่างสามารถ เปิดดูและดาวน์โหลด ได้เลย

    สูตรที่ใช้ใน F2 จะเป็นแบบนี้ โดยข้อมูลอยู่ในตารางชื่อ tblPeopleTestScore

    =SORT(tblPeopleTestScore[#Data],4,-1,FALSE)

    โดยที่

    • array ข้อมูลต้นฉบับที่เราใส่เข้าไปคือ tblPeopleTestScore[#Data]
    • โดยเราจะจัดเรียงข้อมูลชุดใหม่จากคอลัมภ์ที่ 4 หรือ post_test นับเริ่มจาก 1
    • ให้เรียงลำดับจากมากไปน้อย จึงใส่ -1
    • และช่องสุดท้ายใส่ FALSE เพราะข้อมูลเราไหลลงไปตามแถว หรือที่จริงแล้วจะไม่ใส่ก็ได้

    มาดูอีกตัวอย่าง เป็นข้อมูลเดียวกันเลย เพียงแค่ข้อมูลถูกวางมาแบบไหลจากซ้ายไปขวา หรือไปตามแนวคอลัมภ์ ดังนั้นเวลาจะเรียงตามคะแนน post test สูตรก็จะเป็น

    =SORT($A$1:$J$4,4,-1,TRUE)

    ดุังภาพนี้

    💡สังเกตว่ากรณีนี้ สูตรที่ผมใช้จะไม่ได้เป็นแบบ table นะครับ เพราะถ้าข้อมูลมาแบบนี้ จะไม่เป็นไปตามวิธีการวางข้อมูลแบบ table ดังนั้นเราจะใช้การอ้างอิง range ตามปกติ

    สิ่งที่ควรทราบ

    • ถ้าเราไม่ระบุอาร์กิวเมนต์ที่ไม่บังคับ ไม่ว่าจะเป็น
      • sort_index ให้ Excel จะใช้แถวที่ 1 หรือไม่ก็คอลัมภ์ที่ 1 หากอาร์กิวเมนต์สุดท้ายเป็น TRUE
      • sort_order ปกติ Excel จะเรียงจากน้อยไปหามากให้
      • by_col ปกติจะเรียงไปตามแถว ยกเว้นเราจะระบุเป็น TRUE
    • คำว่าเรียงลำดับจากน้อยไปมาก หรือจากมากไปน้อย นั้นจะหมายถึงตามลำดับใน ASCII code และ มอก. 620 นะ 💡แต่ถ้าเป็นเลขไทยลำดับจะมาก่อน ก ถึงแม้ว่าลำดับใน มอก. 620 จะมากกว่าอักขระ และจะให้ค่ากลับมาเป็นเลขอารบิก 😂 ก็คือ Excel น่าจะมีฟังก์ชันแปลงให้อีกทีแหละ
    • ฟังก์ชัน SORT จะเรียงข้อมูลได้เฉพาะตามที่มีอยู่ใน array หรือ range ที่ระบุให้เท่านั้น หากคุณต้องการจัดเรียงข้อมูลตามค่าที่อยู่นอก array ที่ระบุให้ แนะนำให้ไปใช้ฟังก์ชัน SORTBY แทน เพราะเป็นฟังก์ชันที่มีความยืดหยุ่นกว่า
    • ฟังก์ชัน SORT เป็นหนึ่งในฟังก์ชันแบบ dynamic array ดังนั้นคุณอาจเจอ #SPILL! ได้ อ่านข้อแนะนำการใช้งานได้จาก วิธีแก้ SPILL error ใน Excel
    • คุณสามารถใส่ array หรือ range ที่แบบที่มี
      • 1 คอลัมภ์หลายแถว
      • 1 แถว หลายคอลัมภ์
      • หรือหลายแถวและหลายคอลัมภ์
      ก็ได้

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

    ฟังก์ชัน SORT ก็จะช่วยเราได้เยอะเลย เพราะข้อมูลแหล่งเดียวแต่เราได้รูปแบบที่ต้องการเลย ไม่ต้องไปไล่คลิกและ copy-paste หลายขั้นตอน

  • ฟังก์ชัน dynamic array ใน Excel

    หนึ่งในคำถามที่ผมพบอยู่บ่อย ๆ สำหรับคนใช้ Excel คือ

    อยากให้ VLOOKUP ได้ผลลัพธ์หลายค่า จากคำค้นหาคำเดียว ทำยังไง?

    ซึ่งคำถามนี้ก็รวมไปถึง XLOOKUP ซึ่งเป็นฟังก็ชันน้องใหม่ที่แนะนำให้ใช้แทน VLOOKUP สำหรับผู้ใช้ Excel รุ่นใหม่ๆ ด้วย

    เพราะหลาย ๆ คนอยากให้ VLOOKUP หรือ XLOOKUP แล้วได้ข้อมูลที่ตรงกับเงื่อนไข (match) ออกมาทั้งหมด หรือได้ทุกค่าที่ตรง

    คำตอบง่ายสุดคือ ไม่ได้ เพราะ 2 ฟังก์ชันนี้ไม่ได้ออกแบบมาเพื่องานนี้

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

    วันนี้เราจะมาพูดถึงฟังก์ชันประเภท dynamic array ใน Excel กัน และ FILTER() ก็เป็นหนึ่งในฟังก์ชันประเภทนี้

    ภาพรวม

    หากจะถามว่า “ฟังก์ชัน dynamic array คืออะไร? ” คำตอบง่าย ๆ คือ

    ฟังก์ชันที่ให้ค่ากลับคืนมาได้มากกว่า 1 ค่า หรือที่เรียกเป็นภาษาอังกฤษว่า array ซึ่งมันคือ รายการข้อมูลชุดหนึ่งนั่นเอง

    โดยฟังก์ชันกลุ่มนี้เริ่มมีให้ใช้ราว ๆ ปี 2020 ในกลุ่มผู้ใช้งาน Excel ในชุดแอป Microsoft 365

    ซึ่งแตกต่างจากฟังก์ชันเดิม ๆ ในเอกซ์เซลที่จะคืนค่าหรือให้ผลลัพธ์กลับมาให้เรา “เพียงค่าเดียว” เท่านั้น

    โดยโพสต์ก่อนหน้านี้ ผมค่อนข้างจะพูดถึงฟังก์ชันกลุ่มนี้บ่อย เลยคิดว่า จะจับกลุ่มรวมไว้ในโพสต์นี้เลย

    โดย dynamic array ถือว่าเป็นจุดเปลี่ยนของการใช้งาน Excel ไปอีกขั้นหนึ่ง เพราะตั้งแต่ก่อน เราไม่แทบจะไม่สามารถกรอง (filter) เอาข้อมูลได้ง่าย ๆ เลย ยกเว้นจะใช้วิธีแบบทำเอง (manual) หรือไม่ก็ใช้โค้ด VBA

    ส่วนตัวอย่างการใช้งานของแต่ละฟังก์ชัน จะเขียนเพิ่มให้ครบในโพสต์ต่อ ๆ ไปครับ

    รายชื่อ

    ฟังก์ชันเริ่มมีใช้งานคำอธิบาย
    FILTER2020ใช้สำหรับกรองเอาเฉพาะข้อมูลจากชุดข้อมูลที่ตรงตามเงื่อนไขที่เรากำหนด
    SORT2020ใช้สำหรับจัดเรียงข้อมูลในรูปแบบที่ต้องการ ไม่ว่าจะเป็นเรียงจากน้อยไปมากหรือมากไปน้อย
    SORTBY2020จัดเรียงข้อมูล โดยอ้างอิงจากอีกคอลัมน์หนึ่ง
    SEQUENCE2020ใช้สร้างลำดับตัวเลขที่ต่อเนื่องกันในตาราง
    UNIQUE2020ใช้เพื่อดึงค่าที่ไม่ซ้ำกันจากช่วงข้อมูลที่กำหนด
    RANDARRAY2020สร้างอาร์เรย์ตัวเลขสุ่มภายในขอบเขตที่เรากำหนดให้
    XLOOKUP2020ใช้สำหรับค้นหาข้อมูลในตารางหรือช่วงข้อมูล โดยสามารถค้นหาได้ทั้งจากด้านบน-ลงล่างและล่าง-ขึ้นบน (⚠️ปกติจะไม่ให้ให้ dynamic array กลับมา แต่ทำได้บางกรณี)

    หวังว่าบทความนี้จะช่วยให้เข้าใจ dynamic array ได้มากขึ้นนะครับ 😊 ถึงแม้จะสั้นหน่อย แต่อยากให้เขียนเกี่ยวกับอะไรเพิ่มเติม ให้ comment ไว้ได้เลย 💗

  • การใช้ HSTACK และ VSTACK ใน Excel

    ภาพรวม

    ทั้ง HSTACK และ VSTACK ใช้รวมอาเรย์ (array) หรือรายการของข้อมูล โดยที่ VSTACK จะนำอาเรย์เหล่านั้นมาเรียงต่อกันในแนวตั้งจากบนลงล่าง ส่วน HSTACK เรียงต่อกันในแนวนอนจากซ้ายไปขวา

    HSTACK()

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

    อย่างเช่น ถ้าเรามีข้อมูลยอดขายแบ่งตามหมวดหมู่สินค้าดังภาพด้านล่างนี้

    ทีนี้เราอยากจะนำมาทำเป็นตาราง เพื่อที่จะดูว่าแต่ละหมวดหมู่สินค้าเหล่านั้นแต่ละเดือนขายได้เท่าไหร่ โดยสมมติว่าเราจะใส่หัวตารางใน F1 และเดือนก็เรียงไปตาม G1, H1 ไปเรื่อย ๆ โดยอันนี้เราใส่ข้อมูลเอง

    ส่วนใน F2 จะนำข้อมูลยอดขายของแต่ละหมวดของแต่ละเดือนมาใส่ (โดยข้อมูลตัวอย่างเราเรียงมาแล้วนะ) สูตรจะเป็นดังนี้

    =HSTACK(C2:C21,D2:D21,D22:D41,D42:D61)

    ซึ่งก็คือเราจะเอาชื่อหมวดหมู่มาจากคอลัมภ์ C และยอดขายของแต่ละเดือนมาจากคอลัมภ์ D ซึ่งในตัวอย่างเราทำมาแค่ 3 เดือน เพื่อให้เห็นภาพกาใช้งานได้ชัดหน่อย เพราะถ้าเอามาหมดมันจะดูยาว และเหมือนจะยากไปซะเปล่า 😁

    รูปแบบการใช้งาน HSTACK

    จากตัวอย่างที่ผ่านมาก็คือ ถ้าเราอยากได้ข้อมูลตรงไหนมาวางต่อ ๆ จากซ้ายไปขวา ก็คือไปเลือกช่วงข้อมูล (range) มาแล้วคั่นด้วยเครื่องหมายคอมม่า (comma) แค่นั้น

    ใช่ครับ มันใช้ง่าย ๆ แบบนี้ล่ะ โดย syntax ของ HSTACK คือ

    =HSTACK(array1,[array2],[array3], ... )

    โดย array1, array2, และ array3 ก็คือ Range ข้อมูลที่เราต้องการนำมาวางต่อกันไปในแนวนอน หรือจะเรียกว่าเป็นการเพิ่มคอลัมภ์ก็ได้

    สิ่งที่ควรทราบ

    array ใหม่ที่ได้จะมี row เท่ากับจำนวน row ที่มากที่สุด เช่นเดียวกันจำนวนคอลัมภ์ที่ได้ก็จะเท่ากับจำนวนคอลัมภ์ที่มากที่สุด โดยตรงไหนที่ไม่มีข้อมูล HSTACK จะแสดงผลเป็น #N/A

    ถ้าไม่อยากให้แสดงผลเป็น #N/A ให้ใช้ร่วมกับฟังก์ชัน IFNA() หรือ IFERROR() ก็ได้ อย่างเช่น

    =IFNA(HSTACK(C2:C21,D2:D21,D22:D40,D42:D60),"-")

    หากใช้สุตรด้านบนนี้ ส่วนที่เป็น #N/A จะถูกเปลี่ยนเป็น -

    สูตรแบบครบ 12 เดือน

    ในตัวอย่างด้านบนเราทำแค่ 3 เดือน และสำหรับข้อมูลเต็มไป 2023 สูตรที่เราใช้ใน F2 จะเป็น

    =IFNA(HSTACK(C2:C21,D2:D21,D22:D41,D42:D61,D62:D81,D82:D101,D102:D121,D122:D141,D142:D161,D162:D181,D182:D201,D202:D221,D222:D241),"-")

    ซึ่งอาจดู งงงง นิดหนึ่ง ดูไม่รู้ว่าเราเอาอะไรมาเรียงต่อกัน และถ้าไม่อยากให้สูตรเป็นแบบนี้ แนะนำให้ทำ Format as Table ในส่วนข้อมูลดิบไว้นะครับ เพราะจะทำให้สูตรเราดูเข้าใจง่ายขึ้นเยอะ

    โดยในกรณีนี้ เราสามารถใช้ฟังก์ชัน FILTER() ในการเลือกข้อมูลที่ตรงกับเงื่อนไขที่เราต้องการ แต่สูตรที่ได้จะดูยาวหน่อย แต่อ่านเข้าใจง่ายกว่า (หรือเปล่านะ) 😊

    =IFERROR(HSTACK(
 UNIQUE(tblPetShopSales[Category]),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=G$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=H$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=I$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=J$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=K$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=L$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=M$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=N$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=O$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=P$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=Q$2),""),
 FILTER(tblPetShopSales[Sales],(tblPetShopSales[Year]=$G$1)*(tblPetShopSales[Month]=R$2),"")
),"-")

    โดยใน G1 เป็นปี ค.ศ. และคอลัมภ์ G2 ถึง R2 จะเป็นชื่อเดือน January จนถึง December

    อ่านวิธีการเพิ่มเติมได้ ตามลิงค์นี้

    VSTACK()

    สำหรับ VSTACK มักใช้สำหรับรวมข้อมูลแบบเดียวกันจาก 2 แหล่งมารวมไว้ในคอลัมภ์เดียวกัน อย่างในภาพตัวอย่าง เรามีข้อมูลพนักงานอยู่ในตาราง 3 ตาราง แยกตามแผนก แต่เราอยากเอามารวมกัน เป็นต้น

    โดยในตัวอย่างสูตรของเราที่เซลล์ A1 จะเป็นดังนี้

    =VSTACK(tblSupplyChainManagement[#Headers],tblSupplyChainManagement,tblEngineeringMaintenance,tblMarketingSales)

    ซึ่งอันนี้ก็จะเป็นข้อดีของการทำ Format as Table แหละ เพราะถ้าเราใช้การระบุ range สูตรก็จะเป็น

    =VSTACK(HSTACK(I1:O1),I2:O4,Q2:W7,Y2:AE10)

    ถึงสูตรจะดูสั้นกว่า แต่ไม่สื่อความหมายว่าข้อมูลคืออะไรสักเท่าไหร่ อีกอย่างถ้าหากมีการเพิ่มข้อมูลเข้ามาก็ต้องคอยกลับมาแก้ไขสูตรอีก แต่ถ้าทำ Format as Table ข้อมูลในสูตรก็จะขยายเพิ่มมาให้โดยอัตโนมัติ

    จากสูตรข้างบน ส่วนหัวตารางเราจะมีการใช้ HSTACK มาช่วยด้วย

    รูปแบบการใช้งาน VSTACK

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

    =VSTACK(array1,[array2],[array3], ... )

    โดย array1, array2, และ array3 ก็คือ range ข้อมูลที่เราต้องการนำมาวางซ้อนกันเป็นหลายแถวขึ้น

    💡และก็เช่นเดียวกับ HSTACK ฟังก์ชัน VSTACK จะได้ array ใหม่ที่ขนาดเท่ากับคอลัมภ์และแถวที่มากที่สุดที่ระบุไว้ โดยหากไม่มีข้อมูลจะให้ #N/A มา

    ถ้าไม่อยากให้แสดงผลเป็น #N/A ให้ใช้ร่วมกับฟังก์ชัน IFNA() หรือ IFERROR() เช่นกัน

    ข้อสังเกตสำหรับการใช้ HSTACK และ VSTACK

    ทั้งสองฟังก์ชันนี้จะทำงานได้ดีกับ range หรือ array ที่มีขนาดเท่ากัน ทั้งที่อยู่ใน worksheet หรือผลลัพธ์ที่ได้มาจากสูตรอื่น (อย่าง FILTER ในตัวย่าง HSTACK) ผลลัพธ์จะเปลี่ยนแปลงไปตามข้อมูลต้นฉบับอัตโนมัติ

    โดยเฉพาะการทำงานกับข้อมูลที่อยู่ในแบบ table จะทำงานได้ดีเลย เพราะขอบเขตของ table จะขยายขึ้นอัตโนมัติจากข้อมูลที่ผู้ใช้ใส่เพิ่มเข้าไป

    ⚠️ เช่นเดียวกับ dynamic array function อื่น ๆ HSTACK และ VSTACK จะแสดง error เป็น #SPILL! หากมีข้อมูลอื่นอยู่ในเซลล์ที่จะใส่ข้อมูล

    และอีกอย่างคือ 2 ฟังก์ชันนี้อยู่ใน table ไม่ได้จะขึ้น #SPILL! เช่นกัน ถ้าทำไปแล้วก็ให้ไปที่ Table Design > Tools > Convert to range เพื่อแปลง table กับไปเป็น range เหมือนเดิม

    สำหรับไฟล์ตัวอย่างของโพสต์นี้ สามารถดูและดาวน์โหลดได้ใน ลิงค์นี้ ได้เลยครับ

    หากมีข้อสังสัย หรืออยากให้เพิ่มเติมตรงในสามารถไว้ใน comment ได้เลยนะ

  • วิธีใช้ฟังก์ชัน FILTER() ของ Excel

    ภาพรวม

    ฟังก์ชัน FILTER() ใช้ดึง (extract) เอาข้อมูลเฉพาะที่ตรงกับเงื่อนไขที่กำหนดกลับมาแบบไดนามิค (dynamic) หรือเปลี่ยนไปตามข้อมูลต้นฉบับอัตโนมัติ ดังนั้นถ้าข้อมูลหรือเงื่อนไขเปลี่ยนไป ฟังก์ชันนี้ก็จะให้ข้อมูลชุดใหม่ที่ตรงกับเงื่อนไขออกมา ซึ่งทำให้เรามีความยึดหยุ่นในการเรียกข้อมูลออกมาดูหรือใช้งานได้ โดยที่ไม่ต้องไปยุ่งกับต้นแหล่งข้อมูลฉบับเลย

    หากต้องการไฟล์ตัวอย่างไปทดลองดู เปิดดูและดาวน์โหลด ได้เลยครับ

    การใช้งานสูตร

    =FILTER(array,included_criteria,[if_empty])
    • array – ให้ใส่ range หรือ array ของข้อมูลที่เราต้องการจะกรอง หรือข้อมูลต้นฉบับนั่นแหละ
    • included_criteria – ระบุเงื่อนไขที่ต้องการจะกรอง
    • if_empty – หากไม่พบข้อมูลจะให้แสดงเป็นอะไร (ไม่จำเป็นต้องใส่)

    ตัวอย่างการใช้งาน

    สมมติเรามีข้อมูลดังตารางด้านล่างนี้

    ชื่อตำแหน่งแผนกเงินเดือน
    สมชายผู้จัดการการตลาด50,000
    อรอนงค์นักวิเคราะห์การเงิน40,000
    สมหญิงผู้ช่วยผู้จัดการการตลาด45,000
    สมศักดิ์วิศวกรวิศวกรรม35,000
    ปวีณาพนักงานขายการขาย30,000
    ธนพลนักวิเคราะห์การเงิน42,000
    วราภรณ์ผู้ช่วยผู้จัดการการเงิน38,000
    กิตติวิศวกรวิศวกรรม37,000
    จิราพรพนักงานขายการขาย32,000
    ปรีชาวิศวกรวิศวกรรม36,000

    และเราอยากรู้ว่ามีใครที่เงินเดือนมากกว่า 40,000 บาท สูตรที่ใช้จะเป็นดังนี้

    =FILTER(A2:D11,D2:D11>40000)

    ซึ่งเงื่อนไขต่าง ๆ ที่เราใส่ไปก็คือ เราระบุตัวดำเนินการเปรียบเทียบ (Comparison operators) ของ Excel ลงไปเลย ไม่ว่าจะเป็น

    Comparison operatorsคำอธิบาย
    >มากกว่า
    >=มากกว่าหรือเท่ากับ
    <น้อยกว่า
    <=น้อยกว่าหรือเท่ากับ
    <>ไม่เท่ากับ
    =เท่ากับ

    กรณีไม่พบข้อมูล

    หรือหากเราจะลองดูว่าไม่พบข้อมูลให้แสดงเป็น “Nothing matched!” โดยกำหนดเงื่อนไขเป็นคนที่เงินเดือนเกิน 50,000 ซึ่งในตัวอย่างไม่มี (มีแต่พอดีเป๊ะ)

    =FILTER(A2:D11,D2:D11>50000,"Nothing matched!")

    เงื่อนไขที่เป็นข้อความ

    สำหรับเงื่อนไขที่เป็นข้อความควรจะใช้ = หากต้องการให้ตรงกับข้อความที่กำหนด หรือไม่ก็ <> ก็คือเอาเฉพาะที่ไม่ตรงกับเงื่อนไข อย่างเช่น หากเราจะดูเฉพาะที่เป็น “ผู้จัดการ” สูตรจะเป็นดังนี้

    =FILTER(A2:D11,B2:B11="ผู้จัดการ","Nothing matched!")

    หรือหากดูเฉพาะที่ “ไม่ใช่” ผู้จัดการ สูตรก็จะเป็น

    =FILTER(A2:D11,B2:B11<>"ผู้จัดการ","Nothing matched!")

    จาก 2 ตัวอย่างที่ผ่านมาจะสังเกตเห็นว่า ถ้าเงื่อนไขที่เป็นข้อความ ข้อมูลกับเงื่อนไขต้องตรงกันแบบเป๊ะ ๆ เลยนะครับ อย่าง “ผู้ช่วยผู้จัดการ” ก็คือไม่ใช่ “ผู้จัดการ”

    หรือแม้แต่เว้นวรรค (space) ไม่เหมือนกันก็คือ ไม่เท่ากัน นะ 😁

    เงื่อนไขที่ตรงกับข้อความแค่บางส่วน (substring match)

    อาจมีบางครั้งเหมือนกัน ที่เราต้องการกำหนดเงื่อนไข แค่บางส่วน เช่น ทุกคนที่ชื่อตำแหน่งมีคำว่า “ผู้จัดการ” ⚠ แต่ว่า FIlTER() ไม่รองรับการใช้อักขระแทนอย่าง ? หรือ * ในเงื่อนไข

    ดังนั้นในการใช้งาน จึงต้องใช้ร่วมกับฟังก์ชัน SEARCH() หรือ FIND() และ ISNUMBER() ร่วมกัน อย่างเช่น

    =FILTER(A2:D11,ISNUMBER(SEARCH("ผู้จัดการ",B2:B11)),"Nothing matched!")

    หรือในทางกลับกัน หากเราอยากดูเฉพาะพนักงาน ไม่เอาคนที่ตำแหน่งมีคำว่า “ผู้จัดการ” เราก็จะใส่ฟังก์ชัน NOT() หน้า ISNUMBER() ไป ดังนี้

    =FILTER(A2:D11,NOT(ISNUMBER(SEARCH("ผู้จัดการ",B2:B11))),"Nothing matched!")

    💡หากใช้เงื่อนไขเป็นภาษาอังกฤษและต้องการแยกความแตกต่างระหว่างตัวพิมพ์เล็กกับตัวพิมพ์ใหญ่ให้ใช้ฟังก์ชัน FIND() แทนนะครับ

    กรองแบบหลายเงื่อนไข – และ (multiple critiria – AND)

    ในบางครั้งเราอาจจำเป็นต้องใช้หลายเงื่อนไขร่วมกัน เช่น พนักงานแผนกการขายที่มีเงินเดือนมากกว่า 30,000 สูตรก็จะเป็น

    =FILTER(A2:D11,(C2:C11="การขาย")*(D2:D11>30000),"Nothing matched!")

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

    โดยวิธีแบบนี้ข้อมูลที่ถูกกรองออกมาจะต้องตรงกับเงื่อนไขทั้งหมดที่ใส่ไป ซึ่งในตัวอย่างก็คือ คนที่อยู่แผนก การขาย และมีเงินเดือนมากกว่า 30,000 เท่านั้น

    กรองแบบหลายเงื่อนไข – หรือ (multiple critiria – OR)

    หรือในบางครั้งเราอาจต้องการข้อมูลที่ตรงกับบางเงื่อนไขเท่านั้น เช่น อยากดูพนักงานที่ตำแหน่งมีคำว่า ผู้จัดการ หรือมีเงินเดือนมากกว่า 40,000

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

    =FILTER(A2:D11,(ISNUMBER(SEARCH("ผู้จัดการ",B2:B11)))+(D2:D11>40000),"Nothing matched!")

    จากผลลัพธ์จะเห็นว่ามี 1 คน (วราภรณ์) ที่เงินเดือนน้อยกว่า 40,000 ด้วย นั่นก็เพราะตำแหน่งเขามีคำว่าผู้จัดการ ซึ่งตรงกับเงื่อนไขแรก ส่วนอีกคนหนึ่ง (ธนพล) ในตำแหน่งก็ไม่ได้มีคำว่าผู้จัดการ แต่ข้อมูลเขาไปตรงกับเงื่อนไขที่สอง

    ข้อควรทราบในการใช้งาน

    • ฟังก์ชัน FILTER() สามารถกรองข้อมูลจาก arrray หรือ range ทั้งแบบแนวตั้ง (vertical) และแนวนอน (horizontal) 💡
    • หากขนาด array ที่จะกรองกับเงื่อนไขใดเงื่อนไขหนึ่งไม่เท่ากัน ฟังก์ชันจะแสดงค่าเป็น #VALUE! ⚠️
    • หากเกิดข้อผิดพลาดในการคำนวณ (error) ในเงื่อนไข ฟังก์ชันก็จะแสดงค่าตาม error นั้น ๆ ⚠️
    • ถ้าในเซลล์ (cell) รอบ ๆ ที่เราใส่สูตรมีข้อมูลอยู่แล้ว ฟังก์ชันจะแสดงเป็น #SPILL! ⚠️ไม่ว่าจะเป็นแนวนอนหรือแนวตั้งก็ตาม

  • VBA คืออะไร?

    Visual Basic for Applications (VBA) เป็นภาษาสำหรับเขียนโปรแกรมหรือชุดคำสั่ง เพื่อให้ Microsoft Office ทำงานในแบบที่ผู้เขียนต้องการได้

    ถ้าให้ขยายความอีกที จะหมายความว่า “เราสามารถเขียนหรือบันทึกขั้นตอนการทำงานที่เราทำงานใน Microsoft Office ไว้ แล้วสั่งให้มันทำงาน ส่วนเราเป็นคนนั่งดูหรือเรียกว่า รอจะดีกว่า 😁” เพื่อประหยัดเวลาที่เราเสียไปกับการเลื่อนเมาส์หรือกดปุ่มบนคีย์บอร์ดลง

    โดยเฉพาะงาน copy and paste ข้อมูลเดิม ๆ เยอะ ๆ หลาย ๆ ไฟล์ ซึ่งทำให้เราเหนื่อย ทำผิดพลาดได้ง่าย นำไปสู่ความน่าเบื่อในที่สุด

    จากประสบการ์ส่วนตัว บางทีเป็นงานที่อาจกินเวลาในแต่ละวันของเราไปเกือบครึ่ง หรืออย่างน้อยก็ 1/3 จนบางทีทำให้บางคนถึงกับนิ้วล็อคไปแล้วด้วยซ้ำ

    หรือบางงานอาจจะเป็นการตรวจสอบข้อมูล หรือการจัดรูปแบบ แบบเดิม ๆ ซ้ำ ๆ

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

    แต่การรวมข้อมูลจากหลาย ๆ ไฟล์มาอยู่ไฟล์เดียวนี่ล่ะ มันเป็นสิ่งที่ผู้สร้าง Excel ซึ่งก็คือ Microsoft ไม่ได้เตรียมไว้ให้เรา เพราะแต่ละคน แต่ละงาน ต่างมีลักษณะข้อมูลที่แตกต่างกันไป

    ในบางที่มีข้อมูลเป็นฐานข้อมูลให้ใช้ ส่วนนี้จะสบายเลย เพราะเราจะได้ข้อมูลพร้อมให้ดึงมาประมวลผลเลย

    แต่สำหรับที่ที่ไม่ได้ทำข้อมูลเป็นฐานข้อมูลไว้ ยังไงก็หนีไม่พ้นการ copy and paste หรือไม่ก็ใช้มาโคร (Macro) หรือเขียน VBA code ที่เรากำลังพูดถึงนี่ล่ะ

    VBA กับ Macro ต่างกันอย่างไร?

    • VBA เป็นชื่อภาษาที่ใช้เขียนโปรแกรม
    • Macro หรือ มาโคร คือ กลุ่มของคำสั่ง ซึ่งเราเขียนขึ้นและตั้งชื่อไว้ เพื่อให้สามารถเรียกใช้งานภายหลังได้

    โดยใน Microsoft Office เราสร้างมาโครได้ 2 ทางคือ

    • กดบันทึกไว้ (Record)
    • เขียนขึ้นมาเอง (Write)

    แน่ว่าทั้งสองวิธี ผลลัพธ์จะได้โค้ด (code) ภาษา VBA ออกมา และเราจะเป็นคนกำหนดชื่อให้มัน พร้อมกับกำหนดวิธีการเรียกใช้

    ในภาษาพูด ทั้ง VBA และ Macro สองคำนี้ อาจหมายถึงสิ่งเดียวกัน หากใช้ Microsoft 365 อย่างไรก็ตาม คำว่า Macro ไม่ได้จำกัดแค่ภาษา VBA เท่านั้น เป็นคำทั่วไป

    จะใช้ VBA ได้อย่างไร?

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

    แต่เราสามารถดูได้จากเมนู View > Macros > View Macros

    อย่างไรก็ตามแอพจะคอยเตือนเราและไม่เปิดใช้งาน Macros หากเราไม่กด Enable

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

    เนื่องจากถ้าใช้ไม่เป็น แล้วกดไปเรื่อยอาจเกิด💥หายนะขึ้นได้เลย เช่น ข้อมูลในไฟล์หายไป โดยที่ Undo ไม่ได้ เพราะอะไรก็ตาม ที่ Macros ทำไปแล้ว จะ Undo ไม่ได้

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

    บันทึกไว้ใช้เอง โดยใช้ปุ่ม Record Macro…

    วิธีนี้เหมาะสำหรับงานที่ง่ายมาก ๆ อย่างเช่น กำหนดว่าคอลัมภ์ B ให้แสดงผลแบบเปอร์เซ็นต์ เป็นตัวหนา และสีดำเท่านั้น

    ซึ่งถ้าให้เราทำเอง เราจะต้องเลื่อนเมาส์และคลิก 5 ครั้ง แต่เราแค่อยากทำโดยกด Ctrl + p ครั้งเดียวเป็นต้น เพราะบางไฟล์ที่ใช้งานหลายคน อาจมีบางคนปรับเปลี่ยนรูปแบบไปเป็นอย่างอื่น ซึ่งเราสามารถทำได้ดังภาพ

    วิธีการบันทึุกมาโครไว้ใช้งาน
    โค้ด VBA ที่ได้จากการบันทึกด้านบน

    อย่างไรก็ตาม macros ที่เราบันทึกไว้ จะทำงานแบบ🥴ซื่อบื้อเลยนะ คือ เราทำไว้แบบไหนมันก็จะทำตามนั้นล่ะ

    เช่น ถ้าเราลบแถวที่ 2 และ 5 เพราะมันไม่มีข้อมูล คือเราดูแล้วล่ะ ว่าไม่มี ก็เลยลบ

    ครั้งต่อไปที่เราเรียกใช้งาน (run) มันก็จะลบแถวที่ 2 และ 5 🥲 ถึงแม้จะมีข้อมูลอยู่ก็ตาม

    เพราะการ record macro คือการบันทึกการกดเมาส์และคีย์บอร์ด ไม่ใช่ความคิดของเรา ✨ซึ่งประเด็นนี้สำคัญมาก✍️

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

    อีกอย่าง ⚠️หากเรากำหนด shortcut ให้ macros มันจะ overide หรือแทนที่ shortcut ของแอพโดยไม่ถามนะ เช่น Ctrl + p ปกติใช้สั่งพิมพ์ (แทนการเลื่อนเมาส์ไปกด File > Print)

    และสิ่งที่การบันทึกมาโครทำไม่ได้อีกอย่างคือ การ copy จากไฟล์หนึ่ง มา paste อีกไฟล์หนึ่ง หรือคนละไฟล์นั่นแหละครับ แม้จะเป็นไฟล์ Excel เหมือนกัน ก็ทำไม่ได้ น่าแปลกใจมาก 😒

    เขียนโค้ด VBA เอง

    เป็นทางเลือกสุดท้าย แต่คุ้มค่ามาก 😁 เพราะเป็นการใช้งานแบบที่เรากำหนดเอง

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

    หากเขียนมาโครเอง เราจะได้โค้ดออกมาประมาณนี้

    Private Sub Workbook_Open()
    Columns("B:B").Select
    With Selection
        .NumberFormat = "0.00%"
        .Font.Bold = True
        .Font.Color = vbBlack
        .Interior.Color = xlNone
    End With
    Range("A1").End(xlDown).Select
End Sub

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

    ซึ่งเราทำได้ตามภาพ โดย

    1. กดปุ่ม Alt + F11 แล้วเลือกที่ ThisWorkbook
    2. เลือกดรอบดาวน์ (Drop Down) เป็น Workbook
    3. เลือกดรอบดาวน์ (Drop Down) เป็น Open
    4. พิมพ์คำสั่ง (โค้ด) ที่เหลือ นอกจากที่ Excel เตรียมไว้ให้

    เพียงเท่านี้ ทุกครั้งที่เปิดไฟล์นี้ขึ้นมา คอลัมภ์ B แสดงผลเป็นเปอร์เซ็นต์ ตัวหนา สีดำ และไม่มีพื้นหลัง อย่างที่ต้องการ โดยไม่ต้องไปทำเอง หรือกดปุ่มเรียกคำสั่งที่บันทึกไว้

    ตัวอย่างถ้ามีคนเปลี่ยนรูปแบบดังนี้ แล้ว save ไว้ด้วย
    แต่เปิดมา แล้วกด Enable Content จะได้ผลดังนี้

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

    โดยถ้าจะให้อธิบายโค้ด ก็คือ

    Columns(“B:B”).Select เราเลือกคอลัมภ์ B ทั้งคอลัมภ์

    With Selection … End With ตรงนี้จะบอก Excel ว่า ที่เราเลือกไว้น่ะ ให้ทำ …

    .NumberFormat = “0.00%” แสดงจำนวนเป็นเปอร์เซ็นต์มีทศนิยม 2 หลัก
    .Font.Bold = True อักษรเป็นตัวหนา
    .Font.Color = vbBlack อักษรเป็นสีดำ
    .Interior.Color = xlNone ไม่ระบายสีเซลล์

    Range(“A1”).End(xlDown).Select เลือกแถวสุดท้ายที่มีข้อมูลของคอลัมภ์ A

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

    อย่างตอนเลือกเปอร์เซ็นต์ แล้วเพิ่มจุดทศนิยม จะได้โค้ดมา 2 บรรทัด ซึ่งถ้าเราสั่งเอง เราก็เลือกแค่แบบสุดท้ายก็พอ

    ก็หวังว่าโพสต์นี้จะเป็นประโยชน์สำหรับผู้ที่สงสัยว่า VBA คืออะไร แล้วใช้ยังไงเบื้องต้น

    ถ้าหากอยากให้เพิ่มเติมตรงไหน ก็ทิ้งคอมเม้นต์ไว้ด้านล่างได้เลยครับ 😁