エクセルの学校：全文検索：Hyper Estraier：平均平日

Results of 1 - 1 of about 28 for 平均平日 (0.001 sec.)

平均 (1364), 平日 (459)

[[20100920120333]]: #score: 10346; @digest: da6a4a7f7445e88c48ffc42f052e58fe; @id: 51410; @mdate: 2010-09-22T14:26:51Z; @size: 16590; @type: text/plain; #keywords: 務日 (140067), 値数 (77080), 常業 (74918), 務a (70021), 業務 (50260), 日!$ (26642), 日," (25552), タ!$ (20443), 年m (15572), の業 (13256), 月") (10815), 元デ (10022), 行3 (9772), 行4 (9353), 平日 (8576), 土曜 (6107), 日」 (6098), 通常 (5942), 日" (4541), ラフ (4160), プダ (4146), 数値 (3939), ンリ (3765), グラ (3737), ドロ (3221), 日数 (3063), 確定 (2868), yyyy (2861), column (2094), デー (1948), トフ (1931), フの (1919); 『元データの日付を条件に従って別シートに転記したい』（ちい）
こんにちは。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に1日から31日までの日付が入っていて、対応した曜日が行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 と、一つの範囲で表せます。「業務日」シートには7月なら7月の業務日しかいれていません。実はこれがとても問題があって例えば、7月のブックが終了して8月を作るのに「名前をつけて保存」で新しいブックを作るのですが、その都度他の「月別業務日程表」というブックから日付をコピペして業務日シートに貼りつけるということをしています。でもそうすると、「平日の業務日」とか「土曜の業務日」とかの名前範囲がガタガタになっちゃって、挿入→名前→定義に行って、なぜか他のブックからの参照に変わってしまっているのを手作業で当ブックの参照に直しています。 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))) この式、全然わかりません。でもちょっとあせっているものですから、とにかく入れてみたら本当に7月の正しい通常業務日だけが自動入力されました。びっくりしました。Rさますごいです・・・実は最初はCtrl+Shift+Enterを押してなくて、Enterだけ押すと、通常業務日のところがブックの名前に変化してしまい、原因が分らなくてまた2時間かかりました。配列ですね。勉強します。そういえばByさまが前回書いてくださった式にも{}が入っていてまだ理解できていませんでした。質問があります。 >確定後、ユーザー定義を d"日" にして、AF3セルまでオートフィル @ここでAF3までオートフィルするのはなぜですか?通常業務日数はだいたい 18から23日くらいなのですが。 Aグラフの元データ範囲が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列までコピーしておけばいいということですね。 > 質問A > 日付表示にすると、横軸が月の日にち全てになってしまう ↑ だけのことなら、グラフを選択して右クリック → グラフのオプション → 軸タブ「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さまのはエラーが出ないはずですよね。 >「X項目軸」が「自動」になっていると思いますが「項目」を選択してみてください。 >抽出された日付だけの表示になりませんか? できました!X軸で項目を選択したらちゃんとなりました!!!なので行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」!を忘れずに入れてます。「そんなこといちいち覚えてられるかあっ!」なんてすごいことを考えているわけではないのですがあわてていると、いただいた式の意味も考えずに、ついふらふらっと「コピー+貼り付け+エンター+できたあ!」+2時間ミスに気がつかない・・という、安直スパイラルにはまってしまって・・・すみません。配列、勉強します。作業列の作り方もご提示いただきましたので、やってみますね。配列はこれからも避けて通れなさそうなので。配列というのは、作業列を最初に作ってからやるものですよね。前回の質問から大変お世話になってありがとうございます。 Rさまも、お忙しいのに（きっと）朝早くにお返事をいただきありがとうございました。たくさんの貴重なご指導に言葉では尽くせないほどに感謝をしております。本当にほんとうにありがとうございます。日中に会社で自分の仕事（会社のだけど）に専念できる日とできない日があり、お返事が遅くなり申し訳ありませんでした。今後ともどうぞどうぞよろしくお願いいたします。ちい ...; http://www.excel.studio-kazu.jp/wiki/kazuwiki/201009/20100920120333.txt - [detail] - similar

PREV NEXT

訪問者：カウンタ