エクセルで出来るんでしょうか？

質問

下図の様のA列のデーターをB列に転記する際
C列で重複がないかをチェック、D列でB列に転記してない残りのNoを表示させる。
この様な作業を関数を使用できるものなのでしょうか？アドバイスお願いします。
できるんか、ぼけーレベルでしたらすみません；；

補足
・B列には入力する順序規則はなし。
・D列に表示する転記してないデーターの表示位置はD列ならどこに表示させてもかまいません。

回答No.3

kagakusuki

>数式の意味が理解できず応用が効きそうではないです・・・

　まず、

ROW()

ですが、これは括弧内で指定した参照先のセルの行番号を求める関数で、この場合は括弧の中に参照先が指定されていないので、この関数が入力されているセルの行番号が返されます。
　次に例えば、

INDEX($A:$A,ROW())

ですが、INDEX関数は

INDEX(配列, 行番号, [列番号])

の形で表され、配列の部分で縦横に広がりのあるセル範囲を指定し、行番号の部分で、そのセル範囲の中の上から何番目であるかを指定し、列番号の部分で左から何番目であるかを指定する事で、配列の部分で指定したセル範囲の中にある、特定のセルに存在するデータを取り出す関数です。

INDEX($A:$A,ROW())

の場合は、配列で指定しているセル範囲は、縦方向に1列のみですから、列番号は指定する必要が無く、

$A:$A

のセル範囲中で、上から数えて

ROW()

番目のセルの値を返す関数となります。

$A:$A

はA列の上端から下端までのセル範囲であり、

ROW()

は関数が入力されているセルの行番号を返す関数なのですから、結局、

INDEX($A:$A,ROW())

は、その関数が入力されているセルと同じ行にある、A列のセルを参照する関数という事です。
　それならば、例えば行番号が2のセルに入力する際に、A2と記述すれば良い様に思われるかも知れませんが、
御質問が重複チェックという事でしたので、重複したデータが存在するセルは削除されてしまう可能性があると予想しました。
　そして、重複箇所を削除する際に、例えばA2セルをセルごと削除してしまいますと、A2セルが無くなり、元はA3セルだったセルがA2セルの位置に移動しますが、A2セルが消滅するため、関数で参照先を指定する際にA2と記述した場合には、その関数はエラーとなってしまいます。
　又、参照先を指定する際にA2と記述した場合で、セルの切取りや挿入等の編集作業を行なうと、セルの位置関係が狂ってしまい、例えばA5セルのデータを変更したのに、その変更に反応したのはF6セルだった、という様な事が起こります。
　これに対して、INDEX($A:$A,ROW())で指定した場合には、削除、切り取り、挿入等の編集作業を行なっても、必ずA列の
同じ行のセルを参照する事が出来ます。
　ですから、F2セルの関数である

=IF(INDEX($A:$A,ROW())="","",IF(COUNTIF($B:$B,INDEX($A:$A,ROW())),"",ROW()))

は、動作上は

=IF($A2="","",IF(COUNTIF($B:$B,$A2),"",ROW()))

と同じ事ですが、元データに対して、削除、切り取り、挿入等の編集作業を行っても構わない関数という事です。
　さて、

COUNTIF($B:$B,$A2)

は、B列の中にA2と同じデータが幾つ存在しているかをカウントする関数です。
　それが、IF関数の判定式の部分にあるのはどんな意味かと言いますと、IF関数では、判定式の部分の計算結果が数値の0の場合には、判定式が成り立っていないと見做し、判定式の部分の計算結果が0以外の数値の場合には、判定式が成り立っていると見做します。
　つまり、

IF(COUNTIF($B:$B,$A2),"",ROW())

は、B列の中にA2と同じデータが1つでも存在している場合には何も表示せず、B列の中にA2と同じデータが無い場合には、関数が入力されているセルの行番号（この場合、F2セルに入力されている関数なので、行番号は2）を表示する関数となっています。
　つまり、F列では、A列と同じデータが、B列中の何処にも存在していない場合（即ち、残りのNo.の場合）に、行番号が表示され、残りのNo.ではない場合には、何も表示されません。
　又、

IF(INDEX($A:$A,ROW())="","",

の部分は、動作上は

IF($A2="","",

と同じ事で、A列が空欄の場合には、何も表示しない様にする働きをしています。

次に、C2セルに入力する関数である

=IF(INDEX($B:$B,ROW())="","",IF(COUNTIF($B$1:INDEX($B:$B,ROW()),INDEX($B:$B,ROW()))>1,"×","○"))

は、

=IF($B2="","",IF(COUNTIF($B$1:$B2,$B2)>1,"×","○"))

と同じ働きをします。

COUNTIF($B$1:$B2,$B2)>1

は、B列の、1番上から関数が入力されているのと同じ行までの間に、「関数が入力されているのと同じ行のB列のセル」と同じ値のセルが幾つあるかをカウントし、カウント結果が1を上回る（即ち、2個以上存在する）か否かを判定します。
　ですから、

IF(COUNTIF($B$1:$B2,$B2)>1,"×","○")

は、B列に同じ値が2つ以上存在している場合には×を表示し、1個以下しか存在しない（即ち、重複が無い）場合には○を表示する関数という事です。

　最後に、D2セルに入力する関数である

=IF(ROWS($2:2)>COUNT($F:$F),"",INDEX($A:$A,SMALL($F:$F,ROWS($2:2))))

ですが、この中で使われているROWS関数は、括弧内に指定されているセル範囲が何行あるのかという事を求める関数です。

ROWS($2:2)

の部分では、括弧内にセル範囲$2:2が指定されていますが、これは2行目から2行目の範囲には、行が何行あるのかという事を求める関数となっています。
　2行目から2行目の範囲には、行が1行だけ存在しますから、

ROWS($2:2)

の部分は、数値の1を返す関数という事です。
　この関数をD3セルにコピーしますと、$2:2の前半の$2の部分は絶対参照になっていますから変わりませんが、後半の2の部分は相対参照であるため、Excelの機能によって、3に書き換えられます。
　すると、

ROWS($2:2)

の部分は、

ROWS($2:3)

に書き換えられますから、2行目から3行目の範囲には、行が何行あるのかという事を求める関数となり、計算結果は2となります。
　この様に

ROWS($2:2)

の部分は、最初に関数を入力したD2セルから数えて、何行目に位置するのかという事を求める関数となっています。
　 そして、SMALL関数は、

SMALL(範囲, 順位)

の形で表され、範囲の部分で指定されているセル範囲内に存在する数値の中で、「順位の部分で指定した数」番目に小さな数を求める関数です。

SMALL($F:$F,ROWS($2:2))

の部分では、範囲にはF列全体が指定されていて、順位には

ROWS($2:2)

という最初に関数を入力したD2セルから数えて、何行目に位置するのかという事を求める関数が入力されていますから、この関数をD2セルに入力した場合には、D2セルは、D2セルから数えて1行目に存在しますから、

SMALL($F:$F,ROWS($2:2))

の部分は、F列で1番小さな数値を返します。
　これをD3セルにコピーしますと、

SMALL($F:$F,ROWS($2:3))

に書き換えられますから、F列で2番目に小さな数値を求める関数となります。
　この様に、

SMALL($F:$F,ROWS($2:2))

の部分は、F列に存在する数値を小さい順に並べる関数となっています。
　F列に入力されている関数は、残りのNo.の場合にのみ行番号を表示する関数なのですから、

SMALL($F:$F,ROWS($2:2))

の部分は、残りのNo.が存在する行の行番号を、小さい順に並べる事になります。
　ですから、

INDEX($A:$A,SMALL($F:$F,ROWS($2:2)))

の部分は、A列の中で、残りのNo.が存在する行に存在するセルの値を、行番号が小さい順に並べて表示する関数となります。
　A列は元データであるNo.が入力されていますから、結局、

INDEX($A:$A,SMALL($F:$F,ROWS($2:2)))

の部分は、残りのNo.を行番号が小さい順に並べて表示する関数となります。
　そして、

COUNT($F:$F)

は、F列の中で数値データが入っているセルの個数をカウントする関数ですから、

IF(ROWS($2:2)>COUNT($F:$F),"",

の部分は、関数が入力されているセルにおいて、D2セルから数えた行数が、F列の中で数値データが入っているセルの個数を上回った場合には、何も表示させない様にしています。
　このため、D列には残りのNo.だけが、上から順番に表示される事になります。

お礼

噛み砕いた説明もして頂きありがとうございました。
解説を読みながら数式を読み返すと納得でした。
答えを教えて頂いたうえに、ご指導までも・・・・（涙）
仕事をする上で入力ミス防止と作業性を考えた時、この数式を必要としてました。
感謝しています。ありがとうございました。

回答No.2

kagakusuki

作業列を設けると良い思います。
　適当な使用していない列（ここでは仮にF列としますが、未使用の別のシートの列を作業列とすれば、余計な表示が見える事は無くなります）の2行目のセル（ここではF2セル）に次の数式を入力して下さい。

=IF(INDEX($A:$A,ROW())="","",IF(COUNTIF($B:$B,INDEX($A:$A,ROW())),"",ROW()))

　次に、F2セルをコピーして、F3以下に貼り付けて下さい。
　次に、C2セルに次の数式を入力して下さい。

=IF(INDEX($B:$B,ROW())="","",IF(COUNTIF($B$1:INDEX($B:$B,ROW()),INDEX($B:$B,ROW()))>1,"×","○"))
　次に、D2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNT($F:$F),"",INDEX($A:$A,SMALL($F:$F,ROWS($2:2))))

　次に、C2～D2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　尚、もし、F列に余計な表示が現れて見苦しいと思われる場合には、F列全体を切り取ってから、適当な未使用のシートの適当な列の1行目のセル（例えば、Sheet2が未使用の場合には、Sheet2のA1セル等）を右クリックし、現れた選択肢の中にある[切り取ったセルの挿入]をクリックしますと、作業列がSheet2のA列に移動し、D2セルに入力した数式も、それに合わせて自動的に書き換わります。

お礼

遅くなって申し訳ありませんでした。
解決できてうれしい限りですが、数式の意味が理解できず応用が効きそうではないです・・・
日々精進します。

回答No.1

neKo_deux

ほぼCOUNTIF(範囲, 条件)関数だけで何とかなるような？

C2だと、
C2:=IF(COUNTIF($A:$A, $B2)<=1, "○", "×")
で、B2の数値をA列から探して数え上げて、重複が無い場合は1個か0個になるでしょうから、その場合は○とか。

D2だと、
C2:=IF(COUNTIF($B:$B, $A2)=0, $A2, "")
で、A2の数値をB列から探して数え上げ、B列に無い場合は0になるでしょうから、その場合はA2の値を表示。

お礼

アドバイスありがとうございます。
しかし上記の式だとC列が全て○になってしまいます。
なにがおかしいくて○にななってしまうのか？を考えるのが私の宿題ですね。