2014年11月6日 星期四

Excel絕密函數之一GET.CELL

來精英俱樂部很久了,但一直是一個竊取者:),沒發過貼,因為怕發貼質量差,讓大俠們見笑,不過今天實在想把我領悟的一些東西共享出來.希望對大家有所幫助.

看了很多高手寫的例子,實在搞不懂他們的公式裡面經常會出現GET.CELL是什麼意思,可是到幫助裡找,也找不到,問高手也都沒回應,試著在自己的工作表中輸入GET.CELL,又說當前所用的函數在EXCEL中不可用!暈,氣死我了.
我想即然他們懂得這個函數,那我為什麼就不行了,經過我N久(N起碼大於200個小時)的奮鬥,終於讓我學會了這個GEL.CELL的意思及用法,哈哈,我不笑不行,不敢獨享,給大家分享.

好了教程正式開始
GET.CELL ----->GET是得到的意思CELL是單元格的意思
--->那麼它的意思就是你想得到單元格的什麼東西(信息)
函數定義:
GET.CELL(類型號,單元格(或範圍))

其中類型號,即你想要得到的信息的類型號,經我試驗,可以在1-66,我的媽啊,也就是說他可以返回一個單元格裡66種你要的信息?以前你一定沒想到一個單元格裡還有這麼多種信息?
單元格和範圍意思就不講了吧?:)

好進入實戰!
-------------------------------------------------------------------------------
注意點:該函數不能直接在單元格裡引用,即不能直接寫在編輯欄的公式裡,我拷,不能寫在公式裡,這個函數還有什麼用?
HEHE,高手就高在這邊了,不能寫在公式,但是我們可以寫在名稱裡!!(我就不明白微軟為什麼要這麼搞?暈倒一大片人,包括我)

比如現在我們要取得A1單元格裡的數據格式?結果顯示在B1步驟如下:
1.任選一個單元格-->插入--->名稱-->定義
2.在名稱稱輸入AA(任意名稱)
3.在引用位置上寫入=GET.CELL(7, Sheet1!$A$1)
4.點添加
5.在B1單元格裡輸入=AA
看到了,顯示的結果是什麼???如果沒修改過的話應該是G/通用格式吧!
----------------------------------------------------------------------------------

1        參照儲存格的絕對地址
2        參照儲存格的列號
3        參照儲存格的欄號
4        類似 TYPE 函數
5        參照地址的內容
6        文字顯示參照位址的公式
7        參照位址的格式，文字顯示
8        文字顯示參照位址的格式
9        傳回儲存格外框左方樣式，數字顯示
10        傳回儲存格外框右方樣式，數字顯示
11        傳回儲存格外框方上樣式，數字顯示
12        傳回儲存格外框方下樣式，數字顯示
13        傳回內部圖樣，數字顯示
14        如果儲存格被設定 locked傳回 True
15        如果公式處於隱藏狀態傳回 True
16        傳回儲存格寬度
17        以點為單位傳回儲存格高度
18        字型名稱
19        以點為單位元傳回字號
20        如果儲存格所有或第一個字符為加粗傳回 True
21        如果儲存格所有或第一個字符為斜體傳回 True
22        如果儲存格所有或第一個字符為單底線傳回True
23        如果儲存格所有或第一個字符字型中間加了一條水平線傳回 True
24        傳回儲存格第一個字符色彩數字， 1 至 56。如果設定為自動，傳回 0
25        MS Excel不支持大綱格式
26        MS Excel不支持陰影格式
27        數字顯示手動插入的分頁線設定
28        大綱的列層次
29        大綱的欄層次
30        如果範圍為大綱的摘要列則為 True
31        如果範圍為大綱的摘要欄則為 True
32        顯示活頁簿和工作表名稱
33        如果儲存格格式為多行文字則為 True
34        傳回儲存格外框左方色彩，數字顯示。如果設定為自動，傳回 0
35        傳回儲存格外框右方色彩，數字顯示。如果設定為自動，傳回 0
36        傳回儲存格外框上方色彩，數字顯示。如果設定為自動，傳回 0
37        傳回儲存格外框下方色彩，數字顯示。如果設定為自動，傳回 0
38        傳回儲存格前景陰影色彩，數字顯示。如果設定為自動，傳回 0
39        傳回儲存格背影陰影色彩，數字顯示。如果設定為自動，傳回 0
40        文字顯示儲存格樣式
41        傳回參照地址的原始公式
42        以點為單位傳回使用中窗口左方至儲存格左方水平距離
43        以點為單位傳回使用中窗口上方至儲存格上方垂直距離
44        以點為單位傳回使用中窗口左方至儲存格右方水平距離
45        以點為單位傳回使用中窗口上方至儲存格下方垂直距離
46        如果儲存格有插入批注傳回 True
47        如果儲存格有插入聲音提示傳回 True
48        如果儲存格有插入公式傳回 True
49        如果儲存格是數組公式的範圍傳回 True
50        傳回儲存格垂直對齊，數字顯示
51        傳回儲存格垂直方向，數字顯示
52        傳回儲存格前綴字符
53        文字顯示傳回儲存格顯示內容
54        傳回儲存格數據透視表名稱
55        傳回儲存格在數據透視表的位置
56        樞紐分析
57        如果儲存格所有或第一個字符為上標傳回True
58        文字顯示傳回儲存格所有或第一個字符字型樣式
59        傳回儲存格底線樣式，數字顯示
60        如果儲存格所有或第一個字符為下標傳回True
61        樞紐分析
62        顯示活頁簿和工作表名稱
63        傳回儲存格的填滿色彩
64        傳回圖樣前景色彩
65        樞紐分析
66        顯示活頁簿名稱

張貼者： 於 沒有留言:
轉貼
花了兩天的時間把這幾年用過的 Excel VBA 指令彙總起來，分門別類，方便以後使用，順手貼出來給有需要的朋友。看倌多珍惜啊，這可是好幾年的心血說。(使用 Delphi 語法)
//Excel篇
mExcel.DisplayAlerts := False;                                  //Disable 提示訊息
mExcel.Visible := True;                                         //顯示 Excel 畫面
mExcel.ActiveWindow.FreezePanes := True;                        //凍結窗格(上一行一定要先選列或格)
mExcel.ActiveWindow.Zoom := 75;                                 //顯示比率為75%
mExcel.ActiveWindow.Zoom := True;                               //依據目前選擇範圍自動決定視窗大小
//Workbook篇
mWorkBook := mExcel.WorkBooks[1];                               //將指定變數設定第一個活頁簿(數字可以用名稱取代)
mWorkBook.Name :='內容';                                        //變更WorkBook名稱
mExcel.WorkBooks.Add;                                           //新增一個空白活頁簿
mExcel.WorkBooks.Open(完整路徑);                                //開啟Excel檔
mExcel.WorkBooks[mFile].Close;                                  //關閉Excel檔
DeleteFile(mPath+mFile);                                        //刪除Excel檔
mWorkBook.SaveAs(mPath+mFile,-4143);                            //儲存Excel檔
//Sheet篇
mSheet := mExcel.WorkBooks[1].WorkSheets[1];                    //將指定變數設定第一個工作表(數字可以用名稱取代)
mSheet.Name :='內容';                                           //變更Sheet名稱
mSheet.Copy[After := mWorkBook.Sheets[mWorkBook.Sheets.Count]]; //將mSheet複製到mWorkBook最後
mSheet.Move[After := mWorkBook.Sheets[mWorkBook.Sheets.Count]]; //將mSheet搬移到mWorkBook最後
mWorkbook.Sheets.Add[After:=mWorkbook.Sheets[mSheetCount-1]];   //新增一個空白工作表
mWorkBook.Sheets[1].Delete;                                     //刪除指定Sheet
mWorkBook.Sheets[1].Activate;                                   //將指定Sheet設為使用中
//刪除多餘Sheet
if (mWorkBook.Sheets.Count > 1) then
begin
  for i:=2 to mWorkBook.Sheets.Count do
    mWorkBook.Sheets[2].Delete;
end;
//選取篇
mSheet.Cells.EntireColumn                                       //所有欄
mSheet.Cells.EntireRow                                          //所有列
mSheet.Cells                                                    //所有儲存格
mSheet.Columns[1]                                               //第一欄
mSheet.Rows[1]                                                  //第一列
mSheet.Cells[r,c]                                               //第r列第c欄
mSheet.Range[起,迄]                                             //區間選擇(起訖可以是欄、列、格)
//填值篇
mSheet.Cells[1,1].Value:= '內容';                               //欄位填值
mSheet.Cells[1,1].Formula:= '公式';                             //欄位填入公式
mSheet.Cells[1,1].FormulaR1C1:= '公式';                         //欄位填入公式
mSheet.Cells[1,1].HasFormula                                    //儲存格是否有公式
//格式篇
mSheet.Range[起,迄].Merge;                                      //合併儲存格
mSheet.Cells.EntireColumn.AutoFit;                              //最適欄寬
mSheet.Cells.EntireRow.AutoFit;                                 //最適列高
mSheet.Columns[1].ColumnWidth := 100;                           //設定欄寬
mSheet.Rows[1].RowHeight := 100;                                //設定列高
mSheet.Rows[1].HorizontalAlignment := -4108;                    //水平置中(靠左:-4131；靠右:-4152)
mSheet.Rows[1].VerticalAlignment := -4108;                      //垂直置中(靠左:-4131；靠右:-4152)
mSheet.Rows[1].WrapText  := True;                               //自動換列
mSheet.Columns[1].Hidden := True;                               //隱藏
mSheet.Columns[1].NumberFormatLocal := '@';                     //設定欄位格式[文字]
mSheet.Columns[1].NumberFormatLocal := '#,##0_ ';               //設定欄位格式[數值(整數位 三位一撇)]
mSheet.Columns[1].NumberFormatLocal := '#,##0_ ;[紅色]-#,##0 '; //設定欄位格式[數值(整數位 三位一撇 負數紅字)]
mSheet.Columns[1].NumberFormatLocal := '#,##0_);[紅色](#,##0)'; //設定欄位格式[數值(整數位 三位一撇 負數括號紅字)]
mSheet.Columns[1].NumberFormatLocal := '0.00_ ';                //設定欄位格式[數值(小數兩位)]
mSheet.Columns[1].NumberFormatLocal := '0.0_);[紅色](0.0)';     //設定欄位格式[數值(小數一位 負數紅字)]
mSheet.Columns[1].NumberFormatLocal := '0.00%';                 //設定欄位格式[百分比(小數兩位)]
mSheet.Cells[1].Interior.ColorIndex := 38;                      //設定底色為玫瑰色
mSheet.Cells[1].Interior.ColorIndex := 6;                       //設定底色為黃色
mSheet.Cells[1].Interior.ColorIndex := 36;                      //設定底色為淺黃色
mSheet.Cells[1].Interior.ColorIndex := 35;                      //設定底色為淺綠色
mSheet.Cells[1,1].Font.Size := 10;                              //設定字體大小
mSheet.Cells[1,1].Font.Bold := True;                            //設定粗體字
......
//框線
mSheet.Cells[1,1].Borders[n].LineStyle := 1;
mSheet.Cells[1,1].Borders[n].Weight := 2;
//n = 5.左上右下斜線 6.左下右上斜線 7.左邊線 8.上邊線 9.下邊線 10.右邊線 11.垂直線 12.水平線
//Borders可使用參數：
// LineStyle = 1 實線；-4115 短虛線；4 長短虛線；5 長短短虛線；-4118 細虛線；-4119 雙實線
// Weight =  由細到粗：1 --> 2 --> -4138 --> 4
// ColorIndex = 顏色
//設定格式化條件
mSheet.Cells[1,1].FormatConditions.Delete;                      //清除格式化條件
mSheet.Cells[1,1].FormatConditions.Add[Type:='1', Operator:='1', Formula1:='1', Formula2:='2']; //新增格式化條件(最多3個)
//參數說明
//  參數   中文說明                                  說明
//======== ======== =======================================================================
//Type     來源型態 1.儲存格的值 2.公式
//Operator 規則     1.介於 2.不介於 3.等於 4.不等於 5.大於 6.小於 7.大於或等於 8.小於或等於
//Formula1 條件起
//Formula2 條件迄
mSheet.Cells[1,1].FormatConditions(1).Interior.ColorIndex := 3; //設定條件一為底色紅色
//可設定之格式有：Fonts(字型)、Borders(外框)、Interior(圖樣)
//資料篇
mSheet.Cells.EntireColumn.AutoFilter;                           //自動篩選
mExcel.Selection.Subtotal(1,-4157,VarArrayOf([4,5,6,7,8]),True,False,True);  //做小計
//參數說明
//      參數               中文說明         預設值
//================ ======================== ======
//GroupBy          分組小計欄位             1
//Function         使用函數                 -4157 加總
//TotalList        新增小計位置
//Replace          取代目前小計             True
//PageBreaks       每組資料分頁             False
//SummaryBelowData 摘要資料置於小計資料下方 True
//可使用函數：-4157 加總；-4106 平均值；-4112 項目個數；-4113 數字項目數；-4136 最大值；-4139 最小值；
mSheet.Outline.ShowLevels(2);                                    //把小計層級設2顯示
//列印篇
mSheet.PageSetup.PrintTitleRows := '$1:$1';                      //列印標題列
mSheet.PageSetup.CenterHeader := '表頭';                         //中頁首
mSheet.PageSetup.LeftHeader   := '頁次: &P / &N';                //左頁首
mSheet.PageSetup.RightHeader  := '';                             //右頁首
mSheet.PageSetup.CenterFooter := '&　&P / &N';                   //中頁尾
mSheet.PageSetup.LeftFooter   := '頁次: &P / &N';                //左頁尾
mSheet.PageSetup.RightFooter  := '';                             //右頁尾
mSheet.PageSetup.PrintArea := '$B$1:$N$300';                     //設定列印範圍
mSheet.PageSetup.Orientation := 2;                               //1.直印 2.橫印
mSheet.PageSetup.Zoom := 65;                                     //列印時小成65%
mSheet.PageSetup.Zoom := True;                                   //使用頁次縮放功能
mSheet.PageSetup.FitToPagesWide := 1;                            //縮放成一頁寬(需配合Zoom = True)
mSheet.PageSetup.FitToPagesTall := 1;                            //縮放成一頁高(需配合Zoom = True)
mSheet.PageSetup.PaperSize := 8;                                 //設定紙張大小 8:A3、9:A4
mSheet.PageSetup.TopMargin := 1/0.035;                           //頂邊距1cm
mSheet.PageSetup.BottomMargin := 1/0.035;                        //底邊距1cm
mSheet.PageSetup.LeftMargin := 1/0.035;                          //左邊距2cm
mSheet.PageSetup.RightMargin := 1/0.035;                         //右邊距2cm
mSheet.PageSetup.HeaderMargin := 1/0.035;                        //頁首1cm
mSheet.PageSetup.FooterMargin := 1/0.035;                        //頁尾1cm
mSheet.PageSetup.CenterHorizontally := True;                     //頁面水平居中
mSheet.PageSetup.CenterVertically := False;                      //頁面垂直居中
張貼者： 於 沒有留言:

range & cells 

使用 Range 物件選取儲存格

選取單一儲存格
Sub RangeSel1()
    Range("C5").Select '選擇儲存格 C5
End Sub
選取連續的儲存格範圍
Sub RangeSel2()
    Range("B2:D4").Select '選擇連續儲存格範圍 B2:D4
End Sub
Sub RangeSel2()
    Range("B2","D4").Select '選擇連續儲存格範圍 B2:D4
End Sub
選取不連續的儲存格範圍
Sub RangeSel3()
    Range("B2,B4,D2,D4").Select
    Range("B2:D3,B5:D6").Select
End Sub
選取定義名稱的儲存格範圍
Sub RangeSel4()
    Range("銷售總額").Select
End Sub
選取 行/列
Sub RangeSel5()
    Range("1:1").Select '選取第一列
    Range("A:A").Select '選取第 A 行
    Range("1:3").Select '選取第一列到第三列
    Range("A:C").Select '選取第 A 行到第 C 行
    Range("1:3,6:6").Select '選取第第一列到第三列，以及第六列
    Range("A:C,F:F").Select '選取第 A 行到第 C 行，以及第 F 行
End Sub

使用 Cells 屬性選取儲存格

選取單一儲存格
Sub CellsSel1()
    Cells(5,3).Active '選取 C5 儲存格，格式為 Cells("列","行")
    Cells(5,"C").Active
End Sub
Sub CellsSel1()
    Cells(1027).Active '用編號選取儲存格，順序是由左至右由上往下從編號 1 開始
End Sub
選取所有儲存格
Sub CellsSel2()
    Cells.Select
End Sub

取得 / 設定 儲存格的值

Sub GetValueRange1()
    MsgBox Range("A1").Value
End Sub
Sub SetValueRange1()
    Range("A1").Value = 123.456 '通用格式
    Range("A2").Value = "-1,234,500" '千分位
    Range("A3").Value = "2009/01/01" '日期
    Range("A4").Value = "11:22:33" '時間
    Range("A5").Value = "01234" '文字
End Sub

取得 / 設定 儲存格的計算式

設定計算式
Sub FormulaRange1()
    Range("A10").Formula = "=SUM(A1:A9)"
    Range("B10").Formula = "=AVERAGE(B1:B9)"
    Range("C10").Formula = "=MAX(C1:C9)"
    Range("D10").Formula = "=MIN(D1:D9)"
End Sub
絕對參照 / 相對參照
Sub FormulaRange1()
    Range("A3").Formula = "=$A$1+$A$2" '絕對參照
    Range("B3").Formula = "=B1+B2" '相對參照
End Sub
張貼者： 於 沒有留言:
訂閱： 文章 (Atom)