在之前的文章《實用Excel技巧分享：學(xué)習(xí)怎么做多因素排名統(tǒng)計表？》中，我們了解了多因素排名統(tǒng)計表的制作方法，而今天我們來聊聊Excel公式中的引用，絕對引用混合引用都不懂？難怪你總是公式填充錯誤！

下面給大家分析一下這道題。

手機如何做表格：點擊查看

瓶子看了大家的留言，大家的公式普遍存在一個小問題：每一個嵌套的IF函數(shù)的第一參數(shù)都用了AND函數(shù)去規(guī)定數(shù)據(jù)的范圍。

比如這樣的：

還有這樣的：

不管上面的公式是否正確，大家思考一下，我們有必要寫這么多的AND嗎？

或許大家是受了教程的影響——誰叫這篇教程正好例舉了AND函數(shù)，誰叫補助年限剛好能用AND描述呢？哈哈……

（上次留的題目中，由于瓶子的粗心，竟然忘了寫大于10年的補助了！自罰多寫一些教程，那下面我們就統(tǒng)一大于10年也是補助600）

公式有兩種寫法，第一種：

=IF（C:C<1 ，0,IF（C:C<2,100,IF(C:C<4,200,IF(C:C<6,300,IF(C:C<8,400, 600）)）))

當(dāng)C列的數(shù)字，小于1時，就返回0，否則就直接看第二個IF，這個時候就是默認的大于等于1，所以第二個IF只需要寫數(shù)字的上限，小于2就可以了，后面的是同樣的道理。

上面的公式是全部限制了上限，其實還可以全部限制下限，第二種：

=IF（C:C>=8,600,IF(C:C>=6,400,IF(C:C>=4,300,IF(C:C>=2,200,IF(C:C>=1,100,0）))))

當(dāng)C列的數(shù)字，大于等于8時，就返回600，大于等于6時，返回400，大于等于4時，返回300，大于等于2時，返回200，大于等于1，返回100，否則返回0。

大家可以發(fā)現(xiàn)，這道題如果用IF來解答的話，采用第二種方法，每次都設(shè)置數(shù)據(jù)下限，更易理解。

除此以外，瓶子還想告訴大家，在編輯欄輸入公式的時候，有些時候可以不必手動輸入，比如上面的公式中，我們直接點擊列號C，公式中就可以出現(xiàn)了C:C。上面第二個小伙伴寫的公式中采用了絕對引用，也可以直接拖選數(shù)據(jù)區(qū)域B5-B14后，按F4，就變成了$B$5:$B$14。

可能剛?cè)腴T的小伙伴對相對引用和絕對引用不是很了解，今天瓶子就來給大家講解一下。

一、相對引用

如下所示，對A、B兩列的數(shù)據(jù)求和，求和結(jié)果顯示在C列。

如下所示，選中C9單元格后，在編輯欄輸入等號，然后直接點擊A9單元格，輸入加號，再直接點擊B9單元格，最后回車。

選中C9單元格，鼠標(biāo)放在右下角，雙擊，可以看到整列都得到了求和結(jié)果。

此時我們?nèi)ヒ来吸c擊C列的單元格，可以看到編輯欄的公式，隨著我們的下移，行號也會隨之改變，變?yōu)閷?yīng)的行號。

大家可以試一下在A18單元格輸入公式，對A列求和，橫向拖動填充公式到C18單元格。大家會看到橫向拖動公式的時候，列號會自動改變，變?yōu)閷?yīng)的列號。這就叫做相對引用，單元格的相對引用格式就是列號+行號，譬如A10、B14等都是相對引用。相對引用的最大特點就是當(dāng)拖動填充公式的時候，單元格會自動根據(jù)公式所在位置發(fā)生變化。

二、絕對引用

絕對引用又是什么呢？

如下圖所示，選中D9單元格，輸入=sum（）之后，點中括號里，直接拖選A9-C9，再按F4，最后回車。

此時雙擊填充公式，再點擊D列各單元格，查看公式，會發(fā)現(xiàn)整列公式都一模一樣。

與相對引用區(qū)別在哪？區(qū)別在于當(dāng)前公式中引用的行號和列號前都加了“$”符號。$就是一把“鎖”，鎖定了單元格，所以不管我們橫著拉，還是豎著拉，公式不會有任何改變，這就叫做絕對引用。

三、混合引用

前面公式中，行號和列號都加了“鎖”，但是大家想一想，在豎著填充公式的時候，即便我們不鎖定列號，列號也不會改變。

如下所示，在E9單元格中，輸入=sum（）之后，點中括號里，拖選A9-C9單元格區(qū)域，然后按兩次F4，此時就只鎖定了行號。

雙擊向下填充后，可以看到結(jié)果和前面絕對引用的結(jié)果一模一樣。

這種只鎖定行號或列號的操作，就叫做混合引用?；旌系囊馑际?，單元格引用既包括了絕對引用又包括了相對引用。譬如，$A1,就是表示單元格的列號是絕對引用，行號是相對引用?；旌弦玫脑E竅就是：向下拉只鎖定行號，向右拉只鎖定列號。這樣在拖動過程中，公式就不會變化。

下面就是驗證大家學(xué)習(xí)成果的時候啦！

看下面這張表格，需要在B2-E5單元格區(qū)域都顯示對應(yīng)的第幾列第幾排，瓶子在B2單元格設(shè)置好了公式，現(xiàn)在大家來想一想，在公式中如何給行號和列號上“鎖”，就可以一次性完成整個區(qū)域的填充呢？

把你答案留在下方留言區(qū)！

相關(guān)學(xué)習(xí)推薦：excel教程