การใช้ excel คำนวณหาตนุลัคน์และตำแหน่งดวงดาวเจ้าของเรือนชะตา

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

อาทิตย์ ๑ จันทร์ ๒ อังคาร ๓ พุธ ๔ พฤหัสบดี ๕ ศุกร์ ๖ เสาร์ ๗ ราหู ๘ เกตุ ๙ มฤตยู ๑o โดยความหมายของดาวทั้ง 10 สามารถดูได้ เพิ่มเติมที่นี่  ตำแหน่งของดาวเจ้าของเรือนชะตาจะอ้างอิงจากตำแหน่งของดาวเกษตร ดาวเกษตร หมายถึง ความมั่นคง,ความยั่งยืน,ความเป็นปึกแผ่นตำ แหน่งดาวเกษตรไม่ได้ขึ้นอยู่กับ วันเดือนปีเกิดหรือเวลาเกิดของเจ้าชะตา แต่เป็นกฎเกณฑ์ที่ได้กำหนดไว้เป็นมาตรฐาน โดยจะอ้างอิงกับราศีต่างๆดังแสดงในภาพที่ 1 

ภาพที่ 1 ตำแแหน่งของดาวเกษตร

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

ภาพที่ 2 ตารางข้อมูลราศีใน excel

เราสามารถใช้สูตรใน excel : VLOOKUP ช่วยคำนวณตำแหน่งดาวเจ้าเรือนชะตาได้ ทันที ดังนี้

สมมุติสูตร excel ในเรือน ตนุ (C10)

=VLOOKUP(B10,$J$2:$M$13,3,FALSE)

เมื่อ copy สูตร excel ดังกล่าวลงในเรือนอื่นๆ (C11 - C21) จะได้ตำแหน่งของดาวเจ้าเรือนชะตาและตนุลัคน์ ดังภาพที่ 3 และเมื่อเขียนลงในจักราศีจะได้ดังภาพที 4

ภาพที่ 3 การคำนวณด้วยสูตร excel แสดงตำแหน่งดาวเจ้าของเรือนชะตาต่างๆ

ภาพที่ 4 จักราศีแสดงตำแหน่งเรือนชะตาและดาวเจ้าเรือน

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

ขอขอบคุณข้อมูลอ้างอิงจาก เรียนลัดโหราศาสตร์ บทที่ 4 รู้จักดาวเจ้าของเรือนชะตา

บทความที่เกี่ยวข้อง

การใช้ excel หาตำแหน่งดวงอาทิตย์และวันเกิด

การใช้ excel หาตำแหน่งลัคนาเกิด

การใช้ excel หาตำแหน่งเรือนชะตา

การใช้ excel ทดสอบความกระจายแบบปกติของข้อมูล 1 (How do you test data normality)

การใช้ excel ในบทความนี้จะเป็นส่วนเสริมของการวิเคราะห์ข้อมูลหรือการทดสอบสมมุติฐานซึ่งได้นำเสนอการทดสอบด้วยสถิติ t (t test) โดยทั่วไปแล้วเงื่อนไขการทดสอบสมมุติฐานทางสถิติ เรามักจะพบเงื่อนไขที่สมมุติว่าตัวอย่างที่สุ่มมานั้นต้องมีการกระจายแบบปกติ (Normal Distribution) ซึ่งหากตัวอย่างที่สุ่มมานั้นมีการกระจายไม่เป็นแบบปกติจะส่งผลให้เกิดความคลาดเคลื่อนตั้งแต่น้อยไปจนถึงยอมรับไม่ได้ ดังนั้นการทดสอบความกระจายแบบปกติของข้อมูลจึงเป็นสิ่งที่ไม่อาจหลีกเลี่ยงได้ หากท่านผู้อ่านเรียนสถิติมาบ้างคงจำได้ว่าการตรวจสอบลักษณะการกระจายตัวสามารถทำได้โดยการพิจารณา แผนภูมิฮิสโตแกรมของกลุ่มข้อมูล โดยฮิสโตแกรมจะมีรูปร่างสมมาตรรอบจุดๆหนึ่งหากข้อมูลมีการกระจายแบบปกติ ซึ่งเราจะเรียกวิธีการนี้ว่า การวิเคราะห์ลักษณะการกระจายตัว (Distribution analysis) โดยในบทความถัดไปจะนำเสนอวิธีการดังกล่าวด้วยการใช้ excel ตั้งแต่การประยุกต์ใช้สูตร excel : rept หรือใช้เครื่องมือการวิเคราะห์ข้อมูล (Data analysis)  ของ excel ในบทความนี้จะนำเสนอการวิเคราะห์ข้อมูลโดยวิธี Normal Quantile Plot  ซึ่งเหมาะกับจำนวนข้อมูลที่ไม่มากนัก

สมมุติฐาน

ตัวอย่างที่เก็บมาจากประชากรที่มีการกระจายแบบ Normal  ก็ควรจะมีการกระจายของค่าตัวอย่างเป็นแบบ Normal เช่นกัน ซึ่งหากสมมุติฐานดังกล่าวเป็นจริงเราจะพบว่าความสัมพันธ์ระหว่าง ค่าตัวอย่างกับค่าสถิติ Z จะใกล้เคียงเส้นตรง ที่มีความชัน (Slope) เท่ากับ  ค่าความแปรปรวนของประชากร และจุดตัดบนแกน Y (Intercept) จะเท่ากับ ค่าเฉลี่ยของประชากร ซึ่งหมายถึงว่า  จุดตัดของแนวเส้นบนแกน Y เป็นค่าเฉลี่ยของตัวอย่างด้วย

Normal Quantile Plot

เป็นการ plot จุดตัดระหว่างค่า Z quantiles กับค่าตัวอย่าง สมมุติว่า i = 1, 2 ......n  เป็นค่าอันดับของตัวอย่าง หลังจากเรียงลำดับค่าจากน้อยไปมากแล้ว  ค่า quantiles ของข้อมูลหาได้จากสมการ  ( i - 0.5 ) / n  ดังนั้นขั้นตอนการทำ Normal quantile plot สามารถทำได้ดังนี้

1.  เรียงลำดับค่าตัวอย่างที่มีอยู่จากน้อยไปหามาก (Sort the data)
2. คำนวณค่า Sample quantiles จากสมการ  ( i - 0.5 ) / n
3. ค่า Sample quantile ที่ได้ตามข้อ 2 คือ ค่าพื้นที่ใต้กราฟของ Z-Distribution หรือ Standard Normal Distribution  ให้นำค่า Sample quantiles ดังกล่าวไปหาค่า Z
4. plot จุดของคู่ลำดับ (x,y) Z percentiles กับ ค่าตัวอย่าง (แกน Y)
จากขั้นตอนที่กล่าวมาเราสามารถใช้ excel ช่วยคำนวณและ plot กราฟได้ โดยมีแนวคิดดังนี้

1. เรียงลำดับค่าตัวอย่างโดยใช้การ Sort ใน excel
2. คำนวณค่า Sample quantiles ได้โดยกำหนดสูตร excel ได้ง่ายๆ
3. คำนวณค่า Z ได้โดยใช้สูตร excel : NORMSINV
4. ใช้ excel plot กราฟ แบบ Scatter

ตัวอย่าง
มีการสุ่มค่าความร้อนของถ่านหินในเหมืองหนึ่ง ได้ทั้งหมด 6 ค่าดังนี้
7950   7890   7910   8040    7970    7840
เราสามารถดำเนินการตามขั้นตอนที่กล่าวมาแล้วใน excel ได้ดังภาพที่ 1

ภาพที่ 1 การใช้ excel Plot Normal Quantile

ท่านผู้อ่านจะสังเกตุจากความเป็นเส้นตรงของ Normal Quantile Plot ได้โดยการเพิ่มเส้นแนวโน้มลงในแผนภูมิ Normal Quantile โดยหากค่า R มีค่าเกินกว่า 0.9 แสดงให้เห็นว่าข้อมูลมีแนวโน้มเป็นเส้นตรง ซึ่งนั่นหมายถึงว่าตัวอย่างที่เราสุ่มมามีการกระจายแบบปกติ ซึ่งเราสามารถนำตัวอย่างดังกล่าวไปใช้ในการทดสอบสมมุติฐานต่อไป

ข้อสังเกต

การสังเกตลักษณะการเรียงตัวของข้อมูลและใช้เป็นข้อมูลในการตัดสินใจเรียกว่าการตัดสินใจด้วยการวิเคราะห์เชิงคุณภาพ ( Qualitative )

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

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

Excel tips : การกำหนดชื่อข้อมูลแบบคงที่ใน excel (Static data)

วันนี้ขอนำเสนอบทความ tips การใช้ excel เพื่อเป็นพื้นฐานในการใช้ excel หรือใช้สูตร excel ในงานอื่นๆต่อไปครับ การกำหนดชื่อข้อมูลใน excel มีประโยชน์อย่างมาก ในกรณีที่เราต้องเรียกใช้งานข้อมูลนั้นบ่อยๆ การพิมพ์ช่วงของข้อมูลหรือตำแหน่งเซลล์ของข้อมูลมีโอกาสเกิดข้อผิดพลาดได้เช่น สูตร excel สำหรับการคำนวณภาษีมูลค่าเพิ่ม หรือ การกำหนดตารางข้อมูลเพื่อใช้ในสูตร excel : vlookup เป็นต้น ผมจะแบ่งชื่อข้อมูลแบบคงที่ใน excel ออกเป็น 3 รูปแบบดังนี้ครับ

1. ข้อมูลประเภท ค่าคงที่ เช่น ภาษีมูลค่าเพิ่ม อัตราแลกเปลี่ยน
2. ข้อมูลประเภทเป็นช่วงข้อมูลแบบรายการ (อาจจะเป็นแบบแถวเดียวหรือหลักเดียว) ผมจะเรียกว่าข้อมูลแบบเวกเตอร์ เราจะพบการใช้งานแสดง List รายการต่างๆให้ผู้ใช้เลือก
3. ข้อมูลประเภทตาราง จะเรียกว่าข้อมูลแบบ Matrix เราจะพบในการใช้งานสูตร excel : vlookup เป็นส่วนใหญ่

ข้อดีของการกำหนดชื่อข้อมูล

1. ลดความผิดพลาดในการใช้สูตร excel
2. ง่ายต่อการเรียกใช้ข้อมูลเนื่องจากชื่อข้อมูลที่กำหนดมีความหมายในตัวเองเช่น vat , customer_name , Table เป็นต้น
3. ง่ายต่อการพิมพ์สูตร excel

ข้อเสีย

1. ใช้ได้อย่างถูกต้องกับข้อมูลที่ไม่มีการเปลี่ยนแปลงขณะใช้งาน

วิธีการกำหนดชื่อใน excel 2007 ขึ้นไป

1. เลือกเมนู สูตร
2. เลือกทูลบาร์ กำหนดชื่อ จะปรากฎหน้าต่าง ชื่อใหม่ ขึ้นมาดังรูปที่ 1
3. กำหนดชื่อข้อมูลในช่อง ชื่อ
4. เลือกขอบเขตการใช้ข้อมูล ในหัวข้อนี้เราสามารถกำหนดให้ข้อมูลที่เรากำหนดสามารถเรียกใช้ได้ตามขอบเขตที่กำหนด อาจจะทั้งหมด (สมุดงาน) หรือว่า เจาะจงใช้ได้เฉพาะ WorkSheet
5. กำหนดตำแหน่งของ cells หรือช่วงของ Cells ที่ต้องการกำหนดชื่อ ในช่องอ้างอิงไปยัง
1. ตัวอย่างเช่น =Sheet1!$F$12 จะเป็นข้อมูลแบบ ค่าคงที่
2. ตัวอย่างเช่น =Sheet1!$F$12:$F$15 จะเป็นข้อมูลแบบเวกเตอร์
3. ตัวอย่างเช่น =Sheet1!$F$12:$G$15 จะเป็นข้อมูลแบบ Matrix
6. คลิกปุ่มตกลง

ภาพที่ 1 ตัวอย่างการกำหนดชื่อข้อมูลใน excel

ตัวอย่างการใช้งาน excel
ข้อมูลแบบค่าคงที่
พิมพ์ค่า 7% ใน Cell A1 กำหนดชื่อข้อมูลแบบค่าคงที่ ชื่อ vat โดยอ้างถึง Cell A1 หากต้องการคำนวณภาษีมูลค่าเพิ่มจากราคาสินค้าใน cells B3จะเขียนสูตร excel ใน Cell C3 ได้ดังนี้
สูตร excel :  C3= vat*B3
ข้อมูลแบบเวกเตอร์
พิมพ์ข้อมูลชื่อลูกค้าลงใน Cells A1 – A5
กำหนดชื่อข้อมูลแบบเวกเตอร์ ชื่อ Customer โดยอ้างถึง $A$1:$S$5
สามารถนำมาใช้เป็น List data ในการตรวจสอบความถูกต้องของข้อมูล โดยกำหนดแหล่งข้อมูล = Customer
ข้อมูลแบบ Matrix
พิมพ์ข้อมูลลงใน Cells A1 – C5
กำหนดชื่อข้อมูลแบบ Matrix ชื่อ data โดยอ้างถึง $A$1:$C$5
สามารถนำมาใช้ในสูตร excel : vlookup เช่น
=vlookup(B4,data,2,false) เป็นต้น

ก่อนจบบทความนี้อยากให้ท่านผู้อ่านคิดต่อนะครับว่า หากต้องการเพิ่มข้อมูลในแบบเวกเตอร์หรือแบบ Matrix เช่น มีลูกค้าเพิ่มขึ้นมาอีก 2 ราย เราจะทำอย่างไร หากเป็นการกำหนดข้อมูลแบบเดิมก็ต้องกลับไปแก้ไขข้อมูลในช่องอ้างอิงไปยังเพื่อให้ครอบคุลมช่วงของข้อมูลที่เพิ่มขึ้นมา  ซึ่งการกระทำดังกล่าวจะทำให้ไม่สะดวกในการใช้งาน ซึ่งเป็นจุดด้อยของการกำหนดชื่อข้อมูลแบบคงที่ ดังนั้นในบทความต่อไปผมจะนำเสนอวิธีการกำหนดชื่อข้อมูลแบบพลวัตร (Dynamic Data) เพื่อให้ข้อมูลมีการอัพเดตและครอบคลุมข้อมูลที่เพิ่มขึ้นมาได้อย่างอัตโนมัติ สวัสดีครับ

สูตร excel : การดำเนินการระหว่างตัวเลขและTime data

สูตร excel วันนี้อยากจะนำเสนอข้อผิดพลาดที่ผมก็ยังไม่แน่ใจนักว่าท่านผู้ใช้ สูตร excel จะเผลอเหมือนผมหรือไม่นะครับ เอาเป็นว่าอยากยกให้เป็นข้อควรระวังก็แล้วกันนะครับ สมมุติท่านผู้อ่านได้รับข้อมูลเป็นจำนวนเวลาเข้ามาก็แล้วกันนะครับ เช่น จำนวนเวลาที่ทำโอที เวลาที่ใช้ทดสอบผลิตภัณฑ์ หรือใช้ดำเนินการกับข้อมูลดิบที่จะนำไปคำนวณ kpi ค่า Avaliabilty Rate หรือ oee เป็นต้นครับ ซึ่งหากได้รับข้อมูลใน excel จะอยู่ในรูปแบบดังนี้ครับ

h.m ซึ่งผมว่ามันเข้าใจในภาษามนุษย์เราใช่มั้ยครับเช่น 4.53 ก็หมายถึง 4 ชั่วโมง 53 นาที เอาหล่ะครับหากเราต้องการแปลงตัวเลขนี้ให้เป็นข้อมูลประเภท Time จะทำอย่างไร เรามาดูกันขั้นตอนกันเลยครับ

สูตร excel

1. ใช้สูตร excel : Floor เพื่อปัดเป็นจำนวนเต็มจะได้เป็น =FLOOR(4.53,1) ผลที่ได้คือจำนวนชั่วโมงเท่ากับ 4
2. นำผลที่ได้จากการใช้สูตร excel : Floor ลบออกจากตัวเลขตั้งต้นและคูณด้วย 100 จะได้เศษนาทีออกมาครับ

ภาพที่ 1 ผลการใช้สูตร excel

เขียน excel vba

1. ใช้ฟังก์ชัน Int เพื่อแปลงตัวเลขเป็นจำนวนเต็ม เขียนได้ดังนี้ h = Int(4.53) ผลที่ได้คือ 4
2. นำผลที่ได้จากการใช้ฟังก์ชัน Int ลบออกจากตัวเลขตั้งต้นและคูณด้วย 100 จะได้เศษนาทีออกมาครับ

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

time_minute = h*60 + m

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

Function NumToMinute(ByVal num As Double) As Integer
Dim h As Integer
h = Int(num)
NumToMinute = h * 60 + (num - h) * 100
End Function

ผลการเรียกใช้ฟังก์ชัน NumToMinute แสดงได้ดังภาพที่ 2

ภาพที่ 2 การเรียกใช้สูตร excel : NumToMinute

มาถึงตรงนี้ท่านผู้อ่านคงเห็นช่องทางในการต่อยอดนะครับ เราสามารถดำเนินการกับจำนวนเวลานี้ได้โดยง่าย โดยทั่วไปเราก็นิยมดำเนินการกับเวลาสองลักษณะครับคือ ผลรวมของเวลา หรือผลต่างของเวลา เช่นในการคำนวณค่า oee จำนวนเวลาที่เครื่องจักรเกิด BreakDown (ใช้ผลรวม) หรือระยะเวลาการซ่อมเครื่องจักรสองเครื่องต่างกันเท่าไหร่(ผลต่าง) เรามาดูกันครับว่าเราจะสร้างสูตร Excel ได้อย่างไร

ผลรวมของเวลา

Function AddTime(tm1 As Integer, tm2 As Integer) As Integer
AddTime = tm1 + tm2
End Function

ผลต่างของเวลา

Function MinusTime(tm1 As Integer, tm2 As Integer) As Integer
If tm1 >= tm2 Then
MinusTime = tm1 - tm2
Else
MinusTime = tm2 - tm1
End If
End Function

ผลการใช้สูตร Excel ทั้งสองแสดงได้ดังภาพที่ 3

ภาพที่ 3 การใช้สูตร Excel ร่วม

ในบางกรณีเราต้องการแยกองค์ประกอบของจำนวนนาทีให้อยู่ในรูปแบบของจำนวนชั่วโมงและจำนวนนาที (h.m) เราสามารถเขียนฟังก์ชันได้ดังนี้

Function MinuteToNum(ByVal tm As Integer) As Double
Dim h As Integer
Dim m As Integer
m = tm Mod 60
h = Int(tm / 60)
MinuteToNum = h + m / 100#
End Function

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

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

การใช้ Excel : Import Text File ลง excel

การใช้ Excel ในบทความนี้ขอนำเสนอการ Import Text File ลง excel ครับ ข้อมูลบางอย่างที่ต้องการคำนวณอาจถูกสร้างในรูปแบบของ Text File เช่น ข้อมูลจากเครื่องรูดบัตร หรือข้อมูลจากเครื่องอ่านบาร์โค้ด เป็นต้น Text File เหล่านี้จะมีรูปแบบการจัดเก็บในลักษณะของฐานข้อมูลอยู่แล้ว บทความนี้ผมขอยกตัวอย่าง Text File ข้อมูลเครื่องรูดบัตรเข้าออกยี่ห้อ TAFF ครับซึ่งมีรูปแบบการเก็บข้อมูลในแต่ละแถวดังนี้

รหัสพนักงาน สถานะการรูด วันเดือนปี เวลารูดบัตร ตัวเลขตรวจสอบ

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

ภาพที่ 1 ตัวอย่างข้อมูลใน Text File

เริ่มต้นให้ไปที่ แฟ้ม->เปิด จะปรากฏหน้าต่างเปิดไฟล์ ให้ท่านไปยังโฟล์เดอร์ที่เก็บ Text File และให้เลือกชนิดแฟ้มในหน้าต่างเปิดไฟล์ เป็น All Files และให้เลือก Text File ที่ต้องการ Import เข้า Excel จากนั้นให้คลิก เปิด จะปรากฏหน้าต่างตัวช่วยสร้างการนำเข้าข้อความดังแสดงในภาพที่ 2 ในกรอบของชนิดข้อมูลดั้งเดิมให้เลือก มีการใช้ตัวคั่น กำหนดให้เริ่มนำเข้าในแถวที่ 1 ให้คลิกปุ่มถัดไป

ภาพที่ 2 ตัวช่วยสร้างการ Import Text File Step 1

เมื่อคลิกปุ่มถัดไปจะปรากฏหน้าต่างตัวช่วยสร้างการ Import Text File Step 2 ดังแสดงในภาพที่ 3 ให้เลือกตัวคั่นเป็นแท็บและช่องว่าง (เนื่องจากข้อมูลใน Text File ที่กำลัง Import แยกข้อมูลในแต่ละหลักด้วยช่องว่างนั่นเอง) จากนั้นให้คลิกปุ่ม ถัดไป

ภาพที่ 3 ตัวช่วยสร้างการ Import Text File Step 2

จะปรากฏหน้าต่างตัวช่วยสร้างการ Import Text File Step 3 ดังแสดงในภาพที่ 4 ในส่วนของหลักวันเดือนปี ให้เลือก รูปแบบข้อมูลคอมลัมน์ เป็นแบบวันที่และเลือกรูปแบบเป็น ปดว (ปีเดือนวัน) จากนั้นให้คลิกปุ่ม เสร็จสิ้น ถือเป็นการสิ้นสุดการ Import Text File ของการรูดบัตรลงใน Excel แล้วครับ ข้อมูลที่ถูกนำเข้าแสดงได้ดังภาพที่ 5

ภาพที่ 4 ตัวช่วยสร้างการ Import Text File Step 3

ภาพที่ 5 ข้อมูลเวลาที่ถูก Import เข้า Excel

ท่านผู้อ่านจะเห็นว่าข้อมูลที่นำเข้าเป็นไปตามที่เรากำหนดในตัวช่วยสร้างการ Import Text File ทุกประการ อ้อ หากข้อมูลในคอลัมน์ไหนไม่ต้องการ ก็สามารถกำหนดได้ในขั้นตอนที่ 3 ในภาพที่ 4 ได้นะครับ ทำได้โดยคลิกเลือกคอลัมน์ที่ไม่ต้องการแล้วกำหนดรูปแบบข้อมูลเป็น ไม่ต้องนำเข้าคอลัมน์(ข้าม) ได้ครับ

ครับมาถึงตรงนี้ท่านผู้อ่านคงเข้าใจการ Import Text File แล้วนะครับ คำถามคือหากต้องการสรุปการทำงานประจำเดือน (30 ไฟล์) พนักงานมี 500 ท่าน การเลือกเปิดไฟล์แบบที่นำเสนอไปคงไม่เหมาะ เพราะเป็นการทำซ้ำๆกัน การเขียน vba excel จะช่วยท่านได้ เดี๋ยวจะนำเสนอในโอกาสต่อไปนะครับ สวัสดีครับ

การใช้งาน excel : การจัดการ Text Value

Text Value เป็นรูปแบบที่ท่านผู้อ่าน how to excel ต่างประสบปัญหากันมาบ้างไม่มากก็น้อย ตัวผมเองก็ประสบมาด้วยตัวเองก็พอสมควรดังได้เคยนำมาเล่าในหัวข้อการค้นหาข้อมูลด้วย VLOOKUP ไปแล้วครับ วันนี้ก็เลยมีความตั้งใจที่จะเสนอแนะลักษณะงานที่เกี่ยวกับ Text value ในการใช้งาน excel เนื่องด้วยบทความนี้เป็นบทความที่ 50 พอดี ย้อนกลับไปก็ให้ฉงนเหมือนกันว่าผมเขียนไปบทความไปเกือบครึ่งร้อยซะแล้ว วันนี้เลยขอนำเสนอบทความที่สบายๆเป็นการผ่อนคลายไปในตัวหล่ะกันนะครับ

วันนี้ผมอยากนำเสนอฟังก์ชัน excel ที่ใช้ในการจัดการข้อมูลประเภท Text Value ซัก 3 ฟังก์ชัน เนื่องด้วยอาทิตย์ที่ผ่านมามีโอกาสได้ใช้จัดการวางแผนการผลิต เรามาเริ่มสูตร Excel สูตรแรกกันเลยครับ

1. UPPER / LOWER เป็น excel function ที่ใช้ในการแปลงอักษรทั้งหมดในข้อความให้เป็นตัวพิมพ์ใหญ่(UPPER) หรือเป็นตัวพิมพ์เล็ก(LOWER) ท่านผู้อ่านใช้ excel function ในด้านใดบ้างครับ ผมเองจะใช้ในการตรวจสอบเงื่อนไขครับ เพื่อป้องกันผู้ใช้พิมพ์ตัวพิมพ์เล็กหรือพิมพ์ใหญ่ทำให้ง่ายต่อการตรวจสอบครับ โดยเรานำข้อความมาทำการแปลงก่อนนำไปเปรียบเทียบเงื่อนไขครับ ตัวอย่างเช่น หากตัวอักษร 2 ตัวแรกของข้อความที่นำเข้ามาเป็นตัวอักษร “EZ” ให้พิมพ์คำว่า yes หากมิใช่ให้พิมพ์ no ท่านผู้อ่านจะเห็นว่าความเสี่ยงที่ผู้ใช้จะพิมพ์ตัวอักษร 2 ตัวแรกในรูปแบบที่แตกต่างกันมีความเป็นไปได้ เช่น Ez , ez , eZ , EZ ดังนั้นเพื่อให้การตรวจสอบความถูกต้องสามารถทำได้ง่าย เราอาจเขียนเป็นสูตร excel ได้ดังนี้ B2 =if(Left(UPPER(A2),2) = “EZ” ,”EZ” , “NO”) จากตัวอย่างที่ผมได้นำเสนอสามารถแสดงได้ดังภาพที่ 1

ภาพที่ 1 ตัวอย่างการใช้งาน excel function ในการตรวจสอบเงื่อนไข

excel function : UPPER / LOWER ในการเขียน VBA excel จะใช้ฟังก์ชัน UCase / LCase

ท่านผู้อ่านจะเห็นว่าผมใช้ฟังก์ชัน Left เพื่อตัดเอาตัวอักษร 2 ตัวแรกด้านซ้ายมาแปลงเป็นตัวพิมพ์ใหญ่และตรวจสอบว่าเป็นตัวอักษร “EZ” หากใช่ก็กำหนดให้เท่ากับ EZ ในหลัก B หากไม่ใช่จะกำหนดให้เท่ากับ NO ในหลัก B

2. การประยุกต์ฟังก์ชัน Find และฟังก์ชัน Left Mid ในการแยกชื่อและนามสกุลออกจากกัน ในบางครั้งเราอาจมีความประสงค์ที่จะแยกชื่อและนามสกุลออกจากันเพื่อนำไปใช้งานอื่นๆ มาดูแนวคิดการแยกชื่อและนามสกุลกันเลยครับ โดยปกติชื่อและนามสกุลจะถูกคั่นด้วยช่องว่างดังนั้นขั้นตอนแรกต้องเราต้องหาตำแหน่งของช่องว่างในชื่อ-นามสกุล จากนั้นใช้ฟังก์ชั่น Left เพื่อแยกชื่อมาแสดง จากนั้นเราจะใช้ฟังก์ชัน Mid ในการแยกนามสกุล ดังแสดงเป็นสูตร Excel ในภาพที่ 2

ภาพที่ 2 การแยกชื่อนามสกุลออกจากกัน

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

การใช้งาน Excel : ประยุกต์ VLOOKUP คำนวณค่าคอมมิชชั่นแบบขั้นบันได

การใช้งาน excel ในบทความนี้ขอยกตัวอย่างการคำนวณค่าคอมมิชชั่นในลักษณะแบบขั้นบันได ซึ่งหากมองดีๆแล้วก็จะคล้ายกับการคำนวณภาษีหรือการคิดค่าไฟฟ้านั่นเอง ขอยกตัวอย่างการคำนวณแบบขั้นบันไดให้ท่านผู้อ่านได้เข้าใจให้ง่ายขึ้นครับ สมมุติว่าฝ่ายการตลาดได้กำหนดการจ่ายค่าคอมมิชชั่นจากการขายดังนี้

1. ยอดขายส่วน 0 – 5,000 บาทแรก จะคิดค่าคอมมิชชั่นให้ 1.5 %
2. ยอดขายส่วน 5,000 – 10,000 บาท ต่อไปคิดค่าคอมมิชชั่นให้ 2.5 %
3. ยอดขายส่วน 10,000 – 20,000 ต่อไปคิดค่าคอมมิชชั่นให้ 3.5 %
4. ยอดขายส่วน 20,000 – 50,000 ต่อไปคิดค่าคอมมิชชั่นให้ 5.0 %
5. ยอดขายส่วนที่ เกิน 50,000 คิดค่าคอมมิชชั่นให้ 7.5 %

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

ตารางที่ 1 ค่าคอมมิชชั่นสูงสุดในแต่ละขั้น

ช่วงของยอดขาย	คอมมิชชั่นสูงสุดในช่วง	เปอร์เซนต์คอมมิชชั่น
0 – 5,000	= 5000*0.015	1.5 %
5,000 – 10,000	= 5,000*0.025	2.5 %
10,000 – 20,000	=10,000*0.035	3.5 %
20,000 – 50,000	=30,000*0.050	5.0 %
50,000 up	= ส่วนเกิน*0.075	7.5 %

หลักในการคำนวณ หากยอดขายรวมของเราอยู่ในช่วงใด ค่าคอมมิชชั่นที่ได้จะเท่ากับ ผลรวมของค่าคอมมิชชั่นสูงสุดของชั้นที่ต่ำกว่าทั้งหมด บวกด้วยผลคูณระหว่างยอดขายที่เหลือในช่วงนี้กับเปอร์เซนต์คอมมิชชั่นในช่วงนี้ ดังนั้นสามรถใช้ excel สร้างตารางเก็บค่าผลรวมคอมมิชชั่นและค่าต่ำสุดของยอดขายในแต่ละช่วงดังแสดงในภาพที่ 1 โดยสูตรการคำนวณค่าผลรวมคอมมิชชั่นในหลัก c ได้ดังนี้ สมมุติคำนวณค่าใน B3 = B2 + (A3-A2)*C2

ภาพที่ 1 ตารางใน excel ซึ่งเก็บข้อมูลการตำนวณค่าคอมมิชชั่น

จากหลักการคำนวณค่าคอมมิชชั่นจะเห็นว่าเราสามารถนำสูตร Excel : VLOOKUP มาประยุกต์ได้โดยการกำหนด option การค้นหาเป็น true เพื่อหาตำแหน่งช่วงข้อมูลค่าที่น้อยกว่ายอดขายได้จริง ตัวอย่างเช่น นาย ก มียอดขายในเดือน มค เท่ากับ 8,800 บาท การคำนวณค่าคอมมิชชั่นจะเริ่มจากหาตำแหน่งของยอดขายที่ต่ำกว่า 8,800 บาทในตารางจะพบว่ามีค่าเท่ากับ 5,000 บาท และจะพบว่าค่าคอมมิชชั่นสะสมในชั้นนี้เท่ากับ 75 บาท เมื่อรวมกับผลต่างในช่วงนี้ซึ่งมีค่าเท่ากับ 3,800*3.5% = 95 บาท รวมเป็นค่าคอมมิชชั่นที่ได้เท่ากับ 75+95= 170 บาท จากวิธีการคำนวณ สมมุติเรามีข้อมูลยอดขายรวมของพนักงานดังแสดงในภาพที่ 2

ภาพที่ 2 ตัวอย่างยอดขายเพื่อคำนวณค่าคอมมิชชั่น

จากแนวคิดการคำนวณสามารถเขียนเป็นสูตร Excel ได้ดังนี้ (สมมุติคำนวณค่าคอมมิชชั่นของพนักงานในแถว A11 = VLOOKUP(B11,$A$2:$C$6,2,TRUE) + (B11 -VLOOKUP(B11,$A$2:$C$6,1,TRUE))*VLOOKUP(B11,$A$2:$C$6,3,TRUE)

ผลการคำนวณด้วย Excel แสดงได้ดังภาพที่ 3

ภาพที่ 3 ผลการคำนวณค่าคอมมิชชั่นตามสูตร Excel

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

ปล สนใจตัวอย่างการคำนวณค่าคอมมิชชั่นในรูปแบบอื่น ดู ที่นี่ ครับ

การใช้งาน 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 ของเราแฮงค์ได้ครับ ลองฝากท่านผู้อ่านเป็นการบ้านหน่อยครับว่าหากเราอยากให้การทำซ้ำยุติลงในกรณีที่เกิดการลู่ออกของผลลัพธ์ จะต้องกำหนดเงื่อนไขการทำซ้ำอย่างไรดี เดี๋ยวในโอกาสต่อไปจะมานำเสนอโอกาสของการลู่ออกของระเบียบวิธีการทำซ้ำว่าจะเป็นไปในรูปแบบใดบ้าง สวัสดีครับ