[[20180315212911]] 『検索値が複数の場合』(PPP) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『検索値が複数の場合』(PPP)

入金日と入金額をSheet2からSheet1の様に抽出したいのですが、
どの様にしたら宜しいでしょうか?
VLOOKUPだとCとDの部分しか抽出出来ない為、
入金が何回かに分かれた際の抽出の仕方を教えていただきたいです。
すみませんが、よろしくお願い致します。

Sheet1

 A       B        C     D          E          F  
 コード  社名       入金日   入金額       入金日      入金額
 01    A社        1/1    1000        1/4       3000
 02    B社         1/2    2000        1/11      2000
 03    C社
 04    D社
 05    E社        1/10    1000
 06    F社

 Sheet2
 A        B        C
 コード 入金日 入金額
  01      1/1    1000
  02      1/2    2000
  01      1/4    3000
  05      1/10   1000
  02      1/11   2000

< 使用 Excel:Excel2016、使用 OS:Windows10 >


 同じ入金日のものが出てくることはあるのか?
(ねむねむ) 2018/03/16(金) 09:09

 おじゃまします。 ^^ vbaだと。。。
恐怖の憶測と推測による日曜大工的コードですが
社名コードが文字列なのか表示だけなのか?気になりますが。。。
外してましたらすみません。要バックアップ。。必須です。^^;

 Option Explicit
'**********************************************************
Sub main()
    Dim sh01 As Worksheet, sh02 As Worksheet
    Dim i As Long, j As Long, lastr As Long, k As Long
    Dim rr As Range, mbuf(1 To 7, 1 To 2)
    Set sh01 = ThisWorkbook.Worksheets("Sheet1")
    Set sh02 = ThisWorkbook.Worksheets("Sheet2")
    lastr = sh02.Cells(sh02.Rows.Count, 1).End(xlUp).Row
    Set rr = sh02.UsedRange
    Call dum_master(mbuf)
    sh01.UsedRange.Clear
    sh01.Range("A1").Resize(UBound(mbuf, 1), UBound(mbuf, 2)) = mbuf
    With sh01
        For j = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
            .Cells(j, 1) = "'0" & .Cells(j, 1)
            For i = 2 To lastr
                If Val(.Cells(j, 1)) = Val(rr(i, 1)) Then
                    k = .Cells(j, .Columns.Count).End(xlToLeft).Column + 1
                    .Cells(1, k) = "入金日"
                    .Cells(1, k)(1, 2) = "入金額"
                    .Cells(j, k) = rr(i, 2)
                    .Cells(j, k).NumberFormat = "m月d日"
                    .Cells(j, k)(1, 2) = rr(i, 3)
                    .Cells(j, k)(1, 2).NumberFormat = "###,###,###"
                End If
            Next
        Next
    End With
End Sub
Private Sub dum_master(buf)
    Dim i As Long
    For i = LBound(buf, 1) To UBound(buf, 1)
        Select Case i
            Case 1
                buf(i, 1) = "コード": buf(i, 2) = "社名"
            Case 2
                buf(i, 1) = "01": buf(i, 2) = "A社"
            Case 3
                buf(i, 1) = "02": buf(i, 2) = "B社"
            Case 4
                buf(i, 1) = "03": buf(i, 2) = "C社"
            Case 5
                buf(i, 1) = "04": buf(i, 2) = "D社"
            Case 6
                buf(i, 1) = "05": buf(i, 2) = "E社"
            Case 7
                buf(i, 1) = "06": buf(i, 2) = "F社"
        End Select
    Next
End Sub
(隠居じーさん) 2018/03/16(金) 10:11

 すまない。
 私の質問はわすれてくれ。
 Sheet1からSheet2を作りたい、と勘違いしていた。
(ねむねむ) 2018/03/16(金) 10:27

 改めて。
 Sheet1のC2セル及びD2セルはそのままVLOOKUP関数で。

(ねむねむ) 2018/03/16(金) 10:49


 E2セルに
 =IFERROR(VLOOKUP($A2&"_"&2,IF({1,0},Sheet2!$A$2:$A$1000&"_"&COUNTIF(INDIRECT("Sheet2!A2:A"&ROW($2:$1000)),Sheet2!$A$2:$A$1000),Sheet2!B$2:B$1000),2,FALSE),"")
 と入力してShiftキーとCtrlキーを押しながらEnterキーで式を確定(確定後、式が{}で囲まれればOK)。
 その後E2セルを右及び他へフィルコピーでどうか。
(ねむねむ) 2018/03/16(金) 10:49

 おっと、
 >その後E2セルを右及び他へフィルコピーでどうか。
    ↓
 その後E2セルを右及び下へフィルコピーでどうか。

(ねむねむ) 2018/03/16(金) 10:53


隠居じーさんさん、マクロでいいのでしょうか?ありがとうございます!
上手く抽出出来ました!
ですが、エクセルの関数の方で表示させたいと思っています。
便利なんですが、他の人も使うので、何かあった時に私では対応出来ないので(/_;)
大変勉強になります!
関数の方で出来たらまた教えて下さい!宜しくお願い致します。

ねむねむさんの数式で最初出てこなくて、少し時間が経ったら出てきました!
{}とか初めて使いました!ありがとうございます!

前任者はG列とH列に下記の式を入れて、
それを元に入金日と入金額を埋めておりましたが、
よくわからなかったので・・・。

G列=IFERROR(MATCH(A2,Sheet1!A:A,0),0)
H列=IFERROR(MATCH(A2,INDIRECT("'Sheet1'!A"&$G2+1&":A"&$AF$1),0)+G2,0)

もちろんねむねむさんの式の意味も分かってない部分が多いですけど・・・。((+_+))
下処理無しでも抽出出来るのですね!

(PPP) 2018/03/16(金) 11:25


 上で挙げた式はSheet2のデータが最大1000行まで対応となっている。
 もし、実際のデータがそこまでないのであれば式中の$1000をすべてもっと小さくしてみてくれ。
 それで今よりも軽くなるかもしれない。
(ねむねむ) 2018/03/16(金) 11:34

わかりました!やってみます!
お二方共貴重なお時間を割いていただいてありがとうございます!
また宜しくお願い致します!
(PPP) 2018/03/16(金) 12:02

ちなみにですが、

G列=IFERROR(MATCH(A2,Sheet2!A:A,0),0) 
上記式で抽出した行番号の次のセルから抽出する為の
H列の式ですが、

H列=IFERROR(MATCH(A2,INDIRECT("'Sheet2'!A"&$B2+1&":A"&$AF$1),0)+B2,0)
の式では番号が抽出出来ないのですが、何か違っているのでしょうか?

AFセルには何も記載されていません。
どうしてもスッキリしないので教えて下さい。
宜しくお願い致します。
(PPP) 2018/03/16(金) 17:29


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.