オートフィルタで選択した項目を別セルにて反映 [EXCEL]

質問

出来るかわかりませんが。

例えばB列が「東北、関東、東海、近畿……」などの「地域名」だとして、「東北」でオートフィルタをかけた時にA1に「東北のリストです」などと表示するような関数はないでしょうか。フィルタの条件に反応して複数の種類にわたって表示が変わるようにしたいのですが。

今まではIF（SUBPRODUCT=COUNTIF）でやってましたが、10種類ほどの項目が出来て正確性を失ったばかりか機能しなくなってしまったので、良い方法があればご教授願います。

回答No.2

maruru01 · ベストアンサー

こんにちは。maruru01です。

>A行に数値をいれず
ということなら、こんな感じで。
A1に、

=IF(OR(SUBTOTAL(3,B2:B30)={0,29}),"",INDEX(B:B,MAX(INDEX(SUBTOTAL(3,INDIRECT("B"&ROW(B2:B30)))*ROW(B2:B30),)))&"のリストです")

と入力します。
B列は、1行目がタイトルで2～30行にデータです。
データ範囲は適宜変更して下さい。

補足

どうもありがとうございます。

なんとも我侭ですが、もう一つだけ教えてください。
この式も生きてはいるのですが、全体を表示しているときに一番最後の文字列（この場合はB30）が出てきてしまいます。これを何も無い状態（""）には出来ないものでしょうか。

回答No.4

Wendy02

関数をお探しなら、ユーザー定義関数もあります。
設定の仕方は説明しませんが、こんなものもあるよって程度です。
少し、おまけもついています。
ただし、オートフィルターの領域には、この関数は含めないでください。
反応しにくくなります。

'標準モジュール
Function CriteriaShow(Optional index As Integer = 1) As String
　'CriteriaShow(列の何番目)
　Dim Ans1 As String
　Dim Ans2 As String
　Dim fugo As String
　Application.Volatile
　With ActiveSheet
　　If Not .AutoFilter Is Nothing Then
　　　On Error Resume Next
　　　Ans1 = Mid(.AutoFilter.Filters(index).Criteria1, 2)
　　　Ans2 = Mid(.AutoFilter.Filters(index).Criteria2, 2)
　　　If Len(Ans1) = 0 Then
　　　　CriteriaShow = "条件がありません."
　　　　Exit Function
　　　ElseIf Len(Ans1) > 0 And Len(Ans2) = 0 Then
　　　　CriteriaShow = Ans1 & "のリストです."
　　　　Exit Function
　　　　Else
　　　　Select Case Left(.AutoFilter.Filters(index).Criteria2, 1)
　　　　　Case "="
　　　　　　fugo = "を含む"
　　　　　Case "<>"
　　　　　　fugo = "を含まない"
　　　　End Select
　　　　CriteriaShow = Ans1 & "と" & Ans2 & fugo & "のリストです."
　　　　Exit Function
　　　End If
　　　Else
　　　CriteriaShow = "オートフィルターがありません."
　　End If
　End With
End Function

使用例:
= CriteriaShow(1)

お礼

これはいいですね。

もう少し勉強して自分で理解してから使ってみます。

回答No.3

SAKURAMYLOVE

＃１です。
補足の回答ですが、これはSUBTOTAL関数が、フィルターで表示されている数値だけを計算（この場合は、最小値を算出）してその番号の右側を検索しているので、数値がないと根底からやり方が変わってきます。
次善の策として、２案ありますが、いずれかで対処可能でしょうか。
(1)A列の数値の色を白にして見えなくする。
(2)または、A列の右側に列を挿入し、そこに数値を入れて列ごと非表示にする。
その場合の式は、
=IF(SUBTOTAL(5,A2:A100)=0,"",VLOOKUP(SUBTOTAL(5,A2:A100),A2:C100,3,0)&"のリストです")
となります。

補足

どうもありがとうございます。

この表を使う人間にド素人の者がいるため、#2さんの関数でB30にダミー（非表示）を追加することにしました。

回答No.1

SAKURAMYLOVE

以下の方法ではいかがでしょうか。
【手順】
(1)データは100行まであり、B1に項目名があるものとします。
(2)２行目を空白行（地域名は入れない）とし、A2からA100まで、0,1,2・・・と連番をふっておきます。
(3)A1に以下の式を入力します。
=IF(SUBTOTAL(5,A2:A100)=0,"",VLOOKUP(SUBTOTAL(5,A2:A100),A2:B100,2,0)&"のリストです")

これで、フィルタをかけると、A1に地域名が出てくると思います。

補足

ありがとうございます。確かに機能しました。

ただ、A行に数値をいれず……、つまりB行だけを対象として関数を組めないものでしょうか。

B行に「1.関東」ですとかそう言うアレンジをしても良いのですが。