エクセルで表を作成（関数教えてください。）

質問

未納品数一覧のデーターが全て手入力なので、どうにかしたと思っています。　自分でもVLOOKUP とか幾つか関数を使った表にチャレンジはしてみたのですが・・・惨敗　思い切って、皆さんに教えてもらおうと思った次第です。
最初からやり直し状態の今、基本の表をどんな形にしたら良いのかから悩んでしまったので、アドバイスいただければと思います。
詳細は添付資料にてご確認いただければ幸いです。

回答No.9

hallo-2007 · ベストアンサー

未納品数量が0以上の行に行番号が表示されていますよね。
表示のシートの式が
=IF(ISERROR(SMALL(受注シート!$G:$G,ROW(A1))),"",INDEX(受注シート!A:A,SMALL(受注シート!$G:$G,ROW(A1)))
となりますが、お解かりでしょうか。
この段階では、受注シートの未納品の一覧表ですので、納品時のインボイスNoは表示されませんよね。
今までの式を応用して、発注IDを入れると、その発注IDに対する納品書のインボイスNo、納品日。数量などが表示される式を考えてみてください。
例えば
表示シート
　A　　　　B　　　　　C　　　　　D　　　　　　　　　E　　　　　F
1　　　発注ID
2　　　09-10
3入荷日・発注ID・invoiceNo.・商品名・数量（入荷数）
4
5
6
7
8受注日・発注ID・商品名・数量（注文数）・納品数量・未納品数量
9　1/1　　09-10 あ　　　　100　　　　　　80　　　　　20
10・・・・
とシートの上のほうにスペースを作って、
B2セルに表示したい発注IDを入れます。（表示シートのB9以下の発注IDを入力規則で設定しては如何でしょうか）
4行目以下に、B２セルでしたいした発注IDに対する一覧表が表示できる式を考えてみてください。
ヒントだけです。
入荷シートA~F：
　A　　　B　　　　C　　　　　D　　　　E　　　　　　　　F　　　　G
入荷日・発注ID・invoiceNo.・商品名・数量（入荷数）・作業列=C2&D2
G列は、=IF(表示シート!B$2=B2,ROW(),"") 下コピィすれば、表示シートのB2セルで指定した発注IDの行にその行の行番号が表示されるはずです。
表示シートのA4に入れる式を考えてみてください。

お礼

今までの式を考えながら、表示シートA4に入るようにやってみます。本当にありがとうございました。

調度昨日品物の入荷があり、大量の情報を別のシートに入力し、先ほどこのシートにコピーしたら、未納数が出せました。（一週間のお仕事が一瞬です）
一部とはいえ、結果が出せて、本当にhallo-2007さんには感謝しています。
ここに費やしていただいた時間を無駄にしないように、頑張ってよいファイルを作り上げますね。
ありがとうございました。

回答No.8

hallo-2007

何度もすみませんね。
入荷シートA~F：
　A　　　B　　　　C　　　　　D　　　　E　　　　　　　　F
入荷日・発注ID・invoiceNo.・商品名・数量（入荷数）・作業列=C2&D2
は
　A　　　B　　　　C　　　　　D　　　　E　　　　　　　　F
入荷日・発注ID・invoiceNo.・商品名・数量（入荷数）・作業列=B2&D2
ですね。
同じ発注IDで複数の商品があるので、それを判別する作業列です。
試しに

未納品数に　=SUMIF(入荷シート!F:F,B2&C2,入荷シート!E:E)　といれて、関数のウィザードで確認してみてください。
発注IDと品名で入荷数が表示されればOKです。

補足

お手数をおかけし、こちらこそ申し訳ありません。
入力したところ、未納品数量に入荷数量が入ったようです。
私的には問題ないので、未納品数量E⇒入荷数、F＝未納品数量D－E　G==IF(F2>0,ROW(),"")としてみたところ、きれいに入りました。
これで、他のシートに問題がしょうじますか？

あと、入荷シートの作業列FにはCのinvoiceNo.が抜けてしまいましたが、大丈夫でしょうか？

回答No.7

hallo-2007

発注シートA~F：
　A　　　B　　　　C　　　　D　　　　　　　　E
受注日・発注ID・商品名・数量（注文数）・未納品数量

入荷シートA~F：
　A　　　B　　　　C　　　　　D　　　　E　　　　　　　　F
入荷日・発注ID・invoiceNo.・商品名・数量（入荷数）・作業列=C2&D2
とわかりやすくしました。

未納品数は　=D2-SUMIF(入荷シート!F:F,B2&C2,入荷シート!E:E)　でしたね。
SUMIF関数のHelpを参照してみてください。入荷シートの発注IDと品名が同じ行の数量の合計をだしますので、これが入荷済み数量になると思います。
1枚の発注IDに対して複数の商品発注があるので作業列にもSUMIF検索条件にも　発注IDと商品名を使うといったトリックです。
発注数 D2からその合計を引くと未入荷数量（発注残）です。
未入荷数量の隣　F列に　=IF(E2>0,ROW(),"")は未入荷数量が0以上の場合に　行番号がでればokです。この番号が表示したいデータのはずです。
表示シートの=INDEX(受注シート!A:A,SMALL(受注シート!$F:$F,ROW(A1)))を分解してみます
　A2セルに　=ROW(A1) 下へコピィすると　1,2,3・・・
　B2セルに　=SMALL(受注シート!$F:$F,A2) 下へコピィすると　表示したい発注シートの行番号が順番に表示されると思います。
　C2セルに　=INDEX(受注シート!A:A,B2)で発注日
　D2セルに　=INDEX(受注シート!B:B,B2)で発注ID　となります。
それをまとめると上記の式になります。

補足

ありがとうございました。
発注シートE未納品数について、再度確認させてください。
発注シートD＝３０　入荷シートE＝１０と入力した場合、現在発注シートE＝30になります。私的には30-10の計算がされた２０が表示されるのでは？と思うのですが。違いますか？
発注シートEの式は=D2-SUMIF(入荷シート!F:F,B2&C2,入荷シート!E:E)となっています。

表示シートの説明は解りやすかったので、そのままA~Dに入力しました。ありがとうございました。
しかし、受注シートができてい為、A以外はNUM状態です。
受注シート（検索シート？）には、どの行に何を入れればよろしいでしょうか？

回答No.6

hallo-2007

すみません。また訂正です。
F列に　=IF(E2>0,ROW(),"")　といれて下コピィします。
意味はE2セル（未納品数量）が0以上の場合　その行の番号（ROW()）0の場合は　空白
ですね。
表示シートの式　=INDEX(受注シート!A:A,SMALL(受注シート!$F:$F,ROW(A1)))
は理解できましたでしょうか。SMALL(受注シート!$F:$F,ROW(A1))
は作成した作業列（F列）の数値の　1番小さい値から順に2番、3番目となります。
参考までに下へコピィしたときのエラー処理いれると
=IF(ISERROR(SMALL(受注シート!$F:$F,ROW(A1))),"",INDEX(受注シート!A:A,SMALL(受注シート!$F:$F,ROW(A1)))
でコピィしてみてください。

補足

再度、ご指導ありがとうございます。
現在シートは2種類まで作りましたが…(*_*;

発注シートA~F：受注日・発注ID・商品名・数量（注文数）・未納品数量=D2-SUMIF(入荷シート!B:B,B2,入荷シート!D:D)・未納品０以上=IF(E2>0,ROW(),"")

入荷シートA~F：入荷日・発注ID・invoiceNo.・商品名・数量（入荷数）・作業列=C2&D2　
です。

発注シートの未納品数量には未納品数ではなく注文数が表示されているようですが、未納数はどこに表示されるのでしょうか？

表示シート=INDEX(受注シート!A:A,SMALL(受注シート!$F:$F,ROW(A1)))＆受注シートは必要理由と記入方法が理解できていません。

＞SMALL(受注シート!$F:$F,ROW(A1))
は作成した作業列（F列）の数値の　1番小さい値から順に2番、3番目となります。
参考までに下へコピィしたときのエラー処理いれると
=IF(ISERROR(SMALL(受注シート!$F:$F,ROW(A1))),"",INDEX(受注シート!A:A,SMALL(受注シート!$F:$F,ROW(A1)))
でコピィしてみてください。

とりあえず、シートを作って各々式のみA２に入れてみましたが、
表示シートA2＝=INDEX(受注シート!A:A,SMALL(受注シート!$F:$F,ROW(A1)))は＃NUM!
受注シートA1未納品数量：A2=D2-SUMIF(入荷シート!F:F,B2&C2,入荷シート!D:D)は当然ながら０表示です。

受注シート＆表示シートの詳細もご指導いただけるとありがたいです。

これから出社ですので、内容は夜以降の確認となってしまいますが、
お時間の許すときにご指導よろしくお願いいたします。

回答No.5

hallo-2007

失礼しました。
F列に　=IF(D2>0,ROW(),"")　といれて下コピィします。
意味はD2セル（未納品数量）が0以上の場合　その行の番号（ROW()）0の場合は　空白

ついでに、1枚の発注IDで複数の商品発注の場合
入荷のシートを
A　　　B　　　　C　　　　　D　　　E　　　F
入荷日　発注ID　納品書番号　品名　数量　作業列
2009/1/20 2009-01　XXXXXX　　あ　　50　XXXXXあ
2009/1/21 2009-01　XXXXXY　　あ　　50　XXXXYあ
とF列に=C2&D2 下コピィしておきます。
受注シートの
未納品数量は　=D2-SUMIF(入荷シート!F:F,B2&C2,入荷シート!D:D)　下へコピィ
とすれば、発注IDと品名で合致した入荷数量の合計がえられるはずです。
このシート構成のメリットには、発注の履歴、入荷の履歴が残りことにあります。
入荷のデータがあるのであれば、自動で入荷シートの最後の行にデータを貼り付けることはマクロで可能です。

お礼

本当に何から何まで、ありがとうございました。
発注＆入荷シートは数値が入りました。（1列だけですが、（笑））でも万歳！感激です。
これをしっかり理解すれば、大量の数字の手入力と蛍光ペンだらけの資料と格闘する担当者のヘルプをせずにすみそうです。
受注シートがまだ理解できてはいませんが、ゆっくりやってみます。

赤子のような相談に、このようにご親切なわかりやすい解説をいただき、本当にありがとうございました。
この齢からの独学はわからないことばかりですが、少しづつ勉強して行こうと思えるようになりました。

御迷惑でしょうが、今後とも、どうかよろしくご指導ください。
長時間、ありがとうございました。

回答No.4

hallo-2007

試しに
発注シート
　A　　　　B　　　　C　　　D　　　E　　　　F
発注日　　発注ID　 品名　数量　未納品数量
2009/1/1　2009-01　あ　　100
・・・
未納品数量は　=D2-SUMIF(入荷シート!B:B,B2,入荷シート!D:D)　下へコピィ
更にF列に　=IF(D>0,ROW(),"")　といれて下コピィします。
未納品数量が　0　以外にその行番号がでます。
入荷シート
　A　　　B　　　　C　　　　　D　　　E
入荷日　発注ID　納品書番号　品名　数量
2009/1/20 2009-01 XXXXXX　　　あ　　50
2009/1/21 2009-01　XXXXXY　　　あ 50
・・・とサンプルでも作成してみてください。
表示シート
=INDEX(受注シート!A:A,SMALL(受注シート!$F:$F,ROW(A1)))
右へコピィ、下へコピィしてください
未納品の一覧表（未納品数量が0以上の一覧表）になります。
一枚の発注IDに複数の商品であれば、作業列をもう一行入れて考えると
わかりやすいと思います。
取合えず、こんなやり方も有ります。
多分、こちらの方が日々の入力（受注シート、入荷シート）もやりやすいと思います。

補足

教えていただいているようにまず受注シートを記入しているのですが、一つ質問です。

発注シートのA=発注日,B=発注ID　 C=品名　D=数量　E=未納品数=D2-SUMIF(入荷シート!B:B,B2,入荷シート!D:D)　F列に　=IF(D>0,ROW(),"")　と入力するとエラーメッセージ＃NAME　無効な名前のエラーとなってしまいます。　D＝の認識が間違っていますか？

回答No.3

hallo-2007

質問者の方が発注者で、発注して入荷するまでの未納品を管理したいという事ですよね。
一枚のシートで処理するのではなく、
発注シート
発注日　発注ID(インボイスNo)　品名　数量　未納品数量
といった具合で、発注が発生した時に下方向へ入力
入荷シート
入荷日　発注ID　品名　数量
入荷が発生した時に下方向へ入力
仮に発注IDに対して1品名であれば、発注シートの未納品数量の列は
=D2-SUMIF(入荷シート!B:B,B2,入荷シート!D:D)
で計算できると思います。（1枚の発注IDに対して複数の品名を発注する時は別の関数で対応します）
後は、オートフィルターで未納品数量が 0以上を表示すれば管理が出来ると思います。
システム的に考えるのであれば、別途　品名と品名IDのリストを作成するシートを準備
（品名の入力ミスを防ぐ為とかに）
未納品或いは、日にちを入れるとその日の入荷リストとか表示するシートを準備する（検索表示のシート）
とか準備すればわかりやすいシートになると思います。
データを入力するシートとご希望の表を表示するシートと分けて考えてみてください。
システムを考えるのであれば
１、入力作業(この場合、発注と入荷）
２、検索作業（この場合、発注残がある発注データの一覧）
３、ほかでは、印刷（発注書の印刷などと発展させます）

補足

有難うございました。
＞質問者の方が発注者で、発注して入荷するまでの未納品を管理したいという事ですよね。＝そのとおりです。

残念ながら、発注IDに対して複数品名でです。発注シートの未納品数量の列はどのような関数になりますか？発注ID＝invoiceNo.にはなりません。（入荷時ごとにinvoiceNo.は違い、発注IDは2009年の1回目（2009-1）のように発注した時に作ります。）

検索表示シート興味あります。
私はお解かりのとおりPC初心者なのですが、頑張りますので
もう少し詳しく教えて頂けますか？

回答No.2

web2525

解説読んだらもっと分からなりました

＞黄色E～Nは日本から注文する際のオーダーNo.に対する未納数
CODE＝注文IDになるのかな?（発注書No)
列E～列Nまでの注文数ですが、これは列ごとに品物が違うのですか?
未納数が合計されているところをみると、すべて同一の物とも受け取れるのですがどちらでしょう。

異なる品物の未納数を合計する事もおかしいと思うし、一度の発注で同一の品物を発注するに分散して発注をかける必要があると言う事も理解できません。

入荷に関しても、No7の場合13+64=77の発注に対して入荷が50と言うのも理解しづらい、受注生産か何かで受注数に関わらず一定のロットごとにしか出荷しないという事でしょうか?

最大の疑問が、列E～列Nまでの品物が同一の場合、入荷した商品がどの発注に対するものかをどうやって判断しているのでしょうか?
No8にもNo7と同じ列に未納数が表示されていてその合計は入荷した50よりも少ないのにNo8に対する入荷としないところをみると、発注順とも取れますが、それであれば上位の発注の入荷としないのはなぜか?
品物が違うからと言う事だと、上記の品物が違う未納の合計をなぜ行うかの疑問が。

状況が特殊すぎるので、発注・入荷のシステムが理解できる人でないと全体を見通したシステム作成は難しいと思います。

ご自身である程度まとめて、部分的な質問を行う形式にされた方がよいかと思います。

補足

ご親切に何度も、ありがとうございました。
では、シート１に全商品4100種類（行A）商品コード(行B）注文ID毎に注文数（行C～M）に数が入力し、入荷した時点で、行AAに全商品4100種類　行ABに商品コード　行AC～AMに注文ID毎に入荷数を入れた場合：　シート２（未納品数一覧）に全商品4100種類（行A）商品コード(行B）全商品の未納数（注文ID毎に（C~M））になるようにするにはどのような式を作ればよいでしょうか？　
できれば、シート２の行AAに該当商品　行ABに商品コード　AC～AMには未納数の存在する物のみを抽出したいのですが、いかがでしょうか？

いただいた疑問にお答えしておいた方が少しはわかりやすかも知れません。本来ならば、発注数に対して入荷がありそれに対しての未納となるのですが、担当者が、未納品だけの管理をしている状態の表です。
全商品4100種類（行A）商品コード(行B）黄色E~Nの例えば2008－15は事務上2008年の15番目の注文2009－1は今年1回目の注文となっておりますそれがこちらの注文IDとなっています。（注文IDなのですが数は未納数です。注文数はどこにも表示されていません。）総合計は列E～列Nまでの各列ごと同一品物となります。（次回注文の際トータル発注残数を知る為に必要とのこと）
＞最大の疑問が、列E～列Nまでの品物が同一の場合、入荷した商品がどの発注に対するものかをどうやって判断しているのでしょうか?
先方から送られてくるinvoiceに記載されている注文IDより入荷数を各品ごとにチェックしています。（同じ品物の場合：注文IDごとにinvoiceが異なります。⇒入荷後はinvoiceNo.で整理することも可能では？とも思うのですが…まずは一歩から）
＞発注順とも取れますが、それであれば上位の発注の入荷としないのはなぜか?
基本発注順になるはずなのですが、先方がなぜか後からの注文を先に出してきたりするので、あくまでも注文IDごとに処理し、未納品に関しては、注文ID○ー○の商品コード＃＃＃＃がまだ来ていない！とするらしいです。

よろしくお願いいたします。

回答No.1

web2525

図中の文字が細かすぎて判別できません、
文章で説明を補足してください。

補足

申し訳ありません。以下表示内容です。
現在の【手作業処理表】は行A～R列1（No.）～15（No.14）表です。行Aは4100種類,Bは基本最大19文字のコード,黄色E～Nは日本から注文する際のオーダーNo.と未納数,行O＝E～Mの未納合計数（行P現在注文数-Q入荷したら手入力＝Rで、品物が入荷されたらRの数字のみ残して削除）します。
入荷時は、例えばNo.7青文字、invoiceNo.（例 0880120　2351）A01W2929087019CI19MA01　50本⇒未納63本アルので後13本残+前の残14も残ります。　注文No.2008-20分,No.8青文字invoiceNo.（例 0880120　3210）A01W2929087019HO19MA01 3本　注文No.2008-11分,No.11青文字invoiceNo.（例 090003　4152）A01W2929087034CI17M　17本　注文No.なし又は2008-13＆2009-2となります。
【作成したい表内容】　
1.注文ID未納品に対していつ何本入荷し、そのinvoiceNo.は何か？
2.注文IDは自動に増え続けられるようにCODEは増えたり減ったりしても対応可能に
3.未納品の有るもののみ表示（別シート）（全て表示も必要なので）
4.入荷数を自動書き込み出来れば、なお可（入荷内容はPDFでinvoiceが届くのですが、CODEと本数＆invoiceNo.を別のエクセルに変換することは出来ます。）
解りにくく申し訳ありまえんが、宜しくお願いします。