不用CELL輔助行實(shí)現(xiàn)忽略隱藏列求和
?
作者:小窩來源:部落窩教育發(fā)布時(shí)間:2023-12-19 16:05:16點(diǎn)擊:637
今天小窩介紹不用單元格寬度輔助行也能忽略隱藏列求和的方法。之所以如此,主要是CELL和INDIRECT函數(shù)都“怪”,怪到一起變神奇了。
很久前在部落窩官網(wǎng)上曾分享過《如何忽略隱藏列求和》文章。
忽略隱藏值求和,大家第一反應(yīng)就是用SUBTOTAL函數(shù),但是SUBTOTAL函數(shù)只對(duì)隱藏行有效。因此文章分享了一個(gè)運(yùn)用CELL函數(shù)建立單元格width寬度輔助行,然后再條件求和,實(shí)現(xiàn)忽略隱藏列求和的方法。
文章最后提到不可能只用一個(gè)公式如“=SUMPRODUCT((CELL("width",B1:G1>0)*B2:G2)”實(shí)現(xiàn)忽略隱藏列求和,因?yàn)?span>CELL函數(shù)只返回引用區(qū)域,如B1:G1,中左上單元格,即B1單元格對(duì)應(yīng)的值。
真的就只能建立輔助行后才能實(shí)現(xiàn)忽略隱藏列求和嗎?
小窩測(cè)試了多種方法,最終發(fā)現(xiàn)搭配INDIRECT函數(shù)可以不用輔助行。
在H2中輸入如下公式并向下填充:
=SUMPRODUCT((CELL("width",INDIRECT("r1c"&COLUMN(B:G),0))>0)*B2:G2)
我們來測(cè)試一下。
隱藏C列,然后按F9刷新,合計(jì)值忽略了C列。
順便說說INDIRECT和CELL兩個(gè)函數(shù)的怪處。
INDIRECT的怪處:不能單獨(dú)輸出數(shù)組。
它可以用數(shù)組實(shí)現(xiàn)對(duì)多個(gè)區(qū)域的引用,但它不能單獨(dú)輸出數(shù)組。譬如,引用B2:G2的數(shù)據(jù),輸入下方公式結(jié)果是錯(cuò)誤的:
=INDIRECT("r2c"&COLUMN(B:G),0)
在編輯欄中選中"r2c"&COLUMN(B:G)按F9,可以看到一組地址:
這些地址單獨(dú)用于INDIRECT函數(shù),都能正確引用數(shù)據(jù):
如果在INDIRECT外嵌套聚合函數(shù),如求和、最大最小值函數(shù)等,也能得到正確結(jié)果:
CELL的怪處:在新版本中計(jì)算單元格width,需嵌套聚合函數(shù)。
新版本支持動(dòng)態(tài)數(shù)組,CELL("width")會(huì)輸出兩個(gè)值:寬度值和是否是默認(rèn)寬度的邏輯值。
如果要建立單元格寬度輔助行,必須外套MAX函數(shù)后才能向右填充:
沒想到兩怪合一,反而解決了不用輔助行的問題,也是神奇哈。
OK,關(guān)于不用寬度輔助行實(shí)現(xiàn)忽略隱藏列求和的方法就介紹這么多。
本文配套的練習(xí)課件請(qǐng)?zhí)砑涌头⑿?span>buluowojiaoyu索取。
做Excel高手,快速提升工作效率,部落窩教育Excel精品好課任你選擇!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
版權(quán)申明:
本文作者小窩;部落窩教育享有稿件專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!