ผสานคุณสมบัติของ Index Function กับ Match Function เพื่อแทนคำสั่ง vlookup Function

บทความนี้ จะอธิบายข้อดีของ Index และ Match ที่แก้ไข จุดด้อยของ Vlookup ได้ หากใครยังไม่รู้จักการทำงานพื้นฐานของฟังก์ชั่น Vlookup สามารถกลับไปอ่าน "การค้นหาข้อมูลด้วย vLookUp จาก Excel" และ "HLookup ช่วยค้นหาข้อมูลได้ต่างกับ VLookup อย่างไร" หรือต้องการทบทวนฟังก์ชั่น Match ก็สามารถอ่าน "ค้นหาเลขคอลัมน์ หรือเลขแถว ของสิ่งที่ค้นหาด้วย Match Function" เพื่อทบทวนได้ค่ะ

INDEX Function 
        หมายถึง การค้นหาค่า จากตารางอ้างอิง ตามแถวหรือคอลัมน์ที่กำหนด หรือ ใช้แทน VLOOKUP ได้ 

ไวยากรณ์ =INDEX(array, row_num, [column_num])

หลักการใช้ฟังก์ชั่น INDEX

• Array คือ ช่วงของเซลล์ที่ต้องการค้นหา
• row_num คือ เลขแถวใน Array 
• [column_num] คือ เลขคอลัมน์ใน Array 

เมื่อนำ INDEX มาใช้ร่วมกับ MATCH จะทำให้สามารถค้นหาข้อมูลได้ โดยการหาตำแหน่งด้วย MATCH และ นำมาแสดงด้วย INDEX ซึ่งจะเทียบได้กับทั้ง 2 ฟังก์ชั่นนี้ ทำหน้าที่แทน VLOOKUP นั้นเอง

ตัวอย่างการใช้ฟังก์ชั่น INDEX
        จากบทความนี้ เราจะใช้ตัวอย่างเดียวกับฟังก์ชั่น VLOOKUP และ MATCH เพื่อให้คุณ ๆ เห็นความแตกต่างระหว่างทั้ง 3 ฟังก์ชั่น 

รูปที่ 1 ตารางที่ใช้แสดงตัวอย่างการนำฟังก์ชั่น INDEX และ MATCH มาทำงาน

จากรูปที่ 1 ถ้าเราใช้ฟังก์ชั่น INDEX ในการค้นหา จะคืนค่าภายในเซลล์นั้นออกมา เช่น ต้องการค้นแถวที่ 10 และคอลัมน์ที่ 2 ก็ต้องเขียนว่า =INDEX($A$1:$D$14,10,2) ผลลัพธ์ที่ได้คือ "Charming" นั้นเอง

ข้อควรระวัง !!! 
ถ้าคุณเขียน =INDEX($A$2:$D$14,10,2) จะเห็นว่าช่วงของเซลล์ที่ต้องการค้นหาเริ่มต้นไม่เหมือนกัน (A2:D14) ทำให้ผลลัพธ์ที่ได้ผิดไปจากเดิม คือ "Cupid" 

หลักการใช้ฟังก์ชั่น Index นี้มันดูง่าย ๆ ไม่ซับซ้อนอะไรเลยถูกไหมคะ 😉😉😊😊

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

คุณสามารถกลับไปทบทวนหลักการของ Match Function ได้ที่บทความ "ค้นหาเลขคอลัมน์ หรือเลขแถว ของสิ่งที่ค้นหาด้วย Match Function" 

ตัวอย่างการใช้ฟังก์ชั่น INDEX ร่วมกับ MATCH แบบที่ 1
        ต้องการค้นหารายได้ (Income) ของร้าน Cupid โดยให้คุณเขียนสูตรจากทั้ง 2 ฟังก์ชั่น คือ Index และ Match ร่วมกัน เพื่อค้นหาค่า (Value) แทนฟังก์ชั่น VLOOKUP ซึ่งสามารถเขียนได้ ดังนี้  

=INDEX($A$2:$D$14,MATCH("Cupid",$B$2:$B$14,0),MATCH("Income",$A$1:$D$1,0))

จากสูตรด้านบนนี้ ถ้าคุณสังเกตดีๆ เรายังใช้ฟังก์ชั่น Index อยู่นะค่ะ แต่ส่วนของ Argument ที่ให้ระบุเลขแถว และเลขคอลัมน์ ของ Index เราได้ใช้ฟังก์ชั่น Match เข้ามาช่วยนั้นเอง 

ซึ่ง MATCH("Cupid",$B$2:$B$14,0) ใช้ในการค้นหาเลขลำดับของข้อมูล โดยเมื่ออ่านความหมายของสูตรนี้ คือ ค้นหาคำว่า Cupid จากลำดับ B2:B14 (แถว) และวิธีหา Cupid ต้องคำตรงกัน ดังนั้น ผลลัพธ์ คือ แถวลำดับที่10 (จุดสังเกต ใครรู้สึกว่าต้องเป็น ลำดับที่ 11 ไหม ซึ่งจะไม่ถูกต้อง เพราะ สูตรที่เขียนกำหนดช่วงเป็น B2:B14 เมื่อนับแต่ละแถวลงมา Cupid จะอยู่ลำดับที่ 10 พอดีค่ะ)

ปล. ให้คุณนึกว่าถ้ามีข้อมูลใน Excel จำนวนมากเป็นพัน ๆ แถว/คอลัมน์ เราคงไม่รู้ว่า สิ่งที่เราค้นหามันอยู่ในเลขแถวหรือเลขคอลัมน์อะไรแน่นอน ดังนั้น ฟังก์ชั่น Match จึงกลายมาเป็นฮีโรของ Index ทันที 

ตัวอย่างการใช้ฟังก์ชั่น Index ร่วมกับ Match แบบที่ 2 
        เราจะใช้โจทย์เดิมตามแบบที่ 1 แต่คุณ ๆ สามารถเขียนได้ ดังนี้

=INDEX($D$2:$D$14,MATCH("Cupid",$B$2:$B$14,0))

ในตัวอย่างแบบที่ 2 นี้ เราใช้ Argument เพียงระบุเลขแถวอย่างเดียวได้ และ ช่วงของเซลล์ที่ต้องการค้นหา ให้ระบุช่วงของสิ่งที่ต้องการให้แสดงผลลัพธ์ นั้นก็คือ คอลัมน์รายได้ (Income) นั้นเอง 

ปล. ทั้งตัวอย่างแบบที่ 1-2 จะได้ผลลัพธ์เหมือนกัน คือ 150,000 บาท เหมือนกันเลย

Read More

ค้นหาเลขคอลัมน์ หรือเลขแถว ของสิ่งที่ค้นหาด้วย Match Function

บทความนี้ จะอธิบายการใช้ฟังก์ชั่น Match และแสดงตัวอย่างการประยุกต์ใช้ระหว่าง Match Function ร่วมกับ Vlookup Function

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

ไวยาการณ์ =MATCH(lookup_value, lookup_array, [match_type])

หลักการใช้ฟังก์ชั่น Match

• lookup_value คือ ค่าที่ต้องการหา
• lookup_array คือ ลำดับคำที่ค้นหา ว่าอยู่ในแถวหรือคอลัมน์อะไร (เลือกได้แค่แถว หรือคอลัมน์ เท่านั้น จะเลือกทั้งคู่ไม่ได้)
• [match_type] คือ การเลือกประเภทการค้นหา ซึ่งมี 3 ประเภท คือ
• 0 : เป็นการหาค่าที่ต้องเหมือนกันทั้งหมด (เหมือนกับ VLOOKUP แบบ Exact Match) แต่ถ้าเจอเหมือนกันมากกว่า 1 ตัว โปรแกรมจะแสดงตัวแรก
• 1 หรือไม่ต้องใส่ค่า : ควรทำการเรียงข้อมูล lookup_array จากน้อยไปมากก่อน ซึ่งหลักการทำงาน จะหาค่าที่มากกว่า ค่าที่กำลังค้นหา แล้วแสดงค่าตัวก่อน 1 ช่อง 
• -1 : ควรทำการเรียงข้อมูล lookup_array จากมากไปน้อยก่อน ซึ่งหลักการทำงาน จะหาค่าที่น้อยกว่า ค่าที่กำลังค้นหา แล้วแสดงค่าตัวก่อน 1 ช่อง

ตัวอย่างการใช้ฟังก์ชั่น Match

เราจะใช้ตารางเดียวกับบทความ VLOOKUP เพื่อให้คุณ ๆ เห็นถึงความต่างของฟังก์ชั่น MATCH และ VLOOKUP 

รูปที่ 1 ตารางใช้แสดงผลการค้นหาด้วย MATCH

จากรูปที่ 1 สามารถหาลำดับคอลัมน์หรือแถว ของสิ่งที่ต้องการค้นหา ดังนี้

• การหาร้านค้าที่ชื่อ Ooh la la สามารถเขียนสูตร (หาแถว) ดังนี้ 

=MATCH("Ooh la la",B1:B14,0) 

หมายถึง ใช้คำสั่ง Match ทำการค้นหาชื่อร้าน Ooh la la โดยมองหาที่ช่วงของคอลัมน์ B1:B14 และต้องค้นหาโดยคำต้องตรงกันทั้งหมด

ผลลัพธ์ของสูตรนี้ คือ 13

• การหาว่ารหัสร้านค้า S009 สามารถเขียนสูตร (หาแถว) ดังนี้

=MATCH(A6,A1:A14,0)

หมายถึง ใช้คำสั่ง Match ทำการค้นหารหัสร้านในเซลล์ A6 โดยมองหาที่ช่วงของคอลัมน์ A1:A14 และต้องค้นหาโดยคำต้องตรงกันทั้งหมดผลลัพธ์ของสูตรนี้ คือ 6

• การหาคำว่า "Nameshop" สามารถเขียนสูตร (หาคอลัมน์) ดังนี้

=MATCH("nameshop",A1:D1,0)

หมายถึง ใช้คำสั่ง Match ทำการค้นหา "nameshop" โดยมองหาที่ช่วงของคอลัมน์ A1:D1 และต้องค้นหาโดยคำต้องตรงกันทั้งหมดผลลัพธ์ของสูตรนี้ คือ 2

• การหาอัตราภาษี 500,000 บาท สามารถเขียนสูตร ดังนี้

=MATCH(D2,D1:D14,1)

หมายถึง ใช้คำสั่ง Match ทำการค้นหาอัตราภาษี 500,000 บาท โดยมองหาที่ช่วงของคอลัมน์ D1:D14 และต้องค้นหาโดยเรียงข้อมูลก่อน 
ผลลัพธ์ของสูตรนี้ คือ 14

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

ปล. ใครยังไม่เข้าใจการทำงานของ VLOOKUP Function สามารถเข้าไปอ่านทบทวนได้ที่ "การค้นหาข้อมูลด้วย vLookUp จาก Excel"

ตัวอย่างการใช้ฟังก์ชั่น Match ร่วมกับฟังก์ชั่น Vlookup

จากรููปที่ 1 จะแสดงวิธีการนำทั้ง 2 ฟังก์ชั่นมาค้นหาข้อมูลได้อย่างอย่าง แต่คุณอย่าพึ่งคิดว่า ข้อมูลเพียงแค่นี้ไม่เห็นต้องเขียนสูตรให้ยุ่งยาก 

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

การค้นหาเลขคอลัมน์โดยใช้ฟังก์ชั่น match ร่วมกับฟังก์ชั่น vlookup

=VLOOKUP("S013",A1:C14,MATCH("Branch",A1:C1,0),FALSE)

จากคำสั่งที่เขียนด้านบน ส่วนของ Match หมายถึง หาเลขลำดับที่ตรงกับคำว่า "Branch" จากช่วง A1:C1 โดยทำต้องสะกดเหมือนกับคำที่กำลังหา คำตอบของสูตร (MATCH("Branch",A1:C1,0)) นี้ เป็นเลขคอลัมน์ คือ 3  

เมื่อนำมาร่วมกับ vlookup คำตอบของสูตร (=VLOOKUP("S013",A1:C14,MATCH("Branch",A1:C1,0),FALSE)) นี้ เป็น Krabi นั้นเอง

คุณ ๆ คงสงสัยว่า ใครจะอยากรู้ เลขคอลัมน์ หรือแถว ข้อมูล (Data) กันบ้างละ สิ่งที่คนใช้งาน Excel อยากจะรู้ ควรจะเป็น ข้อมูล (Data) ที่อยู่ในเซลล์นั้นๆ ซิ !!!!

คนที่จะอยากรู้ว่าเลขคอลัมน์ หรือเลขแถว ของสิ่งที่ค้นหาเป็นเลขอะไร ก็คือ คนที่เขียนสูตรด้วยฟังก์ชั่น INDEX นั้นเอง 

Read More

HLookup ช่วยค้นหาข้อมูลได้ต่างกับ VLookup อย่างไร

ในบทความนี้ จะอธิบายฟังก์ชั่นในการค้นหาข้อมูลอีกแบบ นั้นก็คือ HLOOKUP Function ซึ่งเป็นฟังก์ชั่นช่วยค้นหาข้อมูลที่ตารางหรือช่วงเป็นแนวนอน 

เราจะใช้ตัวอย่างเดิมจากบทความ "การค้นหาข้อมูลด้วย vLookUp จาก Excel" เพื่อแสดงตัวอย่างการใช้ hloopup แต่ต้องทำการแปลงข้อมูลจาก Column เป็น Row ด้วยคำสั่ง Transpose (T) สามารถอ่านทบทวนการหมุนข้อความที่คัดลอกมา ที่บทความ "6 ปุ่มที่ให้ผล Paste option ใน Excel ที่แตกต่างกันไป"

เมื่อทำการหมุนตารางเรียบร้อยเราจะได้ดังรูปที่ 1

รูปที่ 1 ตารางที่จะใช้เป็นตัวอย่างแสดงการค้นหาด้วยคำสั่ง HLOOKUP

จากรูปที่ 1 ที่ตารางสีเหลือ ให้คุณทำการค้นหาสถานที่ของร้านค้าลงใน Row ที่ 3 ส่วนตารางสีฟ้า ให้คุณทำการค้นหาอัตราภาษีของแต่ละร้านตามรายได้ ลงใน Row ที่ 8 

ฟังก์ชั่น HLOOKUP

เป็นฟังก์ชั่นในการค้นหาข้อมูลที่ต้องการ จากแถว (Row) หรือ ทำการค้นหาในแนวนอน นั้นเอง

รูปแบบฟังก์ชั่น HLOOKUP

ไวยากรณ์ =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

หลักการใช้ฟังก์ชั่น HLOOKUP

• lookup_value คือ ค่าที่ต้องการค้นหา
• table_array คือ ช่วงที่ต้องทำการค้นหา
• row_index_num คือ หมายเลขแถวที่ table_array ต้องส่งค่าไปแสดง
• [range_lookup] คือ การระบุวิธีการค้นหา ซึ่งมี 2 แบบ ดังนี้
• • true (ค่าที่ค้นหามา สามารถเอาค่าใกล้เคียงมาแสดงได้ : Approximate Match)
  • false (ค่าที่ค้นหามา จำเป็นต้องหาข้อมูลที่ตรงกันเท่านั้น : Exact Match)

ตัวอย่างการใช้ HLoopup Function แบบ Exact Match ด้วยการค้นหาชื่อสาขา (ฺฺBranch) ของแต่ละรหัสร้านค้า (idShop) ดังรูปที่ 2

รูปที่ 2 แสดงผลการค้นหาด้วยคำสั่ง HLOOKUP แบบ Exact Match

จากรูปที่ 2 แสดงผลการค้นหาใน Row ที่ 3 โดยต้องเขียนสูตร ดังนี้ 

=HLOOKUP(B$1,$A$5:$N$7,2,FALSE) 

สูตรนี้ หมายถึง ค้นหาด้วย HLOOKUP โดยใช้ คำในเซลล์ B$1 ค้นหา ค้นหาในช่วง $A$5:$N$7 จากช่วงดังกล่าวให้นำค่าในแถวที่ 2 มาแสดง วิธีหาแบบคำตรงกัน 

โดยสูตรตั้งแต่เซลล์ B3:N3 เขียนแบบนี้

=HLOOKUP(B$1,$A$5:$N$7,2,FALSE)
=HLOOKUP(C$1,$A$5:$N$7,2,FALSE)
=HLOOKUP(D$1,$A$5:$N$7,2,FALSE)
=HLOOKUP(E$1,$A$5:$N$7,2,FALSE)
=HLOOKUP(F$1,$A$5:$N$7,2,FALSE)
=HLOOKUP(G$1,$A$5:$N$7,2,FALSE)
=HLOOKUP(H$1,$A$5:$N$7,2,FALSE)
=HLOOKUP(I$1,$A$5:$N$7,2,FALSE)
=HLOOKUP(J$1,$A$5:$N$7,2,FALSE)
=HLOOKUP(K$1,$A$5:$N$7,2,FALSE)
=HLOOKUP(L$1,$A$5:$N$7,2,FALSE)
=HLOOKUP(M$1,$A$5:$N$7,2,FALSE)
=HLOOKUP(N$1,$A$5:$N$7,2,FALSE)

ตัวอย่างการใช้ HLoopup Function แบบ Approximate Match ด้วยการค้นหาอัตราภาษีที่ร้านค้าต้องจ่าย โดยพิจารณาจากรายได้ของร้านค้าอยู่ในช่วงใดของอัตราภาษี ดังรูปที่ 4

รูปที่ 4 แสดงผลการค้นหาของด้วยคำสั่ง HLOOKUP แบบ Approximate Match

จากรูปที่ 4 แสดงผลการค้นหาใน Row ที่ 8 โดยต้องเขียนสูตร ดังนี้ 

=HLOOKUP(B$7,$A$10:$H$12,3,TRUE) 

สูตรนี้ หมายถึง ค้นหาด้วย HLOOKUP โดยใช้ คำในเซลล์ B$7 ค้นหา ค้นหาในช่วง $A$10:$H$12 จากช่วงดังกล่าวให้นำค่าในแถวที่ 3 มาแสดง วิธีหาแบบค่าใกล้เคียงกันได้ 

โดยสูตรตั้งแต่เซลล์ B8:N8 เขียนแบบนี้
=HLOOKUP(B$7,$A$10:$H$12,3,TRUE)
=HLOOKUP(C$7,$A$10:$H$12,3,TRUE)
=HLOOKUP(D$7,$A$10:$H$12,3,TRUE)
=HLOOKUP(E$7,$A$10:$H$12,3,TRUE)
=HLOOKUP(F$7,$A$10:$H$12,3,TRUE)
=HLOOKUP(G$7,$A$10:$H$12,3,TRUE)
=HLOOKUP(H$7,$A$10:$H$12,3,TRUE)
=HLOOKUP(I$7,$A$10:$H$12,3,TRUE)
=HLOOKUP(J$7,$A$10:$H$12,3,TRUE)
=HLOOKUP(K$7,$A$10:$H$12,3,TRUE)
=HLOOKUP(L$7,$A$10:$H$12,3,TRUE)
=HLOOKUP(M$7,$A$10:$H$12,3,TRUE)
=HLOOKUP(N$7,$A$10:$H$12,3,TRUE)

คุณ ๆ สามารถทำแบบฝึกหัดตามบทความนี้ โดย Download ไฟล์ Ex. HLOOKUP Function ที่นี่

Read More

การค้นหาข้อมูลด้วย vLookUp จาก Excel

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

ฟังก์ชั่น VLOOKUP
เป็นฟังก์ชั่นในการค้นหาข้อมูลที่คุณต้องการจากคอลัมน์ (Column) หนึ่งในตาราง หรือ ทำการค้นหาในแนวตั้ง นั้นเอง

รูปแบบฟังก์ชั่น VLOOKUP

ไวยากรณ์ =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

หลักการใช้ฟังก์ชั่น VLOOKUP
lookup_value คือ ค่าที่ต้องการค้นหา
table_array คือ ช่วงหรือตารางที่ใช้ทำการค้นหา
col_index_num คือ หมายเลขคอลัมน์ที่ table_array ต้องส่งค่าไปแสดง

[range_lookup]) คือ การระบุวิธีการค้นหา ซึ่งมี 2 แบบ คือ 

• true (ค่าที่ค้นหามา สามารถเอาค่าใกล้เคียงมาแสดงได้ : Approximate Match)
• false (ค่าที่ค้นหามา จำเป็นต้องหาข้อมูลที่ตรงกันเท่านั้น : Exact Match) 

ตัวอย่างการใช้ VLoopup Function แบบ Exact Match ด้วยการค้นหาชื่อสาขา (ฺฺBranch) ของแต่ละรหัสร้านค้า (idShop) ดังรูปที่ 1

รูปที่ 1 แสดงตัวอย่างการค้นหาข้อมูลใน Excel ด้วย vlookup

จากรูปที่ 1 คุณต้องค้นหาสาขาของแต่ละรหัสร้านค้า ลงในคอลัมน์ C โดยจะมี 2 ตาราง คือ ตารางที่ 1 ระบุ idShop NameShop vLookup และ ตารางที่ 2 ระบุ idShop Branch 

เราจะเริ่มต้นจากคุณต้องสังเกตว่าโจทย์ของเราว่า มีตารางลักษณะใด (แนวนอน หรือ แนวตั้ง) และ ต้องการค้นหาแบบใด (Approximate Match or Exact Match) 

จากโจทย์ที่อยู่ในรูปที่ 1 พบว่า ตารางในโจทย์เป็นลักษณะแนวตั้ง (Vertical - ชื่อหัวตารางอยู่บนเนื้อหาตาราง)  และข้อมูลที่เราต้องการคือ ข้อมูลที่ถูกต้อง เป๊าะๆ ตรงกันเท่านั้น เพราะคุณคงจะไม่อยากนำสาขาที่ใกล้เคียงมาแสดงตำแหน่งของร้านค้าใช่ไหม ดังนั้น โจทย์นี้เราจะใช้ vloopup ที่คุณต้องระบุ false ใน range_lookup ดังนี้ 

=VLOOKUP($A2,$E$2:$F$14,2,FALSE)

จากสูตรที่เขียน หมายถึง ค้นหาค่าใน A2 ในช่วง E2:F14 โดยใช้คอลัมน์ที่ 2 หา ค่าที่ได้ต้องเหมือนกับค่าใน A2

รูปที่ 2 แสดงผลลัพธ์เมื่อค้นหาด้วย vloopup

โดยเมื่อคุณเขียนสูตรในเซลล์ C2 เรียบร้อย สามารถคัดลอกสูตรไปยังเซลล์ C3:C14 ด้วยการคัดลอกแบบลาก สามารถอ่านทบทวนการคัดลอกที่บทความ "เทคนิคการคัดลอกข้อมูลในโปรแกรม Excel"

สูตรที่เขียนในคอลัมน์ C จะเป็น ดังนี้
=VLOOKUP($A2,E$2:F$14,2,FALSE)
=VLOOKUP($A3,E$2:F$14,2,FALSE)
=VLOOKUP($A4,E$2:F$14,2,FALSE)
=VLOOKUP($A5,E$2:F$14,2,FALSE)
=VLOOKUP($A6,E$2:F$14,2,FALSE)
=VLOOKUP($A7,E$2:F$14,2,FALSE)
=VLOOKUP($A8,E$2:F$14,2,FALSE)
=VLOOKUP($A9,E$2:F$14,2,FALSE)
=VLOOKUP($A10,E$2:F$14,2,FALSE)
=VLOOKUP($A11,E$2:F$14,2,FALSE)
=VLOOKUP($A12,E$2:F$14,2,FALSE)
=VLOOKUP($A13,E$2:F$14,2,FALSE)
=VLOOKUP($A14,E$2:F$14,2,FALSE)

ตัวอย่างการใช้ VLoopup Function แบบ Approximate Match ด้วยการค้นหาอัตราภาษีที่ร้านค้าต้องจ่าย โดยพิจารณาจากรายได้ของร้านค้าอยู่ในช่วงใดของอัตราภาษี  ดังรูปที่ 3

รูปที่ 3 แสดงตัวอย่างการหาข้อมูลด้วย vlookup function

จากรูปที่ 3 คุณต้องค้นหาอัตราภาษีของร้านค้า ในตารางสรุปอัตราของแต่ละร้านค้า และ ตารางเกณฑ์การจ่ายอัตราภาษีเป็นตารางในการหา โดยคุณต้องสังเกตว่าโจทย์ของเราว่า มี (1) ตารางลักษณะใด (แนวนอน หรือ แนวตั้ง) และ (2) ต้องการค้นหาแบบใด (Approximate Match or Exact Match) 

จากข้อสังเกตทั้ง 2 ข้อ พบว่า ตารางในโจทย์เป็นลักษณะแนวตั้ง (Vertical - ชื่อหัวตารางอยู่บนเนื้อหาตาราง)  และข้อมูลที่เราต้องการคือ ข้อมูลอัตราภาษี (H Column) ซึ่งต้องค้นหาจากรายได้ของร้านค้า (C Column) เทียบกับตารางการจ่ายอัตราภาษี ดังนั้น โจทย์นี้เราจะใช้ vloopup ที่คุณต้องระบุ True ใน range_lookup ดังนี้ 

=VLOOKUP($C3,$F$3:$H$19,3,TRUE)

จากสูตร หมายถึง VLOOKUP เป็นคำสั่งค้นหาแบบแนวตั้ง (Vertical) นำค่าในเซลล์ $C3 มาใช้ค้นหา ซึ่งหาในช่วง $F$3:$H$19 โดยช่วงจะดึงผลลัพธ์จากคอลัมน์ที่ 3 มาแสดง ค่าที่ต้องการค้นหาค่าแบบใกล้เคียงได้
หลังจากเขียนสูตรในเซลล์ D3 ที่เหลือให้คัดลอกแบบลากลงมาเซลล์ที่ต้องการ จะได้ผลดังรูปที่ 4

รูปที่ 4 แสดงผลการเขียนสูตร VLOOKUP แบบ Approximate Match

สูตรที่เขียนในคอลัมน์ D จะเป็น ดังนี้
=VLOOKUP($C13,$F$3:$H$9,3,TRUE)
=VLOOKUP($C14,$F$3:$H$9,3,TRUE)
=VLOOKUP($C15,$F$3:$H$9,3,TRUE)
=VLOOKUP($C16,$F$3:$H$9,3,TRUE)
=VLOOKUP($C17,$F$3:$H$9,3,TRUE)
=VLOOKUP($C18,$F$3:$H$9,3,TRUE)
=VLOOKUP($C19,$F$3:$H$9,3,TRUE)
=VLOOKUP($C20,$F$3:$H$9,3,TRUE)
=VLOOKUP($C21,$F$3:$H$9,3,TRUE)
=VLOOKUP($C22,$F$3:$H$9,3,TRUE)
=VLOOKUP($C23,$F$3:$H$9,3,TRUE)
=VLOOKUP($C24,$F$3:$H$9,3,TRUE)
=VLOOKUP($C25,$F$3:$H$9,3,TRUE)

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

Read More

TRIM Function ปะทะ Clear Function

ในบทความนี้ จะรวบรวมฟังก์ชั่นที่ใช้ในการลบช่องว่างในโปรแกรม Excel เช่น TRIM CLEAN 

TRIM Function

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

ไวยากรณ์  =TRIM(text)

ตัวอย่าง ใช้คำสั่ง TRIM Function ในการลบช่องว่างในข้อความ ดังนี้

ประโยคในเซลล์ A1 เป็น text ซึ่งในประโยคที่เป็น text นี้จะมีช่องว่างตามที่ทำสีเหลือง

A1=         TRIM Function to remove leading    & trailing spaces from text and     to remove spaces only single spaces between words.        "

เมื่อระบุ TRIM Function ด้วยการเขียน TRIM(A1) ดังรูปที่ 1 

รูปที่ 1 แสดงตัวอย่างการใช้ Trim Function 

จาก TRIM Function ทำการตัดช่องว่างหน้าประโยค และลบช่องว่างระหว่างคำทั้ง 3 ตำแหน่ง 

เงื่อนไขการทำงาน TRIM

• ตัดช่องว่างต้น-ท้ายประโยค
• ตัดช่องว่างระหว่างคำให้เลือก 1 ช่อง

ผลลัพธ์ใน A2 คือ TRIM Function to remove leading & trailing spaces  from text and to remove spaces only single spaces between words. "

👇👇👇👇👇

Clear Function
คำสั่งนี้เป็นการที่ Clear บรรทัด (line breaks) และอักษรที่ไม่สามารถพิมพ์ได้ (non-printable characters) ออกไป 

ไวยากรณ์ =Clear(text)

ตัวอย่าง การใช้คำสั่ง Clear ในการนำบรรทัดและอักษรที่ไม่สามารถพิมพ์ได้ ออก โดยโจทย์ให้ Clear ข้อความในเซลล์ A1 ซึ่งมีข้อความดังนี้
A1="CLEAN function takes a text string and 
returns text that has been "cleaned" of 

line breaks 

and"&CHAR(5)&" other"&CHAR(7)&"  non-printable characters."&CHAR(17) ดังรูปที่ 2

รูปที่ 2 แสดงข้อความที่ต้องการจะใช้คำสั่ง Clear

จากรูปที่ 2 จะเห็นว่า ประโยคในเซลล์ A1 มีการขึ้นบรรทัดใหม่ที่ return, line, and นอกจากนี้ ยังมีอักษรที่ไม่สามารถพิมพ์ได้ CHAR(5), CHAR(7), CHAR(17) เมื่อใช้คำสั่ง Clear Function จะได้ผลลัพธ์ ดังรูปที่ 3

รูปที่ 3 แสดงผลลัพธ์การใช้คำสั่ง Clear Function

Read More

คุณลักษณะของการใช้ Paste ใน Excel

ใน Excel มีฟังก์ชั่นในการวางข้อมูล เป็นสิ่งที่คุณๆ หลายคนคุ้นเคย แต่จะมีสักกี่คน ที่จะเข้าใจคุณสมบัติของ Paste อย่างถูกต้องครบถ้วนบ้าง

ในบทความนี้ จะนำเสนอคุณลักษณะ (Features) ของ Paste โดยช่องทางอย่าง Home Tab หรือ คลิกขวาที่เซลล์ที่ต้องการจะวางข้อความ ซึ่งจะมีคุณลักษณะแตกต่างกัน ดังนี้ 

วิธีแรก เปิด Home Tab >> คลิกปุ่มสามเหลี่ยมที่ Paste >> จะแสดงแถบเมนู ดังรูปที่ 1  

รูปที่ 1 แสดงแถบเมนู Paste

จากรูปที่ 1 จะแบ่งเป็น 4 ส่วน คือ 

1. ส่วน Paste
2. ส่วน Values
3. ส่วน Other Paste Options
4. ส่วน Paste Special...

อธิบายปุ่มที่อยู่ในส่วน Paste 

Paste (P) : ผลลัพธ์การวางเนื้อหาเซลล์ที่คัดลอกจะได้ ทั้งเนื้อหา (Content) และรูปแบบ (Format)

Formulas (F) : ผลลัพธ์การวางเนื้อหาที่คัดลอกจะได้เฉพาะสูตร โดยไม่รูปแบบ (Format) ของเนื้อหามาแสดง

Formulas & Number Formatting (O) : ผลลัพธ์การวางเนื้อหาที่คัดลอกมาได้ทั้งสูตรและรูปแบบของเนื้อหามาแสดง

Keep source formatting (K) : ผลลัพธฺการวางเนื้อหาที่คัดลอกมา พร้อมทั้งรูปแบบของเซลล์เดิมมาด้วย โดยเครื่องมือนี้ ต้องระวังกรณีข้อมูลของเซลล์เดิม มีการใช้การอ้างอิงเซลล์ (Call Reference) แล้วเซลลปลายทางที่จะวาง ไม่ได้มีโครงสร้างของ Sheet เหมือนกัน เช่น 

รูปที่ 2 แสดงตัวอย่างข้อมูลที่จะวางด้วย Keep source formatting

จากรูปที่ 2 นี้ เซลล์ A3 ถึง A5 เป็นเซลล์อ้างอิง ถ้าคุณ ๆ ทำการคัดลอกแล้ว ไปวางที่เซลล์ F ดังรูปที่ 3

รูปที่ 3 แสดงผลการวางโดนโครงสร้าง Sheet ไม่ตรงกัน

จากรูปที่ 3 ผลลัพธ์การวางในคอลัมน์ F (F3, F4) จะมองหาเนื้อหาจากเซลล์ถัดไป ให้เหมือนกับเซลล์อ้างอิงของเซลล์ต้นทาง (A3, A4) 

No Border (B) : ผลลัพธ์การวางเนื้อหาที่คัดลอกมา โดยไม่นำเส้นตารางของเซลล์เดิมมาด้วย

Keep source column width (W) : ผลลัพธ์การวางเนื้อหาที่คัดลอกมา พร้อมความกว้างของเซลล์เดิมมาด้วย

Transpose (T) : ผลลันธ์การวางเนื้อหาที่ค้ดลอกมา โดยหมุนเนื้อหาให้ตรงข้ามกับช่วงของเซลล์เดิม 



อธิบายปุ่มที่อยู่ในส่วน Values

Values (V) : ผลการวางจะวางเฉพาะผลการคำนวณแล้ว

Values & Number Formatting (A) : ผลการวางจะวางทั้งสูตรและรูปแบบ ของเซลล์เดิม

Values & Source Formatting (E) : ผลการวางจะวางทั้ง ผลการคำนวณและรูปแบบ ของเซลล์เดิม



อธิบายปุ่มที่อยู่ในส่วน Other Paste Options

Formatting (F) : ผลการวางจะวาง เฉพาะรูปแบบของเซลล์เดิม

Paste Link (N) : ผลการวางจะวาง เซลล์อ้างอิง (Call Reference) ของเซลล์เดิม

Picture (U) : ผลการวางจะวางเป็นรูปภาพ

Linked Picture (I) : ผลการวางจะวาง URL ของภาพ



อธิบายส่วนของ Paste Special...  
เมื่อคุณ ๆ คลิกที่ปุ่ม Paste Special... ตามรูปที่ 1 หรือ Shortcut : Ctrl+Alt+V คุณ ๆ จะพบ pop up ดังรูปที่ 4

รูปที่ 4 แสดงแถบเมนู Paste Special...

รูปที่ 4 ในส่วน Paste จะเหมือนกับคำอธิบายด้านบน ส่วน Operation จะทำให้คุณ ๆ นำเนื้อหาที่คัดลอกมาทำการวางลงไปที่เซลล์ใหม่ โดยไม่ดำเนินการใด (None) บวก (Add) ลบ (Subtract) คูณ (Multiply) และ หาร (Divide) นอกจากนี้ ยังสามารถยกเว้นช่องวาง (Skip blanks) อีกด้วย

ส่วนวิธีการ "คลิกขวาที่เซลล์ที่ต้องการจะวางข้อความ" สามารถอ่านได้ที่บทความ ุ"6 ปุ่มที่ให้ผล Paste option ใน Excel ที่แตกต่างกันไป"

Read More