ワークシート関数 SUBTOTAL を使いこなす 1

Excel には SUBTOTAL という便利な関数があります。

基本的な使い方は SUM 関数や COUNT 関数と 同じように範囲を指定して使います。

WRITE_0513_01

違うのは上のように「集計方法」を指定することです。

SUBTOTAL 関数は 範囲に対して次のような 「集計方法」に指定で集計ができます。 (上の図の 9 は SUM になります)

指定集計関数
1AVERAGE
2COUNT
3COUNTA
4MAX
5MIN
6PRODUCT
7STDEV
8STDEVP
9SUM
10VAR
11VARP

引数で集計方法を指定できるのが SUBTOTAL 関数のスゴい使い方ではありません。 (もちろんそれもありますが)

SUBTOTAL 関数は SUBTOTAL 関数の結果を集計に含めない、という 他の関数にはないスゴい特徴があります。

次のように赤色と青色の範囲の小計を出すときに SUM 関数だと範囲の指定が面倒ですが SUBTOTAL 関数は範囲が被っても問題ないので 範囲の指定が簡単になります。

WRITE_0513_02

上の図の B5 セルの 6 は SUBTOTAL 関数の結果なので B9 セルの 21 には含まれないわけです。

次回に続きます。

ワークシート関数 SUMPRODUCT を使いこなす 3

SUMPRODUCT 編 最終回です。

次の表のように計算式だけでなく 条件も含めたい場合があります。 これも SUMPRODUCT で対応できてしまいます。

WRITE_0511_01

次の表のように計算式だけでなく 条件も含めたい場合があります。 これも SUMPRODUCT で対応できてしまいます。

次のような式になります。

WRITE_0511_02

上の表の「購入:○」を入れれば、動的に合計を計算してくれます。

3 つ目の引数がポイントです。
展開すると次のようになります。

  30 * 2 * ( ""  = "○")*1
+ 40 * 1 * ("○" = "○")*1
+ 80 * 4 * ("○" = "○")*1
+ 20 * 3 * ( ""  = "○")*1

ここで「 ( "" = "○") 」の部分は TRUE/FALSE を返すのですが Excel では TRUE = 1, FALSE = 0 という扱いになります。

ただ TRUE/FALSE のままではマズいので 1 を掛けて数値に変換しています。 この方法で、条件式を含むことができます。

計算用の列を持てば同じことができますし、式もシンプルにはなるのですが 計算用の列は、行を追加するときにちゃんと入れてくれなかったり 自分だけが使うのではないときに気を使うことがあります。

SUMPRODUCT だと式は複雑になりますが シートのセルはシンプルにすることができます。

VLOOKUP や INDIRECT ほど主演な感じではないですが 助演くらいはやってくれる便利な SUMPRODUCT です。

ワークシート関数 SUMPRODUCT を使いこなす 2

2 回目です。

前回は次のような 2 列の表の合計を計算しました。

WRITE_0510_01

SUMPRODUCT の引数に範囲を 2 つ指定していますが 実は範囲は 30 個まで指定することができます。

ですので、例えば次のような 3 列の表も。

WRITE_0510_02

列同士が掛け算なら SUMPRODUCT で計算することができます。

ただ、よく見ると上の表の「消費税」は 5% や 0% なので そのまま「単価」「数量」と掛けると 次のように、おかしなことになってしまいます。

WRITE_0510_03

これは当然次のような計算になっているためです。

(50 * 2 * 0.05) + (80 * 1 * 0.05) + (220 * 4 * 0) = 9

ここで消費税を 105% にしたり 税金の列を別に持たせてしまっては SUMPRODUCT を使う意味がありません。

こういうときは次のようにします。

WRITE_0510_04

消費税の範囲に「 +1 」をしています。

これは次のような計算になります。

(50 * 2 * (0.05+1)) + (80 * 1 * (0.05+1) + (220 * 4 * (0+1)) = 1609

慣れるまではややこしく感じるかもしれません。

あと 1 回続きます。

ワークシート関数 SUMPRODUCT を使いこなす 1

Excel には SUMPRODUCT という便利な関数があります。

例えば次のような表があります。

WRITE_0508_01

この表全体の合計金額を出したい場合 次のように 各行の「単価」と「数量」を掛けて 行の「金額」を計算して それを合計します。

WRITE_0508_02

これだとシート上に「金額」欄を 作る必要があります。

次のように SUMPRODUCT を使うと 簡単に合計が出せます。

WRITE_0508_03

SUMPRODUCT の引数には「範囲」を指定します。

上の図では、色分けしてありますが 範囲に名前を付けると 次のようにもっとスッキリわかりやすくなります。

WRITE_0508_04

SUMPRODUCT は 指定した範囲の対応する要素を掛けて その合計を計算してくれます。

次回は 応用編です。

PHP でクラスのオートロード

PHP でクラスをロードする場合、 クラスを定義したファイルを読み込んでおく必要がありますが PHP5 からは オートロードという自動化の仕組みが追加されました。

[参考]
PHP: クラスのオートローディング - Manual

Sample というクラスを Sample.php に定義した場合 PHP4 では次のようにインスタンスを作成していました。

include 'Sample.php';
$obj  = new Sample();

使用するクラスが少なければこれでも良いのですが クラスが大量にある場合は面倒です。

PHP5 からは __autoload 関数を定義しておけば 未定義のクラスを使用した時に この関数を実行してくれます。 引数にクラス名が入るので この関数の中でファイルの読み込めば 自動化できるわけです。

function __autoload($name) {
    include $name . '.php';
}

$obj1  = new Sample1();
$obj2  = new Sample2();
$obj3  = new Sample3();

これでソースをすっきりさせることができます。

パスワードをロックする

Linux では passwd コマンドを使って ユーザのパスワードをロックすることができます。

実験用ユーザの準備。

$ sudo useradd -M -d /tmp -s /bin/bash test01
$ echo "test01:hogehoge" | sudo chpasswd

当然、今作ったユーザ test01 に切り替えることができます。

$ su - test01
パスワード:

$ id
uid=1002(test01) gid=1002(test01) 所属グループ=1002(test01)

passwd コマンドに "-l" オプションを付けて実行します。

$ sudo passwd -l test01
passwd: password expiry information changed.

これで test01 に切り替えることができなくなります。

$ su - test01
パスワード:
su: 認証失敗

ログインもできません。

ただし root からは 切り替えることができます。

$ sudo su - test01

$ id
uid=1002(test01) gid=1002(test01) 所属グループ=1002(test01)

解除するには "-u" オプションを使います。

$ sudo passwd -u test01
passwd: password expiry information changed.

usermod コマンドを使っても同じことができます。

$ sudo usermod -L test01

"-L" がロックで "-U" が解除です。大文字なので注意です。

$ sudo usermod -U test01

同じことをしているので passwd コマンドでロックして usermod コマンドで解除することもできます。

ユーザの所属するグループを表示する

ユーザのグループを表示する groups というコマンドがあります。

$ groups

ubuntu adm dialout cdrom plugdev lpadmin sambashare admin

ユーザを指定しないと 現在のユーザのグループを表示します。

ユーザを指定するとコロンで区切って表示してくれます。

$ groups root

root : root

ユーザを複数指定することもできます。

$ groups root ubuntu

root : root
ubuntu : ubuntu adm dialout cdrom plugdev lpadmin sambashare admin

なので、こんなこともできます。

$ groups `cut -f 1 -d : /etc/passwd`

root : root
daemon : daemon
bin : bin
sys : sys
sync : nogroup
games : games
man : man
lp : lp
mail : mail
news : news
uucp : uucp
proxy : proxy
www-data : www-data
backup : backup
list : list
irc : irc
gnats : gnats
nobody : nogroup
libuuid : libuuid
syslog : syslog
sshd : nogroup
landscape : landscape
ubuntu : ubuntu adm dialout cdrom plugdev lpadmin sambashare admin
postgres : postgres ssl-cert

iPhone4 のモバイルデータ通信オフで通信不調のとき

iPhone4 を使っていてプランが「パケットし放題 for スマートフォン」なので ふだんモバイルデータ通信はオフにしています。

WRITE_0504_01

iOS5 に変えたあたりからたまに通信が不調になることがありました。 WiFi の通信ではなく SMS などの電話の方の通信です。

不調になるのは地下鉄などで長時間圏外になった後です。 その後、左上のアンテナの表示は通常に戻るのですが SMS の送受信が できない状態になることがあります。

機内モードのオン・オフをしても回復せず 左上の「Softbank」の表示が消えたりします。 再起動するまではずっとそのままでした。

ところがあるとき、何となくモバイルデータ通信をオンにすると回復しました。 オンにするのは数秒で、すぐオフにします。

対症療法ですが、同じことで困ってる人がいるかもしれないので書いてみました。

アクセス権を指定してディレクトリを作成する

mkdir コマンドで ディレクトリを作成するときに アクセス権を同時に設定することができます。

"-m" オプションを指定します。

$ mkdir -m 700 a

見てみます。

$ ls -lF

合計 4
drwx------ 2 ubuntu ubuntu 4096 2011-11-20 23:54 a/

当然 chmod を使ってもできますが バッチ処理で実行させるときなど コマンドの成否判定が 1 回で済むので便利なときがあります。

find で確認しながらコマンド実行する

find コマンドは "-exec" でコマンドを実行することができます。

$ find . -type f -exec echo {} \;

./bbb.txt
./ccc.txt
./aaa.txt

rm コマンドなどは 実行を確認するオプション "-i" があるので それを指定するとファイルごとに確認してくれます。

$ find . -type f -exec rm -i {} \;

rm: 通常の空ファイル `./bbb.txt'を削除しますか?
rm: 通常の空ファイル `./ccc.txt'を削除しますか?
rm: 通常の空ファイル `./aaa.txt'を削除しますか?

コマンド自体に確認のオプションがない場合 find コマンドで "-exec" の代わりに "-ok" を使用します。

$ find . -type f -ok rm {} \;

< rm ... ./bbb.txt > ?
< rm ... ./ccc.txt > ?
< rm ... ./aaa.txt > ?

こんな感じになります。

打ったコマンドに自信がないときや 目でチェックしながら流したいときに便利です。

Google サイト内検索

Amazonアソシエイト