Excelで、シート1に書いた内容を、条件分岐をした上でシート2以降に挿入させるにはどうしたら良いでしょうか。


具体的には下記のようなイメージです。
◆シート1
client1,5000,Jan
client2,2000,Feb
client3,1000,Apr
client4,1000,Apr

◆シート2(シート1から自動で書き出す)
client1,5000,Jan
client2,2000,Feb

◆シート3(シート1から自動で書き出す)
client3,1000,Apr
client4,1000,Apr

【自分で分かっていること】
・他のシートから位置指定で情報を取ることは知っています
・SUMIFやSUMPRODUCTは知っています
・ただし、配列で取ってくる方法が分かりません

これをExcelの関数だけで解決させたいのですが、どうしたらよいでしょうか。
すみません、どなたか教えてください!

回答の条件
  • 1人1回まで
  • 登録:2007/12/02 20:50:21
  • 終了:2007/12/06 12:53:38

回答(2件)

id:castiron No.1

castiron回答回数418ベストアンサー獲得回数302007/12/02 23:49:48

ポイント35pt

シート分岐

シート分岐をダウンロードしてください。

{=IF($A$2<=B2+1,"",

  IF(IF(ISERROR(MATCH("client1",LEFT(INDIRECT(ADDRESS(1+B2,1,,,"Sheet1")&":"&ADDRESS($A$2,1)),7),0)),$A$2+1

   ,MATCH("client1",LEFT(INDIRECT(ADDRESS(1+B2,1,,,"Sheet1")&":"&ADDRESS($A$2,1)),7),0))

 <IF(ISERROR(MATCH("client2",LEFT(INDIRECT(ADDRESS(1+B2,1,,,"Sheet1")&":"&ADDRESS($A$2,1)),7),0)),$A$2+1</p>

   ,MATCH("client2",LEFT(INDIRECT(ADDRESS(1+B2,1,,,"Sheet1")&":"&ADDRESS($A$2,1)),7),0))

,MATCH("client1",LEFT(INDIRECT(ADDRESS(1+B2,1,,,"Sheet1")&":"&ADDRESS($A$2,1)),7),0)

,MATCH("client2",LEFT(INDIRECT(ADDRESS(1+B2,1,,,"Sheet1")&":"&ADDRESS($A$2,1)),7),0)))}

※一つの式ですw(やろうと思えば隣にあるセルの値を式の中にあるので式に組み込んでしまえば省略できます。もっと長くなります)

配列はCtrl+Shift+Enterを押して関数を決定します。(これをやらないと#VALUEになると思います)

結論として出来ないことはありません。見ただけでかなり強引なことをしているのが分かると思います。

(私のマシンはかなり高性能なのでもしかしたらデータの量とかによってはフリーズするかもしれません。)

けれどもVBを使ってマクロを書けばかなり簡単にできます。

For文を使って、あとは文字列比較をするだけです。

そっちを知りたいなら別途質問してい見ると良いでしょう。(完璧な物を教えてくれるはずです)

ちなみに抽出条件別に列を分けるのならばもう少し簡略化することは可能です。

たぶん、#N/Aとかデータを増やすと成るかもしれませんが、質問者さんががんばってどうにかしてください。もう疲れました・・・

(一番先頭のIF関数がいらなくてその次にあるIF関数の出力の真の場合、偽の場合の出力にそれぞれIF関数を追加してISERROR関数でチェックすればよい)

id:danielocean

私の脳内ではSUMPRODUCTが限界でしたw

「配列で突っ込む」+「条件分岐」ができればOKだと思っているのですが、どうしましょうねw

これだとさすがに長すぎて、他の人に引き継ぐには長いですね・・・。

でも意味はよくわかりました!有難うございます、参考にさせていただきます。

2007/12/04 01:25:32
id:kaiton No.2

kaiton回答回数260ベストアンサー獲得回数342007/12/03 10:17:44

ポイント35pt

いろいろな方法があると思いますが一案と言うことで..

  1. 元データが A1:C4 にあるものとします。
  2. Sheet1に作業列を4列(E:H)作ります。(別シートでもOK)
  3. この例で条件分岐はB列が2000以上・未満で分岐したと課程
E1 =IF(B1>=2000,ROW(),"")
F1 =IF(COUNT($E$1:$E$4)>=ROW(),SMALL($E$1:$E$4,ROW()),"")
G1 =IF(B1<2000,ROW(),"")
H1 =IF(COUNT($G$1:$G$4)>=ROW(),SMALL($G$1:$G$4,ROW()),"")

これらを下にコピー

Sheet2!A1 =IF(Sheet1!$F1<>"",INDEX(Sheet1!A$1:A$4,Sheet1!$F1,1),"")
Sheet3!A1 =IF(Sheet1!$H1<>"",INDEX(Sheet1!A$1:A$4,Sheet1!$H1,1),"")

これらを横3列、縦4行にコピー


条件が違うならE,G列を修正してください。


PS.

条件は、単純に3行目より下か上かではないんですよね?

id:danielocean

回答ありがとうございます。はい、どちらかというと季節を四半期ごとに分けたいというイメージです。

まだよく理解できてません。すみません。後で試してみます。

2007/12/04 01:24:24
  • id:zuguimo
    抽出機能を使った方が簡単だと思いますよ。
    参考までに
    ①シート1 のデータ数は多いのか?
    ②見出しは付いているのか?
    ③検索条件は 2000以上なのか、Aprなのか 
    を教えてください。回答できるかどうか分かりませんが。
  • id:danielocean
    zuguimoさんに回答。
    1:多くて100程度ですね。
    2:見出しはある、という前提にしますか。
    3:Aprですね。
    実は推測されている方もいらっしゃるかもしれませんが、これは
    「年間の見込顧客をシート1に書き、(受注達成目標を)四半期毎に分けてシート2以降に書き出す」
    というのがこの質問の趣旨です。
    なお関数だけという条件を付けたのは、誰でもメンテナンスできるようにするためです。
    VBAを説明するよりは簡単に業務を振ることができますので。
  • id:kaiton
    100件ぐらいあるとなると、SUMPRODUCT関数や配列数式では遅いと思います。
    四半期毎に別シートですか..
    C列の四半期の判断が別にいるのですね、方法は月の一覧と四半期の値をVLOOKUPするか、C列は日付で4/1などで入力し表示は月だけ四半期は=INT(MONTH(C1)/4)+1で書式を標準に、ぐらいでしょうか?
    四半期の判断をD列として、1行目に見出しとすると、
    E2=IF($D2=1,ROW(),"") 1四半期ならその行数
    F2=IF(COUNT(E$2:E$101)>=ROW()-1,SMALL(E$2:E$101,ROW()-1),"") 1四半期の件数を調べ、Small関数で小さな値(行数)順に表示(1四半期に該当する行を上から順に)
    G1=IF($D2=2,ROW(),"")
    H1=IF(COUNT(G$2:G$101)>=ROW()-1,SMALL(G$2:G$101,ROW()-1),"")
    I1=IF($D2=3,ROW(),"")
    J1=IF(COUNT(I$2:I$101)>=ROW()-1,SMALL(I$2:I$101,ROW()-1),"")
    K1=IF($D2=4,ROW(),"")
    L1=IF(COUNT(K$2:K$101)>=ROW()-1,SMALL(K$2:K$101,ROW()-1),"")

    Sheet2!A2=IF(Sheet1!$F2<>"",INDEX(Sheet1!A$1:A$101,Sheet1!$F2,1),"")
    Sheet3以降は上に準じてセル位置を修正してください。

    http://www.geocities.jp/chiquilin_site/data/060322_extraction.html
    でよく似た事例が事例が紹介されています。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません