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

[JieJuen (David)]

最近發現了這個版很高興，
於是開始爬文，也回了一些久一點了的文章^^;;
發現有些問題常常被提，但一些解決方法較少被提到，
稍微整理一下吧，希望對各位有一點點幫助:

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，插入/名稱/定義，"現有名稱"自打，例如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)
推 imrt:雖然一時看不懂，先推..
推 fushi:大推
推 websterskimo:謝謝分享
推 singermath:推 好文章
推 cloudxyz:推 實用好文
※ 編輯: JieJuen         來自: 218.164.48.216       (11/02 14:51)
推 csleafy:感謝你!!!有幫助到...推推!!
※ 編輯: JieJuen         來自: 218.164.50.184       (11/14 02:55)