2238Excel 求不連續資料顯示方法 - 錦子老師

由於會使用到IFERROR、OFFSET、SMALL、SUMPRODUCT、ROW、COUNTIF函數，故對許多人來說會比較麻煩。

1、點取B15儲存格輸入公式：. 關閉廣告 錦子老師 跳到主文 由於疫情關係，錦子老師己沒法回覆各位親朋好友的問題，請見諒。

對電腦&網路資訊充滿興趣與熱情、並針對微軟Office的各種教學，瘋了似的每日堅持發文2~3篇，包括Excel、Word、PowerPoint、Project、Visio等....，希望能讓大家一看就明瞭，如果有任何問題也歡迎在LINE留言詢問。

謝謝！ 部落格全站分類：數位生活 相簿 部落格 留言 名片 Sep27Fri201920:34 2238Excel求不連續資料顯示方法 2238 Excel求不連續資料顯示方法 錦子老師您好： 想從資料中判斷「總計」>0時，會下方顯示相對應資料的方法，看過OFFSET函數的不連續方法，但都失敗，有方法嗎?請教教我，謝謝！ 由於會使用到IFERROR、OFFSET、SMALL、SUMPRODUCT、ROW、COUNTIF函數，故對許多人來說會比較麻煩。

1、點取B15儲存格輸入公式： =IFERROR(OFFSET($B$1,SUMPRODUCT(SMALL(($O$2:$O$11>0)*(ROW($O$2:$O$11)),COUNTIF($O$2:$O$11,0)+ROW()-14))-1,0,1,1),"") 也可以將公式更改為SNARE網友提供的陣列公式(按CTRL+SHIFT+ENTER鍵完成輸入)： =IFERROR(INDEX(OFFSET($B$2:$B$11,,(COLUMN()-2)*13),SMALL(IF($O$2:$O$11>0,ROW($O$2:$O$11)-ROW($O$2)+1),ROW()-14)),"") 再將公式複製到B16:B24儲存格。

【公式解說】 ($O$2:$O$11>0)傳回O2:O11儲存格>0的儲存格有那些(TRUE、FALSE、TRUE、FALSE、FALSE、TRUE、TRUE、TRUE、FALSE、TRUE)。

(Row($O$2:$O$11))傳回O2:O11儲存格的列號(2,3,4,5,6,7,8,9,10,11)。

Countif($O$2:$O$11,0)統計O2:O11儲存格中內容為0的儲存格數目。

Small(($O$2:$O$11>0)*(Row($O$2:$O$11)),Countif($O$2:$O$11,0)+Row()-14)抓取($O$2:$O$11>0)乘(Row($O$2:$O$11))的數值，第N(Countif($O$2:$O$11,0)+列號-14)小的值。

這是由於0值一樣會在統計中，故要先將內容為0的儲存格數量統計出來後依列順序加值，由於再第15列開始故要減14變成為1，依序遞增。

Sumproduct(Small(($O$2:$O$11>0)*(Row($O$2:$O$11)),Countif($O$2:$O$11,0)+Row()-14))-1將統計出的數值減1，這是因為要從第一列開始移動，故必須將移動值減1再會正確對應到該儲存格。

Offset($B$1,Sumproduct(Small(($O$2:$O$11>0)*(Row($O$2:$O$11)),Countif($O$2:$O$11,0)+Row()-14))-1,0,1,1)從B1儲存格開始移動N(Sumproduct(Small(($O$2:$O$11>0)*(Row($O$2:$O$11)),Countif($O$2:$O$11,0)+Row()-14))-1)列，0欄，框選1列1欄。

而C15的公式與B15的公式差別只是在OFFSET的第一個參數，C15為O1儲存格來移動。

Iferror(Offset($B$1,Sumproduct(Small(($O$2:$O$11>0)*(Row($O$2:$O$11)),Countif($O$2:$O$11,0)+Row()-14))-1,0,1,1),"")如果出現錯誤訊息則不填入資料。

  文章標籤 iferror countif row sumproduct small offset 黃金麵線 立可康餐廳 大安料理遊樂場 全站熱搜 創作者介紹 錦子老師 錦子老師 錦子老師發表在痞客邦留言(0)人氣() E-mail轉寄 全站分類：數位生活個人分類：EXCEL此分類上一篇：2218Excel序號產生-IF、COUNTIF、VLOOKUP函數 此分類下一篇：Q133Excel依條件自動格式化整列 上一篇：老天真的有眼 下一篇：Q133Excel依條件自動格式化整列 歷史上的今天 2021:3687Google精用密技-快速查看歷史記錄 2021:3272Excel如何刪除不要英文字串 2021:2577Excel數字出現統計問題-EXCEL2019版 2020:2858Win10輸入法 2020:706使用Excel函數來按月份對資料進行匯總的方法 2019:老天真的有眼 2019:2218Excel序號產生-IF、COUNTIF、VLOOKUP函數 2019:Q132Outlook的收件匣內如何顯示出日期及寄件者 2018:W0217Word2016文件中給超連結添加提示的方法 2018:977股票超連結-HYPERLINK函數 2018:老年人為什麼熱衷於歌唱 2017:36716種常見的COUNTIF函數公式設置 ▲top 留言列表 發表留言 文章分類 光華商職(2) 作業考試(15)心得文章(32) 軟體教學(18) ACCESS(5)CORELDRAW(12)EXCEL(3062)FACEBOOK(32)FLASH(4)GOOGLE(90)ONENOTE(1)OUTLOOK(15)PHOTOCAP(7)POWERPOINT(38)PROJECT(30)PS-5(16)WIN(171)WORD(328)影像美工(9)痞客邦(5)電子商務(40)雜七雜八(87) 雜七雜八(9) 個人心情(16)親子教育(38)養生保健(142)感人故事(54)娛樂休閒(173)勵志小品(150)政經人文(108)食譜(10)分享(45) TQC(3) TQCEXCEL2003(37)TQCWORD2003(31)TQC(2) 丙級檢定(2) 電腦軟體應用(18)網頁設計(4) 手機(2) LINE(52)Instagram(3) 未分類文章(3) 贊助商連結 最佳美味 剛出爐包子 動態訂閱 文章精選 文章精選 2022六月(5) 2022五月(34) 2022四月(35) 2022三月(63) 2022二月(83) 2022一月(93) 2021十二月(93) 2021十一月(92) 2021十月(87) 2021九月(67) 2021八月(63) 2021七月(62) 2021六月(72) 2021五月(94) 2021四月(88) 2021三月(95) 2021二月(86) 2021一月(91) 2020十二月(69) 2020十一月(62) 2020十月(70) 2020九月(61) 2020八月(69) 2020七月(64) 2020六月(63) 2020五月(69) 2020四月(67) 2020三月(66) 2020二月(47) 2020一月(68) 2019十二月(81) 2019十一月(81) 2019十月(79) 2019九月(69) 2019八月(91) 2019七月(71) 2019六月(56) 2019五月(56) 2019四月(40) 2019三月(36) 2019二月(31) 2019一月(60) 2018十二月(54) 2018十一月(47) 2018十月(39) 2018九月(83) 2018八月(47) 2018七月(37) 2018六月(37) 2018五月(44) 2018四月(39) 2018三月(37) 2018二月(33) 2018一月(41) 2017十二月(37) 2017十一月(36) 2017十月(36) 2017九月(36) 2017八月(38) 2017七月(39) 2017六月(69) 2017五月(79) 2017四月(63) 2017三月(55) 2017二月(65) 2017一月(60) 2016十二月(47) 2016十一月(25) 2016十月(72) 2016四月(4) 2016三月(1) 2016二月(2) 2016一月(11) 2015十二月(19) 2015十一月(3) 2015十月(5) 2015四月(7) 2015三月(4) 2015二月(4) 2015一月(24) 2014十二月(16) 2014十一月(1) 2014十月(14) 2014八月(24) 2014六月(15) 2014五月(31) 2014四月(32) 2014三月(3) 2012十二月(10) 2012十一月(12) 2012十月(8) 2012九月(9) 2012五月(16) 2012四月(19) 2012三月(23) 2012二月(10) 2012一月(17) 2011十二月(50) 2011十一月(70) 2011十月(3) 所有文章列表 新聞交換(RSS) 來來去去，萬分珍惜！ 參觀人氣 本日人氣： 累積人氣： POWEREDBY (登入) {{article.user_name}} {{article.timestamp*1000|date:'MMM.dd.y.hh.mm'}} {{article.title}} {{article.content}} 我要留言 RSS訂閱 小小回應，錦子感恩 月曆 « 六月2022 » 日 一 二 三 四 五 六       1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30     活動快報 蘭蔻冰珠亮眼粹 蘭蔻超未來肌因冰珠亮眼粹，注入小黑瓶獨家活性粹取... 看更多活動好康 站方公告 [公告]2022年度農曆春節期間服務公告[公告]MIB廣告分潤計劃、PIXwallet錢包帳戶條款異動通知[公告]2021年度農曆春節期間服務公告 文章搜尋 我的連結 待過公司  光華商職厚生市集泰和清潔數位鳳凰兆豐銀行增你強股份有限公司瑪琍歐玩具美哲塑胶制品有限公司廈門-福聯飯店華康科技華經資訊誼光保全 線上免費教學 中小企業網路大學校文建會-藝學網職業訓練網路大學 網拍  郵局快捷貨到付款 友情連結   文建會兒童文化館中國小商品連結網免費LOGO製作免費線上作印章SM&MERRY品牌專櫃女鞋官方部落格美圖秀秀POWERPOINT樣板彩字秀 好友  董老師部落格謝老師部落格 學生  阿玉麻粩部落格 贊助商連結 我的好友 回到頁首 回到主文 免費註冊 客服中心 痞客邦首頁 ©2003-2022PIXNET 關閉視窗 PIXNET Facebook Yahoo! Google MSN {{guestName}} (登出) 您尚未登入，將以訪客身份留言。

亦可以上方服務帳號登入留言 請輸入暱稱(最多顯示6個中文字元) 請輸入標題(最多顯示9個中文字元) 請輸入內容(最多140個中文字元) 請輸入左方認證碼： 看不懂,換張圖 請輸入驗證碼 送出留言