Excel snadno (Easy Excel)Community of friends of Excel (#excelsnadno)
Excel snadno (Easy Excel)Our about
Merge in Power Query using “Between And”.
New search function in Excel.
The first video of the two contains the ETL process within PowerQuery.
Second video is about PowerPivot. It’s a very broad topic, we’ll only show a few features of PowerPivot.
TextJoin is a newer function of Excel that allows you to create string array from values in a column with the option of using any delimiter.
The column from the example in Power Query will help you create a new column without the need to know the M language.
Combination of vlookup with match should be very helpful.
Pivot tables are very useful but sometimes you need to refer to the pivot table value. For this reason you can use GetPivotData function.
Very powerful function offset can move and increase or decrease range of cells. With this range we can do sum, average, etc.
Sumproduct function was very often used before function sumifs was released. It can sum range of cells with more conditions. Main advantage of this solution is that you can use combination of vertical and horizontal conditions. Disadvantage of this function is that it need to much perfomance. So it is not suitable for bigger solutions.
This function can be used of course as standard sumproduct formula where (a, b, c) sumproduct (d, e, f) equals (a*d + b*e + c*f).