我有十二個月的資料（見圖片一），按日期-一月、日、小時、/日期-二月、日、小時/等...總共 36 列。
我正在嘗試將該作業表轉換為新的作業表報告樣式，它應該看起來是堆疊的（見圖 2），它應該有四列，按名稱（員工的）、日期、天、小時排列。
需要注意的幾點：
它必須是一個參考- 所以當我在 sheet2中自動更新sheet1 中的小時數時。沒有復制粘貼。(所以像 example(=b2)) （不需要顛倒）。
如果我嘗試每三列重申一次，請記住每個月都有不同的天數，我們不想要空行。
我正在考慮 vlookup 或索引功能，但似乎無法使其作業
uj5u.com熱心網友回復：
開發單個動態公式參考 (MS 365)
“它必須是一個參考 - 所以當我在 sheet2 中自動更新 sheet1 中的小時數更改時”
在確定預固定結構可以讓你的出路，以獲得想要的動態參考。該“退路”很簡單，就是
- 通過公式將日期值（col 2-Date 和 3-Day）重新計算為數字序列，而不是嘗試參考原始日期，
- 并包括所有需要的列結果作為第一步
=CHOOSE({1,2,3,4},Employee,dt,dt,hours)
作為以下步驟，您需要將所有需要的列引數包含到LET()定義所有列輸入的公式容器（）中，部分通過參考命名單元格，例如
Employee.. 例如"Bob Smith"）StartDate.. 例如 2021 年 4 月 1 日（此處等于44291）StartYear.. 例如 2021StartMonth.. 例如 4
該LET功能（在 MS 365 中可用）允許以結構化的方式執行此操作，同時也避免了一些冗余。
=LET(data,Sheet1!$C$6:$AL$36,dt,SEQUENCE(366,1,StartDate),hours,INDEX(data,DAY(dt),(MONTH(dt) (YEAR(dt)-StartYear)*12-StartMonth)*3 3),CHOOSE({1,2,3,4},Employee,dt,dt,hours))
通過將此公式輸入任何目標單元格（例如在 中Sheet2），您可以獲得四列的動態溢位范圍，并自動顯示原始小時數的變化。
提示：我也把Let考慮閏年的公式留給你來完善。
為了最終為計算出的日期序列（只是數字）獲得正確的報告布局，您必須使用所需的日期格式設定第二和第三輸出列的格式，例如"'m\/d"和"[$-409]ddd"。
配方部分概述
其中包括換行以提高可讀性
=LET(
data, Sheet1!$C$6:$AL$36,
dt, SEQUENCE(366,1,StartDate),
hours, INDEX(data,DAY(dt),(MONTH(dt) (YEAR(dt)-StartYear)*12-StartMonth)*3 3),
CHOOSE({1,2,3,4},Employee,dt,dt,hours)
)
uj5u.com熱心網友回復：
正如@PEH 在評論中正確提到的那樣，關于更改月份長度和您的要求“它必須是參考 - 所以當我在 sheet2 中自動更新 sheet1 中的小時數時，沒有“簡潔的解決方案”
沒有直接參考的 VBA Run-up
（參見 ?2nd 開發單個動態公式參考的帖子）
但是，由于31 x 36 資料單元格范圍的固定結構，您可以
- 提供具有 31*12行和 4列 （名稱、日期、天、小時）的報告陣列，
- 您填寫員工姓名 (col.1)，計算 日期（假設：字串！）范圍從 1 到最大值。31 天（第 2 和第 3 欄），以及從源代碼按列讀取的小時數
- 并寫回任何想要的目標。
示例呼叫
根據您的需要更改作業表指示。
Sub WriteReport()
'A) create report
Dim report As Variant
report = getReport("Bob Smith", ThisWorkbook.Worksheets("Sheet1"))
'B) write report to any wanted target
With Sheet2
.Range("A1".resize(1,4) = split("Name,Date,Day,Hours", ",")
.Range("A2").Resize(UBound(report), UBound(report, 2)) = report
End With
End Sub
幫助功能 getReport()
Function getReport(ByVal employee As String, _
SourceSheet As Worksheet, _
Optional StartYear As Long = 2021, _
Optional startMonth As Long = 4)
'0) get start dates for e.g. 12 months via help function getDates()
Const MonthsCount As Long = 12
Dim datearr: datearr = getDates(DateSerial(StartYear, startMonth, 1), MonthsCount)
'1) define source range
Dim rng As Range
Set rng = SourceSheet.Range("A6").Resize(31, 3 * MonthsCount)
'2) define 1-based 2-dim report array comprising 31 x 4 elements
Dim report
ReDim report(1 To MonthsCount * 31, 1 To 4)
'3) add calculated dates and add monthly hours to report array
Dim mth As Long, d As Long, cnt As Long
For mth = 1 To MonthsCount
'get monthly hours as 2-dim array (1 column each)
Dim monthlyHours: monthlyHours = rng.Columns(mth * 3 2).Value
For d = 1 To ultimo(datearr(mth))
cnt = cnt 1
report(cnt, 1) = employee
report(cnt, 2) = Application.Text(datearr(mth) d - 1, "'m\/d") ' force date string
report(cnt, 3) = Application.Text(datearr(mth) d - 1, "[$-409]ddd") ' force EN-US vers.
report(cnt, 4) = monthlyHours(d, 1)
Next d
Next
'4) return function result
getReport = report
End Function
幫助功能 getDates()
回傳每個月開始日期的一維陣列
Function getDates(dt As Date, Optional MonthsCount As Long = 12)
'Purpose: get 1-dim array of last 12 months dates
'a) get start date
Dim EndDate As Date: EndDate = DateAdd("m", MonthsCount, dt)
Dim yrs As Long: yrs = Year(EndDate) - Year(dt)
'b) get column numbers representing a months sequence
Dim cols As String
cols = Split(Cells(, Month(dt)).Address, "$")(1)
cols = cols & ":" & Split(Cells(, Month(EndDate) - 1 Abs(yrs * 12)).Address, "$")(1)
'c) evaluate dates
getDates = Evaluate("Date(" & Year(dt) & _
",Column(" & cols & "),1)")
End Function
幫助功能 ultimo()
計算給定月份日期的最后一天（范圍從 28 到 31）。
如果應用于下個月 (month 1) ，則可以使用零 ( 0) 作為理論日期輸入和函式中的最后一個引數getSerial()。
Function ultimo(ByVal dt) As Long
'Purp.: return last day of month
ultimo = Day(DateSerial(Year(dt), Month(dt) 1, 0))
End Function
uj5u.com熱心網友回復：
我最終使用了這個公式并且它奏效了。
firstDate = DateValue("4/1/2021")
secondDate = DateValue("4/1/2024")
n = DateDiff("d", firstDate, secondDate)
sc = Sheets.Count
scd = sc - 3
datar = "$A$1:$G$" & scd * n 1
For c = sc - 1 To 3 Step -1
q = Sheets(c).Name
Sheets("Report").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Formula2R1C1 = "=SEQUENCE(DAYS(""4/1/2024"",""4/1/2021""),,""4/1/2021"")"
Sheets("Report").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Resize(n).Formula2R1C1 = "=TEXT(INDIRECT(""RC[-1]"",0),""ddd"")"
Sheets("Report").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(n).Formula2R1C1 = Sheets(c).Range("$K$1")
Sheets("Report").Cells(Rows.Count, 1).End(xlUp).Offset(-n 1, 0).Resize(n).Select
Selection.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & q & "'" & "!$A$1"
Sheets("Report").Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Resize(n).Formula2R1C1 = "=INDEX(" & q & "!R6C3:R114C38,IF(LARGE((" & q & "!R6C3:R114C38=RC2)*ROW(" & q & "!R6C3:R114C38),1),LARGE((" & q & "!R6C3:R114C38=RC2)*ROW(" & q & "!R6C3:R114C38),1)-5,""""),IFERROR(MATCH(RC2,INDEX(" & q & "!R6C3:R114C38,IF(LARGE((" & q & "!R6C3:R114C38=RC2)*ROW(" & q & "!R6C3:R114C38),1),LARGE((" & q & "!R6C3:R114C38=RC2)*ROW(" & q & "!R6C3:R114C38),1)-5,""""),0),0),"""") 2)"
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/367853.html