txheem
Yog tias koj xav txheeb cov npe, ces muaj ntau txoj hauv kev ntawm koj qhov kev pabcuam, qhov yooj yim tshaj plaws uas yog cov nyees khawm ntawm lub tab lossis hauv cov ntawv qhia zaub mov. Cov ntaub ntawv (Cov ntaub ntawv - Sort). Txawm li cas los xij, muaj cov xwm txheej thaum kev txheeb xyuas cov npe yuav tsum tau ua tiav, piv txwv li cov qauv. Qhov no tej zaum yuav tsum tau, piv txwv li, thaum tsim cov ntaub ntawv rau ib tug drop-down daim ntawv teev, thaum xam cov ntaub ntawv rau kab kos, thiab lwm yam. Yuav ua li cas txheeb cov npe nrog ib tug qauv ntawm ya?
Txoj Kev 1. Cov ntaub ntawv naj npawb
Yog tias daim ntawv teev npe tsuas muaj cov ntaub ntawv xov xwm, ces txheeb nws tuaj yeem ua tau yooj yim siv cov haujlwm Tsawg (KOJ) и LINE (ROW):
muaj nuj nqi Tsawg (KOJ) rub tawm ntawm array (kem A) n-th tsawg tshaj plaws nyob rau hauv kab. Cov. ME (A: A; 1) yog tus lej tsawg tshaj plaws hauv kab, SMALL(A: A; 2) yog qhov thib ob tsawg tshaj plaws, thiab lwm yam.
muaj nuj nqi LINE (ROW) rov qab tus lej xov tooj rau lub xov tooj uas tau teev tseg, piv txwv li ROW(A1) = 1, ROW(A2) = 2 thiab lwm yam. Nyob rau hauv rooj plaub no, nws yog siv yooj yim los ua lub tshuab hluav taws xob ntawm ib ntu ntawm cov lej n = 1,2,3... peb sorted list. Nrog tib txoj kev vam meej, nws muaj peev xwm ua tau ib kem ntxiv, sau nws manually nrog cov lej 1,2,3 ... thiab xa mus rau nws tsis yog ROW muaj nuj nqi.
Txoj Kev 2. Cov ntawv sau thiab cov qauv tsis tu ncua
Yog tias daim ntawv teev npe tsis muaj tus lej, tab sis cov ntawv nyeem, ces SMALL muaj nuj nqi yuav tsis ua haujlwm ntxiv lawm, yog li koj yuav tsum mus rau qhov sib txawv, me ntsis ntev dua, txoj hauv kev.
Ua ntej, cia peb ntxiv ib qho kev pabcuam nrog cov qauv uas tus lej xov tooj ntawm txhua lub npe nyob rau yav tom ntej txheeb npe yuav suav nrog kev ua haujlwm COUNTIF (COUNTIF):
Hauv Askiv version nws yuav yog:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Thawj lub sij hawm yog lub luag haujlwm rau suav cov cell uas tsawg dua qhov tam sim no. Qhov thib ob yog qhov chaw ruaj ntseg yog tias muaj npe tshwm sim ntau dua ib zaug. Tom qab ntawd lawv yuav tsis muaj qhov qub, tab sis txuas ntxiv nce tus lej.
Tam sim no cov lej tau txais yuav tsum tau npaj ua ntu zus hauv kev txiav txim siab nce mus. Rau qhov no koj tuaj yeem siv lub luag haujlwm Tsawg (KOJ) los ntawm thawj txoj kev:
Zoo, thaum kawg, nws tsuas yog rub tawm cov npe ntawm cov npe los ntawm lawv cov lej. Txhawm rau ua qhov no, koj tuaj yeem siv cov qauv hauv qab no:
muaj nuj nqi XOV XWM ntxiv (MATCH) tshawb hauv kab B rau qhov xav tau serial tooj (1, 2, 3, thiab lwm yam) thiab, qhov tseeb, rov qab tus lej ntawm kab uas tus lej no nyob. Muaj nuj nqi INDEX (INDEX) rub tawm ntawm kab A lub npe ntawm kab no.
Txoj Kev 3: Array Formula
Txoj kev no yog, qhov tseeb, tib qhov kev tso kawm algorithm li hauv Method-2, tab sis siv los ntawm cov qauv array. Txhawm rau ua kom yooj yim cov qauv, qhov ntau ntawm cov hlwb C1: C10 tau muab lub npe daim ntawv teev (xaiv cov hlwb, nias Ctrl + F3 thiab khawm tsim):
Hauv cell E1, luam peb cov qauv:
=INDEX(List; MATCH(SMALL(COUNTIF(List; “<"&List); ROW(1:1)); COUNTIF(List; "<"&List); 0))
Los yog hauv lus Askiv version:
=INDEX(List, MATCH(SMALL(COUNTIF(List, «<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0))
thiab laub Ctrl + ua haujlwm + nkagnkag mus rau nws raws li ib tug array formula. Tom qab ntawd cov qauv tsim tau tuaj yeem luam tawm tag nrho ntev ntawm daim ntawv teev npe.
Yog tias koj xav kom tus qauv coj mus rau hauv tus account tsis yog qhov chaw ruaj khov, tab sis tuaj yeem hloov kho thaum ntxiv cov ntsiab lus tshiab rau hauv daim ntawv teev npe, ces koj yuav tsum tau hloov lub tswv yim me ntsis.
Ua ntej, Daim Ntawv Teev Npe yuav tsum tau teeb tsa dynamically. Txhawm rau ua qhov no, thaum tsim, koj yuav tsum qhia meej tsis yog qhov chaw ruaj khov C3: C10, tab sis cov qauv tshwj xeeb uas yuav xa mus rau txhua qhov muaj nuj nqis, tsis hais lawv tus lej. Nyem Alt + F3 los yog qhib lub tab Formulas - Tus Thawj Tswj Lub Npe (Formulas - Tus Thawj Saib Xyuas Lub Npe), tsim lub npe tshiab thiab hauv daim teb txuas (Kev txheeb xyuas) nkag mus rau cov qauv hauv qab no (Kuv xav tias qhov ntau ntawm cov ntaub ntawv yuav tsum tau txheeb pib los ntawm cell C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
Qhov thib ob, cov qauv saum toj no yuav tsum tau nthuav tawm nrog cov npoo - nrog rau kev cia siab ntawm cov ntaub ntawv ntxiv nkag mus rau yav tom ntej. Hauv qhov no, cov qauv array yuav pib muab qhov yuam kev #NUMBER ntawm cov hlwb uas tseem tsis tau sau. Txhawm rau cuam tshuam nws, koj tuaj yeem siv txoj haujlwm IFERROR, uas yuav tsum tau ntxiv "nyob ib ncig ntawm" peb array formula:
=IFERROR(INDEX(List; MATCH(SMALL(COUNTIF(List; “<"&List); ROW(1:1)); COUNTIF(List; "<"&List); 0));»»)
=IFERROR(NDEX(List, MATCH(SMALL(COUNTIF(List, «<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0));"")
Nws ntes qhov #NUMBER yuam kev thiab tso tawm qhov tsis muaj dab tsi (tsis muaj nqe lus) hloov.
:
- Sib txawv ntawm cov xim
- Dab tsi yog cov qauv array thiab vim li cas lawv xav tau
- SORT sorting thiab dynamic arrays hauv Office 365 tshiab