1. Introduction to Excel BYCOL
The
BYCOL function allows you to apply a Lambda function to each column in a range and return an array of results.
2. BYCOL Arguments
This is the syntax for using the
BYCOL function.=BYCOL(array, lambda(column))array: required The array or range to which the Lambda function will be applied. This can be a single column, multiple columns, or an entire table.lambda: required The Lambda function to apply to each column. The column parameter represents the current column in the array, and the Lambda function defines the operation to be performed on that column.3. Getting Started with the BYCOL Function
Here we use the classic example of calculating the sum of a column.
Key in:
Key in:
=BYCOL(
4. Select Source Data Range
At this point, the function will prompt you to select the source data range. Click on the start of the source data at
C5, then hold down the Shift key and click on the end of the source data at F7. This will select the content of the source data and fill it into the function parameter as: =BYCOL(C5:F7.
5. SUM as Lambda Function
Here we pass the Lambda function for column processing. Excel will intelligently suggest a series of functions that can be passed, all of which are used to process a column of data. We select
SUM as the Lambda function.
6. Almost Done
After passing the
SUM function, add the closing parenthesis, and the call to the BYCOL function is complete.
7. BYCOL Function Activated
Press Enter, and we can see that a sum has been successfully calculated for each column.
8. Short Form of Lambda Function
Some may wonder why the second parameter requires a Lambda function, while passing
SUM is sufficient. The current call is actually equivalent to: =BYCOL(C5:F7,LAMBDA(column, SUM(column))).
9. Combining with Other Functions
Similarly, we can combine other functions as Lambda functions to use.
For example, here we use COUNTIF to count the number greater than 250:
For example, here we use COUNTIF to count the number greater than 250:
=BYCOL(C5:F7,LAMBDA(column,COUNTIF(column,">250"))).10. 🎉 Finish! 🎉
Author's Note: I hope you can feel the effort I put into these tutorials. I hope to create a series of very easy-to-understand Excel tutorials.If it is useful, help me share these tutorials, thank you!
Follow me: