jegyzet
Ez egy olyan cikksorozat, amely a Podcast 2316 kihíváshoz elküldött megoldásokat részletezi.
A YouTuber Rico S teljesen más megközelítést alkalmazott. Táblázatát egyetlen dinamikus tömbképlet generálja.
A Total in O nem töltődik be. Ha Charles Williams Speed Tools V4-es verzióval rendelkezne, Rico képletét TOTALS-ba foglalhatja az alábbiak szerint.
Itt, további megjegyzések nélkül, itt található Rico képlete az I13-ban:
=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))
Frissítés! Fent a Charles Williams HTOTALS funkciót használtam Rico képletének javítására. Rico belemerült a FastExcel bővítmény dokumentációjába, és két csodálatos funkciót talált: a SETMEM és a GETMEM. Optimalizálhatja a képleteket úgy, hogy a képletben többször használt, nagyon számításigényes kifejezések eredményeit tárolja és visszakeresi. Ez egy úttörő funkció.
Rico arra használta, hogy 6866 karakteres képletét 593 karakteres képletére redukálja:
=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)))
A SETMEM-ről és a GETMEM-ről itt olvashat bővebben.
Charles Williams bejelentkezett és ezt a képletet javasolta:
=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árcius 19-én, miután a LET függvény debütált az Excelben, Rico S küldte ezt a rövidebb képletet. Ehhez jelenleg az Office 365 és a bennfentesek gyors használata szükséges.
=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)
)
Térjen vissza a Podcast 2316 kihívás főoldalára.
A sorozat következő cikkének elolvasása: Old School Helper oszlopok.
