精华内容
下载资源
问答
  • ?点击关注Excel表哥公众号上期我们分享了Excel联想式下拉菜单...先看最终的效果动图:今日分享难度指数:★★★★☆01创建辅助列在需要设置下拉列表的工作表中创建一列辅助列,如下图所示:在辅助列F3单元格输入下...

    ?点击关注Excel表哥公众号

    上期我们分享了Excel联想式下拉菜单制作教程,可以简化大家在下拉菜单过长时的数据录入工作。

    e024c481213b35262e5870a68532b54b.png

    文章末尾给大家提出了一个问题:为什么下拉列表数据源需要先排序?

    今天我们继续分享一个不用对数据源排序也能实现的下拉列表制作方法。

    先看最终的效果动图:

    93174854d9de75a5c5ff2820a616052f.gif

    今日分享难度指数:★★★★☆

    01创建辅助列在需要设置下拉列表的工作表中创建一列辅助列,如下图所示:

    20eb2c4b11e90ffd906cc3ead98a6622.png

    在辅助列F3单元格输入下面的公式:
    =IFERROR(INDEX(数据源!$B$3:$B$999,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),数据源!$B$3:$B$999),数据源!$B$3:$B$999,""),数据源!$B$3:$B$999,0),2^16),ROW(B1))),"")

    ▎注意:这是一个数组公式,输入完成后需要按Ctrl+Shift+Enter三键完成数组公式的设置。

    公式三键设置结束后,会弹出循环引用的提示,忽视即可。0c54f0fc3aa8cfecb26bbe0a33a845a8.png

    这个公式似乎有点长啊!不过公式虽长,我们却可以通过定义名称的形式进行缩减。

    f7790b71684fd6264207222d93e49170.png

    然后我们的公式就变短了:

    =IFERROR(INDEX(List,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),List),List,""),List,0),2^16),ROW(B1))),"")
    后面如果List数据源位置调整了在名称管理器里修改也是比较简单的。同时通过名称管理器的定义也可以方便其他使用者直接套用此公式。将F3中的公式向下填充至足够多行即可完成辅助列的设置。公式设置完成后我们先来测试一下:

    ce3bd6729b31b383036b58234b123215.gif

    通过测试我们发现在除了辅助列之外的任意单元格输入关键词,F列中的查找内容就会根据这些关键词进行自动更新,而这正是我们需要实现的效果。02设置数据有效性公式选中需要设置数据下拉列表的区域,如举例中的B3:B10,按照下图设置数据录入工作表的数据有效性。

    04e3175818afc0fe2e0e3686860459af.png

    至此,所有设置均已完成。最终的联想式下拉列表如文章开头的动图所示。通过测试我们发现使用这种方法设置的下拉列表不仅不需要提前对数据源进行排序而且关键词还不用区分大小写,可以说是比较方便了。03核心公式解释实现联想式输入下拉列表的核心在于此公式:
    =IFERROR(INDEX(List,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),List),List,""),List,0),2^16),ROW(B1))),"")
    CELL("contents")函数的作用是可以获取最后编辑的单元格内容,即我们要搜索的动态关键词。在之前的这篇文章我们有讲到过CELL函数,戳此复习▼为什么说聚光灯是练习单元格引用方式最好的教材此处为了理解方便,我们将CELL("contents")换成B3单元格。整个公式的设置其实还是一个模糊查找问题。由于公式嵌套较多,且比较复杂,我们通过辅助列来加深理解:

    e828c1a615e66c742f93aaf266695b28.png

    • FIND函数返回输入的字符$B$3在数据源!$B$3:$B$999中每一行字符的起始位置。

    • IF函数如果匹配到数据则返回数据源中该行数据,否则返回空""(,""也可省略)。

    • MATCH函数根据IF函数的结果返回在数据源中相应的行号,如果未匹配到$B$3中的关键字则返回#VALUE!错误值。

    • IFERROR函数则将#VALUE!错误值修改为一个极大值2^16=65536。

    • SMALL函数根据J列查找出来的行号进行排序。由于SMALL函数不支持错误值,这也是上一步必须用IFERROR函数修正MATCH返回结果的原因。

    • INDEX函数根据SMALL函数返回的符合项从小到大的行号查找出所有的包含关键词的选项。

    • 最后再次通过IFERROR函数屏蔽#REF!引用错误。

    至此,终于完成了核心公式的设置。

    最后我们把查找关键词替换为CELL("contents")就可以实现任意位置(除了辅助列)输入字符,在F列匹配出数据源中所有包含该关键词的符合项了。

    至于数据验证中OFFSET函数就很好理解了,和上期分享的方法一致此处就不再赘述了。今天分享的公式理解起来比较困难,大家可以通过构造多列辅助列的方式进行学习掌握。同时也可以下载表哥提供的模板文件进行对照学习。有疑问欢迎在文末留言区进行提问交流。我是分隔线然写了2千字的教程,然而WPS用户的读者朋友看了后却一脸嫌弃,难道这种联想式输入列表不应该是软件自带的吗?

    5b3ccb1bb9955c89e3755b32b69c7936.gif

    仔细看,WPS确实自带联想式输入技能!

    不得不说这是Excel用户非常羡慕的一个功能。那么恰好看到表哥这篇文章的Excel用户的读者朋友,希望能弥补这个小遗憾。

    WPS在使用细节上面确实够人性化,类似的功能还有根据内容批量合并某一列相同单元格,Excel用户则需要借助VBA才能实现,表哥的文章也有介绍过哦:一键批量合并Excel工作表相同内容单元格需要此联想式Excel下拉菜单制作模板的朋友可以关注Excel表哥公众号并在下方的留言区获取后台下载关键词哦~历史文章

    [01] 自动扩展的下拉菜单,你会制作吗?

    [02] 手把手教你制作Excel联想式下拉菜单,建议收藏

    注:本公众号所载原创文章均为作者辛苦创作,转载请联系作者并标明出处。处处留心皆学问,建议大家可以将这篇推文收藏,以备不时之需。处处留心皆学问,建议大家将这篇推文收藏,以备不时之需。

    你点的每个"在看"我都认真当成了喜欢▼ 4c51986fcb100b283e361b6b21c672d9.png

    展开全文
  • 今天咱们聊的内容是单元格的数据有效性(2010版本后更名为数据验证),在EH论坛上,经常碰到网友提问下面类似的问题:如何创建去除重复项后的下拉列表?什么意思?举个小栗子。如下图所示,D列是一些人名,含有重复项...
    嗨,大家好,我是星光。今天咱们聊的内容是单元格的数据有效性(2010版本后更名为数据验证),在EH论坛上,经常碰到网友提问下面类似的问题:如何创建去除重复项后的下拉列表?什么意思?举个小栗子。如下图所示,D列是一些人名,含有重复项。现在需要根据D列的人名,在表格的A列创建去除重复人名后的数据验证下拉列表。cc64ba307dae9a6289720309158a2e95.png
    动画效果:
    1d0951a991e09df0cef51e83708298e6.gif
    代码如下:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Intersect([a:a], Target) Is Nothing Then Exit Sub
    '如果选择的单元格不存在于A列,则退出。A列是设置数据验证的区域
        If Target.Rows.Count > 1 Then Exit Sub
        '不允许选择多行
        Dim arr, brr, i&, j&, k&, s
        Dim d As Object
        Set d = CreateObject("scripting.dictionary")
        '后期绑定字典
        arr = Range("d1:d" & Cells(Rows.Count, "d").End(xlUp).Row)
        '数据来源列
        If Not IsArray(arr) Then Exit Sub
        '如果不存在数据源选项,则arr非数组,那么退出程序
        For i = 2 To UBound(arr)
        'D1是标题,从第2行开始遍历数据源,将人名装入字典
            If arr(i, 1) <> "" Then d(arr(i, 1)) = ""
        Next
        s = Join(d.keys, ",")
        With Target.Validation
            .Delete '删掉旧的
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:=s
            's为数据验证的序列来源
        End With
        Application.SendKeys "%{down}"
        'SendKeys发出快捷键atl+↓直接弹出数据验证下拉列表
        Set d = Nothing
        '释放字典内存
    End Sub

    小贴士:

    1,代码需要粘贴在相关工作表标签所对应的VBE窗口中。2,代码使用了Worksheet_SelectionChange事件,当鼠标点击A列单元格时,系统自动运行该段代码。可以通过修改Intersect([a:a], Target)中的[a:a],设置为其它目标区域。3,代码使用了 Application.SendKeys "%{down}"语句,其意思是键盘输入快捷键alt+↓,自动打开数据验证下拉选项。该快捷键可能会和电脑的其它热键冲突,该语句并不是必须的,因此部分亲们可以注释掉它。4,握爪~下期再见~

    更多资源和教程

    • 零基础学SQL in Excel 25篇合集

    • VBA爱好者请进,VBA代码宝概述


    ©EH看见星光

    ac37b23e003f37d908c2c6075db4283e.png

    《VBA经典代码应用大全》当当、天猫、京东均有销售~
    展开全文
  • 我们今天聊的内容是单元格的数据有效性(2010版本后更名为数据验证),在EH论坛上,星光经常碰到网友提问下面酱紫的问题:如何创建去除重复项后的下拉列表?举个小栗子。如下图所示,D列是一些人名,含有重复项。现在...

    我们今天聊的内容是单元格的数据有效性(2010版本后更名为数据验证),在EH论坛上,星光经常碰到网友提问下面酱紫的问题:

    如何创建去除重复项后的下拉列表?

    举个小栗子。

    如下图所示,D列是一些人名,含有重复项。

    现在需要根据D列的人名,在表格的A列创建去除重复人名后的数据验证下拉列表。

    ca30c6903fe73e579c47282d37a6c97e.png

    动画效果:

    f2ec848d4bf3271b2c817ad9a48dde1b.gif

    代码如下:


    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

        If Intersect([a:a], Target) Is Nothing Then Exit Sub

        '如果选择的单元格不存在于A列,则退出。A列是设置数据验证的区域

        If Target.Rows.Count > 1 Then Exit Sub '不允许选择多行

        Dim arr, brr, i&, j&, k&, s

        Dim d As Object

        Set d = CreateObject("scripting.dictionary") '后期字典

        arr = Range("d1:d" & Cells(Rows.Count, "d").End(xlUp).Row)'数据来源列

        If Not IsArray(arr) Then Exit Sub

        '如果不存在数据源选项,则arr非数组,那么退出程序

        For i = 2 To UBound(arr)

        'D1是标题,从第2行开始遍历数据源,将人名装入字典

            If arr(i, 1) <> "" Then d(arr(i, 1)) = ""

        Next

        s = Join(d.keys, ",")

        With Target.Validation

            .Delete'删掉旧的

            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _

            Operator:=xlBetween, Formula1:=s 'S为数据验证的序列来源

        End With

        Application.SendKeys "%{down}"

        'SendKeys发出快捷键atl+↓直接弹出数据验证下拉列表

        Set d = Nothing '释放字典

    End Sub


    小贴士:

    1,代码需要粘贴在相关工作表标签所对应的VBE窗口中。

    2,代码使用了Worksheet_SelectionChange事件,当鼠标点击A列单元格时,系统自动运行该段代码。可以通过修改Intersect([a:a], Target)中的[a:a],设置为其它目标区域。

    3,代码使用了 Application.SendKeys "%{down}"语句,其意思是键盘输入快捷键alt+↓,该快捷键可能会和电脑的其它热键冲突,该语句并不是必须的,因此部分亲们可以注释掉它。

    图文作者:看见星光

    58d2a70e56c9bacaee7b8b5672a1b1b6.png

    VBA编程学习与实践

    点击图标进入ExcelHome云课堂,发现更多精彩课程

    3b84f16a62397fc3aeb2f9e70b8652a8.png

    专业的职场技能充电站

    展开全文
  • VBA 中COMBOBOX下拉列表的收起

    千次阅读 2015-04-30 16:07:48
    在使用VBA的COMBOBOX组件的时候,想制作一个点击单元格即自己弹出下拉列表,点击其它单元格更改COMBOBOX的位置,同样的弹出下拉列表的功能,遇到了这样的一个问题: COMBOBOX提供了DROPDOWN的方法,但是却没有提供...

    <span style="background-color: rgb(255, 255, 255);"></span><pre name="code" class="vb"><span style="font-size: 18px; font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);">在使用VBA的COMBOBOX组件的时候,想制作一个点击单元格即自己弹出下拉列表,点击其它单元格,即收起列表的功能。</span>
    
    

    COMBOBOX提供了DROPDOWN的方法,但是却没有提供收起列表的方法,所以如果你点击了第一个单元格,自动弹出列表,并且你没有选择其中任何一项的时候,再点击第二个单元格,下拉列表是不会自己收起来的……

    在网上也搜了很多的解决办法,包括mousemove方法和sendkeys方法的联合使用,或者定义全局变量,判断前后两次点击的单元格是否是同一个单元格等等,都没能完全的解决问题,后经自己尝试,找到了下面的办法,特记录如下:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    With ComboBox1
    .Visible = False
        If Target.Count = 1 Then
            If Target.Column = 2 Then
                .Visible = True
                .Width = Target.Width + 15
                .Left = Target.Left + Target.Width
                .Top = Target.Top
                .Height = Target.Height
                .Activate
                Target.Activate
                .DropDown
            Else
                .Visible = False
            End If
        End If
    End With
    
    End Sub
     解决办法实际就是运用activate这个方法,因为在实际中发现,COMBOBOX组件实际是被分离了两个部分,分别是下拉按钮和下拉列表,所以就算你把VISIBLE调成false,也只隐藏了下拉按钮,列表还在。

    所以直接就不隐藏,让它整个组件处于活动状态,那么当你点击不同的单元格的时候,就会随着你的点击更新到对应的位置。这里先将控件activate,整个组件将会被更新到当前位置,然后将点击的单元格更新,再将列表弹出来。


    展开全文
  • Excel利用VBA实现下拉列表,同时支持输入时动态查询,根据输入的不同实现动态的查询 先看一下实验效果: 当点击website这一列时会出现所有的网站列表,双击可点击选择数值填入 输入关键字时会只出现包含关键字的...
  • 一级下拉列表初级下拉列表方法一:首先,在Excel中选中需要添加下拉菜单的所有单元格,然后数据-数据工具-数据验证,在允许中设为序列,填入需要加入的下拉列表的值,确定即可搞定。注意填入列表是值之间需要用英文...
  • 希望在excel单元格中输入关键字后列出所有含有该关键字的相关列表供快速选择。要实现上述功能,需要解决以下几个问题:所有的列表项需要集合在数组或者单元格区域中;在要实现的单元格区域中触发Worksheet_...
  • Private Sub Worksheet_... '用SendKeys语句发送快捷键【ALT+方向键向下】,调取单元格下拉列表  If Target.Address = "$H$2" Then Application.SendKeys "%{down}"  End Sub  ...
  • ——本文不介绍使用"数据有效性"设置下拉列表,而是要挑点"数据有效性"设置下拉列表的小刺:① 不点击单元格不知道哪个单元格可以下拉输入;② 跨工作表引用时要先定义名称才能引用,不能直接引用;③ 通过复制...
  • 经常看到别人做一些比较好用的EXECL表,其中一个就是在表中提供一些下拉列表供选择使用,但又不是VBA等实现的。其实做起来很简单,说完你就会。 1、选择要加入此下拉列表单元格或列或行; 2、选择菜单“数据->...
  • 学习Excel技术,关注微信公众号:excelperfect在网站中,我们经常会碰到需要一层层向下选择的情况。在第一个选项列表中选择第一个数据,第二个选项列表中选择第二个数据,...第1列单元格中的值分别是该列下方数...
  • VBA

    2017-05-24 08:47:00
    下拉列表:选中要处理的单元格>>数据>>数据验证(数据有效性)>>序列>>输入示例:1,2,3 function和sub最大区别在两点 1.function可以返回值,sub则不可以返回值 2.sub可以直接执行,但function...
  • VBA笔记

    2019-06-22 09:32:54
    1. 在VBA中打开某个特定的Excel文件,并将其赋给某个变量ws: Dim wb As Workbook, ws As Worksheet Set wb = Workbooks....2. 生成一个下拉列表,并根据选中的内容,改变指定单元格或区域的显示内容: 第一...
  • EXCEL VBA常用技巧

    2010-02-09 22:28:27
    12-4 自动展开数据有效性下拉列表 36 技巧13 单元格中的公式 37 13-1 在单元格中写入公式 37 13-2 检查单元格是否含有公式 38 13-3 判断单元格公式是否存在错误 39 13-4 取得单元格中公式的引用单元格 40 13-5 将...
  • VBA常用技巧

    2014-12-21 16:39:28
    12-4 自动展开数据有效性下拉列表 12 技巧13 单元格中的公式 12 13-1 在单元格中写入公式 12 13-2 检查单元格是否含有公式 12 13-3 判断单元格公式是否存在错误 12 13-4 取得单元格中公式的引用单元格 12 13-5 将...
  • VBA事件(十七)

    2019-09-28 00:14:49
    VBA中,要手动更改单元格单元格值范围时,可以触发事件驱动的编程。 更改事件可能会使事情变得更容易,但您可以非常快速地结束一个完整的格式化页面。...用户可以选择这些工作表中的每一个,并从下拉列表中...
  • VBA编程技巧大全

    2013-08-05 09:03:19
    12-4 自动展开数据有效性下拉列表 43 技巧13 单元格中的公式 44 13-1 在单元格中写入公式 44 13-2 检查单元格是否含有公式 45 13-3 判断单元格公式是否存在错误 46 13-4 取得单元格中公式的引用单元格 47 13-5 将...
  • 首先弄好 下拉单选的,[data(数据)-- data validation(数据验证) -- 选 list (列表)-- 输入 你的所有选项,用英文逗号分割开] 然后--开发工具(默认隐藏的,在Excel的file(文件)--options(选项)--customize ribbon--...
  • 04065引用不连续单元格区域集合内的各个连续的单元格区域 04066引用工作簿窗口范围内所有的单元格 04067引用设定了允许滚动区域的单元格区域 04068引用指定了左上角和右下角的单元格区域(之一) 04069引用指定了...
  • 1.打开一个新工作表 2.在工作表中央画一个文本框,并且随便输入什么文字 3. 选择文本框之外的...8. 在“工程/库”下拉列表框里选择“Excel”类 9. 在搜索框里输入“textbox”并点击搜索按钮。确保你没有在文字间敲...
  • 表格中的排序功能是一个非常实用、强大功能。对于数据的主要排序方式有升序排列和降序排列,在日常工作中可能会经常根据要求要...在C2单元格制作一个简单的下拉列表下拉列表内容设置为升序、降序。2.在E1单元格...
  • 方法二:下拉列表、COLUMN、VLOOKUP;方法三:下拉列表、INDEX、MATCH1.利用控件、COLUMN、INDEX函数实现图表联动开发工具-插入-表单控件-组合框。注意插入表单控件,而不是ActiveX控件。ActiveX控件多用于VBA。右击...
  • 表格样式:员工详细数据:解决思路:第1步在"表格样式"工作表中插入ActiveX控件:下拉列表框,数据源定位于"员工详细数据"工作表中的B列序列号,当人工选择下拉列表项时,选中的"序列号"值存在A2单元格,为防止误...

空空如也

空空如也

1 2 3 4
收藏数 79
精华内容 31
关键字:

vba单元格下拉列表