Zamkatimu
Kodi muli ndi matebulo okhala ndi data mu Excel omwe angasinthidwenso, mwachitsanzo, kuchuluka kwa mizere (mizere) kumatha kuchulukira kapena kuchepera panthawi yantchito? Ngati kukula kwa tebulo "kuyandama", ndiye kuti muyenera kuwunika nthawi zonse ndikuwongolera:
- maulalo amitundu yama lipoti omwe amatengera tebulo lathu
- mitundu yoyambirira ya matebulo a pivot omwe amamangidwa molingana ndi tebulo lathu
- mitundu yoyambirira ya ma chart opangidwa molingana ndi tebulo lathu
- osiyanasiyana otsika omwe amagwiritsa ntchito tebulo lathu ngati gwero la data
Zonsezi sizingakulole kuti mutope 😉
Zidzakhala zosavuta komanso zolondola kupanga "rabara" yosinthika, yomwe imangosintha kukula kwa chiwerengero chenicheni cha mizere ndi mizati ya deta. Kuti akwaniritse izi, pali njira zingapo.
Njira 1. Tebulo lanzeru
Onetsani magulu anu osiyanasiyana ndikusankha kuchokera pa tabu Kunyumba - Mapangidwe Monga Tebulo (Kunyumba - Mapangidwe Monga Tebulo):
Ngati simukufuna mapangidwe amizeremizere omwe amawonjezedwa patebulo ngati mbali, ndiye kuti mutha kuyimitsa pa tabu yomwe ikuwoneka. Wopanga (Design). Gome lililonse lopangidwa motere limalandira dzina lomwe lingasinthidwe ndi losavuta pamalo omwewo pa tabu Wopanga (Design) m'munda Dzina latebulo (Dzina la Table).
Tsopano titha kugwiritsa ntchito maulalo amphamvu ku "smart table" yathu:
- Gulu 1 - kulumikizana ndi tebulo lonse kupatula mzere wamutu (A2:D5)
- Gulu1[#Onse] - ulalo ku tebulo lonse (A1:D5)
- Tebulo1[Petro] - kutchula mndandanda wamtundu wopanda mutu woyamba wa cell (C2:C5)
- Gulu1[#Mitu] - kulumikizana ndi "mutu" wokhala ndi mayina amipingo (A1:D1)
Maumboni oterowo amagwira ntchito bwino pama formula, mwachitsanzo:
= SUM (Table1 [Moscow]) - kuwerengera kuchuluka kwa gawo "Moscow"
or
= VPR(F5;Gulu 1;3;0) - fufuzani patebulo la mweziwo kuchokera ku selo F5 ndikupereka ndalama zake ku St. Petersburg (VLOOKUP ndi chiyani?)
Maulalo oterowo atha kugwiritsidwa ntchito bwino popanga ma pivot table posankha pa tabu Ikani - Pivot Table (Lowetsani - Pivot Table) ndikulowetsa dzina la tebulo lanzeru ngati gwero la data:
Ngati musankha chidutswa cha tebulo lotere (mwachitsanzo, mizati iwiri yoyambirira) ndikupanga chithunzi chamtundu uliwonse, ndiye powonjezera mizere yatsopano, idzawonjezedwa pazithunzizo.
Mukapanga mindandanda yotsikira pansi, maulalo olunjika kuzinthu zanzeru sangagwiritsidwe ntchito, koma mutha kuthana ndi malire awa pogwiritsa ntchito njira yanzeru - gwiritsani ntchito ntchitoyi. KUMALO (KODI), zomwe zimasintha mawuwo kukhala ulalo:
Iwo. ulalo ku tebulo lanzeru mu mawonekedwe a zingwe zolembera (mu ma quotation mark!) umasanduka ulalo wathunthu, ndipo mndandanda wotsikira pansi umazindikira.
Njira 2: Dynamic dzina osiyanasiyana
Ngati kusandutsa deta yanu kukhala tebulo lanzeru sikungakhale koyenera pazifukwa zina, ndiye kuti mutha kugwiritsa ntchito njira yovuta, koma yowoneka bwino komanso yosunthika - pangani gulu lodziwika bwino mu Excel lomwe limatanthawuza tebulo lathu. Kenako, monga momwe zilili patebulo lanzeru, mutha kugwiritsa ntchito momasuka dzina lamitundu yopangidwa mwanjira iliyonse, malipoti, ma chart, ndi zina zambiri. Tiyeni tiyambe ndi chitsanzo chosavuta:
Ntchito: pangani mndandanda wamatchulidwe omwe angatanthauze mndandanda wamizinda ndikudzitambasulira ndi kucheperako powonjezera mizinda yatsopano kapena kuichotsa.
Tidzafunika ntchito ziwiri zomangidwa mu Excel zomwe zikupezeka mumtundu uliwonse - Mtengo wa POICPOZ (MATCH) kudziwa selo lomaliza la mndandanda, ndi INDEX (INDEX) kupanga ulalo wamphamvu.
Kupeza selo lomaliza pogwiritsa ntchito MATCH
MATCH(kuyang'ana_mtengo, mtundu, match_mtundu) - ntchito yomwe imafufuza mtengo woperekedwa mumtundu (mzere kapena mzere) ndikubwezeretsa nambala ya ordinal ya selo yomwe idapezeka. Mwachitsanzo, fomula MATCH(“March”;A1:A5;0) ibweza nambala 4 monga chotsatira, chifukwa mawu oti “March” ali mu selo lachinayi mugawo A1:A5. Mkangano womaliza wa ntchito Match_Type = 0 zikutanthauza kuti tikufuna kufanana kwenikweni. Ngati mkanganowu sunatchulidwe, ndiye kuti ntchitoyi idzasinthira kumayendedwe osaka pamtengo wapafupi kwambiri - izi ndizomwe zingagwiritsidwe ntchito bwino kuti tipeze selo lomaliza lomwe lili mugulu lathu.
Chofunika kwambiri chachinyengo ndi chophweka. MATCH imafufuza ma cell kuyambira pamwamba mpaka pansi ndipo, mwamalingaliro, iyenera kuyima ikapeza mtengo wapafupi kwambiri ndi womwe wapatsidwa. Ngati mungatchule mtengo womwe mwachiwonekere ndi wokulirapo kuposa chilichonse chomwe chili patebulo ngati mtengo womwe mukufuna, ndiye kuti MATCH ifika kumapeto kwenikweni kwa tebulo, osapeza chilichonse ndikupereka nambala yotsatizana ya cell yomaliza. Ndipo tikuzifuna!
Ngati pali manambala okha pamndandanda wathu, ndiye kuti titha kufotokozera nambala ngati mtengo womwe tikufuna, womwe mwachiwonekere ndi waukulu kuposa womwe uli patebulo:
Kuti mupeze chitsimikizo, mungagwiritse ntchito nambala 9E + 307 (9 nthawi 10 ku mphamvu ya 307, mwachitsanzo 9 ndi 307 zero) - chiwerengero chachikulu chomwe Excel angagwire ntchito.
Ngati pali zolemba pamagawo athu, ndiye kuti ndizofanana ndi nambala yayikulu kwambiri, mutha kuyika zomangazo REPEAT("i", 255) - chingwe chokhala ndi zilembo 255 "i" - chilembo chomaliza cha zilembo. Since Excel actually compares character codes when searching, any text in our table will technically be “smaller” than such a long “yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy” line:
Pangani ulalo pogwiritsa ntchito INDEX
Tsopano popeza tadziwa malo a chinthu chomaliza chomwe chilibe kanthu patebulo, chimatsalira kupanga ulalo wamtundu wathu wonse. Kuti tichite izi, timagwiritsa ntchito:
INDEX(mtundu; mzere_nambala; gawo_nambala)
Imapereka zomwe zili mu selo kuchokera pamzere ndi mzere ndi nambala ya mzere, mwachitsanzo, ntchito = INDEX (A1: D5; 3; 4) patebulo lathu ndi mizinda ndi miyezi kuchokera ku njira yapitayi idzapereka 1240 - zomwe zili mkati. kuchokera pamzere wa 3 ndi gawo la 4, mwachitsanzo, ma cell D3. Ngati pali gawo limodzi lokha, ndiye kuti nambala yake ikhoza kusiyidwa, mwachitsanzo, formula INDEX(A2:A6;3) ipereka "Samara" pachithunzi chomaliza.
Ndipo pali lingaliro limodzi losadziwikiratu: ngati INDEX sinangolowa m'selo pambuyo pa = chizindikiro, monga mwachizolowezi, koma imagwiritsidwa ntchito ngati gawo lomaliza lazofotokozera zamtundu pambuyo pa colon, ndiye kuti sichimaperekanso. zomwe zili mu cell, koma adilesi yake! Chifukwa chake, fomula ngati $ A $ 2: INDEX ($ A $ 2: $ A $ 100; 3) ipereka zonena zamitundu A2: A4 pazotulutsa.
Ndipo apa ndipamene ntchito ya MATCH imabwera, yomwe timayika mkati mwa INDEX kuti tidziwe mapeto a mndandanda:
=$A$2:INDEX($A$2:$A$100; MATCH(REP(“Ine”;255)A2:A100))
Pangani gulu lotchulidwa
Zimatsalira kuti zinyamule zonsezo mumtundu umodzi. Tsegulani tabu chilinganizo (Mafomula) Ndipo dinani Woyang'anira Dzina (Name Manager). Pazenera lomwe limatsegulidwa, dinani batani Pangani (chatsopano), lowetsani dzina lathu ndi fomula m'munda zosiyanasiyana (Buku):
Zimakhalabe kuti alemba pa OK ndipo mndandanda wokonzeka ukhoza kugwiritsidwa ntchito muzolemba zilizonse, mindandanda yotsikira pansi kapena ma chart.
- Pogwiritsa ntchito ntchito ya VLOOKUP kulumikiza matebulo ndi mawonedwe owunikira
- Momwe mungapangire mndandanda wotsitsa wongodzaza zokha
- Momwe mungapangire tebulo la pivot kuti mufufuze kuchuluka kwa deta