Turinys
Jei jums reikia rūšiuoti sąrašą, jūsų paslaugoms yra daugybė būdų, iš kurių paprasčiausias yra rūšiavimo mygtukai skirtuke arba meniu. Duomenys (Duomenys – Rūšiuoti). Tačiau yra situacijų, kai sąrašo rūšiavimas turi būti atliekamas automatiškai, ty formulės. To gali prireikti, pavyzdžiui, generuojant duomenis išskleidžiamam sąrašui, skaičiuojant duomenis diagramoms ir pan. Kaip surūšiuoti sąrašą pagal formulę skrydžio metu?
1 metodas. Skaitiniai duomenys
Jei sąraše yra tik skaitinė informacija, jį galima lengvai surūšiuoti naudojant funkcijas MAŽIAUSIAI (MAŽAS) и LINIJA (ROW):
Funkcija MAŽIAUSIAI (MAŽAS) ištraukia iš masyvo (A stulpelis) n-tą mažiausią elementą iš eilės. Tie. SMALL(A:A;1) yra mažiausias skaičius stulpelyje, SMALL(A:A;2) yra antras pagal mažumą ir pan.
Funkcija LINIJA (ROW) grąžina nurodyto langelio eilutės numerį, ty ROW(A1)=1, ROW(A2)=2 ir tt Šiuo atveju jis naudojamas tiesiog kaip skaičių sekos n=1,2,3… generatorius. mūsų surūšiuotas sąrašas. Taip pat pavyko sukurti papildomą stulpelį, jį rankiniu būdu užpildyti skaitine seka 1,2,3 … ir nurodyti jį vietoj funkcijos ROW.
2 metodas. Tekstinis sąrašas ir reguliarios formulės
Jei sąraše yra ne skaičiai, o tekstas, tada funkcija SMALL nebeveiks, todėl tenka eiti kitu, kiek ilgesniu, keliu.
Pirmiausia pridėkime paslaugų stulpelį su formule, kurioje kiekvieno pavadinimo eilės numeris būsimame surūšiuotame sąraše bus apskaičiuojamas naudojant funkciją COUNTIF (COUNTIF):
Angliškoje versijoje tai bus:
=SKAIČIUSIF(A:A,<"&A1)+COUNTIF($1:A1,"="&A1)
Pirmasis terminas yra funkcija, skirta skaičiuoti langelių skaičių, kuris yra mažesnis už dabartinį. Antrasis yra apsauginis tinklas, jei kuris nors pavadinimas pasitaiko daugiau nei vieną kartą. Tada jie turės ne tą patį, o nuosekliai didėjantį skaičių.
Dabar gauti skaičiai turi būti išdėstyti iš eilės didėjančia tvarka. Tam galite naudoti funkciją MAŽIAUSIAI (MAŽAS) iš pirmo būdo:
Na, pagaliau belieka ištraukti vardus iš sąrašo pagal jų skaičių. Norėdami tai padaryti, galite naudoti šią formulę:
Funkcija DAUGIAU ATSKIRTA (MATCH) B stulpelyje ieško norimo serijos numerio (1, 2, 3 ir kt.) ir iš tikrųjų pateikia eilutės, kurioje yra šis numeris, numerį. Funkcija RODYKLĖ (INDEKSAS) iš A stulpelio ištraukia pavadinimą ties šios eilutės numeriu.
3 būdas: masyvo formulė
Šis metodas iš tikrųjų yra tas pats išdėstymo algoritmas, kaip ir 2 metode, bet įgyvendinamas masyvo formule. Siekiant supaprastinti formulę, langelių diapazonas C1:C10 buvo pavadintas sąrašas (pasirinkite langelius, paspauskite "Ctrl" + F3 ir mygtuką kurti):
E1 langelyje nukopijuokite formulę:
=INDEX(sąrašas; MATCH(SMALL(COUNTIF(sąrašas; “<"&sąrašas); ROW(1:1)); COUNTIF(sąrašas; "<"&sąrašas); 0))
Arba anglų kalba:
=INDEX(sąrašas, ATITIKTIS(MAŽAS(SKAIČIUS(Sąrašas, «<"&sąrašas), ROW(1:1)), COUNTIF(sąrašas, "<"&sąrašas), 0))
ir stumti Ctrl + Shift + Enternorėdami įvesti jį kaip masyvo formulę. Tada gautą formulę galima nukopijuoti per visą sąrašo ilgį.
Jei norite, kad formulėje būtų atsižvelgta ne į fiksuotą diapazoną, bet būtų galima koreguoti įtraukiant naujus elementus į sąrašą, tuomet turėsite šiek tiek pakeisti strategiją.
Pirma, sąrašo diapazoną reikės nustatyti dinamiškai. Norėdami tai padaryti, kurdami turite nurodyti ne fiksuotą diapazoną C3:C10, o specialią formulę, kuri nurodys visas turimas reikšmes, nepaisant jų skaičiaus. Spustelėkite Alt + F3 arba atidarykite skirtuką Formulės – Vardų tvarkyklė (Formulės – vardų tvarkyklė), sukurkite naują pavadinimą ir lauke ryšys (Nuoroda) įveskite šią formulę (manau, kad rūšiuojamų duomenų diapazonas prasideda nuo langelio C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
Antra, aukščiau pateiktą masyvo formulę reikės ištempti su parašte – tikintis, kad ateityje bus įvestų papildomų duomenų. Tokiu atveju masyvo formulė dar neužpildytose langeliuose pradės rodyti klaidą #NUMBER. Norėdami jį perimti, galite naudoti funkciją IFERROR, kurią reikia pridėti „aplink“ mūsų masyvo formulę:
=IFERROR(INDEX(sąrašas; MATCH(SMALL(COUNTIF(sąrašas; “<"&sąrašas); ROW(1:1)); COUNTIF(sąrašas; "<"&sąrašas); 0));»»)
=IFERROR(NDEX(sąrašas, MATCH(SMALL(COUNTIF(sąrašas, «<"&sąrašas), ROW(1:1)), COUNTIF(sąrašas, "<"&sąrašas), 0));"")
Ji užfiksuoja klaidą #NUMBER ir vietoj jos išveda tuštumą (tuščias kabutes).
:
- Rūšiuoti asortimentą pagal spalvą
- Kas yra masyvo formulės ir kodėl jos reikalingos
- RŪŠIUOTI rūšiavimą ir dinaminius masyvus naujajame „Office 365“.