I 'ISALATHISO "e-Excel: inkcazelo, isicelo kunye nemizekelo

Kweli nqanaba, Excel ngenxa yodumo yayo liLizwi kunguhata kuphela. It yenza kube lula ukwenza iintlobo ngeentlobo izibalo kwezoqoqosho kunye lwezibalo izixa ezikhulu data. Ukulungiselela le njongo, ulungiselela inani elikhulu iimpawu eyakhelwe-ngaphakathi, kuquka inkxaso. Ezinye zazo bayakwazi ukwenza iintshukumo, kuqukwa iisethi zedatha. "ISALATHISO" function kunye awanyange. Ngo-Excel, isetyenziswa ngokwahlukeneyo yaye "FANISA", nto leyo eza kuxoxwa ngezantsi.

incazelo

"ISALATHISO" function in Excel ubuyisela ixabiso (ixabiso reference on) imixholo yeseli, umqolo elinikiweyo kunye neenombolo ikholam betafile okanye kwebanga ezansi.

ezinesintaksi ilula kwaye ngolu hlobo lulandelayo: ISALATHISO (uluhlu kumqolo №, column №).

Lo msebenzi kwakhona ezisebenza nge kumqolo omnye okanye njengomhalthi owodwa. Kulo mzekelo, emva kokucacisa uluhlu-liyajikeleza elibekwe inombolo enye. Oku kubhekisela kwinani kumgca ukuba uluhlu ikholam, kwaye vice versa.

I 'ISALATHISO "e-Excel ngamanye amaxesha unika ixabiso" #REF! ". Oku kwenzeka kakhulu rhoqo xa iseli imi kuhlangana kumqolo Wathi kwaye Ikholamu ngaphandle ethile.

izibonelo isicelo

Cinga nje iimeko ezimbalwa usebenzisa i "ISALATHISO 'xa befundisa.

Thelekelela ukuba kukho uluhlu lwemihlathi 4 nezintlu-4 (jonga. Table). Ukuba kungena omnye iiseli table ibekwe ngaphandle uluhlu A1: E5 ibinzana "= ISALATHISO (B2: E5, 2, 3) '(ngaphandle izicaphulo) kwaye cinezela" Ngena ", emva koko" begonia "iya kukhutshwa impendulo.

Ukuba ufuna ukwazi zingaphi abafundi Group 2 rated "enganelisiyo" kwibhokisi efanelekileyo ufake ibinzana ilandelayo: ISALATHISO (C2: C5, 1).

I "FANISA AMANANI" ku Excel

Zombini imizekelo inikwe ngentla aziyi kusebenza iiseti data enkulu. Inyaniso kukuba ukusebenzisa i 'ISALATHISO "e-Excel uba kumqolo igalelo kunye Ikholamu hayi itafile ngokwayo kunye uluhlu data. Yinto kakhulu kunzima ukwenza, xa sithetha inani elikhulu lweziqalelo. Ukusombulula ingxaki kunokunceda omnye umsebenzi ekselevskaya.

Cinga ngemeko apho uluhlu iquka line olulodwa.

Uluhlu lwamaxabiso kule B3 mzekelo: B6.

Ukukhetha iseli enye kumqolo, umzekelo D1. Siya kungena kuyo igama iziqhamo, nto leyo kwindawo sifuna ukufumana; kulo mzekelo, i "orenji". Xa iseli (E1), apho sifuna ukuba abhale inombolo umgca ohambelana, faka "= FANISA (i-D1; B3: B6; 0) '(jonga itheyibhile.). Ngenxa yoko, kubonakala inani 3. Eli linani ku ezahlukene B3: B6 kwi ibinzana elithi "-orenji".

Last 0 ithetha ukuba ufuna ukufumana uthelekiso ngqo kunye ixabiso D1.

Indlela yokufumana onke amaxabiso kwisicatshulwa ukwanelisa akukho kwingongoma enye

Ngendlela eboniswe ngasentla, i "FANISA" ubuyisela ixabiso enye kuphela (lokuqala, okt. E. Phezulu). Kodwa kuthekani ukuba uluhlu iphindiwe. Kulo mzekelo, ifomula kunceda uluhlu. lonke uluhlu lwe data kwaye usebenzise udibaniso lwesitshixo «Ctrl + Shift + Ngena» kufuneka yabelwe ukusetyenziswa kwazo. Noko ke, ingqalelo yawo ngumxholo kweli nqaku.

I 'ISALATHISO "kunye" FANISA "e-Excel: Imizekelo

Masithi kufuneka bakhethe uluhlu elikhulu ngokwaneleyo of data kuphela ezithile. Ukuze kube lula, cinga ngemeko inani elincinane lweziqalelo. Umzekelo, ukuba une ingxelo yokusebenza kumaqela amaninzi abafundi kunye ngovandlakanyo. Masithi ufuna inani labafundi Wabonakala kwiseli H2 kuhlolwa njenge "enganelisiyo".

Ukuze wenze oku, kuya kuba kuhle ukuba usebenzise zombini imisebenzi kunye. Ukuze ukwazi ukuba kufuneka ungene e H2, kuqala cinga ibinzana kakhulu elula, enokusetyenziselwa ukulungiselela le njongo. Ngokukodwa, ngaba ixabiso olifunayo ingafumaneka ngokubhalela kule seli "ISALATHISO = (A2: E5-1; 2)." Apha wasebenzisa eyongeziweyo lobeko lwe yemizekelo zangaphambili, xa inani umgca ze kwikholam yabalelwa ngesandla. Noko ke, injongo yethu - zokusebenzisa le nkqubo. Ukuze wenze oku, endaweni ababini, omnye, nto leyo ebonisa kumqolo obunqwenelekayo ikholam kule uluhlu ukurekhoda imisebenzi ehambelana "FANISA" khupha ezi manani. Nceda qaphela ukuba nifuna ukuba ibinzana elithi "oud", ibekwe kwiseli G2 kunye "t. 2 ukusuka H2. Ukongeza, sifuna umdlalo ngqo, oko lokugqibela, yesithathu, le ingxabano zombini zibonisa 0.

Emva koko, kunokuba 1 ISALATHISO ifomula (A2: E5-1; 2) ukuba kubhalwe: FANISA (G2; A2: A5; 0), kwaye endaweni ka-2 - FANISA (H2; A2: E2; 0).

Emva kokuba kufakwe esinayo: ISALATHISO (A2: E5; MATCH (G2; A2: A5; 0), FANISA (H2; A2: E2; 0)). Ngenxa yoko, ngokucindezela "Ngena", siye "10" kwiseli.

Indlela ukwandisa indlela kuluhlu ifunyenwe

Njengoko yaziwa, i "ISALATHISO" e-Excel ukuba "Wasolula" kuluhlu ethile. Kulo mzekelo kuxoxwa ngasentla, oku zonke iiseli 4 H2: J3. Kule uqhagamshelo kuyimfuneko ukufumana phandle indlela yokwenza oko kukuthi, "welula" ilungelo ifomula yaye phantsi ukufumana amaxabiso ezichanekileyo.

Ubunzima ephambili kuxhomekeke kwinto yokuba uluhlu A2: E5 inedilesi isalamane. Ukulungisa oku, umele uvule ibe ngokupheleleyo. Kule uluhlu kubhalwe njengoko $ A $ 2: $ E $ 5. Kunjalo kufuneka kwenziwe kuwo omabini imisebenzi eyakhelwe-ngaphakathi, okt, bakhangele ngathi FANISA ($ G $ 2, $ A $ 2: $ A $ 5, 0) .. Ke FANISA ($ H $ 2, kwaye A $ 2: $ E 2; 0).

Ifom yokugqibela ifomyula: ISALATHISO ($ A $ 2: $ E $ 5, FANISA ($ G $ 2, $ A $ 2: A $ 5, 0), FANISA ($ H $ 2, $ A $ 2: $ E $ 2, 0)).

Ngenxa yoko, siye itafile eboniswe ngezantsi

Ukuze ufumane umphumo ochanekileyo, kufuneka uqiniseke ukuba ixabiso lembhalo libhalwa ngokuchanekileyo, kubandakanywa ukuba ayinayo i-typos kunye neendawo ezongezelelweyo. Ngaphandle koko, inkqubo ayiyi kubaphatha ngokufanayo.

Ngoku uyazi indlela yokusebenzisa "INDEX" umsebenzi kwi-Excel. Imizekelo yokwabelana ngayo kunye ne "MATCH" nayo iyaziwa kuwe, kwaye uya kukwazi ukuyisebenzisa ngokuchanekileyo ukuxazulula iingxaki ezininzi.