Datu kārtošana programmā Excel ir ļoti noderīgs rīks, kas ļauj uzlabot informācijas uztveri, īpaši liela apjoma gadījumā. Šajā nodarbībā apgūsim kārtošanas lietošanu, apgūsim pamata komandas, kā arī iepazīsimies ar šķirošanas veidiem programmā Excel.

Pievienojot datus programmai Excel, ir ļoti svarīgi pareizi sakārtot informāciju darblapā. Viens rīks, kas ļauj to izdarīt, ir šķirošana. Izmantojot kārtošanu, var izveidot kontaktinformācijas sarakstu pēc uzvārda, sakārtot tabulas saturu alfabētiskā secībā vai dilstošā secībā.

Kārtojot datus programmā Excel, vispirms jāizlemj, kā lietot kārtošanu: visai darblapai (tabulai) vai tikai noteiktam šūnu diapazonam.

Kā kārtot lapu (tabulu, sarakstu) programmā Excel

Nākamajā piemērā T-kreklu pasūtījuma veidlapu sakārtosim pēc Uzvārdi(C kolonna) un sakārtojiet tos alfabētiskā secībā.


Kārtojot tabulu vai sarakstu programmā Excel, tas ir jāatdala no svešiem datiem darblapā ar vismaz vienu rindu vai kolonnu. Pretējā gadījumā šķirošanā tiks iesaistīti sveši dati.

Kā kārtot diapazonu programmā Excel

Nākamajā piemērā mēs Excel darblapā atlasīsim atsevišķu nelielu tabulu, lai sakārtotu noteiktās dienās pasūtīto T-kreklu skaitu.


Ja kārtošana programmā Excel netiek veikta pareizi, vispirms pārbaudiet, vai ievadītās vērtības ir pareizas. Pat neliela drukas kļūda var radīt problēmas, šķirojot lielas tabulas. Nākamajā piemērā mēs aizmirsām ievietot defisi šūnā A18, kā rezultātā tika iegūta neprecīza kārtošana.

Lai sakārtotu un analizētu datus sarakstā, izmantojiet komandu Kārtot. Kārtot ļauj pārkārtot ierakstus noteiktā secībā, pamatojoties uz vienas vai vairāku kolonnu vērtībām, vai pārkārtot kolonnas, pamatojoties uz vērtībām rindās.

Ir trīs šķirošanas veidi:

  • augošā secībā
  • dilstošā secībā
  • pielāgotā kārtībā

Kārtot sarakstu augošā secībā nozīmē sakārtot sarakstu šādā secībā: no 0 līdz 9, atstarpes, simboli, burti A līdz Z vai A līdz Z, un dilstošā secībā apgrieztā secībā. Pielāgoto kārtošanas secību lietotājs norāda dialoglodziņā "Opcijas" cilnē "Saraksti", kas tiek atvērta ar komandu "Opcijas" izvēlnē "Rīki", un šī kārtošanas secība tiek parādīta sadaļā "Kārtošanas opcijas". " dialoglodziņš (1. att.).


Rīsi. 1.

Lai parādītu dialoglodziņus "Sorting Range", "Sorting Options" un izvēlētos kolonnu vai rindu kārtošanas secību, ir jāizvēlas saraksta šūna un jāizpilda komanda "Dati" - "Sorting". Ekrānā parādīsies logs "Sorting Range", noklikšķinot uz pogas "Opcijas", parādīsies logs "Sorting Options".

Logā "Kārtošanas opcijas" (2. att.) no saraksta "Kārtošana pēc primārās atslēgas" varat izvēlēties pielāgotu kārtošanas secību, kā arī iestatīt slēdzi "Kārtot" pozīcijā: "diapazona rindas" vai "diapazons". kolonnas".


Rīsi. 2.

Logā "Diapazona šķirošana" (3. att.) varat iestatīt slēdzi pozīcijā: "augšupejošs" vai "dilstošs", kā arī izvēlēties datu diapazona identifikācijas slēdža pozīciju.


Rīsi. 3.

Ja paraksti ir formatēti atbilstoši iepriekš minētajām prasībām, pēc noklusējuma slēdzis ir iestatīts uz "paraksti". Turklāt sarakstos Kārtot pēc, Pēc tam un Pēdējā pēc varat atlasīt kolonnu galvenes, pēc kurām kārtot. Tādējādi ierakstus var kārtot pēc vienas, divām vai trim kolonnām.

Tūlītēja ierakstu kārtošana pēc vienas kolonnas tiek veikta, tajā atlasot šūnu, standarta rīkjoslā noklikšķinot uz pogas "Kārtot augošā secībā" vai "Kārtot dilstošā secībā". Rezultātā redaktors pārkārtos ierakstus atbilstoši izvēlētajai secībai.

Algoritms ierakstu kārtošanai pēc vienas kolonnas ir šāds:

  • Sarakstā atlasiet šūnu, kuru vēlaties kārtot;
  • Izpildiet komandu "Dati" - "Sorting", atveras dialoglodziņš "Sorting Range";
  • Sarakstā "Kārtot pēc" atlasiet kolonnas virsrakstu, pēc kuras veiksiet kārtošanu;
  • Izvēlieties šķirošanas veidu "augošā" vai "dilstošā";

4. un 5. attēlā parādīti saraksta fragmenti pirms šķirošanas un pēc šķirošanas “augšup” pa vienu kolonnu “noliktavas numurs”.


Rīsi. 4.


Rīsi. 5.

Kārtojot sarakstu pēc vairākām kolonnām, rindas ar vienādām vērtībām pirmajā sakārtotajā kolonnā (Sort By) tiek sakārtotas secībā, ko nosaka sarakstā Pēc tam norādītā galvene, un rindas ar vienādām vērtībām sarakstā pirmās divas sakārtotās kolonnas tiek sakārtotas pēc nosaukuma, kas norādīts sarakstā "Pēdējais pēc". Citiem vārdiem sakot, kolonnas, kas norādītas sarakstos Nākamais pēc un Pēdējais pēc, neietekmē pirmās kārtojamās kolonnas kārtošanas secību, bet gan atšķir kārtoto sarakstu.

Algoritms ierakstu kārtošanai pēc divām vai vairākām kolonnām ir šāds

  • Sarakstā atlasiet šūnu;
  • Sarakstā "Kārtot pēc" atlasiet kārtojamo virsrakstu un iestatiet kārtošanas secību uz "augšupejošu" vai "dilstošu";
  • Atveriet sarakstu “Tad By”, iestatiet citas kolonnas galveni, lai kārtotu, un iestatiet kārtošanu uz “augšupejoša” vai “dilstoša”;
  • Paplašiniet sarakstu "Pēdējais pēc" un atlasiet trešās kolonnas virsrakstu, lai kārtotu, un norādiet kārtošanu kā "augšupejošu" vai "dilstošu";
  • Noklikšķiniet uz Labi, lai veiktu kārtošanu.

Algoritms datu kārtošanai pēc rindām

Lai pārkārtotu kolonnas sarakstā, jums ir jākārto, pamatojoties uz rindu vērtībām:

  • Norādiet kārtojamo šūnu sarakstā;
  • Izvēlnē "Dati" izvēlieties komandu "Sorting";
  • Diapazona kārtošanas logā noklikšķiniet uz pogas Opcijas;
  • Iestatiet radio pogu Kārtot uz Range Columns un noklikšķiniet uz Labi;
  • Logā Kārtošanas diapazons atlasiet rindas, pēc kurām vēlaties kārtot kolonnas sarakstos Kārtot pēc, Pēc tam vai Pēdējā pēc.
  • Noklikšķiniet uz Labi, lai kārtotu

Dažos gadījumos saraksti ir jākārto nestandarta secībā, piemēram, jākārto dati pa mēnešiem kalendāra secībā vai jākārto ieraksti lietotāja izveidotā secībā. Pielāgota pasūtījuma izveide tiek veikta cilnes "Saraksti" logā "Parametri", kuru var izsaukt ar komandu "Rīki" - "Opcijas". Pielāgotu kārtošanas secību var lietot tikai kolonnas galvenē, kas norādīta nolaižamajā sarakstā Kārtot pēc

Kārtošanas algoritms pielāgotā secībā

  • Sarakstā atlasiet šūnu;
  • Izvēlnē "Dati" izvēlieties komandu "Sorting";
  • Sarakstā "Kārtot pēc" atlasiet slejas galveni, ko kārtot;
  • Iestatiet slēdzi pozīcijā "augošā" vai "dilstošā";
  • Noklikšķiniet uz pogas "Opcijas";
  • Nolaižamajā sarakstā "Kārtot pēc pirmās atslēgas" atlasiet vajadzīgo kārtošanas secību un noklikšķiniet uz Labi;
  • Lai pabeigtu kārtošanu, logā Sort Range noklikšķiniet uz Labi.

Šķirošana programmā Excel ir iebūvēta funkcija, ar kuras palīdzību lietotājs var sakārtot datus lapas kolonnās ērtā secībā turpmākai analīzei.

Informāciju var kārtot alfabētiskā secībā, pēc augošām vai dilstošām vērtībām, pēc datuma vai ikonas, pēc teksta vai šūnas krāsas. Tieši par to tiks runāts šajā rakstā.

Skaitļi

Šeit viss ir pavisam vienkārši. Kā piemēru ņemsim šādu tabulu. Sakārtosim datus pēc C kolonnas. Lai to izdarītu, atlasiet to un cilnē "Sākums" noklikšķiniet uz pogas "Kārtošana un filtrēšana". Nākamajā izvēlnē atlasiet vai "...no minimuma uz maksimālo", vai "...no maksimālā līdz minimumam". Izvēlēsimies otro variantu.

Tagad mums ir dati C, kas sakārtoti dilstošā secībā.

Man ir C kolonna, kas atrodas starp divām citām, kas ir aizpildītas ar datiem. Šajā gadījumā programma Excel uzskata atlasīto kolonnu par tabulas daļu (un skaita pareizi). Rezultātā parādījās šāds ziņojums. Tā kā man ir jāšķiro speciāli klasei, es izceļu vienumu ar marķieri "...norādītā piešķīruma ietvaros" un noklikšķiniet uz "Kārtot".

Alfabētiski

Tas tiek darīts saskaņā ar to pašu principu, kā aprakstīts iepriekš. Izvēlieties vajadzīgo diapazonu un nospiediet pogu "Kārtošana un filtrēšana". Vienumi nolaižamajā izvēlnē ir mainīti. Izvēlieties no “A līdz Z” vai no “Z līdz A”.

Vārdu saraksts piemērā ir sakārtots alfabētiskā secībā.

Pēc datuma

Lai kārtotu datumus programmā Excel, vispirms pievērsiet uzmanību tam, kāds formāts ir iestatīts šūnām, kurās tie ir rakstīti. Atlasiet tos un cilnē "Sākums" skatiet grupu "Numurs". Labākais formāts ir “Datums”, īss vai garš, vai “(visi formāti)” — datumu var rakstīt dažādos veidos: DD.MM.GGGG, DD.MMM, MMM.GG.

Šis punkts ir ļoti svarīgs, jo pretējā gadījumā datumus var kārtot vienkārši augošā secībā ar pirmajiem diviem cipariem vai pa mēnešiem alfabēta secībā.

Pēc tam atlasiet vajadzīgo šūnu diapazonu un noklikšķiniet uz pogas "Kārtošana un filtrēšana". Izvēlnē varat izvēlēties vai "no vecā uz jauno", vai "no jauna uz veco".

Pēc šūnas vai teksta krāsas

Šo metodi var izmantot, ja Excel tabulas šūnās ir teksts vai pašas šūnas ir nokrāsotas dažādās krāsās. Piemēram, ņemsim skaitļu kolonnu, kas nokrāsota dažādās krāsās. Sakārtosim tā, lai vispirms būtu sarkanā krāsā iekrāsotie skaitļi, pēc tam zaļie un melnie.

Izvēlieties visu diapazonu un noklikšķiniet uz pogas "Kārtošana un filtrēšana" un izvēlieties no izvēlnes "Pielāgojams...".

Nākamajā logā noņemiet atzīmi no izvēles rūtiņas, ja atlasījāt tos bez augšējās rindas, kas ir tabulas galvene. Pēc tam mēs atlasām kolonnu, pēc kuras kārtosim, piemērā tas ir “I”. Sadaļas “Kārtošana” nolaižamajā sarakstā atlasiet “Fonta krāsa”. Pasūtījumu sadaļā atlasiet “sarkanā krāsa” - “Augšējais”. Mēs sakārtojām skaitļus sarkanā krāsā.

Tagad kolonnā ir jābūt zaļiem cipariem. Noklikšķiniet uz pogas "Pievienot līmeni". Visi iestatījumi ir vienādi, vienkārši atlasiet “zaļā krāsa”. Noklikšķiniet uz Labi.

Mūsu kolonna ir sakārtota šādi.

Kā redzat, skaitļi nav sakārtoti. Sakārtosim skaitļus augošā secībā. Atlasiet kolonnu un noklikšķiniet uz "Kārtošana un filtrēšana" – "Pielāgojams...". Atvērtajā logā noklikšķiniet uz pogas "Pievienot līmeni". Kolonna paliek “I”, nākamajā laukā atlasām pēc “Vērtība”, secība "Augšupejoša". Noklikšķiniet uz Labi.

Tagad mūsu kolonna ir sakārtota gan pēc teksta krāsas, gan datu augošā secībā.

Dati tiek kārtoti tādā pašā veidā pēc šūnu krāsas, tikai sadaļā “Kārtošana” sarakstā atlasiet “Šūnas krāsa”.

Tabulas

Ja jums ir tabula, kurā jākārto pēc vairākām kolonnām vienlaikus, rīkojieties šādi. Atlasiet visu tabulas šūnu diapazonu kopā ar galveni. Noklikšķiniet uz pogas "Kārtošana un filtrēšana" un izvēlēties "Pielāgojams...".

Sakārtosim klasi augošā secībā un tādā pašā veidā vidējo punktu skaitu.

Šķirošanas logā atzīmējiet izvēles rūtiņu "Mani dati satur galvenes". Sadaļā “Kolonna” sarakstā atlasiet “Klase”, kārtojiet pēc “Vērtības” un sakārtojiet "Augšupejoša".

Lai to pašu izdarītu ar vidējo punktu skaitu, noklikšķiniet uz pogas "Pievienot līmeni". Sadaļā “Sleja” atlasiet “Vidējais vērtējums”. Noklikšķiniet uz Labi.

Dati tabulā ir sakārtoti.

Tagad kolonnā “Vārds” krāsojiet šūnas ar zēniem zilā krāsā un šūnas ar meitenēm rozā krāsā. Lai to nedarītu katrai šūnai atsevišķi, izlasiet rakstu par šūnu atlasi programmā Excel — tajā ir norādīts, kā atlasīt šūnas, kas nav blakus.

Sakārtosim šo kolonnu pēc šūnu krāsas: vispirms būs meitenes, tad zēni. Atkal atlasiet visu tabulu, noklikšķiniet uz “Kārtot” - "Pielāgojams...".

Atvērtajā logā jau ir divi līmeņi, kurus mēs izveidojām iepriekš. Šiem līmeņiem ir prioritāte – pirmais ir lielākais, otrais ir mazāks utt. Tas ir, ja mēs vēlamies, lai dati meiteņu/puišu tabulā vispirms tiktu kārtoti, pēc tam pēc klases un pēc tam pēc vidējā rezultāta, līmeņi ir jāsakārto šādā secībā.

Noklikšķiniet uz pogas "Pievienot līmeni". Sadaļā “Kolonna” atlasiet “Nosaukums”, kārtojiet – “Šūnas krāsa”, secība – “rozā”, “Augšā”.

Tagad, izmantojot bultiņas, pārvietojiet šo rindiņu uz saraksta augšdaļu. Noklikšķiniet uz Labi.

Tabula ar sakārtotiem datiem izskatās šādi.

Kārtojot datus programmā Excel, var rasties nepieciešamība kārtot datus vairākās kolonnās vienlaikus. Šodien es jums pastāstīšu, kā to izdarīt.

Kā piemēru ņemsim tabulu, kurā ir produktu saraksts ar to izmaksām un derīguma termiņu. Uzdevums ir sakārtot preces alfabētiskā secībā un pēc derīguma termiņa beigām.

Atlasiet visas tabulas šūnas, kurām piemērosim kārtošanu. Mūsu gadījumā jūs varat vienkārši stāvēt uz jebkura diapazona šūnas. Tālāk cilnē mājas noklikšķiniet uz ikonas Šķirošana un filtrēšana un nolaižamajā izvēlnē atlasiet vienumu Pielāgota šķirošana.

Tiks atvērts kārtošanas iestatījumu logs. Pirmā lieta, kas jums jādara, ir pārbaudīt, vai ir atzīmēta izvēles rūtiņa Manos datos ir galvenes. Šis iestatījums ļauj noņemt pirmo rindu no diapazona un nepiedalīties turpmākajā filtrēšanā.

Tagad mēs pievienosim kolonnas, pēc kurām notiks šķirošana. Pirmā, mūsu gadījumā, ir kolonna Produkti. Laukā Šķirošana mēs eksponējam Nozīme un instalēt Pasūtiet no A līdz Z.

Tagad produkti tiks sakārtoti vispirms pēc pirmās kolonnas un papildus pēc otrās. Šajā gadījumā otrā šķirošana neietekmē pirmo. Tādā pašā veidā varat kārtot pēc vairākām kolonnām.

Starp citu, šķirošanu var veikt arī pa rindām. Kā to izdarīt, es jums pastāstīšu nākamajā nodarbībā.

Divās iepriekšējās nodarbībās mēs noņēmām dublikātus. Jūs varat lasīt arī par šo. Šajā nodarbībā mēs īstenosim meklēt dublikātus.

Šajā nodarbībā es jums pastāstīšu, kā katrai Excel lapai pievienot galveni. Tas var būt nepieciešams, ja strādājat ar lieliem galdiem. Parasti, tos drukājot, tiek izgatavoti dokumenti ar lielu lapu skaitu, un, lai ar tiem būtu ērti strādāt, katrā lapā ir jāsaglabā tabulas galvene.

Šajā nodarbībā es jums pastāstīšu, kā Excel šūnas sākumā pirms skaitļa ievietot pluszīmi vai nulli. Iedomāsimies situāciju, kad šūnā jāievada tālruņa numurs formātā "+7 987...". Parasti Excel vienkārši noņems šo pluszīmi.

Sakārtosim tabulu, kas sastāv no 2 kolonnām, izmantojot formulas. Kārtosim pēc vienas no tabulas kolonnām (risināsim 2 uzdevumus: tabulas kārtošanu pēc cipariem un kārtošanu pēc teksta kolonnas). Sakārtosim kārtošanas formulas tā, lai, pievienojot avota tabulai jaunus datus, sakārtotā tabula dinamiski mainītos. Tas ļaus jums vienmēr sakārtot tabulu bez lietotāja iejaukšanās. Mēs veiksim arī divu līmeņu kārtošanu: vispirms pēc skaitļiem, pēc tam (skaitļu atkārtošanai) pēc teksta kolonnas.

Lai būtu tabula, kas sastāv no 2 kolonnām. Viena kolonna ir teksts: Augļu saraksts; un otrais ir skaitlisks Pārdošanas apjoms(skatiet parauga failu).

1. uzdevums (tabulas kārtošana pēc skaitļu kolonnas)

Tabulas rindas jāsakārto pēc ciparu kolonnas satura (pēc pārdošanas apjoma). Tiek pieņemts, ka lietotājs nepārtraukti aizpilda tabulas rindas, tāpēc ir nepieciešams rakstīt formulas, ņemot vērā pievienotās vērtības.

Skaidrības labad vērtības kolonnā Pārdošanas apjoms iezīmēts ar (). Atkārtotas vērtības ir arī iezīmētas dzeltenā krāsā.

Piezīme: Vienas kolonnas (saraksta) kārtošanas problēma tika atrisināta rakstos un.

Risinājums 1

Ja tiek garantēts, ka ciparu kolonnā nav vērtību, problēmu ir viegli atrisināt:

  • Kārtojiet ciparu kolonnu, izmantojot funkciju LARGE() (skatiet rakstu);
  • Funkcija VLOOKUP() vai virkne funkciju INDEX()+MATCH() atlasiet vērtības no teksta kolonnas pēc atbilstošās skaitliskās vērtības.

Taču reālos uzdevumos ciparu kolonnā var būt atkārtojumi, un, tā kā funkcija VLOOKUP() atkārtojumu gadījumā vienmēr atlasa tikai pirmo vērtību no augšas (skat. rakstu), šī pieeja nav piemērota ( augļi tiks parādīti nepareizi).

Līdz ar to šķirošanas mehānisms būs jāīsteno citādi.

INDEKSS(Pārdošana;
ROUND(REMAT(LARGEST(
---(COUNTIF(Pārdošana;"<"&Продажи)&","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6));
ROW()-ROW($E$6));1)*1000;0)
)

Šī formula kārto kolonnu Pārdošanas apjoms(dinamiskais diapazons Pārdošana) lejupejoši. Iztrūkumi avota tabulā nav atļauti. Avota tabulas rindu skaitam ir jābūt mazākam par 1000.

Apskatīsim formulu sīkāk:

  • Formula COUNTIF(Pārdošana;"<"&Продажи) atgriež masīvu (4:5:0:2:7:1:3:5). Tas nozīmē, ka skaitlis 64 (no šūnas B7 oriģinālā tabula, t.i. pirmais numurs no diapazona Pārdošana) vairāk nekā 4 vērtības no tā paša diapazona; numurs 74 (no šūnas B8 oriģinālā tabula, t.i. otrais numurs no diapazona Pārdošana) vairāk nekā 5 vērtības no tā paša diapazona; nākamais cipars 23 ir mazākais (tas nav lielāks par jebkuru) utt.
  • Tagad pārvērtīsim iepriekš minēto veselo skaitļu masīvu par skaitļu masīvu ar daļēju daļu, kur daļējā daļā būs masīva skaitļa pozīcijas numurs: (4,001:5,002:0,003:2,004:7,005:1,006:3,007:5,008 ). Tas tiek īstenots ar izteiksmi &","&ATKĀRTOT("0";3-GARUMS(RINDA(Pārdošana)-RINDA($6.$)))&RINDA(Pārdošana)-RINDA($6.$)) Tieši šajā formulas daļā avota tabulā ir ierobežojums, kas nepārsniedz 1000 rindas (skatīt iepriekš). Ja vēlaties, to var viegli mainīt, taču tas ir bezjēdzīgi (skatiet sadaļu par skaitļošanas ātrumu zemāk).
  • Funkcija LARGE () sakārto iepriekš minēto masīvu.
  • Funkcija ROD() atgriež skaitļa daļēju daļu, kas ir pozīcijas numuri/1000, piemēram, 0,005.
  • Funkcija ROUND() pēc reizināšanas ar 1000 noapaļo līdz veselam skaitlim un atgriež pozīcijas numuru. Tagad visi pozīciju numuri atbilst kolonnu numuriem Pārdošanas apjomi, sakārtoti dilstošā secībā.
  • Funkcija INDEX() ar pozīcijas numuru atgriež tai atbilstošo skaitli.

Līdzīgu formulu var uzrakstīt, lai kolonnā parādītu vērtības Augļi=INDEKSS(augļi,APAĻA(...))

Piemēra failā aprēķinu ātruma apsvērumu dēļ (skat. zemāk) tāda paša veida formulas daļa, t.i. viss funkcijas ROUND() iekšpusē ir ievietots atsevišķā kolonnā Dž . Tāpēc galīgās formulas sakārtotajā tabulā izskatās šādi: =INDEKSS(augļi,J7) Un =INDEKSS(pārdošana,J7)

Tāpat, mainot funkciju LARGE() uz SMALL() masīva formulā, mēs iegūstam kārtošanu augošā secībā.

Skaidrības labad vērtības kolonnā Pārdošanas apjoms iezīmēts ar ( Sākums/ Stili/ Nosacītā formatēšana/ Histogrammas). Kā redzat, šķirošana darbojas.

Testēšana

Tagad pievienosim oriģinālajai tabulai jaunu rindu. Dinamiski sakārtotajās tabulās mums ir jāiegūst atbilstoša šķirošana.

1. Uz šūnu A15 avota tabulā ievadiet vārdu Burkāns;
2. Uz šūnu B15 ievadiet Pārdošanas apjoms Burkāni = 25;
3. Pēc vērtību ievadīšanas kolonnās D Un E automātiski tiks parādīta tabula, kas sakārtota dilstošā secībā;
4. Sakārtotā tabulā jaunā rinda tiks parādīta pirms pēdējā.

Formulas aprēķināšanas ātrums

"Vidējā" datorā veiktspējas ziņā pārrēķins par tādu masīvu formulas, atrodas 100 rindās, ir praktiski neredzams. Tabulām ar 300 rindām pārrēķina laiks aizņem 2-3 sekundes, kas ir neērti. Vai arī jums ir jāatspējo automātiskais lapas pārrēķins ( Formulas/Aprēķini/Aprēķinu opcijas) un periodiski nospiediet taustiņu F9, vai nu atteikties no masīvu formulu izmantošanas, aizstājot tās ar kolonnām ar atbilstošām formulām, vai arī pilnībā atteikties no dinamiskās kārtošanas par labu standarta pieeju izmantošanai (skatiet nākamo sadaļu).

Alternatīvas pieejas tabulu šķirošanai

Sakārtosim avota tabulas rindas, izmantojot standarta filtru (atlasiet avota tabulas galvenes un noklikšķiniet uz CTRL+SHIFT+L). Nolaižamajā sarakstā atlasiet vajadzīgo kārtošanu.

Mēs iegūsim tabulas versiju, kas ir identiska mūsējai, taču, pievienojot tabulai jaunas vērtības, filtrs būs jāpiemēro vēlreiz.

Varat arī izmantot rīku Kārtot ( Dati/Kārtot un Filtrēt/Kārtot). Lai to izdarītu, jums ir jāatlasa visas avota tabulas vērtības, izņemot galveni, jāizsauc rīks Kārtot, jāatlasa kolonna, pēc kuras vēlaties kārtot, un kārtošanas opcija.

Mēs iegūsim tabulas versiju, kas ir identiska mūsējai, taču, pievienojot jaunas vērtības, mums būs arī vēlreiz jāpiemēro filtrs.

Tāpat kā iepriekšējā uzdevumā, pieņemsim, ka kolonnā, pēc kuras tiek veikta šķirošana, ir atkārtojumi (augļu nosaukumi tiek atkārtoti).

Lai sakārtotu tabulu, jums būs jāizveido 2 pakalpojumu kolonnas (D un E).

=COUNTIF($B$7:$B$14;"<"&$B$7:$B$14)+1

Šī formula ir analoga teksta vērtībām (vērtības pozīcija attiecībā pret citām vērtībām sarakstā). Teksta vērtībai, kas ir zemāka alfabētā, ir augstāks "rangs". Piemēram, vērtība Apples atbilst maksimālajam “rangam” 7 (ieskaitot atkārtojumus).

E kolonnā ievadiet parasto formulu:

=COUNTIF($D$6:D6,D7)+D7

Šī formula ņem vērā teksta vērtību atkārtojumus un pielāgo "rangu". Tagad dažādas ābolu vērtības atbilst dažādām "pakāpēm" - 7 un 8. Tas ļauj parādīt sakārtoto vērtību sarakstu. Lai to izdarītu, izmantojiet formulu (G kolonna):

=INDEKSS($B$7:$B$14, ATTIECĪBAS(RINDA()-RINDA($G$6),$E$7:$E$14,0))

Līdzīga formula parādīs atbilstošo pārdošanas apjomu (H kolonna).

2.1. problēma (divu līmeņu šķirošana)

Tagad atkal kārtosim sākotnējo tabulu pēc pārdošanas apjoma. Bet tagad atkārtotām vērtībām (A slejā ir trīs vērtības 74) mēs parādīsim atbilstošās vērtības alfabētiskā secībā.

Lai to izdarītu, mēs izmantosim 1.1. un 2. uzdevuma rezultātus.

Sīkāka informācija parauga failā uz lapas Task2.