アーカイブ

‘Excel’ カテゴリーのアーカイブ

罫線の下端で改ページするマクロ

2011 年 12 月 9 日 コメントはありません

Excelで罫線を使って表を作る場合は多いです。
一つのシートに複数の表を縦に作っていく場合、表の途中で改ページされると困ることがあります。
そこであらかじめ、それぞれの表の下端で改ページしておく、という方法が考えられます。
これを自動的に実行するマクロを作りました。 続きを読む…

カテゴリー: Excel タグ: , ,

単語の先頭だけを大文字にする

2011 年 11 月 17 日 コメントはありません

ExcelにはPROPERというワークシート関数があり、英文の各単語の先頭を大文字に、その他の文字を小文字に変換することができます。
これをJavaScriptで行うための関数を考えてみました。

function proper(str){
    return str.replace(/\b([a-z])([a-z']*)\b/gi,function($0,$1,$2){
        return $1.toUpperCase() + $2.toLowerCase();
    });
}

単語を取り出すために正規表現として単語の切れ目を表す「\b」を使うのがポイントでしょうか。
シングルクォーテーションは「I’m」などに対応するためです。

カテゴリー: Excel タグ: , ,

ボジョレーヌーボーの日付をExcelで求める

2011 年 11 月 17 日 コメントはありません

ボジョレーヌーボーは11月の第3木曜日です。
今年はまさに今日です。

毎年のヌーボーの日付をExcelのVBAで求める方法を考えてみます。 続きを読む…

カテゴリー: Excel タグ:

VBAで引数の数を可変にする

2011 年 11 月 12 日 コメントはありません

VBAのユーザー定義関数で引数の数が一定でない場合があります。

Function kansu(a, b, c)

このケースでは、a、b、cが引数です。引数の数は3個です。

Function kansu(a, b, c, d)

このケースでは、a、b、c、dが引数です。引数の数は4個です。
このように、場合によって引数の数が違う場合のユーザー定義関数の作り方を示します。 続きを読む…

カテゴリー: Excel タグ:

Excelで偏差値を求める

2011 年 10 月 27 日 コメントはありません

Excelで偏差値を求める方法を考えます。
生徒にテストを受けさせて、その得点から偏差値を求めるものとします。

(1) 全員の得点の平均を計算する。【平均】※ワークシート関数としてはAVERAGE
(2) 一人ずつ得点と平均との差の2乗を計算する。
(3) (2)の平均を計算する。【分散】※ワークシート関数としてはVARP
(4) (3)の平方根を計算する。【標準偏差】※ワークシート関数としてはSTDEVP
(5) 一人ずつ 10*(得点-平均)/標準偏差+50 を計算する。【偏差値】

もしExcelで偏差値をダイレクトに計算するためには次のような数式を使います。
A1セルからA10セルに10人分の得点がセットされている場合です。

=10*(A1-AVERAGE(A1:A10))/STDEVP(A1:A10)+50

なお「10*(得点-平均)/標準偏差+50」の「10」と「50」は固定です。

実際に計算例を見てみます。 続きを読む…

カテゴリー: Excel タグ: ,

Excelで文字列リストを元に連続して置換するマクロ

2011 年 9 月 22 日 コメントはありません

以前、Wordで文字列リストを元に連続して置換するマクロについて書いたのですが、今度はExcel版です。

まず「文字列リスト」というシートと「作業」というシートを作成します。

文字列リスト

A B
1 検索文字列 置換文字列
2 エディター エディタ
3 プリンター プリンタ
4 サーバー サーバ

文字列リストにはこのような置換用の表をセットしておきます。

マクロ

Sub 文字列リストに基づき連続して置換する()
    i = 2
    Do
        x1 = Sheets("文字列リスト").Cells(i, 1)
        x2 = Sheets("文字列リスト").Cells(i, 2)
        Sheets("作業").Cells.Replace _
            What:=x1, Replacement:=x2, _
            SearchOrder:=xlByColumns, MatchCase:=True
        i = i + 1
    Loop Until Sheets("文字列リスト").Cells(i, 1) = ""
End Sub

マクロ(VBA)をこのように書きます。

使い方

「文字列リスト」シートに上のような置換用の表をセットします。
「作業」シートに置換を施したい元の内容をセットしておきます。
マクロ「文字列リストに基づき連続して置換する」を実行します。
置換すると元には戻せませんので注意してください。

カテゴリー: Excel タグ: ,

Excelで取消線の付いた文字を削除するマクロ

2011 年 8 月 31 日 コメントはありません

Wordには変更履歴を記録する機能があります。
文字を削除すると自動的に取消線を施すように設定できます。
最終版ができた際は自動的に取消線を消して該当する文字を削除することができます。
Word2007ならばメニューの校閲-変更箇所-承諾-ドキュメント内のすべての変更を反映を選択します。

Excelの場合はどうでしょうか。
Excelでも取消線を施すことができます。セル全体だけでなく文字単位で取消線をセットできます。
しかし変更履歴を記録しているわけではないので、単に文字に取消線が付いているだけの飾りです。

そこで選択範囲の取消線の付いた文字を一気に削除するマクロを考えました。

Sub 選択範囲の取消線の付いた文字を削除()
    For Each myCell In Selection
        textBefore = myCell.Value
        textArter = ""
        For i = 1 To Len(textBefore)
            If myCell.Characters(Start:=i, Length:=1).Font.Strikethrough = False Then
                textArter = textArter & Mid(textBefore, i, 1)
            End If
        Next i
        myCell.Value = textArter
    Next myCell
End Sub

該当する文字を含むセルを選択し、このマクロを実行します。
セル全体に取消線が付いている場合は全体が削除されますし、文字単位で取消線が付いている場合はその文字だけが削除されます。
ただし部分的に色を付けたりサイズを変えたりしている場合は、その効果が消えてしまいますので、注意してください。

カテゴリー: Excel タグ: ,

セルを楕円で囲むマクロ

2011 年 8 月 11 日 コメントはありません

選択したセル範囲を囲むように楕円を描くマクロです。
セルを選択して右クリックをするとセル範囲の内側に合わせて楕円が描かれます。

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    With ActiveSheet.Shapes.AddShape(msoShapeOval, _
        Target.Left, _
        Target.Top, _
        Target.Width, _
        Target.Height)
        .Fill.Visible = msoFalse
        .Line.Weight = 0.75
    End With
End Sub

楕円をもう少し大きくしたければ、次のようにします。5ピクセルずつ上下左右に大きくなります。
「Target」はここではセル範囲を表します。順に左端、上端、幅、高さを表します。

Target.Left - 5, _
Target.Top - 5, _
Target.Width + 10, _
Target.Height + 10

色を指定したい場合は、次のようにします。赤い線になります。

.Line.ForeColor.RGB = RGB(255, 0, 0)
カテゴリー: Excel タグ: , ,

全ワークシートのアクティブセルをホームに移動するマクロ

2011 年 8 月 9 日 コメントはありません

Excelファイルを他人に渡すときなどに、アクティブセル(カーソル)がホーム(A1セル)にあると整った印象を与えます。
しかしシートがたくさんあると面倒です。
そこで考えたのが、編集中のExcelファイルのすべてのワークシートのアクティブセルをホームに移動するマクロです。

Sub 全ワークシートのアクティブセルをホームに移動()
    Dim myWS As Worksheet
    For Each myWS In Worksheets
        myWS.Select
        Range("A1").Select
    Next
End Sub
カテゴリー: Excel タグ: ,

指数表示で指数を「06」に固定する表示形式

2011 年 7 月 7 日 コメントはありません

Excelには指数表示という表示形式があります。
例えば「7000000」を「7.0E+06」と表示します。
7000000=7*10^6
という意味です。
桁を一つ増やし「70000000」とすると「7.0E+07」となります。
この「E+」の後に付く数を「指数」と呼びますが、桁が変わっても指数部分を変えずに表示する表示形式を考えます。 続きを読む…

カテゴリー: Excel タグ: ,