Una fórmula de matriz dinámica: consejos de Excel

Tabla de contenido

Nota

Este es uno de una serie de artículos que detallan las soluciones enviadas para el desafío Podcast 2316.

YouTuber Rico S adoptó un enfoque completamente diferente. Su tabla se genera mediante una única fórmula de matriz dinámica.

Una matriz dinámica

El total en O no se está completando. Si tuviera Charles Williams Speed Tools V4, podría envolver la fórmula de Rico en TOTALES como se muestra a continuación.

SpeedTools V4

Publicado aquí, sin más comentarios, está la fórmula de Rico en 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))

¡Actualizar! Arriba, utilicé la función HTOTALS de Charles Williams para mejorar la fórmula de Rico. Rico se sumergió en la documentación del complemento FastExcel y encontró dos funciones asombrosas: SETMEM y GETMEM. Puede optimizar sus fórmulas almacenando y recuperando los resultados de expresiones muy intensivas en cálculos utilizadas más de una vez en una fórmula. Esta es una función innovadora.

Rico lo usó para reducir su fórmula de 6,866 caracteres a una fórmula de 593 caracteres:

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

Lea más sobre SETMEM y GETMEM aquí.

Charles Williams se registró y sugirió esta fórmula:

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

El 19 de marzo de 2020, después de que la función LET debutó en Excel, Rico S envió esta fórmula más corta. Actualmente, esto requiere Office 365 con 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) )