Excel。新関数のCONCAT関数はこう使う！3-D集計は数値しかできないので、文字の場合はどうするの？【CONCAT】

Excel。新関数のCONCAT関数はこう使う！3-D集計は数値しかできないので、文字の場合はどうするの？

【IF+CONCAT関数】

複数シートの同じ位置にある場合、3-D集計を使うと、あっという間に合算してくれたりします。
数値だから3-D集計を使えるわけですが、次のような文字の場合どのようにしたらいいのでしょうか？

【複数シートから同じ文字を見つける】

次の3枚のシートがあります。

このシートは各人の予定表で会議に参加できる時間には○。
参加できない時間には×を入力した表です。

この3枚のシートの同じ位置が○ならば、3名とも空いているとわかるようにしたいわけです。

そして、その結果を、別のシートに集計したいということをやりたいわけですね。

当然、人力による『目視』で確認するという方法もありますが、時間がかかりすぎます。

また、先程も書きましたが、数値を合算させるならば、3-D集計という考え方がありますが、文字ですので、集計というわけにはいきません。

考え方としては、営業部長の9月1日の10時が○で、企画部長の9月1日の10時が○で…というように、AND関数を使ってみようと考えるところですが、AND関数だと、数式が長くなる傾向がありますので、今回は、Office365・Office2019で追加された新関数のCONCAT関数を使うと比較的簡単に確認することができます。

【CONCAT関数は作業グループのように設定できる】

まずは、CONCAT関数の動きだけを確認してみましょう。
集計先の参加可能日シートのB4をクリックして、CONCAT関数ダイアログボックスをクリックしましょう。

テキスト1のボックスをクリックしてカーソルを表示したら、集計したい先頭のシートである、営業部長のシート名をクリックして、最終シートの経理部長のシート名をShiftキーを押しながら、クリックします。

つまり『作業グループ』です。

そして、B4をクリックします。

OKボタンをクリックしましょう。B4には、
=CONCAT(営業部長:経理部長!B4)という数式が設定されています。

結果は、×××。つまり、3シートともB4には×が入力されていることがわかります。

ところで、CONCATENATE関数ではダメなのでしょうか？
念のために確認しておきましょう。

=CONCATENATE(営業部長:経理部長!B4)
とB4の数式を変更してみましたが、残念ながら、#REF！というエラーが表示されてしまいました。

CONCATENATE関数では、作業グループのような数式を使うことができません。

なので、一つずつシートごとにセルをクリックする方法ならばエラーはでません。

=CONCATENATE(営業部長!B4&企画部長!B4&経理部長!B4)

これでもいいのですが、シートが増えるとAND関数どうように数式が長くなってしまい煩雑になってしまいます。

では、本題に戻っていきましょう。

CONCAT関数の結果が、”○○○”だったら、○で、そうでなければ×と表示すればいいわけですから、IF+CONCAT関数で簡単に算出することができます。

B4の数式は、
=IF(CONCAT(営業部長:経理部長!B4)="○○○","○","×")
あとは、オートフィルで数式をコピーすれば完成ですね。

このように、新しく登場したCONCAT関数を使うと、今までよりも改善できる場合もありそうですね。

Excel Technique_BLOG Categoryに追加しました。2019/05/30

Excel Technique_BLOG Categoryに追加しました。

＜目次サイト＞

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

Excel。0（ゼロ）を表示させないようにするIF関数を使った公式

商品名がないところでも、数式があると、0（ゼロ）を表示してしまって、
カッコ悪いというか、出来る事なら、ゼロを表示させたくない訳ですね。

＜続きはこちらへ＞
Excel。0（ゼロ）を表示させないようにするIF関数を使った公式
https://infoyandssblog.blogspot.com/2014/06/excel0if.html

Excel。数値の入力チェックする技で、DELTA関数を使ってみる。

正しい数値が入力されているかどうかのチェックがなかなか、大変だとか、振分伝票で、借方と貸方が合致しているのか？というチェックもしたい。というご質問があったりします。
このように、２つの数値を比べてチェックするという関数があるので、それをご紹介しております。
その関数は、DELTA関数。

＜続きはこちらへ＞
Excel。数値の入力チェックする技で、DELTA関数を使ってみる。
https://infoyandssblog.blogspot.com/2014/06/exceldelta.html

Excel。DELTA関数を使って、振分伝票入力の借方と貸方の金額チェックする技を紹介

今回はこのDELTA関数を使用して、振分伝票の借方貸方の金額が合致しているのか？
というチェックで使用してみたいと思います。

＜続きはこちらへ＞
Excel。DELTA関数を使って、振分伝票入力の借方と貸方の金額チェックする技を紹介
https://infoyandssblog.blogspot.com/2014/07/exceldelta.html

Excel。VLOOKUP関数を改めて、勉強してみよう。基本編

VLOOKUP関数を使って求めるセルとデータの範囲（リスト）がある、VLOOKUP関数の入り口ともいえる、基本的な動きを基本編として、ご紹介していきましょう。

＜続きはこちらへ＞

Excel。VLOOKUP関数を改めて、勉強してみよう。基本編

https://infoyandssblog.blogspot.com/2014/07/excelvlookup.html

Excel。集合縦棒グラフのプロットエリアを縦方向に色分けしたい【Vertical bar chart】

Excel。集合縦棒グラフのプロットエリアを縦方向に色分けしたい

＜集合縦棒グラフ＞

集合縦棒グラフは多くの資料で見かけますが、たとえば、プロットエリアを2年ごとなどに塗り分けすることができれば、わかりやすい資料にすることができます。

グラフが大きくなった場合でも自動的にプロットエリアの大きさも変わりますので、図形を使うわけにはいきませんし、プロットエリアを塗りつぶすには1色しかできません。そこで、プロットエリアの代わりになるものを用意する必要があります。
また、横方向で塗り分けをするならば、積み上げ面グラフを使う方法もありますが、縦方向ではどのようにしたらいいのでしょうか？

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

A1:C7を範囲選択します。

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

売上高のデータを第2軸にするのですが、第2軸は、左側に配置しておきたいで、少々ややこしいのですが、最初に売上高の第2軸をONにします。
次に、プロットも第2軸をONにします。第2軸のチェックが両方とも消えますが気にせずに、作業を続けましょう。
最後に、改めて、売上高の第2軸をONにしましょう。

第2軸にした売上高の縦軸が第1軸側である、左側に表示することができました。あとは、プロットエリアを塗りつぶすためのデータは、「100%積み上げ縦棒」にして、OKボタンをクリックしましょう。

横軸が表示されていませんので、横軸を表示していきます。
グラフツールのデザインタブにある、「グラフ要素を追加」から軸の「第1横軸」をクリックします。

グラフに第1横軸（横軸）が表示されます。グラフタイトルは今回は不要なので合わせて削除しておきます。

オレンジ色のプロットのデータをクリックして、グラフツールの書式タブにある、グラフ要素が、「系列 "プロット"」になっているのを確認したら、選択対象の書式設定をクリックします。

画面右側に「データ系列の書式設定」作業ウィンドウが表示されます。

系列のオプションにある「要素の間隔」を0%に変更します。
あとは、2つずつのデータを異なる色で塗りつぶしを行っていきましょう。

凡例のプロットを削除して、右側のパーセント表示になっている縦軸が不要なので、消していきます。ただ、注意しないといけないのは、DELキーで削除すると、せっかく作った塗りつぶしも消えてしまいますので、非表示で対応させます。
右側の縦軸をクリックして、「軸の書式設定」作業ウィンドウにある、軸のオプションの「ラベル」をクリックして、ラベルの位置を「なし」に変更しましょう。

これで完成しました。

今週のFacebookページの投稿 2019/5/20-2019/5/26

今週のFacebookページの投稿　2019/5/20-2019/5/26

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

5月20日
Excel。If関数でよく使う、比較演算子。<は、より小さい（未満）。という意味ですね。

5月21日
Excel。If関数でよく使う、比較演算子。>=は、以上。という意味ですね。

5月22日
Excel。If関数でよく使う、比較演算子。<=は、以下。という意味ですね。

5月23日
Excel。If関数でよく使う、比較演算子。>=は、>を先に=を後に入力します。≧は、先に>を書きますので、その手順に基づきますね。

5月24日
Excel。If関数でよく使う、比較演算子。<=は、<を先に=を後に入力します。≦は、先に<を書きますので、その手順に基づきますね。

5月25日
Excel。入力規則。エラーメッセージ。停止は、規則に合致しないデータの入力は許可されませんね。

5月26日
Excel。入力規則。エラーメッセージ。情報は、エラーメッセージの"はい"をクリックすると、許可されて無効なデータでも入力することができますね。

Excelテクニック and  MS-Office recommended by PC training
https://www.facebook.com/exceltechniqueandmsoffice/

Access。出欠席を確認したいけど、AccessでCOUNTIF関数ってあるの？

Access。出欠席を確認したいけど、AccessでCOUNTIF関数ってあるの？

＜Access＞

Excelと違って、Accessは、フィールドに対して縦方向に集計することができますが、複数のフィールドを跨いで、要するに、横方向のフィールドには、Sum関数などの集計する関数は使うことができません。

しかしながら、次のようなテーブルがあって、1回目～5回目までの出欠席の数を算出したい場合は、Excelファイルとしてエクスポートするしか方法はないのでしょうか？

Excelだったら、COUNTIF関数を使って、”○”の数を数えればあっという間に出席した数を求めることができますが、Accessには、COUNTIF関数は存在しません。

抽出と集計を使った縦方向しか求めることができないからです。

そこで、Accessで使えるものだけで、求めていこうと考えます。

『一つのフィールド』だったら光が見えてきそうですね。

まずは、1回目～5回目までの○と×を1つのフィールドに集めます。

作成タブの「クエリデザイン」を使って、テーブルのすべてのフィールドを使ってクエリを作成します。

そして、次の演算フィールドを追加します。

結合: [01回目] & [02回目] & [03回目] & [04回目] & [05回目]
「&（アンパサンド）」を使って、各フィールドの文字を結合させたフィールドを作ります。
実行してデータシートビューで確認してみましょう。

この演算フィールドの『結合』を使って、出席数を求めます。

デザインビューに戻って、次の演算フィールドを作ります。

出席数: Len(Replace([結合],"×",""))
これで、出席数を算出することができます。

まずは、実行して確認してみましょう。

このように、”○”の数を算出することができていますね。
それでは、演算フィールドの説明をしましょう。

Len(Replace([結合],"×",""))

引数のReplace([結合],"×","")から説明します。

Replace関数は、文字を置換することができる関数です。
そこで、欠席を意味する”×”を消してしまえば、出席を意味する”○”だけが残ります。
その残った”○”を数えればいいわけです。
Replace関数を使って、”×”を「””（空白）」に置換させる処理をしています。

“○”だけ残った文字数を数えさせているのが、Len関数です。Len関数は文字数を数えることができる関数です。

このように、Len+Replace関数というネストを使うことで、算出することができます。

逆に、Len(Replace([結合],"○",""))と、”×”を”○”に替えてあげるだけで、欠席数を算出することができます。

出席率: [出席数]/Len([結合])
という演算フィールドを作れば、出席数は算出済みですし、結合した文字数を数えてしまえば、総数を求めることができますので、簡単に、出席率も求めることができるわけですね。

なお、出席率のパーセント表示は、プロパティを使うことで表示することができます。

Accessでは、Excelのように、ボタン一つでパーセントスタイルを設定することができませんので、書式を「パーセント」に設定をして、小数点以下表示桁数を必要に応じて設定すれば、パーセントで表示することができますよ。

こちらも、合わせて知っておくと便利な機能ですね。

Excel関数辞典 VOL.12。COMPLEX関数～CONFIDENCE.T関数

Excel関数辞典　VOL.12。COMPLEX関数～CONFIDENCE.T関数

＜Excel関数＞

今回は、COMPLEX関数～CONFIDENCE.T関数までをご紹介しております。

CONCAT関数は新しく、Officee365とOffice2019から登場しました。

COMPLEX関数
コンプレックス
複素数を表す文字列を生成する
COMPLEX(実数,虚数,虚数単位)

CONCAT関数
コンキャット
複数の文字列を統合
CONCAT(文字列1,文字列2…)
CONCATENATE関数の後継関数として、Excel2019から登場しました。Office365に搭載されたのはIIF関数などと同時期です。

CONCATENATE関数
コンカティネイト
複数の文字列を統合
CONCATENATE(文字列1,文字列2…)

CONFIDENCE関数
コンフィデンス
正規分布で母集団に対する信頼区間の1/2幅を算出
CONFIDENCE(α,標準偏差,標本の大きさ)
「標準偏差」と「標本数」をもとに、指定された有意水準「α」で、母集団に対する信頼区間の1/2幅を算出する関数です。


CONFIDENCE.NORM関数
コンフィデンス・ノーマル
正規分布で母集団に対する信頼区間の1/2幅を算出
CONFIDENCE.NORM(α,標準偏差,標本の大きさ)
CONFIDENCE関数の後継関数として、Excel2010から登場しました。
「標準偏差」と「標本数」をもとに、指定された有意水準「α」で、母集団に対する信頼区間の1/2幅を算出するのはCONFIDENCE関数と同じです。


CONFIDENCE.T関数
コンフィデンス・ティー
t分布で母集団に対する信頼区間の1/2幅を算出
CONFIDENCE.T(α,標準偏差,標本の大きさ)
Excel2010から登場しました。母集団の「標準偏差」が不明または、標本数が少ない時に「t分布」を使った「信頼区間」の1/2幅で算出します。


YandSシステムズのExcel関数一覧表
https://sites.google.com/view/yandsssystems/function?authuser=0

Excel。九九の表は複合参照でなくてデータテーブルが楽！【Data Table】

Excel。九九の表は複合参照でなくてデータテーブルが楽！

＜データテーブルと複合参照＞

Excelには、驚くほどの様々な機能が搭載されていますが、意外と知っていると便利だったりするものの中に、『データテーブル』というのがあります。
そこで、今回は、『データテーブル』を使って九九の表を作っていきます。

【複合参照で九九の表】
九九の表？と思うかもしれませんが、九九の表の作成は【複合参照】を使うので、難易度がアップします。

なぜ、複合参照を使わないといけないのかというと、「行だけ」「列だけ」の固定をしないといけないからです。
例えば、E3の数式を=E2*D3とすれば、E3には、4と算出されますが、この数式をオートフィルで数式をコピーすると、当然相対参照なので、セル番地がズレてしまします。
E2に着目して考えてみると、
オートフィルで、下方向にコピーすると、次のようになってしまいます。

E10の数式を確認すると、=E9*D10
E2を参照してほしいのに、E9になっています。では、絶対参照にすれば、いいのでしょうか？
たしかに、E列は上手くいきますが、となりのF列にオートフィルで数式をコピーすると、
絶対参照のためE2を常に参照するため、F2を参照してくれません。

そこで、常に2行目を参照してほしいけど、列は移動（相対のまま）させたいということから、=E$2*D3とすることで、2行目を参照することができます。

次に、列も同じです。D列を常に参照させたいわけですが、行は移動しないといけません。
よって、E3の数式は、
=E$2*$D3
とすることで、オートフィルで数式をコピーすれば九九の表が完成します。

紹介したように、【複合参照】をつかわないと、簡単に算出することができません。

【データテーブルで九九の表】
列×行のような、ある計算式を用意しておいて、列や行の数値がかわるだけで、いっぺんに結果を算出することができるのが、『データテーブル』。馴染みが薄いかもしれませんが、知っていると結構便利ですので、今回は、九九の表を使って紹介していきます。

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

A2:B3に列と行にある数値を設定しておきます。D2で使うための欄です。
D2には、E3:L10に算出させたい計算式を設定しておきます。
今回は、単なる乗算なので、=B2*B3

これで準備完了ですので、D2:L10を範囲選択して、データタブの「What-If分析」にある「データテーブル」をクリックします。

データテーブルダイアログボックスが表示されますので、列と行を設定していきます。

行の代入セルには、B3。列の代入セルはB2。
そして、OKボタンをクリックしましょう。

このように簡単に算出することができました。
もっと高度な計算式を左上に設定しておけば、様々な数値でどのように変わるの一目瞭然の表を作ることも出来ます。
最後に、左上のD2に数値が見えてしまっているので、見えなくして完成です。
D2をクリックして、セルの書式設定ダイアログボックスを表示しましょう。

表示形式の「ユーザー定義」の種類を「;;;（セミコロン×3）」と入力してOKボタンをクリックしましょう。これで、見えなくすることができました。

Excelには、意外と知られていない機能がたくさんありますので、アレコレ確認してみると、それぞれの現場で使えるものが潜んでいるのかもしれませんね。

今週のFacebookページの投稿 2019/5/13-2019/5/19

今週のFacebookページの投稿　2019/5/13-2019/5/19

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

5月13日
Excel。絶対参照を設定するときには、F4キーを3回クリックする$列行で表示されて、列が絶対で、行が相対の複合参照の設定できますね。

5月14日
Excel。絶対参照を設定するときには、F4キーを4回クリックすると一巡して、通常の相対参照に戻りますね。

5月15日
Excel。複合参照は、絶対参照を理解してから使うほうがいいですね。
混乱の基ですね。

5月16日
Excel。関数で、よく聞く、"引数"って、計算結果を求めるために必要な値のことですが、なかなかわかりにくいので、カッコの中身という感覚でいいと思いますね。

5月17日
Excel。If関数でよく使う、比較演算子。
=は、等しいという意味ですね。

5月18日
Excel。If関数でよく使う、比較演算子。
<>は、等しくないという意味ですね。

5月19日
Excel。If関数でよく使う、比較演算子。
>は、より大きい。という意味ですね。

Excelテクニック and  MS-Office recommended by PC training
https://www.facebook.com/exceltechniqueandmsoffice/

Excel。今再びの関数基本。数える関数全員集合！COUNT関数～AGGREGATE関数【COUNT】

Excel。今再びの関数基本。数える関数全員集合！COUNT関数～AGGREGATE関数

＜COUNT関数～AGGREGATE関数＞

現場でお馴染みの数える関数ですが、色々ありまして、まとめて一度確認しておきましょう。
次のような表があります。

【数値を数えるならCOUNT関数】

参加人数などを数えたい場合、もしNoなどの数値の列があれば、とても簡単に算出することができるのが、COUNT関数です。

C9に算出してみましょう。オートSUMボタンの▼をクリックして、数値の個数を選び範囲選択して確定させます。

ココでポイントになるのは、A列のように数値でなければなりません。

今回は、A2:A7を範囲選択します。

C9の数式は、
=COUNT(A2:A7)
とても簡単に算出することができますが、問題なのは、「数値の個数」であること。
つまり、B列のように『文字』は数えてくれないわけです。

【空白以外を数えるのがCOUNTA関数】

B列のような文字も数えたい場合は、COUNTA関数を使います。COUNTA関数は、文字も数値も対象になります。

テキストなどには、空白以外が対象になるように書かれているものが多いですが、計算式の算出結果が空白の場合は、数える対象になってしまいます。

C10に算出していきます。
COUNT関数と同じように、途中まで作っていきます。

残念ながら、0件と算出されました。

数式は、
=COUNT(B2:B7)
と出来上がっていますので、「T」と「(」の間に「A」と入力すればCOUNTA関数に変わり算出することができます。

C10の数式は、
=COUNTA(B2:B7)
これで、算出することができました。

【条件が一つの場合はCOUNTIF関数】

得点が80点以上の件数を知りたい場合は、COUNT関数やCOUNTA関数では対応することができません。

そこで条件付きで対応している関数が、COUNTIF関数とCOUNTIFS関数です。

今回は、80点以上と条件が一つだけ（単数条件）なので、COUNTIF関数を使います。

慣れれば、手入力で数式を作るのが楽なのですが、まだExcelの手入力になれていない人は、ダイアログボックスで作っていくといいでしょう。

C11をクリックして、COUNTIF関数ダイアログボックスを表示しましょう。

範囲には、次の検索条件が含まれている範囲なので、C列の得点である、C2:C7を設定します。

検索条件ですが、80点以上なので、「”>=80”」と入力します。比較演算子と数値を合わせて使うときには、「””(ダブルコーテーション)」が必要になります。

なお、比較演算子とセル番地を使うときには、「”>=”&セル番地」（例：”>=”&C3）という表示の仕方になります。

あとは、OKボタンをクリックして数式は完成です。

直接でなくても、80点以上の件数を数える方法はあります。

たとえば、E列の80点以上という列のように該当するデータをわかるようにしておいて、その結果の数を数えることでも求めることができます。

今回、E2には、
=IF(C2>=80,"○","")
というIF関数を使って、80点以上なら○。そうでなければ、空白。という数式を作っています。

なので、○の個数で何名該当しているかがわかるわけです。

そこで、空白以外ということで、COUNTA関数を使ってみると、どういう結果が算出されるのでしょうか？

結果は、6。

なんと空白も数えてしまっています。確認できたように、COUNTA関数は、数式を使って算出された結果の『空白』も数えてします。

このような場合にも、COUNTIF関数をつかって、「○」を数えるようにすればいいわけです。

【複数条件は、COUNTIFS関数】

条件が複数になった場合は、複数形ではありませんが、IFにSをつけた、COUNTIFS関数を使うことで算出することができます。

今回は、80点以上で、セミナー参加希望の件数を求めていきます。

C13に結果を出しますので、COUNTIFS関数ダイアログボックスを表示しましょう。

検索条件範囲1には、得点のC2:C7
検索条件1には、80点以上ということで、”>=80”
検索条件範囲2には、セミナー希望のD列で、D2:D7
検索条件2には、”○”と入力します。

数式はというと、
=COUNTIFS(C2:C7,">=80",D2:D7,"○")

【空白を数えるのは、COUNTBLANK関数】

COUNTIF関数で検索条件を「””（空白）」で設定しても空白セルを数えることはできますが、空白セルを数える専用の関数があります。

それが、COUNTBLANK関数。

このCOUNTBLANK関数は、引数に範囲を設定するだけで、空白を数えてくれますので、COUNTIF関数よりも簡単に空白を数えることができますし、計算結果が空白だったものも、空白として数えてくれます。

C14の数式は、
=COUNTBLANK(D2:D7)

【非表示に対応するにはSUBTOTAL関数・AGGREGATE関数】

行が非表示になると、COUNT系の関数では、対応されません。

非表示になった行を除いて算出させるには、SUBTOTAL関数・AGGREGATE関数を使う必要があります。

SUBTOTAL関数・AGGREGATE関数ともに、手入力することをお勧めします。

SUBTOTAL関数。

手入力していくと、入力補助が出てきますので、今回は、2がCOUNTとなっていますので、選択したくなりますが、非表示に対応しておりませんので、102のCOUNTを選択するようにします。100番台は、非表示に対応してくれます。

C15の数式は、
=SUBTOTAL(102,A2:A7)
AGGREGATE関数。

最初の入力補助は、集計方法。今回は2番のCOUNTを選択します。
オプションですが、今回は、単純に非表示の行に対応する、5番を採用します。

C16の数式は、
=AGGREGATE(2,5,A2:A7)

では、非表示にしてみましょう。

非表示に合わせて、対応してくれましたね。

このように、数えるだけでも様々な関数がありますので、現場に沿った関数を見つけられるといいですね。