Skip i'r prif gynnwys

Sut i ddychwelyd gwerth os oes gwerth penodol yn bodoli mewn ystod benodol yn Excel?

Fel y dangosir y screenshot chwith, os yw rhif 5 penodol mewn ystod rhif penodol, sut i ddychwelyd y gwerth yn y gell gyfagos. Gall y dull fformiwla yn yr erthygl hon eich helpu i'w gyflawni.

Dychwelwch werth os oes gwerth penodol yn bodoli mewn ystod benodol trwy ddefnyddio fformiwla

Dychwelwch werth os oes gwerth penodol yn bodoli mewn ystod benodol trwy ddefnyddio fformiwla

Defnyddiwch y fformiwla ganlynol i ddychwelyd gwerth os oes gwerth penodol yn bodoli mewn ystod benodol yn Excel.

1. Dewiswch gell wag, nodwch fformiwla = VLOOKUP (E2, A2: C8,3, GWIR) i mewn i'r Bar Fformiwla ac yna pwyswch y Rhowch allwedd. Gweler y screenshot:

Gallwch weld bod y rhif 5 a roddir yn yr ystod rhif 4-6, yna mae'r gwerth cyfatebol Addin 012 yn y gell gyfagos yn cael ei boblogi i'r gell a ddewiswyd yn syth fel y dangosodd y screenshot uchod.

Nodyn: Yn y fformiwla, E2 yw'r gell sy'n cynnwys y rhif a roddir, mae A2: C8 yn cynnwys yr ystod rhif a'r gwerth y byddwch chi'n ei ddychwelyd yn seiliedig ar y rhif penodol, ac mae rhif 3 yn golygu bod y gwerth y byddwch chi'n ei ddychwelyd yn lleoli yn nhrydedd golofn ystod A2: C8. Newidiwch nhw ar sail eich angen.

Erthyglau cysylltiedig:

Offer Cynhyrchiant Swyddfa Gorau

🤖 Kutools AI Aide: Chwyldro dadansoddi data yn seiliedig ar: Cyflawniad Deallus   |  Cynhyrchu Cod  |  Creu Fformiwlâu Personol  |  Dadansoddi Data a Chynhyrchu Siartiau  |  Invoke Swyddogaethau Kutools...
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 ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer 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...

Disgrifiad

Mae 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 (14)
No ratings yet. Be the first to rate!
Nilton Vanini
Nilton Vanini
This comment was minimized by the moderator on the site
Prezados,
Bom dia!

É possível determinar algum parâmetro dentro do Range.

Quero fazer o PROCV trazer de um intervalo, porém ele acaba retornando valores de CEPs que não existem, mas que estão muito próximos...

Exemplo:

Na tabela existe a faixa de CEP 68795000 A 68796999 que é do estado do Pará
Ao utilizar as formula sugeridas, resulta como "verdadeiro" para a faixa de CEP 69301000 A 69390971 que é do estado de Roraima, devido a proximidade da numeração.
Reply
0
0
crystal
crystal     Nilton Vanini
This comment was minimized by the moderator on the site
Hi Nilton Vanini,
I don't quite understand your question. Do you mind uploading a screenshot of your data? Sorry for the inconvenience.
Reply
Report
0
0
Andrew
Andrew
This comment was minimized by the moderator on the site
This seems to also work with text. I got this to work with exact value rather than a range by using the "Value" column as part of the range in the formula.

My question is, are you forced to use 2 columns or is it possible to only use 1 and the value column?

I created a master sheet to call from but this is how I setup the formla

=VLOOKUP(I2,'Master Sheet'!$A$2:$B$53,2,TRUE)

Column "I" text (Number column in the example), I was forced to have a range so column "B" is both the Range and Value.

Is this best practice or would I run into unexpected issues down the road if more line items are added to this list?
Reply
0
0
crystal
crystal     Andrew
This comment was minimized by the moderator on the site
Hi Andrew,
You may need to upload a screenshot of your data. Sorry for the inconvenience.
Reply
Report
0
0
Damien Dulac
Damien Dulac
This comment was minimized by the moderator on the site
Bonjour je cherche à vérifier si une de mes valeurs correspond à une plage de données, le problème est que je n'ai pas qu'une valeur à vérifier mais une longue liste. J'aimerais que si une des 3 valeurs suivantes que je tape dans une case (exemple "panneau";"Strat";"chants") correspondent à une plage de données, elles 's'affichent en rouge par exemple. Sauriez vous me renseigner ? Merci.
Reply
0
0
Daili
Daili
This comment was minimized by the moderator on the site
Yo quiero devolver 1, si se encuentra en un rango de numeros determinados, ej. el rango es de 4 a 10, y en la celda tengo 5, devolver 1, pero si tengo 2 o 15, devolver 0.
Reply
0
0
crystal
crystal     Daili
This comment was minimized by the moderator on the site
Hi Daili,
If you just want to check if a number exists in a given range and return a specified value, you can combine IF with AND functions to get it done.
Suppose the number 4 is in cell A9, the number 10 is in cell B9, the number you want to determine if it exists between 4 and 10 is locateed in D9, you can apply the following formula to get the correct result.
=IF(AND(D9>A9,D9<B9),1,0)
Reply
Report
0
0
Judy
Judy
This comment was minimized by the moderator on the site
Can you do a lookup searching a range of 3digit zips (i.e. 010-013 or 280-283,286,297) and pull in the city / state from your zip code list. The zip code list is all 3 digit zips in US broken out individually. The zips are listed multiple times because there are several city / state combinations. (i.e. 010 Ludlow MA; 010 Plamer MA etc).
Reply
0
0
kl
kl
This comment was minimized by the moderator on the site
The value works on the first cell. How do you do it for the entire column cell?
Reply
0
0
Jim
Jim     kl
This comment was minimized by the moderator on the site
You will need to use $ for the lookup cells values
So if you have a column of values you want to look up you would modify the formula as follows:
=VLOOKUP(E2,$A$2:$C$8,3, TRUE)

E2 would change as you fill down but the table you are looking up from will remain constant
Reply
0
0
Adele
Adele
This comment was minimized by the moderator on the site
awesome, thanks!
Reply
0
0
Teresa
Teresa
This comment was minimized by the moderator on the site
Doesn't work. if only it was that simple, wouldn't need to be googling for this
Reply
0
0
treefireonwater@gmail.com
    Teresa
This comment was minimized by the moderator on the site
It obviously does. Instead of whining and trying to be a smartass about it, you could have at least tried it out.
Reply
Report
0
0
Mika Donner
Mika Donner     Teresa
This comment was minimized by the moderator on the site
It does. Try it out before rudely complaining, simple as that.
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