☰

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.

現在、複数の値を返す方法を見つけましたが、いずれも自動的には機能しません。

私は材料セルに名前を入力し、その隣のセルに数式を配置して数か所下にドラッグすると、すべての可能な一致が自動入力されます。他のソリューションでは、戻り値を 1 つのセル (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 の場合は、Power Query アドインを使用します。Excel 2016 からは、Power Query は [取得と変換] セクションの [データ] リボンに組み込まれています。

Power Query は Excel テーブルから開始できます。クエリを結合できるマージ コマンドがあり、一致のみを保持するオプションもあります。クエリの結果は Excel テーブルに書き込むことができます。

要件を Power Query で設計するには、コードを必要とせず、数分しかかかりません。

...

役に立つフィードバックに触発されて、この回答を拡張しました。実際に、OneDrive からダウンロードして試すことができる実用的なソリューションを構築しました。

http://1drv.ms/1AzPAZp

ファイルは次のとおりです:Power Query デモ - 値のリストごとに複数の値を返す

ビルドには 2 分もかかりませんでした (入力データのコピーと Read Me シートの作成は除く)。コードや関数は必要ありませんでした。

重要なテクニックは、ここで説明するように、マージと拡張です。

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

関連情報