สอน 2 เทคนิคการทำ VLOOKUP หลายค่า จากคำค้นหาเดียว 1

สอน 2 เทคนิคการทำ VLOOKUP หลายค่า จากคำค้นหาเดียว

จะทำยังไงถึงจะทำ VLOOKUP หลายบรรทัด จาก Lookup_Value เดียวได้นะ?? นี่คือคำถามที่เกิดขึ้นบ่อย เพราะปกติแล้ว เวลาเราใช้ VLOOKUP หรือจะ INDEX MATCH ก็แล้วแต่ หาก Lookup_Value ซ้ำกัน มันจะได้ผลลัพธ์เป็นตัวบนสุดเสมอ บทความนี้ผมจะมาบอกวิธีทำ VLOOKUP เพื่อแสดงผลลัพธ์หลายบรรทัดให้เอง และจะทำให้ดูหลายแบบด้วยครับ!! โจทย์คือแบบนี้ครับ เราต้องการเลือกสินค้า แล้วให้แสดง Order ID ทั้งหมดของสินค้านั้นออกมา (ถ้าเราเอา Order ID ออกมาได้แล้ว อย่างอื่นก็คงไม่ยากแล้วจริงมั้ยครับ?) วิธีที่ 1 : ใช้สูตรเพื่อทำ vlookup หลายบรรทัด ใช้ VLOOKUP + Helper Column ในเมื่อ ปัญหาคือมี lookup_value ซ้ำกันหลายตัว ใน table_array…
VLOOKUP โหมด Approximate Match 2

VLOOKUP โหมด Approximate Match

อย่างที่เคยบอกไปแล้วว่า VLOOKUP มี 2 แบบ ซึ่งแบบพื้นฐานคือแบบ Exact Match ซึ่งจะ Map ข้อมูลได้ จะต้องเจอคำค้นหาอยู่ในคอลัมน์แรกของตารางอ้างอิงแบบเป๊ะๆ เท่านั้น แต่ในหลายๆ สถานการณ์ VLOOKUP แบบ Exact Match นั้นไม่สามารถตอบโจทย์ได้ เช่น กรณีที่มีเงื่อนไขในการใช้ตารางอ้างอิงเป็นช่วง เช่น การจัดเกรด การให้ commission เป็นต้น ตัวอย่างเช่น การจัดเกรดจากคะแนนแบบนี้ จะเห็นว่า ถ้าจะทำตารางเพื่อรองรับการหาแบบ Exact Match จะต้องสร้างตารางให้มี 100 ค่าเลย เพื่อรองรับแต่ละคะแนน (จริงๆ ต้องทำ 101 ค่าด้วย เพราะต้องรองรับตั้งแต่ 0-100 คะแนน) ยิ่งที่ค่าตัวเลขคะแนนเป็นทศนิืิยมได้นี่ ไม่มีทางสร้างตารางแบบ Exact Match รองรับไว้ล่วงหน้าได้เลย แต่ถ้าเราเปลี่ยนวิธีมาใช้…
ฟังก์ชันปัดตัวเลข ปัดเศษทศนิยม 3

ฟังก์ชันปัดตัวเลข ปัดเศษทศนิยม

เวลาเราใช้ Excel คำนวณตัวเลข บ่อยครั้งผลลัพธ์จะออกมาเป็นตัวเลขที่หน้าตาน่าเกลียดหน่อยๆ เพราะมีทศนิยมเยอะเกินไป เช่น 1234.5465 ซึ่งในชีวิตจริงเราไม่ค่อยอยากเห็นทศนิยมเยอะขนาดนี้อยู่แล้ว ดังนั้นฟังก์ชันที่สามารถปัดตัวเลขได้จึงเป็นสิ่งจำเป็น ROUND, ROUNDUP, ROUNDDOWN ฟังก์ชันที่ใช้บ่อยที่สุดในการปัดตัวเลขก็คือ ฟังก์ชันกลุ่ม ROUND, ROUNDUP, ROUNDDOWN นั่นเอง ซึ่งผมขอพูดถึงตัวที่เข้าใจง่ายที่สุดก่อน นั่นก็คือ ROUNDDOWN ROUNDDOWN ฟังก์ชันนี้ มีวิธีใช้งาน คือ =ROUNDDOWN(number,num_digits) หรือแปลได้ว่า =ROUNDDOWN(ตัวเลขที่จะปัด,จำนวนทศนิยมที่จะให้คงไว้) เช่น =ROUNDDOWN(1234.5465 , 2) จะได้ 1234.54 เพราะต้องการทศนิยม 2 ตำแหน่ง และการใช้ ROUNDDOWN แปลว่า ตำแหน่งที่เหลือให้ตัดทิ้งได้เลย ไม่ต้องสนใจว่าจะมีค่ามากหรือน้อยแค่ไหน เช่น =ROUNDDOWN(1234.5465 , 1) จะได้ 1234.5 เพราะต้องการทศนิยม 1 ตำแหน่ง และการใช้…
ฟังก์ชันสรุปข้อมูลพื้นฐาน 4

ฟังก์ชันสรุปข้อมูลพื้นฐาน

SUM : ฟังก์ชันแรกที่คุณรู้จัก...รู้จักดีรึยัง? หากถามว่า "อะไรคือฟังก์ชันใน Excel อันแรกที่คุณรู้จัก?" ผมคิดว่าหลายคนน่าจะตอบว่าฟังก์ชัน SUM แน่นอน แต่หารู้ไม่ว่า มันอาจมีบางเรื่องที่คุณอาจไม่รู้เกี่ยวกับฟังก์ชัน SUM ก็ได้ เช่น จากรูปนี้ ผม SUM ได้ผลลัพธ์ 6000 แทนที่จะเป็น 9000 สาเหตุเพราะว่า SUM จะทำการสรุปข้อมูล โดยหาผลรวมจาก Data ที่เป็นข้อมูลประเภทตัวเลข เท่านั้น!! (ถ้าใครไม่ระวัง เจอข้อมูลที่เน่าๆ มี Text ปนมาหน่อย อาจได้ผลลัพธ์ที่ผิดแบบไม่รู้ตัวก็ได้ครับ) ฟังก์ชันสรุปผลแบบพื้นฐาน ฟังก์ชันสรุปผลแบบ Basic ตัวอื่นๆ ก็เช่นกัน ส่วนใหญ่ก็จะหาผลสรุปจากข้อมูลที่เป็นตัวเลขเท่านั้น (แต่มีบางตัวที่สรุปได้จากข้อมูลประเภทอื่นด้วย เช่น การนับด้วย COUNTA) SUM = หาผลรวมจากช่องที่เป็นตัวเลขเท่านั้น AVERAGE = หาค่าเฉลี่ยจากช่องที่เป็นตัวเลขเท่านั้น…
สร้าง Dropdown กี่ชั้นก็ได้ใน Excel (Infinity Dependent Dropdown) 5

สร้าง Dropdown กี่ชั้นก็ได้ใน Excel (Infinity Dependent Dropdown)

ผมเคยสอนการทำ Drop down 2 ชั้นมาแล้ว ซึ่งก็พบว่าหลายคนทำแล้วติดขัดกับข้อจำกัดบางอย่างของมันอยู่... คราวนี้ผมก็เลยขอทำคลิปสอนการทำ Drop down หลายชั้น (กี่ชั้นก็ได้ ใช้คำอะไรก็ได้) มาซะเลย จะได้ไม่ติดปัญหาอีกต่อไป 555 ใครสนใจคลิ๊กดูได้เลยครับ ในคลิปนี้ผมใช้้ OFFSET ในการแก้ปัญหาครับ ทำ Dropdown กี่ชั้นก็ได้แบบข้ามชีท (New!) https://youtu.be/ykreZt6v1ek โหลดไฟล์ประกอบได้ที่นี่ https://drive.google.com/open?id=1tynrAHEkNc_XCj7HjOMo3w8i8m2tLxoD ใครยังไม่แม่น OFFSET ลองดูคำอธิบายในคลิปนี้ก่อนนะครับ [embed]https://youtu.be/aNd8ENjk-Oc[/embed] สร้าง Dropdown กี่ชั้นก็ได้ใน Excel ด้วย OFFSETสามารถ download ไฟล์ประกอบของคลิปเก่าได้ที่นี่ครับ http://bit.ly/infinity-dropdown
ใส่เครื่องหมาย $ ในสูตรยังไงไม่ให้งง 6

ใส่เครื่องหมาย $ ในสูตรยังไงไม่ให้งง

พื้นฐานที่สำคัญมากในการเขียนสูตร Excel คือการใส่เครื่องหมาย $ ใน Cell Reference (เช่น A1 C4 ) เวลาเขียนสูตร ซึ่งถ้าใครงงหรือทำไม่เป็น จะไม่สามารถเขียนสูตรที่ซับซ้อนในอนาคตได้เลย ดังนั้นเรามาปูพื้นฐานตรงนี้ให้แน่นยิ่งขึ้นดีกว่า ทำไมต้องใส่เครื่องหมาย $ ล่ะ? เพราะปกติว่าเขียนสูตรที่มี Cell Reference แล้วเรา Copy Cell ช่องนั้นไป Paste ที่อื่น Cell Reference มันจะเลื่อนไปตามทิศทางของการ Copy ซึ่งบางกรณีการเลื่อนนั้นก็เป็นประโยชน์กับเรา เช่น เวลา Copy ลงมาข้างล่าง Cell Reference ก็จะเลื่อนตามลงมา (จาก B3 -> B4 และจาก C3 -> C4) ซึ่งสะดวกมาก แต่มันก็จะมีบางกรณีที่เราไม่อยากให้มันเลื่อนตามลงมา เช่น ถ้า…
แนวทางจัดการวันที่สุดมั่ว (ผิดไปแล้วทำไงดี) 7

แนวทางจัดการวันที่สุดมั่ว (ผิดไปแล้วทำไงดี)

างที่ผมพยายามเตือนเรื่องการกรอกข้อมูลวันที่ไปแล้ว 2 ตอนด้วยกัน สมมติว่าคุณแก้ไขตามที่ผมบอกไปแล้ว แปลว่าการกรอกวันที่ในอนาคตไม่น่าจะผิดอีกแล้ว แต่ของในอดีตที่เคยผิดไปแล้วจะทำไงดีล่ะ? วันนี้ผมเลยทดลองกรอกวันที่มั่วๆ ลงไปในหลายๆ รูปแบบ แล้วลองเขียนสูตรดูซิว่าจะช่วยจัดการให้เข้าร่องเข้ารอยได้หรือไม่? แบบว่าอัดสูตรแบบ combo เข้าไปแบบเดียวกันดูเลยว่ามีใครรอดมั้ย? ดาวน์โหลดไฟล์ ไฟล์ที่ผมลองทำ => mess-up-date2 Screenshot การทำ มีหลายขั้นตอนมาก หลักๆ คือ ผมก็คิดก่อนว่า ตัวเองรู้ได้ไงว่าวันที่อันไหนผิด ด้วยวิธีอะไร แล้วจะแก้ให้ถูกต้องได้ยังไง? แล้วเอาสิ่งนั้นแหละมาเขียนเป็นสูตร ผมก็เลยพยายามแยก ปี เดือน วัน ออกมา แล้วลอง adjust เช่น ลบ 543 หรือ สลับวันกับเดือน แล้วสุดท้ายเอาไปตรวจว่าอยู่ในช่วงวันที่ที่ต้องการหรือไม่? (กำหนดอยู่ข้างบน ซึ่งเป็นตัวที่ช่วยเช็คได้ดีมากๆ ตัวนึงเลย) ถ้าอยู่ในช่วงวันที่ที่กำหนด ก็แสดงว่า ok น่าจะถูกครับ (สุดท้ายได้คอลัมน์ R) โดยสรุป ผมใช้หลายขั้นตอนมากๆ…
อะไรจะเกิดขึ้น? เมื่อใส่วันที่ใน Excel ด้วยปี พ.ศ. แทนที่จะใส่ ค.ศ. 8

อะไรจะเกิดขึ้น? เมื่อใส่วันที่ใน Excel ด้วยปี พ.ศ. แทนที่จะใส่ ค.ศ.

หลังจากบทความที่แล้ว ผมได้ให้คำแนะนำเกี่ยวกับการตั้งค่าวันที่ให้ถูกต้องแล้ว (เรื่อง วัน/เดือน/ปี หรือ เดือน/วัน/ปี ) มาวันนี้ ผมจะขอย้ำอีกเรื่องนึงซึ่งเกี่ยวกับวันที่เช่นเดียวกัน (ซึ่งจริงๆ ได้พยายามย้ำหลายครั้งแล้ว) นั่นก็คือ... การทำงานเกี่ยวกับวันที่ใน Excel เราจะต้องใส่ปีเป็น ค.ศ. ไม่ใช่ พ.ศ.  เช่น หากต้องการใส่วันที่ 27 เมษายน พ.ศ. 2560 ให้ใส่ว่า 27/4/2017 ไม่ใช่ 27/4/2560 ! เพราะถ้าใส่เป็น 27/4/2560 ...Excel จะคิดว่าเราหมายถึง 27 เมษายน ค.ศ. 2560 ซึ่งเป็นวันในอนาคตอีก 543 ปี!! (บ้าไปแล้ว) บางคนถึงรู้อย่างงี้ก็บอกว่า... ฉันไม่แค่ร์หรอก! ก็เรารู้เองว่าจริงๆ มันคือ พ.ศ. 2560 ก็ได้ ยิ่งถ้านัดกับเพื่อนๆ ร่วมงานทุกคนให้ร่วมใจกันเข้าใจว่ามันเป็น พ.ศ. ก็น่าจะใช้ได้นี่นา? (เหมือนอุปาทานหมู่ หรือพวก ทฤษฎีสมคบคิดเลยแฮะ) ผมจะบอกว่าถึงเราจะฮั้วกับเพื่อนแล้วมันก็จะผิดอยู่ดี ซึ่งผมจะแสดงให้เห็นชัดๆ…
การตั้งค่าให้ Excel รู้จักวันที่ในรูปแบบที่ต้องการ เช่น วัน/เดือน/ปี ไม่ใช่ เดือน/วัน/ปี 9

การตั้งค่าให้ Excel รู้จักวันที่ในรูปแบบที่ต้องการ เช่น วัน/เดือน/ปี ไม่ใช่ เดือน/วัน/ปี

ต้องบอกก่อนเลยว่าเรื่องวันที่และเวลา เป็นเรื่องที่หลายคนไม่เข้าใจ และอาจจะกำลังปวดหัวกับมันอย่างมาก ซึ่งสิ่งหนึ่งที่ทำให้หลายท่านปวดหัวแบบสุดๆ ไปเลยก็คือเรื่องของ "รูปแบบวันที่" นี่คือตัวอย่างกรณีที่ตั้งค่าผิด สิ่งที่เกิดขึ้นคือ เราตั้งใจพิมพ์รูปแบบวันที่ในรูปแบบ วัน/เดือน/ปี แต่หารู้ไม่ว่าคอมพิวเตอร์ที่เรากำลังใช้อยู่ดันถูกตั้งค่าใน Control Panel ให้เป็น เดือน/วัน/ปี ผลลัพธ์ที่ได้เลยมั่วนิ่มเลย!! อธิบายความผิดพลาด 13/4/2017 ถูกจัดชิดซ้าย (เป็น Text) เพราะมันไม่รู้จัก เดือนที่ 13 นั่นเอง (วันที่ 4 เดือน 13 ค.ศ. 2017 ???) แปลว่าเลข 10/4/2017 ที่เราเข้าใจว่าถูกต้อง จริงๆ ก็ผิดด้วย เพราะ Excel เข้าใจว่าเป็นวันที่ 4 ตุลาคม แต่จริงๆ เราต้องการเป็นวันที่ 10 เมษายนต่างหาก!! ส่วนการที่ 3/5/2017 ลาก Fill…
ลากข้อมูลแล้วให้ Excel ใส่ A-Z โดยอัตโนมัติ ด้วย Custom List 10

ลากข้อมูลแล้วให้ Excel ใส่ A-Z โดยอัตโนมัติ ด้วย Custom List

แต่ก่อนตอนผมใช้ Excel ใหม่ๆ มีปัญหาอย่างหนึ่งที่ผมหงุดหงิดมาก นั่นคือ ผมต้องการลาก Fill Handle แล้วให้ Excel สร้างตัวอักษร A-Z หรือ ก-ฮ โดยอัตโนมัติ แต่สิ่งที่ Excel ทำให้มันกลับแค่เป็นการ Copy ตัว A หรือ ก ที่ผมพิมพ์ลงมาให้ซะงั้น! ผมงงมากเลย เพราะขนาด series ที่เป็นตัวเลขดูเหมือนว่าจะยากๆ เช่น 1,4,7,10,... มันยังฉลาดใส่มาให้ได้ แต่ทำไมแค่ A-Z หรือ ก-ฮ ดันไม่ใส่มาให้ฟะ!? ตอนนั้นก็ต้องใช้วิธีพิมพ์เอาเองบ้าง ใช้ฟังก์ชัน CHAR มาช่วยบ้าง ซึ่งก็ไม่ใช่วิธีที่ดีเท่าไหร่ จนผมได้พยายามค้นใน Internet ก็รู้ว่าจริงๆ แล้ว Excel ทำแบบที่ผมต้องการได้ ด้วยการสร้างสิ่งที่เรียกว่า Custom List…