エクセルで納品書作成

質問

エクセルで納品書を作成しています。

まず、『Sheet1』に受注一覧表があります。
　　　Ａ　　　　Ｂ　　　　　Ｃ　　　　　 Ｄ　　　 Ｅ　　　Ｆ
１　受注ID　　　顧客　　　　品名　　　　単価　　　数量　　合計
２　100100　　あい商店　　　みかん　　　150　　　　4　　　600
３　100100　　あい商店　　　りんご　　　150　　　　2　　　300
４　100100　　あい商店　　　ばなな　　　200　　　　1　　　200
５　100101　　(株)うえ　　　　みかん　　　150　　　　4　　　600
３　100101　　(株)うえ　　　　りんご　　　150　　　　2　　　300
４　100101　　(株)うえ　　　　ばなな　　　200　　　　1　　　200

次に、、『Sheet2』に納品書があります。
セルＡ１に受注IDを『100100』と入力すると、
自動的に、

　　　Ａ　　　　Ｂ　　　　Ｃ　　　Ｄ　　
５　品名　　　　単価　　　数量　　合計
６　みかん　　　150　　　　4　　　600
７　りんご　　　150　　　　2　　　300
８　ばなな　　　200　　　　1　　　200

と表示されるようにしたいのですが・・・。
セルＡ６～Ａ８に
=IF(ISBLANK($A$1),"",VLOOKUP($A$1,Sheet1!$A$1:$F$9997,3))
セルＢ６～Ｂ８に
=IF(ISBLANK($A$1),"",VLOOKUP($A$1,Sheet1!$A$1:$F$9997,4))
セルＣ６～Ｃ８に
=IF(ISBLANK($A$1),"",VLOOKUP($A$1,Sheet1!$A$1:$F$9997,5))

入力したのですが、↓↓こうなっちゃいましたo(;△;)o

　　　Ａ　　　　Ｂ　　　　Ｃ　　　Ｄ　　
５　品名　　　　単価　　　数量　　合計
６　りんご　　　150　　　　2　　　300
７　りんご　　　150　　　　2　　　300
８　りんご　　　150　　　　2　　　300

至急、助けてください(/_<。)

回答No.7

hallo-2007 · ベストアンサー

エラー処理の案です。
１、ISERROR,ISNA関数などがあります(IS関数でHELPで調べてください)
　　この場合　=IF(ISERROR(数式),"",数式)
で良いと思いますが、以下に説明しておきますが、重くなる傾向あります。
２、条件付書式で処理する方法
　　C25~F42を選択して「書式」「条件付書式」で、左側の「セルの値」を「数式」に変更、=ISERROR(C25)と入力
書式のボタンで、文字色に、白を設定
この方法では、重くはなりませんが、金額の合計などで、関数にトリック必要でしょう。

以下、１の方法の補足説明です。
=5*A1 パソコンでは楽々です。
=ROUND(A1/B1)*C1 変数が増えていますが、まだ楽勝です。
=SMALL(受注一覧!$Z$5:$Z$9999,1) 範囲が指定する関数は少し重いです。
=INDEX(受注一覧!$K$1:$N$9999　範囲が広くなれば、更に重いです。
この様な、関数が、沢山あれば、更に重くないます。
=IF(ISERROR(数式),"",数式)の場合、同じ数式が2度、あります。

回避案
納品書のシートのA25に
=SMALL(受注一覧!$Z$5:$Z$9999,ROW（A1))
A42まで、下フィル（SMALL関数の部分だけ、作業列にします）
C25には
=IF(ISERROR(A25),"",INDEX(受注一覧!K1:K9999,A25,1)))
で42まで下フィル
F25には
=IF(C25="","",INDEX(受注一覧!L1:L9999,A25,1)))
右フィル、下フィルでいかがでしょうか。
IF文の違いと、範囲の列の違い確認してください。

=IF(ISERROR(数式),"",数式)の場合、１つのセルに、広範囲を指定するINDEX関数が2個、SMALL関数が2個、それが横方向に4個必要(合計16回）ですが、
この方法ですと、1列に、SMALL関数　1個、INDEX関数　4個ですみます。
今のパソコンは高性能なので、気にならないかもしれませんが、必ず、動作が遅くなっています。
上記の理屈、覚えておくと損はしないと思います。
以下に、同じようなサイトがありましたので紹介しておきます。
他、蛇足ですが、
納品書の23行目までには、納品日、取引先会社名、住所など、自社の社名、住所など、納品書番号などあると思いますが、これも、入力規則と
VLOOKUP関数で、自動化すれば、ミスも少なく、効率の良いファイルが出来ると思います。
受注IDの規則性があれば、自動で決める方法もあるでしょう。
がんばってください。

参考URL：

http://www2.moug.net/bbs/exopr/20071014000008.htm

回答No.9

hallo-2007

新しいスレで質問されていますね。
そちらに回答しておきます。
マルチポストに当たりますので、こちらのスレは閉じてください。
また、参考になった回答には、投票しておいてください。
他の方が、検索されるときの参考になります。

回答No.8

hallo-2007

忘れてました。
納品書のA列に数字が表示されたままですね。
A25~A42選択して、書式で、文字色を白にするか
A列を非表示にしてください。

この手法ですが、納品書以外にも、依頼書、請求書、発注書、連絡書など
会社の中では、結構、使えると思います。
理解されたら、他の人にも紹介してあげてください。

お礼

ありがとうございます。
大変(^人^)感謝♪感激です♪

＞納品書の23行目までには、納品日、取引先会社名、住所など、自社の社名、住所など、納品書番号などあると思いますが、これも、入力規則と
VLOOKUP関数で、自動化すれば、ミスも少なく、効率の良いファイルが出来ると思います。

これもバッチリです♪
残るは・・・・・(^▽^;)

シート受注一覧表の列に
『締月』と『支払月』があります。
日にちまでは必要ないので、
せめて月のみ表示させたく
これも関数でどうにかできないかと・・・。
もし、ご教授いただければ幸いです。

以下、よろしくおねがいいたします。

　　　Ｃ　　　　　　　　Ｓ　　　　　　Ｔ　　　　　　　 Ｕ　　　　　　Ｖ
4　　受注日　　　　締日　　　支払日　　 　締月　　　支払月
5　　11/12　　　　 5日 翌月末日
6　　11/12　　　　 10日　　翌々月末日
7　　11/12　　　　 15日　　翌々月5日
8　　11/12　　　　 20日　　翌々月10日
9　　11/12　　　　 25日　　 翌月20日
10　　11/12　　　 末日　　 翌月15日
11　　11/12　　　 不明　　　不明　　　　　不明　　　不明

『Ｃ列（受注日）』は手入力
『Ｓ列（締日）』『Ｔ列（支払日）』には
シート名『顧客マスター』よりVLOOKUP関数が入っております。
『Ｕ列（締月）』と『Ｖ列（支払月）』の関数が知りたいのですが、
締日は上記のとおり、7通りあります。

※『Ｓ列（締日）』『Ｔ列（支払日）』『不明』表示ならば
　『Ｕ列（締月）』と『Ｖ列（支払月）』も『不明』表示で。。。

『Ｖ列（支払月）』の関数を出すには、
やはり、『Ｔ列（支払日）』を
月と日の列に分けた列を追加しなければならないのでしょうか？
（例）翌々月末日なら　

　　　　　　　Ｔ　　　　　　　　Ｕ　　　　　 Ｖ
　　　｜　翌々月末日　｜　2　｜　30　｜　　でしたっけ？

以上、宜しくお願いいたします。

回答No.6

hallo-2007

すみません、先ほど回答した内容ですが、
納品書の印刷に必要なのは　受注IDだけですね
作業列は
=IF((A1=Sheet2!$A$1),ROW(),"")
だけで、十分ですね。ただ、一般的には、
納品書のシートに　TODAY()関数を入れて、本日の日付を表示させ
データのシートには、納品日を入れて
=IF(AND((A2=Sheet2!$A$1),(「納品日の列」=Sheet2!「TODAY()入れたセル」)),ROW(),"")
で、抽出する方が便利かと思います。
受注IDが、受注時に決定されて、納品が別々の日になっても対応できます。

補足

ありがとうございます。
あと一歩なので、再度ご教授お願いいたします。

シート名『受注一覧』に
　　　 K L M N
4　品名コード　　品名　 　単価　　数量
5 100 りんご　 150 2
6 200 ばなな　　200　　　3
7　　 300　　　 みかん 250 4

として、
受注IDを、納品書!$O$2に入力しています。
そして、
作業セルをＺ列とし
セルZ5に
=IF(($B5=納品書!$O$2),ROW(),"")
と入力し、下フィルしています。

シート名『納品書』のレイアウトは、
下記の通りです。

　　　　　C　　　　　 F Z AE
24　　品名コード　 品名　　　　単価　　　　数量
25
26
27
28
・
・
42

よって、
C25には
INDEX(受注一覧!$K$1:$N$9999,SMALL(受注一覧!$Z$5:$Z$9999,1),1))
F25には
INDEX(受注一覧!$K$1:$N$9999,SMALL(受注一覧!$Z$5:$Z$9999,1),2))
C26には
INDEX(受注一覧!$K$1:$N$9000,SMALL(受注一覧!$Z$5:$Z$9999,2),1))
と入力し、正しく抽出することができました。
しかし、問題なのは、
今回は3行のみの抽出なので
28行以下の行が、『#NUM!』と表示されてしまうのです。
上記関数（INDEX(受注一覧!$K$1:$N$9000,SMALL(受注一覧!$Z$5:$Z$9999,2),1))
を含めたIF文を教えていただけないでしょうか。
宜しくお願いいたしますｍ(＿ ＿;)ｍ

回答No.5

hallo-2007

すみません、間違いがありました。
＞　Sheet1　H列に
=IF(AND((A2=Sheet2!$A$1),(B2=Sheet2!$B$1)),ROW(),"")
ですね、Sheet2で指定した、ID、顧客名が同じ場合にそのセルの行番号を表示することを確認してください。
条件が一致しない行は、全て空白です。
＞　Sheet2の品名の部分(A5)は
＞　=INDEX(Sheet1!C:E,SMALL(Sheet1!H,ROW(H1),COLUMN(A5))
=INDEX(Sheet1!C:E,SMALL(Sheet1!Z,ROW(Z1),COLUMN(A5))
でかまいません。
>ROW(H1)のＨは、どのシートのどの列の意味ですか？
ただ下方向にフィルした場合に、1,2,3・・・と増やしたいだけです。
ROW(A1)、ROW(B1)でもかまいません。
>COLUMN(A5))のAは、どのシートのどの列の意味ですか？
ROWと同じです、右フィルした場合に、1,2,3・・・・と増やしたいだけです。
CLOUMN(A2)、COLUMN(A3)でもかまいません。
ROW、COLUMN関数使わなければ、1,2,3と順番に数字入れてもらってもかまいません。
IF文で、必要な行を抽出する。
フィルした場合に、1,2,3とするためにROW,COLUMN関数を利用しました。

回答No.4

hallo-2007

作業列案です。
Sheet2のA1に100100　　B1にあい商店　入力したとします。
（A1,B1セルには、リスト作成しておいて、入力規則設定しておくと
　便利でしょう）
Sheet1　H列に
=IF(AND((A2=Sheet1!$A$1),(B2=Sheet1!$B$1)),ROW(),"")
下フィルで、納品書に表示したいデータ行のみ、その行数を表示させます。
Sheet2の品名の部分(A5)は
=INDEX(Sheet1!C:E,SMALL(Sheet1!H,ROW(H1),COLUMN(A5))
C5まで、右フィル、下フィルする。
ROW(H1)は、単に下フィルした時に
1,2,3・・・と順に数字を上げるためです。
CLOUMN(A5)も同様に、右フィルした時に、
1,2,3とするためです。
上記式の内容は、データのシートのC~E列を指定
指定した範囲で、表示する行数は、H列で、1番小さい数字
下フィルしているので、下の行には、2番目に小さい数字
と順になります。
表示する列は、1列目ですが、右フィルした順に、2列目3列目
が表示されると思います。
SMALL関数は、必要なデータを上詰で表示させるため、
ROW,COLUMN関数は、フィルした時に、作業を簡単にするためです。

お礼

ご教授ありがとうございます。
もうしわけありません。
質問させてください。

＞　Sheet1　H列に
＞　=IF(AND((A2=Sheet1!$A$1),(B2=Sheet1!$B$1)),ROW(),"")

を入力した場合、セルには何か数字が表示されるのでしょうか？
上記を入力し下フィルしても、どのセルも何も表示されないのです。

あと、
＞　Sheet2の品名の部分(A5)は
＞　=INDEX(Sheet1!C:E,SMALL(Sheet1!H,ROW(H1),COLUMN(A5))

ですが、
＞　=IF(AND((A2=Sheet1!$A$1),(B2=Sheet1!$B$1)),ROW(),"")
この関数を、Z列に入力したばあいは、
=INDEX(Sheet1!C:E,SMALL(Sheet1!Z,ROW(Z1),COLUMN(A5))
でよろしいのでしょうか？
ROW(H1)のＨは、どのシートのどの列の意味ですか？
COLUMN(A5))のAは、どのシートのどの列の意味ですか？
再度、ご教授できましたら、よろしくお願いいたします。

回答No.3

imogasi

これはVLOOKUPが該当の２番目以後を捉えることができないという欠点
（特徴）をわきまえない失敗作です。
VLOOKUPは使えません。
あえてやるなら、私の自称imogasi方式です。
作業列を使いますが、VBAでもできなければ、辛抱しないと仕方がない。
ーー
まずSheet2のどこかに、受注IDを入れるセルを決めます。
Sheet2!A１とします。
Sheet1の空き列を作業列としH列とします。
ｈ１に
=IF(A2=Sheet2!$A$1,MAX($H$1:H1)+1,"")
と入れて最終行まで式を複写します。
例データと結果
A1:H7
受注ID 顧客 品名 単価 数量 合計
100100 あい商店 みかん 150 4 600
100100 あい商店 りんご 150 2 300
100100 あい商店 ばなな 200 1 200
100101 (株)うえ みかん 150 4 600 1
100101 (株)うえ りんご 150 2 300 2
100101 (株)うえ ばなな 200 1 200 3
ーーー
Sheet2に行って
Sheet2のA2に
=INDEX(Sheet31!$A$1:$F$100,MATCH(ROW()-1,Sheet1!$H$1:$H$100,0),COLUMN())
と入れてF2まで式を複写する。
A2:F2を範囲指定してA4:F４まで＋ハンドルを引っ張る。
結果
100101 (株)うえ みかん 150 4 600
100101 (株)うえ りんご 150 2 300
100101 (株)うえ ばなな 200 1 200
#N/A #N/A #N/A #N/A #N/A #N/A
・・
となる。
#N/Aを出さないようにするには
IF(ROW()-1>MAX(Sheet1!$H$2:$H1$00),"",上記式）
のようにする.
---
まづ私の回答の例でやってみて、じっくりその仕組みを理解し、その後、自分の納品書の場合のレイアウトに適合する為にどこを修正すべきか考えてください。

=INDEX(Sheet31!$A$1:$F$100,MATCH(ROW()-1,Sheet1!$H$1:$H$100,0),COLUMN())
のCOLUMN()の部分は、具体的に項目位置列に従って１から５の
数字を指定することになると思います。
あと自動的に印刷するには、VBAが必要です。

お礼

ご教授ありがとうございます。
出来ました。完璧です。
しかし、自分の納品書の場合のレイアウトに適合させようとすると
出来ないのです。
もう少し、がんばってみようと思います。
また近日に、再度ご教授お願いするかもしれません。
その際は、宜しくお願いいたします。

回答No.2

noname#176215

一時計算用の作業列も使わずに数式だけで抽出しようとするなら配列計算
という方法を使わないとできません。配列計算や論理演算が分からない状
態で手を出すべきではないと思います。それにデータ範囲を見る限り処理
が重くて使い物にならないでしょうし。

私もフィルタ機能で充分だと思いますが「自動で抽出」に拘るならパラメ
ータクエリを使う方法もあります。

Excel豆知識41:エクセル:外部データの取り込み
（パラメータークエリの利用）:条件によるデータの抽出
http://www11.plala.or.jp/koma_Excel/contents6/mame6041/mame604101.html

参考URL：

http://www.geocities.jp/chiquilin_site/data/060322_extraction.html

お礼

早々のご回答ありがとうございます。
とても勉強になりました。
少し工夫して使用してみようと思いますＯ(≧▽≦)Ｏ

回答No.1

redowl

A 列の　　100100　に該当する項目が複数行有るから、質問中の数式では表示出来ません。
数式を使うよりも、オートフィルターを使うのが　効率的では？
オートフィルターの説明　　http://excel.100page.net/etc/kowaza3.html

お礼

早々のご回答(^人^)感謝♪いたします(o_ _)ｏ))
やはり、オートフィルターで
納品書シートにコピペですよねぇ～
ＰＣ初心者が使用するため
少しでも作業が少なくなるようにとの指示で作成中なのです(/_<。)
関数があれば、受注IDを入力すれば
すぐに、印刷ボタンをクリックして納品書発行！！！
としたいのですが・・・。断念かな～