Excelの関数教えて下さい。

質問

読んで頂きましてありがとうございます。
説明が下手かも知れませんが、よろしくお願いします。
画像のようにＢ列に「注番」が入力されています。
Ｃ列に「金額」が入力されています。
同じように、Ｄ列に「注番」Ｅ列に「金額」が入力されています。
基本的にはＢ列の「注番」とＤ列の「注番」は同じ物が入力されていますが、セルの位置が異なります。
画像のようにバラバラ状態で、本来は約600位の「注番」が入力されています。
Ｇ3のセルにＢ列の「注番」のどれかを入力するとＤ列の「注番」から同じ「注番」を探し出してＣ列の「金額」からＥ列の「金額」を引いた答えがＨ2のセルに表示出来るような関数式を教えて頂きたいのです。
同じ金額が入力されていれば答えは「0」になる訳です。
基本的には「0」でなければならないので、それを調べる為に必要です。
よろしくお願い致します。

回答No.7

Prome_Lin · ベストアンサー

回答No.3です。

仕事が入って、こちらを確認する時間がありませんでした。

遅くなり、申し訳ございません。

取り急ぎ、回答しておきますが、「ボタン」までは、対応していません。

金額が合わないところには、「薄い黄色」、「注番」が存在しないところには、「薄い緑」を付けています。

さしあたり、これでよろしいでしょうか？

Option Explicit
Sub Test()
Dim c(), p(), q(), i, j, l, r, k As Long
r = Range("B2").End(xlDown).Row
l = Range("D2").End(xlDown).Row
ReDim c(r - 2), p(r - 2), q(r - 2)
For i = 1 To r - 2
c(i) = Cells(i + 2, 2).Value
p(i) = Cells(i + 2, 3).Value
q(i) = i + 2
Next i
For i = 1 To r - 3
For j = i + 1 To r - 2
If c(i) > c(j) Then
k = c(i)
c(i) = c(j)
c(j) = k
k = p(i)
p(i) = p(j)
p(j) = k
k = q(i)
q(i) = q(j)
q(j) = k
End If
Next j
Next i
For i = 1 To r - 2
Cells(i + 2, 7).Value = c(i)
For j = 3 To r
If Cells(j, 4).Value = c(i) Then
Cells(i + 2, 8).Value = p(i) - Cells(j, 5).Value
If p(i) - Cells(j, 5).Value <> 0 Then
Range("B" & q(i)).Interior.ColorIndex = 6
Range("D" & j).Interior.ColorIndex = 6
End If
Exit For
End If
Next j
Next i
For i = 3 To r
k = 0
For j = 3 To l
If Cells(i, 2).Value = Cells(j, 4).Value Then
k = 1
Exit For
End If
Next j
If k = 0 Then
Range("B" & i).Interior.ColorIndex = 35
End If
Next i
For i = 3 To l
k = 0
For j = 3 To r
If Cells(i, 4).Value = Cells(j, 2).Value Then
k = 1
Exit For
End If
Next j
If k = 0 Then
Range("D" & i).Interior.ColorIndex = 35
End If
Next i
End Sub

回答No.6

bunjii

回答No.5の数式に誤りがありましたので一部訂正します。
=IF(AND(COUNTIF(B:B,G3),COUNTIF(D:D,G3)),INDEX(C:C,MATCH(G3,B:B))-INDEX(E:E,MATCH(G3,D:D)),"注番なし")
　　　　↓
=IF(AND(COUNTIF(B:B,G3),COUNTIF(D:D,G3)),INDEX(C:C,MATCH(G3,B:B,0))-INDEX(E:E,MATCH(G3,D:D,0)),"注番なし")
MATCH関数の第3引数を省略すると近似値の検出になりエラーが出ることもありますので今回の場合は第3引数を0として絶対値の検出としなければなりませんでした。

お礼

度々ありがとうございます。

回答No.5

bunjii

>Ｇ3のセルにＢ列の「注番」のどれかを入力するとＤ列の「注番」から同じ「注番」を探し出してＣ列の「金額」からＥ列の「金額」を引いた答えがＨ2のセルに表示出来るような関数式を教えて頂きたいのです。
回答No.1で良いでしょう。
しかし、答えは1ではありません。
次の数式でも同じ結果を得られます。
=IF(AND(COUNTIF(B:B,G3),COUNTIF(D:D,G3)),INDEX(C:C,MATCH(G3,B:B))-INDEX(E:E,MATCH(G3,D:D)),"注番なし")

>Ｂ列に存在する注番以外がＤ列にあった場合、そのセルに色を付けるなどの目印を表示する事は可能でしょうか？
条件付き書式で定義すれば可能です。
D列を選択して「ホーム」タブの「条件付き書式」から「新しいルール」を選択して、「数式を使用して、書式を設定するセルを決定」で次の数式を入力します。
=AND(D1<>"",COUNTIF($B:$B,D1)=0)
次に「書式」ボタンをクリックして「塗りつぶし」タブで好みの色を選択し、「OK」ボタンで戻ります。
同様にB列にもD列に含まれない注番のセルに色付けできます。
更に、G3セルへも条件付き書式でセルに色付けすることができます。
添付画像はExcel 2013で検証した結果です。

お礼

丁寧なご説明ありがとうございました。
凄いですね。
助かります。

回答No.4

kagakusuki

回答No.2です。

>Ｂ列とＤ列の注番に相違があるセルやＣ列とＥ列の金額に相違があるセルの色などの目印が付くとすごく助かります。

というだけの事でしたら、マクロを使わずとも条件付き書式を使って実現する事が出来ます。
　例えば、以下の様に操作して条件付き書式を設定しますと、B列～E列の中に未記入の項目がある行や、C列かE列に数値以外の値が入力されている行、B列とD列の値が異なっている行、C列とE列の値が異なっている行、の何れかに該当する行のセルが赤く塗りつぶされる様になりますので、後から確認する際や入力ミスを防ぐ事にも役立ちます。

B3セルを選択
　　↓
[ホーム]タブ内の「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択してください」欄の中にある[数式を使用して、書式設定するセルを決定]を選択してクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄に

=AND(COUNTBLANK($B3:$E3)<4,OR(COUNTBLANK($B3:$E3),COUNT($C3,$E3)<2,$C3<>$E3))

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある赤色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある、「ルール(表示順で適用)」欄が「数式:=AND(CO…」、「適用先」欄が「=$B$3」となっていて、「書式」欄が赤色に塗り潰されている行の、「適用先」欄に入力されている内容を

=$B$3:$E$603

に変更する(表の入力欄の範囲がB3:E603の場合)
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

お礼

ありがとうございました。
仕事の関係でお礼が遅くなりまして、申し訳ありませんでした。
凄く勉強になりました。

回答No.3

Prome_Lin

この回答は、ご希望の回答とは異なりますので、無視して頂いて結構です。

「VBA」でマクロを組みました。

というのは、手入力で１つ１つ確かめるのでは大変なので、すべて一覧になって出力されれば楽だと思ったからです。

「Ｇ」列に、「注番」がソートされ出力、「Ｈ」列には「Ｃ－Ｅ」の値が表示されます。

これで、一気に確認できると思います。

以下のマクロを、範囲指定して「Ctrl＋c」（コントロールキーを押しながら「c」：コピー・記憶）を押し、記憶させます。

次に、エクセルを立ち上げ、今、調べたいファイルを開けます。

「Alt＋F11」（「Alt」（「オルト」と読みます）キーを押しながら「F11」キーを押す）と、「Visual Basic」の画面が表示されます。

メニューの「挿入」から「標準モジュール」を選択すると、右側のグレーだった部分が、白くなります、

その白い部分にカーソルを持って行き、「Ctrl＋v」（ペースト・貼り付け）します。

あとは、「F5」を押して実行するだけです。

Option Explicit
Sub Test()
Dim c(), p(), i, j, r, k As Long
r = Range("B2").End(xlDown).Row
ReDim c(r - 2), p(r - 2)
For i = 1 To r - 2
c(i) = Cells(i + 2, 2).Value
p(i) = Cells(i + 2, 3).Value
Next i
For i = 1 To r - 3
For j = i + 1 To r - 2
If c(i) > c(j) Then
k = c(i)
c(i) = c(j)
c(j) = k
k = p(i)
p(i) = p(j)
p(j) = k
End If
Next j
Next i
For i = 1 To r - 2
Cells(i + 2, 7).Value = c(i)
For j = 3 To r
If Cells(j, 4).Value = c(i) Then
Cells(i + 2, 8).Value = p(i) - Cells(j, 5).Value
Exit For
End If
Next j
Next i
End Sub

お礼

ありがとうございます。
これは便利です。
ちゃんとできました。
欲を言わせていただけると、Ｂ列とＤ列の注番に相違があるセルやＣ列とＥ列の金額に相違があるセルの色などの目印が付くとすごく助かります。

補足

このマクロを使用するのにボタンのような物をClickすると実行されるようにする方法はありますでしょうか？
ありましたら教えて下さい。
お手数をお掛けして申し訳ありません。

回答No.2

kagakusuki

御質問の件を実現するには、H3セルに次の関数を入力されると良いでしょう。

=IF($G$3="","",IF(COUNTIF($B:$B,$G$3),IF(COUNTIFS($B:$B,$G$3,$C:$C,"<9E+307",$E:$E,"<9E+307"),VLOOKUP($G$3,$B:$E,2,FALSE)-VLOOKUP($G$3,$B:$E,4,FALSE),"金額が入力されていません"),"該当注番なし"))

　尚、

>基本的には「0」でなければならないので、それを調べる為

という事でしたら、適当な使用していない列の3行目のセルに次の関数を入力してから、そのセルをコピーして同じ列の4行目以下に貼り付けますと、注番が入力されている行でC列とE列の金額が異なっている行の所にのみ、×印が表示されます。

=IF($B3="","",IF(AND(COUNT($C3,$E3)=2,$C3=$E3),"","×"))

お礼

ありがとうございました。
凄く勉強になりました。

回答No.1

mshr1962

EXCEL2007以降なら
=IFERROR(VLOOKUP(G3,B:C,2,FALSE)-VLOOKUP(G3,D:E,2,FALSE),”注番がありません”)

お礼

早速のご回答ありがとうございました。
凄いです。
出来ますね！
ありがとうございました。
嬉しいです。

補足

すみません、図々しく追加事項ですけど、例えば検索する注番がＤ列に存在しない場合は「注番がありません」と表記されますが、Ｂ列に存在する注番以外がＤ列にあった場合、そのセルに色を付けるなどの目印を表示する事は可能でしょうか？
注番と差額の部分はお蔭さまで解決致しました。