Excel。以前からある、複数の文字列を統合する関数はCONCATENATE関数です。【CONCATENATE】

Excel。以前からある、複数の文字列を統合する関数はCONCATENATE関数です。

＜関数辞典：CONCATENATE関数＞

CONCATENATE関数

読み方： コンカティネイト　　

分類： 互換性 

CONCATENATE(文字列1,[文字列2],…)

複数の文字列を統合する　

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

Excel。手早く文字結合するには、CONCATENATE関数をつかってみましょう。【function:CONCATENATE】

Excel。手早く文字結合するには、CONCATENATE関数をつかってみましょう。

＜CONCATENATE関数＞

「&（アンパサンド）」でも文字結合をすることができますが、連結したい数が増えると、結合のたびに「&」を入力するのは、面倒になってきます。

そこで、CONCATENATE関数をつかうことで、「&（アンパサンド）」をつかったのと同じように文字結合をすることができます。

C2の数式は、

=CONCATENATE(A2," ",B2,"公")

と設定しています。

セル番地も使うことができますし、文字や数値も使うことができます。

ただ、CONCAT関数が登場したことで、CONCATENATE関数は、互換性関数になってしまいましたので、今後は、CONCAT関数に置き換わることになるかもしれませんね。

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

CONCATENATE関数の読み方は「コンカティネイト」です。

所属は、「互換性」です。

CONCATENATE関数の引数は、

CONCATENATE(文字列1,[文字列2],…)

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。CONCATENATE関数や&で結合すると表示形式が消えちゃう！【Display format】

Excel。CONCATENATE関数や&で結合すると表示形式が消えちゃう！

＜YEN関数・TEXT関数・FIXED関数＞

Excelのクセのために、色々と困ることがありますが、
今回は、表示形式とセルの結合についての対応方法を確認しておきましょう。

次の表があります。

どんな表なのかを説明すると、
E3に販売金額が入力されています。

そして、桁区切りスタイルが設定されていますので、
三桁区切りの”,”が設定されています。

F3には、消費税8%として、数式が設定しています。
=E3*8%

そして、桁区切りスタイルが設定されています。

G3には、販売金額と消費税の合算した結果が表示されていて、
=SUM(E3:F3)
という数式が設定されており、通貨スタイルが設定しています。

そして、C2に、請求額として、
請求額（消費税）というスタイルで表示したいわけです。

このような場合は、セルの結合をすればいいわけですから、
C2には、
=G3&"(内消費税"&F3&"円)"
という数式を設定すればいいはずです。

&（アンパサンド）でセルの結合を行うことができます。

ところが、
16200(内消費税1200円)
と数値に設定してあった、
桁区切りスタイルがなくなった状態で表示されてしまいました。

これは、Excelの特徴というか、
クセというか、&（アンパサンド）で結合すると、
表示形式が外れてしまうようになっているのです。

なお、セルを結合するには、CONCATENATE関数や、
office365のExcel2016から登場した、
CONCAT関数を使った場合でも、同じように、表示形式は外れてしまいます。

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

【表示形式を付けてから結合する】

表示形式が外れてしまうので、考え方として、
表示形式を付けてあげてから結合をするようにすれば、
この問題は解決することができます。

そこで登場するのが主に次の3つの関数です。
FIXED関数
この関数は、数値に桁区切りスタイルを付けることができる関数なので、
単純に桁区切りスタイルを付けたい場合には、この関数を使います。

YEN関数
通貨スタイル。￥（円マーク）を付けたい場合には、
このYEN関数を使うことで対応することができます。

TEXT関数
表示形式ではおなじみの関数ですね。
桁区切りスタイルは、FIXED関数でなくても、
このTEXT関数でも問題はありませんので、
TEXT関数だけ覚えておくのでもOKですね。

では、
¥16,200(内消費税1,200円)
と表示されているC3の数式を確認してみましょう。

=YEN(G3,0)&"(内消費税"&TEXT(F3,"#,##0")&"円)"

販売金額には、YEN関数を使うことで、
通貨スタイルを設定することができます。

消費税には、FIXED関数でもOKですが、TEXT関数だと、
TEXT(F3,"#,##0")
とすることで、桁区切りスタイルで、
数値を表示することができるようになります。

このように、表示形式が設定されているセルを結合する場合、
表示形式が外れてしまいますので、対応が必要となりますので、
表示形式を付けてから結合すると覚えておくといいでしょう。

Excel。合計値の数値をセル一つずつに分割して表示するには【Split character】

Excel。合計値の数値をセル一つずつに分割して表示するには

＜CONCATNATE&LEFT&RIGHT&COLUMN&SUM関数＞

現場には様々な帳票がありまして、その中には、
なんでそんな風にしちゃったのかなぁ～という帳票もあるようでして、
例えば、次のような帳票がその一例ですね。

2行目の金額が、一つのセルにあるのではなくて、
セルごとに数値が入力されている状況なのです。

たぶん、かつて使っていた紙の帳票のレイアウトを基に同じように作ったので
このようなことになってしまったと推測はできますが、
変更しようとしても、帳票番号などを取っていると変更もままなりません。

そこで、今回は、3行目の消費税と4行目の合計を算出させて、
セルごとに表示する方法を考えていきます。

【セルごとに分かれていては何もできない】

2行目の金額ですが、このままでは、2・9・4・6・1という数値でしかないので、
一つの数値にしないと、消費税も算出することができません。

G列に一つの数値にするための数式を作っていきます。

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

文字1には、万の位のB2を入力します。

同じように、一の位まで入力してOKボタンをクリックします。

29461と算出されました。

数式は、
=CONCATENATE(B2,C2,D2,E2,F2)
”&”で結合させてもOKです。

ただ、今は、文字型の数字になっているので、
×1をして数値型に変更しますので、数式を修正します。

=CONCATENATE(B2,C2,D2,E2,F2)*1

次に、G3に消費税を算出します。

算出した結果をセルごとに分けて表示させていきます。

今回は、小数点は切り捨てにすることにしますので、
ROUNDDOWN関数を使っていきます。

数値には、G2*8%
桁数は、小数点は不要なので、0と入力します。
OKボタンをクリックします。

G3の数式は、
=ROUNDDOWN(G2*8%,0)

G4には、合計を算出させますので、SUM関数を使って算出させましょう。

G4の数式は、
=SUM(G2:G3)

今のところ、このようになっています。

あとは、算出した結果をセルごとに振り分けて
表示させてあげればいいわけですね。

【一文字ずつ振り分けるのがポイント】

3行目の消費税の万の位である、B3に数式を作っていきます。
最初に、LEFT関数ダイアログボックスを表示しましょう。

仮に、文字列に算出した数値のG3を入力して、
文字数はセルひとつに一つの数値ですから、1としてしまうと、
万の位にも関わらず、千の位の2が表示されてしまいますので、
アイディアが必要になります。

左から1文字を抽出したいけど、空白があるわけですね。

そこで、RIGHT関数を使って、右側から指定した文字数を抽出して、
その左側の数値を持ってくるようにしてあげればいいわけです。

指定した文字数が変われば、桁数をスライドさせることが出来ます。

わかりにくいので、一度、RIGHT関数を使って動きを確認しましょう。

文字列には、
" "&$G3
と入力します。

スペースを結合させているのは、位がないケースの対策です。

スペース一文字でいいの？と思われた方もいるかもしれませんが、
最終的に一番左の文字を抽出させたいわけですから、
先頭が空白ならば別に構いません。

G3を$G3と複合参照にしているのは、
オートフィルで数式をコピーするためですね。

文字数には、
7-COLUMN()
スライドさせたいので、右側から何文字抽出するのかを指示させるのですが、
このように数値が変動する場合には、数式を列方向にコピーをしますから、
COLUMN関数を使ってあげると便利です。

7は、逆算して求めた値です。

F列の一の位を抽出したいわけですから、
1を求めたいF列はCOLUMN()で6なので、7-6で1というわけですね。

では、OKボタンをクリックして、オートフィルで数式をコピーしましょう。

B3の数式は、
=RIGHT(" "&$G3,7-COLUMN())
です。

それでは、このRIGHT関数の考え方を踏まえて、
改めてLEFT関数から数式を作っていきます。

OKボタンをクリックして、オートフィルで数式をコピーしましょう。

B3の数式は、
=LEFT(RIGHT(" "&$G3,7-COLUMN()),1)
ですね。

算出はできたのですが、数値が文字型になってしまっています。

単純に右揃えにすればいいのですが、
数式でクリアしたい方は数式を修正する必要があります。

B3には、” “というスペース（空白）という文字が入っていますので、
数式に×1としてしまうと、エラーが発生するので、IF関数を使って修正します。

=IF(LEFT(RIGHT(" "&$G3,7-COLUMN()),1)=" ","",LEFT(RIGHT(" "&$G3,7-COLUMN()),1)*1)

これで完成しましたね。

このように、なかなか厄介なので、
本来ならこのような帳票は避けたいところですね。

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関数の出番ということになりそうですね。

Excel。Slash。数値の間に【/】をいれて、日付にしたい時の方法をご紹介

Excel。数値の間に【/】をいれて、日付にしたい時の方法をご紹介

LEFT関数・MID関数・RIGHT関数& CONCATENATE関数

前回は、【/】スラッシュを削除して、日付を数値に変換する方法をご紹介しましたが、
今回はその逆で、
数値になっているデータを【/】スラッシュを入れて日付にしちゃう方法をご紹介します。

前回と逆のことをすればいいんだと思いきや、そうでもないところがポイントになります。

今回登場するのは、LEFT関数・MID関数・RIGHT関数が活躍します。

それでは、早速紹介していきます。
下記のような表があります。

C列には、誕生日が入力されていますが、数値の状態です。
これでは、日付での計算が出来ません。

この表示形式を日付に変更しても、
9999年12月31日をこえてしまうので、######という表示になってしまいます。

ちなみに、9999年12月31日のシリアル値は、2,958,465です。

ということで、表示形式では今回は太刀打ちが出来ません。

そこで、【/】スラッシュを入れることを考えていきます。
今回は左から4ケタが西暦で揃っております。

そして、5・6ケタが月を表していて、右から2ケタが日を表していますので、
それぞれを抽出する関数を使っていくのと、その抽出したデータを再結合する必要もあります。

では、D3をクリックして、文字結合をする関数。
CONCATENATE関数ダイアログボックスを表示しましょう。

文字列1には、西暦を抽出させますので、
LEFT関数をネストしていきますので、LEFT関数ダイアログボックスを表示しましょう。

文字列には、C3を入力しましょう。
文字数は、西暦に該当する4ケタを抽出させますので、4と入力します。

CONCATENATE関数を作成途中ですから、
ここでOKボタンをクリックしてはいけませんので、注意します。

数式バーの=CONCATENATEをクリックして、
CONCATENATE関数ダイアログボックスに戻りましょう。

文字列2には、”/”　スラッシュを入力します。

文字列3には、月に該当する2文字分を抽出したいので、
MID関数をネストしますので、MID関数ダイアログボックスを表示しましょう。

文字列には、C3と入力します。
開始位置は、5文字目ですから、5　と入力します。
文字数は、２桁分を抽出しますので、2　と入力します。

ここでも、CONCATENATE関数を作成途中ですから、
ここでOKボタンをクリックしてはいけませんので、注意しましょう。

それでは、CONCATENATE関数に戻りましょう。

文字列4には、”/”　スラッシュを入力します。

そして最後。
文字列5には、右から2桁分を抽出させますので、
RIGHT関数をネストしますので、RIGHT関数ダイアログボックスを表示しましょう。

文字列には、C3　を入力します。
文字数は、日が2ケタですから、2と入力します。

そして、OKボタンをクリックしましょう。

あとは、オートフィルハンドルを使って数式をコピーしましょう。

これで完成しました。このように数値を日付に変換することも出来ますので、
状況によって使ってみてはいかがでしょうか？

Excel。関数を知っていればいいという訳ではない。文字列結合は&が楽 CONCATENATE関数

Excel。関数を知っていればいいという訳ではない。
文字列結合は&が楽

CONCATENATE関数

下記のように、都道府県と市区町村を合わせた、
住所１という列を作成する場合、文字列結合を行いたいわけです。
そこで、登場する関数が、

CONCATENATE関数。

しかしまぁ…なんだけど、まずは、関数を紹介しましょう。

下記のようなデータがあるとします。

E2をクリックして、CONCATENATE関数のダイアログボックスを表示します。
文字列1に都道府県のC2をクリック
文字列2に市区町村のD2をクリック

これで、完成ですね。

ところが、この関数、実は…。ほとんど使わない。下記の方法が楽なので、
ということで、もう一つのやり方をご紹介します。

E2をクリックして、数式を作っていきます。

=C2&D2

これで、OK。完成です。要するに、文字列同士を&で接着したワケです。
何でもかんでも、関数を知っていればいいという訳ではないケースですね。
関数にとらわれず幅広くスキルを知っておきたいものですね。