Excel-扣除六日與特殊日公式

Posted on October 9, 2021 by Tingyu Cho

最近在公司要幫忙追蹤大家資料繳交的狀況，主管希望我可以幫忙寫一個公式來計算大家從需求提出到繳交資料的耗時天數，來評量每個部門的回答效率，一開始只是寫了很簡單的A-B計算，但因這個案子持續將近一個月，中間又有雙十連假，因此希望公式可以升級，自動扣除掉例假日與國慶日。

來拆解這個公式：

=IF(ISBLANK(B2)," ",IF(ISBLANK(F2),NOW()-B2-(WEEKNUM(TODAY(),2)-WEEKNUM(B2,2))*2-IF(WEEKNUM(TODAY(),2)-WEEKNUM($K$1,2,1,0),F2-B2-(WEEKNUM(F2,2)-WEEKNUM(B2,2))*2-IF(WEEKNUM(M2)>WEEKNUM($K$1,2),1,0)))

先以必要輸入資料格B欄來做判定，若B欄值為空值，則G欄亦為空值，此辦法可以一次套用整個G欄，就不用每次都拉一次公式。

=IF(ISBLANK(B2),” “,IF(ISBLANK(F2),NOW()-B2-(WEEKNUM(TODAY(),2)-WEEKNUM(B2,2))*2-IF(WEEKNUM(TODAY(),2)>WEEKNUM($K$1,2),1,0),F2-B2-(WEEKNUM(F2,2)-WEEKNUM(B2,2))*2-IF(WEEKNUM(M2)>WEEKNUM($K$1,2),1,0)))

如果未繳交資料，則以現在時間Now()減掉需求提出時間(B欄)，得到總經歷時間

=IF(ISBLANK(B2),” “,IF(ISBLANK(F2),NOW()-B2-(WEEKNUM(TODAY(),2)-WEEKNUM(B2,2))*2-IF(WEEKNUM(TODAY(),2)>WEEKNUM($K$1,2),1,0),F2-B2-(WEEKNUM(F2,2)-WEEKNUM(B2,2))*2-IF(WEEKNUM(M2)>WEEKNUM($K$1,2),1,0)))

用WEEKNUM()的公式，來計算現在是一整年當中的第幾週，Today(),2，後面的2是表示一個禮拜的第一天為星期一，再將兩個週數相減*

=IF(ISBLANK(B2),” “,IF(ISBLANK(F2),NOW()-B2-(WEEKNUM(TODAY(),2)-WEEKNUM(B2,2))*2-IF(WEEKNUM(TODAY(),2)>WEEKNUM($K$1,2),1,0),F2-B2-(WEEKNUM(F2,2)-WEEKNUM(B2,2))*2-IF(WEEKNUM(M2)>WEEKNUM($K$1,2),1,0)))

如果今天大於K1(國慶連假)，則再扣掉一天

=IF(ISBLANK(B2),” “,IF(ISBLANK(F2),NOW()-B2-(WEEKNUM(TODAY(),2)-WEEKNUM(B2,2))*2–IF(WEEKNUM(TODAY(),2)>WEEKNUM($K$1,2),1,0),F2-B2-(WEEKNUM(F2,2)-WEEKNUM(B2,2))*2-IF(WEEKNUM(M2)>WEEKNUM($K$1,2),1,0)))

如果已繳交(F欄)則改以繳交日期減掉需求提出日期(B欄)作為所耗時間(G欄)，同樣扣除相減週數*2與國慶連假

Excel建立下拉式選單

Posted on August 23, 2021 by Tingyu Cho

大家應該常常會收到一些用Excel表格會要需要以下拉式的選單來選填，使用Excel建立清單可以限定填表者在選填的時候只選取幾個特定的內容，又或者是可以用來提示表格需填寫的資料，是一個很方便的功能喔！

1.點選需要建立清單的表格，選取資料>資料驗證>資料驗證

2.在”儲存格內允許”中選擇”清單”

3. 輸入選單內容，已逗號隔開每個選項

也可以將選單內容建立在表單中，已選取範圍建立

4. 按下確定後下拉式選單就建立完成了

Excel VBA-For/Do While迴圈

Posted on August 17, 2021 by Tingyu Cho

計算判斷式可以使用的迴圈有兩種，若有確定的範圍可以使用for迴圈，若資料量較大或隨時會更新則可使用while迴圈。

For迴圈/If 判斷式

r = 0 '預設值

For r = 2 To 8 '計算範圍

    w = Cells(r, 4) '體重
    l = Cells(r, 3) '身高
    bmi = Math.Round(w / (l * 0.01) ^ 2) '計算BMI
    Cells(r, 5) = bmi '填入
    
    '判斷BMI

    If bmi >= 35 Then
        Cells(r, 7) = "重度肥胖"
    ElseIf bmi >= 30 Then
        Cells(r, 7) = "中度肥胖"
    ElseIf bmi >= 27 Then
        Cells(r, 7) = "輕度肥胖"
    ElseIf bmi >= 24 Then
        Cells(r, 7) = "過重"
    ElseIf bmi >= 18.5 Then
        Cells(r, 7) = "正常"
    Else
        Cells(r, 7) = "過瘦"
    End If
Next r 'For迴圈結尾

Do While迴圈/ Select判斷式

On Error Resume Next '

r = 2

Do While Not IsEmpty(Cells(r, 1))

    w = Cells(r, 4) '體重
    l = Cells(r, 3) '身高
    bmi = Math.Round(w / (l * 0.01) ^ 2) '計算BMI
    Cells(r, 6) = bmi '填入

    Select Case bmi
        Case Is < 18.5
            Cells(r, 8) = "過瘦"
        Case Is < 24
            Cells(r, 8) = "正常"
        Case Is < 27
            Cells(r, 8) = "過重"
        Case Is < 30
            Cells(r, 8) = "輕度肥胖"
        Case Is < 35
            Cells(r, 8) = "中度肥胖"
        Case Else
            Cells(r, 8) = "重度肥胖"
    End Select

    r = r + 1
Loop

Excel VBA-打卡系統

Posted on August 9, 2021 by Tingyu Cho

用VBA寫了一個簡單的打卡系統，可以判斷上班遲到早退以及加班時數

Private Sub CommandButton1_Click()
'r=>要準備寫資料的那一行 (ROW)
Dim r As Long
Dim d As Date
Dim t As Dated = Range("C3")
t = Range("C4")


'如果現在沒有資料，那麼r就從第二行開始寫 (r=2)
If Cells(10, 2) = "" Then
    r = 10
Else
    '如果有資料，找到最後一筆資料，並取得他的ROW
    Range("B9").Select
    Selection.End(xlDown).Select
    r = Selection.Row + 1

End If


'資料是從 r 開始寫入
Cells(r, 2) = Range("C2")
Cells(r, 4) = Range("C3")
Range("D" & r).Select
Selection.NumberFormatLocal = "yyyy/m/d"
Cells(r, 5) = Range("C4")
Range("E" & r).Select
Selection.NumberFormatLocal = "[$-x-systime]h:mm:ss AM/PM"

'顯示目前多少資料
Range("C7") = r - 10 + 1


'判斷上下班

If (DateDiff("h", t, "12:00:00")) > 0 Then
    Cells(r, 3) = "上班"
Else
    Cells(r, 3) = "下班"
End If

If (DateDiff("h", t, "9:30:00")) < 0 And (DateDiff("h", t, "9:00:00")) > 0 Then Cells(r, 6) = "遲到"
If (DateDiff("h", t, "18:00:00")) > 0 And (DateDiff("h", t, "12:00:00")) < 0 Then Cells(r, 6) = "早退"
If (DateDiff("h", t, "18:00:00")) < -1 And (DateDiff("h", t, "9:00:00")) < 0 Then
    out = -DateDiff("h", t, "19:00:00")
    Cells(r, 6) = "加班" & out & "小時"
End If


If r <> 10 And Cells(r - 1, 3) = "上班" And Cells(r, 3) = "上班" And DateDiff("d", d, Cells(r - 1, 4)) < 0 Then
    If Cells(r, 6) <> "" Then
        Text = Cells(r, 6)
        Cells(r, 6) = Text & " , 未登記下班"
    Else
        Cells(r, 6) = "未登記下班"
    End If

End If

If r <> 10 And Cells(r - 1, 3) = "上班" And Cells(r, 3) = "上班" And DateDiff("d", d, Cells(r - 1, 4)) = 0 Then
    Cells(r, 2) = ""
    Cells(r, 3) = ""
    Cells(r, 4) = ""
    Cells(r, 5) = ""
    Cells(r, 6) = ""
    MsgBox ("重複打卡")

End If

If r <> 10 And Cells(r - 1, 3) = "下班" And Cells(r, 3) = "下班" And DateDiff("d", d, Cells(r - 1, 4)) < 0 Then

    If Cells(r, 6) <> "" Then
        Text = Cells(r, 6)
        Cells(r, 6) = Text & " , 未登記上班"
    Else

        Cells(r - 1, 6) = "未登記上班"
    End If

End If

If r <> 10 And Cells(r - 1, 3) = "下班" And Cells(r, 3) = "下班" And DateDiff("d", d, Cells(r - 1, 4)) = 0 Then
    Cells(r, 2) = ""
    Cells(r, 3) = ""
    Cells(r, 4) = ""
    Cells(r, 5) = ""
    Cells(r, 6) = ""
    MsgBox ("重複打卡")

End If
End Sub

Excel VBA-抽獎/抽籤

Posted on August 8, 2021 by Tingyu Cho

用Excel VBA Rnd亂數以及Select Case語法，可以寫一個簡單的抽籤跟抽獎程式

Public Sub button1()
Dim r As Long

r = Int(Rnd * 10) + 1 ' 1~10

Select Case r

    Case 1: 'r=1
        Range("C4") = "大凶"
    Case Is <= 3
        Range("C4") = "凶"
    Case Is <= 7
        Range("C4") = "平"
    Case Is <= 9
        Range("C4") = "吉"
    Case Else
        Range("C4") = "大吉"

End Select
End Sub

Public Sub button2()
Dim r As Long

r = Int(Rnd * 101)

Select Case r
    Case Is <= 1 ' r=0, r=1 ===> case 0 to 1  ===> case 0,1  => case 0: case 1:
        Range("C4") = "GOGORO"
    Case Is <= 10
        Range("C4") = "機票"
    Case Is <= 30
        Range("C4") = "$6000"
    Case Is <= 60
        Range("C4") = "$3000"
    Case Else
        Range("C4") = "$1000"
End Select
End Sub