Ukukhula njengephesenti - ifomula ku-Excel

Amaphesenti angenye yezindlela ezidume kakhulu zokusebenza nge-Excel. Ikakhulukazi, abasebenzisi abaningi bafuna ukufunda indlela yokubala ukukhuphuka kwenkomba ethile njengephesenti. Ngakho-ke, kuyasiza ekuhlaziyeni izingcaphuno zemali noma izinguquko ezintengo zezimpahla ezithile uma kuqhathaniswa nesikhathi sokubika sangaphambilini.

Indlela Yokubala Izinga Lokukhula Nezinga Lokukhula ku-Excel

Ukuze unqume izinga lokukhula nelokukhula ku-Excel, kufanele uqale uchaze ukuthi iyiphi yale mibono. Izinga lokukhula lisho isilinganiso esiphakathi kwevelu ekhiqizwe phakathi nalesi sikhathi sokubika kanye nepharamitha efanayo yedlule. Le nkomba ichazwa njengephesenti. Uma kungekho ukukhula uma kuqhathaniswa nesikhathi sokubika sangaphambilini, kusho ukuthi inani lingu-100%.

Uma izinga lokukhula lingaphezu kwamaphesenti angu-100, lokhu kubonisa ukuthi inkomba ethile ikhulile esikhathini sokubika sokugcina (noma eziningana). Uma kuncane, ngakho-ke, ngokufanele, yawa. Ifomula evamile ifana nefomula evamile yokuthola iphesenti, lapho isihlukanisi siyinani okufanele siqhathaniswe, futhi idinomineyitha iyinkomba okufanele iqhathaniswe nayo.

Ngokulandelayo, incazelo yamazinga okukhula yenziwa ngendlela ethe ukuhluka. Okokuqala, izinga lokukhula libalwa, ngemva kwalokho sikhipha ikhulu kunani eliwumphumela. Okusele yiphesenti ukwenyuka noma ukwehla kwenkomba ebalulekile. Iyiphi inkomba ongayisebenzisa? Konke kuncike ekutheni yiluphi uhlobo lokumelela olufaneleka kakhulu esimweni esithile. Uma kudingekile ukukhombisa ukwanda noma ukwehla ngokuphelele, khona-ke amazinga okukhula asetshenziswa; uma kuqhathaniswa, amazinga okukhula asetshenziswa.

Amazinga okukhula kanye nokukhula ahlukaniswe abe izinhlobo ezimbili: iketango kanye nesisekelo. Esokuqala isilinganiso senani lamanje nedlule. Ukukhula okuyisisekelo nokukhula akuthathi inani langaphambili njengesisekelo sokuqhathanisa, kodwa uhlobo oluthile lwevelu yesisekelo. Ngokwesibonelo, eyokuqala ngokulandelana.

Yini ebhekwa njengenani eliyisisekelo nelidlule? Uma sikhuluma ngenkomba yokuqala, isibonelo, inkomba ye-Dow Jones ngoJanuwari 2020, futhi izilinganiso zithathwa ngoJanuwari 2021, khona-ke singasho ukuthi izinga lokukhula kwesisekelo senkomba laliningi kakhulu. Futhi, njengesibonelo sokukhula okuyisisekelo noma ukukhula, ungaqhathanisa nevelu yokuqala yale nkomba lapho ishicilelwa okokuqala. Isibonelo sokukhuphuka noma ukuzuza kwangaphambilini ukuqhathaniswa kwevelu yale nkomba ngo-December uma kuqhathaniswa nesizinda sikaNovemba wonyaka ofanayo. Kungakhathaliseki ukuthi hlobo luni lokukhula, udinga ukususa u-100 ukuze uthole izinga lokukhula kukho.

Ungabala kanjani amaphesenti ku-Excel

Ukubala amaphesenti ku-Excel kwenziwa ngokwesisekelo. Udinga ukufaka izinombolo ezidingekayo kanye, bese uhlelo lokusebenza luzokwenza zonke izenzo ngokwalo. Ifomula evamile yokuthola inzalo ingxenyana yenombolo/inombolo*100. Kodwa uma senza izibalo nge-Excel, ukuphindaphinda kwenziwa ngokuzenzakalelayo. Ngakho-ke yini okudingeka siyenze ukuze sinqume iphesenti ku-Excel?

1. Okokuqala sidinga ukusetha ifomethi yephesenti. Ukuze wenze lokhu, chofoza kwesokudla kuseli oyifunayo, bese ukhetha inketho ethi “Fometha Amaseli”. Lapho ibhokisi lengxoxo livela, kufanele sikhethe ifomethi elungile.
2. Futhi ukusetha ifomethi kungenzeka ngemenyu enkulu. Udinga ukuthola ithebhu "Ekhaya", uye kuyo futhi uthole "Inombolo" iqembu lamathuluzi. Kunenkambu yokufaka yefomethi yeseli. Udinga ukuchofoza umcibisholo oseduze kwawo bese ukhetha owudingayo ohlwini.

Manje ake sibonise ukuthi lokhu kwenziwa kanjani ngokusebenza, sisebenzisa isibonelo sangempela. Ake sithi sinethebula eliqukethe amakholomu amathathu: inombolo yomkhiqizo, ukuthengisa okuhleliwe, nokuthengisa kwangempela. Umsebenzi wethu ukunquma izinga lokuqaliswa kohlelo.

Ukuze ufeze umgomo, udinga ukwenza izenzo ezinjalo. Sizochaza umgomo, futhi uzodinga ukuhlinzeka ngamanani afanele wecala lakho.

1. Sibhala phansi ifomula =C2/B2 kuseli D2. Okusho ukuthi, sidinga ukuhlukanisa ukwenziwa kwangempela komsebenzi kunombolo, kanye nalowo ohleliwe enanini elincane.
2. Ngemva kwalokho, sisebenzisa indlela echazwe ngaphambili, sihumusha ifomethi ibe yiphesenti.
3. Okulandelayo, sinweba ifomula kumaseli asele sisebenzisa isibambo sokuqedela ngokuzenzakalela.

Ngemva kwalokho, zonke izenzo ezisele zizokwenziwa ngokuzenzakalelayo. Lokhu kuyinzuzo ye-Excel uma kuqhathaniswa nendlela yokubala amaphesenti - udinga nje ukufaka ifomula kanye, bese ungayikopisha izikhathi eziningi ozidingayo, futhi wonke amanani azobalwa ngokwawo. , futhi ngendlela efanele.

Iphesenti lenombolo

Ake sithi siyazi ukuthi yimaphi amaphesenti okufanele abe yingxenye yenombolo. Futhi umsebenzi wabekwa ukunquma ukuthi le ngxenye izoba yinani lezinombolo. Ukuze wenze lokhu, sebenzisa ifomula = iphesenti% * inombolo. Ake sithi, ngokwemibandela yenkinga, kuyadingeka ukunquma ukuthi kuzoba yimalini ku-7% kwamashumi ayisikhombisa. Ukuze uyixazulule, udinga:

1. Chofoza iseli elilungile bese ufaka ifomula elandelayo lapho: =7%*70.
2. Cindezela inkinobho ethi Enter futhi umphumela uzobhalwa kule seli.

Kungenzeka futhi ukukhomba inombolo ethile, kodwa isixhumanisi. Ukuze wenze lokhu, kwanele ukufaka ikheli leseli elihambisanayo ngefomethi ye-B1. Qiniseka ukuthi iqukethe idatha yezinombolo ngaphambi kokuyisebenzisa kufomula.

Amaphesenti yenani

Ngokuvamile, phakathi nokucubungula idatha, umsebenzisi unikezwa umsebenzi wokunquma isamba esiwumphumela samanani, bese kubalwa iphesenti yenani elithile ukusuka kunani eliwumphumela. Kunezixazululo ezimbili ezitholakalayo: umphumela ungabhalwa ngokusekelwe kuseli ethile noma usatshalaliswe kuthebula. Ake sinikeze isibonelo sokuxazulula inguqulo yokuqala yenkinga:

1. Uma sidinga ukurekhoda umphumela wokubala iphesenti leseli elilodwa elithile, sidinga ukubhala ireferensi ephelele kudinomineyitha. Ukuze wenze lokhu, udinga ukubeka uphawu olulodwa lwedola ($) phambi kwekheli lomugqa nekholomu.
2. Njengoba inani lethu lokugcina libhalwe kuseli B10, kuyadingeka ukulungisa ikheli layo ukuze lapho ifomula isakazeka kwamanye amaseli, ingashintshi. Ukuze senze lokhu, sibhala ifomula elandelayo: =B2/$B$10.
3. Bese udinga ukushintsha ifomethi yawo wonke amaseli kulolu chungechunge ibe amaphesenti. Ngemva kwalokho, usebenzisa umaka wokuqedela ngokuzenzakalela, hudula ifomula kuyo yonke eminye imigqa.

Singahlola umphumela. Ngenxa yokuthi isithenjwa esisisebenzisile sasiphelele, idinomineyitha kufomula ayizange ishintshe kwamanye amaseli. Uma singalubekanga uphawu lwedola, khona-ke ikheli “lizoshelela” phansi. Ngakho-ke, emgqeni olandelayo, i-denominator isivele inekheli elithi B11, bese - B12, njalonjalo.

Kodwa yini okufanele uyenze uma ulwazi oludingekayo lusatshalaliswa kulo lonke itafula? Ukuxazulula le nkinga eyinkimbinkimbi, udinga ukusebenzisa umsebenzi I-SUMMESLI. Ihlola amanani kububanzi ngokuqhathanisa nemibandela eshiwo, futhi uma enza kanjalo, iwafingqe. Ngemuva kwalokho, udinga ukuthola iphesenti yenani eliwumphumela.

Ifomula ngokwayo ngokuvamile ine-syntax elandelayo: i-SUMIF engu-uXNUMXd (ububanzi bemibandela; uhla lokuhlanganisa) / isamba esiphelele. Kunguqulo yesiNgisi yohlelo, lo msebenzi ubizwa ngokuthi I-SUMIF. Ake sichaze ukuthi ifomula engenhla isebenza kanjani:

1. Esimweni sethu, ububanzi bamanani busho amagama emikhiqizo. Zikukholamu yokuqala.
2. Ububanzi bokwengezwa yiwo wonke amanani aqukethwe kukholomu B. Okusho ukuthi, esimweni sethu, lena inombolo yemikhiqizo yesihloko ngasinye. Lawa manani kufanele ahlanganiswe.
3. Umbandela. Esimweni sethu, igama lesithelo.
4. Umphumela urekhodwa kuseli B10.

Uma sivumelanisa ifomula evamile engenhla esibonelweni sethu, izobukeka kanje: =СУММЕСЛИ(A2:A9;E1;B2:B9)/$B$10. Kanye nesithombe-skrini sokucaca.

Ngakho ungathola imiphumela yokubala ngayinye yepharamitha.

Ungabala Kanjani Ushintsho Lwephesenti

Futhi manje ake sithole ukuthi yini okudingeka yenziwe ukuze kunqunywe ukunyuka noma ukwehla kwenani elithile uma kuqhathaniswa nesikhathi esifanayo esidlule. Ukusebenza okwakhelwe ngaphakathi kwe-Excel kukuvumela ukuthi wenze izibalo ezinjalo. Ukuze wenze lokhu, udinga ukusebenzisa ifomula, okuyifomu lezibalo elijwayelekile (elingashintshwanga ku-Excel) libukeka kanje: (BA)/A = umehluko. Kepha ukuguqulwa kwephesenti kubalwa kanjani ku-Excel?

1. Ake sithi sinethebula lapho ikholamu yokuqala iqukethe umkhiqizo esiwuhlaziyayo. Ikholomu yesibili neyesithathu ibonisa inani layo lika-August no-September, ngokulandelana. Futhi kukholamu yesine, sizobala ukwanda noma ukwehla njengephesenti.
2. Ngokufanelekile, kuyadingeka kuseli lokuqala ngemva kwesihloko esikukholomu D ukubhala ifomula yokubala ukushintshwa kwephesenti kumugqa wokuqala. =(C2/B2)/B2.
3. Okulandelayo, sebenzisa ukuqedela ngokuzenzakalela ukuze welule ifomula kuyo yonke ikholomu.

Uma amanani okudingeka siwabale ebekwe kukholamu eyodwa yomkhiqizo othile isikhathi eside, kufanele sisebenzise indlela yokubala ehluke kancane:

1. Ikholomu yesibili iqukethe ulwazi lokuthengisa lwenyanga ngayinye ethile.
2. Kukholomu yesithathu, sibala ukushintshwa kwephesenti. Ifomula esiyisebenzisayo ithi: =(B3-B2)/B2 .
3. Uma ufuna ukuqhathanisa amanani nenkomba echazwe kahle eseselini elithile, lapho-ke senza isixhumanisi siphelele. Ake sithi uma sidinga ukwenza isiqhathaniso noJanuwari, ifomula yethu izoba kanje. Ungayibona kusithombe-skrini.

Iqiniso lokuthi kukhona ukwanda, hhayi ukuwa, singakuqonda ngokungabi bikho kophawu lokususa phambi kwenombolo. Ngokulandelayo, amanani angemahle akhombisa ukwehla kwezinkomba uma kuqhathaniswa nenyanga yesisekelo.

Ukubalwa kwenani nenani eliphelele

Ngokuvamile, sazi kuphela iphesenti lenombolo, futhi sidinga ukunquma inani eliphelele. I-Excel inikeza izindlela ezimbili zokuxazulula le nkinga. Ake sithi unekhompuyutha ephathekayo ebiza u-$950. Ngokwemininingwane yomthengisi, i-VAT, engu-11%, nayo kufanele yengezwe kule ntengo. Ukuze uthole umphumela ophelele, udinga ukwenza izibalo ezimbalwa eziyisisekelo ku-Excel.

1. Ifomula evamile esizoyisebenzisa ithi − Isamba * % = Inani.
2. Beka ikhesa kuseli C2. Kuyo sibhala phansi ifomula ekhonjiswe kusithombe-skrini.
3. Ngakho, umaki obangelwa intela uzoba ngu-$104,5. Ngakho-ke, izindleko eziphelele ze-laptop zizoba ngu-$ 1054.

Ake sisebenzise esinye isibonelo ukukhombisa indlela yokubala yesibili. Ake sithi sithenga ikhompyutha ephathekayo engu-$400 futhi umthengisi uthi intengo isivele ihlanganisa isaphulelo esingu-30%. Futhi sithathwa yilukuluku, kodwa iyini intengo yokuqala? Ukuze wenze lokhu, udinga ukulandela le algorithm:

1. Okokuqala, sinquma isabelo esikhokhwa yithi. Esimeni sethu yi-70%.
2. Ukuze sithole inani loqobo, sidinga ukuhlukanisa isabelo ngamaphesenti. Okusho ukuthi, ifomula izoba kanje: Ingxenye/% = Isamba senani
3. Esibonelweni sethu, ikholomu yokuqala iqukethe izindleko zekhompyutha ephathekayo, futhi ikholomu yesibili iqukethe iphesenti lokugcina yenani lokuqala esiyikhokhile. Ngakho-ke, umphumela wokugcina urekhodwa kukholamu yesithathu, kuseli lokuqala ngemva kwesihloko esibhala ifomula yalo. =A2/B2 bese ushintsha ifomethi yeseli ibe amaphesenti.

Ngakho-ke, izindleko ze-laptop ngaphandle kwesaphulelo zazingamadola angama-571,43.

Ukushintsha inani ngephesenti

Ngokuvamile kufanele siguqule inombolo ngephesenti elithile. Kwenziwa kanjani? Umsebenzi ungafezwa ngokusebenzisa ifomula =Izindleko*(1+%). Udinga ukubeka amanani afanele ezindaweni ezifanele, futhi umgomo uyafinyelelwa.

Iphesenti Lokusebenza ku-Excel

Eqinisweni, amaphesenti ayizinombolo ezifanayo njengezinye, ngakho-ke ungenza yonke imisebenzi ye-arithmetic okungenzeka ngayo, futhi usebenzise amafomula. Ngakho-ke, namuhla sithole izici zokusebenza ngamaphesenti ku-Excel. Ikakhulukazi, siye saqonda ukuthi singabala kanjani ukunyuka kwamaphesenti, kanye nendlela yokwandisa inombolo ngamaphesenti athile.