Kuwerengera kosiyana mu Microsoft Excel

Kuwerengera kusiyana ndi imodzi mwazochita zotchuka kwambiri masamu. Koma kuwerengera uku sikugwiritsidwa ntchito mu sayansi zokha. Timapitiliza kuchita izi, popanda kuganiza, m'moyo watsiku ndi tsiku. Mwachitsanzo, pofuna kuwerengetsa zosintha kuchokera kugula m'sitolo, kuwerengetsa komwe kumapeza kusiyana pakati pa kuchuluka komwe wogula anapatsa wogulitsa ndi kufunika kwa katunduyo kumagwiritsidwanso ntchito. Tiyeni tiwone momwe mungawerengere kusiyana kwa Excel mukamagwiritsa ntchito mawonekedwe osiyanasiyana.

Kuwerengera kwakusiyana

Poganizira kuti Excel imagwira ntchito ndi mitundu yosiyanasiyana ya deta, pochotsa phindu limodzi kuchokera kwina, mafomula osiyanasiyana amagwiritsidwa ntchito. Pazonse, onse amatha kukhala amtundu umodzi:

X = A-B

Ndipo tsopano tiyeni tiwone momwe tingachotsere malingaliro amitundu yosiyanasiyana: manambala, ndalama, tsiku ndi nthawi.

Njira 1: Kutengera Manambala

Nthawi yomweyo tiyeni tiwone njira yomwe ingagwiritsidwe ntchito kuwerengetsa kusiyana, komwe ndiko kutulutsa kwa manambala. Pazifukwa izi, ku Excel mutha kuyika chizolowezi chamawonekedwe ndi chizindikiro "-".

1. Ngati mukufunikira kuthana ndi manambala mwachizolowezi pogwiritsa ntchito Excel monga Calculator, ndiye ikani chizindikirocho "=". Kenako, chizindikirochi chitangolemba, lembani nambala yomwe yachepetsedwa kuchokera ku kiyibodi, ikani chizindikiro "-"kenako lembani zopereka. Ngati pali zopereka zingapo, ndiye kuti muyenera kuyikanso chizindikiro "-" ndipo lembani nambala yomwe mukufuna. Njira yosinthira chizindikiro cha masamu ndi manambala ziyenera kuchitika mpaka onse omwe atengedwera kale alowe. Mwachitsanzo, kuchokera 10 chotsa 5 ndi 3, muyenera kulemba mafomu awa mu chinthu chapa Excel:

   =10-5-3

   Mutatha kujambula mawuwo, kuti muwonetse zotsatira za kuwerengera, dinani batani Lowani.



2. Monga mukuwonera, zotsatira zake zikuwonetsedwa. Ndikofanana ndi manambala 2.

Koma nthawi zambiri, njira yochotsera mu Excel imagwiritsidwa ntchito pakati pa manambala omwe amaikidwa m'maselo. Nthawi yomweyo, kusintha kwa masamu pawokha sikungasinthe, pokhapokha m'malo mwa manambala enieni, maumboni amaperekedwa ku maselo komwe amapezeka. Zotsatira zake zikuwonetsedwa mu pepala lopanda pepala, pomwe chizindikiro chimayikidwa. "=".

Tiyeni tiwone momwe angawerengere kusiyana pakati pa manambala 59 ndi 26ili ndendende mu zinthu za pepala ndi zogwirizira A3 ndi C3.

1. Timasankha chinthu chopanda kanthu mbuku momwe timafunira kuti tiwonetse zotsatira za kuwerengera kusiyana kwake. Tidayikamo chizindikiro "=". Pambuyo pake, dinani pafoni A3. Timayika chizindikiro "-". Kenako, dinani pa pepala. C3. Mu pepala kuti mutulutse zotsatirazo, mafomula akuyenera kuwoneka:

   = A3-C3

   Monga momwe zinalili kale, kuti muwonetse zotsatira pazenera, dinani batani Lowani.



2. Monga mukuwonera, pankhaniyi, kuwerengera kunachita bwino. Zotsatira zake zowerengera ndizofanana ndi chiwerengerocho 33.

Koma, m'malo mwake, zina zimafunikira kuchita chotsitsa, momwe ziwerengerozo zimalumikizira zokha komanso zolumikizana ndi maselo komwe zimakhalapo zidzatengapo gawo. Chifukwa chake, mukuyenera kukumana ndi mawu, mwachitsanzo, awa:

= A3-23-C3-E3-5

Phunziro: Momwe mungachotsere nambala kuchokera ku nambala ku Excel

Njira 2: mtundu wa ndalama

Kuwunika kwa mitundu mwazachuma sikusiyana ndi manambala. Maluso omwewo amagwiritsidwa ntchito, popeza, mwanjira yayikulu, njira iyi ndi imodzi mwazosankha zamanambala. Kusiyanitsa kokhako ndikuti kumapeto kwa kuchuluka komwe kumakhudzidwa kuwerengera, chizindikiro cha ndalama chomwe chimayikidwa.

1. Kwenikweni, mutha kuchita opareshoniyo, monga kuchotsa kwachizolowezi kwamanambala, pokhapokha mutapanga zotsatira zomaliza za ndalama. Chifukwa chake, tikuchita kuwerengera. Mwachitsanzo, chotsani ku 15 kuchuluka 3.



2. Pambuyo pake, timadina pa pepala lomwe lili ndi zotsatirapo. Pazosankha, sankhani phindu "Mtundu wamtundu ...". M'malo moyitanitsa menyu yankhaniyo, mutha kuyika ma keytrok atasankhidwa Ctrl + 1.



3. Ndi chimodzi mwazosankha ziwirizi, kuwonekera kwawindo kukhazikitsidwa. Timasunthira ku gawo "Chiwerengero". Mu gululi "Mawerengero Amanambala" njira iyenera kudziwika "Ndalama". Nthawi yomweyo, magawo apadera adzawoneka mu mawonekedwe oyenera mawonekedwe a zenera momwe mungasankhire mtundu wamtundu wa ndalama ndi kuchuluka kwa malo otsatsa. Ngati muli ndi Windows mwapadera komanso Microsoft Office, makamaka ku Russia, ndiye kuti ayenera kukhala mumizere "Maudindo" chizindikiro cha ruble, ndipo m'masamba angapo "2". Mwambiri, zosintha izi sizifunika kusintha. Koma, ngati mukufunikirabe kuwerengera m'madola kapena opanda maimenti, ndiye muyenera kusintha zomwe mukufunikira.

   Pambuyo pakusintha konse kofunikira, dinani "Zabwino".



4. Monga mukuwonera, zotsatira zakuchotsera mu chipindacho zidasinthidwa kukhala ndalama ndi nambala yokhazikika ya malo osinthika.

Pali njira inanso yosinthira zotsatira za kuchotsera ndalama. Kuti muchite izi, pa riboni pa tabu "Pofikira" dinani patatu mpaka kumanja kwa malo owonetsera omwe ali mu foni yamakono mu gulu lazida "Chiwerengero". Kuchokera pamndandanda womwe umatsegulira, sankhani njira "Ndalama". Mfundo zamanambala zimasinthidwa kukhala ndalama. Zowona, pankhani iyi palibe mwayi wosankha ndalama ndi kuchuluka kwa malo omwe amawerengetsa. Njira yomwe imakhazikitsidwa ndi kusakhazikika mu dongosololi imayikidwa, kapena kukhazikitsidwa kudzera pazenera lopangidwe lomwe tafotokozazi.

Ngati mumawerengera kusiyana pakati pa zinthu zomwe zili m'maselo omwe amapangidwira kale mawonekedwe amtundu wa ndalama, ndiye kuti kusanja mawonekedwe a pepala kuti muwonetse zotsatira sikofunikira. Idzasinthidwa modzikongoletsa kuti ikhale yoyenera pambuyo pa fomula ikalumikizidwa ndi maulalo azinthu zomwe zimatsika ndikuchepetsa manambala, komanso kudina kumapangidwira pa batani Lowani.

Phunziro: Momwe mungasinthire mawonekedwe amtundu mu Excel

Njira 3: masiku

Koma kuwerengera kwa kusiyana kwa masiku kumakhala ndi mfundo zazikulu zomwe ndizosiyana ndi zomwe zidasankhidwa kale.

1. Ngati tikufunika kuchotsa masiku angapo kuchokera tsiku lomwe lasonyezeredwa patsamba lina, ndiye kuti choyamba tikhazikitse chizindikiro "=" kwa gawo lomwe zotsatira zomaliza ziwonetsedwa. Pambuyo pake, dinani pa pepala lomwe linali tsikulo. Adilesi yake izawoneka muzotulutsa ndi mu baramu yamakanidwe. Kenako tikuika chizindikiro "-" ndikuyendetsa masiku angapo omwe atengedwe ku kiyibodi. Pofuna kupanga kuwerengera dinani Lowani.



2. Zotsatira zake zikuwonetsedwa mu khungu lomwe adatipatsa. Nthawi yomweyo, mawonekedwe ake amasinthidwa okha ngati mtundu wa deti. Chifukwa chake, timakhala ndi tsiku lenileni.

Pali zinthu zomwe zingasinthe pamene zikufunika kuchotsa zina kuchokera tsiku limodzi ndikuwona kusiyana pakati pawo masiku.

1. Khazikitsani mkhalidwe "=" muchipinda chomwe zotsatira zikuwonetsedwa. Pambuyo pake, dinani pazomwe zili papepala, zomwe zili ndi tsiku lotsatira. Pambuyo adilesi yakeyo atawonetsedwa kale, ikani chisonyezo "-". Dinani pa foni yomwe ili ndi tsiku loyambirira. Kenako dinani Lowani.



2. Monga mukuwonera, pulogalamuyo imawerengera molondola kuchuluka kwa masiku pakati pa masiku andimawa.

Kusiyana kwa masiku kungathenso kuwerengedwa pogwiritsa ntchito ntchitoyo HAND. Ndizabwino chifukwa zimakupatsani mwayi wokonzekera, mothandizidwa ndi mfundo yowonjezera, momwe magawo a muyeso amawonetsedwa: miyezi, masiku, ndi zina zambiri. Zoyipa za njirayi ndikuti kugwira ntchito ndi ntchito kumadwalirabe kuposa momwe zimakhalira kale. Kuphatikiza apo, wothandizira HAND osatchulidwa Ogwira Ntchito, chifukwa chake mudzayenera kulowa m'malo mwanu pogwiritsa ntchito syntax yotsatirayi:

= DATE (kuyamba_dikidwani; kumapeto_dongosolo; gawo)

"Tsiku loyambira" - mkangano woyimira tsiku loyambirira kapena cholumikizira chomwe chili patsamba.

Tsiku lomaliza - Uwu ndi mkangano munthawi ya tsiku lotsatila kapena mawu ake.

Kutsutsana kosangalatsa "Gulu". Ndi iyo, mutha kusankha njira yamomwe zotsatirazi zikuwonetsedwa. Itha kusinthidwa pogwiritsa ntchito mfundo zotsatirazi:

• "d" - zotsatirazi zikuwonetsedwa m'masiku;
• "m" - m'miyezi yathunthu;
• "y" - zaka zonse;
• "YD" - kusiyanasiyana kwa masiku (kupatula zaka);
• "MD" - kusiyanasiyana kwa masiku (kupatula miyezi ndi zaka);
• "Ym" - kusiyana m'miyezi yambiri.

Chifukwa chake, m'malo mwathu, tikuyenera kuwerengera kusiyana pakati pa Meyi 27 ndi Marichi 14, 2017. Madetiwa amapezeka m'maselo omwe ali ndi mgwirizano B4 ndi D4, motero. Tikuyika cholozera chilichonse papepala lopanda kanthu pomwe tikufuna kuwona zotsatira za mawerengedwa, ndikulemba njira iyi:

= HANDLE (D4; B4; "d")

Dinani Lowani ndipo pezani zotsatira zomaliza zowerengera kusiyana 74. Inde, pakati pa masiku amenewa pali masiku 74.

Ngati zikufunika kuchotsa masiku omwewo, koma osalowetsa zilembo za pepalalo, ndiye kuti tikugwiritsa ntchito njira iyi:

= HANDLE ("03/14/2017"; "05/27/2017"; "d")

Kanikizani batani kachiwiri Lowani. Monga mukuwonera, zotsatira zake zimakhala zofanana, zimangopezeka mwanjira yosiyana pang'ono.

Phunziro: Chiwerengero cha masiku pakati pa masiku ku Excel

Njira 4: nthawi

Tsopano tafika pakuphunzira za algorithm yopereka nthawi mu Excel. Mfundo zazikuluzikulu zimakhalabe chimodzimodzi ndikamachotsa masiku. Ndikofunika kuchotsa koyambirira nthawi ina.

1. Chifukwa chake, tikukumana ndi ntchito yodziwira kuti ndi mphindi zingati zadutsa kuyambira 15:13 mpaka 22:55. Timalemba izi nthawi mumaselo osiyanasiyana papepala. Chochititsa chidwi, mutalowa tsambalo, zinthuzo zimasungidwa pazokhazo ngati sizinapangidwe kale. Kupanda kutero, adzayenera kujambulidwa pamanja pa tsikulo. Mu khungu lomwe zotsatira za kugonjera ziziwonetsedwa, ikani chisonyezo "=". Kenako timadinira chinthucho pambuyo pake (22:55). Pambuyo poti adilesi yaonetsedwa mu fomula, ikani chikwangwani "-". Tsopano dinani chinthucho papepala momwe nthawi yoyamba iliri (15:13) M'malo mwathu, tili ndi kachitidwe ka mawonekedwe:

   = C4-E4

   Kuti muchite kuwerengera, dinani Lowani.



2. Koma, monga tikuwona, zotsatira zake zidawonetsedwa pang'ono momwe timafunira. Tinkangofuna kusiyana kwa mphindi, ndipo zidawoneka 7 maola 42 mphindi.

   Kuti tipeze mphindi, tiyenera kuchulukitsa zotsatira zam'mbuyomu chifukwa chokwanira 1440. Kukwanira kumeneku kumapezeka pochulukitsa kuchuluka kwa mphindi ola limodzi (60) ndi maola tsiku lililonse (24).

Chifukwa chake, ikani chizindikirocho "=" m'chipinda chopanda kanthu papepala. Pambuyo pake, timadina pa chinsalu cha pepalalicho momwe chosiyanitsira nthawiyo chili (7:42) Pambuyo pazolumikizana ndi khungu mufotokozedwe, dinani chizindikirochi chulukitsa (*) pa kiyibodi, kenako pa iyo timayang'ana nambala 1440. Kuti mupeze zotsatira, dinani Lowani.



3. Koma, monga tikuwona, zotsatira zake zidawonetsedwa molakwika (0:00) Izi ndichifukwa choti pochulukitsa, zomwe zidasinthidwa zidasinthidwa zokha kuti zikhale momwe zidasinthidwira. Kuti kusiyana kwa mphindi kuwonetseredwe, tifunika kubwezeretsa mtundu wonsewo.



4. Chifukwa chake, sankhani khungu mu tabu "Pofikira" dinani pazintatu zomwe timazidziwa kale kumanja kwa mawonekedwe owonetsedwa. Pamndandanda wokhazikitsa, sankhani njira "General".

   

   Mutha kuchita zosiyana. Sankhani zomwe zalembedwa pa pepalalo ndikusindikiza makiyi Ctrl + 1. Tsamba losintha limayamba, lomwe tachita kale nawo kale. Pitani ku tabu "Chiwerengero" ndipo mndandanda wamitundu yamagulu musankhe njira "General". Dinani "Zabwino".



5. Mukatha kugwiritsa ntchito zonsezi, cell imasinthidwa kukhala yofanana. Ziwonetsa kusiyana pakati pa nthawi yodziwika mumphindi. Monga mukuwonera, kusiyana pakati pa 15:13 ndi 22:55 ndi mphindi 462.

Phunziro: Momwe mungasinthire maora kukhala mphindi mu Excel

Monga mukuwonera, zovuta zakuwerengera kusiyana kwa Excel zimatengera deta yomwe wogwiritsa ntchito akugwira nayo. Koma, komabe, njira yayikulu yofikira pamasamuyi imakhalabe yosasinthika. Ndikofunikira kuchotsa wina ku nambala imodzi. Izi zitha kuchitika pogwiritsa ntchito njira zamasamu zomwe zimagwiritsidwa ntchito poganizira kapangidwe kapadera ka Excel, komanso kugwiritsa ntchito ntchito zomangidwa.