Note: The other languages of the website are Google-translated. Back to English

Bagaimana mengembalikan beberapa nilai yang cocok berdasarkan satu atau beberapa kriteria di Excel?

Biasanya, mencari nilai tertentu dan mengembalikan item yang cocok itu mudah bagi kebanyakan dari kita dengan menggunakan fungsi VLOOKUP. Tapi, pernahkah Anda mencoba mengembalikan beberapa nilai yang cocok berdasarkan satu atau lebih kriteria seperti yang ditunjukkan gambar berikut? Pada artikel ini, saya akan memperkenalkan beberapa rumus untuk menyelesaikan tugas kompleks ini di Excel.

Kembalikan beberapa nilai yang cocok berdasarkan satu atau beberapa kriteria dengan rumus array


Kembalikan beberapa nilai yang cocok berdasarkan satu atau beberapa kriteria dengan rumus array

Misalnya, saya ingin mengekstrak semua nama yang usianya 28 dan berasal dari Amerika Serikat, harap terapkan rumus berikut:

1. Salin atau masukkan rumus di bawah ini ke dalam sel kosong di mana Anda ingin menemukan hasilnya:

=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Note: Dalam rumus di atas, B2: B11 adalah kolom tempat nilai yang cocok dikembalikan; F2, C2: C11 adalah kondisi pertama dan data kolom yang berisi kondisi pertama; G2, D2: D11 adalah kondisi kedua dan data kolom yang berisi kondisi ini, silahkan dirubah sesuai kebutuhan anda.

2. Lalu tekan Ctrl + Shift + Enter kunci untuk mendapatkan hasil pencocokan pertama, lalu pilih sel formula pertama dan seret pegangan isian ke sel sampai nilai kesalahan ditampilkan, sekarang, semua nilai yang cocok dikembalikan seperti gambar di bawah ini:

Tips: Jika Anda hanya perlu mengembalikan semua nilai yang cocok berdasarkan satu kondisi, harap terapkan rumus larik di bawah ini:

=IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1), ROW(1:1))),"" )


Artikel yang lebih relatif:

  • Kembalikan Beberapa Nilai Pencarian Dalam Satu Sel yang Dipisahkan Koma
  • Di Excel, kita dapat menerapkan fungsi VLOOKUP untuk mengembalikan nilai pertama yang cocok dari sel tabel, tetapi, terkadang, kita perlu mengekstrak semua nilai yang cocok dan kemudian dipisahkan oleh pemisah tertentu, seperti koma, tanda hubung, dll… menjadi satu sel seperti gambar berikut yang ditampilkan. Bagaimana kita bisa mendapatkan dan mengembalikan beberapa nilai pencarian dalam satu sel yang dipisahkan koma di Excel?
  • Vlookup Dan Kembalikan Beberapa Nilai Yang Cocok Sekaligus Di Google Sheet
  • Fungsi Vlookup normal di lembar Google dapat membantu Anda menemukan dan mengembalikan nilai pencocokan pertama berdasarkan data yang diberikan. Tapi, terkadang, Anda mungkin perlu vlookup dan mengembalikan semua nilai yang cocok seperti gambar berikut yang ditampilkan. Apakah Anda memiliki cara yang baik dan mudah untuk menyelesaikan tugas ini di lembar Google?
  • Vlookup Dan Kembalikan Beberapa Nilai Dari Daftar Drop Down
  • Di Excel, bagaimana Anda bisa vlookup dan mengembalikan beberapa nilai yang sesuai dari daftar drop-down, yang berarti ketika Anda memilih satu item dari daftar drop-down, semua nilai relatifnya ditampilkan sekaligus seperti gambar berikut yang ditampilkan. Artikel ini, saya akan memperkenalkan solusi langkah demi langkah.
  • Vlookup Dan Mengembalikan Beberapa Nilai Secara Vertikal Di Excel
  • Biasanya, Anda dapat menggunakan fungsi Vlookup untuk mendapatkan nilai pertama yang sesuai, tetapi terkadang Anda ingin mengembalikan semua rekaman yang cocok berdasarkan kriteria tertentu. Artikel ini, saya akan berbicara tentang cara vlookup dan mengembalikan semua nilai yang cocok secara vertikal, horizontal atau ke dalam satu sel.
  • Vlookup Dan Mengembalikan Data Yang Cocok Antara Dua Nilai Di Excel
  • Di Excel, kita dapat menerapkan fungsi Vlookup normal untuk mendapatkan nilai yang sesuai berdasarkan data yang diberikan. Tapi, terkadang, kami ingin vlookup dan mengembalikan nilai yang cocok antara dua nilai seperti yang ditunjukkan gambar layar berikut, bagaimana Anda bisa menangani tugas ini di Excel?

 


  • Bilah Formula Super (dengan mudah mengedit beberapa baris teks dan rumus); Membaca Tata Letak (membaca dan mengedit sel dalam jumlah besar dengan mudah); Tempel ke Rentang yang Difilter...
  • Gabungkan Sel / Baris / Kolom dan Menyimpan Data; Pisahkan Konten Sel; Gabungkan Baris Duplikat dan Jumlah / Rata-rata... Mencegah Sel Duplikat; Bandingkan Rentang...
  • Pilih Duplikat atau Unik Baris; Pilih Baris Kosong (semua sel kosong); Temukan Super dan Temukan Fuzzy di Banyak Buku Kerja; Pilih Acak ...
  • Salinan Tepat Beberapa Sel tanpa mengubah referensi rumus; Buat Referensi Otomatis ke Beberapa Lembar; Sisipkan Poin, Kotak Centang, dan lainnya ...
  • Favorit dan Sisipkan Rumus dengan Cepat, Rentang, Grafik dan Gambar; Enkripsi Sel dengan kata sandi; Buat Milis dan mengirim email ...
  • Ekstrak Teks, Tambahkan Teks, Hapus berdasarkan Posisi, Hapus Space; Membuat dan Mencetak Subtotal Paging; Konversi Konten Antar Sel dan Komentar...
  • Filter Super (simpan dan terapkan skema filter ke sheet lain); Penyortiran Lanjutan menurut bulan / minggu / hari, frekuensi dan lainnya; Filter Khusus dengan huruf tebal, miring ...
  • Gabungkan Workbooks dan WorkSheets; Gabungkan Tabel berdasarkan kolom kunci; Pisahkan Data menjadi Beberapa Lembar; Konversi Batch xls, xlsx dan PDF...
  • Pengelompokan Tabel Pivot menurut nomor minggu, hari dalam seminggu dan banyak lagi ... Tampilkan Sel yang Tidak Terkunci dan Terkunci dengan warna berbeda; Sorot Sel Yang Memiliki Rumus / Nama...
tab kte 201905
  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
officetab bawah

 

Urutkan komentar berdasarkan
komentar (25)
Belum ada peringkat. Jadilah yang pertama memberi peringkat!
Komentar ini diminimalkan oleh moderator di situs
Saya mencoba rumus yang sama persis ini; disalin 100%. Satu-satunya hal yang saya ubah adalah data yang dicocokkan dan dikembalikan. Saat saya menggunakan rumus ini, Excel mengatakan "Anda telah memasukkan terlalu banyak argumen untuk fungsi ini).=INDEX('2020 Volume Report'!$B$3:$B$100,SMALL(IF(COUNTIF($A$1,'2020 Volume Laporkan'!$A$3:$A$100)*COUNTIF($A$3,'Laporan Volume 2020'!$D$3:$D$100),ROW('Laporan Volume 2020'!$A$3:$G$100)- MIN(ROW('Laporan Volume 2020'!$A$3:$G$100))+1,"0"),ROW(A1),COLUMN(A1))
Komentar ini diminimalkan oleh moderator di situs
Hai, Bisakah Anda memberikan kesalahan data dan rumus Anda sebagai screesnhot di sini?
Komentar ini diminimalkan oleh moderator di situs
Halo, Bagaimana saya bisa menggunakannya untuk kondisi Horizontal.
Komentar ini diminimalkan oleh moderator di situs
Apa "0" setelah +1 dalam rumus? Itu tidak ada di contoh satu.
Komentar ini diminimalkan oleh moderator di situs
Hai saya sudah mencoba formula yang sama. saya mendapatkan hasil tetapi ketika memberikan CSE itu tidak memberikan banyak tanggapan
Komentar ini diminimalkan oleh moderator di situs

Komentar ini diminimalkan oleh moderator di situs
Mengenai Mengembalikan Beberapa Nilai Pencocokan Berdasarkan Satu Atau Beberapa Kriteria Dengan Rumus Array: Mengapa jika saya memiliki data di tempat lain kecuali mulai dari A1 tidak berfungsi meskipun saya memperbarui semua referensi sel dalam rumus?
Komentar ini diminimalkan oleh moderator di situs
Pada contoh pertama, perubahan apa pada formula yang diperlukan untuk mengembalikan semua orang yang berusia kurang dari 28 tahun?
Komentar ini diminimalkan oleh moderator di situs
Hai,

Saya bertanya-tanya apakah mungkin untuk memasukkan kriteria ke-2 tetapi dari kisaran yang sama dengan kriteria ke-1,

Misalnya dengan contoh yang digunakan di atas saya ingin mencari nama-nama orang dari Amerika dan Prancis Jadi sel F3 akan memiliki Prancis, Scarlett & Andrew juga akan mengisi daftar di Kolom G

Terima kasih atas bantuan sebelumnya.
Komentar ini diminimalkan oleh moderator di situs
Halo Nik,

Senang untuk membantu. Jika Anda ingin mendapatkan nama orang dari Amerika dan Prancis, saya sarankan Anda menggunakan rumus kami dua kali untuk mendapatkan hasilnya. Silakan lihat tangkapan layar, Di F2 dan G2 ada nilai "Amerika Serikat" dan "Prancis". Terapkan rumus =IFERROR(INDEX($B$2:$B$11, KECIL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1 ), ROW(1:1))),"" ) untuk mendapatkan hasil untuk Amerika. Dan terapkan rumus =IFERROR(INDEX($B$2:$B$11, SMALL(IF($G$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+ 1), ROW(1:1))),"" ) untuk mendapatkan hasil untuk Prancis. Itu mudah. Silakan coba.

Hormat kami,
Mandy
Komentar ini diminimalkan oleh moderator di situs
Saat saya menggunakan rumus kedua dan menyeretnya ke bawah, tidak ada yang muncul. Hasil rumus (fx) mengatakan itu harus mengembalikan sesuatu tetapi kosong. Bagaimana cara memperbaiki ini?
Komentar ini diminimalkan oleh moderator di situs
Halo Alysia,

Senang untuk membantu. Saya mencoba rumus kedua di artikel dan menyeret rumus ke bawah, sisa hasil dikembalikan. Saya pikir mungkin ada dua alasan untuk masalah Anda. Pertama, mungkin Anda lupa menekan tombol Ctrl + Shift + Enter untuk memasukkan rumus. Kedua, hasil pencocokan hanya satu, jadi tidak ada hasil lain yang tidak dikembalikan. Silakan cek.

Hormat kami,
Mandy
Komentar ini diminimalkan oleh moderator di situs
Halo,
saya mencoba menggunakan rumus dan menghasilkan nilai 0 atau gambar terlampir
Komentar ini diminimalkan oleh moderator di situs
Halo, Milku
Tangkapan layar Anda menunjukkan perangkat lunak WPS versi MAC, jadi saya tidak yakin apakah formula kami tersedia.
Saya mengunggah file Excel ke sini, Anda dapat mencoba untuk melihat apakah itu dapat menghitung dengan benar di lingkungan Anda.
Terima kasih!
Komentar ini diminimalkan oleh moderator di situs
Halo,
apa yang diperlukan untuk memperluas formula pertama dalam kasus berikut:
Beberapa ID Kosong (misalnya sel A5 kosong) dan saya ingin kondisi tambahan mengeluarkan baris hanya jika ID tidak kosong. (Jadi hasilnya harus James dan Abdul.
Terima kasih!
Komentar ini diminimalkan oleh moderator di situs
Halo, Jo,
Untuk mengatasi masalah Anda, silakan terapkan rumus di bawah ini:
=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11)*($A$2:$A$11<>0), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Silakan ry, semoga bisa membantu Anda!
Komentar ini diminimalkan oleh moderator di situs
Hai,

jika di sel H1 saya menulis "Nama" dan ingin menghubungkannya dengan rumus, bagaimana cara kerjanya?
Kemudian saya bisa menulis "ID" di sel H1 dan secara otomatis akan mendapatkan sebagai hasilnya: AA1004; DD1009; PP1023 (untuk rumus pertama)

Terima kasih sebelumnya!
Komentar ini diminimalkan oleh moderator di situs
Halo, Marie
Maaf, saya tidak dapat memahami inti dari masalah pertama Anda, dapatkah Anda menjelaskan masalah Anda lebih jelas dan rinci? Atau Anda dapat menyisipkan tangkapan layar di sini untuk menjelaskan masalah Anda.
Adapun pertanyaan kedua, Anda hanya perlu mengubah referensi sel seperti ini:
=INDEX($A$2:$A$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Ingatlah untuk menekan Ctrl + Shift + Enter kunci bersama
Silakan dicoba, semoga bisa membantu Anda!
Komentar ini diminimalkan oleh moderator di situs
Hei, terima kasih untuk formulanya. Ini berfungsi untuk nilai / teks "tetap" sebagai kriteria. Namun, salah satu kriteria yang saya coba gunakan adalah kondisi (nilai <>0 ), tetapi tidak berfungsi dengan rumus yang dijelaskan. Apakah kalian tahu apa yang harus saya ubah untuk mengadaptasi formula sehingga saya dapat memiliki kondisi sebagai salah satu kriteria, tolong?

Terbaik,

John
Komentar ini diminimalkan oleh moderator di situs
Halo, Marcus
Untuk mengatasi masalah Anda, silakan lihat artikel ini:
https://www.extendoffice.com/documents/excel/6393-excel-vlookup-function.html#b3-2
Ada beberapa penjelasan rinci tentang tugas ini. Anda hanya perlu mengubah kriteria menjadi milik Anda sendiri.
Terima kasih!
Komentar ini diminimalkan oleh moderator di situs
Hai,

Pertama, terima kasih telah berbagi!

Bisakah Anda memberikan solusi untuk kasus di bawah ini:

Saya memiliki 3 kolom (A: Berisi informasi referensi, B: Berisi informasi yang akan dicari, C: Hasil pencarian)

Url gambar disediakan di bawah ini

https://ibb.co/VHCd09K

Kolom A-------------------------Kolom B------------Kolom C
Nama File-------------------------Nama----------------Nama File, Nama Dokumen, Nama Elemen, Nama
Elemen Berubah-----------------Elemen--------------Elemen Berubah, Nama Elemen, ID Elemen
Lokasi Kolom
Nama dokumen
Nama Elemen
Nama
Kategori
Jaminan
Lereng
ID elemen

Yang saya butuhkan adalah mencari di kolom A untuk kecocokan sebagian dengan sel B2 (Nama) atau B3 (Elemen) dan mendapatkan hasilnya dalam satu sel,

Terima kasih, Behzad
Komentar ini diminimalkan oleh moderator di situs
Halo, Behzad
Mungkin Fungsi Ditetapkan Pengguna di bawah ini dapat membantu Anda.
Public Function ConcatPartLookUp(rngInput As Range, rngSource As Range, Optional strDelimiter As String, Optional blCaseSensitive)
Dim rng As Range
If strDelimiter = "" Then strDelimiter = ","
If IsMissing(blCaseSensitive) Then
    blCaseSensitive = False
Else
    blCaseSensitive = True
End If
For Each rng In rngSource
    If blCaseSensitive Then
        If InStr(1, rng.Value, rngInput.Value, vbBinaryCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    Else
        If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    End If
Next
If Len(ConcatPartLookUp) > 0 Then ConcatPartLookUp = Mid(ConcatPartLookUp, 2, Len(ConcatPartLookUp))
End Function


Setelah menyalin dan menempelkan kode ini, lalu gunakan rumus ini:=ConcatPartLookUp(B2,$A$2:$A$8) untuk mendapatkan hasil yang Anda butuhkan.
Silahkan dicoba, semoga bisa membantu!
Komentar ini diminimalkan oleh moderator di situs
Hai,

Terima kasih telah memposting contoh-contoh ini.
Saya mencoba menerapkan ini di lembar saya sendiri, tetapi tidak membuatnya berfungsi (mungkin karena saya menggunakan excel versi eropa)?

Saya ingin mendapatkan tanggal hari-hari saya memiliki shift atau bahwa saya telah bekerja 'beberapa' (>0) jam untuk klien.

Jadi di I3 adalah nama dan di J3 bulannya. K3 dan L3 adalah shift (1 bekerja) dan jam (tidak tahu cara mengatur ini, harus lebih dari nol)

Hasil yang saya harapkan ada di:
Pergeseran: I7 dan I8
jam: J7

Jadi saya bekerja lebih dari 0 jam untuk 'orang 2' di oktober pada 3-10-2022
memiliki shift untuk orang 2 pada '10-10-2022' dan 28-10-2022

Ketika saya menambahkan '=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW ($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))' di lembar excel saya, itu tidak memungkinkan koma antara bagian yang berbeda dari rumus.
Jadi saya perlu mengubahnya menjadi ';'.
Tetapi ketika saya mencobanya selalu tertulis: '#NAME?'

Jadi bisakah seseorang membantu saya dengan ini?

Salam,

Bas
[img]https://drive.google.com/file/d/1iIPQKuj_PNhqWyWlwJ4IQTqGNEd6B9Hw/view?usp=share_link[/img]
Komentar ini diminimalkan oleh moderator di situs
Hai, jika ada nilai duplikat (misalnya dua adam), bagaimana cara memastikan bahwa hanya mengembalikan 1 adam dan bukan 2?
Komentar ini diminimalkan oleh moderator di situs
Halo, Bobi,
Untuk mengekstrak hanya nilai pencocokan unik, Anda harus menerapkan rumus di bawah ini:
Setelah menempelkan rumus, silakan tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan hasil yang benar.
=IFERROR(INDEX($B$2:$B$5, MATCH(0, COUNTIF(H1:$H$1, $B$2:$B$5)+IF($D$2:$D$5<>$G$2, 1 , 0)+JIKA($C$2:$C$5<>$F$2, 1, 0), 0)), "")

Silahkan dicoba, semoga bisa membantu!
Belum ada komentar yang diposting di sini
Tinggalkan komentar anda
Posting sebagai Tamu
×
Beri peringkat pos ini:
0   Karakter
Lokasi yang Disarankan

Ikuti kami

Hak Cipta © 2009 - www.extendoffice.com. | Seluruh hak cipta. Dipersembahkan oleh ExtendOffice. | Peta Situs
Microsoft dan logo Office adalah merek dagang atau merek dagang terdaftar dari Microsoft Corporation di Amerika Serikat dan / atau negara lain.
Dilindungi oleh Sectigo SSL