エクセル シート２枚の比較・抽出、英数字・日付など

質問

iwao32と申します。
EXCELについてお教え下さい。またまた躓いてしまいました。どなたか助けてください。

先日同じような質問をさせていただいたのですが、別パターンの質問です（セルの中身が違います。）。

以下の例で示します。

Ｓｈｅｅｔ１　　　　　　　　　　　　　　　　　　　　　　　　Ｓｈｅｅｔ２
Ａ列　　　　Ｂ列　　　　Ｃ列　　　　　　　　　　　　　　Ａ列
３　　　　　　Ｘ３　　　　　Ｙ３　　　　　　　　　　　　　　　２
５　　　　　　Ｘ５　　　　　Ｙ５　　　　　　　　　　　　　　　４
１　　　　　　Ｘ１　　　　　Ｙ１　　　　　　　　　　　　　　　５
４　　　　　　Ｘ４　　　　　Ｙ４
２　　　　　　Ｘ２　　　　　Ｙ２

という２枚のＳｈｅｅｔがあるとき、Ｓｈｅｅｔ１のＡ列とＳｈｅｅｔ２のＡ列を比較して、Ｓｈｅｅｔ１の中で、Ｓｈｅｅｔ２のＡ列にあるものだけ、Ｓｈｅｅｔ３に、

Ｓｈｅｅｔ３
Ａ列　　　　Ｂ列　　　　　Ｃ列
２　　　　　　Ｘ２　　　　　　Ｙ２
４　　　　　　Ｘ４　　　　　　Ｙ４
５　　　　　　Ｘ５　　　　　　Ｙ５

というものを作りたいのですが、ここで問題なのは、Ｘ１～Ｘ５、Ｙ１～Ｙ５が、例えば、

２０１１１２２０
２０１１－１２－２０
Ｈ１２Ｌ
２０１１／１２／２０
１２月２０日
空欄

のように、日付（スラッシュ入り、マイナス入り）、数字＋漢字、空欄など、だったりすることです。

全くの初心者なので、細かくお教えいただけると幸いです。

よろしくお願いいたします。

回答No.1

kagakusuki · ベストアンサー

>日付（スラッシュ入り、マイナス入り）、数字＋漢字、空欄など、だったりすることです。

　それは、まずいやり方です。
　データを入力する際には、統一された形式で入力する様にして下さい。

　但し、もしも、Sheet1のB列とC列に、日付を表しているもの以外では、数値データが一切存在していない場合には、以下の様な方法があります。

　まず、Ｓｈｅｅｔ3のA1セルに次の数式を入力して下さい。

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

　次に、Ｓｈｅｅｔ3のB1セルに次の数式を入力して下さい。

=IF(ISNUMBER(1/(VLOOKUP($A1,Sheet1!$A:B,COLUMNS($A:B),FALSE)<>"")),IF(ISNUMBER(TEXT(VLOOKUP($A1,Sheet1!$A:B,COLUMNS($A:B),FALSE),"0000-00-00")*1),TEXT(VLOOKUP($A1,Sheet1!$A:B,COLUMNS($A:B),FALSE),"0000-00-00")*1,VLOOKUP($A1,Sheet1!$A:B,COLUMNS($A:B),FALSE)),"")

　次に、Ｓｈｅｅｔ3のB1セルをコピーして、Ｓｈｅｅｔ3のC1セルに貼り付けて下さい。
　次に、Ｓｈｅｅｔ3のA1～C1の範囲をコピーして、同じ列の2行目以下貼り付けて下さい。

　尚、例えば2011111は、２０１１年１月１１日なのか、２０１１年１１月１日なのかを区別する事が出来ない事からも判ります様に、8桁以外の数字のみで表された日付を変換する方法というものは存在しません。
　又、12月20日の様に、年を指定せずに入力した場合には、そのセルにその日付を入力した年の12月20日として記録されてしまいますから、例えば2011年1月以降になってから、12月20日と入力した場合、入力した人間が2010年の12月20日のつもりで入力していても、実際に残るデータは2011年12月20日になってしまいます。
　これらの事から判ります様に、年が表示されないデータや、数字のみで日付を表しているデータが含まれている場合には、Sheet1に入力されているデータに誤りがあるのかないのかを、前もって確認しておく必要があります。
　この様に、元のデータの中に、正しい日付を表していないデータが含まれている恐れがありますから、上記の方法で集計した結果を入念にチェックして、誤りの有無を必ず確認して下さい。
　その上で、前述の様なミスを誘発する恐れを避けるためにも、今後、日付データを入力する際には、年月日が全て含まれていて、年と月、月と日との間には、スラッシュ等を挟んだ形式に、全ての日付データを統一する様にして下さい。

お礼

kagakusuki様

お教えいただいたことを参考にして、何とかできたような気がします。
どうもありがとうございました。

iwao32

補足

kagakusuki様

いつもご丁寧なご回答、どうもありがとうございます。

おっしゃる通り、まずいデータだとは思うのですが、実は私が今取り扱っているのはある統計データでして、統一されていない記述になっているところが多い上、量も膨大なので、全部チェックすることは実質的に不可能な状況でして、なんとか、セル（X1～X5やY1～Y5）内の様々なタイプの記述（例：20111220、2011-12-20、H12L、2011/12/20、12月20日、空欄など）を、そのまんまドカッとSheet3にコピーできないでしょうか？

よろしくお願いいたします。