excelavista

Category: Spreadsheet

  • ใช้ฟังก์ชัน FILTER กรองเอาเฉพาะตัวเลขในคอลัมภ์

    วันนี้เจอปัญหาหนึ่งในข้อมูลที่เราเก็บไว้ใช้เอง คือ ในคอลัมภ์นั้นมีทั้งตัวเลข ตัวอักษร และไม่มีข้อมูล แต่เราอยากได้เฉพาะตัวเลข ไม่เอาตัวอักษรและเซลล์ที่ไม่มีข้อมูล

    โดยปกติ Excel ทำได้โดยใช้ Autofilter ในตารางได้อยู่แล้วล่ะ

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

    และคิดว่าฟังก์ชัน FILTER คงทำได้แหละ และไปค้นเจอใน Google Groups นี้ และก็ตรงกับที่อยากใช้พอดี

    การใช้ฟังก์ชัน FILTER กรองเราเฉพาะตัวเลขในคอลัมภ์

    โดยฟังก์ชัน FILTER จะใช้กรองข้อมูลที่ตรงกับเงื่อนไขที่ระบุไว้จาก Range อื่น

    และเราจะใช้ร่วมกับอีก 3 ฟังก์ชัน (แต่ไม่ยากนะ 😁 อย่าพึ่งรีบหนีไปล่ะ) ก็คือฟังก์ชัน

    • VALUE() ซึ่งให้ตัวเลขกลับมา แม้ว่าในเซลล์นั้น ๆ จะเป็นตัวเลขที่เก็บในรูปแบบข้อความ (number stored as text)
    • ISERROR() ซึ่งบอกว่าสูตรที่เราใช้ error ไหม? ถ้ามีจะใช้ค่ากลับมาเป็น TRUE อย่างเช่น ISERROR(VALUE(“5”)) จะให้ค่ากลับมาเป็น FALSE ก็คือไม่ error เพราะค่าของ 5 เป็นจำนวน ไม่ใช่ข้อความ
    • NOT() จะให้ค่าตรรกะตรงกันข้าม จาก TRUE เป็น FALSE และจาก FALSE เป็น TRUE อย่างเช่น NOT(ISERROR(VALUE(“5”))) จะให้ค่ากลับมาเป็น TRUE

    ใครอยากดูไฟล์ตัวอย่าง คลิกตรงนี้และ File > Save as > Download a copy จาก OneDrive ได้เลยนะครับ

    จากภาพตัวอย่างข้อมูลจะอยู่ในคอลัมภ์ A ส่วนผลลัพธ์จะอยู่คอลัมภ์ C

    โดยสูตรที่ใช้คือ

    =FILTER(A2:A25,NOT(ISERROR(VALUE(A2:A25)))*(A2:A25<>""))
    • A2:A25 เป็น Range ที่มีข้อมููลทั้งหมด (ทั้งตัวเลข ข้อความ และไม่มีข้อมูล)
    • NOT(ISERROR(VALUE(A2:A25))) คือ บอกฟังก์ชัน FILTER ว่า เอาเฉพาะเซลล์ที่ทดสอบว่าเป็นตัวเลขแล้วไม่ error นะ
    • ส่วน *(A2:A25<>””) ก็คือ บอกฟังก์ชัน FILTER ว่าเซลล์ที่ไม่มีข้อมูลก็ไม่เอานะ ใครสงสัยส่วนนี้ อ่านวิธีใช้งานฟังก์ชัน FILTER เพิ่มได้

    ถ้าเราต้องการแค่ข้อความล่ะ ไม่เอาตัวเลข

    👍 ถูกต้องแล้วครับ เอาฟังก์ชัน NOT ออกไปซะ จบเลย

    โดยสูตรที่ใช้เหลือแค่นี้

    =FILTER(A2:A25,ISERROR(VALUE(A2:A25))*(A2:A25<>""))

    ก็คือ เราบอกฟังก์ชัน FILER ว่า เอาเฉพาะเซลล์ที่ทดสอบว่าเป็นตัวเลขแล้วเกิด error

    เอิ่ม! แล้วทำไมไม่ใช้ฟังก์ชัน UNIQUE ร่วมกับ COUNT ไปเลยล่ะ

    😒 บอกตรง ๆ เลยว่่า พึ่งนึกออกตอนเขียนโพสต์นี้ล่ะครับ 😶‍🌫️

    สำหรับคนที่ งง ว่าอะไรของมึ้ง…

    คือฟังก์ชัน COUNT มันนับเฉพาะตัวเลขอยู่แล้ว ส่วนฟังก์ชัน UNIQUE ก็จะกรองเอาเฉพาะข้อมูลที่ไม่ซ้ำมาให้

    ดังนั้นสิ่งที่ผมต้องการจริง ๆ ก็คือ ใช้สูตรแค่นี้ก็ได้

    =COUNT(UNIQUE(A2:A25))

    โอ้ย… นกเอี้ยงบินมาเกาะเพียบเลย ต้องไปนอนแล้วล่ะ 😂✌️

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

    =COUNTA(UNIQUE(FILTER(A2:A25,ISERROR(VALUE(A2:A25))*(A2:A25<>""))))

    ใช้สูตร =COUNTA(UNIQUE(A2:A25)) เลยไม่ได้ เพราะตัวเลขนับเป็นข้อความใน Excel ด้วย หากสงสัยลองดูในไฟล์ตัวอย่างนะครับ

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

    🥴 ปล. ผมนึกออกแล้วว่ากรณีงานของผม ทำไมต้องใช้ฟังก์ชัน FILTER ใช่แค่ COUNT ร่วมกับ UNIQUE ไม่ได้ผล เพราะผมมีเงื่อนไขเพิ่มเติมในอีก 2 คอลัมภ์ ซึ่งต้องไปใช้ร่วมกับฟังก์ชัน COUNTIFS อีกที

  • ข้อดีของการใช้ Format as Table ใน Excel

    ข้อดีของการใช้ Format as Table ใน Excel

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

    โพสต์นี้จึงอยากแนะนำข้อดีของการใช้ Format as Table ใน Excel ว่าจะช่วยให้เราทำงานได้ง่ายและเร็วขึ้นได้ยัง

    Table คืออะไร?

    สำหรับผมแล้ว table (ที่ไม่ใช้ data tables ในส่วนของการทำ what-if analysis) คือ การตั้งชื่อ range เพื่อให้เราสามารถเรียกใช้งานได้ง่ายขึ้นมาก ๆ โดยใช้ปุ่ม Format As Table ในแถบ Home หรือกด Ctrl + T

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

    1. ไม่ต้องเสียเวลาปรับสีและรูปแบบ

    เป็นข้อดึที่เห็นเป็นรูปอธรรมที่สุด ทำแล้วเห็นเลย เพียงแค่เลือกรูปแบบที่โดนใจจากรายการที่มีให้ หลังจากนั้นก็ไม่ต้องทำอะไรแล้ว โดยเฉพาะส่วนที่เรียกว่า banded row ซึ่งก็คือแถวคู่กับแถวคี่สีไม่เหมือนกัน ซึ่งถ้าคุณใช้ Format as Table นะ Excel จัดการให้เสร็จสรรพ

    และถ้าอยากเปลี่ยนรูปแบบก็แค่เลือกรูปแบบไหม่ ไม่ต้องไปปรับทีและแถวหรือทีละคอลัมภ์

    หรือถ้าไม่ชอบรูปแบบที่ Excel ทำมาให้ เราก็สามารถทำกำหนดเองและบันทึกไว้ใช้ได้อีกด้วย

    2. ใส่ Filter ให้โดยอัตโนมัติ

    คงไม่ต้องอธิบายเยอะ ก็คือ เราไม่ต้องไปคลิกเมาส์เพื่อใส่ Filter สำหรับกรองข้อมูลเอง หรือถ้าไม่อยากให้มีก็เลือกไม่ให้แสดงจาก contextual tab

    3. หัวตารางจะอยู่ให้เห็นเสมอ

    เมื่อข้อมูลในตารางของเราเพิ่มขึ้นเรื่อย ๆ และเราเลื่อนลงไปดูข้อมูลด้านล่าง ก็ต้อง Freeze แถวบน เพื่อที่จะให้เห็นหัวตาราง

    แต่ถ้าใช้ Format as Table ชื่อคอลัมภ์จะกลายเป็นหัวตารางไปเลย ดูในภาพด้านล่าง

    4. ตารางจะขยายออกโดยอัตโนมัติ

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

    แต่หาเราไม่ต้องการ เราก็สามารถ Undo ได้ ซึ่ง Excel จะหยุดขยายตารางออกมา แต่ข้อมูลที่พึ่งจะพิมพ์เข้าไปยังคงอยู่

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

    5. ตารางข้อมูลจะเป็น named range โดยอัตโนมัติ

    named range เป็นอีกฟังก์ชันหนึ่งที่ผมมักแนะนำให้คนใกล้ตัวใช้เสมอ โดยเฉพาะคนที่มีสูตร (formulas) ในไฟล์เยอะ ๆ เพราะเวลาอ่านมันเข้าในง่ายกว่าเยอะ เช่น

    =C5 + C5 * $B$3

    ถ้าเทียบกับ

    =[@Price] + [@Price] * VAT

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

    แต่สำหรับคนที่ยังไม่เคยใช้ตารางข้อมูลแบบนี้อาจจะงงงงหน่อย โดย [@Price] จะหมายถึงให้เอาข้อมูลในคอลัมภ์ชื่อ Price ในแถวเดียวกันมา

    โดยแนวทางการเรียกชื่อส่วนต่าง ๆ ใน table จะเป็นไปตามตารางนี้ และสมมติว่า table ของเราอยู่ที่ range A1:E202

    การเรียกข้อมูลในตารางคำอธิบายRange ที่จะอ้างถึง
    tblOrders[#All]อ้างถึงทุกส่วนของตารางข้อมูล ไม่ว่าจะเป็นหัวตาราง ข้อมูลในตาราง รวมไปถึงผลรวม หรือ total (ถ้ามี)$A$1:$E$202
    tblOrders[#Header]อ้างถึงเฉพาะแถวที่เป็นหัวตาราง หรือ Header row เท่านั้น$A$1:$E$1
    tblOrders[#Data]อ้างถึงเฉพาะส่วนข้อมูลของตาราง เท่านั้น$A$2:$E$201
    tblOrders[ColumnHeaderName]อ้างถึงข้อมูลในคอลัมภ์นั้น ๆ เท่านั้น เช่น tblOrders[TotalAmount]$E$2:$E$201
    tblOrders[#Totals]อ้างถึงเฉพาะแถวที่เป็นผลรวม ถ้ามีนะ ส่วนถ้าไม่มีก็จะได้ null หรือไม่มีค่าอะไรส่งกลับมา ก็คือไม่มีอะไรแสดงออกมานั่นล่ะ$A$202:$E$202

    ใส่ตัวกรอง การเรียงลำดับ และตัวช่วยค้นหา ให้แต่ละคอลัมภ์อัตโนมัติ

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

    เติมสูตรคำนวณในคอลัมภ์เดียวกันอัตโนมัติ

    อย่างเช่น หากผมอยากใส่สูตรที่จะบอกว่า order ในแต่ละแถวนั้นเกิดขึ้นเดือนไหน ต่อจากคอลัมภ์สุดท้ายของ table

    ผมแค่พิมพ์สูตรนี้

    =CHOOSE(MONTH([@TimeStamp]),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec","")

    ต่อจากข้อมูลในคอลัมภ์ F แถวไหนก็ได้ Excel จะเติมสูตรนี้ในแถวอื่นให้อัตโนมัติ ที่เหลือเราแค่ไปตั้งชื่อคอลัมภ์เอง เพราะปกติ Excel จะตั้งให้เป็น Column1

    เพิ่มข้อมูลในกราฟให้โดยอัตโนมัติ

    หากเรามีกราฟที่แสดงผลจากข้อมูลใน table
    เมื่อเราเติมข้อมูลเข้าไปใน table ข้อมูลที่ก็จะถูกแสดงผลเพิ่มในกราฟด้วย หรือ Dynamic Charts

    โดยสรุป

    การนำข้อมูลที่มีอยู่แปลงเป็น table (Format as Table) ช่วยให้เราประหยัดเวลาในการทำงานไปหลายอย่างมาก

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

    ดังนั้นควรใช้ table ร่วมกับ data validate ด้วย เพื่อป้องกันการป้อนข้อมูลและผลลัพธ์ที่ผิดพลาด

  • สูตรสุ่มตัวเลขแบบไม่ซ้ำใน Excel

    ⚠ ข้อควรระวัง – สูตรนี้ใช้ได้เฉพาะใน Excel 365 และ Excel 2021 ซึ่งสนับสนุนการใช้ dynamic array เท่านั้น

    โดยจะใช้ฟังก์ชัน SORTBY, SEQUENCE และ RANDARRAY ทำงานร่วมกัน โดยจะเริ่มจากง่าย ๆ ไม่ต้องใส่ตัวเลือกอะไรเยอะแยะไปก่อน

    =SORTBY(SEQUENCE(n),RANDARRAY(n))

    โดยที่ n คือ จำนวนสุ่มที่เราต้องการ และตัวอย่างในภาพคือ 10 ซึ่งจะเห็นว่าสูตรนี้เหมือนแค่เอาเลข 1 ถึง 10 มาเรียงสลับตำแหน่งกันเฉย ๆ

    ซึ่งบางท่านไม่ได้ต้องการแบบนี้

    การสุ่มจำนวนเต็มแบบไม่ซ้ำ

    คราวนี้หากเราใส่ตัวเลือกเพิ่มเติมเข้าไปในฟังก์ชัน SEQUENCE ดังนี้

    • n คือ จำนวนที่จะสุ่มเหมือนเดิม
    • start คือ เริ่มสุ่มจากเลขอะไร
    • step คือ ให้เพิ่มขึ้นทีละเท่าไหร่
    =SORTBY(SEQUENCE(n,,start,step),RANDARRAY(n))

    โดยตัวอย่างในภาพคือ เราต้องการสุ่ม 10 เลข เริ่มจาก 100 และเพิ่มขึ้นทีละ 3

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

    แต่หากไม่ต้องการแบบนี้ ลองดูอีกแบบด้านล่าง ซึ่งอาจมีความซับซ้อนกว่านิดหน่อย แต่ไม่มีการกำหนด step ไว้

    การสุ่มจำนวนเต็มแบบไม่ซ้ำ (แบบที่ 2)

    • n คือ จำนวนที่จะสุ่ม
    • start คือ เริ่มสุ่มจากเลขอะไร
    • stop คือ สุ่มถึงเลขอะไร
    =INDEX(UNIQUE(RANDARRAY(n^2, , start, stop, TRUE)), SEQUENCE(n))

    การสุ่มเลขมีทศนิยมแบบไม่ซ้ำ

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

    =INDEX(UNIQUE(RANDARRAY(n^2, , min, max, FALSE)), SEQUENCE(n))

    สำหรับ ไฟล์ตัวอย่าง นี้ อย่างลืมนะว่า สูตรที่เราใช้เป็น dynamic arrays ดังนั้นถ้าจะ copy ให้เลือกที่แถว 2 เท่านั้น


    หากเลือกที่แถวอื่น ❌ จะเห็นสูตรเป็นสีเทา ๆ 😁

  • สูตร Excel สำหรับแยกชื่อและนามสกุล ด้วยฟังก์ชัน TEXTBEFORE และ TEXTAFTER

    ผมเคยเขียนวิธีการแยกชื่อ-สกุลออกมากันไปแล้วตอนหนึ่ง โดยใช้ Text to Columns ในแถบ Data

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

    ซึ่งถ้าความต้องการเป็นแบบนั้น เราก็จะต้องให้สูตร หรือไม่ก็ฟังก์ชันแหละ โดยสำหรับผู้ใช้ Office 365 ตอนนี้ฟังก์ชัน TEXTBEFORE() กับ TEXTAFTER() น่าจะเปิดให้ใช้แล้ว

    สำหรับผู้ที่ไม่มี 2 ฟังก์ชันดังกล่าว ก็สามารถใช้ LEFT() และ RIGHT() รวมกับ FIND() และ LEN() ได้

    แต่ก่อนอื่นมาดูข้อมูลก่อน

    ตัวอย่างข้อมูลที่จะใช้ฟังก์ชัน TEXTBEFORE() และ TEXTAFTER() แยกชื่อ-สกุล

    จากภาพตารางข้อมูลด้านบน ช่องข้อมูลหลักของเราจะเป็นคอลัมภ์ A ซึ่งจะเป็นชื่อ-สกุล รวมอยู่ในเซลล์เดียวกัน แยกด้วย spacebar ส่วนคอลัมภ์ B จะเป็นชื่อที่เราจะใช้ TEXTBEFORE() แยกออกมา และคอลัมภ์ C จะใช้ TEXTAFTER()

    การใช้งาน TEXTBEFORE()

    โดยฟังก์ชันนี้ออกแบบมาเพื่อลดความซับซ้อนในการใช้ฟังก์ชัน LEFT() ร่วมกับ FIND() หรือ SEARCH() กับ LEN() จากสูตรเดิม ๆ ที่ใช้กันมา รวมไปถึงถ้าไม่พบกำหนดได้เลยว่า ให้แสดงข้อความว่าอะไร โดย syntax จะเป็นอย่างงี้

    =TEXTBEFORE(text,delimiter,[instance_num],[match_mode],[match_end],[if_not_found])
    • text – ก็คือข้อความ หรือเซลล์ที่เราจะดึงข้อมูลออกมา
    • delimiter – เป็นอักษร หรือข้อความที่เราจะบอกให้ฟังก์ชันหา และเอาข้อความที่อยู่ก่อนหน้านี้ออกมาให้เรา
    • instance_num [ไม่ต้องใส่ก็ได้] – แต่ถ้าใช้หมายถึงจะเลือกจากการเจอ delimiter ครั้งที่เท่าไหร่ ปกติค่านี้เป็น 1 แต่ถ้าตัวเลขติดลบ เช่น -1 ฟังก์ชันจะค้นจากด้านหลังย้อนกลับมา
    • match_mode [ไม่ต้องใส่ก็ได้] – ใช้กำหนดว่าเราต้องการแยกความแตกต่างระหว่างตัวเล็กกับตัวใหญ่หรือไม่ (ภาษาอังกฤษ) ปกติค่าเป็น 0 คือจะแยก a กับ A ว่าต่างกัน ถ้ากำหนดเป็น 1 จะไม่สนใจจะเป็น a หรือ A ฟังก์ชันจะหยุด
    • match_end [ไม่ต้องใส่ก็ได้] – ใส่เพิ่มให้ delimiter เป็นตัวอักษรสุดท้ายไหม ซึ่งในทางปฏิบัติคือ ถ้าไม่พบ delimiter ผลลัพธ์จะเป็นข้อความที่ใส่ไปนั่นเอง ปกติค่าจะเป็น 0 คือ ไม่ต้องทำ ถ้าไม่พบผลลัพธ์จะเป็น #N/A หรือข้อความที่เรากำหนดเองในอาร์กิวเมนต์สุดท้าย ส่วนถ้ากำหนดเป็น 1 ผลลัพธ์ก็คือ ค่าที่อยู่ในเซลล์ หรือข้อความที่เราใส่เข้าไปในอาร์กิวเมนต์แรก
    • if_not_found [ไม่ต้องใส่ก็ได้] – ใช้กำหนดข้อความที่จะให้แสดง หากไม่พบ delimiter ในค่าที่อยู่ในเซลล์ หรือข้อความที่เราใส่เข้าไปในอาร์กิวเมนต์แรก ปกติหากไม่พบผลลัพธ์จะเป็น #N/A ส่วนถ้าจะกำหนดก็คือ ใส่ข้อความของเราในเครื่องหมายคำพูด (double quote) เช่น “Not found” หรือ “ไม่พบ” เป็นต้น

    คราวนี้เราจะมาดูวิธีการใช้งานแบบง่าย ๆ แบบข้อมูลที่เราเตรียมไว้ ดังภาพ

    ตัวอย่างการใช้ฟังก์ชัน TEXTBEFORE() อย่างง่าย

    โดยสูตรที่เราใช้ใน B2 ก็คือ

    =TEXTBEFORE(A2," ")

    ก็จะเห็นว่า เราจะได้เฉพาะข้อความที่อยู่หน้า spacebar กลับมา

    การใช้งาน TEXTAFTER()

    =TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

    โดยจะสังเกตว่าอาร์กิวเมนต์ต่าง ๆ จะเหมือนกับ TEXTBEFORE() ทุกอย่าง แต่จะได้ผลลัพธ์ที่ตรงกันข้ามคือ ได้ข้อความที่อยู่หลัง delimiter กลับมา

    ตัวอย่างการใช้ฟังก์ชัน TEXTAFTER() อย่างง่าย

    โดยสูตรที่ใช้ใน C2 ก็คือ

    =TEXTAFTER(A2," ")

    และเราจะได้เฉพาะข้อความที่อยู่หลัง spacebar กลับมา

    การแยกชื่อด้วยฟังก์ชัน LEFT() ร่วมกับ FIND()

    สำหรับผู้ที่ไม่มีฟังก์ชัน TEXTBEFORE() ในช่อง B2 เราจะใช้สูตร

    =LEFT(A2,FIND(" ",A2)-1)

    โดยฟังก์ชัน LEFT() จะดึงตัวอักขระเริ่มจากทางซ้าย (ซึ่งบางทีผมก็เรียกว่า “ข้อความ” นะ 😅) ตามจำนวนที่เราใส่ไว้ในอาร์กิวเมนต์ที่ 2 ซึ่งในที่นี้เราใช้ฟังก์ชัน FIND() ซึ่งจะบอกว่าไปเจออักขระ หรือข้อความที่เราใส่ในอาร์กิวเมนต์แรก ว่าเจอเป็นลำดับที่เท่าไหร่

    และเหตุที่ต้องลบ 1 ออกจากค่าที่ FIND() ส่งกลับมา เพราะ FIND() จะส่งตำแหน่งที่เจอ (ในตัวอย่างนี้คือ spacebar) กลับมา อย่างใน B2 คือตำแหน่งที่ 4 แต่เราต้องการแค่ 3 อักขระ ก็คือ “ชยา”

    ส่วนหากใครไม่ได้ลบออก 1 อักขระที่เราไม่เห็นเพราะมันเป็น spacebar แต่ถ้าใช้ฟังก์ชัน LEN() ซึ่งจะบอกว่าข้อความนั้นมี 4 อักขระ

    ⚠️ฟังก์ชัน FIND() จะเป็นสนใจความแตกต่างระหว่างตัวเล็กกับตัวใหญ่ในภาษาอังกฤษนะ ถ้าเราไม่สนใจให้ใช้ฟังก์ชัน SEARCH() แทน

    การแยกนามสกุลด้วยฟังก์ชัน RIGHT() ร่วมกับ LEN() และ FIND()

    ส่วนการแยกนามสกุล เราจะใช้สูตรนี้

    =RIGHT(A2,LEN(A2)-FIND(" ",A2))

    โดยฟังก์ชัน RIGHT() จะดึงอักขระเริ่มจากด้านขวาของอาร์กิวเมนต์ที่เราใส่เข้าไป ตรงกันข้ามกับ LEFT()

    ส่วนอาร์กิวเมนต์ที่ 2 เราจะใช้ฟังก์ชัน LEN() ซึ่งจะบอกว่า A2 มีอักขระทั้งหมดกี่ตัว และในตัวอย่างคือ 13 อักขระ แล้วมาหักลบออกด้วยค่าที่ฟังก์ชัน FIND() ไปเจอ spacebar มา ซึ่งก็คือ 4 จะเหลือ 9 อักขระ ดังนั้นผลลัพธ์ก็เลยเป็น “ภัชรปรีดา”

    จะเห็นได้ชัดว่ากรณีแยกนามสกุลฟังก์ชัน TEXTAFTER() ช่วยทำให้การพิมพ์สูตรของเราดูง่ายขึ้นเยอะเลย

  • การทำตัวเลือก drop down แบบค้นหาได้

    การทำตัวเลือก drop down แบบค้นหาได้

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

    ตัวอย่างที่งานฝ่ายบุคคลพบบ่อยได้แก่ คนที่ชื่อ “นา” แผนก Operation นี่เขาชื่อจริงว่าอะไรนะ โดยเฉพาะคนที่ไม่ได้ส่งเมล์หาคนคนนั้นบ่อย ๆ

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

    ตัวอย่างคือ ถ้าเราพิมพ์คำว่า “นา” เข้าไป จะมีเฉพาะรายชื่อพนักงานที่มีคำนี้อยู่ในชื่อมาให้เลือก

    ก่อนอื่นข้อมูลตัวอย่างอยู่ในซีตชื่อ “data” โดยข้อมูลนี้ได้จากการสุ่มมาจาก https://kidhaina.com/thainamegenerator.html และเหมือนเช่นเคย (สำหรับผม 😁) ข้อมูลทำการ Format as Table แล้ว และตั้งชื่อเป็น tblData

    ข้อมูลพนักงานที่เราใช้นำมาทำตัวอย่าง

    มีอีก 2 คอลัมภ์ที่นำข้อมูลที่มีอยู่มาเชื่อมกัน โดยคอลัมภ์ i หรือ forlookup จะใช้แสดงผลลัพธ์เมื่อผู้ใช้เลือกชื้อใดชื่อหนึ่ง

    ส่วนคอลัมภ์ j หรือ forsearch ใช้เพื่อช่วยให้เราสามารถค้นหาได้ทั้งภาษาไทยและภาษาอังกฤษ ดังภาพ

    2 คอลัมภ์ที่นำข้อมูลที่มีอยู่มาเชื่อมกัน

    ฟังก์ชันที่ใช้กรองข้อมูล

    สำหรับการกรองข้อมูลที่มีอยู่เทียบกับข้อมูลที่ถูกพิมพ์เขามา เราจะใช้ฟังก์ชัน FILTER(), SEARCH() และ ISNUMBER() ทำงานร่วมกัน

    สำหรับตัวอย่างผมแยกชีต “Data” กับ “Search” ออกจากกัน โดยข้อมูลที่จะให้ผู้ใช้พิมพ์และเลือกตัวเลือกจะอยู่ในเซลล์ B2 ของชีตชื่อ “Search”

    ของเริ่มจากฟังก์ชัน SEARCH() ซึ่งใช้สำหรับค้นหาตัวอักษรหรือข้อความ โดยไม่สนความแตกต่างของตัวใหญ่หรือตัวเล็กในภาษาอังกฤษ โดยสูตรผมจะใส่ในเซลล์ L2 ตามนี้ก่อน

    =SEARCH(Search!$B$2,tblData[forsearch])

    ในที่นี้หมายถึงให้ค้นหาคำว่า “นา” ซึ่งถูกพิมพ์ไว้ในเซลล์ B2 ของชีตชื่อ Search โดยผลลัพธ์จะเป็นดังภาพ ซึ่งก็คือในแถว 6 เจอคำนี้อยู่ลำดับที่ 16 ส่วนแถวที่ 8 เจอที่ตัวแรก ไปเรื่อย ๆ

    แต่เราจะใช้เฉพาะแถวที่เป็นผลเป็นตัวเลข เราก็เลยต้องพึ่งฟังก์ชัน ISNUMBER() ซึ่งผลจะได้เป็นไม่ TRUE ก็จะเป็น FALSE ดังภาพ

    =ISNUMBER(SEARCH(Search!$B$2,tblData[forsearch]))

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

    =FILTER(tblData[ชื่อ-สกุล],ISNUMBER(SEARCH(Search!$B$2,tblData[forsearch])),"Not found")

    โดยอาร์กิวเมนต์ตัวแรกจะเป็นข้อมูลที่เราต้องการ ซึ่งในที่นี้คือคอลัมภ์ “ชื่อ-สกุล” เพราะต้องการแสดงข้อมูลจากคอลัมภ์นี้

    และเป็นสูตรที่เราใช้ก่อนหน้านี้ ⚠️ ซึ่งจะค้นหาจากคอลัมภ์ชื่อ “forsearch” เพราะเราต้องการค้นหาทั้งภาษาไทยและภาษาอังกฤษ

    สุดท้ายถ้าไม่พบให้ส่งค่า “Not found” กลับมา

    ผลลัพธ์หลังใช้ร่วมกับฟังก์ชัน FILTER()

    จะเห็นว่าเราจะใช้เฉพาะชื่อพนักงานที่มีคำว่า “นา” อยู่ด้วยกลับมา

    และหากเราต้องการเรียงข้อมูลด้วย เราสามารถใส่ฟังก์ช้น SORT() เพิ่มเข้าไปอีกชั้นหนึ่งได้ และสูตรก็จะกลายเป็น

    =SORT(FILTER(tblData[ชื่อ-สกุล],ISNUMBER(SEARCH(Search!$B$2,tblData[forsearch])),"Not found"))
    ผลลัพธ์หลังจากที่เราใช้ฟังก์ชัน SORT เพิ่มเข้าไป

    โดยฟังก์ชัน FILTER() จะให้ข้อมูลกลับมาเป็นไดนามิก อาร์เรย์ (dynamic array) ซึ่งผลลัพธ์จะส่งกลับมามากกว่า 1 ค่า และจะเรียงลงไปตามแถวเรื่อย ⚠️ ดังนั้นคอลัมภ์ L ไม่ควรจะมีอะไรนอกจากนี้ ไม่เช่นนั้นข้อมูลจะถูกวางทับไป แต่ถ้าเราจะแก้ไขอะไรก็แก้ไขแค่ L2 เท่านั้น

    เมื่อเสร็จจากการเตรียมรายการข้อมูลที่จะให้ผู้พิมพ์เลือกแล้ว เราจะกลับไปที่ชีต Search และเลือกเซลล์ B2 เพื่อทำ Data Validation (Data > Data Validation > Data Validation…)

    ที่ Allows: เลือกเป็น List และที่ Source: เลือกเป็นชีต Data เซลล์ L2 และพิมพ์เครื่องหมาย # เปิดท้าย 💡 เพื่อให้ Excel ดึงรายการด้านล่างมาดด้วย ดังภาพ

    =Data!$L$2#

    และ💥ที่ลืมไม่ได้คือ เราต้องยกเลิก Error Alert ของ Data Validation ของเซลล์นี้ด้วย ไม่เช่นนั้นเราจะพิมพ์ค่าอื่น นอกเหนือจากที่มีข้อมูลไม่ได้ และ Excel จะแสดง Error ให้ผู้ใช้ทราบ

    ซึ่งถ้าพิมพ์ข้อมูลที่ไม่พบ รายการจะแจ้งว่า “Not found” อยู่แล้ว

    คลิกที่แถบ Error Alert และคลิกที่เครื่องหมายถูกที่มีอยู่ให้หายไป ซึ่งจะได้ลักษณะดังภาพ

    เมื่อทำเสร็จ สมมติเราลองพิมพ์ “ya” เข้าไป เพื่อหาผู้ที่ในชื่อมีคำนี้ ก็จะได้ผลลัพธ์ดังภาพ

    ทดลองค้นด้วยภาษาอังกฤษ

    และหากเราเลือกเป็น “ชยา ภัชรปรีดา (คิด)” ในช่อง D2 ก็จะแสดงชื่อและแผนกของคน ๆ นั้นขึ้นมา ดังภาพด้านล่างนี้

    ใช้ XLOOKUP() ไปด้วยข้อมูลกลับมาแสดงผล

    เพียงเท่านี้ก็เรียบร้อยแล้วครับ หากติดขัดตรงไหนดูจากไฟล์ตัวอย่าง หรือ comment ไว้ด้านล่างได้นะครับ

  • การใส่เครื่องหมายคำพูดในสูตร Excel

    อาจมีบางครั้งเหมือนกันที่เราต้องการใช้ผลลัพธ์ใน Excel ที่เป็นเครื่องหมายคำพูด หรือเครื่องหมายอัญประกาศ หรือบางคนก็เรียกว่า “ฟันหนู” (มีหลายชื่อเหมือนกันแฮะ) โดยในภาษาอังกฤษก็คือ double quote (“) หรือเรียกเต็มคือ double quotation mark นั่นเอง

    และก็เป็นที่รู้กันอยู่แล้วว่า double quote ใช้สำหรับกำหนดช่วงของข้อความในสูตร Excel เช่น ถ้าเราใช้สูตร =A1&" นิ้ว" และในเซลล์ A1 มีค่าเท่ากับ 3 ผลลัพธ์ในเซลล์ที่เราใส่สูตรไว้จะแสดงเป็น 3 นิ้ว

    ตัวอย่างการเพิ่มข้อความ (string) ในสูตร Excel

    ดังนั้นถ้าเราใส่เครื่องหมายคำพูดไม่ครบคู่ในสูตร เซลล์นั้น ๆ ก็จะ error ดังตัวอย่างในภาพด้านล่างนี้ และหากทำได้ Excel จะแก้ไขให้ได้ สำหรับสูตรที่ไม่ซับซ้อน

    ตัวอย่าง error ที่เกิดขึ้นหากเราใส่เครื่องหมายคำพูดใน Excel ไม่ครบคู่

    จากตัวอย่างด้านบน หากเราตอบไว้ใช่ Excel จะแก้ไขให้ได้ โดยเพิ่มเครื่องหมายคำพูดขึ้นมาอีกตัว ซึ่งเป็นวิธีที่เรียกว่า ใส่ escape string ในสูตร ดังภาพด้านล่าง

    วิธีการใส่เครื่องหมายคำพูดในผลลัพธ์ที่ถูกต้อง

    หรือหากใครใช้สูตรที่ซับซ้อนเกรงว่าจะงง เราสามารถใช้ฟังก์ชัน CHAR() แทนการใช้ escape string ก็ได้ โดยรหัส ASCII ของเครื่องหมายคำพูดคือ 34 จากภาพด้านล่างสูตรในเซลล์ E2 จะเป็น =D2&CHAR(34) และจะได้ผลลัพธ์เป็น 6"

    การใช้ฟังก์ชัน CHAR เพื่อแสดงเครื่องหมายคำพูด

  • การตัดข้อมูลซ้ำออกใน Excel

    สำหรับใครที่เจอปัญหาที่จะต้องกรอง หรือตัดข้อมูลซ้ำออกบ่อย ๆ โดยเฉพาะข้อมูลที่มาจาก Microsoft Forms หรือ Google Forms ซึ่งตั้งแต่ก่อนจะต้องใช้ advance filter มาช่วยจัดการ แต่ค่อนข้างจะใช้หลายคลิกถึงจะได้เฉพาะข้อมูลที่ต้องการ รวมถึงหากข้อมูลเปลี่ยนไปข้อมูลที่กรองออกมาแล้ว จะไม่เปลี่ยนแปลงตาม (ไม่ dynamic update) ตอนนี้ใน Excel มีฟังก์ชันชื่อ UNIQUE มาช่วยแก้ไขปัญหานั้นแล้ว

    ฟังก์ชัน UNIQUE ใน Excel

    ⚠ แต่ต้องบอกไว้ก่อนว่า จะใช้ได้เฉพาะผู้ที่เป็นสมาชิก Office 365 หรือ Excel 2021 เป็นต้นไป โดยฟังก์ชันนี้จะกรองเอาเฉพาะข้อมูลที่ไม่ซ้ำกันมาให้เรา

    ตัวอย่างการใช้ฟังก์ชัน UNIQUE และการใช้ร่วมกับฟังก์ชัน SORT

    การใช้งานฟังก์ชัน UNIQUE

    วิธีใช้งานง่าย ๆ คือใช้สูตร =UNIQUE(A2:A40) ใน cell แรกที่เราต้องการจะใส่ข้อมูลที่ผลการกรอกข้อมูลจะออกมา

    ไวยากรณ์ (Syntax)

    =UNIQUE(array,[by_col],[exactly_once])
    • array ก็คือ range ข้อมูล หรือจะใส่ข้อมูลเป็น array ก็ได้ (หมายถึงฟังก์ชันที่ให้ค่ากลับมาเป็น array)
    • by_col 💬 ไม่จำเป็นต้องระบุ จะเป็น FALSE โดยปริยาย แต่ถ้ากำหนด TRUE จะกำหนดให้กรองข้อมูลโดยดูจากคอลัมภ์ (ใช้ในกรณีข้อมูลที่จะกรองมาในแบบคอลัมภ์) เพราะปกติฟังก์ชัน UNIQUE จะกรองในแบบแถว (row)
    • exactly_once 💬 ไม่จำเป็นต้องระบุ จะเป็น FALSE โดยปริยาย แต่ถ้ากำหนดเป็น TRUE

    ตัวอย่างการกรองข้อมูลซ้ำแบบคอลัมภ์

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

    และในแถวที่ 4 จะเป็นผลลัพธ์จากการตัดข้อมูลซ้ำออกไปแล้ว โดยข้อมูลซ้ำในแถวที่ 1 จะถูกไฮไลท์สีแดงไว้

    การกรองเอาเฉพาะข้อมููลที่พบเพียงครั้งเดียว

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

    ซึ่งจากในภาพจะเห็นว่า มีเพียง 2 คน แต่ถ้าเปิดจากไฟล์ตัวอย่างข้อมูลอาจแตกต่างไปจากนี้ ทั้งตัวอย่างใน sheet ที่ 2 และ 3 เพราะข้อมูลได้มาจากการสุ่ม

    หากลองใช้แล้วติดปัญหาอะไร ใส่ comment ไว้ได้เลยนะครับ 😄

  • การนับแบบหลายเงื่อนไขใน Excel

    การนับแบบหลายเงื่อนไขใน Excel

    ในบางครั้งเราก็ต้องการรู้จำนวนบางสิ่ง โดยที่มีหลายเกณฑ์ หรือหลายเงื่อนไข เช่น

    • อยากรู้ว่าพนักงานในฝ่ายวิศวกรรมมีเพศชายกี่คน เพศหญิงกี่คน หรือ
    • อยากรู้ว่า รถรุ่นนี้ สีแดงในภาคตะวันออกขายได้กี่คัน เป็นต้น

    เมื่อใช้ Excel เราจะใช้สูตรอะไร หรือฟังก์ชันไหนมาช่วยดี มาหาคำตอบกันในโพสต์นี้ครับ

    สรุปย่อ

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

    ดังนั้น ⚠️”ข้อมูลที่ตรงแค่บางเงื่อนไขจะไม่ถูกนับ” นะ

    ตัวอย่าง

    ขอยกตัวอย่างเป็นการนับพนักงานในแต่ละแผนกก็แล้วกันครับ โดยผลลัพธ์ที่ได้จะเป็นไปตามนี้

    ตัวอย่างผลลัพธ์จากฟังก์ชัน COUNTIFS()

    ซึ่งจะได้มาจากการนับข้อมูล ซึ่งผมกำหนดให้เป็นตาราง (Format as Table) ไว้แล้ว ดังภาพด้านล่าง หรือจะดูจากลิงค์ หรือดาวน์โหลด “ไฟล์ตัวอย่าง” มาลองใช้ดูก็ได้ 😁

    โดยตารางนี้จะชื่อ EmployeeTable และมีหัวตารางชื่อ Firstname, Lastname, Gender, Department และ Salary แต่หากใครสะดวกใช้ range แบบปกติก็ไม่มีปัญหาอะไรนะ ผมชอบทำแบบนี้เพราะมันอ่านง่ายและที่สำคัญคือ Excel จะช่วย suggest ให้โดยที่เราไม่ต้องพิมพ์ชื่อ range หรือ cell ในสูตร

    ส่วนจำนวนที่นับได้จะอยู่ในตารางชื่อ SummaryTable ดังภาพ

    จะเห็นว่าในเซลล์ I2 สูตรจะเป็น

    =COUNTIFS(EmployeeTable[Department],[@Department],EmployeeTable[Gender],"M")

    โดยมีเกณฑ์ที่เราใส่เข้าไปอยู่ 2 อย่าง คือ “ชื่อแผนก” กับ “M” หรือเพศชาย และผลลัพธ์ที่ได้ออกมาคือ 3 ก็คือจากข้อมูลในแผนก HR & Administration มีพนักงานผู้ชายอยู่ 3 คน

    ถึงตรงนี้หากใครยัง งงงง 🤔 อยู่ ไม่เป็นไรนะ เราไปดูไวยากรณ์ หรือวิธีการใช้ฟังก์ชันจะเข้าใจมากขึ้น

    ไวยากรณ์

    COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)

    มีอาร์กิวเมนต์ที่เราต้องบอกให้ฟังก์ชันทราบ ดังนี้

    • criteria_range1 [จำเป็นต้องมี] ให้กำหนด range ข้อมูลของเกณฑ์แรก ที่เราจะให้ฟังก์ชันไปหา
    • criteria1 [จำเป็นต้องมี] เกณฑ์ข้อมูลตัวแรก ที่เราต้องการจะนับ
    • criteria_range2 กำหนด range ข้อมูลของเกณฑ์ที่สอง ที่เราจะให้ฟังก์ชันไปหา
    • criteria2 เกณฑ์ข้อมูลที่ 2 ที่เราต้องการจะนับ
    • … หมายถึง คุณใส่คู่ของ range และเกณฑ์เพิ่มเข้าไปได้เรื่อย ๆ 😏 แต่จำไม่ได้ว่าเท่าไหร่

    ⚠️ข้อควรระวัง

    • สำคัญมาก❗criteria_range2 และอื่น ๆ ที่เพิ่มเข้าไป จะต้องมีแถวและคอลัมภ์เท่ากันกับ criteria_range1 เสมอ แต่ไม่จำเป็นต้องอยู่ติดกัน
    • อันนี้บอกไปแล้ว ต้องเขาทุกเกณฑ์ หรือทุกเงื่อนไขนะ ถึงจะนับเพิ่มให้
    • หากอาร์กิวเมนต์ criteria อ้างอิงไปยังเซลล์ที่ว่าง (ไม่มีข้อมูล) จะถือว่า อาร์กิวเมนต์นั้นคือ 0 (ศูนย์) ถ้าใน criteria_range มี 0 ถือว่าเข้าเงื่อนไขนะ และจะนับเพิ่มให้
    • สามารถใช้อักขระตัวแทน (wildcard) เครื่องหมายคําถาม (?) และดอกจัน (*) ในเกณฑ์ได้ โดยที่
      • ? หมายถึง “อักขระตัวเดียว”
      • และ ??? หมายถึง “อักขระ 3 ตัว”
      • ส่วน * หมายถึง “อักขระกี่ตัวก็ได้”
      • แล้วถ้าอยากใช้ ? และ * ในเกณฑ์ ให้ใช้เครื่องหมาย ~ นำหน้า เช่น ~? และ ~*

    อธิบายสูตรเพิ่มเติม

    สำหรับคนที่ไม่ถนัดใช้ตาราง (Table) สูตรที่ผมใช้ใน I2 ก็คือ

    =COUNTIFS($D$2:$D$36,G2,$C$2:$C$36,"M")
    • โดย criteria_range1 ของเราก็คือ range ที่ชื่อแผนกทั้งหมดของพนักงานแต่ละคน โดยในที่นี้คือ $D$2:$D$36 หรือ EmployeeTable[Department]
    • criteria1 ของเราคือ G2 หรือ [@Department] ซึ่งเป็นชื่อแผนกที่เรากำหนดเป็นเกณฑ์ ในที่นี้ก็คือ HR & Administration
    • criteria_range2 ก็จะเป็น range ที่เก็บเพศของพนักงานแต่ละคน ในที่นี้คือ $C$2:$C$36 หรือ EmployeeTable[Gender]
    • criteria2 เราใส่ “M” สำหรับผู้ชาย และ “F” สำหรับผู้หญิง เนื่องจากข้อมูลเราเป็นแบบนี้

    สำหรับใครมีหลายเงื่อนไขกว่านี้ ก็ให้ใส่เพิ่มเข้าไปเป็นคู่ range กับ เกณฑ์ ที่จะนับแบบนี้ไปเรื่อย ๆ

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

    =COUNTIFS(EmployeeTable[Salary],">50000",EmployeeTable[Department],"Production",EmployeeTable[Gender],"F")

    ฟังก์ชันที่เกี่ยวข้องและใช้งานเหมือนกัน

    นอกจาก COUNTIFS() แล้ว ในกลุ่มนี้ยังมีฟังก์ชันอื่นที่ใช้งานเหมือนกัน และมักผมมักถูกถามอยู่บ่อย ๆ ได้แก่

    • MINIFS() – หาค่าที่ต่ำที่สุด โดยที่ต้องเข้าทุกเกณฑ์ หรือทุกเงื่อนไข
    • MAXIFS() – หาค่าที่มากที่สุด โดยที่ต้องเข้าทุกเกณฑ์ หรือทุกเงื่อนไข
    • SUMIFS() – หาผลรวม โดยที่ต้องเข้าทุกเกณฑ์ หรือทุกเงื่อนไข
    • AVERAGEIFS() – หาค่าเฉลี่ย โดยที่ต้องเข้าทุกเกณฑ์ หรือทุกเงื่อนไข

    ส่วนการนับ หาค่าต่ำสุด หาค่าสูงสุด หาผลรวม และหาค่าเฉลี่ย แบบที่มีเกณฑ์เพียงตัวเดียว เราก็สามารถเลือกใช้ฟังก์ชัน COUTIF(), MINIF(), MAXIF(), SUMIF() และ AVERAGEIF() ตามลำดับได้เช่นกัน

    ถึงตรงนี้แล้ว หากใครมียังสงสัยก็ 📝comment ไว้ได้เลยนะครับ 😊

    และไฟล์ตัวอย่างสามารถดู หรือดาวน์โหลดได้จาก ➡️ OneDrive เช่นเคย

  • การทำตัวเลือกแบบ drop down แบบหลายชั้นใน Excel

    การทำตัวเลือกแบบ drop down แบบหลายชั้นใน Excel

    สรุปย่อ

    การทำตัวเลือกแบบ drop down หลายชั้น หรือตัวเลือกในเซลล์หนึ่งขึ้นอยู่กับข้อมูลที่เราเลือกในเซลล์อื่น เราจะใช้ data validation, named range, และ INDIRECT() ทำงานร่วมกัน

    ตัวอย่าง

    สมมติว่าเราต้องการให้ตัวเลือกในคอลัมภ์ A เป็นประเภทของสินค้า ซึ่งมี 3 อย่าง ก็คือ ผลไม้ เนื้อสัตว์ และพืชผัก

    และข้อมูลในคอลัมภ์ B จะขึ้นอยู่กับสิ่งที่เราเลือกในคอลัมภ์ A เช่น ถ้าคอลัมภ์ A เลือกเป็น “พืชผัก” ในคอลัมภ์ B ควรจะมีตัวเลือกเป็น ผักหัว ผักใบ ผักสลัด เป็นต้น ดังภาพ

    ขั้นตอนที่ 1 กำหนดตัวเลือกและกำหนดชื่อ (named range)

    จากภาพตัวอย่างด้านบน จะเห็นว่าผมใส่ข้อมูลที่จะใช้เป็นตัวเลือกไว้ในคอลัมภ์ E ถึง G โดยประเภทที่จะเลือกในคอลัมภ์ A จะอยู่ในแถวที่ 1 ส่วนตัวเลือกที่จะให้แสดงในคอลัมภ์ B จะอยู่ในแถวที่ 2 เป็นต้นไป

    ที่นี้การกำหนดชื่อเรียกใช้กับ cell หรือ range ให้เราไปที่แถบ (tab) Formulas > Name Manager แล้วเลือก New… แล้วจะมีหน้าต่างขึ้นมาให้เราใส่ข้อมูล ดังภาพ

    แล้วให้เรากำหนดชื่อที่เราต้องการในช่อง Name: พร้อมกับเลือก cell หรือ range ของข้อมูลในช่อง Refer to:

    โดยในตัวอย่างที่เราใช้ แต่ละช่องจะใส่ข้อมูลตามตารางนี้

    Name:Scope:Comment:Refers to:
    ประเภทWorkbook=Sheet1!$E$1:$G$1
    ผลไม้Workbook=Sheet1$E$2:$E$12
    เนื้อสัตว์Workbook=Sheet1$F$2:$F$10
    พืชผักWorkbook=Sheet1$G$2:$G$7

    📝 กฎการตั้งชื่อ named range หรือ Name Manager ของ Excel

    • ชื่อจะต้องขึ้นต้นด้วยตัวอักษร หรือ underscore ( _ ) หรือ backslash ( \ )
    • ในชื่อจะต้องไม่มีช่องว่าง (space) รวมถึงสัญลักษณ์พิเศษอื่น ๆ
    • อย่าตั้งชื่อเหมือนชื่อ cell หรือ range อย่างเช่น A1 Z100 หรือ XY22.
    • ใช้อักษรตัวเดียวก็ได้ แต่จะใช้ตัว r กับตัว c ไม่ได้ เพราะ Excel สงวนไว้ใช้เอง
    • ตัวอักษรเล็ก หรือใหญ่ ถือว่าเหมือนกัน (not case-sensitive) ดังนั้น VAT, Vat, vat, vAt จะถือว่าเป็นชื่อเดียวกัน

    ขั้นตอนที่ 2 กำหนด data validation ให้ range ที่เราต้องการใช้ drop down

    1. เลือก range หรือ cell (ซึ่งในภาพเลือกหลายเซลล์ หรือ range 😂 บอกเ.ฉย ๆ เผื่อเผลอลืม) ที่ต้องการจะตรวจสอบข้อมูลว่า ใส่ได้เฉพาะที่เข้าเงื่อนไขเท่านั้น หรือ data validation นั่นเอง
    2. ไปที่แถบ (tab) Data
    3. คลิกที่ไอคอนของ Data Validation
    1. ที่แถบ Settings ในช่อง Allow: ให้เลือกเป็น List
    2. ในช่อง Source: ให้พิมพ์ =ประเภท หรือชื่อที่คุณตั้งไว้ในขั้นตอนที่ 1 (หรือใส่ =Sheet1!$E$1:$G$1 ก็ได้ หากคุณไม่ได้ทำตามขั้นตอนที่ 1 มาก่อน แต่การทำแบบนี้หากคุณปรับ range ที่เก็บข้อมูล “ประเภท” คุณต้องมาไล่แก้ค่าที่ใส่นี้ในทุก cell คงไม่สะดวกนัก ถึงจะมีตัวเลือก ◽ Apply these changes to all other cells with the same settings แต่แนะนำให้ใช้ named range จะดีกว่า)

    พอเสร็จขั้นตอนนี้ หากคุณคลิกที่ A2 ถึง A10 จะเห็นว่าจะมีตัวเลือก หรือรายการให้เลือกแบบ drop down ขึ้นมาแล้ว อย่างในภาพ

    ขั้นตอนที่ 3 การทำตัวเลือกแบบ drop down ชั้นที่ 2 โดยใช้ฟังก์ชัน INDIRECT

    ซึ่งตัวเลือกในชั้นที่ 2 นี้จะขึ้นอยู่กับตัวเลือกที่เราเลือกไปก่อนหน้านี้ หรือในคอลัมภ์ A ของตัวอย่างนี้

    โดยเราก็จะใช้ data validation เหมือนในขั้นตอนที่ 2 แต่ในช่อง Source: เราจะใช้ฟังก์ชัน INDIRECT ไปดูชื่อ “ประเภท” ที่ถูกเลือกไว้ในคอลัมภ์ A ของแถวนั้น ๆ สำหรับตัวอย่างที่เราใช้จะมีขั้นตอนการทำดังนี้

    เลือก range ที่ต้องการ (ในตัวอย่างคือ B2:B10) แล้วไปที่แถบ Data > Data Validation

    1. ดูให้แน่ใจว่าในช่อง Allow: คุณเลือกเป็น List
    2. ในช่อง Source: ให้พิมพ์ =INDIRECT($A2) ❗ให้สังเกตนะ เรา fix คอลัมภ์อย่างเดียวนะ ไม่ได้ fix แถว

    เมื่อเสร็จขั้นตอนนี้แล้ว ลองเลือกตัวเลือกในคอลัมภ์ B ดู โดยให้สังเกตว่า จะมีเฉพาะตัวเลือกที่อยู่ในหมวดของคอลัมภ์ A เท่านั้น

    อย่างเช่นถ้าคอลัมภ์ A เป็น “พีชผัก” ตัวเลือกในคอลัมภ์ B ก็จะมีเฉพาะ ผักหัว ผักใบ ผักสลัด …

    จากตัวอย่างการทำตัวเลือกแบบ drop down 2 ชั้นนี้ คุณสามารถนำไปประยุกต์ใช้แบบหลาย ๆ ชั้นก็ได้ โดยการผ่านกำหนดข้อมูลและตั้งชื่อ แล้วมาใช้ Data Validation ร่วมกับฟังก์ชัน INDIRECT แบบที่เราทำใน 3 ขั้นตอนที่ผ่านมา

    ⚠️ข้อควรระวัง

    • ชื่อหมวดหมู่ที่จะใช้ ต้องเป็นไปตามกฎการตั้งชื่อ named range ของ Excel เท่านั้น ❗สำคัญมาก // หมายความว่า คุณจะตั้งชื่อตามใจตนเองได้ แต่ไม่มากนัก
    • ใน Range ที่จะกำหนด อย่าเลือกช่องว่างเผื่อไว้ สำหรับเพิ่มข้อมูลทีหลัง เพราะช่องว่างจะแสดงมาให้เลือกด้วย ตามจำนวนช่องว่างที่คุณเลือกไว้เลยด้วย (ตรงนี้ทาง Microsoft ทราบปัญหาอยู่แล้ว และกำลังให้กลุ่มผู้ใช้ Office Insider Program ทดลองใช้อยู่)
    • หากคุณพิมพ์ตัวเลือกซ้ำ Excel ก็จะแสดงข้อมูลนั้นซ้ำด้วย
    • ตัวเลือกจะเรียงตามลำดับที่คุณป้อนข้อมูลเข้าไป Excel จะไม่เรียงให้ หากต้องการข้อมูลเรียงตามลำดับอักษร ให้จัดเรียงใน range ที่กำหนดไว้

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

    สำหรับไฟล์ตัวอย่างดาวน์โหลดได้นี่ ➡️ ตัวอย่างการทำตัวเลือกแบบ drop down หลายชั้นใน Excel

    🎉 ในไฟล์มีตัวอย่างการทำ drop down ขั้นที่ 3 ด้วยนะ ✨

    อ่านแล้ว ถ้าติดปัญหาตรงไหน ก็ใส่ comment ไว้ได้เลยครับ 😊

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

    สรุป

    ฟังก์ชัน LAMBDA ใช้สำหรับ สร้างฟังก์ชันใหม่ เพิ่มเข้าไปในไฟล์ Excel (workbook) โดยไม่ต้องเขียน Macro(s) หรือ VBA code 😊👍 เพิ่มเข้าไปในไฟล์ ซึ่งจะช่วยให้เราไม่ต้องพิมพ์ หรือ copy-paste สูตรที่เขียนขึ้นซ้ำแล้วซ้ำอีก รวมทั้งฟังก์ชันที่สร้างจาก LAMBDA() นี้ จะสามารถเรียกใช้ตลอดในไฟล์ Excel นั้น ๆ เสมือนเป็นหนึ่งในฟังก์ชันที่มีมากับ Excel

    ข้อจำกัดในการใช้งาน!

    ตอนนี้ฟังก์ชัน LAMBDA อยู่ในช่วงทดลองใช้งานในกลุ่ม Office Insiders อยู่ ดังนั้นสำหรับผู้ใช้งานทั่วไป จะยังไม่เห็นฟังก์ชันนี้ เปิดให้ใช้งานทั่วไปแล้ว (ข้อมูล ณ วันที่ 25 เม.ย. 2566)

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

    =LAMBDA([parameter1,parameter2,...],calculation)

    อาร์กิวเมนต์ (Arguments)

    อาร์กิวเมนต์คำอธิบายจำเป็นต้องมี?
    parameterXใช้กำหนดค่า หรือตัวแปรต้นที่จะใช้ประกอบการคำนวณ
    แต่จะมีได้ไม่เกิน 253 ค่า โดยจะเป็นตัวเลข (number) หรือข้อความ (text) ก็ได้    		ไม่
    calculationเป็นสูตร หรือสมการที่เราจะใช้ในการคำนวฯ ซึ่งผลจากการคำนวณนี้จะเป็นผลลัพธ์ของฟังก์ชัน LAMDA ที่เราสร้างขึ้นจำเป็น

    ข้อควรระวังในการใช้งาน

    • ถ้าใส่ parameter เกิน 253 ตัว จะแสดง error เป็น #VALUE!
    • ถ้าเรียงลำดับอาร์กิวเมนต์ผิด จะแสดง error เป็น #VALUE! หรือไม่ก็จะเป็นอัลกอริทึม error ไปเลย ซึ่งตรงนี้ Excel จะไม่รู้กับเรานะ 😁 แต่ผลการคำนวณจะไม่ตรงกับสิ่งที่เราอยากได้
    • ถ้าเราเรียกใช้ฟังก์ชัน LAMBDA() นั้น ๆ ในเซลล์เดียวซ้อนกันในตัวมันเอง จะแสดง error เป็น #NUM!
    • ถ้าคุณสร้างฟังก์ชัน LAMBDA() ในเซลล์ แต่ไม่ได้เรียกใช้มัน จะแสดง error เป็น #CALC!
    • ชื่อ parameter ห้ามมีจุด (.) และให้ตั้งชื่อตามกฎการตั้งชื่อของ Excel
    • ตรวจสอบผลลัพธ์ที่ได้จาก LAMBDA() ว่าตรงตามที่ต้องการทุกกรณี โดยเฉพาะการส่งผ่านอาร์กิวเมนต์ เพราะ LAMBDA() จะใช้คำสั่ง Evaluate ใน Excel ช่วยตรวจสอบไม่ได้

    การสร้างฟังก์ชัน LAMBDA

    ขั้นตอนที่ 1 กำหนดสูตร หรือสมการที่เราจะใช้

    อย่างที่บอกไว้ในข้อควรระวัง การใช้ LAMBDA() ผู้สร้างจะต้องมั่นใจว่า สูตร หรือสมการที่เขียนขึ้นมานั้น ให้ผลลัพธ์ที่ถูกต้อง ตามกฎเกณฑ์ที่จะนำไปใช้ เพราะไม่เช่นอาจได้ผลลัพธ์จากการคำนวณที่ผิดพลาดได้

    สมมติว่า เราจะสร้างใช้ LAMBDA() ในการแปลงหน่วยออนซ์ (ounce) ไปเป็นมิลลิลิตร (milliliter) โดยหลักการแล้ว 1 oz จะเท่ากับ 29.57353 mL ดังนั้นสูตรที่ใช้แปลงจากออนซ์ไปเป็นมิลลิลิตร คือ oz * 29.57353

    และผลลัพธ์ของการคำนวณควรออกมาตามตารางนี้

    Ounce(s)Milliliter(s)สูตรคำนวณปกติที่ใช้ใน Excel ของคอลัมภ์ B
    00.00=A6*29.57353
    129.57=A7*29.57353
    259.15=A8*29.57353
    388.72=A9*29.57353
    4118.29=A10*29.57353
    5147.87=A11*29.57353
    ตารางแปลงหน่วยออนซ์ให้เป็นมิลลิลิตร โดยหน่วยมิลลิลิตรถูกปัดให้เหลือทศนิยม 2 ตำแหน่ง

    อ้างอิงจาก – https://www.metric-conversions.org/th/volume/us-ounces-to-milliliters.htm

    และเราจะลองคำนวณใน Excel จะเป็นไปตามรูปด้านล่างนี้

    ขั้นตอนที่ 2 ทดสอบสูตร หรือสมการของ LAMBDA()

    ขั้นตอนนี้ใช้ยืนยันผลการคำนวณจาก LAMBDA() ว่าตรงตามสูตรคำนวณปกติไหม โดยผลลัพธ์จะอยู่ในคอลัมภ์ B และสูตรที่ใช้ในคอลัมภ์ B จะอยู่ในคอลัมภ์ C ดังภาพด้านล่างนี้

    จากภาพก็จะเห็นว่า ผลลัพธ์ที่ได้จากการคำนวณจะเท่ากันกับสูตรในขั้นตอนแรกเลย แต่เราจะไม่ใช้ LAMBDA() แบบนี้หรอกนะ เพราะไม่งั้นมันจะไม่ต่างจากการเขียนสูตร (formula) ทั่วไปใน Excel

    ขั้นตอนที่ 3 การกำหนดชื่อให้ฟังก์ชัน LAMBDA ที่เราสร้างขึ้น

    ซึ่งเป็นจุดประสงค์ของการมีฟังก์ชัน LAMBDA คือ ต้องการให้เรียกใช้งานมันได้ง่าย เหมือนฟังก์ชันอื่นใน Excel เพียงแต่มันจะมีขอบเขตใช้งานภายในไฟล์ Excel นั้น ๆ เท่านั้น ไม่เหมือนฟังก์ชัน bulit-in ของ Excel ที่เรียกใช้งานได้จากทุกไฟล์

    และการกำหนดชื่อนี้จะช่วยให้เราเรียกใช้ฟังก์ชัน LAMBDA ที่เราสร้างขึ้นได้ง่าย ไม่ต้องพิมพ์ หรือ copy-paste สูตรซ้ำ ๆ

    โดยมีขั้นตอนดังนี้

    1. ไปที่แถบ (tab) Formulas > Name Manager (สูตร > ตัวจัดการชื่อ) แต่ถ้าใช้ macOS จะใช้ Formulas > Define Name
    2. ใส่ข้อมูลในช่อง (fields) ต่าง ๆ โดย
      • Name: พิมพ์ชื่อทีเราจะตั้งให้กับ LAMBDA ที่เราจะสร้างนี้ โดยมักเป็นชื่อที่สื่อความหมายของสิ่งที่ฟังก์ชันทำ
      • Scope: ปกติจะเลือกเป็น Workbook ตามที่ Excel เลือกมา ยกเว้นคุณอยากจะเปลี่ยนให้เห็นเฉพาะใน Worksheet เท่านั้น
      • Comment: ถึงจะไม่จำเป็นต้องใส่ แต่แนะนำให้ใส่เป็นอย่างยิ่ง เพราะเมื่อเวลาผ่านไป เราจะได้รู้ว่าชื่อที่เรากำหนดไว้นี้ใช้ทำอะไร มีที่มาที่ไปอย่างไร
      • Refer to: ใส่สูตรของ LAMBDA() ที่เราจะใช้ (ถ้าไม่อยากให้ Excel ใช้ automatic cell reference ให้กด F2)
    Dialog สำหรับกำหนดชื่อให้ LAMBDA() หรือชื่ออื่น ๆ ที่ใช้ใน Excel

    จากภาพข้อบนจะเห็นว่า ผมตั้งชื่อของฟังก์ชัน LAMBDA นี้ว่า OzTomL และสูตรในช่อง Refer to: เป็น =LAMBDA(Oz,Oz*29.57353) และมีการใส่ Comment แหละ แต่ Dialog จะเป็นภาษาไทย 😂 บางท่านอาจไม่ชอบ

    และเมื่อจะเรียกใช้ เราก็สามารถพิมพ์ชื่อฟังก์ชันที่กำหนดไว้เข้าไปได้เลย ดังภาพ

    Excel จะเสนอชื่อฟังก์ชัน LAMBDA ที่เรากำหนดขึ้นเสมือนเป็นหนึ่งใน built-in function ของ Excel เลย

    ตัวอย่างข้างบน อาจเป็นตัวอย่างการเขียน comment ที่ไม่ดีนัก 😅 เพราะควรจะบอกวิธีการเรียกใช้ โดยเฉพาะลำดับอาร์กิวเมนต์ที่จะต้องส่งเข้าไปในฟังก์ชัน ลองนึกถึงภาพตอนที่ Excel เสนอฟังก์ชันที่ built-in มา

    เมื่อคลิกเลือกฟังก์ชัน จะบอกว่าต้องใส่พารามีเตอร์อะไรเข้าไป ซึ่งในที่นี้ก็คือ Oz

    และสุดท้ายจะเป็นตัวอย่างตารางที่เราเรียกใช้ LAMBDA() ที่สร้างขึ้นจริง ๆ

    การเรียกใช้งานฟังก์ชัน LAMBDA ที่เรากำหนดชื่อไว้

    เป็นยังไงกันบ้าง พอเห็นภาพการใช้งานฟังก์ชัน LAMBDA ที่กำลังจะเปิดให้ใช้งานกันไหม หรือนึกออกยังว่าจะเอาไปประยุกต์ใช้งานยังไงดี

    สำหรับตัวอย่างคลิกดาวน์โหลดได้ตรงนี้ ➡️ ไฟล์ตัวอย่างการใช้งานฟังก์ชัน LAMBDA ใน Excel

    มีความคิดเห็น หรือสงสัยอะไรก็ทิ้ง comment ไว้ได้นะครับ