Zamkatimu
Kusaka konse kwachikale ndikusintha kwamtundu VPR (VLOOKUP), GPR (HLOOKUP), ZAMBIRI ZOVUTIKA (MATCH) ndipo omwe ali ngati iwo ali ndi chinthu chimodzi chofunikira - amafufuza kuyambira pachiyambi mpaka kumapeto, mwachitsanzo, kuchokera kumanzere kupita kumanja kapena pamwamba mpaka pansi mu deta yochokera. Machesi ofananirawo akangopezeka, kusaka kumayimitsidwa ndipo kupezeka koyamba kwa chinthu chomwe tikufuna ndi komwe kumapezeka.
Zoyenera kuchita ngati sitiyenera kupeza woyamba, koma womaliza? Mwachitsanzo, kugulitsa komaliza kwa kasitomala, kulipira komaliza, kuyitanitsa kwaposachedwa, ndi zina zambiri?
Njira 1: Kupeza Mzere Womaliza wokhala ndi Array Formula
Ngati tebulo lapachiyambi liribe ndime yokhala ndi deti kapena nambala ya mndandanda wa mzere (dongosolo, malipiro ...), ndiye kuti ntchito yathu ndi kupeza mzere womaliza womwe umakwaniritsa zomwe tapatsidwa. Izi zitha kuchitika pogwiritsa ntchito njira zotsatirazi:
Pano:
- ntchito IF (NGATI) imayang'ana ma cell onse mumzere umodzi ndi umodzi kasitomala ndikuwonetsa nambala ya mzere ngati ili ndi dzina lomwe tikufuna. Nambala ya mzere pa pepala lapatsidwa kwa ife ndi ntchito LINE (ROW), koma popeza tikufuna nambala ya mzere patebulo, tiyeneranso kuchotsa 1, chifukwa tili ndi mutu patebulo.
- Ndiye ntchito Max (MAX) amasankha mtengo wapamwamba kwambiri kuchokera pamndandanda wopangidwa wa manambala a mzere, mwachitsanzo, chiwerengero cha mzere waposachedwa kwambiri wa kasitomala.
- ntchito INDEX (INDEX) imabweretsa zomwe zili mu cell ndi nambala yomaliza yomwe idapezeka kuchokera pagawo lina lililonse lofunikira (Khodi yoyitanitsa).
Zonsezi ziyenera kulembedwa ngati ndondomeko yowonjezera, ie:
- Mu Office 365 ndi zosintha zaposachedwa zomwe zakhazikitsidwa ndi chithandizo chamagulu osinthika, mutha kungosindikiza Lowani.
- M'mitundu ina yonse, mutalowa fomula, muyenera kukanikiza njira yachidule ya kiyibodi Ctrl+kosangalatsa+Lowani, zomwe zimangowonjezera zomangira zopindika kwa izo mu bar formula.
Njira 2: Sinthani kuyang'ana ndi ntchito yatsopano ya LOOKUP
Ndalemba kale nkhani yayitali ndi kanema wokhudza chinthu chatsopano Onani (XLOOKUP), yomwe idawonekera m'mawonekedwe aposachedwa a Office kuti alowe m'malo mwa VLOOKUP yakale (VLOOKUP). Mothandizidwa ndi BROWSE, ntchito yathu imathetsedwa kwenikweni, chifukwa. pa ntchitoyi (mosiyana ndi VLOOKUP), mutha kuyika momveka bwino komwe mukusakira: pamwamba-pansi kapena pansi-mmwamba - mkangano wake womaliza (-1) ndiwochititsa izi:
Njira 3. Fufuzani chingwe chokhala ndi tsiku laposachedwa
Ngati m'malo oyambira tili ndi ndime yokhala ndi nambala ya serial kapena tsiku lomwe limagwira ntchito yofananira, ndiye kuti ntchitoyo imasinthidwa - sitiyenera kupeza mzere womaliza (wotsikitsitsa) wokhala ndi machesi, koma mzere waposachedwa ( maximum) tsiku.
Ndakambirana kale mwatsatanetsatane momwe ndingachitire izi pogwiritsa ntchito ntchito zapamwamba, ndipo tsopano tiyeni tiyese kugwiritsa ntchito mphamvu zamagulu atsopano osinthika. Pofuna kukongola kwambiri komanso kusavuta, timasinthanso tebulo loyambirira kukhala tebulo la "anzeru" pogwiritsa ntchito njira yachidule ya kiyibodi Ctrl+T kapena malamulo Kunyumba - Pangani ngati tebulo (Kunyumba - Pangani Monga Table).
Ndi chithandizo chawo, "banja lakupha" ili limathetsa vuto lathu mokoma mtima:
Pano:
- Ntchito choyamba FILTER (SEFA) amasankha mizere yokhayo patebulo lathu pomwe ili mgawo kasitomala - dzina lomwe tikufuna.
- Ndiye ntchito kalasi (SINTHA) amasanja mizere yosankhidwa potengera deti motsika, ndipo malonda aposachedwa kwambiri amakhala pamwamba.
- ntchito INDEX (INDEX) imatulutsa mzere woyamba, mwachitsanzo, imabweretsa malonda omaliza omwe tikufuna.
- Ndipo, potsiriza, ntchito ya FILTER yakunja imachotsa zowonjezera za 1st ndi 3rd pazotsatira (Khodi yoyitanitsa и kasitomala) ndikusiya tsiku ndi kuchuluka kwake. Kwa izi, mndandanda wa zokhazikika zimagwiritsidwa ntchito. {0;1;0;1}, kufotokozera ndime zomwe tikufuna (1) kapena zomwe sitikufuna (0) kuwonetsa.
Njira 4: Kupeza Machesi Omaliza Mumafunso a Mphamvu
Chabwino, chifukwa cha kukwanira, tiyeni tiwone njira yothetsera vuto lathu losakira pogwiritsa ntchito Power Query add-in. Ndi chithandizo chake, zonse zimathetsedwa mwachangu komanso mokongola.
1. Tiyeni tisinthe tebulo lathu loyambirira kukhala la “nzeru” pogwiritsa ntchito njira yachidule ya kiyibodi Ctrl+T kapena malamulo Kunyumba - Pangani ngati tebulo (Kunyumba - Pangani Monga Table).
2. Kwezani mu Power Query ndi batani Kuchokera pa Table/Range tsamba Deta (Deta - Kuchokera pa Table/Range).
3. Timasankha (kupyolera mu mndandanda wotsikira pansi wa fyuluta pamutu) tebulo lathu mu dongosolo lotsika la tsiku, kuti zochitika zaposachedwa kwambiri zikhale pamwamba.
4… Mu tabu Transformation sankhani gulu Gulu ndi (Sinthani - Gulu Mwa) ndi kukhazikitsa gulu ndi makasitomala, ndipo monga ntchito aggregating, kusankha njira Mizere yonse (Mizere yonse). Mutha kutchula gawo latsopanoli chilichonse chomwe mungafune - mwachitsanzo tsatanetsatane.
Pambuyo pogawa m'magulu, tidzalandira mndandanda wa mayina apadera a makasitomala athu ndi mndandanda tsatanetsatane - matebulo omwe ali ndi zochitika zonse za aliyense wa iwo, pomwe mzere woyamba udzakhala waposachedwa, zomwe timafunikira:
5. Onjezani gawo latsopano lowerengedwa ndi batani Mzati mwamakonda tsamba Onjezani mzati (Onjezani gawo - Onjezani gawo lazokonda)ndipo lowetsani formula iyi:
apa tsatanetsatane - iyi ndi gawo lomwe timatengera matebulo ndi makasitomala, ndi 0 {} ndi nambala ya mzere womwe tikufuna kuchotsa (kuwerengera mizere mu Power Query kumayambira paziro). Timapeza mzere wokhala ndi zolemba (mbiri), pomwe cholowa chilichonse chili mzere woyamba kuchokera patebulo lililonse:
Zimatsalira kukulitsa zomwe zili m'marekodi onse ndi batani lokhala ndi mivi iwiri pamutu wagawo Ndalama yomaliza kusankha mizati yomwe mukufuna:
… ndiyeno chotsani gawo lomwe silikufunikanso tsatanetsatane podina kumanja pamutu wake - Chotsani mizati (Chotsani mizati).
Pambuyo kukweza zotsatira pa pepala kudzera Kunyumba - Tsekani ndikutsegula - Tsekani ndikulowetsani (Kunyumba - Tsekani & Kwezani - Tsekani & Kwezani ku...) tipeza tebulo labwino kwambiri lomwe lili ndi mndandanda wazomwe zachitika posachedwa, monga momwe timafunira:
Mukasintha magwero a data, musaiwale kusintha zotsatira mwa kuwonekera kumanja pa iwo - lamulo Sinthani & Sungani (Bwezeretsani) kapena njira yachidule ya kiyibodi Ctrl+alt+F5.
- Ntchito ya LOOKUP ndi mbadwa ya VLOOKUP
- Momwe mungagwiritsire ntchito zida zatsopano zosinthira SORT, FILTER, ndi UNIC
- Kupeza selo lomaliza lopanda kanthu pamzere kapena ndime yokhala ndi ntchito ya LOOKUP