エクセル

Excel2016 でファイルの一覧を作る。

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2016 Office365 Business サブスクリプション バージョン1710(ビルド8625.2121))

1.ワークシートを開く。(新規でも既存のものでもよい。)

2.「データ」タブ-「データの取得」-「ファイルから」-「フォルダから」をクリック

3.「フォルダーパス」を入力する画面になるので「参照」をクリックして、ファイルの一覧を作りたいフォルダを選んで、OKをクリック

4.「フォルダーパス」の欄で目的のフォルダが指定されていることを確認して、OKをクリック

5.プレビュー画面が表示されるので「読み込み」をクリック

6.テーブル形式でファイル名の一覧が作成される。
3. で選んだフォルダの下のフォルダにもエクセルのファイルがある場合、それも一覧に含まれる。「上のフォルダでだけ一覧を作る(フォルダの下のフォルダは対象にしない)」という設定はない。

一度一覧を作ると、該当のフォルダのなかで新規作成・ファイル名の変更・あとから保存したファイルも、「クエリ」タブの「更新」を実行することでファイル名を追記・変更できる。
また、上記3.と4.で指定したフォルダのなかであれば、新たにフォルダを作ってファイルを移動させても「更新」でファイル名の追記・変更に対応できる。

「データ」タブ-「クエリと接続」グループ-「プロパティ」-「クエリ プロパティ」ボタンで、「定期的に更新する」を使うと、1分刻みに更新の間隔を指定することができる。
最低「1分」から最大「32767分」。

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

Excel とメモ帳で、ダブルコーテーションで値を囲った CSV ファイルを作る。

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2016 Office365 Business サブスクリプション バージョン1710(ビルド8625.2121))

■元の表

Dq_kakomu1

■考え方

最後に、ダブルコーテーションで値を囲った CSV ファイルになればよい。
値の左右に、何か目印になるものを付け加えて CSV ファイルを作り、あとで目印を置換して、ダブルコーテーションで値が囲まれているようにする。

■作業の手順

1.元の表とは別の列で、生年月日のセルを TEXT 関数で文字列に変換する。
1967/12/12 2のように / で区切った数字は、「日付」として扱われている(エクセルの内部で「シリアル値」という数字として扱われている。)ので、文字列に変換しておくことが必要。
・例
=TEXT(B2,"yyyy/mm/dd")

Dq_kakomu2

2.変換した文字列のセルを、生年月日の列へ「値貼り付け」する。
貼り付け終わったら、文字列に変換する式を入れたほうの列は不要なので削除。
(そのままにしておいてもよい。)

Dq_kakomu3

4.元の表とは別のセルに、 " (ダブルコーテーション)を入れて、そのセルを値の左右に結合する式を入れる。ダブルコーテーションを入れたセルは絶対参照にしておく。
・例
=$E$1&A1&$E$1

5.式をほかのセルにコピーして表を完成させる。

Dq_kakomu4

7.完成した表を選択してコピーする。

8.Ctrl + N で新しいワークシートを起こして、「値貼り付け」する。

Dq_kakomu5

9.値貼り付けしたワークシートを CSV で保存する。

8.メモ帳で CSV を開く。
"""氏名""" のように値の左右を3つのダブルコーテーションが囲っている。

Okikae0

9.置換で、検索する文字列に "" (2つのダブルコーテーション)を入力。

10.置換後の文字列の欄は空欄にしておく。
左右にある3つのうち2つを消せば、ダブルコーテーションを左右に1つずつ残せる。

Okikae1

11.「すべて置換」をクリック

Okikae2

13.メモ帳を上書き保存する。
これで、ダブルコーテーションで値を囲った CSV ファイルが完成。

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

Excel2016(Office 365 サブスクリプション)にだけある IFS 関数

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2016 Office 365 Business サブスクリプション)

K.F さん。

お互い、Office 365 が入ってよかったですね。
Office 365 Business だと聞いています。

同じ Office2016 (Excel2016, Wrod 2016, Powerpoint2016 など)でも、単体で買った人と、Office 365 に加入してインストールした人とでは、使える機能が少し違ってきます。

Office 365 は、定期的に利用料金を支払ってエクセルなど使いますが、そういう使い方を「サブスクリプション」というそうです。

関数も、サブスクリプションの Excel2016 にだけ搭載されている関数があります。
(Office2016 や Excel2016 を単体で購入した人は使えない関数です。)

その一つが IFS 関数です。

IFS 関数 (IFS 関数 - Office サポート)

IFS 関数を使ったファイルを、Excel2013 で開いてみました。
数式の前に _xlfn. という文字が付いて #NAME? というエラーが起きます。サブスクリプションではない Excel2016 でも同じかと思います。

自分以外の人にファイルを渡すときは注意が必要です。
先方がサブスクリプションの Excel2016 なのかどうかわからない場合は、IFS 関数は使わないでおくのが無難でしょうね。

■IFS 関数の利用について

IFS 関数を使うと「関数をネストする」必要がないので、見やすくて何をしているかの理解がしやすい式を書けます。
たとえば、 IF 関数で

  • セル A1 が空欄なら何も表示しない。
  • 76以上ならSを表示する。
  • 61以上76未満ならA
  • 46以上61未満ならB
  • 31以上46未満ならC
  • 0以上31未満ならD

を表示する。という式を書くと、

If

になります。

IFS 関数だとこうです。

Ifs

IF 関数よりもずっと見やすく、理解しやすいですよね。

■式の中でも Alt+Enter でセル内改行ができる。

ちなみに、僕もひとから教えてもらったのですが、式の中でも Alt+Enter のセル内改行ができます。
文字で説明するとなんのことだかわからないと思いますが、こういうことです。

Siki_no_nakade_kaigyo_ifs

2行目以降の A1 の左端はスペースを入れて頭をそろえています。
これでもなんのエラーも起こさないで答えが出ます。

こういう形にしておくと、何をしている式なのかがますます読み取りやすくなります。

IF関数だとこうです。

Siki_no_nakade_kaigyo_if

式の中での改行は、サブスクリプションではない Excel2016 はもちろん、 Excel2013 でも可能です。

あとから自分が見る場合にもわかりやすいです。自分以外の人もそうでしょう。業務で使うファイル(数式、VBA コード)は、自分以外の人が見てもわかりやすく作っておくことが大切です。

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

Excel でフォルダを作る。

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

K.K さん。

Excel でフォルダを作るマクロです。

  1. フォルダ名の頭に 00 や 01 という番号をつけたい。
  2. 番号は連番とは限らない。
  3. 番号の横に任意のフォルダ名をつけたい。
  4. 多い場合は100個ほどのフォルダを作る場合もある。

ということでしたので、次の手順でお願いします。

番号から始まる文字列をワークシート上でつくってから、それをフォルダ名に使うという流れにしてみました。

■手順
1.任意のフォルダを作り、エクセルの新規作成したファイルを「名前を付けて保存」から「ファイルの種類」を「マクロ有効ブック」にしてそこへ保存。エクセルのファイル名は適当に。

2.保存したマクロ有効ブックを開く。

3.「開発」タブの「Visual Basic」をクリック
または、 Alt + F11 のショートカット。

4.Visual Basic Editor が起動してくるので「挿入」から「標準モジュール」を選択

5.下記のコードをコピー貼り付けして、いったん VBE は閉じる。

6.A2からセルを必要な数だけ選択し、セルの表示形式を「文字列」にする。
1行目は列見出しをつける想定。

7.00 や 01 から始まる番号を、A2 から入力。
コードのなかで、A列の最終行を取得しているので、A列は必ず入力を終えておく。

8.番号に続ける文字列を、B列に入力。

9.A列の番号とB列の文字列を、C列で結合する。(例 =A2&B2 )

10.今のシートを開いたままで、「開発」タブの「マクロ」をクリック

11.「フォルダの作成」が選択されていることを確認して「実行」をクリック。

12.エクセルのファイルを保存しているのと同じフォルダ内に、C列の値を使ったフォルダができる。
できたフォルダを切り取って、目的のフォルダ内に移動させる。

※付記
1.の時点で、既存のフォルダ内にファイルを保存しておけば、そのフォルダの直下に新しいフォルダを作ることができます。
ただ、パスの文字数が多すぎないかとか、フォルダ名には使えない文字を使っていないかといったチェックが、下記のコードではできていません。

いったんワークシート上でフォルダ名をつくってから、そのあたりのチェックをしておいた方が無難です。

いろいろ面倒ですみません。私の VBA のスキルの限界でして……。


Sub フォルダの作成()
    Dim i As Long
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        MkDir ThisWorkbook.Path & "\" & Cells(i, 3)
    Next i
'
'任意のフォルダを作り、このマクロを含むマクロ有効ブックをそこに保存しておく。
'
'開いているシートの2行目から値を入れていく。
'
'列見出しを使わずに1行目から値を入力する場合は、コードのなかの
' For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row の 2 を 1 にして、
' For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
'に変える。
'
'フォルダ名として使われるのは C 列に入っている文字列。
'マクロを実行すると、 C 列のセルにある文字列を名前としてフォルダが作られる。
'
End Sub

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

INDEX 関数の速さは、VLOOKUP 関数の約110倍

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

K.Y さん。

このあいだ送った Excel のファイルで、「VLOOKUP 関数ではなくて、INDEX 関数なのはどうしてか」、「VLOOKUP 関数ではいけないのか」という件ですが、あの場合は、INDEX 関数でも VLOOKUP 関数でもどちらを使っても問題ないです。

どちらでもよかったのですが、一応、基本というか、定石というか、セオリー通りというか、原則というかで、INDEX 関数を使ってあります。

値を表引きしてくる速度は、VLOOKUP 関数よりも IDEX 関数のほうが速いんです。その速さはタイトルにある通り。

とはいえ、あの例では値を引っ張ってくる元になっているデータは、最大でも19件(行)しかありません。
19件程度では、人間が知覚できるような差は生じないので、どちらでもいいというわけです。

データの件数が大量になってくると、答えが出るまでの時間が違ってきます。
大量というのがどのくらいかというと、手元で試してみたら、9,000件超えてきたら見た目に「あれ?」と気づくぐらいの違いが出てきます。

1,000や2,000では、たいした差がありませんでした。

思い切って50万件で試してみました。
(時間がかかるだろうなと思っていたので昼飯を食べながら……)
試しに使った PC は以下のような機種です。
■計測に使ったPC
Windows 10 Pro 64bit
Intel(R) Core(TM) i5-4200U CPU @ 1.60GHz
メモリ4GB

試したやり方は以下のようです。
■INDEX 関数
A列とC列にあらかじめ 1 から 500,000 までを連番・昇順で入力。
セル D1 に INDEX 関数を使った次の数式を入力。

=INDEX($A1$:$A$500000,C1,1)

D1 のフィルハンドルをダブルクリックすることで、数式を D500000 までコピーし、すべてのセルに答えが表示されるまでの時間を計測。

約7秒。

■VLOOKUP 関数
A列、B列、D列にあらかじめ 1 から 500,000 までを連番・昇順で入力。
セル E1 に VLOOKUP 関数を使った次の数式を入力。

=VLOOKUP(D1,$A1$:$B$500000,2,FALSE)

E1 のフィルハンドルをダブルクリックすることで、数式を E500000 までコピーし、すべてのセルに答えが表示されるまでの時間を計測。

約13分。(約780秒)

INDEX 関数のほうが約110倍速いでしょ?
でもまあ、我々の日々の仕事でそんな大量のデータを扱うかというと――ないでしょうね。

というわけで、範囲の左端を目印にできるなら VLOOKUP 関数が使える。左端を目印にできないなら、INDEX 関数が使える。
このぐらいの認識でいいと思います。
(たまに勘違いして、 VLOOKUP 関数を使うときに、表の左端の列しか指定できないと思っている人がいますが、そんなことありません。選択した範囲の左端です。4列目から6列目まで選択したら、4列目が左端になります。)

ちなみに、1 から 500,000 まで連番で入れるやり方ですが、A1 に 1 を入れて、 A2 に 2 を入れて両方を選択してず~~っと下へ引っ張る――1分やってみました。5622までいきました――って、そんなやり方ではかなり暇がないとできないですよね。

「フィル」という機能の「連続データの作成」が便利です。

1.セル A1 に 1 を入力して選択しておく。

2.「ホーム」タブ-「編集」グループ-「フィル」-「連続データの作成」を選択。

Fill

3.「範囲」で「列」をクリック。

4.「種類」が「加算」になっていることを確認。

5.「増分値」には 1 を、「停止値」に 500000 を入れて、OK をクリック。

Renzokudata_2

これでA列に 1 から 500000 までの連番ができます。

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

Excel で簡単に範囲選択するやり方

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

K.Y さん。

Excel で表を選択する場合のやり方です。

いろいろあるでしょう( VBA で Range("C1:C10").Select  とか書いて実行するのも「選択」ですしね。)けど、「簡単に」ということだったので、次の2つはどうでしょうか。

■ショートカットを使う。

Ctrl + Shift + : (コロン)

隣接しているセルを一括で選択するショートカット。

表全体を選択したいといった場合に便利。
さきに表のなかのセルをどこでもいいので選択しておいてからショートカットを実行。

■「名前ボックス」を使う。

数式バーの左側にある「名前ボックス」に、セル参照を入力。
離れたセルも指定できる。
(画像の出典:Excel ヘルプ)

名前ボックスの1

・名前ボックスに入力するセル参照の書き方例
(入力時は a1 のように小文字で入力してよい。)
C5 → セル C5 を選択
A1:D11 → A1 から D11 までを選択
B2,C5,E9 → B2 と C5 と E9 を選択

上記を組み合わせた書き方もできる。
A1,B3:B10,F15 → A1 と B3からB10 、F15 を選択

まとめると、

名前ボックスの2

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

Excel のセルに表示された数式を計算結果に変える。

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

表示形式が「文字列」になっているセルに数式を入力すると、数式がそのまま表示される。

計算結果を表示するには、 = を = で置換する。

■ = を = で置換する。

  1. 数式が表示されているセルを選択して表示形式を「標準」に変更する。
  2. 「検索と置換」ダイアログボックスを呼び出す。( Ctrl + H )
  3. 「検索する文字列」の欄に  = を入力。
  4. 「置換後の文字列」の欄に同じく = を入力。
  5. 「すべて置換」ボタンをクリック
    数式の表示が消えて、計算結果が表示される。

数式が表示されているセルの個数が少なければ、表示形式を「標準」にしてから、F2 かダブルクリックで編集モードにして Enter を押すやり方もある。

もし、数式が表示されているセルを選択して、参照元(セル内の数式によって参照されているセル)に色がついたら「数式の表示」がオンになっている。
「数式」タブ-「ワークシート分析」グループ-「数式の表示」をクリックすると、計算結果が表示される。

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

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)

より以前の記事一覧