วันนี้ผมจะมาสอนเทคนิค Excel โดยใช้เทคนิคต่างๆ ที่ ใช้ Gimmick เรื่อง Infinity Stone ทั้ง 6 ที่แต่ละอันมีความสามารถพิเศษแตกต่างกัน จากหนังเรื่อง Avengers กำลังมาแรงมากๆ และกำลังกวาดรายได้อย่างถล่มทลาย
แต่เพื่อไม่ให้เป็นการสปอยล์คนที่ยังไม่ได้ดู ผมจะยังไม่พูดถึงเนื้อหาในภาค End Game แต่จะขอนำ มาผสมผสานเพื่อนำเสนอความรู้ Excel แบบสนุกสนาน เพื่อให้คนที่ยังไม่ได้ดู Avengers ภาค End Game ก็สามารถอ่านได้ครับ
เพื่อไม่ให้เสียเวลา เราไปดูกันเลย!!
Space Stone = Go to
ความสามารถในหนัง : พาผู้ใช้งานวาร์ปไปโผล่ที่ไหนก็ได้
ใน Excel เราก็มีเครื่องมือที่คล้ายกับเจ้า Space Stone นี้มากๆ เลย นั่นก็คือ เครื่องมือ Go to… ซึ่งมีความสามารถในการไปที่ Cell ไหนก็ได้นั่นเองครับ โดยเครื่องมือ Go to นี้สามารถเรียกใช้งานง่ายๆ ได้ด้วยการกดปุ่ม F5
พอกด F5 แล้ว เราสามารถพิมพ์ Cell reference ที่ต้องการจะวาร์ปไปได้ หรือจะเลือก Defined Name ที่ตั้งไว้แล้วก็ย่อมได้
แต่ความสามารถที่แท้จริงของ Go to นี้อยู่ในปุ่ม Special… ซึ่งมีลูกเล่นอีกมหาศาล เช่น เลือก Cell ที่มีค่า Blank, เลือก Cell ที่เป็น Text, เลือก Cell ที่มีค่าต่างจากช่องอื่น และอีกมากมาย
โดยเราสามารถนำไปประยุกต์ได้หลายเรื่อง เช่น เติมเต็มข้อมูลในช่องว่างให้เหมือนค่าข้างบน เป็นต้น
ใครอยากรู้เพิ่มเติม อ่านรายละเอียดได้ที่นี่
Time Stone = Undo Redo
ความสามารถในหนัง : ควบคุมเวลา สามารถย้อนเวลาไปอดีต หรือ ไปอนาคตได้
ใน Excel เราก็มีเครื่องมือที่ทำตัวคล้ายๆ กับ Time Stone อยู่เหมือนกันนะ นั่นก็คือ เครื่องมือ Undo, Redo นั่นเอง
เวลาเราทำอะไรผิดพลาดแล้วอยากจะย้อนกลับไปแก้ไข เราสามารถกด Undo หรือ Ctrl+z ได้ และถ้าหากเราย้อนเกิน เราก็กด Redo (Ctrl+y หรือ F4) เพื่อทำอีกทีได้ และถ้า Redo ไปเรื่อยๆ เราก็สามารถทำ Action นั้นซ้ำ แม้ว่าไม่เคย undo มาก่อน ซึ่งเรียกว่า Repeat นั่นเอง
Tips : ปกติแล้ว Excel จะตั้งค่าให้ Undo ได้ถึง 100 ครั้ง และเราสามารถเพิ่มค่านี้ได้ด้วยการไปแก้ Registry ซึ่งใครสนใจลองไป Google ดูได้ แต่ผมว่าไม่ได้จำเป็นมากนะ
สิ่งสำคัญกว่าคือ การต้องรู้ว่า เราไม่สามารถ Undo ย้อนเวลาไปได้ทุกเรื่องนะครับ เช่น Action ที่เกิดจากคำสั่ง File เช่น การ Save เนี่ย Save แล้วเราจะ Undo ไปก่อน Save ไม่ได้ นอกจากนี้คำสั่งจากพวก Macro หรือ VBA ก็ไม่สามารถ Undo ได้ ดังนั้นจะทำอะไรก็อย่าลืม Save ไฟล์ Backup ไว้ดีๆ นะครับ
Reality Stone = เปลี่ยน Format
ความสามารถในหนัง : เปลี่ยนแปลงความจริงได้
สิ่งที่ดูคล้ายกับเจ้า Reality Stone ใน Excel ก็คือ เรื่องของการเปลี่ยน Format ให้กับ Cell ต่างๆ นั่นเอง
ใน Cell หนึ่งๆ ของ Excel นั้น จริงๆ แล้วมีส่วนประกอบอยู่สองส่วนด้วยกัน นั่นคือ Content (เนื้อหา) กับ Format (รูปแบบการแสดงผล) ซึ่งสำหรับผมแล้ว Content คือ ความจริง ส่วน Format เป็นสิ่งที่สามารถทำให้เราเห็นสิ่งต่างๆ เปลี่ยนไปจากความจริงได้
ตัวอย่าง
ถ้าเราใส่ค่าจริงๆ ลงไปใน Cell ว่า 3.5 แต่ลองเปลี่ยน Format ให้ไม่มีทศนิยม เราก็จะเห็นเป็นเลข 4 ทั้งๆ ที่จริงๆ ค่ามันยังเป็น 3.5 อยู่ (รายละเอียดลองดูคลิปข้างล่าง)
ค่าวันที่ที่เราเห็นเป็น 28/4/2019 จริงๆ แล้วถูกเก็บค่าเป็นตัวเลขธรรมดา เช่น 43583 (Excel จะเก็บค่า 1/1/1900 เป็น เลข 1 รายละเอียดอ่านได้ที่นี่)
Power Stone = Power Tools
ความสามารถในหนัง : เพิ่มพลังให้ผู้ใช้งานแข็งแกร่งมากขึ้น
ใน Excel เราก็มีเจ้า Power Stone เหมือนกัน และชื่อของมันก็คล้ายกันด้วย นั่นก็คือ เครื่องมือ Power Tools ต่างๆ ไม่ว่าจะเป็น
- Power Query : มีความสามารถในการรวบรวมข้อมูลจาก Data Source หลายๆ รูปแบบได้ อีกทั้งยังดัดแปลงข้อมูลจากข้อมูลที่อยู่ในรูปแบบเน่าๆ ให้กลายเป็นรูปแบบที่เหมาะสมต่อการเอาไปทำงานต่อ เช่น PivotTable ได้
- Power Pivot : มีความสามารถในการวิเคราะห์ข้อมูลได้เหนือกว่า PivotTable ธรรมดา เนื่องจากมันสามารถใช้ Data Model มาสร้าง Relationship เพื่อผูกความสัมพันธ์ให้ตารางข้อมูลของเราได้ อีกทั้งยังมีภาษา DAX Formula มาสร้าง Calculated Column และ Measure ซึ่งมีฟังก์ชันที่ Excel ปกติไม่มี แต่ใช้ง่ายกว่า Excel มาก เช่น ใน Excel การเขียน VLOOKUP ที่ต้องใช้ Input 4 ตัวกว่าจะเขียนจบ แต่ใน DAX มีคำสั่ง RELATED ที่ทำงานเหมือน VLOOKUP เลย แต่ใช้ Input แค่ตัวเดียว
- Power BI : เป็นโปรแกรม Business Intelligent (BI) ซึ่งแยกออกมาจาก Excel โดยสามารถโหลดใช้ฟรีได้เลยสำหรับ version desktop ซึ่งมีความสามารถของ Power Query และ Power Pivot อยู่ด้วยกัน แถมยังสามารถสร้างกราฟและ Dashboard แบบ Interactive ได้ง่ายกว่า Excel มาก อีกด้วย
ใครที่สนใจ สามารถอ่านรายละเอียดของเจ้า Power Tools ทั้งหลายนี้ได้ที่นี่
ในบทความจะมีคลิปวีดีโอสอนด้วยครับ เช่น
Mind Stone = VBA
ความสามารถในหนัง : ควบคุมจิตใจของผู้อื่นให้ทำตามสิ่งที่เราต้องการได้
ใน Excel ผมมองว่า เครื่องมือที่ทำสิ่งที่คล้ายกับ Mind Stone ทำได้ ก็คือการเขียนโปรแกรม VBA นั่นเอง
ใน Excel มีหลายครั้งที่เราต้องทำงานถึกๆ แบบมี Pattern ซ้ำๆ กันอยู่ จะดีกว่ามั้ยถ้าเราสามารถสั่งให้ Excel ทำงานแทนเราได้?
คนที่เขียนโปรแกรมไม่เป็นเลย ก็ไม่ต้องกลัว ทุกคนสามารถเริ่มต้นเรียนรู้ได้ครับ และโชคดีมากๆ ที่ Excel มีเครื่องมือ Macro Recorder ที่สามารถ Record หรือบันทึกการกระทำของเราแล้วแปลงให้กลายเป็น VBA Code ได้โดยอัตโนม้ติ และถ้าเราเรียนรู้ VBA อีกเล็กน้อย เราก็สามารถดัดแปลง Code ที่อัดไว้แล้วให้กลายเป็นโปรแกรมที่ฉลาดขึ้นได้
ซึ่งใครที่สนใจรายละเอียดเพิ่มเติม สามารถอ่านบทความสองอันนี้ได้ครับ
ในบทความจะมีคลิปวีดีโอสอนด้วย เช่น
Soul Stone = Array Formula
ความสามารถในหนัง : ควบคุมและกักเก็บวิญญาณได้
มณีเม็ดนี้ในหนังมีความพิเศษมากกว่าก้อนอื่นมาก และความสามารถก็ไม่ค่อยชัดเจนมากนัก แต่เท่าที่อ่านดูในเว็บเหมือนจะม่ีความสามารถในการสร้างโลกจำลองแล้วกักเก็บวิญญาณไว้ในนั้นได้
สำหรับผมแล้ว ใน Excel เราก็สามารถสร้างโลกจำลองขึ้นมาได้เช่นกัน และในโลกจำลองนั้นเราสามารถเก็บข้อมูลหลายๆ อันลงไปได้ ซึ่ง การเก็บข้อมูลเป็นชุดลงไปในสิ่งเดียว ใน Excel เรียกว่า Array นั่นเอง (แถสุดๆ 555)
เวลาที่เราเขียนสูตรที่รองรับข้อมูลแบบ Array หรือที่เรียกว่า Array Formula นั้น ปกติเราจะต้องกดปุ่มพิเศษ คือ Ctrl+Shift+Enter แทนที่จะกด Enter ธรรมดาเหมือนสูตรปกติ
สมมติว่าเราต้องการหาราคาที่มากที่สุดของสินค้าที่ชื่อขึ้นต้นด้วยตัว T จากข้อมูลต่อไปนี้ เราก็สามารถทำได้โดยเขียนสูตรทุกอย่างอยู่ในช่องเดียว (เหมือนสร้างโลกจำลองขึ้นมาในช่องเดียว)
เราก็สามารถเขียน LEFT เอาตัวแรกออกมาเทียบว่าเป็นตัว T รึเปล่า? แล้วใช้ IF เช็คว่าถ้าใช่ ก็ให้ดูค่าราคา (ถ้าไม่ใช่ ก็ปล่อยเป็นเป็น FALSE)
จากนั้นก็เอา MAX มาครอบเพื่อหาค่าสูงสุดได้เลยในช่องเดียวด้วย Array Formula โดยเขียนสูตรว่า
=MAX(IF(LEFT(A2:A7,1)=”T”,B2:B7)) แล้วกด Ctrl+Shift+Enter ครับ
ซึ่งถ้าค่อยๆ ลอง Highlight สิ่งที่สนใจแล้วลองกด F9 ดูจะเห็นว่ามันเก็บข้อมูลไว้มากมายในโลกของ Array ครับ
รวบรวม Infinity Stone ครบ 6 ชิ้นแล้ว ก็ดีดนิ้วได้!!
ความสามารถในหนัง : สามารถทำให้สิ่งมีชีวิตครึ่งจักรวาลหายไปในแบบสุ่ม
สำหรับ Excel แล้ว ความสามารถในการสุ่มข้อมูลสามารถทำได้ด้วยฟังก์ชัน RAND หรือ RANDBETWEEN ก็ได้ครับ ซึ่งทั้งสองฟังก์ชันนี้จะสุ่มตัวเลขออกมาด้วยการแจกแจกแบบ Uniform Distribution หรือว่าทุกค่ามีโอกาสเท่ากันหมดนั่นเอง แต่สองตัวนี้จะต่างกันดังนี้
- =RAND() : จะทำการสุ่มค่าตั้งแต่ 0 ถึง 1 ออกมาเป็นทศนิยม
- =RANDBETWEEN(bottom,top) : จะสุ่มตัวเลขจำนวนเต็มระหว่างค่า Bottom กับ Top ที่กำหนด
วิธีที่ 1
ดังนั้นถ้าเรามีข้อมูลอยู่ชุดหนึ่ง แล้วอยากจะสุ่มว่าข้อมูลไหนจะได้อยู่ต่อ ข้อมูลไหนจะต้องหายไป บางทีเราก็สามารถเขียนสูตรง่ายๆ ได้ดังนี้
=CHOOSE(RANDBETWEEN(1,2),”อยู่ต่อ”,”หายไป”)
ซึ่งสูตรดังกล่าวจะสุ่มเลขระหว่าง 1 กับ 2 ออกมาด้วย RANDBETWEEN (ความน่าจะเป็นครึ่งๆ ระหว่าง 1 กับ 2) แล้วใช้ CHOOSE เลือกว่าถ้า 1 ให้อยู่ต่อ ถ้า 2 ให้หายไป
ซึ่งจะเห็นว่า ผลลัพธ์สุดท้ายถ้าดูที่จำนวนคนที่เหลือรอดอยู่มันไม่ได้เป็นครึ่งนึงจริงๆ เพราะมันขึ้นอยู่กับการ Random แต่ละครั้งโดยแต่ละ Cell จะไม่ได้มีความสัมพันธ์อะไรกันเลยว่าถ้า Cell ก่อนหน้ารอดไปแล้ว อีก Cell จะมีโอกาสรอดน้อยลง
วิธีที่ 2
แต่ถ้าอยากได้จำนวนครึ่งนึงที่หายไปแน่ๆ เลย ก็อาจต้อง RANK ข้อมูลผสมลงไปด้วย แล้วกำหนดเลยว่าให้รอดแค่ครึ่งเดียว โดยต้องทำการนับข้อมูลก่อนว่าตอนแรกทั้งหมดมีกี่คน?
ด้วยวิธีแบบนี้จะการันตีได้ว่า จะรอดแค่ครึ่งเดียวจริงๆ ครับ
=IF(RANK.AVG([@[เลขสุ่มด้วย RAND]],[เลขสุ่มด้วย RAND])<=(COUNTA([สิ่งมีชีวิต])/2),”อยู่ต่อ”,”หายไป”)
และถ้าใครอยากให้ข้อมูลมันดูเหมือนหายไปจริงๆ อาจใช้ Conditional Format เขียนเพิ่ม โดยใส่ Custom Number Format เป็น ;;; (; 3 ตัว) ก็ได้ครับ เพื่อให้มองไม่เห็นค่านั้นๆ
พลังของ Infinity Stone แห่ง Excel อยู่ในมือคุณแล้ว
ยิ่งเราเชี่ยวชาญแต่ละหัวข้อมากเท่าไหร่ เราก็จะยิ่งสามารถนำไปพลิกแพลง ต่อยอดเรื่องต่างๆ ได้มากเท่านั้นครับ หวังว่าเพื่อนๆ เองจะรวบรวมความรู้เกี่ยวกับ Infinity Stone ใน Excel แต่ละเม็ด เพื่อปลดปล่อยพลังสูงสุดของ Excel เพื่อนๆ ออกมาให้ได้นะครับ
ไว้ Avengers : End Game ออกจากโรงเมื่อไหร่ ไว้ผมจะมาเขียนบทความถึง End Game อีกทีครับ ซึ่งรับรองว่ามีเรื่องเอามาประยุกต์ได้อีกเพียบ!!
