Month: July 2016

  • Excel ความเร็วแสง : ตอน VLOOKUP ความเร็วแสง

    Excel ความเร็วแสง : ตอน VLOOKUP ความเร็วแสง

    Excel ความเร็วแสง

    หากมนุษยชาติมีภารกิจจะต้องเดินทางไปยังดวงดาวในอวกาศอันไกลโพ้น… พวกเขาจะทำอย่างไร?

    เหล่านักบินอวกาศมีทางเลือกหลายทาง ตั้งแต่

    1. เดินทางไปเลย เตรียมเสบียงไปเต็มที่ กำลังใจมาเต็ม ซักวันคงเดินทางถึง!
    2. ตัดใจ ยอมแพ้กับภารกิจ อยู่บ้านเฉยๆ ดีกว่า
    3. พัฒนาวิธีเดินทางด้วยความเร็วแสงให้ได้!

    ถ้าทำได้ทุกคนคงอยากจะเลือก ข้อ 3 จริงมั้ยครับ?

    ในชีวิตของการใช้งาน Excel ก็เช่นกัน… หลายครั้งเราทำงานกับข้อมูลจำนวนมหาศาล
    เปรียบได้กับภารกิจการเดินทางอันแสนไกลของนักบินอวกาศ…
    เช่น ต้องใช้ VLOOKUP Map ข้อมูลที่มีหลายหมื่น หลายแสนบรรทัด คุณจะทำอย่างไร?

    1. นั่งรอไป ซักวันนึง เจ้า % Calculate จะกระดิกไปถึง 100% ในที่สุด พร้อมกับพลังในตัวของคุณที่หมดลง
    2. ไม่ยุ่งกับข้อมูลเยอะๆ เด็ดขาด
    3. เรียนรู้วิธีใช้เทคนิค Excel ความเร็วแสง!

    Excel ความเร็วแสง คือเทคนิคหลายอย่างที่ผมจะรวบรวมไว้เป็น series เรื่องการทำงานใน Excel ให้เร็วขึ้นโดยเฉพาะ

    แล้ววันนี้ผมจะนำเสนอเทคนิคเด็ด นั่นคือ วิธีการใช้ VLOOKUP ความเร็วแสงครับ ซึ่งแม้จะเคย Post เรื่อง VLOOKUP เร็ว 100 เท่า มาแล้ว แต่ขอเอามาขัดเกลาให้อ่านง่ายขึ้นอีกครั้ง อันนี้อ่าน 5-10 นาทีจบ ทำได้เลยแน่นอนครับ!

    เทคนิค VLOOKUP ความเร็วแสง

    เทคนิคในบทความนี้จะเน้นว่ากันด้วยวิธีปฏิบัติล้วนๆ ทฤษฎีใส่ให้บางๆ เท่านั้ย หากอยากเข้าใจที่มาที่ไปแบบละเอียด สามารถไปอ่านใน Post เก่าได้นะครับ

    เอาล่ะ จะ VLOOKUP ให้เร็วขึ้นทำได้ยังไง? ก่อนอื่น ต้องเข้าใจว่า VLOOKUP มี 2 แบบ คือ

    1. แบบ Approximate Match (เลือก Range Lookup เป็น TRUE หรือ 1) ซึ่งทำงานเร็วมาก และสามารถให้ผลลัพธ์ที่ไม่ตรงกับ Lookup_Value มาได้ด้วย ข้อควรระวังคือ คอลัมน์แรกของตารางอ้างอิงต้องเรียงจากน้อยไปมากเท่านั้น
    2. แบบ Exact Match (เลือก Range Lookup เป็น FALSE หรือ 0) ซึ่งทำงานช้ามาก แต่ให้ผลลัพธ์แม่นยำ

    เรื่องของเรื่องคือ เวลาคนส่วนใหญ่ Map ข้อมูลแบบต้องให้ผลลัพธ์เป๊ะๆ ก็จะใช้แบบ Exact Match นี่แหละ (เพราะมันแม่น) แต่ผมจะบอกว่าถ้าข้อมูลมีเยอะมาก คุณควรใช้แบบ Approximate Match แทนครับ!

    หลักการคือ

    1. เรียงข้อมูลในตารางอ้างอิง ให้คอลัมน์แรก (ที่เอา Lookup Value ไปหา) เรียงจากน้อยไปมากก่อน (สำคัญสุดๆ!!!)
    2. เขียนสูตร VLOOKUP แบบ Approx. เพื่อดึงค่าคอลัมน์ที่ต้องการกลับมา
      • เขียนว่า =VLOOKUP(คำค้นหา,ตารางอ้างอิง,คอลัมน์ที่ต้องการ,TRUE)
        ผลลัพธ์จะคำนวณร็วมาก แต่บางบรรทัดจะผิด คือ ควรจะเป็น #N/A แต่ให้ค่าเกินมา
    3. เราแก้จุดผิด โดยการใช้ VLOOKUP แบบ Approx. ดึงค่าคอลัมน์แรกกลับมาเช็คกับ Lookup Value มาตรงกันหรือไม่?
      โดยใช้ IF มาช่วยเช็คว่าถ้าตรงกันแสดงว่าเป็นค่าที่เชื่อถือได้ ถ้าไม่ตรงกันแสดงว่าไม่ควรใช้  
      • เขียนดึงคอลัมน์แรก =VLOOKUP(คำค้นหา,ตารางอ้างอิง,1,TRUE)
      • เช็คว่าตรงกับ Lookup Value หรือไม่? ว่า =VLOOKUP(คำค้นหา,ตารางอ้างอิง,1,TRUE)=คำค้นหา
    4. ถ้าตรงเชื่อได้ ถ้าไม่ตรงเชื่อไม่ได้
      1. เชื่อได้ ต้องเอาค่าคอลัมน์ที่ต้องการกลับมา =VLOOKUP(คำค้นหา,ตารางอ้างอิง,คอลัมน์ที่ต้องการ,TRUE)
      2. เชื่อไม่ได้ ให้เป็น #N/A โดย =NA()

    สรุปสูตรสุดท้าย
    =IF(VLOOKUP(คำค้นหา,ตารางอ้างอิง,1,TRUE)=คำค้นหา,VLOOKUP(คำค้นหา,ตารางอ้างอิง,คอลัมน์ที่ต้องการ,TRUE),NA())

    ตัวอย่าง

    vlookup100x

    ในช่อง B11 ผมเขียนสูตรได้ว่า

    =IF(VLOOKUP(คำค้นหา,ตารางอ้างอิง,1,TRUE)=คำค้นหา,VLOOKUP(คำค้นหา,ตารางอ้างอิง,คอลัมน์ที่ต้องการ,TRUE),NA())

    =IF(VLOOKUP(A11,$A$1:$B$6,1,TRUE)=A11,VLOOKUP(A11,$A$1:$B$6,2,TRUE),NA())

    จะได้ผลลัพธ์เป็น สมกอตต์ ถูกต้องครับ! และถ้าเป็น B10 ก็จะต้อง #N/A ถูกต้อง เช่นกัน เพราะไม่มีค่า p002x ในตารางอ้างอิง

    ก่อนจากกัน

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

    Warp !!

  • สอนการทำ Dynamic Range ด้วย Table

    สอนการทำ Dynamic Range ด้วย Table

    อะไรคือ Dynamic Range?

    Dynamic Range มันก็คือการทำให้ Range มีความสามารถในการ “ยืดได้หดได้” ตามสถานการณ์ที่เหมาะสม
     
    เช่น พอเราใส่ข้อมูลเพิ่มปุ๊ป Dropdown List ที่สร้างไว้ก็รู้จักข้อมูลที่เรากรอกเลย หรือ แม้แต่สามารถให้ User เลือกข้อมูลที่จะแสดงผลบบนกราฟได้
     
    ซึ่งจริงๆ แล้วการทำ Dynamic Range นั้น มีวิธีการทำอยู่ 2 แนวทางหลักๆ เลย ก็คือ
    1.แนวทางการใช้เครื่องมือ Table กับ 2.แนวทางการเขียนสูตร
     
    ซึ่งในบทความนี้เราจะมาเรียนรู้การทำ Dynamic Range ด้วย Table ซึ่งเป็นวิธีที่ง่ายที่สุดกันก่อนครับ 

    การทำ Dynamic Range ด้วย Table

    เครื่องมือ Table (ไม่ใช่ Data Table ที่เรียนผ่านไปแล้วนะ) สามารถสร้าง Dynamic Range ได้แบบง่ายๆ ภายในพริบตา

    สมมติ ผมมี Range อยู่ แล้วผม convert เป็น Table ก่อน โดยกด Ctrl+T แล้วติ๊ก My Table has header ด้วย จะได้แบบนี้

    01 02

    หากผมเอาไปสร้างกราฟแท่งธรรมดาๆ เลย ขอแบบเร็วๆ
    ผมขอ กดปุ่ม F11 เลย จะได้กราฟแบบนี้ (ซึ่งจะเห็นว่ามีข้อมูล 4 แท่ง)

    03

    ต่อไปเราไปเพิ่มข้อมูลต่อท้าย Table อีก สังเกตว่าสีของ Table จะงอกออกไปครอบคลุมข้อมูลใหม่โดยอัตโนมัติ

    04

    พอเรากลับมาดูกราฟ ก็จะเห็นว่ามีข้อมูลใหม่ถูกเพิ่มมาโดยอัตโนมัติ

    05

    นี่แหละครับคือความเป็น Dynamic Range แบบง่ายๆ ซึ่งสามารถนำไปประยุกต์กับการเลือก Data Source ของ PivotTable ได้ด้วย ซึ่งจะช่วยให้ไม่ต้องมาเลือก Source Data ใหม่ และไม่ต้องเลือกทั้งคอลัมน์เผื่อไว้ก่อน (ถ้าเลือกเผื่อไว้ก่อนจะกด Group Data แบบอัตโนมัติไม่ได้)

    Dynamic Dropdown List

    อีกตัวอย่างหนึ่งคือการทำ Dynamic Dropdown List เช่น จาก Table ก่อนหน้านี้ ผมจะเอาชื่อทีมมาเป็น Dropdown ให้เลือก

    ซึ่งผมสามารถอ้างอิงรายการในทีมได้โดยเขียนสูตร = แล้วไปจิ้มตรงหัวคอลัมน์ จะได้ว่า =Table1[ทีม]

    06

    ผมก็เอาสูตรที่ได้เนี่ยแหละ ไปใส่ใน Data Validation List เลย แต่ปรากฏว่า Excel ไม่ยอม!

    แต่อย่ายอมแพ้ครับ มันมีวิธีแก้เล็กน้อย โดยการใช้ Defined Name มาช่วยนั่นเอง

    วิธีการคือ เอาสูตรที่ได้ไปตั้งชื่อก่อนครับ แล้วค่อยเอาชื่อนั้นไปใส่ใสน Data Validation อีกทีหนึ่ง

    07

    ใส่ใน Data Validation ได้เลย

    08

    จากนั้นจะเห็นว่า หากในอนาคตเรามีเพิ่มรายการเข้าไป Dropdown ของเราก็จะรู้จักรายการใหม่ๆ นั้นเองโดยอัตโนมัติครับ

    09

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

    เช่น ถ้ามี Table อยู่ในไฟล์ เราจะใช้เครื่องมือ Custom View ไม่ได้ หรือ บางทีเราต้องการทำ Dynamic Range ที่ซับซ้อนมากขึ้น เช่น แสดงยอดขาย 6 เดือนล่าสุดไปเรื่อยๆ แบบนี้จะต้องใช้สูตรแทนการใช่ Table แล้วครับ ซึ่งใครอยากรู้ รออ่านได้ในหนังสือเล่ม 2 นะครับ ^^