EXCEL

教學目標:完成以下程式

20250207盤中AM11 30(YT)

尚在收集資料階段! 未經有系統整理

A Step-by-Step Guide to Create a Pivot Table in Excel using VBA – MACRO CODE(link)
Excel 教學 E44 | 利用樞紐多重彙總功能，快速進行跨頁彙總合併計算(YT)
樞紐分析表要 R.I.P. 了？Excel 新秘密武器 GROUPBY + PIVOTBY 函數讓你的數據分析能力開掛！(YT)
GROUPBY with Multiple Tables in Excel – The Ultimate Guide!(YT)

如何將工作表或工作簿合併或合併為一個工作表？(link)
EXCEL新函數VSTAK快速合併多個工作表(link)

GROUPBY 函數(link)

Quick Excel Chart VBA Examples(link)

Intro to VBA: Macros Explained in the Kitchen (Part 1 of 3)(link)

Intro to VBA: The Excel Object Hierarchy (Part 2 of 3)(link)

Intro to VBA: Reading, Writing and Running Code (Part 3 of 3)(link)

Start with last word and work your way left, replacing the dots/periods with the words, “of” or “of the”.
Example:

Application.Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”).Range(“A1”).Value = 100

Starting from the right…  Set the Value of the Range A1 of Sheet1 of Book1.xlsx of the Application (Excel) equal to 100.

There are three basic things we can do with properties and methods.
#1 – Read Properties
We can get information from Excel by reading the properties of the objects. This is similar to asking questions about the current workbook, worksheet, etc. Here are a few examples:

Worksheets.Count – returns the number of worksheets in the active workbook.

Range("A1").Value – returns the value in cell A1 of the active workbook and active sheet.

#2 – Write Properties

We can also set or change the properties of the objects in Excel. This is usually done by using the equals sign “=” in VBA. Here are a few examples:

Worksheets(1).Name = "Summary" – changes the name of the first worksheet in the active workbook to Summary.

Range("A1").Value = 100 – changes the value in cell A1 to 100.

#3 – Perform Actions with Methods

Methods are actions that can be performed with the object. These are usually actions you would take in Excel by pressing a menu button or keyboard shortcut. 

Range("A1").Copy Range("D1") – copies cell A1 and pastes it to cell D1

ActiveWorkbook.Close – Closes the active workbook.

Worksheets.Add – adds a worksheet before the active sheet (same as Shift+F11 keyboard shortcut)
When we use the Worksheets.Add method in Excel to add a worksheet, the Add method has optional arguments or parameters that can be specified to tell VBA: where to place the new sheet, how many sheets to insert, and what type of sheet.

定位

The following is the Excel object hierarchy:
Application > Workbook > Worksheet > Range
Application > Workbook > Worksheet > Cells

例:
Application.Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("B5").Value = 100

Application.Workbooks("Book1.xlsx").Worksheets("Sheet1").Cells(5,2).Value = 100

Application.Workbooks("Book1.xlsx").Activate

Worksheets("Sheet1").Range("D6")
Worksheets("Sheet1").Cells(6,4)

if Sheet1 is active, then both of these lines will refer to the same cell range:

Application.Workbooks("Book1.xlsx").Worksheets("Sheet1").Activate
Range("D6")              Cells(6,4)          Cells(6,"D")      Cells(Row, Column) 

If you don't specify the workbook or worksheet in a line of code, then VBA assumes you are referring to the active workbook and active worksheet.

To refer to a range of cells,
Range("A1:D6")         Range("A1", "D6")

Dim lastRow as long
lastRow=100
Range("A" & lastRow)   -->       Range("A100")    
Range("A1:D" & lastRow) --> Range("A1:D100")

To refer to an entire row, for example, Row 5:
Range("5:5")

To refer to an entire column, for example, Column D:
Range("D:D")

Excel VBA also allows you to refer to multiple ranges at once
Range("B2:D8, F4:G5")

To make it quicker for you to type, you can use shortcuts that involve using square brackets without quotes, as shown in the table below:

Syntax            Shortcut

Range("D5")     [D5]

Range("A1:D5") [A1:D5]

Range("5:5")       [5:5]

Range("B2:D8, F4:G5")     [B2:D8, F4:G5]

Members of the Worksheet object can include: Ranges, PivotTables, Shapes, Charts, ListObjects, etc.

To refer to a range outside the active sheet, you need to include the worksheet name.
Worksheets("Sheet2").Range("A1:D5")

 Like the Cells property, the Offset property has two parameters. The first determines how many rows to offset, while the second represents the number of columns to offset. Here is the syntax:
Range.Offset(RowOffset, ColumnOffset)

For example, the following code refers to cell D5 from cell A1:
Range("A1").Offset(4,3)

The following example refers to cell A1:
Range("D3").Offset(-2, -3)

If you need to go over only a row or a column, but not both, you don’t have to enter both the row and the column parameters. You can also use 0 as one or both of the arguments. For example, the following lines refer to D5:
Range("D5").Offset(0, 0)
Range("D2").Offset(3, 0)
Range("G5").Offset(, -3)

To select a range of cells, use the Select method.
The following line selects a range from A1 to D5 in the active worksheet:
Range("A1:D5").Select

To select a range from A1 to the active cell, use the following line:
Range("A1", ActiveCell).Select

The following code selects from the active cell to 3 rows below the active cell and five columns to the right:
Range(ActiveCell, ActiveCell.Offset(3, 5)).Select

It’s important to note that when you need to select a range on a specific worksheet, you need to ensure that the correct worksheet is active. Otherwise, an error will occur. For example, you want to select B2 to J5 on Sheet1. The following code will generate an error if Sheet1 is not active:
Worksheets("Sheet1").Range("B2:J5").Select
Instead, use these two lines of code to make your code work as expected:
Worksheets("Sheet1").Activate
Range("B2:J5").Select

The following statement sets a value of 100 into cell C7 of the active worksheet:
Range("C7").Value = 100

The Value property allows you to represent the value of any cell in a worksheet. It’s a read/write property, so you can use it for both reading and changing values.
You can also set values of a range of any size. The following statement enters the text “Hello” into each cell in the range A1:C7 in Sheet2:
Worksheets("Sheet2").Range("A1:C7").Value = "Hello"

Value is the default property for a Range object. This means that if you don’t provide any properties in your range, Excel will use this Value property.
Both of the following lines enter a value of 100 into cell C7 of the active worksheet:
Range("C7").Value = 100
Range("C7") = 100

Excel VBA: Clear a range
There are three ways to clear a range in Excel VBA.
The first is to use the Clear method, which will clear the entire range, including cell contents and formatting.
The second is to use the ClearContents method, which will clear the contents of the range but leave the formatting intact.
The third is to use the ClearFormats method, which will clear the formatting of the range but leave the contents intact.For example, to clear a range B1 to M15, you can use one of the following lines of code below, based on your needs:
Range("B1:M15").Clear
Range("B1:M15").ClearContents
Range("B1:M15").ClearFormats

Excel VBA: Delete a range

When deleting a range, it differs from just clearing a range. That’s because Excel shifts the remaining cells around to fill up your deleted range.
The code below deletes Row 5 using the Delete method:
Range("5:5").Delete

To delete a range that is not a complete row or column, you have to provide an argument (such as xlToLeft, xlUp — based on your needs) that indicates how Excel should shift the remaining cells.
For example, the following code deletes cell B2 to M10, then fills the resulting gap by shifting the other cells to the left:
Range("B2:M10").Delete xlToLeft

寫入公式(字串) 與 複製公式

Range("C1").Formula = "=A1+B1"
Range("C1").Copy
Range("C1:C10").Pastespecial(XlPasteall)

例選取2列5行的儲存格

Cells(2,5).Select

選取單一儲存格，例如E2儲存格

Range("E2").Select

選取一個範圍，例如B3:E6 範圍

Range("B3:E6").Select

也可以使用Range(Cells(2, 5), Cells(3, 6)).Select來選擇一個範圍

跳著選取多格儲存格

Range(“A1,B2,A3,B4”).Select

選取單一欄：

Range(“B:B”).Select

一次選取多欄：

Range(“B:B,E:E”).Select

如要選取列的話只要把欄英文改列數字即可

Range("2:2,5:5").Select

使用Cells在儲存格中填入123

Cells(2, 3) = 123

使用Range範圍中(B2:C5)中填入100

Range("B2", "C5") = 100

Cells跟Range很像但差別在哪裡？

主要差別在Cells更為彈性，可以使用變數的方式填入，例如想要在C1到C5的儲存格寫入倍數5的數字，即可使用for迴圈來實現，將變數值填入Cells中。

儲存格樣式設定

首先在B2 輸入一個預設文字Hello World

將B2中的文字設定大小為16

Range(“B2”).Font.Size = 16

將B2中的文字設定顏色為綠色

Range(“B2”).Font.Color = RGB(0, 255, 0)

將B2中的背景改成藍色

Range(“B2”).Interior.Color = RGB(0, 0, 255)

將B2設定為粗體

Range(“B2”).Font.Bold = true

調整B2欄位寬度

Range(“B2”).ColumnWidth = 25

將B2設定為自動調整欄寬

Range(“B2”).EntireColumn.AutoFit

將B2外框設定為雙框線

Range(“B2”).Borders.LineStyle = xlDouble

清除B2的資料格式

Range(“B2”).ClearFormats

清除B2儲存格內容

Range(“B2”).ClearContents

Range 與 Cells 互相轉換

Range 轉 Cells

Dim rng As Range

Set rng = Range("A1:B2")

Dim cell As Range

Set cell = rng.Cells(1, 1) ' 轉換為範圍內的第一個儲存格

Cells 轉 Range

Dim cell As Range

Set cell = Cells(1, 1)

Dim rng As Range

Set rng = Range(cell.Address) ' 轉換為範圍

Range 物件

優點：可以一次操作多個儲存格，適合處理範圍操作。

缺點：需要指定範圍，對於動態範圍操作較不方便。

注意事項：範圍必須存在，否則會出錯。

Cells 物件

優點：可以精確定位單一儲存格，適合逐一操作儲存格。

缺點：一次只能操作一個儲存格，對於大範圍操作效率較低。

注意事項：行列索引必須在工作表範圍內，否則會出錯。

ThisWorkbook.Sheets("工作表1").Range("B1").Value = ThisWorkbook.Sheets("工作表1").Range("A1").Address
$A$1
ThisWorkbook.Sheets("工作表1").Range("B2").Value = ThisWorkbook.Sheets("工作表1").Range("A1").Address(False, False)
A1

ThisWorkbook.Sheets("工作表1").Range("B3").Value = ThisWorkbook.Sheets("工作表1").Range("A1").Address(True, False)
$A1

ThisWorkbook.Sheets("工作表1").Range("B4").Value = ThisWorkbook.Sheets("工作表1").Range("A1").Address(False, True)
A$1

ThisWorkbook.Sheets("工作表1").Range("C1").Value = ThisWorkbook.Sheets("工作表1").Range("B3").Row           '3

ThisWorkbook.Sheets("工作表1").Range("C2").Value = ThisWorkbook.Sheets("工作表1").Range("B3").Column      '2

ThisWorkbook.Sheets("工作表1").Range("D1").Value = ThisWorkbook.Sheets("工作表1").Cells(3, 2).Row '3

ThisWorkbook.Sheets("工作表1").Range("D2").Value = ThisWorkbook.Sheets("工作表1").Cells(3, 2).Column '2

Set mc = Worksheets("Sheet1").Cells(1, 1) 

MsgBox mc.Address() ' $A$1 

MsgBox mc.Address(RowAbsolute:=False) ' $A1 

MsgBox mc.Address(ReferenceStyle:=xlR1C1) ' R1C1 

MsgBox mc.Address(ReferenceStyle:=xlR1C1, _ 

 RowAbsolute:=False, _ 

 ColumnAbsolute:=False, _ 

 RelativeTo:=Worksheets(1).Cells(3, 3)) ' R[-2]C[-2]

=MATCH(MAX(IF(ISNUMBER($B2:$B$85),$B2:$B$85)),$B2:$B$85, 0)

Cells(2, 16).Formula2R1C1 = _

"=MATCH(MAX(IF(ISNUMBER(RC2:R" & (lastRowA - 2) & "C2),RC2:R" & (lastRowA - 2) & "C2)),RC2:R" & (lastRowA - 2) & "C2, 0)"

Cells(2, 16).Formula = _

"=MATCH(MAX(IF(ISNUMBER(B2:B" & lastRowA  & "), B2:B" & lastRowA & " )),B2:B" & lastRowA  & ", 0)"

VBA code

Dim rng as range
Dim lastRowA as long
lastRowA = ActiveSheet.Range("A1").End(xlDown).Row
Set rng = ActiveSheet.Range("K2:K" & lastRowA)

Range("B1").Formula = "=MIN(" & rng.Address & ")"

Dim rng As Range, targetCell As Range, diffRange As Range, minDiffCell As Range

For Each cell In rng
        diffRange.Cells(cell.Row - rng.Row + 1, 1).Formula = "=ABS(" & cell.Address & "-" & targetCell.Address & ")"

Next cell

=MATCH(搜尋值,搜尋範圍,搜尋類型)

以下是個參數的意義：

搜尋值：想要尋找的值。

搜尋範圍：搜尋的範圍。

搜尋類型：指定搜尋類型，若至指定為 0 則代表尋找完全一樣的值；若指定為 1 則代表尋找小於或等於搜尋值的最大值；若指定為 -1 則代表尋找大於或等於搜尋值的最小值。

MATCH 函數會搜尋儲存格範圍中的指定項目，並傳回該項目於該範圍中的相對位置。 例如，若範圍 A1:A3 中含有值 5、25 及 38，則公式 =MATCH(25,A1:A3,0) 會傳回數字 2，因為 25 是範圍中的第二個項目。

如何使用 Excel 的 MATCH 函數 (link)

formula = "=AND(

ISNUMBER(" & rng.Cells(1, 1).Address(False, True) & "),

 " & rng.Cells(1, 1).Address(False, True) & "=MAX(" & rng.Address & "))"

 AA.Range("A1").Formula = "=U" & iRow & "*1000*(1-0.001425-0.003)-AM" & iRow 

    With AA.Range("A1")

        .FormatConditions.Add Type:=xlExpression, Formula1:="=A1>0"

        .FormatConditions(1).Font.Color = -16776961

        .FormatConditions.Add Type:=xlExpression, Formula1:="=A1<0"

        .FormatConditions(2).Font.Color = -11489280

    End With

' 找到最後一列

lastRowA =Range("A1").End(xlDown).Row    '如果中間有空白資料，就會停止，由最後資料開始找，會保險些。

lastRowA = Range("A65535").End(xlUp).Row  '從最後一row找到有資料為止

lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row    '第 1 colum 最後1 row

lastColumn=ActiveSheet.Range(5, Columns.Count).End(xlToLeft).Column   '第5 row 最後1 column

=FILTER(A2:D,B2:B>=60) 

用途：篩選 A2:D 欄位範圍，條件為 B2:B 欄位裡的資料，需大於等於 60

=FILTER(F2:F100 ,ISNUMBER(F2:F100))   '篩選F2:F100欄位範圍內 為數值者

=MAX(FILTER(F2:F100  ,  ISNUMBER(F2:F100)))  '再求最大值

' Call欄位F 負值紅色 最大值與最小值 藍色

 Dim lastRowA As Long
Dim formula1A As String
Dim formula1B As String
lastRowA = ActiveSheet.Range("A1").End(xlDown).Row

Range(Cells(2, 6), Cells(lastRowA, 6)).Select
formula1A = "=AND(ISNUMBER($F2),$F2=MAX(FILTER($F$2:$F$" & lastRowA & ",ISNUMBER($F$2:$F$" & lastRowA & "))))"   Selection.FormatConditions.Add Type:=xlExpression, formula1:=formula1A
formula1B = "=AND(ISNUMBER($F2),$F2=MIN(FILTER($F$2:$F$" & lastRowA & ",ISNUMBER($F$2:$F$" & lastRowA & "))))"   Selection.FormatConditions.Add Type:=xlExpression, formula1:=formula1B
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, formula1:="=0"

With Selection.FormatConditions(1)
        .Interior.Color = RGB(0, 0, 255)
        .Font.Color = RGB(255, 255, 255)
        .TintAndShade = 0
End With

With Selection.FormatConditions(2)
        .Interior.Color = RGB(0, 0, 255)
        .Font.Color = RGB(255, 255, 255)
        .TintAndShade = 0
End With

With Selection.FormatConditions(3).Font
        .Color = RGB(255, 0, 0)
        .TintAndShade = 0
End With

EXCEL & VBA功能 – 筆記(link)(股票相關)

Excel VBA Range Tutorial: How to Work with Ranges (+ Examples)(link)
https://blog.coupler.io/category/excel/(link)

 ABLEBIT.COM "Excel" category(link)

Auto-format Excel GROUPBY and PIVOTBY results with conditional formatting(link)

FormatConditions.Add method (Excel)(link)
FormatCondition.Formula2 property (Excel)(link)

This example adds a conditional format to cells E1:E10.
With Worksheets(1).Range("E1:E10").FormatConditions _
.Add(xlCellValue, xlGreater, "=$A$1")
    With .Borders
      .LineStyle = xlContinuous
      .Weight = xlThin
      .ColorIndex = 6
    End With 

    With .Font
      .Bold = True
      .ColorIndex = 3
    End With 

End With

How to correct a #VALUE! error in AVERAGE or SUM functions(link)
Excel VBA Formulas – The Ultimate Guide(link)