Chimodzi mwa zinthu zothandiza kwambiri pa Excel ndi operator INDEX. Imafunafuna deta pamtunda pamtunda wa mzere ndi ndondomekoyi, ndikubwezera zotsatira ku selo yoyenera. Koma mphamvu zonse za ntchitoyi zimawululidwa pamene zimagwiritsidwa ntchito m'machitidwe ophatikiza pamodzi ndi ogwira ntchito ena. Tiyeni tiwone njira zosiyanasiyana zomwe zingagwiritsidwe ntchito.
Kugwiritsa ntchito INDEX ntchito
Woyendetsa INDEX ndilo gulu la ntchito kuchokera m'gululi "Zolumikizana ndi zolemba". Lili ndi mitundu iwiri: kwa zolemba ndi zolemba.
Mndandanda wa zojambulazo uli ndi mawu ofanana awa:
= INDEX (mzere; mndandanda_mndandanda; ndondomeko_mndandanda)
Pankhaniyi, mfundo ziwiri zomalizazi zingagwiritsidwe ntchito palimodzi pamodzi ndi chimodzi mwa izo, ngati ndondomekoyi ndi imodzi. Muzigawo zosiyanasiyana, zikhalidwe zonse ziyenera kugwiritsidwa ntchito. Tiyeneranso kukumbukira kuti mzere ndi chiwerengero cha chiwerengero si nambala yomwe ili pamakonzedwe a pepala, koma ndondomeko mkati mwazomwezo.
Mawu omasulidwe a zosiyana siyana amawoneka ngati awa:
= INDEX (mgwirizano; mzere_mndandanda; mndandanda_mtengo; [malo_numberu])
Pano mungagwiritse ntchito mfundo imodzi yokha mwa njira yomweyo: "Nambala ya mzere" kapena "Nambala ya column". Kutsutsana "Nambala Nambala" Kawirikawiri zimakhala zomveka komanso zimagwiritsidwa ntchito pokhapokha ngati pali zochitika zosiyanasiyana.
Potero, woyendetsafufuza amafufuza deta muzomwe akufotokozera pofotokoza mzere kapena mzere. Ntchitoyi ndi yofanana kwambiri ndiyi vpr operator, koma mosiyana ndi iyo ikhoza kufufuza pafupifupi paliponse, osati kumbali ya kumapeto kwa tebulo.
Njira 1: Gwiritsani ntchito INDEX operekera malemba
Tiyeni, poyamba, tifufuze, pogwiritsa ntchito chitsanzo chosavuta, ndondomeko yogwiritsira ntchito wogwiritsira ntchito INDEX chifukwa chotsatira.
Tili ndi tebulo la malipiro. M'ndandanda yoyamba, mayina a antchito akuwonetsedwa, m'chiwiri - tsiku la kulipira, ndipo lachitatu - kuchuluka kwa mapindu. Tifunika kusonyeza dzina la wogwira ntchitoyo mu gawo lachitatu.
- Sankhani selo momwe zotsatira zogwirira ntchito ziwonetsedwe. Dinani pazithunzi "Ikani ntchito"yomwe ili pomwepo kumanzere kwa bar.
- Njira yothandizira imapezeka. Oyang'anira ntchito. M'gululi "Zolumikizana ndi zolemba" chida ichi kapena "Mndandanda wathunthu wa alfabeti" fufuzani dzina INDEX. Tikapeza ogwiritsira ntchito, tizisankha ndipo dinani pa batani. "Chabwino"yomwe ili pansi pazenera.
- Fasilo yaing'ono imatsegulidwa kumene muyenera kusankha imodzi ya mitundu: "Mzere" kapena "Lumikizanani". Njira yomwe tikusowa "Mzere". Iko ili yoyamba ndi yosankhidwa mwachinsinsi. Kotero, ife tikungoyenera kukanikiza batani "Chabwino".
- Ntchito yotsutsana yenera ikutsegula. INDEX. Monga tanenera kale, ili ndi zifukwa zitatu, ndipo, motero, magawo atatu odzaza.
Kumunda "Mzere" Muyenera kufotokozera adiresi ya deta yomwe ikutsatiridwa. Ikhoza kuthamangitsidwa ndi dzanja. Koma kuti tikwaniritse ntchitoyi, tipitiliza mosiyana. Ikani cholozeracho m'munda woyenera, ndipo pendekani zonse zomwe zili pamasamba. Zitatha izi, adiresi yam'manja imasonyezedwa nthawi yomweyo kumunda.
Kumunda "Nambala ya mzere" ikani chiwerengerocho "3", chifukwa chofunikira kudziwa dzina lachitatu m'ndandanda. Kumunda "Nambala ya column" ikani nambalayi "1"popeza mndandanda uli ndi mayina ndiwo woyamba m'masankhidwe.
Pambuyo pazinthu zonse zomwe tazipanga, timasankha pa batani "Chabwino".
- Zotsatira za kugwiritsidwa ntchito zikuwonetsedwa mu selo zomwe zafotokozedwa mu ndime yoyamba ya malangizo awa. Ndilo dzina lomaliza lomwe ndilo lachitatu m'ndandanda mwadongosolo ladasankhidwa.
Tapenda momwe ntchito ikugwirira ntchito. INDEX muzithunzi zambiri (mizere yambiri ndi mizere). Ngati maulendowa anali ofanana, ndiye kuti kudzaza deta muzenera zotsutsana kungakhale kosavuta. Kumunda "Mzere" njira yomweyi pamwambapa, timafotokoza adiresi yake. Pankhaniyi, kusiyana kwa deta kumakhala ndi zikhulupiliro zokha m'mbali imodzi. "Dzina". Kumunda "Nambala ya mzere" tchulani mtengo "3", chifukwa mukufunikira kudziwa deta kuchokera ku gawo lachitatu. Munda "Nambala ya column" Mwachidziwikire, mukhoza kusiya icho chopanda kanthu, popeza tili ndi gawo limodzi lokha limene limagwiritsidwa ntchito ndi mzere umodzi wokha. Timakanikiza batani "Chabwino".
Zotsatira zidzakhala zofanana ndi zapamwamba.
Unali chitsanzo chosavuta kuti muwone momwe ntchitoyi ikugwirira ntchito, koma pakuchita njirayi ntchito yake idakagwiritsidwa ntchito kawirikawiri.
Phunziro: Excel ntchito wizara
Njira 2: gwiritsani ntchito pamodzi ndi wogwiritsa ntchito MATCH
Mwachizoloŵezi, ntchitoyi INDEX kawirikawiri amagwiritsidwa ntchito ndi kutsutsana GANIZANI. Bunch INDEX - GANIZANI ndi chida champhamvu pamene mukugwira ntchito ku Excel, yomwe imasintha mosavuta mu ntchito yake kuposa momwe zimagwirira ntchito mofanana ndi woyendetsa Vpr.
Ntchito yaikulu ya ntchitoyo GANIZANI ndi chiwonetsero cha nambala mwa dongosolo la mtengo wapadera muzithunzi zosankhidwa.
Opaleshoni yamagetsi GANIZANI monga:
= MATCHA (wofufuzira, fufuzani, [match_type])
- Kufuna mtengo - ichi ndi mtengo umene malo ake omwe tikufunafuna;
- Ankawoneka - uwu ndilo mtengo womwe mtengo uwu uli;
- Mtundu wa mapu - Ichi ndi choyimira chosankha chomwe chimatsimikizira ngati molondola kapena pafupi kufufuza zinthu. Tidzayang'ana zoyenera, choncho mfundoyi siigwiritsidwe ntchito.
Ndi chida ichi mutha kuyambitsa kutsutsa kwazitsutso. "Nambala ya mzere" ndi "Nambala ya column" mu ntchito INDEX.
Tiyeni tiwone momwe izi zingachitire ndi chitsanzo chapadera. Timagwira ntchito limodzi ndi tebulo lomwelo, limene takambirana pamwambapa. Mosiyana, tili ndi magawo ena awiri - "Dzina" ndi "Mtengo". Ndikofunika kuti mutengere dzina la wogwila ntchito, ndalama zomwe mumapeza zimasonyezedwa. Tiyeni tiwone momwe izi zingagwiritsidwe ntchito pogwiritsa ntchito ntchito INDEX ndi GANIZANI.
- Choyamba, tidzakhala ndi mtundu wa wothandizira wa Parfenov DF omwe timalandira.
- Sankhani selo kumunda "Mtengo"momwe zotsatira zomaliza zidzawonetsedwa. Yambani zenera zotsutsana INDEX chifukwa chotsatira.
Kumunda "Mzere" ife timalowa muzolumikizana za gawo limene malipiro a malipiro a antchito ali.
Munda "Nambala ya column" timachoka chopanda kanthu, chifukwa tikugwiritsa ntchito mtundu umodzi mwachitsanzo.
Koma kumunda "Nambala ya mzere" tikungoyenera kulemba ntchito GANIZANI. Kuti tilembere, timatsatira mawu ofanana omwe tatchulidwa pamwambapa. Nthawi yomweyo mumunda mulowe dzina la woyendetsa "KUKHALA" popanda ndemanga. Kenaka mutsegule pakanema ndikuwonetseratu makonzedwe a mtengo wofunikila. Awa ndiwo makonzedwe a selo momwe ife timalembera payekha dzina la antchito a Parfenov. Timayika semicolon ndikudziwitsani makonzedwe a magulu oyang'ana. Kwa ife, iyi ndi adiresi ya chigawocho ndi mayina a antchito. Pambuyo pake, tseka pakani.
Zonsezi zitatha, dinani pa batani "Chabwino".
- Zotsatira za kuchuluka kwa mapindu a Parfenova DF mutatha kusinthidwa akuwonetsedwa m'munda "Mtengo".
- Tsopano ngati mundawo "Dzina" timasintha zokhudzana ndi "Parfenov D.F."pa, mwachitsanzo, "Popova M.D."ndiye mtengo wa malipiro m'munda udzasintha mosavuta. "Mtengo".
Njira 3: kukonza matebulo angapo
Tsopano tiyeni tiwone momwe akugwiritsira ntchito woyendetsa INDEX Mungathe kusamalira matebulo angapo. Mtsutso wochuluka udzagwiritsidwa ntchito paichi. "Nambala Nambala".
Tili ndi matebulo atatu. Gome lililonse limasonyeza malipiro a antchito kwa mwezi umodzi. Ntchito yathu ndi kupeza malipiro (gawo lachitatu) la wogwira ntchito yachiwiri (mzere wachiwiri) mwezi wachitatu (dera lachitatu).
- Sankhani selo limene zotsatira zake zidzawonetsedwa komanso mwa njira yowonekera Mlaliki Wachipangizo, koma posankha mtundu wamagetsi, sankhani malingaliro owonetsera. Timafunikira izi chifukwa ndi mtundu uwu umene umathandizira ntchito ndi mkangano "Nambala Nambala".
- Fesholo yotsutsana ikutsegula. Kumunda "Lumikizanani" Tiyenera kufotokoza maadiresi a mitundu itatu. Kuti muchite izi, yikani mtolo mmunda ndikusankha mtundu woyamba ndi batani lamanzere lomwe likugwiritsidwa ntchito. Ndiye ife timayika semicoloni. Izi ndizofunikira kwambiri, chifukwa ngati mwangopita kumasankhidwe atsopano, adiresiyo idzangosintha malo omwe apitako. Tsono, mutangoyamba kumene semicoloni, sankhani zotsatirazi. Kenaka timayika semicolon ndikusankha gulu lomaliza. Mawu onse omwe ali kumunda "Lumikizanani" tengerani maubereka.
Kumunda "Nambala ya mzere" tchulani nambala "2", popeza tikuyang'ana dzina lachiwiri mndandanda.
Kumunda "Nambala ya column" tchulani nambala "3", chifukwa gawo la malipiro ndilo lachitatu pa tebulo lililonse.
Kumunda "Nambala Nambala" ikani chiwerengerocho "3", popeza tikufunikira kupeza deta mu tebulo lachitatu, lomwe liri ndi malipiro a malipiro a mwezi wachitatu.
Deta yonse italowa, dinani pa batani "Chabwino".
- Pambuyo pake, zotsatira za mawerengedwe zikuwonetsedwa mu selo yoyamba yosankhidwa. Zimapereka malipiro a malipiro a wantchito wachiwiri (V. Safronov) mwezi wachitatu.
Njira 4: Chiwerengero chachiwerengero
Fomu yawotchulidwa nthawi zambiri sagwiritsidwa ntchito ngati mawonekedwe, koma ingagwiritsidwe ntchito pokhapokha mutagwira ntchito zosiyanasiyana, komanso zosowa zina. Mwachitsanzo, angagwiritsidwe ntchito kuwerengera ndalamazo kuphatikizapo woyendetsa SUM.
Powonjezerapo ndalamazo SUM ili ndi mawu ofanana awa:
= SUM (adiresi ya gulu)
Momwe ife timachitira, ndalama zomwe antchito onse amapindula kwa mweziwo zikhoza kuwerengedwa pogwiritsira ntchito njira iyi:
= SUM (C4: C9)
Koma mukhoza kusintha pang'ono pogwiritsa ntchito ntchitoyi INDEX. Ndiye ziwoneka ngati izi:
= SUM (C4: INDEX (C4: C9; 6))
Pachifukwa ichi, makonzedwe a chiyambi cha mndandanda amasonyeza selo limene limayambira. Koma m'makonzedwe owonetsera mapeto a gulu, woyendetsa ntchito amagwiritsidwa ntchito. INDEX. Pankhaniyi, kukangana koyamba kwa woyendetsa INDEX imasonyeza zosiyana, ndipo yachiwiri ku selo yake yotsiriza ndi yachisanu ndi chimodzi.
Phunziro: Zogwiritsira ntchito za Excel
Monga mukuonera, ntchitoyi INDEX angagwiritsidwe ntchito mu Excel pofuna kuthetsa ntchito zosiyanasiyana. Ngakhale takhala tikulingalira kutali ndi njira zonse zomwe zingatheke kuti tigwiritse ntchito, koma ndizofunikira kwambiri. Pali mitundu iwiri ya ntchitoyi: Buku ndi zolemba. Zogwira mtima kwambiri zingagwiritsidwe ntchito pamodzi ndi ogwira ntchito ena. Mafomu omwe amapangidwa motere adzatha kuthetsa ntchito zovuta kwambiri.
