Month: November 2013

  • แนะนำฟังก์ชั่น DATEDIF

    แนะนำฟังก์ชั่น DATEDIF

    DATEDIF ไว้ใช้ทำอะไร?

    DATEDIF เป็นฟังก์ชั่นเกี่ยวกับวันที่ ใช้หาว่าวันสองวันที่กำหนด ห่างกันกี่วัน กี่เดือน หรือ กี่ปี (เราเลือกได้)

    วิธีการใช้ฟังก์ชั่น

    DATEDIF( start_date, end_date, interval )
    • DATEDIF( วันเริ่ม, วันจบ, รูปแบบการวัดช่วงเวลา )
    • โดย interval (รูปแบบการวัดช่วงเวลา) คือ
    IntervalExplanation
    Yปี (แบบครบปี)
    Mเดือน (แบบครบเดือน)
    Dวัน
    MDวัน (ไม่สนใจ เดือน ปี)
    YMเดือน (ไม่สนใจวัน ปี)
    YDวัน (ไม่สนใจปี)

    อย่างไรก็ตาม ฟังก์ชันนี้มีความแปลกและพฤติกรรมประหลาด ให้ไปอ่านรายละเอียดได้ในบทความใหม่ของผมอันนี้ครับ

    อธิบายการทำงาน DATEDIF ใน Excel และแนวทางแก้ไขให้ได้ผลลัพธ์ดั่งใจ
  • การทำงานเกี่ยวกับวันและเวลา (Date & Time) ใน Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

     

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

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

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

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

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

     

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

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

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

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

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

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

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

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

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

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

    002

     

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

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

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

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

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

    explain-timedif

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

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

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

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

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

    explain-timedif2

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

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

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

  • เจาะลึกฟังก์ชั่น OFFSET

    เจาะลึกฟังก์ชั่น OFFSET

    ฟังก์ชั่น OFFSET เอาไว้แสดงผลลัพธ์เป็นการอ้างอิงช่อง/ช่วง (ผลเป็น Reference) โดยใช้วิธีเลื่อนตำแหน่งจากช่องที่เราอ้างอิงไปในทิศทางต่างๆ เช่น เราสามารถอ้างอิงให้เริ่มที่ช่อง G3 แล้วให้เลื่อนลงล่าง 2 แถว ไปทางซ้ายอีก 4 แถว ซึ่งจะได้ผลลัพธ์เป็นค่าในช่อง C5 เป็นต้น

    พูดง่ายๆ ว่า OFFSET มีความสามารถในการทำ Dynamic Range นั่นเองครับ

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

    =OFFSET(reference,rows,cols,[height],[width])

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

    = OFFSET(จุดอ้างอิง, เลื่อนลงล่างกี่แถว, เลื่อนขวากี่แถว, [ความสูงของ Range ผลลัพธ์], [ความกว้างของ Range ผลลัพธ์])

    ประเภทของ Argument ที่ใส่ลงไป

    = OFFSET(Cell/Range Reference, ตัวเลข, ตัวเลข, ตัวเลข, ตัวเลข)

    ประเภทของผลลัพธ์ที่ได้จาก Function

    ได้เป็น Cell/Range Reference

    ประเด็นที่น่าสนใจ

    • จุดอ้างอิงจะเป็น Cell หรือ Range ก็ได้
    • Rows ถ้าใส่เลขบวกจะเลื่อนลง ถ้าใส่ติดลบจะเลื่อนขึ้น
    • Columns ถ้าใส่เลขบวกจะเลื่อนไปขวา ถ้าใส่ติดลบจะเลื่อนไปทางซ้าย
    • ถ้าไม่ใส่ Height หรือ width จะถือว่าให้สูงหรือกว้างเท่าช่องอ้างอิงต้นฉบับ
    • Height และ Width ติดลบไม่ได้ เป็น 0 ไม่ได้

    เช่น

    OFFSET
    • =OFFSET(C3,2,3,1,1)
      • จะส่งผลกลับมาเป็นการอ้างอิงช่อง F5 เพราะเลื่อนจาก C3 ไปล่าง 2 ช่อง ขวา 3 ช่อง
    • =SUM(OFFSET(C3:E5,-1,0,3,3))
      • จะทำการ Sum ช่วง C2:E4 เพราะเลื่อนจาก C3:E5 ขึ้นไป 1 ช่อง
    • =OFFSET(C3:E5,0,-3,3,3)
      • จะ Error (#REF!) เพราะการอ้างอิงหลุดกรอบการทำงานของ Worksheet (เลื่อน C ไปซ้าย 3 ช่อง จะเลย A ออกไป)

    ตัวอย่างการประยุกต์ใช้ OFFSET เพื่อสร้าง Dependent Dropdown List

  • เจาะลึกสูตร VLOOKUP ดูจบใช้เป็นเลย

    เจาะลึกสูตร VLOOKUP ดูจบใช้เป็นเลย

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

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

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

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

    VLOOKUP มี 2 โหมดด้วยกัน

    1. Approximate Match (ตั้งค่า Range Lookup เป็น TRUE หรือ 1 หรือ เว้นไว้ไม่กรอก) แปลว่า แม้ไม่เจอค่าที่ต้องการหาเป๊ะๆ ก็ยังส่งค่าบางอย่างกลับมาได้ (แล้วมันส่งค่าอะไรกลับมาเดี๋ยวจะอธิบายอีกที)
    2. Exact Match (ตั้งค่า Range Lookup เป็น FALSE หรือ 0) แปลว่า ต้องเจอค่า/คำที่ต้องการเป๊ะๆ จึงจะส่งค่ากลับมา

    ซึ่งในบทความนี้ผมจะขออธิบายจากตัวที่ง่ายกว่าก่อน นั่นก็คือ VLOOKUP โหมด Exact Match ครับ

    VLOOKUP โหมด Exact Match

    จุดประสงค์หลัก : เหมาะกับการ Map ข้อมูล (ดึงค่าจาก Reference ที่กำหนด)
    มันจะทำงานดังนี้

    Excel VLOOOKUP exact match
    1. หาคำที่ต้องการจากคอลัมน์ซ้ายสุดของตารางที่กำหนด (Table Array) และทำการหาจากบนลงล่าง (Vertical Lookup)
    2. ผลมี 2 กรณี
      1. ถ้าเจอคำที่ต้องการ(ต้องเหมือนเป๊ะๆ) มันจะหยุดที่บรรทัดนั้น
        • ถ้ามีค่าที่ตรงเงื่อนไขหลายค่าในคอลัมน์ที่ค้นหา มันจะยึดที่เจออันแรกสุด (เฉพาะแบบ Exact Match นะ)
      2. ไม่เจอคำที่ต้องการเป๊ะๆ จะแสดงค่า Error กลับมาเป็น #N/A (ย่อมาจาก Not Available แปลว่าหาไม่เจอ)
    3. กรณีเจอคำที่ต้องการเป๊ะๆ จะวิ่งไปทางขวา (ในบรรทัดเดียวกับตัวที่เจอ) ตาม Col Index ที่กำหนด เริ่มนับที่คอลัมน์ซ้ายสุดของ Table_Array เป็น 1, ถัดไปทางขวา =2, 3, 4… เพื่อเอาค่าใน Col Index ที่กำหนดกลับมาแสดงค่า
      • ตัวอย่างในรูป ช่อง G3 เกิดจากการใช้ VLOOKUP ค้นหาทะเบียน นม6666 ว่าอยู่บรรทัดไหน ถ้าเจอให้เอาคอลัมน์ที่ 2 ของบรรทัดนั้นกลับมา (นับเฉพาะขอบเขตตารางที่เลือกไว้ ไม่เกี่ยวว่าคอลัมน์ที่ 2 คือ B) ซึ่งจะได้ค่า BMW

    ทำไมใช้ VLOOKUP แล้วขึ้น #N/A หาไม่เจอ (ทั้งๆ ที่น่าจะเจอ)?

    คุณอาจเคยเจอปัญหา ว่าเห็น lookup_value ในตารางอ้างอิงอยู่เต็มๆ ตา ทำไม VLOOKUP แล้วกลับขึ้นมาว่าเป็น #N/A ซึ่งอาการที่พบบ่อยมีอยู่ 3 แบบ ดังนี้

    ข้อมูลไม่ตรงกันจริงๆ เช่น ติดช่องว่างมา

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

    Data Type ไม่ตรงกัน

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

    vlookup-error

    =VLOOKUP(30,ตารางอ้างอิง,2,0) จะขึ้น #N/A หากว่าในตารางอ้างอิงเป็นข้อมูลประเภท Text

    ซึ่งทางแก้ คือ เราก็ต้องทำให้ Lookup_value กับตารางอ้างอิงเป็นข้อมูลประเภทเดียวกัน เช่น

    • ทำให้ Lookup_value เป็น Text ไปด้วย เช่น =VLOOKUP(“30”,ตารางอ้างอิง,2,0) หรือ
    • จะ ใช้ cell reference ที่มีข้อมูลเป็น Text เลข 30 ก็ได้ เช่น ถ้าเอา Text เลข 30 ไปไว้ใน B8 ถ้าเขียน =VLOOKUP(B8,ตารางอ้างอิง,2,0) ก็จะเจอ เช่นกัน หรือ
    • ทำให้ตารางอ้างอิงเป็นเลขไปเลย ซึ่งทำได้หลายวิธี เช่น ใช้วิธีเอาเลข 1 ไปคูณให้หมด
      คือ copy เลข 1 –> เลือกตารางอ้างอิงคอลัมน์แรก –> Paste Special –> Multiply
    vlookup-error1

    Cell Reference เลื่อน

    vlookup-error2-add

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

    ทางแก้ คือ เราควรทำการ ตรึงตำแหน่งสูตร Cell Reference ในส่วนของ ตาราง Table__array ทุกครั้ง โดยการกดปุ่ม F4 ให้มีเครื่องหมาย $ ขึ้นมาคลุม

    หรือไม่ก็ใช้วิธีตั้งชื่อ Defined Name ก็ได้เช่นกัน อันนั้นมันจะ ตรึงตำแหน่งสูตร Cell Reference ด้วยการใช้ชื่อเอง เราไม่ต้องมานั่ง Lock อีกครั้ง สะดวกมาก

    vlookup-error3

    แล้วยังไงต่อ?

    และนี่ก็จบแล้วสำหรับการใช้ VLOOKUP พื้นฐานแบบ Exact Match ถ้าใครอยากศึกษา VLOOKUP โหมด Approximate Match ต่อไป สามารถคลิ๊กอ่านได้ที่นี่

  • เจาะลึกสูตร 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 นะครับ

  • การแปลงค่าจาก ตัวหนังสือ ตัวเลข

    การแปลงค่าจาก ตัวหนังสือ ตัวเลข

    ทำไมต้องแปลงค่า?

    หากเราใช้ข้อมูลที่มีรูปแบบไม่ตรงตามที่ต้องการ อาจเปิดปัญหาในการใช้สูตร เช่น Lookup ค่าไม่เจอ ก็เป็นไปได้ครับ

    วิธีแปลงค่า จากตัวหนังสือ => ตัวเลข ใน Excel

    วิธีที่ง่าย และได้ผลที่สุด คือ เอาไปคูณ 1 ครับ เช่น

    • ในช่อง A1 มีตัวหนังสือว่า 000540
    • หากจะแปลงไปเป็นตัวเลข และแสดงผลในช่อง B1
    • ในช่อง B1 ให้เขียนว่า =A1*1
    • ซึ่งจะได้ค่าเป็น 540 แบบเป็น Number นั่นเอง

    วิธีแปลงค่า จากตัวเลข => ตัวหนังสือ ใน Excel

    วิธีที่ง่าย และได้ผลที่สุด คือ เอาไป & ช่องว่าง ครับ เช่น

    • ในช่อง A1 มีตัวเลข 540
    • หากจะแปลงไปเป็นตัวหนังสือ และแสดงผลในช่อง B1
    • ในช่อง B1 ให้เขียนว่า =A1&””
    • ซึ่งจะได้ค่าเป็น 540 แต่เป็น Text นั่นเอง

    วิธีตรวจสอบประเภทของข้อมูลใน Excel

    อ่านได้ที่ http://www.thepexcel.com/check-data-type/

  • การขึ้นบรรทัดใหม่ใน Cell เดิม

    การขึ้นบรรทัดใหม่ใน Cell เดิม

    หากคุณใช้โปรแกรม Excel แล้วจะพิมพ์ข้อความให้ขึ้นบรรทัดใหม่ แต่อยู่ในช่องเดิม คุณไม่สามารถกดปุ่ม Enter เฉยๆ เหมือนกับโปรแกรมอื่นๆ ทั่วไปอย่าง Microsoft Word ได้ (หาดกด Enter ใน Excel มันจะเด้งลงไป Cell ถัดไปที่อยู่ข้างล่างแทน !!)

    ใน Microsoft Excel การจะบังคับขึ้นบรรทัดใหม่ได้นั้น ต้องกดปุ่ม Alt+Enter เท่านั้นครับ (กด Alt ค้างไว้ แล้วค่อยกด Enter) เมื่อต้องการจะขึ้นบรรทัดใหม่ตรงไหนให้กดตรงนั้นครับ

  • ประเภทข้อมูล (Data Type) ในโปรแกรม Excel

    ประเภทข้อมูล 4 แบบ

    ใน Excel ไม่ว่าเราจะเขียนสูตร หรือ พิมพ์ข้อมูลลงไปใน Cell …. ผลลัพธ์ค่าที่แท้จริง (Value) สามารถแบ่งออกเป็น 4 ประเภทข้อมูลใหญ่ๆ ด้วยกัน คือ

    ประเภทข้อมูล
    1. Number ตัวเลข สามารถเอามาคำนวณทางคณิตศาสตร์ได้
      • ตัวเลขปกติทั่วไป 10, 2.3, 1/2, 1.234E+03
      • ตัวที่อาจไม่เหมือนตัวเลข แต่จริงๆเป็นตัวเลขอย่าง เช่น วันที่และเวลา คือ ตัวเลขที่เปลี่ยน Format ไป
        • วันที่ เช่น 31 Jan 2013
          • Excel จะมองวันที่ เป็นจำนวนเต็ม เช่น
          • เลข 1 คือ วันที่ 1 เดือน 1 ปี คศ. 1900
          • เลข 2 คือ วันที่ 2 เดือน 1 ปี คศ. 1900
        • เวลา เช่น 16:30
          • Excel จะมองเวลา เป็นจุดทศนิยม โดย เที่ยงวันคือ 0.5 เป็นต้น
        • รายละเอียด อ่านได้ที่ การทำงานเกี่ยวกับวันและเวลา (Date & Time) ใน Excel
      • ธรรมชาติจะอยู่ ชิดขวาของ Cell โดยอัตโนมัติ
    2. Text ตัวอักษร เอาไว้แสดงผลข้อความ ไม่ได้เอาไว้มาคำนวณ
      • ตัวหนังสือจริงๆ เช่น ช้าง, ม้า, cow, sid110, I love my pen
      • ตัวหนังสือที่หน้าตาเหมือน Type อื่น เช่น 123 จริงๆ สามารถเป็นตัวหนังสือก็ได้ ถ้าพิมพ์ว่า ‘123
      • ธรรมชาติจะอยู่ ชิดซ้ายของ Cell โดยอัตโนมัติ
      • แปลว่า บางช่องเราอาจเห็นว่าเป็นตัวเลข แต่จริงๆ เป็น Text ก็ได้ วิธีดูคร่าวๆ คือ หากมันถูกจัดชิดซ้าย โดยที่เราไม่ได้ไปเป็นคนกำหนดจัดซ้ายเอง มันจะเป็น Text, ถ้าชิดขวาจะเป็นตัวเลข)
      • เราสามารถกำหนดให้เวลาพิมพ์ข้อมูลลงไป แล้วบังคับให้ผลลัพธ์เป็น Text ได้ โดย
        • วิธีที่ 1 : ใส่เครื่องหมาย ‘ นำหน้า เช่น ‘001234 มันจะออกมาเป็น 001234 ที่เป็น Text
        • วิธีที่ 2 : เปลี่ยน format ของ Cell เป็น Text ก่อน แล้วค่อยพิมพ์ข้อมูล
    3. Logic ตรรกะ มีอยู่ 2 อย่างคือ
      • TRUE เกิิดเมื่อมีการเปรียบเทียบค่าแล้วเป็นจริง เช่น ใส่สูตรว่า =10>3
      • FALSE เกิิดเมื่อมีการเปรียบเทียบค่าแล้วเป็นเท็จ เช่น ใส่สูตรว่า =5<2
      • ธรรมชาติจะอยู่ กึ่งกลางของ Cell โดยอัตโนมัติ
    4. Error ข้อผิดพลาด
      • ธรรมชาติจะอยู่ กึ่งกลางของ Cell โดยอัตโนมัติ
      • ความผิดพลาดมีหลายสาเหตุด้วยกัน การที่เรารู้ความแตกต่างของมัน จะทำให้เราเข้าใจ และแก้ไขข้อบกพร่องได้อย่างถูกต้องมากขึ้นครับ
    ประเภท Errorความหมายค่าทดสอบที่แสดงออกมาจากการใช้ฟังก์ชัน
    ERROR.TYPE
    #NULL! เกิดจากการใช้ Intersection Operator (ช่องว่าง) แล้วปรากฎว่าการ Intersect นั้นออกมาเป็น Set ว่าง เช่น =SUM(A10:A20 C10:C20) (ช่วง 2 อันไม่มีช่องซ้ำกันเลย)1
    #DIV/0!เกิดจากการคำนวณที่มีการหารด้วย 02
    #VALUE!เกิดจากการใส่ค่า Input ลงไปในสูตรผิดประเภทข้อมูล
    เช่น =IF(“แมว”,1,0) จะผิด เพราะ ตรง “แมว” จริงๆ ต้องเป็นตรรกกะ ที่ถูกคือ = IF(A3=”แมว”,1,0) จึงจะไม่ Error    		3
    #REF!เกิดจากการที่ไม่สามารถอ้างถึง Cell ได้ ซึ่งอาจเกิดจากการ Delete Cell, Column หรือ Row ไปจนช่องนั้นหายไป4
    #NAME?มีการอ้างถึงชื่อ Cell หรือ Function ที่ไม่มีอยู่จริง (ปกติมักจะเกิดจากการจะใส่ข้อความ แต่ลืมใส่ ” ” ครอบ)5
    #NUM!มีการใส่ค่าตัวเลขที่มากเกินกว่า Excel จะรับไหว หรือ อาจเกิดจากการที่ Excel ทำการคำนวณ Trial & Error ค่า (Iterative) แล้วไม่ได้ผลลัพธ์ที่ต้องการ เช่น ตอนใช้สูตร IRR6
    #N/Aเป็น Error ที่เจอบ่อยมาก เช่น หาข้อมูลด้วยการ Lookup ไม่เจอ, อาจเกิดจากใส่ Input ลงสูตรผิดหรือเกิน (ซึ่งการ Lookup ไม่เจอ ไม่ใช่แปลว่าเขียนสูตรผิด)7
    #GETTING_DATA8

    วิธีตรวจสอบประเภทข้อมูล

    โดยเราสามารถตรวจสอบว่าข้อมูลที่เราสนใจอยู่ในประเภทไหนได้ง่ายๆ มี 3 วิธี คือ

    ใช้ยางลบ Clear Format ทิ้งแล้วดูด้วยตา

    • หากเป็น Number จะอยู่ชิดขวาของช่อง
    • หากเป็น Text ปกติจะอยู่ชิดซ้ายของช่อง
    • หากเป็น Logic จะเป็นคำว่า TRUE FALSE อยู่กึ่งกลางช่อง
    • หากเป็น Error มักมีเครื่องหมาย # หรือ ! และ อยู่กึ่งกลางช่อง

    ใช้ Function =TYPE(ช่องที่ต้องการตรวจสอบ) 

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

    • 1 = Number (ตัวเลข)
    • 2 = Text (ตัวอักษร)
    • 4 = Logic (ตรรกกะ) :
    • 16 = Error (ผิดพลาด)
    • 64 = Array (อาเรย์) **** เป็นการใส่ข้อมูลหลายๆ ค่าใน Cell เดียว ซึ่งขอไม่พูดถึึง ณ ตอนนี้ครับ

    ใช้ฟังก์ชั่นกลุ่ม IS….

    เป็นการถามคำถามกับ Excel ว่าใช้ประเภทข้อมูลที่สนใจมั้ย? ซึ่งจะให้ผลลัพธ์เป็น TRUE/FALSE

    • ISNUMBER เช็คว่า ใช่ตัวเลขหรือไม่?
    • ISTEXT เช็คว่า ใช่ตัวเลขหรือไม่?
    • ISNONTEXT เช็คว่า ไม่ใช่ตัวหนังสือหรือไม่?
    • ISLOGICAL เช็คว่า ใช่ตรรกกะหรือไม่?
    • ISERROR  เช็คว่า error หรือไม่?
    • ISFORMULA  เช็คว่า ใช่สูตรหรือไม่?

    ทำไมเราต้องเรียนรู้ประเภทของข้อมูลใน Excel?

    • หากเรารู้ว่าข้อมูลที่เรากำลังใส่เป็นข้อมูลแบบไหน เราจะสามารถใช้สูตรได้พลิกแพลงมากขึ้น โดยเรารู้ว่าเราต้องใส่ Input ลงไปในสูตรในแต่ละ Argument (แต่ละช่อง Input) ให้ถูกประเภท ตามที่แต่ละสูตรต้องการ เช่น
      excel formula arguement
      • สูตร =LEFT(ข้อความ,จำนวนตัวอักษรที่ต้องการ) จะเห็นว่า จำนวนตัวอักษรที่ต้องการ จะต้องเป็นข้อมูลที่เป็น Number (ตัวเลข) เท่านั้น แปลว่า หากเรามีสูตรที่ได้ผลลัพธ์เป็นตัวเลข เราก็สามารถผสมสูตรนั้นลงไปในช่อง “จำนวนตัวอักษรที่ต้องการ” ได้เช่นกัน
    • เราจะเข้าใจการทำงานของ Excel มากขึ้น เช่น เราจะเข้าใจว่า ทำไมเวลาเอาวันที่มาลบกันแล้วจะได้ออกมาเป็นช่วงเวลาระยะห่างของสองวันได้ (เพราะ Excel มองว่าวันที่คือตัวเลขตัวหนึ่งนั่นเอง)

    เมื่อคุณอ่านบทความนี้จบแล้ว ก็น่าจะมีพื้นฐานเกี่ยวกับประเภทข้อมูลใน Excel กันแล้วล่ะ ในตอนต่อๆ ไปเราจะได้มาเรียนรู้วิธีการใช้สูตร (Formula) กันครับ