[[20061003172627]]　『範囲内に数値が入っている列の見出しを返したい』（MAT）　ページの最後に飛ぶ

『範囲内に数値が入っている列の見出しを返したい』（MAT）

	[A]	[B]	[C]	[D]	[E]	[F]
[  1]	0001	0002	0003	0004	0005	
[  2]	  20
[  3]	           30	
[  4]		                    50	
[  5]	                   40
[  6]		                    50　　　60

上のような表があります。
セルF2には0001(A1)、セルF3には0002(B1)、セルF4には0004(D1)という
ように、特定の範囲内に数値（若しくは文字）が入っている場合に、
その見出しを返してくれる関数をＦ列に作りたいのですが、
どうすればよいかご教授いただけ無いでしょうか。

尚、原則として表内の各行には１つのデータしか入らないのですが、
例外的に6行目のように、データが２つ入るような場合もあります。
この場合は、A列に近いD列に対応する0004(D1)のセルを返してほしいです。

以上、よろしくお願いいたします。

 A2:E6は、手入力なのか数式なのか等によって、使える関数が変って来ます。
が、一応どちらの場合でも網羅できていると思います。(自信度70%)
F2セルに下記の式を入れて、CtrlとShiftとEnterを一緒に押して、配列数式として確定して下さい。
※数式バー内で　{下の式}　と　{} で括られていれば配列数式です。 
=IF(COUNTBLANK(A2:E2)=COLUMNS(A2:E2),"",OFFSET(A2,-(ROW(A2)-1),SMALL(IF(LEN(A2:E2)>0,COLUMN(A2:E2)),1)-1))
確定後に、下方コピーしてください。
(sin)

OFFSET関数・配列が勉強になりました。
うまくいきました。
どうもありがとうござました。
（MAT）

 ◆解決済みですが、こんな方法もありますよ
F2=IF(COUNTA(A2:E2),INDEX($A$1:$E$1,MATCH(1,INDEX(1/(A2:E2<>""),),)),"")
(Maron)

 Maron　さんの二番煎じで
 =INDEX($1:$1,MATCH(TRUE,INDEX((A2:E2<>""),),))

 「数値が入っている」　に反応して
 =INDEX($1:$1,MATCH(TRUE,INDEX(ISNUMBER(A2:E2),),))

 ともに、エラー処理はしていません

 By しげちゃん

実際の作業ファイルでやったらうまくいかなかったので、またきました。
SMALL(IF(LEN(A2:E2)>0,COLUMN(A2:E2)),1)-1
まだ、この意味が良く分かっていないようです。

１こずつ質問させていただきます。
配列の文字数をLEN関数で数える場合（上の例で言うと）
{=LEN(B2:F2)}の答えは、2
{=LEN(B3:F3)}の答えは、0
となります。
この違いはなぜなのでしょうか？（MAT）

 >SMALL(IF(LEN(A2:E2)>0,COLUMN(A2:E2)),1)-1
  ======   ~~~~~~~~~~~~ ~~~~~~~~~~~~~ ===__
 ~~~この部分が、配列になっています。
IF関数により　A2:E2の各セル内の文字数が0より大きい場合に、各セルの列番号を返します。
※_1 式全体の頭にある　IF(COUNTBLANK(A2:E2)=COLUMNS(A2:E2),"",　この部分で、
このレスの文頭のIF関数部分が全てFALSEとなる要素は排除されています。この理由は、※_2で・・・
 ===部分は、SMALL関数の要素です。2行目(A2:E2)の場合は、
=SMALL({2,FALSE,FALSE,FALSE,FALSE},1)　この様になります。{ }部分が上記配列の結果。
※_2 この時、上記の※_1部分で処理せず全て空白もしくは""だと、{ }内の全要素がFALSEとなり、
SMALL関数の結果は、#NUM!　となります。
 __部分は、OFFSET関数で列数を指定するための調整用です。

 >（上の例で言うと） {=LEN(B2:F2)}の答えは、2 {=LEN(B3:F3)}の答えは、0 となります。
A2:E2 A3:E3　の書き間違いとして、空いている行（仮に A10:E10)で、次の事を行なってください。
A10:E10セルを選択し、=LEN(A2:E2)　と入力し、配列数式として確定する。
2や0は、LEN(A2）やLEN(A3)の結果という事が、お分かりいただけると思います。
配列数式の結果は、配列範囲と同範囲の仮想セルに置かれていると考えていいと思います。
(sin)

 >2や0は、LEN(A2）やLEN(A3)の結果という事が、お分かりいただけると思います。
これは理解しました。

 >このレスの文頭のIF関数部分が全てFALSEとなる要素は排除されています
LEN(A2:E2)>0この部分が常にTRUEになるということですね？
ただ、LEN(A3:E3)だと0が返ってきて、このIF関数部分がFALSEになりませんか(#1)？
間違っているのは分かっているのですが、クリアになりません。

 また、上の例で、SMALL(IF(LEN(A2:E2)>0,COLUMN(A2:E2)),1)-1の
返り値は1になるはずですが、その考え方は次の通りでよろしいですか？
IF関数内のLEN(A2:E2)>0がTRUEなので、
SMALL({2,FALSE,FALSE,FALSE,FALSE},1)となり、
よってSMALL関数の返り値は2。
従って、SMALL(IF(LEN(A2:E2)>0,COLUMN(A2:E2)),1)-1は2-1で1に
なるのだと思います。
これはあっていますか？

 だとすると、LEN(A3:E3)は0なので、IF関数がFALSEとなり、
やはり#1の疑問がクリアになりません(MAT)。

 ご質問への答えでなくて申し訳ないのですが・・・

 文章の初めに半角スペースを入れると改行出来ますのでご利用下さい。
  ←この部分に半角スペース。
 (上の投稿には勝手に半角スペース入れました。)

 また、文章の最後のニックネームはお忘れ無き様お願いします。

 (HANA)

 横から失礼します。
 >=SMALL({2,FALSE,FALSE,FALSE,FALSE},1)
 は=SMALL({1,FALSE,FALSE,FALSE,FALSE},1)
 で値は1ですね

 同様に　３行目の場合
 =SMALL({FALSE,2,FALSE,FALSE,FALSE},1)
 で値は2ですね

  By　しげちゃん

 しげちゃんさん、どうも。ご指摘の通りです。

 >>このレスの文頭のIF関数部分が全てFALSEとなる要素は排除されています
 >LEN(A2:E2)>0この部分が常にTRUEになるということですね？
違います。LEN(A2:E2)>0　が、全てFALSEにはならない。　という事です。
A10セルに =IF(LEN(A2)>0,COLUMN(A2),FALSE)　と普通にEnterし、E10セルまでコピーします。
結果は、A10=1,B10=FALSE,C10=FALSE,D10=FALSE,E10=FALSE　となります。
F10に、=SMALL(A10:E10,1) とすると、1 になります。
配列式の {=SMALL(IF(LEN(A2:E2)>0,COLUMN(A2:E2)),1)} は、
上の様に作業列としてA10:E10を設けて計算させ、F10セルでSMALL関数を使った結果を
作業列なしで行なっているだけです。

 >ただ、LEN(A3:E3)だと0が返ってきて、このIF関数部分がFALSEになりませんか(#1)？
IF関数は、A3セルにだけ掛かるのではありません。
A3:E3のそれぞれのセルに対して掛かります。
上の説明の作業列（セル） A10,B10,C10,D10.E10 の集合体といったところです。

 =IF(COUNTBLANK(A2:E2)=COLUMNS(A2:E2),"",OFFSET(A2,-(ROW(A2)-1),SMALL(IF(LEN(A2:E2)>0,COLUMN(A2:E2)),1)-1))
                                    配列計算されるのはこの部分だけです→ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ここ以外は、普通の関数計算と同じです。
 今度は、どうでしょうか？

===以下、追記です===

 配列数式の別の例です。 ※２番目のレスの補足のつもりですが、余計だったかも。
     A	B   C   D
 1 	1   2   3
 2   0	0		
 3   0	3
A1セルは、未入力です。B1に1、C1に2、D1に3と入力しています。
 A2セルに　=LEN(A1:D1)　とし普通に確定。
 B2セルに　=LEN(A1:D1)　とし配列数式として確定。
ともに、0です。理由 LEN(A1) の結果表示だから。

 A3セルに　=SUM(LEN(A1:D1))　とし普通に確定。
 B3セルに　=SUM(LEN(A1:D1))　とし配列数式として確定。
普通に確定すると、0です。LEN(A1:D1)部分の　SUM(LEN(A1))のみの計算です。
配列数式だと、3です。LEN(A1:D1)部分が、SUM(LEN(A1),LEN(B1),LEN(C1))として計算されます。
配列数式にすることで、LEN(A1),LEN(B1),LEN(C1)　のそれぞれの結果を内部で持っているため。
(sin)

 IF(LEN(A2:E2)>0,COLUMN(A2:E2)) 5つの数式に分解できます
= IF(LEN(A2)>0,COLUMN(A2),1)	
= IF(LEN(B2)>0,COLUMN(B2),1)	
= IF(LEN(C2)>0,COLUMN(C2),1)	
= IF(LEN(D2)>0,COLUMN(D2),1)
= IF(LEN(E2)>0,COLUMN(E2),1)
でそれぞれの結果
5つの数値をsmall関数で計算です。
（JJ)

 皆さん、回答ありがとうございました。
 配列の意味、使い方を調べてから、改めて説明を読んだらより分かりました。
 まだ使い慣れないので、もう少し練習してみます。
 エクセルを使っていて、今日初めて配列を使いました（感動dT Tb）。
 実際のエクセルファイルのほうでもうまくいきました。
 (OFFSET関数の調整がうまくいっていなかったようでした)
 本当に助かりました（MAT）。

コメント返信：

[ 一覧(最新更新順) ]

[[20061003172627]] 『範囲内に数値が入っている列の見出しを返したい』（MAT） ページの最後に飛ぶ

[[20061003172627]]　『範囲内に数値が入っている列の見出しを返したい』（MAT）　ページの最後に飛ぶ