Excelでは「出席13人」のように、1つのセル内に文字と数字が混在しているデータを扱うことがあります。このようなセルをそのまま足し算しようとしても、Excelは文字列として認識するため正しく計算できません。
しかし、関数を組み合わせることでセル内の数字だけを抽出し、複数セルの合計を自動計算できます。この記事では、文字付きの数字データから数値だけを取り出して合計する方法を分かりやすく解説します。
文字と数字が混在したセルはそのままでは計算できない
例えば、I2セルに「出席13人」、I23セルに「出席8人」、I44セルに「出席12人」と入力されている場合、見た目では数字が含まれていますが、Excelでは文字列として扱われます。
そのため、単純に「=I2+I23+I44」と入力しても計算できず、エラーになったり正しい結果が表示されなかったりします。
このような場合は、セルの中から数字部分だけを抜き出して数値化する処理が必要になります。
TEXTBEFOREやTEXTAFTERが使える場合の簡単な方法
Excelの新しいバージョンでは、文字列の位置を指定して数字部分を取得できる関数を利用できます。
例えば「出席13人」のように数字の前後に決まった文字がある場合は、文字を削除して数字だけを取り出す方法が便利です。
例として、I2セルから数字だけを取り出す場合は以下のような式を利用できます。
=VALUE(SUBSTITUTE(SUBSTITUTE(I2,"出席",""),"人",""))この式では「出席」と「人」という文字を削除し、残った「13」を数値として変換しています。
複数セルの数字を取り出して合計する計算式
今回のように複数セルをまとめて合計したい場合は、SUM関数と組み合わせます。
I2、I23、I44の値を合計する場合は、以下のような計算式になります。
=SUM(VALUE(SUBSTITUTE(SUBSTITUTE(I2,"出席",""),"人","")),VALUE(SUBSTITUTE(SUBSTITUTE(I23,"出席",""),"人","")),VALUE(SUBSTITUTE(SUBSTITUTE(I44,"出席",""),"人","")))この式を入力すると、「出席13人」「出席8人」「出席12人」から数字だけを取得し、13＋8＋12＝33として計算できます。
セル数が多い場合は補助列を使うと便利
対象セルが数個ではなく大量にある場合、長い計算式を作るよりも補助列を利用する方法がおすすめです。
例えば、J列に数字だけを抽出する列を作り、J2に以下の式を入力します。
=VALUE(SUBSTITUTE(SUBSTITUTE(I2,"出席",""),"人",""))その後、必要な行までコピーすれば、I列のデータから自動的に数字だけを取り出せます。最後にSUM関数でJ列を合計すれば管理しやすい表になります。
例えば、出席管理表や参加人数一覧などでは、元データを変更せずに集計用の列だけ追加できるため、実務でもよく使われる方法です。
数字の位置が一定ではない場合の対応方法
「出席13人」のように数字の前後に決まった文字がある場合はSUBSTITUTE関数で対応できますが、データによって文字が変わる場合は別の方法が必要です。
例えば「参加者13名」「合計8人」「12名参加」のように数字の場所が違う場合は、MID関数やTEXTSPLIT関数、VBAなどを利用して数字を抽出する方法があります。
データ形式をできるだけ統一すると、Excelの関数だけでも簡単に集計できるようになります。
まとめ
Excelで「出席13人」のような文字と数字が混在したセルを計算する場合、そのまま足し算することはできません。
文字部分をSUBSTITUTE関数で削除し、VALUE関数で数字に変換することで、数字だけを取り出して合計できます。
少ないセルなら直接計算式を作成し、データ数が多い場合は補助列を使うことで、ミスの少ない効率的な集計が可能になります。
コメント