EXCEL 空白セル削除 VBA使用せず

質問

EXCELで、以下のような表を作成しています。
　　A　　　B　　　C　　　D　　　E
１　あ　　10
２　い　　20
３　う　　20
４　え　　10
５　お　　10

もし、B列の値が10なら、C列にA列の内容を次のように表示します。
　　A　　　B　　C　　D　　E
１　あ　　10　　あ
２　い　　20　　え
３　う　　20　　お
４　え　　10
５　お　　10

上の例の場合、C列の2行目・3行目にはA列を反映させないということを
VBA無しで行いたいのですが、関数だけで可能でしょうか？
式はいくら複雑になっても構いません。

ヒントになるようなことでも、自分でトライしてみますので、
お教えください。
宜しくお願いします。

回答No.8

maron--5 · ベストアンサー

◆こんな方法もありますよ
C1=IF(ROW(A1)>COUNTIF(B:B,10),"",INDEX(A:A,SMALL(INDEX(SUBSTITUTE(N($B$1:$B$5=10),0,10^5)*ROW($B$1:$B$5),),ROW(A1))))
★下にコピー

お礼

うまくやれそうです。
ありがとうございました。

回答No.9

Wendy02

こんばんは。Wendy02です。

回答とは関係のないことを書いてすみません。（削除になってもよいです）
これは深刻な問題なのです。

>とある業務用ソフトと連携させて作成したEXCELファイルを
>開く仕様になっています。

勉強になりました。この件は、ずっと覚えておくことになると思います。

>業務用ソフトとEXCELマクロの相性が良くなく

それは、ある程度、問題は想像つきます。

おそらくは、それは誤解によるものだと思っています。

厳密には、アドインもマクロですし、内容的には、VBAもあれば、旧マクロもあれば、新しいコンパイル型まで、いろんな種類を使用しています。今のところ、ExcelのCOMアドインは見かけませんが、すぐに登場してくると思います。たとえば、ピボットテーブルのようなものも、組み込み型のマクロです。それらを否定したら、Excelは、ものすごく使用しづらくなってしまいます。というか、「ありえません」

しかし、VBAはというものは、一般ユーザー側が、さまざまな問題に対処するというのは、難しいです。もともと、それが業務ではありませんからね。だからこそ、本来は、マクロは、データとは別の分離型で書くほうがよいのかもしれません。

回答No.7

Wendy02

#6 のWendy02です。

>VBA使用禁止を聞いたとき、頭が真っ白になってしまいました。

この VBA使用禁止令の理由って、いろいろなきっかけがあるようですね。
そちらの理由はなんですか？参考のために、よかったら、教えてください。理論武装ほどではないのですが、こちらもVBAが中心なので、いろんな対応策は用意しなければならないわけです。

だいたいは、こういうのは、派遣社員に対して、厳しい制限がつくことが多いのですが。私なんかが気にするのは、社員の労働時間の変化につながってくると思っているのです。労働効率が大幅に違ってくるのですね。ただ、社員にとっては、労働の質よりも、時間さえ長ければよいということもありますが、アメリカ人は、そういうことを、一番に日本人に対して言いますね。

最後に、もっとも簡単なのは、自分の手動でデータを入れることかもしれません。

お礼

あまり詳しくは書けませんが、
とある業務用ソフトと連携させて作成したEXCELファイルを
開く仕様になっています。
その、とある業務用ソフトとEXCELマクロの相性が良くなく、
マクロを含んでいると正常に動作しなくなるらしいということで
マクロ使用禁止ということになりました。
また、相手に渡ったときのメンテナンス性も考慮されて
マクロは使用しないようにということでした。

回答No.6

Wendy02

こんにちは。

私は、VBAを中心に回答しているものですが、もしかしたら、VBAでしかできないか、と思ったのではないでしょうか？

=IF(COUNTIF($B$1:$B$5,10)>=ROW(A1),INDEX($A$1:$A$5,SMALL(INDEX((B$1:$B$5=10)*ROW($A$1:$A$5),,),COUNTIF($B$1:$B$5,">10")+ROW(A1)),1),"")

こんな式でできるはずですね。

ただ、数式のロジックからすると、VBAよりは、遥かに難しいですね。

VBAでは、単純に、自分の考えているとおりに、

B列から、10の数が出会ったら、A列の同じ数を拾って、それを、C列の1行目から一つずつ、コピーしていく

って考えるから、ごく人間的な発想ですが、

関数の場合は、まず、B列全体ら、10であるものだけの行数を取り出して、それを、小さい順に並び替え、その時に、10以外のものは、0が発生するので、B列の10以外の数を数えて、その次のところから、選び出した数字（行番号）を、INDEX関数に、A列に対して、当てはめます。

当然、10だけしか出しませんから、If関数で、それ以外のものは、"" として、長さ0文字列にする、とするわけです。

どちらが簡単でしょうか？ただ、VBAというか、プログラミング言語って、一つ一つの積み重ねでできるものだから、関数のように、積み重ねの勉強をしなくても、どこからでも、関数さえあれば、答えが導きだせるというわけではないから、大変なのですね。

お礼

VBAはずっと仕事で使用していたため、
質問させていただいたものの作成以来が来たときは、
ちょろいなと思っていたのですが、
VBA使用禁止を聞いたとき、頭が真っ白になってしまいました。
正直、VBAのほうが簡単だと思っているので、
関数を避けて通ってきた感じはあります。
これを機会に、関数も勉強させていただきます。
ご意見、ありがとうございました。

回答No.5

daidai024

作業用セルを使ってみました。
その代わり、行と列がずれるのですが、ご了承ください。

　　Ａ　　Ｂ　　Ｃ　　Ｄ　　　Ｅ
１
２　※　　あ　　10　　△　　　▽
３　※　　い　　20　　△　　　▽
４　※　　う　　20　　△　　　▽
５　※　　え　　10　　△　　　▽
６　※　　お　　10　　△　　　▽

1行目を挿入します。タイトル、項目名などを入れてください。
※のセルは、Ａ２のセルに　=IF(C2=10,MAX($A$1:A1)+1,"")
　以下、下へコピー
△のセルは、Ｄ２のセルに　=VLOOKUP(ROW()-1,$A$2:$C$6,2,FALSE)
　以下、下へコピー
▽のセルは、Ｅ２のセルに　=IF(ISERROR(D2),"",D2)
　以下、下へコピー
最後に作業用セルのＡ列、Ｄ列を表示しないようにすれば完成です。

お礼

ありがとうございます。
VLOOKUPを使い、はじめは検討していたので、
こういうやり方もあったかと、
感心してしまいました。

ありがとうございます。
いろんな縛りの中で、これを完成させなければならないので、
参考にさせていただき、役立てたいと思います。

回答No.4

imogasi

先ほども類似質問に答えてまたか、ですが、自称imogasi方式というのを説明します。
作業列を（どの空き列でもよい）使います。
例データ
A２:B６
あ　　10
い　　20
う　　20
え　　10
お　　10
D2に
=IF(B2=10,MAX($D$1:D1)+1,"")
と入れて下方向にD６まで式複写。
結果
あ 10 1
い 20
う 20
え 10 2
お 10 3
Sheet2に行って、A2に
=OFFSET(Sheet1!$A$2,MATCH(ROW()-1,Sheet1!$D$2:$D$6,0)-1,0)
と入れて、Sheet1のD列の最大数の行数分式を複写します。
結果
あ
え
お
ーー
＞VBA無しで行いたいのですが
この質問はVBA向きです。そのことを覚えておいてください。
ーー
配列数式などを使った、作業列を使わない方法も回答に出ますが、
難しいものになります。

お礼

ありがとうございます。
VBAを使用しないという縛りの上に、
別シートも使用しないというしばりもあるんです。
作業列を作ってその列を幅０にするというのは
やってもいいみたいなので、
参考にさせていただいて、
自分なりにやってみたいと思います。

回答No.3

ham_kamo

C1に以下の式を入力し、配列数式なのでCtrl+Shift+Enterで確定してください。あとは下にコピーです。行数が100を超えるときは、$100の部分はそれ以上の数に書き換えてください。

=IF(ROW(A1)>COUNTIF($B$1:$B$100,10),"",INDEX($A$1:$A$100,SMALL(IF($B$1:$B$100=10,ROW($B$1:$B$100),10^10),ROW(A1))))

お礼

ご返答ありがとうございます。
さっそく試してみます。
じつは、似たような式を自分で作って試したのですが、
うまくいかずに質問をさせていただきました。
そのときは
=IF(ROW()・・・
でやっていたのですが、そこに違いがあるのでしょうか？

回答No.2

ryuujiok2205

少なくとも私はとまどってしまいます。このサンプル、たしかに1行目はC列にA列の値を反映しているようですが、4行目5行目はなぜブランクなのですか？　これから反映させたいということですか？

2行目3行目にはなぜ「え」「お」が入っているのですか？　元々C列には値が入っていて、B列の値によりC列の値を編集したいということなんですか？　だとすれば1さんの式じゃまずいし（消しちゃいますよ）。よもやB列が20なら「い」→「え」、「う」→「お」なんてことを要求してるんじゃないんでしょうね。

せっかくサンプル書いてくれてるんですからこんな疑問がないようにしてください。

お礼

ご返答、ありがとうございます。
No.1の方へのお礼にも書かせていただきましたが、
質問の仕方が良くなかったと思います。
B列の値が10のときだけ、A列の値を抽出して
C列に書き込むということをしたいのです。
質問をわかりやすくできなくて、
申し訳ありませんでした。

回答No.1

noname#58440

=if(B1=10,A1,"")
これで出来ると思うのですが、他に条件があるのですか?

お礼

ご返答、ありがとうございます。
質問の仕方が良くなかったと思います。
B列の値が10のときだけ、A列の値を抽出して
C列に書き込むということをしたいのです。
また、ツールバーにあるコマンドを使用せず、
VBAも使用しないという取り決めの中で
行わなくてはならないので、
関数でどうにかできないかと思いました。