主页 / coding / 问题

问题[excel](coding)

Martin Hope
Artybunny
Asked: 2025-04-30 23:30:05 +0800 CST
  • 6

我对 VBA 还不太熟悉，所以很难解决这个问题……我有一个用户窗体，其中包含各种输入，包括一些使用 Excel 工作表列表的组合框。我想要做的是，如果用户向该组合框添加了一个新值，则在保存表单时，它应该检查列表，如果该值不存在，则将其添加到列表末尾。这是我目前为止尝试根据在线找到的内容整理出来的（一旦我成功完成，我将把它放入循环中以覆盖多个组合框）：

Dim ws As Worksheet: Set ws = Sheets("Lists")
Dim EmptyRow As Long
Dim FoundVal As Range
EmptyRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row + 1
If CoBJobList1.ListIndex > -1 Then
    Set FoundVal = ws.Range("D2:D" & EmptyRow).Find(CoBJobList1.Value)
    If Not FoundVal Is Nothing Then
       'Do Nothing
    Else
       ws.Range("D" & EmptyRow).Value = CoBJobList1.Value
    End If
End If
excel
Martin Hope
ffc2004
Asked: 2025-04-30 17:20:37 +0800 CST
  • 6

我有下面的代码，我设法将它们拼凑在一起，将一张表中的行转置到另一张表中我需要的一行上 - 我现在遇到的问题是，当它从表中复制时，它也会复制表格式。

我不确定如何粘贴所有值而不是直接复制/粘贴。

Sub transpLeagueTable()
Dim MyTime, MyDate, MyStr
targetrow = 6
For i = 8 To ActiveSheet.Range("A" & Rows.Count).End(xlUp).row
    ActiveSheet.Range("A" & i & ":A" & i & ",C" & i & ":J" & i).Copy Worksheets("League Table CSV").Cells(targetrow, 2 + (i - 8) * 9)
Next i
Columns("A:A").EntireColumn.Hidden = False
Application.GoTo Sheets("League Table CSV").Range("A6"), True

Columns("A:A").EntireColumn.Hidden = True

Application.CutCopyMode = False
Application.GoTo Sheets("League Table CSV").Range("B6"), True

End Sub

我认为它是 .Copy 部分，但我不确定如何去改变它。

excel
Martin Hope
nightfire36
Asked: 2025-04-30 00:02:48 +0800 CST
  • 7

我一直在会计部门帮忙，他们会给加班的人发放额外工时。如果你在某个班次连续工作了5天或7天，除了你实际工作的工时外，还会额外获得一些额外工时。所以，你可能会得到5*10的额外工时，再加上4小时，总共54小时，而不是50小时。

问题在于，所使用的电子表格会跟踪每个月的班次，并将天数加在一起。例如，7月份，一个人工作了5天，又工作了7天，所以他7月份的单元格会被标记为“=5+7”。我们需要知道该班次的天数，以及全年应奖励的奖励小时数。

我知道有更好的方法来计算这个问题，但我们还是用老旧的电子表格，因为会计部门就是这么想的。有没有办法统计单元格包含 7 或 5 的次数，然后把它们加到新的一列？

对于一名员工来说，给定的行可能看起来像这样：

七月 八月 九月
=5+7 5 = 7+7

因此，对于这名员工，我需要三列来输出总天数（31）、连续 5 天轮班的数量（2）和连续 7 天轮班的数量（3）。

他们目前是手动操作的，但对于100多名员工来说，手动操作太繁琐，而且容易出错。他们也明确表示希望在每个单元格中都这样计算，所以我没办法解决这个问题。我试过用单元格函数，但不知道它能否同时处理多个单元格。我想我可以做一个countif，然后用单元格函数来查看所有12个月的数据，但这似乎不太优雅。

excel
Martin Hope
nickC
Asked: 2025-04-29 23:16:16 +0800 CST
  • 7

有没有办法复制粘贴到单元格注释中？只能从剪贴板复制粘贴，不能从单元格或区域引用复制粘贴。或者有没有办法用 VBA 调用剪贴板中的信息然后粘贴？

Sub fillcomment()
    
    Sheets("Calendar").Select
    Application.Goto Reference:=Range(Range("Calendar!calendarRef").value)
    Application.CutCopyMode = False
    
    Sheets("FORMULAS").Select  'Surname
     Range("C8").Select
    Selection.Copy
    
    Sheets("Calendar").Select  'paste surname
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
      Application.CutCopyMode = False
   
  
    'select and paste comment
        Sheets("Calendar").Select
        ActiveCell.ClearComments
        ActiveCell.AddComment
        ActiveCell.Comment.Visible = False
        ActiveCell.Comment.Text Text:=Range(Range("FORMULAS!calcom").value)

End Sub

我需要来自名为“calcom”的范围的变量信息，或者如果更容易的话，从 B6 中的连接单元格中获取变量信息并复制粘贴到注释中

excel
Martin Hope
Dorkhan C.
Asked: 2025-04-29 08:15:33 +0800 CST
  • 8
年 一个 b c
2017 0 1 3
2018 0 3 0
2019 0 0 0

鉴于上面的表格，有什么 Excel 公式可以帮助我计算出有多少列至少有一个单元格大于 1？我希望有一个公式，即使有 1000 列也能用。

在这种情况下，答案应该是 2（b 列和 c 列）。

我尝试使用 COUNTIFS 和 SUM 的不同组合，但尚未找到正确的组合。到目前为止，我已经尝试过

=SUM(--(MAX(B2:B4)>1), --(MAX(C2:C4)>1), --(MAX(D2:D4)>1))

这是可行的，但是对于 1000 列来说就不可行了。

excel
Martin Hope
SCM
Asked: 2025-04-28 14:00:12 +0800 CST
  • 6

我有一列日期格式为 dd-mm-yyyy。

我尝试用“.”替换“-”，这样选定的所有日期看起来都像 01.01.2025，而不是 01-01-2025。

当我在 Excel 表中使用 ctrl+H 时它可以工作，但记录的 VBA 代码不起作用。

Selection.Replace What:="-", Replacement:=".", LookAt:=xlPart, _
  SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
  ReplaceFormat:=False
excel
Martin Hope
Shiela
Asked: 2025-04-26 20:28:13 +0800 CST
  • 6

我这里有一个列表框，可以与下面的代码一起正常工作。

列表框1

列表框

更新了整个代码：

Option Explicit
Dim bInit As Boolean ' ** module-scoped variable

Private Sub UserForm_Initialize()
    bInit = True  ' ** set UserForm_Initialize mode **from Taller
    clearAll
    Me.cmbName.Value = "Nory"
    Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
    Dim i As Long
    Dim arr: arr = ws.Range("B1").CurrentRegion.Value
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")

    For i = 2 To UBound(arr)
        dict(arr(i, 2)) = arr(i, 1)
    Next
   ' ***
     If dict.exists(Me.cmbName.Value) Then
        Me.cmbTeam.Value = dict(Me.cmbName.Value)
        If Not cmbTeam.Value = "" And Not cmbDate.Value = "" And Not cmbName.Value = "" Then
            Team
            forListBoxUpdate 'calling forListBoxUpdate
            forDateCombobox 'filling in date dropdowns
            forNamesCombobox 'filling in names combobox as long as 3 comboboxes are not blank
        Else
            Team 'calling Team dropdowns in case team is blank or default cmbName value has no team
            cmbDate.Value = ""
            cmbName.Value = ""
        End If
    Else
            Team 'calling Team dropdowns in case team is blank or default cmbName value has no team
            cmbDate.Value = ""
            cmbName.Value = ""
    End If
    bInit = False  ' ** reset **from Taller
End Sub

Private Sub cmbDate_Change()
    If Not cmbTeam = "" And Not cmbName = "" Then
        forListBoxUpdate 'calling to show info if team and name not blank
        forNamesCombobox 'filling in cmbname dropdowns
    Else
        cmbDate.Clear 'if cmbteam and cmbname blank, cmdate should also be blank
    End If
End Sub

Private Sub cmbName_Change()
    forListBoxUpdate
End Sub

Private Sub cmbTeam_Change()
    cmbDate.Value = ""
    'cmbName.Value = "" 'issue, during initialize, default cmbname is removed.
    clearAll 'used this instead
    forDateCombobox 'fills in date dropdowns
End Sub



Sub forListBoxUpdate() 'to show info from sheets and to be called after the 3 comboboxes are filled
    Dim ws As Worksheet, colList As Collection
    Dim arrData, arrList, i As Long, j As Long
    
    Set colList = New Collection
    Set ws = Worksheets("Sheet2")
    arrData = ws.Range("A1:C" & ws.Cells(ws.Rows.count, "A").End(xlUp).Row)
    
        For i = 2 To UBound(arrData)
            If Format(arrData(i, 1), "mmmm yyyy") = Me.cmbDate.Value And arrData(i, 3) = Me.cmbName.Value Then
                colList.Add i, CStr(i)
            End If
    Next

    ReDim arrList(1 To colList.count + 1, 1 To UBound(arrData))
    For j = 1 To 3
        arrList(1, j) = arrData(1, j) ' header
        For i = 1 To colList.count
                arrList(i + 1, j) = arrData(colList(i), j)
        Next
    Next

    With Me.ListBox1
        .Clear
        .ColumnCount = UBound(arrData, 2)
        .list = arrList
    End With
    
    
    labelCount.Caption = ListBox1.ListCount - 1
End Sub

Sub clearAll() 'to clear comboboxes except teams
    If Not bInit Then cmbName.Value = ""  ' ** doesn't run when calling from UserForm_Initialize **from Taller
    cmbDate.Clear
    cmbName.Clear
    'cmbName.Value = ""
    ListBox1.Clear
End Sub

Sub Team() 'for adding the teams dropdown in cmbTeam
    clearAll
    Dim ws As Worksheet, _
        Dic As Object, _
        rCell As Range, _
        Key
    
    Set ws = Worksheets("Sheet1")
    Set Dic = CreateObject("Scripting.Dictionary")
    
    
    For Each rCell In ws.Range("A2", ws.Cells(Rows.count, "A").End(xlUp))
            If Not Dic.exists(rCell.Value) And Not rCell = "" Then
                Dic.Add rCell.Value, Nothing
            End If
    Next rCell
    
    For Each Key In Dic
        cmbTeam.AddItem Key
    Next
End Sub

Sub forNamesCombobox() 'for adding the names dropdown in cmbName
Dim ws As Worksheet, _
    Dic As Object, _
    rCell As Range, _
    Key

Set ws = Worksheets("Sheet1")
Set Dic = CreateObject("Scripting.Dictionary")


For Each rCell In ws.Range("B2", ws.Cells(Rows.count, "B").End(xlUp))
        If Not Dic.exists(rCell.Value) And rCell.Offset(0, -1) = cmbTeam.Value Then
            Dic.Add rCell.Value, Nothing
        End If
Next rCell

For Each Key In Dic
    cmbName.AddItem Key
Next
End Sub

Sub forDateCombobox() 'for adding the date dropdown in cmbDate
            Dim date1 As Variant
            Dim date2 As Variant
            date1 = Format(Now, "mmmm yyyy")
            date2 = Format(DateAdd("m", -1, CDate(date1)), "mmmm yyyy")
            
            With cmbDate
            .Clear
            .AddItem Format(date2, "mmmm yyyy")
            .AddItem Format(date1, "mmmm yyyy")
            .Value = Format(date1, "mmmm yyyy")
            End With
            
End Sub

工作表1

一个 B
团队 名字
鹦鹉 莉娜
乔治 诺里
乔治 最大限度
杰克 担

工作表2

一个 B 碳
日期 ID 名字
2025年3月25日 1101 莉娜
2025年4月25日 1102 莉娜
2025年3月25日 1103 诺里
2025年4月25日 1104 诺里
2025年3月25日 1105 担
2025年4月25日 1106 担

现在，在团队组合框的更改事件期间，我希望清除名称组合框（“Nory”或任何值cmbName都应被删除并消失）。

从上面的代码中，团队变更事件片段是：

Private Sub cmbTeam_Change()
    cmbDate.Value = ""
    'cmbName.Value = "" 'issue, during initialize, default cmbname is removed.
    clearAll 'used this instead
    forDateCombobox 'fills in date dropdowns
End Sub

Sub clearAll() 'to clear comboboxes except teams
    If Not bInit Then cmbName.Value = ""  ' ** doesn't run when calling from UserForm_Initialize **from Taller
    cmbDate.Clear
    cmbName.Clear
    'cmbName.Value = ""
    ListBox1.Clear
End Sub

即使我将在初始化期间插入cmbName.Value = ""到 sub 中clearAll，"Nory"也会被删除，但我不希望它在初始化时被删除。

如何修复上述代码，在初始化期间，“Nory”将保留，团队和日期也将保留，而当团队发生变化时，日期和姓名组合框将为空白。

非常感谢您的帮助。

excel
Martin Hope
Shiela
Asked: 2025-04-25 20:11:13 +0800 CST
  • 5

工作表1

Sheet1 图片

初始化过程中出错：

错误

如果团队成员在第 2 列匹配，则应修复以下代码以获取 Me.cmbTeam.Value（组合框）中填充的准确团队名称。

Private Sub UserForm_Initialize()
    Me.cmbDev.Value = "Nory"

    Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
    Dim i As Long
    Dim arr: arr = ws.Range("B1").CurrentRegion.Value
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    Dim teamName As Variant

    For i = 2 To UBound(arr)
        dict(arr(i, 2)) = Empty
    Next
   ' ***
   
    If dict.exists(Me.cmbDev.Value) Then
        teamName = Application.VLookup(Me.cmbDev.Value, ws.Range("B1").CurrentRegion.Value, 1, False)
        Me.cmbTeam.Value = teamName 'should get a result of George
    Else
        Me.cmbDev.Value = ""
        Me.cmbTeam.Value = ""
    End If

End Sub
excel
Martin Hope
Martin Brown
Asked: 2025-04-25 19:01:52 +0800 CST
  • 5

Excel 有时会直接拒绝正确创建 XY 散点图。它默认采用一种愚蠢的格式，将行绘制成带有图例“系列 1”、“系列 2”和“系列 3”的线条，尽管它标识了列标题并将其粘贴在 X 轴上！它本应绘制成点与直线连接的 XY 散点图（也就是我们选择的那种），但却无法绘制。

我偶尔看到的问题似乎可能与之前关于XY散点图的帖子有关，其中X轴实际上是时间格式的时间。但我看不出这有什么理由适用于这里。数据显然都是整数。

我知道如果标题或数据格式错误（包含 Nan、Inf 或空格），就会发生这种情况（这很正常）。但有时，当输入完全有效的数值数据时，它会无缘无故地出现异常。这是我今天发现的一个特别小的例子，简化成 MRE 后仍然失败。通常情况下，MRE 的列和行要多得多。以下是 CSV 文件格式：

log2N,N,valid,solutions
3,8,35,10
4,16,1365,119
5,32,31465,852

如果你把这个看似无害的数据集复制粘贴到Excel中，选中它，然后尝试绘制N、有效值和解与Log2N的XY图，它只会画出一个疯狂无用的图。事实上，只有当你选择A列和B列时，它才会绘制正确的XY散点图Log2N。N一旦建立了正确的XY图格式，它就会很乐意扩展数据范围。选择绘图数据并进行编辑"=Sheet1!$A$1:$B$4"，"=Sheet1!$A$1:$D$4"格式仍然保持为XY，但这很麻烦。

在此处输入图片描述

我正在使用 Excel 2021 MS Office Pro。它的 ID 为：Microsoft® Excel® 2021 MSO（版本 2503，内部版本 16.0.18623.20178）64 位

自2007年以来，我在所有最新版本的Excel中都看到了这种现象。这只是我第一次发现一个小型MRE如此清晰地表现出这种现象。我认为这是Excel对动态范围触发的数据假设的一个错误，但如果有人能找到解决这个问题的方法，使绘制XY散点图能够正确地处理任何有效数值数据，并将第一列视为X轴，第一行视为图线的图例，我将不胜感激。

或者如果它是可重现的并且是一个错误，如何以某种方式将其报告给 MS 以便修复它。

虽然我确实有解决方法，但我更希望能够永久修复。如果我的数据中存在任何细微的格式错误，而我却忽略了，请指出。谢谢。

excel
Martin Hope
Ben
Asked: 2025-04-25 11:35:24 +0800 CST
  • 10

我有一张表，我想根据第三列的值来计算两列中值的出现次数，并且为了便于阅读，我希望能够看到旁边带有计数的唯一值。

例如：

人员1 Person2 数字
账单 戴夫 2
戴夫 约翰 2
约翰 账单 1

我想根据第三列的值来查看每个人的名字出现了多少次。由于这是一个流动文档，所以唯一需要更新的列是这三列，因此我需要创建一个新的列，其中包含 A 和 B 这两个唯一值，如果 C 的值 = 2

到目前为止我想到的是：

=UNIQUE(VSTACK(A2:A100,B2:B100))

效果很好，但接下来根据 C 的值计算出现次数，我遇到了麻烦。我试过

=COUNTIFS(A2:B100,E2,C2:C100,">1")

但它总是出现“值是错误的数据类型。我已确保列是正确的类型，例如 C 是一个数字，并且将 UNIQUE 函数中的值复制到单独的列中，这样它就不会尝试堆叠函数。

我尝试过其他类似的功能，COUNT(UNIQUE(FILTER())但这并不能消除无效计数，还有另一种解决方案，我不知道它是否有效，因为我无法理解该功能：

=SUM(--(FREQUENCY(IF(C2:C18=2,MATCH(A2:A18,B2:B18,0)),ROW(A2:B18)-ROW(A2)+1)>1))
excel