跳到主要內容

如何調整圖片大小以適合Excel中的單元格？

通常，插入的圖片會漂浮在單元格上，大多數情況下，一張圖片會覆蓋很多單元格。 如果工作表中包含大量圖片，則可能需要將每張圖片放入單個單元格中。 但是如何處理呢？ 實際上，有幾種技巧可以幫助我們快速調整圖片大小以適合單元格的大小。

使用VB宏調整所選圖片的大小以適合單個單元格

使用Kutools for Excel調整插入圖片的大小以適合單元格

箭頭藍色右氣泡 使用VB宏調整所選圖片的大小以適合單個單元格

我們可以應用VB宏來調整所選圖片的大小以適合單個單元格，但不能手動調整圖片的寬度和高度。 您可以按照以下步驟進行操作：

步驟1：將圖片插入到工作表中，然後選擇要調整大小以適合單個單元格的圖片。

doc調整圖片大小以適合單元格1

步驟2：按住 ALT + F11 鍵，然後打開 Microsoft Visual Basic for Applications 窗口。

步驟3：點擊 插入 > 模塊，然後將以下宏粘貼到“模塊窗口”中。

VBA：調整所選圖片的大小以適合單元格。

Public Sub FitPic()
On Error GoTo NOT_SHAPE
Dim PicWtoHRatio As Single
Dim CellWtoHRatio As Single
With Selection
PicWtoHRatio = .Width / .Height
End With
With Selection.TopLeftCell
CellWtoHRatio = .Width / .RowHeight
End With
Select Case PicWtoHRatio / CellWtoHRatio
Case Is > 1
With Selection
.Width = .TopLeftCell.Width
.Height = .Width / PicWtoHRatio
End With
Case Else
With Selection
.Height = .TopLeftCell.RowHeight
.Width = .Height * PicWtoHRatio
End With
End Select
With Selection
.Top = .TopLeftCell.Top
.Left = .TopLeftCell.Left
End With
Exit Sub
NOT_SHAPE:
MsgBox "Select a picture before running this macro."
End Sub

步驟4：按下 F5 運行此宏的鍵。

步驟5：重複上述步驟以調整其他圖片的大小以適合單個單元格。

現在，所有插入的圖片都將調整大小並適合單元格。 請參見以下屏幕截圖：

doc調整圖片大小以適合單元格2

備註：一次只能調整一張照片的尺寸。 如果一次選擇多張圖片，將彈出一個警告對話框。

箭頭藍色右氣泡 使用Kutools for Excel調整插入圖片的大小以適合單元格

如果你有 Excel的Kutools 安裝後，可以應用它 導入圖片 該工具可在插入圖片期間調整所有圖片的大小以適合單元格或固定高度。

Excel的Kutools 包括300多個便捷的Excel工具。 30天免費試用，不受限制。 立即獲取.

步驟1：選擇要插入圖片的範圍單元格，然後單擊 企業 >進出口 > 導入圖片。 請參見以下屏幕截圖：

doc調整圖片大小以適合單元格1

步驟2：在 導入圖片 對話框中，在下拉列表中選擇插入順序，然後單擊 加入 按鈕從文件或文件夾添加所需的圖片，然後單擊 尺寸 按鈕。 看截圖：

doc調整圖片大小以適合單元格2

步驟3：檢查 匹配的細胞大小 選項。 然後點擊 Ok > 進口 關閉對話框。 看截圖：

doc調整圖片大小以適合單元格3

現在您可以看到已插入圖片以適合單元格。

doc調整圖片大小以適合單元格4

備註：您還可以在步驟3中指定單元格的高度和寬度，以調整圖片大小。

如果您想進一步了解 導入圖片 功能，請訪問 這裡.

相關文章:

如何在Excel中快速調整多個單元格的大小以適合其上方的圖片？

最佳辦公生產力工具

🤖 Kutools 人工智慧助手：基於以下內容徹底改變數據分析： 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能，體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述

Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (15)
No ratings yet. Be the first to rate!
Daniel
Daniel
This comment was minimized by the moderator on the site
La macro posiziona l'immagine "Immagine 2" al centro della cella ("D5"), anche se questa è unita ad altre celle. Spero possa esservi utile

Sub ImpFoto()
Dim hcell As Single ' altezza cella
Dim Lcell As Single ' larghezza cella
Dim hfoto As Single ' altezza foto
Dim Lfoto As Single ' larghezza foto
Dim Rfoto As Single ' rapporto foto h/L

Range("D5").Select

hcell = Selection.Height
Lcell = Selection.Width
hfoto = ActiveSheet.Shapes("Immagine 2").Height
Lfoto = ActiveSheet.Shapes("Immagine 2").Width
Rfoto = hfoto / Lfoto

If hfoto < Lfoto Then
Lfoto = Lcell - 10
hfoto = Lfoto * Rfoto

If hfoto > hcell Then
hfoto = hcell - 3
Lfoto = hfoto / Rfoto
End If

ActiveSheet.Shapes("Immagine 2").Height = hfoto
ActiveSheet.Shapes("Immagine 2").Width = Lfoto
Else
hfoto = hcell - 10
Lfoto = hfoto / Rfoto
ActiveSheet.Shapes("Immagine 2").Height = hfoto
ActiveSheet.Shapes("Immagine 2").Width = Lfoto
End If

Range("D5").Select

With ActiveSheet.Shapes("Immagine 2")
.Top = Selection.Top + (Selection.Height - .Height) / 2
.Left = Selection.Left + (Selection.Width - .Width) / 2
End With
End Sub
Reply
0
0
Kaka
Kaka
This comment was minimized by the moderator on the site
Thank you, it really works!
Reply
0
0
Ranjith
Ranjith
This comment was minimized by the moderator on the site
Hi, Need this code disabling the lock aspect ratio
Reply
0
0
syedsunnygillani@gmail.com
This comment was minimized by the moderator on the site
Hi..
perfact solution. But i want that function to work with command button. i need to make a command button to insert and auto resize to cell size. please help.
Reply
Report
0
0
Shripad Lale
Shripad Lale
This comment was minimized by the moderator on the site
Brilliant! Works great! Just what I was looking for! Thank you!
Reply
0
0
claus
claus
This comment was minimized by the moderator on the site
it works if the cells are not merged. unfortunately, I merged some cells and it fits only in the upper left cell which makes the photo very tiny.
Reply
0
0
Harry
Harry
This comment was minimized by the moderator on the site
Hi, I really love this macro that makes images fit cells, it really helped me with some work stuff. But does anyone know how to write this code for applescript? I have to use macs and the apple version of excel, Numbers, only uses applescript. Help! Many thanks, Harry
Reply
0
0
Carol
Carol
This comment was minimized by the moderator on the site
Thanks it did work but I have about 500 pictures so I don't want to do them one at a time - how do I do them all at the same time please?
Reply
0
0
Marcelo
Marcelo
This comment was minimized by the moderator on the site
Works!! But i have one question, i want to fit a img in a range of cells merged, how can i do it?
Reply
0
0
Nick
Nick     Marcelo
This comment was minimized by the moderator on the site
Public Sub FitPic()
'https://www.extendoffice.com/documents/excel/1060-excel-resize-picture-to-fit-cell.html
'Slight modification to resize into merged cells
On Error GoTo NOT_SHAPE
Dim PicWtoHRatio As Single
Dim CellWtoHRatio As Single
With Selection
PicWtoHRatio = .Width / .Height
End With
With Selection.TopLeftCell
CellWtoHRatio = .MergeArea.Width / .MergeArea.Height
End With
Select Case PicWtoHRatio / CellWtoHRatio
Case Is > 1
With Selection
.Width = .TopLeftCell.MergeArea.Width
.Height = .Width / PicWtoHRatio
End With
Case Else
With Selection
.Height = .TopLeftCell.MergeArea.Height
.Width = .Height * PicWtoHRatio
End With
End Select
With Selection
.Top = .TopLeftCell.MergeArea.Top
.Left = .TopLeftCell.MergeArea.Left
.Placement = xlMoveAndSize
End With
Exit Sub
NOT_SHAPE:
MsgBox "Select a picture before running this macro."
End Sub
Reply
0
0
Christina
Christina     Marcelo
This comment was minimized by the moderator on the site
[quote]Works!! But i have one question, i want to fit a img in a range of cells merged, how can i do it?By Marcelo[/quote] Marcelo did you get the updated marco for the merged cell? I am trying to do the same thing. thanks
Reply
0
0
mentex78
mentex78     Christina
This comment was minimized by the moderator on the site
I tried this macro last night and it adjust only the height. The width it doesn't incorrectly. Any Help please?
Reply
Report
0
0
Monika
Monika
This comment was minimized by the moderator on the site
Thank you this was perfect!!
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations