エクセルで請求書作成について

質問

エクセルで請求書を作りたいと考えているのですが、

　　A　　　B　　　　C
1　(1)　　　(2)　　　(3)
2　☆　　　☆　　　☆
3　□　　　△　　　
4　△
のような表があるとして、(1)、(2)、(3)は商品名
☆、□、△は購入者（出荷先）だとします。

そこで、別シートに
☆さんが買ったもの
(1)　100円
(2)　200円
(3)　300円
合計　600円

□さんが買ったもの
(1)　100円
合計　100円

△さんが買ったもの
(1)　100円
(2)　200円
合計　300円

のように表示したいと思うのですが、
うまい方法が思いつきません・・・

商品別に買った人を入力し、
買った人別に商品を集計するのはどのようにしたらいいでしょうか？
何か良い方法がありましたら宜しくおねがいします

回答No.3

zap35 · ベストアンサー

うーん、これだとデータが増えて顧客が複数回出現した場合はマクロを使わないとできないかもしれません。関数で実現しようとするとかなり難しいですよ。
それは元データの形式が悪いからです。

エクセルに限らずデータ処理する場合は、元データは
Ａ列　　　Ｂ列　　Ｃ列　　
商品名　購入者　　単価
（１）　　　☆　　　　600　　　　←単価はVLOOKUP関数で別表から検索させる
（２）　　　☆　　　　300
（３）　　　☆　　　　200
（１）　　　□　　　　600
（１）　　　△　　　　600
（２）　　　△　　　　300
（３）　　　□　　　　200
のように縦１方向に入力するのが「鉄則」です。二次元の表にしてはいけないのです。

上に書いた形式であれば、別シートの適当なセルに以下の式を貼り付けて下方向にコピーすれば「☆さん」の購入した商品が表示できます。（これでも「簡単な式」とは言えませんが、要求を満たすためには必要になります）
=INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$B$1:$B$100="☆")*ROW(Sheet1!$B$1:$B$100),),COUNTIF(Sheet1!$B$1:$B$100,"☆")-ROW(Z1)+1))
"☆"と書く代わりに購入者名を入力したセルアドレスを指定しても良いです。

あとはVLOOKUP関数で単価を検索して金額を求めればよいでしょう。
ただし上記式だと表示する商品がなくなるとエラーになります。それを回避するにはIF関数で条件を与えればよいです。
=IF(COUNTIF(Sheet1!$B$1:$B$100,"☆")>=ROW(Z1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$B$1:$B$100="☆")*ROW(Sheet1!$B$1:$B$100),),COUNTIF(Sheet1!$B$1:$B$100,"☆")-ROW(Z1)+1)),"")

更に疑問点を書きますが「誰と誰が購入者か」はどうやって見つけますか？　質問の例のように３名の購入者程度しかいないなら、別シートに
＞☆さんが買ったもの
＞□さんが買ったもの
＞△さんが買ったもの
と羅列するのは簡単ですが、１００名、２００名と増えたら、全ての購入者を拾えますか？　それを目視で行うのは大変そうですね。

上記の形式にすればピボットテーブルで購入者毎の売上金額を計算することもできますし、フィルタオプションや関数式で「重複しない購入者リスト」を作成することも可能です。
既に膨大なデータを入力してしまっているのでなければ、今からでも表の形式を見直すことをおすすめします。（見直さなければもっと難しいことになります）

お礼

回答ありがとうございます。
データは入力してしまっているのですが、
今後のことも考えて形式の変更も考えてみます。
ありがとうございます。

回答No.4

imogasi

質問の文章について
＞　　A　　　B　　　　C
1　(1)　　　(2)　　　(3)
2　☆　　　☆　　　☆
3　□　　　△　　　
4　△
なんて記号で書かず、
（商品　）鉛筆　　消しゴム　ノート
（購入者）山田　　岡田　　鈴木
（購入者）今井　　村田　　田中
・・
とかかけないの。判りやすいじゃない。
ーーー
こんな仕事がらみのものを関数でやろうとするのは、他の局面も含めて考えて、無理だと思う。
VBAが出来ないと無理と思う。
ーーー
商品　）鉛筆　　消しゴム　ノート
（購入者）山田　　岡田　　鈴木
のような表から出発しているが、こんな表から出発することに必然性があるのか。もっと別の原始帳票（取引が起こったときに一番初めに出来る帳票）があるでしょう。これを優先して考えないと、２度手間になるよ。
ーーー
いつもこのタイプの問題に、考えが甘い質問が多いのは、購入者の（ダブりのない）リストはあるのか。ないならどう言う方法でその表をつくるのか
それを質問するのが先だ。
ーー
もうひとつ商品（売価）単価の問題はどうするの、触れてなくて
、この課題をするには、経験や筋道を立ててものを考える、が不足しているとおもう。
ーー
関数は多列の中から見つけるのは苦手だと思う。
□さんはA,B,C列に出現がありえる。
MATCH関数など良く調べて見ること。検索列を複数列指定できない。
ーー
関数は表の組み替えも苦手だ。

お礼

ご指摘ありがとうございます。

私が直接請求業務を行っているわけではないので・・・
請求人数が多いもので、
いちいち
山田様4月分
鉛筆
消しゴム
合計　　　円

岡田様4月分
・・・
とやるのが面倒なので、
商品ごとに集計してある表からまとめてできないかなぁ～なんて
安易な考えでした・・・

回答No.2

olololol

私は、オートフィルタで☆だけを選び、（抽出）
それを別ファイルのシートにコピーして請求書作成する方法を取っています。
一覧上記の表（☆・□・△など入り乱れたもの）は売上日報専用ファイルとし、
そのデータをコピーして使う方は、請求書ファイルとわけています。
請求書ファイルの方は、シートA（適当に名前をつけてください）に☆だけを抽出したものをコピーし、シートB（請求書の原本）にはLOOKUP関数でシートAのデータが出るようにしています。
そして、来月になったら、シートC（適当に名前をつけてください）に、先月の請求データを移すようにしています。
シートAには、今月の請求データが入力されていて、
シートB（請求書原本）には今月のデータがLookup関数で反映される、
シートCには、過去の請求データがずらーと入力されていて、
というしくみにしています。
シートBは請求書の表を作り、関数を当てはめています。
ファイルの題名は、取引先名にしています。

請求書を出す先の数分、請求書ファイルがあります。
そして、そのファイルは請求書フォルダという題名をつけたフォルダにすべて収めています。
請求書フォルダを開けば、取引先名のついたファイルがあり、
それを開くと請求書が出せるというしくみです。

式を使う方法は分かりませんので、コピーしていますが、
結構早く出来ますし、使い勝手はいいです。

お礼

回答ありがとうございます。

いろいろな工夫をされているみたいですね。
ためして見たいと思います。
ありがとうございます。

回答No.1

Miusuke

★シート１★
商品A　　商品B　　商品C
顧客1　　顧客2　　顧客3

★シート２★
(セルA1)⇒顧客名入力
(セルA2)⇒商品A (セルB2)⇒商品Aの金額 (セルC2)⇒=COUNTIF(Sheet1!A:A,A1)
(セルA3)⇒商品B (セルB3)⇒商品Bの金額 (セルC3)⇒=COUNTIF(Sheet1!B:B,A1)
(セルA4)⇒商品C (セルB4)⇒商品Cの金額 (セルC4)⇒=COUNTIF(Sheet1!C:C,A1)
各、E列⇒B列×C列を表示。
任意のセル⇒E列のトータルを表示。

質問者さんのおっしゃる下表を用いての作成だとこのような形でしょうか？
私の回答では商品数、顧客数が多い場合には向かないと思いますが。。。
＞　　A　　　B　　　　C
＞1　(1)　　　(2)　　　(3)
＞2　☆　　　☆　　　☆
＞3　□　　　△　　　
＞4　△
よろしければご参考までに。

お礼

回答ありがとうございます。

商品数はそれほど多くはないのですが、
顧客数が結構多いもので・・・

参考にさせていただきます。
ありがとうございます。