『当日計上したセルのフォント色を変えるには』（Mei） こんにちは。 いつも参考にさせていただいています。 このたび、ご教授をお願いしたいのですが Sheet1に a b 　c 1 商品 入荷予定 　注文数 2 富士通 2007/7/13 　　15 3 NEC 2007/7/16 　　31 4 DELL 2007/8/1 　　70 5 東芝 2007/8/3 　　10 6 HP 2007/8/1 　　5 7 SONY 2007/7/11 　　27 と入荷予定と注文数のDATAがあります。 SHEET2に a b c d e 1 商品 7月 8月 2 富士通 入荷数 10 3 受注数 15 4 NEC 入荷数 31 5 受注数 31 6 DELL 入荷数 7 受注数 70 8 東芝 入荷数 9 受注数 10 10 HP 入荷数 11 受注数 5 12 SONY 入荷数 25 13 受注数 27 とSUMPRODUCT関数を使用して月別に入荷されたTOTALの数を計算しています。 そこで、当日（本日なら7/13）に入荷したセルのフォントを赤に変更するにはどうしたらよいでしょうか。 数式は、 =SUMPRODCUT((sheet1!$A$2:$A$65536=$A2)*(sheet1!$B$2:$B$65536=D$1)) 流れとしては、当日に入荷されたらフォントが終日赤になって、次の日に入荷がなければ、そのフォんとは黒に戻る…みたいな。 複雑ですかね。 条件付書式設定だと、他のSHEETの抽出はできませんよね…。 となると、SHEET1のデータのほうに当日の日付に色をつけて、その色のついた数値を…と思ったけど、加算しなければ意味がないので色だけの数じゃだめでした…。 赤文字＋前回の合計となればいいのですが…。 独り言です、すみません。 ---- Sheet1に「入荷予定日」があるのですよね？ １．「入荷した日」ってのは、どこかに情報があるのですか？ ２．何処のセルのフォントの色を変えたいのですかね？ ３．＞=SUMPRODCUT((sheet1!$A$2:$A$65536=$A2)*(sheet1!$B$2:$B$65536=D$1)) 　　この式は何を求めるもので、何処のセルに入力される式なのですかね？ ４．Sheet2に作業列を作って、今日の日付が有る商品にフラグをたて 　　条件付き書式で「今月の列で、作業列にフラグがある物」の 　　フォントの色を変える　と言うのでは上手く行きませんかね？ ご質問の際に、セル番地を決めていただく事も重要ですが 実際のデータが何であるのか（文字なのか、数値なのか、 表示形式はどうなっているか　等）の情報も重要です。 上手く行かない場合は、載せられるのが宜しいかと思います。 (HANA) ---- HANAさん アドバイスありがとうございます。 1.について E列に入荷日の列があります。 a b 　c　　　…　e　　　　f　　　　 1商品 入荷予定 　注文数　　入荷日　　入荷数 2富士通 2007/7/13 　　15　　　2007/7/12　 10 3NEC 2007/7/16 　　31　　　2007/7/14　 31 4DELL 2007/8/1 　　70 5東芝 2007/8/3 　　10 6HP 2007/8/1 　　5 7SONY 2007/7/11 　　27　　　2007/7/11　 25 と入荷した際に空白が埋められていくようになっています。 2.について フォントの色を変えたい箇所は、前例に挙げたSHEET2にある「入荷数」のところです。 （D2,D4…） 対象となる条件は、SHEET1の「入荷日」を範囲とします。 入荷した時点で、SHEET2にある表の数値が都度変更されます。 結果「数値が変動した」「前日の数値と違う」のように認知できるのであれば、 4.の条件を進めていきたいと思っています。 3.について この数式は、D2=SUMPRODCUT((sheet1!$A$2:$A$65536=$A2) …SHEET1のA列にある文字列をSHEET2のA列「富士通」と照合 　　　　　　 *(sheet1!$B$2:$B$65536=D$1))　…B列にある入荷予定とD1（7月とありますが、実際は7/1と7/31にわけてある）の7月対象の件数をカウント。 今までは、手作業で数値を入力していました。 冷静に考えると、B列を検索範囲ではなくE列の入荷日でなければなりませんね…。 データはほぼ文字列です。 日付欄は日付（シリアル値ではありません） ご質問にうまくお答えできず、申し訳ありませんでした。 言葉足らずな点もあったことお許しください。 (mei) ---- ＞ 日付欄は日付（シリアル値ではありません） 書式が　日付け　設定なら　シリアル値ですよ。 もし文字列なら　無理でしょう。　（シリアル値に変更しなければ） ---- 名無しさん ご指摘ありがとうございます。 シリアル値というのは、2007/7/14であれば39277と表示されていることの意味だと思っていました。 勘違いするとこでした、ありがとうございます。 文字列でないことは確かです。 (mei) ---- SUMPRODUCT関数で処理したら、動きが鈍すぎたので、Sheet1に作業列を設ける方法です。 Sheet1のA列に列挿入し、A1セルに　入荷チェック　と手入力。 A2セルに　=IF(C2=TODAY(),TEXT(C2,"yyyy/m月")&"_"&B2,"")　として、下方コピー。 A列全体を選択し、【挿入】-【名前】-作成　で、上端行にチェックを入れ、OK。 ※Sheet2の条件付き書式で使えるようにするために、名前を定義しました。 ※範囲は、列全体でない方が良いとは思いますが、とりあえず全体です。 Sheet2の該当範囲（例だと、D2:E13)を選択し、【書式】-【条件付き書式】-　数式が　で =COUNTIF(入荷チェック,D$1&"_"&$A2)　　と入れて、書式を設定し、OK。 ただし、Sheet2は、月だけでは困るので、2007/7月　の様に入力するものとして考えています。 当初のご質問内容と数式しか見て無かったので、HANAさんのレス以降は加味してません（HANAさんゴメンね）。 勘違いしてたら、ゴメンナサイ！　です。 (sin) 昨日書き込んだと思い込んでた。。。 ---- sinさん 確かにSUMPRODUCTは鈍いですよね…更新するには便利な機能なのですが。 上記の方法を早速試させていただきました。 名前の定義まではうまくいったのですが、 SHEET2の条件付書式が反映されませんでした。 月表示もユーザ定義でyyyy/m月に設定しました。 確認したいのですが、 =COUNTIF(入荷チェック,D$1&"_"&$A2)　 は、SHEET1のA列にある入荷チェック下行の2007/8月_富士通（当日分のみ表示）の個数を SHEET2のD2:E13に転記させて、更に書式を設定する作業でよろしかったでしょうか。 もしかすると、当日分のみのCOUNTになってしまうのかなと思ったので質問させていただきました。 フォントの変更はできそうですね。 (mei) ---- >確かにSUMPRODUCTは鈍いですよね… 65000行もの配列を2列使ってますから・・・ 昨日試したものは、TODAY()部分を含むと4列(?)分ぐらいでしたから、推測は出来ましたけど、 それにしても遅かった。クラッシュしないだけよかったですけど。。。 >SHEET2の条件付書式が反映されませんでした。 >月表示もユーザ定義でyyyy/m月に設定しました。 だと、見た目だけで、実際は入力した日付が使われていますので、''D$1''の部分を''TEXT(D$1,"yyyy/m月")''と変えてください。 >もしかすると、当日分のみのCOUNTになってしまうのかなと思ったので質問させていただきました。 はい、今日（TODAY()と同じ日）のものだけです。 簡単に説明すると、 Sheet1で『入荷チェック』と名前を付けている範囲（Sheet1のA2からA65536 Ver2007は違うらしい・・・）には、 C列の入荷予定日が''今日の日付''のもの（2007/8月_富士通　の形で返されている部分）か、 文字数0の文字列(数式をコピーした範囲で""が返される部分）か、 未入力（数式をコピーしていない部分）が存在します。 この範囲の中で、D$1&"_"&$A2（TEXT(D$1,"yyyy/m月")&"_"&$A2）の個数が、 1個以上あれば、設定する書式が反映されます。 意図と違ってます？？？ (sin) ---- sinさん なるほど・・見た目では2007/8月と返されても、実際は8/1でも8/14でも2007/8月と表示されますからね。 太文字の部分の修正したところ、A列に対する個数が反映されました！ 本来の目的は、当日より以前の数値も加算するようにしたいのです。 流れとしては、8/13に入荷があった個数が3個、今日は8/14で7個。　D2に10個と赤い字で表示。 明日（8/15）になったとして、その日は入荷0個なので、フォントが黒になり10個と表示。 明後日（8/16）になったとして、入荷が10個入ったら、またフォントが赤になり20個と表示。 どの商品が当日に入荷されたか目視で確認できるように、当日だけフォントの色を赤などにすることが目的でした。 sinさんの方法でもいいかなと思っています。 月単位で表示させてますから、その日だけだとその月に入荷された合計が確認できなくなるため、思考中です・・。 一日単位にすればいけそうですが、それもまた重くなるのでしょうか・・(^-^;) (mei) ---- >流れとしては、8/13に入荷があった個数が3個、今日は8/14で7個。　D2に10個と赤い字で表示。 >明日（8/15）になったとして、その日は入荷0個なので、フォントが黒になり10個と表示。 >明後日（8/16）になったとして、入荷が10個入ったら、またフォントが赤になり20個と表示。 ここで言われている''色の変更''に対する条件付き書式の設定に関してのみ回答していました。 それは、↓と書き込まれていたので、 >とSUMPRODUCT関数を使用して月別に入荷されたTOTALの数を計算しています。 TOTALの計算は、出来ていて、条件付き書式で今日入荷のセルの色の変更をさせたいだけ　っと読んでいましたので。。。 どこかが食い違ってそうですねぇ？？？？ 色を変えるためには、今日（TODAY())の条件が必要ですが、 Total数を出す為には、その期間（多分同月と思いますが）が必要で、同じ式では出来ません。念のため・・・ という事をご参考に、もう一度何をなさりたいのかを整理してみてはいかがでしょう？ (sin)　またまた、私の大ぼけかも？？？ ---- 私が首を突っ込むと、話しがごちゃごちゃするかもしれませんが・・・ ＞1.について 回答を頂いたので、もう少し質問させてください。 ●「入荷予定 - 注文数」と「○月 - 受注数」が対応していますか？ 　 a b 　c　　　…　e　　　　f　　　　 1商品 入荷予定 　注文数　　入荷日　　入荷数 2富士通 2007/7/13 　　15　　　2007/7/12　 10 ●残りの「5」つが入荷されたときは 　何処にどの様に記入するおつもりですか？ もう少し、手順に沿って　どの様にしたいのか ご説明して頂くのが良いような気がします。 何がどうなっているのかよく分かりませんが、私なら以下のようにすると思います。 今日が「8/14」の場合です。 入荷シートを作成し、商品・入荷日・入荷数　データを蓄積させます。 [A] [B] [C] [1] 商品 入荷日 入荷数 [2] SONY 2007/7/11 25 [3] 富士通 2007/7/12 10 [4] 富士通 2007/7/13 5 [5] NEC 2007/7/14 31 [6] 東芝 2007/8/14 10 集計するシートのA列を作業列にします。 [A] [B] [C] [D] [E] [1] 商品 7月 8月 [2] 0 富士通 入荷数 15 0 [3] 受注数 [4] 0 NEC 入荷数 31 0 [5] 受注数 [6] 0 DELL 入荷数 0 0 [7] 受注数 [8] 1 東芝 入荷数 0 10 [9] 受注数 [10] 0 HP 入荷数 0 0 [11] 受注数 [12] 0 SONY 入荷数 25 0 [13] 受注数 A2 =SUMPRODUCT((入荷!$A$2:$A$1000=$B2)*(入荷!$B$2:$B$1000=TODAY())) D2 =SUMPRODUCT((入荷!$A$2:$A$1000=$B2)*(MONTH(入荷!$B$2:$B$1000)&"月"=D$1)*入荷!$C$2:$C$1000) D2の条件付き書式の設定で 数式が =(D$1=MONTH(TODAY())&"月")*$A2　文字色　赤 必要な範囲にコピーします。 入荷シートの6行目の「東芝」が今日の日付なので 集計シートのA8セル（東芝のセル）に「1」が表示されています。 1行目が今月（8月)の列で、A列が「1」となっている行が 条件付書式で設定した数式が　TRUE　になるセルですので 文字の色が変わります。（E8セル） なお、１行目には文字列で「0月（半角数字＆月）」と入力してあります。 To sinさん ＞HANAさんのレス以降は加味してません（HANAさんゴメンね）。 私も間の応酬を加味してませんので、許してください。 (HANA) ---- あっ、下の方に　独り言　があった。 と書き込んでたら、衝突。ごちゃ混ぜになるかも？　と思いながらもUPしときます。 集計は、 =SUMPRODUCT((Sheet1!$B$2:$B$65536=$A2)*(TEXT(Sheet1!$F$2:$F$65536,"yyyy/m月"=TEXT(D$2,"yyyy/m月")*Sheet1!$G$2:$G$65536) だと重すぎでしょうから？　行範囲狭くするわけにはいきませんか？ 別案として、Sheet1で条件付き書式で本日分に色付けし、ここでのみ確認。または、 別列を設けて、本日の日付が入力されたら、『1』と出してオートフィルタで抽出・確認。 という感じで、状況変化だけならば、Sheet1のみで、よい様な？？？ 集計表は、ピボットテーブルにして、Sheet1の結果のみを表示・更新してゆくとか・・・ だめでしょうか？　最後の最後は、マクロか。これは私の専門外ですが・・・ (sin)　Sheet1 とSheet2 の入力範囲が、知りたかったりして・・・参考に。。。 ---- sinさん、HANAさん うまく説明できず、混乱させてしまってすみません。 私的にも、一度整理させていただきました。 ↓検索範囲となる元データ a b 　c　　　…　e　　　　f　　　　 1商品 入荷予定 　注文数　　入荷日　　入荷数 2富士通 2007/7/13 　　15　　　2007/7/12　 10 3NEC 2007/7/16 　　31　　　2007/7/14　 31 4DELL 2007/8/1 　　70 5東芝 2007/8/3 　　10 6HP 2007/8/1 　　5 7SONY 2007/7/11 　　27　　　2007/7/11　 25 >>●残りの「5」つが入荷されたときは 　何処にどの様に記入するおつもりですか？ 翌日のデータが、 a b 　c　　　…　e　　　　f　　　　 1商品 入荷予定 　注文数　　入荷日　　入荷数 2富士通 2007/8/27　　5　　　　　　　　　 3DELL 2007/8/1 　　70 4東芝 2007/8/3 　　10 5HP 2007/8/1 　　5 6SONY 2007/8/25 　　2　　　 ↑のように、次回の入荷予定日と変更されて、データが挙がってきます。 そのため残りの個数は、次回の入荷日として扱います。 私が勘違いしているようですみません。 理解力に欠けてしまっていました。 ↓集計結果の表 a b c d e 1商品 7月 8月 2富士通 入荷数 10 3 受注数 15 5 4NEC 入荷数 31 5 受注数 31 0 6DELL 入荷数 7 受注数 70 70 8東芝 入荷数 9 受注数 10 10 10HP 入荷数 11 受注数 5 5 12SONY 入荷数 25 13 受注数 27 2 D2,D4,D6…の受注数には、SHEET1のC列の数値が入るように設定しています。 D3などには、=C3-D2としてきました…。 行範囲は、65536じゃなくても問題ないです…。 大体、7000くらいあれば余裕です、それでも多い気がしますが。 作業列を追加することも問題ありません。 先生方のご提示通り、列を設けてチャレンジしてみます。 まだ試験していませんが、先ずはお礼をば…。 ありがとうございます。 (mei) ---- ふと思った疑問ですが、 すぐ上のmeiさんの書き込みに、 >翌日のデータが、 > 〜〜　略　〜〜 >↑のように、次回の入荷予定日と変更されて、データが挙がってきます。 >そのため残りの個数は、次回の入荷日として扱います。 とありますが、これは、Sheet1のデータが毎日（？）更新されるという事でしょうか？ それとも、毎日更新されるデータをSheet1に追記(コピペ等の方法で）するという事でしょうか？ 前者であれば、月度の集計等は元になるデータがなくなるため、関数処理は出来ない構造のように思います。 マクロなら出来るでしょうが、証拠になる明細が無いというのは、どうなんでしょう？？？ 後者であれば、集計する元データに重複というか不整合要素が生まれるように感じます。 私の勘違いというのなら、問題は無いのでしょうが・・・ っと、何かの参考にでもなればレスでした。。。 (sin) ---- sinさん あ…どうしましょう。 データは毎日SHEET1にコピペしていますので、sinさんの仰っている後者の状態です。 今まで手作業で意識していませんでしたが…。 気づいてくださってありがとうございます！ 入荷が終わってしまったものも残さなければ、月の合計ができないということですもんね…。 困った…(^-^;) 少しデータの転記方法を考えてみます。 (mei) ---- sinさん、HANAさん 本題の目的は、どちらの数式も思い通りの掲示になりました。 ありがとうございます(T-T) ばっちり当日入荷された商品の月が赤くなってました。感動です。 sinさんのご指摘があった新たな問題で、 データ上入荷済みの項目が掲載されていないため、合算できないことについては 話し合いの結果、入荷された商品もそのまま既存させていくことにしました。 例えば、 富士通15件受注され、2007/7/13入荷予定に対して10件入荷され、残り5件は次回入荷待ち状態だとしたら a b 　c　　　…　e　　　　f　　　　 1商品 入荷予定 　注文数　　入荷日　　入荷数 2富士通　2007/7/13　　15　　　2007/7/14　　10 3富士通 2007/8/27　　5　　　　　　　　　 としていこうと思います。あっ…注文数が狂いますね…。 2回目以降の注文数を入力しないようにすればよいのですね。（大きな独り言でした） また追加でグラフ化にしていく方向でいます。 週間グラフにする場合には、 HANAさんの数式では、 =SUMPRODUCT((入荷!$A$2:$A$1000=$B2)*(WEEKDAY(入荷!$B$2:$B$1000)&"週"=D$1)*入荷!$C$2:$C$1000) のようにしていけばよろしいのでしょうか。 sinさんの数式では、 入荷チェックの表示方法を、yyyy/m/d"日"にしてから、月-日形式にするのがよいでしょうか。 (mei) ---- 先の ＞●残りの「5」つが入荷されたときは ＞　何処にどの様に記入するおつもりですか？ この質問は 「履歴はどの様に残りますか？」 を聞く物で、現在履歴もデータとして存在する事に なったようなので良いのですが ＞●「入荷予定 - 注文数」と「○月 - 受注数」が対応していますか？ こちらへのご回答が無い様に感じます。 ＞あっ…注文数が狂いますね…。 「注文数」と「納入予定数」は違うのではないでしょうか。 そして、現在「注文数」として表している数字は 厳密には「納入予定数」と言えると思いますが　違いますかね？ 「注文番号」の様な物はどこかに無いのですか？ （まぁ、其処まで管理するか　と言う問題ですが。） ＞また追加でグラフ化にしていく方向でいます。 コレに関しては、グラフにするためのデータをどの様な表にするか によって、式が変わると思います。 ＞=SUMPRODUCT((入荷!$A$2:$A$1000=$B2)*(WEEKDAY(入荷!$B$2:$B$1000)&"週"=D$1)*入荷!$C$2:$C$1000) WEEKDAY関数は、曜日を表す物です。 曜日毎に集計をなさりたいのなら　それでよいと思いますが 集計したいのは週毎ではないのですか？ sinさんからも返答があると思いますので 私は、過去ログ配達にとどめておきます。 直近で、こちらは個数を数える物ですが　ご参考にどうぞ。 [[20070813173242]]『日付のデータから特定の一週間内の日を数えたい』（sasa） (HANA) ---- HANAさん >>●「入荷予定 - 注文数」と「○月 - 受注数」が対応していますか？ どうお答えすればよいか分からずに流れてしまいました、すみません。 「入荷予定-注文数（表では受注数）」で 「○月-入荷数」で対応していました。 入荷予定は、発注をかけた際の納期と同じです。 >>厳密には「納入予定数」と言えると思いますが　違いますかね？ データと表の文言を変えていたため、混乱させてしまいすみません。 注文数=受注数 お客様から注文を受け、その個数をメーカーに発注していたので 注文数として扱っていました。 確かに納入予定数としたほうが良いかもしれません。 注文番号、発注番号、型番はあります。 情報保護が厳しいため、こちらへ記載したものが一部だったことで 皆様には大変ご迷惑をおかけしています。すみませんでした。 配列が変わってしまいますが、その辺はこちらで変更していました。 正確には、 a　　　　　b　　　 c　　　　 d　　　　　e　　　　 f　　　　g　　　　　h 受注No.　　型番　　商品名　　 メーカー　 入荷予定　注文数　 入荷日　　 入荷数　 xxxx-xx　　xx0000　xx-0000-x　富士通　　 2007/7/13 　15　　 2007/7/12　 10 　・ 　・ 　・ となっています。 a-c列を省略していました。 グラフについてですが、こちらは週毎で行う予定です。 よく読んでいませんでした。てっきり一週間単位のデータを振り分けてくれると勘違いしていました。 読み直したら、WEEKDAY関数とは曜日を1〜7の数値を求めるとかに使用するものなんですね。 過去ログありがとうございます。 もう少し表示方法も考え直してみます。 (mei) ---- 皆様、できました。 ありがとうございます。 (mei) ---- >sinさんからも返答があると思いますので このプレッシャーをどうやって、左に受け流す♪　かと悩んでいたら、解決したようなので、 私の返答は >'''おめでとうー''' です。 (sin)