“說起Excel中各種各樣致命的錯誤,那就不得不提到隱藏的行或者列所帶來的數據歧義。為了幫助我們儘可能得避免這樣的失誤,讓我們討論下如何製作一個隱藏行列的提示器。
01 提示被隱藏的行數
先考慮製作一個輔助列,將其的數值標為1。
然後再B2中輸入這樣一個公式命令:
=COUNT(Table1[訂單計數]) - AGGREGATE(2,5,Table1[訂單計數])
一起來解讀下這個公式的含義:
- COUNT(Table1[訂單計數])
- 合計輔助數列,也就是統計整個表格行數量
- AGGREGATE(2,5,Table1[訂單計數])
- Aggregate作為一個功能強大的函數,它能夠實現對非隱藏行的求和。在這裡參數1設置為2,代表求和;參數2設置為5,則代表忽略隱藏行。
而兩者的差值,就是被隱藏掉的行的數量。
02提示被隱藏的列數
有了AGGREGATE函數,我們照著上文,再寫一個對被隱藏列數的計算公式,可好?
很不幸的是,AGGREGATE函數只對行有效,對於計算被隱藏的列數,我們得另尋出路了。
可以嘗試如此改造表格,
- 添加一個輔助行,並輸入對應的函數公式 =IF(CELL(“width”,A1)>1,1,0)
- 利用CELL函數中求單元格寬度的命令,可以將被隱藏的單元格標註為0值,而未被隱藏的單元格標註為1值。
- 在B3單元格中輸入 =COUNT(A1:J1)-SUM(A1:J1)
- 從而計算出實際列數量與未被隱藏的列數間差異,從而得到被隱藏的列數。
不過要注意的是,這並不是一個完美的公式,因為當設置隱藏列或者取消隱藏列時,這個公式無法自動重新計算:
比如在上表基礎上取消對於E、F列的隱藏,輔助行並沒有重新計算,因而最終計算的隱藏行數出現了錯誤。
因此,你需要做的是,在每次隱藏或者取消隱藏後,都應該使用F9這個快捷鍵,強制讓所有公式進行重新計算。
閱讀更多 效率火箭 的文章