<   2005年 12月 ( 25 )   > この月の画像一覧

表から検索し表示する(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 | エクセル一般

年齢計算

年齢や経過年数などを調べるにはDATEDIF関数を使うと便利だ。
この関数は、ダイアログボックスが使用できないのでセルに直接入力となる。

下図の場合は、C2セルに『=DATEDIF(B2,TODAY(),"Y")』と入力する。
これで基本的な年齢は算出される。
しかし図ではそれだけでもアレなので、"歳"や"ヶ月"などオマケでつけてみた。
e0080122_17345651.gif

単位部分もかなり豊富なので目的に応じた引数を入力するとよいかと。
[PR]
by slayer0210 | 2005-12-26 17:36 | マクロ

重複した行を削除する(AdvancedFilter)

以前に重複データ削除でも紹介したが、今回はAdvancedFilterを使ってリストから削除するマクロを。
e0080122_825479.gif

Sub 重複削除()
 Range("a1").CurrentRegion.AdvancedFilter action:=xlFilterCopy, _
 copytorange:=Range("E1"), unique:=True
End Sub

今回は同一シートのE1セルに放出したが、場合に応じて他のシートに表示させてもいいと思う。
ActionxlFilterInPlaceを指定すれば、その位置で実行される。
ここでのポイントはUniqueTrueを指定し重複した行を無視して抽出する点だろうか。
[PR]
by slayer0210 | 2005-12-26 08:35 | マクロ

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 | エクセル一般

色に応じて個数を数える(ユーザー定義関数)

例えば下表のように、何色のセルが何個あるのか数えたい場合がある。そんな時の為にユーザー定義の関数を作ってみた。
e0080122_12184232.gif

Function CCOUNTIF(範囲 As Range, 色 As Range) As Integer

Dim myrange As Range
Dim iro As Integer
Dim i As Integer
 iro = 色.Interior.ColorIndex
 i = 0
   For Each myrange In 範囲
     If myrange.Interior.ColorIndex = iro Then
        i = i + 1
     End If
   Next
     CCOUNTIF = i
End Function

セルに直接この関数を入力してもいいが、関数のダイアログを表示させてもいいと。
e0080122_12211847.gif

今回は範囲の部分を絶対参照にしたが、場合に応じて・・・ということで。
また、引数の『色』の部分にはその色が表示されていセルをクリックすればよい。
関数名はCOUNTIFをまねてCCOUNTIFとしてみた。
[PR]
by slayer0210 | 2005-12-22 12:23 | マクロ

塗りつぶされたセル数を数える(ユーザー定義関数)

以前にユーザー定義関数(色合計)赤色の数を数えるでもやってみてはいるが、
今回は指定した範囲内に塗りつぶされたセルの数を数える関数を作ってみた。

Function CCOUNT(範囲 As Range) As Integer
   Dim myrange As Range
   Dim i As Integer
     i = 0
    For Each myrange In 範囲
      If myrange.Interior.ColorIndex <> xlNone Then
        i = i + 1
      End If
    Next
      CCOUNT = i
End Function

コードは出来るだけ簡略化したが、目的の関数が出来たのでよしとしよう。
e0080122_9564711.gif

e0080122_958453.gif

関数名はDCOUNTをまねてCCOUNTとした。
[PR]
by slayer0210 | 2005-12-22 09:59 | マクロ

目次シート作成(イベントマクロ)

以前に目次シートの作成でも紹介したが今回はイベントマクロで目的のシートを選択してみた。
シートには予め「目次シート」というシートを作成しておき、このシートモジュールに下記を記しておく。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Column <> 1 Or Target.Value = "" Then
    Exit Sub
  End If
    Sheets(ActiveCell.Value).Activate
End Sub

また、標準モジュールには

Sub 目次リスト作成()
 Dim i As Integer
 Columns(1).Clear
   For i = 1 To Sheets.Count
     Cells(i, 1).Value = Sheets(i).Name
   Next
End Sub
などと記しておき、目次シートに最初から作成していたボタンをクリックすると目次が更新されるようにしてみた。

以前目次シート作成で紹介したマクロはシート名を日付データなどにしていた場合にはリンク機能が上手く働かなかったが、今回は大丈夫だ。
相変わらず雑なコーディングだが目的のものができたので良かった。
e0080122_0313622.gif

e0080122_036115.gif

[PR]
by slayer0210 | 2005-12-22 00:36 | マクロ

他のデータの変更をさせない?

セルをロックして変更をさせない方法があるが、今回はイベントマクロを使ってselectさせない方法を。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim myrange As Range
 Set myrange = Application.Intersect(Range("C3:F7"), Selection)

   If myrange Is Nothing Then
     Range("C3").Select
   Else
     myrange.Select
   End If
End Sub
e0080122_0515425.gif

上図のように変更させたくない場所を選択した場合には、許可された範囲のみを選択しなおす。
なにかの折には役に立つかもしれないが・・・
[PR]
by slayer0210 | 2005-12-21 00:55 | マクロ

表を図として貼り付ける(リンクする)

リンクしない図として貼り付けるマクロを、リンクさせてみた。

Sub リンクする図として貼り付け()
  ActiveCell.CurrentRegion.Copy
  ActiveSheet.Pictures.Paste link:=True
  Application.CutCopyMode = False
End Sub
e0080122_095328.gif
上図のように元データを変更すると図も変更された。
[PR]
by slayer0210 | 2005-12-21 00:10 | マクロ