表計算で VLOOKUP 関数の第３引数をハードコードしないで MATCH 関数の結果を与える。- 参照先（検索対象）の変更が、参照元に影響を与えないために。

• VLOOKUP 関数による値の参照 - Google スプレッドシートで「日付」から「曜日」を表示.WEEKDAY 関数、「範囲名」を利用して のつづき

1. VLOOKUP 関数の第３引数がハードコードされていると柔軟性に欠ける

a. CHOOSE 関数

例えば、表計算で「日付」から「曜日」を得るには、WEEKDAY 関数を日付に適用し、その結果を元に CHOOSE 関数で場合分けを行う。

=CHOOSE(WEEKDAY(A2),"日","月","火","水","木","金","土","日")

この場合、「曜日」はハードコードされるため、曜日の表示を変えたい場合、式の中身を変更しなければならない。

 

b. VLOOKUP 関数

別の方法として、VLOOKUP 関数を用いてデータを参照することで、日付から曜日を得ることができる。

この場合、「WEEKDAY 関数が返す値」と、「曜日」に対応付けた表を予め作成しておく。

表計算を「簡易データベース」として利用することを考えた場合、「曜日」シートを作成し、当該範囲に対して「曜日」と名前を付けておく。

そして、以下のように VLOOKUP 関数を記述する。

=vlookup(weekday(A2),曜日,2,false)

 

c. 参照先の表に情報を追加する場合

ここで例えば、「曜日」の表記を「英語」に変更したいとする。

そのための方法の一つは、参照先の範囲「曜日」に入力した「曜日名」を、日本語から英語に書き換えること。しかし、この方法では「曜日」の表記に変更したいとき、範囲「曜日」の曜日名を変更しなければならない。

別の方法としては、「曜日」シートに「英語」表記の列を追加し、VLOOKUP 関数の第 3 引数で返す値を変更する。

=vlookup(weekday(A1),曜日,3,false)

VLOOKUP 関数の第 3 引数は、参照先の範囲において、関数が返す値が入力されている「列のインデックス」を指定する。

VLOOKUP - Drive Help によると、

VLOOKUP(search_key, range, index, [is_sorted])

index - The column index of the value to be returned, where the first column in range is numbered 1.

 

d. VLOOKUP 関数の第 3 引数をハードコードすると柔軟性に欠ける

上記のように VLOOKUP 関数の第 3 引数をハードコードしてしまうと、参照先の範囲を変更すると、呼び出し元に影響を与えてしまう。

例えば、参照先の範囲に「日本語の曜日」「英語の省略表記」の列を追加したら、参照元の VLOOKUP 関数の結果が変わってしまう。入力済みの VLOOKUP 関数の第 3 引数を変更しなければならなくなる。

 

2. MATCH 関数で位置を取得する

これを解決するためには、VLOOKUP 関数の第 3 引数に必要な値を MATCH 関数で求めるようにすれば良い。

MATCH 関数は、検索する範囲と、検索キーを与えると、条件にあったセルの相対的な位置を返す。

MATCH(search_key, range, search_type)

 

見出し行となる範囲に名前をつける

今回の場合、範囲「曜日」に「見出しとなる行」を予め作成しておく。注意する点は、見出し行にある値が重複しないようにすること。

ここでは、MATCH 関数で範囲を参照しやすくするために、見出し行の範囲に名前をつけておいた。

見出し行の範囲の名前は、「簡易データベース」を意識して、

[対象範囲の名前]_属性

となるようにした。

具体的には、見出し行を選択した後、

• メニューより > データ > 名前付き範囲…

を選択し、

'曜日'!A1:E1

を「曜日_属性」という名前にする。

 

MATCH 関数を使う

確認のため、「英語省略表記」が何列目にあるのか「曜日」シートで確認してみる。

=match("英語省略形", 曜日_属性,0)

結果は 5 となり、「曜日シート」の５列目が「英語省略形」であることを確認した。

MATCH 関数における第３引数は、検索対象がソート済みではないことを表す。

MATCH - Drive Help によると、

0 indicates exact match, and is required in situations where range is not sorted.

 

3. VLOOKUP 関数と MATCH 関数を組み合わせる

MATCH 関数を使うことにより、VLOOKUP 関数の第3引数を「固定された数値」から「match 関数により得られる値」に変えることができる。

例えば、日付から「英語省略形」を曜日に書き込みたい場合、次のように入力する。

=vlookup(
	weekday(A2), 
	曜日,
	match("英語省略形",曜日_属性, 0),
	false
)

表計算の式を Excel Formula Beautifier で整形する

1. 表計算の式を整形したい

Google スプレッドシートや LibreOffice Calc, Excel で入力した「式」を整形したい。

例えば、セルの値が「２で割り切れるか？」確認するために、

• ２で割り切れる場合は「◯」
• 割り切れない場合は「✕」

を対象のセルの隣に表示したいとする。

対象のセルが A1 の場合、以下のような式となる。

=if(mod(A1,2) = 0,"◯","?")

この程度なら、一行で書かれていても式の内容を理解できる。しかし、式が複雑になるに連れ、一目で把握することは難しい。

式を整形してから、意味を考えなくてはならない。

 

2. 式を整形する

Online Excel Formula Beautifier は、表計算の式を整形してくれる。

先ほどの式を上記サイトの左上のフィールドに貼り付けると、下に整形された式が表示される。

この程度の短い式でも、整形された方が読みやすい。

=if(
    mod(
        A1,
        2
    ) = 0,
    "◯",
    "?"
)

LebreOffice Calc, Excel では、整形された結果をブラウザ上でコピーしてからセルに貼り付けても、問題なく計算が行われる。

しかし、Google スプレッドシートでは、一度、整形結果をエディタに貼り付けてから、それをコピーして貼り付けないとエラーが表示された。

Excel, LibreOffice, SQL におけるワイルドカード (*) の意味

1. Excel でワイルドカード

Excel で文字データを入力した。入力中、不明なデータについては、半角で

?

とした。後になって、不明なデータを検索しようと `?’ を検索。しかし、上手くヒットしない。ただし、全角の

？

で検索すると、半角の ? がヒットした。

なぜなら、Excel では、半角の `?’ が 任意の一文字を表すため。また、全角の `？’ は、文字通りクエッションマークとして認識され、デフォルトでは半角と全角を区別しないことにより、半角の `?’ も検索対象となるため。

「*」と「?」を検索する：Excel即効テクニック によると、

Excelでデータを検索するときに文字列の一部が分からなくても、ワイルドカードと呼ばれる特別な記号「*」と「?」を使って検索できるのはご存知の通り。「*」は任意の文字列を表し「?」は任意の1文字を表す。…

「*」と「?」を検索文字として指定するときは「~*」「~?」のように「~」を付けて入力すればよい

正規表現 – Wikipedia の書き方とは違うので気をつける必要がある。

 

2. LibreOffice で正規表現による検索

これに対して、LibreOffice では、正規表現で検索できる。

Writer/Using Wildcards in Text Searches/ja - LibreOffice Help によると、

編集 → 検索と置換を選択します。

ダイアログを展開するには、詳細オプションをクリックします。

正規表現チェックボックスを選択します。

素直に書けるので、検索するときに間違えることはない。

任意の 1 文字を表すワイルドカードは、ピリオド記号 (.) です。

直前の文字の任意回数の繰り返し (ゼロ回を含む) を示すワイルドカードは、アスタリスク記号です。

LibreOffice の方がシンプルで好きだなぁ。

 

3. SQL との比較

ちなみに、SQL では、任意の一文字を表すのに `_’ を用いる。

SQLの基礎「SELECT」文を覚えよう によると、

（4） LIKE条件
文字の検索条件を指定します。ここで、％と＿（アンダースコア）は特殊な意味が割り当てられており、％は「任意の文字数の任意の文字」、＿は「1文字の任意の文字」を表します。

Access は `?’, `_’ が任意の一文字を表す。

あいまいな条件抽出 - LIKE演算子 ： SQL入門講座 によると、

アスタリスク (*)
パーセント(%)
0文字以上の任意の文字列を表す。

疑問符（?）
アンダスコア(_)
任意の一文字を表す。

普段から使ってないと、混乱する。 (+_+)

PDF ファイルの比較 - xdocdiff WinMerge Plugin

1. WinMerge と xdocdiff

2 つの PDF ファイルを比較し、変更点を確認したい。

xdocdiff WinMerge Plugin は、

diffツールであるWinMergeで、 Word、Excel、PowerPoint、pdf、その他のファイルを比較し差分を見られるようにするプラグインです。

WinMerge via kwout

WinMerge の 64 bit 版では xdocdiff を使えないので、WinMerge 日本語版 より 32bit版(XP以降) をダウンロードしてインストール。

 

プラグインのインストール

後は、xdocdiff WinMerge Plugin のインストール方法に従う。

xdoc2txt.exe、zlib.dllを、WinMergeのインストールフォルダ（WinMerge.exeと同じフォルダ）にコピーしてください

amb_xdocdiffPlugin.dllを、インストールフォルダのサブフォルダ"MergePlugins"にコピーしてください

(太字は引用者による)

右図のような配置になればよい。

 

2. 各種エラー

64bit版を使った場合

ちなみに、WinMerge 日本語版 の 64bit 版の場合、ファイルを比較すると、

Failed to load library!
Continue anyway?

とエラーが表示された。

 

プラグインの自動展開を忘れた場合

WinMerge のメニューより、

• プラグイン > 自動展開

が選択されていない状態で、ファイルの比較すると、

エンコーディングエラーにより情報が失われています。

とエラーが表示される。

 

3. ファイルの比較方法

1. ファイルを比較するには、WinMerge を起動
2. 比較したい 2 つのファイルを WinMerge に D&D

 

4. レポートの作成

上記の結果を他人に見せたい場合は、レポート機能を使う。

• ツール > レポートの生成

これにより HTML ファイルが作成される。

 

関連記事

• WinMerge - すぐに忘れる脳みそのための Wiki

表計算のセルを使った簡易グラフ - Google スプレッドシート, Excel, LibreOffice Calc で手作業による入力

専用のツールを使ったり、ドロー系のアプリを使わなくても、表計算のセルを使って簡易グラフを作成できる。原始的だけれど、正確さや厳密さを求めず、必要な情報を素早く把握するためのお手軽でコストをかけない方法。

例えば、下図のように「複数のタスクからなるプロジェクト」があり、

• 予定していた期間
• 実際に行なった期間

の二つを大雑把に把握したいとする。

( 上図、日付の連続データを作成する方法はこちら。 )

 

グラフ部分の作成

グラフを入力するには、グラフとして表示したいセルを範囲選択しておき、メニューより、

• 表示形式 > 条件に応じて色を変更  (Google スプレッドシート)
• 書式    > 条件付き書式          (Excel)
• 書式    > 条件付きの書式設定     (LibreOfice Calc)

ここでは条件として「完全に一致するテキスト」を選択し、入力されるテキストとして数値の 1、2 をそれぞれ入力した。

グラフのように見せかけるためには、「テキスト」と「背景」を同じ色に設定する。

 

グラフの入力

赤色のバーを表示させるには、セルに`1’ を入力した後、

セルの右下のハンドルをつかんで右へ伸ばしていく。

• LibreOffice の場合は、Ctrl キーを押しながら右へ伸ばしていく。

グラフを削除したいときは、範囲を選択して Del キーを押す。

LibreOffice Calc でセルに複数行を入力

1. 表計算で改行を入力

Excel, Google スプレッドシート でセルに複数行入力するときは、改行したい場所で

Alt + Enter

LibreOffice の場合は、

Ctrl + Enter キーを押すと、任意改行されます。

( Calc/Writing Multi-line Text/ja - LibreOffice Help より )

 

2. 特定の IME と相性が悪い

しかし、SKKIME で入力中 … あれ？押したけれど改行されない。。 (@_@;

そしてなぜか

Ctrl + Alt + Enter

で改行された。

どうやら使っている SKKIME と相性が悪いようだ。

Goolge 日本語入力では、上記のように Ctrl + Enter で改行された。

追記(2015/5/25): 直接入力モードになっていると、Ctrl + Enter で改行できる。

Excel でコメントの編集ができない

Excel でセルに入力されているコメントを編集しようとした。

しかし、コメントが挿入されているセルで、コメントの内容を編集しようと右クリックしても、

「コメントの編集」

が表示されず、「コメントの削除」しかない。

メニューより「ツール > 保護」で「シートの保護」がされていないか確認したけれど、保護されていない。

おかしいな？と思いよく見たら、ウィンドウのタイトルの末尾に

[作業グループ]

と表示されており、シート Sheet1 と Sheet2 の二つがアクティブな状態になっていた。

複数のシートの番地が同じセルにコメントを挿入できないのだから、コメントを編集できるわけないか。。 一つのシートだけをアクティブにしたら、コメントを編集できるようになった。

あ～、それにしても [作業グループ] の状態になっているのに気がつかず他のセルを編集してしまったので、表がぐちゃぐちゃになってもうた。 (+_+)

表計算 (Excel, OpenOffice Calc) における移動系のショートカットキー

シートがたくさんある表を見る場合、移動系のショートカットキーを覚えた方が操作が楽になる。

基本 Ctrl キーを押しながら…

1. Page Down , Page Up : シートの移動
2. Home, End               ： 左端・右端へ
3. 矢印キー                   : 連続するデータのジャンプ

詳しくは、Excel のショートカット キーとファンクション キーについて - Excel - Microsoft Office より

シートの移動

Ctrl + PageDown キーを押すと、ブック内で次のシートに移動します。

Ctrl + PageUp キーを押すと、ブック内で前のシートに移動します。

ワークシート内の左端・右端へ

Ctrl + Home キーを押すと、ワークシートの先頭に移動します。

Ctrl + End キーを押すと、ワークシートの最も下の行の右端の列にある最後のセルに移動します。カーソルが数式バーにあるときは、文字列の末尾にカーソルを移動します。

連続するデータの移動

Ctrl キーを押しながら方向キーを押していくと、ワークシート内の現在のデータ範囲 (データ範囲: データが入力されていて、周囲が空白セルまたはシートの端で囲まれているセル範囲。) の先頭行、末尾行、左端列、または右端列に移動します。

Excel のフィルが動作しない場合

Excel で行ごとに同じ計算を繰り返す場合、一つ入力したセルの右下にマウスのポインタを移動させ、十字になったところで下方向へドラッグする。しかし、一見これが動作していないように見えたら、

F9

を押して再計算。

これで意図した計算結果が表示された場合、メニューより「ツール > オプション」の「計算方法」タブで、計算方法において「自動」が選択されていることを確認する。

知らない内に上記の設定が変わっていたなら原因は、

• [XL2002] 作業グループの状態で Excel 終了時にファイルを保存すると計算方法が手動になる

とのこと。

 

参考サイト

• 計算方法が勝手に手動になる－作業グループのまま終了：Excel エクセルの使い方-関数/計算式
• 複数シートへの一括操作－作業グループ：Excel エクセルの使い方-入力/編集

Excel で組織図を描く

手順

1. メニューより「挿入 > 図表…」
2. 「図表ギャラリー」が表示されたら、組織図のアイコンを選択。

 

見栄えを良くする

デフォルトで表示される組織図の図形はシンプルなので、見栄えを良くしたい。

1. 作成された組織図を選択
2. 「組織図のツールバー」 で、「組織図スタイルギャラリー」ボタンを押して、表示される適当なスタイルを選ぶ。

 

カスタマイズ

上記と同じく「組織図のツールバー」 で「レイアウト > 組織図のオートレイアウト」を選択。これで図形を自由を移動したり、形を変えたりできる。

 

コネクタ

組織図の線を引くには、画面下のオートシェイプのコネクタを使うのがよい。(オートシェイプが表示されてない場合は、メニューの何もないところで 「右クリック > 図形描画」 を選択。)

適当に図形を配置し、コネクタで接続する。

Excel でリンクを含んだセルから HTML の A 要素を作成

Excel の A 列に特定のサイトへのリンク付きのテキストがある。ここから HTML の

<li><a>サイト名</a></li>

というような文字列を作成したい。

 

方法

単純にハイパーリンクを記入したセルを別のセルから参照すればいいのかと思いきや、参照するとリンクがなくなりテキストだけになってしまう。

リンクを抽出したい場合、どうすればいいのだろう？

エクセルのハイパーリンクについて教えて下さい –OKWave を見ると、Range オブジェクトから Hyperlinks コレクションを取得する方法が書かれていた。特定の範囲からリンクのアドレスを抽出するということのようだ。(via  エクセルのハイパーリンクのURLを抽出するため、... - 人力検索はてな)

 

標準モジュールに以下の関数を定義。

Function createHtmlLink(objRange As range) As String
    createHtmlLink = objRange.Hyperlinks(1).Address
End Function

B1 セルに以下を記述して、下方向へフィル。

="<li><a href="""&createHtmlLink(A1)&""">"& A1 & "</a></li>"

結果

すぐに忘れる脳みそのためのメモ

Home ‎(すぐに忘れる脳みそのための Wiki)‎

 

参考サイト

• 【EXCEL】ハイパーリンクからURL情報だけを抜き出すには？ - Yahoo!知恵袋

Excel の VBA でグラフの対象範囲を更新する

1. グラフの内容を、データ入力に応じて変化させたい

Excel でデータから、グラフを作成。グラフをワークシートに埋め込んだとする。

このグラフをデータの入力応じて変化するようにしたい。

このために、グラフの範囲設定に OFFSET 関数を入力してみたけれど、できなかった。

• cf. Excel で入力に応じて計算の範囲を変化させる

OFFSET の具体的な結果が設定され、値が固定されてしまう。 (+_+)

 

2. マクロで記録したものを改造

そこで、マクロで、グラフの対象範囲を変更する操作を記録してみた。

Sub Macro1()
    ActiveSheet.ChartObjects("グラフ 1").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C10")
End Sub

これを元にして、ワークシートに埋め込まれたグラフを更新する関数を作成。

' ワークシートに埋め込まれたグラフを更新する
Sub SetChartObjectRange(strWorksheet As String, strChart As String, strRange As String)
    Worksheets(strWorksheet).ChartObjects(strChart).Activate
    ActiveChart.SetSourceData Source:=Sheets(strWorksheet).Range(strRange)
End Sub

呼出すときは、グラフが埋め込まれたシート名、グラフ名、範囲を文字列を引数に指定する。

SetChartObjectRange "Sheet1", "グラフ 1", "A1:c10"

ところで、グラフをアクティブにしてないと上記はエラーになる。アクティブにしないで更新する方法はないのかなぁ？

 

3. Excel におけるオブジェクト

上記 ChartObject オブジェクト とは、

ワークシートにある埋め込みグラフを表します。ChartObject オブジェクトは、Chart オブジェクトのコンテナとして機能します。(ヘルプより)

これに対して Chart オブジェクト というのもあったが、これは、

ブック内のグラフを表します (ヘルプより)

つまり、Chart オブジェクトは埋め込みではなく、一つのシートで一面グラフのもの (グラフシート) を表わすようだ。

ちなみによく使う Worksheet オブジェクト は、

ワークシートを表します。(ヘルプより)

当り前か。 ^^;

Excel のオブジェクトモデルを見ていたら、名前がよく似た Sheets コレクション オブジェクト というのがあった。

指定されたブックまたは作業中のブックにあるすべてのシートのコレクションです。Sheets コレクションには、Chart オブジェクトまたは Worksheet オブジェクトを含めることができます。(ヘルプより)

Workbook オブジェクト は、

Excel ブックを表します。Workbook オブジェクトは Workbooks コレクションのメンバーです。Workbooks コレクションには、現在開かれているすべての Workbook オブジェクトが含まれています。(ヘルプより)

そして、最上位には Application オブジェクトがある。これをまとめて図にすると、

 

4. グラフシートの範囲を設定

Chart オブジェクトを更新するには、

' グラフシートを更新する
Sub SetChartRange(strChart As String, strWorksheet As String, strRange As String)
    Charts(strChart).Activate
    ActiveChart.SetSourceData Source:=Sheets(strWorksheet).Range(strRange)
End Sub

呼出すときは、グラフシート名, グラフの対象のシート名, 範囲を文字列で指定する。

SetChartRange "Graph1", "Sheet1", "A1:c10"

上記 Charts コレクション とは、

指定されたブックまたは作業中のブックにあるすべてのグラフ シートのコレクションです。各グラフ シートは、Chart オブジェクトによって表されます。ワークシートまたはダイアログ シートにある埋め込みグラフは含まれません。

Charts(“グラフチャート名”), Worksheets(“ワークシート名”) というように、コレクションに対して文字列でその要素を指定するというのがパターンのようだ。これは Access で Forms(“フォーム名”) と指定するのと同じ。

 

5. グラフの対象となるワークシートの最後の行番号を知りたい

「No.8 ワークシートの最終行、最終列を取得する」によると、

UsedRangeプロパティは指定されたワークシートで使われたセル範囲を返します

これを利用して、ワークシートで使われている範囲のアドレスを文字列で取得。それを先ほど定義した埋め込み用のグラフの範囲を設定するプロシージャに渡す。

SetChartObjectRange "Sheet1", "グラフ 1", Worksheets("Sheet1").UsedRange.Address

しかし、これだと離れた列を対象とした、以下のグラフを作成できない。

最後の行番号を取得できた方が、柔軟に対応できそう。Address プロパティで取得した文字列から行番号を抽出することにした。

 

6. 正規表現を利用

Office TANAKA - Excel VBA(正規表現によるマッチング) によると、

VBAから正規表現を使うには、VBScriptが便利です。ただし、正規表現をサポートしているVBScriptはVer5.0からですから、IE5.0がインストールされているパソコンでないと使えません。

また、正規表現における後方参照は、VBAで正規表現 - へたれプログラマな日々 を参考にした。

ワークシート名を渡されると、使われている最後の行番号を返す関数を定義する。

Function LastRowNum(strWorksheet As String)
    Set re = CreateObject("VBScript.RegExp")
    strCellAddress = Worksheets(strWorksheet).UsedRange.Address(RowAbsolute:=False, ColumnAbsolute:=False)
    re.Pattern = "[A-Z]+[0-9]+:[A-Z]+([0-9]+)"
    LastRowNum = re.Replace(strCellAddress, "$1")
End Function

これで例えば、上記のグラフの範囲を設定するなら、

    rownum = LastRowNum("Sheet1")
    SetChartObjectRange "Sheet1", "グラフ 1", "A1:A" & rownum & ",C1:C" & rownum

 

7. 全体

' ワークシートに埋め込まれたグラフの範囲を更新する
Sub SetChartObjectRange(strWorksheet As String, strChart As String, strRange As String)
    Worksheets(strWorksheet).ChartObjects(strChart).Activate
    ActiveChart.SetSourceData Source:=Sheets(strWorksheet).Range(strRange)
End Sub

' グラフシートの範囲を更新する
Sub SetChartRange(strChart As String, strWorksheet As String, strRange As String)
    Charts(strChart).Activate
    ActiveChart.SetSourceData Source:=Sheets(strWorksheet).Range(strRange)
End Sub

' ワークシートの使われている最後の行番号を返す
Function LastRowNum(strWorksheet As String)
    Set re = CreateObject("VBScript.RegExp")
    strCellAddress = Worksheets(strWorksheet).UsedRange.Address(RowAbsolute:=False, ColumnAbsolute:=False)
    re.Pattern = "[A-Z]+[0-9]+:[A-Z]+([0-9]+)"
    LastRowNum = re.Replace(strCellAddress, "$1")
End Function

Sub UpdateGraph()
    rownum = LastRowNum("Sheet1")
    SetChartObjectRange "Sheet1", "グラフ 1", "A1:A" & rownum & ",C1:C" & rownum
End Sub

表計算で入力に応じて計算の範囲を変化させる - 「範囲を返す」 OFFSET 関数と「データ数を数える」 COUNT 関数を用いて

1. 入力に応じて計算対象の範囲を指定したい

例えば、A 列に「テストの得点」を入力する列がある。

テストの得点は、下方向へ入力していく。この入力に応じて「平均」を計算するための「範囲」が変化し、計算が行われるようにしたい。

平均を計算するとき、以下のように列まるごと対象にした式を書くことはできる。

=AVERAGE(A:A)

ここでは、列全体を AVERAGE 関数に与えるのではなく、入力された範囲を引数に与えるようにしたい。

 

2. COUNT 関数でデータ数を得る

最初に、データ数をカウントするための

• COUNT 関数

について確認する。Excel のヘルプによると、

COUNT 関数では、数値、日付、数値を表す文字列が計算の対象となります。エラー値、数値に変換できない文字列は無視されます。

よって、A 列にあるデータ数を得るには、

=COUNT(A:A)

このとき、先頭の見出しの文字列はカウントされない。

D2 セルに上記の式を記述し、そのセルを「データ数」と名付けた。

 

3. OFFSET 関数で計算対象の「範囲」を得る

次に、計算対象の「範囲」を取得する。そのためには、OFFSET 関数を利用する。

OFFSET の意味は、オフセットとは 【offset】 - 意味・解説 ： IT用語辞典 によると、

あるデータの位置を、基準点からの差(距離)で表した値のこと。

この意味からは、範囲を返す関数であることは分からない。関数の名前が良くないと思う。

Excel のヘルプによると、

基準のセルまたはセル範囲から指定された行数と列数だけシフトした位置にある高さと幅のセルまたはセル範囲の参照 (オフセット参照) を返します。

使い方は 「[XL2002] OFFSET 関数の使用方法」 を参照。

OFFSET 関数の引数をイメージしやすいように図にしておく。

表は、基本的に「行 → 列」という順序で考えるのが普通。よって、引数の並びとして、「行数」「列数」が並んでいるので覚えやすい。「高さ」「幅」というのは、言い換えれば、「対象となる行のデータ」と「対象となる列のデータ」ということ。これも「行 → 列」という順序に並んでいると見なせる。

今回は OFFSET の意味に相当する引数「行数、列数」は考えない。必要なのは、基準となるセルとそこからの範囲指定を行うための「高さ」と「幅」。

OFFSET 関数を適用する前に、セル A2 を「データの先頭」と名付けた。平均を計算するため、セル D3 に次のように記述。

=AVERAGE(OFFSET(データの先頭,0,0,データ数,1))

 

セルに名前を付けず、途中の計算も書かないで書くなら、

=AVERAGE(OFFSET(A2,0,0,COUNT(A:A),1))

となる。

Excel の VBA で名前を付けたセルの内容を参照

特定の範囲に名前を付ける

一つのセル、または、特定の範囲に名前を付けるには、セルを選択した後にワークシートの左上をクリックして名前を入力する。

例えば、セルA1 に “hoge” と名前を付けてみた。

 

付けた名前を管理しているのは、メニューより「挿入 > 名前 > 定義」。ここで定義した名前を追加・削除できる。

先ほど定義した “hoge” が表示されている。

ちなみに、ワークシートごとに名前空間があるのではなく、一つの Excel ファイルにおいて一意な名前を付けることができるようだ。

 

VBA で名前を付けたセルの値を参照

メニューより「ツール > マクロ > Visual Basic Editor」を選択し、標準モジュールを作成。(「挿入 > 標準モジュール」)

さて、セルの値を参照するにはどうすればいいのだろう？ Visual Basic Editor 内において F1 でヘルプを表示し、「Microsoft Office Excel オブジェクト モデル」を見ると、Cell というのは見当たらず。

ところで、上記のように名前を付けるとき、範囲を指定して名前を付けることができた。ということは Range で操作できそうだ。

ヘルプの Range コレクションによると、

セル、行、列、1 つ以上のセル範囲を含む選択範囲、または 3-D 範囲を表します。

標準モジュールにプロシージャを定義した。

Sub test()
    MsgBox Range("hoge").Value
End Sub

プロシージャ内で、F5 または F8 を押して動作を確認。

 

ちなみにセルを表わすものは、Worksheets オブジェクトのプロパティとして表現されていた。

Excel VBA で選択範囲を値に応じたセルの色に変更する

例

以下の表は、各男性の各女性に対する好みを 5 段階評価で表わしている。

上記の表を次のように値に応じてセルに色を付けたい。

方法

予めセルには値が入力されていて、色をつけたい範囲を選択してから、ボタンを押して色を付けるという実装にする。

VBA で以下のコードをサブルーチンとして定義する。

With Selection
For i = .Row To .Row + .EntireRow.Count - 1
    For j = .Column To .Column + .EntireColumn.Count - 1
        If IsNumeric(ActiveSheet.Cells(i, j).Value) Then
            ActiveSheet.Cells(i, j).Interior.ColorIndex = _
                ActiveSheet.Cells(i, j).Value + 41
        End If
    Next
Next
End With

ボタンを作成し、上記サブルーチンを呼出すようにする。

セルの色は、Cells の Interior.ColorIndex で設定する。この値は以下の数値と対応している。

参考

• Range メンバ (Microsoft.Office.Interop.Excel)
• Range.EntireColumn プロパティ (Microsoft.Office.Interop.Excel)

疑問

最初、Worksheet の Change イベントで値が入力されたときに、セルの色を変更しようと考えた。一つ一つの値を入力するときは問題なかったが、複数の値をコピペして値を変更しようとするとエラーがでてしまった。 QQQ

Zoho から csv 形式でエクスポートしたファイルを開く

Zoho Sheet や DB & Reports には、csv 形式でデータをエクスポートすることができる。ただし、文字コードが UTF-8 になるので適切な変換が必要である。 ( csv 形式のファイルをアップロードするときには、逆に UTF-8 にする必要がある。)

 

OpenOffice Calc の場合

テキストのインポートダイアログの、インポート > 文字列 で 「Unicode (UTF-8)」を選択する。

 

xyzzy, サクラエディタ, Notepad++ の場合

自動的に UTF-8 で開いてくれる。

 

Excel の場合

ファイルを 文字コード変換ツール「KanjiTranslator」 で Shift_JIS に変換してから開く。

Excel の折れ線グラフで、数値の離れた二つのデータ系列を同じグラフに表示する

1. 値が大きく離れた 2 つのデータ系列から、グラフを作成するときの問題

数値が大きく離れた、二つのデータ系列を、同じ折れ線グラフ上に表示したい。

例えば、データ系列 A と B があるとする。

• データ系列 A は、値として、1000 ~ 1200 の数値をとる。
• データ系列 B は、値として、1 ~ 5 の数値をとる。

A と B の数値が大きく離れている。そのため、二つの系列のデータから、折れ線グラフを作成すると、データ系列 A の変化の幅が小さく、データの推移を読み取りにくい。

 

2. 見やすいグラフを作る方法

２つのデータの推移を把握しやすくするには、

1. グラフの左側の軸である、データ系列 A の表示する値を、1000 ~ 1200 の範囲にして、
2. 右側の軸は、データ系列 B の表示する値を 1 ~ 5 の範囲としたい。

この場合、次のような操作をする。

1. とりあえず、グラフを作成する。
2. 作成したグラフにおいて、データ系列 B の折れ線を、右クリック > データ系列の書式設定 を選択
3. 「軸」タブをクリックし、「使用する軸」において、「第2軸」を選択する。

Excel で特定の期間のデータだけ抽出したい

データ > フィルタ > オートフィルタ

日付の列に表われたセレクタ > オプション を選択し、日付の範囲を指定する。

参考

Excel(エクセル)基本講座：オートフィルタ・フィルタオプション(データ抽出)