帖子: 233
主題: 53
精華: 0
積分: 292
點名: 0
作業系統: WinXp
軟體版本: 2010
閱讀權限: 20
性別: 男
註冊時間: 2012-1-6
最後登錄: 2024-3-8

論壇贊助勳章

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2014-7-3 08:26 | 只看該作者

[發問] 請問能將內文中的範圍公式擴大嗎?

本帖最後由 jackson7015 於 2014-7-3 08:30 編輯

請問各位前輩；
如何才能將下列公式中的$V$5:$V$22，更改成V列整列的範圍?
{=COUNT(FIND(TRANSPOSE($V$5:$V$22),($E10)))}

如果改成$V:$V，會造成空白處也一併計算
我想只計算V列有內文的部分就好了

每次要新增內文，就需要整個重新調整過
望站上大大能幫忙，謝謝~

樓主熱帖

p212

高中生 Rank: 3 Rank: 3

帖子: 618
主題: 17
精華: 0
積分: 663
點名: 1
作業系統: WINDOWS 7
軟體版本: OFFICE 2013
閱讀權限: 50
性別: 男
來自: Pingtung
註冊時間: 2011-9-2
最後登錄: 2024-7-5

2014馬年全勤獎章 2013蛇年全勤獎章熱烈參與勳章

2^#

發表於 2014-7-3 08:55 | 只看該作者

本帖最後由 p212 於 2014-7-3 09:03 編輯

回復 1# jackson7015
建議使用定義名稱，其「參照到」用OFFSET函數解決。
例如：
1、資料$V$5:$V$22，其欄位名稱位於儲存格V4
定義名稱為「DATA」，「參照到」輸入
=OFFSET(Sheet1!$V$5,,,COUNTA(Sheet1!$V:$V)-1)
2、1#之原公式修改為
{=COUNT(FIND(TRANSPOSE(DATA),($E10)))}
請參考！

TOP

jackson7015

中學生 Rank: 2

帖子: 233
主題: 53
精華: 0
積分: 292
點名: 0
作業系統: WinXp
軟體版本: 2010
閱讀權限: 20
性別: 男
註冊時間: 2012-1-6
最後登錄: 2024-3-8

論壇贊助勳章

3^#

發表於 2014-7-3 13:58 | 只看該作者

回復 2# p212

感謝p212大大

公式測試成功
執行正常

定義名稱使用方式，目前還無法參透
剛好可以練習看看

謝謝~

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

4^#

發表於 2014-7-6 10:23 | 只看該作者

回復 1# jackson7015

{=COUNT(FIND(TRANSPOSE($V$5:$V$22),($E10)))}

一、TRANSPOSE函數有沒有用應該不影響答案
　可以改為如下
　{=COUNT(FIND($V$5:$V$22,$E10))}

二、動態資料範圍是很重要的技巧，可以直接放在公式中或用名稱定義，用名稱定義可以讓公式簡短看起來清楚明瞭，若多處應運也可以加速公式處理
　一般使用OFFSET函數，OFFSET(左上角位置,列偏移,欄偏移,高度,寬度)
　以本例示範為OFFSET($V$5,0,0,COUNTA($V$5:$V$999),1)
　常見簡化為　OFFSET($V$5,,,COUNTA($V$5:$V$999))，列偏移,欄偏移　沒有填寫視為０，最後　寬度　沒有填寫內定為１（寬度　前面有 , 時要填1）
　帶入公式內
{=COUNT(FIND(OFFSET($V$5,,,COUNTA($V$5:$V$999)),$E10))}

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

jackson7015

中學生 Rank: 2

帖子: 233
主題: 53
精華: 0
積分: 292
點名: 0
作業系統: WinXp
軟體版本: 2010
閱讀權限: 20
性別: 男
註冊時間: 2012-1-6
最後登錄: 2024-3-8

論壇贊助勳章

5^#

發表於 2014-7-7 08:53 | 只看該作者

回復 4# ML089
感謝ML089版大的回應指導

一、TRANSPOSE函數有沒有用應該不影響答案
是說，原本運算方式，會直接自行從列開始運算，所以才不需要TRANSPOSE函數去轉置嗎?

二、動態資料範圍是很重要的技巧，可以直接放在公式中或用名稱定義，用名稱定義可以讓公式簡短看起來清楚明瞭，若多處應運也可以加速公式處理
測試公式後，發現亦可將範圍$V$5:$V$999改成$V:$V全範圍

個人比較喜歡直觀公式，因為在使用名稱定義的時候，需要再去看看名稱定義內的公式條件
不過名稱定義真的可以節省很多公式運算

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

6^#

發表於 2014-7-7 11:28 | 只看該作者

回復 5# jackson7015

一、TRANSPOSE函數有沒有用應該不影響答案
是說，原本運算方式，會直接自行從列開始運算，所以才不需要TRANSPOSE函數去轉置嗎?
回覆: 就目前的公式來判斷不需要。

二、動態資料範圍是很重要的技巧，可以直接放在公式中或用名稱定義，用名稱定義可以讓公式簡短看起來清楚明瞭，若多處應運也可以加速公式處理
測試公式後，發現亦可將範圍$V$5:$V$999改成$V:$V全範圍
回覆:改成$V:$V全範圍比較簡潔但運算會比較慢，若公式很多(幾萬個)時會影響，少量時沒差。

個人比較喜歡直觀公式，因為在使用名稱定義的時候，需要再去看看名稱定義內的公式條件
不過名稱定義真的可以節省很多公式運算

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

jackson7015

中學生 Rank: 2

帖子: 233
主題: 53
精華: 0
積分: 292
點名: 0
作業系統: WinXp
軟體版本: 2010
閱讀權限: 20
性別: 男
註冊時間: 2012-1-6
最後登錄: 2024-3-8

論壇贊助勳章

7^#

發表於 2014-7-9 08:23 | 只看該作者

回復 6# ML089
感謝ML089大大的不吝指教提點
原本沒想到運算時間問題，感謝提點
使用部分範圍會比較適用~

謝謝!!

TOP