エクセルでの転記について

質問

関数を使って転記をしています。
Sheet1 が　売上げ全体
Sheet2 が　担当者別　という形で、
Sheet2のB1に、「Sheet1　のA1（担当） が　田中　ならば、Sheet1のB1を入れなさい。そうでなければ空白にしなさい。」という関数を使用し、売上げ金額、経費、利益・・・・等C1,D1,E1・・・と転記するようにしています。

転記自体はうまく行くのですが、担当が「田中」でない場合、空白になるため、１０件の売上げの内、田中の売上げが４件しかなかった場合に残りの６件は空白行になってしまい、一覧としてみた場合見栄えが悪くなってしまいます。

原因は、Sheet1のA1が田中なら・・・,A2が田中なら・・・,A3が田中なら・・・と1行ずつ固定して式を入れているからだということは何となく解るのですが、１０件の売上げの中の田中だけをSheet2を転記しなさいという関数が色々調べているのですが答えにたどり着けません。

どういった関数にすれば、可能なのでしょうか？
どうぞよろしくお願いします。

回答No.5

mshr1962

#1,3のmshr1962です。
とりあえず最初のほうだけですが...
＞転記されたときに、各担当０００１の転記だけされないのです。
Sheet2の開始行は2行目からですか？
B2=IF(COUNTIF(Sheet1$B:$B,$A$2)>=ROW(A1),VLOOKUP($A$2&TEXT(ROW(A1),"0000"),Sheet1!$A$1:$Z$60000,COLUMN()+1,FALSE),"")
とROW()のカッコ内にA1とするかROW()-1として調整してください。

お礼

ありがとうございます。
ROW(A1)にしたら、担当単位で全て転記されました。
セルの違いで変わってしまうは解っていたので、慎重にやったつもりだったのに、すみません。

回答No.4

omusupa

#2です。
すみません・・・。抽出結果も同じシートに作成してました。解決しているようですが・・・・参考になれば。

Sheet2のA2番地に以下の式を入れます
=IF(ISERROR(MATCH("田中",Sheet1!$A$2:$A$23,0)),"",MATCH("田中",Sheet1!$A$2:$A$23,0))

今度は、Sheet2のA3番地に以下の式を入力し、適当な行までコピーします。
=IF(ISERROR(MATCH("田中",OFFSET(Sheet1!$A$2:$A$23,Sheet2!A2,0,100,1),0)+Sheet2!A2),"",MATCH("田中",OFFSET(Sheet1!$A$2:$A$23,Sheet2!A2,0,100,1),0)+Sheet2!A2)

あとは、Sheet2のB2番地に
=IF(ISERROR(INDEX(Sheet1!$B$2:$B$23,Sheet2!A2,1)),"",INDEX(Sheet1!$B$2:$B$23,Sheet2!A2,1))
です。

お礼

ありがとうございます。
↑の式をいれたらできました。

同じ転記をやるのでも、色々な方法があるものですねぇ～。
１つ１つの関数は何となくわかっても、組み合わせるのがさっぱり解らなくて・・・。
まだ、日付で転記したり、残高で転記したり、色々作る予定ですが、思わずＰＣの電源を落としたくなる今日この頃です・・・。

回答No.3

mshr1962

#1のmshr1962です。
＞Sheet1に入力したら自動的にSheet2に転記する方法を探しています。
数式にこだわるなら、
Sheet1でA列の前に列を挿入して
=$B1&TEXT(COUNTIF($B$1:$B1,$B1),"0000")
として出たの最後までコピーします。
Sheet2のA1に田中と入力
B1=IF(COUNTIF(Sheet1$B:$B,$A$1)>=ROW(),VLOOKUP($A$1&TEXT(ROW(),"0000"),Sheet1!$A$1:$Z$60000,COLUMN()+1,FALSE),"")
これを横方向にコピー後、必要分下方にコピー

もっともVBAを使って転記（コピー＆ペースト）を行ったほうがスマートかもしれませんが．．．

補足

またまたお返事ありがとうございます。

↑の式で苦戦しながらも転記できました。
画面が変わっていく時は感動でした。
しかし・・・・１番目の式で、担当のカウントを付けていきますが、
転記されたときに、各担当０００１の転記だけされないのです。
設定セルは式にあうように変えたし、式も間違っていないようなのですが・・・・。
何が理由でしょうか？教えていただいたのに、式の中身が所々わかるものの、完全に理解できない為すみません。

また・・・今回は担当者で転記をしましたが、これを１月（1/1～1/31）に納品された契約のみ転記、ということも可能でしょうか？
1/1以上、1/31以下というスラッシュをいれた関数を作ってみたら、エラーがでて、スラッシュはダメなんだろうな・・・とは思うんですが。
日付だから、↑の式をちょっと変えてというわけにはいかないのでしょう・・・ねぇ。

正直、関数での転記を覚えてから色々な表を作成しましたが、作るたびにどこかで式がずれたり、コピー＆ペーストの繰り返しで疲れを感じ、先日VBAの初心者の本を購入しました。ただ、２月１日までに綺麗に転記される表を提出しなければならなかったため、とりあえず関数で。
これからVBAを勉強して（苦難の道ですが・・・）挑戦しようと思っています。

回答No.2

omusupa

Sheet1のA2からA23までに、担当者の名前が入っていると仮定します。
Sheet1B2からB23までにも、何か入力しておいてください。
そして、Sheet2のA2番地に以下の式を入れます。

=IF(ISERROR(MATCH("田中",$A$2:$A$23,0)),"",MATCH("田中",$A$2:$A$23,0))

今度は、A3番地に以下の式を入力し、10行ぐらいコピーしてみてください。

=IF(ISERROR(MATCH("田中",OFFSET($A$2:$A$23,F2,0,100,1),0)+F2),"",MATCH("田中",OFFSET($A$2:$A$23,F2,0,100,1),0)+F2)

上記の式で、「田中」さんがリストの中の何番目に出てくるかを調べることができます。あとは、B2番地に

=IF(ISERROR(INDEX($B$2:$B$23,F2,1)),"",INDEX($B$2:$B$23,F2,1))

を入力して、コピーしてみてください。
Ａ列の数字が気になるようであれば、非表示にするとか、文字色を白にするとか・・工夫してください。

補足

お返事ありがとうございます。

教えていただいた関数入れてみました。
２番目の式をいれコピーをしたら、空白セルと０セルの２パターンに分かれ、３番目の式をいれコピーを入れると２番目とずれた位置で空白と０が並びました。

選択するセルが間違っていたのか・・・。
sheet1を選択する文章？はいらないのでしょうか？
F2にってセルのF2？？
教えていただいた関数の意味を自分で理解できるようにこれから勉強です！！

回答No.1

mshr1962

数式でなくていいなら
Sheet1の1行目に項目名を設定してから
「データ」「フィルタ」「オートフィルタ」として
氏名の列の▼ボタンを押し、田中を選択する。
田中のデータのみが表示されるので、転記が必要ならコピーして別のシートに貼り付ける。

お礼

お返事ありがとうございます。

でも・・・オートフィルタではなく、Sheet1に入力したら自動的にSheet2に転記する方法を探しています。
店舗が数件あり、それぞれの店舗、担当で日々オートフィルタ→コピーとなりますと、手間が掛かってしまうので・・・。