工作中總結的一些Excel心得

放開那個禽獸 · 發(fā)表于 2013-5-12 20:02

一些Excel函數(shù)與其簡化運算符的對應

放開那個禽獸 · 發(fā)表于 2013-5-12 20:03

在Excel中使用“表”的結構化引用時，向左或向右拖拽公式時，會導致字段也跟著移動，但Excel并沒有提供結構化引用的絕對引用。當我們不想讓字段跟著移動時，可以用ctrl+enter來達到復制公式而不移動字段的效果。
向右拖拽公式時，當引用的字段已經達到最后一個字段時，再往右拖，會從第一個字段開始循環(huán)，所以不會出現(xiàn)#REF!錯誤。不過這好像很少會用到。

放開那個禽獸 · 發(fā)表于 2013-5-12 20:03

”表中不能使用多單元格數(shù)組公式“這個提示實際上做了一個提醒：在表中的單個單元格中結合[字段]使用數(shù)組公式也是很危險而且得不到想要結果的。比如=SUM(VLOOKUP([險類],參數(shù),{2,3,4,5},0))并不能對每行數(shù)據(jù)都得到每行的“險類”對應的參數(shù)之和，而是各行數(shù)據(jù)都一樣，全都是第一行數(shù)據(jù)對應的參數(shù)之和。
在表中使用了數(shù)組公式時，切記要用表名[[#此行],[字段]] 的形式，不能直接用[字段]的形式，否則Excel會對每一行都以整個字段的數(shù)據(jù)進行數(shù)組公式的計算，不但計算會變慢很多（因為每行都對整個字段的所有數(shù)據(jù)計算），而且會得到錯誤的結果（因為每行都只會顯示第一行的計算結果）。
發(fā)現(xiàn)過程：在表中使用數(shù)組公式后，計算慢了很多，而采用過程一樣的替代算法時速度很快。找原因時發(fā)現(xiàn)數(shù)組公式計算結果有誤。進一步發(fā)現(xiàn)表中的數(shù)組公式對整個字段的數(shù)據(jù)進行了計算，并只在當前行顯示了數(shù)組計算結果中的第一個元素。于是同時解釋了為何計算速度慢而且計算結果錯誤。
這也同時解釋了為什么直接點擊表中同行字段時，Excel直接返回了表名[[#此行],[字段]] 形式，而沒有省略為[字段]形式，因為這樣做有計算錯誤的風險。同時，把表轉化為區(qū)域的時候，以[字段]形式的引用會轉化為整個字段的所有數(shù)據(jù)，而以表名[[#此行],[字段]] 則會轉化為當前行的數(shù)據(jù)。
Excel的幫助文件對[字段]形式的引用叫做“非限定的結構化引用”，對表名[[#此行],[字段]] 的形式叫做“完全限定的結構化引用”。幫助文件中只提到“如果在表格內使用結構化引用，可以使用非限定的結構化引用”，卻沒提到使用數(shù)組公式+非限定的結構化引用時會得到錯誤結果。

放開那個禽獸 · 發(fā)表于 2013-5-16 17:10

對于非限定的結構化引用，要時刻注意該引用實際上對整個字段的數(shù)據(jù)進行了計算，只不過有時是按數(shù)組公式計算并將計算結果分配到每個單元格，有時對每行單獨計算。這主要取決于表公式中是否存在SUM、MAX、MIN、COUNT等聚合函數(shù)。

放開那個禽獸 · 發(fā)表于 2013-5-19 18:45

CTRL+\在賠款三角流量表中是最方便、而且不用VBA的用于選擇上三角和下三角的方法，不過要注意初始數(shù)據(jù)范圍的選擇方式（選擇上三角需要從右下往左上或從右上往左下選擇，選擇下三角需要從左上往右下或從左下往右上選擇）。不過這里要求三角表中的賠款數(shù)據(jù)由公式生成，而非常數(shù)。
CTRL+\的功能是選擇行中不與該行內活動單元格的值相匹配的單元格。其功能等于按F5后定位“行內容差異單元格”。因此在選中數(shù)據(jù)區(qū)域后，按CTRL+\，會對每一行選擇與活動單元格所在列的每個單元格的公式不一樣的范圍。對于三角表來說，若活動單元格在最右列（如圖），則定位結果為上三角；在最左則下三角。
之前用的方法是VBA編了個選上三角和下三角的小工具。與這個方法相比，各有利弊。這個方法對于三角表數(shù)據(jù)為常數(shù)的情況不適用。有時為提高計算速度，需要將三角表數(shù)據(jù)由公式轉為常數(shù)，或者三角表數(shù)據(jù)由外界得到，此時無法使用這個方法。

放開那個禽獸 · 發(fā)表于 2013-5-19 18:46

事故季和事故月下拉時用的最簡公式是什么？事故季可以用A2=A1+1+6*(--RIGHT(A1)=4)下拉，其中A1為20111的形式，最后對單元格使用#Q0或#Q#格式即可顯示為2011Q1的形式。事故月則可以用A2=EDATE(A1,1)下拉，其中A1為2011/1/1的形式，最后對單元格使用emm格式即可顯示為201101的形式。
在提取賠款數(shù)據(jù)的事故季和事故月時，要注意保證格式與Excel三角表中的格式一致，才能在三角表中使用SUMIFS等函數(shù)得到正確的三角表數(shù)據(jù)。
事故季的格式除了#Q0和#Q#外，0Q0也可以。當然，事故月的格式除了emm外，也可以用yyyymm，雖然長一點。yyyymm能保證絕大多數(shù)人看懂，而emm不是所有人都知道。

放開那個禽獸 · 發(fā)表于 2013-5-22 17:07

報表里經常以萬為單位，所以經常在引用數(shù)據(jù)源的公式后面/10000。其實直接加兩個%%就可以，每個%就表示已經除以100了。用%的兩個好處：1、公式短，僅占兩個字符，而/10000要六個字符；2、好輸入，左手不必移動就可以搞定%，而/10000中的/和0都需要左手的大范圍移動。

放開那個禽獸 · 發(fā)表于 2013-5-31 21:52

Excel存在第16位有效數(shù)字。
其他一些事項備注：1、手動輸入的第16位有效數(shù)字會被截掉；2、=判斷不到第16位；3、+-*/均可保留計算結果到第16位；4、只是不能顯示到第16位而已，顯示最多15位，但存儲是以16位存的；5、countif、=、exact均無法辨別第16位有效數(shù)字，工程函數(shù)delta可以辨別，其他函數(shù)待測試。

放開那個禽獸 · 發(fā)表于 2013-6-11 08:43

賠款流量三角表中經常需要求對角線上的所有數(shù)據(jù)之和（截至評估時點的已報告或累計已決賠款），除了一個一個加或者用offset結合row用數(shù)組公式之外，還有什么好辦法嗎？答案是有的，而且公式很短，函數(shù)只用了一個SUM。但思路可能對于不熟悉數(shù)組公式的人來說不太好理解。如圖所示。

公式中的G14:R25=""形成了一個上三角為FALSE，下三角為TRUE的矩陣，其中對角線為TRUE。當這個矩陣與三角表區(qū)域G13:R24一一相乘時，只有對角線上的數(shù)據(jù)會被TRUE作用，其他數(shù)據(jù)要么被FALSE作用而變?yōu)?，要么本身就是空值，與TRUE作用后也是0。然后SUM一下，就得到了對角線上的所有數(shù)據(jù)之和。

該思路還可以用來求多個列的最后一個非空單元格的值之和。

放開那個禽獸 · 發(fā)表于 2013-7-2 21:47

我們知道在自定義名稱中使用!A1這樣的公式可以讓自定義名稱對每個工作表適用，這可以簡化很多工作。但與Sheet1!A1這樣的公式相比，它也有個缺點：在工作表（即使是全部工作表形成的組）中的A1之前插入行或列時，!A1并不會跟著變成!B1或!A2。而Sheet1!A1這樣的名稱是會變成Sheet1!B1或Sheet1!A2的。

換句話說，!A1有點INDIRECT("A1")的趕腳。不管你在A1前面怎么插入行列，我總是引用A1單元格，不會跟著變。

		自動登錄	找回密碼
密碼			注冊

[分享] 工作中總結的一些Excel心得