[[20180306125427]]　『空白列を詰めて表示』（エクセルJ.r）　ページの最後に飛ぶ

『空白列を詰めて表示』（エクセルJ.r）

マクロを組まず、関数のみで以下のようなことをしたいです。
どなたかご教授お願いいたします。

A1：C1はご教授頂きたい関数が入力されているとします。
D1：H1セルは文字列が入力できるとします。

例）D1セルに名前1、F1セルに名前2、H1セルに名前3、と入力されているとします。その結果、A1セルに名前1、B1セルに名前2、C1セルに名前3が関数によって自動で表示させたいです（E1セル及びG1セルは空白のため、D1セル・F1セル・H1セルの文字列がA1：C1に表示された＝D1セル：H1セルの内、空白を詰めてA1セル：C1セルに表示された。）。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 3つくらいなら
 A1:=IF(D1="","",D1)
 B1:=IF(F1="","",F1)
 C1:=IF(H1="","",H1)

 で済みますが、実際はもっとデータが多いのですか？
（bi） 2018/03/06(火) 13:17

 D1セルからH1セルのうちどのセルが空白になるかわかっていないということだろう。
 A1セルに
 =IFERROR(INDEX($A1:$H1,SMALL(IF($D1:$H1<>"",COLUMN($D1:$H1),""),COLUMN(A1))),"")
 と入力してShiftキーとCtrlキーを押しながらEnterキーで式を確定後（確定後、式が{}で囲まれればOK）C1セルまでフィルコピーでどうか。
（ねむねむ） 2018/03/06(火) 13:22

 そういうことでしたか。失礼しました。
（bi） 2018/03/06(火) 13:28

 D1:H1の5セルすべてに名前が入力されていたら、
 もしくは1セルのみ空白で、4つの名前が入力されていたら、
 A1:C1はどうなればいいんですか？

 それとも最大3つしか入力されないということ？

 確認だけ
（笑） 2018/03/06(火) 14:35

ねむねむさんの仰せの通りです。
ありがとうございます。

実際には上記ご質問の内、A1セルではなく、T列からが作業列なのですが、その場合COLUMN関数の引数はどうしたらよいでしょうか？
（エクセルJ.r） 2018/03/06(火) 14:58

 最初の$A1:$H1はA列から、元の値が入力されている最終セルまでを指定してくれ。
 $D1:$H1部分は元の値が入力されている列範囲を指定、最後のCOLUMN(A1)はそのままとしてくれ。

（ねむねむ） 2018/03/06(火) 15:19

 2018/03/06(火) 14:35

 ↑ の質問に答えてくれませんか？
（笑） 2018/03/06(火) 15:33

《（笑）さん》、大変失礼しました。
D1：H1セル全てに入力されている場合、D1：F1セルの文字列が左側のA1セル：C1セルに表示され、G1：H1セルの文字列は表示されなくしたいです（A1セル：C1セルは３列のため、必然的にG1：H1セルは無視される）。
仮に、E1セルにのみ文字列が入力されていた場合、E1セルの文字列がA1セルに反映され、B1セル：C1セルは空白のままにしたいです。つまり、D1：H1セルの内、空白を除き文字列が入力されているところのみA1：C1セルに表示（詰めたいです）させたいです。

《ねむねむさん》
お示しの通り試してみましたが、数式の結果、空白が表示されます。

（エクセルJ.r） 2018/03/06(火) 20:51

 どのセル範囲に値が入っていてどのような式を入力したか説明してくれ。
（ねむねむ） 2018/03/06(火) 21:42

《ねむねむさん》
関数の入力箇所は、AA14：AC14です。
文字列の入力箇所は、AD14：AH14です。

AA14セルに、＝IFERROR（INDEX（$A：＄AI,SMALL（IF（＄AD：＄AI〈〉"",COLUMN（＄AD：＄AI）,""）,COLUMN（A1）））,""）
上記を配列数式として入力しています。

AD14：AH14の列の内、空白を除いた文字列のみを、AA14：AC14に詰めて表示させたいです。
（エクセルJ.r） 2018/03/07(水) 11:33

 もしかして
 >元の値が入力されている列範囲を指定
 がまずかったか。
 こちらとしては入力されている行の列範囲というつもりだったが。

 =IFERROR(INDEX($A14:$AH14,SMALL(IF($AD14:$AH14<>"",COLUMN($AD14:$AH14),""),COLUMN(A1))),"")
 と行も指定してくれ。
（ねむねむ） 2018/03/07(水) 11:52

《ねむねむさん》
お示しの通り、理想の結果になりました。
本当にありがとうございました。

何度も恐縮ですが、宜しければ上記の数式の意味もご教授頂けたら幸いです。
（エクセルJ.r） 2018/03/07(水) 15:05

 まず
 IF($AD14:$AH14<>"",COLUMN($AD14:$AH14),"")
 だがAD14せるからAH14セルのうち値が入っている場合その列番号を返し、入っていない場合は""を返す。
 例えばAD・AF・AG列に値が入っていると{30,"",32,33,""}という結果になる。

（ねむねむ） 2018/03/07(水) 15:22

 次にSMALL関数の部分だが
 SMALL({30,"",32,33,""},COLUMN(A1))
 となり{30,"",32,33,""}内の数値でCOLUMN(A1)番目に小さい数値を返す。
 またCOLUMN関数は右へコピーすることで
 COLUMN(A1)→1
 COLUMN(B1)→2
 COLUMN(C1)→3
 … 
 と増えていくためそれぞれ１番小さな数値、2番目に小さな数値…を返す。
（ねむねむ） 2018/03/07(水) 15:32

 最後にINDEX関数だが
 INDEX($A14:$AH14,結果)
 で$A14:$AH14の結果番目を返す。
 ここで結果は値が入っているセルの列番地を小さいほうから順番に返すことから値が入っているセルを左から順番に返すことになる。
（ねむねむ） 2018/03/07(水) 15:40

わざわざお時間を割いて頂き本当にありがとうございます。

完全に理解するまで時間がかかりそうですが、じっくりと考察してみようと思います。

また、今後ともお世話になるかと思いますが、その時は是非ご教授お願いいたします。

この度はありがとうございました。
（エクセルJ.r） 2018/03/07(水) 20:36

コメント返信：

[ 一覧(最新更新順) ]

[[20180306125427]] 『空白列を詰めて表示』（エクセルJ.r） ページの最後に飛ぶ

[[20180306125427]]　『空白列を詰めて表示』（エクセルJ.r）　ページの最後に飛ぶ