Ichi ndi chinyengo chosavuta koma chosangalatsa chomwe chimakulolani kuti musinthe deta kuchokera kumatebulo ang'onoang'ono osagwiritsa ntchito maselo konse. Chofunikira chake ndikuti mutha "kusoka" mndandanda wazinthu zolowa m'malo mwachindunji mu fomula. Tiyeni tiwone njira zingapo zochitira izi.
Sankhani ntchito
Ngati mukufuna kusintha deta kuchokera kumagulu amtundu umodzi ndi nambala, ndiye kuti mutha kugwiritsa ntchito INDEX ntchito kapena yosavuta komanso yoyenera, pakadali pano, analogue - ntchitoyo. KUSANKHA (SANKHA). Imatulutsa chinthu chotsatira ndi nambala yake ya ordinal. Kotero, mwachitsanzo, ngati tikufuna kusonyeza dzina la tsiku la sabata ndi nambala yake, ndiye kuti tikhoza kugwiritsa ntchito zomangamanga.
Ichi ndi chitsanzo chosavuta choyamba kuti mupeze lingaliro lakuti tebulo loyang'ana likhoza kulumikizidwa mu fomula. Tsopano tiyeni tiwone chitsanzo chovuta kwambiri, koma chokongola.
Mndandanda wa zosinthika mu formula
Tiyerekeze kuti tili ndi mndandanda wa mizinda, kumene ntchito ntchito VPR (VLOOKUP) Makhalidwe amalipiro amtundu wachiwiri wa tebulo lachikasu kumanja amalowetsedwa m'malo:
Chinyengo ndichakuti mutha kusintha zomwe zatchulidwa pamitundu yosiyanasiyana ndi tebulo $E$3:$F$5 mndandanda wa zokhazikika mwachindunji mu chilinganizo, ndi tebulo lamanja sadzafunikanso. Kuti musalowe deta pamanja, mukhoza kupita kwachinyengo pang'ono.
Sankhani selo iliyonse yopanda kanthu. Lowetsani chizindikiro chofanana kuchokera pa kiyibodi ndikusankha mtundu womwe uli ndi tebulo - adilesi yake iyenera kuwonetsedwa mu bar formula:
Sankhani ndi mbewa ulalo E3:F5 mu kapamwamba kapamwamba ndikusindikiza kiyi F9 - ulalowo udzasandulika kukhala wosasintha:
Zimatsalira kukopera zotsatilazo ndikuziyika mu fomula yathu ndi VLOOKUP, ndikuchotsa tebulo lokha ngati losafunika:
Amatchedwa mndandanda wa zokhazikika
Kukulitsa lingaliro la njira yapitayi, mutha kuyesa njira ina - kuchita amatchulidwa mndandanda wa zokhazikika mu RAM, yomwe imagwiritsidwa ntchito mu formula. Kuti muchite izi, dinani pa tabu chilinganizo (Mafomula) batani Woyang'anira Dzina (NameManager)… Kenako dinani batani Pangani, ganizirani ndikuyika dzina (zikhale, mwachitsanzo, Cities) ndi m'munda zosiyanasiyana (Buku) matani mndandanda wa zosinthika zomwe zidakopera mu njira yapitayi:
Press OK ndi kutseka Woyang'anira Dzina. Tsopano dzina lowonjezera litha kugwiritsidwa ntchito mosamala papepala lililonse la bukhuli mwanjira iliyonse - mwachitsanzo, mu ntchito yathu ya VLOOKUP:
Yowoneka bwino, yokongola komanso, mwanjira ina, imateteza kumanja kwa anthu omwe si akatswiri 🙂
- Momwe mungagwiritsire ntchito VLOOKUP kuyang'ana deta kuchokera patebulo limodzi kupita pa lina
- Momwe mungagwiritsire ntchito kusaka ndi VLOOKUP
- Kuwerengera popanda mafomu