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

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

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

A表

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

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

B表

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

No.2 ベストアンサー 回答者： keithin 回答日時： 2011/04/27 15:35

A列にそういうのがあるとすると

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

この回答へのお礼

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

お礼日時：2011/04/28 09:37

No.5 回答者： kagakusuki 回答日時： 2011/04/27 23:16

　ドロップダウンリストに空欄無しで表示出来れば、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の範囲に貼り付けて下さい。
　そして、作業列と関数を使う方法と同様に、入力規則を設定して下さい。

この回答へのお礼

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

お礼日時：2011/04/28 09:41

No.4 回答者： mu2011 回答日時： 2011/04/27 16:55

一例です。

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))),"")

この回答へのお礼

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

お礼日時：2011/04/28 09:16

No.3 回答者： KURUMITO 回答日時： 2011/04/27 16:42

計算に負担を掛けないで簡単で分かり易い方法を提唱しています。

例えば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)))

この回答へのお礼

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

お礼日時：2011/04/28 09:17

No.1 回答者： qualheart 回答日時： 2011/04/27 14:13

オートフィルタで「空白以外のセル」のみを表示するようにするのはいけませんか？

見かけ上は空白セルの行が表示されなくなるので、希望されたとおりの表示になります。

ご参考まで。

この回答へのお礼

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

お礼日時：2011/04/28 09:20