Search

วิศวกรรีพอร์ต

Make your reports better with shorter time

Month

January 2015

ชำแหละ VLOOKUP จากข้างหลังทะลุถึงหัวใจ ภาค 3 (VLOOKUP vs IF)

32122880_s

มาถึงภาคสุดท้ายของไตรภาค VLOOKUP กันแล้วนะครับ

จากสองภาคแรก ชำแหละ VLOOKUP จากข้างหลังทะลุถึงหัวใจ ภาค 1 (VLOOKUP คลุกเคล้า COLUMNS หรือ MATCH) และ ชำแหละ VLOOKUP จากข้างหลังทะลุถึงหัวใจ ภาค 2 (VLOOKUP vs INDEX) เราคุยกันเฉพาะ VLOOKUP ที่ลงท้ายด้วย FALSE หรือ 0 เท่านั้น

VLOOKUP แบบนี้มีชื่อทางการว่า VLOOKUP แบบ Exact Match

ยังมี VLOOKUP อีกแบบนึง ที่ลงท้ายด้วย TRUE หรือ 1 (หรือไม่ใส่องค์ประกอบลำดับที่ 4 ในสูตร)

VLOOKUP แบบนี้มีชื่อทางการว่า VLOOKUP แบบ Approximate Match

VLOOKUP แบบ Exact Match ต่างกับ Approximate Match ยังไงน่ะหรือครับ?

ถ้าเป็นแบบ Exact Match ค่าที่ค้นหาต้องเหมือนค่าในตารางอ้างอิงแบบเป๊ะๆ ถ้าไม่เป๊ะ คำตอบที่ได้คือ #N/A

ถ้าเป็นแบบ Approximate Match ค่าที่ค้นหาเหมือนค่าในตารางอ้างอิงแบบ “ครือๆ” ก็พอ ถ้าไม่เหมือนเป๊ะ มันจะดึงค่าใกล้เคียงมาให้เอง

เหมือนแบบ “ครือๆ” คืออะไรน่ะหรือครับ?

ขออธิบายด้วยตัวอย่างนี้ครับ น่าจะเห็นภาพได้ง่ายที่สุด

สมมติว่า เราเป็นอาจารย์ หลังจากตะลุยตรวจข้อสอบนักศึกษาจนหมดแล้ว ก็ถึงเวลาให้เกรด

เกณฑ์การให้เกรดเป็นแบบนี้

Criteria_Grade

ปัญหาคือ มีนักศึกษาตั้ง 500 คน ถ้ามานั่งใส่เกรดทีละคน มีหวังทำถึงตีสองแน่นอน T_T

Data_NoGrade.png

ถ้าใช้ IF ช่วย ก็ต้องใช้ IF ซ้อนกัน 8 ชั้น !

8 ชั้น !!

ซ้อนกันเยอะขนาดนี้ แค่คิดก็เศร้าแล้ว

ถึงเขียนได้ โอกาสเขียนผิดก็สูงมาก

ยิ่งถ้าเกณฑ์การให้คะแนนเปลี่ยน ต้องมานั่งเปลี่ยนสูตรใน IF ทุกชั้น

แบบนี้ไม่สนุกเลย T_T

แต่เดี๋ยวก่อน !

เรื่องนี้จะไม่เป็นปัญหาเลย ถ้าใช้ VLOOKUP แบบ Approximate Match

ใช้ยังไงน่ะหรือครับ?

จากเคสนี้ เราเก็บค่าคะแนนสอบไว้ที่เซลล์ E4 แล้วต้องการให้เซลล์ F4 แสดงผลว่า คะแนนนี้ (77) ได้เกรดเท่าไร

แค่เขียนสูตรในเซลล์ F4 ว่า

=VLOOKUP( E4, $A$4:$C$12, 3 , 1 )

ได้คำตอบ B+

จบ!

ต่อให้มีนักศึกษา 5,000 คน แค่ก็อปปี้สูตรลงมาด้านล่าง แว๊บเดียวเสร็จ ^__^

VLOOKUP แบบนี้ใช้งานยังไงน่ะหรือครับ?

ลองเทียบโครงสร้างของ VLOOKUP กันแบบตัวๆนะครับ

โครงสร้างปกติของ VLOOKUP คือ

=VLOOKUP ( Lookup_value , Table_array , Col_index_num , Range_lookup )

Lookup_value คือ ต้องการดึงค่าของอะไร ในที่นี้คือ E4 (คะแนน 77)

Table_array คือ ตารางอ้างอิง ในที่นี้คือเกณฑ์ที่เราใช้สำหรับการให้เกรด หรือเขียนไว้ในช่วงเซลล์ $A$4:$C$12 (ควรเขียนแล้วล็อคด้วยดอลลาร์ไซน์ ($) เพื่อป้องกันการเลื่อนเวลาลากสูตร)

Col_index_num ค่าที่ต้องการดึง เป็นคอลัมน์ลำดับที่เท่าไรของตารางอ้างอิง ในที่นี้คือ 3 เพราะเราต้องการดึงเกรด เกรดอยู่ในคอลัมน์ C ซึ่งถือเป็นคอลัมน์ลำดับที่ 3 ของตารางอ้างอิง

Range_lookup คือ ระบุว่าให้ค้นหาแบบใด ระหว่าง Exact Match หรือ Approximate Match

  • ถ้าเป็น Exact Match ให้ใส่ FALSE หรือ 0
  • ถ้าเป็น Approximate Match ให้ใส่ TRUE หรือ 1 หรือปล่อยว่างไปเลยก็ได้ (ถ้าเราไม่ใส่ เอ็กเซลจะทึกทักว่าเราต้องการค้นหาแบบ Approximate Match)

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

เช่น 77 อยู่ในช่วงของ 75 – 79

Criteria_Explanation.png

ไม่ใช่ตัวเลขที่ตรงกับ 75 แบบเป๊ะๆ แต่ก็สามารถดึงคำตอบที่ถูกต้อง (B+) ออกมาได้

เพราะเราใช้ VLOOKUP แบบ “ครือๆ” หรือแบบ Approximate Match นั่นเอง

แล้วถ้าข้อมูลตรงกับค่าในคอลัมน์ด้านซ้ายแบบเป๊ะๆล่ะ?

ถ้าตรงแบบเป๊ะๆ ก็จะดึงค่าได้ตามนั้นครับ

เช่น คะแนน 60 ได้เกรด C

VLOOKUP_SameNumber.png

จริงๆแล้ว การทำงานของ VLOOKUP แบบ Approximate Match นี้ เอ็กเซลต้องการแค่คอลัมน์อ้างอิง และคอลัมน์ที่ต้องการดึงค่า เพียง 2 คอลัมน์เท่านั้น

เช่นเคสนี้ เอ็กเซลต้องการแค่คอลัมน์ A (ที่เก็บคะแนนขอบบนของแต่ละช่วง) และ คอลัมน์ C เท่านั้น

คอลัมน์ B (ที่เก็บคะแนนขอบบนของแต่ละช่วง) จะมีหรือไม่มีก็ได้

Criteria_FocusOnly2Column

หรือจะสร้างตารางอ้างอิงให้มีแค่ 2 คอลัมน์ แบบนี้ก็ได้

Criteria_Essential.png

ได้ผลลัพธ์เหมือนกัน (อย่าลืมเปลี่ยน Col_index_num จาก 3 ให้เป็น 2 ด้วยนะครับ เพราะเกรดถูกเลื่อนมาอยู่ในคอลัมน์ B ซึ่งถือเป็นคอลัมน์ลำดับที่ 2 ของตารางอ้างอิง)

แต่การเขียนตารางอ้างอิงแบบนี้ (ระบุเฉพาะขอบล่าง) อาจยากต่อการสื่อสาร และมีโอกาสงง การระบุทั้งขอบบนและขอบล่าง ให้ความเข้าใจที่ดีกว่า

Criteria_Grade

ถ้าศึกษาในเชิงลึก จะพบว่า VLOOKUP แบบ Approximate Match ทำงานเร็วกว่าแบบ Exact Match หลายเท่า !!

ยิ่งถ้าใช้กับข้อมูลเยอะๆ จะพบว่าเร็วกว่ามากๆๆๆ

VLOOKUP แบบ Approximate Match นี่สุดยอดเลยใช่ไหมครับ ^__^

งั้นเราใช้ VLOOKUP แบบ Approximate Match แทน Exact Match เลยได้ไหม?

คำตอบคือ ได้แบบมีเงื่อนไข ครับ

เงื่อนไขที่ว่า คือ

ต้องเรียงลำดับข้อมูลในตารางอ้างอิง (คอลัมน์ซ้ายสุด) จากน้อยไปมาก เท่านั้น !

ห้ามเรียงจากมากไปน้อย หรือ ห้ามเรียงลำดับแบบตามใจฉัน ไม่งั้นค่าที่ดึงมามีโอกาสผิดสูงมาก

ถ้าข้อมูลเป็นตัวเลข (เช่นเคสนี้) การเรียงข้อมูลในตารางอ้างอิงจากน้อยไปมาก คงไม่ใช่ปัญหา

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

(เทคนิคการเขียนสูตรเพื่อป้องกันความผิดพลาดกรณีที่ข้อมูลเป็นตัวหนังสือ คุณเทพเอ็กเซล (inwexcel, ศิระ เอกบุตร) เคยเผยเคล็ดลับไว้ในบทความนี้ ขอบคุณคุณเทพเอ็กเซลสำหรับเทคนิคดีๆครับ)

เพราะฉะนั้น การศึกษา VLOOKUP ให้เข้าใจแบบถ่องแท้ทั้งแบบ Exact Match และ Approximate Match จึงเป็นสิ่งจำเป็น

ยิ่งถ้าใช้ INDEX+MATCH จนคล่องแล้วล่ะก็

วรยุทธ์เอ็กเซลของคุณ คงอยู่ไม่ตำกว่าขั้นที่ 5 ของ 10 ขั้นวิทยายุทธ Excel แน่นอน

ใครที่ผ่านขั้นนี้มาแล้ว อย่าลืมศึกษาฟังก์ชัน OFFSET และ INDIRECT เพื่อลบข้อจำกัดเคสที่ซับซ้อนนะครับ

แล้วคุณจะรู้ว่า ความ “มันส์” มันเป็นยังไง ^^

เช่นเคยเหมือนทุกครั้ง ไฟล์ตัวอย่างของบทความนี้ สามารถดาวน์โหลดจากลิงค์ด้านล่างได้เลยครับ

VLOOKUP_ApproximateMatch_170315

.

หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่

อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^_^

ชำแหละ VLOOKUP จากข้างหลังทะลุถึงหัวใจ ภาค 2 (VLOOKUP vs INDEX)

จากบทความที่แล้ว ชำแหละ VLOOKUP จากข้างหลังทะลุถึงหัวใจ ภาค 1 (VLOOKUP คลุกเคล้า COLUMNS หรือ MATCH) ผมทิ้งท้ายว่า VLOOKUP ยังมีข้อจำกัดอีกข้อหนึ่ง

และด้วยข้อจำกัดนี้เอง ทำให้หลายคนหันไปใช้ฟังก์ชันอื่นแทน

ข้อจำกัดที่ว่าคือ

เซลล์อ้างอิงต้องอยู่คอลัมน์ซ้ายสุดเสมอ !!

Continue reading “ชำแหละ VLOOKUP จากข้างหลังทะลุถึงหัวใจ ภาค 2 (VLOOKUP vs INDEX)”

ชำแหละ VLOOKUP จากข้างหลังทะลุถึงหัวใจ ภาค 1 (VLOOKUP คลุกเคล้า COLUMNS หรือ MATCH)

คนทำงานส่วนใหญ่ น่าจะเคยใช้ฟังก์ชันใน Excel กันมาไม่น้อย

หนึ่งในฟังก์ชันยอดฮิตที่ทุกคนต้องใช้ ก็คือฟังก์ชันที่มีชื่อ 3 พยางค์ว่า

VLOOKUP

VLOOKUP เป็นฟังก์ชันที่มีประโยชน์มาก เรียกได้ว่าเป็นฟังก์ชันแรกในตระกูล Lookup & Reference (เช่น HLOOKUP, MATCH, INDEX, INDIRECT) ที่เราฝึกใช้กัน

แต่… VLOOKUP ก็เป็นฟังก์ชันที่มีข้อด้อยพอตัว ซึ่งบางคนรู้ แต่บางคนไม่รู้!

แล้วเจ้าข้อด้อยทีว่ามันคืออะไรล่ะ?

วันนี้เรามาชำแหละ VLOOKUP ให้ถึงกึ๋นทะลวงทรวงกันเลยครับ ^__^

Continue reading “ชำแหละ VLOOKUP จากข้างหลังทะลุถึงหัวใจ ภาค 1 (VLOOKUP คลุกเคล้า COLUMNS หรือ MATCH)”

สร้างสูตรคำนวณจุดคุ้มทุน (Payback Period) แบบอัตโนมัติด้วย Excel

13564599_s

คุณเคยมีประสบการณ์ทำโปรเจ็คต์ไหมครับ?

เวลาทำโปรเจ็คต์ ขั้นตอนแรกคือ วิเคราะห์ว่าควรทำโปรเจ็คต์หรือไม่

หรือก็คือการทำ Project Feasibility นั่นเอง

ตัวเลขยอดฮิตที่ผู้บริหารมักใช้เป็นเกณฑ์การตัดสินใจคือ NPV, IRR และ จุดคุ้มทุน (Payback Period) Continue reading “สร้างสูตรคำนวณจุดคุ้มทุน (Payback Period) แบบอัตโนมัติด้วย Excel”

10 ขั้นวิทยายุทธ Excel

เคยตั้งคำถามกับตัวเองไหมครับว่า

“ความรู้เอ็กเซลของเราอยู่ระดับใด พอจะไปวัดไปวากับชาวบ้านเค้าได้ไหม?”

“เราว่าเราเทพในบริษัทแล้วนะ แต่จะสู้คนข้างนอกได้มั๊ยหนอ?”

เหล่านี้อาจเป็นปัญหาที่หลายคนสงสัย แต่ยากจะหาใครตอบ

งั้นผมขออุกอาจตอบ โดยแบ่งระดับความรู้ของเอ็กเซลออกเป็น 10 ขั้น หรือ เรียกสั้นๆว่า

10 ขั้นวิทยายุทธ์เอ็กเซล

10StepsExcel_151117

Continue reading “10 ขั้นวิทยายุทธ Excel”

Why Reporting Engineer?

หลายคนอาจสงสัยว่าบล็อก “วิศวกรรีพอร์ต” เกี่ยวกับอะไร หรือทำไมผมถึงเขียนบล็อกนี้ขึ้นมา

Logo_ReportingEngineer_150227

จุดประสงค์หลักของบล็อกนี้คือ

แบ่งปันเทคนิคการทำรีพอร์ต ตารางคำนวณ และพรีเซ็นเตชัน

โดยประยุกต์ใช้เครื่องมือยอดฮิตอย่าง ไมโครซอฟต์เอ็กเซล (Microsoft Excel) และไมโครซอฟต์พาวเวอร์พอยต์ (PowerPoint)

เพื่อหวังว่าจะเป็นคนสุดท้ายของประเทศไทยที่ต้องเสียเวลามากมายกับเรื่องนี้

Continue reading “Why Reporting Engineer?”

แฉ! เทคนิคสร้างกราฟวิเคราะห์แบบมือโปร [Waterfall Chart Analysis]

หลายคนอาจเคยเห็นบริษัทที่ปรึกษาชื่อดัง นำเสนอข้อมูลด้วยกราฟเท่ๆ แนวๆ และคิดในใจว่า

“ต้องใช้โปรแกรมไฮโซถึงจะสร้างกราฟพวกนี้ได้หรือเปล่าหนอ?”

ถูกส่วนหนึ่งครับ กราฟพวกนี้มักถูกสร้างจากโปรแกรมที่เราไม่ค่อยรู้จัก เช่น Think-Cell, Tableau

แต่ไม่ได้หมายความว่าเอ็กเซลจะสร้างไม่ได้เสมอไป Continue reading “แฉ! เทคนิคสร้างกราฟวิเคราะห์แบบมือโปร [Waterfall Chart Analysis]”

Blog at WordPress.com.

Up ↑

%d bloggers like this: