The "Index" mu kupambana: kufotokoza, ntchito ndi zitsanzo

Pa nthawi imeneyi, kupambana kutchuka kwake ndi otsika Mawu okha. Izo zikhale zosavuta kuchita zosiyanasiyana kuwerengetsera zachuma ndi zowerengera pa yambiri deta. Chifukwa chaichi, lili ndi chiwerengero chachikulu cha mbali anamanga-, kuphatikizapo chithandizo. Ena a iwo ali wokhoza kuchita zochita, kuphatikizapo pa waika deta. "Index" ntchito ndi kuwachitira. Mu kupambana, amagwiritsidwanso ntchito onse mosiyana ndi "machesi", tikambirana pansipa.

mafotokozedwe

"Index" ntchito mu kupambana akubwerera mtengo (Buku mtengo) zili selo, ndi mzere anapatsidwa ndi manambala ndime ya tebulo kapena uthunthu ndi mawu.

malembedwe ake yosavuta motere: Index (lomenyera mzere №, ndime №).

nchito imeneyi akhoza opareshoni ndi mzere umodzi kapena ndime umodzi. Pankhaniyi, pambuyo malangizo khamu limodzi azithunzi omwe tikunena wayikidwa nambala wani. Zimatanthauza chiwerengero mzere ngati zosiyanasiyana ndi ndime, ndi mosemphanitsa.

The "Index" mu kupambana nthawizina zimatipatsa ubwino "#REF!". Izi zimachitika nthawi zambiri pamene selo lili ku mphambano za mzere anati ndi mzati ndi kunja osiyanasiyana yake.

zitsanzo ntchito

Taonani zina pogwiritsa ntchito "Index" kuchita.

Kuganiza pali osiyanasiyana m'manyuzipepala 4 ndi mizere 4 (onani. Table). Ngati inu kulowa wina wa maselo tebulo ili kunja osiyanasiyana A1: E5 akuti "= Index (B2: E5, 2, 3)" (popanda zotchingira) ndi atolankhani "Lowani", ndiye kuti "begonia" adzakhala operekedwa ayankhe.

Ngati mukufuna kudziwa mmene ophunzira ambiri Gulu 2, akuti ndi "osagwira" mu bokosi loyenera kulowa mawu otsatirawa: Index (C2: C5, 1).

The "machesi" mu kupambana

Zitsanzo ziwiri anapatsidwa pamwamba sizigwira ntchito ndi akanema lalikulu deta. mfundo ndi yakuti ntchito "Index" mu kupambana amadzisankhira mzere athandizira ndi ndime si tebulo palokha ndipo zosiyanasiyana deta. Ndi kovuta kuchita, pamene tikulankhula ambiri zinthu. Kuti athetse vuto angathandize wina ntchito ekselevskaya.

Taganizirani choncho kumene zosiyanasiyana tichipeza mzere umodzi.

Osiyanasiyana makhalidwe izi B3 mlandu: B6.

Kusankha khungu mu mzere Mwachitsanzo D1. Ife kulowa dzina la chipatso, amene ali malo tikufuna kupeza, mu nkhani iyi, "malalanje". Mu selo (E1), kumene ife tikufuna kuti alembe chogwirizana mzere nambala, kulowa "= machesi (a D1; B3: B6; 0)" (onani gome.). Chifukwa, ukuoneka chiwerengero 3. Ichi ndi chiwerengero mu osiyanasiyana B3: B6 m'mawu oti "lalanje".

Last 0 zikutanthauza kuti mukufuna kupeza machesi enieni ndi mtengo D1.

Kodi kupeza mfundo zonse lemba kuti kukwaniritsa palibe muyezo umodzi

Mu mawonekedwe lili pamwambali, la "machesi 'umabwerera mtengo umodzi wokha (woyamba, mwachitsanzo. E. Top). Koma bwanji ngati mndandanda akubwerezedwa. Pankhaniyi, njira amathandiza zosiyanasiyana. uthunthu wonse wa deta ndi ntchito kuphatikiza kiyi «Ctrl + kuloza + Lowani» ayenera allocated ntchito zawo. Komabe, kuganizira ake si nkhani imeneyi.

The "Index" ndi "machesi" mu kupambana: Zitsanzo

Tiyerekeze kuti muli ndi kusankha kwa zosiyanasiyana mokwanira lalikulu deta chabe zina. Pakuti kuphweka, taganizirani za ochepa zinthu. Mwachitsanzo, muli lipoti la ntchito ya magulu angapo a ophunzira ndi kuwunika awo. Yerekezerani kuti mukufuna chiwerengero cha ana anaonekera mu selo H2 ndi imawunikidwa monga "osagwira".

Kuchita izi, ndi bwino kugwiritsa ntchito ntchito pamodzi. Kudziwa kuti alowe mu H2, kaye mfundo ambiri, amene angagwiritsidwe ntchito kwa cholinga ichi. Makamaka, mtengo wolakalakika akhoza zopezera polemba mu chipindacho "Index = (A2: E5; 1; 2)." Apa ife ntchito zosinthika zitsanzo za m'mbuyomo, pamene nambala mzere ndi ndime ya inaŵerengeredwa pamanja. Komabe, cholinga chathu - kuti automate ndondomeko imeneyi. Kuchita izi, m'malo awiri, ndi wina, amene amasonyeza mzere anakhumba ndi mzati mwa dongosolo lomenyera kulemba ofanana ntchito "machesi" opereka manambala awa. Chonde dziwani kuti ife tikuyang'ana kwa mawu oti "oud" ili selo G2 ndi "T. 2 'kuchokera H2. Komanso, timafunikira machesi enieni, kotero lapitali, lachitatu, mtsutso milandu onse amasonyeza 0.

Kenako, m'malo 1 dongosolo Index (A2: E5; 1, 2) kuti analemba kuti: machesi (G2; A2: A5; 0), ndipo mmalo 2 - machesi (H2; A2: E2; 0).

Pambuyo M'MALO tili: Index (A2: E5; machesi (G2; A2: A5; 0), machesi (H2; A2: E2; 0)). Chifukwa, kukanikiza "Lowani", tili "10" mchipindamo.

Kodi kuwonjezera njira kwa osiyanasiyana analandira

Monga amadziwika, ndi "Index" mu kupambana zikhoza "anatambasula" kwa mitundu ina. Mu chitsanzo zafotokozedwa pamwambapa, ili ndi maselo onse 4 H2: J3. Pa nkhani imeneyi m'pofunika kuti muganize momwe kutero, "anatambasula" chilinganizo chabwino ndi pansi kuti mfundo zolondola.

Vuto lalikulu kwagona kuti khamu A2: E5 ali ndi adiresi wachibale. Mungakonzere izi, muyenera kutembenukira mu mtheradi. Kuti khamu ili kwalembedwa monga $ A $ 2: $ E $ 5. The chomwecho muyenera kuchita ntchito zonse anamanga-, mwachitsanzo, ayenera kuyang'ana ngati zikugwirizana ($ G $ 2, $ A $ 2: $ A $ 5, 0) .. Ndipo machesi ($ H $ 2, ndipo A $ 2: $ E 2; 0).

Mawonekedwe womaliza wa chilinganizo ndi: Index ($ A $ 2: $ E $ 5, machesi ($ G $ 2, $ A $ 2: A $ 5, 0), machesi ($ H $ 2, $ A $ 2: $ E $ 2, 0)).

Chifukwa, ife tebulo pansipa

Kuti chifukwa m'pofunika onetsetsani kuti mfundo lemba analembedwa molondola, kuphatikizapo mulibe zolakwika kapena mipata owonjezera. Apo ayi pulogalamu sadzakhala kuziona ngati chimodzimodzi.

Tsopano inu mukudziwa momwe ntchito "Index" mu kupambana. Zitsanzo za ntchito yake ndi "machesi" Inu mukudziwa, ndipo mudzatha kuti molondola angazigwiritsire ntchito kuti tithetse mavuto zambiri.