Board logo

標題: [發問] 請問 搜尋一固編號 至 空白 再往下搜期搜尋 取出工作表全部相同標號之資料 [打印本頁]
作者: mycmyc    時間: 2012-4-23 17:28     標題: 請問 搜尋一固編號 至 空白 再往下搜期搜尋 取出工作表全部相同標號之資料

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

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

運用 函數 搜尋 取出 資料
如圖 紅色 資料 取出 到另工作表

用 科目代號  6180 搜尋 至 空白行   再往下搜期6180 至 空白行  取出工作表全部6180至 空白行 之資料

試了很多方法多失敗  請問  運用 函數  有辦法   做這一搜尋嗎?  謝謝  麻煩指教

[attach]10610[/attach][attach]10610[/attach][attach]10610[/attach]0



[attach]10611[/attach]
作者: register313    時間: 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)&"")   陣列公式  往右複製 往下複製
[attach]10618[/attach]
[attach]10619[/attach]
作者: mycmyc    時間: 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)&"")  
可以有 更精簡 作法嗎？  
對不起  冒昧 要求  
謝謝你
作者: register313    時間: 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)&""   陣列公式
作者: mycmyc    時間: 2012-4-24 00:30

回復 4# register313

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

謝謝你
作者: Hsieh    時間: 2012-4-25 15:11

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

回復 2# register313


    美中不足，(如圖)
[attach]10660[/attach]
[attach]10662[/attach]
作者: mycmyc    時間: 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 列出
請妳指教  謝謝妳
作者: Hsieh    時間: 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"

[attach]10665[/attach]
作者: mycmyc    時間: 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 不明白  所以括號錯放位置   而不成功

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

謝謝你  感恩



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)