如何从两个不同的工作表中匹配相同的名称后获取一个％值 [英] how to get a % value after matching same name from 2 different worksheets

问题描述

我想知道有人能帮我解决以下问题吗？以前有人帮助我获得了同一工作表上的 dctest / In 值的％。但是现在，我需要做同样的事情，但是在另一种工作表上。

说Sheet1

< img src =https://i.stack.imgur.com/N5YDA.pngalt =enter image description here>

这是复制Sheet1（1 ）在采取％

  Sub marco1（）
 
'开始制作Sheet1到％
'~~> ;添加/删除要忽略的文本
 Excludetext =In，test1，test2，test3，test4，test5，test6
 
 MyArray = Split（Excludetext，，） 
 
设置ws =表（Sheet1）
 LastRow = ws.Range（A& Rows.Count）.End（xlUp）.Row 
 
'将列B设置为％
对于i = 1 To LastRow 
 boolContinue = True 
 
对于j = 0到UBound（MyArray）
 SearchText = UCase（Trim （MyArray（j）））
如果UCase（Trim（ws.Range（A& i）.Value））= SearchText然后
 boolContinue = False 
退出
结束如果
下一步j 
 
如果boolContinue = True然后
带范围（B& i）
 .Formula = _ 
= OFFSET（INDEXECT（ADDRESS（INDEX（MATCH（A& $&$ 
，$ A $ 1：$ A $ 45,0），1,0），1,1,1，Duplicated_Sheet1 ）$ 0,1 $ / $ B $ 5
 .NumberFormat =0.00％
 End with 
 End If 
 Next i 
 End sub 
  $ / pre> 
 
 
在公式中显示一些错误，我用公式做错了吗？谢谢你提前！
解决方案
这是你正在尝试的吗？
 
 
 
  TRIED AND TESTED  
  Option Explicit 
 
 Sub Sample 
 Dim ws As Worksheet 
 Dim wsData As String 
 Dim SearchText As String，Excludetext As String 
 Dim LastRow As Long，i As Long，j As Long 
 Dim MyArray （）As String 
 Dim boolContinue As Boolean 
 
'~~>添加/删除文本
 Excludetext =In，Test1，Test2，Test3，Test4，Test5，Test6
 
'~~>将其更改为具有数据的相关工作表
 wsData =Sheet1
 
 MyArray = Split（Excludetext，，）
 
设置ws = Sheet2）
 LastRow = ws.Range（A& Rows.Count）.End（xlUp）.Row 
 
对于i = 1 To LastRow 
 boolContinue = True 
 
对于j = 0到UBound（MyArray）
 SearchText = MyArray（j）
如果ws.Range（A& i）.Value = SearchText Then 
 boolContinue = False 
退出
结束如果
下一步j 
 
如果boolContinue = True然后
使用ws.Range（B& amp ; i）
 .Formula = _ 
= OFFSET（INDIRECT（ADDRESS（INDEX（MATCH（A& I& _ 
，& $ wsData& A $ 1：$ A $ 11,0），1,0），1,1，TRUE，& _ 
 wsData&）），0,1）/& wsData& ！B1
 .NumberFormat =0.00％
 End with 
 End If 
 Next i 
 End Sub 
  
在不同工作表中为单元格使用ADDRESS（）时，必须指定其他参数。
 
 
 
 直接从Excel的帮助 
 
 
 
 ADDRESS功能的语法
  ADDRESS（row_num，column_num，[abs_num]，[a1]，[sheet_text]）
  
其中 [sheet_text] 是我们所指的表单的名称。我会建议您在Excel帮助中阅读更多信息。
 
 
 
这是实际的公式表示 dctest  
  = OFFSET（INDIRECT（ADDRESS（INDEX（MATCH（A7，Sheet1！$ A $ 1：$ A $ 11,0），1,0），1,1， TRUE，Sheet1）），0,1）/ Sheet1！B1 
  
 HTH 
 
 
 
 Sid 
 
I was wondering if someone can help me solve the following problem. Someone has previously helped me on get the % of dctest/In value on the same worksheet. But right now, i need to do the same thing but on a different worksheet.

Say Sheet1



this is copied Sheet1 (1) after taking the %

Sub marco1()

'start making Sheet1 into %
'~~> Add/Remove the text here which you want to ignore
Excludetext = "In,test1,test2,test3,test4,test5,test6"

MyArray = Split(Excludetext, ",")

Set ws = Sheets("Sheet1")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

'Set Column B into %
For i = 1 To LastRow
    boolContinue = True

    For j = 0 To UBound(MyArray)
        SearchText = UCase(Trim(MyArray(j)))
        If UCase(Trim(ws.Range("A" & i).Value)) = SearchText Then
            boolContinue = False
            Exit For
        End If
    Next j

    If boolContinue = True Then
        With Range("B" & i)
            .Formula = _
            "=OFFSET(INDIRECT(ADDRESS(INDEX(MATCH(A" & i & _
            ",$A$1:$A$45,0),1,0),1,1,1,'Duplicated_Sheet1')),0,1)/$B$5"
            .NumberFormat = "0.00%"
        End With
    End If
Next i
End sub
There is some error showing at the formula, did i make a mistake with the formula? Thank you in advance!
解决方案
Is this what you are trying?

TRIED AND TESTED
Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim wsData As String
    Dim SearchText As String, Excludetext As String
    Dim LastRow As Long, i As Long, j As Long
    Dim MyArray() As String
    Dim boolContinue As Boolean

    '~~> Add/Remove the text here
    Excludetext = "In,Test1,Test2,Test3,Test4,Test5,Test6"

    '~~> Change this to the relevant sheetname which has the data
    wsData = "Sheet1"

    MyArray = Split(Excludetext, ",")

    Set ws = Sheets("Sheet2")
    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To LastRow
        boolContinue = True

        For j = 0 To UBound(MyArray)
            SearchText = MyArray(j)
            If ws.Range("A" & i).Value = SearchText Then
                boolContinue = False
                Exit For
            End If
        Next j

        If boolContinue = True Then
            With ws.Range("B" & i)
                .Formula = _
                "=OFFSET(INDIRECT(ADDRESS(INDEX(MATCH(A" & i & _
                "," & wsData & "!$A$1:$A$11,0),1,0),1,1,TRUE,""" & _
                wsData & """)),0,1)/" & wsData & "!B1"
                .NumberFormat = "0.00%"
            End With
        End If
    Next i
End Sub
When using ADDRESS() for a cell in different sheet, you have to specify additional arguments.

Straight from Excel's help

Syntax of ADDRESS Function
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
Where [sheet_text] is the name of the sheet which we are referring to. I would recommend reading more about it in Excel Help.

This is the actual formula for say dctest 
=OFFSET(INDIRECT(ADDRESS(INDEX(MATCH(A7,Sheet1!$A$1:$A$11,0),1,0),1,1,TRUE,"Sheet1")),0,1)/Sheet1!B1
HTH

Sid

                        
这篇关于如何从两个不同的工作表中匹配相同的名称后获取一个％值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！