Drwy gyfuno swyddogaeth VLOOKUP Excel gyda swyddogaeth COLUMN, gallwn greu fformiwla chwilio sy'n eich galluogi i ddychwelyd nifer o werthoedd o un rhes o gronfa ddata neu dabl o ddata.
Yn yr enghraifft a ddangosir yn y ddelwedd uchod, mae'r fformiwla edrych yn ei gwneud hi'n hawdd dychwelyd pob gwerth - megis pris, rhan-rif, a chyflenwr - sy'n gysylltiedig â gwahanol ddarnau o galedwedd.
01 o 10
Dychwelyd Gwerthoedd Lluosog gyda VLOOKUP Excel
Mae dilyn y camau a restrir isod yn creu'r fformiwla chwilio a welir yn y ddelwedd uchod a fydd yn dychwelyd nifer o werthoedd o gofnod data unigol.
Mae'r fformiwla edrych yn mynnu bod y swyddogaeth COLUMN yn cael ei nythu y tu mewn i VLOOKUP.
Mae nythu swyddogaeth yn golygu mynd i mewn i'r ail swyddogaeth fel un o'r dadleuon ar gyfer y swyddogaeth gyntaf.
Yn y tiwtorial hwn, caiff y swyddogaeth COLUMN ei gofnodi fel y ddadl rhif mynegai colofn ar gyfer VLOOKUP.
Y cam olaf yn y tiwtorial yw copïo'r fformiwla chwilio i golofnau ychwanegol er mwyn adennill gwerthoedd ychwanegol ar gyfer y rhan a ddewiswyd.
Cynnwys Tiwtorial
- Mynd i'r Data Tiwtorial
- Creu Ystod a enwir ar gyfer y Tabl Data
- Dechrau'r Swyddog VLOOKUP
- Ymdrin â'r Dadansoddiad Gwerth Chwilio gan ddefnyddio Cyfeiriadau Cell Absolute
- Mynd i'r Ddogfen Dadansoddiad Tabl
- Ymuno â'r Function COLUMN Nested
- Cwblhau'r Swyddog VLOOKUP
- Copïo'r Fformiwla Edrych gyda Llenwi Llenwi
- Adalw Data gyda'r Fformiwla Edrych
02 o 10
Rhowch y Data Tiwtorial
Y cam cyntaf yn y tiwtorial yw cofnodi'r data yn daflen waith Excel.
Er mwyn dilyn y camau yn y tiwtorial, rhowch y data a ddangosir yn y ddelwedd uchod i'r celloedd canlynol.
- Rhowch yr ystod uchaf o ddata i gelloedd D1 i G1
- Rhowch yr ail amrediad i gelloedd D4 i G10
Bydd y meini prawf chwilio a'r fformiwla chwilio a grëwyd yn ystod y tiwtorial hwn yn cael eu cynnwys yn rhes 2 o'r daflen waith.
Nid yw'r tiwtorial yn cynnwys y fformatio a welir yn y ddelwedd, ond ni fydd hyn yn effeithio ar sut mae'r fformiwla edrych yn gweithio.
Mae gwybodaeth am opsiynau fformatio tebyg i'r rhai a welir uchod ar gael yn y Tiwtorial Fformatu Excel Sylfaenol hwn.
Camau Tiwtorial
- Rhowch y data fel y gwelir yn y ddelwedd uchod i gelloedd D1 i G10
03 o 10
Creu Ystod a enwir ar gyfer y Tabl Data
Mae ystod a enwir yn ffordd hawdd o gyfeirio at ystod o ddata mewn fformiwla. Yn hytrach na theipio yn y cyfeiriadau cell ar gyfer y data, gallwch deipio enw'r ystod yn unig.
Ail fantais ar gyfer defnyddio amrediad a enwir yw na fydd y cyfeiriadau cell ar gyfer yr amrediad hwn byth yn newid hyd yn oed pan fo'r fformiwla yn cael ei gopïo i gelloedd eraill yn y daflen waith.
Mae enwau amrediad, felly, yn ddewis arall i ddefnyddio cyfeiriadau cell absoliwt i atal camgymeriadau wrth gopïo fformiwlâu.
Sylwer: Nid yw'r enw amrediad yn cynnwys y penawdau na'r enwau maes ar gyfer y data (rhes 4) ond dim ond y data ei hun.
Camau Tiwtorial
- Amlygu celloedd D5 i G10 yn y daflen waith i'w dewis
- Cliciwch ar y Blwch Enw a leolir uwchben golofn A
- Teipiwch "Tabl" (dim dyfynbrisiau) yn y Blwch Enw
- Gwasgwch yr allwedd ENTER ar y bysellfwrdd
- Bellach mae gan gelloedd D5 i G10 yr enw amrediad "Tabl". Byddwn yn defnyddio'r enw ar gyfer dadl gronfa tabl VLOOKUP yn ddiweddarach yn y tiwtorial
04 o 10
Agor y Blwch Dialog VLOOKUP
Er ei bod hi'n bosibl i deipio ein fformiwla edrych yn uniongyrchol i mewn i gell mewn taflen waith, mae llawer o bobl yn ei chael yn anodd cadw'r cystrawen yn syth - yn enwedig ar gyfer fformiwla gymhleth megis yr un yr ydym yn ei ddefnyddio yn y tiwtorial hwn.
Un arall, yn yr achos hwn, yw defnyddio'r blwch deialu VLOOKUP. Mae gan bron pob un o swyddogaethau Excel bocs deialog sy'n eich galluogi i nodi pob un o ddadleuon y swyddogaeth ar linell ar wahân.
Camau Tiwtorial
- Cliciwch ar gell E2 y daflen waith - y lleoliad lle bydd canlyniadau'r fformwla edrych dau-ddimensiwn yn cael ei arddangos
- Cliciwch ar daflen Fformiwlâu'r rhuban
- Cliciwch ar yr opsiwn Chwilio a Chyfeirio yn y rhuban i agor y rhestr ostwng swyddogaeth
- Cliciwch ar VLOOKUP yn y rhestr i agor blwch deialog y swyddogaeth
05 o 10
Ymdrin â'r Dadansoddiad Gwerth Chwilio gan ddefnyddio Cyfeiriadau Cell Absolute
Fel arfer, mae'r gwerth edrych yn cyfateb i faes data yng ngholofn gyntaf y tabl data.
Yn ein hes enghraifft, mae'r gwerth edrych yn cyfeirio at enw'r rhan caledwedd yr ydym am ddod o hyd i wybodaeth amdano.
Y mathau o ddata a ganiateir ar gyfer y gwerth edrych yw:
- data testun
- gwerth rhesymegol (GWIR neu FALSE yn unig)
- nifer
- Cyfeirnod cell at werth yn y daflen waith
Yn yr enghraifft hon, byddwn yn cofnodi cyfeirnod y gell i ble y bydd yr enw rhan wedi'i leoli - cell D2.
Cyfeiriadau Cell Absolute
Mewn cam diweddarach yn y tiwtorial, byddwn yn copïo'r fformiwla chwilio yn cell E2 i gelloedd F2 a G2.
Fel arfer, pan fydd fformiwlâu yn cael eu copïo yn Excel, mae cyfeiriadau cell yn newid i adlewyrchu eu lleoliad newydd.
Os bydd hyn yn digwydd, bydd D2 - y cyfeirnod cell ar gyfer y gwerth edrych - yn newid wrth i'r fformiwla gael ei gopďo gan greu gwallau yn y celloedd F2 a G2.
Er mwyn atal y gwallau, byddwn yn trosi cyfeirnod cell D2 i gyfeirnod cell absoliwt .
Nid yw cyfeiriadau cell absoliwt yn newid pan fydd fformiwlâu yn cael eu copïo.
Crëir cyfeiriadau cell absoliwt trwy wasgu'r allwedd F4 ar y bysellfwrdd. Mae gwneud hynny yn ychwanegu arwyddion doler o gwmpas y cyfeirnod cell fel $ D $ 2
Camau Tiwtorial
- Cliciwch ar y llinell lookup_value yn y blwch deialog
- Cliciwch ar gell D2 i ychwanegu'r cyfeirnod cell hwn at y llinell lookup_value . Dyma'r gell lle byddwn yn teipio enw'r rhan yr ydym yn chwilio am wybodaeth amdano
- Heb symud y pwynt mewnosod, pwyswch yr allwedd F4 ar y bysellfwrdd i drosi D2 i gyfeirnod cell absoliwt $ D $ 2
- Gadewch y blwch deialog VLOOKUP ar agor ar gyfer y cam nesaf yn y tiwtorial
06 o 10
Mynd i'r Ddogfen Dadansoddiad Tabl
Y tabl o ddata yw'r tabl o ddata y mae'r fformiwla chwilio yn chwilio amdano i ddod o hyd i'r wybodaeth yr ydym ei eisiau.
Rhaid i'r amrywiaeth bwrdd gynnwys o leiaf ddau golofn o ddata .
- mae'r golofn gyntaf yn cynnwys y ddadl werth edrych (y cam blaenorol yn y tiwtorial)
- bydd yr ail, ac unrhyw golofnau ychwanegol, yn cael eu chwilio gan y fformiwla chwilio i ddod o hyd i'r wybodaeth a nodwn.
Rhaid cofnodi'r ddadl lluosog tabl fel naill ai ystod sy'n cynnwys cyfeiriadau cell ar gyfer y tabl data neu fel enw amrediad .
Ar gyfer yr enghraifft hon, byddwn yn defnyddio enw amrediad a grëwyd yng ngham 3 y tiwtorial.
Camau Tiwtorial
- Cliciwch ar y llinell table_array yn y blwch deialog
- Teipiwch "Tabl" (dim dyfynbrisiau) i nodi'r enw amrediad ar gyfer y ddadl hon
- Gadewch y blwch deialog VLOOKUP ar agor ar gyfer y cam nesaf yn y tiwtorial
07 o 10
Nestio'r Swyddogaeth COLUMN
Fel rheol dim ond VLOOKUP sy'n dychwelyd data o un golofn o dabl data a gosodir y golofn hon yn ôl y ddadl rhif mynegai colofn .
Yn yr enghraifft hon, fodd bynnag, mae gennym dri cholofn yr ydym am ddychwelyd data felly mae angen ffordd arnom i newid rhif mynegai colofnau yn hawdd heb olygu ein fformiwla edrych.
Dyma lle mae'r swyddogaeth COLUMN yn dod i mewn. Trwy ei gynnwys fel dadl rhif mynegai'r golofn , bydd yn newid wrth i'r fformiwla chwilio gael ei gopïo o gell D2 i gelloedd E2 a F2 yn ddiweddarach yn y tiwtorial.
Swyddogaethau Neidio
Mae swyddogaeth COLUMN, felly, yn gweithredu fel dadl rhif mynegai colofn VLOOKUP.
Gwneir hyn trwy nythu swyddogaeth COLUMN y tu mewn i VLOOKUP yn llinell Col_index_num y blwch deialog.
Ymuno â Swyddogaeth COLUMN â llaw
Pan fo swyddogaethau nythu, nid yw Excel yn ein galluogi i agor blwch deialog yr ail swyddogaeth i roi ei ddadleuon i mewn.
Rhaid cofnodi'r swyddogaeth COLUMN, felly, â llaw yn y llinell Col_index_num .
Dim ond un ddadl sydd gan y swyddogaeth COLUMN - y ddadl Gyfeirio sy'n gyfeirnod celloedd.
Dewis Argraff Cyfeirnod y Swyddogaeth COLUMN
Gwaith y swyddogaeth COLUMN yw dychwelyd nifer y golofn a roddwyd fel y ddadl Gyfeirnod .
Mewn geiriau eraill, mae'n trosi llythyr y golofn i rif gyda cholofn A sef y golofn gyntaf, colofn B yr ail ac yn y blaen.
Ers y maes data cyntaf yr ydym am ei ddychwelyd yw pris yr eitem - sydd yng ngholofn dau o'r tabl data - gallwn ddewis y cyfeirnod cell ar gyfer unrhyw gell yng ngholofn B fel y Dystysgrif Cyfeirnod er mwyn cael rhif 2 ar gyfer y ddadl Col_index_num .
Camau Tiwtorial
- Yn y blwch deialog swyddogaeth VLOOKUP, cliciwch ar y llinell Col_index_num
- Teipiwch golofn enw'r swyddogaeth ac yna braced cylch agored " ( "
- Cliciwch ar gell B1 yn y daflen waith i nodi'r cyfeirnod cell hwnnw fel y ddadl Gyfeirnod
- Teipiwch fraced rownd cau " ) " i gwblhau'r swyddogaeth COLUMN
- Gadewch y blwch deialog VLOOKUP ar agor ar gyfer y cam nesaf yn y tiwtorial
08 o 10
Mynd i Gofnod Amser VLOOKUP Lookup
Mae dadl Range_lookup VLOOKUP yn werth rhesymegol (TRUE neu FALSE yn unig) sy'n nodi a ydych am i VLOOKUP ddod o hyd i gêm union neu fras i'r Lookup_value.
- Os GWIR, neu os na chaiff y ddadl hon ei hepgor, mae VLOOKUP yn dychwelyd naill ai'n union gyfatebol i'r Lookup_value, neu, os na chafwyd union gyfatebol, mae VLOOKUP yn dychwelyd y gwerth mwyaf nesaf. Ar gyfer y fformiwla i wneud hyn, rhaid datrys y data yn y golofn gyntaf o Table_array mewn trefn esgynnol .
- Os yw FALSE, bydd VLOOKUP ond yn defnyddio union gyfatebol â'r Lookup_value. Os oes dau neu fwy o werthoedd yn y golofn gyntaf o Table_array sy'n cydweddu â'r gwerth edrych, defnyddir y gwerth cyntaf. Os na cheir hyd i union gêm, dychwelir gwall # N / A.
Yn y tiwtorial hwn, gan ein bod yn chwilio am wybodaeth benodol am eitem benodol o galedwedd, byddwn yn gosod Range_lookup yn gyfwerth â Ffug .
Camau Tiwtorial
- Cliciwch ar y llinell Range_lookup yn y blwch deialog
- Teipiwch y gair Ffug yn y llinell hon i nodi ein bod am i VLOOKUP ddychwelyd union gyfatebol ar gyfer y data yr ydym yn chwilio amdani
- Cliciwch OK i gwblhau'r fformiwla edrych a chau blwch ymgom
- Gan nad ydym eto wedi mynd i'r meini prawf chwilio i mewn i gell D2 bydd gwall # N / A yn bresennol yn y gell E2
- Caiff y gwall hwn ei gywiro pan fyddwn yn ychwanegu'r meini prawf chwilio yn ystod cam olaf y tiwtorial
09 o 10
Copïo'r Fformiwla Edrych gyda Llenwi Llenwi
Bwriad y fformiwla chwilio yw adfer data o nifer o golofnau o'r tabl data ar yr un pryd.
I wneud hyn, rhaid i'r fformiwla chwilio fod yn byw ym mhob un o'r meysydd y mae arnom eisiau gwybodaeth amdanynt.
Yn y tiwtorial hwn, rydym am iddo adennill data o golofnau 2, 3 a 4 o'r tabl data - hynny yw pris, y rhif rhan, ac enw'r cyflenwr pan fyddwn yn nodi enw rhan fel y Gofyniad chwilio.
Gan fod y data wedi'i osod allan mewn patrwm rheolaidd yn y daflen waith , gallwn gopïo'r fformiwla edrych ar gelloedd E2 i gelloedd F2 a G2.
Wrth i'r fformiwla gael ei gopïo, bydd Excel yn diweddaru'r cyfeirnod cell cymharol yn swyddogaeth COLUMN (B1) i adlewyrchu lleoliad newydd y fformiwla.
Yn ogystal, nid yw Excel yn newid cyfeirnod cell absoliwt $ D $ 2 a'r Tabl amrediad a enwir wrth i'r fformiwla gael ei gopïo.
Mae yna fwy nag un ffordd i gopïo data yn Excel, ond mae'n debyg mai ffordd hawddaf yw defnyddio'r Llenwad Dileu .
Camau Tiwtorial
- Cliciwch ar gell E2 - lle mae'r fformiwla chwilio yn cael ei leoli - i'w wneud yn y celloedd gweithredol
- Rhowch y pwyntydd llygoden dros y sgwâr du yn y gornel waelod dde. Bydd y pwyntydd yn newid i arwydd mwy " + " - dyma'r daflen lenwi
- Cliciwch ar y botwm chwith y llygoden a llusgo'r daflen llenwi i gell G2
- Rhyddhau'r botwm llygoden a dylai cell F3 gynnwys y fformiwla edrych dau-ddimensiwn
- Os gwneir yn gywir, dylai celloedd F2 a G2 nawr gynnwys y gwall # N / A sydd yn bresennol yng ngell E2
10 o 10
Mynd i'r Meini Prawf Edrych
Unwaith y bydd y fformiwla chwilio wedi cael ei gopïo i'r celloedd gofynnol y gellir ei ddefnyddio i adennill gwybodaeth o'r tabl data.
I wneud hynny, deipiwch enw'r eitem yr ydych am ei adfer i mewn i'r cell Lookup_value (D2) a phwyswch yr allwedd ENTER ar y bysellfwrdd.
Ar ôl ei wneud, dylai pob celloedd sy'n cynnwys y fformiwla chwilio gael darn o ddata gwahanol am yr eitem caledwedd rydych chi'n chwilio amdano.
Camau Tiwtorial
- Cliciwch ar gell D2 yn y daflen waith
- Teipiwch Widget i mewn i gell D2 a phwyswch yr allwedd ENTER ar y bysellfwrdd
- Dylai'r wybodaeth ganlynol gael ei harddangos mewn celloedd E2 i G2:
- E2 - $ 14.76 - pris teclyn
- F2 - PN-98769 - y rhan rhan ar gyfer teclyn
- G2 - Widgets Inc. - enw'r cyflenwr ar gyfer gwefannau
- Prawf ymhellach y fformiwla trefn VLOOKUP trwy deipio enw rhannau eraill i mewn i gell D2 ac arsylwi canlyniadau yng nghelloedd E2 i G2
Os yw neges gwall fel #REF! yn ymddangos yn y celloedd E2, F2, neu G2, gall y rhestr hon o negeseuon gwall VLOOKUP eich helpu i benderfynu ble mae'r broblem yn gorwedd.