☰

Excel - 각 값 목록에 대해 여러 값을 반환합니다.

tag-icon • tag-icon microsoft-excel worksheet-function vlookup
Excel - 각 값 목록에 대해 여러 값을 반환합니다.

몇 시간 동안 이 문제를 해결하려고 노력했지만 결과가 없습니다.

내 설정:

  • 엑셀 시트가 있어요화장품 제품 레시피.

  • 각 레시피에는 수십 가지 재료가 포함되어 있으며 각 재료는 해당 레시피에 따라 제공됩니다.상표명.

  • 각 상품명을 해당 상품명으로 번역한 긴 목록이 있습니다.'과학적인 이름.

각 상품명을 과학적 대응어로 번역해야 합니다. 이 부분은 vlookup을 사용하면 매우 쉽습니다.

하지만 일부상표명~이다조리법그 자체로 표에 여러 성분이 포함되어 있습니다.학명. 따라서 1:1 관계가 아니며 때로는(항상은 아니지만) 1:다 관계가 됩니다.

예를 들어:

화장품 A시트

 IngredientA_trade_name

 IngredientB_trade_name

 IngredientC_trade_name

학명 목록 마스터 파일로 거래:

 IngredientA_trade_name     Science1
 IngredientB_trade_name     Science2
 IngredientB_trade_name     Science3
 IngredientB_trade_name     Science4    
 IngredientC_trade_name     Science5
 IngredientC_trade_name     Science6

 ....etc, for lots and lots of ingredients.

이제 여러 값을 반환하는 방법을 찾았지만 다음 중 어느 것도 자동으로 작업을 수행하지 않습니다.

나는 넣어야한다재료셀에 이름을 지정한 다음 수식을 옆에 있는 셀에 놓고 몇 군데 아래로 끌어서 가능한 모든 일치 항목을 자동으로 채웁니다. 일부 다른 솔루션은 반환 값을 단일 셀(Science2,Science3,Science4)에 넣습니다. 이는 조금 더 좋지만 나머지 작업 흐름에서는 작동하지 않습니다.

상품명 목록을 살펴보고 필요에 따라 모든 학명이 포함된 행을 삽입할 수 있는 방법이 있습니까?

답변1

VBA를 사용하는 이 매크로는 다음 작업을 수행합니다.

Public Sub ingredients()
    Dim wkb As Workbook
    Dim wks, wks1 As Worksheet
    Set wkb = ThisWorkbook
    Set wks = ActiveSheet
    wks.Application.ScreenUpdating = False
    mastername = "Master"
    totalsheets = wkb.Worksheets.Count
    For i = 1 To totalsheets
        Set wks1 = wkb.Worksheets(i)
        wks1name = wks1.Name
        If wks1name = mastername Then
            i = totalsheets
        Else
            Set wks1 = Nothing
        End If
    Next i
    reviewing = True
    activerow = 1
    While reviewing
        tradename = wks.Cells(activerow, 1)
        If tradename = "" Then
            reviewing = False
        End If
        reviewingmaster = True
        activerowmaster = 1
        found = 0
        While reviewingmaster
            sciname = wks1.Cells(activerowmaster, 1)
            If sciname = "" Then
                reviewingmaster = False
            End If
            If sciname = tradename Then
                found = found + 1
                If found > 1 Then
                    activerow = activerow + 1
                    wks.Rows(activerow).Insert
                End If
                wks.Cells(activerow, 2) = wks1.Cells(activerowmaster, 2)
                wks.Cells(activerow, 3) = wks1.Cells(activerowmaster, 3)
            End If
            activerowmaster = activerowmaster + 1
        Wend
        activerow = activerow + 1
    Wend
    wks.Application.ScreenUpdating = True
    theend = MsgBox("Finished on " & wks.Name, vbInformation)
End Sub

매크로/VBA를 열고 아래에 모듈을 삽입해야 합니다.이워크북그리고 오른쪽에 코드를 붙여넣으세요.

다음 사항에 유의하세요.

  • 코드는 활성 워크시트에서 실행됩니다.
  • 상표명의 첫 번째 이름을 제외한 모든 새로운 과학 이름은 새 행에 추가됩니다.
  • 워크시트의 A열에 빈 셀이 없으면 코드가 작동할 준비가 되어 있습니다.
  • 마스터 목록 워크시트의 이름은 다음과 같다고 가정합니다.주인mastername="Master"해당 줄을 적절한 이름으로 변경해야 합니다 .
  • 500개의 상표명과 5000개의 학명을 설정하는 데 23초가 걸렸습니다.

답변2

Excel 2010 또는 2013의 경우 이를 위해 파워 쿼리 추가 기능을 사용합니다. Excel 2016부터 파워 쿼리는 "가져오기 및 변환" 섹션 아래의 데이터 리본에 기본 제공됩니다.

파워 쿼리는 Excel 테이블에서 시작할 수 있습니다. 쿼리를 함께 결합할 수 있는 병합 명령과 일치 항목만 유지하는 옵션이 있습니다. 쿼리 결과는 Excel 테이블에 기록될 수 있습니다.

코드가 필요하지 않고 요구 사항을 파워 쿼리에서 디자인하는 데 몇 분 정도 걸립니다.

...

이 답변을 확장하는 데 유용한 피드백에서 영감을 받았습니다. 실제로 저는 OneDrive에서 다운로드하여 시험해 볼 수 있는 작동하는 솔루션을 구축했습니다.

http://1drv.ms/1AzPAZp

파일은 다음과 같습니다.파워 쿼리 데모 - 각 값 목록에 대해 여러 값을 반환합니다.

구축하는 데 2분 미만이 걸렸으며(입력 데이터 복사 및 Read Me 시트 작성은 제외) 코드/함수가 필요하지 않았습니다.

핵심 기술은 여기에 설명된 대로 병합 및 확장입니다.

https://support.office.com/en-us/article/Merge-queries-Power-Query-fd157620-5470-4c0f-b132-7ca2616d17f9

관련 정보