การทำงานของฟังก์ชัน Index และ Match

ผมเคยตอบคำถามในกระทู้หนึ่ง โดยเสนอใช้วิธีแก้ปัญหาด้วยฟังก์ชัน Index และ Match แต่เจ้าของกระทู้นั้น ก็ถามกลับมาว่า"Function Index and Match ทำงานอย่างไรค่ะ เคยอ่านในหนังสือแต่อธิบายไม่เข้าใจนะคะ จะลองเอาไปใช้กับของจริงที่ทำงาน"

ผมขออธิบายดังนี้


รูปที่ 1


การทำงานของ INDEX
สมมติว่า เรามีรายชื่อเดือน ม.ค. - ธ.ค. อยู่ในช่วง A1:A12 และ มีชื่อเดือนภาษาอังกฤษ Jan - Dec อยู่ในช่วง B1:B12 ถ้า ณ เซลล์ใดๆ เราพิมพ์สูตรว่า =INDEX(A1:A12, 10) เราจะได้ผลลัพธ์เป็น "ต.ค." หรือถ้าพิมพ์สูตรว่า =INDEX(B1:B12, 10) จะได้ผลลัพธ์เป็น"Oct" เป็นต้น


รูปที่ 2


สำหรับการใช้ INDEX ผมมักจะบอกผู้เรียนว่า "อยากรู้อะไร ก็ให้ INDEX ช่วงนั้น" เช่น อยากรู้ชื่อเดือนภาษาไทย ก็ INDEX ช่วงเดือนภาษาไทย (A1:A12) หรืออยากรู้ชื่อเดือนภาษาอังกฤษ ก็ INDEX ช่วงเดือนภาษาอังกฤษ (B1:B12) เป็นต้น

โดยโครงสร้างของ Index คือ => INDEX( ช่วงผลลัพธ์ , ลำดับที่ต้องการ )

****************************************************

การทำงานของ MATCH
ในทางกลับกัน ถ้าเราทำการค้นหาคำว่า "ต.ค." จากช่วง A1:A12 จะได้ผลลัพธ์เป็น 10 (หมายถึงคำว่า "ต.ค." อยู่ในอันดับที่ 10 จากช่วง A1:A12), หรือจะค้นหาคำว่า "Oct" จากช่วง B1:B12 ก็จะได้ผลลัพธ์เป็น 10 เช่นกัน (หมายถึงคำว่า "Oct" อยู่ในอันดับที่ 10 จากช่วง B1:B12)


รูปที่ 3


กรณีนี้เราหาอันดับที่ ของข้อมูลที่ต้องการได้จากฟังก์ชัน MATCH โดยมีโครงสร้างฟังก์ชันคือ
MATCH( ข้อความที่จะค้นหา , ช่วงข้อมูลที่จะค้นหา , รูปแบบการค้นหา )
สำหรับรูปแบบการค้นหานี้ โดยทั่วไปจะกำหนดให้เป็นศูนย์ (0) เสมอ

****************************************************

สำหรับกรณีการใช้ INDEX ควบคู่กับ MATCH ผมขอยกตัวอย่าง ดังนี้
ถ้าเราพิมพ์ชื่อเดือนใดๆ เป็นภาษาไทยลงในเซลล์ D2 แล้วต้องการให้ที่ E2 แสดงชื่อเดือนเป็นภาษาอังกฤษโดยอัตโนมัติ พูดง่ายๆ คือ ให้ผลลัพธ์ที่ E2 แปรผันไปตาม D2 นั่นเอง
กรณีนี้ ที่ E2 พิมพ์สูตร =INDEX(B1:B12, MATCH(D2,A1:A12,0) )


รูปที่ 4


อย่างที่ผมบอกหลักการจำไปแล้วว่า "อยากรู้อะไร ก็ให้ Index ช่วงข้อมูลนั้นๆ" เพราะฉะนั้นที่ E2 เราอยากรู้ชื่อเดือนภาษาอังกฤษ เราจึงเริ่มต้นสูตรว่า =INDEX(B1:B12,

แต่เราจะให้แสดงผลเป็นลำดับที่เท่าไรของช่วง B1:B12 นั้น ต้องมาพิจารณาชื่อเดือนภาษาไทยใน D2 โดยเอา D2 ไปเทียบกับช่วง A1:A12 โดยใช้ MATCH ก็จะได้ลำดับที่ออกมา ผลลัพธ์จาก MATCH ก็จะส่งไปยัง INDEX อีกทอดหนึ่ง ก็จะได้ชื่อเดือนภาษาอังกฤษ ตามที่ต้องการ

มีข้อแม้ว่า ลำดับที่ในช่วง A1:A12 และ B1:B12 จะต้องสัมพันธ์กัน ผลลัพธ์จึงจะถูกต้อง เช่น ถ้า A1 เริ่มต้นด้วย "ม.ค.", B1 ก็ต้องเริ่มต้นด้วย "Jan" เช่นกัน แต่ถ้า B1 เป็น "May" ลำดับที่ถัดลงไปก็จะผิดตามกันไป เช่นเดียวกับการติดกระดุมผิดเม็ดนั่นล่ะครับ


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

INDEX(array, row_num, [column_num], [area_num])

ซึ่งจะได้กล่าวถึงในโอกาสต่อไป 

Read More

แปลงตัวเลขจำนวนเงินเป็นข้อความ (ไทย-อังกฤษ)

ตลอดระยะเวลาหลายปีที่ผมบรรยายหลักสูตร Excel เรื่องที่ผู้เข้าอบรมมักจะสอบถามกันมากคือ การแปลงจำนวนเงินเป็นข้อความภาษาอังกฤษ ทั้งนี้ ผู้ที่ใช้งาน Excel อยู่บ่อยๆ คงจะคุ้นเคยกับการใช้ฟังก์ชัน BAHTTEXT เพราะฟังก์ชันตัวนี้ช่วยในการ แปลงจำนวนเงินเป็นข้อความภาษาไทย เช่น =BAHTTEXT(125.50) จะได้ผลลัพธ์ “หนึ่งร้อยยี่สิบห้าบาทห้าสิบสตางค์” แต่ถ้าตัวเลขจำนวนเต็ม ไม่มีเศษทศนิยม เช่น =BAHTTEXT(125) จะได้ผลลัพธ์ “หนึ่งร้อยยี่สิบห้าบาทถ้วน” เป็นต้น

ถ้าต้องการให้มีสัญลักษณ์หรือข้อความอื่นๆ กำกับด้วย
เช่น “===หนึ่งร้อยยี่สิบห้าบาทห้าสิบสตางค์===”
ก็พิมพ์สูตรดังนี้ 
="==="&BAHTTEXT(125.50)&"==="
หรือ "รวมเป็นเงินทั้งสิ้น : หนึ่งร้อยยี่สิบห้าบาทห้าสิบสตางค์"
ก็พิมพ์สูตรดังนี้ 
="รวมเป็นเงินทั้งสิ้น : "&BAHTTEXT(125.50)

และถ้าต้องการให้มีวงเล็บครอบข้อความ
เช่น “(หนึ่งร้อยยี่สิบห้าบาทห้าสิบสตางค์)”
ก็พิมพ์สูตรดังนี้ 
="("&BAHTTEXT(125.50)&")"

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


แนะนำ Add-Ins ThaiFunction

ก่อนอื่นผมขอแนะนำให้คุณไปดาวน์โหลดไฟล์ Add-Ins ที่ชื่อว่า ThaiFunction.xla จากเว็บไซต์http://sites.google.com/site/excel4hr/download/ โหลดมาไว้ในเครื่องของคุณก่อน แต่เดิม Add-Ins ชุดนี้ ผมรวบรวมเอาไว้ใช้งานเอง แต่หลังจากที่ได้แนะนำให้เพื่อนๆ ใช้ ก็มีการบอกต่อกัน และก็เริ่มแพร่หลาย แต่ฟังก์ชันต่างๆ ที่รวบรวมอยู่ใน Add-Ins ชุดนี้ ส่วนหนึ่งผมรวบรวมมาจากเว็บ ExcelExpertTraining บางส่วนก็เขียนขึ้นเอง (แบบว่าหัดทำครับ) ซึ่งก็ต้องขอขอบคุณผู้พัฒนาโค๊ดต้นฉบับทุกท่านมา ณ ที่นี้ด้วย


การติดตั้ง Add-Ins ThaiFunction

เมื่อดาวน์โหลดไฟล์มาแล้ว ให้แตกไฟล์ .rar และนำไฟล์ ThaiFunction.xla ไปวางไว้ในโฟลเดอร์ใด โฟลเดอร์หนึ่ง แต่อยากจะแนะนำให้วางไว้ในโฟลเดอร์ที่คาดว่าจะมีอยู่ในทุกๆ เครื่อง เช่น C:\Windows หรือ C:\Program Files เป็นต้น (เพื่อว่า ถ้ามีการนำไฟล์ Excel ที่มีการใช้ฟังก์ชันใน Add-Ins ชุดนี้ ไปเปิดในเครื่องอื่น ไฟล์งานนั้นจะสามารถเปิดได้เป็นปกติ)


วิธีการติดตั้งกรณีใช้ Excel 2003 ให้ทำตามขั้นตอนดังนี้

1. เปิดโปรแกรม Excel ขึ้นมา
2. คลิกที่เมนู Tools > Add-Ins...
3. เมื่อหน้าต่าง Add-Ins ปรากฎขึ้นมา (อาจมีรายการไม่เหมือนกับในรูป) ให้คลิกที่ปุ่ม Browse...
4. เลือกไปยังโฟลเดอร์ที่อยู่ของไฟล์ ThaiFunction.xla เช่น C:\Windows
5. คลิกที่ชื่อไฟล์ ThaiFunction.xla แล้วคลิก OK เพื่อกลับมายังหน้า Add-Ins
6. จะเห็นว่ามีรายการ รวมฟังก์ชันแบบไทย เพิ่มขึ้นมาอีกตัวหนึ่ง ให้คลิก OK อีกครั้งหนึ่ง

สำหรับ 2007 ให้คลิกที่โลโก้ MS Office มุมซ้ายบน แล้วคลิกปุ่ม Excel Options (ซึ่งจะอยู่ล่างๆ) แล้วเข้าไปที่ Add-Ins

ส่วน Excel 2010 ให้คลิกที่เมนู File, เลือก Options แล้วเลือก Add-Ins และคลิก Go




ทดลองใช้งาน

1. ฟังก์ชัน BahtOnly

ฟังก์ชันตัวแรกที่จะแนะนำได้แก่ BahtOnly มีวิธีการใช้เช่นเดียวกับ BahtText เช่น =BahtOnly(125.50) หรือถ้าใส่ตัวเลข 125.50 ไว้ที่ A1 ก็ลองพิมพ์สูตร =BahtOnly(A1) ก็จะได้ผลลัพธ์เป็น One Hundred Twenty Five Baht and Fifty Satang



2. ฟังก์ชัน Money

ในกรณีที่เราต้องการแปลงตัวเลขเป็นภาษาอังกฤษ แต่เป็นหน่วยอื่นที่ไม่ใช่ บาท-สตางค์ ก็ขอแนะนำฟังก์ชัน Money ฟังก์ชันนี้ใช้งานคล้ายกับ BahtOnly แต่เราสามารถกำหนดหน่วยเองได้ โดยใส่หน่วยหลัก และหน่วยรองลงไปด้วย เช่น=Money(125.50,"Dollar","Cent") หรือ =Money(A1,"Dollar","Cent") จะได้ผลลัพธ์เท่ากับ One Hundred Twenty Five Dollars and Fifty Cents

3. ฟังก์ชัน ReadText

กรณีที่ต้องการแปลงตัวเลขเป็นภาษาไทย แต่อยากเปลี่ยนหน่วยเป็นอย่างอื่น ที่ไม่ใช่ บาท-สตางค์ ในลักษณะเดียวกับการใช้ Money สาเหตุที่สร้างฟังก์ชันตัวนี้ขึ้นมา ก็เพื่อใช้อ่านตัวเลขใดๆ ให้เป็นคำอ่านข้อความภาษาไทย โดยไม่จำเป็นต้องมีหน่วย (ถ้าใช้ BahtText จะได้หน่วยบาทถ้วน หรือบาท...สตางค์มาด้วย ต้องมาเขียนสูตรเพื่อตัดหน่วยอีก) ผมจึงศึกษา Code ของฟังก์ชัน Money และปรับเป็นฟังก์ชัน ReadText ซึ่งจะกำหนดหน่วยหรือไม่ก็ได้ (ถ้าต้องการ) ถ้าไม่กำหนดหน่วย ก็จะเป็นการอ่านตัวเลขแบบทั่วไป

ตัวอย่างเช่น =ReadText(125.25,"ดอลล่าร์","เซ็นต์",1) จะได้ผลลัพธ์เป็น หนึ่งร้อยยี่สิบห้าดอลล่าร์ยี่สิบห้าเซ็นต์ แต่ถ้าพิมพ์สูตร=ReadText(125.25) จะได้ผลลัพธ์เป็น หนึ่งร้อยยี่สิบห้าจุดสองห้า


หากติดตั้ง Add-Ins แล้วใช้งานไม่ได้

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

ถ้าทุกอย่างถูกต้องครบถ้วน ให้ลองแก้ไขโดยการ คลิกที่เมนู Tools -> Add-Ins... เมื่อหน้าต่าง Add-Ins ปรากฏขึ้น ให้เอาเครื่องหมายถูกหน้ารายการ รวมฟังก์ชันแบบไทย ออกก่อน แล้วคลิกปุ่ม OK

จากนั้นให้คลิกที่เมนู Tools -> Add-Ins... อีกครั้ง ติ๊กเลือกให้มีเครื่องหมายถูกหน้ารายการ รวมฟังก์ชันแบบไทย อีกครั้งหนึ่ง แล้วคลิกปุ่ม OK จากนั้นทดลองเรียกใช้ฟังก์ชันอีกครั้งหนึ่ง

Read More

การทำ Mail Merge ด้วย Word2003 โดยดึงข้อมูลจาก Excel

Mail Merge คืออะไร

Mail Merge หรือจดหมายเวียน หมายถึง ลักษณะของเอกสารชนิดหนึ่ง ที่มีจำนวนมากๆ โดยที่เนื้อหาใจความส่วนใหญ่ในเอกสารนั้น เหมือนกัน แต่มีเพียงบางตำแหน่ง ที่มีข้อความต่างกัน เช่น ชื่อ-ที่อยู่ หรือตำแหน่งหน้าที่ ของผู้รับ

คุณลักษณะการทำ Mail Merge หรือจดหมายเวียนนี้ มีอยู่ในโปรแกรม MS Word มานานแล้ว ซึ่งช่วยให้ผู้ใช้ สามารถสร้างจดหมายเวียน หนังสือเชิญ หรือพิมพ์หน้าซองจดหมาย ฯลฯ โดยดึงข้อมูลจากตารางฐานข้อมูล ที่สร้างด้วย MS Word หรือ MS Excel มาพิมพ์โดยอัตโนมัติ


ขั้นตอนการทำ Mail Merge

เนื่องจากในปัจจุบัน มีผู้ใช้งาน MS Word 2003 เป็นจำนวนมาก ดังนั้น ผมขออธิบายขั้นตอนการทำ Mail Merge โดยอ้างอิงโปรแกรม MS Word 2003 เป็นหลัก ดังขั้นตอนต่อไปนี้

1. เตรียมตารางฐานข้อมูล ซึ่งไฟล์ฐานข้อมูลนี้ อาจจะสร้างด้วย Excel หรือสร้างด้วย Word ก็ได้ โดยให้มีข้อมูลที่ต้องการจะนำมา Merge ลงในเอกสารหลัก เช่น ชื่อผู้รับการประกาศรางวัล หรือชื่อ-ที่อยู่ผู้รับ ฯลฯ เป็นต้น

รูปที่ 1 ตัวอย่างรายชื่อที่จะนำไป Merge เป็นใบประกาศเกียรติบัตร

2. สร้างไฟล์เอกสารหลัก หรือเปิดไฟล์เอกสาร ที่ทำไว้ด้วยโปรแกรม MS Word ขึ้นมา เช่น ไฟล์ใบประกาศเกียรติบัตร, ไฟล์หน้าซองจดหมาย ฯลฯ เป็นต้น

รูปที่ 2 ตัวอย่างใบประกาศเกียรติบัตร

3. เข้าเมนู Tools - Letter and Mailings - Mail Merge... ดังรูป

รูปที่ 3 แสดงการเข้าเมนูเพื่อสร้างจดหมายเวียน

4. Mail Merge Wizard Step1 จะปรากฎขึ้นทางด้านขวาของโปรแกรม ดังรูป เป็นการถามลักษณะของงานที่จะทำ Mail Merge ว่าจะเป็นแบบไหน เช่น จดหมาย, อีเมล์, ซองจดหมาย หรือป้ายสติ๊กเกอร์ เป็นต้น ในที่นี้เราจะเลือกลักษณะเป็นจดหมาย (Letters) ซึ่งถูกเลือกอยู่แล้ว ก็ไม่ต้องทำอะไร ให้คลิก Next: Starting document ที่อยู่ด้านล่าง เพื่อไปขั้นตอนถัดไป


รูปที่ 4 ตัวช่วยในการสร้าง Mail Merge ขั้นตอนที่ 1


5. Mail Merge Wizard Step2 ในขั้นตอนนี้เป็นการถามว่า จะเลือกใช้เอกสารหลักอันไหน ซึ่งเราต้องการใช้ไฟล์ปัจจุบันที่กำลังเปิดอยู่ (ก็ไม่ต้องเลือกอะไรเช่นกัน) ให้คลิกที่ Next: Select recipient ซึ่งอยู่ด้านล่าง เพื่อไปยังขั้นตอนถัดไป


รูปที่ 5 ตัวช่วยในการสร้าง Mail Merge ขั้นตอนที่ 2


6. Mail Merge Wizard Step3 ขั้นตอนการเลือกรายชื่อที่จะเอามาเติม โดยคลิก Browse... แล้วเลือกไปยังไฟล์ข้อมูลที่เตรียมไว้ (ในข้อ1)


รูปที่ 6 ตัวช่วยในการสร้าง Mail Merge ขั้นตอนที่ 3 และการเลือกแหล่งข้อมูล


7. เลือกชื่อไฟล์ข้อมูล ซึ่งถ้าเป็นไฟล์ Excel และในไฟล์นั้นมีข้อมูลอยู่หลายชีท เราจะต้องเลือกชีทที่ต้องการ แล้วคลิก OK


รูปที่ 7 เลือกแหล่งข้อมูล


8. ตรวจสอบความถูกต้องของข้อมูลอีกครั้ง แล้วคลิก OK

รูปที่ 8


9. กลับมาที่หน้าจอเดิม ให้คลิก Next: Write your letter ที่อยู่ด้านล่าง ดังรูป


รูปที่ 9


10. Mail Merge Wizard Step4 เป็นขั้นตอนการเติม (Merge) ข้อมูล โปรแกรมจะมีข้อความสำเร็จรูป เตรียมไว้ให้เลือกใช้ (ถ้าต้องการ) เช่น คำขึ้นต้น คำลงท้าย ฯลฯ เป็นต้น แต่ในที่นี้ให้คลิกที่คำว่า More items...


รูปที่ 10


11. เลือกฟิลด์ข้อมูลที่จะนำมาเติม ซึ่งในที่นี้มีอยู่ฟิลด์เดียว ก็คือ "Name" ดังรูป โดยคลิกที่คำว่า Name แล้วคลิกปุ่ม Insert (ถ้ามีหลายฟิลด์ ก็ทำการ Insert ไปทีละฟิลด์) เสร็จแล้วคลิกปุ่ม Close


รูปที่ 11


12. ฟิลด์ข้อมูลที่เลือกเข้ามา จะมีลักษณะดังรูป เราสามารถที่จะคัดลอก หรือย้ายฟิลด์ข้อมูลดังกล่าว ไปยังตำแหน่งอื่นที่ต้องการได้ เสร็จแล้วคลิก Next: Preview your letter ที่อยู่ด้านล่าง

รูปที่ 12 ลักษณะของชื่อฟิลด์เมื่อถูกนำมา Merge ลงในเอกสารหลักแล้ว
13. Mail Merge Wizard Step5 ในขั้นตอนนี้ สามารถคลิกที่ปุ่ม Navigator เดินหน้า - ถอยหลัง เพื่อดูความเปลี่ยนแปลงของข้อมูล เสร็จแล้วคลิก Next: Complete the merge ที่อยู่ด้านล่าง เพื่อไปยังขั้นตอนต่อไป


รูปที่ 13


14. Mail Merge Wizard Step6 ในขั้นตอนสุดท้าย ก็เป็นการสั่งพิมพ์ ว่าต้องการพิมพ์ทั้งหมด
หรือพิมพ์หน้าปัจจุบัน หรือจะพิมพ์จากหน้าไหนถึงหน้าไหน ดังรูป เราก็ใส่กระดาษที่จะพิมพ์ใบประกาศลงไปในเครื่อง แล้วสั่งพิมพ์ทั้งหมด ก็จะได้ใบประกาศที่เป็นชื่อแต่ละคนออกมา จนครบทุกรายชื่อในฐานข้อมูล


รูปที่ 14


15. หลังขั้นตอนที่ 12 ถ้าบนแถบไอคอนบาร์ มีแถบ Mail Merge Icon แสดงอยู่ ก็สามารถที่จะเลื่อนดูผลลัพธ์ได้ เช่นเดียวกับในข้อ 13 และยังสามารถคลิกไอคอน Merge to Printer ได้เช่นเดียวกับข้อ 14 แต่ถ้าแถบ Mail Merge Icon ไม่แสดง ให้คลิกขวาบริเวณไอคอนใดๆ แล้วเลือกไปที่รายการ Mail Merge ดังรูป

รูปที่ 15 แสดงแถบไอคอน Mail Merge

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


รูปที่ 16 ตัวอย่างรายชื่อที่จะพิมพ์หน้าซอง


ส่วนขนาดหน้าซอง ให้ลองหาดูจากรายการในเมนูการตั้งค่าหน้ากระดาษ (Page Setup...) แต่ถ้าไม่มีขนาดที่ตรงกับขนาดซองที่เราต้องการใช้ ก็ให้กำหนดขนาดเป็นแบบกำหนดเอง (Custom) แล้วใส่ตัวเลขขนาดความสูงความกว้างที่ต้องการ


รูปที่ 17 ตัวอย่างแม่แบบหน้าซองจดหมายหรือซอง สคส.


ทดลองฝึกปฏิบัติไปทีละขั้นตอนนะครับ

Read More