Excel で 数値を K (キロ) や M (メガ) で表示する

(使用している Excel は Excel 2016 です)

会社で扱う資料などでは 金額が大きいため 「〇千円」や「〇K円」のように 桁を減らして記載することがあります。

"K" は "Kg" の "K" で 1000倍、 "M" は 100万倍です。

WRITE_0921_01

今回は、これをユーザー定義書式で実現する方法です。

このような書式の確認では TEXT ワークシート関数を使うのが わかりやすくて良いです。

TEXT(値,表示形式)

今回の話からはそれますが TEXT ワークシート関数は文中の値を整形したいときに 使える便利な関数です。

="今日は" & TEXT(TODAY(),"mm月dd日") & "です"

WRITE_0921_02

まずは普通の数値書式。

WRITE_0921_03

普通ですね。

数値を 3桁上げるには 数値書式の最後にカンマを付けます。

#,##0,

WRITE_0921_04

1000未満の 3桁が消えました。

自動的に四捨五入もされます。

この書式に "K円" などを付けます。

#,##0, "K円"

WRITE_0921_05

"M円" で 6桁上げる場合はカンマが 2つです。

#,##0,, "M円"

WRITE_0921_06

100万単位だと大き過ぎるときは 小数表記も併用できます。

#,##0.0,, "M円"

WRITE_0921_07

bash の ":"(コロン) は、builtin command

(使っている Linux は Ubuntu 14.04.3 LTS です)

bash のシェルスクリプトを読んでいると ときどき ":" (コロン) が登場します。 この ":" は bash の builtin です。

実行すると、、、

$ :

何も返ってきませんが エラーも出ません。

ちなみに ";"(セミコロン) だと 次のようにエラーが出ます。

$ ;
-bash: syntax error near unexpected token `;'

type コマンドで見ると builtin となってます。

$ type :
: is a shell builtin

次のようにコメントとして使うことができます。

: comment comment
: comment comment
: comment comment

コマンドプロンプトの REM と同じですね。

何も返さないので 次のようにして 空ファイルを作成することもできます。

$ : > blank.log

プロセス置換と組み合わせて 次のようにして空ファイルを作成することもできます。

$ cat <(:)> blank.log

知っていれば読めますが 知らないと記号にしか見えません。

Excel の 罫線に関するショートカット 2つ

(使用している Excel は Excel 2016 です)

今回は Excel の罫線に関するショートカットを 2つ紹介します。 良く使うものがショートカットになっています。

[Ctrl] + [Shift] + [&]

まず、こちら。

[Ctrl] + [Shift] + [&]

選択範囲に 外枠を引くショートカットです。

WRITE_0918_01

外枠を引くだけで 範囲内の罫線を消したりはしません。

WRITE_0918_02

これは「罫線」のメニューにある「外枠」と同じ動きです。

WRITE_0918_03

[Ctrl] + [Shift] + [_]

次に、こちら。

[Ctrl] + [Shift] + [_]

選択範囲内の罫線を消すショートカットです。

WRITE_0918_04

不要な罫線をまとめて消せるので便利です。

bash の プロセス置換

script コマンドを使った小技です。
(使っている Linux は Ubuntu 14.04.3 LTS です)

bash には 「プロセス置換」という機能があります。

Process Substitution

    Process  substitution is supported on systems that support named pipes (FIFOs) or the /dev/fd method of naming
    open files.  It takes the form of <(list) or >(list).  The process list is run with its input or  output  con‐
    nected  to a FIFO or some file in /dev/fd.  The name of this file is passed as an argument to the current com‐
    mand as the result of the expansion.  If the >(list) form is used, writing to the file will provide input  for
    list.   If  the  <(list)  form  is used, the file passed as an argument should be read to obtain the output of
    list.

    When available, process substitution is performed simultaneously with parameter and variable  expansion,  com‐
    mand substitution, and arithmetic expansion.

処理中の一時ファイルの作成を減らせる機能で、 無くても他の方法で代用は効くのですが 良い感じで使うとコマンドが読みやすくなったりします。

書き方は、次の 2つ。

<(コマンド)
>(コマンド)

名前の似てる「コマンド置換」と書き方も似てますね。

コマンド置換とプロセス置換

$(コマンド)   #コマンド置換

「コマンド置換」は、コマンドの結果を コマンドやコマンドの引数として使用することができました。

$ echo $(echo date)

date
$ $(echo date)

Thu Dec  8 22:58:19 JST 2018

「プロセス置換」は、コマンドの結果を ファイルの入力のように扱ったり 出力をコマンドに渡したりできます。

<(コマンド)

まずは "<(コマンド)" の方ですが、コマンドで入力ファイルのパスを 指定するところに書くことができます。

よく使われる例としては diff コマンドです。
diff コマンドは、2つのファイルを比較する際に 標準出力が 1つしか指定できないため 2つのコマンドの実行結果を比較したい場合 比較するコマンドの片方は実体が必要になります。

$ ls -l /var/xxxx > xxxx.txt
$ ls -l /var/yyyy > yyyy.txt
$ diff xxxx.txt yyyy.txt
$ ls -l /var/xxxx > xxxx.txt
$ ls -l /var/yyyy | diff - xxxx.txt

「プロセス置換」を使うと 一時ファイルを作成せずに 2つのコマンドの実行結果を そのまま比較することができます。

$ diff <(ls -l /var/xxxx) <(ls -l /var/yyyy)

コマンドも見やすいですね。
一時ファイルを削除する手間もなくなります。

echo で出力すると 「プロセス置換」の実体が ファイルデスクリプタだとわかります。

$ echo <(echo 1)

/dev/fd/63

わかりにくくなりますが 次のようなこともできます。

$ $(cat <(echo date))

Thu Dec  8 22:14:36 JST 2018

これは まず 次のように処理されて "date" が返ってきます。

$ echo date > /dev/fd/63
$ cat /dev/fd/63

返ってきた date を実行して 日時が出力されました。

$ date

Thu Dec  8 22:14:36 JST 2018

また 次のような do ... done のループで パイプでつなぐと 中の処理が別プロセスになり 環境変数を 上書きしてくれないような場合、、、

$ filename=none

$ ls | grep -v "test." | while read filepath
> do
>     filename=$(basename $filepath)
> done

$ echo $filename

none   #←別プロセスのため上書きされない

「プロセス置換」を使うと ファイルを指定するのと同じように 同じプロセスで処理することができます。

$ filename=none

$ while read filepath
> do
>     filename=$(basename $filepath)
> done < <(ls | grep -v "test.")

$ echo $filename

xxxxx.txt   #←同じプロセスのため上書きされた

>(コマンド)

次に ">(コマンド)" の方ですが、こちらは だいたいパイプでできてしまうため あまり良い例が思い浮かびませんでした。

よく使うのは次のように 標準エラー出力で 不要な行をオミットするケースすです。

$ command 2> >(grep -v ^Notice: >&2)

これは、標準エラー出力を一旦 プロセス置換で grep コマンドに渡して "Notice:" から 始まる行を省いて、再度標準エラー出力に渡しています。

他には tee コマンドと組み合わせて 特定の出力だけ振り分ける なんてことができます。

$ cat test.txt | \
>    tee >(grep ^case1 > case1.txt) \
>    tee >(grep ^case2 > case2.txt) \
>    tee >(grep ^case3 > case3.txt) \
>    > /dev/null

この例では、出力された行の先頭の文字によって 別のファイルに保存しています。

script コマンドで、別の端末の操作をモニタリングする

script コマンドを使った小技です。
(使っている Linux は Ubuntu 14.04.3 LTS です)

script コマンドは 作業のログをとってくれる便利なコマンドですが そのコマンドを利用すると 別の端末の操作をモニタリングすることができます。

端末 A

操作する側の 端末 A では、
script コマンドを "-f" オプション付きで実行します。

$ script -f /tmp/test.log
-f, --flush    run flush after each write

"-f" は 即時にログに出力するためのオプションで 接続が切れた場合にも安心のオプションです。

端末 B

モニタリングする側の 端末 B では、
tail コマンドを "-f" オプション付きで 実行し、 端末 A の script コマンドのログを参照します。

$ tail -f /tmp/test.log

この状態で 端末 A で操作をすると 端末 B のコンソールにも同じ内容が出力されます。

ダブルチェックや 指導する場合などに便利だと思います。

※ 間違えて "script -f" を実行した 端末 A で script コマンドの ログファイルを "tail -f" で開いてしまうと 無限ループに入ってしまうので注意してください。

Excel の ユーザー定義書式B

(使用している Excel は Excel 2016 です)

前回の「Excel の ユーザー定義書式A」の続きです。

Excel の ユーザー定義書式の記号に "*" があります。

*

"*" は、「 "*" の後ろの1文字のを繰り返す」という記号なのですが 色々と面白い使い方ができます。

まずは、右寄せ・左寄せです。

* @
@*  (@の後ろに半角スペース)

WRITE_0914_01

このようになります。

わかりにくいので 半角スペースを "_" に変えてみます。

*_@
@*_

WRITE_0914_02

この "_" は、書式なので テキストエディタなどにコピペすると値の "abc" のみになります。

次に左右分割です。

「様」などの敬称を付ける場合に使えます。

@*_様 (わかりやすさのために "_" にしています)

WRITE_0914_03

このように名前と敬称を左右にわけることができます。

他にもコメントの飾り文字を作ったり、、、

+++ @ *+

WRITE_0914_04

セルの幅に関係なく文字で埋めたりできます。

*+

WRITE_0914_05

(空だと表示されないため "0" などの値を入れておきます)

Excel の ユーザー定義書式A

(使用している Excel は Excel 2016 です)

前回の「Excel の ユーザー定義書式@」の続きです。

セミコロンは 書式の区切り記号ですが セミコロン自体を表示したい場合は エスケープする必要があります。

Excel の ユーザ定義書式のエスケープ記号は "!" です。

!

セミコロン以外の記号でも同じです。

たとえば 文字の最後に ";" を付ける場合、、、

@!;

WRITE_0913_01

このようになります。

エスケープ記号 "!" を表示する場合は "!!" にします。

!!

Excel の ユーザー定義書式@

(使用している Excel は Excel 2016 です)

前に セミコロン 3つのユーザー定義書式でセルの値を非表示にする というネタを書いたのですが 今回は これをもうちょっと掘り下げてみたいと思います。

ユーザー定義書式のセミコロンは 書式の区切り記号です。

WRITE_0912_01

つまりセミコロン 3つは 書式の区切りしかないため、 「書式が無い」という設定になります。

;;;

「書式が無い」ため 値が非表示になるわけです。

このユーザー定義書式ですが 3つのセミコロンで 4つの定義に分けられます。

それぞれは 次の設定になります。

@正の数の書式
A負の数の書式
Bゼロの書式
C文字の書式

例えば、次のように「Bゼロの書式」だけ設定すると

;;ゼロ;

WRITE_0912_02

このようにゼロのときにだけ表示されるようになります。 (この書式では "ゼロ" という文字を設定しています)

セミコロンを 2つにすると 「@正の数の書式」「A負の数の書式」「Bゼロの書式」を「書式なし」で設定したことになり 「C文字の書式」は未設定になります。

;;

未設定の「C文字の書式」は、標準の書式になります。

WRITE_0912_03

これを踏まえると 次のような「負のみ表示」する書式や

;-#,##0;;

ゼロのときに文字を赤くする書式を設定することができます。

#,##0;-#,##0;[赤]0;@

この [赤] は、 文字を赤色にする記号で "@" は文字をそのまま表示する記号です。

色は以下の記号が定義されています。

[黒][白][赤][緑][青][黄][紫][水]

定義されていない色も数字で指定できます。

[色1]〜[色56] ([色1]が[黒]、[色8]が[水])

正負くらいであれば 「条件付き書式」を使用しなくても 目立たせることができます。

[緑]#,##0;[青]-##0;[赤]0;[紫]@

WRITE_0912_04

PHP復習メモ - 文字列と加算子

PHP の落とし穴シリーズ。
(ちなみに PHP のバージョンは 5.5.9 です)

まず、PHP には 文字列でも数値として解釈できそうであれば 頑張って計算してくれる優しさがあります。

たとえば、次の処理、、、

$a = '1yen';
$b = '3円';

$c = $a + 1;
$d = $b + 2;

var_dump($a);
var_dump($b);
var_dump($c);
var_dump($d);

出力結果は、次のようになります。

string(4) "1yen"
string(4) "3円"
int(2)
int(5)

"1yen" や "3円" の数字部分だけが計算されました。

変数を使わなくても同じです。

var_dump('1yen' + 1);
var_dump('3円' + 2);
int(2)
int(5)

通常、こんな使い方はしないと思いますが、 数字部分だけ解釈したんだな、とまだ納得できます。

ところが、加算子の場合は、また微妙な動きをします。

$a = '1yen';
$b = '3円';

$a++;
$b++;

var_dump($a);
var_dump($b);
string(4) "1yeo"
string(4) "3円"

なんと "1yen" の "n" がインクリメントされて "o" になりました。 "円" の方はそのままです。

13 回インクリメントすると、、、

$a = '1yen';

foreach (range(1, 13) as $i) $a++;

var_dump($a);
string(4) "1yfa"

桁が上がり "1yen" の "e" まで "f" になってしまいました。

ちなみに減算子だとそのままです。

$a = '1yen';
$b = '3円';

$a--;
$b--;

var_dump($a);
var_dump($b);
string(4) "1yen"
string(4) "3円"

こういう動きをするから PHP は楽しいですね。

[参考]
PHP: 加算子/減算子 - Manual

Excel や Word から一括で画像を抽出する

(使用している Excel, Word は Excel, Word 2016 です)

Excel から簡単に画像を抽出するネタです。

Excel 2007 以降は ".xlsx" という拡張子で保存できますが これは ZIP ファイルとして解凍することができます。

それを利用した方法です。

次のように画像が貼り付けてあるシートがあります。

WRITE_0909_01

ファイルの拡張子を ".xlsx" を ".zip" に変更して解凍します。 (".xls" の場合は、".xlsx" で保存しなおしてください)

WRITE_0909_02

解凍すると、 画像がフォルダにまとめられています。

WRITE_0909_03

画像が多かったり 複数のシートに貼り付けてあるときなど 役に立ちます。

Word も同じように 拡張子 ".docx" を ".zip" に変更して解凍すれば 画像を簡単に抽出することができます。

Excel で 「改行」を検索・置換する

(使用している Excel は Excel 2016 です)

Excel で「改行」を削除したり 逆に「空白」を「改行」に置換したいときがあります。

WRITE_0908_01

セルの中であれば [Alt] + [Enter] で改行させることができますが 「検索と置換」ダイアログの中では [Alt] + [Enter] では改行しません。

WRITE_0908_02

セル以外の場所で「改行」を入力するには 以前、ここで 書いたように [Ctrl] + [J] を使用します。

なぜ "J" なのかについては 次のサイトに書いてありました。

[参考]
秘密の改行キー「Ctrl」+「J」(第24回) | 日経 xTECH(クロステック)

Google サイト内検索

Amazonアソシエイト