Kukula ngati peresenti - formula mu Excel

Maperesenti ndi imodzi mwa njira zodziwika kwambiri zogwirira ntchito ndi Excel. Makamaka, ogwiritsa ntchito ambiri amafuna kuphunzira momwe angawerengere kuwonjezeka kwa chizindikiro china ngati peresenti. Chifukwa chake, ndizothandiza kusanthula mitengo yandalama kapena kusintha kwamitengo yazinthu zina poyerekeza ndi nthawi yapitayi.

Momwe Mungawerengere Mlingo wa Kukula ndi Kukula kwa Mtengo mu Excel

Kuti mudziwe kukula ndi kukula kwa Excel, choyamba muyenera kufotokozera mfundo iliyonse mwa mfundozi. Kukula kumatanthawuza chiŵerengero chapakati pa mtengo wopangidwa pa nthawi yochitira lipoti ndi gawo lomwelo la m'mbuyomo. Chizindikirochi chimatanthauzidwa ngati peresenti. Ngati palibe kukula poyerekeza ndi nthawi yapitayi, ndiye kuti mtengo wake ndi 100%.

Ngati chiwongola dzanja chikuposa 100 peresenti, izi zikuwonetsa kuti chizindikiro china chakula pa nthawi yomaliza yopereka lipoti (kapena zingapo). Ngati zochepa, ndiye, mogwirizana, anagwa. Fomula yofananira ndi yofanana ndi njira yopezera gawo, pomwe gawoli ndilofunika kufaniziridwa, ndipo denominator ndiye chizindikiro chofananira nacho.

Momwemonso, tanthauzo la kukula kwa mitengo ikuchitika m'njira yosiyana pang'ono. Choyamba, kuchuluka kwa kukula kumawerengedwa, pambuyo pake timachotsa zana kuchokera pamtengo wotsatira. Chotsalira ndi kuchuluka komwe kuwonjezeka kapena kuchepa kwa chizindikiro chofunikira kunachitika. Ndi chizindikiro chiti chomwe mungagwiritse ntchito? Zonse zimatengera mtundu woyimira womwe uli wosavuta pazochitika zinazake. Ngati kuli kofunikira kuwonetsa kuwonjezereka kapena kuchepa kwathunthu, ndiye kuti kukula kwake kumagwiritsidwa ntchito; Ngati ndi kotheka, kukula kwake kumagwiritsidwa ntchito.

Ziwerengero za kukula ndi kukula zimagawidwa m'magulu awiri: unyolo ndi woyambira. Choyamba ndi chiŵerengero cha mtengo wamakono ndi wam'mbuyo. Kukula koyambira ndi kukula sikutengera mtengo wam'mbuyomu ngati maziko ofananiza, koma mtundu wina wa mtengo woyambira. Mwachitsanzo, choyamba mu mndandanda.

Kodi ndi chiyani chomwe chimatengedwa ngati mtengo woyambira komanso wam'mbuyomu? Ngati tikulankhula za choyambira, mwachitsanzo, index ya Dow Jones mu Januware 2020, ndipo miyeso imatengedwa mu Januware 2021, ndiye kuti titha kunena kuti kukula kwa index kunali kochulukirapo. Komanso, monga chitsanzo cha kakulidwe kapena kakulidwe kake, mungayerekezere ndi mtengo woyamba wa index imeneyi pamene inasindikizidwa koyamba. Chitsanzo cha kukwera kapena kupindula m'mbuyomo ndi kuyerekezera mtengo wa ndondomekoyi mu December motsutsana ndi maziko a November chaka chomwecho. Ziribe kanthu kukula kwamtundu wanji, muyenera kuchotsa 100 kuti mutenge kukula kwake.

Momwe mungawerengere maperesenti mu Excel

Kuwerengera maperesenti mu Excel kumachitika poyambira. Muyenera kulowa manambala ofunikira kamodzi, ndiyeno pulogalamuyo idzachita zonse zokha. Njira yopezera chiwongola dzanja ndi kagawo kakang'ono ka nambala/nambala*100. Koma ngati tichita kuwerengera kudzera mu Excel, kuchulukitsa kumachitika zokha. Ndiye tifunika kuchita chiyani kuti tidziwe kuchuluka kwa Excel?

1. Choyamba tiyenera kukhazikitsa mtundu wa chiwerengero. Kuti muchite izi, dinani kumanja pa cell yomwe mukufuna, ndikusankha "Format Cells". Pamene bokosi la zokambirana likuwonekera, tiyenera kusankha mtundu woyenera.
2. Komanso kukhazikitsa mawonekedwe ndizotheka kudzera pa menyu yayikulu. Muyenera kupeza "Home" tabu, kupita kwa izo ndi kupeza "Nambala" gulu la zida. Pali malo olowetsa mtundu wa selo. Muyenera dinani muvi womwe uli pafupi ndi izo ndikusankha womwe mukufuna pamndandanda.

Tsopano tiyeni tiwonetse momwe izi zimagwiritsidwira ntchito, pogwiritsa ntchito chitsanzo chenicheni. Tiyerekeze kuti tili ndi tebulo lomwe lili ndi mizati itatu: nambala yazinthu, malonda omwe akukonzekera, ndi malonda enieni. Ntchito yathu ndikuzindikira kuchuluka kwa kukhazikitsidwa kwa dongosololi.

Kuti mukwaniritse cholingacho, muyenera kuchita izi. Tifotokoza mfundoyi, ndipo muyenera kupereka zikhalidwe zoyenera pamlandu wanu.

1. Timalemba ndondomeko = C2 / B2 mu selo D2. Ndiko kuti, tifunika kugawanitsa ntchito yeniyeniyo mu nambala, ndi yomwe inakonzedwa mu denominator.
2. Pambuyo pake, pogwiritsa ntchito njira yomwe tafotokozera kale, timamasulira mawonekedwewo kukhala peresenti.
3. Kenako, timakulitsa chilinganizo kumaselo otsalawo pogwiritsa ntchito chogwirira cha autocomplete.

Pambuyo pake, zochita zonse zotsala zidzachitidwa zokha. Uwu ndiye mwayi wa Excel poyerekeza ndi njira yowerengera maperesenti - muyenera kungoyika fomula kamodzi, ndiyeno mutha kuyikopera kangapo momwe mungafunire, ndipo zikhalidwe zonse zidzawerengedwa zokha. , ndi molondola.

Peresenti ya chiwerengero

Tiyerekeze kuti tikudziwa kuchuluka kwa chiwerengerocho. Ndipo ntchitoyo idakhazikitsidwa kuti idziwe kuchuluka kwa gawoli lomwe lidzakhale mu manambala. Kuti muchite izi, gwiritsani ntchito formula = peresenti% * nambala. Tiyerekeze, malinga ndi zikhalidwe za vuto, pakufunika kudziwa kuchuluka kwa 7% ya makumi asanu ndi awiri. Kuti muthetse, muyenera:

1. Dinani pa selo lolondola ndikulowetsamo zotsatirazi: = 7% * 70.
2. Dinani batani la Enter ndipo zotsatira zake zidzalembedwa mu cell iyi.

N'zothekanso kuloza osati nambala yeniyeni, koma ulalo. Kuti muchite izi, ndikwanira kuyika adilesi yofananira mumtundu wa B1. Onetsetsani kuti ili ndi manambala musanagwiritse ntchito m'njira.

Peresenti ya ndalamazo

Nthawi zambiri, pokonza deta, wogwiritsa ntchitoyo amapatsidwa ntchito yodziwira kuchuluka kwamtengo wapatali, ndiyeno kuwerengera chiwerengero cha mtengo wina kuchokera pamtengo wotsatira. Pali njira ziwiri zomwe zilipo: zotsatira zake zikhoza kulembedwa kutengera selo limodzi kapena kugawidwa patebulo. Tiyeni tipereke chitsanzo cha kuthetsa vuto loyamba:

1. Ngati tikufuna kulemba zotsatira za kuwerengera kuchuluka kwa selo limodzi, tifunika kulemba chilozera chenicheni mu denominator. Kuti muchite izi, muyenera kuyika chizindikiro chimodzi cha dola ($) kutsogolo kwa adilesi ya mzere ndi mzere.
2. Popeza mtengo wathu womaliza udalembedwa mu cell B10, ndikofunikira kukonza adilesi yake kuti mawonekedwewo akafalikira ku maselo ena, sasintha. Kuti tichite zimenezi, timalemba ndondomeko yotsatirayi: =B2/$B$10.
3. Kenako muyenera kusintha mawonekedwe a maselo onse mumndandandawu kukhala maperesenti. Pambuyo pake, pogwiritsa ntchito chikhomo cha autocomplete, kokerani fomula ku mizere ina yonse.

Tikhoza kuyang'ana zotsatira. Chifukwa mawu omwe tidagwiritsa ntchito anali mtheradi, choyimira mu fomula sichinasinthe m'maselo ena. Ngati sitinayike chikwangwani cha dollar, ndiye kuti adilesiyo "itha" pansi. Kotero, mu mzere wotsatira, choyimira chikanakhala kale ndi adilesi B11, ndiye - B12, ndi zina zotero.

Koma chochita ngati chidziwitso chofunikira chikugawidwa patebulo lonse? Kuti muthane ndi vutoli, muyenera kugwiritsa ntchito ntchitoyi SUMMESLI. Imayang'ana zomwe zili mumndandandawu motsutsana ndi zomwe zatchulidwa, ndipo ngati zitero, zimawaphatikiza. Pambuyo pake, muyenera kupeza kuchuluka kwa mtengo wotsatira.

Fomula yokhayo nthawi zambiri imakhala ndi mawu awa: uXNUMXd SUMIF (mitundu yosiyanasiyana; kuchuluka kwake) / ndalama zonse. M'Chingerezi cha pulogalamuyi, ntchitoyi imatchedwa Zithunzi za SUMIF. Tiyeni tifotokoze momwe fomula ili pamwambapa imagwirira ntchito:

1. Kwa ife, mitundu yosiyanasiyana imatanthawuza mayina azinthu. Iwo ali m’danga loyamba.
2. Zowonjezera ndizo zonse zomwe zili mugawo la B. Ndiye kuti, kwa ife, ichi ndi chiwerengero chazinthu zamutu uliwonse. Makhalidwe awa ayenera kuwonjezeredwa.
3. Criterion. Kwa ife, ndi dzina la chipatsocho.
4. Zotsatira zake zimalembedwa mu cell B10.

Ngati tisintha ndondomeko yomwe ili pamwambayi kuti ikhale ndi chitsanzo chathu, izi zidzawoneka motere: =СУММЕСЛИ(A2:A9;E1;B2:B9)/$B$10. Ndipo chithunzithunzi kuti chimveke bwino.

Kotero mutha kupeza zotsatira za kuwerengera pazigawo zonse.

Momwe Mungawerengere Kusintha Kwa Peresenti

Ndipo tsopano tiyeni tiwone zomwe zikuyenera kuchitika kuti tidziwe kuchuluka kapena kuchepa kwa mtengo wina poyerekeza ndi nthawi yomweyi. Magwiridwe amtundu wa Excel amakulolani kuti muwerenge motere. Kuti muchite izi, muyenera kugwiritsa ntchito chilinganizo, chomwe mu mawonekedwe a masamu (osasinthidwa ku Excel) amawoneka motere: (BA)/A = kusiyana. Koma kodi kusintha kwa maperesenti kumawerengedwa bwanji mu Excel?

1. Tiyerekeze kuti tili ndi tebulo momwe gawo loyamba lili ndi zinthu zomwe tikuzisanthula. Mizati yachiwiri ndi yachitatu ikuwonetsa mtengo wake wa Ogasiti ndi Seputembala motsatana. Ndipo mu gawo lachinayi, tiwerengera kuchuluka kapena kuchepa ngati peresenti.
2. Chifukwa chake, ndikofunikira kuti mu cell yoyamba itatha mutu wa D kuti mulembe chilinganizo chowerengera kuchuluka kwa kusintha pamzere woyamba. =(C2/B2)/B2.
3. Kenako, gwiritsani ntchito autocomplete kutambasula fomula mpaka gawo lonse.

Ngati zikhalidwe zomwe tiyenera kuwerengera zayikidwa muzakudya zina kwa nthawi yayitali, ndiye kuti tiyenera kugwiritsa ntchito njira yowerengera yosiyana pang'ono:

1. Mzere wachiwiri uli ndi zambiri zogulitsa mwezi uliwonse.
2. Mugawo lachitatu, timawerengera kuchuluka kwa kusintha. Njira yomwe timagwiritsa ntchito ndi: =(B3-B2)/B2 .
3. Ngati mukufuna kufananiza zikhalidwe ndi chizindikiro chodziwika bwino chomwe chili mu cell inayake, timapanga ulalo kukhala wokwanira. Tinene ngati tifunika kufananiza ndi Januware, ndiye kuti njira yathu ikhala motere. Mutha kuziwona pa skrini.

Mfundo yakuti pali kuwonjezeka, osati kugwa, tikhoza kumvetsa chifukwa chosowa chizindikiro chochepa patsogolo pa chiwerengero. Komanso, makhalidwe oipa amasonyeza kuchepa kwa zizindikiro poyerekeza ndi mwezi woyambira.

Kuwerengera mtengo ndi ndalama zonse

Nthawi zambiri, timangodziwa kuchuluka kwa nambala, ndipo timafunikira kudziwa kuchuluka kwake. Excel imapereka njira ziwiri zothetsera vutoli. Tiyerekeze kuti muli ndi laputopu yomwe imawononga $950. Malinga ndi chidziwitso cha wogulitsa, VAT, yomwe ndi 11%, iyeneranso kuwonjezeredwa pamtengowu. Kuti muwone zotsatira zonse, muyenera kuwerengera zoyambira zingapo mu Excel.

1. Njira yomwe tigwiritse ntchito ndi − Total * % = Mtengo.
2. Ikani cholozera mu selo C2. Mmenemo timalemba ndondomeko yomwe yasonyezedwa pazithunzi.
3. Chifukwa chake, kuchuluka kwa msonkho kudzakhala $104,5. Chifukwa chake, mtengo wonse wa laputopu udzakhala $1054.

Tiyeni tigwiritse ntchito chitsanzo china kusonyeza njira yachiwiri yowerengera. Tiyerekeze kuti tikugula laputopu ya $400 ndipo wogulitsa akuti mtengowo ukuphatikiza kale kuchotsera 30%. Ndipo timatengedwa ndi chidwi, koma mtengo wake woyamba ndi wotani? Kuti muchite izi, muyenera kutsatira algorithm iyi:

1. Choyamba, timazindikira gawo lomwe timalipira. Kwa ife ndi 70%.
2. Kuti tipeze mtengo woyambirira, tiyenera kugawa gawolo ndi kuchuluka kwake. Ndiye kuti, formula idzakhala motere: Gawo/% = Ndalama zonse
3. Muchitsanzo chathu, gawo loyamba lili ndi mtengo wa laputopu, ndipo gawo lachiwiri lili ndi gawo lomaliza la mtengo woyambirira womwe tidalipira. Chifukwa chake, chotsatira chomaliza chimalembedwa mugawo lachitatu, mu selo loyamba pambuyo pa mutu womwe timalemba fomula. =A2/B2 ndikusintha mawonekedwe a cell kukhala peresenti.

Chifukwa chake, mtengo wa laputopu wopanda kuchotsera unali madola 571,43.

Kusintha mtengo ndi peresenti

Nthawi zambiri timafunika kusintha nambala ndi gawo linalake. Kodi kuchita izo? Ntchitoyi ikhoza kuchitidwa pogwiritsa ntchito fomula =Mtengo*(1+%). Muyenera kuyika zofunikira m'malo oyenera, ndipo cholinga chimakwaniritsidwa.

Peresenti ya ntchito mu Excel

M'malo mwake, maperesenti ndi manambala ofanana ndi ena aliwonse, kotero mutha kuchita nawo ma masamu onse, komanso kugwiritsa ntchito ma formula. Chifukwa chake, lero tazindikira mawonekedwe akugwira ntchito ndi magawo mu Excel. Makamaka, tamvetsetsa momwe tingawerengere kuwonjezeka kwa chiwerengero, komanso momwe tingawonjezere chiwerengerocho ndi chiwerengero china.