Excel製作信息查詢表(控件版)

Urin的自习室
19粉絲
大家好這章呢
給大家分享信息查詢類型的表格
你可以用在產品查詢
人員查詢或者類似於發票查詢等等
道理呢都是一樣的
譬如這個是一個人員的信息查詢表格
當我們更換姓名的時候
所有的信息呢
都可以發生一個相應的變化
這些部分啊都是比較簡單的
難點呢在於這個圖片
有可能呢
大家會在我們的數據源表格的最後面
比如說在這個位置來放置一張圖片
當圖片被插入進來之後呢
我們就需要來講究到
這個圖片的高度和寬度
可能會要把它給調成這個樣子
但是之前的信息呢
我們並不需要這樣的行高
同時呢插入啊這個圖片過多呢
也會導致你的表格啊內存會比較大
所以我建議大家呢
使用到今天我們所講的方法
看到我們的表格
首先呢
我們可以到這邊來做一個下拉清單
啊它來自於啊
這個sheet二當中的姓名系列當中
並且呢
有可能這邊還會要得到一個更新
所以啊我建議大家呢啊
先把數據源表格變成一張超級表
Ctrl加上t
上方呢會有一些篩選按鈕
如果大家不習慣的話呢
可以在設計當中把篩選按鈕進行取消
在查詢表格當中呢
大家可以根據自己的需求
去做一些設計
合併單元格啊都是可以的
但是大家要注意啊
爲了講究美觀呢
比如說這個身份證號碼
它會有五個字
我們就可以把它給放在兩行
所以大家在輸入的時候呢
應該這樣去做啊身
份證
然後Alt加上回車鍵跳一行
然後再來寫到號碼啊
這樣的話呢就會比較好看啊
大家注意到這一點
接下來呢就可以通過啊
這個姓名來進行內容的查找啊
所以呢我們可以到這邊這樣來做啊
等於VLOOKUP函數來查找小黃
並且呢一定是查找這個單元格
F4絕對引用它
然後在哪個區域當中呢
在我們的sheet二整張表格中間
它就會標記啊
叫做表r的全部
當我們在更新的時候呢
它所查找的範圍呢
也能夠得到一個更新
好我們再回到查詢表格當中
然後呢我們再來match match這個性別
好吧並且這個性別C3單元格呢
我們不需要對它做任何的鎖定啊
這樣會比較方便
然後在sheet 2當中的表頭系列當中
來進行match
然後0精確匹配括弧回來
這是我們的第三個參數
最後呢0精確匹配括弧回來
確定然後呢
我們再把這個單元格做一個複製
粘過來
粘過來啊
都把它給粘過來
好大家可以注意到啊
譬如說籍貫所找的呢
是小黃所match的呢
是籍貫所以他永遠呢
會在這個單元格之前的單元格當中呢
去match這個表頭
然後呢
在我們的數據源表頭當中來進行match
但是大家注意啊
在合併單元格的位置呢
就會出現問題
比如說
我們再把這個單元格做一個複製
粘過來的時候它會變成廣州
並且呢是一個文本
它並不是一個公式
所以啊我們先可以把所有沒有合
並過單元格的單元格
把它給粘完
粘過來粘過來啊
這邊呢都沒有合併單元格好
其他的部分都合併了單元格
所以呢我們可以再一次
任意選擇某一個單元格
Ctrl加上c
然後在粘貼的時候呢
選擇到右鍵
選擇到公式
這邊呢也是公式同理啊公式
到這一步爲止呢
都還算是比較簡單
先可以來做一個測試
譬如說是小明
小明所有的信息啊
大家都可以到這邊來進行查詢
肯定是沒有任何問題的
接下來呢
我們想要把sheet 2當中這個照片
它的超鏈接
以圖片的形式
把它給放置在這個單元格當中
啊就會有一些麻煩了
首先啊
我們可能會用到一丟丟的VBA啊
很簡單所以呢
我們可以先選擇到文件
選擇到另存爲
把它給放置在某一個位置
然後呢可以選擇到吶
啓用紅的工作簿x l s m格式
好吧先把給做這樣的一個保存
然後呢名稱呢
大家可以做一個修改啊
接下來啊
我們在選擇到開發工具
大家如果沒有開發工具呢
可以選擇到文件
選擇到選項
然後在自定義功能區當中呢
找到開發工具
勾選起來
點擊到確定
然後在開發工具下方找到插入
然後在active x空間這個位置啊
大家可以找到啊
叫做圖像active x空間
然後選擇到它
然後呢
把它給放置在這個單元格當中
不妨先來測試一下這個控件
選擇到控件
選擇到屬性
然後大家可以來找到有個叫做
picture的地方
然後呢選擇到後面的三個點
然後呢
我們可以來找到某一張圖片看一下
比如說找到蕭峯的圖片
你看這個圖片呢
似乎啊他的體現方式不對
所以啊
大家可以在這邊來找到一個叫做
那picture size mode
然後呢
可以來選擇到第二個選項Stretch啊
這樣的話呢
也就正常了
但是它同樣會有個黑邊對吧
所以呢
我們可以再一次選擇到叫做啊
border color然後呢
可以到這邊把它給更換成爲白色啊
這樣的話就看不到它了
這個空間呢
現在也還沒有和我們的超鏈接啊
所對應到的姓名來進行鏈接
所以呢我們可以先把這個照片
把它給移在一邊
然後把這個公式來做一個複製
複製這個單元格
粘的時候呢
選擇到公式好
然後可以來做一個切換
比如說切換到小盒
它沒有變化
爲什麼呢
因爲現在我們所找的啊match的呢
它並不是照片
所以啊我們可以把這個F3呢
把它給手動的雙引號中間呢來寫上啊
叫做照片
啊這樣的話呢
就會有一個路徑在這個位置了
好接下來啊
選擇到開發工具下方的visual basic
然後呢選擇到SHEET一這個位置
SHEET一呢
對應到的也就是我們的查詢表格了
然後呢在通用當中切換到worksheet
然後在過程當中切換到change
把這一段可以把它給刪除
保留worksheet change以及啊ansup這一段
意思也就是說
當我們啊
在這個表格當中來進行更換內容的
時候它就會觸發某一個事件
那這個事件過程呢
也就是更換圖片
好接下來呢
我們可以先來看到啊
這個控件
它的名稱啊叫做image
一對它進行復制
然後呢把它給粘在這個位置
然後寫到點picture等於lot picture括弧
好大家再注意啊
這個單元格它的座標呢是G3單元格
所以呢我們在vegetable當中呢
就應該來寫到叫做range
然後雙引號中間呢來寫上G3點value
括弧回來
好這樣的話
當我們在切換的時候呢
並且大家注意啊
取消設計模式
先來測試一下
選擇到小李
選擇到小坤
選擇到小徐
然後選擇到小黃
那這邊所對應到的照片呢
都是啊我們所在單元格的超鏈接
所對應到的照片
這個邏輯呢
其實是特別簡單的
我們使用到VLOOKUP函數來找小黃
並且呢是match到表頭照片
所以呢
我們可以找到小黃在數據源表格當中
他所對應到的照片
這個單元格它的值
它的值呢就是一個超鏈接
就是一個文件路徑對吧
啊所以呢
我們就可以把這個值把它給放過來
這個就是它的文件路徑
然後啊我們是沒有辦法
在這個空間的基礎之上
在屬性當中這邊會有個picture
我們沒有辦法說直接等於到G3單元格
因爲這個位置呢
你是輸入不了任何內容的
所以啊
我們只能夠說去到visual basic當中呢
來寫到這樣一段簡短的代碼
很簡單啊
好當我們在做切換的時候呢
大家一定要記
住啊把這個設計模式把它給取消掉
這邊還殘留了一個問題
當我們切換到小陳的時候呢
他是沒有任何問題
但是呢當我們切換到小蔡的時候
因爲小蔡他並沒有照片的鏈接
所以啊當我們切換到他的時候
就會出現一個報錯
所以啊出現這樣的情況呢
啊有很多處理辦法啊
但是大家如果是會VBA的話呢
應該是比較簡單啊
主要的關鍵語句呢
就是這一段
當然我們可以加一些錯誤啊
譬如說啊on error
resume next
啊
next
啊這樣的話他就不會報錯
但是邏輯上他也會出現一個漏洞啊
那當我們切換到呃
小坤沒有問題
切切換到小徐也沒有問題
但是切換到小蔡的時候呢
他依然是小徐的圖片啊
這樣也是不符合情理的
所以呢我在VBA當中呢
加入了另外兩種方法
這種方法呢
是在VBA當中呢
直接調取VLOOKUP函數啊
這種方法呢
是比較靠譜的啊
所以當我們使用到這個方法的時候呢
再切換到小菜
哪怕小菜啊這個單元格當中沒有值
它會變成一片空白
好吧或者呢
也可以使用到第三種方法啊
大家都可以去試一下
課件呢大家下載之後呢
啊可以把這些代碼都把它給記住了
比較簡單
最後呢
我們可以把這個單元格的值啊
你看是使用哪一種方法
使用第一種方法就需要它
如果是使用到第二種或第三種方法呢
就可以不需要啊這個函數了啊
我們直接呢
寫在VBA當中瞭然
後呢現在可以把這邊的字體
來做一個白色的處理
然後呢
我們再選擇到開發工具設計模式
把這個控件呢
把它給放在啊啊比較居中的位置
好吧然後再取消設計模式
最後再來做一個切換啊
好當我們如果是在啊這個表格當中
有內容的添加也是比較簡單的啊
比如說到這邊吶
小菜哎呦
我們找到他的照片了
所以呢可以選擇到插入
選擇到啊這邊的超鏈接啊
這邊會有個鏈接
或者呢你可以選擇到右鍵啊
這邊也會有個超鏈接啊
然後呢選擇到你的文件路徑
然後呢選擇到某一張圖片
好現在我們可以來試一下
再切換到小菜
沒有任何問題
把這兩個時間的單元格呢
我們也可以
最後把它給轉換成爲日期格式
那這一章呢
咱們就講到這個位置啊
比較簡單啊
希望大家呢能夠有所收穫
