カテゴリ:エクセル一般( 55 )

表から検索し表示する(VLOOKUP関数)

以前にIFのような使い方のCHOOSEでも紹介したが、表などから一致するデータを検索して
表示する場合には、やはりVLOOKUP関数を使うのが一般的だろうか。
e0080122_18263757.gif

ここでのポイントは『検索の型』引数にTRUEを指定する点だろうか・・・
TRUEに指定すると、検索値未満の値で検索してくれる。

また、VLOOKUPなどで検索する場合はかならずといっていいほど、検索する範囲を記した別表が必要だが、配列定数を使用すると別表が不要となる。
例えば上図の場合だが、セルD3に
=VLOOKUP(C3,{0,"なし";100,"1万円";200,"2万円";300,"5万円"},2,TRUE)
などと直接入力すれば大丈夫だ。しかし、検索範囲が広いと面倒くさいが、式的にもシート的にもスッキリとしていて見やすくなる。
また、配列定数は、列を『,』、行を『;』で区切る。さらに別表と思しき範囲は{}で囲まねばならない。

しかし、別表を作ったほうが明らかに早くできそうだ。
[PR]
by slayer0210 | 2005-12-26 18:38 | エクセル一般

IFのような使い方のCHOOSE

下表のようなものがあって、特別手当額を表示させるときは通常はIFやVLOOKUPなどを使う場合が妥当だ。
IFでやる場合は『=IF(C3=300,"5万円",IF(C3>=200,"2万円",IF(C3>=100,"1万円","なし")))』のような数式を作る(個人的だが)。だけど、何個もIFを入れ子していくと自分がなにをやってるか見失う場合も多い。

よって今回はCHOOSEを使ってみた。
D3セルに『=CHOOSE(C3/100+1,"なし","1万円","2万円","5万円")』と入力。
ここでポイントとなるのはインデックスとしてC3/100+1を使用する点だろうか。
またこのインデックスは小数点以下の値でも整数部分だけが使用されるのでINT等は不要だということだ。
e0080122_18751100.gif

CHOOSEを使うのは好みによるが、少なくとも数式的にはすっきりしてて見やすい。
しかしVLOOKUPなどを使うのが王道だとは思うが。
[PR]
by slayer0210 | 2005-12-24 18:14 | エクセル一般

CHOOSE関数でデータを選択

自分的には地味な関数の中にCHOOSE関数というのがある。
この関数は、引数のインデックス番号にあたるデータを取り出す関数だ。
下表のようなものがあった場合、商品番号(これがインデックスとなるのだが・・・)を入力すると
それに対応する値をD列に返す。
e0080122_2351361.gif

ここでは空白の場合にエラーが表示されないようにIF関数を用いてみたが、これは別にどうでもいいだろう。
もしこれをIF関数でやろうとするならば『=IF(C3=1,"タオル",IF(C3=2,"シャツ","靴下"))
という式が成り立つわけだが、選択肢が増えるとIF関数だと煩雑になってしまって分かりづらい。
CHOOSE関数は、選択肢が29まで大丈夫なのでかなり便利な点もあると思う。
また、代表的(?)な使い方かどうかは別として、日付から曜日を取り出す場合とかにも使用する。
e0080122_072911.gif

しかし、曜日を取り出す場合には『=TEXT(B3,"aaa")』のようにTEXT関数を使うのがより一般的かもしれない。
ま、人それぞれってことで。
[PR]
by slayer0210 | 2005-12-23 00:11 | エクセル一般

上と同じ値を入力

下図のように空白があり上と同じ値を入力したい場合は、空白のセルすべてを選択し
その後C3セルをアクティブにしてから『=上のセル番地』を指定してCtrl+Enterで確定。
e0080122_7505653.gif

するとこのように空白セルに上と同じ値が入力された
e0080122_752453.gif

[PR]
by slayer0210 | 2005-12-19 07:53 | エクセル一般

グラフにテキストボックス追加

下図のようなグラフがあったとして、任意の文字列を入れたい場合はグラフを選択してテキストボックスを使用すると容易にグラフ上に文字を入れられる。
e0080122_2184010.gif

また、セル上にある文字列をグラフ上に入れたい場合は(リンク)グラフを選択しておき
数式バーに『=』を入力してから目的のセル番地をクリックすれば、容易にグラフに文字列が入る。
e0080122_21125878.gif
e0080122_21152964.gif

ここで注意すべき点は、一度テキストボックスの選択を解除した後ではテキストボックス選択が難しいということだろうか。
[PR]
by slayer0210 | 2005-12-18 21:17 | エクセル一般

数行おきに合計

例えば下図のような表があって、数行おきに合計を求めたい場合がある。
e0080122_925191.gif

まず、E5セルにSUMなどを使って合計を求めておいて、E3:E5までを範囲選択し下方向へコピーすれば大丈夫だ。
e0080122_973325.gif

[PR]
by slayer0210 | 2005-12-13 09:08 | エクセル一般

時間表示を数値に直す

給料計算などで使用する場合が多いが、15:20などの時間を15.3に置き換えるには24を乗算するとよい。
また予めC列にはセルの表示形式を『標準』などにしておくとよいだろう。
e0080122_9433464.gif

[PR]
by slayer0210 | 2005-12-09 09:44 | エクセル一般

設定以下の値がある行に色をつける(条件付書式)

e0080122_99757.gif

上図のような表があったとして、基準に達していない値が1つでもあれば、その行全体に色をつける条件付書式を。この場合は基準値は200以下。
e0080122_913302.gif

このように色をつけたい部分全体を選択してから、条件付書式で『=countif($B2:$E2,"<=200")>=1』と入力。
すると下図のように行全体に色がついた。
e0080122_916392.gif

[PR]
by slayer0210 | 2005-12-09 09:18 | エクセル一般

重複しないデータを数える

同一列内にデータがあり、その重複しないデータ個数を数えなければならないことがある。
そんなときはcountifsumを使って求めると便利だった。
下図のようにB2セルに『=1/COUNTIF($A$2:$A$10,A2)』と入力し下までコピー。
あとはその結果をSUMで求めればデータ個数が求められる。
e0080122_8204610.gif

ここでのポイントは、COUNTIFで求めた答えで1を割る・・・ということだろう。
[PR]
by slayer0210 | 2005-12-09 08:22 | エクセル一般

重複データを除く表を作成(関数)

以前に重複データを削除するマクロを載せたが、今回は関数によって表を作成してみた。
まずは予めC列などに最初に出現するデータの行番号を表示させておく
=IF(B2<>B1,ROW(),"")』などと入力すればOKだろう。
次に表示させたい場所を(この場合はE2に『=INDEX($A$1:$A$11,SMALL($C$2:$C$11,ROW()-1))』と入力し
下にコピー。その要領の式をF列にも施せば完成だ。
e0080122_1020698.gif

[PR]
by slayer0210 | 2005-12-06 10:20 | エクセル一般