Kuyerekeza mafayilo a 2 mu Excel pazosiyana

Wogwiritsa ntchito aliyense angakumane ndi vuto lomwe angafunikire kufananiza matebulo awiri. Chabwino, monga njira yomaliza, aliyense ayenera kufananiza mizati iwiri. Inde, inde, kugwira ntchito ndi mafayilo a Excel ndikosavuta komanso kosavuta. Pepani, uku sikufananitsa. Zachidziwikire, kusanja kowoneka kwa tebulo laling'ono ndikotheka, koma kuchuluka kwa ma cell kukafika masauzande ambiri, muyenera kugwiritsa ntchito zida zowonjezera zowunikira.

Tsoka ilo, wand yamatsenga sinatsegulidwe yomwe imakupatsani mwayi wofananiza zidziwitso zonse ndikudina kamodzi. Chifukwa chake, muyenera kugwira ntchito, mwachitsanzo, kusonkhanitsa deta, tchulani mafomu ofunikira ndikuchita zina zomwe zimakupatsani mwayi wofananirako pang'ono.

Pali zambiri zochita zoterezi. Tiyeni tione ena mwa iwo.

Kodi cholinga chofanizira mafayilo a Excel ndi chiyani?

Pakhoza kukhala zifukwa zambiri zomwe mafayilo angapo a Excel amafaniziridwa. Posachedwapa, aliyense wogwiritsa ntchito amakumana ndi vuto lotere, ndipo alibe mafunso otero. Mwachitsanzo, mungafune kufananiza deta kuchokera ku malipoti awiri a magawo osiyanasiyana kuti muwone ngati ndalama zakwera kapena kutsika.

Kapenanso, mphunzitsiyo akuyenera kuona kuti ndi ophunzira ati omwe adathamangitsidwa kuyunivesite pofanizira gulu la ophunzira chaka chatha ndi chaka chino.

Pakhoza kukhala chiwerengero chachikulu cha zochitika zoterezi. Koma tiyeni tipitirire kuchita, chifukwa mutuwo ndi wovuta kwambiri.

Njira Zonse Zofananizira Matebulo a 2 mu Excel

Ngakhale kuti mutuwu ndi wovuta, ndi wosavuta. Inde, musadabwe. Ndizovuta chifukwa zimapangidwa ndi zigawo zambiri. Koma mbali zimenezi paokha n'zosavuta kumva ndi kuchita. Tiyeni tiwone momwe mungafananizire mapepala awiri a Excel, mwachindunji muzochita.

Fomula Yofanana ndi Mayeso Abodza-Zoona

Tiyeni tiyambe, ndithudi, ndi njira yosavuta. Njira iyi yofananira zikalata ndizotheka, komanso mkati mwamitundu yambiri. Simungathe kufanizitsa malemba okha, komanso manambala. Ndipo tiyeni titenge chitsanzo chaching'ono. Tiyerekeze kuti tili ndi mizere iwiri yokhala ndi ma cell amtundu wa manambala. Kuti muchite izi, ingolembani chilinganizo chofanana =C2=E2. Zikapezeka kuti ndi ofanana, "CHOONADI" chidzalembedwa mu cell. Ngati asiyana, ndiye ZABODZA. Pambuyo pake, muyenera kusamutsa fomulayi kumtundu wonsewo pogwiritsa ntchito chikhomo cha autocomplete.

Tsopano kusiyana kukuwonekera ndi maso.

Kuwonetsa Makhalidwe Osiyana

Mutha kupanganso zikhalidwe zomwe zimasiyana zikuwonekera mumtundu wapadera. Ilinso ndi ntchito yophweka kwambiri. Ngati ndikwanira kuti mupeze kusiyana pakati pamitundu iwiri yamitengo kapena matebulo athunthu, muyenera kupita ku tabu ya "Home", ndikusankha "Pezani ndikuwunikira" pamenepo. Musanayidina, onetsetsani kuti mwawunikira ma cell omwe amasunga zambiri kuti mufananize. 

Pazosankha zomwe zikuwoneka, dinani pa menyu "Sankhani gulu la ma cell ...". Kenako, zenera lidzatsegulidwa momwe tiyenera kusankha kusiyana ndi mizere ngati muyeso.

Kufananiza ma tebulo awiri pogwiritsa ntchito masanjidwe okhazikika

Kupanga koyenera ndi njira yabwino kwambiri komanso yofunika kwambiri, yothandiza yomwe imakupatsani mwayi wosankha mtundu womwe ungawonetse mtengo wosiyana kapena wofanana. Mutha kupeza izi pa tabu Yanyumba. Kumeneko mungapeze batani lokhala ndi dzina loyenera ndipo pamndandanda womwe umawonekera, sankhani "Sinthani malamulo". Woyang'anira malamulo adzawonekera, momwe tiyenera kusankha "Pangani Rule".

Kenako, kuchokera pamndandanda wazomwe tikuyenera kusankha, tiyenera kusankha yomwe imati tigwiritse ntchito fomula kuti tidziwe ma cell omwe apangidwe mwapadera. Pofotokozera lamuloli, muyenera kufotokoza ndondomeko. Kwa ife, izi ndi = $ C2 <> $ E2, pambuyo pake timatsimikizira zochita zathu mwa kukanikiza batani la "Format". Pambuyo pake, timayika maonekedwe a selo ndikuwona ngati timakonda kudzera pawindo lapadera la mini ndi chitsanzo. 

Ngati zonse zikuyenda bwino, dinani batani "Chabwino" ndikutsimikizira zomwe zikuchitika.

Mu Conditional Formatting Rules Manager, wosuta atha kupeza malamulo onse okonza omwe ali m'chikalatachi. 

COUNTIF ntchito + malamulo ofananitsa tebulo

Njira zonse zomwe tazifotokozera kale ndizabwino kwa mafomu omwe mawonekedwe awo ndi ofanana. Ngati matebulo sanalamulidwe kale, ndiye njira yabwino kwambiri ndikufanizira matebulo awiri pogwiritsa ntchito ntchitoyi COUNTIF ndi malamulo. 

Tiyerekeze kuti tili ndi mipata iwiri yokhala ndi chidziwitso chosiyana pang'ono. Timayang'anizana ndi ntchito yowafanizira ndikumvetsetsa kuti ndi phindu liti lomwe ndi losiyana. Choyamba muyenera kusankha pagulu loyamba ndikupita ku tabu "Home". Pamenepo timapeza chinthu chodziwika bwino "Conditional Formatting". Timapanga lamulo ndikuyika lamulo loti tigwiritse ntchito ndondomeko. 

Muchitsanzo ichi, chilinganizochi chikuwonetsedwa pachithunzichi.

Pambuyo pake, timayika mawonekedwe monga momwe tafotokozera pamwambapa. Ntchitoyi imagawa mtengo womwe uli mu selo C1 ndikuyang'ana mndandanda womwe wafotokozedwa mu fomula. Imafanana ndi gawo lachiwiri. Tiyenera kutenga lamulo ili ndikulikopera pamtundu wonse. Hooray, ma cell onse omwe ali ndi zikhalidwe zosabwereza amawunikidwa.

VLOOKUP imagwira ntchito kuti mufananize matebulo awiri

Mu njira iyi, tikambirana ntchito VPR, yomwe imayang'ana ngati pali machesi m'matebulo awiri. Kuti muchite izi, muyenera kuyika chilinganizo chomwe chili pachithunzichi ndikusamutsira kumtundu wonse womwe umagwiritsidwa ntchito poyerekeza.

Ntchitoyi imabwereza mtengo uliwonse ndikuwona ngati pali zobwereza kuchokera pamzati woyamba mpaka wachiwiri. Chabwino, mutatha kuchita ntchito zonse, mtengo uwu umalembedwa mu selo. Ngati palibe, ndiye kuti timapeza cholakwika cha #N/A, chomwe ndi chokwanira kuti timvetsetse phindu lomwe silingafanane.

Ngati ntchito

logic ntchito NGATI - iyi ndi njira ina yabwino yofananizira mitundu iwiri. Chinthu chachikulu cha njirayi ndikuti mutha kugwiritsa ntchito gawo lokhalo lomwe likuyerekezedwa, osati tebulo lonse. Izi zimasunga zothandizira pakompyuta komanso wogwiritsa ntchito.

Tiyeni titenge chitsanzo chaching’ono. Tili ndi mizati iwiri - A ndi B. Tiyenera kufanizitsa zina mwa izo ndi wina ndi mzake. Kuti tichite izi, tiyenera kukonzekera gawo lina lautumiki C, momwe ndondomeko yotsatirayi yalembedwa.

Kugwiritsa ntchito mankhwala osokoneza bongo IF, IFERROR и ZAMBIRI ZOVUTIKA mutha kubwereza zinthu zonse zomwe mukufuna pagawo A, kenako mugawo B. Ngati zidapezeka m'mizere B ndi A, zimabwezeretsedwanso ku cell yofananira.

VBA macro

A macro ndizovuta kwambiri, komanso njira yapamwamba kwambiri yofananizira matebulo awiri. zosankha zina zofananitsa nthawi zambiri sizingatheke popanda zolemba za VBA. Iwo amakulolani kuti automate ndondomeko ndi kusunga nthawi. Ntchito zonse zofunika pokonzekera deta, ngati zakonzedwa kamodzi, zidzapitiriza kuchitidwa.

Kutengera vuto lomwe liyenera kuthetsedwa, mutha kulemba pulogalamu iliyonse yomwe ikufanizira deta popanda kulowererapo kwa ogwiritsa ntchito.

Momwe mungafananizire mafayilo mu Excel

Ngati wogwiritsa ntchitoyo adadziyika yekha ntchitoyo (chabwino, kapena wapatsidwa imodzi) kuti afanizire mafayilo awiri, ndiye kuti izi zikhoza kuchitika ndi njira ziwiri nthawi imodzi. Yoyamba ikugwiritsa ntchito ntchito yapadera. Kuti mugwiritse ntchito njirayi, tsatirani malangizo:

1. Tsegulani mafayilo omwe mukufuna kufananitsa.
2. Tsegulani tabu "Onani" - "Window" - "Onani mbali ndi mbali".

Pambuyo pake, mafayilo awiri adzatsegulidwa mu chikalata chimodzi cha Excel.

Zomwezo zitha kuchitika ndi zida wamba za Windows. Choyamba muyenera kutsegula owona awiri osiyana mazenera. Pambuyo pake, tengani zenera limodzi ndikulikokera kumanzere kwenikweni kwa chinsalu. Pambuyo pake, tsegulani zenera lachiwiri ndikulikokera kumanja kwambiri. Pambuyo pake, mazenera awiriwo adzakhala mbali ndi mbali. 

Mapangidwe okhazikika kuti mufananize mafayilo 2 apamwamba

Nthawi zambiri kufananiza zikalata kumatanthauza kuziwonetsa moyandikana. Koma nthawi zina, ndizotheka kusintha izi pogwiritsa ntchito masanjidwe okhazikika. Ndi izo, mukhoza kufufuza ngati pali kusiyana pakati pa mapepala. Izi zimakupatsani mwayi wosunga nthawi yomwe mungagwiritse ntchito pazinthu zina.

Choyamba, tiyenera kusamutsa mapepala oyerekeza kukhala chikalata chimodzi. 

Kuti muchite izi, muyenera dinani kumanja pa pepala loyenera, ndiyeno dinani batani la "Sungani kapena kukopera" pamenyu yowonekera. Kenako, bokosi la zokambirana lidzawoneka momwe wogwiritsa ntchito angasankhe chikalata chomwe pepalali liyenera kuyikidwamo.

Kenako, muyenera kusankha ma cell onse omwe mukufuna kuti muwonetse kusiyana konse. Njira yosavuta yochitira izi ndikudina batani lakumanzere lakumanzere, ndikukanikiza makiyi ophatikizira Ctrl + Shift + End.

Pambuyo pake, pitani ku zenera lokhazikika ndikupanga lamulo latsopano. Monga muyezo, timagwiritsa ntchito chilinganizo choyenera pazochitika zinazake, kenako timayika mawonekedwe.

chisamaliro: maadiresi a maselo ayenera kuwonetsedwa omwe ali pa pepala lina. Izi zitha kuchitika kudzera pa menyu yolowetsa fomula.

Kuyerekeza deta mu Excel pamasamba osiyanasiyana

Tiyerekeze kuti tili ndi mndandanda wa antchito omwe amalembanso malipiro awo. Mndandandawu umasinthidwa mwezi uliwonse. Mndandandawu wakopedwa kutsamba latsopano.

Tiyerekeze kuti tikuyenera kufananiza malipiro. Pankhaniyi, mutha kugwiritsa ntchito matebulo ochokera pamapepala osiyanasiyana ngati data. Tidzagwiritsa ntchito masanjidwe azikhalidwe kuti tiwonetse kusiyana. Zonse ndi zophweka.

Ndi masanjidwe okhazikika, mutha kufananitsa bwino ngakhale mayina a antchito ali mosiyanasiyana.

Momwe mungafananizire masamba 2 mu Excel spreadsheet

Kuyerekeza kwa chidziwitso chomwe chili pamasamba awiri chikuchitika pogwiritsa ntchito ntchitoyi ZAMBIRI ZOVUTIKA. Monga gawo lake loyamba, pali zinthu ziwiri zomwe muyenera kuyang'ana pa pepala lomwe limayang'anira mwezi wamawa. Mwachidule, March. Titha kusankha mtundu womwe umawonedwa ngati gulu la maselo omwe ali m'gulu lamagulu otchulidwa, ophatikizidwa awiriawiri.

Kotero inu mukhoza kufananitsa zingwe malinga ndi njira ziwiri - dzina lomaliza ndi malipiro. Chabwino, kapena china chilichonse, chofotokozedwa ndi wogwiritsa ntchito. Pamachesi onse omwe angapezeke, nambala imalembedwa mu cell momwe fomula imalowetsedwa. Kwa Excel, mtengo uwu udzakhala wowona nthawi zonse. Choncho, kuti masanjidwewo agwiritsidwe ntchito pamaselo omwe anali osiyana, muyenera kusintha mtengowo KUnama, kugwiritsa ntchito =OSATI().

Chida Chofananitsa ndi Spreadsheet

Excel ili ndi chida chapadera chomwe chimakulolani kuti mufanizire ma spreadsheets ndikuwunikira zosintha zokha. 

Ndikofunika kuzindikira kuti chida ichi chimapezeka kwa ogwiritsa ntchito okha omwe agula maofesi aofesi a Professional Plus.

Mukhoza kutsegula mwachindunji kuchokera pa "Home" tabu posankha chinthu cha "Yerekezerani Mafayilo".

Pambuyo pake, bokosi la zokambirana lidzawoneka momwe muyenera kusankha buku lachiwiri la bukhu. Mukhozanso kulemba adiresi ya intaneti kumene kuli bukhuli.

Tikasankha mitundu iwiri ya chikalatacho, tiyenera kutsimikizira zochita zathu ndi kiyi ya OK.

Nthawi zina, cholakwika chikhoza kuchitika. Ngati zikuwoneka, zitha kuwonetsa kuti fayiloyo ili ndi mawu achinsinsi. Mukadina Chabwino, mudzafunsidwa kuti mulowetse. 

Chida chofananizira chimawoneka ngati maspredishithi awiri a Excel pafupi ndi wina ndi mnzake pawindo lomwelo. Malingana ndi ngati chidziwitso chawonjezeredwa, chachotsedwa, kapena pakhala kusintha kwa ndondomeko (komanso mitundu ina ya zochita), zosinthazo zimawonekera mumitundu yosiyanasiyana. 

Momwe mungatanthauzire zotsatira zofananitsa

Ndi zophweka: mitundu yosiyanasiyana ya kusiyana imasonyezedwa ndi mitundu yosiyanasiyana. Kupanga kungapitirire mpaka kudzaza kwa selo ndi malembawo. Chifukwa chake, ngati deta idalowetsedwa muselo, ndiye kuti kudzaza kumakhala kobiriwira. Ngati chinachake sichidziwika bwino, ntchitoyo yokha imakhala ndi zizindikiro zomwe zimasonyeza mtundu wa kusintha womwe umawonetsedwa mu mtundu wanji.