105.03.17 第3次上課 (範例-02邏輯函數.xls)

 

01_AND函數與轉為VBA說明

Excel  AND函數  OR函數

AND(條件1 , 條件2 ) 兩者皆成立時回傳 True

OR(條件1 , 條件2 ) 其中之一成立時回傳 True

配合 IF 判斷式

If ( AND(條件1 , 條件2) , 成立時執行 ,不成立時執行)

轉為VBA 程序

Sub _____()
      If  條件1 And 條件2 Then
          成立時執行
      Else:不成立時執行
      End IF
End Sub

轉為VBA 自訂函數

Function ______( A , B )
         If  A~陳述式 And B~陳述式 Then
             成立時執行
         Else:不成立時執行
         End If
End Function

例題

E2==IF(AND(B2>20,D2>80000),"優","劣")

 

 

VBA

Private Sub 銷售情況()
      For i = 2 To 7
      If Cells(i, "B") > 20 And Cells(i, "D") > 80000 Then
        Cells(i, "F") = "優"
      Else: Cells(i, "F") = "劣"
      End If
      Next
End Sub

Private Function 銷售業績(銷售量, 銷售金額)
      If 銷售量 > 20 And 銷售金額 > 80000 Then
        銷售業績 = "優"
      Else: 銷售業績 = "劣"
      End If
End Function

 

02_增減體重範例與VBA解法

K4==IF(OR(J4>5%,J4<-5%),"不標準","標準")

L4==IF(AND(J4<5%,J4>-5%),"標準","不標準")
 

VAB

Private Sub 標準體重1()
    For i = 4 To 13
    If Cells(i, "J") > 0.05 Or Cells(i, "J") < -0.05 Then
         Cells(i, "M") = "不標準"
    Else: Cells(i, "M") = "標準"
    End If
    Next
End Sub

Private Function 標準體重函數(百分比)
      If 百分比 > 0.05 Or 百分比 < -0.05 Then
         標準體重函數 = "不標準"
      Else: 標準體重函數 = "標準"
      End If
End Function
 

03_如何反向刪除條件為標準的列

刪除資料，利用反向迴圈，避免後一列自動補上要計算列數的問題

正向迴圈 For i = 4 to 13

反向迴圈 For i = 13 to 4 Step-1
 

04_安養中心函數邏輯與解答

F5==IF(AND(D5>=70,RIGHT(E5,2)=”北市”),”符合條件”,”不符合條件”)

G5==IF(AND(D5>=70,OR(E5=”台北市”,E5=”新北市”)),”符合條件”,”不符合條件”)
 

05_安養中心VBA與表單製作

VBA程序與自訂函數

Private Sub 資格判定()
      For i = 5 To Range("A4").End(xlDown).Row
      If Cells(i, "D") >= 70 And Right(Cells(i, "E"), 2) = "北市" Then
        Cells(i, "H") = "符合條件"
      Else: Cells(i, "H") = "不符合條件"
      End If
      Next
End Sub

Private Function 條件函數(年齡, 戶籍地)
      If 年齡 >= 70 And Right(戶籍地, 2) = "北市" Then
        條件函數 = "符合條件"
      Else: 條件函數 = "不符合條件"
      End If
End Function
 

自訂表單

 

06_如何產生可以自動更新的下拉清單

Private Sub B01_Click()
    r = Sheets("綜合練習2").Range("A4").End(xlDown).Row + 1
    Sheets("綜合練習2").Cells(r, "A") = txt01.Text
    Sheets("綜合練習2").Cells(r, "B") = txt02.Text
    Sheets("綜合練習2").Cells(r, "C") = txt03.Text
    Sheets("綜合練習2").Cells(r, "D") = "=DATEDIF(C" & r & ",$F$2,""Y"")"
    '下拉選單
    Sheets("綜合練習2").Cells(r, "E") = cb01.Text
    Sheets("綜合練習2").Cells(r, "F") = 條件函數(Sheets("綜合練習2").Cells(r, "D"), Sheets("綜合練習2").Cells(r, "E"))
    '清空表單資料
    txt01.Value = ""
    txt02.Value = ""
    txt03.Value = ""
    cb01.Value = ""
    End Sub

Private Sub UserForm_Activate()
    '利用迴圈抓取資料 AddItem加入選單選項
    For i = 1 To Sheets("戶籍地").Range("A1").End(xlDown).Row
      cb01.AddItem Sheets("戶籍地").Cells(i, "A")
    Next
End Sub

備註

下拉選單 新增選項
XXX.AddItem 範圍.內容

 

07_補充兩個工作表範圍比對解答 (範例_兩個工作表範圍比對.xlsm)

Public Sub 工作表比較()
    Call 清除設定
    For i = 4 To 9
      For j = 3 To 14
        If Sheets(1).Cells(i, j) <> Sheets(2).Cells(i, j) Then
          '儲存格增加底色
         Cells(i, j).Interior.Color = RGB(255, 128, 128)
          '儲存格增加備註
          Cells(i, j).AddComment "去年度:" & Sheets(2).Cells(i, j)
        End If
      Next
    Next
End Sub

Sub 清除設定()
    For i = 4 To 9
      For j = 3 To 14
        If Sheets(1).Cells(i, j) <> Sheets(2).Cells(i, j) Then
          'RGB(255,255,255)-白色
          Cells(i, j).Interior.Color = RGB(255, 255, 255)
          '儲存格移除備註
          Cells(i, j).ClearComments
        End If
     Next
    Next
End Sub