Chimodzi mwazinthu zofunikira kwambiri pulogalamu ya Excel ndi opanga ma INDEX. Imafufuza zosankha m'malire a mzere ndi mzere womwe, ndikubwezeretsanso ku selo lomwe lidasankhidwa kale. Koma kuthekera kwathunthu kwa ntchitoyi kumawululidwa mukagwiritsidwa ntchito pamafomu ovuta kuphatikiza ndi ena ogwira ntchito. Tiyeni tiwone zosankha zingapo momwe zingagwiritsire ntchito.
Kugwiritsa ntchito INDEX ntchito
Wogwiritsa ntchito INDEX A gulu la ntchito kuchokera pagululi Malingaliro ndi Kufika. Ili ndi mitundu iwiri: ya ma array ndi maumboni.
Kusankha kwa ma arraw kuli ndi syntax yotsatirayi:
= INDEX (mndandanda;
Nthawi yomweyo, malingaliro awiri omaliza mu fomulamu angagwiritsidwe ntchito onse pamodzi ndipo aliyense waiwo, ngati gulu ndilofanana. Pazigawo zamitundu mitundu, mfundo zonsezi ziyenera kugwiritsidwa ntchito. Tiyeneranso kukumbukiranso kuti mzere ndi nambala sizimvetseka kuti ndi nambala pazogwirizana ndi pepalalo, koma dongosolo lomwe lili munkalatayo.
Syntax yamasinthidwe amawu ndi motere:
= INDEX (yolumikizira; mzere;
Pano, momwemonso, mutha kugwiritsa ntchito mfundo imodzi mwa awiri: Nambala ya mzere kapena Chiwerengero Cha Column. Kukangana "Nambala ya Area" Nthawi zambiri imakhala yosankha ndipo imagwiritsidwa ntchito pokhapokha ngati magulu angapo akukhudzidwa.
Chifukwa chake, wothandizirayo amafufuza zosankha zomwe zili mumtundu womwe wafotokozedwayo akakhala mzere kapena mzere. Izi zikufanana kwambiri ndi Wothandizira wa VLR, koma mosiyana ndi iyo, imatha kusaka pafupifupi kulikonse, osati kungolowera kumanzere kwa tebulo.
Njira 1: gwiritsani ntchito INDEX opangira mawonekedwe
Choyamba, tiyeni tisanthule wothandizirayo pogwiritsa ntchito chitsanzo chosavuta INDEX za array.
Tili ndi tebulo lamalipiro. Mzere woyamba ukuonetsa mayina a antchito, chachiwiri chikuwonetsa tsiku lolipira, ndipo chachitatu chikuwonetsa kuchuluka kwa zomwe amapeza. Tiyenera kuwonetsa dzina la wantchito pamzere wachitatu.
- Sankhani foni yomwe makonzedwe akuwonetsedwa. Dinani pachizindikiro "Ikani ntchito", yomwe ili pomwepo kumanzere kwa barula yamu formula.
- Njira yothandizira ikuyenda Ogwira Ntchito. Gulu Malingaliro ndi Kufika chida ichi kapena "Mndandanda wathunthu wa zilembo" kufunafuna dzina INDEX. Mukapeza katswiriyu, sankhani ndikudina batani "Zabwino", yomwe ili pansi pazenera.
- Iwindo laling'ono limatseguka pomwe muyenera kusankha imodzi mwazintchito: Menya kapena Lumikizani. Tikufuna njira Menya. Imapezeka koyambirira ndikuwonetsedwa mwachisawawa. Chifukwa chake, tiyenera kungodina batani "Zabwino".
- Ntchito yotsutsana ndi ntchito imatsegulidwa INDEX. Monga tafotokozera pamwambapa, ali ndi mfundo zitatu, motero, magawo atatu oti mudzaze.
M'munda Menya Muyenera kutchulanso adilesi yamitundu yomwe ikukonzedwa. Itha kuyendetsedwa pamanja. Koma kuti tigwiritse ntchito ntchitoyi, titero. Ikani cholozera m'munda woyenera, ndikuzunguliza lonse lalemba patsamba. Pambuyo pake, adilesi yamtunduwo iwonetsedwa pomwepo m'munda.
M'munda Nambala ya mzere ikani nambala "3", popeza mwa chikhalidwe tikufunika kudziwa dzina lachitatu pamndandanda. M'munda Chiwerengero Cha Column khazikitsani nambala "1", popeza mzati wokhala ndi mayina ndi woyamba pamitundu yosankhidwa.
Mukamaliza makonzedwe onse atamalizidwa, dinani batani "Zabwino".
- Zotsatira zakonzedwe zikuwonetsedwa mu khungu lomwe lasonyezedwa m'ndime yoyamba ya malangizowa. Mwachidziwikire, dzina lodziwika ndi lachitatu pamndandanda womwe uli mumasamba osankhidwa.
Tidasanthula momwe ntchitoyo imagwirira ntchito INDEX mumizere yamaulidi (mizera yambiri ndi mizere). Zikadakhala kuti mbali zonse zinali zofanana, kudzaza chidziwitso pawindo la mkangano kukadakhala kophweka. M'munda Menya mwa njira yomweyo monga pamwambapa, tikuwonetsa adilesi yawo. Potere, manambala ali ndi zofunikira zokhala mgulu limodzi. "Dzinalo". M'munda Nambala ya mzere onetsani phindu "3", popeza muyenera kudziwa zambiri kuchokera mzere wachitatu. Mundawo Chiwerengero Cha Column mwambiri, mutha kuzisiyira zopanda kanthu, popeza tili ndi gawo limodzi momwe mzere umodzi umagwiritsidwa ntchito. Dinani batani "Zabwino".
Zotsatira zake zidzakhala chimodzimodzi monga pamwambapa.
Ichi chinali chitsanzo chosavuta kwambiri kwa inu kuti muwone momwe ntchito iyi imagwirira ntchito, koma machitidwe, mtundu wofananawo wa momwe amagwiritsidwira ntchito sagwiritsidwabe ntchito.
Phunziro: Excel Feature Wizard
Njira 2: gwiritsani ntchito molumikizana ndi woyeserera SEARCH
Pochita, ntchito INDEX Nthawi zambiri amagwiritsidwa ntchito ndi mkangano Sakani. Chakudya INDEX - Sakani Ndi chida champhamvu pogwira ntchito ku Excel, momwe magwiridwe akewo amasinthika kuposa ma analogi apafupi kwambiri - woyendetsa VPR.
Cholinga chachikulu cha ntchitoyo Sakani ndichizindikiro cha kuchuluka kwa manambala mu mtundu womwe wasankhidwa.
Ntchito Syntax Sakani monga:
= SEARCH (search_value, lookup_array, [match_type])
- Chofunika - iyi ndiye mtengo wake womwe mulingo womwe tikuyembekezera;
- Wawonedwa ndi mulingo womwe phindu ili;
- Mtundu Wofananira - Awa ndi njira yosankha yomwe ingasankhe ngati angafufuze mfundo molondola kapena pafupifupi. Tikuyang'ana zenizeni, chifukwa chake malingaliro awa sagwiritsidwa ntchito.
Kugwiritsa ntchito chida ichi mutha kusinthanitsa magwiridwe a mfundo Nambala ya mzere ndi Chiwerengero Cha Column pakugwira ntchito INDEX.
Tiyeni tiwone momwe izi zingachitikire ndi zitsanzo zenizeni. Tikugwira ntchito limodzi ndi tebulo lomweli, lomwe takambirana pamwambapa. Payokha, tili ndi magawo ena awiri - "Dzinalo" ndi "Ndalama". Ndikofunikira kuonetsetsa kuti mukalowetsa dzina la wantchito, kuchuluka kwa ndalama zomwe zimapeza zimawonetsedwa zokha. Tiyeni tiwone momwe izi zingagwiritsidwire ntchito pogwiritsa ntchito ntchito INDEX ndi Sakani.
- Choyamba, timazindikira zomwe amalipira omwe wogwira ntchito a Parfenov D.F. Lowani dzina lake m'munda woyenera.
- Sankhani khungu m'munda "Ndalama"pomwe zotsatira zomaliza ziziwonetsedwa. Yambitsani zenera zotsutsa ntchito INDEX za array.
M'munda Menya timalowa pazolumikizana za mzere momwe malipiro a antchito amakhala.
Mundawo Chiwerengero Cha Column musiyeni opanda kanthu, pamene tikugwiritsa ntchito mawonekedwe amtundu umodzi mwachitsanzo.
Koma m'munda Nambala ya mzere timangofunika kulemba ntchito Sakani. Kuti tilembe, timamatira pamawu omwe takambirana pamwambapa. Nthawi yomweyo lembani dzina la wothandizira mundawo "Sakani" opanda mawu. Kenako tsegulani bulaketi ndikuwonetsa zolumikizira zomwe mukufuna. Izi ndi ziwonetsero za foni momwe tidalembera dzina la wantchito Parfenov. Timayika semicolon ndikuwonetsa magwirizano a magulu omwe akuwonedwera. M'malo mwathu, iyi ndi adilesi ya mzere womwe uli ndi mayina antchito. Pambuyo pake, kutseka bulaketi.
Pambuyo kuti mfundo zonse zalowetsedwa, dinani batani "Zabwino".
- Zotsatira zachuma D. Parfenov atatha kukonza zikuwonetsedwa m'munda "Pindani".
- Tsopano ngati ali m'munda "Dzinalo" tisintha zomwe zili ndi "Parfenov D.F.", mwachitsanzo, "Popova M. D.", ndiye kuti mtengo wamalipiro m'munda udzangosintha "Ndalama".
Njira 3: gwiritsani magome ambiri
Tsopano tiwone momwe ogwiritsira ntchito opareshoni INDEX Mutha kukonza magome angapo. Pachifukwa ichi, mfundo ina idzagwiritsidwa ntchito. "Nambala ya Area".
Tili ndi matebulo atatu. Gome lililonse limawonetsa malipiro aogwira ntchito kwa mwezi umodzi. Ntchito yathu ndikupeza malipiro (gawo lachitatu) la wogwira ntchito wachiwiri (mzere wachiwiri) wa mwezi wachitatu (dera lachitatu).
- Sankhani khungu lomwe zotsatira zake zidzakhale zotuluka komanso m'njira yotseguka Fotokozerani Wizard, koma posankha mtundu wa opareting'i, sankhani komwe mukuwonera. Tifunikira izi chifukwa mtundu uwu umalimbikitsa kusamalira mikangano. "Nambala ya Area".
- Windo la mkangano likutseguka. M'munda Lumikizani tifunika kufotokoza ma adilesi a magulu onse atatu. Kuti muchite izi, ikani cholozera m'munda ndi kusankha mndandanda woyamba ndi batani la mbewa yakumanzere. Kenako ikani semicolon. Izi ndizofunikira kwambiri, chifukwa ngati mukapita posankha mtundu wotsatila, ndiye kuti adilesi yake imangotenga m'malo mwa oyambayo. Chifukwa chake, mutalowa semicolon, sankhani mitundu ina. Kenako timayika semicolon ndikusankha zomaliza. Mawu onse omwe ali m'munda Lumikizani tengani mabatani.
M'munda Nambala ya mzere onetsani nambala "2", popeza tikufuna dzina lachiwiri lomaliza pamndandanda.
M'munda Chiwerengero Cha Column onetsani nambala "3"popeza mzere wa malipiro ndi wachitatu mzere mgome lililonse.
M'munda "Nambala ya Area" ikani nambala "3", popeza tifunikira kupeza zambiri mu tebulo lachitatu, lomwe lili ndi zidziwitso pamalipiro a mwezi wachitatu.
Pambuyo polemba data yonse, dinani batani "Zabwino".
- Pambuyo pake, zotsatira za kuwerengera zimawonetsedwa mu foni yosankhidwa kale. Zimawonetsa kuchuluka kwa malipiro a wogwira ntchito wachiwiri (V. M. Safronov) wa mwezi wachitatu.
Njira 4: kuwerengera kuchuluka kwake
Fomu lotchulira silogwiritsidwa ntchito ngati mawonekedwe apangidwe, koma lingagwiritsidwe ntchito osati kokha pakugwira ntchito ndi magulu angapo, komanso pazosowa zina. Mwachitsanzo, itha kugwiritsidwa ntchito kuwerengera kuchuluka kwake komanso wopangira SUM.
Mukamawonjezera ndalamazo SUM ili ndi syntax yotsatirayi:
= SUM (gulu_dongosolo)
M'malo mwathu, kuchuluka kwa ndalama zomwe ogwira ntchito amagwira ntchito pamwezi kumawerengeredwa pogwiritsa ntchito njira zotsatirazi:
= SUM (C4: C9)
Koma mutha kuyisintha pang'ono pogwiritsa ntchito ntchitoyo INDEX. Kenako idzakhala ndi mawonekedwe otsatirawa:
= SUM (C4: INDEX (C4: C9; 6))
Poterepa, zolumikizira za chiyambi cha mndandanda zimawonetsa khungu lomwe limayambira. Koma pazogwirizira zomwe zikuwonetsa kutha kwa zosanjazo, wothandizirayo amagwiritsidwa ntchito INDEX. Poterepa, mkangano woyamba wa wothandizira INDEX akuwonetsa mndandanda, ndipo wachiwiri - pa foni yotsiriza - wachisanu ndi chimodzi.
Phunziro: Zolemba Zothandiza za Excel
Monga mukuwonera, ntchitoyo INDEX itha kugwiritsidwa ntchito mu Excel kuthetsa mavuto osiyanasiyana. Ngakhale takambirana kutali ndi njira zonse zomwe zingagwiritsidwe ntchito, koma okhawo odziwika. Pali mitundu iwiri ya ntchitoyi: yowunikira komanso yosanja. Itha kugwiritsidwa ntchito bwino kwambiri kuphatikiza ndi ena ogwira ntchito. Ma formula opangidwa motere atha kuthana ndi mavuto ovuta kwambiri.
