Zamkatimu
Kufunika kopeza zabwino kwambiri komanso zotsika kwambiri mubizinesi iliyonse ndizodziwikiratu: zinthu zopindulitsa kwambiri kapena makasitomala ofunikira, zotumiza zazikulu kapena zotumiza, ndi zina zotero.
Koma pamodzi ndi izi, nthawi zina mumayenera kuyang'ana mu deta osati pamwamba, koma pazikhalidwe zomwe zimachitika kawirikawiri, zomwe, ngakhale zimawoneka ngati zofanana, siziri zofanana. Pokhudzana ndi sitolo, mwachitsanzo, izi zikhoza kukhala kufufuza zinthu zomwe zimagulidwa kawirikawiri, osati zopindulitsa kwambiri, kapena kuchuluka kwa zinthu zomwe zimachitika kawirikawiri mu dongosolo, mphindi pazokambirana, ndi zina zotero.
Zikatero, vutoli liyenera kuthetsedwa mosiyana, malingana ndi zomwe tikuchita - manambala kapena malemba.
Kupeza Nambala Zodziwika Kwambiri
Tiyerekeze kuti tikuyang'anizana ndi ntchito yosanthula deta yomwe ilipo pa malonda m'sitolo kuti tidziwe kuchuluka kwa zinthu zomwe zimachitika kawirikawiri. Kuti mudziwe kuchuluka kwanthawi zonse pamagawo angapo, mutha kugwiritsa ntchito ntchitoyi FASHION (Mawonekedwe):
Ndiye kuti, malinga ndi ziwerengero zathu, nthawi zambiri ogula amagula ma PC atatu. katundu.
Ngati palibe, koma mfundo zingapo nthawi imodzi zomwe zimachitika nthawi yomweyo (mitundu ingapo), ndiye kuti muwazindikire, mutha kugwiritsa ntchito ntchitoyi. FASHION.NSK (MODE.MULT). Iyenera kulowetsedwa ngati chilinganizo chotsatira, mwachitsanzo, sankhani maselo angapo opanda kanthu nthawi imodzi, kuti pakhale zokwanira mitundu yonse yokhala ndi malire ndikulowetsa =MODA.NSK(B2:B16) mu bar ya formula ndikudina njira yachidule ya kiyibodi. Ctrl + Shift + Lowani.
Pazotulutsa, tipeza mndandanda wa ma mods onse kuchokera ku data yathu:
Ndiko kuti, kuweruza ndi deta yathu, nthawi zambiri samatenga 3 okha, komanso zidutswa 16. katundu. Chonde dziwani kuti pali mitundu iwiri yokha mu data yathu (3 ndi 16), kotero ma cell ena onse omwe aikidwa "osungidwa" adzakhala ndi #N/A cholakwika.
Kusanthula pafupipafupi ndi magulu okhala ndi FREQUENCY ntchito
Ngati kuli koyenera kusanthula osati manambala, koma manambala ang'onoang'ono, ndiye kuti zingakhale zolondola kwambiri kuti musayese kuchuluka kwa ziwerengero zofanana, koma kugwera kwawo mumilingo yotchulidwa. Mwachitsanzo, tiyenera kumvetsetsa kuti ndi kulemera kotani komwe nthawi zambiri kumagulidwa ndi katundu kuti tisankhe trolleys yoyenera ndi matumba onyamula a kukula koyenera kwa sitolo. M'mawu ena, tiyenera kudziwa manambala angati kugwa mu imeneyi 1..5 makilogalamu, angati mu imeneyi 5..10 makilogalamu, etc.
Kuti muthane ndi vuto lofananalo, mutha kugwiritsa ntchito ntchitoyi Kangati (FREQUENCY). Kwa izo, muyenera kukonzekera ma cell pasadakhale ndi nthawi (matumba) otisangalatsa ndikusankha maselo opanda kanthu (G2: G5) cell imodzi yayikulu kuposa kuchuluka kwa matumba (F2: F4) ndikulowa ngati sinthani fomula mwa kukanikiza kuphatikiza kumapeto Ctrl + Shift + Lowani:
Kusanthula pafupipafupi ndi tebulo la pivot ndi magulu
Njira ina yothetsera vutoli: pangani tebulo la pivot pomwe mumayika kulemera kwa zogula m'mizere, ndi kuchuluka kwa makasitomala m'dera lamtengo wapatali, ndiyeno gwiritsani ntchito magulu - dinani kumanja pazolemera ndi sankhani lamulo gulu (Gulu). Pazenera lomwe likuwoneka, mutha kukhazikitsa malire ndi gawo lamagulu:
... ndipo pambuyo podina batani OK pezani tebulo lomwe lili ndi chiwerengero cha ogula omwe agulidwa mumagulu aliwonse:
kuipa Tiyeni uku:
- gawo lamagulu likhoza kukhala lokhazikika, mosiyana ndi ntchitoyo Kangati, kumene matumba akhoza kutchulidwa mwamtheradi aliyense
- tebulo la pivot liyenera kusinthidwa pamene gwero la deta likusintha (podina batani lakumanja la mbewa - Refresh), ndipo ntchitoyo imawerengedwanso pokhapokha pa ntchentche.
Sakani mawu omwe akupezeka kwambiri
Ngati sitikuchita ndi manambala, koma ndi malemba, ndiye kuti njira yothetsera vutoli idzakhala yosiyana kwambiri. Tiyerekeze kuti tili ndi tebulo lokhala ndi mizere 100 ya zinthu zogulitsidwa m'sitolo ndipo tikufuna kudziwa kuti ndi zinthu ziti zomwe zimagulidwa nthawi zambiri?
Yankho losavuta komanso lodziwika bwino lingakhale kuwonjezera ndime pafupi ndi ntchitoyi COUNTIF (COUNTIF)kuwerengera kuchuluka kwa zochitika za chinthu chilichonse mugawo A:
Kenako, inde, sankhani mzere wotsatira mu dongosolo lotsika ndikuyang'ana mizere yoyamba.
Kapena onjezani ndime yomwe ili ndi pamndandanda woyambirira ndikumanga tebulo lachidule kutengera tebulo lomwe likubwera, kuwerengera kuchuluka kwazomwe zili patsamba lililonse:
Ngati palibe zambiri zoyambira ndipo simukufuna kugwiritsa ntchito ma pivot tables, mutha kugwiritsa ntchito njira yotsatsira:
Tiyeni tiphwanye pang'onopang'ono:
- COUNTIF(A2:A20;A2:A20) ndi dongosolo lomwe limayang'ana motsatizana kuchuluka kwa zochitika za chinthu chilichonse mumtundu A2:A100 ndipo imapanga mndandanda wokhala ndi kuchuluka kwa kubwereza zomwe zatulutsa, mwachitsanzo, m'malo mwa ndime yowonjezera
- MAX - imapeza nambala yayikulu kwambiri pamndandanda wazomwe zimachitika, mwachitsanzo, zogulidwa kwambiri
- MATCH - imawerengera nambala ya ordinal patebulo pomwe MAX adapeza nambala yayikulu kwambiri
- INDEX - imabweretsa kuchokera patebulo zomwe zili muselo ndi nambala yopezeka ndi MATCH
- Kuwerengera kuchuluka kwazinthu zapadera pamndandanda
- Kutulutsa Zinthu Zapadera Pamndandanda Wobwerezedwa
- Kuyika m'magulu a pivot table