Excel。数値を右揃えでセルに1文字ずつ振り分けたい【One character】

Excel。数値を右揃えでセルに1文字ずつ振り分けたい

＜MID+TEXT+SEQUENCE+LEN関数＞

あるフォーマットがあって、入力したデータを1セルに1文字ずつ振り分けて入力したい

A列に数値や文字のデータを入力しております。

あいうえお　ならば、B4:F5にかけて、1セル1文字ずつ振り分けて入力したいわけです。

そこで、B2にはどのような数式を設定したかというと、

=MID(A2,SEQUENCE(1,LEN(A2)),1)

この数式で、対応することができます。

この数式を確認しておきましょう。

MID関数は、文字列の中で指定した場所から指定した文字数を抽出する関数です。

最初の引数は、文字列なので、A2

2つ目の引数は、開始位置。ここにSEQUENCE関数を使っています。

SEQUENCE関数のところは、あとで説明します。

3つ目の引数は、文字数。抽出したい文字数なので　1

ポイントになるSEQUENCE関数です。

SEQUENCE関数は、数列を返す関数です。

最初の引数は、行。1行内に収めますので、1

2つ目の引数は、列。文字数の列が必要です。

そこで、文字数を求めることができるLEN関数をネストします。

あいうえお　ならば　5　という具合です。

このSEQUENCE関数は、スピル機能に対応した関数なので、オートフィルで数式をコピーしなくてもA列の文字数分列を作ってくれます。

このように、

=MID(A2,SEQUENCE(1,LEN(A2)),1)

という数式で、左側から1文字つずつ振り分けて入力することができます。

ただ、数値の場合は、左詰めではなくて、右詰にしたいわけです。

あいうえお　のような文字列ならばいいのですが、どうやったら、123などの数値を右詰にすることができるのでしょうか。

TEXT関数をつかって、次のように修正してみました。

=MID(TEXT(A2,"0000000"),SEQUENCE(1,LEN(TEXT(A2,"0000000"))),1)

これで、右詰めにすることはできました。

TEXT関数をつかってしまったので、文字型になっています。セル内が左揃えになっていますので、右揃えに修正すればOKです。

ここまでで、いいとは思いますが、0は消したいよね。とさらに欲もでてきます。

0を消すにはIF関数をつかうのが、一番わかりやすい方法です。

=IF(MID(TEXT(A2,"0000000"),SEQUENCE(1,7),1)="0","",MID(TEXT(A2,"0000000"),SEQUENCE(1,7),1))

0だったら空白、そうでなければ、そのままという判断分岐をさせます。

欠点は、可読性が悪くなりました。

LET関数を使う方法もあることはありますが、ちょっと苦手な人も多いようなので、参考までですが、LET関数をつかうと、可読性も向上します。

=LET(t,TEXT(A2,"0000000"),m,MID(t, SEQUENCE(1,7),1),IF(m="0","",m))

Access。クエリ。住所から都道府県を抽出するにはどうしたらいい【prefectures】

Access。クエリ。住所から都道府県を抽出するにはどうしたらいい

＜Access＞

住所フィールドには、都道府県名から建物名まで入力されているデータなので、都道府県をそのデータから抽出したい場合、Accessでは、どのようにしたらいいのでしょうか。

Excelの場合は、IF関数をつかってもいいのですが、次の方法でも、住所から都道府県名を抽出することができます。

C列には住所が入力されています。D列に都道府県を抽出したので、D2に数式を設定します。

=LEFT(C2,(MID(C2,4,1)="県")+3)

数式を設定後、オートフィルで数式をコピーします。

これで、都道府県を抽出することができます。

そもそも、なんでこんな数式をつくらないといけないのかというと、4文字目県問題があるからです。

4文字目県問題とは、神奈川県・和歌山県・鹿児島県のことを指します。

この3件だけ、県が左から4文字目にあります。

ほかの県や北海道・東京都・大阪府・京都府はすべて左から3文字目が区切り位置になっています。

そのため、4文字目が県かどうかの判断が必要だというわけです。

この数式のポイントは、

MID(C2,4,1)="県"

左から4文字目が県だったら、TRUE。

そうでなければ、FALSEを返します。

ExcelではTRUEを1、FALSEを0と定義しています。

4文字目が県ならば1　その値に+3すれば4になります。

そして、LEFT関数をつかうことで、左から4文字目までを抽出するという数式をつくることができます。

これで、都道府県を抽出することができるわけですが、Accessではどのようにしたらいいのでしょうか。

AccessにはIIF関数が用意されていますが、今回紹介したLEFT+MID関数の組み合わせと同じようできるといいのですが。

Accessでの確認をしておきます。

T_都道府県というテーブルの住所フィールドから都道府県を抽出するクエリをつくります。

クエリデザインをつかいます。

Excelと同じ数式を演算フィールドでつくってみました。

都道府県: Left([住所],(Mid([住所],4,1)="県")+3)

実行してみます。

残念ながら、うまく抽出できません。

神奈川県が「神奈」というように、左から4文字目どころか2文字目までで抽出されていることがわかります。

なんでこのようになったのかを確認するために、引数内のMid関数だけの演算フィールドをつくって確認してみます。

Mid関数確認: (Mid([住所],4,1)="県")

実行してみます。

 

AccessではTRUEを「-1」と定義されているので、左から2文字目までしか抽出してくれなかったわけです。

ならば、「+1」となるように演算フィールドを修正すればいいわけです。

都道府県: Left([住所],((Mid([住所],4,1)="県")*-1)+3)

「-1」を「1」にしたいだけなので「*-1」とマイナス1をかけてあげればいいわけです。

では、実行してみましょう。 

これで、住所フィールドから都道府県名を抽出することができました。

Excel。数値を右詰で1セル1桁で表示するには【1 digit】

Excel。数値を右詰で1セル1桁で表示するには

＜REPT+LEN関数とMID+SEQUENCE関数＞

請求書や領収書などの金額を1セル1文字で入力されたものをつくりたい場合、どのようにしたら、手早く、1セル1文字で表示することができるのでしょうか。

次の表をご覧ください。

A1の値をB3:I3の1つずつのセルに1桁ずつ表示したいのですが、どのようにしたら、効率よく表示することができるのでしょうか。

このような場合、SEQUENCE関数をつかうことで解決します。

なお、SEQUENCE関数とは、指定した範囲の連続した数値を自動的に生成する関数です。

SEQUENCE関数にMID関数を組み合わせすことで対応できます。

では、B3に次の数式を設定します。

=MID(A1,SEQUENCE(1,8),1)

1セルに1文字ずつ入力することができました。

数式を確認してみましょう。

MID関数は、指定した文字列の中から、任意の位置から指定した文字数分だけ文字を抽出する関数です。

最初の引数は、文字列なので、A1を設定します。

2つ目の引数は、開始位置です。

開始位置に、SEQUENCE関数をつかいます。

SEQUENCE関数はあとで説明します。

3つ目の引数は、文字数。

1文字ずつなので、1を設定します。

2つ目の引数につかっていた、SEQUENCE関数を見ていきましょう。

SEQUENCE関数の最初の引数は、行です。1行で表示したいので、1と設定します。

2つ目の引数は、列です。

今回は、最大8桁までの数値に対応することにしたので、8と設定しました。

SEQUENCE関数は、スピル対応の関数なので、自動的に数式を拡張してくれます。

MID+SEQUENCE関数で対応することができたのですが、よくみると、左詰になっています。

一番右側に「円」と表示させているので、できたら、右詰にしたい。

A1の値を修正したものがB1にあります。

B1の数式は、

=REPT("　",8-LEN(A1))&A1

A1は6桁しかありませんでした、8桁にすれば解決できますが、０をつけて00123456としても、0は表示されません。

よって、0を先頭の前につけても8桁にすることはできません。

そこで、空白と合体させることで対応することができるようになります。

何文字分の空白を追加すればいいかを判断したいので、REPT関数をつかって、空白文字を繰り返します。

数値が何桁なのかは、LEN関数をついかいます。

あとは、先ほど設定した

=MID(A1,SEQUENCE(1,8),1)を

=MID(B1,SEQUENCE(1,8),1)

と修正して完成です。

Excel。文字列の任意の位置から文字を取り出せるのが、MID関数です。【MID】

Excel。文字列の任意の位置から文字を取り出せるのが、MID関数です。

＜関数辞典：MID関数＞

MID関数

読み方： ミッド　　

分類： 文字列操作 

MID(文字列,開始位置,文字数)

文字列の任意の位置から文字を取り出す

Excel。数値を一桁ごとセルにわけて表示して、桁の不足分はゼロで補いたい【character per cell】

Excel。数値を一桁ごとセルにわけて表示して、桁の不足分はゼロで補いたい

＜MID＋TEXT＋SEQUENCE＋LEN関数＞

銀行の入出金用紙などで、口座番号を一桁ずつ右から埋めていくわけですね。

次の表のように、Excelで手早くやるにはどうしたらいいのでしょうか。

例えば、5桁として、それに満たない場合には、「0（ゼロ）」をいれるようにもしたいわけです。

これを、SEQUENCE関数を中心とした、一つの数式で、手早く処理することができます。

C2には、

=MID(TEXT(A2,"00000"),SEQUENCE(1,LEN(TEXT(A2,"00000"))),1)

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

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

MID+SEQUENCE+LEN関数の組み合わせで、1つのセルに1文字ずつわりふることができます。

=MID(A2,SEQUENCE(1,LEN(A2)),1)

とすれば、文字列の場合ならば、対応できるのですが、数値だと、次のように、左側から割り振ってしまいます。

そのため、表示形式のTEXT関数をつかって対応する必要があるというわけです。

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

最初のMID関数は、指定の位置から文字列を抽出することができる関数です。

最初の引数が「文字列」なので、A2を設定します。

ただ、桁が足らない場合、「0（ゼロ）」を表示したいので、TEXT関数をつかって、5桁にするようにします。

なので、最初の引数は、「TEXT(A2,"00000")」と設定します。

2つ目の引数が「開始位置」です。

ここに、SEQUENCE関数をつかいます。

SEQUENCE関数の説明は後にしまして、MID関数の3つ目の引数は「文字数」です。何文字抽出するかということなので1文字ずつなので「1」と設定します。

さて、SEQUENCE関数の説明ですが、

SEQUENCE関数の最初の引数は、「行」です。複数行にまたがることはありませんので「1」と設定します。

2つ目の引数は、「列」です。何列必要なのかという、列数を設定することで、スピル機能によって、自動的に2文字目3文字目と表示してくれます。

そこで、LEN関数をつかって、文字数=列数を設定することができますので、LEN(TEXT(A2,"00000"))とします。

あとの引数は省略できますので、省略。

これで、数値の場合対応することができるというわけです。

Excel。文字列をセルに1文字ずつに分けて表示するには、どうするの【one character per cell】

Excel。文字列をセルに1文字ずつに分けて表示するには、どうするの

＜MID+SEQUENCE+LEN関数＞

SEQUENCE関数の登場で、今までは複雑な数式、あるいは、中間結果を算出してから結果を求めるなどしていた数式を、シンプルな数式にすることができます。

今回は、SEQUENCE関数をつかって、手早く、文字列をセルに1文字ずつに分けて表示する方法をご紹介します。

A列には、サンプルの文字列を用意しました。

文字数はバラバラ。

A5は、「立花 宗茂」と入力してあって、苗字と名前の間に半角スペースが入力されています。

C2に、SEQUENCE関数をつかった数式を設定します。

=MID(A2,SEQUENCE(1,LEN(A2)),1)

あとは、C5まで、オートフィルで数式をコピーするだけで、文字列をセルに1文字ずつに分けて表示することができました。

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

最初のMID関数は、指定の位置から文字列を抽出することができる関数です。

最初の引数が「文字列」なので、A2を設定します。

2つ目の引数が「開始位置」です。

ここに、SEQUENCE関数をつかいます。

SEQUENCE関数の説明は後にしまして、MID関数の3つ目の引数は「文字数」です。

何文字抽出するかということなので1文字ずつなので「1」と設定します。

さて、SEQUENCE関数の説明ですが、

SEQUENCE関数の最初の引数は、「行」です。

複数行にまたがることはありませんので「1」と設定します。

2つ目の引数は、「列」です。

何列必要なのかという、列数を設定することで、スピル機能によって、自動的に2文字目3文字目と表示してくれます。

そこで、LEN関数をつかって、文字数=列数を設定することができますので、LEN(A2)とします。

あとの引数は省略できますので、省略。

これで、文字列をセルに1文字ずつに分けて表示することができるというわけです。

Excel。読み込んだ時間が5桁6桁の数値だったので時間にするにはどうしたらいい。【clock】

Excel。読み込んだ時間が5桁6桁の数値だったので時間にするにはどうしたらいい。

＜TEXT+TIME+MID関数＞

データを読み込んでみたら、時間のデータが、なんと数値になっていた。

しかも、0～9時台は、5桁の数値。

それ以降は6桁の数値として、入力されていることがわかりました。

どうしたら、時間に変更することができるのでしょうか。

C2に設定した数式は、下記のようになります。

=TEXT(TIME(MID(TEXT(A2,"000000"),1,2),MID(TEXT(A2,"000000"),3,2),MID(TEXT(A2,"000000"),5,2)),"h:mm:ss")

数式自体は、長いのですが、やっていることは、同じようなことをやっていますので、一つずつ確認していきます。

MID関数は、指定した文字列から指定した文字数目から、指定の文字数を抽出する関数です。

MID関数の引数に、TEXT(A2,"000000")　とTEXT関数をつかっているのは、5桁の場合、つまり0時台～9時台までの場合、頭に「0（ゼロ）」をついかしたいわけです。

数値の前に「0」を付けることはできませんので、表示形式のTEXT関数をつかって、5桁の場合0を付ける作業をしています。

6桁はそのままです。

これで、5桁のデータも6桁にすることができましたので、時・分・秒のデータを抽出しますので、MID関数をつかうというわけです。

あとは、TIME関数で、時・分・秒のデータを結合させれば、完成です。

ただ、今回その外側にTEXT関数を設定しているのは、AM/PM表示になってしまいます。

h:mm:ss形式で表示したかったので、TEXT関数をつかって、表示形式を整えてみました。

そもそも、時間として入力されていればいいわけですが、もし数値だった場合は、手間がかかってしまいます。

Excel。IF関数を使わなくても住所から都道府県を簡単に抽出することができます【Prefectures】

Excel。IF関数を使わなくても住所から都道府県を簡単に抽出することができます

＜LEFT+MID関数＞

都道府県から建物名まで、一つのセル内に入力されている場合、都道府県だけを別のセルに抽出したい場合、残念ながら簡単に抽出することができません。

フラッシュフィルとかで抽出できればいいのですが、まだできません。

やはり、関数をつかって抽出するのが、簡単なようです。

上記の表のように、B列に住所が入力されていて、C列に都道府県を抽出する場合、C2には、次の数式を設定するだけで、抽出することができます。

=LEFT(B2,(MID(B2,4,1)="県")+3)

たったこれだけで、あとは、オートフィルで数式をコピーすると都道府県を住所から抽出することができます。

なんとなくIF関数を使うイメージがありますが、IF関数をつかわなくても、LEFT関数とMID関数を組み合わせてつかうだけで、抽出することができます。

さて、そもそも、住所から都道府県を抽出することが面倒なのかというと、「神奈川県・和歌山県・鹿児島県」問題があるからです。

要するに、この3県だけが、県まで含めて4文字なわけです。

北海道も京都府や大阪府。

そして東京都も3文字です。

全部が3文字ならば、LEFF関数をつかって、左から3文字まで抽出とすればいいのですが、そういうわけにはいきません。

なので、左から4文字目が「県」だったら、左から4文字を抽出して、それ以外は左から3文字を抽出する数式を作るわけです。

そのため、IF関数をつかって数式をつくってもいいわけですが、さらに簡単な数式にしたのが、今回のLEFT+MID関数の方法というわけです。

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

=LEFT(B2,(MID(B2,4,1)="県")+3)

LEFT関数は、左から引数内で指定された文字数を抽出する関数です。

1つ目の引数「B2」は対象となるセル番地なので、B2ですね。

2つ目の引数が、左から何文字目まで抽出するのか指示するわけです。

そこで、MID関数をつかうことで、4文字目が「県」かどうかを判定さえています。

それが、

MID(B2,4,1)="県"

です。

そして、最後に「+3」しているわけですが、この+3も含めて、LEFT関数の2つ目の引数内です。

なぜ、+3をしているのかというと、先程の「MID(B2,4,1)="県"」が、県の場合、このMID関数の式が成立しているので「TRUE」なわけです。

Excelでは「TRUE」を「1」としていますので、「1」＋「3」で「4」と算出されるので、左から4文字目が「県」ならば、左から4文字を抽出するというわけです。

「県」でなければ、成立していないので「FALSE」となり、Excelでは「FALSE」を「0」としています。

「0」+「3」で「3」と算出されるため、IF関数をつかわなくても、住所から都道府県を抽出することができるというわけです。

 

「TRUE」と「FALSE」を考えに入れると、数式がコンパクトになる可能性がありますので、色々試してみるといいかもしれませんね。

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。ピリオドで区切られた日付では計算でつかえない！どうしたらいいの？【DATE】

Excel。ピリオドで区切られた日付では計算でつかえない！どうしたらいいの？

＜DATE・LEFT・MID・RIGHT関数＞

見た目、日付とわかるからという感じで、何も考えないで入力している場合、Excelの機能を使えないということが、ちょこちょこあります。

例えば、次のような日付計算。

B4には、B2-B3。

C4には、C3-C3という数式が設定してあります。

B4には、#VALUE!というエラーが表示されていますが、C4は、普通に減算された結果が算出しています。

何が違うのかというと、B列の日付は、「.（ピリオド）」で区切られた日付になっています。

「.（ピリオド）」で区切られいる日付は、見た目が日付なだけで、Excelとしては、日付と認識していません。

「文字」として認識されているので、#VALUE!というエラーが表示されてしまっているのです。

最初から「/（スラッシュ）」で区切っていれば、問題はなかったのです。

今回のように、2件程度のデータならば、入力し直すことも容易ですが、件数が多い場合は、絶望的な作業となってしまいます。

そこで、「.（ピリオド）」を「/（スラッシュ）」に変換する方法を紹介していきます。

とても単純ですが、現場レベルとしては重要な作業です。

C2には、次の数式を設定してあります。

=DATE(LEFT(B2,4),MID(B2,6,2),RIGHT(B2,2))

年月日を別々のセルに算出するならば、YEAR関数やMONTH関数などをつかって、抽出するわけです。

ところが、「.（ピリオド）」で区切ってしまっていると文字型になってしまっているので、YEAR関数などの日付系の関数を使うことができません。

文字型のデータならば、LEFT関数・MID関数・RIGHT関数をつかうことで、対応することができます。

そして、DATE関数をつかうことで、日付にすることができます。

DATE関数の最初の引数は、「年」の設定です。

左から4文字が年ですから、

LEFT(B2,4)

と設定することで、「年」を抽出することができます。

次の引数ですが、左から6文字目からの2文字を抽出する必要があります。

文字列の途中の文字を抽出したいので、MID関数を使います。

MID(B2,6,2)

と設定することで、「月」を抽出することができます。

最後の引数ですが、MID関数を使うよりも、右から数えた方が簡単なので、RIGHT関数を使います。

RIGHT(B2,2)

と設定することで、「日」を抽出することができます。

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

ただし、日付にはなるのですが、「0（ゼロ付）」の日付になっていません。

そのため、表示形式のユーザー定義をつかって、「0（ゼロ付）」の日付にしてあげるといいですね。

なお、TEXT関数をつかってしまうと、「0（ゼロ付）」の日付にすることはできるのですが、日付文字型になってしまうので、注意が必要です。

入力するという作業では、今回のように、もったいないというか、あとで、面倒な作業が発生することもありますので、ちょっと気を遣う必要がありますね。

Excel関数辞典 VOL.49。MEDIAN関数～MINIFS関数【dictionary】

Excel関数辞典　VOL.49。MEDIAN関数～MINIFS関数

＜Excel関数＞

今回は、MEDIAN関数～MINIFS関数までをご紹介しております。

MEDIAN関数

メディアン

中央値を算出します。

MEDIAN(数値1,[数値2]…)

MID関数

ミッド

文字列の任意の位置から文字を取り出す

MID(文字列,開始位置,文字数)

MIDB関数

ミッドビー

文字列の任意の位置から指定バイト数の文字を返す

MIDB(文字列,開始位置,バイト数)

MIN関数

ミニマム

最小値を算出します。

MIN(数値1,[数値2]…)

MINA関数

ミニマムエー

数値・文字列・論理値を含む最小値を算出します。

MINA(値1,[値2]…)

MINIFS関数

ミニマムイフズ（ミニマムイフエス）

条件により指定した範囲内の最小値を算出

MINIFS(最小範囲,条件範囲1,条件1,…)

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。決まった場所にハイフンなどの文字を割り込ます（追加）方法を確認してみよう。【interrupt】

Excel。決まった場所にハイフンなどの文字を割り込ます（追加）方法を確認してみよう。

＜MID関数＞

CSVファイルやTEXTファイルをExcelに読み込んだあとに、データを修正したいことは多々あります。

例えば、次のようなケース。

郵便番号のフィールドが、ハイフンがなかったので、7ケタの数値で読み込んでいます。

このままでも支障はないのかもしれませんが、3桁と4桁の間に「-（ハイフン）」をいれて郵便番号と見てわかるように、変更したいのが、今回の目的です。

件数が少なければ、自力で「-（ハイフン）」を入力していくというのアリなのかもしれませんが、時間がかかりますし、面倒です。

文字をコントロールする時に考えるのは、文字列の左側からか、右側からなのか？それとも、文字列の中間なのかによって、使う関数が異なってきます。

今回は、左から3文字目と4文字目の間に「-（ハイフン）」を入れたいので、MID関数を使うことで、解決することができます。

B2に数式を設定します。
=MID(A2,1,3)&"-"&MID(A2,4,4)

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

「-（ハイフン）」を文字列に挿入することができました。

確かに目的は達成したのですが、MID関数を2回使っており、数式がわかりにくいので、REPLACE関数をつかうことで、もっとシンプルに同じことを行うことができます。

B列を削除して、改めて、REPLACE関数を使って数式を作っていきますので、REPLACE関数ダイアログボックスを表示します。

文字列には、A2。
開始位置には、4。
これは、4文字目のところに「-（ハイフン）」をいれたいので、4と設定します。

文字数には、「0」。
1にすると、4文字目から1文字目の文字を次の置換文字列と置換するという処理のための引数です。

今回は、文字と置換するのではなく、そこに挿入したいわけですよね。
文字と文字の間に割り込ます場合は、文字列を0にすればいいわけです。

REPLACE関数は、置換する関数というイメージがあるので、なかなか、文字を割り込ます・追加するこの発想は浮かびませんね。

そして、置換文字列は、「-（ハイフン）」を設定します。
あとは、OKボタンをクリックして、オートフィルで数式をコピーしてみましょう。

なお、B2の数式は、
=REPLACE(A2,4,0,"-")

確認してもらうと、先程のMID関数同様に、「-（ハイフン）」を3文字目と4文字目の間に追加することができました。

REPLACE関数のほうがMID関数よりも馴染みが薄いかもしれませんが、知っておくといざという時に便利な関数かもしれませんね。

なお、MID関数であれ、REPLACE関数であれ、注意しないといけないのは、今回のデータは、全部のデータに「-（ハイフン）」が入っていなかったということです。

「-（ハイフン）」があったりなかったりすると、当然、条件分岐が必要になってきます。

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。IF関数を使わないで、生年月日から干支を算出方法？！【Zodiac】

Excel。IF関数を使わないで、生年月日から干支を算出方法？！

＜MID+MOD+YEAR関数＞

面白いリクエストがありまして、
「スタッフさんの生年月日から『干支』がわかるようにしたいんだけど～」
とのこと。

IF関数を使ってみたら、わからなくなっちゃって…だそうでして。
ということで、ちょっと考えてみましょう。

次のような表があります。

今年2017年の干支は、酉。
というように表示することができればいいわけですね。

このような場合は、
どのようなルール（パターン）になっているかを見つけることが
出来るのかがカギになります。

干支のルールは、
子丑寅…と始まって12種類で元の子に戻るというルールなので、
12で除算してみて、その余りを使うことで、
どの種類なのかを判断することが出来そうですね。

とりあえず、C列に、余りを求める関数のMOD関数を使って、
生年月日を12で除算してみましょう。

C2にMOD関数ダイアログボックスを表示しましょう。

数値には、年数を入れたいので、年数を算出することができる、
YEAR関数を使って算出させますので、YEAR(A2)

除数には、干支の数である12

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

D列には、ルール（パターン）確認のため、自分で干支を入力してみました。

これによると、0（ゼロ）つまり、余りがない年は、
申年だということがわかりましたね。

0ならば、申。

1ならば、酉というように算出させるには、
IF関数を使うといいように思えますが…

IF関数のネストが多くなりすぎて煩雑になってしまうので、お勧めできません。

そこで、登場するのが、MID関数を使うと便利ですね。

CHOOSE関数でもいいのですが、少し数式が長くなります。

では、B2にMID関数ダイアログボックスを表示しましょう。

このMID関数は、
文字列の指定の位置から指定の文字数分を抽出することが出来る関数ですね。

文字列には、"申酉戌亥子丑寅卯辰巳午未"

子からじゃありませんよ。ここがポイント。

0が申だったので、申からスタートさせます。

開始位置は、先程確認した、数式を使いますので、MOD(YEAR(A2),12)。

ただこれでは、0になってしまいます。
0文字目では、MID関数が機能しませんので、+1をしてあげるといいですね。

文字数は、1文字を抽出するので、1

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

このように、『干支』がちゃんと算出することが出来ましたね。

ちなみに、B2の数式は、
=MID("申酉戌亥子丑寅卯辰巳午未",MOD(YEAR(A2),12)+1,1)
となっていますね。

なお、CHOOSE関数を使ってみると…
=CHOOSE(MOD(YEAR(A2),12)+1,"申","酉","戌","亥","子","丑","寅","卯","辰","巳","午","未")

今回は、干支というお題でしたが、
文字列の抽出という方法を使うことで、
IF関数を使った時の煩雑な数式から少しは
解放されるケースもあるかもしれませんので、
色々考えてみるといいかもしれませんね。

Excel。一文字ずつをセルごとに表示するにはどうしたいいの？【MID+COLUMN】

Excel。一文字ずつをセルごとに表示するにはどうしたいいの？

＜MID+COLUMN関数＞

【一文字ずつをセルごとに表示】

わざわざ、こんなことしなくても…と思いつつも、
困っているので解決してあげるコトって結構あるのですが、
今回の、文字を一セルずつに表示する方法もその中の一つでして、
出来そうで、わかりにくいので、

今回は、一文字ずつをセルごとに表示する方法をご紹介していきます。

まずは完成形ですが、次のようにしたいわけですね。

A1に、文字をいれたら、
D1:N1のセル一つずつに一文字ずつ表示したいということなんですね。

考え方として、
D1には、A1の文字の一番左側の一文字を表示して、
E2には、A1の文字の左から二番目の一文字を表示するという
パターンになっていますので、それを表現するようにしていきましょう。

指定の場所の文字を表示するには、MID関数を使うのがいいですね。

そして、一文字ずつ、ずれる必要がある。

つまり、1→2→3と数値を変化させる必要があります。
数式を横方向にオートフィルで数式をコピーすることを考えると、
何列目かという数値を使って、一列ずつ、ずらしていけばいいわけですね。

このように何列目かを算出するには、
COLUMN関数を使えば算出できますので、
今回は、MID関数とCOLUMN関数をコラボレーションさせれば、
一文字ずつ表示することが出来そうですね。

煩雑な数式になりそうなときには、やりたいことを、バラシて、
使える関数や、Excelのテクニックを紐づけしてあげると、
煩雑な感じのする数式でも、突破口が見つかる可能性があります。

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

文字列には、$A$1。
オートフィルで数式をコピーしますので、絶対参照を忘れずに設定しましょう。

開始位置ですが、COLUMN(A1)を設定します。
COLUMN関数をネストしていきます。
引数にA1を使っているのは、1という数値がほしいからですね。

これをA4にしてしまうと、4という数値を算出してしまうので、
使えないのでご注意ください。

文字数は、1。表示数文字数は1ですから、
これで1文字を表示することが出来ます。

あとは、OKボタンをクリックして、オートフィルで数式をコピーすれば完成ですね。

ちなみに、D1の数式は、
=MID($A$1,COLUMN(A1),1)
という数式になっています。

当然、アルファベットのお名前の人にも対応しております。

今回のように、一文字ずつをセルごとにそれぞれ表示するには、
MID+COLUMN関数のネストを使ってあげると、
比較的簡単に算出することが出来ますので、
このような升目に沿った資料や帳票を作るときには、
MID+COLUMN関数を使ってみてはいかがでしょうか。