引用与排序

quqiyuan

bown2022 发表于 2024-11-29 21:23
疑点：1.用定义名称的方法，b2从g8:g13取值，但有重复值；
      2.b2变化，自动从g8:k8中取值，用了loo ...

第一个问题，低版本无解，现在365可以去重

其他两个我重新做了下，仅供参考，。。。

zwzc

疑问1   我用的wps 并没有看到重复值。
疑问3  没看懂你的需求，你这不是基础数据吗，就应该是填写进去的？用函数从哪里取值？

bown2022

zwzc 发表于 2024-11-29 22:17
疑问1   我用的wps 并没有看到重复值。
疑问3  没看懂你的需求，你这不是基础数据吗，就应该是填写进去的 ...

为了规范基础数据并与成绩单选项一致，我想用公式做二级下拉菜单，用公式写了，没成功，所以用了数据有效性。求教公式方法生成二级菜单

bown2022

bown2022 发表于 2024-11-29 22:37
为了规范基础数据并与成绩单选项一致，我想用公式做二级下拉菜单，用公式写了，没成功，所以用了数据有 ...

简单点说就是根据a、b列，做二级下拉菜单，因为a有合并单元格，试了几次公式没成功。

quqiyuan

bown2022 发表于 2024-11-29 23:09
简单点说就是根据a、b列，做二级下拉菜单，因为a有合并单元格，试了几次公式没成功。

像你这种情况，低版本，数据又不是很规范，用vba应该是最好的办法。。。

bown2022

quqiyuan 发表于 2024-11-30 13:13
像你这种情况，低版本，数据又不是很规范，用vba应该是最好的办法。。。

感谢大神的提醒，我查了一下office版本为2021，这次用了一些新的函数。

空值的问题已经解决，同步取值的问题也解决。但是用了辅助列b，能不能不用，在FILTER函数时，处理不好合并单元格的问题所以用了辅助列。
求和的方法也不够智能化，想用sumif求语数外减违纪的分数，因合并单元格也没成功。
为了编辑方便，这次把数据区与成绩单放在一起，后期我想用剪贴的方式把数据区单独放一个表格中，但是会有引用错位，有没有修改的办法。
求教！！！
副本考核成绩单11292203疑问.zip (16.6 KB, 下载次数: 5)

2024-11-30 22:50 上传

点击文件名下载附件

quqiyuan

写了两个事件，专门用于点击单元格然后添加数据验证使用
合计由于有合并单元格，不要用sumifs，改用sumproduct函数。

quqiyuan

代码如下。。。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 Then
        Application.EnableEvents = False
        If Target.Column = 10 And Target.Row > 7 Then
            Set dic = CreateObject("scripting.dictionary")
            With Me
                r = .Cells(Rows.Count, 2).End(3).Row
                brr = .[a5].Resize(r - 4, 5)
                For i = 1 To UBound(brr)
                    If brr(i, 1) = Empty Then
                        brr(i, 1) = brr(i - 1, 1)
                    End If
                Next
                For i = 1 To UBound(brr)
                    If Not dic.exists(brr(i, 1)) Then
                        dic(brr(i, 1)) = brr(i, 2)
                    Else
                        dic(brr(i, 1)) = dic(brr(i, 1)) & "," & brr(i, 2)
                    End If
                Next
                With Target.Offset(, 1).Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                            xlBetween, Formula1:=dic(Target.Value)
                    .IgnoreBlank = True
                    .InCellDropdown = True
                End With
            End With
            If Target.Value = Empty Then
                Target.Offset(, 1).Value = Empty
                Target.Offset(, 1).Validation.Delete
            End If
        End If
        Application.EnableEvents = True
    End If
End Sub
   
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.ScreenUpdating = False
    If InStr(Target.Address, "$B$2") Then
        arr = Me.[i7].CurrentRegion
        Set d = CreateObject("scripting.dictionary")
        For i = 2 To UBound(arr)
            If arr(i, 1) <> Empty Then
                d(arr(i, 1)) = ""
            End If
        Next
        With Me
            With .Cells(2, 2).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:=Join(d.keys, ",")
                .IgnoreBlank = True
                .InCellDropdown = True
            End With
        End With
    End If
    If Target.Count = 1 Then
        If Target.Column = 10 And Target.Row > 7 Then
            If Target.Offset(, -1) <> Empty Then
                Set dic = CreateObject("scripting.dictionary")
                With Me
                    r = .Cells(Rows.Count, 2).End(3).Row
                    brr = .[a5].Resize(r - 4, 5)
                    For i = 1 To UBound(brr)
                        If brr(i, 1) = Empty Then
                            brr(i, 1) = brr(i - 1, 1)
                        End If
                    Next
                    For i = 1 To UBound(brr)
                        If Not dic.exists(brr(i, 1)) Then
                            dic(brr(i, 1)) = brr(i, 2)
                        Else
                            dic(brr(i, 1)) = dic(brr(i, 1)) & "," & brr(i, 2)
                        End If
                    Next
                    With Target.Validation
                        .Delete
                        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                                xlBetween, Formula1:=Join(dic.keys, ",")
                        .IgnoreBlank = True
                        .InCellDropdown = True
                    End With
                End With
            Else
                Target.Validation.Delete
            End If
        End If
    End If
    Application.ScreenUpdating = True
End Sub

bown2022

quqiyuan 发表于 2024-12-1 10:21
代码如下。。。

Private Sub Worksheet_Change(ByVal Target As Range)

非常感谢，但我对vba不太懂，能否在公式上简便一下算法，取消辅助列！

quqiyuan

bown2022 发表于 2024-12-1 12:14
非常感谢，但我对vba不太懂，能否在公式上简便一下算法，取消辅助列！

不要vba ，你这个数据不要辅助列估计还不得行。。。。