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関数を使ってみてはいかがでしょうか。

Excel。VLOOKUP。氏名の一文字ずつをセル取り出して分割するには？

Excel。氏名の一文字ずつをセル取り出して分割するには？

＜MID+ VLOOKUP +COLUMN関数＞

まずは、完成例を見ていただいた方がイメージしやすいと思いますので、
次の表があります。

納品書のような表で、どうしても、
担当者名を一文字ずつに分割してセルに表示させたいというのが
今回のリクエスト。

自分で入力するとしたら、B2のコードが変わる都度、
入力し直す必要が発生しますから、確かに効率が悪い。

問題が解決しやすくなりますので、
まずはやりたいことの流れを、考えてみましょう。

「文字を抽出する」ことがテーマ。これを押さえておきましょう。

まず思いつくのは、
B2のコードからA5:B8の表から担当者名を検索するので、
これは、VLOOKUP関数を使うと良さそうですね。

次は、VLOOKUPで検索した結果を一文字ずつセルに
振り分けていきたいので、
端から一文字ずつ抽出したいので、MID関数を使うと抽出できます。

さらに、その結果を列方向にオートフィルで数式をコピーします。

となると、2文字目・3文字目というように
抽出箇所をずらしていく必要がありますので、
COLUMN関数を使うとうまくいきそうな感じですね。

ということで、

今回は、MID+ VLOOKUP +COLUMN関数の
コラボレーションで数式を作成して、
問題を解決していきましょう。

あくまでも、
今回は、コードから担当者名を検索するのがテーマではなくて、
文字を抽出するのがテーマですから、MID関数が母体になるわけです。

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

文字列。文字列には、コードで検索された担当者名が入力されてきますので、
VLOOKUP関数をネストしていきます。

名前ボックスの▼を使ってVLOOKUP関数ダイアログボックスを表示しましょう。

検索値には、コードを入力してあるところですので、$B$2。

範囲には、名簿が該当しますので、$A$6:$B$8。
オートフィルを使って数式をコピーするので、
絶対参照を忘れないようにしましょう。

列番号には、今回検索したいのは担当者名なので、
範囲の左から2列目のデータですから、2。

検索方法は、完全一致ですから、FALSE。
あるいは、0【ゼロ】でもOKですね。

そして、OKボタンは押さないように気を付けて、
数式バーを使って、MID関数に戻りましょう。

開始位置ですが、最初は1、
次は2、3と数字を増やすことによって、
一文字目二文字目というように抽出することが出来るのですが、
どうやって数値を増やしたらいいのか？

そこで、COLUMN関数が登場します。

列番号の情報を算出してくれる、COLUMN関数。

COLUMN(A2)
と設定することによって、1という数値を算出してくれます。

列方向にオートフィルすればCOLUMN(B2)となって
”2”という数値を算出してくれます。

文字数には、1文字分を抽出したいので、1。

あとは、OKボタンをクリックして、
列方向にオートフィルを使って数式をコピーしてみましょう。

なお、C2の数式は、

=MID(VLOOKUP($B$2,$A$6:$B$8,2,FALSE),COLUMN(A2),1)

手入力で数式が作れる方は、ダイアログボックスにこだわる必要はありませんね。

数式をコピーすると、

このように、完成しました。

実際に一つずつのセルに一文字ずつ抽出入力する必要があるのか、
ないのかは別としてこのような方法を使うと作成することが出来ますので、
機会があれば是非。

Excel。DATE。数値を元号表示の日付に変えたいけど、うまくいかないので、どうしたらいい？

Excel。数値を元号表示の日付に変えたいけど、うまくいかないので、どうしたらいい？

＜TEXT関数・LEFT関数・MID関数・RIGHT関数&DATE関数＞

最初から日付として入力されているデータならば、
単純に表示形式を変更することで、和暦にしたり、西暦にしたり、
年月日にしたりなどできるのですが、

下記のようなデータの場合、なかなか、大変な作業が必要になるのです。

では、このようなことをしたいわけです。

A列に申込日というのがあって、単純に日付を数値として入力してあるそうです。

さらに、年は元号というものなのですが、
これをB列のように西暦表示にしたいというのが今回のやりたいことなのです。

別に難しくはないでしょう？と思った方も多いかと思いますが、
今回の曲者は年が元号ということなのです。

西暦の場合だと、どうなるのかを確認してみましょう。

次の表を見てみましょう。

こちらの場合は、A列は、西暦で数値を入力しております。

B列のように、こちらは、/（スラッシュ）で区切られている西暦表示にしてあります。

B8の数式は、

=DATE(C8,D8,E8)

関数に慣れている人でしたら、
LEFT関数などをネストしながら算出することもできると思いますが、

今回は、C列に年。D列に月。E列に日。
を求めて、DATE関数を使って日付を算出させています。

C8には、=LEFT(A8,4)
左から4文字分ということですね。

D8には、=MID(A8,5,2)
5文字目から2文字分ということですね。

E8には、=RIGHT(A8,2)
右から2文字分ということですね。

という数式がそれぞれ設定されております。

ということで、西暦の場合は、それほど困らずに算出することができます。

なお、表示形式で、/（スラッシュ）が入る表示形式に変えたらいいのでは？
と思われるかもしれませんが、それはできません。

日付。Excelではシリアル値というもので管理しております。
1を1900年1月1日として、一日を1としてカウントするようになっております。
ですから、1900年1月1日から20160614日後を指すことになってしまうので、
表示形式では対応できないわけです。

そこで、本題に戻りますが、このシリアル値のこともあり、表示形式は使えません。

C列の年。D列の月。E列の日。は先ほどと同じように算出しております。

先ほどの西暦は、DATE関数を使って算出できましたが、今回はどうでしょうか？

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

年には、C3
月には、D3
日には、E3
これで、OKボタンをクリックしましょう。

残念ながら、DATE関数ではダメでした。要するに年が元号という認識がないわけです。

だから、今回のように簡単に思われるかもしれませんが、一筋縄では、いかないわけです。

今回は、TEXT関数と表示形式を使って算出していきます。

C3の数式は、

=TEXT("H"&C3&-D3&-E3,"yyyy/mm/dd")*1

というように設定していきます。

それでは、C3をクリックして、TEXT関数ダイアログボックスを表示しましょう。

値の"H"&C3&-D3&-E3　ですが、
"H"は元号の頭文字です。平成なのでHです。

もし昭和の場合は、Sで作成する必要があります。&で年月日を結合させるのですが、
-（ハイフン）を年月日の間にいれることで、文字タイプの日付型に変えることができます。

/（スラッシュ）を入れたいところですが、/（スラッシュ）では、結合することができません。

この時点で、値の結果は、H27-5-9　となるのですが、
西暦表示にしたいので、TEXT関数を使って、表示形式を"yyyy/mm/dd"とします。

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

とりあえず、完成しました。

ただ、完成ではあるのですが、先ほど作りました、
B8は、右揃えになっていますが、B3は左揃えになっていますよね。

本来日付は、右揃えになるはずですよね。

実は、TEXT関数を使って求めたB3:B5は、文字タイプの日付になっているのです。

そこで、×1をすることで、シリアル値にすることができるようになりますので、

B3の数式の後ろに×1を追加します。
よって、
B3の数式は、

=TEXT("H"&C3&-D3&-E3,"yyyy/mm/dd")*1

となっているわけです。

このように、日付を用途に合わせて、表示をかけたい場合、
簡単にできないケースもありますので、最初から、
日付形式で入力するように心がけるといいかもしれませんね。