[算表] 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，插入/名稱/定義(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)
推 imrt:雖然一時看不懂，先推..
推 fushi:大推
推 websterskimo:謝謝分享
推 singermath:推 好文章
推 cloudxyz:推 實用好文
※ 編輯: JieJuen         來自: 218.164.48.216       (11/02 14:51)
推 csleafy:感謝你!!!有幫助到...推推!!
※ 編輯: JieJuen         來自: 218.164.50.184       (11/14 02:55)
→ JieJuen:http://i.am.ntu.googlepages.com/getcell_abc.xls
> -------------------------------------------------------------------------- <

作者: JieJuen (David) 看板: Office
標題: Re: [算表] Excel較少被提及的函數與小技巧
時間: Thu Nov 29 23:21:15 2007

之前文章的一部分
出了一題作業...
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

新增：
一欄解決的方法用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

作業完成...


--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 218.164.59.188
→ JieJuen:補充說明，全部是陣列公式，以Ctrl+Shift+Enter結束公式.
> -------------------------------------------------------------------------- <

作者: JieJuen (David) 看板: Office
標題: Re: [算表] Excel較少被提及的函數與小技巧
時間: Fri Nov 30 00:07:30 2007

看不慣公式的朋友可能會覺得公式怎麼那麼長....不知道在做什麼...
尤其是有好多條的時候
※ 引述《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
推 mddc62:陣列公式跟ACCESS 後者好像簡單一點
推 JieJuen:補個檔案 http://kuso.cc/2$Dg
推 JieJuen:要在文字處加註解，則再用T()把0轉為空白~
> -------------------------------------------------------------------------- <

作者: JieJuen (David) 看板: Office
標題: Re: [算表] Excel較少被提及的函數與小技巧
時間: Fri Nov 30 01:41:49 2007

推 mddc62:陣列公式跟ACCESS 後者好像簡單一點
在板友建議了VBA之後我就想，
會不會不久就有建議access了呢^^

因為不是很了解access，有些觀念不正確還請指正~
我認識的一些長者，學了access後都說很好用很好用，
但....他們在沒有用XD

一些商店或是機構、醫院等
每天處理大量資料，
會讓人員用寫好的類似VB的東西輸入，
可以避免出錯，易於自動整理，
似乎也不是用ACCESS。

不知道，是不是ACCESS剛好位於中間，
處理小量的使用者覺得它太大，
大量的使用者覺得它不夠用？

ACCESS的表單，一筆一筆的輸入
輸完感覺好像沒看到結果，不知跑哪去了XD
要一次輸入很多筆的時候，
程序也跟一筆一筆時不同?
輸入資料時，使用滑鼠(或TAB?)的步驟也比較多?
(因為要一筆完了才下一筆?)

這只是概略的感覺，
也許是預設這樣，但可以改 之類的~

EXCEL就像寫在紙上，看到了比較心安XD
比較不會出錯?
因為錯誤大部分最多就累積在一個公式之中
(除非寫VBA...)

EXCEL複製上也很方便，
也可以照自己喜愛的順序輸入。

ACCESS雖然自由度比較大(寫程式嘛~)
但是寫好的程式反而讓使用者的自由度更小了

不過以上講的，可能跟用ACCESS解決這題沒有真正的關係:P
因為求出 重覆條件皆傳回相應的值 應該用不到"使用者輸入介面"?

能否介紹一下ACCESS的解法？(因為更容易呀~)

老實說，
=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())))
這句話對我來說還蠻直觀的XDXD
IF只是偵錯條件不看
INDEX本就要有範圍，不看
SMALL(array,ROW())選個值嘛~不看
EXACT("b",$A$1:$A$4)這就是題目嘛~不看
IF(題目,ROW($A$1:$A$4))這是解答，看...

確實，在PO本系列首文時我還不知道這個解法，
但後來用到這方法的文章也有好幾篇了，
只是現在才剛好看到之前文章中的解法~

歡迎不吝賜教~^^

--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 218.164.59.71
※ 重新編輯: JieJuen (09/29/2008)