今週のFacebookページの投稿 2021/10/24-2021/10/31【one thing】

今週のFacebookページの投稿　2021/10/24-2021/10/31

＜Facebookページ＞

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

10月25日

Excel。条件付き書式で、書式を設定するセルと条件の対象のセルが異なる場合は、数式を使用してルールを作ることになりますね。

10月26日

Excel。条件付き書式の数式には関数も使えますので、メッチャ、奥が深くなりますね。

10月27日

Excel。SORT。

Excel2010は値以外にもフォントの色やセルの色、条件付き書式のアイコンでも並び替えることが出来ますね。

10月28日

Excel。SORT。

合計行とか除いて並び替えをするときには、その行はのぞかないといけないので、データを範囲選択する必要がありますね。

10月29日

Excel。Excelのデータを他のアプリケーションで使用するファイル形式にすることを、エクスポートといいます。

10月30日

Excel。他のアプリケーションをExcel内に取り込むことをインポートといいます。

10月31日

Excel。複数のコメントをまとめて削除する場合は、対象となるコメントが挿入されているセル範囲を選択してから操作を行うと、まとめて削除出来ちゃいます。

Excel。複数条件に合致するデータの行全体を塗りつぶして、わかりやすくしたい【Multiple conditions】

Excel。複数条件に合致するデータの行全体を塗りつぶして、わかりやすくしたい

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

大きなデータがあって、複数条件に合致するデータがどこにあるのかを知りたい場合、目視では大変ですね。

自動的に、セルに色を付ける。できることなら、行全体を塗りつぶしたい。

たしかに、オートフィルターをつかって、複数条件に合致するデータを抽出する方法もありますが、抽出の欠点は、そのデータだけになってしまうことで、データ全体としてみるのは難しくなってしまいます。

今回のように、条件があるセルの塗りつぶしをするには、「条件付き書式」を使うに限ります。

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

今回は、「店舗名が秋葉原」で「売上高が500以上」という条件でレコード（行全体）を塗りつぶすようにしていきます。

条件付き書式をつかっていきますが、用意されているメニューでは対応できないので、数式を使って条件を設定していきます。

数式をつかって、条件を表すには、AND関数を使うと、煩雑な数式にならずにすみます。

今回のように、「店舗名が秋葉原」で「売上高が500以上」というように、フィールド（列）ごとに条件がある場合には、AND関数を使うと条件を設定しやすくなります。

もし、「店舗名が秋葉原か品川」というように、フィールド（列）内の複数条件だった場合には、OR関数を使うといいわけです。

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

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

ルールの種類には「数式を使用して、書式設定するセルを決定」を選択します。

書式設定のボックスには、次の数式を設定します。

=and($A2="秋葉原",$B2>=500)

数式の説明は後に回すとして、塗りつぶしの色を設定したら、OKボタンをクリックします。

条件に合致したデータのレコード（行）だけが塗りつぶしされていることが確認できました。

このように、複数条件でわかりやすくしたい場合には、条件付き書式という方法もあります。

さて、先程設定した数式についてです。

=and($A2="秋葉原",$B2>=500)

AND関数はさておき、ポイントになるのは、「$A2」「$B2」です。

列番号は、絶対参照で、行番号は、相対参照が設定されている、複合参照で設定しています。

列番号を固定してあげることで、行全体を塗りつぶすことができます。

ある種、「決まり事」のようなものなので、条件付き書式で行全体を塗りつぶしたい時には、列番号固定の複合参照を設定すると覚えておくと、便利です。

Excel。CHAR関数は、文字コードの文字を表示する関数です。【function: CHAR】

Excel。CHAR関数は、文字コードの文字を表示する関数です。

＜CHAR関数＞

指定した文字コードに対応する文字を表示してくれるのが、CHAR関数です。

CHAR関数だけで使われることは少ないかもしれませんね。

というのも「A」を「65」と覚えている人は少ないでしょうから、まずは、文字コードをCODE関数で調べて、その数値をつかって、Excel VBAなどでつかったりするわけです。

CHAR関数の読み方は「キャラクター」です。

所属は、「文字列操作」です。

CHAR関数の引数も確認しておきましょう。

CHAR(数値)

引数の数値は文字コードを意味しています。

なお、半角英数はASCIIコードを使用しています。

日本語などの全角文字はJISコードに対応してます。

ただ、この文字コードの割り振りが、ちょっと厄介なんですね。

B2には、

=CHAR(A2)

という数式が設定してあります。

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

「A B C D…」の文字コードは連番で割り振られているので、Excel VBAなどでは「+1」するようにしてあげれば、A～Zを表示すること自体、簡単に出来るのですが、「ひらがな」などは、「あいうえお」ではなくて、途中に、「ぃ」など、いわゆる半音が設定されているため、単純に「+1」しても対応してくれません。

促音でつかう「っ」や拗音でつかう「ゃ」などをまとめて、割り振ってくれればよかったのですが…。

あとは、UTFコード。すなわち、UNICODEは、JISコードと異なっていますので、注意が必要です。

 

「あ」はJISコードでは、9250ですが、UNICODEでは、12354です。

ちなみに、UNICODEに対応した、UNICHAR関数とUNICODE関数というのもあります。

Excel。そのデータに関係性（相関関係）はあるのかないのか。偶然なのか知りたい。【CORREL】

Excel。そのデータに関係性（相関関係）はあるのかないのか。偶然なのか知りたい。

＜CORREL関数＞

日々のデータを入力しているけど、それで終わってしまっては、もったいないと思うこともあります。

例えば、あるコーナーに訪れた人数とその売上のデータがそろってきたけど、どのようなことがわかったりするのか、知りたくなってきます。

そういう時は、CORREL関数をつかってみてはいかかでしょうか？

次の表があります。

来店数が増えれば、売上高は増えるといえるのでしょうか？

NO3のデータは、来店数が17で、売上高が2726。

NO5のデータも来店数は17ですが、売上高は2126と同じではありませんし、NO1の来店数は7で売上高は1011に対して、NO10は来店数が4なのに、売上高は1344と来店数は少ないのに売上高は高いようです。

この2つのデータに関係性（相関関係）があるのかないのかを知りたいわけですね。

そこで、登場するのが、CORREL関数です。

CORREL関数は、引数も少なくわかりやすい関数ですが、この関数を使うことで、簡単に相関係数を算出することができ、関係性があるのか、ないのかを知ることができます。

F1に、CORREL関数の数式を設定します。

F1の数式は、

=CORREL(B2:B11,C2:C11)

引数は、比較したいフィールドを設定するだけです。

算出された値は、

0.988622671

この値は何を意味しているのでしょうか？

算出された値が「1」に近ければ近いほど、相関関係が強いという意味です。

逆に、「0」に近づくと、関連性は薄いということを意味しています。

0～0.3は、ほぼ相関はない

0.3～0.6は、相関は弱いといえる

0.6～0.8は、相関があるといえる

0.8～1.0だと、相関は強いといえる

といわれています。

なので、今回の算出結果は、「0.988～」なので、「1」に近いわけですから、相関関係は強いと考えられます。

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

よって、売上高をあげようとしたら、来店数を増やす施策をしてみるといいのではないかと考えもいい訳ですね。

データがあつまったら、そのままにせず、色々な角度から、今まで気づかなかったことが見えてくるかもしれませんね。

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

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

＜Facebookページ＞

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

10月18日

Excel。名前の定義をすると、既定ではシート名とセル番地が絶対参照で定義されますね。

10月19日

Excel。3-D集計・串刺し集計は集計対象の表及びセルが共通でないと計算できませんね。

10月20日

Excel。3-D集計・串刺し集計はSUM/AVERAGE/COUNT/COUNTA/MAX/MINでできますよ

10月21日

Excel。スパークラインは、表のデータを見ながら、傾向を視覚的に把握確認できるツールですね。

10月22日

Excel。スパークラインは、マーカーを表示して最大や最小を強調することが出来ますね

10月23日

Excel。スパークラインは、色を変更したりすることもできますね。

10月24日

Excel。条件付き書式を設定すると、手動で設定した書式よりも優先されますね。

Access。小数点を切り捨てするには、Int関数とFix関数があるけど、どう違うの。【Decimal point】

Access。小数点を切り捨てするには、Int関数とFix関数があるけど、どう違うの。

＜Access：Int関数・Fix関数＞

Excelには、小数点以下を切り捨てるINT関数とTRUNC関数という2つの関数がありますが、Accessにも、Int関数とFix関数というのがあります。

そこで、Int関数とFix関数の違いを確認しておきましょう。

次のテーブルを用意しました。

 

それでは、Int関数とFix関数をつかったクエリを作成してみます。

Int関数という演算フィールドには、

Int関数: Int([数値])

Fix関数という演算フィールドには、

Fix関数: Fix([数値])

を設定しました。

では、実行してデータシートビューで確認してみましょう。

NO1とNO2のデータですが、5.1も5.5も小数点以下が切り捨てられていて、Int関数もFix関数とも「5」という値が算出されました。

Int関数もFix関数も差が無いようです。

しかしながら、実際にはInt関数とFix関数には違いがあります。

Int関数は、数値以下の最大の整数を算出することができます。

一方

Fix関数は、数値の小数部分を取り除いた数値を算出することができます。

つまり、「正数」のときには、「差」がないのですが、「負数」のときには、算出結果が異なっていることが確認できます。

No3の「-3.1」をInt関数で算出させると、「-4」と算出されます。

Int関数は数値が小さくなる方に向かって算出されるようになっています。

No4の「-2.9」も同様に小さい値である「-3」を算出していることが確認できます。

Int関数は小数点以下を切り捨てるイメージなので、「-3.1」は「-3」になるイメージがしますが、そういうわけではないのです。

逆に、「正数」ではInt関数と同じ算出結果だったFix関数はというと、No３の「-3.1」は、「-3」。

No4の「-2.9」は「-2」と算出されています。

Fix関数は「0」に向かって値が算出されていることがわかります。

Fix関数の方が「-3.1」が「-3」と算出されているので、イメージに近い算出結果だといえます。

このように、Int関数とFix関数には、「負数」の時に違いが生じますので、使用する時にケースバイケースで使い分ける必要があります。

Accessの関数はExcelの関数程、種類はありませんが、ExcelのINT関数とTRUNC関数の関係性と同じような、Int関数とFix関数というのが用意されていますので、注意が必要になってきます。

Excel関数辞典 VOL.54。NORMSDIST関数～NOT関数【dictionary】

Excel関数辞典　VOL.54。NORMSDIST関数～NOT関数

＜Excel関数＞

今回は、NOMINAL関数～NORM.INV関数までをご紹介しております。

NORMSDIST関数

ノーマルスタンダードディスト（ディストリビューション）

標準正規分布の累積確率を算出します。

NORMSDIST(Z)

NORM.S.DIST関数

ノーマル・スタンダード・ディスト（ディストリビューション）

標準正規分布の累積確率を算出します。Excel2010以降

NORM.S.DIST(Z,関数形式)

NORMSINV関数

ノーマルスタンダードインヴァース

累積確立から標準正規の数値を逆算する

NORMSINV(確率)

NORM.S.INV関数

ノーマル・スタンダード・インヴァース

累積確立から標準正規の数値を逆算する　Excel2010以降

NORM.S.INV(確率)

NOT関数

ノット

指定した条件が成立しないことを調べる

NOT(論理式)

Excel。数値で構成された表からグラフを作ると、横軸が1・2・3…となるのでどうしたらいい【Horizontal axis】

Excel。数値で構成された表からグラフを作ると、横軸が123…となるのでどうしたらいい

＜折れ線グラフ＞

Excelでグラフを作ること自体は簡単でも、表によっては思っているようなグラフをつくることができないことがあります。

例えば、次のような表をつかってグラフを作るとわかります。

今回は、この表を使って、マーカー付き折れ線グラフを作ってみます。

別に問題なく作れると思うかもしれませんが、挿入後アレコレやらないといけないグラフが挿入されるので確認してみましょう。

A1:B7を範囲選択して、挿入タブの「折れ線/面グラフの挿入」にある「マーカー付き折れ線グラフ」をクリックします。

次のようなグラフが挿入されてました。

横軸に、年の「2016・2017…」と表示されずに、「1・2・3…」と数値が表示されてしまっています。

さらに、年は、データとして扱われてしまっていて、プロットエリア内にマーカー付き折れ線グラフとして描かれてしまっています。

この原因は、表にあります。

表のデータが、横軸に当たる所も含めて、すべて「数値」になっている表だからです。

仮に次のように、年が2016年という「文字」の表でつくってみます。

この場合マーカー付き折れ線グラフをつくってみると、横軸に年が表示されたグラフになります。

このように、データ全体が「数値」の場合には、データを指示してあげる必要が発生します。

それでは、グラフを修正していきます。

グラフのデザインタブの「データの選択」をクリックします。

データの選択ダイアログボックスが表示されます。

プロットされている「年」を削除しますので、凡例項目の年を選択して、削除ボタンをクリックします。

続いて横軸を修正します。

横（項目）軸ラベルの編集ボタンをクリックします。

軸ラベルダイアログボックスが表示されます。

横軸で表示したいA2:A7を範囲選択してOKボタンをクリックします。

データソースの選択ダイアログボックスに戻ってきますので、OKボタンをクリックします。

マーカー付き折れ線グラフは、次のように変わりました。

これで、文字データと同じように、横軸を「2016・2017…」と変えることができました。

このように、グラフにしたい表のデータが「数値」だった場合には、ちょっと注意する必要があります。

なお、データが数値の場合、マーカー付き折れ線グラフでグラフを作らないで、挿入タブの「散布図またはバブルチャートの挿入」にある、散布図（直線とマーカー）でグラフを作ると、何の問題もなく描くことができます。

綺麗グラフが挿入されました。

折れ線グラフは、散布図でも描くことができますので、データによっては、散布図でつくってみてもいいですし、横軸がおかしなときは、修正して対応することになります。

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

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

＜Facebookページ＞

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

10月11日

Excel。ミニツールバーが邪魔な時は、ファイルタブ→オブション→基本設定→選択時にミニツールバーを表示する→OKボタンで非表示にできますね。

10月12日

Excel。ジャンプ機能を使うと数値データのセルをまとめて選択することが出来ますね。

10月13日

Excel。シート名を変更するには、シート名の上でダブルクリックすると修正可能になりますね。

10月14日

Excel。シート名を変更するには、シート名の上で右クリックをして、メニューから名前の変更で修正可能ですね。

10月15日

Excel。シート見出しの色は、設定した後、薄いなぁ～と感じた時は、別のシートに移動してみましょう。きっと指定した色がついているのが確認できますよ。

10月16日

Excel。セルに名前を定義しておくと、目的のセルを素早く選択したり、数式に使用することが出来ますね。

10月17日

Excel。セル範囲に名前を定義しておくと、目的のセル範囲を素早く選択したり、数式に使用することが出来ますね。

Excel。パーセントじゃなくて割分厘の歩合で表示するにはどうしたらいい【Percentage】

Excel。パーセントじゃなくて割分厘の歩合で表示するにはどうしたらいい

＜表示形式：ユーザー定義＞

パーセント表示にすることは、比較的お馴染みな処理で、簡単に表示を変更することができますが、小数点を「割分厘」の歩合で表示するには、どのようにしたらいいのでしょうか？

パーセント表示にするには、ホームタブのパーセントボタンの「パーセントスタイル」をクリックすれば、パーセント表示にすることができます。

また、小数点の上げ下げもボタンをつかうことで、簡単に上げ下げすることができます。

パーセント表示は通常処理で行うので、ボタンがありますが、歩合に表示するためのボタンはありません。

そのため、オリジナルで表示形式を作る必要があります。

該当するセルをクリックして、セルの書式設定ダイアログボックスを表示します。

セルの書式設定ダイアログボックスは、Ctrl+1というショートカットキーで表示するのが便利ですね。

表示形式タブの分類にある、ユーザー定義をクリックして、種類を「0"割"0"分"0"厘"」と設定します。

OKボタンをクリックして確認してみましょう。

「0割0分0厘」と表示することはできましたが、「1割2分3厘」と表示されていません。

ユーザー定義の表示形式で設定した「0"割"0"分"0"厘"」ですが、これは、0割=百の位。

0分=十の位。

0厘＝一の位を表示させるようになっています。

歩合の文字が表示されているだけであって、表示形式としては「000」と同じな訳です。

0.123のままだと、0割0分0厘と表示されてしまったわけです。

表示するために、便宜上1000倍すれば、1割2分3厘と表示してくれるはずです。

では、C4の数式に「*1000」を追加します。

これで、希望通りに「1割2分3厘」と表示することができました。

ただし、歩合で表示するだけならいいのですが、最大の注意点があって、この歩合の数値をつかって計算しようとした場合、「*1000」していますので、「/1000」を計算式に追加しないと、とんでもない数値が算出されてしまいます。

滅多につかうことはないかもしれませんが、あくまで、歩合の形式で表示しただけですので、注意しましょう。

Excel。図形もグラフも列を非表示にすると縮んじゃうのでどうにかしたい。【Shape shrink】

Excel。図形もグラフも列を非表示にすると縮んじゃうのでどうにかしたい。

＜オブジェクトと列幅＞

Excelに図形やグラフを挿入したのはいいのですが、その後列幅を広げる・狭くする。

または、列を非表示にすると、ちょっと困ったことが発生します。

D列の幅を広げてみます。

すると、列幅を広げたのと連動して、図形も勝手に横幅が広がってしまいました。

逆に、D列を非表示にしてみます。

今度は、列が非表示になったのと連動して、図形も勝手に横幅が短くなってしまいました。

このように、図形やグラフ等のオブジェクトは、列幅に連動してサイズが変更されてしまうように設定されています。

これでは、列幅を変更するたびに、図形やグラフ等のオブジェクトの横幅も修正するのは面倒です。

そこで、連動しないように設定することができます。

違いを確認するために、最初の状態に戻しておきます。

図形をクリックして、アクティブにしておきます。

図形の書式タブのサイズにある「サイズとプロパティ」ボタンをクリックします。

図形の書式設定作業ウィンドウが表示されます。

図形のオプションのプロパティにある「セルに合わせて移動するがサイズ変更はしない」にチェックマークをいれて変更します。

これで、設定が完了しましたので、改めて、D列を非表示にします。

このように、列幅と連動しないで、そのままのサイズなのが確認できました。

勝手に列幅を変更されたくない時には、設定を変更するといいですね。

Excel。VBA。印刷設定を毎回行うのは面倒なので手早く設定したい【Print settings】

Excel。VBA。印刷設定を毎回行うのは面倒なので手早く設定したい

＜Excel VBA＞

データを読み込んだり、ファイルを開いたりしたあとに、印刷する場合、様々な設定をしないといけません。

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

印刷設定として、よく行う作業としては、

・横1ページに収めるようにする

・フッターに　ページ数/総ページ数　を表示する

・2ページ目以降にも見出し行が印刷されるようにする

といったところでしょうか。

これらの処理を、毎回設定して印刷するというのは、面倒ですし、意外と設定に時間がかかります。

できることなら、手早く印刷したいところです。

そこで、Excel VBAでマクロをつくって、実行すると、手早く設定した状態で印刷することが出来るようになります。

本来ならば、2行程度が2ページ目に送られているので、1枚に収めると思いますが、今回は、フッターや見出し行が複数ページに印刷されるようになっているのか、確認したいので、2ページとしています。

プログラム文は、次のとおりです。

Sub 印刷設定()

    With ActiveSheet

        '横1ページに収める

        .PageSetup.Zoom = False

        .PageSetup.FitToPagesWide = 1

        .PageSetup.FitToPagesTall = False

        'フッター中央にページ番号

        .PageSetup.CenterFooter = "&P/&N"

        

        'タイトル行の設定

        .PageSetup.PrintTitleRows = "$1:$1"

        

        'プレビュー画面を表示

        .PrintPreview

    End With

End Sub

とりあえず、実行してみます。

横1ページに収めるようにする

フッターに　ページ数/総ページ数　を表示する

2ページ目以降にも見出し行が印刷されるようにする

これらが、きちんと反映されています。

では、プログラム文を確認していきましょう。

With ActiveSheet　～　End With

アクティブのシートを印刷します。プログラム文に「ActiveSheet」と入力するのは、面倒なので、With文をつかって、省略させています。

プレビューを除いて、本来は、「With ActiveSheet.PageSetup」とするといいですね。

'横1ページに収める

.PageSetup.Zoom = False

.PageSetup.FitToPagesWide = 1

.PageSetup.FitToPagesTall = False

横は1ページに収めるようにさせるのが、このブロック。

.PageSetup.Zoom = False　は、-拡大・縮小率を指定しないようにさせています。

.PageSetup.FitToPagesWide = 1　は、横方向1ページで印刷するように設定します。

.PageSetup.FitToPagesTall = False　は、縦方向はそのまま自動で対応とします。1ページに収める場合は、「1」とします。

'フッター中央にページ番号

.PageSetup.CenterFooter = "&P/&N"

フッターの中央（CenterFooterプロパティ）に、「&P」のページ番号と区分けするための「/」と総ページ数の「&N」とすること、ページ数/総ページ数をフッター中央に表示することができます。

'タイトル行の設定

.PageSetup.PrintTitleRows = "$1:$1"

見出し行の1行目を設定しています。

これは、ページ設定ダイアログボックスのタイトル行を設定する作業そのものです。

 'プレビュー画面を表示

.PrintPreview

最後の、PrintPreviewをつかうことで、印刷プレビューで確認することができます。

たった、数行ですが、このマクロを実行することだけで、印刷設定を瞬時に完了することができます。

Excel。CEILING.MATH関数は、発注書などで活躍する関数です。【function: CEILING.MATH】

Excel。CEILING.MATH関数は、発注書などで活躍する関数です。

＜CEILING.MATH関数＞

職場によって見る見ないという関数だとは思いますが、CEILING.MATH関数は、発注書などをつくるときに、大変活躍してくれる関数です。

なお、CEILING.MATH関数の読み方は「シーリング・マス」です。

所属は、「数学/三角」です。

CEILING.MATH関数の引数も確認しておきましょう。

CEILING.MATH (数値,[基準値],[モード])

となっています。

どのような時に活躍してくれるのか確認してみましょう。

次の表を用意しました。

発注希望数がB列に入力されています。

1個ずつ発注できるならば、79個とか146個とかで行うことが出来るのですが、納品される箱がダース。

つまり12個入りのために、12の倍数で発注数を設定しないといけないわけです。

B4の120のように12×10なので、そのまま120と発注数に入力することができますが、79や146という場合、なかなかダースでというのは、大変ですし、データ数が増えれば暗算で対応するとしても時間がかかってしまいます。

そこで、CEILING.MATH関数の登場です。

C2に次の数式をつくって、オートフィルで数式をコピーします。

=CEILING.MATH(B2,12)

すると、このように算出されました。

発注数が、どれも、12の倍数になっていることがわかりますね。

当然、一番近い数値に切り上げてくれています。

仮に引数の基準値を「8」にすれば、8の倍数で切り上げてくれます。

Excel。等間隔のデータを手早くコピーしてリスト化するにはどうしたらいいの？【Equally spaced】

Excel。等間隔のデータを手早くコピーしてリスト化するにはどうしたらいいの？

＜INDEX+ROW関数＞

セル参照にしても、コピーペーストをするとしても、等間隔のデータを手早くコピーしてリスト化する作業は結構面倒です。例えば次のような表の場合です。

３日ごとの集計されている合計値を、別セルにリスト化したいわけですね。

サンプルのように件数が少ない場合、「努力と根性」でどうにかできますが、面倒です。

Excel VBAでプログラムを作ってもいいですが、簡単な関数をつかった数式で対応することができます。

その数式は、

=INDEX($B$2:$B$13,ROW(A1)*4)

という数式が、E1に設定されています。

この数式を、オートフィルで数式をコピーするだけで、簡単で、手早くリスト化することができます。

今回のようなケースの場合は、行番号・列番号が交差されるセル番地を算出できる「INDEX関数」か、指定した参照の行番号・列番号の範囲の値を算出する「OFFSET関数」のどちらかで、対応できないかを考えてみるといいですね。

このデータの特徴としては、3日間の合計値は、行数が、4つごと。

つまり、4の倍数ごとに登場しているわけです。

そこで、行番号・列番号が交差されたセル番地を参照できる「INDEX関数」をつかうことで、算出できるのではと、想像します。

では、実際にINDEX関数をつかって、数式をつくってみます。

INDEX関数は、2つの種類から選択できる関数です。

INDEX（配列,行番号,[列番号]）で算出できますので、こちらを採用します。

引数を確認していきます。

配列ですが、これは、値がある範囲なので、$B$2:$B$13。

完成した数式をオートフィルでコピーしますので、絶対参照を設定しておきます。

行番号。

ROW(A1)*4

ここがポイントです。

合計値が登場しているセルは、B5・B9・B13ですが、配列で、B2:B13としています。

行数は2行目が1で13行目が12なわけですから、実際に求めたい数値は、「4・8・12」ですね。

B2:B13の４行目・8行目・12行目を算出したいわけです。

B5＝4行目＝1×4

B9＝8行目＝2×4

B13＝12行目=3×4

1～3と増加する数値は、行番号を算出することができるROW関数をつかうと、すべての問題をクリアすることができます。

ROW(A1)は1。オートフィルで数式をコピーするので、ROW(A2)なら2と算出してくれます。

引数の列番号は、列を移動させる必要がないので、省略が可能です。

INDEX関数は、アイディアによって使い勝手がいい関数なので、機会がありましたら、アレコレつかってみると、現場で使用している数式を、コンパクトにすることができるかもしれませんね。

今週のFacebookページの投稿 2021/10/4-2021/10/10【one thing】

今週のFacebookページの投稿　2021/10/4-2021/10/10

＜Facebookページ＞

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

10月4日

Excel。置換で条件に書式を設定できますね。

10月5日

Excel。検索の条件にある書式設定を削除するには書式検索のクリアをクリックしましょう。

10月6日

Excel。置換の条件にある書式設定を削除するには書式検索のクリアをクリックしましょう。

10月7日

Excel。日付データをもとにしてオートフィルを行うと、連続データの単位が日・週・月・年で選択できますね

10月8日

Excel。表示形式を解除するには、標準に設定する必要がありますね。

10月9日

Excel。表示形式を設定していないセルにはG/標準が設定されていますね。

10月10日

Excel。表示形式のG/標準「G」とはGeneral（一般の、普遍的な、全般の）頭文字の「G」の略です。

英語版のExcelでは「G/標準」は「General」となっています。

Excel。小計を除いて最大値を求めたいのに範囲選択が面倒なのでどうにかしたい【Exclude subtotals】

Excel。小計を除いて最大値を求めたいのに範囲選択が面倒なのでどうにかしたい

＜SUBTOTAL関数＞

帳票と同じようにExcelで表を作ってしまうと、意外と面倒な処理が発生することがあります。

例えば次のような表。

小計が含まれている帳票ですね。

最大値を算出しているB14。

おかしいですよね。

合計値の値を算出しています。

範囲選択をB3:B11までにすると、今度は、1782という小計値を算出してしまいます。

要するに、範囲選択内の最大値を算出してしまうわけです。

となると、範囲選択は、「B3:B5,B7:B8,B10:B11」というように、何度も区切らなければいけません。

これでは、件数が増えた場合、かなり面倒な作業をしないと、数式を作ることができないということになってしまいます。

では、どうしたらいいのでしょうか？

まずは、合計値の場合からみてみましょう。

B6・B9・B12には、SUM関数をつかった数式で小計値を算出しています。

B13もSUM関数をつかっていますが、オートSUMボタンをつかって、合計をつかうと、その範囲選択内にあるデータ内でSUM関数の算出結果のみを合算してくれます。

B13の数式は、自動的に、

=SUM(B12,B9,B6)

と生成してくれます。

SUM関数をつかうと、B3:B12をドラッグしても、SUM関数のところだけをつかってくれるので、便利です。

では、最大値はどうなのでしょうか？

オートSUMボタンにある、最大値をつかって、B3:B13までを範囲選択してみると、データであろうが、小計値であろうが、合計値だろうが、関係なく範囲選択内の、最大値を算出してしまいます。

SUM関数をつかったところを除外して算出してくれることはありません。

実は、SUM関数やMAX関数だと、このような帳票には不向きな関数なのです。

今回のような場合は、SUBTOTAL関数かAGGREGATE関数をつかうことで、一気に問題を解決することができ、合理的に数式を作ることができます。

SUBTOTAL関数をつかってみた場合をみてみましょう。

最大値のC14は、小計や合計の値ではなく、店舗の最大値が表示されています。

まずは、小計の数式を確認してみます。

C6の数式は、

=SUBTOTAL(109,C3:C5)

と設定しています。

引数の109をつかうことで、合計を算出することができます。

残りも同じようにSUBTOTAL関数をつかっています。

C13の数式は、

=SUBTOTAL(109,C3:C12)

と設定しています。

範囲選択も小計のセルのみを選択するわけではなく、全体を選択してもSUM関数どうように、SUBTOTAL関数で算出した値のところだけを合算してくれいます。

問題の最大値を算出しているC14の数式は、

=SUBTOTAL(104,C3:C13)

引数の104は、最大値を算出する番号です。

範囲選択は、C3:C13とデータも小計値も合計値も関係なく範囲選択していますが、最大値がきちんと算出されています。

このように、途中に小計や合計が含まれる帳票のような表の場合には、SUM関数やMAX関数ではなくて、SUBTOTAL関数やAGGREGATE関数をつかうことで、結果的に効率よく数式をつくることができます。

ケースバイケースで、どの関数をつかうのが効率的なのかを考えながら帳票をつくるといいかもしれませんね。