エクセルで複数シート間での重複データを避けるための方法
こんにちは いつもお世話になっています。
エクセル2003を使用しています。
Sheet1は表引き先です。A1に検索値(名前)を入れると、他のシート(教科別)からコード(数字)を引いてきます。具体的には、セルA2以下に教科名、セルB2以下にコードを引いてきます。
例えば
教科 コード
国語 1
算数 2
英語 3
こんな感じです。
Sheet2は国語、Sheet3は算数、Sheet4は英語で、それぞれA列に名前、B列にコードを入力します。
例えば、
新井 1
井上 2
上野 3
こんな感じです。
ここで質問です。Sheet1でのコードの表引きで各教科のコードが重複しないようにしたいのです。Sheet1での確認ではなく、実際に各教科ごとのシート(Sheet2-4)のコード欄に他の教科のコードが重複した時に、重複を知らせるようなプログラムを作りたいのです。どの教科から入力するかは不定ですが、必ず入力されます。
(ここでの「重複」とは、あくまでもSheet1の「名前」で表引きしたときの各教科のコードの重複のことです。つまり、例えばSheet2で「新井」と「井上」のコードが重複しても構いません。)
わかりにくい表現でしたらお詫びします。情報の不足がありましたら教えてください。よろしくお願いします。
No.3ベストアンサー
- 回答日時:
回答番号:ANo.1、2です。
先程の回答で、Sheet1の4行目以下に数式を設定する事を、書き忘れておりました。
ですから、ANo.1、2の作業に引き続いて、Sheet1のA3~B3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。
kagakusuki 様 ありがとうございました。
お蔭様で解決しました。
まだ、作業シートの色づけしか試していませんが成功しました。
面倒な内容にもかかわらず、回答字数制限を越えて丁寧に説明していただき大変助かりました。
ポイントだけではもったいない気持ちですが、大切に使わせていただきます。
簡単で恐縮ですが、お礼申し上げます。
No.2
- 回答日時:
回答番号:ANo.1に追加して、
以下の操作を行って、Sheet2のB3セルに条件付き書式を設定して下さい。
Sheet2のB3セルをクリックして選択
↓
メニューの[書式]をクリック
↓
現れた選択肢の中にある[条件付き書式]をクリック
↓
現れた「条件付き書式の設定」ウィンドウの左端の欄をクリック
↓
現れた選択肢の中にある「数式が」をクリック
↓
「条件付き書式の設定」ウィンドウの右端の欄に次の数式を入力
=COUNTIF(OFFSET(INDIRECT("Sheet0!1:1"),MATCH($A3,INDIRECT("Sheet0!A:A"),0)-1,),$B3)>1
↓
「条件付き書式の設定」ウィンドウの[書式]ボタンをクリック
↓
現れた「セルの書式設定」ウィンドウの[パターン]タグをクリック
↓
赤色の四角形をクリック
↓
「セルの書式設定」ウィンドウの[OK]ボタンをクリック
↓
「条件付き書式の設定」ウィンドウの[OK]ボタンをクリック
次に、以下の操作を行って、Sheet2のB3セルに入力規則を設定して下さい。
Sheet2のB3セルをクリックして選択
↓
メニューの[データ]をクリック
↓
現れた選択肢の中にある[入力規則]をクリック
↓
現れた「データの入力規則」ウィンドウの[設定]タグをクリック
↓
「入力値の種類」欄をクリック
↓
現れた選択肢の中にある「ユーザー設定」をクリック
↓
「数式」欄に次の数式を入力
=COUNTIF(OFFSET(INDIRECT("Sheet0!1:1"),MATCH($A3,INDIRECT("Sheet0!A:A"),0)-1,),$B3)=1
↓
「データの入力規則」ウィンドウの[OK]ボタンをクリック
次に、以下の操作を行って、Sheet2のB3セルに入力規則を設定して下さい。
Sheet2のB3セルをクリックして選択
↓
メニューの[データ]をクリック
↓
現れた選択肢の中にある[入力規則]をクリック
↓
現れた「データの入力規則」ウィンドウの[設定]タグをクリック
↓
「入力値の種類」欄をクリック
↓
現れた選択肢の中にある「ユーザー設定」をクリック
↓
「数式」欄に次の数式を入力
=COUNTIF(OFFSET(INDIRECT("Sheet0!1:1"),MATCH($A3,INDIRECT("Sheet0!A:A"),0)-1,),$B3)=1
↓
「データの入力規則」ウィンドウの[OK]ボタンをクリック
次に、以下の操作を行って、Sheet2のB3セルに設定した条件付き書式と入力規則を、Sheet2~Sheet4のコードを入力するセルにコピーして下さい。
Sheet2のB3セルを右クリック
↓
現れた選択肢の中にある[コピー]をクリック
↓
キーボードの[Shift]キーを押しながらBookウィンドウの「Sheet4」見出しをクリック
↓
B3セルをクリック
↓
キーボードの[Shift]キーを押しながら、B列の(コードを入力する予定の範囲をカバーするのに充分な範囲の)最も下のセルをクリック
↓
黒い太枠で囲まれた範囲の内側を右クリック
↓
現れた選択肢の中にある[形式を選択して貼り付け]をクリック
↓
現れた「形式を選択して貼り付け」ウィンドウの中にある「書式」と記されている箇所にチェックを入れる
↓
「形式を選択して貼り付け」ウィンドウの[OK]ボタンをクリック
↓
黒い太枠で囲まれた範囲の内側を右クリック
↓
現れた選択肢の中にある[形式を選択して貼り付け]をクリック
↓
現れた「形式を選択して貼り付け」ウィンドウの中にある「入力規則」と記されている箇所にチェックを入れる
↓
「形式を選択して貼り付け」ウィンドウの[OK]ボタンをクリック
後は、各教科のSheetに教科の名前と個人の名前、そしてコードを入力して行き、重複コードを入力しようとすると
「入力した値は正しくありません。」
という表示が現れて入力操作が停止します。
又、既に重複した値が入力されていた場合には、各教科のSheetとSheet0において、重複したデータが存在しているセルの色が赤くなります。
No.1
- 回答日時:
今仮に、Sheet2~Sheet4の
A1セルに「教科」
A2セルに「氏名」
B2セルに「コード」
B1セルに国語等の教科名が入力されていて、
A列の3行目以下に個人の名前
B列の3行目以下に個人毎のコード
が入力されているものとします。
まず、作業用Sheet兼一覧表として、Sheet0を設けます。
そして、Sheet0の1行目のB列から右に向かって、「Sheet2」等の各教科毎のSheet名を入力して下さい。(「」は不要)
次に、Sheet0のB2セルに次の数式を入力して下さい。
=IF(ISTEXT(INDIRECT(B$1&"!B1")),INDIRECT(B$1&"!B1"),"")
次に、Sheet0のB3セルに次の数式を入力して下さい。
=IF(OR(B$2="",$A3=""),"",IF(ISNUMBER(VLOOKUP($A3,INDIRECT(B$1&"!A:B"),2,0)),VLOOKUP($A3,INDIRECT(B$1&"!A:B"),2,0),""))
次に、以下の操作を行って、Sheet0のB3セルに条件付き書式を設定して下さい。
Sheet0のB3セルをクリックして選択
↓
メニューの[書式]をクリック
↓
現れた選択肢の中にある[条件付き書式]をクリック
↓
現れた「条件付き書式の設定」ウィンドウの左端の欄をクリック
↓
現れた選択肢の中にある「数式が」をクリック
↓
「条件付き書式の設定」ウィンドウの右端の欄に次の数式を入力
=AND(B3<>"",COUNTIF(3:3,B3)>1)
↓
「条件付き書式の設定」ウィンドウの[書式]ボタンをクリック
↓
現れた「セルの書式設定」ウィンドウの[パターン]タグをクリック
↓
赤色の四角形をクリック
↓
「セルの書式設定」ウィンドウの[OK]ボタンをクリック
↓
「条件付き書式の設定」ウィンドウの[OK]ボタンをクリック
次に、Sheet0のB2~B3の範囲をコピーして、同じ行のC列から右に向かって、教科の数だけ貼り付けて下さい。
次に、Sheet0の3行目を範囲コピーして、4行目以下に貼り付けて下さい。
そして、Sheet0のA3以下に、各個人の名前を、漏れなく入力して下さい。
次に、Sheet1の
A2セルに「教科」
B2セルに「コード」
と入力して下さい。
次に、Sheet1のA3セルに次の数式を入力して下さい。
=INDEX(Sheet0!$2:$2,ROWS(Sheet1!$1:2))
次に、Sheet1のB3セルに次の数式を入力して下さい。
=IF(OR($A$1="",$A3=""),"",IF(COUNTIF(Sheet0!$A:$A,$A$1)=0,"該当者無し",VLOOKUP($A$1,Sheet0!$A:$Z,MATCH($A3,Sheet0!$2:$2,0))))
これだけでも、Sheet0において、重複したコードが表示されているセルの色が赤くなりますから、判別は出来ます。
尚、各教科毎のSheetにコードを入力する際に、重複コードの有無を知らせる方法に関しては、このサイトの入力文字数の制限を超えるため、次回の回答で述べさせて頂きます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
今まで文字化けなく開けていたc...
-
スプレッドシートで指定された...
-
ExcelでASCを使って全角を半角...
-
マクロの処理が遅くなった
-
Excel関数について教えてくださ...
-
作成した数式を値として表示し...
-
エクセルの質問です。 F列からL...
-
エクセルでファイルの最終更新...
-
EXCELの散布図で日付が1900年に...
-
【マクロ】VLOOKUPにて参照元に...
-
エクセルのセル内に分数などの...
-
Excelの警告について
-
Microsoft 365Excelの見開きペ...
-
Excel関数について教えてくださ...
-
シートの情報を別のシートへま...
-
Excelマクロで空白セルを詰めて...
-
【マクロ】フォルダ内にあるPDF...
-
index関数の説明をお願いします。
-
重複しない値を取り出したい
-
エクセルの数式バーのフォント...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報