tabele de sortare dinamice în MS Excel - compatibil cu Microsoft Excel 2018, Excel 2018

Sortați tabelul de formule compuse din 2 coloane. Sort va produce conform uneia dintre coloanele tabelului (2 rezolva problema: pentru sortarea și sortarea coloană de text numeric din tabel). Sortare set formulă astfel încât, atunci când adăugați date noi tabela sursă pentru a sorta modificările de masă în mod dinamic. Acest lucru va permite întotdeauna au sortat tabelul fără nicio intervenție din partea utilizatorului. De asemenea, vom face o sortare pe două niveluri, în primul rând prin numeric, apoi (pentru numere recurente) - pe coloana de text.

Să presupunem că există un tabel format din 2 coloane. O coloană - textul: Lista fructelor; iar al doilea - un numerice de volume de vânzări (a se vedea exemplul de fișier.).

Zadacha1 (Sortarea după coloană tabel numeric)

Este necesar pentru a sorta rândurile din tabel cu conținutul unei coloane numerice (volumul vânzărilor). Se presupune că utilizatorul este în mod constant umple rândurile din tabel, deci trebuie să scrie o formulă, ținând cont de valoarea adăugată.

Pentru claritate, valorile amplitudine în ce privește volumul vânzărilor coloana izolate folosind formatarea condițională (Home / stiluri / formatare condițională / histograme). De asemenea, a subliniat în duplicate galbene.

În cazul în care o coloană numerică garantat pentru a nu conțin valori duplicate, atunci problema este rezolvată ușor:

• Numerică funcția de sortare a coloanelor mari () (a se vedea lista sortată de articol (valoare numerică).);
• Funcția CDF () funcții sau INDEX ligamentare () + MECI () selectați valorile din coloana de text din valoarea sa numerică corespunzătoare.

Cu toate acestea, în aplicații reale coloană numerică poate cuprinde repetiții, precum și un CDF funcție () în cazul repetării selectează întotdeauna doar prima valoare din partea de sus (a se vedea. Articolul WRT () în MS Excel), această abordare nu este adecvată (nume de fructe va derivat incorect).

Prin urmare, mecanismul de sortare va trebui să pună în aplicare într-un alt.

Crearea pentru comoditatea gamei dinamice de 2 fructe și vânzări. care se va referi la intervalele de celule conținând valorile din coloanele corespunzătoare ale tabelei sursă. Când adăugați rânduri noi la un tabel, limitele gamei dinamice va fi extins automat.

În coloanele D și E un tabel care vor fi sortate în mod dinamic,

INDEX = (vânzări;
ROUND (MOD (MARE (
--(COUNTIF (vânzări; "<"&Продажи)&","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6));
ROW () - ROW ($ E $ 6)) 1) * 1000, 0)
)

Această formulă va sorta volumul coloanei de vânzări (interval dinamic de vânzări), în ordine descrescătoare. Lacunele în tabelul original nu sunt permise. Numărul de rânduri din tabela sursă trebuie să fie mai mică de 1.000.

Să examinăm formula mai mult:

• Formula COUNTIF (Sales; "<"&Продажи) возвращает массив . Это означает, что число 64 (из ячейки B7 исходной таблицы, т.е. первое число из диапазона Продажи ) больше 4-х значений из того же диапазона; число 74 (из ячейки B8 исходной таблицы, т.е. второе число из диапазона Продажи ) больше 5-и значений из того же диапазона; следующее число 23 - самое маленькое (оно никого не больше) и т.д.
• Acum, șirul de numere întregi mai sus se va transforma într-o matrice de numere cu o parte fracționată, în cazul în care partea fracționară va conține un număr de elemente din matrice :. Acest lucru se realizează prin expresia "" REPEAT ( "0", 3-LEN (ROW (Sales) -Row ($ E $ 6))) LINE (Sales) -Row ($ E $ 6)) În această parte a formulei limitării inerente a nu mai mult de 1000 de linii tabela sursă (a se vedea. de mai sus). Dacă se dorește, acesta poate fi ușor să se schimbe, dar este lipsită de sens (a se vedea. Secțiunea de mai jos pe viteza de calcul).
• GREATEST () funcție sortează matrice de mai sus.
• Funcția MOD () returnează partea fracționară a numărului reprezentând numărul de posturi / 1000, de exemplu, 0,005.
• ROUND () funcția. după înmulțirea cu 1000, rotunjit la un număr întreg și returnează numărul de articol. Acum, toate numerele de articol corespund cu numărul de coloane de vânzări, sortate în ordine descrescătoare.
• INDEX () întoarce numărul de articol al numărului corespunzător.

O formulă similară poate fi scrisă pentru valorile de ieșire din coloana Fructe INDEX = (fructe; ROUND ().)

În exemplul de fișier. din motive de viteză de calcul (cm. mai jos), aceeași parte tip cu formula adică totul în interiorul funcției ROUND (). plasat într-o coloană separată J. Prin urmare, formula care rezultă din tabelul de sortat sunt după cum urmează: INDEX = (fructe; J7) și INDEX = (vânzări; J7)

De asemenea, schimbarea în formula matrice funcție MARE () pe SMALL () se sortate în ordine crescătoare.

Pentru claritate, valorile amplitudine în ce privește volumul vânzărilor coloana izolate folosind formatarea condițională (Home / stiluri / formatare condițională / histograme). După cum se poate observa, lucrări de sortare.

Acum, adăugați o nouă linie în tabela sursă. În tabele dinamic Sortable, avem nevoie pentru a obține sortarea corectă.

1. În A15 celulă, introduceți cuvântul Morcovi de masă originale;
2. In celula B15 intra vanzarile de morcov = 25;
3. După introducerea valorilor în stolbtsahD și E vor apărea automat sortate în tabelul descendent;
4. Sortați tabelul O nouă linie va fi afișată penultima.

Formulele de calcul viteza

La matrice de scalare „medie“ performanță de calculator a unor astfel de perechi de formule aranjate în rânduri 100, practic invizibile. Pentru tabelele cu 300 de linii în timp ce conversia durează 2-3 secunde, ceea ce provoacă neplăceri. Sau trebuie să dezactivați conversia automată a foii (formule / calcule / parametri de calcul) și apăsați periodic F9. sau să refuze folosirea unor formule de matrice prin înlocuirea acestora cu coloanele corespunzătoare formulelor, sau, în general, să refuze sortarea dinamică utilizând abordări standard, (a se vedea. secțiunea următoare).

abordări alternative la tabele de sortare

Sortare rândurile din tabel sursă folosind filtrul standard (selectați antetele tabela sursă și apăsați CTRL + SHIFT + L). În lista verticală, selectați sortarea dorită.

Obținem versiunea noastră identică a tabelului, dar atunci când sunt adăugate la masa de valori noi va trebui să se aplice din nou filtrul.

Puteți utiliza, de asemenea, instrumentul de sortare (date / Sortarea și filtrarea / Sort). Pentru a face acest lucru, selectați toate valorile din tabela sursă nu include antetul, apelați instrumentul de sortare, selectați coloana prin care pentru a sorta și opțiune de sortare.

Obținem versiunea noastră identică a tabelului, dar cu adăugarea de noi valori, de asemenea, să se aplice din nou filtrul.

opțiune este de preferat - ca întotdeauna - pentru a selecta un dezvoltator.

O altă formulă matrice (+ coloană suplimentară). Zadacha1.1

Masa Sort se poate face cu ajutorul altor, mai simple formula matrice. dar avem nevoie de suplimentare (serviciu) coloana D (a se vedea lista de fișier exemplu Exemplul 2.):

Coloana F conține o coloană de sortat în (vânzări). Formula returnează poziția valorii vânzărilor. De exemplu, numărul 86 este al 5-lea rând al tabelului.

Pentru exprimarea repetă IF (F8 = $ B $ 7: $ B $ 14; LINE ($ B $ 7: $ B $ 14) -Row ($ B $ 6); 0) va returna valori multiple: adică numărul 74 este situat în rândurile 2 și 8.

Cu funcție MARE () este afișat mai întâi 2, apoi 8 (pe linii separate).

Această formulă este mai afișat proeminent decât sa discutat mai sus la început, dar necesită o coloană suplimentară.

Zadacha2 (Sortare după tabelul coloană de text)

Sortarea rândurile din tabel conținutului coloanelor de text (din fructe).

Ca și în problema anterioară, să presupunem că coloana pe care sortarea se realizează, există repetiții (nume de fructe sunt repetate).

Pentru a sorta tabelul 2 trebuie să creeze coloană deasupra capului (D și E).

Această formulă este un analog al valorilor de rang pentru text (poziția valori în raport cu cealaltă listă de valori). Valorile text, în continuare în jos în alfabet corespunde unui „rang“ mai mare. De exemplu, valoarea maximă corespunde merelor „rank“ 7 (repetiții considerând).

Coloana E Introduceți formula obișnuită:

Această formulă ia în considerare valorile de text repetitive și reglează „rangul“. Acum Mere valori diferite corespund diferite „grade“ - 7 și 8. Acest lucru vă permite să afișați o listă de valori sortate. Pentru a face acest lucru, se folosește formula (coloana G):

O formulă similară retrage un volum corespunzător de vânzări (coloana H).

Sarcina 2.1 (sortare duplex)

Acum, din nou, vom sorta tabelul original pe volumul de vânzări. Dar acum, pentru valorile duplicate (în coloana A trei valori 74), obținem valorile corespunzătoare în ordine alfabetică.

Pentru a face acest lucru, vom folosi rezultatele Sarcinilor 1.1 și 2.

Detalii în fișierul mostră pe foaia Zadacha2.

articole conexe