Tom
Lv 4
Tom 發問時間: 電腦與網際網路軟體 · 7 年前

函數 對應值的個數統計。

http://www.funp.net/84076

S3=INDIRECT("$B$4:$H$"&COUNTA(A:A))的S$1,

其在INDIRECT("$J$4:$P$"&COUNTA(A:A))的$Q3對應值的個數。

S4=INDIRECT("$J$4:$P$"&COUNTA(A:A))的S$2,

其在INDIRECT("$B$4:$H$"&COUNTA(A:A))的$Q4對應值的個量。

跳欄統計。

統計個數=0時,則顯示""。

EX︰

S$1在J︰P的$Q3對應值共有1個,則顯示1

S$1在J︰P的$Q25對應值共有1個,則顯示1

S$1在J︰P的$Q29對應值共有0個,則顯示""

S$2在B︰H的$Q4對應值共有1個,則顯示1

S$2在B︰H的$Q26對應值共有1個,則顯示1

S$2在B︰H的$Q30對應值共有2個,則顯示2

其餘……以此類推。

請問︰

S3和S4的函數公式?

謝謝!

已更新項目:

以公式說明,好像顯得有點複雜^^"

再以文字重新整理及將說明簡化。

先將搜尋範圍改為B3至H欄有顯示資料的最末列︰

S3=顯示搜尋範圍內與S$1同欄位的下一列(格) =$Q3的次數加總;右拉填滿。

S4=顯示搜尋範圍內與S$2同欄位的上一列(格) =$Q4的次數加總;右拉填滿。

然候選取$3及$4下拉填滿。

請問︰

以上整理後的S3和S4函數公式?

謝謝!!

3 個解答

評分
  • 冰淇
    Lv 6
    7 年前
    最佳解答

    S3 陣列公式 355字

    ~

    筆記本公式檔案

    http://www.funp.net/395905

    ~

    提供測試

    2013-10-16 07:14:30 補充:

    1.公式應右拉下刷及可

    2.公式太長,應有更短,效能更好的,先用,等其他大大的佳解吧

    2013-10-16 15:34:18 補充:

    分四頁自取所需,試試看

    sheet1 定義名稱 一式到位

    sheet2 定義名稱 兩式分離

    sheet3 無定義名稱 一式到位

    sheet4 無定義名稱 兩式分離

    參考檔案

    http://www.funp.net/752868

    2013-10-16 15:37:16 補充:

    格式設為自訂 #

    沒有中括號

    2013-10-16 15:49:12 補充:

    (腦袋不會轉彎)

    請 准大 上答

    2013-10-16 22:11:44 補充:

    S3 一式到位陣列公式

    =IF(SUM((((IF(MOD(ROW(),2),INDIRECT("B4:H"&COUNTA(A:A)),INDIRECT("J4:P"&COUNTA(A:A)))=S$1)*IF(MOD(ROW(),2)=0,INDIRECT("B4:H"&COUNTA(A:A)),INDIRECT("J4:P"&COUNTA(A:A))))=$Q3)*1)=0,"",SUM((((IF(MOD(ROW(),2),INDIRECT("B4:H"&COUNTA(A:A)),INDIRECT("J4:P"&COUNTA(A:A)))=S$1)*IF(MOD(ROW(),2)=0,INDIRECT("B4:H"&COUNTA(A:A)),INDIRECT("J4:P"&COUNTA(A:A))))=$Q3)*1))

    右拉至需求,下刷至需求

    或改 sum 為 sumproduct (一般公式)

    參考檔案

    http://www.funp.net/752868

    分四頁自取所需,試試看

    sheet1 定義名稱 一式到位

    sheet2 定義名稱 兩式分離

    sheet3 無定義名稱 一式到位

    sheet4 無定義名稱 兩式分離

    另見 准大 精解,學習其思考模式

    S3 公式

    =SUMPRODUCT((INDIRECT("B4:H"&COUNTA(A:A))=IF(MOD(ROW(),2),S$1,$Q3))*(INDIRECT("J4:P"&COUNTA(A:A))=IF(MOD(ROW(),2),$Q3,S$1)))

    餘見各意見

    2013-10-17 15:01:57 補充:

    Uti-冰 & Uti-准

    B2:H2 應空白

    B2:H51=J2:P51

    結果變統計 總數

  • ?
    Lv 7
    7 年前

    循冰大模式,參考:

    INDIRECT("B4:H"&COUNTA(A:A))=IF(MOD(ROW(),2),S$1,$Q3)

    INDIRECT("J4:P"&COUNTA(A:A))=IF(MOD(ROW(),2),$Q3,S$1)

    若想公式短,及提高效率,

    就不要使用=IF(計算=0,"",計算),這公式將陣列算了兩次,

    可將格式設為自訂〔#〕不顯示0!

    2013-10-16 15:41:27 補充:

    =SUMPRODUCT((INDIRECT("B4:H"&COUNTA(A:A))=IF(MOD(ROW(),2),S$1,$Q3))*(INDIRECT("J4:P"&COUNTA(A:A))=IF(MOD(ROW(),2),$Q3,S$1)))

    使用冰大的定義名稱可以更短!

    2013-10-16 18:01:20 補充:

    公式原理與冰大相同,且冰大又做了定義名稱,更好用,

    冰大上答最合宜,不要推辭!

  • Tom
    Lv 4
    7 年前

    以公式說明,好像顯得有點複雜^^"

    再以文字重新整理及將說明簡化。

    先將搜尋範圍改為B3至H欄有顯示資料的最末列︰

    S3=顯示搜尋範圍內與S$1同欄位的下一列(格) =$Q3的次數加總;右拉填滿。

    S4=顯示搜尋範圍內與S$2同欄位的上一列(格) =$Q4的次數加總;右拉填滿。

    然後由選取S3︰BO4下拉填滿。

    請問︰

    以上整理後的S3和S4函數公式?

    謝謝!!

    2013-10-16 06:42:37 補充:

    冰大:早安!

    S3 陣列公式測試成功!

    S4位址我自行更動就可以了。

    敬請上答!

    謝謝您^^

    2013-10-16 12:47:43 補充:

    冰大︰

    1.哈~哈~早上將貴公式貼上S3右拉填滿,然後選取S3︰BO4下拉填滿。

    因趕著上班見S3跳行答案正確,尚未細琢就回應,敬請見諒^^"

    2.本題解答以一式到位,字元較多誠屬正常,

    如果分成二式,二行一次下拉,應該就會減少許多字元,

    故請勿客氣!敬請上答。謝謝您^^

    2013-10-16 13:47:35 補充:

    冰大︰

    將貴公式的SUM改為SUMPRODUCT而成一般公式,效率快很多^^

    但一式到位改二式分開,一直改不成功^^"

    2013-10-16 13:54:28 補充:

    一式到位改二式分開,是為了萬一表格格式變動時(目前為初稿),比較好應用^^

    2013-10-16 15:22:24 補充:

    准大:

    謝謝不吝賜教!

    已將格式自訂〔#〕

    可否將貴公式寫全,小弟不大瞭^^"

    謝謝!

    2013-10-16 17:08:07 補充:

    哈~哈~我真是夠拙了^^"

    002的公式,死腦筋,竟將MOD(ROW(),2)改為ROW(A3)和ROW(A4)

    008的公式,少了*

    所以.....^^"

    謝謝冰大和准大的一直耐心賜教。小弟感恩萬分!

    2013-10-16 19:25:22 補充:

    冰大:

    小弟覺得還是維持初意,請您上答。

    准大是出於善意指導,所以他才會常常等發問者提出上答者或結案後,

    再另提供佳解共享。

    倘若您不上答,以後我們就沒機會再加菜~欣賞到准大的高解喔^^

    2013-10-16 19:33:01 補充:

    Sorry~漏回011

    謝謝冰大的善心提醒^^

    小弟瞭解,只是偷懶COPY准大的原文^^"

    2013-10-17 13:26:31 補充:

    冰大︰

    小弟將您及准大的公式應用於新表格後,發現自己還是沒有全部消化^^"

    http://www.funp.net/797027

    以新表格來說︰

    貴原型單列公式>>J︰P01~49直列>>

    B︰H某欄列值=Y$1;其在J︰P對應欄列值=$BV2

    =SUMPRODUCT(((INDIRECT("$B$2:$H$"&COUNTA($A:$A))=Y$1)*INDIRECT("$J$2:$P$"&COUNTA($A:$A))=$BV2)*1)

    答案OK

    2013-10-17 13:30:48 補充:

    R︰X某欄列值=BW$1;其在J︰P對應欄列值=$BV2

    =SUMPRODUCT(((INDIRECT("$R$2:$X$"&COUNTA($A:$A))=BW$1)*INDIRECT("$J$2:$P$"&COUNTA($A:$A))=$BV2)*1)

    答案OK

    2013-10-17 13:31:17 補充:

    應用後公式>>J︰P01~49橫列>>

    B︰H某欄列值=$Y2;其在J︰P對應欄列值=Z$1

    =SUMPRODUCT(((INDIRECT("$B$2:$H$"&COUNTA($A:$A))=$Y2)*INDIRECT("$J$2:$P$"&COUNTA($A:$A))=Z$1)*1)

    答案NG

    2013-10-17 13:31:33 補充:

    R︰X某欄列值=$BW2;其在J︰P對應欄列值= BX$1

    =SUMPRODUCT(((INDIRECT("$R$2:$X$"&COUNTA($A:$A))=$BW2)*INDIRECT("$J$2:$P$"&COUNTA($A:$A))=BX$1)*1)

    答案OK

    准大的單列公式應用後亦然

    不知小弟是哪裡誤解應用錯了?

    敬請您指點迷津。

    謝謝您^^

    2013-10-17 15:14:14 補充:

    哈~哈~沒注意到Uti的B:H資料COPY錯了

    還以為自己又搞混公式^^"

    謝謝您的耐心抓錯和指導^^

還有問題?馬上發問,尋求解答。