#005 Формулите за масиви и как да се отървем от тях:)

Това е първи "сбъсък" с т. нар. формули за масив (Array formula)... Тази тема ще има продължения и често ще има примери, в които ще използвам такива формули... За начало се запознайте с тези базови понятия:

http://office.microsoft.com/bg-bg/excel-help/CH001003700.aspx
Доста трикове има тук (на английски):

http://office.microsoft.com/en-us/excel-help/guidelines-and-examples-of-array-formulas-HA010228458.aspx


Всъщност леко ви подведох:) Аз ще се опитвам всячески да избягвам такива формули...:) Ето малко примери и пояснения:

Пример 1: Да се намери общата дължина на текста в група клетки.

В "триковете" от втората връзка се вижда, че се препоръчва следната формула {=SUM(LEN(A1:A5))} (Ограждащите скоби {} не ги въвеждате а се появяват АВТОМАТИЧНО след като натиснете Ctrl+Shift+Enter при въвеждането на тази формула.... За повече подробности вижте препоръчаните връзки!

Формулите от този вид имат един досаден недостатък... При редактиране трябва да се сетите, че формулата е била такава! В реда за редактиране след двойно щракане (или F2) ограждащите скоби изчезват :( Т.е. ако решите да промените зоната и редактирате формулата ПАК трябва да натиснете Ctrl+Shift+Enter за въвеждането, което си е досадно и често и аз го забравям... ... и се появява досадната грешка #Value :) (в някой примери не се появява грешка, а грешен резултат, което е още по неприятно)

Да видим къде е проблемът и защо се налага използването на такъв вид формула... Частта Len(A1:A5) връща тип масив а не тип зона! Т.е. връща нещо от сорта на {5,4,3,3,2} където числата са дължините на текста в клетките... За съжаление функцията SUM не е чувала за понятието масив в нормален режим и за това използваме режим масив.

За щастие има друга функция, която е чувала:) Това е функцията SumProduct... Тази функция работи прекрасно с масиви. И примерът може да бъде заменен с "нормалната" формула =SUMPRODUCT(LEN(A1:A5)) !

Пример 2. Изчисляване средна стойност на група клетки изключвайки нулевите стойности... В "триковите" примери се дава формула за масив {=AVERAGE(IF(F1:F5<>0,F1:F5))}. Няма да се спирам на тази формула ами направо ще посоча нейните заместители. Нормалните =AVERAGEIF(F1:F5,"<>0") за Excel 2007 (и следващи версии) или по-съвместимото решение работещо и на по-старите версии =SUMIF(F1:F5,"<>0")/COUNTIF(F1:F5,"<>0").

Пример 3. Да се намери броят на разликите между две зони. Тук се търси "пълно" съвпадение на стойности и на тяхната позиция. Примерът който е даден е с формулата за масиви {=SUM(1*(A1:A4<>B1:B4))}. Както е отбелязано в текста, във формулата е използван един трик за преобразуване на масив от логически стойности в числа. Защото сравнението ще върне масив от вида {TRUE, FALSE, FALSE....} в зависимост от резултатите от сравнението и когато се умножи по 1 се получава {1,0,0...}. Същият ефект може да се постигне и с два знака минус пред масива. Т.е. формулата може да се представи и като {=SUM(--(A1:A4<>B1:B4))}.

Използвайки "нормалната" SumProduct се получава : =SUMPRODUCT(--(A1:A4<>B1:B4))