Cynnwys
Mae'r wers hon yn esbonio sut i ddelio'n gyflym â sefyllfa lle mae swyddogaeth VPR (VLOOKUP) ddim eisiau gweithio yn Excel 2013, 2010, 2007 a 2003, a sut i nodi a thrwsio gwallau cyffredin a goresgyn cyfyngiadau VPR.
Mewn sawl erthygl flaenorol, rydym wedi archwilio gwahanol agweddau ar y swyddogaeth VPR yn Excel. Os ydych wedi eu darllen yn ofalus, dylech nawr fod yn arbenigwr yn y maes hwn. Fodd bynnag, nid heb reswm y mae llawer o arbenigwyr Excel yn ei gredu VPR un o'r nodweddion mwy cymhleth. Mae ganddo griw o gyfyngiadau a nodweddion sy'n dod yn ffynhonnell llawer o broblemau a gwallau.
Yn yr erthygl hon fe welwch esboniadau syml o wallau #AT (#N/A), # ENW? (#NAME?) a #VALUE! (#VALUE!) sy'n ymddangos wrth weithio gyda'r ffwythiant VPR, yn ogystal â thechnegau a dulliau o ddelio â nhw. Byddwn yn dechrau gyda'r achosion mwyaf cyffredin a'r rhesymau amlycaf pam. VPR nad yw'n gweithio, felly mae'n well astudio'r enghreifftiau yn y drefn y maent yn cael eu rhoi yn yr erthygl.
Trwsio gwall # N/A yn swyddogaeth VLOOKUP yn Excel
Mewn fformiwlâu gyda VPR neges gwall #AT (#N/A) yn golygu Dim ar gael (dim data) - yn ymddangos pan na all Excel ddod o hyd i'r gwerth rydych chi'n edrych amdano. Gall hyn ddigwydd am sawl rheswm.
1. Mae'r gwerth dymunol yn cael ei gamsillafu
Syniad da gwirio'r eitem hon yn gyntaf! Mae typos yn aml yn digwydd pan fyddwch chi'n gweithio gyda llawer iawn o ddata, sy'n cynnwys miloedd o linellau, neu pan fydd y gwerth rydych chi'n edrych amdano wedi'i ysgrifennu mewn fformiwla.
2. #N/A gwall wrth chwilio am gyfatebiaeth fras â VLOOKUP
Os ydych yn defnyddio fformiwla gydag amod chwilio cyfatebol bras, hy dadl ystod_lookup (range_lookup) yn WIR neu heb ei nodi, mae'n bosibl y bydd eich fformiwla yn adrodd am wall # N / A mewn dau achos:
- Mae'r gwerth i edrych i fyny yn llai na'r gwerth lleiaf yn yr arae sy'n cael ei edrych i fyny.
- Nid yw'r golofn chwilio wedi'i threfnu mewn trefn esgynnol.
3. #N/A gwall wrth chwilio am union gyfatebiaeth â VLOOKUP
Os ydych yn chwilio am union gyfatebiaeth, hy dadl ystod_lookup (range_lookup) yn ANGHYWIR ac ni chanfuwyd yr union werth, bydd y fformiwla hefyd yn adrodd am wall # N / A. Dysgwch fwy am sut i chwilio am gyfatebiaethau union a bras â swyddogaeth VPR.
4. Nid yw colofn chwilio ar y chwith
Fel y gwyddoch mae'n debyg, un o'r cyfyngiadau mwyaf arwyddocaol VPR mae'n na all wynebu i'r chwith, felly mae'n rhaid i'r golofn chwilio yn eich tabl fod ar y chwith. Yn ymarferol, rydym yn aml yn anghofio am hyn, sy'n arwain at fformiwla nad yw'n gweithio a gwall. # N / A.
Penderfyniad: Os nad yw'n bosibl newid y strwythur data fel bod y golofn chwilio ar y chwith, gallwch ddefnyddio cyfuniad o swyddogaethau MYNEGAI (MYNEGAI) a MWY AMLWG (MATCH) fel dewis amgen mwy hyblyg ar gyfer VPR.
5. Mae rhifau'n cael eu fformatio fel testun
Ffynhonnell gwall arall # N / A mewn fformiwlâu gyda VPR yw rhifau mewn fformat testun yn y prif dabl neu dabl chwilio.
Mae hyn fel arfer yn digwydd pan fyddwch yn mewnforio gwybodaeth o gronfeydd data allanol, neu pan fyddwch yn teipio collnod cyn rhif i gadw'r sero blaen.
Dangosir yr arwyddion mwyaf amlwg o rif mewn fformat testun yn y ffigur isod:
Yn ogystal, gellir storio rhifau yn y fformat cyffredinol (Cyffredinol). Yn yr achos hwn, dim ond un nodwedd amlwg sydd - mae'r niferoedd wedi'u halinio i ymyl chwith y gell, tra yn ddiofyn maent wedi'u halinio i'r ymyl dde.
Penderfyniad: Os yw'n werth sengl, cliciwch ar yr eicon gwall a dewiswch Trosi i Rif (Trosi i Rif) o'r ddewislen cyd-destun.
Os mai dyma'r sefyllfa gyda llawer o rifau, dewiswch nhw a de-gliciwch ar yr ardal a ddewiswyd. Yn y ddewislen cyd-destun sy'n ymddangos, dewiswch Celloedd Fformat (Fformat Celloedd) > tab Nifer (Rhif) > fformat Nifer (Rhif) a gwasg OK.
6. Mae gwagle ar y dechrau neu ar y diwedd
Dyma'r rheswm lleiaf amlwg am y camgymeriad. # N / A mewn swyddogaeth VPR, gan ei bod yn anodd gweld y lleoedd ychwanegol hyn, yn enwedig wrth weithio gyda thablau mawr, pan fydd y rhan fwyaf o'r data oddi ar y sgrin.
Ateb 1: Bylchau ychwanegol yn y prif dabl (lle mae'r swyddogaeth VLOOKUP)
Os bydd bylchau ychwanegol yn ymddangos yn y prif dabl, gallwch sicrhau bod y fformiwlâu yn gweithio'n gywir trwy amgáu'r ddadl lookup_value (lookup_value) i mewn i swyddogaeth Torrwch (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Datrysiad 2: Bylchau ychwanegol yn y tabl chwilio (yn y golofn chwilio)
Os oes bylchau ychwanegol yn y golofn chwilio - ffyrdd syml # N / A yn y fformiwla gyda VPR ni ellir ei osgoi. Yn lle VPR Gallwch ddefnyddio fformiwla arae gyda chyfuniad o swyddogaethau MYNEGAI (MYNEGAI), MWY AMLWG (MATCH) и Torrwch (TRIM):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Gan mai fformiwla arae yw hon, peidiwch ag anghofio pwyso Ctrl + Shift + Enter yn lle yr arferol Rhowchi fewnbynnu'r fformiwla yn gywir.
Gwall #VALUE ! mewn fformiwlâu gyda VLOOKUP
Yn y rhan fwyaf o achosion, mae Microsoft Excel yn adrodd gwall #VALUE! (#VALUE!) pan nad yw'r gwerth a ddefnyddir yn y fformiwla yn cyfateb i'r math o ddata. Ynglyn VPR, yna fel arfer mae dau reswm dros y gwall #VALUE!.
1. Mae'r gwerth yr ydych yn chwilio amdano yn hwy na 255 nod
Byddwch yn ofalus: swyddogaeth VPR methu chwilio am werthoedd sy'n cynnwys mwy na 255 nod. Os yw'r gwerth yr ydych yn chwilio amdano yn fwy na'r terfyn hwn, byddwch yn derbyn neges gwall. #VALUE!.
Penderfyniad: Defnyddiwch griw o nodweddion MYNEGAI+CYDRADDOLDEB (MYNEGAI + MATCH). Isod mae fformiwla a fydd yn gwneud yn iawn ar gyfer y dasg hon:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Nid yw'r llwybr llawn i'r llyfr gwaith chwilio wedi'i nodi
Os ydych yn adalw data o lyfr gwaith arall, rhaid i chi nodi'r llwybr llawn i'r ffeil honno. Yn fwy penodol, rhaid i chi gynnwys enw'r llyfr gwaith (gan gynnwys yr estyniad) mewn cromfachau sgwâr [ ], ac yna enw'r ddalen, ac yna ebychnod. Rhaid amgáu'r holl adeiladwaith hwn mewn collnodau, rhag ofn y bydd enw'r llyfr neu'r ddalen yn cynnwys bylchau.
Dyma strwythur cyflawn y swyddogaeth VPR i chwilio mewn llyfr arall:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Efallai y bydd y fformiwla go iawn yn edrych fel hyn:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Bydd y fformiwla hon yn edrych ar werth y gell A2 mewn colofn B ar y ddalen Sheet1 yn y llyfr gwaith Prisiau Newydd a thynnwch y gwerth cyfatebol o'r golofn D.
Os caiff unrhyw ran o'r llwybr bwrdd ei hepgor, eich swyddogaeth VPR ni fydd yn gweithio a bydd yn rhoi gwybod am gamgymeriad #VALUE! (hyd yn oed os yw'r llyfr gwaith gyda'r tabl chwilio ar agor ar hyn o bryd).
Am ragor o wybodaeth am y swyddogaeth VPRgan gyfeirio at ffeil Excel arall, gweler y wers: Chwilio llyfr gwaith arall gan ddefnyddio VLOOKUP.
3. Mae Colofn_num dadl yn llai nag 1
Mae'n anodd dychmygu sefyllfa lle mae rhywun yn mynd i mewn i werth llai na 1i nodi o ba golofn i dynnu'r gwerth. Er ei bod yn bosibl os yw gwerth y ddadl hon yn cael ei gyfrifo gan ffwythiant Excel arall sydd wedi'i nythu ynddi VPR.
Felly, os yw'n digwydd bod y ddadl col_index_num (colofn_number) llai na 1swyddogaeth VPR bydd hefyd yn rhoi gwybod am gamgymeriad #VALUE!.
Os bydd y ddadl col_index_num (rhif_colofn) yn fwy na nifer y colofnau yn yr arae a roddir, VPR yn rhoi gwybod am gamgymeriad #REF! (#SSYL!).
Gwall #NAME? yn VLOOKUP
Yr achos symlaf yw camgymeriad # ENW? (#NAME?) – yn ymddangos os byddwch yn ysgrifennu enw swyddogaeth gyda gwall yn ddamweiniol.
Mae'r ateb yn amlwg - gwiriwch eich sillafu!
Nid yw VLOOKUP yn gweithio (cyfyngiadau, cafeatau a phenderfyniadau)
Yn ogystal â'r gystrawen eithaf cymhleth, VPR mae ganddo fwy o gyfyngiadau nag unrhyw swyddogaeth Excel arall. Oherwydd y cyfyngiadau hyn, mae fformiwlâu sy'n ymddangos yn syml gyda VPR yn aml yn arwain at ganlyniadau annisgwyl. Isod fe welwch atebion ar gyfer nifer o senarios cyffredin lle VPR yn anghywir.
1. Nid yw VLOOKUP yn sensitif i achosion
swyddogaeth VPR nid yw'n gwahaniaethu rhwng llythrennau bach ac mae'n derbyn llythrennau bach a phriflythrennau fel yr un peth. Felly, os oes sawl elfen yn y tabl sy'n wahanol yn unig rhag ofn, bydd y swyddogaeth VLOOKUP yn dychwelyd yr elfen gyntaf a ddarganfuwyd, waeth beth fo'r achos.
Penderfyniad: Defnyddiwch swyddogaeth Excel arall a all berfformio chwiliad fertigol (LOOKUP, SUMPRODUCT, INDEX, a MATCH) ar y cyd â EXACTA sy'n gwahaniaethu achos. Am ragor o fanylion, gallwch ddysgu o'r wers - 4 ffordd o wneud VLOOKUP yn sensitif i achosion yn Excel.
2. Mae VLOOKUP yn dychwelyd y gwerth cyntaf a ddarganfuwyd
Fel y gwyddoch eisoes, VPR yn dychwelyd y gwerth o'r golofn a roddir sy'n cyfateb i'r cyfatebiad cyntaf a ddarganfuwyd. Fodd bynnag, gallwch ei gael i dynnu'r 2il, 3ydd, 4ydd, neu unrhyw ailadrodd arall o'r gwerth rydych chi ei eisiau. Os oes angen i chi echdynnu'r holl werthoedd dyblyg, bydd angen cyfuniad o swyddogaethau arnoch MYNEGAI (MYNEGAI), LEAST (BACH) a LLINELL (ROW).
3. Cafodd colofn ei hychwanegu neu ei thynnu i'r tabl
Yn anffodus, y fformiwlâu VPR rhoi'r gorau i weithio bob tro y bydd colofn newydd yn cael ei hychwanegu neu ei thynnu i'r tabl chwilio. Mae hyn yn digwydd oherwydd bod y gystrawen VPR yn gofyn i chi nodi ystod lawn y chwiliad a rhif y golofn benodol ar gyfer echdynnu data. Yn naturiol, mae'r ystod a roddir a rhif y golofn yn newid pan fyddwch chi'n dileu colofn neu'n mewnosod un newydd.
Penderfyniad: Ac eto mae swyddogaethau ar frys i helpu MYNEGAI (MYNEGAI) a MWY AMLWG (MATCH). Yn y fformiwla MYNEGAI+CYDRADDOLDEB Rydych chi'n diffinio colofnau chwilio ac adalw ar wahân, ac o ganlyniad, gallwch chi ddileu neu fewnosod cymaint o golofnau ag y dymunwch heb boeni am orfod diweddaru'r holl fformiwlâu chwilio cysylltiedig.
4. Mae cyfeiriadau celloedd yn garbled wrth gopïo fformiwla
Mae'r pennawd hwn yn esbonio hanfod y broblem yn drwyadl, iawn?
Penderfyniad: Defnyddiwch gyfeirnodau cell absoliwt bob amser (gyda'r symbol $) ar gofnodion yr ystod, er enghraifft $ A $ 2: $ C $ 100 or $ A: $ C.. Yn y bar fformiwla, gallwch chi newid y math cyswllt yn gyflym trwy glicio F4.
VLOOKUP - gweithio gyda'r swyddogaethau IFERROR ac ISERROR
Os nad ydych chi eisiau dychryn defnyddwyr gyda negeseuon gwall # N / A, #VALUE! or # ENW?, gallwch ddangos cell wag neu neges eich hun. Gallwch chi wneud hyn trwy osod VPR i mewn i swyddogaeth IFERROR (IFERROR) yn Excel 2013, 2010 a 2007 neu defnyddiwch griw o swyddogaethau OS+AMWALL (IF+ISERROR) mewn fersiynau cynharach.
VLOOKUP: gweithio gyda'r swyddogaeth IFERROR
Cystrawen swyddogaeth IFERROR (IFERROR) yn syml ac yn siarad drosto'i hun:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Hynny yw, ar gyfer y ddadl gyntaf rydych chi'n mewnosod y gwerth i'w wirio am wall, ac ar gyfer yr ail ddadl rydych chi'n nodi beth i'w ddychwelyd os canfyddir gwall.
Er enghraifft, mae'r fformiwla hon yn dychwelyd cell wag os na chanfyddir y gwerth yr ydych yn chwilio amdano:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Os ydych chi am arddangos eich neges eich hun yn lle neges gwall safonol y swyddogaeth VPR, rhowch ef mewn dyfyniadau, fel hyn:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: gweithio gyda'r swyddogaeth ISERROR
Ers y swyddogaeth IFERROR ymddangos yn Excel 2007, wrth weithio mewn fersiynau cynharach bydd yn rhaid i chi ddefnyddio'r cyfuniad IF (IF) a EOSHIBKA (ISERROR) fel hyn:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Er enghraifft, y fformiwla IF+ISERROR+VLOOKUP, yn debyg i'r fformiwla IFERROR+VLOOKUPa ddangosir uchod:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
Dyna i gyd am heddiw. Rwy'n gobeithio y bydd y tiwtorial byr hwn yn eich helpu i ddelio â'r holl gamgymeriadau posibl. VPR a gwneud i'ch fformiwlâu weithio'n gywir.