How to use the Excel MAP and REDUCE functions
HTML-код
- Опубликовано: 7 ноя 2023
- This video explains how to use the MAP and REDUCE functions in Excel, and shows how to apply them to an Excel challenge from the Microsoft Excel World Championship (Flower Power, by Harry Gross).
It is very nice to explain complex ideas in a simple way.
Thanks Bo!
Thank you Dim and BO for your excellent work. I appreciate your efforts in creating video solutions for the Excel BI Power Query challenges. I find them very helpful and informative. Please keep up the good work and share more videos if possible.
Gracias Diarmuid por esta master class, me encanta como usas la funcion map en este ejemplo. Es un gusto poder verte resolviendo estos retos con Excel. Saludos.
Gracias! Hoy he aprendido la palabra 'retos' - me gusta! : )
Great video! Really appreciate the effort you are putting into the content.
For level 3, I have so far carried over my approach from Level 2 (changing the last condition from Sunflowers (S) to Tulips (T) to match the context of the question. My pivot input is currently in N43 as PivotInput. Avoiding a data table approach, I am stuck with solving the issue with a MAP function to pick up a range of inputs ("A1"-"C3") and feed it into the formula as opposed to just whatever is in N43.
Can a MAP() be added to this? If so, I would greatly appreciate your guidance on how to do so :)
In my mind, I'm picturing/hoping that MAP() can be applied somewhere and then an array of results will show for each input for "A1" to "C3" inputs that I can then apply a Max() to. I've tried to delete PivotInput, N43, from the formula below and wrap the remaining within =MAP([my range of inputs],lambda(PivotInput,[existing formula])) to no avail.
=LET(
PivotInput, N43,
STXletters, O43:W43,
matrixrange, $O$34:$W$34,
AD,CELL("address",INDIRECT(matrixrange)),
ADup,IFERROR(CELL("address",OFFSET(INDIRECT(matrixrange),-1,)),""),
ADdown,IFERROR(CELL("address",OFFSET(INDIRECT(matrixrange),1,)),""),
ADleft,IFERROR(CELL("address",OFFSET(INDIRECT(matrixrange),,-1)),""),
ADright,IFERROR(CELL("address",OFFSET(INDIRECT(matrixrange),,1)),""),
stackedAD,VSTACK(AD,ADup,ADdown,ADleft,ADright),
cleanedstackedAD, SUBSTITUTE(stackedAD,"$",""),
relevantcells,CHOOSECOLS(cleanedstackedAD,XMATCH(PivotInput,matrixrange,0)),
PivotedSTXletters,IF(ISNUMBER(MATCH(matrixrange,relevantcells,0)),SWITCH(STXletters,"T","S","S","T","X","X"),STXletters),
SUM(--(PivotedSTXletters="T"))
)
Wow, That was really cool. I didn't realize MAP could take multiple inputs. I also really struggled with that level. Here's another one that I need to work through to fully practice and understand it! Lot's of homework for the off season! Good luck in Vegas!
Thank Danny! And yes, never stop learning : )
Just worked through this one your way. So much easier knowing I can just work with the 2D array and the 1D array with the same formula, I had spent so much time flattening the lvl4 & 5 flowerbeds into a single line for my pathetic formulas previously!@@DimEarly
Great video! I think it would be helpful if you would do another video without the custom functions in map and reduce.
Glad you liked it!
I find it tricky to explain the value of these functions to people without using a more complex LAMBDA - the ‘basic’ examples just get people thinking about alternatives that don’t need that (e.g. REDUCE is not the best way to add up a list of numbers or concatenate a text string, and MAP is not the best way to combine text like in my canned example at the start).
There’s probably a sweet spot that’s in between the two, but in this case the genesis was the other way around (i.e. I looked at the problem and thought ‘this could make a good demo for MAP and REDUCE’ rather than looking at MAP and REDUCE and thinking about a good use case to demo them).
Hello! a quick question! is pivot function part to excel usual function or was it created for this workbook (using let or other)?
Yes, that’s a custom LAMBDA that was written for this workbook.
@@DimEarly got it! Thanks!!