Zamkatimu
Tili ndi matebulo awiri (mwachitsanzo, mitundu yakale ndi yatsopano ya mndandanda wamitengo), yomwe tiyenera kufananiza ndikupeza kusiyana kwake:
Nthawi yomweyo n'zoonekeratu kuti chinachake wakhala anawonjezera latsopano mtengo mndandanda (masiku, adyo ...), chinachake mbisoweka (mabulosi akuda, raspberries ...), mitengo zasintha zina katundu (nkhuyu, mavwende ...). Muyenera kupeza mwachangu ndikuwonetsa zosintha zonsezi.
Pantchito iliyonse mu Excel, nthawi zambiri pamakhala njira zingapo (nthawi zambiri 4-5). Kwa vuto lathu, njira zosiyanasiyana zingagwiritsidwe ntchito:
- ntchito VPR (VLOOKUP) - yang'anani mayina azinthu kuchokera pamndandanda wamitengo watsopano muakale ndikuwonetsa mtengo wakale pafupi ndi watsopano, ndiyeno gwirani kusiyana kwake
- phatikizani mindandanda iwiri kukhala imodzi ndiyeno pangani tebulo la pivot kutengerapo, pomwe kusiyana kwake kudzawonekera bwino
- gwiritsani ntchito Power Query Add-in kwa Excel
Tiyeni tizitenge zonse mu dongosolo.
Njira 1. Kufananiza matebulo ndi ntchito ya VLOOKUP
Ngati simukuzidziwa bwino izi, ndiye choyamba yang'anani apa ndikuwerenga kapena muwonere kanema wamaphunziro - dzipulumutseni zaka zingapo za moyo.
Nthawi zambiri, ntchitoyi imagwiritsidwa ntchito kukoka deta kuchokera patebulo limodzi kupita ku lina pofananiza magawo ena ofanana. Pankhaniyi, tidzagwiritsa ntchito kukankhira mitengo yakale kumtengo watsopano:
Zogulitsazo, zomwe #N/A zolakwika zidachitika, sizili pamndandanda wakale, mwachitsanzo, zidawonjezedwa. Kusintha kwamitengo kumawonekeranso bwino.
ubwino njira iyi: yosavuta komanso yomveka, "yodziwika bwino yamtunduwu", monga akunena. Imagwira ntchito mumtundu uliwonse wa Excel.
kuipa lilinso pamenepo. Kuti mufufuze zinthu zomwe zawonjezeredwa pamndandanda wamitengo yatsopano, muyenera kuchitanso chimodzimodzi kumbali ina, mwachitsanzo, kukweza mitengo yatsopano pamtengo wakale mothandizidwa ndi VLOOKUP. Ngati makulidwe a matebulo asintha mawa, ndiye kuti mafomuwo ayenera kusinthidwa. Chabwino, komanso pamatebulo akuluakulu (> mizere 100 zikwi), chisangalalo chonsechi chidzachepa.
Njira 2: Kufananiza matebulo pogwiritsa ntchito pivot
Tiyeni titengere matebulo athu pansi pa chimzake, ndikuwonjezera ndime yokhala ndi dzina la mndandanda wamitengo, kuti pambuyo pake mutha kumvetsetsa kuti ndi mzere uti:
Tsopano, kutengera tebulo lopangidwa, tipanga chidule kudzera Ikani - PivotTable (Lowetsani - Pivot Table). Tiyeni tiponye munda mankhwala kudera la mizere, munda Price ku khola ndi munda Цena mu range:
Monga mukuonera, tebulo la pivot lidzangopanga mndandanda wazinthu zonse kuchokera pamitengo yakale ndi yatsopano (palibe kubwereza!) Mutha kuwona momveka bwino zowonjezeredwa (zilibe mtengo wakale), zochotsedwa (zilibe mtengo watsopano) ndikusintha kwamitengo, ngati kulipo.
Ziwopsezo zazikulu patebulo zotere sizomveka, ndipo zitha kuzimitsidwa pa tabu Womanga - Ziwerengero zazikulu - Zimitsani mizere ndi mizati (Mapangidwe - Magulu Akuluakulu).
Ngati mitengo isintha (koma osati kuchuluka kwa katundu!), ndiye kuti ndikwanira kungosintha mwachidule zomwe zidapangidwa ndikudina kumanja - kulunzanitsa.
ubwino: Njira iyi ndi dongosolo la kukula mwachangu ndi matebulo akulu kuposa VLOOKUP.
kuipa: muyenera kukopera pamanja deta pansi pa wina ndi mzake ndi kuwonjezera ndime ndi dzina la mtengo mndandanda. Ngati kukula kwa matebulo kumasintha, ndiye kuti muyenera kuchita zonse mobwerezabwereza.
Njira 3: Kufananiza matebulo ndi Kufunsa Mphamvu
Power Query ndi chowonjezera chaulere cha Microsoft Excel chomwe chimakupatsani mwayi wotsitsa deta mu Excel kuchokera pafupifupi gwero lililonse ndikusintha izi mwanjira iliyonse yomwe mukufuna. Mu Excel 2016, chowonjezera ichi chamangidwa kale mwachisawawa pa tabu Deta (Data), ndi Excel 2010-2013 muyenera kutsitsa mosiyana ndi tsamba la Microsoft ndikuyiyika - pezani tabu yatsopano. Kufunsa Mphamvu.
Tisanakweze mindandanda yathu yamitengo mu Power Query, iyenera kusinthidwa kukhala matebulo anzeru. Kuti muchite izi, sankhani mtundu womwe uli ndi deta ndikusindikiza kuphatikiza pa kiyibodi Ctrl+T kapena sankhani tabu pa riboni Kunyumba - Pangani ngati tebulo (Kunyumba - Pangani Monga Table). Mayina a matebulo opangidwa akhoza kukonzedwa pa tabu Constructor (Ndisiya muyezo Gulu 1 и Gulu 2, zomwe zimapezedwa mwachisawawa).
Kwezani mtengo wakale mu Power Query pogwiritsa ntchito batani Kuchokera pa Table/Range (Kuchokera pa Table/Range) kuchokera ku tabu Deta (Tsiku) kapena kuchokera ku tabu Kufunsa Mphamvu (kutengera mtundu wa Excel). Pambuyo potsitsa, tidzabwereranso ku Excel kuchokera ku Power Query ndi lamulo Tsekani ndikutsegula - Tsekani ndikulowetsani… (Tsekani & Kwezani — Tsekani & Kwezani Ku…):
… ndipo pa zenera lomwe likuwoneka ndiye sankhani Ingopangani kulumikizana (Kulumikizana kokha).
Bwerezani zomwezo ndi mndandanda wamtengo watsopano.
Tsopano tiyeni tipange funso lachitatu lomwe lingaphatikize ndikufanizira zomwe zachokera ziwiri zam'mbuyomu. Kuti muchite izi, sankhani ku Excel pa tabu Deta - Pezani Zambiri - Phatikizani Zopempha - Phatikizani (Deta - Pezani Zambiri - Gwirizanitsani Mafunso - Gwirizanitsani) kapena dinani batani Gwirizanitsani (Gwirizanitsani) tsamba Kufunsa Mphamvu.
Pazenera lolumikizana, sankhani matebulo athu pamndandanda wotsikira pansi, sankhani mizati yokhala ndi mayina azinthu zomwe zilimo, ndipo pansi, ikani njira yolumikizirana - Malizitsani kunja (Kunja Kwathunthu):
Pambuyo pang'anani OK tebulo la mizati itatu liyenera kuwoneka, pomwe mugawo lachitatu muyenera kukulitsa zomwe zili m'matebulo okhala ndi zisa pogwiritsa ntchito mivi iwiri pamutu:
Zotsatira zake, timapeza kuphatikiza kwa data kuchokera pamatebulo onse awiri:
Ndikwabwino, inde, kutchulanso mayina amigawo pamutu podina kawiri paomveka bwino:
Ndipo tsopano chidwi kwambiri. Pitani ku tabu Onjezani mzati (Onjezani Mzere) ndipo dinani batani Conditional column (Conditional Column). Kenako pawindo lomwe limatsegulidwa, lowetsani miyeso ingapo yoyeserera ndi zomwe zikugwirizana nazo:
Zimakhalabe kuti alemba pa OK ndikukweza lipoti lotsatila ku Excel pogwiritsa ntchito batani lomwelo kutseka ndikutsitsa (Tsekani & Kwezani) tsamba Kunyumba (Kunyumba):
Kukongola.
Komanso, ngati kusintha kulikonse kudzachitika pamndandanda wamitengo mtsogolomo (mizere ikuwonjezedwa kapena kuchotsedwa, mitengo ikusintha, ndi zina zotero), ndiye kuti zikhala zokwanira kungosintha zopempha zathu ndi njira yachidule ya kiyibodi. Ctrl+alt+F5 kapena pa batani Tsitsani zonse (Tsitsani Zonse) tsamba Deta (Tsiku).
ubwino: Mwina njira yokongola komanso yabwino kuposa zonse. Imagwira ntchito mwanzeru ndi matebulo akulu. Simafunika kusintha pamanja posintha ma tebulo.
kuipa: Imafunika kuwonjezera kwa Power Query (mu Excel 2010-2013) kapena Excel 2016 kuti iyikidwe. Mayina amzati zomwe zili muzochokera siziyenera kusinthidwa, apo ayi tipeza cholakwika "Mzere wakuti ndi wakuti sunapezeke!" pamene mukuyesera kusintha funsolo.
- Momwe mungasonkhanitsire deta kuchokera kumafayilo onse a Excel mufoda yopatsidwa pogwiritsa ntchito Power Query
- Momwe mungapezere machesi pakati pa mindandanda iwiri mu Excel
- Kuphatikiza mindandanda iwiri popanda kubwereza