Ungabala kanjani inani elilandelanayo ku-Excel. Izindlela Ezi-3 Zokubala Isamba Sezinombolo Emgqeni we-Excel

Ukusebenza ngolwazi lwethebula, abasebenzisi ngokuvamile badinga ukubala inani lenkomba. Ngokuvamile lezi zinkomba zingamagama emigqa okudingeka ngayo ukufingqa lonke ulwazi kumaseli. Kusukela esihlokweni uzofunda zonke izindlela ezikuvumela ukuthi usebenzise le nqubo.

Isamba samanani silandelana

Ungakwazi ukukhiqiza kabusha inqubo yokufingqa amanani ngokulandelana usebenzisa izindlela ezilandelayo:

  • ifomula ye-arithmetic;
  • ukulinganisa okuzenzakalelayo;
  • imisebenzi ehlukahlukene.

Ngayinye yalezi zindlela ihlukaniswe yaba izindlela ezengeziwe. Ake sibhekane nazo ngokuningiliziwe.

Indlela yoku-1: ifomula ye-arithmetic

Okokuqala, ake sithole ukuthi, ngokusebenzisa ifomula ye-arithmetic, kungenzeka kanjani ukufingqa ngokulandelana. Ake sihlaziye yonke into ngesibonelo esithile. Ake sithi sinetafula elibonisa imali engenayo yezitolo ezi-5 ngezinsuku ezithile. Amagama ezitolo amagama emigqa. Amadethi amagama amakholomu.

Ungabala kanjani inani elilandelanayo ku-Excel. Izindlela Ezi-3 Zokubala Isamba Sezinombolo Emgqeni we-Excel
1

Inhloso: ukubala inani eliphelele lemali engenayo yesitolo sokuqala ngaso sonke isikhathi. Ukufeza lo mgomo, kuyadingeka ukwengeza wonke amaseli omugqa ahlobene nalesi sitolo. I-Walkthrough ibonakala kanje:

  1. Sikhetha iseli lapho umphumela uzoboniswa khona ngokuzayo. Faka uphawu “=" kuseli. Sicindezela i-LMB kuseli yokuqala ngqa kulo mugqa oqukethe izinkomba zezinombolo. Siyaqaphela ukuthi ngemva kokuchofoza izixhumanisi zeseli zaboniswa kuseli ukuze kubalwe umphumela. Faka uphawu "+" bese uchofoza iseli elilandelayo kumugqa. Siyaqhubeka sishintsha uphawu "+" ngokuxhumanisa amaseli omugqa wokuphuma kokuqala. Ngenxa yalokho, sithola ifomula: =B3+C3+D3+E3+F3+G3+H3.
Ungabala kanjani inani elilandelanayo ku-Excel. Izindlela Ezi-3 Zokubala Isamba Sezinombolo Emgqeni we-Excel
2
  1. Ngemuva kokwenza konke ukukhohlisa, cindezela "Enter".
  2. Ilungile! Umphumela uboniswe kuseli lapho sifake khona ifomula yokubala inani.
Ungabala kanjani inani elilandelanayo ku-Excel. Izindlela Ezi-3 Zokubala Isamba Sezinombolo Emgqeni we-Excel
3

Nakani! Njengoba ubona, le ndlela icacile futhi ilula, kodwa ine-drawback eyodwa embi. Ukuqaliswa kwale ndlela kuthatha isikhathi esiningi. Ake sicabangele ukuhlukahluka okusheshayo kokufinyezwa.

Indlela 2: I-AutoSum

Ukusebenzisa i-autosum kuyindlela eshesha kakhulu kunalena okukhulunywe ngayo ngenhla. I-Walkthrough ibonakala kanje:

  1. Ngokusebenzisa i-LMB ecindezelwe, sikhetha wonke amaseli omugqa wokuqala anedatha yezinombolo. Sithuthela engxenyeni ethi “Ekhaya”, etholakala phezulu kwesixhumi esibonakalayo sespredishithi. Sithola ibhulokhi yemiyalo ethi "Ukuhlela" bese uchofoza into ebizwa ngokuthi "Ukuhlela".
    Ungabala kanjani inani elilandelanayo ku-Excel. Izindlela Ezi-3 Zokubala Isamba Sezinombolo Emgqeni we-Excel
    4

Isincomo! Enye inketho ukuya esigabeni esithi “Amafomula” bese uchofoza inkinobho ethi “AutoSum” etholakala kubhulokhi elithi “Function Library”. Inketho yesithathu ukusebenzisa inhlanganisela yokhiye "Alt" + "=" ngemuva kokukhetha iseli.

Ungabala kanjani inani elilandelanayo ku-Excel. Izindlela Ezi-3 Zokubala Isamba Sezinombolo Emgqeni we-Excel
5
  1. Kungakhathaliseki ukuthi iyiphi inketho oyifakile, inani lenombolo livele kwesokudla samaseli akhethiwe. Le nombolo iyisamba sezikolo zomugqa.
Ungabala kanjani inani elilandelanayo ku-Excel. Izindlela Ezi-3 Zokubala Isamba Sezinombolo Emgqeni we-Excel
6

Njengoba ubona, le ndlela yenza ukujula komugqa ngokushesha kakhulu kunalena engenhla. I-drawback eyinhloko ukuthi umphumela uboniswa kuphela kwesokudla sebanga elikhethiwe. Ukuze umphumela uboniswe kunoma iyiphi indawo ekhethiwe, kuyadingeka ukusebenzisa ezinye izindlela.

Indlela yesi-3: Umsebenzi we-SUM

Ukusebenzisa umsebenzi wespredishithi ohlanganisiwe obizwa ngokuthi i-SUM akunakho ukubi kwezindlela okuxoxwe ngazo ngaphambilini. I-SUM umsebenzi wezibalo. Umsebenzi womsebenzisi uwukufinyeza kwamanani ezinombolo. Ukubuka okujwayelekile komsebenzisi: =USUM(inombolo1,inombolo2,…).

Kubalulekile! Izimpikiswano zalo msebenzi zingaba amanani ezinombolo noma izixhumanisi zamaseli. Inombolo enkulu yezimpikiswano ngu-255.

Isifundo sesinyathelo ngesinyathelo sibukeka kanje:

  1. Senza ukukhetha kwanoma iyiphi iseli engenalutho ekhasini lokusebenza. Kuyo sizobonisa umphumela wokuhlanganisa. Kuyaphawuleka ukuthi ingatholakala nasephepheni elihlukile ledokhumenti. Ngemva kokukhetha, chofoza inkinobho ethi "Faka Umsebenzi", etholakala eduze komugqa wokufaka amafomula.
Ungabala kanjani inani elilandelanayo ku-Excel. Izindlela Ezi-3 Zokubala Isamba Sezinombolo Emgqeni we-Excel
7
  1. Iwindi elincane elibizwa ngokuthi “Function Wizard” lavezwa esikrinini. Nweba uhlu eduze nombhalo othi “Isigaba:” bese ukhetha into ethi “Mathematical”. Okuphansi kancane ohlwini "Khetha umsebenzi:" sithola u-opharetha we-SUM bese uchofoza kuso. Ngemuva kokwenza konke ukukhohlisa, chofoza inkinobho ethi "OK" etholakala phansi kwefasitela.
Ungabala kanjani inani elilandelanayo ku-Excel. Izindlela Ezi-3 Zokubala Isamba Sezinombolo Emgqeni we-Excel
8
  1. Iwindi elibizwa ngokuthi “Izimpikiswano Zomsebenzi” livele esibukweni. Esigabeni esingenalutho "Inombolo1" faka ikheli lomugqa, amanani ofuna ukuwengeza. Ukuze senze le nqubo, sibeka i-pointer kulo mugqa, bese, sisebenzisa i-LMB, sikhetha lonke uhla olunamanani ezinombolo. Ngemuva kokwenza konke ukukhohlisa, chofoza "OK".
Ungabala kanjani inani elilandelanayo ku-Excel. Izindlela Ezi-3 Zokubala Isamba Sezinombolo Emgqeni we-Excel
9
  1. Ilungile! Umphumela wokufinyeza uboniswe kuseli elikhethiwe ekuqaleni.
Ungabala kanjani inani elilandelanayo ku-Excel. Izindlela Ezi-3 Zokubala Isamba Sezinombolo Emgqeni we-Excel
10

Umsebenzi we-SUM awusebenzi

Kwesinye isikhathi kuyenzeka ukuthi u-opharetha we-SUM angasebenzi. Izimbangela eziyinhloko zokungasebenzi kahle:

  • ifomethi yenombolo engalungile (umbhalo) lapho ungenisa idatha;
  • ukuba khona kwezinhlamvu ezifihliwe nezikhala kumaseli anamanani ezinombolo.

Kumele uqaphele! Amanani ezinombolo ahlala enesizathu sokunene futhi imininingwane yombhalo ihlale inesizathu sokushiya.

Ungayithola kanjani isamba samanani amakhulu (amancane kakhulu).

Ake sithole ukuthi singabala kanjani isamba samanani amancane kakhulu noma amakhulu. Isibonelo, sidinga ukuhlanganisa amanani amathathu obuncane noma amathathu aphezulu.

Ungabala kanjani inani elilandelanayo ku-Excel. Izindlela Ezi-3 Zokubala Isamba Sezinombolo Emgqeni we-Excel
11

I-opharetha ENKULU ikuvumela ukuthi ubuyisele amaphuzu aphezulu kusuka kudatha ekhethiwe. I-agumenti yesi-2 icacisa ukuthi iyiphi imethrikhi okufanele ibuyiswe. Esibonelweni sethu esithile, ifomula ibonakala kanje: =СУММ(НАИБОЛЬШИЙ(B2:D13;{1;2;3})).

Ukusesha inani elincane kunawo wonke kusebenza ngendlela efanayo, kusetshenziswa umsebenzi OKUNCANE kuphela esikhundleni se-opharetha OMKHULU KAKHULU. Ifomula ibukeka kanje: =СУММ(НАИМЕНЬШИЙ(B2:D13;{1;2;3})).

Ukwelula ifomula/umsebenzi kweminye imigqa

Sithole ukuthi inani eliphelele libalwa kanjani kumaseli emugqeni owodwa. Ake sithole ukuthi singayisebenzisa kanjani inqubo yokuhlanganisa kuyo yonke imigqa yetafula. Amafomula okubhala ngesandla nokufaka opharetha we-SUM kuyizindlela ezinde nezingasebenzi kahle. Isixazululo esilungile ukwelula umsebenzi noma ifomula enanini elifiswayo lemigqa. I-Walkthrough ibonakala kanje:

  1. Sibala inani sisebenzisa enye yezindlela ezingenhla. Hambisa isikhombi segundane kuzimele ongezansi kwesokudla seseli ngomphumela obonisiwe. Ikhesa izoba ngesimo sophawu oluncane olumnyama oluhlanganisayo. Bamba i-LMB bese uhudulela ifomula phansi impela kwepuleti.
Ungabala kanjani inani elilandelanayo ku-Excel. Izindlela Ezi-3 Zokubala Isamba Sezinombolo Emgqeni we-Excel
12
  1. Ilungile! Sifingqa imiphumela yazo zonke izihloko. Sithole lo mphumela ngenxa yokuthi lapho sikopisha ifomula, amakheli ayagudluzwa. I-offset yezixhumanisi kungenxa yokuthi amakheli ahlobene.
Ungabala kanjani inani elilandelanayo ku-Excel. Izindlela Ezi-3 Zokubala Isamba Sezinombolo Emgqeni we-Excel
13
  1. Emgqeni wesi-3, ifomula ibukeka kanje: =B3+C3+D3+E3+F3+G3+H3.
Ungabala kanjani inani elilandelanayo ku-Excel. Izindlela Ezi-3 Zokubala Isamba Sezinombolo Emgqeni we-Excel
14

Ibalwa kanjani isamba somugqa ngamunye we-Nth.

Esibonelweni esithile, sizohlaziya indlela yokubala isamba somugqa ngamunye we-Nth. Isibonelo, sinethebula elibonisa inzuzo yansuku zonke yesitolo isikhathi esithile.

Ungabala kanjani inani elilandelanayo ku-Excel. Izindlela Ezi-3 Zokubala Isamba Sezinombolo Emgqeni we-Excel
15

Umsebenzi: ukubala inzuzo yamasonto onke yesonto ngalinye. Umsebenzisi we-SUM ikuvumela ukuthi uhlanganise idatha hhayi kuphela kububanzi, kodwa futhi ngohlelo. Lapha kuyadingeka ukusebenzisa i-OFFSET yomsebenzisi osizayo. Umsebenzisi we-OFFSET ucacisa inani lezimpikiswano:

  1. Iphuzu lokuqala. Iseli C2 lifakwa njengesithenjwa esiphelele.
  2. Inombolo yezinyathelo eziya phansi.
  3. Inombolo yezinyathelo eziya kwesokudla.
  4. Inani lezinyathelo eziya phansi.
  5. Inani lamakholomu kumalungu afanayo. Ukushaya iphuzu lokugcina lohlu lwezinkomba.

Siphetha ngefomula elandelayo yeviki lokuqala: =СУММ(СМЕЩ($C$2;(СТРОКА()-2)*5;0;5;1)). Ngenxa yalokho, u-opharetha wesamba uzohlanganisa wonke amanani ezinombolo ezinhlanu.

Isamba esingu-3-D, noma ukusebenza ngamashidi amaningi ebhuku lokusebenzela le-Excel

Ukuze ubale izinombolo ukusuka kububanzi obufanayo bomumo phakathi kwenani lamakhasi okusebenza, i-syntax ekhethekile ebizwa ngokuthi “inkomba ye-3D” kufanele isetshenziswe. Ake sithi kuwo wonke amaphepha okusebenzela encwadi kunepuleti elinolwazi lwesonto. Kudingeka sikuhlanganise konke futhi sikulethe esibalweni sanyanga zonke. Ukuze uqalise, udinga ukubuka ividiyo elandelayo:

Sinezingcwecwe ezine ezifanayo. Indlela evamile yokubala inzuzo ibukeka kanje: =СУММ(неделя1!B2:B8;неделя2!B2:B8;неделя3!B2:B8;неделя4!B2:B8). Lapha, ububanzi bamaseli busebenza njengama-agumenti.

Ifomula yesamba ye-3D ibukeka kanje: =ISAM(isonto1:isonto4!B2:B8). Ithi lapha ukuthi ukufinyezwa kwenziwa ezinhlwini B2:B8, ezitholakala kumakhasi okusebenza: isonto (kusuka ku-1 kuye ku-4). Kukhona ukwenyuka kwesinyathelo ngesinyathelo enanini lephepha lomsebenzi ngalinye.

Isamba esinemibandela eminingi

Kunezikhathi lapho umsebenzisi edinga ukuxazulula inkinga ecacisa izimo ezimbili noma ngaphezulu futhi edinga ukubala isamba samanani ezinombolo ngokuya ngemibandela ehlukile. Ukuze usebenzise le nqubo, sebenzisa umsebenzi «=SUMMESLIMN".

Ungabala kanjani inani elilandelanayo ku-Excel. Izindlela Ezi-3 Zokubala Isamba Sezinombolo Emgqeni we-Excel
16

Isifundo sesinyathelo ngesinyathelo sibukeka kanje:

  1. Okokuqala nje, kwakhiwa itafula.
  2. Ikhetha iseli lapho umphumela wokuhlanganisa uzoboniswa khona.
  3. Yiya emugqeni wokufaka amafomula.
  4. Sifaka opharetha: =SUMMAESLIMN.
  5. Isinyathelo ngesinyathelo, sifaka ububanzi bokwengeza, ububanzi be-condition1, condition1 njalonjalo.
  6. Ngemuva kokwenza konke ukukhohlisa, cindezela "Enter". Ilungile! Isibalo senziwe.

Kuyaphawuleka! Kufanele kube nesihlukanisi esisesimweni sesemicolon “;” phakathi kwezingxabano zomsebenzisi. Uma le delimiter ingasetshenziswa, isipredishithi sizokhiqiza iphutha elibonisa ukuthi umsebenzi ufakwe ngokungalungile.

Ibalwa kanjani iphesenti lemali

Manje ake sikhulume ngendlela yokubala kahle iphesenti lemali. Indlela elula, ezoqondwa yibo bonke abasebenzisi, ukusebenzisa isilinganiso noma umthetho "wesikwele". Ingqikithi ingaqondwa esithombeni esingezansi:

Ungabala kanjani inani elilandelanayo ku-Excel. Izindlela Ezi-3 Zokubala Isamba Sezinombolo Emgqeni we-Excel
17

Inani eliphelele liboniswa kuseli F8 futhi linenani lika-3060. Ngamanye amazwi, lokhu kuyinzuzo engamaphesenti ayikhulu, futhi sidinga ukuthola ukuthi ingakanani inzuzo eyenziwe ngu-Sasha. Ukubala, sisebenzisa ifomula yengxenye ekhethekile, ebukeka kanje: =F10*G8/F8.

Kubalulekile! Okokuqala, amanani ezinombolo ama-2 aziwayo u3buXNUMXbare aphindaphindeka ngokudayelayo, bese ahlukaniswa ngenani le-XNUMX elisele.

Ukusebenzisa lo mthetho olula, ungakwazi kalula futhi kalula ukubala iphesenti lemali.

Isiphetho

I-athikili ixoxe ngezindlela ezimbalwa zokuthola isamba sedatha yomugqa kusipredishithi se-Excel. Indlela ngayinye inezinzuzo zayo kanye nokubi. Ukusebenzisa ifomula ye-arithmetic kuyindlela elula yokusebenzisa, kodwa kufanelekile kakhulu ukuyisebenzisa lapho usebenza nenani elincane lolwazi. Ngokusebenza ngenani elikhulu ledatha, ukujula okuzenzakalelayo kufanelekile, kanye nomsebenzi we-SUM.

shiya impendulo