Excel之UPPER、LOWER、IFERROR和SUBSTITUTE函数

屋顶住过的风

已于 2022-09-13 22:09:14 修改

阅读量1.5k

点赞数 1

文章标签：其他

于 2022-09-13 00:01:01 首次发布

本文链接：https://blog.csdn.net/qq_44876582/article/details/126797802

版权

本文介绍了如何在Excel中使用UPPER和LOWER函数进行大小写转换，以及IFERROR和SUBSTITUTE函数处理SQL查询中的文本处理。通过实例展示了获取租户与表名的方法，适合数据分析和文本操作初学者。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

UPPER函数

简介

UPPER函数可以将文本字符串转换成字母全部大写形式。

UPPER(text)

UPPER(文本字符串)

使用

1.在B2单元格输入内容：

=upper(A2)

2.回车+拖动

3.最终结果

LOWER函数

简介

LOWER函数可以将一个文本字符串的所有字母转换为小写形式。

LOWER(text)

LOWER(文本字符串)

使用

1.在A2单元格输入内容：

=lower(B2)

2.回车+拖动

3.最终结果

IFERROR函数

简介

如果表达式是一个错误，则返回value_if_error，否则返回表达式自身的值。

IFERROR(value, value_if_error)

IFERROR(第一个表达式，当第一个表达式是错误时需要返回的新表达式)

使用

如下图，用函数找出“周”所在文本字符串的位置，如果没有，则找出“曹”所在文本字符串的位置。

1.单元格输入内容：

=iferror(find("周",A2),find("曹",A2))

说明：当文本字符串里没有“周”时，find("周",A2)是一个错误，所以IFERROR返回find("曹",A2)。关于find函数的使用，前面写的博客已经介绍过，这里不再赘述。

2.回车+拖动

3.最终结果

SUBSTITUTE函数

简介

SUBSTITUTE函数可以将字符串中的部分字符串以新字符串替换。

SUBSTITUTE(text, old_text, new_text, [instance_num])

SUBSTITUTE(需要替换其中字符的文本，需要替换的旧文本，用于替换 old_text 的文本，用来指定以 new_text 替换第几次出现的 old_text)

如果指定了 instance_num，则只有满足要求的 old_text 被替换；如果缺省则将用 new_text 替换 text中出现的所有 old_text。

使用

如下图，这些同学交作业的时候命名格式不对，老师规定的格式是使用下划线，而他们用的都是短横线，你能使用函数将同学们的作业命名格式改正过来吗？

1.单元格输入内容：

=substitute([@作业名称（短横线）],"-","_")

说明：SUBSTITUTE函数的第一个参数不用手动输入，用鼠标点击对应单元格（例如：二（1）班-020101-周杰伦.MP3），Excel就会自动帮助我们输入。

2.回车

上面我们只用到了SUBSTITUTE的前三个参数，如果我们加上第四个参数instance_num，并且赋值为1，即：

=SUBSTITUTE([@作业名称（短横线）],"-","_",1)

回车，可以看到只有第1个短横线（“-”）被换成了下划线（“_”）。

综合案例

如下图，使用Excel函数将SQL中的租户和表名填到对应的单元格中。

获取租户

观察上图可发现，租户（如：heilongjiang）位于“from”与“.”之间，因此，我们可以通过find函数分别找到“from”和“.”的位置，相减可得到租户的长度，接着使用mid函数将租户从SQL中截取出来。

MID(
    LOWER(D2),
    FIND( -- 获取文本中最后一个“from”字符串的位置
        "****",
        SUBSTITUTE( 
            LOWER(D2),
            "from",
            "****",
            (LEN(D2) - LEN(SUBSTITUTE(LOWER(D2), "from", ""))) / LEN("from") -- 计算字文本中存在多少个“from”字符串
        )
    ) + LEN("from") + 1, -- 获取租户第一个字符所在位置
    FIND(".", D2) - FIND( 
        "****",
        SUBSTITUTE(
            LOWER(D2),
            "from",
            "****",
            (LEN(D2) - LEN(SUBSTITUTE(LOWER(D2), "from", ""))) / LEN("from")
        )
    ) - LEN("from") - 1 -- 获取租户的长度
)

最终结果：

值为#VALUE!的单元格，是因为其SQL本来就没有租户。

获取表名

观察下图可发现，表名要么直接位于“.”或者“from”之后，要么位于“.”或者“from”之后，“where”之前。我们可以通过find函数找到“.”、“from”、“where”的位置，通过right或mid函数截取表名长度即可得到表名。

IFERROR(
    IFERROR(
        MID(
            D2,
            FIND(".", D2) + 1,
            FIND("where", D2) - FIND(".", D2) -2 -- 表名位于“.”与“where"之间的情况：select population, natural from guangdong.guangzhou where region = 'yuexiu';
        ),
        RIGHT(D2, LEN(D2) - FIND(".", D2)) -- 表名直接位于“.”之后的情况：select population, economy from heilongjiang.harbin;
    ),
    IFERROR(
        MID(
            D2,
            FIND( -- 获取文本中最后一个“from”字符串的位置
                "****",
                SUBSTITUTE(
                    LOWER(D2),
                    "from",
                    "****",
                    (LEN(D2) - LEN(SUBSTITUTE(LOWER(D2), "from", ""))) / LEN("from") -- 计算文本中出现“from”字符串的次数
                )
            ) + LEN("from") + 1,
            FIND("where", D2) - FIND(
                "****",
                SUBSTITUTE(
                    LOWER(D2),
                    "from",
                    "****",
                    (LEN(D2) - LEN(SUBSTITUTE(LOWER(D2), "from", ""))) / LEN("from")
                )
            ) - LEN("from") - 1
        ), -- 表名位于“where”与“from”的情况：select population, economy, natural, technological from xian where region = 'changan';
        RIGHT(
            D2,
            LEN(D2) - FIND(
                "****",
                SUBSTITUTE(
                    LOWER(D2),
                    "from",
                    "****",
                    (LEN(D2) - LEN(SUBSTITUTE(LOWER(D2), "from", ""))) / LEN("from")
                )
            ) - LEN("from")
        ) -- 表名直接位于“from”之后的情况：select population, natural from shanghai;
    )
)

最终结果：