エクセル 空白以外のセルを関数などで抽出したい

質問

以下のように、１列上に、入力されてるセルと空白セルが存在します。

　　Ａ
１｜あああ
２｜
３｜
４｜いうえ

これを、以下のように抽出したいのですが、方法を教えて下さい。
オートフィルタなどではなく、集計終了後、以下のように抽出された値を
そのまま別シートにコピーしたいと考えています。

　　　　Ａ
１００｜あああ
１０１｜いうえ

●ちなみに、２行目や３行目に、集計終了までに値が入る可能性があります。
よろしくお願いします。

回答No.3

zap35 · ベストアンサー

＃０１です。ご期待に沿えたようで嬉しいです。
補足していただいた条件なら以下の式になります。Shift＋Ctrl＋Enterで確定してください。

=IF(ROW()-167>COUNTIF($AG$12:$AG$159,"<>"),"",INDEX($AG$1:$AG$159,SMALL(IF($AG$12:$AG$159="","",ROW($AG$12:$AG$159)),ROW()-167)))

お礼

本当にほんとうにありがとうございます。
理解不足＆知識不足で、INDEXまで範囲を変えてしまっていたのですね。
今まで「コピペ」作業で時間を費やしていた事がたくさんあったので、これで、作業効率がはかれ、上司にもほめてもらえそうです！
これからも、何かありましたら、よろしくお願い致します。

回答No.4

ham_kamo

Ｃ列からAI列まで、12行目～159行目で値が入っているセルだけを160行目以降に抽出する、ということでしょうか。
マクロを使ってよければこんなので。

Sub 抽出()
　　Dim Column As Integer, FromRow As Integer, ToRow As Integer
　　For Column = 3 To 35 'C列からAI列まで
　　　　Range(Cells(168, Column), Cells(65536, Column)).ClearContents
　　　　ToRow = 168
　　　　For FromRow = 12 To 159
　　　　　　If Cells(FromRow, Column).Value <> "" Then
　　　　　　　　Cells(ToRow, Column).Value = Cells(FromRow, Column).Value
　　　　　　　　ToRow = ToRow + 1
　　　　　　End If
　　　　Next
　　Next
End Sub

お礼

ありがとうございました。
関数とは違ってボタン１つで出来ることがとても使いやすかったです。
でも、ちょこちょこ変更したり、エクセルをあまりわからない上司が利用する為、教えて頂いたマクロは次回自分で作ったデータで使わせて頂きます。
ありがとうございました。

回答No.2

nanndemoii

オートフィルタを使って「空白以外のセル」で以下のように表示させます。

　　　Ａ
１００｜あああ
１０１｜いうえ

コピーしたい範囲をドラッグしCtrl+Gで「ジャンプ」ダイアログが開きますので
左下の「選択オプション」ボタンを押し、右下の方にある「可視セル」を選択し
コピペではいかがでしょうか？

お礼

ありがとうございました。
ただ、集計した内容が、C列～AI列まであり、オートフィルタをかけなければいけない内容の集計列が10以上ある為、効率よく作業する為、一度に出来る関数をお教え頂ければ。。。と思っての質問でした。

回答No.1

zap35

データがA1:A99にあるとして、以下の式を任意の列の１行目に貼り付けてShift＋Ctrl＋Enterで確定してください（配列数式にする）。そのセルを下方向に必要分だけコピーします。

=IF(ROW()>COUNTIF($A$1:$A$99,"<>"),"",INDEX($A$1:$A$99,SMALL(IF($A$1:$A$99="","",ROW($A$1:$A$99)),ROW())))

質問例では１００行目に並び替えたリストを表示するようになっています。どうしても１００行目からにする必要があるなら以下の式になります。
=IF(ROW()-99>COUNTIF($A$1:$A$99,"<>"),"",INDEX($A$1:$A$99,SMALL(IF($A$1:$A$99="","",ROW($A$1:$A$99)),ROW()-99)))

補足

ありがとうございました。感激です。根本的に出来た事がとてもうれしいです。例文でやってみたら出来ました。
しかし、一番やりたい内容がどうしても完成しません。

「AG列、12行目～159行目」を範囲として、AG列の168行目以降に答えを表示させたいのです。
お教え頂いた下の式を「-167」とし、「AG12:AG159」と変えても答えが「0」になってしまいます。
※AG168に、式中一部を、「-167」とし、「AG1:AG159」にしたら出来ました。
※答えを出したい内容が記載されてるのが12行目以降なので困ってます。
※範囲を、途中の行を開始とする事は出来ないのでしょうか。。。

理解不足で申し訳ありません。お教え頂けるとうれしいです。