2011年07月04日

「開発」タブ

Excel中級ExcelVBA入門 講座で頂いたご質問]

Excel2007、2010でVBA関連の機能を使いたい場合には、リボンに[開発]タブを表示させておくと便利ですが、バージョンによって[開発]タブの表示方法が異なるため迷う方が多いようです。

Excel2007の場合は、

 1)Officeボタンをクリックします。

 2)下方にある[Excelのオプション]をクリックします。

 3)最初に表示されている[基本設定]の画面の右側から
   "[開発]タブをリボンに表示する" 
   のチェックボックスをオンにします。

 4)[OK]ボタンを押します。


Excel2010の場合は、

 1)リボンから[ファイル]をクリックします。

 2)左側の下方にある[オプション]をクリックします。

 3)[リボンのユーザー設定]を選択します。

 4)画面の右側に表示される[リボンのユーザー設定]の
   [メインタブ]の一覧から
   [開発]タブのチェックボックスをオンにします。

 5)[OK]ボタンを押します。


いずれのバージョンでも、マクロの記録と実行を中心に利用するのであれば、リボンの[表示]タブの右端にある[マクロ]からも利用することができます。

また、目立たない場所にあるので気が付かない方が多いですが、「マクロの記録」は、ステータスバーの左端の[コマンド]と表示されている箇所の右隣に[マクロの記録]ボタンがありますので、こちらを使っても簡単に記録することができます。
posted by Three bit com at 10:00| Excel(エクセル)

2011年06月23日

Excelの画面をズーム

Excel関連講座で頂いたご質問]

セミナー中にExcelを使ってデモを行っていると、Excelの機能とは直接関係が無いことでも、デモ中の操作で質問をいただくことがあります。

中でも頻繁に質問されるのが、ワークシートの表示倍率を変更する方法です。

表示倍率は、Excelのズーム機能を使って変更することもできますが、マウスを使っている場合でしたら、Ctrlキー+マウスホイールを回転させることでも表示倍率を変更することができます。

 Ctrl+マウスホイールを上方向に回転 
           → 15%ずつ拡大表示されます。
 Ctrl+マウスホイールを下方向に回転
           → 15%ずつ縮小表示されます。

最近のバージョンのブラウザでも、同じ操作をすることができますので大変便利です。
まだお使いになったことがないようでしたら、是非試してみてください。
posted by Three bit com at 10:00| Excel(エクセル)

2010年06月04日

数値を漢字に変換するNUMBERSTRING関数

Excel(エクセル)で、数値を漢字に変換する NUMBERSTRING 関数があります。

これは、数値(NUMBER)を文字列(STRING)=漢字 に変換する
というような意味でしょうか。

余り利用する機会も多くはないように思いましたので、先日の記事の中でごく簡単にご紹介しましたが
「データを縦書きにして漢数字で印刷する場合に活用したい」
というリクエストを頂きましたので、もう少し詳しくご紹介してみます。

NUMBERSTRING関数の書式は以下のようになります。

(構文)
 NUMBERSTRING(数値, 表示方法)
(表示方法)
 第2引数の「表示方法」は、以下の数値が使えます。

  一、十、百・・・の場合は 1
  壱、壱拾、壱百・・・の場合は 2
  一、一〇、一〇〇・・・の場合は 3

一例をご紹介すると
(例1)
 =NUMBERSTRING(100, 1)
 数値の100を変換して 百 が返ってきます。

(例2)
 =NUMBERSTRING(100, 2)
 数値の100を変換して 壱百 が返ってきます。

(例3)
 =NUMBERSTRING(100, 3)
 数値の100を変換して 一〇〇 が返ってきます。

これもヘルプには見当たらない関数です。
他の人と共同で使うファイルで利用する場合には配慮が必要かと思いますが、便利な関数だと思います。

posted by Three bit com at 10:00| Excel(エクセル)

2010年05月25日

DATEDIF関数

Excelには、経過年数や月数などを求める DATEDIF という関数があります。

これはヘルプに出てこない関数で、マイクロソフトのサポート外の関数という位置づけだと困りますので、普段、セミナーではあまりご紹介していないのですが、先日のセミナーでご質問を頂きましたので、簡単にご紹介したいと思います。

この関数はもともとLotus1-2-3という表計算ソフトにある@DATEDIF関数を、Excelに移行した場合にエラーにならないようにするための関数です。
そのためExcelで使用することを想定していないのか、Excel2003や2007ではヘルプで引数を確認することができません。

関数の背景を考えると、開発の納品時など自分の手元を離れてしまうようなファイルの中では他の方法で求めるようにしていますが、使い方がとても簡単ですので便利だとは思います。

DATEDIF関数の書式は以下のようになります。

(構文)
 DATEDIF(開始日, 終了日, 単位)

(単位)
 第三引数の「単位」は、以下の文字列が使えます。
  年数を求める場合は "Y"
  月数を求める場合は "M"
  日数を求める場合は "D"
  開始日と終了日の年と月を無視して「日」の部分のみの差を求める場合 "MD"
   (1ヶ月未満の日数)
  開始日と終了日の年と日を無視して「月」の部分のみの差を求める場合 "YM"
   (1年未満の月数)
  開始日と終了日の年を無視して「日」の部分のみの差を求める場合 "YD"
   (1年未満の日数)

一例をご紹介すると
(例1)
 =DATEDIF("2010/5/25","2011/5/24","Y")
 これは2つの日付の間が1年に満たないので 0 が返ってきます。

(例2)
 =DATEDIF("2010/5/25","2011/5/25","Y")
 ちょうど1年経過しているので 1 が返ってきます。

 ※上記の" "で囲まれた部分をセル番地に置き換えれば、
   他の関数と同様にセルを使った計算が可能です。


DATEDIF関数を利用すると、
 「生年月日から、年齢を求める」
 「商品が発売になってからの販売年数を求める」
 「入社・入会してからの年数を求める」
などに利用することができます。

また、上記を上手く組み合わせると、
 「リースを開始してから何年何カ月経過したかを求める」
など、色々な場合に活用できます。

上記の「何年何カ月経過したか」を計算する場合はいくつか方法がありますが、例えば以下の計算式で求められます。

(例)
 =DATEDIF("2010/5/25","2011/6/25","Y")&"年"
   &DATEDIF("2010/5/25","2011/6/25","YM")&"カ月"
 ※本画面上では2行ですが入力時は1行です。

(意味)
 "2010/5/25"から"2011/6/25"までの経過年数("Y")と
 文字列 "年" と
 "2010/5/25"から"2011/6/25"までの1年未満の月数("YM")と
 文字列 "カ月"を 結合して表示する。
 という意味になります。

ついでにご紹介すると、他にもヘルプには表示されない関数があります。
NUMBERSTRING関数もその例で、数値を漢数字に置き換えて表示するものです。

(例1)
 =NUMBERSTRING(1,2)
 数字の 1 を 漢字の「壱」 に変換して表示します。
 第一引数が 変換したい数値
 第二引数が 変換方法 です。
 
ヘルプにない関数を利用する時には、
公開されていない引数を調べる必要があったり、
他の人とファイルを共同で利用する場合などには配慮が必要かとは思いますが、
ちょっとした業務では、面倒な計算式を組み立てなくても簡単に答えが出るものもあり、使い方次第だと思います。
 
posted by Three bit com at 10:00| Excel(エクセル)

2009年03月19日

アドインの作成

Excel(エクセル)の[ツール]メニューに表示される[アドイン]を作成するのにはどうしたらよいか、ご質問を頂くことがあります。

作成方法は、
まずアドインとして利用したいFunctionを作成します。
このブックを保存する際に、ファイルの種類として「アドイン」を指定します。
アドインは、名前を付けて保存のダイアログの「ファイルの種類」覧の一番下に表示されます。

 Microsoft Office Excel アドイン (*.xla)

保存するファイル名がアドインに表示される名前になりますので、分かりやすい名前を付けておきます。

Excelを再起動します。
[ツール]メニューから[アドイン]を選択すると、上記で保存したファイルの名前がアドイン名として表示されます。
利用したいアドイン名のチェックボックスをクリックして「OK」ボタンをクリックします。

ワークシート内で作成済みのFunctionを利用することが出来るようになります。

posted by Three bit com at 10:00| Excel(エクセル)

2009年03月09日

2007との違い

Excel(エクセル)2003と2007ではユーザインターフェースが変わりました。

ですが、VBA(マクロ)の作成に関してはほぼ同じと考えてよいと思います。
Excel2003で作成したほとんどのコードが問題なく2007で利用できますし、2007特有の機能を利用しない限りは2007の標準モジュールはそのまま2003で利用することができます。

開発環境のVBEのインターフェースにも変更はありませんので、開発時にはスムーズに移行できると思います。
Excel2003との違いで覚えておきたい2007のポイントを2つご紹介します。

1)開発タブの表示
 2007では初期設定ではマクロの作成関連メニューがリボンに表示されません。
 マクロ関連メニューを表示するには、
 Officeボタンをクリックし、右下に表示される「Excelのオプション」をクリックします。
 オプションのダイアログが表示されますので、「基本設定」の「Excelの使用に関する基本オプション」より
 「[開発]タブをリボンに表示する」にチェックをいれます。
 Excel2003のVisual Basicツールバーと類似のメニューが表示されます。

2)保存時のファイル形式
 2007ではマクロを含まないファイルとマクロを含むファイルではファイル形式が異なります。
 名前を付けて保存する際のファイル形式として「Excelマクロ有効ブック」 を指定してください。

社内で2003と2007の両方を利用している場合には、2003で開発したものを2007で動作確認する方がデバッグは楽なように思います。
利用環境に合わせて開発時から使い分けをした方がよいかも知れませんね。
posted by Three bit com at 10:00| Excel(エクセル)

2009年01月29日

行と列を入れ替える

Excel(エクセル)のワークシート内で、1列に入力されているデータを、1行のデータとして入れ替えたい場合があります。
例えば以下のような例です。

(元データ)
-----            
 A1
-----
 A
 B
 C

(入れ替え後)
-------------------------------
 B1    C1    D1    E1 
-------------------------------
 A     B     C     D

「形式を選択して貼り付け」を利用すると行と列を入れ替えてコピーすることができます。

 (元データ)を選択してコピーします。
 コピー先(上の場合ではB1)を選択します。
 [編集]メニュー→[形式を選択して貼り付け]→[行列を入れ替える]チェックボックスを
 オンにして[OK]ボタンを押します。

Excel(エクセル)関連のセミナー中のデモで利用すると、時々ご質問を頂くことがあります。
使用頻度はそれほど高くはありませんが、ちょっとした表の変換作業に使えますので便利ですね。

posted by Three bit com at 10:00| Excel(エクセル)

2009年01月25日

選択範囲にデータ入力

先日のExcelVBA(エクセルマクロ)の入門セミナーで面白いご質問を頂きました。
Excelのリストやデータベース(Access他)と組み合わせてプログラミングすると、便利なツールが作れるかも知れませんので、ご紹介いたします。

ドラッグされているセル範囲にデータを連続書き込みするためのプログラムです。
セル範囲をどのように認識するかがポイントですが、いくつか方法があると思います。

ここでは選択範囲の行数と列数を求めて、縦の行数分を繰り返すForの処理に加え、
横の列数分だけ繰り返すForの処理を追加してみました。
ExcelVBA入門セミナーレベルで繰り返し処理を始めて使う方には、Forの中にForを入れる繰り返し処理をマスターする練習になるかも知れません。

プログラムを見ただけではピンと来ない場合は、F8(ステップイン)を使ってExcelの動きを見ながら考えてみると分かりやすいと思います。

Sub 選択範囲にデータ入力()
  Dim 行数 As Long
  Dim 列数 As Integer

  行数 = Selection.Rows.Count '選択範囲の行数を数える
  列数 = Selection.Columns.Count '選択範囲の列数を数える

  '横の列数分だけ繰り返すためのFor
  For 横カウンタ = 0 To 列数 - 1
    '縦の行数分だけ繰り返すためのFor
    For 縦カウンタ = 0 To 行数 - 1
       ActiveCell.Offset(縦カウンタ, 横カウンタ).Value = "A"
    Next 縦カウンタ '次の行へ
  Next 横カウンタ '次の列へ
End Sub

基本的なVBAの文法を学習しただけでも、Excelでの実務経験が多い場合には面白いアイディアが色々と出てきますね。こういうところがExcelの面白さでもあると思いました。
posted by Three bit com at 10:00| Excel(エクセル)

2008年12月25日

可視セルの選択と集計

Excel(エクセル)でグループ集計を行う機能の一つに[データ]メニューの[集計]という機能があります。

Excelの応用レベルの講習会ではいつもご紹介しています。
ピボットテーブルに比べると機能名に特色がないせいか、意外にご存知ない方が多いようです。

この集計という機能を使うとExcel(エクセル)の行番号の左隣にアウトラインが表示され、一部のデータ(レコード)を非表示にしたり、再表示したりすることができます。

データを非表示にしたままの状態でレコードをコピーすると、非表示のデータも一緒にコピーされてしまいますので、範囲指定には一手間増えることになりご質問頂くことがあります。

Excel(エクセル)のジャンプ機能を使うと簡単に表示レコードだけを範囲指定することができます。

ジャンプ機能は
 あらかじめコピーしたい範囲をドラッグしておきます。
 [編集]メニューから[ジャンプ]を選択します。
 [ジャンプ]ダイアログボックスの左下にある[セル選択]ボタンを押します。
 [可視セル]というオプションをクリックし[OK]ボタンを押します。

非表示になっているセルは範囲指定されないため、画面はちょうどCtrlキーを押しながらドラッグしたような画面表示になって範囲が点滅します。

後は通常のコピーと貼り付けを使えば表示レコードだけをコピーすることができます。
便利な集計機能ではありますが、その特色を理解して使わないと不便に感じてしまうこともありますね。
posted by Three bit com at 10:00| Excel(エクセル)

2008年12月16日

範囲名(名前の定義)

Excel(エクセル)には、セル範囲に名前を付ける機能があります。
色々な場合に使えるので便利なのですが、Excelの応用レベルの講習会や、ExcelVBAのセミナー時にご紹介することが多いのですが8〜9割の方が使ったことがないようです。

操作手順は、

 あらかじめ名前を付けておきたいセル範囲をドラッグしておきます。
 [挿入]メニューから[名前]の[定義]を選択します。
 「名前の定義」ダイアログボックスが表示されますので、
 「名前」欄に範囲名として付けておきたい名前を入力します。
 [追加]ボタンを押した後に[OK]ボタンを押してダイアログを閉じます。

範囲名をつけたセル範囲にジャンプするには、

 Excel(エクセル)の左上にある「名前ボックス」の右側の「▼」ボタンから範囲名を選択するだけで、
 該当するセル範囲にジャンプすることができます。
 「名前ボックス」とは、数式バーの左側で通常はアクティブセルのセル番地が表示されている
 ボックスのことです。

よく利用するリストにジャンプする場合や印刷範囲を切り替えたい時にも便利ですが、他にはVLOOKUP関数の第二引数にもよく使われます。

VLOOKUPでデータを参照する際に、参照するデータ件数が増減した場合でも、関数を修正せずに範囲名の定義だけを修正すればよいのでお薦めです。
posted by Three bit com at 10:00| Excel(エクセル)

2008年12月09日

ワークシートを新規ブックにコピーする

ワークシートを、新しいブックを作成してそのブックにコピーする方法は簡単な記述ですが、かえって分かりにくいこともあるようです。

セミナーの実習時にマクロの記録を使ってVBAでの記述を調べて頂くと、よくそうしたご質問頂きます。
例えばSheet1を新規ブックに移動する処理は、マクロの記録では以下のように記録されます。

Sheets("Sheet1").Copy

Sheet1をコピーしているだけなのに、なぜ新しいブックが出るのだろう? という疑問が出てきます。
Copyというキーワードの上でF1(ヘルプ)を押して、WorksheetsオブジェクトのCopyメソッドを調べてみてください。

ヘルプ説明の解説欄に説明が書かれています。
Copyメソッドは本来引数としてBeforeまたはAfterのいずれかを指定することができますが、その両方を省略した場合は、新規ブックが自動的に作成され、シートはそのブック内にコピーされるようになっています。

マクロの記録は便利な面もある一方、記録されたままでは意味が分からないことや、そのまま実行すると動かないことも少なくありません。記録結果はヘルプ、Web、参考書などで再度正確な意味を調べてからプログラムの中で利用しておくと、次回以降のプログラミングに役立つことが多いように思います。
posted by Three bit com at 10:00| Excel(エクセル)

2008年12月02日

Format関数とCurrency型

ExcelVBAの入門レベルのセミナーで変数と変数の宣言方法をご説明しています。
単価や合計金額など、金額はCurrency型として定義するのが一般的です。

ところがこれをそのままセルに代入してしまうと、セルの書式が変わってしまうことがあります。
例えば以下のようなプログラムを、書式設定をしていないワークシートに対して実行すると、
セルA1には \100.00 のように\マークだけではなく、小数点以下2桁までが表示されるようになります。


Sub 練習()
  Dim 価格 As Currency
  価格 = 100
  Range("A1").Value = 価格
End Sub


このような動きを回避するには、予めワークシートのセルに書式を設定しておくか、
あるいは
Format関数を使って 価格の値をどのように表示するか定義してからセルに代入すると
期待通りの表示結果になります。


例えば3桁ごとにカンマを表示するだけならば以下のようなFormat関数を作成します。


Sub 練習()
  Dim 価格 As Currency
  価格 = 100
  Range("A1").Value = Format(価格, "#,##0")
End Sub
posted by Three bit com at 10:00| Excel(エクセル)

2008年11月25日

EndキーとEnd()

Excel VBA(エクセル マクロ)では、エクセルのコントロール方法を調べるのに「マクロの記録」という方法があり、セミナーの中でも紹介しています。
リスト内の上下左右の端へカーソルをジャンプさせる動きを調べるには、Endキーと相対参照キーを組み合わせて使うと簡単にVBAでの記載方法が分かります。

Excel(エクセル)でリストとは、1行目にフィールド名が並び1行目以降がデータ(レコード)になっている表のことを言いますが、この表内にカーソルを合わせて Endキーを押してから方向キー(↑↓← →)の1つを押すと、押した方向の最終データにカーソルがジャンプします。
(ショートカットキーはCtrl+方向キーです。)

これをマクロの記録中で、相対参照ボタンをOnにした状態で操作すると、

Selection.End(xlDown).Select

のように記録されます。
xlDown とは Ctrl+↓ を押した場合の動きと同じになります。
Excel(エクセル)の操作では勿論ですが、ExcelVBAでプログラムを書く際にも
セルの位置指定や移動、あるいは範囲指定方法を調べるヒントにになります。
posted by Three bit com at 10:00| Excel(エクセル)

2008年11月18日

文字列演算子 &

Excel(エクセル)のセルに入力する式内にも、Excel(エクセル)VBA(マクロ)のプログラム内にも記載できる &演算子があります。
簡単で便利なのでセミナーで式を作成するときに何気なく使っていると、使い方についてご質問を頂くことがあります。

& は文字列を結合して表示するときに使う演算子です。
例えば以下のようにA1に"東京"、B1に"花子" というデータが入力されている場合に、

C1に =A1&B1 という式を入力すると C1には "東京花子"という文字が表示されます。

-----------------------------
 A1  |  B1  |  C1
-----------------------------
東京    花子    東京花子

C1に入力する式を =A1&B1&"さん" と、修正すると以下のように表示が変わります。 
(式中に文字列を入力するときは "" ダブルクォーテーションで囲むというルールがあります。)

-----------------------------
 A1  |  B1  |  C1
-----------------------------
東京    花子    東京花子さん

この使い方をうまく応用しVLOOKUP関数と組み合わせると、複雑なデータのマッチングを行う際にも、ややこしいマクロ(VBA)を作成しなくてもよいことがあります。

またAccess(アクセス)のクロス集計クエリにも応用することができます。
posted by Three bit com at 10:00| Excel(エクセル)

2008年11月07日

RangeのValue

Excel(エクセル)VBAのセミナーで最近よくご質問頂くことの一つにRangeのValueプロパティがあります。
例えば、セルA1に10を入力(代入)する操作をプログラムで書くと

Range("A1").Value = 10 

のように書くことができます。
ですが、実際の現場(職場)の既存のプログラムには上記の Value が省略されていて

Range("A1") = 10 

という記載が多いようです。
いずれの方法でも全く同じ結果になりますので、Valueは省略しても構いません。

Excel(エクセル)はRangeのプロパティの指定を省略すると、自動的にValueを補って解釈してくれます。

プログラムソースに曖昧さを残したくないようでしたらValueを省略せずに記載した方が分かりやすいと思います。
また初めてプログラムを書く方の場合には、Valueは省略しないで書いた方が練習になることと思います。
posted by Three bit com at 10:00| Excel(エクセル)