エクセルで全ての範囲で同じ値がある場合に抜き出す。

質問

エクセルで指定範囲のセル,例えば、範囲「A」A1～A3、範囲「B」A10～A15、範囲「C」A20～A25,
これらそれぞれの範囲内にあるセルの値が全ての範囲に同じ値が入ってる場合、その値を抜き出してB列に表示する。
A
４番
11番
16番

B
４番
15番
７番
11番
７番

C
４番
11番
16番
５番
６番

上記の様にA列にデータがあります。この場合全ての範囲にあるのは。４番と１１番なので、B列に４番と１１番を縦に並べる。

B列に入れる式を教えて下さい。

回答No.2

noname#204879 · ベストアンサー

B1: =IF(SUM(COUNTIF(A$10:A$15,A1),COUNTIF(A$20:A$25,A1))=2,A1,"")

お礼

ありがとうございました。

回答No.4

MackyNo1

関数で表示したいなら以下のような配列数式を使うことになります(Excel2007以降のバージョンを使用している場合）。
配列数式ですので、上記の式を入力後Ctrl+Shift+Enterで確定してください。

=IFERROR(INDEX(A:A,SMALL(IF(COUNTIF($A$10:$A$15,$A$1:$A$3)*COUNTIF($A$20:$A$25,$A$1:$A$3),ROW($A$1:$A$3)),ROW(A1))),"")

お礼

ありがとうございました。

回答No.3

tom04

こんばんは！
VBAになってしまいますが
一例です。

Sheet1のA列にデータがあり、B列に結果を表示するとします。
Sheet2を作業用のSheetとしていますので、Sheet2は何もデータがない！という状態にしておいてください。

画面左下のSheet1のSheet見出し上で右クリック　→　コードの表示　→　VBE画面がでますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）
当方のやり方が悪いのかもしれませんけど、
簡単にできるかな！って思っていましたが、結構ダラダラと長くなってしまいました。

Sub test() 'この行から
Dim i, j, k As Long
Dim str As String
Dim ws As Worksheet
Set ws = Worksheets("sheet2")
Columns(1).Copy Destination:=ws.Cells(1, 2)
For i = 1 To ws.Cells(Rows.Count, 2).End(xlUp).Row
Application.ScreenUpdating = False
If ws.Cells(i, 2) Like "[A-z]" Then
str = ws.Cells(i, 2)
End If
If ws.Cells(i, 2) <> "" And Not ws.Cells(i, 2) Like "[A-z]" Then
ws.Cells(i, 1) = str & "_" & ws.Cells(i, 2)
Else
ws.Cells(i, 1) = ""
End If
Next i
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 1) Like "[A-z]" Then
j = j + 1
ws.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1) = Cells(i, 1)
End If
Next i
For j = 3 To ws.Cells(1, Columns.Count).End(xlToLeft).Column
For i = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row
If ws.Cells(i, 1) Like ws.Cells(1, j) & "_" & "*" Then
ws.Cells(Rows.Count, j).End(xlUp).Offset(1) = ws.Cells(i, 2)
End If
Next i
Next j
For i = 2 To ws.Cells(Rows.Count, 3).End(xlUp).Row
For j = 4 To ws.Cells(1, Columns.Count).End(xlToLeft).Column
If WorksheetFunction.CountIf(ws.Columns(j), ws.Cells(i, 3)) Then
k = k + 1
End If
Next j
Cells(1, 2) = "結果"
If k = ws.Cells(1, Columns.Count).End(xlToLeft).Column - 3 Then
Cells(Rows.Count, 2).End(xlUp).Offset(1) = ws.Cells(i, 3)
End If
k = 0
Next i
ws.Cells.Delete
Application.ScreenUpdating = True
End Sub 'この行まで

※　A列の範囲は「A」～「Z」までのアルファベットで区切られているという前提です。

他に簡単な方法があればごめんなさいね。m(_ _)m

お礼

ありがとうございました。

回答No.1

aokii

=IF(COUNTIF(A1:A24,A1)=3,A1,"")

補足

回答ありがとうございます。
私の説明不足でしたが、実際にはＡ列の指定の範囲と範囲の間にも値が入ってるために、
そこの値もカウントされてしまいました。
指定範囲だけで出来ますか？