[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『商品の発注依頼について』（カズ）
現在、食材を業者に発注する際は、その食材を使用する前日に納品される事を前提に、発注から納品までに掛かる日数や業者の休業日は納品されないので休業日の前日に納品されるよう逆算して発注依頼をかけています。
しかし、約２０社の業者や食材の種類から、少しでも自動化できないか？と思います。マクロ等はわからないので、使えそうなフリーソフトとか探しましたが、使えそうなのが無く、少しでも自動化できる部分があればご教授願いたいです。
下記が現在のEXCEL表です。この表は1Sheetに１０種類の食材が入力されていて食材が15あれば2Sheet分になります。
このファイルが各発注業者毎にあります。
A B C D E F P Q R S T U
1 〇〇業者
2 7/20 7/21・・・7/31 8/1 8/2 8/3 8/4 8/5 8/6・・8/31 3 月 火 金 土 日 月 火 水 ・・ -------------------------------------------------------------------------------- 4 豚レバ 6
5 商品No ○
6 在庫管理 4
7 中3日 3 8/4 --------------------------------------------------------------------------------
表は20日始まりで、この表は消して使い回しです。
上記のように１品目につき４行使用で、上の表でやりたい事を説明しますと「豚レバ」を使う日に注文個数「6」を入力すると、納品は前日とする場合、納品までの所要日数が「中3日」必要なので注文期限は7/31となり「商品No」の行に赤丸が付く。
正し、業者の休みが土日、木、祝日が休みなので休みの日は納品されないから休みに入る前日に納品となる事で「中3日」を逆算して赤丸を付ける。
(業者によっては、火、木、土、日休みもある） 業者が休みの日は条件付き書式でセルに色が付くようになっています。
6行目は手入力します。 7行目はA7は「中3日」と入力されていて、B7に「中3日」の「3」が入っています。 8/4の意味は納品日が8/4という意味です。 (食材によって「中2日」や「中1日」「翌日」と納品までの日数が変わります。
と言うように、一つのシートに10の種類の注文ができるようになっています。
以上となります。説明も不十分かも知れませんが、宜しくお願いします。
＜ 使用 Excel：unknown、使用 OS：unknown ＞
Excelのバージョンが書いてないですが、 XL2010以降であれば WORKDAY.INTL関数 が使えるので、 休日リストが「休日」と名前定義されているものとすると・・
(1) D5セル =IF(ISNUMBER(D4),WORKDAY.INTL(WORKDAY.INTL(D$2,-1,"0001011",休日)-$B7,-1,"0001011")+WORKDAY.INTL(D$2,-1,"0001011",休日)*0.5^20,0) (2) D7セル =IFERROR(MOD(INDEX($D5:$AT5,MATCH(D$2,INDEX(INT($D5:$AT5),0),0)),1)/0.5^20,"")
それぞれ、右にコピー(AT列まで）
なお、数式中の"0001011"は木土日が休みであることを意味する。 火木土日休みなら"0101011"とする必要がある。
以上により下記結果図を得る。後述の書式設定で見てくれを整えてください。
<書式設定前 結果図> 行 ____A____ _B_ _C_ __D__ _ E _ ___ F ___ __G__ __N__ _ O _ P Q _R_ _S_ _____T_____ _U_ 1 〇〇業者 2 7/20 7/21 7/22 7/23 7/30 7/31 8/1 8/2 8/3 8/4 8/5 8/6 3 月 火 水 木 木 金 土 日 月 火 水 木 4 豚レバ 6 5 商品No 0 0 0 0 0 0 0 0 0 0 44043.04201 0 6 在庫管理 7 中3日 3 44047
以下、見てくれを整える書式設定
D5セル以降の書式(表示形式) → ;; (※セミコロン２つです）
D5セル以降の条件付き書式 条件 → =D7*1>0 書式(表示形式) → [赤]○
D7セル以降の書式(表示形式) → m"月"d"日"
<書式設定後 結果図> 行 ____A____ _B_ _C_ __D__ _ E _ ___ F ___ __G__ __N__ __ O __ P Q _R_ _S_ _T_ _U_ 1 〇〇業者 2 7/20 7/21 7/22 7/23 7/30 7/31 8/1 8/2 8/3 8/4 8/5 8/6 3 月 火 水 木 木 金 土 日 月 火 水 木 4 豚レバ 6 5 商品No ○ 6 在庫管理 4 7 中3日 3 8月4日
（半平太） 2020/08/02(日) 12:08
お世話になります。
早速のご教授ありがとうございます。
関数式の方を入れてみました所、私のやり方が悪いのか、４行目の注文個数を入れると同列の５行目が#NAME?となります。
2行目の日付ですが実際の表は、「=IF(D2="","",IF(DAY(D2+1)<DAY(D2),"",D2+1))」の式が入っており
3行目の曜日は「=TEXT(E2,"aaa")」が入っています。
業者が休みの日は条件付き書式で「=OR(D$3="土",D$3="日",D$3="水")」が設定されてて２行から43行まで
休みの曜日セルに色が付きます。
なので、日付の部分を、関数を入れずに変更し「休日」を入れてみましたが#NAME?が出ます。
もしかして、
D5セル以降の書式(表示形式) → ;; (※セミコロン２つです）
D5セル以降の条件付き書式 条件 → =D7*1>0 書式(表示形式) → [赤]○
ここのやりかたが間違っているのでしょうか？
現在は、自宅のパソコンWin10 / Excel365で試しています。
（カズ） 2020/08/02(日) 18:01
＞#NAME?となります。
休日リスト範囲を「休日」と名前定義してあると言う前提で作ってある数式です。 そちらの現実に合わせてください。
※どこに休日リストを作っているんですか？ とにかく、それ(祝日一覧）がないと祝日を考慮することは出来ません。
＞業者が休みの日は条件付き書式で「=OR(D$3="土",D$3="日",D$3="水")」が設定されて 祝日を考慮してないのと違いますか？ 水じゃなく木が休みと言う話だったと思いますが、どうなっているんですか？
＞2行目の日付ですが実際の表は、「=IF(D2="","",IF(DAY(D2+1)<DAY(D2),"",D2+1))」の式が入っており それじゃ、8月が全部空白になりませんか？
（半平太） 2020/08/02(日) 19:12
すみません。ご指摘ありがとうございます。
この表の横に年間祝日リストを設定しました。
=IF(ISNUMBER(D4),WORKDAY.INTL(WORKDAY.INTL(D$2,-1,"0010011",$BC$3:$BC$55)-$B7,-1,"0010011")+WORKDAY.INTL(D$2,-1,"0010011",$BC$3:$BC$55)*0.5^20,0)
このように入れてみました。
ありがとうございます。ちゃんと表示されました。
２行目の日付ですが確かに次月は空白になるので、次月の頭だげ9/1とか入れています。
少し面倒ですが。
（カズ） 2020/08/02(日) 21:08
お世話になっています。
赤○の付くセルですが、中3日とか中2日は業者の営業日のみで数えたいです。業者の定休日、祝日等は中３日とかに含めないようにしたいのですが可能でしょうか？
なので営業日だけを数えると赤○がかなり前に来て、注文から納品まで日数が空く場合があります。
すみません。気付くのが遅れ今になってしまいました。
宜しくお願いします。
（カズ） 2020/08/04(火) 10:33
＞中3日とか中2日は業者の営業日のみで数えたいです。 ＞業者の定休日、祝日等は中３日とかに含めないようにしたいのですが可能でしょうか？
「営業日のみ」じゃないでしょう？ 土日祝定休日が無関係なので、厳密には「カレンダー日数」ですよね？
・・で、そうなる様に作ってあるんですけども・・
実際に問題が発生しているなら、 食材使用日をいつにしたら、○がいつになるべきなのに、何日になってしまう、 と言う具体例を挙げてください。
検証には、その業者の休みがいつなのかも必要なので、それも説明に入れてください。
（半平太） 2020/08/04(火) 11:14
お世話になっております。
すみません。何度も。
１か月のうち、ちゃんとそうなってる所もあれば、なっていない所もあります。
5月、例1、「0000011」で５月のG/Wの休日あり。の場合。 （納期中３日） だと
?@食材を使う日が5/3(日),食材入荷日5/1(金),発注日4/27(月)で、ちゃんと表示されOKです。
?A食材を使う日が5/8(金),食材入荷日5/7(金),発注日5/1(金)に○が付きますが、ここの○はG/Wの連休や土日があり、そこに中３日を加えると○は4/27(月)になります。
5月は、祝日部分に○が重ならない部分は全て大丈夫です。土日も考慮されています。
5月、例2、「0010011」で５月のG/Wの休日あり。の場合。 （納期中３日） だと
?@食材を使う日5/2(土),入荷日5/1(金),発注日4/27(月)で4/27に○が付きますが、中３日で土日が入るので○は4/24になります。
?A5/9に食材を使う場合も、○が5/4のG/Wに付くので、4/28に○が付くのが正解になります。
?B5/25に食材を使う場合も、○が5/18に付きますが、土日、水が休みで中３日なので○は5/17に付きます。
発注先の業者さんは15社あって、普通の定休日は水土日の所もあれば、木土日、火木土日とさまざまです。それにプラスして祝日も休みです。
中３日とか2日とか、1日とかは一つの業者で統一はされていますが、一部の食材によっては異なります。
宜しくお願いします。
（カズ） 2020/08/05(水) 09:05
＞ 2 7/20 7/21・・・7/31 8/1 8/2 8/3 8/4 8/5 ＞ 3 月 火 金 土 日 月 火 水 ＞ ---------------------------------------------------------------- ＞ 4 豚レバ 6 ＞ 5 商品No ○ ＞ 6 在庫管理 4 ＞ 7 中3日 3 8/4
当初の質問では、使用日8/5、納品日8/4、注文期限7/31 となっていました。
8/4から7/31までで中3日なら、土日はカウントしていますよね。 土日をカウントしないなら、中1日にしかなってないのと違いますか？
上図で注文期限が7/31と言うのは間違いだったんですか？
（半平太） 2020/08/05(水) 09:46
お世話になっています。
すみません。 間違いではありません。合っています。
確かに上図の説明は土曜日はカウントしていますが、発注業者が約15社くらいある中で、殆どの業者が土日祝が休みに対して、ここの業者も含め３社ほど、土日営業の業者もあるので、そちらで載せていましたが、検証する際、土日祝が休みの業者で確認しておりました。
本当にお騒がせして申し訳ございません。
（カズ） 2020/08/06(木) 12:52
＞発注先の業者さんは15社あって、普通の定休日は水土日の所もあれば、 ＞木土日、火木土日とさまざまです。それにプラスして祝日も休みです。 ＞中３日とか2日とか、1日とかは一つの業者で統一はされていますが、 ＞一部の食材によっては異なります。
＞確かに上図の説明は土曜日はカウントしていますが、発注業者が約15社くらいある中で、 ＞殆どの業者が土日祝が休みに対して、ここの業者も含め３社ほど、土日営業の業者もあるので、 ＞そちらで載せていましたが、検証する際、土日祝が休みの業者で確認しておりました。
あのー・・ですね、業者ごと、食材ごとに色んなバターンがあるなら、 まずそれをマスタに書き出してください。
こんな感じに作れませんか？ それがないと自動化できる部分が限定的になります。(あれば全面的自動化が可能） ↓ 行 ______BC______ ___BD___ ____BE____ __BF__ __BG__ _BH_ 2 祝日一覧 業者名 休パターン 食材 期限 日数 3 2020/1/1(水) 〇〇業者 0000000 豚レバ 中3日 3 4 2020/1/13(月) ××業者 0001011 鶏レバ 中1日 1 5 2020/2/11(火) △△業者 0101011 牛タン 翌日 0 6 2020/2/23(日) 鹿肉 中2日 2 7 2020/2/24(月) ： ： ：
（半平太） 2020/08/06(木) 15:40
お世話になっています。
有難うございます。色々と良い方法を考えてもらい。
ご提案頂いたように作れます。
BC列は年間祝日を作り、BEの列は祝日以外の定休日となるわけですね。
作成してみます。
（カズ） 2020/08/06(木) 17:45
＞中３日とか2日とか、1日とかは一つの業者で統一はされていますが、一部の食材によっては異なります。
ここ、ちょっと勘違いしました。
中3日かどうかも業者ごとに統一されているのでしたか。 そうなると、その決まりより優先する一部食材がどう決まっているのか分かる表が必要です。
こんな感じ ↓ 行 ______BC______ ___BD___ ____BE____ __BF__ _BG_ ___BH___ __BI__ _BJ_ 2 祝日一覧 業者名 休パターン 期限 日数 優先食材 期限 日数 3 2020/1/1(水) 〇〇業者 0000000 中3日 3 豚レバ 中1日 1 4 2020/1/13(月) ××業者 0001011 中1日 1 鶏レバ 中4日 4 5 2020/2/11(火) △△業者 0101011 翌日 0 牛タン 中3日 3 6 2020/2/23(日) 2 鹿肉 中4日 4
これで条件は全部ですか？ もしかして、その優先食材も、業者によって中3日なんかも変わるんですか？ そうだとしたら、それも全部わかるような表にしてください。
(後出しされるとキリがないので、次の返信で締めきります）
（半平太） 2020/08/06(木) 20:43
お世話になります。
・各業者中3日、中2日等は食材毎に決まっています。なので表には食材毎に、この品は中何日と書いています。
A B
4 豚レバー 5 商品No 6 在庫管理 7 中3日 3 ＿＿＿＿＿＿＿＿＿＿＿ 8 牛バラ 9 商品No 10 在庫管理 11 中2日 2 ＿＿＿＿＿＿＿＿＿＿＿ 12 豚ロース 13 商品No 14 在庫管理 15 翌日 0 ＿＿＿＿＿＿＿＿＿＿＿
と各食材毎に中日数が決まっています。1Sheetに1業者でA4用紙1枚に10種類の食材が書け、業者により１枚で済む業者もあれば、最高６枚使う業者もあります。
・食材の種類については、数十種類ありますが同じ食材を複数の業者に発注する事はありませんし、業者に発注する食材も決まっています。
・発注業者は現在１２社。
・BE列の休みパターンは殆どが「0000011」で一部業者で違いあり。
・BF列の食材は数十種類あり。（発注する食材は業者で決まっている）
・期限は翌日〜中4日の5パターン。
・食材を使う予定日の前日に納品
中３日とか２日とかの統一についてですが、１２社中１社だけ発注する食材（ここの業者は１０種類発注する）が「中１日」たまたま統一であると言う意味です。他の業者は食材毎まちまちです。
と言うのが全ての条件になります。優先する食材は、ありません。
すみません。こんな書き方で良いでしょうか？
宜しくお願いします。
（カズ） 2020/08/07(金) 18:48
ちょっと分からない部分があるので、以下の方針とします。
A列「中3日」、B列「3」 と言う類のデータにはこちらはタッチしないものとします。 そちらで、何らかの形(手作業か自動か）でご対応願います。
業者の定休日の情報は、土日以外に定休日がある業者だけ、BD:BE列に書き出すものとします。 ※そのリストにない業者は土日のみと見做します。
(1) BD1セル =IFERROR(TEXT(VLOOKUP(F1,$BD$3:$BE$30,2,FALSE),"0000000"),"0000011")
(2) E2セル =D2+1
AQ2セルまでコピー ※途中で月初日を入れないで済むようにする
(3) AR2セル =IF(AQ2="","",IF(DAY(AQ2+1)<DAY(AQ2),"",AQ2+1)) AT2セルまでコピー
(4) D3セル =TEXT(D2,"aaa")
(5) D5セル =IF(ISNUMBER(D4),WORKDAY.INTL(D$2,-$B7-2,$BD$1,$BC$3:$BC$55),0) (6) D7セル =IF(COUNTIF($D5:$AT5,D$2),WORKDAY.INTL(D$2,$B7+1,$BD$1,$BC$3:$BC$55),"")
上記(4)から(6)をAT列までコピー
第二食材以降は、上記(5)から(6)をコピーする。
セルの書式は、前回指示したままにしてください。
<結果図>
行 ____A____ _B_ _C_ __D__ _ E _ ___ F ___ __G__ __H__ _ I _ _ J _ __K__ __ L __ __M__ _N_ O P Q _R_ _S_ _T_ ______BC______ ___BD___ ____BE____
1 ××業者 0001011
2 2/20 2/21 2/22 2/23 2/24 2/25 2/26 2/27 2/28 2/29 3/1 3/2 3/3 3/4 3/5 3/6 3/7 祝日一覧 業者名 休パターン
3 木 金 土 日 月 火 水 木 金 土 日 月 火 水 木 金 土 2020/1/1(水) 〇〇業者 0000000
4 豚レバー 6 2020/1/13(月) ××業者 0001011
5 商品No ○ 2020/2/11(火) △△業者 0101011
6 在庫管理 4 2020/2/23(日)
7 中3日 3 3月6日 2020/2/24(月)
8 牛バラ 7 2020/3/20(金)
9 商品No ○ 2020/4/29(水)
10 在庫管理 4 2020/5/3(日)
11 中2日 2 3月4日 2020/5/4(月)
2020/5/5(火)
2020/5/6(水)
2020/7/23(木)
2020/7/24(金)
2020/8/10(月)
＞業者が休みの日は条件付き書式で「=OR(D$3="土",D$3="日",D$3="水")」が設定されてて２行から43行まで ＞休みの曜日セルに色が付きます。
祝日が反映されていないハズので、以下の条件式を使ってください。 =NETWORKDAYS.INTL(D$2,D$2,$BD$1)=0
（半平太） 2020/08/07(金) 20:23
＞ 祝日が反映されていないハズので、以下の条件式を使ってください。 ＞ =NETWORKDAYS.INTL(D$2,D$2,$BD$1)=0 ↓ =NETWORKDAYS.INTL(D$2,D$2,$BD$1,$BC$3:$BC$55)=0
※肝心の祝日リストが入ってなかった m(__)m
（半平太） 2020/08/08(土) 06:27
最後まで、ありがとうございました。
確実に表示されます。
おかげ様で、かなり仕事も早くなります。
本当にありがとうございました。
（カズ） 2020/08/08(土) 23:35
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.