[Numbers(excel)] 計算式を文字列として評価する方法(他にも色々なテクニック紹介)

2023年7月14日

Numbers Tips

eyecatch Macを使っている人でわざわざOfficeを購入して、Excellを使っている人、金の無駄です。 無料のOfficeを使えることを知らないのでしょうか? Googleスプレッドシートであれば、インターネットブラウザで無料で使えるし、 昔からある、OpenOfficeは、色々なOSにインストールもできます。 他にも、LibreOfficeも比較的昔からある無料ツールで使い勝手も悪くないです。 紹介したこれらのツールは全て無料で使えるんですが、それでもOfficeを使っている人って、どう考えても仕事の効率がいいとは思いません。 そして、Macを使っている人であれば、NumbersというアプリがOSに最初からインストールされているので、これに使い慣れたら、Officeいらなくなりますよね。 そんなわけで、会社では、無駄経費となっている、Office撲滅運動を行っていて、Numbersを使い倒しているんですが、色々と使っていて、そのやり方を覚えておくと便利な事がいくつか合ったので、備忘録しておきます。 Excellでも同じことができると思うので、この手の計算表ソフトでは、どれでも使えるやり方じゃないかな〜と考えています。(他のアプリでは試していないので、出来なかったら教えてください)

計算式を文字列として評価する

セルに"=1+1"と入力すると、そのセルには、"2"という計算結果が表示されるのは、Excelを使う人が最低限知っている内容だと思いますが、 他のセルから、そのセルを参照した時にはその計算結果の値しか取得できません。 そのセルが計算式で書かれているのか、単なる数値(値)で書かれているかを判断するには、文字列の最初に"="が書かれているかを判定する必要があるので、それをどのようにするかは、以下のような式を書くことでできます。 # 文字列として参照する =FORMULATEXT(%参照セル) # 計算式かどうか判定する =IF(COUNTIF(FORMULATEXT(%参照セル),"=*")≥1,FORMULATEXT(%参照セル),%参照セル) 参照するセルは、"B2"などの値を入れると判定してくれます。

yyyymmddhhiiss => yyyy-mm-dd hh:ii:ss変換

システムなどに書き込まれたログで、データベースフォーマット形式になっていない文字列日時情報の書き方フォーマットを変換するやり方です。 ただし、この方法は、文字数が一定の場合のみで使える技です。 # yyyymmdd => yyyy-mm-dd =CONCATENATE(LEFT(セル,4),"-",MID(セル,5,2),"-",MID(セル,7,2)) # yyyymmddhhiiss => yyyy-mm-dd hh:ii:ss =CONCATENATE(LEFT(セル,4),"-",MID(セル,5,2),"-",MID(セル,7,2)," ",MID(セル,9,2),":",MID(セル,11,2),":",RIGHT(セル,2))

解説

1. LEFT、MID、RIGHTを使って、セルの文字列を部分的に切り抜く。 2. CONCATENATE()で複数の文字列をつなぎあわあせる。

エラーマークを出さない方法

他のセルを参照する計算式を書いた時に、次の画像のような、エラーマークが表示されてしまう場合があります。 今回紹介したセルの文字列操作を行った時に、参照したセルが数値の場合、何も書かれていないと、NULLというシステム値が返り、数字じゃないのでエラーになっているという状態です。 これを回避するには、2つの方法があり、

1. 参照する対象のセルがテキストであれば、セルのタイプを「自動」から「テキスト」に切り替える

画面右側に表示されるプロパティ情報の、「セル」タブ、「データフォーマット」のプルダウンで、「自動」から、「テキスト」に切り替えます。

2. そのセルの値が数値の場合、ブランクかどうかを判定する処理

=IF(セル="","",セル)

計算式をプログラミングで使える形に整形

Numbersで書かれた式を、そのままコピペでシステムで使おうとすると、Errorになります。 このえらーは、以下の文字列が、全角文字列に置き換わってしまうのが原因でした。
掛け算 *(アスタリスク) => ×(全角) 割り算 /(スラッシュ) => ÷(全角) 引き算 -(ハイフン) => ー(全角)
何故か、+(プラス)だけは、半角文字列だったので、これはスルーできます。 ということで、これらを一気に変換するのは、次の式を書きます。 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(セル,"×","*"),"÷","/"),"−","-") 変換したい文字列の数だけ、SUBSTITUTEという、文字列置換処理を書いていきます。 あまりスマートに見えない式ですが、色々調べた結果、これしか出来なかったんですよ・・・

あとがき

というワケで、今回は、仕事で使った、Numbersの式備忘録を書いてみました。 仕事をすればするほど、この手の備忘録は出てくるので、都度書き留めて行きたいと思います。 この記事を自分で見返した時に、調査した時間やら悩んだ時間の元が取れたという感覚、分かる人いるかな〜?

このブログを検索

ごあいさつ

このWebサイトは、独自思考で我が道を行くユゲタの少し尖った思考のTechブログです。 毎日興味がどんどん切り替わるので、テーマはマルチになっています。 もしかしたらアイデアに困っている人の助けになるかもしれません。