MATCH

ฟังก์ชั่น MATCH ใช้หา"ตำแหน่ง"ในช่วงของชุดข้อมูลนั้นๆ ใช้หาตำแหน่งได้ทั้งตัวเลขและ ตัวอักษร และCharacter ต่างๆ

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

ไวยากรณ์
= MATCH(lookup_value, lookup_array, match_type)
= MATCH(สิ่งที่ต้องการหาตำแหน่ง,ชุดข้อมูล, Match_type)

Match type นี้จะแยกเป็น 3 แบบ คือ 0,1,-1
โดยปกติในการใช้ฟังก์ชั่น MATCH โดยทั่วไปจะเลือกใช้ Match type = 0 เป็นหลักเนื่องจากไม่จำเป็นต้องจัดเรียงค่าในชุดข้อมูล จากน้อยไปมากหรือ มากไปน้อยนั่นเอง แต่ในที่นี้จะอธิบายถึง Match type ทั้ง 3 แบบ

ก่อนจะอธิบายถึงความแตกต่างของ Match type ทั้ง 3 แบบนั้นจะขอยกตัวอย่างการใช้ฟังก์ชั่น MATCH ให้ดูเป็นตัวอย่างสักเล็กน้อย ก่อนดังนี้

เช่น ถ้าช่วงเซลล์ A1 ถึง A4 ประกอบด้วย 15,5,12,8 หาเราต้องการหา"ตำแหน่ง"ของ 12 จะเขียนสูตรได้ดังนี้ =MATCH(12,A1:A4,0) และได้"ตำแหน่ง"คือ 3 เนื่องจาก 12 เป็นเป็นรายการที่สาม ในชุดข้อมูลนั่นเอง

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






Match type ,0 (ไม่บังคับเรียงชุดข้อมูล)

จะเรียงข้อมูล จากมากไปน้อย หรือน้อยไปมาก หรือไม่ก็ตาม
สามารถหา แหน่ง ของค่า ที่มีอยู่ ในชุดข้อมูลนั้นได้ เสมอ
แต่ถ้า ไม่มีค่านั้นๆ อยู่ในชุดข้อมูล จะหา ค่านั้นๆ ไม่ได้ ส่งกลับค่าเป็น #N/A
นั่นก็หมายถึง ชุดข้อมูล จะค่ามากน้อยสลับกันไปมา หรือเรียงจาก มากไปน้อย หรือ จากน้อยไปมาก จะไม่มีผลในการหาตำแหน่งของข้อมูลเลย




Match Type ,1 (บังคับ เรียงชุดข้อมูลน้อยไปมาก)
หากไม่เรียง ข้อมูล จาก น้อยไปมาก อาจจะหาตำแหน่ง ถูก หรือ ผิดก็ได้ ไม่แน่นอน
ดังนั้นจึง บังคับ เรียงข้อมูล จาก น้อยไปมาก จึงจะหา ตำแหน่งที่ถูกต้องได้


ข้อน่าสนใจของการใช้ Match type ,1
กรณีที่ไม่มีค่านั้นๆ อยู่ใน ชุดข้อมูล Match Type ,1 จะแสดง ตำแหน่ง ของค่า
ที่น้อยกว่า ที่อยู่ใกล้ค่านั้นที่สุด เสมอ ตัวอย่าง...



ในที่นี้ 58 ไม่มีในชุดข้อมูล ดังนั้น Match type ,1 จึงแสดงตำแหน่งของค่าที่น้อยกว่า 58 คือ 50 แทน และจะได้ตำแหน่งคือ 3

ในที่นี้ 72 ไม่มีในชุดข้อมูล ดังนั้น Match type ,1 จึงแสดงตำแหน่งของค่าที่น้อยกว่า 72 คือ 70แทน และจะได้ตำแหน่งคือ 5

ในทีนี้ 20 ไม่มีในชุดข้อมูล และไม่มีค่าที่น้อย กว่า 20 ในชุดข้อมูล ดังนั้น Match type ,1 จึงไม่สามารถหาตำแหน่งใดๆ ออกมาได้ (#N/A)


Match Type ,-1 (บังคับเรียงชุดข้อมูลมากไปน้อย )
หากไม่เรียง ข้อมูล จาก มากไปน้อย อาจจะหาตำแหน่ง ถูก หรือ ผิดก็ได้ ไม่แน่นอน
ดังนั้นจึง บังคับ เรียงข้อมูล จาก มากไปน้อย จึงจะหา ตำแหน่งที่ถูกต้องได้



ข้อน่าสนใจของ Match type ,-1
กรณีที่ไม่มีค่านั้นๆ อยู่ใน ชุดข้อมูล Match Type ,-1 จะแสดง ตำแหน่ง ของค่า ที่ มากกว่า ที่อยู่ใกล้ค่านั้นที่สุด เสมอ


ในที่นี้ 20ไม่มีในชุดข้อมูล ดังนั้น Match type ,-1 จึงแสดง ตำแหน่งของค่าที่ มากกว่า 20 คือ 30 แทน และจะได้ตำแหน่งคือ 5

ในที่นี้ 58 ไม่มีในชุดข้อมูล ดังนั้น Match type ,-1 จึงแสดง ตำแหน่งของค่าที่ มากกว่า 58 คือ 60 แทน และจะได้ตำแหน่งคือ 2

ในที่นี้ 72 ไม่มีในชุดข้อมูล และไม่มีค่าที่มาก กว่า 72 ในชุดข้อมูล ดังนั้น Match type ,-1 จึงไม่สามารถหาตำแหน่งใดๆ ออกมาได้ (#N/A)

RANK

RANK ใช้จัดอันดับตามคะแนนมากไปน้อยลดหลั่นกันลงไป หรือจะจัดลำดับจากคะแนนน้อยไปหามากก็ได้

ไวยากรณ์
=RANK(number,ref,order)
=RANK(จำนวน หรือเซลล์อ้างอิง,ชุดข้อมูลที่ให้อ่านค่า,0 หรือ 1)


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

ตัวอย่าง


กรณีเรียงอันดับจากมากไปน้อย คือ คะแนนมากอยู่อันดับหนึ่ง คะแนนน้อยกว่าอยู่อันดับถัดมา แบบนี้สามารถละ ,0 ได้

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




การจัดอันดับคะแนนแบบ"กอล์ฟ"

โจทย์แบบนี้เจอบ่อย ขอเรียกว่าการจัดอันดับคะแนนแบบกอล์ฟก็แล้วกัน เช่นการแข่งขันกอล์ฟที่มีแต้มต่อ และกรณีที่มีผู้ที่ได้คะแนนเท่ากันจะมองที่แต้มต่อ

โจทย์ต่อไปนี้กำหนดว่า หากคะแนนเท่ากันให้มองที่แต้มต่อถ้าใครแต้มต่อ น้อยกว่าให้ถือเป็นผู้ชนะ(จะกำหนดให้ผู้มีแต้มต่อมากกว่าชนะก็ได้ แล้วแต่กรณี)

หลักการง่ายๆคือ ทำแต้มต่อให้เป็นจุดทศนิยม แล้วนำไปบวกกับคะแนนที่ได้ จะได้คะแนนที่ไม่ซ้ำกันออกมา แล้วจึงไปหาลำดับ ในที่นี้ให้ แต้มต่อถูกหารด้วย 100 ก่อน แล้วนำไปบวกกับคะแนนที่ได้

การหารแต้มต่อเพื่อให้เป็นทศนิยมในที่นี้ ควรหารด้วย 100 ขึ้นไป เนื่องจากแต้มต่อเป็นเลขไม่เกินสองหลัก

หากโจทย์กำหนดว่าให้หาอันดับที่ 1,2,3 ออกมาเลยว่าเป็นใคร จากตัวอย่างข้างบน ค่า RANK ที่ออกมาจะพบว่า คุณ E ได้ที่ 1 คุณ A ได้ที่ 2 และ คุณ C ได้ที่ 3

แต่กรณีแบบนี้จะไม่ใช้ RANK เลยก็ได้ เนื่องจากโจทย์ไม่ได้ต้องการหา"ค่า" RANK แต่ต้องการให้หาว่าใครชนะ ที่ 1,2,3 จึงควรใช้ คะแนนที่น้อยที่สุดเป็นตัววัดแล้ว ชี้ลงไปว่า ใครได้ที่ 1,2,3 การวัดค่าน้อยไปมาก เราจะใช้ฟังก์ชั่น SMALL มาช่วย โดยไม่ต้องใช้ RANK เลย แล้วใช้ MATCH + INDEX หารายชื่อของผู้ที่ได้คะแนนตามต้องการออกมา

จะเปลี่ยนค่าตัวเลขเพื่อหาค่าน้อยไปมาก ที่ SMALL

กรณีโจทย์ดังกล่าวหากไม่เพิ่มคอลัมน์คะแนน-2 ขึ้นมาเลยก็ทำได้ โดยทำเป็น Array ดังนี้ หลักการยังคงใช้ SMALL + MATCH+INDEX เหมือนเดิม

ที่เซลล์ใดๆ ในที่นี้คือ D12 พิมพ์ =INDEX($B$4:$B$10,MATCH(SMALL($D$4:$D$10+$C$4:$C$10/100,1),$D$4:$D$10+$C$4:$C$10/100,0))
จากนั้น(บังคับ) กดแป้น Ctrl +Shift +Enter พร้อมกัน
ติดรูป กล Array

{=INDEX($B$4:$B$10,MATCH(SMALL($D$4:$D$10+$C$4:$C$10/100,1),$D$4:$D$10+$C$4:$C$10/100,0))}

RANK กระโดดและ RANK ไม่กระโดด

โดยปกติทั่วๆไปนั้น ฟังก์ชั่น Rank จะจัดอันดับแบบกระโดดให้เสมอเช่น มีลำดับที่ 3 อยู่ 3 คน ดังนั้นคนที่ได้คะแนนถัดไปจะถูกจัดเป็นลำดับที่ 6 ทันที เนื่องจากถือว่า คนที่ได้ที่ 3 ทั้ง 3คนนั้น ถูกมองว่าจะจัดอันดับเป็น 3-4-5 ดังนั้น คนที่ได้อันดับถัดมาจะถูกแสดงเป็นอันดับ 6
ถ้าหากต้องการทำให้เป็นแบบ Rank ไม่กระโดดจะทำอย่างไร เช่น กรณีที่มีผู้ที่ได้อันดับ 3 อยู่ 3 คน คนที่ได้อันดับถัดไป ให้แสดงเป็นอันดับ 4 ไม่ใช่อันดับ 6


ตัวอย่าง มีคะแนนอยู่ชุดหนึ่งต้องการจัดลำดับแบบไม่กระโดด

สามารถทำได้หลายๆวิธี จะยกตัวอย่างสัก 2 วิธี

วิธีที่ 1 หากสามารถ Sort data ได้ จะช่วยลดความยุ่งยากลงไปได้มาก
แล้วใช้ IF ธรรมดาเงื่อนไขเดียว จัดอันดับออกมา



หากมีกรณีไม่สามารถ Sort data ได้ จะต้องใช้ฟังก์ชั่นอื่นๆมาช่วยปรับ
ข้อมูลหรือ ชุดข้อมูลให้เป็นไปตามที่ต้องการก่อน



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

ถ้าเข้าใจได้ดี จะพบว่า การซ้อนๆอะไรๆ เข้าไปในสูตรเพื่อให้มันเป็นสูตรเดียวกันที่กระชับนั้น จะมีลีลาของมันเอง และวิชา Excel ของท่านจะก้าวหน้าไปอย่างเงียบๆโดยไม่รู้ตัว

เชื่อผมเต๊อะ !!

รายละเอียดสูตรต่างๆ เช่น IF และ VLOOKUP ในที่นี้จะไม่อธิบายตัวสูตรละเอียด เนื่องจาก ในบทความก่อนๆนี้ ทั้ง IF และ VLOOKUP ผมได้อธิบายมาค่อนข้างละเอียดระดับหนึ่งแล้ว

และต่อๆไป อาจจะอธิบายในสูตรต่างๆ ที่เคยเขียนไว้ให้น้อยลง จะไปเน้นถึง ลีลา การเขียนสูตรและความเข้าใจว่า จะต้องเขียนสูตรอย่างไร และจะใช้สูตรนั้นๆ อย่างไร ให้มากขึ้น

SMALL

SMALL ใช้หาค่าในข้อมูลหรือชุดข้อมูลว่า เป็นเท่าใดตามลำดับกำหนด เรียงลำดับที่น้อยที่สุดไปมากที่สุด

นั่นคือฟังก์ชั่น SMALL จะตรงข้ามกับฟังก์ชั่น LARGE นั่นเอง

ไวยากรณ์
=SMALL(array,k)
=SMALL(ข้อมูลหรือชุดข้อมูล , ลำดับที่น้อยต้องการ)

ตัวอย่าง

ให้หาค่าที่เรียงลำดับจากน้อยที่สุดไปมากที่สุด

LARGE

LARGE ใช้หาค่าในข้อมูลหรือชุดข้อมูลว่า เป็นเท่าใด ตามลำดับที่กำหนด


ไวยากรณ์

=LARGE(array,k)
=LARGE(ข้อมูลหรือชุดข้อมูล , ลำดับที่ต้องการ)


ตัวอย่าง
มีคะแนนอยู่ ชุดหนึ่งตามตารางด้านล่าง ต้องการหาว่า อันดับ 1-2-3-4-5 คือคะแนนอะไร

CONCATENATE หรือ &

CONCATENATE หรือ & ใช้เชื่อมเซลล์ หรือ Character ใดๆ ตั้งแต่ 2 เซลล์ขึ้นไปเข้าด้วยกัน

ไวยากรณ์
=CONCATENATE (text1,text2,...)
หรือจะใช้เครื่องหมาย & เป็นตัวเชื่อมก็ได้ โดยปกติ มักจะนิยม ใช้ & เชื่อมมากกว่าการใช้ฟังก์ชั่น CONCATENATE

ตัวอย่าง



CONCATENATE มีประโยชน์มาก ในการเชื่อมเซลล์หรือ Character ต่างๆ เนื่องจากในการใช้งานบน Excel นั้นบางครั้งไม่สามารถจะใช้สูตรให้อ่านค่าจากเซลล์นั้นๆโดยตรงได้ จำเป็นต้องใช้ CONCATENATE เข้าช่วยเพื่อให้เซลล์นั้นๆ สามารถอ่านค่าได้ถูกต้อง

ตัวอย่างที่เห็นได้ชัดและ เจอบ่อยๆ เช่นการใช้ VLOOKUP เป็นต้น
และ CONCATENATE ยังใช้ได้กับกรณีอื่นๆ อีกมากมาย

SUBSTITUTE

SUBSTITUTE จัดเป็น อีกหนึ่ง String Function ที่ใช้บ่อย

บางครั้งในการใช้งานบน Excel นั้น มีบางอย่างที่เราต้องการให้เปลี่ยนไป ในเซลล์นั้นๆ เราสามารถใช้ฟังก์ชั่น Substitute เข้าช่วยได้

ไวยากรณ์

=Substitute( text, old_text, new_text, nth_appearance )
=Substitute(เซลล์อ้างอิง,สิ่งที่ต้องการให้เปลี่ยนไป,ให้เปลี่ยนเป็น,ลำดับของตัวที่ต้องการให้เปลี่ยน)

ตัวอย่าง



=SUBSTITUTE(B3,"t",999,1) ต้องการเปลี่ยน t ในเซลล์ B3 ให้เป็น 999 , t ลำดับที่ 1

=SUBSTITUTE(B4,"t",999,2) ต้องการเปลี่ยน t ในเซลล์ B4 ให้เป็น 999 , t ลำดับที่ 2

=SUBSTITUTE(B6,"t",999,0) ต้องการเปลี่ยน t ในเซลล์ B6 ให้เป็น 999 , t ลำดับที่ 0
ลำดับที่ 0 ไม่มีในเซลล์ B6 จึงส่งกลับค่าเป็น #VALUE!

=SUBSTITUTE(B8,"t",999,3) ต้องการเปลี่ยน tในเซลล์ B8 ให้เป็น 999, t ลำดับที่ 3 ในเซลล์ B8 มี t เพียง 2 ตัว ดังนั้น จึงส่งกลับค่า เป็น ตัวเดิม


ลำดับที่ต้องการให้เปลี่ยนนี้สำคัญ ถ้าไม่ระบุไว้ว่าเป็นลำดับไหน หากในเซลล์นั้นๆ มีตัวที่ต้องการให้เปลี่ยนซ้ำกัน ตัวที่ซ้ำกันเหล่านั้นจะถูกเปลี่ยนให้เป็นตัวใหม่ทั้งหมด เช่น


ในเซลล์ B2 ไม่ได้ถูกระบุลำดับที่ต้องการให้เปลี่ยน ดังนั้น t จึงถูกเปลี่ยนเป็น 999 ทั้งหมด ( 2 ตัว)

ในเซลล์ B3 ไม่ได้ถูกระบุลำดับที่ต้องการเปลี่ยน ดังนั้น - จึงถูกเปลี่ยนเป็น ค่าว่างทั้งหมด (4 ตัว)


ข้อสังเกตุอีกประการ คือ ตัวอักษรภาษาอังกฤษ ตัวพิมพ์เล็กพิมพ์ใหญ่ มีผลต่อการใช้คำสั่งนี้ด้วย เช่น



ที่เซลล์ B4 ไม่มีตัว T (พิมพ์ใหญ่) ทำให้ไม่สามารถเปลี่ยนแปลงใดๆ จึงส่งกลับค่าออกมาเป็นตัวเดิม (Start)

ลองดูอีกซักชุด


ตัวอย่างน่าสนใจ เจอบ่อย


ตามด้วย

TRIM

TRIM จัดเป็นอีกหนึ่ง String Fuction ที่น่าสนใจ

TRIMใช้จัด อักขระหรือตัวเลขในเซลล์ใดๆ ให้เข้ามาชิดกันโดยเว้นช่วงตามการเว้นวรรค 1 วรรค

ไวยากรณ์

=TRIM(text)

=TRIM(เซลล์อ้างอิง)

ตัวอย่าง

สังเกตุว่า จะเว้นช่วงให้ห่างกัน 1 วรรค

LOWER UPPER และ PROPER

LOWER ,UPPER และ PROPER จัดเป็นฟังก์ชั่นในกลุ่ม String Functions ที่น่าสนใจ

LOWER ใช้บังคับตัวอักษรในเซลล์นั้นๆ (ภาษาอังกฤษ)ให้เป็นตัวพิมพ์เล็กทั้งหมด

UPPER ใช้บังคับตัวอักษรในเซลล์นั้นๆ (ภาษาอังกฤษ)ให้เป็นตัวพิมพ์ ใหญ่ทั้งหมด

PROPER ใช้บังคับตัวอักษรในเซลล์นั้นๆ (ภาษาอังกฤษ)ให้ขึ้นต้นด้วยตัวพิมพ์ใหญ่แล้วตามด้วยตัวพิมพ์เล็กทั้งหมด โดยจะเว้นช่วงตามการเว้นวรรค

ทั้ง 3 ฟังก์ชั่นมีไวยากรณ์ที่เหมือนกันดังนี้
=LOWER(TextToConvert)

=LOWER(เซลล์อ้างอิง)

=UPPER(TextToConvert)
=UPPER(เซลล์อ้างอิง)

=PROPER(TextToConvert)

=PROPER(เซลล์อ้างอิง)

ตัวอย่าง

IS FUNCTIONS ที่ควรรู้ (ISBLANK,ISTEXT,ISNUMBER)

ฟังก์ชั่นที่ขึ้นต้นด้วย IS ที่น่าสนใจ ที่เจอบ่อยๆ ที่ควรรู้ไว้ISBLANK ,ISTEXT,ISNUMBER

ISBLANK ใช้ตวรจสอบว่า เซลล์นั้นๆ เป็นเซลล์ว่างหรือไม่
ถ้าจริง จะเป็น TRUE ถ้าไม่จริงจะเป็น FALSE

ISTEXT ใช้ตรวจสอบว่า เซลล์นั้นๆเป็น Text หรือไม่
ถ้าจริง จะเป็น TRUE ถ้าไม่จริงจะเป็น FALSE

ISNUMBER ใช้ตรวจสอบว่า เซลล์นั้นๆ เป็น NUMBER หรือไม่
ถ้าจริง จะเป็น TRUE ถ้าไม่จริงจะเป็น FALSE

ไวยากรณ์ของฟังก์ชั่นทั้ง 3 จะเหมือนกันคือ
=ISBLANK(Value)
=ISBLANK( เซลล์อ้างอิง)

=ISTEXT(Value)
=ISTEXT(เซลล์อ้างอิง)

=ISNUMBER(Value)
=ISNUMBER(เซลล์อ้างอิง)



ตัวอย่าง





อีกซักตัวอย่าง



โดยปกติเรามักจะใช้ IS FUNCTIONS ร่วมกับฟังก์ชั่นอื่นๆ เช่น IF เป็นต้น

FIND และ SEARCH

FIND ใช้หาตำแหน่ง ของตัวเลข หรือตัวอักษร หรืออักขระใดๆในเซลล์นั้นๆ
ไวยากรณ์
=FIND(find_text,within_text,start_num)
=FIND(ตำแหน่งสิ่งที่ต้องการหา,เซลล์อ้างอิง,เริ่มนับจากตัวที่..)
ตัวอย่าง



กรณี เป็นตัวเลข จะพิมพ์ " " ปิดหน้า หลัง สิ่งที่ต้องการหาตำแหน่งในเซลล์นั้นๆ หรือไม่มีก็ได้




กรณี มีตัวที่ซ้ำกันในเซลล์ใดๆ ต้องระบุ ว่าให้เริ่มนับจากตำแหน่งใด
เช่น คำว่า Allocation ถ้าให้เริ่มนับ ที่ตัวที่ 1 จะได้ o อยู่ตำแหน่งที่ 4
ถ้าต้องการหาตำแหน่ง ของ o ตัวหลัง ต้องให้นับจากตำแหน่งที่เกิน 4 ไปแล้ว




FIND จะมีผลต่อการอ่านค่า ตัวพิมพ์ เล็ก พิมพ์ใหญ่ ในภาษาอังกฤษด้วย
เช่น Star ถ้าให้หาตำแหน่ง "s" (ตัวพิมพ์เล็ก) จะหาค่าไม่ได้
หรือ Desktop ถ้าให้หา ตำแหน่ง "K" ( ตัวพิมพ์ใหญ่) จะหาค่าไม่ได้




หากต้องการตัดเรื่อง ตัวพิมพ์เล็ก หรือ พิมพ์ใหญ่ ในภาษาอังกฤษ ให้เลี่ยงไปใช้ ฟังก์ชั่น SEARCH แทน

ฟังก์ชั่น SEARCH มีไวยากรณ์ เหมือนกันกับ FIND
=SEARCH(find_text,within_text,start_num)
=SEARCH(ตำแหน่งสิ่งที่ต้องการหา,เซลล์อ้างอิง,เริ่มนับจากตัวที่..)

ตัวอย่าง
จาก ตัวอย่างเดิม สังเกตุว่า การพิมพ์ ตัวพิมพ์เล็กหรือ พิมพ์ใหญ่ จะไม่มีผลต่อการหาตำแหน่ง
โดยการใช้ฟังก์ชั่น SEARCH




กรณีเป็นตัวเลข จะเลือกใช้ FIND หรือ SEARCH จะได้ผลลัพท์ไม่แตกต่างกัน
ดังนั้นการจะเลือกใช้ FIND หรือ SEARCH ก็เลือกเอาแล้วแต่ชอบ
ตัวอย่าง เช่น




ตัวอย่างการนำ FIND หรือ SEARCH ไปใช้

จากตารางด้านล่างนี้ ให้แยกคำออกมา จากการเว้นวรรค ให้ไปอยู่คนละคอลัมน์ กรณีนี้เจอบ่อยๆในเรื่องการ แยก ชื่อ และนามสกุลให้ออกจากกันเป็นต้น หรือ แยก User ID ออกจาก Name เป็นต้น


จะแสดงการใช้ ฟังก์ชั่นSEARCH หรือ FIND ร่วมกับบางฟังก์ชั่น ที่เราพอรู้กันแล้วมาประยุกต์ใช้ เช่น LEN ,LEFT,RIGHT ส่วนรายละเอียดของสูตรจะไม่อธิบายละเอียดในที่นี้ ลองหัดแกะสูตรดู (หัดอ่านล็อกเอง) หากยัง สับสนในวิธีการใช้ของแต่ละสูตร ขอให้กลับไปอ่านบทความเก่าๆ ที่เขียนไว้ก่อนหน้านี้


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



การแยกคำออกมาแบบนี้นั้น หากไม่ใช้สูตรดังที่ว่ามา อาจจะเลือกใช้สิ่งที่ โปรแกรม Excel มีไว้ให้แล้ว คือ Data--->Text to Columns..

โดยทำดังนี้

1.คลุมพื้นที่ข้อมูลทั้งหมด
2. Data --->Text to Columns…
3. Next
4. ติ๊กเครื่องหมายถูก ที่ช่องสี่เหลี่ยมเล็กๆหน้าคำว่า Space
5. Next
6.Finish

ลองทำดู (หนูทำได้)....

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

หลักการเดียวกันกับตัวอย่างที่ผ่านมา คือ หากไม่ต้องการใช้สูตร ก็สามารถใช้ Data --->Text to Columns

ได้เช่นกัน ดังนี้

1.คลุมพื้นที่ข้อมูลทั้งหมด
2. Data --->Text to Columns…
3. Next
4. ติ๊กเครื่องหมายถูก ที่ช่องสี่เหลี่ยมเล็กๆหน้าคำว่า Other
แล้วพิมพ์ x ลงในช่อง สี่เหลี่ยม (เงื่อนไขที่ต้องการให้แยก)
5. Next
6.Finish

กรณีที่ใช้สูตรแยก

จะอธิบาย แถวที่ 9 ในภาพ เพียงแถวเดียว เป็นหลัก ส่วนแถวอื่นๆนั้น สามารถ Copy สูตร จากแถวที่ 9 ลงไปได้เลย

ให้มองหาเงื่อนไข หรือสะพาน ที่จะใช้แยกออกมา ในที่นี้ "x" 2 ตัว คือสะพานที่เราต้องใช้

ดังนั้นจึงต้องหา ตำแหน่งของสะพาน "x" ทั้ง 2 ตัวออกมา โดยการใช้ ฟังก์ชั่น SEARCH

x ตัวที่ หนึ่ง =SEARCH("x",B9,1) ต้องการหาตำแหน่งของ x ในเซลล์ B9 โดยนับจากตัวที่ หนึ่ง

x ตัวที่ สอง =SEARCH("x",B9,C9+1) x ตัวที่ สอง ต้องเริ่มนับจากตำแหน่งหลังจาก ตำแหน่งของ x ตัวที่ หนึ่ง ดังนั้น จึงต้อง ใช้ตำแหน่งของ x ตัวที่ หนึ่ง บวก 1

หาความกว้าง =LEFT(B9,C9-1)+0 นับจากทางซ้ายมาถึงตำแหน่งก่อน x ตัวที่ หนึ่ง ดังนั้นจึงต้องเอา ตำแหน่งของ x ตัวที่ หนึ่ง ลบ 1

หาความยาว =MID(B9,C9+1,D9-C9-1)+0 นับจากตำแหน่งหลัง x ตัวที่ หนึ่งดังนั้นจึงต้องเอา ตำแหน่งของ x ตัวที่ หนึ่ง บวก 1 นับมากี่ตัว ....? นับมาถึงก่อนตำแหน่งของ x ตัวที่ สอง ดังนั้นจึงต้อง เอา ตำแหน่งของ x ตัวที่ สอง ลบ ตำแหน่งของ x ตัวที่ หนึ่ง และ ลบด้วย 1 (หลักการ นับจำนวนที่อยู่ระหว่างหัว ท้าย ต้องเอา ท้าย ลบ หัว ลบ 1.. เช่น จำนวนนับที่อยู่ระหว่าง 3 ถึง 8 ก็คือ 8-3-1 = 4 ตัวนั่นเอง ไม่นับ 3 และ 8)

หาความสูง =RIGHT(B9,LEN(B9)-D9)+0 นับจากด้านขวาของเซลล์ จำนวนที่นับคือที่เหลือจาก จำนวนทั้งหมด ลบด้วยตำแหน่งของ x ตัวที่ สอง

ในสูตร LEFT ,RIGHT,MID ข้างบนนั้น จะ บวกด้วย 0ไว้ด้วย

เนื่องจาก Excel จะมองเห็นตัวเลขมาจาก สูตร LEFT,RIGHT,MID เป็น Text เสมอ การ บวกด้วย 0 นั้นจะทำให้เปลี่ยนสัญชาติจาก Text เป็น Number โดยอัตโนมัติ

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

ที่แสดงไว้ ก็เพียงเพื่อเป็นแนวทางให้ทราบว่า มันถูกมองเป็น Text และเพื่อความมั่นใจว่า สิ่งที่ได้ออกมาจากสูตร เป็น Number ก็ให้บวกด้วย 0 ต่อท้ายสูตร หรือจะคูณด้วย 1 ก็ได้ผลลัพท์เช่นเดียวกัน หรือจะใช้เครื่องหมายลบ(-)ติดกันสองอันหลังเครื่องหมายเท่ากับในสูตรก็ยังได้

ดังนี้

=--LEFT(B9,C9-1)
=--MID(B9,C9+1,D9-C9-1)
=--RIGHT(B9,LEN(B9)-D9)

ตัวอย่าง การนำไปประยุกต์ใช้

จะแสดงการแยกสูตรและการวบสูตรเข้าไว้ด้วยกัน

LEN

ฟังก์ชั่น LEN ใช้นับจำนวน ตัวเลขหรือตัวอักษร หรือค่าอื่นๆ รวมทั้ง การเว้นวรรคในเซลล์นั้นๆด้วย
LEN จะนับค่าว่าง หรือเซลล์ว่าง เป็น 0

ไวยากรณ์ =LEN(Text)
=LEN(เซลล์อ้างอิง)

ตัวอย่าง


โดยปกติ เรามักจะไม่ใช้ LEN แบบโดดๆ
มักจะใช้ร่วมกับฟังก์ชั่นอื่นๆ ฟังก์ชั่นหลักๆที่ใช้ร่วมกัน เช่น
LEFT ,RIGHT , MID,COUNT,COUNTIF ,SUMIF ,FIND ,SEARCH ,หรืออื่นๆ แล้วแต่กรณี ที่จะนำ LEN ไปประยุกต์ใช้