エクセル関数について

質問

エクセルで重複に関する質問です。

［元データ］
Ａ　　　　　　Ｂ　　　　　　　Ｃ　　　　　　　
会員番号　氏名　　　　　選択コース
１１１１　　　山田一郎　　英語
１２１２　　　鈴木恵子　　英語
１３２３　　　佐藤次郎　　算数
１１１１　　　山田一郎　　国語
１２１２　　　鈴木恵子　　算数
１２１２　　　鈴木恵子　　国語
という表があるとします。（実際のデータはもっとある）

山田さんは英語と国語の２コースを選択。
鈴木さんは英語と国語と算数の３コースを選択。
佐藤さんは算数だけを選択。
というわけです。

ここで、D列に、

Ａ　　　　　　Ｂ　　　　　　　Ｃ　　　　　　　　　　D　　　　
会員番号　氏名　　　　　　選択コース　　　総選択内容
１１１１　　　山田一郎　　　英語　　　　　　　英語・国語
１２１２　　　鈴木恵子　　　英語　　　　　　　英語・国語・算数
１３２３　　　佐藤次郎　　　算数　　　　　　　算数のみ
１１１１　　　山田一郎　　　国語　　　　　　　英語・国語
１２１２　　　鈴木恵子　　　算数　　　　　　　英語・算数・国語
１２１２　　　鈴木恵子　　　国語　　　　　　　英語・算数・国語

という表示をさせたいのですが、
どうすればいいでしょう？

いろいろあると思うのですが、
作業させる人がPC操作が苦手なので、
元データを貼り付けただけで、
自動的にD列が表示されるようにしなければならないのです。
ピポットやマクロなどは使えないのです。
毎回私がやってあげられないので、
関数のみで、（勿論、隠しシートで関数を組み合わせることになると思いますが）、
あるいは、とにかく作業させる人が何もしないのに元データを貼り付けただけで、
D列が自動的に表示される、という状態にする方法があれば、どうか教えてください。

よろしく御願いします。

回答No.6

imogasi · ベストアンサー

#2です。ここでは、関数を使い、作業列を使うやり方をやってみます。
例データ
Sheet1
元データA1:C７
D2:D7は書き関数で作る作業列データ。
会員番号 氏名 選択コース
1111 山田一郎 英語 山田一郎1
1212 鈴木恵子 英語 鈴木恵子1
1323 佐藤次郎 算数 佐藤次郎1
1111 山田一郎 国語 山田一郎2
1212 鈴木恵子 算数 鈴木恵子2
1212 鈴木恵子 国語 鈴木恵子3
D2の式は
=B2&COUNTIF($B$2:B2,B2)
余分行に、前もって多数行に式を入れておくなら　=IF(A2="","",B2&COUNTIF($B$2:B2,B2))
これは同じ氏名に上行から連番を振ったことに当たる。
ーー
B列でデーターフィルターフィルタオプションの設定で、重複するレコードは無視するの操作を行う。
操作の前もってF列にF1には項目見出し「氏名」が必要です。
氏名
山田一郎
鈴木恵子
佐藤次郎
を作る（もれも無くダブリも無い氏名のリスト）
ーー
G2に=INDEX($C$1:$C$100,MATCH($F2&COLUMN()-6,$D$1:$D$100,0))
を入れて（全般に許容最多科目列数だけ式を右方向に複写）
G2:J２（最大科目数４の場合）を範囲指定して、下方向に式を複写。
ーー
結果
氏名
山田一郎 英語 国語 #N/A
鈴木恵子 英語 算数 国語
佐藤次郎 算数 #N/A #N/A
ーー
これを私はimogasi方式となずけて照会している。
作業列を使う
同じ値（氏名）に上行から連番を降る
連番をCOLUMNで関連して検索し（MATCH）、INDEXでひるような列の行の値を持ってくる。
結果を別シートへ持っていくことも式を修正して（Sheet1のデータは番地にSheet1!を添える）簡単。
作業列を別シート（作業シート）に持っていくことも出来る。
ーー
欠点は作業列を使うことである。
式も簡単とは言いがたいかも知れない。
他の回答者の複雑な式より、多少判りやすいと思っている。
ーー
＃N/Aを出さないようにするには、エラーだったら空白を返す式にする。
G2に　=IF(ISERROR(MATCH($F2&COLUMN()-6,$D$1:$D$100,0)),"",INDEX($C$1:$C$100,MATCH($F2&COLUMN()-6,$D$1:$D$100,0)))
のような式である。

回答No.5

kagakusuki

御使いのＥｘｃｅｌのバージョンがＥｘｃｅｌ2007以降であれば、次の様な数式をD2セルに入力してから、D2セルをコピーして、D3以下に貼り付けて下さい。

=IF($B2="","",REPLACE(IF(COUNTIFS($B:$B,$B2,$C:$C,"英語"),"・英語","")&IF(COUNTIFS($B:$B,$B2,$C:$C,"算数"),"・算数","")&IF(COUNTIFS($B:$B,$B2,$C:$C,"国語"),"・国語","")&IF(COUNTIFS($B:$B,$B2,$C:$C,"理科"),"・理科","")&IF(COUNTIFS($B:$B,$B2,$C:$C,"社会"),"・社会","")&IF(COUNTIF($B:$B,$B2)=1,"のみ",""),1,1,))

　又、Ｅｘｃｅｌ2007よりも前のバージョンで、同じ事を行う場合には、次の様な数式となります。

=IF($B2="","",REPLACE(IF(SUMPRODUCT(($B$1:INDEX($B:$B,MATCH("゛",$B:$B,-1))=$B2)*($C$1:INDEX($C:$C,MATCH("゛",$B:$B,-1))="英語")),"・英語","")&IF(SUMPRODUCT(($B$1:INDEX($B:$B,MATCH("゛",$B:$B,-1))=$B2)*($C$1:INDEX($C:$C,MATCH("゛",$B:$B,-1))="算数")),"・算数","")&IF(SUMPRODUCT(($B$1:INDEX($B:$B,MATCH("゛",$B:$B,-1))=$B2)*($C$1:INDEX($C:$C,MATCH("゛",$B:$B,-1))="国語")),"・国語","")&IF(SUMPRODUCT(($B$1:INDEX($B:$B,MATCH("゛",$B:$B,-1))=$B2)*($C$1:INDEX($C:$C,MATCH("゛",$B:$B,-1))="理科")),"・理科","")&IF(SUMPRODUCT(($B$1:INDEX($B:$B,MATCH("゛",$B:$B,-1))=$B2)*($C$1:INDEX($C:$C,MATCH("゛",$B:$B,-1))="社会")),"・社会","")&IF(COUNTIF($B:$B,$B2)=1,"のみ",""),1,1,))

　只、2番目に挙げた数式やANo.1様の数式の様に、SUMPRODUCT関数を使用した方法の場合、表の行数があまり多くなると計算に時間が掛かる様になります。
　ですから、計算に要する時間が長くなり過ぎる様な場合には、対策して作業列を使用する方法もあります。
　今仮に、元データが入力されているシートがSheet1で、Sheet2のA列を作業列として使用するものとします。
　まず、Sheet2のA2セルに次の数式を入力して下さい。

=Sheet1!$B2&Sheet1!$C2

　次に、Sheet2のA2セルをコピーして、Sheet2のA3以下に貼り付けて下さい。
　次に、Sheet1のD2セルに次の数式を入力して下さい。

=IF($B2="","",REPLACE(IF(COUNTIF(Sheet2!$A:$A,$B2&"英語"),"・英語","")&IF(COUNTIF(Sheet2!$A:$A,$B2&"算数"),"・算数","")&IF(COUNTIF(Sheet2!$A:$A,$B2&"国語"),"・国語","")&IF(COUNTIF(Sheet2!$A:$A,$B2&"理科"),"・理科","")&IF(COUNTIF(Sheet2!$A:$A,$B2&"社会"),"・社会","")&IF(COUNTIF($B:$B,$B2)=1,"のみ",""),1,1,))

　次に、Sheet1のD2セルをコピーして、Sheet1のD3以下に貼り付けて下さい。
　以上です。

回答No.4

KURUMITO

エクセルの初心者がダダの入力だけでお望みの結果を表示させるようにするためには多少の作業列を作って、見やすく表示させることが重要でしょう。あなたの表示の方法が必ずしもベストとは思いませんし、ここでの提案もベストではないでしょう。次のようにしてはいかがでしょう。
初めにどれだけの人の会員数や氏名があるかを明らかにするため元の表（シート1とします）のD列を作業列としてD2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTIF(A$2:A2,A2)=1,A2,"")

次にシート2にお望みに近い形で結果を表示させることにします。シート2ではA1セルに会員番号、B1セルに氏名、C1セルに国語、D1セルに算数、E1セルに英語、・・・と右側に教科科目を入力します。
そこでA2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(OR(ROW(A1)>COUNT(Sheet1!$D:$D),A$1=""),"",IF(COLUMN(A1)<=2,INDEX(Sheet1!$A:$B,MATCH(SMALL(Sheet1!$D:$D,ROW(A1)),Sheet1!$D:$D,0),COLUMN(A1)),IF(SUMPRODUCT((Sheet1!$A$2:$A$1000=$A2)*(Sheet1!$C$2:$C$1000=A$1))>0,"○","×")))

これで会員番号順に氏名が重複することなく並べられ、しかも、それぞれの会員がどの教科を受け、どの教科を受けていないかが一目瞭然となりますね。お尋ねの方法では見ずらいのではないでしょうか？
勿論新たな会員が追加されても自動的にその名前や教科などが自動的に表示されます。

もしも、同じ会員の人が過去に同じ教科で記載されていることから二重に入力されることを防ぎたいためにD列の表示を考えておられるのでしたら、その場合にはC列の教科の入力ができないように設定することがベターでしょう。

その場合にはシート1のC列を選択してから「データの入力規則」で入力の種類を「ユーザー設定」にして入力の窓には次の式を入力すればよいでしょう。

=SUMPRODUCT((A$2:A$1000=A1)*(C$2:C$1000=C1))=1

回答No.3

keithin

単純に工夫の問題です。それ故，ご相談に嘘があると工夫の前提が外れて全く用をなさなくなります。

シート２のC2に
=IF(Sheet1!C2="","",Sheet1!C2&" "&IF(COUNTIF(Sheet1!B3:B1000,Sheet1!B2),INDEX(C3:C1000,MATCH(Sheet1!B2,Sheet1!B3:B1000,0)),""))
以下沢山コピーしておく

シート１のD2に
=IF(B2="","",SUBSTITUTE(TRIM(INDEX(Sheet2!C:C,MATCH(B2,B:B,0)))," ","・"))
以下沢山コピーしておく

回答No.2

imogasi

質問者がエクセルのことを十分知らず、勝手な条件をつけて、回答者にクイズ的な問題を発しているが、関数では回答が複雑になりすぎるのだ。
エクセルの関数でも少し勉強し考えたらどうです。エクセルの関数では、表の組み換えや、抜き出し問題には、良い関数が無く（注）何種も組み合わせたり、配列数式でやったり、作業列を使ったりしてやるのだが複雑である。
（注）２番目に該当する行をとらえる、ズバリの関数が無いため。
回答者の出す式を、そのままコピペしなければ、一生考えても出てこないような名人芸による式になる。
質問者の場合に当てはめるとき、式をどう修正すべきか判らない人も多かろう。
Googleで「imogasi方式」で照会すれば、数百の抜き出し問題の質問回答が出るが、その回答の複雑さを見れなわかる。
私はこういう業務がらみの処理をするには、VBAの習得は必須と力説している。
VBAでも勉強して考えるべきことだ。素直な考え方で組めるから。
＞作業させる人がPC操作が苦手なので、・・
またエクセルは、他人に利用してもらうにに適したソフトではない。全体が見えるが、何処でも壊されたりする。
元データを貼り付けただけで・・はおかしい。利用者が不慣れであればあるほど、VBAなどで面倒を見ないとならない。
むしろアクセスなどが適している。
利用者をエクセル未経験者に見立てながら、質問は丸投げで、質問者は考えて無いように見える。
＞毎回私がやってあげられないので
質問者がやってあげているというのはどういう方式ですか。

お礼

ありがとうございます。

おっしゃる通り、私の勉強不足です。

いろいろな事情があり、同僚が泣いているのに下手な同情心をおこし、
１０年前にちょっとだけかじったエクセルで手伝ってみたのです。
私がやってあげられないというのは、
彼女の担当の仕事なので、
私がまるまるその作表を出すわけには、
時間的にも立場的にも難しいという意味なのです。

要するに、月に2回、在籍簿を作成する仕事なのですが、
元のデータを会社の専用システムからコピーしてエクセルに貼って、
それで作成しろという上からの命令なのです。

元データはCG列×４００行位まである大きなテーブルになっていて、
そこから毎回必要項目だけ抽出するのは、
抜き出し間違えたり貼り間違えたり、書式を消してしまったりで、
ひどく大変と同僚が泣いていたのです。

そこで、私の無い知恵を絞って、
とにかく全部コピーして、全部貼り付けたら、
あらかじめ作成しておいた雛形の表に保護をかけたシートに、
必要項目が割り振られて入って、印刷するだけで完成する様にまではしたのですが、
最後の要望のこの作業だけが、
私のつたないエクセルの知識では解決できなかったのです。

一時はIFとCOUNTIFを使って重複を出し、
重複３つなら下の行二つの該当内容を＆でつないで表示させてみたりしたのですが、
当然、重複なしの１つだけの場合か、重複していても一番上の行ならいいのですが、
次の行からは関係ない下の行のほかの人のデータを拾ってしまうし・・・と、
我ながら煮詰まって、バカなことをしてしまったり・・・。

VBA、難しそうですが、
もういちど、エクセルやアクセスも含め、じっくり勉強してみたくなりました。

本当にありがとうございます。

＞利用者が不慣れであればあるほど、VBAなどで面倒を見ないとならない。

なるほど・・・です。素直にありがとうございます。
私なんか・・・と思ってエクセルとアクセスを生半可にかじったままにしていましたが、
この一言、VBA勉強してみようかなという意欲がわいてきました。

回答No.1

mshr1962

D2=IF(COUNTIF(A$2:A$100,A2)=1,C2&"のみ",CHOOSE(SUMPRODUCT((A$2:A$100=A2)*((C$2:C$100="英語")+(C$2:C$100="算数")*2+(C$2:C$100="国語")*4)),,,"英語・算数",,"英語・国語","算数・国語","英語・算数・国語"))

補足

さっそくの回答ありがとうございます。
取り急ぎ、明日にでもやってみます。

また何かあったらお伺いしてしまうと思いますが、
よろしく御願いします。
本当にありがとうございます。