Momwe mungapezere ndikuchotsa zobwereza mu Excel. Njira 5 zopezera ndikuchotsa zobwereza mu Excel

Si chinsinsi kuti mu Excel nthawi zambiri mumayenera kugwira ntchito ndi matebulo akulu omwe amakhala ndi zidziwitso zambiri. Nthawi yomweyo, kuchuluka kwa chidziwitso pakukonza kungayambitse kulephera kapena kuwerengera kolakwika mukamagwiritsa ntchito mitundu yosiyanasiyana kapena kusefa. Izi zimamveka makamaka mukayenera kugwira ntchito ndi chidziwitso chandalama.

Chifukwa chake, kuti muchepetse ntchitoyo ndi zidziwitso zambiri zotere ndikuchotsa kuthekera kwa zolakwika, tidzasanthula momwe tingagwiritsire ntchito mizere mu Excel ndikuigwiritsa ntchito kuchotsa zobwereza. Zitha kumveka ngati zovuta, koma kuziganizira ndizosavuta, makamaka ngati pali njira zisanu zogwirira ntchito kupeza ndi kuchotsa zobwereza zomwe zilipo.

Njira 1: Chotsani Pamanja Mizere Yobwerezedwa

Chinthu choyamba ndikulingalira kugwiritsa ntchito njira yosavuta yothanirana ndi zobwerezedwa. Iyi ndi njira yamanja, yomwe imaphatikizapo kugwiritsa ntchito tabu "Data":

1. Choyamba muyenera kusankha ma cell onse a tebulo: gwiritsani LMB ndikusankha gawo lonse la ma cell.
2. Pamwamba pazida, muyenera kusankha gawo la "Data" kuti mupeze zida zonse zofunika.
3. Timaganizira mosamala zithunzi zomwe zilipo ndikusankha yomwe ili ndi magawo awiri a maselo ojambulidwa mumitundu yosiyanasiyana. Ngati muyang'ana pa chithunzichi, dzina la "Delete Duplicates" lidzawonetsedwa.
4. Kuti mugwiritse ntchito bwino magawo onse a gawoli, ndikwanira kusamala ndikutenga nthawi yanu ndi zoikamo. Mwachitsanzo, ngati tebulo lili ndi "Header", ndiye onetsetsani kuti mukuyang'ana chinthucho "Deta yanga ili ndi mitu", iyenera kufufuzidwa.

5. Kenako pamabwera zenera lomwe likuwonetsa zambiri ndi column. Muyenera kusankha mizati yomwe mukufuna kuwona ngati yobwerezedwa. Ndikwabwino kusankha zonse kuti muchepetse zodumphira.

6. Zonse zikakonzeka, yang'ananinso zomwe zalembedwa ndikudina Chabwino.
7. Excel imangosanthula maselo osankhidwa ndikuchotsa zonse zofananira.
8. Pambuyo poyang'anitsitsa ndikuchotsa zobwereza patebulo, zenera lidzawonekera mu pulogalamu yomwe padzakhala uthenga woti ndondomekoyi yatha ndipo chidziwitso chidzawonetsedwa kuti ndi mizere ingati yofanana yomwe inachotsedwa.

Muyenera kungodina "Chabwino" ndipo mutha kuganiza kuti zonse zakonzeka. Chitani zonse mosamala, ndipo zotsatira zake sizingakukhumudwitseni.

Njira 2: Kuchotsa Zobwerezedwa Pogwiritsa Ntchito Table Yanzeru

Tsopano tiyeni tione mwatsatanetsatane njira ina yothandiza yochotsera zobwerezedwa, zomwe zimachokera pakugwiritsa ntchito "smart table". Ndikokwanira kutsatira malangizo awa:

1. Choyamba, sankhani tebulo lonse lomwe mukufuna kugwiritsa ntchito algorithm yanzeru yodziwikiratu.

2. Tsopano gwiritsani ntchito chida, pomwe muyenera kusankha gawo la "Home", ndiyeno pezani "Format as Table". Chizindikirochi nthawi zambiri chimakhala mugawo la "Masitayelo". Zimatsalira kugwiritsa ntchito muvi wapadera womwe uli pafupi ndi chithunzicho ndikusankha mawonekedwe a tebulo lomwe mumakonda kwambiri.

3. Zonse zikachitika molondola, uthenga wowonjezera wokhudza kupanga tebulo udzawonekera. Imatchula mtundu womwe Smart Table idzagwiritsidwe ntchito. Ndipo ngati mwasankha kale maselo ofunikira, ndiye kuti mndandandawo udzawonetsedwa zokha ndipo mudzangoyang'ana.

4. Zimangotsala kuti muyambe kusaka ndikuchotsanso mizere yobwereza. Kuti muchite izi, muyenera kuchita zina:
   • ikani cholozera pa selo losasintha la tebulo;
   • pazida pamwamba, sankhani gawo la "Table Design";
   • tikuyang'ana chithunzi chofanana ndi zigawo ziwiri za maselo okhala ndi mtundu wosiyana, mukamayendetsa pamwamba pawo, mawu akuti "Chotsani zobwereza" zidzawonetsedwa;
   • tsatirani njira zomwe tafotokoza m'njira yoyamba mutagwiritsa ntchito chithunzi chomwe mwapatsidwa.

Tcherani khutu! Njirayi ili ndi katundu wapadera - chifukwa cha izo, zidzatheka kugwira ntchito ndi matebulo amitundu yosiyanasiyana popanda zoletsedwa. Malo aliwonse osankhidwa mukamagwira ntchito ndi Excel adzawunikidwa mosamala kuti abwere.

Njira 3: Kugwiritsa Ntchito Zosefera

Tsopano tiyeni tiyang'ane pa njira yapadera yomwe imakulolani kuti musachotse zobwereza patebulo, koma kungobisala. M'malo mwake, njirayi imakupatsani mwayi wopanga tebulo m'njira yoti palibe chomwe chingasokoneze ntchito yanu yopitilira patebulo ndipo ndizotheka kupeza chidziwitso chofunikira komanso chofunikira. Kuti mugwiritse ntchito, muyenera kutsatira izi:

1. Gawo loyamba ndikusankha tebulo lonse momwe musinthira kuti muchotse zobwereza.
2. Tsopano pitani ku gawo la "Data" ndipo nthawi yomweyo pitani ku gawo la "Zosefera".

3. Chizindikiro chodziwikiratu kuti fyulutayo yayatsidwa ndi kukhalapo kwa mivi yapadera pamutu patebulo, pambuyo pake kudzakhala kokwanira kuti mugwiritse ntchito ndikuwonetsa zambiri zokhudzana ndi zobwereza (mwachitsanzo, mawu kapena dzina pakufufuza) .

Chifukwa chake, mutha kusefa zobwerezedwa zonse nthawi yomweyo ndikuchita zosintha zina nazo.

Zosefera zapamwamba kuti mupeze zobwerezedwa mu Excel

Palinso njira ina yowonjezera yogwiritsira ntchito zosefera mu Excel, pazimenezi mudzafunika:

1. Chitani masitepe onse a njira yapitayi.
2. Pazenera la zida, gwiritsani ntchito chizindikiro cha "Advanced", chomwe chili pafupi ndi fyuluta yomweyo.

3. Mukatha kugwiritsa ntchito chithunzichi, muyenera kungoyang'ana pazenera lazokonda. Chida chapamwambachi chimakupatsani mwayi wodziwa zambiri zoyambira:
   • choyamba, muyenera kuyang'ana mndandanda wa tebulo kuti ufanane ndi zomwe mwalemba;
   • onetsetsani kuti mwayang'ana bokosi lakuti "Zolemba zapadera zokha";
   • zonse zikakonzeka, zimangodinanso batani la "Chabwino".

4. Malingaliro onse akakwaniritsidwa, zomwe muyenera kuchita ndikuyang'ana patebulo ndikuwonetsetsa kuti zobwereza sizikuwonetsedwanso. Izi zidzawoneka nthawi yomweyo ngati muyang'ana zomwe zili pansi kumanzere, zomwe zikuwonetsera chiwerengero cha mizere yomwe ikuwonetsedwa pazenera.

Zofunika! Ngati mukufunikira kubwezeretsa chirichonse ku mawonekedwe ake oyambirira, ndiye kuti ndi zophweka momwe mungathere kuchita izi. Ndikokwanira kungoletsa fyulutayo pochita zofanana ndi zomwe zasonyezedwa mu malangizo a njira.

Njira 4: Mapangidwe Okhazikika

Conditional formatting ndi chida chapadera chomwe chimagwiritsidwa ntchito kuthetsa mavuto ambiri. Mutha kugwiritsa ntchito chida ichi kuti mupeze ndikuchotsa zobwerezedwa patebulo. Kuti muchite izi, muyenera kuchita izi:

1. Monga kale, choyamba muyenera kusankha ma cell a tebulo omwe mukufuna kupanga.
2. Tsopano muyenera kupita ku "Home" tabu ndikupeza chizindikiro chapadera cha "Conditional Formatting", chomwe chili mugawo la "Styles".

3. Mukamaliza masitepe, mudzakhala ndi zenera lotchedwa "Malamulo Kusankha Maselo", ndiye muyenera kusankha "Kubwereza Makhalidwe" chinthu.

4. Onetsetsani kuti mwatcheru makonda a masanjidwewo, akuyenera kusiyidwa osasinthika. Chinthu chokhacho chomwe chingasinthidwe ndi kulembera mtundu malinga ndi zomwe mumakonda. Zonse zikakonzeka, mukhoza kudina "Chabwino".

5. Chifukwa cha zochita zotere, mutha kuwunikira zobwereza zonse mumtundu wina ndikuyamba kugwira nawo ntchito mtsogolo.

Chenjerani! Choyipa chachikulu cha njirayi ndikuti mukamagwiritsa ntchito izi, zikhalidwe zonse zomwezo zimayikidwa chizindikiro, osati zokhazo zomwe zingwe zonse zimagwirizana. Ndikoyenera kukumbukira nuance iyi kuti mupewe zovuta zowonera ndikumvetsetsa momwe mungachitire komanso zomwe muyenera kulabadira.

Njira 5: Fomula Yochotsera Mizere Yobwerezedwa

Njirayi ndiyovuta kwambiri kuposa zonse zomwe zalembedwa, chifukwa zimangopangidwira ogwiritsa ntchito omwe amamvetsetsa ntchito ndi mawonekedwe a pulogalamuyi. Kupatula apo, njirayi imaphatikizapo kugwiritsa ntchito njira yovuta. Zikuwoneka motere: =ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов:адрес_шапки_столбца_дубликатов(абсолютный);адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;адрес_столбца;)>1;0;1);0));»»). Tsopano muyenera kusankha momwe mungaigwiritsire ntchito komanso komwe mungaigwiritse ntchito:

1. Gawo loyamba ndikuwonjezera gawo latsopano lomwe liziperekedwa kwa obwereza.

2. Werengani верхнюю ячейку ndi введите mu нее формулу: =ЕСЛИОШИБКА(ИНДЕКС(A2:A90;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E1:$E$1;A2:A90)+ЕСЛИ(СЧЁТЕСЛИ(A2:A90;А2:А90)>1;0;1);0));»»).
3. Tsopano sankhani ndime yonse ya zobwereza popanda kukhudza mutu.

• Ikani cholozera kumapeto kwa chilinganizo, ingosamalani ndi chinthu ichi, popeza chilinganizo sichimawonekera bwino mu selo, ndi bwino kugwiritsa ntchito kapamwamba kofufuzira ndikuyang'ana mosamala malo olondola.
• Mukakhazikitsa cholozera, muyenera kukanikiza batani F2 pa kiyibodi.
• Pambuyo pake, muyenera kukanikiza kuphatikiza kiyi "Ctrl + Shift + Lowani".

4. Chifukwa cha zomwe zachitika, zitheka kudzaza fomulayo ndi chidziwitso chofunikira patebulo.

Kupeza zofananira ndi lamulo la Pezani

Tsopano ndikofunikira kulingalira njira ina yosangalatsa yopezera zobwereza. Makamaka panjira yotere, mudzafunika fomula ina yomwe imawoneka motere: =COUNTIF(A:A, A2)>1.

Zina Zowonjezera! Munjira iyi, A2 amatanthauza chizindikiro cha cell yoyamba kuchokera kudera lomwe mukufuna kusaka. Fomuyi ikangolowa muselo yoyamba, mutha kukoka mtengo ndikupeza zomwe mukufuna. Chifukwa cha zochita zoterezi, zidzatheka kugawa zambiri mu "CHOONADI" ndi "BODZA". Ndipo ngati mukufuna kusaka m'malo ochepa, ndiye lembani kusaka ndikuwonetsetsa kuti mwasunga mayinawa ndi chizindikiro cha $, chomwe chidzatsimikizira kudzipereka ndikuchipanga kukhala maziko.

Ngati simukukhutitsidwa ndi zomwe zili mumtundu wa "CHOONADI" kapena "BODZA", tikupangira kuti tigwiritse ntchito njira iyi, yomwe imapanga chidziwitsocho: =IF(COUNTIF($A$2:$A$17, A2)>1;"Kubwereza";"Wapadera"). Kuchita bwino pazochita zonse kumakupatsani mwayi wopeza zofunikira zonse ndikuthana ndi zomwe zilipo kale.

Momwe mungagwiritsire ntchito pivot table kuti mupeze zobwereza

Njira yowonjezera yogwiritsira ntchito ntchito za Excel kuti mupeze zobwereza ndi PivotTable. Zowona, kuti mugwiritse ntchito, mufunikabe kumvetsetsa koyambira kwa ntchito zonse za pulogalamuyi. Ponena za zochita zazikulu, zikuwoneka motere:

1. Gawo loyamba ndikupanga dongosolo la tebulo.
2. Muyenera kugwiritsa ntchito gawo lomwelo monga chidziwitso cha zingwe ndi zikhalidwe.
3. Mawu ofananira osankhidwa adzakhala maziko owerengera obwereza. Osayiwala kuti maziko a ntchito yowerengera ndi lamulo la "COUNT". Kuti mumve zambiri, kumbukirani kuti zinthu zonse zomwe zimaposa mtengo wa 1 zidzakhala zobwereza.

Samalani ndi chithunzithunzi, chomwe chimasonyeza chitsanzo cha njira yotereyi.

Chosiyanitsa chachikulu cha njirayi ndikuti palibe mafomu aliwonse. Itha kulandiridwa bwino, koma choyamba muyenera kuphunzira mawonekedwe ndi ma nuances ogwiritsira ntchito tebulo la pivot.

Kutsiliza

Tsopano muli ndi chidziwitso chonse chofunikira chokhudza njira zogwiritsira ntchito kufufuza ndi kuchotsa zobwerezabwereza, komanso muli ndi malingaliro ndi malangizo omwe angakuthandizeni kuthetsa vutoli mwamsanga.