เผยแพร่เมื่อวันที่ 30 เมษายน 2026 เวลา 10:30 น. (EDT)
Yasir เป็นวิศวกรกลที่เขียนบทความด้านเทคโนโลยีสำหรับ MUO ครอบคลุม Windows, ประสิทธิภาพ, ความปลอดภัย และอินเทอร์เน็ต ความสนใจในระบบอัตโนมัติทำให้เขาทดลองทั้งฮาร์ดแวร์และซอฟต์แวร์อย่างต่อเนื่อง
การเดินทางเขียนเทคโนโลยีของเขาเริ่มต้นระหว่างปีที่ 3 ของวิศวกรรมศาสตร์ นำเขาไปสู่ Android Police ก่อนเข้าร่วม MUO เขาเน้นการทำให้เทคโนโลยีเข้าถึงได้ง่าย ไม่ว่าจะเป็นการแก้ไขปัญหา Windows, สำรวจเครื่องมือเพิ่มประสิทธิภาพ หรืออธิบายความเสี่ยงด้านความปลอดภัยด้วยภาษาที่เข้าใจง่าย สำหรับ Yasir วิธีที่ดีที่สุดคือการใช้เครื่องมือจริงและเผชิญปัญหาเดียวกับผู้อ่าน
เมื่อไม่ได้เขียนหรือทำวิศวกรรม จะพบ Yasir กำลังดู Impractical Jokers และหัวเราะจริงๆ กับการหลอกลวงที่เขาเคยดูไปแล้วหลายสิบครั้ง
ผู้ใช้ Excel ส่วนใหญ่ที่ฉันรู้จบเรียนรู้สูตรตรรกะแบบเดียวกัน — เรียนฟังก์ชันทีละตัว ซ้อนทับสิ่งที่รู้อยู่แล้ว ฟังก์ชันอาร์เรย์แบบไดนามิกไม่ได้แทนที่ทักษะเหล่านั้น แค่ทำให้หลายวิธีแก้ปัญหา (workaround) ไม่จำเป็นอีกต่อไป ฉันใช้ TAKE และ DROP สำหรับรายการอันดับ 5 ที่อัปเดตอัตโนมัติมาเป็นระยะเวลา และการเปลี่ยนแปลงเดียวกันเกิดขึ้นกับฟังก์ชัน 4 ตัวด้านล่าง แต่ละตัวลดขั้นตอนหลายขั้นตอนที่เคยทำเป็นสัทธนูปะปนให้เหลือเพียงสูตรเดียว
เนื้อหาเกี่ยวข้อง
FILTER แทนที่พิธีกรรมทั้งหมดของคอลัมน์ช่วยและสูตรอาร์เรย์
สูตรเดียวทำสิ่งที่หลายฟังก์ชันเคยแบ่งทำกัน
การดึงแถวที่ตรงตามเงื่อนไขใน Excel รุ่นเก่าหมายถึงการสร้างสูตร INDEX, MATCH, SMALL และ IFERROR แบบซ้อนกัน และกด Ctrl + Shift + Enter มันใช้ได้ แต่การดูแลรักษาต่อมาเป็นปัญหา ตัวเลือกอีกอย่างคือใช้ AutoFilter คัดลอกแถวที่มองเห็นได้ และวางเป็นค่าคงที่ที่อื่น ก็ใช้ได้จนกว่าข้อมูลต้นทางจะเปลี่ยน
ฟังก์ชัน FILTER ทำงานเดียวกันในบรรทัดเดียว ในสเปรดชีตขายของฉันที่มี 32 แถว ครอบคลุมภูมิภาค, หมวดหมู่อุปกรณ์ และพนักงานขาย การดึงการขายอุปกรณ์อิเล็กทรอนิกส์ทุกรายการจากภูมิภาคตะวันตก เขียนได้แบบนี้:
=FILTER(A2:G33, (B2:B33="West")*(C2:C33="Electronics"))
อาร์กิวเมนต์แรกคือช่วงที่ต้องการคืนค่า อาร์กิวเมนต์ที่สองคือเงื่อนไข และเครื่องหมายคูณระหว่างสองการตรวจสอบทำหน้าที่เป็น AND — ทั้งสองต้องเป็นจริง การเปลี่ยนเครื่องหมายดอกจัน (*) เป็นเครื่องหมายบวก (+) ทำให้กลายเป็น OR และผลลัพธ์จะกระจาย (spill) อัตโนมัติ การเพิ่มแถวใหม่ในข้อมูลต้นทางจะอัปเดตเอาต์พุตที่กระจายทันทีที่กด Enter
คุณสามารถห่อ FILTER ด้วย IFERROR พร้อมอาร์กิวเมนต์ที่สามเพื่อจัดการผลลัพธ์ว่างเปล่า =FILTER(range, condition, "No matches") ป้องกันไม่ให้ชีตแสดงข้อผิดพลาด #CALC! เมื่อไม่มีแถวใดคุณสมบัติ
UNIQUE เปลี่ยนขั้นตอนลบซ้ำ 3 ขั้นตอนของฉันให้กลายเป็นเซลล์เดียว
Remove Duplicates ใช้ได้ แต่ไม่เคยอัปเดตเองให้ฉัน
ตัวเลือก Remove Duplicates ใต้แท็บ Data ใช้ได้สำหรับทำความสะอาดครั้งเดียว จุดอ่อนคือมันสร้างรายการคงที่ (static) เมื่อคุณคัดลอกคอลัมน์ไปที่ใหม่ รันกล่องโต้ตอบ หรือเรียงลำดับผลลัพธ์ คุณจะต้องทำทั้งหมดซ้ำเมื่อใครเพิ่มแถวใหม่ ฉันทำแบบนี้มาเยอะมาก
UNIQUE ข้ามทุกขั้นตอนนั้นไปเลย ชี้ไปที่คอลัมน์พนักงานขายในสเปรดชีตขายของฉัน:
=UNIQUE(D2:D33)
ผลลัพธ์กระจายออกมาเป็น John Smith, Sarah Johnson, Mike Wilson, Lisa Brown, David Chen, Emma Davis, Tom Rodriguez และ Amy Foster นั่นคือ 8 ชื่อโดยไม่ต้องใช้กล่องโต้ตอบ การห่อด้วย =SORT(UNIQUE(D2:D33)) คืนรายการเดียวกันแต่เรียงตามตัวอักษร เอาต์พุตยังเชื่อมต่อกับต้นทางอยู่ ดังนั้นการเพิ่มชื่อใหม่ในข้อมูลจะขยายรายการที่กระจายโดยอัตโนมัติ
การตั้งค่านี้ยังทำแหล่งที่มาที่สะอาดสำหรับรายการหล่น (dropdown) ของ Data Validation หากคุณอ้างอิงช่วงที่กระจายด้วยเครื่องหมายแฮชเช่น D35# dropdown ของคุณจะขยายเองเมื่อมีชื่อใหม่ปรากฏในข้อมูลต้นทาง
SORTBY จบสิ้นการคัดลอก-วาง-แล้วเรียงของฉัน
เรียงลำดับมุมมองโดยไม่แตะต้องข้อมูลต้นทาง
การเรียงลำดับใน Excel เสมอมีความเสี่ยงเล็กน้อย การจัดเรียงข้อมูลต้นทางใหม่อาจทำลายสูตรที่อ้างอิงแถวคงที่ ดังนั้นวิธีแก้ของฉันคือคัดลอกชุดข้อมูลไปที่ปลอดภัยแล้วเรียงลำดับสำเนา SORT ช่วยได้ แต่เฉพาะตอนที่ฉันยอมแสดงทุกคอลัมน์ที่ใช้เรียง
SORTBY กำจัดข้อจำกัดนั้น มันเรียงช่วงหนึ่งโดยใช้ค่าจากอีกช่วงหนึ่ง และช่วงที่สองไม่จำเป็นต้องปรากฏในเอาต์พุต ฉันใช้สูตรนี้เพื่อจัดอันดับพนักงานขายตามรายได้ในชุดข้อมูลของฉัน:
=SORTBY(D2:D33, G2:G33, -1)
อาร์กิวเมนต์แรกคือสิ่งที่ต้องการคืนค่า (พนักงานขาย) อาร์กิวเมนต์ที่สองคือสิ่งที่ใช้เรียง (รายได้จากการขาย) และ -1 กำหนดให้เรียงจากมากไปน้อย ชื่อจะกลับมาจัดอันดับจากยอดขายสูงสุดไปต่ำสุด และคอลัมน์ยอดขายไม่เคยปรากฏในเอาต์พุตเว้นแต่คุณขอ
มันทำงานร่วมกับ FILTER ได้ดีมาก การห่อ FILTER(D2:G33, B2:B33="North") ไว้ใน SORTBY จะคืนเฉพาะระเบียนภูมิภาคเหนือ เรียงจากสูงไปต่ำ ในสูตรเดียว ข้อมูลต้นทางคงอยู่ที่เดิม
SORTBY รองรับหลายระดับการเรียง คุณสามารถเพิ่มคู่ช่วง/ลำดับเพื่อเรียงตามภูมิภาคก่อน แล้วตามด้วยยอดขายภายในแต่ละภูมิภาค
SEQUENCE แทนที่ Fill Handle และเทคนิค ROW ที่ฉันเคยภูมิใจ
สร้างลำดับตัวเลขโดยไม่ลากเซลล์เดียว
การสร้างชุดตัวเลขลำดับเคยหมายถึงการลาก fill handle จนเหนื่อย หรือเขียน =ROW(A1) แล้วคัดลอกลงไป ทั้งสองวิธีใช้ได้ แต่ไม่มีอะไรปรับขนาดเมื่อข้อมูลต้นทางเปลี่ยน
SEQUENCE จัดการงานเดียวกันในเซลล์เดียว และสร้างแถว, คอลัมน์ หรือกริดเต็มรูปแบบขึ้นอยู่กับอาร์กิวเมนต์ ไวยากรณ์คือ:
=SEQUENCE(rows, [columns], [start], [step])
เพื่อตั้งเลขลำดับ 8 พนักงานขายที่ไม่ซ้ำกันในข้อมูลของฉัน เริ่มที่ 1001 ฉันใช้สูตร:
=SEQUENCE(8, 1, 1001, 1)
นี่คืนค่า 1001 ถึง 1008 ในคอลัมน์เดียว การตั้งคอลัมน์เป็น 1 และ step เป็น 1 ทำให้เป็นรายการแนวตั้งด้วยจำนวนเต็มติดกัน SEQUENCE ส่องสว่างได้เมื่อใช้ภายในฟังก์ชันอื่น การสร้างช่วงวันที่ 31 วันสำหรับมกราคม 2026 ใช้สูตรเดียว:
=DATE(2026, 1, SEQUENCE(31))
การนำอาร์เรย์ที่กระจายไปใส่ใน TEXT, INDEX หรือฟังก์ชันอื่นที่คาดหวังช่วงข้อมูล ทำให้สิ่งที่เคยต้องใช้คอลัมน์ช่วยหดตัวเหลือเพียงสูตรเดียว ฉันเคยเขียนรายละเอียดเพิ่มเติมเกี่ยวกับการใช้ SEQUENCE เติมคอลัมน์วันที่ และมันยังคงประหยัดเวลาให้ฉันในทุกปฏิทินหรือตารางเวลาที่สร้าง
ฟังก์ชันที่ฉันอยากเจาะลึกต่อไป
ไม่มีวิธีเก่าไหนเสียหาย พวกมันแค่ใช้เวลานานกว่า และสร้างไฟล์ที่ยากต่อการดูแลรักษา — ทั้งสำหรับฉันและคนอื่นที่เปิดเวิร์กบุ๊กภายหลัง สำหรับฉัน การเปลี่ยนแปลงคือสิ่งที่ฉันหยิบมาใช้โดยค่าเริ่มต้น สเปรดชีตใหม่เริ่มต้นด้วยการคิดแบบอาร์เรย์ไดนามิก และชุดเครื่องมือเก่าออกมาเฉพาะตอนแก้ไขไฟล์ของคนอื่น ต่อไปในรายการคือ TEXTSPLIT สำหรับแยกสตริง, VSTACK และ HSTACK สำหรับรวมช่วง และ PIVOTBY และ GROUPBY สำหรับข้าม Pivot Table ไปเลย แต่ละการเผยแพร่ดูเหมือนจะเกษียณอีกนิสัยหนึ่ง ซึ่งโอเคก็ได้
