KNIME・EXCEL倉庫

KNIMEやEXCELマクロの備忘録。

【EXCEL】単位を表示させたい

EXCELを使っていると1つのセルの中に単位付きの数値を入力したい場面にたまに遭遇する。

・例えば、「100円」と一つのセルに入力すると、文字列として認識され、数式で金額を計算することができなくなる(下図)。

f:id:choron81:20210612220557p:plain

・これを解決するには、100と円を別のセルに入力し、100の入っているセルのみを対象として数式を入力すれば良い。すぐ思いつく範囲で、以下のように表記できる(下図)。個人的には真ん中が好みだが、用途によって変わる。

f:id:choron81:20210612221135p:plain


・敢えて一つのセルに単位まで入れ込んだ上で、数式を使えるようにする方法があるので、メモしておく。

・「セルの書式設定」から、ユーザー定義で、好きな単位を表示させることができる。この場合、セルの中身は100と数字で入力されており、表示は100円となっている。セルの中身が数字なので、関数で計算できる(下図)。

f:id:choron81:20210612221818p:plain

 

・現実的に、192837円といわれると、ぱっと幾らか分かりづらい。通貨単位は特に、3桁ごとにカンマで区切られていることが多く、その方が桁を読み取りやすい(192,837円と表示されていれば、19万円であるとぱっと見で分かる)。

・3桁区切りで表示したい場合、#,##0で数字を定義し、お好みの単位("円"など)を入力すれば良い(下図)。

f:id:choron81:20210612222531p:plain

・では、#,###0"円"と入力すると4桁区切りになるのかと思ったが、案外そうでもなく、同じように192,837,465円と表示された。

 

・この","は千の位、"0"は数値、"#"はワイルドカードを表しているっぽい(本当にそうかは知らないので、信じるかどうかは自己責任で)。#の数には関係なく、","が10^3単位を表しているのかもしれない。

・では、#,##0,"円"と入力するとどのように表示されるか?

f:id:choron81:20210612223859p:plain

・なるほど、192,837,465円の1,000の位以降が表示された。

・ということは、#,##0,"千円"とする方が正しそうだ。

・同様に、百万円単位であれば、#,##0,,"百万円"とすると良いと思われる。

 

終わり。

【EXCEL】「条件付き書式」の背景色を固定したい

・情報を視覚化するのに、「条件付き書式」という機能がある。

・セルの中の数値をもとに、書式を変更する機能であり、非常に便利。

・ただ、何らかの理由で書式はそのままに、セルの中の数値だけ弄りたい場面が、私の場合しばしばある。

・例えば、何らかの変数をもとにヒートマップを作成し、ヒートマップ上の異なる変数における分布を確認する場面など。

・関数だとどうしようもないので、マクロで背景色だけ抜き出せるようにした。

 

Sub 色写し()
Dim Cl, totNr, cNr As Long

totNr = Selection.Count

cNr = 1

Do While cNr < totNr + 1

Cl = Selection(cNr).DisplayFormat.Interior.Color
ActiveSheet.Range(Selection(cNr).Address).Interior.Color = Cl

cNr = cNr + 1

Loop

End Sub

 

・使い方は、条件付き書式で背景に色がついたセルを選択し、マクロを起動するだけ。

・そうすると、背景色として条件付き書式の背景が適用される。

・条件付き書式を解除しても、背景色はそのまま残る。

・セルの色を使うのではなく、DisplayFormatの色を使って色情報を抜き出すところがポイント。

 

・動作不良、その他不利益に関して、当方は一切補償しない。

 

終わり。

 

 

【EXCEL】読み取り専用で開いて欲しい

・開いたEXCEL Bookを読み取り専用にしたい場合は、ツールバーに「読み取り専用に切り替える」ボタンを作れば良い。

・一つのファイルをいろいろな人が使う場合、そのように対応してくれない人が必ず出てくるので、「読み取り専用で開きなさいよ」又は「パスを入れない限り編集できませんよ」という仕様にしたくなる。

・ファイルの仕様を変更するのは簡単で、名前を付けて保存を選択し、「ツール」「全般オプション」(下図)から、

f:id:choron81:20210521214112p:plain

ファイルの共有設定をする(下図)。

f:id:choron81:20210521214237p:plain

・制限をかけたければパスワードをかければ良いし、それほどではなく「読み取り専用で開いてね!」とアラートを出したいだけならパスワード欄を空欄にしてチェックボックスにレ点を入れれば良い。

・いろいろな人が見るようなファイル(予算情報など)に便利。

 

終わり。

 

 

 

【EXCEL】結合せずにセル同士の真ん中に表示したい

・セルの結合という機能がある。表の見栄えを良くしたいときに使うことも多い機能だけれど、如何せん表としての取り回しが悪くなって仕方がない。

・少しでも改善しないかと調べたところ、セル同士の真ん中に入力内容を表示する機能があった。

・「セルの書式設定」の「横位置」で、「選択範囲内で中央」を選択すると、それっぽく真ん中に表示されるようになる(下図)。

f:id:choron81:20210521001025p:plain

・Before(上)、After(下)

f:id:choron81:20210521001212p:plain

・結合されているわけではなく、一番左のセルに入っている"a"が3つのセルの真ん中に表示されている。そのため、真ん中のセルに何かを入力すると、以下のように表示される。

f:id:choron81:20210521001334p:plain

・表としての機能をなるべく損なわず、見栄えを少し良くしたい場合に便利。

 

・終わり。

 

 

【EXCEL】マクロで有効数字を揃えたい

・有効数字を揃えるのが面倒くさい。

・例えば、0.101、0.000345、1.234などのレンジの広いデータが大量にあった場合、有効数字を揃えて表示するのが非常に面倒なことになる。

・有効数字を2桁に揃える場合、少数で表示するならば0.10、0.00035、1.2となる(指数表示に関してはいったんおいておく)。

・書式の変更をデフォルトの関数でやることは難しいので、書式を変更するマクロを作成した(以下)。

 

Sub 有効数字書式()

Dim i, j, keta As Long
Dim y As Single
Dim z As String
On Error GoTo myError

'有効数字の桁数の設定(今回は2桁)
keta = 2

'とりあえず桁を小数点第一位でリフレッシュ(0でバグが出るので)
For i = 1 To Selection.Count
Selection(i).NumberFormatLocal = "0." & 0
Next i


'有効数字書式を設定
For i = 1 To Selection.Count
If Selection(i).Value + 0 = 0 Then
Selection(i).NumberFormatLocal = "0." & 0

Else
y = Log(Selection(i).Value) * (-1) / Log(10) + keta - 2
z = "0"
j = 0
For j = 0 To y
z = z & "0"
Next j
Selection(i).NumberFormatLocal = "0." & z
End If
Next i


myError:

End Sub

 

・NumberFormatLocal というので表示桁数を設定できるらしい。

・使用する際は、①有効数字を2桁に揃えたいセルを囲い、②マクロを実行する。

・マクロ実行前⇒マクロ実行後

f:id:choron81:20210521000326p:plain

 

・動作不良、その他不利益に関して、当方は一切補償しない。

 

・終わり

【EXCEL】逆相補鎖を出す関数を自作したい

・プライマーを発注する際に、塩基配列の逆相補鎖を求めることがしばしばある。

・基本的にソフトウェアでプライマーをデザインするが、EXCEL関数で逆相補鎖を求めることができるとちょっと楽しそうだと考え、練習がてらに以下の関数を作成した。

 

Function 逆相補鎖(a)

a = StrReverse(a)
a = LCase(a)

a = Replace(a, "a", "a1")
a = Replace(a, "t", "t1")
a = Replace(a, "c", "c1")
a = Replace(a, "g", "g1")

a = Replace(a, "a1", "t")
a = Replace(a, "t1", "a")
a = Replace(a, "c1", "g")
a = Replace(a, "g1", "c")

逆相補鎖 = a

End Function

・大文字はすべて小文字に変換することにした。

f:id:choron81:20210520235026p:plain

 

・動作不良、その他不利益に関して、当方は一切補償しない。

 

・終わり。

 

 

 

 

 

 

【EXCEL】関数を自作したい

VBAのfunctionプロシージャというやつで、関数を自作することができるらしい。

・例えば、指定のセルに入った数字を二乗する関数を作ってみる(下図)。

f:id:choron81:20210520232431p:plain


・これをEXCEL上で実行すると、こんな感じになる。

f:id:choron81:20210520232541p:plain

 

・関数としては、test(★★)で★★の二乗を返してくれるのだけれど、マクロブックを指定してあげないと、「そんな関数知りません!」という"#NAME?"エラーが出る。

EXCELに怒られないためには、アドインとして読み込むのも有効。

・個人的には、自作関数であることを忘れないように、わざわざ"personal.xlsb!"を入力している。

 

・終わり。