パワパークホーム > Offce > Excel Tips (3)
単位付きで入力したデータを数値と単位に分割する方法
セルに単位をつけて“9pack”や“500個”のように入力されている場合、これを“9”と“pack”の様に分割して保存する方法を解説する。
図1.Excelでの入力イメージ
図1のようにD列に単位付きの数量が入力されているものとして、E列とF列に数値・単位を切り分けて保存する。
E列、F列にはそれぞれ次のような配列数式を入力した。
{=VALUE( LEFT( $D4,SUM( IF( ISNUMBER(FIND($H$4:$H$6,$D4)),FIND($H$4:$H$6,$D4),0 ) )-1 ) )}・・・(1)
{=RIGHT( $D4,LEN($D4)+1-SUM( IF(ISNUMBER(FIND($H$4:$H$6,$D4)),FIND($H$4:$H$6,$D4),0) ) )}・・・(2)
この配列数式の中で最初に評価される（計算される）のはFind関数。通常Find関数は、
=FIND（"a","abc"）
のように使用し、戻り値は検索値の見付かった位置（この場合は1）が返されるが、今回の場合は、E列（数量が入力されている列）には複数種類の単位が繰り返し使用されている可能性があり、予めH列に作成した“使用単位リスト”のいずれかを含んでいるか調べる。そこで、
{＝FIND($H$4:$H$6,$D4)}
のように配列数式を作成する。Find関数の戻り値は、H列リストのそれぞれと比較した結果なので、答えも比較するリストの個数分あり、セルに表示されるのは一つ目の値だけである。（この場合は、常にH4との比較結果が表示される。）
このままでは、セルにリスト先頭の単位が含まれていた場合しか正否が分からない。
| 表1-1.D4とH列リストを比較した結果 | 表1-2.D5とH列リストを比較した結果 | |||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||
上表のようにセルに反映されるのはD4を検索した場合“3”が、D5を検索した場合はエラー値“#Value!”が表示される。これを必ず数値が反映されるように、IFによる場合分けを行い、検索位置の合計を求めている。（この場合、合計といっても他の単位と比較した場合はエラーとなり除外されるので、単にFind関数が検索に成功した時の値、つまり単位が付いている位置を取得していることになる。）
ここまでの操作で、単位と数値の区切り位置が検出できたので、Left関数、Right関数（またはMid関数）により、文字列を分割する。
=LEFT($D4,[Find関数の結果]-1)
=RIGHT($D4,[D4の全体長さ]-[Find関数の結果]+1)
=MID($D4,[Find関数の結果],[D4の全体長さ])
| 表2.使用した関数 | ||
| 配列数式 | ・・・ | 式の入力後、Ctrl+Shift+Enterで確定する。単にEnterを押すだけでは配列数式として計算されない。 |
| Len関数 | ・・・ | 引数の長さ（文字数）を数える。戻り値は、符号や小数点も含む長さ。 |
| Value関数 | ・・・ | 引数を数値に変換する。Left関数の戻り値は文字列となるので数値に変換する。 |
| Left関数 （Right・Mid関数） |
・・・ | 文字列を左から指定桁数切り出す （右から・中間部分を） |
| SUM関数 | ・・・ | 引数の合計を取る |
| IF関数 | ・・・ | 条件分岐。Find関数の結果が数値であれば、Find関数の結果をそのまま使い、エラー値なら0とする。 |
| IsNumber関数 | ・・・ | 引数が数値かどうか調べる。 |
| Find関数 | ・・・ | 対象に検索値が含まれるか調べる。Find関数が検索に成功すれば数値、失敗ならエラー値。 |
～Memorandum～
この文章は、PawaPark内～MS Office
Q&A集～に寄せられた質問をもとに、再考したものです。派遣スタッフ・弊社登録者の方で、サンプルが必要な方は、パワレイドまでメールして下さい。サンプルファイルを添付して返信いたします。
2007/1/11 パワレイド