Excel Tips

最近、Excel方眼紙じゃない本来のExcelの使い方を実践してるのだけれど、あまりのExcel力の無さに萎える事しばしば。いったんは覚えるのだけれど、真当なExcelの使い道ってそうそう無いので、すぐ忘れる。
自分の鳥頭さ加減を嘆いても仕方ないので、覚えた事をメモっておくよ。

SI単位の設定

SI単位つうか、値の桁数に応じてKとかMとかつける方法。こんな風にするらしい。

「セルの書式設定」→「表示形式」→「ユーザー定義」
    #,##0,"KB"
または
    [<1000]0;[<1000000]#,##0,"KB";#,##0,,"MB"

出来栄えはこんな感じ。

でも桁数固定（例えばKB表記のみ）の場合はどうするんだろう（0.5KBとか表示させたい。

ほかのシートの値を参照

いわゆるマスターデータとなるシート（ないしは表）からキー項目に対応するレコードのどっかの値をルックアップする。

   =INDEX(sheetName!$X:$X, MATCH($Rn, sheetName!$Y:$Y, 0), 1)

        sheetName!$X;$X    参照したい領域
        Rn                 参照したい値（キー項目）
        sheetName!$Y:$Y    キー項目の領域
        n                  参照したい領域の列位置

文章説明だけだと、なんだかよく分んないけど、要するに（↓）こんなことやりたい。

キー項目が左端だったらVLOOKUPで十分なんだけど、この方法だと任意の列をキー項目にできて便利だった。

フィルターした状態の集計値を取得

SUBTOTALを使う。SUMやCOUNTだとオートフィルタに関係ないけど、SUBTOTALだと可視セルだけが集計対象になるんだって。

    =SUBTOTAL(3, $X:$Y)
        ※第１引数の値で集計方法を指定する（３はCOUNTだったけかな？

方眼紙にして文章を書くのではなく、こんなふうに集計作業させるとExcelはすごいできる子。特にピボットテーブルはスバラシイ。:-)
あと、持ってて良かったExcel Hacks

Excel Hacks―プロが教える究極のテクニック100選