Neman aiki a Microsoft Excel

Daya daga cikin mashahurin masu aiki tsakanin masu amfani da Excel shine aikin Neman. Ayyukanta sun haɗa da ƙayyadadden matsayi na ɗimbin yawa a cikin tsarin da aka bayar Yana kawo fa'idodi mafi girma yayin amfani dashi a hade tare da sauran masu gudanar da aiki. Bari mu ga abin da ya ƙunshi aiki. Neman, da yadda za'a iya amfani dashi a aikace.

Aikace-aikacen mai bincike na SEARCH

Mai aiki Neman ɓangare na ayyuka Tunani da Arrays. Yana bincika abubuwan da aka ƙayyade a cikin tsararren ƙayyadaddun abubuwa da maganganu a cikin sel daban da adadin matsayin sa a wannan kewayon A zahiri, har ma da sunansa yana nuna wannan. Hakanan, wannan aikin, lokacin amfani dashi tare da wasu masu gudanar da ayyukan, yana gaya musu matsayin matsayin wani sashi na musamman don aiki na wannan bayanan mai zuwa.

Syntax mai aiki Neman ya yi kama da wannan:

= SEARCH (bincike_value; lookup_array; [wasa_type])

Yanzu bincika kowane ɗayan waɗannan mahaɗan huɗan daban.

"Neman darajar" - Wannan shine kashin da yakamata a samo. Zai iya samun rubutu, lamba, kuma ya dauki darajar ma'ana. Bayani game da tantanin halitta wanda ya ƙunshi kowane ɗayan dabi'un da aka ambata a sama zai iya zama wannan mahawara.

Ganin Array Adireshin kewayon ne yake ciki inda binciken nema yake. Matsayin wannan sigar a cikin wannan tsararren da dole ne ma'aikacin ya ƙayyade Neman.

Nau'in Match yana nuna daidai wasan don neman ko ba daidai ba. Wannan gardamar tana da ma'anoni guda uku: "1", "0" da "-1". A darajar "0" mai aiki kawai yana bincika wani daidai wasa. Idan an kayyade daraja "1", sannan in babu cikakken wasa Neman dawo da kashi kusa da ita a saukowa tsari. Idan an kayyade daraja "-1", sannan idan ba a sami daidaitaccen wasa ba, aikin ya dawo da mafi kusancinsa ta hanyar haurawa. Yana da mahimmanci idan kana bincika ba don ƙimar daidai ba, amma don ƙimar kimar domin ɗaukar da kake gani ana tsara shi ta hanyar haɓakawa (nau'in daidaitawa) "1") ko saukowa (nau'in wasa "-1").

Hujja Nau'in Match ba a buƙata. Ana iya watsi da shi idan ba a buƙatarsa. A wannan yanayin, madaidaicin darajar shi ne "1". Aiwatar da hujja Nau'in MatchDa farko dai, yana da ma'ana ne kawai lokacin da aka aiwatar da lambobi, ba rubutu ba.

A yanayin Neman a saitunan da aka ƙayyade ba su iya samun abun da ake so ba, mai aiki yana nuna kuskure a cikin tantanin halitta "# N / A".

Lokacin gudanar da bincike, mai gudanar da aikin bai bambanta tsakanin rajista na shari'ar ba. Idan akwai wasu daidaitattun ashana a cikin shirin, to Neman yana nuna matsayin farkon farkon su a cikin tantanin halitta.

Hanyar 1: nuna wurin abu a cikin adadin rubutu

Bari muyi la’akari da misalin shari’ar mafi sauki yayin amfani Neman Zaka iya sanin wurin da aka ayyana a cikin tsarin bayanan rubutu. Mun gano matsayin da kalmar ta ƙunsa a cikin kewayon inda sunan samfurin yake Sukari.

1. Zaɓi tantanin da za a nuna sakamakon sarrafa shi. Danna alamar "Saka aikin" kusa da layin tsari.



2. Farawa Wizards na Aiki. Bude sashen "Cikakken jerin haruffa" ko Tunani da Arrays. A cikin jerin masu aiki muna neman suna "Neman". Bayan samo shi da nuna alamarsa, danna maballin "Ok" a kasan taga.



3. Fuskar mai aiki da Tushe tana aiki Neman. Kamar yadda kake gani, a wannan taga, ta hanyar yawan muhawara, akwai filaye uku. Dole ne mu cika su.

   Tunda muna buƙatar neman matsayin kalmar Sukari a cikin kewayon, sannan fitar da wannan suna zuwa filin "Neman darajar".

   A fagen Ganin Array kuna buƙatar bayyana abubuwan tsarawa na kewayon kanta. Kuna iya fitar da shi da hannu, amma ya fi sauƙi don saita siginan kwamfuta a cikin zaɓi kuma zaɓi wannan tsararren akan takardar, yayin riƙe maɓallin linzamin kwamfuta na hagu. Bayan haka, adireshinsa zai bayyana a taga muhawara.

   A cikin na uku filin Nau'in Match sanya lamba "0", tunda zamuyi aiki tare da bayanan rubutu, sabili da haka muna buƙatar cikakken sakamako.

   Bayan an shigar da dukkan bayanan, danna kan maɓallin "Ok".



4. Shirin yana aiwatar da lissafin kuma yana nuna lambar serial matsayi Sukari a cikin zaɓar da aka zaɓa a cikin tantanin da muka ƙayyade a matakin farko na wannan umarnin. Matsayin lamba zai kasance daidai "4".

Darasi: Mayan Maɗaukaki

Hanyar 2: aiki da kai na AIKI na SEARCH

A sama, mun bincika mafi mahimmancin yanayin amfani da mai amfani Nemanamma har ana iya sarrafa kansa.

1. Don dacewa, ƙara ƙarin ƙarin filayen guda biyu akan takardar: Matsayi da "Lambar". A fagen Matsayi tuƙi da sunan da kuke buƙatar samu. Yanzu bari ya kasance Nama. A fagen "Lambar" saita siginan siginan kuma je zuwa taga mai muhawara ta hanyar da aka tattauna a sama.



2. A cikin taga muhawara na aiki, a fagen "Neman darajar" nuna adireshin tantanin halitta da aka rubuta kalmar Nama. A cikin filayen Ganin Array da Nau'in Match saka daidai bayanai kamar yadda suke a cikin hanyar da ta gabata - address address da lamba "0" daidai da. Bayan haka, danna maɓallin "Ok".



3. Bayan mun aiwatar da ayyuka na sama, a fagen "Lambar" matsayin kalma za a nuna Nama a cikin kewayon da aka zaɓa. A wannan yanayin, daidai yake da "3".



4. Wannan hanyar tana da kyau a cikin cewa idan muna son gano matsayin kowane suna, ba za mu buƙatar sake maimaita ko canza dabara ba kowane lokaci. Sauƙi a cikin filin Matsayi shigar da sabon kalmar bincike maimakon wanda ya gabata. Gudanar da fitarwa sakamakon wannan zai faru ta atomatik.

Hanyar 3: yi amfani da FIND mai aiki don maganganun lambobi

Yanzu bari mu kalli yadda zaka iya amfani Neman don aiki tare da maganganun lambobi.

Aikin shine neman kaya a cikin adadin tallace-tallace na 400 rubles ko mafi kusa ga wannan adadin a cikin hauhawar tsari.

1. Da farko, muna buƙatar rarrabe abubuwan a cikin shafi "Adadin" cikin saukowa domin tsari. Zaɓi wannan shafi kuma je zuwa shafin "Gida". Danna alamar Dadi da kuma Matatarwalocated a kan tef a cikin toshe "Gyara". Cikin jeri wanda ya bayyana, zaɓi "A ware daga matsakaici zuwa ƙarami".



2. Bayan an gama tantancewa, zaɓi tantanin da za a nuna sakamakon, sai ka fara taga gardamar kamar yadda aka tattauna a farkon hanyar.

   A fagen "Neman darajar" fitar da lamba "400". A fagen Ganin Array saka aikin daidaita shafin "Adadin". A fagen Nau'in Match saita darajar "-1", tunda muna neman daidaita ɗaya ko mafi girma daga binciken. Bayan kammala dukkan saitunan, danna maballin "Ok".



3. Sakamakon sarrafawa an nuna shi a cikin tantanin halitta da aka ayyana. Wannan shine matsayin. "3". Daidai da ita "Dankali". Tabbas, adadin kudaden shiga daga siyarwar wannan samfurin shine mafi kusanci ga lamba 400 a hawan tsari kuma yakai 450 rubles.

Hakanan, zaku iya bincika mafi kusancin matsayi zuwa "400" cikin saukowa domin tsari. Kawai don wannan kuna buƙatar tace bayanai ta hanyar hawa zuwa sama, da kuma a fagen Nau'in Match aikin muhawara yana saita darajar "1".

Darasi: A ware da kuma tace bayanai a cikin Excel

Hanyar 4: amfani dashi a hade tare da sauran masu gudanar da aiki

Zai fi dacewa amfani da wannan aikin tare da sauran masu aiki a zaman wani ɓangaren tsari mai rikitarwa. Mafi yawancin lokuta ana amfani dashi a tare tare da aiki INDEX. Wannan gardamar tana nuna abubuwan da ke cikin kewayon da aka tsara ta sashin layirta ko lambar lamba a cikin tantanin da aka sanya. Bugu da ƙari, lambobi, kamar yadda game da ma'aikaci Neman, ana yin dangi dangane da duk takarda, amma a cikin kewayon ne kawai. Ginin kalma na wannan aikin kamar haka:

= INDEX (tsararru; jere_number; shafi_number)

Haka kuma, idan tsararren tsari daya ne, to zaka iya amfani da daya daga cikin muhawara guda biyu: Lambar layi ko Lambar Harafi.

Featararren hanyar Haɗawa INDEX da Neman ya ta'allaka ne da cewa za a iya amfani da karshen a matsayin hujja ta farkon, wato, nuna matsayin jere ko shafi.

Bari mu bincika yadda za a iya yin wannan a aikace ta amfani da tebur gaba ɗaya. Aikinmu shine a nuna a cikin ƙarin filin takarda "Samfura" sunan samfurin, jimlar kudaden shiga daga abin da yake shine rubles 350 ko mafi kusa ga wannan darajar a cikin saukowa. An bayyana wannan magana a fagen. "Kimanin adadin kudaden shiga kowace takardar".

1. A ware abubuwa a cikin shafi Adadin Haraji " hawa sama. Don yin wannan, zaɓi ɓangaren da ake buƙata kuma, kasancewa a cikin shafin "Gida"danna alamar Dadi da kuma Matatarwa, sannan cikin menu wanda ya bayyana, danna abun Tace daga matsakaici zuwa matsakaici ".



2. Zaɓi sel a cikin filin "Samfura" kuma kira Mayan fasalin a cikin hanyar da aka saba ta hanyar maɓallin "Saka aikin".



3. A cikin taga yana buɗewa Wizards na Aiki a cikin rukuni Tunani da Arrays neman suna INDEX, zaɓi shi kuma danna maballin "Ok".



4. Bayan haka, taga yana buɗe wanda ya ba da zaɓin zaɓuɓɓukan aikin mai aiki INDEX: don array ko don tunani. Muna buƙatar zaɓi na farko. Sabili da haka, muna barin a wannan taga duk saitunan tsoho kuma danna maballin "Ok".



5. Farashin muhawara na aiki zai bude INDEX. A fagen Shirya saka adireshin kewayon inda mai aiki INDEX zai bincika sunan samfurin. A cikin lamarinmu, wannan shafi ne "Sunan samfurin".

   A fagen Lambar layi da nested aiki za a located Neman. Dole ne a tura shi da hannu ta amfani da syntax ɗin da aka ambata a farkon labarin. Nan da nan rikodin sunan aikin - "Neman" ba tare da ambato ba. Sannan bude murfin. Hujja ta farko ga wannan ma'aikaci ita ce "Neman darajar". An samo shi a kan takardar a cikin filin "Kimanin adadin kudaden shiga". Sanya abubuwan daidaitawa na kwayar da ke dauke da lambarta 350. Mun sanya wani wasan kwaikwayo. Hujja ta biyu ita ce Ganin Array. Neman za su duba cikin kewayon da adadin kudaden shiga ya ke ciki kuma nemi wanda ke kusa da 350 rubles. Sabili da haka, a wannan yanayin, saka ma'anar ayyukan shafi Adadin Haraji ". Bugu da sake mun sanya wani ɗan kwando. Hujja ta uku ita ce Nau'in Match. Tun da zamu bincika lamba daidai da wanda aka bayar ko mafi ƙanƙantar wanda yake, muna sanya lambar anan "1". Muna rufe baka.

   Hujja ta uku ga aikin INDEX Lambar Harafi bar shi babu komai. Bayan haka, danna maɓallin "Ok".



6. Kamar yadda kake gani, aikin INDEX ta amfani da afareta Neman a cikin tantanin halitta da aka riga aka bayyana sunan Shayi. Tabbas, adadin daga siyar da shayi (300 rubles) ya fi kusa da saukowa don adadin 350 rubles daga duk ƙimar da ke cikin teburin da ake sarrafawa.



7. Idan muka canza lamba a fagen "Kimanin adadin kudaden shiga" ga wani, sannan za a sake karanta abubuwan da ke cikin ta atomatik gwargwadon yadda suka dace "Samfura".

Darasi: INDEX aiki a cikin Excel

Kamar yadda kake gani, mai aiki Neman ne mai sauƙin aiki don ƙayyade jerin jerin abubuwan da aka ƙayyade a cikin tsararren bayanai. Amma amfaninsa yana ƙaruwa sosai idan an yi amfani da shi a cakudaddun tsari.