Re: [算表] Excel較少被提及的函數與小技巧

看板Office作者JieJuen (David)時間18年前 (2007/11/30 00:07)推噓3(3推 0噓 0→)

留言3則, 2人參與討論串3/4 (看更多)

看不慣公式的朋友可能會覺得公式怎麼那麼長....不知道在做什麼... 尤其是有好多條的時候 ※ 引述《JieJuen (David)》之銘言： : 一欄解決的方法用offset或index : C1 : =IF(ROW()>COUNTIF($A$1:$A$4,"b"),"", : OFFSET($B$1,SMALL(IF($A$1:$A$4="b",ROW($A$1:$A$4)),ROW())-1,)) : 或 : =IF(ROW()>COUNTIF($A$1:$A$4,"b"),"", : INDEX($B$1:$B$4,SMALL(IF($A$1:$A$4="b",ROW($A$1:$A$4)),ROW()))) : 看起來是變長了，但三個式子合為一個式子，其實寫起來是比較簡單，也比較短 : 當然計算可能慢了點 : 但靈活度也大了，要分大小寫就可以改成 : =IF(ROW()>COUNT(1/EXACT("b",$A$1:$A$4)),"", : INDEX($B$1:$B$4,SMALL(IF(EXACT("b",$A$1:$A$4),ROW($A$1:$A$4)),ROW()))) : 要找"含有小寫b" : =IF(ROW()>COUNT(FIND("b",$A$1:$A$4)),"",INDEX($B$1:$B$4, : SMALL(IF(ISNUMBER(FIND("b",$A$1:$A$4)),ROW($A$1:$A$4)),ROW()))) : 不分大小寫，FIND改成SEARCH 例如下邊寫"作業完成" 但其實不知道上面哪一條式子才是答案....(頭都昏了) : 作業完成... =IF(ROW()>COUNT(1/EXACT("b",$A$1:$A$4)),"", INDEX($B$1:$B$4,SMALL(IF(EXACT("b",$A$1:$A$4),ROW($A$1:$A$4)),ROW()))) 作業是這一條當然公式不適合在BBS上看的放到EXCEL裡比較好，不過有時候放到EXCEL也不明瞭，這時就需要解釋一下了如果演算法一樣的話公式應該不會比VBA長，至少公式不用定義但是全都擠在一起，不怎麼好看那...就分開看吧 =IF( ROW() > COUNT( 1/EXACT( "b",$A$1:$A$4 ) ), "", INDEX( $B$1:$B$4, SMALL( IF( EXACT( "b",$A$1:$A$4 ), ROW( $A$1:$A$4 ) ), ROW() ) ) ) 這樣有好一點嗎XD 如果您喜歡的話可以這樣寫貼到EXCEL裡可以動喔! 還差一個註解是吧，沒問題~ =IF( ROW() > COUNT( 1/EXACT( "b",$A$1:$A$4 ) +N("如果是TRUE，1/TRUE=1，如果是FALSE，1/FALSE=#DIV/0!，因為COUNT忽略錯誤值，只算數字的個數，所以可以知道有幾個符合條件") ) +N("列號大於""b""的個數,就反回空白"), "", INDEX( $B$1:$B$4, SMALL( IF( EXACT( "b",$A$1:$A$4 ), ROW( $A$1:$A$4 ) +N("符合完全等於b的，傳回列號，其他傳回FALSE") ), ROW() +N("在第幾列，就傳回第幾小的") ) ) ) 上面可以執行的一模一樣~~ N()是會將上面寫的文字轉為0，所以可以寫在加上零不會變動的地方。當然平常寫應該不用這麼極端，但是換行與加註解這兩個小技巧還是蠻好用的，如果公式真的很長的話 (上面那個在我的EXCEL中只有一行) 換行的方法是按Alt+Enter，也許有些人知道這樣按，但沒想過在寫公式時也可以按XD 最後提醒，如果有人真的要試，這是陣列公式，輸入完要按Ctrl+Shift+Enter. -- ※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 218.164.59.188