Hatena::Grouparaistudy

czk-htnの日記

2009-06-07

[] Excel Hacks 06:45

Excel Hacks 第2版― プロが教える究極のテクニック140選

Excel Hacks 第2版― プロが教える究極のテクニック140選

セルの範囲に名前をつける(名前付きセル範囲)

セルを範囲選択して「数式バー」の左にある「名前ボックス」に名前を入れる。

以降、名前ボックスのプルダウンメニューから付けた名前を選択するだけで、その範囲が選択されるようになる。

INDIRECT

文字列として指定された参照の指し示す内容を返します。

引数で指定した文字列("A1"とか)でセルを指定する。文字列を組み立てることで複雑なセル指定が可能になる。

B列で対象セルより上のセルまでの最大値を取得する

=MAX(INDIRECT("B1:B" & ROW(B2:B2)-1)) 

OFFSET

指定した列数、行数だけオフセットした参照を返します。

セルを選択し[データ]-[入力規則]で入力規則を設定する。[入力値の種類]を「リスト」にし[元の値]に

=OFFSET($A$1,1,0,COUNTA($A$1:$A$100)-1,1)

とすると、A列(セルA1は見出し)に入力された値をセルのプルダウンメニューで選択できるようになる。A列に値を追加すれば、その値はプルダウンメニューにも追加される。

実際に使う場合は、OFFSETで指定した範囲に名前を定義して、入力規則には名前を指定するのが良い。

SUMPRODUCT

配列の対応する要素の積を合計した結果を返します。

ABC
1単価数量
21001100
32002400
440031200
580043200

本来は以下のように使う。各要素同士をかけ算した合計を求める。

=SUMPRODUCT(A2:A5,A2:A5)
=4900

つまり次の式と結果は同じになる。

=SUM(C2:C5)

でも次のように使えば、複数条件にマッチするセルの個数を数えることもできる。有効に○がついていて、商品名が C じゃないセルの数を求めている。本来の使い方と組み合わせれば少々複雑な条件での合計も可能。

ABC
1有効なら○商品名売上
2A100
32B200
4C400
5D800
=SUMPRODUCT((A2:A5="○")*(B2:B5<>"C"))
=> (A,D)

=SUMPRODUCT((A2:A5="○")*(B2:B5<>"C"), C2:C5)
=> 900 (AとDの売上合計)

各条件を括弧でくくる点に注意。くくらないと数式エラーとして #VALUE! になる。

Intersect

Application.*

Application.OnTime は指定した時間に任意のマクロを呼び出せる。

Application.DisplayAlerts  = [True | False]
Application.ScreenUpdating = [True | False]
Application.OnTime Now + TimeValue("00:15:00"), "macro"

ファイル名やシート名を取得する

ThisWorkbook.Name
ThisWorkbook.FullName
ActiveSheet.Name
rCell.parent.Name
トラックバック - http://araistudy.g.hatena.ne.jp/czk-htn/20090607