Articles

สูตร VLookup และสูตร Match มีจุดอ่อนตรงที่สูตรเหล่านี้จะทำงานกับข้อมูลที่ไม่ซ้ำเท่านั้น หากนำไปใช้กับตารางที่มีข้อมูลซ้ำจะให้คำตอบของข้อมูลรายการแรกที่ซ้ำกัน ส่วนคำสั่ง Data > Advanced หรือ Filter จะทำงานต่อเมื่อถูกสั่งใหม่เป็นครั้งๆไป ดังนั้นหากต้องการแสดงรายละเอียดของข้อมูลที่ซ้ำกันในทันทีที่มีการเปลี่ยนแปลง ต้องหันมารู้จักกับสูตรที่เรียกกันว่าสูตร Multiple Match

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

 

 

ตารางด้านซ้ายมือของภาพนี้เป็นตารางฐานข้อมูลซึ่งมีค่าซ้ำกันบันทึกไว้ เช่น ข้อมูลของรหัส a001 ถูกบันทึกไว้ในรายการที่ 1, 6, 9, 11, และ 15 โดยตัวเลขตำแหน่งรายการของรหัส a001 เหล่านี้สามารถหาได้ด้วยสูตร Multiple Match {=SMALL(IF(ID=G3,Num),Num)} ซึ่งสร้างไว้ในเซลล์ L3:L12 แบบ Array

ตัวอย่างนี้ใช้การตั้งชื่อ Range Name ว่า Num, ID, Name, และ Amount ให้กับพื้นที่ข้อมูล B3:B20, C3:C20, D3:D20, และ E3:E20 ตามลำดับ ส่วนเซลล์ G3 เป็นเซลล์รับค่ารหัสที่ต้องการค้นหาตำแหน่งรายการ

เพื่อแสดงให้เห็นที่ไปที่มาของสูตร {=SMALL(IF(ID=G3,Num),Num)} ว่าเกิดจากการคำนวณหลายขั้นตอนและแต่ละขั้นทำหน้าที่หาค่าอะไรบ้าง ขออธิบายสูตรที่สร้างไว้ใน Step1 - Step3 ดังนี้ (เวลาใช้งานจริงไม่จำเป็นต้องสร้างสูตรเหล่านี้แต่อย่างใด)

Step1 : เริ่มจากเลือกเซลล์ I3:I20 พร้อมกันแล้วสร้างสูตร =ID=G3 แล้วกดปุ่ม Ctrl+Shift+Enter จะมีวงเล็บปีกกาปิดหัวท้ายสูตรกลายเป็น {=ID=G3} คืนค่าออกมาเป็น True คู่ขนานตรงกับตำแหน่งรายการที่มีรหัส a001

Step2 : ปรับตำแหน่งที่เป็น True จาก Step1 ให้เป็นเลขที่รายการ Num โดยเลือกเซลล์ J3:J20 พร้อมกันแล้วสร้างสูตร =IF(I3:I20,Num) แล้วกดปุ่ม Ctrl+Shift+Enter จะมีวงเล็บปีกกาปิดหัวท้ายสูตรกลายเป็น {=IF(I3:I20,Num)} จะพบว่าตำแหน่งที่เคยเป็นค่า True กลายเป็นเลขตำแหน่งรายการ 1, 6, 9, 11, 15 ขึ้นมาให้เห็นแทน

Step3 : นำตัวเลขตำแหน่งรายการจาก Step2 มาเรียงจากน้อยไปมากโดยนำไปซ้อนในสูตร Small โดยเลือกเซลล์ K3:K20 พร้อมกันแล้วสร้างสูตร =SMALL(J3:J20,Num) แล้วกดปุ่ม Ctrl+Shift+Enter จะมีวงเล็บปีกกาปิดหัวท้ายสูตรกลายเป็น {=SMALL(J3:J20,Num)}

เมื่อนำสูตรจาก Step1 – Step3 มาซ้อนกันจะกลายเป็นสูตร Multiple Match ในเซลล์ L3:L12 {=SMALL(IF(ID=G3,Num),Num)} ทั้งนี้โปรดสังเกตว่าไม่จำเป็นต้องสร้างสูตรลงไปในเซลล์ L3:L20 ที่ยาวลงมาถึง row 20 เช่นที่ใช้กับสูตรใน Step1 – Step3 แต่อย่างใด แต่ให้เลือกจำนวน row จากเซลล์ L3:L12 หรือ 10 row เท่าที่คิดว่ารหัสจะมีโอกาสซ้ำกันสูงสุดกี่ครั้งก็พอ จากนั้นให้ใช้สูตร Index ดึงข้อมูลตามลำดับรายการที่หาได้มาแสดงต่อไป

 

 

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

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

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

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

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

กว่าจะมาเป็นคุณหมอได้ ยากกว่าการมาเป็นอาจารย์สอน Excel หลายเท่านัก กว่าจะสำเร็จการศึกษาได้รับปริญญาแพทย์ศาสตร์บัณฑิตก็ต้องเรียนมาอย่างหนักและผ่านการสอบแล้วสอบอีกทั้งภาคทฤษฎีปฏิบัติ ส่วนใครที่อยากจะเป็นอาจารย์สอน Excel ก็ทำได้ง่ายดาย แม้ผ่านข้อสอบของบริษัทไมโครซอฟท์หรือได้รับยกย่องให้รางวัลเป็น Microsoft Excel Most Valuable Professional (MVP) แล้วก็ตาม มิได้เป็นหลักฐานยืนยันว่าจะมาเป็นอาจารย์สอน Excel ที่ให้ความรู้ได้ถูกต้องไปทุกเรื่อง คนที่ขึ้นชื่อว่าเป็นอาจารย์สอน Excel บางคนใช้วิธีสร้างนามแฝงขึ้นมาเพื่อใช้ยกย่องฝีมือตัวเองเพื่อใช้ชื่อหลอกๆเรียกขานตัวเองให้ได้ขึ้นชื่อว่าอาจารย์ก็มีมาแล้ว ส่วนหลักเกณฑ์ที่ใช้ให้รางวัลเป็น MVP ก็ไม่เปิดเผยชัดเจนว่าดูอะไรบ้าง ส่งผลทำให้ MVP หลายคนได้เป็น MVP แบบน่าสงสัยเพราะมีคุณสมบัติน้อยกว่าคนที่ไม่ได้เป็น MVP เสียอีก

ความรู้ Excel ที่แนะนำกันบนสื่อออนไลน์ ถ้าเปรียบเทียบกับยารักษาโรค ก็ใช่ว่าจะตรงกับโรคเสมอไป วิธีใช้ Excel ที่เผยแพร่กันแม้ใช้แก้ไขปัญหาเฉพาะหน้าได้ชะงัด ทานยาปุ้ป หายโรคปั้ป แต่พอนานไปอาจกลายเป็นพิษ เพราะคุณหมอ Excel เล่นใช้วิธีจ่ายยาแรงมาให้ ผู้ใช้ Excel ส่วนใหญ่พอรับยามา ก็ยังต้องลองผิดลองถูกกันเอง บ้างก็ไปซื้อยามาทานเอง หรือไปหาหมอตี๋ที่จ่ายยาเพร็ดนิโซโลนเป็นแค่ขนานเดียว (ยาเพร็ดนิโซโลนนี่ทานเข้าไปแล้วจะรู้สึกแข็งแรงกระปี้กระเป่าขึ้นมาทันที มันจะเข้าไปกระตุ้นระบบภูมิคุ้มกันให้ทำงานเต็มที่ แต่พอหมดฤทธิ์ยาก็หมดแรง กลายเป็นคนที่ขาดภูมิคุ้มกัน ต้องพึ่งยาไปตลอด ... ถ้าจำไม่ผิด)

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

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

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

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

ผลจากการวิจัยพบว่า 94% ของจำนวนแฟ้มสเปรดชีตที่ใชในบริษัททั่วไปและ 5% ของสูตรที่สร้างไว้มีข้อผิดพลาด ซึ่งข้อผิดพลาดที่พบบ่อยที่สุดเกิดจากการสร้างสูตรแบบที่มีในสิ่งที่ไม่ควรมี และไม่มีในสิ่งที่ควรมี

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

=2*3

=VLOOKUP(A2,B5:E60,2)

=SUM(INDIRECT(“A1:B”&COUNTA(C1:C100))

สูตรแรก =2*3 เป็นสูตรที่เรียกว่า Hard Code เพราะใส่ตัวเลขที่เป็นค่าคงที่ลงไปในสูตรและคำนวณให้คำตอบเท่ากับ 6 ไปตลอด หากวันหนึ่งต้องการแก้สูตรใหม่ให้เป็นตัวเลขอื่นมาคูณกัน ก็ต้องไล่หาเซลล์ที่มีสูตรเก่านี้ให้พบก่อนแล้วจึงเหลี่ยนค่าเป็นตัวเลขใหม่แทนลงไป แต่ถ้าหลงลืมไม่ได้แก้ไขก็จะยังคงได้ผลเท่าเดิมไปตลอด

สูตรที่สอง =VLOOKUP(A2,B5:E60,2) มีตัวเลข 2 ที่ใช้ระบุตำแหน่ง Column ของคำตอบที่ต้องการ หากต้องการระบุตัวเลข 2 ไว้ ก็ต้องมีเงื่อนไขสำคัญว่า ห้ามมีการสลับหรือ Insert Column แทรกในพื้นที่ตาราง B5:E60

สูตรที่สาม =SUM(INDIRECT(“A1:B”&COUNTA(C1:C100)) ได้กำหนดตำแหน่งตารางที่เป็นค่าคงที่ “A1:B” เอาไว้ ดังนั้นจึงต้องห้ามโยกย้ายตำแหน่งเซลล์ใดๆใน Column A กับ B อย่างเด็ดขาด และยังห้ามใส่ค่าใดๆที่ไม่เกี่ยวข้องลงไปในพื้นที่ตารางช่วง C1:C100

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

จากนี้ไปช่วยกันตวจสอบดูให้ดีๆก่อนว่า ใครแน่จริง

Go to top