Excel imagwira ntchito ndi maulalo ndi masanjidwe

Muchitsanzo ichi, muphunzira zonse za ntchito za Excel zogwirira ntchito ndi maulalo ndi masanjidwe, monga VPR, GPR, ZAMBIRI ZOVUTIKA, INDEX и KUSANKHA.

VPR

ntchito VPR (VLOOKUP) imayang'ana mtengo womwe uli kumanzere kwenikweni kwa tebulo ndikubwezeretsanso mtengo wa selo mumzere womwewo.

1. Kuyika ntchito VPR:

   =ВПР(A2;$E$4:$G$7;3;ЛОЖЬ)

   =VLOOKUP(A2,$E$4:$G$7,3,FALSE)

   Kufotokozera:

   • ntchito VPR kufunafuna mtengo ID (104) ili kumanzere kwenikweni kwa mzerewu $E$4:$G$7 ndikubwezeretsanso mtengo kuchokera pamzere wachitatu wa mzere womwewo (popeza mkangano wachitatu wa ntchitoyi ndi 3).
   • Mtsutso wachinayi wa ntchitoyi ndi KUnama (FALSE) - izi zikutanthauza kuti mwina machesi enieni adzapezeka, kapena uthenga wolakwika udzawonetsedwa # N / A (#N / A).
2. Kokani mbewa kuti mukopere ntchito VPR kuchokera ku cell B2 kutsika ndime kupita ku selo B11.Kufotokozera: Tikamakopera ntchito VPR pansi, mtheradi ulalo $E$4:$G$7 imakhalabe yosasinthika, pomwe chilolezo chogwirizana A2 kusintha kwa A3, A4, A5 ndi zina zotero.

GPR

Ntchitoyi imagwira ntchito mofananamo. GPR (HLOOKUP):

ZAMBIRI ZOVUTIKA

ntchito ZAMBIRI ZOVUTIKA (MATCH) imabweretsanso malo a mtengo womwe wafufuzidwa mumtundu womwe waperekedwa:

Kufotokozera:

• Mawu Yellow ali pa malo achitatu mu osiyanasiyana E4:E7.
• Mtsutso wachitatu wa ntchito ndi wosankha. Ngati mulowetsa mtengo wa mkanganowu 0 (zero), ndiye ntchitoyo ibweza malo a chinthucho chomwe chikufanana ndendende ndi mtengo womwe wafufuzidwa (A2). Ngati chofanana chenicheni sichinapezeke, ntchitoyi ibweretsa cholakwika. # N / A (#N / A).

INDEX

ntchito INDEX (INDEX) imabweretsanso mtengo womwe wapatsidwa kuchokera kumitundu iwiri kapena ya dimensional imodzi.

Kufotokozera: Kutanthauza 92 ili pa mphambano ya mzere 3 ndi column 2 mu osiyanasiyana E4:f7.

Kufotokozera: Kutanthauza 97 ilipo 3 malo m'malo E4:E7.

KUSANKHA

ntchito KUSANKHA (SANKHANI) amasankha mtengo kuchokera pamndandanda womwe wapatsidwa.

Kufotokozera: Mawu bwato ali m'malo 3.