[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『日付一覧を参照して平日の売上だけを集計したい』（ちい）
はじめまして。エクセル2003です。
C3:AG3に1日から31日までの日付のシリアル値を入力しています。
D4:AG4には各日にちの売上げが数値で入力されています。
たとえば、E4ににC3:AG3の火曜から金曜日までの売上の合計を
出したいのですが、関数がわかりません。
今できている関数は
=SUMPRODUCT(WEEKDAY(C3:AG3,2)<6,C4:AG4)
です。＃REFというエラーがでてしまいます。
どなたか正しい数式を教えていただけませんでしょうか。
また、火曜から金曜だけの計算をしたいのですが、上の式だと
月曜から金曜を指定しています。火曜：金曜だけの指定の仕方も
教えていただけますでしょうか。
初心者で申し訳ありません。よろしくお願いします。
=SUMPRODUCT((WEEKDAY(C3:AG3,2)<6)*(C4:AG4)) ではどうでしょう？ エラーはでますか？ (bbq)
お返事をありがとうございます。
早速やってみましたが同じ#REFでした。
SUMPRODUCTの関数がよく分っていないのかもしれないのですが
SUMなのに*というのがわかりません。
まだまだ勉強が足りなくてすみません。
こんにちは。 そもそも参照範囲（C3:AG3,C4:AG4）の中に#REF!となっているセルがありませんか？
（コタ）
入ってました、たくさん！
コタさんありがとうございます。
bbqさんすみませんでした。
実は行４には複雑な式が入っていて、INDIRECTで他のブックの
日付ごとのシートのセルを参照しています。
その元のブックを開けていなかったので、＃REFがたくさん・・・
もう一度やってみます。
実は、元ブックを開けていても事情があって#N/Sというエラーが
どうしても出てしまうセルがあるのですが、やはりSUMPRODUCTを
入れるとエラーがでるのでしょうか。
#N/Sのエラーを消すためのISERRORを使ってみようかと思っているのですが
元の数式が複雑すぎて、どこに入れればいいのか苦戦しています。
今日これから仕事に出かけなければならないのですが、会社から
このサイトに来れたら、#REFを消してbbqさまの式をもう一度試してから
ご報告します。会社のPCで見れなかったら、夜か明日になるかも。
また、INDIRECTの複雑な式にISERRORを追加する式も見ていただければと
思います。もうこれはVBAにするしかないのかと考えていますが
私の実力では・・・とほほな状況です。
でも、月曜日にVBAの基礎一日セミナーを受講する予定なので(*^_^*)
関係ないことですみませんでした。
また後ほど更新させていただきます。どうぞよろしくお願いいたします。
ちい
=SUMPRODUCT((WEEKDAY(C3:AG3,2)<6)*(C4:AG4))
を入れてみたのですが、今度は「#VALUE!」が出ました。
試しに「*」を「,」に代えてみたら答えは「0」でした。
C3:AG3にもC4:AG4にもエラー値はありません。
もう一度表の仕組みを説明させてください。
C3:AG3には日付（=DATEVALUE($A$1&C2)）が入っています。
表示形式はユーザー定義で(aaa)です。
A1は月でC2:AG2はtextで「1日」から「31日」と入力しており、
これが他のブックのシート名でINDIRECT関数を使って
4行目以降に販売データが自動的に入力される仕組みです。
C D E F G
2 1日 2日 3日 4日 5日 → textで別ブックのシート名
3 木 金 土 日 月 → =DATEVALUE($A$1&C2)
4 3 23 25 26 7 → INDIRECT関数ですが、コピー値のみ貼り付けして式無し
という表です。
行2の数式がいけないのでしょうか。
またお知恵をいただけますでしょうか。
よろしくお願いいたします。
ちい
ブランクを無視して計算する方法はありますか？
お世話をおかけします。どうかよろしくお願いします。
ちい
AE3:AG3にはどんな数式が入っているのでしょうか？
> C3:AG3には日付（=DATEVALUE($A$1&C2)）が入っています。
この式だと、A1が2月とか9月のとき #VALUE!エラーになりませんか？
C3 =TEXT($A$1&C2,"aaa;;;") 表示形式を「標準」にして AG3セルまでフィルコピー
> 火曜から金曜だけの計算をしたい
=SUM(SUMIF(C3:AG3,{"火","水","木","金"},C4:AG4))
月だけで、年は指定しなくてもいいのか？ という疑問が残りますが････ 【追記】 不測のトラブルを避けるためにも年を指定した方がいいと思いますよ。
A B 1 2010年 9月
A1に年、B1に月を指定して
C3 =TEXT($A$1&$B$1&C2,"aaa;;;") 表示形式を「標準」にして AG3セルまでフィルコピー
（R）
AE3:AG3にはどんな数式が入っているのでしょうか？
C3の1日から数式が始まっていますので、AE3:AG3は
同じく=DATEVALUE($A$1&C2)です。AE3は29日に対応する
（木）で、AF3が（金）、AG3が（土）です。言い忘れましたが
これはA1に７月が入っているケースです。
> C3:AG3には日付（=DATEVALUE($A$1&C2)）が入っています。 この式だと、A1が2月とか9月のとき #VALUE!エラーになりませんか？ C3 =TEXT($A$1&C2,"aaa;;;") 表示形式を「標準」にして AG3セルまでフィルコピー
うるう年とか日数の足りない月に問題があるのでしょうか。
両方とも試してみます。
> 火曜から金曜だけの計算をしたい
=SUM(SUMIF(C3:AG3,{"火","水","木","金"},C4:AG4))
月だけで、年は指定しなくてもいいのか？ という疑問が残りますが････
これやってみました。戻った値は「0」です（泣）
で、以下のように実際の表示に合わせて曜日を()で括りましたが
結果は同じです。（泣）
=SUM(SUMIF(C3:AG3,{"(火)","(水)","(木)","(金)"},C4:AG4))
bbqさまが教えてくださった
=SUMPRODUCT((WEEKDAY(C3:AG3,2)<6)*(C4:AG4))を入れると
エラーが出るので、行4にデータの入っている連続した日だけを
足すようにすると正確な数字が出ますがブランクのセルも範囲に
入るとエラーになります。
【追記】 不測のトラブルを避けるためにも年を指定した方がいいと思いますよ。 A B 1 2010年 9月 A1に年、B1に月を指定して C3 =TEXT($A$1&$B$1&C2,"aaa;;;") 表示形式を「標準」にして AG3セルまでフィルコピー
おっしゃること分かるような気がします。
これもやってみますね。
今、目先の問題はSUMところです。
もう一度お力をお貸しくださればとっても嬉しいです。
どうぞよろしくお願いします。すみません。
ちい
SUMPRODUCT関数を理解していれば問題ないと思いますが・・・
=SUMPRODUCT((WEEKDAY(C3:AG3,2)<6)*C4:AG4) でエラーが出る理由は 範囲 C4:AG4 に空白文字（""）があるためと思います
=SUMPRODUCT(WEEKDAY(C3:AG3,2)<6,C4:AG4) で 0 になる理由は WEEKDAY(C3:AG3,2)<6 が論理値 TRUEまたはFALSE かえすため SUMPRODUCT関数は 数値以外なので0とみなすためと思います
上記2点を解消すればいいので =SUMPRODUCT((WEEKDAY(C3:AG3,2)<6)*1,C4:AG4)
「,」と「*」を使用する場所や違いによって計算結果も変わります
By
> =SUM(SUMIF(C3:AG3,{"火","水","木","金"},C4:AG4))
この数式は 3行目が =TEXT($A$1&C2,"aaa;;;") または A1が年、B1が月なら =TEXT($A$1&$B$1&C2,"aaa;;;") になっていることが前提ですよ？ 3行目の数式を変更しましたか？
曜日を( )付きにしたいのであれば
C3 =TEXT($A$1&C2,"(aaa);;;") または =TEXT($A$1&$B$1&C2,"(aaa);;;")
火曜から金曜までの合計は
=SUM(SUMIF(C3:AG3,{"(火)","(水)","(木)","(金)"},C4:AG4))
3行目はシリアル値でないと困るという事情があるなら、それでもできますけどどうしますか？
3行目をシリアル値とする場合 > =SUMPRODUCT((WEEKDAY(C3:AG3,2)<6)*1,C4:AG4)
↑ のようにしても2月とか9月でエラーになることに変わりはありません。 3行目の数式を、2月とか9月でもエラーにならないように（「""」にする）変更して、さらに
=SUMPRODUCT((WEEKDAY(0&C3:AG3,2)<6)*1,C4:AG4) ← 0&C3:AG3 にする
でも、求めたいのは火曜から金曜の合計ですよね？ > うるう年とか日数の足りない月に問題があるのでしょうか。
試してみればすぐわかると思いますが、2月とか9月だとエラーになりませんか？
（R）
（R）さんへ
>↑ のようにしても2月とか9月でエラーになることに変わりはありません。 >3行目の数式を、2月とか9月でもエラーにならないように 何を根拠にこのような記載をするのでしょう
質問者の質問文から抜粋 >C3:AG3に1日から31日までの日付のシリアル値を入力しています。
>=SUMPRODUCT((WEEKDAY(C3:AG3,2)<6)*(C4:AG4)) を入れてみたのですが、今度は「#VALUE!」が出ました。 >試しに「*」を「,」に代えてみたら答えは「0」でした。 >C3:AG3にもC4:AG4にもエラー値はありません
（R）さんご指摘のように、2月とか9月にエラーになる可能性はありますが・・・
私は、質問文を前提に回答しています。 (C3:AG3がシリアル値、C3:AG3にもC4:AG4にもエラー値はありません)
By
> 何を根拠にこのような記載をするのでしょう
根拠 ↓ > AE3:AG3は同じく=DATEVALUE($A$1&C2)です。 > C3:AG3にもC4:AG4にもエラー値はありません。
7月でしか試してないからでしょう。 質問者さんへ
これは月別にシートを分けてるんでしょうか？ そうだとしても、2月の場合はエラー処理が必要になりますね。
（R）
（R）さんの回答が、合っているとか、間違っているというような論争をする気はありません
前回も記載しましたが、質問文を前提に回答しています
因みに、憶測で・・・
>2 1日 2日 3日 4日 5日 → textで別ブックのシート名 1日から31日が直接入力ではないようです 2月の場合 AG2 がどのような値なのか不明です 空白・エラー・その他 （たぶんエラーになるのかな・・・エラーを気にせずに式を立てる質問者のようなので）
私の回答で、エラーになるとの質問者のコメントがあれば、そのときには、 私は、原因を追求するための質問をしようとおもっています
個人的に、質問者のカレンダーの作成方法について 変更可能なのか、 または、作り方の質問があれば いろんな回答があると思います 他の回答者も無駄なことをしているなぁ と思っているのではないですか
By
質問者さんへ
> C2:AG2はtextで「1日」から「31日」と入力しており
この text というのはTEXT関数のことですか？ それともテキスト、つまり数値ではなく文字列で入力しているという意味ですか？
たぶん後者だろうと思うのですが もし TEXT関数なら 2行目がどんな数式なのかを提示して下さい。
> 行2の数式がいけないのでしょうか。
2行目の数式が提示されていないので答えようがないんですが これは行3の間違いですか？
（R）
>INDIRECTで他のブックの日付ごとのシートのセルを参照しています。 >また、INDIRECTの複雑な式にISERRORを追加する式も見ていただければと思います。 式を提示ください ブックは月ごとなのでしょうか ブックに2月、9月の場合31日というシートが存在しますか?
>A1は月でC2:AG2はtextで「1日」から「31日」と入力しており、これが他のブックのシート名 >でINDIRECT関数を使って 4行目以降に販売データが自動的に入力される仕組みです。
>2 1日 2日 3日 4日 5日 → textで別ブックのシート名 >3 木 金 土 日 月 → =DATEVALUE($A$1&C2)
>行2の数式がいけないのでしょうか。
2行目はどのような式なのでしょう
回答内容によって、今までの回答すべてが無になってしまうかもしれませんね
3行目の曜日は必要ですか
参考までに C2=DATE(2010,A1,1) D2=C2+1 D2からAG2までフィルコピー 書式 セル ユーザー定義 d(aaa)
AD2:AG2 を選択して 条件付き書式 数式が =MONTH(AD2)<>$A$1 セルの色 白
=SUMPRODUCT((WEEKDAY(C2:AG2,2)<6)*(MONTH(C2:AG2)=$A$1),C4:AG4)
4行目の式は 質問者の返事待ちとします
By
> =SUMPRODUCT((WEEKDAY(C2:AG2,2)<6)*(MONTH(C2:AG2)=$A$1),C4:AG4)
質問は火曜から金曜の合計では？
=SUMPRODUCT((WEEKDAY(C2:AG2-1,2)<5)*(MONTH(C2:AG2)=$A$1),C4:AG4)
（R）
火曜から金曜の合計の場合 =SUMPRODUCT((WEEKDAY(C2:AG2,2)<6)*(WEEKDAY(C2:AG2,2)>1)*(MONTH(C2:AG2)=$A$1),C4:AG4)
By
仕事中でゆっくり読めなくて会話についていけてません。
なるべく早く読ませていただき、頭を整理してお返事いたします。
どうぞよろしくお願いいたします。
ちい
今帰って拝見しました。
お返事することがたくさんありますので
明日の朝にさせていただけますでしょうか。
私の説明に詳細が欠けていたためかご迷惑をおかけしてしまった
ようです。
でも、bbqさま、コタさま、Rさま、Byさまのどのアドバイスも
私には無駄ではありませんし、
私の表と皆様の解釈の間に大きな齟齬もないと
思っています。
いただいた数式を自分なりに理解しながら
お返事をしているため時間がかかっております。
ただ、Rさまの
この数式は 3行目が =TEXT($A$1&C2,"aaa;;;") または
A1が年、B1が月なら =TEXT($A$1&$B$1&C2,"aaa;;;") になっていることが前提ですよ？ 3行目の数式を変更しましたか？
は、私の未熟と早とちりで3行目との関連を考えずに
> =SUM(SUMIF(C3:AG3,{"火","水","木","金"},C4:AG4))のみを
使ってしまいました。すみませんでした。
ByさまのSUMPRODUCTのアドバイスにも
目から鱗で、
エクセルのヘルプを
読んで、*1で1を掛けてfaultとtrueを数値データに
変換する必要があるということが分りました。
bbqさま、コタさまのご助言も初心者の私が更に前に進む一歩を
くださいました。
未熟と不注意をお詫びします。
会社ではネットへのアクセスが自動的に制限、
監視されているため
もっぱらエクセルでいただいた数式をあれこれと試していました。
もう一度皆様からのコメントに対して私からのお返事を整理して
明日更新させていただきます。
ほんとうにありがとうございます。
これからもよろしくお願いたします。
ちい
もう一度表の構造をご説明させてください。
行１はタイトル、A1は月ですが、このフォーマットを年間で使いまわそうと 思ったので、ドロップダウンで1月から12月までのリストにしました。
行2はC2:AG2には直接入力の文字列で「1日」、「2日」、「3日」、「4日」・・・ と始まりAG2は「31日」で終了しています。AH3からはブランクです。 そして「1日」、「2日」・・・というのは「元データ」の ブックのシートの名前でもあります。つまり「元データ」のブックには 「1日」、「2日」、「3日」・・・「31日」という名前で31シート （プラス集計シート,etc)があるということです。
元データは月ごとにブックを作っていますが、シートの数（日数）は全て一緒にする 予定でした。
この「元データ」の各日のシートに各日の業務結果データが入力されており 現在作成している「月別集計」ブックの行4以降にINDIRECTで数字を 持ってきています。
行2には数式は一つも入っていません。
行3のC3:AG3ですが、行2のC2:AG2の日付（本当はただの文字列）の下のセルに "本当の日付"を入力するためC3に=DATEVALUE($A$1&C2)を入れてAG3まで コピーしています。つまり、A1の月リストを8月に変えれば、C3:AG3の 曜日が自動的に変わるようにしたのです。つまり、行2は年間を通して 固定されている日付（見かけ上）で、変化するのは行3の曜日(aaa)なのです。
行3を日付のシリアル値にしておく必要性ですが、暦に従って、"平日の 業務日"と"土曜日の業務日"、そして"単独業務日"という当社の3種類の 業務日の種類で条件付き書式でセルに色をつけているからです。
当社の業務日の一覧は「業務日」シートに貼り付けてあります。
例えば、7/17は"業務日（範囲の名前）"だけれど、土曜日なので水色とか、 7/20は火曜日だけれど"単独業務日（範囲の名前）"なのでピンク、 というようにしています。
曜日を文字列にしてorとかandで条件をつけてもいいかもしれないのですが 日付のシリアル値の方が扱いやすいと思ったのです。
行4以降はINDIRECTで参照した業務数値が行47まで自動入力されるように しています。
現在のINDIRECT関数ですが、例えばH3(7/6火曜日）の数式は：
=IF(COUNTIF(全業務日,$H$3)>0,VLOOKUP($B4,INDIRECT("[元データ.xls]"&$H$2&"!$A$5:$D$29"),2,FALSE),"")
これをC25までコピー。隣のI3にもコピーして$H$3を$I$3、$H$2を
$I$2に変えてそれぞれ行25までコピーしています。
これは、別シートにある"全業務日"（"業務日"と"単独業務日"を合わせた範囲） という名前の範囲に7/6があれば、H2（つまり"6日"）と同じ名前の シートをブック「元データ」に探しに行って、B4（業務の種類がB25まで 手入力されています）と同じ文字列を 元データのA列（正確には$A$5:$D$29）から探して、 左から2番目（つまりB列）の数字を返す。 というものです。
このINDIRECT数式の現在の問題は、検索した日付が"全業務日"に含まれているので、 「元データ」の日付シートにB4:B25の項目を見に行ってもその項目がないことがあり その場合に#N/Sが出てしまうのです。
それで、ISERRORをどこかに入れて#N/Sを何とか消さなければならないと思いながら 数式が複雑でどこに入れていいのか分らず未だにエラーが出たままになっている状況です。
これについて質問をしなかったのは、まだ自分が十分に試してなくてたたき台も ない状態でまる投げで教えてもらうのはよくないことだと思ったからです。
最初に#REF!が出たのも元データを開けていなかったからです。特にエラーを 気にせず・・・というわけではないのですが、勉強不足と不注意ですみませんでした。
なので当面の質問として、行25までデータが入力できたあとに 各行の火曜から金曜（最初は平日と質問してしまったので後になって混乱を 招いてしまってすみませんでした。これは"平日の業務日"のことです。）の 合計を出す方法を 伺ったのです。それをヒントにして、"土曜日の業務日"だけ、"単独業務日"だけの 合計を出す方法を自分で考えてみようと思っていました。
行2の日付を固定して年間で使いまわそうと単純に考えていたところ （R)さまがその先のエラーの可能性に気づいてくださって、アドバイスを いただいたのだと思います。そのアドバイスがなくてそのままやっていれば 9月や2月になってやっと落とし穴に気がついたのだと思います。
すみません、ここまで書いてきてお二人がおっしゃるように、私自身が大きな勘違いを していたことに気がつきました。
集計しなければいけないのは、 "平日の業務日" "土曜日の業務日" "単独業務日" でした。
行3で言えば、セルにつけた色別でした。申し訳ありません。
最初の質問が間違っていたばかりにみなさまのお時間を無駄にしてしまいました。 私自身にとっては、SUMPRODUCTからWEEKDAYの考え方から大変な勉強を させていただいたのですが、みなさまには見当違いとなってしまいました。
なんとお詫びしていいのか。
本当に申し訳ありませんでした。
ちい
参考までに
表の入力内容をかなり変えるつもりで見てください
A1に 数値で月を入力（8月の場合8のみ、9月の場合9のみ） A1の書式を 書式 セル ユーザー定義 0"月"
C2=DATE(2010,$A$1,COLUMN(A1))*(MONTH(DATE(2010,$A$1,COLUMN(A1)))=$A$1) C2:G2にフィルコピー C2:G2の書式を 書式 セル ユーザー定義 d"日"(aaa);;;
個人的には C2=DATE(2010,$A$1,1) D2=C2+1 D2:AD2 にフィルコピー AE2=DATE(2010,$A$1,COLUMN(AC1))*(MONTH(DATE(2010,$A$1,COLUMN(AC1)))=$A$1) AE2:AG2 にフィルコピー
>行3を日付のシリアル値にしておく必要性ですが、暦に従って、"平日の >業務日"と"土曜日の業務日"、そして"単独業務日"という当社の3種類の >業務日の種類で条件付き書式でセルに色をつけているからです 2行目がシリアル値ですので、2行目を利用して条件付き書式を設定
>=IF(COUNTIF(全業務日,$H$3)>0,VLOOKUP($B4,INDIRECT("[元データ.xls]"&$H$2&"!$A$5:$D$29"),2,FALSE),"")
C4に式を入力するとして
=IF(COUNTIF(全業務日,C$2)>0,VLOOKUP($B4,INDIRECT("[元データ.xls]"&TEXT(C$2,"d!日")&"!$A$5:$D$29"),2,FALSE),"")
C4:AG45にフィルコピー
>ブックのシートの名前でもあります。つまり「元データ」のブックには >「1日」、「2日」、「3日」・・・「31日」という名前で31シート > 元データは月ごとにブックを作っていますが、シートの数（日数）は全て一緒にする >予定でした =IF(COUNTIF(全業務日,C$2)>0,VLOOKUP($B4,INDIRECT(TEXT($A$1,"![0!月.!x!l!s!]")&TEXT(C$2,"d!日")&"!$A$5:$D$29"),2,FALSE),"")
>元データのA列（正確には$A$5:$D$29）から探して、 >左から2番目（つまりB列）の数字を返す。 >というものです であれば、はじめから範囲は $A$5:$B$29 のほうがいいですね
>それで、ISERRORをどこかに入れて#N/Sを何とか消さなければならないと思いながら べたでするなら =IF(COUNTIF(全業務日,C$2)>0,if(ISERROR(VLOOKUP($B4,INDIRECT(TEXT($A$1,"![0!月.!x!l!s!]")&TEXT(C$2,"d!日")&"!$A$5:$D$29"),2,FALSE)),"",VLOOKUP($B4,INDIRECT(TEXT($A$1,"![0!月.!x!l!s!]")&TEXT(C$2,"d!日")&"!$A$5:$D$29"),2,FALSE),"")
countif関数を使用する方が普通かな? =IF(COUNTIF(全業務日,C$2)>0,if(countif(INDIRECT(TEXT($A$1,"![0!月.!x!l!s!]")&TEXT(C$2,"d!日")&"!$A$5:$a$29"),$B4),VLOOKUP($B4,INDIRECT(TEXT($A$1,"![0!月.!x!l!s!]")&TEXT(C$2,"d!日")&"!$A$5:$D$29"),2,FALSE),""),"")
前回までの 火曜から金曜の合計の場合 =SUMPRODUCT((WEEKDAY(C2:AG2,2)<6)*(WEEKDAY(C2:AG2,2)>1)*(MONTH(C2:AG2)=$A$1),C4:AG4)
該当無い日付の集計すべきデータがありませんので =SUMPRODUCT((WEEKDAY(C2:AG2,2)<6)*(WEEKDAY(C2:AG2,2)>1),C4:AG4)
>集計しなければいけないのは、 >"平日の業務日" >"土曜日の業務日" >"単独業務日" > でした。 ということであれば、作業用行を利用してsumif関数を利用した方がいいかな
作業用行を3行目として c3=if(countif(平日の業務日,C2),1,if(countif(土曜日の業務日,c2),2,if(countif(単独業務日,c2),3,""))) C3:AG3 にフィルコピー
※ この3行目を 条件付き書式に使用してもいいかな
平日の業務日=SUMIF(C3:AG3,1,C4:AG4)) 土曜日の業務日=SUMIF(C3:AG3,2,C4:AG4)) 単独業務日=SUMIF(C3:AG3,3,C4:AG4))
エクセルは表計算ソフトですので、一般にデータは 数値の方が扱いやすいです
By
あの後、昨日からみなさまにいただいた数式を何度も見直しながらやってみました。
最後に作っていただいた >=SUMPRODUCT((WEEKDAY(C2:AG2-1,2)<5)*(MONTH(C2:AG2)=$A$1),C4:AG4) （R）さま >=SUMPRODUCT((WEEKDAY(C2:AG2,2)<6)*(WEEKDAY(C2:AG2,2)>1)*(MONTH(C2:AG2)=$A$1),C4:AG4) Byさま この二つの式は難しすぎて私には応用して加工することはできませんでした。
最終的には >=SUMPRODUCT((WEEKDAY(C3:AG3,2)<6)*1,(C4:AG4)) を利用して
=SUMPRODUCT((COUNTIF(平日業務日,$C$3:$AG$3)>0)*1,(C4:AG4)) =SUMPRODUCT((COUNTIF(土曜業務日,$C$3:$AG$3)>0)*1,(C4:AG4)) =SUMPRODUCT((COUNTIF(単独日,$C$3:$AG$3)>0)*1,(C4:AG4)) としました。この式で答えが出るまでに５時間かかりました。
また、A1には2010から2012までをリストにし、月のリストはB1に移動しました。 ただ、A1の年をDATEVALUEに入れる方法はこれから勉強しようと思いますので 行3（日付のセル）は当面=DATEVALUE($B$1&C2)としておくことにします。
>この式だと、A1が2月とか9月のとき #VALUE!エラーになりませんか？ >C3=DATE(2010,A1,1) を試してみたのですが、何度やっても#VALUE!になってしまいその先に+1してコピーするまでにいたりませんでした。 継続して試してみて成功したら >AD2:AG2 を選択して 条件付き書式 数式が =MONTH(AD2)<>$A$1 セルの色 白 >=SUMPRODUCT((WEEKDAY(C2:AG2,2)<6)*(MONTH(C2:AG2)=$A$1),C4:AG4) に進みたいと思います。 >3行目の数式を、2月とか9月でもエラーにならないように（「""」にする）変更して まだ意味が分りませんがこれも。
昨日からみなさまには大変ご迷惑をおかけしましたが、目的の計算ができました。 また将来のエラーを防ぐ糸口もみつかりました。みなさまのアドバイスがなかったら 絶対にここまで来れなかったです。
最後の難しい式も理解できるようになるまで持ち歩くことにいたします。
bbqさま、コタさま、(R)さま、Byさま、心から感謝しております。 本当にありがとうございました。
ちい
Byさま！
コメントをありがとうございました。
朝から自分のページを開きっぱなしにして参照しており、今自分の コメントを更新したところByさまのコメントを発見しました。
ありがとうございます。
もうどなたも見てくれてないと思ってました。 涙がでました。本当にありがとうございます。
もう一度よく読んでみます。
取り急ぎお礼を申し上げます。ありがとうございました。
ちい
>また、A1には2010から2012までをリストにし、月のリストはB1に移動しました。 >ただ、A1の年をDATEVALUEに入れる方法はこれから勉強しようと思いますので >行3（日付のセル）は当面=DATEVALUE($B$1&C2)としておくことにします。 A1の年を数値(2010,2011,2012)で入力していませんか? =DATEVALUE(A1&"年"&$B$1&C2)
2010年、2011年、2012年のように入力の場合 =DATEVALUE(A1&$B$1&C2)
>この式だと、A1が2月とか9月のとき #VALUE!エラーになりませんか？ >C3=DATE(2010,A1,1) >を試してみたのですが、何度やっても#VALUE!になってしまいその先に+1して >コピーするまでにいたりませんでした。 A1が数値であることを前提に記載していました (詳しくは前回の回答をご覧ください・・配慮を欠いた回答で申し訳ありません) A1が「7月」のように数字以外の文字が含まれる場合それを省く必要があります =DATE(2010,SUBSTITUTE(A1,"月",),1)
By
Byさま
>A1の年を数値(2010,2011,2012)で入力していませんか? >=DATEVALUE(A1&"年"&$B$1&C2)
はい、数値で入力していました。それで=DATEVALUE(A1&B1&C2)に したのでエラーになったのですね。"年"を入れるなんて全くわかりませんでした。 上の式を入れたら、1(水)とちゃんと出ました！（これは９月の表です）
>=DATE(2010,SUBSTITUTE(A1,"月",),1)でもいいのですね。SUBSTITUTEも初めてです。
先ほどの集計については今まで使ったことがないIFの使い方なので、 ちょっと難しそうです。 少しお時間をいただいてやってみたいと思います。
それと、コメントでは#N/Aの消し方まで教えていただきありがとうございます。 やってみます。これが成功すれば、この表はほぼ完成になります。
マニュアルにも書いていないことをたくさん教えてくださってありがとうございます。 知識が少ないので理解に時間がかかってすみません。
またお返事させていただきます。
これからもどうぞよろしくお願いいたします。
ちい
A B C D E ・・（略）・・ AE AF AG 1 2010年 9月 2 1日 2日 3日 29日 30日 31日 3 水 木 金 水 木
A1 と B1 はドロップダウンリストから選択（「年」や「月」をつけた文字列にしておく） C2:AG2 は元のように「1日」から「31日」を入力（C2に「1日」と入力してAG2までオートフィル）
C3 =($A$1&$B$1&C2)*1 AD3セルまでオートフィル AE3 =IF(ISERR(($A$1&$B$1&AE2)*1),"",AD3+1) AG3セルまでオートフィル
C3:AG3 のユーザー定義 aaa
これで2行目は文字列の日にち、3行目がシリアル値になります。
4行目以降は VLOOKUP ではなく SUMIF関数を使います。
C4
=IF(OR(C$3="",COUNTIF(全業務日,C$3)=0),"",SUMIF(INDIRECT("["&$B$1&".xls]"&C$2&"!A5:A29"),$B4,INDIRECT("["&$B$1&".xls]"&C$2&"!B5:B29")))
下と右にフィルコピー（B列の項目がない場合は 0 になります） （R）
(R)さま！ありがとうございます！！
>C3 =($A$1&$B$1&C2)*1 AD3セルまでオートフィル >AE3 =IF(ISERR(($A$1&$B$1&AE2)*1),"",AD3+1) AG3セルまでオートフィル
やってみました。この場合はA1は文字列で９月と入れるのですね。 さっきByさまが解説してくださったのでこれは反対のケースだとすぐわかりました。 2番目の式で、9月31日のエラーが消えました!Byさまの >AD2:AG2 を選択して 条件付き書式 数式が =MONTH(AD2)<>$A$1 セルの色 白 というアイデアと両方いつでも活用できるようにします。
関数は知識を基にして状況によってどのように組み合わせるかを 工夫することが大切なのだとつくづく思いました。
C4の数式ですが、元データのブックの名前を月にしておけば毎月参照ブックの 名前を入力しなおす必要がないのですね。そしてB4の項目があったら 集計して・・・ということです・・・すみません、やっぱり少し お時間をいただいて勉強させてください。
最初はしろうとが苦し紛れで作った中途半端な表でしたが Byさま、（R)さま、みなさまのおかげでプロが作ったみたいな表になりそうな 予感がします。
でも、教えていただいたものをそのまま使う前に、次からは自分でも 書けるくらいによく理解したいと思います。
すぐに理解できるか心配ですが、またご報告させていただだきます。
とっても回り道をさせてしまいましたのに、親身になってご教授いただき 本当に感謝しております。見捨てないでくださってありがとうございました。
これからもどうぞよろしくお願いいたします。
ちい
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.