実践的な Excel スキルの共有: 非表示の列を無視して合計を実行する方法は?

前回の記事「Excel実践スキル共有：「条件付き書式」と「関数式」を併用する」では、2つの例を通して「条件付き書式」と「関数式」について学びました。の用法。今日はデータの合計について説明し、非表示の列を無視して合計する方法を紹介します。

私たちは日常の仕事で、よく合計を使用します。この問題は誰にとっても簡単です。SUM 関数を使用すると解決できます。

数行のデータが非表示になる場合がありますが、この場合は SUBTOTAL 関数を使用して合計します。非表示がない場合、SUBTOTAL 関数の結果は、次のように SUM 関数の計算結果と一致します。

特定の行 (3、6、9 など) を追加すると、 、12 行）データは非表示になり、結果は次のように変わります。

SUBTOTAL 関数の最初のパラメーターでは、非表示の合計が 109 であることを示すことに注意してください。行は無視されます。

SUBTOTAL 関数については、以前にもチュートリアルが公開されていますので、興味のある方は過去の記事を読んでみてください。

今日説明する問題は、非表示の行を無視した場合の合計方法ではなく、非表示の列を無視した場合の合計方法です。まず第一に、SUBTOTAL 関数ではこれができないことを明確にしておく必要があります。関数のヘルプに明確に記載されています:

SUBTOTAL 関数ではできないだけではありません。これを実行しますが、たとえば、Excel には現時点では非表示の列を無視して集計する機能がまだありません。では、この需要にどのように対処すればよいのでしょうか?これには、比較的新しい関数、つまり CELL 関数を使用する必要があります。

この関数を見たことがある友人は少ないと思いますし、使い方を知っている人もさらに少ないと思いますが、まず CELL 関数が何に使われるのかを簡単に理解しましょう。セルに =c と入力すると、この関数が表示されます。関数を選択すると、簡単な説明が表示されます:

この文で、Go to the CELL 関数を使用して、セルの形式、位置、その他の情報を取得します。この関数をダブルクリックすると、いくつかのオプションが表示されます。

ご覧のとおり、この関数には info_type と Reference という 2 つのパラメーターがあります。最初のパラメータ info_type 情報の種類は全部で 12 種類あり、各種類の具体的な意味は関数で理解できます:

これらの情報に興味のある友達タイプはそれ自体で行うことができます。見てください。どれも非常に理解しやすいものです。ここで使用する最も重要なことは、最後の情報タイプ「幅」です。これは単に列の幅です。

友人の中には、列を非表示にすると列幅が 0 になると考えた人もいるかもしれませんが、これは本当ですか?試してみることができます。数式の最初のパラメータとして「幅」を選択し、2 番目のパラメータを B1 に設定します。これは、セル B1 の列幅 (実際には列 B の列幅) を取得することを意味します。セル B16 に数式 =CELL("width", B1) を入力し、その数式を右に移動します:

結果はすべて 8 です。個々の列の幅を調整して、変化があるかどうかを確認できます。

幅を調整しても、結果は 8 のままです。何か問題がありますか?式？

実は、いいえ、その理由は、CELL 関数が少し気まぐれであるためです。セルの形式が変更されると (列幅は形式です)、結果を更新するために再計算する必要があります。再計算には 2 つの方法があります。1 つは F9 ファンクション キーを押す方法、もう 1 つは任意のセルをダブルクリックして Enter キーを押す方法です。もう一度見ると、結果が更新されていることがわかります:

数値のサイズはセルの幅に対応します。非表示の列の合計を無視する方法について言えば、答えはすでに明らかです。CELL 関数を使用して列幅を取得し、SUMIF 関数を使用して合計を実装します。セル I2 に数式を =SUMIF($B$16:$G$16,">0",B2:G2) と入力し、数式をプルダウンします。

#非表示がない場合、すべてが合計されます。次に、効果を確認するためにいくつかの列を非表示にしてみましょう:

# # 非表示にした後は必ず F9 キーを押すか、ダブルクリックしてください。

問題はここで終わるように見えますが、補助列 (補助行) を使用したくないパートナーが常に存在し、それを実現するために配列数式を使用したいと考えています。たとえば、 =SUMPRODUCT((CELL) ("width",B1 :G1>0)*B2:G2)

この考えは合理的であるように見えますが、これは不可能です。パラメータ参照が特定のセル範囲である場合、関数 CELL は単にセル範囲への情報 エリアの左上のセル つまり、B1:G1のような範囲を書いたのに、B1の列幅しか得られません。 cell 関数の性質は非常に奇妙です~~~

実際、この関数には興味深い使い方がたくさんあります。知りたい場合は、下にメッセージを残してください!

関連する学習の推奨事項:

以上が実践的な Excel スキルの共有: 非表示の列を無視して合計を実行する方法は?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。