2010年4月27日 星期二

Excel-儲存格中顯示工作表名稱

如果想要在Excel的工作表中顯示某個工作表的名稱,該如何做呢?

例如在Sheet1工作表的儲存格A1中輸入公式:

=MID(CELL("filename",Sheet2!A1),FIND("]",CELL("filename",Sheet2!A1))+1,256)

結果會得到Sheet2,如果工作表的名稱有所更改,其結果也會跟著變動。

其中CELL()函數會傳回有關儲存格之格式、位置或內容的資訊

語法:CELL(info_type, [reference])

info_type如下:

info_type 傳回
"address" 以文字形式表示 reference 中第一個儲存格的位址。
"col" Reference 中儲存格的欄名。
"color" 如果儲存格設定為會因負數而改變色彩的格式,則傳回 1;否則傳回 0 (零)。
"contents" 參照左上角儲存格的數值;不是公式。
"filename" 以文字形式傳回 reference 所屬文件的檔案名稱 (包含完整的路徑名稱)。如果該文件尚未存檔,則傳回空字串 ("")。
"format" 對應於數值儲存格格式的文字表示形式。各種格式的文字表示列示於以下的表格中。如果儲存格為會因負數而改變色彩,則傳回的文字值的後面會帶有負號 (-)。如果儲存格被設定為將正數或任何數值放在一組括弧中的格式時,則在傳回的文字值的後面加一組 "()"。
"parentheses" 如果儲存格格式設定為將正數或所有數值放在一組括弧中,傳回 1;否則傳回 0。
"prefix" 文字儲存格的「標籤首碼」的文字表示形式。如果該儲存格含有靠左對齊的文字時,傳回單引號 (');如果該儲存格中含有靠右對齊的文字時,傳回雙引號 (");如果該儲存格中含有置中對齊的文字時,傳回脫字符號 (^);如果該儲存格中含有填滿對齊的文字時,傳回反斜線 (\);如果該儲存格含有其他的資料,則傳回空字串 ("")。
"protect" 如果儲存格並未鎖定保護,傳回 0;如果儲存格已鎖定保護,則傳回 1。
"row" 參照位址中儲存格的列號。
"type" 儲存格中資料類型的一個對應文字值。如果該儲存格是空白的,傳回「b」(代表 blank),如果該儲存格含有文字常數,則傳回標籤「l」(代表 label);如果該儲存格中含有其他類別的資料,則傳回「v」(代表 value)。
"width" 儲存格欄寬四捨五入成整數值。每個欄寬單位都等於預設字型大小的一個字元寬度。

 

其中公式:

CELL("filename",Sheet2!A1)會傳回檔案的完整路徑,你必須先該檔案存檔。

得到結果例如:C:\Users\Administrator\Documents\[Book.xlsx]Sheet2

FIND("]",CELL("filename",Sheet2!A1))+1

找出"]”在第幾個位置,+1的目的是指定下一個位置

MID(CELL("filename",Sheet2!A1),FIND("]",CELL("filename",Sheet2!A1))+1,256)

指在"]”的下一個位置取256個字,由於一個儲存格最多只能放256個字元,所以保證可以取得工作表的完整名稱。

7 則留言:

  1. 前輩您好,小弟利用網路搜尋看到您的文章對我非常有用,針對工作表名稱一問:
    就是我依您的教學,使用函數讓儲存格能帶出工作表名稱,但是我發現一個問題,就是,當我有很多工作表時,只要有帶入函數的儲存格只會帶出最後一個設定的工作表名稱,無法依個別名稱顯示,請問應該如何改善?

    回覆刪除
  2. =COUNTIF('0'!F5,B2)+COUNTIF('1'!F5,B2)+COUNTIF('2'!F5,B2)+.......
    請問像這樣各工作表的加總有沒有比較簡便的公式,因為目前有一百頁左右的工作表需要加總,麻煩您了,我嘗試過用"*"但是沒有用,算不出答案。

    回覆刪除
  3. =COUNTIF('0'!F5,B2)+COUNTIF('1'!F5,B2)+COUNTIF('2'!F5,B2)+COUNTIF('3'!F5,B2)+COUNTIF('4'!F5,B2)+........
    請問像這樣多個工作表的相加,有沒有比較簡單的方法,我嘗試用過"*"的方式,但無法成功。麻煩您了,謝謝您~

    回覆刪除
  4. 請參考以下文章:
    http://isvincent.blogspot.com/2011/12/excel-countif.html

    回覆刪除
  5. 您好:我想問若要每次加總前一個工作表相同的儲存格,是否公式可使用?
    例如在104年的工作表輸入 ='103年'!C4+'103年'!C5 ,等105年的工作表時又要103年改成104年,請問有公式可以自動抓前一個工作表的函數嗎?

    回覆刪除
    回覆
    1. 請參考:http://isvincent.blogspot.tw/2012/11/excel_23.html

      刪除

好康東東