excelavista

Category: Spreadsheet

  • การใช้ฟังก์ชัน XLOOKUP ใน Excel

    ภาพรวม XLOOKUP ใช้ค้นหาค่าที่เราสนใจใน range หรือ table จากอีกค่าหนึ่งที่เรารู้อยู่แล้ว (keyword) โดยเปรียบเทียบง่าย ๆ ก็เหมือนเลขในบาร์โค้ด เมื่อเราสแกนแล้วระบบจะดึงชื่อสินค้าและราคามาให้เราเห็นนั่นแหละ ถ้าใน Excel จะมีฟังก์ชันในกลุ่มนี้อยู่หลายตัว แต่ตัวใหม่ล่าสุดคือ XLOOKUP นี่แหละ ซึ่ง Microsoft บอกว่า ทำงานได้ไวกว่าด้วย ⚠️ โดยผู้ที่จะใช้งาน XLOOKUP ได้ จะต้องมี Microsoft 365 ใน Excel 2016 และ Excel 2019 ไม่มีให้ใช้ครับ รูปแบบการใช้งาน (Syntax) จำเป็น? อาร์กิวเมนต์ คำอธิบาย จำเป็น lookup_value ค่าที่เรารู้ และจะใช้ค้นหาค่าอื่น จำเป็น lookup_array array หรือ range ที่คาดว่าจะมีค่าที่เราค้นหา จำเป็น return_array array หรือ…

  • การใช้ฟังก์ชัน RANDARRAY ใน Excel

    ภาพรวม ฟังก์ชัน RANDARRAY ใช้สุ่มเลขให้เรา โดยกำหนดค่าเริ่มต้นและค่ามากสุดได้ รวมทั้งกำหนดได้ว่าจะเอากี่แถวกี่คอลัมภ์ รวมไปถึงระบุได้ว่า ค่าเหล่านั้นจะเป็นทศนิยมหรือจำนวนเต็มก็ได้ รูปแบบการใช้งาน (Syntax) จำเป็น? อาร์กิวเมนต์ คำอธิบาย ไม่ใส่ก็ได้ [row] ตัวเลขจำนวนแถวที่เราต้องการ ถ้าไม่ระบุจะได้ 1 แถว ไม่ใส่ก็ได้ [column] ตัวเลขจำนวนคอลัมภ์ที่เราต้องการ ถ้าไม่ระบุจะได้ 1 คอลัมภ์ ไม่ใส่ก็ได้ [min] ตัวเลขระบุจำนวนที่ค่าน้อยสุดที่ต้องการ ถ้าไม่ระบุจะได้เลยทศนิยม หรือ 0.xxxxxx ไม่ใส่ก็ได้ [max] ตัวเลขระบุจำนวนที่ค่ามากสุดที่ต้องการ ถ้าไม่ระบุค่ามากสุดคือ 0.999999 ไม่ใส่ก็ได้ [whole_number] ตัวเลขระบุจะกำหนดให้สุ่มค่าเป็นจำนวนเต็มไหม? หากต้องการให้ระบุเป็น TRUE ถ้าไม่ระบุจะเป็น FALSE ตัวอย่างการใช้งาน ที่จริงแล้ว ฟังก์ชันนี้ใช้ได้อย่างตรงไปตรงมา คือ จะได้เลขที่สุ่มขึ้นมา โดยที่คุณไม่ต้องคิดเอง และไม่ต้องใช้ Autofill ลากเพิ่มเหมือนการใช้ฟังก์ชัน RAND และ RANDBETWEEN…

  • การใช้ฟังก์ชัน SEQUENCE ใน Excel

    ภาพรวม ฟังก์ชัน SEQUENCE ใช้สำหรับสร้างตัวเลขแบบเรียงลำดับ อย่างเช่น 1, 2, 3, … โดยเราสามารถกำหนดได้ว่าจะเอากี่แถวกี่คอลัมภ์ รวมไปจุดเริ่มต้นและขั้นละเท่าไหร่ด้วย ต้วอย่างเช่น เราต้องการเลขเรียงลำดับเริ่มจาก 1 เพิ่มทีละ 1 จำนวน 3 แถว 4 คอลัมภ์ ดังภาพด้านล่างนี้ สูตรก็จะเป็น รูปแบบการใช้งาน (Syntax) จำเป็น? อาร์กิวเมนต์ คำอธิบาย จำเป็น row ตัวเลขจำนวนแถวที่เราต้องการ ไม่ใส่ก็ได้ [column] ตัวเลขจำนวนคอลัมภ์ที่เราต้องการ ถ้าไม่ระบุจะได้ 1 คอลัมภ์ ไม่ใส่ก็ได้ [start] ตัวเลขระบุจำนวนที่เป็นเริ่มต้น ถ้าไม่ระบุจะเริ่มที่ 1 ไม่ใส่ก็ได้ [step] ตัวเลขระบุจำนวนที่จะเพิ่มขึ้นในแต่ละขั้น ถ้าไม่ระบุจะเพิ่มขึ้นที่ละ 1 สิ่งที่ควรทราบ ตัวอย่างการใช้งาน ที่จริงแล้วฟังก์ชันนี้ น่าจะมีจุดประสงค์เพื่อช่วยให้เราเตรียมข้อมูลบางอย่างไปทำต่อ โดยที่ไม่ต้องพิมพ์เองซ้ำ ๆ เช่น การสร้างเดือนเรียงจากมกราคมไปถึงธันวาคม…

  • การใช้ฟังก์ชัน SORTBY ใน Excel

    ภาพรวม ฟังก์ชัน SORTBY ใช้ดึงพร้อมกับเรียงลำดับข้อมูลจาก array หรือ range อื่น โดยที่ข้อมูลที่ได้มา จะเปลี่ยนไปตามข้อมูลต้นฉบับและเรียงลำดับให้ใหม่ด้วย หากใครได้อ่านโพสต์ก่อนที่หน้านี้ ที่เราพูดถึงฟังก์ชัน SORT ไปแล้ว การใช้งานจะคล้ายกัน แต่ SORT จะเรียงลำดับตามข้อมูลใน array หรือ range โดยยึดที่คอลัมภ์ใดคอลัมภ์หนึ่งเท่านั้น หรือแถวใดแถวหนึ่งเท่านั้น ในกรณีที่เรากำหนดให้จัดลำดับข้อมูลที่วางแบบแนวคอลัมภ์ แต่ใน SORTBY จะเรียงลำดับข้อมูลให้จาก 1 คอลัมภ์หรือมากกว่านั้นก็ได้ และคอลัมภ์ที่เป็นเงื่อนไขในการจัดเรียงก็ไม่จำเป็นต้องอยู่ในข้อมูลด้วย ตัวอย่างการใช้งาน ตัวอย่างข้อมูลด้านล่าง สมมติเราต้องการจัดเรียงข้อมูลพนักงานตามแผนกจากน้อยไปหามาก (A-Z) โดยข้อมูลนี้สามารถ ดูและดาวน์โหลด ได้เลย (File > Save As > Download a Copy) สูตรที่ใช้ก็จะเป็นดังนี้ (กรณีที่ข้อมูลทำเป็น table ไว้แล้ว) หรือสำหรับข้อมูลที่เป็น range สูตรจะเป็น 💡โดยจะสังเกตว่า การใช้งานแบบนี้ก็แทบจะเหมือนกับฟังก์ชัน SORT…

  • การใช้ฟังก์ชัน SORT ใน Excel

    การเรียงลับดับข้อมูลใน Excel ไม่ใช้เรื่องใหม่ 😊 แต่ที่อาจจะดูใหม่คงจะเป็นการมีฟังก์ชันเรียงลำดับข้อมูลให้เราเรียกใช้และให้ข้อมูลกลับมาแบบ dynamic (เปลี่ยนไปตามข้อมูลต้นฉบับ) นี่พึ่งจะมีให้ใช้ใน Excel 2021 นั่นคือ ตั้งแต่ก่อนพวกเราต้องทำแบบ manual คือไปที่ Data > Sort หรือผ่าน VBA มาตลอด 😂 ภาพรวม ฟังก์ชัน SORT ใช้ดึงเอาข้อมูลออกมาจาก range หรือ array ที่เรามีอยู่มาเรียงลำดับใหม่ในแบบที่เราต้องการ รูปแบบการใช้งาน (syntax) จำเป็น? อาร์กิวเมนต์ คำอธิบาย ต้องใส่ array ข้อมูลที่เราต้องการและจะนำมาเรียงลำดับ อาจจะเป็น range หรือ array ที่ได้มาจากพวกฟังก์ชัน dynamic array ทั้งหลาย ไม่ใส่ก็ได้ [sort_index] ตัวเลขใช้ระบุแถวหรือคอลัมภ์ที่เราจะให้เรียงลำดับ ไม่ใส่ก็ได้ [sort_order] ตัวเลขใช้ระบุวิธีการจัดลำดับ โดย ใช้ 1 หากต้องการเรียงจากน้อยไปมาก…

  • ฟังก์ชัน dynamic array ใน Excel

    หนึ่งในคำถามที่ผมพบอยู่บ่อย ๆ สำหรับคนใช้ Excel คือ อยากให้ VLOOKUP ได้ผลลัพธ์หลายค่า จากคำค้นหาคำเดียว ทำยังไง? ซึ่งคำถามนี้ก็รวมไปถึง XLOOKUP ซึ่งเป็นฟังก็ชันน้องใหม่ที่แนะนำให้ใช้แทน VLOOKUP สำหรับผู้ใช้ Excel รุ่นใหม่ๆ ด้วย เพราะหลาย ๆ คนอยากให้ VLOOKUP หรือ XLOOKUP แล้วได้ข้อมูลที่ตรงกับเงื่อนไข (match) ออกมาทั้งหมด หรือได้ทุกค่าที่ตรง คำตอบง่ายสุดคือ ไม่ได้ เพราะ 2 ฟังก์ชันนี้ไม่ได้ออกแบบมาเพื่องานนี้ แต่ก็ไม่ใช่ว่ามันจะทำไม่ได้ซะทีเดียว แต่มันต้องใช้สูตรที่ค่อนข้างจะมีซับซ้อนน่ะ และเราจะไม่อธิบายในที่นี้ 😂 แต่แนะนำให้ไปอ่านในวิธีการใช้งานฟังก์ชัน FILTER แทน เพราะนี่คือ คำตอบจริง ๆ ที่คุณค้นหาอยู่ เพียงแค่บังเอิญว่ามันมีใช้แค่ใน Excel 365 วันนี้เราจะมาพูดถึงฟังก์ชันประเภท dynamic array ใน Excel กัน และ FILTER()…

  • วิธีแก้ SPILL error ใน Excel

    ภาพรวม #SPILL! จะเกิดขึ้นเมื่อสูตรของเรามีค่าที่ส่งกลับมากกว่า 1 ค่า (multiple results) หรือจะเรียกว่า dynamic array และที่นี้ผลลัพธ์ที่ได้ มันล้นกรอบหรือขอบเขตที่ Excel จะวางข้อมูลให้เราได้ ซึ่งอาจเกิดได้หลายกรณี ไม่ว่าจะเป็น ก่อนจะไปดูรายละเอียด ไฟล์ตัวอย่างดาวน์โหลดได้ที่ลิงค์นี้ครับ Range ที่จะใส่ข้อมูลไม่ว่าง หรือพูดได้ว่า ถ้าผลลัพธ์ที่จะส่งกลับมาจะวางทับข้อมูลเดิมที่มีอยู่ในเซลล์ Excel จะไม่ทำ คงเพราะกลัวข้อมูลเราหาย 👍 ดังนั้น Excel จะแสดง error เป็น #SPILL! กำหนดขนาดที่แน่นอนของ range ไม่ได้ ส่วนมากเกิดขึ้นจากขนาดของ spill range หรือพื้นที่ที่จะใส่ข้อมูลนั้นไม่คงที่ โดยมักจะถูกปรับขนาดไปตามผลการคำนวณในแต่ละรอบ เช่น โดย error นี้มักจะเกิดขึ้นจากการใช้ฟังก์ชัน RAND, RANDARRAY และ RANDBETWEEN หรือฟังก์ชันฟังก์ชันที่มีการเปลี่ยนค่าไปเรื่อย ๆ (volatile functions) หรือค่าไม่คงที่ จะถูกคำนวณใหม่เสมอหากมีการเปลี่ยนแปลงใด ๆ…

  • การใช้ HSTACK และ VSTACK ใน Excel

    ภาพรวม ทั้ง HSTACK และ VSTACK ใช้รวมอาเรย์ (array) หรือรายการของข้อมูล โดยที่ VSTACK จะนำอาเรย์เหล่านั้นมาเรียงต่อกันในแนวตั้งจากบนลงล่าง ส่วน HSTACK เรียงต่อกันในแนวนอนจากซ้ายไปขวา HSTACK() มักใช้สำหรับรวมข้อมูลแบบเดียวกันจาก 2 แหล่งมาวางไว้ข้างกันจากซ้ายไปขวา เพื่อจับกลุ่มข้อมูลใหม่จากข้อมูลเดิม และข้อมูลที่ได้จะเป็นแบบไดนามิค (dynamic) หรือเปลี่ยนไปตามข้อมูลต้นฉบับอัตโนมัติ อย่างเช่น ถ้าเรามีข้อมูลยอดขายแบ่งตามหมวดหมู่สินค้าดังภาพด้านล่างนี้ ทีนี้เราอยากจะนำมาทำเป็นตาราง เพื่อที่จะดูว่าแต่ละหมวดหมู่สินค้าเหล่านั้นแต่ละเดือนขายได้เท่าไหร่ โดยสมมติว่าเราจะใส่หัวตารางใน F1 และเดือนก็เรียงไปตาม G1, H1 ไปเรื่อย ๆ โดยอันนี้เราใส่ข้อมูลเอง ส่วนใน F2 จะนำข้อมูลยอดขายของแต่ละหมวดของแต่ละเดือนมาใส่ (โดยข้อมูลตัวอย่างเราเรียงมาแล้วนะ) สูตรจะเป็นดังนี้ ซึ่งก็คือเราจะเอาชื่อหมวดหมู่มาจากคอลัมภ์ C และยอดขายของแต่ละเดือนมาจากคอลัมภ์ D ซึ่งในตัวอย่างเราทำมาแค่ 3 เดือน เพื่อให้เห็นภาพกาใช้งานได้ชัดหน่อย เพราะถ้าเอามาหมดมันจะดูยาว และเหมือนจะยากไปซะเปล่า 😁 รูปแบบการใช้งาน HSTACK จากตัวอย่างที่ผ่านมาก็คือ ถ้าเราอยากได้ข้อมูลตรงไหนมาวางต่อ ๆ…

  • วิธีใช้ฟังก์ชัน FILTER() ของ Excel

    ภาพรวม ฟังก์ชัน FILTER() ใช้ดึง (extract) เอาข้อมูลเฉพาะที่ตรงกับเงื่อนไขที่กำหนดกลับมาแบบไดนามิค (dynamic) หรือเปลี่ยนไปตามข้อมูลต้นฉบับอัตโนมัติ ดังนั้นถ้าข้อมูลหรือเงื่อนไขเปลี่ยนไป ฟังก์ชันนี้ก็จะให้ข้อมูลชุดใหม่ที่ตรงกับเงื่อนไขออกมา ซึ่งทำให้เรามีความยึดหยุ่นในการเรียกข้อมูลออกมาดูหรือใช้งานได้ โดยที่ไม่ต้องไปยุ่งกับต้นแหล่งข้อมูลฉบับเลย หากต้องการไฟล์ตัวอย่างไปทดลองดู เปิดดูและดาวน์โหลด ได้เลยครับ การใช้งานสูตร ตัวอย่างการใช้งาน สมมติเรามีข้อมูลดังตารางด้านล่างนี้ ชื่อ ตำแหน่ง แผนก เงินเดือน สมชาย ผู้จัดการ การตลาด 50,000 อรอนงค์ นักวิเคราะห์ การเงิน 40,000 สมหญิง ผู้ช่วยผู้จัดการ การตลาด 45,000 สมศักดิ์ วิศวกร วิศวกรรม 35,000 ปวีณา พนักงานขาย การขาย 30,000 ธนพล นักวิเคราะห์ การเงิน 42,000 วราภรณ์ ผู้ช่วยผู้จัดการ การเงิน 38,000 กิตติ วิศวกร วิศวกรรม 37,000 จิราพร…

  • ใช้ฟังก์ชัน FILTER กรองเอาเฉพาะตัวเลขในคอลัมภ์

    วันนี้เจอปัญหาหนึ่งในข้อมูลที่เราเก็บไว้ใช้เอง คือ ในคอลัมภ์นั้นมีทั้งตัวเลข ตัวอักษร และไม่มีข้อมูล แต่เราอยากได้เฉพาะตัวเลข ไม่เอาตัวอักษรและเซลล์ที่ไม่มีข้อมูล โดยปกติ Excel ทำได้โดยใช้ Autofilter ในตารางได้อยู่แล้วล่ะ แต่ประเด็นคือเราอยากใช้สูตร เพราะไม่อยากมาคลิก 😂 ก็คือขี้เกียจนั่นแหละ เพราะต้องเอาไปนับเลขที่ไม่ซ้ำ และใช้ในการคำนวณต่ออีก และคิดว่าฟังก์ชัน FILTER คงทำได้แหละ และไปค้นเจอใน Google Groups นี้ และก็ตรงกับที่อยากใช้พอดี การใช้ฟังก์ชัน FILTER กรองเราเฉพาะตัวเลขในคอลัมภ์ โดยฟังก์ชัน FILTER จะใช้กรองข้อมูลที่ตรงกับเงื่อนไขที่ระบุไว้จาก Range อื่น และเราจะใช้ร่วมกับอีก 3 ฟังก์ชัน (แต่ไม่ยากนะ 😁 อย่าพึ่งรีบหนีไปล่ะ) ก็คือฟังก์ชัน ใครอยากดูไฟล์ตัวอย่าง คลิกตรงนี้และ File > Save as > Download a copy จาก OneDrive ได้เลยนะครับ จากภาพตัวอย่างข้อมูลจะอยู่ในคอลัมภ์…