ใช้ TAKE และ DROP ของ Excel เพื่อสร้างรายการที่ปรับปรุงตัวเองได้อย่างยอดเยี่ยม

สูตร Excel หลายสูตรที่ฉันใช้สำหรับรายการต้องการการดูแล บางครั้งเมื่อฉันเพิ่มแถวใหม่ สิ่งต่างๆ จะเสียหรือจำเป็นต้องปรับ ฟังก์ชัน TAKE และ DROP ไม่มีปัญหานั้น ฟังก์ชันอาร์เรย์ไดนามิกทั้งสองนี้ ซึ่งทำให้ตารางขยายโดยอัตโนมัติ ช่วยให้คุณดึงหรือเอาแถวและคอลัมน์เฉพาะออกจากชุดข้อมูลและอัปเดตเองเมื่อข้อมูลของคุณเปลี่ยนแปลง

จับคู่พวกมันกับฟังก์ชันอย่าง SORT และ FILTER แล้วคุณจะได้รายการที่ดูแลตัวเองโดยไม่ต้องมีความพยายามเพิ่มเติม นี่คือวิธีที่ฉันใช้พวกมันในการสร้างรายการที่คงทันสมัยจริง ๆ

TAKE จะจับแถวหรือคอลัมน์ที่คุณต้องการอย่างแม่นยำ

ชี้ไปที่ข้อมูลของคุณ บอกจำนวนแถว และมันก็จัดการให้

TAKE ทำสิ่งหนึ่งได้ดี มันดึงจำนวนแถวหรือคอลัมน์ที่กำหนดจากช่วงข้อมูล โดยเริ่มจากด้านที่คุณระบุ คุณไม่จำเป็นต้องมีคอลัมน์ช่วยเหลือหรือการซ้อน INDEX-MATCH ที่ซับซ้อน คุณเพียงชี้ไปที่ข้อมูลของคุณ บอกว่าต้องการกี่แถว และมันก็จะส่งมอบให้

ต่อไปนี้คือไวยากรณ์:

=TAKE(array, rows, [columns])

• array: ช่วงแหล่งที่มา หรือ อาเรย์ที่คุณกำลังดึงข้อมูลออกมา
• rows: จำนวนแถวที่จะส่งกลับ ตัวเลขบวกดึงจากด้านบน; ตัวเลขลบดึงจากด้านล่าง
• คอลัมน์ (ไม่บังคับ): ทำงานในลักษณะเดียวกัน — จำนวนบวกเริ่มจากซ้าย จำนวนลบเริ่มจากขวา

สมมติว่าคุณมีข้อมูลยอดขายในช่วง A2:D20 และต้องการหาห้าแถวแรก คุณจะใช้สูตรดังต่อไปนี้:

=TAKE(A2:D20, 5)

ต้องการสามอันล่าสุดใช่ไหม? เปลี่ยนเป็น -3 ผลลัพธ์จะไหลโดยอัตโนมัติไปยังเซลล์ที่อยู่ข้างเคียง และมันจะคำนวณใหม่ทุกครั้งที่ข้อมูลต้นฉบับเปลี่ยนแปลง

DROP จะลบแถวหรือคอลัมน์ที่คุณไม่ต้องการ

บอกมันว่าต้องทิ้งอะไร แล้วมันจะส่งทุกอย่างที่เหลือกลับมา

DROP เป็นสิ่งตรงข้ามกับ TAKE แทนที่จะระบุสิ่งที่คุณต้องการเก็บ คุณบอกมันว่าต้องการทิ้งอะไร และมันจะคืนทุกอย่างที่เหลืออยู่ ฉันพบว่านี่เป็นวิธีที่เข้าใจง่ายกว่าในสถานการณ์ที่ฉันรู้จำนวนแถวที่ไม่เกี่ยวข้องตรงบนหรือด้านล่างของชุดข้อมูลอย่างชัดเจน

ไวยากรณ์สะท้อน TAKE:

=DROP(array, rows, [columns])

• array: ช่วงแหล่งที่มา หรือ อาร์เรย์
• แถว: จำนวนแถวที่จะยกเว้น ค่าบวกจะตัดจากด้านบน; ค่าลบจะตัดจากด้านล่าง
• คอลัมน์ (ไม่บังคับ): ค่าบวกจะลบจากด้านซ้าย ค่าลบจะลบจากด้านขวา

สมมติว่าชุดข้อมูลของคุณในช่วง A2:D20 มีสองแถวสรุปที่คล้ายหัวข้อซึ่งคุณไม่ต้องการ สูตรต่อไปนี้จะลบแถวเหล่านั้นและคืนค่าที่เหลือ

=DROP(A2:D20, 2)

คุณสามารถลบคอลัมน์ได้โดยใช้:

=DROP(A2:D20, 0, 1)

สิ่งนี้จะลบคอลัมน์แรก ซึ่งมีประโยชน์เมื่อฟิลด์ ID ทำให้ผลลัพธ์ของคุณรก เช่นเดียวกับ TAKE ผลลัพธ์จะล้นแบบไดนามิกและอัปเดตเมื่อข้อมูลของคุณเติบโต

รวม TAKE และ DROP เพื่อการแบ่งข้อมูลอย่างแม่นยำ

ซ้อนเข้าด้วยกันเพื่อแยกส่วนใดก็ได้จากตรงกลาง

ด้วยตัวเอง, ฟังก์ชัน TAKE และ DROP ดึงข้อมูลจากขอบของชุดข้อมูล อย่างไรก็ตาม หากคุณซ้อนพวกมันเข้าด้วยกัน คุณสามารถดึงส่วนใดก็ได้จากตรงกลาง ซึ่งเป็นสิ่งที่ฟังก์ชันทั้งสองไม่สามารถทำได้เพียงลำพัง

แนวคิดคือการใช้ DROP เพื่อลบแถวที่คุณไม่ต้องการที่ด้านบน จากนั้นใช้ TAKE ครอบผลลัพธ์เพื่อจำกัดจำนวนแถวที่คุณเก็บไว้ โดยใช้ข้อมูลการขายเป็นตัวอย่าง สมมติว่าคุณต้องการข้าม 4 รายการแรกและดึงอีก 8 รายการถัดไป ครอบคลุมธุรกรรมตั้งแต่วันที่ 12 มกราคมถึง 3 กุมภาพันธ์ สูตรจะเป็นดังนี้:

=TAKE(DROP(A2:D20, 4), 8)

DROP จะลบแถวสี่แถวแรก (รายการของวันที่ 5 มกราคม) และ TAKE จะคืนแถวถัดไปแปดแถวจากสิ่งที่เหลือ ผลลัพธ์รวมทุกคอลัมน์ทั้งสี่ ตั้งแต่ Date ถึง Salesperson ดังนั้นคุณจะได้ส่วนที่สมบูรณ์โดยไม่ยุ่งกับคอลัมน์ช่วยเหลือหรือเลือกช่วงด้วยตนเอง

นี่มีประโยชน์โดยเฉพาะเมื่อชุดข้อมูลของคุณเติบโตขึ้นตามเวลา เนื่องจากทั้งสองฟังก์ชันปรับตัวได้อย่างไดนามิกเมื่อมีการเพิ่มแถวใหม่

สร้างรายการ 'ท็อป N' ที่ปรับปรุงตัวเองด้วย TAKE และ SORT

รายการจะอัปเดตตัวเองทุกครั้งที่ข้อมูลของคุณเปลี่ยนแปลง

นี่คือจุดที่ TAKE เริ่มสร้างมูลค่าให้กับคุณ การจับคู่มันกับ SORT ช่วยให้คุณสร้างรายการที่แสดงค่าที่สูงที่สุด (หรือต่ำที่สุด) ในชุดข้อมูลของคุณเสมอ — และมันจะอัปเดตตัวเองทุกครั้งที่ข้อมูลเปลี่ยนแปลง

โดยใช้ข้อมูลการขายอีกครั้ง สมมติว่าคุณต้องการรายการธุรกรรมที่มีรายได้สูงสุดห้ารายการตามจำนวนการขาย สูตรคือ:

=TAKE(SORT(A2:J20, 7, -1), 5)

SORT จัดเรียงแถวทั้ง 19 แถวตามคอลัมน์ที่ 7 (ยอดขาย) ในลำดับจากมากไปน้อย และ TAKE ดึงห้าอันดับแรกจากผลลัพธ์ที่จัดเรียงแล้ว คุณจะได้แถวทั้งหมดสำหรับแต่ละรายการ วัน เดือน ปี ภูมิภาค พนักงานขาย และทุกอย่างอื่น ๆ ดังนั้นบริบทจึงยังคงครบถ้วน หากคุณไม่คุ้นเคยกับฟังก์ชัน SORT คู่มือของเราเกี่ยวกับการจัดเรียงข้อมูลใน Excel ครอบคลุมพื้นฐานทั้งหมด

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

สร้างรายการ "ข้อมูลล่าสุด" แบบไดนามิกด้วย DROP และ COUNTA

มันจะแสดงแถวล่าสุดอยู่เสมอ

รายการ 'top N' มีประโยชน์ แต่บางครั้งคุณอาจต้องการเพียงแค่ดูสิ่งที่เพิ่มเข้ามาล่าสุดในชุดข้อมูล การใช้ DROP ร่วมกับ COUNTA จัดการเรื่องนี้ได้ดี — มันนับจำนวนแถวที่มีอยู่และลบทุกอย่างยกเว้นไม่กี่แถวสุดท้าย

โดยใช้ข้อมูลการขาย นี่คือวิธีการแสดงรายการสุดท้ายห้ารายการเสมอ:

=DROP(A2:A20, COUNTA(A2:A20) - 5)

COUNTA นับทุกเซลล์ที่มีข้อมูลในคอลัมน์ A — ซึ่งในกรณีนี้มี 19 เซลล์ การลบ 5 จะได้ DROP 14 ดังนั้นมันจะลบ 14 แถวแรกและส่งคืน 5 แถวที่เหลือ ในตอนนี้ แถวเหล่านั้นจะเป็นธุรกรรมในเดือนมีนาคมจากพนักงานขายเช่น Mike Wilson ไปยัง Tom Rodriguez

หากคุณเพิ่มแถวใหม่สำหรับเดือนเมษายน ค่า COUNTA จะเพิ่มเป็น 20 ค่า DROP จะปรับเป็น 15 และผลลัพธ์จะแสดงรายการล่าสุดห้ารายการ คุณยังสามารถใช้สูตรต่อไปนี้เพื่อผลลัพธ์เดียวกันได้ แต่แนวทางของ DROP และ COUNTA จะยืดหยุ่นมากกว่าเมื่อช่วงของคุณไม่คงที่

=TAKE(A2:A20, -5)

TAKE และ DROP ทำงานได้ดียิ่งขึ้นเมื่อใช้กับฟังก์ชันอื่นๆ

ลองชุดค่าผสมเหล่านี้ต่อไป

คุณเคยเห็น TAKE กับ SORT และ DROP กับ COUNTA แล้ว แต่ยังมีสิ่งที่ต้องสำรวจเพิ่มเติม ใช้ FILTER ซ้อนกับ TAKE เพื่อสร้างรายการที่แสดง เช่น ยอดขายอุปกรณ์อิเล็กทรอนิกส์ที่มากกว่า $3,000 — จัดเรียงและจำกัดจำนวนตามที่กำหนด หรือใช้ DROP กับ UNIQUE เพื่อลบหัวเรื่องซ้ำเมื่อรวมข้อมูลจากหลายแผ่น CHOOSECOLS คู่กับ TAKE ก็สามารถลดขนาดชุดข้อมูลกว้างลงเหลือแค่คอลัมน์ที่สำคัญได้ เมื่อคุณเริ่มซ้อนฟังก์ชันเหล่านี้ การรวมกันจะใช้ประโยชน์จริงได้อย่างรวดเร็ว