Back to site
Since 2004, our University project has become the Internet's most widespread web hosting directory. Here we like to talk a lot about web servers, web development, networking and security services. It is, after all, our expertise. To make things better we've launched this science section with the free access to educational resources and important scientific material translated to different languages.

Формуле низа



Формуле низа су један од најмоћнијих функција Ексел-а,иако није увек најлакши за коришћење. Погледајте Боб Умласов одличан документ на http://www.emailoffice.com/excel/arrays-bobumlas.html са многим примерима за Араи формулу, и такође Чип Пеарсонову страницу на "http://www.cpearson.com/excel/array.htm

Једноћелијски низ формуле се процењују више пута, зависно од броја ћелија из формуле. То може да потраје много времена, а може или не може бити бржи од алтернатива.
Опсег који покрива више формула низа ћелија може бити бржи за израчунавање него појединачна формула у свакој ћелији (иако предност брзине изгледа мања у Ексел97 и Ексел2000).
Због низа формуле и функције као што је СУМ чији референтни опсези утичу на редослед у коме Ексел израчунава, требало би избегавати мешање редова и колона референце или низа преклапање референце.

Како Микрософт Офис Ексел проширује Формуле низа


Када користите константу низа у формули или функције, остале компоненте или аргументи треба да имају исте димензије као први низ. Ако је потребно, Ексел проширује неопходне компоненте за траженим димензијама. Свака компонента мора да има исти број редова као компонента са највећим бројем редова, и истим бројем колона као компонента са највећим бројем колона.

На пример, у формули = СУМ ({1,2,3} * 4), једна компонента је Л-од-3 низа и друга је појединачне вредности. Оцењујући ову формулу, Микрософт Ексел аутоматски проширује другу компоненту на л-од-3 низа и оцењује формулу као = СУМ ({1,2,3} {4,4,4} *). Формула је резултат једнак 24, што је збир од 1 * 4, 2 * 4 и 3 * 4.

Следећа табела приказује како се проширио низ компоненти или аргумената.

У овој формули

Аргументи су оцењени као

Објашњења

=1+{1,2,;3,4}

{1,1;1,1}+{1,2;3,4}

Константа 1 је проширена тако да одговара низу. .

={1,2,3}*{2;3}

{1,2,3;1,2,3}* {2,2,2;3,3,3}

Сваки низ је проширен да прими друге димензије. .

={1,2}+{1,2,3;4,5,6}

{1,2,#N/A;1,2,#N/A}+ {1,2,3;4,5,6}

Сваки низ се проширио, али не постоји вредност у првом низу да се убаци у трећу колону вредности у другом низу .

=MOD({5,6,7},4)

MOD({5,6,7},{4,4,4})

Константа 4 је проширена тако да одговара низу.



Требате да унесете формулу низа у опсегу ћелија са истим димензијама као резултат низа произведене по формули. Микрософт Ексел може онда поставити сваку вредност у добијеном низу у једну ћелију у опсегу низа.
  • Ако формула низа производи низ мањи од изабраног опсега низа, Микрософт Ексел проширује добијени низ да попуни опсег.>
  • Ако је Микрософт Офис Ексел проширује низ да попуни опсег већи од формуле низа, # Н / А вредности грешке се појављује у ћелијама за које не важи да је проширена вредност на располагању.
  • Ако формула низа производи низ већи од изабраног опсега низа, вишак вредности се не појављују на радном листу.
На пример, формула = {1,2,3}*{2,3,4} производи л-од-3 низ {2,6,12}.
  • Ако унесете ову формулу у 2 са 3 низа опсега, Микрософт Ексел проширује резултат на {2,6,12; 2,6,12}.
  • Ако унесете исту формулу у л са 4 низа опсега, Ексел проширује резултат на {2, 6,12, # Н/А}.
  • Ако унесете исту формулу у Л са 2 опсега низа, резултат је {2,6} .

Специјалне Функције Вам Помажу да Радите са Низовима


Микрософт Офис Ексел садржи неке функције које враћају вредности низа или захтевају било константе низа или референце на ћелије опсега као аргументе.

На пример, желите да израчуна тренд својих месечних продаја током последњих шест месеци. Тај тренд није описан од стране једне вредности, него са шест-тренд вредности које одговарају на месечну продају у последњих шест месеци. Микрософт Ексел обезбеђује функцију радног листа, тренд, који обавља ово израчунавање за вас и производи шест вредности. Можете унети формулу тренда у 6 суседних ћелија помоћу Контрол / Шифт / Ентер.

Микрософт Ексел функције које раде са низовима су укључене у следећој табели

Назив функције

Низ употреба

COLUMN

Даје низ када је аргумент низ

COLUMNS

Захтева низ или опсег ћелија као аргумент

GROWTH

Захтева низове или опсеге ћелија као аргументе и могу да се врате низу

HLOOKUP

Захтева низ или опсег ћелија као аргумент

INDEX (array form)

Захтева низ као аргумент, и могу да се врате низу

LINEST

Увек даје низ

LOGEST

Увек даје низ

LOOKUP (array form)

Захтева низ или опсег ћелија као аргумент

MATCH

Захтева низ или опсег ћелија као аргумент

MDETERM

Захтева низ као аргумент

MINVERSE

Увек даје низ

MMULT

Увек даје низ

ROW

Увек даје низ када је аргумент опсег

ROWS

Захтева низ или опсег ћелија као аргумент

SUMPRODUCT

Захтева низове, опсеге ћелија или вредности као аргументе

TRANSPOSE

Увек даје низ

TREND

Захтева низове или опсеге ћелија као аргументе и могу да се врате низу

VLOOKUP

Захтева низ или опсег ћелија као аргумент



Ту су такође и друге функције које могу да преузму једну вредност аргумента или низ аргумената. Ове функције враћају различите резултате када се користе са аргументима низа. За више информација о функцијама, кликните на дугме Функције Визарда на траци са алаткама Стандард или погледајте Помоћ на мрежи.

Упозорење

Пре него што унесете функцију у радном листу, морате да знате шта та функција враћа. На пример, МИНВЕРСЕ (инверзна матрица) функција враћа низ на исту величину као што његов аргумент. Још једна од функција, ТРАНСПОСЕ, даје низ са супротним димензијама од његовог аргумента. Осигурајте простор на вашем радном листу за резултирајући низ.

Смањити број операција низа


Смањење броја ћелија и процењеног израза.

Користите помоћне колоне или редове да бисте узели што више ћелијских референца изван формуле низа.

Главни кључ оптимизације калкулација брзине формуле Низа је да се уверите да је број ћелија и израза оцењен у формули низа што је мање могуће.

Запамтите да је формула низа помало као нестабилна формула: ако је било која од ћелија референце промењена или нестабилна, или је била прерачунавана онда ће формула низа проценити све ћелије њене референце.
  • Узмите изразе и опсег референци из формуле низа у одвојене помоћне колоне односно редове. То ће учинити много боље коришћење Екселовог паметног поновног израчунавања.
  • Немојте референцирати комплетне редове, или више редова и колона него што је потрбно.
  • Употребљавајте Динамички Опсег Имена где је могуће. Иако су они нестабилни исплатљиви су јер смањују величину опсега.
  • Будите опрезни са формулама низа које референцирају ред и колону: ово приморава израчунавање правоугаоног опсега.
  • Користите СУМПРОДУКТ ако је то могуће: нешто је бржи од еквивалентне формуле низа.

Формула низа СУМ са вишеструким условима


Једна честа употреба Формуле низа је да урадите неку суму са више услова. Ово је релативно лако да урадите, нарочито ако користите Екселов Кондиционал Сум Визард, али често веома спор. Обично постоје алтернативни начини добијања истог резултата, али много брже.

Две добре методе убрзавања више услова СУМА су:
  • Подела вишеструких услова у колоне помоћничких формула које дају Тачно или Нетачно за сваки ред, а затим референцу помоћне колоне у СУМИФ или формула низа. Иако може изгледати да се за једну формулу низа не смањује број калкулација, у ствари највећи део времена то омогућава да Екселов паметни рекалкулациони процес само прерачуна формуле у помоћну колону која треба да се прерачуна.
  • Ако подаци могу бити сортирани онда је добра техника да се рачунају групе редова и ограничи формула низа да гледате подскуп група.
Погледајте СУМИФ пример за радни задатак. Преузети ФастЕксел Пример Проблем има и пример који показује начин и значајну временску уштеду која је често остварљива.

Коришћење СУМПРОДУКТ за вишеструки услов формуле низа


Постоје неколико предности коришћења СУМПРОДУЦТ-а него СУМ формуле низа:
  • СУМПРОДУКТ не мора да буде низ-унешен коришћењем Контрол-Шифт-Ентер.
  • СУМПРОДУКТ је обично незнатно бржи (5-10%)
  • СУМПРОДУКТ може третира празнине и текст као нулу
Можете користити СУМПРОДУКТ за вишеструки услов формуле низа на следећи начин:

SUMPRODUCT(--(Condition1),--(Condition2),RangetoSum)


Услов 1 и 2 су условни изрази попут $А$1: $А$10000 <= $ З4. Због тога што условни изрази враћају Тачно или Нетачно пре него бројеве они морају да буду присиљени на бројеве унутар СУМПРОДУЦТ функцији. То можете да урадите помоћу два знака минус (-) или додавањем 0 (+0) или множењем од 1 (* 1). Коришћење -- је веома незнатно брже него +0 или * 1.

Баг у програму Ексел 2007 СП2, фиксиран у 12.0.6545.5000: Ако користите 2 знака минус (--) синтакса у формули низа или СУМПРОДУКТ формулу са директном референцом на нестабилној функцији у Екселу 2007 СП2 формула не прерачунава када се краткотрајна функција односи на прерачунавање. (Ово ради исправно у свим осталим верзијама Ексела). На пример:


A1:A5 =RAND()
B1:B5 {=-A1:A5}

Ћелије Б1: Б5 неће се прерачунавати када притиснете тастер Ф9. Захваљујући Луке Висбеј сам обавештен о овом багу.

Обратите пажњу да величина и облик опсега или низова који се користе у условним изразима и опсегу до збира мора бити иста, и не могу да садрже читаве колоне.

Такође можете директно множити услове унутар СУМПРОДУКТ уместо да их одвојите зарезима:

SUMPRODUCT((Condition1)*(Condition2)*RangetoSum)


Али ово је обично мало спорије него коришћење зареза синтаксе, и даје грешку ако опсег збира садржи текстуалну вредност.
Међутим, то је мало више флексибилније у смислу да опсег збира може имати на пример више колона, када услови имају само 1 колону.

Коришћење СУМПРОДУКТ за множење и додавање опсега и низова


У случајевима као што су пондерисани просечни прорачуни где треба да помножите низ бројева од стране другог опсега бројева и сумирати резултате користећи зарез синтаксе за СУМПРОДУКТ може бити 20-25% бржи од забележеног низа СУМ:

{=SUM($D$2:$D$10301*$E$2:$E$10301)}

=SUMPRODUCT($D$2:$D$10301*$E$2:$E$10301)

=SUMPRODUCT($D$2:$D$10301,$E$2:$E$10301)


Све три формуле производе исти резултат, али трећој формули зареза синтаксе за СУМПРОДУКТ треба само око 77% времена за израчунавање него што треба другим формулама.

Користите ДСУМ уместо више услова низа формуле.


Ако имате само неколико формула низа суме са више услова можда ћете моћи да користите ДСУМ уместо тога. ДСУМ је знатно бржи од еквивалентног низа формуле. Недостатак ДСУМ је да критеријуми морају да буду у посебном опсегу, што га чини непрактичним да се користи и одржава у многим околностима.

Користите ФастЕксел АВЛООКУПС.


Можете користити БрзуЕксел Верзију 2 АВЛУКУПС функцију унутар СУМ функције да се врати више редова и сабере их. Ако радите више условних сума, нарочито на сортираном податку АВЛООКУПС може да буде знатно бржи од формуле низа.

Низ и функција за прорачун уских грла


Екселов мотор рачунања је оптимизован да искористи формулу низа и функције да се референтно креће. Међутим, неки необични аранжмани ових формула и функција могу понекад, али не увек, проузроковати значајно повећање рачунања времена.

Ако пронађете калкулацију уско грло укључивање низ-формуле и опсег функција потражи:
  • Делимично преклапање референце:
  • Формуле низа / опсег функција где је референца део блока ћелије које су обрачунате у другој формули низа / опсег функција. Ова ситуација се често јавља у анализи временских серија.
  • Један скуп формула референцирања по реду, и други сет формула референцирања првог сета по колони:
  • Велики скуп једног-реда формула низа покрива блок колона, са СУМ функција у подножју сваке колоне.

Предности и недостаци Формуле Низа


Плус поени Формуле Низа:
  • Формуле Низа су концизне: Можете елиминисати колоне и редове за паковање калкулације у формулу низа.
  • Формуле Низа су моћне: Можете лако извршити многе сложене калкулације као што је више условних сума и рачуна помоћу формуле низа
  • Формула Низа штеди простор на диску: Коришћење формуле низа који покрива опсег ћелија може да смањи величину радне свеске у поређењу еквивалентне индивидуалне формуле. Коришћена меморија обично не изгледа значајно смањена.
  • Формула Низа понуде повећане заштите: Ексел ће вам само омогућити да промените комплетан блок формуле низа тако да је корисник спречен да случајно промени једну формулу.
Минус поени Формуле Низа:
  • Ефекат Црне Кутије: Формула Низа може бити сложен и тешко разумљив. Већина Екцсел корисника не разумеју формуле низа уопште. Ово може да смањи поверење и употребљивост ваше табеле.
  • Режијски обрачун: Сваки пут када се формула низа израчунава све виртуелне ћелије потребне за формуле низа су израчунате, без обзира да ли је то потребно или не. То може довести до низа конфигурација да буду спорије од не-низа еквивалентана скупа формула.
  • Услов за све компоненте формула низа да буду исте величине: Ово може захтевати формулу низа да изврши велики број непотребних калкулација. Овај проблем се често јавља у СУМ са вишеструким условима проблема. Сортирање и онда израчунавање подскупа опсега који треба да се сажме често може направити значајан напредак израчунавања брзине. СУМИФ пример показује како неколико сати Прорачуна Низа може бити смањен на испод секунде.




Published (Last edited): 04-06-2012 , source: http://www.decisionmodels.com/optspeedj.htm