Contents
Idan kana buƙatar tsara lissafin, to akwai hanyoyi da yawa a sabis ɗin ku, mafi sauƙi wanda shine nau'in maɓalli a kan shafin ko a cikin menu. data (Data - Nau'i). Koyaya, akwai yanayi lokacin da ake buƙatar rarrabuwar lissafin ta atomatik, watau dabara. Ana iya buƙatar wannan, misali, lokacin samar da bayanai don jerin zaɓuka, lokacin ƙididdige bayanai don ginshiƙi, da sauransu. Yaya za a warware jeri tare da dabara akan tashi?
Hanyar 1. Bayanan lambobi
Idan lissafin ya ƙunshi bayanan lambobi kawai, to ana iya rarraba shi cikin sauƙi ta amfani da ayyukan KASHE (KARAMIN) и LINE (ROW):
aiki KASHE (KARAMIN) yana cirewa daga jeri (shafi A) ƙaramin n-th a jere. Wadancan. KARAMIN (A: A;1) ita ce mafi kankantar lamba a cikin ginshikin, KARAMIN (A: A;2) ita ce ta biyu mafi karami, da sauransu.
aiki LINE (ROW) yana dawo da lambar jere don tantanin halitta, watau ROW(A1)=1, ROW(A2)=2 da sauransu. A wannan yanayin, ana amfani da shi kawai azaman janareta na jerin lambobi n=1,2,3… jerin sunayen mu. Tare da wannan nasarar, yana yiwuwa a yi ƙarin ginshiƙi, cika shi da hannu tare da jerin lambobi 1,2,3 ... kuma koma zuwa gare shi maimakon aikin ROW.
Hanyar 2. Lissafin rubutu da ƙididdiga na yau da kullum
Idan lissafin bai ƙunshi lambobi ba, amma rubutu, to aikin SMALL ba zai ƙara yin aiki ba, don haka dole ne ku bi ta daban, ɗan ɗan tsayi, hanya.
Da farko, bari mu ƙara ginshiƙin sabis tare da dabara inda za'a ƙididdige lambar serial na kowane suna a cikin jerin da aka jera a gaba ta amfani da aikin. COUNTIF (COUNTIF):
A cikin harshen Ingilishi zai kasance:
= COUNTIF(A: A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Kalma ta farko aiki ce don kirga adadin ƙwayoyin da ba su kai na yanzu ba. Na biyu shine hanyar tsaro idan kowane suna ya bayyana fiye da sau ɗaya. Sa'an nan ba za su sami iri ɗaya ba, amma a jere suna haɓaka lambobi.
Yanzu lambobin da aka karɓa dole ne a tsara su bi-da-bi-da-kulli a cikin tsari masu hawa. Don wannan zaka iya amfani da aikin KASHE (KARAMIN) daga hanyar farko:
To, a ƙarshe, ya rage kawai don cire sunayen daga lissafin ta adadin su. Don yin wannan, zaka iya amfani da dabara mai zuwa:
aiki MORE BAYYANA (MATSAYI) bincike a shafi na B don neman lambar serial ɗin da ake so (1, 2, 3, da sauransu) kuma, a zahiri, yana mayar da adadin layin da wannan lambar take. Aiki INDEX (INDEX) yana cirewa daga shafi A suna a wannan lambar layin.
Hanyar 3: Tsarin Tsara
Wannan hanyar ita ce, a gaskiya, algorithm wuri ɗaya kamar yadda yake a cikin Hanyar-2, amma an aiwatar da shi ta hanyar tsararru. Don sauƙaƙe dabarar, an ba kewayon sel C1:C10 suna list (zaɓi sel, latsa Ctrl + F3 da maballin Create):
A cikin cell E1, kwafi dabararmu:
=INDEX(List; MATCH(SMALL (COUNTIF(List; "<"&List); ROW(1:1)); COUNTIF
Ko kuma a cikin Turanci:
=INDEX(List, MATCH(SMALL(COUNTIF(List, «<»&List), ROW(1:1)), COUNTIF(List, "<"&List), 0))
da turawa Ctrl + Shigar + Shigardon shigar da shi azaman tsarin tsararru. Sa'an nan sakamakon dabara za a iya kwafi zuwa dukan tsawon jerin.
Idan kuna son dabarar yin la'akari ba tsayayyen kewayon ba, amma ku sami damar daidaitawa lokacin ƙara sabbin abubuwa zuwa jerin, to kuna buƙatar canza dabarun kaɗan.
Na farko, za a buƙaci a saita kewayon jeri a hankali. Don yin wannan, lokacin ƙirƙirar, kuna buƙatar ƙayyade ba tsayayyen kewayon C3: C10 ba, amma ƙayyadaddun tsari na musamman wanda zai nuna duk ƙimar da ake samu, ba tare da la’akari da adadin su ba. Danna Alt+F3 ko bude shafin Formulas - Mai sarrafa Suna (Formulas - Name Manager), ƙirƙirar sabon suna kuma a cikin filin link (Magana) shigar da dabara mai zuwa (Ina ɗauka cewa kewayon bayanan da za a jerawa yana farawa daga cell C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
= KASHE (C1,0,0,SCHÖTZ(C1:C1000),1)
Abu na biyu, tsarin tsararrun da ke sama zai buƙaci a shimfiɗa shi tare da gefe - tare da tsammanin ƙarin bayanan shigar a nan gaba. A wannan yanayin, tsarin tsara tsarin zai fara ba da kuskure #NUMBER akan sel waɗanda ba a cika su ba tukuna. Don shiga tsakani, zaku iya amfani da aikin IFEROR, wanda ke buƙatar ƙarawa "a kusa da" tsarin tsararrun mu:
=IFERO (INDEX (List; MATCH ( KARAMIN (COUNTIF (List; "<"&List); ROW (1: 1)); COUNTIF (List; "<"&List); 0));»))
=IFERROR(NDEX(List, MATCH( KARANAN (COUNTIF (List, «<"&List), ROW(1:1)), COUNTIF (List, "<"&List), 0));"))
Yana kama kuskuren #NUMBER kuma ya fitar da maras amfani (rauni mara kyau) maimakon.
:
- Tsara kewayo ta launi
- Menene tsarin tsararru kuma me yasa ake buƙatar su
- SORT rarrabuwa da tsararraki masu ƙarfi a cikin sabon Office 365