2010年05月25日

DATEDIF関数

Excelには、経過年数や月数などを求める DATEDIF という関数があります。

これはヘルプに出てこない関数で、マイクロソフトのサポート外の関数という位置づけだと困りますので、普段、セミナーではあまりご紹介していないのですが、先日のセミナーでご質問を頂きましたので、簡単にご紹介したいと思います。

この関数はもともとLotus1-2-3という表計算ソフトにある@DATEDIF関数を、Excelに移行した場合にエラーにならないようにするための関数です。
そのためExcelで使用することを想定していないのか、Excel2003や2007ではヘルプで引数を確認することができません。

関数の背景を考えると、開発の納品時など自分の手元を離れてしまうようなファイルの中では他の方法で求めるようにしていますが、使い方がとても簡単ですので便利だとは思います。

DATEDIF関数の書式は以下のようになります。

(構文)
 DATEDIF(開始日, 終了日, 単位)

(単位)
 第三引数の「単位」は、以下の文字列が使えます。
  年数を求める場合は "Y"
  月数を求める場合は "M"
  日数を求める場合は "D"
  開始日と終了日の年と月を無視して「日」の部分のみの差を求める場合 "MD"
   (1ヶ月未満の日数)
  開始日と終了日の年と日を無視して「月」の部分のみの差を求める場合 "YM"
   (1年未満の月数)
  開始日と終了日の年を無視して「日」の部分のみの差を求める場合 "YD"
   (1年未満の日数)

一例をご紹介すると
(例1)
 =DATEDIF("2010/5/25","2011/5/24","Y")
 これは2つの日付の間が1年に満たないので 0 が返ってきます。

(例2)
 =DATEDIF("2010/5/25","2011/5/25","Y")
 ちょうど1年経過しているので 1 が返ってきます。

 ※上記の" "で囲まれた部分をセル番地に置き換えれば、
   他の関数と同様にセルを使った計算が可能です。


DATEDIF関数を利用すると、
 「生年月日から、年齢を求める」
 「商品が発売になってからの販売年数を求める」
 「入社・入会してからの年数を求める」
などに利用することができます。

また、上記を上手く組み合わせると、
 「リースを開始してから何年何カ月経過したかを求める」
など、色々な場合に活用できます。

上記の「何年何カ月経過したか」を計算する場合はいくつか方法がありますが、例えば以下の計算式で求められます。

(例)
 =DATEDIF("2010/5/25","2011/6/25","Y")&"年"
   &DATEDIF("2010/5/25","2011/6/25","YM")&"カ月"
 ※本画面上では2行ですが入力時は1行です。

(意味)
 "2010/5/25"から"2011/6/25"までの経過年数("Y")と
 文字列 "年" と
 "2010/5/25"から"2011/6/25"までの1年未満の月数("YM")と
 文字列 "カ月"を 結合して表示する。
 という意味になります。

ついでにご紹介すると、他にもヘルプには表示されない関数があります。
NUMBERSTRING関数もその例で、数値を漢数字に置き換えて表示するものです。

(例1)
 =NUMBERSTRING(1,2)
 数字の 1 を 漢字の「壱」 に変換して表示します。
 第一引数が 変換したい数値
 第二引数が 変換方法 です。
 
ヘルプにない関数を利用する時には、
公開されていない引数を調べる必要があったり、
他の人とファイルを共同で利用する場合などには配慮が必要かとは思いますが、
ちょっとした業務では、面倒な計算式を組み立てなくても簡単に答えが出るものもあり、使い方次第だと思います。
 
posted by Three bit com at 10:00| Excel(エクセル)

2010年05月19日

SQLのWHERE句

つい先日SQLについて書いたところ、早速セミナーでもSQLの基本に関するご質問を頂きました。

Accessを学習し始めて最初に覚えておいた方がよいSQLというとWHERE句ではないでしょうか。
クエリのデザインビューで言うと「抽出条件」の欄に入力した部分が、SQL文のWHERE句(WHEREで始まる部分)に変換されます。

ごく簡単な例を書いてみます。

(抽出条件1)
 数値型の「顧客ID」フィールドの値が 100 と等しいデータ
(WHERE句の例1)
 WHERE 顧客ID = 100

(抽出条件2)
 テキスト型の「顧客名」フィールドの値が文字列 花子 と等しいデータ
(WHERE句の例2)
 WHERE 顧客名 = "花子"

SQL文の中で文字列データは半角の " " で囲みます。
VBAのプログラムの中で " " を使って囲む場合は " " を ' ' に修正します。

(VBAで利用する場合の例)
 "WHERE 顧客名 = '花子'"

クエリのデザインビューで抽出条件を入力し、SQLビューで確認すると、WHEREに続けてどのような記載になるかを確認することができます。
SQLビューでは以下のように ( ) や テーブル名も追加されます。

(AccessのSQLビューの例)
 WHERE (((顧客マスター.顧客名)="花子"));
(意味)
 「顧客マスター」テーブルの中にある「顧客名」フィールドの値が、文字列 花子 と等しいデータ

意外に多いミスは半角と全角の入力ミスです。
「WHERE」や文字と文字の間に入る空白などは半角になります。
WHEREを小文字の where と書いても動きますが、
全角で WHERE と書くと動きません。

Accessの表示が初期設定で「MS Pゴシック」になっているので、全角と半角の違いが画面上で見分けにくく、スペースキーで半角に変換して入力していると、ついつい間違えてしまうことが多いようです。

「どう見ても合っていると思うのになぜかエラーになってしまう」
というような場合は、もう一度入力し直してみてください。

Accessを使っていると、グループ集計、クロス集計、マクロなどで「WHERE条件」という言い方で抽出条件を指定させる機能があります。
SQL文のWHERE句について理解しておくと、AccessのWHERE条件も入力がしやすくなることと思います。




posted by Three bit com at 10:00| Access(アクセス)

2010年05月12日

SQLの勉強

ブログのシステムが整わず、記事投稿を長らくお休みしていましたが、ようやく準備が整いましたので再開することになりました。
セミナーで頂いたご質問の紹介や、開発、IT関連ほか、色々書いていきたいと思っています。
どうぞよろしくお願いいたします。

大分前のセミナーになりますが、AccessのSQLの学習方法についてご質問を頂いたことがあります。
ブログでもSQLについて紹介していきたいとお約束していましたし、つい先日のセミナーでも類似のご質問を頂きました。
記事投稿の再開にあたりSQLの習得に関連することに少し触れてみたいと思います。

Accessはクエリの「SQLビュー」を使えば簡単にSQL文を調べることができますので、SQLの勉強はとかく後回しにされることが多いように思います。
私自身のことを振り返ってSQLを勉強しておいて分かりやすかったことと言えば、例えばSum関数などの利用方法です。

Excelを使う時のような感覚でレコードのフィールドの一つにCount関数やSum関数の結果を表示して演算に使用したくなる場合があります。
Sum関数だけを使って合計だけを求めるのであれば、例えば以下のようなSQL文で結果が求められます。

(例)
SELECT Sum([金額]) AS 売上 FROM T_受注;
(意味)
「T_受注」テーブルから、[金額]フィールドの合計を求め、「売上」というフィールド名で表示する。

ところが、この「SELECT」の後ろに他のフィールドを追加したとします。

(間違った例)
SELECT 受注ID, 金額, [金額]/Sum([金額]) AS シェア FROM T_受注;

気持ちとしてはExcel風に
「受注IDごとに、その売上金額のシェアを求めるつもり」
なのですが、このクエリ(SQL)を実行しようとすると次のようなエラーが返ってきます。

「集計関数の一部として指定された式'受注ID'を含んでいないクエリを実行しようとしました。」

ヘルプを見ると

「指定した式を集計関数やグループ化の一部として指定せずにクエリを実行しようとしました。」というエラー解説が出てきます。

集計関数とはSumやCountのような集計を行う関数のことです。

「集計関数の一部」として利用する方法とは、
以下の[数量]や[定価]のようにSum関数の引数としてフィールド名を指定することが出来る
という意味になります。

(例)
SELECT Sum([数量]*[定価]) AS 売上 FROM T_受注;

もう一つの「グループ化の一部」として利用する場合は、
グループ化のSQL文「GROUP BY」 に続けてそのフィールド名が指定されているならば「SELECT」の後に続けて記載が可能
というものです。

(例)
SELECT 受注ID, Sum(金額) AS 売上 FROM T_受注 GROUP BY 受注ID;
(ポイント)
SELECTとGROUP BYの両方の後ろに「受注ID」が記載されています。)

Accessには、Excelと似た使い方が出来るところが沢山あり、比較対照しながら覚えると早くマスター出来ます。
この知識は業務処理を行う際に、ExcelとAccessのどちらを使った方がよいかの判断に繋がり役立ちます。

ですがクエリの難易度が上がってきた場合には、SQLの知識もあると、ExcelとSQLの違いの切り分けが出来るようになり、SQLのルールに照らして
「これは出来る」
「これは出来ない」
などの判断が素早くできるようになり、作成効率が上がってくると思います。

SQLの学習には一般にOracleの資格が利用されることが多いと思います。
エンジニアを目指される場合にはお勧めですが、Accessの作業を効率化したいだけでしたら、時間がある時にクエリのSQLビューを少しずつ見ておき、ある程度頭に入ってきたら参考書などで体系的に学習するのもよいと思います。

posted by Three bit com at 10:00| Access(アクセス)