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

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

A表

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

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

B表

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

このQ&Aに関連する最新のQ&A

A 回答 (5件)

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


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

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

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

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

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

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

一例です。


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))),"")
    • good
    • 0
この回答へのお礼

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

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

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


例えば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)))
    • good
    • 1
この回答へのお礼

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

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

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


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

ご参考まで。
    • good
    • 0
この回答へのお礼

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

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

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Q【Excel】選択したすべてのセルを絶対参照にする方法

ExcelでF4を押せばセルを絶対参照に変えられますが、複数のセルを同時に絶対参照に変える方法を教えてください。

Aベストアンサー

こんばんは!
一例ですが・・・
Ctrlキーを押しながら、範囲指定(飛び飛びでも構いません)した後に
F4キーを押してみてください。
これでOKかと思います。m(__)m

QEXCEL データをコピーして別シートの最初の空白行に貼り付け

EXCEL データをコピーして別シートの最初の空白行に貼り付け

解決方法がわからず再度質問させていただきます。
どなたかご教示お願いいたします。

シート
1-1 毎月決まった項目のデータが入ります。
1-4 1-1の月データを一年分貼付
2-1 毎月決まった項目のデータが入ります。
2-3 2-1の月データを一年分貼付
3-1 毎月決まった項目のデータが入ります。
3-2 3-1の月データを一年分貼付

1,2,3と三種類ありますが、やりたいことは同じで、
1-1、2-1、3-1には毎月決まったデータを貼り付けています。
そのデータをそれぞれ1-4、2-3、3-2の前月の最終行の次に継ぎ足しで貼り付けたいのです。
(項目の下の空白行に貼り付けたい)

1-1と1-4、2-1と2-3、3-1と3-2の項目は同じです。

-----------------------------------------------------------------------------------
1-1
A1:O1に項目(A2:02以下にデータが入り、行はその月によりまちまちです)
Q2:R28にはA2:O2以下のデータをカウントしています。

1-4
A1:O1に項目
A2:O2以下に一年分のデータを自動的に貼り付け
-----------------------------------------------------------------------------------
2-1
A1:L1に項目(A2:L2以下にデータが入り、行はその月によりまちまちです)
N2:O15にはA2:L2以下のデータをカウントしています。

2-3
A1:L1に項目
A2:L2以下に一年分のデータを自動的に貼り付け
-----------------------------------------------------------------------------------
3-1
A1:K1に項目(A2:K2以下にデータが入り、行はその月によりまちまちです)

3-2
A1:K1に項目
A2:K2以下に一年分のデータを自動的に貼り付け
-----------------------------------------------------------------------------------

EXCEL2003です。
マクロは記録程度の知識しかなく、申し訳ないのですが、
ここの部分をどうしても自動的に行いたいのです。
どうかよろしくお願いいたします。

EXCEL データをコピーして別シートの最初の空白行に貼り付け

解決方法がわからず再度質問させていただきます。
どなたかご教示お願いいたします。

シート
1-1 毎月決まった項目のデータが入ります。
1-4 1-1の月データを一年分貼付
2-1 毎月決まった項目のデータが入ります。
2-3 2-1の月データを一年分貼付
3-1 毎月決まった項目のデータが入ります。
3-2 3-1の月データを一年分貼付

1,2,3と三種類ありますが、やりたいことは同じで、
1-1、2-1、3-1には毎月決まったデータを貼り付けています。
そのデ...続きを読む

Aベストアンサー

それぞれの毎月分のデータを月末か月初に、年間シートに移植してるんですね。

元データのバックアップを取った上で、お試しファイルのほうでご確認ください。

********************************************************************************
Sub Harituke()

Dim k As Integer
Dim Rng As Range
Dim Ws(6) As Worksheet
Set Ws(1) = Worksheets("1-1")
Set Ws(2) = Worksheets("1-4")
Set Ws(3) = Worksheets("2-1")
Set Ws(4) = Worksheets("2-3")
Set Ws(5) = Worksheets("3-1")
Set Ws(6) = Worksheets("3-2")


For k = 1 To 5 Step 2
  Set Rng = Ws(k).Cells(1, 1).CurrentRegion
  Set Rng = Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1, Rng.Columns.Count)
  Rng.Copy Ws(k + 1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Next k

For k = 1 To 6
  Set Ws(k) = Nothing
Next k
Set Rng = Nothing

End Sub

それぞれの毎月分のデータを月末か月初に、年間シートに移植してるんですね。

元データのバックアップを取った上で、お試しファイルのほうでご確認ください。

********************************************************************************
Sub Harituke()

Dim k As Integer
Dim Rng As Range
Dim Ws(6) As Worksheet
Set Ws(1) = Worksheets("1-1")
Set Ws(2) = Worksheets("1-4")
Set Ws(3) = Worksheets("2-1")
Set Ws(4) = Worksheets("2-3")
Set Ws(5) = Worksheets("3-1")
Set Ws(6) = Worksheets(...続きを読む

Q別シートのセルを絶対参照にする

office2007、Excelで別シートのセルを絶対参照する方法を教えて下さい。
セルに「=シート名セル番地」を力した場合は、
セル番地を$で絶対参照にしても
参照先シートのその上に行を挿入すると
$A$5を指定していても行を挿入した場合は
自動的に$A$6になってしまいます。
別シートに行を挿入しても参照先のセルを固定する方法を教えて下さい。

Aベストアンサー

INDIRECT関数なら、文字列として範囲を指定できるので、行挿入や列挿入で勝手に指定範囲が変わることはありません。

例:
 INDIRECT(参照文字列,参照形式)
 と、使います。
 =INDIRECT("Sheet1!A1",TRUE)


※ INDIRECT関数の詳細は Excelのヘルプや解説サイトなどで確認してください。

Qエクセルの空白を詰めて別シートに表示

sheet1のデータを参照して、空白を詰めてsheet2へ表示させたいんです!

sheet1(元データになるもの)
 A列 |B列|C列・・・
1行目A|100|100
2行目B|  |200
3行目C|100|
4行目D|  |200
5行目E|100|
6行目F|  |100



sheet2(sheet1でB列に入力があるものを抽出)
 A列 |B列
1行目A|100
2行目C|100
3行目E|100

sheet3(sheet1でC列に入力があるものを抽出)
 A列 |B列
1行目A|100
2行目B|200
3行目D|200
4行目F|100

sheet2のA1に下記の式を入力してA列とB列に数式をコピー
=IF(COUNT(Sheet1!$B$1:$B$6)<ROW(A1),"",INDEX(Sheet1!A$1:A$6,SMALL(IF(Sheet1!$B$1:$B$6<>"",ROW($A$1:$A$6)),ROW(A1))))

すると下記のように表示されます。
A1=A     B1=100
A2=#NUM! B2=#NUM!
A3=#NUM! B3=#NUM!
4行目から空白

sheet2のA2のところにエラーが出ていますが、「関数の引数」のところで「数式の結果」には「100」と
正解が表示されています。(B2、A3、B3も同様に)数式の結果のところには正解が表示されています。

答えの「#NUM!」のところに正解を表示させるには、どうしたらいいですか?

見よう見まねでつくったのもで。。。関数にあまり詳しくありません。
よろしくお願い致します。

sheet1のデータを参照して、空白を詰めてsheet2へ表示させたいんです!

sheet1(元データになるもの)
 A列 |B列|C列・・・
1行目A|100|100
2行目B|  |200
3行目C|100|
4行目D|  |200
5行目E|100|
6行目F|  |100



sheet2(sheet1でB列に入力があるものを抽出)
 A列 |B列
1行目A|100
2行目C|100
3行目E|100

sheet3(sheet1でC列に入力があるものを抽出)
 A列 |B列
1行目A|100
2行目B|200
3行目D|200
4行目F|100

s...続きを読む

Aベストアンサー

No.1です!
たびたびごめんなさい。

投稿した後で質問をもう一度読み返して見ました。

数式は合っていると思いますよ。

ただ、配列数式になっていないのでエラーになっているだけみたいですね。

そのままの数式で
Shift+Ctrl+Enterキーで確定してみてください。
ちゃんと表示されるはずです。

ということは・・・
前回の私の回答は余計なお世話になってしまいました。

どうも何度も失礼しました。m(__)m

Qexcel 割引率のセルを絶対参照した場合の式は?

下記で、D2の15%のセルを絶対参照にした場合、式はどのようにすればいいでしょうか。
例)
絶対参照にするセル D2 15%(割引率) 
          B4~B7 元値
          C4~C7 結果 
自分では・・・ 
C4=B4-B4*$D$2 で入力してみたのですが、他に式はありますか。

絶対参照にしない場合は、C4=B4*(1-0.15)でやってみました。
いずれも自信はありません。
よろしくお願いします。

Aベストアンサー

一例です。
小数点以下四捨五入、=ROUND(B4*(1-$D$2),0)
小数点以下切り上げ、=ROUNDUP(B4*(1-$D$2),0)
小数点以下切り捨て、=ROUNDDOWN(B4*(1-$D$2),0)

Qエクセルの入力規則のプルダウンのサイズ変更

エクセルの「入力規則」のプルダウンのサイズ変更。
入力規則のプルダウンの表示は、」通常8行の表示となりますが、日付を全て表示させる方法はありませんか?

Aベストアンサー

私にも以前同じような要望がありましたが、そのときはすぐあきらめました。
今回あらためて調べてみましたが、通常メニューには無いようです。

ひとつ情報が見つかりましたので、参照URLに載せておきます。

参考URL:http://dtsman.blog12.fc2.com/blog-entry-103.html

Qエクセルで複数のセルの絶対参照を一度に外す方法

宜しくお願いします。

複数のセルに例えば 

”$A3$*D3”

という式が入っていて、一度に

”A3*D3”

にする方法というのはあるのでしょうか?

私はいちいちセルをアクティブにして、F4キーで絶対参照を
外すというのを各セルごとに行っているのですが、
一度に置換か何かで”$A3$”を”A3”にすることは出来ないでしょうか

教えてください。
宜しくお願いします。

Aベストアンサー

解っているんでは、??。
編集⇒置換⇒検索する文字 $
置換する文字 (空白にして) カーソルをここに入れる(あわせるとも言う?) ⇒全て置換

これで、終わりです。

QEXCEL関数 空白セルを詰める関数

図1の表を図2のようにオートフィルタを使わず表示させる関数で下記を見つけましたが、実際使うときには、番地がA1からの位置ではなくAA60ぐらいの位置でこの関数を実行すると表示されません。
どの部分をその番地にあわせるとよいでしょうか?

<図1>
    A     B
1   あいう
2         えお
3   かきく
4         けこ
5   さしす

<図2>各行の空白セルを詰めて表示させる
    A     B    
10  あいう   えお
11  かきく   けこ
12  さしす
13
14

【関数】

A10: =IF(ROW(A1)>COUNTA(A$1:A$5),"",INDEX(A$1:A$5,SMALL(IF(A$1:A$5<>"",ROW($A$1:$A$5),""),ROW(A1))))

Ctrl + Shift + Enterキーで式を確定します。

右と下にコピーします。

Aベストアンサー

元データがA60から始まっているとすると,

A10に
=IF(ROW(A1)>COUNTA(A$60:A$65),"",INDEX(A$60:A$65,SMALL(IF(A$60:A$65<>"",ROW($A$1:$A$6),""),ROW(A1))))
をコントロールキーとシフトキーを押しながらEnter,下にコピー,右にコピー と直す格好になりますね。




#好みの問題ですが
=IF(ROW(A1)>COUNTA(A$60:A$65),"",INDEX(A:A,SMALL(IF(A$60:A$65<>"",ROW(A$60:A$65),""),ROW(A1))))
をCtrl+Shift+Enterの方が,判りやすいと思います。

Q多数のセルに入力してある数式を$を使わずに絶対参照コピーする方法

 初めて質問いたします。
 技術系計算をエクセルで組み立てる際、多数のセルに入力されている相当長い数式等を$を使用することなくコピーする方法を教えてください。
 以前他のサイトで、単にコピーしたいセルを選びCtrl+C Ctrl+V 程度の操作で一括絶対参照コピーができる(コピーした先のセル内の数式にも$がつていなかったように記憶しています)を覚えたのですがその操作方法を忘れてしまいました。 
 多数のセルを対象にしていますのでF4 キーによる変換では相当な時間がかかるので投稿しました。
 宜しくお願いいたします。

Aベストアンサー

こんばんは。

#3 の回答者ですが、#2さんのテクニックが上手く行かないということで、あえてそう書いたまでなのです。

>以前他のサイトで、単にコピーしたいセルを選びCtrl+C Ctrl+V 程度の操作で一括絶対参照コピーができる(コピーした先のセル内の数式にも$がつていなかったように記憶しています)を覚えたのですがその操作方法を忘れてしまいました。 

私は、何年も掲示板を読んでいますから、ほとんどは見ているつもりではいるものの、この種のご質問は難しいですね。私の知らないものもあるのかもしれませんが、現実に困っていない状況から、はっきりしていない記憶の中から、他人の独特のテクニックを導きだすからです。

以下の方法も定番とはいえません。

一旦、文字化させておいて、貼り付け、それを「区切り位置」ボタンで、一気に数式化させるというテクニックかもしれません。

具体的には、数式を置換で、「=」 --> 「'=」 と全体を全て置換します。そして、その範囲をコピーして、目的の場所に貼り付け、「区切り位置」で、「完了」をクリックします。
コピー元も同じく、「区切り位置」で元に戻します。ただし、これは一列に限ります。

他にも同種の特殊なテクニックもありますし、概ね、マクロ化も可能ですが、こういうことは、めったに必要ありません。実は、左側の数式は、右の数式と等価ですから、数式はひとつだけでよいわけです。

 A1方式  R1C1方式
--------------------------
=B1+C1   =RC[1]+RC[2]
=B2+C2   =RC[1]+RC[2]
=B3+C3   =RC[1]+RC[2]

こんばんは。

#3 の回答者ですが、#2さんのテクニックが上手く行かないということで、あえてそう書いたまでなのです。

>以前他のサイトで、単にコピーしたいセルを選びCtrl+C Ctrl+V 程度の操作で一括絶対参照コピーができる(コピーした先のセル内の数式にも$がつていなかったように記憶しています)を覚えたのですがその操作方法を忘れてしまいました。 

私は、何年も掲示板を読んでいますから、ほとんどは見ているつもりではいるものの、この種のご質問は難しいですね。私の知らないものもあるのか...続きを読む

Qエクセル 空白セルに文字が入力できない 入力規則の設定?

エクセル 空白セルに文字が入力できない 入力規則の設定?
◇使用機器 Win7 エクセルOffice2007
◇入手した資料の作成ソフト=エクセル0Ffice2003
◇エクセルOfice2003で作成した資料を入手して、Office2007で開いています。
セルをクリックすると下向き三角矢印が出る列があり、その三角ボタンをクリックすると、下のほうにズラー文字を入力したセルが表示されます。この行の最後に「東北」と「関東」2行を追記したい。
セルをクリックして入力しようとすると罰点マークが出て、且つ入力が規制されているので入力が出来ない趣旨の表示が出ます。
このような状態で新しいセルに「東北」と「関東」の二行を追記する操作方法を教えて下さい
◇コメント
リボンにある入力規則欄にも類似した表現があり「入力規則」なる設定が行われており「入力規則」のボタンから操作の必要があるように思えますが、私には初体験で操作方法が分かりません。資料作成者にコンタクトできないため、賢者の方々の御知恵を拝借致したくお願い申し上げます。

Aベストアンサー

入力しようとされているセルには、リストを使った「入力規則」が設定されているようです。


下記URLのページをご参照して、直接入力されたリスト、または、どこかのセルに入力されたリストに、ご希望の項目を追加して下さい。
Excel2007をお使いなので、
「データ」タブの「データ ツール」グループの「データの入力規則」をクリックするとURLのページと同じダイアログボックスが表示されます。


「ワークシート上でリスト入力」
http://officetanaka.net/excel/function/tips/tips05.htm

「エクセル ドロップダウンリスト」
http://www.kenzo30.com/ex_kisopoint/onepoint_sonota3.htm


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング

おすすめ情報