ดัชนี/การจับคู่/ผลรวมหลายแถวและคอลัมน์

ฉันกำลังพยายามหาผลรวมตั้งแต่ต้นปีจนถึงปัจจุบันสำหรับบัญชีใดบัญชีหนึ่ง สมมติว่าตอนนี้เราอยู่ในเดือนกรกฎาคม ยอดรวมตั้งแต่ต้นปีจนถึงปัจจุบันสำหรับบัญชี 5340 ควรเป็น 2800

ป้อนคำอธิบายรูปภาพที่นี่

ในสูตรด้านล่าง AT29=5340 และ AT28=7

ฉันเหนื่อย

=SUM(INDEX(AV$4:AV$25,MATCH(AT29,AU$4:AU$24,0)):INDEX(AV$4:BG$25,MATCH(AT29,AU$4:AU$24,0),7))แต่นั่นทำให้ฉันพบแถวแรกเท่านั้น

=SUMPRODUCT((AU4:AU24=AT29)*AV4:BG24)แต่นั่นทำให้ฉันตลอดทั้งปี

=SUMPRODUCT((AU4:AU24=AT29)*INDEX(AV4:BG24,,AT28))แต่นั่นแค่ให้ผลรวมสำหรับเดือนกรกฎาคมเท่านั้น

=SUMPRODUCT((AU4:AU24=AT29)*(SUM(INDEX(AV4:AV24,,1):INDEX(AV4:BG24,,AT28))))แต่นั่นทำให้ฉันมีบางอย่างที่ฉันไม่รู้ว่ามันคืออะไร XD


excel excel-formula
person findwindow    schedule 21.03.2016    source แหล่งที่มา
comment
ตัวเลขใน AU เรียงตามลำดับเสมอหรือไม่? เพราะการปรับเปลี่ยนสูตรแรกเล็กน้อยน่าจะได้ผล =SUM(INDEX(AV$4:AV$25,MATCH(AT29,AU$4:AU$24,0)):INDEX(AV$4:BG$25,MATCH(AT29,AU$4:AU$24,0)+countif(AU$4:AU$24,AT29)-1,7))   -  person Scott Craner    schedule 01.04.2016
comment
@ScottCraner ใครลบโพสต์ของฉันทั้งหมด ;_;   -  person findwindow    schedule 01.04.2016
comment
ชุมชนทำ. ฉันเดาว่าพวกเขาเห็นด้วยกับซิดด์ว่าที่นี่ไม่ใช่ที่สำหรับมัน ฉันก็สนุกนะ ขอบคุณ   -  person Scott Craner    schedule 01.04.2016
comment
อ่า :/ผมถอยออกไปก็หมดแล้ว ขอบคุณที่ทำให้ฉันมีอารมณ์ขัน! อืม ฉันต้องศึกษาสูตรของคุณ...   -  person findwindow    schedule 01.04.2016
comment
@ScottCraner สอนให้ฉันเป็นเหมือนคุณ ~ หากคุณต้องการตอบฉันโหวตมัน   -  person findwindow    schedule 02.04.2016

คำตอบ (2)


arrow_upward
2
arrow_downward

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

หากต้องการระบุพื้นที่ที่คุณจะสรุป ให้เริ่มต้นที่มุมซ้ายบนด้านบนและด้านซ้ายของข้อมูล สมมติว่าเป็น A1 (จากตัวอย่างของคุณฉันไม่สามารถบอกได้ว่าจริงๆ แล้วหน้าเว็บของคุณตั้งค่าอย่างไร) คุณจะต้องเลื่อนลงตามจำนวนแถวจนกว่าคุณจะตรงกับบัญชีที่คุณกำลังมองหา คุณจะต้องย้ายไปทางขวา 1 คอลัมน์ เนื่องจากคุณจะเริ่มต้นในเดือนมกราคมเสมอ จากนั้นให้คุณค้นหาแถวให้มากที่สุดเท่าที่มีหมายเลขบัญชีที่ตรงกันภายในข้อมูลของคุณ คุณจะใช้คอลัมน์ได้มากเท่าที่มีเดือนในไทม์ไลน์การทดสอบของคุณ เมื่อรวมกันแล้วจะมีลักษณะดังนี้:

=SUM(OFFSET(A1,MATCH(A12,A1:A9,0),1,COUNTIFS(A2:A9,A12),A13))

หมายเหตุ: การดำเนินการนี้จะถือว่าบัญชีของคุณเริ่มตั้งแต่ A2:A9 และคุณได้พิมพ์หมายเลขบัญชีที่ระบุในเซลล์ A12 และคุณได้คำนวณจำนวนเดือนในปีจนถึงปัจจุบันในเซลล์ A13

TL;DR: รวมกล่อง 2D ที่เริ่มต้นที่ด้านซ้ายบนด้วยอินสแตนซ์แรกของบัญชีนั้นๆ ลงไปตามแถวต่างๆ ตามที่มีอินสแตนซ์ของบัญชีนั้น และไปทางขวาสำหรับ กี่คอลัมน์ตามจำนวนเดือนในหนึ่งปีจนถึงปัจจุบัน

person Grade 'Eh' Bacon    schedule 21.03.2016
comment
เซลล์ 5000 เปิดอยู่ AU4 ฉันจึงทำ =SUM(OFFSET(AU4,MATCH(AT29,AU4:AU24,0),1,COUNTIFS(AU4:AU24,AT29),AT28)) แต่ล้มเหลว ไม่คุ้นเคยกับ offset ดังนั้นจะลองดูว่าฉันกำลังทำอะไรอยู่.... - person findwindow; 21.03.2016
comment
อืมสูตรน่าจะใช้ได้ offset อาร์กิวเมนต์คือ 10, 1, 4 และ 7 ซึ่งถูกต้อง... แล้วมีอะไรผิด XD - person findwindow; 21.03.2016
comment
@findwindow คุณจะต้องให้สูตรนี้เริ่มต้นที่ AU3 ไม่ใช่ AU4 เนื่องจากคุณได้เลื่อนลงไปหลายแถวเท่ากับจุดที่คุณมี AT29 ที่ตรงกับ - เป็นไปได้มากว่าคุณจะเลื่อนลงไป 1 แถวมากเกินไปในตอนนี้ - person Grade 'Eh' Bacon; 21.03.2016
comment
โอ้ใช่แล้ว ถ้า 1 หมายถึง 1 คอลัมน์ทับ 10 หมายถึง 10 แถวด้านล่าง แต่ฉันต้องการ 11 XD ขอบคุณ! - person findwindow; 21.03.2016

arrow_upward
2
arrow_downward

หากตัวเลขใน AU เรียงตามลำดับ สูตรที่ไม่ลบเลือนต่อไปนี้จะใช้ได้:

=SUM(INDEX(AV$4:AV$25,MATCH(AT29,AU$4:AU$24,0)):INDEX(AV$4:BG$25,MATCH(AT29,AU$‌​4:AU$24,0)+COUNTIF(AU$4:AU$24,AT29)-1,7))

การใช้ COUNTIF() จะขยายแถวตามจำนวนแถวที่ถูกต้อง

person Scott Craner    schedule 01.04.2016