如何搜索和提取单元格中的某些值 - VBA [英] How to search and extract certain values in cells - VBA

问题描述

我有以下数据，可以通过以下链接看到：

输入和所需输出数据示例

使用以下逻辑： strong>

从Sheet1中提取所有JPM（编排者）的总值，并将其绘制在表2中的列下的每一行说JPM。例如，如果Sheet2中的B1等于BAML，则在表1中搜索BAML，并从列F中提取其所有总值，然后插入Sheet2 ，在BAML列下。

请注意：例如BAML本身可以在单元格中，或者可以在列C中的单元格中的任何其他位置。

我需要帮助和助手开发一个宏，可以在Sheet2中显示上述数据链接中显示的输出。我不确定，我该如何解决这个问题。

任何帮助都将非常感激。

请问

输入数据（Sheet1）

输出数据sheet2）

解决方案

如果您只需要每个协调者的总数，可以使用以下Excel公式来执行。基于贡献者名称的更改标准，例如JPM，CITG，BAML ...等等。

公式：

  = SUMIF（$ C $ 2：$ C $ 13，* JPM *，$ B $ 2：$ C $ 13）
  

输出

  JPM CITG BAML BCG CIBC DB 
 2432.74 500 500 500 1812.84 1812.84 
  

这对你来说足够了吗？
您是否想在分配的透视图中显示每个投放者的价格？

由于OP的请求是按行排序每个贡献者，VBA代码段已编码。为了更容易的视觉理解，解决方案的枢纽显示在您的源数据旁边。

以下是解决方案和输出的步骤。

1. 添加按钮您的工作表。


2. 双击该按钮并添加 Call splitSortPivot（）


3. 添加模块到您的Excel项目


4. 双击模块并复制以下代码


5. 保存，编译

<



代码：

  Option Explicit 
 
公开Sub splitSortPivot（）
 Dim ws As Worksheet 
 Dim sourceRange As Range 
 Dim rng As Range 
 Dim i，j，k As Integer 
 Dim sourceArray As Variant 
 Dim arrangersArray As Variant 
 Dim ary As Variant 
 
设置ws = Worksheets（Sheet1）
设置sourceRange = ws.Range（B2：C13）
 
'根据您在源表单（Sheet1）
'中的记录数量+2，您可以设置数组的第一个维度的上限
'+1用于标题和+ 1为总计 - >在当前情况下，12 + 1 + 1 = 14 
'即使设置此数字也可以使用C列中使用的行进行编程。 
 ReDim arrangersArray（0到13，5）
 
'可以优化代码以编程方式添加名称
 arrangersArray（0，0）=JPM
 arrangersArray （0，1）=CITG
 arrangegersArray（0,2）=BAML
 arrangegersArray（0,3）=BCG
 arrangegersArray（0，4）=CIBC 
 arrangegersArray（0，5）=DB
 
 sourceArray = sourceRange.Value 
 
对于j = LBound（sourceArray，1）到UBound（sourceArray， 1）
如果InStr（1，sourceArray（j，2），，）> 0然后
 ary = Split（sourceArray（j，2），，）
对于k = LBound（ary）到UBound（ary）
对于i = LBound（arrangersArray，2）然后
 arrangegersArray（j，i）= sourceArray（j，1）
 arrangegersArray（ 13，i）= arrangersArray（13，i）+ arrangegersArray（j，i）
 End If 
 Next i 
 Next k 
 Else 
对于k = LBound（ 2，对于UBound（编程数组，2）
如果arrangersArray（0，k）= sourceArray（j，2）然后
 arrangersArray（j，k）= sourceArray（j，1）
 arrangegersArray（13，k）= arrangersArray（13，k）+ arrangersArray（j，k）
 End If 
 Next k 
 End If 
 Next j 
 
'将处理后的数组输出到Sheet中。 
 Range（G1）。调整大小（UBound（arrangersArray）+ 1，_ 
 UBound（Application.Transpose（arrangersArray）））= arrangersArray 
 
 End Sub 
  

输出屏幕截图：







I have the following data, which can be seen by using the link below:

Example of Input and desired Output Data

Using the following logic:

Extract all of JPM (arranger’s) total values from "Sheet1" and plot them in sheet 2, on each row under the column that says JPM.

For example, if B1 in "Sheet2", is equals to BAML, then search BAML in sheet 1 and extract all its total values from column F and insert in "Sheet2", under the BAML column.

Please NOTE: for example BAML can be in a cell by itself or it can be in any other position in a cell in column C.

I require help and assistant in developing a macro which can display the output as shown in the data link above, in "Sheet2". I am not sure, how would I go about solving this problem.

Any help would be very much appreciated.

Kind regards

Input data (Sheet1)

Output data (Sheet2)

解决方案

If you only need the total of each cotributor, you can do it using following Excel formula. Change Criteria based on the contributor name, e.g. JPM, CITG, BAML... so on.

Formula:

=SUMIF($C$2:$C$13,"*JPM*",$B$2:$C$13)

Output

JPM      CITG    BAML    BCG     CIBC       DB
2432.74  500     500     500     1812.84    1812.84

Will this be sufficient for you? Else do you want to show price for each contributor in a distributted pivot view?

Since the OP's request is to sort prices by row for each contributor, a VBA snippet is coded. For easier visual understanding the solution pivot is displayed next to your source data.

Following is the steps to solution and output.

1. Add a button to your Sheet.
2. Double click the button and add Call splitSortPivot()
3. Add a module to your Excel project
4. Double click on the module and copy the following code
5. Save, compile

Code:

Option Explicit

Public Sub splitSortPivot()
Dim ws As Worksheet
Dim sourceRange As Range
Dim rng As Range
Dim i, j, k As Integer    
Dim sourceArray As Variant
Dim arrangersArray As Variant
Dim ary As Variant

Set ws = Worksheets("Sheet1")
Set sourceRange = ws.Range("B2:C13")

'Based on the number of records +2 you have in source sheet (Sheet1)
'you may set the first dimension's upper bound of the array
'+1 for header and +1 for total --> in current case 12  + 1 + 1 = 14
'even setting this number can be done programmatically using used rows in C column.
ReDim arrangersArray(0 To 13, 5)

'This code can be optimized to add names programmatically
arrangersArray(0, 0) = "JPM"
arrangersArray(0, 1) = "CITG"
arrangersArray(0, 2) = "BAML"
arrangersArray(0, 3) = "BCG"
arrangersArray(0, 4) = "CIBC"
arrangersArray(0, 5) = "DB"

sourceArray = sourceRange.Value

    For j = LBound(sourceArray, 1) To UBound(sourceArray, 1)
        If InStr(1, sourceArray(j, 2), ",") > 0 Then
            ary = Split(sourceArray(j, 2), ",")
            For k = LBound(ary) To UBound(ary)
                For i = LBound(arrangersArray, 2) To UBound(arrangersArray, 2)
                    If arrangersArray(0, i) = Trim(ary(k)) Then 
                       arrangersArray(j, i) = sourceArray(j, 1)
                       arrangersArray(13, i) = arrangersArray(13, i) + arrangersArray(j, i)
                    End If
                Next i
            Next k
        Else
            For k = LBound(arrangersArray, 2) To UBound(arrangersArray, 2)
                If arrangersArray(0, k) = sourceArray(j, 2) Then
                    arrangersArray(j, k) = sourceArray(j, 1)
                    arrangersArray(13, k) = arrangersArray(13, k) + arrangersArray(j, k)
                End If
            Next k
        End If
    Next j

'Output the processed array into the Sheet. 
Range("G1").Resize(UBound(arrangersArray) + 1, _ 
UBound(Application.Transpose(arrangersArray))) = arrangersArray

End Sub

Output screen shot:

这篇关于如何搜索和提取单元格中的某些值 - VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！