Kuphwanya mzere mu Excel. Momwe mungadulire mzere mu cell ya Excel - njira zonse

Nthawi zambiri anthu omwe amagwira ntchito mu pulogalamu ya Excel spreadsheet amakumana ndi vuto lomwe amafunikira kukulunga mzere. Mukhoza kugwiritsa ntchito njira yosavutayi m'njira zosiyanasiyana. M'nkhaniyi, tidzasanthula mwatsatanetsatane njira zonse zomwe zimakulolani kusamutsa mzere pa malo ogwirira ntchito a chikalata cha spreadsheet.

Momwe mungachotsere zopumira zama cell mu Excel 2013, 2010 ndi 2007

Pali njira zitatu zoyendetsera kuchotsedwa kwa zobwerera zamagalimoto kuchokera kuminda. Ena a iwo amagwiritsa ntchito m'malo mwa zilembo zophwanya mzere. Zosankha zomwe zafotokozedwa pansipa zimagwiranso ntchito mofanana m'matembenuzidwe ambiri a spreadsheet editor.

Kukulunga kwa mzere m'mawu olembedwa kumachitika pazifukwa zosiyanasiyana. Zifukwa zodziwika bwino zimaphatikizapo zinthu monga kugwiritsa ntchito njira yachidule ya kiyibodi ya Alt+Enter, komanso kusamutsa deta kuchokera patsamba lawebusayiti kupita kumalo ogwirira ntchito a spreadsheet. Tiyenera kuchotsa kubwereranso kwangoloyo, chifukwa popanda njirayi ndizosatheka kukhazikitsa kusaka kwabwino kwa mawu enieni.

Zofunika! Poyambirira, mawu akuti "Line feed" ndi "Cariage return" adagwiritsidwa ntchito popanga makina osindikizira ndipo amatanthawuza zochita ziwiri. Makompyuta aumwini adapangidwa poganizira ntchito za makina osindikizira.

Kuchotsa zobwerera zamagalimoto pamanja

Tiyeni tiwunike njira yoyamba mwatsatanetsatane.

• Ubwino: kupha mwachangu.
• Zoipa: Kupanda zina zowonjezera.

Malangizo atsatanetsatane amawoneka motere:

1. Timasankha ma cell onse momwe ndikofunikira kuti tigwiritse ntchito izi kapena kusintha zilembo.

2. Pogwiritsa ntchito kiyibodi, yesani kuphatikiza kiyi "Ctrl + H". Zenera lotchedwa "Pezani ndi Kusintha" linawonekera pazenera.
3. Timayika cholozera pamzere "Pezani". Pogwiritsa ntchito kiyibodi, yesani kuphatikiza kiyi "Ctrl + J". Pali kadontho kakang'ono pamzere.
4. Pamzere wakuti "Bwezerani ndi" timalowetsa mtengo womwe udzalowe m'malo mwa zobweza zonyamula katundu. Nthawi zambiri, danga limagwiritsidwa ntchito, chifukwa limakupatsani mwayi kuti musankhe mawu awiri oyandikana nawo. Kuti mugwiritse ntchito kuchotsa kukulunga kwa mzere, mzere wa "Replace with" sayenera kudzazidwa ndi chidziwitso chilichonse.

5. Pogwiritsa ntchito LMB, dinani "Bwezerani Zonse". Okonzeka! Takhazikitsa zochotsa zonyamula katundu.

Chotsani zoduka mizere pogwiritsa ntchito ma fomula a Excel

• Ubwino: kuthekera kogwiritsa ntchito mitundu yosiyanasiyana yomwe imatsimikizira zovuta zamawu pamawu osankhidwa. Mwachitsanzo, mutha kugwiritsa ntchito kuchotsa zobwerera zamagalimoto, ndiyeno kupeza malo osafunika.
• Zoyipa: muyenera kupanga ndime yowonjezera, komanso kuchita zinthu zambiri zosokoneza.

Malangizo atsatanetsatane amawoneka motere:

1. Tiyeni tigwiritse ntchito kuwonjezera gawo lina kumapeto kwa chidziwitso choyambirira. Mu chitsanzo ichi, idzatchedwa "1 mzere"
2. M'gawo loyamba la gawo lowonjezera (C1), timayendetsa munjira yomwe imathandizira kuchotsa kapena kusinthanitsa mizere. Njira zingapo zimagwiritsidwa ntchito pochita izi. Fomula yoyenera kugwiritsidwa ntchito limodzi ndi kuphatikizika kwa chobweza chobweza ndi mizere ikuwoneka motere: =KUSINTHA(MALOWA(B2,CHAR(13),””);CHAR(10),””).
3. Njira yoyenera yosinthira mzere woduka ndi zilembo zimawoneka motere: =TRIMSPACES(MALOWA(BWINO(B2,CHAR(13)),””);CHAR(10);”, “). Ndikoyenera kudziwa kuti pankhaniyi sipadzakhala kuphatikiza mizere.
4. Njira yochotsera zilembo zonse zomwe sizingasindikizidwe palemba limawoneka motere: = CHOYERA(B2).

5. Timakopera fomula, ndikuyiyika mu selo lililonse lazagawo lowonjezera.
6. Kuphatikiza apo, mutha kusintha gawo loyambirira ndi latsopano, momwe zodulira mizere zidzachotsedwa.
7. Timasankha maselo onse omwe ali mu gawo C. Timagwiritsira ntchito "Ctrl + C" kuphatikiza pa kiyibodi kuti tigwiritse ntchito kukopera zambiri.
8. Timasankha gawo B2. Dinani kuphatikiza kiyi "Shift + F10". Pamndandanda wawung'ono womwe ukuwoneka, dinani LMB pachinthu chomwe chili ndi dzina "Ikani".
9. Tiyeni tigwiritse ntchito kuchotsa gawo lothandizira.

Chotsani zotsalira za mzere ndi VBA macro

• Ubwino: kulenga kumachitika kamodzi kokha. M'tsogolomu, macro angagwiritsidwe ntchito m'mapepala ena a spreadsheet.
• Zoyipa: Muyenera kumvetsetsa momwe chilankhulo cha pulogalamu ya VBA chimagwirira ntchito.

Kuti mugwiritse ntchito njirayi, muyenera kulowa pawindo kuti mulowe ma macros ndikulowetsamo nambala iyi:

Manga mawu mu selo

Mkonzi wa spreadsheet Excel amakulolani kusamutsa mauthenga kumunda. Izi zachitika kuti malemba deta kuwonetsedwa pa mizere ingapo. Mutha kupanga njira yokhazikitsira gawo lililonse kuti kutumiza zolemba kumangochitika zokha. Komanso, mukhoza kukhazikitsa mzere yopuma pamanja.

Kukulunga mawu mokha

Tiyeni tiwunike mwatsatanetsatane momwe tingagwiritsire ntchito kusamutsidwa kwa malemba. Algorithm ya sitepe ndi sitepe ikuwoneka motere:

1. Timasankha cell yofunika.
2. Mu gawo la "Home" timapeza malamulo otchedwa "Kulinganiza".
3. Pogwiritsa ntchito LMB, sankhani chinthu cha "Move Text".

Zofunika! Zomwe zili m'maselo zidzasamutsidwa poganizira kukula kwa gawolo. Kusintha m'lifupi m'lifupi kudzasintha zokha kuzimata kwa data. 

Sinthani kutalika kwa mzere kuti muwonetse zolemba zonse

Tiyeni tiwunike mwatsatanetsatane momwe tingagwiritsire ntchito njira yosinthira kutalika kwa mzere kuti tiwonetse zolemba zonse. Malangizo atsatanetsatane amawoneka motere:

1. Timasankha ma cell omwe tikufuna.
2. Mugawo la "Home" timapeza malamulo otchedwa "Maselo".
3. Pogwiritsa ntchito LMB, sankhani chinthu cha "Format".
4. Mubokosi la "Kukula kwa Ma cell", muyenera kuchita chimodzi mwazomwe zafotokozedwa pansipa. Njira yoyamba - kugwirizanitsa kutalika kwa mzere, dinani LMB pa "Auto-fit line height". Njira yachiwiri ndikukhazikitsa pamanja kutalika kwa mzere podina chinthu cha "Line height", kenako ndikulowetsa chizindikiro chomwe mukufuna mumzere wopanda kanthu.

Kuyika malire a mzere

Tiyeni tiwunike mwatsatanetsatane momwe tingagwiritsire ntchito ndondomeko yolowera pamzere wopuma. Malangizo atsatanetsatane amawoneka motere:

1. Mwa kuwonekera kawiri LMB, timasankha gawo lomwe tikufuna kuyendetsako kudutsa mzere. Ndikofunikira kudziwa kuti mutha kusankha gawo lomwe mukufuna, ndiyeno dinani "F2".
2. Mwa kuwonekera kawiri LMB, timasankha malo omwe mzerewo udzawonjezedwa. Dinani kuphatikiza Alt + Enter. Okonzeka!

Momwe mungadulire mzere mu cell ya Excel ndi fomula

Nthawi zambiri, ogwiritsa ntchito ma spreadsheet amawonjezera ma chart ndi ma graph osiyanasiyana kumalo ogwirira ntchito. Nthawi zambiri, njirayi imafuna kukulunga mizere muzolemba za m'munda. Tiyeni tiwone mwatsatanetsatane momwe tingagwiritsire ntchito mphindi ino.

Fomula yokulunga mizere mu ma cell a Excel

Mwachitsanzo, tili ndi histogram yomwe yakhazikitsidwa mu pulogalamu ya spreadsheet. X-axis ili ndi mayina a antchito, komanso zambiri zamalonda awo. Siginecha yamtunduwu ndi yabwino kwambiri, chifukwa ikuwonetsa bwino kuchuluka kwa ntchito zomwe antchito amazichita.

Ndikosavuta kugwiritsa ntchito njirayi. Ndikofunikira kuwonjezera woyendetsa SYMBOL m'malo mwa fomula. Imakulolani kuti mugwiritse ntchito m'badwo wa zizindikiro m'minda kuti musayine zambiri pazithunzi.

Zachidziwikire, m'munda, mutha kugwiritsa ntchito njira yokulunga mizere kulikonse, chifukwa cha kuphatikiza mabatani a Alt + Enter. Komabe, njirayi ndi yovuta ngati pali deta yambiri.

Momwe CHAR imagwirira ntchito pomanga mizere mu cell

Pulogalamuyi imagwiritsa ntchito ma code kuchokera pa tebulo la zilembo za ASCII. Lili ndi zizindikiro za zilembo zomwe zikuwonetsedwa pazowonetsera mu OS. Phaleti ili ndi ma code mazana awiri ndi makumi asanu ndi asanu.

Wogwiritsa ntchito tebulo yemwe amadziwa manambalawa amatha kuwagwiritsa ntchito mu CHAR kuti akwaniritse kuyika kwa munthu aliyense. Muchitsanzo chomwe takambirana pamwambapa, kusweka kwa mzere kumawonjezeredwa, komwe kumalumikizidwa mbali zonse za "&" pakati pa zizindikiro za minda C2 ndi A2. Ngati mawonekedwe otchedwa "Sungani malemba" sakutsegulidwa m'munda, ndiye kuti wogwiritsa ntchitoyo sangazindikire kukhalapo kwa chizindikiro chopuma. Izi zitha kuwoneka pachithunzi pansipa:

Ndizofunikira kudziwa kuti pama chart osiyanasiyana, zodulira mizere zowonjezeredwa pogwiritsa ntchito fomula zidzawonetsedwa mwanjira yokhazikika. Mwa kuyankhula kwina, mzere wa malembawo udzagawanika kukhala 2 kapena kuposerapo.

Gawani m'mipingo poduka mizere

Ngati wogwiritsa ntchito mugawo la "Data" asankha chinthu cha "Text by columns", ndiye kuti azitha kugwiritsa ntchito kusamutsa mizere ndikugawa chidziwitso m'maselo angapo. Njirayi ikuchitika pogwiritsa ntchito kuphatikiza kwa Alt + Enter. Mu bokosi la "Wizard of text distribution by columns", muyenera kuyang'ana bokosi pafupi ndi "zina" ndikulowetsani kuphatikiza "Ctrl + J".

Ngati muyang'ana bokosi pafupi ndi mawu akuti "Olekanitsa monga amodzi", ndiye kuti mutha kukhazikitsa "kugwa" kwa mizere ingapo motsatizana. Pamapeto pake, dinani "Next". Chifukwa chake, tipeza:

Gawani mizere ndi Alt + Enter kudzera mu Power Query

Pali nthawi zina pomwe wogwiritsa ntchito amafunika kugawa zidziwitso zamizere yambiri osati m'mizere, koma m'mizere.

Kuti mugwiritse ntchito njirayi, chowonjezera cha Power Query, chomwe chawonekera mu mkonzi wa spreadsheet kuyambira 2016, ndichabwino. Malangizo atsatanetsatane amawoneka motere:

1. Pogwiritsa ntchito kuphatikiza kwa "Ctrl + T", timatembenuza magwero kukhala mbale "yanzeru". Njira ina ndikusunthira kugawo la "Home" ndikudina LMB pa "Format as table".
2. Pitani ku gawo la "Data" ndikudina "Kuchokera pa Table/Range". Izi zidzalowetsa mbale mu chida cha Power Query.

3. Timasankha ndime yokhala ndi zolemba zambiri. Timapita ku gawo la "Home". Wonjezerani mndandanda wa chizindikiro cha "Split column" ndikudina LMB pa chinthu cha "By separator".

4. Dinani "Chabwino" kutsimikizira zosintha zomwe zachitika. Okonzeka!

Macro yogawidwa kukhala mizere ndi Alt + Enter

Tiyeni tiwone momwe tingachitire izi pogwiritsa ntchito ma macro apadera. Timatsegula VBA pogwiritsa ntchito makiyi a Alt + F11 pa kiyibodi. Pazenera lomwe likuwoneka, dinani "Ikani", kenako "Module". Apa tikuwonjezera nambala iyi:

Timabwerera kumalo ogwirira ntchito ndikusankha minda yomwe chidziwitso cha multiline chilipo. Dinani kuphatikiza "Alt + F8" pa kiyibodi kuti mutsegule macro opangidwa.

Mawuwo

Kutengera zomwe zalembedwa m'nkhaniyi, mutha kuwona kuti pali njira zambiri zogwiritsira ntchito kukulunga mizere mu chikalata cha spreadsheet. Mutha kuchita izi pogwiritsa ntchito ma formula, ogwira ntchito, zida zapadera ndi ma macros. Wogwiritsa ntchito aliyense azitha kusankha njira yabwino kwambiri kwa iye yekha.