Excel 画像の圧縮方法

Excelでは、挿入した画像を

圧縮することができます。

1.図の形式

図の圧縮を選択

Excelで画像を圧縮するには。

画面上部のメニューから挿入→画像から好みの

画像を選択し挿入します。

画面上部のメニューから図の形式→図の圧縮を選択します。

「図の圧縮」のパネルが開きます。

OKボタンをクリックすれば、圧縮されます。

2.圧縮のオプションと解像度の種類について。

圧縮オプションは主に2つあります。

「この画像だけに適用する」のチェックマークを外せば、

シート内のすべての画像を圧縮できます。

また、「図のトリミング部分を削除する」に

チェックマークを入れれば、トリミングして

見えなくなっている部分が削除されます。

3.解像度の種類

解像度の種類には、下記があります。

・HD・・・HD用の高解像度に圧縮されます。

・印刷用・・・印刷に適した解像度に圧縮されます。

・Web・・・Webやプロジェクターで 表示するのに適した解像度に圧縮されます。

    電子メール用・・・電子メールで確認するのに適した解像度に圧縮されます。

・ドキュメントの解像度を適用・・・Wordに設定されている解像度に圧縮されます。

    初期設定だと［印刷用］と同じ解像度になります。

画像の圧縮をすれば、ファイル容量が軽くなります。

ぜひ紹介した内容を参考にしてみてください。

　

UNIQUE関数、一意の値（ユニーク化）

UNIQUE関数は、範囲または配列から一意な値を返します。

範囲または配列から重複を削除して一意化した配列を返します。

UNIQUE関数はスピルで登場した新しい関数です。

◆UNIQUE関数の書式

=UNIQUE(配列,[列の比較],[回数指定])

配列

必須です。

並べ替え処理する元データを指定します。

セル範囲または配列です。

列の比較

省略可能。

TRUE : 一意の列を返す

FALSE : 一意の行を返す

省略するとFALSE（一意の行）になります。

そもそも引数名がとても分かりづらいです。

これは、

横（列）方向のデータを一意にする。

縦（行）方向のデータを一意にする。

ということです。

つまり、通常の縦に連なるデータに対して使う時は、

FALSE : 一意の行を返す

これを使う事になります。

従って、ほとんどの場合この引数は省略して使えば良いです。

回数指定

省略可能。

TRUE : 1回だけ出現するアイテムを返す

FALSE : 個別のアイテムをすべて返す

省略するとFALSE（個別のアイテム）になります。

この引数も分かりづらいです。

FALSEが普通の一意化、重複を無くす処理になります。

従って、通常はこの引数は省略して使います。

TRUEの1回だけ出現が分かりづらいのですが、

元データに1回しか出現しないデータのみを

対象とするという事です。

つまり、

そもそも一意になっているデータのみ出力する、

重複しているデータは出力しないということです。

実際の使い道については、かなり限られると思います。

◆ワークシートでの一意化

ワークシート上で、データを一意化する方法として

・COUNTIF関数で重複を判定して削除

・ピボットテーブルで重複を削除

・重複の削除で重複を削除

・フィルタの詳細設定(フィルターオプションの設定)で重複を削除

どれも一長一短はあります。

◆UNIQUE関数の使用例

1列だけの単純な例

=UNIQUE(A1:A15)

A列だけで一意化しています。

単純にA列のデータで重複を無くした状態で出力されます。

◆複数列の場合

=UNIQUE(A1:B15)

A列とB列で一意化しています。

A列とB列を合わせたデータで重複を無くした状態で出力されます。

出力の仕方は違いますが、これは以下と同じことになります。

=UNIQUE(A1:A15&B1:B15)

◆横（列）方向の一意化

=UNIQUE(A1:O2,TRUE)

引数の「列の比較」という言葉がわかりづらいですが、

単純に、横（列）方向なら第2引数をTRUEにすれば

良いと覚えれば良いでしょう。

◆1回だけ出現するアイテム

=UNIQUE(A1:A15,FALSE,TRUE)

A列で一回しか出てこないデータのみ出力されます。

複数列でも理屈は同じです。

=UNIQUE(A1:B15,FALSE,TRUE)

例えば、A社A商品やA社C商品は2回出てきているので出力されていません。

◆UNIQUE関数の応用例

全自動の集計表を作成します。

データ行数、集計項目値の増減にも対応できます。

元表（A:E）のデータ変更に全自動で対応しています。

データ行数はもちろん、取引先や商品の増減にも対応できます。

G2=SORT(SORT(UNIQUE(FILTER(FILTER(A:E,ISNUMBER(C:C)),{1,1,0,0,0})),2),1)

以下の順に処理するように数式を組み立てています。

・内側のFILTER関数でデータのある行だけに絞り、

　外側のFILTER関数で2列に減らしています。

FILTER関数は、定義した条件に基づいてデータ範囲を

フィルター処理した結果を返します。FILTER関数は

スピルで登場した新しい関数です。最後の方では、

表示する列を選択する方法も掲載しています。

FILTER関数の書式 =FILTER(配列,含む,[空の場合]) 配列 必須です。

・UNIQUE関数で一意化

・内側のSORT関数で商品順、外側SORT関数で

　取引先順に並べ替えています。

SORT関数は、範囲または配列の内容を並べ替えます。

SORTBY関数は、範囲または配列を対応する範囲または

配列の値に基づいて並べ替えます。

SORT関数とSORTBY関数は範囲を並べ替える関数ですが、

同じこともできますが、

れぞれの関数でなければできないこともあります。

I2=SUMIFS(D:D,$A:$A,OFFSET($G1,1,0,COUNTA($G:$G)-1)

,$B:$B,OFFSET($H1,1,0,COUNTA($H:$H)-1))

J2=SUMIFS(E:E,$A:$A,OFFSET($G1,1,0,COUNTA($G:$G)-1),

$B:$B,OFFSET($H1,1,0,COUNTA($H:$H)-1))

行数自動対応でスピルさせている為、数式が複雑になっています。

SUMIFS関数を集計結果の行数変更に

自動対応させた上でスピルさせています。

SUMIFS関数は、SUMIF関数の条件が複数指定できるようになったものです。

COUNTIFS関数は、COUNTIF関数の条件が

複数指定できるようになったものです。

範囲の中で、指定した条件を満たすセルの個数を数えます。

難解に見えるのはOFFSET関数があるからです。

基準のセルまたはセル範囲から指定された行数と列数だけ

シフトした位置にある、指定の高さと幅のセル範囲の参照を返します。

つまり、基準セルから、指定数だけ移動したセルを起点として、

指定の大きさのセル範囲を取得出来ます。

OFFSET関数の書式 

OFFSET(基準,行数,列数,[高さ],[幅]) 基準

OFFSET関数を固定のセル範囲に置き換えてしまえば、

単なるスピルさせているSUMIFS関数だけになります。

これは、以下のようにテーブル＋ピボットテーブルで

作成するものとほぼ同じことになります

※データ行数に自動対応させるためにテーブルにしています。

どちらが良いという事ではなく、適宜使い分けすれば良いでしょう。

   

Excel2021の新関数 SORT関数、SORTBY関数

SORT関数は、範囲または配列の内容を並べ替えます。

SORTBY関数は、範囲または配列を対応する

範囲または配列の値に基づいて並べ替えます。

SORT関数とSORTBY関数は範囲を並べ替える関数ですが、

同じこともできますが、れぞれの関数でなければ

できないこともあります。

SORT関数とSORTBY関数はスピルで登場した新しい関数です。

SORT関数の書式

=SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])

配列

必須です。

並べ替え処理する元データを指定します。

セル範囲または配列です。

並べ替えインデックス

省略可能。

並べ替えのキーを指定します。

何列目（または何行目）を基準に並べ替えるかを

数値で指定します。

範囲の左端列が1（または上端行が1）です。

この引数を省略すると、範囲の1列目（1行目）を

基準として並べ替えます。

この引数は1つしか指定できません、つまり並べ替えに

指定できるキーは1つだけです。

上記説明における何列目または何行目の違い

並べ替え順序

省略可能。

並べ替えの順序、「昇順」か「降順」を指定します。

1 : 昇順

-1 : 降順

この引数を省略すると、1（昇順）で並べ替えられます。

並べ替え基準

省略可能。

並べ替えを行方向に行うか列方向に行うかを指定します。

TRUE : 列で並べ替え ・・・ 横に並べ替える

FALSE : 行で並べ替え ・・・ 縦に並べ替える

この引数を省略するとFALSE（行で並べ替え）で並べ替えられます。

TRUE,FALSEは、1,0で指定しても構いません。

行・列の表現が分かりづらいので、間違えないようにしてください。

一般的なデータ（横に項目、縦にデータ）では、

FALSE（行で並べ替え）になります。

SORTBY関数の書式

=SORTBY(配列,基準配列,[並べ替え順序],...)

配列

必須です。

並べ替え処理する元データを指定します。

セル範囲または配列です。

基準配列

必須です。

並べ替えのキーとして使うセル範囲または配列を指定します。

SORT関数では元データ範囲の列位置（行位置）を

数値で指定しましたが、SORTBY関数では

セル範囲または配列で指定します。

この基準配列の配列の向きによって、並べ替えの向きが決定されます。

行方向（縦方向）の配列を指定した場合は行方向（縦方向）に並べ替えます。

列方向（横方向）の配列を指定した場合は列方向（横方向）に並べ替えます。

この基準配列は、元データ範囲内にある必要はありません。

元データ範囲とは全く別のセル範囲または配列を指定できます。

行方向（縦方向）で並べ替える場合は、元データの行数と一致した縦の配列を指定します。

列方向（横方向）で並べ替える場合は、元データの列数と一致した横の配列を指定します。

並べ替え順序

省略可能。

並べ替えの順序、「昇順」か「降順」を指定します。

1 : 昇順

-1 : 降順

この引数を省略すると、1（昇順）で並べ替えられます。

SORTBY関数のまとめ

SORT関数、SORTBY関数と、ワークシートの並べ替えの違い

ワークシートの並べ替えは、指定範囲のデータを並べ替えてしまいますが、

SORT関数SORTBY関数は、指定範囲のデータを並べ替えて

別のセル範囲に出力します。

ワークシートの並べ替えでは、元表のデータをそのままにしておきたい

場合は表範囲をコピーしてから行う必要があります。

しかし、これらの関数を使用すれば、このコピーが不必要になります。

ただし関数では、ワークシートの並べ替えにある以下の指定はできません。

・先頭行を見出しとして使用する。

・大文字と小文字を区別する

・ふりがなを使う

つまり、SORT関数、SORTBY関数では、

・全てデータ行として扱われる

・大文字と小文字を区別しない

・ふりがなを使わない

したがって、このような並べ替えが必要な場合は、

今まで通りワークシートで並べ替えを行う必要があります。

最も単純な並べ替え

並べ替え対象内の指定列で並べ替えるだけならSORT関数が簡単です。

SORT関数

=SORT(A2:E51)

※「適当に作った個人情報」です。

1列目（A列）をキーとして行方向に昇順で並べ替えしています。

[並べ替えインデックス],[並べ替え順序],[並べ替え基準]

これらを全て省略しています。

省略せずに指定するなら、

=SORT(A2:E51,1,1,FALSE)

SORTBY関数

=SORTBY(A2:E51,A2:A51)

1列目（A列）をキーとして行方向に昇順で並べ替えしています。

[並べ替え順序]

これを省略しています。

省略せずに指定するなら、

=SORTBY(A2:E51,A2:A51,1)

引数において範囲を2度指定しなければならず、このような単純な

並べ替えであればSORT関数を使ったほうが良いでしょう。

複数キーでの並べ替え

キーを連結した作業列を作成しキーとすれば様々な

並べ替えに対応できますが、以下では作業列を作成せずに

並べ替える場合の例になります。

※作業列を使って複数キーを結合して並べ替える事自体は

決して悪いものではありません。

SORT関数で複数キー並べ替え

SORT関数単独ではできませんが、

SORT関数をネストすれば可能です。

都道府県(E列) > 性別(D列) で並べ替えます。

=SORT(SORT(A2:E51,4),5)

注意点としては、関数ネストの内側から順に実行される点になります。

上記では、性別で並べ替えた後に都道府県で並べ替えられます。

つまり、優先度の高い並べ替えを外側の関数で指定します。

場合によっては、このような使い方をすることもあるかもしれませんが、

複数キーの場合はSORTBY関数が便利でしょう。

SORTBY関数で複数キー並べ替え

都道府県(E列) > 性別(D列) で並べ替えます。

=SORTBY(A2:E51,E2:E51,1,D2:D51,1)

並べ替え順序の1は省略できるので、以下でも同じです。

=SORTBY(A2:E51,E2:E51,,D2:D51,)

ただし、最後の,カンマは省略できないので注意してください。

列方向（横方向）で並べ替え

SORT関数で列方向（横方向）並べ替え

3行目の単価で昇順に並べ替えています。

=SORT(B1:F3,3,1,TRUE)

SORTBY関数で列方向（横方向）並べ替え

=SORTBY(B1:F3,B3:F3)

範囲を2度指定しなければならないので、このような場合は

SORT関数を使ったほうが良いでしょう。

しかし、この下で説明しているように、並べ替え範囲外を

指定できるSORTBY関数ならではの使い方があります。

並べ替え範囲（配列）以外の基準で並べ替える

SORT関数は、並べ替え範囲しか並べ替えのキーが指定できませんが、

SORTBY関数は、並べ替え範囲以外をセル範囲または配列で指定できます。

列方向（横方向）を指定順序で並べ替え

=SORTBY(A2:E51,{2,5,1,4,3})

配列定数として、{2,5,1,4,3}これで指定していますが、

もちろんセル範囲でも指定できます。

=SORTBY(A3:E52,A1:E1)

さらに出力先の項目名を利用して、MATCH関数と

組み合わせるとより便利に使えます。

=SORTBY(A2:E51,MATCH(A1:E1,G1:K1,0))

ランダムに並べ替え

=SORTBY(A2:F51,RANDARRAY(ROWS(A2:A51)))

RANDARRAY関数で行数分の乱数を作り、その乱数を基に並べ替えています。

VLOOKUPの結果で並べ替え

都道府県コードを別表から取得し、取得したコードで並べ替えます。

=SORTBY(A2:F51,VLOOKUP(F2:F51,O:P,2,FALSE))

このように、他の関数（特にVLOOKUP系）で取得した順番で

並べ替える方法は、今後は頻繁に使われるようになるかもしれません。

ただし、並べ替え後の結果を見てもそれが

正しいかの確認がかなり困難になります。

可能な限り作業列を作成し、その列に他の関数の結果を出力して、

それを並べ替えのキーとして使うことをお勧めします。

列全体を範囲指定する場合

スピル関数で一番困るのが、列全体を指定しづらい事です。

単純に列全体を指定すると、SORT関数および

SORTBY関数はエラーとなってしまいます。

そこで、FILTER関数でデータが空白以外(<>"")の行だけに絞ってみると、

=SORT(FILTER(A:E,A:A<>""))

一見良さそうですが、見出し行まで並べ替えに入ってしまいます。

上記の場合の解決方法としては、データの入っている行の

判定方法を工夫することで対応できます。

データには、大抵はこのように数値しか入っていない

列が存在するはずなので、この手法は幅広く使えるはずです。

もちろん数値とは限らず、データと見出しを

区別することができる判定ならどのような条件でも構いません。

ただし、

AND関数で複数条件を指定するとエラーとなってしまうので、

単一条件だけで済むようにしてください。

　　

Excel スピルとは

2019年にOffice365のExcelに実装された

革新的な機能としてスピルがあります。

数式を入力したセルから結果があふれて

隣接したセルにも出力されるのがスピルです。

今までは数式を入れたセルにしか

結果を出せませんでしたが、スピルでは

隣接するセルにまで結果が表示されます。

この革新的なスピルとは何か、

その概要について説明します。

以下で、「従来」または「旧」と呼んでいるのは

スピルしないエクセルを指しています。

スピルって？

スピルを説明するには、まず配列についての

説明が必要になります。

まずは、従来のエクセルでの「配列数式」と

「共通部分の参照」について説明します。

従来のエクセルの挙動

配列数式

複数の値を返す数式（配列数式）は結果を表示する

全てのセルを選択して、

Ctrl + Shift + Enterで数式を一括入力することで、

{=数式}のように{}で囲まれた配列数式となります。

この配列数式はCSEと略されてます。

例えば、

B1:B3を選択し、=A1:A3を

Ctrl + Shift + Enterで入力すると、

数式は{=A1:A3}となり、B1:B3はA1:A3を

参照するようになります。

共通部分の参照

暗黙的に共通部分を参照する機能があります。

これは、同一行または同一列の

値のみが返される機能です。

B1セルに=A1:A3と入力すると、A1セルが参照されます。

B2セルに=A1:A3と入力すると、A2セルが参照されます。

B3セルに=A1:A3と入力すると、A3セルが参照されます。

A2セルに=A1:C1と入力すると、A1セルが参照されます。

B2セルに=A1:C1と入力すると、B1セルが参照されます。

C2セルに=A1:C1と入力すると、C1セルが参照されます。

スピルとは

スピル(spill)とは、こぼれる、あふれる、

と言うような意味です。

数式を入力したセルから結果があふれて

隣接したセルにも出力されるのがスピルです。

スピルは動的配列数式とも呼ばれます。

スピルは数式の結果の複数の値が

隣接するセルに自動的に出力されます。

複数の値を返す配列数式を該当セル範囲の

先頭（左上セル）に入力すると、結果の

複数の値が入力したセルからこぼれ出して

隣接するセルに出力されます。

従来なら配列数式としてCSE(Ctrl+Shift+Enter)で入力

しなければならなかったものが、

数式をEnter入力することで、該当セル範囲

（スピルする範囲）に結果が出力されます。

スピルする範囲は、数式の結果データの

縦横（行列）の大きさによって決まります。

したがって、数式が変更されれはスピル範囲も

変わりますし、数式が参照している

セル値によっても大きさが動的に変化します。

スピルによって新しく追加された関数

関数名	説明
FILTER	フィルターは定義した条件に基づいたデータ範囲です。
SORT	範囲または配列の内容を並べ替えます。
SORTBY	範囲または配列の内容を、対応する範囲または 配列の値に基づいて並べ替えます。
UNIQUE	一覧表または範囲内から重複データを削除した一覧を返します。
RANDARRAY	0から1までのランダムな数値の配列を返します。
SEQUENCE	1、2、3、4など、配列内の連続した数値の一覧を生成します。
XLOOKUP	範囲または配列を検索し、見つかった最初の一致に 対応する項目を返します。 一致が存在しない場合、XLOOKUP は最も近い (概算) 一致を返すことができます。
XMATCH	配列またはセル範囲内の項目の相対的な位置を返します。

   

Excel2021の新関数 FILTER関数 "範囲をフィルター処理"

FILTER関数は、定義した条件に基づいてデータ範囲を

フィルター処理した結果を返します。

FILTER関数はスピルで登場した新しい関数です。

FILTER関数の書式

=FILTER(配列,含む,[空の場合])

配列

必須です。

フィルター処理する元データを指定します。

セル範囲または配列です。

含む

必須です。

フィルター条件を指定します。

元データの配列の縦または横の大きさと同じ

真偽値(TRUE,FALSE)の1次元配列を指定します。

空の場合

省略可能。

フィルター結果が空の場合に表示する値を指定します。

フィルター結果が空の場合にこの引数を

省略していると、#CALC!となります。

FILTER関数使用例のサンプルデータ

使い道の広い関数です。

配列を意識して使いこなすと、かなり便利なことができます。

以下のFILTER関数使用例で使う表は以下になります。

例題にした個人情報です。50件用意しました。

以下では、上表を元にFILTER関数を使用した場合を例示しています。

FILTER関数の基本

数値でフィルター

30歳未満でフィルター

=FILTER(A2:E51,C2:C51<30)

文字でフィルター

"東京都"でフィルター

=FILTER(A2:E51,E2:E51="東京都")

フィルター結果が0件の場合

フィルター結果が0件の場合に、引数「空の場合」を

省略していると、#CALC!のエラーとなります。

引数「空の場合」を適宜指定します。

空白セルを0ではなく空白にする場合

エクセル関数全般での問題ですが、

空白セルを参照すると0になってしまいます。

=FILTER(A2:E51,B2:B51="女")

空白セルを空白にしたい場合、いろいろな方法がありますが、

&""を付け加える方法が最も簡単だと思います。

=FILTER(A2:E51,B2:B51="女")&""

複数条件のフィルター

AND条件

"女" AND "東京都"でフィルター

=FILTER(A2:E51,(B2:B51="女")*(E2:E51="東京都"))

AND条件は、*演算子を使います。

引数「含む」には、真偽値(TRUE,FALSE)の配列を指定するので、

個々の真偽値の掛け算がAND条件になります。

AND関数は使えません。

OR条件

"女" OR "東京都"でフィルター

=FILTER(A2:E51,(B2:B51="女")+(E2:E51="東京都"))

OR条件は、+演算子を使います。

引数「含む」には、真偽値(TRUE,FALSE)の配列を指定するので、

個々の真偽値の足し算がAND条件になります。

OR関数は使えません。

関数を使ってフィルター

四則演算

ここだけ少し違うデータ例になります。

C列とD列の足し算の結果でフィルターする場合。

=FILTER(A2:E51,C2:C51+D2:D51>100)

論理式として評価できる式であればどんな

四則演算でも構いません。

文字列関数

LEFT,MID,RIGHT等の文字列関数が使えます。

"京都府"と"大阪府"でフィルターしています。

=FILTER(A2:E51,RIGHT(E2:E51)="府")

日付・時刻関数

YEAR,MONTH,DAY等の日付・時刻関数が使えます。

10月生まれでフィルターしています。

=FILTER(A2:E51,MONTH(D2:D51)=10)

ただし、FILTER出力結果の日付の表示形式は

自動では設定されません。

適宜表示形式を設定してください。

関数使用時の注意

引数「含む」の計算結果が1行でもエラーを

含んでいる場合、FILTER関数全体がエラーとなります。

=FILTER(A2:E51,FIND("京",E2:E51)>0)

FIND関数は、検索値が無い場合はエラーとなるため、

上記ではFILTER関数全体がエラーとなっています。

このような場合は、IFERROR関数でくるみます。

=FILTER(A2:E51,IFERROR(FIND("京",E2:E51),0)>0)

したがって、

見出し列を範囲に含めたり列全体で

指定したりする場合は、IFERROR関数が必要になる場合が多くなります。

横（列）でフィルター

ここまで、縦のデータによってフィルターしましたが、

横（列）のデータによってフィルターすることもできます。

=FILTER(A1:E51,A1:E1="年齢")

ある特定の文字列を含む見出し列で

フィルターするといった使い方ができます。

しかし実務的には、この機能だけを単発で

使う事は少ないように思います。

表示する列を選択する

引数「含む」は真偽値(TRUE,FALSE)の配列を指定するものです。

1次元の配列であれば、縦・横どちらでも受け付けてくれます。

配列リテラルの書き方

縦の配列

{}の中に;セミコロンで区切って各要素を入れます。

{1;2;3}

1

2

3

横の配列

{}の中に,カンマで区切って各要素を入れます。

{1,2,3}

1

2

3

そこで、ここまでの例で示した「含む」のほとんどは

縦の配列であることを理解してください。

E2:E51="東京都"

これは、

{FALSE;FALSE;…;TRUE,FALSE;…}

行数分の縦の配列になります、

条件に合致した行はTRUE、それ以外はFALSEです。

そして、TRUEは1、FALSEは0として代用できます。

そこで、「含む」に直接この配列を指定してみましょう。

「含む」の行数は元配列と同じ行数に

しなければならないので、ここでは5行だけにしました。

=FILTER(A2:E6,{1;0;1;0;1})

「含む」に指定した配列の1(TRUE)の行だけが出力されました。

とはいえ、このような使い方をすることはまずないでしょう。

では、横の配列を指定してみましょう

=FILTER(A2:E6,{1,0,1,0,1})

見事に元範囲の1,3,5列だけが出力されました。

これは使えそうです。

FILTER関数の結果をFILTERすれば、

条件で絞り込んだ後に必要な列だけに絞り込めます。

FILTER関数をネストして表示する列を選択

=FILTER(FILTER(A2:E51,C2:C51<30),{1,0,1,0,0})

外側のFILTER関数に指定した配列通りの列のみ出力されました。

これを使えば、元表から欲しい列だけにすることができます。

注意点としては、「含む」に指定する

配列の大きさは、元表の列数に必ず合わせることです。

表示する列の選択を自動化する

配列{1,0,1,0,0}を自動的に作成すれば良いだけです。

FILTER関数を入れる上の行に事前に見出し文字列を入れておくことで、

配列{1,0,1,0,0}を見出し文字列から自動生成します。

=COUNTIF(G1:H1,A1:E1)

この数式はスピルして、

1

0

1

0

0

このようになりますので、これをそのま引数に指定できます。

=FILTER(FILTER(A2:E51,C2:C51<30),COUNTIF(G1:H1,A1:E1))

FILTER関数の結果を他の関数で使う

FILTER関数が返すものは配列です。

したがって、配列を受け入れる関数の引数として使う事が出来ます。

"東京都"の"男"の平均年齢を出して見ましょう。

もちろん、AVERAGEIFSで簡単に求められます。

=AVERAGEIFS(C2:C51,B2:B51,"男",E2:E51,"東京都")

あくまで、ここまでの総復習としてやってみましょう。

=AVERAGE(FILTER(FILTER(A2:E51,(B2:B51="男")*

(E2:E51="東京都")),COUNTIF(C1,A1:E1)))

この数式は解読してみてください。

ここまでに説明してきたことを組み合わせて

使っているだけです。

この数式が読めれば、FILTER関数はほぼ確実に

理解できたと言えるでしょう。

スピル導入によって、エクセルの使い方が

大きく変わろうとしています。

FILTER関数は、その中核となる関数と言えると思います。

 

Excel2021の新関数 LET関数 “変数”を使って可読性と計算速度アップ

オートコンプリートにも対応

米Microsoftは11月16日（現地時間）、「Excel」の新しい関数

「LET」を一般公開したと発表した。

今年3月から“Office Insider”でテストされていた機能です。

「LET」関数は計算結果に名前を割り当て、

それを再利用できるようにするもの。

同じような処理を何度も記述して数式が

長く読みにくくなるのを避けられるほか、

計算量の多い処理を何度も繰り返す必要がなくなるため

パフォーマンスが向上する。

プログラミング言語の“変数”によく似た概念ですが、

それが利用できる範囲

（スコープ）のはその数式の中だけです。

「LET」関数の基本的な構文は、以下の通りです。

= LET (name1, name_value1, name2, name_value2, ……calculation)

・name：変数の名前。文字で始まる必要があり、

　数式の出力や範囲構文と競合してはならない

・name_value：“name”に割り当てる値

・calculation：変数を利用した計算。

　最後の引数はこれでなければならない

変数は最大126個が利用でき、その数だけ

名前と値のペアを引数に指定する。

最後の引数は変数を利用した

計算式（calculation）である必要があるため、

引数の数はかならず奇数個になる（最短の引数は3）。

たとえば以下の売り上げデータから社員名が

“Fred”のものだけを抽出したい場合は以下のようになります。

LET を利用しない場合： =IF(ISBLANK(FILTER(A2:D8,A2:A8="Fred")),"-", FILTER(A2:D8,A2:A8="Fred")) LETを利用した場合： =LET(filterCriteria,"Fred”,filteredRange,FILTER (A2:D8,A2:A8=filterCriteria), IF(ISBLANK(filteredRange),"-",filteredRange))

“Fred”や“FILTER(A2:D8,A2:A8=……)”を繰り返し記述する

代わりにわかりやすい変数名を付けられるので、

あとでフィルタリングの条件を変更する

必要が生じても容易に式を再利用できる。

計算速度も「LET」関数を

利用した方が2倍速いという。

正式版の「LET」関数では、“name1”“name2”などで宣言した

変数を“calculation”引数でオートコンプリートできるようになった。

データ参照の競合を防ぐため

変数の名前に“.”（ドット）を利用できなくしたり、

関数名のローカライゼーションが廃止されたのも、

“Office Insider”のフィードバックを取り入れた成果です。

どの言語の「Excel」でも

「LET」という名前で関数を利用できます。

「LET」関数は「Office 365」または「Microsoft 365」の「Excel」

「Excel 2021」で利用可能。Webを含めたすべての

プラットフォームの“Current”チャネルで利用できます。

 

Excelファイルのパスワードを設定、解除する方法

顧客情報などの重要な情報を含むMicrosoft Officeファイルは、

不用意に読まれたり編集したりされないように

ファイル自体にパスワードを設定しておくとよいです。

ExcelやWordでファイルにパスワードを設定して、

パスワードを知らないと開けないようにしたり、

編集できないようにしたりする方法を紹介します。

Microsoft Officeファイルにパスワードを設定する

Microsoft Officeファイルにパスワードを設定すれば、

ファイルを開く際にパスワードの入力が求められる。

ここで正しいパスワードを入力しないと開けないようにできます。

オンラインストレージなどに保存している

Microsoft Officeファイルに設定しておけば、

万一ファイルが流出した際でも、

パスワードを入力しないと開けないため、

情報漏えいのリスクが軽減できます。

顧客情報や技術情報などの重要な情報を含む

ファイルをメールの添付やオンラインストレージを介して、

誰かに渡さなければならないこともあるでしょう。

こうした場合、パスワードを設定して、パスワードを知らないと

読んだり編集したりできないようにしておくと安全です

（パスワードは、携帯電話のSMSを使うなど、

必ず別経路を使って送ること）。

メール添付の際にZIPファイルにアーカイブしてから、

ZIPファイルにパスワードを設定する人も多いようですが

しかしこの方法だと、いちいちZIPファイルにまとめてから

パスワードを付ける必要がある上に、

内容を更新した時にはまた再設定する

必要があり、管理が面倒です。

「Microsoft Office」のファイルを単体で送付するなら、

ファイル自体にパスワードを付けて送るといいです。

パスワードを付けると、ファイルを開く際や編集する際に

パスワードが要求されるようになります。

これならばZIPファイルにする手間は不要だし、

再編集時にパスワードを付け忘れることもないです。

「Microsoft Excel（エクセル）」を例にファイルにパスワードを

付ける方法を紹介します。

WordやPowerPointでも、Excelの場合と同じ手順

（［名前を付けて保存］ダイアログの［全般オプション］）で

パスワードを設定することが可能です。

Microsoft Officeのファイルにパスワードを設定する

Microsoft Office（今回はExcelを例に）でファイルを開き、

［ファイル］タブを開き、左ペインで［名前を付けて保存］

または［コピーを保存］を選択します。

右ペインの上部にファイル名の入力ボックスと

ファイル形式が選択できる

プルダウンリストなどが表示されるはずだ。

ここの［その他のオプション］リンクをクリックします。

［名前を付けて保存］ダイアログが表示されるので、

［保存］ボタンの左側にある［ツール］の

プルダウンリストを開き、［全般オプション］を選択します。

［全般オプション］ダイアログが表示されるので、

ここで「読み取りパスワード」「書き込みパスワード」の設定を行います。

両方のパスワードを設定すると、「読み取りパスワード」で

ファイルの読み込みを制限し、

「書き込みパスワード」で編集を制限できます。

設定	説明
読み取りパスワード	パスワードを入力しないとファイルが読み込まれない
書き込みパスワード	パスワードの入力で編集可能になる。またはパスワードを入力せずに ［読み取り専用］ボタンで編集を禁止した状態で読み込み可能

パスワードの種類

パスワードを設定して、［OK］ボタンをクリックしたら、

パスワードの確認のダイアログが表示されるので、

設定したパスワードと同じものをもう一度入力します。

両方設定した場合は、「読み取りパスワード」「書き込みパスワード」の

順でパスワードを再入力します。

後は、通常通りにファイルを保存します。

これでファイルにパスワードが設定されます。

パスワードを設定する（1）

パスワードを設定したいMicrosoft Officeファイルを開き、

［ファイル］タブの［名前を付けて保存］または

［コピーを保存］を選択します。

ファイルの種類を選択するプルダウンリストの下にある

［その他のオプション］をクリックします。

パスワードを設定する（2）

［名前を付けて保存］ダイアログが表示されるので、

［ツール］をクリックして、プルダウンリストから

［全般オプション］を選択します。

パスワードを設定する（3）

「読み取りパスワード」「書き込みパスワード」の各欄に

パスワードを入力します。

閲覧できる人を限定したい場合は「読み取りパスワードを」、

編集できる人を限定したい場合は

「書き込みパスワード」をそれぞれ指定すること。

パスワードを設定する（4）

設定したパスワードに対して、

確認のためのパスワード入力が求められます。

「読み取りパスワード」と「書き込みパスワード」の

両方を設定した場合は、それぞれの入力が求められます。

パスワードを設定したら上書き保存します。

パスワード指定時に注意すべきこと

まず、十分に長くて複雑なパスワードを指定すべきです。

世の中には、パスワード付きOfficeファイルの

パスワードを解読できるソフトウェアツールも存在します。

もし単純なパスワードを指定すると、

こうしたツールであっさりと解読できてしまいます。

最低限、次の条件を全て満たす文字列を指定しましょう。

・最低でも12文字以上

・英数字だけでなく記号を混ぜる（いずれも半角）

・辞書に載っているような単語以外の文字を含める

・記念日や親しい人の名前、ペットの名前など、

　調査されるとすぐ判明しそうな個人情報は避ける

・別の用途に使ったパスワードの使い回しは避ける

ファイルを保存する際には、古いOfficeの

ファイル形式を避けるべきでし。

具体的には、拡張子が「.doc」「.xls」「.ppt」といった

Office 2003以前のファイル形式を使わない方がいいです。

パスワードを付けたときの暗号化の技術が古いせいで、

解読されやすいからです。

Office 2007以降でデフォルトの

「.docx」「.xlsx」「.pptx」といった

フォーマットの方を必ず選択しましょう。

パスワードが付けられたOfficeファイルを開く

パスワードが付けられたOfficeファイルを開くと、

パスワードの入力ダイアログが表示されます。

「読み取りパスワード」を設定した場合、

ここでパスワードを入力しないと

ファイルを開くことができません。

パスワードが付けられたファイルを開く

パスワードが設定されたMicrosoft Officeファイルを開くと、

このようにパスワードの入力が求められます。

「読み取りパスワード」が設定されている場合は、

ここで正しいパスワードを入力しないと

ファイルを開くことができません。

「書き込みパスワード」が設定されている場合

「書き込みパスワード」を設定した場合、

パスワードを入力すると編集が可能になり、

編集後に上書き保存も行えます。

ただしパスワードを入力しなくても、

［読み取り専用］ボタンをクリックすることで、

読み取り専用としてファイルを開くことができる点に

注意が必要、読み取り専用のため編集したファイルを

上書き保存することはできないが、

別名での保存は可能なので、パスワードを知らない人でも

コピーが作れてしまう。その上、そのファイルには

「書き込みパスワード」のパスワードが設定されない状態となります。

ファイルのセキュリティを保つために

パスワードを設定するのであれば、

「読み取りパスワード」も指定する方がいいです。

「書き込みパスワード」が設定されている場合（1）

「書き込みパスワード」が設定されている場合、

パスワードを入力することで編集と上書きが可能になります。

ただし、「読み取りパスワード」が未設定の場合、

パスワードを入力しなくても、［読み取り専用］ボタンを

クリックすることで、「読み取り専用」で

ファイルを開くことができます。

「書き込みパスワード」が設定されている場合（2）

［読み取り専用］ボタンをクリックすると、

「読み取り専用」でファイルが開かれ。

この状態で編集も可能だが、上書き保存はできません。

「書き込みパスワード」が設定されている場合（3）

ファイル名を変更すればファイルの保存が可能です。

その際、オリジナルのファイルに設定されていた

「書き込みパスワード」は無効になり、

パスワードなしにファイルを開くことが可能になります。

両方のパスワードが設定されている場合

両方のパスワードを設定した場合、まず

「読み取りパスワード」の入力が求められます。

ここでパスワードを入力しないか、

入力ミスをしてしまうと、ファイルは読み込まれません。

正しいパスワードを入力すると、次に

「書き込みパスワード」の入力が求められます。

ここでパスワードを入力せずに

［読み取り専用］ボタンをクリックした場合は

読み取り専用としてファイルが開き、

パスワードを入力すると編集可能な

状態でファイルが開く。

編集後に上書き保存も可能です。

設定したパスワードを解除する

設定したパスワードを解除するには、

［名前を付けて保存］－［全般オプション］ダイアログで

設定したパスワードを削除して、

空欄にして、別のファイル名を付けて保存します。

その後、オリジナルのファイルの名前を変更するか、

オリジナルのファイルを削除してから、

パスワードを削除したファイル名を元の名前に変更するとよい。

パスワードを削除して、

同じファイル名で上書き保存ができない点に注意してほしい。

設定したパスワードを解除する（1）

パスワードを入力して、ファイルを開いたら、

［名前を付けて保存］ダイアログが開き、

［ツール］のプルダウンリストから

［全般オプション］を選択します。

［全般オプション］ダイアログには、

設定済みのパスワードが「*」で示されているので、

これを全て削除して、［OK］ボタンをクリックします。

設定したパスワードを解除する（2）

ファイル名を変更してから、［保存］ボタンを

クリックして保存します。

その後、オリジナルのファイルの名前を変更するか、

オリジナルのファイルを削除してから、

パスワードを削除したファイル名を元の

名前に変更するとよい。

にほんブログ村

自動化への一歩 マクロ（VBA）を使えるようにする

Excelには、マクロ機能があり、上手に使うことで自動処理が行えます。

しかし、デフォルトではマクロを使うための

［開発］タブが表示されておらず、マクロの作成や実行が行えません。

そこで、［開発］タブを表示させ、マクロの作成と実行が行えるようにしよう。

Excelのマクロは便利ですが……

Excelでは、マクロを使うことでさまざまな自動化が行え。

しかし、デフォルトではマクロの実行などに

必要な［開発］タブが非表示となっており使うことができない。

「Microsoft Excel（エクセル）」では、マクロを使うことで、

機能を拡張したり、自動処理が行えたり、新たにワークシートで使える

関数などを作ることができるようになったり。

基本的にはプログラミングの素養が必要となるが、

マクロを使うことでExcelの世界が広がります。

セキュリティ的な問題からExcelは、標準状態で

［開発］タブが「オフ（非表示）」になっている。

これは、指示を受けて意味も分からず

マクロを動かしてしまうような操作を避けるためです。

［開発］タブの表示状態は、マクロ起動の可否を決めているのではなく、

単にマクロ関連機能にユーザーが

アクセスできるかどうかを決めているにすぎません。

マクロはかつてOfficeの文書ファイルとともにメールで送られ、

「悪意のあるプログラム」を実行させる手段としても使われ。

しかし、現在では、マクロの入った文書ファイルは別の拡張子を使い、

読み込む前に警告が表示されるなど対策が取られています。

マクロが何であるかを理解して利用するなら、

自分でマクロを使うこと自体がExcelのセキュリティを低下させ、

「悪意のあるプログラム」を動かしてしまうことにはならない。

日常的にExcelを使うなら、マクロを利用できるようにして、

新しい世界の扉を開くのも悪くないです。

マクロの利用に必要になる［開発］タブを表示させ、

マクロの入力／実行／保存を簡単に紹介します。

ただし、マクロのプログラミングに関しては解説しないので、

各自で学習して下さい。

マクロの扉を開ける

まずはExcelのマクロ開発に必要な［開発］タブを表示させ。

開発タブには、マクロの入力や編集、

実行に必要な全ての機能が入っています。

Excelで新規のブックを開き、Excelの［ファイル］タブを開き、

左ペインの［オプション］をクリックします。

すると、［Excelのオプション］ダイアログが開くので、

左ペインで［リボンのユーザー設定］を選択します。

左右に2つのリストが表示されるので、

その右側のリストにある［開発］の前にあるチェックを「オン」にして、

［OK］ボタンでダイアログを閉じる。

［開発］タブを表示する（1）

デフォルトでは［開発］タブが表示されておらず、

マクロを使うことができません。

［開発］タブを表示するには、まず［ファイル］タブを開く。

［開発］タブを表示する（2）

［ファイル］タブを開き、左ペインで［オプション］を選択する。

［開発］タブを表示する（3）

［Excelのオプション］ダイアログの［リボンのユーザー設定］で、

「開発」にチェックを入れて、［開発］タブを表示させる。

これはExcel自体の設定変更なので、

以後、［開発］タブは常に表示されるようになります。

同じ手順で［開発］のチェックを外せば、

［開発］タブは表示されなくなります。

［開発］タブを表示する（3）

Excelのリボンに［開発］タブが表示されます。

これでExcelのリボンに［開発］タブが表示されています。

なお、これは、Excel自体の設定となるため、

今後、他のブックを開いても

［開発］タブは表示されたままになります。

他人のPCを一時的に操作するような場合、

［開発］タブを「オン」にしたら、終了時に元に戻しておきましょう。

標準で「オフ」になっているように、

マクロを利用しない人にとっては、［開発］タブは不要だし、

マクロを誤って実行してしまったり、間違って操作したりすると、

見たこともないウィンドウが表示されるなど混乱が生じます。

［開発］タブを非表示に戻す手順は、

同じで、チェックを「オフ」にするだけです。

［開発］タブでまず覚えるべき2つのアイコン

［開発］タブには、多くのアイコンが並んでいますが、

取りあえず使うのは、左側のものだけです。

初めてマクロに触れるような場合なら、

必要なのは左側にある「コード」部分の

［Visual Basic］と［マクロ］のアイコンに限られます。

［開発］タブでよく使うのは左側の2つのアイコン

［開発］タブには、さまざまなボタンが並びますが、

最初の段階で利用するのは左端の2つだけです。

［Visual Basic］アイコンは、マクロ（VBAプログラム）を入力したり、

編集したりする場合に使う。

このボタンを押すと、「Visual Basic Editor（VBE）」ウィンドウが開く。

「Visual Basic Editor（VBE）」ウィンドウの構成

VBEは、マクロを入力／編集／デバッグするときに利用します。

ウィンドウは大きく3つに分かれていて、

左側がマクロの編集対象を指定するための

「プロジェクトエクスプローラー」、その下が

「プロパティウィンドウ」、右側がエディタとなり

「コードウィンドウ」です。

［マクロ］アイコンは、マクロを実行したり、

デバッグしたりする場合に利用します。

クリックすると［マクロ］ダイアログが開きます。

［マクロ］ダイアログの構成

［マクロ］ダイアログは、マクロの「実行」

「デバッグ」の開始が指示できます。

また、「編集」や「作成」の指示が行えますが、

作業はVBE側で行います。

なおExcelは、ユーザーの行った操作をマクロとして記録し、

後から再実行したり、編集したりする

機能を持ちますが、ここでは紹介しません。

マクロを作成、実行して保存

マクロを作るには、［Visual Basic］アイコンを

クリックしてVBEを開きます。

ウィンドウの左側は、現在開いているブックなどの

オブジェクトを表し、右側にプログラム（マクロ）を入力する。

もちろん、マクロはVBAの文法に沿ったものを入力する必要があります。

ここでは、マクロを

作成」「実行」「保存」する手順だけを紹介します。

新規にマクロを作る場合、［マクロ］ダイアログから始める方法と、

VBEから始める方法の2つがあり。

手順としては前者の方が簡単で初心者向きです。

ただし、マクロ作成のたびに、その管理単位である

「標準モジュール」が増えていって混乱を招きやすい。

最初のうちは［マクロ］ダイアログから作成しても構わないですが、

慣れたら、後者の手順でVBEを直接起動して

マクロ作成を開始するようにしよう。

［マクロ］ダイアログからマクロ作成を始める

［開発］タブで［マクロ］ボタンをクリックすると、

［マクロ］ダイアログが現れ。

その［マクロ名］に、作成したいマクロの名前を入れ、

［作成］ボタンを押すとVBEが起動します。

例えば、マクロ名として「shioda_test」と入力し、

［作成］ボタンを押せば、VBEのコードウィンドウには

同名のサブルーチンが作成された状態となり。

ただし、作られるのは

サブルーチンの枠だけで、中身は何もないです。

［マクロ］ダイアログからマクロを作成する（1）

［マクロ］ダイアログからマクロ

（VBAサブルーチン）を作成するには、

「マクロ名」欄に作成したいマクロの名前に入力し、

作成ボタンを押します。

［マクロ］ダイアログからマクロを作成する（2）

VBEが起動し、VBE側に自動的に標準モジュールが作られ、

入力した名前のマクロ（サブルーチン）の

枠だけが入力された状態となります。

［マクロ］ダイアログからマクロを作成する（3）

サブルーチンの枠内にマクロの本体を入力します。

VBEからマクロ作成を始める

VBEからマクロ作成を行う場合には、VBEを起動後、

［挿入］メニューの［標準モジュール］を選択します。

ブックを表すツリーの下に［標準モジュール］が作られ、

その下に「Module1」が置かれます。

VBEでは、左側のツリー内のオブジェクトを

ダブルクリックで選択すると、右側の領域は、

そのオブジェクトのマクロ編集状態となります。

VBEからマクロを作成する（1）

VBE側でマクロを作成する場合、

VBEを起動して［挿入］－［標準モジュール］を選択して、

標準モジュールをプロジェクト（ブック）に挿入します。

VBEからマクロを作成する（2）

プロジェクトに「Module1」が作成され。

コードウィンドウには何も入力されていない状態です。

VBEからマクロを作成する（3）

コードウィンドウでマクロを入力する。

マクロは標準モジュールに記述するのが基本

マクロは、特定のブックやシートに

関連するものとしても作成でき。一方、標準モジュールに置くことで、

ブック全体で共通して利用することも可能。

まずは、標準モジュールの「Module1」にマクロを置くため、

ここを編集状態とし。

なお、［挿入］メニューから「標準モジュール」を選択した直後は、

作成された「Module1」が自動的に選択状態になっています。

慣れないうちは、ブックやシートに

マクロを書いてしまうことがあります。

［コード］ウィンドウにマクロを入力する前に

プロジェクトエクスプローラーで

正しいものを選んでいるか確認します。

マクロを記述する

この時点では、まだ何も作っていないので、

「Module1」は空白のままか、

あるいは中身のないサブルーチン

（プログラムの中でよく利用する作業などを1つにまとめたもの）が

記述されているだけです。

ここにサブルーチンや関数の実体を書き込んでいく。

記述から保存までを実際に試してみるなら、

右側の領域に以下のテキストを入力する。

非常に簡単ではあるがこれがマクロ（VBAのプログラム）です。

Sub shioda_test() 　 MsgBox ("Hello World") End Sub

サブルーチンのサンプル

このマクロには「shioda_test」という名前がついてます。

それは1行目に記述されている。

2行目はメッセージボックスを表示するMsgBox関数。

3行目はサブルーチンがここで終わることを示すもので、

1行目の「Sub」と対になっています。

作成したマクロを実行する

このマクロを実行するには、Excelの［開発］タブにある

［マクロ］ボタンを使う（VBEは閉じてしまっても構いません）。

［マクロ］ダイアログが表示され、現在実行可能なマクロの一覧が

「マクロ名」に並ぶ。

ここでは、もちろん、先ほど入力したマクロ

（「shioda_test」）が表示されています。

これをリストから選択して「実行」ボタンを押せば、

マクロが実行され、「Hello World」と

書かれたメッセージボックスが表示されます。

作成したマクロを実行する（1）

マクロを実行するには、［マクロ］ダイアログを開き、

リストからマクロを選択して、［実行］ボタンを押します。

VBEからも実行は可能、マクロの多くは、ワークシートや

セルに対して動作するため、

Excelのウィンドウを選択した状態で

実行しなければエラーになることがある。

そのため［マクロ］ダイアログを使って起動する方がよい。

作成したマクロを実行する（2）

マクロが実行される。この場合は、

「Hello World」と書かれたメッセージボックスが表示されます。

マクロを保存する方法

最後に、マクロを保存する方法を紹介します。

マクロは、ブックの中に記録されるため、

ブックごと保存しなければならない。

しかし、マクロを含むブックは、

「マクロ有効ブック」という形式でしか保存できず、

通常のブック形式では、マクロを含めて保存できません。

なお、VBEにも［保存］ボタンがありますが、

Excel側で保存しても、VBE側で保存しても結果的には、

ブックの保存なので同じ動作となります。

Excelの［ファイル］タブからブックを保存する。

このとき、ファイル形式から［マクロ有効ブック］を選択します。

この場合、拡張子は「.xlsm」になり。

その他に関しては、通常のブック保存と変わりないです。

マクロを保存する（1）

マクロは、ブックの中に記録されるため、

保存はブックの保存で行います。

通常のブックの保存と同様、［ファイル］タブで

［名前を付けて保存］を選択します。

マクロを保存する（2）

ブックを保存する際、保存形式として

［Excelマクロ有効ブック］を選択しないと、

ブック内にマクロを保存することができません。

マクロを入力したブックをマクロ有効ブック以外の

形式で保存しようとすると、警告が表示されます。

これは、「このまま保存するとマクロがブックに含まれない」という

意味であり、ブックにマクロがあることも示します。

マクロを含むExcelブックの保存時に

表示されることがある警告メッセージ

マクロがあるブックを通常のExcelブック形式で

保存しようとすると、メッセージボックスが表示され、

マクロが保存されないことを通知します。

Excelでの作業時間が多いようなら、

マクロについて学んでおくと省力化が可能になり。

ただし、インターネットなどから中身の分からない

マクロを含むExcelブックなどを

ダウンロードして動かすなどは、危険な行為といえます。

自分で作ったものか、プログラムソースが

理解できるものに限定すべきでしょう。

時々、マクロのために長時間プログラミングした結果、

省力化という目的から外れてしまうことがあります。

もちろん、本人が好きならそれでいいのですが、

まずは多少の距離感を持って

付き合う方がいいと思います。

にほんブログ村

発生する「循環参照」とその対策

Excelでセルに何らかの数式を設定していると、

「循環参照」というメッセージが

表示されることがあります。

この「循環参照」とは何なのか対策と解消方法を紹介します。

意外と簡単に起きる「循環参照」

多くのExcel関係の文書や記事には、

「循環参照エラー」と表記されることが多い。

しかし循環参照はエラーではなく、

れっきとしたExcelの計算方法の1つです。

現在では使う意味が薄れてきており、間違いの元になりやすいため、

循環参照が起こるとメッセージを表示するようになっています。

SUM関数の引数に自分自身のセルを含むと

「循環参照」というメッセージが表示される。

この「循環参照」は、「Microsoft Excel（エクセル）」で

比較的よく見かけるメッセージの1つ。

簡単な式であっても、セル範囲指定を間違うなどすると、

表示されてしまうからです。

自由にセル範囲を指定できるExcelの数式では、

循環参照が発生することを防ぐことはできません。

循環参照は、「エラー」ではない。

もともと循環参照は、Excelで関数などから構成される

数式では計算できない「繰り返し」計算を行うために

利用されてきた方法だからです。

例えば、1から100までの和を求める場合、

反復回数を100回と設定しておき、セル「A2」に「=A1+A2」という

数式を入れ、セル「A1」に「=A1+1」という数式を入力すると、

セル「A2」に計算結果の「5050」が表示されます。

Excelの関数は初期の頃に比べると充実し、

VBAを使って繰り返し計算を行う関数を定義する

ことも可能になったため、

現在では循環参照を使う意味は薄れています。

過去に作られたExcelのシートや数式の互換性のため、

循環参照を完全に禁止することはできず、

現状では、循環参照に対する設定（後述）と、

発生時にメッセージを表示するという妥協点に落ち着いています。

新規作成時には、メッセージで循環参照の対応を促し、

過去に作成された循環参照による繰り返し計算は、

設定で有効にしておくことができます。

循環参照が発生しても、常にメッセージが表示されるとは限らない。

シート内に既に循環参照があった場合、

これをコピーしたときなど、循環参照が発生したことを示す

メッセージが出ないことがある。

今回は、こうした循環参照への対応方法を紹介します。

循環参照とは

循環参照は、簡単にいうと、数式で自分が書き込まれている

セルを参照することで発生します。

例えば、セル「B3」に「=SUM(B1:B3)」のような

自分自身を参照するような数式があれば、

循環参照が発生します。

「循環参照」とは

数式で自分が書き込まれているセルを参照すると「循環参照」となる。

画面では、SUM関数で自身を含むセルが参照されて。

循環参照が発生すると、このようにメッセージが表示されます。

循環参照が起こったセルの再計算は停止し、

セルの値は「0（ゼロ）」になることがほとんど。

多くの場合、これで数式に問題があることに気が付く。

しかし循環参照は、他のセルからの参照で発生することもあり、

必ずしも結果が「0」になるとは限らず、

循環参照が発生する前の値が残ることがあります。こ

のため一見ちゃんと計算が行われているように見えることがあります。

「反復計算を行う」を有効にすると循環参照のメッセージが表示されない

意図的に循環参照を使って繰り返し計算を行うことも可能。

［ファイル］タブの［オプション］を選択して、

［Excelのオプション］ダイアログを表示し、

左ペインで［数式］を選び、右ペインの「計算方法の設定」欄にある

「反復計算を行う」を「オン」にして、

「最大反復回数」を設定することで、

Excelは循環参照を使う繰り返し計算が行えるようになります。

この設定を行うと、循環参照が発生したことを示す

メッセージは表示されなくなるので注意して下さい。

反復計算を有効にする（1）

［ファイル］タブを開き、［オプション］を選択する。

反復計算を有効にする（2）

［Excelのオプション］ダイアログの左ペインで［数式］を選択し、

右ペインの「反復計算を行う」にチェックを入れて、

「最大反復回数」で、計算の繰り返し回数を指定します。

ここをチェックすると、循環参照のメッセージが

表示されなくなるので注意して下さい。

一部の関数は自分を含むセルを参照しても循環参照にならない

一部の関数に関しては、自分を含むセル参照を引数にしても、

循環参照にならないものがある。

循環参照は、式の結果である「値」を参照しているときのみ発生する。

例えば、セルの数式を参照する「FORMULATEXT」関数です。

この関数は、数式そのものをテキストとして

返すものであるため、循環参照にならない。

具体的には、2つのセルがあり、セル「B2」に「FORMULATEXT」関数

（数式を文字列として返す関数）を使い、

セル「B3」の数式を文字列として、

その文字列の長さを「LEN」関数で求めている。

セル「B3」は、「SUM」関数でセル「B2」の値を合計しています。

循環参照にならない関数（1）

循環参照を行っても「循環参照」として認識されない関数もある。

例えば、「FORMULATEXT」関数は、指定したセルの数式を

文字列として返すものであるため、循環参照にならない。

このようにセル「B2」は、セル「B3」を参照していますが、

「FORMULATEXT」関数を使っているため、循環参照にならない。

しかし、セル「B2」を書き換えて「FORMULATEXT」関数を外すと

循環参照が発生する。

こうした循環参照にならない関数には、

「ISFOMULA」関数（セルが数式かどうか）、

「ROW」関数（セルの行番号を返す）、

「COLUMN」関数（セルの列番号を返す）などがあります。

循環参照にならない関数（2）

「FORMULATEXT」関数を外してしまうと、循環参照になってしまう。

循環参照が発生した場合の表示

ブック内で循環参照が発生すると、デフォルトで

Excelウィンドウ下部にあるステータスバーに

循環参照が発生していることを示す

メッセージが表示されるようになっています。

入力時に循環参照が発生しましたが、

後で直すつもりでメッセージボックスを閉じても、

ここに表示が出ているため循環参照が

存在していることが常に把握でき。

なお、この表示は、ステータスバーの設定では、

「オフ」にすることはできません。

ステータスバーに「循環参照」が表示される（1）

循環参照が起こると、ステータスバーに

「循環参照」と表示されます。

このとき、表示中のシートに循環参照がある場合、

そのセルアドレスが表示されます。

ステータスバーに「循環参照」が表示される（2）

循環参照が起こるとステータスバーに「循環参照」と表示され。

表示中のシートに循環参照がないと

セルアドレスが表示されません。

ステータスバーに「循環参照」と表示されているとき、

その右側にセルアドレスが表示されることがあり。

これは、アクティブになっているExcelウィンドウで

開いているシートに循環参照が起こっていることを示します。

開いている複数のブックのうち、

現在アクティブなウィンドウに表示されていない

ブックやシートに循環参照が発生している場合は

セルアドレスが表示されません。

循環参照で間違ったセルアドレスが表示されることもある

このステータスバーの表示は、基本的には正しいが

他のシートで循環参照が発生しているとき、

表示中のシートに循環参照にならない関数などが使われていると、

ここに間違ったセルアドレスが表示されることがあり。

この不具合は、かなり昔からあるもので、

最新のMicrosoft 365版Excelでも発生します。

循環参照ではないのにステータスバーに

「循環参照」と表示されるケース（1）

循環参照が含まれるブックで、「FORMULATEXT」関数のような

循環参照にならない関数を別のシートで使うと、

ステータスバーの循環参照のセルアドレスが

間違って表示されることがあります。

循環参照ではないのにステータスバーに

「循環参照」と表示されるケース（2）

別のシート（Sheet2）で循環参照が発生しています。

この表示の影響で、前画面の

「循環参照」の誤表示が行われるようです。

循環参照のセルに矢印が表示されるとき

Excelのバージョンによっては、循環参照が発生したとき、

セルに矢印が表示されることがあります。

これは、「トレース矢印」と呼ばれ、

数式の参照関係を示すものです。

Excelでは条件により、自動的に表示されることがありますが、

必ずしも全ての循環参照に表示されるわけではないです。

トレース矢印は、［数式］タブにある「

ワークシート分析」グループの［トレース矢印の削除］

コマンドで消すことが可能です。

なお、循環参照で自動的に表示されたトレース矢印は、

循環参照が解消されると自動的に消えます。

循環参照の発見方法

循環参照を解決する確実な方法は、［数式］タブにある

「ワークシート分析」グループの

［エラーチェック］－［循環参照］を使って、

循環参照が発生しているセルを確認する方法です。

この方法では、確実に循環参照が

発生しているセルを確実に選択できます。

複数箇所で循環参照が発生している場合、

1カ所を解決した後、ここに次の循環参照の位置が表示され。

つまりエラーチェックで発生位置を特定し、

数式などの書き換えを行い、ステータスバーから循環参照の

表示が消えるまで作業を繰り返す必要があります。

循環参照が発生しているセルを確実に確認する

循環参照を確実に修正するには、

［数式］タブにある［エラーチェック］を使う。

ここにある［循環参照］には、循環参照が発生している

セルアドレスが表示され、クリックで

該当のセルを確認、選択が行えます。

循環参照があるブックが開いていれば、

他のブックウィンドウからでも利用できます。

同時に複数のセルが表示されることがあり。

これらは、1つの循環参照に関連する複数のセルを表す。

ここに表示されているセルのうち1つの数式を見ても、

循環参照しているように見えない場合、

別のセルを調べてみる必要があります。

そのようなときには、数式の参照先や参照元のセルへ

移動できるショートカットキーが役に立ちます。

現在のセルに入力されている数式が参照しているセルには、

［Ctrl］＋［[］キーで、逆に現在のセルを参照している

数式を持つセルには［Ctrl］＋［]］キーでそれぞれ移動できます。

循環参照の解消を繰り返し、ステータスバーの

「循環参照」の表示が消えれば、開いているブック内には、

循環参照がなくなった状態です。

このとき、［エラーチェック］－［循環参照］は

グレーアウトした状態に戻ります。

循環参照は、かつて1つの計算方法であったため、

エラーと表示されるものの、数式エラーなどと違い、

セル表示が変わらないなど、存在が許容されています。

また、入力時にはメッセージボックスによる警告だけなので、

無意識に［OK］ボタンをクリックしてしまい、

後から循環参照に気が付くこともあり。

こうした循環参照の解消には、

［数式］タブにある［エラーチェック］が確実な方法です。

にほんブログ村