Yadda za a rubuta dabara a Excel? Horo. Mafi yawan dabarun da ake buƙata

Barka da rana

Sau ɗaya a cikin lokaci, rubabbun tsari don kanku a cikin Excel wani abu ne mai ban mamaki a gare ni. Kuma dukda cewa yawanci nayi aiki a wannan shirin, ban cika komai ba sai rubutun ...

Kamar yadda ya juya, yawancin dabarun ba komai bane masu rikitarwa kuma zaka iya aiki tare dasu, koda don mai amfani da komputa mai amfani da novice. A cikin labarin, kawai, Ina so in bayyana mafi mahimmancin dabaru, wanda yawancin lokuta dole ne in yi aiki ...

Don haka, bari mu fara ...

1. Ayyuka na asali da kayan yau da kullun. Koya kayan yau da kullun na Excel.

Dukkanin ayyuka a cikin labarin za a nuna su a cikin fasalin Excel na 2007.

Bayan fara shirin Excel - taga yana bayyana tare da sel da yawa - teburinmu. Babban fasalin shirin shine yana iya karanta (azaman mai ƙididdigewa) dabarar ku waɗanda kuka rubuta. Af, zaka iya ƙara dabara zuwa kowane tantanin halitta!

Tsarin tsari dole ne ya fara da alamar "=". Wannan fa'ida ce wacce ake bukata. Bayan haka kuna rubuta abin da kuke buƙatar lissafa: alal misali, "= 2 + 3" (ba tare da ambato ba) kuma danna maɓallin Shigar - sakamakon, zaku ga cewa sakamakon "5" ya bayyana a cikin tantanin halitta. Duba hotunan allo a kasa.

Mahimmanci! Duk da cewa an rubuta lambar "5" a cikin tantanin A1, an ƙididdige shi ta hanyar dabara ("= 2 + 3"). Idan a cikin sel na gaba kawai sai a rubuta “5” a cikin rubutu - sannan kuma lokacin da kake tafe akan wannan kwayar a cikin editar dabara (layin da ke sama, Fx) - zaku ga Firayim lamba "5".

Yanzu yi tunanin cewa a cikin tantanin halitta zaka iya rubuta darajar 2 + 3 kawai, amma lambobin sel waɗanda ƙididdigar ku kuke buƙatar ƙarawa. Bari mu ce "= B2 + C2".

A zahiri, dole ne akwai wasu lambobi a B2 da C2, in ba haka ba Excel zai nuna mana a cikin sel A1 sakamakon shine 0.

Kuma mafi mahimmancin batun ...

Lokacin da kayi kwafin sel wanda akwai tsari, misali A1 - kuma liƙa shi a cikin wata tantanin halitta - ba darajar "5" bane da aka kwafa, amma dabara kanta!

Haka kuma, dabara zai canza daidai gwargwado: i.e. idan an kwafa A1 zuwa A2, to, samfurin a cikin sel A2 zai zama "= B3 + C3". Excel yana canza tsarin da kanka ta atomatik: idan A1 = B2 + C2, to yana da ma'ana cewa A2 = B3 + C3 (duk lambobin sun haɓaka da 1).

Sakamakon, ta hanyar, yana cikin A2 = 0, saboda Kwayoyin B3 da C3 ba a tantance su ba, saboda haka suna daidai 0.

Sabili da haka, zaka iya rubuta dabara sau ɗaya, sannan ka kwafa a cikin dukkanin sel na abin da ake buƙata - kuma Excel zai ƙididdige a kowane layi na tebur naka!

Idan baku son B2 da C2 su canza yayin kwafa kuma koyaushe za'a haɗe su akan waɗannan ƙwayoyin, to sai kawai ƙara "$" alamar a wurinsu. Misali a kasa.

Wannan hanyar, duk inda kuka kwafa kwayar A1, koyaushe zai koma ga sel waɗanda aka haɗa.

2. ofarin dabi'u a cikin layuka (Tsarin SUMM da SUMMESLIMN)

Tabbas, zaku iya ƙara kowace ƙwayar ta hanyar samar da dabara A1 + A2 + A3, da sauransu. Amma don kada ku sha wahala, akwai wani tsari na musamman a cikin Excel wanda ke haɓaka duk dabi'u a cikin ƙwayoyin da kuka zaɓi!

Aauki misali mai sauƙi. Akwai nau'ikan kayayyaki da yawa a cikin kaya, kuma mun san nawa kowane samfuran keɓaɓɓu a cikin kg. yana cikin hannun jari Bari muyi kokarin yin lissafi, amma nawa ne a cikin kilogiram. kaya a cikin jari.

Don yin wannan, je zuwa tantanin da za'a nuna sakamakon sa sai a rubuta fom ɗin: "= SUM (C2: C5)". Duba hotunan allo a kasa.

Sakamakon haka, duk sel a cikin zaɓin da aka zaɓa za a taƙaita su, kuma zaku ga sakamakon.

2.1. Additionarin ƙari ga yanayin (tare da yanayi)

Yanzu tunanin cewa muna da wasu yanayi, i.e. ƙara sama ba duk ƙimar da ke cikin sel ba (Kg, a cikin hannun jari), amma kawai takamaiman ne, ka faɗi, tare da farashin (1 kg.) ƙasa da 100.

Akwai tsari mai kyau na wannan. "SUMMESLIMN". Nan da nan misali, sannan bayanin kowane alama a cikin dabara.

= SAURARA (C2: C5; B2: B5; "<100")ina:

C2: C5 - wancan shafin (waɗancan ƙwayoyin) waɗanda za'a haɗe su;

B2: B5 - kwalin da za'a bincika yanayin (i.e. farashin, misali, ƙasa da 100);

"<100" - yanayin da kanta, lura cewa yanayin an rubuta shi a alamomin zance.

Babu wani abu mai rikitarwa a cikin wannan tsari, babban abinda shine a kiyaye daidaito: C2: C5; B2: B5 - dama; C2: C6; B2: B5 - ba daidai ba. I.e. kewayon tattarawa da kewayon yanayi dole ne su zama daidai, in ba haka ba dabara zai dawo da kuskure.

Mahimmanci! Za'a iya samun yanayi da yawa na jimlar, i.e. Kuna iya dubawa ba ta hanyar 1 ba, amma ta 10 nan da nan, saita yanayi mai yawa.

3. Kidaya yawan layuka wadanda suka gamsar da sharuɗan (thea'idar shine KYAUTATAWA)

Neman wani babban aiki gama gari: don ƙididdige yawan kuɗin da aka samu a sel, amma yawan irin waɗannan ƙwayoyin da ke gamsar da wasu halaye. Wani lokaci, akwai yanayi mai yawa.

Sabili da haka ... bari mu fara.

A wannan misalin, bari mu gwada yin lissafin adadin abubuwan da farashi ya ninka 90 (idan ka duba, zaku iya cewa akwai samfuran 2 guda biyu: tangerines da lemu).

Don ƙididdige kaya a cikin tantanin da ake so, mun rubuta wannan dabarar (duba sama):

= LATSA (B2: B5; "> 90")ina:

B2: B5 - kewayon da za a bincika su, gwargwadon yanayin da mu ka sanya;

">90" - yanayin da kansa an rufe shi a alamomin zance.

Yanzu bari muyi kokarin rikitar da misalinmu dan kadan, kuma ƙara lissafi gwargwadon ƙarin yanayi: tare da farashin fiye da 90 + adadin a cikin shagon yana ƙasa da kilogiram 20.

Dabara yana ɗaukar fom:

= LABARI (B2: B6; "> 90"; C2: C6; "<20")

Anan ne komai ya kasance iri ɗaya, sai dai don ƙarin yanayi guda (C2: C6; "<20") Af, ana iya samun irin wannan yanayi mai yawa!

A bayyane yake cewa babu wanda zai rubuta irin wannan dabarar don karamin karamin tebur, amma don tebur da dama layuka ɗari, wannan lamari ne daban. Misali, wannan tebur ya fi na gani gani.

4. Bincika da musanya dabi'u daga wannan tebur zuwa wani (Tsarin VLOOKUP)

Ka yi tunanin cewa sabon tebur ya zo garemu, tare da sabon alamun farashin don samfurin. Da kyau, idan abubuwan sun kasance 10-20, zaka iya sake saita su gaba ɗaya. Kuma idan akwai daruruwan irin waɗannan abubuwan? Yana da sauri sosai idan an sami Excel cikin daidaitattun sunaye masu dacewa daga tebur zuwa wani, sannan a kwafa sabon alamar farashin zuwa teburin tsohuwarmu.

Don irin wannan aiki, ana amfani da dabara VPR. A wani lokaci, ya kasance mai “hikima” tare da dabaru masu ma'ana “IF” har sai da ya hadu da wannan abin banmamaki!

Don haka, bari mu fara ...

Ga misalinmu + sabon tebur tare da alamun farashin. Yanzu muna buƙatar canza sabon alamun farashin ta atomatik daga sabon tebur zuwa tsohuwar (sabon alamun farashin yana ja).

Sanya siginan kwamfuta a cikin sel B2 - i.e. a cikin tantanin farko, inda muke buƙatar canza alamar farashin ta atomatik. Bayan haka, muna rubuta fola, kamar yadda yake a cikin sikirin fuska a kasa (bayan hotunan allon za a sami cikakken bayani game da shi).

= VLOOKUP (A2; $ D $ 2: $ E $ 5; 2)ina

A2 - tamanin da zamu nema domin daukar sabon farashi mai daraja. A cikin yanayinmu, muna neman kalmar "apples" a cikin sabon tebur.

$ D $ 2: $ E $ 5 - gabaɗa zaɓi sabon teburinmu (D2: E5, zaɓin yana daga kusurwar hagu ta sama zuwa ƙasan madaidaiciyar dama), i.e. inda bincike zai gudana. Alamar "$" a cikin wannan tsari wajibi ne don lokacin da ka kwafa wannan dabara ga wasu ƙwayoyin - D2: E5 kada ku canza!

Mahimmanci! Binciken kalmar "apples" za a aiwatar ne kawai a farkon shafi na teburin da aka zaɓa, a wannan misalin, "apples" za'a bincika a shafi na D.

2 - Lokacin da aka samo kalmar "apples", aikin dole ne ya san daga wane shafi na teburin da aka zaɓa (D2: E5) don kwafa ƙimar da ake so. A cikin misalinmu, kwafi daga shafi na 2 (E), saboda a farkon shafi (D) mun bincika. Idan teburin da kuka zaɓa don binciken zai ƙunshi ginshiƙai 10, sannan shafi na farko zai bincika, kuma daga shafi 2 zuwa 10 - za ku iya zaɓan lambar don kwafa.

Zuwa dabara = VLOOKUP (A2; $ D $ 2: $ E $ 5; 2) wanda aka maye gurbin sabon dabi'u don wasu samfuran samfuran - kawai kwafa shi zuwa wasu ƙwayoyin a cikin shafi tare da alamar farashin samfurin (a cikin misalinmu, kwafi zuwa sel B3: B5). Dabarar zata bincika ta atomatik kuma kwafin ƙimar daga shafi na sabon teburin da kuke buƙata.

5. Kammalawa

A wannan labarin, mun bincika mahimmancin aiki tare da Excel, yadda za a fara dabarun rubutu. Sun ba da misalai na tsararren tsari waɗanda yawancin mutane waɗanda ke aiki a Excel yawanci dole ne su yi aiki tare.

Ina fatan cewa misalan da suka watse zasu kasance masu amfani ga mutum kuma zai taimaka wajen hanzarta ayyukansa. Yi kyakkyawan gwaji!

PS

Kuma waɗanne dabaru kuke amfani da shi? Ko zai yiwu a sauƙaƙe dabarun da aka bayar a cikin labarin? Misali, akan kwamfutoci masu rauni, lokacin da wasu dabi'u suka canza a cikin manyan tebura inda ake yin lissafin ta atomatik, kwamfutar zata daskare har tsawon dakika biyu, tara da nuna sabbin sakamako ...