[[20080611154154]]　『エクセルのプログラム（？）についてどなたか教え』（てつ）　ページの最後に飛ぶ

『エクセルのプログラム（？）についてどなたか教えてください。』（てつ）

 　
シート1に、200人の社員リストを作りました。社員番号をA列に、氏名をB列に並べました。
シート2に、講習会に出席した人の社員番号を入力しVLOOKUP関数で右列に氏名が出るようにしました。
　
そこで、シート3に、講習会に出席してない人の社員番号を自動的に表示させたいのです。
どのような考え方、関数、マクロを使用すればよいのでしょうか？
どなたか教えてください。エクセルは2003です。

 INDEX、SMALL、MATCH、ROWなど組み合わせると出来そうですね。
 レイアウト提示がないと具体的にはお示しできませんが。

 マクロであれば、Dictionaryを使えば簡単に出来そうです。
 社員リストすべてをDictionaryにセットして、出席した人をRemoveします。
 残ったKeyを出力すればOKです。

 (ROUGE)

うわ、本当ですか？ROUGEさん、ありがとうございます。
具体的に教えていただくにはどうしたらいいでしょうか？

レイアウト提示というと、

シート1には元データとも言うべきデータを入力してあります。数字は社員番号です。

 A       B
001　鈴木太郎
002　山下啓二
003　飯田雅彦
004　大田良治
005　小山憲一

シート2では、講習会に出席した人を入力します。
社員番号を入力すると、
if(a1="","",vlookup(a1,a1:b5,1,false))という関数で氏名を表示させています。

 A       B
002　山下啓二
003　飯田雅彦
005　小山憲一

シート3のa1からa50まで上から社員番号順に表示させたいのです。
いかがでしょうか？

尚、マクロにもとても興味があります。
お手間でなければ、コードを教えていただけるとうれしいです。

 =IF(COUNTA(Sheet1!$A$1:$A$10)-COUNTA(Sheet2!$A$1:$A$10)<ROW(A1),"",
  INDEX(Sheet1!$A$1:$A$10,SMALL(IF(ISERROR(MATCH(Sheet1!$A$1:$A$10,Sheet2!$A$1:$A$10,0)),ROW($A$1:$A$10),""),ROW(A1))))
 と入力して、Ctrl+Shift＋Enterで数式を確定。
 必要分下にフィルドラッグします。
 名前はVLOOKUP関数で表示してください。

 マクロならばこんな感じ。
 (ROUGE)
'----
Sub test()
Dim dic As Object
Dim tbl, ky
Dim i As Long
With Sheets("Sheet1")
    tbl = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
End With
Set dic = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(tbl, 1)
    ky = tbl(i, 1) & "_" & tbl(i, 2)
    dic(ky) = Empty
Next
With Sheets("Sheet2")
    tbl = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
End With
For i = 1 To UBound(tbl, 1)
    ky = tbl(i, 1) & "_" & tbl(i, 2)
    If dic.Exists(ky) Then
        dic.Remove ky
    End If
Next
With Sheets("Sheet3")
    .Range("A:B").ClearContents
    If dic.Count > 0 Then
        i = 0
        For Each ky In dic.keys
            i = i + 1
            .Range("A" & i).Resize(, 2).Value = Split(ky, "_")
        Next
    End If
End With
Set dic = Nothing
End Sub

ROUGEさん、ありがとうございます。
さっそく、関数のバージョンを試してみました。

シート名や、社員数の部分の数字などを変更して張付けてみました。
見ましたが、うまくいきません。

上の例は簡単にするために簡略化したのですが、

実物は、社員データのシートは別に。

出席した人を書き込むシートと、
出席していない人を表示させるシートは
同じシートにしたのです。

これが原因でしょうか？
もしよければ、この関数プログラムの解説をしていただくわけにはいきませんか？
どういう考え方で、どういう流れでこういうプログラムとなるのかを知りたいのです。
これがわかれば、自分のプログラム上で応用できそうな気がします。
よろしくお願いします。

てつ

 =IF(COUNTA(Sheet1!$A$1:$A$10)-COUNTA(Sheet2!$A$1:$A$10)<ROW(A1),"",
  INDEX(Sheet1!$A$1:$A$10,SMALL(IF(ISERROR(MATCH(Sheet1!$A$1:$A$10,Sheet2!$A$1:$A$10,0)),ROW($A$1:$A$10),""),ROW(A1))))
　
 まず、IF関数の部分。
　
 COUNTA(Sheet1!$A$1:$A$10)-COUNTA(Sheet2!$A$1:$A$10)<ROW(A1)
 リストに記載されている人数から出席した人数を引いた数、即ち欠席した人数が、行数よりも少ないときは空白を返す。
 そうでない場合はINDEX(・・・の結果を返す、というものです。
　
　
 次はこの関数のコア部分はINDEXの部分ですので、その解説です。
　
 INDEX(Sheet1!$A$1:$A$10,SMALL(IF(ISERROR(MATCH(Sheet1!$A$1:$A$10,Sheet2!$A$1:$A$10,0)),ROW($A$1:$A$10),""),ROW(A1)))
　
 Sheet1のリストからSMALL(・・・)で得られる数字の順番のものを返すというものです。
　
　
 SMALL(IF(ISERROR(MATCH(Sheet1!$A$1:$A$10,Sheet2!$A$1:$A$10,0)),ROW($A$1:$A$10),""),ROW(A1))
　
 これは、IF(ISERROR(MATCH(Sheet1!$A$1:$A$10,Sheet2!$A$1:$A$10,0)),ROW($A$1:$A$10),"")で得られる
 数値の配列から小さい順に数値を返すというものです。
　
　
 IF(ISERROR(MATCH(Sheet1!$A$1:$A$10,Sheet2!$A$1:$A$10,0)),ROW($A$1:$A$10),"")
　
 ISERROR(MATCH(Sheet1!$A$1:$A$10,Sheet2!$A$1:$A$10,0))がTrueの場合、それぞれの順番に合った数値を返すというものです。
　
　
 ISERROR(MATCH(Sheet1!$A$1:$A$10,Sheet2!$A$1:$A$10,0))
　
 Sheet1のリストの人でSheet2の出席者名簿に名前がない場合、Trueを返します。
　
　
 といった構造になっています。
　
 (ROUGE)

解説、本当にありがとうございます。
本当に自分は頭悪いんですね。頭がごっちゃになってます。
じっくり、解説を読み、理解してみます。

ありがとうございます。又、ぜひ質問させて下さい。

（てつ）

コメント返信：

[ 一覧(最新更新順) ]

[[20080611154154]] 『エクセルのプログラム（？）についてどなたか教え』（てつ） ページの最後に飛ぶ

[[20080611154154]]　『エクセルのプログラム（？）についてどなたか教え』（てつ）　ページの最後に飛ぶ