Excel mabasa ekushanda nemalink uye arrays

Mumuenzaniso uyu, iwe uchadzidza zvese nezveExcel mabasa ekushanda nemalink uye arrays, akadai VPR, GPR, ZVAKAWANDA AKAFUMURWA, INDEX и SELECTION.

VPR

basa VPR (VLOOKUP) inotarisa kumusoro kukosha muchikamu chekuruboshwe chetafura uye inodzorera kukosha kwesero mumutsara wakatsanangurwa wemutsara mumwe chete.

1. Kupinza basa VPR:

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

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

   Tsanangudzo:

   • basa VPR kutsvaga kukosha ID (104) iri kuruboshwe rworuboshwe rwerenji $E$4:$G$7 uye inodzorera kukosha kubva pamutsara wechitatu wemutsara mumwe chete (sezvo nharo yechitatu yebasa iri 3).
   • Nharo yechina yebasa ndeye KUNYEPA (FALSE) - izvi zvinoreva kuti kana mutambo chaiwo uchawanikwa, kana meseji yekukanganisa icharatidzwa # N / A (#N/A).
2. Dhonza mbeva kuti ukope basa VPR kubva muchitokisi B2 pasi pembiru kuenda kuchitokisi B11.Tsanangudzo: Patinokopa basa VPR pasi, mhedziso link $E$4:$G$7 inoramba isina kuchinjwa, nepo chirevo chehukama A2 shanduko ku A3, A4, A5 zvichingoenda zvakadaro.

GPR

Basa racho rinoshanda nenzira yakafanana. GPR (HLOOKUP):

ZVAKAWANDA AKAFUMURWA

basa ZVAKAWANDA AKAFUMURWA (MATCH) inodzosa chinzvimbo chemutengo wakatsvaga muhuwandu hwakapihwa:

Tsanangudzo:

• izwi Yero inotora nzvimbo yechitatu muhuwandu E4:E7.
• Yechitatu basa nharo ndeyekusarudza. Kana iwe ukaisa kukosha kweiyi nharo 0 (zero), ipapo basa racho rinodzosa chinzvimbo chechinhu chinonyatsoenderana kukosha kwakatsvaga (A2). Kana machisi chaiwo akasawanikwa, basa racho rinodzosa kukanganisa. # N / A (#N/A).

INDEX

basa INDEX (INDEX) inodzosa kukosha kwakapihwa kubva kune maviri-dimensional kana one-dimensional renji.

Tsanangudzo: Zvinoreva 92 iri pamharadzano yemutsetse 3 uye column 2 munharaunda E4:F7.

Tsanangudzo: Zvinoreva 97 iripo 3 nzvimbo munzvimbo E4:E7.

SELECTION

basa SELECTION (SARUDZA) inosarudza kukosha kubva pane rondedzero pane yakapihwa chinzvimbo nhamba.

Tsanangudzo: Shoko Ngarava ari pachinzvimbo 3.