『壹』 excel表格如何使用宏在當前單元格內插入圖片
代碼如下先給你，我先聲明下，我也是之前需要這個操作在網上找的，可以實現你的功能，很好用。
具體用法：
打開excel的VB，把代碼復制進去。
在A列輸入需要插入照片的名稱，然後選定這些有名稱的單元格（重點！要選定）。
然後運行宏，在彈出的對話框選【否】，意思就是在名稱的右邊插入圖片，上面的漢字應該也能看懂。
然後會出來一個對話框讓你選擇路徑，選擇你放照片的那個文件夾，點擊文件夾里的任意一張照片點【打開】。
然後照片就插進去了，而且是每張照片都填滿單元格。
建議提前把單元格的大小設置好然後再運行宏。
如下看我的示例，功能真雞兒強大，再次膜拜下VB大神。
Sub A()
Dim Rng As Range
Set Rng = Selection
K = MsgBox("Yes=按姓名行下插入，No=按姓名列右挿入,Cancel=直接覆蓋插入", vbYesNoCancel)
If K = vbYes Then
r = 1: c = 0
ElseIf K = vbNo Then
r = 0: c = 1
Else
r = 0: c = 0
End If
Pf = "ai,"
Pf = Pf & "bmp,bmz"
Pf = Pf & "cdr,cgm,"
Pf = Pf & "dib,dwg,dxf,"
Pf = Pf & "emf,emz,eps,exf,exif,"
Pf = Pf & "fpx,"
Pf = Pf & "gfa,gif,"
Pf = Pf & "hdr,"
Pf = Pf & "ico,"
Pf = Pf & "jfif,jpe,jpeg,jpg,"
Pf = Pf & "pcd,pct,pcx,pcz,pict,png,psd,"
Pf = Pf & "raw,rle,"
Pf = Pf & "svg,"
Pf = Pf & "tga,tif,tiff,"
Pf = Pf & "ufo,"
Picformat = Pf & "wdp,wmf,wmz,"
OpenFile = Application.GetOpenFilename("Picture Files(*.*),*.*", , "打開目的檔案夾後選擇任一圖片即可指定資料夾。或按取消則會將當前檔所在資料夾認作指定資料夾。")
If OpenFile = False Then
myDir = ThisWorkbook.Path & ""
Else
myDir = Left(OpenFile, InStrRev(OpenFile, ""))
End If
Application.ScreenUpdating = False
Filename = Dir(myDir)
Do While Filename <> ""
If InStr(Picformat, LCase(Right(Filename, Len(Filename) - InStrRev(Filename, ".")))) > 0 Then
PicName = Left(Filename, InStrRev(Filename, ".") - 1)
Rng.Select
On Error Resume Next
Selection.Find(What:=PicName, After:=ActiveCell, LookAt:=xlWhole).Activate
If Err.Number <> 0 Then
Err.Clear
Else
ActiveSheet.Pictures.Insert(myDir & Filename).Select
With Selection
.Placement = xlMoveAndSize
.ShapeRange.LockAspectRatio = msoFalse
.Top = ActiveCell.Offset(r, c).Top
.Left = ActiveCell.Offset(r, c).Left
.Height = ActiveCell.Offset(r, c).Height
.Width = ActiveCell.Offset(r, c).Width
End With
End If
End If
Filename = Dir
Loop
Rng.Select
End Sub
『貳』 如何把圖片鑲嵌在EXCEL表格內固定 讓自動篩選時圖片和名稱能對應的上
你插入圖片後把圖片格式設置「大小和位置隨單元格而變」。這樣篩選時就能對應得上了。
不過要注意，這樣設置以後你改變單元格大小的時候圖片也會改變。
另，如果要一次性選中所有圖片，可用「定位」功能，F5或者CTRL+G，然後選中「對象」就可以。