VBA实现非规则时间段年销售数据占比计算求助

Hey Julia, let's work through this problem together—since you're new to VBA, I'll keep this clear, practical, and easy to adapt to your workbook.

Here's the breakdown of what we need to do:

1. Capture your input start and end sales dates
2. Identify every calendar year covered by this date range
3. For each year, calculate how many days fall within your sales period
4. Divide that count by the total days in the year (with automatic leap year handling) to get your ratio

This macro will prompt you for your date range, then output the results in a new worksheet (you can tweak it to use an existing sheet if you prefer):

Sub CalculateYearlyDayRatios()
    Dim startDate As Date
    Dim endDate As Date
    Dim currentYear As Integer
    Dim firstYear As Integer
    Dim lastYear As Integer
    Dim yearStart As Date
    Dim yearEnd As Date
    Dim effectiveStart As Date
    Dim effectiveEnd As Date
    Dim totalDaysInYear As Integer
    Dim effectiveDays As Integer
    Dim outputSheet As Worksheet
    Dim rowNum As Integer
    
    ' Get user input for dates
    On Error Resume Next
    startDate = InputBox("Enter the start date (e.g., 2007-09-01):", "Start Date")
    endDate = InputBox("Enter the end date (e.g., 2010-04-01):", "End Date")
    On Error GoTo 0
    
    ' Validate input to avoid errors
    If startDate = 0 Or endDate = 0 Or startDate > endDate Then
        MsgBox "Please enter valid dates where the start date comes before the end date.", vbExclamation
        Exit Sub
    End If
    
    ' Create a new sheet for results (rename or point to an existing sheet if needed)
    Set outputSheet = ThisWorkbook.Sheets.Add
    outputSheet.Name = "Yearly Ratios"
    
    ' Set up header row
    outputSheet.Range("A1:D1").Value = Array("Year", "Effective Days", "Total Days in Year", "Ratio")
    outputSheet.Rows(1).Font.Bold = True
    
    rowNum = 2
    firstYear = Year(startDate)
    lastYear = Year(endDate)
    
    ' Loop through each year in the date range
    For currentYear = firstYear To lastYear
        ' Define full start/end of the current calendar year
        yearStart = DateSerial(currentYear, 1, 1)
        yearEnd = DateSerial(currentYear, 12, 31)
        
        ' Calculate effective start: later of sales start or year start
        effectiveStart = IIf(startDate > yearStart, startDate, yearStart)
        ' Calculate effective end: earlier of sales end or year end
        effectiveEnd = IIf(endDate < yearEnd, endDate, yearEnd)
        
        ' Get total days in the year (auto-handles leap years)
        totalDaysInYear = DateDiff("d", yearStart, DateAdd("yyyy", 1, yearStart))
        
        ' Calculate days in the sales period for this year (include both start/end dates)
        effectiveDays = DateDiff("d", effectiveStart, effectiveEnd) + 1
        
        ' Compute the ratio
        ratio = effectiveDays / totalDaysInYear
        
        ' Write data to the output sheet
        outputSheet.Cells(rowNum, 1).Value = currentYear
        outputSheet.Cells(rowNum, 2).Value = effectiveDays
        outputSheet.Cells(rowNum, 3).Value = totalDaysInYear
        outputSheet.Cells(rowNum, 4).Value = ratio
        ' Format ratio to 6 decimal places (change to "0.00%" for percentage if preferred)
        outputSheet.Cells(rowNum, 4).NumberFormat = "0.000000"
        
        rowNum = rowNum + 1
    Next currentYear
    
    ' Auto-fit columns for readability
    outputSheet.Columns("A:D").AutoFit
    
    MsgBox "Calculation done! Check the 'Yearly Ratios' sheet for results.", vbInformation
End Sub

1. Open your Excel workbook with the sales data
2. Press Alt + F11 to open the VBA Editor
3. Right-click your workbook in the Project Explorer > Insert > Module
4. Paste the code above into the new module
5. Press F5 to run the macro, or assign it to a button in your worksheet for easier access
6. When prompted, enter your start and end dates (use a format Excel recognizes, like 9/1/2007 or 2007-09-01)

• Leap Year Fix: The code automatically counts 366 days for leap years by calculating the days between January 1 of a year and January 1 of the next—no manual checks needed!
• Date Inclusivity: We add +1 to the DateDiff result because the function counts days between two dates, not including the end date. This ensures your start and end dates are both counted.
• Customize Output: If you don't want a new sheet, replace the Set outputSheet line with something like Set outputSheet = ThisWorkbook.Sheets("Your Existing Sheet Name")

Hope this works seamlessly for you! Let me know if you need help adjusting anything for your specific setup.

内容的提问来源于stack exchange，提问作者Julia T.