☰

Excel で名前付き範囲内の各行を合計するにはどうすればよいですか?

tag-icon • tag-icon microsoft-excel worksheet-function named-ranges
Excel で名前付き範囲内の各行を合計するにはどうすればよいですか?

予算テンプレートをリバース エンジニアリングして変更しようとしています。予算テンプレートには、さまざまな支出 (家、娯楽、交通など) のセクションがあります。各セクションには複数の支出があります (交通には自動車保険、ガソリン、駐車場など)。各セクションは名前付き範囲で、各支出は行の右端のセルに合計されます。ただし、テンプレートはセクション内の各行を同じ機能で実行しており、その仕組みがわかりません。

これは、関数が上部にあり、名前が manager である画像です。前述したように、強調表示されている関数は、「Daily Living」のすべての行の合計でまったく同じ関数です。 https://i.stack.imgur.com/mbMij.jpg

まったく同じ名前グループ (名前マネージャーとすべての範囲が同一) と同じレイアウトがありますが、同じ関数がエラーを生成し、受け入れられないということを理解できません。私が実行できた最善の方法は、各行を個別にではなく、セクション内のすべての行を合計する SUM(Daily) を入力することです。

誰か、画像からこの機能=SUM(Daily[@[January]:[December]])、特に「@」記号について説明してもらえますか。また、名前マネージャーに JAN がない場合、Excel はどのようにして JAN (列ヘッダー内) が 1 月を意味すると認識するのでしょうか。

ボーナス: セクション合計と全体の合計機能がどのように機能するかについても説明してもらえますか? https://i.stack.imgur.com/ydMfz.jpg

答え1

そうですね、たくさんのことを聞かれるので、一つ一つ答えられたらいいなと思います。

まず第一に、データ レイアウトは ですTable。ExcelTableでは、データが古い形式で表示されるセル範囲とは異なります。これは、単純なルールに従う正式なオブジェクトであり、その代わりに、Excel では、世界中の誰もが設定時に行う可能性のあるすべての奇妙な操作を考慮する必要がありません (合計セルがデータ内に表示され、上下左右に表示されず、同じ範囲内で異なる列の異なる行に表示されるのを見たことがあります。これは、愚かなやり方でこの人の計画に適合しましたが、人間にとって奇妙でわかりにくいものに見えた場合、Excel は一体どうすればよいのでしょうか。10 人の人間にとってはどうでしょうか。

いずれにせよ、テーブルの名前は毎日ところで、数式の要素の入力方法から、それがテーブルであることがわかります。他に理由がなくても、角括弧 (“"[ ]") が見えますか? これらは、ほとんどの場合、テーブルとその他のまれな場所でのみ使用されます (これらすべては、おそらくテーブル テクノロジを使用して機能します)。とにかく、単純なSUM()数式を作成するときに、行全体を強調表示すると、Excel は、強調表示したセル アドレスを、列見出しに基づいてテーブル参照に変換します。1 月から 12 月は、ここで強調表示した範囲の端です。したがって、D20:O20 のような参照ではなく、[January:December] が表示されます。

この式に残る要素は、@これは表なので、Excel にこの行のみを意味することを伝える方法がなければ、すべての行を合計しているとみなされます。Excel では、 は Excel に@「この行のみ」を伝え、表やその他のさまざまな場所だけでなく、Spill数式を使用する際にも広く使用されます。具体的には、Excel にこの行を伝えるので、ドライ クリーニングの行のみを合計し、1 月から 12 月までのすべての列の数値を合計します。

多くの点で、SUM()ここで使用されている とSUBTOTAL()(どこでも使用されている) は、ほぼ同じです。ただし、SUBTOTAL()大きな違いがあります。Excel は、列を合計するときに、それを使用して取得した SUBTOTAL を無視します。ただし、 で作成された SUBTOTAL は無視されないSUM()ため、それらが生の数値と同じ列にある場合は、合計が 2 倍になります。また、平均値など、単独では返されないSUBTOTAL()さまざまなものも返されます。SUM()

スプレッドシートを作成した人は、明らかに非常に標準的で合理的な方法で合計を設定しました。彼は、SUM()その後に実行した垂直方向の合計で水平方向の合計が自動的に無視されないように、 を使用して水平方向の合計を取得し、その後、SUBTOTAL()表に表示されている垂直方向の合計を作成しました。

これらはすべて自動的に使用可能になるため、名前付き範囲にはありません (自動なので、回避するのが難しい場合もあります...)。全体がテーブルであるためです。テーブルなので、列ヘッダーを参照として使用できます。以前はいつでもこれを行うことができましたが、約 17 年前に削除され、この形式で復活しました... ため息...

また、列ヘッダーは自動的にテキストになるため、数式にすることができないという点が時々厄介です。そのため、テーブルでは動的なヘッダーは使用できません。

テーブルに見えない理由の 1 つは、各ヘッダーの右側にある、通常はヘッダーの一部の上に表示される、目障りなフィルタリング アイコンが表示されないことです... これらを消すのは簡単で、作成者は明らかにそうしました。 これらがなければ、ほとんどの人はしばらくの間「テーブル」とは思わないでしょうが、数式参照の構成は決定的な証拠です。 事実を不明瞭にするもう 1 つの点は、ほとんどの人がテーブルに「グリーン バンド」の外観があることに慣れていることです (色は何でも構いませんが、その紙は以前は主に緑色で、私は 845 歳ですから...)。 ただし、これも、適切でない場合、または不快と見なされる場合は書式設定できます (なぜですか? なぜですか? 画面全体のテーブルが非常に読みやすくなります!)。 この場合、明らかにそれは不適切です。 各データ領域で使用することもできますが、各データ領域には行数が異なるため、毎年多大な労力がかかります。

それで：

  1. それはTable、そして、それはあなたに利用可能ないくつかの良いものがあります
  2. データは 2 つのセクションに分かれており、左の列にはセクション ヘッダー、次にサブセクション (この場合は個々の行) ヘッダーがあります。たとえば、「ドライ クリーニング」と、1 月から 12 月の見出しの下に「時間の経過とともに存在するとおりに入力する」セクションがあります。
  3. 各行は関数を使用して右側の年ごとに合計されますSUM()。
  4. 各列は関数を使用して月ごとに合計されます (右端の列には年も合計されます) SUBTOTAL()。
  5. 関数は、範囲参照の最初の項目の前（「@January」）にSUM()配置するため、一度に 1 行のみで機能します。@
  6. 個々のセクション (「収入」、「家」など) では、各月と年ごとにビットの合計が関数を使用して計算されますSUBTOTAL()。これは、全体の列を合計するときには無視されるためです。(SUM()各セクションの結果が下部の全体に含まれるのとは対照的に、合計では本来の 2 倍の値が返されます。)

重要な詳細に注意を払って、非常に説得力があり、便利にレイアウトされています。このようなものは、単純な表、または表のバージョンにまとめた方がよいと主張する人もいるでしょう。つまり、単純な表にして、次にピボット テーブル (そう、845 年の歴史があるので、クロス テーブル、クロス タブでさえ、私にはまだ普通に聞こえます) でプレゼンテーションします。結局のところ、ピボット テーブル、またはより正確には、Power Pivot テーブルには、独自の利点 (おお、スライサーなど) や合計機能などがたくさんありますが、それはまた別の学習事項であり、これは誰かのニーズを非常にうまく解決しました。

何をする必要があるのか​​よくわからないので、それに関してお手伝いすることはできません。しかし、もしあなたが、この努力を何とか改善したい、あるいは、何とか自分のものにしたいだけなら、それは実際には不可能です。とてもうまくできているのですから。ピボットテーブルを使わない限りそれで、それがすべてなら、面倒なことは避けてください。これは非常にまともなものであり、実際に改善する必要はありません。それが単に上司が何かをしたいだけの場合、まあ、私も同じ気持ちです。私たち全員が経験したことがあるでしょう。

関連情報