今天和大家來分享高級(jí)篇，高級(jí)篇嘛，當(dāng)然是以燒腦為主，介紹一些在論壇和QQ群困擾了很多人的公式錯(cuò)誤原因。
1，EDATE和EOMONTH到底能不能返回?cái)?shù)組？
有些時(shí)候需要引用數(shù)據(jù)源返回相關(guān)的數(shù)組結(jié)果，但很多小盆友引用了區(qū)域后發(fā)現(xiàn)EOMONTH這些函數(shù)結(jié)果是報(bào)錯(cuò)的。
因此得出一個(gè)結(jié)論，這批函數(shù)在引用區(qū)域后是不能返回?cái)?shù)組的。
但是，這只是因?yàn)檫@批函數(shù)性質(zhì)特殊而已，稍微加點(diǎn)料就可以返回?cái)?shù)組了。
數(shù)據(jù)源區(qū)域只要*1，- -等把直接引用轉(zhuǎn)化為內(nèi)存數(shù)組，就可以使這批函數(shù)在引用區(qū)域后成功返回?cái)?shù)組結(jié)果。
這是個(gè)很偏門的性質(zhì)，具有這種性質(zhì)的函數(shù)有EDATE，EOMONTH，WEEKNUM，QUOTIENT/DELTA等等。共同點(diǎn)是，這批函數(shù)在2003版需要加載工具庫(kù)才能使用，這或許就是這批函數(shù)具有這個(gè)奇葩性質(zhì)的原因。
2)高精度浮點(diǎn)
這個(gè)性質(zhì)也是坑過無數(shù)老司機(jī)的：
這個(gè)性質(zhì)在于，這種浮點(diǎn)數(shù)在目前函數(shù)階段，F(xiàn)9，公式求值，格式小數(shù)位數(shù)，等號(hào)比較等等多種方法都無法檢測(cè)出來，但就是會(huì)造成MATCH，VLOOKUP，RANK，MODE，F(xiàn)REQUENCY等函數(shù)的結(jié)果異常。
因?yàn)檫@種浮點(diǎn)超過了Excel的15位精度，所以無法顯示，但部分函數(shù)還是會(huì)識(shí)別這種差異，重點(diǎn)是影響上面幾個(gè)函數(shù)的結(jié)果(這種精度差異可以使用DELTA函數(shù)檢查)。
解決方法是使用ROUND等修正這類數(shù)值的實(shí)際精度，或者換成不識(shí)別這種精度的函數(shù)或判斷式(等號(hào)比較不識(shí)別這種差異)。
3)真?zhèn)蝺?nèi)存數(shù)組和365動(dòng)態(tài)數(shù)組性質(zhì)差異
365前的版本VLOOKUP和INDEX等函數(shù)有一類性質(zhì)。如果VLOOKUP的第1參數(shù)和INDEX的第2/3參數(shù)，直接使用數(shù)組，并不能使整個(gè)VLOOKUP或INDEX返回內(nèi)存數(shù)組。
雖然可以以區(qū)域數(shù)組方式錄入多個(gè)單元格返回對(duì)應(yīng)的多個(gè)結(jié)果，但并不能作為真內(nèi)存數(shù)組用于后續(xù)計(jì)算，因此這個(gè)性質(zhì)被稱為偽內(nèi)存數(shù)組。
如果要把INDEX的結(jié)果變成真內(nèi)存數(shù)組，必須加N/T+IF結(jié)構(gòu)來進(jìn)行轉(zhuǎn)化。
而自從出現(xiàn)了365版本，真?zhèn)蝺?nèi)存數(shù)組性質(zhì)發(fā)生了變化：
即365下部分公式使用三鍵和不使用三鍵，結(jié)果是會(huì)有區(qū)別的，
即通常在不使用三鍵環(huán)境下，365版本會(huì)把早期版本的偽內(nèi)存結(jié)構(gòu)直接形成真內(nèi)存數(shù)組，但如果使用三鍵，還是早期版本的偽內(nèi)存數(shù)組性質(zhì)，這個(gè)性質(zhì)主要在早期版本的偽內(nèi)存數(shù)組和多維引用兩種公式結(jié)構(gòu)下存在。
4) 1和{1}的區(qū)別
這類性質(zhì)，通常出現(xiàn)在涉及INDIRECT或OFFSET的動(dòng)態(tài)引用問題內(nèi)，INDIRECT或OFFSET函數(shù)配合ROW或COLUMN函數(shù)時(shí)容易出現(xiàn)。
很多人會(huì)被這個(gè)錯(cuò)誤困擾不知道如何解決，其實(shí)這個(gè)公式之所以錯(cuò)誤，根源在于ROW函數(shù)，因?yàn)镽OW(A1)的結(jié)果為{1}，注意這是1個(gè)單元素的數(shù)組。
而INDIRECT和OFFSET都是容易構(gòu)造多維引用的，所以目前的觀點(diǎn)上面公式錯(cuò)誤和這兩個(gè)函數(shù)的多維引用下的降維理論有關(guān)。
解決方法也很簡(jiǎn)單，1是在ROW外面套個(gè)聚合函數(shù)，SUM/MAX/MIN等等均可，目的是把{1}變成常量1，就不會(huì)出現(xiàn)類似錯(cuò)誤了。
5)時(shí)間精度
Excel里的時(shí)間精度到千分之一毫秒，即YYYY/MM/DDHH:MM:SS.000
到了時(shí)間階段，Excel里的日期函數(shù)會(huì)出現(xiàn)兩大陣營(yíng)。部分日期函數(shù)對(duì)日期的識(shí)別僅到日期部分，不考慮時(shí)間范圍。
但還有部分日期函數(shù)，對(duì)日期的識(shí)別精度達(dá)到了這個(gè)毫秒級(jí)，時(shí)間不大于23:59:59.499的日期識(shí)別為當(dāng)天，但23:59:59.500-23:59:59.999的范圍會(huì)被識(shí)別為第2天。
測(cè)試表明DAY、WEEKDAY、YEAR、MONTH、HOUR、MINUTE、SECOND、DAYS360與TEXT的日期時(shí)間格式處理，對(duì)日期時(shí)間的識(shí)別不完全按照日期部分，
這幾個(gè)函數(shù)對(duì)日期的識(shí)別范圍至少到毫秒的精度，23:59:59.499(含)前識(shí)別為當(dāng)日日期，23:59:59.500開始識(shí)別為第2天。
而DAYS、EDATE、EOMONTH、WEEKNUM、ISOWEEKNUM、WORKDAY(.INTL)、NETWORKDAYS(.INTL)、YEARFRAC這10個(gè)函數(shù)對(duì)日期時(shí)間的識(shí)別僅識(shí)別日期部分，嚴(yán)格取整，與時(shí)間范疇無關(guān)。
因此如果你們的軟件導(dǎo)出數(shù)據(jù)時(shí)間精度達(dá)到毫秒級(jí)，請(qǐng)注意這個(gè)精度差異對(duì)結(jié)果的影響。
6)參數(shù)上限
有些函數(shù)具有內(nèi)置上限，這個(gè)上限會(huì)限制這些函數(shù)的使用，其中大部分函數(shù)在超過上限后是會(huì)報(bào)錯(cuò)的，例如MOD函數(shù)：
但是，少數(shù)函數(shù)在超過上限后不會(huì)報(bào)錯(cuò)而是返回特殊結(jié)果，這類性質(zhì)比較隱蔽，會(huì)影響函數(shù)運(yùn)算結(jié)果，例如DATE函數(shù)：
這個(gè)函數(shù)的優(yōu)勢(shì)是例如第3參數(shù)大于本月天數(shù)時(shí)，會(huì)自動(dòng)把函數(shù)結(jié)果轉(zhuǎn)化為對(duì)應(yīng)的有效日期。但這個(gè)函數(shù)有內(nèi)置參數(shù)，第3參數(shù)有效的最大值只有32767，超過32767的數(shù)值都會(huì)縮減到32767進(jìn)行日期換算，故需要注意這個(gè)性質(zhì)避免轉(zhuǎn)化的日期結(jié)果異常。
7)數(shù)組和引用
曾經(jīng)寫過一篇文章介紹AGGREGATE函數(shù)，這個(gè)函數(shù)堪稱函數(shù)小霸王，因?yàn)檫@個(gè)函數(shù)是19個(gè)函數(shù)的集合體，然而有些新手在沒看完攻略的前提下想用這個(gè)函數(shù)對(duì)某個(gè)內(nèi)存數(shù)組忽略錯(cuò)誤值求和。
9是對(duì)應(yīng)求和SUM，6是忽略錯(cuò)誤值，然而發(fā)現(xiàn)結(jié)果還是錯(cuò)的，
AGGREGATE是個(gè)神奇的函數(shù)，有兩種形式：向量型和數(shù)組型。
Excel里具有2種形式的函數(shù)不多，除了AGGREGATE外常見的還有INDEX和LOOKUP，這其中AGGREGATE由于某些性質(zhì)是最特殊的。
注意AGGREGATE的參數(shù)，數(shù)組型里的第4參數(shù)為K值，而AGGREGATE的第1參數(shù)對(duì)應(yīng)的19個(gè)函數(shù)里，只有14-19的6個(gè)函數(shù)是具有K值的。
所以關(guān)于AGGREGATE的最大局限就是，只有在替代LARGE，SMALL，PERCENTILE，QUARTILE這幾個(gè)函數(shù)時(shí)，第3參數(shù)才支持?jǐn)?shù)組，否則只支持引用，這是這個(gè)函數(shù)的最大局限。
因此上面用AGGREGATE針對(duì)數(shù)據(jù)源存在錯(cuò)誤值時(shí)的求和，3參數(shù)只能使用引用，不支持?jǐn)?shù)組。
8)你不知道的性質(zhì)
Excel有大量簡(jiǎn)寫模式和特殊識(shí)別的字符串規(guī)則：
0-12的整數(shù)+空格+A / P 為整點(diǎn)時(shí)間的縮寫表達(dá)式，可以被Excel直接識(shí)別為時(shí)間的(12 A 為12:00 AM實(shí)際為0:00故結(jié)果為0)。
這些特殊規(guī)則很多，都可能影響你的計(jì)算結(jié)果：
(小編測(cè)試過很多最特殊的，大概是下面這個(gè)從2013版本開始出現(xiàn)的以空格+冒號(hào)開頭的特殊表達(dá)式)
很多時(shí)候，你自認(rèn)為公式是正確的，但結(jié)果異常，很多時(shí)候就是因?yàn)槟悴]有了解Excel的這些特殊規(guī)則和機(jī)制造成的。
因此，學(xué)習(xí)函數(shù)不是一朝一夕的事情，只有多練習(xí)，多看別人的文章和公式，才能隨心所欲，否則就可能出現(xiàn)各式各樣的問題。
好了，今天的內(nèi)容就是這些吧，我是流浪鐵匠，點(diǎn)個(gè)贊，咱們一起走天涯。
