Zamkatimu
Ngati mukufuna kusanja mndandanda, ndiye kuti pali njira zambiri pautumiki wanu, chophweka chomwe ndi mabatani amtundu pa tabu kapena menyu. Deta (Deta - Sanjani). Komabe, pali nthawi zina pamene kusanja mndandanda kumafunika kuchitidwa mwachisawawa, mwachitsanzo, ma formula. Izi zingafunike, mwachitsanzo, popanga deta ya mndandanda wotsikira pansi, powerengera deta ya ma chart, ndi zina zotero. Momwe mungasankhire mndandanda ndi ndondomeko pa ntchentche?
Njira 1. Deta ya manambala
Ngati mndandandawo uli ndi manambala okha, ndiye kuti kusanja kumatha kuchitika mosavuta pogwiritsa ntchito ntchitozo KAPENA (ZANG'ONO) и LINE (ROW):
ntchito KAPENA (ZANG'ONO) imatulutsa kuchokera pamndandanda (gawo A) chinthu chaching'ono kwambiri cha n-th pamzere. Iwo. ANG'ONO (A: A; 1) ndi nambala yaying'ono kwambiri pagawoli, SMALL(A:A;2) ndi yachiwiri yaying'ono, ndi zina zotero.
ntchito LINE (ROW) imabweretsa nambala ya mzere wa selo lotchulidwa, mwachitsanzo, ROW(A1)=1, ROW(A2)=2 ndi zina zotero. Apa, agwiritsidwa ntchito ngati jenereta wa mndandanda wa manambala n=1,2,3… mndandanda wathu wosanjidwa. Ndi kupambana komweko, zinali zotheka kupanga gawo lowonjezera, ndikulidzaza pamanja ndi manambala 1,2,3 ... ndikulozera m'malo mwa ROW ntchito.
Njira 2. Mndandanda wa malemba ndi machitidwe okhazikika
Ngati mndandandawo ulibe manambala, koma zolemba, ndiye kuti SMALL ntchito sidzagwiranso ntchito, kotero muyenera kupita njira ina, yotalikirapo pang'ono.
Choyamba, tiyeni tiwonjezere gawo lautumiki ndi fomula pomwe nambala ya seriyo ya dzina lililonse pamndandanda wosankhidwa wamtsogolo idzawerengedwera pogwiritsa ntchito ntchitoyi. COUNTIF (COUNTIF):
M'Chingerezi zikhala:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Mawu oyamba ndi ntchito yowerengera chiwerengero cha maselo omwe ali ocheperapo kuposa omwe alipo. Chachiwiri ndi khoka lachitetezo ngati dzina lililonse lipezeka kangapo. Ndiye sadzakhala ndi zofanana, koma motsatizana kuchulukana.
Tsopano manambala olandiridwa ayenera kukonzedwa motsatizana pokwera. Kwa izi mungagwiritse ntchito KAPENA (ZANG'ONO) kuyambira njira yoyamba:
Chabwino, potsiriza, zimangokhala kungotulutsa mayina pamndandanda ndi manambala awo. Kuti muchite izi, mutha kugwiritsa ntchito njira iyi:
ntchito ZAMBIRI ZOVUTIKA (MATCH) amafufuza mugawo B kuti apeze nambala yofunikila (1, 2, 3, ndi zina zotero) ndipo, kwenikweni, amabwezera nambala ya mzere umene nambalayi ili. Ntchito INDEX (INDEX) amatulutsa kuchokera pagawo A dzina pamzerewu.
Njira 3: Njira Yopangira
Njirayi ndi yofanana ndi njira ya Method-2, koma imayendetsedwa ndi njira zingapo. Kuti muchepetse chilinganizocho, ma cell angapo C1: C10 adapatsidwa dzina List (sankhani ma cell, dinani Ctrl + F3 ndi batani Pangani):
Mu cell E1, lembani fomula yathu:
= INDEX(Mndandanda; MATCH(YAMNG’ono(COUNTIF(Mndandanda; “<"&List); ROW(1:1)); COUNTIF(Mndandanda; "<"&List); 0))
Kapena mu Chingerezi:
=INDEX(Mndandanda, MATCH(YAMNG'ONO(COUNTIF(List, «<"&List), ROW(1:1)), COUNTIF(Mndandanda, "<"&List), 0))
ndikukankha Ctrl + Shift + Lowanikuti mulowetse ngati ndondomeko yowonjezera. Kenako chilinganizo chotsatira chikhoza kukopera kutalika kwa mndandanda wonse.
Ngati mukufuna kuti chilinganizocho chisaganizire zamtundu wokhazikika, koma mutha kusintha powonjezera zinthu zatsopano pamndandanda, ndiye kuti muyenera kusintha pang'ono njirayo.
Choyamba, mndandanda wa List uyenera kukhazikitsidwa mwamphamvu. Kuti muchite izi, popanga, muyenera kufotokoza osati mtundu wokhazikika C3: C10, koma ndondomeko yapadera yomwe idzatanthauze zikhalidwe zonse zomwe zilipo, mosasamala kanthu za chiwerengero chawo. Dinani Alt + F3 kapena tsegulani tabu Mafomula - Woyang'anira Dzina (Mafomula - Woyang'anira Dzina), pangani dzina latsopano ndi m'munda Lumikizani (Buku) lowetsani chilinganizo chotsatirachi (ndikuganiza kuti kuchuluka kwa data yomwe ikuyenera kusanjidwa kumayambira pa selo C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
Kachiwiri, ndondomeko yomwe ili pamwambayi iyenera kutambasulidwa ndi malire - ndikuyembekeza zowonjezera zomwe zidzalowe m'tsogolomu. Pamenepa, ndondomekoyi idzayamba kupereka cholakwika #NUMBER pamaselo omwe sanadzazidwe. Kuti muthane ndi vutoli, mutha kugwiritsa ntchito IFERROR, yomwe iyenera kuwonjezeredwa "mozungulira" mndandanda wathu:
=IFERROR(INDEX(Mndandanda; MATCH(YAMNG'ono(COUNTIF(List; “<"&List); ROW(1:1)); COUNTIF(Mndandanda; "<"&List); 0));»»)
=IFERROR(NDEX(List, MATCH(SMALL(COUNTIF(List, «<"&List), ROW(1:1)), COUNTIF(List, "<"&List),0));"")
Imagwira #NUMBER cholakwika ndikutulutsa zopanda kanthu (mawu opanda kanthu) m'malo mwake.
:
- Sanjani osiyanasiyana ndi mtundu
- Kodi mafomula ndi chifukwa chiyani amafunikira
- SORT kusanja ndi magulu osinthika mu Office 365 yatsopano