Skip i'r prif gynnwys

Sut i newid maint lluniau i ffitio celloedd yn Excel?

Mae lluniau a fewnosodir fel arfer yn arnofio dros gelloedd, ac mae'r rhan fwyaf o amser un llun yn gorchuddio llawer o gelloedd. Os oes gan daflen waith nifer fawr o luniau, efallai yr hoffech chi roi pob llun mewn un gell. Ond sut i ddelio ag ef? A dweud y gwir, mae yna sawl ffordd anodd i'n helpu i newid maint lluniau i ffitio maint celloedd yn gyflym.

Newid maint llun a ddewiswyd i ffitio un gell gyda VB Macro

Newid maint lluniau wedi'u mewnosod i ffitio celloedd gyda Kutools ar gyfer Excel


swigen dde glas saeth Newid maint llun a ddewiswyd i ffitio un gell gyda VB Macro

Gallwn gymhwyso macros VB i newid maint llun a ddewiswyd i ffitio un gell, ond nid newid maint lled ac uchder llun â llaw. Gallwch ei wneud gyda'r camau canlynol:

Cam 1: Mewnosodwch y lluniau mewn taflen waith, a dewis llun y byddwch chi'n ei newid maint i ffitio un gell.

doc-newid maint-lluniau-i-ffitio-celloedd1

Cam 2: Dalwch i lawr y ALT + F11 allweddi, ac mae'n agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.

Cam 3: Cliciwch Mewnosod > Modiwlau, a gludwch y macro canlynol yn y Ffenestr Modiwl.

VBA: Newid maint llun a ddewiswyd i ffitio cell.

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

Cam 4: Pwyswch y F5 allwedd i redeg y macro hwn.

Cam 5: Ailadroddwch y camau uchod i newid maint lluniau eraill i ffitio un gell.

Nawr mae'r holl luniau sydd wedi'u mewnosod yn cael eu newid maint ac yn ffitio i mewn i gelloedd. Gweler y screenshot canlynol:

doc-newid maint-lluniau-i-ffitio-celloedd2

Nodyn: Gallwch newid maint un llun yn unig un tro. Os dewiswch fwy nag un llun un tro, bydd blwch deialog rhybuddio yn ymddangos.


swigen dde glas saeth Newid maint lluniau wedi'u mewnosod i ffitio celloedd gyda Kutools ar gyfer Excel

Os oes gennych Kutools ar gyfer Excel wedi'i osod, gallwch gymhwyso ei Mewnforio Lluniau offeryn i newid maint yr holl luniau i ffitio celloedd neu ag uchder sefydlog wrth fewnosod lluniau.

Kutools ar gyfer Excel yn cynnwys mwy na 300 o offer Excel defnyddiol. Am ddim i geisio heb unrhyw gyfyngiad mewn 30 diwrnod. Get it Now.

Cam 1: Dewiswch y celloedd amrediad rydych chi am fewnosod lluniau a chlicio ar y Menter >Mewnforio / Allforio > Mewnforio Lluniau. Gweler y screenshot canlynol:

doc-newid maint-llun-i-ffitio-cell-1

Cam 2: Yn y Mewnforio Lluniau blwch deialog, dewiswch y drefn fewnosod yn y gwymplen, a chlicio Ychwanegu botwm i ychwanegu lluniau sydd eu hangen arnoch o ffeil neu ffolder, yna cliciwch Maint botwm. Gweler y screenshot:

doc-newid maint-llun-i-ffitio-cell-2

Cam 3: Gwiriwch Paru maint celloedd opsiwn. Yna cliciwch Ok > mewnforio i gau deialogau. Gweler y screenshot:

doc-newid maint-llun-i-ffitio-cell-3

Nawr gallwch weld bod y lluniau'n cael eu mewnosod i ffitio'r celloedd.

doc-newid maint-llun-i-ffitio-cell-4

Nodyn: Gallwch hefyd nodi uchder a lled y gell yng ngham 3 i newid maint y lluniau.

Os ydych chi eisiau gwybod mwy am hyn Mewnforio Llun nodwedd, ewch i yma.


Erthygl Cysylltiedig:

Sut i newid maint celloedd lluosog i ffitio lluniau uwch eu pennau yn gyflym yn Excel?

Offer Cynhyrchiant Swyddfa Gorau

Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog    VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr    Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd    Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

tab kte 201905


Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
Comments (15)
No ratings yet. Be the first to rate!
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
This comment was minimized by the moderator on the site
Thank you, it really works!
This comment was minimized by the moderator on the site
Hi, Need this code disabling the lock aspect ratio
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.
This comment was minimized by the moderator on the site
Brilliant! Works great! Just what I was looking for! Thank you!
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.
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
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?
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?
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
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
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?
This comment was minimized by the moderator on the site
Thank you this was perfect!!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations