Tag: IF

  • IF vs IFS vs SWITCH vs LET ใครคิดเร็ว ใครคิดช้า? มาเรียนรู้วิธีทำให้สูตร Excel เร็วขึ้น

    IF vs IFS vs SWITCH vs LET ใครคิดเร็ว ใครคิดช้า? มาเรียนรู้วิธีทำให้สูตร Excel เร็วขึ้น

    บทความนี้จะพาคุณลึกเข้าไปใน “Performance” ของฟังก์ชันตรวจสอบเงื่อนไขใน Excel
    ตั้งแต่ IF() พื้นฐาน ไปจนถึง IFS(), SWITCH(), LET() และเทคนิคซ่อนสูตรด้วย LAMBDA() เพื่อเลื่อนการคำนวณ จับเวลาด้วย LET() + NOW() แล้วแสดงผลเป็นมิลลิวินาที (ms) ให้เห็นกันชัด ๆ


    🌟 1. ทำไมต้องสนใจ Performance ของสูตร?

    เมื่อสูตร Excel ซับซ้อนขึ้น หรือมีการคำนวณกับ dataset ขนาดใหญ่ ความเร็วในการคำนวณ (calculation speed) จะกลายเป็นปัจจัยสำคัญ

    • ถ้าเราซ้อน VLOOKUP/XLOOKUP/MAKEARRAY/FILTER หลายครั้งในเงื่อนไขที่ไม่จำเป็น → Excel จะ “คิด” ทุกเงื่อนไขซ้ำนับร้อยครั้ง
    • สูตรที่ช้าไม่ใช่แค่รอผลนาน แต่ยังกินทรัพยากรเครื่อง และอาจทำให้ workbook ทั่วทั้งไฟล์อืดตาม

    บทความนี้อ้างอิงแนวคิดจาก

    The SWITCH and LET functions – Excel formula performance
    by Owen Price (Microsoft MVP)
    ซึ่งลงลึกเรื่องว่า IF() กับ CHOOSE() ให้การประมวลผลแบบ lazy evaluation (short‑circuit) แต่ IFS()/SWITCH()/LET() จะคำนวณ ทุก พารามิเตอร์โดยไม่เลือก


    🔧 2. วิธีจับเวลาใน Excel แบบง่าย ๆ ด้วย LET() + NOW()

    Excel ไม่มีฟังก์ชัน Timer เหมือนภาษาโปรแกรม แต่เราสามารถวัดเวลาคร่าว ๆ ได้ด้วยสูตรนี้ (เพราะใน LET จะไล่คำนวณจากบนลงล่าง แม้จะไม่ได้เรียกใช้ตัวแปรนั้นๆ เลย) :

    =LET(
      start,     NOW(),           // เก็บเวลาเริ่มต้น (หน่วยวัน)
      result,    <สูตรที่อยากวัด>,   // สูตรหนัก ๆ เช่น MAKEARRAY
      elapsed,   NOW() - start,   // ผลต่าง (หน่วยวัน)
      elapsedMs, elapsed * 24 * 60 * 60 * 1000,  // แปลงมิลลิวินาที
      elapsedMs                   // คืนค่าเวลา (ms)
    )

    หมายเหตุ:

    • ค่าที่ได้เป็นการประมาณ ไม่แม่นยำระดับ millisecond เป๊ะ ๆ แต่ใช้เปรียบเทียบระหว่างสูตรได้ดีและง่าย โดยไม่ต้องใช้ VBA จับเวลา

    🧪 3. ชุดทดสอบ: MAKEARRAY(2000,2000,PRODUCT)

    เพื่อให้เห็นภาพชัดที่สุด เราใช้สูตรสร้าง array ขนาดใหญ่ เบิ้มๆ ไปเลย:

    =MAKEARRAY(2000, 2000, PRODUCT)

    เป็นการคูณเลขแถว × เลขคอลัมน์ จำนวน 4 ล้านเซลล์ → Excel ต้องคำนวณนานหลายร้อยมิลลิวินาที

    จากนั้นเอาสูตรนี้ไปใส่ใน IF(), IFS(), SWITCH(), LET() และ LET+LAMBDA() แล้วดู ค่าเวลา (ms) จริงที่ได้

    IF vs IFS vs SWITCH vs LET ใครคิดเร็ว ใครคิดช้า? มาเรียนรู้วิธีทำให้สูตร Excel เร็วขึ้น 1
    สูตรพฤติกรรมคำนวณผลลัพธ์ (ms)สรุป
    MAKEARRAYคำนวณตรง ๆ~400ความเร็วที่ต้องใช้สูตร
    IF(TRUE,…)ถ้าเงื่อนไขเป็น TRUE → ไม่ประมวล MAKEARRAY~0✅ Lazy & เร็ว
    IFS(TRUE,…, FALSE, MAKEARRAY)ประมวลทุกเงื่อนไข แม้ไม่จำเป็น~420❌ ช้า
    SWITCH(1,1,…,2,MAKEARRAY)ประมวลทุกทางเลือก~420❌ ช้า
    LET(slow,MAKEARRAY, result)ประกาศตัวแปรก็เรียกคำนวณทันที แม้ไม่ได้ใช้~420❌ ช้า
    LET(thunk, LAMBDA(MAKEARRAY), result)เก็บสูตรไว้ใน LAMBDA ไม่ประมวลจนกว่าจะเรียก ()~0✅ Lazy & เร็ว

    🧠 4. เจาะลึกแต่ละฟังก์ชัน

    4.1 IF() – ขี้เกียจแบบฉลาด ทำให้คำนวณเร็ว 😂

    =IF(condition, value_if_true, value_if_false)
    =IF(TRUE, "เร็ว", MAKEARRAY(1000,1000,PRODUCT))
    • Short‑circuit (lazy): ถ้า condition = TRUE → Excel จะไม่ไปคำนวณ value_if_false เลย
    • เช่น ในทีนี้จะไม่คำนวณตรง MAKEARRAY(1000,1000,PRODUCT) เลย
    • เหมาะกับกรณีที่เงื่อนไขหลักมีโอกาสเป็นจริงสูงมาก

    Tips : พวก IFNA, IFERROR ก็คำนวณเร็วเช่นกันนะ

    4.2 IFS() – อ่านง่าย เขียนง่าย แต่ขยันเกิน ทำให้ช้า

    =IFS(cond1, val1, cond2, val2, …)
    =IFS(
        TRUE, "เร็ว",
        FALSE, MAKEARRAY(2000,2000,PRODUCT)
      )
    • ไม่ short‑circuit: Excel จะประมวล ทุกเงื่อนไข condN, valN แม้จะเจอเงื่อนไขแรกเป็นจริงแล้วก็ตาม (ทำเพื่อ! 😅)
    • เช่น ในที่นี้แม้ Condition แรกจะจริงไปแล้ว แต่ก็มาคิด Condition ที่สองต่อ ทำให้ยังคำนวณ MAKEARRAY(2000,2000,PRODUCT) ช้าๆ ออกมาอยู่ดี
    • ถ้าใช้ภายใน IFS() สูตรหนัก ๆ หลายตัว → ช้ากว่าซ้ำนับครั้งไม่ถ้วน

    4.3 SWITCH() – อ่านเปรียบเทียบค่าคงที่ได้ดี แต่ขยันเกินจนช้า

    =SWITCH(expression, val1, res1, val2, res2, …)=SWITCH(1,
        1, "เร็ว",
        2, MAKEARRAY(2000,2000,PRODUCT)
      )
    
    • ไม่ short‑circuit: Excel จะคำนวณทุกเงื่อนไข แม้ expression จะตรงกับ val1 ตั้งแต่แรกแล้ว
    • เช่น ในทีนี้ แม้ expression จะเป็น 1 ซึ่งตรงอันแรกแล้ว มันก็ยังคิด 2 ที่เป็น MAKEARRAY ช้าๆ อยู่ดี
    • อ่านง่ายกว่าซ้อน IF/IFS แต่หากมีสูตรหนักก็ไม่ work

    Tips: ใช้ CHOOSE จะ Short-circuit ทำให้เร็วได้ เช่น

    =CHOOSE(3, "A", "B", MAKEARRAY(2000,2000,PRODUCT))  // ถ้าเลือก 3 จะคำนวณ MAKEARRAY แค่ครั้งเดียว

    4.4 LET() – ลดการคำนวณตอนเรียกใช้ซ้ำแต่คำนวณทุกค่าที่ประกาศไว้

    =LET(name1, expr1, name2, expr2, …, result)
    =LET(slow, MAKEARRAY(2000,2000,PRODUCT),
      result, "เร็ว",
      result)
    • ทุก exprN ที่มีการประกาศตัวแปรไว้ จะถูกประมวลผลทันทีเมื่อสูตรรัน โดยไม่สนว่า nameN จะถูกนำไปใช้ใน result สุดท้ายหรือไม่
    • เช่น ในทีนี้ตัวแปร slow ไม่ได้ถูกเรียกใช้ในตอนจบเลย แต่ก็ยังคำนวณ MAKEARRAY ช้าๆ อยู่ดี
    • ใช้ลดการซ้ำของสูตร เช่น VLOOKUP ครั้งละหลายรอบได้ดี แต่ประกาศตัวแปรหนัก ๆ แล้วไม่ได้ใช้ → ก็ยังช้าเหมือนเดิม

    4.5 LET() + LAMBDA() – “Thunk” เลื่อนการคำนวณได้

    =LET(
      Thunk, LAMBDA(MAKEARRAY(2000,2000,PRODUCT)),
      result,    "เร็ว",
      result
    )
    • เมื่อประกาศตัวแปร ที่ใช้ LAMBDA ครอบไว้ → Excel จะไม่ประมวล MAKEARRAY ทันที
    • ถ้าต้องการคำนวณจริง ๆ ให้เรียก Thunk() โดยต้องเติมวงเล็บ () ไว้ข้างหลังด้วยนะ แบบนี้
    =LET(
      Thunk, LAMBDA(MAKEARRAY(2000,2000,PRODUCT)),
      result,    "เร็ว",    Thunk())

    🚀 5. ทริกปรับสูตรให้เร็วขึ้น

    1. ใช้ IF() แทน IFS() ถ้าเงื่อนไขแรกมีโอกาสเป็น TRUE สูง
    2. ห่อสูตรหนัก ๆ ใน LAMBDA() แล้วเรียกเมื่อจำเป็น
    3. ลดการคำนวณซ้ำ ด้วย LET() แต่แน่ใจว่าชื่อที่ประกาศจะถูกใช้จริง
    4. เลือก CHOOSE() แทน SWITCH() เมื่อค่าต้องเลือกเป็นลำดับ (1,2,3…) เพื่อได้ lazy evaluation

    ✨ 6. สรุปอีกทีให้เซฟเก็บ

    • IF(), IFNA(), IFERROR() = ✅ Lazy Evaluation ทำให้เร็ว : หยุดคำนวณเงื่อนไขหลังทันที
    • IFS() = ❌ Eager Evaluation ทำให้ช้า: คิดทุกเงื่อนไข
    • CHOOSE() = ✅ Lazy Evaluation ทำให้เร็ว : คิดเฉพาะทางเลือกที่เลือกจริงๆ
    • SWITCH() = Eager Evaluation ทำให้ช้า: คิดทุกทางเลือก
    • LET() = Eager Evaluation ทำให้ช้า: คิดทุกตัวแปร
    • LET+LAMBDA() = Lazy Evaluation ทำให้เร็ว: เลื่อนคำนวณจนกว่าจะเรียกใช้

    ใครกำลังเจอ Excel อืดเพราะสูตรซ้อนซับซ้อน ลองเอาแนวทางนี้ไปปรับใช้ดูครับ
    – บางทีแค่เปลี่ยนจาก IFS → IF หรือห่อสูตรใน LAMBDA ก็ช่วยลดเวลาไปได้ครึ่งหนึ่ง!

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

  • IF

    TLDR สรุปสั้นๆ

    IF ตรวจสอบเงื่อนไขว่าจริงหรือเท็จ แล้วคืนค่าที่กำหนดตามผลลัพธ์แยกเป็น 2 กรณี

    คำอธิบาย

    ฟังก์ชัน IF ช่วยเปรียบเทียบเงื่อนไขและคืนค่าที่ต่างกัน 2 กรณี ขึ้นอยู่กับผลของการเปรียบเทียบนั้น โดยถ้าเทียบแล้วเงื่อนไขเป็นจริง (TRUE) จะสามารถใช้สูตรแบบนึง ถ้าไม่จริง (FALSE) จะสามารถใช้สูตรอีกแบบนึงแทนได้ เช่น ถ้าคะแนนได้ตั้งแต่ 50 ขึ้นไปให้แสดง “ผ่าน” ถ้าไม่ใช่ให้แสดง “ไม่ผ่าน”

    มีครั้งแรกในเวอร์ชันไหน

    2003 หรือ Version ก่อนหน้า

    รูปแบบคำสั่ง (Syntax)

    IF(logical_test, value_if_true, [value_if_false])

    Arguments

    • logical_test (Required – logical expression)
      เงื่อนไขที่ต้องการตรวจสอบ (เป็นค่า True หรือ False หรือตัวเลข)
    • value_if_true (Required – any value)
      ค่าที่จะคืนเมื่อผลของ logical_test เป็น True หรือเลขที่ไม่ใช่ 0
    • value_if_false (Optional – any value)
      ค่าที่จะคืนเมื่อผลของ logical_test เป็น False หรือเลข 0

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

    IF 2
    • Formula:
      =IF(D3="book",G3,0)
      Description: ถ้าเซลล์ D3 เท่ากับ ‘book’ (ไม่สนใจพิมพ์เล็กพิมพ์ใหญ่) ให้คืนค่า G3 มิฉะนั้นคืนค่า 0
      Result: 1,100 (ค่าใน G3)
    • Formula:
      =IF(SUM(TxData[SalesAmt])>5000,"Pass Target","Fail")
      Description: ตรวจสอบว่ายอดขายรวมทั้งหมด (จากคอลัมน์ SalesAmt ในตาราง TxData) เกิน 5,000 หรือไม่ แล้วแสดงผลลัพธ์เป็นข้อความ “Pass Target” หรือ “Fail”
      Result: ได้ข้อความว่า “Pass Target”
    • Formula:
      =IF(G3>2000, "High", IF(G3>=1000, "Medium", "Low"))
      Description: แบ่งเกรดจากยอดขายใน G3 (SalesAmt) เป็น 3 ระดับ ซึ่งต้องใช้ IF 2 ตัวซ้อนกัน
      • “High” ถ้ายอดขายมากกว่า 2,000
      • “Medium” ถ้ายอดขายอยู่ระหว่าง 1,000 ถึง 2,000
      • “Low” ถ้ายอดขายน้อยกว่า 1,000
        Result: ได้ข้อความว่า “Medium”
    • Formula:
      =IF(SUM(TxData[SalesAmt]), "Sales Exist", "No Sales")
      Description: ถ้า SUM(TxData[SalesAmt]) มียอดขายรวมไม่ใช่ 0 → แสดง “Sales Exist” แต่ถ้าเป็น 0 จะขึ้นว่า “No Sales”
      Result: ได้ข้อความว่า “Sales Exist”

    Tips & Tricks

    • ถ้าต้องการได้ผลลัพธ์แค่เป็นค่า TRUE/FALSE เราไม่ต้องใช้ IF ก็ได้ เพราะแค่ใช้การเปรียบเทียบเชิง Logic เช่น A1=”cat” หรือใช้ฟังก์ชันตรวจสอบ เช่น ISNUMBER พวกนี้ก็ได้ค่า TRUE/FALSE แล้ว
    • ตรง Logical Test นอกจากจะใส่ TRUE/FALSE ได้แล้ว ถ้าเราใส่เป็นแค่ตัวเลขแทน มันจะมองเลขที่เป็น 0 คือ FALSE นอกนั้นถือเป็น TRUE ทั้งหมด (รวมถึงเลขติดลบ) ซึ่งเหมาะกับสูตรแบบ Array ที่ใช้ Boolean Logic แทน AND/OR มากๆ
    • ถ้าอยากเขียนสูตร IF ซ้อนกัน แนะนำใช้ Alt+Enter เพื่อขึ้นบรรทัดใหม่ในช่องเดิม สูตรจะอ่านง่ายขึ้น เช่น
    =IF(G3>2000, "High",
    IF(G3>=1000, "Medium", 
    "Low"))

    ข้อควรระวัง (Cautions)

    ระวังการเขียนฟังก์ชันที่ซับซ้อนด้วย IF ซ้อน (Nested IF) เพราะอาจทำให้สูตรยากต่อการจัดการและเกิดข้อผิดพลาดได้ ควรตรวจสอบการสะกดให้ถูกต้องเพื่อไม่ให้เกิดข้อผิดพลาดเช่น #NAME? ลองพิจารณาใช้สูตรอื่นมาช่วย เช่น

    • ถ้าเงื่อนไขเป็นช่วง สามารถใช้ VLOOKUP (Approximate Match) หรือ XLOOKUP (Exact Match or Next Larger, Smaller items)
    • ถ้าเงื่อนไขซับซ้อน อาจใช้ IFS หรือ SWITCH แทน

    ฟังก์ชันที่เกี่ยวข้อง

    References

    ขอบคุณที่เข้ามาอ่านนะครับ ❤️

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

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

    หลังจากในตอนที่แล้วผมได้มีการแนะนำแนวทางการใช้ Python ใน Excel เบื้องต้นไปแล้ว ในตอนนี้หลักๆ แล้ว ผมจะมาแนะนำวิธีเขียน Code เพื่อสร้าง Loop ใน Python ให้เพื่อนๆ ได้รู้จักกันครับ

    Loop คือการสั่งให้โปรแกรมคอมพิวเตอร์ทำงานซ้ำๆ ตามที่เรากำหนด ซึ่งใน python มี Loop แบบนึงที่นิยมมาก คือ for loop ซึ่งค่อนข้างทรงพลังมาก

    วิธีการคือ for loop จะวนพิจารณาทำงานซ้ำโดยอ่านค่า Item ใน Object ที่มี item ย่อยๆ อยู่ข้างใน ที่ใช้บ่อยมากคือ List Range กับ String (ข้อความ) เป็นต้น ซึ่งเป็นเรื่องที่ผมจะอธิบายในบทความนี้

    แต่ก่อนที่ผมจะอธิบายเรื่อง Loop เพื่อให้เห็นภาพมากขึ้น เรามาทำความรู้จัก List ให้ดีขึ้นกันอีกนิดนึงดีกว่าครับ

    เรื่องของ List ใน Python

    List คือประเภทข้อมูลแบบนึงของ Python ที่สามารถเก็บข้อมูลไว้ในตัวมันได้หลายตัว โดยที่ข้อมูลแต่ละตัวจะเป็นข้อมูลประเภทอะไรก็ได้ (ใน List มี List ซ้อนอีกก็ได้) เก็บซ้ำกันก็ได้ และลำดับการเก็บข้อมูลมีความหมาย (เราอ้างอิงตำแหน่งของมันได้)

    สมมติว่า List ต้นฉบับคือแบบนี้

    colorList=["red","green","blue","yellow"]
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 3

    อ้างอิงถึง item ใน List

    หากเราต้องการอ้างอิงถึง item ใน List สามารถใช้

    ListName[เลขindex]

    โดยที่เราสามารถอ้างอิงเลข index ได้ 2 แบบ คือ

    • เลข index เป็นบวก โดยเริ่มจาก item แรก คือ index 0 คือ แล้ว item 2 คือ index 1
    • เลข index ติดลบ ไล่ย้อนหลับนับ item สุดท้ายเป็น -1, รองสุดท้ายคือ -2

    เช่น

    • colorList[1] จะได้ item ที่ 2 คือ string ว่า green
    • colorList[-3] คือ item ลำดับที่ 3 จากท้าย ซึ่งในที่นี้ก็คือ green เช่นกัน
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 4

    Slice หั่นเลือกเอาช่วง item ใน List

    เราสามารถ slice เลือกบางส่วนของ List ได้ด้วยวิธีแบบนี้

    ListName[ indexเริ่ม : แต่ไม่ถึง indexจบ ]
    colorList[1:3]
    # เอาตั้งแต่ index1 (เริ่มตัวที่2) แต่ไม่ถึง index3 (ไม่ถึง 4 คือเอาถึงตัวที่3)

    สรุปแล้ว [1:3] แปลว่าเอาตั้งแต่ ตัวที่2 ถึงตัวที่ 3

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

    ถ้าจะเริ่มเอาแต่แรก สามารถใส่ colorList[0:3] หรือจะเว้นไปเลย เหลือแค่ colorList[:3] ก็ได้

    colorList[:3]
    # เอาตั้งแต่แรก แต่ไม่ถึง index3 (ไม่ถึง 4 คือเอาถึงตัวที่3)

    สรุปแล้ว [:3] แปลว่าเอาตั้งแต่ ตัวแรก ถึงตัวที่ 3

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

    ถ้าจะเอาถึงตัวสุดท้าย ก็เว้นการใส่ตัวสุดท้ายไปเลยก็ได้ เช่น

    colorList[1:]
    # เอาตั้งแต่ index1 (เริ่มตัวที่2) จนถึงตัวสุดท้าย

    สรุปแล้ว [1:] แปลว่าเอาตั้งแต่ตัวที่2 ถึง ตัวสุดท้าย

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

    แล้วก็สามารถใส่ index ติดลบได้เช่นเดิม

    colorList[1:-1]
    # เอาตั้งแต่ index1 (เริ่มตัวที่2) แต่ไม่ถึง index -1 (ไม่ถึงตัวสุดท้าย คือเอาถึงตัวรองสุดท้าย)

    สรุปแล้ว [1:-1] แปลว่าเอาตั้งแต่ตัวที่2 ถึง ตัวรองสุดท้าย

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

    การรวม List เข้าด้วยกัน

    เราสามารถรวม Item ใน List เข้าด้วยกันได้ด้วยเครื่องหมาย + ธรรมดาเลย simple สุดๆ เช่น

    MyList1=["red","green","blue"]
    MyList2=[55,20,200]
    combinedList=MyList1+MyList2
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 9

    แต่ถ้าจะมัดรวมเป็นคู่อันดับ ให้ใช้ zip มาช่วย เช่น

    MyList1=["red","green","blue"]
    MyList2=[55,20,200]
    zipObject=zip(MyList1,MyList2)
    zipList=list (zipObject)
    print(zipList)
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 10

    เริ่มทำการวน Loop

    พอเราเริ่มรู้จัก List ที่เป็น Object ประเภทนึงที่สามารถเก็บข้อมูลหลาย item ในตัวมันเองแล้ว เราลองมาเริ่มดูวิธีวน Loop กันบ้าง

    โดยโครงสร้างของ for loop ใน Python เป็นแบบนี้

    for item in Objectที่มีSequence:       # ต้องมี : ด้วย
        คำสั่งใน loop ที่จะให้ทำซ้ำ   #ต้องมี tab เพื่อย่อหน้าเข้ามา 1 ทีด้วย
        คำสั่งใน loop ที่จะให้ทำซ้ำ   #ต้องมี tab เพื่อย่อหน้าเข้ามา 1 ทีด้วย
    คำสั่งที่ไม่เกี่ยวกับ loop          #ถ้าไม่มีย่อหน้าคือไม่อยู่ใน loop

    วน Loop ใน List

    สมมติถ้าอยากจะวน Loop ใน colorList ที่สร้างไว้ แล้วพยายาม print เอาข้อมูลสีออกมาในแบบตัวพิมพ์ใหญ่

    โดยผมลองเขียนให้ดู เพื่อทำความเข้าใจแบบละเอียด ดังนี้

    เราสามารถจะตั้งชื่อตัวแปรที่เอาไว้วน Loop (ตัวที่อยู่หลัง for) ว่าอะไรก็ได้ เช่น i, x, num, char, color, blahblah หรืออะไรก็ได้

    ซึ่งในที่นี้เราตั้งว่า color โดยการวน loop แต่ละรอบ ตัวแปร color นี้ก็จะเปลี่ยนไปเรื่อยๆ โดยรับค่าที่เป็นข้อความ “red” “green” “blue” “yellow” เปลี่ยนไปเรื่อยๆ ทีละตัว

    การที่เราจะทำให้เป็นตัวพิมพ์ใหญ่ เราสามารถใช้ method ที่ชื่อว่า .upper() มาช่วยได้

    เพราะมันคือ Method ที่ถูกฝังไว้ใน object ประเภท String หรือข้อความนั่นเอง ซึ่งชื่อสีของเราเป็นข้อความก็ต้องใช้คำสั่งนี้ได้

    for color in colorList:
        UpColor=color.upper()    #สร้างตัวแปร UpColor มาเก็บตัวพิมพ์ใหญ่
        print(UpColor)        #print UpColor ออกมาใน Console
    print("นี่คือนอก loop")

    ผลที่ได้ คือ จะมีการ print ชื่อสีแต่ละอันเป็นตัวพิมพ์ใหญ่ ออกมาใน console ซึ่งใน Excel มันจะถือว่า Console อยู่ในส่วนของ Diagnostics ด้านขวา (ลากออกมาได้นะ)

    เราจะพบว่า มีการ Print สีจำนวน 4 ครั้ง (เพราะเรามีทั้งหมด 4 สี มันไล่วน Loop Print แต่ละสี สีละ 1 ครั้ง) แต่มีการ Print คำว่า “นี่คือนอก loop” แค่ 1 ครั้ง เพราะเราใส่ไว้โดยไม่มีการย่อหน้า แสดงว่าไม่อยู่ใน loop แล้ว

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

    เอาจริงๆ การวน loop print เราเขียนแค่นี้ก็พอ ไม่ต้องประกาศอะไรเยอะแยะ

    for color in colorList:
        print(color.upper())
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 12

    แต่ถ้าเราอยากให้มันออกมาใน Cell เป็น Dynamic Array เราต้องทำให้ผลลัพธ์เป็น List ก่อน Excel จึงจะเข้าใจ ถ้าปล่อยเป็น NoneType จะเอาออกมาไม่ได้

    วิธีการแบบนึงที่ทำได้คือ สร้าง List ว่างเปล่าขึ้นมาก่อน แล้ววน Loop เอา item แต่ละอันใส่ เข้าไปใน List ด้วย .append() แล้วค่อยเรียก List นั้นออกมาอีกที ดังนี้ (แล้วอย่าลืมแก้ Output ให้เป็น Excel Value ด้วย)

    UpperColorList=[]        #สร้าง UpperColorList ว่างเปล่า
    for color in colorList:
        UpperColorList.append(color.upper()) #ใส่ item เพิ่มใน List
    UpperColorList         #แสดง UpperColorList ออกมา
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 13

    List Comprehension

    อย่างไรก็ตาม เราสามารถสร้าง List ใหม่ได้ง่ายๆ กว่าที่เขียนข้างบน ด้วยเทคนิคที่เรียกว่า List Comprehension ซึ่งเหมือนการเขียน for loop แบบย่อสุดๆ ที่ให้ผลเป็น List ใหม่ได้เลย โดยไม่ต้องมานั่งเขียน code ยาวๆ เช่น

    ในรูปแบบของ

    [itemผลลัพธ์ for item in Objectที่มีSequence]

    เช่น

    [color.upper() for color in colorList]
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 14

    เดี๋ยวเราลองไปดูการวน Loop ใน item อื่นที่ไม่ใช่ List โดยตรงบ้าง

    วน Loop ใน Range

    ถ้าเราอยากจะวน Loop กับ ตัวเลข 6 ตัว ที่เริ่มจากเลข 0 เราสามารถใช้ฟังก์ชัน range(6) ได้ ซึ่งมันคือ Object ที่คล้าย List ที่ประกอบไปด้วย item 0,1,2,3,4,5 อยู่ข้างในนั่นเอง

    ลองให้มันสั่ง print เลข 0-5 ออกมาใน console โดยที่ตั้งตัวแปร i ขึ้นมาแทนแต่ละเลขขณะวน Loop

    for i in range(6):
        print(i)
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 15

    แต่ถ้าเราอยากให้มันออกมาใน Cell เป็น Dynamic Array ก็ใช้ List Compreshension ก็ได้ ง่ายดี

    [i for i in range(6)]

    เราจะได้ผลลัพธ์เป็น List ใหม่ทันที ง่ายๆ แบบนี้เลย

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

    หรือถ้าอยากได้เลขที่ไม่ได้เริ่มจาก 0 ก็สามารถทำแบบนี้ได้ ในรูปแบบของ

    range(เลขเริ่ม,แต่ไม่ถึงเลขจบ)

    เช่น

    [i for i in range(3,10)]

    แบบนี้คือ 3 ถึง 9 (เลขจะไม่รวม 10)

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

    และถ้าอยากจะเพิ่ม step มากกว่า 1

    range(เลขเริ่ม,แต่ไม่ถึงเลขจบ,step)

    เช่น เพิ่มทีละ 2 ก็ทำแบบนี้ได้ คือใส่ ,2 ต่อไปอีก

    [i for i in range(3,9,2)]
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 18

    กลับมาที่ range(6) คือ เลข 0-5 อีกที…

    ถ้าผลลัพธ์ที่เราต้องการไม่ได้เป็นการสร้าง List ใหม่ ก็อาจจะไม่ต้องใช้ List Comprehension ก็ได้

    เช่น ลองสร้างเลขบวกสะสมกันไปเรื่อยๆ ตั้งแต่ 0-5

    x=0
    for i in range(6):
        x=x+i
    x
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 19

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

    MyList=[i for i in range(6)]
    x = sum(MyList)
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 20

    วน Loop ใน String

    นอกเหนือจากการวน Loop ใน List และใน Range แล้ว เรายังสามารถวน Loop ใน String หรือข้อความได้ด้วย ซึ่งมันคือ การวนพิจารณาทีละอักขระของข้อความได้เลย ซึ่งทรงพลังมากๆ

    MyList=[]    #สร้าง List ว่างเปล่า
    for char in "thepexcel":
        MyList.append(char)    #ใส่แต่ละอักขระเพิ่มเข้าไปใน List
    MyList        #แสดง MyList ออกมา
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 21

    ซึ่งใช้ List Comprehension ง่ายกว่าเยอะ

    MyList=[char for char in "thepexcel"]
    
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 22

    การใส่ Condition

    เราสามารถจะใช้ if เพื่อสร้าง condition บางอย่างได้

    กรณีถ้าเงื่อนไขจริงแล้วให้ทำอะไรบางอย่าง (ถ้าไม่ตรงก็ไม่ทำอะไร)

    if condition:
        #action1 if true
        #action2 if true

    เช่น

    ผมทำให้ดู 2 เคสเลย ว่ากรณีเงื่อนไขเป็นจริง ก็จะทำตาม action ที่กำหนด แต่ถ้าไม่ตรงตามเงื่อนไขก็จะไม่มีอะไรเกิดขึ้น

    result1="original1"
    score=45
    if score>10:
        result1="score1 มากกว่า 10"
    
    result2="original2"
    score=5
    if score>10:
        result2="score2 มากกว่า 10"
        
    [result1,result2]
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 23

    การเขียน if แบบนี้ ซึ่งถ้าผลลัพธ์มีแค่ 1 action เราสามารถย่อได้ จะได้ไม่ต้องปวดหัวกับการ indent ย่อหน้าเข้าไป

    if condition: action_if_true

    เช่น อันนี้ผมทำเคสจริงให้ดูอย่างเดียว

    result1="original1"
    score=45
    if score>10:result1="score1 มากกว่า 10"
        
    result1
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 24

    กรณีถ้าเงื่อนไขจริงแล้วให้ทำอะไรบางอย่าง ถ้าไม่จริงให้ทำอีกอย่าง

    ก็คือนอกจากจะมี if แล้ว คราวนี้ต้องมี else เพื่อทำกรณีที่ไม่จริงด้วย ซึ่งโครงสร้างคือ

    if condition:
        #action if true
    else:
        #action if false

    เช่น

    result="original"
    score=5
    if score>10:
        result="score มากกว่า 10"
    else:
        result="score ไม่ได้มากกว่า 10"
    result
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 25

    ซึ่งถ้าแต่ละเงื่อนไขมีแค่ 1 action เราสามารถย่อได้เช่นกัน ในรูปแบบนี้ คือไม่มี : ไม่มีย่อหน้าเลยด้วย แต่จะมีการสลับเอา action กรณีจริงขึ้นมาก่อน

    action_if_true if condition else action_if_false

    แต่ action ในรูปแบบนี้จะใช้การ assign ตัวแปรไม่ได้นะ ดังนั้นผมจะใช้การ print เพื่อพิสูจน์ให้ดูแทน

    score=5
    print("score มากกว่า 10") if score>10 else print("score ไม่ได้มากกว่า 10")
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 26

    ลองใช้ condition ใน Loop

    เราลองมาดูตัวอย่างการใช้ Condition ใน Loop กัน

    สมมติว่า เราจะวน Loop ใน String เพื่อพิจารณาแต่ละอักขระ ถ้าพบว่าอักขระที่กำลังพิจารณา (char) เป็นตัว e เราจะให้ทำเป็นตัวพิมพ์ใหญ่ โดยใช้ method .upper() ภายใน condition ของ if

    MyList=[]    #สร้าง List ว่างเปล่า
    for char in "thepexcel":
        if char =="e":
            char=char.upper()    #ถ้าเป็นตัว e ให้ทำเป็นพิมพ์ใหญ่
        MyList.append(char)    #ใส่แต่ละอักขระเข้าไปใน List
    MyList        #แสดง MyList ออกมา
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 27

    แต่ถ้าในกรณีนี้เราทำด้วย List Comprehension แบบทื่อๆ เลย การเขียน condition if แบบย่อแค่ action จริง จะไม่ work ในกรณีนี้

    MyList=[char.upper() for char in "thepexcel" if char=="e" ]

    เพราะว่ามันดันเป็นการสร้าง List ใหม่ โดย “คัดเลือกเฉพาะที่ตรงกับเงื่อนไข” ที่กำหนดคือเป็นตัว e เท่านั้น (กลายเป็นได้ E 3 ตัวเฉยเลย นั่นคือ กรณี False จะไม่มีการเอามาใส่ใน List)

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

    ถ้าอยากให้มีทุกตัวเท่าเดิม เราควรกำหนดทั้งกรณีทั้ง True และ False แต่โครงสร้างของ List Comprehension จะเปลี่ยนไปนิดหน่อย กลายเป็น if else ซึ่งจะมีการสลับตำแหน่ง

    MyList=[char.upper() if char=="e" else char for char in "thepexcel" ]

    แบบนี้ถึงจะ work ได้ตามต้องการครับ

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

    ลองการประยุกต์

    วน Loop เพื่อแก้ชื่อคอลัมน์ของ DataFrame

    สมมติผมมี DataFrame อยู่ แล้วอยากจะได้ชื่อคอลัมน์ออกมาเป็น List ผมสามารถทำได้แบบนี้

    df = xl("IrisDataSet[#All]", headers=True)
    dfColList=list(df.columns)
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 30

    ทีนี้ผมอยากจะวน Loop เพื่อทำการแก้ชื่อคอลัมน์เฉพาะที่มีเครื่องหมาย _ ให้เป็นพิมพ์ใหญ่

    ผมทำได้ดังนี้

    NewdfColList=[ col.upper() if "_" in col else col for col in dfColList ]
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 31

    แล้วเราก็เอาชื่อคอลัมน์ใหม่ assign เข้าไปใน df เดิมก็ได้ ดังนี้

    df.columns = NewdfColList
    df
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 32

    จริงๆ ก็คือเขียนรวมกันหมดเลยใน code ของ cell เดียวก็ได้นะ เช่น

    df=xl("IrisDataSet[#All]", headers=True)
    dfColList=list(df.columns)
    NewdfColList=[ col.upper() if "_" in col else col for col in dfColList ]
    df.columns = NewdfColList
    df
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 33

    คัดเลือกให้แสดง DataFrame แค่บางคอลัมน์

    ทีนี้ในเมื่อเราจัดการ List เป็นแล้ว อาจใช้มันในการเลือก List ที่ต้องการก็ได้ เช่น ผมต้องการ Columnที่ 1,4,5 เสมอ ผมอาจใช้แบบนี้

    SelectCol=[NewdfColList[i-1] for i in [1,4,5]]
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 34

    แล้วผมค่อยเอา List รายชื่อคอลัมน์นี้ใส่เข้าไปใน DataFrame เพื่อเป็นการคัดเลือกเอาคอลัมน์ที่ต้องการได้เลย เช่น

    df[SelectCol]
    สอนใช้ Python ใน Excel ตอนที่ 2 : List, Loop, Condition 35

    ตอนต่อไป

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

  • สอนใช้ฟังก์ชัน IF และผองเพื่อน (IFS, SWITCH)

    สอนใช้ฟังก์ชัน IF และผองเพื่อน (IFS, SWITCH)

    หนึ่งในฟังก์ชันที่ถูกใช้บ่อยที่สุดอันนึงในการเขียนสูตร Excel ก็คือฟังก์ชันที่ชื่อว่า IF ซึ่งมีความสามารถในการเขียนสูตรแบบมีเงื่อนไข ซึ่งเวลาผมสอนมักจะบอกเสมอว่า หากในหัวเรานึกถึงคำว่า “ถ้า” ก็ให้นึกถึง IF ได้เลย ซึ่งนอดีตผมเคยเขียนบทความเกี่ยวกับ IF ไว้แล้ว แต่วันนี้จะมา Refresh ให้ใหม่ พร้อมแนะนำเพื่อนๆ ของมันคือ IFS และ SWITCH ด้วย

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

    เช่น สมมติเราจะให้ขนมเป็นของรางวัลเด็ก โดยมีเงื่อนไขว่า จะให้จำนวนขนมตามอายุของเด็ก แต่ถ้าอายุเกิน 10 ปี จะได้ครึ่งนึงของอายุ (เศษปัดทิ้ง) เช่น อายุ 8 ขวบได้ขนม 8 ชิ้น แต่ถ้า 13 ขวบ จะได้ 6 ชิ้น เป็นต้น

    เริ่มจากการคิด Logic ของ Decision Tree

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

    if

    ถ้ามีปัญหาเหล่านี้ สิ่งที่ผมอยากบอกคือ อย่าเพิ่งเขียนสูตร แต่ให้ลองเขียนแผนผังการตัดสินใจแบบ Decision Tree ขึ้นมาก่อน (ถ้าเรายังไม่ค่อยคล่อง) เพื่อให้เราสามารถเข้าใจภาพรวมทั้งหมด และคิดเป็นระบบมากขึ้น โดยตกผลึกความคิดได้อย่างชัดเจนก่อนว่าวิธีคิดคำตอบมันมีกี่กรณี ตัวแบ่งคืออะไร?

    จะเห็นว่าผลลัพธ์ของโจทย์นี้มีอยู่ 2 กรณี คือ

    1. กรณีอายุเกิน 10 ปี : ได้ครึ่งนึงของอายุ (เศษปัดทิ้ง) ซึ่ง สูตรผลลัพธ์คือ INT(เลขอายุ/2)
    2. กรณีอายุไม่เกิน 10 ปี : ได้ตามอายุ ซึ่ง สูตรผลลัพธ์คือ เลขอายุ

    พอเห็นแบบนี้ดังนั้นเงื่อนไขที่เราต้องเช็คก็คือ “อายุเกิน 10 ปีหรือไม่?” นั่นเอง

    ดังนั้นสามารถเขียนสูตรได้แบบนี้

    การเขียนสูตร IF

    สูตร IF 1 ตัว จะแยกผลลัพธ์ได้เป็น 2 แบบ ดังนี้

    =IF(logical_test,value_if_true,value_if_false)
    =IF(เงื่อนไข,ถ้าเงื่อนไขจริงจะทำอะไร,ถ้าเงื่อนไขเป็นเท็จจะทำอะไร)
    
    Tips : ตรงส่วน logical_test นั้นโดยทั่วไปเราจะใส่สูตรเพื่อให้ออกมาเป็นค่า TRUE/FALSE แต่มันยังสามารถรับค่าที่เป็นตัวเลขได้ด้วย (ถ้าเป็นเลข 0 จะเป็น FALSE ส่วนเลขอื่นๆ จะเป็นจริงทั้งหมด)

    ดังนั้นในเคสนี้ สูตรจึงออกมาเป็นแบบนี้

    =IF(อายุ > 10,INT(อายุ/2),อายุ)
    สอนใช้ฟังก์ชัน IF และผองเพื่อน (IFS, SWITCH) 36

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

    ส่วน logical_test

    การจะเขียนสูตรในส่วนของ logical_test ได้ จะต้องเข้าใจเรื่องดังนี้มาก่อน

    • การจะได้ค่า TRUE/FALSE เกิดได้จาก 2 กรณีหลักๆ คือ
      • การใช้เครื่องหมายเปรียบเทียบ เช่น =, >, <, >=, <=, <> (ไม่เท่ากับ) เช่น ในเคสนี้มีการเทียบว่า อายุ >10 หรือไม่?
      • การฟังก์ชันพวกกลุ่ม IS… เช่น ISNUMBER, ISERROR
    • ฟังก์ชันตรรกศาสตร์เพื่อรวบเงื่อนไขหลายๆ อันเข้าด้วยกัน
      • AND เงื่อนไขย่อยๆ ทุกอันต้องเป็นจริงทั้งหมด จึงจะให้ผลลัพธ์เป็นจริง เขียนในรูปแบบ AND(เงื่อนไข1,เงื่อนไข2)
      • OR เงื่อนไขย่อยๆ อย่างน้อยอันใดอันหนึ่งเป็นจริง จึงจะให้ผลลัพธ์เป็นจริง เขียนในรูปแบบ OR(เงื่อนไข1,เงื่อนไข2)
      • NOT กลับจริงเป็นเท็จ เท็จเป็นจริง เขียนในรูปแบบ NOT(เงื่อนไข)

    Tips : ตรงส่วน logical_test นั้นโดยทั่วไปเราจะใส่สูตรเพื่อให้ออกมาเป็นค่า TRUE/FALSE แต่มันยังสามารถรับค่าที่เป็นตัวเลขได้ด้วย (ถ้าเป็นเลข 0 จะเป็น FALSE ส่วนเลขอื่นๆ จะเป็นจริงทั้งหมด) ดังนั้นมันจึงเหมาะกับการใช้ Boolean Logic แทนการใช้ AND, OR ใน Array Formula มากๆ ซึ่งถ้าใครสนใจลองอ่านได้ที่นี่

    ส่วนของ value_if_true, value_if_false

    ถ้าถามว่าสูตรในส่วนนี้เขียนยังไง ผมจะบอกว่าอันนี้เป็นเรื่องอื่นที่ไม่เกี่ยวกับ IF โดยสิ้นเชิงเลย ถ้าคุณไม่มีความรู้เรื่องการเขียนสูตรอื่นๆ ก็จะทำ Part นี้ไม่ได้ครับ ดังนั้นให้ไปฝึกฝนเรื่องอื่นๆ ด้วย เช่น ในตัวอย่างนี้มีการใช้ INT หรือจะใช้ ROUNDDOWN ก็ได้ แต่ถ้าไม่รู้จักเลยก็จะทำโจทย์นี้ไม่ได้นั่นเอง ซึ่งจะเห็นว่าไม่ได้เกี่ยวกับ IF เลยนะครับ

    แล้วถ้าผลลัพธ์มีมากกว่า 2 กรณีล่ะ?

    อย่างที่ผมบอกไปก่อนหน้านี้ว่า IF สามารถแสดงผลลัพธ์ได้ 2 กรณี หลายคนอาจะสงสัยว่า แล้วถ้าผลลัพธ์มีมากกว่า 2 กรณีล่ะ? จะทำไง?

    ผมจะขอแนะนำแบบนี้ครับ

    ถ้าผลลัพธ์ของแต่ละกรณี สามารถใช้การ Lookup ได้ ให้ Lookup

    พูดง่ายๆ มันถ้าสิ่งที่เราต้องการ สามารถใช้การสร้างตารางอ้างอิงแล้วดึงค่ากลับมาได้ เช่น การดึงชื่อพนักงานจากรหัสพนักงาน ประเภทสินค้าจากรหัสสินค้า หรือแม้แต่การตัดเกรดจากคะแนนที่ได้ พวกนี้ควรใช้สูตรพวก Lookup/Reference เช่น VLOOKUP, INDEX+MATCH, XLOOKUP แทนการใช้ IF ครับ เพราะจะเขียนสูตรง่ายกว่า แถมยืดหยุ่นและแก้เงื่อนไขได้ง่ายกว่าการใช้ IF เยอะเลย

    สอนใช้ฟังก์ชัน IF และผองเพื่อน (IFS, SWITCH) 37

    ถ้าผลลัพธ์ของแต่ละกรณี Lookup ไม่ได้

    สอนใช้ฟังก์ชัน IF และผองเพื่อน (IFS, SWITCH) 38

    เช่น จากข้อมูลข้างต้น เรามีเงื่อนไขการลดราคาว่า

    • ถ้าซื้อเกิน 3 ชิ้น ลด 10%
    • ถ้าซื้อตั้งแต่ 5 ชิ้นขึ้นไป และชำระเงินเป็นเงินสด ถึงจะลด 15%

    แบบนี้เราจะใช้พวกสูตรกลุ่ม Lookup ไม่ได้แล้วครับ แต่จะต้องใช้ IF ซ้อนกัน/IFS/SWITCH มาช่วยแทน

    และเราก็ควรคิดให้ดีก่อนว่า ควรพิจารณาเงื่อนไขไหนก่อนดี ซึ่งถ้าไล่ให้ครบจริงๆ ต้องเขียนให้มีผลลัพธ์ 3 แบบดังนี้

    • ถ้าซื้อตั้งแต่ 5 ชิ้นขึ้นไป และชำระเงินเป็นเงินสด ถึงจะลด 15%
      (เราต้องคิดอันนี้ก่อนเรื่องซื้อเกิน 3 ชิ้น ไม่งั้นจะไม่มีทางมาตกเงื่อนไขตัวนี้เลย)
    • ถ้าซื้อเกิน 3 ชิ้น ลด 10%
    • นอกนั้น ลด 0%

    IF ซ้อนกัน

    โดยที่ผมจะขอเริ่มจากตัวที่เป็นพื้นฐานที่ใช้ได้ทุก version นั่นก็คือ IF ซ้อนกัน ซึ่งวิธีทำก็คือใส่ IF ลงไปอีกชุดนึงเลยในส่วนที่เป็น value_if_true หรือ value_if_false ก็ได้ (หรือทั้งคู่ก็ได้)

    สอนใช้ฟังก์ชัน IF และผองเพื่อน (IFS, SWITCH) 39

    ซึ่งถ้าเขียนเป็นโครงสร้างสูตร จะเป็นดังนี้

    =IF(logical_test1,value_if_true1,IF(logical_test2,value_if_true2,value_if_false2))

    ถ้าใช้กับโจทย์ที่ให้ไปก็จะเป็นแบบนี้

    =IF(AND([@จำนวนชิ้น]>=5,[@วิธีการชำระเงิน]="เงินสด"),0.15,IF([@จำนวนชิ้น]>3,0.1,0))

    ซึ่งสูตรข้างบน สามารถใช้ IFS แทนได้ ก็จะไม่ต้องใช้หลายฟังก์ชันซ้อนกัน

    IFS

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

    ifs
    =IFS(logical_test1,value_if_true1,logical_test2,value_if_true2,...)

    ซึ่งถ้าอยากจะแสดงค่ากรณีที่ไม่ตรงกับเงื่อนไขใดๆ เลย สามารถใช้ TRUE มาช่วยได้ตอนจบดังนี้

    ifs
    =IFS(logical_test1,value_if_true1,logical_test2,value_if_true2,TRUE(),value_else)

    ถ้าใช้กับโจทย์ข้างบนจะเป็นแบบนี้

    =IFS(AND([@จำนวนชิ้น]>=5,[@วิธีการชำระเงิน]="เงินสด"),0.15,[@จำนวนชิ้น]>3,0.1,TRUE,0)

    ลองดูตัวอย่างอื่นๆ ได้จากคลิปนี้

    SWITCH

    ถ้าเราไม่มี IFS ให้ใช้ แต่มี SWITCH ก็สามารถใช้ได้เหมือนกัน (เช่นใน DAX จะ ไม่มี IFS แต่มี SWITCH)

    ตามปกติแล้ว SWITCH คือจะคล้ายๆ กับ CHOOSE คือเลือกว่า ถ้าค่าที่เราสนใจเป็นแบบนี้ๆ จะทำอะไร แต่ SWITCH ดีกว่า CHOOSE ตรงที่ว่าค่าที่สนใจสามารถเป็นอะไรก็ได้ ในขณะที่ CHOOSE ค่าที่สนใจต้องเป็นตัวเลข 1,2,3,4,… ไปเรื่อยๆ เท่านั้น

    switch
    =SWITCH(expression,value1,result1,value2,result2,...,default)

    แต่ถ้าเรานำ SWITCH มาพลิกแพลงโดยใส่ TRUE ลงไปใน expression หรือค่าที่จะเช็ค มันจะทำตัวแบบ IFS ได้เลย เพราะเมื่อไหร่ที่ value เป็นจริง มันจะแสดง result ของ value นั้นทันที ดังนี้

    switch
    =SWITCH(TRUE(),logical_test1,value_if_true1,logical_test2,value_if_true2,value_else)

    ถ้าใช้กับโจทย์ข้างบนจะเป็นแบบนี้

    =SWITCH(TRUE,AND([@จำนวนชิ้น]>=5,[@วิธีการชำระเงิน]="เงินสด"),0.15,[@จำนวนชิ้น]>3,0.1,0)

    จบแล้ว

    เช่นเคย ใครอ่านแล้วสงสัยอะไรตรงไหนก็ถามได้เลยนะครับ หากชอบก็ฝากแชร์ให้เพื่อนๆ ด้วยนะ ^^

  • สอนฟังก์ชัน IF ใน Excel แบบ Basic สุดๆ ดูจบใช้เป็นเลย (เรียนจากความสูงของตัวละครใน Dragon Ball)

    สอนฟังก์ชัน IF ใน Excel แบบ Basic สุดๆ ดูจบใช้เป็นเลย (เรียนจากความสูงของตัวละครใน Dragon Ball)

    ในคลิปนี้เราจะเอาความสูงของตัวละครต่างๆ ในการ์ตูนสุดฮิตอย่างดราก้อนบอล (Dragon Ball) มาช่วยให้เราเรียนรู้เกี่ยวกับฟังก์ชัน IF ใน Excel กันครับ คลิปสั้นๆ แต่ดูจบได้ความรู้เต็มเปี่ยมแน่นอน ดูจบใช้เป็นเลย

    คุณสามารถโหลดไฟล์ประกอบได้ที่ https://github.com/ThepExcel/download/blob/master/DBZ.xlsx

    ถ้าชอบคลิปนี้ ช่วยกด Like / Subscribe / ช่วยแชร์ ให้ด้วยนะครับ

  • สอนวิธีทำ Drop Down List แบบ Search ได้ใน Excel (ไม่ต้องใช้ VBA)

    สอนวิธีทำ Drop Down List แบบ Search ได้ใน Excel (ไม่ต้องใช้ VBA)

    คุณเคยมีปัญหามีรายการที่จะต้องแสดงใน Drop Down List จำนวนมากจนแสดงไม่ไหวหรือไม่?

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

    หลายคนคิดว่าเรื่องแบบนี้ต้องใช้ VBA ทำเท่านั้น แต่ในความเป็นจริง เราก็สามารถเขียนสูตรเพื่อสร้าง “Drop down List แบบค้นหาได้” โดยจะใช้ Excel Version ไหนก็สามารถทำได้ครับ สุดยอดไปเลยมั้ยล่ะ!!

    คลิปวีดีโอ

    ในคลิปนี้ผมมีการอธิบายโดยละเอียดถึงวิธีทำตั้งแต่ต้นจนจบ ลองดูได้เลยครับ

    ไฟล์ประกอบการทำตาม

    ไฟล์เริ่มต้น :

    https://drive.google.com/open?id=1qiWOJjoB9dGyPDt9HjByaCLUwky8_jqX

    ไฟล์จบ :

    https://drive.google.com/open?id=17QXXR5xVtHqxPgU1ay5biyg4Vcd1ypod

    หลักการทำงานของ Drop Down List แบบค้นหาได้

    • ใช้ ISNUMBER + SEARCH เพื่อเช็คว่ารายการไหน มีคำค้นหาอยู่บ้าง
    • พยายามเอารายการที่เจอ มากองรวมกันข้างบนด้วย INDEX
      • ในนี้ผมมีใช้ฟังก์ชัน ROW กับ RANK เพื่อช่วยจัดอันดับตัวที่เจออันดับ 1,2,3 ด้วยครับ
    • จากนั้นใช้ OFFSET เพื่อเลือก Range ของรายการที่เจอทั้งหมด
    • เอาสูตร OFFSET ที่ได้ไปตั้งชื่อ
    • เอาชื่อที่ตั้งไปใส่ใน Data Validation List
    • เลือก Option ว่าไม่ต้องแสดง Error Alert
    • ใช้ IF ดักว่าถ้าหาไม่เจอ ให้แสดงคำว่า “ไม่เจอข้อมูล”
    Drop Down List แบบ Search ได้ใน Excel (ไม่ต้องใช้ VBA)

    สรุปสูตร

    IsFound =ISNUMBER(SEARCH($A$2,[Province]))
    GetRowNum =IF([@IsFound],ROW([@Province]),"")
    Rank =RANK.EQ([@GetRowNum],[GetRowNum],1)
    Rearrange =INDEX([Province],MATCH(ROW([@Province])-1,[Rank],0))
    count=--NOT(ISERROR([@Rearrange]))
    
    =IF(SUM(Table1[count])=0,$K$4,OFFSET(Table1[[#Headers],[Rearrange]],1,0,SUM(Table1[count]),1))
    
    ใน A2 คือช่องที่จะทำ Dropdown
    ใน K4 มีคำว่า ไม่เจอข้อมูล
    

    อยากใช้ Drop Down หลายที่ทำไง?

    ถ้าอยากให้ Dropdown List ที่ทำมาสามารถ Copy ไปใช้ได้หลายๆ ที่ ต้องเปลี่ยนสูตรตรง IsFound เล็กน้อยนะครับ

    แก้จาก
    IsFound =ISNUMBER(SEARCH($A$2,[Province]))
    เป็น
    IsFound =ISNUMBER(SEARCH(CELL("contents"),[Province]))

    หลักการคือ ฟังก์ชัน CELL หากไม่ระบุ Cell Reference จะอ้างอิงถึงช่องล่าสุดที่มีการแก้ไขได้ ดังนั้น CELL(“contents”) จะดึงข้อมูลจากช่องที่มีการแก้ไขล่าสุดไปใช้ใน SEARCH ได้ครับ

    แหล่งอ้างอิง

  • ติวเข้มก่อนเข้าโรงเรียนเวทมนตร์ #2 : ข้อควรระวังในการเขียนวันที่ในสูตร

    ติวเข้มก่อนเข้าโรงเรียนเวทมนตร์ #2 : ข้อควรระวังในการเขียนวันที่ในสูตร

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

    8) ตามรูป หากเขียนสูตรว่า =IF(A1<31/12/2016,”ยังไม่หมดเขต”,”หมดเขตแล้ว”) ผลลัพธ์จะได้ออกมาเป็นอะไร?
    (A1 คือ วันที่ 29 สิงหาคม ค.ศ. 2016)

    011

    ข้อนี้หลายคนโดนหลอกอย่างจัง เพราะข้อนี้คำตอบคือ “หมดเขตแล้ว”

    สาเหตุคือ เวลาเราเขียนค่าที่เป็นวันที่ลงไปในสูตร หากเราดันไปเขียนว่า =A1<31/12/2016 มันจะกลายเป็น เอา 31 หารด้วย 12 แล้วหารด้วย 2016 แทน (ไม่เชื่อลองเขียนสูตรว่า =31/12/2016 ดูสิ จะได้ ซึ่งจะได้ A1<0.00128141534391534  )

    ดังนั้นสูตรที่เขียนว่า =A1<31/12/2016 จะได้ =A1<0.00128141534391534

    ซึ่ง A1 เป็นวันที่จริงๆ ซึ่งมันเปรียบเทียบได้เป็นเลข 42611 (จริงๆ แล้ววันที่คือเลขธรรมดา เช่น 1 มกราคม ค.ศ. 1900 คือเลข 1, 2 มกราคม ค.ศ. 1900 คือเลข 2 …)

    ดังนั้นสูตรที่เขียนจริงเหมือนกับเขียนว่า =42611<0.00128141534391534 ดังนั้นมันจึงออกมาเป็น FALSE ซึ่งก็คือ “หมดเขตแล้ว” นั่นเอง

    ทีนี้หากจะเปรียบเทียบวันที่แบบถูกต้องจะทำยังไงได้บ้าง???

    ผมอยากจะแนะนำ 3 วิธีด้วยกันนะครับ ลองเอาไปเลือกใช้ดูตามความเหมาะสม

    1. เขียนวันที่เป็น Text แล้วแปลงเป็นตัวเลข

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

    วิธีเขียนคือ ให้เขียนวันที่ในเครื่องหมายคำพูด แล้วเอาไป convert ให้เป็น Number โดยการเอาไปทำอะไรซักอย่างกับ Math Operator เช่น *1, +0, ใส่ – นำหน้า เป็นต้น (ส่วนตัวผมชอบใส่ – นำหน้า เพราะสังเกตง่าย และคำนวนเร็วสุด)
    explain-03

    ซึ่งพอกด Enter ผลลัพธ์ก็จะออกมาเป็น “ยังไม่หมดเขต” ตามที่ควรจะเป็น (อย่าลืมลองเปลี่ยนวันที่เล่นดูว่าสูตรยังทำงานถูกหรือไม่?)

    แต่วิธีนี้มีข้อควรระวัง คือ เรื่องของ Format วันที่ของ Region and Language ใน Control Panel ของคอมพิวเตอร์ที่กำลังเปิดไฟล์นั้นๆ อยู่ เพราะอาจทำให้วันและเดือนสลับกันได้ซึ่งอันตรายพอตัวเลยครับ

    เช่น บางเครื่องตั้งค่าวันที่แบบที่อ่านค่า 3/4/2016 แปลว่า วันที่ 3 เดือน 4 แต่บางเครื่องตั้งค่าให้แปลว่า วันที่ 4 เดือน 3 เป็นต้น

    ซึ่งการที่เราใส่วันที่เป็นรูปแบบตัวเลข xx/yy/zzzz แบบนี้ มันเป็นการ Hard Code แบบใส่ค่าคงที่ลงไปในสูตร ซึ่งไม่ว่าจะเปิดที่ Computer เครื่องไหนก็ตาม มันก็จะเห้นสูตรแบบเดียวกันหมดนี่แหละ แต่อาจตีความไม่เหมือนกันก็ได้

    ดังนั้น ถ้าจะใช้วิธีนี้ โปรดทำให้แน่ใจว่า ไฟล์ที่เราทำจะถูกเปิดใช้โดย Computer ที่ตั้งค่า Format วันที่ของ Region and Language แบบที่เราคิดไว้เท่านั้น

    2.เขียนวันที่ลงไปใน Cell เลย

    วิธีนี้เป็นวิธีที่ดีตรงที่เห็นชัดว่าเรากำลังเปรียบเทียบกับวันอะไรอยู่?
    explain-01

    ซึ่งพอกด Enter ผลลัพธ์ก็จะออกมาเป็น “ยังไม่หมดเขต” ตามที่ควรจะเป็น

    วิธีนี้สิ่งที่เราต้องทำคือ จงแน่ใจว่าเราใส่รูปแบบวันที่แบบเดียวกับวันที่ที่นำมาเปรียบเทียบ เช่น ถ้าเป็น DD/MM/YYYY ก็ใส่ให้เหมือนกัน หรือถ้าเป็น MM/DD/YYYY ก็จงทำให้เหมือนกัน ไม่่งั้นอาจผิดเพราะเรื่อง Format วันที่ของ Region and Language แบบเดียวกับข้อข้างบนได้

    3. เขียนวันที่ด้วยฟังก์ชั่น DATE

    วิธีนี้มีความปลอดภัยสูงที่สุด เพราะจะไม่มีปัญหาเรื่องการตั้งค่า Format วันที่ของ Region and Language ของคอมพิวเตอร์แต่ละเครื่องเลย เพราะฟังก์ชั่น DATE จะถามหา Argument แยกกัน นั่นคือ ปี เดือน วัน แต่ละตัวแยกกันโดยไม่ปนกันเลย
    explain-02

    ดังนั้นถ้าถามผมว่าวิธีไหนน่าจะ Work และปลอดภัยที่สุด ความเห็นส่วนตัวของผมคือ วิธีสุดท้ายนี่แหละครับ ^^

    เอาล่ะ ใครอ่านจบถึงตรงนี้ ถ้าหากมีคำถามอะไร อย่าปล่อยผ่านนะครับ ถามมาได้เล้ยยยย

     

  • IF vs CHOOSE vs VLOOKUP เปรียบเทียบฟังก์ชั่นทำหน้าที่ตัดสินใจ

    IF vs CHOOSE vs VLOOKUP เปรียบเทียบฟังก์ชั่นทำหน้าที่ตัดสินใจ

    “ชีวิตคนเราเต็มไปด้วยการตัดสินใจ…” จริงๆ แล้วคนเราตัดสินใจอยู่ตลอดเวลา (บางทีก็ตัดสินใจที่จะอยู่เฉยๆ 555) บางทีเราก็ต้องเลือกระหว่าง Choice 2 อย่าง แต่บางทีก็มี Choice มากมายนับไม่ถ้วน…

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

    ซึ่งจริงๆ แล้วการตัดสินใจใน Excel นั้นมีฟังก์ชั่นที่มาช่วยในเรื่องนี้หลายตัวด้วยกัน แต่ในบทความนี้ผมจะขอยกตัวอย่างฟังก์ชั่นที่น่าสนใจมากๆ 3 อัน นั่นคือ IF, CHOOSE, และ VLOOKUP นั่นเอง ซึ่งผมบอกเลยว่า นี่คือ “หัวใจสำคัญ” ที่จะทำให้คุณใช้ Excel ได้เก่งกาจขึ้นอีกมากครับ!!

    ก่อนอื่น ขอเริ่มจากฟังก์ชั่นพื้นฐานที่สำคัญที่สุดในการคิดแบบตัดสินใจ นั่นคือ IF ครับ

    IF

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

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

    ซึ่งเงื่อนไขที่ว่าก็คือสิ่งที่เขียนอยู่ในส่วนของ logical_test ในฟังก์ชั่น IF นั่นเอง ซึ่งจะต้องให้ผลลัพธ์ออกมาได้แค่ 2 อย่าง คือ TRUE กับ FALSE เท่านั้น!

    if
    โครงสร้างฟังก์ชั่น

    =IF(logical_test,value_if_true,value_if_false
    =IF(เงื่อนไขที่ต้องเช็คว่าจริงหรือเท็จ,ถ้าจริงใช้สูตรนี้,ถ้าเท็จใช้สูตรนี้)

    เช่น ถ้า A1 คือ จำนวนงานที่มีตำหนิ ถ้างานมีตำหนิมากกว่า 10 ชิ้น โดนค่าปรับชิ้นละ 5000 บาท แต่ถ้าไม่เกิน 10 ชิ้นจะโดนปรับชิ้นละ 1000 บาท

    ถ้าเราต้องการคำนวณค่าปรับใน A2 จะเขียนสูตรได้ว่า =IF(A1>10,5000*A1,1000*A1)
    ซึ่งแปลได้ว่า ถ้า A1 > 10 ชิ้น (เงื่อนไข) ค่าปรับคือ 5000*A1 (กรณีจริง) แต่ถ้าไม่เกิน ค่าปรับคือ 1000*A1 (กรณีเท็จ) นั่นเอง

    หมายเหตุ : เงื่อนไขอาจมีได้หลายเงื่อนไข ซึ่งเอามาผสมรวมกันได้ด้วยฟังก์ชั่นทางตรรกะ เช่น AND, OR, NOT จนสุดท้ายเหลือออกมาแค่ TRUE/FALSE

    หมายเหตุ 2 : เนื่องจาก IF แยกได้แค่ 2 กิ่ง ถ้าอยากให้ผลลัพธ์มีได้มากกว่า 2 กิ่งให้ใช้ IF ซ้อนกันหลายๆ ชั้น

    CHOOSE

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

    choose

    ที่ผมเปรียบเทียบ CHOOSE เป็นเหมือนลิฟต์ เพราะว่า CHOOSE นั้นเรา จะต้องระบุตัวเลข ให้มันก่อน ว่า จะให้มันทำงานด้วยสูตรลำดับที่เท่าไหร่? (มีได้สูงสุด 254 สูตร!!)

    โครงสร้างฟังก์ชั่น

    =CHOOSE(index_num,value1,value2,value3,…,value254)
    =CHOOSE(เลขลำดับสูตรที่จะให้ใช้,สูตรที่1,สูตรที่2,สูตรที่3,…,สูตรที่254)

    เช่น =CHOOSE(3,A1+2,A1*2,A1/2,A1^2) จะได้ผลลัพธ์ว่า A1/2
    เพราะว่าเราเลือกสูตร ลำดับที่3 จากสูตรทั้งหมดที่มีให้เลือกดังนี

    1. A1+2
    2. A1*2
    3. A1/2
    4. A1^2

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

    VLOOKUP

    ตัวสุดท้ายที่จะพูดถึงคือฟังก์ชั่นสุดเจ๋งอย่าง VLOOKUP ซึ่งจริงๆ แล้วมีการทำงานถึง 2 โหมดด้วยกัน นั่นคือ VLOOKUP แบบ Approximate Match และ แบบ Exact Match

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

    ถ้าจะให้เปรียบเทียบ VLOOKUP แบบ Exact Match ผมคิดว่าเราสามารถเปรียบเทียบกับเวลาที่เรากำลังมองไปที่เมนูของร้านกาแฟได้เลยครับ (ตัวอย่างร้านกาแฟนี้ ผมได้แนวคิดมาจากเว็บ http://www.excelcampus.com/functions/excel-vlookup-explained/ ครับ อธิบายได้เห็นภาพมาก)

    vlookup3

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

    โครงสร้างฟังก์ชั่น

    =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
    =VLOOKUP(ข้อมูลที่สนใจ,ตารางอ้างอิง,ลำดับคอลัมน์ของผลลัพธ์ที่ต้องการ, อันสุดท้าย ใส่ 0 เพื่อบอกว่าใช้โหมด Exact Match)

    ถ้าเป็นตัวอย่างกาแฟ สมมติว่าผมต้องการกิน Cappuccino (lookup_value) ผมก็จะมองไปที่เมนู (table_array) แล้วกวาดสายตาจากบนลงล่างจนเจอกับ Cappuccino…

    จากนั้นก็จะกวาดสายไปมองไปทางขวา สมมติผมต้องการแบบเย็น ผมก็ต้องมองไปที่คอลัมน์ลำดับที่ 3 (col_index_num) แล้วสุดท้ายก็จะได้ราคา นั่นคือ 65 บาท กลับมานั่นเอง

    ซึ่งจะเขียนเป็นสูตรเต็มๆ ได้ว่า

    =VLOOKUP(“Cappuccino”,เมนูกาแฟ,3,0) ซึ่งจะได้ผลลัพธ์คือ 65 กลับมาครับ

    หมายเหตุ : ผลลัพธ์ของ VLOOKUP สามารถเป็นค่าผลลัพธ์ได้อย่างเดียว ไม่สามารถใส่เป็นสูตรคำนวณเช่นเดียวกับ IF หรือ CHOOSE ได้

    สรุปเปรียบเทียบฟังก์ชั่นแต่ละตัว

    if-vlookup-compare - Excel2

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

    แต่ถ้ามีหลายทาง ก็ให้ดูว่าผลลัพธ์เป็นค่าธรรมดาหรือเป็นสูตรคำนวณ ถ้าเป็นสูตรคำนวณก็ต้องคิดเอาว่าจะถนัดใช้ IF ซ้อน IF หรือจะใช้ CHOOSE ดีกว่า

    แต่ถ้ามีหลายทางแล้วผลลัพธ์เป็นค่าธรรมดาๆ ไม่ใช่สูตร ก็ใช้ VLOOKUP ได้เลยครับ!!

    สำหรับคนที่อยากศึกษาต่อเพิ่มเติม ก็สามารถค้นหาคำว่า IF, CHOOSE, VLOOKUP ในเว็บผมได้เลยครับ มีให้อ่านอีกเยอะครับ ^^

  • Logic Function สิ่งสำคัญที่คุณต้องเชี่ยวชาญให้ได้

    Logic Function สิ่งสำคัญที่คุณต้องเชี่ยวชาญให้ได้

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


    ในส่วนนี้จะปูพื้นฐานฟังก์ชั่นที่จำเป็นมากๆ ต่อการใช้งานส่วนใหญ่ในชีวิตจริง ทั้งใช้ในการเขียนสูตรปกติ และใช้ในเครื่องมืออื่นๆ ของ Excel เช่น Conditional Formatting, Advanced Filter เป็นต้น

    ฟังก์ชั่นที่ใช้ในการคำนวณสรุปผล

    การ “สรุปผล” จากข้อมูลจำนวนมาก สามารถทำได้หลากหลายวิธี เช่น หาผลรวม (SUM) , หาค่ามากที่สุด (MAX), ค่าน้อยที่สุด (MIN), หาค่ากลางของข้อมูล ซึ่งมีหลายประเภท เช่น  ค่าเฉลี่ย(AVERAGE) ฐานนิยม (MODE)  มัธยฐาน(MEDIAN), หาค่าการกระจายตัวของข้อมูล ซึ่งก็มีหลายประเภทอีกเช่นกัน เช่น ค่าพิสัย, ค่าเบี่ยงเบนมาตรฐาน

    การสรุปผลแบบ Basic

    table1-fix

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

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

    • Mean (ค่าเฉลี่ยเลขคณิต)=AVERAGE
      • เป็นค่ากลางที่นิยมที่สุด คำนวณโดยเอาข้อมูลทุกค่าบวกกันแล้วหารด้วยจำนวนข้อมูล
        เช่น = AVERAGE(A1:A5) มันจะเอา (A1+A2+A3+A4+A5)/5
      • ถ้ามีค่ามากหรือน้อยผิดปกติอาจจะดึงค่า MEAN ไปในทิศทางนั้นๆ อาจให้ผลไม่ดีได้
    • Mode (ฐานนิยม) = MODE
      • เป็นการวัดค่ากลาง โดยจะแสดงข้อมูลที่เกิดขึ้นบ่อยที่สุด (มีความถี่สูงสุด)
    • Median (มัธยฐาน)= MEDIAN
      • เป็นการวัดค่ากลาง โดยจะนำค่ามากเรียงกันจากน้อยไปมาก แล้วดูตำแหน่งตรงกลาง
      • สามารถใช้ได้แม้กับข้อมูลที่มีค่าที่มากหรือน้อยผิดปกติ เพราะค่าเหล่านั้นจะไม่ส่งผลใดๆ ต่อการคำนวณเนื่องจากจะถูกเรียงลำดับอยู่ที่หัวและท้าย
      • มีฟังก์ชั่นคล้ายๆ กัน คือ QUARTILE (ควอไทล์) และ PERCENTILE (เปอร์เซ็นต์ไทล์) ซึ่ง Concept คล้ายกัน แต่จะแบ่งช่วงข้อมูลต่างกัน

    การหาค่าการกระจายของข้อมูล

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

    เช่น เรามีกลุ่มข้อมูล 2 กลุ่ม มีค่าเฉลี่ยเท่ากัน คือ 70 แต่กลุ่มแรกมีการกระจายตัวน้อย (ข้อมูลมีการเกาะกลุ่มอยู่ที่ใกล้ๆ 70) อีกกลุ่มมีการกระจายตัวมาก เช่น อาจกระจาย 40-100 เลยก็ได้

    การวัดการกระจายตัวที่นิยม มีอยู่ 3 แบบ คือ

    • Range (พิสัย) คือการหาผลต่างของค่าสูงสุดและค่าต่ำสุดของข้อมูลนั้น
      • ดังนั้นสามารถใช้สูตร MAX มาลบด้วย MIN ได้ เช่น =MAX(A1:A10)-MIN(A1:A10)
      • ข้อมูลที่ได้จะค่อนข้างหยาบ เพราะใช้ข้อมูลแค่ 2 ตัวคือ ค่าสูงสุดและต่ำสุดเท่านั้น
    • Standard Deviation (ส่วนเบี่ยงเบนมาตรฐาน) เป็นการวัดการกระจายที่มีความนิยมมากที่สุด หลักการคล้ายๆ การหาค่าเฉลี่ยของระยะห่างระหว่างข้อมูลแต่ละตัวกับค่าเฉลี่ยเลขคณิต
      • STDEV หรือ S ใช้กับข้อมูลที่เป็นกลุ่มตัวอย่าง (S=Sample) และ
      • STDEVP หรือ P เอาไว้ใช้กับข้อมูลทั้งหมด (P=Populations)

    (more…)

  • เทคนิคลับสุดยอด VLOOKUP ข้อมูลเร็วขึ้น 100 เท่า! (ขึ้นไป)

    เทคนิคลับสุดยอด VLOOKUP ข้อมูลเร็วขึ้น 100 เท่า! (ขึ้นไป)

    Edit : ผมมีเขียนบทความใหม่ไว้ ซึ่งเนื้อหาครอบคลุมและมีเครื่องมือที่อัปเดทกว่านี้ หากสนใจไปอ่านได้ที่นี่ ครับ ^^

    เพื่อนๆ เคยทำงานที่ต้อง Lookup ข้อมูลเยอะๆ ใน Excel รึเปล่าครับ? หลายๆคนอาจใช้เทคนิคปรับโหมดการคำนวณจาก Automatic เป็น Manual (ที่ต้องกด F9 เพื่อคำนวณ) เพื่อให้มันคำนวณทีเดียวเมื่อเราเขียนสูตรทุกอย่างพร้อมแล้ว ที่ต้องทำอย่างนั้นเพราะว่ามันนานมากกกกก

    สิ่งที่ทำให้มันช้ามักจะเกิดขึ้นกับสูตรประเภทที่ต้อง Lookup ข้อมูล โดยเฉพาะอย่างยิ่งถ้ามีข้อมูลที่ต้อง Lookup เป็นหมื่นหรือเป็นแสนแถว บางทีรอหลายชั่วโมงก็ยังไม่เสร็จ และถ้าสังเกตให้ดี การ Lookup ที่ช้าจะเป็นการ Lookup ประเภท Exact Match เท่านั้น (ต้องเจอผลลัพธ์เป๊ะๆ) ซึ่งจะไม่เกิดอาการช้ากับการ Lookup แบบ Approximate Match ซึ่งจะเร็วกว่ามาก

    วันนี้ผมจะมาแนะนำเทคนิคที่จะทำให้ VLOOKUP แบบหาเป๊ะๆ แต่ไม่ต้องรอนานอย่างที่เคย ทำยังไงมาดูกันครับ 100xVLOOKUP

    ทบทวนสูตร VLOOKUP กันซักนิด

    =VLOOKUP(lookup_value, table_array, col_index_num,[range_lookup])

    =VLOOKUP(ค้นหาคำนี้,จากคอลัมน์แรกในตารางนี้,เมื่อเจอแล้วให้เอาค่าในคอลัมน์ที่ xx ของตารางกลับมา (นับซ้ายไปขวา), [ใช้โหมด Lookup แบบ Approximate Match หรือ Exact Match])

    VLOOKUP มี 2 โหมดด้วยกัน เรามาดูกันว่าทั้งสองแบบต่างกันยังไง?

    • Approximate Match (ตั้งค่า Range Lookup เป็น TRUE หรือ 1) แปลว่า แม้ไม่เจอค่าที่ต้องการหาเป๊ะๆ ก็ยังส่งค่าบางอย่างกลับมาได้ โดยมีหลักการดังนี้
      • ทำงานเร็วมาก เพราะทำงานด้วยการค้นหาแบบ Binary Search (ลองอ่านข้างล่างสุดดูวิธีการทำงานได้ แต่ไม่เข้าใจไม่เป็นไรครับ)
      • มีข้อจำกัดคือ ข้อมูล Reference (คอลัมน์แรกของ table_array) ต้องเรียงน้อยไปมากเท่านั้น (ไม่งั้นผลลัพธ์จะมั่ว)
      • ถ้าหากข้อมูลเรียงจากน้อยไปมากแล้ว : มันจะวิ่งหาในคอลัมน์แรกใน table_array ลงจากบนลงล่าง จนเจอข้อมูลที่มากกว่า lookup_value แล้วเด้งกลับขึ้นข้างบน 1 ช่อง
      • จากนั้นจะเอาค่าในคอลัมน์ที่ col_index_num กลับมา
    • Exact Match (ตั้งค่า Range Lookup เป็น FALSE หรือ 0) แปลว่า ต้องเจอค่า/คำที่ต้องการเป๊ะๆ จึงจะส่งค่ากลับมา ถ้าไม่เจอจะ Error เลย
      • ทำงานช้า(มาก) เพราะทำงานด้วยการค้นหาแบบ Linear Search (ไม่เข้าใจไม่เป็นไรครับ)
      • ข้อดีคือ ข้อมูล Reference (คอลัมน์แรกของ table_array) ไม่จำเป็นต้องเรียงจากน้อยไปมาก
      • วิ่งหาในคอลัมน์แรกใน table_array ลงจากบนลงล่าง จนเจอข้อมูลเป๊ะๆ ถ้าไม่เจอขึ้น Error

    ซึ่งบ่อยครั้งเราต้องการได้ผลลัพธ์จากการ Lookup แบบเป๊ะๆ มากกว่า (คือถ้าไม่เจอก็ให้ขึ้น Error ไปเลย) เราจึงมักจะเลือกที่จะใช้งาน VLOOKUP แบบ Exact Match เสมอๆ เพราะถ้าเลือกใช้แบบ Approximate Match ข้อมูลก็จะมั่วเลย เพราะไปดึงข้อมูลจากบรรทัดอื่นมาแสดง

    เคล็ดลับอยู่ตรงนี้แหละครับ!! เราจะไม่ใช้สูตร Exact Match เพราะมันช้า เราจะใช้ VLOOKUP แบบ Approximate Match มาช่วยแทนเพราะเร็วกว่า

    แต่จะกำจัดผลลัพธ์ที่ไม่ต้องการออกไปได้ยังไง มาดูกัน

    Concept ของเทคนิคลับ ทำยังไงให้เร็ว!?

    ก่อนอื่น เราต้องเรียงคอลัมน์แรกในตารางอ้างอิงจากน้อยไปมากก่อน จึงจะใช้สูตร VLOOKUP แบบ TRUE ได้

    จากนั้นเราก็สามารถ แต่ใส่เงื่อนไขเพื่อเช็คว่า “ถ้าเอาคอลัมน์แรกกลับมา มันจะเท่ากับ Lookup_value ที่ใช้คนหารึเปล่า?”

    • ถ้าเท่ากันแสดงว่าใช้ได้ ให้เอาค่าจากคอลัมน์ที่ต้องการ ( คอลัมน์ที่ 3 )กลับมา
    • ถ้าไม่เท่ากัน แสดงว่าใช้ไม่ได้ ก็ให้ขึ้น Error ไป

    เงื่อนไขที่เช็ค =VLOOKUP(lookup_value, table_array, 1, TRUE)=lookup_value
    (ซึ่งจะให้ผลลัพธ์เป็น TRUE/FALSE)

    • กรณีใช้ได้ (จริง) : =VLOOKUP(lookup_value, table_array, 3, TRUE)
    • กรณีใช้ไม่ได้ (เท็จ) : ให้ขึ้นข้อความ Error เช่น = NA() หรือจะขึ้น =”Error” หรือ =”ไม่เจอจ้า” ก็ได้ครับ…
    VLOOKUP แบบเร็ว 100 เท่า

    สรุปวิธีเขียนสูตร VLOOKUP ให้เร็วขึ้น 100 เท่า!! (แต่ต้องเรียงคอลัมน์แรกของ table_array ก่อน)

    =IF(VLOOKUP(lookup_value, table_array, 1, TRUE)=lookup_value,
    VLOOKUP(lookup_value, table_array, col_index_num, TRUE),NA() )

    เพียงแค่นี้ สูตร VLOOKUP คุณก็จะเร็วขึ้นสุดๆ จนเทียบกับของเดิมไม่ได้เลย Enjoy VLOOOKUP นะครับ!!

  • การเขียน IF ที่ให้ผล TRUE/FALSE โดยไม่จำเป็น

    ถ้าจะเขียน IF ให้ออกมาเป็น TRUE/FALSE

    เช่น =IF(A>B,TRUE,FALSE)

    ไม่ต้องใช้ IF ก็ได้

    ให้ใส่แค่เงื่อนไขพอ เช่น =A>B แค่นี้พอครับ

  • สอนทำเกม Sudoku ( Excel Sudoku)

    สอนทำเกม Sudoku ( Excel Sudoku)

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

    กติกาเบื้องต้นเกม Sudoku

    excel sudoku
    • มีตาราง 9 x 9
    • ใส่ได้แค่เลข 1-9
    • ใส่เลขห้ามซ้ำกัน
      • ในแต่ละแถว
      • ในแต่ละคอลัมน์
      • ในแต่ละกรอบตาราง Block ใหญ่ 3 x 3 (เหมือนที่ตีกรอบหนาไว้)
    • จะมีเลขใส่มาให้เบื้องต้นบางส่วนก่อน ถ้าเกมง่ายๆก็จะใส่มาให้เยอะหน่อย ถ้าเกมยาก ก็จะใส่มาให้น้อย
    • เราจะเล่นเกม โดยมีเป้าหมายคือ ใส่เลขต่อให้ครบทุกช่อง โดยไม่ทำผิดกติกาข้างบน ถ้าทำได้ก็ถือว่าจบเกม

    วิธีสร้างตัวตรวจกติกาเกม Sudoku

    โหลดไฟล์ตัวอย่างได้ที่นี่

    • ไฟล์ก่อนกรอกเลข (แสดงสูตรให้เห็น) sudoku-inwexcel(.xlsx)
    • ไฟล์หลังกรอกเลข (ซ่อนสูตร + lock cell พร้อมให้ user เล่น) sudoku-inwexcel2(.xlsx)

    Concept : เช็คว่ามีการทำผิดกติกาหรือไม่ ไล่ตามกติกาที่เขียนข้างบน แล้วนับว่ามีการทำผิดกติกากี่อัน/กี่ครั้ง จากนั้นนำผลการนับที่ได้มารวมกัน ถ้าสรุปแล้วมากกว่า 0 แสดงว่ามีการทำผิดกติกา

    สร้างตารางขนาด 9×9

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

    สอนทำเกม Sudoku ( Excel Sudoku) 40

    กำหนดเงื่อนไข ว่าให้ใส่ได้แค่เลข 1-9 เท่านั้น

    เราจะทำโดยใช้ Data Validation โดยเลือกพื้นที่ตาราง 9×9 แล้วไปที่ Ribbon Data=> Data Validation =>Data Validation…

    ใน Allow ให้เลือก Whole Number (จำนวนเต็ม) Between Minimum=1 Maximum=9
    sudoku-2

    สร้างตัวตรวจสอบว่าแต่ละแถวมีเลข 1-9 กี่ตัว

    ใช้ COUNTIF มาช่วย =COUNTIF(แต่ละแถว,แต่ละเลข1-9)

    ในที่นี้ผมเขียนว่า 

    =COUNTIF($C3:$K3,M$2)

    เพราะ C3:K3 ผมคือแถวแรก ส่วน M2 ผมคือเลข 1

    พอ copy ไปยังช่องอื่น สูตรจะใช้ได้โดยอันโนมัติ เพราะผม Lock คอลัมน์ไว้ที่คอลัมน์ C กับ K (เพื่อไล่ไปแต่ละแถวลงไปข้างล่าง) ส่วนช่อง M2 ล๊อคแถวไว้ เวลา Copy ไปจะได้ไล่ไปยังเลข 2-9 ต่อไปได้

    สอนทำเกม Sudoku ( Excel Sudoku) 41

    สร้างตัวตรวจสอบว่าแต่ละคอลัมน์มีเลข 1-9 กี่ตัว

    ใช้ COUNTIF มาช่วย =COUNTIF(แต่ละคอลัมน์,แต่ละเลข1-9)

    ในที่นี้ผมเขียนว่า

    =COUNTIF(C$3:C$11,$B13)

    เพราะ C3:C11 ผมคือคอลัมน์แรก ส่วน B13 ผมคือเลข 1

    พอ copy ไปยังช่องอื่น สูตรจะใช้ได้โดยอันโนมัติ เพราะผม Lockแถวไว้ที่แถว 3 กับ 11 (เพื่อไล่ไปแต่ละคอลัมน์ทางขวา) ส่วนช่อง B13 ล๊อคคอลัมน์ไว้ เวลา Copy ไปจะได้ไล่ไปยังเลข 2-9 ต่อไปได้

    สอนทำเกม Sudoku ( Excel Sudoku) 42

    สร้างตารางตรวจเช็คการใส่ข้อมูลซ้ำ ในแต่ละ Zone 3×3 อันใหญ่ 

    โดยใช้เทคนิค คล้ายๆ ที่เขียนไว้ใน http://www.thepexcel.com/prevent-duplicate-data-entry/
    แต่ว่าเราจะไม่ใช้ Data Validation แล้ว เพราะเราได้ใช้ไปแล้วในชั้นตอนแรก และผู้เล่นอาจต้องการลองใส่เลขเข้าไปก่อน ถ้าใช้ Data Validation จะไม่ยอมให้ใส่เลย อันนั้นแรงเกินไป ดังนั้นเราจะเขียนทดไว้อีกทีนึงแทนตามรูป (ขวาล่าง)

    โดยที่เราต้องเขียนสูตร 9 ครั้ง แยกแต่ละ Block ใหญ่ที่เป็นขนาด 3×3

    สอนทำเกม Sudoku ( Excel Sudoku) 43

    ใน Block แรก (M13) เขียนว่า =COUNTIF($C$3:$E$5,C3) แล้ว copy paste แค่ zone ตัวเอง คือ ช่อง M13 ถึง O15

    สอนทำเกม Sudoku ( Excel Sudoku) 44

    ใน Block ถัดไปทางขวา (P13) เขียนว่า 

    =COUNTIF($F$3:$H$5,F3)

     แล้ว copy paste แค่ zone ตัวเอง คือ ช่อง P13 ถึง R15

    ทำแบบนี้จบครบ 9 Block

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

    โดยใช้ COUNTIF มานับว่าช่วงที่เราหามีเลขอะไรมากกว่า 1 หรือไม่นั่นเอง โดยเขียนว่า

    =COUNTIF(C13:K21,">1")
    =COUNTIF(M3:U11,">1")
    =COUNTIF(M13:U21,">1")

    จากนั้นนำผลนับที่ได้มากรวมกัน ถ้ามากกว่า 0 แปลว่ามีการทำผิดกติกาครับ

    สอนทำเกม Sudoku ( Excel Sudoku) 45

    ทำการ Link ไปแสดงผลด้านบน พร้อมใส่ Conditional Format ให้สวยงาม

    เช่น ถ้า 3 ช่องนั้น SUM ได้มากกว่า 0 ให้เขียนว่า ไม่ ok เป็นต้น
    ในที่นี้ผมเขียนว่า 

    =IF(M23>0,"ไม่ok","ok") 

    โดย M23 ผมเป็นช่องที่เช็คผลรวมความผิดปกติ

    สอนทำเกม Sudoku ( Excel Sudoku) 46

    สุดท้ายก็เช็คว่ากรอกครบทุกช่องแล้วหรือยัง ถ้าครบ 81 ช่องแล้วและยัง Ok อยู่ก็ถือว่าจบเกมครับ ^^
    ในที่นี้ ผมเขียนเพิ่มไปว่า 

    =IF(M23>0,"ไม่ok",IF(COUNT(C3:K11)=9*9,"จบเกม","ok"))

    การเตรียมการก่อนให้ผู้เล่นเริ่มเล่นเกมจริง

    ทีนี้ ก็สามารถใส่เลขเริ่มต้น ตามแต่ละ Puzzle ที่ได้มาได้เลย

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

    เทคนิคการเลือกพื้นที่เฉพาะที่เป็นเลขเริ่มต้นของเรา
    ให้เลือกพื้นที่ทั้งหมดในตาราง => Ctrl+G => Special => Constant => มันจะเลือกเฉพาะที่มีการกรอกข้อมูลเริ่มต้นไปแล้ว

    จากนั้นค่อยใส่สี หรืออะไรให้ต่างจากปกติ แล้วค่อยซ่อนแถวและคอลัมน์ที่เป็นตัวทดของเราไปซะ เพื่อความสะอาดตาด้วยการเลือก แถว หรือ column แล้วคลิ๊กขวา => Hide

    สอนทำเกม Sudoku ( Excel Sudoku) 47

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

    เลือกพื้นที่ทั้งหมดในตาราง => Ribbon Home =>Cells =>Format =>ติ๊กเอา Lock Cell ออก (เพื่อปลดการ Lock Cell ทั้งหมดก่อน)

    เลือกพื้นที่เฉพาะที่เป็นเลขเริ่มต้นของเรา มันจะเลือกเฉพาะที่มีการกรอกข้อมูลเริ่มต้นไปแล้ว จากนั้นกด

    Ribbon Home =>Cells =>Format => ติ๊กให้ Lock Cell ทำงาน (เพื่อให้ Lock Cell เฉพาะที่มีการใส่เลขลงไปแล้ว) จากนั้นกด Ribbon Review => Protect Sheet

    ถ้าติ๊กเอา Select Locked Cell ออก จะเป็นการห้ามให้ผู้เล่นมาเลือกช่องที่กรอกไว้แล้ว
    จะใส่ Password หรือไม่ก็ได้ แล้วกด OK

    เท่านี้ เวลาเล่น ก็จะไม่สามารถไม่ต้องกังวลว่าจะเผลอลบช่องที่เป็นตัวเลขเริ่มต้นแล้ว และเราก็สามารถเอาไปให้คนอื่นเล่นเกม Sudoku ของเราได้แล้วครับ

    ที่นี้ถ้าใครอยากได้ตัวโปรแกรมแก้ปัญหา Sudoku แบบบอกเฉลยได้เลย ผมจะแนะนำ Link ให้ครับ

    Sudoku Solver

    แบบที่ 1 : แก้โดยไม่ใช้ VBA แต่ใช้ Iteration แทน

    ดาวน์โหลดไฟล์

    https://templates.office.com/en-us/Sudoku-puzzle-solver-TM10080972

    สอนวิธีเขียน

    แบบที่ 2 แก้โดยใช้ VBA

    ลอง search Google ว่า Excel Sudoku Solver VBA ดูครับ

  • เจาะลึกสูตร IF ใน Excel

    เจาะลึกสูตร IF ใน Excel

    สูตร IF คืออะไร?

    สูตร IF (หรือจะเรียกให้ถูกคือฟังก์ชัน IF) คือ ฟังก์ชั่น (Function) ซึ่งทำหน้าที่เลือกใช้สูตรคำนวณได้ 2 แบบ ขึ้นอยู่กับเงื่อนไขที่เราระบุลงไปว่าจริงหรือเท็จ?

    ถ้าเงื่อนไขเป็นจริง (TRUE) จะทำการคำนวณด้วยสูตรแบบหนึ่ง ถ้าเงื่อนไขเป็นเท็จ (FALSE) จะคำนวณด้วยสูตรอีกแบบหนึ่ง

    ** ผมใช้คำว่า “สูตร” เพราะใส่สูตรยาวๆ ซับซ้อนแค่ไหนก็ได้

    ผมแนะนำให้มองว่า IF 1 ตัว สามารถแตกกิ่งก้านสาขาการตัดสินใจ (Decision Tree) ออกไปได้ 2 กิ่ง
    คือ ผลลัพธ์จากกรณีเงื่อนไขเป็น TRUE และ ผลลัพธ์จากกรณีเงื่อนไขเป็น FALSE

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

    ปล. ผมมีเขียนบทความใหม่เกี่ยวกับ IF รวมถึงตัวที่ใกล้เคียงกับมัน เช่น IFS และ SWITCH เอาไว้ด้วย สามารถไปอ่านได้ที่นี่

    วิธีเขียนสูตร

    =IF(logical_test,[value_if_true],[value_if_false])
    =IF(เงื่อนไขที่เราโยนเข้าไปให้ทดสอบ, [ถ้าจริงจะทำอันนี้], [ถ้าเท็จจะทำอันนี้])

    เช่น หากเทียบกับภาษาพูด จะได้ว่า

    • ถ้า ฉันสอบตก ฉันจะเลี้ยงข้าว ไม่งั้น เธอเป็นคนเลี้ยงข้าว
      • ฉันสอบตก = เงื่อนไข
      • ฉันเลี้ยงข้าว = การกระทำหากเงื่อนไขเป็นจริง
      • เธอเป็นคนเลี้ยงข้าว = การกระทำหากเงื่อนไขเป็นเท็จ

    ซึ่งเงื่อนไขที่เราโยนเข้าไปให้ทดสอบ นั้นจะต้องมี  ตัวเปรียบเทียบ (COMPARISON OPERATOR) อยู่ด้วย เพื่อให้ค่าออกมาเป็นจริง (TRUE)  หรือเท็จ (FALSE) เช่น = เท่ากับ, < น้อยกว่า, > มากกว่า, <> ไม่เท่ากับ, >= มากกว่าหรือเท่ากับ, <= น้อยกว่าหรือเท่ากับ

    ตัวอย่าง 1

    สูตร IF

    หากช่อง B4 เราเขียนว่า

    =IF(B2>B3,"ของไม่พอ","ของพอ")

    สิ่งที่มันจะทำคือดูว่าค่าใน B2 มากกว่า B3 หรือไม่?
    ซึ่งในที่นี้มากกว่า แปลว่า Logical Test (เงื่อนไข) ได้ผลลัพธ์เป็น TRUE
    ซึ่งเปรียบได้กับ =IF(TRUE,”ของไม่พอ”,”ของพอ”)

    และเมื่อ Logical Test เป็น TRUE ก็จะแสดงผลลัพธ์จาก value_if_true ซึ่งก็คือ “ของไม่พอ” นั่นเองครับ

    หากมีหลายเงื่อนไขจะทำอย่างไร?

    โดยหากมีหลายเงื่อนไข เราสามารถเอาแต่ละเงื่อนไขมาเชื่อมกับฟังก์ชั่นทางตรรกศาสตร์ ได้อีก เช่น

    • AND(เงื่อนไข1,เงื่อนไข2,เงื่อนไข3,…) => และ : เงื่อนไขต้องเป็นจริงทุกอัน ผลลัพธ์ถึงจะออกมาเป็นจริง
    • OR(เงื่อนไข1,เงื่อนไข2,เงื่อนไข3,…) => หรือ : หากเงื่อนไขอันใดอันหนึ่งจริงถือว่าผลลัพธ์จริง

    ตัวอย่าง 2

    สูตร IF
    สูตร IF

    การเขียน IF ซ้อน IF

    เราสามารถเขียน IF ซ้อนกันไปเรื่อยๆ ได้ (จริงๆแล้วจะเอาฟังก์ชั่นอื่นมาซ้อนด้วยก็ยังได้)
    โดย IF แต่ละตัวก็จะมีการเช็คเงื่อนไขของตัวเอง และทำค่าจริง/เท็จ ของตัวเองแล้วแต่ผลลัพธ์ที่ประมวลได้ เช่น

    =IF(เงื่อนไข 1, IF( เงื่อนไข2,จริง2,เท็จ2),IF(เงื่อนไข3,จริง3,เท็จ3))

    เทคนิคการเขียน IF ซ้อนกันหลายตัว

    • จำนวนวงเล็บเปิดต้องเท่ากับวงเล็บปิด (ในที่นี้มีอย่างละ 4 อันเท่ากัน)
    • ในแต่ละกิ่งที่ จริง /เท็จ เราสามารถเลือกที่จะใส่ IF ซ้อนลงไปอีกหรือไม่ก็ได้ ลองดูได้จากตัวอย่างที่จะเห็นต่อไป
    Nested IF Excel

    ตัวอย่าง 3

    หากเราต้องการจะจัดเกรดจากคะแนนดิบของนักเรียน โดยมีเกณฑ์ดังนี้

    • คะแนน <50 : F
    • 50 <= คะแนน<60 : D
    • 60 <= คะแนน<70  : C
    • 70 <= คะแนน<80  : B
    • คะแนน >= 80 : A

    สมมติคะแนนดิบอยู่ในช่อง A2 และเราจะใส่เกรดในช่อง B2 / ในช่อง B2 เราต้องเขียนดังนี้

    =IF(A2<50,"F",IF(A2<60,"D",IF(A2<70,"C",IF(A2<80,"B","A"))))

    อธิบายแนวคิด

    ให้ใส่เงื่อนไขทีละ Step อันแรกเช็คว่าน้อยกว่า 50 หรือไม่? ถ้าน้อยกว่าให้แสดงเกรด F ถ้าไม่น้อยกว่า ต้องเช็คต่อว่า < 60 หรือไม่?… ทำแบบนี้ไปเรื่อยๆ

    Nested IF function Excel

    สรุปแบบ Slide

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