Excelで重複値を検索し全て表示させる関数
- Excelで重複値を検索し、その値を全て表示させる関数の方法について教えてください。
- Sheet1のA列とB列にデータが入っており、Sheet2のA列のデータと一致する名前があれば、Sheet2のB列に対応した値を表示させる方法を教えてください。
- 名前が一致する場合はB列の値をSheet2に表示し、一致しない場合はB列は空欄のままにする方法を教えてください。
- ベストアンサー
Excel 重複値を検索し全て表示させる関数
Sheet1の１行目には見出しがあり、A列とB列の２行目から下にデータが入っています。（約２００行） A列 B列 田中 13 山本 8 谷口 11 鈴木 6 田中 10 佐々木 9 奥村 15 佐々木 15 佐々木 20 ・ ・ ・ Sheet2のA列１行目から下には別のデータが入っています。（約６００行） A列 太田川 山村 田中 多賀先 鈴木 奥村 幸田 ・ ・ ・ Sheet2のA列のデータと完全一致するデータ（名前）をSheet1のA列から探して、同じ名前があれば、その隣のB列にある数値をSheet2のB列に貼り付ける(B列が重複の場合、下に張り付ける。但し、A列の名前は1行目は表示、2行目からは表示、空欄でも構いません)。 見つからない場合はSheet2のB列は空欄のままです。 Sheet2 A列 B列 太田川 山村 田中 13 10 多賀先 鈴木 6 奥村 15 佐々木 9 15 20 幸田 ・ ・ ・ 何卒、ご指導の程宜しくお願い致します。
- chi_ko6262
- お礼率27% (33/121)
- Excel（エクセル）
- 回答数5
- ありがとう数10
- みんなの回答 （5）
- 専門家の回答
質問者が選んだベストアンサー
回答No.1様の仰る通り、Excelのワークシート関数では既に値が入力されている行の間に新たな行を挿入する事は出来ません。 ですから、Sheet1とSheet2のデータを基にして、Sheet3のA列～B列に結果が表示させる様にされては如何でしょうか? まず、Sheet2のA1セルに項目名等の 何らかの値を入力して下さい。 次に、Sheet3のA1セルに次の関数を入力して下さい。 =Sheet2!$A$1 次に、Sheet3のA2セルに次の関数を入力して下さい。 =IF(A1="","",IF(COUNTIF(A$1:A1,A1)<COUNTIF(Sheet1!$A:$A,A1),A1,INDEX(Sheet2!$A:$A,MATCH(A1,Sheet2!$A:$A,0)+1)&"")) 次に、Sheet3のB2セルに次の関数を入力して下さい。 =IF($A2="","",IF(COUNTIF(Sheet1!$A:$A,$A2),IF(ISERROR(1/(INDEX(Sheet1!$B:$B,SUMPRODUCT(ISNUMBER(ROW(Sheet1!$A$1:$A$999))*(COUNTIF(OFFSET(Sheet1!$A$1,,,ROW(Sheet1!$A$1:$A$999)-ROW(Sheet1!$A$1)+1),$A2)<COUNTIF($A$1:$A2,$A2)))+1)<>"")),"",INDEX(Sheet1!$B:$B,SUMPRODUCT(ISNUMBER(ROW(Sheet1!$A$1:$A$999))*(COUNTIF(OFFSET(Sheet1!$A$1,,,ROW(Sheet1!$A$1:$A$999)-ROW(Sheet1!$A$1)+1),$A2)<COUNTIF($A$1:$A2,$A2)))+1)),"")) 次に、Sheet3のA2～B2セルをコピーして、Sheet3のA列～B列の3行目以下に貼り付けて下さい。 以上です。
その他の回答 (4)
- imogasi
- ベストアンサー率27% (4737/17068)
よくある質問の再出なので考え方だけ。 ＞指導の程宜しくお願い致しますのご指導にあたり、回答は別ですが、知っておいたらよいと思って。 いままで下記のことを知らずに、のんきに関数での答えを求めている人が圧倒的に多い。 エクセル関数やその本質を、勉強不足が原因で、つかんでいない場合が多い。 （１）エクセルは式を入れたセルにしか答えが出せない。 ＶＢＡだと任意のセルにセットできる。 （２）式の繰り返しができない。 ＶＢＡだとできる。 （３）一時記憶しておく変数が使えない。ＶＢＡでは当たり前に使う。 そのため関数（ＶＢＡでのユーザー定義関数のことは除外）だと知恵を絞る必要がある。 ーーー エクセル関数では（条件により）抜出問題は苦手な個所だと思う。 （１）条件を付けての抜出し問題はＶＢＡ向けです。将来的にビジネスで使いこなそうと思えば、ＶＢＡをよく勉強しする必要がある。この件ではＦｉｎｄメソッドという者１つが中心。Ａｃｃｅｓｓなどならもっと簡単だろう。 またエクセル関数は、該当データの整理（見えかた、フォーマット）に注文を付けると 複雑化する。 （２）設計的に、複数該当がある場合は、関数が、難しく複雑になる （３）（２）について作業列を使わない方式だと式が複雑になる。 ーー＞だから初心者はできるだけ作業列を活用しろ。 （４）ふつうは、該当が元データの飛び飛び行に現れるが、これを検出するのは、ＣＯＵＮＴＩＦ（ＣｏｕｎｔｉｆＳ）関数などで簡単だが、これを行的に（ある列に）「詰めて！」表示したいのだろうが、これがむつかしい。 どの関数をどう組み合わせるかの方法が、天下り的に教えてもらい、覚えていくほかない。 （普通は、何年考えても、独力では、作り出す域にはならないと思うような技巧的な式。）回答に出た式のまる写しで、「よし」とするか。 ーーー Ｇｏｏｇｌｅで「small index 抜き出し」で照会し読んでみたら、この手の質問の概略は判るだろう。あるいは「エクセル 関数 抜出し」などで検索。
お礼
色々とご指摘頂き有難うございます。私もその中の一人かも知れませんね。 ご指摘頂いた点も含め、もう一度トライしてみます。 気づかせて頂き有難うございました。
- msMike
- ベストアンサー率20% (363/1775)
「Sheet2のA列１行目から下には別のデータが入っています」の直下の表には「奥村」と「田中」の間に「佐々木」は存在しないのに、 「見つからない場合はSheet2のB列は空欄のままです」の直下の表にノウノウと「佐々木」が登場しているのはなぜ？！ シッカリしてね！
お礼
今後はこのようなミスをする用が無い様に致します。ご指摘有難うございました。
補足
失礼いたしました。ご指摘の様に矛盾があります。先を急ぐあまり順番に気を付けづに質問してしまいました。申し訳ございません。
- f272
- ベストアンサー率46% (8019/17138)
田中や佐々木のように複数見つかった場合には，行を挿入することになるが，それをエクセルの関数で行うのは不可能です。VBAなら出来る。 行を挿入するのではなく，C列，D列...を使って表示するというのなら関数を使っても可能です。
お礼
参考になりました。有難うございます。宜しければ、補足コメントにあるようにVBAを教えて頂ければ幸いです。
補足
早速のお返事有難うございます。VBAで出来るのであればご指導いただけないでしょうか？何卒、宜しくお願い致します。
関連するQ&A
- エクセル 同じ値を探して隣の数値をコピーする
エクセルで以下の作業を簡単にする方法を教えて下さい。 Sheet1の１行目には見出しがあり、A列とB列の２行目から下にデータが入っています。（約２００行） A列 B列 田中 13 山本 8 谷口 11 鈴木 6 佐々木 9 奥村 15 ・ ・ ・ Sheet2のA列１行目から下には別のデータが入っています。（約６００行） A列 太田川 山村 田中 多賀先 鈴木 奥村 幸田 ・ ・ ・ Sheet2のA列のデータと完全一致するデータ（名前）をSheet1のA列から探して、同じ名前があれば、その隣のB列にある数値をSheet2のB列に貼り付ける。 見つからない場合はSheet2のB列は空欄のままです。 Sheet2 A列 B列 太田川 山村 田中 13 多賀先 鈴木 6 奥村 15 幸田 ・ ・ ・
- ベストアンサー
- オフィス系ソフト
- エクセルの関数について
エクセルの関数の作り方について教えてください。 A列が曜日、、月、火、水、、、、土、日 B列が５人の名前がランダムに入っている 田中 鈴木、小泉、佐々木、、、 とします。それがずっと、、約１０００行あります。 日曜日の鈴木さんの数をカウントする式は作れるでしょうか。
- ベストアンサー
- オフィス系ソフト
- エクセル関数について
下記のようなエクセルのデータを作りたいのですが、どのような関数を使えばいいかわかりません。 シート１ シート１１ シート１２ A B A B A B １ 田中 ５００円 １ 田中 ５００ １ 田中 ５００ ２ ２ シート２ A B A B A B １ 伊藤 ３００円 ３ 伊藤 ３００ 2 伊藤 ３００ ２ 佐藤 ４００円 ４ 佐藤 ４００ 3 佐藤 ４００ このようにシート１から１０まで２行入力する欄を作ります その中から大事なデータだけを集計するため一度シート１１に反映させます この時２行目が空欄の場合はシート１１も空欄になっています。 そしてこのシート１１から空白部分を除いて順番に並べるシート１２を作りたいです。 シート１１は=Sheet1!A1のように単純に反映ができるのかと思うのですが シート１２へ空白を除いて反映させる方法がわかりません。 わかりづらい質問でも申し訳ありませんが 何卒よろしくお願い致します。
- ベストアンサー
- その他MS Office製品
- ExcelのSUMIF関数について質問です
A列 │B列 │C列│ ----十---十----十 田中│ 男│177 │ 鈴木│ 男│169 │ 吉田│ 女│162 │ 田中│ 女│161 │ 佐藤│ 女│164 │ 田中│ 男│174 │ 鈴木│ 女│166 │ 上記のようなデータがsheet1にあって、このうち A列が”田中”で、かつB列が”男”に該当する C列のデータの合計をsheet2で計算したいのですが これはどのようにすればいいのでしょうか。 下記のように記述するところまではやったのですが、 これに「なおかつsheet1のB列がsheet2のB3（男）に一致する場合」 という検索条件を付け加える方法が分かりません。 （↓Sheet2のC3セル） =SUMIF(Sheet1!A:A,Sheet2!A3,Sheet1!C:C) （sheet2のA3には”田中”、B3には”男”と入力してあり、 計算結果をC3に出力したいと思っています。） OSはWindows2000、エクセルはEXCEL2000を使用しています。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセルで、ある条件で検索し、対応するデーターをすべて表示させる方法を教えてください。
こんばんは。大変お世話になっています。 エクセルでシフト表を作っています。 職員は１００名ほど、勤務時間の種類は８種類ほどあります。 Ａ列に名前が並び、Ｂ列の２行目から日付が並んでいます。 １日 ２日 ３日 ４日 ・・・ 佐々木 Ａ 休 Ｃ Ａ 佐藤 休 Ｄ Ｄ Ｂ 鈴木 Ｂ Ｂ Ａ 休 田中 休 Ｄ Ｄ Ｄ 別のシートに、その日の勤務職員をシフト別にそれぞれ表示させた一覧表を作りたいのです。 =LOOKUP(F3,B2:B5,A2:A5) （Ｆ３＝シフトコードが入っている） とLOOKUP関数で入れてみたのですが、一番最初の反応する名前が表示されるだけで、すべての対応者の名前は出ませんでした。 どうすればできるでしょうか？ 教えてください。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- Excelでどのような関数を使えばいいでしょうか。
関数について教えて下さい。 Excelで、同じBook内に以下のような２シートがあるときに、 （Sheet1） Ａ Ｂ Ｃ Ｄ １ １ あい 12 りんご ２ うえ 34 みかん ３ おか 56 イチゴ ４ １ おか 78 イチゴ （Sheet2） Ａ Ｂ Ｃ Ｄ １ あい 11 りんご ２ うえ 34 みかん ３ おか 78 イチゴ ４ うえ 34 みかん Sheet2のA列にSheet1にあるデータを入れたいのです。 まず、列B,C,Dのそれぞれのデータが横に３つ一致するものを探して 横３つのデータが同じなら、Sheet2のA列に、 Sheet1のA列にあるデータをシートＢにも持ってくる、 というようなイメージです。 （Sheet2） Ａ Ｂ Ｃ Ｄ １ あい 11 りんご ２ うえ 34 みかん ３ １ おか 78 イチゴ ４ うえ 34 みかん ↑このようにしたいのです。 A列にどのような関数を入れればいいのでしょう。 （A列にあるのは１か空欄かのどちらかです。 同じ３つの組み合わせで、１の場合と空欄の場合両方があることはありません。） また、Sheet2には、A列に１とついた行に色を付けたいのです。 例えば、上記の例ですと Sheet2のB3:D3のセルに色をつけたいのです。 関数や条件付書式などで、できますでしょうか。
- ベストアンサー
- オフィス系ソフト
- エクセルのCOUNTIF関数について質問です。
エクセルのCOUNTIF関数について質問です。 例えば下記のように入力されているとき、「田中」が含まれるセルを数えるとき、Sheet2のB1の列には「=COUNTIF(Sheet1!A:A,"*田中*")」と入力すればいいと思いますが、「"*田中*"」の部分を「A1」、上田の場合は「A2」とする場合はどうすれば良いでしょうか？ ＜Sheet1＞ A 1 上田夫妻 2 田中兄弟 3 田中太郎 4 田中さん 5 鈴木姉妹 ＜Sheet2＞ A B 1 田中 2 上田 3 鈴木
- ベストアンサー
- その他MS Office製品
- エクセル関数
シート1という名前のシートと シート2という名前のシート2つのシートがあります。 シート1は ・A列には従業員の氏名が入っています ↓このように A 空欄 鈴木 伊藤 佐藤 ・資格名は見出しとしてB2～Q2まで入ってます(見出しとして) ↓シート1の全体はこのような感じ A B C D 空欄 資格1 資格2 資格3 鈴木 伊藤 佐藤 次にシート2は ・A列に個人コードが入ってます ・B列に名前が入ってます ・C列に資格名称が入ってます ・D列に資格取得日が入ってます ↓シートの全体はこのような感じ A B C D 1 鈴木 資格1 6月 1 鈴木 資格2 7月 2 伊藤 資格3 8月 2 伊藤 資格1 9月 2 伊藤 資格2 10月 3 佐藤 資格3 11月 上記のようなシートがあります。 ここでシート1にシート2の値を返していきたいのですが 見ての通り書式はバラバラです 例えばシート2を見て鈴木は資格1、資格2、資格3を持っています 資格1だったら○をシート1の鈴木と資格1がぶつかってるセルに 資格2だったら○をシート1の鈴木と資格2がぶつかってるセルに 返していきたいのです 何か有効な関数などはありませんでしょうか？ むしろ関数でできるのでしょうか？ 説明が訳分からなくてすみません。
- 締切済み
- Windows 7
- excelで検索用シートを作りたい。
お世話になります。 excelでわからない事があり、困っています。 検索用シート データシート Ａ Ｂ Ａ Ｂ Ｃ 1 部署名 (空欄) 1 部署名 担当者 書類名 2 担当者 (空欄) 2 総務部 鈴木 新人用の書類 3 書類名 (空欄) 3 人事部 田中 部長の書類 上図の様に検索用シートとデータ用シートが同じBook内にあります。 検索用シートのB列は空欄になっており、例えば(1-B)に検索したい部署名の一部、又は全てを入れると、(2-B)及び(3-B)にその部署名に対応した、担当者、書類名が表示 される。 また、検索結果が複数ある場合には、上のものから順々に検索用シートの空欄に表示される。 部署名での検索以外にも、同様に担当者、書類名でも検索できる。 この様な仕様にしたいのですが、どのすれば良いでしょうか？ どうぞよろしくお願い致します。
- ベストアンサー
- オフィス系ソフト
お礼
すごーいですね。今、目が点になっています。・・・・・・ 有難うございました。物凄い物を見させて頂きました。感謝の言葉を忘れて呆然としています。結果についても文句は全くありませんし、何とお礼したらいいか分かりません。どうしたらkagakusukiさんみたいにこの短時間の間にこれ程の関数が出来上がるのでしょうか。 ここでは文字でしかあらわせませんが、本当に感謝、感謝の気持ちでいっぱいです。有難うございました。