2020年10月28日 星期三

Excel VBA FOR 迴圈 簡單教學

 同事卡在迴圈問題,按課本說得,可以作出99乘法表,但是確被書籍的課後練習題卡關了,所以寫在BLOG當作記錄跟分享。

題目:如何使每一列顯示的數字以降冪的方式第一列1到6,第二列1到5方式,直到數字1則結束。

首先先來溫習一下單迴圈行跟列操作,與雙迴圈的標準練習題"99乘法表"。

迴圈三大要素:起始值、結束值/條件、步進值。

結束值/條件:這部分特別說明,不同迴圈操作方式會存在差異,所以不一定是值,在處理某些情況要靠指定條件成立則離開迴圈。

行:

先清除使用中的工作表資料

設定 I 的起始值為1結束為6,每次步進值為1

以CELLS方式控制資料寫入,若單獨要寫在A行,也可以寫成RANGE("A" & I)也行歐

CODE:

ActiveSheet.Cells.Clear

For I = 1 To 6 Step 1

        ActiveSheet.Cells(I, 1) = 1   '
     
Next I

列:

差別在CELLS( 1 , I ) 這部分,要換再把數字 1 改掉即可

CODE:

ActiveSheet.Cells.Clear

For I = 1 To 6 Step 1

        ActiveSheet.Cells(1, I) = 1
     
Next I

雙迴圈:

99乘法表:

先清除使用中的工作表資料

設定 I 迴圈的起始值為1結束為9,每次步進值為1

設定 J 迴圈的起始值為1結束為9,每次步進值為1

Cells(J, I) 為 J 控制列,I 控制行

CODE:

ActiveSheet.Cells.Clear

For I = 1 To 9 Step 1

    For J = 1 To 9 Step 1

        ActiveSheet.Cells(J, I) = I * J
    
    Next J
Next I

回顧一下題目:如何使每一列顯示的數字以降冪的方式第一列1到6,第二列1到5方式,直到數字1集結束。

在這需要發揮一下想像力,當然也很多書籍是當數學問題來看。試著想像一下用雙迴圈,標準輸出會是如下圖數字所示,根據題目要求,反黃的部分是我們不需要的。想像一下似乎能找到一些規則在其中。

第一列0格黃,第2列1格黃,第3列2格黃,第4列3格黃,第5列4格黃,第6列5格黃,對吧!!!!

我們再來回顧一下迴圈在運作時,本身的三大要素"起始值、結束值/條件、步進值",且一設定好,除了起始值外,其他就不能改了。

第一次嘗試:

第一列0格黃,第2列1格黃,第3列2格黃,第4列3格黃,第5列4格黃,第6列5格黃;所以每一列-1,所以寫成  J - (I - 1):每一列-1

CODE:

ActiveSheet.Cells.Clear

For I = 1 To 6 Step 1

    For J = 1 To 6 Step 1
    
         ActiveSheet.Cells(I, J) = J - (I - 1)
       
    Next J
    
Next I

問題:沒想到 J-(I-1)時第一列後,I越來越大,J都是1開始,自然出現負數。如果還是這樣去思考,真的就是個數學問題了,不行!!!這絕對不是一個好的出發點。

重新觀察問題:位置似乎都是固定的,每一行都是固定的數字。所以昇華思考,讓每一行出現應該出現的數字,然後對應每一列作遞減如何??? 在來TRY TRY

說明:將J 迴圈得結束值組合上 I 的開始值再減一。For J = 1 To 6 - (I - 1) Step 1

CODE:

For I = 1 To 6 Step 1

    For J = 1 To 6 - (I - 1) Step 1
    
         ActiveSheet.Cells(I, J) = J
       
    Next J
    
Next I

大功告成!!!!!

另解:組合IF條件式作判斷

把原先 J 迴圈的結束值改成 IF 條件式方式去判斷,滿足條件才執行寫入資料。

CODE:

ActiveSheet.Cells.Clear

For I = 1 To 6 Step 1

    For J = 1 To 6 Step 1
    
        If J <= (6 - (I - 1)) Then

            ActiveSheet.Cells(I, J) = J
        
        End If
        
    Next J
    
Next I

腦力激盪一下,來挑戰單迴圈:

組合 IF 判斷式的作法,來作一下變化。

我增加了一個SETP_FINISH的變數作為列的控制。

另外保持原 IF 判斷式概念來判斷資料寫入( I 改成 SETP_FINISH )

但是要寫6行6列ㄝ,I 迴圈則扮演 控制行的角色,SETP_FINISH是列, I 是行。然後增加一個判斷當 I =6時,使起始值 I=0(使下一次迴圈開始時 I+步進值=1),

SETP_FINISH控制列,則換下一列故為SETP_FINISH+1

似乎少了控制SETP_FINISH的結束值,那我們可以再寫一個 IF 判斷試來判斷 SETP_FINISH滿足大於6時,執行離開迴圈(EXIT FOR)

CODE:

ActiveSheet.Cells.Clear

SETP_FINISH = 1

For I = 1 To 6 Step 1

        If SETP_FINISH > 6 Then
                
            Exit For
        
        End If


        If I <= (6 - (SETP_FINISH - 1)) Then

            ActiveSheet.Cells(SETP_FINISH, I) = I
        
        End If
        
        If I = 6 Then
        
            SETP_FINISH = SETP_FINISH + 1
            
            I = 0
        
        End If
Next I

GOOD。分享,程式可以解決數學問題,但別把程式當數學看,多一點想像力跟觀察,自然會走出一條路。

2020年10月27日 星期二

VBA入門:樞紐分析 小工具(列資料隱藏、增加與取消小計)

 

最近寫了各樞紐分析的案子,用了一些小副程式,提供參考。

功能取得樞紐分析表的名稱,列印在即時運算上。

Private Sub CommandButton1_Click()

For A = 1 To ActiveSheet.PivotTables.Count Step 1

    Debug.Print ActiveSheet.PivotTables(A).Name

         Next

End Sub

隱藏列資料的選項。

說明:xlHidden是隱藏,很多人當作刪除資料

Sub DELETE_All_PTFieldsRow()

 For Each pf In ActiveSheet.PivotTables("PivotTable1").RowFields

    pf.Orientation = xlHidden

Next pf

End Sub

資加列的資料,副程式引數為文字資料,直接增加。

Sub PivotTable_ADD(ROW_NAME)

    With ActiveSheet.PivotTables("PivotTable1").PivotFields(ROW_NAME)

        .Orientation = xlRowField

    End With

End Sub

取消各行的小計結果,副程式引數為陣列資料,陣列內須以文字型態設定資料

Sub PivotTable_Subtotals_CANEL(TAG)

Set pt = ActiveSheet.PivotTables(1)

With pt

 For P = LBound(TAG) To UBound(TAG, 2) Step 1

      .PivotFields(TAG(1, P)).Subtotals(1) = True     

     .PivotFields(TAG(1, P)).Subtotals(1) = False

 Next

End With