エクセルで注文書→請求書を作っていて困っています。

質問

下記のような注文書がSheet1にあります。
A列　 B列　C列　　D列　E列
品名　 数量　単位　単価　 小計
りんご　1　 個　　100 ￥100
みかん　０　 個　　 30 ￥ 0
バナナ 2 本　　100 ￥200
スイカ 2　 個 200　　￥400
メロン 0 個　　300 ￥ 0
　　　　　合計 ￥700
上表のような顧客が数量を入力すれば小計が出るとこまでは出来ています。

Sheet2に注文のあった品目のみで請求書を生成したいのですが、
良い方法がありましたら教えていただけますでしょうか。
A列　 B列　 C列　　D列　　E列
品名　 数量　単位　 単価　　小計
りんご　1　 個　　 100 ￥100
バナナ 2 本　　 100 ￥200
スイカ 2　 個 200　　￥400
　　　　　 合計 ￥700
※要するに品名のラインナップがある中で0個のものは飛ばして
　上図のような形に自動生成できるものが希望です。

色々な関数を試してみましたが、結果関数の意味も分からずやっているので、
なかなか思うようにできなくて困っています。

どうぞ、良い方法がありましたらご教示の程、よろしくお願いいたします。

回答No.1

kkkkkm · ベストアンサー

一例ですが
(どちらのシートも項目名が1行目とした場合の式です)
Sheet1のF列に作業列を作ります。(空いてる列ならどこでもいいです)
F2に
=IF(B2>0,COUNTIF($B$2:B2,">0"),"")
として必要なだけ下にコピーします。
Sheet2の
A2に
=IFERROR(INDEX(Sheet1!A:A,MATCH(ROW(1:1),Sheet1!$F:$F,0),1),"")
$F:$FはSheet1の作業列を作成した列を指定してください。
として必要なだけ右と下にコピーします。

お礼

kkkkkm さん
早速のご教示ありがとうございました。
初めはセルを結合させていたりしたので、うまくいきませんでしたが、
それに気づいてからはスイスイ出来て、実用出来そうです。
感謝いたします。
本当にありがとうございました。

回答No.3

nishi6

Sheet1に100行目までの入力を可能にして、数量が0の行をSheet2に書き出します。100行以上入力があれば算式の100を多めに変更してください。
合計も出力していますが、算式ではなく、Sheet1の値です。Sheet2の数値部分の書式は「#,##0_ ;;」としてください。合計行の未入力セルを空白表示する目的です。

Sheet1,Sheet2ともセルA1を基準にしています。実際は違うと思いますが、セルA1を基準に回答通りに作成し、完成したら行・列の挿入、削除で適切な位置に調整してください。

Sheet2:セルA2　=IFERROR(INDEX(Sheet1!$A$2:$E$100,MATCH(LARGE((Sheet1!$E$2:$E$100<>0)*1/ROW(Sheet1!$A$2:$A$100),ROWS($A$2:$A2)),1/ROW(Sheet1!$A$2:$A$100),0),COLUMNS(Sheet1!$A$1:A$1)),"")

長い。この算式をコピーし、Sheet2のセルA2を選択して数式バーに貼り付け、Ctrl+Shift+Enterで登録します。配列数式になります。
上手く登録できたら、セルA2を２行目の列方向に普通にコピーします。２行目がコピーできたら、２行目を下方向に多めにコピーしてください。Sheet1で何行入力があるか分からないので何行でも登録できます。算式は100行目までとしています。

当方、Win10、Excel2010です。

回答No.2

imogasi

「その行で、指定列の値が条件該当の場合、行を詰める（飛ばす）」という関数による技法があればよいのでしょう。
ーー
下記は方法の一例です。
ＶＢＡでやれば、何の珍奇な工夫も要らないのだが。
業務にエクセルを（データベース的に）使うなら関数を卒業したら。
例で説明すると
Ａ－Ｅ列で、Ａ、Ｂ列は元データ　他は下記関数で返す値。
柿とキウィは、記入なし（空白）、とする例。
リンゴ 20 1 1 1
柿 　　　 1 2 0
梨 　　2 2 3 2
桃　　 5 3 4 3
ブドウ 0 4 4 0
みかん 34 5 5 4
キウィ 5 6 0
オレンジ あり 5 7 #VALUE!
ーー
Ｃ２セルの式　=COUNT($B$2:B2)
Ｄ２セルの式　=COUNTIFS($B$2:B2,"<>0",$B$2:B2,"<>""""")
Ｅ２セルの式　=IF(VALUE(B2)<>0,MAX($E$1:E1)+1,0)
３通りやってみたが、Ｅ列が良さそう。
ーー
それで　Ｇ２の式
=IFERROR(INDEX(A1:A10,MATCH(ROW()-1,E1:E10,0),0),"")
Ｈ２の式　
=IFERROR(INDEX(B1:B10,MATCH(ROW()-1,E1:E10,0),0),"")
結果　Ｇ列、Ｈ列
リンゴ 20
梨　　 2
桃　　 5
みかん 34
ーー
他シートに結果データを持って来たいときは、上記各関数式において、元データの「シート名！」をセル範囲に被せて式を作ってください。
＝＝＝
一種の、条件による関数式での，「抜出し問題」で、ワーク列（中間列）を使わないと、式が複雑になる。
過去にもこのタイプの質問は（絶えることなく）時々あり、複雑な式の組み合わせの回答が出ている。
Ｇｏｏｇｌｅで「Ｅｘｃｅｌ　関数　条件による抜出」などにより照会を勧める。
Ｅｘｃｅｌ関数はこの手の課題に適してない。

お礼

imogasiさん
ご教示ありがとうございました。
先に教えてくださった方の方法がとてもシンプルでしたので、
そちらを採用させていただきました。

お忙しいところお手数をお掛けし、誠にありがとうございました。