C, PHP, VB, .NET

Пример. Работа с функции върху динамични масиви (array functions).

Дадена е следната таблица с примерни данни (създайте си ваша):

Искаме да изчислим „максималният прогрес“ на студент. За прогрес считаме позитивно нарастване на резултата от единия тест към другия.

По традиционния начин можем лесно първо да намерим прогреса на всеки поотделно:

След което да намерим максималния прогрес с функция MAX:

ARRAY функциите ни позволяват да извършваме това действие без предварително да сме изчислявали междинната колона с данните. Вместо това ще накараме Excel да изчисли тези данни скрито от нас в паметта си, а ние да вземем направо готовия резултат. Изтрийте колоната с прогреса и максималната стойност и отидете на празна клетка. В нея напишете следната формула:

ВАЖНО: Трябва да изпълните формулата не по стандартния начин (натискайки Enter), а използвайки CTRL+SHIFT+ENTER. По този начин ще укажете на Excel, че формулата трябва да се изпълни като array formula.

Ще видите, че след натискането на клавишната комбинация, формулата се е оградила с фигурни скоби: {=MAX(C2:C10-B2:B10)}. Това не е нещо, което можете вие да направите сами (няма да работи) – това е просто визуална индикация за вас, че Excel е изпълнил формулата като array formula.

Понякога можете да използвате един трик, с който предварително да видите данните. Маркирайте текста дефиниращ масива вътре в самата формула по следния начин:

Натиснете F9 от клавиатурата и ще видите изчислението на междинните резултати:

Задача. Създайте си таблица със следната структура и я запълнете с голямо количество произволно генерирани данни (*):

* Подходящо е да използвате функцията CHOOSE(RANDBETWEEN(X;Y); „стойност 1“, „стойност 2“, …)

В нов sheet създайте обобщение на данните по следния начин:

По вертикала в обобщаващата таблица сложете уникалните региони, а по хоризонтала – партиите. В клетките да стои броя гласувания за дадената партия в дадения регион. Първото затруднение, което ще изпитате в тази задача, е свързано с изваждането на отделен лист на уникалните имена на партии и уникалните номера на региони. За целта може да използвате следния трик:

Използваната формула е Array Formula и е със следното съдържание:

=IFERROR(INDEX(A2:A10;MATCH(0;COUNTIF($B$1:B1;A2:A10);0));““)

След като приложите същата тактика и за партиите и районите, ще достигнете до нещо подобно на следното:

Ако сме постъпили по този начин, ще се досетим, че данните за партиите ни трябват по хоризонтала, а не по вертикала. Единият начин да се справим с проблема е да преправим формулата (опитайте се). Другият начин е да използваме Array Formula TRANSPOSE по следния начин:

Функцията TRANSPOSE трябва да се използва като Array Function, т.е. да се изпълни с CTRL+SHIFT+ENTER

Следващият проблем е как да изчислим стойностите в получената таблица. Следният опростен пример показва как това може да бъде постигнато чрез функцията COUNTIFS:

След като приключите с тази търсената обобщена таблица, се запознайте с лесния начин да правим такива обобщения – INSERT > PIVOT TABLE. Използвайки автоматично генерирана обобщена таблица, създайте следния отчет: