iT邦幫忙

2

Excel工作表之間,動態超連結的關鍵字搜尋(已改善公式用法)

  • 分享至 

  • xImage

我Google一下,好像沒人分享做法?
我研究了一下Exel公式
終於弄出我想要的動態超連結
這個只限於Excel裡面使用
若是動態檔案超連結,這個我沒有研究(就是檔案換了任何位置,Excel都能正常點擊)
若是工作表之間的儲存格動態超連結,公式是寫出來了,只是大家想法會跟我一樣嗎?

我先貼圖,再貼公式

1.首先 工作表1 有兩個欄位是 資料 紀錄超連結目標位置
https://ithelp.ithome.com.tw/upload/images/20241030/20061369puTOGupKIk.png

2.工作表2 有一個欄位是 超連結
https://ithelp.ithome.com.tw/upload/images/20241030/200613693Mw56VHQza.png

3.當我在 工作表1 新增兩列時,那麼超連結目標位置,就會換成新的位置,那我從 工作表2 點擊超連結的時候,就會移動到 工作表1 新的超連結位置
https://ithelp.ithome.com.tw/upload/images/20241030/20061369Kmv27NxhOZ.png

這邊有哪位高手可以知道可能用了什麼公式寫法嗎?

--

更新分享

恩..這個問題需求原由,是來自我要做資料庫結構Excel紀錄(主管想看資料庫欄位紀錄)

我用Sql查詢目前資料庫的所有資料表

select a.name [表格名稱]
,a.crdate [建檔日期]
,b.rows [資料筆數]
from sysobjects AS a 
join sysindexes AS b on a.id = b.id
where (a.type = 'U') AND (b.indid IN (0, 1)) 
order by a.name

然後再用SQL查詢所有資料表的結構(這樣可以知道前人設定什麼備註說明、主鍵、關聯鍵FK)

declare @TmpSchema table(
	TableName nvarchar(250)
	,ColumnName nvarchar(250)
	,ConstraintName nvarchar(250)
	,XType nvarchar(50)
)
insert into @TmpSchema
SELECT A.NAME [TableName]
,c.COLUMN_NAME [ColumnName]
,B.NAME AS [ConstraintName]
,B.XTYPE
FROM SYSOBJECTS A 
join SYSOBJECTS B on A.ID = B.PARENT_OBJ 
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE C on b.name = c.CONSTRAINT_NAME

SELECT a.Table_name [表格名稱]
,b.ORDINAL_POSITION [欄位順序]
,(
	case isNull(c.XType,'')
	when 'PK' then N'主鍵'
	when 'F' then N'關聯鍵'
	else isNull(c.XType,'')
	end
) [主鍵類型]
,isNull(c.ConstraintName,'') [主鍵命名]
,b.COLUMN_NAME [欄位名稱]
,b.DATA_TYPE [資料型別]
,(
	case when isNull(Convert(varchar,b.CHARACTER_MAXIMUM_LENGTH),'') = '-1'
	then 'Max'
	else isNull(Convert(varchar,b.CHARACTER_MAXIMUM_LENGTH),'')
	end
) [長度]   
,isnull(b.COLUMN_DEFAULT,'') as [預設值]   
,b.IS_NULLABLE as [Null 值]
,isNull((
	SELECT value   
	FROM fn_listextendedproperty (NULL, 'schema', a.Table_schema, 'table', a.TABLE_NAME, 'column', default)   
	WHERE name='MS_Description' 
	and objtype='COLUMN'    
	and objname Collate Chinese_Taiwan_Stroke_CI_AS = b.COLUMN_NAME   
),'') as [欄位描述]   
from INFORMATION_SCHEMA.TABLES  a   
left join INFORMATION_SCHEMA.COLUMNS b ON a.TABLE_NAME = b.TABLE_NAME   
left join @TmpSchema c on a.Table_name = c.TableName
and b.COLUMN_NAME = c.ColumnName
where TABLE_TYPE='BASE TABLE'
order by a.TABLE_NAME 
, b.ORDINAL_POSITION 

早上花了點時間查Google,發現好像沒有文章,類似我想要的超連結效果

菩薩慈悲...勸退求答者...0.0
https://ithelp.ithome.com.tw/questions/10212185

後來在三個文章找到關鍵字(我自己用程式思維推敲的)

1.基本超連結的用法
https://zanzan.tw/archives/4359

2.取得目前儲存格位置
https://www.ptt.cc/bbs/Office/M.1385477705.A.43B.html

3.用VLOOKUP回傳參照的內容(影片解惑教學)
https://support.microsoft.com/zh-tw/office/vlookup-%E5%87%BD%E6%95%B8-0bbc8083-26fe-4963-8ab8-93a18ad188a1

首先我在【工作表1】建立資料,先設定取得目標儲存格的設定

ADDRESS(ROW(),COLUMN(),4)

ROW() 目前所在列
COLUMN() 目前所在欄

公式 ADDRESS 函數用法,請參考此網址教學
https://support.microsoft.com/zh-tw/office/address-%E5%87%BD%E6%95%B8-d0c26c0d-3991-446b-8de4-ab46431d4f89

那我是 ADDRESS(ROW(),COLUMN() -1 ,4),也就是取得 目前儲存格的前一欄位置

https://ithelp.ithome.com.tw/upload/images/20241030/20061369XsPvnDed44.png

再來就是【工作表2】,這邊就花了點思考,到底怎樣才符合公式用法,畢竟我又很少用Excel..(0.0|||

想了 HYPERLINK 到底如何傳入動態值的資料,後來找到 VLOOKUP 公式,可以取得回傳資料~

於是湊出這樣的公式

HYPERLINK("#工作表1!" & VLOOKUP("Test資料",工作表1!A1:B2,2,) , "前往瀏覽")

https://ithelp.ithome.com.tw/upload/images/20241030/2006136943NP0ctzOV.png

我是先確定 VLOOKUP("Test資料",工作表1!A1:B2,2,) 抓到的資料為何,

以及測試在【工作表1】新增2列的時候,是否還能抓到資料。

測試後新增2列的時候, 發現他會自己變動儲存格

也就是

VLOOKUP("Test資料",工作表1!A1:B2,2,) 自己變動-> VLOOKUP("Test資料",工作表1!A1:B4,2,)

所以那個 ADDRESS(ROW(),COLUMN() -1 ,4) 公式,也是會自動變動 指定儲存格 的位置

以上就是我的公式寫法,不知道誰有更正確公式寫法?!


更新

感謝 froce 高手指導公式,省略 ADDRESS 函數,直接連到我要的位置~

=HYPERLINK("#工作表1!A"&MATCH("Test資料",工作表1!A:A,FALSE), "前往瀏覽")

最後改成自動依表格名稱的關鍵字搜尋~

這樣欄位無限新增情況下~工作表2的超連結目標也不會跑掉

如下圖:

https://ithelp.ithome.com.tw/upload/images/20241031/20061369U16OnWBP3y.png

https://ithelp.ithome.com.tw/upload/images/20241031/20061369LwjJx8k3c6.png

看更多先前的討論...收起先前的討論...
froce iT邦大師 1 級 ‧ 2024-10-30 13:40:54 檢舉
工作表2!A2: =HYPERLINK("#工作表1!"&工作表1!B2, "前往瀏覽")
工作表1!B2: ="A"&row()

這樣?
froce iT邦大師 1 級 ‧ 2024-10-30 13:42:27 檢舉
另外用公式大概跨活頁簿沒辦法做,因為工作表1變動時,不同活頁簿大概看不到。
我等等公布我研究的作法@@~
froce iT邦大師 1 級 ‧ 2024-10-30 15:54:48 檢舉
跟我的差不多嘛。我只是不知道你的 "Test資料" 是要被查詢的,你原本沒寫啊...
這個不會有什麼叫正確的寫法,能達到效果就是正確的。
恩~可能要公式完整顯示,
@@..才看得出不同..
你說能解決問題的~任何寫法都是ok的!
所以你的寫法比較簡略快!
froce iT邦大師 1 級 ‧ 2024-10-30 16:07:25 檢舉
=HYPERLINK("#工作表1!A"&MATCH("Test資料",工作表1!A:A,FALSE), "前往瀏覽")

如果你是要關鍵字查詢的話,直接這樣就好,這樣的話你工作表1 B欄也不需要。
喔喔~這句滿適合~
那我那句 ADDRESS 可以省略了~
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友回答

立即登入回答