工作中總結(jié)的一些Excel心得

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

一些Excel函數(shù)與其簡(jiǎn)化運(yùn)算符的對(duì)應(yīng)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

		自動(dòng)登錄	找回密碼
密碼			注冊(cè)

精品日本亚洲一区二区三区,伊人久久狼人色精品无码 ,日鲁夜鲁天天鲁视频,国产精品久久亚洲,秋霞理论理论福利院久久,国产日韩欧美视频一区二区三区,色九九,国产精品美女久久久久久免费 ,九九干,韩国精品一区二区三区

[分享] 工作中總結(jié)的一些Excel心得