105.04.14 第6次上課
(範例-切割名字.xlsm、06_萬年曆、範例_切割名字(INDEX_ROW_COLUMN函數))
上課內容：
01_切割字串與萬年曆與INDIRECT函數
切割名字02(範例-切割名字.xlsm)
=INDIRECT("A"&COLUMN()-2+(ROW()-1)*5)
資料來源 A1~A27
排成 5欄
參照關係：存放儲存格的欄位值C欄是3， 資料室從第1列開始，3-2=1 =第1列，再 +(1-1)* 5的倍數(儲存格的列數值是1)
第1 列 C1~G1 →參照位置 A1 ~A5 →A & 5的0倍 => 3-2+((1-1)*5) , 4-2+((1-1)*5), 5-2+((1-1)*5), 6-2+((1-1)*5), 7-2+((1-1)*5) => 1, 2, 3, 4, 5,
第2列 C2~G2 →參照位置 A6~A10 →A & 5的1倍 => 3-2+((2-1)*5) , 4-2+((2-1)*5), 5-2+((2-1)*5), 6-2+((2-1)*5), 7-2+((2-1)*5) = >6, 7, 8, 9, 10,
語法：INDIRECT(ref_text, [a1])
- Ref_text 必要。 單一儲存格的參照，其中包含 A1 樣式參照、R1C1 樣式參照、定義為參照的名稱，或定義為文字字串的儲存格參照。 如果 ref_text 不是有效的儲存格參照，INDIRECT 會傳回 #REF! 錯誤值。
- A1 選用。 指定 ref_text 儲存格中所包含參照類型的邏輯值。
- 如果 a1 為 TRUE 或省略，則 ref_text 會被解釋成 A1 樣式參照。
- 如果 a1 為 FALSE，則 ref_text 會被解譯成 R1C1 樣式參照。
02_如何用INDIRECT函數切割與轉為VBA的Range物件
利用 for 迴圈
外迴圈 i ：分割後放資料的列數，1 to 6 ，27筆資料分成每行5個，會有6行
內迴圈 j ：分割後放資料的欄數，3 to 7 ，資料從C欄(3)開始放入，每行5筆資料，共有5欄
Public Sub 切割()
For i = 1 To 6
For j = 3 To 7
Application.ScreenUpdating = False
Cells(i, j) = Range("A" & j - 2 + (i - 1) * 5)
Application.ScreenUpdating = True
Next
Next
End Sub
進階：複製原始資料的格式
Public Sub 切割_複製原格式()
For i = 1 To 6
For j = 3 To 7
Application.ScreenUpdating = False
Cells(i, j) = Range("A" & j - 2 + (i - 1) * 5)
'x = Range("A" & j - 2 + (i - 1) * 5)
'MsgBox (x)
'Cells(i, j).Interior.Color = RGB(255, 255, 0)
Range("A" & j - 2 + (i - 1) * 5).Select
Selection.Copy
Cells(i, j).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True
Next
Next
End Sub
03_如何用INDEX與INDIRECT函數將多欄轉為單欄
INDEX
資料範圍：C1~G6
參照關係
列：INT((ROW()-1)/5)+1，
1~5列是 資料範圍的[ (列數-1/5)+1)]列→(0倍+1)列
6~10列是 資料範圍的[ (列數-1/5)+1)]列→(1倍+1)列
欄：MOD(ROW()-1,5)+1
1~5列是 資料範圍的 [(列數-1 / 5的餘數)+1]欄→(0+1)欄、(1+1)欄、(2+1)欄、(3+1)欄、(4+1)欄
6~10列是 資料範圍的[(列數-1 / 5的餘數)+1]欄→(0+1)欄、(1+1)欄、(2+1)欄、(3+1)欄、(4+1)欄
Q1→列1，欄1
Q2→列1，欄2
…
Q5→列1，欄5
Q6→列2，欄1
INDIRECT
資料範圍：C1~G6→ C：CHAR(67)、G：CHAR(71)
參照關係：CHAR(67+(MOD(ROW()-1,5))) & INT((ROW()-1)/5)+1
J1：參照位置=C1 →CHAR(67+0) & 0+1
J2：參照位置=D1→CHAR(67+1) & 0+1
…
J5：參照位置=G1→CHAR(67+4) & 0+1
J6：參照位置=C2→CHAR(67+0) & 1+1
04_公式的欄、列如何鎖定
關鍵：公式鎖住資料來源的 標題列 跟 標題欄的位置
應用：九九乘法表
資料來源 A1 => 標題欄A 標題列1
公式= B$1 & “*” & $A2 & “=”& B$1*$A2
函數=COLUMN()-1 & “*” & ROW()-1 & “=” &( COLUMN()-1)*(ROW()-1)
05_九九乘法表轉為VBA說明
Public Sub 九九乘法表()
For i = 2 To 10
For j = 2 To 10
Cells(i, j) = j - 1 & "X" & i - 1 & "=" & (j - 1) * (i - 1)
'=B$1&"X"&$A2&"="&B$1*$A2
Next
Next
End Sub
Public Sub 九九乘法表清除()
For i = 2 To 10
For j = 2 To 10
Cells(i, j) = ""
'=B$1&"X"&$A2&"="&B$1*$A2
Next
Next
End Sub
06_九九乘法表加上邏輯與底色VBA
Public Sub 九九乘法表_3整除()
For j = 2 To 10
For i = 2 To 10
If (i - 1) Mod 3 = 0 Then
Cells(i, j).Interior.Color = RGB(255, 255, 0)
Cells(i, j).Font.Color = RGB(255, 0, 0)
End If
'=B$1&"X"&$A2&"="&B$1*$A2
Cells(i, j) = (j - 1) & "X" & (i - 1) & "=" & (j - 1) * (i - 1)
Next
Next
End Sub
Public Sub 清除格式()
For i = 2 To 10
For j = 2 To 10
'Cells(i, j).Interior.Color = RGB(255, 255, 255)
'Cells(i, j).Font.Color = RGB(0, 0, 0)
Cells(i, j).Interior.ColorIndex = xlNone
Cells(i, j) = ""
Next
Next
'Range("B2:J10").Interior.Color = RGB(255, 255, 255)
End Sub
07_如何在九九乘法表中加入框線(Border物件使用)
Sub 加框線_實線()
'指定框線的線條樣式。
'名稱 值 描述
'xlContinuous 1 連續線
'xlDash -4115 虛線
'xlDashDot 4 交替的虛線與點
'xlDashDotDot 5 虛線後接兩點
'xlDot -4118 點狀線
'xlDouble -4119 雙線
'xlLineStyleNone -4142 無線條
'xlSlantDashDot 13 斜虛線
Range("B2:J10").Borders.LineStyle = xlContinuous
End Sub
Sub 加框線_實線紅色()
Range("B2:J10").Borders.LineStyle = xlContinuous
Range("B2:J10").Borders.Color = RGB(255, 0, 0)
End Sub
Sub 加框線_實線紅色粗線()
'指定範圍外圍框線的粗細。
'名稱 值 描述
'xlHairline 1 毫線 (最細的框線)
'xlMedium -4138 適中
'xlThick 4 粗線 (最寬的框線)
'xlThin 2 細線
Range("B2:J10").Borders.LineStyle = xlContinuous
Range("B2:J10").Borders.Color = RGB(255, 0, 0)
Range("B2:J10").Borders.Weight = xlThick
End Sub
Public Sub 清除框線()
Range("B2:J10").Borders.LineStyle = xlNone
End Sub
備註：用INPUTBOX選取範圍
Sub 加框線_實線()
Set X = Application.InputBox("範圍!!!", Type:=8)
X.Borders.LineStyle = xlContinuous
End Sub
Public Sub 清除框線()
Set X = Application.InputBox("範圍!!!", Type:=8)
X.Borders.LineStyle = xlNone
End Sub
Type:=8 →儲存格參照，視為 Range 物件
必須使用 Set 陳述式將結果指定給 Range 物件
Application.InputBox的使用方法
備註：InputBox 函數 與 Application.InputBox方法的差別
08_定存範例與轉為VBA說明
公式=$B$1*(1+$A4)^B$3
函數=FV($A4,B$3, ,-$B$1)
FV函數說明
VBA
Public Sub 計算()
'Range("B1") = Application.InputBox("請輸入存款金額!!!", Type:=1)
Range("B1") = InputBox("請輸入存款金額!!!")
For j = 2 To Range("B3").End(xlToRight).Column
For i = 4 To Range("A4").End(xlDown).Row
Cells(i, j) = Range("B1") * (1 + Cells(i, "A")) ^ Cells(3, j)
'=$B$1*(1+$A4)^B$3
Next
Next
End Sub
Public Sub 計算_函數()
Range("B1") = Application.InputBox("請輸入存款金額!!!", Type:=1)
'Range("B1") = InputBox("請輸入存款金額!!!")
For i = 4 To Range("A4").End(xlDown).Row
For j = 2 To Range("B3").End(xlToRight).Column
'Cells(i, j) = Range("B1") * (1 + Cells(i, "A")) ^ Cells(3, j)
Cells(i, j) = Application.WorksheetFunction.FV(Cells(i, "A"), Cells(3, j), 0, -Range("B1"))
'=FV($A4,B$3,0,-$B$1)
Next
Next
End Sub
Public Sub 計算清除()
For j = 2 To Range("B3").End(xlToRight).Column
For i = 4 To Range("A4").End(xlDown).Row
Cells(i, j) = ""
'=$B$1*(1+$A4)^B$3
Next
Next
End Sub