Excel文字列関数

はじめに

事務処理業務においては、既存の名簿データなどを扱う際に、都合の良いようにデータを整形したりしますが、その際に活用するのが文字列関数です。

このページでは、主要な文字列関数である、LEN関数、LEFT関数、RIGHT関数、MID関数、FIND関数、TEXT関数などを例に説明します。

加えて、CSV形式データファイルなど外部データの取り込み方法や、データを取り込んだ後のデータ整形及び条件付き書籍設定による1行おきのセル塗りつぶし方法なども説明します。

[本テキスト用データダウンロード]
ex_string.xlsx


文字列関数

文字列関数例1

まずは、文字列を切り出したり、検索したりする関数を例に紹介します。

  • LEN関数 … 文字列の長さ(文字数)を返す。
  • LEFT関数 … 文字列の先頭から指定された文字数の文字列を返す。
  • RIGHT関数 … 文字列の末尾から指定された文字数の文字列を返す。
  • FIND関数 … 検索文字列が、対象文字列内に最初に現れる位置を返す。
  • MID関数 … 文字列の指定された位置から、指定された数の文字列を返す。

構文

LEN(文字列)
LEFT(文字列, 文字数)
RIGHT(文字列, 文字数)
FIND(検索文字列, 対象文字列[, 開始位置])
MID(文字列, 開始位置, 文字数)

※文字位置は、文字列の先頭(最初)の文字が「1」として数えます。

ex-str01

文字列関数例2

次に、文字列を加工したり、変換したりする関数を例に紹介します。

  • TRIM関数 … 単語間の空白を1つだけ残して、後の不要な空白を削除する。
  • JIS関数 … 半角の英数カナ文字を、全角の英数カナ文字に変換する。
  • CONCATENATE関数 … 複数の文字列を結合して、1つの文字列にまとめる。
  • TEXT関数 … 数値に指定した書式を設定し、文字列に変換した値を返す。
  • VALUE関数 … 文字列として入力されている数字を数値に変換する。

構文

TRIM(文字列)
JIS(半角英数カナ文字列)
CONCATENATE(文字列1, 文字列2, 文字列3, ...)
TEXT(値, 表示形式)
VALUE(数字文字列)

ex-str02


練習課題1

次図のようなワークシートを作成し、各問いに答えてください。

  • Q1. “◯◯学部△△学科“という「所属」を表す文字列が格納されているセルを参照して、”◯◯学部“と”△△学科“のように文字列を切り分けて、それぞれ「学部」と「学科」のセルに格納されるようにしてください。
  • Q2. 「個人番号」の欄に”00301150“のような数字の「0」で始まるIDを入力したい場合、どのようにしたらよいか考えてください。
  • Q3. 十の位の数字(例えば「4」)が入っているセルと、一の位の数字(例えば「3」)が入っているセル、これらを連結して「43」というような数字を作り、それに10を加算して適当な答えになるにはどうしたら良いか考えてください。

ex-str-sub01


練習課題2

次図のような、複数人の学生の個人情報が記載されたCSV形式のテキストファイルがあるとします。
※以下のリンクからダウンロードしてください。

[データのダウンロード]
gakuse.csv [CSV形式: 8.2KB]

本CSVの名簿データは架空の情報です。ここに掲載している氏名と同姓同名の人物が実在したとしても、全く無関係です。

ex-str-sub02

このCSVファイルをExcelのワークシートに取り込んで、次図のようなリストに整形してください。

ex-str-sub03

[注意点]

  1. 項目「フリガナ」は、全角カナ文字であること。
  2. 項目「学部」及び「学科」は、「所属」からそれぞれ切り分けること。
  3. 項目「生年月日」は、「年4桁/月2桁/日2桁」の形式であること。
  4. 項目「年齢」は、生年月日より算出すること。
  5. ”法文学部”、”教育学部”、”理工学部”、”医学部”、”農学部”の順番で、その中でも学籍番号順になるように並べ替えて、項目「No.」に連番を振ること。
  6. 簡単に並べ替えや、フィルターが利用できるリストにすること。
  7. 1行ごとに塗りつぶしをし、リストを見やすくすること。

データ取り込み方法、及び注意点5、7については、以下に記載の「練習課題のヒント」を参照してください。


練習課題3

練習課題3で作成したリストを元に、COUNTIFS関数と配列数式を利用して、次のような集計表を作成してください。

ex-str-sub04


練習課題のヒント

任意の文字列順での並べ替え方法

学部の順番については、別途キーとなる列を設けても良いのですが、次図のように、「ユーザー設定リスト」をあらかじめ作成しておくと便利です。

ex-str-hint01

ユーザー設定リスト」の定義(編集)は、メニュー「ファイル」>「オプション」>「詳細設定」>「全般」にあります。

表を1行ごとにセルを塗りつぶす方法

1行ごとに塗りつぶす方法については、「条件付き書式設定」を利用します。リストのデータ行全てを選択して、ルールの内容(数式)を次のように設定します。該当セルの行番号をROW関数にて取得し、MOD関数にて行番号の値を2で割った余りを算出します。行番号を2で割った余りは、0、1、0、1、…と変化していくので、0または1どちらかの場合に塗りつぶしをすれば良いことになります。

=MOD(ROW(),2)=0

表の途中にて、行を挿入したり削除したりしても、きちんと1行ごとに塗りつぶされるのを確認してください。

CSV形式ファイルの取り込み方法(Excel 2016以前)

拡張子が「CSV」だと、そのままExcelで開くことも可能ですが、今回のように「0」で始まるIDのような値が入ったデータですと、その「0」の桁を無視した数値として認識されてしまいます。

そこで、以下のように、「外部データの取り込み」の「テキストファイルのインポート」機能で取り込むとその問題が解決できます。

(1) メニュー「データ」から、「外部データの取り込み」>「テキストからデータを取り込み」を選び、取り込みたいファイル(今回の場合は「gakusei.csv」)を選択し、「インポート」をクリックすると、以下のような「テキストファイルウィザード」が表示されます。

先頭行は項目名になっているので、これを見出しとして使用します。

ex-str-inp01

(2) 区切り文字は「,」なので「カンマ」を選択します。

ex-str-inp02

(3) ここがポイントで、データのプレビューから「学籍番号」の列を選択して、列のデータ形式に「文字列」を指定します。

ex-str-inp03

あとは、「完了」をクリックして、任意の取り込み場所を指定すれば完了です。

CSV形式ファイルの取り込み方法(Excel 2016の場合)

Excel 2016からは、メニュー「データ」から、「データの取得と変換」>「テキストまたはCSVから」を選択し、任意のファイルを選択、「インポート」をクリックすると、CSVの中身を表示したダイアログが表示され、その中の「編集」をクリックすると、次図のような「クエリエディター」が起動します。

これも「学籍番号」の列を選択し、「データ型」を「10進数」から「テキスト」に変更すれば、学籍番号を文字列として取り込むことができます。

ex-str-inp04


参考学習

個人番号などを入力する際は、何かと入力ミスをしがちですが、10桁以上の長いコードの場合、たいてい入力コードに間違いがあるか否かをチェックする機構がコードに組み込まれています。それが「チェックデジット」で、コードの下一桁の番号がそれになります。

チェックデジットの算出方法(モジュラス11の例)

  1. 下1桁目はチェックデジットなので、下1桁目を除いた9桁の数値にする
  2. その数値の各桁に、上の桁から9、8、…、1(重み)を掛ける
  3. その重み掛け合わせた各桁の結果の合計を求める
  4. その合計を11で割り、余りを求める
  5. 11からその余りを引く
  6. さらにその引いた数値を10で割り、その余りがチェックデジット
  7. 入力個人番号の下1桁目と一致していれば、この個人番号は正しい

チェックデジットによる入力コードの判別

ex-str-ref

チェックデジットを調べる数式

[セルC6の式]

=MOD((11-MOD(SUM(MID(B6,1,1)*9,MID(B6,2,1)*8,MID(B6,3,1)*7,MID(B6,4,1)*6,MID(B6,5,1)*5,MID(B6,6,1)*4,MID(B6,7,1)*3,MID(B6,8,1)*2,MID(B6,9,1)),11)),10)

[セルD6の式]

=IF(VALUE(RIGHT(B6,1))=C6,"正しい","間違い")

[セルB13における条件付き書式設定のルール(数式)]

=VALUE(RIGHT(B9,1))MOD((11-MOD(SUM(MID(B9,1,1)*9,MID(B9,2,1)*8,MID(B9,3,1)*7,MID(B9,4,1)*6,MID(B9,5,1)*5,MID(B9,6,1)*4,MID(B9,7,1)*3,MID(B9,8,1)*2,MID(B9,9,1)),11)),10)

 


以下は、なるべく見ないで、練習課題に挑戦してください。

練習課題1の解答例

次図を参照してください。

ex-str-ans01


練習課題2の解答例

ex-str-ans02

  • 「学部」「学科」の切り出しについては、練習課題1のQ1を参考に行います。切り出し後、数式のままデータを残すことはできないので、それぞれの列のデータをコピーし、新しい列を挿入し、そこに「形式を選択して貼り付け」で値(式の結果)として貼り付けるようにします。
  • 「フリガナ」の全角文字化は、「文字列関数例2」のQ3を参考にし、これも数式の結果(値)として残します。
  • 「生年月日」の表示形式は、ユーザー定義において、「yyyy/mm/dd」を指定します。
  • 「年齢」の計算式は、生年月日データの先頭のセルがG6の場合「=DATEDIF(G6,TODAY(),"Y")」になります。
  • 項目名は見かけ上は2行ですが、3行で構成するようにします。Excelがリストとして適当な範囲を自動選択するよう、項目名「所属」と項目名「学部」「学科」の間には、1行空白行を入れておき、その行の高さを小さくするか非表示にします。

練習課題3の解答例

練習課題2で作成した「学生名簿」が入力されているシート名: 練習課題2
練習課題3で作成する集計表「学生現員数」があるシート名: 練習課題3

とした場合、シート「練習課題3」の「学生現員数」の表(セル範囲 D4:E14)に設定する配列数式は、以下の通り。

=COUNTIFS(練習課題2!F6:F105,練習課題3!C4:C14,練習課題2!I6:I105,練習課題3!D3:E3)

Excel文字列関数」への2件のフィードバック

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト /  変更 )

Google フォト

Google アカウントを使ってコメントしています。 ログアウト /  変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト /  変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト /  変更 )

%s と連携中