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と関連する良く見られている質問

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エクセルの参照結果を空欄を詰めて表示させたい

エクセルの入門書などによくある学校の成績表で、縦軸B3からB25までに氏名、横軸C3からCQまで科目名があるとします。
このJの列にある科目で100点を取った生徒のみを別シートのB28から縦に順番に表示させたいです。

基本的な関数を使って、100点を取った人のみ抽出して表示させることはできるのですが、空欄を詰めて順に表示させることができず困っております。
よろしくお願いします。

Aベストアンサー

 もし、A列等に出席番号等の「同じ列の中に、同じ値が複数入力されている事があり得ないデータ」が入力されている場合には、次の様な方法もあります。

 今仮に、

>エクセルの入門書などによくある学校の成績表で、縦軸B3からB25までに氏名、横軸C3からCQまで科目名があるとします。

の成績表が存在しているシートのシート名がSheet1であり、Sheet2に「Sheet1のJの列にある科目」で100点を取った生徒のデータのみを表示させるものとします。

 まず、Sheet2のA27セルに次の関数を入力して下さい。

=Sheet1!$A$2

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

=IF(ROWS($28:28)>COUNTIF(Sheet1!$J:$J,"=100"),"",INDEX(INDEX(Sheet1!$A:$A,MATCH(A27,Sheet1!$A:$A,0)+1):INDEX(Sheet1!$A:$A,ROWS(Sheet1!$A:$A)),MATCH(100,INDEX(Sheet1!$J:$J,MATCH(A27,Sheet1!$A:$A,0)+1):INDEX(Sheet1!$J:$J,ROWS(Sheet1!$A:$A)),0)))

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

=IF($A28="","",VLOOKUP($A28,Sheet1!$A:$B,2,FALSE))

 次に、Sheet2のA28~B28の範囲をまとめてコピーして、Sheet2のA29~B51の範囲に貼り付けて下さい。

 以上です。


 因みに、もし、生徒の中に同姓同名の生徒が存在しない事が保障されている場合には、A列を表示させる事なしに、Sheet2のB28セルに次の様な関数を入力してから、Sheet2のB28セルをコピーして、Sheet2のB29~B51の範囲に貼り付けるだけで済みます。(Sheet2のB27セルに、Sheet1のB2セルと同じ値を入力しておく必要はあります)

=IF(ROWS($28:28)>COUNTIF(Sheet1!$J:$J,"=100"),"",INDEX(INDEX(Sheet1!$B:$B,MATCH(B27,Sheet1!$B:$B,0)+1):INDEX(Sheet1!$B:$B,ROWS(Sheet1!$B:$B)),MATCH(100,INDEX(Sheet1!$J:$J,MATCH(B27,Sheet1!$B:$B,0)+1):INDEX(Sheet1!$J:$J,ROWS(Sheet1!$B:$B)),0)))

 もし、A列等に出席番号等の「同じ列の中に、同じ値が複数入力されている事があり得ないデータ」が入力されている場合には、次の様な方法もあります。

 今仮に、

>エクセルの入門書などによくある学校の成績表で、縦軸B3からB25までに氏名、横軸C3からCQまで科目名があるとします。

の成績表が存在しているシートのシート名がSheet1であり、Sheet2に「Sheet1のJの列にある科目」で100点を取った生徒のデータのみを表示させるものとします。

 まず、Sheet2のA27セルに次の関数を入力して下さい。

=Sheet1!$...続きを読む

QEXCELで空白行を詰めるには

WindowsXP Excel2000 です。

下の図の「表1」を「表2」の様にしたいのですが
Excelの基本操作だけで出来るでしょうか?

やりたい事は以下の通りです。
(1)空白行は削除する
(2)ダブっている行を削除する


(表1)
+====+=======+=======+====
| | A | B |・・
+====+=======+=======+====
| 1 | 1011 | xxxxx |
+----+-------+-------+----
| 2 | | |
+----+-------+-------+----
| 3 | | |
+----+-------+-------+----
| 4 | 1011 | xxxxx |
+----+-------+-------+----
| 5 | | |
+----+-------+-------+----
| 6 | 1012 | yyyyy |
+----+-------+-------+----
| 7 | | |
+----+-------+-------+----
| 8 | 1012 | yyyyy |
+----+-------+-------+----

↓ ↓ ↓

(表2)
+====+=======+=======+====
| | A | B |・・
+====+=======+=======+====
| 1 | 1011 | xxxxx |
+----+-------+-------+----
| 2 | 1012 | yyyyy |
+----+-------+-------+----

[注]表は等幅フォントで見てください。
以上よろしくお願いします。

WindowsXP Excel2000 です。

下の図の「表1」を「表2」の様にしたいのですが
Excelの基本操作だけで出来るでしょうか?

やりたい事は以下の通りです。
(1)空白行は削除する
(2)ダブっている行を削除する


(表1)
+====+=======+=======+====
| | A | B |・・
+====+=======+=======+====
| 1 | 1011 | xxxxx |
+----+-------+-------+----
| 2 | | |
+----+-------+-------+----
| 3 | | |
+----+-------+-------+----
| 4 | 1011 | x...続きを読む

Aベストアンサー

方法1
1行目にタイトルを追加
A B
別の空いている列で
A
>0
と入力
「データ」「フィルタ」「フィルタオプションの設定」で
リスト範囲をA,B列
検索条件範囲を先ほどタイトルと条件を入れた2セルにして
「重複するレコードは無視する」にチェックしてOK
A B
1011 xxxxx
1012 yyyyy
の表示になるのでコピーして別のシートに貼り付ける

方法2
1行目にタイトルを追加
A B C
C2=COUNTIF($A$2:$A2,$A2)
として最終行までコピー、A,B,C列を選択して
「データ」「フィルタ」「オートフィルタ」
C1の▼を押して1を選択
A B C
1011 xxxxx 1
1012 yyyyy 1
の表示になるのでA,B列をコピーして別のシートに貼り付ける

方法3
1行目にタイトルを追加
A B
「データ」「ピボットテーブル」で行にA B、データにAを設定して完了
データの個数 : A
AB計
1011 xxxxx 2
1011 計2
1012 yyyyy 2
1012 計2
(空白)(空白)
(空白) 計
総計4
になるので計の行を右クリックで「表示しない」にする。
A,Bの列をコピーして別シートに値として貼り付け

方法1
1行目にタイトルを追加
A B
別の空いている列で
A
>0
と入力
「データ」「フィルタ」「フィルタオプションの設定」で
リスト範囲をA,B列
検索条件範囲を先ほどタイトルと条件を入れた2セルにして
「重複するレコードは無視する」にチェックしてOK
A B
1011 xxxxx
1012 yyyyy
の表示になるのでコピーして別のシートに貼り付ける

方法2
1行目にタイトルを追加
A B C
C2=COUNTIF($A$2:$A2,$A2)
として最終行までコピー、A,B,C列を選択して
「データ」「フィルタ」「オートフィルタ」
...続きを読む

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

QExcel 複数のデータを別シートに上から詰めて表示させたい

Excelで、色々なシートに複数のデータがあります。
それらを一定条件で抽出して(そこは問題ないのですが)
ある1枚のシートに「上から詰めて」1行ずつ表示させたいのですが、
その表示されるべき「複数のデータ」は、時に空欄になっている事も
あるものです。
という事は、「上から詰めて」という条件をかなえるためには、
その表示させたいシートにセルを固定して参照させる訳には
行きませんよね・・・。
かと言って&を使ってみても、関数ではセル内改行させられない・・・
ですよね?

そんな事で悩んでおります。
お知恵を貸してくださる方いらしたらお願いします。

Aベストアンサー

こんにちは。
>それらを一定条件で抽出して(そこは問題ないのですが)
>時に空欄になっている事も
↑これって、抽出方法に問題があるのでは???
現状はどうされているのですか?
>&を使ってみても、関数ではセル内改行させられない・・・
セル内改行は「CHAR(10)」です。
例えば、「="○○○"&CHAR(10)&"△△△"」なら
○○○
△△△
と、1つのセルに表示されます。

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

Q空白セルのある行を削除する関数をご教示下さい。

空白セルのある行を削除する関数をご教示下さい。
ただし、条件付で、特定の列の空白セルに該当する行しか削除しない
という条件付の関数を作成したいと考えています。

文章だけでは分かりにくいため、図を添付します。
エクセルに詳しい皆様の回答を心よりお待ちしています。

Aベストアンサー

こんばんは!
一例です。
↓の画像の配置で説明します。

F2セルに
=IF(COUNTA($B$2:$B$1000)<ROW(A1),"",INDEX(A$2:A$1000,SMALL(IF($B$2:$B$1000<>"",ROW($A$1:$A$999)),ROW(A1))))

これは配列数式になってしまいますので、この画面からF2セルにコピー&ペーストしただけではエラーになると思います。
貼り付け後、F2キーを押す、または貼り付けセルをダブルクリック、または数式バー内で一度クリックします。
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定します。
数式の前後に{ }マークが入り配列数式になります。

これを列方向と行方向にオートフィルでコピーした後に、日付の列は表示形式を「日付」にします。
尚、元データが空白の場合は「0」が表示されますので、
当方使用のExcel2003の場合ですが
メニュー → ツール → オプション → 「表示」タブで 「ゼロ値」のチェックを外せば
0は表示されなくなります。

数式は元データの1000行目まで対応できるようにしていますが
データ量によって範囲指定の領域はアレンジしてみてください。

以上、長々と書きましたが
参考になれば幸いです。m(__)m

こんばんは!
一例です。
↓の画像の配置で説明します。

F2セルに
=IF(COUNTA($B$2:$B$1000)<ROW(A1),"",INDEX(A$2:A$1000,SMALL(IF($B$2:$B$1000<>"",ROW($A$1:$A$999)),ROW(A1))))

これは配列数式になってしまいますので、この画面からF2セルにコピー&ペーストしただけではエラーになると思います。
貼り付け後、F2キーを押す、または貼り付けセルをダブルクリック、または数式バー内で一度クリックします。
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定します。
数式の前後に{ }マ...続きを読む

Qエクセルで条件に一致したセルの隣のセルを取得したい

下のような「得点」という名前のシートがあります。
(「田中」のセルがA1です。)

 [ 田中 ][ 10 ][ 200 ]
 [ 山田 ][ 21 ][ 150 ]
 [ 佐藤 ][ 76 ][ 250 ]
 [ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

 [ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

 [ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは!
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー!
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

Q空白セルを飛ばして転記したい

こんばんは。
またもやexcelで悩んでおります。どうぞご教授ください。
下のようなデータがあります。

    A   B   C   D    E   F   G
1 山岡     山田  丸山  山岡  山田  丸山
2      平尾     南田  平尾  南田         




A列からD列まで名前のデータがあり、空白のセルもあります。
E列からG列には、各行のA列からD列の名前を、空白セルを飛ばして表記したいのです。(詰めて表記したいです)
名前は各行とも0から3つまでです。

前回同様(QNo.4073079)行き詰ってしまいました。
よろしくお願いいたします。

Aベストアンサー

E1=IF(COLUMN(A1)>COUNTA($A1:$D1),"",INDEX($A1:$D1,SMALL(INDEX(SUBSTITUTE(($A1:$D1<>"")*1,0,10^5)*COLUMN($A1:$D1),),COLUMN(A1))))
★右と下にコピー


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

人気Q&Aランキング