Excel（エクセル）活用コミュの《関数の質問》注文リストから納品書の作成

いつもお世話になっております。
お知恵を貸して頂けましたら幸いです。

私はA～Fという商品を販売しており、それぞれの価格は違います。
顧客名ごとに、何を何個購入した、というリストをExcelで作成しています。（写真上部）

このリストから、顧客ごとの納品書を作成したいと考えています。（写真下部）
この納品書を、リストから関数を使って自動作成をしたいです。
購入するものはひとそれぞれ違います。その人ごとの納品書を上から詰めて表示するには
どのように関数を入力したらいいか、お教えいただきたいです。

それぞれのセルに入力したい条件は以下のとおりです。

C13→　C5：H5のなかで数字が入力されているセルのうち一番左の列の3行目に入力してある文字は何か
D13→　C5：H5のなかで数字が入力されているセルのうち一番左の列の4行目に入力してある数字は何か
E13→　C5：H5のなかで数字が入力されているセルのうち一番左のセルに入力してある数字は何か

C14→　C5：H5のなかで数字が入力されているセルのうち左から2番目の列の3行目に入力してある文字は何か
D14→　C5：H5のなかで数字が入力されているセルのうち左から2番目の列の4行目に入力してある数字は何か
E14→　C5：H5のなかで数字が入力されているセルのうち左から2番目のセルに入力してある数字は何か

説明がわかりづらかったらもうしわけございません。
宜しくお願いいたします。

mac OS X Yosemite 10.10.4
Excel for Mac 2011

コメント(28)

最初
全て
最新の40件

[1] mixiユーザー 08月21日 01:14

>C13→　C5：H5のなかで数字が入力されているセルのうち
>一番左の列の3行目に入力してある文字は何か

画像で見る限り、セル位置がずれてるようですが…

↓
C12→　B5：G5のなかで数字が入力されているセルのうち
一番左の列の3行目に入力してある文字は何か

だとして、
・エラー処理は割愛。
・商品名の種類は9個以内（1桁）

実用面でもう少し工夫が必要な気もしますが、
現状の方向性として
（当方の環境WIN7、EXCEL2007）

補助行
B2に　=10^(5-COLUMN()+2)*(COLUMN()-1)　といれてG2までフィル

C12に　=INDEX($B$3:$G$3,MID(SUBSTITUTE(SUMPRODUCT(($B$2:$G$2)*($B$5:$G$5>0)),"0",""),ROW()-11,1))
D12に　=HLOOKUP(C12,$B$3:$G$6,2,FALSE)
E12に　=HLOOKUP(C12,$B$3:$G$6,3,FALSE)
F12に　=D12*E12

と入れ、C12:F12を選択状態で、その範囲を下方向にフィル

顧客２も参照範囲を変えれば同様の式でいけるかと思います。
参考になりますでしょうかね。

[3] mixiユーザー 08月21日 10:17

>>[1]
早速のコメント、ありがとうございます。
セル位置のずれ、質問しておきながら失礼致しました。
ご指摘のとおりです。

商品名の種類は、場合によっては70種類に及ぶことがあります。
その場合の対処法などはございますでしょうか？

[4] mixiユーザー 08月21日 10:20

関数でやるのは無理がありますね。
データの形式も根本的に見直したほうがいいと思います。

[5] mixiユーザー 08月21日 16:28

>>[4]
ご指摘、ありがとうございます。
試行錯誤してみたいと思いますが、暫定的にこの形式で対応できる方法で考えております。

[6] mixiユーザー 08月21日 16:38

>>[5]

でも、無理だと思いますよ。

エクセルの練習ならいいんですけど、業務で使おうと思うのならやめておいたほうがいいと思います。

[9] mixiユーザー 08月22日 01:17

関数で出来ないことはないかもしれませんが、面倒ですね
現状のものですとVBAを使用できるなら、そのほうが楽に出来るかと思いますね

とりあえず関数で組んでは見ましたが、かなり面倒な式になっています・・・

C10セル顧客名
C12セル =IF(ISERROR(INDEX($B$3:$G$3,1,SUMPRODUCT(MATCH(0,0/(INDIRECT(ADDRESS(MATCH($C$10,A:A,0),IF(ROW(C12)=12,COLUMN($B$3),MATCH(C11,$B$3:$G$3,0)+2))&":"&ADDRESS(MATCH($C$10,A:A,0),COLUMN($G$3)))),0))+IF(ROW(C12)=12,0,MATCH(C11,$B$3:$G$3,0)))),"",INDEX($B$3:$G$3,1,SUMPRODUCT(MATCH(0,0/(INDIRECT(ADDRESS(MATCH($C$10,A:A,0),IF(ROW(C12)=12,COLUMN($B$3),MATCH(C11,$B$3:$G$3,0)+2))&":"&ADDRESS(MATCH($C$10,A:A,0),COLUMN($G$3)))),0))+IF(ROW(C12)=12,0,MATCH(C11,$B$3:$G$3,0))))
D12セル =IF(C12="","",HLOOKUP(C12,$B$3:$G$6,2))
E12セル =IF(C12="","",HLOOKUP(C12,$B$3:$G$6,MATCH($C$10,$A$3:$A$6,0)))
F12セル =IF(C12="","",D12*E12)
C12:F12をC14:F14までオートフィル

C10セルの値を変更するとその値と一致する顧客名のデータを表示します
式内の必要な参照範囲を調整すれば商品項目が変わっても使用できるはずです(多分・・・)

※コメント7,8は誤植があったため削除しました、すみません

[10] mixiユーザー 08月23日 04:46

A列B列を作業列にして、冗長性を増やした代わりにコピペしやすくしました。
(と言うか作業列をなくす事は放棄してます＾＾；)

A11= 5
(B11は空白か0)
C10= =INDIRECT("A"&A11)
F10= =SUM(F12:F14)

A12= =A11
B12= {=MATCH(1,(INDIRECT("B"&A12&":G"&A12)>0)*(COLUMN(INDIRECT("B"&A12&":G"&A12))>(COLUMN(INDIRECT("B"&A12))+B11-1)),0)}
C12= =IF(ISNA(B12)=1,"",INDEX(B$3:G$3,1,B12))
D12= =IF(C12="","",INDEX(B$4:G$4,1,B12))
E12= =IF(D12="","",INDEX(INDIRECT("B"&A12&":G"&A12),1,B12))
F12= =IF(E12="","",D12*E12)

A13:F14= A12:F12をコピペ

B12は配列数式です。
中括弧の中身だけを入力してからctrlとshiftを押しながらenterで確定します。
(Windows7 LibreOfficePortable3.4.5にて)

[11] mixiユーザー 08月23日 21:15

突然で申し訳ないですが、この表ってもしかして横にも伸びません？

＞商品名の種類は、場合によっては70種類に及ぶことがあります。

この一文から察するに、継時的に横方向に商品数が増やしながら運用している気がするんですけど。

自分のことで申し訳ないのですが、先日他部署からこれとよく似た相談を受けたので気になりまして。
他部署の表がやっぱり横に伸びるタイプだったんですよ（笑）

それで、トピ主さんの質問とは若干ずれてしまうのですが、私もミスキタさんと同様、この表自体の見直しをした方がよいのではと思っています。
ちなみに私が相談への返答は、『「商品リスト」と「顧客リスト」を別のシートに作って、「購入記録」として商品の動きを記録するシートを作るようにしてください』です。
具体的には添付図の３種類のシートを作成し、「購入記録」シートでvlookupで「顧客リスト」「商品リスト」を参照する形です。（「購入記録」の顧客名、単価こうすればフィルタで特定の顧客の記録も出せますし、テーブルにしておけば特別なことは何もしなくても合計金額も出せます。
この形なら、ピボットテーブルへの利用もできますし、関数も簡単なので自力でメンテナンスできますし。
どうでしょうか？

[12] mixiユーザー 08月23日 21:28

訂正
×ちなみに私が相談への返答は⇒○ちなみに私のした他部署からの相談への返答は

です。日本語不自由ですみませんm(_ _)m
それと、図では購入記録のＡ列は購入日となっていますが、伝票番号などの方がよいかもですね。

[13] mixiユーザー 08月23日 22:33

>9で「式内の必要な参照範囲を調整すれば商品項目が変わっても使用できるはずです(多分・・・)」
と書きましたが正確には
「式内の必要な参照範囲を調整すれば商品項目数が変わっても使用できるはずです(多分・・・)」でした

[15] mixiユーザー 08月24日 09:37

>>[11]
コメント・ご提案ありがとうございます。
はい、横にも伸びます。ですが、伸びても70位までです。
納品書形式で（仮に）1000人、一人一人にプリントアウトしなければならなく、購入記録という形でフィルターがけ扱いにすると、フィルターの内容を変えては1000回プリントアウト、としなければならなくなるためなかなか難しい状況です。

顧客も、ある程度固定、というわけではなく常時違う人ばかりなので顧客はリスト管理する程のものではない状態です。

業務内容に関わってしまうため詳しく説明できず、申し訳ありません。過去事例をお教え頂き、ありがとうございます！お教え頂いた方法も参考にさせて頂きます(^-^)

[16] mixiユーザー 08月24日 10:58

>>[15]

> 購入記録という形でフィルターがけ扱いにすると、フィルターの内容を変えては1000回プリントアウト、としなければならなくなるためなかなか難しい状況です。
そうですよね？手作業ではミスも入り込むでしょうし、やりたくないですよね？

だから、９でマリ男さんが「VBAを使用できるなら、そのほうが楽に出来るかと思いますね」と言っているのです。

> 顧客も、ある程度固定、というわけではなく常時違う人ばかりなので顧客はリスト管理する程のものではない状態です。
なぜリピーターしか登録する必要がないと思われるのでしょう？たった1枚でも、たった1回限りでも、納品書を発行する以上は顧客だとなぜ捉えられないのでしょう？一見さんと判断された「顧客」は登録されていない以上、2回目も一見さん扱いになるんですよね？いつ常連さんと認めてもらえるんですか？

> 業務内容に関わってしまうため詳しく説明できず、申し訳ありません。
実データは必要ありません。むしろ業務の細部よりも俯瞰して一般化するところが大事なのです。

[18] mixiユーザー 08月24日 18:09

>>[15]

私の提案の形に変更すると関数やＶＢＡの知識がなくても添付図のようなものに展開できますよ。
かなりご希望の形には近いのではないでしょうか？
まあ、1000人ものお客さんのデータを～という話になると何人かの方がおっしゃっているようにＶＢＡが現実的だと私も思いますけど。
しゃしゃってすみません。お目汚し失礼いたしました。

[19] mixiユーザー 08月24日 18:12

総計消すの忘れました。総計は気にしないでください。

[20] mixiユーザー 08月25日 10:37

>>[9]
細かくお調べいただきまして、誠にありがとうございます。
現在、お教え頂きました関数を元に、画面上部と下部のシートを別にした場合
どのように変えればそのまま使用できるか調べているところです。
が、なかなかうまくいかず苦戦しています...

VBAは経験がなく、これを機にVBAについて調べてみはじめたところですが
てんでさっぱり...という状況で、実用にはもう少し時間がかかりそうです...

[22] mixiユーザー 08月26日 03:17

>>[20]
すみません、>9の方法にバグを発見したので別の方法で
考えたほうが良いかと思います
バグは元気があれば取りますが、元々とりあえず組んだものなので
他にもバグがあるかもしれません。。。

もっとも関数であれこれやっている時間を考えれば、VBAを勉強したほうが
他のときにも役立つので、私はそちらのほうが良いと思いますがね

[23] mixiユーザー 08月26日 03:27

と思いましたが、案外簡単に修正出来たので修正版を載せます

C12セル =IF(ISERROR(INDEX($B$3:$G$3,1,SUMPRODUCT(MATCH(0,0/(INDIRECT(ADDRESS(MATCH($C$10,A:A,0),IF(ROW(C12)=12,COLUMN($B$3),MATCH(C11,$B$3:$G$3,0)+2))&":"&ADDRESS(MATCH($C$10,A:A,0),COLUMN($H$3)))),0))+IF(ROW(C12)=12,0,MATCH(C11,$B$3:$G$3,0)))),"",INDEX($B$3:$G$3,1,SUMPRODUCT(MATCH(0,0/(INDIRECT(ADDRESS(MATCH($C$10,A:A,0),IF(ROW(C12)=12,COLUMN($B$3),MATCH(C11,$B$3:$G$3,0)+2))&":"&ADDRESS(MATCH($C$10,A:A,0),COLUMN($H$3)))),0))+IF(ROW(C12)=12,0,MATCH(C11,$B$3:$G$3,0))))

[24] mixiユーザー 08月26日 03:28

>>[20]
>なかなかうまくいかず苦戦しています...
面倒な式になっているので当然、苦戦するでしょうね
どの関数がどのような働きをして、どの分岐がどう作用するのかなど
複雑なものを読み解かなければ、これは改変できないと思います

データ表示位置が変わると参照範囲だけでなく、定数でおいているものも
直す必要が出てきてしまいますし、別シートから参照しようとすれば
間接的に参照範囲を指定している部分でシートも指定する必要が
出てきますので余計に面倒ですね

参考までにSheet1に元表(画像1)、Sheet2に顧客A,Bのデータを表示(画像2)
するように直してみました

C1セル顧客A
C3セル =IF(ISERROR(INDEX(Sheet1!$B$3:$G$3,1,SUMPRODUCT(MATCH(0,0/(INDIRECT("Sheet1!"&ADDRESS(MATCH($C$1,Sheet1!$A:$A,0),IF(ROW(C3)=3,COLUMN(Sheet1!$B$3),MATCH(C2,Sheet1!$B$3:$G$3,0)+2))&":"&ADDRESS(MATCH($C$1,Sheet1!$A:$A,0),COLUMN(Sheet1!$H$3)))),0))+IF(ROW(C3)=3,0,MATCH(C2,Sheet1!$B$3:$G$3,0)))),"",INDEX(Sheet1!$B$3:$G$3,1,SUMPRODUCT(MATCH(0,0/(INDIRECT("Sheet1!"&ADDRESS(MATCH($C$1,Sheet1!$A:$A,0),IF(ROW(C3)=3,COLUMN(Sheet1!$B$3),MATCH(C2,Sheet1!$B$3:$G$3,0)+2))&":"&ADDRESS(MATCH($C$1,Sheet1!$A:$A,0),COLUMN(Sheet1!$H$3)))),0))+IF(ROW(C3)=3,0,MATCH(C2,Sheet1!$B$3:$G$3,0))))
D3セル =IF(C3="","",HLOOKUP(C3,Sheet1!$B$3:$G$6,2))
E3セル =IF(C3="","",HLOOKUP(C3,Sheet1!$B$3:$G$6,MATCH($C$1,Sheet1!$A$3:$A$6,0)))
F3セル =IF(C3="","",D3*E3)
C3:F3をC5:F5までコピー又はオートフィル

C8セル顧客B
C10セル =IF(ISERROR(INDEX(Sheet1!$B$3:$G$3,1,SUMPRODUCT(MATCH(0,0/(INDIRECT("Sheet1!"&ADDRESS(MATCH($C$8,Sheet1!$A:$A,0),IF(ROW(C10)=10,COLUMN(Sheet1!$B$3),MATCH(C9,Sheet1!$B$3:$G$3,0)+2))&":"&ADDRESS(MATCH($C$8,Sheet1!$A:$A,0),COLUMN(Sheet1!$H$3)))),0))+IF(ROW(C10)=10,0,MATCH(C9,Sheet1!$B$3:$G$3,0)))),"",INDEX(Sheet1!$B$3:$G$3,1,SUMPRODUCT(MATCH(0,0/(INDIRECT("Sheet1!"&ADDRESS(MATCH($C$8,Sheet1!$A:$A,0),IF(ROW(C10)=10,COLUMN(Sheet1!$B$3),MATCH(C9,Sheet1!$B$3:$G$3,0)+2))&":"&ADDRESS(MATCH($C$8,Sheet1!$A:$A,0),COLUMN(Sheet1!$H$3)))),0))+IF(ROW(C10)=10,0,MATCH(C9,Sheet1!$B$3:$G$3,0))))
D10セル =IF(C10="","",HLOOKUP(C10,Sheet1!$B$3:$G$6,2))
E10セル =IF(C10="","",HLOOKUP(C10,Sheet1!$B$3:$G$6,MATCH($C$8,Sheet1!$A$3:$A$6,0)))
F10セル =IF(C10="","",D10*E10)
C10:F10をC12:F12までコピー又はオートフィル

[25] mixiユーザー 08月26日 16:13

>>[24]
こんなにもご丁寧にありがとうございます！
感謝しきりです。

実際のシートに反映できるか。細かい修正をできるかどうかを
確認してみたいと思います。

[26] mixiユーザー 08月26日 17:51

>>[25]
>23で解決したのは>22で発見したバグのみなので
他にもバグはあるかもしれません

仕事で実用するには内容を理解し、他にバグがないか、
ご自分で改変できるのかなどなどクリアすべき
項目がたくさんあるように思います

作業列を使用するものなど他の方の回答もありますので
そちらも検討してみることをお勧めします
私のように式をまとめてしまうと、理解することが
難しかったり、時間がかかってしまうことがあります

[27] mixiユーザー 08月27日 23:41

>>みなさま

おかげさまで、無事希望通りの表を完成することができました！
早速、内容の理解に達し、実用しております。

これを機に、VBAの学習を始めました。もっと作業が効率化できるよう、勉強したいと思います。知恵をお貸し頂き、誠にありがとうございました！

[28] mixiユーザー 09月08日 17:14

既に解決済なので蛇足ですが…。

>>[23] マリ男さんの式からヒントを頂いてsumproductを使い(配列数式不要)、無駄なindirectを減らした形に修正してみました。

B12= =SUMPRODUCT(MATCH(1,(INDIRECT("B"&A12&":G"&A12)>0)*(COLUMN(B$3:G$3)-COLUMN(B$3)+1>B11),0))
(後半の「-COLUMN(B$3)+1」を「-1」で代用すると更に短縮)

作業列にB列ではなくI列を使う形も作ってみました。

I11= 0
I12= =SUMPRODUCT(MATCH(1,(INDIRECT("B"&A12&":G"&A12)>0)*(COLUMN(B$3:G$3)-COLUMN(B$3)+1>I11),0))
C12= =IF(ISNA(I12)=1,"",INDEX(B$3:G$3,1,I12))
D12= =IF(C12="","",INDEX(B$4:G$4,1,I12))
E12= =IF(D12="","",INDEX(INDIRECT("B"&A12&":G"&A12),1,I12))
(他は>>[10]と同じ)

ログインすると、残り6件のコメントが見れるよ