KPIs คืออะไร

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

ความหมายของ KPIs

KPI ย่อมาจากคำว่า Key Performance Indicator หมายถึง ตัวชี้วัดผลงานหลักที่แสดงให้เห็นว่าเป้าหมายของภาระกิจนั้นๆประสบความสำเร็จหรือล้มเหลว

มาดูว่า KPIs แบ่งออกเป็นกี่ประเภท

1 KPIs ตามหน้าที่งาน(Job KPIs)

หมายถึง ตัวชี้วัดผลงานที่สะท้อนให้เห็นถึงความสำเร็จของการปฏิบัติงานเทียบกับผลที่องค์กรคาดหวังจากตำแหน่งงานนั้นๆ ถ้าลักษณะงานไม่เปลี่ยน KPIs จะเป็นตัวเดิม KPIs ตำแหน่งงานเดียวกันในองค์กรต่างๆจะไม่แตกต่างกัน

2 KPIs เชิงกลยุทธ์(Strategic KPIs)

หมายถึง ตัวชี้วัดผลงานที่แสดงให้เห็นถึงความสำเร็จหรือล้มเหลวของกลยุทธ์หรือวัตถุประสงค์กลยุทธ์ ซึ่ง KPIs นี้มีการเปลี่ยนแปลงตามกลยุทธ์ที่เปลี่ยนไปในแต่ละช่วงเวลา

นอกจากนี้ยังสามารถแบ่ง KPIs ออกได้อีก 2 กลุ่มดังนี้

1 Laging indicator

หมายถึงกลุ่ม KPIs ที่เป็นค่าวัดผลรวมสรุป ซึ่งจะได้ค่าหลังจากงานนั้นสำเร็จไปแล้ว เช่น จำนวนสินค้าที่ผลิตได้ Quality Rate ,OEE Performance Rate , Availability Rate เป็นต้น หรือหากพิจารณากิจกรรมการลดน้ำหนัก จะนิยมใช้ค่า BMI เป็น KPI ของการลดน้ำหนัก

2 Lead indicator

หมายถึงกลุ่ม KPIs ที่เป็นค่าวัดผลแล้วแสดงให้เห็นถึงแนวโน้มในอนาคต เช่น จำนวนสินค้าที่ผลิตต่อชั่วโมง จำนวนของเสียที่เกิดขึ้นสูงสุดต่อหนึ่งชั่วโมงเป็นต้น

ความถี่ในการวัด KPIs

ขึ้นอยู่กับความเหมาะสมของ KPIs ที่ต้องการวัด แต่ KPIs ในกลุ่ม Lead indicator อาจต้องวัดเป็นรายชั่วโมงก็เป็นได้ เพื่อให้สามารถติดตามและแก้ปัญหาได้อย่างทันเวลา

ความยุ่งยากของระบบ KPIs คือ การเลือก KPIs ให้เหมาะสมและสอดคล้องกับภาระกิจของตำแหน่งงานหรือขององค์กรนั้น และท้ายสุดสิ่งที่สำคัญก็คือการวัดผล KPIs เหล่านั้นและการประเมินผล KPIs ดังที่ได้กล่าวมาแล้ว

เอาหล่ะครับก็อธิบายพอสมควรหวังว่าท่านผู้อ่านจะพอรู้จักบ้างแล้วนะครับว่า KPIs คืออะไร

ตัวอย่าง kpi : Performance Rate (kpi ด้านการผลิต)

ตัวอย่าง kpi ในบทความนี้ขอนำเสนอ องค์ประกอบที่สามของการคำนวณค่า OEE นั่นคือ ความเร็วในการผลิตสินค้า (Performance Rate) จุดประสงค์ในการวัด kpi ตัวนี้ก็เพื่อ วัดความเร็วในการผลิตสินค้า ซึ่งเป็นผลมาจากสภาพของเครื่องจักรและความสามารถของผู้ปฏิบัติงานกับเครื่องจักร ความเร็วในการผลิตสินค้าบางครั้งไม่สามารถคำนวณได้โดยตรง เนื่องจากมีความสูญเสียที่ไม่สามารถจับเวลาได้ แต่ก็ทำให้เครื่องจักรเสียกำลัง เช่น ไฟตก เครื่องเดินไม่เรียบเครื่องสะดุดหรือหยุดเล็กน้อย เป็นต้น เวลามาตรฐานในการทำงานต่อชิ้นสามารถช่วยให้เราแก้ปัญหาในการคำนวณได้ เพราะหากทราบเวลามาตรฐาน ก็จะทราบว่าตามเวลาเดินเครื่องควรจะผลิตงานได้กี่ชิ้นและในความเป็นจริงผลิตได้กี่ชิ้น

ข้อมูลการวัด kpi

1. Theoritical Cycle Time คือเวลาที่ใช้ในการผลิตสินค้า 1 หน่วย (นาทีต่อชิ้น)
2. Actual Output คือจำนวสินค้าที่ผลิตได้
3. Operation Time = Loading Time - Downtime

สูตรการคำนวณ kpi

หน่วยวัด kpi : %

การรายงานผล kpi ควรรายงานเป็นรายสัปดาห์เพื่อให้สามารถติดตามและแก้ปัญหาได้อย่างรวดเร็ว

จบจากตัวอย่าง kpi นี้แล้ว บทความต่อไปผมจะนำเสนอ kpi template ในรูปแบบของสูตร excel เพื่อใช้บันทึกและคำนวณค่า OEE ต่อไปครับ สวัสดีครับ

ตัวอย่าง kpi ที่เกี่ยวข้อง

1. OEE
2. Quality Rate
3. Availability Rate

ตัวอย่าง kpi : Availability Rate (kpi ด้านการผลิต)

ตัวอย่าง kpi ด้านการผลิต ที่จะนำเสนอในบทความนี้เป็นองค์ประกอบที่ 2 ที่ใช้ในการวัด kpi ของค่า OEE หลังจากได้กล่าวถึง Quality Rate ไปแล้วในบทความที่ผ่านมา ซึ่งเป็นตัวชี้วัดในด้านคุณภาพของสินค้า ตอนนี้มาพิจารณาความพร้อมใช้ของเครื่องจักรหรือของกระบวนการผลิตกันครับ kpi ตัวที่ใช้วัดคือ ความพร้อมของเครื่องจักร(Availability Rate) ซึ่งใช้วัดความพร้อมของเครื่องจักรที่สามารถผลิตสินค้าได้จริง โดยนำเวลาที่ใช้ในการผลิต (Loading time) หักออกจากเวลาสูญเสียทั้งหมดออกไป มาดูกันครับว่าเราต้องใช้ข้อมูลดิบอะไรบ้าง

1. Working Time คือเวลาทำงานทั้งหมดของเครื่องจักรที่กำหนดตามแผน
2. Planned Halt Time คือเวลาที่เครื่องจักรหยุดโดยมีการกำหนดเอาไว้ เช่น การประชุมประจำก่อนเริ่มงาน การกำหนดหยุดซ่อมเครื่องจักร(การ PM เครื่องจักร) เป็นต้น
3. Down Time คือ เวลาที่สูญเสียไปเนื่องจักรเครื่องจักรไม่สามารถทำงานหรือผลิตสินค้าได้
4. Loading Time = Working Time - Planned Halt Time

สูตรคำนวณ kpi

หน่วยวัด kpi : %

การรายงานผลการวัด kpi

ควรรายงานการวัด kpi เป็นรายสัปดาห์เพื่อให้สามารถติดตามการแก้ปัญหาได้อย่างรวดเร็ว โดยอาจแยกเป็นเครื่องจักร หรือเป็นกระบวนการผลิต หรือรวมเป็นแต่ละโรงงาน

ข้อสังเกต

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

1. Total Time คือเวลาเป็นนาทีในหนึ่งสัปดาห์ (กรณีเป็นการวัด kpi รายสัปดาห์) มีค่าเท่ากับ 7*24*60 = 10,080 นาที ต่อสัปดาห์/เครื่อง
2. Available Time = Total Time – Unavailable Time เมื่อ Unavailable Time คือวันหยุดประจำสัปดาห์หรือวันหยุดประจำปีที่ตรงกับสัปดาห์นั้นๆ หน่วยเป็น นาที
3. Used Time = Available Time - Available Unsed Time เมื่อ Available Unsed Time คือเวลาที่เครื่องพร้อมผลิตแต่ไม่สามารถผลิตได้เนื่องจากไม่มีแผนผลิตหรือการสั่งซื้อ
4. Operation Time = Used Time - Planned Halt Time เมื่อ Planned Halt Time คือ เวลาที่จงใจวางแผนไม่ผลิตสินค้าเช่น เวลาในการวางแผนซ่อมบำรุงเครื่องจักร เวลาในการประชุมประจำวันก่อนเริ่มงานเป็นต้น
5. Production Time = Operation Time - Routine Operation Time เมื่อ Routine Operation Time คือเวลาที่ต้องหยุดเครื่องจักรเป็นประจำ เช่น Cleaning , Start up, Adjustment , Shut down,Change over
6. Effective Time = Production Time – Unexpect Stoppage เมื่อ Unexpect Stoppage คือเวลาที่หยุดโดยไม่คาดคิด เช่น เครื่องจักรเสีย วัตถุดิบหมดกระทันหัน ไฟดับ นำไม่ไหล คนงานมีจำนวนไม่พอ เป็นต้น

จากนิยามของเวลาในการผลิตจะพบว่า หากเราสามารถลด Unexpect Stoppage หรือ Down Time และ Planned Halt Time ลงได้ จะทำให้เครื่องจักรมีความพร้อมที่จะผลิตสินค้าได้เพิ่มขึ้น ซึ่งแสดงได้ในรูปของ Availability Rate สิ่งที่ควรระวังในการคำนวณ Availability Rate คือการนิยาม Down Time และ Planned Halt Time ให้ชัดเจนเพื่อความถูกต้อง นอกจากนี้ยังมีการคำนวณหา Availability Rate อีกรูปแบบหนึ่งซึ่งใช้ค่า MTTR และ MTBF ช่วยคำนวณ

เอาหล่ะครับ บทความนี้ผมก็ได้นำเสนอ ตัวอย่าง kpi ด้านการผลิตที่บ่งชี้ด้านสุขภาพของเครื่องจักรในการผลิตของเราไปแล้วนะครับ ในบทความต่อๆไปผมจะได้นำเสนอ kpi template ในรูปแบบของ excel เพื่อใช้วัด Availability Rate และให้ท่านผู้อ่านนำไปใช้ในการวัด kpi กันได้ต่อไปครับ ดูตัวอย่าง kpi ที่ผมนำเสนอได้ตามหัวข้อด้านล่างเลยครับ

ตัวอย่าง kpi ที่เกี่ยวข้อง

1. OEE
2. Quality Rate
3. Performance Rate
   

ตัวอย่าง kpi : Quality Rate (kpi ด้านการผลิต)

ตัวอย่าง kpi ในบทความนี้จะกล่าวถึง kpi ด้านการผลิตที่เป็นองค์ประกอบ 1 ใน 3 ของข้อมูลดิบซึ่งใช้ในการประเมินผลค่า OEE โดยเป็น kpi ซึ่งสามารถทำการวัด kpi ได้ไม่ยากมากนัก นั่นคือ ค่า Quality Rate(คุณภาพของสินค้า) จุดประสงค์ในการวัด kpi นี้คือใช้วัดปริมาณสินค้าที่มีคุณภาพได้ตามมาตรฐานที่กำหนดไว้ และยังนำไปเป็นข้อมูลดิบสำหรับการวัด kpi ค่า OEE เรามาดูข้อมูลดิบที่ต้องใช้ในการวัดคุณภาพสินค้ากันเลยครับ

1. Defect คือจำนวนสินค้าที่มีคุณภาพต่ำกว่ามาตรฐานที่กำหนดไว้
2. Actual Output คือจำนวนสินค้าที่ผลิตได้ทั้งหมด

สูตรคำนวณ kpi

หน่วยการวัด kpi : %

การรายงานผลหรือการวัดผล kpi

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

ท่านผู้อ่านจะเห็นว่า Quality Rate เป็น ตัวอย่าง kpi ที่เข้าใจได้ง่ายและสามารถวัดได้ง่ายไม่ซับซ้อน สามารถสร้างเป็น kpi template ใน Excel ได้ไม่ยากนัก

สิ่งที่พึงระมัดระวังเป็นอย่างมากในการวัด kpi ตัวนี้คือ การนับจำนวน defect ของสินค้า ผู้ทำการเก็บข้อมูลต้องทำความตกลงกันให้ชัดเจนในส่วนของมาตรฐานของสินค้า มีคำถามทิ้งท้ายให้ท่านผู้อ่านลองตัดสินดูนะครับ สินค้าที่ต้อง Rework จะถูกนับเป็น defect ของสินค้าหรือไม่ เพราะอย่าลืมนะครับว่า Quality Rate เป็น 1 ใน 3 ขององค์ประกอบในการคำนวณค่า OEE ดังนั้นหากการวัด kpi ไม่ถูกต้องจะส่งผลต่อค่า OEE ที่จะคำนวณในขั้นตอนต่อไปครับ

ตัวอย่าง kpi ด้านการผลิต

1. OEE
2. Availability Rate
3. Performance Rate
   

ตัวอย่าง kpi : OEE kpi ด้านการผลิต

สวัสดีครับท่านผู้อ่าน วันนี้ผมขอเสนอเนื้อหาอีกด้านหนึ่งของ blog จากที่ผ่านมาผมได้เสนอวิธีการประเมินผล kpi ไปแล้วบางส่วน แต่ในระบบ kpi นอกจากวิธีการประเมินผล kpi แล้ว การวัด kpi และ การรวบรวมตัวอย่าง kpi ก็มีความสำคัญเนื่องจากการเลือก kpi ที่เหมาะสมจะทำให้สามารถปรับปรุงประสิทธิภาพการทำงานในด้านต่างๆได้มากขึ้น บทความในหมวดนี้จึงขอนำ ตัวอย่าง kpi มาแนะนำให้ทุกท่านได้ทราบ ในบทความนี้ขอนำเสนอ kpi ด้านการผลิตที่นิยมใช้ในการวัดประสิทธิภาพการผลิตนั่นคือ Overall Equipment Efficiency หรือ เรียกย่อๆว่าค่า OEE จุดประสงค์สำคัญของการวัด OEE ก็เพื่อวัดประสิทธิภาพโดยรวมของเครื่องจักรหรือกระบวนการผลิต ครอบคลุมถึงความพร้อมของเครื่องจักร (Availability Rate) ความเร็วในการผลิตสินค้า (Performance Rate) และคุณภาพของสินค้า (Quality Rate) เมื่อพิจารณาค่า OEE จะเห็นว่าเป็น ตัวอย่าง kpi ที่น่าสนใจ เนื่องจากเป็น kpi ที่ไม่ต้องการให้ฝ่ายผลิตเน้นการทำงานด้านใดด้านหนึ่ง ฝ่ายผลิตจะต้องใส่ใจกับปัจจัยทั้งสามด้าน

ข้อมูลดิบ

1. %Availability Rate (AR)

2. %Performance Rate (PR)

3. %Quality Rate (QR)

OEE calculations

OEE = (%AR)*(%PR)*(%QR)/10,000

หน่วย kpi : เปอร์เซนต์

การรายงานหรือการวัด kpi

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

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

หมายเหตุ

OEE อาจจะใช้วัดประสิทธิภาพการทำงานในงานด้านอื่นได้นอกจากงานด้านการผลิต เช่นด้าน IT

การใช้งาน Excel:ประยุกต์สูตร Excel:VLOOKUP คำนวณค่าคอมมิชชั่นในด้านการตลาด

สวัสดีครับท่านผู้อ่าน How to excel ครับ บทความนี้ขอนำเสนอการใช้สูตร Excel : VLOOKUP มาประยุกต์ใช้กับการคำนวณค่าคอมมิชชั่น ตามปกติ พนักขายทุกท่านจะได้รับค่าคอมมิชชั่นในการขายผลิตภัณฑ์ตามเป้าหมายที่บริษัทตั้งไว้ ตัวอย่างเช่น

หากขายได้ไม่เกิน 100,000 บาทจะได้ค่าคอมมิชชั่น 5% ของยอดขาย

หากขายได้มากกว่า 100,000 แต่ไม่เกิน 200,000 บาท จะได้ค่าคอมมิชชั่น 10% ของยอดขาย

หากขายได้มากกว่า 200,000 แต่ไม่เกิน 500,000 บาท จะได้ค่าคอมมิชชั่น 15% ของยอดขาย

หากขายได้มากกว่า 500,000 บาท แต่ไม่เกิน 750,000 บาท จะได้ค่าคอมมิชชั่น 20% ของยอดขาย

หากขายได้มากกว่า 750,000 บาท แต่ไม่เกิน 1,000,000 บาท จะได้ค่าคอมมิชชั่น 25% ของยอดขาย

และหากขายได้มากกว่า 1,000,000 บาท จะได้ค่าคอมมิชชั่น 30% ของยอดขาย

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

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

ภาพที่ 1 ตารางเงื่อนไขค่าคอมมิชชั่น

จะเห็นว่าผมเรียงลำดับขอบเขตล่างของยอดขายในแต่ละระดับจากน้อยไปหามากแล้ว เพื่อให้เราสามารถใช้เงื่อนไข TRUE ของสูตร Excel : VLOOKUP ได้อย่างถูกต้อง เอาหล่ะครับต่อไปเราก็เขียนสูตร Excel เพื่อคำนวณค่าคอมมิชชั่นและตัดเกรดได้ดังนี้ครับ สมมุติผมให้ รหัสพนักงาน ชื่อพนักงาน และยอดขายของพนักงาน อยู่ในหลัก D, E, F ตามลำดับ หากผมให้ค่าคอมมิชชั่นที่ถูกคำนวณเก็บไว้ในหลัก G และเกรดพนักงานดีเด่นอยู่ในหลัก H สมมุติกำหนดให้ข้อมูลเริ่มจากแถวที่ 3 เป็นต้นไป เราจะเขียนสูตร Excel : VLOOKUP ได้ดังนี้

G2 = VLOOKUP(F2,$A$4:$C$8,2,1)*F2

H2 = VLOOKUP(F2,$A$4:$C$8,3,1)

ทำการ Autofill ให้ครบจะได้ผลลัพธ์ดังแสดงในภาพที่ 2

ภาพที่ 2 ผลการคำนวณค่าคอมมิชชั่นและการจัดระดับพนักงานขาย

หากต้องการหายอดขายสูงสุดหรือต่ำสุดก็สามารถทำได้

ยังมีวิธีการคำนวณค่าคอมมิชชั่นอีกรูปแบบหนึ่งที่น่าสนใจ ดูได้ที่นี่ครับ

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

การทำซ้ำด้านบนเพื่อพิมพ์หัวตารางแบบซ้ำๆกันในทุกหน้า (Repeat a Row)

VLOOKUP การตัดเกรด

การค้นหาค่า Max$Min(Custom Lookup)

การสร้างแผนภูมิในรายงานด้วยคำสั่ง REPT

การใช้งาน Excel ตอน การค้นหาข้อความด้วย Excel Text Function

สวัสดีครับท่านผู้อ่าน How to excel ทุกท่านครับ วันนี้ขออนุญาตหยิบปัญหาที่ผมได้รับจากลูกค้าภายในที่ทำงาน เกี่ยวกับการค้นหาข้อความและแยกข้อความที่ต้องการเก็บไว้ในเซลล์ต่างหาก ใน Excel ด้วยเหตุที่ว่าข้อมูลที่มีอยู่ในปัจจุบันมีเยอะมาก การบันทึกข้อมูลดังกล่าวคงไม่สามารถทำได้โดยง่ายนักใน Excel วันนี้จึงขอนำเสนอ Excel text function ที่นิยมช่วยในการค้นหาข้อความและสำเนาข้อความที่ค้นเจอ ดังนี้ครับ

1.สูตร Excel text : Left เป็นสูตร Excel ที่ใช้สำเนาข้อความต้นฉบับ (text) โดยเริ่มสำเนาจากด้านซ้ายไปยังด้านขวาตามจำนวนตัวอักษรที่กำหนด มีรูปแบบดังนี้

LEFT(text,[num_chars])

โดย text คือ ข้อความต้นฉบับ

num_chars คือจำนวนตัวอักษรที่ต้องการสำเนา

ตัวอย่างเช่น สมมุติข้อความในเซลล์ B2 เท่ากับ LOVE หากเราใช้ สูตร Excel =LEFT(B2,2) สูตร LEFT จะสำเนาข้อความกลับมาให้เป็น LO เป็นต้น

2.สูตร Excel text : FIND เป็นสูตร Excel ที่ใช้หาตำแหน่งของตัวอักษรหรืออักขระที่อยู่ภายในข้อความ มีรูปแบบดังนี้

FIND(find_text,within_text,[start_num])

โดย find_text คือ ตัวอักษรหรืออักขระที่ต้องการค้นหาตำแหน่ง

within_text คือข้อความที่ต้องการค้นหา

start_num คือตำแหน่งในข้อความที่เริ่มค้นหาโดยตำแหน่งที่ 1 จะเริ่มจากตัวอักษรแรกจากซ้าย

ตัวอย่างเช่น FIND(“/”,”12/13”,0) จะคืนค่า 3 กลับมา เป็นต้น

3. สูตร Excel text : MID เป็นสูตร Excel ที่ใช้สำเนาอักษรหรือตัวอักขระจากข้อความโดยกำหนดตำแหน่งเริ่มต้นในการสำเนาและจำนวนอักษรหรือตัวอักขระที่ต้องการสำเนา มีรูปแบบดังนี้

MID(text,start_num,[num_chars])

โดย text คือข้อความต้นฉบับที่ต้องการสำเนา

start_num คือ ตำแหน่งเริ่มต้นในการสำเนา

num_chars คือ จำนวนอักษรหรือตัวอักขระที่ต้องการสำเนา

ตัวอย่างเช่น MID(“12/03/53”,3,2) จะคืนอักษร “03” กลับมา เป็นต้น

จากที่ได้ยกตัวอย่างมาเรามาดูตัวอย่างกันดีกว่าครับ สมมุติเรามีข้อมูลชื่อบริษัทและที่ตั้งของบริษัทลูกค้าอยู่ในเซลล์เดียวกันดังแสดงในภาพที่ 1 หากเราต้องการแยกชื่อของบริษัทลูกค้าออกมาเก็บไว้ในหลักใหม่เราสามารถประยุกต์ใช้ Excel text function ได้ ท่านผู้อ่านจะสังเกตเห็นว่าชื่อบริษัทและที่ตั้งจะถูกแบ่งด้วยอักขระ “,” จากเงื่อนไขนี้ หากเราสามารถหาตำแหน่งของ “,” ได้เราก็จะสามารถสำเนาชื่อของบริษัทลูกค้าออกมาเก็บไว้ในหลักใหม่ได้ โดยมีระเบียบขั้นตอนการใช้สูตร Excel ดังนี้

1. ใช้ สูตร FIND หาตำแหน่งของ “,”

2. ใช้สูตร LEFT สำเนาชื่อบริษัทไปเก็บไว้ในหลักใหม่

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

C2 = LEFT(B2,FIND(“,”,B2,1)-1) ผลการใช้สูตร Excel แสดงได้ดังภาพที่ 2

ภาพที่ 1 ข้อมูลการทดสอบ Excel text function

ภาพที่ 2 ผลการใช้สูตร Excel : LEFT&FIND

หากต้องการสำเนาที่อยู่หลังอักขระ “,” เก็บไว้ในหลัก D สามารถเขียนสูตร Excel ได้ดังนี้

D2 = MID(B2,FIND(“,”,B2,1)+1,LEN(B2)) โดย LEN เป็นสูตร Excel ที่คืนค่าความยาวของข้อความ ผลการใช้สูตร Excel นี้แสดงได้ดังภาพที่ 3

ภาพที่ 3 ผลการใช้สูตร Excel : MID , FIND , LEN

หวังว่าท่านผู้อ่านบทความนี้คงได้ไอเดียในการประยุกต์ใช้สำหรับการสำเนาข้อความในไฟล์ Excel นะครับ ส่วนตัวผมเองนอกจากจะตอบโจทย์ลูกค้าภายในบริษัทได้แล้ว ท่านผู้อ่านคงจะได้เห็นผมประยุกต์ใช้ สูตร Excel นี้ในการประเมินผลด้วย KPI ต่อไปครับ ราตรีสวัสดิ์ครับ

การใช้งาน Excel : การสร้างแผนภูมิในรายงานด้วยคำสั่ง REPT

สวัสดีครับท่านผู้อ่าน how to excel ทุกท่าน วันนี้ผมขอเสนอ Excel tips ด้านการสร้าง Bar chart สำหรับการแสดงผล เพื่อให้ท่านผู้อ่านเตรียมพร้อมสู่การบริหารจัดการการประเมินผลด้วย KPI ในการประเมินผล สิ่งที่สำคัญไม่น้อยกว่าการคำนวณและการประเมินก็คือ การแสดงผลลัพธ์การประเมิน ซึ่งการแสดงผลด้วยกราฟฟิคเป็นสิ่งสำคัญ การแสดงกราฟฟิคแบบง่ายๆสามารถทำได้โดยใช้สูตร REPT ซึ่งเป็นสูตรใน Excel โดย REPT มีรูปแบบการใช้งานดังนี้
=REPT(text,number_temes)
ฟังก์ชัน REPT เป็นสูตร Excel ที่ใช้สำหรับทำซ้ำข้อความ
เมื่อ text คือข้อความที่ต้องการทำซ้ำ
number_temes คือ จำนวนครั้งที่ต้องการทำซ้ำ
จากรูปแบบของสูตร Excel REPT เราสามารถนำมาประยุกต์ใช้แสดงผลเป็นแบบกราฟฟิคได้ง่ายๆ ดังกรณีตัวอย่างในภาพที่ 1

ภาพที่ 1 ข้อมูลยอดขายในแต่ละเดือน

จากภาพที่ 1 หากเราต้องการแสดงยอดขายในรูปของกราฟฟิคจะช่วยให้การนำเสนอน่าสนใจยิ่งขึ้น ซึ่งท่านผู้อ่านอาจจะบอกให้ไปใช้คำสั่งในการพล็อตกราฟของ Excel ซึ่งสามารถทำได้ง่ายเช่นกัน แต่หากเราใช้การแสดงกราฟฟิคแบบง่ายๆเพื่อให้เป็นการเปรียบเทียบในตารางข้อมูล เราจะใช้สูตร Excel การทำซ้ำข้อมูล (สูตร REPT) มาประยุกต์ได้ สมมุติว่าเราใช้เครื่องหมาย | เป็นตัวแทนของยอดขาย 1 ล้านบาท ดังนั้นในเดือนมกราคมจะแสดงยอดขายในรูปแบบกราฟฟิค เป็นเครื่องหมาย | จำนวน 15 แท่ง โดยเขียนสูตร Excel ได้ดังนี้

= REPT(“|”,B2)

เมื่อทำการ AutoFill จะได้การนำเสนอในรูปแบบกราฟฟิคได้ดังแสดงในภาพที่ 2

ภาพที่ 2 ผลการใช้คำสั่ง REPT สำหรับการแสดงผล

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

=REPT(“|”,B2/0.5)

แสดงผลได้ดังภาพที่ 3

ภาพที่ 3 การแสดงผลหลังปรับสเกล

หวังว่าท่านผู้อ่านจะนำไปประยุกต์ใช้ในรายงานการนำเสนอด้วย Excel หรือการสร้างแผนภูมิฮิสโตแกรมได้ครับ ส่วนผมเองจะนำไปประยุกต์ใช้ในการนำเสนอผลการประเมินด้วย KPI ดังจะนำเสนอในบทความต่อๆไปครับ ราตรีสวัสดิ์ครับ

การใช้งาน Excel : การอ้างถึง Worksheets ใน VBA

สวัสดีครับท่านผู้อ่าน How to excel ทุกท่านนะครับ หายไปหลายวันด้วยภาระกิจด้านอื่นๆที่ต้องสะสางครับ บทความนี้เป็นบทความต่อเนื่องจากบทความที่ผ่านมาครับ บทความก่อนหน้าผมได้นำเสนอการอ้างถึงไฟล์ Excel หรือ Workbooks ที่ถูกเปิดอยู่ เพื่อที่เราจะได้เข้าไปอ่านข้อมูลในเซลล์ หรือเข้าไปแก้ไขข้อมูลในเซลล์นั้นๆได้ ท่านผู้อ่านคงทราบกันดีว่า ในWorkbooks ก็จะประกอบไปด้วย worksheets หลายๆ worksheets และใน worksheets ก็จะประกอบไปด้วยเซลล์ต่างๆ ซึ่งการประมวลผลของ Excel จะดำเนินการระหว่างเซลล์ ซึ่งจากที่ได้กล่าวมาการประมวลผลข้อมูลระหว่างเซลล์ข้าม Workbooks หรือ Worksheets นั้นสามารถทำได้หากเราสามารถอ้างถึงตำแหน่งของเซลล์ได้ถูกต้อง(workbooks ,worksheets,row and column) เอาหล่ะครับวันนี้เรามาดูวิธีการอ้างถึง Worksheet ในแต่ละวิธีกันเลยครับ

1. การอ้างถึง Worksheets ที่กำลังถูกใช้งานอยู่ (active worksheets) เราจะใช้สมบัติ ActiveSheet ในการอ้างถึง เช่น หากต้องการทราบชื่อของ WorkSheet ที่กำลังใช้งานอยู่สามารถเขียนโค้ดได้ดังนี้

sheetsName = ActiveSheet.Name

2. การอ้างถึงผ่าน object ของ WorkSheet

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

 Dim ws As Worksheet
For Each ws In Worksheets
   ws.Select 
   ListBox1.AddItem ws.Name
Next ws
ตัวอย่างนี้เป็นการแสดงชื่อของ WorkSheet ทั้งหมดใน Workbooks ที่กำลัง Active
โดยแสดงใน ListBox

3.การอ้างถึงแบบชัดแจ้ง (Explicitly)

เราสามารถใช้สมบัติของ WorkSheets ได้ในกรณีที่ทราบชื่อ WorkSheet
ตัวอย่างเช่นเราสามารถลบ WorkSheets ได้โดยผ่านสมบัติของ WorkSheets
โดยต้องระบุชื่อของWorkSheetที่ต้องการลบ ดังแสดงในโค้ด

‘workshtname คือ ชื่อของWorkSheetที่ต้องการลบ 

Worksheets(workshtname).Delete

4.การอ้างถึง WorkSheets โดยใช้ Index

การนับจำนวน WorkSheets จะใช้สูตรใน VBA เป็น

Worksheets.Count

Index หรือ ลำดับของ WorkSheet ใน Workbooks จะเริ่มตั้งแต่ 1ถึง
จำนวน WorkSheets โดย WorkSheets(1) จะหมายถึง WorkSheet แรก
ขณะที่WorkSheets(Worksheets.Count)จะหมายถึง WorkSheet สุดท้าย

เอาหล่ะครับท่านผู้อ่าน How to excel มาถึงตอนนี้ Excel Utility ที่ผมได้ปูพื้นฐาน
มาให้ท่านผู้อ่านเพื่อเตรียมพร้อมในการเข้าสู่เนื้อหาของการประเมินผลด้วย KPI 
ใกล้สมบูรณ์แล้ว บทความต่อไปผมอยากนำเสนอ การนำเสนอผลการประเมินKPI
ด้วยรูปภาพกันอีกซักบทความก่อนเข้าสู่การประเมินผล KPI กันครับ สวัสดีครับ

การใช้งาน Excel : การอ้างถึงไฟล์ Excel ใน VBA

สวัสดีครับท่านผู้อ่าน How to excel บทความนี้เกิดจากความรับผิดชอบต่อท่านผู้อ่านในบทความเรื่อง การเปิดไฟล์ Excel ใน VBA ซึ่งการทำงานใน vba ส่วนใหญ่จะมีการอ้างถึงไฟล์ Excel ที่เปิดใช้งานอยู่ ซึ่งมีหลายวิธี เรามาทำความเข้าใจในแต่ละวิธีกันครับ

1. การอ้างถึง Workbooks หรือไฟล์ Excel ที่กำลังทำงานอยู่หรือกำลัง Focus เราจะใช้

ActiveWorkbook properties

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

หลังจากการใช้งาน Excel บนWorkbooks เสร็จสิ้น หากต้องการปิด Workbooks โดยไม่บันทึก จะใช้คำสั่ง

ActiveWorkbook.Close False หมายถึงให้ปิด Workbooks ที่กำลัง Focus โดยไม่บันทึก

หากต้องการ บันทึกข้อมูลก่อนปิด จะใช้คำสั่ง

ActiveWorkbook.Close True หมายถึงให้ปิด Workbooks ที่กำลัง Focus โดยบันทึกก่อนปิดไฟล์

หากต้องการทราบชื่อหรือตำแหน่งของไฟล์ Excel หรือ Workbook ที่กำลัง Focus สามารถใช้คำสั่ง

ActiveWorkbook.Path  หมายถึงให้คืนค่าตำแหน่งที่เก็บไฟล์ Excel นี้กลับ

ActiveWorkbook.Name หมายถึงให้คืนชื่อไฟล์ Excel นี้กลับ

2.การอ้างถึง Workbooks หรือไฟล์ Excel ที่กำลังรัน โค้ด VBA เนื่องจาก โค้ด VBA ไม่ได้อยู่ใน Workbooks ที่กำลัง Focus เสมอไป เราจะใช้ ThisWorkbook property ในการอ้างถึง ตัวอย่างการใช้งาน เช่น

ThisWorkbook.Path

ThisWorkbook.Name

3.การอ้างถึง Workbooks หรือไฟล์ Excel ที่เปิดอยู่ทั้งหมดด้วยWorkbooks collection ตัวอย่างเช่น การแสดงชื่อ Workbooks ทั้งหมดที่เปิดลงใน ListBox1

Dim wb As Workbook For Each wb In Workbooks ListBox1.AddItem wb.Name Next wb

4.การอ้างถึง Workbooks หรือไฟล์ Excel แบบชัดแจ้ง (explicit name) เป็นการอ้างถึง Workbooks หรือไฟล์ Excel ด้วยชื่อ Workbooks หรือไฟล์ Excel นั่นเช่น หากต้องการปิดไฟล์ Excel ชื่อ Test.xls โดยไม่บันทึก เราจะใช้คำสั่งดังนี้

Workbooks(“Tesr.xls”).Close False
หรือหากต้องการ Focus หรือ Active ก็สามารถสั่งได้ดังนี้
Workbooks(“Tesr.xls”).Active
การอ้างถึงด้วยวิธีนี้สิ่งที่ควรระวังให้มากคือ ชื่อ Workbooks หรือไฟล์ Excel ที่อาจมีการเปลี่ยนแปลงได้ซึ่งจะทำให้เกิดความผิดพลาดในการะหว่างทำงานได้ (Run-time error)

5. การอ้างถึง Workbooks หรือไฟล์ Excel ด้วย Index Excel จะกำหนดค่า Index ให้กับ Workbooks หรือไฟล์ Excel ที่เปิด โดยกำหนดตามลำดับที่เปิดก่อนหลัง ดังนั้นหากต้องการอ้างถึง Workbooks หรือไฟล์ Excel ที่เปิดเป็นไฟล์แรก ก็จะอ้างถึงได้ดังนี้

Workbooks(1).Active

สิ่งที่ควรระวังในการอ้างถึงคือ Index ของ Workbooks หรือไฟล์ Excel ที่เปิด จะมีการเปลี่ยนแปลงหากมีการปิดไฟล์ โดยจะมีการจัดเรียง Index ใหม่ทุกครั้ง

ผมหวังว่าท่านผู้อ่านจะทำความเข้าใจกับการอ้างอิง Workbooks หรือไฟล์ Excel แบบต่างๆและหยิบเอาไปใช้ในการเขียนโค้ด VBA เพราะเราคงปฏิเสธไม่ได้ว่าในทางปฎิบัติเราคงต้องเจอไฟล์ Excel หรือ Workbooks ที่ทั้งเกี่ยวข้องกับการประมวลผลและไม่เกี่ยวข้องเปิดอยู่เต็มไปหมดบนคอมพิวเตอร์ของเราขณะรัน Macro หากเราเข้าใจในบทความนี้อย่างถ่องแท้ เราจะไม่หลงทางไปเข้าไปอ่านข้อมูลผิดไฟล์ครับ เดี๋ยวบทความต่อไปมาทำความเข้าใจกับการอ้างถึง Worksheets ใน VBA ซึ่งมีความสำคัญมากเช่นกันครับ สวัสดีครับ

VBA Excel ตอน การเปิดไฟล์ Excel ด้วย VBA

สวัสดีตอนเช้าครับท่านผู้อ่าน How to excel บทความนี้ยังเป็นการนำเสนอ Excel utility สำหรับเตรียมพร้อมในการสร้างระบบการประเมินผลด้วย KPI ที่ผมได้นำเสนอเป็น Serie ค้างไว้ เนื่องด้วยอยากปูพื้นฐานถึงที่มาที่ไปของการทำงานในระบบการประเมินผลของเรา Excel utility ในบทความนี้ขอกล่าวถึงการเปิดไฟล์ Excel ครับ เนื่องด้วยหากท่านผู้อ่านย้อนกลับยัง Serie ระบบการประเมินผลด้วย KPI ท่านจะเห็นว่า เราจำเป็นต้องเข้าไปอ่านข้อมูลการประมวลผล KPI ของ KPI ระดับต่างๆดังแสดงในภาพที่ 1

ภาพที่ 1 ลักษณะการเข้าถึงข้อมูลผลการชี้วัด KPI

สิ่งแรกที่ต้องทำในการเข้าไปอ่านข้อมูลและดึงข้อมูลมาเก็บที่ไฟล์ Excel (สรุปผล KPI.xls) ก็คือต้องทำการเปิดไฟล์ก่อนครับ เริ่มจากสร้างปุ่มกด Worksheets ของไฟล์ สรุปผล KPI.xls สำหรับเรียกใช้งาน Macro และสร้าง Macroในการเปิดไฟล์ สมมุติให้ชื่อว่า ImportData_Click ซึ่งมีรายละเอียดดังนี้ครับ

Sub ImportData_Click()
Dim cur As Integer
Dim actWBName As String
actWBName = ActiveWorkbook.Name
With Application.FileDialog(msoFileDialogOpen)
.Title = "Select File"
.AllowMultiSelect = False
.Show
Workbooks.Open (.SelectedItems(1))
cur = Workbooks.Count
Workbooks(cur).Activate

‘ Do some thing

Workbooks(cur).Close
Workbooks(actWBName).Activate
End With
End Sub

สามารอธิบายการทำงานของ VBA โค้ดได้ดังนี้ครับ

Dim cur As Integer
Dim actWBName As String

กำหนดตัวแปร cur สำหรับเก็บตำแหน่งของ workbooks ล่าสุด กำหนดตัวแปร actWBName สำหรับเก็บชื่อไฟล์ Excel ที่เรียกใช้งาน Macro

actWBName = ActiveWorkbook.Name

เก็บชื่อไฟล์ Excel ที่กำลังใช้งานอยู่ไว้ในตัวแปร actWBName

With Application.FileDialog(msoFileDialogOpen)
เรียก Dialog สำหรับเปิดไฟล์ด้วยฟังก์ชัน FileDialog และกำหนดโหมดเปิดไฟล์ด้วยตัวแปร msoFileDialogOpen

.Title = "Select File"
.AllowMultiSelect = False
.Show
กำหนดข้อความใน Title ของDialog เป็น Select File

กำหนดให้ผู้ใช้งานสมารถเลือกเปิดไฟล์ได้ไฟล์เดียวหรือหลายไฟล์ด้วย properties AllowMultiSelect ในตัวอย่างนี้กำหนดให้เลือกได้ไฟล์เดียว (False)

สั่งให้แสดงDialogด้วย properties Show

เมื่อ ถึงคำสั่งในบรรทัดนี้ Excel จะแสดง Dialog สำหรับเปิดไฟล์ ดังแสดงในภาพที่ 2

ภาพที่ 2 Dialog สำหรับเปิดไฟล์

Workbooks.Open (.SelectedItems(1))
cur = Workbooks.Count
Workbooks(cur).Activate

‘ Do some thing

Workbooks(cur).Close

เปิดไฟล์ Excel ที่เลือก (.SelectedItems(1)) ด้วยคำสั่ง Open ของ object Workbooks

เก็บตำแหน่งไฟล์ Excel ล่าสุดที่เปิดไว้ (ตำแหน่งไฟล์ Excel ที่เราเปิดนั่นแหล่ะครับ) เพื่อใช้อ้างอิง Workbooks โดยเก็บไว้ที่ตัวแปร cur

Active ไฟล์ Excel ที่เปิดล่าสุดผ่านการอ้างอิงเพื่อเตรียมเข้าไปอ่านหรือทำงานในไฟล์ Excel นั้น

ดำเนินการภายในไฟล์ Excel ที่ Active

เมื่อเสร็จแล้วปิดไฟล์ Excel ที่ Active ด้วยคำสั่ง Close

Workbooks(actWBName).Activate
End With

ทำการกำหนดให้ไฟล์ Excel ที่เรียกใช้งาน Macro นี้ Activate โดยผ่านการอ้างอิงด้วยชื่อไฟล์ ในตัวแปร actWBName

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

Excel VLOOKUP ตอน VLOOKUP กับการตัดเกรด

สวัสดีครับท่านผู้อ่าน How to excel บทความนี้ผมอยากนำเสนอ Excel Utility ที่จะช่วยในการประเมินผลงานพนักงานด้วย KPI ในบทความอนุกรมชุด ระบบ KPI นะครับ นั่นคือ การตัดเกรด โดยจะใช้ฟังก์ชัน VLOOKUP หากกล่าวถึงการตัดเกรด คนที่ผ่านการเป็นนักเรียนแบบเราก็คงรู้กันดีใช่มั้ยครับว่าการตัดเกรดต้องมีเงื่อนไขของช่วงเกณฑ์ที่ถูกกำหนดขึ้น ตอนเรียนปริญญาตรีของผมใครได้คะแนนต่ำกว่า 30 จะได้เกรด F เอาหล่ะครับเกริ่นเล็กน้อยพอให้เข้าใจ มาดูกันต่อครับ ฉะนั้นองค์ประกอบของการตัดเกรดก็คือเงื่อนไขในการตัดเกรดนั่นเอง โดยทั่วไปก็มักจะใช้เกณฑ์ระดับคะแนนเป็นข้อมูลตัดสินใจ บางกรณีอาจใช้เกณฑ์น้ำหนัก(เช่น การตัดเกรดแบ่งความอ้วนผอม หรือการตัดเกรดผลิตภัณฑ์บางอย่าง) เรามาดูตัวอย่างกันเลยครับ

ก่อนตัดเกรด เราต้องกำหนดเงื่อนไขการตัดเกรดและจัดเก็บในรูปแบบของตารางข้อมูลโดยเรียงลำดับเงื่อนไขจากน้อยไปหามาก(เพื่อให้สามารถใช้ VLOOKUP ได้ตรงตามความต้องการ) ดังแสดงในภาพที่ 1

ภาพที่ 1 เกณฑ์การตัดเกรด

มาดูตัวอย่างการตัดเกรดพนักงานกันครับ สมมุติให้ตารางข้อมูลคะแนนที่ได้จากการประมวลผลแสดงได้ดังภาพที่ 2

ภาพที่ 2 ตัวอย่างตารางข้อมูล

เราจะใช้ฟังก์ชัน VLOOKUP เข้ามาช่วยกันครับ มาดูสูตร Excel กันเลยครับ ให้ท่านคลิกที่ เซลล์ D2 และให้พิมพ์ =VLOOKUP(C2,$F$3:$H$7,3,1)

จากสูตร VLOOKUP สามารถอธิบายได้ดังนี้ ให้หาตำแหน่งซึ่งมีค่าที่น้อยกว่าหรือเท่ากับค่าใน C3 และคืนค่าในหลักที่ 3 ของตาราง $F$3:$H$7

ค่าที่น้อยกว่าหรือเท่ากับ คือการกำหนดเงื่อนไขในเทอมสุดท้ายของ VLOOKUP Function

เมื่อทำ AutoFill ลงไปถึงบรรทัดที่ 10 จะได้ผลดังแสดงในภาพที่ 3

ภาพที่ 3 ผลการใช้ฟังก์ชัน VLOOKUP ช่วยตัดเกรด

ตัวอย่างการใช้งาน VLOOKUP ที่ได้นำเสนอไป คงทำให้ท่านผู้อ่านสามารถนำไปใช้งานที่เหมาะสมกับแต่ละงานได้นะครับ การตัดเกรดก็เป็นลักษณะงานหนึ่งที่ประยุกต์ใช้ VLOOKUP ที่ใช้ option true บทความต่อๆไปผมจะนำเสนอให้ท่านผู้อ่านได้ทราบถึงการประยุกต์ใช้ VLOOKUP ในด้านอื่นๆ แต่สิ่งสำคัญที่อยากให้ท่านผู้อ่านระลึกไว้ขณะใช้งาน VLOOKUP คือ VLOOKUP เป็นฟังก์ชันที่ใช้ในการค้นหาข้อมูล จากบทความนี้ทำให้ผมได้ Excel Utility ที่จะนำไปใช้ในการประเมินผลพนักงานด้วย KPI ในส่วนของการตัดเกรดและจ่ายค่าตอบแทนพิเศษต่อไปครับ ลืมไปอีกอย่างครับ หากข้อมูลที่ตัดเกรดด้วย VLOOKUP มีจำนวนแถวมากเกินหนึ่งหน้า หากจะพิมพ์อย่าลืมกำหนดการพิมพ์ซ้ำด้านบนของกระดาษดังที่ผมได้แนะนำในหัวข้อ Excel Trip ได้นะครับ สวัสดีครับ

Excel trip ตอน การทำซ้ำด้านบน (Repeat a Row)

สวัสดีท่านผู้อ่าน How to excel บทความตอนนี้เป็นหัวข้อใหม่ที่ผมคิดว่าน่าจะเป็นประโยชน์กับท่านผู้ใช้ Excel นะครับ Excel trip เป็นเทคนิคเล็กๆน้อยๆที่ผมอยากนำเสนอเป็นหัวข้อๆไปตามแต่โอกาสจะอำนวยนะครับ มาดู excel trip อันแรกที่ผมคิดว่าหลายคนอาจจะยังไม่ทราบ(เช่นผมเป็นต้น) นั่นคือการพิมพ์ซ้ำหัวตาราง ผมพบปัญหานี้เมื่อแฟนผมโทรมาถามสาเหตุที่เธอไม่สามารถใช้คำสั่งนี้ได้ ผมเองไม่เคยใช้ ตัวเธอเองบอกว่า Excel มีคำสั่งตั้งค่าให้พิมพ์หัวตารางซ้ำๆกันได้ทุกหน้า ใช่แล้วครับ ท่านผู้อ่านที่ต้องพิมพ์รายการสินค้าที่มีข้อมูลมากกว่าหนึ่งหน้าโดยในแต่ละหน้าในบรรทัดแรกจะมีรายการบนหัวตารางที่เหมือนๆกัน สมมุติผมมีรูปแบบการเก็บข้อมูลสินค้าในไฟล์ Excel ดังภาพที่ 1

ภาพที่ 1 ข้อมูลในหัวตารางที่ต้องการพิมพ์ซ้ำ

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

ภาพที่ 2 หน้าต่างการตั้งค่าหน้ากระดาษ ใน Excel

แค่นี้เราก็จะได้หัวตารางที่ซ้ำๆกันในทุกหน้าของกระดาษแล้วครับ โดยจำนวนแถวที่จะพิมพ์ซ้ำบนหัวตารางสามารถเลือกได้หลายแถวนะครับ เช่น $1:$3 จะหมายถึงให้พิมพ์ซ้ำข้อมูลในแถวที่ 1 – 3 ในหัวตารางแต่ละหน้าครับ

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

ปล ผมศึกษาไว้เพราะกำลังจะนำไปใช้ในรายงานการประเมินผลงานด้วยระบบ KPI นั่นเองครับ ราตรีสวัสดิ์ครับท่านผู้อ่าน How to excel

Excel tips

การสลับแถวสลับหลักของข้อมูล

pdf creator สำหรับแปลงไฟล์ข้อมูล excel

การแปลงเป็น pdf จากไฟล์หลายรูปแบบ

ระบบ KPI ตอน การประเมินผล KPI ของพนักงาน (1)

สวัสดีตอนเช้าครับท่านผู้อ่าน How to excel วันนี้มาต่อกันกับบทความเกี่ยวกับระบบ KPIs ครับ บทความนี้ขอนำเสนอขั้นตอนการประเมินผล KPIs ท่านผู้อ่านที่ยังไม่ทราบที่มาที่ไปของการประเมินขอให้ย้อนกลับไปอ่านบทความเก่าๆของผมได้นะครับ ผมได้นำเสนอแนวคิดการประเมินผล KPIs ไปแล้วในตอนแรก สมมุติให้ KPIs ในระดับองค์กร ระดับฝ่าย และระดับแผนก และระดับพนักงานถูกประเมินเสร็จเรียบร้อยแล้ว ซึ่งหากเราใช้ Excel ในการบันทึกผลการประเมินและเพื่อให้เกิคความรวดเร็ว เราอาจกำหนดให้ผู้จัดการฝ่ายหรือหัวหน้าแผนกประเมินผล KPIs ของหน่วยงานตนเองและพนักงาน ในขณะที่ระดับองค์กรอาจเป็นหน้าที่ของผู้ดูแลระบบเป็นผู้ประเมินผล ผลการประเมิน KPIs จะอยู่ในรูปแบบเปอร์เซนต์ตามวิธีการคำนวณที่ได้นำเสนอไป จากนั้นผู้บริหารต้องกำหนดให้ทุกฝ่ายทุกแผนกส่งไฟล์ Excel ที่ประมวลผล KPIs ในส่วนของตนเองไปยังศูนย์ประมวลผล(อาจเป็นหน่วยงานที่จัดตั้งขึ้นเพื่อรวบรวมผลการประเมิน KPIs) ดังแสดงในภาพที่ 1

ภาพที่ 1 ผังแสดงการประเมินผล KPIs ของแต่ละระดับ

ท่านผู้อ่านจะเห็นว่าไฟล์ประเมินผล KPIs จะมีจำนวนมากหรือน้อยขึ้นอยู่กับขนาดขององค์กร ทีนี้เรามาดูกันครับว่าไฟล์ Excel สรุปผล KPIs ในแต่ละระดับจะมีรูปแบบการเก็บข้อมูลอย่างไร สมมุติว่าองค์กรที่จะประเมินผลมีโครงสร้าง 4 ระดับดังที่อธิบายมานะครับ

ภาพที่ 2 รูปแบบการจัดเก็บผลประเมิน KPIs ระดับองค์กรในexcel

ภาพที่ 3 รูปแบบการจัดเก็บผลประเมิน KPIs ระดับฝ่ายในexcel

ภาพที่ 4 รูปแบบการจัดเก็บผลประเมิน KPIs ระดับแผนกในExcel

ภาพที่ 5 รูปแบบการจัดเก็บผลประเมิน KPIs ระดับพนักงานในexcel

มาถึงตรงนี้ท่านผู้อ่านคงพอเห็นภาพวิธีการคำนวณแล้วนะครับ เราต้องรวบรวมผลการประเมิน KPIs แต่ละระดับจากฝ่ายหรือแผนกที่เกี่ยวข้องในรูปแบบไฟล์ Excel จากนั้นนำผลประเมิน KPIs ในระดับต่างๆเหล่านั้นบันทึกลงไปในไฟล์ Excel สรุปผลการประเมิน KPI โดยแยกเก็บในแต่ละ WorkSheets ในที่นี้ผมขอตั้งชื่อ Worksheet ที่เก็บข้อมูลการประเมินผล KPIsไว้ดังนี้

Company เก็บผลประเมิน KPIs ระดับองค์กร

Dep เก็บผลประเมิน KPIs ระดับฝ่าย

Section เก็บผลประเมิน KPIs ระดับแผนก

Employer เก็บผลประเมิน KPIs ระดับพนักงาน

เดี๋ยวบทความต่อไปเรามาเขียน VBA Excel ช่วยในการอ่านผลการประเมิน KPIs จากต้นสังกัดและบันทึกข้อมูลลงในไฟล์สรุปผลประเมิน KPIs กันเพื่อลดเวลาในการโอนย้ายข้อมูลครับ สวัสดีครับ

ประเมินผล KPI ด้วย Excel (1)

ระบบ KPI ตอน การกระจาย KPI ตามโครงสร้างองค์กร

ระบบ KPI ตอน กระจายน้ำหนัก KPI ตามระดับพนักงาน

ระบบ KPI ตอน ฐานข้อมูลพนักงานใน Excel

ระบบ KPI ตอน ฐานข้อมูลพนักงานใน Excel

สวัสดีตอนเช้าท่านผู้อ่าน How to excel ครับ ช่วงนี้ผมขอนำเสนอบทความเกี่ยวกับการพัฒนาระบบ KPI ในส่วนของการจัดเตรียมฐานข้อมูล KPIs ให้ต่อเนื่องกันเพื่อไม่ให้เสียอรรถรสในการรับข้อมูลครับ มาต่อกันกับบทความต่อเนื่องของระบบ KPI กันเลยครับ จากบทความที่ผ่านมาผมได้กล่าวถึง KPIs Matrix ไปแล้วนะครับ ทีนี้หากเราจะจับเอาค่าน้ำหนัก KPIs มาประเมินผลงานพนักงาน ทำอย่างไรจะเข้าถึงค่าน้ำหนัก(Wij) ได้อย่างถูกต้องและเหมาะสม ก่อนอื่นมาดูกันเลยว่าหากเราจับรหัสพนักงานมาหนึ่งท่านข้อมูลที่เราจะต้องรู้มีอะไรบ้าง(เอาเฉพาะที่เกี่ยวข้องกับการเข้าถึงน้ำหนักคะแนนใน KPIs Matrix)

ข้อมูลของพนักงานที่จำเป็นสำหรับการเข้าถึงน้ำหนักคะแนนใน KPI Matrix มีดังนี้ครับ

รหัสพนักงาน, ชื่อ-นามสกุล,บริษัทที่สังกัด(กรณีมีหลายบริษัทในเครือ) , ฝ่ายที่สังกัด ,แผนกที่สังกัด และสุดท้ายก็คือ ระดับของพนักงานท่านนั้นๆ ข้อมูลของพนักงาน ท่านผู้อ่านอาจจะนำเข้าจากโปรแกรมฐานข้อมูลอื่นๆ เช่น Access ,sql หรือนำเข้าจากไฟล์ Text หรือ CSV ไฟล์

สมมุติให้ฐานข้อมูลพนักงานถูกอ่านและเก็บไว้ใน Excel worksheet ชื่อ ข้อมูลพนักงาน โดยมีรูปแบบการจัดเก็บข้อมูลใน Excel ดังแสดงในภาพที่ 1

ภาพที่ 1 ฐานข้อมูลพนักงานใน Excel

เอาหล่ะครับ มาถึงตรงนี้หากผมจะเข้าถึงน้ำหนัก KPIs ของนายกนก รักนะ เราต้องใช้ Excel ฟังก์ชันตัวไหนหรือต้องเขียน VBA ในการเข้าถึงข้อมูลใน KPIs Matrix ใช่แล้วครับ เราก็ใช้ VLOOKUP Function โดยค้นหาตามระดับพนักงานและ Lookup ข้อมูลน้ำหนัก KPIs ตามโครงสร้างองค์กรในหลักต่างๆ และในทางปฏิบัติเราก็ต้องทำการประเมินผลงานของพนักงานทุกท่าน ซึ่งเดี๋ยวในตอนต่อไปผมจะนำเสนอ VBA โค้ดสำหรับการอ่านน้ำหนัก KPIs ของพนักงานแต่ละท่านเพื่อเตรียมนำไปประเมินผลงานต่อไป สวัสดีครับ

ประเมินผล KPI ด้วย Excel (1)

ระบบ KPI ตอน การกระจาย KPI ตามโครงสร้างองค์กร

ระบบ KPI ตอน กระจายน้ำหนัก KPI ตามระดับพนักงาน

ระบบ KPI ตอน การประเมินผล KPI ของพนักงาน(1)

ระบบ KPI ตอน กระจายน้ำหนัก KPI ตามระดับพนักงาน

สวัสดีท่านผู้อ่าน How to excel ทุกท่านครับ วันนี้จะเสนอภาคต่อของการพัฒนาระบบ KPI บทความที่ผ่านมาผมได้นำเสนอการแบ่ง KPI ออกเป็นระดับตามโครงสร้างองค์กรไปแล้ว วันนี้มาดูกันต่อเรื่องของพนักงานครับ ในโครงสร้างขององค์กรนิยมแบ่งพนักงานออกเป็นระดับต่างๆเพื่อให้ทราบถึงความก้าวหน้าในหน้าที่การงาน เช่น วิศวกร อาจจะถูกจัดอยู่ในระดับ 4 ถึง ระดับ 6 ผู้จัดการฝ่ายอาจถูกจัดอยู่ในระดับ 7 พนักงานระดับปฏิบัติการอาจจะเริ่มจากระดับ 1 เป็นต้น ดังนั้นหากเราหยิบชื่อพนักงานท่านหนึ่งขึ้นมา ข้อมูลของพนักงานที่เราต้องทราบคือ สังกัดฝ่ายใด แผนกใด และอยู่ในระดับไหน ผมขอยกตัวอย่างการพัฒนาระบบ KPI ให้สอดคล้องกับตัวอย่างบทความที่ผ่านมาเลยนะครับ สมมุติให้องค์กรแบ่งระดับพนักงานออกเป็น 8 ระดับ ดังนั้นเราสามารถเขียนน้ำหนักของ KPI อยู่ในรูปแบบของ KPI MATRIX ได้ดังนี้

KPI Matrix

ระดับพนักงาน		โครงสร้างองค์กร
	องค์กร	ฝ่าย	แผนก	พนักงาน
L1	W11	W12	W13	W14
L2	W21	W22	W23	W24
L3	W31	W32	W33	W34
L4	W41	W42	W43	W44
L5	W51	W52	W53	W54
L6	W61	W62	W63	W64
L7	W71	W72	W73	W74
L8	W81	W82	W83	W84

จาก KPI Matrix เราสามารถเขียนน้ำหนัก KPI ในรูปแบบทางคณิตศาสตร์เพื่อให้ง่ายต่อการคำนวณผลการประเมินได้เป็น Wij โดย i แสดงถึงตำแหน่งแถวใน KPI Matrix (ระดับพนักงาน) j แสดงถึงตำแหน่งหลักใน KPI Matrix (ระดับโครงสร้างองค์กร) ในการประเมินผลงานของพนักงานโดยส่วนใหญ่เราจะนิยมประเมินผลออกมาในรูปของเปอร์เซนต์ ดังนั้นเราจึงกำหนดเงื่อนไขผลรวมของน้ำหนักคะแนน KPI ต้องเท่ากับ 100 (ผลรวมของน้ำหนักคะแนนในแต่ละแถวจะเท่ากับ 100) โดยสามารถเขียนเป็นสมการทางคณิตศาสตร์ได้เป็น

การกำหนดคะแนน Wij องค์กรจะต้องจัดตั้งคณะกรรมการขึ้นมาเพื่อพิจารณากระจายน้ำหนัก KPI ให้เหมาะสมกับพนักงานในแต่ละระดับ ยกตัวอย่างเช่น พนักงานในระดับบริหาร ควรจะมีน้ำหนัก KPI ในระดับองค์กร หรือในระดับฝ่ายสูงกว่า พนักงานในระดับปฏิบัติการ เป็นต้น

ในกรณีที่องค์กรมีขนาดหรือลักษณะโครงสร้างนอกเหนือจากที่ผมยกตัวอย่าง เรายังสามารถใช้หลักการหรือแนวคิดการกระจายน้ำหนัก KPI ลงใน KPI Matrix ได้ เหมือนเดิม

เอาหล่ะครับมาถึงตรงนี้ผมได้อธิบายถึงน้ำหนักคะแนน KPI และได้นำเสนอ KPI Matrix ไปแล้วเดี๋ยวบทความต่อไปเราจะมากล่าวถึงการสร้างฐานข้อมูลพนักงานใน Excel ให้สามารถเข้าถึงข้อมูลใน KPI Matrix ซึ่งก็จะถูกสร้างใน Excel เช่นกัน เพื่อนำไปประมวลผลงานด้วย KPI ต่อไป รอติดตามกันต่อนะครับ ส่วนท่านผู้อ่านที่ต้องการอ่านบทความย้อนหลังเกี่ยวกับระบบ KPI เชิญติดตามได้เลยครับ สวัสดีตอนเช้าครับ

ประเมินผล KPI ด้วย Excel (1)

ระบบ KPI ตอน การกระจาย KPI ตามโครงสร้างองค์กร

ระบบ KPI ตอน ฐานข้อมูลพนักงานใน Excel

ระบบ KPI ตอน การประเมินผล KPI ของพนักงาน (1)

ระบบ KPI ตอน การกระจาย KPI ตามโครงสร้างองค์กร

สวัสดีครับท่านผู้อ่าน How to excel ทุกท่านในสัปดาห์นี้ผมขอนำเสนอบทความเรื่อง การกระจายน้ำหนักและการประมวลผลในระบบ KPI เนื่องจากเนื้อหาค่อนข้างยาวจึงต้องหั่นออกเป็นหัวข้อย่อยๆนำเสนอเป็นบทความต่อเนื่อง (Series) กันนะครับ มาดูตอนแรกกันก่อนเลยครับ เรื่องของการกระจายน้ำหนักของ KPI ตามระดับ(Level) ระบบ KPI ในองค์กรนิยมกระจาย KPI ออกเป็นระดับต่างๆตามโครงสร้างขององค์กร เพื่อให้สามารถประเมินประสิทธิภาพของหน่วยงานต่างๆได้อย่างละเอียด สมมุติโครงสร้างขององค์กรประกอบไปด้วยฝ่ายต่างๆ ในแต่ละฝ่ายจะประกอบด้วยแผนกต่างๆและในแต่ละแผนกจะประกอบไปด้วยบุคลากรหลายๆคน ดังนั้นเราจะได้ KPI ทั้งหมด 4 ระดับ นั่นคือ KPI ระดับองค์กร KPI ระดับฝ่าย KPI ระดับแผนก และ KPI ระดับบุคคล โดย KPI ในแต่ละระดับจะต้องครอบคลุมและสมดุลตามหลักของ Balance Score Card จากระดับของ KPI เราจะเห็นว่าพนักงานทุกคนต้องถูกประเมินด้วย KPI ระดับองค์กรเดียวกัน ในขณะที่พนักงานในฝ่ายใดๆทุกคนต้องถูกประเมินด้วย KPI ระดับฝ่ายของตนเอง ในขณะที่พนักงานในแผนกใดๆทุกคนต้องถูกประเมินด้วย KPI ระดับแผนกของตนเอง สุดท้ายพนักงานทุกคนต้องถูกประเมินด้วย KPI ของตัวเอง จากที่ได้อธิบายมาในบทความนี้ อยากให้ท่านผู้อ่านได้เห็นแนวคิดการกระจาย KPI ออกเป็นระดับต่างๆตามโครงสร้างก่อนครับ โดยระดับของ KPI อาจมีมากกว่า 4 ก็เป็นได้หากองค์ของท่านมีขนาดใหญ่ และเพื่อให้การเขียนสูตรในการคำนวณหรือประมวลผลใน Excel ผมขอเรียงระดับ KPI จากระดับใหญ่ไปถึงเล็กนะครับ โดยเริ่มจากหมายเลข 1 เป็นต้นไป ยกตัวอย่างเช่น KPI1 จะหมายถึง KPI ระดับองค์กร เอาหล่ะครับเดี๋ยวตอนต่อไปจะมากล่าวถึงการแบ่งระดับของพนักงานกันบ้างนะครับ ผู้ที่สนใจบทความเกี่ยวกับระบบ KPI ที่ผ่านมาเชิญติดตามได้เลยนะครับ

ประเมินผล KPI ด้วย Excel (1)

ระบบ KPI ตอน กระจายน้ำหนัก KPI ตามระดับพนักงาน

ระบบ KPI ตอน ฐานข้อมูลพนักงานใน Excel

ระบบ KPI ตอน การประเมินผล KPI ของพนักงาน (1)

VBA Excel ตอน ระเบียบวิธีการทำซ้ำ (Interation)

สวัสดีครับท่านผู้อ่าน How to excel ทุกท่านครับ บทความที่อยากจะนำเสนอในวันนี้ก็คือ ระเบียบวิธีการทำซ้ำแบบหนึ่งจุด (one-point interation method) ซึ่งหากท่านผู้อ่านได้ติดตามการนำเสนอบทความของผมในกลุ่มของการคำนวณทางวิศวกรรมด้วยระเบียบวิธีเชิงตัวเลข จะเห็นว่าผมได้นำเสนอการใช้ Goal Seek ซึ่งเป็น Excel function ที่ใช้ในการหาค่าเป้าหมาย โดยผมได้นำเสนอการประยุกต์ใช้ในการหารากของสมการ การแก้ปัญหาในด้านการออกแบบ เป็นต้น โดยพบว่าหลักของการคำนวณของฟังก์ชัน Goal Seek เป็นลักษณะของการทำซ้ำ โดยเริ่มจากการกำหนดค่าเริ่มต้น และ Excel จะทำการลองผิดลองถูก จนกระทั่งได้ค่าผลลัพธ์ตามเป้าหมายที่กำหนด ระเบียบวิธีการทำซ้ำก็เป็นอีกวิธีการหนึ่งที่ใช้ในการหารากของสมการ(ค่าเป้าหมาย) โดยเป็นวิธีการที่สามารถทำได้โดยง่าย โดยหลักการของระเบียบวิธีการทำซ้ำ คือการจัดฟังก์ชัน ที่กำหนดมาให้มีค่า x อยู่ตัวเดียวโดดๆทางด้านซ้ายของสมการ เช่น

f(x) = 3x^3 – 20x^2 + 1000x + 12000 = 0

ทำการจัดสมการใหม่ได้เป็น

x = (-3x^3 +20x^2 –12000)/1000

และให้เขียนสมการนี้ในรูปแบบของการทำซ้ำ ได้ดังนี้

x(i+1) = (-3xi^3 + 20xi^2 – 12000)/1000

หมายถึงว่าให้คำนวณค่า x(i+1) ซึ่งเป็นค่าใหม่ จากสมการด้านขวาซึ่งประกอบด้วยค่า xi เก่า

บางครั้งฟังก์ชันที่ต้องการหารากไม่มีเทอมที่ประกอบด้วยค่า x เดี่ยวๆที่สามารถแยกออกได้ง่ายเช่น

cos x - xe^x = 0

ให้ทำการบวกค่า x ใดๆลงในสมการทั้งสองข้างจะได้ว่า

x = cos x - xe^x + x

และจัดสมการให้อยู่ในรูปแบบของการทำซ้ำได้เป็น

x(i+1) = cos xi - xie^xi + xi

เงื่อนไขการยุติการทำซ้ำจะพิจารณาการเปลี่ยนแปลงของค่า x(i+1) และ x(i) โดยกำหนดเป็นค่าความผิดพลาดโดยประมาณ เท่ากับ (x(i+1) – x(i))/x(i+1)

จากรูปแบบการทำซ้ำเราสามารถนำไปเขียนเป็นฟังก์ชันย่อยใน VBA เพื่อค้นหาค่าเป้าหมาย (รากของสมการ) โดยการทำซ้ำ สามารถเขียนโค้ดใน VBA ได้ดังนี้

Function OnePointInter(init As Double) As Double

Dim error As double

Dim xold As double

Dim xnew As double

error = 1

xold = init

Do While error > 0.01

xnew = Cos(xold) – xold*Exp(xold) + xold

error = abs((xnew – xold)/xnew)*100

xold = xnew

Loop

OnePointInter = xnew

End Function

จาก VBA code ท่านผู้อ่านจะเห็นว่า มีการทำซ้ำใน Do While…Loop โดยการทำซ้ำจะยุติลงหากค่าความผิดพลาดโดยประมาณมีค่าน้อยกว่า 0.01% น่าสนใจอยุ่ไม่น้อยนะครับว่าหากเราเดาค่าเริ่มต้นได้ไม่เหมาะสม(ดูเทคนิดการเดาค่าเริ่มต้น) เราอาจจะหารากของสมการหรือค่าเป้าหมายไม่เจอเลยก็ได้ครับ ซึ่งก็จะทำให้เกิดการทำซ้ำอยู่ใน Do While…Loop ตลอดไป ซึ่งก็จะส่งผลให้ไฟล์ excel ของเราแฮงค์ได้ครับ ลองฝากท่านผู้อ่านเป็นการบ้านหน่อยครับว่าหากเราอยากให้การทำซ้ำยุติลงในกรณีที่เกิดการลู่ออกของผลลัพธ์ จะต้องกำหนดเงื่อนไขการทำซ้ำอย่างไรดี เดี๋ยวในโอกาสต่อไปจะมานำเสนอโอกาสของการลู่ออกของระเบียบวิธีการทำซ้ำว่าจะเป็นไปในรูปแบบใดบ้าง สวัสดีครับ

Excel function ตอน การค้นหาค่า Max & Min (Custom LOOKUP)

สวัสดีวันฝนตกครับ เข้าสู่เดือนเก้ามาสามวันฝนตกทุกวันเลยครับที่ กรุงเทพ วันนี้บทความด้าน Excel ที่อยากนำเสนอคือ การใช้งานฟังก์ชัน Max และ ฟังก์ชัน Min ซึ่งมีประโยชน์มากในการทำรายงานด้วย Excel เสนอผู้บริหาร มาดูรูปแบบของ Excel Function ทั้งสองกันเลยครับ

ฟังก์ชัน Max เป็นฟังก์ชันที่คืนค่ามากสุดในช่วงข้อมูล(เซลล์)ที่เลือกกลับมาขณะที่ฟังก์ชัน Min ก็จะคืนค่าน้อยสุดในช่วงข้อมูล(เซลล์)ที่เลือกกลับมา โดยข้อมูลที่เลือกต้องเป็นตัวเลขเท่านั้น

มาดูตัวอย่างสูตร excel กันเลยครับ

สมมุติผมมีข้อมูลยอดขายบันทึกไว้ที่ไฟล์ excel ดังนี้

	A	B
1	มกราคม	76212
2	กุมภาพันธ์	15379
3	มีนาคม	62220
4	เมษายน	83119
5	พฤษภาคม	33872
6	มิถุนายน	80881
7	กรกฎาคม	54263
8	สิงหาคม	35472
9	กันยายน	55361
10	ตุลาคม	71600
11	พฤศจิกายน	13330
12	ธันวาคม	22587

หากต้องการทราบยอดขายสูงสุดในรอบปีก็สามารถเขียนสูตร Excel ได้ดังนี้

= Max(B1:B12)

ซึ่งจะคืนค่า 83119 กลับมาให้ครับ

ในกรณีกลับกันหากต้องการทราบยอดขายต่ำสุดในรอบปีก็สามารถเขียนสูตร Excel ได้ดังนี้

=Min(B1:B12)

ซึ่งจะคืนค่า 13330 กลับมาให้ครับ

แต่ในความเป็นจริงผู้บริหารคงไม่ต้องการทราบแค่ยอดขายสูงสุดหรือต่ำสุดแน่ๆ สิ่งที่ผู้บริหารอยากทราบต่อคือ แล้วมันเกิดขึ้นเดือนไหนกันบ้าง ใช่แล้วครับเราต้องประยุกต์ Excel function ที่ผมเคยนำเสนอมาแล้ว ก่อนอื่นมาคิดกันก่อนครับ หากเราอยากทราบเดือนที่ยอดขายสูงหรือต่ำเราต้องทราบตำแหน่งของข้อมูลก่อน ซึ่งฟังก์ชันที่ใช้ก็คือฟังก์ชัน MATCH จากนั้นก็ใช้ฟังก์ชัน INDEX แสดงข้อมูลเดือนที่มียอดขายสูงสุดหรือต่ำสุดต่อไป สมมุติผมอยากทราบเดือนที่มียอดขายสูงสุด มาดูสูตร Excel กันเลยครับ

=MATCH(83119 ,$B$1:$B$12,0) ซึ่งจะคืนค่า 4 กลับมา หมายถึงว่ายอดขายสูงสุดอยู่ในลำดับที่ 4 ของช่วงข้อมูล $B$1:$B$12 จากนั้นเราจะใช้ฟังก์ชัน INDEX ให้คืนค่าข้อมูลในลำดับเดียวกันในช่วงข้อมูลเดือน(A1:A12) โดยเขียนสูตร Excel ได้ดังนี้

=INDEX($A$1:$A$12,4) ซึ่งจะคืนค่า เมษายน กลับมาให้ผู้ใช้ครับ

จากที่อธิบายมาเราสามารถรวมเป็นสูตร Excel เดียว โดยแทนเลข 4 ด้วยสูตร Match และแทนค่า 83119 ด้วยสูตร Max ได้ดังนี้

=INDEX($A$1:$A$12,MATCH(Max(B1:B12) ,$B$1:$B$12,0))

หากท่านผู้อ่านต้องการหาเดือนที่มียอดขายต่ำที่สุดก็เปลี่ยนฟังก์ชันMax เป็น Min ซึ่งสามารถทำได้โดยง่าย

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

Excel VLOOKUP ตอน ป้องกันการแสดง #N/A

สวัสดีครับท่านผู้อ่าน How to VBA Excel วันนี้ได้ลองย้อนกลับไปอ่านบทความเก่าๆใน Blog นี้มีบทความอยู่ 2-3 บทความที่กล่าวถึง VLOOKUP ซึ่งเป็น Excel Function ที่ผมได้นำเสนอไปในตอนแรกๆเนื่องจาก VLOOKUP เป้นฟังก์ชันที่ช่วยอำนวยความสะดวกให้กับผู้ใช้งาน Excel ที่เกี่ยวข้องกับการค้นหาข้อมูลในตารางได้ดีมาก แต่ผมเองกลับพบความบกพร่องของการนำเสนอบทความที่ประยุกต์ใชฟังก์ชัน VLOOKUP เหล่านี้ของผมเอง นั่นคือการไม่ได้กล่าวถึงข้อผิดพลาดที่ฟังก์ชัน VLOOKUP ส่งกลับมาครับ วันนี้เลยขอนำเสนอในปัญหานี้ครับ ที่มาของข้อผิดพลาดของ ฟังก์ชัน VLOOKUP ผมได้กล่าวไปแล้วในบทความเหล่านั้น ซึ่งจะเกิดจากการที่ Excel ไม่พบข้อมูลที่ต้องการค้นหาในหลักที่เรากำหนดในตารางข้อมูลนั้น ผลที่ถูกส่งกลับจาก VLOOKUP ก็คือ #N/A ซึ่งจะแสดงในเซลล์ที่เรากำหนด ทีนี้หากเราไม่ต้องการให้ excel แสดงค่า #N/A ออกมาในกรณีการใช้สูตร VLOOKUP เราจะใช้ฟังก์ชัน ISNA ในการตรวจค่าที่ถูกส่งกลับมาจาก VLOOKUP ว่าใช่ #N/A หรือไม่ หากใช่ ISNA จะคืนค่า true กลับมา หากไม่ใช่ จะคืนค่า false กลับมา ลองมาคิดกันแบบภาษามนุษย์เรานะครับ “ถ้า VLOOKUP ส่งค่า #N/A ให้แสดงข้อความว่า “หาไม่พบ” แต่หากหาพบให้แสดงค่านั้นออกมา” มาดูตัวอย่างกันเลยครับตามภาพที่ 1

ภาพที่ 1 ผลการประยุกต์ใช้ ISNA เพื่อตรวจสอบการคืนค่าจาก VLOOKUP

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

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