Momwe mungawerengere maperesenti mu Excel - ndi zitsanzo za fomula

Cholembachi chimapereka chidziwitso chatsatanetsatane cha njira yowerengera chidwi mu Excel, amafotokoza mafomu akulu ndi owonjezera (onjezani kapena kuchepetsa mtengo ndi peresenti inayake).

Palibe gawo lililonse la moyo momwe kuwerengera chidwi sikungafunikire. Itha kukhala nsonga kwa woperekera zakudya, ntchito kwa wogulitsa, msonkho wa ndalama kapena chiwongola dzanja chanyumba. Mwachitsanzo, kodi munapatsidwa kuchotsera 25 peresenti pa kompyuta yatsopano? Kodi zoperekazi ndizopindulitsa pati? Ndipo ndi ndalama zingati zomwe muyenera kulipira, ngati mutachotsa kuchuluka kwa kuchotsera.

Masiku ano mudzatha kuchita maperesenti osiyanasiyana mu Excel bwino kwambiri.

Njira yodziwira maperesenti a mtengo wonse

Mawu akuti "peresenti" amachokera ku Chilatini. Chilankhulochi chili ndi zomangamanga "percentum", yomwe imatanthawuza "zana". Anthu ambiri ochokera kumaphunziro a masamu amatha kukumbukira zomwe zilipo.

Peresenti ndi gawo la nambala 100. Kuti mupeze, muyenera kugawa nambala A ndi nambala B ndikuchulukitsa nambalayo ndi 100.

Kwenikweni, njira yodziwira maperesenti ndi motere:

(Part number/Nambala yonse)*100.

Tiyerekeze kuti muli ndi ma tangerines 20, ndipo mukufuna kupereka asanu aiwo pa Chaka Chatsopano. Ndi ndalama zingati pamaperesenti? Pambuyo pochita ntchito zosavuta (= 5/5 * 20), timapeza 100%. Iyi ndiye njira yayikulu yowerengera kuchuluka kwa nambala m'moyo wamba.

Ku Excel, kudziwa maperesenti ndikosavuta chifukwa ntchito zambiri zimachitika ndi pulogalamu kumbuyo.

Ndizomvetsa chisoni, koma palibe njira yapadera yomwe imakulolani kuti mugwiritse ntchito mitundu yonse yomwe ilipo. Chilichonse chimakhudzidwa ndi zotsatira zofunikira, chifukwa cha kupindula komwe mawerengedwe amachitidwa.

Chifukwa chake, nazi ntchito zina zosavuta mu Excel, monga kudziwa, kuchulukitsa / kuchepetsa kuchuluka kwa chinthu pamaperesenti, kupeza kuchuluka kofanana ndi peresenti.

Njira yayikulu yodziwira maperesenti mu Excel

Gawo/Chiwerengero = peresenti

Poyerekeza chilinganizo chachikulu ndi njira yodziwira kuchuluka kwa maspredishiti, mutha kuwona kuti pamapeto pake palibe chifukwa chochulukitsa mtengo wake ndi 100. Izi ndichifukwa choti Excel imachita izi palokha ngati mutayamba kusintha mtundu wa selo. ku "peresenti".

Ndipo ndi zitsanzo zotani zowonetsera kuchuluka kwa Excel? Tiyerekeze kuti mumagulitsa zipatso ndi zakudya zina. Muli ndi chikalata chosonyeza kuchuluka kwa zinthu zomwe makasitomala adalamula. Mndandandawu waperekedwa mu gawo A, ndi chiwerengero cha madongosolo mu gawo B. Ena a iwo ayenera kuperekedwa, ndipo chiwerengerochi chaperekedwa mu gawo C. Choncho, ndime D iwonetsa kuchuluka kwa zinthu zomwe zaperekedwa. Kuti muwerenge, muyenera kutsatira izi:

1. Sonyezani = C2 / B2 mu selo D2 ndikuchitsitsa pansi pochikopera ku chiwerengero chofunikira cha maselo.
2. Dinani pa batani la "Percentage Format" pa "Home" tabu mu gawo la "Nambala".
3. Kumbukirani kuonjezera chiwerengero cha manambala pambuyo pa decimal point ngati kuli kofunikira.

Ndizomwezo.

Mukayamba kugwiritsa ntchito njira ina yowerengera chidwi, kutsatizana kwa masitepe kudzakhala kofanana.

Pachifukwa ichi, kuchuluka kwazinthu zomwe zaperekedwa zimawonetsedwa pagawo la D. Kuti muchite izi, chotsani malo onse. Pulogalamuyi imangowonetsa mtengo wozungulira.

Zachitidwa mwanjira iyi

Kuzindikira kachigawo kakang'ono ka mtengo wonse

Nkhani yosankha gawo la nambala yonse yomwe tafotokozera pamwambapa ndi yofala kwambiri. Tiyeni tifotokoze zochitika zingapo zomwe chidziwitso chopezeka chingagwiritsidwe ntchito pochita.

Mlandu 1: chiwerengero chili pansi pa tebulo mu selo yeniyeni

Anthu nthawi zambiri amaika mtengo wathunthu kumapeto kwa chikalata mu selo linalake (nthawi zambiri pansi kumanja). Pazifukwa izi, chilinganizocho chidzakhala chofanana ndi chomwe chinaperekedwa kale, koma ndi nuance pang'ono, popeza adiresi ya selo mu denominator ndi mtheradi (ndiko kuti, ili ndi dola, monga momwe chithunzi chili pansipa) .

Chizindikiro cha dola $ chimakupatsani mwayi womanga ulalo kuselo inayake. Chifukwa chake, ikhalabe yofanana, ngakhale chilinganizocho chidzakopera kumalo ena. Chifukwa chake, ngati mawerengedwe angapo akuwonetsedwa mugawo B, ndipo mtengo wake wonse walembedwa mu selo B10, ndikofunikira kudziwa kuchuluka kwake pogwiritsa ntchito fomula: =B2/$B$10.

Ngati mukufuna kuti adilesi ya cell B2 isinthe malinga ndi komwe kuli kopiyo, muyenera kugwiritsa ntchito adilesi yachibale (popanda chizindikiro cha dollar).

Ngati adilesi yalembedwa mu cell $B10, momwemo chizindikirocho chidzakhala chimodzimodzi mpaka mzere 9 wa tebulo ili m'munsimu.

Malangizo: Kuti musinthe adilesi yachibale kukhala adilesi yeniyeni, muyenera kuyika chizindikiro cha dollar mmenemo. Ndizothekanso kudina ulalo wofunikira mu kapamwamba kapamwamba ndikudina batani F4.

Nachi chithunzi chowonetsa zotsatira zathu. Apa tidapanga ma cell kuti tizigawo tofika pa zana tiwonekere.

Chitsanzo 2: Zigawo zonse zandandalikidwa pamizere yosiyanasiyana

Mwachitsanzo, tinene kuti tili ndi chinthu chomwe chimafunikira masititchi angapo, ndipo tikuyenera kumvetsetsa momwe mankhwalawa amatchuka motsutsana ndi zomwe zagulidwa. Kenako muyenera kugwiritsa ntchito ntchito ya SUMIF, yomwe imapangitsa kuti muyambe kuwonjezera manambala onse omwe angatchulidwe ndi mutu womwe wapatsidwa, ndikugawa manambala okhudzana ndi mankhwalawa ndi zotsatira zomwe zapezeka powonjezera.

Kuti mukhale osavuta, nayi fomula:

=SUMIF(mulingo wamtengo wapatali, chikhalidwe, mafotokozedwe osiyanasiyana)/sum.

Popeza gawo A lili ndi mayina azinthu zonse, ndipo gawo B likuwonetsa kuchuluka kwa zinthu zomwe zidagulidwa, ndipo selo E1 imafotokoza dzina la chinthu chofunikira, ndipo kuchuluka kwa maoda onse ndi selo B10, chilinganizochi chiziwoneka motere:

=SUMIF(A2:A9 ,E1, B2:B9) / $B$10.

Komanso, wogwiritsa ntchito amatha kutchula dzina lachinthucho mwachindunji momwe alili:

=SUMIF(A2:A9, «matcheri», B2:B9) / $B$10.

Ngati ndikofunikira kudziwa gawo lazogulitsa zazing'ono, wogwiritsa ntchito amatha kufotokozera kuchuluka kwa zotsatira zomwe zapezedwa kuchokera kuzinthu zingapo za SUMIF, ndikuwonetsa kuchuluka kwa zomwe zidagulidwa pagululo. Mwachitsanzo, monga chonchi:

=(SUMIF(A2:A9, «matcheri», B2:B9) + SUMIF(A2:A9, «maapulo», B2:B9)) / $B$10.

Momwe mungawerengere kuchuluka kwa kuwongolera kwa mtengo ngati peresenti mu Excel

Pali njira zambiri zowerengera. Koma, mwina, njira yodziwira kusintha kwa kuchuluka imagwiritsidwa ntchito nthawi zambiri. Kuti mumvetse kuchuluka kwa chizindikirocho chawonjezeka kapena kuchepa, pali chilinganizo:

Kusintha kwamaperesenti = (BA) / A.

Powerengera zenizeni, ndikofunikira kumvetsetsa mtundu womwe mungagwiritse ntchito. Mwachitsanzo, mwezi wapitawo panali mapichesi 80, ndipo tsopano alipo 100. Izi zikusonyeza kuti panopa muli ndi mapichesi 20 kuposa kale. Kuwonjezekaku kunali 25 peresenti. Ngati kale panali mapichesi 100, ndipo tsopano alipo 80 okha, izi zikusonyeza kuchepa kwa chiwerengero cha 20 peresenti (popeza zidutswa 20 mwa zana ndi 20%).

Chifukwa chake, formula mu Excel idzawoneka motere: (mtengo watsopano - mtengo wakale) / mtengo wakale.

Ndipo tsopano muyenera kudziwa momwe mungagwiritsire ntchito njirayi m'moyo weniweni.

Chitsanzo 1: kuwerengera kusintha kwa mtengo pakati pa mizati

Tinene kuti gawo B likuwonetsa mitengo yanthawi yomaliza yofotokozera, ndipo gawo C likuwonetsa mitengo yapano. Kenako lowetsani fomula ili mu selo C2 kuti mudziwe kuchuluka kwa kusintha kwa mtengo wake:

= (C2-B2) / B2

Imayesa momwe mtengo wazinthu zomwe zalembedwa mugawo A wachulukira kapena kutsika poyerekeza ndi mwezi wapitawo (gawo B).

Mukakopera selo ku mizere yotsala, ikani mtundu wa chiwerengero kuti manambala pambuyo pa ziro awonetsedwe moyenera. Zotsatira zake zidzakhala zofanana ndi zomwe zili pazithunzi.

Mu chitsanzo ichi, machitidwe abwino akuwonetsedwa muzochita zakuda ndi zoipa zofiira.

Chitsanzo 2: kuwerengera kuchuluka kwa kusintha pakati pa mizere

Ngati pali ndime imodzi yokha ya manambala (mwachitsanzo, C yomwe ili ndi malonda a tsiku ndi tsiku ndi sabata), mudzatha kuwerengera kuchuluka kwa kusintha kwa mtengo pogwiritsa ntchito njira iyi:

= (S3-S2) / S2.

C2 ndi yoyamba ndipo C3 ndi selo yachiwiri.

Zindikirani. Muyenera kudumpha mzere woyamba ndikulemba fomula yofunikira mu selo yachiwiri. Muchitsanzo choperekedwa, ichi ndi D1.

Mukamaliza kugwiritsa ntchito mtundu waperesenti pamzati, zotsatirazi zidzapangidwa.

Ngati kuli kofunika kuti mudziwe kuchuluka kwa kusintha kwa mtengo kwa selo linalake, muyenera kukhazikitsa ulalowo pogwiritsa ntchito ma adilesi omwe ali ndi chizindikiro cha dola $.

Chifukwa chake, njira yowerengera kusintha kwa kuchuluka kwa maoda mu February poyerekeza ndi mwezi woyamba wa chaka ndi motere:

=(C3-$C$2)/$C$2.

Mukakopera selo kumaselo ena, adiresi yeniyeni sisintha malinga ngati wachibale ayamba kutchula C4, C5, ndi zina zotero.

Kuwerengera chidwi mu mawu kuchuluka

Monga momwe mwawonera kale, kuwerengera kulikonse mu Excel ndi ntchito yosavuta. Kudziwa kuchuluka kwake, ndikosavuta kumvetsetsa momwe zingakhalire kuchokera kuzinthu zonse zama digito.

Chitsanzo 1: kuwerengera gawo lachiwonkhetso

Tiyerekeze kuti mumagula laputopu $950 ndipo muyenera kulipira 11% msonkho pa kugula. Ndi ndalama zingati zomwe ziyenera kulipidwa pamapeto pake? Mwanjira ina, 11% ya $950 ingakhale ndalama zingati?

Njirayi ndi:

Nambala * peresenti = gawo.

Ngati tikuganiza kuti zonse zili mu cell A2, ndipo kuchuluka kwake kuli mu cell B2, imasinthidwa kukhala yosavuta. =A2*B2  Mtengo wa $104,50 umawoneka mu cell.

Kumbukirani kuti mukalemba mtengo womwe ukuwonetsedwa ndi chizindikiro cha peresenti (%), Excel amatanthauzira ngati zana. Mwachitsanzo, 11% imawerengedwa ndi pulogalamuyi ngati 0.11, ndipo Excel imagwiritsa ntchito chiwerengerochi m'mawerengedwe onse.

Mwanjira ina, chilinganizo =A2*11% ofanana =A2*0,11. Mwachilengedwe, mutha kugwiritsa ntchito mtengo 0,11 m'malo mwa kuchuluka mwachindunji munjira ngati ndizosavuta panthawiyo.

Chitsanzo 2: kupeza zonse kuchokera pagawo ndi paperesenti

Mwachitsanzo, mnzanu anakupatsani kompyuta yake yakale $400, yomwe ndi 30% ya mtengo wake wogula, ndipo muyenera kudziwa ndalama zogulira kompyuta yatsopano.

Choyamba muyenera kudziwa kuti ndi zingati peresenti ya mtengo woyambirira wa laputopu yogwiritsidwa ntchito.

Zikuoneka kuti mtengo wake ndi 70 peresenti. Tsopano muyenera kudziwa njira yowerengera mtengo woyambira. Ndiko kuti, kumvetsetsa kuchokera pa nambala 70% idzakhala 400. Njirayi ndi iyi:

Gawo la chiwerengero / peresenti = mtengo wonse.

Ngati ikugwiritsidwa ntchito ku data yeniyeni, ikhoza kutenga imodzi mwa mawonekedwe awa: =A2/B2 kapena =A2/0.7 kapena =A2/70%.

Momwe mungasinthire nambala kukhala peresenti

Tiyerekeze kuti tchuthi chayamba. Mwachilengedwe, ndalama zomwe zimagwiritsidwa ntchito tsiku ndi tsiku zimakhudzidwa, ndipo mungafune kulingalira njira zina zopezera ndalama zokwanira mlungu uliwonse zomwe ndalama za sabata zingachuluke. Ndiye ndi zothandiza kuonjezera chiwerengero ndi peresenti inayake.

Kuti muwonjezere kuchuluka kwa ndalama ndi chiwongola dzanja, muyenera kugwiritsa ntchito njira iyi:

= mtengo * (1+%).

Mwachitsanzo, mu formula =A1*(1+20%) mtengo wa selo A1 ukuwonjezeka ndi chachisanu.

Kuti muchepetse nambala, gwiritsani ntchito fomula:

= Tanthauzo * (1–%).

Inde, njira = A1*(1-20%) amachepetsa mtengo wa cell A1 ndi 20%.

Muchitsanzo chomwe tafotokozazi, ngati A2 ndiye mtengo wanu wapano ndipo B2 ndiye kuchuluka kwake komwe muyenera kuwasinthira, muyenera kulemba mafomula mu cell C2:

1. Kuwonjezeka kwaperesenti:  =A2*(1+B2).
2. Chepetsani ndi peresenti: =A2*(1-B2).

Momwe mungakulitsire kapena kuchepetsa zikhalidwe zonse zagawo lonse ndi peresenti

Momwe mungasinthire zikhalidwe zonse mugawo kukhala peresenti?

Tiyerekeze kuti muli ndi mndandanda wazinthu zomwe muyenera kusintha kukhala gawo lina, ndipo mukufuna kukhala ndi zikhalidwe zosinthidwa pamalo omwewo popanda kuwonjezera ndime yatsopano ndi formula. Nazi njira 5 zosavuta kuti mumalize ntchitoyi:

1. Lowetsani zikhalidwe zonse zomwe zimafunikira kuwongolera mugawo linalake. Mwachitsanzo, mu gawo B.
2. Mu cell yopanda kanthu, lembani imodzi mwa njira zotsatirazi (kutengera ntchitoyo):
   1. Wonjezani: =1+20%
   2. Chepetsani: = 1-20%.

Mwachilengedwe, m'malo mwa "20%" muyenera kufotokoza mtengo wofunikira.

1. Sankhani cell yomwe fomulayo idalembedwera (iyi ndi C2 mu chitsanzo chomwe tikufotokoza) ndikukopera ndikukanikiza kuphatikiza kiyi Ctrl + C.
2. Sankhani ma cell omwe akufunika kusinthidwa, dinani kumanja kwawo ndikusankha "Paste Special ..." mu mtundu wa Chingerezi wa Excel kapena "Paste Special" mu .

1. Kenako, bokosi la zokambirana lidzawoneka momwe muyenera kusankha "Values" parameter (makhalidwe), ndikuyika ntchitoyo ngati "Kuchulukitsa" (chulukitsani). Kenako, dinani batani "Chabwino".

Ndipo nazi zotsatira zake - zonse zomwe zili mugawo B zawonjezeka ndi 20%.

Mwa zina, mutha kuchulukitsa kapena kugawa mizati yokhala ndi maperesenti ena. Ingolowetsani kuchuluka komwe mukufuna mubokosi lopanda kanthu ndikutsata njira zomwe zili pamwambapa.