Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2016)
データの空白セルを無視して上から順に詰めて表示させたい
投稿日時: 20/01/27 21:14:48
投稿者: うなぎいぬ

お世話になります。
下のような名簿から空白セルを無視して、上から順に詰めて表示させたいと思ています。
 
【元シート】
 
    A    B    C    D
1    名前1    名前2    名前3    名前4
2    一郎    二郎        
3    三郎            
4    四郎    五郎    六郎    七郎
5    八郎            
 
   ↓
 
【抽出シート】
 
    A
1    名前
2    一郎
3    二郎
4    三郎
5    四郎
6    五郎
7    六郎
8    七郎
9    八郎
 
横方向に見ていって次の行に飛び、また横方向に…という方法が全く分かりません。
お手数おかけしますが、よろしくお願いします。
 

回答
投稿日時: 20/01/27 22:33:15
投稿者: WinArrow
投稿者のウェブサイトに移動

作業列を使っています。
 
【元シート】
E列
E2:=A2&IF(B2<>"",","&B2,"")&IF(C2<>"",","&C2,"")&IF(D2<>"",","&D2,"")&","&E3
E3:=A3&IF(B3<>"",","&B3,"")&IF(C3<>"",","&C3,"")&IF(D3<>"",","&D3,"")&","&E4
E4:=A3&IF(B3<>"",","&B3,"")&IF(C3<>"",","&C3,"")&IF(D3<>"",","&D3,"")&","&E4
E5:=A5&IF(B5<>"",","&B5,"")&IF(C5<>"",","&C5,"")&IF(D5<>"",","&D5,"")
 
F列
F2:1
F3:=G2+1
F9までフィルドラッグ
 
G列
G2:=FIND(",",$E$2&",",F2)
G9までフィルドラッグ
 
H列
H2:=MID($E$2,F2,G2-F2)
H9までフィルドラッグ
 
【抽出シート】
元シートのH列を値複写すれば出来上がります。

投稿日時: 20/01/27 23:12:27
投稿者: うなぎいぬ

早速お答えいただきありがとうございます。
教えていただいた通りに計算式をコピーしたところ、
E4セルへ入れる計算式が上の行と同じものだったので、そこだけ直しましたところ、
上手くいきました。
ありがとうございます。
 
ところが、再度問題が発生いたしまして、
行に何もデータがない場合があることが判明しました。
 
【元シート】
 
    A    B    C    D
1    名前1    名前2    名前3    名前4
2    一郎    二郎        
3    三郎            
4    四郎    五郎    六郎    七郎
5    八郎            
6                
7    九郎            
8    十郎            
 
上記のように6行目には何もデータがない場合、教えていただいたものですと、
「八郎」と「九郎」の間に空白セルが入ってしまいました。
そのような状態にならないようにするにはどのようにすればよろしいのでしょうか。
こちら初心者で頼り切りで申し訳ありません。
また、最初の質問の際に空白行があることをお知らせしなかったことも申し訳ありません。
再度お手数をおかけしますが、教えていたえだけると助かります。
よろしくお願いします。
 
[/u]

回答
投稿日時: 20/01/28 08:50:59
投稿者: んなっと

     A    B   C   D E F  G   H
 1 名前1 名前2 名前3 名前4    201 一郎
 2  一郎  二郎           202 二郎
 3  三郎               301 三郎
 4  四郎  五郎  六郎  七郎    401 四郎
 5  八郎               402 五郎
 6                   403 六郎
 7  九郎               404 七郎
 8  十郎               501 八郎
 9                   701 九郎
10                   801 十郎
 
G1
=IFERROR(AGGREGATE(15,6,ROW($2:$500)*100+COLUMN(A:E)/(A$2:E$500<>""),ROW(G1)),"")
下方向・↓
H1
=IF(G1="","",INDEX(A:E,INT(G1/100),MOD(G1,100)))
下方向・↓
 
 
作業列を使わない方法も一応ありますが、INDIRECTを使うのが欠点。
 
     A    B   C   D E F   G
 1 名前1 名前2 名前3 名前4    一郎
 2  一郎  二郎           二郎
 3  三郎               三郎
 4  四郎  五郎  六郎  七郎    四郎
 5  八郎               五郎
 6                   六郎
 7  九郎               七郎
 8  十郎               八郎
 9                   九郎
10                   十郎
 
G1
=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,ROW($2:$500)*100+COLUMN(A:E)/(A$2:E$500<>""),ROW(G1)),"!R0C00"),0),"")
下方向・↓

回答
投稿日時: 20/01/28 09:54:27
投稿者: Mike

  A         A
1 名前      1 名前
2 一郎      2 一郎
3 二郎      3 二郎
4   0 ⇒⇒⇒⇒ 4 三郎
5   0 ⇒⇒⇒⇒ 5 四郎
6 三郎      6 五郎
7   0      7 六郎
8   0      8 七郎
9   0      9 八郎
10 四郎
11 五郎
12 六郎
13 七郎
14 八郎
15  0
16  0
17  0
18
 
元シート、抽出シートをそれぞれ Sheet1、Sheet2 とします。
Sheet2 において、
1.次式を入力したセル A1 を下方に(データも数値 0 も表示
 ̄ ̄されなくなるまで)ズズーッとオートフィル
 ̄ ̄=IF(ROW(A1)>(COUNTA(Sheet1!A:A)-1)*COLUMNS(Sheet1!A$1:D$1),"",OFFSET(Sheet1!A$2,(ROW(A1)-1)/COLUMNS(A$1:D$1),MOD(ROW(A1)-1,COLUMNS(A$1:D$1))))
2.列A を選択 ⇒ Ctrl+C ⇒ [値の貼り付け] ⇒ 列A の選択
 ̄ ̄状態のままで Ctrl+G ⇒ [セル選択] ⇒“定数”に目玉入
 ̄ ̄れ、かつ、“数値”以外に付いているチェック外し ⇒[OK]
 ̄ ̄⇒ 選択状態の任意のセル上でマウスの右クリック ⇒ [削
 ̄ ̄除] ⇒“上方向にシフト”に目玉入れ ⇒ [OK]

回答
投稿日時: 20/01/28 10:57:08
投稿者: んなっと

Excel2016ならPowerQueryを使う方法も。
 
 表の範囲を選択
→[データ]
→[テーブルまたは範囲から]
→「先頭行をテーブルの見出しとして使用する」にチェックを入れてOK
→[詳細エディター]

 
let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"名前1", type text}, {"名前2", type text}, {"名前3", type text}, {"名前4", type text}})
in
    変更された型
 
のうちの最初の2行だけそのままにして、それより下の文字列を変更して以下のようにする
 
let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    Unp = Table.UnpivotOtherColumns(ソース, {}, "Tmp", "名前"),
    Del = Table.RemoveColumns(Unp,{"Tmp"})
in
    Del
 
→[完了]
→[ホーム]タブの[閉じて読み込む] 

回答
投稿日時: 20/01/28 11:00:51
投稿者: んなっと

元データに変更があったら
 
 データ
→すべて更新
(これが面倒)

投稿日時: 20/01/28 22:54:54
投稿者: うなぎいぬ

複数ご回答いただきまして、ありがとうございます。
教えていただいた方法を試してみまして、
2回目に質問したすぐ後にご回答をいただきました、
んなっとさんの最初の方法を使うことにいたしました。
他の方法も知らないことだったので、とても勉強になりました。
いつか参考にして使えるときが来るかと思っております。
本当にありがとうございました。