エクセル

Excelのオートフィルタ(変数で範囲の値を受けとる。)

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

「数値フィルタ」の「指定の範囲内」を実行した場合と同じ結果になる。


Sub オートフィルタ_変数で範囲の値を受けとる()
    Dim フィルターを実行する列 As Variant '変数の型を Variant にしておけば、半角数字でも全角数字でも入れられる。
    Dim 開始 As Variant
    Dim 終了 As Variant
    Dim 確認 As Variant
'
'---■何列目でフィルタをかけるかを指定し、「数値フィルター」の「指定の範囲内」について、範囲の最初の値と最後の値を変数に入れる ---ここから
        フィルターを実行する列 = InputBox(Prompt:="フィルターを実行する列を指定してください。1列目なら 1 を、 2列目なら 2 を入力します。", Title:="フィルターを実行する列")
            If フィルターを実行する列 = "" Then Exit Sub
        開始 = InputBox(Prompt:="指定の範囲の最初の値を入れてください。", Title:="開始")
            If 開始 = "" Then Exit Sub
        終了 = InputBox(Prompt:="どの値まで含めるかを入れてください。", Title:="終了")
            If 終了 = "" Then Exit Sub
'---ここまで---
'
'
'---■メッセージボックスを表示させる---ここから
        MsgBox "フィルターを実行する列:" & フィルターを実行する列 & vbCrLf & "開始する値:" & 開始 & vbCrLf & "終了する値:" & 終了 & vbCrLf & "範囲の値は正しいですか?"
        確認 = MsgBox("処理を続行しますか?", vbYesNo + vbInformation)
'---ここまで---
'
'
'---■変数 確認 が vbYes ならオートフィルタを実行する。 vbYes 以外ならプロシージャーを抜ける。---ここから
        If 確認 = vbYes Then
            Selection.AutoFilter
            ActiveSheet.Range("A1").AutoFilter Field:=フィルターを実行する列, Criteria1:=">=" & Format(開始, "##"), _
            Operator:=xlAnd, Criteria2:="<=" & Format(終了, "##") 'ActiveSheet.Range("A1").AutoFilter のコードは範囲の一番左上のセルが A1 の場合を意味する。
        Else
            Exit Sub
        End If
'---ここまで---
End Sub

| | コメント (0) | トラックバック (0)

Excel でワークシートに任意のヘッダーを設定する。

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

K.M さん。

ワークシートに任意のヘッダーを設定するための VBA のコードです。
私にできる範囲でつくってみました。

作業の流れは次のようです。

  1. シート「ワークシート名取得」を一番右端に作っておく。(シート名は変えない。)
  2. マクロの「ワークシート名取得」を実行。
  3. シート「ワークシート名取得」のA列にワークシート名が入るので、各シートのヘッダーに入れたい文字列などをB列に入力。
  4. 同じくマクロから「ヘッダーへの文字列の挿入」を選んで実行。
  5. B列に入れた内容が各ワークシートのヘッダーとして入る。

※入れたヘッダーを消したければ、B列に入れた値を削除してセルを空欄にしたら手順4をもう一度実行。

まず最初に、ヘッダーを入れたいブックを拡張子.xlsm で保存してから、シート「ワークシート名取得」を一番右端につくっておいてください。(シート名は変えないで。)

シートをつくったら、ワークシート名を取得するコードを実行してください。

ワークシートごとに任意のヘッダーを指定するということでしたので、ワークシート名を取得することから始めてみました。
そうやって、ブックのなかにいまあるワークシート名をすべて取得したら、ワークシートごとにヘッダーに入れたい値を手入力してください。

それから、いま手入力した値をヘッダーへ挿入するコードを実行するという流れにしてみました。

「開発」タブの Visual Basic をクリックして、VBE (Visual Basic Edtor) を起動したら、標準モジュールを挿入して、以下のコードをコピー貼り付けしてください。(Sub から End Sub まで。)


■ワークシート名を取得するコード

Sub ワークシート名取得()
    Dim i As Long
    Dim Sheet_Name As Variant

        Worksheets("ワークシート名取得").Select
    For i = 1 To Worksheets.Count - 1
        Sheet_Name = Worksheets(i).Name
        Cells(i + 1, "A").Value = Sheet_Name
    Next i
        MsgBox ("ワークシート名の取得ができました。")
'
'
'このプロシージャーを使う前には、シート「ワークシート名取得」を一番右端に作っておく。シート名は変えない。
'
'ブックのなかの左端のワークシートを 1 として右端のワークシートまでの枚数を
'Worksheets.Count で数えている。
'-1 しているのは、シート「ワークシート名取得」の一つ手前までを指定するため。
'
'変数 Sheet_Name にワークシート名を代入する。
'さらに、その変数 Sheet_Name をA列のセルに入れていく。
'変数 Sheet_Name の型を Variant にしているのは、ワークシート名が文字列でも数字だけでも何であっても変数に格納できるように。
'
'ワークシートの1行目に列見出しが入る場合を想定して、
'A列で、一つずつ下にセルをずらしてワークシート名を入れている。
'Cells(i + 1, "A").Value
'のコードで、変数 i に 1 を足している。
'Cells(行,列)なので、変数 i が 1 のときは、Cells(2, "A")となってセルA2を、
'変数 i が 2 のときはさらに一つ下の A3 を、変数 Sheet_Name の代入先に指定したことになる。
'
'ワークシート名の取得が終わったら、その旨のメッセージを表示する。
'
End Sub


■ヘッダーへ文字列を挿入するコード
先に「ワークシート名取得」のコードを実行しておく。次に、ヘッダーに入れたい文字列をシート「ワークシート名取得」のB列に入力しておく。

Sub ヘッダーへの文字列の挿入()
    Dim i As Long, j As Long
    Dim Flag As Long

    Worksheets("ワークシート名取得").Select

    For i = 1 To Worksheets.Count - 1
        For j = 2 To Cells(Rows.Count, "A").End(xlUp).Row
            If Worksheets(i).Name = Cells(j, "A").Value Then
                Flag = 1
                Worksheets(i).PageSetup.LeftHeader = Cells(j, "B").Value
                Cells(j, "C") = "ヘッダー設定済み"
            End If
        Next j
            If Flag = 0 Then
                MsgBox ("ワークシート名がありません")
            End If
    Next i
        Worksheets("ワークシート名取得").Select
        MsgBox ("ヘッダーの設定が終わりました。" & vbCrLf & "C列のセルが空白のものはA列のワークシート名が間違っています。 ")
'
'
'シート「ワークシート名取得」を選択。
'
'左端のワークシートを 1 として右端のワークシートまでの枚数を
'Worksheets.Count で数えている。
'それを変数iに代入。
'-1しているのは、シート「ワークシート名取得」の一つ手前までを指定するため。
'(シート「ワークシート名取得」が一番右端にある場合)
'
'ワークシート名とA列に入った文字列が同じの場合、変数 Flag に 1 を代入する。
'ワークシート名を取得した後でワークシート名をうっかり変更すると、
'A列の値とワークシート名が異なっていることになる。
'そういう場合を想定して、A列の値とワークシート名を比較して異なっていないかを確認している。
'ワークシートのヘッダーの左側 ( LeftHeader ) にB列の値を入れていく。
'Cells(j, "C") = "ヘッダー設定済み"
'の式で、C列に「ヘッダー設定済み」という文字列を入れて、
'ヘッダーの記入が済んだことがわかるようにしている。
'
'変数 Flag が 0 の場合、メッセージボックスを表示する。
'変数 Flag が 0 であるということは、A列の値と実際のワークシート名が合っていない。
' "ワークシート名がありません" のメッセージが表示されたら、A列の値とワークシート名を確認する。
'
'シート「ワークシート名取得」を選択。
'メッセージボックスを表示する。
'
'
End Sub

| | コメント (0) | トラックバック (0)

Excel で、一行おきの連番をつくる。

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

K.M さん。

Excel で1行おきの連番をつくるやり方です。難しく考える必要はありません。

まずは、普通に連番をつくるときのように 1 と 2 を入力します。

このとき、空けたい行数離して入力します。たとえば、一行おきにしたければ、1 を入れたセルから間に1行おいて 2 を入力してください。
A1 に 1 を入れて、A3 に 2 を入れるというように。

で、ポイントは 1 と 2 を選択するときの選び方です。

空けたい行数と同じ数の行を 2 のあとに選びます。
A1 に 1 を入れて、A3 に 2を入れるのは、一行おきにしたいからです。なので、A1 から A4 までを選択します。

その状態でドラッグすると一行おきの連番になります。

二行おきにしたければ、 A1 に 1 、 A4 に 2 を入れて、 A1 から A6 までを選択してドラッグします。

1gyoutobashi

2gyoutobashi

3gyoutobashi

| | コメント (0) | トラックバック (0)

Excel で同じ数字が列内に並ぶ連番を作る。

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

1 1 2 2 3 3 というように同じ数字が列内に並ぶ連番を作る。

■同じ数字が2個ずつ列内に並ぶ連番の作り方
A. 一番目になるセルに 1 を入れる。

B. 1 を入れた一番目のセルを参照する式を、二番目のセルに入れる。
3個以上並ぶ連番を作る場合は、B. を必要な回数繰り返す。

C. 三番目のセルに、一番目のセルに 1 を足す式を入れる。

D. C.のセルをドラッグ。


例: 1 1 2 2 3 3 と2個ずつ列内に並ぶ連番
セル A1 に 1 を入力。
セル A2 に =A1 を入力。
セル A3 に =A1+1 を入力。
セル A3 を選択して下へドラッグ。

例: 1 1 1 2 2 2 3 3 3 と3個ずつ列内に並ぶ連番
セル A1 に 1 を入力。
セル A2 に =A1 を入力。
セル A3 に =A1 を入力。
セル A4 に=A1+1 を入力。
セル A4 を選択して下へドラッグ。


■VBAで作る場合の例(2017/7/4追記)

Sub 連番sample1()
    Dim i As Long, j As Long, R As Long
    For i = 1 To 25
        For j = 1 To 2
            R = R + 1
            Cells(R, 1).Value = i
        Next j
    Next i
'
'
'For i = 1 To 25
'
'Next i
'1 から 25 までをセルの値として入れるための指定。
'最大の値を変える場合は、25 変える。たとえば、10 にすると、
'1 から 10 までがセルの値として入る。
'
'
'For j = 1 To 2
'
'Next j
'いくつずつ数字を入れるかを指定している。この場合、
'1
'1
'2
'2
'というように2つずつ入っていく。3つずつ入れる場合は、For j = 1 to 3 にする。
'
'
'■外側の繰り返しの1回目
'変数 i に 1 が入る。
    '■内側の繰り返しの1回目
        '変数 j に 1 が入る。
        '変数 R は発生した直後は 0 なので、
        'R = R + 1 で変数 R に 1 が入る。
        'Cells(R, 1).Value = i は、
        'Cells(1, 1).Value = 1 となって、
        'セルA1の値に 1 が入る。
        'もし、セルA2から値を入れるなら、
        'Cells(R, 1).Value = i の部分を、
        'Cells(R + 1, 1).Value = i にする。
'
    '■内側の繰り返しの2回目
        '変数 j に 2 が入る。
        '変数 R にはすでに 1 が入っているので、この回では、
        'R = R + 1 は、 R = 1 + 1 になって、変数 R には 2 が入る。
        'Cells(R, 1).Value = i は、
        'Cells(2, 1).Value = 1 となって、
        'セルA2の値に 1 が入る。
'
'
'■外側の繰り返しの2回目
'変数 i に 2 が入る。
    '■内側の繰り返しの1回目
        '変数 j に 1 が入る。
        'さきのときに変数 R には、2 が入ったままなので、ここでは
        'R = R + 1 は、 R = 2 + 1 になって、変数 R に 3 が入る。
        'Cells(R, 1).Value = i は、
        'Cells(3, 1).Value = 2 となって、
        'セルA3の値に 2 が入る。
'
    '■内側の繰り返しの2回目
        '変数 j に 2 が入る。
        '変数 R にすでに 3 が入っているので、この回では、
        'R = R + 1 は、R = 3 + 1 になって、変数 R に 4 が入る。
        'Cells(R, 1).Value = i は、
        'Cells(4, 1).Value = 2 となって、
        'セルA4の値に 2 が入る。
'
'
End Sub

| | コメント (0) | トラックバック (0)

Excel で未使用の「セルのスタイル」を一括して消す。

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

使っていない「セルのスタイル」が大量にある場合、

Misiyoucellstyle

それらを一括して消すには次のようにする。

  1. すべてのシートを選択。
  2. ワークシートのタブの上で右クリックして、「移動またはコピー」を選択。
  3. 移動先ブック名に「新しいブック」を選択し、「コピーを作成する」をチェックを入れてOKをクリック

これで「新しいブック」には、使用中の「セルのスタイル」だけが引き継がれるので、名前を付けてブックを保存する。

結果として未使用のセルのスタイルを全部削除することができている。

| | コメント (0) | トラックバック (0)

Excel で得点幅ごとの人数を数える。

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

M.K さん
エクセルで、得点の幅ごとに何人いるかを数えるやり方です。
「30点以上40点以下は何名で、41点以上50点以下は何名いて……」とか――いわゆる度数分布というものですね。

COUNTIFS関数を使えばいいかと思います。
(COUNTIFS関数が使えるのはエクセル2007以上)

オートフィルタを使ってもできますが、その場合は「指定の範囲内」を必要な回数だけ実行することになります。ちょっと面倒ですよね。

Shiteinohaninai_2

なもので、COUNTIFS関数を利用することにしました。

なお、Googleなんかで「エクセル 点数 人数」とか探すと、このような場合はFREQUENCY関数を使う説明も見つかります。
FREQUENCY関数でもかまいません。最終的に得られる結果は同じですから。

なぜ、FREQUENCY関数を今回使わないでCOUNTIFS関数を使ったかというと、「FREQUENCY関数のほうが理解しにくく、少し変えたいといった場合に面倒になるだろう」と考えたからです。

詳細を知りたければFREQUENCY関数を検索してみてください――。使い方をすんなり理解できますか。なんだかややこしいと感じませんか。

そんなややこしいと感じるものを仕事では使わないほうがいいと僕は思います。仕事で作るファイルは作った人のものではなく、皆のものです。
たとえば、「エクセルってほとんど初めてです」とかいう人に仕事を引き継ぐことだってあるかもしれません。
たとえ、 M.K さんがFREQUENCY関数を自在に使えるとしても、引き継いだ後の人が困ります。

でも、COUNTIFS関数なら、同じ初めてでも、FREQUENCY関数よりは理解しやすいと思います。
だとしたら、計算結果は同じなのだから、より理解しやすいであろうCOUNTIFS関数を使っておくのが、仕事としては正しいのではないかと思ったしだい。

仕事でのファイル作成では、「自分以外の人がファイルを使う場合もある」ことを考慮してください。
見やすい式を作ったり、理解の助けになるコメントを残したり、やり方はいろいろあるかと思います。

FREQUENCY関数を使ってはいけないといっているのではありません。
もし M.K さんも含めて周囲にいる人たちが「FREQUENCY関数を使っても何も困らない」という人ばかりなら、使うことに何の問題もないと思います。

■例:COUNTIFS関数を使って、得点ごとの人数を数える。

COUNTIFS関数の書式は次のようです。詳細はエクセルのヘルプでCOUNTIFS関数を調べてください。
・COUNTIFS(条件範囲 1, 検索条件 1, [条件範囲 2, 検索条件 2]…)
出典: Excel ヘルプ

「条件範囲」には、人数を数えたい点数の一群を指定します。「検索条件」には >=41 (41以上)などの条件を指定します。

こういう表があるとします。

Countifs0_2
※氏名は「なんちゃって個人情報」のサイトを使って生成

C、D、E列に入っている得点を対象として人数を数えます。
(C、D、E列がそれぞれ「条件範囲」になります。)

得点の幅は、下図のようにG列に入れました。
(実際には計算のなかではG列の値は使っていません。だからなくてもいいのですが、あったほうが見やすいという気がしたので入れました。)

適当なセルに「以上」と「以下」を示す式を入力します。
下図では、H列に「以上」、I列に「以下」を示す式を入れています。
(H列とI列の値が「検索条件」になります。)

「45以下」については、「Aの人数」「Bの人数」「Cの人数」ともにCOUNTIFS関数の検索条件としてI2セルを指定します。
J2セルに、
=COUNTIFS(C$2:C$21,$I$2)
という式を入れて、L2セルまでコピーします。

Countifs1

C2からC21の範囲を対象にして、I2に入れた条件に合致する値の個数を数えます。
I2の条件は  <=45 なので、C2からC21の範囲で「45以下」の値を数えます。

J3セルには、
=COUNTIFS(C$2:C$21,$H3,C$2:C$21,$I3)
という式を入れます。

H3には >=46 が、I3には <=50 が入っているので、C2からC21の範囲で「46以上」かつ「50以下」の値を数えます。

それをJ12セルまでコピーします。
そのあとJ3からJ12を選択して、L列までコピーします。

Countifs2

なお、ここで入力した式について、
=COUNTIFS(C$2:C$21,"<=45")

=COUNTIFS(C$2:C$21,">=46",C$2:C$21,"<=50")
のように、COUNTIFS関数の条件として、点数の幅の下限や上限を示す文字列を入れても計算結果は同じです。
同じですが、このようにしないほうがよいと思います。

なぜかというと、やはり先にふれたように自分以外の人が見ても理解しやすいというのもありますが、それよりも「ちょっと変えたい」となったときにより簡単に修正できるようにしておくためです。

「以下」と「以上」の条件を変える必要が出てきたとき、図のように条件が別のセルに書かれているのと、関数の式のなかに条件が書かれているのとでは、どちらが修正しやすいですか。

たぶん、前者だと思います。
(後者のほうが修正しやすいのでしたら、そうしてください。そういう人はいないと思いますけど……)

こういうセルの使い方を作業列とか作業セルといいます。

FREQUENCY関数を使う説明したサイトでは、一つのセルにFREQUENCY関数で答えを求めれば、見た目がすっきりする――といった説明をしているサイトもあります。
それはそれで間違っていません。

ただ、一つのセルだけで計算の答えを得ようとすると、どうしても数式が長くなって見づらくなり、何をしているのか理解もしづらくなりがちです。理解しづらいということは、ちょっと変えたいときに面倒でもあるということです。

作業列を使って計算の過程を分けておけば、誰もが理解しやすくできますし、「ちょっと変えたい」にも対応しやすくなります。
上図でいえば、下限を「30以下」から初めて「15」ずつ区切っていく――に変えたい場合、H列とI列の式を書き換えるだけで済みます。

    <=30
>=31 <=45
>=46 <=60

というように。

数式のなかに数式を入れる――いわゆる「ネスト」する場合も、各数式を別の別のセルに入れておいて計算結果を参照すると、視認性も高まり、修正も容易になります。
(作業列の利用は、僕も先達から教えてもらった知恵です。作業列を使ったせいで困ったことは一度もありませんが、逆はあります。)

「エクセルのシートで作業列を使うと、列がどんどん増えていってシートが見づらくなるから作業列は使わない」とかいって、作業列を使うことを嫌う人もいますが、僕に言わせればそれはちょっとした勘違いです。

見づらければ、列を非表示にすればいいんです。

とはいえ、ここでも「そうしなければならない」という話ではありません。
職場では「作業列を使わないほうが仕事が円滑に進む」、「作業列を使わなくても数式の理解や修正は誰もが簡単にできる」というのであれば、使う必要はないと思います。

| | コメント (0) | トラックバック (0)

Excel でワークシート名を一覧にする VBA のコード

この記事は自分用の覚書です。

K.N さん
エクセルで、ブックのなかのワークシート名を一覧にするマクロのコードの件です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

ネットで見つけたものが役に立ってよかったですね。
まずはよしとして、「よくわからないけど、やりたいことはできた」ままでは応用ができません。

少しでも役立てられるようにコメントをつけてみました。わかりづらいところがあったら、今度会った時に聞いてください。
もっとも、私にわかる範囲でしか話せませんが。悲しいかな私も「VBAなんて、おちゃのこさいさい」というわけではないので。


Sub ワークシート名を一覧にする()
    Dim i As Long
    Dim Sheet_Name As Variant

    Worksheets("ワークシート名の一覧").Select
    For i = 1 To Worksheets.Count - 1
        Sheet_Name = Worksheets(i).Name
        Cells(i, "A").Offset(1, 0).Value = Sheet_Name
    Next i
'
'あらかじめシート「ワークシート名の一覧」を一番右端に作っておく。
'
'変数 Sheet_Name は Variant (バリアント)型で宣言。
'バリアント型にしておけば、ワークシート名が文字でも数字だけでもどんな値であっても格納できる。
'なお、変数の型をバリアント型にしたい場合は、型の指定を省略できる。
’    Dim Sheet_Name
'と書くと、宣言した変数をバリアント型で指定したことになる。
'
'左端のワークシートを 1 で表す。
'変数 i に 1 が代入されると、
'Sheet_Name = Worksheets(i).Name
'は、
'Sheet_Name = Worksheets(1).Name
'となって、これは一番左端のワークシートの名前を、変数 Sheet_Name に代入するということ。
'右端のワークシートまでの枚数を Worksheets.Count で数えている。
'-1 しているのは、ワークシート名の一覧を載せるシートの一つ手前までを指定するため。
'
'変数 Sheet_Name をA列のセルに代入していく。
'OffsetプロパティでA列のセルを一行ずつ下にずらしていく指定をしている。
'Offsetプロパティは、Offset(行, 列)、Cellsプロパティも Cells(行,列)という書式なので、
'
'Cells(i, "A").Offset(1, 0).Value
'
'のコードで、変数 i が 1 のときは、
'
'Cells(1, "A").Offset(1, 0).Value = Sheet_Name
'
'ということになって、A1セルの一行下のA2セルを、変数 Sheet_Name の代入先に指定したことになる。
'変数 Sheet_Name の代入先が、A2セルからなのは1行目に列見出しを入れるため。
'
End Sub


難しいのは、 Cells(i, "A").Offset(1, 0).Value のところでしょうか。
VBA を学びだした際に Offsetプロパティの動きをイメージするのは難しいかと思います。私もまだイメージしづらいです。

基準となるセルやセル範囲を、指定した行数や列数あるいは両方ともずらす・移動する――というものなんですが。

Offset(行,列) ということになっています。 Offset(1, 0) なら「(列はそのままで)1行下にずらす」ということです。
なので、変数 i が 1 の場合は、

Cells(1, "A").Offset(1, 0).Value

となって、 Cellsプロパティも Cells(行,列)なので、これは――A1のセルから1行下のセル―― A2 のセルの値を指していることになります。
なお、 Cells(i, "A") は、 Cells(i, 1) と書いてもどちらも同じ意味です。

Offsetプロパティって文字で書くとよけいにイメージしづらいですよね。

なので、ちょっと考え方を変えてみます。

Cells(i, "A") の変数 i が 1 のときは 、OffsetプロパティによってA2セルを、 i が2 のときはA3セルを、3 のときはA4セルを意味します。

Cells(1, "A").Offset(1, 0).Value なら、セルA2の値。
Cells(2, "A").Offset(1, 0).Value なら、セルA3の値。
Cells(3, "A").Offset(1, 0).Value なら、セルA4の値。

これって変数 i が「一つ増え」て1行下を示しているのと同じことですよね。
ならば、この場合は変数 i に 1 を足してやれば、Offsetプロパティを使わなくてもよくなるといえます。

Cells(i + 1, "A").Value = Sheet_Name

というように。

こちらのほうが「1行ずらしていく」イメージがしやすくないですか?
もちろん、「Offsetプロパティのほうがイメージがしやすい」ということでしたら、ぜんぜんそれで構わないと思います。

ようは、わかりやすいほうがいいよねという話ですから。
先のコードを変えてみました。


Sub ワークシート名を一覧にする()
    Dim NewWorkSheet As Worksheet
    Dim i As Long
    Dim Sheet_Name As Variant
   
    Set NewWorkSheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    NewWorkSheet.Name = "ワークシート名の一覧"
        For i = 1 To Worksheets.Count - 1
            Sheet_Name = Worksheets(i).Name
            Cells(i + 1, "A").Value = Sheet_Name
        Next i
'
'変数 NewWorkSheet を ワークシート型で宣言。
'Addメソッドは、ワークシートを正常に挿入したあとWorksheetオブジェクトを返す。
'挿入したシートの名前を文字列で返すのではなく、挿入したシート自体を返す。
'したがって、返り値を受け取る変数はワークシート型かバリアント型を指定しておかなければならない。
'
'Set NewWorkSheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
'のコードで、今あるワークシートの一番右(後ろ)に新しくワークシートを追加し、
'変数 NewWorkSheet でそれを受け取る。
'
'Addメソッドのオプションを () で囲っているのは、VBAのルール。
'このコードのように返り値を変数に代入するなど、返り値を利用する場合は、括弧を付けなければならない。
'たとえば、
'
'Sub Sample01()
'    Worksheets.Add after:=Worksheets(Worksheets.Count)
'    ActiveSheet.Name = "新しいワークシート"
'End Sub
'
'のコードでも、現在あるワークシートの最後尾にワークシートを追加できる。
'この場合は、Add メソッドの返り値を利用していないので、Add メソッドの後ろに あるオプションを () で囲むことは不要。
'
'また、
'Worksheets.Add(after:=Worksheets(Worksheets.Count))
'の部分を
'Worksheets.Add(Before:=Worksheets(1))
'のように書くと、
'現在あるワークシートの一番左(先頭)に新しいワークシートを追加できる。
'
'変数 Sheet_Name は Variant (バリアント)型で宣言。
'バリアント型にしておけば、ワークシート名が文字でも数字だけでもどんな値であっても格納できる。
'なお、変数の型をバリアント型にしたい場合は、型の指定を省略できる。
’    Dim Sheet_Name
'と書くと、宣言した変数をバリアント型で指定したことになる。
'
'左端のワークシートを 1 で表す。
'変数 i に 1 が代入されると、
'Sheet_Name = Worksheets(i).Name
'は、
'Sheet_Name = Worksheets(1).Name
'となって、これは一番左端のワークシートの名前を、変数 Sheet_Name に代入するということ。
'右端のワークシートまでの枚数を Worksheets.Count で数えている。
'-1 しているのは、ワークシート名の一覧を載せるシートの一つ手前までを指定するため。
'
'変数 Sheet_Name をA列のセルに入れていく。
'変数 i に 1 を足すことでA列のセルを一行ずつ下にずらしていく指定をしている。
'Cellsプロパティは Cells(行,列)という書式なので、
'
'Cells(i + 1, "A").Value
'
'のコードで、変数 i が 1 のときは、
'
'Cells(2, "A").Value = Sheet_Name
'
'ということになって、A2セルを変数 Sheet_Name の代入先に指定したことになる。
'変数 Sheet_Name の代入先が、A2セルからなのは1行目に列見出しを入れるため。
'
End Sub


せっかくなので、新しいワークシートを追加するようにしてみました。
これで、ワークシートを最初に右端に作る必要がありません。

また、今回の場合は

        Sheet_Name = Worksheets(i).Name
        Cells(i + 1, "A").Value = Sheet_Name

のところは、

        Cells(i + 1, "A").Value = Worksheets(i).Name

のように変数を使わないで1行で書いてもいいかなとも思います。
Worksheets(i).Name からなんとなく「ワークシート名を取得しているんだな」と VBA に不慣れな人でも想像がついて、何をしているかを理解しやすいと思うので。

変数を使ったのは私のクセです。
変数を使ったほうが何かと便利な場合のほうが多いので、日ごろから変数を使うクセをつけているというか、心がけているというかです。

でも、このコードの場合、1行あたりも短いし、全体的な行数も多くないので全体として何をやっているかを理解しやすいと思いますし、どこかで間違えてデバッグするのもそう大変ではないと思います。

そういう理由で今回は変数を使わないで1行に書くのもありかもと思ったしだい。
どちらがいい・悪いという話ではないです。自分や周りの人たちが見やすく、理解しやすいことが大事です。

それから変数を使う場合は、

    Dim i As Long
    Dim Sheet_Name As Variant

のように Dim を使って必ず変数を宣言してください。
「変数は宣言しなくてもいい」「宣言しなくてもちゃんとコードが実行できた」といった話を見たり聞いたりするかもしれませんが、「宣言しなくていい」を信じないでください。

変数を宣言をしたからといって害になることは何もありませんが、宣言していないと害を被ることがあります。だったら、宣言しておくのが賢い選択です。

ちなみに、変数の宣言は、

    Dim i As Long, Sheet_Name As Variant

というように , で区切って1行で書くこともできます。

なぜ変数は宣言しなければならないか、宣言しないとどんな目に遭うのか――。それについては、下記のサイトでとてもわかりやすく説明してくれています。
日本におけるExcelの第一人者とされている「田中亨」という方のサイトです。

変数って宣言しなくちゃいけないの?

| | コメント (0) | トラックバック (0)

Excel でB列に値が入っていたらA列に連番を入れる。

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

K.M さん。
B列のセルに値が入っていたらA列のセルに連番を振っていくVBAのコードの試作です。
下記のような感じでどうでしょう。

B列に空欄がある場合にそれを数えるか、数えないかでコードが少しだけ違います。
N = N + 1 の位置が違っています。

B列の空欄セルは飛ばしてA列に連番を入れるか、B列の空欄セルも数えてA列に連番を入れるかの違いです。
前者だと、常に 1 から始まり、 2 3 4 5 と続きます。
後者だと、B列が空欄のセルを数えても、A列には入力しないので、1 3 5 というようになります。(2 と 4 がB列の空欄セルの場合)

ちなみに、B列に空欄がない場合は、どちらを実行してもA列に入るのは 1 2 3 4 5 という連番です。
なお、1行目に列見出しをつけるのでしたら、
For i = 1 To 5
の部分を For i = 2 To 5 に変えてください。(1 を 2 に変える。)

Sub B列を基準に連番_空欄セルは飛ばす()
    Dim i As Long
    Dim N As Long
    For i = 1 To 5
        If Cells(i, "B").Value <> "" Then
            N = N + 1
            Cells(i, "A").Value = N
        End If
    Next i
'
'Cells(i, "B").Value <> "" は、「B列のセルの値が空欄に等しくない(空欄ではない)」という意味。
'
'B列のセルが空欄でない場合、 if と End if の間にあるコードを実行する。
'変数 N に 1 を足したものを N に代入。さらに N をA列のセルに代入する。
'
'宣言した直後は変数は 0 なので、 N + 1 は 0 + 1 ということで、 N = N + 1 は  N = 1 ということになり、
'変数 N に 1 が代入される。
'この場合の = は、左辺と右辺が等しいことを意味する等号ではない。右辺を左辺に代入することを意味する代入記号。
'
'Cells(i, "A").Value = N のコードで、1 が代入された変数 N がA列のセルの値に代入される。
'つまり、A列のセルの値に 1 が入るということ。
'
'二度目に「B列のセルの値が空欄でない」という条件が満たされた場合は、一度目に変数 N に 1 が代入されているので、
'N = N + 1 は N = 1 + 1 ということで、変数 N に 2 が代入される。
'
'B列のセルが空欄の場合は、if と End if の間にあるコードは実行されないので、
'結果的に、A列のセルに入る値は常に 1 からの連番になる。(B列の空欄セルは飛ばしてA列のセルに連番を入れることになる。)
'
End Sub

空欄セルは飛ばす

Sub B列を基準に連番_空欄セルも数える()
    Dim i As Long
    Dim N As Long
    For i = 1 To 5
        N = N + 1
        If Cells(i, "B").Value <> "" Then
            Cells(i, "A").Value = N
        End If
    Next i
'
'変数 N に N + 1 を代入する。
'宣言した直後は変数は 0 なので、 N + 1 は 0 + 1 ということで、 N = N + 1 は  N = 1 ということになり、
'変数 N に 1 が代入される。
'この場合の = は、左辺と右辺が等しいことを意味する等号ではない。右辺を左辺に代入することを意味する代入記号。
'
'Cells(i, "B").Value <> "" は、「B列のセルの値が空欄に等しくない(空欄ではない)」という意味。
'
'B列のセルが空欄でない場合、 if と End if の間にあるコードを実行する。
'変数 N をA列のセルに代入する。
'
'For Next の繰り返しのなかに N = N + 1 のコードがあるので、繰り返されるごとに変数 N が 1 ずつ増える。
'最初は 1 、次は 2 、次は 3 というように。
'
'その繰り返しのなかで、「B列のセルの値が空欄ではない」条件が満たされた場合、
'Cells(i, "A").Value = N のコードで、変数 N がA列のセルの値に代入される。
'繰り返しの最初なら 1 が、二度目なら 2、三度目なら 3 が変数 N に入っているので、その数がA列のセルの値に入ることになる。
'
'B列のセルが空欄の場合は、if と End if の間にあるコードは実行されないので、
'結果的に、A列のセルに入る値は、B列の空欄のセルも数えた数になる。
'(B列の空欄セルも飛ばさずに数えた連番をA列のセルに入れることになる。)
'
End Sub

空欄セルも数える

| | コメント (0) | トラックバック (0)

Excel で連番の抜けをチェックする。

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

重複のない連番について、途中で抜けている番号は何かをチェックする。

・B列のセルに入っている式
=VLOOKUP(ROW()-1,$A$2:$A$16,1,FALSE)

#N/A のエラーが表示されているセルが番号が抜けているところ。

Renbannukecheck

| | コメント (0) | トラックバック (0)

Excel の「アクティブ行との相違」、「アクティブ列との相違」

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

エクセルの「ジャンプ」機能の「セル選択」のなかにある「アクティブ行との相違」と「アクティブ列との相違」の動きの違い。

■アクティブ行との相違
アクティブセル領域のなかで、アクティブセルがある列の個々のセルに対して左右に向かって(つまり行のなかで)それぞれのセルの値と異なる値が入っているセルを見つけて選択する。

■アクティブ列との相違
アクティブセル領域のなかで、アクティブセルがある行の個々のセルに対して上下に向かって(つまり列のなかで)それぞれセルの値と異なる値が入っているセルを見つけて選択する。

■アクティブ行との相違
1.セル範囲を選択して、「ジャンプ」の機能を呼び出す。
「ジャンプ」はショートカット Ctrl + G で呼び出せる。
リボンからは、「ホーム」タブ-「編集」グループ-「検索と選択」のなかの「ジャンプ」

Gyousoui1s

※氏名は、「疑似個人情報データ生成サービス」を利用して生成。

2.「ジャンプ」のダイアログボックスが開いたら「セル選択」をクリック。

Jumpdialogbox

3.「アクティブ行との相違」をチェックして、OKをクリック。

Active_gyousouis

4.セルが選択される。

Gyousoui2s

この場合は最初に選択した状態のときは、セルA2の「浜崎穂乃香」がアクティブセルなので、A列のなかで次のような動きをする。

セルA2の左右に対して「浜崎穂乃香でないもの」を探す。
(つまり2行目のなかで「浜崎穂乃香でないもの」)

セルA3の左右に対して「小寺信彦」でないもの」を探す。
(3行目のなかで「小寺信彦でないもの」)

セルA4の左右に対して「小口美帆でないもの」を探す。
(4行目のなかで「小口美帆でないもの」)

セルA5の左右に対して「熊田好でないもの」を探す。
(5行目のなかで「熊田好でないもの」)

セルA6の左右に対して「石原千沙でないもの」を探す。
(6行目のなかで「石原千沙でないもの」)

イメージとしては次のよう。

Gyousoui3s

この場合は、最初にアクティブセルがあるA2の左にはセルがないので、セルの右方向だけで異なる値を探している。

A列にある各セルを基準にして、行ごとに相違を探しているので、A列のどのセルをアクティブセルにした場合も、最後に選択されるセルの結果はすべて同じになる。

最初に選択した状態から、アクティブセルを移動するには、Tabキーを押す。
Shift + Tab で逆方向へ戻る。

■アクティブ列との相違
1.セル範囲を選択して、「ジャンプ」の機能を呼び出す。
画像では、セル範囲 A2:C6 を選択してから、Tabキーでアクティブセルを A4 に移動している。

Retusoui1s

2.「ジャンプ」のダイアログボックスが開いたら「セル選択」をクリック。

3.「アクティブ列との相違」をチェックして、OKをクリック。

Activ_retusouis

4.セルが選択される。

Retusoui2s

最初に選択した状態のときは、セルA4の「小口美帆」がアクティブセルなので、次のような動きをする。

セルA4の上下に対して「小口美帆でないもの」を探す。
(つまりA列のなかで「小口美帆でないもの」)

セルB4の上下に対して「福井栄一」でないもの」を探す。
(B列のなかで「福井栄一でないもの」)

セルC4の上下に対して「出口博子でないもの」を探す。
(C列のなかで「出口博子でないもの」)

イメージとしては次のよう。

Retusoui3s

この場合は、最初にアクティブセルがA列の4行目にあるので、4行目にある各セルの上下で異なる値を探している。

4行目にある各セルを基準にして、列ごとに相違を探しているので、4行目のどのセルをアクティブセルにした場合でも、最後に選択されるセルの結果はすべて同じになる。

| | コメント (0) | トラックバック (0)

より以前の記事一覧