Nilalaman
Kung kailangan mong pag-uri-uriin ang listahan, pagkatapos ay mayroong maraming mga paraan sa iyong serbisyo, ang pinakamadali ay ang mga pindutan ng pag-uuri sa tab o sa menu data (Data — Pagbukud-bukurin). Gayunpaman, may mga sitwasyon kung kailan kailangang awtomatikong gawin ang pag-uuri ng listahan, ibig sabihin, mga formula. Maaaring kailanganin ito, halimbawa, kapag bumubuo ng data para sa isang drop-down na listahan, kapag nagkalkula ng data para sa mga chart, atbp. Paano mag-uri-uriin ang isang listahan na may formula sa mabilisang?
Paraan 1. Numeric data
Kung ang listahan ay naglalaman lamang ng numeric na impormasyon, pagkatapos ay ang pag-uuri nito ay madaling gawin gamit ang mga function LEAST (MALIIT) и LINE (ROW):
tungkulin LEAST (MALIIT) kinukuha mula sa array (column A) ang n-th na pinakamaliit na elemento sa isang hilera. Yung. SMALL(A:A;1) ay ang pinakamaliit na numero sa column, SMALL(A:A;2) ay ang pangalawang pinakamaliit, at iba pa.
tungkulin LINE (ROW) ibinabalik ang row number para sa tinukoy na cell, ibig sabihin, ROW(A1)=1, ROW(A2)=2 atbp. Sa kasong ito, ito ay ginagamit lamang bilang generator ng isang sequence ng mga numero n=1,2,3… para sa ang aming pinagsunod-sunod na listahan. Sa parehong tagumpay, posibleng gumawa ng karagdagang column, manu-manong punan ito ng numerical sequence 1,2,3 … at sumangguni dito sa halip na ang ROW function.
Paraan 2. Listahan ng teksto at mga regular na formula
Kung ang listahan ay naglalaman ng hindi mga numero, ngunit teksto, ang SMALL function ay hindi na gagana, kaya kailangan mong pumunta sa ibang, bahagyang mas mahaba, landas.
Una, magdagdag tayo ng column ng serbisyo na may formula kung saan kakalkulahin ang serial number ng bawat pangalan sa hinaharap na nakaayos na listahan gamit ang function. COUNTIF (COUNTIF):
Sa Ingles na bersyon ito ay magiging:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Ang unang termino ay isang function para sa pagbibilang ng bilang ng mga cell na mas mababa kaysa sa kasalukuyang isa. Ang pangalawa ay isang safety net kung sakaling lumitaw ang anumang pangalan nang higit sa isang beses. Pagkatapos ay hindi sila magkakaroon ng pareho, ngunit sunud-sunod na pagtaas ng mga numero.
Ngayon ang mga natanggap na numero ay dapat na ayusin nang sunud-sunod sa pataas na pagkakasunud-sunod. Para dito maaari mong gamitin ang function LEAST (MALIIT) mula sa unang paraan:
Well, sa wakas, ito ay nananatiling lamang upang bunutin ang mga pangalan mula sa listahan sa pamamagitan ng kanilang mga numero. Upang gawin ito, maaari mong gamitin ang sumusunod na formula:
tungkulin MAS EXPOSED (MATCH) naghahanap sa column B para sa gustong serial number (1, 2, 3, atbp.) at, sa katunayan, ibinabalik ang numero ng linya kung saan matatagpuan ang numerong ito. Function INDEX (INDEX) hinuhugot mula sa hanay A ang pangalan sa numero ng linyang ito.
Paraan 3: Array Formula
Ang pamamaraang ito ay, sa katunayan, ang parehong algorithm ng pagkakalagay tulad ng sa Paraan-2, ngunit ipinatupad ng isang array formula. Upang gawing simple ang formula, ang hanay ng mga cell C1:C10 ay binigyan ng pangalan listahan (piliin ang mga cell, pindutin ang Ctrl + F3 at pindutan Lumikha):
Sa cell E1, kopyahin ang aming formula:
=INDEX(Listahan; MATCH(MALIIT(COUNTIF(List; “<"&List); ROW(1:1)); COUNTIF(List; "<"&List); 0))
O sa Ingles na bersyon:
=INDEX(Listahan, MATCH(MALIIT(COUNTIF(Listahan, «<"&Listahan), ROW(1:1)), COUNTIF(Listahan, "<"&Listahan), 0))
at itulak Ctrl + Shift + Enterupang ipasok ito bilang isang array formula. Pagkatapos ay maaaring kopyahin ang resultang formula sa buong haba ng listahan.
Kung gusto mong isaalang-alang ang formula na hindi isang nakapirming saklaw, ngunit magagawa mong ayusin kapag nagdaragdag ng mga bagong elemento sa listahan, kakailanganin mong bahagyang baguhin ang diskarte.
Una, ang hanay ng Listahan ay kailangang dynamic na itakda. Upang gawin ito, kapag lumilikha, kailangan mong tukuyin hindi isang nakapirming hanay na C3:C10, ngunit isang espesyal na formula na magre-refer sa lahat ng magagamit na mga halaga, anuman ang kanilang numero. I-click Alt + F3 o buksan ang tab Mga Formula – Name Manager (Mga Formula — Name Manager), lumikha ng bagong pangalan at sa field link (Sanggunian) ipasok ang sumusunod na formula (Ipagpalagay ko na ang hanay ng data na pag-uuri-uriin ay nagsisimula sa cell C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
Pangalawa, ang formula ng array sa itaas ay kailangang i-stretch pababa na may margin – na may inaasahan ng karagdagang data na ipinasok sa hinaharap. Sa kasong ito, ang array formula ay magsisimulang magbigay ng error na #NUMBER sa mga cell na hindi pa napupunan. Upang harangin ito, maaari mong gamitin ang function IFERROR, na kailangang idagdag "sa paligid" ng aming array formula:
=IFERROR(INDEX(Listahan; MATCH(MALIIT(COUNTIF(Listahan; “<"&Listahan); ROW(1:1)); COUNTIF(Listahan; "<"&List); 0));»»)
=IFERROR(NDEX(List, MATCH(SMALL(COUNTIF(List, «<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0));"")
Nakukuha nito ang #NUMBER error at nag-output ng void (empty quotes) sa halip.
:
- Pagbukud-bukurin ang hanay ayon sa kulay
- Ano ang mga array formula at bakit kailangan ang mga ito
- SORT sorting at dynamic arrays sa bagong Office 365