Tag: replace

  • REPLACE

    คำอธิบาย

    ฟังก์ชัน REPLACE ใช้ในการแทนที่บางส่วนของข้อความ โดยตัดข้อความเก่า “ในตำแหน่งที่เราระบุ” ทิ้งไปซะ แล้วใส่ข้อความใหม่ตามที่เราต้องการเข้าไปได้เลย!

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

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

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

    REPLACE(old_text, start_num, num_chars, new_text)

    Arguments

    • old_text (Required – string)
      ข้อความต้นฉบับที่คุณต้องการแก้ไข
    • start_num (Required – number)
      ตำแหน่งเริ่มต้นใน old_text ที่คุณต้องการเริ่มการแทนที่ด้วย new_text
    • num_chars (Required – number)
      จำนวนของตัวอักษรใน old_text ที่ต้องการแทนที่
    • new_text (Required – string)
      ข้อความใหม่ที่จะแทนที่ใน old_text

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

    • Formula:
      =REPLACE("abcdefghijk", 6, 5, "-")
      Description: แทนที่ 5 ตัวอักษรในข้อความ ‘abcdefghijk’ ด้วยเครื่องหมาย * เริ่มต้นจากอักษรตัวที่ 6
      Result:ข้อความจะถูกเปลี่ยนเป็น ‘abcde-k’
    • Formula:
      =REPLACE("2009", 3, 2, "10")
      Description: แทนที่ 2 ตัวอักษรสุดท้ายของข้อความ ‘2009’ ด้วย ’10’
      Result:ข้อความจะถูกเปลี่ยนเป็น ‘2010’
    • Formula:
      =REPLACE("fathippo", 4, 0, "@")
      Description: เริ่มที่ตำแหน่ง 4 (ตัวเลข h ใน “hippo”) ไม่ลบอักขระใดเลย (เพราะระบุ 0 ใน num_chars) จากนั้นเพิ่มข้อความ “@” ในตำแหน่งที่ 4
      Result: “fat@hippo”

    Tips & Tricks

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

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

    Oops! ต้องระวังนะถ้าคุณเลือกตำแหน่งเริ่มต้นหรือจำนวนตัวอักษรผิด ข้อมูลที่ได้อาจจะดูแปลกไปจากที่คุณตั้งใจ

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

    ข้อดี / ข้อจำกัด

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

    References

  • จัดการข้อความให้อยู่หมัด ด้วยฟังก์ชั่นกลุ่ม TEXT

    จัดการข้อความให้อยู่หมัด ด้วยฟังก์ชั่นกลุ่ม TEXT

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

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

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

    สรุปฟังก์ชั่นกลุ่ม Text ที่ใช้บ่อย

    LEN

    นับจำนวนตัวอักษร (นับทั้งอักษร ช่องว่าง สระ และวรรณยุกต์ด้วย)

    len

    =LEN(text)
    =LEN(ข้อความที่จะนับอักษร)
    ผลลัพธ์ : เป็น Number
    ตัวอย่าง : =LEN(“มดแดง”) =5

    LEFT

    ตัดคำจากทางซ้าย (ระบุได้ว่าตัดกี่ตัวอักษร)

    left

    =LEFT(text,[num_chars])
    =LEFT(ข้อความต้นฉบับ, [จำนวนอักษรที่จะตัด])
    ผลลัพธ์ : เป็น Text
    ตัวอย่าง : =LEFT(“มดแดง”,2) =“มด”

    MID

    ตัดคำโดยระบุได้ว่าเริ่มตัดที่ตำแหน่งไหน เป็นจำนวนกี่ตัวอักษร

    mid-fix

    =MID(text,start_num,num_chars)
    =MID(ข้อความต้นฉบับ,อักษรที่เริ่มตัด,จำนวนอักษรที่จะตัด)
    ผลลัพธ์ : เป็น Text
    ตัวอย่าง : =MID(“มดแดง”,3,2) =“แด”

    RIGHT

    ตัดคำจากทางขวา (ระบุได้ว่าตัดกี่ตัวอักษร)

    right
    =RIGHT(text, [num_chars])
    =RIGHT(ข้อความต้นฉบับ, [จำนวนอักษรที่จะตัด])
    ผลลัพธ์ : เป็น Text
    ตัวอย่าง : =RIGHT(“มดแดง”,2) =“ดง”

    TRIM

    ตัดช่องว่างส่วนเกินออก (ลบช่องว่างที่อยู่หน้า หลัง รวมถึงช่องว่างตรงกลางที่เกิน 1 เคาะ)

    trim
    =TRIM(text)
    =TRIM(ข้อความที่จะตัด)
    ผลลัพธ์ : เป็น Text
    ตัวอย่าง : =TRIM(”  inw  excel “) =”inw excel” (เหลือช่องว่างกลาง 1 space)

    FIND 

    ค้นหาคำที่ต้องการ ว่าอยู่ตำแหน่งที่เท่าไหร่ของข้อความที่กำหนด (สนใจตัวพิพม์เล็กพิมพ์ใหญ่) ระบุได้ว่าให้เริ่มหาตั้งแต่อักษรที่เท่าไหร่

    find
    =FIND(find_text, within_text, [start_num])
    =FIND(ส่วนของคำที่ต้องการหา, หาในคำไหน, [ตำแหน่งที่เริ่มหา])
    ผลลัพธ์ : เป็น Number
    ตัวอย่าง : =FIND(“Excel”,”inwexcel is Excellent”) =13

    SEARCH

    ค้นหาคำที่ต้องการ ว่าอยู่ตำแหน่งที่เท่าไหร่ของข้อความที่กำหนด โดยใช้การหาแบบ Wildcard (ไม่สนใจตัวพิพม์เล็กพิมพ์ใหญ่) ระบุได้ว่าให้เริ่มหาตั้งแต่อักษรที่เท่าไหร่

    search
    =SEARCH(find_text,within_text,[start_num])
    =SEARCH(ส่วนของคำที่ต้องการหา, หาในคำไหน, [ตำแหน่งที่เริ่มหา])
    ผลลัพธ์ : เป็น Number
    ตัวอย่าง : =SEARCH(“Excel”,”inwexcel is Excellent”) =4

    SUBSTITUTE

    แทนที่คำที่ต้องการด้วยอีกคำหนึ่ง ใช้เมื่อรู้คำที่จะถูกแทนที่

    substitute
    =SUBSTITUTE(text, old_text, new_text, [instance_num])
    =SUBSTITUTE(คำที่ต้องการแทนที่, ส่วนของคำเดิมที่จะถูกแทนที่, ส่วนของคำใหม่ที่จะมาแทนที่, [เจาะจงอันที่ถูกแทนที่ได้])
    ผลลัพธ์ : เป็น Text
    ตัวอย่าง : =SUBSTITUTE(“ผม like มาก”,”like”,”ชอบ”) = “ผม ชอบ มาก”

    REPLACE

    แทนที่ตำแหน่งที่ต้องการด้วยอีกคำหนึ่ง ใช้เมื่อรู้ตำแหน่งและจำนวนตัวอักษรที่จะถูกแทนที่

    replace
    =REPLACE(old_text, start_num, num_chars, new_text)
    =REPLACE(คำที่ต้องการแทนที่, ตำแหน่งเริ่มที่จะแทนที่, จำนวนอักษรที่จะแทนที่, คำใหม่ที่จะมาแทนที่)
    ผลลัพธ์ : เป็น Text
    ตัวอย่าง : =REPLACE(“081-234-5678”,5,3, “ไม่บอก”) = “081-ไม่บอก-5678”

    TEXT

    เปลี่ยนรูปแบบ Number Format โดยใช้สูตร

    text
    =TEXT(value, format_text)
    ในช่อง format_text ให้ใส่รูปแบบคล้ายการทำ Custom Format ซึ่งจะอธิบายต่อไปในบทหลัง
    ผลลัพธ์ : เป็น Text
    ตัวอย่าง : =TEXT(1234.5678,”0.00″)= “1234.57”

    UPPER

    แปลงข้อความให้กลายเป็นตัวพิมพ์ใหญ่ทั้งหมด

    upper
    = UPPER(text)
    ผลลัพธ์ : เป็น Text
    ตัวอย่าง : =UPPER(“mOnKey”)= “MONKEY”

    LOWER

    แปลงข้อความให้กลายเป็นตัวพิมพ์เล็กทั้งหมด

    lower
    = LOWER(text)
    ผลลัพธ์ : เป็น Text
    ตัวอย่าง : =LOWER(“mOnKey”)= “monkey”

    PROPER

    แปลงข้อความแต่ละคำให้ขึ้นต้นด้วยตัวพิมพ์ใหญ่ นอกนั้นตัวพิมพ์เล็กทั้งหมด

    proper
    = PROPER(text)
    ผลลัพธ์ : เป็น Text
    ตัวอย่าง : =PROPER(“mOnKey”)= “Monkey”

    (more…)

  • การค้นหาข้อมูล และ Wildcard เครื่องหมายมหัศจรรย์

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

    ปกติแล้วเวลาพูดถึงเรื่องการค้นหาข้อมูล คนส่วนใหญ่คงนึกถึงเครื่องมือ Find (Ctrl+F) ซึ่งผมก็ใช้มันบ่อยมากเช่นกัน แต่วันนี้ผมจะมาเปิดเผยความสามารถลับๆ ของมันที่หลายคนอาจยังไม่รู้ เป็นยังไงมาดูกันเลย!

    การใช้เครื่องมือ Find เพื่อค้นหาข้อมูล

    เครื่องมือนี้ เอาไว้ค้นหาสิ่งที่ต้องการ มีวิธีสั่งงานโดยให้ไปที่ [Home] –> Editing–> Find & Select –> Find… หรือ กด Ctrl+F ก็ได้ ง่ายกว่าเยอะ โดยที่ก่อนจะทำการค้นหา ถ้าเราเลือก Range เอาไว้ก่อน แล้วค่อยกด Ctrl+F มันจะหาเฉพาะ Range ที่เราเลือก (แต่ถ้าเลือกที่ Cell เดียว มันจะหาทั้งหมดเลย เพราะคงไม่มีใครหา Cell เดียวจริงมั๊ยครับ?)

    find

    จากนั้นใส่คำค้นหาที่ต้องการใน Find what: จากนั้นก็จะให้เลือกเอาว่าจะกด Find Next หรือ Find All

    Find Next ก็จะทำให้ Active Cell เด้งไปยังผลลัพธ์ที่อยู่ถัดไป ส่วน Find All ก็จะ List ผลลัพธ์ทั้งหมดที่เจอมาให้เลย

    โดยที่การค้นหาของเรา สามารถกำหนด Options >> เพิ่มเติมได้ ซึ่งจะมีเงื่อนไขให้เลือกเพิ่ม คือ

    • Format… ระบุรูปแบบที่ต้องการ ซึ่งหากขี้เกียจระบุเองก็สามารถกด Choose Format from cell แล้วจิ้มไปยัง Cell ที่มีรูปแบบที่ต้องการได้เลย
    • Within: เอาไว้ระบุว่าจะหาเฉพาะใน Sheet หรือใน Workbook
    • Look in: ตรงนี้มีให้เลือก 2 แบบ ซึ่งสำคัญมากๆๆ นั่นก็คือFormulasหรือValues
      • Formula : จะเป็นการค้นหาจากสิ่งที่อยู่ใน Formula Bar ของแต่ละช่อง
      • Value : เป็นการค้นหาจากผลลัพธ์ที่แสดงออกมาใน Cell แต่ละช่อง
    • Match Case : ติ๊กเลือกถ้าต้องการสนใจตัวพิมพ์เล็กตัวพิมพ์ใหญ่ว่าต้องตรงกัน
    • Match entire cell contents : ติ๊กเลือกอันนี้ ถ้าต้องการให้เจอทั้งคำใน Cell เท่านั้น

    การใช้เครื่องมือ Find เพื่อเลือกข้อมูลหลายๆ Cell

    ที่ผมบอกว่าเครื่องมือ Find มันเจ๋งก็คือตรงความสามารถ Find All นี่แหละ ปกติเวลาคนหาแล้วกด Find All มันจะ List ผลการค้นหาทั้งหมดที่เจอมาให้ แต่สังเกตว่ายังเลือกช่องเดียวอยู่

    findall-1

    ตรงนี้แหละ ให้กด Ctrl+A เพื่อเลือกผลลัพธ์ทุกอัน คุณจะเห็นว่า Cell ต่างๆ ใน Excel ก็จะถูกเลือกไปด้วย ซึ่งอันนี้แหละเป็นเทคนิคการ Select Cell ที่เจ๋งมาก

    findall-2

    (more…)

  • แฉ 10 ความลับของ Excel ภาค 2 : เรื่องง่ายๆ ที่หลายคนไม่รู้

    แฉ 10 ความลับของ Excel ภาค 2 : เรื่องง่ายๆ ที่หลายคนไม่รู้

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

    เมื่อพร้อมแล้วไปลุยกันเลย!

    1. Copy Paste ง่ายๆ ด้วยการกดคลิ๊กขวาแล้วลากเม้าส์

    right-click-menu

    รู้หรือไม่ว่า เราสามารถเลือก Range ของข้อมูลที่ต้องการ จากนั้นเลื่อนเม้าไปที่ขอบของ Range ที่เราเลือกไว้ (มันจะกลายเป็นรูปลูกศรชี้ 4 ทิศ พร้อมกัน ที่ปกติเราเอาไว้ Move ข้อมูล) แล้วกดคลิ๊กขวาค้างไว้่ แล้วลากไปยังเป้าหมายที่ต้องการ จากนั้นจะมีเมนูขึ้นมาให้เพียบเลย (คล้ายๆตอนกด Paste Special) ซึ่งวิธีนี้มีข้อดีคือ จะเห็นชัดเจนก่อนทำการ Paste ว่า ช่องเป้าหมายจะครอบคลุมถึงแค่ไหน

    2. Copy แล้ว Paste Value Only แบบเร็วสุดๆ ด้วย Ctrl+C => Ctrl+V => Ctrl =>V

    เรื่องการ Copy ยังมีเคล็ดลับอีก เพราะเป็นเรื่องที่เราใช้บ่อย ผมเลยขอพูดอีกเรื่องนึงนะครับ หากเราต้องการจะ Copy แค่ Value แบบไม่เอา Format เราสามารถทำตาม Step นี้ได้เลยครับ

    ctrl-menu
    1. เลือก Range ต้นฉบับแล้วกด Ctrl+C เพื่อ Copy
    2. เลือกช่องปลายทางแล้วกด Ctrl+V เพื่อ Paste
    3. กด Ctrl เพื่อเข้าสู่ เมนู Paste Options
    4. กด V เพื่อ Paste Values (กดตัวอื่นๆก็ได้ ลองเลื่อนดู Shortcut เอาครับ)

    ที่นำเสนอแบบนี้เพราะว่าจำง่ายมาก คือ Ctrl+V => Ctrl => V แถวเร็วสุดๆด้วย ยังไงลองใช้ดูนะครับ

    3. สร้างตาราง Pivot Table ที่ Filter ตัวเลือกแตกต่างกันโดยอัตโนมัติ

    report-filter
    report-filter2

    เวลาเรา Pivot ข้อมูล แล้วมีการใช้ Filter ข้อมูลโดย อยากให้ 1 ตาราง Pivot เป็นของแต่ละ Product

    ปกติแล้วเราก็อาจจะใช้วิธี Pivot ข้อมูลแล้วเลือก Filter ทีละอัน แล้ว Copy เป็นตารางใหม่ เช่น เลือก Product ของเล่น 1 ที แล้ว Copy Pivot ออกมาเป็นอีกตาราง แล้วค่อยเปลี่ยน Filter เป็น Product เครื่องใช้ไฟฟ้า.. ทำแบบนี้ไปเรื่อยๆ เป็นต้น (ถ้ามี item เป็นสิบอันนี่เหนื่อยเลยครับ)

    ผมจะบอกว่า Excel มีเครื่องมือทำรายงานแบบนี้ให้โดยอัตโนมัติ โดยทำตามนี้

    1. ไปกดที่ Ribbon Options ของ Pivot Table (ต้องกดเลือกบริเวณที่เป็น Pivot ก่อน)
    2. ติ๊กปุ่มสามเหลี่ยมใต้ที่ตั้งชื่อตาราง Pivot
    3. เลือก Show Report Filter Page
    4. เลือก Field ที่ต้องการ Filter
    5. Report จะถูกสร้างขึ้นโดยอัตโนมัติ โดยแยก 1 ตาราง ต่อ 1 sheet (มีการตั้งชื่อ Sheet ให้เป็นแต่ละ item ของสิ่งที่เรา Filter ให้ด้วย)

    4. ปิดคำสั่ง Generate GetPivotData

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

    getpivotdata

    ปกติเวลาเราเขียนสูตร แล้วเข้าไปคลิ๊กในบริเวณที่เป็นตาราง Pivot Table เราอาจจะเห็นสูตรพิลึกๆ ขึ้นมาอย่างเช่น
    =GETPIVOTDATA(“Sum of xxx”,$A$3,”yyy”,zzz,”aaa”,”bbb”) ซึ่งทำให้เกิดปัญหาว่าเรา Copy สูตรไปที่อื่น มันก็ไม่เลื่อนช่องให้แบบเวลาใช้ Cell Reference ปกติ ซึ่งมีวิธีแก้เพื่อให้เวลาใช้สูตรแล้วคลิ๊กเข้าไปใน Pivot Table แล้วยังเป็น Cell ปกติได้ โดยให้ทำดังนี้

    1. ไปกดที่ Ribbon Options ของ Pivot Table (ต้องกดเลือกบริเวณที่เป็น Pivot ก่อน)
    2. ติ๊กปุ่มสามเหลี่ยมใต้ที่ตั้งชื่อตาราง Pivot
    3. เอาติ๊กคำว่า Generate Getpivotdata ออก

    อาจมีคนสงสัย แล้ว GETPIVOTDATA มันมีข้อดียังไง? จริงๆแล้วมันก็มีข้อดีของมันอยู่ครับ นั่นก็คือ เวลาเรา Link ข้อมูลมาแล้ว ไม่ว่าตาราง Pivot Table จะถูกพลิกบิดมุมมองไปมาแค่ไหน ค่าที่เรา Link ไว้ก็จะยังเป็นค่าเดิมเสมอ ต่างจากการใช้ Cell Reference ปกติ ที่อาจกลายเป็นค่าใหม่ที่เราไม่ต้องการไปโดยไม่รู้ตัวเลยก็ได้

    5. จริงๆ แล้ว Keyboard Shortcut มันจำง่ายกว่าที่คิดนะ

    เคยสังเกตมั๊ยว่า Keyboard Shortcut ใน Excel หลายๆตัวนั้นจำง่ายกว่าที่คิด

    ถ้าเป็นคำสั่ง Basic เช่น Copy แทนด้วย Ctrl+C เพราะ คำว่า Copy แทนด้วย C หรือเช่นทำให้ตัวอักษรเป็นตัวหนา (Bold) ด้วย Ctrl+B นั้นก็ ไม่น่าแปลงใจมากนัก แต่ว่าจริงๆแล้ว แม้จะเป็นเครื่องหมายแปลกๆ ก็จำง่ายกว่าที่คิดนะครับ เพราะ Excel ตั้งใจในการเลือกใช้ตัวแทนคีย์ลัดได้น่าสนใจทีเดียว เช่น

    keyboard
    • Ctrl+% เพื่อแปลงเป็นหน่วย % (จริงๆ ต้องกด Ctrl+Shift+5 เพราะ Shift +5 คือตัว % แต่ถ้าต้องจำว่า Ctrl+Shift+5 จะไม่มีทางจำได้เลย)
    • Ctrl+^ ก็เพื่อแปลงเป็นเลข Scientific E ยกกำลัง (เพราะเป็นเครื่องหมายยกกำลัง)
    • Ctrl+$ ก็เพื่อแปลงเป็นรูปแบบสกุลเงิน
    • Ctrl+# ก็เพื่อแปลงเป็นวันที่ (เพราะในโปรแกรม Access ก็ใส่วันที่ในเครื่องหมาย #)
    • Ctrl+@ ก็เพื่อแปลงเป็นเวลา เพราะ เครื่องหมาย@ ก็ดูเจาะจง คล้ายว่าจะระบุว่า ณ กี่โมง
    • Ctrl+: ใส่เวลาปัจจุบัน เพราะเหมือนเครื่องหมายคั่น ชม:นาที
    • Ctrl+* เลือก Range ทั้งหมด เพราะ * แทนความหมายว่าทั้งหมด ในภาษาฐานข้อมูล
    • จริงๆ มีอีกเยอะเลย ถ้าเพื่อนๆ ถ้าเจอตัวอื่นที่ดู Make Sense แบบตัวอย่างข้างบนนี้ก็ Comment ไว้ได้นะครับ

    6. รู้หรือไม่ว่า Excel เวอร์ชั่นแรกนั้นออกให้กับ Apple ไม่ใช่ Microsoft

    excel1

    อันนี้เป็นเกร็ดเล็กเกร็ดน้อยครับ หลายๆคนอาจรู้ว่า Excel ไม่ใช่โปรแกรม Spreadsheet อันแรกของโลก เพราะก่อนหน้าที่จะมี Excel นั้นก็มีโปรแกรม Spreadsheet อื่นๆ เกิดขึ้นมาก่อนมากมาย โดยโปรแกรมแรกของโลกนั้น คือโปรแกรมที่มีชื่อว่า VisiCalc ซึ่งออกมาในปี ค.ศ. 1978 โน่นแน่ะ ต่อมาก็มีอีกหลายโปรแกรม แต่ตัวทีมีชื่อเสียงมากที่พวกเราน่าจะพอรู้จักก็คือ Lotus 1-2-3 ซึ่งออกมาในปี ค.ศ. 1983 และในที่สุด Excel เวอร์ชั่นแรกก็ออกมาในปี คศ.1985 แต่ที่แปลกคือ Excel 1.0 ซึ่งเป็น Version แรกนั้น กลับออกมาให้กับสำหรับเครื่อง Apple Macintosh ก่อนที่จะออก Version 2.0 ใน  Windows ซะอีกนะครับ ทั้งที่สร้างโดย Microsoft เองนี่แหละ! สาเหตุอาจเป็นเพราะความสามารถของการทำงานด้วยกราฟิก กับการใช้เม้าส์จิ้มเมนูต่างๆได้นี่แหละ ที่ Microsoft จำเป็นต้องเลือกลงให้ Macintosh ก่อน PC

    7. ฟังก์ชั่น TRIM ไม่ได้ตัดแค่ Space ที่หัวกับท้ายเท่านั้นนะ

    trim-example

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

    8. ในคำสั่ง FIND/REPLACE เราสามารถกำหนดรูปแบบที่จะค้นหาได้ด้วยนะ

    find-replace

    ปกติเวลาเราค้นหาคำ เราก็จะกด Ctrl+F เพื่อค้นหา แล้วก็พิมพ์คำที่ต้องการลงไป แต่จริงๆแล้วมันสามารถ กด Option แล้วเลือก Format เพื่อหาสิ่งที่ต้องการได้แบบเจาะจงยิ่งขึ้น เช่น หาช่องที่ถมสีเหลือง เป็นตัวหนา มี Number format ที่กำหนด หรือว่ามีการ Lock / Unlock cell เอาไว้ก็ยังได้

    นอกจากนี้ บางทีหลายๆคนเวลากดหาข้อมูลก็อาจหาไม่เจอ ทั้งๆที่เห็นอยู่ตำตา สาเหตุอาจเป็นเพราะเพื่อนๆ เลือกโหมดการหาผิด
    เช่น ใน Look in จะมีให้เลือก 3 อัน คือ Formula , Value, Comment โดยปกติจะเลือกไว้ที่ Formula ซึ่งมันจะหาสิ่งที่พิมพ์ลงไปใน Formula Bar จริงๆ ไม่ใช่ผลลัพธ์จากการคำนวณ หากเราต้องการหาคำที่อยู่ในผลลัพธ์การคำนวณจะต้องกดหาใน Values นะครับ

    9. เราสามารถ Search หาฟังก์ชั่นได้ด้วยนะ

    search-function

    ในเมื่อ Excel นั้นมีฟังก์ชั่นให้เลือกอยู่มากมาย จะหาตัวที่น่าจะใช่ก็ยากนัก (เพราะเลือกใช้ไม่ถูก) ผมจะบอกว่าจริงๆแล้ว Excel สามารถค้นหาฟังก์ชั่นได้เหมือนกันนะครับ (มันจะหาจากคำบรรยายที่เกี่ยวข้อง) โดยกดปุ่ม fx จากนั้นพิมพ์ลักษณะของฟังก์ชั่นที่ต้องการได้เลย พอกด Enter มันจะ List ฟังก์ชั่นทั้งหมดที่เกี่ยวกับคำนั้นมาให้ จากนั้นค่อยอ่านคำบรรยายด้านล่างเอาว่าใช้สิ่งทีต้องการหรือไม่ เคล็ดลับง่ายๆแบบนี้ หลายๆคนอาจยังไม่รู้ก็ได้นะ!

    10. รู้จักใช้เครื่องหมาย Wildcard ใน Function ค้นหาข้อมูล

    wildcard_joker_main2

    เวลาเราใช้ Function หลายๆ อย่างเพื่อค้นหาข้อมูล เช่น SEARCH หรือ VLOOKUP หรือ MATCH จริงๆเราสามารถค้นหาคำที่มีลักษณะคล้ายกับคำที่กำหนดได้ ด้วยการใช้เครื่องหมาย Wildcard ได้

    คำว่า Wildcard นั้นหากเปรียบเทียบกับการเล่นไพ่ มันก็เปรียบเสมือนตัวโจ๊กเกอร์ที่แทนไพ่อะไรก็ได้ ใน Excel ก็เช่นกัน เครื่องหมาย Wildcard คือตัวที่ใช้แทนตัวอักษรอะไรก็ได้ ซึ่งมีอยู่ 2 ตัว โดยมีรายละเอียดดังนี้

    • เครื่องหมายคำถาม (?) แทนตัวอักษรใดๆก็ได้จำนวน 1 ตัวอักษร
      • เช่น “b?t” จะเจอคำที่มี 3 ตัวอักษร ที่ขึ้นต้นด้วย b และลงท้ายด้วย t เช่น bat และ but แต่ไม่จอ bt (เพราะตรงกลางไม่มีตัวอักษร)
    • เครื่องหมายดอกจัน (*) แทนตัวอักษรใดๆก็ได้จำนวนกี่ตัวอักษรก็ได้
      • เช่น “b*t” จะเจอทุกคำที่ขึ้นต้นด้วย b และ ลงท้ายด้วย t เช่น bat, but, bt, beast, boot
      • เช่น “*ing” จะเจอทุกคำที่ลงท้ายด้วย ing เช่น interesting, sing, ping
      • เช่น “a*” จะเจอทุกคำที่ขึ้นต้นด้วย a รวมถึง a ตัวเดียวด้วย
    • ลองใช้ผสมๆ กัน
      • เช่น “a?*” จะเจอทุกคำที่ขึ้นต้นด้วย a แต่ไม่รวมถึงคำที่มี a แค่ตัวเดียว
    • ถ้าอยากจะหาคำที่มีเครื่องหมายคำถาม หรือ ดอกจันจริงๆ ในข้อความ ให้ใส่เครื่องหมาย ~ นำหน้าอักษรนั้นๆ เช่น ต้องการหาคำว่า star*wars ให้ใส่ใน criteria ว่า “star~*wars”

    ตัวอย่าง:

    =MATCH(“*excel*”,A1:B10,0) จะเจอแถวแรกที่มีคำว่า  excel ผสมอยู่เป็นส่วนประกอบ
    เช่นเป็นคำว่า inwexcel หรือแม้แต่ i love Excel very much

    ถ้าอยากให้เป็น Cell Reference อาจใช้แบบนี้ก็ได้ครับ =MATCH(“”*”&C1&”*”,A1:B10,0)

    ฟังก์ชั่นที่สามารถใช้ Wildcard ได้มีดังนี้

    • AVERAGEIF
    • AVERAGEIFS
    • COUNTIF
    • COUNTIFS
    • DPRODUCT
    • DSTDEVP
    • DSUM
    • DVARP
    • HLOOKUP
    • MATCH
    • SEARCH
    • SEARCHB
    • SUMIF
    • SUMIFS
    • VLOOKUP

    ประเด็นที่สำคัญอีกอันคือ ฟังก์ชั่นที่รองรับ Wildcard มันจะไม่สนใจตัวพิมพ์เล็กพิมพ์ใหญ่นะครับ เช่น VLOOOKUP หาคำว่า INWEXCEL ธรรมดาก็เเจอคำว่า inwexcel ได้เหมือนกัน อันนี้อาจต้องระวัง

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