みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセル2010 項目、金額等を自動振分作成したい

エクセルの初心者で自動振分がどうしてもわからないので教えていただきたいのです。
A1のセルには「仕入先・発注先」
B1のセルには「現場名」
C1のセルには「担当名」
D1のセルには「納入日」
E1のセルには「金額(税抜)」
F1のセルには「金額(税込)」
の項目を記入しています。
(1)別のシートにそれぞれ「担当名」別で自動振分を行いたいのです。
さらに、
(2)別のシートにそれぞれ「現場名」別で自動振分を行いたいのです。
上記の事をエクセルでできますでしょうか?
わかる方おられましたら教えていただけますでしょうか?
エクセルに関して初心者なものでわかりやすく教えていただけると大変ありがたいので
宜しくお願いします。

投稿日時 - 2011-09-07 14:57:11

QNo.6995429

すぐに回答ほしいです

質問者が選んだベストアンサー

 今仮に、元データが入力されているシートのシート名がSheet1であるものとします。
 又、例えば「Aさん」という人が担当している案件のデータのみを表示するためのシートもまた「Aさん」というシート名であるものとします。
 それと、適当な空きシートの中の適当な列を、作業列(補助的な計算処理を行うための関数を入力するために使用するセルの列)として使用します。
 ここでは仮に、Sheet2のA列を作業列として使用するものとします。

 まず、Sheet2のA1セルに次の数式を入力して下さい。

=IF(INDEX(Sheet1!$C:$C,ROW())="","",INDEX(Sheet1!$C:$C,ROW())&COUNTIF(Sheet1!$C$1:INDEX(Sheet1!$C:$C,ROW()),INDEX(Sheet1!$C:$C,ROW())))

 次に、Sheet2のA1セルをコピーして、Sheet2のA2以下に(元データの行数を上回るのに充分な行数となるまで)貼り付けて下さい。(ドラグ&ドロップで行うと操作が楽です)

 次に、Aさんのシートの

A1セルに  担当名
A3セルに  仕入先・発注先
B3セルに  現場名
C3セルに  納入日
D3セルに  金額(税抜)
E3セルに  金額(税込)

という具合に、元データの項目名を入力して下さい。
 次に、AさんのシートのA4セルに次の数式を入力して下さい。

=IF(ROWS($4:4)>COUNTIF(Sheet1!$C:$C,$B$1),"",INDEX(Sheet1!$A:$F,MATCH($B$1&ROWS($4:4),Sheet2!$A:$A,0),MATCH(A$3,Sheet1!$1:$1,0)))

 次に、AさんのシートのA4セルをコピーして、AさんのシートのB4~E4の範囲に貼り付けて下さい。
 次に、Sheet1のD2セルをコピーしてから、AさんのシートのC4セルにカーソルを合わせて、マウスを右クリックして下さい。
 すると現れる選択肢の中から、「%」マークと筆の絵が一緒に描かれているボードのイラストを、選択してクリックして下さい。(書式のみをコピーするための操作です)
 次に、AさんのシートのA4~E4の範囲をまとめてコピーして、Aさんのシートの5行目以下に貼り付けて下さい。

 最後に、AさんのシートのB1セルに

Aさん

という具合に、元データに入力されている名前を入力しますと、「Aさん」が担当している案件のデータのみが自動的に表示されます。
 そして、他の担当者のシートを作成する際には、Aさんのシートのコピーシートを作成して、担当者に合わせたシート名とした後、各担当者のシートのB1セルに、その担当者の名前を入力して下さい。
 コピーシートの作成方法は、Excelウィンドウの下の方に、各シートのタグがありますから、コピー元としたいシートのシート名が表示されているにカーソルを合わせて、マウスを右クリックして下さい。
 すると現れる選択肢の中にある[移動またはコピー]と記されている箇所をクリックして下さい。
 すると「シートの移動またはコピー」ダイアログボックスが現れますから、その中にある[コピーを作成する]と記されている箇所をクリックして、チェックを入れてから、「シートの移動またはコピー」ダイアログボックスの[OK]ボタンをクリックしますと、コピーシートが作成されます。
 それから、シート名が記されているタグにカーソルを合わせて、マウスをダブルクリックすれば、そのシート名を編集する事が出来ます。

 尚、AさんのシートのB1セルに、次の数式

=REPLACE(CELL("filename",B1),1,FIND("]",CELL("filename",B1)),)

を入力しておけば、そのシートのシート名が自動的にB1セルに表示されますから、Aさんのシートを始めとする、各担当者のシート名を、

Aさん

という具合に、元データに入力されている名前と同じものとしておけば、Aさんのシートを雛形として、他の担当者のシートを作成する際に、シート名を担当者名と同じものとするだけで良いので、一々、B1セルに担当者名を入力し直す手間が不要になります。

 又、AさんのシートのA4セルに入力する数式を、先述のものではなく、次の様な数式としますと、Sheet2に作業列を設けなくとも、同様の事が可能になります。(但し、元データの行数が数千行以上にもなると、以下の関数では計算処理に要するパソコンの負荷が過大になります)

=IF(ROWS($4:4)>COUNTIF(Sheet1!$C:$C,$J$1),"",INDEX(Sheet1!$A:$F,SUMPRODUCT(ROW(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH("゛",Sheet1!$C:$C,-1)))*(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH("゛",Sheet1!$C:$C,-1))=$J$1)*(COUNTIF(OFFSET(Sheet1!$C$1,,,ROW(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH("゛",Sheet1!$C:$C,-1)))-ROW(Sheet1!$C$1)+1),$J$1)=ROWS($4:4))),MATCH(I$3,Sheet1!$1:$1,0)))

投稿日時 - 2011-09-07 19:15:21

このQ&Aは役に立ちましたか?

2人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.3

例えば元の表をシート1とします。
作業列としてG1セルには現場作業列、H1セルには担当作業列とでも文字列を入力します。その後にG2セルに次の式を入力してH2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(B2="","",IF(COUNTIF(B$2:B2,B2)=1,ROUND(INT(MAX(G$1:G1))+1.0001,4),ROUND(INT(INDEX(G$1:G1,MATCH(B2,B$1:B1,0)))+COUNTIF(B$2:B2,B2)/10000,4)))

そこで別のシートに担当者名別や現場名ごとにシートを作成するわけですがそれらすべての表示に必要とするシートについて作業グループを設定します。例えばシート2からシート20までを作業グループに設定するのでしたらシート見出しでSheet2をクリックしたのちにShiftキーを押しながらSheet20をクリックします。又は、Ctrlキーを押しながらそれぞれのシート名をクリックしていきます。なお、作業グループを解除するためには単にシート1をクリックすればよいでしょう。
さて、このように作業グループを作成した状態ではシート2がアクティブなシートになっていますね。そこでシート2のA1セルには担当名、A2セルには現場名と文字を入力します。
C1セルには次の文字列を入力します。
「B1セルには担当者名をB2セルには現場名を入力します。ただしB1,B2セルのいづれか一方のセルは空にしてください。」
A3セルからF3セルまでにはシート1の1行目と同じ項目名を入力します。
A4セルには次の式を入力したのちにF4セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COUNTA($B$1:$B$2)<>1,"",IF(OR(AND($B$1<>"",COUNTIF(Sheet1!$H:$H,INT(INDEX(Sheet1!$H:$H,MATCH($B$1,Sheet1!$C:$C,0)))+ROW(A1)/10000)=0),AND($B$2<>"",COUNTIF(Sheet1!$G:$G,INT(INDEX(Sheet1!$G:$G,MATCH($B$2,Sheet1!$B:$B,0)))+ROW(A1)/10000)=0)),"",INDEX(Sheet1!$A:$F,IF($B$1<>"",MATCH(INT(INDEX(Sheet1!$H:$H,MATCH($B$1,Sheet1!$C:$C,0)))+ROW(A1)/10000,Sheet1!$H:$H,0),IF($B$2<>"",MATCH(INT(INDEX(Sheet1!$G:$G,MATCH($B$2,Sheet1!$B:$B,0)))+ROW(A1)/10000,Sheet1!$G:$G,0),"")), COLUMN(A1))))

これらの入力操作が終わったら作業グループを解除します。作業グループに含まれたすべてのシートに同じ文字列や式がコピーされています。
そこで例えばシート2のB1セルに担当者名を入力することでその担当者のデータが表示されます。別のシートでB1セルに同じように担当者名を入力すればそのデータが表示されます。B2セルに現場名を入力すればその現場名でのデータが表示されます。このようにB1やB2に入力したデータ関連のシートを次々に作ることができますね。

投稿日時 - 2011-09-07 20:42:39

ANo.1

自動振分の意味がつかめませんでしたが、ソートをかけて集計できればよい、ということでしょうか?


1 シートをコピーしましょう

2 コピーしたシートについて、
  担当名のらんにカーソルをあわせて、ソートをかけましょう
  *AZ↓のアイコンを使えばソートがかかります。

3 データ → 集計 を選択し

  グループの基準   「担当名」
  集計の方法      「合計」
  集計するフィールド  「金額(税込)」「金額(税抜)」

これで出てくる結果で、いかがでしょうか。

全く違うものをお求めでしたら、お役に立てず、すみません。
 
  

投稿日時 - 2011-09-07 16:39:02

あなたにオススメの質問