在之前的文章《實用excel技巧分享:學習怎么做多因素排名統計表?》中,我們了解了多因素排名統計表的制作方法,而今天我們來聊聊excel公式中的引用,絕對引用混合引用都不懂?難怪你總是公式填充錯誤!
下面給大家分析一下這道題。
瓶子看了大家的留言,大家的公式普遍存在一個小問題:每一個嵌套的if函數的第一參數都用了AND函數去規定數據的范圍。
比如這樣的:
還有這樣的:
不管上面的公式是否正確,大家思考一下,我們有必要寫這么多的AND嗎?
或許大家是受了教程的影響——誰叫這篇教程正好例舉了AND函數,誰叫補助年限剛好能用AND描述呢?哈哈……
(上次留的題目中,由于瓶子的粗心,竟然忘了寫大于10年的補助了!自罰多寫一些教程,那下面我們就統一大于10年也是補助600)
公式有兩種寫法,第一種:
=IF(C:C
當C列的數字,小于1時,就返回0,否則就直接看第二個IF,這個時候就是默認的大于等于1,所以第二個IF只需要寫數字的上限,小于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)))))
當C列的數字,大于等于8時,就返回600,大于等于6時,返回400,大于等于4時,返回300,大于等于2時,返回200,大于等于1,返回100,否則返回0。
大家可以發現,這道題如果用IF來解答的話,采用第二種方法,每次都設置數據下限,更易理解。
除此以外,瓶子還想告訴大家,在編輯欄輸入公式的時候,有些時候可以不必手動輸入,比如上面的公式中,我們直接點擊列號C,公式中就可以出現了C:C。上面第二個小伙伴寫的公式中采用了絕對引用,也可以直接拖選數據區域B5-B14后,按F4,就變成了$B$5:$B$14。
可能剛入門的小伙伴對相對引用和絕對引用不是很了解,今天瓶子就來給大家講解一下。
一、相對引用
如下所示,對A、B兩列的數據求和,求和結果顯示在C列。
如下所示,選中C9單元格后,在編輯欄輸入等號,然后直接點擊A9單元格,輸入加號,再直接點擊B9單元格,最后回車。
選中C9單元格,鼠標放在右下角,雙擊,可以看到整列都得到了求和結果。
此時我們去依次點擊C列的單元格,可以看到編輯欄的公式,隨著我們的下移,行號也會隨之改變,變為對應的行號。
大家可以試一下在A18單元格輸入公式,對A列求和,橫向拖動填充公式到C18單元格。大家會看到橫向拖動公式的時候,列號會自動改變,變為對應的列號。這就叫做相對引用,單元格的相對引用格式就是列號+行號,譬如A10、B14等都是相對引用。相對引用的最大特點就是當拖動填充公式的時候,單元格會自動根據公式所在位置發生變化。
二、絕對引用
絕對引用又是什么呢?
如下圖所示,選中D9單元格,輸入=sum()之后,點中括號里,直接拖選A9-C9,再按F4,最后回車。
此時雙擊填充公式,再點擊D列各單元格,查看公式,會發現整列公式都一模一樣。
與相對引用區別在哪?區別在于當前公式中引用的行號和列號前都加了“$”符號。$就是一把“鎖”,鎖定了單元格,所以不管我們橫著拉,還是豎著拉,公式不會有任何改變,這就叫做絕對引用。
三、混合引用
前面公式中,行號和列號都加了“鎖”,但是大家想一想,在豎著填充公式的時候,即便我們不鎖定列號,列號也不會改變。
如下所示,在E9單元格中,輸入=sum()之后,點中括號里,拖選A9-C9單元格區域,然后按兩次F4,此時就只鎖定了行號。
雙擊向下填充后,可以看到結果和前面絕對引用的結果一模一樣。
這種只鎖定行號或列號的操作,就叫做混合引用。混合的意思是,單元格引用既包括了絕對引用又包括了相對引用。譬如,$A1,就是表示單元格的列號是絕對引用,行號是相對引用。混合引用的訣竅就是:向下拉只鎖定行號,向右拉只鎖定列號。這樣在拖動過程中,公式就不會變化。
下面就是驗證大家學習成果的時候啦!
看下面這張表格,需要在B2-E5單元格區域都顯示對應的第幾列第幾排,瓶子在B2單元格設置好了公式,現在大家來想一想,在公式中如何給行號和列號上“鎖”,就可以一次性完成整個區域的填充呢?
把你答案留在下方留言區!
相關學習推薦:實用excel技巧分享:學習怎么做多因素排名統計表?