สร้างแบบฟอร์มสลับภาษาอัตโนมัติ (ไทย-อังกฤษ)

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

ฟังก์ชัน IF ไม่ได้มีความสามารถเพียงแค่ ตรวจสอบว่า ถ้า A มากกว่า B ให้เอา A ลบ B แต่ถ้าไม่ใช่ ให้เอา B ลบ A เท่านั้น แต่ยังนำไปประยุกต์ใช้งานให้ตื่นตาตระการใจได้อีกมากมาย


ภาพที่ 1 ตัวอย่างแบบฟอร์มที่เปลี่ยนสลับข้อความได้ 2 ภาษา


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

เพื่อให้เกิดความเข้าใจยิ่งขึ้น ขอให้ลองสร้างไฟล์ตัวอย่าง คล้ายๆ กับในรูป เพื่อจะได้ทดลองสร้างสูตร และฝึกปฏิบัติไปพร้อมกัน

สมมติว่าเรากำลังจะสร้างแบบฟอร์มใบสั่งซื้อ ซึ่งจะสามารถสลับเปลี่ยนข้อความในแบบฟอร์ม ให้เป็นภาษาไทยหรืออังกฤษได้โดยอัตโนมัติ เพียงแค่เปลี่ยนรหัสที่เซลล์ L1 ซึ่งถ้าเลือกเป็น "Th" แบบฟอร์มก็จะแสดงเป็นภาษาไทย แต่ถ้าเป็น "En" ข้อความในแบบฟอร์มก็จะเปลี่ยนเป็นภาษาอังกฤษทันที



ทำรายการเลือก เพื่อสะดวกในการเปลี่ยนภาษา

ผมกำหนดให้เซลล์ L1 ต้องใส่รหัส "Th" หรือ "En" อย่างใดอย่างหนึ่ง ดังนั้น เพื่อความสะดวกในการกรอกรหัสในเซลล์ L1 ผมจะใช้วิธีให้คลิก Drop down list เพื่อเลือกรหัสระหว่าง "Th" หรือ "En" ดังขั้นตอนต่อไปนี้

1. คลิกที่เซลล์ L1 จากนั้นเข้าไปที่เมนู Data > Validation...
2. ในแท็ป Setting ให้เลือก List ในช่อง Allow:
3. ในช่อง Source ให้พิมพ์ Th,En แล้วกดปุ่ม OK

เสร็จแล้วคุณจะเห็นว่า เซลล์ L1 สามารถคลิกรายการเลือก Th หรือ En ได้แล้ว

หมายเหตุ : ลองอ่านเพิ่มเติม เรื่อง "การสร้างรายการเลือก (List) เพื่อช่วยเติมข้อมูล"



ใช้ฟังก์ชัน IF เพื่อเปลี่ยนข้อความในแบบฟอร์ม

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

ตัวอย่างเช่น กำหนดเงื่อนไขใน K25 ถ้าเป็นภาษาไทย ให้แสดงคำว่า "จำนวนเงิน" แต่ถ้าเป็นภาษาอังกฤษ ให้แสดงคำว่า"Amount" ดังนั้น เราจะพิมพ์สูตรใน K25 ดังนี้
=IF($L$1="En", "Amount", "จำนวนเงิน")

เสร็จแล้ว ให้ลองคลิกเปลี่ยนรหัสภาษาที่เซลล์ L1 เพื่อดูการเปลี่ยนสลับข้อความ ไทย-อังกฤษ ในเซลล์ K25 ซึ่งถ้าได้ผลจนเป็นที่น่าพอใจแล้ว เราก็จะใช้สูตรลักษณะเดียวกันนี้ กับเซลล์อื่นๆ ที่เหลือ เช่น ชื่อ-ที่อยู่บริษัท, วันที่, เลขที่, ... ฯลฯ เป็นต้น ทดลองทำดูนะครับ เพื่อทดสอบความเข้าใจ



ใช้ตารางข้อความมาตรฐานเพื่อเปลี่ยนสลับภาษา

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


ภาพที่ 2 ตารางชื่อข้อความภาษาไทยและอังกฤษ


แต่ถ้าเราใช้วิธีสร้าง ตารางกำหนดชื่อมาตรฐาน ภาษาไทย - ภาษาอังกฤษ แยกไว้ต่างหาก (ดังภาพที่ 2) เวลาต้องการจะแก้ไขข้อความใดๆ ก็ให้มาแก้ที่ตารางนี้ โดยไม่ต้องไปแก้ที่สูตร (เราอาจจะทำการล๊อกการแก้ไขสูตรไว้ด้วย)

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

ผมขออนุญาตเปลี่ยนชื่อชีทที่เป็นแบบฟอร์ม ให้ชื่อว่า Report2 โดยในชีท Report2 เราจะทำการแก้ไขสูตร ที่แสดงข้อความในแต่ละตำแหน่งในแบบฟอร์มเสียใหม่ เช่น ที่ K25 ให้พิมพ์สูตรใหม่ ดังนี้
=IF($L$1="En", List!B17, List!A17)

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



ใช้ฟังก์ชัน HLOOKUP เพื่อเปลี่ยนข้อความในแบบฟอร์ม

ในกรณีที่เราสร้างตารางข้อความดังในภาพที่ 2 เรายังสามารถใช้ฟังก์ชัน HLOOKUP เพื่อเปลี่ยนการแสดงข้อความ แทนการใช้ฟังก์ชัน IF ได้อีกด้วย

ฟังก์ชัน HLOOKUP จะทำการตรวจสอบค่าในเซลล์ L1 ว่ามีค่าเป็น Th หรือ En จากนั้นก็จะไปเทียบค่ากับตารางข้อมูล ช่วง A3:B24 ในชีท List แล้วจะนำข้อมูลในลำดับที่ที่กำหนด มาแสดงผล ซึ่งจากข้อมูลในภาพที่ 2 ขอบเขตของข้อมูลคือ $A$3:$B$24 ซึ่งข้อมูลลำดับที่ 1 จะเริ่มนับจากบรรทัดที่ 3 ดังนั้น ข้อความที่เป็น จำนวนเงิน หรือ Amount จะเป็นลำดับที่ 15 เป็นต้น


เพื่อเก็บสูตรเดิมไว้ศึกษา ขอให้เราทำการคัดลอกแบบฟอร์ม Report2 ไปเป็นชีทใหม่ แล้วตั้งชื่อชีทว่า Report3

จากนั้น ที่ตำแหน่งเดิม คือ K25 ของชีท Report3 ให้แก้ไขสูตรเป็น

=HLOOKUP($L$1, List!$A$3:$B$24, 15, 0)

แต่..จาก ที่ผมกล่าวไว้ในตอนต้นแล้วว่า ถ้ารหัสในเซลล์ L1 เป็น En จะแสดงแบบฟอร์มเป็นภาษาอังกฤษ แต่ถ้าใน L1 เป็น Th (หรือไม่ใส่ข้อความใดๆ) จะแสดงแบบฟอร์มเป็นภาษาไทย

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

=HLOOKUP(IF($L$1<>"", L1, "Th"), List!$A$3:$B$24, 15, 0)

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


ภาพที่ 3 แบบฟอร์มเมื่อเปลี่ยนข้อความเป็นภาษาอังกฤษ



ภาพที่ 4 แบบฟอร์มเมื่อเปลี่ยนข้อความเป็นภาษาไทย


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

ในตอนต่อไป ผมจะแนะนำวิธีการสร้างฐานข้อมูล รายชื่อผู้ขาย และรายชื่อสินค้า เพื่อนำมากรอกลงในแบบฟอร์มนี้ โดยอัตโนมัติ ดังนั้น ไฟล์ workshop นี้ ยังจะต้องใช้อีกในครั้งต่อไปนะครับ

โปรดติดตามตอนต่อไป สวัสดีครับ

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

ก่อนที่จะฝึกปฏิบัติตอนที่ 2 นี้ ผมอยากจะแน่ใจว่า ไฟล์ workshop คราวที่แล้วของคุณยังอยู่ และได้ใส่สูตรครบถ้วนทุกเซลล์แล้ว (หากใครยังทำไม่เสร็จ ก็ขอให้กลับไปทำให้เสร็จก่อน ไม่ต้องใจร้อนครับ ค่อยๆ ทำ จะได้จำได้) สำหรับท่านที่ทำเสร็จแล้ว ก็เปิดไฟล์ดังกล่าวขึ้นมาเลยครับ

ในตอนที่แล้ว เราสร้างเฉพาะแบบฟอร์มเปล่าๆ แต่ในคราวนี้ เราจะลิงค์ข้อมูลมาใส่ในแบบฟอร์มด้วย เนื่องจากแบบฟอร์มนี้คือ "ใบสั่งซื้อ" ซึ่งเราจะต้องใส่ข้อมูลบริษัทผู้ขาย เบอร์โทร เบอร์แฟกซ์ และชื่อผู้ติดต่อ ลงในแบบฟอร์ม เราจำเป็นต้องมีฐานข้อมูลรายชื่อบริษัทผู้ขายด้วย

ผมออกแบบฐานข้อมูลผู้ขาย (Vendor) โดยสร้างไว้ในไฟล์เดียวกันกับไฟล์ตัวอย่างเดิมที่เราทำไว้ เพียงแต่แยกกันคนละชีทงาน สำหรับรายละเอียดในฐานข้อมูลนั้น ก็สุดแล้วแต่ความต้องการครับ แต่เพื่อให้สอดคล้องกับแบบฟอร์มใบสั่งซื้อ อย่างน้อยก็ต้องมี รหัสผู้ขาย, ชื่อบริษัทผู้ขาย, ชื่อผู้ติดต่อ, เบอร์โทรศัพท์ และเบอร์แฟกซ์ (จะใส่ที่อยู่ด้วยก็ไม่ว่ากันครับ)

ในส่วนชื่อบริษัทผู้ขาย และชื่อผู้ติดต่อ ผมออกแบบให้มีการเก็บข้อมูลเป็น 2 ภาษา แยกกันคนละคอลัมน์ มีลักษณะดังรูปที่ 1 


รูปที่ 1 แสดงตัวอย่างทะเบียนรายชื่อผู้ขายทั้งสองภาษา


ผมเตรียมข้อมูลไว้ให้ทั้งหมด 21 รายการ ดังนั้น ช่วงฐานข้อมูลรายชื่อผู้ขาย ก็คือช่วง $A$4:$G$24 หรือถ้าจะอ้างอิงช่วงเต็มๆ ก็คือvendor!$A$4:$G$24

ก่อนที่จะไปเริ่มเขียนสูตร เพื่อดึงข้อมูลเหล่านี้ไปกรอกลงในแบบฟอร์ม ให้เราพิจารณา ชื่อผู้ขาย และชื่อผู้ติดต่อ

โดยชื่อผู้ขาย ที่เป็นภาษาอังกฤษ จะเป็นคอลัมน์ B หรือคอลัมน์ที่ 2 ของช่วงฐานข้อมูล ส่วนชื่อภาษาไทย จะเป็นคอลัมน์ C หรือคอลัมน์ที่ 3

สำหรับชื่อผู้ติดต่อ ที่เป็นภาษาอังกฤษ จะเป็นคอลัมน์ D หรือคอลัมน์ที่ 4 ของช่วงฐานข้อมูล ส่วนชื่อที่เป็นภาษาไทย จะเป็นคอลัมน์ E หรือคอลัมน์ที่ 5 นั่นเอง

สำหรับเบอร์โทรศัพท์ และเบอร์แฟกซ์ (คงไม่จำเป็นต้องเก็บเป็นเลขไทยหรือเลขอารบิค) จะอยู่ในคอลัมน์ F หรือคอลัมน์ที่ 6 และคอลัมน์ G หรือคอลัมน์ที่ 7 ตามลำดับ ขอให้เราจำตำแหน่งคอลัมน์ของข้อมูลเหล่านี้ให้แม่นๆ เพราะเราจะต้องนำไปเขียนสูตรด้วย

ในชีท Report ให้ใส่รหัสผู้ขายสักรายหนึ่งในเซลล์ F15 (ดูรายการรหัสจากคอลัมน์ A ในชีท vendor) สมมติว่าผมขอเลือกเป็น SMP ก็แล้วกัน

ผมจะใช้ฟังก์ชัน VLOOKUP เพื่อดึงข้อมูลผู้ขายรหัส SMP มาแสดงในแต่ละเซลล์ที่เกี่ยวข้อง อันได้แก่ ชื่อผู้ขาย ชื่อผู้ติดต่อ เบอร์โทร และเบอร์แฟกซ์ (อ่านเพิ่มเติมเรื่อง การใช้ VLOOKUP เพื่อแสดงข้อมูลจากฐานข้อมูล ตอนที่1 และ ตอนที่2 ที่เคยเขียนไปแล้วก่อนหน้านี้)

เนื่องจาก เบอร์โทร เบอร์แฟกซ์ เป็นรายการที่ใช้ได้กับแบบฟอร์มทั้งที่เป็นภาษาไทยและภาษาอังกฤษ ดังนั้น การเขียนสูตรจึงไม่ยุ่งยาก ดังนี้
แสดงเบอร์โทรศัพท์ที่ F19 ให้พิมพ์สูตรดังนี้
=VLOOKUP($F$15,Vendor!$A$4:$G$24,6,0)

และแสดงเบอร์แฟกซ์ที่ F21 ให้พิมพ์สูตรดังนี้
=VLOOKUP($F$15,Vendor!$A$4:$G$24,7,0)


สำหรับเซลล์ F17 (ชื่อผู้ขาย) และเซลล์ F23 (ชื่อผู้ติดต่อ) นั้น โดยหลักการแล้วก็จะใช้สูตรแบบเดียวกัน แต่จะต้องเลือกว่า ต้องการแสดงภาษาอังกฤษ หรือภาษาไทย เช่น ถ้าต้องการแสดงชื่อภาษาอังกฤษ สูตรจะเป็น =VLOOKUP($F$15,Vendor!$A$4:$G$24,2,0) หรือถ้าต้องการแสดงชื่อภาษาไทย สูตรก็จะเป็น
=VLOOKUP($F$15,Vendor!$A$4:$G$24,3,0)

ซึ่งจะเห็นว่า เราเขียนสูตรเหมือนกัน ไม่ว่าจะแสดงเป็นอังกฤษหรือไทย ต่างกันแค่เลข 2 กับเลข 3 ซึ่งเป็นเลขชี้คอลัมน์ข้อมูลเท่านั้นเอง

เราจะเอาสูตรทั้งสองแบบนี้ มาเขียนรวมเป็นสูตรเดียว โดยนำฟังก์ชัน IF เข้ามาตรวจสอบสถานะที่ L1 ว่าเป็น En หรือ Th ดังนั้น สูตรแสดงชื่อผู้ขายในเซลล์ F17 จะเป็นดังนี้
=VLOOKUP($F$15,Vendor!$A$4:$G$24,IF($L$1="En",2,3),0)

ส่วนสูตรแสดงชื่อผู้ติดต่อในเซลล์ F23 จะเป็นดังนี้
=VLOOKUP($F$15,Vendor!$A$4:$G$24,IF($L$1="En",4,5),0)


รูปที่ 2 แสดงตัวอย่างการดึงข้อมูลรายชื่อผู้ขายมากรอกในแบบฟอร์ม


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


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