1. <rp id="asmyu"></rp>
      1. <progress id="asmyu"><track id="asmyu"></track></progress>

        <em id="asmyu"><strike id="asmyu"><u id="asmyu"></u></strike></em>
        <span id="asmyu"></span>
        <rp id="asmyu"><ruby id="asmyu"><input id="asmyu"></input></ruby></rp>

            二維碼 購物車
            部落窩在線教育歡迎您!

            BYROW和BYCOL函數用法詳解

            ?

            作者:小窩來源:部落窩教育發布時間:2024-04-16 11:10:19點擊:613

            分享到:
            0
            收藏    收藏人氣:0人
            版權說明: 原創作品,禁止轉載。
            編按:

            BYROWBYCOL函數可以對區域中的數據逐行或者逐列進行聚合運算,輸出一個單列或單行的數組。下面詳解這兩個函數的用法。

             

            今天學習LAMBDA最后兩個配套函數BYROWBYCOL。這兩個函數相比其他四個配套函數更簡單易懂,并且兩者參數和用法類似,所以小窩將它們放在一起介紹。

            1.作用與語法

            BYROW函數將LAMBDA算式應用于數組的每一行(可以理解為一行一行的計算),并返回一個單列等行(與被計算的數組行數相等)的數組。

            =BYROW(數組,LAMBDA運算式)

            BYCOL函數則是將LAMBDA算式應用于數組的每一列(可以理解為一列一列的計算),并返回一個單行等列(與被計算的數組列數相等)的數組。

            =BYCOL(數組,LAMBDA運算式)

            數組,要分列或分行計算的數組。

            LAMBDA運算式,按行或者列進行聚合計算,只有一個變量參數。

            注意:因為Excel函數不支持數組的數組,所以BYROW(BYCOL)LAMBDA每次運算結果須是單值。

            2. BYROWBYCOL基本用法

            1LAMBDA對行或者列進行聚合運算

            下方是銷售員各月的銷售額?,F在需要求每人最高月銷量,以及當月最高單人銷量。

             

            表格
描述已自動生成

             

            1)各人最高月銷量

            傳統做法就是輸入公式=MAX(C3:H3)并向下填充。

             

            表格
描述已自動生成

             

            現在用BYROW,公式=BYROW(C3:H11,LAMBDA(R,MAX(R)))

             

            表格
描述已自動生成

             

            說明:

            LAMBDA(R,MAX(R)),依次掃描C3:H11中第1行、第2行、第3行等數據,并獲取各行MAX值。

            公式不用向下填充,直接獲得各行的最大值。

            2)當月最高單人銷量

            BYCOL,公式=BYCOL(C3:H11,LAMBDA(C,MAX(C))).

             

            表格
描述已自動生成

             

            說明:

            公式不用向右填充,直接向右逐列掃描獲得各列的最大值。

             

            2)如果LAMBDA運算結果是多個值

            如果公式中LAMBDA運算結果并非單值,則BYROWBYCOL函數出現#CALC錯誤。

            譬如求各人最高的兩月銷量。

            公式=BYROW(C17:H25,LAMBDA(R,LARGE(R,{1,2}))),結果錯誤:

             

            表格
描述已自動生成

             

            說明:

            LARGE(R,{1,2})會返回每行第一、第二大的數值,輸出的并非單個值,因此出現#CALC錯誤。

             

            這個時候,可以繼續嵌套聚合函數,讓值變成單個。

            譬如此處在LARGE函數外嵌套文本組合函數TEXTJOIN函數,TEXTJOIN("、",,LARGE(R,{1,2})),結果就正確了:

             

            表格
描述已自動生成

             

            3. BYROWBYCOL典型運用

            似乎BYROWBYCOL并沒有什么實際用處:求最大值中,用它們,公式比用傳統函數的公式還復雜。實際上BYROWBYCOL的主要價值并非是直接用來得到最終結果,而是將它們的結果作為內存數組供其他函數使用。

             

            1)求平均月銷量大于280的人員

             

            表格
描述已自動生成

             

            求平均銷量大于某個值的人員名單,肯定需要先得到各人的平均值,然后進行比較篩選。

            此時,如果用傳統函數就較繁瑣。

            一種方法是增加一列平均銷量輔助列,然后用FILTER進行篩選。

             

            表格
描述已自動生成

             

            一種方法是用SUBTOTAL取代AVERAGE,并搭配OFFSET函數獲得平均值數組,然后篩選。公式比較復雜還不易理解:

            =FILTER(B33:B41,SUBTOTAL(101,OFFSET(C32:H32,ROW(1:9),,1,6))>280)

             

            表格
描述已自動生成

             

            說明:

            不用輔助列,就需要把平均值作為數組用于公式中,但AVRERAGE是聚合函數,單用它沒法得到平均值數組。此處SUBTOTALOFFSET的作用就是得到平均值數組。這里有點神奇,大家可以留意。

            單寫公式=OFFSET(C32:H32,ROW(1:9),,1,6),因為函數不支持數組的數組,所以結果會是#VALUE錯誤;但當在外面嵌套上SUBTOTAL后,得到正確結果。

             

            圖形用戶界面, 表格
描述已自動生成

             

            由于不存在隱藏行,因此SUBTOTAL函數功能代碼用1也可以。1101都表示求平均值。

             

            如果用今天的BYROW,則相對簡單并好理解:

             

            表格
描述已自動生成

             

            2)求銷量前3位人員名單

             

            游戲界面截圖
低可信度描述已自動生成

             

            如果不用動態數組函數,公式較復雜:

            =XLOOKUP(LARGE(SUBTOTAL(9,OFFSET(C44:H44,ROW(1:9),,1,6)),{1;2;3}),SUBTOTAL(9,OFFSET(C44:H44,ROW(1:9),,1,6)),B45:B53)

            即便用LET優化,也比較長:

            =LET(a,SUBTOTAL(9,OFFSET(C44:H44,ROW(1:9),,1,6)),XLOOKUP(LARGE(a,{1;2;3}),a,B45:B53))

             

            表格
描述已自動生成

             

            如果用動態數組函數和BYROW結合,公式比較簡單:

            公式=TAKE(SORT(HSTACK(B45:B53,BYROW(C33:H41,LAMBDA(x,SUM(x)))),2,-1),3,1)

             

            表格
描述已自動生成

             

            說明:

            BYROW(C33:H41,LAMBDA(x,SUM(x))),獲得各行的銷售合計;

            接著用HSTACK將姓名與銷售合計列組成新數組;

            然后再用SORT函數對新數組降序排序;

            最后用TAKE函數取排序后的第1列(姓名列)前3行。

             

            本文配套的練習課件請添加客服微信buluowojiaoyu索取。

            Excel高手,快速提升工作效率,部落窩教育Excel精品好課任你選擇!

            掃下方二維碼關注公眾號,可隨時隨地學習Excel

            IMG_256

            相關推薦:

            數據與表格合并函數HSTACK和VSTACK

            數組提取函數TAKE和DROP

            以一敵十的SUBTOTAL函數,你怎能錯過?

            SORT函數排序比用排序命令還好用

            版權申明:

            本文作者小窩;部落窩教育享有稿件專有使用權。若需轉載請聯系部落窩教育。

             

             

            国产欧美一区二区精品久久久_自拍性旺盛老熟女视频_人与禽ZOZ0性伦交老太婆_久久精品国产第一区二区三区