我這裡說的組合函數就是多個函數搭配使用的情況,很多函數的參數中存在第幾行、第幾行,第幾個等情況,像常用的Vlookup、Hlookup、Index、Offset、Address、Large、Small等等結合使用,使用的範圍是非常廣的,也是非常頻繁的。
一、column()函數和row()函數的語法
column()函數是返回所在的列标,參數數可以是單元格,也可以是一個區域,比如column(A1)=1,column(A1:A6)=1,column(A:A)=1
row()函數是返回所在的行标,參數數可以是單元格,也可以是一個區域,比如row(A1)=1,row(A1:C1)=1,row(1:1)=1
下面以ROW函數為例,column()函數用法類似,隻不過列标是字母,行标是數字的區别,比如表示整列為A:A(第一列)或C:C(第3列)用字母來表示,表示整行是1:1(第一行)或2:2(第二行)
二、與VLOOKUP函數、HLOOKUP函數、INDEX函數的結合使用
VLOOKUP函數和HLOOKUP函數的第3個參數是第幾列和第幾行,INDEX函數的第2個和第3個參數是第幾行、第幾列,我們可以把這些位置的參數用column()函數和row()函數來替換,如果表比較大的時候我覺得還是很好用的。
下面我就用VLOOKUP和INDEX函數來舉個例子,HLOOKUP用法類似,隻不過是VLOOKUP主要選擇與column函數組合,HLOOKUP主要和ROW函數進行組合,如果直接用column()函數和row()函數返回結果不合适,可以通過column()加減數字或row()函數加減數字來實現。
與VLOOKUP函數結合使用
與INDEX函數結合使用
三、與SMALL函數、LARGE函數的結合使用
SMALL函數和LARGE函數的語法是基本一樣的,(區域,第幾大或第幾小),如果表示從一組數據中找出前十大來,我們就可以用column()函數和row()函數來配合使用
四、與SUM函數結合實現隔行或隔列求和
這個其實是利用了行号或列号用MOD()求餘來實現的,比如下面的例子:
在N3單元格輸入
{=SUM(($B$3:$M$3)*(MOD(COLUMN(B3:M3),2)=0))},然後複制既可以完成計劃合計的計算,這個看起來比較複雜些,我就簡單說一下,計劃所在列号都是偶數,所以MOD(COLUMN(B3:M3),2)=0,偶數結果就是true,數字表示就是1,如果是奇數,那結果就是0,所以隻把偶數的進行了求和,在實際合計的計算我們就使用MOD(COLUMN(B3:M3),2)=1來計算。
五、和INDIRECT函數、ADDRESS函數的結合使用
INDIRECT函數、ADDRESS函數也是比較常用的比較高級的函數,尤其是sheet表比較多的時候,INDIRECT的快速取數的能力是相當強悍的,我以兩個表進行簡化展示,下面的表還是用的四提到的計劃和實際的表,隻不過我分為了2020年和2021年,
在B3輸入
=INDIRECT(B$2&"!"&ADDRESS(ROW('2020年'!C3),COLUMN('2020年'!C3),1))
在C3輸入
=INDIRECT(C$2&"!"&ADDRESS(ROW('2020年'!C3),COLUMN('2020年'!C3),1))
然後向下向右複制就完成數據了。
有話要說...