Author: Sira Ekabut

  • วิธีใช้ฟังก์ชัน ROUND ปัดเศษทศนิยม

    วิธีใช้ฟังก์ชัน ROUND ปัดเศษทศนิยม

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

    อย่างไรก็ตามการปัดเศษทศนิยมนั้นมีอยู่ 2 ลักษณะใหญ่ๆ คือ ปัดแค่การมองเห็น (ไม่เปลี่ยนค่าจริง) กับ ปัดค่าจริงๆ เลย ซึ่งการปัดแค่การมองเห็นนั้น เราสามารถกดปุ่ม Increase/Decrease Decimal ตรง Number Format ได้เลยอยู่แล้ว (คิดว่าหลายๆ คนคงทำเป็นอยู่แล้วล่ะ)

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

    ROUND, ROUNDUP, ROUNDDOWN

    ฟังก์ชันที่ใช้บ่อยที่สุดในการปัดตัวเลขก็คือ ฟังก์ชันกลุ่ม ROUND, ROUNDUP, ROUNDDOWN นั่นเอง ซึ่งผมขอพูดถึงตัวที่เข้าใจง่ายที่สุดก่อน นั่นก็คือ ROUNDDOWN

    ROUNDDOWN ปัดเศษทศนิยมทิ้งเสมอ

    ฟังก์ชันนี้ มีวิธีใช้งาน คือ

    =ROUNDDOWN(number,num_digits) หรือแปลได้ว่า
    =ROUNDDOWN(ตัวเลขที่จะปัด,จำนวนทศนิยมที่จะให้คงไว้)

    เช่น =ROUNDDOWN(1234.5465 , 2) จะได้ 1234.54 เพราะต้องการทศนิยม 2 ตำแหน่ง และการใช้ ROUNDDOWN แปลว่า ตำแหน่งที่เหลือให้ตัดทิ้งได้เลย ไม่ต้องสนใจว่าจะมีค่ามากหรือน้อยแค่ไหน

    เช่น =ROUNDDOWN(1234.5465 , 1) จะได้ 1234.5 เพราะต้องการทศนิยม 1 ตำแหน่ง และการใช้ ROUNDDOWN แปลว่า ตำแหน่งที่เหลือให้ตัดทิ้งได้เลย ไม่ต้องสนใจว่าจะมีค่ามากหรือน้อยแค่ไหน

    ROUNDUP ปัดเศษทศนิยมขึ้นเสมอ

    ฟังก์ชันนี้ก็คล้ายๆ ROUNDDOWN แต่จะดูว่ามีตัวเลขถัดจากตำแหน่งทศนิยมที่ต้องการรึเปล่า? ถ้ามีก็จะปัดขึ้น เช่น

    =ROUNDUP(1234.502,2) = 1234.51
    =ROUNDUP(1234.502,1) = 1234.6

    ROUND ปัดเศษทศนิยมตามหลักคณิตศาสตร์

    ฟังก์ชันนี้ทำตัวผสมกันระหว่าง ROUNDUP และ ROUNDDOWN โดยจะดูว่าตัวเลขถัดจากตำแหน่งทศนิยมที่ต้องการถึงเลข 5 หรือไม่? ถ้าถึงก็จะปัดขึ้นแบบ ROUNDUP ถ้าไม่ถึง ก็จะปัดเศษทิ้งแบบ ROUNDDOWN เช่น

    =ROUND(1234.5465,2) = 1234.55
    =ROUND(1234.5465,1) = 1234.5

    เรื่องของเรื่องคือ เจ้า ROUND, ROUNDUP, ROUNDDOWN ทั้ง 3 ตัวนี้ สามารถใส่จำนวน Digit ทศนิยมให้เป็นเลข 0 หรือ ติดลบก็ได้!! (วิธีการพิจารณาตัวถัดไปมันก็จะวิ่งย้อนกลับไปทางซ้าย) เช่น

    =ROUND(1234.5465,1) = 1234.55
    =ROUND(1234.5465,0) = 1235
    =ROUND(1234.5465,-1) = 1230
    =ROUND(1234.5465,-2) = 1200

    สรุปแล้วเป็นดังนี้

    ปัดเศษทศนิยม ROUND
  • ฟังก์ชันสรุปข้อมูลพื้นฐาน

    ฟังก์ชันสรุปข้อมูลพื้นฐาน

    การหาผลสรุปข้อมูลแบบพื้นฐานใน Excel อย่างเช่น SUM COUNT AVERAGE MAX MIN นั้นอาจมีบงเรื่องที่คุณยังไม่รู้จักดีก็ได้ มีอะไรมาดูกัน

    SUM : ฟังก์ชันแรกที่คุณรู้จัก…รู้จักดีรึยัง?

    หากถามว่า “อะไรคือฟังก์ชันใน Excel อันแรกที่คุณรู้จัก?” ผมคิดว่าหลายคนน่าจะตอบว่าฟังก์ชัน SUM แน่นอน

    แต่หารู้ไม่ว่า มันอาจมีบางเรื่องที่คุณอาจไม่รู้เกี่ยวกับฟังก์ชัน SUM ก็ได้ เช่น

    ฟังก์ชันสรุปข้อมูล SUM COUNT AVERAGE MAX MIN

    จากรูปนี้ ผม SUM ได้ผลลัพธ์ 6000 แทนที่จะเป็น 9000

    สาเหตุเพราะว่า SUM จะทำการสรุปข้อมูล โดยหาผลรวมจาก Data ที่เป็นข้อมูลประเภทตัวเลข เท่านั้น!! (ถ้าใครไม่ระวัง เจอข้อมูลที่เน่าๆ มี Text ปนมาหน่อย อาจได้ผลลัพธ์ที่ผิดแบบไม่รู้ตัวก็ได้ครับ)

    ฟังก์ชันสรุปผลแบบพื้นฐาน

    ฟังก์ชันสรุปผลแบบ Basic ตัวอื่นๆ ก็เช่นกัน ส่วนใหญ่ก็จะหาผลสรุปจากข้อมูลที่เป็นตัวเลขเท่านั้น (แต่มีบางตัวที่สรุปได้จากข้อมูลประเภทอื่นด้วย เช่น การนับด้วย COUNTA)

    • SUM = หาผลรวมจากช่องที่เป็นตัวเลขเท่านั้น
    • AVERAGE = หาค่าเฉลี่ยจากช่องที่เป็นตัวเลขเท่านั้น
    • MAX = หาค่ามากสุดจากช่องที่เป็นตัวเลขเท่านั้น
    • MIN = หาค่าน้อยสุดจากช่องที่เป็นตัวเลขเท่านั้น
    • COUNT = นับ cell ที่เป็นตัวเลขเท่านั้น
    • COUNTA = COUNT ALL นับ cell ที่ไม่ใช่ช่องว่าง
  • ทำไมถึงต้องเก่ง Excel ?

    ทำไมถึงต้องเก่ง Excel ?

    อ่านสารพัดเหตุผลที่ทำไมคุณจะต้องเก่ง Excel ได้ที่นี่

    ทำน้อย ได้มาก สุดยอด!

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

    ผมยังคงความสามารถระดับเดิมอยู่จนเรียนจบปริญญาตรีวิศวะ จุฬาฯ และได้มีโอกาสไปทำงานเป็นวิศวกรที่บริษัทแห่งหนึ่ง ผมได้เห็นรุ่นพี่คนหนึ่งใช้ Excel เก่งมาก ซึ่งทำให้เค้าทำงานได้เร็ว (และดูสบายๆ ชิลๆ ด้วย) และที่สำคัญเขายังเขียนโปรแกรมบน Excel ให้มันทำงานอัตโนมัติได้ด้วย!

    “เฮ้ย! Excel มันทำงานอัตโนมัติได้ด้วยเหรอ!?” ผมคิดในใจดังๆ (ด้วยความเซอไพรซ์มาก) ซึ่งตอนหลังผมก็ได้เรียนรู้ว่ามันคือการเขียนภาษาคอมพิวเตอร์ที่เรียกว่า VBA ซึ่งเป็นภาษาที่เอาไว้ควบคุมการทำงานของโปรแกรมบน Microsoft Office ได้ทั้งหมดนั่นเอง

    “อย่างนี้ ถ้าเราใช้ Excel ได้เก่งเทพๆ แบบพี่คนนี้เราก็สบายขึ้นเยอะเลยสิ!!” นี่แหละคือหนทางที่จะ Work Smart อย่างแท้จริง ในแบบที่พนักงานธรรมดาๆ แบบเราก็น่าจะทำได้

    ตอนนั้นเองทำให้ผมเริ่มที่จะสนใจ Excel ขึ้นมาเป็นครั้งแรก “ทำน้อย ได้มาก สุดยอด!”

    Excel คือเครื่องทุ่นแรง

    ที่บอกว่า “ทำน้อย ได้มาก” มันก็คือหลักการของ “เครื่องทุ่นแรง” (Leverage) ที่ช่วยให้คุณสบายขึ้น กูรูหลายท่านเรียกคำนี้ว่า “พลังทวี” ซึ่งจะเห็นว่าผลลัพธ์ ขึ้นอยู่กับ 2 ปัจจัยหลักดังนี้

    ผลลัพธ์ = แรงที่เราออก (ความรู้เกี่ยวกับงาน)  x  พลังในการทุ่นแรง (ความรู้ Excel)

    อ่านสารพัดเหตุผลที่ทำไมคุณจะต้องเก่ง Excel ได้ที่นี่
    • ถ้าคุณมีทักษะเดิมที่ดี แต่ไม่รู้จักการนำ Excel มาช่วย คุณก็จะต้องออกแรงเยอะเกินความจำเป็น…
    • ในทางกลับกัน หากคุณเก่ง Excel มาก แต่ไม่มีทักษะหรือความรู้พื้นฐานอื่นเลย มันก็จะไม่ช่วยอะไร… เพราะว่า 0 x 100 ก็ยังคงได้ 0 อยู่ดี… จริงมั๊ยครับ?

    Excel ทำอะไรได้มากกว่าที่คิด

    ต่อมา ผมก็เปลี่ยนแผนไปเรียนต่อปริญญาโท MBA จึงลาออกจากบริษัทเดิมซะก่อน…การฝึก Excel ของผมจึงพักไปชั่วครู่ เพราะไม่รู้จะฝึกใช้ไปทำไม (ตอนนั้นคิดสั้นมากกก)

    พอได้มีโอกาสมาเรียนโท MBA ที่จุฬาฯ ผมก็ได้เจอเพื่อนที่เก่ง Excel มากอีกคนหนึ่ง ตอนทำงานกลุ่มเพื่อแก้โจทย์ปัญหาเกี่ยวกับการวางแผนการผลิตสินค้าและกระจายสินค้า ว่าจะผลิตสินค้า A B C อย่างละกี่ชิ้นดี และส่งกระจายสินค้าด้วยเส้นทางไหนถึงจะได้กำไรสูงสุด มันบอกว่าถ้าให้ Excel มามันแก้ได้สบายๆ เลย

     “เฮ้ย! Excel มันแก้โจทย์ปัญหาได้ด้วยเหรอ? นึกว่าทำได้แต่ตารางข้อมูล” คราวนี้ผมไม่คิดในใจแล้ว ผมถามมันเลย

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

    ที่สำคัญตอนเรียนปริญญาโท MBA อาจารย์มีการสั่ง(และสอน)ให้ใช้ Excel ช่วยทำงานเยอะมาก ทั้งวิชา Accounting, Statistics, Finance, Operations Management และอีกมากมาย

    ผมจึงได้เรียนรู้แล้วว่า Excel มันช่วยเราได้เกือบทุกวิชาเลย อย่างนี้ก็แปลว่าการทำงานจริงก็สามารถใช้ Excel มาช่วยได้อีกเพียบเลยน่ะสิ! (มิน่าล่ะ..พี่ที่ทำงานเก่าถึงดูเจ๋งจัง)

    คราวนี้ผมเข้าไปค้นหาข้อมูลในอินเตอร์เน็ตใหญ่เลย ผมได้ค้นพบความเจ๋งๆ ของ Excel เพิ่มอีกมากมาย (คุณลองดูสิ)

    Basic ต้องแน่นก่อน

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

    แต่พอจะเขียน Resume แล้วมันให้กรอกทักษะการใช้ Microsoft Office ต่างๆ ซึ่งส่วนใหญ่จะถามทั้ง Word Excel PowerPoint

    ผมเองในตอนนั้นก็รู้สึกกระดากมือ จะเขียนว่าผมเก่ง Excel ก็ไม่กล้า เพราะจริงๆ ตอนนั้นก็ไม่เก่งเท่าไหร่ ถึงเขียนมั่วไป เวลาสัมภาษณ์ก็โดนถามจนรู้ความจริงอยู่ดี

    เมื่อคิดได้เช่นนั้น ผมจึงต้องฝึกที่จะเรียนรู้วิธีใช้ Excel วิเคราะห์ข้อมูลให้ผมเก่งขึ้นจริงๆ เท่านั้น!

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

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

    ทำไมถึงต้องเก่ง Excel ? 1

    เมื่อรู้อย่างนั้น ผมตัดสินใจไม่ทำตัวเป็นน้ำเต็มแก้ว แต่พยายามทำตัวเป็นแก้วที่ไม่มีน้ำ (ซักหยด) แทนแล้วเริ่มต้นเรียนรู้ Excel ใหม่จากศูนย์

    ผมตั้งใจอ่าน E-Book จำนวนมาก และเรียนวีดีโอสอน Excel ใน YouTube ของ ExcelisFun ซึ่งเค้าสอนดีมากๆ ตั้งแต่พื้นฐานจน Advance เลย แต่วีดีโอเค้ามีเยอะมาก (เป็นพันคลิป) ผมจึงต้องพยายามเลือกดูให้ได้ประสิทธิภาพสูงสุด

    มั่นใจว่าคุณเองก็สามารถเก่ง Excel ได้

    ข้อเท็จจริงอันหนึ่งที่หลายคนมักจะลืมคิดไปก็คือ คนเก่งทุกคนล้วนเคยไม่เก่งมาก่อนทั้งนั้น ไม่มีใครเล่นกีฬา เล่นดนตรี ทำงาน หรือใช้ Excel เก่งมาตั้งแต่เกิดหรอกครับ

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

    อย่างกรณีตัวผมเอง อย่างที่บอกว่าผมอยากทำงานที่น่าจะได้วิเคราะห์ข้อมูลต่างๆ หรือทำงานเกี่ยวกับตัวเลข ผมจึงสนใจงานด้านการเงินการธนาคารเป็นอย่างมาก และเพื่อเตรียมตัวให้พร้อมสำหรับการสัมภาษณ์งานเข้าทำงานที่ธนาคารสีเขียว (ซึ่งต่อไปจะขอเรียกว่า Bank) ผมใช้เวลาเกือบๆ  1 เดือนเต็มๆ ฝึก Excel อย่างจริงจัง ทั้งอ่านหนังสือ E-Book ฝรั่ง และดูคลิป Excel ใน YouTube มากมาย

    พอได้มีโอกาสเข้ามาทำงานที่ Bank จริงๆ ผมใช้เวลาเพียงแค่ 2-3 เดือน ก็สามารถเรียนรู้ทักษะ Excel เพิ่มขึ้น จนผมได้กลายเป็นที่ปรึกษาด้าน Excel ประจำ Office ไปแล้ว!

    เมื่อผมเริ่มมีความรู้มากขึ้น ผมก็อยากจะแบ่งปันความรู้ให้กับคนมากขึ้นอีก จึงตัดสินใจว่าจะต้องให้ความรู้กับคนในโลกออนไลน์ด้วย ระหว่างนั้นเพื่อให้เกิดความมั่นใจในความรู้ตัวเองมากขึ้น ผมก็ตัดสินใจลองไปสอบใบ Certificate Excel ของ Microsoft ด้าน Excel ดู จนในที่สุดผมได้รับใบ Certificate ชื่อ Microsoft Office Specialist : Excel ระดับ Expert มาจนได้ และผมก็พยายามหาความรู้มากขึ้นอย่างต่อเนื่อง (และตอนนี้ก็ยังไม่หยุด แถมกำลังเรียนรู้เครื่องมือใหม่ๆ ที่คนใช้ Excel อย่างเราสามารถต่อยอดได้ไม่ยาก อย่าง Power BI อีกด้วย)

    ทั้งหมดนี้ก็เริ่มมาจากคนที่เคยไม่เก่ง Excel มาก่อนคนหนึ่ง เพราะฉะนั้น จงมั่นใจเถอะว่าคุณก็เก่ง Excel ได้ (ในเวลาไม่นานด้วย) 

  • การทำ Data Validation ขั้นพื้นฐาน

    การทำ Data Validation ขั้นพื้นฐาน

    บังคับให้กรอกข้อมูลตามที่กำหนดด้วย Data Validation

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

    ปัญหานี้สามารถลดให้เหลือน้อยลงได้โดยการใช้เครื่องมือที่ชื่อว่า Data Validation

    องค์ประกอบของ Data Validation

    1. ในแต่ละช่องจะยอมให้กรอกข้อมูลอะไรได้บ้าง (Settings) เช่น ตัวหนังสือ ตัวเลข วันที่
      โดยกำหนดละเอียดได้ด้วยว่า จะยอมให้กรอกได้กี่ตัวอักษร กรอกห้ามเกินค่าเท่าไหร่ ไม่ต่ำกว่าเท่าไหร่
    2. ข้อความแนะนำ เมื่อมีการเลือก Cell (Input Message)
    3. เมื่อกรอกผิดจะให้ขึ้นเตือนว่าอะไร?? (Error Alert)
    data validation

    Dropdown List

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

    การทำ Data Validation ขั้นพื้นฐาน 2

    ใครทำ Dropdown List ได้แล้ว สนใจอยากทำ Dropdown List หลายชั้น อ่านได้ที่นี่

    Data Validation ขั้นสูง

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

    เช่นรูปข้างล่าง ผมใส่เงื่อนไขว่าต้องนับแต่ละคำได้ไม่เกิน 1 ครั้ง (รายละเอียดวิธีทำ อ่านได้ที่นี่)

    prevent-duplicate-entry2

  • สิ่งที่เราเห็นใน Cell มาจากไหน ?

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

    ตัวอย่างที่ 1

    สิ่งที่เราเห็นใน Cell มาจากไหน ? 3

    สมมติว่าใน cell นึงเราเขียนสูตรว่า =1.5+3 ส่วนนี้คือ Formula

    มันก็จะคำนวณจนได้ค่าผลลัพธ์ คือ 4.5 ส่วนนี้คือ Value

    สิ่งที่เราเห็นใน Cell มาจากไหน ? 4

    จากนั้นถ้าเราลอง ปรับ Format ให้ไม่มีทศนิยม โดยกดปุ่ม Decrease Decimal

    สิ่งที่เราเห็นใน Cell มาจากไหน ? 5

    จะได้เห็นตัวเลขใน Cell เป็น 5
    แต่ค่าจริงๆ ใน Cell ยังคงเป็น 4.5 อยู่เช่นเดิมนะครับ

    สิ่งที่เราเห็นใน Cell มาจากไหน ? 6

    ลองพิสูจน์ได้โดยลองเช็คว่า A1=5 รึเปล่า? จะได้ FALSE แต่ถ้าเช็คว่าได้ 4.5 รึเปล่าจะได้ TRUE

    สิ่งที่เราเห็นใน Cell มาจากไหน ? 7

    ทั้งนี้เป็นเพราะการปรับ Format เป็นเพียงปรับสิ่งที่มองเห็น แต่ไม่กระทบกับค่าที่แท้จริงใน Cell แต่อย่างใดครับ

    ตัวอย่างที่ 2

    basic1

    ใน A1 มีค่าที่แท้จริงเป็น 2.5 แต่แสดงผลเป็น 3 เพราะปรับ Format ให้ไม่มีทศนิยม
    แต่เวลานำไปคำนวณ Excel ก็ยังเอาค่าที่แท้จริงคือ 2.5 ไปคำนวณอยู่ดี

    เช่น =A1*A2 แปลว่าให้เอาค่าใน A1 คูณด้วยค่าใน A2 ซึ่งในที่นี้ได้ 5 ไม่ใช่ 6
    เพราะเอาค่าที่แท้จริง คือ 2.5 ไปคูณ 2 ต่างหาก (ไม่ใช่ 3*2)

    นี่คือประเด็นสำคัญที่ผมอยากจะเน้นย้ำในบทความนี้ครับ ^^

  • สร้าง Drop down list กี่ชั้นก็ได้ใน Excel (Drop down list หลายชั้น)

    สร้าง Drop down list กี่ชั้นก็ได้ใน Excel (Drop down list หลายชั้น)

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

    ใครสนใจคลิ๊กดูได้เลยครับ ในคลิปนี้ผมใช้้ OFFSET ในการแก้ปัญหาครับ ซึ่งผลลัพธ์จะเป็นแบบนี้

    Drop down list หลายชั้น Drop down list กี่ชั้นก็ได้

    ทำ Drop down list หลายชั้นแบบข้ามชีท (New!)

    โหลดไฟล์ประกอบได้ที่นี่
    https://drive.google.com/open?id=1tynrAHEkNc_XCj7HjOMo3w8i8m2tLxoD

    ใครยังไม่แม่น OFFSET ลองดูคำอธิบายในคลิปนี้ก่อนนะครับ

    สร้าง Drop down list หลายชั้นใน Excel ด้วย OFFSET
    สามารถ download ไฟล์ประกอบของคลิปเก่าได้ที่นี่ครับ http://bit.ly/infinity-dropdown

  • เทคนิคการทำงานใน Excel แบบคิดย้อนกลับ

    เทคนิคการทำงานใน Excel แบบคิดย้อนกลับ

    วันนี้ inwexcel ขอนำเสนอเทคนิคที่ผมใช้บ่อยมากๆ ในการทำงานจริง นั่นก็คือ เทคนิคการ “คิดย้อนกลับ” นั่นเอง

    การคิดย้อนกลับ คือ อะไร?

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

    ปล. ถ้าใครรู้จักหนังสือพัฒนาตัวเองสุดฮิตอย่าง 7 Habits ก็จะพบว่าหลักการนี้ก็เหมือนกับหลักการ ที่ชื่อว่า Begin with the End in Mind หรือ เริ่มต้นด้วยจุดมุ่งหมายในใจ นั่นเองครับ

    ตัวอย่างการคิดย้อนกลับใน Excel

    เทคนิคการทำงานใน Excel แบบคิดย้อนกลับ 8

    เป้าหมาย

    สมมติว่าสุดท้ายแล้ว เป้าหมาย คือ เราอยากได้กราฟ ที่แสดง Trend กำไรในแต่ละเดือน โดยเปรียบเทียบระหว่างช่องทาง E-Channel กับ ช่องทางการขายหน้าร้าน

    คิดย้อนกลับ

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

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

    คิดย้อนกลับ

    จะสร้าง PivotTable ได้เราจะต้องเตรียมข้อมูลให้อยู่ในลักษณะของ Database ซะก่อน
    ซึ่งปัจจัยสำคัญคือการ List ออกมาว่า Database จะต้องมี Field หรือ คอลัมน์ว่าอะไรบ้าง?

    เราก็ต้องคิดว่า Pivot มันต้องมี 3 Field หลัก คือ เดือน / กำไร / ช่องทางการขาย

    และเราก็ต้องคิดต่อว่าการจะได้ Field เหล่านั้นมา จะต้องเตรียม Field อะไรเพิ่มอีก ก็จะพบดังนี้

    • เดือน : ควรเก็บข้อมูลเป็นวันที่ไปเลยดีกว่า ละเอียดดี แล้วไป Group ใน Pivot หรือ จะสร้าง Field คำนวณเพิ่มก็ได้
      • วันที่ที่ขายได้
    • กำไร : จะรู้กำไรได้ ต้องรู้่ยอดขาย และ ต้นทุน
      • ยอดขาย
      • ต้นทุน
    • ช่องทางการขาย => มี Field เดียว แต่มี 2 ค่า คือ E-Channel และ หน้าร้าน

    สรุป

    สรุปแล้วเราต้องเตรียมข้อมูลดังนี้

    • วันที่ขายได้
    • ยอดขาย
    • ต้นทุน
    • กำไร
    • ช่องทางการขาย

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

    เมื่อเตรียมข้อมูลเสร็จแล้ว ก็เอาไปทำ PivotTable เพื่อทำตารางสรุป แล้วสร้างกราฟด้วย PivotChart ก็ได้ครับ

  • วิธี Fix Cell Reference ไม่ให้สูตรเลื่อน ทำยังไงไม่ให้งง

    วิธี Fix Cell Reference ไม่ให้สูตรเลื่อน ทำยังไงไม่ให้งง

    พื้นฐานที่สำคัญมากในการเขียนสูตร Excel คือการใส่เครื่องหมาย $ เพื่อ Fix Cell Reference (เช่น A1 C4 ) เวลาเขียนสูตรเพื่อไม่ให้สูตรเลื่อนเวลาเรา Copy Paste ซึ่งถ้าใครงงหรือทำไม่เป็น จะไม่สามารถเขียนสูตรที่ซับซ้อนในอนาคตได้เลย ดังนั้นเรามาปูพื้นฐานให้แน่นยิ่งขึ้นดีกว่า

    ทำไมต้อง Fix Cell ไม่ให้สูตรเลื่อนล่ะ?

    เพราะปกติว่าเขียนสูตรที่มี Cell Reference แล้วเรา Copy Cell ช่องนั้นไป Paste ที่อื่น Cell Reference มันจะเลื่อนไปตามทิศทางของการ Copy

    ซึ่งบางกรณีการเลื่อนนั้นก็เป็นประโยชน์กับเรา เช่น

    วิธี Fix Cell Reference ไม่ให้สูตรเลื่อน ทำยังไงไม่ให้งง 9

    เวลา Copy ลงมาข้างล่าง Cell Reference ก็จะเลื่อนตามลงมา (จาก B3 -> B4 และจาก C3 -> C4) ซึ่งสะดวกมาก

    วิธี Fix Cell Reference ไม่ให้สูตรเลื่อน ทำยังไงไม่ให้งง 10

    แต่มันก็จะมีบางกรณีที่เราไม่อยากให้มันเลื่อนตามลงมา เช่น

    วิธี Fix Cell Reference ไม่ให้สูตรเลื่อน ทำยังไงไม่ให้งง 11

    ถ้า Copy ลงมาข้างล่างก็จะได้ผลลัพธ์ที่ผิดทันที สาเหตุเพราะมันดันเลื่อนลงมานั่นเอง

    วิธี Fix Cell Reference ไม่ให้สูตรเลื่อน ทำยังไงไม่ให้งง 12

    เราจึงต้องมีการใส่ $ ลงไปที่ Cell Reference เพื่อไม่ให้มันเลื่อน ซึ่งสามารถเลือกที่ Cell Reference ในสูตรแล้วกดคีย์ F4 เช่น

    วิธี Fix Cell Reference ไม่ให้สูตรเลื่อน ทำยังไงไม่ให้งง 13

    เวลา Copy ลงมา ไอ้ H1 ก็จะไม่เลื่อนตามแล้ว แค่นี้ก็สามารถ Copy ได้อย่างสบายในจ และผลลัพธ์ถูกต้อง

    วิธี Fix Cell Reference ไม่ให้สูตรเลื่อน ทำยังไงไม่ให้งง 14

    ใส่เครื่องหมาย $ ได้กี่แบบ?

    ใส่ได้ทั้งหมด 4 แบบ ตามรูป ซึ่งสามารถเปลี่ยนรูปแบบได้เรื่อยๆ โดยการกดปุ่ม F4 เวลาเขียนสูตรแล้ว Cursor อยู่ที่ Cell Reference นั้นๆ

    Fix Cell Reference ไม่ให้สูตรเลื่อน

    แล้วถ้าซับซ้อนขึ้นล่ะ คิดยังไง?

    ตามรูปข้างล่าง เราจะต้องเขียนสูตรใน B3 ยังไง ให้เขียนทีเดียวแล้ว Copy ไปใช้ได้ทั้งตาราง (เช่น พอไปอยู่ที่ D6 จะต้องเป็น A6*D2)

    วิธี Fix Cell Reference ไม่ให้สูตรเลื่อน ทำยังไงไม่ให้งง 15

    ซึ่งถ้าเราไม่ใส่ $ เลยผลลัพธ์จะมั่วซั่ว เพราะ Cell Reference มันเลื่อน

    วิธี Fix Cell Reference ไม่ให้สูตรเลื่อน ทำยังไงไม่ให้งง 16

    วิธีคิดเวลาที่งง

    วิธี Fix Cell Reference ไม่ให้สูตรเลื่อน ทำยังไงไม่ให้งง 17

    ให้ดู Cell Reference ทีละตัว เช่น ให้ดู A3 ก่อน แล้วค่อยดู B2

    A3 : คอลัมน์ A ต้องไม่เลื่อนไปทางขวา แต่แถวเลข 3มันต้องเลื่อนลงมาข้างล่าง ดังนั้น ต้องใส่ $ แค่คอลัมน์ A

    วิธี Fix Cell Reference ไม่ให้สูตรเลื่อน ทำยังไงไม่ให้งง 18

    B2 : คอลัมน์ B ต้องเลื่อนไปทางขวา แต่แถวเลข 2 ต้องไม่เลื่อนลงมาข้างล่าง ดังนั้น ต้องใส่ $ แค่แถว 2

    วิธี Fix Cell Reference ไม่ให้สูตรเลื่อน ทำยังไงไม่ให้งง 19

    เพียงเท่านี้ ก็ Copy Paste ได้ทั้งตารางแล้วววว หลักการง่ายๆ แค่ค่อยๆ ดูทีละ Cell Reference นะครับ ^^

    วิธี Fix Cell Reference ไม่ให้สูตรเลื่อน ทำยังไงไม่ให้งง 20
  • หายหัวร้อน! ไขความลับวิธีคำนวณดาเมจเกม ROV และ ผลจากการออกของต่างๆ

    หายหัวร้อน! ไขความลับวิธีคำนวณดาเมจเกม ROV และ ผลจากการออกของต่างๆ

    ผมเพิ่งได้มีโอกาสเล่นเกม ROV (Realm of Valor) มาประมาณ 2-3 อาทิตย์ ซึ่งบอกเลยว่าเล่นแล้วติดมาก เพราะมันสนุก 555 แต่มีสิ่งหนึ่งที่ผมหงุดหงิดมาก คือ มันหาข้อมูลยากมากเลยว่าวิธีคำนวณดาเมจต่างๆ ในเกม มันคิดยังไงกันแน่? (คือพอนึกออกมะ? มันเป็นเกม ดังนั้น มันต้องมีกฎที่ชัดเจนแน่นอน แค่เรายังหาไม่เจอ)

    ซึ่งถ้าเรารู้กฎของเกม รู้วิธีคำนวณต่างๆ มันจะทำให้เราเลือกกลยุทธ์การเล่นที่เหมาะสมได้นะ !!

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

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

    ค่า Stat พื้นฐานของแต่ละตัวละครไม่เหมือนกัน

    ตัวละคนแต่ละตัว จะมี Stat พื้นฐานไม่เหมือนกัน
    และเมื่อ Level Up จะมี Stat บางตัวเพิ่มขึ้นทำให้ตัวละครเก่งขึ้น แต่ Stat บางตัวจะไม่เพิ่มขึ้นนะ

    ที่น่าสนใจคือ ไม่มีตัวไหนมี Stat พลังเวทติดตัวมาเลย และ การต้านทานเวท ทุกตัวเท่ากันหมดทุก Level เลย

    การออกไอเท็มเพื่อเพิ่มค่า Stat

    ไอเท็มในเกม สามารถออกได้สูงสุด 6 ชิ้นด้วยกัน ซึ่งมีหลักการคิดที่ผลของไอเทมจะแสดงออกมาซ้อนกันด้วย นั่นคือ

    • ถ้าไม่ใช่สกิลติดตัว : จะเอาค่า Effect มารวมกันเลย มีหลายอันก็เอามารวมได้เรื่อยๆ
    • ถ้าเป็น สกิลติดตัว : หากเป็น Effect เหมือนกัน จะเอา Effect อันที่มีค่ามากที่สุดมาอันเดียว

    เช่น ผมออก Spooky Mask 2 อัน (ความสามารถของมันคือ +100 พลังเวท และ สกิลติดตัวคือ เจาะเกราะเวท +75)

    หายหัวร้อน! ไขความลับวิธีคำนวณดาเมจเกม ROV และ ผลจากการออกของต่างๆ 21
    หายหัวร้อน! ไขความลับวิธีคำนวณดาเมจเกม ROV และ ผลจากการออกของต่างๆ 22

    มันจะ +100 พลังเวท 2 ที รวมเป็น +200 พลังเวท แต่เจาะเกราะเวท +75 ที่เป็นสกิลติดตัวจะมาแค่อันเดียว

    แต่ถ้าออก Staff of Nuul ที่ + เจาะเกราะเวท 45% ด้วยมันจะถือว่าเป็นคนละ Effect กัน จึงสามารถแสดงผลได้

    หายหัวร้อน! ไขความลับวิธีคำนวณดาเมจเกม ROV และ ผลจากการออกของต่างๆ 23

    นี่ไง ได้ทั้งเจาะเกราะ +75 และ +45% เลย

    หายหัวร้อน! ไขความลับวิธีคำนวณดาเมจเกม ROV และ ผลจากการออกของต่างๆ 24

    ดังนั้น การออกสมุดเวท 6 เล่ม จึง +พลังเวท +400*6 = +2400 แต่จะบวกพลังชีวิตแค่ 1400 เท่านั้นนะ

    หายหัวร้อน! ไขความลับวิธีคำนวณดาเมจเกม ROV และ ผลจากการออกของต่างๆ 25
    หายหัวร้อน! ไขความลับวิธีคำนวณดาเมจเกม ROV และ ผลจากการออกของต่างๆ 26

    วิธีการคิดค่าดาเมจ

    การทดสอบ#1

    ผมเข้าโหมดฝึกซ้อม แล้วเอาฮีโร่ไปโจมตีครีป และ Hero อีกฝั่งหนึ่ง โดยที่ไม่ได้ใส่ Item เจาะเกราะเลย แต่มีการเพิ่มพลังโจมตีไปเรื่อยๆ ผลลัพธ์ที่ได้คือ Damage ที่เกิดขึ้นจริงนั้นมีสัดส่วน %ที่คงที่ (%ที่ Damage หายไป คงที่) ซึ่งคิดว่าน่าจะเป็นเพราะเกราะ

    หายหัวร้อน! ไขความลับวิธีคำนวณดาเมจเกม ROV และ ผลจากการออกของต่างๆ 27

    การทดสอบ#2

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

    แล้วผลก็ออกมาชัดเลย ว่า % Damage ที่หายไปขึ้นอยู่กับ %เกราะของศัตรู อย่างชัดเจน (ค่า Damage จริง จะแกว่งเล็กน้อย % เลยไม่เป๊ะ)

    หายหัวร้อน! ไขความลับวิธีคำนวณดาเมจเกม ROV และ ผลจากการออกของต่างๆ 28

    การทดสอบ#3

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

    • ช่วงแรกๆ ถ้าเพิ่มเกราะ %เกราะจะเพิ่มเยอะมาก
    • แต่ช่วงหลังๆ เวลาเกราะเยอะแล้ว เวลาเพิ่มเกราะเข้าไป… %เกราะก็จะแทบไม่เพิ่มแล้ว
      (แบบว่าเพิ่มให้ตายก็ไปตันที่ประมาณ 80%)
    • ซึ่งเลขนี้ใช้ได้กับค่า ต้านทานเวท กับ % ต้านทานเวทด้วย ซึ่งเป็นเลขเดียวกันเป๊ะเลย
    คำนวณดาเมจ ROV ROV Damage Calculation

    ตารางเปรียบเทียบทั้งหมด ดูได้ที่นี่

    เกราะ/ต้านทานเวท%เกราะ/%ต้านทานเวท
    507.6%
    588.8%
    6710.0%
    7511.1%
    8312.1%
    8412.2%
    8612.5%
    8712.6%
    8812.7%
    8912.9%
    9113.1%
    9213.2%
    9513.6%
    9613.7%
    10014.2%
    10114.4%
    10314.6%
    10414.7%
    10715.1%
    10915.3%
    11015.4%
    11816.4%
    12016.6%
    12116.7%
    12317.0%
    12517.2%
    12617.3%
    12817.5%
    12917.6%
    13518.3%
    13718.5%
    13818.6%
    13918.8%
    14018.9%
    14219.1%
    14319.2%
    14819.7%
    14919.8%
    15220.2%
    15420.4%
    15520.5%
    15620.6%
    16021.0%
    16921.9%
    17022.0%
    17122.1%
    17222.2%
    17622.6%
    18623.6%
    18823.8%
    18923.9%
    19024.0%
    20225.1%
    20525.4%
    20625.5%
    21025.9%
    21126.0%
    21726.5%
    22227.0%
    22327.0%
    22927.6%
    23027.7%
    23127.7%
    23227.8%
    23928.4%
    24028.5%
    24829.2%
    25129.4%
    25529.8%
    25629.9%
    25830.0%
    26330.4%
    26930.9%
    27131.1%
    27231.1%
    27531.4%
    27831.6%
    28231.9%
    28932.5%
    29232.7%
    29332.8%
    30533.7%
    30633.7%
    30833.9%
    30933.9%
    31234.2%
    31334.2%
    31534.4%
    32334.9%
    32635.2%
    33335.6%
    33535.8%
    33635.8%
    33836.0%
    35036.8%
    35337.0%
    36137.5%
    36938.0%
    37138.2%
    37438.3%
    38839.2%
    39439.6%
    40240.1%
    40940.5%
    41440.8%
    44142.3%
    45943.3%
    46743.7%
    46943.8%
    49445.1%
    56948.6%
    59449.7%
    66952.7%
    69453.6%
    76956.1%
    79456.9%
    89459.8%
    99462.3%
    109464.5%
    119466.5%
    129468.3%
    139469.9%
    149471.3%
    159472.6%
    169473.8%
    179474.9%
    189475.9%
    199476.8%
    209477.7%
    219478.5%
    229479.2%
    235479.6%
    241480.0%
    247480.4%
    253480.8%
    259481.2%
    265481.5%
    หายหัวร้อน! ไขความลับวิธีคำนวณดาเมจเกม ROV และ ผลจากการออกของต่างๆ 29

    พอไปดูในข้อมูลฮีโร่ในโหมดฝึก เจ้า Thane ในโหมดฝึก มันจะมีเกราะ 259 มาตั้งแต่ต้นที่ Level 1 เลย ( ปกติ Thane Level 1 จะเกราะไม่เยอะขนาดนี้นะ) ซึ่งพอดูจากกราฟโค้งๆ ข้างบน เกราะ 259 มันคือ % เกราะประมาณ 30% พอดี จึงยิ่งมั่นใจว่าผมน่าจะเข้าใจเรื่องกราฟถูกต้อง

    การทดสอบ#4

    หลังจากนั้นผมลองไปดูเรื่องผลของการเจาะเกราะในโหมดฝึก

    หายหัวร้อน! ไขความลับวิธีคำนวณดาเมจเกม ROV และ ผลจากการออกของต่างๆ 30

    ตอนแรกก็ยังดูไม่ค่อยออกว่ามันคำนวณยังไงกันแน่ เพราะ เจาะเกราะดูเหมือนไม่ได้สัมพันธ์กับ Damage ที่หายไปตรงๆ (เช่น เจาะเกราะ 60 ไม่ได้ทำให้ค่า Damage ต่างกัน 60) แต่ที่แน่ๆ การเจาะเกราะ และ % เจาะเกราะ มันทำให้เกราะศัตรูลดลงไป

    ลองคิดๆๆๆๆๆ

    เจาะเกราะ

    ถ้าเจาะเกราะ 60 คือการลดเกราะศัตรูลงไป 60 หน่วยล่ะ แล้วค่อยไปดูกราฟโค้งอีกทีว่า % เกราะเหลือประมาณเท่าไหร่?
    นั่นคือ เกราะจะเหลือ =259-60 = 199 ซึ่งจะคิดเป็น % เกราะประมาณ 25%
    ซึ่งใช่เลย!! จากผลการทดลอง Damage หายไป 24.7% ซึ่งใกล้เคียงมาก

    %เจาะเกราะ

    แล้ว %เจาะเกราะล่ะ ถ้า 35% มันคือลดเกราะศัตรูไป 35% รึเปล่าน่ะ?
    นั่นคือ =259- (35%*259) = 168.35 จากกราฟโค้งได้ 21.9% ซึ่งจากการทดลองคือ 22% ซึ่งมันใช่เลย

    ถ้า %เจาะเกราะ คือ 45% ก็ต้องเป็น =259- (45%*259) = 142.45 จากกราฟโค้งจะได้ 19.2% ซึ่งการทดลองได้ 20% ก็ถือว่าตรงนะ

    ถ้ามีเจาะเกราะด้วย % เจาะเกราะด้วยล่ะ? จะคิดอะไรก่อนหลัง?

    จากที่ผมทดลอง คือ เจาะเกราะ 60    %เจาะเกราะ 45

    • ถ้าคิด %เจาะเกราะก่อน แล้วค่อยคิดเจาะเกราะ
      • เกราะที่ถูกเจาะ =259*45%  + 60 = 176.55
      • เกราะคงเหลือ = 259-176.55 = 82.45 จากกราฟโค้งได้ 12.1%
    • ถ้าคิด เจาะเกราะก่อน แล้วค่อยคิด %เจาะเกราะ 
      • เกราะที่ถูกเจาะ =60 + (259-60)*45%  = 149.55
      • เกราะคงเหลือ = 259-149.55 = 109.45 จากกราฟโค้งได้ 15.3% เป๊ะ!!

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

    และหลักการนี้ก็บ่งบอกด้วยว่า เนื่องจากรูปร่างกราฟโค้ง ถ้าเราเจาะเกราะของคนที่มีเกราะเยอะมาก ก็อาจทำให้ % เกราะยังคงเหลือเยอะอยู่เล่นกัน เรียกได้ว่า ช่วงเกราะเยอะ ทั้งเพิ่ม % เกราะยาก และลด % เกราะยากทั้งคู่นั่นแหละ!!
    แต่วิธีที่จะลดเกราะของศัตรูที่เยอะๆ ได้ดีที่สุด ต้องเน้น item ที่เจาะเกราะเป็น % มากกว่านะครับ

     สรุปวิธีการคำนวณดาเมจ

    • พลังโจมตีกายภาพ และพลังเวท ใช้หลักการเดียวกันเป๊ะ โดยเทียบกันดังนี้
      • พลังโจมตี (กายภาพ) == พลังเวท
      • เกราะ == ต้านทานเวท
      • %เกราะ == %ต้านทานเวท
      • เจาะเกราะ == เจาะเกราะเวท
      • %เจาะเกราะ == %เจาะเกราะเวท
    • พลังโจมตีถูกต้านทานด้วยเกราะ
      • ดาเมจที่ศัตรูโดน = พลังโจมตี * (1- %เกราะ)
    • เกราะ เป็นตัวสร้าง % เกราะ
      • ยิ่งเกราะ มาก %เกราะ ก็ยิ่งมาก แต่จะเพิ่มในอัตราที่ลดลงเรื่อยๆ (ทั้งเกราะ และ ต้านทานเวท ใช้กราฟโค้งเดียวกัน)
    • เกราะศัตรูสามารถถูกลดลงได้จากการถูก เจาะเกราะ และ % เจาะเกราะ
      • เกราะคงเหลือจะโดน คิดจากเจาะเกราะธรรมดาก่อน แล้ว ค่อยคิดจาก %เจาะเกราะ
      • เกราะคงเหลือ = เกราะ – ( เจาะเกราะ + (เกราะ – เจาะเกราะ)*%เจาะเกราะ )
      • แล้วค่อยเอา เกราะคงเหลือที่คำนวณได้ ไปดูกราฟโค้ง ว่ามี %เกราะเท่าไหร่

    การดูดเลือด และ เวทแวมไพร์

    ดูดเลือด และ เวทแวมไพร์  คิดเป็น % จากดาเมจที่ทำได้ครับ เช่น

    ถ้าใส่ Soul Reaver 5 อัน จะดูดเลือด 50% สมมติ เวลาโจมตีเข้า 400 ก็จะดูดเลือด 200 ครับ

    หายหัวร้อน! ไขความลับวิธีคำนวณดาเมจเกม ROV และ ผลจากการออกของต่างๆ 31
    หายหัวร้อน! ไขความลับวิธีคำนวณดาเมจเกม ROV และ ผลจากการออกของต่างๆ 32
    หายหัวร้อน! ไขความลับวิธีคำนวณดาเมจเกม ROV และ ผลจากการออกของต่างๆ 33
    หายหัวร้อน! ไขความลับวิธีคำนวณดาเมจเกม ROV และ ผลจากการออกของต่างๆ 34

    การโจมตีคริติคอล

    เมื่อโจมตีติดคริติคอล (ตัวเลขดาเมจอันใหญ่ๆ ) ดาเมจที่เกิดขึ้นจะเป็น 2 เท่าของดาเมจปกติครับ

    เช่น

    หายหัวร้อน! ไขความลับวิธีคำนวณดาเมจเกม ROV และ ผลจากการออกของต่างๆ 35
    หายหัวร้อน! ไขความลับวิธีคำนวณดาเมจเกม ROV และ ผลจากการออกของต่างๆ 36

    พลังโจมตี 411 เกราะศัตรู 183 จากกราฟโค้งคือ ประมาณ 23%

    ดาเมจปกติ = 411-(1-23%) = 316.47 ในรูปได้ 314

    ดาเมจคริติคอล = ดาเมจปกติ *2 = 314*2 = 628

  • แจกฟรี รวม Bookmark Link เว็บ Excel เจ๋งๆ ทั้งไทยและเทศ

    แจกฟรี รวม Bookmark Link เว็บ Excel เจ๋งๆ ทั้งไทยและเทศ

    ถ้าใช้ Chrome ให้ทำตามขั้นตอนต่อไปนี้

    1. โหลดไฟล์ได้ที่นี่ => https://drive.google.com/file/d/0B4xG7-sIyJBDTFhscW84WmpSSkk/view?usp=sharing
      แจกฟรี รวม Bookmark Link เว็บ Excel เจ๋งๆ ทั้งไทยและเทศ 37
      แล้ว save ลง folder ที่ต้องการ
      แจกฟรี รวม Bookmark Link เว็บ Excel เจ๋งๆ ทั้งไทยและเทศ 38
    2. ไปที่ Bookmark Manager (หรือกด Ctrl+Shift+O)
      แจกฟรี รวม Bookmark Link เว็บ Excel เจ๋งๆ ทั้งไทยและเทศ 39
    3. ไปที่ Organize -> Import bookmark from HTML file
      แจกฟรี รวม Bookmark Link เว็บ Excel เจ๋งๆ ทั้งไทยและเทศ 40
    4. มันจะโหลดมาไว้ใน Folder Imported ซึ่งจะโผล่มาล่างสุด ซึ่งถ้าอยู่ท้ายเกินไปจนมองไม่เห็น ให้ลากขึ้นมาไว้บนๆ
      แจกฟรี รวม Bookmark Link เว็บ Excel เจ๋งๆ ทั้งไทยและเทศ 41
    5. ถ้าให้ดี ลาก Folder inwExcel Link ออกไว้ในในชั้นนอกสุด (นอก Imported) จะได้เห็นชัดๆ เลยดีกว่า
      แจกฟรี รวม Bookmark Link เว็บ Excel เจ๋งๆ ทั้งไทยและเทศ 42
    6. เพียงเท่านี้ก็เสร็จแล้ว คราวต่อไปจะได้เข้าไปอ่านเว็บเหล่านี้ได้ง่ายๆ ครับ
      นี่เว็บไทย
      แจกฟรี รวม Bookmark Link เว็บ Excel เจ๋งๆ ทั้งไทยและเทศ 43
      นี่เว็บเมืองนอก
      แจกฟรี รวม Bookmark Link เว็บ Excel เจ๋งๆ ทั้งไทยและเทศ 44
  • วิธีใช้ Slicer แทน Drop down หลายชั้น

    วิธีใช้ Slicer แทน Drop down หลายชั้น

    ผมเคยได้อธิบายวิธีทำ Drop down หลายชั้นไปแล้ว คราวนี้เราจะมาลองใช้ Slicer ทำแบบนั้นกันบ้างครับ ซึ่งตอน post แบบน้ำจิ้มไว้ใน Facebook ผมแสดงเทคนิคที่ต้องใช้ Excel 2013 ขึ้นไปดังนี้ แล้วบอกว่าจะเขียนวิธีทำแบบละเอียดให้

    slicer drop down

    แต่เผอิญวันนี้ผมมาต่างจังหวัด ในคอมมันมีแต่ Excel 2010 …

    ดังนั้นเดี๋ยวผมจะเปลี่ยนวิธีมาใช้แบบที่ Excel 2010 ขึ้นไปทำได้ครับ (แต่มันไม่เจ๋งเท่า Excel 2013)

    วิธีใช้ Slicer แบบที่ Excel 2010 ทำได้

    ก่อนอื่นให้เตรียมตารางความสัมพันธ์ของข้อมูลเอาไว้ก่อน เช่น

    วิธีใช้ Slicer แทน Drop down หลายชั้น 45

    จากนั้นกด Convert เป็น Table (Ctrl+T) เดี๋ยวเราจะเอาไป Pivot ครับ

    วิธีใช้ Slicer แทน Drop down หลายชั้น 46

    แล้วเราก็เอาไป Pivot แล้วเอา ทุก Field ไปไว้ที่ Row Label

    วิธีใช้ Slicer แทน Drop down หลายชั้น 47

    เปลี่ยน Layout เป็น Tabular Form (อยู่ใน Ribbon Design)

    วิธีใช้ Slicer แทน Drop down หลายชั้น 48

    เลือก Do not show subtotal

    วิธีใช้ Slicer แทน Drop down หลายชั้น 49

    คราวนี้ใส่ Slicer ทั้ง 3 อัน

    วิธีใช้ Slicer แทน Drop down หลายชั้น 50

    ลองเลือกดู ก็จะเห็นว่ามันทำงานได้แล้ว แต่มันดันแสดง Item ที่ไม่เกี่ยวข้องอยู่ แต่จะเป็นสีเทาๆ แล้วจะแสดงอยู่ท้ายๆ (ก็ไม่แย่มากนะผมว่า ^^ )

    วิธีใช้ Slicer แทน Drop down หลายชั้น 51
    วิธีใช้ Slicer แทน Drop down หลายชั้น 52

    ซึ่ง Excel 2013 ขึ้นไป จะสามารถไปที่ Slicer Setting แล้วซ่อน Item ที่ไม่เกี่ยวข้องได้

    แต่ Excel 2010 ผมเข้าใจว่าทำไม่ได้ครับ  ผมก็เลยต้องใช้วิธีปรับสีของ Slicer ให้ Item ที่ไม่ได้เลือกมองไม่เห็นซะ (หลอกตาคนดูเอา)

    โดยผมแนะนำให้ Duplicate  Style เดิมที่คุณชอบออกมา แล้ว Modify แก้ Style ของ Selected Item with no Data กับ Unselected item with no data ให้กลายเป็นสีขาวไปเลยก็ได้ 555

    วิธีใช้ Slicer แทน Drop down หลายชั้น 53

    จากนั้นลองเล่นดู แค่นี้ก็ดูเนียนแล้ว 5555

    วิธีใช้ Slicer แทน Drop down หลายชั้น 54
    วิธีใช้ Slicer แทน Drop down หลายชั้น 55

    อย่าลืมนะครับ ถ้าถูกใจช่วยบอกต่อด้วยนะ ^^

  • วิธีนับข้อมูลใน Pivot แบบนับไม่ซ้ำกัน (Distinct Count)

    วิธีนับข้อมูลใน Pivot แบบนับไม่ซ้ำกัน (Distinct Count)

    เวลาที่ใช้ PivotTable เพื่อวิเคราะห์ข้อมูล หลายๆ คนน่าจะมี Moment ที่อยากสรุปข้อมูลโดยนับจำนวนสิ่งที่สนใจแบบไม่ซ้ำ หรือ Distinct Count กันบ้างแหละ…

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

    “แต่การนับแบบนี้มันเป็นเรื่องยากมากเลยนะ จะให้นับยังไงล่ะ ก็ใน Pivot มันมีแต่ Count กับ Count Number นี่นา?” นี่คงเป็นสิ่งที่หลายคนคิดมาโดยตลอด

    ผมอยากจะบอกว่า ใช่ครับ มันเคยเป็นเรื่องยากมากๆ แต่ว่าตั้งแต่ Excel 2010 เป็นต้นไป หากเราใช้ Concept ของ Data Model เข้ามาช่วยในการวิเคราะห์ข้อมูลแล้วล่ะก็….

    Pivot Table มันจะมีความสามารถใหม่เพิ่มขึ้นมานั่นก็คือ Distinct Count นั่นเอง!! และเรื่องของเรื่องคือ มันทำง่ายมากกกกกกกก!!

    วิธีการทำให้ PivotTable นับแบบ Distinct Count

    1. ต้องสร้าง PivotTable แบบที่ใช้ Data Model เข้าช่วย
    (ถ้าใครใช้ Excel 2010 ต้องสร้างผ่าน PowerPivot รายละเอียดคล้ายๆ บทความ OLAP CUBE อันนี้ครับ)

    วิธีนับข้อมูลใน Pivot แบบนับไม่ซ้ำกัน (Distinct Count) 56

    2. ทำการจัด PivotTable ตามรูปแบบที่ต้องการก่อน

    วิธีนับข้อมูลใน Pivot แบบนับไม่ซ้ำกัน (Distinct Count) 57

    3.คลิ๊กขวาที่ Field ที่ต้องการเปลี่ยน เลือก Summarize Value By –> More Options…

    วิธีนับข้อมูลใน Pivot แบบนับไม่ซ้ำกัน (Distinct Count) 58

    4.เลือก Distinct Count กด ok จบ!

    Distinct Count

    แค่นี้ผลลัพธ์ก็ออกมาแล้ว ง่ายมากๆ จริงมั้ยครับ ^^

    Distinct Count

    ช่วยกันบอกต่อ

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

  • วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula

    วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula

    จากที่ผมได้ Post ถามหยั่งเชิงใน Facebook ไปว่าใครสนใจวิธีทำลายข้อจำกัดเรื่องหน้าตาของ PivotTable บ้าง? ปราฏว่ามีคนให้ความสนใจเยอะมากๆ ดังนั้นผมเลยขอมาเขียนบทความอธิบายให้เล็กน้อยครับ

    PivotTable นั้นสร้างง่ายมาก แต่จัด Layout ไม่ได้ดั่งใจ

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

    แต่สิ่งที่มักจะเกิดขึ้นตามมาคือ คำพูดของหัวหน้าที่บอกว่า “รายงานนี้มันหน้าตาไม่สวยเลย ช่วยจัด xxx เว้นบรรทัด yyy แทรกคอลัมน์ zzz หน่อยสิ”

    แล้วคุณก็คงตอบกลับไปแบบสุภาพว่า “อ๋อ ทำไม่ได้ครับ…มันเป็นข้อจำกัดของ PivotTable ครับหัวหน้า” ทั้งๆ ที่จริงๆ อาจอยากตะโกนตอบกลับไปว่า “PivotTable มันแทรกบรรทัดไม่ได้เฟ้ย!”

    วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 59

    วิธีจัด Layout ที่อาจพอรู้กันอยู่แล้ว

    แต่ถ้าคุณอยากจะทำให้รายงานมันสวยได้ดั่งใจจริงๆ ก็มีวิธีที่หลายคนอาจจะรู้อยู่แล้ว ดังนี้

    1. Copy Pivot แล้ว Paste Value เพื่อเอาไปทำรายงาน : วิธีนี้ไม่แนะนำเป็นอย่างยิ่ง เพราะเสียความสามารถในการ Refresh ข้อมูลไปเลย ควรทำเฉพาะกรณีที่คิดว่าทำครั้งเดียวในชีวิต ไม่ต้องทำอีกแล้ว 555
    2. ใช้ GETPIVOTDATA : ปกติเวลากด = แล้วคลิ๊กไปในพื้นที่ Pivot แต่ละ Cell มันจะดึงข้อมูล Cell นั้นมา โดยสร้างสูตรยาวๆ ที่ขึ้นต้นด้วยคำว่า GETPIVOTDATA มาให้ด้วย
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 60
      • ซึ่งข้อดีคือ ไม่ว่า Pivot Table ต้นทางจะย้ายไปไหน หรือมีการ Filter Row/Column Label บางอย่างจนข้อมูลเลื่อนไปที่อื่น เจ้า GETPIVOTDATA จะยังเอาข้อมูลที่ถูกต้องมาให้
      • ข้อเสียคือ จะต้องกด = จิ้มทีละ Cell หรือเขียนสูตรทีละ Cell ซึ่งลำบากมากกว่าจะได้ครับ
    3. กด = แล้วจิ้ม แบบ Cell Reference ปกติ : บางคนไม่ชอบ GETPIVOTDATA เลยไปเลือก Option ให้ Excel ไม่ต้องสร้างเจ้าฟังก์ชันนั้นขึ้นมาให้ (ติ๊ก Generate GetPivotData ออก) โดยอยากให้จิ้มแล้วเป็น Cell Reference ธรรมดาแทน จะได้เขียน/copy สูตรง่ายๆ
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 61 
      • ข้อดี : เขียนง่ายมาก แค่ = แล้วจิ้ม จากนั้น Copy มาได้ตามปกติ
        วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 62
      • ข้อเสีย : เสี่ยงต่อการที่ PivotTable เลื่อนจากที่เดิม ทำให้ได้ข้อมูลที่ผิดกลับไปโดยไม่รู้ตัว
    4. เลิกใช้ Pivot หันมาเขียนสูตรแทน : วิธีนี้เรียกว่าเปลี่ยนแนวโดยสิ้นเชิง เขียนสูตรเอาเองดีกว่า ไม่ง้อ Pivot ก็ได้ ไม่ว่าจะเป็นสูตรง่ายๆ อย่าง SUMIFS หรือจะใช้ Data Table มาช่วย หรืออาจจะใช้ Array Formula กรณีที่การคำนวณซับซ้อน
      • ข้อดี : มีความยืดหยุ่นสูงกว่า Pivot
      • ข้อเสีย : เขียนสูตรยาก (บางคนเจอตรงนี้ก็ยอมแพ้แล้ว) และอาจคำนวณช้ากว่า Pivot

    แต่วันนี้ผมมีอีกวิธีมาแนะนำครับ นั่นคือ ใช้ความสามารถที่เรียกว่า OLAP CUBE Formula

    วิธี OLAP CUBE Formula

    วิธีนี้ดีตรงที่ ไม่ต้องเขียนสูตรเอง และยังสามารถอัปเดทผลลัพธ์ได้เมื่อข้อมูลต้นทางเปลี่ยน เช่นเดียวกับ PivotTable เลย แต่ผมจะไม่ขอพูดทฤษฏี ณ ตอนนี้แล้วกันเอาเป็นว่าบทความนี้มาลองทำกันเลยนะครับ

    ผมจะขอแบ่งออกเป็น 2 วิธี คือ 1. ใช้ Data Model (Excel 2013 ขึ้นไป) 2. ใช้ PowerPivot (ซึ่งใช้ได้ตั้งแต่ Excel 2010 แต่ต้องลง Add-in) ซึ่งวิธีแรกน่าจะง่ายกว่าสำหรับคนส่วนใหญ่ครับ

    ใช้ Data Model (Excel 2013 ขึ้นไป)

    มี Step ประมาณนี้

    1. ตอนจะสร้าง PivotTable ให้ติ๊กช่อง Add to Data Model ด้วย
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 63
    2. สร้าง Pivot ให้สรุปข้อมูลตามปกติ เอาให้คล้ายรายงานที่ต้องการมากที่สุดก่อน
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 64
    3. ไปคลิ๊ก OLAP Tools -> Convert to Formulas…
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 65
       
      • เพียงเท่านี้ PivotTable ก็จะถูกแปลงเป็น Cell ที่เต็มไปด้วยสูตร CUBE ซึ่งข้อมูลแต่ละช่องจะแยกออกจากกัน
        วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 66
    4. จัด Layout ตามใจชอบ – คุณจะแทรกคอลัมน์ยังไง โยกข้อมูลยังไง ก็สามารถทำได้แล้ว
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 67

    จะเห็นว่า ผมจะปรับแต้งหน้าตายังไงก็ได้เลย ซึ่งอิสระขึ้นมากๆ ครับ ^^

    วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 68

    ใช้ PowerPivot (Excel 2010 ขึ้นไป)

    มี Step ประมาณนี้

    1. ถ้ายังไม่มี PowerPivot ให้ไปโหลดมา install ก่อน => Download ที่นี่
    2. Add Data เข้า Power Pivot โดยกดปุ่ม Create Linked Table
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 69
    3. สร้าง Pivot จากหน้าต่าง PowerPivot
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 70
    4. จากนั้นก็เหมือนแบบวิธี Data Model แล้วล่ะ จัดระเบียบแล้วกด Convert to Formulas โลด!
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 71
    5. Pivot จะถุกกระจายกลายเป็นสูตรที่แยกกันคนละช่องเช่นเดียวกับวิธีข้างบนครับ
      วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 72

    ผลลัพธ์ยังสามารถผูกกับ Slicer ได้เช่นเดิม

    หากตอนแรก Pivot ของเรามีการผูก Slicer ไว้ หลังจากแปลงเป็น CUBE Formula แล้ว Slicer ก็ยังทำงานได้นะครับ

    วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 73
    วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 74

    หลังกด Slicer ผลลัพธ์ก็จะถูกตัดกรองเช่นเดียวกับ PivotTable ปกติเลย!!

    วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 75

    แปลว่า เราสามารถเอาวิธีการนี้ ไปสร้าง Dashboard เท่ๆ ได้เลยนะ!!

    CUBE เป็นสูตร แต่ยังต้องกด Refresh เช่นเดียวกับ Pivot

    เนื่องจากสูตรแบบ CUBE เป็นการเชื่อมต่อกับฐานข้อมูล ดังนั้นเวลาทำงานจริง หากข้อมูลต้นทางเปลี่ยนไป จะต้องมีการกด Refresh ก่อน ผลลัพธ์จึงจะเปลี่ยน เช่นเดียวกับ Pivot Table ซึ่งเรา Refresh ได้ 2 ที่ คือ

    Refresh ที่ Data Connections

    ซึ่งสามารถ Refresh ได้ที่เครื่องมือ Data -> Refresh All ครับ

    วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 76

    อย่าลืม!! ถ้าเราไม่ Refresh ผลลัพธ์จะไม่เปลี่ยนนะ

    Refresh ที่ PivotTables

    แต่ถ้าใครไม่ชอบวิธีนี้ จะกด Refresh ที่ PivotTables ก็ได้นะ แต่ต้องทำการเก็บ Pivot ไว้อย่างน้อยอันนึงก่อนที่จะแปลงเป็น OLAP Formula นะครับ (ถ้าแปลงไปแล้ว จะกดที่กด Refresh แบบ Pivot ไม่เจอ)

    วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 77

    คราวนี้เราจะ Refresh ที่ Pivot ได้ตามปกติแล้ว

    วิธีทลายข้อจำกัดหน้าตาของ PivotTable ด้วย OLAP CUBE Formula 78

    จบแล้ว

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

  • แนวทางจัดการวันที่เน่า (ผิดไปแล้วทำไงดี)

    แนวทางจัดการวันที่เน่า (ผิดไปแล้วทำไงดี)

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

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

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

    ไฟล์ที่ผมลองทำ => mess-up-date2

    Screenshot การจัดการวันที่เน่า

    มีหลายขั้นตอนมาก

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

    ผมก็เลยพยายามแยก ปี เดือน วัน ออกมา แล้วลอง adjust เช่น ลบ 543 หรือ สลับวันกับเดือน

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

    ถ้าอยู่ในช่วงวันที่ที่กำหนด ก็แสดงว่า ok น่าจะถูกครับ (สุดท้ายได้คอลัมน์ R)

    แนวทางจัดการวันที่เน่า (ผิดไปแล้วทำไงดี) 79
    จัดการวันที่เน่า

    โดยสรุป

    ผมใช้หลายขั้นตอนมากๆ เพราะมีการ Error หลากหลายรูปแบบเหลือเกิน

    แต่จะมีวันที่บางอันที่ผิดแล้วไม่มีทางรู้ เช่น 3/2/2017 กับ 2/3/2017 ไม่รู้เลยว่าจริงๆ หมายถึง 3 กพ. หรือ 2 มีค. กันแน่? อันนี้คุณเองแหละที่ต้องรู้ดีที่สุดนะครับ ว่างานของคุณแต่ละอันมันน่าจะอยู่ในเดือนไหน?

    ใครลองทำตามนี้แล้วได้ผลหรือไม่ได้ผลยังไงช่วยบอกด้วย เผื่อผมจะได้ช่วยเขียนดักเพิ่มให้อีกนะครับ แต่ดีสุดคือ “กันไว้ดีกว่าแก้” นะครับ ^^

  • อะไรจะเกิดขึ้น? เมื่อใส่วันที่ใน Excel ด้วยปี พ.ศ. แทนที่จะใส่ ค.ศ.

    อะไรจะเกิดขึ้น? เมื่อใส่วันที่ใน Excel ด้วยปี พ.ศ. แทนที่จะใส่ ค.ศ.

    หลังจากบทความที่แล้ว ผมได้ให้คำแนะนำเกี่ยวกับการตั้งค่าวันที่ให้ถูกต้องแล้ว (เรื่อง วัน/เดือน/ปี หรือ เดือน/วัน/ปี )

    มาวันนี้ ผมจะขอย้ำอีกเรื่องนึงซึ่งเกี่ยวกับวันที่เช่นเดียวกัน (ซึ่งจริงๆ ได้พยายามย้ำหลายครั้งแล้ว)
    นั่นก็คือ… การทำงานเกี่ยวกับวันที่ใน Excel เราจะต้องใส่ปีเป็น ค.ศ. ไม่ใช่ พ.ศ. 
    เช่น หากต้องการใส่วันที่ 27 เมษายน พ.ศ. 2560 ให้ใส่ว่า 27/4/2017 ไม่ใช่ 27/4/2560 !

    เพราะถ้าใส่เป็น 27/4/2560 …Excel จะคิดว่าเราหมายถึง 27 เมษายน ค.ศ. 2560 ซึ่งเป็นวันในอนาคตอีก 543 ปี!! (บ้าไปแล้ว)

    บางคนถึงรู้อย่างงี้ก็บอกว่า… ฉันไม่แค่ร์หรอก! ก็เรารู้เองว่าจริงๆ มันคือ พ.ศ. 2560 ก็ได้ ยิ่งถ้านัดกับเพื่อนๆ ร่วมงานทุกคนให้ร่วมใจกันเข้าใจว่ามันเป็น พ.ศ. ก็น่าจะใช้ได้นี่นา? (เหมือนอุปาทานหมู่ หรือพวก ทฤษฎีสมคบคิดเลยแฮะ)

    ผมจะบอกว่าถึงเราจะฮั้วกับเพื่อนแล้วมันก็จะผิดอยู่ดี ซึ่งผมจะแสดงให้เห็นชัดๆ ว่ามันไม่ ok ยังไงบ้าง?

    ถ้าใส่เป็น พ.ศ. จะผิดยังไง?

    อะไรจะเกิดขึ้น? เมื่อใส่วันที่ใน Excel ด้วยปี พ.ศ. แทนที่จะใส่ ค.ศ. 80

    จะเห็นว่า ถ้าใส่เป็น พ.ศ. แล้ว…

    1. Excel จะไม่รู้จักวันที่ 29 กุมภา ในปีที่ควรรู้จัก แต่ดันไปคิดว่ามี 29 กุมภาในปีที่ไม่ควรจะมีแทน! (แปลว่า ถ้านับระยะห่างของวันก็อาจจะผิดได้ด้วย)
    2. เรื่องการตรวจสอบว่าวันนั้นเป็นวัน จันทร์,อังคาร,พุธ,พฤหัส,ศุกร์,เสาร์,อาทิตย์ จะผิดไปโดยสิ้นเชิง (แปลว่า ถ้าใช้พวก NETWORKDAYS, WORKDAYS เพื่อทำงานเกี่ยวกับวันทำงาน/วันหยุด ก็จะผิดเช่นกัน)

    วิธีตรวจสอบง่ายๆ ว่าใส่ถูกหรือผิด

    วิธีดูง่ายๆ เลยว่าใส่ถูกหรือไม่ถูก ให้ลองเปลี่ยน Format เป็น General ดู ถ้าเลขออกมาประมาณ 4 หมื่นกว่าๆ ก็น่าจะถูก แต่ถ้าออกมา 2 แสนนี่ผิดละ (เฉพาะกรณีที่เราใช้วันที่ในยุคปัจจุบันนะ)

    อะไรจะเกิดขึ้น? เมื่อใส่วันที่ใน Excel ด้วยปี พ.ศ. แทนที่จะใส่ ค.ศ. 81

    แล้วถ้าเราอยากให้มันแสดงเป็นปี พ.ศ. จะให้ทำไง?

    วิธีจะใส่ข้อมูลด้วย ปี. ค.ศ. แต่จะให้แสดงเป็น ปี พ.ศ. น่ะเหรอครับ ง่ายที่สุดคือ ทำการเปลี่ยน Number Format ซะ

    โดยเลือก Cell/Range ที่ต้องการเปลี่ยน Format แล้วคลิ๊กขวา เลือก Format Cells… แล้วทำตามรูป

    อะไรจะเกิดขึ้น? เมื่อใส่วันที่ใน Excel ด้วยปี พ.ศ. แทนที่จะใส่ ค.ศ. 82

    ถ้าอยากกรอกเป็น ปี พ.ศ. จริงๆ แต่ให้ถูกต้องล่ะ จะทำยังไง?

    จริงๆ มันก็ตั้งค่าให้กรอกเป็น พ.ศ. ได้ครับ แต่จะยุ่งยากหน่อย และห้ามลืมด้วย (ไม่งั้นก็ผิดอีก)

    แต่ถ้าดึงดันว่าอยากจะทำจริงๆ ก็ทำตามนี้ครับ
    คือต้อง set format ตามนี้ (ติํก Input Dates According to Selected Calendar ) ก่อนพิมพ์วันที่ลงไปนะ

    อะไรจะเกิดขึ้น? เมื่อใส่วันที่ใน Excel ด้วยปี พ.ศ. แทนที่จะใส่ ค.ศ. 83

     

     

  • การตั้งค่าให้ Excel รู้จักวันที่ในรูปแบบที่ต้องการ เช่น วัน/เดือน/ปี ไม่ใช่ เดือน/วัน/ปี

    การตั้งค่าให้ Excel รู้จักวันที่ในรูปแบบที่ต้องการ เช่น วัน/เดือน/ปี ไม่ใช่ เดือน/วัน/ปี

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

    นี่คือตัวอย่างกรณีที่ตั้งค่าผิด

    การตั้งค่าให้ Excel รู้จักวันที่ในรูปแบบที่ต้องการ เช่น วัน/เดือน/ปี ไม่ใช่ เดือน/วัน/ปี 84

    สิ่งที่เกิดขึ้นคือ เราตั้งใจพิมพ์รูปแบบวันที่ในรูปแบบ วัน/เดือน/ปี

    แต่หารู้ไม่ว่าคอมพิวเตอร์ที่เรากำลังใช้อยู่ดันถูกตั้งค่าใน Control Panel ให้เป็น เดือน/วัน/ปี ผลลัพธ์ที่ได้เลยมั่วนิ่มเลย!!

    อธิบายความผิดพลาด

    • 13/4/2017 ถูกจัดชิดซ้าย (เป็น Text) เพราะมันไม่รู้จัก เดือนที่ 13 นั่นเอง (วันที่ 4 เดือน 13 ค.ศ. 2017 ???)
    • แปลว่าเลข 10/4/2017 ที่เราเข้าใจว่าถูกต้อง จริงๆ ก็ผิดด้วย เพราะ Excel เข้าใจว่าเป็นวันที่ 4 ตุลาคม แต่จริงๆ เราต้องการเป็นวันที่ 10 เมษายนต่างหาก!!
    • ส่วนการที่ 3/5/2017 ลาก Fill Handle ลงมาแล้วเหมือนว่าเดือนจะเปลี่ยน จริงๆ แล้วสิ่งที่เปลี่ยนคือวันที่ถูกแล้ว แต่เพราะวันที่อยู่ตรงกลางเราเลยสับสน(เอามากๆ!)

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

    รู้ได้ยังไงว่าคอมพิวเตอร์ของเรากำลังใช้ Format แบบไหน?

    มี 2 วิธีครับ

    1. ลองกรอกวันที่ ที่มากกว่า 12 ดู เช่น วันสงกรานต์ก็ได้ 13/4/2017 ว่า Excel รู้จักหรือไม่? (ถ้ารู้จักจะต้องชิดขวา ไม่รู้จักจะชิดซ้าย)
      • ถ้าออกมาเป็นชิดซ้าย (Excel ไม่รู้จักว่าเป็นวันที่) แสดงว่าตั้งค่าใน Control Panel ผิด
    2. ลองกด Ctrl+; เพื่อ Stamp ค่าวันที่ปัจจุบันก็ได้
      • ถ้าออกมาไม่ใช่ วัน/เดือน/ปี แสดงว่าตั้งค่าผิด

    วิธีตั้งค่ารูปแบบวันที่ใน Control Panel

    เดี๋ยวเราจะไปเปลี่ยนค่า Region Format ใน Control Panel กันครับ ซึ่งขั้นตอนจะขึ้นกับ Version ของ Windows เลยครับ

    หลังจากทำตาม Step เหล่านี้เสร็จ Excel จะรู้จักวันที่ในรูปแบบ วัน/เดือน/ปี ทันทีโดยไม่ต้อง Restart โปรแกรมเลยนะครับ
    แต่อะไรที่พิมพ์พิมพ์ผิด Format ไปแล้วมันก็จะหลายเป็น Text แบบถาวรเลย ไม่ได้มีการ Convert ให้นะครับ !!

    ถ้าเป็น Windows 10

    1.ให้คลิ๊กที่รูป Windows แล้วไปเลือก Settings ที่เป็นรูปเฟืองก่อน

    การตั้งค่าให้ Excel รู้จักวันที่ในรูปแบบที่ต้องการ เช่น วัน/เดือน/ปี ไม่ใช่ เดือน/วัน/ปี 85

    2. เลือก Time & Language

    การตั้งค่าให้ Excel รู้จักวันที่ในรูปแบบที่ต้องการ เช่น วัน/เดือน/ปี ไม่ใช่ เดือน/วัน/ปี 86

    3. เลือก Additional Date Time & Regional Settings

    การตั้งค่าให้ Excel รู้จักวันที่ในรูปแบบที่ต้องการ เช่น วัน/เดือน/ปี ไม่ใช่ เดือน/วัน/ปี 87

    4. เลือก Region แล้วไป Tab ชื่อ Format แล้วเลือก Thai แล้ว OK

    การตั้งค่าให้ Excel รู้จักวันที่ในรูปแบบที่ต้องการ เช่น วัน/เดือน/ปี ไม่ใช่ เดือน/วัน/ปี 88

    ถ้าเป็น Windows 7

    1. เลือก Control Panel
    การตั้งค่าให้ Excel รู้จักวันที่ในรูปแบบที่ต้องการ เช่น วัน/เดือน/ปี ไม่ใช่ เดือน/วัน/ปี 89

    2. ไปที่ Clock, Language, and Region
    การตั้งค่าให้ Excel รู้จักวันที่ในรูปแบบที่ต้องการ เช่น วัน/เดือน/ปี ไม่ใช่ เดือน/วัน/ปี 90

    หรือ ตามนี้

    การตั้งค่าให้ Excel รู้จักวันที่ในรูปแบบที่ต้องการ เช่น วัน/เดือน/ปี ไม่ใช่ เดือน/วัน/ปี 91

    3. เลือก Region and Language แล้วไป Tab ชื่อ Format แล้วเลือก Thai แล้ว OK

    การตั้งค่าให้ Excel รู้จักวันที่ในรูปแบบที่ต้องการ เช่น วัน/เดือน/ปี ไม่ใช่ เดือน/วัน/ปี 92

     

    ใครทำตามนี้แล้วยังทำไม่ได้ comment ไว้ได้เลยครับ ผมจะได้ช่วยแก้ปัญหาให้ได้

  • ใส่ Comment เป็นรูปภาพก็ได้นะ รู้ป่าว?

    ใส่ Comment เป็นรูปภาพก็ได้นะ รู้ป่าว?

    มีแฟนเพจถามผมว่า เราสามารถใส่รูปภาพเข้าไปใน Comment ได้หรือไม่? ผมเลยขอตอบด้วยบทความนี้ครับ

    สมมติเราอยากใส่ Comment เป็นรูปภาพ ให้กับ Cell ที่ต้องการ ก็จะมีวิธีการดังนี้

    1. คลิ๊กขวา กด Insert Comment ตามปกติ
      ใส่ Comment เป็นรูปภาพก็ได้นะ รู้ป่าว? 93
    2. คลิ๊กขวาที่ Comment เลือก Format Comment แล้วกดตามรูป จนไปเจอ Fill Effect…
      ใส่ Comment เป็นรูปภาพก็ได้นะ รู้ป่าว? 94
    3. เลือกที่ Picture แล้ว Browse รูปที่เรามี
      ใส่ Comment เป็นรูปภาพก็ได้นะ รู้ป่าว? 95
    4. กด Ok ไปเรื่อยๆ ก็จะได้รูปมาเป็นฉากหลังให้กับ Comment แล้ว (ซึ่งเราพิมพ์ข้อความทับได้)
      ใส่ Comment เป็นรูปภาพก็ได้นะ รู้ป่าว? 96
  • งานเสร็จฉับไว ด้วย Flash Fill

    งานเสร็จฉับไว ด้วย Flash Fill

    คุณเคยมั้ย? ที่ต้องจัดการข้อมูลบางอย่างอย่างเร่งด่วน และไม่ใช่งาน Routine จะเอาเวลาไปเขียนสูตรก็คงไม่คุ้ม ผมมีเครื่องมือที่ชื่อว่า Flash Fill มาแนะนำสำหรับคนที่ยังไม่รู้จักครับ ซึ่งเครื่องมือนี้มามาตั้งแต่ Excel 2013 แล้วล่ะ มีแล้วก็ใช้ให้คุ้มหน่อย

    หลักการทำงานของ Flash Fill

    หากคุณต้องการผลลัพธ์แบบไหน ก็พิมพ์บอก Excel ไปซะ จากนั้นกด Ctrl+E แล้ว Excel จะพยายามหารูปแบบ (Pattern) จากสิ่งที่คุณพิมพ์ลงไปเอง ! (ถ้ายังหา Pattern ไม่ได้ ก็พิมพ์สอนเพิ่มไปอีก แล้วมันจะฉลาดขึ้น)

    Step การทำงานของ Flash Fill

    1. เตรียมข้อมูลดิบและคอลัมน์ผลลัพธ์

    สมมติคุณมีข้อมูลแบบนี้ และคอลัมน์สีเหลืองคือสิ่งที่คุณต้องทำเพิ่ม

    งานเสร็จฉับไว ด้วย Flash Fill 97

    2. ใส่ตัวอย่างสอน Excel

    คุณสามารถพิมพ์ผลลัพธ์ที่ต้องการไปก่อนซัก 1-2 บรรทัด เพื่อทำการสอน Excel ให้รู้ว่าอยากได้อะไร (ลองบรรทัดเดียวก่อนก็ได้)

    งานเสร็จฉับไว ด้วย Flash Fill 98

    3. ใช้ Flash Fill

    ให้คลิ๊กที่ช่องที่เราสอน (หรือจะคลิ๊กช่องข้างใต้ก็ได้) แล้วกด Ctrl+E ทีละช่อง (ทีละคอลัมน์)

    งานเสร็จฉับไว ด้วย Flash Fill 99

    ถ้ามันโวยวายแบบนี้ แสดงว่ามันงง ให้เราสอนเพิ่มอีกบรรทัดนึง แล้วกด Ctrl+E คราวนี้น่าจะได้แล้ว

    งานเสร็จฉับไว ด้วย Flash Fill 100

    กด Ctrl+E ทีละคอลัมน์

    งานเสร็จฉับไว ด้วย Flash Fill 101

    กด Ctrl+E ทีละคอลัมน์

    Flash Fill

    สังเกตว่าผลลัพธ์ที่ได้ มันเลียนแบบได้ดีทีเดียว แม้กระทั่ง การ Extract ข้อความ การเติมข้อความ เติมสัญลักษณ์ . และ @ หรือแม้กระทั่งเรื่องตัวพิมพ์เล็กพิมพ์ใหญ่มันก็จัดการให้ได้นะครับ

    ถ้ามันเติมให้ผิดล่ะ?

    ถ้ามันให้ผลลัพธ์ผิด ก็ต้องสอนเพิ่มไปอีกซักบรรทัดนึง มันน่าจะฉลาดขึ้นครับ

    โห! มันฉลาดจัง แล้วแบบนี้จะเขียนสูตรให้ยากทำไม?

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

    อ่อ! อย่าลืมว่า Flash Fill ใช้ได้แค่ Excel 2013 ขึ้นไปเท่านั้นนะครับ ดังนั้นถ้าใครยังใช้ Excel Version เก่าก็ใช้ความสามารถนี้ไม่ได้หรอกนะครับ 

    คุณใช้ Flash Fill ทำอะไรเจ๋งๆ บ้าง?

    ใครใช้ Flash Fill ทำอะไรเจ๋งๆ ได้ มาแชร์ความรู้กันได้นะครับ อยากเห็นตัวอย่างการใช้ Flash Fill เจ๋งๆ ของเพื่อนๆ จังครับ

    ใครนึกไม่ออก ลองดูรูปพวกนี้เป็นไอเดียได้ครับ

    ตัวอย่างอื่นๆ

    ใส่ตัวคั่นตัวเลข

    งานเสร็จฉับไว ด้วย Flash Fill 102

    ดึงวันเดือนปี

    งานเสร็จฉับไว ด้วย Flash Fill 103
  • Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ

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

    แต่ก่อนจะบอก Steps การทำงานทั้งหมด ผมต้องแนะนำให้พอรู้จักลักษณะการทำงานของ Power Query กันซักหน่อย ว่ามันทำงานประมาณไหน จะได้นึกภาพออก

    ลักษณะการทำงานของ Power Query

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 104

    ผมคิดว่า Power Query เป็นเหมือนเครื่องมือมหัศจรรย์ ที่สามารถ รวบรวม&เชื่อมต่อกับข้อมูลได้หลายที่ หลายชนิด แล้วนำข้อมูลแต่ละอันมาคัดกรอง & ดัดแปลงให้อยู่ในรูปแบบที่เหมาะสม รวมถึงผสมข้อมูลหลายก้อนเข้าด้วยกันได้ ซึ่งผลลัพธ์สุดท้ายจะออกมาเป็นตารางให้เราสามารถนำไปใช้งานต่อได้อย่างง่ายดาย ไม่ว่าจะเอาไปใช้ใน PivotTable ทำกราฟ หรือจะเอาไปเขียนสูตรใน Excel ต่อก็ย่อมได้

    หรือถ้าจะให้นิยามแบบสั้นๆ ผมขอบอกว่า Power Query คือ เครื่องมือรวบรวมและดัดแปลงข้อมูล นั่นเอง

    ประเด็นที่สำคัญที่สุดเลย คือ มันเก่งมาก แต่ใช้งานไม่ยากเลย! ดังนั้นอย่ารอช้า เรามาเรียนรู้วิธีการใช้งาน Power Query กันเลยดีกว่า!

    Steps การใช้งาน Power Query เบื้องต้น

    จากคำบรรยายลักษณะการทำงานของ Power Query ผมก็ขอแบ่งออกมาเป็นขั้นตอนดังนี้

    1.รวบรวม & เชื่อมต่อข้อมูล

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

    สมมติผมมีข้อมูลอยู่ 3 ที่ คือ 1. ในไฟล์ Txt 2.ในไฟล์ Excel ที่จะทำ PowerQuery เอง 3.ในไฟล์ Excel อื่น โดยให้โหลดไฟล์ที่นี่

    ก่อนอื่นให้เปิดไฟล์ที่จะทำ Power Query ซะก่อน (ชื่อไฟล์ PowerQuery-Basic.xlsx)

    วิธีเชื่อมต่อข้อมูลก็ง่ายมาก  ไปที่ Ribbon Power Query แล้วมันจะมีให้เลือกว่าจะเอาข้อมูลจากไหน?

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 105

    ปกติที่ใช้บ่อยๆ ก็จะมีจาก

    • From File = เอาจากไฟล์อื่น เช่น TXT, CSV, ไฟล์ Excel เอง รวมถึงเอามาทั้ง Folder ได้ด้วย
    • From Database = เอาจากไฟล์ Access
    • From Table/Range = เอาจาก Table หรือ Range ที่อยู่ในไฟล์เดียวกันนี้

    เช่นไฟล์ data1.txt ผมก็ต้องเลือก From File -> TXT/CSV -> เลือกไฟล์ที่ต้องการ

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 106

    ซึ่งตรง Load จะมีลูกศรให้เลือกว่า จะ Load เฉยๆ หรือ Load to และปุ่มข้างๆ มีคำว่า Edit ซึ่งแต่ละอันมีความหมายดังนี้

    • Load = จะถูกสร้างเป็น Table ในไฟล์ Excel ที่เรากำลังเปิด
    • Load to = จะมี Option ให้เลือกต่อว่า จะสร้างเป็น Table หรือ จะแค่สร้างการเชื่อมต่อเฉยๆ (Create Connection Only)
    • Edit = จะสามารถเข้าไปแก้ไขข้อมูลก่อนที่จะดึงข้อมูลมาใช้ เช่น แก้ชื่อตาราง, Sort/Filter, เพิ่มคอลัมน์คำนวณ, และอีกมากมาย…  (ซึ่งทั้งหมดนี้เราดึงข้อมูลมาใช้ก่อนแล้ว Edit ทีหลังก็ได้)

    ในที่นี้ผมขอเลือก Load to แล้วติ๊ก Only Create Connection ดีกว่า ไฟล์จะได้ไม่ใหญ่ด้วย

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 107

    เมื่อกด Load ก็จะมีหน้าต่างบอกว่า มีการเชื่อมต่อกับข้อมูล Data1 แล้ว

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 108

    ผมทำแบบเดียวกันกับ Data2.xlsx โดยเลือกFrom File -> Excel คราวนี้มันจะบอกว่าเจอข้อมูล 2 ชีท

    เราสามารถเลือกแบบ Multiple พร้อมกันได้เลย

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 109

    ในที่นี้ผม Load to แบบ Only Create Connection เหมือนเดิม จได้ว่ามีการเชื่อมต่อ 3 อันแล้ว

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 110

    สุดท้ายเอาข้อมูลจากไฟล์ตัวเอง สามารถกดปุ่ม From Table/Range ได้ แต่ผมแนะนำว่าถ้ายังไม่ได้เปลี่ยนข้อมูลให้เป็น Table ให้เราทำการแปลงเป็น Table ซะก่อน โดยการคลิ๊กที่ข้อมูลแล้วกด Ctrl+T

    เดิม

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 111

    กด Ctrl+T เพื่อแปลงเป็น Table แล้วตั้งชื่อตามใจชอบ

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 112

    จากนั้น ขณะที่เลือก Table อยู่ ให้กดปุ่มที่ Tab Power Query ว่าเราจะเชื่อมต่อข้อมูลกับ Table

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 113

    มันจะขึ้นหน้าต่าง Query Editor มาให้โดยอัตโนมัติ (หน้าต่างเดียวกันกับที่เวลาเรากด Edit นั่นแหละ) ในที่นี้ผมจะ Close & Load to เพื่อ Only Create Connection เหมือนเดิม

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 114

    คราวนี้เราก็สร้าง Connection ครบทุกอันแล้ว!!

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 115

    2.คัดกรอง & ดัดแปลง

    ถ้าข้อมูลแต่ละอันไม่ค่อยสมบูรณ์ เช่น มีข้อมูลซ้ำ ข้อมูลขยะ เราสามารถทำการ Edit Query เพื่อคัดกรอง / group ข้อมูลก่อนจะผสมข้อมูลเข้าด้วยกันได้ (ถ้าเราไม่กำจัดการซ้ำก่อน เวลา Map ข้อมูลแล้วมันอาจจทำให้ข้อมูลเบิ้ลเข้าไปอีก)

    วิธีการทำคือ คลิ๊กขวาที่ Query ที่ต้องการแล้วกด Edit ได้เลย

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 116

    แต่ในที่นี้ข้อมูลของผม ok แล้ว เลยไม่ต้องมีการแก้ไขอะไรครับ เราข้ามขั้นตอนนี้ไปได้เลยครับ ^^

    3. ผสมข้อมูล (Combine โดย Merge/Append)

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

    • Merge = เอาข้อมูลมา Map กัน แบบเดียวกับ VLOOKUP (เพิ่มคอลัมน์)
    • Append = เอาข้อมูลหลายตารางมาต่อท้ายกันไปเรื่อยๆ (เพิ่มจำนวนบรรทัด)

    ในที่นี้ผมจะ Map ข้อมูลจากตารางอ้างอิง เลยต้องใช้การ Merge นั่นเอง ซึ่งพอกดเข้าไปแล้ว มันจะถามว่าจะเอาอะไรมา Merge กับอะไร?

    • (1) เอาเอาตารางหลัก (data1) ไว้ข้างบน <= คล้ายๆ Lookup_Value ใน VLOOKUP
    • (2) ตารางอ้างอิง (Customer Country) ไว้อันล่าง  <= คล้ายๆ Table_Array ใน VLOOKUP
    • (3) และ (4) เลือกคอลัมน์ที่เป็นตัวเชื่อมกัน
    • (5) เลือกวิธีการเชื่อม <= แบบแรก คือ จะเหมือน VLOOKUP นั่นคือ เอาอันบนทุกอัน และเอาอันล่างเท่าที่ Map เจอ
    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 117

    หลังจากกด Load จะขึ้นหน้าจอ Query Editor มาให้อีกแล้ว แต่คราวนี้จะเห็นว่ามีคอลัมน์ใหม่ เขียนว่า NewColumn แล้วมีค่าเป็น มาให้ด้วย แปลกดีมั้ย?? ความหมายมันคือเป็นคอลัมน์ที่มาจากการ Map ซึ่งเราต้องทำการกดตาม Step ข้างล่าง เพื่อเลือกว่าจะเอาคอลัมน์ไหนมาแสดงบ้าง?

    • (1) แตกคอลัมน์
    • (2) เลือกคอลัมน์ที่ต้องการแสดง
    • (3) ถ้าจะให้เอาชื่อคอลัมน์แบบไม่มี prefix ให้ติ๊กออก
    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 118

    พอกด Ok ก็จะเห็นว่ามีคอลัมน์เพิ่มมาแล้ว และจะเห็นว่ามี Applied Step ด้านขวา เพิ่มมาด้วย

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 119

    จากนั้นให้เราทำการ Merge กับตารางที่เหลือให้ครบ โดยไปที่คำสั่ง Merge Query

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 120

    แล้วเชื่อมข้อมูลให้ครบ

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 121
    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 122

    พอเสร็จแล้วสามารถกดปุ่ม Close & Load ได้เลย คราวนี้เราอยากจะสร้าง Table ผลลัพธ์สุดท้ายแล้ว

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 123

    4. แก้ไขรายละเอียด / เขียนสูตร / แก้ลำดับการทำงาน

    สมมติว่า คุณลืมอะไรบางอย่างไป หรือทำอะไรผิด ก็สามารถเข้าไปแก้ Query เดิมได้ โดยคลิ๊กขวาที่ Query ที่ต้องการแล้วกด Edit ได้เลยครับ

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 124

    เวลากดเข้าไปแล้ว จะสังเกตเห็นข้อมูล และ Applied Step ซึ่งคือขั้นตอนที่มันบันทึกไว้ว่ามันทำอะไรกับข้อมูลบ้าง ซึ่งเราสามารถแก้ไข ลบ สลับลำดับ action เหล่านี้ได้

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 125

    เวลาเราทำ action อะไรเพิ่มเติมมันก็จะบันทึกต่อไปเรื่อยๆ (จริงๆ เราสามารถแทรก Step การทำงานก็ได้ โดยไปเลือก Step ที่ต้องการจะแทรก แล้วค่อยทำ action)

    Tips : ตัวอย่าง 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 ได้ด้วยนะ แต่ยาก เอาไว้ทีหลังแล้วกันครับ ^^”

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 126

    มันก็จะมี Step เพิ่มขึ้นมา

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 127

    ลบคอลัมน์

    ผมจะตัดคอลัมน์บางอันทิ้ง ก็เลือกคอลัมน์ที่จะไม่เอา เช่น ราคาต่อชิ้น (ผมไม่ใช้แล้ว) แล้วกดปุ่ม Del บน Keyboard ได้เลย

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 128

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

    เปลี่ยนชื่อคอลัมน์

    ให้ดับเบิ้ลคลิ๊กที่ชื่อคอลัมน์ตรงหัวตาราง แล้วแก้ไขได้เลย

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 129

    เปลี่ยนประเภทข้อมูล

    คลิ๊กขวาที่คอลัมน์ที่ต้องการ แล้วเลือก Change Type ได้เลย

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 130

    ถ้าทำแล้ว Error แสดงว่ามันมองปฏิทินผิดแบบ

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 131

    ให้ไปที่ Change Type -> Using Locale… แทน แล้วเลือกเป็น Date / English (UK) แล้วกด Enter

    จะเห็นว่ามันยังคง Error อยู่ เป้นเพราะว่ามัน Error มาตั้งแต่ Step ก่อนหน้า

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 132

    ดังนั้นเราต้องลบ Step ก่อนหน้าทิ้งซะ โดย Click ขวาที่ Step ก่อนหน้า แล้วกด Delete

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 133

    จะเห็นว่าข้อมูลไม่ Error แล้ว และสามารถ Filter แบบ Date ได้

    Filter ข้อมูล

    จะ Filter ข้อมูลได้ดี จะต้องเปลี่ยนประเภทข้อมูลให้ถูกต้องซะก่อน เช่น ถ้าไม่ใช่วันที่ ก็จะไม่มี Date Filter ให้เลือก เป็นต้น

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 134

    Split Column

    เราสามารถ Split คอลัมน์ได้ 2 แบบ เช่นเดียวกับเครื่องมือ Text to Column นั่นคือ แบ่งแบบใช้ตัวคั่น (delimiter) กับแบบมีจำนวนอักขระชัดเจน ( number of character)

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 135

    แล้วมันจะมี Option ต่ออีก ว่าจะแยกแค่ตัวแรก ตัวหลัง หรือทุกตัว ผมจะแยกคำนำหน้าชื่อ ก็เลยเอาแค่ตัวแรก

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 136

    ถ้าทำทุกอย่างเสร็จหมดแล้ว เราก็กด Close & Load ได้เลยครับ

    ใครอยากดูเทคนิคต่างๆ ที่ใช้บ่อยอ่านต่อได้ที่นี่

    ปราณคิวรี่ พื้นฐาน 10 รูปแบบ : รวมกระบวนท่า Power Query พื้นฐานที่คุณควรรู้จัก

    5. นำไปใช้งานต่อใน Excel

    ผลลัพธ์ที่ออกมาจากการ Load จะเป็น Table ที่เอาไปใช้งานต่อได้ เช่น เอาไปวิเคราะห์ต่อใน PivotTable

    Power Query ตอนที่ 2 : มหากาพย์การใช้งาน Power Query ตั้งแต่ต้นจนจบ 137

    แต่มันมันเจ๋งคือ Table นี้ยังคง Link Connection กับข้อมูลต้นทางอยู่ แปลว่าหากข้อมูลต้นทางเปลี่ยนไป เราแค่ Refresh เจ้า Query นี้ และ Refresh PivotTable อีกที รายงานของเราก็จะเสร็จเลยทันที!!

    ใครมีคำถาม…

    อ่านจบแล้ว ใครสงสัยอะไรตรงไหน หรือว่าการทำงานจริงอยากรู้อะไรเป็นพิเศษ สามารถ Comment ถามไว้ในนี้ หรือจะ inbox ถามใน Facebook ก็ได้ครับ ตรงไหนมีคนสงสัยเยอะๆ ผมจะได้อธิบายละเอียดให้อีกทีครับ ^^

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

  • Power Query ตอนที่ 1: ปลุกพลัง Power Query ให้ตื่นขึ้น!!

    จากที่ผมได้มีการ Post แนะนำ Power Query ไปในเพจ ปรากฏว่ามีคนให้ความสนใจเป็นจำนวนมาก ดังนั้นผมจึงคิดว่าจะเขียนบทความแนะนำ Power Query เบืองต้นให้ทุกคนได้อ่านกันครับ ซึ่งเครื่องมือนี้ใช้ได้กับ

    • Microsoft Office 2010 Professional Plus ที่มี Software Assurance
    • Microsoft Office 2013
    • Microsoft Office 2016 ขึ้นไป รวมถึง Office 365

    สำหรับบทความนี้ เราจะมาเริ่มกันที่การปลุก Power Query ให้ตื่นขึ้นมาก่อน เพื่อที่ทุกคนจะได้ใช้งานมันได้ในตอนต่อๆ ไปครับ

    คุณมี Power Query แล้วหรือยัง?

    • วิธีดู version Excel ในเครื่องคุณ
    • สำหรับคนที่ใช้ Excel Version 2016 ขึ้นไป (หรือใช้ Office 365) จะมี Power Query แฝงมาให้อยู่แล้ว ใน Ribbon ส่วนของ Data
    • ดังนั้นไม่ต้องอ่านบทความนี้ต่อแล้วครับ
      Power Query ตอนที่ 1: ปลุกพลัง Power Query ให้ตื่นขึ้น!! 138
    • ส่วนคนที่ใช้ Excel Version ต่ำกว่า 2016 หากดูที่ Ribbon แล้วมีคำว่า Power Query อยู่ ก็แสดงว่าใช้งานได้แล้ว ไม่ต้องอ่านบทความนี้ต่อแล้วครับ
      Power Query ตอนที่ 1: ปลุกพลัง Power Query ให้ตื่นขึ้น!! 139
    • ส่วนคนที่ยังหา Ribbon Power Query ไม่เจอ ให้ลอง Enable Add-in ตามนี้ก่อน https://support.office.com/en-us/article/troubleshooting-power-query-2546eaa8-9893-4574-9ef9-afacb9b0b495
    • ถ้าใน Add-in ไม่มีแสดงว่ายังไม่ได้ทำการ Install Power Query เลยครับ นี่แหละคือสาเหตุที่หลายคน Comment ว่าไม่เคยได้ยินเครื่องมือนี้มาก่อนเลย ก็เพราะมันไม่ได้ Install มาด้วยแต่แรกนั่นเอง 555  ดังนั้นให้ทำตามบทความข้างล่างนี้ เพื่อ Install Power Query ครับ

    วิธี Install Power Query

    ก่อนอื่นให้ปิด Excel ทั้งหมดไปก่อน

    จากนั้นให้ไปที่ https://www.microsoft.com/en-us/download/details.aspx?id=39379 หรือ  Search Google ด้วยคำว่า “download power query” แล้วคลิ๊ก Link แรก

    จากนั้นคลิ๊กคำว่า Download ได้เลย

    Power Query ตอนที่ 1: ปลุกพลัง Power Query ให้ตื่นขึ้น!! 140

    เลือก Download ว่าจะเอา 32-bit หรือ 64-bit ตามที่ดูใน Step ข้างบน แล้วกด Next โลด
    (ถ้าไม่แน่ใจก็โหลดมา 2 ไฟล์เลย )

    Power Query ตอนที่ 1: ปลุกพลัง Power Query ให้ตื่นขึ้น!! 141

    พอโหลดมาแล้วก็ Double Click เลย แล้วกด Run

    Power Query ตอนที่ 1: ปลุกพลัง Power Query ให้ตื่นขึ้น!! 142

    พอเจอหน้าจอข้างบนก็กด Next , Accept, Next รัวๆ เลย (ถ้าไม่ขึ้นแบบข้างบน แสดงว่าโหลดมาผิด Version)

    พอ Install เสร็จ ก็ลองเปิด Excel ขึ้นมาใหม่ คราวนี้ก็จะมี Ribbon Power Query โผล่มาแล้วครับ!!

    Power Query ตอนที่ 1: ปลุกพลัง Power Query ให้ตื่นขึ้น!! 143

    ถ้ามันไม่ขึ้นมา???

    ให้ลองไปที่ File -> Options -> Add-ins –> Com Add in ->Go -> ติ๊ก Microsoft Power Query ครับ

    Power Query ตอนที่ 1: ปลุกพลัง Power Query ให้ตื่นขึ้น!! 144
    Power Query ตอนที่ 1: ปลุกพลัง Power Query ให้ตื่นขึ้น!! 145

    ใครลองทำตามนี้แล้วยังไม่ได้ ก็ Post Comment ไว้ได้ครับ เดี๋ยวมาดูให้

    เดี๋ยวตอนต่อไปเราจะมาลองใช้งาน Power Query กันแล้วครับ

  • ลากข้อมูลแล้วให้ Excel ใส่ A-Z โดยอัตโนมัติ ด้วย Custom List

    ลากข้อมูลแล้วให้ Excel ใส่ A-Z โดยอัตโนมัติ ด้วย Custom List

    แต่ก่อนตอนผมใช้ Excel ใหม่ๆ มีปัญหาอย่างหนึ่งที่ผมหงุดหงิดมาก นั่นคือ ผมต้องการลาก Fill Handle แล้วให้ Excel สร้างตัวอักษร A-Z หรือ ก-ฮ โดยอัตโนมัติ แต่สิ่งที่ Excel ทำให้มันกลับแค่เป็นการ Copy ตัว A หรือ ก ที่ผมพิมพ์ลงมาให้ซะงั้น!

    ผมงงมากเลย เพราะขนาด series ที่เป็นตัวเลขดูเหมือนว่าจะยากๆ เช่น 1,4,7,10,… มันยังฉลาดใส่มาให้ได้ แต่ทำไมแค่ A-Z หรือ ก-ฮ ดันไม่ใส่มาให้ฟะ!? ตอนนั้นก็ต้องใช้วิธีพิมพ์เอาเองบ้าง ใช้ฟังก์ชัน CHAR มาช่วยบ้าง ซึ่งก็ไม่ใช่วิธีที่ดีเท่าไหร่

    จนผมได้พยายามค้นใน Internet ก็รู้ว่าจริงๆ แล้ว Excel ทำแบบที่ผมต้องการได้ ด้วยการสร้างสิ่งที่เรียกว่า Custom List นั่นเอง

    Custom List

    Custom List เป็นเครื่องมือที่ช่วยให่เราสามารถสร้าง List รายการแบบที่เรากำหนดได้เอง ซึ่งจะทำให้ Excel สามารถ Auto Fill ข้อมูล หรือ Sort ข้อมูลตาม List ที่เรากำหนดได้

    วิธีการสร้าง Custom List

    1. สร้าง List ข้อมูลเตรียมไว้ก่อน
      • ผมสร้าง List A-Z รวมถึง ก-ฮ ไว้ก่อน ด้วยการลากตัวเลขลงมา 255 ตัว แล้วใช้ฟังก์ชัน CHAR ครอบลงไป
      • ซึ่งฟังก์ชัน CHAR สามารถแปลง Code ตัวเลข ให้กลายเป็นตัวหนังสือได้
        เช่น =CHAR(65) จะได้ออกมาเป็นตัว A ครับ
        ลากข้อมูลแล้วให้ Excel ใส่ A-Z โดยอัตโนมัติ ด้วย Custom List 146
      • จากนั้นทำการ Copy แล้ว Paste Value เพื่อให้สูตรกลายเป็น Value (ถ้าค้างเป็นสูตรไว้ จะเอาไปใส่ Custom List ไม่ได้)
        ลากข้อมูลแล้วให้ Excel ใส่ A-Z โดยอัตโนมัติ ด้วย Custom List 147
    2. เอาไปใส่ใน Custom List
      • ไปที่ Excel Options -> แถบ Advanced -> เลื่อนลงล่างๆ -> Edit Custom Lists…
        ลากข้อมูลแล้วให้ Excel ใส่ A-Z โดยอัตโนมัติ ด้วย Custom List 148
      • ไปที่ช่องข้างๆ ปุ่ม Import แล้วลากครอบพื้นที่ ที่เตรียมตัว A-Z ไว้
        ลากข้อมูลแล้วให้ Excel ใส่ A-Z โดยอัตโนมัติ ด้วย Custom List 149
      • กดปุ่ม Import ก็เสร็จการใส่ List A-Z แล้ว
        ลากข้อมูลแล้วให้ Excel ใส่ A-Z โดยอัตโนมัติ ด้วย Custom List 150
    3. ทำแบบเดิมกับ ก-ฮ หรือ List อื่นๆ ที่คุณต้องการ
      ลากข้อมูลแล้วให้ Excel ใส่ A-Z โดยอัตโนมัติ ด้วย Custom List 151ลากข้อมูลแล้วให้ Excel ใส่ A-Z โดยอัตโนมัติ ด้วย Custom List 152

    การเรียกใช้ Custom List ที่สร้างไว้แล้ว

    ใช้ใน AutoFill

    วิธีใช้ก็ง่ายแสนง่าย แค่พิมพ์ตัวใดตัวหนึ่งที่อยู่ใน List แล้วลาก Fill Handle ยาวลงมาก็ใช้ได้แล้ว

    ลากข้อมูลแล้วให้ Excel ใส่ A-Z โดยอัตโนมัติ ด้วย Custom List 153

    ใช้ใน Sort

    เวลาจะเรียงข้อมูลด้วย Custom List จะมีวิธียุ่งยากเล็กน้อย คือ ให้เราเลือก Sort แบบ Custom Sort (หรือไปที่ Ribbon [Data] => Sort ก็ได้)

    ลากข้อมูลแล้วให้ Excel ใส่ A-Z โดยอัตโนมัติ ด้วย Custom List 154

    จากนั้นตรง Order ให้เลือก Custom List… แล้วเลือก List ที่ต้องการ จากนั้นกด OK

    Custom List

    จากนั้น Excel ก็จะเรียงข้อมูลตาม List ที่เราต้องการครับ

    ลากข้อมูลแล้วให้ Excel ใส่ A-Z โดยอัตโนมัติ ด้วย Custom List 155

    ก่อนจากลา…

    ก็จบแล้วนะครับกับบทความเรื่อง Custom List นี้ หวังว่าจะเป็นประโยชน์กับเพื่อนๆ นะครับ เพราะถ้าใครรู้เทคนิคนี้ ก็สามารถเลิกพิมพ์ List อะไรซ้ำๆ ไปอีกตลอดชีวิต อย่าง A-Z เนี่ย ผมใช้บ่อยมากๆ เลย ลดเวลาไปได้มากครับ

    แต่มันจะเจ๋งกว่านี้อีก ถ้าเรามี List อื่นๆ เตรียมไว้ด้วย ดังนั้นถ้าใครมี List เจ๋งๆ ก็แชร์กันมาได้นะครับ เผื่อจะเป็นประโยชน์กับเพื่อนๆ คนอื่นด้วย ^^

  • The Mask Function ตอนที่ 3 : แสดงความสามารถครั้งสุดท้าย

    The Mask Function ตอนที่ 3 : แสดงความสามารถครั้งสุดท้าย

    ตอนนี้เป็นตอนต่อจาก The Mask Function ตอนที่ 2 :  ถอดหน้ากาก ! มาดูกันว่ามันคือฟังก์ชันอะไร !?

    ซึ่งฟังก์ชันทีผ่านเข้ารอบคือ ตัวแทนสาย C และ D ซึ่งได้แสดงผลงานในรอบแรกในการแข่ง Lookup ข้อมูลแบบ Multiple Criteria ไปแล้ว ทั้งคู่ต่างหาผลลัพธ์ได้ถูกต้องคือ ราคา 60 บาทถูกต้องเหมือนกันหมด แถมบอกเพิ่มด้วยว่า ถึงค่าที่จะ Lookup จะไม่ใช่ตัวเลข ตัวเองก็ไม่กลัวเลย สามารถทำงานได้ สบายหายห่วง!

    The Mask Function ตอนที่ 3 : แสดงความสามารถครั้งสุดท้าย 156

    การแสดงความสามารถรอบ Final

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

    ตัวแทนสาย D ไม่รอช้า รีบแสดงความสามารถเลย เพราะถือว่า “เข้าทางมากๆ”

    The Mask Function ตอนที่ 3 : แสดงความสามารถครั้งสุดท้าย 157

    ต่างกันกับตัวแทนสาย C ซึ่งเจอโจทย์นี้ไปก็เกิดอาการอึดอัดไปซักพักหนึ่ง แต่ในที่สุดก็แสดงความสามารถออกมาจนได้…

    The Mask Function ตอนที่ 3 : แสดงความสามารถครั้งสุดท้าย 158

    Comment จากกรรมการ

    ทั้งคู่ก็หาคำตอบมาได้ในที่สุด… แต่ด้วยความยากลำบากที่ต่างกัน

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

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

    เอ้า! แล้วตอนนี้คุณรู้หรือยังครับว่าตัวแทนสาย C คือใคร และตัวแทนสาย D คือใคร?

    ศุกร์หน้า จะมาถอดหน้ากากเฉลยนะครับ!!

  • การเขียนข้อความในสูตร และการใช้เครื่องหมาย & เชื่อม

    การเขียนข้อความในสูตร และการใช้เครื่องหมาย & เชื่อม

    เรื่องพื้นฐานบางเรื่อง เป็นเรื่องง่ายๆ แต่หลายคนก็มองข้ามไป หรือทำไม่เป็นอย่างน่าเสียดาย …

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

    รู้หรือไม่ว่า…

    • & คือตัวเชื่อมข้อความ เชื่อมข้อความ จากหลายช่องมารวมกันในช่องเดียวกันได้
      • เช่น ในรูป สามารถรวม ชื่อ และนามสกุล เดิมอยู่แยกกันให้รวมกันเป็น Cell เดียวได้
    • & ทำให้ผลลัพธ์เป็นข้อมูลประเภท Text
      • สามารถแปลง Number เป็น Text ได้ด้วย เช่น =100&”” จะทำให้ 100 กลายเป็น Text
    • ข้อความใน Cell เราไม่ต้องใส่เครื่องหมายคำพูด
      • พิมพ์ไปใน Cell ตรงๆ แล้วอ้างอิงมาใช้ในสูตรด้วย Cell Reference เช่น A1 B2 ได้เลย
    • ข้อความในสูตร ต้องพิมพ์อยู่ในเครื่องหมายคำพูด (” “) เพื่อบ่งบอกว่าเรากำลังจะพิมพ์ข้อมูลที่เป็น Text
    • แต่ถ้าจะใส่เครื่องหมายคำพูดจริงๆ ให้ใส่ “” ซ้อนลงไปข้างใน เครื่องหมายคำพูด (ที่เป็นตัวบ่งบอกความเป็น Text) อีกที
      • เช่น =““”นี่คือข้อความภายในเครื่องหมายคำพูด“” อันนี้นอกนะ…”
      • จะออกมาเป็น “นี่คือข้อความภายในเครื่องหมายคำพูด” อันนี้นอกนะ…

    ใครมีประเด็นที่น่าสนใจเพิ่มเติม ก็ Comment ได้เลยครับ

  • The Mask Function ตอนที่ 2 : ถอดหน้ากาก ! มาดูกันว่ามันคือฟังก์ชันอะไร !?

    The Mask Function ตอนที่ 2 : ถอดหน้ากาก ! มาดูกันว่ามันคือฟังก์ชันอะไร !?

    ตอนนี้เป็นตอนต่อจาก The Mask Function : หน้ากากฟังก์ชัน ! มาทายกันนี่คือฟังก์ชันอะไร?? ซึ่งฟังก์ชันทั้ง 4 ตัวได้โชว์ฝีมือในการแข่งขัน แข่ง Lookup ข้อมูลแบบ Multiple Criteria ไปแล้ว ทุกตัวต่างหาผลลัพธ์ได้ถูกต้องคือ ราคา 60 บาทถูกต้องเหมือนกันหมด!

    The Mask Function ตอนที่ 2 : ถอดหน้ากาก ! มาดูกันว่ามันคือฟังก์ชันอะไร !? 159

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

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

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

    ใบ้เพิ่มเติม

    ทั้งตัวแทนสาย C และ D ที่เข้ารอบไปแล้วฝากบอกมาว่า ถึงค่าที่จะ Lookup จะไม่ใช่ตัวเลข ตัวเองก็ไม่กลัวเลย สามารถทำงานได้ สบายหายห่วง!
    ต่างจากตัวแทนกลุ่ม A และ B ที่หากจ้อง Lookup ค่าที่เป็นตัวหนังสือกลับมาคงจะลำบากสุดๆ ครับ

    ดังนั้นวันนี้ผมจะทำการถอดหน้ากาก ตัวแทนสาย A และ B ซะก่อนครับ !!

    ถอดหน้ากากตัวแทนสาย A

    ปรากฏว่าตัวแทนสาย A คือฟังก์ชัน SUMIFS ครับ!!

    The Mask Function ตอนที่ 2 : ถอดหน้ากาก ! มาดูกันว่ามันคือฟังก์ชันอะไร !? 160

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

    ใช่ครับ SUMIFS มีหน้าที่หลักคือเอาไว้หาผลรวมตามเงื่อนไขที่กำหนด … แต่ถ้าค่าที่จะ Lookup เป็นตัวเลข การใช้ SUMIFS ก็เป็นอีกวิธีที่เข้าท่ามากๆ ในการทำ Lookup แบบ Multiple Criteria

    =SUMIFS(sum_range,criteria_range,criteria,…)

    • sum_range คือ range ที่เราจะหาผลรวม ในที่นี้ก็คือ range ที่จะ lookup ค่ามา ซึ่งก็คือ C2:C7 นั่นเอง
    • criteria_range1 คือ range ที่จะมีข้อมูลเงื่อนไขตัวแรกอยู่ ซึงคือ product A2:A7
    • criteria1 คือ เงื่อนไขตัวแรก ในที่นี้คือ product ข (A10)
    • criteria_range2 คือ range ที่จะมีข้อมูลเงื่อนไขตัวแรกอยู่ ซึงคือ Type B2:B7
    • criteria2 คือ เงื่อนไขตัวแรก ในที่นี้คือ Type 2 (B10)

    ข้อควรระวัง

    ถ้ามีค่า Criteria ซ้ำๆ หลายบรรทัด เช่น มี ข 2 หลายอัน การใช้ AVERAGEIFS ก็อาจจะเป็นทางเลือกที่ดีกว่า เพราะจะได้ค่าเฉลี่ยกลับไป แทนที่จะได้ผลรวมหากใช้ SUMIFS

    SUMIFS : กรณีมี Criteria ซ้ำกันหลายบรรทัด จะได้ (60+60)

    The Mask Function ตอนที่ 2 : ถอดหน้ากาก ! มาดูกันว่ามันคือฟังก์ชันอะไร !? 161

    AVERAGEIFS : กรณีมี Criteria ซ้ำกันหลายบรรทัด จะได้ (60+60) /2

    The Mask Function ตอนที่ 2 : ถอดหน้ากาก ! มาดูกันว่ามันคือฟังก์ชันอะไร !? 162

    ถอดหน้ากากตัวแทนสาย B

    ปรากฏว่าตัวแทนสาย A คือฟังก์ชัน SUMPRODUCT ครับ!!

    The Mask Function ตอนที่ 2 : ถอดหน้ากาก ! มาดูกันว่ามันคือฟังก์ชันอะไร !? 163

    มีคนเดาถูกเช่นกัน และหลายคนก็คงจะงงอีกเช่นกัน เพราะปกติ SUMPRODUCT จะเอาไว้คำนวณการจับคู่คูณกันแล้วค่อยหาผลรวมของทุกคู่

    เช่นรูปนี้ มันจะเอา (100*60%) + (200* 50%) + (300*40%)

    The Mask Function ตอนที่ 2 : ถอดหน้ากาก ! มาดูกันว่ามันคือฟังก์ชันอะไร !? 164 The Mask Function ตอนที่ 2 : ถอดหน้ากาก ! มาดูกันว่ามันคือฟังก์ชันอะไร !? 165

    ซึ่งถ้าสังเกตจะเห็นว่า 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

    โชว์เหนือ Lookup 2 แกน

    สูตร SUMPRODUCT ฝากบอกมาว่า ถึงตารางจะพลิกไปเป็นอีกแบบ มันก็ยังทำงานได้นะ (เหมือน Lookup 2 แกน)

    The Mask Function ตอนที่ 2 : ถอดหน้ากาก ! มาดูกันว่ามันคือฟังก์ชันอะไร !? 166

    The Mask Function ตอนที่ 2 : ถอดหน้ากาก ! มาดูกันว่ามันคือฟังก์ชันอะไร !? 167

    ซึ่งสูตรนี้ทำงานได้ เป็นการคูณ Array แบบมิติ 2 ที่มี Range ไม่เท่ากัน นั่นคือ

    • A3:A5 มีมิติ 3R x 1C
    • B2:C2 มีมิติ 1R x 2C

    คูณกัน ได้ผลลัพธ์เป็น 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

    ตอนต่อไป

    วันศุกร์หน้า พบกับความสามารถของฟังก์ชั่นทั้งสองที่เข้ารอบไป ว่าจะแสดงอะไรเจ๋งๆ ให้พวกเราได้ดูกันครับ

     

     

  • เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง?

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง?

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

    วันนี้ผมก็เลยจะมาช่วยเพื่อนๆ มาสำรวจ Ribbon ไปด้วยกันครับ โดยเฉพาะคำสั่งตามซอกหลืบต่างๆ ที่คุณอาจไม่ทันสังเกตเห็นมาก่อน ^^
    ผมจะลองไล่ Tab Home ให้ดูนะครับ ส่วน Tab อื่นไปเล่นกันเองน้าาา

    ภาพรวม Tab Home

    Tab Home เป็น Tab ที่รวมเรื่องที่เราใช้งานบ่อยที่สุดเอาไว้ ถ้านึกอะไรไม่ออก ก็น่าจะต้องสั่งงานใน Tab นี้แหละ

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 168

    ในส่วนของ Paste เองก็มีลูกเล่นหลายอย่างมากๆ และถ้ากด Paste Special ก็จะยิ่งมี Option เพิ่มขึ้นอีก เช่น ทำ Operation บวก ลบ คูณ หารได้ด้วย

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 169  เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 170

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

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 171 เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 172

    ซึ่งโหมด Copy as Picture แบบ As Shown when printed นี้จะได้ภาพที่คมชัดกว่า Copy เฉยๆ แล้ว Paste As Picture นะครับ แถมสามารถช่วยตัดพวกสัญลักษณ์ Error แปลกๆ (ธงเขียว) และสัญลักษณ์การ Comment (ธงแดง) ออกไปได้ด้วย !! (คุณบิว วิศวกรรีพอร์ต บอกมา และผมก็ลองแล้วเป็นอย่างนั้นจริงๆ ด้วย!!)

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 173

    ส่วนของการปรับแต่ง Font ก็มีขีดเส้นใต้คู่ด้วยนะ 555

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 174

    การตีกรอบก็มีได้หลายวิธีมากๆ  ยิ่งถ้ากดไปใน More Border เราสามารถทำเส้นกรอบแบบแทยงมุมได้ด้วยนา

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 175 เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 176

    การจัดวางตัวอักษรก็ปรับได้หลายแบบ ถ้าเลือก Format Cell Alignment ก็จะปรับได้เยอะขึ้นไปอีก (กด ctrl+1 ก็ได้)

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 177

    เคยกดพวก Shrink to Fit บ้างมั้ย? มันจะลดขนาด Font ให้พอดีช่องเองนะ

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 178

    ส่วนการ Merge Cell ก็มีโหมด Merge Across ให้เลือกด้วย ซึ่งมันจะ Merge แบบแยกบรรทัดกันได้ด้วยนะ

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 179

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 180

    การปรับ Number Format ก็มี Preset พื้นฐานให้เลือกพอสมควร ถ้ายังไม่ได้ดั่งใจก็ต้องไป More Number Format… แล้วไป Custom เอา

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 181 เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 182

    ส่วนการใช้ Conditional Format (ปรับ Format ตามเงื่อนไข) ก็มี Preset ให้เลือกเยอะเลย แต่ถ้ายังไม่พอใจ ก็ต้องไป New Rule… แล้ว Use Formula เอา เพื่อความ Flexible ขั้นสุดยอด

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 183เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 184

    กลุ่ม Insert / Delete คิดว่าน่าจะเคยลองกดเล่นกันบ้าง

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 185 เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 186

    กลุ่ม Format นี่มีอะไนซ่อนอยู่เยอะเหมือนกันนะ

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 187

    รู้หรือไม่ว่า AutoSum มันมี Function อื่นๆ ให้เลือกด้วยนะ

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 188

    รู้มั้ยว่ามันมีคำสั่ง Fill ด้วยน่ะ แถมมีหลายทิศอีก และถ้าเลือก Series… ก็มี Option ให้เลือกอีกตูมเลย!

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 189  เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 190

    ทำ Growth ประหลาดๆ ก็ได้

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 191

    ปุ่ม Clear ก็มีหลายแบบ แถมยัง Clear เฉพาะ Hyperlink ออกได้ด้วยนะ

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 192

    Sort & Filter น่าจะใช้กันบ่อย

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 193

    Find & Select นี่มีประโยชน์มากนะ ถ้าใช้เป็นทุ่นแรงได้เยอะมากๆ

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 194

    Find… แล้วเลือก Option >>> ใน Look in นี่สำคัญมาก และพวก Match entire cell contents ก็ทำให้ร่วมกับพวกเครื่องหมาย wildcard ได้อีก

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 195

    Go to Special นี่เจ๋งมาก การเลือกช่องที่ Blank ทำได้ง่ายๆ โดยเครื่องมือนี้

    เคยสำรวจ Ribbon บน Excel จนครบทุกซอกทุกมุมหรือยัง? 196

    นี่แค่ Tab Home อันเดียวนะครับ ใน Tab อื่นก็ยังมีเครื่องมือเจ๋งๆ ซ่อนอีกเยอะ และจริงๆ ยังมีเครื่องมือที่ไม่อยู่ใน Ribbon ปกติ แต่เราเรียกใช้งานผ่าน Quick Access Toolbar ได้อีกเพียบเลยด้วยนา

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

    ========================================

    Post นี้คือ ส่วนหนึ่งของโครงการ inwexcel365tips  ที่ผมจะ post บทความในเพจทุกวัน ต่อเนื่องกันเป็นเวลา 1 ปี ถ้าผมพลาดไม่ post ภายในเวลา 3 ทุ่ม คนที่มาทวง post คนแรก รับของแจกฟรีได้เลย !!

     

  • แนะนำโครงการ inwexcel365tips 

    ผมตั้งเกมสนุกๆ ให้แฟนเพจได้เล่นกันครับ ในชื่อ #inwexcel365tips นั่นคือ

    ตั้งแต่ 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 Function : หน้ากากฟังก์ชัน ! มาทายกันนี่คือฟังก์ชันอะไร??

    The Mask Function : หน้ากากฟังก์ชัน ! มาทายกันนี่คือฟังก์ชันอะไร??

    The Mask Singer สำหรับคนที่ไม่รู้จัก

    จากกระแสที่ร้อนแรงของรายการ The Mask Singer (หน้ากากนักร้อง) ที่จัดให้มีการร้องเพลงภายใต้หน้ากาก โดยให้ผู้ชมลุ้นว่าคนภายใต้หน้ากากคือใคร หากนักร้องคนไหนที่ตกรอบ ก็จะต้องมีการถอดหน้ากาก เพื่อเปิดเผยตัวตนที่แท้จริงของตัวเอง ส่วนผู้ชนะก็จะเข้ารอบต่อไป

    ซึ่งรายการนี้จะมีการแบ่งนักร้องเป็น 4 สายเพื่อหาตัวแทนแต่ละสายมาแข่งกันในรอบ Final ในฐานะสุดยอดหน้ากากนักร้องทั้ง 4 คน…

    ผมก็เลยคิดว่า ถ้าลองจับเอาฟังก์ชันใน Excel มาแสดงความสามารถเจ๋งๆ แล้วให้เพื่อนๆ ทายว่าจริงๆ แล้วมันคือฟังก์ชันอะไรกันแน่ก็น่าจะสนุกดีนะ

    แข่ง Lookup ข้อมูลแบบ Multiple Criteria !!

    ผมก็เลยมีฟังก์ชันมาให้ทาย 4 ตัวเช่นเดียวกัน ซึ่งผมให้ทุกตัวแก้ปัญหาเดียวกันเลย นั่นคือ การ Lookup ข้อมูลแบบ Multiple Criteria โดยต้องดูข้อมูลจากคอลัมน์ A และ B เพื่อหาราคาในคอลัมน์ C มาให้ได้ !! (Advance ชะ?)

    The Mask Function : หน้ากากฟังก์ชัน ! มาทายกันนี่คือฟังก์ชันอะไร?? 197

    ทุกตัวต่างหาผลลัพธ์ได้ถูกต้องคือ ราคา 60 บาท แต่ทว่าแต่ละตัววาดลวดลายหาคำตอบด้วยวิธีที่แตกต่างกันอย่างมาก

    เราลองมาดูความสามารถของฟังก์ชันแต่ละตัวกันครับ

    • =ตัวแทนสาย A (C2:C7,A2:A7,A10,B2:B7,B10)
    • =ตัวแทนสาย B ((C2:C7)*(B2:B7=B10)*(A2:A7=A10))
    • =ตัวแทนสาย C (A1:C7,C1,A9:B10)
    • {=ตัวแทนสาย D (A10&B10,CHOOSE({1,2},A2:A7&B2:B7,C2:C7),2,FALSE)}
      ** ตัวแทนสาย D ต้องกดปุ่ม Ctrl+Shift+Enter ถึงจะทำงานได้

    คุณล่ะทายได้หรือไม่ว่า ตัวแทนแต่ละสาย คือ ฟังก์ชันอะไร??

    ให้ลองทายเล่นๆ ได้ใน 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 คนแรก รับของแจกฟรีได้เลย !!

  • 10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม

    นอกจาก Excel จะเป็นเครื่องมือที่ใช้ในการคำนวณได้อย่างเก่งกาจแล้ว มันยังสามารถนำมาใช้เป็นเครื่องมือในการ Track งาน/วาระประชุมต่างๆ ได้อย่างยอดเยี่ยมด้วย วันนี้ผมเลยจะมาแชร์เทคนิคที่คิดว่าน่าจะเป็นประโยชน์กับเพื่อนๆ ครับ (ถ้าใครมีแนวทางอื่นเจ๋งๆ ก็บอกผมด้วยนะ ^^)

    1. ออกแบบการจดงานในลักษณะตาราง Database

    เวลาอยากจะใช้ Excel ได้อย่างเต็มประสิทธิภาพ การเก็บข้อมูลในลักษณะตาราง Database จะเป็นการเริ่มต้นที่ดีที่สุด เพราะมันสามารถช่วยให้เราสามารถนำข้อมูลไปใช้ในความสามารถอื่นๆ ของ Excel ได้อีก เช่น Sort/Filter, PivotTable เป็นต้น

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

    ดังนั้นสิ่งสำคัญคือ คุณจะต้องคิดว่าการจดงานของคุณควรจะมีคอลัมน์อะไรบ้าง? เช่น

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 198

    2. ปรับตาราง Track งานให้เป็น Table ก็เจ๋งนะ

    ไหนๆ เราก็จะบันทึกข้อมูลในลักษณะของ Database แล้ว การปรับตารางปกติให้กลายเป็น ตารางแบบ Table ไปเลยก็จะได้ประโยชน์หลายอย่าง เช่น

    • ไม่ต้อง Copy ข้อมูลจากบรรทัดบนไปบรรทัดล่างอีกต่อไป มันจะลงมาให้เองอัตโนมัติ ทั้งสูตร, Data Validation, Conditional Format ทั้งหลายที่เรากำลังจะอธิบายต่อๆไป
    • มีเครื่องมือ Filter ขึ้นมาให้โดยอัตโนมัติ

    ซึ่งวิธีทำก็ง่ายแสนง่าย ก็คือเลือกส่วนใดส่วนหนึ่งของตารางแล้วไปที่ Insert -> Table หรือกด Ctrl+t เป็นอันจบ

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 199

    Tips : รู้จักใช้เครื่องมือ Sort/Filter

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

    เราสามารถเปิด/ปิด Auto Filter ได้โดยการกดปุ่ม Auto Filter บน Ribbon หรือจะใช้ Keyboard Shortcut:  Ctrl+Shift+L ก็ได้

    3. Shortcut เจ๋งๆ ในการบันทึก / แก้ไขข้อมูล

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

    และวิธีการบันทึกข้อมูลที่เร็วที่สุดอันหนึ่งก็คือ การใช้ Keyboard Shortcut นั่นเอง (มันเร็วกว่าการใช้ Mouse คลิ๊กมากๆ) ดังนั้นผมจะขอแนะนำ Shortcut ที่คิดว่าจะมีประโยชน์ต่อการบันทึกงานดังนี้ครับ

    • ไปช่องถัดไป (ถ้าอยู่ช่องสุดท้าย จะขึ้นบรรทัดใหม่) = Tab
    • เข้าไปแก้ไขข้อมูล = F2
    • ทำซ้ำ action ล่าสุด = F4
    • สร้างเลข Running = ใส่เลข 1 แล้วกด Ctrl ค้างไว้ แล้วลาก Fill Handle ลง
    • ใส่วันที่ปัจจุบัน = Ctrl+;
    • ใส่เวลาปัจจุบัน = Ctrl+: (Ctrl+Shift+;)
    • ขีดฆ่าข้อมูล (strike through) = Ctrl+5
    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 200

    4. สร้าง Drop Down List ให้กรอกข้อมูลง่าย

    เราสามารถสร้าง Drop down List ให้คนกรอกข้อมูลได้เร็วและถูกต้องได้ง่ายๆ ซึ่ง Dropdown ที่จะนำมาใช้ได้
    เช่น คนรับผิดชอบงาน/กลุ่มของงาน/Status ของงาน เป็นต้น

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 201

    วิธีการทำก็ง่ายมาก ให้ไปที่ [Data] -> Data Validation -> List -> แล้วพิมพ์ตัวเลือกที่ต้องการ คั่นด้วย Comma หรือจะเลือก Range ของ Choice ที่ต้องการ หรือจะเลือกจากชื่อที่ตั้งเอาไว้แล้วก็ได้ (ในรูปผมตั้งชื่อ Range ของ Choice ที่จะให้เลือกว่า ลูกน้อง ตามวิธีด้านล่าง)

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 202

    Tips : การตั้งชื่ออย่างรวดเร็ว

    • ให้เขียนชื่อไว้ช่องข้างบน (เช่น คำว่า Status)  แล้วตามด้วย Choice ทั้งหมดที่จะอยู่ในชื่อนั้น (New,Doing,Stuck,Done)
    • ลากพื้นที่คลุม แล้วกด Ctrl+Shift+F3
    • ติ๊ก Top Row แล้ว OK
    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 203

    Tips : Dropdown List แบบกรอกข้อมูลเพิ่มได้

    แต่เทคนิคที่หลายคนอาจไม่รู้คือ เราสามารถทำ Drop down List แบบที่เลือกแล้วยังแก้ข้อมูลต่อได้ด้วย ซึ่งจะเป็นการเน้นเรื่องของความเร็วในการกรอกข้อมูลมากกว่าความถูกต้อง เทคนิคคือ ใน Tab Error Alert ให้เอาติ๊กถูกออกไปตรงส่วนของ Show error alert after invalid data is entered

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 204

    แถม: การทำ Dropdown 2 ชั้น

    บางทีถ้าเรามีทุกอย่างให้เลือกใน Dropdown เลย คนเลือกอาจเลือกไม่ไหว เพราะเยาะมาก เราจึงอาจต้องทำให้มี Dropdown 2 ชั้น เพื่อให้ Dropdown แรกเป็นตัว scope ตัวเลือกของ Dropdown อันที่ 2 ให้น้อยลง => เทคนิคการทำ Dropdown 2 ชั้น

    5. รู้จักใช้สูตรข้อมูลเพื่อลดการกรอกที่ไม่จำเป็น

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

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

    ใช้สูตรเพื่อ Lookup ข้อมูลมาอัตโนมัติ

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

    ตารางอ้างอิง ชื่อหัวหน้่า

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 205

    จากนั้น เราก็สามารถเขียนสูตรเพื่อดึงชื่อหัวหน้างานมาได้โอยอัตโนมัติได้แล้ว ง่ายมากๆ

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 206

    ** สัญลักษณ์ [@ชื่อหัวคอลัมน์] เป็นการอ้างอิงแบบ Structure Reference เมื่อมีการใช้ Table หมายถึงเอาข้อมูลในคอลัมน์นั้นจากแถวเดียวกัน**

     รู้จักเขียนสูตรเพื่อให้ข้อมูลขึ้นมาโดยอัตโนมัติ

    ทำได้หลายแบบมากๆ เช่น

    • ถ้าวันที่ปัจจุบัน TODAY() เลย Deadline แล้ว และยังทำงานไม่เสร็จ ให้มีสถานะขึ้นมาว่างาน Late แล้ว
    • ถ้าใช้พวก COUNTIFS นับจำนวนงานที่รับผิดชอบของคนคนหนึ่งๆ ถ้ามีเกินกว่าค่าที่กำหนด ให้ขึ้นว่า Overload

    6. ถ้ากรอกข้อมูลผิดก็ต้องเตือน

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 207

    เพื่อให้แน่ใจว่าจะมีการกรอกข้อมูลได้อย่างถูกต้อง เครื่องมือ Data Valuidation เราสามารถเลือกคำสั่ง Allow เป็น Number, Text, Date เพื่อกำหนดประเภทข้อมูลที่ต้องการให้กรอกได้อย่างง่ายดาย เช่น ในช่อง “Deadline” อาจจะใส่เงื่อนไขว่า ห้ามกรอกวันที่ในอดีต โดยใส่ว่า Date ต้อง greater than or equal to =TODAY() เป็นต้น

    และเมื่อกรอกข้อมูลผิด ก็ควรเตือนคนกรอกได้ว่ามันผิดยังไง คนกรอกจะได้แก้ให้ถูกต้อง เช่น

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 208

    เวลากรอกผิดเงื่อนไขก็จะขึ้นเตือนมาได้

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 209

    7. ใส่ Timestamp แบบไม่ง้อ VBA

    ปกติแล้วในช่องพวกวันที่/เวลา ถ้าเราใส่สูตร เช่น =TODAY() หรือ =NOW()
    ค่าของมันจะเปลี่ยนไปเรื่อยๆ เมื่อ Excel มีการคำนวณใหม่ ทำให้ไม่สามารถคงวันที่ที่เราต้องการไว้ได้

    ซึ่งเราสามารถแก้ปัญหานี้ด้วยการ Stamp ค่าจาก Keyboard Shortcut Ctrl+; และ Ctrl+: ก็ได้นะ เช่น

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 210
    • Stamp วันที่ กด Ctrl+; อย่างเดียว
    • Stamp เวลา กด Ctrl+: [หรือ Ctrl+Shift+;] อย่างเดียว
    • Stamp วันที่และเวลา กด Ctrl+; แล้วกด spacebar 1 ที แล้วกด Ctrl+: [หรือ Ctrl+Shift+;]

    ซึ่งจะเห็นว่าการ Stamp วันที่และเวลา กดยากและไม่สะดวก.. ซึ่งผมมีอีกวิธีมาแนะนำ นั่นคือ การ Stamp ค่าด้วยสูตร!!

    Stamp ค่าด้วยสูตร

    เราสามารถใช้เทคนิคการเขียนสูตรแบบ Circular Reference (วน Loop งูกินหาง) เพื่อให้ Excel Stamp วันที่+เวลา เมื่อเกิดเหตุการณ์ที่ต้องการได้  เช่น เมื่อมีคนเปลี่ยน Status เป็น Done เมื่อไหร่ ก็ให้ Stamp วันที่+เวลา ณ ตอนนั้นได้ทันที! เช่น 

    =IF([@สถานะงาน]="Done",IF([@วันจบงานจริง]="",NOW(),[@วันจบงานจริง]),"")
    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 211

    ** ส่วน [@วันจบงานจริง] ในสูตรข้างบน คือ ส่วนที่เป็น Circular Reference หรืออ้างอิงกันเองแบบงูกินหาง ซึ่งปกติ Excel จะด่าเรา เวลาเราเขียนสูตรแบบ Circular นี้

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 212

    แต่ถ้าเราไปตั้งค่าใน Excel Option -> Tab Formula -> ติ๊กให้ Enable Iterative Calculation มันก็จะทำงานได้!!

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 213

    หลังปรับ Option แล้ว จะเห็นว่า พอลองแก้ Status เป็นอย่างอื่น แล้วเปลี่ยนเป็น Done อีกที สูตรก็จะทำงานแล้ว แต่ Format อาจจะผิด!

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 214

    Tips : สร้างเลข Running อัตโนมัติ

    คุณสามารถใช้เทคนิค Circular นี้กับเลข Running Number ก็ได้ เช่น

    =IF([@ชื่องาน]="","",IF([@ลำดับเลขงาน]="",ROW([@ลำดับเลขงาน])-1,[@ลำดับเลขงาน]))

    ** หากใส่สูตรว่า =ROW([@ลำดับเลขงาน])-1 ธรรมดาก็ใช้งานได้ แต่ถ้า Sort ปุ๊ปทุกอย่างจะพังเลย

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 215

    8. ปรับ Format ตัวเลขให้โดนใจ

    ตัวเลขวันจบงานที่ขึ้นมา เราใช้สูตรว่า NOW() แปลว่ามันจะต้องเป็นวันที่และเวลา
    ซึ่งจริงๆ เลข 4 หมื่นแบบมีทศนิยมก็คือ วันที่+เวลานั้นแหละ แค่ยังไม่ได้ปรับ Format เท่านั้นเอง

    หน้าที่ของเราก็คือปรับ Number Format ให้ถูกต้อง โดยคลิ๊กขวา -> Format Cell หรือกด Ctrl+1

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 216

    นี่ไง ออกมาเป็นวันที่และเวลาแล้ววว

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 217

    Custom Number Format

    นอกจากนี้ยังมี Custom Number Format ที่น่าสนใจอีกเพียบ

    • #,##0.00,, = แสดงตัวเลขในหลักล้านด้วยทศนิยม 2 ตำแหน่ง
    • #,##0.00 “บาท” = ใส่คำว่าบาท หลังตัวเลข แต่ยังคงทำให้คำนวณต่อได้
    • 000-000-0000 = ใส่ – ระหว่างตัวเลขหลักต่างๆ

    9. รู้จักใช้ Conditional Formatting เพื่อใส่ Format แบบ Dynamic

    เวลาเราเลือก Status ของงาน เช่น To do/Doing/Done เราอาจต้องการให้สีของพื้นหลังของ Cell เปลี่ยนไปตาม Status ที่ปลี่ยนไปได้ ซึ่งเราทำแบบนี้ได้ด้วยเครื่องมือ Conditional Formatting

    • ถ้าจะเปลี่ยนสีตามข้อมูลที่อยู่ในช่องตัวเอง แบบนี้จะทำง่าย มีเมนูให้เลือกเลยตามใจชอบ
    • แต่ถ้าจะให้เปลี่ยนสีโดยขึ้นกับข้อมูลในช่องอื่น แบบนี้ต้องเลือกแบบ Use Formula… ซึ่งจะต้องมีความรู้การเขียนสูตรเชิง Logic ด้วย

    ปรับ Format ตามเงื่อนไขจากข้อมูลช่องตัวเอง

    เช่น ถ้า Status เป็น Done ผมจะให้สีของช่อง Status เป็นสีเขียว แบบนี้ทำง่ายมากโดยเลือกที่ช่อง Status แล้วเลือก
    Conditional Formatting -> Highlight Cells Rules -> Equal to –> ใส่คำว่า Done แล้วเลือก Format ที่ต้องการ (ถ้าไม่ตรงใจให้เลือก Custom)

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 218

    ปรับ Format ตามเงื่อนไขจากข้อมูลช่องอื่น

    สมมติอยากให้ลำดับเลขงานและชื่องานที่เสร็จแล้วเป็นสีเขียวด้วย แบบนี้ต้องไปเลือกช่อง ลำดับเลขงานและชื่องาน
    แล้วไปที่ Conditional Formatting -> New Rules -> Use Formula to determine…

    จากนั้นใส่สูตรเพื่อเช็คเงื่อนไข ถ้าสูตรเป็นจริงก็จะ Apply Format ที่ตั้งค่าไว้

    ในที่นี้ผมจึงต้องใส่สูตรว่า =$F2=”Done” โดยต้องใส่ $ที่คอลัมน์ ไม่ใส่ที่ Row เพราะต้องอ้างอิงกับคอลัมน์ F ตลอด แต่ให้เลื่อนบรรทัดลงไปเรื่อยๆ ได้

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 219

    จากนั้นกำหนด Format ตามใจชอบ แล้วกด Ok ก็จะเห็นว่าสีเปลี่ยนตามสถานะได้แล้ว

    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 220
    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 221

    10. เอาข้อมูลไปสรุปผลต่อใน PivotTable/PivotChart

    เนื่องจากเราเก็บข้อมูลอยู่ในลักษณะ Database อย่างดีแล้ว เวลาเราจะวิเคราะห์/สรุปผลข้อมูลด้วยก็ทำได้ว่านแสนง่าย

    โดยให้คลิ๊กที่ช่องไหนก็ได้ แล้วกด Insert PivotTable/PivotChart แล้วลากข้อมูลไปมา แปปเดี๋ยวก็เสร็จแล้ว

    Track งาน
    10 เทคนิคการใช้ Excel ในการ Track งาน/วาระประชุม 222

    Tips แถม :  หา Template เจ๋งๆ มาใช้ซะ

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

    ใครมี Template อะไรเจ๋งๆ ฟรีๆ ก็บอกมาได้นะครับ

    ตาคุณแล้ว

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

    ยังไงก็ขอบคุณมากครับ ที่อ่านมาจนจบบทความ ถ้าถูกใจก็ฝากแชร์ให้เพื่อนๆ ของคุณรู้ด้วยนะครับ ^^

  • สารพัดวิธีแยกข้อความออกจากกัน

    สารพัดวิธีแยกข้อความออกจากกัน

    สมมติว่าเรามีข้อความบางอย่าง แล้วเราอยากจะแยกข้อความออกจากกัน

    เช่น ตัวอย่างข้างล่างนี้ มีคั่นด้วย – (แต่ว่าตำแหน่งของมันไม่แน่ไม่นอน) เราจะแยกข้อความออกมาเป็น 3 ส่วนได้ยังไง? มาดูกัน

    สารพัดวิธีแยกข้อความออกจากกัน 223

    วิธีแยกข้อความ 1 : Text to Column

    วิธีที่ง่ายที่สุดคือการใช้เครื่องมือ Text to Column โดยให้ Copy ข้อมูลต้นฉบับออกมาก่อน

    แล้วเลือกข้อมูล แล้วไปที่ [Data]–> Data Tools –> Text to Column –> Delimited และให้ใส่เครื่องหมาย – (ที่เป็นตัวคั่น) ลงไปใน Other

    สารพัดวิธีแยกข้อความออกจากกัน 224

    จากนั้นกด Finish แค่นี้ก็เสร็จแล้ว ได้เป็น 3 คอลัมน์อย่างที่ต้องการ

    สารพัดวิธีแยกข้อความออกจากกัน 225

    วิธีแยกข้อความ 2 : ใช้สูตร

    การใส่สูตรมีข้อดีอย่างมาก คือ ข้อมูลอัปเดทโดยอัตโนมัติ โดยไม่ต้องกดเครื่องมือใดๆ เลย แต่ข้อเสียคือ เขียนยาก ถ้ายังใช้สูตรไม่คล่อง

    คำตัวต้น

    ตัวต้นเป็นตัวที่หาง่ายสุด ใน 3 ตัว เพราะการใช้ฟังก์ชัน FIND หรือ SEARCH ซึ่งสามารถหาตำแหน่ง – ตัวแรกได้อยู่แล้ว

    หาตำแหน่งตัวคั่นแรก : =FIND("-",A2)
    เอาข้อความข้างซ้าย : =LEFT(A2,FIND("-",A2)-1)

    ที่ต้อง -1 เพราะไม่ต้องการเครื่องหมาย – มาด้วย จึงต้องร่นไปทางซ้ายอีก 1 ตำแหน่ง

    สารพัดวิธีแยกข้อความออกจากกัน 226

    คำตัวกลาง

    ตัวกลาง จะหาได้เราต้องรู้ตำแหน่งของ – ตัวหน้า และ – ตัวหลัง ซึ่งตัวหน้าเรารู้อยู่แล้ว

    ซึ่ง – ตัวหลัง หากได้ 2 วิธี

    วิธีแรก

    • ใช้ FIND ซ้ำไปอีก โดยระบุการค้นหาให้เริ่มจากตำแหน่ง – ตัวแรก โดย+เพิ่มไปอีก 1 ตำแหน่ง ซึ่งจะทำให้เจอ – ตัวที่สอง
    =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)
    สารพัดวิธีแยกข้อความออกจากกัน 227

    คำตัวหลัง

    หาไม่ยากโดยใช้ RIGHT

    แต่จะเอา RIGHT กี่ตัวดี?  วิธีคิดคือเอาจำนวนตัวอักษรทั้งหมด – ตำแหน่งตัวคั่นที่สอง

     =LEN(A2)-FIND("|",SUBSTITUTE(A2,"-","|",2))

    จากนั้นก็ใช้ RIGHT ได้เลย

     =RIGHT(A2,   LEN(A2)-FIND("|",SUBSTITUTE(A2,"-","|",2))   )
    สารพัดวิธีแยกข้อความออกจากกัน 228

    วิธีแยกข้อความ 3 : Power Query

    ถ้า Excel 2016 จะมีมาให้เลย แต่ถ้าต่ำกว่านั้นต้องไป Download Add-in ก่อน (ฟรี) แต่โหลดให้ถูก version นะ ไม่งั้นจะลงไม่ได้

    ถ้าพร้อมแล้วก็สร้าง Table ก่อน โดยเลือกข้อมูลแล้วกด Ctrl+T

    สารพัดวิธีแยกข้อความออกจากกัน 229

    จากนั้นไปที่ Data (หรือ Power Query)-> From Table

    สารพัดวิธีแยกข้อความออกจากกัน 230

    Add Column -> Duplicate Column แรกออกมาก่อน ไม่งั้นจะไม่เหลือตัว Original เก็บไว้

    เลือก column ใหม่แล้วไปที่ Transform -> Split Column -> Delimiter

    สารพัดวิธีแยกข้อความออกจากกัน 231

    เลือก custom ใส่ – ลงไป แล้ว ok

    สารพัดวิธีแยกข้อความออกจากกัน 232

    ได้ผลลัพธ์ใน Power Query ดังใจแล้ว กด Close & Load ได้เลย

    สารพัดวิธีแยกข้อความออกจากกัน 233

    ผลลัพธ์จะกลับมาที่ Excel ใน Sheet ใหม่

    สารพัดวิธีแยกข้อความออกจากกัน 234

    จะเห็นว่าคล้ายวิธีแรก แต่ข้อดีกว่ามากๆ เลย คือ หากมีข้อมูลเพิ่มมา แค่กด Refresh ผลลัพธ์ก็จะเปลี่ยนและอัปเดทให้อัตโนมัติ!!

    เพิ่ม Data ไปอีก 2 บรรทัด

    สารพัดวิธีแยกข้อความออกจากกัน 235

    ไปที่ผลลัพธ์ กดคลิ๊กขวา Refresh

    สารพัดวิธีแยกข้อความออกจากกัน 236

    ปรากฎว่า Data ใหม่มา แต่มีอันนึงไม่ยอมแยกข้อความให้

    สารพัดวิธีแยกข้อความออกจากกัน 237

    เราสามารถไปตรวจสอบได้โดยไปที่ Query –> Edit

    สารพัดวิธีแยกข้อความออกจากกัน 238

    ปรากฎว่า มีค่า Error ขึ้นมาบรรทัดนึงนี่เอง

    สารพัดวิธีแยกข้อความออกจากกัน 239

    พอลองไล่คลิ๊ก Applied Steps ดูก็พบว่า สามารถทำมาถึง Split Column by Delimiter ได้โดยไม่ Error เลย

    สารพัดวิธีแยกข้อความออกจากกัน 240

    ซึ่งแปลว่า Error น่าจะเกิดจากการทำ Changed Type Step สุดท้าย (เปลี่ยนตัวหนังสือเป็นตัวเลข)

    เราจึงกด x ข้างหน้่า Changed Type อันสุดท้ายเพื่อบอกว่า Power Query ไม่ต้องทำ Step นี้แล้วนะ ลบ Step ทิ้งไปเลย

    สารพัดวิธีแยกข้อความออกจากกัน 241

    จากนั้นกด Close & Load เป็นอันจบ เสร็จแบบเนียนๆ เลย

    สารพัดวิธีแยกข้อความออกจากกัน 242

    บทสรุป

    • ถ้าทำที่เดียวจบ ใช้ Text to Column เนี่ยเจ๋งสุดแล้ว เร็วและง่าย แต่ถ้ามีข้อมูลมาใหม่ต้องทำใหม่
    • วิธีเขียนสูตรจะเขียนยากหน่อย แต่ Flexible มาก จะเขียนอะไรก็ได้ อีกอย่าง ถ้ามีข้อมูลใหม่แค่ลากสูตรก็ได้ผลลัพธ์เลย
    • วิธี Power Query ใช้ไม่ยาก แต่อาจยังไม่คุ้นเคย หัดใช้บ่อยๆ จะพบว่าทรงพลังมากๆ ข้อมูลใหม่มาแค่กด Refresh ก็ได้ผลลัพธ์เลย

    ยาวหน่อยแต่หวังว่าจะถูกใจผู้อ่านนะครับ ใครสงสัยอะไรก็สามารถถามมาได้เลยครับ

  • 17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋

    17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋

    นี่คือของขวัญที่ผมตั้งใจเตรียมไว้ให้กับทุกคน ด้วยบทความ “17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋” ซึ่ง Concept คืออ่านบทความเดียวแล้วเก่ง Excel ขึ้นอย่างรวดเร็ว!!

    ผมไม่รู้ว่าจะเป็นไปได้มั้ย? แต่ก็อยากลองให้อ่านดูครับ ด้วยการคัดเลือกเคล็ดลับที่สำคัญในการใช้ Excel มีทั้งง่ายและยากปนกัน มันต้องมีบางอันที่ช่วยปลดล๊อคความสามารถของคุณบ้างล่ะ ลงทุนเวลาอ่านซัก 30 นาที รับรองว่าจะได้เวลาคืนมาอีกเพียบครับ !!

    หมายเหตุ : จุดประสงค์ในบทความนี้จะเน้นให้คุณไม่พลาด Concept เจ๋งๆ ซึ่งบางทีอาจไม่สามารถอธิบายโดยละเอียดได้ในบทความเดียว ดังนั้นถ้าอ่านแล้วยังสงสัยในวิธีทำ ก็สามารถ Google หาวิธีทำละเอียดจาก Keyword ได้เลยครับ

    17 เคล็ดวิชา เก่ง Excel

    เคล็ดวิชา 1 : หลักคิดพิชิตทุกปัญหา

    17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋ 243

    หลักการนี้ คือ สิ่งที่ผมใช้ในการแก้ปัญหาเกือบทุกๆ อย่างใน Excel ซึ่งหลักการเหล่านี้เป็นแก่นที่แฝงอยู่ในเคล็ดลับข้ออื่นๆ ด้วย

    • หาเป้าหมายหรือโปรเจคที่อยากจะทำให้ได้ การเรียนรู้แบบมีเป้าหมาย จะช่วยให้เรียนรู้ไวและจับต้องได้มากกว่าอ่านเฉยๆ
    • คิดย้อนกลับจากเป้าหมาย (Backward Thinking)
      • เมื่อต้องการผลลัพธ์อะไรก็ตาม ให้คิดย้อนกลับว่าเราจะไปถึงเป้าหมายนั้นด้วยวิธีใด
      • เช่น หากอยากเขียนสูตรที่สามารถดึงค่าชื่อ Sheet ได้
        • เราอาจไปรู้มาว่า ฟังก์ชัน =CELL(“filename”,A1) จะให้ชื่อ Sheet กลับมาได้
        • แต่มันดันให้ค่ากลับมา ในรูปแบบของ –>  path[ชื่อไฟล์.สกุล]ชื่อsheet
        • ทีนี้ก็ต้องมาคิดต่อว่าจะเอาออกมาแต่ ชื่อSheet อย่างเดียวได้ยังไง?
    • แบ่งงานใหญ่ให้เป็นงานย่อย (Divide & Conquer)
      • หากงานหนึ่งๆ ที่จะทำมันยากและซับซ้อนเกินไป ให้แบ่งมันเป็นงานย่อยๆ ที่เราเข้าใจและจัดการกับมันได้
      • เช่น เป้าหมาย คือ ดึง ชื่อSheet ออกมา จาก path[ชื่อไฟล์.สกุล]ชื่อsheet
        • เราก็ต้องคิดต่อว่า ตัวเราเองรู้ได้ไงว่าชื่อSheet อยู่ไหน? ซึ่งมันอยู่เครื่องหมาย ] นั่นเอง
        • ซึ่งอาจใช้ FIND หาตำแหน่ง ]
        • แล้วเอา LEN RIGHT มาช่วยตัดชื่อ Sheet ออกมา อีกที
      • ฟังก์ชันไหนใช้ไม่เป็น ก็ค่อยๆ หาทางเรียนรู้เพิ่มเติม
        • สมมติว่าคุณยังไม่รู้ฟังก์ชันใน Excel เท่าที่ควร
        • ลองตั้งเป้าในการเรียนรู้ฟังก์ชันใหม่ๆ เพิ่มขึ้นวันละ 1 ตัวดูมั้ยล่ะครับ?
        • แค่ 1 เดือน คุณก็รู้ฟังก์ชันมากมายมหาศาลแล้ว
        • การแบ่งทำวันละ 1 อัน มันดูเป็นไปได้มากกว่าการเรียนฟังก์ชัน 30 ตัวเยอะครับ!
    • ใช้ Excel ภาษาอังกฤษ และเรียนรู้ศัพท์เทคนิคภาษาอังกฤษเอาไว้
      • Keyword ที่ผมจะให้ในบทความนี้ก็จะเป็นภาษาอังกฤษนะครับ
      • Google หาคำตอบ ด้วยภาษาอังกฤษ จะเจอคำตอบง่ายกว่าเยอะครับ

    เคล็ดวิชา 2: การเขียนสูตรคือ อะไรกันแน่?

    17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋ 244

    • การเขียนสูตรคือ “การดึง” ไม่ใช่การผลัก
      • อยากให้ผลลัพธ์แสดงในช่องไหน ต้องเขียนสูตรช่องนั้น
      • หากเราจะเขียนสูตรให้ช่อง B3 แสดงค่าอะไรซักอย่าง เราต้องเขียนสูตรที่ B3 เท่านั้น
      • ไม่สามารถไปเขียนสูตรที่อื่นแล้วผลักค่าไปที่ช่อง B3 โดยที่ B3 ไม่เขียนอะไรเลย (ถ้าจะทำต้องใช้ VBA สั่ง)
      • การดึงค่าแบบตรงๆ นี่แหละ ช่วยให้เราไม่ต้องพิมพ์ข้อมูลซ้ำๆ กันหลายที
      • เรา”ดึงค่า” จาก Cell ไหนก็ได้ โดยกด = แล้วจิ้ม Cell ที่ต้องการ ไม่ว่าจะอยู่ Sheet เดียวกัน/Sheet อื่น/ไฟล์อื่นก็ได้
    • นอกจากจะดึงค่าตรงๆ แล้ว สิ่งที่ต้องทำก็ไม่พ้นการเอามาใส่สูตรเพื่อ “ผูกความสัมพันธ์” เพื่อสร้างผลลัพธ์บางอย่าง
      • ความสัมพันธ์ อาจเป็นสิ่งที่เป็นสากล เช่น การคำนวณทางคณิตศาสตร์/วิทยาศาสตร์ หรือ
      • ความสัมพันธ์ อาจเป็นสิ่งที่เราคิดหรือตั้งเกณฑ์ขึ้นมาเอง เช่น การประมาณการยอดขาย เป็นต้น
    • เราไม่สามารถที่จะทำให้ Cell หนึ่งๆ เป็นทั้งสูตร และเป็นทั้งช่องกรอกข้อมูลพร้อมๆ กันได้ (ถ้ากรอกข้อมูลทับสูตรจะหายไป)
    • สุดท้ายแล้ว จงมองให้เห็น Flow ของข้อมูล ที่ถูกเชื่อมด้วยการเขียนสูตรผูกความสัมพันธ์
    • การแยกพื้นที่ระหว่าง Input และ Output ออกจากกันอย่างเป็นระเบียบ จะช่วยให้ Flow ของข้อมูลเป็นระเบียบและแก้ไขได้ง่าย

    เคล็ดวิชา 3: แก้ Format ไม่เกี่ยวกับ Content

    17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋ 245
    • เรื่องของ Format เป็นแค่การกำหนดรูปแบบการแสดงผลให้เราเห็น Content ในรูปแบบที่เปลี่ยนไปตามต้องการ
      • คล้ายกับการเปลี่ยนเคสของมือถือ หรือ เปลี่ยน Theme ของ App Line นั่นแหละ
    • ตัวอย่างเช่น
      • หากเราเขียนใน A1 เป็น 3.5
      • แล้วเขียนใน A2 ว่า =A1 (จะได้ 3.5 เหมือน A1)
      • จากนั้นเปลี่ยน Number Format ลดทศนิยมใน A1 จะเห็นเป็นเลข 4 (เพราะ 3.5 ปัดขึ้นเป็น 4)
      • ค่าที่แท้จริงใน A1 ยังคงเป็น 3.5 อยู่ดี เช่นเดียวกับค่าใน A2 (ที่ดึงค่าใน A1 ไปแสดง)
    • เรื่องของ Format กับ Content จะถูกพูดถึงอย่างเด่นชัดอีกทีในเคล็ดวิชาที่ 6 เรื่องวันที่และเวลาครับ

    เคล็ดวิชา 4: ประเภทของข้อมูลนั้นสำคัญยิ่ง!

    17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋ 246
    • ประเภทข้อมูลมี 4 แบบหลักๆ คือ Number, Text, Logic, Error
    • เช็คประเภทข้อมูลด้วยตาเปล่าไม่ได้ ให้ใช้ฟังก์ชัน TYPE มาช่วย จะได้ผลลัพธ์เป็นตัวเลข
      • 1 = Number, 2=Text, 4= Logic, 16 = Error
    • หากข้อมูลเป็นคนละประเภทกัน แม้จะดูเหมือนกัน แต่ Excel จะถือว่าไม่เท่ากัน เช่น พวกนี้จะได้ FALSE ทั้งหมด
      • “10”=10
      • 1 = TRUE
      • 0 = FALSE
    • เช่นเดียวกับการใช้ Lookup Function อย่าง VLOOKUP และ MATCH ที่จะหาเจอเฉพาะข้อมูลประเภทเดียวกันเท่านั้น
    • Input หรือ Argument ของฟังก์ชันต่างๆ ก็จะต้องการประเภทข้อมูลที่ค่อนข้างเฉพาะเจาะจง ต้องใส่ข้อมูลให้ถูกประเภท
    • นอกจากนี้ ผลลัพธ์ของฟังก์ชันต่างๆ ก็จะให้ประเภทข้อมูลที่แตกต่างกันด้วย

    เคล็ดวิชา 5: Operator ใช้แปลงประเภทข้อมูลได้

    17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋ 247
    • Operator แต่ละประเภท เมื่อนำไปประกอบกับในสูตร จะให้ “ประเภทผลลัพธ์ของข้อมูล” ที่แตกต่างกัน
    • Arithmetic Operator  เช่น +, -, *, /, ^, % เอาไว้คำนวณ
      • ทำให้ผลลัพธ์เป็น Number
      • ทำได้หลายแบบ ยังไงก็ได้ให้ผลลัพธ์ไม่เพี้ยน เช่น *1,+0,-0,/1
        • แต่ที่นิยมคือ ใช้การ *1 หรือ — นำหน้า
        • =”100″*1 จะได้ 100 (เป็น Number)
        • =–TRUE จะได้ 1 (เป็น Number)
    • Text Operator เช่น & เอาไว้เชื่อมข้อความ
      • ทำให้ผลลัพธ์เป็น Text
      • เช่น 200&”” จะได้ “200” (เป็น Text)
    • Comparison Operator เช่น >, =, <, >=, <=, <>
      • ทำให้ผลลัพธ์เป็น Logic
      • เช่น =500>1000 ได้ FALSE
    • Range Operator เช่น , (comma) : (colon)  (space) เอาไว้เชื่อม Cell Reference
      • ทำให้ผลลัพธ์เป็น Cell Reference/Range
      • comma เชื่อมแบบไม่ต่อเนื่อง เช่น C10,D12
      • colon เชื่อมแบบต่อเนื่องกัน เช่น A1:B5
      • space เอาส่วนที่ทับซ้อนกัน เช่น A2:E3 C1:D5 จะได้ C2:D3
    • Tips : ข้อมูลที่มี Error เช่น #DIV/0!, #N/A, #NAME?
      • ทำให้ผลลัพธ์ Error ตามไปด้วย
      • นอกจากจะใช้ฟังก์ชันพวก ISERROR, IFERROR เข้ามาช่วย

    เคล็ดวิชา 6: วันที่และเวลา คือ ตัวเลข จำนวนเต็มและทศนิยม ที่เปลี่ยน Format ไปเท่านั้น

    17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋ 248
    • Excel จะมองวันที่เป็นแค่เลขจำนวนเต็มธรรมดาๆ และมองเวลาเป็นทศนิยม (ส่วนหนึ่งของวัน)
      • วันที่ 1/1/1900 (ค.ศ.) เวลา 0:00 เป็นเลข 1
      • วันที่ 1/1/1900 (ค.ศ.) เวลา 12:00 เป็นเลข 1.5
      • วันที่ 2/1/1900 (ค.ศ.) เวลา 0:00 เป็นเลข 2
      • วันที่ 2/1/1900 (ค.ศ.) เวลา 12:00 เป็นเลข 2.5
    • จะแปลงตัวเลข เป็นวันที่/เวลา หรือกลับกัน ทำได้ง่ายๆ โดยการเปลี่ยน Number Format
    • ดังนั้นการหาระยะห่างของวัน 2 วัน จึงเอาวันที่มาลบกันตรงๆ ได้เลย เช่น
      • A1=28/11/2016 และ A2=4/12/2016
      • ระยะห่าง = A2-A1 = 6 วัน
      • เพราะค่าที่แท้จริง คือ A1=42702 และ A2 = 42708
    • ดังนั้นถ้ามีวันที่+เวลาอยู่ใน Cell หนึ่งๆ เช่น A1
      • จะแยกวันออกมาใช้ INT เอาจำนวนเต็ม นั่นคือ =INT(A1)
      • จะแยกเวลาใช้ MOD หาร 1 เพื่อเอาทศนิยม นั่นคือ =MOD(A1,1)
    • เวลา Input ข้อมูลวันที่ Excel จะตีความปีที่กรอกเป็น ค.ศ. (ยกเว้นไปตั้งค่า Format Cell นั้นๆ เป็นปฏิทินไทย แล้วเลือก Input dates according to selected calendar จะสามารถกรอกเป็น พ.ศ.ได้)

    เคล็ดวิชา 7: การใส่ $ ใน Cell Reference

    17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋ 249
    • เมื่อทำการ Copy สูตรที่มี Cell Reference อยู่ในสูตร จะทำให้ Cell Reference เลื่อนตำแหน่งตามทิศทางการ Copy/Paste โดยอัตโนมัติ
    • หากไม่อยากให้เลื่อนต้องใส่เครื่องหมาย $ ลงไปใน Cell Reference (กด F4 ช่วยได้ สามารถกดวนรูปแบบได้ 4 อย่าง)
    • หากมี $ หน้าอะไร ถือว่าตัวนั้นจะไม่เลื่อน เช่น
      • A1 –> Column เลื่อน, Row เลื่อน เรียกว่า Relative Cell Reference
      • $A$1 –> Column ไม่เลื่อน, Row ไม่เลื่อน เรียกว่า Absolute Cell Reference
      • A$1 –> Column เลื่อน, Row ไม่เลื่อน เรียกว่า Mixed Cell Reference
      • $A1 –> Column ไม่เลื่อน, Row เลื่อน เรียกว่า Mixed Cell Reference
    • หลักคิดสำคัญเพื่อไม่ให้งง ว่า เมื่อไหร่ต้องใส่ $ ยังไง คือ ใช้หลักการ Divide & Conquer
      • โดยพิจารณามองทีละ Cell Reference
      • แล้วคิดทีละทิศคือ แนวนอน และ แนวตั้ง โดยถามคำถามว่า…
      • ถ้า Copy Cell นี้ไปทางขวา เจ้า Cell Reference ที่กำลังอ้างถึงอยู่ควรจะเลื่อนทางขวาด้วยมั้ย?
        • ถ้าไม่ควร ก็ใส่ $หน้ารหัสคอลัมน์
        • ถ้าควร ก็ไม่ใส่ $หน้ารหัสคอลัมน์
      • ถ้า Copy Cell นี้ลงข้างล่าง เจ้า Cell Reference ที่กำลังอ้างถึงอยู่ควรจะเลื่อนลงด้วยมั้ย?
        • ถ้าไม่ควร ก็ใส่ $หน้าเลขแถว
        • ถ้าควร ก็ไม่ใส่ $หน้าเลขแถว

    เคล็ดวิชา 8: การทดข้อมูล / ทดสูตร / Helper Column ช่วยได้มาก

    17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋ 250
    • การทดข้อมูล คือ การประยุกต์เอาหลัก Divide & Conquer มาใช้แบบหนึ่ง
      • การเขียนสูตรแบบซับซ้อน จริงๆ แล้วเราไม่จำเป็นต้องเขียนสูตรยาวๆ ในทีเดียว แต่เราเขียนสูตรทีละส่วน (โดยให้อ้างอิงถึงกัน) แล้วค่อยจับนำมารวมกันภายหลัง
      • เช่น อยากดึงค่าบางอย่างจากที่อื่นมาไว้ใน Cell ที่กำหนด
        • เราสามารถใช้ INDEX ได้ ในรูปแบบ =INDEX(array,row_num,column_num)
        • และใน row_num และ column_num เราต้องการให้มันมีความเป็น Dynamic โดย Link กับ Dropdown List ที่ User เลือก ดังนั้นเราจะใช้ MATCH มาช่วย
        • แต่แทนที่จะเขียน MATCH ลงไปใน INDEX เลย เราสามารถเขียนแยกไว้ในอีก Cell เพื่อลดความงง
        • พอเขียนเสร็จ ตรวจแล้วทำงานถูกต้อง ค่อยจับนำมารวบรวมกับ INDEX ทีหลัง
      • การทำแบบนี้ ฝรั่งเรียกว่า Mega Formula ซึ่งจะช่วยให้เราจะดูเทพขึ้นมาทันที !! 555
    • Helper Column ก็เป็นหนึ่งในรูปแบบของการทดเหมือนกัน มันคือการสร้างคอลัมน์ที่ช่วยให้เราจัดการกับอะไรต่างๆ ง่ายขึ้น
      • สามารถเขียนเงื่อนไข AND OR ที่ซับซ้อน แทนที่จะใช้ Criteria ใน Filter/Pivot/SUMIFS/Advanced Filter ซึ่งอาจไม่ Flexible เท่าการเขียนสูตร
      • ลองดูตัวอย่าง การสร้าง Helper Column เพื่อรองรับการ Unsort ในเคล็ดวิชา 9 ได้

    เคล็ดวิชา 9: การเตรียมข้อมูลเพื่อ การ Sort/Filter/Pivot

    17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋ 251
    • ก่อนจะนำข้อมูลไป Sort/Filter หรือ PivotTable ควรจัดข้อมูลให้อยู่ในรูปแบบ Database ก่อนเสมอ
      • มีหัวตารางที่บอกว่าคอลัมน์นั้นๆ คือ อะไร แค่ 1 บรรทัด
      • ข้อมูลห้ามขาดหายไปทั้งบรรทัด
    • ไม่ว่าจะ Sort หรือ Filter ผมแนะนำให้ใช้ คำสั่ง Filter เครื่องมือเดียวนี่แหละ
    • การ Filter สามารถ Search ได้ และมีคำสั่ง Add to Current selection ด้วย เพื่อที่จะ Add สิ่งที่ Search เจอได้โดยไม่ต้อง Clear สิ่งที่เลือกไว้เดิม
    • การ Filter สามารถ Clear Filter กลับมาเป็นแบบเก่าได้ แต่ Sort ไม่มีคำสั่ง Unsort
      • ดังนั้นถ้าจะอยากให้เรียงกลับเป็นเหมือนเดิมได้ ต้องใช้ Helper Column สร้างเลข Running เอาไว้ก่อนเลย
    • หากอยากที่จะ Filter แล้วเลขสามารถ Run ใหม่ตามข้อมูลที่ Filter ให้ใช้ SUBTOTAL/AGGREGATE เข้าช่วย เพราะมีความสามารถนับข้อมูลเท่าที่มองเห็นได้ (รายละเอียดอ่านได้ที่ http://www.thepexcel.com/filtered-running-number/)
    • ก่อนจะนำข้อมูลไปวิเคราะห์ใดๆ ควรเช็คความถูกต้องโดย Sort/Filter ดูความผิดปกติก่อน
      • เช่น ไม่มีค่าน้อยไป/มากไป
      • ไม่มีข้อมูลขยะ
      • มีข้อมูล Blank ที่ไม่ต้องการหรือไม่
    • หากข้อมูลไม่อยู่ในรูปแบบที่เหมาะสม อาจต้องเขียนสูตรเพื่อดัดแปลงข้อมูลก่อน
    • หากข้อมูลแยกกันอยู่หลาย Sheet อาจพิจารณาใช้สูตรพวก Lookup เพื่อรวม Database ก่อน หรือ จะใช้พวก Data Model เพื่อสร้าง Relationship โดยไม่ต้องรวมตารางก่อนก็ได้

    เคล็ดวิชา 10: รู้จักการใช้ Table เพื่อสร้าง Dynamic Range

    17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋ 252
    • ข้อดีที่สุดอย่างหนึ่งของการใช้ Table ก็คือ ความสามารถในการทำ Dynamic Range อย่างง่ายที่สุด
    • Table สามารถยืดพื้นที่การอ้างอิง ให้งอกตามการใส่ข้อมูลที่เพิ่มขึ้นได้เองโดยอัตโนมัติ
    • การอ้างอิงค่าจาก Table สามารถอ้างอิงได้ทั้งการใช้ Cell Reference ปกติ และ Structure Reference เช่น Table1[สินค้า] ดังรูป
    • นำไปประยุกต์เป็น Data Source ของเครื่องมืออื่นๆ ได้มากมาย เช่น Dropdown List (Data Validation), PivotTable, กราฟต่างๆ เป็นต้น

    เคล็ดวิชา 11: รู้จักใช้ความสามารถของ PivotTable

    17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋ 253
    • PivotTable สามารถแปลงข้อมูลเชิง Database ให้เเป็นข้อมูลสรุปได้อย่างง่ายดาย
      • PivotTable ใช้งานง่ายมาก เขียนสูตรไม่เป็นเลยก็ยังใช้งานได้
    • หัวตารางของ Data Source จะถูกสร้างให้กลายเป็น Field List
    • เวลาใช้ ให้พิจารณาองค์ประกอบทีละส่วนว่า Field List แต่ละอันจะเอาไปไว้ในส่วนไหน ใน 4 ส่วนนี้
      • Filter : เอาไว้คัดกรองข้อมูลให้กับทั้งตาราง Pivot
        • มีลูกเล่น Generate Report Filter Page
        • ปกติแล้วมันจะเก็บข้อมูล Item List เอาไว้แม้ว่าใน Data Source จะไม่มีข้อมูลนั้นๆ แล้ว
        • เราสามารถทำให้ Item List มันยึดอันใหม่เสมอได้โดยไปที่ PivotTable Option –> Data –> Number of Items to retain per field –> None
      • Rows/Columns : เอาข้อมูลมาแสดงไว้คนละแถว/คอลัมน์ (แบบไม่ซ้ำกัน) เพื่อเป็น Caetgory ให้กับตารางสรุป
        • มีลูกเล่น Sort/Filter/Grouping
        • เราสามารถ Grouping ข้อมูลใน Row/Column Label ได้ ทั้งแบบ Manual และ แบบ Auto
          • Grouping แบบ Manual เหมาะกับข้อมูลที่เป็น Text และมันจะสร้าง Field ใหม่ขึ้นมาให้ด้วย
          • Grouping แบบ Auto ข้อมูล Field นั้นๆ จะต้องเป็นตัวเลข หรือ วันที่เท่านั้น และห้ามมีช่องว่าง จึงจะใช้ได้
        • ปกติแล้ว ข้อมูลใน Row/Column Label  ใดที่ไม่มีค่า มันจะไม่แสดง Row/Column Label  นั้นๆ
          • เราสามารถบังคับให้แสดงได้โดยไปที่ Field Setting -> Layout & Print -> Show items with no data
      • Values : เอาไว้คำนวณสรุปผลข้อมูล ซึ่งมีลูกเล่นสำคัญ 2 อย่าง คือ
        • Summarized Value by : เลือกวิธีในการคำนวณสรุปผล เช่น Sum, Count, Max, Min
          • ถ้าข้อมูลเป็นตัวเลข และไม่มี Blank เลย เวลาเอามาใส่จะเป็น Sum โดยอัตโนมัติ นอกนั้นเป็น Count
        • Show Value As : เลือกว่าจะโชว์ค่าตรงๆ หรือ จะแสดงเทียบกับช่องอื่น เช่น %ของ xxx
    • ข้อเสียสำคัญ ของ PivotTable คือ เมื่อข้อมูลต้นทางเปลี่ยน จะต้องกดปุ่ม Refresh ค่าใน Pivot ถึงจะเปลี่ยน (ถ้าไม่อยากกดเอง สามารถใช้ VBA ช่วยได้)
    • PivotTable ปกติจะเปิดโหมด Generate GetPivotData ไว้
      • ทำให้เมื่อเขียนสูตรแล้วจิ้มไปในบริเวณ PivotTable แล้วจะเกิดสูตรยาวๆ ที่ใช้งานยาก
      • แต่ข้อดีคือ สามารถอ้างอิงค่าโดยไม่ต้องกังวลว่า PivotTable จะพลิกหน้าตาหลายเป็นแบบใด
      • วิธีปิดโหมดนี้ คือไปที่ Ribbon ของ PivotTable แล้วติ๊กลูกศรใต้ PivotTable Option
    • การใช้ Calculated Field ใน PivotTable เหมาะกับ การคำนวณค่า %Success Rate ของแต่ละกลุ่มข้อมูลมาก
      • การหาพวก % Success จะทำใน Data Source ด้วยการเขียนสูตรได้ลำบากกว่าการใช้ Calculated Field ใน PivotTable มาก
      • นอกจากนี้ การสร้าง PivotTable แล้วเขียนสูตรคำนวณเองข้างๆ ก็เสี่ยงต่ออารถูกหมุนข้อมูลไปทับ
      • เทคนิคการทำ % Success ง่ายๆ คือ ให้สร้าง Helper Column 2 อันใน Data Source
        • อันแรกเป็นตัวส่วน สมมติชื่อว่า AllCase (อาจให้เป็นเลข 1 เหมือนกันทุกแถว)
        • อีกอันเป็นตัวเศษ สมมติชื่อว่า SuccessCase ซึ่งถ้า Success จะให้เป็นเลข 1 ถ้าไม่ Success ก็ให้เป็นเลข 0
      • จากนั้นตอนสร้าง Calculated Field ก็จะสามารถใส่สูตรว่า SuccessCase/AllCase ได้เลย

    เคล็ดวิชา 12: หัดใช้ Keyboard Shortcut เถอะ

    17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋ 254

    ใช้ Keyboard Shortcut แล้วช่วยให้ทำงานเร็วขึ้นเยอะครับ ที่ต้องใช้บ่อยๆ มีไม่กี่ตัว ที่ผมใช้บ่อยก็มีตามนี้

    • กด Alt แล้วตามด้วยอักษรที่ขึ้นมา เพื่อเรียกใช้งาน Ribbon และ Quick Access Toolbar
    • Ctrl + ลูกศร = วิ่งไปสุดทาง
    • Ctrl + Shift + ลูกศร = เลือกพื้นที่ไปสุดทาง
    • Ctrl+c = copy
    • Ctrl+x = cut
    • Ctrl + v = paste
    • Ctrl+z = undo
    • Ctrl+Shift+L = Filter on/off
    • Ctrl+1 = Format Cell / Format ส่วนประกอบของกราฟ
    • นอกจากนี้ยังมี Set เอาไว้เปลี่ยน Number Format ซึ่งมีวิธีการจำที่น่าสนใจมาก
      • Ctrl+% เพื่อแปลงเป็นหน่วย % (จริงๆ ต้องกด Ctrl+Shift+5 เพราะ Shift +5 คือตัว % แต่ถ้าต้องจำว่า Ctrl+Shift+5 จะไม่มีทางจำได้เลย)
      • Ctrl+^ ก็เพื่อแปลงเป็นเลข Scientific E ยกกำลัง (เพราะ ^ เป็นเครื่องหมายยกกำลัง)
      • Ctrl+$ ก็เพื่อแปลงเป็นรูปแบบสกุลเงิน
      • Ctrl+# ก็เพื่อแปลงเป็นวันที่ (เพราะในโปรแกรม Access ก็ใส่วันที่ในเครื่องหมาย #)
      • Ctrl+@ ก็เพื่อแปลงเป็นเวลา เพราะ เครื่องหมาย@ ก็ดูเจาะจง คล้ายว่าจะระบุว่า ณ กี่โมง
      • Ctrl+: Stamp เวลาปัจจุบัน เพราะเหมือนเครื่องหมายคั่น ชม:นาที (ส่วน Ctrl+; = stamp ค่าวันที่ปัจจุบัน)
      • Ctrl+* เลือกข้อมูลใน Range ทั้งหมด เพราะ * แทนความหมายว่าทั้งหมด ในภาษาฐานข้อมูล
    • F2 = Edit สูตร
    • F3 = Paste ชื่อที่ตั้งไว้
    • F4 = ใส่ $ ใน Cell Reference (เมื่อกำลัง edit สูตร) / Repeat Action ล่าสุด

    เคล็ดวิชา 13: Function ที่ต้องรู้จักมีไม่เยอะ

    17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋ 255
    • รู้จักฟังก์ชั่น 20% แต่ทำงานได้ 80% ตามกฎ 80/20
    • ผมมั่นใจว่า ถ้าคุณรู้จักฟังก์ชันไม่กี่อันเหล่านี้เป็นอย่างดี รับรองว่าหากินได้เยอะมาก
    • สิ่งที่ต้องจำคือ “หน้าที่ของมัน” ซึ่งสำคัญกว่าวิธีการเขียน (อันหลังเปิด Google/Help เอาก็ได้)
    • กลุ่มคำนวณ
      • SUM = หาผลรวมของข้อมูลที่เป็นตัวเลข
      • COUNT = นับจำนวนข้อมูลที่เป็นตัวเลข
      • COUNTA = นับจำนวนข้อมูลที่ไม่ใช่ช่องว่าง
      • MAX/MIN = หาค่ามากสุด/น้อยสุด
      • LARGE/SMALL = หาค่ามากสุด/น้อยสุด เป็นลำดับที่ xx
      • AGGREGATE = ไว้สรุปผลข้อมูล มีหลาย option เช่น ไม่สนใจช่องที่ซ่อนอยู่ ไม่สนใจค่า error เป็นต้น
      • INT = ตัดทศนิยมทิ้ง ให้เหลือแต่จำนวนเต็ม
      • MOD = หาเศษจากการหาร
      • ROUND/ROUNDUP/ROUNDDOWN = ปัดทศนิยม
      • SUMPRODUCT = จับคู่คูณแล้วหาผลรวม
      • SUMIFS = หาผลรวมตามเงื่อนไข
    • กลุ่มตรรกะ
      • AND/OR/NOT = เอาไว้เชื่อมตรรกะ TRUE/FALSE
      • IF = ตรวจสอบเงื่อนไข ถ้าจริงใช้สูตร 1 ถ้าไม่จริงใช้สูตร 2
      • ISERROR = เช็คว่าค่า Error หรือไม่
      • ISNUMBER = เช็คว่าค่าเป็นตัวเลขหรือไม่
      • CHOOSE = เลือกว่าจะใช้สูตรไหนคำนวณ
    • กลุ่ม Lookup & Reference
      • VLOOKUP (ทั้งโหมด Exact และ Approximate Match) = Map ข้อมูลคำค้นหาดึงค่าจากตารางอ้างอิง
      • MATCH = หาว่าคำค้นหาอยู่ลำดับที่เท่าไหร่
      • INDEX = ให้ Cell Reference กลับมาเมื่อรู้พิกัดแถว/คอลัมน์ (ใช้คู่กับ MATCH จะเก่งมาก)
      • ADDRESS = แปลงลำดับแถว/คอลัมน์ ให้เป็น Text ที่หน้าตาเป็น Cell Reference
      • INDIRECT = แปลง Text ที่หน้าตาเป็น Cell Reference ให้เป็น Cell Reference จริงๆ
      • OFFSET = เลื่อน/ปรับขนาด Cell Reference
      • ROW/COLUMN = หาว่าอยู่แถว/คอลัมน์ที่เท่าไหร่
    • กลุ่ม Text
      • LEN = นับจำนวนอักขระ
      • TRIM = ตัดช่องว่างส่วนเกิน
      • LEFT/MID/RIGHT = ตัดคำจากทิศทางต่างๆ
      • FIND/SEARCH = ค้นหาคำที่ต้องการว่าอยู่อักขระที่เท่าไหร่
      • SUBSTITUTE = แทนที่คำเมื่อรู้คำที่ต้องการแทนที่
      • REPLACE = แทนที่คำเมื่อรู้ตำแหน่งที่ต้องการแทนที่
      • TEXT = แปลงตัวเลข ให้กลายเป็น Text ตาม Custom Number Format ที่กำหนด
    • กลุ่มวันที่และเวลา
      • TODAY = ให้ค่าวันที่ปัจจุบัน
      • DAY = ดึงค่าวัน จาก วันที่
      • MONTH = ดึงค่าเดือน จาก วันที่
      • YEAR = ดึงค่าปี จาก วันที่
      • DATE = สร้างวันที่ จาก วัน เดือน ปี
      • NETWORKDAYS = หาระยะเวลาวันทำงาน ไม่นับวันหยุด (นับวันเริ่ม)
      • WORKDAYS = หาวันสิ้นสุดงาน  เมื่อรู้ระยะเวลาวันทำงาน ไม่นับวันหยุด (ไม่นับวันเริ่ม)
      • NOW = ให้ค่าเวลาปัจจุบัน
      • HOUR = ดึงค่า ชั่วโมง จากเวลา
      • MINUTE = ดึงค่า นาที จากเวลา

    เคล็ดวิชา 14: เทคนิคการทำกราฟสุดพลิกแพลง

    รูปประกอบจากเพจ Excel Nana (ขอนำมาใช้เพราะชอบกราฟนี้มาก)

    17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋ 256
    • ต้องรู้จักประเภทกราฟหลักๆ ให้ดี เช่น
      • Bar/Column Chart ทั้งแบบ Cluster และ แบบ Stacked
      • Line Chart : แกน x เป็นเพียง Category label (ข้อความ)
      • XY Scatter : แกน x เป็นตัวเลขจริงๆ
      • Pie Chart
    • สามารถ Link ค่าจาก Cell มาที่ Label บนกราฟได้
      • เลือก Label ไปที่ Formula Bar กด = แล้วจิ้ม Cell mี่ต้องการ
    • สามารถผสมกราฟหลายชนิดในกราฟเดียวได้ (Change series chart type)
    • สามาารถ Plot ข้อมูลลงในแกน Y 2 แกนได้ (มักใช้กับกรณีที่ Scale ต่างกันมากๆ)
    • เทคนิคทำให้กราฟพลิกแพลงได้มักจะใช้องค์ประกอบแบบนี้
      • นำกราฟมาซ้อนกัน
      • ใช้ข้อมูลหลาย Series มาต่อกันให้เหมือนเป็น Series เดียว
      • ปรับสีของกราฟให้มองไม่เห็นบางส่วน (เช่น เลือก No Color)
      • ใช้การ Fill (ถมสี) ด้วย Picture เพื่อสร้างกราฟที่สวยงามและมีลูกเล่นมากขึ้น
    • หากสร้าง PivotChart จาก PivotTable เราสามารถเอาปุ่มสีเทาที่รกๆ ออกได้ โดยไปที่ Pivot Chart –> Field Buttons –> Hide All
    • ใครสนใจการทำกราฟแบบสร้างสรรค์ลองศึกษาได้จากเพจ Excel Nana ได้เลยครับ https://www.facebook.com/ExcelNaNa/

    เคล็ดวิชา 15: Defined Name นั้นมีดีกว่าแค่ชื่อ

    เก่ง Excel
    • นอกจากจะตั้งชื่อให้กับ Cell/Range ได้แล้ว ยังตั้งชื่อให้กับสูตรหรือค่าคงที่ได้ด้วย
    • การตั้งชื่อมีหลาย Scope คือ ระดับ Workbook  และ Worksheet (เป็นชื่อที่ฝังอยู่ที่ Sheet ใดชีทหนึ่ง)
    • ใน 1 Scope ห้ามมีชื่อที่ซ้ำกัน
    • เรามักประยุกต์เอา Defined Name ไปเป็น Data Source ของเครื่องมือต่างๆ เช่น Dropdown List (Data Validation), PivotTable, กราฟต่างๆ เป็นต้น
    • การใช้ Defined Name ทำให้สามารถใช้ Array Formula ได้โดยไม่ต้องกดปุ่ม Control+Shift+Enter
    • การใช้ Defined Name ทำให้สามารถสร้าง Dynamic Linked Picture เพื่อสร้าง Dynamic Chart ได้ (ร่วมกับสูตร INDEX)
    • Cell Reference ใน Defined Name ปกติจะใส่ $ ไว้เป็น Absolute Cell Reference โดยอัตโนมัติ แต่เราสามารถปรับออก เพื่อพลิกแพลงได้ตามต้องการ

    เคล็ดวิชา 16: เนรมิต Dashboard เจ๋งๆ

    รูปประกอบจากเรื่อง iron man

    17 เคล็ดวิชา เก่ง Excel แบบเร็วจี๋ 257
    • Dashboard คือ การแสดงผลข้อมูลโดยเอากราฟและข้อมูลตัวเลขสำคัญๆ มาแสดงรวมกันในหน้าเดียว
    • สำคัญที่สุดคือการออกแบบ Dashboard โดยคำนึงถึงคนใช้งานเป็นสำคัญ ว่าเค้าต้องการเห็นอะไร?
    • เทคนิคที่สำคัญในการทำ Dashboard คือ
      • การเขียนสูตร : คำนวณค่าต่างๆ ให้แสดงได้ตามต้องการ
      • PivotTable/ PivotChart : แสดงข้อมูลสรุป
      • Slicer : Slicer 1 ตัวจะควบคุม PivotTable/PivotChart ได้หลายๆ อันพร้อมกัน แบบ Interactive
      • Sparkline : สร้าง In-cell- Chart แบบง่ายๆ เพื่อเหมาะกับการแสดงผล
      • Conditional Format : ปรับ Format เพื่อเน้นสิ่งที่ต้องการนำเสนอ แบบ Interactive เช่น ทำไฟเขียวไฟแดง,  เปลี่ยนสีพื้นหลัง, เปลี่ยนสี/รูปแบบตัวอักษร
      • Linked Picture : สามารถดึง “สิ่งที่เราเห็น” จาก Range ในที่ต่างๆ มาแสดงในอีกที่ได้ แถมย่อ/ขยาย และวางตำแหน่งได้ดั่งใจ ถ้าข้อมูลต้นทางเปลี่ยน ปลายทางก็เปลี่ยนตาม เหมือนกับการตั้งกล้องถ่ายทอดสดไว้
      • Form Control : ทำให้ Dashboard Interactive มากขึ้น เช่น ทำ Scroll Bar / Radio Button แล้วให้ส่งค่าไปยัง Cell ที่กำหนด เพื่อผูกสูตรในการแสดงผลอีกที
      • ActiveX Control & VBA (Advanced) : ไม่มีอะไรที่ VBA ทำไม่ได้ แต่ต้องเรียนรู้ค่อนข้างเยอะ
    • จะเห็นว่ากว่าจะทำ Dashboard เจ๋งๆ ได้ต้องใช้ทักษะ Excel มากมายเลยทีเดียว นี่แหละเป็นเป้าหมายที่ท้าทาย

    เคล็ดวิชา 17: อย่าหยุดพัฒนาตัวเอง

    • จงมุ่งมั่นหาวิธีที่ดีขึ้นเสมอๆ มันอาจมีวิธีที่ดีกว่าวิธีที่คุณใช้อยู่ แค่คุณยังไม่รู้ และยังไม่ลองค้นหาเท่านั้นเอง!!
    • ตัวอย่างเช่น การ Map ข้อมูล จริงๆ แล้วทำได้หลายวิธีมากๆ ตั้งแต่วิธีที่แย่ จนไปถึงวิธีที่ดี
      • วิธีที่ถึกที่สุดคือดูด้วยตา กรอกด้วยมือ (ทั้งเหนื่อยและอาจผิดพลาดได้ง่ายๆ)
      • แทนที่จะดูด้วยตา กรอกด้วยมือ ก็ใช้สูตร เช่น IF หรือ VLOOKUP แทน
      • แทนที่จะใช้ IF หลายๆ ตัวซ้อนกัน ดูสิว่าใช้ VLOOKUP แทนได้หรือไม่
      • กรณีต้อง VLOOKUP ข้อมูลบรรทัดเดียวกันหลายรอบ ใช้ INDEX + ตำแหน่งแถวที่หามาแล้ว จะเร็วกว่าเยอะ
      • ใช้ VLOOKUP Approximate Match + IF แทน VLOOKUP แบบ Exact Match กรณีที่ข้อมูลเยอะมากๆ จะคำนวณเร็วขึ้น 100-1000 เท่า!
      • ใช้ INDEX + VLOOKUP Approximate Match แทน 2 วิธีข้างบน ก็จะยิ่งเร็วขึ้นไปอีก !!
      • ถ้าเรารู้จักใช้พวก Power Tool เช่น Power Query ในการ Map ข้อมูล จะยิ่งเร็วแถมง่ายด้วย !!!
    • จงเชื่อมั่นและเปิดใจเรียนรู้ เพราะ การเรียนรู้มันไม่สิ้นสุดจริงๆ ครับ ผมเองก็ยังไม่เรื่องที่ไม่รู้อีกมากมาย
    • หากคุณรู้อะไรเจ๋งๆ อย่าลืมมาแบ่งปันความรู้ให้คนอื่นได้รู้ด้วยนะครับ รับรองว่า “ยิ่งให้ยิ่งได้” จริงๆ ครับ