[發問] 請問 搜尋一固編號 至 空白 再往下搜期搜尋 取出工作表全部相同標號之資料

帖子: 173
主題: 42
精華: 0
積分: 220
點名: 0
作業系統: win10
軟體版本: Office 365
閱讀權限: 20
性別: 男
註冊時間: 2010-10-13
最後登錄: 2023-10-11

9^#

發表於 2012-4-25 22:54 | 只看該作者

本帖最後由 mycmyc 於 2012-4-25 23:20 編輯

回復 8# Hsieh

感謝 Hsieh 大大  撥空做詳細解說
原來 x、y、z、a都是定義名稱
對不起沒詳細檢查
INDIRECT("Sheet1!"&CHAR(COLUMN(B1)+64)&a)
我有在程式列一一查詢
對&a 不明白  所以括號錯放位置而不成功

感謝你一一解釋  我無法馬上理解
我得一一嘗試謝謝你
不懂之處在煩請你指導

謝謝你  感恩

HI

Hsieh

超級版主 Rank: 8 Rank: 8

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 256
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2024-11-26

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

8^#

發表於 2012-4-25 20:05 | 只看該作者

回復 7# mycmyc
x、y、z、a都是定義名稱，作用儲存格位於第2列時，建立此4個名稱
1、取得A1到此列為止，最後一個文字位置，因為你的日期並非正確日期格式，是以文字串形成的資料，所以必須使用文字搜尋
x參照到公式
=LOOKUP("龘",Sheet1!$A$1:$A2,ROW(Sheet1!$1:2))
可改成
x參照到公式
=LOOKUP(2,1/(Sheet1!$A$1:$A2<>""),ROW(Sheet1!$1:2))
這樣可配合任何資料型態
2、本列以下到第2000列的B欄若為空白就傳回列號，求得這些列號的最小值，這樣可取得資料尾端。
y參照到公式
=MIN(IF(Sheet1!$B2:$B$2000="",ROW(Sheet1!$B2:$B$2000),""))
3、傳回每個區域範圍
z參照到公式
=INDIRECT("Sheet1!B"&x&":B"&y-1)
4、傳回E欄的所有數值
a參照到公式
=SMALL(Sheet1!$E$2:$E$2000,ROW(!A1))
5、E2公式=IF((B2<>"")*(COUNTIF(z,$F$1)>0),ROW(),"")
假如B欄不等於空白，而且所在列的連續資料範圍中含有搜尋準則F1的值，就傳回該列的列號。
6、Sheet2!A2公式=IF(ISERROR(a),"",INDIRECT("Sheet1!"&CHAR(COLUMN(A1)+64)&a))
如果超過Sheet1!E欄數值的數量，a就會是錯誤值，所以判斷a為數值就帶入INDIRECT("Sheet1!"&CHAR(COLUMN(A1)+64)&a)這段來計算
CHAR(COLUMN(A1)+64)這會傳回字串"A"，向右複製時就成為CHAR(COLUMN(B1)+64)，傳回字串"B"，以此類推
INDIRECT是以儲存格參照字串來傳回該位置，所以，("Sheet1!"&CHAR(COLUMN(A1)+64)&a就等於字串"Sheet1!A2"

變動範圍.rar (7.58 KB)

學海無涯_不恥下問

帖子: 173
主題: 42
精華: 0
積分: 220
點名: 0
作業系統: win10
軟體版本: Office 365
閱讀權限: 20
性別: 男
註冊時間: 2010-10-13
最後登錄: 2023-10-11

7^#

發表於 2012-4-25 18:50 | 只看該作者

本帖最後由 mycmyc 於 2012-4-25 18:55 編輯

回復 6# Hsieh

感謝 Hsieh  大大
妳厲害看出小弟需求
搜尋並把整個收支整個列出
並說明了  遺漏之處
如 B24 即  遺漏之處
感謝
但小弟有些不明白
COUNTIF(z,$F$1)>0
其中 Z  代表意思
INDIRECT(CHAR(COLUMN(B5)+64)&a)
&a  代表意思

還有  我沒能力表妳函數  改成在 Sheet2 列出
我希望結果在Sheet2 列出
請妳指教  謝謝妳

HI

Hsieh

超級版主 Rank: 8 Rank: 8

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 256
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2024-11-26

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

6^#

發表於 2012-4-25 15:11 | 只看該作者

本帖最後由 Hsieh 於 2012-4-25 15:19 編輯

回復 2# register313

美中不足，(如圖)

Book1.rar (8.01 KB)

學海無涯_不恥下問

帖子: 173
主題: 42
精華: 0
積分: 220
點名: 0
作業系統: win10
軟體版本: Office 365
閱讀權限: 20
性別: 男
註冊時間: 2010-10-13
最後登錄: 2023-10-11

5^#

發表於 2012-4-24 00:30 | 只看該作者

回復 4# register313

感謝 register313 大大
謝謝
小弟不才需要時間消化
但由衷感謝大大幫忙

謝謝你

HI

register313

　頭銜: 麻辣路人甲

大學生 Rank: 4

帖子: 967
主題: 0
精華: 0
積分: 1001
點名: 0
作業系統: WIN XP
軟體版本: OFFICE 2003
閱讀權限: 50
性別: 男
來自: 台北
註冊時間: 2010-11-29
最後登錄: 2022-5-17

2012龍年全勤獎章熱烈參與勳章

4^#

發表於 2012-4-24 00:25 | 只看該作者

回復 3# mycmyc

G2=INDEX($A:$D,SMALL(IF($E$2:$E$25="Y",ROW($E$2:$E$25),65536),ROW(A1)),COLUMN()-6)&"" 陣列公式

帖子: 173
主題: 42
精華: 0
積分: 220
點名: 0
作業系統: win10
軟體版本: Office 365
閱讀權限: 20
性別: 男
註冊時間: 2010-10-13
最後登錄: 2023-10-11

3^#

發表於 2012-4-23 23:35 | 只看該作者

本帖最後由 mycmyc 於 2012-4-23 23:37 編輯

回復 2# register313

感謝 register313  大大指導

輔助欄
E2=IF(B2="","",IF(OR(B2=6180,E1="Y"),"Y",""))
真是巧思讓複雜問題單純化

我一直想用 , 一行解決,  但多無法達成
謝謝你
G2=IF(INDEX($A:$D,SMALL(IF($E$2:$E$25="Y",ROW($E$2:$E$25),65536),ROW(A1)),COLUMN()-6)="","",INDEX($A:$D,SMALL(IF($E$2:$E$25="Y",ROW($E$2:$E$25),65536),ROW(A1)),COLUMN()-6)&"")
可以有更精簡作法嗎？
對不起  冒昧要求
謝謝你

HI

register313

　頭銜: 麻辣路人甲

大學生 Rank: 4

帖子: 967
主題: 0
精華: 0
積分: 1001
點名: 0
作業系統: WIN XP
軟體版本: OFFICE 2003
閱讀權限: 50
性別: 男
來自: 台北
註冊時間: 2010-11-29
最後登錄: 2022-5-17

2012龍年全勤獎章熱烈參與勳章

2^#

發表於 2012-4-23 23:06 | 只看該作者

回復 1# mycmyc

輔助欄
E2=IF(B2="","",IF(OR(B2=6180,E1="Y"),"Y","")) 往下複製
G2=IF(INDEX($A:$D,SMALL(IF($E$2:$E$25="Y",ROW($E$2:$E$25),65536),ROW(A1)),COLUMN()-6)="","",INDEX($A:$D,SMALL(IF($E$2:$E$25="Y",ROW($E$2:$E$25),65536),ROW(A1)),COLUMN()-6)&"") 陣列公式往右複製往下複製

B00K1.rar (9.28 KB)