Skip i'r prif gynnwys

Sut i wylio rhifau sydd wedi'u storio fel testun yn Excel?

Gan dybio, mae gen i'r ystod ddata ganlynol, y rhif ID yn y tabl gwreiddiol yw fformat rhif, yn y gell edrych sy'n cael ei storio fel testun, pan fyddaf yn cymhwyso'r swyddogaeth VLOOKUP arferol, byddaf yn cael canlyniad gwall fel islaw'r screenshot a ddangosir. Yn yr achos hwn, sut allwn i gael y wybodaeth gywir pe bai gan y rhif edrych a'r rhif gwreiddiol yn y tabl y fformat data gwahanol?

Rhifau Vlookup wedi'u storio fel testun gyda fformwlâu

rhif vlookup doc wedi'i storio fel testun 1

swigen dde glas saeth Rhifau Vlookup wedi'u storio fel testun gyda fformwlâu

Os yw'ch rhif edrych yn cael ei storio fel testun, a bod y rhif gwreiddiol yn y tabl ar ffurf rhif go iawn, defnyddiwch y fformiwla ganlynol i ddychwelyd y canlyniad cywir:

Rhowch y fformiwla hon: = VLOOKUP (GWERTH (G1), A2: D15,2, ANWIR) i mewn i gell wag lle rydych chi am ddod o hyd i'r canlyniad, a gwasgwch Rhowch allwedd i ddychwelyd y wybodaeth gyfatebol sydd ei hangen arnoch, gweler y screenshot:

rhif vlookup doc wedi'i storio fel testun 2

Nodiadau:

1. Yn y fformiwla uchod: G1 yw'r meini prawf yr ydych am edrych arnynt, A2: D15 yw'r ystod tabl sy'n cynnwys y data rydych chi am ei ddefnyddio, a'r rhif 2 yn nodi rhif y golofn sydd â'r gwerth cyfatebol rydych chi am ei dychwelyd.

2. Os yw eich gwerth edrych ar ffurf fformat, a bod y rhif adnabod yn y tabl gwreiddiol yn cael ei storio fel testun, ni fydd y fformiwla uchod yn gweithio, dylech gymhwyso'r fformiwla hon: = VLOOKUP (TESTUN (G1,0), A2: D15,2, ANWIR) i gael y canlyniad cywir yn ôl yr angen.

rhif vlookup doc wedi'i storio fel testun 3

3. Os nad ydych yn siŵr pryd fydd gennych rifau a phryd y bydd gennych destun, gallwch ddefnyddio'r fformiwla hon: =IFERROR(VLOOKUP(VALUE(G1),A2:D15,2,0),VLOOKUP(TEXT(G1,0),A2:D15,2,0)) i drin y ddau achos.

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 (13)
No ratings yet. Be the first to rate!
JZ
JZ
This comment was minimized by the moderator on the site
Worked. Thank you! 
Reply
0
0
MandyZhou
MandyZhou     JZ
This comment was minimized by the moderator on the site
Hello,
You are welcome. Glad it helps. Any questions, please feel free to contact us. Have a great day.
Sincerely,
Mandy
Reply
Report
0
0
jessicam
jessicam
This comment was minimized by the moderator on the site
Thanks so much!!!!! This worked for me, the #3 solution! HUGE help!!!
Reply
Report
0
0
MandyZhou
MandyZhou     jessicam
This comment was minimized by the moderator on the site
Hello,
You are welcome. Glad it helps. Any questions, please feel free to contact us. Have a great day.
Sincerely,
Mandy
Reply
Report
0
0
Pratheesh
Pratheesh
This comment was minimized by the moderator on the site
Thank You... :) Its worked for me
Reply
0
0
MandyZhou
MandyZhou     Pratheesh
This comment was minimized by the moderator on the site
Hello,
You are welcome. Glad it helps. Any questions, please feel free to contact us. Have a great day.
Sincerely,
Mandy
Reply
Report
0
0
Jaji813
Jaji813
This comment was minimized by the moderator on the site
Same thing here. The problem is the data range. For some reason, even when the data type is text, MS Excel still looks at it as a number. But if you hit enter, it looks at it as text. So I usually end up with the data range where some of it is numbers and others, text.

SOLUTION: Use IFERROR function to address BOTH possibilities: IFERROR(VLOOKUP(TEXT(A1,0),$A$1:$A$100,1,FALSE),VLOOKUP(VALUE(A1),$A$1:$A$100,1,FALSE)). Hope this helps.
Reply
Report
0
0
Rens
Rens
This comment was minimized by the moderator on the site
Unfortunately this method does not Always work. I import numbers in a column defined as tekst. I lookup these numbers in a table in which the numbers are also stored as tekst. I would say a Vlookup should work, but nor the standard lookup, nor the lookup with TEXT() or VALUE() function as expected. When I retype all values in the columns it works as expected, so there's no error in the formula.
Reply
0
0
Guuz
Guuz     Rens
This comment was minimized by the moderator on the site
I have the same problem @Rens, does anybody know a solution for this?
Reply
0
0
Jaji813
Jaji813     Rens
This comment was minimized by the moderator on the site
Same thing here. The problem is the data range. For some reason, even when the data type is text, MS Excel still looks at it as a number. But if you hit enter, it looks at it as text. So I usually end up with the data range where some of it is numbers and others, text.

SOLUTION: Use IFERROR function to address BOTH possibilities: IFERROR(VLOOKUP(TEXT(A1,0),$A$1:$A$100,1,FALSE),VLOOKUP(VALUE(A1),$A$1:$A$100,1,FALSE)). Hope this helps.
Reply
Report
0
0
Salim Dz
Salim Dz     Jaji813
This comment was minimized by the moderator on the site
This is what I do and is perfect.
Reply
0
0
Jaji813
Jaji813     Rens
This comment was minimized by the moderator on the site
Same thing here. The problem is the data range. For some reason, even when data type is text, it looks like it's still looked on as a number. But if you hit enter, it looks at it as text. So I usually would end up with the data range where some of it is numbers and others text.

SOLUTION: Use IFERROR function to address BOTH possibilities: IFERROR(VLOOKUP(TEXT(A1,0),$A$1:$A$100,1,FALSE),VLOOKUP(VALUE(A1),$A$1:$A$100,1,FALSE)). Hope this helps.
Reply
0
0
Rens
Rens     Jaji813
This comment was minimized by the moderator on the site
Hello Jaji, thanks, this works, I hope in all cases. Nevertheless it looks like a bug to me.
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