VBAを使わず、計算式を使いまわし

質問

エクセルで勤務表を作っています。

たとえば、B2に出勤時間を、C2に退社時間を入力すると、D2に勤務時間が表示されます。

現在の計算手法は、A1、B1の値を別セルからそれぞれ参照し、計算をし、結果を別セルに表示させます。その結果をC1セルが参照して、C1セルに表示させるような手法をとっています。

※話を簡単にするための一例です。この程度ならダイレクトにC１に結果を表示させることは簡単ですが、実際にはさまざまな条件があるため、計算はもっと複雑です。

この手法だと、同じ計算式を日にちごとに用意しなければいけません。
計算式がそこそこ複雑なので、毎日分用意すると、メンテナンスやファイルサイズの問題などあまり
よろしくないと思っています。

そこで、VBAのFunctionプロシージャのように、計算式は1セットのみで、毎日の出勤、退勤時間を引数で渡して、結果を返すような方法はできないのでしょうか？

回答No.13

WindFaller · ベストアンサー

しばらく読んで考えてみましたが、私も、少し口を挟ませていただきます。こちらの話が、不要だと思えば、どうぞ、補足やお礼には書かないでいただきたいです。また、感情的な表現は控えてください。このような掲示板では、ご自身のプライドを損なうようなこともあるでしょうが、それは回答を得るために仕方がありません。

>すでに何人かの回答者の方には質問を理解していただき回答ももらっているので
私にはそうは思えません。ふつう、このようなOffice系の掲示板で、12件の回答まで続く頃には、ある程度の見通しが立っているはずですが、そのような風には思えません。いたずらに、回答が増えているだけだと思います。初めから、「VBAありき」で進められても、具体的な質問を何一つしているとは思えないからです。

「VBA」で出来るものを、使わないで、代わりのものはないかというご相談のように思えますが、経験的に、そのような必要があるとは思えません。

ただ単に、ご質問者さんは、Excelの使い方は独学か、かなり偏った覚え方をしているような気がします。#4/#12さんが、説明した内容を少しも理解していないようですから、基礎的な使いこなしに難があるように思われます。

すでに、これらの内容は出ていますが、あらためて書きますが、
キーワードは、
　　「オートフィル・コピー」「絶対参照と相対参照」「テーブル」
です。これを押さえてください。

「テーブル」を除く、２つは用語はともかく、その使いこなしや存在を知らないと話にならないのです。バージョンは書かれていませんが、添付画像をみると、2007以上ですから、その範囲を「テーブル」に変換するのが、楽です。

>VBAのFunctionプロシージャのように、計算式は1セットのみで、毎日の出勤、退勤時間を引数で渡して、結果を返すような方法はできないのでしょうか？

誰も指摘していませんが、「VBAのFunctionプロシージャ」とは何でしょうか？Accessでは、ほとんどが、Functionプロシージャですが、Excel VBAでは、その扱いが慣れていない人のみが書くことが多いです。私は、何千件のVBAのプログラムを書いていますが、Excel VBAで、Function プロシージャを書いた覚えは、10個にも見たないです。通常は、Sub プロシージャです。同じFunction でも、「ユーザー定義関数」です。機能が全然違います。文面からすると、VBAの「ユーザー定義関数」だと思います。

>休日やら、遅刻やら早退やら、休憩やらをその他条件を考慮した上で結果に表示させます。

最初の数件(1～3）の補足・お礼の文面をみて、数式で、複雑と言われても、ここの回答者さんたちの多くにとって、それは、たかが知れています。どこの掲示板でも、同じような質問は解決しています。ただし、質問で、他人に分からせる技術が明暗を別けます。言い換えると、後出しの質問や内容の変更は、ダメです。

私自身、勤務時間の計算は、やったことはありますが、それは、計算式に付随する要素が4件程度あって、それをまとめるというだけの話です。その程度のことは、会社員（主に工場勤務）の人たちで管理部門や総務の方は、誰でも経験していますから、大変なことだとは思いません。単に、それぞれの事情があって説明がしづらいというだけのことだと思います。この程度の話で、好きで作るならともかく、VBAなど必要とは思えません。

ご自身が、複雑に考えているだけで、「計算エリア」などという発想を、盛り込む、つまり、今の計算式そのものを壊したくないというだけで、具体的な内容を見せずに、考えだけを主張しているように思われます。

また、区役所や市役所などの外部組織に、ExcelやOffice を教えるボランティアのアドバイザーを用意していることもありますので、もしあれば、スポットで教えてもらうのもよいかもしれません。

お礼

回答ありがとうございます。

下記回答者さまたちに感情的にコメントしてしまったことを反省しております。
確かに、複雑といっているだけで、具体的な例を挙げなかったことはよくなかったと思います。

どのように複雑か、簡単に書きます。(エクセルになれている方なら複雑でないかもしれませんが)
始業時刻と、就業自国以外の情報として、下記があります。
・休憩時間(休憩時間の回数、また時期によって休憩時間や、休憩時間の開始時間が変わります)
・半日勤務か(午前、午後、時期(月)でも条件が変わります。)
・有給休暇か
・休日か、また、休日出勤か
・業務内容は何か
・すべて漏れなく記入されているか(チェック機能)
・早退、遅刻などした場合の対処(規定時間からのマイナスなど）
・どれだけ残業しているか(残業時間によってはアラームを出す機能)
・その他こまごまとした処理

上記を実現しようとすると、どうしても複数セル(というか、ある程度の計算エリア)が必要になってきます。ひとつのセルで、一行の式だけで実現するのは、到底無理です。
ですので、具体的な例は面倒だったので出さなかったのです。
ちなみに、現在作っている勤務管理表では、一日の判定につき、5×32(＝160セル)のセルを使っています。(もちろん、すべて計算式のみでなく、テーブルの見出しや、説明文も込みで)

それから、VBAについては私もそれほど詳しくはないのですが、間違った表現があったかもしれません。
＞「VBAのFunctionプロシージャ」とは何でしょうか？
Functionプロシージャ＝ユーザー定義関数との認識でした。(違ってたらすいません)
http://www.moug.net/tech/exvba/0100042.html
http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub04_110_02.html

回答No.12

kagakusuki

>質問文に出した例は、行いたいことの概念を簡単に説明するための単純なモデルですので、いくら「=IF(COUNT(B2,C2)=2,C2-B2,"")」のような方法を示していただいてもあまり意味がないのです。

　その様な事は最初から承知しております。
　だからこそ、

>添付゛画像の例にある

>=C2-B2

>という関数は、A2に入力されている日付には関係なく値を求める事が出来る関数ですから、添付画像の例は、「何故、同じ計算式を日にちごとに用意しなければならない事になるのか」という事の説明にはなっておりません。

>又、現在F列に入力しておられる関数に関して、日付けが何年何月何日の場合の関数式と、それとは別の日付けの場合の関数式を、具体例として御提示頂いた上で、日付けの違いよって何故その様に関数式を変えているのかという事に関して御説明頂けた方が、回答が得られ易くなると思います。

と申し上げたのですが、それに対して質問者様は実際に入力されている関数の具体例を御示しになる事なく、

>質問分にあげた画像での例だと、
>F3セルには"=C3-B3"という計算式が入ります。
>F4セルには"=C4-B4"という計算式が入ります。
>F5セルには"=C5-B5"という計算式が入ります。
>F6セルには"=C6-B6"という計算式が入ります。
>以下同様

という例を示されたのみでしたので、当方も、御質問の趣旨を確認させて頂く上で、、あくまで質問者様が欲しておられる方法と対比させるために一般的に使われている方法の代表例として、「=IF(COUNT(B2,C2)=2,C2-B2,"")」という関数を提示させて頂いただけの事です。

>それと、
>＞「空欄を表示する事にも関数を使用するのは容量の無駄なので何とかしたい」という事なのでしょうか？
>このようなことを言い合っても、本質ではなく意味がないのです。

　ですから、その本質（御質問の趣旨）を確認するために発した問いがその一文なのですから、意味が無い等という事は無い筈です。
　それとも質問の趣旨を確認しないまま、回答した方が宜しいのでしょうか？

お礼

回答ありがとうございます。

なんか堂々巡りですね。

すでに何人かの回答者の方には質問を理解していただき回答ももらっているので、いまさら本質がどうのとか、趣旨がどうのとかを追及する気はないです。

万人が理解できるよう質問文を考えているわけでなく、わかってもらえる方にわかってもらえて、回答もらえればいいと思っています。(最初の質問だけでは説明が足りなかったことは認めますが。)

補足

kagakusukiさん
少し落ち着いたので、改めましてコメントさせていただきます。
まずは、先日は感情的にコメントしてしまい、申し訳ありません。

さて、No4に回答いただいた内容についてのコメントです。

＞A2に入力されている日付には関係なく値を求める事が出来る関数ですから、
正直いうと、今回の場合はおっしゃるとおり、日付は関係ありません。
前提から話しますと、今回の場合、質問文に出した画像のように、1か月分の入力欄がはじめから
設けてあります。画像では「○月×日」となっていますが、実際は2月1日、2月2日・・・と月の終わりまで
用意しています。画像ですと、一行一行が一日一日に対応し、計算は1行単位で完結しているので、
計算において、それが何月何日の情報かは関係ありません。強いてあげれば、その日が休日かどうか位ですが、その条件は実際には盛り込むとしても今回の質問には特に必要のない情報と判断して、出しませんでした。

＞日付けが変わった際に、F列の関数をどのように変更しなければならないのか
日付が変わる＝記入行が変わるということなので、F列の関数は、過去のコメントで例を挙げて書いたとおり、参照先を自分の行のB列とかC列に変えるだけです。

次にNo.10のご回答に対して

＞等の様な関数を入力してから、D2セルをコピーして、D3以下に貼り付けておくのが普通なのですが、
もちろん、普通はそのような作り方をします。
”D3以下に貼り付けて”とありますが、この方式(=通常のやり方)の場合、D2からD32まで(31日の月の場合)同じ式(同じ構造の式)が必要になりますよね。
ここまではわかっていただけますか？あたりまえ過ぎて疑問に思わなかったかもしれませんが。

仮に今回例に挙げていただいた「=IF(COUNT(B2,C2)=2,C2-B2,"")」程度の式であれば、容量的にも、メンテナンス的にもそれが31個分あったとしても何の問題もありません。

ただ、実際はNo13の方のお礼にも書いたように、休日、有給、その他条件をすべて盛り込んだ上で状況判断、および計算をするため、一日あたり5×32(＝160セル)にわたって計算式を構成しております。

現在は、この160セルを日数分用意して実現しております。つまり、計算だけで4960セル(160セル×31日
分)もの範囲を計算エリアとして使用しています。
これは、容量的にも、後々のメンテナンス的にもやりづらいのではないかと思ったのです。
やっていることは同じなのに、31日分もの同じ(構造の)計算エリアを設けるのは無駄だと思ったのです。

だったら、ひとつの(一日分の)計算エリアを用意し、そこに毎日の出勤、退勤時刻を引数として渡し、計算結果を返してくれるような方法がないか疑問に思ったのです。
この考え方はプログラムの世界では、ごく一般的な、当たり前の考え方です。もちろんエクセルでもVBAを使えば可能です。

ただ、今回の質問のお題(趣旨)は、VBAを使わずにということなので、今回のような質問をしたわけです。

ですから回答者様の出した例で無理やり今回のしたいこと(趣旨)に当てはめて考えますと…

>=IF(COUNT(B2,C2)=2,C2-B2,"")
>等の様な関数を入力してから、D2セルをコピーして、D3以下に貼り付けておくのが普通なのですが

=IF(COUNT(B2,C2)=2,C2-B2,"")
この式をD2セルだけに用意し(D3以下に貼り付けずに)、各行(=各日)の勤務時間をD2セルに用意した計算式を利用して計算し、結果を表示するにはどうしたらよいかということです。

長々と失礼しました。
疑問点があれば、回答お願いします。

しかし、VBAを使わずに実現することは無理というのが今現在の感触です。

回答No.11

keithin

ん？
＞やはりVBAじゃないと無理ですよね。

別に無理だとか一言も言ってませんけど、所詮ヒトの話しを聞くつもりのない釣り質問だったということで。

回答No.10

kagakusuki

>質問分にあげた画像での例だと、
>F3セルには"=C3-B3"という計算式が入ります。
>F4セルには"=C4-B4"という計算式が入ります。
>F5セルには"=C5-B5"という計算式が入ります。
>F6セルには"=C6-B6"という計算式が入ります。
>以下同様

>30日あれば、30個の構造が同じ計算式が必要ですよね。

　その様な場合、一般的には前もって（F2セルではなく）D2セルに

=IF(COUNT(B2,C2)=2,C2-B2,"")

等の様な関数を入力してから、D2セルをコピーして、D3以下に貼り付けておくのが普通なのですが、質問者様は「空欄を表示する事にも関数を使用するのは容量の無駄なので何とかしたい」という事なのでしょうか？
　それならば確かにVBA等のマクロを使用しなくては不可能な事ですが、その様な使い方をされるのであれば、マクロを使って関数を入力するのではなく、マクロを使って計算を行い、その計算結果の値のみをD列のセルに（マクロによって自動的に）入力する様にすれば済む話なのですから、枠線と値の入力が可能でさえあれば良い話（Wordでも可能な事です）で、Excel等の様な（関数を使って計算を行う事が最大の特徴である）表計算ソフトを使って行う意味が無い様な気が致します。

お礼

回答ありがとうございます。

質問文に出した例は、行いたいことの概念を簡単に説明するための単純なモデルですので、いくら「=IF(COUNT(B2,C2)=2,C2-B2,"")」のような方法を示していただいてもあまり意味がないのです。

それと、
＞「空欄を表示する事にも関数を使用するのは容量の無駄なので何とかしたい」という事なのでしょうか？
このようなことを言い合っても、本質ではなく意味がないのです。

結果としては、VBAを利用しなければだめそうです。
VBAを利用しないのであれば、同じ計算式をコピーしていくしかなさそうです

回答No.9

keithin

まぁあともう一個出来そうな方法としては。

添付図：
Sheet1のＢ列Ｃ列に記入した時刻に応じてＦ：Ｇ列の対応表から値を算出し、その差分を計算してみる

手順：
Ｃ１セルを選択する（必ず行う）
挿入メニューの名前の定義で
　名前　出勤値
　値　　=VLOOKUP(!A1,Sheet1!$F$2:$G$6,2)
　名前　退社値
　値　　=VLOOKUP(!B1,Sheet1!$F$2:$G$6,2)
　名前　一括計算
　値　　=VLOOKUP(!B1,Sheet1!$F$2:$G$6,2)-VLOOKUP(!A1,Sheet1!$F$2:$G$6,2)
とそれぞれ作成し、追加する

Ｄ２セルに
=退社値-出勤値
または
=一括計算
と数式を記入する。

名前定義の中の数式を修正すれば、一斉に結果が変化する。
数式を修正する場合は、その操作を行った時点でのアクティブセルの位置と、数式の中で「相対参照」しているセルの位置関係に十分注意して作成する。

お礼

なるほど。ここまでくるとアイデアしだいですよね。
しかし、例に挙げた単純なモデルだといろいろ方法がありますが、
実際にはもっと複雑な条件で状況判断して計算しているので、単純に適用することは難しいそうです。

ただ、アイデアとして、部分部分で応用できるところは利用していきたいと思います。

回答No.8

keithin

一応ご質問の直接の回答として
＞計算式は1セットのみで、毎日の出勤、退勤時間を引数で渡して、結果を返すような方法はできないのでしょうか？

ある無いで言えばエクセルの複入力テーブル（状況に依っては単入力テーブル）という機能を使い、数式を一つ与えておくだけでパラメータを順次入れていけば勝手に結果が並ぶ（与えた一つの数式を修正すれば、計算結果も一斉に変わる）という具合に、ご相談の文字通りの事は出来ます。

もっともご相談のその絵柄のような状況では、そのままさくっと使えるシロモノじゃありません。
ぶっちゃけＶＢＡでユーザー定義関数を作った方が、遥かに簡単です。

お礼

＞ぶっちゃけＶＢＡでユーザー定義関数を作った方が、遥かに簡単です。

そうですよね。やはりVBAじゃないと無理ですよね。

回答ありがとうございました。

回答No.7

moon00

ｋマクロを使う際、使うときの許可は、一定のフォルダに保存することで解消することができます。
参照URLのセキュリティセンターで「信頼できる場所」を追加すれば、その場所にあるファイルに付随する
マクロは、警告が出ません。
ネットワーク上の場所は、セキュリティ上望ましくないとされていますが、組み込むことは可能です。

また、実行するさいに「実行」を押さないとできない、とのことですが、VBAの組み込み方によっては、
「セルの内容が変更されたタイミングで実行」や「ファイルを保存するタイミングで実行」「閉じる前に
実行」など、「実行」ボタンを使わなくてもVBAを動かすことはできます。

もし、私が今回の場合にVBAを組むとすれば、複雑な計算式をVBAでプロシャージャにして、自作の関数を
作ることを考えると思います。
それもイマイチということであれば、「閉じる前に実行」か「セル内容の変更があれば実行」にして
マクロを組むと思います。

参考URL：

http://office.microsoft.com/ja-jp/excel-help/HA010354311.aspx

お礼

回答ありがとうございます。

なるほど、いろいろ方法もあるようですね。
いろいろ試してみる価値ありそうですね。

回答No.6

bunjii

>↑注釈は読みましたか？
回答の最初に「質問の趣旨が分かり難いのですが・・・・・」としました。
質問の文言が理解に苦しむ状況なので注釈も無意味の状態です。

>そんなことは知っていますが、今の議論はそこではないです。
あなたの真意を引き出すための回答と解釈してください。

>ちなみに、「出勤時間」とすると、どのように意味が変わってくるのでしょうか？
コミュニケーションの基礎が出来ていないとあなたを知らない人が全てのことに無知であるを誤解するでしょう。

回答No.3に対する補足から感じたことはExcelの基本的な動作を理解していなかったことによる疑問と推察できます。
あなたが提示した計算エリア（F列）はF2以下に必要数のセルコピーされているような表示です。
F2セルの関数でD2セルへ書き込むと言う動作はExcelの機能にありませんのでその逆のD2セルからF2セルを参照するしか手段がありません。
従って、D3セルはF3セルを参照する、D4セルはF4セルを参照すると言うようにすべての対応が必要になりますのでマクロの代用にはなりません。
つまり、B3セルとC3セルに時刻が入力されたことを検知してF2セルがC3-B3の結果をD3セルに書き込むと言う動作はあり得ません。

お礼

けんか腰にコメントしてしまい、申し訳ありません。

基本的なことを理解していないつもりはないのですが、もしかしたら何かいい方法があるのでは？
と思い、だめもとで質問したのが実情です。

やっぱり今回のようなことをしたければ、VBAを使うしかないですよね。

回答No.5

moon00

もうご自分でもお気づきのようですが、ワークシート上での関数で対応することは無理だと思います。
計算式を入力するところは1か所、引数はそれぞれの日付欄、結果が返るのもそれぞれの日付欄ということでは、
結果を受け取る方は計算式のセルを参照するしかないので、それを日付ごとに違う結果を受け取るように
なるように参照するのは無理があります。

強引に考えれば、INDIRECT関数で実現できるかもしれませんが、その場合、おそらく結果セルに入力する
数式は今とそんなに変わらないと思われます。

補足

回答ありがとうございます。

やはりマクロを使わなければ無利ですよね。

ちなみに、マクロを使わない理由は、立ち上げるとき毎回許可を求められるのと、実行するには実行ボタンを押すなどのアクションをしなければならないからです。

回答No.4

kagakusuki

ここまでの質問者様の御説明の中には、「何故、同じ計算式を日にちごとに用意しなければならない事になるのか」という事に関する説明がされておりません。
　添付゛画像の例にある

=C2-B2

という関数は、A2に入力されている日付には関係なく値を求める事が出来る関数ですから、添付画像の例は、「何故、同じ計算式を日にちごとに用意しなければならない事になるのか」という事の説明にはなっておりません。
　又、回答No.3様の御回答に対する御補足で述べておられる内容も、「日付けが変わった際に、F列の関数をどのように変更しなければならないのか」という事の説明には全くなっておりません。
　つまり、肝心の「日付けが変わった際に、関数をどのように変更しなければならないのか」という事に関する説明が無いため、質問者様が欲しておられる回答を考えるために必要不可欠な情報が不足しているのです。
　これでは、回答のしようが御座いませんので、「日付けが変わった際に、関数をどのように変更しなければならないのか」という事に関して御説明頂く様御願い致します。
　又、現在F列に入力しておられる関数に関して、日付けが何年何月何日の場合の関数式と、それとは別の日付けの場合の関数式を、具体例として御提示頂いた上で、日付けの違いよって何故その様に関数式を変えているのかという事に関して御説明頂けた方が、回答が得られ易くなると思います。

補足

質問分にあげた画像での例だと、
F3セルには"=C3-B3"という計算式が入ります。
F4セルには"=C4-B4"という計算式が入ります。
F5セルには"=C5-B5"という計算式が入ります。
F6セルには"=C6-B6"という計算式が入ります。
以下同様

日にち分、違う計算式が必要ですよね。参照セルが変わるだけで計算式の構造自体は
一緒ですが。
30日あれば、30個の構造が同じ計算式が必要ですよね。

構造が一緒なのに、このように日にち分用意するのが無駄だと感じているわけです。
構造が一緒なのだから、プログラムの関数のように、引数を渡して同じ計算式を
使いまわせないか考えているのです。
要はプログラムの「関数」の考え方です。

＞何故、同じ計算式を日にちごとに用意しなければならない事になるのか
というか、ここまで説明しないとわかりませんかね。

回答No.3

Nebusoku3

前の回答者さんも同じかも知れませんが質問の内容が読み取れません。　何とか理解しようと頑張ったのですが、何が問題なのか分からない状態です。
質問の方法も技術のひとつなので推敲してから出される事を希望します。

補足

「VBAのFunctionプロシージャのように」と書いたことで、わかってくれるものと思い込んでました。
申し訳ないです。

現在は、F列に計算式が入力してあります。この計算式は日付と1対1で日数分用意しなければなりません。

実現したいのは、以下のようなことです。

計算用の計算式は、たとえばF2セル1つのみしかない。いま「F2セル1つのみ」と書きましたが、実際には計算は複数セルで構成されています。この複数セルを一まとめで計算エリアと呼ぶことにします。

計算エリアに、たとえば○月1日の出勤時刻、退社時刻を引数として渡します。計算エリアは渡された出勤時刻、退社時刻を元に、勤務時間を計算し、計算エリアのとあるセルに結果として表示します。

○月1日の「勤務時間」セルは、この「結果」セルを参照し、自身のセルに表示します。これで○月1日の勤務時間は記録されたことになります。

さて、翌日になり、今度は○月2日が出勤時刻、退社時刻を引数として"同一の計算エリア"に渡します。
同じように計算エリアは勤務時間を計算し、計算エリアのとあるセルに結果として表示します。
このとき、「計算エリア」も、「計算エリアのとあるセル」も○月1日で出てきたそれと”同一”です。

そして○月2日の「勤務時間」セルは、この「結果」セルを参照し、自身のセルに表示します。
もちろんこのとき、○月1日の結果は○月1日に入力した状態のまま保持され、○月2日を入力したからといって変化しません。

つまり、この「計算エリア」をひとつ用意するだけで、毎日の計算をすることができます。

ただ、書いているうちに思いましたが、VBA使わずに実現するのはたぶん無理ですね。