Indeks/Pencocokan/Sumproduk beberapa baris dan kolom

Saya mencoba mendapatkan total tahun ini untuk akun tertentu. Misalkan kita sekarang berada di bulan Juli. Total tahun ini untuk akun 5340 seharusnya adalah 2800.

masukkan deskripsi gambar di sini

Dalam rumus di bawah ini, AT29=5340 dan AT28=7

saya telah mencoba

=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)) tapi itu hanya memberi saya baris pertama yang ditemukan.

=SUMPRODUCT((AU4:AU24=AT29)*AV4:BG24) tapi itu memberiku waktu satu tahun penuh.

=SUMPRODUCT((AU4:AU24=AT29)*INDEX(AV4:BG24,,AT28)) tapi itu hanya memberi saya jumlah untuk bulan Juli saja.

=SUMPRODUCT((AU4:AU24=AT29)*(SUM(INDEX(AV4:AV24,,1):INDEX(AV4:BG24,,AT28)))) tapi itu memberiku sesuatu yang aku tidak tahu apa itu XD


excel excel-formula
person findwindow    schedule 21.03.2016    source sumber
comment
Apakah angka-angka di AU selalu berurutan? karena sedikit memodifikasi rumus pertama Anda akan berhasil. =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 Siapa yang menghapus semua postingan saya ;_;   -  person findwindow    schedule 01.04.2016
comment
Komunitas melakukannya. Saya kira mereka setuju dengan sidd bahwa ini bukanlah tempat yang tepat untuk itu. Saya bersenang-senang, terima kasih.   -  person Scott Craner    schedule 01.04.2016
comment
Ah :/ Aku menjauh dan semuanya hilang. Terima kasih telah menghiburku! Hmmm aku harus mempelajari rumusmu...   -  person findwindow    schedule 01.04.2016
comment
@ScottCraner Ajari saya untuk menjadi seperti Anda ~ Jika Anda ingin menjawabnya, saya upvote.   -  person findwindow    schedule 02.04.2016

Jawaban (2)


arrow_upward
2
arrow_downward

Karena data Anda diurutkan pada kolom indeks utama, Anda dapat melakukan ini dengan satu fungsi SUM di atas satu fungsi OFFSET.

Untuk menentukan luas area yang akan Anda jumlahkan, mulailah dari sudut kiri atas di atas dan di sebelah kiri data Anda - katakanlah A1 (saya tidak tahu dari contoh Anda bagaimana halaman Anda sebenarnya diatur). Anda ingin menurunkan jumlah baris sampai Anda COCOK dengan akun yang Anda cari. Anda sebaiknya pindah ke 1 kolom kanan, karena Anda akan selalu memulai di bulan Januari. Kemudian Anda mencari baris sebanyak yang cocok untuk nomor rekening itu dalam data Anda. Anda memilih kolom sebanyak jumlah bulan dalam timeline pengujian Anda. Secara keseluruhan, ini terlihat seperti:

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

Catatan: Ini mengasumsikan akun Anda berasal dari A2:A9, dan Anda telah mengetikkan nomor akun tertentu di sel A12, dan Anda telah menghitung jumlah bulan dalam satu tahun hingga saat ini di sel A13.

TL;DR: menjumlahkan kotak 2D yang dimulai di kiri atas dengan instance pertama dari akun tertentu, turun ke baris sebanyak jumlah instance dari akun tersebut, dan ke kanan untuk kolom sebanyak jumlah bulan dalam satu tahun hingga saat ini.

person Grade 'Eh' Bacon    schedule 21.03.2016
comment
Sel 5000 ada di AU4 jadi saya melakukan =SUM(OFFSET(AU4,MATCH(AT29,AU4:AU24,0),1,COUNTIFS(AU4:AU24,AT29),AT28)) tetapi gagal. Tidak akrab dengan offset jadi akan mencarinya dan melihat apa yang saya lakukan.... - person findwindow; 21.03.2016
comment
Hmm rumusnya seharusnya berhasil. offset argumennya 10, 1, 4 dan 7 mana yang benar...lalu apa yang salah XD - person findwindow; 21.03.2016
comment
@findwindow Anda ingin rumus ini dimulai pada AU3, bukan AU4. Ini karena Anda telah berpindah ke bawah sejumlah baris yang sama dengan titik yang Anda miliki MATCH'd AT29 - jadi kemungkinan besar Anda turun 1 terlalu banyak baris sekarang. - person Grade 'Eh' Bacon; 21.03.2016
comment
Oh ya ya. Kalau 1 berarti 1 kolom di atas, 10 berarti 10 baris ke bawah tapi saya butuh 11 XD Terima kasih! - person findwindow; 21.03.2016

arrow_upward
2
arrow_downward

JIKA angka-angka dalam AU berurutan maka rumus non-volatil berikut akan berhasil:

=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))

Menggunakan COUNTIF() akan memperpanjang baris dengan jumlah baris yang benar.

person Scott Craner    schedule 01.04.2016