#009 "Пакетиране" на стойности

Често се налага да извлечем стойности които отговарят на дадено условие. Може би в някоя друга тема ще поговорим за Advanced Filter. Сега обаче искам да се спра на възможността да извличаме данни отговарящи на дадено условие в отделна област с помощта на функции. Всъщност това го използвах в темата за контрол с изключване (#004), но там реших да не отделям внимание на тази техника и детайлите при нейното използване.

Проблемът е как да съберем на едно място данни отговарящи на дадено условие.

Пример: дадени са факултетни номера и оценки от тест. 

• Да се извлекат студентите имащи резултат който е >= на 90% от най-доброто постижение на тестът. Това са отличниците:)
• Да се извлекат студентите имащи резултат който е <= на 10% от най-слабото постижение на тестът. Това са слабаците:)

Обръщам внимание че се търси не 10% бройка ами студентите които имат близък до най-силния и най-слабия резултат. Ако имате "гении" които има резултата над 10% от следващия го дадената таблица може само него да покаже!

Ето външния вид на входните и изходните данни:

Извличане на данни

Данните се въвеждат в колонките A, B и C и автоматично се прехвърлят в F,G,H и I. Колонките D и Е са помощни и спокойно могат да се скрият или да им се намери по-подходящо място:) Умишлено съм ги оставил за илюстрация.

В този пример няма да декларирам имена и ще работя със зони клетки.

1. Помощни колони D и E. Тези колонки служат за "маркиране" на данните които ни трябват. Маркировка се изразява с записване на номера на реда за тези данни които отговарят на условието ни.

• Формула в D2 -> =IF(C2>=MAX($C$2:$C$35)*0.9,ROW(A2),"") - Ако оценката е по-голяма от 90% от максималната оценка се записва номера на реда иначе клетката остава празна. NB! не забравяйте да "заключвате" зоната с оценките, защото формулата ще се мести и на други места.
• Формула в E2 -> =IF(C2<=MIN($C$2:$C$35)*1.1,ROW(A2),"") аналогично.

Размножавате формулите надолу. Както виждате от примера там където оценката е по-голяма 76.5 (90% от най-високата оценка която в случая е 85) се маркират. Същото важи и за колонка E.

2. Извличане на маркираните стойности. На базата на номера на реда се вземат съответните данни от колонка А и колонка В. За да се "пакетират" данните отново ще използваме трика с функцията Small и динамичен брояч.

• Формула в клетка F3: =IFERROR(INDEX($A:$A,SMALL($D$2:$D$35,ROW(A1))),""). Отново последователно се вземат първата, втората и т.н. стойности по големина. В показаните данни за първата клетка функцията SMALL($D$2:$D$35,ROW(A1)) ще върне стойност 10 (първия маркиран студент бил той и не с най-високата оценка от теста, но не това е задачата ни в момента!). Най-малкия номер на ред! Чрез функцията Index се осъществя извличането от колонка A на данните от десети ред  ред (намереното число от Small). Това е факултетния номер на студента. При размножаване на формулата надолу ще се извлече втория маркиран ред (числото 11) след това третото (22) и т.н.
• Формула в клетка G3: =IFERROR(INDEX($B:$B,SMALL($D$2:$D$35,ROW(A1))),"") аналогично, но се извличат данните от колонка B (тук може да се реализира и с помощта на Vlookup както в предишен пример).
• Формула в клетка H3: =IFERROR(INDEX($A:$A,SMALL($E$2:$E$35,ROW(A1))),"") Аналогично, но се използва маркировката от колонка E.
• Формула в клетка G3: =IFERROR(INDEX($B:$B,SMALL($E$2:$E$35,ROW(A1))),"")

Това е:) Имате "пакетирани" отличниците и слабите студенти:) Нанасяйте в книжките:)

Бонус: Можете да "украсите" списъкът за въвеждане с цветове използвайки условното форматиране.... ;)

• Изберете областта A2:C35. Не само колонка C!
• Създайте ново правило Home/Conditional formatting/New Rule (може и с Alt+H,L,N).
• Изберете тип на правилото формула
• В полето за формула запишете: =$C2>=MAX($C$2:$C$35)*0.9
• С бутона Format направете фона  (Fill) зелен

Условно форматиране

По аналогичен начин направете още едно правило със формула =$C2<=MIN($C$2:$C$35)*1.1 и червен фон. Не забравяйте че трябва да маркирате от А2 до C35  а не само колонка C!.

И е готово:) Успех;)

Условно оформяне