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

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

手機(jī)如何做表格：點(diǎn)擊查看

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

比如這樣的：

還有這樣的：

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

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

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

公式有兩種寫(xiě)法，第一種：

=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時(shí)，就返回0，否則就直接看第二個(gè)IF，這個(gè)時(shí)候就是默認(rèn)的大于等于1，所以第二個(gè)IF只需要寫(xiě)數(shù)字的上限，小于2就可以了，后面的是同樣的道理。

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

=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時(shí)，就返回600，大于等于6時(shí)，返回400，大于等于4時(shí)，返回300，大于等于2時(shí)，返回200，大于等于1，返回100，否則返回0。

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

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

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

一、相對(duì)引用

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

如下所示，選中C9單元格后，在編輯欄輸入等號(hào)，然后直接點(diǎn)擊A9單元格，輸入加號(hào)，再直接點(diǎn)擊B9單元格，最后回車(chē)。

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

此時(shí)我們?nèi)ヒ来吸c(diǎn)擊C列的單元格，可以看到編輯欄的公式，隨著我們的下移，行號(hào)也會(huì)隨之改變，變?yōu)閷?duì)應(yīng)的行號(hào)。

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

二、絕對(duì)引用

絕對(duì)引用又是什么呢？

如下圖所示，選中D9單元格，輸入=sum（）之后，點(diǎn)中括號(hào)里，直接拖選A9-C9，再按F4，最后回車(chē)。

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

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

三、混合引用

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

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

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

這種只鎖定行號(hào)或列號(hào)的操作，就叫做混合引用?；旌系囊馑际?，單元格引用既包括了絕對(duì)引用又包括了相對(duì)引用。譬如，$A1,就是表示單元格的列號(hào)是絕對(duì)引用，行號(hào)是相對(duì)引用。混合引用的訣竅就是：向下拉只鎖定行號(hào)，向右拉只鎖定列號(hào)。這樣在拖動(dòng)過(guò)程中，公式就不會(huì)變化。

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

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

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

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