Skip i'r prif gynnwys

Sut i edrych ar werth cyntaf nad yw'n sero a dychwelyd pennawd colofn gyfatebol yn Excel?

Gan dybio, mae gennych chi ystod o ddata, nawr, rydych chi am ddychwelyd pennawd y golofn yn y rhes honno lle mae'r gwerth cyntaf nad yw'n sero yn digwydd fel a ganlyn y llun a ddangosir, yr erthygl hon, byddaf yn cyflwyno fformiwla ddefnyddiol i chi ddelio â'r dasg hon yn Excel.

doc lookup cyntaf nad yw'n sero 1

Edrychwch ar y gwerth cyntaf nad yw'n sero a dychwelwch y pennawd colofn cyfatebol gyda'r fformiwla

swigen dde glas saeth Edrychwch ar y gwerth cyntaf nad yw'n sero a dychwelwch y pennawd colofn cyfatebol gyda'r fformiwla

I ddychwelyd pennawd colofn y gwerth cyntaf nad yw'n sero yn olynol, gall y fformiwla ganlynol eich helpu chi, gwnewch fel hyn:

Rhowch y fformiwla hon: =INDEX($B$1:$I$1,MATCH(TRUE,INDEX(B2:I2<>0,),0)) i mewn i gell wag lle rydych chi am ddod o hyd i'r canlyniad, K2er enghraifft, ac yna llusgwch yr handlen llenwi i lawr i'r celloedd rydych chi am gymhwyso'r fformiwla hon, a dychwelir holl benawdau colofn cyfatebol y gwerth cyntaf nad yw'n sero fel y dangosir y llun a ganlyn:

doc lookup cyntaf nad yw'n sero 2

Nodyn: Yn y fformiwla uchod, B1: I1 yw'r penawdau colofn rydych chi am eu dychwelyd, B2: I2 yw'r data rhes yr ydych am edrych ar y gwerth cyntaf nad yw'n sero.

Offer Cynhyrchiant Swyddfa Gorau

Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog  |   VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr   |  Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd   |  Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau:  12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools for Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed o'r blaen. Kutools for Excel Yn cynnig dros 300 o nodweddion uwch i hybu cynhyrchiant ac arbed amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

tab kte 201905

Office Tab Yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
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