精华内容
下载资源
问答
  • Excel---提取单元格中红色字符串

    千次阅读 2019-04-29 09:35:18
    申明:主要参考...EXCEL单元格中有标红的字符串,需要将其从整个字符串中提取出来 2 方法 使用VBA,看起来是通过循环的方式提取的 贴个函数,更具体的可以参考https://www.extendoffice.com/z...

    申明:主要参考https://www.extendoffice.com/zh-CN/documents/excel/2234-excel-extract-cell-color.html

    1 需求

    EXCEL单元格中有标红的字符串,需要将其从整个字符串中提取出来

    2 方法

    使用VBA,看起来是通过循环的方式提取的
    贴个函数,更具体的可以参考https://www.extendoffice.com/zh-CN/documents/excel/2234-excel-extract-cell-color.html

    Function GetColorText(pRange As Range) As String
        'Updateby20141105
        Dim xOut As String
        Dim xValue As String
        Dim i As Long
        xValue = pRange.Text
        For i = 1 To VBA.Len(xValue)
            If pRange.Characters(i, 1).Font.Color = vbRed Then
                xOut = xOut & VBA.Mid(xValue, i, 1)
            End If
        Next
        GetColorText = xOut
    End Function
    

                                  2019-04-29小雨 于 南京市栖霞区 紫东

    展开全文
  • 但在日常的工作部分Excel打印档要求将某列上下内容相同的单元格合并,以便看起来清爽。比如下面这样的表格:老板一般会要求在打印之前将B,C列上下相邻单元格内容相同的合并成如下这样的:如果表格少,一个一个...

    从汇总统计的角度,合并单元格非常不友好。单元格一旦合并,使用数据透视表,分类汇总都无法得到正确的结果。所以,对于原始数据,最好别随意合并单元格。但在日常的工作中,部分Excel打印档要求将某列上下内容相同的单元格合并,以便看起来清爽。比如下面这样的表格:

    老板一般会要求在打印之前将B,C列上下相邻单元格内容相同的合并成如下这样的:

    如果表格少,一个一个手动合并也未尝不可,但若遇到大量的表格,就得做好手软的准备了。现在用Python,一键批量处理Excel工作簿中的所有工作表的单元格合并,解放您的手指。

    #定义合并单元格的函数

    def Merge_cells(ws,target_list,start_row,col):

    '''ws: 是需要操作的工作表target_list: 是目标列表,即含有重复数据的列表start_row: 是开始行,即工作表中开始比对数据的行(需要将标题除开)col: 是需要处理数据的列'''

    start = 0 #开始行计数,初试值为0,对应列表中的第1个元素的位置0

    end = 0 #结束行计数,初试值为0,对应列表中的第1个元素的位置0

    reference = target_list[0] #设定基准,以列表中的第一个字符串开始

    for i in range(len(target_list)): #遍历列表

    if target_list[i] != reference: #开始比对,如果内容不同执行如下

    reference = target_list[i] #基准变成列表中下一个字符串

    end = i - 1 #列计数器

    ws.merge_cells(col + str(start + start_row) + ":"+col + str(end + start_row))

    start = end + 1

    if i == len(target_list) - 1: #遍历到最后一行,按如下操作

    end = i

    ws.merge_cells(col + str(start + start_row) + ":"+ col + str(end + start_row))

    由于合并单元格是一个重复动作,一张工作表的B列和C列都需要使用。对于这种需要重复使用的功能,定义成函数,每用一次调用一次,最为方便。此处,我们先定义一个函数Merge_cells,方便后续调用。这个函数包含四个参数,ws指需要操作的工作表,target_list指目标列表,即B列所有客户名称的列表,或者C列所有产品编码的列表,这些列表中有很多重复项。我们就根据这些重复项的重复次数为依据来判断单元格合并的起始行。

    我们以“2月”的工作表中客户名称为例,前2个是“客户HH”,后面3个是“客户R”。在调用Merge_cells函数的时候,传入的参数中,ws是wb["2月]这个工作表;target_list是就是customer_list,其内容为['客户HH', '客户HH', '客户R', '客户R', '客户R'];最后一个参数是“B”,指这个合并的操作是针对B列的。

    customer_list

    >>

    ['客户HH', '客户HH', '客户R', '客户R', '客户R']

    我们使用Merge_cells(ws,customer_list,start_row,"B")调用函数,并传入4个参数。然后进入到函数内部,看每一步是怎样运行的。首先开始行和结束行的计数都设置为0,然后设置一个比较基准reference,其值为列表中的第一个元素,此处为客户HH。然后用for循环遍历列表,用if语句来开始逐个调出列表中的元素与基准比对。现在,i的值是0,target_list[0]的值是客户HH,与基准reference的值相同,那么第一个if下面的语句就不会执行,直接跳到第二个if语句。第二个if语句是判断是否遍历到列表的最后一个元素,此处,i的值是0,len(customer_list)-1是4,所以二者显然不等,那也跳过下面的语句。

    所以第一轮循环,因不满足操作条件,没有合并单元格。然后第二轮循环,此时i的值是1。target_list[1]的值还是客户HH,与基准reference的值仍然相同,所以第一个if下面的语句继续跳过,到第二个if语句。此时i的值还是不等于len(customer_list)-1的值4,第二个if后的也跳过。

    接下来进入第三轮循环,此时i的值是2。target_list[2]的值还是客户R,与基准reference的值不同了,所以第一个if下面的语句会执行。因为已经出现了不同值,需要将基准做改变,变成了target_list[2],即客户R,后续都用新的基准来比对了。第一轮比较完成后,需要确定结束行计数器即end的值,令它等于i-1,即2-1=1,对应的是列表中的第二个元素所在位置。然后开始第一次激动人心的合并单元格,通过观察我们看到应该合并B6到B7共两个单元格。ws.merge_cells(col + str(start + start_row) + ":"+col + str(end + start_row))此时为ws.merge_cells("B6":"B7")。合并完单元格后,开始行计数器start也需要前进几位, 这取决于结束行计数器end。结束行计数器基础上加1就是下一个开始,此时start为2,对应列表中的第三个元素所在的位置。现在程序来到第二个if语句,此时i的值是2,还不等于len(customer_list)-1的值4,第二个if后的继续跳过。

    然后进入第四轮循环,此时i的值是3。target_list[3]的值还是客户R,与基准reference的值相同,所以第一个if下面的语句跳过,到第二个if语句。此时i的值3,不等于len(customer_list)-1的值4,第二个if后的也跳过。

    然后进入第五轮循环,此时i的值是4。target_list[4]的值还是客户R,与基准reference的值相同,所以第一个if下面的语句跳过,到第二个if语句。此时i的值4,等于len(customer_list)-1的值4,说明已经循环到列表中的最后一个元素了。那第二个if后程序将执行。通过观察,此时应该合并B8到B10共3个单元格。ws.merge_cells(col + str(start + start_row) + ":"+ col + str(end + start_row))应该为ws.merge_cells("B8":"B10")。那么,start + start_row 需要等于8,end + start_row需要等于10。而在上一个循环结束,start的值为2, end的值为1。end此时应该对应列表的最后一个元素的位置,所以要令结束行计数器end的值为i,即为4。然后执行合并单元格操作。

    以上比较难理解的是第一个if语句中的end = i - 1和start = end + 1,以及第二个if语句中的end = i。请只需要记住,start和end始终对应列表中元素的开始和结束位置,以此为依据来合并单元格。这个函数来源于其他人,我们不用深究其原理,只要会使用,用来解决问题就行。像这种经典程序,可以单独记录起来,以便在需要的时候直接拿过来使用。当遇到难理解的程序时,可以代入相应的值,逐步逐步看结果,就能逐步理解了。

    #获取Excel表格中的数据

    from openpyxl import load_workbook #用于读取Excel中的信息

    wb = load_workbook('产品清单.xlsx')

    sheet_names = wb.get_sheet_names()

    for sheet_name in sheet_names: #遍历每个工作表,抓取数据,并根据要求合并单元格

    ws = wb[sheet_name]

    customer_list = [] #客户名称

    pn_list = [] #产品编码

    for row in range(6,ws.max_row-2):

    customer = ws['B' + str(row)].value

    pn = ws['C' + str(row)].value

    customer_list.append(customer)

    pn_list.append(pn)

    #调用以上定义的合并单元格函数`Merge_cells`做单元格合并操作

    start_row=6 #开始行是第六行

    Merge_cells(ws,customer_list,start_row,"B") #"B" - 客户名称是在B列

    Merge_cells(ws,pn_list,start_row,"C") #"C" - 产品编码是在C列

    wb.save("产品清单-合并单元.xlsx")

    以上遍历工作簿中的所有工作表,分别提取每个工作表中的数据,并调用单元格合并函数做合并单元格操作。选定一个工作表后,先抓取其中待合并所在列的数据,此例是B列的客户名称和C列的产品编码。数据抓取后,分别存入列表customer_list,pn_list。然后调用上面定义好的单元格合并函数Merge_cells做单元格合并操作。最终完成后保存数据。

    如果Openpyxl版本较低,在合并单元格操作后,可能出现单元格的边框会部分缺失(如下图)。这时需要用pip uninstall openpyxl卸载原版本,然后再用pip install openpyxl重新安装,以便升级到最新版本。

    如果您有需要处理的问题,可发邮件到我邮箱:donyo@qq.com,一起探讨解决方案。

    以上在Jupyter notebook上完成,所用到的代码及Excel 资料已上传GitHub及百度网盘, 欢迎下载到本地随意玩。

    Python版本:Python 3.6 64bit

    操作系统:Windows 7

    GitHub:Office_Automation_by_Using_Python

    百度Pan:“pan.baidu”加上“.com/s/1WXcoYts_uNJmccfJ0lrmWg” 提取码: kry7

    WeiXin:Python操作Office软件高效工作

    展开全文
  • Sub循环遍历一列数据并提取与相邻单元格的匹配项。 What setup is necessary? 需要什么设置? What are Excel's special characters for Regular expressions? Excel正则表达式的特殊字符是什么? I understand ...

    本文翻译自:How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

    How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation? 如何在Excel中使用正则表达式并利用Excel强大的类似于网格的设置进行数据处理?

    • In-cell function to return a matched pattern or replaced value in a string. 单元内函数可返回匹配的模式或字符串中的替换值。
    • Sub to loop through a column of data and extract matches to adjacent cells. Sub循环遍历一列数据并提取与相邻单元格的匹配项。
    • What setup is necessary? 需要什么设置?
    • What are Excel's special characters for Regular expressions? Excel正则表达式的特殊字符是什么?

    I understand Regex is not ideal for many situations ( To use or not to use regular expressions? ) since excel can use Left , Mid , Right , Instr type commands for similar manipulations. 我知道Regex在许多情况下都不理想( 要使用还是不使用正则表达式? ),因为excel可以使用LeftMidRightInstr类型的命令进行类似的操作。


    #1楼

    参考:https://stackoom.com/question/1WaQ6/如何在单元格和循环中使用Microsoft-Excel中的正则表达式-Regex


    #2楼

    Regular expressions are used for Pattern Matching. 正则表达式用于模式匹配。

    To use in Excel follow these steps : 要在Excel中使用,请按照以下步骤操作:

    Step 1 : Add VBA reference to "Microsoft VBScript Regular Expressions 5.5" 步骤1 :将VBA引用添加到“ Microsoft VBScript正则表达式5.5”

    • Select "Developer" tab ( I don't have this tab what do I do? ) 选择“开发人员”标签( 我没有此标签,我该怎么办?
    • Select "Visual Basic" icon from 'Code' ribbon section 从“代码”功能区部分选择“ Visual Basic”图标
    • In "Microsoft Visual Basic for Applications" window select "Tools" from the top menu. 在“ Microsoft Visual Basic应用程序”窗口中,从顶部菜单中选择“工具”。
    • Select "References" 选择“参考”
    • Check the box next to "Microsoft VBScript Regular Expressions 5.5" to include in your workbook. 选中要包含在您的工作簿中的“ Microsoft VBScript正则表达式5.5”旁边的框。
    • Click "OK" 点击“确定”

    Step 2 : Define your pattern 第2步 :定义模式

    Basic definitions: 基本定义:

    - Range. -范围。

    • Eg az matches an lower case letters from a to z 例如az匹配从a到z的小写字母
    • Eg 0-5 matches any number from 0 to 5 例如0-5匹配0到5之间的任何数字

    [] Match exactly one of the objects inside these brackets. []完全匹配这些括号内的对象之一。

    • Eg [a] matches the letter a 例如[a]匹配字母a
    • Eg [abc] matches a single letter which can be a, b or c 例如[abc]匹配一个字母,可以是a,b或c
    • Eg [az] matches any single lower case letter of the alphabet. 例如[az]匹配字母表中的任何单个小写字母。

    () Groups different matches for return purposes. ()不同的匹配分组以便返回。 See examples below. 请参阅下面的示例。

    {} Multiplier for repeated copies of pattern defined before it. {}乘数,用于重复定义在其之前的模式。

    • Eg [a]{2} matches two consecutive lower case letter a: aa 例如[a]{2}匹配两个连续的小写字母a: aa
    • Eg [a]{1,3} matches at least one and up to three lower case letter a , aa , aaa 例如[a]{1,3}匹配至少一个且最多三个小写字母aaaaaa

    + Match at least one, or more, of the pattern defined before it. +匹配至少一个或多个之前定义的模式。

    • Eg a+ will match consecutive a's a , aa , aaa , and so on 例如a+将匹配连续的aaaaaa ,依此类推

    ? Match zero or one of the pattern defined before it. 匹配零或之前定义的模式之一。

    • Eg Pattern may or may not be present but can only be matched one time. 例如,模式可能会或可能不会出现,但只能匹配一次。
    • Eg [az]? 例如[az]? matches empty string or any single lower case letter. 匹配空字符串或任何单个小写字母。

    * Match zero or more of the pattern defined before it. *匹配零个或多个之前定义的模式。 - Eg Wildcard for pattern that may or may not be present. -例如通配符,表示可能存在或可能不存在的模式。 - Eg [az]* matches empty string or string of lower case letters. -例如[az]*匹配空字符串或小写字母字符串。

    . Matches any character except newline \\n 匹配除换行符\\n以外的任何字符

    • Eg a. 例如a. Matches a two character string starting with a and ending with anything except \\n 匹配两个字符串,以a开头,以\\n以外的任何结尾

    | OR operator OR运算符

    • Eg a|b means either a or b can be matched. 例如a|b表示ab均可匹配。
    • Eg red|white|orange matches exactly one of the colors. 例如, red|white|orange正好匹配一种颜色。

    ^ NOT operator ^ NOT运算符

    • Eg [^0-9] character can not contain a number 例如[^0-9]字符不能包含数字
    • Eg [^aA] character can not be lower case a or upper case A 例如[^aA]字符不能为小写a或大写A

    \\ Escapes special character that follows (overrides above behavior) \\转义后跟的特殊字符(覆盖行为之上)

    • Eg \\. 例如\\. , \\\\ , \\( , \\? , \\$ , \\^ \\\\\\(\\?\\$\\^

    Anchoring Patterns: 锚定模式:

    ^ Match must occur at start of string ^匹配必须在字符串开头

    • Eg ^a First character must be lower case letter a 例如^a第一个字符必须为小写字母a
    • Eg ^[0-9] First character must be a number. 例如^[0-9]第一个字符必须是数字。

    $ Match must occur at end of string $匹配必须出现在字符串的末尾

    • Eg a$ Last character must be lower case letter a 例如a$最后一个字符必须是小写字母a

    Precedence table: 优先级表:

    Order  Name                Representation
    1      Parentheses         ( )
    2      Multipliers         ? + * {m,n} {m, n}?
    3      Sequence & Anchors  abc ^ $
    4      Alternation         |
    

    Predefined Character Abbreviations: 预定义的字符缩写:

    abr    same as       meaning
    \d     [0-9]         Any single digit
    \D     [^0-9]        Any single character that's not a digit
    \w     [a-zA-Z0-9_]  Any word character
    \W     [^a-zA-Z0-9_] Any non-word character
    \s     [ \r\t\n\f]   Any space character
    \S     [^ \r\t\n\f]  Any non-space character
    \n     [\n]          New line
    

    Example 1 : Run as macro 示例1作为宏运行

    The following example macro looks at the value in cell A1 to see if the first 1 or 2 characters are digits. 下面的示例宏查看单元格A1中的值,以查看前1个或2个字符是否为数字。 If so, they are removed and the rest of the string is displayed. 如果是这样,它们将被删除并显示字符串的其余部分。 If not, then a box appears telling you that no match is found. 如果没有,则会出现一个框,告诉您找不到匹配项。 Cell A1 values of 12abc will return abc , value of 1abc will return abc , value of abc123 will return "Not Matched" because the digits were not at the start of the string. 单元格A112abc将返回abc ,值1abc将返回abc ,值abc123将返回“不匹配”,因为这些数字不在字符串的开头。

    Private Sub simpleRegex()
        Dim strPattern As String: strPattern = "^[0-9]{1,2}"
        Dim strReplace As String: strReplace = ""
        Dim regEx As New RegExp
        Dim strInput As String
        Dim Myrange As Range
    
        Set Myrange = ActiveSheet.Range("A1")
    
        If strPattern <> "" Then
            strInput = Myrange.Value
    
            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With
    
            If regEx.Test(strInput) Then
                MsgBox (regEx.Replace(strInput, strReplace))
            Else
                MsgBox ("Not matched")
            End If
        End If
    End Sub
    

    Example 2 : Run as an in-cell function 示例2作为单元内函数运行

    This example is the same as example 1 but is setup to run as an in-cell function. 该示例与示例1相同,但设置为作为单元内功能运行。 To use, change the code to this: 要使用,请将代码更改为此:

    Function simpleCellRegex(Myrange As Range) As String
        Dim regEx As New RegExp
        Dim strPattern As String
        Dim strInput As String
        Dim strReplace As String
        Dim strOutput As String
    
    
        strPattern = "^[0-9]{1,3}"
    
        If strPattern <> "" Then
            strInput = Myrange.Value
            strReplace = ""
    
            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With
    
            If regEx.test(strInput) Then
                simpleCellRegex = regEx.Replace(strInput, strReplace)
            Else
                simpleCellRegex = "Not matched"
            End If
        End If
    End Function
    

    Place your strings ("12abc") in cell A1 . 将您的字符串(“ 12abc”)放在单元格A1 Enter this formula =simpleCellRegex(A1) in cell B1 and the result will be "abc". 在单元格B1输入此公式=simpleCellRegex(A1) ,结果将为“ abc”。

    在此处输入图片说明


    Example 3 : Loop Through Range 示例3循环范围

    This example is the same as example 1 but loops through a range of cells. 此示例与示例1相同,但循环通过一系列单元。

    Private Sub simpleRegex()
        Dim strPattern As String: strPattern = "^[0-9]{1,2}"
        Dim strReplace As String: strReplace = ""
        Dim regEx As New RegExp
        Dim strInput As String
        Dim Myrange As Range
    
        Set Myrange = ActiveSheet.Range("A1:A5")
    
        For Each cell In Myrange
            If strPattern <> "" Then
                strInput = cell.Value
    
                With regEx
                    .Global = True
                    .MultiLine = True
                    .IgnoreCase = False
                    .Pattern = strPattern
                End With
    
                If regEx.Test(strInput) Then
                    MsgBox (regEx.Replace(strInput, strReplace))
                Else
                    MsgBox ("Not matched")
                End If
            End If
        Next
    End Sub
    

    Example 4 : Splitting apart different patterns 示例4 :拆分不同的模式

    This example loops through a range ( A1 , A2 & A3 ) and looks for a string starting with three digits followed by a single alpha character and then 4 numeric digits. 本示例循环遍历一个范围( A1A2A3 ),并查找一个字符串,该字符串以三个数字开头,后跟一个字母字符,然后是4个数字。 The output splits apart the pattern matches into adjacent cells by using the () . 输出使用()将模式匹配拆分为相邻的单元格。 $1 represents the first pattern matched within the first set of () . $1表示在()的第一组中匹配的第一个模式。

    Private Sub splitUpRegexPattern()
        Dim regEx As New RegExp
        Dim strPattern As String
        Dim strInput As String
        Dim Myrange As Range
    
        Set Myrange = ActiveSheet.Range("A1:A3")
    
        For Each C In Myrange
            strPattern = "(^[0-9]{3})([a-zA-Z])([0-9]{4})"
    
            If strPattern <> "" Then
                strInput = C.Value
    
                With regEx
                    .Global = True
                    .MultiLine = True
                    .IgnoreCase = False
                    .Pattern = strPattern
                End With
    
                If regEx.test(strInput) Then
                    C.Offset(0, 1) = regEx.Replace(strInput, "$1")
                    C.Offset(0, 2) = regEx.Replace(strInput, "$2")
                    C.Offset(0, 3) = regEx.Replace(strInput, "$3")
                Else
                    C.Offset(0, 1) = "(Not matched)"
                End If
            End If
        Next
    End Sub
    

    Results: 结果:

    在此处输入图片说明


    Additional Pattern Examples 附加图案示例

    String   Regex Pattern                  Explanation
    a1aaa    [a-zA-Z][0-9][a-zA-Z]{3}       Single alpha, single digit, three alpha characters
    a1aaa    [a-zA-Z]?[0-9][a-zA-Z]{3}      May or may not have preceeding alpha character
    a1aaa    [a-zA-Z][0-9][a-zA-Z]{0,3}     Single alpha, single digit, 0 to 3 alpha characters
    a1aaa    [a-zA-Z][0-9][a-zA-Z]*         Single alpha, single digit, followed by any number of alpha characters
    
    </i8>    \<\/[a-zA-Z][0-9]\>            Exact non-word character except any single alpha followed by any single digit
    

    #3楼

    To make use of regular expressions directly in Excel formulas the following UDF (user defined function) can be of help. 要直接在Excel公式中使用正则表达式,以下UDF(用户定义函数)可能会有所帮助。 It more or less directly exposes regular expression functionality as an excel function. 它或多或少直接将正则表达式功能公开为excel函数。

    How it works 怎么运行的

    It takes 2-3 parameters. 它需要2-3个参数。

    1. A text to use the regular expression on. 使用正则表达式的文本。
    2. A regular expression. 正则表达式。
    3. A format string specifying how the result should look. 一个格式字符串,指定结果的外观。 It can contain $0 , $1 , $2 , and so on. 它可以包含$0$1$2等。 $0 is the entire match, $1 and up correspond to the respective match groups in the regular expression. $0是整个匹配项, $1和up对应于正则表达式中的各个匹配项组。 Defaults to $0 . 默认为$0

    Some examples 一些例子

    Extracting an email address: 提取电子邮件地址:

    =regex("Peter Gordon: some@email.com, 47", "\w+@\w+\.\w+")
    =regex("Peter Gordon: some@email.com, 47", "\w+@\w+\.\w+", "$0")
    

    Results in: some@email.com 结果: some@email.com

    Extracting several substrings: 提取几个子字符串:

    =regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")
    

    Results in: E-Mail: some@email.com, Name: Peter Gordon 结果在: E-Mail: some@email.com, Name: Peter Gordon

    To take apart a combined string in a single cell into its components in multiple cells: 要将单个单元格中的组合字符串分解成多个单元格中的组件,请执行以下操作:

    =regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 1)
    =regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 2)
    

    Results in: Peter Gordon some@email.com ... 结果: Peter Gordon some@email.com ...

    How to use 如何使用

    To use this UDF do the following (roughly based on this Microsoft page . They have some good additional info there!): 要使用此UDF,请执行以下操作(大致基于此Microsoft页面 。它们在此处有一些不错的附加信息!):

    1. In Excel in a Macro enabled file ('.xlsm') push ALT+F11 to open the Microsoft Visual Basic for Applications Editor. 在Excel中的启用宏的文件('.xlsm')中,按ALT+F11打开Microsoft Visual Basic for Applications编辑器。
    2. Add VBA reference to the Regular Expressions library (shamelessly copied from Portland Runners++ answer ): 将VBA参考添加到正则表达式库中(从Portland Runners ++ answer中无耻地复制):
      1. Click on Tools -> References (please excuse the german screenshot) 单击工具 -> 参考 (请原谅德语截图) 工具->参考
      2. Find Microsoft VBScript Regular Expressions 5.5 in the list and tick the checkbox next to it. 在列表中找到Microsoft VBScript正则表达式5.5 ,然后选中它旁边的复选框。
      3. Click OK . 单击确定
    3. Click on Insert Module . 单击插入模块 If you give your module a different name make sure the Module does not have the same name as the UDF below (eg naming the Module Regex and the function regex causes #NAME! errors). 如果你给你的模块不同的名称确保模块具有相同的名称,下面的UDF(如命名模块Regex和函数regex导致#NAME!错误)。

      图标行中的第二个图标->模块

    4. In the big text window in the middle insert the following: 在中间的大文本窗口中,插入以下内容:

       Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object Dim replaceNumber As Integer With inputRegexObj .Global = True .MultiLine = True .IgnoreCase = False .Pattern = matchPattern End With With outputRegexObj .Global = True .MultiLine = True .IgnoreCase = False .Pattern = "\\$(\\d+)" End With With outReplaceRegexObj .Global = True .MultiLine = True .IgnoreCase = False End With Set inputMatches = inputRegexObj.Execute(strInput) If inputMatches.Count = 0 Then regex = False Else Set replaceMatches = outputRegexObj.Execute(outputPattern) For Each replaceMatch In replaceMatches replaceNumber = replaceMatch.SubMatches(0) outReplaceRegexObj.Pattern = "\\$" & replaceNumber If replaceNumber = 0 Then outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value) Else If replaceNumber > inputMatches(0).SubMatches.Count Then 'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "." regex = CVErr(xlErrValue) Exit Function Else outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1)) End If End If Next regex = outputPattern End If End Function 
    5. Save and close the Microsoft Visual Basic for Applications Editor window. 保存并关闭“ Microsoft Visual Basic for Applications编辑器”窗口。


    #4楼

    Here is my attempt: 这是我的尝试:

    Function RegParse(ByVal pattern As String, ByVal html As String)
        Dim regex   As RegExp
        Set regex = New RegExp
    
        With regex
            .IgnoreCase = True  'ignoring cases while regex engine performs the search.
            .pattern = pattern  'declaring regex pattern.
            .Global = False     'restricting regex to find only first match.
    
            If .Test(html) Then         'Testing if the pattern matches or not
                mStr = .Execute(html)(0)        '.Execute(html)(0) will provide the String which matches with Regex
                RegParse = .Replace(mStr, "$1") '.Replace function will replace the String with whatever is in the first set of braces - $1.
            Else
                RegParse = "#N/A"
            End If
    
        End With
    End Function
    

    #5楼

    I needed to use this as a cell function (like SUM or VLOOKUP ) and found that it was easy to: 我需要将其用作单元函数(例如SUMVLOOKUP ),发现很容易:

    1. Make sure you are in a Macro Enabled Excel File (save as xlsm). 确保您在启用宏的Excel文件中(另存为xlsm)。
    2. Open developer tools Alt + F11 打开开发人员工具Alt + F11
    3. Add Microsoft VBScript Regular Expressions 5.5 as in other answers 在其他答案中添加Microsoft VBScript正则表达式5.5
    4. Create the following function either in workbook or in its own module: 在工作簿中或在其自己的模块中创建以下函数:

       Function REGPLACE(myRange As Range, matchPattern As String, outputPattern As String) As Variant Dim regex As New VBScript_RegExp_55.RegExp Dim strInput As String strInput = myRange.Value With regex .Global = True .MultiLine = True .IgnoreCase = False .Pattern = matchPattern End With REGPLACE = regex.Replace(strInput, outputPattern) End Function 
    5. Then you can use in cell with =REGPLACE(B1, "(\\w) (\\d+)", "$1$2") (ex: "A 243" to "A243") 然后,您可以在带有=REGPLACE(B1, "(\\w) (\\d+)", "$1$2") (例如:“ A 243”到“ A243”)的单元格中使用


    #6楼

    Expanding on patszim 's answer for those in a rush. 急于扩展patszim答案

    1. Open Excel workbook. 打开Excel工作簿。
    2. Alt + F11 to open VBA/Macros window. Alt + F11打开VBA /宏窗口。
    3. Add reference to regex under Tools then References 以正则表达式下添加工具 ,然后引用参考
      ![Excel VBA表单添加参考
    4. and selecting Microsoft VBScript Regular Expression 5.5 并选择Microsoft VBScript正则表达式5.5
      ![Excel VBA添加正则表达式参考
    5. Insert a new module (code needs to reside in the module otherwise it doesn't work). 插入一个新模块(代码需要驻留在模块中,否则它将不起作用)。
      ![Excel VBA插入代码模块
    6. In the newly inserted module, 在新插入的模块中,
      ![Excel VBA将代码插入模块
    7. add the following code: 添加以下代码:

       Function RegxFunc(strInput As String, regexPattern As String) As String Dim regEx As New RegExp With regEx .Global = True .MultiLine = True .IgnoreCase = False .pattern = regexPattern End With If regEx.Test(strInput) Then Set matches = regEx.Execute(strInput) RegxFunc = matches(0).Value Else RegxFunc = "not matched" End If End Function 
    8. The regex pattern is placed in one of the cells and absolute referencing is used on it. 将正则表达式模式放在其中一个单元格中,并在其上使用绝对引用 ![Excel regex函数的单元内用法 Function will be tied to workbook that its created in. 功能将与其在其中创建的工作簿联系在一起。
      If there's a need for it to be used in different workbooks, store the function in Personal.XLSB 如果需要在不同的工作簿中使用它,请将函数存储在Personal.XLSB中

    展开全文
  • 虽然开发出此功能,请使用过程务必要保持节制,在可以称为报表功能的表格上使用,对用于作数据源的部分尽最大的可能性少用。用了合并单元格,同时提供了一剂解药,反合并单元格,将合并单元格打散后再恢复规范的...

    在高级Excel用户群体中无比痛恨的合并单元格,在现实的表格中却阴魂不散的纠缠不断。今天Excel催化剂也来成为“帮凶”,制造更多的合并单元格。虽然开发出此功能,请使用过程中务必要保持节制,在可以称为报表功能的表格上使用,对用于作数据源的部分尽最大的可能性少用。
    用了合并单元格,同时提供了一剂解药,反合并单元格,将合并单元格打散后再恢复规范的数据。

    视频演示

    Excel催化剂已正式在千聊上发布视频,如查阅文章有理解障碍,不妨查看下视频,视频不定期更新,内容丰富,干货满满,有术亦有道!

    推广期间有砍价购活动,白菜价59元,更送知识星球门票50元,具体可微信笔者获取详情。

    千聊视频,长按二维码直达
    千聊视频,长按二维码直达

    业务场景

    若一刀切无限攻击合并单元格不好,这个有所妥当,毕竟在报表层面有了合并单元格,排版的美化程度得到很大的提升,例如一般看到的打印版的内容,都大量使用了合并单元格。

    就算在数据源中,有时数据源临时需要用作报表的方式查看,也勉强可以接受使用合并单元格,但深盼众多小白、初中级Excel用户们,能够在Excel催化剂的正确Excel使用方法论的基础上去灵活使用合并单元格,而不是随意地造成如今的无穷混乱的Excel数据源世界。

    在前面提供的报表格式转标准数据源和标准数据源转报表格式两大功能上,已经详尽地描述过报表和数据源的分工使用和一些最佳的使用方式,这里不再重复,甚至也录制了大篇幅的视频教程了。

    合并单元格永远最恰当的场景是用于报表类型的数据源,这个要谨记!

    其实开发此功能,是源于手里一个棘手的问题,怎样在合并单元格上插入图片,虽然之前开发的插入图片功能,已经足够覆盖众多场景,再加上自由报表的生成已经足够灵活运用。

    用户永远是懒惰的,多个步骤,可能就有些受不了,回归正传,谈谈实际的场景,如下图所示:

    原始数据中一些数量指标,或还有更多的其他指标,在服饰行业中,SKU由款号+颜色+尺码组成。
    之前的插入图片功能不太好使,如果可以使用合并单元格方式再插入图片到合并单元格上,效果就不错。

    需要可视化商品款号,方便查阅
    需要可视化商品款号,方便查阅

    最终的想实现的效果如下:
    今天很高兴告诉大家,在Excel催化剂里已经可以轻松实现。插入图片也有众大更新补充可用于合并单元格和批注的图片插入。

    最终预想效果
    最终预想效果

    具体功能

    有批量取消合并单元格,并赋值原合并单元格区域内的所有单元格相同的原内容。

    有对行或都列的相同内容进行批量合并合并单元格功能,同时细分出合并单元格是只按原生的方式只保留第1个单元格有值,还是合并单元格内所有单元格都有值供其他函数引用调用两种。

    合并单元格相关功能
    合并单元格相关功能

    批量取消合并赋相同值

    原来的合并单元格样式,公式引用后发现,仅有首个单元格有值。

    使用过程中,尽量选择有合并单元格,需要取消合并单元格的区域,不要全选表格等操作,否则程序遍历单元格太多可能会假死机。

    原格式
    原格式
    功能同样适用于多行多列的情况
    功能同样适用于多行多列的情况
    操作后的效果
    操作后的效果

    批量合并区域相同值-按列-留空

    使用场景如上面所说的插入合并单元格图片,合并单元格内只有首单元格有内容,图片仅插入一次即可。
    按列的意思为,判断相同的内容仅会按单列的顺序由上而下来对比,而不会多列单双同的单元格亦合并在一起,一般现实中规范的数据结构乃是一列数据代表一种属性或指标,不同列之间没有强关联性,如数量、金额、商品名称、年份、月份等列。

    选定需合并单元格的列,可选多列,不连续列亦可接受,无需频繁去操作多列。

     

    选择1、2、4列
    选择1、2、4列

    最终结果如预期所想,多列同时可合并单元格,且合并单元格内仅首个有内容。

     

    image.png
    image.png
    附带说明下新的插入合并单元格图片功能
    附带说明下新的插入合并单元格图片功能
    插入图片后最终效果
    插入图片后最终效果

    批量合并区域相同值-按列-全满

    和以上操作类似,一次可多选多列,全满填充的方式为,所有单元格保留原来的值不变,使用外部函数引用时不受影响。

    合并区域相同值-按行

    按行和按列的方式,比较类似,具体效果如下:

     

    按行合并,留空
    按行合并,留空
    按行合并,全满
    按行合并,全满

    总结

    Excel的应用场景十分丰富多样,虽然大部分场景不应该随便在数据源上使用合并单元格,但在节制有科学方法论指导下,使用合并单元格,有时产生的效果还是不错,Excel催化剂不止于做一些功能的开发,更想带给大家在最合适的时候用最合适的功能,而不是因一时的方便,致使后续的工作充满了许多的难处。

    最后广告一下,Excel催化剂将会在千聊上用视频来演绎此功能的使用,更直观和更容易掌握!

    系列文章

    一文带你全面认识Excel催化剂系列功能
    安装过程详解及安装失败解决方法
    第1波-工作表导航
    第2波-数字格式设置
    第3波-与PowerbiDesktop互通互联
    第4波-一大波自定义函数高级应用,重新定义Excel函数的学习和使用方法
    第5波-使用DAX查询从PowerbiDeskTop中获取数据源
    第6波-导出PowerbiDesktop模型数据字典
    第7波-智能选区功能
    第8波-快速可视化数据
    第9波-数据透视表自动设置
    第10波-快速排列工作表图形对象
    第11波-快速批量插入图片
    第12波-快速生成、读取、导出条形码二维码
    第13波-一键生成自由报表
    第14波-一键生成零售购物篮分析
    第15波-接入AI人工智能NLP自然语言处理
    第16波-N多使用场景的多维表转一维表
    第17波-批量文件改名、下载、文件夹创建等
    第18波-在Excel上也能玩上词云图
    第19波-Excel与Sqlserver零门槛交互-查询篇
    第20波-Excel与Sqlserver零门槛交互-数据上传篇
    第21波-Excel与Sqlserver零门槛交互-执行SQL
    第22波-Excel文件类型、密码批量修改,补齐Power短板
    第23波-非同一般地批量拆分工作表
    第24波-批量发送邮件并指点不同附件不同变量
    第25波-小白适用的文本处理功能
    第26波-正确的Excel密码管理之道
    第27波-Excel工作表设置快捷操作
    第28波-工作薄瘦身,安全地减少非必要冗余
    第29波-追加中国特色的中文相关自定义函数
    第30波-工作表快捷操作(批量创建、命名、排序、工作表目录) 
    第31波-数量金额分组凑数功能,财务表哥表姐最爱
    第32波-空行空列批量插入和删除
    第33波-报表形式数据结构转标准数据源
    第34波-提取中国身份证信息、农历日期转换相关功能
    第35波-Excel版最全单位换算,从此不用到处百度找答案
    第36波-新增序列函数用于生成规律性的循环重复或间隔序列
    第37波-把Sqlserver的强大分析函数拿到Excel中用
    第38波-比Vlookup更好用的查找引用函数
    第39波-DotNet版的正则处理函数
    第40波-工资、年终奖个人所得税计算函数
    第41波-文件文件夹相关函数
    第42波-任意字符指定长度随机函数
    第43波-文本处理类函数增强
    第44波-可见区域复制粘贴不覆盖隐藏内容
    第45波-逻辑判断函数增强
    第46波-区域集合函数,超乎所求所想 
    第47波-VBA开发者喜爱的加密函数类
    第48波-拆分工作薄内工作表,堪称Excel界的单反
    第49波-标准数据结构表转报表样式结果
    第50波-批量打印、导出PDF、双面打印功能
    第51波-聚光灯功能,长宽工作表不看错位使用

    关于Excel催化剂

    Excel催化剂先是一微信公众号的名称,后来顺其名称,正式推出了Excel插件,插件将持续性地更新,更新的周期视本人的时间而定争取一周能够上线一个大功能模块。Excel催化剂插件承诺个人用户永久性免费使用!

    Excel催化剂插件使用最新的布署技术,实现一次安装,日后所有更新自动更新完成,无需重复关注更新动态,手动下载安装包重新安装,只需一次安装即可随时保持最新版本!

    Excel催化剂插件下载链接:https://pan.baidu.com/s/1kDtFkM5KZ4R1lAO0TO07AA

    因插件使用VSTO开发技术完成,插件的安装需要电脑满足相关的环境配置才能运行,且需可连接外网的方式实现自动更新机制,若下载安装过程中有任何疑问或需要离线版安装等,尽量不单独私聊询问,加QQ群可高效解决(群内已汇集了VSTO开发、Powerbi技术、Sqlserver商业智能等方面的国内顶尖大牛人物,进群的好处不用多说了)

    Excel催化剂插件交流群群二维码
    Excel催化剂插件交流群群二维码
    联系作者
    联系作者
    公众号
    公众号

    取名催化剂,因Excel本身的强大,并非所有人能够立马享受到,大部分人还是在被Excel软件所虐的阶段,就是头脑里很清晰想达到的效果,而且高手们也已经实现出来,就是自己怎么弄都弄不出来,或者更糟的是还不知道Excel能够做什么而停留在不断地重复、机械、手工地在做着数据,耗费着无数的青春年华岁月。所以催生了是否可以作为一种媒介,让广大的Excel用户们可以瞬间点燃Excel的爆点,无需苦苦地挣扎地没日没夜的技巧学习、高级复杂函数的烧脑,最终走向了从入门到放弃的道路。

    最后Excel功能强大,其实还需树立一个观点,不是所有事情都要交给Excel去完成,也不是所有事情Excel都是十分胜任的,外面的世界仍然是一个广阔的世界,Excel只是其中一枚耀眼的明星,还有其他更多同样精彩强大的技术、工具等。*Excel催化剂也将借力这些其他技术,让Excel能够发挥更强大的爆发!

    关于Excel催化剂作者

    姓名:李伟坚,从事数据分析工作多年(BI方向),一名同样在路上的学习者。
    服务过行业:零售特别是鞋服类的零售行业,电商(淘宝、天猫、京东、唯品会)

    技术路线从一名普通用户,通过Excel软件的学习,从此走向数据世界,非科班IT专业人士。
    历经重重难关,终于在数据的道路上达到技术平原期,学习众多的知识不再太吃力,同时也形成了自己的一套数据解决方案(数据采集、数据加工清洗、数据多维建模、数据报表展示等)。

    擅长技术领域:Excel等Office家族软件、VBA&VSTO的二次开发、Sqlserver数据库技术、Sqlserver的商业智能BI技术、Powerbi技术、云服务器布署技术等等。

    2018年开始职业生涯作了重大调整,从原来的正职工作,转为自由职业者,暂无固定收入,暂对前面道路不太明朗,苦重新回到正职工作,对Excel催化剂的运营和开发必定受到很大的影响(正职工作时间内不可能维护也不可能随便把工作时间内的成果公布于外,工作外的时间也十分有限,因已而立之年,家庭责任重大)。

    和广大拥护者一同期盼:Excel催化剂一直能运行下去,我所惠及的群体们能够给予支持(多留言鼓励下、转发下朋友圈推荐、小额打赏下和最重点的可以和所在公司及同行推荐推荐,让我的技术可以在贵司发挥价值,实现双赢(初步设想可以数据顾问的方式或一些小型项目开发的方式合作)。

    转载于:https://www.cnblogs.com/ExcelCuiHuaJi/p/10775617.html

    展开全文
  • //外部表格循环 while (tab.hasNext()){ Table table = tab.next(); System.out.println("第\t"+(pivot+1)+"\t个表"); //pivot==0表示第一个表 if (pivot==0){ //获取第一行第一列,需要获取这个单元格...
  • 案例思想是源于前两天帮读者做了一个 demo ,需求大致将一上百个 word 表格内容提取出来(所有word 表格样式一样),把提取到的内容自动存入 Excel word 表格形式如下 目前含有数个上面形式的 word 文档需要...
  • 有许多方法可以在没有循环的情况下完成此操作.大多数解决方案涉及将向量x和y扩展为更大的索引矩阵,并且可能使用函数REPMAT,BSXFUN或SUB2IND的一个或多个.可以在here找到用于矩阵索引的良好教程.但是,既然你要求一...
  • Python—提取Word的文本内容 参考博客:Python读取word文档内容
  • 广告关闭腾讯云11.11云上盛惠 ,精选热门产品助力上云,云服务器首年88元起,买的越多返的越多,最高返5000元!... 使用循环判断是合并单元格还是普通单元格,并将合并单元格中的首行值赋值给合并单元格...
  • 批量提取多个Excel文件内指定单元格的数据(文件名和数据) 例如收集了excel表格统一格式的个人简历,从中抽取个人基本信息,生成数据表。 将源数据excel文件放在同一个文件夹下。 新建一个shouji.xls也放在该...
  • word 2010 宏的使用Word对宏的定义Word宏的优缺点Word宏运用实例---批量提取word数据结语 Word对宏的定义 Microsoft Word对宏定义为:“宏就是能组织到一起作为一独立的命令使用的一系列word命令,它能使日常...
  • 在日常数据处理过程,需要对缺失数据进行填充时,按一定逻辑规则进行处理,实现快速填充,规范数据源。此篇给大家带来多种填充数据的场景。 业务使用场景 对各种系统导出的数据,很多时候存在数据缺失的情况...
  • VBA 数组导入单元格

    千次阅读 2013-05-31 15:09:53
    第3例我们学会了如何把单元格中的数据搬入内存,变身为VBA数组。那么,VBA数组怎么输回到单元格中呢?就象菜做好了,怎么更快的上桌呢?1、二维数组的导入:VBA常见的数组形式有一维数组和二维数组,相比一维数组...
  • 20190407 Word合并单元格

    2019-04-07 01:32:00
    很长一段时间没处理word合并单元格,又忘记了采取忽略错误的方式测试出相应单元格的行列坐标这种方式。真是浪费时间。以后再也不想为此在深夜熬命。 今晚算是和它杠上了,很想弄清楚合并单元格之后行列坐标重新分配...
  • StructFind,在结构体查找某个字符串或值的索引 index=structfind(a,field,value) 输入, a : 一个 Matlab 结构体,例如 a(1).name='red', a(2).name='blue'; field : 搜索字段的名称,例如 'name' value : 搜索...
  • 在使用Matlab处理Excel数据的时候,有时常常需要先获取Excel单元格的位置,然后再执行读取或写入的操作,比如xlsread和xlswrite两个函数的xlRange,还有actxserver函数。 以前的做法,通常是先对要处理的数据做个...
  • 返回目录 以下知识源码位置: [Git: ... ... 本节概要:首先对项目进行两点小优化,然后介绍如何点亮单元格对应的行列标题。...首先修正一个bug,在编辑单元格之后,...
  • Email:1563178220@qq.com 内容可能有不到之处,欢迎交流。 未经本人允许禁止转载。 问题情境 在word存在多个表格,每个表格会有一些使用不同颜色标注的高亮文本,那么如何利用编程语言自动的提出这些高亮文本呢?...
  • 提取Excel特定间隔的数据

    千次阅读 2018-05-07 09:48:10
    主要是对Excel数据进行处理,D列主要是对D列数据求一个平均值,fty1-fty2000张图片,每张图片有60人对其进行打分,D1=AVERAGE(C1:C60),然后选中D2,在fx的左边方框输入D1:D...循环得到一些结果,我们在E1输入公...
  • vbscript 对excel常见操作dim oExcel,oWb,oSheet Set oExcel= CreateObject("Excel.Application") Set oWb = oExcel.... MsgBox oSheet.Range("B2").Value '#提取单元格B2内容 '..... <b
  • 最近工作需要提取几千个excel表的指定区域的数据,如图提取此sheet红色区域部分数据: ![图片说明](https://img-ask.csdn.net/upload/201912/23/1577085176_166774.png) 红色区域在每个文件的行列都...
  • 前几节讲了表格控件的用法,应该是可以操作大部分单元格控件了。 今天接着讲一下,表格控件常常用到的一个技巧,单元格变色。作者本人在写上位机程序的时候就经常用到这个。试验一个元件,合格变绿色,不合格变...
  • 大家好,从PDF中提取信息是办公场景经常需要用到的操作,也是经常又读者在后台问的一个操作。内容少的话我们可以手动复制粘贴,但如果需要批量提取就可以考虑使用Python,之前我也转载过相关...
  • 利用Python+Pandas实现从一个excel表中提取列形成新表

    千次阅读 多人点赞 2020-07-09 15:01:51
    利用python实现把一个工作表的某些列,和其中单独的一列,提取成为一个个新表。 如图(处理前)蓝色部分是需要保留的列,红色是需要一项一项分出来作为单独表格的列。 其中,第一行的名字是用的回车作为分隔,所以...
  • 一、问题描述利用Excel制作下拉菜单,当某一列有空白单元格时,如下图所示 在下拉菜单,也会存在空白单元格 常规制作下拉菜单的方法没有办法避免该问题,所以需要利用VBA解决这个问题二、解决方法解决该问题的VBA...
  • 问题描述:假设有Word文件“Python题库.docx”有若干Python题目(目前有1000道,已在公众号内分享第一期1000道Python题库系列分享一(17道))和对应的答案。其...
  • 原标题:从几百个Excel查找数据,用Python一分钟搞定今天给大家分享一个真实的办公自动化需求,日常办公肯定都会遇到的,大家一定要仔细阅读需求说明,在理解需求之后即可体会Python的强大!一、需求说明首先我们...
  • 在片区内部,要找到需提取数据在片区内的分布规律,准确计算出单元格名称或所在行列号,然后用SPL的xlscell函数读取单元格值。 举例 员工信息表employee.xlsx中部分数据如下图所示,每个员工信息片区占9行7列,在...

空空如也

空空如也

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

如何循环提取单元格中的部分内容