[[20100920120333]]　『元データの日付を条件に従って別シートに転記した』（ちい）　ページの最後に飛ぶ

 『元データの日付を条件に従って別シートに転記したい』（ちい）

 こんにちは。XP excel2003です。

 前回は[[20100828081924]]『日付一覧を参照して平日の売上だけを集計したい』で
 Byさま、Rさまに大変お世話になりました。

 まだ教えていただいた難しい式で使いこなせないのもありますけれど
 色もつけたりして綺麗な表ができています。ありがとうございました。

 実は今、前回の「元データ」シートを元にして、Sheet2「グラフ」に
 「平日の業務日」と「土曜の業務日」の業務結果を表にして
 その表を範囲選択して表の下にグラフを表示する、ということをしています。

 「元データ」の内容は以下の通りです。（前回Rさまが書いてくださったのを
 使わせていただきます。）

 　　　　A　　　　B　　　　C　　　D　　　E　・・（略）・・ AE　　AF　　AG　　　
 1　　　2010年　　7月
 2　　　　　　　　　　　　1日　　2日　　3日　　　　　　　　29日　30日　31日
 3　　　　　　　　　　　　水　　 木　　 金　　　　　　　　 水　　木
 4      業務A   作業a   数値   数値   数値 ・・（略）・・ 数値   数値   数値
 5      業務A   作業b   数値   数値   数値 ・・（略）・・ 数値   数値   数値
 6      業務A   作業c   数値   数値   数値 ・・（略）・・ 数値   数値   数値
 ・
 ・
 (略)
 ・
 ・
 79

 別シートの「グラフ」は
 　　　　A　　　　B　　　　C　　　D　　　E　・・（略）・・ Q     R    S　　　
 1　　　2010年　　7月
 2　　　業務種類　作業a
 3  　　          6日　   　7日　 　8日　　　　　　　　    29日　30日　31日
 4　　　          火   　　 水　    木　　　　　　　　     木    金　　土
 5      業務A     数値　　数値      数値　　　 　　　　　　数値　数値　数値
 6      業務B     数値    数値      数値                   数値  数値  数値
 7　　　業務C　　 数値    数値      数値　　　　　　       数値  数値  数値

 となっています。行1の「2010年」と「7月」はドロップダウンリスト。
 行2の「作業a」もドロップダウンリストです。行4以降の数値は
 自力で勉強して例えばB5ならば
 =INDEX(業務A,MATCH($B$2,元データ!$B$6:$B$27,0),MATCH(B$3,元データ!$C$3:$AG$3,0))   
 として正しい数値が入力されました。この「業務A」は「元データ」の業務Aの
 データ範囲に「業務A」という名前をつけています。そして行3,5,6,7をグラフに
 しました。

 前置きが長くなってすみません。

 問題は、行3の日付なのですが、今は元のシートを見ながら手入力を
 していますが、自動的に入力されるようにしたいのです。

 ここでいう日付とは、「業務日」シートにあるデータに名前をつけたもので、
 「平日の業務日」と「土曜の業務日」（他に「単独の業務日」もありますが）
 のことです。

 「元データ」シートには固定で行2に１日から３１日までの日付が
 入っていて、対応した曜日が行3に入力されているのです。B1の月を
 変えるとこの行3の曜日が変わります。（月が変わってもエラーが出ない
 方法を教えていただきました）

 つまり、毎月の「平日の業務日」と「土曜の業務日」が一定していないので、
 「業務日」シートに各月の業務日を貼り付け名前をつけて、「元データ」の
 曜日に条件付き書式で色をつけています。

 つまりやりたいことは、「元データシート」のその月の「平日の業務日」と
 「土曜の業務日」を「グラフ」シートのA3から右に向かって自動で
 貼り付けたいのです。業務日以外はデータがないので。

 これまで教えていただいたSUMPRODUCTなどを使ってできるのか、他の関数が
 必要なのかどうしてもわかりません。

 再度ご指導いただけますでしょうか。

 なにとぞよろしくお願いいたします。

 ちい

 >「業務日」シートに各月の業務日を貼り付け名前をつけて、

 「各月の」という表現が気になるんですけど、月別ではなく1年分（かどうか知りませんが）の日付が
入力されてるんですよね？

 「平日の業務日」と「土曜の業務日」というのは、それぞれ「業務日」シートのどこからどこまでですか？

 例えばですが「平日の業務日」が　A1:A100、「土曜の業務日」が　B1:B100　のように隣接していれば
 業務日!$A$1:$B$100　と、一つの範囲で表せます。

 B3セル
 =IF(SUM((TEXT(業務日!$A$1:$B$100,"yyyy年m月")=$A$1&$B$1)*1)<COLUMN(A1),"",SMALL(IF(TEXT(業務日!$A$1:$B$100,"yyyy年m月")=$A$1&$B$1,業務日!$A$1:$B$100),COLUMN(A1)))

 これは配列数式です。
 Ctrl＋Shift＋Enter（CtrlキーとShiftキーを押しながらEnter）で数式を確定して下さい。
 数式バーを見て、数式が {　} で囲まれていればOKです。

 確定後、ユーザー定義を　d"日"　にして、AF3セルまでオートフィル

 B4 =TEXT(B3,"aaa")　普通に Enter だけで確定し、AF4セルまでオートフィル

 ※ 3行目の日にちがシリアル値で、4行目の曜日は文字列です。
　
　
 > =INDEX(業務A,MATCH($B$2,元データ!$B$6:$B$27,0),MATCH(B$3,元データ!$C$3:$AG$3,0))

 $B$6:$B$27　になっていますが、なぜ6行目からなんでしょう？
 4行目からではないのですか？

 B5 =IF(B$3="","",INDEX(業務A,MATCH($B$2,元データ!$B$4:$B$27,0),MATCH(B$3,元データ!$C$3:$AG$3,0)))
　
　
 > 行1の「2010年」と「7月」はドロップダウンリスト。
　
 これ、まさかグラフシートもドロップダウンリストじゃないでしょうね？
 グラフシートは　
 A1 =元データ!A1
 B1 =元データ!B1
 でいいと思います。

 （R）

 ↑ の追記

 どこでもいいんですが、例えば C1セルに

 =SUMPRODUCT((TEXT(業務日!A1:B100,"yyyy年m月")=A1&B1)*1)

 Enterだけで確定、表示形式「標準」
 指定月の業務日数です。

 こうしておけば

 B3 =IF($C$1<COLUMN(A1),"",SMALL(IF(TEXT(業務日!$A$1:$B$100,"yyyy年m月")=$A$1&$B$1,業務日!$A$1:$B$100),COLUMN(A1)))

 Ctrl＋Shift＋Enter で確定し、ユーザー定義　d"日"
 AF3セルまでオートフィル

 単に数式が短くなるというだけでなく
 同じ計算を何度もしなくて済むというメリットがあります。

 ところで「単独の業務日」という範囲の日付は、今回は無視していいんですよね？

 （R）

 > B5 =IF(B$3="","",INDEX(業務A,MATCH($B$2,元データ!$B$4:$B$27,0),MATCH(B$3,元データ!$C$3:$AG$3,0)))

 ↓ でも同じ結果になると思います。

 B5 =IF(B$3="","",INDEX(業務A,MATCH($B$2,元データ!$B$4:$B$27,0),DAY(B$3)))

 （R）

 Rさま、お返事をありがとうございます。

 >「平日の業務日」と「土曜の業務日」というのは、それぞれ「業務日」シートの
 どこからどこまでですか？
 例えばですが「平日の業務日」が　A1:A100、「土曜の業務日」が　B1:B100　のように隣接していれば
 業務日!$A$1:$B$100　と、一つの範囲で表せます。

 「業務日」シートには７月なら７月の業務日しかいれていません。実はこれがとても問題があって
 例えば、７月のブックが終了して８月を作るのに「名前をつけて保存」で新しいブックを
 作るのですが、その都度他の「月別業務日程表」というブックから日付をコピペして業務日シートに
 貼りつけるということをしています。でもそうすると、「平日の業務日」とか「土曜の業務日」とかの
 名前範囲がガタガタになっちゃって、挿入→名前→定義に行って、なぜか他のブックからの参照に
 変わってしまっているのを手作業で当ブックの参照に直しています。
 Rさまのおっしゃるように、数か月分でもいいから(一年分はないので..たぶん）
 「業務日シート」に入れて、月が変わってもなにもしなくていいようにしようと思っています。

 なので、今は「業務日」シートのB1:B22が平日業務日でC1:C22が土曜業務日、D1:D22が単独日で
 A1:C22を業務日としています。グラフのためにA1:A50には平日業務日と土曜業務日全部入れて
 通常業務日としています。

 B:BとC:CがA:Aにだぶっている、A:A,B:B,C:CがA1:C22にだぶっているとか
 おかしなことがあるのですが、とりあえず正しい数字が取れているのでこのまま使っています。

 なので、Rさまに作っていただいた式の「業務日!$A$1:$B$100」の部分は
 「通常業務日」という範囲の名前に置き換えました。ここの説明が最初の
 質問から抜けていてすみませんでした。

 あと今回は単独日は考慮に入れていません。単独日は極端に数値が低いので他の業務日と
 一緒にグラフにするには比較にならないのです。ご配慮ありがとうございます。

 B3=
 =IF(SUM((TEXT(通常業務日,"yyyy年m月")=$A$1&$B$1)*1)<COLUMN(A1),"",SMALL(IF(TEXT(通常業務 日,"yyyy年m月")=$A$1&$B$1,通常業務日),COLUMN(A1)))

 この式、全然わかりません。でもちょっとあせっているものですから、とにかく入れてみたら
 本当に７月の正しい通常業務日だけが自動入力されました。びっくりしました。Rさますごいです・・・

 実は最初はCtrl+Shift+Enterを押してなくて、Enterだけ押すと、通常業務日のところが
 ブックの名前に変化してしまい、原因が分らなくてまた２時間かかりました。
 配列ですね。勉強します。そういえばByさまが前回書いてくださった式にも{}が入っていて
 まだ理解できていませんでした。

 質問があります。

 >確定後、ユーザー定義を　d"日"　にして、AF3セルまでオートフィル

 ①ここでAF3までオートフィルするのはなぜですか？通常業務日数はだいたい
 18から23日くらいなのですが。

 ②グラフの元データ範囲がB3:S3（通常業務日）,A7:A9（業務A,B,C),B7:S9（数値）なのですが、
 B3:S3のユーザー定義を　d"日"とするとグラフの横軸の日付が「通常業務日」以外の日付も全て
 入ってしまうのです。今日Rさまに式を教えていただくまえは手入力をしていたのですが
 やはり日付表示にすると、横軸が月の日にち全てになってしまうので、行4はシリアル値のままで
 行3にコピーしてユーザー定義をd"日"として業務日がみんなに見えるようにして
 実際のグラフの元データとしては行4を使っています。（行3がシリアル値のままじゃおかしいので
 行4は文字色を白にして目隠ししています。）

 今日Rさまに教えていただいた方法でも、行3をd"日"表示にするとグラフに日付が
 全部出てしまいます。シリアル値のままにするとグラフが業務日だけで正しく表示されます。

 とても不思議です。もしもグラフのことお分りになりましたらお教えいただけますか？

 とりあえず、行3はシリアル値のままにして、文字色を白にして隠して、グラフの範囲として設定、
 行4は日付と曜日が分かるようにするために、=TEXT(B3,"d(aaa)")としました。
 せっかく教えていただきましたのに変なことしてごめんなさい。
　
 > =INDEX(業務A,MATCH($B$2,元データ!$B$6:$B$27,0),MATCH(B$3,元データ!$C$3:$AG$3,0))
 $B$6:$B$27　になっていますが、なぜ6行目からなんでしょう？
 4行目からではないのですか？

 これも言葉が足りずにすみません。前回ご相談したときには4行目からデータが
 始まったのですがその後、日々の業務数値の傾向性を知るために、
 元データシートの行4と行5に業務日の特徴（例えば、非業務日明けとか
 一つの業務のサイクルの何日目に当たるかという特徴です。）これがグラフのシートの行5と
 行6にも入っています（手入力__;)。今思いつきましたが、いただいた式を（B3の）B5とB6にも使ってみようと思います。

 なので
 B5 =IF(B$3="","",INDEX(業務A,MATCH($B$2,元データ!$B$6:$B$27,0),MATCH(B$3,元データ!$C$3:$AG$3,0)))
 とさせていただきました。IFを入れるのはやっぱりエラー予防ですね!まったく思いつきませんでした。本当にありがとうございます。
　
 > 行1の「2010年」と「7月」はドロップダウンリスト。
　 これ、まさかグラフシートもドロップダウンリストじゃないでしょうね？

 はい、そのまさかでした。ドロップダウンリストにしていました。自分でもなんか
 へんだなとは思いながらそのままにしていました。A1 =元データ!A1とB1=元データ!B1にしました。すみません。

 >どこでもいいんですが、例えば C1セルに
 >=SUMPRODUCT((TEXT(業務日!A1:B100,"yyyy年m月")=A1&B1)*1)
 >Enterだけで確定、表示形式「標準」
 >指定月の業務日数です。

 すごいです!確かに業務日数がグラフの上にあるとすごくまとまりますね！
 これを参考にして、その月の 平均数値とか合計数値とか、元データでパッと目が
 行きにくいデータをグラフシートに表示しようと思います。感激です！ありがとうございます。
 業務日!A1:B100のところ「通常業務日」に置き換えました。

 それと、この式なら=A1&B1が入っているから業務日シートに月の異なる
 業務日を入れて一つの範囲の名前をつけても、該当月の業務日だけ拾ってくれそうです！
 これから解決しなければならない問題まで片付いてしまいそうです！！！

 >B5 =IF(B$3="","",INDEX(業務A,MATCH($B$2,元データ!$B$4:$B$27,0),DAY(B$3)))
 これありがとうございます。DAYってシンプルですね。

 グラフのデータで不思議なことが残りますが、とりあえず白で隠しちゃったので
 形は整いそうです。

 上司が今週くらいに「会議行くから資料ちょーだい」って言い出しそうなので、またSOSして
 しまいました。いつも親身なご指導と、まだ見えていない先の問題の解決までご指導をいただき
 本当に感謝しています。こちらの表を見ていただいていないのに、私よりも
 表の状況を把握していらっしゃるのでいつもびっくりします。式の完璧さも魔法みたい・・・(..?)

 これから
 >=IF(SUM((TEXT(通常業務日,"yyyy年m月")=$A$1&$B$1)*1)<COLUMN(A1),"",SMALL(IF(TEXT(通常業務日,"yyyy年m月")=$A$1&$B$1,通常業務日),COLUMN(A1)))
 と
 >=SUMPRODUCT((TEXT(業務日!A1:B100,"yyyy年m月")=A1&B1)*1)
 を分解して勉強してみます。いつも関数の,ごとに改行してヘルプを見ながら・・時間かかりますが。

 また助けていただくことがあると思います。どうぞ今後ともよろしくお願い申し上げます。

 ちい

 > 質問①
 > AF3までオートフィルするのはなぜですか？

 B列から数えて31列目がAF列だからです。
 ここまでコピーしておけば、1ヶ月まるまる全部が業務日なんてことがあっても
 対応できますよね。

 まあそんなことはないだろうとは思いつつも、業務日数が最大何日かなんて見当もつきませんし、
 注釈入れるのも面倒だし（オイ）、多少多めにコピーしても問題ないでしょということで
 AF3までオートフィルというコメントに至った次第です。

 多くても23日で、24日以上になることはありえないということなら、X列までコピーしておけばいいということですね。

 > 質問②
 > 日付表示にすると、横軸が月の日にち全てになってしまう

 ↑ だけのことなら、グラフを選択して右クリック → グラフのオプション → 軸タブ
「Ｘ項目軸」が「自動」になっていると思いますが「項目」を選択してみてください。
 抽出された日付だけの表示になりませんか？
　
　
 それと、C1セルに ↓ の数式を入れているのなら
 =SUMPRODUCT((TEXT(通常業務日,"yyyy年m月")=A1&B1)*1)
　
 B3セルは ↓ でいいんですよ。

 =IF($C$1<COLUMN(A1),"",SMALL(IF(TEXT(通常業務日,"yyyy年m月")=$A$1&$B$1,通常業務日),COLUMN(A1)))

 Ctrl＋Shift＋Enter で確定することに違和感があるというか、
 そんなこといちいち覚えてられるかあっ！とかいった率直なご感想をお持ちなら
 一応お知らせしておきますが ↓ のようにすれば Enter だけの確定でいけると思います。

 =IF($C$1<COLUMN(A1),"",SMALL(INDEX((TEXT(通常業務日,"yyyy年m月")<>$A$1&$B$1)*10^5+通常業務日,0),COLUMN(A1)))

 ※ C1セルに、上の SUMPRODUCT の数式が入っていることが前提です。
 ※ ただし、通常業務日の範囲内に文字列（数式の""など）があるとエラーになります。
　
　
 Enterだけで確定できるといっても、数式の意味はかえってわかりづらいですよね。

 業務日シートに作業列を使ってもよければ、もっとシンプルな数式でできます。
 ついでですので、その方法も書いておきますね。

 ■業務日シート
 通常業務日の範囲は A1:A50 とのことですが、数ヶ月分の日付を入力する予定なら
 もう少し範囲を広くとった方がいいので、一応 A1:A100 とします。

 作業列はどこでもいいのですが、B列からD列は使用中のようですので
 仮にここではE列にします（列を挿入してB列を作業列にしてもかまいません）

 E1 =IF(TEXT(A1,"yyyy年m月")=元データ!$A$1&元データ!$B$1,A1,"")

 表示形式を「日付」にして E100セルまでオートフィル

 ■グラフシート
 C1 =COUNT(業務日!E1:E100)　　← 業務日数

 B3 =IF($C$1<COLUMN(A1),"",SMALL(業務日!$E$1:$E$100,COLUMN(A1)))

 ユーザー定義を d"日" にして（←グラフの問題が解決していればですけど）X3セルまでオートフィル
 確定はもちろん Enter だけでOKです。

 （R）

 Rさま、こんばんは！

 >まあそんなことはないだろうとは思いつつも、業務日数が最大何日かなんて見当もつきませんし、
 >注釈入れるのも面倒だし（オイ）、多少多めにコピーしても問題ないでしょということで
 >AF3までオートフィルというコメントに至った次第です。

 よくわかりました。説明が足りない質問に想像力をフル回転して、回転レシーブもして、
 アドバイスを下さるみなさまのご苦労がとっても理解できました。

 とりあえずXまでコピーしました。確かに、つきによって業務日数に幅があるので
 元データシートと同じように設定の日数に足りない部分がエラーが出ると
 気になりますので。でもRさまのはエラーが出ないはずですよね。

 >「Ｘ項目軸」が「自動」になっていると思いますが「項目」を選択してみてください。
 >抽出された日付だけの表示になりませんか？

 できました！Ｘ軸で項目を選択したらちゃんとなりました！！！なので
 行3はd"日"で行4はaaaで、Rさまご指示のようにちゃんと表示できて、グラフも
 必要な業務日だけ表示できました。

 Rさまはグラフの達人でもいらっしゃるのですね！！ありがとうございます。
 （「不思議」・・って(・o・?)、自分は思考停止状態かもしれない・・・(__)）
 ・・・でも最近は括弧の数は気になるようになりました・・

 B5は
 =IF($C$1<COLUMN(A1),"",SMALL(IF(TEXT(通常業務日,"yyyy年m月")=$A$1&$B$1,通常業務日),COLUMN(A1)))
 としました。もちろん今は「Ctrl+Shift+Enter」!を忘れずに入れてます。

 「そんなこといちいち覚えてられるかあっ！」なんてすごいことを
 考えているわけではないのですが あわてていると、いただいた式の
 意味も考えずに、ついふらふらっと

 「コピー＋貼り付け＋エンター+できたあ！」＋２時間ミスに気がつかない・・

 という、安直スパイラルにはまってしまって・・・すみません。配列、勉強します。

 作業列の作り方もご提示いただきましたので、やってみますね。
 配列はこれからも避けて通れなさそうなので。 配列というのは、
 作業列を最初に作ってからやるものですよね。

 前回の質問から大変お世話になってありがとうございます。

 Rさまも、お忙しいのに（きっと）朝早くにお返事をいただき
 ありがとうございました。

 たくさんの貴重なご指導に言葉では尽くせないほどに感謝をしております。

 本当にほんとうにありがとうございます。

 日中に会社で自分の仕事（会社のだけど）に専念できる日とできない日があり、
 お返事が 遅くなり申し訳ありませんでした。

 今後ともどうぞどうぞよろしくお願いいたします。

 ちい

コメント返信：

[ 一覧(最新更新順) ]

[[20100920120333]] 『元データの日付を条件に従って別シートに転記した』（ちい） ページの最後に飛ぶ

[[20100920120333]]　『元データの日付を条件に従って別シートに転記した』（ちい）　ページの最後に飛ぶ