Drwy ddefnyddio fformiwla ar ffurf yn Excel, gallwn greu fformiwla chwilio sy'n defnyddio meini prawf lluosog i ddod o hyd i wybodaeth mewn cronfa ddata neu dabl o ddata.
Mae'r fformiwla array yn golygu nythu swyddogaeth MATCH y tu mewn i'r swyddogaeth INDEX .
Mae'r tiwtorial hwn yn cynnwys enghraifft gam wrth gam o greu fformiwla chwilio sy'n defnyddio meini prawf lluosog i ddod o hyd i gyflenwr Widgets titaniwm mewn cronfa ddata sampl.
Yn dilyn y camau yn y pynciau tiwtorial isod teithiau cerdded chi trwy greu a defnyddio'r fformiwla a welir yn y ddelwedd uchod.
01 o 09
Mynd i'r 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 F2
- Rhowch yr ail amrediad i gelloedd D5 i F11
Mae gweddillion 3 a 4 wedi'u gadael yn wag er mwyn darparu ar gyfer y fformiwla ar ffurf a grëwyd yn ystod y tiwtorial hwn.
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.
02 o 09
Dechrau'r Swyddog INDEX
Y swyddogaeth INDEX yw un o'r ychydig yn Excel sydd â ffurflenni lluosog. Mae gan y swyddogaeth Ffurflen Array a Ffurflen Gyfeirio .
Mae'r Ffurflen Array yn dychwelyd y data gwirioneddol o gronfa ddata neu dabl o ddata, tra bod y Ffurflen Gyfeirio yn rhoi cyfeirnod cell neu leoliad y data yn y tabl i chi.
Yn y tiwtorial hwn byddwn yn defnyddio'r Ffurflen Array gan ein bod am wybod enw cyflenwr ar gyfer gwisgoedd titaniwm yn hytrach na chyfeirnod y gell i'r cyflenwr hwn yn ein cronfa ddata.
Mae gan bob ffurflen restr wahanol o ddadleuon y mae'n rhaid eu dewis cyn dechrau'r swyddogaeth.
Camau Tiwtorial
- Cliciwch ar gell F3 i'w wneud yn y gell weithredol . Dyma lle y byddwn yn mynd i mewn i'r swyddogaeth nythu.
- Cliciwch ar daflen Fformiwlâu'r ddewislen rhuban .
- Dewiswch Chwiliad a Chyfeiriad o'r rhuban i agor y rhestr ostwng swyddogaeth.
- Cliciwch ar INDEX yn y rhestr i ddod â'r blwch deialog Dewis Argymhellion i fyny.
- Dewiswch y ddewislen, rhes_num, col_num yn y blwch deialog.
- Cliciwch OK i agor y blwch deialu swyddogaeth INDEX.
03 o 09
Mynd i mewn i'r Argument Array Function INDEX
Y ddadl gyntaf sy'n ofynnol yw'r ddadl Array. Mae'r ddadl hon yn pennu ystod y celloedd i'w chwilio am y data a ddymunir.
Ar gyfer y tiwtorial hwn, y ddadl hon fydd ein cronfa ddata sampl.
Camau Tiwtorial
- Yn y blwch deialu swyddogaeth INDEX, cliciwch ar y llinell Array .
- Amlygu celloedd D6 i F11 yn y daflen waith i nodi'r amrediad yn y blwch deialog.
04 o 09
Dechrau'r Swyddog MATCH Nested
Wrth nythu un swyddogaeth y tu mewn i un arall, nid yw'n bosib agor yr ail blwch deialog neu swyddogaeth nythedig i nodi'r dadleuon angenrheidiol.
Rhaid teipio'r swyddogaeth nythu fel un o ddadleuon y swyddogaeth gyntaf.
Yn y tiwtorial hwn, bydd y swyddogaeth MATCH nythu a'i ddadleuon yn cael ei roi i ail linell y blwch deialu INDEX swyddogaeth - y llinell Row_num .
Mae'n bwysig nodi, wrth fynd i mewn i swyddogaethau â llaw, fod dadleuon y swyddogaeth yn cael eu gwahanu oddi wrth ei gilydd gan goma "," .
Ymateb i Argymhelliad Swyddogaeth MATCH's Lookup_value
Y cam cyntaf wrth fynd i mewn i'r swyddogaeth MATCH nythedig yw cofnodi'r ddadl Lookup_value .
Y Lookup_value fydd y cyfeirnod lleoliad neu gell ar gyfer y term chwilio yr ydym am ei gyfateb yn y gronfa ddata.
Fel arfer, mae'r Lookup_value yn derbyn dim ond un meini prawf neu derm chwilio. Er mwyn chwilio am feini prawf lluosog, mae'n rhaid inni ymestyn y Nodwedd Amddiffyn .
Gwneir hyn trwy concatenating neu ymuno â dau neu fwy o gyfeiriadau gell gyda'i gilydd gan ddefnyddio'r symbol ampersand " a ".
Camau Tiwtorial
- Yn y blwch deialog swyddogaeth INDEX, cliciwch ar y llinell Row_num .
- Teipiwch enw'r swyddogaeth yn cydweddu gyda braced cylch agored " ( "
- Cliciwch ar gell D3 i nodi'r cyfeirnod cell hwnnw yn y blwch deialog.
- Teipiwch ampersand " & " ar ôl cyfeirnod cell D3 er mwyn ychwanegu cyfeirnod ail gell.
- Cliciwch ar gell E3 i nodi'r cyfeirnod ail gell hwn yn y blwch deialog.
- Teipiwch goma "," ar ôl cyfeirnod cell E3 i gwblhau cofnod dadl Lookup_value swyddogaeth MATCH.
- Gadewch y blwch deialog INDEX ar agor ar gyfer y cam nesaf yn y tiwtorial.
Yn ystod cam olaf y tiwtorial, bydd y Lookup_values yn cael eu rhoi i mewn i gelloedd D3 ac E3 y daflen waith.
05 o 09
Ychwanegu'r Lookup_array ar gyfer y Swyddogaeth MATCH
Mae'r cam hwn yn cynnwys ychwanegu'r ddadl Lookup_array ar gyfer y swyddogaeth MATCH nythedig.
Y Lookup_array yw'r ystod o gelloedd y bydd y swyddogaeth MATCH yn chwilio i ganfod y ddadl Lookup_value a ychwanegu yng ngham blaenorol y tiwtorial.
Gan ein bod wedi nodi dau faes chwilio yn y ddadl Lookup_array , rhaid inni wneud yr un peth ar gyfer y Lookup_array . Mae'r swyddogaeth MATCH yn chwilio am un set yn unig ar gyfer pob tymor a bennir.
I fynd i mewn i fagiau lluosog, byddwn yn defnyddio'r "ampersand" a "i gyd-fynd â'r arrays gyda'i gilydd eto.
Camau Tiwtorial
Rhaid cofnodi'r camau hyn ar ôl y coma a gofnodwyd yn y cam blaenorol ar y llinell Row_num yn y blwch deialog swyddogaeth INDEX.
- Cliciwch ar y llinell Row_num ar ôl y coma i osod y pwynt mewnosod ar ddiwedd y cofnod cyfredol.
- Amlygu celloedd D6 i D11 yn y daflen waith i nodi'r amrediad. Dyma'r amrywiaeth gyntaf y swyddogaeth yw chwilio.
- Teipiwch ampersand " & " ar ôl cyfeiriadau cell D6: D11 am ein bod am i'r swyddogaeth chwilio dau arrays.
- Amlygu celloedd E6 i E11 yn y daflen waith i nodi'r amrediad. Dyma'r ail grynodeb y swyddogaeth yw chwilio.
- Teipiwch goma "," ar ôl cyfeirnod cell E3 i gwblhau cofnod dadl Lookup_array swyddogaeth MATCH.
- Gadewch y blwch deialog INDEX ar agor ar gyfer y cam nesaf yn y tiwtorial.
06 o 09
Ychwanegu'r math Match a Chyflawni'r Swyddogaeth MATCH
Y ddadl Match_type yw'r drydedd ddadl olaf o'r swyddogaeth MATCH .
Mae'r ddadl hon yn dweud wrth Excel sut i gydweddu'r Chwiliad_value gyda gwerthoedd yn y Lookup_array. Y dewisiadau yw: 1, 0, neu -1.
Mae'r ddadl hon yn ddewisol. Os caiff ei hepgor, mae'r swyddogaeth yn defnyddio gwerth diofyn 1.
- os yw'r Match_type = 1 neu wedi ei hepgor: Mae MATCH yn canfod y gwerth mwyaf sy'n llai na'r cyfartal Chwilio am. Rhaid datrys y data Lookup_array mewn trefn esgynnol.
- os yw'r match_type = 0: MATCH yn canfod y gwerth cyntaf sy'n union yr un fath â'r Chwiliad_value. Gellir datrys y data Lookup_array mewn unrhyw drefn.
- os yw'r Match_type = -1: MATCH yn canfod y gwerth lleiaf sy'n fwy na neu'n hafal i'r Lookup_value. Rhaid datrys y data Lookup_array mewn trefn ddisgynnol.
Camau Tiwtorial
Rhaid cofnodi'r camau hyn ar ôl y coma a gofnodwyd yn y cam blaenorol ar y llinell Row_num yn y blwch deialog swyddogaeth INDEX.
- Yn dilyn y coma ar linell Row_num , dechreuwch sero " 0 " gan ein bod am i'r swyddogaeth nythu ddychwelyd union gyfatebol i'r telerau rydym yn eu rhoi mewn celloedd D3 ac E3.
- Teipiwch fraced rownd cau " ) " i gwblhau'r swyddogaeth MATCH.
- Gadewch y blwch deialog INDEX ar agor ar gyfer y cam nesaf yn y tiwtorial.
07 o 09
Yn ôl i'r swyddogaeth INDEX
Nawr bod y swyddogaeth MATCH yn cael ei wneud, byddwn yn symud i drydedd llinell y blwch deialog agored a nodwch y ddadl olaf ar gyfer y swyddogaeth INDEX.
Y ddadl olaf a'r drydedd hon yw'r ddadl Column_num sy'n dweud wrth Excel y rhif colofn yn yr ystod D6 i F11 lle y bydd yn dod o hyd i'r wybodaeth yr ydym am ei ddychwelyd gan y swyddogaeth. Yn yr achos hwn, cyflenwr ar gyfer gwisgoedd titaniwm .
Camau Tiwtorial
- Cliciwch ar y llinell Column_num yn y blwch deialog.
- Rhowch y rhif tri " 3 " (dim dyfynbrisiau) ar y llinell hon gan ein bod yn chwilio am ddata yn nhrydedd golofn yr ystod D6 i F11.
- Peidiwch â Chlicio OK neu gau'r blwch deialu INDEX swyddogaeth. Rhaid iddo fod yn agored ar gyfer y cam nesaf yn y tiwtorial - gan greu'r fformiwla array .
08 o 09
Creu'r Fformiwla Array
Cyn cau'r blwch deialu, mae angen inni droi ein swyddogaeth nythu i mewn i fformiwla ar ffurf .
Fformiwla ar ffurf yw hyn sy'n ei alluogi i chwilio am dermau lluosog yn y tabl data. Yn y tiwtorial hwn rydym yn edrych i gyd-fynd â dau derm: Widgets o golofn 1 a thitaniwm o golofn 2.
Gwneir fformiwla creu amrywiaeth yn Excel trwy wasgu'r allweddi CTRL , SHIFT ac ENTER ar y bysellfwrdd ar yr un pryd.
Effaith pwyso'r allweddi hyn gyda'i gilydd yw amgylchio'r swyddogaeth gyda braces cromlin: {} sy'n nodi ei fod bellach yn fformiwla ar ffurf.
Camau Tiwtorial
- Gyda'r blwch deialog wedi'i chwblhau yn dal i fod ar agor o gam blaenorol y tiwtorial hwn, pwyswch a chadw'r allweddi CTRL a SHIFT ar y bysellfwrdd yna gwasgwch a rhyddhewch yr allwedd ENTER .
- Os caiff ei wneud yn gywir, bydd y blwch deialog yn cau a bydd gwall # N / A yn ymddangos yn y gell F3 - y gell lle'r aethom ati i mewn i'r swyddogaeth.
- Mae'r gwall # N / A yn ymddangos yn y gell F3 oherwydd bod celloedd D3 ac E3 yn wag. D3 ac E3 yw'r celloedd lle rydyn ni wedi dweud wrth y swyddogaeth i ddod o hyd i'r Chwiliadau yn gam 5 y tiwtorial. Unwaith y caiff data ei ychwanegu at y ddau gell hyn, bydd gwybodaeth o'r gronfa ddata yn disodli'r gwall.
09 o 09
Ychwanegu'r Meini Prawf Chwilio
Y cam olaf yn y tiwtorial yw ychwanegu'r termau chwilio i'n taflen waith.
Fel y crybwyllwyd yn y cam blaenorol, rydym yn edrych i gyd-fynd â'r termau Widgets o golofn 1 a Titaniwm o golofn 2.
Os, a dim ond os yw ein fformiwla yn canfod cyfatebol ar gyfer y ddau derm yn y colofnau priodol yn y gronfa ddata, a fydd yn dychwelyd y gwerth o'r drydedd golofn.
Camau Tiwtorial
- Cliciwch ar gell D3.
- Teipiwch Widgets a gwasgwch y Enter Enter ar y bysellfwrdd.
- Cliciwch ar gell E3.
- Teipiwch Titaniwm a gwasgwch y Enter Enter ar y bysellfwrdd.
- Dylai enw'r cyflenwr Widgets Inc ymddangos yn y gell F3 - lleoliad y swyddogaeth gan mai ef yw'r unig gyflenwr sydd wedi'i restru sy'n gwerthu Titaniwm Widgets.
- Pan fyddwch yn clicio ar gell F3 y swyddogaeth gyflawn
{= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
yn ymddangos yn y bar fformiwla uwchben y daflen waith .
Nodyn: Yn ein hes enghraifft, dim ond un cyflenwr oedd ar gyfer gwisgoedd titaniwm. Pe bai mwy nag un cyflenwr, dychwelir y cyflenwr a restrir yn y gronfa ddata yn gyntaf gan y swyddogaeth.