今週のFacebookページの投稿 2021/1/25-2021/1/31【Facebook】

今週のFacebookページの投稿　2021/1/25-2021/1/31

＜Facebookページ＞

Facebookページで【書いてみた】ワンポイントです。

1月25日

Excel。SUMXMY2関数。

読み方は、サム オブ エックス マイナス ワイ スクエアエドで、Σ(x-Y)^2。 対応する組の要素の差を2乗して合計

1月26日

Excel。SWITCH関数。

読み方は、スイッチで、式で指定した値を比較して最初に一致する値を返す

1月27日

Excel。SYD関数。

読み方は、エスワイディーで、減価償却費を算術級数法で算出（日本では利用が認められてない） Sum of Year's Digit depreciationの略

1月28日

Excel。T関数。読み方は、ティーで、文字列を抽出する

1月29日

Excel。TAN関数。読み方は、タンジェントで、角度の正接（タンジェント）算出

1月30日

Excel。TANH関数。読み方は、ハイパーポリック タンジェントで、数値の双曲線正接を算出

1月31日

Excel。TBILLEQ関数。読み方は、ティービルイーキューで、米国財務省短期証券の債権に相当する利回りを算出

Excel。条件付き書式。And条件での行単位の塗りつぶしはどうやったらいいの？【Conditional formatting】

Excel。条件付き書式。And条件での行単位の塗りつぶしはどうやったらいいの？

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

1回目2回目とも70点以上のデータは、わかりやすいように、行単位で塗りつぶしたい場合、どのようにしたらいいのでしょうか？

「70点以上で塗りつぶしたい」ということなので、条件付き書式を使うことはイメージできると思います。

問題になってくるのが、2つあって、行単位での塗りつぶしたいという希望と2列のデータが該当するかしないのか判断させる必要があるわけですね。

要するに、「○○かつ○○」ということなので、AND条件を使うといいこともわかります。

今回は、次の表を使って説明していきます。

この表のように、1回目と2回目の得点が70点以上の場合、行単位で塗りつぶしを設定したいわけです。

条件を判定するために表に列が追加できる場合には、次のように条件に合致するかしないかと判定させて、その結果を使って、条件付き書式を設定する方法が、わかりやすいと思われます。

E2の数式は、

=IF(AND(C2>=70,D2>=70),"○","")

というように、IF関数とAND関数をつかって、判定させることが多いと思います。

あと、「○」のときは、塗りつぶすように条件付き書式を設定してあげればいいわけです。

ただ、E列のように判定する列を用意しなくても、条件付き書式とAND関数を組み合わせることで、設定することもできます。

条件付き書式は、書式ですから、オートフィルで数式をコピーするわけではないので、A2:D11を範囲選択します。

ホームタブの「条件付き書式」にある「新しいルール」をクリックします。

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

「数式を使用して、書式設定するセルを設定」をルールの種類から選択して、次の数式を満たす場合に値を書式設定には、AND関数の数式を直接入力します。

=and($C2>=70,$D2>=70)

この数式の説明は後回しとして、書式を設定していきますので、書式ボタンをクリックします。

セルの書式設定ダイアログボックスが表示されますので、塗りつぶしを設定します。

設定したら、OKボタンをクリックします。

先程のダイアログボックスに戻りますので、さらにOKボタンをクリックします。

1回目・2回目ともに70点以上に該当したデータのみに、行単位でセルの塗りつぶしが行われていることが確認できますね。

では、設定で使用した数式を確認しておきましょう。

=and($C2>=70,$D2>=70)

2列（複数列）にあるデータが満たされるかどうかなので、AND関数を使います。

そして、「$C2」「$D2」というように、列を固定した複合参照にすることで、行単位で塗りつぶしを設定することができます。

条件付き書式は、数式と組み合わせることで、日頃の資料にプラスすることが出来るかもしれませんので、使ってみるといいかもしれませんね。

Excel Technique_BLOG Categoryに追加しました。2020/1/28【Technique】

Excel Technique_BLOG Categoryに「移動平均」などを追加しました。

＜目次サイト＞

このBLOGの記事を、カテゴリー分けにした【Excel Technique_BLOG Category】に追加しました。

Excel。平均は平均でも、移動平均を指定した日数で算出する方法

移動平均は、一定期間の平均を連続的に求めることで、データの変動を吸収できるので、全体の方向性を掴みやすいツールです。

＜続きはこちら＞

Excel。平均は平均でも、移動平均を指定した日数で算出する方法

https://infoyandssblog.blogspot.com/2015/10/excelaverage.html

Excel。最高値からそのデータがある日付をセル参照するには?

大量の売上データがあるそうでして、その中から、最高金額がいくらなのか？そして、その発生した日がいつなのか？

＜続きはこちら＞

Excel。最高値からそのデータがある日付をセル参照するには?

https://infoyandssblog.blogspot.com/2015/10/excelmatch.html

Excel。一行おきのデータを集めて別表にするためのテクニック

一行おきのデータをそれぞれ抽出して、別の表にしたい

＜続きはこちら＞

Excel。一行おきのデータを集めて別表にするためのテクニック

https://infoyandssblog.blogspot.com/2015/11/exceloffset.html

Excel。XLOOKUP関数をつかうと、楽に、上位3位の点数に該当する氏名を抽出できます。【XLOOKUP】

Excel。XLOOKUP関数をつかうと、楽に、上位3位の点数に該当する氏名を抽出できます。

＜SMALL+ROW関数&XLOOKUP関数＞

最高値を算出するには、MAX関数を使いますし、2位や3位など指定の順位を算出するには、LARGE関数をつかいます。

ただ、困ったことに、数値そのものしか算出することができません。

どういうことかというと、次の表があります。

 

AB列の表は、ある競技の時間の表です。

上位3位までのタイムのいい人を抽出したいわけです。

作業としては、最初にD列を算出します。

数値が小さい上位3名までなので、SMALL関数を使えばOKです。

D2の数式を確認してみましょう。

=SMALL($B$2:$B$11,ROW(A1))

引数のROW(A1)は何を意味似ているのかというと、ROW(A1)としないで、1位を算出したいので、「1」と設定したら、オートフィルで数式をコピーしても、「1」のままなので、「2」「3」と修正しないといけません。

ROW関数をつかって、数式を修正する手間を省いています。

SMALL関数を使うことで、上位1～3位までのデータを算出することはできるのですが、そのデータの他のフィールドを抽出することは、SMALL関数ではできません。

垂直の表になっているので、VLOOKUP関数を使えば、抽出することができるのではと考えるかもしれませんが、残念ながら、今回のような表からでは、VLOOKUP関数で、別のフィールドのデータを抽出することはできません。

なぜならば、VLOOKUP関数は、抽出する範囲の一番左側が、検索値でなければなりません。

つまり、今回は、参加者・時間という並びになっていますので、VLOOKUP関数が使えないわけです。

時間・参加者ならばVLOOKUP関数が使えます。

このような表に対応するには、INDEX+MATCH関数をつかうことで、抽出することはできるのですが、なかなか面倒な関数の組み合わせです。

ところが、近年追加された「XLOOKUP関数」を使うと、とても簡単に問題を解決してデータを抽出することができます。

E2をクリックして、XLOOKUP関数をつかった数式を作っていきます。

なお、XLOOKUP関数ですが、XLOOKUP関数ダイアログボックスで数式をつくると、どのように引数を設定したらわかりにくいので、出来ることならば、手入力で数式をつくるほうがいいように思えます。

なので、今回は、手入力で、XLOOKUP関数を作っていきます。

最初は、検索値ですが、これは、D2の時間が該当しますので、「D2」と入力したいところですが、Office365のExcelやXLOOKUP関数が使えるようになったExcel2019には、「スピル機能」があるので、「D2:D4」と入力します。

検索範囲ですが、検索値が所属しているフィールド（列）ですから、B2:B11。

オートフィルで数式をコピーするのではなく、「スピル機能」によって数式がコピー（スピル）されますので、絶対参照は不要です。

戻り範囲ですが、これは、抽出したいものですから、参加者の列ですね。

A2:A11と入力します。

ここも、絶対参照は不要です。

見つからない場合は、空白にしたいので「””（ダブルコーテーション×2）」

一致モードは、完全一致なので「0」

検索モードは、データの上部から下部へと検索しますので「1」

Enterキーで数式を確定すると、スピル機能によって、数式が設定されます。

E2の数式は、

=XLOOKUP(D2:D4,B2:B11,A2:A11,"",0,1)

です。

このように、XLOOKUP関数は、VLOOKUP関数よりも、融通がききやすいので、使ってみると便利です。

なお、「スピル機能」がよくわからない場合は、旧来通り、絶対参照をつかった数式をつくり、オートフィルで数式をコピーしてもOKですよ。

その場合の数式は、

=XLOOKUP(D2,$B$2:$B$11,$A$2:$A$11,"",0,1)

です。

新しく加わった機能や関数を、取り入れてみると、改善できるものとかあるかもしれませんね。

今週のFacebookページの投稿 2021/1/18-2021/1/24【Facebook】

今週のFacebookページの投稿　2021/1/18-2021/1/24

＜Facebookページ＞

Facebookページで【書いてみた】ワンポイントです。

1月18日

Excel。SUM関数。

読み方は、サムで、数値の合計

1月19日

Excel。SUMIF関数。

読み方は、サムイフで、条件付きで数値の合計

1月20日

Excel。SUMIFS関数。

読み方は、サムイフズで、複数の条件付きで数値の合計

1月21日

Excel。SUMPRODUCT関数。

読み方は、サムプロダクトで、複数の数値の組を掛け合わせて合計

1月22日

Excel。SUMSQ関数。

読み方は、サムスクウェアで、数値の2乗の合計を算出

1月23日

Excel。SUMX2MY2関数。

読み方は、サム オブ エックススクエアエド マイナス ワイ スクエアエドで、Σ(x^2-Y^2)。対応する組の要素の平方差の合計

1月24日

Excel。SUMX2PY2関数。

読み方は、サム オブ エックス スクエアエド プラスワイ スクエアエドで、Σ(x^2+Y^2)。対応する組の要素の平方和の合計

Excel。2行連続・3行連続と連続して連番を設定したいけど、楽に設定するにはどうするの？【Serial number】

Excel。2行連続・3行連続と連続して連番を設定したいけど、楽に設定するにはどうするの？

＜INT&ROW関数＞

連番を設定する作業は、オートフィル機能を使うことで、いとも簡単に設定することができますが、次の帳票のように、連続した行になっていないと、オートフィルでは対応することが出来ません。

今回は、店舗ごとに連番を設定したいというのがやりたいこと。

各店舗には、上半期と下半期の2行で構成されています。

このような帳票だと、オートフィルで一発というわけにはいかないのです。

A列の左側にNOという見出しの列を挿入します。

A2に「1」と入力して、オートフィルをつかって、「連続データ」にすると連番を設定することができます。

ただ、今回は、店舗ごとに連番を設定したいわけです。

それでは、A2:A3に「1」と入力して、オートフィルをつかえば、連番が設定できるように思えますが、実行してみると、うまくいきません。

データの件数が少なければ、自力で入力するということでも、いいかもしれませんが、大変な事には変わりありません。

そこで、ちょっとした数式を作ることで、この問題から解放されます。

使うのは、INT関数とROW関数の2つです。

INT関数は、整数化するための関数ですね。

ROW関数は、行番号を算出することができる関数ですね。

では、A2に次の数式を設定しましょう。手入力でも対応できると思います。

=INT(ROW(A2)/2)

たったこれだけですが、数式を設定したら、オートフィルで数式をコピーしてみましょう。

 

このように、2行連続で、同じ番号で連番を設定することができました。

ポイントは、この数式の仕組みですね。仕組みがわからないと、表の起点が変わる、要するに、何行目にその行があるのかによって、「1」にするために、ちょっと考える必要が発生します。

数式の説明をしておきましょう。

ROW(A2)ですが、2行目なので、行番号の「2」が算出されます。

算出された「2」を2で除算します。算出結果は「1」ですね。

算出結果の「1」をINT関数で整数化されるので、「1」が算出されたというわけです。

なので、奇数行は、2で除算して、0.5の余りが発生しても、INT関数で整数化されるために連番を設定することができたわけです。

この仕組みがわかれば、3行連続とか、四半期ごとなどでも、連番を設定することができるようになります。

四半期の場合だと、次のようにすればOKですね。

A2の数式は、次のように設定してあります。

=INT(ROW(A4)/4)

ポイントは、どうやったら「1」を表示することができるのかですね。

Excel関数辞典 VOL.42。ISBLANK関数～ISLOGICAL関数【dictionary】

Excel関数辞典　VOL.42。ISBLANK関数～ISLOGICAL関数

＜Excel関数＞

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

ISBLANK関数

イズブランク

対象が空白セルの場合にTRUEを返す

ISBLANK(テストの対象)

ISERR関数

イズイーアールアール

対象がエラー値の#N/A以外の場合にTRUEを返す

ISERR(テストの対象)

ISERROR関数

イズエラー

対象がエラー値の場合にTRUEを返す

ISERROR(テストの対象)

ISEVEN関数

イズイーブン

対象が偶数の場合にTRUEを返す

ISEVEN(テストの対象)

ISFORMULA関数

イズフォーミュラー

セルに数式が含まれている場合にTRUEを返す

ISFORMULA(テストの対象)

ISLOGICAL関数

イズロジカル

対象が論理値の場合にTRUEを返す

ISLOGICAL(テストの対象)

Excel。全体の上位30%以上のデータか判断したいけど、どうやったらいいの？【Top of the whole】

Excel。全体の上位30%以上のデータか判断したいけど、どうやったらいいの？

＜百分位：PERCENTILE.INC＞

成績とかポイントとかタイムとか、上位30%以上なら優秀とか予選突破というように判定したい場合、ある関数を知っているのといないのとでは、作業工程がかわってしまいます。

今回は、全体のデータのうち、上位30%以上を算出させる方法を紹介していきます。

なんで比率であるパーセントを使うことがあるのかというと、例えば点数で、75点以上としてしまうと、テストの難易度や、スポーツだったら諸条件によっては、その設定を越えてしまう件数が、多くなったり、少なくなったりしてしまうわけです。

そのような点から、比率による抽出方法があるわけですね。

次の表を用意しました。

行う処理として、上位30%を越えているかどうかを判断したいわけです。

よく、平均値以上というように判断する時には、基本として、平均値を算出する必要がありますが、考え方としては同じで、ポイントになるのは、どうやったら、上位30%の数値を算出することができるのかということにつきます。

そこで、登場する関数が、PERCENTILE.INC関数です。

このPERCENTILE.INC関数は、百分位を算出することができる関数です。

この関数を使うことで、判断するための値を算出することができます。

PERCENTILE.INC関数を知らないと、色々計算しないといけなくなってしまいます。

それでは、F1にPERCENTILE.INC関数ダイアログボックスをつかって、関数を作っていきます。

配列には、データ全体を範囲選択しますので、B2:B13と設定します。

率ですが、30%としてはいけません。

上位30%を判断したいわけなので、全体の70%以上の場所にある数値かどうかを判断したいわけですから、70%で設定する必要があります。

100%-30%で70%ですね。

あとは、OKボタンをクリックします。

2240.3と算出されました。

この数値が、全体の70%にある数値というわけです。

あとは、算出された数値以上かどうかを判断すればいいわけですから、C2にIF関数をつかって判断させていきます。

C2に設定する数式は、

=IF(B2>=$F$1,"○","")

あとは、オートフィルで数式をコピーしましょう。

上位30%にあるのかないのか、判断することできました。

なじみの薄い、PERCENTILE.INC関数をつかうことで、算出することができました。Excelには、知っていると実は便利だったという関数があるかもしれませんので、アレコレ調べてみるのもいいかもしれませんね。

また、今回は、2段階で算出させましたが、IF+ PERCENTILE.INC関数というネストを使えば、一発で算出することもできます。

ただし、上位30%の境目になる数値が視認できるほうがいいような気もします。

今週のFacebookページの投稿 2021/1/11-2021/1/17【Facebook】

今週のFacebookページの投稿　2021/1/11-2021/1/17

＜Facebookページ＞

Facebookページで【書いてみた】ワンポイントです。

1月11日

Excel。STDEVP関数。

読み方は、スタンダードディブピーで、数値群を母集団全体とみなした標準偏差を算出します。

1月12日

Excel。STDEVPA関数。

読み方は、スタンダードディブピーエーで、全データを母集団全体とみなした標準偏差を算出します。

1月13日

Excel。STDEV.P関数。

読み方は、スタンダードディブ・ピーで、数値群を母集団全体とみなした標準偏差を算出します。2010から登場

1月14日

Excel。STDEV.S関数。

読み方は、スタンダードディブ・エスで、数値群を標本データとみなした標準偏差を算出します。2010から登場

1月15日

Excel。STEYX関数。

読み方は、スタンダードエラーワイエックスで、回帰直線の標準誤差を算出します。

1月16日

Excel。SUBSTITUTE関数。

読み方は、サブスティチュートで、文字列中の特定の文字を別の文字に置換する

1月17日

Excel。SUBTOTAL関数。

読み方は、サブトータルで、11種類の集計方法で小計を算出します。

Excel。VBA。フィルターの抽出後にコピー。テーブルの時は注意しないといけません。【Copy】

Excel。VBA。フィルターの抽出後にコピー。テーブルの時は注意しないといけません。

＜VBA:フィルターとコピー＞

大きなデータなど、オートフィルターをつかってデータを抽出します。

毎度毎度同じ条件で抽出するのは面倒ですし、その抽出した結果を別のシートにコピーするという作業もまとめて行いたい時には、Excel VBAでマクロをつくったほうが作業効率の改善が見込めるかもしれません。

Excel VBAのプログラム文そのものは、簡単なのですが、テーブルでの抽出からのコピーは思ったようにコピーできないというトラブルが発生しますので、注意が必要です。

最初は、通常の表での抽出からのコピーを確認していきます。

今回は次のデータを用意しました。

C列の店舗が「横浜店」のデータを抽出して、シート「COPY」にコピーするというマクロを作っていきます。

Sub 抽出()

    Range("a1").AutoFilter field:=3, Criteria1:="横浜店"

    Range("a1").CurrentRegion.Copy Destination:=Worksheets("COPY").Range("a1")

    Range("a1").AutoFilter

End Sub

とりあえず実行してみましょう。

このように、横浜店のデータだけがCOPYという別シートに貼り付けることができました。

プログラム文を説明しておきましょう。

Range("a1").AutoFilter field:=3, Criteria1:="横浜店"

A行が見出し行なので、Range(“a1”)、つまりA1を起点として、.AutoFilterを設定します。

抽出の条件が、

field:=3は、左から3列目。C列の店舗名という意味ですね。

Criteria1:="横浜店"は、Criteria1が「抽出条件1」という意味なので、抽出条件1が横浜店だったら。という条件で、抽出を実行します。

Range("a1").CurrentRegion.Copy Destination:=Worksheets("COPY").Range("a1")

この一行は、抽出した範囲をコピーして貼り付ける作業をしている行です。

Range("a1").CurrentRegion.Copyは、A1を起点として隣接しているセル（表）をコピーします。

Destination:=Worksheets("COPY").Range("a1")は、貼付け先（Destination）は、Worksheets("COPY").Range("a1")で、シート「COPY」のA1という意味の行です。

Range("a1").AutoFilter

最後のこの一行は、オートフィルターを解除する行です。

これによって、抽出も解除されます。

このようにとても簡単に作れるマクロなので、知っておくと便利なのですが、困ったことがあって、テーブルだと、コピーした結果が「？」なことが発生します。

先程の表をテーブルにしてみました。

店舗が「横浜店」のデータを抽出してシート「COPY」に貼り付けますので、先程のマクロを実行してみましょう。

まず、困ったことが発生しました。

テーブルに設定されていた、オートフィルターが解除されてしまっています。

原因は、抽出をクリアして解除するための最後の一行です。

Range("a1").AutoFilter

これは、同じ行を追加すればいいので、問題は解決しますが、一番の問題は、コピー先をみてみましょう。

抽出どころか、シートそのものがコピーされてしまっています！

これが『トラブル』なんですね。

テーブルだと、あることをしないと、なぜかちゃんと処理してくれません。

では、テーブル内のセルをクリック（アクティブ）して、同じマクロを実行してみましょう。

今度は、問題なくデータが抽出されて、テーブルではなくコピーされています。

理由はよくわかりませんが、テーブルの時は、テーブル内のセルをクリックしてアクティブにしないと、うまく処理してくれないようです。

これを踏まえてマクロを修正してみましょう。

Sub 抽出_テーブル対応()

    Range("a1").Activate

    

    Range("a1").AutoFilter field:=3, Criteria1:="横浜店"

    Range("a1").CurrentRegion.Copy Destination:=Worksheets("COPY").Range("a1")

    Range("a1").AutoFilter

    Range("a1").AutoFilter

    

End Sub

Range("a1").Activate

最初の一行目は、シート内をアクティブにするためのものです。

実行すると、テーブルでも抽出されたデータをコピーすることができます。

このように、抽出からのデータコピーをしたいときに、テーブルだとちょっと、クセがありますので、注意する必要がありそうですね。

Excel。グラフの復習。相関図～一部強調円グラフ【graph】

Excel。グラフの復習。相関図～一部強調円グラフ

＜グラフの復習＞

Excelのグラフは、用途に合わせて様々なグラフを作ることができます。

今回は、グラフの復習ということ、4つをピックアップ

・Excel。相関図（相関グラフ）を作るにはどうしたらいいの？

・Excel。横軸項目を縦棒グラフに内蔵したグラフの作り方。

・Excel。差異を表現する横棒グラフをつくってみたい。

・Excel。円グラフの一部を大きくした、『一部強調円グラフ』の作り方

Excel。相関図（相関グラフ）を作るにはどうしたらいいの？

2つの項目が交わるところがわかるグラフで、2つの項目の相関関係を『見える化』したグラフが、【相関図】。

相関グラフともいわれたりしますが、この作り方がちょっと、わかりにくいということなので、今回は、改めて作り方を確認してみましょう。

＜続きはこちら＞

Excel。相関図（相関グラフ）を作るにはどうしたらいいの？

https://infoyandssblog.blogspot.com/2019/01/excelcorrelation-diagram.html

Excel。横軸項目を縦棒グラフに内蔵したグラフの作り方。

横軸の項目が、縦棒グラフの下側に内蔵されているグラフをつくるにはどうしたらいいのか？

＜続きはこちら＞

Excel。横軸項目を縦棒グラフに内蔵したグラフの作り方。

https://infoyandssblog.blogspot.com/2019/02/excelcolumn-chart.html

Excel。差異を表現する横棒グラフをつくってみたい。

プラスの場合は、左側にラベルが表示されていて、マイナスの場合は、右側にラベルを表示している横棒グラフをつくりたい

＜続きはこちら＞

Excel。差異を表現する横棒グラフをつくってみたい。

https://infoyandssblog.blogspot.com/2019/02/exceldifference-bar-chart.html

Excel。円グラフの一部を大きくした、『一部強調円グラフ』の作り方

2Dでも一部を切り離した円グラフを作ることは簡単にできますが、一部だけを強調したグラフをつくりたい

＜続きはこちら＞

Excel。円グラフの一部を大きくした、『一部強調円グラフ』の作り方

https://infoyandssblog.blogspot.com/2019/03/excelpie-chart.html

Excel。ピボットテーブルで集計データが空白だったらわかりやすいようにしたい。【Cell is blank】

Excel。ピボットテーブルで集計データが空白だったらわかりやすいようにしたい。

＜ピボットテーブル＞

ピボットテーブルをつかってクロス集計などを作った時に、元のデータがなければ、当たり前ですが、空欄で表示されます。

ちょっとしたデータがあればピボットテーブルをつかうことで、比較的簡単にクロス集計のような帳票を作れますが、空欄が多かったりすると、ちょっと気になります。

空欄のままでもいいのですが、例えば空欄に「0（ゼロ）」を表示したい場合は、どのようにしたらいいのでしょうか？

ピボットテーブルではなく、通常の表ならば、範囲選択をして、空欄を「0（ゼロ）」に置換すればいいわけですが、ピボットテーブルでは、そういうわけにはいきません。

例えば、C5のサンフランシスコの2月のデータが空欄なので、「0」を入力しようとすると、次のようなメッセージが表示されてしまいます。

 

ピボットテーブルで管理させているセルには直接入力することはできません。

範囲選択して、置換しようとしても、メッセージが表示されてしまい、置換することはできません。

ピボットテーブル側で管理しているので、次の方法で対応していきます。

ピボットテーブル内のセルをクリックして、ピボットテーブルをアクティブにします。

ピボットテーブル分析タブのピボットテーブルにある「オプション」をクリックします。

ピボットテーブルオプションダイアログボックスが表示されます。

レイアウトと書式タブの書式にある、「空白セルに表示する値」に「0（ゼロ）」と設定してOKボタンをクリック。

空白セルだったところに、「0（ゼロ）」が表示されました。

このようにピボットテーブルオプションを使うことで、「0（ゼロ）」を表示することが出来るわけですが、「空白セルに表示する値」となっているので、数値だけだと思っている人もいるようでして、例えば「データなし」と設定すれば、「データなし」と表示されます。

このように、データがない空白セルに「データなし」と表示することができました。

ただ、今回のように「データなし」という文字にしてしまうと、件数が多い場合、見にくい帳票になってしまう恐れがありそうですね。

また、「0（ゼロ）」を表示することも出来ますが、データがないので「0（ゼロ）」にするというのもどうかというケースもあるかもしれませんね。

「集計結果が0」と「データがない=０」とするでは意味が異なってしまうので、こちらも注意する必要がありますね。

Excel Technique_BLOG Categoryに追加しました。2020/1/13【Technique】

Excel Technique_BLOG Categoryに追加しました。

＜目次サイト＞

このBLOGの記事を、カテゴリー分けにした【Excel Technique_BLOG Category】に追加しました。

Excel。あれれ！グラフが表示されない！！そんな時は、第2軸で表示しましょう。

【第2軸】を使えるようになると、グラフで様々表現をすることができますので、覚えておきたいテクニックの一つですね。

＜続きはこちら＞

Excel。あれれ！グラフが表示されない！！そんな時は、第2軸で表示しましょう。

https://infoyandssblog.blogspot.com/2015/10/excelgraph2.html

Excel。上限の二者択一なら、IF関数よりもMIN関数という手もあります。

上限付きの二者択一の場合は、MIN関数というアイディアもありますので、使ってみてはどうでしょうか？

＜続きはこちら＞

Excel。上限の二者択一なら、IF関数よりもMIN関数という手もあります。

https://infoyandssblog.blogspot.com/2015/10/excelminifmin.html

Excel。上位80%を合格と判定させたいけど、RANK.EQ関数では歯が立たない

上位80%の人を合格と判断したい時には、PERCENTRANK.INC関数です。

＜続きはこちら＞

Excel。上位80%を合格と判定させたいけど、RANK.EQ関数では歯が立たない

https://infoyandssblog.blogspot.com/2015/10/excelpercentrankinc80rankeq.html

Access。テーブルの一部のフィールド全体を空白に戻したいけど、「””」じゃ戻らない【Blank】

Access。テーブルの一部のフィールド全体を空白に戻したいけど、「””」じゃ戻らない

＜Access:更新クエリのNull＞

定期的に使っているテーブルがあるのですが、データ全体を消すのではなくて、データの一部のフィールド全体を空白に戻したい場合、あることを知らないと対応することができません。

今回は、「Tアンケート」というテーブルの「NO」以外のフィールドのデータを空白にして流用したいわけです。

今回のように少ないデータ量ならば、自力で削除してもいいのですが、データ量が増えれば当然、大変な作業ですし、ミスが発生する確率もアップします。

テーブルをコピーして、貼り付けの設定を「テーブル構造のみ」にしても、データの一部だけを残すことは残念ながらできません。

そうなると、該当するデータを空白に置換する。

つまり、空白に更新してあげればいいわけです。

テーブルのデータを更新するには、選択クエリではなくて、更新クエリで対応します。

作成タブの「クエリデザイン」をクリックします。

最初は、通常どおり選択クエリを作りますので、該当するフィールドだけで設定します。

このまま更新クエリに変更してもいいのですが、失敗すると取り返すが付かないこともありますので、まずはこの選択クエリで該当のデータが抽出されているのか、確認しておくといいでしょう。

確認したら、クエリツールの「デザイン」にある更新クエリに変換するボタンをクリックします。

設定に「レコードの更新」という行が追加されました。

データを空白にしたいわけですから、ExcelのIF関数などでお馴染みの「””」（ダブルコーテーション×2）を使えば置換されるように思います。

実行ボタンをクリックしてみると、更新されるというメッセージが表示されますので、「はい」をクリックします。

すると、残念ながら、エラーメッセージが表示されてしまい、実行することはできません。

更新クエリを使うまではいいのですが、Excelのように「””」（ダブルコーテーション×2）をつかって、空白に戻すことができないのです。

そこで、登場するのが、『Null』（ヌル）です。Nullをつかうことで、未入力の状態にすることができます。

使い方は先ほどの「””」（ダブルコーテーション×2）をNullにするだけです。

それでは、改めて、更新クエリを実行してみましょう。

更新を確認するためのダイアログボックスが表示されますので、「はい」をクリックします。

エラーメッセージは表示されなかったので、Tアンケートを確認してみましょう。

NOのフィールド以外、すべて空白に戻すことができました。

今回のようにExcelでよく使っていることができないというのが、Accessにはちょこちょこあるようですので、注意する必要がありそうですね。