Excelで文字・日付を数式・関数で楽に入れる方法。CONCAT・CONCATENATE・＆。

Excelで資料をつくるとき、文字・日付を数式や関数で入れると楽でミスが減ります。

※自宅にて　Pixel 3

Excelで、文字をつなげる方法

Excelでは文字と文字をつなげることができます。
これをうまく使うと、効率的に文字や文章を入れることができるので抑えておきましょう。

たとえば、セルの中に「2019年」と「決算」という文字をつなげて、「2019年決算」としてみます。
その場合、数式を使いますので、数式の始まりを示す「=」を入れて、「2019年決算」を入れればいいはずです。
しかしながら、普通にこうやって入れてしまうとエラーが出ます。

Excel をはじめとする IT の世界では文字は、ダブルコーテーションで囲むというルールがあるのです。
（シングルコーテーションの場合もあります）
これは、プログラミングをするときにも役に立ちますのでぜひ覚えておきましょう。

また、数式の始まりの=は半角英数モードで入れなければいけません。
こうなってしまうとダメです 。

ダブルコーテーションも英数モードで半角です。
ダブルコーテーションが英数モード、2019年決算が日本語モード、再度ダブルコーテーションが衛星英数モードと切り替えるのは大変なので、ダブルコーテーションを2つ入れ、その間に日本語モードで文字を入れるというのもおすすめです。

この「2019年決算」というのものを「2019年」と「決算」とに分けてつなげて入れてみます。
文字をつなげるには、＆　を使います。
=”2019年”&”決算”と入れれば文字をつなげることができるわけです 。

2019年と決算の間にスペースを入れたい場合は、ダブルクォーテーションで囲んだスペースを ＆ でさらにつなげば次のようにできます 。

文字をつなげるにはこのように&で、どんどんつなげていけばいいわけです。
しかしながら2つや3つならまだいいのですが、これが4つ5つ6つ・・・と増えていくと、&でつなぐのも面倒くさくなります。
そこで関数を使ってみましょう。

Excelで数字を足すときは＋をつなげていけばいいのですが、たくさんつなげるときは面倒くさくなるので、SUM関数を使うわけです。

同じような考え方で&を使うのがめんどくさいので、ここではつなげる関数を使えます 。
文字をつなげる関数はCONCATENATE（コンカチネート）です 。

=CONCATENATE（〇,△,□）と、カンマで区切っていけば、文字をかんたんにつなげることができます。

Office 365または Excel 2019を使っている場合は、CONCAT（コンカット）関数を使うことができ、こちらのほうがシンプルで見た目もすっきりしているのでおすすめです。

 

Excelの数式で文字を入れる方法の基本

では、この「2019年」というものを他のセルに入れてそれを使ってみましょう。
たとえばセル A1に、「2019年」と入れてセルA1を数式に使えばこうなります。

こうしておけばセルA1を変えるだけで、それを使った文字を変えることができるのです。
2019年決算という言葉を複数のシートや複数のセルに入れていた場合もセルA1を変えればいっぺんに変更することができます。
2020年になっても2021年になっても、かんたんに変更ができますし、なおかつミスがありません。

ここで注意したいのはセル A1にはにはダブルコーテーションをつけないことです。
ダブルクォーテーションをつけてしまうと A1 という文字になってしまいます。

 

 

ExcelのTEXT関数で、日付を数式で使う

 

このしくみを日付を使ってやることもできます。
たとえば、セル A1に決算の始まりの日付、セル A2に終わりの日付を入れておき、これを使って粘土を表示してみましょう。

「2019年4月1日～2020年3月31日」というものを表示してみます。
CONCAT関数（使えない場合はCONCATENAGE）を使えば、セル A1と　～　という文字、そして セルA2をくっつければいいはずです。

しかしながら結果はこうなってしまいます。

この43556というのは2019年4月1日を1900年1月1日からカウントした数です。
セル A1には、この43556が入っており、それを日付という書式で見え方を変えているだけなのです。
数式で使った場合は、この43556の素の状態になってしまいます。
セル A1でCtrl＋1を押し、セルの書式設定を見てみると、日付という書式です。

この書式を標準にすると43556となります 。

こういった場合、日付を数式でそのまま使いたいのであれば、日付の書式を指定する TEXT関数を使いましょう。

= TEXT（〇、△）で、〇を△の書式で表示するという意味です。

セル A1にTEXT関数を入れて、書式は”yyyy/m/d”と設定します 。

yyyy（YEAR）が西暦、yyだと西暦下二桁 、mが月、mmだと0付きに、ddが日、ddだと0付きになるというルールです。

同様に後半の方も交えてみましょう 。

=CONCAT(TEXT(A1,”yyyy/m/d”),” ~”,TEXT(A2,”yyyy/m/d”))

CONCATを使えないバージョンの方は、こちらです。
=CONCATENATE(TEXT(A1,”yyyy/m/d”),” ~”,TEXT(A2,”yyyy/m/d”))

年月日を使いたいときはこのように変えます 。

こういったしくみをうまく使えば次のような表示も可能です
=CONCATENATE(“第”,A3,”期 (“,TEXT(A1,”yyyy年m月d日”),” ~”,TEXT(A2,”yyyy年m月d日”),”）決算報告書”)

なお、もちろん元号(和暦。平成、令和）にすることもできます。
元号の場合は yyyy を ggge に変えましょう 。

平成31年4月1日～令和2年3月31日という違和感がある表示になってしまいますが。

次の事例では長めに文字をつくっています。

=CONCAT(TEXT(data!$B$3,”yyyy年m月”),”期の業績”,”(“,TEXT(data!B2,”yyyy年mm月dd日”),”～”,TEXT(data!B3,”yyyy年mm月dd日”)&”)”)

別シートにこのようなデータをつくっておき、それを参照して文字をつくっているわけです。
ここを変えれば、数式に入れているすべてに反映されます。

こういったところも数式です。

 

セルの数字や文字を使ってこのような文章にすることもできます。
=CONCATENATE(TEXT(data!B3,”yyyy年m月”),”期の売上高は”,TEXT(C7,”#,###0円”),”であり前期の”,TEXT(C8,”#,###0円”),”より”,TEXT(C7-C8,”#,##0円”),”の増減となった”)

 

桁区切りの数字は、やはり TEXT 関数を使います。
もうちょっと工夫するとこのように単位を変え、If で増えたか減ったかを判断して増加か減少かを表示するといったことも自由自在です。

=CONCATENATE(TEXT(data!B3,”yyyy年m月”),”期の売上高は”,TEXT(ROUNDDOWN(C7/100000000,1),”#,###0.#億円”),”であり前期の”,TEXT(ROUNDDOWN(C8/100000000,1),”#,###0.#億円”),”より”,TEXT(ROUNDDOWN((C7-C8)/100000000,2),”#,##0.##億円”),”の”&IF(C7>C8,”増加”,”減少”)&”となった”)。

めちゃくちゃ長い数式になってしまいますが、一度入れておけば、自動的にこの文章をつくってくれます。
もし繰り返しやるのであれば入れておいて損はないでしょう。
これを毎回変更すると結構大変です。
文字をつなげる方法セルの参照をうまく使う方法などを押さえておきましょう

 

---

■編集後記

昨日は、終日決算、税務申告。
無事ネット提出し、夜は、日比谷公会堂（野外音楽堂）のイベントへ。
はじめていきました。
面白いものを知ったので、早速予約して明日行ってきます。

■昨日の「1日1新」

日比谷公会堂

■昨日の娘日記
朝、リビングで仕事していると、寄ってきて「おしまい」とPCを閉じようとすることが増えてきました。
昨日は、それでもやっているとしくしく泣きだし‥‥‥。
場所を変えねばかもです。