Access。クロス集計クエリで列の表示順序を希望通りにするにはどうするの【Cross tabulation】

Access。クロス集計クエリで列の表示順序を希望通りにするにはどうするの

＜Access：クロス集計クエリ＞

作成タブの「クエリウィザード」などで作成することで、テーブルから、簡単にクロス集計を算出したクロス集計クエリをつくることができます。

特に、クエリウィザードをつかえば、ウィザード形式で設定していくので、とても簡単です。

ただ、完成したクエリをデータシートビューで見たときに、列の順番を変更したいとか、決まった店舗順にしたいと思った場合、どのようにしたら、希望する店舗順にすることができるのでしょうか。

例えば、横浜と渋谷を入れ替えるだけならば、Excelのピボットテーブルのように、列を選択して、ドラッグすることで順序を変更することができます。

完全で手作業レベルです。

ただし、列数が多く、また、移動箇所も多い場合、ドラッグ作業で対応するとなれば、かなり面倒な作業となってしまいます。

そこで、プロパティシートの「クエリ見出し」を使うことで順序を変更することができます。

デザインビューに変更します。

プロパティシートを表示したら、「列見出し」のフィールドをクリックします。

クエリ列見出しに、並べたい順番を入力します。

「"新宿","渋谷","品川","横浜"」

では、保存して、データシートビューに変更して、クロス集計クエリを確認してみましょう。

クエリ列見出しに設定した順番で、フィールドが並んでいることが確認できます。

実務では、順序をExcelなどでつくっておいて、クエリ列見出しに、コピーアンドペーストするといいでしょう。

プロパティシートには、様々なものが用意されていますので、調べてみると、意外と問題を解決する方法が見つかるかもしれませんね。

Excel。今週のFacebookページの投稿 2022/4/18-2022/4/24【Trivia】

Excel。今週のFacebookページの投稿　2022/4/18-2022/4/24

＜Facebookページ＞

Facebookページで【書いてみた】Excelの豆知識（Trivia）です。

4月18日

Excel。CHISQ.INV関数。

読み方は、カイスクウェア・インバースで、カイ二乗分布の下側確率から確率変数を算出します

4月19日

Excel。CHISQ.INV.RT関数。

読み方は、カイスクウェア・インバース・ライトテールで、上側累積確率からカイ二乗分布のパーセント点の値を逆算します

4月20日

Excel。CHISQ.TEST関数。

読み方は、カイスクウェア・テストで、カイ二乗検定の上側確率を算出します

4月21日

Excel。CHITEST関数。

読み方は、カイテストで、カイ二乗検定の上側確率を算出します

4月22日

Excel。CHOOSE関数。

読み方は、チューズで、引数リストの何番目かの値を抽出します

4月23日

Excel。CLEAN関数。

読み方は、クリーンで、文字列中に含まれる制御文字を取り除くします

4月24日

Excel。CODE関数。

読み方は、コードで、文字を文字コードに変換するします

Excel。BIN2HEX関数は、2進数を16進数に変換する関数です【BIN2HEX】

Excel。BIN2HEX関数は、2進数を16進数に変換する関数です

＜関数辞典：BIN2HEX関数＞

BIN2HEX関数

読み方： ビントゥヘックス

読み方： バイナリ・トゥ・ヘキサデジマル

分類： エンジニアリング 

BIN2HEX(数値,[桁数])

2進数を16進数に変換する　

Excel。TAKE関数など14個の新しい関数が追加されました。【New_function】

Excel。TAKE関数など14個の新しい関数が追加されました。

＜新しく追加された関数＞

なんとExcelの関数に新しく14個も関数がOffice Insiderに追加されました。

ビルドとしては、バージョン2203：ベータチャネルからです。

検索/行列の関数が多く追加されているのが特長ですね。

これで、Excelの関数は、500台に突入しました。513種類……。

どんな関数なのかをアルファベット順で紹介します。

なお、引数は、英語表記のままです。

CHOOSECOLS関数

読み方： チューズコルズ　・　チューズカラムズ

分類： 検索/行列 

CHOOSECOLS(array,col_num1,[col_num2],…)

指定された列だけを含む配列を返します　

CHOOSEROWS関数

読み方： チューズロウズ　　

分類： 検索/行列 

CHOOSEROWS(array,row_num1,[row_num2],…)

指定された行だけを含む配列を返します　

DROP関数

読み方： ドロップ　　

分類： 検索/行列 

DROP(array,rows,[columns])

配列から連続する指定された数の連続する行または列を除外する　

EXPAND関数

読み方： エクスパンド　　

分類： 検索/行列 

EXPAND(array,rows,[columns],[pad_with])

配列を指定した行数列数だけ拡大します　

HSTACK関数

読み方： エイチスタック　　

分類： 検索/行列 

HSTACK(array1,[array3],…)

水平方向に配列を2つの配列に積み重ね（スタック）する　

TAKE関数

読み方： テイク　　

分類： 検索/行列 

TAKE(array,rows,[columns])

配列から連続する指定された数の連続する行または列を返す　

TEXTAFTER関数

読み方： テキストアフター　　

分類： 文字列操作 

TEXTAFTER(text,delimiter,[instance_num],[ignore_case])

文字を区切った後のテキストを返す　

TEXTBEFORE関数

読み方： テキストビフォー　　

分類： 文字列操作 

TEXTBEFORE(text,delimiter,[instance_num],[ignore_case])

文字を区切る前のテキストを返す　

TEXTSPLIT関数

読み方： テキストスピリット　　

分類： 文字列操作 

TEXTSPLIT(text,col_delimiter,row_delimiter,ignore_empty,pat_with)

区切り記号を使用してテキストを行または列に分割する　

TOCOL関数

読み方： トゥカル　・　トゥカラム

分類： 検索/行列 

TOCOL(array,[ignore],[scan_by_column])

配列を1つの列として返します　

TOROW関数

読み方： トゥロウ　　

分類： 検索/行列 

TOROW(array,[ignore],[scan_by_column])

配列を1つの行として返します　

VSTACK関数

読み方： ブイスタック　　

分類： 検索/行列 

VSTACK(array1,[array2],…)

垂直方向に配列を1つの配列に積み重ね（スタック）する　

WRAPCOLS関数

読み方： ラップコルズ　　ラップカラムズ

分類： 検索/行列 

WRAPCOLS(vector,wrap_count,[pad_with])

指定した数の値の後に列で折り返（ラップ）します　

WRAPROWS関数

読み方： ラップロウズ　　

分類： 検索/行列 

WRAPROWS(vector,wrap_count,[pad_with])

指定した数の値の後に行で折り返（ラップ）します　

Excel。CHOOSECOLS関数は好きな列データを抽出できるので便利です。【CHOOSECOLS】

Excel。CHOOSECOLS関数は好きな列データを抽出できるので便利です。

＜CHOOSECOLS関数＞

2022年3月に追加された、「CHOOSECOLS関数」。

なんで今までいなかったの？といえるほど、便利な関数が追加されました。

次のデータで説明します。

A1:G6には、店舗別の年度別になった来店客数と売上高のデータがあります。

このデータから、売上高の列だけをA8:D13のように、まとめたい場合、イチイチCtrlキーをつかい範囲選択して、コピーするという方法を取りますが、データ量が多かったり列数が多かったりすれば、とても面倒な処理になります。

このためだけに、Excel VBAでプログラムをつくるというのも面倒です。

そこで、新しく登場した「CHOOSECOLS関数」をつかえば、手早く抽出したデータの表をつくることができます。

A8には、次の数式を設定します。

=CHOOSECOLS(A1:G6,1,3,5,7)

あとは、スピル機能によって、勝手に数式がコピーされますので、一列置きにデータを抽出することができました。

CHOOSECOLS関数の引数は、

CHOOSECOLS(array,col_num1,[col_num2],…)

arrayは、配列＝範囲で、col_numは範囲の列番号ということで、とてもわかりやすい関数ですね。

たぶん、読み方は、チューズカラムズ　だと思いますが…。

なお、この関数は、現在、Office InsiderのMicorsoft365。

バージョン2203ベータチャネル版にしか、追加されていません。

おいおい、Micorsoft365のExcelにも追加されること思われます。

Excel。BIN2DEC関数は、2進数を10進数に変換します。【BIN2DEC】

Excel。BIN2DEC関数は、2進数を10進数に変換します。

＜関数辞典：BIN2DEC関数＞

BIN2DEC関数

読み方： ビントゥデック　　

             バイナリ・トゥ・デジマル

分類： エンジニアリング 

BIN2DEC(数値)

2進数を10進数に変換する

Excel。折れ線グラフに外れ値がわかるように最高・最低の2本の線を描きたい【Line graph】

Excel。折れ線グラフに外れ値がわかるように最高・最低の2本の線を描きたい

＜折れ線グラフ＞

天気予報でお馴染みの気温の折れ線グラフ。

平均最高気温と平均最低気温の線を加えることで、平均値よりも外れているのか、次のグラフのように線を加えたグラフをつくるにはどうしたらいいのでしょうか？

 

今回は、線を描くのではなく、プロットエリアの両端。

全体に描かれるようにします。

このグラフを描くために、次の表を用意しました。

平均最低気温と平均最高気温のデータがないと、当然、線を描くことはできません。

A1:D6を範囲選択して、挿入タブの「すべてのグラフを表示」をクリックします。

グラフの挿入ダイアログボックスが表示されますので、すべてのグラフタブにある「組み合わせ」をクリックします。

気温データは、グラフの種類を「マーカー付き折れ線グラフ」

平均最高気温と平均最低気温は、グラフの種類を「折れ線」として、第2軸にチェックマークをいれます。

プロットエリアの両端から線を描かなければ、第2軸にする必要はありません。

この第2軸にするのがポイントです。

OKボタンをクリックすると、グラフが挿入されます。

グラフタイトルは、大きく見せるために、削除しています。

第1軸（縦軸）と第2軸（縦軸）のフォントサイズを変更したい場合は、この時点で変更することをお勧めします。なお、同じフォントサイズにします。

第2軸に描いてある2本の線を両端から描くために、第2軸（横軸）を表示します。

グラフのデザインタブの「グラフ要素を追加」にある軸から「第2横軸」をクリックします。

グラフ上部に、第2横軸が表示されます。

表示された、第2横軸を選択して、書式タブの「選択対象の書式設定」をクリックして、軸の書式設定作業ウィンドウを表示します。

軸のオプションにある軸位置を「目盛」に変更することで、プロットエリア全体に線を延ばすことができます。

あとは、表示している第2横軸と第2縦軸が不要なので、削除したいのですが、第2縦軸は、DELキーで削除することができます。

なお、第2横軸は削除すると、せっかくプロットエリア全体に延ばした線が元に戻ってしまいます。

そこで、軸の書式設定作業ウィンドウで非表示にしていきます。

ラベルの「ラベルの位置」を「なし」に変更します。

それと、線の色を「なし」に変更します。

 

あとは、わかりやすいように、グラフエリアやプロットエリアを塗りつぶしたりすれば、完成です。

ちょっとした、アイディアで、わかりやすいグラフを作ることができますので、色々試してみるといいかもしれませんね。

Excel。COUNT関数は数値の件数しか算出できません。【Function：COUNT】

Excel。COUNT関数は数値の件数しか算出できません。

＜COUNT関数＞

オートSUMボタンにある。「数値の個数」。

COUNT関数をつかうことで、簡単に件数を算出することができるわけですが、ちょっと注意が必要なのが、数値しか数えてくれないということです。

B7の数式は、

=COUNT(B2:B6)

と設定してあります。

これを、D7までオートフィルで数式をコピーしています。

B列は、数値なので、問題なく算出できますが、C列は、文字なので、数えることができないので「0（ゼロ）」と算出されます。

D列は、日付ですが、Excelでは、シリアル値という「数値」で管理しているので、件数を算出することができます。

このように、オートSUMボタンの「数値の個数」は、数値のみが対象ですので、注意しましょう。

では、COUNT関数の基本情報を確認しておきましょう。

COUNT関数の読み方は「カウント」です。

所属は、「統計」です。

COUNT関数の引数は、

COUNT(値1,[値2],…)

Excel。BETA.INV関数は、累積確率からベータ分布の値を逆算します。【BETA.INV】

Excel。BETA.INV関数は、累積確率からベータ分布の値を逆算します。

＜関数辞典：BETA.INV関数＞

BETA.INV関数

読み方： ベータ・インバース　　

分類： 統計 

BETA.INV(確率,α,β,[A],[B])

累積確率からベータ分布の値を逆算する　

Excel。2つの数値に関連性があるのかないのか、手早く知りたい【Relevance】

Excel。2つの数値に関連性があるのかないのか、手早く知りたい

＜CORREL関数・PEARSON関数＞

日々の売上データをただ作成するのでは、もったいないかもしれません。

Excelには、あまり使わない関数でも、簡単に色々算出することができる関数が多くあります。

例えば次のような売上表。

B列には、店舗ごとの来店客数、C列には、店舗ごとの売上高が入力されています。

「売上金額がいくらだね」とか「結構売上いったね」なども、大切な所感ではありますが、来店客数と売上高って何らかの関係性ってあるのでしょうか。

そこで、登場する関数が2つ。

ひとつは、CORREL関数。

もう一つが、PEARSON関数です。

どちらも、相関係数を算出することで、相関関係の強さを把握することができる関数です。

これらの関数をつかうと、簡単に相関関係がどうなのかを数値として把握することはできます。

ただし、算出された値は、相関係数なので、相関関係の強さを数値化したものです。

因果関係とは限りませんが、関連性が強いのか、弱いのかを知ることは、Excelの関数をつかうことで、容易にできるわけです。

まずは、それぞれの関数の数式を確認してみましょう。

F2の数式は、

=CORREL(B2:B8,C2:C8)

F3の数式は、

=PEARSON(B2:B8,C2:C8)

とても単純な構造の数式ではありますが、相関関係の強さを知ることができるわけですから、使ってみると日頃見えていなかったものが見えてくるかもしれませんね。

算出された相関係数は「0.95…」。

これは、どう判断するのかというと、相関係数が、「1」に近ければ正の相関が強く、「-1」に近ければ、負の相関が強いと判断することができます。

そして、「0」に近ければ、相関関係は薄いということもわかるわけです。

「0.95…」なので、正の相関が強いことがわかりました。

つまり、来店客数が増えれば、売上高は上がる傾向にあるといえるわけですね。

改めてですが、相関関係が強いということがわかったにすぎません。

疑似相関ということもあるので、これ以外にも様々なデータから読み解くことも大切ですね。

Excel。新機能のスピルが邪魔なので、スピルにならない数式をつくりたい【Spill】

Excel。新機能のスピルが邪魔なので、スピルにならない数式をつくりたい

＜Spillと「@」・CELL関数＞

Excelに追加された新機能の「Spill」（スピル）。

確かに、便利な機能ではあるのですが、勝手に「Spill」（スピル）を使用した数式を変わってしまって、算出したいのに、邪魔をされることが多々あります。

例えば、次のようなケース。

BからD列の列幅を知りたいので、CELL関数をつかった数式を今まで通りにつくってみました。

=CELL("width",B1)

ところが、希望していないにもかかわらず、勝手に「Spill」（スピル）が、追加されてしまいました。

B6の数式をオートフィルで横方向にコピーしても、エラーが出るなど、せっかくの便利な新機能も迷惑でしかありません。

では、どのようにしたら、「Spill」（スピル）にならず、今まで通りの数式を作ることができるのでしょうか。

実は、「=（イコール）」のあとに「@（アットマーク）」を追加するだけで、今まで通りに算出することができます。

 

B6の数式は、

=@CELL("width",B1)

とCELL関数の前に「@（アットマーク）」を追加しました。

あとは、オートフィルで数式をコピーすれば、今まで通りに算出することができました。

このように、「Spill」（スピル）で邪魔された場合は、「@（アットマーク）」で対応することができそうなので、イライラが少し緩和できるかもしれませんね。

Excel。BETAINV関数は、累積確率からベータ分布の値を逆算します。【BETAINV】

Excel。BETAINV関数は、累積確率からベータ分布の値を逆算します。

＜関数辞典：BETAINV関数＞

BETAINV関数

読み方： ベータインバース　　

分類： 互換性 

BETAINV(確率,α,β,[A],[B])

累積確率からベータ分布の値を逆算します。

Excel。わかりやすいように、文字が入力されている行全体を塗りつぶしたい【Paint the line】

Excel。わかりやすいように、文字が入力されている行全体を塗りつぶしたい

＜条件付き書式＋IFERROR関数＞

列内のデータで数値ではなく、文字が入力されている場合、その行全体を手早く塗りつぶすにはどのようにしたらいいのでしょうか

次の表を用意しました。

B列の数値が入力されているデータはそのままで、文字が入力されているデータがあれば、その行全体を塗りつぶしたいわけです。

文字が同じならば、その文字をつかって、条件付き書式のルールを作ることもできますが、様々な文字の場合、Excelでは、判断する関数が用意されていません。

数値を除外したくても、例えば、1～10と決まった範囲ならば、それ以外ということも出来るかもしれませんが、それもできません。

ただ、Excelにおいて、文字か数値かを判断するには、それほど、難しくはありません。

では、条件付き書式を設定していきます。

A2:B6を範囲選択して、ホームタブの条件付き書式にある、「新しいルール」をクリックします。

新しいルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」を選択します。

次の数式を満たす場合に値を書式設定のボックスに、次の数式を設定します。

=IFERROR($B2*0,1)=1

あとは、書式を設定したら、OKボタンをクリックします。

これで、文字が入力されているデータの行全体を塗りつぶすことができました。

ところで、設定した数式は、どう動いているのでしょうか。

確認しておきましょう。

=IFERROR($B2*0,1)=1

B2に0を乗算しています。数値に「0（ゼロ）」を乗算すれば「0（ゼロ）」になります。

文字の場合、「0（ゼロ）」を乗算すれば、「#VALUE!」というエラーが表示されます。「#VALUE!」は、文字と数値で計算をした場合に表示されるエラーです。

要するに、エラーが表示されたら、文字ということがわかるというわけです。

そこで、IFERROR関数をつかうことで、エラーだった「1」を算出するように設定します。

その「1」と等しかったらセルを塗りつぶすように、条件付き書式を設定すればいいというわけです。

Excelには、エラー発生時に対応する関数が用意されていますので、ワザとエラーを発生させて対応させるという方法もありますよ。

Excel。今週のFacebookページの投稿 2022/4/11-2022/4/17【Trivia】

Excel。今週のFacebookページの投稿　2022/4/11-2022/4/17

＜Facebookページ＞

Facebookページで【書いてみた】Excelの豆知識（Trivia）です。

4月11日

Excel。CEILING.PRECISE関数。

読み方は、シーリング・プリサイズで、指定した数値の倍数に切り上げます

4月12日

Excel。CELL関数。

読み方は、セルで、セルの書式・位置・内容に関する情報を得ます

4月13日

Excel。CHAR関数。

読み方は、キャラクターで、文字コードを文字に変換します

4月14日

Excel。CHIDIST関数。

読み方は、カイディストで、カイ二乗分布の上側確率を算出します

4月15日

Excel。CHIINV関数。

読み方は、カイインバースで、上側累積確率からカイ二乗分布のパーセント点の値を逆算します

4月16日

Excel。CHISQ.DIST関数。

読み方は、カイスクウェア・ディストで、カイ二乗分布の確立を算出します

4月17日

Excel。CHISQ.DIST.RT関数。

読み方は、カイスクウェア・ディスト・ライトテールで、カイ二乗分布の上側確率を算出します

Excel。BETA.DIST関数は、ベータ分布の累積確率を算出します【BETA.DIST】

Excel。BETA.DIST関数は、ベータ分布の累積確率を算出します

＜関数辞典：BETA.DIST関数＞

BETA.DIST関数

読み方： ベータ・ディスト

　　　　 ベータ・ディストリビューション

分類： 統計 

BETA.DIST(x,α,β,関数形式,[A],[B])

ベータ分布の累積確率を算出します　

Excel。カレンダー。曜日の欄に祝日名を手早く表示するにはどうしたらいい【holiday】

Excel。カレンダー。曜日の欄に祝日名を手早く表示するにはどうしたらいい

＜IFNA+VLOOKUP+TEXT関数＞

祝日名をカレンダーの曜日のとなりに、祝日名という列を設けると、あまり使わない列を追加することになります。

当然、印刷した時に幅広な表になってしまいます。

そこで、次の表のように、曜日の列に祝日名を表示するようにしたい場合、どのようにしたら、手早く表示することができるのでしょうか。

最初に、準備として、祝日の一覧表を用意する必要があります。

祝日一覧表は、運用上、成人の日など、どうしても毎年祝日が変わるので更新し続ける必要があります。

今回は、わかりやすいように、5月の祝日一覧をD1:E4に設定しています。

B列の数式を考えていくことにしましょう。

祝日一覧に該当する日付があれば、E列を表示して、そうでなければ、曜日を表示するという考え方がわかりやすいかと思います。

曜日は、WEEKDAY関数だと、曜日ごとの数値は算出できますが、1だったら、「日」というようしなければいけないので、多くの関数を使う必要がありそうです。

そのため、曜日は、「TEXT関数」をつかって、日付を曜日で表示させることにします。

祝日一覧から抽出するには、A列の日付が含まれているかいないかを判断する必要があります。

あるかないかを判断するには、MATCH関数をつかうことになるわけです。

ただ、MATCH関数をつかった場合、何番目にそのデータがあるのかを算出してくれるだけで、E列の祝日名をB列に表示させるためには、さらに、数式を修正する必要があります。

IFNA関数をつかうことで、当初イメージした、日付が祝日一覧にあれば、祝日名をそうでなければ、曜日を表示するという数式を作ることができます。

これらのことを踏まえて、B2の数式を確認してみましょう。

B2の数式は、

=IFNA(VLOOKUP(A2,$D$2:$E$4,2,FALSE),TEXT(A2,"aaa"))

VLOOKUP関数で検索方法を完全一致で設定した場合、抽出データが無い場合「#N/A」というエラーが表示されます。

ならば、「#N/A」エラーなのかを判断材料として、数式を組み立てることができれば、コンパクトな数式で処理を行うことができそうです。

「#N/A」エラーかどうなのかを判断することが出来る関数が「IFNA関数」です。

IFNA関数をつかうことで、#N/Aだったら、VLOOKUP関数。

そうでなければ、曜日を表示するという数式をつくることができるというわけです。

エラーで対応というわけですから、IFERROR関数でも問題はありません。

今回のように、エラーだったらということを判断基準とする関数をつかうことで、数式をシンプルにすることができるかもしれませんね。

Excel関数辞典 VOL.62。PRICE関数～PROB関数【dictionary】

Excel関数辞典　VOL.62。PRICE関数～PROB関数

＜Excel関数＞

今回は、PMT関数～PPMT関数までをご紹介しております。

PRICE関数

プライス

PRICE(受渡日,満期日,利率,利回り,償還価額,頻度,[基準])

定期利付債の時価を算出する

PRICEDISC関数

プライスディスク

PRICEDISC(受渡日,満期日,割引率,償還価額,[基準])

割引債の額面100に対する価格を算出します

PRICEMAT関数

プライスマット

PRICEMAT(受渡日,満期日,発行日,利率,利回り,[基準])

満期利付債の時価を算出します

PRICE at MATurityの略

PROB関数

プロブ

プロバビリティ

PROB(x範囲,確率範囲,下限,[上限])

範囲内の確率値を算出します

Excel。BETADIST関数はベータ分布の累積確率を算出します【BETADIST】

Excel。BETADIST関数はベータ分布の累積確率を算出します

＜関数辞典：BETADIST関数＞

BETADIST関数

読み方： ベータディスト

     ベータディストリビューション

分類： 互換性 

BETADIST(x,α,β,[A],[B])

ベータ分布の累積確率を算出します

Excel。オートフィルターで抽出した最大値の行を塗りつぶししたい【MAX-DATA】

Excel。オートフィルターで抽出した最大値の行を塗りつぶししたい

＜条件付き書式＋AGGREGATE関数＞

Excelの機能を組み合わせることで、思ったような結果にならないことが、見受けられます。

例えば、オートフィルターと条件付き書式の組み合わせです。

条件付き書式をつかうことで、データの状況をわかりやすくできます。

ただ、オートフィルターで抽出した結果に連動して、条件付き書式が機能してくれないことがあります。

次のような表を用意しました。

D列の合計フィールド内で、最大値のデータがある行全体を塗りつぶす、条件付き書式を設定しています。

行全体で塗りつぶしたいので、数式を使用して、条件付き書式を設定する必要があります。

今、設定している数式は、

=MAX($D$2:$D$11)=$D2

としてあります。

MAX関数をつかっていますが、MAX関数を使うことで最大値を算出することができます。

その結果と同じならば、最大値のデータが含まれている行ということがわかります。

さて、問題はここからで、B列の「Leading」が70点以上という条件で、オートフィルターをつかって抽出してみます。

すると、このような結果になりました。

残念ながら、抽出結果に連動して、条件付き書式が動いていません。

なぜ、このような結果になってしまったのかというと、MAX関数が、引数の範囲内が非表示になった時に対応していないのが原因です。

つまり、MAX関数は、可視データのみというわけにはいかないというわけです。

そのため、最大値＝MAX関数というよりも、行の非表示に対応にしたAGGREGATE関数を使う必要があるわけです。

今回のような最大値の場合は、SUBTOTAL関数でも対応できますが、上位の順位を算出できるLARGEという条件が含まれていませんので、AGGREGATE関数のほうが、様々なケースに対応しやすいかと思います。

では、条件付き書式の数式を変更していきます。

次の数式を満たす場合に値を書式設定の数式を

=AGGREGATE(4,5,$D$2:$D$11)=$D2

と変更しました。

先程と同じ条件で抽出してみます。

最大値が167に変わったことに連動して、条件付き書式が機能していることがわかります。

フィルター機能と条件付き書式を組み合わせて使うときには、非表示に対応するということを念頭に置いて対応することが大切のようですね。