Excel。空いている時間はいつ。複数人の出席可能時間を見つけたい【Gather everyone】

Excel。空いている時間はいつ。複数人の出席可能時間を見つけたい

＜IF+CONCAT関数+REPT関数＞

会議参加予定の空いている時間を見つけて、その日時で会議を開催したいと考えています。

下記のような予定になっています。

参加予定の3名。

それぞれ時間ごとに、○✕で、時間が空いているかどうかの確認はできます。

ただし、この3名がすべて○になっている時間を見つけようとしたら、大変です。

今回は、紹介用なので、3名で3日間の4時間ですが、人数が多くなればなるほど大変な作業になることは間違いありません。

IF関数をつかってみるとしたら、それも大変です。Aさんの10時は○かどうかを判断して、さらにBさんCさんの10時というような数式をつくるとしたら、面倒でしかありませんし、何より非効率です。

そこで、文字結合のCONCAT関数をつかうことで、簡潔に解決することができます。

では、B2をクリックして、CONCAT関数をつかった数式をつくります。

B2に設定した数式は、

=IF(CONCAT(B8,B14,B20)=REPT("○",3),"OK","")

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

これで、この3名が空いている時間は、

12月1日の10時と11時

12月2日の14時

ということがわかりました。

では、数式を確認しておきましょう。

全員空いているならば、OKと表示したかったので、IF関数をつかっております。

IF関数の論理式ですが、

3名の12月1日10時を確認したい。

そこで、CONCAT関数をつかって文字結合します。

CONCAT(B8,B14,B20)

こうすることで、○○○　のように文字結合することができます。

この結果が、○○○　と等しいかという論理式をつくるわけです。

今回は、3名なので、次のように数式をつくってもよかったのですが、

=IF(CONCAT(B8,B14,B20)=”○○○","OK","")

人数が多くなると、○の数が多くしなければなりません。

仮に、10名だと○が10個。

入力するのも大変ですが、ミスが発生しやすくなります。

そこで、文字を繰り返す関数のREPT関数をつかって、○を入力する作業を簡素化します。

REPT("○",3)

とすることで、○を3回繰り返すので、○○○という文字をつくることができます。

このように、CONCAT関数、さらにREPT関数をつかうことで、空いている時間を手早く見つけることができます。

Excel。範囲のすべてが空白の場合、行単位で塗りつぶしをしたい【All blank】

Excel。範囲のすべてが空白の場合、行単位で塗りつぶしをしたい

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

データの全てが空白の場合、わかりやすいように、そのデータ全体を塗りつぶして確認できるようにしたいのですが、どのようにしたら、いいのでしょうか。

今回はどのような表を用意しました。

1回目から5回目のデータすべてが空白だった、そのデータ全体、つまり行全体を塗りつぶしたいわけです。

条件で塗りつぶすということから、条件付き書式をつかうわけです。

そこで、問題になるのが、条件式です。

AND条件で、セルひとつずつが、空白かどうかを確認するのは、大変です。

また、COUNTBLANK関数で、空白の件数を求めて、全体の件数と空白の件数が合致するのかを確認するというのも、面倒です。

そこで、文字結合のCONCAT関数をつかうことで、楽に条件式を設定することができます。

A2:F7を範囲選択します。

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

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

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

=concat($B2:$F2)=""

あとは、書式ボタンをクリックして、塗りつぶしたい色を設定したら、OKボタンをクリックして、完成です。

条件式を確認しておきましょう。

=concat($B2:$F2)=""

ポイントは、引数です。

$B2:$F2　の列固定した複合参照にすることで、行全体を対象にすることができます。

CONCAT関数は、セル結合なので、結合した結果、空白ならばという条件式をつくればいいわけです。

なお、行固定の複合参照にすれば、列を対象に塗りつぶすことができます。

Excel。範囲全てが空白の場合、行単位で塗りつぶしを手早く設定するには【blank line】

Excel。範囲全てが空白の場合、行単位で塗りつぶしを手早く設定するには

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

該当するセルが空白の場合での条件付き書式ならば、そのセルだけが空白かどうかを確認すればいいわけです。

ところが、複数の範囲が対象になった場合、AND関数をつかって、空白かどうかを繰り返すような引数を設定するします。
そうすることで、逆に、対象範囲が増えれば増えるほど、煩雑なうえ、設定が面倒になってしまいます。

そこで、CONCAT関数をつかうと、手早く範囲全てが空白の場合という条件で対応することができます。

では、設定方法を確認しておきましょう。

A2:E6を範囲選択します。

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

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

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

条件の入力ボックスに、

=concat($B2:$E2)=""

と入力します。

あとは、書式ボタンをクリックして、塗りつぶしたい色を設定したら、OKボタンをクリックすれば、完成です。

CONCAT関数は、引数に設定した範囲のセル内にある文字列をすべて結合する関数です。

空白セルだけならば、「=””」とすることで、すべて空白なのかどうかの判定をすることができるというわけです。

Excel。文字列に統合や3-D集計は、対応していないので、CONCAT関数をつかいます。【string】

Excel。文字列に統合や3-D集計は、対応していないので、CONCAT関数をつかいます。

＜IF+CONCAT関数＞

当たり前ですが、文字列は合算したりすることはできません。

たとえば、次のシート。

Aさん・Bさん・Cさんのそれぞれの出席可能がわかる表があります。

「○」があるところが出席可能を表しています。

やりたいことは、3名が出席できる日時を、手早く確認したいわけです。

「○」ではなく、数値で判定されていれば、統合などの方法もあるのでしょうけども、文字列なので、つかえません。

IF関数とAND関数をつかってもいいですが、もっと多くの表の場合、AND条件が多くなり、可読性も悪化します。

そこで、IF関数とCONCAT関数をつかって、全員出席できる日時を確認していきます。

B2に次の数式を設定します。

=IF(CONCAT(B7,F7,J7)="○○○","開催OK","")

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

これで、10月3日10時は、予定が空いているので、開催できる日時を見つけることができました。

この数式のCONCAT関数ですが、CONCAT関数は文字結合をする関数です。

範囲選択するだけで、文字を結合してくれます。

すべて「○」。

つまり「○○○」ならば、出席できると判断することができるというわけです。

Excel。複数の文字列を連結するときにCONCAT関数をつかうと便利です。【CONCAT】

Excel。複数の文字列を連結するときにCONCAT関数をつかうと便利です。

＜関数辞典：CONCAT関数＞

CONCAT関数

読み方： コンキャット　　

分類： 文字列操作 

CONCAT(テキスト1,…)

複数の文字列を統合する関数です。

Excel。セルごとに分割された数値を合算し、その値もセルごとに分割したい【cell merging】

Excel。セルごとに分割された数値を合算し、その値もセルごとに分割したい

＜CONCAT・SUM・IFERROR・MID・LEN・COUNT関数＞

なんでそんなことしちゃったのという、Excelの帳票をみることがあります。

例えば、数値をセルごとに分割してある帳票。

そもそも、1500と分割しないでセル内にあれば、単純な和算で済むわけですが、セルごとに分割してあるわけですね。

このような場合、一の位で合算して、繰り上がったら…なんてやっていたら、桁数が増えたら大変というか、数式を考えるだけでも大変です。

最初にやることは、結合して1つのセルにまとめて数値にします。

A6に次の数式を作り、交通費を一つのセルにまとめます。

A6に設定した数式は、

=CONCAT(B2:G2)

同じように、A7に、宿泊費を一つのセルにまとめます。

A8には、合算値を算出したいので、A8に設定する数式は、

=SUM(A6:A7)

ところが、「0」と算出されてしまいました。

原因は、よくみると、A6とA7は、左揃えになっています。

これは文字型になっていることを表しています。

CONCAT関数は、文字結合をする関数なので、算出されたものは、文字になっていて数値ではないわけです。

そこで、A6とA7の数式のうしろに「*1」を追加します。

=CONCAT(D2:G2)*1

こうすると、文字型は文字型でも、数値文字型に変わるので、合算値を算出することができます。

あとは、どうやって、一つずつのセルに分割して表示させる数式を考えていきます。

合計のB4の数式は、

=IFERROR(MID($A$8,LEN($A$8)-COUNT(C1:$G$1),1)*1,"")

あとは、この数式をG4まで、オートフィルで数式をコピーすれば完成です。

数式が長くなったので、説明していきます。

B1:G1までの見出し行は、「十万・一万…一」と表示してますが、元は、普通の数値が入力してあって、表示形式をつかって、漢数字にしています。

別に直接、漢数字で入力しても問題はありません。

最初の「IFERROR関数」は、数値がない場合、#VALUEというエラーが発生するので、エラーならば空白にするようにしています。

MIDはセル内の文字列を指定の場所にある、指定した文字数分だけ抽出する関数です。

先程、合算値を算出したA8を指定します。

オートフィルで数式をコピーしますので、絶対参照を忘れずに設定します。

何文字目の文字なのかを指定します。

「LEN($A$8)-COUNT(C1:$G$1)」

LEN関数は、文字数を算出する関数です。これで合算値が何桁なのかを算出します。

その値から、C1:G1の数値の個数を算出した値を減算します。

桁数をずらす必要があるので、終点のみを絶対参照に設定しておきます。

今回の例としては、

LEN($A$8)は、「5」

COUNT(C1:$G$1)は、「5」

5-5=0なので、0文字目を抽出ということになるのですが、0文字目を抽出というのは、おかしいので、エラーが表示されるわけですね。

このように、ちょっと複雑な数式になっていますね。

帳票上の必要があるかもしれませんが、基本的には、数値を一つずつ、一つのセルごとに分割する場合には、アレコレ、アイディアが必要になりますね。

Excel。範囲選択で文字結合できる、CONCAT関数はとても便利です。【function:CONCAT】

Excel。範囲選択で文字結合できる、CONCAT関数はとても便利です。

＜CONCAT関数＞

文字結合できるCONCATENATE関数ですが、ちょっと不便な点がありました。

それは、複数のセルを結合したい時に、範囲選択で手早く結合できないこと。

それを補う形で登場したのが、CONCAT関数です。

E列には、

=CONCAT(B2:D2)

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

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

引数に範囲選択するだけで、文字結合できました。

F列のCONCATENATE関数で同じように算出しようとすると、

=CONCATENATE(B2,C2,D2)

という数式を設定することになります。引数の設定がセルごとに「,」（カンマ）で区切る必要があります。

そのため、セルの数が増えると、手早く文字結合することができません。

このようにCONCAT関数は、CONCATENATE関数よりも、使い勝手がいいようです。

なお、「=CONCATENATE(B2:D2)」とすると、文字結合できないことがわかります。

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

CONCAT関数の読み方は「コンキャット」です。

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

CONCAT関数の引数は、

CONCAT(テキスト1,…)

Excel。スタッフ全員の日程確認から全員参加可能日を見つけるいい管理方法はないの【Full participation】

Excel。スタッフ全員の日程確認から全員参加可能日を見つけるいい管理方法はないの

＜IF＋LEN＋SUBSTITUTE＋CONCAT関数＞

Microsoft TeamsやOutlookなどでスタッフ全員の会議参加日時をみつけることが、比較的容易に出来るようになりましたが、これをExcelでやりたい場合はどのようにしたらいいのでしょうか？

スタッフさん個人個人のシートが用意されています。

やりたいことは、全員が「○」の日時を探すということです。

今回は、3人分のシートですが、人数が増えるとか、項目がもっと細かく区分けされていたら、目視で確認するのは、とても大変な作業だといえます。

これからご紹介する数式をつかうと、このような結果が表示されます。

「OK」と表示されているところは、3人全員が「○」と報告してきたところです。短時間で、日程調整が出来るというわけです。

では、どのような数式をつくったのか、B4の数式をみてみましょう。

=IF(LEN(SUBSTITUTE(CONCAT(内藤:北沢!B4),"×",""))=3,"OK","")

ちょっと長い数式ですが、関数ごとに確認していきましょう。

数式の内側から確認していきます。

最初の登場する関数は、「CONCAT関数」。

文字結合する関数です。

CONCATENATE関数の進化系ですね。

CONCAT関数のところだけを算出してみます。

B4に、

=CONCAT(内藤:北沢!B4)

という数式を設定しました。

CONCAT関数が便利なところは、シート間で対応できるのと、範囲選択するだけで、文字結合できる点です。

スタッフごとのシートのB4の文字を結合させた結果が表示されています。

「○」が3個あれば、全員参加というのがわかるのですが、人数が多いと「○×」だらけで見つけるのも大変です。

そこで、「SUBSTITUTE関数」をつかって、「×」を消す作業をします。

そうすれば、「○」だけ残るので、見やすくなります。

B4の数式を

=SUBSTITUTE(CONCAT(内藤:北沢!B4),"×","")

と変更します。

SUBSTITUTE関数は、置換する関数です。

「×」を空白に置換するように数式を変更してみましょう。

「×」が消えて「○」だけが残りました。

「○」だけ残ったのですが、「○」が3個あれば、「OK」と表示するように修正すれば、もっとわかりやすくなります。

そこで、次のように修正します。

=IF(LEN(SUBSTITUTE(CONCAT(内藤:北沢!B4),"×",""))=3,"OK","")

LEN関数は、文字の数を算出する関数です。

「○」の数を算出します。

最後に、

IF関数をつかって、

LEN関数の結果が3（文字）だったら、「OK」と表示する

というように設定すれば、完成というわけですね。

新しく加わった関数をつかうことで、今までは算出するのに苦労していたことが改善できるかもしれませんので、数式を見直してみるのもいいのかもしれませんね。

Excel。ファイル添付時用に強固なパスワードを使って作りたいけどどうしたらいい？【Strong password】

Excel。ファイル添付時用に強固なパスワードを使って作りたいけどどうしたらいい？

＜RANDBETWEEN関数・IF+OR+AND+CHAR関数・CONCAT関数＞

ファイルを添付してメールなどで送信する場合、パスワードを付けて送信することが多々あります。

その場合のパスワードを同じにするのも、ちょっとどうかと思いますし、かといって、「Squt!kYmx9」のようなパスワードを、その都度、自分で考えるというのも、大変です。

そこで、Excelをつかって、パスワードを生成するシートをつくっていきます。

Excel VBAでマクロをつくってもいいのですが、作ることまでもなく、関数で対応できます。

考え方として、適当な文字を10個組み合わせたいわけですね。

なお、今回は重複OKとします。

直接ランダムな文字を表示させる方法はExcelにはありません。

まずは、使えそうな関数を考えみることにします。

文字は、文字コードという数値をもっていますから、数値から文字に変換する関数が、CHAR関数です。

その数値をランダムで表示することができるのが、RANDBETWEEN関数です。

この2つの関数があれば、どうにかなりそうですね。

ただし、注意しないといけないのが、文字が文字コードに綺麗に割り振られていない点です。

文字コードの48が「0」で57が「9」

文字コードの65が「A」で90が「Z」

文字コードの97が「a」で122が「z」

というように割り振られています。

そのため、ランダムの数値を48～122という単純な条件で算出するだけではダメということになるわけです。

58～64を除くなど、除外する必要があるわけです。

それを踏まえたうえで、次のようなランダムパスワードを作成するためのシートを作りました。

E1のパスワードは、4行目に算出したランダムな文字を結合させています。

E1に設定した数式は、

=CONCAT(A4:J4)

CONCAT関数は、文字結合することが出来る関数です。

CONCATENATE関数だと、範囲選択では文字結合することができません。

セルごとに「,（カンマ）」で区切る必要があるので、新しく登場したCONCAT関数を使うほうが便利です。

A3には、ランダムで数値を算出する関数。RANDBETWEEN関数をつかって、48～122の間の乱数を算出しています。

=RANDBETWEEN(48,122)

本当ならば、文字コードがない数値を算出させないようにしたいのですが、一筋縄ではいきませんので、単純に48～122の間の数値を表示させるようにしています。

逆にA4の数式は、文字コードに該当するようにしてあげる必要があります。

A4の数式は、

=IF(OR(AND(A3>=48,A3<=57),AND(A3>=65,A3<=90),AND(A3>=97,A3<=122)),CHAR(A3),"!")

数式が長くなっていますが、

文字コードの48が「0」で57が「9」

文字コードの65が「A」で90が「Z」

文字コードの97が「a」で122が「z」

に対応させるために、OR関数とAND関数をミックスしてつかっています。別にAND関数のみでも大丈夫です。

文字コード以外の数値だったら、「!」（感嘆符：エクスクラメーション）を表示するようにしております。

あとは、A3:A4を範囲選択して、まとめて、オートフィルで数式をコピーしていきます。

パスワードの文字数が10文字なので、それにあわせて数式をコピーします。

これで、ランダムパスワードを作成することができます。

あとは、運用上の注意で、RANDBETWEEN関数は、セルに文字を入力するなど、アクションをしてしまうと、値が変わってしまうので、ランダムパスワードを作成したら、すぐに、値のコピーをつかって、管理する必要があります。

Excel。セル内の文字をセルごとに一文字ずつ入力したい。またその逆は？とにかく楽したい。【Characters in cells】

Excel。セル内の文字をセルごとに一文字ずつ入力したい。またその逆は？とにかく楽したい。

＜MID&COLUMN関数とCONCAT関数＞

あるアンケート結果の表があります。

B列の回答欄は、問1から問5までの質問結果をまとめた状態です。

例えば、NO1の「54132」は、問1を「5」問2を「4」というように答えています。

しかしながら、結果を連結してしまっているので、データとして使い勝手が悪いので、次のように、セルごとに結果を表示させたいわけです。

当然、自力でコツコツと入力するとしたら、膨大な時間を費やしてしまうのは明らかです。

Excel VBAでマクロを作ってとか思いがちですが、ちょっとした関数を使えば、比較的容易に、セルごとに文字を入力することができます。

文字列を指定された位置から、指定された文字を抽出したいわけですね。

このような時には、『MID関数』がオススメです。

では、C2に次のような数式を設定します。

=MID($B2,COLUMN(A1),1)

数式をオートフィルでコピーすれば、完成します。

では、簡単にMID関数でつくった数式の説明をします。

最初の引数。

「$B2」。

元の文字列はB列にあります。

オートフィルでコピーするので、複合参照にしています。

2番目の引数には、「COLUMN(A1)」と設定しました。

この2番目の引数は、開始位置を設定するわけですが、C2の数式は、一文字目ですが、D2は、二文字目を抽出させるようにする必要があります。

要するに、開始位置をずらさないといけません。

どうにか、１・２・３…とオートフィルでコピーした時にずれてほしい。

列番号を使えば対応することができそうです。

列番号を算出するには、「COLUMN関数」を使うことで、算出できます。

3番目の引数は、何文字、抽出するの？ということですから「1」と設定すればいいわけです。

これで、セル内の文字を、一文字ずつセルに入力することができます。

さて、今度は逆で、セルごとに一文字ずつ入力されているのを、一つのセルにまとめるには、どうしたらいいのでしょうか？

問1が○とか×ならば、B2には、PHONETIC関数を使う方法もありますが、今回のように数値だと上手くいきません。

今回のようにセルの文字を結合させるのに便利な関数があります。

それが「CONCAT関数」。

このCONCAT関数は新しく登場した関数で、CONCATENATE関数が進化した関数です。

では、B2に次の数式を設定して、オートフィルでコピーしてみましょう。

B2の数式は、

=CONCAT(C2:G2)

たったこれだけです。

これで、一つのセルに、まとめることができました。

CONCATENATE関数では、この範囲選択で文字接続をすることができなかったので、少々面倒でした。

Excel。出席確認を簡単にしたい、各シートの同じ位置にあればマークをつけたい【Same position】

Excel。出席確認を簡単にしたい、各シートの同じ位置にあればマークをつけたい

＜IF+CONCAT関数＞

各シートの同じセルに○があれば、会議に出席可能で、×はNG。空白もNGという表があります。

自力で視認して、シートごとですべて○であることを確認するのは、容易ではありません。

当然シートの枚数が増えたら、さらに困難になるのは明白です。
しかも、数値ではないので、3-D集計を使って、合致しているかどうかもわかりません。

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

実は、Excel2019やOffice365のExcelならば、比較的簡単に確認することができるのです。

それでは、集計用のシートを用意します。

セル番地は、同じにしてあります。

【CONCAT関数の動きを確認】

B3に次のような数式を作成して、オートフィルを使って数式をコピーします。
=CONCAT(内藤:大塚!B3)

シート間で文字を結合することが、CONCAT関数では行うことができます。

Excel2016までは、CONCATENATE関数という文字結合する関数がありました。

Office365のExcelにもあります。
CONCAT関数は、CONCATENATE関数をバージョンアップした関数なのですが、特徴として、今回のようにシート間で文字結合をすることができるようになりました。

確認のため、CONCATENATE関数で算出してみましょう。
B3には、次の数式を作成します。

=CONCATENATE(内藤:大塚!B3)

オートフィルを使って数式をコピーしてみましょう。

CONCATENATE関数では、エラーになってしまいましたね。

このように、CONCATENATE関数では簡単に処理することができなかったわけです。

CONCAT関数では、シート間で文字を結合しただけにすぎませんので、わかりやすくしていきます。

B3の数式をIF+CONCAT関数で作り直します。

B3の数式は、
=IF(CONCAT(内藤:大塚!B3)="○○○","○","×")
とすれば、簡単に算出することができます。

【CONCAT関数がない場合】

Excel2016などCONCAT関数がないバージョンを使っている現場は当然多いわけですね。
その場合は、どのようにしたらいいのでしょうか？

COUNT関数系の引数の範囲には、シート間で設定することができないので、算出することができません。

要するに、COUNTIF関数とかで、「○」ならば数えるということができないわけです。

「○」なのか、そうでないのかを判断させる方法しかありませんので、IF+AND関数で対応する方法を使います。

B3の数式を次のように設定します。
=IF(AND(内藤!B3="○",高田!B3="○",大塚!B3="○"),"○","×")

これで、CONCAT関数と同じように算出することができます。

しかしながら、シートが増えるとAND関数の引数が多くなり非常に数式を作成するのにも、修正するのにも、作業が大変ですね。

Excelに限らずバージョンによって仕様が異なっていますので、それぞれのバージョンでより良い方法を使えるようになるといいですね。

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関数辞典 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。アンケート結果の集計で使える、セル内のこの文字だけを数える。セルにまとめる【Count only letters】

Excel。アンケート結果の集計で使える、セル内のこの文字だけを数える。セルにまとめる

＜PHONETIC関数・CONCAT関数・MID+COLUMN関数・LEN+SUBSTITUTE関数＞

簡単そうに思う処理であっても、意外とできない人が多いものって結構あるのですが、今回は、アンケート結果の集計とかで知っていると便利なものをご紹介していきましょう。

【回答を一つのセルにまとめる】

次の表があります。

アンケート結果が、D列からH列にそれぞれの回答が入力されています。

これをC列の回答欄である、一つのセルにまとめたい。

すなわち、回答者1は、○×○×○と回答欄のC2に入力したい場合どのようにしたらいいのでしょうか？

CONCATENATE関数やあるいは、”&”による文字結合ということを考えるかもしれませんが、このケースでは、『PHONETIC関数』を使えば簡単に算出することができます。

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

参照に、D2:H2
と入力してOKボタンをクリックして、オートフィルで数式をコピーしましょう。

C2の数式は、
=PHONETIC(D2:H2)
となっています。

このように、一つのセルにまとめることができました。

フリガナでお馴染みのPHONETIC関数を使うというのは面白いアイディアですよね。

また、office365のExcel2016で新しく加わった、CONCAT関数を使うことでも、同じように算出することができます。

数式は、
=CONCAT(D2:H2)
としても算出することができます。

このCONCAT関数は、CONCATENATE関数の進化版の関数で、文字結合をすることができる関数なのですが、CONCATENATE関数とは異なり、範囲選択して範囲内のセルを結合することができます。

【一つのセルの文字をセルごとに入力する】

次の表のように、今度は、逆に、一つのセルにまとめられたデータを一文字ずつ、セルに入力するにはどうしたらいいのでしょうか？

このようなケースでは、MID関数を使うことで簡単に算出することができます。

D9をクリックしてMID関数のダイアログボックスを表示しましょう。

文字列には、$C9
複合参照にしているのは、このあと、オートフィルで数式をコピーからですね。

開始位置ですが、左側から1文字ずつズレた文字を検索させますので、列番号を算出する、COLUMN関数を使って、そこから-3することで、1をつくれますので、
開始位置には、
COLUMN()-3
文字数は、1文字ずつ検索させますので、
1
OKボタンをクリックして、オートフィルで数式をコピーして完成ですね。

なお、D9の数式には、
=MID($C9,COLUMN()-3,1)
という数式が設定されています。

これで完成しました。

【セルの中の特定の文字だけを数える】

アンケート結果を一つのセルにまとめた状態の表があります。

回答を一つずつのセルにいれて、特定の文字を数えるならば、全く問題はありませんが、このように、例えば回答のセルの中に○がいくつあるのか、数えたい場合にはどのようにしたらいいのでしょうか？

セルの中にある特定の文字を数える関数はありません。

なので、次のように数式をD16に作ってみましょう。

=LEN($C16)-LEN(SUBSTITUTE($C16,D$15,""))
LEN関数はセルの中の文字の数を数える関数です。

まずは、セル全体の文字数を数えます。

そして、特定の文字を空白に置換して、その残った文字数を数えたものを、先程LEN関数を使って算出した結果から減算してあげれば、セルの中の特定の文字数を数えることができます。

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

今回は、D15に○がはいっているので、この文字を、空白に置換させます。

そしてLEN関数と合わせて使うことで、求めることできます。

このように、アンケート結果などの集計で使えそうなものはたくさんありますので、少し知っているだけでも作業効率が改善できるかと思いますので、使ってみてください。

Excel。CONCAT関数は、CONCATENATE関数の進化版です。Office365 Excel2016【CONCAT】

Excel。CONCAT関数は、CONCATENATE関数の進化版です。Office365 Excel2016

＜CONCAT関数＞

文字列を結合する時に使う関数。CONCATENATE関数。
この進化版の関数が、Office365のExcel2016に登場しました。
その関数の名前は、CONCAT関数。

基本的な動きは、CONCAT関数もCONCATENATE関数も大差はないのですが、
当然変わったことがあったから、関数が増えたわけなので、
CONCAT関数の特徴を確認しておきましょう。

次の表があります。

例えば、A2の東京という文字からA7の三島という文字が入っているセルを
縦方向に結合した結果を表示したいとします。

まずは、&を使っても文字列を結合することが出来ますので、
D列から作ってみると、
D2には、次の数式が完成します。

=A2&A3&A4&A5&A6&A7

数が増えると数式を作るのが大変ですね。

結果はこのようになりました。

文字結合の基本ですよね。

次に、E列に、CONCATENATE関数で同じように文字結合をしていきますので、
E2をクリックして、CONCATENATE関数ダイアログボックスを表示しましょう。

文字列1からA2・A3と順々にセルを設定していき、
A7まで設定したら、OKボタンをクリックしましょう。

きちんと文字結合した結果が表示されていますね。

E2の数式は、
=CONCATENATE(A2,A3,A4,A5,A6,A7)
となっています。

それでは、新しく登場した、
CONCAT関数ではどうなるのかを確認しておきましょう。

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

テキスト1にA2を設定して、A7まで設定したら、
OKボタンをクリックしましょう。

こちらも、問題なく文字結合できましたね。

F2の数式は、
=CONCAT(A2,A3,A4,A5,A6,A7)

これでは、ただ、名前が変わった、
名前が短くなっただけでは？と思ってしまいますが、
CONCAT関数は、セル数が増えれば増えるほど、
その威力が増す関数なのです。

例えば、文字結合したいセルが50個あった場合、
引数は50個と手で入力しても、マウスを使っても、非常に大変ですし、
ミスが発生する可能性も高まります。

そこで、範囲選択して、
その範囲の文字列を結合できるようになったのがCONCAT関数なのです。

CONCATENATE関数では出来ないのでしょうか？確認してみましょう。

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

文字列1に、A2:A7と入力して、OKボタンをクリックしましょう。

残念ながら、文字結合をすることは出来ませんでした。

CONCAT関数で確認しますので、
F3をクリックしてCONCAT関数ダイアログボックスを表示しましょう。

テキスト1にA2:A7を設定して、OKボタンをクリックしましょう。

F3の数式は、

=CONCAT(A2:A7)

それでは、結果はどうなったのでしょうか？

先程と同じように、文字結合が出来ていますね。
なので、セル数が50個という場合でも範囲選択するだけで、
簡単に文字結合をすることができます。

なので、B列に、「駅 」（駅+半角スペース）をいれておいて、
F4にCONCAT関数を使って、範囲を、A2:B7として作成してみましょう。

F4の数式は、
=CONCAT(A2:B7)

結果は、このようになりました。

まず、行で結合して、次の行を結合してくれていますね。

このように、データが増えた文字結合をする場合は、
CONCAT関数の出番ということになりそうですね。