Sumar.si y buscarv en una función Let

Z

zelarra

Board Regular
Joined
Jan 2, 2021
Messages
70
Office Version
  1. 365
Platform
  1. Windows
Hola, gente.

Quería pedir ayuda. Estoy intentando crear un resumen de una hoja donde tengo las incidencias de mi trabajo. En una hoja, tengo la fecha, el tipo de incidencia, las horas de inicio y fin, y la duración.

Quiero conseguir un resumen por años, pero me atasco al intentar meter en una función Let las funciones Sumar.si y Buscarv, y no sé la manera de seguir.

ScreenShot001.jpg

Espero que alguien me pueda ayudar a lograrlo. Muchas gracias de antemano.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Are your account details correct? Are you using Excel 2016? I ask because the file in post #3 uses functions not found in Excel 2016.
If you are using Excel 365, then a single complicated formula can construct the summary table.
Source worksheet:
Resumen_rev1 (version 1).xlsm
ABCDE
1Hora
2FechaIncidenciaInicioFinalDuración
3jueves, 20 de abril de 2023a8:209:501:30
4viernes, 28 de abril de 2023a8:199:341:15
5jueves, 4 de mayo de 2023a9:4511:071:22
6jueves, 11 de mayo de 2023a8:099:471:38
7viernes, 19 de mayo de 2023b 
8lunes, 22 de mayo de 2023b 
9jueves, 25 de mayo de 2023a10:1511:181:03
10martes, 13 de junio de 2023c18:0119:291:28
11lunes, 17 de julio de 2023a8:239:220:59
12martes, 18 de julio de 2023d8:5310:231:30
13miércoles, 19 de julio de 2023a13:4114:300:49
14viernes, 28 de julio de 2023b 
15martes, 1 de agosto de 2023b 
16miércoles, 9 de agosto de 2023a7:208:080:48
17jueves, 10 de agosto de 2023a13:4614:300:44
18martes, 22 de agosto de 2023e 
19miércoles, 23 de agosto de 2023e 
20jueves, 24 de agosto de 2023e 
21viernes, 25 de agosto de 2023e 
22lunes, 28 de agosto de 2023e 
23martes, 29 de agosto de 2023e 
24miércoles, 30 de agosto de 2023e 
25jueves, 31 de agosto de 2023e 
26viernes, 1 de septiembre de 2023e 
27lunes, 4 de septiembre de 2023e 
28martes, 12 de septiembre de 2023a13:2314:301:07
29jueves, 14 de septiembre de 2023f 
30viernes, 15 de septiembre de 2023f 
31jueves, 28 de septiembre de 2023g8:269:140:48
32viernes, 29 de septiembre de 2023a13:5014:300:40
33martes, 3 de octubre de 2023h14:1514:300:15
34viernes, 6 de octubre de 2023h9:1711:182:01
35miércoles, 11 de octubre de 2023d10:1211:371:25
36jueves, 26 de octubre de 2023h8:309:280:58
Hoja1
Cell Formulas
RangeFormula
E3:E36E3=IF(OR(C3="",D3=""),"",D3-C3)

Output:
Resumen_rev1 (version 1).xlsm
ABCDEIJKNOPQ
1IncidenciaTotalDisponiblesQuedanDuraciónebElegir año2023Años
2a1111:5520232082023
3e102010
4b484
5h33:14
6d22:55
7f2
8c11:28
9g10:48
Hoja2
Cell Formulas
RangeFormula
A1:E9A1=LET(ary,Hoja1!A:E,asign,J1:K2,headers,{"Incidencia","Total","Disponibles","Quedan","Duración"}, rcount,XMATCH(1,--ISNUMBER(TAKE(ary,,1)),0,-1)-2, v,CHOOSEROWS(ary,SEQUENCE(rcount,,3)), v2ary,CHOOSECOLS(v,2), durary,CHOOSECOLS(v,5), fincid,UNIQUE(v2ary), matchary,N(v2ary=TRANSPOSE(fincid)), totals,TRANSPOSE(MMULT(SEQUENCE(1,ROWS(matchary),1,0),matchary)), diasasign,MAP(fincid,LAMBDA(r,IF(r=INDEX(asign,1,1),INDEX(asign,2,1),IF(r=INDEX(asign,1,2),INDEX(asign,2,2),"")))), quedan,IF(diasasign="","",diasasign-totals), dura,TRANSPOSE(MMULT(SEQUENCE(1,ROWS(matchary),1,0),matchary*N(durary))), dura2, IF(dura=0,"",dura), fulltbl,VSTACK(headers,SORT(HSTACK(fincid,totals,diasasign,quedan,dura2),2,-1)),fulltbl)
Q2Q2=LET(colA,Hoja1!A:A,UNIQUE(YEAR(FILTER(colA,ISNUMBER(colA)))))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
O1List=Q2#

It is not clear to me whether the "year" matters. All of the dates in column A of Sheet1 occur in 2023, and the year selected in Sheet2!O1 is also 2023, but none of the formulas enforce a requirement to consider only those dates occuring in the same year as Sheet2!O1. Is that okay?...Are you handling that some other way?
 
Upvote 0
Solution
Muchas gracias por vuestras ideas. He conseguido lograr lo que buscaba.

KRice, el año importa, pues ahora mismo solo he introducido las incidencias en mi trabajo de 2023, pero cuando llegue 2024, iré metiendo la de ese año, y así sucesivamente.

Por otra parte, he cambiado la versión de Office que usaba, pues ahora mismo uso 365.

Saludos.


Thank you very much for your ideas. I have managed to achieve what I was looking for.

KRice, the year matters, because right now I have only entered the incidents in my work from 2023, but when 2024 arrives, I will enter the one from that year, and so on.

On the other hand, I have changed the version of Office I was using, since right now I use 365.

Greetings.
 
Upvote 0
Thank you for the explanation, and I am glad you have something working. If you do adapt the approach I offered, then the year issue will need to be addressed. The easiest fix is to modify the definition for the v array to include a FILTER that ensures only dates occurring in the selected Sheet2!O1 calendar year will be considered. In this example, I changed the first entry (cell A3 on Sheet1) to the year 2024, which means the "a" incidents in 2023 will NOT consider its 1:30 duration. Here is a revised approach to handle the year requirement:
Resumen_rev1 (version 1).xlsm
ABCDEIJKNOPQ
1IncidenciaTotalDisponiblesQuedanDuraciónebElegir año2023Años
2a1010:2520232082024
3e1020102023
4b484
5h33:14
6d22:55
7f2
8c11:28
9g10:48
Hoja2
Cell Formulas
RangeFormula
A1:E9A1=LET(ary,Hoja1!A:E,asign,J1:K2,headers,{"Incidencia","Total","Disponibles","Quedan","Duración"}, rcount,XMATCH(1,--ISNUMBER(TAKE(ary,,1)),0,-1)-2, v,LET(vall,CHOOSEROWS(ary,SEQUENCE(rcount,,3)),FILTER(vall,YEAR(CHOOSECOLS(vall,1))=O1)), v2ary,CHOOSECOLS(v,2), durary,CHOOSECOLS(v,5), fincid,UNIQUE(v2ary), matchary,N(v2ary=TRANSPOSE(fincid)), totals,TRANSPOSE(MMULT(SEQUENCE(1,ROWS(matchary),1,0),matchary)), diasasign,MAP(fincid,LAMBDA(r,IF(r=INDEX(asign,1,1),INDEX(asign,2,1),IF(r=INDEX(asign,1,2),INDEX(asign,2,2),"")))), quedan,IF(diasasign="","",diasasign-totals), dura,TRANSPOSE(MMULT(SEQUENCE(1,ROWS(matchary),1,0),matchary*N(durary))), dura2, IF(dura=0,"",dura), fulltbl,VSTACK(headers,SORT(HSTACK(fincid,totals,diasasign,quedan,dura2),2,-1)),fulltbl)
Q2:Q3Q2=LET(colA,Hoja1!A:A,UNIQUE(YEAR(FILTER(colA,ISNUMBER(colA)))))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
O1List=Q2#
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question Question
Pegar una lista de valores sólo en celdas visibles
Replies
1
Views
4K
DanteAmor
DanteAmor
D
  • Question Question
Tengo un proyecto en el cual tengo que usar Excel... !Necesito ayuda¡
Replies
1
Views
586
sijpie
sijpie
M
  • Solved
Problema con línea de macro
Replies
4
Views
1K
misssalsa
M
I
  • Question Question
Direccionar de un rango a una celda especifica
Replies
0
Views
604
Inocente
I
L
  • Question Question
Enviar datos específicos de una hoja a otra al cumplir una condición
Replies
0
Views
191
leobrice
L

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,632
Latest member
jladair

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top