[[20100301174937]]　『入力しているデータを自動的に他の該当シートに振』（みみ）　ページの最後に飛ぶ

『入力しているデータを自動的に他の該当シートに振り分けたい』（みみ）

 XP,Excel2003

 このような感じの表があります。

　　　　Ａ　　　　　　Ｂ　　　　Ｃ　　　　　Ｄ　　　　　Ｅ　　　　　Ｆ

 １　　日　付　　　　産　地　　品　名　　　個　数　　　貸出日　　　作業用
 ２　2009年7月1日　　青森　　　りんご　　　　　10　　　　2/5　　　　1
 ３　2009年7月1日　　和歌山　　りんご　　　　　20　　　　　　　　　　
 ４　2009年7月1日　　熊本　　　いちご　　　　　15　　　　2/8　　　　2
 ５　2009年7月2日　　青森　　　りんご　　　　　20　　　　2/10　　　 3
 ６　2009年7月2日　　宮崎　　　マンゴー　　　　5

 貸出日が記載されているものだけ、「貸出あり」シートにないものは「貸出なし」の
 シートに自動的に振り分けたいです。
 一応F列には
 {=IF(COUNTA(E2:E6),COUNT($F$2:F5)+1,"")}
 で自動的に採番されるようにしています。

 採番順に貼り付けが出来るようにしたいのですが・・・。
 数式を教えてください。
 よろしくお願いします。

 配列数式になります。
　
 シート【貸出あり】
	[A]　　　　	[B]	[C]	[D]	[E]	[F]
[1]	日付　　　	産地	品名	個数	貸出日	作業用
[2]	2009年7月1日	青森	りんご	10 	2/5	1 
[3]	2009年7月1日	熊本	いちご	15 	2/8	2 
[4]	2009年7月2日	青森	りんご	20 	2/10	3 
[5]						
[6]						
　
 A2 =IF(COUNT(Sheet1!$E$2:$E$30)<ROW(A1),"",
  INDEX(Sheet1!$A$2:$F$30,SMALL(IF((Sheet1!$E$2:$E$30<>"")*ROW($A$1:$A$29)=0,"",ROW($A$1:$A$29)),ROW(A1)),COLUMN(A1)))
　上記数式を入力または、コピペ後に Ctrl + Shift + Enter で確定
　｛　数式　｝となればOK
 必要範囲までコピペ

 シート【貸出なし】
	[A]　　　　	[B]	[C]	[D]
[1]	日付　　　	産地	品名	個数
[2]	2009年7月1日	和歌山	りんご	20

 A2　=IF(COUNT(Sheet1!$E$2:$E$30)<ROW(A1),"",
  INDEX(Sheet1!$A$2:$F$30,SMALL(IF((Sheet1!$E$2:$E$30="")*ROW($A$1:$A$29)=0,"",ROW($A$1:$A$29)),ROW(A1)),COLUMN(A1)))
　同じく配列数式
 必要範囲までコピー
　
 ※数式内の範囲（ROW関数やCOLUMN関数等）は適当ですから、ご自身のシートに合わせてください。　
 ※配列数式ですので、データ量によっては大変重たくなります。
 　マクロのほうがいいのかしら？？？
　
 （キリキ）（〃⌒o⌒)b

 キリキさん

 ありがとうございます。

	[A]　　　　	[B]	[C]	[D]	[E]	[F]
 [1]	日付　　　	産地	品名	個数	貸出日	作業用
 [2]	2009年7月1日	青森	りんご	10 	2/5	1 
 [3]	2009年7月1日　　  和歌山    りんご 　20　　　　　　　　　　
 [4]     2009年7月1日	熊本	いちご	15 	2/8	2 
 [5]	2009年7月2日	青森	りんご	20 	2/10	3

 エラーがでてうまくいかないです。

 A2　=IF(COUNT(Sheet1!$E$2:$E$30)<ROW(A1),"",
  INDEX(Sheet1!$A$2:$F$30,SMALL(IF((Sheet1!$E$2:$E$30="")*ROW($A$1:$A$29)=0,"",ROW($A$1:$A$29)),ROW(A1)),COLUMN(A1)))
 最初の数式$E$2:$E$30ではなくて、$F$2:$F$30ですか？
 素人ですみません。

 （みみ）

 貸出日有り　の方は、F列が有るので
 もう少し簡単で良いと思います。

 有りの方だけですが。。。		
	[A]	[B]
[1]	作業用F	日付
[2]	1	2009年7月1日
[3]	2	2009年7月1日
[4]	3	2009年7月2日
[5]		
 A2に=IF(MAX(Sheet1!F:F)<ROW(A1),"",ROW(A1))		
 B2に=IF($A2="","",INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!$F:$F,0)))

 無しの方も、作業用のG列を作ることにすれば
 同じように出来るのではないかと思います。

 ちなみに
 ＞一応F列には
 ＞{=IF(COUNTA(E2:E6),COUNT($F$2:F5)+1,"")}
 =IF(E2="","",COUNT($E$2:E2))
 こんな式で良くないですか？

 G列を追加する事を考えると。。。
 =IF($A2="","",IF($E2="","",COUNT(F$1:F1)+1))
 こんな式とか。

 (HANA)

 HANAさんありがとうございます。

 じつは一覧表が大きく変更されてしまいました・・・。
 これはどうやって作ればいいでしょうか？

 元データ
	 [A] 	 [B] 	 [C] 	 [D] 	 [E] 	 [F] 	 [G] 	 [H] ～	 [L] 	 [M] 
 [1]	産地	品名	№	商品名	code	個数	備考	貸出日	受付№	集計用
 [2]	青森	りんご	101	ふじ	1007	10				
 [3]	和歌山	りんご	801	世界一	0011	20		注残		1
 [4]	熊本	いちご	802	あまおう	0011	15		注残		2
 [5]	青森	りんご	801	ふじ	0011	20	開			
 [6]	宮崎	マンゴー	802	太陽	0011	5	開			
 [7]	愛媛	みかん	803	有田みかん	0011	35	⑦	2/26	248785

 [H]～[L]の項目にひとつでも文字や数字が入力されていたら、貸出ありｼｰﾄにそれ以外のものは
 すべて貸出なしｼｰﾄにコピーして欲しいと言われました。
 一応[M]列にH～L列に表示がある場合は、
 =IF(COUNTA(H4:L4),COUNT($M$3:M3)+1,"")
 の数式を作って番号をふるようにしました。

 毎日一覧表の中は増えたり減ったり変更したりします。
 追加で入力してそのたびに並び替えA⇒B⇒Cの順位で並び替えをします。
 最高で600行くらいのデータ量になります。 
 なにかいい数式やマクロはないでしょうか？

 説明ベタですみません。
 よろしくお願いします。

 （みみ）

 ＞なにかいい数式やマクロはないでしょうか？
 これまでの方法では駄目だという事ですか？

 間の列は増えていますが
 基本的には変わってないと思いますが。

 なんだかこちらと似てますね。
 参考に成るかもしれません。
[[20100228001437]]　『指定した範囲の列に文字や数値が入った場合にマー』（ひとみ）

 (HANA)

 基本的に数式を理解してないからだめなのかもしれないです。
 応用が利かないので、良かったら数式を教えてもらえるとうれしいです。
 申し訳ありません。

 （みみ）

 えっと。。。数式は載せているのですが？
 どこまで出来て居るんですか？

 元は「有」の場合の番号が元データ（Sheet1）のF列に振られてましたよね？
 だから、Sheet2のA2セルに
 ＞A2に=IF(MAX(Sheet1!F:F)<ROW(A1),"",ROW(A1))
 として貰えば、1から必要な個数分連番が表示されると思いますが
 これは出来ましたか？

 新しいレイアウトでは、F列からM列に変わったので
 Sheet1!F:F って成っている所を、M列に変えて貰えば良いと思いますが。。。
 分からなければ、まずは元の表のレイアウトでやってみて
 どこがどうなって上手く行かないのか・どこまでは出来たのか教えて下さい。

 (HANA)

 HANAさん

 お手数をおかけします。

 ＞A2に=IF(MAX(Sheet1!F:F)<ROW(A1),"",ROW(A1))

 上記の数式に関しては、FをMに変更したのでうまくいきました。

 ＞A2に=IF(MAX(Sheet1!F:F)<ROW(A1),"",ROW(A1))		
 ＞B2に=IF($A2="","",INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!$F:$F,0)))

 これも数式上はうまくFからMにしてうまく行ったのですが、
 M列の集計用をSheet2のA列にもってきたのもうまくいきましたが、
 Sheet2のG（金額）からM列（受付№）までで、空白だった項目がすべて0と表示されているので、
 その表示を出ないようにしたいのですが・・・。

 	 [A] 	 [B] 	 [C] 	 [D] 	 [E] 	 [F] 	 [G] 	 [H] 	 [I]～	 [M] 
 [1]	集計用	産地	品名	№	商品名	code	個数	備考	貸出日	受付№
 [2]	1	和歌山	りんご	801	世界一	0011	20	0	注残	0
 [3]	2	熊本	いちご	802	あまおう	0011	15	0	注残	0
 [4]	3 	愛媛	みかん	803	有田みかん	0011	35	⑦	2/26	248785

 （みみ）

 そこですか。
 でしたら、INDEX関数の戻り値を確認し、IF関数で場合分けして下さい。

 数式内の
 「INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!$F:$F,0)」部分を
 IF(INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!$F:$F,0)=0,"",INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!$F:$F,0))
 に変更です。

 (HANA)

 HANAさんへ

 ということは・・・。

 =IF($A2="","",IF(INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!$F:$F,0)=0,"",INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!$F:$F,0)))

 という数式でいいのでしょうか？
 すみません。なんだかわけわからなくなってしまって・・・。

 あと貸出なしシートも貸出ありシートのように集計用の列を作った方がいいんですよね？
 どういう数式がいいのでしょうか・・・。

 何度も申し訳ありません。

 （みみ）

 ＞という数式でいいのでしょうか？
 あ・・・済みません違いますよね。

 0かどうかを確認するのではなく「""」かどうかを確認しないと駄目ですよね。
 しかも括弧を一つコピーし忘れてるみたいです。
 INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!$F:$F,0))=""

 なので
=IF($A2="","",IF(INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!$F:$F,0))="","",INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!$F:$F,0))))
 でどうですか？

 (HANA)

 HANAさん

 ありがとうございます！！うまくいきました～(^-^)

 あとさきほども書き込みしましたが、
 貸出なしシートも貸出ありシートのように集計用の列を作った方がいいんですよね？
 どういう数式がいいのでしょうか・・・。
 集計用の列の作成の仕方を教えて欲しいです。

 (みみ）

 一度頭の中を整理してから質問されると良いと思います。

 ＞一応[M]列にH～L列に表示がある場合は、
 ＞=IF(COUNTA(H4:L4),COUNT($M$3:M3)+1,"")
 ＞の数式を作って番号をふるようにしました。

 この式を少し変更すれば良いと思いますが。

 まぁ、M列が"" かどうかを確認するのでも
 良いかもしれませんが。

 ご自身で分かる式を使われるのが一番良いと思いますので。
 ご存じ無ければ、IF関数を調べてみて下さいね。

 (HANA)

 HANAさんへ

 わかりました。
 あとは自分で調べて見ます。
 ありがとうございました。

 （みみ）

コメント返信：

[ 一覧(最新更新順) ]

[[20100301174937]] 『入力しているデータを自動的に他の該当シートに振』（みみ） ページの最後に飛ぶ

[[20100301174937]]　『入力しているデータを自動的に他の該当シートに振』（みみ）　ページの最後に飛ぶ