エクセルで複数列から任意の値を検索しその隣の値を表示したい。

Question

タイトル
エクセルで複数列から任意の値を検索しその隣の値を表示したい。

以下のような処理をエクセルでしたいのですが、色々検索してみましたが、できませんでした。
関数や数式を教えていただけると助かります。
また、データが重いので、できれば処理の軽め数式でできると助かります。

シート1
名称　　金額コード1　金額1　金額コード2　金額2　金額コード3　金額3・・・
***1　　1234　　　　10,000　　　1345　　12,000　　1500　　　1,000　
***2　　1345　　　　1,000　　　  1500　　2,000　
***3　　1100　　　　500　　　　 1234　　100 　　　 1500　　　600
***4　　1600　　　　2,500　　　 1700　　1,200　　　1800　　　4,500　
***5　　1234　　　　2,000
・・・

（説明）シート1には、10万件以上の行があります。
金額コード1で1列、金額1で1列で、1～99まであります。金額コード99、金額99が最後の列です。
シート1から、金額コード「1234」が1,000円以上のものを抽出したい→結果：名称「***1」と「***5」といった処理をしたいです。
1列にまとまっていないので、抽出方法に苦慮しています。
エクセルに詳しい方、どうか知恵を貸してください。
よろしくお願いいたします。

yuji3690 · Accepted Answer

重ねてすみません。
「（」が多いのではなくて「）」が足りなかったようです　（冷汗
=IF(SUMPRODUCT((MOD(COLUMN(B2:GR2),2)=0)*1,(B2:GR2=1234)*1,(C2:GS2>=1000)*1)>0,GU1+1,GU1)
に直してみてください。

MOD(COLUMN(B2:GR2),2)=0『)』*1　これで、（列番号÷２の余りが0？→TRUE）*1となるので数字と判定され1が出てきます。
括弧が無い為、列番号÷２の余りが0*1？→TRUE　となってしまい、TRUEは数字ではないので判定が0となってました。
申し訳ありません。

yuji3690 · Answer

すいません。これはこちらの打ち間違いですね。
MODの前の「(」が１つ不要です。
対応する「)」がない為エラーメッセージが出たのですね。
「（」を消して試してみてください。

yuji3690 · Answer

ちょっと画質が悪くて見づらいですね…
入力した式を直接こちらに打ってみてもらえますか？

今エクセルを使える所にいないので、返答はすぐに出来ないかもしれませんが…

yuji3690 · Answer

No4ですが、金額が1234になる場合もあるとの事ですので、がらっと式を変更します。
ただ、表示する内容は一緒ですので、AAAとした列の式を修正します。
この際、データの入力されている最終列の右側は空白の列が必要になりますので、
ZYまでデータが入っていて、ZZは空白、AAAにカウントする式、AABに名称　といった具合になります。

AAAの式ですが、
AAA2=IF(SUMPRODUCT((MOD(COLUMN(B2:ZY2),2)=0*1,(B2:ZY2=1234)*1,(C2:ZZ2>=1000)*1)>0,AAA1+1,AAA1)
これは、もし該当するものが存在すれば（＞0なら）１つ上のセルに１加えて表示。存在しなければ上のセルをそのまま表示。というのは同じです。

該当するものについてですが、MOD(COLUMN(B2:ZY2),2)=0というのが、B2～ZY2のセルの中で、列番号を２で割った余りが0の場合。つまり、偶数列目（＝金額コードの列）であるならば、１（*1の部分）とします。（違う場合は0です）
更に、(B2:ZY2=1234)というのが、B2～ZY2のセルの中で1234に該当する場合1（以下同様）
更に、(C2:ZZ2>=1000)というのが、C2～ZZ2のセル（つまりB2~ZY2のセルの右隣）が1000以上である場合1
これら３種類の判別をした結果の合計（全てに該当する場合1*1*1＝1とカウントされ、どこかに該当しない物があれば*0が含まれるので0となります。　それらの合計なので、全てに該当したセルの個数を表示します）が（最初の説明文に戻り）、＞0ならば・・・　となります。

いかがでしょうか？

ママチャリ · Answer

これを数式で実装してしまうと運用に耐えられないと思います。ひとつ値を変える度にクルクル回ってしまうでしょう。または、値を変えるときは手動計算にしておくとか…。
やはり、マクロでの実装が現実的です。
プログラムを手打ちしなければならないということなので、できるだけ短い（機能も限定）ものにしてみました。こんな感じです。

Sub sample()
    Set sr = Range("A1")
    For Each r In Selection
        If r.Column Mod 2 = 0 And _
            r.Offset(0, 1).Value >= 1000 Then
            Set sr = Union(sr, Cells(r.Row, "A"))
        End If
    Next r
    sr.Select
End Sub

上記のVBAを標準モジュールに打ち込んでから、次のように操作してください。
①「ホーム」タブの「検索と選択」から「検索」をクリック。
②検索文字列（1234）を入力して「すべて検索」をクリック。
③Ctrl+aですべてを選択状態にして、「×」でウィンドウを閉じる。
④上記のマクロを実行する（選択されているセルのA列が選択状態になります）。
⑤上記⑤で選択されたセルをCtrl+ｃでコピーし、適当なセルにペーストすればできあがり。

yuji3690 · Answer

とりあえずこちらの思っているとおりとして説明しますが、再確認したいこととして、
金額が1234円となる事はありませんか？
名称が1234となる事はありませんか？
なる場合改良するか考えを変えなければならない可能性がありますので。

最後尾の列番号が分からない（計算してないだけですが）ので、ZZ列までデータがあり、AAA列に新しい式を入れるとして記入します。
また、1行目がタイトル行、2行目からデータとしています。
そしてAAA1は空白であること前提としています。
AAA2＝IF(COUNTIF(A2:ZZ2,1234)=1,IF(INDIRECT(ADDRESS(ROW(),MATCH(1234,A2:ZZ2,0)+1,4))<1000,AAA1,AAA1+1),AAA1)
これは、A2~ZZ2に1234があった場合、「それが何番目だったかを取得し、同じ行のその次の列のセルに入っているデータを取得。もしそれが1000より小さければ1つ上のセルと同じ数、1000以上であればそれに１つ加えた数を表示する」なかった場合は1つ上のセルと同じ数を表示する。　というものです。
長いので打ち間違えがあったらすみません。
これにより、該当した行では上の数から1つ増えた数を表示し、そうでなければ同じ数を表示することができます。

AAB2＝A2　次の検索の関係で、先ほどの数字の右側に名称を表示しておきたいので。

表示したいセルが分かりませんのでAACの列で2行目以降（2行目に1つ目の該当を表示）とします。
AAC2=VLOOKUP(ROW()-1,AAA:AAB,2,FALSE)
その行数より１少ない数をAAAで検索し、該当する一番上の行の２つ目の列（＝AAB）にある値＝名称を表示します。

これでできていればいいのですが。試してみてください。

tom04 · Answer

No.2です。

＞できればマクロを使用しないでできればありがたいです。
結局関数で処理したい！というコトですよね？

元データのSheet1に作業用の列を設けてみます。
A列を挿入　→　元データが右へ列ずつずれます。
A2セルに
=IF(COUNTIFS(C2:QQ2,1234,D2:QR2,">=1000"),ROW(),"")
という数式を入れフィルハンドルでダブルクリック！
これで「1234」の右側セルが1000以上の場合その行の行番号が表示されます。

そしてSheet2のA2セルに
=IFERROR(INDEX(Sheet1!B:B,SMALL(Sheet1!A:A,ROW(A1))),"")
という数式を入れフィルハンドルでずぃ～～～！っと下へコピーしてみてください。

※　前回も書いたようにデータ数が10万行以上あるというコトですので、
検証はしていませんが、フリーズ状態になるかもしれません。

＞データが重いので、できれば処理の軽め数式で・・・
とありますが、今はこの程度しか思いつきません。m(_ _)m

tom04 · Answer

こんばんは！

「金額コード○」が「1234」でその右となりの「金額○」が1000以上が1組以上あれば
その行のA列「名称」データを表示すればよいのですよね？

データが10万件以上あるというコトですので、関数ですべての行に数式をいれると相当重くなると思います。
そこでVBAになりますが、一例です。
↓の画像のように元データはSheet1にあり、Sheet2のA列に羅列してみました。
標準モジュールにしてください。

Sub Sample1()   'この行から//
    Dim lastRow As Long, wS As Worksheet
    Set wS = Worksheets("Sheet2")
        Application.ScreenUpdating = False
        wS.Range("A:A").Clear
        With Worksheets("Sheet1")
            lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
            .Range("A:A").Insert
            .Range("A1") = .Range("B1")
            With Range(.Cells(2, "A"), .Cells(lastRow, "A"))
                .Formula = "=IF(COUNTIFS(C2:QQ2,1234,D2:QR2,"">=1000""),B2,"""")"
                .Value = .Value
            End With
            .Range("A:A").AutoFilter field:=1, Criteria1:="<>"
            Range(.Cells(1, "A"), .Cells(lastRow, "A")).SpecialCells(xlCellTypeVisible).Copy wS.Range("A1")
            .AutoFilterMode = False
            .Range("A:A").Delete
        End With
        Application.ScreenUpdating = True
        wS.Activate
        MsgBox "完了"
End Sub 'この行まで//

※　上記VBAでも砂時計がくるくる回る（「応答なし」になる）可能性がありますので
腕組して画面とにらめっこし待ってみてください。

※　関数でないのでデータ変更があるたびにマクロを実行する必要があります。m(_ _)m

yuji3690 · Answer

同じ行に金額コード1234が複数出てくる可能性はあるのでしょうか？
ある場合、名称****は複数回表示するのでしょうか？それとも何度出ようが同じ行なら1回なのでしょうか？
金額の所にも1234が含まれる可能性はあるのでしょうか？
金額は1000円以上か比較するだけで、抽出する必要はないということでよろしいでしょうか？

抽出したデータはどこにどのような形で表示するのでしょうか？
縦一列にずらっと並べるのでしょうか？抽出した列に合わせて横にも並べるのでしょうか？
それとも同じ行に複数回出た場合のみ横に並べるのでしょうか？
はたまた全てのデータを1つのセルに***1、***5、****・・・といったように入れるのでしょうか？

同じ行には最大１個で金額に1234が含まれないのであれば、以下の様にできると思います。
条件が曖昧なので式の詳細は省略して流れだけの説明をします。
（それ以外の条件の場合はもっと複雑になりますので、条件をきちんと確認した上で考えます）

まず、1234がその行にあるかどうかを判定し、ある場合はそのセルの位置を取得し、その隣のセルの値を1000と比較し、
　1000以上であればこれが上から何個目の該当であるかを表示する。　という列①を作ります。
　列①の右に、その行の名称を表示する列②を作ります。（先ほど作った列①を左端に挿入できるのでしたら、列②は不要です）
　結果を表示したい所に、VLOOKUPを用いて①を検索して②を表示する式を作ります。
　これを該当する数だけコピーすれば抽出完了です。

エクセルで複数列から任意の値を検索しその隣の値を表示したい。

重ねてすみません。

すいません。

ちょっと画質が悪くて見づらいですね…

No4ですが、金額が1234になる場合もあるとの事ですので、がらっと式を変更します。

これを数式で実装してしまうと運用に耐えられないと思います。

とりあえずこちらの思っているとおりとして説明しますが、再確認したいこととして、

No.2です。

こんばんは！

同じ行に金額コード1234が複数出てくる可能性はあるのでしょうか？

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング