รวมทุกแถวหรือคอลัมน์ที่ n ในช่วงใน Excel

บทช่วยสอนนี้จะแสดงวิธีสร้างสูตรตามฟังก์ชัน SUMPRODUCT, MOD และ COLUMN เพื่อรวมทุกแถวหรือคอลัมน์ที่ n ในช่วงใน Excel

วิธีการรวมทุกแถวหรือคอลัมน์ที่ n ในช่วงที่กำหนดใน Excel?

ดังที่แสดงไว้ด้านบน ในการรวมทุกแถวที่สามในคอลัมน์จำนวนเงิน คุณสามารถใช้สูตรด้านล่างได้

สูตรทั่วไป

=SUMPRODUCT(--(MOD(ROW(value_range)-ROW(first_cell)+1,n)=0),value_range)

ข้อโต้แย้ง

ค่า_ช่วง: ช่วงของค่าที่จะรวม;

เซลล์แรก: เซลล์แรกใน value_range;

N: ตัวเลขที่แทนแถวที่ n ทุกแถวที่ใช้หาผลรวม

จะใช้สูตรนี้ได้อย่างไร?

1. เลือกเซลล์ว่าง

2. คัดลอกหรือป้อนสูตรด้านล่างลงไปแล้วกด เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์

=SUMPRODUCT(--(MOD(ROW(E5:E14)-ROW(E5)+1,3)=0),E5:E14)

หมายเหตุ: ในการรวมทุกคอลัมน์ที่ n ในช่วงแถวใดแถวหนึ่ง คุณเพียงแค่แทนที่ ROW ทั้งหมดในสูตรด้วย COLUMN เพื่อให้เสร็จดังนี้

=SUMPRODUCT(--(MOD(COLUMN(C6:H6)-COLUMN(C6)+1,3)=0),C6:H6)

คำอธิบายของสูตร

=SUMPRODUCT(--(MOD(ROW(E5:E14)-ROW(E5)+1,3)=0),E5:E14)

แถว(E5:E14)-แถว(E5)+1:

> ROW(E5:E14): ฟังก์ชัน ROW รับหมายเลขแถวสัมพันธ์สำหรับช่วง E5:E14 และส่งคืนอาร์เรย์ดังนี้: {5;6;7;8;9;10;11;12;13;14} ;

> ROW(E5): ฟังก์ชัน ROW จะคืนค่าแถวที่ 5 สำหรับเซลล์ E5;

> {5;6;7;8;9;10;11;12;13;14}-{5}+1: จากนั้นแต่ละตัวเลขในอาร์เรย์จะลบเลข 5 แล้วบวก 1 เพื่อให้ได้อาร์เรย์ใหม่: {1; 2;3;4;5;6;7;8;9;10}.

MOD(({1;2;3;4;5;6;7;8;9;10},3): ฟังก์ชัน MOD จะคืนค่าส่วนที่เหลือของแต่ละตัวเลขในอาร์เรย์หารด้วย 3 (ในที่นี้ ตัวเลข 3 คือค่าของ N): {1;2;0;1;2;0;1;2;0;1}; ในอาร์เรย์นี้ 0s แทนแถวที่ 3, 6, 9 เป็นต้น

SUMPRODUCT(--({1;2;0;1;2;0;1;2;0;1}=0),E5:E14):

> --({1;2;0;1;2;0;1;2;0;1}=0): ที่นี่ เศษที่เหลือเปรียบเทียบกับ 0 เพื่อบังคับอาร์เรย์ TRUE หรือ FALSE เช่นนี้ {FALS;FALSE;TRUE; เท็จ;เท็จ;จริง;เท็จ;เท็จ;จริง;เท็จ}; จากนั้นเครื่องหมายลบทั้งสองจะแปลง "TRUE" และ "FALSE" เป็น 1s และ 0s: {0;0;1;0;0;1;0;0;1;0};

> ผลรวม({0;0;1;0;0;1;0;0;1;0},E5:E14)=SUMPRODUCT({0;0;1;0;0;1;0;0;1; 0},{1428;2010;2288;1831;2069;1728;1325;1469;1023;1100}): แต่ละคู่ของรายการในอาร์เรย์จะถูกคูณเข้าด้วยกันและส่งกลับ {0;0;2288;0;0;1728 ;0;0;1023;0} จากนั้นฟังก์ชัน SUMPRODUCT จะรวมตัวเลขทั้งหมดในอาร์เรย์และส่งกลับผลลัพธ์สุดท้ายเป็น 5039

ฟังก์ชันที่เกี่ยวข้อง

ฟังก์ชัน Excel SUMPRODUCT
ฟังก์ชัน Excel SUMPRODUCT สามารถใช้ในการคูณคอลัมน์หรืออาร์เรย์ตั้งแต่สองคอลัมน์ขึ้นไปเข้าด้วยกัน จากนั้นจึงหาผลรวมของผลิตภัณฑ์

ฟังก์ชัน Excel MOD
ฟังก์ชัน Excel MOD จะคืนค่าส่วนที่เหลือหลังจากที่ตัวเลขถูกหารด้วยตัวหาร

ฟังก์ชัน Excel ROW
ฟังก์ชัน Excel ROW จะคืนค่าหมายเลขแถวของข้อมูลอ้างอิง

ฟังก์ชัน Excel COLUMN
ฟังก์ชัน Excel COLUMNS จะคืนค่าจำนวนคอลัมน์ที่สูตรปรากฏขึ้นหรือส่งกลับหมายเลขคอลัมน์ของข้อมูลอ้างอิงที่กำหนด

สูตรที่เกี่ยวข้อง

รวมมูลค่าตามเดือน (มีหรือไม่มีปี)
บทช่วยสอนนี้แสดงรายละเอียดสองสูตรโดยละเอียดเพื่อช่วยในการรวบรวมค่าตามเดือนและปีหนึ่งๆ หรือการรวมค่าในแต่ละเดือนโดยไม่สนใจปีใน Excel

รวมค่าตามสัปดาห์ของวันที่ที่กำหนด
บทช่วยสอนนี้พูดถึงวิธีรวมค่าต่างๆ ในสัปดาห์ที่วันที่ที่ระบุตก

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

รวมทุก n แถวหรือคอลัมน์ในช่วง
บทช่วยสอนนี้อธิบายสูตรสองสูตรตามฟังก์ชัน SUM และ OFFSET เพื่อรวมทุก n แถวหรือคอลัมน์ในช่วงที่ระบุใน Excel

เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด

Kutools สำหรับ Excel - ช่วยให้คุณโดดเด่นจากฝูงชน

คุณต้องการทำงานประจำวันของคุณให้เสร็จอย่างรวดเร็วและสมบูรณ์แบบหรือไม่? Kutools สำหรับ Excel นำคุณสมบัติขั้นสูงที่มีประสิทธิภาพ 300 รายการ (รวมสมุดงาน, รวมตามสี, แยกเนื้อหาของเซลล์, แปลงวันที่และอื่น ๆ ... ) และประหยัดเวลาให้คุณ 80%

ออกแบบมาสำหรับสถานการณ์การทำงาน 1500 สถานการณ์ ช่วยคุณแก้ปัญหา Excel 80%
ลดการคลิกแป้นพิมพ์และเมาส์หลายพันครั้งต่อวันช่วยบรรเทาอาการเมื่อยล้าของดวงตาและมือ
เป็นผู้เชี่ยวชาญด้าน Excel ใน 3 นาที ไม่จำเป็นต้องจำสูตรและรหัส VBA ที่เจ็บปวดอีกต่อไป
ทดลองใช้ฟรี 30 วันไม่ จำกัด รับประกันคืนเงิน 60 วัน อัปเกรดและสนับสนุนฟรี 2 ปี

Ribbon ของ Excel (ติดตั้ง Kutools for Excel)

อ่านเพิ่มเติม ดาวน์โหลด

แท็บ Office - เปิดใช้งานการอ่านแบบแท็บและการแก้ไขใน Microsoft Office (รวม Excel)

หนึ่งวินาทีเพื่อสลับไปมาระหว่างเอกสารที่เปิดอยู่มากมาย!
ลดการคลิกเมาส์หลายร้อยครั้งสำหรับคุณทุกวันบอกลามือเมาส์
เพิ่มประสิทธิภาพการทำงานของคุณได้ถึง 50% เมื่อดูและแก้ไขเอกสารหลายฉบับ
นำแท็บที่มีประสิทธิภาพมาสู่ Office (รวมถึง Excel) เช่นเดียวกับ Chrome, Firefox และ Internet Explorer ใหม่