Динамични сортиране таблици в MS Excel - съвместим с Microsoft Excel 2018, Excel 2018
Сортиране на таблицата на формули, състоящи се от 2 колони. Сортиране ще произвежда по една от колоните на таблицата (2 решаване на проблема: за сортиране и сортиране на числения текст колона на таблицата). Сортиране формула, създадена така, че когато добавяте нови данни към таблицата източник, за да сортирате промени в таблицата динамично. Това ще позволи да винаги са подредени на масата, без намеса на потребителя. Също така, ние правим за сортиране на две нива, за първи път от цифрова, а след това (за повтарящи се числа) - на самото съобщение.
Да предположим, че има маса, състояща се от 2 колони. Една колона - текст: Списъка на плодове; а втората - с цифрова обема на продажбите (виж например файла.).
Zadacha1 (сортиране чрез цифрова колона маса)
Необходимо е да се сортира редовете на таблицата от съдържанието на такава колона (обем на продажбите). Предполага се, че потребителят е постоянно изпълва редовете на таблицата, така че трябва да напишете формула като се вземе предвид добавената стойност.
За яснота, стойностите на величина в обема на колоната на продажбите изолирани с помощта на условно форматиране (Начало / стилове / условно форматиране / хистограми). Също така се подчертава в жълти дубликати.
Ако такава колона гарантирано да не съдържат дублиращи се стойности, тогава проблем е лесно решим:
- Цифров колона вид функция голям () (виж списъка със статии подредени (цифрова стойност).);
- функция CDF () функции или лигамент INDEX () + MATCH () избират стойностите от текст колона от неговия съответен цифрова стойност.
Въпреки това, в реални приложения колона може да съдържа повторения, както и функция CDF () в случай на повторение винаги избира само първата стойност от върха (вж. Член WRT () функция в MS Excel), този подход не е подходящ (имена на плодове ще получен неправилно).
Затова сортиране механизъм ще трябва да се приложи в друга.
Създаване за удобство на динамичния обхват на 2 Плодове и покупко-продажба. които се отнасят до диапазоните на клетки, съдържащи стойностите в съответните колони на таблицата източник. Когато добавяте нови редове към таблицата, границите на динамичния обхват ще бъде разширен автоматично.
В Г и Д колони публикувате маса, която ще бъде динамично подредени,
ИНДЕКС = (продажба;
КРЪГ (MOD (голям (
--(COUNTIF (Продажби ";<"&Продажи)&","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6));
Ред () - ред ($ E $ 6)) 1) * 1000, 0)
)
Тази формула ще сортира обем на колоната Продажби (динамичен обхват на продажбите) в низходящ ред. Пропуски в оригиналната таблица не са разрешени. Броят на редовете в таблицата източник трябва да е по-малко от 1000.
Нека разгледаме формулата още:
- COUNTIF формула (продажба; "<"&Продажи) возвращает массив . Это означает, что число 64 (из ячейки B7 исходной таблицы, т.е. первое число из диапазона Продажи ) больше 4-х значений из того же диапазона; число 74 (из ячейки B8 исходной таблицы, т.е. второе число из диапазона Продажи ) больше 5-и значений из того же диапазона; следующее число 23 - самое маленькое (оно никого не больше) и т.д.
- Сега, гореспоменатото масив от цели числа, ще се превърне в масив от числа с фракционна част, където дробна част ще съдържа редица елементи в масива :. Това се реализира чрез изразяването "" Повторение ( "0", 3-ЛЕН (ред (продажби) -разходка ($ E $ 6))) ЛИНИЯ (продажби) -разходка ($ E $ 6)) В тази част на присъщо ограничение на формула от не повече от 1000 линии таблицата източник (вж. по-горе). Ако е необходимо, тя може да бъде лесно да се промени, но това е без значение (вж. По-долу раздел изчислителна скорост).
- () Функция ГОЛЯМАТА сортира масива горе.
- MOD () връща фракционна част на броя представлява броя на позиции / 1000, например 0.005.
- КРЪГЪЛ () функция. след умножение с 1000, закръглена до цяло число и връща номера на артикула. Сега всички номера на артикули съответстват на броя на колоните на продажбите, подредени в низходящ ред.
- INDEX () връща броя на елементите на съответния брой.
Подобен формула може да бъде написано за изходните стойности в колоната плодове ИНДЕКС = (плодове; КРЪГЪЛ ().)
В пример файла. поради причини, свързани с изчислителна скорост (см. по-долу), по същия тип част с формула т.е. всичко вътре (функцията ROUND). поставен в отделна колона J. Следователно, полученият формула в сортираната таблица са както следва: Индекс = (плодове; J7) и индекс = (продажба; J7)
Също така, се променя във формула масив LARGE функция () на малки () се подредени във възходящ ред.
За яснота, стойностите на величина в обема на колоната на продажбите изолирани с помощта на условно форматиране (Начало / стилове / условно форматиране / хистограми). Както може да се види, сортиране работи.
Сега добавете нов ред в таблицата източник. В динамично сортови маси, ние трябва да получите правилното сортиране.
1. В клетка A15, напишете думата оригинални Морковите маса;
2. В клетка B15 влиза продажбите Морков = 25;
3. След въвеждане на стойности в stolbtsahD и Е ще се появи автоматично подредени в низходящ маса;
4. Сортиране на таблицата нова линия ще се появи предпоследната.
формули скоростта изчисляване
В "средна" на компютъра мащабиране спектър от такива двойки с формули подредени в редици 100, почти невидими. За маси с 300 линии, докато превръщането отнема 2-3 секунди, което причинява неудобство. Или трябва да се забрани автоматичното преобразуване на листа (формулите / изчисления / изчисляване на параметрите) и периодично натиснете F9. или да откаже използването на формули за масиви чрез заместването им със съответните колони на формулите, или изобщо да откаже динамичен сортиране за използване на стандартни подходи (вж. следващия раздел).
Алтернативни подходи за сортиране маси
Подреди редовете на таблицата източник с помощта на стандартен филтър (изберете заглавията на таблицата източник и натиснете Ctrl + Shift + L). От падащия списък изберете желания сортирането.
Ние получи нашата идентична версия на масата, но когато се добави към масата нови стойности ще трябва да приложите филтъра отново.
Можете да използвате инструмента за сортиране (Data / сортирате и филтрирате / сортиране). За да направите това, изберете всички стойности на таблицата източник не включва заглавната част, обадете се на инструмента за сортиране, изберете колоната, с което да се справи и сортиране опция.
Ние получи нашата идентична версия на масата, но с добавянето на нови стойности също трябва да приложите филтъра отново.
Кой вариант е за предпочитане - както винаги - за да изберете един разработчик.
Друг формула масив (+ допълнителна колона). Zadacha1.1
Сортиране на таблицата може да се направи с помощта на други, по-опростена формула за масив. но ни е необходима допълнителна (услуга) колона D (виж списъка например файл Пример 2.):
Колона F съдържа сортиран колона в продажба (покупка). Формулата връща позицията на стойността на продажбите. Например, броят 86 е 5-та линия на масата.
За експресия повтаря IF (F8 = $ B $ 7: $ B $ 14; ЛИНИЯ ($ B $ 7: $ B $ 14) -разходка ($ B $ 6); 0) ще се върне няколко стойности: т.е. броя 74 се намира в редове 2 и 8.
С големи функция () се появява първият 2, след това 8 (на отделни редове).
Тази формула е по-видно от обсъдено по-горе в началото, но изисква допълнително колона.
Zadacha2 (Сортиране по таблица текст колона)
Подреди редовете на таблицата със съдържанието на текста, колоните (на плода).
Както и в предишния проблем, предполагам, че колоната, на която се извършва сортиране, има повторения (имена на плодове се повтарят).
За да сортирате таблицата 2 трябва да се създаде над колона (D и E).
Тази формула е аналог на ранг стойности за текст (позиция стойности по отношение на другия списък на стойности). Текстови ценности, по-надолу в азбуката съответства на по-висок "ранг". Например, максималната стойност съответства Ябълки "ранг" 7 (обмислят повторения).
Колона E Въведете обичайната формула:
Тази формула се вземат предвид повтарящите се стойности текстови и регулира "звание". Сега Ябълки различни стойности съответстват на различни "класове" - 7 и 8. Това ви позволява да се покаже списък с сортирани ценности. За тази цел се използва формулата (колона G):
Подобен формула оттегли подходящ обем на продажбите (колона Н).
Задача 2.1 (дуплекс сортиране)
Сега, отново, ние сортирате оригиналния масата на обема на продажбите. Но сега за дублиращи се стойности (в колона три стойности 74), които извличаме съответните стойности и по азбучен ред.
За да направите това, ние използваме резултатите от Задача 1.1 и Задача 2.
Детайли в примерния файл на Zadacha2 лист.