Yadda za a lissafta adadin dabi'u a cikin Excel shafi. Hanyoyi 6 don ƙididdige yawan dabi'u a cikin shafi na Ekstel

Sau da yawa, a gaban masu amfani da editan teburin, tambayar ta taso cewa wannan ya shafi kirga yawan alamu a cikin shafi. A takaice dai, ya zama dole a lissafta adadin filayen a cikin shafi cike da takamaiman rubutu ko bayanan da yawa. Editan tebur yana da bambance-bambance da yawa waɗanda zasu ba ku damar yin wannan fasalin.

Tsarin ƙididdigar a cikin shafi

Ya danganta da dalilai na mai amfani a cikin editan Tabute, zaku iya aiwatar da kirgawa duk bayanai a cikin shafi. Za mu bincika hanyar kowace hanya cikin ƙarin bayani.

Hanyar 1: Nuna yawan dabi'u a cikin sandar hali

Wannan hanyar shine mafi sauƙin amfani. Yana amfani da ƙididdigar lambobi da rubutu, amma ba za a iya amfani dashi lokacin aiki tare da yanayin ba. Janar Action Algorithm:

1. Zaɓi yankin bayanan da ake buƙata.
2. Muna kallon layin jihar.
3. Muna koyon adadin dabi'u.

ɗaya

Hanyar tana dacewa don amfani kawai lokacin aiki tare da matsakaicin bayanan kundin, in ba haka ba kuna buƙatar amfani da wasu zaɓuɓɓuka. Wani debe na hanyar - ana iya ganin sakamakon kawai a lokacin ware, don haka kuna buƙatar cire ko tsinkaye ko gyara a wani wuri. Mataki-mataki jagora don haɗa "lambar" mai nuna:

1. Danna PCM akan sandar hali.
2. Allon yana nuna jerin. A nan ya wajaba don bincika ko alamar game da sigogi "ƙimar". Idan ba haka ba ne, to, dole ne a saka ta ta danna maɓallin linzamin kwamfuta na hagu.

2.

1. Shirya! Yanzu lambar "lambar" tana cikin sandar sandar tebur.

Hanyar 2: Aikace-aikacen Aikin RC

Wannan mai aiki yana aiwatar da abu ɗaya a matsayin hanyar da ta gabata, amma a lokaci guda yana gyara sakamakon. Sauran flaws na ci gaba, tunda aikin daftari ba zai iya aiki da yanayin ba.

Akwai nau'ikan masu-manufa guda biyu:

1. = Mashaya (tantanin halitta; tantanin halitta;
2. = Blossom (Sel1: Cell).

A cikin tsari na 1, mai aiki yana lissafin kowane zaɓaɓɓen tantanin halitta. A cikin tsari na 2, yawan filayen cike da yawa a cikin kewayon daga lambar wayar 1 zuwa tantanin halitta N. Mataki ta hanyar jagora:

1. Muna samar da zaɓi na tantanin halitta wanda aka ƙidaya kirga.
2. Motsi a cikin subsate "dabarun". Danna "Saka aiki."

3.

1. Madadin Zabi - latsa Aikin "Saka kayan" gunkin "Icon, wanda yake kusa da layi don tsarin tsarin.

huɗu

1. Saka "Saka Ayyuka" ya bayyana akan allon. Bude jerin sunayen kusa da "Kashe". A cikin jerin da aka katse jerin, zaɓi maɓallin "ƙididdigar ƙididdiga. A cikin "Zaɓi Aiki:" Filin, muna samun afareton daftari kuma danna shi ta lkm. Bayan duk magudi, danna kan "ok".

biyar

1. Nunin nuni da taga don rubuta muhawara ta after. Anan wajibi ne ta hanyar canja wurin sel ko shigarwar da shigarwar don saita muhawara. Ya dace a lura cewa ana saita kewayon tare da taken. Bayan duk magidano, danna kan "ok" Election, located a kasan taga.

6.

1. Shirya! A sakamakon haka, mun karbi sakamakon ƙidaya a cikin sel da aka ƙaddara. Hakan ya shafi asusun gaba daya kowane sel mai cike da ruwa.

7 Hanyar 3: Yin Amfani da Asusun

Asusun yana da kyau don aiki tare da alamun lambobi. Akwai nau'ikan masu-manufa guda biyu:

1. = Account (Cell1; Cell2; Cell).
2. = Asusun (tantanin halitta).

Jagorar Mataki na mataki-mataki:

1. Muna samar da zaɓi na tantanin halitta wanda aka ƙidaya kirga.
2. Motsi a cikin subsate "dabarun". Danna kan "saka aiki" abu. Allon yana nuna karamin taga da ake kira "Aikin Saka". Bayyana jerin da ke tare da rubutun "Kateory". A cikin jerin da aka katse jerin, zaɓi maɓallin "ƙididdigar ƙididdiga. A cikin "Zaɓi Aiki:" Filin, muna samun afaretocin asusun kuma danna kan shi tare da lkm. Bayan duk magudi, danna kan "ok".

takwas

1. Tufafin muhawara ta cika daidaitawar sel na wajibi. Bayan duk magidano, danna kan "ok" Election, located a kasan taga.

tara

1. Shirya! A sakamakon haka, mun karbi sakamakon ƙidaya a cikin sel da aka ƙaddara. Anan ana yin la'akari da sel cike da fanko, ban da fanko da waɗanda ke cikin bayanan rubutu.

Hanyar 10 4: Mai aiki 8

Wannan ma'aikaci yana ba ku damar lissafin adadin ƙimar don takamaiman yanayi. Janar na mai aiki: = 8 (Range; Haɗin kai).

1. Range - Wannan yana nuna yankin da ya zama dole don lissafa yawan daidaituwa akan takamaiman yanayin.
2. Shari'a shine yanayin da za'a rarrabe su.

Za mu bincika komai akan takamaiman misali. Jagorar Mataki na mataki-mataki:

1. Misali, zamu tantance adadin kalmar "Gudun" a cikin shafi tare da wasanni. Motsawa a cikin filin da zaku nuna sakamakon.
2. Motsi a cikin subsate "dabarun". Danna kan "saka aiki" abu. Allon yana nuna karamin taga da ake kira "Aikin Saka". Bayyana jerin da ke kusa da rubutun "rukuni na" ". A cikin jerin da aka katse jerin, zaɓi maɓallin "ƙididdigar ƙididdiga. A cikin "Zaɓi Aiki:" Filin, mun sami afareton kuma danna shi da lkm. Bayan duk magudi, danna kan "ok".

goma sha ɗaya

1. Allon ya bayyana da "ayyukan muhawara". A cikin layin kewayon, muna gabatar da daidaitawar ƙwayoyin halitta waɗanda ke halartar kirgawa. A cikin layi "sharuddan" Muna tambayar yanayin da kanta. Fitar da nan: "Run". Bayan aiwatar da duk duk magudi, danna kan "ok".

12

1. Mai aiki da aka lissafta kuma ya nuna yawan daidaituwa a filin da aka nuna tare da kalmar "gudu". Mun samu haduwa goma sha shida.

13

Bari mu shirya gyara yanayin don ya fi fahimtar aikin mai aiki. Jagorar Mataki na mataki-mataki:

1. Bari muyi kirga wasu dabi'u a cikin wannan shafi, ban da darajar "gudu".
2. Muna maimaita matsayi na biyu daga koyarwar da ke sama.
3. A cikin layin kewayon, muna shiga iri iri iri kamar yadda yake a cikin misalin da ke sama. A cikin layin "sharhi" muna fitar da alamar rashin daidaituwa "" kafin "Run". Bayan duk magidano, danna "Ok".

goma sha huɗu

1. A sakamakon haka, mun sami adadin da goma sha bakwai - yawan kalmomin a cikin shafi da aka zaɓa ba tare da la'akari da kalmar "Gudun" ba.

A ƙarshen la'akari da hanyar, mun gano yadda wakilin yake aiki tare da yanayin da haruffa ">" da "

1. Yin amfani da umarnin da ke sama, shigar da mai aiki don bincika sel mai mahimmanci.
2. A cikin layin kewayon, mun shigar da daidaitawa masu mahimmanci na sel na mai magana. A cikin layin "sharuddan", shigar da darajar "> 350". Bayan duk magidano, danna "Ok".

goma sha biyar

1. A sakamakon haka, mun sami darajar goma - yawan lambobi sun fi ƙarfe 350 a cikin shafi da aka zaɓa.

Misalai 16 na amfani da aikin aikin a Excel

Za mu bincika wani misali don ƙarin cikakken fahimtar aikin aikin. Misali, muna da alamun da ke gaba tare da bayanan tallace-tallace:

17.

Dalili: Gano nawa kayan da aka sayar samfurori daga Samsung. Muna amfani da wannan tsari mai zuwa: = C3: C17; "Samsung" / A17. A sakamakon haka, muna samun sakamako na 40%:

18 fasali na amfani da aikin aikin a fice

Yi magana game da wasu fasali na mai aiki:

• Rajistar alamu ba ta da mahimmanci idan an saita layin nau'in rubutu na rubutu a matsayin ma'auni;
• Sakamakon ƙidaya zai zama sifili idan aka gabatar da tunani game da sel mara amfani ko kuma babu komai a cikin alaka;
• Za'a iya amfani da sabis a matsayin dabara na tsararru, wanda kamar adadin sel tare da bayani da yawa ga yanayin zai faru.

Hanyar 5: Yin amfani da mai aiki shine 8 mai aiki

Mai aiki yana da daidai gwargwadon aikin da aka ambata a sama. Ya bambanta a cikin cewa yana ba ku damar ƙara wasu adadin jeri tare da yanayi daban-daban ko iri ɗaya. Misali, muna buƙatar yin lissafin adadin samfuran da aka sayar a adadin fiye da ɗari uku. Bugu da ƙari, ya zama dole a lissafta cewa farashin abin da samfurin ya wuce darajar dubu shida (60,000). Jagorar Mataki na mataki-mataki:

1. Muna samar da zaɓi na tantanin halitta wanda aka ƙidaya kirga.
2. Motsi a cikin subsate "dabarun". Danna "Saka aiki." Allon yana nuna karamin taga da ake kira "Aikin Saka". Bayyana jerin da ke tare da rubutun "Kateory". A cikin jerin da aka katse jerin, zaɓi maɓallin "ƙididdigar ƙididdiga. A cikin "Zaɓi Aiki:" Filin, muna samun siginar afareto kuma danna kan shi tare da lkm. Bayan duk magudi, danna kan "ok".

goma sha tara

1. Wufantar da aka san mu a ƙarƙashin sunan "Gargajiya muhawara" ta bayyana. Anan, lokacin shigar da yanayi guda, sabon layi yana bayyana nan da nan don cika wani yanayi. A cikin layi "Range 1", muna fitar da adireshin shafi wanda bayanan tallace-tallace ke ciki gunduma. A cikin layi "yanayin 1", muna fitar da "> 300" mai nuna alama. A cikin layi "Range 2", ja adireshin shafi wanda bayanan farashin yake. A cikin "yanayin 2", ƙara darajar "> 6000". Bayan duk magidano, danna "Ok".

ashirin

1. A sakamakon haka, mun sami sakamakon da ya nuna wane ne wane sel da suka dace don yanayin ƙara yanayin bayyana a cikin ƙayyadadden kewayon. Mun sami adadin goma sha huɗu.

Hanya 21 na 6: Matsa aikin

Zaɓin zaɓi mai zuwa yana ba ku damar lissafin adadin sel mara komai. Don aiwatar da wannan hanyar, an daidaita shi daidai. Janar na mai aiki: = tsabtace tsabta (kewayon). Jagorar Mataki na mataki-mataki:

1. Muna samar da zaɓi na tantanin halitta wanda aka ƙidaya kirga.
2. Motsi a cikin subsate "dabarun". Danna kan "saka aiki" abu. Allon yana nuna karamin taga da ake kira "Aikin Saka". Bayyana jerin da ke kusa da rubutun "rukuni na" ". A cikin jerin da aka katse jerin, zaɓi maɓallin "ƙididdigar ƙididdiga. A cikin "Zaɓi Aiki:" Filin, muna samun mai aiki don tsaftacewa da danna ta da lkm. Bayan duk magudi, danna kan "ok".

22.

1. A cikin "hujjojin aikin" sun bayyana, za mu fitar da daidaitawa da suka dace na sel, sannan danna maballin "Ok".

23.

1. A sakamakon haka, mun sami sakamakon da adadin sel babu za a nuna.

24.

Kirga dabi'un rubutu na musamman a cikin MS Excel

Misali, yi la'akari da wannan alama:25.

Manufar: lissafta adadin sigogi na musamman a cikin A7: A15. Don yin wannan, muna amfani da dabara: = sumulacary ((A7: A15 ") / 8: A7: A15; A7: A15).

Yadda ake nemo dabi'un maimaitawa a cikin fice.

Misali, muna da farantin mai zuwa:

26.

Don yin lissafin dabi'un maimaitawa, ya zama dole don gabatar da tsari mai zuwa a G5: = Idan ($ 5; A5)> 1; 8). 1). Sannan kuna buƙatar kwafa wannan tsari a kan duk shafi.

Lissafta adadin maimaitawa a cikin shafi

Domin lissafta adadin da ake buƙata na ninki biyu a cikin shafi, dole ne ka yi masu zuwa:

1. Yi amfani da mu duka alama ce daga misalin da ke sama. Muna samar da zaɓi na sel na shafi.
2. Mix a cikin taga taga.
3. A cikin "nau'in lokaci zaɓi:" filin, danna kan kashi "Yi amfani da dabara don tantance sel da aka tsara".
4. A cikin layi "tsara tsari don tantance sel da aka tsara", tuƙi = 8 ($ A: $ A5; A5)> 1.

27.

1. Shirya! Mun aiwatar da lissafin wannan alamomi iri ɗaya a cikin shafi, kuma ya kasafta bayanan maimaituwa a wasu launuka.

Kirga yawan takamaiman darajar sel a Excel ya bayar

Yi la'akari da wannan lokacin akan takamaiman misali. A ce muna da farantin tebur masu zuwa:

28.

Mun ware e2: E5 kewayon kuma gabatar da dabara: = 8: B3: B19; D2: D5). B3: B19 - Kwayoyin da ke dauke da kimantawa, da D2: D5 - sel, a cikin abin da sharuɗɗa suke don kirga adadin rubutu. A sakamakon haka, muna samun irin wannan sakamakon:

29.

Ƙarshe

Editan tebur na Exel shine shirin da yawa na yau da kullun waɗanda ke ba ka damar warware yawancin ayyukan da yawa daban-daban. Tsarin aikin yana samar da ayyuka da yawa waɗanda ke ba ku damar lissafin adadin ƙimar da ke cikin ƙayyadaddun kewayon. Kowane mai amfani zai iya zaɓar yadda ya dace da damar da ya dace da ayyukansa.

Saƙo Yadda za a lissafta yawan ƙimar a cikin Excel shafi. Hanyoyi 6 don kirga yawan dabi'u a cikin Shafin Eksel sun bayyana da farko akan fasahar bayanai.