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 pielietoš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. Ar kārtošanas palīdzību 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, vai kārtošanu lietot 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, tā ir jāatdala no svešiem datiem darblapā vismaz ar 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 vērtības ir ievadītas pareizi. Pat neliela drukas kļūda var radīt problēmas lielu tabulu šķirošanas laikā. Nākamajā piemērā mēs aizmirsām ievietot defisi šūnā A18, kā rezultātā tika iegūta neprecīza kārtošana.

Izmantojiet komandu Kārtot, lai kārtotu un analizētu datus sarakstā. "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ā

Saraksta kārtošana augošā secībā nozīmē saraksta sakārtošanu secībā: no 0 līdz 9, atstarpes, rakstzīmes, burti A līdz Z vai A līdz Z un dilstoša apgrieztā secībā. Pielāgotu kārtošanas secību lietotājs norāda dialoglodziņā Opcijas cilnē Lists, kas tiek atvērts ar komandu Opcijas izvēlnē Rīki, un šī kārtošanas secība tiek parādīta dialoglodziņā Kārtošanas opcijas (1. attēls).


Rīsi. 1.

Lai parādītu dialoglodziņus "Sort Range", "Sort Options" un atlasītu kolonnu vai rindu kārtošanas secību, atlasiet saraksta šūnu un izpildiet komandu "Dati" - "Sort". Noklikšķinot uz pogas "Opcijas", ekrānā parādīsies logs "Kārtošanas opcijas", parādīsies logs "Kārtošanas opcijas".

Logā "Kārtošanas opcijas" (2. att.) no saraksta "Kārtot pēc primārās atslēgas" var 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 etiķetes ir formatētas atbilstoši iepriekš minētajām prasībām, noklusējuma radio poga ir iestatīta uz "paraksti". Turklāt sarakstos: "Kārtot pēc", "Tad pēc" un "Pēdējais, pēc" varat atlasīt sleju virsrakstus, pēc kuriem kārtot. Tādējādi ierakstus var kārtot pēc vienas, divām vai trim kolonnām.

Tūlītēja ierakstu šķirošana pēc vienas kolonnas tiek veikta, tajā atlasot šūnu, standarta rīkjoslā nospiežot pogu "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" - "Sort", atveras dialoglodziņš "Range Sort";
  • Sarakstā "Kārtot pēc" atlasiet kolonnas virsrakstu, pēc kuras veiksiet kārtošanu;
  • Izvēlieties kārtošanas veidu "augošā" vai "dilstošā";

4. un 5. attēlā parādīti saraksta fragmenti pirms šķirošanas un pēc šķirošanas "augošā secībā" pēc vienas kolonnas "noliktavas numurs".


Rīsi. 4.


Rīsi. 5.

Kārtojot sarakstu pēc vairākām kolonnām, rindas ar vienādu vērtību pirmajā kārtošanas kolonnā ("Kārtot pēc") tiek sakārtotas tādā secībā, ko nosaka sarakstā "Pēc tam pēc" norādītais virsraksts, un rindas ar tādu pašu vērtību. pirmajās divās kārtošanas kolonnas ir 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ē kārtošanas secību pirmajā kārtotajā kolonnā, bet izceļ sakā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 virsrakstu, ko kārtot, un iestatiet kārtošanas secību uz "augošā" vai "dilstošā secībā";
  • Atveriet sarakstu "Pēc tam", iestatiet kārtošanai citas kolonnas virsrakstu un iestatiet kārtošanu uz "augšupejoša" vai "dilstoša";
  • Paplašiniet sarakstu "Pēdējais pēc" un atlasiet kārtošanai trešās kolonnas virsrakstu un norādiet šķirošanu "augšupejošā" vai "dilstošā secībā";
  • Noklikšķiniet uz Labi, lai kārtotu.

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 šūnu šķirojamajā sarakstā;
  • Izvēlnē "Dati" atlasiet komandu "Kārtot";
  • Logā Sort Range noklikšķiniet uz pogas Opcijas;
  • Iestatiet radio pogu "Kārtot" uz "diapazona kolonnas" 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, Nākamais pēc, Pēdējā pēc.
  • Noklikšķiniet uz Labi, lai kārtotu

Dažos gadījumos ir nepieciešama nestandarta sarakstu kārtošanas secība, piemēram, datu kārtošana pa mēnešiem kalendāra secībā vai ierakstu kārtošana lietotāja izveidotā secībā. Pielāgota pasūtījuma izveidošana tiek veikta logā "Parametri" cilnē "Saraksti", ko var izsaukt ar komandu "Rīki" - "Parametri". Pielāgotu kārtošanas secību var lietot tikai kolonnas virsrakstam, kas norādīts nolaižamajā sarakstā Kārtot pēc

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

  • Norādiet šūnu sarakstā;
  • Izvēlnē "Dati" atlasiet komandu "Kārtot";
  • Sarakstā "Kārtot pēc" atlasiet slejas virsrakstu, kuru vēlaties kārtot;
  • Iestatiet slēdzi uz "augšupejošu" vai "dilstošu";
  • 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;
  • Noklikšķiniet uz Labi logā Kārtošanas diapazons, lai pabeigtu kārtošanu.

Kārtoš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ā to 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 ikonām, pēc teksta vai šūnas krāsas. Tas ir tas, kas tiks apspriests šajā rakstā.

Skaitļi

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

Tagad C dati ir 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, ka iezīmētā kolonna ir daļa no tabulas (un tas tiek darīts 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ētiskā secībā

Tas tiek darīts saskaņā ar to pašu principu, kā aprakstīts iepriekš. Izvēlieties vajadzīgo diapazonu un nospiediet pogu "Kārtot un filtrēt". Nolaižamās izvēlnes vienumi 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 atzīmējiet, kāds formāts ir iestatīts šūnām, kurās tie ir rakstīti. Atlasiet tos un cilnē "Sākums" skatiet grupu "Numurs". Vispiemērotākais ir “Datuma” formāts, ī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 palielinot pirmos divus skaitļus vai pa mēnešiem alfabēta secībā.

Pēc tam atlasiet vajadzīgo šūnu diapazonu un noklikšķiniet uz pogas "Kārtot un filtrēt". Jūs varat izvēlēties no izvēlnes vai "vecais uz jauno", vai "no jauna uz veco".

Pēc šūnas vai teksta krāsas

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

Izvēlieties visu diapazonu, noklikšķiniet uz pogas "Kārtot un filtrēt" 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 izvēlamies kolonnu, pēc kuras mēs kārtosim, piemērā tas ir "I". Sadaļā "Kārtot" nolaižamajā sarakstā atlasiet "Fonta krāsa". Pasūtījumu sadaļā izvēlieties "sarkanā krāsa" - "Augšā". Esam sakārtojuši skaitļus sarkanā krāsā.

Tagad mums kolonnā 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, noklikšķiniet uz "Kārtot un filtrēt" – "Pielāgojams...". Atvērtajā logā noklikšķiniet uz pogas "Pievienot līmeni". Kolonna paliek "I", nākamajā laukā atlasiet pēc "Vērtība", secība "augošā". Noklikšķiniet uz Labi.

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

Tāpat dati tiek sakārtoti pēc šūnas krāsas, tikai sadaļā "Kārtot" atlasiet no saraksta "Šū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. Mēs noklikšķinām uz pogas "Kārtot un filtrēt" un izvēlēties "Pielāgojams...".

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

Kārtošanas lodziņā atzīmējiet izvēles rūtiņu "Mani dati satur galvenes". Sadaļā "Sleja" sarakstā atlasiet "Klase", kārtojiet pēc "Vērtības" un secības "augošā".

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ā, šūnas ar meitenēm rozā. Lai to nedarītu katrai šūnai atsevišķi, izlasiet rakstu, kā programmā Excel atlasīt šūnas - tur ir teikts, kā atlasīt šūnas, kas nav blakus.

Sakārtosim šo kolonnu pēc šūnas 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 – pirmajā ir lielākais, otrajā ir mazāk utt. Tas ir, ja 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ļā "Sleja" izvēlieties "Nosaukums", kārtošana - "Šūnas krāsa", secība - "rozā", "Augšējā".

Tagad izmantojiet bultiņas, lai pārvietotu š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, iespējams, būs jākārto dati pēc vairākām kolonnām vienlaikus. Šodien es jums parādīšu, kā to izdarīt.

Piemēram, ņ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 Kārtot un filtrēt un nolaižamajā izvēlnē atlasiet Pielāgota šķirošana.

Tiks atvērts kārtošanas iestatījumu logs. Pirmais solis ir pārliecināties, vai ir atzīmēta izvēles rūtiņa. Manos datos ir galvenes. Šis iestatījums ļauj noņemt pirmo rindiņu 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 izstādi 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 parādīšu nākamajā nodarbībā.

Iepriekšējās divās nodarbībās mēs noņēmām dublikātus. Par to var arī lasīt. Šajā nodarbībā mēs to darīsim meklēt dublikātus.

Šajā apmācībā es jums parādīšu, kā labot galveni katrā Excel lapā. Tas var būt nepieciešams, ja strādājat ar lieliem galdiem. Parasti, tos izdrukājot, iznāk dokumenti ar lielu lapu skaitu, un, lai ar tiem būtu ērti strādāt, katrā lapā ir jāsaglabā tabulas galvene.

Šajā apmācībā es jums parādīšu, kā Excel šūnas sākumā skaitļa priekšā ievietot plusa vai nulles zīmi. Iedomāsimies, ka šūnā ir jāievada tālruņa numurs formātā "+7 987 ...". Parasti Excel vienkārši noņems šo pluszīmi.

Kārtot pēc formulām tabulu, kas sastāv no 2 kolonnām. Kārtosim pēc vienas no tabulas kolonnām (risināsim 2 uzdevumus: tabulas kārtošana pēc cipariem un kārtošana pēc teksta kolonnas). Iestatīsim kārtošanas formulas, 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ī kārtošanu divos līmeņos: vispirms pēc skaitļiem, pēc tam (atkārtojot skaitļus) pēc teksta kolonnas.

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

1. uzdevums (Kārtot tabulu pēc ciparu kolonnas)

Nepieciešams sakārtot tabulas rindas 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 kolonnas vērtību lielums Pārdošanas apjoms iezīmēts ar (). Dublētās vērtības ir arī iezīmētas dzeltenā krāsā.

Piezīme: Atsevišķas kolonnas (saraksta) šķirošanas uzdevums tiek risināts rakstos un .

Risinājums 1

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

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

Taču reālās problēmās skaitliskā kolonna var saturēt atkārtojumus, 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ļu nosaukumi tiks parādīti nepareizi ).

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

INDEX(Pārdošana;
ROUND(RESID(LARGE(
---(COUNTIF(Pārdošana;"<"&Продажи)&","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6));
STRING()-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.

Analizē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ālais galds, t.i. pirmais numurs diapazonā Pārdošana) vairāk nekā 4 vērtības no tā paša diapazona; numurs 74 (no šūnas B8 oriģinālais galds, t.i. otrais skaitlis diapazonā Pārdošana) vairāk nekā 5 vērtības no tā paša diapazona; nākamais cipars 23 ir mazākais (tas nav neviens cits), un tā tālāk.
  • 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 pozīcijas numurs masīvā: (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-DLSTR(RINDA(Pārdošana)-RINDA($E$6)))&RINDA(Pārdošana)-RINDA($E$6))Šajā formulas daļā ir ierobežojums ne vairāk kā 1000 rindu avota tabulā (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 MOD() 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() pēc pozīcijas numura atgriež 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īt zemāk) tāda paša veida formulas daļa, t.i. viss, kas atrodas funkcijā ROUND(), tiek pārvietots uz atsevišķu kolonnu 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 funkciju SMALL() masīva formulā, mēs iegūstam kārtošanu augošā secībā.

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

Testēšana

Tagad pievienosim oriģinālajai tabulai jaunu rindu. Dinamiski sakārtotajās tabulās mums jāiegūst atbilstošā šķ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 tabula tiks automātiski parādīta sakārtota dilstošā secībā;
4. Sakārtotajā tabulā jaunā rinda tiks parādīta priekšpēdējā.

Formulas aprēķināšanas ātrums

Uz "vidējā" datora veiktspējas ziņā pārrēķins par tādu masīvu formulas, atrodas 100 rindās, praktiski nav manāms. Tabulām ar 300 rindām pārrēķina laiks aizņem 2-3 sekundes, kas rada neērtības. 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 atteikties no masīvu formulu izmantošanas, aizstājot tās ar kolonnām ar atbilstošām formulām, vai vispār atteikties no dinamiskās kārtošanas par labu standarta pieeju izmantošanai (skatiet nākamo sadaļu).

Alternatīvas pieejas tabulu šķirošanai

Kārtojiet avota tabulas rindas, izmantojot standarta filtru (atlasiet avota tabulas virsrakstus 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āpielieto 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 pēc alfabēta zemāka, ir augstāks "rangs". Piemēram, ābolu vērtība atbilst maksimālajam "rangam" 7 (ņemot vērā atkārtojumus).

E kolonnā ievadiet parasto formulu:

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

Šī formula ņem vērā teksta vērtību atkārtošanos 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 izvadīs atbilstošo pārdošanas apjomu (H kolonna).

2.1. problēma (divu līmeņu kārtoš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 74 kolonnā ir trīs vērtības) mēs parādīsim atbilstošās vērtības alfabētiskā secībā.

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

Sīkāka informācija parauga failā lapā Task2.