Cari dan ambil seluruh kolom
Untuk mencari dan mengambil seluruh kolom dengan mencocokkan nilai tertentu, dan INDEKS dan MATCH formula akan membantu Anda.
Cari dan ambil seluruh kolom berdasarkan nilai tertentu
Jumlahkan seluruh kolom berdasarkan nilai tertentu
Analisis lebih lanjut dari seluruh kolom berdasarkan nilai tertentu
Cari dan ambil seluruh kolom berdasarkan nilai tertentu
Untuk mendapatkan daftar penjualan Q2 sesuai dengan tabel di atas, Anda dapat menggunakan fungsi MATCH terlebih dahulu untuk mengembalikan posisi penjualan Q2, dan yang akan diumpankan ke INDEX untuk mengambil nilai pada posisi tersebut.
Sintaks umum
=INDEX(return_range,0,MATCH(lookup_value,lookup_array,0))
Catatan: Ini adalah rumus array yang mengharuskan Anda memasukkan dengan Ctrl + perubahan + Enter.
- rentang_kembali: Rentang tempat Anda ingin formula kombinasi mengembalikan daftar penjualan Q2. Di sini mengacu pada kisaran penjualan.
- nilai lookup: Nilai yang digunakan rumus kombinasi untuk menemukan informasi penjualan yang sesuai. Di sini mengacu pada kuartal yang diberikan.
- pencarian_array: Rentang sel yang cocok dengan nilai lookup. Di sini mengacu pada header kuartal.
- kecocokan_tipe 0: Memaksa MATCH untuk menemukan nilai pertama yang persis sama dengan nilai lookup.
Untuk mendapatkan daftar penjualan Q2, silahkan copy atau masukkan rumus dibawah ini pada cell I6, tekan Ctrl + perubahan + Enter, lalu klik dua kali pada sel dan tekan F9 untuk mendapatkan hasil:
=INDEKS(C5:F11,0,cocok("Q2",C4:F4, 0))
Atau, gunakan referensi sel untuk membuat rumus dinamis:
=INDEKS(C5:F11,0,cocok(I5,C4:F4, 0))
Penjelasan rumus
=INDEX(C5:F11,0,MATCH(I5,C4:F4,0))
- PERTANDINGAN(I5,C4:F4,0): Grafik kecocokan_tipe 0 memaksa fungsi MATCH untuk mengembalikan posisi Q2, nilai dalam I5, dalam kisaran C4:F4, Yang 2.
- INDEKS(C5:F11, 0,PERTANDINGAN(I5,C4:F4,0)) = INDEKS(C5:F11, 0,2): Fungsi INDEX mengembalikan semua nilai dalam 2kolom ke-n dari jangkauan C5:F11 dalam array seperti ini: {7865;4322;8534;5463;3252;7683;3654}. Perhatikan bahwa untuk membuat array terlihat di Excel, Anda harus mengklik dua kali pada sel tempat Anda memasukkan rumus, lalu tekan F9.
Jumlahkan seluruh kolom berdasarkan nilai tertentu
Karena kami memiliki daftar penjualan sekarang, untuk mendapatkan Total volume penjualan Q2 akan menjadi kasus yang mudah bagi kita. Yang perlu kita lakukan adalah menambahkan fungsi SUM ke rumus untuk menjumlahkan semua nilai penjualan dari daftar.
Sintaks umum
=SUM(INDEX(return_range,0,MATCH(lookup_value,lookup_array,0)))
Dalam contoh ini, untuk mendapatkan Total volume penjualan Q2, silakan salin atau masukkan rumus di bawah ini di sel I8, dan tekan Enter untuk mendapatkan hasil:
=JUMLAH(INDEKS(C5:F11,0,cocok(I5,C4:F4,0)))
Penjelasan rumus
=SUM(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))
- PERTANDINGAN(I5,C4:F4,0): Grafik kecocokan_tipe 0 memaksa fungsi MATCH untuk mengembalikan posisi Q2, nilai dalam I5, dalam kisaran C4:F4, Yang 2.
- INDEKS(C5:F11, 0,PERTANDINGAN(I5,C4:F4,0)) = INDEKS(C5:F11, 0,2): Fungsi INDEX mengembalikan semua nilai dalam 2kolom ke-n dari jangkauan C5:F11 dalam array seperti ini: {7865;4322;8534;5463;3252;7683;3654}.
- JUMLAH(INDEKS(C5:F11, 0,PERTANDINGAN(I5,C4:F4,0))) = JUMLAH({7865;4322;8534;5463;3252;7683;3654}): Fungsi SUM menjumlahkan semua nilai dalam array, dan kemudian mendapatkan total volume penjualan Q2, $40,773.
Analisis lebih lanjut dari seluruh kolom berdasarkan nilai tertentu
Untuk pemrosesan tambahan pada daftar penjualan Q2, Anda cukup menambahkan fungsi lain seperti SUM, AVERAGE, MAX, MIN, LARGE, dll. ke rumus.
Misalnya untuk mendapatkan rata-rata volume penjualan selama Q2, Anda dapat menggunakan rumus:
= RATA-RATA(INDEKS(C5:F11,0,cocok(I5,C4:F4,0)))
Untuk mengetahui penjualan tertinggi selama Q2, gunakan salah satu rumus di bawah ini:
=MAX(INDEKS(C5:F11,0,cocok(I5,C4:F4,0)))
OR
=BESAR(INDEKS(C5:F11,0,cocok(I5,C4:F4,0)),1)
Fungsi terkait
Fungsi INDEX Excel mengembalikan nilai yang ditampilkan berdasarkan posisi tertentu dari rentang atau larik.
Fungsi Excel MATCH mencari nilai tertentu dalam rentang sel, dan mengembalikan posisi relatif dari nilai tersebut.
Rumus Terkait
Untuk mencari dan mengambil seluruh baris data dengan mencocokkan nilai tertentu, Anda bisa menggunakan fungsi INDEX dan MATCH untuk membuat rumus array.
Pencocokan persis dengan INDEX dan MATCH
Jika Anda perlu mengetahui informasi yang tercantum di Excel tentang produk, film, atau orang tertentu, dll., Anda harus memanfaatkan kombinasi fungsi INDEX dan MATCH dengan baik.
Perkiraan kecocokan dengan INDEX dan MATCH
Ada kalanya kita perlu menemukan perkiraan kecocokan di Excel untuk mengevaluasi kinerja karyawan, menilai nilai siswa, menghitung ongkos kirim berdasarkan berat, dll. Dalam tutorial ini, kita akan berbicara tentang cara menggunakan fungsi INDEX dan MATCH untuk mengambil hasil yang kita butuhkan.
Pencarian peka huruf besar/kecil
Anda mungkin tahu bahwa Anda dapat menggabungkan fungsi INDEX dan MATCH, atau menggunakan fungsi VLOOKUP untuk mencari nilai di Excel. Namun, pencarian tidak peka huruf besar-kecil. Jadi, untuk melakukan pencocokan peka huruf besar-kecil, Anda harus memanfaatkan fungsi EXACT dan CHOOSE.
Alat Produktivitas Kantor Terbaik
Kutools for Excel - Membantu Anda Menonjol Dari Kerumunan
Kutools for Excel Membanggakan Lebih dari 300 Fitur, Memastikan Apa yang Anda Butuhkan Hanya Dengan Sekali Klik...
Tab Office - Aktifkan Pembacaan dan Pengeditan dengan Tab di Microsoft Office (termasuk Excel)
- Satu detik untuk beralih di antara lusinan dokumen terbuka!
- Kurangi ratusan klik mouse untuk Anda setiap hari, ucapkan selamat tinggal pada tangan mouse.
- Meningkatkan produktivitas Anda sebesar 50% saat melihat dan mengedit banyak dokumen.
- Menghadirkan Tab Efisien ke Office (termasuk Excel), Sama Seperti Chrome, Edge, dan Firefox.
