• 締切済み

エクセル 複数範囲から重複する数字を抽出

エクセルで複数の範囲すべてに同じ数字があった場合、その数字を抽出することは出来ますか? 例:A1| B1 C1 D1 E1|F1 G1 H1 I1|J1 K1 L1 M1| N1 O1 P1 Q1| (1) B1~E1=(20 50 60) (2) F1~I1=(20 50 60) (3) J1~M1=(20 60) (4) N1~Q1=(20 50 80 ) (1)B1~E1、(2)F1~I1、(3)J1~M1、(4)N1~Q1の4つの範囲すべてに入っているのは、”20”になります。 A1に”20”と出したいのですが、どうしたら良いでしょうか。 どうぞよろしくお願いします。

みんなの回答

  • KURUMITO
  • ベストアンサー率42% (1835/4283)
回答No.3

作業列を使って対応する方法です。 例えばB1セルからQ1セルまで数値が入力されているのでしたら、R1セルには次の式を入力してU1セルまで横にドラッグコピーします。 =COUNTIF(INDEX($B1:$Q1,COLUMN(A1)*4-3):INDEX($B1:$Q1,COLUMN(A1)*4),$B1)+COUNTIF(INDEX($B1:$Q1,COLUMN(A1)*4-3):INDEX($B1:$Q1,COLUMN(A1)*4),$C1)+COUNTIF(INDEX($B1:$Q1,COLUMN(A1)*4-3):INDEX($B1:$Q1,COLUMN(A1)*4),$D1)+COUNTIF(INDEX($B1:$Q1,COLUMN(A1)*4-3):INDEX($B1:$Q1,COLUMN(A1)*4),$E1) その後にA1セルには次の式を入力します。 =IF(COUNTIF(R1:U1,4),INDEX(B1:E1,MATCH(4,R1:U1,0)),"")

  • keithin
  • ベストアンサー率66% (5278/7940)
回答No.2

>上記の例ではこの数式で思った通りのことが、できました このように、教わった回答を自力でちょこちょこっと応用すればきっと出来ると、甘く見てたのに実際はそうじゃなかったワケです。 >範囲の中の数字は別表からVLOOKUPからもってきた数字なのです 手で記入してもVLOOKUPで持ってきても、全く何も変わりはありません。実際に、あなたが「試してみて上手くいったサンプル」のその数字の部分に、VLOOKUPの数式を記入して試してみれば簡単に確認できることです。 一体全体何が悪くてどうすればよかったのか、「実際のエクセルがどうなっているのか」目に見えるあなたのエクセルの『事実』が示されていないので、これ以上何も回答できません。 ●セルに実際に記入されている内容が、ご相談で書かれているのと違うから失敗しているのかもしれません ●単純にセル配置などが違うのを、数式を適切に修正できなかっただけかもしれません。 >例とほとんどかわりません ○「実際には」どこのセル番地に正しく具体的に何が記入されていて ○教わった回答を見て「あなたは実際に」どういう内容で試してみたら ○どうなって欲しいのに実際にはどんな結果が現れて どんなエクセルを持っていて、どうなってしまって「うまくいかなかった」のか、目に見えるあなたのエクセルの事実をキチンとご相談に書いて。 このご相談は解決で一旦閉じてから、改めて丁寧にご相談を書いて、投稿し直して下さい。

  • keithin
  • ベストアンサー率66% (5278/7940)
回答No.1

たとえば =INDEX(B1:E1,MIN(IF(COUNTIF(F1:I1,B1:E1)+COUNTIF(J1:M1,B1:E1)+COUNTIF(N1:Q1,B1:E1)=3,COLUMN(B1:E1)))-1) と記入し、必ずコントロールキーとシフトキーを押しながらEnterで入力します。 ちなみに一つも該当が無い場合は、判りやすくエラーを表示します。 ご相談のエクセルのバージョンがご相談に明記されていないので今後注意して欲しいですが、エクセル2007以降であればIFERROR関数辺りを追加して対処するのが良い方法です。 #他にもかなりいろいろと気になる点はありますが、まぁ気にせずに出来ることで使ってください。

hana61087
質問者

お礼

ありがとうございます! 上記の例ではこの数式で思った通りのことが、できました! しかし、自分の表に当てはめてみると、うまくいきません。 (例とほとんどかわりませんが。。。) 範囲の中の数字は別表からVLOOKUPからもってきた数字なのですが、その場合でもこの数式つかうことはできるでしょうか?? 夜分遅くに、すみません! バージョンは2007です。

関連するQ&A

  • エクセルで、0~9数字の中で使ってない数字を抽出す

    エクセルで、A1~L1のセルに0~9の数字がランダムで並んでるとします。0~9数字の中で使ってない数字をM1以降に並べたいのです。関数を使ってやりたいのですが、誰かご教授下さい。 こんな感じですが・・・   A・B・C・D・E・F・G・H・I・J・K・L---M・N 1  1・1・4・9・8・0・3・2・5・5・4・2----6・7 2  4・6・7・3・2・5・1・0・1・9・6・4----8 3  7・0・5・9・1・3・9・4・7・8・7・6----2

  • リストのデータを重複なしでランダムに抽出する

    シート1に下記のように14種類の名前リストがあります     A 1   A 2   B 3   C 4   D 5   E 6   F 7   G 8   H 9   I 10  J  11  K 12  L 13  M 14  N 上記の名前を下記のように別シートの数列おきの列(行は同一)に重複なしに行毎にランダムに抽出する事が関数で出来るでしょうか?(エクセルは2010です) ちなみに下記は一列おきのセルに抽出した例です   A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA 1 D   L   K    I    A   M   N    B   H   J    C    F    E   G 2 K   J   M   H    I   G   F    E   D    A    B   N   C    L 3 E   J   A    L   B   M    K   C   N    G    F   D    H   I どなたか教えていただける方がおりましたらよろしくお願いします。

  • ランダムで抽出した数字と該当する数字は

    下記のような表があります。  A B C D E F G H I J K L M N O 1 範囲 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 2 50回毎 2 3 5 6 9 10 11 13 14 16 19 21 22 25 26  =RANDBETWEEN(1,15) で、5つの乱数を出しています。 A B C D E 乱数 3 1 8 13 4 乱数で選んだ数字が、範囲数字(1~15)に該当しているとき、50回毎のどの数字に当てはまるか、関数で求める方法を知りたいのです。 第1数字 3 が、範囲 3 の時、50回毎の 5 第2数字 1 が、範囲 1 の時、50回毎の 2 第3数字 8 が、範囲 8 の時、50回毎の 13 第4数字 13 が、範囲 13 の時、50回毎の 22 第5数字 4 が、範囲 4 の時、50回毎の 6 と、いう感じです。 下手な説明でわかりにくいかと思いますが、どなたかお教え願いますか。     

    • 締切済み
    • Mac
  • エクセル、任意の「行」だけを抽出したい。

    こんなことできますか? A1:F100のセル内にランダムに数字が入っています。 質問1) この中から 1行目→3行目→5行目と一つ飛ばしで入っているデータを抽出してH1:M100の範囲内に表示させるにはどのような操作をすればいいのでしょうか?関数とかで簡単に抽出表示させる技とかあればいいのですが・・・。 A1-B1-C1-D1-E1-F1 A2-B2-C2-D2-E2-F2 A3-B3-C3-D3-E3-F3 A4-B4-C4-D4-E4-F4 A5-B5-C5-D5-E5-F5 A6-B6-C6-D6-E6-F6 A7-B7-C7-D7-E7-F7 ・・・ A100-B100-C100-D100-E100-F100 の中から A1-B1-C1-D1-E1-F1 A3-B3-C3-D3-E3-F3 A5-B5-C5-D5-E5-F5 ・・・ を抽出して H1-I1-J1-K1-L1-M1 ・・・ H100-I100-J100-K100-L100-M100 の範囲内に表示させたいです。 質問2) 同様に、 1行目→4行目→7行目と二つ飛ばしで入っているデータを抽出してO1:T100の範囲内に表示させるにはどのような操作をすればいいのでしょうか?関数とかで簡単に抽出表示させる技とかあればいいのですが・・・。 ※ 説明がわかりにくいかもしれませんが、よろしくお願いします。

  • エクセルの使い方についてお教えください。

    エクセルの使い方についてお教えください。 例えば、 a | b | f c | g | j d | h | k | m e | i | l | n | o | のように、右肩下がりになっている列を、 a | b | c | d | e | f | g | h | i | j | k | l | m | n | o | のように一列にまとめる方法は無いでしょうか? 何卒よろしくお願いいたします。

  • リストのデータを重複なしでランダムに抽出する

    シート1に下記のように14種類の名前リストがあります     A 1   A 2   B 3   C 4   D 5   E 6   F 7   G 8   H 9   I 10  J  11  K 12  L 13  M 14  N 上記の名前を下記のように別シートの数列おきの列(行は同一)に重複なしに行毎にランダムに抽出する事が関数で出来るでしょうか?(エクセルは2010です) ちなみに下記は一列おきのセルに抽出した例です   A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA 1 D   L   K    I    A   M   N    B   H   J    C    F    E   G 2 K   J   M   H    I   G   F    E   D    A    B   N   C    L 3 E   J   A    L   B   M    K   C   N    G    F   D    H   I どなたか教えていただける方がおりましたらよろしくお願いします。 という質問をさせていただき、mike gさんに下記のような回答をいただきました 1.Sheet1 の例えばセル C1 に式 =RAND() を入力して、此れを右に2列(注1参照)ドラッグ&ペースト 2.範囲 C1:E1 を下方にズズーッと(14行目まで)ドラッグ&ペースト 以下は別シート(Sheet2)における操作です。 3.セル A1 に次式を入力して、此れを右方にズズーッと(セル AA1 まで)ドラッグ&ペースト(注2参照)   =IF(MOD(COLUMN(),2)=1,INDEX(Sheet1!$A$1:$A$14,MATCH(SMALL(OFFSET(Sheet1!$C$1,,ROW()-1,14,),CEILING(COLUMN()/2,1)),OFFSET(Sheet1!$C$1,,ROW()-1,14,),0)),"") 4.1行目全体を下方にズズーッと(3行目まで)ドラッグ&ペースト 注1:「右に2列」は別シートの行数が3行の場合で、4行(5行)の場合は「右に3列(4列)」に読み替える。 注2:式中の 2 は「一列おき」の場合で、二列(三列)おきの場合は 2 を 3(4) に書き替える。 これで完璧に出来たのですが 実際にはSheet2のセルH6から15列おきに抽出したいのです。 15列おきは出来たのですが、セルH6から抽出を始めるにはどの部分を変更すれば良いのでしょうか? どなたか教えて頂ける方がおりましたらよろしくお願いします。

  • エクセルについて

    エクセルにおいて、下記のように1~5のみ残し、その他無くなった数字は見えなくする、もしくわ文字の色を白にして見えないようにしたいと思っています。   A B C D E F G H I J K L M N O P Q R S T U 1 0 0 0 0 0 1 2 3 3 3 3 3 4 5 5 5 5 5 5 5 5 2 0 0 1 2 2 2 2 3 4 4 5 5 5 5 5 5 5 5 5 5 5         ↓         ↓   A B C D E F G H I J K L M N O P Q R S T U 1 1 2 3 3 3 3 3 4 5 2   1 2 2 2 2 3 4 4 5 私は基本的な関数は使用出来ますが、VBAは勉強し始めたばかりです…。 良い方法をご教示頂ければと思っております。 よろしくお願いいたします。 ※記入した例において、1~5の列がズレていますが、行も列も同じばしょのままでお願いします。

  • エクセル・時間の合計が合わない

    実労働時間の1ヶ月の合計をだすのに、SUMを使ったのですが、 合計がすごく小さい数字になります。 表示は※※:※※で表示しています。 表の内容は下記の通りです。 4行目に題名 ・B出勤時刻・C休憩開始・D休憩終了・E退社時刻 F4・I4・L4・N4・Q4=0:00 G4・J4・M4・O4・R4=17:00 F  =IF($E5-F$4>0,$E5-F$4,0)-IF($B5-F$4>0,$B5-F$4,0) G  =IF($E5-G$4>0,$E5-G$4,0)-IF($B5-G$4>0,$B5-G$4,0) I  =F5-SUM(J5:$K5) L  =IF($D5-L$4>0,$D5-L$4,0)-IF($C5-L$4>0,$C5-L$4,0) M  =IF($D5-M$4>0,$D5-M$4,0)-IF($C5-M$4>0,$C5-M$4,0) N  =L5-SUM(O5:$P5) O  =M5-SUM(P5:$P5) Q  =I5-N5  (実労働時間17時までの1日の計) R  =J5-O5  (実労働時間17時からの1日の計) Qに=SUM(Q5:Q14) Rに=SUM(R5:R14) を入力しましたが、まったく違う時間数になります。 どうすれば正しい計算式がでるのでしょうか? お教え願いたくお願い申し上げます。

  • エクセルについて

    エクセルで A B C D E F G H I J K L M N O P Q R … と並んでいるデータを A B C D E F G H I J K L … のように並べ変えたいのですが、最も効率のよい方法を教えてください。 上記のアルファベット1つにセル1つです。 Office2007を使用しています。 以上、宜しくお願い致します。

  • 難解なエクセル集計の仕方を教えてください!!

    以下のように集計したいのですが、いい方法はありませんか? 解りにくいですが、宜しくお願いします!!! ------------------------ 【関係図】  (1)(2)という2つグループの関係を表しています。  組織図のようなものと思ってくださって結構です。 ■元データ 【表あ】  関係図を分解し、2つごとの関係に直したデータです。   【表い】  それぞれA-Oに対応する数字が入っています。 ■集計要望 【表あ】と【表い】ふたつのデータを元に、関係図に基づいた集計をしたい。 例: 1.Aを集計すると、Aの下位のものはすべて集計したい。(A~Gまでの集計) 2.Dを集計すると、Dの下位のみが集計されるようにしたい。(D+E+F+G) 3.Fを集計すると、Fの下位のみ集計したい。(F+G) 4. Iを集計=I+K、Jを集計=J+L+M+N+O 【関係図】  (1)A-B-C      D-E       F-G  (2)H-I-K     J-L-M       N-O 【表あ】  A ― B  B ― C  B ― D  D ― E  D ― F  F ― G  H ― I  I ― K  H ― J  J ― L  L ― M  L ― N  N ― O 【表い】 A=1 B=2 C=4 D=5 E=6 F=7 G=8 H=9 I=10 J=11 K=12 L=13 M=14 N=15 O=16

専門家に質問してみよう