「10月の売上、ゼロだった?」月ごとに列を足すExcel表が集計を壊す理由と、1行1データへの直し方

アイキャッチ:横に月を足す表を1行1データの縦の表に直す。NGは2段見出しでSUMIFが12,400、OKは縦の表で交通費3か月37,500 初心者シリーズ

入社1年目の秋、こんな場面を想像してみてください。月次報告を出して席に戻ったところで、上司に呼び止められます。

「合計が先月と変わってないんだけど、10月って売上ゼロだった?」

そんなはずはありません。10月の数字は前の日に自分で入力しています。表を開き、合計のセルをクリックして数式バーを見たところで、ようやく分かります。

=SUM(B2:G2)

足しているのは4月から9月までの6列だけ。10月の列は、合計列のさらに右、空いていたI列に作ってあったのです。数式の範囲の外です。担当者12人分、合わせて684,000円が、エラーも警告も出ないまま年間合計から抜け落ちていたわけです。

冒頭の例なら、数式の範囲を直して報告を出し直せば、その場は収まります。けれど翌月になれば、また列を足します。そのたびに合計も平均もグラフも、範囲を目で確かめ直すことになる。原因は数式の書き方よりも手前、月が変わるたびに列が増えていく表の形にあります。

この形を直すときの合言葉が「1行1データ」です。よく聞く言葉ですが、自分の表がそうなっているかをその場で判断できる人は多くありません。そこでこの記事は見分け方から始めて、崩れ方の種類、直す手順、直さなくてよい表の線引きまでを順に書いていきます。

扱うのは、月が横に並ぶ表や2段見出しの表を、集計できる縦の形に直す方法です。自分の表がこの形かどうかは、ピボットテーブルで表の形を確かめる点検でも見分けられます。

最初に1分だけ。3つの質問に答えてください

手元の表を開いて、次の3つに答えてみてください。関数は使いません。

質問1:来月のデータは、どこに足しますか。

下に行を足すなら問題ありません。右に列を足すなら、この記事はあなたの表の話です。

質問2:見出しを左から声に出して読むと、何が並んでいますか。

「日付、担当者、商品、金額」のように項目の名前が並んでいれば大丈夫です。「4月、5月、6月」「田中、佐藤、鈴木」「A案、B案、C案」のように具体的な中身が並んでいたら、黄色信号です。

質問3:表の真ん中あたりの1行を指さして、「これは何の記録か」を1文で言えますか。

「6月3日に田中さんがトナーを8,600円で買った記録」と言い切れれば合格です。「田中さんの、ええと、半年分のいろいろ」となるなら、その1行には複数の出来事が詰め込まれています。

3つとも引っかからなければ、その表はすでに1行1データです。1つでも当てはまった方は、このあと出てくる3つの型のどれに近いかを見ながら読み進めてください。

月の列が何十本、何百本と続く横長の表や、見出しが2段になっている表も、根っこは同じ問題です。154列の表を4列に作り直す手順とあわせて、後半にまとめました。

「1行1データ」を一言でいうと

定義は短くて、1つの出来事を、1行に、過不足なく書く。これだけです。

なぜこれが大事かというと、Excelの集計機能のほとんどがこの形を前提に作られているからです。SUMIFSやCOUNTIFSは「この列がこの値になっている行」を探して集計します。フィルターは列の中の値で行を絞り込みます。ピボットテーブルは列を分類の軸として使います。どれも、行が1件、列が1項目になっていて初めて思ったとおりに動きます。

裏を返すと、表の形さえ合っていれば、覚えている関数が少なくても集計はたいてい何とかなります。「関数が難しくて集計できない」と相談されて表を見せてもらうと、関数より前に表の形で止まっていることが少なくありません。

崩れ方には3つの型がある

1行1データになっていない表というと、月を横に並べた表だけを思い浮かべがちです。実際にはあと2つ型があって、そちらは気づかれにくいぶん長く放置されます。

型A:見出しに「中身」を入れて横へ伸ばす

冒頭の例の表がこれです。小さくするとこうなります。

担当者4月5月6月合計
田中50,00062,00048,000160,000
佐藤71,00055,00080,000206,000
鈴木43,00067,00059,000169,000

田中さんの行には「4月の売上」「5月の売上」「6月の売上」という3つの出来事が入っています。そして「月」という情報は、列の中のどこにもありません。見出しに上がってしまっています。

この型で起きることは決まっています。月が増えるたびに列と数式が増える。「4月だけ表示する」というフィルターがかけられない(フィルターが絞り込めるのは列の中の値で、見出しは対象外です)。ピボットテーブルを作っても「月」という軸が出てこない。そして冒頭の例のように、足した列が合計の範囲から漏れます。

合計列の左に列を挿入すれば大丈夫だろう、と思うかもしれません。ただ、数式の範囲が一緒に広がるかどうかは挿入する位置しだいで、結局は毎回、合計のセルをクリックして範囲を目で確かめることになります。確かめ忘れた月に事故が起きる、という点は変わりません。

月のほかに、日付、担当者名、店舗名、アンケートの選択肢が見出しに並んでいる表も同じ型です。さらに「4月」の下に「A商品・B商品」と見出しを2段に重ね始めると、見出しが2行になっている表の問題も同時に抱えることになります。

型B:1件が2行以上にまたがっている

氏名所属・連絡先
田中 一郎営業1課
内線 2041
佐藤 花子総務課
内線 1105

名簿でよく見かける形です。1人分の情報が2行に分かれ、2行目は氏名が空欄になっています。上段に予定、下段に実績を書く進捗表も同じ仲間です。

この表を氏名で並び替えると、内線番号の行だけが下にまとまってしまい、どれが誰の番号か分からなくなります。フィルターで「田中 一郎」を選んでも、出てくるのは所属の行だけです。

氏名のセルを上下で結合して見た目を整えてある場合は、結合セルが集計を壊す仕組みまで重なります。直し方そのものは単純で、「所属」「内線」と列を分けて、1人を1行に収めます。

型C:1行、あるいは1セルに複数件が入っている

日付担当者購入品金額
6/3田中コピー用紙、トナー、付箋12,400
6/5佐藤ボールペン880

型Bとは逆に、今度は1行に詰め込みすぎています。「トナーに年間いくら使ったか」を知りたくても、12,400円のうちトナーがいくらだったのかは、この表からは誰にも分かりません。COUNTIFで「トナー」を数えようとしても、セルの中身が「トナー」と一致しないので0件が返ってきます。

「商品1」「商品2」「商品3」と列を横に並べる形も同類です。4つ買った日に列が足りなくなりますし、「どれかの列がトナーである行」を探そうとすると、列の数だけ条件を書くことになります。

直すときは、品目ごとに行を分けます。

日付担当者購入品金額
6/3田中コピー用紙3,200
6/3田中トナー8,600
6/3田中付箋600
6/5佐藤ボールペン880

同じ日付と担当者が3行続きますが、それで合っています。この繰り返しを無駄に感じる方は多いのですが、Excelにとっては繰り返されている言葉こそが集計の手がかりです。

型は違いますが、表の途中に「※確認中」のような注記を1行はさむのも、1行1データを崩す原因になります。そちらは表の途中に書いたメモの見つけ方と、消さずに移す手順にまとめました。

筆者が実際に手を焼いたのは、1つのセルに複数の値が入った表です。人から受け取った表で、そのままでは件数も合計も出せませんでした。数十行ほどの規模だったので、1セルずつ分けて手で直し、1時間ほどかかりました。行数が少なくても、崩れた表を直す時間は思った以上にかさみます。

同じ5つの依頼を、2つの表にぶつけてみる

型Aの表を1行1データに直すと、こうなります。A列が月、B列が担当者、C列が売上です。

月担当者売上
4月田中50,000
4月佐藤71,000
4月鈴木43,000
5月田中62,000
5月佐藤55,000
(以下、6月まで続く)

3行5列だった表が、9行3列になりました。縦に長くなって不安になるところですが、職場で実際に飛んでくる依頼を両方の表にぶつけてみると、印象が変わります。

左は見出しに4月・5月・6月を並べた担当者別の売上表、右は同じ9件を月・担当者・売上の3列で縦に並べた1行1データの表
赤枠の月が、右の表では緑枠の列の中身になっています
よくある依頼横へ伸ばした表1行1データの表
5月の合計は?=SUM(C2:C4) を月ごとに作る=SUMIFS(C:C,A:A,"5月")
田中さんの合計は?=SUM(B2:D2)。列を足すたびに範囲を直す=SUMIFS(C:C,B:B,"田中")
6万円以上の記録だけ見たい月の列ごとにしか絞れず、一度には出せない売上列の数値フィルターで「指定の値以上」
担当者×月の一覧がほしい最初からその形(ここは得意)ピボットテーブルで作る
7月分を追加したい列を足し、合計・平均・グラフの範囲を直す下に3行足すだけ

横へ伸ばした表が勝っているのは、4番目だけです。そしてその「担当者×月の一覧」は、1行1データの表があれば後からすぐ作れます。逆方向、つまり横の表から縦の表を作るほうは手間がかかります。どちらを手元に持っておくべきかは、この非対称で決まります。

SUMIFSは「合計する列、条件を探す列、条件」の順に書きます。条件を増やしたいときは後ろに足すだけです。

=SUMIFS(C:C,A:A,"5月",B:B,"田中")

これで「5月の、田中さんの売上」が出ます。月が増えても担当者が増えても、数式を触る必要はありません。ほかにどんな関数が使えるかは、目的別によく使う関数の見つけ方で一覧にしています。

月を「4月」という文字で持つときの注意

上の例では分かりやすさを優先して、月を「4月」という文字で入れました。1年で使い切る表ならこれで困りませんが、年をまたいで使い続けると、去年の4月と今年の4月の区別がつかなくなります。

長く使う表では、月の列に「2026/4/1」のように日付を入れておくのがおすすめです。セルの書式設定でユーザー定義を選び、yyyy"年"m"月" と指定すれば、見た目は「2026年4月」になります。集計するときはこう書きます。

=SUMIFS(C:C,A:A,DATE(2026,5,1))

「1件」の単位で迷ったら、いちばん細かいほうを選ぶ

1行1データの話をすると、ほぼ毎回聞かれるのが「1件って、どこまでを1件にすればいいんですか」という質問です。売上なら、1回の取引なのか、1日分なのか、1か月分なのか。

答えは、手元で記録できるいちばん細かい単位です。

理由は単純で、細かい記録から粗い集計は作れますが、その逆はできないからです。取引1件ずつの記録があれば、日別にも月別にも担当者別にもまとめ直せます。月の合計しか書いていない表から、あとになって「15日の売上は?」に答えることはできません。

とはいえ、細かい記録が手に入らないこともあります。冒頭の例の売上表がそうで、手元に届くのは担当者ごとの月合計だけです。その場合は「ある担当者の、ある月の売上」を1件として、1行に書けば十分です。

列を決めるときは、「いつ・誰が・何を・いくら」をそれぞれ1列にします。あとから店舗や商品カテゴリといった分類を足したくなったら、列を足してかまいません。

ここで、「列を足すのは悪いことだったのでは」と引っかかる方がいると思います。見分け方があります。新しく足す列の見出しが、すでにある全部の行にも当てはまるかどうかを考えてください。

「店舗」という列なら、4月の行にも5月の行にも値が入ります。これは項目を足す列で、問題ありません。「7月」という列は、7月のデータにしか意味を持ちません。これは中身を足す列で、型Aの入口です。

今ある横の表を直す手順

直し方は表の大きさで変わります。

数十セルくらいの表なら、手作業のほうが早く終わります。次の順番で進めます。

  1. 新しいシートを作り、1行目に「月」「担当者」「売上」と見出しを入れる
  2. 元の表から担当者の列をコピーし、月の数だけ縦に続けて貼る
  3. 月の列に、担当者の人数分ずつ同じ月を入れる(先頭に1つ入力し、下まで選択してCtrl+Dを押すと一気に埋まります)
  4. 元の表の4月の列をコピーし、売上の列に「値」で貼り付ける。5月、6月と同じように続ける
  5. 元の表の総合計と、新しい表の売上列の合計が一致するか確かめる

冒頭の例の表は担当者12人の7か月分で、直すと84行になります。かかる時間は40分ほどです。途中で1か月分を貼る位置を1行ずらしても、手順5で合計が合わないので、その場で気づけます。5番目だけは省かないでください。

列が何十本もある表で同じことをやると、どこかで必ず貼り間違えます。その規模になったら、Power Queryの「列のピボット解除」やTOCOL関数に任せたほうが安全です。操作は、この記事の後半のPower Queryで一気に縦にする手順にまとめています。

直し終わったら、表の中のセルを1つ選んでCtrl+Tを押し、「先頭行をテーブルの見出しとして使用する」にチェックを入れてOKを押してください。テーブルにしておくと、下に行を足したときに範囲が自動で広がり、あとで作るピボットテーブルにも新しい行が含まれるようになります。

なお、表の途中に空白行があると、テーブルの範囲がそこで切れてしまいます。心当たりがあれば、先に空白行の見つけ方と安全な消し方を確認しておくと安心です。

見出しが2段になっている表を、まず1行にする

横に伸びた表は、見出しが2段になっていることがよくあります。上の段に「交通費」「交際費」、下の段に「4月」「5月」「6月」。上の段はセルを結合して横に広げてある、という形です。見た目は整っていますが、Excelが見出しとして扱うのは一番上の1行だけで、下の段は「氏名が空欄の、ちょっと変わったデータ」として処理されます。

営業部12名の経費を「交通費・交際費×4月〜6月」の7列で管理している表で考えてみます(作例です)。フィルターをかけると、▼は7列中3つしか付きません。山田さんの交通費3か月分を出すつもりの =SUMIF(B1:G1,"交通費",B3:G3) は、37,500円になるはずが12,400円を返します。結合したB1:D1のうち、文字が入っているのは左端のB1だけで、C1とD1は空っぽだからです。エラーは出ません。ピボットテーブルにいたっては、見出しに空のセルがあるので作成の時点で止められます。

交通費と交際費の見出しを結合した2段見出しの経費表。山田さんの交通費3か月分は37,500円なのに、SUMIF(B1:G1,"交通費",B3:G3)は12,400円を返している
赤枠の結合見出しのうち、文字があるのは左端のB1だけです

直し方は、見出しを「交通費_4月」のような1行にまとめることです。作業はシートのコピーで行ってください。

  1. 1行目の結合を外す(「セルを結合して中央揃え」をもう一度押す)
  2. 1行目の空いたセルを埋める。列が多いときは、見出しの範囲を選んで「検索と選択」→「条件を選択してジャンプ」→「空白セル」でOK。そのまま「=」と左矢印キーを押し、「Ctrl」+「Enter」で確定すると、空白に「左隣と同じ」が入る
  3. データの上に1行挿入し、B列に =B1&"_"&B2 を入れて右へコピーする。A列には「氏名」と直接入力する
  4. 新しい見出し行をコピーして同じ場所に「値」で貼り付けてから、古い2行を削除する

値に貼り付ける前に古い行を消すと、見出しが全部エラーになるので順番だけは守ってください。7列の表なら10分かかりません。見出しを「費目_月」の形にそろえておくと、=SUMIF($B$1:$G$1,"交通費*",B2:G2) のように「交通費で始まる列だけ」を足せるようになります。

どうしても今日中に絞り込みだけ使いたい、という場合は、下の段の行番号をクリックして行ごと選んでからフィルターを押すと、▼が7つ付きます。並び替えや関数の問題は残るので、あくまでその場しのぎです。

列が何十本もある表を、一気に縦に直す

ここまでの手作業は、数十セル程度の表なら十分です。列が100本を超えるような表は、手で貼り替えるとどこかで必ずずれるので、Excelに任せます。

他部署から届いた売上表を開いたら、最終列が「EX列」、154列あった――という作例で手順を追います。A列に商品名が45行、その右に「2024/04_田中」「2024/04_佐藤」……と、担当者6人×24か月分の列が続き、途中に担当者別や年度別の合計列が9本挟まっています。作成者は毎月6列を挿入して合計の範囲を直す作業に30分ずつ、24か月で12時間ほどを使っていて、それでも年度合計の範囲から1か月分が漏れ、2025年度の合計が約38万円少なく出ていた、という状態です。

中身を整理すると、入っている情報は「商品名・担当者・月・売上」の4種類だけです。この表は、4列・6,480行の表に作り直せます。

下準備:合計の列を消し、見出しを1行にそろえる

合計の列を残したまま縦にすると、「合計」という名前の担当者がいるかのように扱われ、集計すると売上が2倍近くに膨らみます。合計はあとでピボットテーブルがいくらでも作るので、元のファイルを別名で保存したうえで削除します。見出しが2段なら、前の章の手順で「2024/04_田中」の形にまとめておきます。区切りの「_」は、あとで月と担当者を分ける目印になります。

Power Queryの「その他の列のピボット解除」

  1. 表の中のセルを選び、「データ」タブの「テーブルまたは範囲から」を押す。Power Queryエディターが開く(ここで何をしても元のシートは変わらない)
  2. 残したい列(ここでは「商品名」)の見出しをクリックし、「変換」タブの「列のピボット解除」の右の矢印から「その他の列のピボット解除」を選ぶ。144列が「属性」と「値」の2列に縦に積み直される
  3. 「属性」の列を選び、「ホーム」タブの「列の分割」→「区切り記号による分割」で「_」を指定する。分かれた2列の名前を「月」「担当者」に、「値」を「売上」に変え、「月」の列の型を日付にしておく
  4. 「ホーム」タブの「閉じて読み込む」で、新しいシートに4列の表ができる

手順2で「商品名以外」をまとめて指定するのは、あとで誰かが列を足しても、取りこぼさずに縦にしてくれるからです。Power Queryが使えない環境でも、Microsoft 365ならTOCOL関数で同じ形を作れます。商品名がA2:A46、見出しがB1:EO1、売上がB2:EO46なら、次の3本を横に並べて置きます。

Power Queryエディターで商品名の列を選び、変換タブの「列のピボット解除」から「その他の列のピボット解除」を選んでいる画面
赤枠の「その他の列のピボット解除」を選びます
=TOCOL(IF(COLUMN(B1:EO1),A2:A46))
=TOCOL(IF(ROW(A2:A46),B1:EO1))
=TOCOL(B2:EO46)

IFの部分は、商品名や見出しを売上と同じ数だけ繰り返すための細工です。できあがったら値に貼り付けて確定させます。

縦にした直後に確かめる3か所

  • 月の書き方:見出しが途中で「2024/04」「2024年5月」「6月」のように変わっていると、日付と文字が混ざり、ピボットテーブルで一部の月だけ一番下に固まる。「月」の列を並べ替えて、順番がおかしくないかを見る
  • 消えた行:ピボット解除は空欄のセルを行として残さない。売上がなかった月も行として残したいなら、解除の前に空白(null)を0に置き換えておく
  • 担当者の数:「佐藤」と「佐藤 」、「鈴木」と「鈴木さん」のような表記ゆれで、6人のはずが8人に増えていることがある。フィルターの一覧で人数を数える

月が横に並んだ一覧が必要になったら、次の章のとおりピボットテーブルで作ります。翌月からの更新は、縦の表の下に270行を貼り付けて、ピボットテーブルの更新ボタンを押すだけです。30分かかっていた作業が2分ほどで終わります。

横の一覧が必要になったら、ピボットテーブルで戻す

「上司は横の表に慣れているから、縦の表は出せない」という声もよく聞きます。出す必要はありません。縦の表は自分の手元に置く元データで、渡すのはそこから作った横の一覧です。

  1. 縦の表の中のセルを1つ選ぶ
  2. 「挿入」タブの「ピボットテーブル」を押し、そのままOKを押す
  3. 画面の右に出るフィールドの一覧で、「担当者」を行へ、「月」を列へ、「売上」を値へドラッグする

これで、最初の横の表とほぼ同じ見た目の一覧ができあがります。総計も自動で付きます。

縦の表から作ったピボットテーブル。担当者を行、月を列、売上を値にして、総計が4月164,000・5月184,000・6月187,000・合計535,000と出ている
緑枠の総計は、元の横の表の合計と一致します

1つだけ注意があります。元データに行を足しても、ピボットテーブルは自動では変わりません。ピボットテーブルの上で右クリックして「更新」を選んでください。これを忘れると、冒頭の例の10月と同じ「数字が増えていない」報告を出すことになります。

入力する表と、見せる表を分ける。慣れるまでは二度手間に感じますが、見せる表のほうは毎回数十秒で作り直せるので、トータルでは早くなります。

横のままでいい表もあります

ここまで読むと、横に並んだ表は全部直すべきだと思えてくるかもしれません。そんなことはありません。

たとえばシフト表です。日付を横に、名前を縦に並べた形は、貼り出して全員が見るための表としてよくできています。出勤日数を数えるだけなら、横のままでも =COUNTIF(B2:AF2,"出") で足ります。直す必要が出てくるのは、「土日の出勤だけ数えたい」「3か月分を通して集計したい」と条件が増えてきたときです。

ほかにも、一度作って終わる見積の比較表や、印刷して記入してもらう様式は、横の形のままで困りません。

迷ったときは2つだけ確かめます。来月も行や列が増えていくか。あとで集計や絞り込みをするか。両方とも「はい」なら縦にします。どちらかが「いいえ」なら、今の形で使い続けてかまいません。

よく聞かれること

縦にすると何百行にもなります。重くなりませんか

Excelの1枚のシートには1,048,576行まで入ります。担当者12人の売上を毎月1行ずつ、10年続けても1,440行です。事務の仕事で扱う量なら、行数が原因で重くなることはまずありません。重いファイルの原因は、行数よりも、大量に入った数式や書式であることが多いです。

同じ担当者名を何度も入力するのが面倒です

打ち直さなくても入力できます。すぐ上と同じ値ならCtrl+D。同じ列にすでに入っている値から選びたいときは、セルを選んでAltキーを押しながら↓を押すと候補が一覧で出ます。

手で打つと「田中」と「田中 」(後ろに空白)のような表記ゆれが混ざりやすく、これがSUMIFSの集計漏れにつながります。症状と直し方はセルに紛れ込んだ空白で関数が動かないときに書きました。

「管理表」のつもりが「報告書」になっていた

冒頭の例の月次の表は、作った本人は「見やすくまとめた管理表」のつもりでも、実際には管理表ではなく、完成した報告書です。報告書を毎月、手で作り直しているわけです。

縦の表に切り替えれば、月初の作業は12行を足してピボットテーブルを更新するだけになり、5分ほどで終わります。合計の範囲を確かめる作業も、そもそも発生しなくなります。

冒頭の例のように684,000円が抜けた報告を出してしまわないために、新しい表を作るときは、最初のセルに何かを入力する前に「これは入力する表か、見せる表か」と自分に聞く習慣をつけておきましょう。

この記事の要点

  • 1行1データとは、1つの出来事を1行に過不足なく書くこと
  • 崩れ方は「見出しに中身を入れて横へ伸ばす」「1件が複数行にまたがる」「1行に複数件を詰め込む」の3つ
  • 来月のデータを右に足す表、見出しに月や名前が並ぶ表は要注意
  • 1件の単位で迷ったら、記録できるいちばん細かい単位にする
  • 直したあとは、元の合計と一致するか必ず検算する
  • 横の一覧は、縦の表からピボットテーブルでいつでも作れる
  • 見るためだけの表や、増えていかない表は横のままでよい

1行1データに直したあとで、空白行や単位の混在といった別の問題が見つかることも少なくありません。見つかった問題をどの順番で直せば手戻りがないかは、点検用のチェックリストと一緒にnoteで整理しています。もう一歩踏み込みたい方はこちらからどうぞ → 「表の健康診断」12項目と直す順番(note)

タイトルとURLをコピーしました