แฉ 10 ความลับของ Excel ที่คุณอาจยังไม่เคยรู้มาก่อน!

Excel นั้นยิ่งใช้ ยิ่งศึกษา ยิ่งพบความน่าพิศวง… เพราะมันมีอะไรหลายอย่างมากๆ ที่ถูกเก็บซ่อนเอาไว้ หรือ ไม่ได้แสดงให้เห็นอย่างเด่นชัดนัก วันนี้ผมจะขอมาแฉ 10 ความลับของ Excel ที่คุณอาจยังไม่เคยรู้มาก่อน! เอาให้เพื่อนๆ ของคุณงงไปเลยว่าคุณรู้เรื่องพวกนี้ได้ยังไง

1. ใช้ Space เป็นเครื่องหมายเชื่อม Cell Reference ก็ได้

เพื่อนๆ คงรู้จักตัวเชื่อม Cell Reference  อย่าง colon (:) ที่ใช้เชื่อมข้อมูลเป็นช่วง หรือตัว comma (,) ที่ใช้เชื่อม Cell ที่ไม่ต่อเนื่องกัน เป็นอย่างดีอยู่แล้ว แต่ผมพนันเลยว่า หลายๆ คนคงไม่รู้จักตัวเชื่อมที่เป็นช่องว่าง (space) แน่นอน

ถ้าเปรียบ comma (,) เป็นตัวเชื่อมในวิชาตรรกศาสตร์หรือเซ็ตแล้ว มันจะคล้ายเครื่องหมาย union เพราะเป็นการเชื่อม Range หลายๆ อันเข้าด้วยกัน แต่เจ้าตัวเชื่อมที่เป็นช่องว่าง (space) นั้น ทำหน้าที่เป็นเครื่องหมาย intersect นั่นคือจะให้ผลลัพธ์ที่เป็นช่องที่ซ้อนทับกัน ของ Range ที่เราเลือกไว้นั่นเอง (Intersection)

ตัวอย่างเช่น =B1:C15 A7:E8 (มี space คั่น) จะให้ผลเป็น Range B7:C8 ที่ระบายสีตามรูปนั่นเอง (อาจจะเอาไปใช้ต่อได้ เช่น SUM เป็นต้น)

2. อ้างอิงข้อมูลจากหลายชีทด้วย 3D Reference

รู้หรือไม่ว่าเราสามารถอ้างอิง Cell Reference แบบ 3 มิติได้ นั่นคือ เป็นการอ้างอิง Cell Reference แบบทะลุข้าม Worksheet นั่นเอง ทำยังไงมาดูกันครับ

แทนที่จะใส่การอ้างอิงไปที่ชีทเดี่ยวๆ ตามการอ้างอิงปกติ (เช่น =Sheet1!B2 ) เราสามารถใส่ชื่อชีทแบบเป็นช่วงได้
โดยใส่ชีทที่เป็นจุดเริ่มต้น ตามด้วยเครื่องหมาย colon (:) และ ตามด้วยชีทที่เป็นจุดสิ้นสุดลงไปได้เลย เช่น

=SUM(Sheet1:Sheet3!B2:D4)

เพียงเท่านี้ มันก็จะทำการบวกข้อมูลช่อง B2:D4 เริ่มจาก Sheet1 ไปจนถึง Sheet3 นั่นคือจะรวม Sheet1, Sheet3 และ Sheet ทุกอันที่คั่นอยู่ระกว่างทั้งสอง Sheet ด้วย! คำว่า “Sheet ทุกอันที่คั่นอยู่ระกว่างทั้งสอง Sheet” หมายความว่า หากในอนาคตเรามีการเพิ่ม Sheet แล้วย้าย Sheet นั้นมาอยู่ระหว่าง Sheet 1 กับ 3 นี้ มันก็จะถูกบวกเข้าไปในผลรวมไปด้วย!!

ฟังก์ชั่นที่สามารถใช้ 3D-Reference ได้ ดูได้ที่นี่ =>
http://office.microsoft.com/en-001/excel-help/create-a-3-d-reference-to-the-same-cell-range-on-multiple-worksheets-HP010102346.aspx

3. Paste เป็น Value กับ Format พร้อมกันได้ง่ายๆ

หากเราต้องการ Copy ข้อมูลที่เป็นสูตรเพื่อที่จะมา Paste ลงอีกที่นึงเป็น Value กับ Format พร้อมกัน ปกติเราจะทำได้อย่างมากคือ Paste เป็น Value ทีนึง และ Paste Format อีกทีนึง ไม่สามารถทำพร้อมกันได้ และบางทีก็ชอบมีปัญหาด้วย โดยเฉพาะเวลามี Cell ที่ Merge กันอยู่จะทำให้ Paste ไม่ลง

ผมมีเทคนิคเจ๋งๆ มาแนะนำให้ครับ นั่นก็คือ ให้เปิดโปรแกรม Excel อีกอันนึงขึ้นมา (ให้เหมือนเราเข้าโปรแกรม Excel ใหม่อีกรอบ) แล้วสร้างไฟล์ใหม่หรือเปิดไฟล์งานที่ต้องการ Paste ห้ามกด New Workbook หรือ Open Workbook จากโปรแกรม Excel เดิม) จากนั้นค่อยกด Copy จากไฟล์ต้นฉบับ แล้ว Paste ลงไปที่อีกไฟล์นึงไปตรงๆ ไม่ต้องมี Special อะไรทั้งสิ้น เพียงเท่านี้มันจะเป็นการ Paste เป็น Value กับ Format พร้อมกันโดยอัตโนมัติ

การทำแบบนี้มีข้อเสียคือ เราจะไม่สามารถ Link หรืออ้างอิงสูตรข้ามไฟล์ในลักษณะนี้ได้ ซึ่งต่างจากการเปิด Workbook ใหม่ หรือ Open File จาก Excel โปรแกรมเดิม

4. สูตร INDEX แท้จริงแล้วให้ค่ากลับมาเป็น Cell Reference

รู้หรือไม่ว่าสูตร INDEX นั้นให้ผลลัพธ์แตกต่างจากสูตรอื่นๆ ในหมวด Lookup & Reference อย่าง VLOOKUP (ที่ให้ค่ากลับมาเป็น Value ในช่องที่กำหนด) หรือ MATCH  (ที่ให้ค่ากลับมาเป็นเลขลำดับที่หาเจอ) เพราะว่าสิ่งที่ INDEX ให้ค่ากลับมานั้นเป็น Cell Reference ต่างหาก

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

นั่นคือ เราสามารถเอา INDEX ไปใช้ผสมกับฟังก์ชั่นอื่นอย่าง OFFSET ก็ได้ (ซึ่งคุณไม่สามารถใช้ OFFSET ร่วมกับ VLOOKUP ได้เลย) หรือ จะเอาไปแทนที่ตรงส่วนของสูตรที่ต้องการค่าเป็น Cell Reference ได้เลยทันที เช่นเอาไปใช้กับฟังก์ชั่น SUM หรือ AVERAGE

ตัวอย่างเช่น ผมสามารถ ทำ Dynamic Range ก็ได้ (คือให้ Excel เลือกช่วงที่มีการเปลี่ยนไปแล้วแต่ input ของ user)  โดยหลักการที่ว่า

• ใช้ MATCH คู่กับ INDEX เพื่อหาตำแหน่งสุดท้ายที่ต้องการเลือก
• จากนั้นเอาไปผสมกับฟังก์ชั่นอย่าง AVERAGE ที่กำหนดจุดเริ่มที่ชัดเจนไว้
• ส่วนจุดจบให้แทนสิ่งที่ได้จาก INDEX
• เช่น
  =AVERAGE(cellจุดเริ่ม:cellสุดท้ายที่ได้จาก INDEX)
  =AVERAGE(cellจุดเริ่ม:INDEX(ตารางอ้างอิง,แถวที่เป็นจุดจบซึ่งได้จาก MATCH))
• ลองดูตัวอย่างได้ที่นี่ เขียนไว้ค่อนข้างดีครับ => http://datapigtechnologies.com/blog/index.php/using-indexmatch-as-a-cell-reference/
• ดูสูตรและเทคนิคลับอื่นของ INDEX ได้ที่นี่ => http://www.inwexcel.com/index-function/

5. Excel พูดได้

เราสามารถสั่งให้ Excel พูดโดยใช้เครื่องมือ Speak Cells กับ Speak Cells on Enter (ใน menu commands not in a ribbon) ซึ่งสามารถ add ได้จาก Quick Access Toolbar ซึ่งประโยชน์ของมันคือการช่วยทวนสิ่งที่เราพิมพ์เข้าไปเพื่อลดความผิดพลาด โดยไม่ต้องเสียเวลามองสิ่งที่เราพิมพ์นั่นเอง

ใครสนใจวิธีทำไปอ่านได้ที่นี่ => http://www.inwexcel.com/quick-access-toolbar/

6. เปลี่ยนรูปแบบของตัวอักษรที่อยู่ประจำหัวคอลัมน์ หรือ หัวแถวได้

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

วิธีแก้ Normal Style ให้ทำดังนี้

1. ไปที่ Ribbon Home
2. Cell Styles => คลิ๊กขวาที่ Normal => เลือก Modify
3. กด Format… => เลือกรูปแบบที่ต้องการ
4. กด ok และ ok อีกที

ซึ่งเจ้า Normal Style ที่เราเปลี่ยนเล่นนี้จะผูกติดไปตาม Workbook ที่เราเปลี่ยน Style ไป ดังนั้นเวลาเปิดไฟล์ใหม่เจ้า Normal Style ก็จะกลายเป็นปกติเองครับ

7. Excel มีฟังก์ชั่นลึกลับเจ๋งๆ อย่าง DATEDIF แต่ไม่บอกคุณ

จริงๆ แล้ว Excel มีฟังก์ชั่นเจ๋งๆ อย่าง DATEDIF  แต่เค้าไม่ยอมบอกเรา (ไม่รู้ทำไมเหมือนกัน!!)
ที่ว่าไม่บอกก็คือ ไม่มีอยู่ใน List ฟังก์ชั่นที่มีให้เลือก แถมตอนพิมพ์ก็ไม่มี Tool Tips หรือ Help อะไรบอกเลยด้วย ทั้งๆ ที่มีประโยชน์พอสมควรเลยล่ะ

วิธีการใช้ดูได้ที่นี่ครับ => http://www.inwexcel.com/datedif-function/

8. Excel สามารถแปลงข้อมูลหลายๆ อย่างได้ง่ายมากๆ

• แปลงหน่วยการวัด : อยากแปลงปอนด์เป็นกิโลกรัม หรือ แปลง ไมล์เป็นกิโลเมตร ไม่ใช่เรื่องยาก เพียงใช้ฟังก์ชั่น CONVERT เราก็ไม่ต้องมานั่งจำตัวเลขแล้ว (ไปดูหน่วยที่ Excel Convert ได้ที่นี่ => http://office.microsoft.com/en-001/excel-help/convert-HP005209022.aspx?CTT=5&origin=HP003056127)
• แปลงเลขอารบิกเป็นเลขโรมัน : เราสามารถใช้ฟังก์ชั่น ROMAN ในการแปลงเลขอารบิกเป็นเลขโรมันได้ง่ายๆ อันนี้นอกจากจะเอาไว้ใช้เพื่อศึกษาก็ได้ว่าเลขจำนวนมากๆ ที่เราไม่คุ้น เวลาแปลงกลายเป็นเลขโรมันจะเขียนยังไง ยังสามารถแกล้งให้เพื่อนงงเล่น โดยแปลงเลขอารบิกเป็นเลขโรมันได้เลยครับ
• แปลงตัวเลขเป็นข้อความภาษาไทย : ไม่น่าเชื่อว่า Excel จะมีฟังก์ชั่นเพื่อคนไทยโดยเฉพาะอย่าง BAHTTEXT
  ในขณะที่กลับไม่มีฟังก์ชั่นแบบนี้สำหรับการแสดงเป็นข้อความภาษาอังกฤษ เล่นเอาฝรั่งน้อยใจกันไปเป็นแถบๆ

9. เราสามารถใส่เวลาให้แสดงข้อมูลเวลาที่มากกว่า 24 ชั่วโมงได้

เราสามารถพิมพ์เลขเวลาที่เกิน 24 ชั่วโมงลงไปใน Cell ได้เลย เช่น  32:30:15 ซึ่ง Excel จะทำการเลือก Custom Format ในรูปแบบที่รองรับการกรอกดังกล่าว คือมีการใช้วงเล็บก้ามปูครอบตัว h ลงไปอีกที เช่น  [h]:mm:ss นั่นเอง

เพราะเครื่องหมาย [ ] เวลาใส่ลงไปแล้วจะหมายถึงให้ใช้ค่าแบบที่เกินเวลาปกติได้ เช่น

• [h] จะเกิน 24 ชม. ได้
• [m] จะเกิน 60 นาทีได้
• [s] จะเกิน 60 วินาทีได้

อ่านเกี่ยวกับการใช้งาน Custom Format เพิ่มเติมได้ที่ http://www.inwexcel.com/custom-format/

10. มีวิธีการใส่สูตรแบบพิเศษ ที่ต้องกดใช้งานด้วยคำสั่ง Ctrl+Shift+Enter

บางครั้งหากคุณเจอเพื่อนเขียนสูตรมาแล้วมีเครื่องหมาย { } ครอบอยู่ด้วยก็อย่างงไป เพราะเค้าคนนั้นได้เขียนสูตรด้วยวิธีที่ที่เรียกว่า Array Formula ซึ่งเป็นวิธีการเขียนสูตรขั้นสูงซึ่งส่วนใหญ่แล้วจะต้องมีการกดคำสั่งพิเศษโดยใช้ปุ่ม 3 อันร่วมกันคือ Ctrl+Shift+Enter ถึงจะใช้ได้

Array Formula นี้จะทำงานต่างจากสูตรปกติตรงที่ ปกติแล้วการดำเนินการของสูตร จะทำกับ cell ทีละช่อง แต่เจ้า Array Formula นี้จะทำเป็นช่วงแทน

ยกตัวอย่างเช่น

หากเราอยากจะเอาเลขในช่อง A1:A10 ไปคูณกับเลขในช่อง B1:B10 จากนั้นให้เอาผลที่ได้มา AVERAGE กัน
เราสามารถทำแบบนี้ได้ด้วยการสูตร Array Formula เพียงช่องเดียว นั่นก็คือ =AVERAGE(A1:A10*B1:B10) ซึ่งพอเขียนจบให้กด Ctrl+Shift+Enter ด้วย สูตรจึงจะทำงานได้ (มันจะมีเครื่องหมายปีกกางอกมาเอง)

วิธีที่มันทำคือ จะจับคู่ทำงานที่ละคู่ลำดับ เช่น A1*B1,A2*B2,A3*B3….A10*B10 จากนั้นก็เอา AVERAGE ไปครอบ
ได้ว่า =AVERAGE(A1*B1,A2*B2,A3*B3….A10*B10) นั่นเอง

ใครสนใจ ในอนาคตอันใกล้นี้ เดี๋ยวผมจะสอนเขียน Array Formula แน่นอน แล้วจะรู้ว่ามันทำอะไรได้มากมายจริงๆ

ที่มาของข้อมูล : เว็บไซต์ inwexcel จาก http://www.inwexcel.com/10-excel-secrets/