Једна динамичка формула низа - Екцел савети

Преглед садржаја

Белешка

Ово је један од низа чланака који детаљно описују решења послана за изазов Подцаст 2316.

ИоуТубер Рицо С је заузео потпуно другачији приступ. Његова табела се генерише помоћу једне динамичке формуле низа.

Један динамички низ

Укупно у О се не попуњава. Ако сте имали Цхарлес Виллиамс Спеед Тоолс В4, могли бисте Рицо-ову формулу умотати у УКУПНО, као што је приказано доле.

СпеедТоолс В4

Овде је, без додатних коментара, објављена Рикова формула у И13:

=CHOOSE((1,2,3,4,5,6),LEFT(INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),FIND(":",INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),1)-1),RIGHT(INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),LEN(INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1))-FIND(":",INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),1)),INDEX(UglyData,MATCH(LEFT(INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),FIND(":",INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),1)-1),$A$3:$A$9,0),MATCH(RIGHT(INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),LEN(INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1))-FIND(":",INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),1)),$A$3:$Z$3,0)+1),INDEX(UglyData,MATCH(LEFT(INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),FIND(":",INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),1)-1),$A$3:$A$9,0),MATCH(RIGHT(INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),LEN(INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1))-FIND(":",INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),1)),$A$3:$Z$3,0)+2),INDEX(UglyData,MATCH(LEFT(INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),FIND(":",INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),1)-1),$A$3:$A$9,0),MATCH(RIGHT(INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),LEN(INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1))-FIND(":",INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),1)),$A$3:$Z$3,0)+3),INDEX(UglyData,MATCH(LEFT(INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),FIND(":",INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),1)-1),$A$3:$A$9,0),MATCH(RIGHT(INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),LEN(INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1))-FIND(":",INDEX($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee"),INT(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1)/4+1),MOD(SEQUENCE(COUNTA($A$4:$A$9&":"&FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),1,0,1),4)+1),1)),$A$3:$Z$3,0)+4))

Ажурирање! Изнад сам користио функцију Цхарлес Виллиамс ХТОТАЛС за побољшање Рицо-ове формуле. Рицо је загазио у документацију за додатак ФастЕкцел и пронашао две невероватне функције: СЕТМЕМ и ГЕТМЕМ. Можете оптимизирати своје формуле похрањивањем и проналажењем резултата врло израчунљивих израза кориштених више пута у формули. Ово је револуционарна функција.

Рицо га је искористио да смањи своју формулу од 6.866 знакова на формулу од 593 знака:

=HTOTALS(CHOOSE((1,2,3,4,5,6),SETMEM(LEFT(SETMEM(LISTDISTINCTS(TRANSPOSE($A$4:$A$9)&":"&TRANSPOSE(FILTER($A$3:$Z$3,LEFT($A$3:$Z$3,8)="Employee")),,,FALSE),"A"),FIND(":",GETMEM("A"),1)-1),"B"),SETMEM(RIGHT(GETMEM("A"),LEN(GETMEM("A"))-FIND(":",GETMEM("A"),1)),"C"),INDEX(UglyData,MATCH(GETMEM("B"),$A$4:$A$9,0)+1,MATCH(GETMEM("C"),$A$3:$Z$3,0)+1),INDEX(UglyData,MATCH(GETMEM("B"),$A$4:$A$9,0)+1,MATCH(GETMEM("C"),$A$3:$Z$3,0)+2),INDEX(UglyData,MATCH(GETMEM("B"),$A$4:$A$9,0)+1,MATCH(GETMEM("C"),$A$3:$Z$3,0)+3),INDEX(UglyData,MATCH(GETMEM("B"),$A$4:$A$9,0)+1,MATCH(GETMEM("C"),$A$3:$Z$3,0)+4)))

Прочитајте више о СЕТМЕМ-у и ГЕТМЕМ-у овде.

Цхарлес Виллиамс се пријавио и предложио ову формулу:

=HTOTALS(VSTACK(HSTACK(,A4:A9,G3,SLICES(A4:AB9,0,H4:K4)),HSTACK(,A4:A9,L3,SLICES(A4:AB9,0,M4:P4)),HSTACK(,A4:A9,Q3,SLICES(A4:AB9,0,R4:U4)),HSTACK(,A4:A9,V3,SLICES(A4:AB9,0,W4:Z4))))

19. марта 2020. године, након што је функција ЛЕТ дебитовала у Екцелу, Рицо С је послао ову краћу формулу. Ово тренутно захтева Оффице 365 са Инсидерс Фаст.

=LET( _splitChar,"~", _categories,$A$4:$A$9, _colHdrs,$A$3:$Z$3, _employees,FILTER(_colHdrs,LEFT(_colHdrs,8)="Employee"), _unique2DTable,_categories&_splitChar&_employees, _cntE,COUNTA(_employees), _cnt,COUNTA(_unique2DTable), _uniqueList,INDEX(_unique2DTable,INT(SEQUENCE(_cnt,1,0,1)/_cntE+1),MOD(SEQUENCE(_cnt,1,0,1),_cntE)+1), _category,LEFT(_uniqueList,FIND(_splitChar,_uniqueList,1)-1), _employee,RIGHT(_uniqueList,LEN(_uniqueList)-FIND(_splitChar,_uniqueList,1)), _row,MATCH(_category,_categories,0)+1, _col,MATCH(_employee,_colHdrs,0), _Q1,INDEX(UglyData,_row,_col+1), _Q2,INDEX(UglyData,_row,_col+2), _Q3,INDEX(UglyData,_row,_col+3), _Q4,INDEX(UglyData,_row,_col+4), _totalCol,INDEX(UglyData,_row,_col), _finalTable,CHOOSE((1,2,3,4,5,6,7),_category,_employee,_Q1,_Q2,_Q3,_Q4,_totalCol), SORT(_finalTable,2) )