EXCELで売上データから請求明細書を作成したい

質問

過去の質問を検索しましたが、解決できなかったので質問させていただきます。

現在EXCEL2007で、お客様との取引を、VBAで作ったフォームに入力するとデータシートに書き込まれるようにしています。
データの内容は、売上日付、得意先、運搬元、運搬先、車種、ナンバー、金額、備考・・・等の計19項目で、それが1レコードとなります。売上明細です。

そこから、得意先名・日付の期間指定でデータを絞り込み、そのデータの何項目かを別シートに作ってある請求明細書の各項目欄に反映させて、それを請求明細として印刷したいのですが・・・

ちょっとした売上ソフトのようになってしまうのですが、こういったことはEXCELで可能なのでしょうか？VBAはかじった程度で知識が浅く、可能か不可能かも判断できません。
可能だとしたら、どのような手順を踏めばよいのでしょう、どなたかお知恵を貸していただけないでしょうか、よろしくお願いします。

回答No.4

MackyNo1 · ベストアンサー

>抽出したデータが“車種”であれば、請求明細シートの車種の列全体を選択して設定するということでよいのでしょうか。

車種が元データ（Sheet1)のE列にあるなら、INDEX関数の範囲を「Sheet1!E:E」にしてください。

>また、いろいろ調べてみたのですが、知識不足でSMALL以降の式の区切れが今一つ理解できませんでした。
最後の　…ROW($A$2:$A$1000),1000),ROW(A1)))　のあたりと、SMALLが入ってくる理由が分からないのですが、できましたらその点についてさらにお教えいただけませんでしょうか。

SMALL(IF((Sheet1!$B$2:$B$1000=$A$2)*(Sheet1!$A$2:$A$1000>=$B$2)*(Sheet1!$A$2:$A$1000<=$C$2),ROW($A$2:$A$1000),1000),ROW(A1))

IF関数部分は、(Sheet1!$B$2:$B$1000=$A$2)*(Sheet1!$A$2:$A$1000>=$B$2)*(Sheet1!$A$2:$A$1000<=$C$2)が成立する（3条件とも成り立ち１になる）場合は、その行数を、成立しない場合は1000（行範囲よりも大きい数）の配列を返しています。
この配列の中で小さい順に１から順にその行数を求めています（ROW（A1)は１になります）。

この数字がそれぞれ3つの条件が合致する行数ですので、INDEX関数でその値を求めています。
この時、該当データがない場合は1000行目のデータを参照しますので、「0」と表示されことになります。

表示したいデータが車種のように文字列データの場合は、数式の最後に「&""」を追加するのが簡単な対応です。

数字データや日付データなどを引っ張ってきたいときは、上記の「&""」の追加では対応できないので、シートのオプションでゼロ値を表示しない設定などの対応をする必要がありますが、配列数式を多用すると、シートの動きが重くなるので、たとえばIF関数などで以下のような数式にするのがお勧めです。

=IF(車種のセル="","",元の配列数式）

お礼

ずいぶんと時間が経ってしまい失礼しました。結局、今回はVBAで何とか作成することができたのですが、配列数式のことを学ぶことができ、とても参考になりました。
どうもありがとうございました。

回答No.3

MackyNo1

このケースならVBAで対応するよりも、関数だけで処理するほうが簡単かもしれません。

元データがSheet1で、日付がA列、得意先がB列にあり、請求書シートのA2セルに抽出したい得意先名、B2セルに開始日、C2セルに終了日が入っている場合、条件に該当するC列のデータを抽出したいなら以下の式を入力して、Ctrl＋Shift+Enterで確定して配列数式にして、下方向にオートフィルします。

=INDEX(Sheet1!C:C,SMALL(IF((Sheet1!$B$2:$B$1000=$A$2)*(Sheet1!$A$2:$A$1000>=$B$2)*(Sheet1!$A$2:$A$1000<=$C$2),ROW($A$2:$A$1000),1000),ROW(A1)))

上記のl数式では、該当データがないと「0」と表示されますので、この「0」を非表示にするには、Excelのオプションで「ゼロ値のセルにゼロを表示する」のチェックを外してください。

なお、抽出する値が文字列の場合は、数式の最後に「&""」を付けることで対応することもできます。

お礼

ご回答ありがとうございます！
これなら解決できそうと思い、試行錯誤してみましたが、配列数式はあまりなじみがなくうまくいきませんでした。

基本的なことで申し訳ないのですが、この式を設定する範囲なのですが・・・
抽出したデータが“車種”であれば、請求明細シートの車種の列全体を選択して設定するということで
よいのでしょうか。

また、いろいろ調べてみたのですが、知識不足でSMALL以降の式の区切れが今一つ理解できませんでした。
最後の　…ROW($A$2:$A$1000),1000),ROW(A1)))　のあたりと、SMALLが入ってくる理由が分からないのですが、できましたらその点についてさらにお教えいただけませんでしょうか。

面倒なお願いで申し訳ないですが、どうかよろしくお願いいたします。

回答No.2

KURUMITO

例えば元となる表がシート１に有ってA１セルに売上日付、B１セルに得意先、C１セルに運搬先などの項目名が右横に入力されてT１セルまでにあるとします。
また、データを抽出したい得意先名はシート２のA１セルに、期間の初めはB１セルに、期間の終わりはC１セルに入力してシート２にそれらの希望に沿ったデータを表示させることにします。
そこでシート１には作業列としてU列を使用しU２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(AND(B2=Sheet2!$A$1,A2>=Sheet2!$B$1,A2<=Sheet2!$C$1),MAX(U$1:U1)+1,"")

その後に例えばシート２の３行目にはシート１の１行目で使われた項目名で請求書を作成するうえで必要な項目名をA３セルから右横方向に並べて入力します。

シート２のA４セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(OR(ROW(A1)>MAX(Sheet1!$U:$U),COUNTIF(Sheet1!$1:$1,A$3)=0),"",INDEX(Sheet1!$A:$T,MATCH(ROW(A1),Sheet1!$U:$U,0),MATCH(A$3,Sheet1!$1:$1,0)))

これで請求書を作るのに必要な項目が選択した得意先と期間についてシート２に表示されますので、例えばシート３には請求書のスタイルを作ったうえでシート２からのデータを直接表示させるようにすればよいでしょう。

これら一連のことが完成していればシート２の１行目の条件を変えることで新しい得意先の請求書もできることになりますね。参考になりましたら幸いです。

お礼

お礼が遅くなりましたが、どうもありがとうございました。
結局、VBAで何とか作成することができました。

回答No.1

riri1609

1例です。19項目のデータは、横1列に並んでいるものとします。

データシートの19項目の前（一番左側）に、ID/No.を入れます。
あとはそのIDを入力するところをつくり、VLOOKUPで反映させれば、一応のことは出来ます。

私は領収書や封筒の宛名印刷、送付案内の宛名等で同様の方法を用いております。

お礼

早々にありがとうございます。なるほど、VLOOKUPを使うという方法もありますね。
ありがとうございます。