การหาผลต่างของจำนวนวัน เดือน ปี ด้วยโปรแกรม Excel แบบง้ายยยยยย...ง่าย

สวัสดีค่ะ ผู้เขียนหายไปนานมากที่ไม่ได้ update ข้อมูลบน Blogger นี้ เนื่องจากภาระงานประจำที่เปลี่ยนไป เผลอแปล๊บบบเดียวจะสิ้นปีแล้ว ปลายปีงานเริ่มเข้าที่เข้าทาง เลยมีเวลาหันกับมาดู ต้องใช้เวลาในการทบทวนความทรงจำกันหน่อย 5555

ก่อนที่จะบ่นยาววววกันไป เข้าเรื่องเนื้อหากันเลย วันนี้ผู้เขียนขอเริ่มบทความแรกก่อนจะสิ้นปี 2561 อิอิอิอิ ด้วยการอธิบายขั้นตอนการทำงานสูตร DATEDIF() ของ Excel โดย MS Office ใน Version ต่างๆ ที่รองรับกับสูตรนี้ ได้แก่ Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 เป็นต้น

DATEDIF เป็นฟังค์ชั่นในการหาผลต่างระหว่าง 2 วัน คือ วันเริ่มต้น กับ วันสิ้นสุด โดยเราสามารถเขียนตามไวยากรณ์ของฟังค์ชั่น ดังนี้

ไวยากรณ์

=DATEDIF(วันเริ่มต้น, วันสิ้นสุด, ชนิดข้อมูลที่ต้องการให้แสดงคำตอบ)

โดย "ชนิดข้อมูลที่ต้องการให้แสดงคำตอบ" คุณๆ สามารถกำหนดได้ 5 รูปแบบด้วยกัน ดังนี้

"ํY"     หาผลต่างของจำนวนปีที่ต่างกันระหว่างวันเริ่มต้นและวันสิ้นสุด

"M"     หาผลต่างของจำนวนเดือนที่ต่างกันระหว่างวันเริ่มต้นและวันสิ้นสุด

"D"     หาผลต่างของจำนวนวันที่ต่างกันระหว่างวันเริ่มต้นและวันสิ้นสุด

"MD"   หาผลต่างของจำนวนเดือนที่ต่างกัน โดยไม่สนใจวันหรือปีของวันเริ่มต้นและวันสุดสุด

"YM"   หาผลต่างของจำนวนวันที่ต่างกัน โดยไม่สนใจปีของวันเริ่มต้นและวันสุดสุด

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

ต้องการหาผลต่างของวันเริ่มต้นและวันสิ้นสุด โดยแสดงข้อมูลตามแบบต่างๆ ดังรูปที่ 1 

รูปที่ 1 ตัวอย่างการหาผลต่างของวันเริ่มต้นและวันสิ้นสุด ตามชนิดข้อมูลต่างๆ

จากรูปที่ 1 ขออธิบายสูตร DATADIF() ตามรูปแบบชนิดข้อมูลที่ต้องการให้แสดง ดังนี้

1. คอลัมน์ C กำหนดสูตรหาผลต่างจำนวนปี ที่ต่างกันระหว่างวันเริ่มต้นและวันสิ้นสุด คือ=DATEDIF(B2,A2,"Y") 

2. คอลัมน์ D กำหนดสูตรหาผลต่างจำนวนเดือน ที่ต่างกันระหว่างวันเริ่มต้นและวันสิ้นสุด คือ =DATEDIF(B2,A2,"M")

3. คอลัมน์ E กำหนดสูตรหาผลต่างจำนวนวัน ที่ต่างกันระหว่างวันเริ่มต้นและวันสิ้นสุด คือ =DATEDIF(B2,A2,"D")

4. คอลัมน์ F กำหนดสูตรหาผลต่างจำนวนเดือนที่ต่างกัน โดยไม่สนใจวันหรือปีของวันเริ่มต้นและวันสุดสุด คือ =DATEDIF(B2,A2,"MD")

5. คอลัมน์ G กำหนดสูตรหาผลต่างจำนวนวันที่ต่างกัน โดยไม่สนใจปีของวันเริ่มต้นและวันสุดสุด คือ =DATEDIF(B2,A2,"YM")

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

รูปที่ 2 ตัวอย่างการหาวันที่ระหว่างวันที่ได้รับมอบหมายและวันที่ดำเนินการเสร็จ

จากรูปที่ 2 ท่านจะเห็นว่ามีบางคอลัมน์ที่ไม่ได้ระบุวันที่ดำเนินการเสร็จ เช่น B2, B5-B7, B12 และ B18 ถ้าเราไม่ใส่สูตรในการตรวจสอบค่าว่างจะทำให้การแสดง DATEDIF ทำงานผิดพลาด ดังนั้น เพื่อแก้ไขปัญหาดังกล่าว เราจะใช้สูตร IF เข้ามาช่วย ดังนี้
=IF(B2="","ไม่ระบุวันที่ไว้",DATEDIF(E2,B2,"D"))

แต่ถ้าหากตัวอย่างนี้ มีบางแถวที่ไม่มีการระบุวันที่ที่ได้รับมอบหมายด้วย คุณสามารถใช้ OR เข้ามาช่วยในการตรวจสอบร่วมกับ IF ได้ ดังนี้
=IF(OR(E2="",B2=""),"ไม่ระบุวันที่ไว้",DATEDIF(E2,B2,"D"))

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

Read More

INDEX MATCH Functions ดีกว่า VLOOKUP Function อย่างไร

บทความก่อนหน้านี้ เรานำเสนอทั้งการทำงานและตัวอย่างของ Vlookup Function / Index Function และ Match Function ซึ่งถ้าคุณสามารถทบทวน vlookup Function ได้ที่ "การค้นหาข้อมูลด้วย vLookUp จาก Excel" ส่วน Index Function หรือ Match Function ได้ที่ "ผสานคุณสมบัติของ Index Function กับ Match Function เพื่อแทนคำสั่ง vlookup Function" 

ในบทความนี้ เรามาดูข้อดีต่างๆ ของการนำ Index&Match มาใช้ร่วมกัน แทน Vlookup ดีกว่า 

ข้อดีของการใช้ INDEX ร่วมกับ MATCH แทนการใช้ VLOOKUP 
1. สามารถแทรกหรือลบคอลัมน์ได้ภายหลัง เมื่อคุณใช้ Index Match ในการทำงานแทน Vlookup คุณสามารถแทรกคอลัมน์ระหว่าง(คอลัมน์เริ่มต้นของตาราง จนถึึง คอลัมน์ที่คุณนำมาใช้อ้างอิงในสูตร) แต่ ✋ถ้าคุณใช้ Vlookup ในสูตร คุณจะไม่สามารถแทรกคอลัมน์ได้ เพราะสูตรที่คุณเขียนไว้มันจะ Error ทันที 

รูปที่ 1 ตัวอย่างที่ใช้เปรียบเทียบระหว่าง Index&Match และ Vlookup

ตัวอย่าง การค้นหาข้อมูลในเซลล์ด้วย Index&Match จากรูปที่ 1 เราได้เขียนสูตรด้วย Index&Match ในคอลัมน์ C และ D เพื่อค้นหาข้อมูลสาขาและรายได้ของแต่ละร้าน จากตารางสีฟ้า 
ได้สูตรในคอลัมน์ C ดังนี้ 

=INDEX($F$2:$I$14,MATCH($A2,$F$2:$F$14,0),MATCH($C$1,$F$1:$I$1,0))

จากสูตรข้างบนนี้ สามารถทำงานได้คำตอบเดียวกับ vlookup ได้ โดยสูตรนี้ ประกอบด้วย Index&Match Function ซึ่งส่วนของ index function มี 3 arguments (แบ่งด้วยสีเป็น 3 สีเพื่อให้คุณสังเกตได้ง่ายขึ้น คือ ส้ม น้ำเงิน ม่วง) 

ในส่วนสีน้ำเงินแทน argument ตัวที่ 2 ของ Index Function ด้วยการใช้ฟังก์ชั่น Match Function เข้ามา ซึ่งเขียนให้นำค่าใน A2 (ค่าในเซลล์ คือ s001) มาค้นหาในช่วง F2:F14 (ค่าในเซลล์ คือ s014, s009, s002, s001, s008, s003, s015, s012, s004, s005, s006, s013, s010) และค้นหาแบบต้องสะกดตรงกันทั้งหมด ผลลัพธ์ของฟังก์ชั่นนี้ คือ 4

ในส่วนสีม่วงแทน argument ตัวที่ 3 ของ Index Function ด้วยการใช้ฟังก์ชั่น Match Function เข้ามา ซึ่งเขียนให้นำค่าใน C1 (ค่าในเซลล์ คือ Branch) มาค้นหาในช่วง F1:I1 (ค่าในเซลล์ คือ idShop, Branch, income, อัตราภาษี) และค้นหาแบบต้องสะกดตรงกันทั้งหมด ผลลัพธ์ของฟังก์ชั่นนี้ คือ 2 

ดังนั้น ช่วงค้นหา F2:I14 ใน Index Function จะมองหาข้อมูลในแถว 4 และคอลัมน์ที่ 2 ดังรูปที่ 2

รูปที่ 2 แสดงผลลัพธ์ของการใช้ Index&Match 

ได้สูตรในคอลัมน์ D ดังนี้

=INDEX($F$2:$I$14,MATCH($A2,$F$2:$F$14,0),MATCH($D$1,$F$1:$I$1,0))

จากสูตรนี้ สามารถทำงานได้คำตอบเหมือนกับ vlookup ได้ โดยสูตรนี้ ประกอบด้วย Index&Match Function ซึ่งส่วนของ index function มี 3 arguments (แบ่งด้วยสีเป็น 3 สีเพื่อให้คุณสังเกตได้ง่ายขึ้น คือ ส้ม น้ำเงิน ม่วง) 

ในส่วนสีน้ำเงินแทน argument ตัวที่ 2 เขียนเหมือน argument ตัวที่ 2 ในคอลัมน์ C


ในส่วนสีม่วงแทน argument ตัวที่ 3 ของ Index Function ด้วยการใช้ฟังก์ชั่น Match Function เข้ามา ซึ่งเขียนให้นำค่าใน C1 (ค่าในเซลล์ คือ Income) มาค้นหาในช่วง F1:I1 (ค่าในเซลล์ คือ idShop, Branch, income, อัตราภาษี) และค้นหาแบบต้องสะกดตรงกันทั้งหมด ผลลัพธ์ของฟังก์ชั่นนี้ คือ 3

ดังนั้น ช่วงค้นหา F2:I14 ใน Index Function จะมองหาข้อมูลในแถว 4 และคอลัมน์ที่ 3 ดังรูปที่ 2

หลังจากนั้น ทำการแทรกคอลัมน์ในตารางที่ใช้สำหรับค้นหา (ตารางสีฟ้า) ดังรูปที่ 3

รูปที่ 3 แสดงตัวอย่างเมื่อแทรกคอลัมน์ในตารางที่ใช้สำหรับค้นหา (ตารางสีฟ้า)

คุณจะเห็นได้ว่าสูตรในคอลัมน์ C มีการเปลี่ยนเซลล์อ้างอิงทำให้คำตอบของสูตรที่ใช้ Index&Match Function ยังคงได้คำตอบเดิม ทำให้เราไม่ต้องแก้ไขสูตรนั้นเอง ซึ่งถ้าคุณทำการค้นหาคอลัมน์ C และ D ด้วย Vlookup Function เมื่อคุณแทรกคอลัมน์แบบเดียวกับรูปที่ 3 คุณต้องกลับมาแก้ไขสูตรด้วย 

✻✼✽✻✼✽✻✼✽

2. สามารถค้นหาข้อมูลจากทิศทางใดก็ได้ เมื่อคุณต้องการค้นหาข้อมูลที่อยู่ทางด้านซ้ายของตารางอ้างอิง คุณจะไม่สามารถใช้ Vlookup ค้นหาได้ ดังนั้น ต้องใช้ Index&Match เข้ามาแทน

ตัวอย่าง จากรูปที่ 1 ที่ตารางสีขาว ให้ทำการค้นหา "ชื่อร้าน" (Name Shop) จากสาขา (Branch) "Lampang" ซึ่งแบบนี้จะใช้ vlookup ไม่ได้ เพราะชื่อร้าน (คอลัมน์ที่ค้นหา) อยู่ทางซ้ายของสาขา (คอลัมน์อ้างอิง) เมื่อคุณเขียนสูตรด้วย Index&Match สามารถเขียนได้ ดังนี้

=INDEX($A$2:$D$14,MATCH("Lampang",C2:C14,0),MATCH("NameShop",A1:D1,0))

จากสูตรนี้ ส่วนของ index function ประกอบด้วย 3 arguments (แบ่งด้วยสีเป็น 3 สี เพื่อให้คุณสังเกตได้ง่ายขึ้น คือ ส้ม น้ำเงิน ม่วง) 
โดย argument ตัวที่ 1 (สีส้ม) เป็นการกำหนดช่วงหรือตารางที่ทำการค้นหาข้อมูลมาแสดง

ส่วน argument ตัวที่ 2 (สีน้ำเงิน) เป็นการกำหนดเลขแถวให้กับ index function ค้นหา โดยใช้ match function มาหาเลขของเซลล์ที่เก็บ Lampang ไว้ ผลลัมน์ที่ได้ คือ 6

ส่วน argument ตัวที่ 3 (สีม่วง) เป็นการกำหนดเลขคอลัมน์ให้กับ index function ค้นหา โดยใช้ match function มาหาเลขของเซลล์ที่เก็บ NameShop ไว้ ผลลัมน์ที่ได้ คือ 2

✻✼✽✻✼✽✻✼✽

3. สามารถค้นหาค่าประมาณการได้ ในฟังก์ชั่น vlookup มีส่วนของการกำหนดวิธีการค้นหาได้ 2 แบบ คือ ค่าที่ตรงกัน (Exact Match) และ ค่าโดยประมาณ (Approximate Match) ซึ่งในตัวอย่างที่ผ่านมา เราได้อธิบายข้อดีที่ใช้วิธีการค้นหาแบบค่าที่ตรงกัน (Exact Match) แต่ในข้อดีข้อนี้ เราจะอธิบายข้อดีที่ใช้วิธีการค้นหาค่าโดยประมาณ (Approximate Match) นั้นเอง

ตัวอย่าง ค้นหาเกรด โดยให้วิธีการค้นหาเป็นแบบประมาณ (Approximate Match) ดังรูปที่ 4

รูปที่ 4 แสดงตัวอย่างการหาเกรดด้วย Index&Match

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

=INDEX($B$2:$B$9,MATCH("99",$A$2:$A$9,-1))

จากสูตรนี้เราใช้ Index Function ที่มี 2 arguments (แบ่งด้วยสี 2 สี ตามลำดับ) โดย argument ที่ 1 (สีส้ม) ใส่ช่วงที่ต้องการค้นหา ซึ่งในที่นี้ต้องการค้นหาเกรด เราจึงใส่ B2:B9

ส่วน argument ที่ 2 เราใช้ Match Function เข้ามาช่วยในการค้นหาเลขของเซลล์ 

โดยใน Match Function มี 3 arguments ซึ่งใน argument ที่ 1 เรากำหนด "99" ลงไป, ใน argument ที่ 2 เรากำหนดช่วงที่จะเข้าไปค้นหา "A2:A9", ใน argument ที่ 3 เรากำหนดวิธีในการค้นหาเป็นแบบประมาณ (Approximate Match) โดยในตัวอย่างนี้ กำหนดเป็น -1 ซึ่งหมายถึง หาค่าที่มากกว่าหรือเท่ากับ นั้นเอง

ปล. ถ้าในรูปที่ 4 เป็นการเรียงคะแนนจากน้อยไปมาก การเขียนสูตรต้องกำหนดวิธีการค้นหาใน Match Function เป็น "1" หมายถึง หาค่าที่น้อยกว่าหรือเท่ากับ นั้นเอง

✻✼✽✻✼✽✻✼✽

4. ไม่ต้องนับคอลัมน์เพื่อระบุเลขคอลัมน์ที่ต้องดึงข้อมูลมาแสดงอีก เมื่อคุณใช้ Index&Match แทน Vlookup คุณไม่ต้องสนใจว่าค่าที่เราค้นหานั้นอยู่เลขคอลัมน์ใดอีก เพราะคุณสมบัติของ Match Function เข้ามาช่วยคุณแล้ว 

Read More

ผสานคุณสมบัติของ 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