EXCELにて請求書と請求明細を作るとき

質問

EXCELにて請求書と請求明細を作るときに効果的な関数があったら教えて下さい。
SHEET1が請求書だとします。
項目は
１．　12/1 りんご　３個　１５０円
２．　12/3　バナナ　２個　２００円
３． 12/10 メロン　１個　５００円
４． 12/18 りんご　５個　２５０円
５．　12/20 バナナ　１個　１００円
の場合、SHEET2がりんごの明細
SHEET3がばななの明細
SHEET4がメロンの明細とます。
SHEET2に　
12/1 りんご　３個　１５０円
12/18 りんご　５個　２５０円
と入り、SHEET3に
12/3　バナナ　２個　２００円
12/20 バナナ　１個　１００円
と明細が各商品のシートごとに入るような関数はありますか？
請求書を訂正した場合に、自動的に明細も変更になるようにしたいのですが、可能なことでしょうか？（12/20のバナナをメロンに替えたら、SHEET4の明細に12/10と12/20が入り、SHEET3から12/20分が抜けるという状態です）

回答No.2

shiotan99 · ベストアンサー

こんにちは～

以下の方法を試してみてください。

**<<前提条件>>*******************
Sheet1 から Sheet4 まですべて
A列‥「日付」、B列‥「品名」、C列‥「数量」、D列‥「金額」
1行目が見出し行で、データは 2行目から
*********************************

■ 作業列を使う方法 ■
作業列は Sheet1のどの列でもかまいません。
ここでは仮に J列からK列を作業列とします。

★ Sheet1 の J2セルに
=IF($B2="りんご",ROW(),"")

と入れて、L2セルまで右にコピー
K2 の "りんご" → "バナナ" に
L2 の "りんご" → "メロン" に変更

J2：L2 の式を、請求データ最終行までコピー
今後もデータが増えるなら、多めにコピーしておいてください。

★ Sheet2 の A2セルに
=IF(COUNT(Sheet1!$J:$J)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$J:$J,ROW(A1))))

をコピー＆ペーストしてください。
そのあと、D2セルまでコピー
A2：D2 の式を必要なだけ下にフィルコピー
コピー後、A列の表示形式を 「日付」にしてください。

◆ Sheet3 は上の数式の Sheet1!$J:$J → Sheet1!$K:$K に変えてください（ 2ヶ所 ）。
◆ Sheet4 は上の数式の Sheet1!$J:$J → Sheet1!$L:$L に変えてください（ 2ヶ所 ）。
↑は、作業列が J列から K列の場合です。J列から K列以外の列を作業列にする場合は、その列記号に合わせてください。
◆ 作業列が目障りなら非表示にしてください。
◆ ROW(A1) は、先頭のセル（ この例では A2セル ）がどこであれ、A1のままにしておいてください。←先頭のセルのみ。あとは右に下にコピーするだけで OK。
*****

■ 作業列を使わない方法 ■
★ Sheet2 の A2セルに
=IF(COUNTIF(Sheet1!$B$1:$B$100,"りんご")<ROW(A1),"",INDEX(Sheet1!A:A,100-LARGE(INDEX((Sheet1!$B$1:$B$100="りんご")*100-ROW($A$1:$A$100),0),ROW(A1))))

をコピー＆ペーストしてください。
そのあと、D2セルまでコピー
A2：D2 の式を必要なだけ下にフィルコピー
コピー後、A列の表示形式を 「日付」にしてください。

◆ Sheet1 の最大データ数を 100としています。
それ以上ある場合は、100（ 5ヶ所 ）を変更してください。
◆ Sheet3、Sheet4 は、"りんご" （ 2ヶ所 ）をそのシートの品名に変えてください。

お礼

ありがとうございました。できました。私の下手な説明を理解してくださってありがとうございます。

回答No.4

imogasi

＞SHEET1が請求書だとします。
というよりも、（１）Ｓｈｅｅｔ１を請求書データとします。
（２）月ごとの請求書として、Ｓｈｅｅｔ１には該当月の請求分明細のみが抜き出されているとします。
そうしないとさらに式が複雑化します。
（３）その中でりんごのみを別葉に印刷することを考えます。
例データ
（質問では個数、金額を全角で単位つきの文字列になっているが
数字を入れるようにして、書式で単位を設定すること。
計算ができない弊害があるため。）
Ｓｈｅｅｔ１Ａ１：Ｅ１２
1 12月1日 りんご ３個 150円
2 12月3日 バナナ ２個 200円
3 12月10日 メロン １個 500円
4 12月18日 りんご ５個 250円
5 12月20日 バナナ １個 100円
6 12月21日 りんご ４個 200円
7 12月22日 バナナ ２個 300円
8 12月23日 りんご １個 400円
9 12月24日 メロン ３個 1,000円
10 12月25日 バナナ ３個 800円
11 12月26日 りんご ５個 400円
Ｈ２に（Ｇ列以右ならどの列でも良い）
=COUNTIF($C$2:C2,Sheet2!$B$1)
Ｈ１２まで式を複写。
Ｈ２：Ｈ１２
1
1
1
2
2
3
3
4
4
4
5
Ｓｈｅｅｔ２に行き
Ｂ１にりんごを入れる。
Ｂ１２に=COUNTIF(Sheet1!$C$1:$C$100,$B$1)
Ｅ１２に=SUM(E2:E10)
をいれる。
Ｂ３に式
=IF((ROW()-2)<=$B$12,INDEX(Sheet1!$B$2:$E$100,MATCH(ROW()-2,Sheet1!$H$1:$H$100,0)-1,COLUMN()-1),"")
と入れてＥ３まで式を複写
Ｂ３：Ｅ３を範囲指定して、Ｅで＋ハンドルを出して、
Ｂ１０：Ｅ１０まで引っ張る
結果
12月1日 りんご ３個 150円
12月18日 りんご ５個 250円
12月21日 りんご ４個 200円
12月23日 りんご １個 400円
12月26日 りんご ５個 400円

5 1,400円
各列や各セル（Ｂ１２、Ｅ１２など）書式を適当に整えてください。
以上imogasi方式です。

お礼

imogasi方式勉強してみます。私の下手な質問でこんなに詳しく答えてくださってありがとうございます。

回答No.3

imogasi

質問の回答ではないですが、
（１）請求書というのは、得意先に出すものだと思います。
得意先の名前、またはコードはどうなってますか。
（２）りんごなどの品物別に請求書シートを分けてますが
請求書は品物別に作るのですか
もしりんごなどが得意先の意味なら、紛らわしいから、今後は
Ａ商事、Ｂ商店とでもにして、例を作ってください。
エクセル関数は、抜き出しが不得手で、関数でやるには式が複雑化します。
最低限のＶＢＡを使って処理するよう、勉強したほうが良い。
請求書であれば、この後印刷するのでしょうが
シートごとに分けないほうが良い。
シート１＝りんご
シート２＝バナナ
・・・
１シートずつ印刷（手作業？）
でやるのでなく
シート１＝りんご
印刷（ＶＢＡで）
シート１＝バナナ
印刷（ＶＢＡで）
・・・
の形を目指すべきですが
明細が複数ある（本質問では複数日の注文あり）とき、１行ずつづらしてセットするのが、関数では
むつかしい。
作業列を使う方法で、私はimogasi方式と名づけて、多数回答しています。ＯＫＷＡＶＥで「imogasi方式」で照会してもらえば、類似例が出ると思います。
別質問で上げてみます。

回答No.1

s___o

ご質問はセルに効果的な関数(数式)を与える方法をお尋ねしているのでしょうか？
そうですと、少々無理があると思います。

マクロを使えば、比較的簡単にできます。
SHEET1の果物名をキーに各シートに割り振るだけですから。

お礼

どうもありがとうございました。マクロを理解できるようになりたいです。