エクセル 空白セルを詰めたい

質問

A表のようなデータを別シートに”データの入力規則”でプルダウンを作ると、プルダウンに空欄ができてしまいます。
空白セルにはCOUNTIF等関数が入力されています。
空白でなく記号や文字を入れることは可能です。

空白セルだけ抜いて上に詰めたB表を、関数のみで表示することはできますか？
空白セルがある状態でもプルダウンで詰める方法があればそちらの方がよいです。

A表

__|______
1 | 日本
2 | アメリカ
3 |
4 | イギリス
5 |
6 | フランス
7 | イタリア

↓A表の隣に表示させたい

B表

__|______
1 | 日本
2 | アメリカ
4 | イギリス
5 | フランス
6 | イタリア

回答No.2

keithin · ベストアンサー

A列にそういうのがあるとすると
B1:
=INDEX(A:A,SMALL(IF($A$1:$A$10<>"",ROW($A$1:$A$10),999),ROW(A1)))&""
と記入してコントロールキーとシフトキーを押しながらEnterし,下向けにつるつるっとコピーして入れておきます。

お礼

出来ました。思った以上に短い式で驚きました。
ありがとうございました。

回答No.5

kagakusuki

ドロップダウンリストに空欄無しで表示出来れば、B表がある場所がA表の隣でなくても構わないと考えて宜しいのでしょうか？
　今仮に、A表がSheet1のA1～A7の範囲に存在していて、Sheet2のA1以下にB列を自動的に表示させるものとします。

　以下に、B表の全自動作成を、関数のみで行う方法と、作業列と関数を使って行う方法を、1例ずつ挙げさせて頂きます。

　【作業列と関数を使う方法】
　まず、適当なシート（例えばSheet2）のB1セルに次の数式を入力して下さい。

=IF(Sheet1!$A1="","",ROW(Sheet1!$A1))

　次に、Sheet2のA1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>COUNT($B$1:$B$7),"",INDEX(Sheet1!$A:$A,SMALL($B:$B,ROWS($1:1))))

　次に、Sheet2のA1～B1の範囲をコピーして、Sheet2のA2～B7の範囲に貼り付けて下さい。
　そして、「データの入力規則」ダイアログボックスの[設定]タグ内の「入力値の種類」欄を[リスト]とした上で、「元の値」欄には次の数式を入力して下さい。

=INDIRECT("Sheet2!A1:A"&ROW(INDIRECT("Sheet2!A1"))-1+ROWS(INDIRECT("Sheet2!C1:C7"))-COUNTBLANK(INDIRECT("Sheet2!C1:C7")))

　因みに、「元の値」欄に入力する数式を

=INDIRECT("Sheet2!A1:A")

とした場合には、B表の6～7行目の空欄もドロップダウンリストに表示されてしまいます。

　【関数のみで行う方法】
　まず、Sheet2のA1セルに次の数式を入力して下さい。

=IF(ROWS($A$1:$C1)>ROWS(Sheet1!$A$1:$A$7)-COUNTBLANK(Sheet1!$A$1:$A$7),"",INDEX(Sheet1!$A:$A,SUMPRODUCT(ROW(Sheet1!$A$1:$A$7)*(Sheet1!$A$1:$A$7<>"")*(ROW(Sheet1!$A$1:$A$7)-ROW(Sheet1!$A$1)+1-COUNTBLANK(OFFSET(Sheet1!$A$1,,,ROW(Sheet1!$A$1:$A$7)-ROW(Sheet1!$A$1)+1))=ROWS($A$1:$C1)))))

　次に、Sheet2のA1セルをコピーして、Sheet2のA2～A7の範囲に貼り付けて下さい。
　そして、作業列と関数を使う方法と同様に、入力規則を設定して下さい。

お礼

出来ました。
【作業列と関数を使う方法】いいですね。
回答ありがとうございました。

回答No.4

mu2011

一例です。
C1に以下の配列数式を入力、入力完了時にshift＋ctrl＋enterキーを同時押下、D1へコピー後、C1:D1を選択して下方向に必要分コピー
=IF(COUNTIF($B:$B,"<>")>=ROW(A1),INDEX(A:A,SMALL(IF($B$1:$B$100<>"",ROW($B$1:$B$100),999),ROW(A1))),"")

お礼

回答ありがとうございました。

回答No.3

KURUMITO

計算に負担を掛けないで簡単で分かり易い方法を提唱しています。
例えばA表ではA2セルから下方に番号が、B2セルから下方に国名が入力されているとします。
C2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B2="","",MAX(C$1:C1)+1)

そこでお求めの表ですがE列とF列を使って2行目から下方に表示させるとしたら、E2セルには次の式を入力してF2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>MAX($C:$C),"",INDEX($A:$B,MATCH(ROW(A1),$C:$C,0),COLUMN(A1)))

お礼

回答ありがとうございました。

回答No.1

qualheart

オートフィルタで「空白以外のセル」のみを表示するようにするのはいけませんか？
見かけ上は空白セルの行が表示されなくなるので、希望されたとおりの表示になります。

ご参考まで。

お礼

操作をする方がパソコンに慣れていない方なので、できれば自動でできるようにしたいのです。
ありがとうございました。