Terapkan fungsi ke baris
=BYROW(array, function)| Parameter | Deskripsi |
|---|---|
array |
The range or array to process. |
function |
The function to apply to each row. |
BYROW takes two arguments: an array to process and a function to run on each row. The function can be written in two ways. The first way is as a long-
=BYROW(array,LAMBDA(row,SUM(row)))
=BYROW(array,LAMBDA(row,AVERAGE(IF(row<>0,row))))
=BYROW(array,LAMBDA(row,MAX(row)-MIN(row)))The second way is a short-form "eta lambda" syntax. Instead of wrapping the function in a LAMBDA, you pass just the function name. This works for sing
=BYROW(array,SUM) // sum each row
=BYROW(array,MAX) // max of each row
=BYROW(array,AVERAGE) // average of each row
=BYROW(array,COUNTA) // count of non-empty valuesIn the worksheet below, the goal is to sum the values in each row of the range C5:H15. The formula in J5 is:
=BYROW(C5:H15,SUM)BYROW processes all 11 rows at once and returns an array of 11 sums that spill into J5:J15. The same pattern works for any single-argument aggregate:
=BYROW(C5:H15,MAX) // max of each row
=BYROW(C5:H15,MIN) // min of each row
=BYROW(C5:H15,AVERAGE) // average of each rowBYROW is useful when you want to count values per row that meet one or more specific conditions. This is where the eta syntax breaks down and the full
=BYROW(C5:H15,LAMBDA(row,SUM(--(row>90))))Zeros sometimes mean "no data" rather than "a real value of zero" (a store that wasn't open, a test that wasn't taken, a product that wasn't stocked).
=BYROW(C5:G15,LAMBDA(row,AVERAGE(IF(row<>0,row))))