『エクセルの関数に詳しいお方、どうかお助け下さい。』（qjcyp880） 現在、在庫表をメンイとし、入荷表、販売表、出荷表があります。 入荷した商品情報を入荷表に記載すると在庫表に在庫として反映され、 販売した商品情報を販売表に記載すると在庫表の在庫が販売済みとなり、販売日が反映され、同じく出荷表に記載すると在庫表の在庫が出荷済みとなり、出荷日が反映されるようになっております。 ※「本題」 今回、教えて頂きたい部分に関しましては、Ｚ列に関しての計算式になります。 Ｘ列の計算式に関しましては、販売表に記載された情報を基に、在庫表のにある同じＪＡＮコードの在庫状態のものに対して入荷日の古い順から販売日が記載されるようになっております。 Ｚ列にＸ列に基づいた販売表の情報（文字列）が記載されるようにしたいのですが、どの様な計算式になるのでしょうか？ また、もう一つ教えて頂きたいのですが、 現在、入荷表及び販売表のＡ列にＪＡＮコード（数値）の入力となっておりますが、 まれに「AD2156HGH」や「DFT1253」などのＪＡＮコードが存在致します。 入荷表及び販売表のＡ列のＪＡＮコードが文字列でも認識するようにしたいのですが、全ての計算式をどのように変更すれば宜しいのでしょうか？ 大変、お手数をお掛け致しますが、ご回答、宜しくお願い致します。 各表の各列に記載されている計算式の一部を下記記載させて頂きます。 ※尚、出荷に関しましては、販売と変わらない内容の為、省略させて頂きます。 各表の例となります。 ※在庫表 .....V...........Ｗ.........X........Ｙ.........Ｚ.....AA.................AB.......AC.......AD 09...作業列......在庫状態...販売日...販売金額...参考...JANコード..........入荷先...入荷日...仕入金額 10...在庫........販売済み...6月3日...1000円.....緊急...4997609026752......A社......6月1日...500円 11...在庫........販売済み...6月3日...1000円.....緊急...4997609026752......A社......6月1日...500円 12...在庫........在庫..................................4997609026752......A社......6月1日...500円 13...在庫........販売済み...6月4日...700円.............4547597942956......B社......6月1日...350円 14...在庫........販売済み...6月4日...700円.............4547597942956......B社......6月1日...350円 15...在庫........販売済み...6月4日...700円.............4547597942956......B社......6月1日...350円 16...在庫........在庫..................................4547597942956......B社......6月1日...350円 17...在庫........在庫..................................4568526365426......B社......6月6日...350円 18...返品済み....返品済み..............................4568526365426......B社......6月6日...350円 19...返品済み....返品済み..............................4568526365426......B社......6月6日...350円 20...在庫........在庫..................................4589251455255......C社......6月9日...400円 ※在庫表に記載の各計算式 V列（通常は、作業列の為、非表示になっております。） V10=IF(AC10="","",IF(ISNUMBER(AG10),IF(OR(AF10="A社",AF10="B社",AF10="C社"),"返品済み","他店移動済み"),IF(NOT(ISBLANK(AC10)),"在庫",""&""))) 以降、下へコピー W列 W10=IF(X10="",V10,"販売済み") 以降、下へコピー X列 X10=IF(COUNTIFS(V$10:V10,"在庫",AA$10:AA10,AA10)>SUMIF(販売表!A:A,AA10, 販売表!D:D),0,IF(V10="在庫",INT(VLOOKUP(AA10*100+COUNTIFS(V$10:V10,"在庫",AA$10:AA10,AA10), 販売表!H$10:I$20,2,TRUE)),"")) 以降、下へコピー Y列 Y10=SUMIFS(販売表!C:C,販売表!A:A,AA10,販売表!B:B,X10) 以降、下へコピー Z列 教えて頂きたい計算式の列になります。（現在は空白状態です。） ※在庫表には表示したい理想の状態を記載しております。 AA列 AA10=IF(ROW(AA1)>SUM(入荷表!$E$10:$E$20),0,INDEX(入荷表!A$1:A$20,VLOOKUP(ROW(AA1),入荷表!$G$10:$H$20,2,TRUE))) 以降、下へコピー AB列 AB10=IF($AA10,INDEX(入荷表!B$1:B$20,VLOOKUP(ROW(AB1),入荷表!$G$10:$H$20,2,TRUE)),0) 以降、下へコピー AC列 AC10=IF($AA10,INDEX(入荷表!C$1:C$20,VLOOKUP(ROW(AC1),入荷表!$G$10:$H$20,2,TRUE)),0) 以降、下へコピー AD列 AD10=IF($AA10,INDEX(入荷表!D$1:D$20,VLOOKUP(ROW(AD1),入荷表!$G$10:$H$20,2,TRUE)),0) 以降、下へコピー ※入荷表 .....A..............B........C........D..........E........F.........G...........H .....JANコード......入荷先...入荷日...仕入金額...入荷数...(空白)....(空白)......(空白) 09...4997609026752..A社......6月1日...500円......3..................(計算式)....(計算式) 10...4547597942956..B社......6月1日...350円......4..................(計算式)....(計算式) 11...4568526365426..B社......6月6日...350円......3..................(計算式)....(計算式) 12...4589251455255..C社......6月9日...400円......1..................(計算式)....(計算式) 13 14 15 16 17 18 19 20 ※入荷表に記載の各計算式 G列 G10=1 G11=IF(E11,G10+INDEX(E$1:E$20,H10),"") 以降、下へコピー H列 {=IF(E10,ROUND(MOD(SMALL(C$10:C$20+ROW($10:$20)/10^4+NOT(C$10:C$20)*10^5,ROW(H1)),1)*10^4,0),"")} 以降、下へコピー ※販売表 ....A.............B......C.........D.......E.....F.......G.......H........I.......J.......K ....JANコード.....販売日.販売金額..販売数..参考..(空白)..(空白)..(空白)..(空白)..(空白)..(空白) 09..4997609026752.6月3日.1000円....2.......緊急..........計算式..計算式..計算式..計算式..計算式 10..4547597942956.6月4日.700円.....3.....................計算式..計算式..計算式..計算式..計算式 11 12 13 14 15 16 17 18 19 20 ※販売表に記載の各計算式 G列 G10=SMALL(A$10:A$20,ROW(G1)) 以降、下へコピー H列 H10=G10*100+K10 以降、下へコピー I列 I10={SMALL(B$10:B$20+ROW(B$10:B$20)/10^4+(A$10:A$20<>G10)*10^5,COUNTIF(G$10:G10,G10))} 以降、下へコピー J列 J10=INDEX(D$1:D$20,ROUND(MOD(I10,1)*10^4,0)) 以降、下へコピー K列 K10=SUMIF(G$9:G9,G10,J$9)+1 以降、下へコピー 以上、大変お手数をお掛け致しますが、どうか宜しくお願い致します。 ＜ 使用 Excel：Excel2013、使用 OS：Windows10 ＞ ---- 自信はないですが、いかがですか？ =IF(X10="","",INDEX(販売表!A:E,MATCH(AA10,販売表!A:A,0),5)) （もん） 2016/07/20(水) 12:43 ---- ＞入荷表及び販売表のＡ列のＪＡＮコードが文字列でも認識するようにしたいのですが、 ＞全ての計算式をどのように変更すれば宜しいのでしょうか？ 一から見直しになると思いますよ。 この式が使えなくなりますから。 　↓ ＞※販売表に記載の各計算式 ＞G10=SMALL(A$10:A$20,ROW(G1)) ※一般論としては、数値型と文字型を同じKeyで混在させると、大抵トラブルの元になります。 まぁ、こちらは所詮他人事なので、深く立ち入りませんけども。 (１)ちょっと分からなかったのですが、販売金額は、販売単価と同じ意味なのですか？ (２)同じJAN商品で、同じ日に、同じ販売先に売るのは、一回と決まっているのですね？ 　　同じ日に追加注文が来たらちょっと心配になります。 　　追加分は、前の契約の個数を増やすだけで対応する決まりなら、問題は起きないですけども。 (３)在庫が無いのに売っちゃった、なんてこともないですか？(勿論、注文契約上の話ですけど） 　　少なくともJANコードを入力ミスすることは起こりますよね？ 人間ですから。 それは形上、在庫が無いものを売っちゃったことになると思いますが、対策は大丈夫ですか？ （半平太） 2016/07/20(水) 15:33 ---- もんさん お世話になります。 お力添え有難う御座います。 ご記載頂きました計算式を入力してみましたが、残念ながら表示がされませんでした。 （qjcyp880） 2016/07/20(水) 15:48 ---- 半平太さん お世話になります。 お力添え頂き、有難う御座います。 数値より文字列認識への変更に関しては、やはり一から見直さなければならないのですね。 是非ともお力添えを頂ければと思います。 (1)に関しまして 販売金額＝販売単価となり、 同じ意味になります。 (2)に関しまして 同じJAN商品で、同じ日に、同じ販売先に売るのは、一回だけでは無く複数販売する場合も御座います。 上記内容を確認しておりませんでした。 気づいておらず非常に助かりました。 現段階で今回の表を利用する為に試行錯誤をしており、現在は非使用の為、気付かずぬけてしまっておりました。 今後の使用に関しましては、在庫を一万個管理が出来るテーブルを作成致します。 こちらの問題点も一緒に解決出来ればと思います。 (3)に関しまして 在庫が無い及びJANコードの入力ミス等の対策についての対策も一緒に解決出来ますととても助かります。 解決は可能でしょうか？ 以上、大変お手数をおかけ致しますが、是非ともお力添えをお願い致します。 （qjcyp880） 2016/07/20(水) 16:27 ---- もし、このような事も可能であれば、 入荷表の入荷数及び販売表の販売数のセルが空白の場合は、自動的に1と計算され、 個数(数字)が入力されている場合には、入力されている個数を計算するようにする事は可能でしょうか？ （qjcyp880） 2016/07/20(水) 16:54 ---- ＞同じJAN商品で、同じ日に、同じ販売先に売るのは、一回だけでは無く複数販売する場合も御座います。 これは、少々意外でした。 １回だけであることを念押しする積りだったんですけど。 そうなると結構厄介かも知れません。 データ量が１万行ですか。 まぁ、それ位は処理できないと、実務じゃ使えないでしょうから、当たり前の行数ではあります。 でも、数式ベースで構築した場合、サクサク動くか自信はないです。 なんたって、全行を見て処理しないとならないことが多いですからねぇ。 じゃ、マクロかってことになりますが、「誰が書くの？」っていう問題が発生します。 （ちょっとしたシステム作りになりますので、無料でやる人は現れないような気がします） 悩ましい。。　ちょっと悩んでみます。 ＞入荷表の入荷数及び販売表の販売数のセルが空白の場合は、自動的に1と計算され、 ＞ 個数(数字)が入力されている場合には、入力されている個数を計算するようにする事は可能でしょうか？ 可能かも知れませんが、間違いの元ですよ。何故、そんな仕様が必要になるんですか? （半平太） 2016/07/20(水) 17:37 ---- 半平太さん ご回答頂き、有難う御座います。 大変ご面倒なご対応で申し訳御座いません。 >同じJAN商品で、同じ日に、同じ販売先に売るのは、一回だけでは無く複数販売する場合も御座います。 こちらに関しましては、可能であればそうしたいのですが、 かなり難しい事だと認識致しました。 追加分に関しては、前の契約の個数を増やすだけの対応で対応したいと思います。 大変困難な課題を突きつけてしまい、申し訳御座いませんでした。 マクロでは無く、関数で可能な限りのお力添えを頂ければと思います。 >入荷表の入荷数及び販売表の販売数のセルが空白の場合は、自動的に1と計算され、 >個数(数字)が入力されている場合には、入力されている個数を計算するようにする事は可能でしょうか？ こちらに関しましては、販売数の1つしか無い場合は、少しでも入力の手間を省ければと思い、ご質問してみたのですが、 ご面倒な事を記載してしまい申し訳御座いません。 こちらは特に重要視しておりません。 以上、大変お手数をおかけ致しますが、宜しくお願い致します。 （qjcyp880） 2016/07/20(水) 18:03 ---- JANコードのことなのですが、後日(例えば10日後）、 同じJANコードの商品が「入荷」することもあるんですか？ それとも、1行/1JANコード(ダブり無し）でしょうか？　 サンプルでは、後日同じJAN商品が入荷する例はなかったですが、 なんか疑わしい気分になって来たんですけど。 （半平太） 2016/07/20(水) 19:13 ---- 半平太さん 大変お世話になります。 サンプルがしっかりしておらず申し訳御座いません。 おっしゃる通り、後日、同じJANコードの商品が入荷致します。 説明がしっかりと行き届いておらず、本当にお手数をおかけ致しまして申し訳御座いません。 是非ともお力添えをお願い致します。 （qjcyp880） 2016/07/20(水) 19:58 ---- ＞後日、同じJANコードの商品が入荷致します。 そうなると、私には、数式だけでは無理のような気がします。 並替えだけで疲弊してしまいます。(5000行でもやれる気はしないです) 「手動の並替え」を併用するか(私的には気が進まない）、「マクロ」でやるかですね。 前レスで、マクロでやってくれる人は居ないかも、とか言っちゃいましたが、処理内容にもよります。 便利機能抜きで、在庫表を作るだけなら、何人か現れるかも知れません。 マクロは絶対にダメと云うことなら、そう表明すれば 数式ベースでも誰かトライしてくれるかも知れません。 いずれを選好するか、再度表明して頂くといいと思います。 （半平太） 2016/07/20(水) 21:00 ---- 半平太さん 大変お世話になっております。 ご回答頂き、有難う御座います。 数値から文字列への認識変更が大変難しい事を実感致しました。 お手数をお掛け致しまして申し訳御座いません。 現在の数値認識のまま利用するにあたり、 在庫表のY列とX列の計算式にてお助け頂きたいのですが、 現在、在庫表のY列には、上記にも記載しておりますが、 Y10=SUMIFS(販売表!C:C,販売表!A:A,AA10,販売表!B:B,X10) 以降、下へコピー が、入力されております。 SUMIFS関数の為、販売表に同じJANコードの商品が同じ日に複数販売され、入力した場合、全ての販売金額が足されたものが在庫表に反映され表示されてしまいます。 この在庫表Y列の計算式を販売金額が正しく表示されるようにしたいのですが... また、在庫表のZ列に販売表のE列（備考）を表示したいのですが、 計算式がわからず、現在は空白の状態になっております。 ※上記表には理想の表示を記載しております。 在庫表のZ列は、どの様な計算式になるのでしょうか？ 以上、大変お手数をお掛け致しますが、是非ともお力添え下さい。 サンプル表 ※販売表 ....A.............B......C.........D.......E.....F.......G.......H........I.......J.......K ....JANコード.....販売日.販売金額..販売数..参考..(空白)..(空白)..(空白)..(空白)..(空白)..(空白) 09..4444444444444.6月3日.1000円....2.............緊急....計算式..計算式..計算式..計算式..計算式 10..4444444444444.6月3日.700円.....3.....................計算式..計算式..計算式..計算式..計算式 11 12 13 14 15 16 17 18 19 20 ※在庫表 .....V...........Ｗ.........X........Ｙ.........Ｚ.....AA 09...作業列......在庫状態...販売日...販売金額...参考...JANコード 10...在庫........販売済み...6月3日...1700円.....緊急...4444444444444 11...在庫........販売済み...6月3日...1700円.....緊急...4444444444444 12...在庫........販売済み...6月3日...1700円............4444444444444 13...在庫........販売済み...6月3日...1700円............4444444444444 14...在庫........販売済み...6月3日...1700円............4444444444444 15...在庫........在庫..................................4444444444444 16...在庫........在庫..................................4444444444444 17...在庫........在庫..................................4444444444444 18...在庫........在庫..................................4444444444444 19...在庫........在庫..................................4444444444444 20...在庫........在庫..................................4444444444444 （qjcyp880） 2016/07/20(水) 23:48 ---- 案はお出ししますが、多分、速度不足で実用には耐えられないと思います。 (1) AE10セル =IF(AA9<>AA10,1,AE9+1) (2) Y10セル =IF(W10="在庫","",IFERROR(INDEX(販売表!C:C,ROUND(MOD(VLOOKUP(AA10*100+AE10,販売表!H:I,2,FALSE),1)*10000,0)),Y9)) (3) Z10セル =IF(W10="在庫","",IFERROR(INDEX(販売表!E:E,ROUND(MOD(VLOOKUP(AA10*100+AE10,販売表!H:I,2,FALSE),1)*10000,0)),Z9)&"") それぞれ下にコピー <在庫表 結果図> 行 ___V___ ____W____ ___X___ ____Y____ ___Z___ ______AA______ ___AB___ __AC__ ___AD___ _AE_ 9 作業列 在庫状態 販売日 販売金額 参考 JANコード 入荷先 入荷日 仕入金額 補助 10 在庫 販売済み 6月3日 1,000 緊急01 4444444444444 入荷社1 6月2日 1234 1 11 在庫 販売済み 6月3日 1,000 緊急01 4444444444444 入荷社1 6月2日 1234 2 12 在庫 販売済み 6月3日 700 4444444444444 入荷社1 6月2日 1234 3 13 在庫 販売済み 6月4日 500 緊急02 4444444444444 入荷社1 6月2日 1234 4 14 在庫 販売済み 6月4日 500 緊急02 4444444444444 入荷社1 6月2日 1234 5 15 在庫 販売済み 6月4日 60 速報01 5555555555555 入荷社2 6月3日 456 1 16 在庫 販売済み 6月4日 60 速報01 5555555555555 入荷社2 6月3日 456 2 <入荷表 サンプル> 行 ______A______ ___B___ ___C___ ____D____ ___E___ _F_ ___G___ _H_ 9 JANコード 入荷先 入荷日 仕入金額 入荷数 入荷計 11 10 4444444444444 入荷社1 6月2日 1234 5 1 10 11 5555555555555 入荷社2 6月3日 456 6 6 11 <販売表 サンプル> 行 ______A______ ___B___ ____C____ ___D___ ___E___ _F_ ______G______ _______H_______ _____I_____ _J_ _K_ 9 JANコード 販売日 販売金額 販売数 参考 10 4444444444444 6月3日 1000 2 緊急01 4444444444444 444444444444401 42524.0010 2 1 11 4444444444444 6月3日 700 1 4444444444444 444444444444403 42524.0011 1 3 12 5555555555555 6月4日 60 2 速報01 4444444444444 444444444444404 42525.0013 3 4 13 4444444444444 6月4日 500 3 緊急02 5555555555555 555555555555501 42525.0012 2 1 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 　　　　↑ 　　　　　　　　　　　　　　　　　　　　　　　　　　　※後々、同JANの売却が増えて100以上になったらアウトになります。 （半平太） 2016/07/21(木) 09:32 ---- 半平太さん 大変お世話になります。 上記内容を確認させて頂きました。 数値認識で実用化が出来るまでになりました。 今回、大変お手数をおかけ致しました。 半平太さんにお力添えを頂いたおかげで大変助かりました。 大変感謝しております。 有難う御座いました。 また何か機会が御座いましたら、是非とも宜しくお願い致します。 （qjcyp880） 2016/07/21(木) 16:15