【Excel関数備忘録】特定の文字が付いた数字をそれぞれ合計する

Pocket
LINEで送る

Excelは独学

 
 私は大学でプログラミングを学びましたが、WindowsOSのこともOfficeのことも誰かに教えてもらったことは一度もありません。

 今、私が仕事で使っているあれこれは全て独学なのです。

 でも、パソコンを使いこなせない人にとっては、「大学でプログラミングを学んだ=パソコンのことなら何でも知ってる」なんですよね。

 なので、たまに「Excelでこういうものを作って欲しい」という依頼が舞い込みます。

 プログラミングを学ぼうと思ったくらいパソコンが好きなので、Excelに対しても抵抗感は全くないのですが、だからと言ってExcelに詳しいわけではありません。

 立ち位置としては、パソコン使える人と同じくらいの知識量なのです。

 Excelに関しては、プログラミングの基礎が身に付いているということ以外は素人同然。

 そんな私に舞い込んだ、「Excelでこういうものを作って欲しい」という依頼。

 理論上は出来なくもなさそう。

 という、かなりふんわりとした感覚で依頼を受けた私が、ここ数日Excel関数で見事に死亡したので、その備忘録を記事にしておこうと思います。

 

別々に計算する、では駄目らしい

 
 依頼主は親戚です。

 小さい頃からお世話になっているので、私で役に立てるならという気持ちもありました。

 が、実際に作り始めてすぐ「これはヤバい」と思いました。

 ExcelはExcelでも、Excel関数上級者向けコースだったのです。

 幸いだったのは、VBAまではいかないレベル(たぶん)だったこと。

 以前にも別件で依頼されたことがあるのですが、その時はVBAでプログラムを組み、ボタン一つで全自動な代物を作りました。

 この時も私死亡。

 何故ならVBAは素人だったから。

 今回も死亡する可能性が高いなと遠い目をしながら作り始めたのですが、今回は特定の文字が付いた数字をそれぞれ集計するという部分で詰みました。

 

 このように、数字のみ、数字+M、数字+Tが混在しているデータから、数字のみ、数字+M、数字+Tそれぞれ集計するというもの。

 数字だけならSUM関数で簡単に集計出来ますが、大問題なのが文字の付いた数字。

 一応、別シートに分けたりして計算するのでは駄目なのかと聞いてもみましたが、あっさり「駄目」という答えが返ってきました(涙)
 

特定の文字を削除→数字を集計

 
 文字を削除するだけならSUBSTITUTE関数を使えば簡単です。

 =SUBSTITUTE(範囲,”M”,)

 これで”M”を削除して数字だけに出来ますが、これだけだとエラーになります。

 残った数字をExcelが数字と認識していないからです。

 文字と一緒だった数字なので、Excelは残った数字を文字と認識したままなのです。

 なので、数字に変換してあげます。

 =SUBSTITUTE(範囲,”M”,)*1

 ※この他にも数字の0を足す、VALUE関数を使うなどの方法があります。

 これで、Excelが数字と認識してくれるようになります。

 続いて集計ですが、文字を削除した数字だけ集計というのは難しいです。

 数字のみの数字が混在しているからです。

 ただ、それぞれ集計しなければいけないので、数字のみの集計も別に出しています。

 なので今回は、数字のみの数字と文字を削除した数字を集計して、数字のみの集計を除くという方法を取りました。

 =SUM(IFERROR(SUBSTITUTE(SUBSTITUTE(範囲,”M”,),”M”,)*1,範囲))-数字のみの集計

 SUBSTITUTE関数が二つあるのは、大文字の”M”と小文字の”M”の両方に対応するためです。

 IFERROR関数で他の文字を含むものはそのままにしています。

 SUM関数が拾うのは数字のみなので、これで”M”の付いた数字の集計が可能になりました。

 最後に、範囲内全てで処理をしなければいけないので、Ctrl+Shift+Enterキーを押して配列数式にします。

 {=SUM(IFERROR(SUBSTITUTE(SUBSTITUTE(範囲,”M”,),”M”,)*1,範囲))-数字のみの集計}

 Excel関数をきちんと学んだわけではないので正解かどうかは分かりませんが、これで一応集計は可能になりました。