อะไรเอ่ยทำให้ Excel หาคำตอบผิดพลาดมากที่สุด

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

cell errors instances

ในภาพ Instances แสดงว่าจากจำนวนผู้ใช้ 281 รายนั้น มีผู้ใช้ Excel ที่สร้างงานแล้วได้คำตอบผิดจำนวน 89 รายหรือ 31.7% ที่มีสาเหตุมาจาก Logic แต่ถ้านับจากจำนวนเซลล์ที่ทำการตรวจสอบทั้งหมด 2,353 เซลล์ พบว่ามีจำนวนเซลล์เกือบครึ่งเท่ากับ 1,073 เซลล์หรือ 45.6% ทีเดียวที่ใช้ Logic ผิดพลาด

cell errors cells

แต่สิ่งที่ผู้ใช้ Excel นึกไม่ถึงว่าเป็นสาเหตุสำคัญของคำตอบที่ผิดพลาดก็คือ การใช้ลิงก์อ้างอิงผิดตำแหน่ง เลือกเซลล์ผิดที่ เลือกพื้นที่ไม่ครบ ขาดบ้างเกินบ้าง ซึ่งการวิจัยนี้ได้ผลมาจากการเข้าไปตรวจสอบสูตรที่สร้างขึ้น พบว่า Reference เป็นสาเหตุหลักที่มากที่สุดจากการใช้งาน 137 ราย (48.8%) และมีจำนวน 857 เซลล์ (36.4%) ซึ่งจะรู้ตัวก็ต่อเมื่อมีการตรวจสอบเท่านั้น

ErrorType

นอกจากการมองที่ผลคำนวณที่ผิดพลาดแล้ว ยังมี Poor Practices ซึ่งเป็นพฤติกรรมการใช้ Excel ที่ไม่เหมาะสมแต่ยังไม่เป็นสาเหตุหรือส่งผลทำให้หาคำตอบผิด ซึ่งพบว่า Hard-coding (การใส่ค่าคงที่ไว้ในวงเล็บของสูตรหรือในสูตรคำนวณ) เป็นพฤติกรรมที่นิยมทำกันมากที่สุดทั้งจำนวนผู้ใช้งานและจำนวนเซลล์ คือ 151 รายและ 1,920 เซลล์ แม้ Hard-coding ไม่ส่งผลทำให้ผิดพลาดทันทีแต่เมื่อเวลาผ่านไปเมื่อค่าคงที่นั้นล้าสมัยหรือแชร์ให้ใช้แฟ้มร่วมกันก็ย่อมทำให้ Excel คำนวณให้คำตอบผิดได้ในอนาคต

จะทำอย่างไรที่จะป้องกันไม่ให้เกิดข้อผิดพลาดหรือลดโอกาสความผิดพลาดให้เหลือน้อยที่สุด

  1. ก่อนจะสร้างสูตร ต้องเรียนรู้ทั้งข้อดี ข้อเสีย และข้อควรระวัง อย่าเรียนรู้แค่เรื่องทำได้
  2. ก่อนจะสร้างสูตร ต้องแยกค่าตัวแปรออกมาเก็บไว้ในเซลล์และตั้งชื่อ Range Name ให้กับพื้นที่ตาราง
  3. เมื่อสร้างสูตร ต้องลิงก์ค่าตัวแปรมาใช้ในสูตรและสามารถนำชื่อ Range Name มาใช้ในการอ้างอิง แทนการใช้เมาส์คลิกหรือพิมพ์ตำแหน่งอ้างอิงเอง
  4. เมื่อสร้างงานเสร็จแล้ว ต้องสามารถแกะสูตรออกว่าแต่ละส่วนของสูตรหาคำตอบมาได้อย่างไร

หากถามว่าคุณมั่นใจ 100% ไหมกับแฟ้มที่คุณสร้างขึ้น ถ้าตอบว่ามั่นใจได้ 99.99% ก็หวังว่าไม่ได้ผิดเรื่องอื่นที่ยังไม่ได้เอ่ยถึงกันนะครับ เช่น การกรอกบันทึกข้อมูล การ copy/paste หรือทำในสิ่งที่ไม่ควรทำ และไม่ได้ทำในสิ่งที่ควรทำ

หวังว่าบทเรียนนี้จะช่วยตอบคำถามว่า “อยากเริ่ม Excel ตั้งแต่พื้นฐาน ควรเริ่มตั้งแต่ตรงไหน แบบเริ่มต้น ถึง Advance”

ถ้าพื้นฐาน Excel ของคุณยังไม่เข้าใจ ใช้ไม่ได้ ทำไม่เป็นตามที่แนะนำไว้นี้ กว่าจะข้ามไปขั้น Advance ก็คงยากทีเดียว

Dynamic Range 6 แบบด้วยสูตร Offset

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

โครงสร้างสูตร OFFSET (สร้างแบบ {Array} กด Ctrl+Shift+Enter เพื่อกระจายค่าให้เห็น)

=Offset(เซลล์อ้างอิง, จำนวน row ถัดไป, จำนวน column ถัดไป, ความสูงของตาราง, ความกว้างของตาราง)

รายละเอียดเนื้อหาอยู่ใน หลักสูตรเคล็ดการเพิ่มผลงาน ลดความซับซ้อนของงานด้วย Excel VBA

A1 หารายการเก่า รายการเดียว
ใช้ Match หาตำแหน่งรายการ
=OFFSET(B2, MATCH(“a010”,ID,0), 0, 1, 3)

A2 หารายการเก่า 2 รายการ
กำหนดเลขความสูงตามใจ
=OFFSET(B2, MATCH(“a010”, ID,0), 0, 2, 3)

B1 หารายการใหม่ต่อท้าย รายการเดียว
ใช้ CountA นับจำนวนรายการเก่า
=OFFSET(B2, COUNTA(B:B), 0, 1, 3)

B2 หารายการใหม่ต่อท้าย 2 รายการ
=OFFSET(B2, COUNTA(B:B), 0, 2, 3)

C1 หารายการทุกรายการที่เพิ่มลด
ใช้ CountA นับหาความสูง ลบ 1 เพราะนับหัวตารางเกินมา
=OFFSET(B2, 1, 0, COUNTA(B:B)-1, 3)

C2 หาพื้นที่ทั้งตารางที่เพิ่มลด รวมหัวตารางด้วย
=OFFSET(B2, 0, 0, COUNTA(B:B), 3)

Number – Make It Easy

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

ข้อผิดพลาดที่พบเห็นเสมอ ซึ่งทำให้ต้องใช้ Excel ยากขึ้น

1. ใส่เครื่องหมายฝนทองนำหน้าเลข 0 เช่น รหัส ‘001 หรือกำหนด Format ให้เป็น Text

2. บันทึกตัวเลข ผสมกับตัวอักษร เช่น 100 บาท

3. สร้างสูตรนำตัวเลขไปผสมกับค่าอื่นด้วยเครื่องหมาย & เช่น =A1&A2

4. ใช้สูตรที่คืนค่าเป็น Text เช่น Left Right Mid Text

5. แก้ error โดยใช้ “” เพื่อทำให้เป็นช่องว่างแทน

6. ลบข้อมูลทิ้งโดยเคาะช่องว่างลงไปแทนที่จะกดปุ่ม Delete

7. บันทึกวันที่แบบ Text เช่น Jan Feb Mar หรือสลับเดือนมาก่อนวัน เช่น 6/27/2021

สังเกตง่ายๆว่า ตัวเลขที่มีสถานะเป็น Text นั้นจะชิดซ้ายของเซลล์

ถ้าตัวเลขที่บันทึกหรือเป็นผลจากการคำนวณนั้น ยังต้องนำไปใช้ในการคำนวณต่อไปอีก ต้องยังคงชิดขวาของเซลล์

หากต้องการทำให้เห็นตัวอักษรแทรกตัวเลข ให้ใช้ Format ช่วย เช่น #,###0 “บาท” จะทำให้เลข 100 แสดงออกมาเป็น 100 บาท โดยตัวเลขยังคงเป็นตัวเลขเหมือนเดิม สามารถนำไปคำนวณต่อได้ทันที

หากต้องการแสดงชื่อเดือน ให้พิมพ์ 15/1/2021 แล้วใช้ Format mmm

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

วิธีใช้วันที่และเวลา

เชิญชมวิดีโอ 10 ตอนในหลักสูตร “เตรียมพร้อมเครื่อง เตรียมพร้อม Excel”

สมัครเรียนหลักสูตรนี้ ฟรี ได้ที่ https://www.excelexperttraining.com/online/courses/01-excel-ready/

  1. ในการบันทึก ให้พิมพ์ให้ครบทั้งวันเดือนปี อย่าบันทึกเฉพาะวันที่หรือเฉพาะเดือนหรือเฉพาะปีเท่านั้น โดยจะพิมพ์ส่วนของเวลาต่อท้ายด้วยหรือไม่ก็ได้ เช่น 14/2/2021 12:00
  2. ให้บันทึกโครงสร้างของวันที่ลงไปในเซลล์ตามแบบที่กำหนดไว้ใน Regional Setting ของ Windows เช่น ถ้ากำหนดไว้ใน Regional Setting เป็น Thai ก็ต้องบันทึกวันที่ตามแบบของประเทศไทยซึ่งใช้ลำดับตามวันก่อนเดือนก่อนปี แต่ถ้ากำหนดใน Regional Setting เป็น USA ก็ต้องบันทึกตามลำดับเดือนก่อนวันก่อนปี
  3. แม้จะใช้ Regional Setting เป็น Thai แต่ในการบันทึกในส่วนของเลขปีต้องใช้ปีค.ศ.ในการบันทึกเท่านั้น จากนั้นให้ใช้ Format ปรับการแสดงปีค.ศ.ให้เป็นปีพ.ศ.ในภายหลัง เช่น ให้บันทึก 14/2/2021 (ห้ามใช้ปีพ.ศ.แทนอย่างเด็ดขาด) จากนั้นเมื่อต้องการแสดงเป็น 14/2/2564 ให้ใช้ Format [$-1070000]d/m/yyyy หรือถ้าใช้ Excel 365 ใช้ Format [$-,107]d/m/yyyy
  4. ในโครงสร้างของวันที่ให้พิมพ์เครื่องหมาย / ในการแบ่งส่วนของ วัน/เดือน/ปีค.ศ. และในโครงสร้างของเวลาให้พิมพ์เครื่องหมาย : ในการแบ่งส่วนของ ชั่วโมง:นาที หรือ ชั่วโมง:นาที:วินาที
  5. ควรพิมพ์เลขปีค.ศ.ให้ครบทั้ง 4 หลักเสมอ แต่ถ้าจำเป็นต้องพิมพ์แค่สองหลักท้าย พอกดปุ่ม Enter เพื่อบันทึกค่าลงไป Excel จะเปลี่ยนตัวเลขปี 2 หลักท้าย ดังนี้
    • ตั้งแต่เลข 00-29 ให้เป็นช่วงปีค.ศ. 2000-2029
    • ตั้งแต่เลข 30-99 ให้ย้อนกลับเป็นช่วงปีค.ศ. 1930-1999
  6. เมื่อบันทึกวันที่แล้วต้องชิดขวาของเซลล์เสมอ (สาเหตุที่ชิดขวาเพราะมีค่าเป็นตัวเลข) และขอให้หลีกเลี่ยงการใช้วันที่แบบ Text ซึ่งชิดซ้ายของเซลล์เนื่องจาก Excel จะไม่นำ Regional Setting มาช่วยควบคุมในการแบ่งส่วนของวันเดือนปีที่ใช้แบบ Text
  7. ค่าของวันที่และเวลามีชื่อเรียกว่า Date Serial Number หรือเรียกว่า Serial Number (SN) โดย Excel ถือว่า 1/1/1900 0:00:00 มีค่า SN=1 ซึ่งเราสามารถแกะดูค่า SN ได้โดยเปลี่ยน Format เป็น General (โดยการกดปุ่ม Ctrl+Shift+ ~) เช่น 14/2/2021 12:00:00 มีค่า SN เท่ากับ 44241.5
    • ในส่วนของตัวเลข SN ส่วนที่เป็นจำนวนเต็ม คือ ค่าของวันที่
    • ในส่วนของตัวเลข SN ส่วนที่เป็นเศษทศนิยม คือ ค่าของเวลา
  8. เวลาของ Excel เริ่มจาก 0:00:00-23:59:59 และใช้ Format h:mm:ss
  9. ระยะเวลา เริ่มจาก 0:00:00 ขึ้นไป โดยไม่สิ้นสุดที่ 23:59:59
    • ใช้ Format [h]:mm:ss เพื่อแสดงเลขตั้งแต่ 24 ชั่วโมงขึ้นไปได้
    • ใช้ Format [mm]:ss เพื่อแสดงเลขตั้งแต่ 60 นาทีขึ้นไปได้
    • ใช้ Format [ss] เพื่อแสดงเลขตั้งแต่ 60 วินาทีขึ้นไปได้
  10. ในการแสดงเวลาหรือระยะเวลา ถ้าค่า SN < 1 จะใช้ Format ของเวลาหรือระยะเวลาก็ได้ แต่ถ้า SN >=1 ต้องเลือกใช้ Format ของระยะเวลาที่ใช้เครื่องหมาย [ ] เท่านั้น
  11. ถ้าต้องการบันทึกการทำงานข้ามคืน ไม่ควรบันทึกเฉพาะเวลาเข้าออกงาน แต่ให้บันทึกวันเดือนปีกำกับเวลาไว้ด้วย เช่น 14/2/2021 20:00 เพื่อทำให้ Excel รับรู้ค่าเป็น SN ที่มีครบทั้งส่วนของวันที่และเวลา
  12. Format ในการแสดงวันและเวลา
    • แสดงเลขวันที่ ใช้ d หรือ dd
    • แสดงเป็นชื่อวัน แบบย่อใช้ ddd หรือแบบเต็ม dddd
    • แสดงเป็นเลขเดือน ใช้ m หรือ mm
    • แสดงเป็นชื่อเดือน แบบย่อใช้ mmm หรือแบบเต็ม mmmm
    • แสดงเป็นเลขปี ใช้ yy หรือ yyyy
    • แสดงชั่วโมง นาที วินาที ใช้ h:mm:ss หรือ hh:mm:ss
    • รหัสควบคุม Locale ใช้ [$-409] สำหรับ USA และ [$-1070000] สำหรับไทย ซึ่งรหัสควบคุมนี้ใน Excel รุ่นใหม่จะต่างไปบ้าง

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

การคำนวณวันที่และเวลา

ก่อนที่จะนำวันที่และเวลามาคำนวณได้ ต้องเริ่มจากการตรวจสอบว่าข้อมูลเกี่ยวข้องกับวันที่และเวลาซึ่งถูกบันทึกไว้ว่ามีโครงสร้างที่ถูกต้องตรงกับ Regional Setting หรือไม่ หากบันทึกไว้ผิด เช่น ใน Regional Setting กำหนดไว้เป็น USA แต่คนที่บันทึกข้อมูลพิมพ์วันที่ไว้ในแบบประเทศไทย เช่น พิมพ์ 4/2/2021 ซึ่งต้องการบันทึกแบบไทยให้หมายถึงวันที่ 4 เดือน 2 ปี 2021 จะพบว่าเมื่อนำแฟ้มนั้นมาเปิดบนเครื่องที่มี Regional Setting เป็นไทย เลขที่ของวันจะสลับกับเลขที่ของเดือน จะแสดงเป็น 2/4/2021 แทน ทำให้กำหนดเวลาที่บันทึกไว้ผิดทั้งหมด ถ้าไม่เก่งสูตรเกี่ยวข้องกับวันที่และเวลาก็ต้องจัดการพิมพ์ทับใหม่ทั้งหมด

นอกจากนี้ต้องตรวจสอบต่อไปอีกว่า ค่าของวันที่ซึ่งบันทึกไว้เป็นข้อมูลที่มีค่าเป็นตัวเลข (Date Serial Number หรือ SN) หรือไม่ โดยเริ่มจากยกเลิกการจัดชิดซ้ายชิดขวาของเซลล์วันที่ทั้งหมด หากพบว่า ชิดขวาก็ใช้ได้ แต่ถ้าพบว่าชิดซ้ายแสดงว่ามีค่าเป็น Text ซึ่งไม่สามารถนำมาคำนวณต่อได้ในทันที จำเป็นต้องอาศัยสูตร Left, Right, Mid แยกตัวเลขแต่ละส่วนที่เป็นวันเดือนปีออกจากกันแล้วใช้สูตรวันที่และเวลามาช่วยแก้ไขให้มีค่าเป็น SN

หากคุณอยากจะเก่งสูตรคำนวณเรื่องวันที่และเวลา ต้องรู้จักสูตรหา SN หรือสูตรที่สามารถแปลงเลขที่ของวันเดือนปีที่มนุษย์เข้าใจไปเป็นค่า SN ที่ Excel รู้จัก เช่น สูตร Now(), Today(), Date(Year,Month,Day), Time(Hour,Minute,Second)

จากนั้นต้องสามารถใช้สูตรแปลงค่า SN กลับมาเป็นเลขที่ของวันเดือนปี เช่น สูตร Day(SN), Month(SN), Year(SN), และ WeekDay(SN) หรือหาเลขที่ของเวลา เช่น Hour(SN), Minute(SN), Second(SN)

สูตรแปลงเลขที่ของวันเดือนปีและเวลาที่มนุษย์เข้าใจ
ไปเป็นค่า Serial Number

เพื่อช่วยให้เข้าใจสูตรได้ง่ายขึ้น ขอสมมติว่าปัจจุบัน คือ วันที่ 14 เดือนกุมภาพันธ์ ปีค.ศ. 2021 เวลา 12 นาฬิกา 30 นาที 45 วินาที

  1. =NOW() จะได้วันเดือนปีและเวลาปัจจุบัน เช่น 14/2/2021 12:30:45
  2. =Today() จะได้เฉพาะวันเดือนปีปัจจุบัน เช่น 14/2/2021
  3. =Date(2021,2,14) จะได้ 14/2/2021
  4. =Date( Year(Today()), Month(Today())+1, 0) จะได้วันเดือนปีของวันสุดท้ายของเดือนปัจจุบัน คือ 28/2/2021 หรือถ้าใช้ Excel 2010 เป็นต้นมามีสูตร =EOMonth(Today(),0) โดยเลข 0 สามารถเปลี่ยนเป็นเลขบวกลบตามเดือนถัดไปหรือก่อนหน้า
  5. =Time(12,30,45) จะได้เวลา 12:30:45
  6. =Time(12,30,45)+1 จะได้ระยะเวลา 36:30:45 ซึ่งต้องใช้ Format [h]:mm:ss ด้วย

สูตรแปลงค่า Serial Number
กลับมาเป็นเลขที่ของวันเดือนปีและเวลาที่มนุษย์เข้าใจ

สมมติว่าเซลล์ A1 มีสูตร =NOW() ซึ่งแสดงออกมาเป็น 14/2/2021 12:30:45 (ถ้าต้องการแสดงค่าออกมาเป็น SN โดยการเปลี่ยน Format เป็น General จะพบว่า เซลล์ A1 มีค่า SN เท่ากับ 44241.5213541667 ซึ่งเป็นตัวเลขที่ Excel รู้จักแต่เราไม่รู้จัก)

  1. =Day(A1) จะได้เลขวันที่ 14
  2. =Month(A1) จะได้เลขเดือน 2
  3. =Year(A1) จะได้เลขปี 2021
  4. =WeekDay(A1) จะได้เลขของวันในสัปดาห์ 1=Sunday, 2=Tuesday,…7=Saturday
  5. =Hour(A1) จะได้เลขชั่วโมง 12
  6. =Minute(A1) จะได้เลขนาที 30
  7. =Second(A1) จะได้เลขวินาที 45

9 รูปแบบการหาค่าใน Excel

Index A, B, C, D, E, F
Match C, D, E, F
Lookup C, D
VLookup E
HLookup F
XLookup E, F
Offset indirect+Address G
SumIF SumIFS Array H, I

อธิบาย

Index A, B, C, D, E, F หมายถึง ใช้สูตร Index หาค่าได้จากตารางหน้าแบบ A, B, C, D, E, F

VLookup ใช้ได้กับตารางหน้าตาแบบ E

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

นี่คิดแบบตรงไปตรงมาง่ายๆครับ ถ้าเอาแบบพิสดาร Index จะนำไปใช้ได้ทั้ง 9 แบบทีเดียว

เคล็ดจัดการกับ Error

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

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

การแก้ Error ทำได้หลายวิธี

  1. แก้ด้วยสูตร IFError หรือ ใช้ If ร่วมกับสูตร IsError เพื่อเปลี่ยน Error ให้เป็นค่าอื่น โดยต้องยึดหลักว่า ค่าใหม่ที่ใช้แทน Error นั้นต้องยังคงมีสถานะตามค่าเดิมที่สูตรจะคำนวณได้ เช่น ให้แสดงเป็นเลข 0 แทนเมื่อสูตรเดิมคำนวณตัวเลขแล้วใช้ Format 0;-0; เพื่อทำให้เห็นเป็นเซลล์ว่าง หรือถ้าสูตรเดิมหาค่าเป็นตัวอักษร ให้แสดงเป็นช่องว่างด้วย Null Text โดยการใส่ “” (ไม่ควรใช้ “” กับสูตรที่คำนวณเป็นตัวเลข เพราะ “” มีสถานะเป็น Text และไม่สามารถนำ “” ไปบวกลบคูณหารต่อได้)
    ..
  2. ถ้าไม่ได้นำค่านั้นไปคำนวณต่ออีก อาจปล่อย Error ไว้ แต่ทำให้มองไม่เห็น Error โดยใช้ Conditional Formatting ตรวจสอบโดยใช้สูตร IsError แล้วเปลี่ยนสี font ให้กลืนกับสีพื้น
    ..
  3. ปล่อย Error ไว้ แต่ทำให้มองไม่เห็น Error บนหน้ากระดาษเมื่อสั่งพิมพ์ โดยกำหนดใน Page Setup > Sheet > Cell errors as แล้วกำหนดให้แสดงเป็น blank หรือ — แทน

Excel Ping Code

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

นอกจากการแกะในช่อง Formula Bar แล้วยังมีหลักการอ่านคู่วงเล็บด้วยสายตาอีก โดยใช้เลขนับเพิ่มจาก 1 2 3 เมื่อพบวงเล็บเปิด และนับตัวเลขถอยหลังจาก 3 2 1 เมื่อพบวงเล็บปิด เช่น

=((( ))) อ่านว่า 123-321

=(( ) ((( )) ( ) )) อ่านว่า 12-2 234-43 3-3 21 แกะคู่วงเล็บทีละลำดับตามนี้
=(( ) ((( )) ( ) )) อ่านว่า 12-2 234-43 3-3 21
=(( ) ((( )) ( ) )) อ่านว่า 12-2 234-43 3-3 21
=(( ) ((( )) ( ) )) อ่านว่า 12-2 234-43 3-3 21
=(( ) ((( )) ( ) )) อ่านว่า 12-2 234-43 3-3 21
=(( ) ((( )) ( ) )) อ่านว่า 12-2 234-43 3-3 21
=(( ) ((( )) ( ) )) อ่านว่า 12-2 234-43 3-3 21

สูตรในตัวอย่างนี้ยาวมาก ลองแกะกันดูครับ
=((( (( ) ( )) ( ( ) (( ( ) ( ) )))))) อ่านว่า 123 455 554 4 55 56 77 77 654321

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

นอกจากนี้ยังมีวิธีแกะสูตรอีกแบบ โดยใช้คำสั่งบนเมนูช่วยแกะ แต่ใช้ได้กับสูตรที่ซ้อนกันแบบไม่ซับซ้อนเท่านั้น ต่างจากวิธีที่แนะนำไว้นี้ซึ่งใช้แกะได้เสมอ

วิดีโอชุด วิธีใช้ Macro Recorder 10 ตอน

Macro ตอนที่ 1 Automatic Running

วิธีทำให้แฟ้มทำงานตามที่กำหนดไว้ทันทีเมื่อเปิดแฟ้มหรือปิดแฟ้ม
โดยตั้งชื่อ Macro ว่า Auto_Open กับ Auto_Close
ไวรัสที่จะทำร้ายคุณก็ใช้วิธีเดียวกันนี้แหละ
เรียนรู้กันไว้จะได้ระวังไว้เสมอ

Download ตัวอย่างได้จาก
https://drive.google.com/open?id=0Bxpfgh-rqhIWYjVBejBBOUUyWWc

=====

Macro ตอนที่ 2 Automatic ซ้อน Automatic ๆๆๆๆ

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

Download ตัวอย่างได้จาก
https://drive.google.com/open?id=0Bxpfgh-rqhIWc1hvaThsQi1taEU

=====

Macro ตอนที่ 3 Dangerous Click

คลิกอันตราย … ต้นเหตุของจุดอ่อนที่ร้ายแรงที่สุดของ VBA

เคล็ดลับสำคัญของคนที่ฉลาดใช้ Macro Recorder
ถ้าอยากจะทำให้ Macro Recoder สร้างรหัสที่ฉลาด ต้องอย่าคลิกลงไปในตาราง
หากคลิกที่ไหน จะเกิดข้อจำกัดขึ้นมาทันที ที่นั่นแหละ

Download ตัวอย่างได้จาก
https://drive.google.com/open?id=0Bxpfgh-rqhIWOU5UaWFaSjVZVjA

=====

Macro ตอนที่ 4 วิธีกำหนดขอบเขตตาราง

จะทำอย่างไรให้ Macro หาขอบเขตตารางที่ยืดได้หดได้
เมื่อคุณฉลาดเลือกใช้วิธีกำหนดขอบเขตตาราง
Macro Recorder ก็จะสร้างรหัส VBA ที่ฉลาดตาม

Ctrl+PgUp
ActiveSheet.Next.Select

Ctrl+PgDown
ActiveSheet.Previous.Select

ใช้ Mouse คลิกเลือกตาราง
Range(“B2:D7”).Select

ใช้แป้นพิมพ์กด Shift ตามด้วยลูกศรเลือกจนครบ
Range(“B2:D7”).Select

Shift+End ลูกศรขวา
Range(Selection, Selection.End(xlToRight)).Select

Shift+End ลูกศรลง
Range(Selection, Selection.End(xlDown)).Select

Ctrl+Shift+ลูกศรขวา
Range(Selection, Selection.End(xlToRight)).Select

Ctrl+Shift+ลูกศรลง
Range(Selection, Selection.End(xlDown)).Select

F5 > Special > Current Region
Selection.CurrentRegion.Select

Ctrl+*
Selection.CurrentRegion.Select

Ctrl+a
Range(“B2:D7”).Select

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

Download ตัวอย่างได้จาก
https://drive.google.com/open?id=0Bxpfgh-rqhIWclZaSU9aS2stM2s

=====

Macro ตอนที่ 5 Keep Active Data

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

Download ตัวอย่างได้จาก
https://drive.google.com/open?id=0Bxpfgh-rqhIWekx5RFlkZFJPUG8

(หมายเหตุ รหัสชุดนี้ยังไม่ฉลาดนัก ยังมีจุดอ่อนอยู่ แต่น่าจะมีประโยชน์กับงานบางอย่าง)

=====

Macro ตอนที่ 6 – Using Excel with VBA Concept

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

=====

Macro ตอนที่ 7 – Smart Macro with F5

วิธีใช้ Range Name ช่วยในการ copy ข้อมูลไปเก็บไว้โดยไม่ต้องใช้เมาส์คลิก
บันทึก Macro 4 ขั้นตอน จะได้รหัสที่นำไปใช้งานได้สารพัดโดยไม่ต้องแก้ไขอีกเลย

  1. กดปุ่ม F5 ไปที่ Source
  2. กดปุ่ม Ctrl+c เพื่อ copy
  3. กดปุ่ม F5 ไปที่ Target
  4. Enter เพื่อ paste

Download ตัวอย่างได้จาก
https://drive.google.com/open?id=0Bxpfgh-rqhIWV3Axak42N3hlR2M

=====

Macro ตอนที่ 8 – Secret Trick – Using Formula Name with Macro

วิธีใช้ Formula Name ช่วยในการ copy ข้อมูลไปเก็บในตำแหน่งเซลล์ที่ต่างกัน
โดยใช้เคล็ดวิชา “สูตรใดที่หาค่าได้ สูตรนั้นย่อมบอกตำแหน่งได้”
วิธีการสำหรับคนที่รู้จัก Excel Expert Training เท่านั้น

บันทึก Macro 4 ขั้นตอน จะได้รหัสที่นำไปใช้งานได้สารพัดโดยไม่ต้องแก้ไขอีกเลย

  1. กดปุ่ม F5 ไปที่ Source
  2. กดปุ่ม Ctrl+c เพื่อ copy
  3. กดปุ่ม F5 ไปที่ Target
  4. Enter เพื่อ paste

Download ตัวอย่างได้จาก
https://drive.google.com/open?id=0Bxpfgh-rqhIWRlppNDM0bnUxQ0E

=====

เรื่องซ้้าๆของ F4 – Macro without Record Macro

F4 ทำให้คุณสามารถ copy ตำแหน่งอ้างอิงไปใช้ซ้ำที่เซลล์อื่นได้เมื่อใส่ $
F4 ยังช่วยทำงานซ้ำตามคำสั่งสุดท้ายที่เพิ่งสั่งไปได้อีก ทั้งทำดีและทำไม่ดี
แต่น้อยคนจะทราบว่า F4 ยังช่วย copy ไปใช้ซ้ำแล้วซ้ำอีกได้ด้วย

=====

Macro ตอนที่ 9 – No More Recording, No More VBA

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

Download ตัวอย่างแฟ้ม myCode.bas ได้จาก
https://drive.google.com/open?id=0Bxpfgh-rqhIWYkVnUGctR2JZUWs

=====

Macro ตอนที่ 10 – Macro Auditor

VBA จะทำงานได้ต่อเมื่อแฟ้ม Excel ที่เปิดอยู่พร้อมจะทำงานด้วย
มาหาทางจับผิดรหัส VBA ที่ได้จากการบันทึก Macro กัน
โดยกดปุ่ม F8 เพื่อสั่ง run รหัสทีละบรรทัด
หรือกดปุ่ม F9 เพื่อสั่ง run ไปหยุดรอที่บรรทัดที่ต้องการ
ระวัง อย่า copy ตารางที่มีสูตรติดไปด้วย

=====

ประโยชน์ของ Macro VBA มีมากมาย ขอแนะนำหลักสูตรเคล็ดการเพิ่มผลงาน ลดความซับซ้อนของงานด้วย Excel VBA