Loncat ke daftar isi utama

Bagaimana cara mencari nilai bukan nol pertama dan mengembalikan header kolom yang sesuai di Excel?

Misalkan, Anda memiliki berbagai data, sekarang, Anda ingin mengembalikan tajuk kolom di baris itu di mana nilai bukan nol pertama terjadi seperti gambar di bawah ini, artikel ini, saya akan memperkenalkan rumus yang berguna bagi Anda untuk menangani tugas ini di Excel.

doc pencarian pertama bukan nol 1

Cari nilai bukan nol pertama dan kembalikan header kolom yang sesuai dengan rumus

panah gelembung kanan biru Cari nilai bukan nol pertama dan kembalikan header kolom yang sesuai dengan rumus

Untuk mengembalikan tajuk kolom dari nilai bukan nol pertama berturut-turut, rumus berikut dapat membantu Anda, lakukan seperti ini:

Masukkan rumus ini: =INDEX($B$1:$I$1,MATCH(TRUE,INDEX(B2:I2<>0,),0)) ke dalam sel kosong tempat Anda ingin mencari hasilnya, K2, misalnya, lalu seret gagang isian ke sel yang ingin Anda terapkan rumus ini, dan semua tajuk kolom yang sesuai dari nilai bukan nol pertama dikembalikan seperti gambar berikut yang ditampilkan:

doc pencarian pertama bukan nol 2

Note: Dalam rumus di atas, B1: I1 adalah tajuk kolom yang ingin Anda kembalikan, B2: I2 adalah data baris yang ingin Anda cari nilai bukan nol pertama.

Alat Produktivitas Kantor Terbaik

馃 Kutools AI Ajudan: Merevolusi analisis data berdasarkan: Eksekusi Cerdas   |  Hasilkan Kode  |  Buat Rumus Khusus  |  Analisis Data dan Hasilkan Grafik  |  Aktifkan Fungsi Kutools...
Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria  |   VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar   |  Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel   |  Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas:  12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

Deskripsi Produk

Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • 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!
Comments (15)
No ratings yet. Be the first to rate!
Grobarka
Grobarka
This comment was minimized by the moderator on the site
Hello, my problem is: a have value 1,2,3,4,...50 that are the number of month. How to denormalize month into real datum. Thank you in advance a lot!!!
Reply
0
0
Allison
Allison
This comment was minimized by the moderator on the site
Does anyone know what you would do if your cell that contains a zero has a formula in it? When I use this formula, I end up with whatever the first column header is regardless of whether it is zero or not.
Reply
0
0
Matheus
Matheus
This comment was minimized by the moderator on the site
E como fa莽o pra procurar o primeiro valor diferente de zero da esquerda pra direita?
Reply
0
0
skyyang
skyyang     Matheus
This comment was minimized by the moderator on the site
Hello, Matheus
If you want to return the first non-zero value from a row, please apply the below formula:
=INDEX(B2:F2,MATCH(TRUE,INDEX(B2:F2<>0,),0))
Please have a try, hope it can help you!
Reply
Report
0
0
Ali
Ali
This comment was minimized by the moderator on the site
What if you needed the last non-zero value in an array?

P.S. This was really helpful, thank you so much!
Reply
0
0
skyyang
skyyang     Ali
This comment was minimized by the moderator on the site
Hello, Ali,
To get the column header based on the last non-zero value, please apply the below formula, see screenshot:
=INDEX($B$1:$H$1,MAX((COLUMN(B2:H2)-MIN(COLUMN(B2:H2))+1)*(B2:H2<>0)))
Please remember to press Ctrl+Shift+Enter key together.
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-last-non-zero.png
Please try, hope it can help you!
Reply
Report
0
0
WNHC
WNHC     skyyang
This comment was minimized by the moderator on the site
The formula is super helpful and helped me sorted out the situation! Thanks!!! Is there any chance you may please help to also explain how it works?
Reply
0
0
Robert
Robert
This comment was minimized by the moderator on the site
How do you look up for the second,third and fourth non-zero value?
Reply
0
0
skyyang
skyyang     Robert
This comment was minimized by the moderator on the site
Hello, Robert,
To lookup the second, third or fourth non-zero values and return their columns, please apply the below formula: (After pasting the formula, please press Ctr + Shift + Enter keys together.)
=INDEX($B$1:$I$1,SMALL(IF(B2:I2<>0, COLUMN(B2:I2)-COLUMN(A2)),2))


Note: To extract the third or fourth result, you just need to change the number 2 to 3,4 as you need.

Please have a try, hope it can help you!
Reply
Report
0
0
PRIYA
PRIYA
This comment was minimized by the moderator on the site
CAN U EXPLIAN HOW THIS FORMULA WORKS
Reply
0
0
MandyZhou
MandyZhou     PRIYA
This comment was minimized by the moderator on the site
Hello Priya,I am glad to help. I will separate the formula = INDEX($B$1:$I$1,MATCH(TRUE,INDEX(B2:I2<>0,),0)) into 3 parts, and explain the formula to you step by step.
First, let us see the inner-most formula =INDEX(B2:I2<>0,). This formula returns an array of TRUE and FALSE whether the values of range B2:I2 are equal to 0. Please see the screenshot 1, formula =INDEX(B2:I2<>0,) returns {FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE}.
Second, =MATCH(TRUE,INDEX(B2:I2<>0,),0) =MATCH(TRUE, {FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE}, 0). It returns the position of the first value "TRUE", while 0 in the formula means an exact match. We can see that the first value "TRUE" occurs at the 5th position of the array. Then the second formula returns 5. See the screenshot 2.
Finally,  =INDEX(B1:I1,MATCH(TRUE,INDEX(B2:I2<>0,),0))=INDEX(B1:I1,5). It returns the 5th value of the array B1:I1. We can see in the screenshot that the value is "May". So the final formula returns "May". 
PS: To drag the fill handle down the formula to get the rest of the results, we must make the range B1:I1 absolute. So we add $ to range B1:I1 to keep it absolute. 
Hope my explanation can solve your puzzle. Have a nice day.
Sincerely,Mandy
View Attachments (3 / 5)
Reply
Report
0
0
Tahir Nazir
Tahir Nazir     MandyZhou
This comment was minimized by the moderator on the site
Thank you Mandy for illustrating it in detail.
Kindly guide how to get the last non zero value from same formula instead of first non zero value.
Will Match work for that or not?
Reply
0
0
Jason
Jason     MandyZhou
This comment was minimized by the moderator on the site
Hi MandyZhou,

Thank you for the explanation!
I have a follow-up question.
How would the formula change if you had to look up the year too?
So in the above example, let's assume that column J is 'Year' and that in cell J2 is written '2015'.
And in cell K2 I would need the column header for the first non-zero value of the year in L2 (year 2015, so I would need to find 'Jun' in cell K2).

Thanks in advance for your help!
Jason
Reply
0
0
tancredi
tancredi
This comment was minimized by the moderator on the site
How can I return the second and third, etc non zero value? Your formula is amazing but I am trying to search more keywords from a text so sometimes they are 2 or 3 in the same sentence
Reply
0
0
gio.gandini@libero.i
gio.gandini@libero.i
This comment was minimized by the moderator on the site
se volessi "l'ultimo" Valore Diverso Da Zero E Restituire L'intestazione Della Colonna Corrispondente Con La Formula?
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
脳
Rate this post:
Reset
0   Characters
Suggested Locations