PC Japan誌の記事（2006.7〜2006.12）: 第5回　表計算の活用と互換性（後編：関数など）

執筆者: matsui 発行日付: 2007/6/9 閲覧数: 22747 サイズは 25.36 KB

本気で使うOpenOffice.org (5) -------------------- これは「PCJapan」誌（ソフトバンク クリエイティブ）に2006年7月〜12月まで連載した記事のオリジナル原稿です。 1．PCJapan編集部のご厚意により、筆者のWebサイトで公開させてもらっています。 2．オリジナル原稿につき、雑誌に掲載した記事とは異なる部分があります。また、Webでの公開にあわせて一部手直しをしています。 3．この公開ドキュメントの文責は松井幹彦にあります。なお、著作権はソフトバンク クリエイティブ株式会社にあります。 4．雑誌掲載記事の公開という性質上、コピーの配布や転載はお断りさせていただきます。 ※内容は2006年7月〜12月現在のものです。最新バージョンとは違っている部分があります。 -------------------- 第5回　表計算の活用と互換性（後編：関数など） 前号では、表計算ソフトCalcの「ツール」メニューや「データ」メニューに注目して互換性情報を取り上げた。もう一回だけ表計算にこだわってみよう。今月は数式や関数の互換性を取り上げる。 1.バージョンアップで着実に進む関数の互換性 筆者は、2003年12月から2005年1月まで、オープンソースマガジン（当時はUNIX USER）誌にOpenOffice.orgについての連載記事を執筆した。そのときも関数の互換性を調査している（注01）。Excelで作成したサンプルファイルをCalcで読み込んでExcelと同様に関数が機能するかを試してみた（注02）。 注01：この記事は筆者の運営するWebサイトで読むことができる（画面01）。 http://oooug.jp/compati/unixuser/ 画面01 画面01 2年前に連載したUNIX USER誌（現「オープンソースマガジン」）の記事は筆者のWebサイトで読むことができる。OpenOffice.orgがどのように進歩したかを知ることができるだろう。 注02：筆者が執筆した解説書「Excel関数実践のツボ」（九天社）に収録してある全サンプルを試した（画面02）。 画面02 画面02 「Excel関数実践のツボ」九天社刊。この書籍に取り上げた114のサンプルをCalcで読み込んで互換性をチェックした。 このとき対象となったOpenOffice.orgのバージョンは1.1.0だ。さまざまな関数を使った114個のサンプルを試した結果は、うまく機能したものが92個。機能しなかったものが22個だった。単純に考えると、114分の92だから、約80％の成功率ということになる。 これをどう判断するかは難しいところだ。読者の中には「20％も機能しないものがあるのか」という感想を持つ人がいるかもしれないが、取り上げたサンプルはいろいろな種類の関数をさまざまな使い方で試している。もちろん、SUMやAVERAGEのような一般的な関数も含まれているが、PERCENTILEやCUMPRINCといった専門的な用途でしか利用しない関数も含んでいる。通常使用する一般的な関数の大部分は機能する方の92のサンプルに含まれているのだから、一般の人が普通に表計算ソフトを使用する範囲で考えると、80％よりもずっと良い結果となることは確かだ。このことを頭に置いて今回の記事を読み進んでほしい。 さて、前回の調査から2年以上経過している。現在のOpenOffice.orgのバージョンは2.0.3だ。メジャーバージョンアップを経て、果たして関数の互換性はどの程度進歩しただろうか。まず、この部分を見てみよう。 2.互換性が向上した関数 前回と同じサンプルをチェックした結果を発表すると、NGだった22ファイルのうち6ファイルがOKに変わっている。つまり、NGのまま残ったのは16個ということになる。エラーの率は14％に減少している。 NG部分の内容については後ほど取り上げることにして、まずNG→OKに変化した関数について解説しておこう。 OKになったのは、HYPERLINK関数、ROW関数やCOLUMN関数（別解や応用も含めて3ファイル）、SUM・COUNT・IFを組み合わせた配列数式および論理演算だ。以下の解説は、細かな話になってしまうが、なるべく分かりやすく書くので、ちょっと我慢して読んでみてほしい。 1）ハイパーリンクを扱うHYPERLINK関数は、旧バージョンのCalcには搭載されていなかった。しかし、Ver.2.0.0から、Calcでも使うことができるようになっている。この関数はセルに入力した文字列を組み合わせてハイパーリンクを組み立てることができるので「挿入」メニューの「ハイパーリンク」コマンドとはひと味違う使い方ができる（画面03）。 画面03 画面03　Excelで作成したワークシートをCalcで開く。旧バージョンでは装備していなかったHYPERLINK関数が追加され、Excelと同じように利用できるようになっている。 2）行番号や列番号を返すROW関数とCOLUMN関数は、「条件付き書式設定」機能の中で「数式条件」として使用できるようになった。 これは、少し解説を加える必要があるだろう。まずROW関数そのものについて説明し、そのあとで「条件付き書式」の中でどのように利用されているかを解説する。 ROW関数は、引数を省略して次のように記入した場合には、その数式が入力されている行や列の番号を返す。 ＝ROW() たとえば、この数式がA2セルに入力されていれば、「2」が表示される。したがって次の数式で奇数行と偶数行を区別することができる。 ＝MOD(ROW(),2) 旧バージョンのCalcでは、この数式を「条件付き書式設定」機能の中で「数式条件」として使用したときに、うまく機能しなかった。「条件付き書式」の中での利用というと特殊な使い方だと思う人がいるかもしれないが、画面04のような1行おきの背景色を設定する目的で利用される頻度の高い処理なのだ（注03）。現行バージョンになって、Calcの条件付き書式設定の中でもうまく機能するようになっている。 画面04 画面04　上がROW関数、下がCOLUMN関数を使って1行おきの背景色を設定している様子だ。「条件付き書式設定」と組み合わせることで、セル範囲の編集作業をしても崩れない書式を作成できる。（Excelファイル→Calcで開く） 注03：このような1行おきの色は、もちろん通常の書式機能を利用しても可能だ。しかし、その場合はセル範囲をカット＆ペーストするたびに、頻繁に書式を補修しなくてはならない。上記のように条件付き書式設定機能を使っておくと、この心配がない（通常の書式設定よりも条件付き書式設定の方が優先して表示される）。カット＆ペーストしても1行置きの色設定が崩れないので、安心して編集作業ができるようになる。 3）配列数式の中でIF関数が機能するようになった。それ以外にも多くの関数が配列数式の中でも利用できるようになっている。 これも少し解説を加える必要があるだろう。 配列数式というのは、画面05のようなものだ。 画面05 画面05　IF関数とSUM関数を組み合わせて配列数式の中で利用する。旧バージョンではIF関数のこのような使い方がうまく機能しなかった。（Excelファイル→Calcで開く） 初心者にはなじみが薄いかもしれないが、なかなか便利なので表計算ソフトのパワーユーザーはよく利用する機能だ。この例では、IF関数を配列数式の中で使って、集計条件に該当するデータの区分けをしている。 旧バージョンのCalcでは、このような配列数式の中でIF関数がうまく機能しなかった。これも前回のバージョンアップで改善されている。 ちなみに、IF関数以外にも多くの関数が配列数式に対応するようになった。画面06に旧バージョンのCalcと現行バージョンのCalcの結果を対比させておく。 画面06 画面06　Excelで作成したファイルをCalcで開いた状態だ。上が旧バージョンのCalcで、下が現行バージョンのCalcだ。配列数式に対応していなかったこれらの関数は、すべて正常に機能するようになった。 3.関数が非互換になる理由を押さえる 前項で見たように、関数の互換性はバージョンアップのたびに少しずつ改善されているが、それでも相変わらず互換性が達成されていない部分もある。うまく機能しない理由として、機能そのものがCalcに存在しないという場合がある。この項では、機能の非互換が元になって関数のエラーが発生するものを取り上げてみよう。 機能そのものの非互換が原因となるケースをまとめると次のようになる。 1）該当する機能がCalcに存在しない 2）日本語に特有の関数 3）文字コードに関する関数 4）OSの機能に依存する関数 それぞれ簡単に具体例を紹介していこう。 該当する機能がCalcに存在しない場合の代表例はPHONETIC関数だ。 Excelには「ふりがな」という機能があり、セルに入力した文字列のふりがな（日本語入力機能を使って変換したときの「よみ」）を保存している。そして、たとえば「並べ替え」をするときに、この「ふりがな」情報が使われる（したがってExcelではユーザー側で意識せずに50音順の並べ替えが可能）。 Calcには、このような「ふりがな」機能が用意されていない。PHONETIC関数はこの「ふりがな」情報を取り出す関数なので、Calcには存在しない。PHONETIC関数を含むファイルをCalcで開くと「#NAME?」エラーとなる（画面07）。 画面07 画面07　PHONETIC関数を使ったExcelのファイルをCalcで開く。Calcには「ふりがな」機能がないので、PHONETIC関数も存在しない。この右側の状態のファイルをExcel形式で保存しても失われたPHONETIC関数は復活しない。 2）〜3）は次節で取り上げることにして、先に4）を解説しよう。 OSの機能に依存する関数にはINFO関数がある。 画面08のように引数にキーワードを設定することで、操作環境についてのさまざまな情報を取得することができる。この関数もCalcに用意されていない。この関数が設定されているExcelのワークシートもCalcでは「#Name?」エラーとなる。 画面08 画面08　INFO関数を使ったExcelのファイルをCalcで開く。CalcにはINFO関数がないので、「#Name?」エラーとなっている。 4.文字列操作にまつわる関数の互換性 上で取り上げた非互換になる理由の2）〜3）は、文字列操作に関するものだ。この2つは、関連しているところがあるので、周辺の関連情報も含めて順番に解説していこう。 日本語に特有な関数の代表例として、ASC関数とJIS関数を挙げることができる。全角と半角を切り替える関数で、画面09のように利用する。この例から分かるように、住所録データベースなどの作成では重宝する関数だが、残念ながらどちらもCalcには用意されていない。 画面09 画面09　ASCは全角文字を半角に、JISは半角文字を全角に変換する関数だ。これらの関数もCalcには無いので、すべて「#Name?」エラーとなっている。（Excelファイル→Calcで開く） 関連してTRIM関数を取り上げよう。TRIM関数は、余分な空白文字を除去する関数だ。この関数はCalcにもあるが、全角・半角の扱いが違っている。ExcelのTRIM関数は空白が半角でも全角でも除去することができるが、CalcのTRIM関数は半角の空白に対してのみ有効だ。 さらに、もうひとつ関連情報を挙げておこう。文字列をバイト単位で取り扱う関数だ。たとえばLEN（文字数を返す関数）に対するLENB（文字数をバイト単位で返す）のように、末尾にBがつく関数がある。ほかにも、FINDB、LEFTB、MIDB、REPLACEB、RIGHTB、SERCHBなどがあるが、これらの関数はすべてCalcには存在しない。 画面10 画面10　CalcのTRIM関数は半角文字を対象としてのみ機能する。また、末尾に「B」のつく文字数をバイト単位で扱う一連の関数は、Calcには用意されていない。（Excelファイル→Calcで開く） 文字コードを扱うCODE関数とCHAR関数も互換性が一部欠如しているので注意が必要だ。画面11が両関数の結果を比較した様子だ。 画面11 画面11　CHAR関数とCODE関数もエラーが出る。全角文字に対応していないだけでなく、セル内改行の判定も違っているので注意する必要がある。（Excelファイル→Calcで開く） 操作環境によって変化するかもしれないので、あくまで筆者の環境での話だが、CalcのCHAR関数は文字コード〜255までが有効範囲で、しかも、その範囲内でもセル内改行などのコードがうまく判定できなかった（注04）。 これらの関数の取り扱いにはくれぐれも注意してほしい。 注04：以前のCalcのヘルプではCHAR関数の引数として文字コード「1〜255」および「8481〜39038」が有効となっていたが、現在のCalcのヘルプでは「2桁または3桁の整数の数値」に変更されている。 5.数式の互換性の基礎知識 関数の互換性でやっかいなのは、ちょっと見たところでは正しく働いていても、特殊な使い方や特定のケースでエラーとなるというような場合だ。そういったケースを取り上げる前提として、数式の互換性についてまとめておこう。 つぎは、比較のためにExcelとCalcの数式を並べて記述したものだ。（上がExcel、下がCalc） =AVERAGE(B4:B7,E4:E7,H4:H8) =AVERAGE(B4:B7;E4:E7;H4:H8) これを見ると分かるように、複数の引数をとる場合の、引数同士の区切り記号が違っている。Excelがカンマ（,）で区切るのに対してCalcはセミコロン（;）となっている。 話がちょっと脇道にそれるが、英米や日本では、小数点がピリオド（.）で位取りにカンマ（,）を使う。日本にいると、それが当たり前に思ってしまうが、ヨーロッパ圏を中心に世界全体では小数点がカンマ、桁区切りがピリオドという国の方が多い。国際標準もそうなっている。そのためCalcでは「カンマを引数の区切りに使わない」という仕様になっている。 おかげでCalcでは、引数として数値データを記入するときに、カンマもピリオドも有効だ。小数点だけでなく3桁カンマをつけて数値を入力することができるのである。また、慣れると「範囲の区切りはコロン、引数の区切りはセミコロン」というのも、なかなか分かりやすいと思う。 数式の記入方法でもうひとつ違いがある。シート名の記入方法だ。別シートを参照する数式を比較すると次のようになっている。違いは、シート名とセル番地のあいだの区切り記号が、Excelはエクスクラメーション（!）で区切るが、Calcではピリオド（.）で区切る。（上がExcel、下がCalc：注05） =Sheet1!A1 =表1.A1 注05：Excelでは「Sheet1」「Sheet2」……だが、Calcの場合にはデフォルトではシート名が「表1」「表2」……なので、上のように記入してみた。ただし、ここでは「Sheet」と「表」の違いは問題ではない。区切り記号の違いに注目してほしい。 こういった違いがあるが、ファイルを交換したときには、引数の区切りは「,」←→「;」に、シート名の区切りは「!」←→「.」に、適切に変換されるようになっている。つまり、ファイル交換時にユーザー側でこの部分の違いを意識する必要はない（画面12）。 画面12 画面12　引数の区切りはExcelがカンマなのに対して、Calcではセミコロンだ。この違いは、ExcelのワークシートをCalcで開いたときに自動的に変換されるので、ユーザー側で修正する必要はない。（Excelファイル→Calcで開く） さて、いま述べたように、シート名や引数の区切り記号の違いは、ExcelファイルをCalcで開いたときに自動的に変換されるが、それは一般的な数式の中に記入されている場合だ。セルアドレスを文字データとして記入しておき、それをINDIRECT関数を使って参照しているようなケースでは自動変換されないので注意する必要がある。画面13に具体例を挙げておこう。 画面13 画面13　INDIRECT関数を使ってセル参照を組み立てる。ここではシート名とセル番地を文字データで入力して組み合わせている。このようなケースでは、自動的に変換されないので、数式をユーザー側で修正してやる必要がある。（Excelファイル→Calcで開く。その後Calcで編集） 1.INDIRECT関数を使ってA列のデータとB列のデータを組み合わせて別シートへのセル参照を作成する。 2.そのファイルをCalcで開く。参照結果はエラーとなっている。 3.INDIRECT関数を使った数式はシート名の区切りが「!」となっている。この部分は自動的に変換されない。 4.関数の中身を修正する。Calcにあわせてシート名の区切りを「.」に変更する。 5.数式を編集した結果、正しく機能するようになった。 6.シート名に関する重要な情報 前項で、シート名とセル番地の区切り記号は通常は自動的に変換されると書いた。INDIRECT関数などを使うのでなければ、違いは自動的に吸収されるので、ユーザー側では何も気にする必要はない。しかし、シートの扱いの違いに関して知っておいた方がよい情報もあるので、ここでそれらを取り上げよう。 たとえば「=Sheet1!A1」という参照を含むExcelファイルをCalcで開くと画面14のように変換される。 画面14 画面14　別シートを参照する数式を設定したExcelファイルをCalcで開くとシート名の前に「$」マークが付与される。 1.Excelで、Sheet2のA1セルにSheet1を参照する式を記入する。 2.そのファイルをCalcで開く。 3.シートの区切り記号は「!」→「.」に自動変換される。 4.さらにシート名の前に$マークがついている。これはシート名を絶対参照として扱うことを表している。 ここで、シート名の先頭に「$」マークが付与されていることに注目してほしい。表計算では＄マークは絶対参照を表す記号だ。なぜこのような記号が付与されるのだろうか。−−−実は、Calcのシート名は相対参照なのだ。 注05で書いたように、Calcでは「表1」「表2」……というシート名が設定されているが、このCalcのシート名についている番号は「相対参照」となっている。たとえば、「表2」シートのセルに「=表1.A1」という数式が入力してあるとしよう。この数式を「表3」シートのセルにコピーすると、「=表2.A1」となる。セル座標の相対参照と同じようにシート名も相対的なものとみなして参照関係を調整するようになっているのである。 残念ながらExcelは、このような仕組みになっていない。Excelのシート名は、基本的に絶対参照なのだ。「Sheet1」はどこのシートにコピーしても「Sheet1」のままである。この違いを吸収するために、CalcではExcelのファイルを開いたときにシート名の先頭に自動的に「＄」マークを付与するのである。したがって、Calcの中でこの「＄」マークを削除すれば、Excelのシート名を相対参照として取り扱うことができる。 シート名に関して、もうひとつ重要な情報がある。Calcではシート名に括弧を使えないということだ。そのため、括弧を使用したシート名をもつExcelファイルをCalcで開くと括弧の部分がアンダーバーに置き換えられる。たとえばExcelでは「Sheet1」をコピーすると「Sheet1 (2)」というシート名が付与される。このシートをCalcで開くと、「Sheet1 _2_」というシート名に置き換えられる（画面15）。 画面15 画面15　Calcではシート名に括弧を使えない。そのためExcelのシート名に括弧が使われているとアンダーバーに置き換えるようになっている。（Excelファイル→Calcで開く） 1.Sheet1をコピーしてシートの複製を作成した。Excelのデフォルト設定では「Sheet1 (2)」というシート名が付けられる。 2.Sheet3に、新しく作成した「Sheet1 (2)」を参照する数式が作成してある。 3.そのファイルをCalcで開く。 4.Calcではシート名に括弧が使えないので、「Sheet1 _2_」という名前に変更されている。 5.作成してある数式で該当のシートを参照しているものがあると、変更したシート名になるように自動修正される。 この置き換えは、単にシートの見出しだけでなく、このシート名を使用している数式があれば、その部分も自動的に修正されるようになっている。したがって、ユーザー側でとくに手を加える必要はない。ただし、このファイルをExcel形式で保存したときに、元のシート名に戻す処理をしない。このアンダーバーのシート名の状態のままでExcelファイルとして保存されるので注意を要する（この点が引数区切りやシート区切りと扱いが違っている）。 7.空白文字の入力されたセルの取り扱い 「空白文字が入力されたセル」というのは「=""」が入力されているセルのことだ。表計算では、エラー表示やゼロ値の表示を抑止するときに、頻繁にこの手法が使われる。この「=""」が入力されているセルの取り扱いがExcelとCalcで違っているので注意が必要だ。 画面16 画面16　空白文字が入力されたセルの扱いを比較してみよう。上がExcelで作成したテスト用のワークシートだ。同じものをCalcでも作成する（下）。Excelはプラス演算子で集計ができない。SUM関数を使う必要がある。Calcでは、どちらもOKとなっている。（ExcelとCalcで同じものを作る） まず、画面16を見てほしい。Excelでは、演算子を使用した計算に空白文字を含めることができない。一方、Calcでは、このセルをゼロとして計算することができる（注06）。 続いて、画面17だ。どちらのソフトもISBLANK関数では「=""」はデータが入力されたものとして扱う（すなわちFALSEを返す）。ところが、COUNTBLANK関数では、Excelは「=""」をブランクとしてカウントする。このような違いはAVERAGEA関数でも現れる（AVERAGE関数では差は現れない）。 画面17 画面17　B6セルに「=""」を入力し、さまざまな関数を使って結果を比較する。ExcelはISBLANK関数では空白として扱わず、COUNTBLANK関数では空白として扱っている。それに対してCalcでは、両方の関数で空白でない扱いとなっている。また、AVERAGEA関数では、Excelが0として集計するのに対して、Calcでは対象から除外している。（ExcelとCalcで同じものを作る） 注06：Excelで「=""」をゼロとして計算に含めたいときはSUM関数などを使う必要がある（画面16参照）。 8.関数の取り扱いに関するその他の情報 OFFSET関数はセル座標をシフトする関数だ。REPLACE関数は文字列の一部を他の文字に置き換える関数だ。これらの関数では処理位置を数値で指定するようになっているが、Calcではこの数値にゼロ値やマイナス値を入力したときの取り扱いがうまくいかないことがあるので注意が必要だ。 それぞれの実例を挙げておこう。まず画面18だ。ExcelではREPLACE関数の「置き換え対象文字数」をゼロに設定することで「文字の挿入」が可能だ。ところがCalcではここにゼロを指定できない。画面17bの上段のようにエラーとなってしまう。同じ処理をするためには、別の方法に置き換える必要がある。一例を画面17bの下段に示す。ここではLEFT関数とRIGHT関数を使ったものに置き換えている。 画面18 画面18　REPLACE関数（ExcelとCalcで同じものを作る） 1.ExcelのREPLACE関数で、置換対象文字を0にして実行すると、文字の挿入ができる。 2.D列に入力したREPLACE関数で、C列の郵便番号を参照してハイフンを挿入している。 3.Calcでは、このような処理をしようとするとエラーとなってしまう。 4.別な処理に置き換える必要がある。ここではLEFT関数とRIGHTを組み合わせている。 続いて画面18を見てほしい。 ExcelのOFFSET関数では、範囲の指定をマイナスにすることで上方向や左方向の「高さ」や「幅」を指定できる。残念ながらCalcでは、このような指定をすると正しく計算されない。これはCalcの不具合といって良いかもしれない（注07）。一見したところ正しく計算されているように見えるので、注意を要する。 画面19 画面19　OFFSET関数では、まったく同じ数式を作成しても結果が違っている。−6ヶ月で設定しているが、過去8ヶ月の移動平均を算出している。一見すると、正しく計算されているように見えるので注意が必要だ。（Excelファイル→Calcで開く） 注07：ただし、マニュアルによると、Excelでもこのような使い方は正式サポートの対象外のようにも読み取れる。OFFSET関数の「高さ」や「幅」はプラスの数値で（＝左上のセルを起点にして）設定しておくのが安全だ。 最後に「配列定数」を取り上げよう。Excelには「配列定数」という機能がある。先ほど取り上げたのは「配列数式」で、こちらは「配列定数」だ。名称が似ているが、違う機能なので注意してほしい。 Excelには、引数を「配列定数」で指定できる関数がある。たとえばINDEX関数やMATCH関数だ。この方法（配列定数で引数を設定する方法）は、Calcでは使えない。画面20のようなエラーとなる。 画面20 画面20　INDEX関数とMATCH関数（Excelファイル→Calcで開く） 1.「配列定数」を引数にとることができる（参照テーブルを引数として数式の中に書き込んでしまうことができる）。 2.Calcで開くと、配列定数を組み込んだ INDEX関数やMATCH関数はエラーとなる。 3. 数式の中から配列定数の部分が切り捨てられてしまっている。 　　　　　　　　＊　　　　　　　　＊　　　　　　　＊ 以上、本稿では関数と数式の互換性に関する不具合を取り上げてきた。繰り返しになるが、ここで取り上げたのは、どちらかといえば特殊なケースだ。通常の使い方なら、ほとんどの場合は問題なく処理をおこなうことができるだろう。当然のことだが、サンプルで試した「何％がエラー」というような率が問題なのではなく、どのような関数がどのような理由で動かないのかという中身が重要だ。理由が分かれば解決方法も見つかるし、あらかじめ対策をすることもできる。また、どういったときに不具合が起こるかを頭に置いておくことで、いざというときに戸惑わずにすむだろう。 くどいようだが、そのための情報だということを理解して、この記事を活用してほしい。 ※　以上　「PCJapan」誌　2006年11月号に掲載

評価された記事: 0.00 (0 件の投票) このファイルの評価

カテゴリに戻る | カテゴリの一覧に戻る