เจาะลึกฟังก์ชั่นการเงินใน Excel : ตอน 3 คำนวณการผ่อนเงินกู้

ตอนนี้เป็นตอนที่ 3 ซึ่งเราจะมาเรียนรู้เกี่ยวกับการคำนวณการผ่อนเงินกู้กันครับ ใครที่ยังไม่ได้อ่าน 2 ตอนที่แล้วก็เชิญอ่านก่อนได้เลย เพราะจะเป็นพื้นฐานที่สำคัญครับ ตอนที่ 1 / ตอนที่ 2

การ Split Cash Flow ก้อนใหญ่ให้กลายเป็น Cash Flow ย่อยๆ ด้วย PMT

ก่อนหน้านี้ เราได้เรียนรู้เกี่ยวกับการเคลื่อนย้าย Cash Flow ข้ามเวลาไปยังอดีต (PV) รวมถึงย้ายไปยังอนาคต (FV) ไปแล้ว คราวนี้เราจะมาเรียนรู้การทำงานฟังก์ชั่นใหม่ นั่นคือ PMT (Payment) ซึ่งจะเป็นตัวที่สามารถ Split Cash Flow จาก 1 ก้อนใหญ่ ให้กลายเป็นหลายๆ ก้อนย่อยในแต่ละ Period โดยที่แต่ละ Period มีจำนวนเงินเท่ากันด้วย

CF-10

PMT

  • =PMT(rate,nper,pv,[fv],[type])
  • =PMT(5%,5,-100) = 23.09748

ถ้าไม่เชื่อ เราสามารถทดสอบได้โดยการใส่ Cash Flow ไป 5 ก้อน แล้ว NPV กลับมา ดูสิว่าจะได้เท่ากับ 100 ตอนต้นหรือไม่

ซึ่งจะได้ว่า เมื่อ =NPV(5%,23.09747,23.09747,23.09747,23.09747,23.09747) = 100 จริงๆ ครับ

การประยุกต์ใช้

เรามักจะใช้ PMT กับการคำนวณการผ่อนเงินกู้ เช่น ถ้าซื้อบ้าน 10 ล้านบาท สมมติต้องดาวน์ 20% ทำให้ต้องกู้จริงๆ คือ 80%*10000000 หรือ 8 ล้านบาท โดยธนาคารให้ผ่อน 30 ปี ที่อัตราดอกเบี้ย 6% ต่อปี จะต้องผ่อนเดือนละเท่าไหร่?

ตรงนี้จุดสำคัญคือ เราต้องคิดก่อนว่า การผ่อนเป็นการผ่อนรายเดือน นั่นคือ 1 Period = 1 เดือน  ดังนั้นจำนวนงวดทั้งหมดจะมี =30*12 = 360งวด (ในความเป็นจริง ธนาคารจะคิด 1 Period ของการคิดดอกเบี้ย =1 วัน แต่เพื่อความง่าย ผมขอคิดเป็นเดือนนะครับ)

ดังนั้นอัตราดอกเบี้ยก็จะต้องเป็นอัตราดอกเบี้ยต่อ 1 Period ด้วย ก็จะถูกต้องมากขึ้น เช่น 6%/ปี หารด้วย 12 เดือน/ปี =6%/12 = 0.5%/เดือน หรือ 0.5%/Period นั่นเอง

เมื่อเข้าสูตร PMT จะได้ว่า

  • =PMT(6%/12,30*12,-10000000*80%) หรือ
  • =PMT(0.5%,360,-8000000)
  • = 47,964.04 บาท/เดือนนั่นเอง

ถ้าอยากรู้ว่าผ่อนไป 10 ปี จะเหลือหนี้เท่าไหร่?

อย่างที่ได้บอกไปแล้วว่าการหาเงินในอนาคต เราต้องใช้ FV แต่พอมีการผ่อนด้วย เราจึงต้องใส่ค่า PMT ไปด้วย (จากที่เดิมเคยว่างไว้) โดย nper ที่ระบุ เป็นเงินใน 10 ปีข้างหน้า ดังนั้นต้องใส่ nper เป็น 10 ปี คือ 120 งวด

  • =FV(rate,nper,pmt,[pv],[type])
  • =FV(0.5%,120,47964.042,-8000000)
  • = 6,694,858.00 บาทนั่นเอง

ถ้าอยากทดสอบว่ามันคำนวณถูกหรือไม่ ให้ลองใส่ nper เป็น 30 ปี หรือ 360 งวด ซึ่งมันควรจะออกมาได้ 0 เพราะว่าผ่อนหมดพอดีครับ

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

เจาะลึกฟังก์ชั่นการเงินใน Excel : ตอน 2 วิเคราะห์แผนประกันชีวิต

ตอนนี้เป็นตอนที่ 2 ต่อจากการปูพื้นฐานด้านการเงิน ในตอนที่แล้ว ใครยังไม่ได้อ่าน สามารถไปอ่านได้เลยที่นี่ครับ

ผลตอบแทน 200% จากประกันชีวิต ทำไมมันเยอะจัง?

คุณเคยเจอคนมาเสนอขายประกันชีวิตมั๊ยครับ? ผมรับรองว่าคุณต้องเคยเจอแน่นอน (และคงไม่ต่ำกว่า 1 ครั้งในรอบ 1 ปีด้วย!! ) แต่สิ่งที่พวกเราเจอส่วนใหญ่ จะถูกนำเสนอว่าแผนประกันแบบโน้นแบบนี้ดีมากๆ ได้ผลตอบแทนหลายเท่าของเงินลงทุนของเรา ซึ่งถ้าเราเป็นคนที่ไม่ค่อยมีความรู้ด้านการเงิน คงจะตะลึงและคล้อยตามกับการนำเสนอของตัวแทน เพราะได้เงินตอบแทนกลับมามากกว่าเงินลงทุน ยังไงก็คุ้ม ได้เงินตั้ง 200-300% ดีกว่าเงินฝากประจำ หรือ เล่นหุ้นอีก แถมไม่เห็นมีอะไรน่าจะเสี่ยงตรงไหนเลย…

ถ้าเราลองมาคิดดีๆ เลข 200% ที่ประกันชีวิตชอบโฆษณามันคือ เงินคืนทั้งหมด เทียบกับจำนวนเอาประกัน ซึ่งไม่รู้จะเทียบกันทำไม เพราะไม่ใช่สิ่งที่เราจ่ายซักหน่อย สิ่งที่เราจ่ายไปจริงๆ มันมากกว่าจำนวนเงินเอาประกันตั้งเยอะ… (ลองบวกดูสิ)

จากความรู้ที่ผ่านมา เราได้รู้ว่า การได้จำนวนเงินตอบแทนมากกว่าเงินลงทุนไม่จำเป็นจะต้องเป็นการลงทุนที่ดีนะครับ เช่น สมมติผมลงทุนด้วยเงิน 1 แสนบาท แล้วมีแผนประกัน 2 แผน อันแรก เรียกว่าแผน A บอกว่าได้เงินตอบแทนรวมทั้งหมด 3 แสนบาท  ส่วนแผน B ได้เงินตอบแทนรวมทั้งหมด 2 แสนบาท แบบนี้ แผน A ไม่จำเป็นต้องคุ้มกว่าแผน B นะครับ ทั้งนี้เพราะ Effect เรื่อง Time Value of Money นั่นเอง

ดังนั้นเราจะใช้แนวทางการวิเคราะห์ Project ด้วยวิธี IRR ในการตัดสินใจเลือกแผนประกันชีวิตกันครับ โดยเราจะหา IRR ของแผนแต่ละอัน แล้วหาอันที่ IRR สูงสุด นั่นเอง (แล้วค่อยเทียบกับค่า Required Return ในใจเราอีกที)

แผน A

มีลักษณะดังนี้

  • ชำระเบี้ยประกัน 4 ปี คุ้มครอง 10 ปี
  • ทุกสิ้นปีกรมธรรม์ที่ 1-3 รับเงินจ่ายคืนปีละ  4% ของทุนประกัน
  • ทุกสิ้นปีกรมธรรม์ที่ 4-6 รับเงินจ่ายคืนปีละ  5% ของทุนประกัน
  • ทุกสิ้นปีกรมธรรม์ที่ 7-9 รับเงินจ่ายคืนปีละ  6% ของทุนประกัน
  • รับเงินครบกำหนดสัญญา 180% ของทุนประกัน ณ สิ้นปีกรมธรรม์ที่ 10

ผมขอไม่พูดถึงเรื่องความคุ้มครองแล้วกันนะครับ Assume ว่าเราจะยังมีชีวิตอยู่จนจบแผนประกัน

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

สมมติว่าผมจะทำประกันโดยจำนวนเอาประกันเป็น 100,000  ทางตัวแทนบอกราคาให้ผมจ่ายเบี้ยประกันปีละ 51,000 บาท ผมสามารถทำเป็นตาราง Excel  และคิด IRR ได้ดังนี้

insure-planA

เงินรับ : จะเห็นว่าผมพยายามใส่สูตรเป็น Cell Reference ให้ Excel คำนวณให้มากที่สุดเท่าที่จะทำได้ โดยไม่คำนวณด้วยมือ เช่น ถ้าเรารู้ว่าเงินคืนคิดเป็น % จากทุนประกัน เราก็ควรผูกสูตรตามนั้น เพื่อที่จะลดความผิดพลาด และให้เราเปลี่ยนค่าเล่นได้ในอนาคต

เงินจ่าย : อย่าลืมว่าเราจ่ายค่าเบี้ย 4 ปี แปลว่าเราจะจ่ายที่ปีที่ 0,1,2,3 เท่านั้น ไม่ได้จ่ายตอนสิ้นปีที่ 4 นะครับ

คุณสามารถใส่ Cash Flow ที่เป็นการจ่ายให้เป็นลบ แล้ว Cash Flow สุทธิให้เอารับ+จ่าย หรือว่าจะให้ Cash Flow ทั้งรับและจ่ายเป็นบวก แล้ว Cash Flow สุทธิ เอา รับ – จ่ายก็ได้ ไม่ว่ากัน ถูกทั้งคู่

ที่สำคัญคือ IRR จะต้องคิดจาก Cash Flow สุทธิเท่านั้น และต้องรวมปีที่ 0 ด้วย จึงต้องเขียนว่า =IRR(B9:L9)  ซึ่งแผนนี้จะออกมาได้ 1.3% เท่านั้น

แผน A + ลดหย่อนภาษี

ที่นี้หลายคนคงเริ่มสงสัยเรื่องการลดหย่อนภาษีจากเบี้ยประกันชีวิต ซึ่ง % ที่เอาไปลดหย่อนได้ ขึ้นอยู่กับฐานภาษีของแต่ละคน ไม่เท่ากัน  โดยที่ฐานภาษีสำหรับปีภาษี 2556 และ 2557 เป็นแบบขั้นบันไดดังนี้

ช่วงรายได้สุทธิ => ภาษี

  • 1 – 150,000 => 0%
  • 150,001 – 300,000 => 5%
  • 300,001 – 500,000 => 10%
  • 500,001 – 750,000 => 15%
  • 750,001 – 1,000,000 => 20%
  • 1,000,001 – 2,000,000 => 25%
  • 2,000,001 – 4,000,000 => 30%
  • 4,000,001 บาทขึ้นไป => 35%

ถ้าถามว่าผลประโยชน์จากการลดหย่อนภาษีมันจะนำมาคำนวณในนี้ยังไง ผมขอยกตัวอย่างแบบนี้ครับ สมมติว่ารายได้สุทธิของคุณหักค่าลดหย่อนทุกอย่างแล้ว อยู่ที่ปีละ 250,000 บาท แปลว่า

ถ้าไม่มีประกันชีวิต คุณต้องจ่ายภาษี = 5% * (250000-150000) = 5000 บาท/ปี (ถ้ารายได้ไม่เปลี่ยน)

ถ้าคุณจ่ายเบี้ยประกัน 51,000 บาท แปลว่าคุณจะมีรายได้สุทธิอยู่ที่ =250,000-51,000= 199,000 บาท แปลว่าคุณต้องจ่ายภาษี = 5% * (199000-150000) = 2450 บาท/ปี (ถ้ารายได้ไม่เปลี่ยน) นั่นคือ ประกันชีวิต ช่วยลดรายจ่ายของคุณไปปีละ 5000-2450 = 2550 บาท/ปี (ไม่ใช่ลดไป 51000 นะครับ!! อย่าเข้าใจผิด)

หรือจะคิดง่ายๆ ก็คือเอาอัตราภาษี * เบี้ยประกันก็ได้ (ถ้าอยู่ช่วงเดียวก็ง่ายหน่อย) เช่น =5%*51000 = 2550 บาท เช่นกัน

แต่ถ้าจะคิดเผื่อเรื่องของเวลาด้วย ถ้าคุณจ่ายเบี้ยประกันต้นปีนี้ กว่าคุณจะได้เงินภาษีคืนก็คือในต้นปีหน้า ดังนั้นคุณอาจต้องเลื่อนผลประโยชน์จากภาษีไปอีกปีนึง แต่ถ้าคุณจ่ายเบี้ยประกันปลายปี อาจไม่จำเป็นต้องเลื่อนก็ได้ เพราะเวลาไม่ห่างมากนัก

สรุปแล้ว สมมติผมจ่ายประกันต้นปี ผมจึงควรเลื่อนผลประโยชน์ภาษีออกไปปีถัดไป เราจึงปรับตารางให้เป็นดังนี้

insure-planA-tax

จะเห็นว่า IRR มีค่าเพิ่มขึ้นมาเยอะเลย อันนี้แหละน่าจะเป็นประโยชน์ของประกันชีวิต ในแง่ของการเงิน ซึ่งอาจจะเห็นผลน้อยถ้าหากว่ามีฐานภาษีที่น้อย แต่ประโยชน์จะเยอะขึ้นมาก คุณต้องจ่ายภาษีเยอะๆ เช่น 30% เป็นต้น

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

ตอนหน้า เดี๋ยวเรามาต่อเรื่องการผ่อนเงินกู้ เช่น กู้บ้าน ซึ่งเราจะมาใช้ฟังก์ชั่น PMT กันครับ

เจาะลึกฟังก์ชั่นการเงินใน Excel : ตอน 1 ปูพื้นฐานวิชาการเงิน

จากที่ผมได้ Post ใน Facebook ไปว่าจะสอนเรื่องการวิเคราะห์แผนประกันชีวิตด้วย Excel ผมมาคิดๆ ดูแล้ว ถ้าสอนแต่เรื่องนั้นอย่างเดียว คนอ่านก็คงได้แต่จำสูตรและวิธีใช้ แต่อาจไม่รู้ว่าตัวเองกำลังทำอะไรอยู่เลยก็ได้ ผมขอพูดไว้เลยว่า หากคุณไม่เข้าใจหลักการด้านการเงินระดับพื้นฐาน คุณไม่มีทางใช้ฟังก์ชั่นทางการเงินของ Excel ได้ดีหรอกครับ ส่งผลให้พลิกแพลงอะไรไม่ได้เลย… เพราะฉะนั้น ผมจะขอเขียนเรื่องนี้ยาวหน่อย และคงต้องแบ่งเป็นหลายตอน โดยเริ่มตั้งแต่ความรู้พื้นฐานด้านการเงินกันก่อนเลย แต่ไม่ต้องเป็นห่วงว่ามันจะเป็นเรื่องยาก เพราะผมจะช่วยสรุป Concept หลักๆ ให้คุณเข้าใจได้อย่างกระชับที่สุด ขอแค่เปิดใจลองทำความเข้าใจมันดู เมื่อพร้อมแล้วเรามาดูกันครับ

เงิน 100 บาทในวันนี้ กับเงิน 100 บาทใน 10 ปีข้างหน้า ไม่ได้มีมูลค่าเท่ากัน

clock-money

ถ้าให้คิดเล่นๆ ผมให้คุณเลือกเอาว่าคุณจะเอา 100 บาทวันนี้เลย หรือต้องรอ 10 ปี เพื่อเอาเงิน 100 บาท? ถ้าคุณเป็นคนปกติทั่วไปน่าจะต้องเลือกเอา 100 บาทในวันนี้ใช่มั๊ยล่ะครับ

นั่นแปลว่า ถ้าจำนวนเงินเท่ากัน เงินในปัจจุบัน (Present Value :PV) จะมีค่ากับคุณมากกว่าเงินในอนาคต (Future Value :FV) หรือในทางกลับกัน จะบอกได้ว่า เงินในอนาคตจะถูกลดทอนมูลค่าลง (Discount) เมื่อนำมาเทียบกับค่าของเงินในปัจจุบันนั่นเอง เราเรียกปรากฏการณ์นี้ว่า  Time Value of Money ครับ

คำถามจะเริ่มยากขึ้น ถ้าจำนวนเงินไม่เท่ากัน เช่น คุณจะเอา 100 บาทวันนี้เลย หรือต้องรอ 10 ปี เพื่อเอาเงิน 200 บาท แบบนี้เริ่มคิดมากขึ้นแล้วใช่มั๊ยครับ? แปลว่า คุณต้องมีวิธีคิดแล้วว่าถ้าต้องรอขนาดนั้น ต้องได้เท่าไหร่ถึงจะคุ้ม

เงินในอนาคตจะถูก Discount ลงไปเท่าไหร่?

ถ้าผมจะบอกว่า “ผมบอกไม่ได้ มันแล้วแต่คุณ” ล่ะครับ… ผมไม่ได้กวนนะ ทั้งนี้เพราะว่า การที่เงินแต่ละปีมีค่าไม่เท่ากัน เป็นเพราะเกิดจาก “ผลตอบแทนที่ต้องการหรือคาดหวังจากการลงทุน” (Required rate of return) ซึ่งเป็นสิ่งที่ขึ้นอยู่กับแต่ละบุคคลนั่นเอง

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

มันจึงมีชื่อเรียกหลายชื่อ ทั้ง Required rate of return, Interest Rate, Discount Rate, Hurdle Rate ทุกอันคือสิ่งเดียวกัน ยกตัวอย่างเช่น ถ้าเราต้องลงทุนในโปรเจคอะไรซักอย่างที่มีความเสี่ยงสูง เราคงต้องอยากได้ผลตอบแทนสูงๆ มาชดเชยความเสี่ยงนั้นใช่มั๊ยครับ? นั่นแหละคือ Required rate of return ของเรา ซึ่งแต่ละคนไม่จำเป็นต้องการเท่ากัน

ใครมีทางเลือกในชีวิตเยอะ อาจต้องการ Required rate of return สูงกว่า เพราะถ้าโปรเจคนี้ผลตอบแทนไม่ดีพอ เค้าก็สามารถไปลงทุนอย่างอื่นที่ผลตอบแทนดีกว่าได้ ต่างจากคนที่ไม่ค่อยมีทางเลือก ซึ่งอาจมีแค่เงินฝากประจำเป็นทางเลือกสำรอง ซึ่งเป็นทางเลือกที่ผลตอบแทนค่อนข้างต่ำ

การวิเคราะห์เรื่อง Time Value of Money ด้วยการวาดรูป

เวลามีเรื่องของ Time Value of Money ขึ้นมาเมือไหร่ สิ่งแรกที่ผมอยากจะให้เราคิดเลย ก็คือ การคิด หรือวาดรูปออกมาเป็นภาพ (เรียกว่า Cash flow Diagram) ซึ่งการคิดเป็นภาพเป็นพื้นฐานที่ดีในการที่เราจะแปลงภาพเหล่านี้เป็นตารางใน Excel ภายหลัง

ซึ่งเราจะวาดภาพที่มีองค์ประกอบดังนี้

  • เส้นแกนนอนเป็นเวลา โดยแบ่งออกเป็นแต่ละช่วง แล้วแต่โจทย์ว่ามีการคิดดอกเบี้ยช่วงหนึ่งๆ นานเท่าไหร่
    • เราสามารถระบุจุดของเวลาได้ 2 แบบ คือ Begin of Period กับ End of Period แต่ปกติแล้วจะนิยมเขียนเป็น End of Period มากกว่า
    • เวลาที่ Period 0 ที่เป็นจุดเริ่มต้น คือเวลาปัจจุบันที่เริ่มมี Action อะไรบางอย่าง เช่น การลงทุนเงินของเรา เป็นต้น เช่น เริ่ม 1 พค. 57
    • เมื่อเวลาผ่านไป 1 Period (เช่น 1 ปี) ก็จะหลายเป็น Period ที่ 1 คือวันที่ 1 พค. 58 เป็นต้น
  • ลูกศรชี้ขึ้นแทนเงินที่ได้รับ (เงินเป็น +)
  • ลูกศรชี้ลงแทนเงินที่จ่ายออกไป (เงินเป็น -)
  • มีการระบุผลตอบแทนที่คาดหวัง หรือ อัตราดอกเบี้ยที่จะใช้ในแต่ละช่วง ว่ามีค่าเท่าไหร่

ตัวอย่าง

ถ้าคุณลงทุน 100 บาท ใน Project A แล้วคาดว่าอีก 2 ปีข้างหน้าจะได้เงินคืนมา 120 บาท โดยมี Discount Rate ที่ 5% แบบนี้เราจะเขียนรูปได้ดังนี้

CF-01

 

คำศัพท์พื้นฐานเกี่ยวกับสูตรทางการเงินของ Excel

  • PV = Present Value คือ หามูลค่าเงินในปัจจุบัน
  • FV = Future Value คือ หามูลค่าเงินในอนาคต
  • Rate = อัตราดอกเบี้ยคาดหวัง หรือ Discount Rate
  • Nper = จำนวน Period ที่จะทำการเคลื่อนย้าย Cash Flow
  • PMT = การแบ่งเงินเพื่อผ่อนชำระเป็นงวด งวดละเท่าๆ กัน (เช่น การคำนวณเงินกู้ซื้อบ้าน) ถ้าไม่ได้มีการผ่อนก็ใส่เลข 0 ไป

PV

ดังนั้น หากเราอยากจะรู้ว่าเงิน 120 บาทใน 2 ปีข้างหน้า (Future Value) จะมีมูลค่าในปัจจุบัน (Present Value) เท่าไหร่? เราสามารถใช้ฟังก์ชั่น PV ใน Excel มาช่วยได้

  • =PV(rate,nper,pmt,[fv],[type])
  • =PV(5%,2,0,120) = -108.84

นั่นหมายถึงว่า เงิน 120 บาทใน 2 ปีข้างหน้า ถ้า Discount กลับมาที่เวลาปัจจุบันจะมีมูลค่า 108.84 บาท

ส่วนเครื่องหมายติดลบเพราะ Excel จะทำให้เครื่องหมายของ PV กับ FV ตรงข้ามกันให้โดยอัตโนมัติ ถ้าเราไม่อยากให้มันติดลบ ให้เราใส่ค่า FV เป็นติดลบไปก่อน มันจะได้กลายเป็นบวกตามปกติ ดังนี้

  • =PV(5%,2,0,-120) = 108.84

CF-02

 

นั่นแปลว่าผลตอบแทนการลงทุนมีมูลค่า 108.84 บาท เทียบกับการลงทุน 100 บาท เมื่อนำมาหักลบกัน จะได้สิ่งที่เรียกว่า Net Present Value หรือ NPV ซึ่งก็เป็นอีกฟังก์ชั่นหนึ่งของ Excel เช่นกัน ซึ่งในที่นี้ NPV = 8.84 บาท ซึ่งถ้า NPV > 0 แปลว่าคุ้มค่า สรุป NPV คือเอา Cash Flow ทั้งหมด ย้ายมา ณ เวลาปัจจุบัน แล้วหา Cash flow สุทธิ

CF-03

NPV ใน Excel

แต่ทว่าฟังก์ชั่น NPV ใน Excel ไม่ได้มีความหมายอย่างเดียวกับวิชา Finance ซะทีเดียว เพราะสิ่งที่มันทำ คือ เป็นการ ย้าย Cash flow สุทธิในอนาคตทุกตัวกลับมาที่ปัจจุบัน แต่มันยังไม่ได้หาค่าสุทธิกับ Cash flow ที่อยู่ Period ที่ 0 ให้

ทั้งนี้มีข้อควรระวัง 2 ข้อ คือ

  • ปีไหนไม่มี Cash Flow จะต้องใส่เลข 0 ลงไปด้วย ห้ามปล่อยเป็นค่าว่าง ไม่งั้นมันจะคิดผิด
  • วิธีการ input ค่าของฟังก์ชั่น NPV จะต้องเริ่มลากจาก Period ที่ 1 เป็นต้นไป ห้ามลากตั้งแต่ Period ที่ 0

ผิด เพราะลืมใส่ 0 ลงไปในปีที่ไม่มี Cash Flow

CF-04

ผิด เพราะลากคลุม Cash Flow ปีที่ 0 ไปด้วย

CF-05

ถูกต้อง

CF-06

จากนั้นค่อยเอาผลจากสูตร NPV มาสุทธิกับ Cash Flow ปีที่ 0 ภายหลัง ก็จะได้ NPV จริงๆ เป็น 8.84 นั่นเอง

FV

หากเราลองปรับค่าการลงทุนในปัจจุบัน ให้เป็นเงินในอนาคตบ้าง แบบนี้ก็ทำได้เช่นกัน โดยใช้สูตร FV ดังนี้

  • =FV(rate,nper,pmt,[pv],[type])
  • =FV(5%,2,0,100) = -110.25 บาท

CF-07

ซึ่งการตีความผลลัพธ์จะได้ว่า มูลค่าเงินลงทุน น้อยกว่าผลตอบแทนที่ได้รับในเวลาเดียวกัน แปลว่าเป็นการลงทุนที่คุ้มเช่นกัน แต่เราจะไม่เรียกมันว่า NPV แล้ว เพราะไม่ใช่มูลค่าในปัจจุบัน

วิธีนี้ มักใช้กับคำถามที่ว่า เงินของฉันในตอนนี้ จะมีมูลค่าเท่าไหร่ในอีก xxx ปีข้างหน้า ซึ่งถ้า Discount rate เยอะๆ เพื่อนๆ จะตกใจในมูลค่าของมันเลยทีเดียว ดังคำกล่าวของไอน์สไตน์ที่ว่า พลังแห่งการคิดดอกเบี้ยทบต้น เป็นสิ่งที่ทรงพลังที่สุด เช่น ถ้าคุณมีเงิน 100 บาท แล้วไปหาแหล่งลงทุนที่ให้ผลตอบแทนได้ 10% ทุกปี ในอีก 60 ปีข้างหน้าเงินนี้จะมีมูลค่าถึง =FV(10%,60,0,-100) = 30,448.16 บาท เลยล่ะ!!

แล้วถ้าจะหาว่า interest rate เท่าไหร่ที่ทำให้คุ้มค่าพอดี

หลายคนคงเริ่มสงสัยแล้ว ว่า Discount Rate ที่เท่าไหร่ ที่จะทำให้การลงทุนนี้ “คุ้มค่าพอดี” มันก็คือ Discount Rate ที่ทำให้ NPV เป็น 0 พอดีนั่นเอง แต่เรามีคำศัพท์เรียกสิ่งนี้โดยเฉพาะว่า IRR หรือ Internal Rate of Return นั่นเอง ซึ่งเป็นสิ่งที่ Excel มีฟังก์ชั่นคำนวณให้โดยเฉพาะเช่นกัน

IRR

=IRR(values,guess)

ในช่อง Value ให้เราใส่ Range ที่เป็น Cash Flow สุทธิ ตั้งแต่ปีที่ 0 ถึงปีสุดท้ายเข้าไปเป็น Range

ทั้งนี้มีข้อควรระวัง 2 ข้อ คือ

  • ปีไหนไม่มี Cash Flow จะต้องใส่เลข 0 ลงไปด้วย ห้ามปล่อยเป็นค่าว่าง ไม่งั้นมันจะคิดผิด
  • วิธีการ input ค่าของฟังก์ชั่น IRR จะต้องเริ่มลากจาก Period ที่ 0 เป็นต้นไป ไม่เหมือน NPV

ผิด เพราะลืมใส่ 0 ลงไปในปีที่ไม่มี Cash Flow

CF-08

CF-09

สรุปเกณฑ์การตัดสินใจการลงทุน Project

คุณสามารถใช้เกณฑ์การตัดสินใจว่าจะลงทุนสิ่งที่เราสนใจ หรือ Project หรือไม่ อยู่ 2 แนวทาง คือ

  1. หาอัตราผลตอบแทนของ Project ด้วย IRR แล้วเทียบกับผลตอบแทนคาดหวัง IRR มากกว่าผลตอบแทนคาดหวังก็น่าลงทุน แต่ถ้ามีหลาย Project ที่ IRR มากกว่า แล้วมีเงินลงทุนจำกัด ก็ให้เลือก Project ที่ IRR สูงสุด
  2. ใช้ผลตอบแทนคาดหวัง ในการ Discount มูลค่าเงินที่จะได้ในอนาคต แล้วหาค่า NPV ถ้ามากกว่า 0 ก็น่าลงทุน

เอาล่ะครับ เมื่อเรามีความรู้พื้นฐานทางด้านการเงินแล้ว ตอนต่อไปเราจะมาวิเคราะห์แผนประกันชีวิตกันจริงๆ แล้วล่ะ 

เทคนิคลับสุดยอด VLOOKUP ข้อมูลเร็วขึ้น 100 เท่า! (ขึ้นไป)

เพื่อนๆ เคยทำงานที่ต้อง Lookup ข้อมูลเยอะๆ ใน Excel รึเปล่าครับ? หลายๆคนอาจใช้เทคนิคปรับโหมดการคำนวณจาก Automatic เป็น Manual (ที่ต้องกด F9 เพื่อคำนวณ) เพื่อให้มันคำนวณทีเดียวเมื่อเราเขียนสูตรทุกอย่างพร้อมแล้ว ที่ต้องทำอย่างนั้นเพราะว่ามันนานมากกกกก

สิ่งที่ทำให้มันช้ามักจะเกิดขึ้นกับสูตรประเภทที่ต้อง Lookup ข้อมูล โดยเฉพาะอย่างยิ่งถ้ามีข้อมูลที่ต้อง Lookup เป็นหมื่นหรือเป็นแสนแถว บางทีรอหลายชั่วโมงก็ยังไม่เสร็จ และถ้าสังเกตให้ดี การ Lookup ที่ช้าจะเป็นการ Lookup ประเภท Exact Match เท่านั้น (ต้องเจอผลลัพธ์เป๊ะๆ) ซึ่งจะไม่เกิดอาการช้ากับการ Lookup แบบ Approximate Match ซึ่งจะเร็วกว่ามาก

วันนี้ผมจะมาแนะนำเทคนิคที่จะทำให้ VLOOKUP แบบหาเป๊ะๆ แต่ไม่ต้องรอนานอย่างที่เคย ทำยังไงมาดูกันครับ (มีวีดีโออยู่ข้างล่าง) 100xVLOOKUP

ทบทวนสูตร VLOOKUP กันซักนิด

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

=VLOOKUP(ค้นหาคำนี้,จากคอลัมน์แรกในตารางนี้,เมื่อเจอแล้วให้เอาค่าในคอลัมน์ที่ xx ของตารางกลับมา (นับซ้ายไปขวา), [ใช้โหมด Lookup แบบ Approximate Match หรือ Exact Match])

VLOOKUP มี 2 โหมดด้วยกัน เรามาดูกันว่าทั้งสองแบบต่างกันยังไง?

  • Approximate Match (ตั้งค่า Range Lookup เป็น TRUE หรือ 1) แปลว่า แม้ไม่เจอค่าที่ต้องการหาเป๊ะๆ ก็ยังส่งค่าบางอย่างกลับมาได้ โดยมีหลักการดังนี้
    • ทำงานเร็วมาก เพราะทำงานด้วยการค้นหาแบบ Binary Search (ลองอ่านข้างล่างสุดดูวิธีการทำงานได้ แต่ไม่เข้าใจไม่เป็นไรครับ)
    • มีข้อจำกัดคือ ข้อมูล Reference (คอลัมน์แรกของ table_array) ต้องเรียงน้อยไปมากเท่านั้น (ไม่งั้นผลลัพธ์จะมั่ว)
    • ถ้าหากข้อมูลเรียงจากน้อยไปมากแล้ว : มันจะวิ่งหาในคอลัมน์แรกใน table_array ลงจากบนลงล่าง จนเจอข้อมูลที่มากกว่า lookup_value แล้วเด้งกลับขึ้นข้างบน 1 ช่อง
    • จากนั้นจะเอาค่าในคอลัมน์ที่ col_index_num กลับมา
  • Exact Match (ตั้งค่า Range Lookup เป็น FALSE หรือ 0) แปลว่า ต้องเจอค่า/คำที่ต้องการเป๊ะๆ จึงจะส่งค่ากลับมา ถ้าไม่เจอจะ Error เลย
    • ทำงานช้า(มาก) เพราะทำงานด้วยการค้นหาแบบ Linear Search (ไม่เข้าใจไม่เป็นไรครับ)
    • ข้อดีคือ ข้อมูล Reference (คอลัมน์แรกของ table_array) ไม่จำเป็นต้องเรียงจากน้อยไปมาก
    • วิ่งหาในคอลัมน์แรกใน table_array ลงจากบนลงล่าง จนเจอข้อมูลเป๊ะๆ ถ้าไม่เจอขึ้น Error

ซึ่งบ่อยครั้งเราต้องการได้ผลลัพธ์จากการ Lookup แบบเป๊ะๆ มากกว่า (คือถ้าไม่เจอก็ให้ขึ้น Error ไปเลย) เราจึงมักจะเลือกที่จะใช้งาน VLOOKUP แบบ Exact Match เสมอๆ เพราะถ้าเลือกใช้แบบ Approximate Match ข้อมูลก็จะมั่วเลย เพราะไปดึงข้อมูลจากบรรทัดอื่นมาแสดง

เคล็ดลับอยู่ตรงนี้แหละครับ!! เราจะไม่ใช้สูตร Exact Match เพราะมันช้า เราจะใช้ VLOOKUP แบบ Approximate Match มาช่วยแทนเพราะเร็วกว่า แต่จะกำจัดผลลัพธ์ที่ไม่ต้องการออกไปได้ยังไง มาดูกัน

Concept ของเทคนิคลับ ทำยังไงให้เร็ว!?

ก่อนอื่น เราต้องเรียงคอลัมน์แรกในตารางอ้างอิงจากน้อยไปมากก่อน จึงจะใช้สูตร VLOOKUP แบบ TRUE ได้

จากนั้นเราก็สามารถ แต่ใส่เงื่อนไขเพื่อเช็คว่า “ถ้าเอาคอลัมน์แรกกลับมา มันจะเท่ากับ Lookup_value รึเปล่า?” ถ้าเท่ากันแสดงว่าใช้ได้ ให้เอาค่าจากคอลัมน์ 3 ที่ต้องการกลับมา ถ้าไม่เท่า แสดงว่าใช้ไม่ได้ ก็ให้ขึ้น Error ไป

  • เงื่อนไขที่เช็ค =VLOOKUP(lookup_value, table_array, 1, TRUE)=lookup_value
  • กรณีใช้ได้ (จริง) : =VLOOKUP(lookup_value, table_array, 3, TRUE)
  • กรณีใช้ไม่ได้ (เท็จ) : ให้ขึ้นข้อความ Error เช่น = NA() หรือจะขึ้น =”Error” หรือ =”ไม่เจอจ้า” ก็ได้ครับ…
  • =IF(VLOOKUP(lookup_value, table_array, 1, TRUE)=lookup_value,VLOOKUP(lookup_value, table_array, 3, TRUE),NA() )

สรุปวิธีเขียนสูตร VLOOKUP ให้เร็วขึ้น 100 เท่า!! (แต่ต้องเรียงคอลัมน์แรกของ table_array ก่อน)

=IF(VLOOKUP(lookup_value, table_array, 1, TRUE)=lookup_value,VLOOKUP(lookup_value, table_array, col_index_num, TRUE),NA() )

เพียงแค่นี้ สูตร VLOOKUP คุณก็จะเร็วขึ้นสุดๆ จนเทียบกับของเดิมไม่ได้เลย ไม่เชื่อลองดูในวีดีโอได้ครับ Enjoy VLOOOKUP นะครับ!!

VDO ตัวอย่าง

Tips: Binary Search  ทำงานยังไง?

  1. หาว่ามีข้อมูลกี่แถว เช่น มี N แถว
  2. หาจุดแบ่งครึ่ง คิดจาก (N+1)/2 แล้วปัดเศษทิ้ง
  3. เทียบ Lookup Value กับค่าที่อยู่จุดแบ่งครึ่ง ว่าค่า Lookup Value เท่ากับ มากกว่า หรือ น้อยกว่า
  4. ถ้าค่า lookup value เท่ากับ ก็เอาตัวแบ่งครึ่งนั้นเลย / ถ้า lookup value น้อยกว่า ก็จะทำการค้นหาจากครึ่งบนต่อ / ถ้าค่า lookup value มากกว่า ก็จะทำการค้นหาจากครึ่งล่างต่อ
  5. ไล่ Step 1-4 ไปเรื่อยๆ จนเหลือตัวเดียว ตัวนั้นคือผลลัพธ์ที่ได้

ตัวอย่าง binary-search

อธิบาย P-004

  • รอบ 1.  มี 8 แถว  (P-002 ถึง P-019) จุดแบ่งครึ่ง คือ P-017 => หาครึ่งบนต่อ เพราะ P-004 น้อยกว่า P-017
  • รอบ 2. มี 4 แถว  (P-002 ถึง P-017) จุดแบ่งครึ่ง คือ P-005=> หาครึ่งบนต่อ เพราะ P-004 น้อยกว่า P-005
  • รอบ 3. จบที่ P-002 เพราะเหลือตัวเดียว

อธิบาย P-018

  • รอบ 1. มี 8 แถว  (P-002 ถึง P-019) จุดแบ่งครึ่ง คือ P-017 => หาครึ่งล่างต่อ เพราะ P-018 มากกว่า P-017
  • รอบ 2. มี 5 แถว  (P-017 ถึง P-019) จุดแบ่งครึ่ง คือ P-016 => หาครึ่งล่างต่อ เพราะ P-018 มากกว่า P-016
  • รอบ 3. มี 3 แถว  (P-016 ถึง P-019)จุดแบ่งครึ่ง คือ P-020 => หาครึ่งบนต่อ เพราะ P-018 น้อยกว่า P-020
  • รอบ 4. จบที่ P-016 เพราะเหลือตัวเดียว

แฉ 10 ความลับของ Excel ภาค 2 : เรื่องง่ายๆ ที่หลายคนไม่รู้

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

เมื่อพร้อมแล้วไปลุยกันเลย!

1. Copy Paste ง่ายๆ ด้วยการกดคลิ๊กขวาแล้วลากเม้าส์

right-click-menu

รู้หรือไม่ว่า เราสามารถเลือก Range ของข้อมูลที่ต้องการ จากนั้นเลื่อนเม้าไปที่ขอบของ Range ที่เราเลือกไว้ (มันจะกลายเป็นรูปลูกศรชี้ 4 ทิศ พร้อมกัน ที่ปกติเราเอาไว้ Move ข้อมูล) แล้วกดคลิ๊กขวาค้างไว้่ แล้วลากไปยังเป้าหมายที่ต้องการ จากนั้นจะมีเมนูขึ้นมาให้เพียบเลย (คล้ายๆตอนกด Paste Special) ซึ่งวิธีนี้มีข้อดีคือ จะเห็นชัดเจนก่อนทำการ Paste ว่า ช่องเป้าหมายจะครอบคลุมถึงแค่ไหน

2. Copy แล้ว Paste Value Only แบบเร็วสุดๆ ด้วย Ctrl+C => Ctrl+V => Ctrl =>V

เรื่องการ Copy ยังมีเคล็ดลับอีก เพราะเป็นเรื่องที่เราใช้บ่อย ผมเลยขอพูดอีกเรื่องนึงนะครับ หากเราต้องการจะ Copy แค่ Value แบบไม่เอา Format เราสามารถทำตาม Step นี้ได้เลยครับ

ctrl-menu

  1. เลือก Range ต้นฉบับแล้วกด Ctrl+C เพื่อ Copy
  2. เลือกช่องปลายทางแล้วกด Ctrl+V เพื่อ Paste
  3. กด Ctrl เพื่อเข้าสู่ เมนู Paste Options
  4. กด V เพื่อ Paste Values (กดตัวอื่นๆก็ได้ ลองเลื่อนดู Shortcut เอาครับ)

ที่นำเสนอแบบนี้เพราะว่าจำง่ายมาก คือ Ctrl+V => Ctrl => V แถวเร็วสุดๆด้วย ยังไงลองใช้ดูนะครับ

3. สร้างตาราง Pivot Table ที่ Filter ตัวเลือกแตกต่างกันโดยอัตโนมัติ

report-filter report-filter2

 

เวลาเรา Pivot ข้อมูล แล้วมีการใช้ Filter ข้อมูลโดย อยากให้ 1 ตาราง Pivot เป็นของแต่ละ Product

ปกติแล้วเราก็อาจจะใช้วิธี Pivot ข้อมูลแล้วเลือก Filter ทีละอัน แล้ว Copy เป็นตารางใหม่ เช่น เลือก Product ของเล่น 1 ที แล้ว Copy Pivot ออกมาเป็นอีกตาราง แล้วค่อยเปลี่ยน Filter เป็น Product เครื่องใช้ไฟฟ้า.. ทำแบบนี้ไปเรื่อยๆ เป็นต้น (ถ้ามี item เป็นสิบอันนี่เหนื่อยเลยครับ)

ผมจะบอกว่า Excel มีเครื่องมือทำรายงานแบบนี้ให้โดยอัตโนมัติ โดยทำตามนี้

  1. ไปกดที่ Ribbon Options ของ Pivot Table (ต้องกดเลือกบริเวณที่เป็น Pivot ก่อน)
  2. ติ๊กปุ่มสามเหลี่ยมใต้ที่ตั้งชื่อตาราง Pivot
  3. เลือก Show Report Filter Page
  4. เลือก Field ที่ต้องการ Filter
  5. Report จะถูกสร้างขึ้นโดยอัตโนมัติ โดยแยก 1 ตาราง ต่อ 1 sheet (มีการตั้งชื่อ Sheet ให้เป็นแต่ละ item ของสิ่งที่เรา Filter ให้ด้วย)

4. ปิดคำสั่ง Generate GetPivotData

เคล็ดลับอันนี้หลายๆคนอาจจะเคยรู้แล้ว แต่ผมเชื่อว่ายังมีหลายคนที่ยังไม่รู้ครับ แถมเป็นเรื่องที่ใช้เครื่องมือใกล้เคียงกับเคล็ดลับข้างบน เลยต้องขอพูดถึงซักหน่อย

getpivotdata

ปกติเวลาเราเขียนสูตร แล้วเข้าไปคลิ๊กในบริเวณที่เป็นตาราง Pivot Table เราอาจจะเห็นสูตรพิลึกๆ ขึ้นมาอย่างเช่น
=GETPIVOTDATA(“Sum of xxx”,$A$3,”yyy”,zzz,”aaa”,”bbb”) ซึ่งทำให้เกิดปัญหาว่าเรา Copy สูตรไปที่อื่น มันก็ไม่เลื่อนช่องให้แบบเวลาใช้ Cell Reference ปกติ ซึ่งมีวิธีแก้เพื่อให้เวลาใช้สูตรแล้วคลิ๊กเข้าไปใน Pivot Table แล้วยังเป็น Cell ปกติได้ โดยให้ทำดังนี้

  1. ไปกดที่ Ribbon Options ของ Pivot Table (ต้องกดเลือกบริเวณที่เป็น Pivot ก่อน)
  2. ติ๊กปุ่มสามเหลี่ยมใต้ที่ตั้งชื่อตาราง Pivot
  3. เอาติ๊กคำว่า Generate Getpivotdata ออก

อาจมีคนสงสัย แล้ว GETPIVOTDATA มันมีข้อดียังไง? จริงๆแล้วมันก็มีข้อดีของมันอยู่ครับ นั่นก็คือ เวลาเรา Link ข้อมูลมาแล้ว ไม่ว่าตาราง Pivot Table จะถูกพลิกบิดมุมมองไปมาแค่ไหน ค่าที่เรา Link ไว้ก็จะยังเป็นค่าเดิมเสมอ ต่างจากการใช้ Cell Reference ปกติ ที่อาจกลายเป็นค่าใหม่ที่เราไม่ต้องการไปโดยไม่รู้ตัวเลยก็ได้

5. จริงๆ แล้ว Keyboard Shortcut มันจำง่ายกว่าที่คิดนะ

เคยสังเกตมั๊ยว่า Keyboard Shortcut ใน Excel หลายๆตัวนั้นจำง่ายกว่าที่คิด

 

ถ้าเป็นคำสั่ง Basic เช่น Copy แทนด้วย Ctrl+C เพราะ คำว่า Copy แทนด้วย C หรือเช่นทำให้ตัวอักษรเป็นตัวหนา (Bold) ด้วย Ctrl+B นั้นก็ ไม่น่าแปลงใจมากนัก แต่ว่าจริงๆแล้ว แม้จะเป็นเครื่องหมายแปลกๆ ก็จำง่ายกว่าที่คิดนะครับ เพราะ Excel ตั้งใจในการเลือกใช้ตัวแทนคีย์ลัดได้น่าสนใจทีเดียว เช่น

keyboard

  • Ctrl+% เพื่อแปลงเป็นหน่วย % (จริงๆ ต้องกด Ctrl+Shift+5 เพราะ Shift +5 คือตัว % แต่ถ้าต้องจำว่า Ctrl+Shift+5 จะไม่มีทางจำได้เลย)
  • Ctrl+^ ก็เพื่อแปลงเป็นเลข Scientific E ยกกำลัง (เพราะเป็นเครื่องหมายยกกำลัง)
  • Ctrl+$ ก็เพื่อแปลงเป็นรูปแบบสกุลเงิน
  • Ctrl+# ก็เพื่อแปลงเป็นวันที่ (เพราะในโปรแกรม Access ก็ใส่วันที่ในเครื่องหมาย #)
  • Ctrl+@ ก็เพื่อแปลงเป็นเวลา เพราะ เครื่องหมาย@ ก็ดูเจาะจง คล้ายว่าจะระบุว่า ณ กี่โมง
  • Ctrl+: ใส่เวลาปัจจุบัน เพราะเหมือนเครื่องหมายคั่น ชม:นาที
  • Ctrl+* เลือก Range ทั้งหมด เพราะ * แทนความหมายว่าทั้งหมด ในภาษาฐานข้อมูล
  • จริงๆ มีอีกเยอะเลย ถ้าเพื่อนๆ ถ้าเจอตัวอื่นที่ดู Make Sense แบบตัวอย่างข้างบนนี้ก็ Comment ไว้ได้นะครับ

6. รู้หรือไม่ว่า Excel เวอร์ชั่นแรกนั้นออกให้กับ Apple ไม่ใช่ Microsoft

excel1

อันนี้เป็นเกร็ดเล็กเกร็ดน้อยครับ หลายๆคนอาจรู้ว่า Excel ไม่ใช่โปรแกรม Spreadsheet อันแรกของโลก เพราะก่อนหน้าที่จะมี Excel นั้นก็มีโปรแกรม Spreadsheet อื่นๆ เกิดขึ้นมาก่อนมากมาย โดยโปรแกรมแรกของโลกนั้น คือโปรแกรมที่มีชื่อว่า VisiCalc ซึ่งออกมาในปี ค.ศ. 1978 โน่นแน่ะ ต่อมาก็มีอีกหลายโปรแกรม แต่ตัวทีมีชื่อเสียงมากที่พวกเราน่าจะพอรู้จักก็คือ Lotus 1-2-3 ซึ่งออกมาในปี ค.ศ. 1983 และในที่สุด Excel เวอร์ชั่นแรกก็ออกมาในปี คศ.1985 แต่ที่แปลกคือ Excel 1.0 ซึ่งเป็น Version แรกนั้น กลับออกมาให้กับสำหรับเครื่อง Apple Macintosh ก่อนที่จะออก Version 2.0 ใน  Windows ซะอีกนะครับ ทั้งที่สร้างโดย Microsoft เองนี่แหละ! สาเหตุอาจเป็นเพราะความสามารถของการทำงานด้วยกราฟิก กับการใช้เม้าส์จิ้มเมนูต่างๆได้นี่แหละ ที่ Microsoft จำเป็นต้องเลือกลงให้ Macintosh ก่อน PC

7. ฟังก์ชั่น TRIM ไม่ได้ตัดแค่ Space ที่หัวกับท้ายเท่านั้นนะ

trim-example

หลายคนอาจคิดว่าฟังก์ชั่น TRIM มันแค่เอาไว้ตัด space หัวท้ายของคำเท่านั้น (เพราะชื่อมันดูเหมือนเอาไว้เล็มๆ ….)
แต่จริงๆ มันตัด space ทั้งหมด ยกเว้นจะเหลือ space ระหว่างคำไว้ให้แค่สูงสุด 1 เคาะเท่านั้น space (ตัวอื่นๆจะถูกตัดทิ้งทั้งหมดครับ) ตามรูป

8. ในคำสั่ง FIND/REPLACE เราสามารถกำหนดรูปแบบที่จะค้นหาได้ด้วยนะ

find-replace

ปกติเวลาเราค้นหาคำ เราก็จะกด Ctrl+F เพื่อค้นหา แล้วก็พิมพ์คำที่ต้องการลงไป แต่จริงๆแล้วมันสามารถ กด Option แล้วเลือก Format เพื่อหาสิ่งที่ต้องการได้แบบเจาะจงยิ่งขึ้น เช่น หาช่องที่ถมสีเหลือง เป็นตัวหนา มี Number format ที่กำหนด หรือว่ามีการ Lock / Unlock cell เอาไว้ก็ยังได้

นอกจากนี้ บางทีหลายๆคนเวลากดหาข้อมูลก็อาจหาไม่เจอ ทั้งๆที่เห็นอยู่ตำตา สาเหตุอาจเป็นเพราะเพื่อนๆ เลือกโหมดการหาผิด
เช่น ใน Look in จะมีให้เลือก 3 อัน คือ Formula , Value, Comment โดยปกติจะเลือกไว้ที่ Formula ซึ่งมันจะหาสิ่งที่พิมพ์ลงไปใน Formula Bar จริงๆ ไม่ใช่ผลลัพธ์จากการคำนวณ หากเราต้องการหาคำที่อยู่ในผลลัพธ์การคำนวณจะต้องกดหาใน Values นะครับ

9. เราสามารถ Search หาฟังก์ชั่นได้ด้วยนะ

search-function

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

10. รู้จักใช้เครื่องหมาย Wildcard ใน Function ค้นหาข้อมูล

wildcard_joker_main2

เวลาเราใช้ Function หลายๆ อย่างเพื่อค้นหาข้อมูล เช่น SEARCH หรือ VLOOKUP หรือ MATCH จริงๆเราสามารถค้นหาคำที่มีลักษณะคล้ายกับคำที่กำหนดได้ ด้วยการใช้เครื่องหมาย Wildcard ได้

คำว่า Wildcard นั้นหากเปรียบเทียบกับการเล่นไพ่ มันก็เปรียบเสมือนตัวโจ๊กเกอร์ที่แทนไพ่อะไรก็ได้ ใน Excel ก็เช่นกัน เครื่องหมาย Wildcard คือตัวที่ใช้แทนตัวอักษรอะไรก็ได้ ซึ่งมีอยู่ 2 ตัว โดยมีรายละเอียดดังนี้

  • เครื่องหมายคำถาม (?) แทนตัวอักษรใดๆก็ได้จำนวน 1 ตัวอักษร
    • เช่น “b?t” จะเจอคำที่มี 3 ตัวอักษร ที่ขึ้นต้นด้วย b และลงท้ายด้วย t เช่น bat และ but แต่ไม่จอ bt (เพราะตรงกลางไม่มีตัวอักษร)
  • เครื่องหมายดอกจัน (*) แทนตัวอักษรใดๆก็ได้จำนวนกี่ตัวอักษรก็ได้
    • เช่น “b*t” จะเจอทุกคำที่ขึ้นต้นด้วย b และ ลงท้ายด้วย t เช่น bat, but, bt, beast, boot
    • เช่น “*ing” จะเจอทุกคำที่ลงท้ายด้วย ing เช่น interesting, sing, ping
    • เช่น “a*” จะเจอทุกคำที่ขึ้นต้นด้วย a รวมถึง a ตัวเดียวด้วย
  • ลองใช้ผสมๆ กัน
    • เช่น “a?*” จะเจอทุกคำที่ขึ้นต้นด้วย a แต่ไม่รวมถึงคำที่มี a แค่ตัวเดียว
  • ถ้าอยากจะหาคำที่มีเครื่องหมายคำถาม หรือ ดอกจันจริงๆ ในข้อความ ให้ใส่เครื่องหมาย ~ นำหน้าอักษรนั้นๆ เช่น ต้องการหาคำว่า star*wars ให้ใส่ใน criteria ว่า “star~*wars”

ตัวอย่าง:

=MATCH(“*excel*”,A1:B10,0) จะเจอแถวแรกที่มีคำว่า  excel ผสมอยู่เป็นส่วนประกอบ
เช่นเป็นคำว่า inwexcel หรือแม้แต่ i love Excel very much

ถ้าอยากให้เป็น Cell Reference อาจใช้แบบนี้ก็ได้ครับ =MATCH(“”*”&C1&”*”,A1:B10,0)

ฟังก์ชั่นที่สามารถใช้ Wildcard ได้มีดังนี้

  • AVERAGEIF
  • AVERAGEIFS
  • COUNTIF
  • COUNTIFS
  • DPRODUCT
  • DSTDEVP
  • DSUM
  • DVARP
  • HLOOKUP
  • MATCH
  • SEARCH
  • SEARCHB
  • SUMIF
  • SUMIFS
  • VLOOKUP

ประเด็นที่สำคัญอีกอันคือ ฟังก์ชั่นที่รองรับ Wildcard มันจะไม่สนใจตัวพิมพ์เล็กพิมพ์ใหญ่นะครับ เช่น VLOOOKUP หาคำว่า INWEXCEL ธรรมดาก็เเจอคำว่า inwexcel ได้เหมือนกัน อันนี้อาจต้องระวัง

 

[ร่วมสนุกชิงรางวัล] เพื่อนๆ ช่วย แชร์เคล็ดลับเจ๋งๆ หากอยู่ใน Top 10 Like ได้หนังสือฟรี!

กติกาคือให้ไป Post เคล็ดลับที่คิดว่าเจ๋ง บน Wall ใน Facebook Page inwexcel หรือจะ บน Wall Facebook ของตัวเอง ก็ได้ (ถ้าคิดว่า Post ที่ตัวเองแล้วจะได้ Like จะเยอะกว่า) แต่ว่าทั้งสองวิธีนี้จะต้องใส่ Hashtag คำว่า #inwexcelsecret ไว้ด้วย ผมจะได้ Track ได้

Post ไหนที่มีคนกด Like เยอะที่สุด 10 อันดับ จะได้หนังสือที่ผมกำลังจัดทำอยู่ ไปฟรีๆ ครับ

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

ไว้หนังสือใกล้จะเสร็จเมื่อไหร่ จะแจ้งอีกทีครับ ตอนนี้เนื้อหาใกล้เสร็จแล้ว