Viena dinaminio masyvo formulė - „Excel“ patarimai

Turinys

Pastaba

Tai vienas iš straipsnių serijos, kuriame pateikiami išsamūs sprendimai, atsiųsti iššūkiui „Podcast 2316“.

„YouTuber Rico S“ laikėsi visiškai kitokio požiūrio. Jo lentelę sukuria viena dinaminio masyvo formulė.

Vienas dinaminis masyvas

„O“ suma neužpildoma. Jei turėtumėte „Charles Williams Speed Tools V4“, Rico formulę galėtumėte įvesti į TOTALS, kaip parodyta žemiau.

„SpeedTools V4“

Čia, be tolesnių komentarų, paskelbta Rico formulė 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))

Atnaujinkite! Viršuje aš panaudojau Charleso Williamso HTOTALS funkciją, kad pagerčiau Rico formulę. Rico įsitraukė į „FastExcel“ priedo dokumentaciją ir rado dvi nuostabias funkcijas: SETMEM ir GETMEM. Galite optimizuoti savo formules saugodami ir gaudami labai skaičiavimo reikalaujančių išraiškų, naudojamų daugiau nei vieną kartą formulėje, rezultatus. Tai yra novatoriška funkcija.

Rico naudojo jį, kad sumažintų savo 6866 simbolių formulę iki 593 simbolių formulės:

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

Daugiau apie SETMEM ir GETMEM skaitykite čia.

Charlesas Williamsas užsiregistravo ir pasiūlė šią formulę:

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

2020 m. Kovo 19 d., Kai LET funkcija debiutavo „Excel“, Rico S atsiuntė šią trumpesnę formulę. Šiuo metu tam reikia „Office 365“ su „Insider 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) )