[[20100427164821]]　『範囲内で指定文字の場合、その項目名をあらわす』（アトム）　ページの最後に飛ぶ

『範囲内で指定文字の場合、その項目名をあらわす』（アトム）

以下の表で

 　　　 ① ② ③ ④ ⑤
 氏名Ａ　4　1　3　2　1　①
 氏名Ｂ　2　3　1　2　1
 氏名Ｃ　2　4　1　1　2

氏名Ａの4の数字の項目名①を設定したセルに現す。

さらに、①の場合、⑪の項目のセルの内容を指定したセルに現す。
方法を教えて下さい。

      A   B  C  D  E  F  G  H  I  J  K  L  M  N
 1 　　　 ① ② ③ ④ ⑤ ⑥ ⑦ ⑧ ⑨ ⑩ ⑪
 2 氏名Ａ　4　1　3　2　1 … … … … … あ ① あ
 3 氏名Ｂ　2　3　1　2　1 … … … … … い
 4 氏名Ｃ　2　4　1　1　2 … … … … … う ②

 こんな感じでいいのでしょうか？

 M2セルに =OFFSET($B$1,0,MATCH(4,B2:F2,0)-1)

 N2セルに =IF(M2="①",L2,"")

 としてM列･N列ともにオートフィルでいかがでしょう？

 ただし
 (1)検索値4がない場合のエラー処理はしてないです。
 (2)検索値4が複数回あった場合、最初に出てきたほうを返します。
    （表の左側に出てくるほう）

 （ぞうちゃん）

ありがとうございました。
Ｍ２セルはうまくいきましたが、
Ｎ２セルのほうは①とは限らないわけで、②③④⑤のケースもあり
IF(M2="①",L2,"")でいいんでしょうか？

また、ただし書きしていただいた（１）についてもエラー処理を
ご教授ください。
（２）についても3回まで必要としますので、それについても
お願いします。

 取り急ぎ…

 (1)　M2セルに=IF(ISERROR(OFFSET($B$1,0,MATCH(4,B2:F2,0)-1)),"",OFFSET($B$1,0,MATCH(4,B2:F2,0)-1)) として下にオートフィル

 検索セル（①～⑤のうち調べたい番号を入力）をN1セルとして
 N2セル =IF(M2=$N$1,L2,"") として下にオートフィル

 ついでにいうと検索セル(N1セル)は入力規則でリストを使うといいと思います。

 3ついるのは少し待ってくださ～い

 （ぞうちゃん）

 おはようございます。
 きのうは休みだったので返事が遅れてすみません。

 M2セルに =IF(B2=4,$B$1,"")&IF(C2=4,IF(B2=4,","&$C$1,$C$1),"")&IF(D2=4,IF(COUNTIF(B2:C2,4)>0,","&$D$1,$D$1),"")
 &IF(E2=4,IF(COUNTIF(B2:D2,4)>0,","&$E$1,$E$1),"")&IF(F2=4,IF(COUNTIF(B2:E2,4)>0,","&$F$1,$F$1),"") として下にオートフィル
 （式が長いので2行にしましたが1行にしてください。）

 N2セルに =IF(COUNTIF(M2,"*"&$N$1&"*")>0,L2,"") として下にオートフィル

 …でいかがでしょう？

 今はこれぐらいしかパッと思いつかなかったんですが、もしかしたらもっとスマートな式があるかも…
 これだと項目が増えたときにどんどん式が長くなるんで。
 他の方でもう少し式を短くする方法が分かる方がいらっしゃいましたら今後のために私にも教えていただきたく思います。

 （ぞうちゃん）

ありがとうございます。すばらしい！！さあ、具体的に作業をと入力したら以下のような長い式になり「式が長すぎます」ということで作動しませんでした。
あと一息、簡単な式への変換をご検討ください。エクセルの面白さに取り付かれはじめています。項目は27に及びます。
よろしくお願いします。

IF(L2=4,$L$1,"")&IF(M2=4,IF(L2=4,","&$M$1,$M$1),"")&IF(N2=4,IF(COUNTIF(L2:M2,4)>0,","&$N$1,$N$1),"")&IF(O2=4,IF(COUNTIF(L2:N2,4)>0,","&$O$1,$O$1),"")&IF(P2=4,IF(COUNTIF(L2:O2,4)>0,","&$P$1,$P$1),"")&IF(Q2=4,IF(COUNTIF(L2:P2,4)>0,","&$Q$1,$Q$1),"")&IF(R2=4,IF(COUNTIF(L2:Q2,4)>0,","&$R$1,$R$1),"")&IF(S2=4,IF(COUNTIF(L2:R2,4)>0,","&$S$1,$S$1),"")&IF(T2=4,IF(COUNTIF(L2:S2,4)>0,","&$T$1,$T$1),"")&IF(U2=4,IF(COUNTIF(L2:T2,4)>0,","&$U$1,$U$1),"")&IF(V2=4,IF(COUNTIF(L2:U2,4)>0,","&$V$1,$V$1),"")&IF(W2=4,IF(COUNTIF(L2:V2,4)>0,","&$W$1,$W$1),"")&IF(X2=4,IF(COUNTIF(L2:W2,4)>0,","&$X$1,$X$1),"")&IF(Y2=4,IF(COUNTIF(L2:X2,4)>0,","&$Y$1,$Y$1),"")&IF(Z2=4,IF(COUNTIF(L2:Y2,4)>0,","&$Z$1,$Z$1),"")&IF(AA2=4,IF(COUNTIF(L2:Z2,4)>0,","&$AA$1,$AA$1),"")&IF(AB2=4,IF(COUNTIF(L2:AA2,4)>0,","&$AB$1,$AB$1),"")&IF(AC2=4,IF(COUNTIF(L2:AB2,4)>0,","&$AC$1,$AC$1),"")&IF(AD2=4,IF(COUNTIF(L2:AC2,4)>0,","&$AD$1,$AD$1),"")&IF(AE2=4,IF(COUNTIF(L2:AD2,4)>0,","&$AE$1,$AE$1),"")&IF(AF2=4,IF(COUNTIF(L2:AE2,4)>0,","&$AF$1,$AF$1),"")&IF(AG2=4,IF(COUNTIF(L2:AF2,4)>0,","&$AG$1,$AG$1),"")&IF(AH2=4,IF(COUNTIF(L2:AG2,4)>0,","&$AH$1,$AH$1),"")&IF(AI2=4,IF(COUNTIF(L2:AH2,4)>0,","&$AI$1,$AI$1),"")&IF(AJ2=4,IF(COUNTIF(L2:AI2,4)>0,","&$AJ$1,$AJ$1),"")&IF(AK2=4,IF(COUNTIF(L2:AJ2,4)>0,","&$AK$1,$AK$1),"")&IF(AL2=4,IF(COUNTIF(L2:AK2,4)>0,","&$AL$1,$AL$1),"")

 アトムさんの式はＭ列の記載がなかったんですけど式を数えたら26項目しかなかったのでL列からAL列までに項目があると勝手に解釈して…

 作業列を使わせてください。（AM列からBM列まで27列使います。）

 AM列に =IF(L2=4,L$1,"")

 AL列に =IF(M2=4,IF(COUNTIF($L2:M2,4)>1,","&M$1,M$1),"")

 そして項目を表示したいセルをBN2せるとして

 BN2セルに =AM2&AN2&AO2&AP2&AQ2&AR2&AS2&AT2&AU2&AV2&AW2&AX2&AY2&AZ2&BA2&BB2&BC2&BD2&BE2&BF2&BG2&BH2&BI2&BJ2&BK2&BL2&BM2

 としてそれぞれオートフィル

 もちろん作業列は非表示にしてくださいね。

 …前回とあんまりかわってないですね、ごめんなさい。

 （ぞうちゃん）

 {=IF(COUNTIF($A$2:$AL$2,4)=0,"",INDEX($A$1:$AL$1,MIN(IF($L$2:$AL$2=4,COLUMN($L$2:$AL$2),""))))&IF(COUNTIF($A$2:$AL$2,4)=1,"",","&INDEX($A$1:$AL$1,SMALL(IF($L$2:$AL$2=4,COLUMN($L$2:$AL$2),""),2)))&IF(COUNTIF($A$2:$AL$2,4)=2,"",","&INDEX($A$1:$AL$1,SMALL(IF($L$2:$AL$2=4,COLUMN($L$2:$AL$2),""),3)))}
 配列数式ですので 
 [Shift]+[Ctrl]+[Enter]　　３つのキーを同時に押して確定

上記、配列数式とやらでトライしてみましたが計算式が正しくないと作動しません。
３つのキーの同時押し確定もしたのですが・・・・
改めて、Ｌ列からＡＬ列のセルが４の場合の項目名を３つ（コンマで区切って）I列に連続表記したいのです。（アトム）

 UDFはいかがですか？
 標準モジュールに

  Function Atom(検索範囲 As Range, 検索値 As Variant, 文字列範囲 As Range) As String
  Dim i As Long
  For i = 1 To 検索範囲.Cells.Count
    If 検索範囲.Cells(i).Value = 検索値 Then
      Atom = Atom & "," & 文字列範囲.Cells(i).Value
    End If
  Next i
  Atom = Mid(Atom, 2)
  End Function

 を貼り付けて、セルに =Atom(L2:AL2,4,$L$1:$AL$1) のように関数を入れます。

 （ｍｏｍｏ）

 訂正
 {=IF(COUNTIF($A$2:$AL$2,4)<1,"",INDEX($A$1:$AL$1,MIN(IF($L$2:$AL$2=4,COLUMN($L$2:$AL$2),""))))&IF(COUNTIF($A$2:$AL$2,4)<2,"",","&INDEX($A$1:$AL$1,SMALL(IF($L$2:$AL$2=4,COLUMN($L$2:$AL$2),""),2)))&IF(COUNTIF($A$2:$AL$2,4)<3,"",","&INDEX($A$1:$AL$1,SMALL(IF($L$2:$AL$2=4,COLUMN($L$2:$AL$2),""),3)))}
  [Shift]+[Ctrl]+[Enter]　　３つのキーを同時に押して確定

配列数式やUDFとなると、もう何がなんだかわかりません。
上記、配列数式を貼り付けてみましたが、やはりうまくいきません
ちなみにエラーメッセージは入力式が正しくありません！となりますが、
かなり基本的なミスをしてる可能性もあると思います。

上記、配列数式を「Iー２」の＝の後に貼り付けて、オートフィル使用と思うのですが
上記メッセージが出ます。

 式はAL列の外、たとえばAM2に貼り付けるものと考えていました。元の式ではI2セルは式の計算範囲に入り、循環参照のエラーになります。　　修正
 I2:{=IF(COUNTIF($L2:$AL2,4)<1,"",INDEX($A$1:$AL$1,MIN(IF($L2:$AL2=4,COLUMN($L2:$AL2),""))))&IF(COUNTIF($L2:$AL2,4)<2,"",","&INDEX($A1:$AL1,SMALL(IF($L2:$AL2=4,COLUMN($L2:$AL2),""),2)))&IF(COUNTIF($L2:$AL2,4)<3,"",","&INDEX($A1:$AL1,SMALL(IF($L2:$AL2=4,COLUMN($L2:$AL2),""),3)))}
[Shift]+[Ctrl]+[Enter]　　３つのキーを同時に押して確定

コメント返信：

[ 一覧(最新更新順) ]

[[20100427164821]] 『範囲内で指定文字の場合、その項目名をあらわす』（アトム） ページの最後に飛ぶ

[[20100427164821]]　『範囲内で指定文字の場合、その項目名をあらわす』（アトム）　ページの最後に飛ぶ