列の情報の種類で「集計値」を選択すると、その列に「数式」を入れることが可能になる。また、リストで定義されている列を数式で使うことができる。まさに Excel の数式に近い。
そして、この数式で利用できる「関数」も用意されている。
Excel でもそうだが、単純な四則演算の他で入力されたデータを行単位で処理するパターンとしては、
- 文字列操作
- 日付の操作・計算
が主だろう。
もちろん、Excel にエクスポートしてしまえば、Excel だけで処理可能であるが、SharePoint リストのビューのグループ化などをする場合に、グループ化の列を入力されたデータから加工して作るケースも考えられる。
Excel ユーザーであれば「ニヤリ」とする場面も多々あることから、SharePoint リストでの数式、関数を覚えておいて損はない。
しかし、いつものように「落とし穴」もあるので注意してほしい。それらも含めて紹介する。
文字列の結合
"姓"と"名"を別々に入力させて氏名にするなどは "&" を使う。姓名の間に半角スペースを入れることも可能だ。アイテム入力にはこの集計値の列は表示されない。リスト表示の際は「氏名」だけを表示するビューを作成すればよい。CONCATENATE 関数も使える。
文字列のチェック/抜出し/置き換え
文字列操作では Excel でおなじみの LEFT、RIGHT、MID 関数、FIND、SEARCH 関数、REPLACE、TRIM、CLEAN 関数が使える。私が試した範囲だと全角を半角にする ASC 関数は動いていないようだ。半角を全角にする JIS 関数はない。（構文エラーになる）
TEXT 関数と VALUE 関数もある。特に TEXT 関数は日付の表示形式でも使う。
UPPER, LOWER, EXACT 関数もある。
UPPER, LOWER, EXACT 関数もある。
もちろん、関数を組み合わせて使うことも可能だ。入力されたコードの "-" より前半部分を抜きだす数式は以下になる。
=LEFT(コード1,FIND("-",コード1)-1)
ただ、数式入力ボックスは狭く、数式オートコンプリートによる入力支援機能はない。よって、引数についてはワークシート関数と同じだと思って入力するか、SharePoint リストで利用できる関数のヘルプをみて入力する必要がある。
日付はシリアル値で保存されている
Excel ユーザーにとっては「！？」という事実なのだが、SharePoint リストで日付をシリアル値で扱っていることは Excel ユーザーにとっては大きなメリットだ。つまり、シリアル値が使えることで、若干 Excel の数式/関数とは違うがほぼ Excel と同様のことができる。
年、月、日、曜日を表示する
YEAR関数、MONTH関数、DAY関数を使えば、年、月、日を抜き出すことができる。ただ、これらの関数を使うと、「返されるデータの種類」を「一行テキスト」していても数値になる。（2014なら 2,014 といったように）
ここで TEXT 関数を使えば =TEXT([日付データ], "YYYY") で文字列として年を取り出すことができる。 Excel 同様に1月を 01 と取り出したいのであれば、=TEXT([日付データ], "MM") となる。
残念ながら "g" や "ge" で元号はとれない。
曜日は若干数式が違うので注意してほしい。
Excel であれば、=TEXT([日付データ],"aaa") で「水」になり、=TEXT([日付データ],"aaaa")で「水曜日」になる。 "ddd" で「Web」になり、"dddd" で「Wednesday」となるが、SharePoint の場合は以下になる。
=TEXT([日付データ], "ddd") 「水」
=TEXT([日付データ], "dddd") 「水曜日」
"aaa" はなく、逆に Fri や Friday と表示させたい場合は、関数で工夫するしかない。
期間/時間の計算
シリアル値を使っているので、日数計算は単純な加減計算となる。
シリアル値で計算後の時間の表記については TEXT 関数を使う。特に TEXT 関数の "[h]:mm" で24時間を超える数値の表記が SharePoint リストの数式でも指定可能だ。
[追記]
Excel でもそうなのだが、1日はシリアル値「1」なので、1をプラスすればよいが、時間になった場合は、12時間だから 0.5 と考えてはいけない。もし、11分だったら 11/60/24 で 0.0076333333.... と循環小数になる。安易に小数点による計算を勧める記事が多すぎるのは Excel のシリアル値操作での失敗例もしくは正しい使い方をご存じないからだろう。
時間の計算はシリアル値を直接操作せずに、文字列 "h:mm:ss" を加算するだけでよい。
たとえば、開始時間をリスト アイテムとして入力させて、集計値の列で予定終了時間を 4 時間後にする場合、入力する式は以下になる。
=TEXT(開始時間+"4:00:00","YYYY/MM/DD h:mm")
TEXT関数を使わなければ、集計値の列ではシリアル値が表示されてしまうため表示形式を関数で指定している。
もちろん、シリアル値として計算するので、60分を超えれば1時間繰り上がる、24時間を超えれば1日繰り上がる。（減算もしかり）
注意点は "h:mm:ss" は時刻表記であることだ。 h は 23 を超えることをはできない、mm や ss は 60 を超えることができない、という点だ。 70分を追加したいのであれば、 "1:10:00" である。26時間を指定したいのであれば、まずシリアル値に 1 を足した後で "2:00:00" を加算してほしい。
その他（日付関連）
シリアル値を使っているので、月末の日は翌月の1日から１を引けばわかる。その時、翌月の1日を作成するには DATE 関数を使って文字列の年月日表記をシリアル値してから１を引く。
=DATE("2014","3","1")-1
上記を応用すれば、うるう年の判定も可能だ。上記式の日付が "29" だったらうるう年である。
[注意] 四則演算結果の集計値の列を SharePoint リストで集計できない
これはできると思ってしまう「落とし穴」だが、たとえば、[数量] * [単価] で金額を計算する集計値の列を作り、SharePoint リストの「集計」を使ってその合計を出そうとしたくなるだろう。
しかし、残念ながらこの集計はできない。
あまり凝ったことは SharePoint リストでやらないほうがいい
ここまで紹介して、最後の結論がこれだとがっかりくるだろう。上述した計算結果を集計できない、また、[h]:mm の時間も集計できない。[h]:mm はTEXT関数で文字列になっているからである。Excel のセルの表示形式はセルの実体は変えずに見た目だけを変更しているが、SharePoint リストの列には表示形式という機能はない。なにより、入力するエリアの横幅がせまく、数式オートコンプリートも効かないため、長い数式は鬼門である。
あくまで、入力するユーザーにとって、または、リスト表示上最低限必要なものに限って数式を使ってデータの表現方法を変えるにとどめ、複雑な計算式は Excel にエクスポートしてからおこなったほうが無難だと言える。ここでも Excel のワークシート作成テクニックである「入力」－「計算」－「出力」の考え方は活きてくる。
注意してほしいのは、データ接続によって Excel 側に出された Excel の計算結果は元の SharePoint リストに標準の機能として反映されないという点だ。
それでも、Excel の計算結果を SharePoint のページ上でみたい、（つまり、「出力」）となれば、Excel Services である。次回は Excel Services を紹介したい。
[参考]
Ｏｆｆｉｃｅ デベロッパー センター 「集計フィールドの数式」
http://msdn.microsoft.com/ja-jp/library/office/bb862071%28v=office.14%29.aspx
Office Online 「データ計算の説明」
http://office.microsoft.com/ja-jp/windows-sharepoint-services-help/HA010379914.aspx?CTT=1
Office Online 「列のタイプおよびオプション」
http://office.microsoft.com/ja-jp/office365-sharepoint-online-enterprise-help/HA010302193.aspx
=TEXT([日付データ], "ddd") 「水」
=TEXT([日付データ], "dddd") 「水曜日」
"aaa" はなく、逆に Fri や Friday と表示させたい場合は、関数で工夫するしかない。
期間/時間の計算
シリアル値を使っているので、日数計算は単純な加減計算となる。
シリアル値で計算後の時間の表記については TEXT 関数を使う。特に TEXT 関数の "[h]:mm" で24時間を超える数値の表記が SharePoint リストの数式でも指定可能だ。
[追記]
Excel でもそうなのだが、1日はシリアル値「1」なので、1をプラスすればよいが、時間になった場合は、12時間だから 0.5 と考えてはいけない。もし、11分だったら 11/60/24 で 0.0076333333.... と循環小数になる。安易に小数点による計算を勧める記事が多すぎるのは Excel のシリアル値操作での失敗例もしくは正しい使い方をご存じないからだろう。
時間の計算はシリアル値を直接操作せずに、文字列 "h:mm:ss" を加算するだけでよい。
たとえば、開始時間をリスト アイテムとして入力させて、集計値の列で予定終了時間を 4 時間後にする場合、入力する式は以下になる。
=TEXT(開始時間+"4:00:00","YYYY/MM/DD h:mm")
TEXT関数を使わなければ、集計値の列ではシリアル値が表示されてしまうため表示形式を関数で指定している。
もちろん、シリアル値として計算するので、60分を超えれば1時間繰り上がる、24時間を超えれば1日繰り上がる。（減算もしかり）
注意点は "h:mm:ss" は時刻表記であることだ。 h は 23 を超えることをはできない、mm や ss は 60 を超えることができない、という点だ。 70分を追加したいのであれば、 "1:10:00" である。26時間を指定したいのであれば、まずシリアル値に 1 を足した後で "2:00:00" を加算してほしい。
その他（日付関連）
シリアル値を使っているので、月末の日は翌月の1日から１を引けばわかる。その時、翌月の1日を作成するには DATE 関数を使って文字列の年月日表記をシリアル値してから１を引く。
=DATE("2014","3","1")-1
上記を応用すれば、うるう年の判定も可能だ。上記式の日付が "29" だったらうるう年である。
[注意] 四則演算結果の集計値の列を SharePoint リストで集計できない
これはできると思ってしまう「落とし穴」だが、たとえば、[数量] * [単価] で金額を計算する集計値の列を作り、SharePoint リストの「集計」を使ってその合計を出そうとしたくなるだろう。
しかし、残念ながらこの集計はできない。
あまり凝ったことは SharePoint リストでやらないほうがいい
ここまで紹介して、最後の結論がこれだとがっかりくるだろう。上述した計算結果を集計できない、また、[h]:mm の時間も集計できない。[h]:mm はTEXT関数で文字列になっているからである。Excel のセルの表示形式はセルの実体は変えずに見た目だけを変更しているが、SharePoint リストの列には表示形式という機能はない。なにより、入力するエリアの横幅がせまく、数式オートコンプリートも効かないため、長い数式は鬼門である。
あくまで、入力するユーザーにとって、または、リスト表示上最低限必要なものに限って数式を使ってデータの表現方法を変えるにとどめ、複雑な計算式は Excel にエクスポートしてからおこなったほうが無難だと言える。ここでも Excel のワークシート作成テクニックである「入力」－「計算」－「出力」の考え方は活きてくる。
注意してほしいのは、データ接続によって Excel 側に出された Excel の計算結果は元の SharePoint リストに標準の機能として反映されないという点だ。
それでも、Excel の計算結果を SharePoint のページ上でみたい、（つまり、「出力」）となれば、Excel Services である。次回は Excel Services を紹介したい。
[参考]
Ｏｆｆｉｃｅ デベロッパー センター 「集計フィールドの数式」
http://msdn.microsoft.com/ja-jp/library/office/bb862071%28v=office.14%29.aspx
Office Online 「データ計算の説明」
http://office.microsoft.com/ja-jp/windows-sharepoint-services-help/HA010379914.aspx?CTT=1
Office Online 「列のタイプおよびオプション」
http://office.microsoft.com/ja-jp/office365-sharepoint-online-enterprise-help/HA010302193.aspx
0 コメント:
コメントを投稿