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

看板Office作者JieJuen (David)時間18年前 (2007/11/02 00:33)推噓7(7推 0噓 1→)

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

最近發現了這個版很高興，於是開始爬文，也回了一些久一點了的文章^^;; 發現有些問題常常被提，但一些解決方法較少被提到，稍微整理一下吧，希望對各位有一點點幫助: 1.字母轉換字母轉數字: 不分大小寫:=COLUMN(INDIRECT(A1&"1")) A1為字母所在大寫:=CODE(A1)-64 小寫:=CODE(A1)-96 可用CHAR找出A的CODE 數字轉英文: =CHAR(A1+64) 1轉為A =CHAR(A1+96) 1轉為a 2.多個條件條件本身是可以傳回true或false的，因此多個條件很好寫，例如 --------------------------------------- F G H 1 總分排名數學排名先總分後數學排名 2 2 3 3 1 1 4 2 1 H2為{=SUM((F$2:F$4=F2)*(G$2:G$4<G2))+F2} --------------------------------------- 第一個()找排名相同的人，第二個()找數學排名較前的人找到幾個，就在原排名加上多少。因此條件式本身就有判斷的值了，如果IF的式子最後有...,0),0),0) 這類的或許都可以試試。 ()*()=且，()+()=或。數字關係: 不等於<> 大於等於>= 小於等於<= AND OR NOT (補註：但在陣列公式中AND與*，OR與+有時會不同 AND OR需整個範圍符合，*+各元素獨自符合即可因此上例不可用AND. ) 如果條件是"或"，也可用MATCH 如 A1="X" 或 "Y" 或 "Z": =MATCH(A1,{"X","Y","Z"},0) 幫分數分類如90分以上為甲等，80~89是乙等，70~79是丙等 =CHOOSE(MATCH(A1,{70,80,90}),"丙等","乙等","甲等") 3. 轉置需要用公式轉置可用=TRANSPOSE(array) 配合其他參照函數可以進行特殊而規律的轉置如: ------------------------------------------------------------------------ A B C D E F 1 A 選選選選 2 B 選選選選 3 C 選選選選 4 D 5 6 A 7 B 8 C 9 D 10 11 A 12 B 13 C 14 D 要轉換於C1~F3之中請選C1~F3儲存格按F2或按編輯列編輯，輸入 =TRANSPOSE(INDIRECT(ADDRESS(5*ROW()-4,1)):INDIRECT(ADDRESS(5*ROW()-1,1))) 再Ctrl+Shift+Enter ------------------------------------------------------------------------- 有些轉置只需用到offset如欲將A4.A6.A8..轉為A1.B1.C1... A1=OFFSET($A$2,2*COLUMN(),0) 往右拉上一題也可在C1輸入 =OFFSET($A$1,5*ROW()+COLUMN()-8,0) 然後再拖曳到F1，F3等等 4. 重覆的條件，皆傳回相應的值這不太算是小技巧了，因為有點麻煩 --------------------------------------------- A B C D 1 b W 1 W 2 a X 3 Y 3 b Y 4 Z 4 B Z 0 FALSE A欄有"b"(不分大小寫)時傳回B欄的內容 C1=MATCH("b",A1:$A$4,0) C2=(MATCH("b",OFFSET($A$1,C1,0):$A$4,0)+C1)*(C1<4)*(C1<>0) 選C2往下拉. 後兩項是避免超過範圍. D1=IF(C1>0,INDEX($B$1:$B$7,C1)) 往下拉 --------------------------------------------- 大意就是用MATCH找出位置，用OFFSET調整下次搜尋的範圍。要分大小寫的話，好像麻煩了些，就當作業吧XD 5. 文字與公式的轉換別格公式，本格算值如 300*400在A1欄位內選A2，插入/名稱/定義(Ctrl+F3)，"現有名稱"自打，例如xxx 參照到 =evaluate(A1) 在A2輸入=xxx 即可得120000 別格公式，本格文字方法類似，定義名稱 =GET.CELL(6,A1) 可得公式的文字內容 6. 日期混合文字 EXCEL說明裡有，使用TEXT函數. A1打時間， A2=TEXT(A1,"d-mmm")&"是個好日子" 從此不用辛苦的取年、月、日，或是製作英文月份對照表了! ^^ 以上，還請各位多多指教^^ -- ※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 218.164.48.216 ※ 編輯: JieJuen 來自: 218.164.48.216 (11/02 00:43)