「Excel 複数項目 集計できない」――もしあなたが今、こんなキーワードで検索してこの記事にたどり着いたのなら、たぶん私と同じ壁にぶつかっています。
先日、私はクラウドワークスであるお仕事の相談を見かけました。
内容をざっくり言うと、こうです。
勤務管理表のR列・S列に「内容」と「時間数」が入っているが、1つのセルの中に複数の項目が表示されていて、うまく集計できない。マクロは使わず、計算式だけで直してほしい。
読んだ瞬間、「あ、これ分かる」と思いました。
経理で日々スプレッドシートと格闘している私にとって、”1つのセルに複数の情報が詰まっていて集計できない”のは、あるあるの困りごとだからです。
でも――。
じゃあどう直すのか、と聞かれると、私には答えが浮かびませんでした。
そこで私は、いつものようにAI（Claude）に相談しました。
この記事では、そのやり取りで見つけた解決策と、途中で私が踏んでしまった”落とし穴”を、同じように困っている方のために全部書きます。
技術的な内容ですが、関数はSUMくらいしか使わないという方でも読めるように、専門用語はできるだけ噛み砕いて進めます。
そもそも何が問題だったのか
まず、何が起きていたのかを整理します。
この勤務管理表では、R列に「休日出勤」「有給休暇」といった勤怠の内容が、S列にその日数や時間数が入る仕組みでした。
普通なら、1行に1つの内容が入ります。
ところが――。
1日に複数の事情が重なると（たとえば「休日出勤して、さらに別の申請もした」など）、1つのセルの中に、改行で複数の項目が詰め込まれることがあったんです。
具体的には、こんな感じです。
R列のセル：
休日出勤（改行）その他S列のセル：1（改行）1.0
見た目は1つのセルですが、中身は2つ分の情報が入っている。
しかも、R列の1番目「休日出勤」とS列の1番目「1」、R列の2番目「その他」とS列の2番目「1.0」が、それぞれ対になっている――。
この「1セルに複数、しかも上下で対応している」という構造が、すべての元凶でした。
なぜ普通の関数（SUMIF）だと集計できないのか
こういう「条件に合うものだけ合計する」場面で、まず思いつくのが SUMIF や SUMIFS です。
※ SUMIF……指定した条件に合う行だけを合計してくれる関数
たとえば「休日出勤の日数を合計したい」なら、こう書きます。
=SUMIF(R列, "休日出勤", S列)
「R列が”休日出勤”の行だけ、S列を合計してね」という意味です。
一見、これで解決しそうに見えます。
でも、ここに落とし穴があります。
SUMIF は、セルの中身が”休日出勤”と”完全に一致”しているかで判定します。
つまり、休日出勤 だけのセルは拾ってくれますが、休日出勤／その他 のように複数入っているセルは――。
「”休日出勤”とは違う文字列だ」と判断して、まるごと無視してしまうんです。
その結果、複数入りのセルだけがごっそり集計から抜け落ちる。
これが「集計されない」の正体でした。
今回のケースでは、有給休暇・時間有給・半日有休・欠勤・特別休暇・休日出勤・遅刻・早退という8つの項目を集計する必要があったのですが、複数入りのセルが混ざることで、あちこちで数字が合わなくなっていたわけです。
解決の核は「n番目を取り出す」こと
では、どうするか。
考え方はシンプルです。
改行で区切られた1番目・2番目・3番目…を、1つずつバラバラに取り出せれば、あとはいつも通り集計できる。
実は新しいバージョンのExcelには、TEXTSPLIT という「区切り文字でセルを分割する」便利な関数があります。
これを使えば一発です。
……が、今回はそれが使えませんでした。
理由は、クライアントから「古い環境でも動くようにしてほしい」という要望があったからです。
TEXTSPLIT のような新しい関数は、古いExcelやGoogleスプレッドシートでは動きません。
そこで私は、AIに「古いExcelでも動く方法で」とお願いしました。
返ってきたのが、SUBSTITUTE・REPT・MID という、昔からある関数を組み合わせた”n番目を取り出す”テクニックでした。
雰囲気だけ、こんな式です。
=TRIM(MID(SUBSTITUTE(セル, 改行, スペースの塊), 取り出す位置, 長さ))
やっていることは、「改行を大量のスペースに置き換えて、そこから狙った位置の文字だけ切り出す」という、なかなかトリッキーな処理です。
正直、私はこの発想を自分では思いつけませんでした。
古いExcelのやり方を知らなかったので、最初は「本当にこんなので動くの?」と半信半疑。
でも実際に動くのを見て、「なるほど……こういうやり方もあるのか」と素直に感心しました。
2つの作り方を用意した
AIに相談する中で、解決策は2つの方向性にまとまりました。
① 作業列方式
表の右側（印刷されない範囲）に、R列・S列を「1番目・2番目…」に分解する作業用の列を追加し、そこから普通の SUMIF で集計する方法です。
処理の流れが目で追えるので、後から直しやすいのが利点。
作業列は非表示にできるので、見た目は元の表のまま保てます。
② 単一式方式
作業列を一切増やさず、集計するセルに直接、長い式を書き込む方法です。
追加の列がゼロなので、見た目が一番すっきりします。
ただし式がかなり長くなるため、後からの手直しはやや大変です。
なぜ2つ用意したのか。
それは、どちらが正解かは相手の好み次第だからです。
「ロジックが見える方が安心」という人もいれば、「余計な列は増やしたくない」という人もいる。
だから両方を提示して、選んでもらう形にしました。
――そして実は、この「2つ作った」ことが、後で私自身を救うことになります。
実際に作った2つのファイルは、こちらからダウンロードできます。中の数式を直接見られるので、「n番目を取り出す」処理がどう書かれているか、気になる方はのぞいてみてください。
【本題】AIが書いた式が、一部だけ間違っていた
ここからが、この記事で一番伝えたい話です。
最初に完成した単一式方式のファイル。
動いているように見えました。
数字もちゃんと出ている。
私は「よし、できた」と思っていました。
ところが――。
念のため、作業列方式と単一式方式、両方の数字を並べて見比べたとき、異変に気づきます。
数字が、合わない。
有給日数・欠勤日数・特休日数・休日日数の合計が、2つの方式で食い違っていたんです。
しかも、気味が悪かったのはここです。
遅刻・早退の”回数”だけは、両方ともピタリと同じ数字。
一部だけ合っていて、一部だけ間違っている。
全部間違っているなら「式が壊れてるな」と一発で分かります。
でも、一部だけ合っていると、逆に「どっちが正しいんだ?」と混乱するんです。
動いているように見えて、一部だけ間違っている――。 これが、AIを使ううえで一番怖いところだと、私は改めて思い知りました。
なぜ”一部だけ”間違ったのか
原因を切り分けていくと、はっきりした違いがありました。
正しかった「遅刻・早退の回数」は、ただ数を数えているだけ。
間違っていた「日数の合計」は、取り出した値を足し算している。
違いは、”値を数字として計算する処理”をしているかどうか、でした。
最初の式は、その数値化のために IFERROR と VALUE という関数を使っていました。
※ ざっくり言うと「文字を数字に変換し、失敗したら0にする」処理です
ところが、この書き方が、SUMPRODUCT という集計処理の中に入ると――。
古いExcelでは、全部のデータではなく”先頭の1つ”だけを見て止まってしまうことがあるんです。
だから、数える処理（回数）は無事で、合計する処理（日数）だけが狂っていた。
この理屈にたどり着いたのも、AIとのやり取りの中ででした。
そして直し方は、意外とシンプル。
問題の IFERROR・VALUE を使うのをやめて、取り出した文字の頭に”0″を足すだけの書き方に変えたところ――。
2つの方式の数字が、ピタリと一致しました。
この失敗が教えてくれたこと
今回の一件で、私が改めて痛感したことが2つあります。
1つ目：AIは、頼まない限り”検算”してくれない
もし私が「この式、合ってる?」と確認しなければ、間違った式のまま納品していたかもしれません。
AIは、こちらが「チェックして」と明示しない限り、自分の出した答えを自分で検算はしてくれない。
これは、AIを使う以上ずっと付き合っていく前提だと思います。
2つ目：間違いに気づけたのは、”2通りの方法で作った”から
今回、私が異変に気づけた理由はただ一つ。
作業列方式と単一式方式、2つの答えを突き合わせたからです。
もし1つしか作っていなかったら、間違いに気づかないまま出していたでしょう。
検算は、「もう一つの作り方」で答え合わせをするのが、一番確実。
これは、電卓で計算を2回やるのと同じ発想ですよね。
経理の世界では当たり前の”ダブルチェック”を、AIの成果物にもやるだけ。
実はこれ、以前私が書いた記事で伝えたことと、まったく同じ教訓でした。
この記事で私は「AIの回答を鵜呑みにしない」「重要な数字は必ず検算する」と書きました。
今回は、まさにその言葉に自分が救われた形です。
まとめ：検算さえすれば、AIは非エンジニアの武器になる
最後に、正直に書きます。
今回のこのお仕事、受注には至りませんでした。
せっかく解決策まで用意したのに、と、少し悔しい気持ちもあります。
でも――。
このお仕事を通じて得た「解決策」と「失敗からの気づき」は、確実に私の中に残りました。
そして、実際に作ったファイルは、ポートフォリオとして公開しています。
案件は落としても、経験と成果物は残る。
副業を続けるうえで、これはこれで悪くない結果だと、今は思っています。
この記事を読んでくださっているあなたが、もし「自分には技術力が足りない」と感じているなら、伝えたいことがあります。
足りない技術は、AIで補えます。
現に、古いExcelの発想も知らなかった私が、AIと組むことで実務レベルの集計表を作れました。
ただし――。
最後の”検算”だけは、人間の仕事です。
その一手間さえ惜しまなければ、AIはあなたの成果物を、信頼できるものに引き上げてくれる。
非エンジニアにとって、これほど心強い武器はないと、私は思っています。
「AIに任せて、人間が確かめる」。
このスタンスで、これからも一緒に、賢くAIを使っていきましょう。
▶ あわせて読みたい
