สอน Excel ผ่าน Avengers : พลัง Infinity Stone ทั้ง 6 ใน Excel (ไม่สปอยล์ End Game)

สอน Excel ผ่าน Avengers : พลัง Infinity Stone ทั้ง 6 ใน Excel (ไม่สปอยล์ End Game) 1
เจาะลึกเครื่องมือ Go to ใน Excel
วิธีตรวจสอบรุ่นของโปรแกรม Excel (Excel Version) ของคุณ

วันนี้ผมจะมาสอนเทคนิค Excel โดยใช้เทคนิคต่างๆ ที่ ใช้ Gimmick เรื่อง Infinity Stone ทั้ง 6 ที่แต่ละอันมีความสามารถพิเศษแตกต่างกัน จากหนังเรื่อง Avengers กำลังมาแรงมากๆ และกำลังกวาดรายได้อย่างถล่มทลาย

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

เพื่อไม่ให้เสียเวลา เราไปดูกันเลย!!

สอน Excel ผ่าน Avengers : พลัง Infinity Stone ทั้ง 6 ใน Excel (ไม่สปอยล์ End Game) 2 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 ที่มีค่าต่างจากช่องอื่น และอีกมากมาย

โดยเราสามารถนำไปประยุกต์ได้หลายเรื่อง เช่น เติมเต็มข้อมูลในช่องว่างให้เหมือนค่าข้างบน เป็นต้น

ใครอยากรู้เพิ่มเติม อ่านรายละเอียดได้ที่นี่

สอน Excel ผ่าน Avengers : พลัง Infinity Stone ทั้ง 6 ใน Excel (ไม่สปอยล์ End Game) 3 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 ไว้ดีๆ นะครับ

สอน Excel ผ่าน Avengers : พลัง Infinity Stone ทั้ง 6 ใน Excel (ไม่สปอยล์ End Game) 4 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 รายละเอียดอ่านได้ที่นี่)

สอน Excel ผ่าน Avengers : พลัง Infinity Stone ทั้ง 6 ใน Excel (ไม่สปอยล์ End Game) 5 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 ทั้งหลายนี้ได้ที่นี่

ในบทความจะมีคลิปวีดีโอสอนด้วยครับ เช่น

สอน Excel ผ่าน Avengers : พลัง Infinity Stone ทั้ง 6 ใน Excel (ไม่สปอยล์ End Game) 6 Mind Stone = VBA

ความสามารถในหนัง : ควบคุมจิตใจของผู้อื่นให้ทำตามสิ่งที่เราต้องการได้

ใน Excel ผมมองว่า เครื่องมือที่ทำสิ่งที่คล้ายกับ Mind Stone ทำได้ ก็คือการเขียนโปรแกรม VBA นั่นเอง

ใน Excel มีหลายครั้งที่เราต้องทำงานถึกๆ แบบมี Pattern ซ้ำๆ กันอยู่ จะดีกว่ามั้ยถ้าเราสามารถสั่งให้ Excel ทำงานแทนเราได้?

คนที่เขียนโปรแกรมไม่เป็นเลย ก็ไม่ต้องกลัว ทุกคนสามารถเริ่มต้นเรียนรู้ได้ครับ และโชคดีมากๆ ที่ Excel มีเครื่องมือ Macro Recorder ที่สามารถ Record หรือบันทึกการกระทำของเราแล้วแปลงให้กลายเป็น VBA Code ได้โดยอัตโนม้ติ และถ้าเราเรียนรู้ VBA อีกเล็กน้อย เราก็สามารถดัดแปลง Code ที่อัดไว้แล้วให้กลายเป็นโปรแกรมที่ฉลาดขึ้นได้

ซึ่งใครที่สนใจรายละเอียดเพิ่มเติม สามารถอ่านบทความสองอันนี้ได้ครับ

ในบทความจะมีคลิปวีดีโอสอนด้วย เช่น

สอน Excel ผ่าน Avengers : พลัง Infinity Stone ทั้ง 6 ใน Excel (ไม่สปอยล์ End Game) 7 Soul Stone = Array Formula

ความสามารถในหนัง : ควบคุมและกักเก็บวิญญาณได้

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

สำหรับผมแล้ว ใน Excel เราก็สามารถสร้างโลกจำลองขึ้นมาได้เช่นกัน และในโลกจำลองนั้นเราสามารถเก็บข้อมูลหลายๆ อันลงไปได้ ซึ่ง การเก็บข้อมูลเป็นชุดลงไปในสิ่งเดียว ใน Excel เรียกว่า Array นั่นเอง (แถสุดๆ 555)

เวลาที่เราเขียนสูตรที่รองรับข้อมูลแบบ Array หรือที่เรียกว่า Array Formula นั้น ปกติเราจะต้องกดปุ่มพิเศษ คือ Ctrl+Shift+Enter แทนที่จะกด Enter ธรรมดาเหมือนสูตรปกติ

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

สอน Excel ผ่าน Avengers : พลัง Infinity Stone ทั้ง 6 ใน Excel (ไม่สปอยล์ End Game) 8

เราก็สามารถเขียน LEFT เอาตัวแรกออกมาเทียบว่าเป็นตัว T รึเปล่า? แล้วใช้ IF เช็คว่าถ้าใช่ ก็ให้ดูค่าราคา (ถ้าไม่ใช่ ก็ปล่อยเป็นเป็น FALSE)
จากนั้นก็เอา MAX มาครอบเพื่อหาค่าสูงสุดได้เลยในช่องเดียวด้วย Array Formula โดยเขียนสูตรว่า
=MAX(IF(LEFT(A2:A7,1)=”T”,B2:B7)) แล้วกด Ctrl+Shift+Enter ครับ

ซึ่งถ้าค่อยๆ ลอง Highlight สิ่งที่สนใจแล้วลองกด F9 ดูจะเห็นว่ามันเก็บข้อมูลไว้มากมายในโลกของ Array ครับ

สอน Excel ผ่าน Avengers : พลัง Infinity Stone ทั้ง 6 ใน Excel (ไม่สปอยล์ End Game) 13
รวบรวม 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 จะมีโอกาสรอดน้อยลง

สอน Excel ผ่าน Avengers : พลัง Infinity Stone ทั้ง 6 ใน Excel (ไม่สปอยล์ End Game) 14

วิธีที่ 2

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

=IF(RANK.AVG([@[เลขสุ่มด้วย RAND]],[เลขสุ่มด้วย RAND])<=(COUNTA([สิ่งมีชีวิต])/2),”อยู่ต่อ”,”หายไป”)

สอน Excel ผ่าน Avengers : พลัง Infinity Stone ทั้ง 6 ใน Excel (ไม่สปอยล์ End Game) 15

และถ้าใครอยากให้ข้อมูลมันดูเหมือนหายไปจริงๆ อาจใช้ Conditional Format เขียนเพิ่ม โดยใส่ Custom Number Format เป็น ;;; (; 3 ตัว) ก็ได้ครับ เพื่อให้มองไม่เห็นค่านั้นๆ

สอน Excel ผ่าน Avengers : พลัง Infinity Stone ทั้ง 6 ใน Excel (ไม่สปอยล์ End Game) 16
สอน Excel ผ่าน Avengers : พลัง Infinity Stone ทั้ง 6 ใน Excel (ไม่สปอยล์ End Game) 17
สอน Excel ผ่าน Avengers : พลัง Infinity Stone ทั้ง 6 ใน Excel (ไม่สปอยล์ End Game) 18

พลังของ Infinity Stone แห่ง Excel อยู่ในมือคุณแล้ว

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

ไว้ Avengers : End Game ออกจากโรงเมื่อไหร่ ไว้ผมจะมาเขียนบทความถึง End Game อีกทีครับ ซึ่งรับรองว่ามีเรื่องเอามาประยุกต์ได้อีกเพียบ!!

........

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

อย่าลืมกดติดตามเทพเอ็กเซลได้ที่ Facebook.com/ThepExcel
และสามารถติดตามคลิปวีดีโอเจ๋งๆ ได้ที่ YouTube Channel ของ Thep Excel ครับ


เจาะลึกเครื่องมือ Go to ใน Excel
วิธีตรวจสอบรุ่นของโปรแกรม Excel (Excel Version) ของคุณ
  • 556
  •  
  •  
  •  
  •  

Posted on: April 28, 2019
Tags: , , , , , , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *