[[20171018115255]] 『IF関数を別のシートへ転記、空白詰め』(tame) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『IF関数を別のシートへ転記、空白詰め』(tame)

Excel初心者です。空白のセルを詰めたいのですが・・・
次のようにしたいのですが書籍やネット等で調べたのですが、よく解らないのでどなたかご教示お願いします。
OS Win7 Excel2013使用です。
Sheet1で簡単な関数を作り、データを集計しているのですが、関係のない文字や数字が入っている中、特定の田中さんの名前がI列に入っています。
また、日にちがD列に入っています。
以下例です。

SheetA
  A   B   C   D  E   F   G   H   I
3 任意 任意  任意  1  任意  任意 任意  任意 田中
4 任意 任意  任意  1  任意  任意 任意  任意 鈴木
5 任意 任意  任意  2  任意  任意 任意  任意 佐藤
6 任意 任意  任意  3  任意  任意 任意  任意 山田
7 任意 任意  任意  4  任意  任意 任意  任意 田中
8 任意 任意  任意  5  任意  任意 任意  任意 佐藤
9 任意 任意  任意  6  任意  任意 任意  任意 田中
〜〜〜〜〜〜〜〜〜〜〜〜 以下略 〜〜〜〜〜〜〜〜〜〜〜〜〜

このデータをSheet田中のB列に整理して縦1列に名前を表示させる為に、
=IF(SheetA!$I$3:$I$130="田中",SheetA!$D$3:$D$130)
と数式を打ち込み一応表記は出来ています。
が、田中さんの名前が1日、4日、6日にあるので、

SheetA田中
  A   B   C   D   E   F   G   H 
1 任意  1  任意  任意 任意  任意 任意  任意
2 空白 空白  空白  空白 空白  空白 空白  空白
3 空白 空白  空白  空白 空白  空白 空白  空白
4 空白 空白  空白  空白 空白  空白 空白  空白
5 任意  4  任意  任意 任意  任意 任意  任意
6 空白 空白  空白  空白 空白  空白 空白  空白
7 任意  6  任意  任意 任意  任意 任意  任意 
〜〜〜〜〜〜〜〜〜〜〜〜 以下略 〜〜〜〜〜〜〜〜〜〜〜〜〜

と表記されてしまっています。
これを下記の様に自動的に空白のセルを詰めたいのです。

  A   B   C   D   E   F   G   H 
1 任意  1  任意  任意 任意  任意 任意  任意
2 任意  4  任意  任意 任意  任意 任意  任意
3 任意  6  任意  任意 任意  任意 任意  任意
〜〜〜〜〜〜〜〜〜〜〜〜 以下略 〜〜〜〜〜〜〜〜〜〜〜〜〜

Sheetは田中だけではなく鈴木や佐藤など全員分です。
行数は130行まであります。
IF関数の「偽の場合」の値に何かを入力してやるのが一番簡単な方法のような気もしますが、入力内容を調べても解りません。

マクロやVBA等はまだまだ初心者の為、関数でご教示いただけると幸甚です。

私の質問の仕方に不備がありましたらご指摘いただき、お解りになる方ご教示お願いします。

< 使用 Excel:unknown、使用 OS:unknown >


A1アクテブにしてて
ctrl+shift+下矢印 次に

ctrl+shift+左矢印

ctri + g 

セルの選択 オして
空白セル 選択

空白の所だけ指定されたから 削除
(mirumiru) 2017/10/18(水) 13:53


mirumiru様

御返信ありがとうございます!
少しやってみましたが、行が下に間延び?するようでA2以降の行数が下にずれるだけでした…。

やり方が間違っているのかもしれません。
ありがとうございます。
(tame) 2017/10/18(水) 14:04


 田中シートのA1セルに
 =IFERROR(INDEX(SheetA!$A$1:$I$1000,SMALL(IF(SheetA!$I$3:$I$1000="田中",ROW($3:$1000),""),ROW(A1)),COLUMN(A1)),"")
 と入力してShiftキーとCtrlキーを押しながらEnterキーで式を確定(確定後、式が{}で囲まれればOK)
 その後、右および下へフィルコピーしてみてくれ。
(ねむねむ) 2017/10/18(水) 14:11

 なお、上記式ではSheetAのデータは最大で1000行目までとしている。
 もっとある場合は式中の$1000をすべて大きくしてくれ。
 また
 SheetA!$A$1:$I$1000
 の部分は必ず1行目から最大行数までとしてくれ。
(ねむねむ) 2017/10/18(水) 14:13

  =IFERROR(INDEX(SheetA!$A$1:$I$1000,SMALL(IF(SheetA!$I$3:$I$1000=$J$1,ROW($3:$1000),""),ROW(A1)),COLUMN(A1)),"")
 とするとJ1セルに入力した名前の者を抜き出す。
 あるいはCELL関数でシート名を抜き出すようにすると式を入力したシート名の者を抜き出すこともできる。
(ねむねむ) 2017/10/18(水) 14:15

ねむねむ様

御返信ありがとうございます!
こちらもやってみましたが式が{}で囲まれはして、セル内に式も表示はされているのですが、日付が何も表示されず、空白のままです…。

再度やり方が間違っているのかもしれません。
ありがとうございます。
(tame) 2017/10/18(水) 14:41


 元シートと抜出先シートでは列の表記順番が違うのか。
 確認。
 SheetA    Sheet田中
  A列        A列
  B列        C列
  C列        D列
  D列        B列
  E列        E列
  F列        F列
  G列        G列
  H列        H列
  I列       表示しない
 でいいのだろうか?
(ねむねむ) 2017/10/18(水) 14:50

ねむねむ様

大変失礼しました。
便宜上、上記のように簡単に書きましたが

 SheetA    Sheet田中
  A列      表示しない
  B列       OPQ結合列
  C列       表示しない
  D列        B列
  E列       表示しない
  F列       表示しない
  G列       表示しない
  H列       表示しない
  I列       表示しない(シートの振り分け)
 J列    K列

その他SheetAにはX列まであり、その際にSheet田中(Sheet鈴木、Sheet佐藤etc…)に必要なものは
SheetA内のW,X列の2つ、Sheet田中内ではそれぞれC列、G列のように表記されるようにしています。

IF関数の「偽の場合」に何か打ち込めば…と簡単に考えていた為、表記足らずでした。
大変失礼致しました。

何卒ご教示いただけると幸甚です。
(tame) 2017/10/18(水) 15:27


ねむねむ様

追記です。
Sheet田中の始まりの行数は1からではなく、3からになります。

上記の列と組み合わせると、ShhetAの最初の田中があった日付(I列の最初に田中があるD列)をSheet田中のB3のセルに転記していきたいということになります。

重ねて申し訳ございません。

何卒宜しくお願い致します。

(tame) 2017/10/18(水) 15:33


 =IFERROR(INDEX(SheetA!$A$1:$I$1000,SMALL(IF(SheetA!$I$3:$I$1000="田中",ROW($3:$1000),""),ROW(A1)),COLUMN(A1)),"")
 の式のCOLUMN(A1)部分が元の表での列を示している。
 各列で一番上のセルに抜き出したい列に変更した式を入れてそれぞれ下へフィルコピーしてみてくれ。
 例えば元の表のD列から持ってきたい場合はCOLUMN(D1)で。
(ねむねむ) 2017/10/18(水) 15:38

 >Sheet田中のB3のセルに転記していきたい
 式を入力するシートで行が違っていても式の変更は必要ない(というか変更してはいけない)のでそのまま使ってくれ。
(ねむねむ) 2017/10/18(水) 15:40

ねむねむ様

出来ました!すごい!!
ありがとうございます。

=IFERROR(INDEX(SheetA!$A$1:$I$1000,SMALL(IF(SheetA!$I$3:$I$1000="田中",ROW($3:$1000),""),ROW(A1)),COLUMN(A1)),"")を
COLUMN(D3)を変えて完璧でした。

ただ、SheetAのD⇒Sheet田中のBは出来たのですが、その他が出来ないようです…。

SheetA     Sheet田中
B列   ⇒   OPQ結合列
J列   ⇒   K列
W列   ⇒   C列
X列   ⇒   G列

の分です。
列によっては時間だったり、金額だったりするのですが関係はありませんよね?
フィルコピーは出来るのですが1つずつズレるだけなので、数値が表示されません。。。
当然COLUMN(D3)の部分を各列で一番上のセルに抜き出したい列に変更しているのですが。

重ねて申し訳ありません。。。
何卒宜しくお願い致します。

(tame) 2017/10/18(水) 16:08


 例えばB列→OQP結合列の場合、
  =IFERROR(INDEX(SheetA!$A$1:$I$1000,SMALL(IF(SheetA!$I$3:$I$1000="田中",ROW($3:$1000),""),ROW(A1)),COLUMN(B1)),"")
 (COLUMN関数部分は列さえあっていれば行はいくつでも構わないのでCOLUMN(B3)でも構わない)
 でShift+Ctrl+Enterは忘れずに行っているだろうか?
(ねむねむ) 2017/10/18(水) 16:26

ねむねむ様

出来ました!
ありがとうございました!!

本当に感謝致します!!!!
(tame) 2017/10/18(水) 17:10


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.