รวม 10 เทคนิค Excel ง่ายๆ ที่ทำให้คุณทำงานเร็วขึ้น

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

1. ดูข้อมูลสรุปทางสถิติจากจาก Status Bar ด้านขวาล่าง

บางครั้งเราต้องการบวกเลข นับเลข หรือหาค่าเฉลี่ยเลขคณิต แบบเร็วๆ (โดยไม่ได้จะเอาไปคำนวณต่อ) เราไม่ต้องเขียนสูตรให้เมื่อย แค่ให้ลาก Select ครอบคลุมข้อมูลที่ต้องการ แล้วไปดูที่ Status Bar ที่อยู่ขวาล่าง มันก็แสดงข้อมูลให้เพียบแล้ว

status-bar-summary2

Tips : เราเลือกได้ว่าใน Status Bar จะแสดงการสรุปอะไรบ้างโดยให้ลองคลิ๊กขวาที่ Status Bar ด้านล่าง แล้วติ๊กเลือกเอาได้ครับ ว่าจะเอาอะไร เช่น Average, Count, Numerical Count, Maximum, Minimum, Sum

2. หัดใช้ Keyboard คีย์ลัดบ้าง

keep-calm-and-ctrl-c-ctrl-v-2

การใช้คีย์ลัดช่วยให้เราทำงานได้เร็วมากขึ้นจริงๆ ครับ โดยเฉพาะตัวไหนที่เราใช้บ่อยๆ ให้จำให้ได้เลย

สำหรับผมเอง เช่น

  • Ctrl+C : Copy
  • Ctrl+X : Cut
  • Ctrl+V : Paste
  • Ctrl + Z : Undo
  • Alt+ =  : Auto Sum
  • F2 : เข้าสู่ Edit Mode
  • F3 : Paste Name
  • F4 : ล๊อค Cell (ใน Edit Mode ) / Repeat ทำซ้ำ
  • F9 : แปลงสูตรเป็น Value (ใน Edit Mode) / Recalculate

สามารถไปดูสรุปคีย์ลัดที่สำคัญได้ที่ => http://www.inwexcel.com/microsoft-excel-shortcut-hotkey/ 

3. ใช้ Quick Access Toolbar

qat

นอกจากเราจะมี Ribbon มาตรฐานให้ใช้แล้ว เรายังสามารถ Add คำสั่งที่เราใช้บ่อยๆ ไว้ที Quick Access Toolbar ที่อยู่มุมบนซ้ายได้ด้วย รวมถึงสามารถใส่คำสั่งที่ไม่มีใน Ribbon ปกติได้อีกต่างหาก และที่สำคัญยังใช้ร่วมกับคีย์ลัด ปุ่ม Alt+1, Alt+2 …. (ไปเรื่อยๆ) ได้อีกด้วย

สามารถดูวิธีเพิ่มปุ่มลงใน Quick Access Toolbar ได้ที่ => http://www.inwexcel.com/quick-access-toolbar/

4. ใช้ Defined Name ในการอ้างอิงสูตร

defined-name

การตั้งชื่อให้ Cell หรือ Range มีข้อดีหลายอย่าง เช่น

  • อ่านสูตรเข้าใจได้ง่ายกว่า เพราะสามารถใส่เป็นคำที่มีความหมายได้
  • บริหารจัดการง่าย เวลาแก้ไขการอ้างอิง ก็สามารถแก้ได้ที่ Name Manager ที่เดียวเลย เหมาะอย่างยิ่งกับการอ้างอิงไปที่ช่องที่มีการอ้างถึงบ่อยๆ หรือ Range ที่อาจมีการเพิ่มเติม Item ภายหลัง เช่น  Reference Table 
  • เวลาใช้ VBA อ้างอิง หากอ้างไปที่ Defined Name จะสะดวกกว่าอ้างไปที่ชื่อ Cell เพราะเวลาเราแทรก Cell แล้ว การอ้างอิงใน VBA จะไม่เลื่อนตามให้ เหมือนการอ้างอิงใน Sheet ปกติ ซึ่งจะไม่เป็นปัญหากเลย หากเราใช้ Defined Name แทน

วิธีตั้งชื่อ Definced Name ให้ Cell หรือ Range

  1. เลือก Cell หรือ Range ที่ต้องการตั้งชื่อ
  2. ไปที่ Name Box ที่อยู่ซ้ายมือของช่องใส่สูตร
  3. พิมพ์ชื่อที่ต้องการแล้วกด Enter

วิธีบริหารจัดการชื่อ ให้ไปที่ Ribbon Formula => Name Manager (หรือกด Ctrl + F3)

5. สร้าง Custom List เพื่อช่วย Auto Fill / Sort

บางทีเราต้องการให้ Excel สามารถ Auto Fill ข้อมูลให้เราโดยอัตโนมัติ เช่น A-Z หรือ ก-ฮ แต่เชื่อหรือไม่ว่า Excel ทำไม่ได้ เพราะมันไม่รู้จัก List พวกนี้ (ไม่เข้าใจมันจริงๆ ว่าทำไมแค่นี้ไม่รู้จัก…)

แต่ไม่ต้องกลัวไป เราสามารถสอนให้ Excel รู้จัก List ที่เรากำหนดเองได้ เพื่อให้มัน Autofill และ Sort ตาม List ที่เรากำหนดเป็นด้วย (เลือกได้ใน Sort Option ว่าจะ Sort Order ตาม อะไร)

custom-list

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

  • ไปที่ Excel Option => Popular => Edit Custom List
  • จากนั้นสามารถใส่คำที่ต้องการลงไปได้เลยในช่อง List entries (แยกคำด้วย Enter) หรือจะ Import จาก Range ที่มีอยู่ก็ได้ครับ (แต่ต้องเป็น Text ที่เป็น Value ไม่ค้างเป็นสูตร)
  • อย่างตัว A-Z นี้ผมสร้างจากสูตร =CHAR(65), =CHAR(66) ไปเรื่อยๆ… แล้วค่อย Paste เป็น Value อีกที 

6. เรียนรู้ Function ให้มากขึ้น

Excel มีฟังก์ชั่นเยอะแยะมากมาย ซึ่งบางฟังก์ชั่นออกแบบมาให้เราทำงานได้ง่ายมากๆ ถ้าเรารู้จักใช้มัน
แต่ในทางกลับกัน หากเราไม่รู้ว่ามี Function แบบนั้นแบบนี้อยู่ เราก็อาจทำงานถึกโดยไม่จำเป็น…

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

product function

นี่แค่ฟังก์ชั่นเดียวนะ แล้วลองคิดดูว่าเราอาจเผลอทำอะไรถึกๆ โดยไม่จำเป็นอยู่เยอะแยะแค่ไหนครับ! (เช่น ฟังก์ชั่นคล้ายๆกับแบบนี้ก็มี SUMPRODUCT อีก ที่เอาไว้คูณแต่ละคู่อันดับแล้วจับมาบวกกัน)

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

7. ใช้ Picture Link ช่วยตรวจผลกระทบที่ช่องปลายทาง

เวลาต้องการตรวจผลกระทบต่อการกรอกข้อมูลช่องนึงไปยังอีกช่องนึง (โดยเฉพาะช่องที่อยู่ไกลๆ) เราสามารถใช้ Picture Link  มาช่วยได้ (มีตั้งแต่ Excel 2007 ขึ้นไป) โดย

  • ให้ Copy Range ปลายทาง ที่ต้องการดูผลลัพธ์
  • Paste ที่ใกล้ๆ กับช่องต้นทาง โดยให้เลือก => Paste =>As Picture => Paste Picture Link
  • ย่อ/ขยาย/เคลื่อนย้ายรูปได้ตามสะดวก
  • ลองเปลี่ยนค่าต้นทางดู เพื่อดู Effect ที่ปลายทาง

picture-link

8. ใช้ Paste Column Widths แทนการนั่งปรับความกว้างของคอลัมน์เอง

ผมเคยเจอบางท่านนั่งจัดความกว้างของคอลัมน์ใน Excel ให้เท่ากับต้นฉบับ ซึ่งบางคนนั่งจัดได้เป๊ะมาก น่านับถือในความพยายามยิ่งนัก! แต่ผมจะบอกว่ามันไม่จำเป็นเลย ในเมื่อเราสามารถ Copy แม้แต่ความกว้างของคอลัมน์ได้

paste-column-widths

วิธีการ

  • เลือกที่ cell หรือ range ต้นฉบับที่ต้องการ copy ความกว้างคอลัมน์มา
  • กด copy
  • ไปเลือกที่ช่องปลายทาง
  • กด Paste Special => Column Widths

9.  Lock Format Painter เอาไว้ เพื่อให้ Paint ช่องปลายทางได้ทีละหลายๆ รอบ

ผมคิดว่าหลายๆ ท่านคงเคยใช้ Format Painter กันบ้างแล้ว แต่ไม่แน่ใจว่าเวลาสมมติอยากจะ copy format  จากช่องนึง ไป Paste ยังช่องปลายทาง หลายๆ ช่องที่ไม่ติดกัน เพื่อนๆ ทำยังไง

ถ้าคนไม่รู้เทคนิค อาจใช้วิธี เลือกช่องต้นฉบับ … แล้วกดปุ่ม Format Painter 1 ที… แล้วกดช่องปลายทาง 1 ที… จากนั้น เริ่มใหม่ ไปที่ช่องต้นฉบับ อีกที painter อีกที ปลายทางอีกที…ไปเรื่อยๆ.. ซึ่งเสียเวลามากครับ!!

format-painter

วิธีที่ดีกว่าคือ

  1. ให้เลือกช่องต้นฉบับ
  2. ดับเบิ้ลคลิ๊ก ที่ปุ่ม Format Painter (ปุ่มจะถูกเลือกค้างไว้เลย ต่างจากการกดปกติที่กดเพียง 1 ที)
  3. เลือกช่องปลายทางกี่ทีก็ได้ตามต้องการ
  4. หากจะเลิก Paint ค่อยกลับมากดปุ่ม Format Painter อีกครั้งหนึ่ง

10. ทำงานใน Excel แล้วค่อยใช้ Copy ไป Paste as Link ลงที่อื่น

เมื่อมีการทำข้อมูลเป็นตาราง มีการคำนวณ หรือมีกราฟ ผมแนะนำให้ใช้ Excel ทำงาน แล้วค่อย Copy เป็น Link ไปยัง MS Word หรือ PowerPoint ปลายทางแทน

paste-link

เหตุผล :

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

วิธี Copy ไป Paste เป็น Link :

  1. ให้ Copy ข้อมูลตาราง หรือ กราฟใน Excel
  2. เปิด MS Word หรือ PowerPoint ที่เป็นโปรแกรมปลายทาง
  3. Paste => Paste Special => เลือก Paste Link

เป็นอย่างไรบ้างครับกับ รวม 10 เทคนิค Excel ง่ายๆ ที่ทำให้คุณทำงานเร็วขึ้น ถ้าเพื่อนๆ มีเทคนิคอื่นๆ จะแชร์ก็สามารถ comment ไว้ได้ หรือจะไปพูดคุยกันใน inwexcel Facebook Page ก็ได้นะครับ

ในตอนต่อไป ผมจะมาแชร์ รวม 5 เทคนิคการเขียนสูตรใน Excel ที่คุณอาจยังไม่รู้มาก่อน! ใครสนใจ คลิ๊กเลยครับ ^^

เทคนิคการลบ Hyperlink และป้องกันไม่ให้ Excel ใส่ Hyperlink เองแบบอัตโนมัติ

บ่อยครั้งที่เราไม่อยากให้ Excel ใส่ Hyperlink ให้เราเองโดยอัตโนมัติ จริงๆ เราสามารถไป set ค่าได้ครับ และมีวิธีเอา Hyperlink ที่มีอยู่ออกแบบง่ายๆ ด้วย ทำยังไงมาดูกัน

วิธีการป้องกันไม่ให้มี Auto Hyperlink

สามารถไป set ค่าได้ใน Options => Proofing => Auto Correct Options => Auto Format As You Type

จากนั้นเอาติ๊กถูกหน้าคำว่า Internet and Network path with hyperlinks ออกครับ

excel-not-auto-hyperlink

วิธีลบ Hyperlink ที่มีอยู่ออกแบบง่ายๆ

Excel 2010 ขึ้นไป

วิธีที่ 1 : คลิ๊กขวา => Remove Hyperlinks

remove-link2010-1

วิธีที่ 2 : ไปที่ Ribbon Home  ส่วนของ Editing => Clear => Remove Hyperlinks

 

 

 

remove-link2010-2

Excel ver. ต่ำกว่า 2010

paste-special-multiply-remove-hyperlink

มี step ดังนี้

  1. ให้ Copy ค่าจากช่องที่ว่างๆอยู่ แล้วมา Paste Special ลงช่องที่มี Link
  2. แล้วให้เลือก Option ของ Paste Special เป็น Multiply

ป.ล. วิธีนี้ผมลองใน Excel 2007 แล้ว Hyperlink หายไปจริง แต่ Format พวกสีตัวอักษรกับการขีดเส้นใต้ยังอยู่ครับ ต้องมาแก้ format อักษรอีกที

ภาพวาดงามๆ โดย ศิลปินผู้ใช้ Excel เป็นเครื่องมือ (Excel Artist)

จะมีกี่คนที่คิดว่า Excel สามารถวาดรูปอย่างจริงจังออกมาสวยงามได้ไม่แพ้โปรแกรมแพงๆ อย่าง Illustrator หรือ Photoshop

ผมจะแสดงให้ดูครับว่า Excel วาดรูปได้สวยงามแค่ไหน

วาดรูปด้วย Shape

คุณปู่ชาวญี่ปุ่นที่มีชื่อว่า Tatsuo Horiuchi เค้าใช้ Excel วาด Shape ออกมาทีละชิ้น แล้วเอามาประกอบกันเป็นรูปสุดอลังการได้สวยงามมากจริงๆครับ

horiuchi-tatsuo-excel

คุณปู่คนนี้ตัดสินใจซื้อคอมพ์แล้วหัดใช้ Excel ก่อนจะถึงวัยเกษียณ โดยเน้นเอามาใช้วาดรูป เพราะปู่เค้าคิดว่าโปรแกรมวาดรูปทั่วไปมีราคาแพงมาก Excel จริงๆก็วาดรูปได้เหมือนกัน  แถมวาดได้สวยกว่าใช้โปรแกรม Paint ตั้งเยอะแน่ะ (ถ้าปู่อยู่เมืองไทยคงซื้อ Photoshop หรือ Illustrator เถื่อนไปแล้ว และคงไม่ดึงถึงขนาดนี้)

ไม่ต้องคิดไรมาก ลองดูรูปผลงานของแกได้เลยครับ

horiuchi-tatsuo-excel2

ใครไม่เชื่อว่าวาดด้วย Excel จริง โหลดไฟล์นี้ไปเปิดเล่นได้เลย excel-art (.zip)

Video การวาดรูป Gundam ด้วย Excel

ส่วนอันนี้เป็นผลงานของ shukei01 ครับ เป็น Video การวาดรูป Gundam ด้วย Excel ซึ่งก็ inw ไม่แพ้กันเลยครับ…

วาดรูปด้วยการ Fill สีลงช่อง แล้วทำให้ช่องขนาดเล็กๆ ละเอียดๆ

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

Pokemon โดย thetokomitsu

excel pokemon

รวมมิตรตัวละครจากเกม (โดย synbios16)

excel-game-art

 

ยังมีอีกเยอะครับ ไปดูได้ที่ http://www.hongkiat.com/blog/microsoft-excel-artworks/

เห็นอย่างนี้แล้ว อยากวาดรูปด้วย Excel กันบ้างมั๊ยครับ??

การกรองข้อมูลใน Excel ด้วย Filter & Sort และ Advanced Filter

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

excel-advanced-filter

เครื่องมือ Filter (โดยเฉพาะแบบ Basic) ถือว่าเป็นทักษะพื้นฐาน ที่บริษัท/โรงงาน คาดหวังให้คนทำงานเกี่ยวกับ Excel ทำได้ ดังนั้นใครยังไม่เข้าใจหรือยังใช้ไม่เป็น ผมอยากให้ดูวีดีโอชุดนี้ครับ

ไฟล์ประกอบ : inwexcel-filter-practice (.Xlsx)

กรองข้อมูลใน Excel ตอนที่ 1/2 : Filter & Sort

ประเด็นสำคัญ ของ Filter

  • เวลาจะ Filter ข้อมูล ควรเตรียมข้อมูลให้บรรทัดบนสุดเป็นหัวตาราง ไม่ใช่ตัวข้อมูลจริง
  • กด Filter ได้ 2 ที่ คือ
    • ที่ Ribbon Home => บริเวณ Editing เลือก Sort & Filter => Filter
    • ที่ Ribbon Data => บริเวณ Sort & Filter เลือก Filter
  • เวลากด Filter แล้วจะมีปุ่มสี่เหลี่ยมขึ้นมาที่หัวตาราง เราสามารถกดปุ่มนั้นเพื่อเข้าสู่เมนูการกรองข้อมูล
  • เวลากดปุ่มกรองที่คอลัมน์ไหน มันก็จะกรองข้อมูลโดยใช้หลักเกณฑ์จากคอลัมน์ที่เรากำลังกด
    • ถ้าจะคัดกรองค่าแบบเจาะจง เวลากดแล้วมี Check Box อันไหนที่ไม่อยากให้เห็นให้ติ๊กออกได้เลย
    • ถ้าจะคัดกรองค่าแบบกำหนดเงื่อนไข
      • ถ้าข้อมูลเป็น Text จะมีให้เลือก Text Filter ซึ่งเลือกได้ว่าจะเอาช่องที่มีข้อความ xxx, ขึ้นต้นด้วยคำนี้, ลงท้ายด้วยคำนี้ เป็นต้น
      • ถ้าข้อมูลเป็น Number จะมีให้เลือก Number Filter ซึ่งเลือกได้ว่า  เอาค่าที่มากกว่าเท่าไหร่ น้อยกว่าเท่าไหร่ หรือจะแสดง Top10 ของข้อมูลก็ได้ เป็นต้น
  • Filter ต่างจาก Hide ตรงที่หากเรา Copy ไป Paste ที่อื่น ข้อมูลที่ถูกกรองทิ้งจะไม่ถูก copy ไปด้วย (แต่ข้อมูลที่ Hide ไว้ยังถูก Copy ไป)

ประเด็นสำคัญ ของ Sort

หาก Sort จากคอลัมน์เดียว ให้กดที่ปุ่มสี่เหลี่ยมที่หัวตาราง จะง่ายกว่า โดย

  • ถ้าข้อมูลเป็น Text จะมีให้เลือก Sort A->Z และในทิศกลับกัน
  • ถ้าข้อมูลเป็น Number จะมีให้เลือก Smallest to Largest และในทิศกลับกัน

หากต้องการ Sort แบบเป็นลำดับขั้น ให้เลือกที่เครื่องมือ Sort ที่อยู่บน Ribbon ข้างๆเครื่องมือ Filter

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

กรองข้อมูลใน Excel ตอนที่ 2/2 : Advanced Filter

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

ประเด็นสำคัญ ของ Advanced Filter

  • สามารถใช้เครื่องมือได้ที่ Ribbon Data => บริเวณ Sort & Filter เลือก Advanced
  • เลือก Location ได้ ว่า
    • จะ Filter อยู่กับที่ (Filter the list, in-place) หรือ
    • จะเอาข้อมูลที่กรองแล้ว Copy ออกไปไว้ที่อื่นเลย (Copy to another location แต่ต้องเป็นชีทเดียวกัน)
      • ตรงนี้สามารถไปสร้างหัวตาราง ที่เรียงลำดับและจำนวนอาจไม่เหมือนต้นฉบับได้ เครื่องมือมันจะโปรยข้อมูลลงให้ถูกช่องเอง
  • List Range ให้เลือก Data Source ของเรา
  • Criteria Range ให้เลือกว่าเรามีเงื่อนไขการคัดกรองอย่างไร (ต้องไปสร้างเป็นตารางเงื่อนไขเพิ่ม ดูรายละเอียดได้ในวีดีโอครับ)
  • Unique Records Only เอาไว้ติ๊กหากต้องการกำจัดข้อมูลที่ซ้ำกันออก

เทคนิคการลบข้อมูลเก่าที่ค้างอยู่ใน Pivot Table

เวลาที่เราทำ Pivot Table เสร็จแล้วมีการเปลี่ยนแปลงข้อมูลใน Source Data อาจทำให้มีเหตุการณ์ที่มี Item ที่เคยอยู่ใน Source Data เก่าตกค้างใน Drop Down List ใน Row Label, Column Label, หรือ Filter ได้

pivot-old-item

หากเพื่อนๆใช้ Excel ตั้งแต่ Version 2007 ขึ้นไป สามารถแก้ไขเหตุการณ์ดังกล่าวได้ง่ายมากๆ โดย

  1. คลิ๊กขวาที่ตาราง Pivot
  2. เลือก PivotTable options
  3. เลือก Data tab
  4. ในหมวด Retain Items เลือก None
  5. OK แล้วกด Refresh ตาราง Pivot อีกครั้ง

pivot-old-item2

สำหรับ Excel Version เก่า สามารถไปโหลด Script VBA เพื่อแก้ไขได้ที่ http://www.contextures.com/xlPivot04.html