精华内容
下载资源
问答
  • 在工作中,常常需要对多行多列的数据设置下拉菜单,但在使用数据验证,设置下拉菜单时,序列来源是不能执行引用多行多列区域的,否则即会出现下图提示框,应该如何进行设置呢?第一步:定义名称框鼠标依次点击公式—...
    4d3ee86b66cff59e503e7ec67c5bbf20.png

    在工作中,常常需要对多行多列的数据设置下拉菜单,但在使用数据验证,设置下拉菜单时,序列来源是不能执行引用多行多列区域的,否则即会出现下图提示框,应该如何进行设置呢?

    d9f4febf2a6b50ba28fafb9b88407324.png

    第一步:定义名称框

    鼠标依次点击公式—定义的名称—定义名称,在打开的窗口中,输入名称“城市”,再将引用位置设为“A2:A9”,之后使用鼠标点击确定。

    2bab9f74a7dae332286b425b6ac89e58.gif

    第二步:设置下拉菜单

    之后再选中区域,鼠标点击数据—数据验证,将验证条件设为“序列”,将序列来源设为“=城市”,鼠标点击确定即可。

    3b54708cc8592e30769b1793b2e9f6ae.gif

    第三步:修改定义名称

    鼠标依次点击公式—定义的名称—名称管理器,在打开的窗口,将引用位置改为多行多列数据,如“A2:D9”,鼠标点击确定,即可完成多行多列下拉菜单的制作。

    9c14506b73667b4b393b11d6b5b08c89.gif

    如果下拉列表太长在下拉选择的时候也是很遭嫌弃的。那我们就做个带关键词搜索的下拉列表吧!就像下图这样的:

    ca171471cf75476ea4bf76d66d2b06df.gif

    有没有觉得很高级?想知道怎么做的吗?紧跟我的步伐,三步就能做出搜索式下拉菜单!

    1根据关键词创建辅助列

    ❶ 在 A 列填写完整的省份列表;

    ❷ 创建根据关键词筛选的辅助列:

    将下列公式填入 B2 单元格,使用【CTRL+SHIFT+ENTER】组合键结束公式,向下填充。

    公式:

    =IFERROR(INDEX($A$2:$A$35,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),$A$2:$A$35)>0,$A$2:$A$35,""),$A$2:$A$35,0),""),ROW(A1))),"")

    3c13da3ee7fd8b72eb6357693198fcd6.gif

    上面的公式这么长,是不是把你吓到了?公式虽然很长很难,但直接套用即可。

    套用方法很简单:

    因为公式中 4 处标蓝的部分是完全一样的:$A$2:$A$35,就是完整的省份列表所在单元格。

    所以,直接把标蓝的部分换成你要做的列表区域就可以了!

    如果简单的套用公式,并不能满足你的求知欲。而是想知道这个公式,是怎么得出筛选列表的!

    公式:

    =IFERROR(INDEX([完整列表区域],SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),[完整列表区域])>0,[完整列表区域],""),[完整列表区域],0),""),ROW(A1))),"")

    1e02f79cc6c6afee02cb473e556319c3.png

    公式解析:

    理解很长很长的嵌套公式,最好是把它拆分出来逐步理解。

    ❶ CELL 函数

    公式:=CELL("contents")

    使用这个公式可以获取最后编辑的单元格内容,就是我们要搜索的动态关键词。

    cd51310c32f035cd59cf146726a97de0.gif

    为了更好理解,这里先不使用 CELL 函数,直接以搜索包含关键词"北"为例,我们把公式拆分出来看看。

    辅助列 1:

    公式:B3=FIND("北",A3,1)

    目标:判断是否含有关键词。

    上述今天和大家分享的Excel下拉菜单的制作小技巧,非常实用哦!

    解析:FIND 函数的作用,是从 A3 单元格「河北省」的第 1 个字开始查找字符串"北"字,找到后就返回「北」字的位置。

    「河北省」的第 2 个字符是"北"所以 B3 单元格显示 2,如果找不到关键词则返回#VALUE!。

    31a69bad859e3bcbd169fd64bbc88a24.png

    辅助列 2:

    公式:C3=IF(B3>0,A3,"")

    目标:将 FIND 的结果数字转换为省份名称。

    解析:IF 函数的作用是,判断条件 B3 单元格 2 是否大于零。

    如果是,显示 A3「河北省」;如果不是,显示空值「」。

    308f40699b4c5eb4c337de7caefd2a5c.png

    到这里,我们其实已经得到了含有关键词「北」的省份列表(辅助列 2)。

    但是,它不能直接作为下拉菜单的列表,因为还包含了很多#VALUE!。

    4914854b7899f2bb46a143db86a6ca18.png

    所以,接下来我们要去掉错误值,并且给含有关键词「北」的省份列表重新排序。

    辅助列 3:

    公式:D3=MATCH(C3,$A$3:$A$19,0)

    目标:根据省份名称,找到该原始列表的相对位置。

    解析:MATCH 的作用是返回 C3 单元格「河北省」,在数组 A3 到 A19 单元格(即原始列表)中的相对位置,匹配方式是 0(即精确匹配)。

    因为河北省在是原始列表 A3:A19 的第一个值,所以结果为 1。

    a03b9c5b5338933ddc2c80eac72d7f9e.png

    辅助列 4:

    公式:E3=IFERROR(D3,1048765)

    目的:去除错误值。

    解析:因为辅助列 5 使用 SMALL 函数进行排顺序,但是该函数不支持错误值。

    所以,这一步先使用 IFEERROR 函数将错误值替换为空值""。

    f73ed5a08dd37929a6e28d8896758cf2.png

    辅助列 5:

    公式:F3=SMALL($E$3:$E$19,ROW(A1))

    目的:对列表进行排序,使有关键词的省份排

    解析:ROW(A1)函数的作用是获取单元格的行号,结果是 1,这里的作用是构建一个随行号递增的数列 1、2、3……

    SMALL 函数的作用是返回数组 E3 至 E19 单元格(辅助列 4)中第 1 小的值,结果是 1。

    70f70e10d7e55954106285e7af0618aa.png

    辅助列 6:

    公式:G3=NDEX($A$3:$A$19,F3)

    目的:根据相对行号找到对应省份。

    解析:INDEX 函数的作用是在数组 A3 至 A19 单元格(原始列表)中找到第 1(F3 单元格)个单元格的内容,结果是河北省。

    这一步也会有很多错误值(#NUM!),同样可以使用 IFERROR 将其替换为空值。

    a2b36239fb7773f50909c3af898c7cfc.png

    到辅助列 6 位置,我们已经获得了含关键词的省份列表。

    如果想要使用一列搞定的话,就是把套(函)娃(数)给组(嵌)装(套)起来!

    嵌套要将公式稍作改动,改成数组公式,这里就不展开啦~

    2定义辅助列名称

    ❶ 点击【公式】选项卡-【名称管理器】-新建名称。

    34e2b773c40b92a0a235903982eee5e2.png

    ❷ 新建名称,名称区输入「省份列表」,引用位置输入公式:

    =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$35)-COUNTIF(Sheet1!$B$2:$B$35,""),1)

    2fc26286f592e9c64e1a0207cc58ea2f.png

    公式解析:

    300fda72e82afd55c342681ebf19b841.png

    Sheet1!$B$2:$B$35 就是辅助列。

    2af18a8755eb1b19d0f08ef8010d2cf5.png

    COUNTA(Sheet1!$B$2:$B$35)是获取辅助列非空单元格的个数。尽管 B5 单元格的公式结果是空值「」,但是仍然属于非空单元格。

    COUNTIF(Sheet1!$B$2:$B$35,"")是获取辅助列空值「」的个数。

    使用 OFFSET 函数构建一个动态的列表区域。以 B2 单元格为起点,向下偏移 0 个单元格,向下偏移 0 个单元格,长度为②-①(即关键词的匹配数),宽度是 1。

    这里关键词是「北」,匹配数是 3,所以整个公式得到的结果就是 B2 到 B4 这个区域。

    根据关键词,区域会动态变化。

    3设置下拉列表

    ❶ 选中需要设置下拉列表的单元格,点击【数据】选项卡-【数据验证】-「数据验证」;

    fb5bdd5e25ccaa790dede0c795d3980c.png

    ❷ 在验证条件对话框的允许中选择「序列」,来源填写「=省份列表」;

    de342e50c461fafaadeaf7cd1fb9165e.png

    ❸ 点击「出错警告」选项卡,取消勾选「输入无效数据时显示出错警告(S)」。

    2ba8602b3f10326eb518c330beeba9b6.png

    完成!

    17616bb70fdee0cff2ce8f92623bbc6c.gif

    搜索式下拉列表和多级下拉列表一样,本质都是利用辅助列,创建动态的下拉选项。

    需要注意的是,因为设置下拉列表时取消了出错警告,所以数据验证无法限制填写内容。

    本文使用的公式虽然很长,但好处是对 Excel 的版本没有太高要求,Office2007 以上的版本和 WPS 都可以使用。

    PS:如果是 WPS2019 及以上的版本,则自带「搜索式下拉菜单」~

    展开全文
  • 设置下拉菜单长度

    千次阅读 2008-05-05 15:54:00
    有多个下拉菜单在同一页面上,为美观设置为同一长度。需要在select标签中添加一个属性 style="width:100"例如: 

    有多个下拉菜单在同一页面上,为美观设置为同一长度。需要在select标签中添加一个属性 style="width:100"

    例如:
          <select id="validtime_set" name="SMSVALID_TIME" style="width:100">

    展开全文
  • 用qt导出的excel接口进行excel读写访问 限制excel cell的输入代码: QString GetExcelCellIndex(const int row_index, const int col_index) { if (row_index < 1 || col_index < 1)//从1开始 { ...

    开发环境:windows vs cpp
    用qt导出的excel接口进行excel读写访问

    限制excel cell的输入代码:

    QString GetExcelCellIndex(const int row_index, const int col_index)
    {
        if (row_index < 1 || col_index < 1)//从1开始
        {
            return QString("");
        }
        const int col_ary = 26;//A--Z 26进制
        int remainder = (col_index - 1) % col_ary;//余数
        int quotient = (col_index - 1) / col_ary;//商
        QString col_str = QChar('A' + remainder);
        while (quotient)
        {
            remainder = (quotient - 1) % col_ary;
            quotient = (quotient - 1) / col_ary;
            col_str = QChar('A' + remainder) + col_str;
        }
        return col_str + QString::number(row_index);
    }
                Excel::Range *range_cell = sheet->Range(GetExcelCellIndex(
                    i + 1,
                    j + 1));
                if (range_cell == NULL)
                {
                    delete sheet;
                    sheet = NULL;
                    EventTimeEnd(std::string(log_buf));
                    return false;
                }
                Excel::Validation *validation = range_cell->Validation();
                if (validation)
                {
                    QString str_list_temp = ",";
                    validation->Delete();
                    validation->Add(Excel::xlValidateList, Excel::xlValidAlertStop, Excel::xlBetween, QVariant(str_list_temp));
                    validation->SetInputMessage(tr("禁止修改表头"));
                    validation->SetShowInput(true);
                    validation->SetErrorMessage(tr("输入错误"));
                    validation->SetShowError(true);
                    validation->SetIgnoreBlank(true);
                    validation->SetInCellDropdown(false);
                    validation->SetIMEMode(Excel::xlIMEModeNoControl);
                }
    

    cell为下拉菜单

    
                        Excel::Validation *validation = range_cell->Validation();
                        if (validation)//限制输出文件中excel cell的输入值,添加excel的cell下拉菜单。
                        {
                            QStringList str_list = pView->GetCellStringList(index);
                            QString str_list_temp;
                            if (str_list.size() == 0)
                            {
                            }
                            else if (str_list.size() == 1)
                            {
                                if (str_list.at(0).trimmed().length() == 0)
                                {
                                }
                                else
                                {
                                    str_list_temp = str_list.at(0).trimmed();
                                }
                            }
                            else
                            {
                                str_list_temp = str_list.join(',').trimmed();
                            }
                            validation->Delete();
                            if (str_list_temp.length() == 0)
                            {
                                str_list_temp = ",";//下拉菜单必须非空,空字符串就是逗号
                            }
                            validation->Add(Excel::xlValidateList, Excel::xlValidAlertStop, Excel::xlBetween, QVariant(str_list_temp));
                            if (str_list_temp == ",")
                            {
                                validation->SetInputMessage(tr("禁止修改"));
                                validation->SetShowInput(true);
                            }
                            validation->SetErrorMessage(tr("输入错误"));
                            validation->SetShowError(true);
                            validation->SetIgnoreBlank(true);
                            validation->SetInCellDropdown(true);
                            validation->SetIMEMode(Excel::xlIMEModeNoControl);
                        }
    

    限制cell的输入字符串的长度:

        if (limit_len > 0)
        {
            Excel::Validation *validation = range_cell->Validation();
            if (validation)
            {
                QString str_list_temp = ",";
                validation->Delete();
                validation->Add(Excel::xlValidateTextLength, Excel::xlValidAlertStop, Excel::xlLess, QVariant(limit_len));
                validation->SetIgnoreBlank(true);
                validation->SetInCellDropdown(true);
                QString str_mess = tr("限制长度%1").arg(limit_len);
                validation->SetInputMessage(str_mess);
                validation->SetShowInput(true);
                validation->SetErrorMessage(str_mess);
                validation->SetShowError(true);
                validation->SetIMEMode(Excel::xlIMEModeNoControl);
            }
            if (str.length() < limit_len)
            {
                range_cell->SetFormulaR1C1(str);//20191126 超长字符串写入excel cell。
            }
        }
        else
        {
            if (limit_len == 0)//无限制
            {
                range_cell->SetFormulaR1C1(str);//20191126 超长字符串写入excel cell。
            }
        }
    

    通用方法:记录宏的方式获得office excel、word…的操作代码,然后根据关键字找到对应接口。
    例如限制长度的vb宏代码如下

        With Selection.Validation
        .Delete
        .Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
        Operator:=xlLess, Formula1:="32"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .IMEMode = xlIMEModeNoControl
        .ShowInput = True
        .ShowError = True
        End With
    
    展开全文
  • 下拉菜单设置的所有值的总字符长度超过一定长度(目前我测的是250个汉字长度),输出Excel正常,代码不报错,但是打开输出的Excel,会出现一下提示_ ![图片说明]...
  • 请教:在EXCEL中如何统计字符长度?打开excel文件,确定需要编辑的内容选择功能区的“公式”功能在公式中选择“LEN”功能在text中选择需要编辑的单元格 7、点击“确定” 8、系统自动计算出了单元excel中怎么判断字符...

    请教:在EXCEL中如何统计字符长度?

    打开excel文件,确定需要编辑的内容

    选择功能区的“公式”功能

    20200602155937.gif

    在公式中选择“LEN”功能

    在text中选择需要编辑的单元格 7、点击“确定” 8、系统自动计算出了单元

    excel中怎么判断字符串的长度

    在Excel中可以使用两种公式计算字符长度 =LEN(A1) 表示计算单元格A1中字符长度(1个双字节字符,长度计算为1) =LENB(A1) 表示计算单元格A1中字符长度(1个双字节字符,长度计算为2) 如果使用上述公式,与预见的字符长度不一致。

    excel 怎样筛选出单元格 字符长度(汉字字数)大于10按照如下步骤即可在excel中筛选出单元格的字符长度(汉字字数)大于10的行,并使字体变成红色:

    首先在excel中新建一个不同内容的工作表。

    选中文本内容,在开始工具栏中点击条件格式,再选择新建规则。

    怎么计算excel里的字符串的长度

    Excel中如何宽度调整为7个标准字符

    2007版,如果你要调整单元格的宽度的话,就选定需要调整宽带的列的范围,再点击菜单-开始-右边的格式栏-下拉-里面有列宽,也有行高,单位是毫米,1cm就填入10,确定就可以了。

    2003版直接在菜单-格式-就有列和行的选择。

    是要限制输入的长度吗 选择整列-数据-有效性-设置-文本长度,可以根据需要寻等于”或“介于”等不同方式。

    excel字符串去掉固定长度的字符串

    例如 2004-1-2 2004-12-26 去掉前面5位,只剩下1-2 12-26 批量解决

    假设A列为 2004-1-2 2004-12-26 要在B列显示结果. 可在B1输入: =right(A1,len(A1)-5),下拉即可

    EXCEL中如何同时限制输入字符的长度,带特定名称的...比如说一列中,只允许输入长度是10,前面三个字符是123,英文只能大写的如果那个单元格在A1中,选中A1,数据,数据有效性,自定义,下面框中输入公式 =(LEN(A1)=10)*(LEFT(A1,3)="123")*EXACT(UPPER(MID(A1,4,7)),MID(A1,4,7)) 确定,再将A1用自动填充柄将有效性内容下拉。 再在A列中输入那些内容试试。

    展开全文
  • 一级下拉列表 初级下拉列表 数量有变化的下拉列表 去除空白的下拉列表 多级下拉列表 建立公式名称的下拉列表 公式下拉列表(去除空白)
  • 3. 快速填充的功能,excel会自己找规律 对于下面的数据,我只想要中文部分 其实只要你的数据有一定的规律,就可以用快速填充 新增一列, 输入第一行的中文部分,向下拖动数据。 选择下面的填充选项.
  • PHPExcel限制数据有效性,显示下拉菜单
  • 在日常工作中,我们经常会使用excel编辑电子表格,但有时默认的行高与列宽与单元格内数据内容的高度和长度会不太匹配,如下图,这时就需要调整表格的行高和列宽,那么今天就来和大家分享几种调整excel表格行高与列宽...
  • [控制结尾,防止你在下拉时,下拉list中的数据,会因为单元格变化而缺失数据。控制结束行,在结束行之前都是list所有数据,超过结束行,还是会因单元格变化而缺失数据]...
  • 最近在项目中使用poi创建excel文件时,需要生成单元格下拉选。 下面是一个demo,注释比较清楚,以此参考。 package poi; import java.io.FileOutputStream; import org.apache.poi.hssf.usermodel....
  • 省市区三级联动下拉菜单  name="author" content="西米岛主">  name="description" content="博客地址:http://my.oschina.net/ximidao">  type="text/css"> .select-box{margin: 30px; padding: 30px; ...
  • EXCEL如何设置,使表格能自动调整列宽以适应文字长度?1、在电脑桌面上找到需要调整EXCEL表格,2入到表格页面,选中需要设置的单元格,右键鼠标,并选择设置单元格格式选项。3、进入到单元格格式页面之后,切换到...
  • 在最近使用excel的数据验证功能时,选择序列,字数的长度是有限制的
  • 首先说一下我们导出的这个Excel表,我们是通过POI来生成一个Excel表,然后在Excel表中添加了下拉列表,然后将数据库中取出的数据放到下拉列表中,这样每次导出的Excel表的下拉列表的数据都是导出那一时刻数据库中的...
  • 2019-10-09阅读(722)一、首先,打开Excel表格程序,在程序主界面中打开要调整Excel表格,可以看到此时宽度不统一。二、然后,在Excel表格左上角点击小三角形。三、然后,即可看到所有的内容都被选中。四、然后,在...
  • 背景:easyexcel导出excel模板时,标题带下拉框及其下拉值过多不显示问题。 使用的easyexcel版本: <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId&...
  • poi生成2003以下版本excel(.xls后缀文件) 注意:该版本生成的会存在兼容性问题,下述会进行说明。 项目引用poi库的依赖 (略过) ... // 传入下拉列表数组值,构造信息模板 public static Workbook generate...
  • 由于B列部门列经常需要重复录入,而且部门列表一般为固定的序列(如图2-58中E列所示),如何用VBA对B列创建下拉列表以便输入内容? 员工编号 部门 姓名 部门列表 0006 财务部 程建华 ...
  •  最近在搞一个Excel扩展的东西,用VSTO自动设计并且填写电子表格。……这是个对于个人休闲作品来说略显复杂的工程,仅C#代码量约达到3000行,还要设计十几个页面模板,并融合约千行VBA代码……。在调试环境中一切...
  • poi设置单元格下拉下表

    千次阅读 2018-07-17 08:14:51
    poi是读写excel最常用的一个开源中间件,实际使用中我们可能会遇到这样的需求:限制某列单元格只能输入特定的内容,这就是excel下拉列表特性,以下是wps中excel的一个截图: 从上图可以看出,维护下拉选项有两...
  • Excel使用技巧

    千次阅读 2012-06-05 18:06:51
    Excel使用技巧 在工作当中用电子表格来处理数据将会更加迅速、方便,而在各种电子表格处理软件中,Excel以其功能强大、操作方便著称,赢得了广大用户的青睐。虽然Excel使用很简单,不过真正能用好Excel的用户并不...
  • excel

    2019-09-29 02:06:29
    一、建立分类下拉列表填充项  我们常常要将企业的名称输入到表格中,为了保持名称的一致性,利用“数据有效性”功能建了一个分类下拉列表填充项。  1.在Sheet2中,将企业名称按类别(如“工业企业”、“商业...
  • plsqldev2、打开需导入的excel表格,单击office按钮,选择另存为--其他格式, 选择保存类型CSV(逗号分隔)(*.csv),单击保存3、打开plsqldev 在工具栏中选择Tools下拉菜单下的Text Importer。进入后选择data from text...
  • 学会这4个Excel实用技巧,数据分析立马高人一等

    千次阅读 多人点赞 2019-11-06 10:22:41
    对于刚进入数据分析行业新手来说,EXCEL可以被当做一款入门的软件。在学习R或Python前,事先掌握一定的EXCEL知识是百利而无一害。EXCEL凭借其功能强大的函数、可视化图表、以及整齐排列的电子表格功能,使你能够快速...
  • excel 操作技巧

    2018-08-17 08:44:49
    也许你已经在Excel中完成过上百张财务报表,也许你已利用Excel函数实现过上千次的复杂运算,也许你认为Excel也不过如此,甚至了无新意。但我们平日里无数次重复的得心应手的使用方法只不过是Excel全部技巧的百分之一...
  • Excel学习

    2010-01-23 21:09:00
    也许你已经在Excel中完成过上百张财务报表,也许你已利用Excel函数实现过上千次的复杂运算,也许你认为Excel也不过如此,甚至了无新意... 一、建立分类下拉列表填充项 我们常常要将企业的名称输入到表格中,为了保持
  • excel学习(二)

    2018-03-23 12:53:51
    前言 本篇文章主要关于excel使用过程中的一些小技巧,...自定义下拉菜单 自定义名称 … 常用快捷键 excel常用快捷键很多,选取一些高频使用的,整理成如下表格: · 快捷键 功能描述 Ctrl+Shift+方...
  • 1. 本节课将为您演示条件格式中的数据条功能。使用数据条功能,可以非常直观的查看选定区域中数值的... 在G列单元格的数据区域中,显示了蓝色的渐变数据条,根据单元格内数值的大小,数据条的长度也不同。较大的数值...
  • Excel用法

    2012-09-12 14:36:56
    也许你已经在Excel中完成过上百张财务报表,也许你已利用Excel函数实现过上千次的复杂运算,也许你认为Excel也不过如此,甚至了无新意。但我们平日里无数次重复的得心应手的使用方法只不过是Excel全部技巧的百分之一...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,623
精华内容 1,049
关键字:

excel下拉菜单调整长度