Mukati
Kana iwe uchida kuronga rondedzero, saka kune dzakawanda nzira pasevhisi yako, iyo iri nyore kwazvo ndeye mabhatani emhando patebhu kana mumenyu. Data (Data - Ronga). Zvisinei, pane mamiriro ezvinhu apo kurongwa kwerondedzero kunoda kuitwa otomatiki, kureva mafomula. Izvi zvinogona kudiwa, semuenzaniso, paunenge uchigadzira dhata yekudonhedza pasi, paunenge uchiverenga data yemachati, nezvimwe. Nzira yekugadzira rondedzero ine fomula pane nhunzi?
Nzira 1. Numeric data
Kana iyo rondedzero iine ruzivo rwenhamba chete, saka kurongedza kunogona kuitwa nyore uchishandisa mabasa LEAST (DIKI) и LINE (MUTSARA):
basa LEAST (DIKI) inoburitsa kubva muchikamu (column A) iyo n-th diki chinhu mumutsara. Avo. SMALL(A:A;1) ndiyo nhamba diki pachikamu, SMALL(A:A;2) ndiyo yechipiri idiki, zvichingodaro.
basa LINE (MUTSARA) inodzosa nhamba yemutsara wesero rataurwa, kureva ROW(A1)=1, ROW(A2)=2 zvichingodaro. Pakadai apa, rinoshandiswa sejenareta renhevedzano yenhamba n=1,2,3… yedu yakarongwa list. Nekubudirira kumwe cheteko, zvakagoneka kugadzira imwe koramu, kuizadza nemaoko nenhamba dzinotevedzana 1,2,3 ... uye tarisa pairi pachinzvimbo cheROW basa.
Nzira 2. Rondedzero yezvinyorwa uye mafomula akajairwa
Kana iyo rondedzero isina manhamba, asi zvinyorwa, saka iyo SMALL basa haichashande, saka iwe unofanirwa kuenda nenzira yakasiyana, yakati rebei.
Chekutanga, ngatiwedzerei koramu yebasa ine fomula apo nhamba yesiriri yezita rega rega mune ramangwana yakarongwa runyorwa ichaverengerwa uchishandisa basa. COUNTIF (COUNTIF):
Mushanduro yeChirungu ichave:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Temu yekutanga ibasa rekuverenga huwandu hwemasero ari mashoma pane azvino. Yechipiri ndeyekuchengetedza mambure kana chero zita rikaitika kanopfuura kamwe chete. Ipapo havazove neakaenzana, asi anoteedzana achiwedzera nhamba.
Iye zvino nhamba dzakagamuchirwa dzinofanira kurongwa sequentially mukukwira kurongeka. Nokuda kweizvi unogona kushandisa basa LEAST (DIKI) kubva munzira yekutanga:
Zvakanaka, pakupedzisira, zvinosara kungoburitsa mazita kubva pane rondedzero nenhamba dzavo. Kuti uite izvi, unogona kushandisa fomula inotevera:
basa ZVAKAWANDA AKAFUMURWA (MATCH) inotsvaga mukoramu B yeinodiwa nhamba yesiriri (1, 2, 3, zvichingodaro) uye, kutaura zvazviri, inodzosa nhamba yemutsara panowanikwa nhamba iyi. Function INDEX (INDEX) inoburitsa kubva muchikamu A zita riri pamutsara nhamba iyi.
Nzira 3: Array Formula
Iyi nzira ndeye, iyo yakafanana yekuisa algorithm senge muMethod-2, asi inoshandiswa neyakarongwa fomula. Kurerutsa fomula, huwandu hwemasero C1: C10 hwakapihwa zita List (sarudza maseru, tinya Ctrl + F3 uye bhatani sika):
Musero E1, kopira fomula yedu:
=INDEX(Rondedzero; MATCH(SMALL(COUNTIF(Rondedzero; “<"&List); ROW(1:1)); COUNTIF(Rondedzero; "<"&List); 0))
Kana mushanduro yeChirungu:
=INDEX(Rondedzero, MATCH(SMALL(COUNTIF(List, «<"&List), ROW(1:1)), COUNTIF(Rondedzero, "<"&List), 0))
uye sundidzira Ctrl + Shift + Pindakuiisa seyakarongwa fomula. Ipapo iyo inoguma fomula inogona kukopwa pasi nehurefu hwese hwechinyorwa.
Kana iwe uchida kuti fomula itarise kwete yakatarwa siyana, asi uchikwanisa kugadzirisa kana uchiwedzera zvinhu zvitsva pane iyo rondedzero, saka iwe unozofanirwa kushandura zvishoma zano.
Kutanga, iyo Rondedzero renji inozoda kusetwa zvine simba. Kuti uite izvi, paunenge uchigadzira, iwe unofanirwa kutsanangura kwete yakatarwa renji C3: C10, asi yakakosha fomura iyo inozoreva kune ese aripo maitiro, zvisinei nenhamba yavo. Dzvanya Alt + F3 kana kuvhura tab Mafomura - Maneja Wezita (Mafomula - Maneja Wezita), gadzira zita idzva uye mumunda batanidzo (Referensi) isa iyo inotevera fomula (ini ndinofungidzira kuti huwandu hwe data icharongwa hunotanga kubva muchitokisi C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
Chechipiri, iyo iri pamusoro peiyo fomula ichada kutambanudzwa pasi nemargin - netarisiro yekuwedzera data rakaiswa mune ramangwana. Muchiitiko ichi, fomula yehurongwa ichatanga kupa kukanganisa #NUMBER pamaseru asati azara. Kuti uibate, unogona kushandisa basa racho IFERROR, iyo inoda kuwedzerwa "kutenderera" yedu array formula:
=IFERROR(INDEX(Rondedzero; MATCH(SMALL(COUNTIF(Rondedzero; “<"&List); ROW(1:1)); COUNTIF(Rondedzero; "<"&List); 0));»»)
=IFERROR(NDEX(Rondedzero, MATCH(SMALL(COUNTIF(List, «<"&List), ROW(1:1)), COUNTIF(Rondedzero, "<"&List), 0));"")
Inobata #NUMBER kukanganisa uye inoburitsa void (isina makotesheni) panzvimbo.
:
- Ronga renji neruvara
- Chii chinonzi array formula uye nei ichidikanwa
- SORT kurongedza uye dhizaini arrays muHofisi itsva 365