KENスクールブログ-パソコンスクール パソコン教室 KENスクール

VBA

【Excel】縦横の合計をワンクリックで求める

ビジネスで欠かせなくなったExcelスキル。
日々あらゆるデータを作成する中で、効率的にすばやく処理ができれば、
業務効率も上がり、「脱!残業」につながりますよ!

今回はよくある表作成の中で、縦横の合計をすばやく求める小技をご紹介します。

このような表があれば、通常は1つのセルに数式を使って結果を表示し、
オートフィルで他のセルへコピーします。
列と行にそれぞれ同様の作業が必要になります。

少し面倒ですよね・・・。

20160717_11

【実践】縦横の合計を一気に計算する

①結果を求めたいデータと合計欄を併せて範囲選択します。

20160717_12

②Σ(合計ボタン/オートSUMボタン)をクリックします。

20160717_13

※Σ(合計ボタン/オートSUMボタン)は[ホーム]タブ⇒[編集]にあります。

合計欄を一気に求めることが出来ました!

20160717_14

また、「平均」を求めたい場合は、
Σ(合計ボタン/オートSUMボタン)の▼から[平均]を選択します。

20160717_15

この方法を使用すれば、1度で必要な箇所に全て数式が入力されるので、
手順が少なくてすみます。

みなさんも是非試してみてください!

この記事に関連する講座

よりスピーディーにわかりやすく作成するExcelテクニック!

詳しくはこちら


カテゴリー: Excel Office VBA Windows ビジネス文書

【Excel】日付データから曜日を表示する

みなさんこんにちは。

Excelでデータを作成する場合に、「曜日欄」が必要なことはよくあります。
受講生の多くの方が「曜日」は、1つのセルに曜日を直接入力し、それ以降はオートフィルを使用して入力しています。
基本はその方法で問題ありません。
しかし、Excelの機能を駆使することで、もっと簡単にかつ、後々活用しやすいテクニックがあるのです!

POINT

日付データの中には曜日の情報も含まれています。
日付データから「曜日」情報だけを表示する設定をすることで、簡単に「曜日欄」の作成が完了します。

【曜日の表示方法】

①「曜日欄」に日付と同じデータを表示します。

このとき、「日付欄」を参照するように設定しておくことで、日付を変更した際に曜日も連動して変更されるようになります。

ここでは、セル[A4]に日付データを入力していますので、「=A4」と入力し確定します。

20160717_07

日付データの入力されているセルを参照しているので、同じ日付のデータが表示されます。
※オートフィルでコピーし、以降のセルも同様に入力しておきましょう。

②「曜日欄」を選択し、「セルの表示形式」を変更します。

右クリックメニュー(コンテキストメニュー) ⇒ [セルの書式設定]ダイアログボックス ⇒ [表示形式]タブ

20160717_08

[分類]は「ユーザー定義」を選択し、[種類]に「aaa」と入力します。

サンプルを確認した上で、OKをクリックします。
※なお、「aaaa」と入力すると「○曜日」と表示することが出来ます。

20160717_09

「曜日欄」に曜日を表示することが出来ました!

20160717_10

この方法で、カレンダーで曜日を調べて入力することなく、曜日を表示することが出来ます。
また、翌月には日付を入力変更するだけで曜日欄の処理は完了しちゃいます!

スケジュール表や勤怠管理表など、日付管理が必要なあらゆるデータで活用できますね!

みなさんも是非試してみてください!

この記事に関連する講座

表作成の「技」。すばやく美しい表作成へのテクニック満載!

詳しくはこちら


カテゴリー: Excel Office VBA

Excel関数・組み合わせパターン数を求める

Excelには様々な関数が用意されています。

計算式では複雑に煩雑になるようなものでも、関数を使用することですばやく結果を求めることができます。

今回は、組み合わせパターンを調べる関数をご紹介します。

20160717_06

【組み合わせパターンを調べる】

例えば、14種類のデータがあったとして、その中から3つを1セットとした場合、全部で何通りのパターンが考えられるか?

COMBIN関数での引数の指定
[総数]:種類の総数
[抜き取り数]:総数の中から1パターンでいくつ選ぶか

その選ぶ数(引数)にはそれぞれの数が表示されているセル番地を入力します。

20160717_05

COMBIN関数を使用すれば、複雑な計算をしなくても簡単に組み合わせパターン数を求めることが出来ます。

みなさんも是非試してみてください!

この記事に関連する講座

これであなたも関数の達人!

詳しくはこちら


カテゴリー: Excel Office VBA

【 Access VBA 】マクロからVBAへの変換方法

startup-593324_640

AccessにはExcelと同様に「マクロ」があります。
マクロを使用すると、さまざまな作業を自動化することができます。

操作方法は以下のとおりです。

① 「作成」タブを選択し、「マクロ」ボタンをクリックします。

img01

② マクロのアクションを選択する画面が表示されます。

img02

③ ドロップダウンリストをクリックすると、アクションの一覧が表示されます。

img03

④ 例えば「データベースを閉じる」を選択し、マクロを保存します。

マクロ名は「練習」とします。
img04

ナビゲーションウィンドウに保存したマクロが表示されます。

Excelのマクロは、記録をした後にVisual Basic Editorを開くと、記録内容をVBAに変換したコードをすぐに確認することができます。しかし、Accessのマクロは、記録をしただけではVBAに変換したコードを確認することができません。VBAに変換したコードを確認するためには、続けて以下の操作を行います。

⑤ 「デザイン」タブを選択し、「マクロをVisual Basicに変換」ボタンをクリックします。

img05

⑥ 下記ダイアログボックスが表示されたら、「変換」ボタンをクリックします。

img06

※「エラー処理コードを追加する」と「コメントを含める」については後述します。

変換が成功するとVisual Basic Editorが自動的に立ち上がり、プロジェクトエクスプローラー内に「変換するマクロ:練習」が作成されます。ダブルクリックをするとVBAに変換した内容を確認することができます。

img07

上図の赤線で囲まれている箇所は「コメント」です。VBAではシングルクォーテーションの後ろに書いた1行分がコメントになります。コメント部分は緑色で表示され、プログラム実行時に無視されます。プログラムの説明などをコメントとして残しておくと、処理内容を把握しやすくなります。

また、青線で囲まれている箇所は「エラー処理」です。プログラム実行時に何らかの理由でエラーが発生すると異常終了となりますが、エラー処理を記述することにより正常終了させることができます。マクロではエラー処理を行うことができませんが、「エラー処理コードを追加する」にチェックを入れるとプログラム内にエラー処理を含むことができます。

「DoCmd.RunCommand acCmdCloseDatabase」の部分が、今回記録した「データベースを閉じる」処理にあたります。マクロで行うことができる処理は、VBAでは「DoCmdオブジェクト」を使うことでほとんど実行できます。

DoCmdオブジェクトには「データベースを閉じる」以外にも多くのメソッドが用意されています。VBAで記述したいマクロのアクションがありましたら、ここまでの手順を行うことで、VBAではどのように記述されるのかを確認することができます。


カテゴリー: Access VBA プログラム

変数の適用範囲

laptop-340721_640

Excel VBAでの変数の適用範囲について記載します。
変数は、宣言した場所によって利用できる適用範囲が決まっています。

① プロシージャ内だけで使用できる変数

プロシージャ内で作成された変数は他のプロシージャでは使用ができません。
サンプルで確認しましょう。
img1

sample1プロシージャでmessage変数が使用できています。
img2

sample2プロシージャを実行するとエラーになります。
img3
sample1プロシージャ内で宣言したmessage変数をsample2プロシージャで使用しているのでエラーになります。

② モジュール内の全てのプロシージャ内で使用できる変数

モジュールの宣言セクションでPrivate宣言された変数はモジュール内の全てのプロシージャで使用できます。
サンプルで確認しましょう。
img4

sample1プロシージャ、sample2プロシージャでmessage変数が使用できています。
img5

img6

③ 全てのモジュール内で使用できる変数

モジュールの宣言セクションでPublic宣言された変数は全てのモジュール内で使用できます。
サンプルで確認しましょう。
img7

sample1プロシージャ、sample2プロシージャ、sample3プロシージャで変数が使用できています。
img8

img9

img10

変数の適用範囲と宣言する場所をまとめた表になります。

変数宣言 宣言する場所 適用範囲
Dim 変数名 As 変数の型 プロシージャ内 プロシージャ内
Private 変数名 As 変数の型 宣言セクション モジュール内の全てのプロシージャ
Public 変数名 As 変数の型 宣言セクション 全てのモジュール内

今回ご紹介した内容はExcel VBA Standard試験対策の範囲となります。


カテゴリー: Excel VBA プログラム

ADOを使用してAccessデータをExcelに取り込む【中級者向け】

kave-638249_640

今回はADOを使用してAccessのデータをExcelに取り込む方法をご紹介します。
環境は、Access2010とExcel2010。
取り込むAccessのファイル名は「顧客管理.accdb」、保存先はCドライブの直下のAccessフォルダの中です(C:\Access\顧客管理.accdb)。

動作イメージは以下のとおりです。
Accessの「T_顧客リスト」テーブルのデータをExcelに取り込み、「顧客リスト」シートに表示させます。

Accessの「T_顧客リスト」テーブル
image1

Excelブックの「顧客リスト」シートimge2-3

ADO(ActiveX Data Object)とは

Accessだけでなく、Oracle、MySQLなどの様々なデータベースに対して共通の手法で操作が出来る仕組みです。ExcelからADOを使用するためには、参照設定が必要です。
事前準備としてVBEの「ツール」メニュー → 「参照設定」をクリックし、「参照設定」ダイアログボックスを表示し、「Microsoft ActiveX Data Objects X.X Library」にチェックを入れます(X.Xはバージョン)。

サンプルコード

Public Sub Access接続()
    Dim con As New ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim i As Integer
    
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=C:\Access\顧客管理.accdb;"
            
    Set rs = con.Execute("SELECT * FROM T_顧客リスト WHERE 性別 = '女'")
    
    i = 2
    
    Do Until rs.EOF
        With Worksheets("顧客リスト")
            .Cells(i, 1).Value = rs!顧客ID
            .Cells(i, 2).Value = rs!顧客名
            .Cells(i, 3).Value = rs!ふりがな
            .Cells(i, 4).Value = rs!性別
            .Cells(i, 5).Value = rs!生年月日
        End With
        
        i = i + 1
        rs.MoveNext
    Loop
    
    rs.Close: con.Close
    Set rs = Nothing: Set con = Nothing
End Sub

コード解説

1. Accessデータベースに接続します。

Accessデータベースへ接続するには、ConnectionオブジェクトのOpenメソッドを使用します。接続文字列内の「Data Source」には接続するAccessファイルのパスを記述します。

con.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=C:\Access\顧客管理.accdb;"

2. ConnectionオブジェクトのExecuteメソッドを使用して、レコードセットを取得します。

今回は「顧客管理.accdb」の「T_顧客リスト」テーブルに対して、「性別」列の値が「女」の行を取得する問合せを発行しています。

Set rs = con.Execute("SELECT * FROM T_顧客リスト WHERE 性別 = '女'")

3. 問合せの結果をExcelの「顧客リスト」シートに出力します。

今回は2行目から出力します。

i = 2
    
    Do Until rs.EOF
        With Worksheets("顧客リスト")
            .Cells(i, 1).Value = rs!顧客ID
            .Cells(i, 2).Value = rs!顧客名
            .Cells(i, 3).Value = rs!ふりがな
            .Cells(i, 4).Value = rs!性別
            .Cells(i, 5).Value = rs!生年月日
        End With
        
        i = i + 1
        rs.MoveNext
    Loop

4. データベースを切断します。

Closeメソッドを使用し、レコードセットとConnectionオブジェクトを閉じます。
また、それぞれの変数に「Nothing」キーワードを代入し、オブジェクトへの参照を解除します。

rs.Close: con.Close
Set rs = Nothing: Set con = Nothing

以上、今回はADOを使用してAccessのデータをExcelに取り込む方法でした。


カテゴリー: Access VBA プログラム