มีแฟนเพจถามผมว่า เราสามารถใส่รูปภาพเข้าไปใน Comment ได้หรือไม่? ผมเลยขอตอบด้วยบทความนี้ครับ
สมมติเราอยากใส่ Comment เป็นรูปภาพ ให้กับ Cell ที่ต้องการ ก็จะมีวิธีการดังนี้
คุณเคยมั้ย? ที่ต้องจัดการข้อมูลบางอย่างอย่างเร่งด่วน และไม่ใช่งาน Routine จะเอาเวลาไปเขียนสูตรก็คงไม่คุ้ม ผมมีเครื่องมือที่ชื่อว่า Flash Fill มาแนะนำสำหรับคนที่ยังไม่รู้จักครับ ซึ่งเครื่องมือนี้มามาตั้งแต่ Excel 2013 แล้วล่ะ มีแล้วก็ใช้ให้คุ้มหน่อย
หากคุณต้องการผลลัพธ์แบบไหน ก็พิมพ์บอก Excel ไปซะ จากนั้นกด Ctrl+E แล้ว Excel จะพยายามหารูปแบบ (Pattern) จากสิ่งที่คุณพิมพ์ลงไปเอง ! (ถ้ายังหา Pattern ไม่ได้ ก็พิมพ์สอนเพิ่มไปอีก แล้วมันจะฉลาดขึ้น)
สมมติคุณมีข้อมูลแบบนี้ และคอลัมน์สีเหลืองคือสิ่งที่คุณต้องทำเพิ่ม
คุณสามารถพิมพ์ผลลัพธ์ที่ต้องการไปก่อนซัก 1-2 บรรทัด เพื่อทำการสอน Excel ให้รู้ว่าอยากได้อะไร (ลองบรรทัดเดียวก่อนก็ได้)
ให้คลิ๊กที่ช่องที่เราสอน (หรือจะคลิ๊กช่องข้างใต้ก็ได้) แล้วกด Ctrl+E ทีละช่อง (ทีละคอลัมน์)
ถ้ามันโวยวายแบบนี้ แสดงว่ามันงง ให้เราสอนเพิ่มอีกบรรทัดนึง แล้วกด Ctrl+E คราวนี้น่าจะได้แล้ว
กด Ctrl+E ทีละคอลัมน์
กด Ctrl+E ทีละคอลัมน์
สังเกตว่าผลลัพธ์ที่ได้ มันเลียนแบบได้ดีทีเดียว แม้กระทั่ง การ Extract ข้อความ การเติมข้อความ เติมสัญลักษณ์ . และ @ หรือแม้กระทั่งเรื่องตัวพิมพ์เล็กพิมพ์ใหญ่มันก็จัดการให้ได้นะครับ
ถ้ามันให้ผลลัพธ์ผิด ก็ต้องสอนเพิ่มไปอีกซักบรรทัดนึง มันน่าจะฉลาดขึ้นครับ
จริงครับ ที่ Flash Fill นั้นฉลาดมาก แต่มันก็มีข้อเสียที่สำคัญคือ ถ้าข้อมูลเปลี่ยน หรือมีข้อมูลใหม่มา ผลลัพธ์มันไม่เปลี่ยนตาม (เพราะเป็นการ Stamp ค่าคงที่ลงไปแล้ว ไม่ใช่สูตร) นอกจากนี้ กรณีที่ผลลัพธ์มันซับซ้อนมากขึ้น Flash Fill มันก็อาจจะดูไม่ออกอยู่ดี ดังนั้นการเขียนสูตรยังไงก็ยังเป็นเรื่องที่จำเป็นครับ
อ่อ! อย่าลืมว่า Flash Fill ใช้ได้แค่ Excel 2013 ขึ้นไปเท่านั้นนะครับ ดังนั้นถ้าใครยังใช้ Excel Version เก่าก็ใช้ความสามารถนี้ไม่ได้หรอกนะครับ
ใครใช้ Flash Fill ทำอะไรเจ๋งๆ ได้ มาแชร์ความรู้กันได้นะครับ อยากเห็นตัวอย่างการใช้ Flash Fill เจ๋งๆ ของเพื่อนๆ จังครับ
ใครนึกไม่ออก ลองดูรูปพวกนี้เป็นไอเดียได้ครับ
ใส่ตัวคั่นตัวเลข
ดึงวันเดือนปี
หลังจาก ตอนที่แล้วผมได้แนะนำวิธีการติดตั้ง Power Query ไปแล้ว วันนี้ผมจะมาแนะนำ มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบให้พวกเราได้รู้จักกันครับ
แต่ก่อนจะบอก Steps การทำงานทั้งหมด ผมต้องแนะนำให้พอรู้จักลักษณะการทำงานของ Power Query กันซักหน่อย ว่ามันทำงานประมาณไหน จะได้นึกภาพออก
ผมคิดว่า Power Query เป็นเหมือนเครื่องมือมหัศจรรย์ ที่สามารถ รวบรวม&เชื่อมต่อกับข้อมูลได้หลายที่ หลายชนิด แล้วนำข้อมูลแต่ละอันมาคัดกรอง & ดัดแปลงให้อยู่ในรูปแบบที่เหมาะสม รวมถึงผสมข้อมูลหลายก้อนเข้าด้วยกันได้ ซึ่งผลลัพธ์สุดท้ายจะออกมาเป็นตารางให้เราสามารถนำไปใช้งานต่อได้อย่างง่ายดาย ไม่ว่าจะเอาไปใช้ใน PivotTable ทำกราฟ หรือจะเอาไปเขียนสูตรใน Excel ต่อก็ย่อมได้
หรือถ้าจะให้นิยามแบบสั้นๆ ผมขอบอกว่า Power Query คือ เครื่องมือรวบรวมและดัดแปลงข้อมูล นั่นเอง
ประเด็นที่สำคัญที่สุดเลย คือ มันเก่งมาก แต่ใช้งานไม่ยากเลย! ดังนั้นอย่ารอช้า เรามาเรียนรู้วิธีการใช้งาน Power Query กันเลยดีกว่า!
จากคำบรรยายลักษณะการทำงานของ Power Query ผมก็ขอแบ่งออกมาเป็นขั้นตอนดังนี้
สิ่งที่ยุ่งยากที่สุดของ Power Query ก็คือขั้นตอนรวบรวมเชื่อมต่อข้อมูลนี่แหละครับ ยิ่งถ้ามีหลายไฟล์ หลายแหล่งข้อมูลอาจต้องทำการเชื่อมต่อหลายทีหน่อย ดังนั้นขั้นนี้ให้อดทนนิดนึงนะครับ (จริงๆ มีวิธีเชื่อมทั้ง Folder ทีเดียวด้วยแต่ว่ายากเกินไปที่จะสอนตอนนี้ครับ ^^)
สมมติผมมีข้อมูลอยู่ 3 ที่ คือ 1. ในไฟล์ Txt 2.ในไฟล์ Excel ที่จะทำ PowerQuery เอง 3.ในไฟล์ Excel อื่น โดยให้โหลดไฟล์ที่นี่
ก่อนอื่นให้เปิดไฟล์ที่จะทำ Power Query ซะก่อน (ชื่อไฟล์ PowerQuery-Basic.xlsx)
วิธีเชื่อมต่อข้อมูลก็ง่ายมาก ไปที่ Ribbon Power Query แล้วมันจะมีให้เลือกว่าจะเอาข้อมูลจากไหน?
ปกติที่ใช้บ่อยๆ ก็จะมีจาก
เช่นไฟล์ data1.txt ผมก็ต้องเลือก From File -> TXT/CSV -> เลือกไฟล์ที่ต้องการ
ซึ่งตรง Load จะมีลูกศรให้เลือกว่า จะ Load เฉยๆ หรือ Load to และปุ่มข้างๆ มีคำว่า Edit ซึ่งแต่ละอันมีความหมายดังนี้
ในที่นี้ผมขอเลือก Load to แล้วติ๊ก Only Create Connection ดีกว่า ไฟล์จะได้ไม่ใหญ่ด้วย
เมื่อกด Load ก็จะมีหน้าต่างบอกว่า มีการเชื่อมต่อกับข้อมูล Data1 แล้ว
ผมทำแบบเดียวกันกับ Data2.xlsx โดยเลือกFrom File -> Excel คราวนี้มันจะบอกว่าเจอข้อมูล 2 ชีท
เราสามารถเลือกแบบ Multiple พร้อมกันได้เลย
ในที่นี้ผม Load to แบบ Only Create Connection เหมือนเดิม จได้ว่ามีการเชื่อมต่อ 3 อันแล้ว
สุดท้ายเอาข้อมูลจากไฟล์ตัวเอง สามารถกดปุ่ม From Table/Range ได้ แต่ผมแนะนำว่าถ้ายังไม่ได้เปลี่ยนข้อมูลให้เป็น Table ให้เราทำการแปลงเป็น Table ซะก่อน โดยการคลิ๊กที่ข้อมูลแล้วกด Ctrl+T
เดิม
กด Ctrl+T เพื่อแปลงเป็น Table แล้วตั้งชื่อตามใจชอบ
จากนั้น ขณะที่เลือก Table อยู่ ให้กดปุ่มที่ Tab Power Query ว่าเราจะเชื่อมต่อข้อมูลกับ Table
มันจะขึ้นหน้าต่าง Query Editor มาให้โดยอัตโนมัติ (หน้าต่างเดียวกันกับที่เวลาเรากด Edit นั่นแหละ) ในที่นี้ผมจะ Close & Load to เพื่อ Only Create Connection เหมือนเดิม
คราวนี้เราก็สร้าง Connection ครบทุกอันแล้ว!!
ถ้าข้อมูลแต่ละอันไม่ค่อยสมบูรณ์ เช่น มีข้อมูลซ้ำ ข้อมูลขยะ เราสามารถทำการ Edit Query เพื่อคัดกรอง / group ข้อมูลก่อนจะผสมข้อมูลเข้าด้วยกันได้ (ถ้าเราไม่กำจัดการซ้ำก่อน เวลา Map ข้อมูลแล้วมันอาจจทำให้ข้อมูลเบิ้ลเข้าไปอีก)
วิธีการทำคือ คลิ๊กขวาที่ Query ที่ต้องการแล้วกด Edit ได้เลย
แต่ในที่นี้ข้อมูลของผม ok แล้ว เลยไม่ต้องมีการแก้ไขอะไรครับ เราข้ามขั้นตอนนี้ไปได้เลยครับ ^^
ขั้นต่อไปเราจะทำการเชื่อมข้อมูลเข้าด้วยกันแล้ว ซึ่งจะมีให้เลือก 2 โหมด คือ
ในที่นี้ผมจะ Map ข้อมูลจากตารางอ้างอิง เลยต้องใช้การ Merge นั่นเอง ซึ่งพอกดเข้าไปแล้ว มันจะถามว่าจะเอาอะไรมา Merge กับอะไร?
หลังจากกด Load จะขึ้นหน้าจอ Query Editor มาให้อีกแล้ว แต่คราวนี้จะเห็นว่ามีคอลัมน์ใหม่ เขียนว่า NewColumn แล้วมีค่าเป็น มาให้ด้วย แปลกดีมั้ย?? ความหมายมันคือเป็นคอลัมน์ที่มาจากการ Map ซึ่งเราต้องทำการกดตาม Step ข้างล่าง เพื่อเลือกว่าจะเอาคอลัมน์ไหนมาแสดงบ้าง?
พอกด Ok ก็จะเห็นว่ามีคอลัมน์เพิ่มมาแล้ว และจะเห็นว่ามี Applied Step ด้านขวา เพิ่มมาด้วย
จากนั้นให้เราทำการ Merge กับตารางที่เหลือให้ครบ โดยไปที่คำสั่ง Merge Query
แล้วเชื่อมข้อมูลให้ครบ
พอเสร็จแล้วสามารถกดปุ่ม Close & Load ได้เลย คราวนี้เราอยากจะสร้าง Table ผลลัพธ์สุดท้ายแล้ว
สมมติว่า คุณลืมอะไรบางอย่างไป หรือทำอะไรผิด ก็สามารถเข้าไปแก้ Query เดิมได้ โดยคลิ๊กขวาที่ Query ที่ต้องการแล้วกด Edit ได้เลยครับ
เวลากดเข้าไปแล้ว จะสังเกตเห็นข้อมูล และ Applied Step ซึ่งคือขั้นตอนที่มันบันทึกไว้ว่ามันทำอะไรกับข้อมูลบ้าง ซึ่งเราสามารถแก้ไข ลบ สลับลำดับ action เหล่านี้ได้
เวลาเราทำ action อะไรเพิ่มเติมมันก็จะบันทึกต่อไปเรื่อยๆ (จริงๆ เราสามารถแทรก Step การทำงานก็ได้ โดยไปเลือก Step ที่ต้องการจะแทรก แล้วค่อยทำ action)
ผมจะเพิ่มคอลัมน์ยอดขาย ก็ไปกดที่ Add Column -> Add Custom Column
แล้วตั้งชื่อ Field ใหม่ จากนั้นพิมพ์สูตรที่ต้องการลงไป (คลิํกที่ชื่อ Field ที่มีให้เลือกได้ คล้ายๆ กับ Calculated Field ของ PivotTable)
Tips : จริงๆ แล้วสูตรที่เขียนใน Custom Column นี้เป็น ภาษาของ Power Query เอง เรียกว่า Power Query Formula Language หรือ ภาษา M (M Language) ซึ่งถ้าจะเอาให้ลึกซึ้งก็ต้องศึกษากันขนานใหญ่เลย แต่ไม่ต้องห่วงไป เราสามารถเขียนสูตรง่ายๆ อย่างบวก ลบ คูณ หาร ได้เช่นเดียวกับ Excel อยู่แล้ว
Tips2 : นอกจากที่ Custom Column แล้ว ภาษา M ยังสามารถเขียนอยู่ในตัวตาราง Query ได้ด้วยนะ แต่ยาก เอาไว้ทีหลังแล้วกันครับ ^^”
มันก็จะมี Step เพิ่มขึ้นมา
ผมจะตัดคอลัมน์บางอันทิ้ง ก็เลือกคอลัมน์ที่จะไม่เอา เช่น ราคาต่อชิ้น (ผมไม่ใช้แล้ว) แล้วกดปุ่ม Del บน Keyboard ได้เลย
สังเกตว่า คอลัมน์ยอดขาย (=จำนวน*ราคาต่อชิ้น) ที่สร้างขึ้นมาใหม่ ก็ยังใช้งานได้อยู่ แม้ว่าเราจะลบคอลัมน์ ราคาต่อชิ้น ออกไปแล้วก็ตาม
ให้ดับเบิ้ลคลิ๊กที่ชื่อคอลัมน์ตรงหัวตาราง แล้วแก้ไขได้เลย
คลิ๊กขวาที่คอลัมน์ที่ต้องการ แล้วเลือก Change Type ได้เลย
ถ้าทำแล้ว Error แสดงว่ามันมองปฏิทินผิดแบบ
ให้ไปที่ Change Type -> Using Locale… แทน แล้วเลือกเป็น Date / English (UK) แล้วกด Enter
จะเห็นว่ามันยังคง Error อยู่ เป้นเพราะว่ามัน Error มาตั้งแต่ Step ก่อนหน้า
ดังนั้นเราต้องลบ Step ก่อนหน้าทิ้งซะ โดย Click ขวาที่ Step ก่อนหน้า แล้วกด Delete
จะเห็นว่าข้อมูลไม่ Error แล้ว และสามารถ Filter แบบ Date ได้
จะ Filter ข้อมูลได้ดี จะต้องเปลี่ยนประเภทข้อมูลให้ถูกต้องซะก่อน เช่น ถ้าไม่ใช่วันที่ ก็จะไม่มี Date Filter ให้เลือก เป็นต้น
เราสามารถ Split คอลัมน์ได้ 2 แบบ เช่นเดียวกับเครื่องมือ Text to Column นั่นคือ แบ่งแบบใช้ตัวคั่น (delimiter) กับแบบมีจำนวนอักขระชัดเจน ( number of character)
แล้วมันจะมี Option ต่ออีก ว่าจะแยกแค่ตัวแรก ตัวหลัง หรือทุกตัว ผมจะแยกคำนำหน้าชื่อ ก็เลยเอาแค่ตัวแรก
ถ้าทำทุกอย่างเสร็จหมดแล้ว เราก็กด Close & Load ได้เลยครับ
ใครอยากดูเทคนิคต่างๆ ที่ใช้บ่อยอ่านต่อได้ที่นี่
ผลลัพธ์ที่ออกมาจากการ Load จะเป็น Table ที่เอาไปใช้งานต่อได้ เช่น เอาไปวิเคราะห์ต่อใน PivotTable
แต่มันมันเจ๋งคือ Table นี้ยังคง Link Connection กับข้อมูลต้นทางอยู่ แปลว่าหากข้อมูลต้นทางเปลี่ยนไป เราแค่ Refresh เจ้า Query นี้ และ Refresh PivotTable อีกที รายงานของเราก็จะเสร็จเลยทันที!!
อ่านจบแล้ว ใครสงสัยอะไรตรงไหน หรือว่าการทำงานจริงอยากรู้อะไรเป็นพิเศษ สามารถ Comment ถามไว้ในนี้ หรือจะ inbox ถามใน Facebook ก็ได้ครับ ตรงไหนมีคนสงสัยเยอะๆ ผมจะได้อธิบายละเอียดให้อีกทีครับ ^^
ใครอ่านแล้วคิดว่ามีประโยชน์ก็อย่าลืมแชร์ให้คนอื่นรู้ด้วยนะครับ ยิ่งคนรู้เยอะเรายิ่งสบายครับ จะได้ช่วยกันลดงานถึกๆ ไปได้นะ ^^
จากที่ผมได้มีการ Post แนะนำ Power Query ไปในเพจ ปรากฏว่ามีคนให้ความสนใจเป็นจำนวนมาก ดังนั้นผมจึงคิดว่าจะเขียนบทความแนะนำ Power Query เบืองต้นให้ทุกคนได้อ่านกันครับ ซึ่งเครื่องมือนี้ใช้ได้กับ
สำหรับบทความนี้ เราจะมาเริ่มกันที่การปลุก Power Query ให้ตื่นขึ้นมาก่อน เพื่อที่ทุกคนจะได้ใช้งานมันได้ในตอนต่อๆ ไปครับ
ก่อนอื่นให้ปิด Excel ทั้งหมดไปก่อน
จากนั้นให้ไปที่ https://www.microsoft.com/en-us/download/details.aspx?id=39379 หรือ Search Google ด้วยคำว่า “download power query” แล้วคลิ๊ก Link แรก
จากนั้นคลิ๊กคำว่า Download ได้เลย
เลือก Download ว่าจะเอา 32-bit หรือ 64-bit ตามที่ดูใน Step ข้างบน แล้วกด Next โลด
(ถ้าไม่แน่ใจก็โหลดมา 2 ไฟล์เลย )
พอโหลดมาแล้วก็ Double Click เลย แล้วกด Run
พอเจอหน้าจอข้างบนก็กด Next , Accept, Next รัวๆ เลย (ถ้าไม่ขึ้นแบบข้างบน แสดงว่าโหลดมาผิด Version)
พอ Install เสร็จ ก็ลองเปิด Excel ขึ้นมาใหม่ คราวนี้ก็จะมี Ribbon Power Query โผล่มาแล้วครับ!!
ให้ลองไปที่ File -> Options -> Add-ins –> Com Add in ->Go -> ติ๊ก Microsoft Power Query ครับ
ใครลองทำตามนี้แล้วยังไม่ได้ ก็ Post Comment ไว้ได้ครับ เดี๋ยวมาดูให้
แต่ก่อนตอนผมใช้ Excel ใหม่ๆ มีปัญหาอย่างหนึ่งที่ผมหงุดหงิดมาก นั่นคือ ผมต้องการลาก Fill Handle แล้วให้ Excel สร้างตัวอักษร A-Z หรือ ก-ฮ โดยอัตโนมัติ แต่สิ่งที่ Excel ทำให้มันกลับแค่เป็นการ Copy ตัว A หรือ ก ที่ผมพิมพ์ลงมาให้ซะงั้น!
ผมงงมากเลย เพราะขนาด series ที่เป็นตัวเลขดูเหมือนว่าจะยากๆ เช่น 1,4,7,10,… มันยังฉลาดใส่มาให้ได้ แต่ทำไมแค่ A-Z หรือ ก-ฮ ดันไม่ใส่มาให้ฟะ!? ตอนนั้นก็ต้องใช้วิธีพิมพ์เอาเองบ้าง ใช้ฟังก์ชัน CHAR มาช่วยบ้าง ซึ่งก็ไม่ใช่วิธีที่ดีเท่าไหร่
จนผมได้พยายามค้นใน Internet ก็รู้ว่าจริงๆ แล้ว Excel ทำแบบที่ผมต้องการได้ ด้วยการสร้างสิ่งที่เรียกว่า Custom List นั่นเอง
Custom List เป็นเครื่องมือที่ช่วยให่เราสามารถสร้าง List รายการแบบที่เรากำหนดได้เอง ซึ่งจะทำให้ Excel สามารถ Auto Fill ข้อมูล หรือ Sort ข้อมูลตาม List ที่เรากำหนดได้
วิธีใช้ก็ง่ายแสนง่าย แค่พิมพ์ตัวใดตัวหนึ่งที่อยู่ใน List แล้วลาก Fill Handle ยาวลงมาก็ใช้ได้แล้ว
เวลาจะเรียงข้อมูลด้วย Custom List จะมีวิธียุ่งยากเล็กน้อย คือ ให้เราเลือก Sort แบบ Custom Sort (หรือไปที่ Ribbon [Data] => Sort ก็ได้)
จากนั้นตรง Order ให้เลือก Custom List… แล้วเลือก List ที่ต้องการ จากนั้นกด OK
จากนั้น Excel ก็จะเรียงข้อมูลตาม List ที่เราต้องการครับ
ก็จบแล้วนะครับกับบทความเรื่อง Custom List นี้ หวังว่าจะเป็นประโยชน์กับเพื่อนๆ นะครับ เพราะถ้าใครรู้เทคนิคนี้ ก็สามารถเลิกพิมพ์ List อะไรซ้ำๆ ไปอีกตลอดชีวิต อย่าง A-Z เนี่ย ผมใช้บ่อยมากๆ เลย ลดเวลาไปได้มากครับ
แต่มันจะเจ๋งกว่านี้อีก ถ้าเรามี List อื่นๆ เตรียมไว้ด้วย ดังนั้นถ้าใครมี List เจ๋งๆ ก็แชร์กันมาได้นะครับ เผื่อจะเป็นประโยชน์กับเพื่อนๆ คนอื่นด้วย ^^
ตอนนี้เป็นตอนต่อจาก The Mask Function ตอนที่ 2 : ถอดหน้ากาก ! มาดูกันว่ามันคือฟังก์ชันอะไร !?
ซึ่งฟังก์ชันทีผ่านเข้ารอบคือ ตัวแทนสาย C และ D ซึ่งได้แสดงผลงานในรอบแรกในการแข่ง Lookup ข้อมูลแบบ Multiple Criteria ไปแล้ว ทั้งคู่ต่างหาผลลัพธ์ได้ถูกต้องคือ ราคา 60 บาทถูกต้องเหมือนกันหมด แถมบอกเพิ่มด้วยว่า ถึงค่าที่จะ Lookup จะไม่ใช่ตัวเลข ตัวเองก็ไม่กลัวเลย สามารถทำงานได้ สบายหายห่วง!
โจทย์คือ มีช่วงตัวเลขกำกับการให้คะแนนแต่ละเกรดอยู่ แล้วให้ Lookup ค่าเกรดจากคะแนนที่กำหนด แถมต้อง Lookup หลายคะแนนต่อเนื่องกันด้วย
ตัวแทนสาย D ไม่รอช้า รีบแสดงความสามารถเลย เพราะถือว่า “เข้าทางมากๆ”
ต่างกันกับตัวแทนสาย C ซึ่งเจอโจทย์นี้ไปก็เกิดอาการอึดอัดไปซักพักหนึ่ง แต่ในที่สุดก็แสดงความสามารถออกมาจนได้…
ทั้งคู่ก็หาคำตอบมาได้ในที่สุด… แต่ด้วยความยากลำบากที่ต่างกัน
ในรอบแรก ตัวแทนสาย C ดูเหมือนจะได้โจทย์ที่เข้าทางมากๆ เขียนสูตรนิดเดียวก็ได้คำตอบเลย
แต่พอมาถึงในรอบหลังนี้ ดูเหมือนจะไม่ใช่แนวทางของตัวแทนสาย C เอาซะเลย…
กลับกันกับตัวแทนสาย D ซึ่งรอบแรกก็หืดขึ้นคอเหมือนกัน ต้องเขียนสูตรยาวมาก แถมต้องใช้ CHOOSE มาช่วยอีก
แต่พอมารอบนี้อะไรก็เข้าทางไปหมด..
เอ้า! แล้วตอนนี้คุณรู้หรือยังครับว่าตัวแทนสาย C คือใคร และตัวแทนสาย D คือใคร?
เรื่องพื้นฐานบางเรื่อง เป็นเรื่องง่ายๆ แต่หลายคนก็มองข้ามไป หรือทำไม่เป็นอย่างน่าเสียดาย …
วันนี้ผมเลยจะขอมาแนะนำความรู้เกี่ยวกับการเขียนข้อความในสูตร และการใช้เครื่องหมาย & เชื่อม ครับ
มีประเด็นที่น่าสนใจหลายอย่างเลย
ใครมีประเด็นที่น่าสนใจเพิ่มเติม ก็ Comment ได้เลยครับ
ตอนนี้เป็นตอนต่อจาก The Mask Function : หน้ากากฟังก์ชัน ! มาทายกันนี่คือฟังก์ชันอะไร?? ซึ่งฟังก์ชันทั้ง 4 ตัวได้โชว์ฝีมือในการแข่งขัน แข่ง Lookup ข้อมูลแบบ Multiple Criteria ไปแล้ว ทุกตัวต่างหาผลลัพธ์ได้ถูกต้องคือ ราคา 60 บาทถูกต้องเหมือนกันหมด!
ตัวผมเองก็ตัดสินใจไม่ถูกเหมือนกันว่าจะให้ฟังก์ชันไหนเข้ารอบต่อไปดี เพราะ ณ ขณะที่ผมกำลังเขียนบทความอยู่นี้ มีตัวแทนสาย C ที่มีคะแนนโหวตนำโด่งอยู่ตัวเดียว ส่วนตัวแทนสายอื่นๆ คะแนนเท่ากันหมดเลย
ผมคิดว่าการที่ตัวแทนสาย C ได้รับคะแนนโหวตเยอะมาก เพราะความง่ายและสั้นในการเขียนสูตรนั่นเอง ซึ่งผมจะเคารพการตัดสินใจของเพื่อนๆ ครับ
ดังนั้นตัวแทนสาย C จะเข้ารอบไป โดยที่จะยังไม่ได้เปิดเผยหน้าตาที่แท้จริงว่าคือฟังก์ชันอะไร?
ส่วนฟังก์ชันอีกอันที่จะเข้ารอบไป ผมขอเลือกเป็นตัวแทนสาย D แล้วกันครับ เนื่องจากความยุ่งยากในการเขียนสูตรที่ยาวมากๆ เรียกได้ว่าเป็นขั้วตรงข้ามกับตัวแทนสาย C โดยสิ้นเชิงเลย
ทั้งตัวแทนสาย C และ D ที่เข้ารอบไปแล้วฝากบอกมาว่า ถึงค่าที่จะ Lookup จะไม่ใช่ตัวเลข ตัวเองก็ไม่กลัวเลย สามารถทำงานได้ สบายหายห่วง!
ต่างจากตัวแทนกลุ่ม A และ B ที่หากจ้อง Lookup ค่าที่เป็นตัวหนังสือกลับมาคงจะลำบากสุดๆ ครับ
ดังนั้นวันนี้ผมจะทำการถอดหน้ากาก ตัวแทนสาย A และ B ซะก่อนครับ !!
ปรากฏว่าตัวแทนสาย A คือฟังก์ชัน SUMIFS ครับ!!
มีหลายคนเดาถูกด้วย แต่หลายคนก็อาจจะงงว่าทำไมฟังก์ชัน SUMIFS ถึงเอามา Lookup ค่าราคาได้ด้วย? มันเป็นฟังก์ชันที่เอาไว้หาผลรวมตามเงื่อนไขไม่ใช่เหรอ?
ใช่ครับ SUMIFS มีหน้าที่หลักคือเอาไว้หาผลรวมตามเงื่อนไขที่กำหนด … แต่ถ้าค่าที่จะ Lookup เป็นตัวเลข การใช้ SUMIFS ก็เป็นอีกวิธีที่เข้าท่ามากๆ ในการทำ Lookup แบบ Multiple Criteria
=SUMIFS(sum_range,criteria_range,criteria,…)
ถ้ามีค่า Criteria ซ้ำๆ หลายบรรทัด เช่น มี ข 2 หลายอัน การใช้ AVERAGEIFS ก็อาจจะเป็นทางเลือกที่ดีกว่า เพราะจะได้ค่าเฉลี่ยกลับไป แทนที่จะได้ผลรวมหากใช้ SUMIFS
ปรากฏว่าตัวแทนสาย A คือฟังก์ชัน SUMPRODUCT ครับ!!
มีคนเดาถูกเช่นกัน และหลายคนก็คงจะงงอีกเช่นกัน เพราะปกติ SUMPRODUCT จะเอาไว้คำนวณการจับคู่คูณกันแล้วค่อยหาผลรวมของทุกคู่
เช่นรูปนี้ มันจะเอา (100*60%) + (200* 50%) + (300*40%)
ซึ่งถ้าสังเกตจะเห็นว่า Range สีน้ำเงิน และ Range สีแดง อยู่แยกกันคนละ Argument (Array1 กับ Array2) มันก็เลยจับคู่มาคูณกันแล้วหาผลรวม
แต่กลับมายังโจทย์การแข่งขันของเรา ทั้ง (C2:C7) และ (B2:B7=B10) และ (A2:A7=A10) มันอยู่ Argument Array1 เหมือนกันหมดเลย!!
ซึ่งการเขียนสูตรแบบเอา Range หลายอันบวกลบคูณหารกันนั้น เป็นการเขียนสูตรแบบ Array Formula ซึ่งปกติต้องกดปุ่ม Control+Shift+Enter ถึงจะใช้งานได้ แต่ฟังก์ชัน SUMPRODUCT สามารถทำงานได้เลยโดยกดปุ่ม Enter ธรรมดา
หากลองวิเคราะห์ละเอียดโดยลากแถบดำในสูตร แล้วกด F9 ดูในองค์ประกอบแต่ละส่วนจะได้ดังนี้
=SUMPRODUCT({10;50;20;60;30;70}*({1;2;1;2;1;2}=2)*({“ก”;”ก”;”ข”;”ข”;”ค”;”ค”}=”ข”))
=SUMPRODUCT({10;50;20;60;30;70}*({FALSE;TRUE;FALSE;TRUE;FALSE;TRUE})*({FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}))
แปลว่า 10 จับคู่กับ FALSE และ FALSE
50 จับคู่กับ TRUE และ FALSE … ไปเรื่อยๆ
=SUMPRODUCT({10;50;20;60;30;70}*{0;0;0;1;0;0})
=SUMPRODUCT({0;0;0;60;0;0})
=60
สูตร SUMPRODUCT ฝากบอกมาว่า ถึงตารางจะพลิกไปเป็นอีกแบบ มันก็ยังทำงานได้นะ (เหมือน Lookup 2 แกน)
ซึ่งสูตรนี้ทำงานได้ เป็นการคูณ Array แบบมิติ 2 ที่มี Range ไม่เท่ากัน นั่นคือ
คูณกัน ได้ผลลัพธ์เป็น Range ที่มีมิติ = 3R x 2C ซึ่งมีมิติเท่ากับ Range ของข้อมูล Price B3:C5 พอดี จึงทำการคูณกันในตอนจบได้ ตามขั้นตอนข้างล่าง
=SUMPRODUCT((B3:C5)*(A3:A5=A10)*(B2:C2=B10))
=SUMPRODUCT({10,50;20,60;30,70}*({“ก”;”ข”;”ค”}=”ข”)*({1,2}=2))
=SUMPRODUCT(({10,50;20,60;30,70})*({FALSE;TRUE;FALSE})*({FALSE,TRUE}))
=SUMPRODUCT(({10,50;20,60;30,70})*{0,0;0,1;0,0})
=SUMPRODUCT({0,0;0,60;0,0})
= 60
วันศุกร์หน้า พบกับความสามารถของฟังก์ชั่นทั้งสองที่เข้ารอบไป ว่าจะแสดงอะไรเจ๋งๆ ให้พวกเราได้ดูกันครับ
ผมเชื่อว่าผู้อ่านหลายคนก็ใช้ Excel มาเป็นเวลานานแล้ว และก็เชื่ออีกว่า คุณต้องไม่เคยสำรวจคำสั่งต่างๆ ของ Excel บน Ribbon อย่างจริงๆ จังๆ แน่
วันนี้ผมก็เลยจะมาช่วยเพื่อนๆ มาสำรวจ Ribbon ไปด้วยกันครับ โดยเฉพาะคำสั่งตามซอกหลืบต่างๆ ที่คุณอาจไม่ทันสังเกตเห็นมาก่อน ^^
ผมจะลองไล่ Tab Home ให้ดูนะครับ ส่วน Tab อื่นไปเล่นกันเองน้าาา
Tab Home เป็น Tab ที่รวมเรื่องที่เราใช้งานบ่อยที่สุดเอาไว้ ถ้านึกอะไรไม่ออก ก็น่าจะต้องสั่งงานใน Tab นี้แหละ
ในส่วนของ Paste เองก็มีลูกเล่นหลายอย่างมากๆ และถ้ากด Paste Special ก็จะยิ่งมี Option เพิ่มขึ้นอีก เช่น ทำ Operation บวก ลบ คูณ หารได้ด้วย
แม้แต่การ Copy ก็ยังมีโหมด Copy as Picture … แต่จริงๆ ไม่ค่อยมีประโยชน์เท่าไหร่ เพราะมัน Paste As Picture ได้อยู่แล้วซึ่งสามารถเลือกโหมดของการ Copy ได้ว่าจะทำการ Copy แบบตามที่เห็นบนหน้าจอ หรือ ตามที่เห็นตอน Print
ซึ่งโหมด Copy as Picture แบบ As Shown when printed นี้จะได้ภาพที่คมชัดกว่า Copy เฉยๆ แล้ว Paste As Picture นะครับ แถมสามารถช่วยตัดพวกสัญลักษณ์ Error แปลกๆ (ธงเขียว) และสัญลักษณ์การ Comment (ธงแดง) ออกไปได้ด้วย !! (คุณบิว วิศวกรรีพอร์ต บอกมา และผมก็ลองแล้วเป็นอย่างนั้นจริงๆ ด้วย!!)
ส่วนของการปรับแต่ง Font ก็มีขีดเส้นใต้คู่ด้วยนะ 555
การตีกรอบก็มีได้หลายวิธีมากๆ ยิ่งถ้ากดไปใน More Border เราสามารถทำเส้นกรอบแบบแทยงมุมได้ด้วยนา
การจัดวางตัวอักษรก็ปรับได้หลายแบบ ถ้าเลือก Format Cell Alignment ก็จะปรับได้เยอะขึ้นไปอีก (กด ctrl+1 ก็ได้)
เคยกดพวก Shrink to Fit บ้างมั้ย? มันจะลดขนาด Font ให้พอดีช่องเองนะ
ส่วนการ Merge Cell ก็มีโหมด Merge Across ให้เลือกด้วย ซึ่งมันจะ Merge แบบแยกบรรทัดกันได้ด้วยนะ
การปรับ Number Format ก็มี Preset พื้นฐานให้เลือกพอสมควร ถ้ายังไม่ได้ดั่งใจก็ต้องไป More Number Format… แล้วไป Custom เอา
ส่วนการใช้ Conditional Format (ปรับ Format ตามเงื่อนไข) ก็มี Preset ให้เลือกเยอะเลย แต่ถ้ายังไม่พอใจ ก็ต้องไป New Rule… แล้ว Use Formula เอา เพื่อความ Flexible ขั้นสุดยอด
กลุ่ม Insert / Delete คิดว่าน่าจะเคยลองกดเล่นกันบ้าง
กลุ่ม Format นี่มีอะไนซ่อนอยู่เยอะเหมือนกันนะ
รู้หรือไม่ว่า AutoSum มันมี Function อื่นๆ ให้เลือกด้วยนะ
รู้มั้ยว่ามันมีคำสั่ง Fill ด้วยน่ะ แถมมีหลายทิศอีก และถ้าเลือก Series… ก็มี Option ให้เลือกอีกตูมเลย!
ทำ Growth ประหลาดๆ ก็ได้
ปุ่ม Clear ก็มีหลายแบบ แถมยัง Clear เฉพาะ Hyperlink ออกได้ด้วยนะ
Sort & Filter น่าจะใช้กันบ่อย
Find & Select นี่มีประโยชน์มากนะ ถ้าใช้เป็นทุ่นแรงได้เยอะมากๆ
Find… แล้วเลือก Option >>> ใน Look in นี่สำคัญมาก และพวก Match entire cell contents ก็ทำให้ร่วมกับพวกเครื่องหมาย wildcard ได้อีก
Go to Special นี่เจ๋งมาก การเลือกช่องที่ Blank ทำได้ง่ายๆ โดยเครื่องมือนี้
นี่แค่ Tab Home อันเดียวนะครับ ใน Tab อื่นก็ยังมีเครื่องมือเจ๋งๆ ซ่อนอีกเยอะ และจริงๆ ยังมีเครื่องมือที่ไม่อยู่ใน Ribbon ปกติ แต่เราเรียกใช้งานผ่าน Quick Access Toolbar ได้อีกเพียบเลยด้วยนา
ดังนั้นอย่าลืมกลับไปสำรวจเครื่องมือที่คุณมีอยู่แล้วให้ดี เพื่อดึงประโยชน์สูงสุดของ Excel ออกมาได้อย่างเต็มที่นะครับ!!
========================================
Post นี้คือ ส่วนหนึ่งของโครงการ inwexcel365tips ที่ผมจะ post บทความในเพจทุกวัน ต่อเนื่องกันเป็นเวลา 1 ปี ถ้าผมพลาดไม่ post ภายในเวลา 3 ทุ่ม คนที่มาทวง post คนแรก รับของแจกฟรีได้เลย !!
ตั้งแต่ 7 มีค. 2560 เป็นต้นไป… ในทุกๆ วันเวลา 2 ทุ่ม – 3 ทุ่ม
ผมจะ Post “Tips/เทคนิค Excel การใช้ Excel” ต่อเนื่องกันใน facebook เพจ inwexcel เป็นเวลา 1 ปี ! ไม่ให้ขาดแม้แต่วันเดียว (สิ้นสุด 6 มีค. 2561)
วันไหนก็ตามที่ผมพลาดไป ไม่ได้มีการ post บทความภายในเวลา 3 ทุ่ม ใครก็ตามที่มา ทวงpost ผมได้เป็นคนแรก
จะสามารถเลือกหนังสือ E-Book หรือ คอร์สออนไลน์ผมอันไหนก็ได้ ฟรี 1 อัน ทันทีครับ!
กติกาเล็กน้อยของเกม #ทวงpost
————
1. คนที่มาทวง post ต้องเป็นแฟนของเพจ inwexcel ด้วยนะครับ นั่นคือต้องกด Like เพจนี้ด้วย
2. เริ่มทวง post ได้ตั้งแต่เวลา 3 ทุ่มเป็นต้นไปครับ
3. ใช้วิธีการ post to page โดยต้องมี hashtag คำว่า #ทวงpost อยู่ใน ด้วย
4. การทวง post จะเป็นวันต่อวัน มีของแจกทุกวัน (ถ้าผมพลาด)
แบบนี้แหละ น่าจะเป็นการบังคับให้ผมขยัน post ด้วย แถมแฟนๆ ก็ได้ประโยชน์ด้วย win-win ทั้งสองฝั่ง ว่ามั้ย อิอิ
จากกระแสที่ร้อนแรงของรายการ The Mask Singer (หน้ากากนักร้อง) ที่จัดให้มีการร้องเพลงภายใต้หน้ากาก โดยให้ผู้ชมลุ้นว่าคนภายใต้หน้ากากคือใคร หากนักร้องคนไหนที่ตกรอบ ก็จะต้องมีการถอดหน้ากาก เพื่อเปิดเผยตัวตนที่แท้จริงของตัวเอง ส่วนผู้ชนะก็จะเข้ารอบต่อไป
ซึ่งรายการนี้จะมีการแบ่งนักร้องเป็น 4 สายเพื่อหาตัวแทนแต่ละสายมาแข่งกันในรอบ Final ในฐานะสุดยอดหน้ากากนักร้องทั้ง 4 คน…
ผมก็เลยคิดว่า ถ้าลองจับเอาฟังก์ชันใน Excel มาแสดงความสามารถเจ๋งๆ แล้วให้เพื่อนๆ ทายว่าจริงๆ แล้วมันคือฟังก์ชันอะไรกันแน่ก็น่าจะสนุกดีนะ
ผมก็เลยมีฟังก์ชันมาให้ทาย 4 ตัวเช่นเดียวกัน ซึ่งผมให้ทุกตัวแก้ปัญหาเดียวกันเลย นั่นคือ การ Lookup ข้อมูลแบบ Multiple Criteria โดยต้องดูข้อมูลจากคอลัมน์ A และ B เพื่อหาราคาในคอลัมน์ C มาให้ได้ !! (Advance ชะ?)
ทุกตัวต่างหาผลลัพธ์ได้ถูกต้องคือ ราคา 60 บาท แต่ทว่าแต่ละตัววาดลวดลายหาคำตอบด้วยวิธีที่แตกต่างกันอย่างมาก
ให้ลองทายเล่นๆ ได้ใน Comment ของเว็บนี้ หรือจะ Comment ใน Post ของ Facebook เพจ inwexcel ก็ได้ครับ
[yop_poll id=”1″]
ส่วนการเฉลย ผมจะขอคัดฟังก์ชันที่ตกรอบ 2 คน แล้วเฉลยผู้แพ้ 2 ฟังก์ชันแรกในวันที่ 10 มีค. 2560 นะครับ นั่นคือหลังจากรายการ The Mask Singer (หน้ากากนักร้อง) ฉายรอบแชมป์ชนแชมป์ 1 วัน (ฉาย 9 มีค. 2560 เวลา 2 ทุ่ม ) ซึ่งเค้าจะคัดออก 2 คนเหมือนกัน 555
และผมจะอธิบายการทำงานของฟังก์ชันแต่ละตัวที่ตกรอบด้วยเลยอย่างละเอียดในวันที่ 10 มีค. นะครับ ^^
========================================
Post นี้คือ ส่วนหนึ่งของโครงการ inwexcel365tips ที่ผมจะ post บทความในเพจทุกวัน ต่อเนื่องกันเป็นเวลา 1 ปี ถ้าผมพลาดไม่ post ภายในเวลา 3 ทุ่ม คนที่มาทวง post คนแรก รับของแจกฟรีได้เลย !!
นอกจาก Excel จะเป็นเครื่องมือที่ใช้ในการคำนวณได้อย่างเก่งกาจแล้ว มันยังสามารถนำมาใช้เป็นเครื่องมือในการ Track งาน/วาระประชุมต่างๆ ได้อย่างยอดเยี่ยมด้วย วันนี้ผมเลยจะมาแชร์เทคนิคที่คิดว่าน่าจะเป็นประโยชน์กับเพื่อนๆ ครับ (ถ้าใครมีแนวทางอื่นเจ๋งๆ ก็บอกผมด้วยนะ ^^)
เวลาอยากจะใช้ Excel ได้อย่างเต็มประสิทธิภาพ การเก็บข้อมูลในลักษณะตาราง Database จะเป็นการเริ่มต้นที่ดีที่สุด เพราะมันสามารถช่วยให้เราสามารถนำข้อมูลไปใช้ในความสามารถอื่นๆ ของ Excel ได้อีก เช่น Sort/Filter, PivotTable เป็นต้น
สำหรับคนที่ไม่รู้ว่าการเก็บข้อมูลในลักษณะตาราง Database เป็นยังไง… มันก็คือการเก็บข้อมูลโดยใส่ข้อมูลโดยมีหัวตาราง โดยเป็นตัวที่จะบอกว่าข้อมูลในคอลัมน์นั้นๆ คือเรื่องอะไร นั่นแปลว่าในคอลัมน์เดียวกัน ข้อมูลจะเป็นข้อมูลประเภทเดียวกัน เช่น ถ้าเป็นชื่อสินค้า ก็จะเป็นชื่อสินค้าทุกบรรทัด ถ้าเป็นวันที่ Deadline ก็จะต้องเป็นข้อมูล Deadline ทุกบรรทัด เป็นต้น
ดังนั้นสิ่งสำคัญคือ คุณจะต้องคิดว่าการจดงานของคุณควรจะมีคอลัมน์อะไรบ้าง? เช่น
ไหนๆ เราก็จะบันทึกข้อมูลในลักษณะของ Database แล้ว การปรับตารางปกติให้กลายเป็น ตารางแบบ Table ไปเลยก็จะได้ประโยชน์หลายอย่าง เช่น
ซึ่งวิธีทำก็ง่ายแสนง่าย ก็คือเลือกส่วนใดส่วนหนึ่งของตารางแล้วไปที่ Insert -> Table หรือกด Ctrl+t เป็นอันจบ
การใช้ Sort/Filter จะสามารถช่วยให้เราสามารถบันทึก/เลือกข้อมูลแก้ไขได้ง่ายขึ้น เพียงคุณมีซักคอลัมน์บอกว่าแถวนั้นๆ คือเรื่องอะไร แล้วใช้เครื่องมือ Sort/Filter คุณก็สามารถทำให้ข้อมูลที่เดิมกระจัดกระจายอยู่หลายบรรทัด สามารถมารวมอยู่ติดๆ กันได้อย่างง่ายดาย เช่น คุณอาจจะบันทึกประเด็นประชุมต่างๆ ไล่ไปเรื่อยๆ ทีละแถวๆ โดยไม่ต้องสนใจว่าแต่ละแถวเป็นเรื่องเดียวกันหรือไม่เพราะสามารถ Sort/Filter ได้ภายหลัง
เราสามารถเปิด/ปิด Auto Filter ได้โดยการกดปุ่ม Auto Filter บน Ribbon หรือจะใช้ Keyboard Shortcut: Ctrl+Shift+L ก็ได้
เวลาจดงานในที่ประชุม สิ่งสำคัญมากๆ อย่างหนึ่งคือ “ความเร็ว” นั่นเอง ดังนั้นเทคนิคในการบันทึก/แก้ไขข้อมูลแบบเร็วๆ จึงเป็นสิ่งที่จำเป็นที่ต้องเรียนรู้
และวิธีการบันทึกข้อมูลที่เร็วที่สุดอันหนึ่งก็คือ การใช้ Keyboard Shortcut นั่นเอง (มันเร็วกว่าการใช้ Mouse คลิ๊กมากๆ) ดังนั้นผมจะขอแนะนำ Shortcut ที่คิดว่าจะมีประโยชน์ต่อการบันทึกงานดังนี้ครับ
เราสามารถสร้าง Drop down List ให้คนกรอกข้อมูลได้เร็วและถูกต้องได้ง่ายๆ ซึ่ง Dropdown ที่จะนำมาใช้ได้
เช่น คนรับผิดชอบงาน/กลุ่มของงาน/Status ของงาน เป็นต้น
วิธีการทำก็ง่ายมาก ให้ไปที่ [Data] -> Data Validation -> List -> แล้วพิมพ์ตัวเลือกที่ต้องการ คั่นด้วย Comma หรือจะเลือก Range ของ Choice ที่ต้องการ หรือจะเลือกจากชื่อที่ตั้งเอาไว้แล้วก็ได้ (ในรูปผมตั้งชื่อ Range ของ Choice ที่จะให้เลือกว่า ลูกน้อง ตามวิธีด้านล่าง)
แต่เทคนิคที่หลายคนอาจไม่รู้คือ เราสามารถทำ Drop down List แบบที่เลือกแล้วยังแก้ข้อมูลต่อได้ด้วย ซึ่งจะเป็นการเน้นเรื่องของความเร็วในการกรอกข้อมูลมากกว่าความถูกต้อง เทคนิคคือ ใน Tab Error Alert ให้เอาติ๊กถูกออกไปตรงส่วนของ Show error alert after invalid data is entered
บางทีถ้าเรามีทุกอย่างให้เลือกใน Dropdown เลย คนเลือกอาจเลือกไม่ไหว เพราะเยาะมาก เราจึงอาจต้องทำให้มี Dropdown 2 ชั้น เพื่อให้ Dropdown แรกเป็นตัว scope ตัวเลือกของ Dropdown อันที่ 2 ให้น้อยลง => เทคนิคการทำ Dropdown 2 ชั้น
การกรอกข้อมูลให้ง่ายขั้นนั้นเป็นสิ่งที่ช่วยให้การจดงานสะดวกขึ้น แต่ที่เจ๋งกว่าการกรอกง่ายก็คือการไม่ต้องกรอกข้อมูลเลย
ดังนั้นการทำงานควรจะต้องคิดด้วยว่าเราสามารถเขียนสูตรอะไรเพื่อลดการกรอกข้อมูลที่ไม่จำเป็นได้บ้าง?
สมมติว่างานแต่ละบรรทัด เรามีการกรอกคนรับผิดชอบงานนั้นๆ ไปแล้ว เราก็อาจไม่จำเป็นต้องกรอกหัวหน้างานเข้าไปอีก เพียงแค่เราทำตารางที่บอกว่าลูกน้องแต่ละคนมีใครเป็นหัวหน้าบ้าง
ตารางอ้างอิง ชื่อหัวหน้่า
จากนั้น เราก็สามารถเขียนสูตรเพื่อดึงชื่อหัวหน้างานมาได้โอยอัตโนมัติได้แล้ว ง่ายมากๆ
** สัญลักษณ์ [@ชื่อหัวคอลัมน์] เป็นการอ้างอิงแบบ Structure Reference เมื่อมีการใช้ Table หมายถึงเอาข้อมูลในคอลัมน์นั้นจากแถวเดียวกัน**
ทำได้หลายแบบมากๆ เช่น
เพื่อให้แน่ใจว่าจะมีการกรอกข้อมูลได้อย่างถูกต้อง เครื่องมือ Data Valuidation เราสามารถเลือกคำสั่ง Allow เป็น Number, Text, Date เพื่อกำหนดประเภทข้อมูลที่ต้องการให้กรอกได้อย่างง่ายดาย เช่น ในช่อง “Deadline” อาจจะใส่เงื่อนไขว่า ห้ามกรอกวันที่ในอดีต โดยใส่ว่า Date ต้อง greater than or equal to =TODAY() เป็นต้น
และเมื่อกรอกข้อมูลผิด ก็ควรเตือนคนกรอกได้ว่ามันผิดยังไง คนกรอกจะได้แก้ให้ถูกต้อง เช่น
เวลากรอกผิดเงื่อนไขก็จะขึ้นเตือนมาได้
ปกติแล้วในช่องพวกวันที่/เวลา ถ้าเราใส่สูตร เช่น =TODAY() หรือ =NOW()
ค่าของมันจะเปลี่ยนไปเรื่อยๆ เมื่อ Excel มีการคำนวณใหม่ ทำให้ไม่สามารถคงวันที่ที่เราต้องการไว้ได้
ซึ่งเราสามารถแก้ปัญหานี้ด้วยการ Stamp ค่าจาก Keyboard Shortcut Ctrl+; และ Ctrl+: ก็ได้นะ เช่น
ซึ่งจะเห็นว่าการ Stamp วันที่และเวลา กดยากและไม่สะดวก.. ซึ่งผมมีอีกวิธีมาแนะนำ นั่นคือ การ Stamp ค่าด้วยสูตร!!
เราสามารถใช้เทคนิคการเขียนสูตรแบบ Circular Reference (วน Loop งูกินหาง) เพื่อให้ Excel Stamp วันที่+เวลา เมื่อเกิดเหตุการณ์ที่ต้องการได้ เช่น เมื่อมีคนเปลี่ยน Status เป็น Done เมื่อไหร่ ก็ให้ Stamp วันที่+เวลา ณ ตอนนั้นได้ทันที! เช่น
=IF([@สถานะงาน]="Done",IF([@วันจบงานจริง]="",NOW(),[@วันจบงานจริง]),"")
** ส่วน [@วันจบงานจริง] ในสูตรข้างบน คือ ส่วนที่เป็น Circular Reference หรืออ้างอิงกันเองแบบงูกินหาง ซึ่งปกติ Excel จะด่าเรา เวลาเราเขียนสูตรแบบ Circular นี้
แต่ถ้าเราไปตั้งค่าใน Excel Option -> Tab Formula -> ติ๊กให้ Enable Iterative Calculation มันก็จะทำงานได้!!
หลังปรับ Option แล้ว จะเห็นว่า พอลองแก้ Status เป็นอย่างอื่น แล้วเปลี่ยนเป็น Done อีกที สูตรก็จะทำงานแล้ว แต่ Format อาจจะผิด!
คุณสามารถใช้เทคนิค Circular นี้กับเลข Running Number ก็ได้ เช่น
=IF([@ชื่องาน]="","",IF([@ลำดับเลขงาน]="",ROW([@ลำดับเลขงาน])-1,[@ลำดับเลขงาน]))** หากใส่สูตรว่า =ROW([@ลำดับเลขงาน])-1 ธรรมดาก็ใช้งานได้ แต่ถ้า Sort ปุ๊ปทุกอย่างจะพังเลย
ตัวเลขวันจบงานที่ขึ้นมา เราใช้สูตรว่า NOW() แปลว่ามันจะต้องเป็นวันที่และเวลา
ซึ่งจริงๆ เลข 4 หมื่นแบบมีทศนิยมก็คือ วันที่+เวลานั้นแหละ แค่ยังไม่ได้ปรับ Format เท่านั้นเอง
หน้าที่ของเราก็คือปรับ Number Format ให้ถูกต้อง โดยคลิ๊กขวา -> Format Cell หรือกด Ctrl+1
นี่ไง ออกมาเป็นวันที่และเวลาแล้ววว
นอกจากนี้ยังมี Custom Number Format ที่น่าสนใจอีกเพียบ
เวลาเราเลือก Status ของงาน เช่น To do/Doing/Done เราอาจต้องการให้สีของพื้นหลังของ Cell เปลี่ยนไปตาม Status ที่ปลี่ยนไปได้ ซึ่งเราทำแบบนี้ได้ด้วยเครื่องมือ Conditional Formatting
เช่น ถ้า Status เป็น Done ผมจะให้สีของช่อง Status เป็นสีเขียว แบบนี้ทำง่ายมากโดยเลือกที่ช่อง Status แล้วเลือก
Conditional Formatting -> Highlight Cells Rules -> Equal to –> ใส่คำว่า Done แล้วเลือก Format ที่ต้องการ (ถ้าไม่ตรงใจให้เลือก Custom)
สมมติอยากให้ลำดับเลขงานและชื่องานที่เสร็จแล้วเป็นสีเขียวด้วย แบบนี้ต้องไปเลือกช่อง ลำดับเลขงานและชื่องาน
แล้วไปที่ Conditional Formatting -> New Rules -> Use Formula to determine…
จากนั้นใส่สูตรเพื่อเช็คเงื่อนไข ถ้าสูตรเป็นจริงก็จะ Apply Format ที่ตั้งค่าไว้
ในที่นี้ผมจึงต้องใส่สูตรว่า =$F2=”Done” โดยต้องใส่ $ที่คอลัมน์ ไม่ใส่ที่ Row เพราะต้องอ้างอิงกับคอลัมน์ F ตลอด แต่ให้เลื่อนบรรทัดลงไปเรื่อยๆ ได้
จากนั้นกำหนด Format ตามใจชอบ แล้วกด Ok ก็จะเห็นว่าสีเปลี่ยนตามสถานะได้แล้ว
เนื่องจากเราเก็บข้อมูลอยู่ในลักษณะ Database อย่างดีแล้ว เวลาเราจะวิเคราะห์/สรุปผลข้อมูลด้วยก็ทำได้ว่านแสนง่าย
โดยให้คลิ๊กที่ช่องไหนก็ได้ แล้วกด Insert PivotTable/PivotChart แล้วลากข้อมูลไปมา แปปเดี๋ยวก็เสร็จแล้ว
ถ้าไม่อยากทำไฟล์เอง ก็หา Template เจ๋งๆ มาใช้ซะเลย ในโลกอินเตอร์เน็ตมีให้โหลดมาใช้ฟรีเยอะแยะ ถ้าที่มีให้โหลดมันยังไม่โดนใจ 100% เราก็สามารถโหลดอันที่ถูกใจที่สุดมาดัดแปลงจนตรงกับความต้องการของเราก็ได้ครับ ^^
ใครมี Template อะไรเจ๋งๆ ฟรีๆ ก็บอกมาได้นะครับ
ใครมีเทคนิคอะไรดีๆ ในการทำงาน อย่าลืม Comment ลงในบทความนี้ เพื่อแบ่งปันให้คนอื่นรู้ด้วยนะครับ
หลักการสำคัญที่ผมเชื่อมาโดยตลอดก็คือ “ยิ่งให้ยิ่งได้” ยิ่งเราสอนคนอื่น เราเองก็จะเก่งขึ้นไปด้วยโดยธรรมชาติ
ยิ่งถ้ารู้ว่ามีคนได้ประโยชน์จากความรู้ที่เราบอกไป แค่นี้เราก็ฟินสุดๆ แล้วล่ะ
ยังไงก็ขอบคุณมากครับ ที่อ่านมาจนจบบทความ ถ้าถูกใจก็ฝากแชร์ให้เพื่อนๆ ของคุณรู้ด้วยนะครับ ^^
สมมติว่าเรามีข้อความบางอย่าง แล้วเราอยากจะแยกข้อความออกจากกัน
เช่น ตัวอย่างข้างล่างนี้ มีคั่นด้วย – (แต่ว่าตำแหน่งของมันไม่แน่ไม่นอน) เราจะแยกข้อความออกมาเป็น 3 ส่วนได้ยังไง? มาดูกัน
วิธีที่ง่ายที่สุดคือการใช้เครื่องมือ Text to Column โดยให้ Copy ข้อมูลต้นฉบับออกมาก่อน
แล้วเลือกข้อมูล แล้วไปที่ [Data]–> Data Tools –> Text to Column –> Delimited และให้ใส่เครื่องหมาย – (ที่เป็นตัวคั่น) ลงไปใน Other
จากนั้นกด Finish แค่นี้ก็เสร็จแล้ว ได้เป็น 3 คอลัมน์อย่างที่ต้องการ
การใส่สูตรมีข้อดีอย่างมาก คือ ข้อมูลอัปเดทโดยอัตโนมัติ โดยไม่ต้องกดเครื่องมือใดๆ เลย แต่ข้อเสียคือ เขียนยาก ถ้ายังใช้สูตรไม่คล่อง
ตัวต้นเป็นตัวที่หาง่ายสุด ใน 3 ตัว เพราะการใช้ฟังก์ชัน FIND หรือ SEARCH ซึ่งสามารถหาตำแหน่ง – ตัวแรกได้อยู่แล้ว
หาตำแหน่งตัวคั่นแรก : =FIND("-",A2)เอาข้อความข้างซ้าย : =LEFT(A2,FIND("-",A2)-1) ที่ต้อง -1 เพราะไม่ต้องการเครื่องหมาย – มาด้วย จึงต้องร่นไปทางซ้ายอีก 1 ตำแหน่ง
ตัวกลาง จะหาได้เราต้องรู้ตำแหน่งของ – ตัวหน้า และ – ตัวหลัง ซึ่งตัวหน้าเรารู้อยู่แล้ว
ซึ่ง – ตัวหลัง หากได้ 2 วิธี
วิธีแรก
=FIND("-",A2,FIND("-",A2)+1)วิธีสอง
ให้ SUBSTITUTE – ตัวที่ 2 ด้วยเครื่องหมายพิเศษอื่น เช่น |
=SUBSTITUTE(A2,"-","|",2) Tips : ถ้าไม่รู้ว่าข้อความมี – กี่ตัว?
ให้ลอง SUBSTITUTE ค่า “-” ด้วย “” แล้วลองนับจำนวนตัวอักษรดูว่าหายไปกี่ตัว
=LEN(A2)-LEN(SUBSTITUTE(A2,"-","")) จากนั้นให้ FIND ตำแหน่ง | อีกที จะได้
=FIND("|",SUBSTITUTE(A2,"-","|",2)) พอรู้ตำแหน่งของตัวที่ 2 ก็จะตัดตัวกลางและตัวท้ายได้ง่ายแล้ว
คำตัวกลาง
=MID(xxx, ตัวคั่นแรก +1, ตัวคั่นสอง - ตัวคั่นแรก -1)=MID(A2, FIND("-",A2) +1, FIND("|",SUBSTITUTE(A2,"-","|",2)) - FIND("-",A2) -1)
หาไม่ยากโดยใช้ RIGHT
แต่จะเอา RIGHT กี่ตัวดี? วิธีคิดคือเอาจำนวนตัวอักษรทั้งหมด – ตำแหน่งตัวคั่นที่สอง
=LEN(A2)-FIND("|",SUBSTITUTE(A2,"-","|",2)) จากนั้นก็ใช้ RIGHT ได้เลย
=RIGHT(A2, LEN(A2)-FIND("|",SUBSTITUTE(A2,"-","|",2)) ) 
ถ้า Excel 2016 จะมีมาให้เลย แต่ถ้าต่ำกว่านั้นต้องไป Download Add-in ก่อน (ฟรี) แต่โหลดให้ถูก version นะ ไม่งั้นจะลงไม่ได้
ถ้าพร้อมแล้วก็สร้าง Table ก่อน โดยเลือกข้อมูลแล้วกด Ctrl+T
จากนั้นไปที่ Data (หรือ Power Query)-> From Table
Add Column -> Duplicate Column แรกออกมาก่อน ไม่งั้นจะไม่เหลือตัว Original เก็บไว้
เลือก column ใหม่แล้วไปที่ Transform -> Split Column -> Delimiter
เลือก custom ใส่ – ลงไป แล้ว ok
ได้ผลลัพธ์ใน Power Query ดังใจแล้ว กด Close & Load ได้เลย
ผลลัพธ์จะกลับมาที่ Excel ใน Sheet ใหม่
จะเห็นว่าคล้ายวิธีแรก แต่ข้อดีกว่ามากๆ เลย คือ หากมีข้อมูลเพิ่มมา แค่กด Refresh ผลลัพธ์ก็จะเปลี่ยนและอัปเดทให้อัตโนมัติ!!
เพิ่ม Data ไปอีก 2 บรรทัด
ไปที่ผลลัพธ์ กดคลิ๊กขวา Refresh
ปรากฎว่า Data ใหม่มา แต่มีอันนึงไม่ยอมแยกข้อความให้
เราสามารถไปตรวจสอบได้โดยไปที่ Query –> Edit
ปรากฎว่า มีค่า Error ขึ้นมาบรรทัดนึงนี่เอง
พอลองไล่คลิ๊ก Applied Steps ดูก็พบว่า สามารถทำมาถึง Split Column by Delimiter ได้โดยไม่ Error เลย
ซึ่งแปลว่า Error น่าจะเกิดจากการทำ Changed Type Step สุดท้าย (เปลี่ยนตัวหนังสือเป็นตัวเลข)
เราจึงกด x ข้างหน้่า Changed Type อันสุดท้ายเพื่อบอกว่า Power Query ไม่ต้องทำ Step นี้แล้วนะ ลบ Step ทิ้งไปเลย
จากนั้นกด Close & Load เป็นอันจบ เสร็จแบบเนียนๆ เลย
ยาวหน่อยแต่หวังว่าจะถูกใจผู้อ่านนะครับ ใครสงสัยอะไรก็สามารถถามมาได้เลยครับ
นี่คือของขวัญที่ผมตั้งใจเตรียมไว้ให้กับทุกคน ด้วยบทความ “17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋” ซึ่ง Concept คืออ่านบทความเดียวแล้วเก่ง Excel ขึ้นอย่างรวดเร็ว!!
ผมไม่รู้ว่าจะเป็นไปได้มั้ย? แต่ก็อยากลองให้อ่านดูครับ ด้วยการคัดเลือกเคล็ดลับที่สำคัญในการใช้ Excel มีทั้งง่ายและยากปนกัน มันต้องมีบางอันที่ช่วยปลดล๊อคความสามารถของคุณบ้างล่ะ ลงทุนเวลาอ่านซัก 30 นาที รับรองว่าจะได้เวลาคืนมาอีกเพียบครับ !!
หมายเหตุ : จุดประสงค์ในบทความนี้จะเน้นให้คุณไม่พลาด Concept เจ๋งๆ ซึ่งบางทีอาจไม่สามารถอธิบายโดยละเอียดได้ในบทความเดียว ดังนั้นถ้าอ่านแล้วยังสงสัยในวิธีทำ ก็สามารถ Google หาวิธีทำละเอียดจาก Keyword ได้เลยครับ
หลักการนี้ คือ สิ่งที่ผมใช้ในการแก้ปัญหาเกือบทุกๆ อย่างใน Excel ซึ่งหลักการเหล่านี้เป็นแก่นที่แฝงอยู่ในเคล็ดลับข้ออื่นๆ ด้วย
ใช้ Keyboard Shortcut แล้วช่วยให้ทำงานเร็วขึ้นเยอะครับ ที่ต้องใช้บ่อยๆ มีไม่กี่ตัว ที่ผมใช้บ่อยก็มีตามนี้
รูปประกอบจากเพจ Excel Nana (ขอนำมาใช้เพราะชอบกราฟนี้มาก)
รูปประกอบจากเรื่อง iron man
อย่างที่ผมเคยย้ำเสมอว่า Excel มองวันที่เป็นแค่ตัวเลขจำนวนธรรมดาตัวหนึ่งที่เปลี่ยนหน้าตาไปเท่านั้น (รายละเอียดอ่านได้ที่นี่)
นอกจากนี้ Excel จะรู้จักวันที่ในรูปแบบที่มันคุ้นเคยเท่านั้น เช่น dd/mm/yyyy หรือ dd-mm-yyyy หรือ yyyy/mm/dd หรือ yyyy-mm-dd (กรณีที่ตั้งค่า Regional Setting ใน Control Panel เป็น Thai )
ดังนั้น ถ้าหากเราใส่วันที่ในรูปแบบที่ Excel ไม่รู้จัก เช่น 2016.12.09 มันก็จะไม่มองว่าเป็นวันที่หรอกนะ นอกจากนี้จะต้องระวังเรื่องการใส่เลขปีด้วย ว่าปกติแล้วควรใส่เป็นปี ค.ศ. เสมอ (ยกเว้นจะตั้งค่าใน Number Format ให้รับ Input เป็น พ.ศ. ไทยไปเลย มีบอกวิธีท้ายบทความ)
แต่บางครั้งเราได้ข้อมูลมาจากคนอื่น (หรือแม้แต่ฐานข้อมูลที่ Office) ซึ่งดันมีข้อมูลวันที่อยู่ในรูปแบบที่แย่ๆ Excel มันก็เลยไม่รู้จัก เราก็เลยต้องยื่นมือไปช่วยหน่อยแล้ววววว!!!
สมมติว่ากรอกวันที่แย่ๆ อยู่ในช่อง A1 นะครับ
วิธีแก้อาการนี้ ทำง่ายๆ ครับ โดยใช้ความรู้เรื่องฟังก์ชั่น TEXT กับ Custom Format มาช่วย ได้ดังนี้
=TEXT(A1,”0000-00-00″)*1 จะได้เป็นตัวเลขที่ถูกต้องแล้ว แต่ถ้าอยากเห็นเป็นวันที่ ก็ให้ Format เป็นวันที่เป็นอันจบ
วิธีแก้ ผมแนะนำ 2 วิธีแล้วกัน
แบบแรก ตัดทีละส่วนแล้วรวมกันด้วย DATE จะได้ว่า
=DATE(LEFT(A1,4)-543,MID(A1,5,2),RIGHT(A1,2))
แบบที่สอง ใช้ TEXT เหมือนเดิม จะได้ว่า
=TEXT((LEFT(A1,4)-543)&RIGHT(A1,4),”0000-00-00″)*1
คล้ายกับวิธีที่ 1 ครับ แต่ปรับ Format เล็กน้อย
=TEXT(A1,”00-00-0000″)*1
คล้ายกับวิธีที่ 2 ครับ มี 2 วิธี
แบบแรก =DATE(RIGHT(A1,4)-543,MID(TEXT(A1,”00000000″),3,2),LEFT(TEXT(A1,”00000000″),2))
แบบที่สอง = TEXT(LEFT(TEXT(A1,”00000000″),4)&RIGHT(A1,4)-543,”00-00-0000″)*1
วิธีแก้ง่ายมาก ก็แปลงจุด (หรือสัญลักษณ์อะไรก็แล้วแต่) ให้เป็นตัวที่ Excel รู้จักเช่น – ด้วย SUBSTITUTE ก็พอครับ
=SUBSTITUTE(A1,”.”,”-“)*1
หลักการก็คือทำให้ . หายไปก่อนด้วย =SUBSTITUTE(A1,”.”,””) แล้วเอาไปเข้ากรณีวันแย่แบบที่สองครับ ซึ่งจะได้ว่า
=DATE(LEFT(A1,4)-543,MID(SUBSTITUTE(A1,”.”,””),5,2),RIGHT(A1,2))
อีกแบบก็จะได้ว่า
=TEXT((LEFT(A1,4)-543)&RIGHT(SUBSTITUTE(A1,”.”,””),4),”0000-00-00″)*1
เช่น DDMMYY โดยปีเป็น ค.ศ. แบบนี้เราก็ต้องรู้ก่อนว่า ปีเป็น 19YY หรือ 20YY
ถ้ามั่นใจว่าเป็น 20YY ก็ทำแค่นี้พอ =TEXT(A1,”00-00-00″)*1
แต่ถ้าเป็น 19YY ก็อาจจะต้องเติมตัวเลข 19 ให้มัน เช่น =DATE(“19″&RIGHT(A1,2),MID(TEXT(A1,”000000″),3,2),LEFT(TEXT(A1,”000000”),2))
แบบนี้ ถ้าคิดว่ามีเครือ่งหมายประหลาดอะไรก็แนะนำว่าให้ใช้ SUBSTITUTE ตัดทิ้งให้หมดก่อน
หลังจากนั้นคุณต้องเขียนสูตรเพื่อตรวจเช็ครูปแบบแล้วล่ะ
เช่น ใช้ LEFT RIGHT เช็คดูว่าเลขน่าจะเป็น ค.ศ. หรือ พ.ศ.? และมีกี่ตัวอักษร??
จากนั้นค่อยเอาไปใช้ IF หรือ CHOOSE เพื่อเลือกสูตรที่เหมาะสมต่อไป
แต่ถ้าเกิดเหตุการณ์แบบนี้จริงๆ ไปแก้ตั้งแต่ต้นเหตุดีกว่า ว่าทำไมถึงกรอกข้อมูลได้มั่วขนาดนี้!!!!
อย่างน้อยก็ควรใส่ Data Validation ไว้บ้างนะครับ เช่น
อย่างที่บอกว่าเราควรกรอกข้อมูลเป็น ค.ศ. เสมอ แต่ถ้าคนกรอกดึงดันว่าจะกรอก พ.ศ.ล่ะ !!?
วิธีที่ทำให้กรอกข้อมูลเป็น พ.ศ. แล้วข้อมูลไม่เพี้ยน คือต้องเลือก Cell ช่องนั้นๆ แล้วเลือก Number Format ดังนี้ครับ
เป็นอย่างไรกันบ้างครับ กับเทคนิคการรับมือกันวันแย่ๆ ใน Excel ผมหวังว่าจะเป็นประโยชน์กับเพื่อนๆ นะครับ
หากใครมีเทคนิคอื่นๆ ในการจัดการกับปัญหาเรื่องวันที่ อย่าลืมแชร์กันให้ผมและเพื่อนๆ รู้บ้างนะครับ เพราะปัญหาหนึ่งๆ มันมีหลายทางแก้ไขแน่นอน ^^
เคยบ้างมั้ย? เวลาใช้ VLOOKUP แล้ว Error แบบไม่รู้สาเหตุ???
ฐานข้อมูลหรือตารางอ้างอิงก็เห็นอยู่จะๆ ว่ามีตัว Lookup_Value ที่เรากรอกลงไป (เลข 100) และก็ดูเหมือนว่าเขียนสูตร VLOOKUP ถูกต้องหมดทุกอย่างแล้ว แต่ผลลัพธ์กลับออกมาเป็น Error ซะอย่างนั้น?
เอ ทำไมหว่า?? ….
หรือว่าผี Excel หลอก!!
ไม่ใช่หรอกครับ ผมจะบอกว่าสาเหตุนึงที่คนมักจะลืมนึกไปก็คือ ประเด็นเรื่อง “ประเภทของข้อมูล” นั่นเอง
รู้หรือไม่ว่าพวก VLOOKUP MATCH หรือแม้แต่การใช้เครื่องหมาย = เพื่อเปรียบเทียบข้อมูล 2 ตัวเข้าด้วยกัน ถ้ามันเป็นข้อมูลคนละประเภท ไม่ว่าจะหน้าตาเหมือนกันแค่ไหน Excel ก็จะถือว่าเป็นข้อมูลคนละตัวกันทันที
เราไม่สามารถรู้ประเภทของข้อมูลได้ด้วยตาเปล่า เพราะอาจถูกลวงด้วยการปรับ Format เช่น ในรูปผมปรับให้ข้อมูลอยู่กึ่งกลางทั้งหมด จึงไม่มีทางดูออกเลยว่าเป็น Number หรือ Text
วิธีที่จะเช็คได้ดีที่สุดคือใช้ฟังก์ชั่น TYPE มาเช็คครับ
ซึ่งแปลความหมายได้ดังนี้ 1 = Number 2= Text 4= Logic 16 = Error
พอลองเช็คดูจะเห็นว่าในตารางอ้างอิง ตัว ID เป็น Text แต่ใน Lookup_Value เป็น Number
นี่คือสาเหตุที่ว่า VLOOKUP หาค่า 100 ในตารางไม่เจอ ทั้งๆ ที่เรามองเห็นอยู่เต็มตาแท้ๆ !!
วิธีแก้ก็คือ ต้องทำให้ข้อมูลเป็นประเภทเดียวกัน ถ้าไม่แก้ที่ตารางอ้างอิง ก็ต้องแก้ที่ Lookup_Value
หรือ ผมอาจจะเขียนสูตรเพื่อบังคับให้มันเปลี่ยนประเภทข้อมูลเป็น Text ไปเลย โดยการใส่ &”” ก็ได้
เป็นไงบ้างครับ เรื่องเล็กๆ น้อยๆ ถ้ารู้ก็ไม่ยากเลยใช่มั้ยครับ แต่ถ้าไม่รู้ก็อาจงงไปอีกนาน ^^
ฟังก์ชันยอดฮิตในการ Map ข้อมูลที่หลายคนชอบใช้ก็คือ VLOOKUP ซึ่งมีข้อดีคือ ใช้ฟังก์ชันเดียวก็ Map ข้อมูลได้ดั่งใจ
แต่ข้อเสียคือถ้าข้อมูลอยู่ด้านซ้ายของคอลัมน์ที่จะค้นหา มันจะเอาค่ากลับมาไม่ได้!
แล้วที่นี้จะทำไงดี??
ถ้าคุณไม่สามารถจะย้ายคอลัมน์ข้อมูลดิบได้ วิธีจัดการกับปัญหานี้ที่อยากจะแนะนำ คือ เปลี่ยนไปใช้ฟังก์ชั่นอื่นซะเถอะครับ (อ้าว)
ตัวที่ผมอยากจะแนะนำที่สุดก็คือ INDEX+MATCH เพราะนอกจากจะเอาข้อมูลมาได้ทั้งทางซ้ายและขวา ยังทนทานต่อการแทรกคอลัมน์อีกต่างหาก (แทรกแล้วข้อมูลที่ Lookup มาก็ยังถูกต้องอยู่)
ก่อนอื่น ผมขอทวนการทำงานของ INDEX แบบสั้นๆ นั่นคือ มันจะดึงข้อมูลตามพิกัดที่เราระบุได้
เช่น หากผมเขียนสูตรแบบนี้ ซึ่งเป็นการบอกว่า ในพื้นที่ A2:A5 นั้น ให้เอาข้อมูลที่อยู่ลำดับที่ 2 กลับมานะ ก็จะได้คำตอบ T004
แต่เรื่องของเรื่องคือ ที่เราใส่เลข 2 ได้ เพราะเราดูด้วยตาเปล่าว่า เจ้าสมหญิง อยู่ลำดับที่ 2
แต่ถ้าเราจะให้ Excel ช่วยหาให้ เราก็ต้องใช้ MATCH มาแทนเลข 2 ในส่วนของ row_num ดังนี้
ซึ่งเป็นการบอกว่า ให้กาคำในช่อง B9 ในช่วง B2:B5 ว่าอยู่ลำดับที่เท่าไหร่??
(ใส่ Match_Type เป็น 0 เพราะจะหาข้อมูลแบบต้องเจอเป๊ะ หรือ Exact Match)
พอ Enter ก็จะได้คำตอบคือ T004 ตามต้องการ และเมื่อลองเปลี่ยนชื่อดู ก็จะได้ผลลัพธ์ที่ถูกต้องอยู่
แต่วิธีเขียนสูตรที่ยืดหยุ่นกว่าก็คือ แทนที่จะเลือก Range ให้เริ่มแถว 2 แล้วจบแถว 5 ก็ให้เลือกมันทั้งคอลัมน์เลยก็ได้ ถ้าข้อมูลไม่เยอะจนเกินไป ก็ไม่น่าจะทำให้ช้ากว่ากันเท่าไหร่ครับ (ถ้าข้อมูลเยอะ ก็ต้องลากครอบเยอะอยู่ดี ซึ่งก็นานอยู่ดี)
=INDEX(A:A,MATCH(B9,B:B,0))
สำหรับบทความนี้ก็ขอจบเพียงเท่านี้ครับ สั้นๆ ง่ายๆ หากใครไม่เข้าใจตรงไหนก็ถามได้ครับ
เมื่อเร็วๆ นี้เองผมเพิ่งได้รับมอบหมายงานจากหัวหน้า ให้หาเหตุผลที่พนักงานขายทำการขายสินค้า/บริการไม่สำเร็จ โดยจะต้องวิเคราะห์จากข้อความที่เป็นคำบรรยายที่พนักงานขายแต่ละคนเขียนมาให้ ซึ่งข้อมูลมีเยอะมาก ประมาณหลายหมื่นข้อความเลย (โอ้ แม่เจ้า!!)
ดังนั้นจะให้ผมนั่งอ่านทีละอันคงไม่ไหวแน่นอน ผมจึงลองเขียนสูตรใน Excel เพื่อพยายามแบ่งกลุ่มเหตุผลขึ้นมาว่าน่าจะมีเหตุผลอะไรบ้าง แต่ละเหตุผลคิดเป็นสัดส่วนซักกี่ % ?
ซึ่งก็ได้ผลลัพธ์ที่น่าพอใจพอสมควรครับ ^^
ดังนั้นผมจึงอยากนำแนวคิดนี้มาลองแบ่งปันกับเพื่อนๆ เผื่อจะเป็นประโยชน์กับคนอื่นที่อาจต้องทำอะไรคล้ายๆ ผม และบางทีคุณอาจมีเทคนิคที่เจ๋งกว่าผมก็ได้ (ถ้ามีก็บอกกันด้วยนะ อิอิ)
ผมขอสมมติสถานการณ์ใหม่ขึ้นมาว่าเราเป็นบริษัทขายของแห่งหนึ่ง แล้วเราได้รวบรวมคำบ่นที่ลูกค้าด่าเรามาไว้ได้หลายพันข้อความเลย (แต่ในไฟล์มีแค่ 15 ข้อความ เพื่อความง่าย) => keyword-analysis (โหลดไฟล์ที่เสร็จสมบูรณ์ได้ที่นี้)
หน้าที่ของคุณคือต้องวิเคราะห์ว่าลูกค้าบ่นเรื่องอะไรมามากน้อยแค่ไหน เพื่อที่จะได้หาทางแก้ปัญหาได้ถูกจุด
ซึ่งหน้าตาข้อมูลเป็นแบบนี้
สิ่งที่เราต้องทำต่อคือ พยายาม List Keyword ที่น่าจะช่วยแบ่งกลุ่มข้อมูลได้ เช่น สมมติผมอยากจะแบ่งประเด็นปัญหาเป็น 2 เรื่องใหญ่ๆ ว่าเป็นเรื่องสินค้า (product) หรือ เรื่องของการบริการ (service)
สมมติผมอ่าน comment คร่าวๆ แล้วแบ่งคำได้ดังนี้
Product
Service
สมมติพอแค่นี้ก่อน…
ต่อไป เรา List Keyword เหล่านี้ลงแต่ละคอลัมน์ แล้วพยายามจัด group เพื่อให้ไม่งง
ต่อไป เราก็จะพยายามเขียนสูตร เพื่อหาว่าแต่ละบรรทัด มี Keyword เหล่านี้อยู่ใน Comment ที่ลูกค้าบ่นหรือไม่?
ซึ่งวิธีการหา ก็มีอยู่ 2 วิธีหลักๆ คือ ใช้ FIND หรือ SEARCH ซึ่งมีการทำงานต่างกัน คือ…
FIND จะสนเรื่องตัวพิมพ์เล็กพิมพ์ใหญ่ด้วย เช่น
แต่ถ้าเราใช้ SEARCH จะเจอทั้งคู่ !!
ดังนั้นในสถานการณ์แบบนี้ผมชอบจะใช้ SEARCH มากกว่า (ไม่งั้นผมต้องใส่ทุก Combination ที่เป็นไปได้ลงไปเลย) และนอกจากนี้ SEARCH ยังหาคำแบบ Wild Card ด้วยด้วย เช่น หาคำว่า b?t จะเจอกับทั้ง bat but bet bit bot … และอีกมากมาย เพราะ ? แทนอะไรก็ได้ 1 ตัวอักษร (รายละเอียดลองหาในบทความเก่า ๆได้)
เมื่อตัดสินใจได้แล้วว่าจะใช้ SEARCH เราก็ต้องมาดูต่อว่ามันทำงานแบบไหน? ซึ่งมันจะหาว่าคำที่เราค้นหาอยู่ตำแหน่งตัวอักษรที่เท่าไหร่ ซึ่งถ้าหาไม่เจอจะให้ค่าเป็น error แบบ #N/A กลับมาเลย
แต่ผมอยากเขียนสูตรให้เป็นว่า ถ้าหาเจอ ให้เป็นเลข 1 ถ้าไม่เจอให้เป็นเลข 0 ดังนั้นผมจะใช้ IF มาช่วย ดังนี้
ต่อไปผมก็ copy ลากยาวให้ครบทั้งหมด
ต่อไปผมจะมาจัดกลุ่ม โดยถ้าอยู่ในกลุ่มแรก จะต้อง flag ช่อง product เป็น 1 ถ้าอยู่ในกลุ่มหลัง ก็จะ flag service เป็น 1
ผมก็จะเขียนสูตรง่ายๆ ว่า ถ้าเอาค่าหลายๆ คอลัมน์บวกกันแล้วมากกว่า 0 (มีตัวใดตัวหนึ่งเป็น 1) ให้ flag ตัวกลุ่มเป็น 1 ดังนี้
ทำเหมือนกันกับอีกกลุ่ม
เพียงเท่านี้เราก็ได้ 2 กลุ่มแล้ว
ต่อไป เราก็จะเก็บตก ว่าอาจมีเหตุผลอื่นๆ ได้อีก เราก็แค่เช็คว่า ถ้าก่อนหน้านั้นรวมกันได้ 0 หมดเลย เราก็จะ flag อื่นๆ เป็น 1 เท่านั้นเอง
เพียงเท่านี้เราก็ flag ข้อมูลเพื่อจัดหมวดหมู่ได้แล้ว
ถ้ายังจัดได้ไม่ดีพอ เราอาจต้องใส่ keyword เพิ่มลงไปอีก ก็แค่แทรก keyword ใหม่เข้าไปตรงกลาง สูตรทุกอย่างก็จะยังใช้ได้อยู่
เช่น ผมจะใส่คำว่า “หัก” ลงไปในกลุ่ม product ก็แทรกคอลัมน์ได้เลย
แล้วใส่ Keyword ที่ต้องการ แล้วใช้สูตรจากเพื่อนข้างๆ
นอกจากนี้เราเอาข้อมูลไป pivot ก็ได้แบบง่ายๆ ชิลๆ
ถ้าใช้วิธีแบบนี้ มีสิทธิ์ที่ข้อมูล ใน 1 บรรทัด จะถูก flag ว่าเป็นทั้ง product และ service
ถ้าอยากให้เป็นได้แค่ตัวเดียว ก็ต้องเขียนสูตรเพิ่มอีกคอลัมน์นึงเพื่อสรุปว่าสุดท้ายแล้วจะเลือกว่าเป็นประเด็นเรื่องไหนเป็นหลัก แต่ในที่นี้ผมไม่ทำให้ดูแล้วกันครับ
เป็นยังไงบ้างครับกับเทคนิคหา keyword ในบทความนี้? ไม่ยากเกินไปจริงมั้ยครับ แต่มีประโยชน์มากๆ ลองเอาไปใช้ดูนะครับ
ใครอ่านบทความนี้แล้วถูกใจ อย่าลืมบอกต่อเพื่อนๆ ด้วยนะครับ เผื่อจะเกิดประโยชน์กับคนอื่นๆ เช่นเดียวกัน ^^
จากตอนที่แล้วที่ผมได้อธิบายเฉลยข้อสอบเข้าโรงเรียนจอมเวทเทพเอ็กเซล ไป 2 เรื่องแล้ว วันนี้มาต่ออีกเรื่องซึ่งเป็นข้อที่มีคนผิดมากที่สุดครับ โดยโจทย์ที่ถามคือ…
8) ตามรูป หากเขียนสูตรว่า =IF(A1<31/12/2016,”ยังไม่หมดเขต”,”หมดเขตแล้ว”) ผลลัพธ์จะได้ออกมาเป็นอะไร?
(A1 คือ วันที่ 29 สิงหาคม ค.ศ. 2016)
ข้อนี้หลายคนโดนหลอกอย่างจัง เพราะข้อนี้คำตอบคือ “หมดเขตแล้ว”
สาเหตุคือ เวลาเราเขียนค่าที่เป็นวันที่ลงไปในสูตร หากเราดันไปเขียนว่า =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 วิธีด้วยกันนะครับ ลองเอาไปเลือกใช้ดูตามความเหมาะสม
วิธีนี้เป็นวิธีที่น่าสนใจอีกอันหนึ่ง เพราะสามารถเขียนในรูปแบบวันที่ แต่สามารถคำนวณได้อย่างถูกต้องได้ด้วย
วิธีเขียนคือ ให้เขียนวันที่ในเครื่องหมายคำพูด แล้วเอาไป convert ให้เป็น Number โดยการเอาไปทำอะไรซักอย่างกับ Math Operator เช่น *1, +0, ใส่ – นำหน้า เป็นต้น (ส่วนตัวผมชอบใส่ – นำหน้า เพราะสังเกตง่าย และคำนวนเร็วสุด)
ซึ่งพอกด 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 แบบที่เราคิดไว้เท่านั้น
วิธีนี้เป็นวิธีที่ดีตรงที่เห็นชัดว่าเรากำลังเปรียบเทียบกับวันอะไรอยู่?
ซึ่งพอกด Enter ผลลัพธ์ก็จะออกมาเป็น “ยังไม่หมดเขต” ตามที่ควรจะเป็น
วิธีนี้สิ่งที่เราต้องทำคือ จงแน่ใจว่าเราใส่รูปแบบวันที่แบบเดียวกับวันที่ที่นำมาเปรียบเทียบ เช่น ถ้าเป็น DD/MM/YYYY ก็ใส่ให้เหมือนกัน หรือถ้าเป็น MM/DD/YYYY ก็จงทำให้เหมือนกัน ไม่่งั้นอาจผิดเพราะเรื่อง Format วันที่ของ Region and Language แบบเดียวกับข้อข้างบนได้
วิธีนี้มีความปลอดภัยสูงที่สุด เพราะจะไม่มีปัญหาเรื่องการตั้งค่า Format วันที่ของ Region and Language ของคอมพิวเตอร์แต่ละเครื่องเลย เพราะฟังก์ชั่น DATE จะถามหา Argument แยกกัน นั่นคือ ปี เดือน วัน แต่ละตัวแยกกันโดยไม่ปนกันเลย
ดังนั้นถ้าถามผมว่าวิธีไหนน่าจะ Work และปลอดภัยที่สุด ความเห็นส่วนตัวของผมคือ วิธีสุดท้ายนี่แหละครับ ^^
เอาล่ะ ใครอ่านจบถึงตรงนี้ ถ้าหากมีคำถามอะไร อย่าปล่อยผ่านนะครับ ถามมาได้เล้ยยยย
จากที่ผมได้ Post ข้อสอบเข้าโรงเรียนจอมเวทเทพเอ็กเซลให้ลองทำกันไปเมื่ออาทิตย์ก่อน (ใครยังไม่ได้ลองทำ เชิญไปทำได้ที่นี่ จะได้รู้ว่าเนื้อหาตรงไหนที่เรายังไม่แม่นนะครับ https://goo.gl/forms/5hqxIOH7tGDk9F3D2 )
ข้อสอบที่มีคนผิดมากเป็นอันดับต้นๆ และเป็นเรื่องที่เกี่ยวเนื่องกัน นั่นคือเรื่องของ “ค่าที่แท้จริง วันที่ และเวลา” ซึ่งมีอยู่ 3 ข้อ นั่นคือ
ดังนั้นผมจะขอนำมาเขียนอธิบายแบบละเอียดให้เข้าใจกันอย่างแจ่มแจ้งอีกครั้งนะครับ โดยในบทความนี้เรามาดู ข้อ 6 กับข้อ 9 กันก่อนครับ บทความถัดไปจะมาดูข้อ 8 กัน
ข้อ 6) จากรูป หากเขียนสูตรใน A2 ว่า =A1+1 จะได้ค่าเท่าไหร่?
ข้อนี้ถ้าไม่สังเกตให้ดีอาจโดนหลอกได้ง่ายๆ เพราะค่าใน Cell เป็นเลข 3 แต่ค่าใน Formula Bar เป็น 2.5
ซึ่งค่าใน Cell ที่เรามองเห็นเกิดจากการเอา “ค่าที่แท้จริง” ไปปรุงแต่ง แต่งตัว แต่งหน้า” ผ่านการปรับ Formatting หรือรูปแบบการแสดงผล จนสุดท้ายออกมาเป็นผลลัพธ์ที่เรามองเห็นด้วยตาเปล่าได้
แต่เรื่องของการแสดงผลก็เป็นเพียงแค่เปลือกนอกเท่านั้น ถึงเราจะปรับเลข 2.5 ให้ไม่แสดงทศนิยมจนแสดงผลออกมาเป็นเลข 3 เวลานำมันไปคำนวณต่อ Excel จะเอาค่าที่แท้จริง คือ 2.5 ไปคำนวณต่อ ไม่ได้เอาการแสดงผลหรือเลข 3 ไปคำนวณนะครับ (ยกเว้นว่าใน Excel Option จะไปตั้งค่า Set precision as displayed ในเมนู Advanced ซึ่งปกติแล้วจะไม่ตั้งค่าแบบนั้น)
ดังนั้นสิ่งที่ต้องจดจำไว้ให้ดีคือ “ค่าที่เห็น…อาจไม่ใช่ค่าที่เป็น” การปรับ Format ทั้งหน้าตาและ Number Format จะไม่มีผลใดๆ ต่อการคำนวณทั้งสิ้น
ข้อ 9) เริ่มทำงาน ด้วยเวลาในช่อง B1 เสร็จงาน ด้วยเวลาในช่อง B2 จะคำนวณว่าใช้เวลากี่นาที ในช่อง B3 ยังไง?
ก่อนที่จะคำนวณข้อนี้ได้ เราจะต้องมีความเข้าใจเรื่องที่ว่า วันที่และเวลา จริงๆ แล้วคือตัวเลขธรรมดาๆ ที่เปลี่ยน Number Format ไปเท่านั้น (อย่างที่อธิบายข้างบนว่า การปรับ Format ไม่มีผลต่อการคำนวณ)
โดยที่ Excel จะแทนวันที่ 1/1/1900 (1 มกราคม คศ. 1900) ด้วยเลข 1 (ค่าที่แท้จริงของวันที่ 1/1/1900 คือ เลข 1) แล้วมันก็แทนวันที่ 2/1/1900 ( 2 มกราคม คศ. 1900) ด้วยเลข 2… ทำอย่างนี้ไปเรื่อยๆ
เช่น หากใน Cell ใส่วันที่เป็น 16/10/2016 แล้วลองเปลี่ยน Number Format ให้เป็น Number จะเห็นว่ากลายเป็นเลข 42659 นั่นเอง (ไม่ต้องจำเลขนะ…)
เมื่อ Excel แทนวันที่ด้วยจำนวนเต็ม ดังนั้น เวลา (ซึ่งคือส่วนหนึ่งของวัน) ก็จะถูกแทนด้วยเลขทศนิยมนั่นเอง เช่น
ทีนี้ถ้าเรากรอกเป็น 16/10/2016 13:25:00 แล้วลองเปลี่ยนเป็น Number จะได้เป็นเลข 42659.5590277778 (ลองเพิ่มทศนิยมดู) ซึ่งจริงๆ แล้วมันก็คือการแปลงวัน+เวลา ชั่วโมงและนาที ให้มีหน่วยเป็น “วัน” นั่นเอง
ดังนั้นถ้าเรามีเวลาเริ่มและจบแล้ว เราจะหาเวลาที่ผ่านไปได้ง่ายมากๆ โดยเอาค่าทั้งสองมาลบกัน
เช่น เอา B2-B1 จะได้ 1.784722222 แบบนี้ ซึ่งจะมีหน่วยเป็นวัน
ถ้าหากโจทย์ต้องการคำตอบที่หน่วยเป็นนาที เราก็แค่ต้องแปลงหน่วยจากวันเป็นนาที ซึ่งง่ายมากๆ แค่เพียงเรารู้หลักการของการแปลงหน่วยเท่านั้น นั่นคือ การทำให้หน่วยตัดกันจนให้ได้ผลลัพธ์เป็นหน่วยสุดท้ายที่ต้องการ โดยการแทนเลข *1 ด้วยอัตราส่วนที่เป็นความจริง เช่น
ดังนั้น (B2-B1) วัน คือ
เห็นมั้ยครับ? การคำนวณเรื่องวันที่และเวลาไม่ได้ยากอย่างที่คิดเลย แค่ต้องเข้าใจ Concept ของมันเท่านั้นเอง
ใครยังสงสัยตรงไหนก็ถามได้เลยนะครับ อย่าปล่อยผ่านไปแบบไม่เข้าใจแบบเคลียร์ๆ นะ ^^
ข้อสอบนี้ เป็นแบบทดสอบวัดระดับพลัง Excel ระดับพื้นฐาน-กลาง
คนที่ทำได้ดี น่าจะอ่านหนังสือเล่ม 2 ของผม ชื่อ “จอมเวท เทพ Excel”
ที่กำลังจะออกประมาณปลายเดือนตุลาได้โดยไม่มีปัญหาครับ
(ดูรูปตัวอย่างได้ใน Facebook
https://www.facebook.com/inwexcel/photos/?tab=album&album_id=628710610640299
และ https://www.facebook.com/inwexcel/photos/?tab=album&album_id=639852389526121)
ใครที่ยังทำได้ไม่ดี ก็ลองกลับไปทบทวนข้อที่ทำผิดอีกทีนะครับ (มีเฉลยหลังทำเสร็จ)
มี 30 ข้อ (เอง) ทำแป๊ปเดียวเสร็จ (คนคล่องๆ ทำแค่ 2-3 นาทีก็เสร็จนะ) ค่อยๆ ลองทำโดยไม่เปิด Excel นะครับ
จะได้รู้ว่าความรู้ Excel ของคุณมีมากน้อยแค่ไหน
ใครทำเสร็จแล้วให้กดปุ่ม “View Your Score” เพื่อดูเฉลยนะครับ ผลเป็นยังไง อย่าลืมมาบอกกันด้วยนะครับ
ถ้าชอบก็อย่าลืมแชร์ให้เพื่อนๆ เล่นด้วยนะครับ ^^
ช่วงนี้กระแส Pokemon GO! กำลังมาแรง ผมเองก็เล่นอยู่บ้าง เลยคิดว่าเอา Pokemon มาสอน Excel ดีกว่า 555 สำหรับคนที่ Level 5 ขึ้นไป จะสามารถเข้าไปตี Gym ได้ ซึ่งจะต้องเอา Pokemon มาสู้กัน
หนึ่งในปัจจัยที่มีผลต่อการแพ้ชนะนอกจากพลัง CP ก็คือ เรื่องของธาตุหรือประเภทของ Pokemon ที่สู้กันนั่นเอง เพราะธาตุแต่ละธาตุมีเรื่องที่แพ้ทางกัน ดังนั้นต้องเลือกการโจมตีอย่างเหมาะสม ซึ่งใน Internet ก็มีข้อมูลพวกนี้เยอะแยะเลย
ซึ่งผมลองเอาข้อมูลมาทำตารางใน Excel โดยสามารถให้เราเลือกประเภท Pokemon ที่เป็นฝ่ายรุก ฝ่ายรับ เพื่อดูว่าผลเป็นยังไงได้
ดาวน์โหลดไฟล์ได้ที่นี่ => PokemonGo ThepExcel
วิธีทำเริ่มจากสร้าง Dropdown ให้เลือกธาตุของฝ่ายรุก และฝ่ายรับ แยกช่องกันไว้
จากนั้นใช้ match เพื่อดูว่าอยู่ row ไหน และ column ไหนของตาราง
หาลำดับคนโจมตี : MATCH(B3,$A$6:$S$6,0)หาลำดับคนป้องกัน : MATCH(B4,$A$6:$A$24,0)จากนั้นใช้ index เพื่อดึงผลลัพธ์การโจมตีกลับมา
INDEX($A$6:$S$24,MATCH(B3,$A$6:$S$6,0),MATCH(B4,$A$6:$A$24,0))
สิ่งที่น่าสนใจคือ ถ้าใช้ INDEX หรือ LOOKUP แบบปกติ ค่าที่เป็นค่าว่าง กับค่า 0 ในตารางจะออกมาเป็น 0 เหมือนกันหมด ทำให้ตีความผิด
ผมจึงใส่ IF ซ้อนไปว่า ถ้าค่ากลับมาเป็นค่าว่าง “” ให้แปลงค่าเป็นตัวอื่น เช่น ” ” หรือ 1 ก็ได้ จากนั้นค่อยเอาไป Lookup ทำเป็นคำแปลต่อไป
=IF(INDEX($A$6:$S$24,MATCH(B3,$A$6:$S$6,0),MATCH(B4,$A$6:$A$24,0))=""," ",INDEX($A$6:$S$24,MATCH(B3,$A$6:$S$6,0),MATCH(B4,$A$6:$A$24,0)))
หากมนุษยชาติมีภารกิจจะต้องเดินทางไปยังดวงดาวในอวกาศอันไกลโพ้น… พวกเขาจะทำอย่างไร?
เหล่านักบินอวกาศมีทางเลือกหลายทาง ตั้งแต่
ถ้าทำได้ทุกคนคงอยากจะเลือก ข้อ 3 จริงมั้ยครับ?
ในชีวิตของการใช้งาน Excel ก็เช่นกัน… หลายครั้งเราทำงานกับข้อมูลจำนวนมหาศาล
เปรียบได้กับภารกิจการเดินทางอันแสนไกลของนักบินอวกาศ…
เช่น ต้องใช้ VLOOKUP Map ข้อมูลที่มีหลายหมื่น หลายแสนบรรทัด คุณจะทำอย่างไร?
Excel ความเร็วแสง คือเทคนิคหลายอย่างที่ผมจะรวบรวมไว้เป็น series เรื่องการทำงานใน Excel ให้เร็วขึ้นโดยเฉพาะ
แล้ววันนี้ผมจะนำเสนอเทคนิคเด็ด นั่นคือ วิธีการใช้ VLOOKUP ความเร็วแสงครับ ซึ่งแม้จะเคย Post เรื่อง VLOOKUP เร็ว 100 เท่า มาแล้ว แต่ขอเอามาขัดเกลาให้อ่านง่ายขึ้นอีกครั้ง อันนี้อ่าน 5-10 นาทีจบ ทำได้เลยแน่นอนครับ!
เทคนิคในบทความนี้จะเน้นว่ากันด้วยวิธีปฏิบัติล้วนๆ ทฤษฎีใส่ให้บางๆ เท่านั้ย หากอยากเข้าใจที่มาที่ไปแบบละเอียด สามารถไปอ่านใน Post เก่าได้นะครับ
เอาล่ะ จะ VLOOKUP ให้เร็วขึ้นทำได้ยังไง? ก่อนอื่น ต้องเข้าใจว่า VLOOKUP มี 2 แบบ คือ
เรื่องของเรื่องคือ เวลาคนส่วนใหญ่ Map ข้อมูลแบบต้องให้ผลลัพธ์เป๊ะๆ ก็จะใช้แบบ Exact Match นี่แหละ (เพราะมันแม่น) แต่ผมจะบอกว่าถ้าข้อมูลมีเยอะมาก คุณควรใช้แบบ Approximate Match แทนครับ!
สรุปสูตรสุดท้าย
=IF(VLOOKUP(คำค้นหา,ตารางอ้างอิง,1,TRUE)=คำค้นหา,VLOOKUP(คำค้นหา,ตารางอ้างอิง,คอลัมน์ที่ต้องการ,TRUE),NA())
ในช่อง B11 ผมเขียนสูตรได้ว่า
=IF(VLOOKUP(คำค้นหา,ตารางอ้างอิง,1,TRUE)=คำค้นหา,VLOOKUP(คำค้นหา,ตารางอ้างอิง,คอลัมน์ที่ต้องการ,TRUE),NA())
=IF(VLOOKUP(A11,$A$1:$B$6,1,TRUE)=A11,VLOOKUP(A11,$A$1:$B$6,2,TRUE),NA())
จะได้ผลลัพธ์เป็น สมกอตต์ ถูกต้องครับ! และถ้าเป็น B10 ก็จะต้อง #N/A ถูกต้อง เช่นกัน เพราะไม่มีค่า p002x ในตารางอ้างอิง
เป็นยังไงบ้างครับกับเทคนิค VLOOKUP เร็วความเร็วแสง หากมีตรงไหนไม่เข้าใจก็ถามได้ครับ
และหากถูกใจ อย่าลืมบอกเพื่อนๆ ให้มาวิ่งไปสู่จุดหมายด้วยความเร็วแสงด้วยกันนะครับ !!
Warp !!
เครื่องมือ Table (ไม่ใช่ Data Table ที่เรียนผ่านไปแล้วนะ) สามารถสร้าง Dynamic Range ได้แบบง่ายๆ ภายในพริบตา
สมมติ ผมมี Range อยู่ แล้วผม convert เป็น Table ก่อน โดยกด Ctrl+T แล้วติ๊ก My Table has header ด้วย จะได้แบบนี้
หากผมเอาไปสร้างกราฟแท่งธรรมดาๆ เลย ขอแบบเร็วๆ
ผมขอ กดปุ่ม F11 เลย จะได้กราฟแบบนี้ (ซึ่งจะเห็นว่ามีข้อมูล 4 แท่ง)
ต่อไปเราไปเพิ่มข้อมูลต่อท้าย Table อีก สังเกตว่าสีของ Table จะงอกออกไปครอบคลุมข้อมูลใหม่โดยอัตโนมัติ
พอเรากลับมาดูกราฟ ก็จะเห็นว่ามีข้อมูลใหม่ถูกเพิ่มมาโดยอัตโนมัติ
นี่แหละครับคือความเป็น Dynamic Range แบบง่ายๆ ซึ่งสามารถนำไปประยุกต์กับการเลือก Data Source ของ PivotTable ได้ด้วย ซึ่งจะช่วยให้ไม่ต้องมาเลือก Source Data ใหม่ และไม่ต้องเลือกทั้งคอลัมน์เผื่อไว้ก่อน (ถ้าเลือกเผื่อไว้ก่อนจะกด Group Data แบบอัตโนมัติไม่ได้)
อีกตัวอย่างหนึ่งคือการทำ Dynamic Dropdown List เช่น จาก Table ก่อนหน้านี้ ผมจะเอาชื่อทีมมาเป็น Dropdown ให้เลือก
ซึ่งผมสามารถอ้างอิงรายการในทีมได้โดยเขียนสูตร = แล้วไปจิ้มตรงหัวคอลัมน์ จะได้ว่า =Table1[ทีม]
ผมก็เอาสูตรที่ได้เนี่ยแหละ ไปใส่ใน Data Validation List เลย แต่ปรากฏว่า Excel ไม่ยอม!
แต่อย่ายอมแพ้ครับ มันมีวิธีแก้เล็กน้อย โดยการใช้ Defined Name มาช่วยนั่นเอง
วิธีการคือ เอาสูตรที่ได้ไปตั้งชื่อก่อนครับ แล้วค่อยเอาชื่อนั้นไปใส่ใสน Data Validation อีกทีหนึ่ง
ใส่ใน Data Validation ได้เลย
จากนั้นจะเห็นว่า หากในอนาคตเรามีเพิ่มรายการเข้าไป Dropdown ของเราก็จะรู้จักรายการใหม่ๆ นั้นเองโดยอัตโนมัติครับ
เราได้เรียนรู้การทำ Dynamic Range ด้วย Table ไปแล้ว ซึ่งจะเห็นว่ามันทำได้ง่ายมากๆ แต่ผมจะบอกว่าเราต้องเรียนรู้วิธีอื่นเอาไว้ด้วย เพราะ Table อย่างเดียวก็ไม่ตอบโจทย์ในบางสถานการณ์
เช่น ถ้ามี Table อยู่ในไฟล์ เราจะใช้เครื่องมือ Custom View ไม่ได้ หรือ บางทีเราต้องการทำ Dynamic Range ที่ซับซ้อนมากขึ้น เช่น แสดงยอดขาย 6 เดือนล่าสุดไปเรื่อยๆ แบบนี้จะต้องใช้สูตรแทนการใช่ Table แล้วครับ ซึ่งใครอยากรู้ รออ่านได้ในหนังสือเล่ม 2 นะครับ ^^
รู้หรือไม่ว่าเราสามารถสร้าง PivotTable จากข้อมูลหลายตารางได้! ในบทความนี้เราจะมาดูกันว่าทำยังไง?
การทำ PivotTable จากหลายตารางนั้น ทำได้หลายวิธี แต่ละวิธีก็มีข้อดีข้อเสีย-ข้อจำกัดต่างกันดังนี้ครับ มาดูรายละเอียดกันดีกว่า
วิธีนี้เป็นการสร้าง PivotTable จากข้อมูลหลายตารางที่มีความสัมพันธ์กัน (เรียกว่า Data Model) ซึ่งเป็น Concept เดียวกับโปรแกรม Power BI เลยครับ หากใช้วิธีนี้เราก็จะสามารถสร้าง Pivot Table แล้วเลือก Field ข้ามตารางได้ โดยที่ไม่จำเป็นต้องเอาข้อมูลมารวมเป็นตารางเดียวกันเลยด้วยซ้ำ
อย่างไรก็ตามวิธีนี้จะไม่สามารถเอาตารางมาต่อแถวกันได้นะครับ
นอกจานี้ วิธีนี้ต้องใช้ Excel version 2013 ขึ้นไปครับ และถ้าจะให้ดีควรจะ Enable Add-in Power Pivot ซะก่อนจึงจะใช้งานได้สะดวก ซึ่งทำตามดังนี้
แล้วเลือก PowerPivot Add-in ซะตามรูป
จากนั้นเราจะต้องเอาตารางแต่ละอัน Add เข้า Data Model ซะก่อน เช่น มีทั้งหมด 3 ตาราง เช่น
1. ตารางการขาย 2. ตารางรหัสพนักงาน 3. ตารางลูกค้า
เราต้องเอาตารางเข้า Data Model โดยเลือกข้อมูล แล้วแปลงแต่ละตารางให้เป็น Table โดย Insert -> Table ซะก่อน แล้วค่อยกด Add To Data Model
จากนั้นมันจะเอาข้อมูลเข้าสู่หน้าต่างของ PowerPivot
จากนั้นให้กด Switch to workbook เพื่อกลับออกมาใน Excel แล้วกด Add To Data Model ให้ครบทุกตารางที่มีความสัมพันธ์กัน
พอ add เข้าไปหลายตาราง ในหน้า Power Pivot จะมีหลายชีทด้วย
จากนั้นให้กดดู Diagram View เพื่อผูกความสัมพันธ์ตารางต่างๆ เข้าด้วยกัน
เราจะเห็นตารางแยกกันอยู่ ให้เรากำหนด Relationship โดยลากเส้นเชื่อมว่า field ไหนคือตัวที่เชื่อมความสัมพันธ์ระหว่างแต่ละตารางเข้าด้วยกัน (จะลากจากไหนไปไหนก็ได้)
ทำให้ครบจะได้แบบนี้ จากนั้นก็กดสร้าง PivotTable ได้แล้ว
เราก็จะสามารถใช้ PivotTable Model Data Model แล้วยังสามารถลาก Field ข้ามตารางได้เลย โดยไม่ต้องเอาข้อมูลมารวมกันเป็นตารางเดียวกันอีก
สำหรับรายละเอียดว่า Concept ของ Data Model ทำงานยังไง ให้ไปอ่านใน Series Power BI ได้ครับ
หมายเหตุ : นอกจากวิธีนี้ เรายังกดสร้าง PivotTable โดยใช้ Data Model แบบไม่เปิด PowerPivot ก็ได้ โดยกดสร้าง PivotTable จากตารางโดยตรง แล้วติ๊ก Add to Data Model
แต่ข้อเสียของวิธีนี้ก็คือการกำหนด Relationship จะทำยากกว่า (เพราะไม่เห็นภาพ) โดยต้องใช้เมนูนี้
การใช้ Power Query นั้นสามารถทำได้ 2 ลักษณะ คือ
ถ้าใช้แบบนี้ ก็เป็นการใช้ Power Query แค่จัดหน้าตาข้อมูลให้เหมาะสมเท่านั้น เพิ่มเติมแค่ตอนกด Close & Load To… ให้เลือกเป็น Connection Only (เพราะเราจะไม่ Load ผลลัพธ์ออกมาใน Excel ปกติ) แต่ให้ติ๊กเลือกว่า Add to Data Model เพื่อให้มันส่งผลลัพธ์เข้า Data Model เท่านั้นเอง
คำว่ารวมให้อยู่ในตารางเดียว จริงๆ แล้วก็มี 2 แบบใหญ่ๆ นั่นคือ รวมคอลัมน์ กับ รวมแถว
จากนั้นค่อยส่งผลลัพธ์ที่รวมตารางเสร็จแล้วเข้าสู่ Pivot Table อีกทีครับ
วิธี Append ก็ให้สร้าง Query 2 ตารางขึ้นมาก่อน ตารางไหนยังไม่เอาผลลัพธ์ออกมาก็ทำแบบ Connection Only ไว้ เช่น อาจทำให้เป็น Connection Only ทั้งคู่เลย แล้วกดคลิ๊กขวาที่ Query ตัวแรก แล้วเลือก Append กับ Query ตัวอื่นๆ
แล้วเราก็จะได้ Query ใหม่ ที่เป็นผลลัพธ์จากการ Append 2 ตารางเข้าด้วยกัน (เอามาต่อแถวกัน)
จากนั้นก็เอาผลลัพธ์ออกไปใช้ใน Pivot Table ได้
ถ้ามีหลายตารางที่มีความสัมพันธ์กันจะติ๊ก Add this Data to the Data Model ด้วยก็ได้
การจะ Merge ก็ให้สร้าง Query 2 ตารางขึ้นมาก่อน ตารางไหนยังไม่เอาผลลัพธ์ออกมาก็ทำแบบ Connection Only ไว้
จากนั้นกดที่ Query แรกแล้วคลิ๊กขวาเลือก Merge กับ Query ตัวที่สอง ที่สำคัญคือต้องเลือกด้วยว่าคอลัมน์ไหนคือตัวเชื่อมกัน และเลือก JoinKind ด้วย ซึ่งปกติก็จะเป็น Left Outer Join แบบนี้แหละ ok ได้เลย
ผลลัพธ์จะออกมาเป็น Table ก่อน
ให้เราคลิ๊กที่มุมขวาบนของคอลัมน์เพื่อ Expand เอาข้อมูลออกมา
แล้วเราก็จะได้ผลลัพธ์คล้ายๆ กับ VLOOKUP เลย (แต่ถ้าตารางอ้างอิงมีหลายบรรทัด มันจะมาทุกบรรทัดนะ ต่างจาก VLOOKUP ที่จะมาแค่อันบนสุด)
จากนั้นก็ Load เอาผลลัพธ์เข้า Pivot Table ได้เลย
วิธีสร้าง PivotTable จากข้อมูลหลายตารางด้วย Microsoft Query เหมาะกับคนที่ไม่มี Power Query ให้ใช้ เช่นคนที่มี Excel Version เก่ามากๆ นั่นเองครับ
วิธีการทำมีดังนี้
วิธีการใช้ Microsoft Query นี้ สามารถนำมาประยุกต์ได้เยอะแยะ เช่น มี 2 ตารางแยกกันอยู่ อยากเอามาผูกกันโดยไม่ต้องใช้ VLOOKUP ก็ยังได้เลยครับ โดยมีทั้งแบบลากเส้นเชื่อมเองใน Editor หรือจะเขียน SQL เอง ก็ได้ (ภาษา SQL ทำได้แทบทุกอย่าง แต่อาจต้องศึกษาเยอะหน่อย)
วิธีสร้าง PivotTable จากข้อมูลหลายตารางด้วยตัวเลือก Multiple Consolidation Ranges ในคำสั่ง PivotTable Wizard เป็นวิธีที่มีข้อจำกัดมากที่สุด ผมจึงไม่แนะนำครับ แค่จะเอาให้ดูเฉยๆ ว่าในอดีตมันทำแบบนี้ได้
กด Alt, D, P เพื่อเรียก PivotTable Wizard แล้วเลือก Multiple Consolidation Ranges
เพื่อความง่ายเลือก Create a single page field for me ไปเลยก็ได้ครับ
เลือก Range ที่ต้องการ แล้วกด Add โดยทำทีละ source พอเสร็จแล้วก็กด Next จนจบกระบวนการ
คุณจะได้ PivotTable หน้าตาประหลาดมา 1 อัน ดังนี้
ผลลัพธ์ที่ได้ มีข้อจำกัดดังนี้
วิธีปรับแต่ง Data Source เพื่อทำให้แสดงผลดีขึ้นภายใต้ข้อจำกัดที่มี
ผมลองทำแล้ว ก็พอจะได้ผลลัพธ์ที่ ok ขึ้น ดังนี้ (ผมแยกแต่ละชีทเป็นคนละเดือน ดังนั้นเลยเอา Page1 มาใส่เป็นเดือนได้)
สรุปแล้วจะเห็นว่าวิธี Multiple Consolidation Ranges นั้นมีข้อจำกัดมากเลยนะครับ เห็นมะ อย่าใช้เลย 555
ส่วนตัวผมชอบใช้ Power Query จัดข้อมูลให้เรียบร้อยก่อน แล้วค่อยส่งเข้า Data Model ไปทำต่อครับ พูดง่ายๆ คือ Step เหมือน Power BI เป๊ะเลย
เพื่อนๆ ชอบแบบไหนกันบ้างครับ อย่าลืม Comment บอกด้วยนะ
จากที่ผมได้ post ใน Fanpage ไปว่า “มีใครอยากรู้เรื่องเทคนิคการใช้เครื่องมือ Mail Merge เพื่อดึงข้อมูลจาก Excel ไปโปรยลงแบบฟอร์มใน Microsoft Word มั้ยครับ? (แล้วเราก็ Print เอกสารออกมาหลายๆ ชุดได้ เช่น Print ใบแจ้งหนี้/ใบเสร็จให้ลูกค้า)”
ปรากฏว่ามีคนสนใจเป็นจำนวนมาก แสดงว่ามีคนที่ยังไม่ได้ใช้ประโยชน์จากเครื่องมือนี้อยู่พอสมควรเลย ซึ่งน่าเสียดายมากๆ เพราะมันมีประโยชน์สุดๆ
ต้องบอกก่อนว่า เทคนิคนี้เป็นเครื่องมือใน Microsoft Word แต่ผมไม่ใช่ผู้เชี่ยวชาญ Word ดังนั้น หากใครเห็นว่ามีวิธีที่ดีกว่าที่ผมแนะนำ ก็สามารถบอกมาเป็นวิทยาทานให้ผมและคนอื่นๆ ได้รู้ด้วยนะครับ
เอาล่ะ เกริ่นมามากแล้ว เพื่อไม่ให้เสียเวลา เราไปดูกันเลย เอาเป็นว่าวันนี้ inwexcel ขอพูดเรื่อง Word บ้างล่ะ 555
Mail Merge คือความสามารถหนึ่งของ Microsoft Word ที่สามารถดึงข้อมูลจาก Database ต่างๆ (รวมถึงข้อมูลใน Excel) ที่มีข้อมูลหลายๆ Record (บรรทัด) มาโปรยลงแบบฟอร์มที่ได้เตรียมเอาไว้ใน Microsoft Word ได้ ซึ่งเราสามารถเอามาประยุกต์ทำเรื่องต่างๆ ได้มากมาย โดยมี Concept ดังนี้
การใช้ Mail Merge เป็นการเสริมจุดแข็ง และปิดจุดอ่อนของ Word ได้เป็นอย่างดี
เสริมจุดแข็ง : Word สามารถจัดข้อมูลลงหน้ากระดาษเพื่อ Print ได้สวยงามกว่าการใช้ Excel มาก ทำให้เราสามารถ ใส่ความคิดสร้างสรรค์เรื่องความสวยงามได้เต็มที่ (คิดดูว่าการจัดหน้าใน Excel เพื่อ Print มันน่าปวดหัวแค่ไหน)
ปิดจุดอ่อน : โยนเรื่องที่ Word ไม่ถนัดให้ไปอยู่ที่โปรแกรมอื่นให้หมด เช่น ความสามารถในการคำนวณ หรือ เรื่องความเป็นระเบียบของข้อมูล ก็ให้ไปอยู่ในฐานข้อมูลให้หมด (ใน Word แม้จะคำนวณได้ แต่ยาก และจำกัดมากๆ )
มี 3 ขั้นตอนใหญ่ๆ
เรามาดูขั้นแรกกัน
สมมติว่าผมเป็นบริษัทขายผลไม้ ต้องการทำใบเสร็จ โดยมีหน้าตาและข้อความประมาณนี้
ต่อมา ผมก็ไปเตรียมฐานข้อมูลใน Excel ให้มีข้อมูลที่น่าจะเพียงพอกับฟอร์มของผม สมมติว่าได้แบบนี้ แล้ว Save เก็บไว้
ต่อมา ผมก็ต้องใช้ Mail Merge เพิ่ม Field ที่ต้องการลงในแต่ละส่วนของแบบฟอร์ม โดยไปที่ Word ที่เตรียมไว้แล้วไปที่ Ribbon [Mailings] –> Start Mail Merge
จากนั้นมันจะให้เลือก Document Type ซึ่งแต่ละอันต่างกันดังนี้
เราต้องการทำพวกใบเสร็จ มันจะคล้ายๆ กับ Letter เพราะต้องเอาไปพิมพ์ออกมาหลายๆ ชุด ในที่นี้จึงให้เลือกเป็น Letter ครับ
จากนั้นให้เลือก Database เพื่อเชื่อมข้อมูล โดยไปที่ Select Recipients แล้ว Use an Existing List
จากนั้น เลือกไฟล์ Excel ที่เตรียมไว้ แล้วเลือก Sheet ให้ถูกต้อง
จากนั้นให้ Highlight ข้อความส่วนที่ต้องการจะ Link กับ Database แล้วเลือก Insert Merge Field แล้วเลือก field ที่ต้องการเชื่อมต่อด้วย
มันจะมีสัญลักษณ์ <<ชื่อ field >> ขึ้นมาแทน นั่นแปลว่า มันกำลัง Link กับ Field นั้นแล้ว
ทำให้ครบทุก Field สุดท้ายจะได้แบบนี้
ถ้าลองกด Highlight Merge Field ดูจะเห็นชัดขึ้น
ทีนี้ลองทดสอบดูว่าข้อมูลมามั้ย? โดยไปที่ Preview Results
และถ้ากดปุ่ม Next มันก็ควรจะดึงข้อมูล record ถัดไปมาให้
ถ้าเป็นแบบนี้ แสดงว่าการเชื่อมต่อข้อมูลน่าจะถูกต้องแล้วล่ะ
จริงๆ ถ้าไม่คิดอะไรมาก ก็น่าจะจบงานได้แล้ว แต่กรณีนี้ ยังมีสิ่งที่ผมต้องการทำแต่ยังไม่ได้ดั่งใจ 3 เรื่อง คือ
วิธีแก้ 3 ประเด็นนี้ จริงๆ แล้วบางอันทำใน Word ก็ได้ โดยการกำหนด Format ใน Field Code
แต่ส่วนตัวผมไม่ชอบวิธีนี้ เพราะขี้เกียจมาจำวิธีการกำหนด Format ใน Word อีก (แค่ Excel ก็มึนแล้ว) ดังนั้นผมจะแก้ปัญหาทุกอย่างใน Excel ให้จบเลย แล้วค่อยส่งมาที่ Word เพื่อแสดงผลอย่างเดียว
แต่ถ้าหากผมลองเปลี่ยน Number Format ใน Excel ให้เป็นแบบที่ต้องการ เช่น เป็นวันที่แบบเต็ม และราคามีจุดทศนิยม
เวลาเข้ามาคลิ๊ก Next / Previous ดูใน Word ก็ยังคงแสดงวันที่และเลขตามปกติอยู่ดี เพราะมันเอาค่าแบบ General ไปแสดงเท่านั้น (ถ้าลองเปลี่ยนค่าไปเลยมันจะเปลี่ยนตามแล้ว แต่ Format ไม่เปลี่ยนตาม)
ดังนั้น วิธีที่ผมแนะนำ เพราะสำหรับผมมันง่ายสุดแล้ว คือ
ผมจะเตรียมให้ข้อมูลทุกอย่างใน Excel ให้อยู่ในรปแบบที่ผมต้องการโดยต้องเป็น Text ทั้งหมด แล้วค่อยส่งไปแสดงผลตรงๆใน Word โดยที่ใน Word ไม่ต้องมีการกำหนด Format อะไรอีกเลย
Key สำคัญคือต้องทำให้เป็น Text ให้หมด อะไรที่เดิมเป็นตัวเลขและวันที่ ผมจะใช้ฟังก์ชั่น TEXT มาช่วย เพื่อแปลงให้มันอยู่ในรูปแบบที่ผมต้องการเลย ซึ่งเราจะต้องรู้ว่าต้องระบุ Custom Format ยังไง
แต่ก็ไม่ใช่เรื่องยากอย่างที่คิด เพราะเราสามารถเลือก Standard Format แบบที่ต้องการก่อน แล้วค่อยไปเลือก Custom Format เพื่อดู Code ที่จะต้องใช้อีกทีก็ได้
เช่น จากวันที่ 31 มีนาคม 2559 พอไปเลือก Custom จะเห็นว่าเป็น Code [$-th-TH,107]d mmmm yyyy;@ ก็เอาไปใช้ในฟังก์ชั่น TEXT ได้เลย เช่น
ผมก็เอาไปใช้กับฟังก์ชั่น Text ดังนี้
=TEXT(B6,”[$-th-TH,107]d mmmm yyyy;@”)
ซึ่งจะทำให้ field วันที่-new เป็นข้อความจริงๆ ไม่ใช่ตัวเลขแล้ว
ทีนี้กลับไปใน Word ผมก็ต้อง link Field ใหม่แทนอันเดิม แต่ผมต้องไป Select Recipient ใหม่ด้วย เพื่อให้มันเห็น Field ใหม่ที่เราเพิ่งเพิ่มเข้าไปก่อน
แล้วก็กด Preview ดู ทุกอย่างก็ออกมาในรูปแบบที่ได้ดั่งใจ โดยที่เราไม่ต้องรู้เรื่อง Field Code ใน Word เลยแม้แต่น้อย!
คราวนี้เวลาจะ Print เพื่อจบงาน ก็ให้ไปที่ Finish & Merge แล้วเลือก Print Documents ได้เลยครับ แล้วมันจะให้เลือกว่าจะ Print Record ไหนบ้าง (ปกติก็ All ได้เลย)
เพียงเท่านี้คุณก็สามารถสร้างใบเสร็จรับเงินให้ลูกค้านับสิบนับร้อยได้อย่างสบายๆ ครับ
จริงๆ แล้ว Label ก็จะคล้ายกับการทำ Letters ครับ เพีงแต่จะมีการใส่ Field ลงไปหลายชุดซ้ำๆ กันลงในตารางที่เตรียมไว้
แต่ Key หลัก คือ จะมีการระบุ Code <<Next Record>> เพื่อบ่งบอกว่า ส่วนต่อจากคำนั้น จะต้องดึงข้อมูลจาก Record ถัดไปแล้ว (แม้จะยังอยู่หน้าเดิม) โดยสามารถระบุเองได้โดยไปที่ Rules -> Next Record ครับ
และนี่คือตัวอย่างที่ผมใช้ Label มา Print เวลามีคนสั่งซื้อหนังสือจากผมครับ
จะเห็นว่าการใช้ Mail Merge นั้นไม่ได้ยากมากอย่างที่คิด หากคุณจัดการ Format ใน Excel ให้เรียบร้อย การส่งมาพิมพ์ใน Word ก็จะหมูมากๆ ครับ
รู้หรือไม่ว่า บางเรื่องแม้ดูเหมือนง่าย และน่าจะเป็นไปตาม Common Sense ของเรา ในความเป็นจริง Excel อาจไม่ได้ทำงานแบบที่เราคิดก็ได้ จนในที่สุดก็ทำให้เกิดความผิดพลาดได้…
ผมจึงอยากจะแบ่งปันข้อควรระวังในการใช้ Excel ไว้ให้ดี เพื่อที่ว่าเวลาเราใช้ Excel ทำงานจะได้ไม่ทำผิดกัน
หลายๆ เรื่องในนี้ผมเคยสรุปไว้ให้ในหนังสือ Excel Level Up! ไปแล้ว (บางเรื่องก็ยังไม่เคยพูด) ก็เลยขอนำมาพูดสรุปให้อีกทีแล้วกันครับ ^^
เรื่องนี้หลายคนอาจไม่ได้สังเกต เพราะดูเป็นเรื่องง่ายๆ แค่ใช้เครื่องหมายเท่ากับ ซึ่งตามหลัก Common Sense แล้ว เครื่องหมายเท่ากับก็เอาไว้เช็คว่าสองช่องมันเท่ากันรึเปล่า… ไม่ใช่เหรอไง?
แต่คำว่าเท่ากันของ Excel มันกลับมองว่าตัวพิมพ์ใหญ่ตัวพิมพ์เล็กไม่มีผลครับ ดังนั้นมันจึงมองคำว่า Dog และ dog ว่าเท่ากันด้วยเครื่องหมายเท่ากับซะงั้น! (ตามรูปให้ผลเป็น TRUE)
ทางแก้ หากเราต้องการเช็คว่ามันท่ากันเป๊ะๆ รึเปล่า แบบสนใจตัวพิมพ์เล็กพิมพ์ใหญ่ด้วย เราต้องใช้ฟังก์ชั่น EXACT มาช่วยครับ ซึ่งตามรูปจะได้ผลเป็น FALSE
บางทีสิ่งที่เราเห็น อาจไม่ใช่สิ่งที่เป็นจริง เลข 50 เหมือนกันแท้ๆ กลับสามารถให้ผลลัพธ์เป็น FALSE ซึ่งบ่งบอกว่าไม่เท่ากันได้ด้วย
สาเหตุมาจากทั้งสองช่องนี้มีประเภทของข้อมูลที่ต่างกันนั่นเอง ซึ่งวิธีเช็คประเภทของข้อมูลที่ดีที่สุดคือใช้ฟังก์ชั่น TYPE มาช่วยเช็คครับ
หลายๆ คนที่เคยผ่านวิชาทางด้านการเงินมา น่าจะคุ้นๆ กับคำศัพธ์ที่มีชื่อเท่ๆว่า NPV (Net Present Value) มาบ้างแล้วล่ะ แต่สำหรับคนที่ไม่รู้จัก ผมก็อยากจะลองแนะนำให้ไปศึกษาดูนะครับ เพราะมันเป็นตัวสามารถช่วยบอกได้ว่าโปรเจคหรือการลงทุนที่เรากำลังสนใจนั้น น่าลงทุนหรือไม่? ซึ่งมีประโยชน์มั่กๆ
โดยวิธีการตีความคือ ถ้า NPV มีค่ามากกว่า 0 ถือว่าน่าสนใจ (ภายใต้ Discount Rate หรืออัตราค่าเสียโอกาสของเงินที่กำหนด) แต่ประเด็นไม่ใช่ตรงนี้ครับ
ประเด็นที่ควรระวังคือ เจ้าฟังก์ชั่น NPV ใน Excel เนี่ย เวลาลากช่วง Cashflow ห้ามใส่ Cashflow ปีที่ 0 เข้าไปด้วยเด็ดขาด โดยเมื่อคำนวณด้วยฟังก์ชั่น NPV เสร็จแล้ว ให้เอาค่าที่ได้มาบวกด้วย Cashflow ปีที่ 0 อีกที ถึงจะได้ NPV ที่ตรงกับตำราทางการเงินครับ
คราวนี้เป็นเรื่องของฟังก์ชั่นเกี่ยวกับวันที่บ้างครับ ซึ่งมีฟังก์ชั่น 2 ตัวที่ทำหน้าที่เกี่ยวกับเรื่องจำนวนวันทำงาน โดยมันทั้ง 2 ทำงานเหมือนเป็นพี่น้องกัน คือ
แต่ที่แปลกคือ ถ้าลองเอา WORKDAY มาใช้วันเริ่มเดียวกันกับ NETWORKDAYS แต่บวกจำนวนวันทำการที่ได้จาก NETWORKDAYS เข้าไป มันกลับได้วันจบเป็นคนละวันกันซะงั้น!
สาเหตุเพราะว่า ไอ้ฟังก์ชั่น 2 ตัวนี้ ดันมีเกณฑ์การนับวันไม่เหมือนกัน โดย NETWORKDAYS จะนับวันเริ่มด้วยเสมอ ส่วน WORKDAY ไม่นับวันเริ่มนั่นเอง
สมมติว่าผมอยากคำนวณค่า Commission 10% ของยอดขาย โดยมีเงื่อนไขว่าต้องทำยอดขายรวมทุกสินค้าได้มากกว่า 3000 บาท จึงจะได้รับเงินนี้ไป
หากผมใช้ Calculated Field ใน PivotTable ผมสามารถใส่สูตร IF ซ้อนลงไปได้ว่า เอาา ถ้าบอดขายมากกว่า 3000 ก็ให้เอายอดขายไปคูณ 10% นะ ไม่งั้นก็ให้เป็น 0 ไป
สิ่งที่ออกมานั้นดูดีเลยครับ คือ Sales ง ที่ยอดขายไม่เกิน 3000 ก็อด Commission ไป แต่สิ่งที่แปลกสุดๆ คือ เจ้า Grand Total ของ Commission มันดันให้ค่าที่ผิดพลาด โดยได้ค่าไม่เท่ากับผลรวมของ Commission ของ Sales ทุกคน
สาเหตุเพราะเวลาที่ Excel คำนวณ มันดันเอาค่ายอดขาย Grand Total ไปใส่ใน IF แล้วเทียบว่าเกิน 3000 เลยเอา 10% ไปคูณตรงๆ ซะงั้น!! ผิดไปเลยครับแบบนี้
วิธีแก้ของเรื่องนี้ คือ อย่าไปสนใจ Grand Total … แต่ถ้าทำไม่ได้ ก็ต้องไปใช้วิธีอื่นแทน เช่น เขียนสูตรลงไปในตารางข้อมูลก่อนจะเอามา Pivot เลยก็ได้ โดยอาจใช้พวก SUMIFS มาช่วย เพื่อหายอดขายรวมราย Sales แต่ละคนให้ได้ว่าเกิน 3000 หรือไม่ เป็นต้น
ปัญหาเรื่อง VLOOKUP พังนี่เกิดได้จากหลายสาเหตุมากๆ ครับ ดังนี้
เรื่องสุดท้าย เป็นเรื่องที่ไม่น่าจะผิด แต่ผมเจอบ่อยพอสมควร นั่นคือ เผลอไปตั้งค่าการคำนวณให้เป็นแบบ Manual แล้วลืมกด F9 เพื่อ Calculate!
หลายคนเขียนสูตรแล้วก็งงว่าทำไมผลลัพธ์ไม่เปลี่ยน หรือ ถ้าผิดรุนแรงบางทีอาจเผลอบอกคำตอบคนอื่น หรือส่งงานแบบผิดๆ โดยที่ Excel ยังไม่ทันคำนวณอะไรเลยก็ได้!!
หลายคนสงสัยว่า เฮ้ย! ตัวเองไม่เคยไปกดเปลี่ยนโหมดในการ Calculate ให้เป็น Manual เลย แล้วมันจะเกิดเหตุการณ์นี้ได้ยังไง? ผมมีคำตอบให้ครับ
หลักการสำคัญคือ ทุกไฟล์ที่เปิดอยู่ต้องอยู่ในโหมดการคำนวณแบบเดียวกันทั้งหมด ซึ่งมีรายละเอียดดังนี้
สรุปมาจาก https://support.microsoft.com/en-us/kb/214395
เอาล่ะครับ ก็จบไปแล้วกับ 7 ข้อควรระวังใน Excel ที่คุณอาจเผลอทำผิดโดยไม่รู้ตัว! หวังว่าเมื่อรู้ “เจ็ด”ข้อนี้แล้ว จะไม่ทำให้คุณ”เจ็บ”ในการทำงานนะครับ
ขอให้โชคดี ทำงานเสร็จรวดเร็วและไม่ผิดพลาดนะครับ!
“ชีวิตคนเราเต็มไปด้วยการตัดสินใจ…” จริงๆ แล้วคนเราตัดสินใจอยู่ตลอดเวลา (บางทีก็ตัดสินใจที่จะอยู่เฉยๆ 555) บางทีเราก็ต้องเลือกระหว่าง Choice 2 อย่าง แต่บางทีก็มี Choice มากมายนับไม่ถ้วน…
การทำงานกับ Excel ก็เช่นกัน หลายๆ ครั้งเราก็ต้องตัดสินใจ “ว่าถ้าข้อมูลเป็นแบบนี้ๆ แล้วจะให้เกิดเหตุการณ์อะไรขึ้น??”
ซึ่งจริงๆ แล้วการตัดสินใจใน Excel นั้นมีฟังก์ชั่นที่มาช่วยในเรื่องนี้หลายตัวด้วยกัน แต่ในบทความนี้ผมจะขอยกตัวอย่างฟังก์ชั่นที่น่าสนใจมากๆ 3 อัน นั่นคือ IF, CHOOSE, และ VLOOKUP นั่นเอง ซึ่งผมบอกเลยว่า นี่คือ “หัวใจสำคัญ” ที่จะทำให้คุณใช้ Excel ได้เก่งกาจขึ้นอีกมากครับ!!
ก่อนอื่น ขอเริ่มจากฟังก์ชั่นพื้นฐานที่สำคัญที่สุดในการคิดแบบตัดสินใจ นั่นคือ IF ครับ
IF แปลได้ตรงๆ ว่า “ถ้า” ซึ่งความหมายและหน้าที่ของฟังก์ชั่นก็ตรงตามคำศัพท์ครับ นั่นคือ “ถ้า เงื่อนไขเป็นจริง แล้วจะใช้สูตร xxx แต่ถ้าไม่จริงจะใช้สูตร yyy”
พูดง่ายๆ ว่า มีทางแยกในการตัดสินใจ แค่ 2 ทาง คือ ถ้าเงื่อนไขเป็นจริง ก็ไปทางหนึ่ง ถ้าเป็นเท็จก็ไปอีกทางหนึ่ง เหมือนว่าเราเดินมาเจอทางแยกที่มีป้ายบอกทางชี้ไปซ้ายกับขวา… และการที่เราจะไปต่อทางไหน ก็ขึ้นอยู่กับเงื่อนไขที่กำหนดไว้
ซึ่งเงื่อนไขที่ว่าก็คือสิ่งที่เขียนอยู่ในส่วนของ logical_test ในฟังก์ชั่น IF นั่นเอง ซึ่งจะต้องให้ผลลัพธ์ออกมาได้แค่ 2 อย่าง คือ TRUE กับ FALSE เท่านั้น!
=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 ซ้อนกันหลายๆ ชั้น
หาก IF เปรียบเสมือนป้ายทางแยก 2 ทางแล้ว 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 จากสูตรทั้งหมดที่มีให้เลือกดังนี
หมายเหตุ : ในชีวิตจริง การที่จะกำหนดลำดับสูตรที่จะใช้ให้เป็นตัวเลขได้นั้นไม่ใช่เรื่องง่าย ซึ่งถ้าให้ผมแนะนำ ก็ต้องใช้ฟังก์ชั่นถัดไปนั่นคือ VLOOKUP มาช่วยตรงส่วน index_num นี้นั่นเอง
ตัวสุดท้ายที่จะพูดถึงคือฟังก์ชั่นสุดเจ๋งอย่าง VLOOKUP ซึ่งจริงๆ แล้วมีการทำงานถึง 2 โหมดด้วยกัน นั่นคือ VLOOKUP แบบ Approximate Match และ แบบ Exact Match
ซึ่งตัวที่ผมจะขอพูดถึงในที่นี้คือ VLOOKUP แบบ Exact Match ซึ่งเป็นแบบที่เราใช้งานกันบ่อยในชีวิตประจำวันแล้วกันครับ
ถ้าจะให้เปรียบเทียบ VLOOKUP แบบ Exact Match ผมคิดว่าเราสามารถเปรียบเทียบกับเวลาที่เรากำลังมองไปที่เมนูของร้านกาแฟได้เลยครับ (ตัวอย่างร้านกาแฟนี้ ผมได้แนวคิดมาจากเว็บ http://www.excelcampus.com/functions/excel-vlookup-explained/ ครับ อธิบายได้เห็นภาพมาก)
สิ่งที่เราทำคือ เราคิดว่าจะกินกาแฟชนิดไหน จากนั้นก็มองไล่หาชนิดกาแฟที่ต้องการ พอเจอแล้ว ก็ค่อยกวาดสายตาไปทางขวา เพื่อดูอีกทีว่า 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 ได้
สรูปแล้วฟังก์ชั่นแต่ละตัวก็มีข้อดีข้อเสียต่างกัน ซึ่งถ้าให้ผมแนะนำว่าควรใช้ตัวไหน ก็ให้ดูว่า ทางแยกตัดสินใจมีมากหรือน้อย ถ้ามีน้อยๆ แค่ 2 ทาง การใช้ IF ก็ค่อนข้างสะดวก
แต่ถ้ามีหลายทาง ก็ให้ดูว่าผลลัพธ์เป็นค่าธรรมดาหรือเป็นสูตรคำนวณ ถ้าเป็นสูตรคำนวณก็ต้องคิดเอาว่าจะถนัดใช้ IF ซ้อน IF หรือจะใช้ CHOOSE ดีกว่า
แต่ถ้ามีหลายทางแล้วผลลัพธ์เป็นค่าธรรมดาๆ ไม่ใช่สูตร ก็ใช้ VLOOKUP ได้เลยครับ!!
สำหรับคนที่อยากศึกษาต่อเพิ่มเติม ก็สามารถค้นหาคำว่า IF, CHOOSE, VLOOKUP ในเว็บผมได้เลยครับ มีให้อ่านอีกเยอะครับ ^^
เคยงงมั้ยครับว่า Excel มีอะไรให้เรียนรู้มากเหลือเกิน แล้วเราควรจะเรียนรู้อะไรก่อนหลังดี? ปัญหานี้จะหมดไปถ้าเรามาเรียนรู้เรื่องของ Excel Skill Tree หรือ แผนผังทักษะ Excel ครับ
คำว่า Skill Tree คือ แผนผังการพัฒนาทักษะ ซึ่งจะบอกได้ว่า…การที่เราจะบรรลุแต่ละทักษะนั้น จำเป็นจะต้องผ่านการเรียนรู้ หรือเข้าใจทักษะอื่นๆ อันไหนก่อนบ้าง?
(หากคุณเคยเล่นเกม RPG มาก่อน คุณน่าจะคุ้นเคยกับ Skill Tree เป็นอย่างดี หากสนใจแบบละเอียด อ่านได้ที่นี่ –> 10 ข้อคิดจากเกมออนไลน์ ใช้พัฒนาความก้าวหน้าทักษะ Excel )
วิธีการดูก็ให้ดูจากซ้ายไปขวาครับ ด้านซ้ายก็จะเป็นเรื่องที่ค่อนข้าง Basic ไม่จำเป็นต้องรู้อะไรก่อนมากนัก แต่ทักษะด้านขวาๆ จะเป็นเรื่องที่ต้องมีพื้นฐานอื่นก่อนเต็มไปหมดครับ ดังนั้นจะยากกว่าแน่นอน
ซึ่งในโปรแกรม Excel จริงๆ แล้วมันก็ไม่ได้มีกฎตายตัวหรอกครับว่าต้องรู้อะไรก่อนหลัง แต่รูปข้างล่างนี้เป็นแนวทางที่ผมขออนุญาตแนะนำแล้วกันครับว่าหากเรียนรู้ด้วยลำดับแบบนี้น่าจะดีที่สุดครับ
โดยที่หากคุณอยากเรียนรู้ทักษะ Block ไหน ก็เลือก Category ที่ Menu ด้านบน หรือ ด้านขวามือ แล้วเลือก Category ที่ต้องการได้เลยครับ
ในเกม RPG ส่วนใหญ่ตัวละครที่เราเลือก ท้ายที่สุดแล้วจะสามารถพัฒนาจนมีความสามารถที่แตกต่างกัน โดยจะแบ่งเป็น “สายอาชีพ” ต่างๆ ซึ่งอาจมีอาชีพขั้นพื้นฐานก่อนแล้วค่อยเติบโตไปเป็นอาชีพขั้นสูง
การใช้ Excel ก็มีลักษณะคล้ายๆ สายอาชีพในเกม RPG เช่นกัน แต่ผมขอเรียกว่า “สายทักษะ” แทน และมันก็มีสายทักษะขั้นสูงด้วย ซึ่งผมขอแบ่งออกเป็น 3 ทักษะขั้นสูง (ใน Skill Tree จะมีดาวปะอยุ่) ดังนี้
หากใครสามารถวางแผนได้ตั้งแต่เนิ่นๆ ค้นหาตัวเองเจอว่าสุดท้ายแล้วตัวเองจะเลือกสายทักษะอะไรก่อน จะได้เปรียบคนที่โลเลเปลี่ยนไปเปลี่ยนมามาก เพราะคุณจะไม่เสียเวลาไปกับทักษะที่สุดท้ายไม่ได้ส่งเสริมสายอาชีพคุณ (แต่ตอนนี้ผมมีเนื้อหาแค่สาย Array Formula นะครับ อิอิ)
อย่างไรก็ตาม ถ้ายังหาตัวเองไม่เจอ คุณก็อย่าคิดมาก คุณแค่ยังทำโน่นทำนี่ไม่มากพอ ยังมีประสบการณ์ไม่มากพอ ก็เลยยังไม่รู้จักตัวเองเท่านั้นเอง
อย่างไรก็ตาม เรื่องของ Demand/Supply ก็เป็นอีกหนึ่งปัจจัยที่ต้องคำนึงถึง เพราะหากสุดท้ายในโลกที่คุณอยู่มีสายอาชีพ/สายทักษะที่คุณเลือกเต็มไปหมดเลย Supply ก็จะเยอะเกินความต้องการ บางทีคุณไปเลือกเป็นสายอาชีพที่ไม่ค่อยมีคนเลือก อาจจะเป็นที่ต้องการมากกว่าก็ได้
แต่คุณไม่จำเป็นต้องเก่ง Excel ไปพร้อมกันหมดทุกสายทักษะ ผมเชียร์ให้คุณเก่งทีละด้านไปเลยดีกว่า เอาให้สุด!! เพื่อให้เป็น Expert ไปเลย ถ้าคิดว่าเรียนรู้สุดแล้ว ค่อยลองหันมาเรียนรู้สายอื่นเพิ่มเติมทีหลังก็ได้
บทความนี้ผมตั้งใจจะให้เป็นบทความที่จะสอน Concept ของ Array Formula แบบสั้นๆ พอได้ไอเดียและพอเห็นภาพว่า Array Formula คืออะไร? มีข้อดีข้อเสียอย่างไร ?
ใครอยากรู้เชิญอ่านได้เลยครับ รับรองว่าได้ความรู้ดีๆ เพียบเช่นเคย!
ในสมัยก่อน…วิชาวิทยาศาสตร์อย่าง ฟิสิกส์ เคมี ที่หลายๆ คนเคยเรียน สามารถอธิบายปัญหาและปรากฏการณ์ต่างๆ ที่เกิดขึ้นบนโลกนี้ได้เป็นอย่างดี นักวิทยาศาสตร์ยุคแรกๆ เรียนรู้… ทำความเข้าใจกฎเกณฑ์ที่เกี่ยวกับมัน จนคิดว่ารู้จักมันดีในระดับหนึ่งแล้ว
แต่ต่อมา เมื่อพวกเขาเหล่านั้นได้พบเจอสิ่งต่างๆ มากขึ้นเรื่อยๆ พวกเขาเริ่มตระหนักได้ว่า ยังมีอีกหลายเรื่องที่วิทยาศาสตร์แบบเดิมอธิบายไม่ได้ เช่น สิ่งที่อยู่นอกจักรวาล การเคลื่อนที่ระดับความเร็วแสง หรือ แม้แต่สิ่งเล็กๆ ระดับอนุภาค ซึ่งจะต้องใช้วิทยาศาสตร์แบบใหม่ อย่าง ทฤษฎีสัมพัทธภาพ หรือ กลศาสตร์ควอนตัม มาอธิบาย จึงจะทำความเข้าใจได้อย่างลึกซึ้ง เรียกได้ว่า ความรู้แบบเดิม “มีข้อจำกัดในการใช้งาน” คือ ใช้กับบางสถานการณ์ที่มีความซับซ้อนมากไม่ได้
ฟิสิกส์แบบนิวตัน ใช้อธิบายหลายๆเรื่องในอวกาศไม่ได้ ต้องใช้ทฤษฎีไอน์สไตน์แทน นี่แหละคือตัวอย่างของความรู้เดิมใช้ได้จำกัด
ที่ผมพูดมาทั้งหมดนี้ เพื่อจะเปรียบเทียบ สูตร Excel ทั่วๆ ไปที่หลายๆ คนได้เรียนรู้กันมาก่อนหน้านี้ ว่าเป็นเหมือน วิทยาศาสตร์แบบเก่า ซึ่งมีข้อจำกัดในการงานมากมาย…
หากเพื่อนๆ เริ่มพบว่า การเขียนสูตรแบบเดิมเริ่มตอบโจทย์บางอย่างไม่ได้ เช่น
ปัญหาทุกอย่างนี้ สามารถถูกแก้ไขได้ด้วย การเขียนสูตรอีกวิธีที่เรียกว่า Array Formula ซึ่งนอกจากจะช่วยแก้ปัญหาแล้ว ยังทำให้ศักยภาพในการเขียนสูตรเราเพิ่มขึ้นอีกมหาศาลเลยทีเดียว
มันคือการเขียนสูตร Excel ขั้นสูง ที่เราเอาไว้ทำเรื่องเจ๋งๆ อย่างการ Dynamic Range (ขยาย หด range ตามต้องการ) , Filter, Sort, การคำนวณแบบแปลกๆ ตามเงื่อนไข ด้วย “การเขียนสูตร” ได้
Array Formula จะเป็นสูตรที่ทำงานกับ ชุดข้อมูลที่มีสมาชิกหลายๆ ตัว (Array) พร้อมๆ กัน
พูดง่ายๆ คือ ถ้าเราใส่ Range แทนลงในตำแหน่งในสูตรที่ปกติจะเป็น Cell เดียว นั่นแหละ Array Formula
ตัวอย่าง เข่น ที่ผม Highlight สีแดงไว้ ก็ถือว่าเป็นส่วนที่ทำให้เกิดสูตรแบบ Array ทั้งสิ้น เพราะมีการใส่ข้อมูลเป็น Range แทนที่จะใส่ช่องเดี่ยวๆ ตามปกติ
โดยที่ปกติแล้ว เวลาเราเขียนสูตรแบบ Array จะต้องกดปุ่ม Ctrl+Shift+Enter เพื่อบอก Excel ว่าจะใช้สูตรแบบ Array แล้วนะ… ไม่งั้น Excel จะให้ค่า Error กลับมา (เหมือนด่าว่าเราเขียนสูตรไม่เป็น)
โดยที่เวลากด Ctrl+Shift+Enter แล้ว ตรง Formula Bar จะมีเครื่องหมายปีกกา { } มาครอบให้โดยอัตโนมัติ (เขียนเองไม่ได้) เพื่อบ่งบอกว่าสูตรนี้เป็นสูตรพิเศษ ที่กด Ctrl+Shift+Enter เพื่อใช้งาน
สมมติผมเขียนสูตรแบบนี้
หากลองกด F9 ดูเพื่อดูค่าที่แท้จริงว่าคืออะไร มันจะได้แบบนี้ครับ
ในทำนองเดียวกัน ถ้าเป็นแบบนี้
เวลากด F9 จะได้แบบนี้
ผลลัพธ์ที่ออกมานั้นบ่งบอกว่า ในสูตรๆ เดียวที่เราเขียนนั้น ให้ค่าผลลัพธ์ออกมาหลายตัวด้วยกัน
ดูเผินๆ อาจดูเหมือนว่าทั้ง 2 กรณีได้ผลลัพธ์เหมือนกัน แต่ถ้าดูดีๆ จะเห็นว่าต่างกันตรงเครื่องหมายที่เป็นตัวคั่น
| สูตรปกติ | Array Formula | |
| การใส่สูตร | ใส่ข้อมูลที่ละตัวเช่น = 1*10 | ใส่ข้อมูลเป็นชุดเช่น ={1,2,3}*{10,20,30} |
| การคำนวณ | คำนวณปกติ ไม่มีการจับคู่คำนวณเช่น = 1*10 | จับคู่อันดับในการคำนวณ (ตัวแรกชุดนึงคู่กับตัวแรกของอีกชุด)={1*10,2*20,3*30}={10,40,90} |
| การ Confirm สูตร | กด Enter หรือ เลื่อนไป Cell อื่น | กด Ctrl+Shift+Enter |
| ผลลัพธ์ | ได้ข้อมูลออกมาค่าเดียวเสมอ= 10 เลยตัวเดียว | ข้อมูลสามารถออกมาได้หลายตัวจากสูตรเดียวอาจกระจายอยู่หลายๆ ช่องได้ เช่น ได้ผลลัพธ์เป็น 3 ค่า คือ = {10,40,90}แต่ Excel ไม่สามารถแสดงข้อมูล 3 ตัวในช่องเดียวกันได้ เลยขึ้นแค่คู่แรก คือ 10 |
เรามีทางเลือก 2 ทางหลักๆ ครับ คือ
ปกติเรามักจะใส่สูตร Array โดยเป็น Range ที่มีขนาดเท่าๆ กัน มาทำอะไรซักอย่าง เช่น บวก ลบ คูณ หาร เชื่อมText หรือเอาไปใส่ใน Function ต่างๆ
เช่น ในรูปข้างล่างเป็น Range ที่มีขนาด 1 แถว 3 คอลัมน์ 2 อันมาคูณกัน Excel จะทำการจับคู่คำนวณ โดยเอาสมาชิกแต่ละอันดับมาคูณกัน (ตัวแรกคู่ตัวแรก ตัวที่สองคู่ตัวที่สอง…)
แบบนี้จะได้ผลลัพธ์ออกมา 3 ตัว เหมือนกับ Range 2 ตัวที่เป็น Input
อีกแบบหนึ่งที่อาจพบบ่อย คือ ตัวนึงเป็น Range อีกตัวเป็นตัวเดี่ยวๆ เวลา Excel จับคู่มันจะจับตัวเดี่ยวคู่กับสมาชิกทุกตัวที่เป็น Range เลย
แบบนี้จะได้ผลลัพธ์ออกมา 3 ตัว เช่นกัน (เหมือนกับ Range ที่เป็น Input)
ถ้าเขียนสูตรปกติ ก็ต้องกดไว้ก่อนว่าขาดเป้าเท่าไหร่ แล้วค่อยเขียน SUM รวมอีกที
ถ้าเขียนสูตรปกติ จะต้องทดไว้ก่อนว่าแต่ละคำยาวกี่ตัวอักษร แล้วค่อยใช้ MAX มาครอบ แต่ถ้าใช้ Array ก็สามารถเขียนใน Cell เดียวได้เลย
ให้ B2 เป็นช่องที่เราจะใส่รหัสประชาชน 13 หลักลงไป แล้วเราจะตรวจเลขตำแหน่งสุดท้ายได้ด้วยสูตรนี้ครับ (เป็น Array Formula ที่ไม่ต้องกด Ctrl+Shift+Enter เนื่องจากเป็นความสามารถพิเศษของ SUMPRODUCT ครับ)
=RIGHT(11-MOD(SUMPRODUCT(--(MID(B2,ROW(INDIRECT("1:12")),1)),14-ROW(INDIRECT("1:12"))),11),1)
การเขียน Array Formula นั้นผมเชื่อว่ามีคนไม่ถึง 5% ของคนทั้งโลกที่ใช้มันเป็น… ดังนั้นข้อดีของมันอย่างแรกก็คือ “ถ้าคุณใช้เป็นคุณจะโคตรเทพ!” ซึ่งอันนี้เป็นข้อดีทางจิตใจ อิอิ
ส่วนข้อดีทางเทคนิคจริงๆ ของ Array Formula ก็คือ มันช่วยให้…
คราวนี้ก็ขึ้นอยู่กับคุณแล้วล่ะ ว่าอยากจะเรียนรู้การเขียนสูตรแบบ Array รึเปล่า? เพราะถ้าถามว่าจำเป็นสุดๆ มั้ย? ก็ต้องตอบว่า ถึงใช้ Array ไม่เป็นก็น่าจะพอหาคำตอบแบบเดียวกันได้ แต่อาจจะยุ่งยากพอสมควรหรือต้องทดไว้ใน Cell อื่นเยอะมากๆ (เช่น ถ้าจะหาค่า Percentile ของข้อมูลตาม Criteria ต่างๆ นี่อาจจะต้องทดเยอะมากๆ เลย)
ใครที่อยากเรียนรู้ Array Formula เพิ่มแบบลึกซึ้งยิ่งขึ้น สามารถศึกษาได้จาก หนังสือ จอมเวท เทพ Excel ครับ
หากใครสนใจสามารถอ่านได้ที่ เปลี่ยนสูตรยากให้เป็นสูตรกล้วยๆ ด้วย Dynamic Array ใน Excel 365