再來一個按顔色求和的解法:先用函數提取背景色号,再用公式忽略隐藏行求同色之和。
越說越玄乎了,但我絕不是故意語出驚人,不信往下看。
案例:對下圖 1 中的綠色區域求和,求和結果要自動忽略隐藏行,效果如下圖 2 所示。
解決方案:
1. 按 Ctrl F3 --> 在彈出的對話框中點擊“新建”
2. 在彈出對話框中按以下方式設置 --> 點擊“确定”:
- 名稱:輸入 color
- 引用位置:輸入“=get.cell(63,B2)”
* 請注意:B2 必須相對引用。
公式釋義:
- GET.CELL 是個宏表函數,用于獲取單元格的信息;
- 參數 63 表示返回單元格的填充色。
有關 get.cell 函數的詳解,請參閱 如果我說有一個函數可以獲取 Excel 單元格的背景色,您信嗎?
3. 點擊“關閉”
4. 将 C 列設置為第一個輔助列 --> 在 C2 單元格中輸入以下公式 --> 下拉複制公式:
=color
5. 将 D 列設置為第二個輔助列 --> 在 D2 單元格中輸入以下公式 --> 下拉複制公式:
=B2/(C2=42)
公式釋義:
- C2=42:判斷 C 列的值是否等于 42,即單元格顔色是否為綠色;結果為 true 或 false,即 1 或 0;
- B2/...:将 B2 的值處于上述邏輯值,當分母為 0 時為錯誤值,為 1 時等于 B2 自身
6. 在 F2 單元格中輸入以下公式:
=AGGREGATE(9,3,D2:D24)
公式釋義:
- aggregate 是個全能函數,第一個參數 9 表示求和,第二個參數 3 表示忽略隐藏行、錯誤值、嵌套 SUBTOTAL 和 AGGREGATE 函數,第三個參數是計算區域
有關 aggregate 函數的詳解,請參閱 Excel函數–集19個函數功能于1身的全能函數aggregate。
已經得出了所需的求和結果。
7. 如果随機隐藏部分行
F2 單元格的求和結果就自動更新了。
8. 隐藏輔助列,便是最終結果。
很多同學會覺得 Excel 單個案例講解有些碎片化,初學者未必能完全理解和掌握。不少同學都希望有一套完整的圖文教學,從最基礎的概念開始,一步步由簡入繁、從入門到精通,系統化地講解 Excel 的各個知識點。
現在終于有了,以下專欄,從最基礎的操作和概念講起,用生動、有趣的案例帶大家逐一掌握 Excel 的操作技巧、快捷鍵大全、函數公式、數據透視表、圖表、打印技巧等……學完全本,你也能成為 Excel 高手。
,