Добавяне на възможност за сортиране по KPI за таблото за управление, Exceltip

Блог на Microsoft Excel: съвети, трикове, тайни, трикове

С помощта на предишната публикация за създаване на таблица с лента за превъртане за табла, започнахме поредица от статии за създаване на интерактивни таблици за табла в Excel. Показването на малка част от списъка с повече елементи, които могат да се превъртат, беше първата стъпка. Таблото за управление с по-задълбочен анализ се нуждае от повече функции. Една от най-простите, но в същото време и най-популярната техника е способността за сортиране. Отново искаме потребителят да избере критериите за сортиране и да види резултата веднага, без да напуска таблото си. Това означава, че няма нужда да отивате в изходния лист с данни, няма нужда да избирате диапазон, няма нужда да използвате командите за сортиране в менюто или на лентата. И разбира се, искаме да направим всичко това без да използваме макроси.

добавяне

Таблицата с нашите KPI изглежда по същия начин като в първата статия, към нея са добавени само 5 допълнителни превключвателя, разположени под заглавието на таблицата и наличието на потъмняване на сортираната колона.

Внедряване

Ще трябва да направим някои незначителни промени в нашето табло за управление: добавете радио бутони, свържете ги със съответните им клетки и добавете условно форматиране към колоните. Също така, интересна част ни очаква на листизчисления.В Excel има много възможности за сортиране с помощта на формули. Повечето от тях се изпълняват с помощта на формули за масиви, далеч най-елегантният начин, но и най-трудният за разбиране. По-долу има инструкции с подсказки (може да не са толкова елегантни като формулите за масиви, но по-лесни за разбиране)

Поставяне на превключватели.НамеретеПрограмист–>Контроли–>Вмъкване->Контроли за формуляри–>Превключване. Вмъкнете радио бутони в заглавките на таблицата на таблото.

Добавяне на критерий за сортиране.В листаИзчислениядобавете още един ред, който ще бъде критерий за сортиране за нас. За удобство го кръстихsort_criteria.

възможност

Установяване на връзка.Щракнете с десния бутон върху някой от радио бутоните, изберетеФорматиране на обекти попълнете диалоговия прозорец, както е показано на фигурата

добавяне

Добавяне на условно форматиране.За по-добра четливост искам сортираната колона да има тъмен цвят. За да направите това, приложете условно форматиране, изберете първата колона с KPI, в лентата с инструменти намирамеНачало -> Стилове -> Условно форматиране -> Създайте правило...В прозореца, който се показва, изберетеИзползване на формула за определяне на форматиране на клетки, въведете формулата (в зависимост от номера на колоната в таблицата, стойността на критерия за сортиране за сравнение ще бъде подходяща, например за втората колона формулата ще бъде=sort_criteria=2). И ние определяме формата, от който се нуждаем.

Повтаряме тази процедура за останалите четири колони на таблицата (не забравяйте да промените формулата за условно форматиране, в зависимост от номера на колоната на таблицата)

добавяне

Настройване на таблицата за сортиране.Забавлението започва. В листаИзчислениядобавете таблица, която в зависимост от критерия за сортиране ще променя своите стойности.

Как работи сортирането в таблото за управление

exceltip

  • Получаваме стойността на сортирания KPI (в зависимост отот критериите за сортиране) с помощта на формулата OFFSET() (колона D)
  • Правене на всяка стойност на KPI уникална чрез добавяне на много малко число към всяка стойност (колона E)
  • Сортирайте списъка с уникални стойности, като използвате формулата LARGE() (колона F)
  • Използвайте формулата MATCH(), за да определите позицията на всяка стойност в несортиран списък (колона G)
  • Събиране на цялата таблица, сортирана по някои KPI, с помощта на колона G и формулата OFFSET() (колони I:N)

Остава да направим малка промяна в таблицата на таблото - променете препратката във формулата OFFSET() към клетката на листаCalculations.Получаваме формулата =OFFSET(Calculations!I9;Calculations!$D$4;0;1;1) за първия елемент в таблицата.Сега формулата връща сортираните данни от листаCalculations. Разширете тази формула до всички KPI и надолу, за да попълните таблицата с правилните формули.

И така, обмислихме възможността да добавим сортиране към нашата таблица. Сега, за по-задълбочен и задълбочен анализ, можем да покажем данни от по-големи към по-малки.

Може да се интересувате и от следните статии

Ренат, благодаря ти много за предоставения материал. Моля, кажете ми защо добавяте с уникална стойност на KPI и защо е невъзможно да сортирате и търсите позиция по колоната "Sortable KPI"?

Този трик ви позволява да избегнете грешки в случаите, когато едни и същи стойности се намират в различни редове. В този случай, когато се опитвате да върнете позицията по колоната KPI за сортиране, формулата MATCH ще върне стойността на първото съответстващо съвпадение два пъти. Добавянето на хилядна към KPI ви позволява да намерите точно неговата позиция

Но не може да има ситуация, когато KPI (n) + число (n) / 10000 ще бъде равно на KPI (m) + число(m) /10000? И как това помага да повлияе на поведението при сортиране?

Благодаря ти. Кажете ми, как можете да сортирате не само по числови стойности, но и по азбучен ред (но в една таблица трябва да присъстват само различни видове сортиране)?

Ако съм те разбрал правилно, интересуваш се от персонализирано сортиране. Намира се в раздела Начало, в групата Редактиране. Бутон за сортиране и филтриране -> Персонализирано сортиране

Ренат, добър ден!

Благодаря за подробния и интересен пример! Моля, кажете ми как можете да добавите нови редове към листа „Данни“, така че те автоматично да се добавят към листа „Изчисления“? Има ли елегантен начин без използване на макроси?!

Владислав, за съжаление е невъзможно да се коригират свойствата на контролите без макрос. В този случай промяната на максималната стойност на плъзгача е ключът към промяната