Формула за един динамичен масив - Съвети за Excel

Съдържание

Забележка

Това е една от поредицата статии, описващи подробно решения, изпратени за предизвикателството на Podcast 2316.

YouTuber Rico S възприе съвсем различен подход. Неговата таблица се генерира от една формула на динамичен масив.

Един динамичен масив

Общото в O не се попълва. Ако имате Charles Williams Speed Tools V4, можете да увиете формулата на Rico в ОБЩО, както е показано по-долу.

SpeedTools V4

Публикувано тук, без допълнителни коментари, е формулата на Рико в I13:

=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))

Актуализиране! По-горе използвах функцията на Charles Williams HTOTALS, за да подобря формулата на Рико. Рико се впусна в документацията за добавката FastExcel и откри две невероятни функции: SETMEM и GETMEM. Можете да оптимизирате формулите си, като съхранявате и извличате резултатите от много интензивни изчисления, използвани повече от веднъж във формула. Това е новаторска функция.

Рико го използва, за да намали формулата си от 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)))

Прочетете повече за SETMEM и GETMEM тук.

Чарлз Уилямс се регистрира и предложи тази формула:

=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 г., след като функцията LET дебютира в Excel, Rico S изпрати тази по-кратка формула. Понастоящем това изисква Office 365 с Insiders Fast.

=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) )