大量出力表データのエクセルによるn行おき合計表の作り方: 新道ブログ

あるWINDOWS用のデータ処理コードで作成された繰り返しのあるテキストデータリストをエクセルに張り付け、n行おきに合計したまとめ表を作る際のやり方をまとめた。
一般には下記の13.14.の繰り返しコピペの際のカーソル移動テクニックと15.以下に記載したsumproductにおけるｎ関数の使用方法とそのＣｈａｔＧＰＴの解説が役立つと思う。


某コードの出力データに対するエクセルによる合計処理方法
1. WINDOWSで某コードを流す。最初に入力ファイルフォルダのフォルダマークをクリックし、cmd とコマンド行に入力した後、表示されるコマンド窓でコマンドスクリプトファイルを実行する。
2. 出力されたデータファイルのテキストデータを丸ごとコピーする。
（この出力データは約３万行からなるが、最初に約100行の頭部分があり、その後、約200行のＮＤ部分が空白行を挟んで約150回繰り返される構成になっている。
以下ではこの繰り返しで繰り返される各ＮＤ部分を上からＮＤ１部分、ＮＤ２部分などと称する。）
3. エクセルの新規シートに張り付け
4. 頭の部分の主要データラインのみ固定長フィールドで区切る（数値区切り個所がＮＤ区切りと一致していないため）
5. 後ろに繰り返し続くＮＤ部分（途中空白行が規則的に挿入されている）を最期まで選択（Ｃ＋Ｓ＋↓を数回繰り返すとエクセルの最後まで選択されるが、一回Ｃ＋Ｓ＋↑を押すとデータの最後の行まで戻る。
6. 全体のコピペが終了したらコンマ、タブ区切りでＮＤ部分を区切る。
7. その際、空白に加えて、指定区切り文字として"（"を追加する。これは"（"に続く数値が後でエクセルでの計算に用いるためにその部分を1セルの数値セルにするためである。
8. ＮＤ１部分を対象に以下の処理をする。
　　　　まず、このセルを参照するセルを右側の空セルでリンクし、その列の空欄のＮＤ１データの記載個所の行全体にコピペする。
9. 次の列にはＭＩＤ関数を用いてＮＤ１内の文字列から得た数値を表示する。
10. 更にその右側列にこの数値と上記の”（”に続く数値とＮＤ１内の対応するセルの積で求めたセルを作成し、ND１領域全体を対象にコピペする。
11. この表の上に各列の解説セルをもとの左側のデータのセルを参照して作成する。
12. このようにしてND1領域の右側に作成した表全体をコピーし、ND2領域以下の右側に繰り返しペーストする。
13. ND領域数が150回繰り返されるので、10回程度ペーストしたらその10領域分をコピーしなおして、ペーストする。
14. その際にＣ＋Ｓ＋↓の繰り返しののちに、Ｃ＋Ｓ＋↑を一回行い、↓を押すとペーストすべき最初の空白行にカーソルを移動できる。
15. エクセルではこの繰り返し表の中のデータを指定した行間隔ごとに合計する必要が出てくる。その際は以下の関数を使う。
　　=SUMPRODUCT((MOD(ROW(R11170:R34190)-ROW(R11170),240)=0)*(N(R11170:R34190)))
　　これはＲ列の11170行から34190行までを対象に240行おきのセルを選び、そのセルが数値の場合だけ（文字や関数でない場合だけ）の合計を計算するようになっている。即ち、*(N(R11170:R34190)を乗じることで、文字や関数が対象領域のセルに含まれる場合に生じるエラーに防ぐようにできている。
この式の動作を解説してください
ChatGPTが解説すると、以下のようになります。

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
=SUMPRODUCT((MOD(ROW(R130:R34190)-ROW(R130),240)=0)*(N(R130:R34190)))

の(N(R130:R34190)))の意味について解説してください

ChatGPT:
(N(R130:R34190)) の意味について

この部分は、N() 関数を使ってセルの値を数値に変換する処理 です。具体的には以下のように動作します。
N(R130:R34190) の役割

Excel の N() 関数は、セルの内容を 数値化 するために使われます。
この関数は以下のような動作をします：
セルの内容 N(セルの値) の結果
100 (数値) 100
"ABC" (文字列) 0
TRUE (論理値) 1
FALSE (論理値) 0
#N/A (エラー) #N/A (変換されない)

N(R130:R34190) を SUMPRODUCT の中で使う理由
1️⃣ 文字列や空白セルを 0 にする

R130:R34190 の中に 文字列や空白セルが含まれている場合、通常 SUMPRODUCT で数値計算をするとエラーが出る可能性があります。
しかし、N() を使うことで 数値はそのまま、文字列や空白は 0 に変換 され、計算が可能になります。
例

もし R130:R34190 のデータが以下のようになっていた場合：
行番号 セルの値 (R列) N(R...) の結果
130 50 50
131 "A" 0
370 100 100
610 "" (空白) 0
850 200 200

➡ N() を使うことで、文字や空白を 0 にして計算を正しく進める ことができます。
2️⃣ SUMPRODUCT の掛け算でエラーを防ぐ

SUMPRODUCT の構造上、途中に 文字列やエラーがあると計算が止まることがある ため、それを防ぐ目的もあります。
具体的な動作

=SUMPRODUCT((MOD(ROW(R130:R34190)-ROW(R130),240)=0) * (N(R130:R34190)))

(MOD(ROW(R130:R34190)-ROW(R130),240)=0)
→ 240行ごとのセルだけを 1 にし、その他は 0 にする
N(R130:R34190)
→ 数値はそのまま、文字列や空白は 0 にする
* （掛け算）
→ 1 × 数値 ならそのまま数値を保持、0 × 数値 や 0 × 文字 なら 0 になる
結果として、240行ごとのセルだけを合計する

まとめ

✅ N(R130:R34190) の役割は 数値以外（文字列・空白など）を 0 に変換する こと
✅ SUMPRODUCT の中で使うことで エラーを防ぎ、意図した数値だけを合計できる
✅ 240行ごとに数値だけを抽出して合計する計算 を可能にする
補足

この N() を使った方法は、 数値と文字列が混在しているデータ範囲 に適した方法です。
もし エラー (#N/A など) を含む場合 は、IFERROR() を組み合わせるとより頑健な計算になります：

=SUMPRODUCT((MOD(ROW(R130:R34190)-ROW(R130),240)=0) * (IFERROR(N(R130:R34190),0)))

これなら、エラーがあっても 0 に変換され、計算が止まることがありません。