Ide: Lòt lang yo se Google-tradui. Ou ka vizite a English vèsyon nan lyen sa a.
Ouvri yon sesyon
x
or
x
x
enskri
x

or

Ki jan yo konbine manyèl miltip nan yon sèl workbook mèt nan Excel?

Èske w te janm te kole lè ou gen konbine manyèl miltip nan yon workbook mèt nan Excel? Bagay ki pi terib se ke liv sa yo ou bezwen konbine genyen plizyè fich. Ak ki jan yo konbine sèlman fich yo espesifye nan manyèl miltip nan yon liv travay pratik? Tutorial sa a montre plizyè metòd itil yo ede ou rezoud etap sa yo pwoblèm pa etap.


Fasil konbine manyèl miltip nan yon sèl nan Excel:

Jounal Konbine manyèl sèvis piblik nan Kutools pou Excel ka ede w fasil konbine miltip dosye Excel nan yon sèl. Anplis, li pèmèt ou sèlman konbine fich yo espesifye nan sèten dosye Excel nan yon workbook mèt fasil. Download karakteristik la plen 60-jou gratis santye kounye a!

Konbine manyèl miltip nan yon liv travay pratik ak deplase oswa Kopi fonksyon

Si gen jis yon koup liv gid bezwen yo dwe konbine, ou ka itilize deplase oswa kopi lòd la manyèlman deplase oswa kopye fey ki soti nan workbook orijinal la nan liv travay la mèt.

NAN. Louvri liv sa yo ki ou pral rantre nan yon liv travayè mèt.

NAN. Chwazi fich yo nan liv travay orijinal la ke ou ap deplase oswa kopye nan liv travay la mèt.

nòt:

1). Ou ka chwazi miltip fo ki pa adjasan avèk kenbe la Ctrl kle epi klike onglè yo fèy youn pa youn.

2). Pou chwazi plizyè fichye adjasan, tanpri klike sou premye tab la, kenbe la Ka glise kle, ak Lè sa a, klike sou dènye tab la fèy papye yo chwazi yo tout.

3). Ou ka klike sou dwa sou nenpòt ki tab fèy, klike sou Chwazi tout dra nan meni kontèks la yo chwazi tout fich nan workbook a an menm tan.

NAN. Apre ou chwazi feyèt ki nesesè yo, klike sou tab tab la, epi klike sou li a Deplase oswa Kopi soti nan meni an kontèks. Al gade nan Ekran:

4. Lè sa a, la Deplase oswa Kopi dyalòg Pops leve, nan la Nan liv gout-desann, chwazi liv travay la mèt ou ap deplase oswa kopye fo nan. Chwazi deplase nan fen nan la Anvan fèy bwat, tcheke a Kreye yon kopi bwat, epi finalman klike sou la OK bouton.

Lè sa a, ou ka wè fich nan de liv yo konbine nan yon sèl. Tanpri repete etap sa yo pi wo a pou avanse pou pi worksheets soti nan liv manyèl nan livrètr la mèt.

Tab Office - yon reyèl tan-ekonomizeur ajoute-an!

Montre tout dokiman yo louvri nan yon fenèt sèl ede rapid jwenn yon dokiman ki nesesè Yon ti koutje sou.

Tout karakteristik jijman gratis 45-jou. Konpatib ak tout vèsyon an Excel, Pawòl ak PowerPoint.

KONNEN PLIS SOU ADD-IN>

Konbine manyèl miltip oswa fèy papye espesifye nan liv gid pou yon workbook mèt ak VBA

Si gen manyèl miltip bezwen fusion nan yon sèl, ou ka aplike kòd sa yo VBA sa yo byen vit reyalize li. Tanpri fè jan sa a.

NAN. Mete tout manyèl ke ou vle konbine nan yon sèl anba menm anyè a.

NAN. Lanse yon dosye Excel (liv travay pratik sa a pral livrèr mèt la).

3. Peze la Alt + F11 kle yo louvri a Microsoft Visual Basic pou aplikasyon yo fenèt. Nan Microsoft Visual Basic pou aplikasyon yo fenèt, klike Antre > Modil, Lè sa a, kopi anba a VBA kòd nan fenèt la modil.

VBA kòd 1: Rantre plizyè manyèl Excel nan yon sèl

Sub GetSheets()
'Updated by Extendoffice 2019/2/20
Path = "C:\Users\dt\Desktop\dt kte\"
Filename = Dir(Path & "*.xls")
  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
     Workbooks(Filename).Close
     Filename = Dir()
  Loop
End Sub
	

nòt:

NAN. Kòd VBA ki anwo a ap kenbe non fèy papye orijinal yo apre fizyon.

NAN. Si ou vle yo fè distenksyon ant ki worksheets nan liv travayè a mèt soti nan kote apre melanje, tanpri aplike anba a kòd VBA 2.

NAN. Si ou jis vle konbine espesifik fichye nan manyèl yo nan yon liv travayè mèt, anba a kòd VBA 3 ka ede.

Nan kòd VBA, "C: Itilizatè yo DT168”Se chemen katab la. Nan kòd la VBA 3, "Sheet1, Sheet3"se fich yo espesifye nan liv yo ou pral konbine nan yon livrètr mèt. Ou ka chanje yo ki baze sou bezwen ou yo.

VBA kòd 2: Rantre nan Liv Atik yon sèl (pral chak Fèy travay yo te rele ak prefiks nan non dosye orijinal li):

Sub MergeWorkbooks()
'Updated by Extendoffice 2019/2/20
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
On Error Resume Next
xStrPath = "C:\Users\DT168\Desktop\KTE\"
xStrFName = Dir(xStrPath & "*.xlsx")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
Do While Len(xStrFName) > 0
    Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
    xStrAWBName = ActiveWorkbook.Name
    For Each xWS In ActiveWorkbook.Sheets
    xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
    Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
    xMWS.Name = xStrAWBName & "(" & xMWS.Name & ")"
    Next xWS
    Workbooks(xStrAWBName).Close
    xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

VBA kòd 3: Rantre nan fich espesifik nan liv travay nan yon workbook mèt.

Sub MergeSheets2()
'Updated by Extendoffice 2019/2/20
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
Dim xI As Integer
On Error Resume Next

xStrPath = " C:\Users\DT168\Desktop\KTE\"
xStrName = "Sheet1,Sheet3"

xArr = Split(xStrName, ",")

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
xStrFName = Dir(xStrPath & "*.xlsx")
Do While Len(xStrFName) > 0
Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
For xI = 0 To UBound(xArr)
If xWS.Name = xArr(xI) Then
xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.count)
Set xMWS = xTWB.Sheets(xTWB.Sheets.count)
xMWS.Name = xStrAWBName & "(" & xArr(xI) & ")"
Exit For
End If
Next xI
Next xWS
Workbooks(xStrAWBName).Close
xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

4. Peze la F5 kle kouri kòd la. Lè sa a, tout worksheets oswa espesifye worksheets nan liv yo nan folder nan sèten yo konbine nan yon workbook mèt nan yon fwa.

Fasil konbine manyèl miltip oswa fèy papye espesifye nan liv gid pou yon liv travay pratik

Kutools pou Excel

Li se yon Excel sou la men ajoute-an ki ranmase plis pase 300 karakteristik avanse senplifye travay ou nan operasyon Excel. Avèk li, ou ka fasilman:

  • Konbine Livrezon miltip;
  • Konte / sòm selil pa koulè;
  • Split cellules;
  • Pakèt retire espas;
  • Plis karakteristik ...

GRATIS DOWNLOAD KOUNYE A!

Erezman, nan Konbine livrezon travayè nan Kutools pou Excel fè li pi fasil pou amalibre liv miltip nan yon sèl. Ann wè ki jan yo ka resevwa travay sa a fonksyon nan konbine manyèl miltip.

NAN. Kreye yon nouvo liv travayè epi klike sou Kutools Plis > Konbine. Lè sa a, yon dyalòg Pops fè ou sonje ke tout workbooks konbine ta dwe sove ak karakteristik nan pa ka aplike nan liv travay pwoteje, tanpri klike sou OK bouton.

2. Nan Konbine Worksheets sòsye, chwazi Konbine fichye miltip soti nan liv sou yon liv opsyon, ak Lè sa a klike sou la Next bouton. Al gade nan Ekran:

3. Nan Konbine Worksheets - Step 2 nan 3 bwat dyalòg, klike sou la Add > File or Dosye ajoute dosye yo Excel ou pral plonje nan yon sèl. Apre ajoute dosye yo Excel, klike sou la Fini bouton epi chwazi yon katab pou konsève liv travayè mèt la. Al gade nan D:

Koulye a, tout manyèl yo fizyone nan yon sèl.

Konpare ak de pi wo a metòd yo, Kutools pou Excel gen avantaj sa yo:

  • 1) Tout manyèl ak workheets yo ki nan lis nan ti bwat la dyalòg;
  • 2) Pou fich yo ou vle eskli nan fusion, jis dezaktive li;
  • 3) fich vid yo eskli otomatikman;
  • 4) ap pote non orijinal la kòm prefiks sou non fèy la apre fizyon;
  • Pou plis fonksyon nan karakteristik sa a, tanpri vizite isit la.

Sa a Konbine liv travay èd sèvis piblik? Se pou nou download epi eseye karakteristik la plen gratis jijman 60-jou!

Rekòmande zouti pwodiktivite pou Microsoft Office

Tab Office - Tabbed Navigasyon, Koreksyon, ak jere nan Excel / Pawòl / PowerPoint:

Biwo Tab pote koòdone nan onglè jan yo wè nan navigatè entènèt tankou Google Chrome, Internet Explorer nouvo vèsyon ak Firefox nan Microsoft Excel / Pawòl / PowerPoint. Li pral yon zouti ekonomize tan ak iranplaceble nan travay ou. Gade anba a Demo:

ot1

Kutools pou Excel - Konbine Plis Pase 300 Fonksyon avanse ak Zouti pou Microsoft Excel:

Kutools pou Excel se yon sou la men Excel ajoute-an ak plis pase XNX karakteristik avanse senplifye divès kalite travay konplike nan yon kèk klik nan Excel. Pa egzanp:

  • Konbine fich travay miltip oswa liv nan yon liv
  • Konte ak selil sòm ki baze sou background / font koulè
  • Sèl selil Split nan ranje miltip / kolòn pa espas / comma / delimiter
  • Batch Retire espas ki mennen nan selil yo
kte

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Dezignext Technologies · 1 months ago
    I like using this technique better than using traditional "3D Formula" techniques in Excel.
  • To post as a guest, your comment is unpublished.
    Jonel · 2 months ago
    Note: This VBA code can merge the entire workbooks into the master workbook, if you want to combine specified worksheets of the workbooks, this code will not work.

    Can we have the module for VBA that above scene will work,
    • To post as a guest, your comment is unpublished.
      crystal · 1 months ago
      Hi Jonel,
      The following code can help you solve the problem. You need to replace folder path and "Sheet1, Sheet3" with the specified folder path and worksheets as you need.

      Sub MergeSheets2()
      Dim xStrPath As String
      Dim xStrFName As String
      Dim xWS As Worksheet
      Dim xMWS As Worksheet
      Dim xTWB As Workbook
      Dim xStrAWBName As String
      Dim xI As Integer
      On Error Resume Next

      xStrPath = " C:\Users\DT168\Desktop\KTE\"
      xStrName = "Sheet1,Sheet3"

      xArr = Split(xStrName, ",")

      Application.ScreenUpdating = False
      Application.DisplayAlerts = False
      Set xTWB = ThisWorkbook
      xStrFName = Dir(xStrPath & "*.xlsx")
      Do While Len(xStrFName) > 0
      Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
      xStrAWBName = ActiveWorkbook.Name
      For Each xWS In ActiveWorkbook.Sheets
      For xI = 0 To UBound(xArr)
      If xWS.Name = xArr(xI) Then
      xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.count)
      Set xMWS = xTWB.Sheets(xTWB.Sheets.count)
      xMWS.Name = xStrAWBName & "(" & xArr(xI) & ")"
      Exit For
      End If
      Next xI
      Next xWS
      Workbooks(xStrAWBName).Close
      xStrFName = Dir()
      Loop
      Application.ScreenUpdating = True
      Application.DisplayAlerts = True

      End Sub
  • To post as a guest, your comment is unpublished.
    Chris · 3 months ago
    When I run this, each sheet in the new workbook is being named based off of the sheet names of the original document rather than the filenames. Any idea what I might be doing wrong?
    • To post as a guest, your comment is unpublished.
      crystal · 1 months ago
      Hi Chris,
      If you want to distinguish which worksheets in the master workbook came from where after merging, please apply the below VBA code to solve the problem.

      Sub MergeWorkbooks()
      Dim xStrPath As String
      Dim xStrFName As String
      Dim xWS As Worksheet
      Dim xMWS As Worksheet
      Dim xTWB As Workbook
      Dim xStrAWBName As String
      On Error Resume Next
      xStrPath = "C:\Users\DT168\Desktop\KTE\"
      xStrFName = Dir(xStrPath & "*.xlsx")
      Application.ScreenUpdating = False
      Application.DisplayAlerts = False
      Set xTWB = ThisWorkbook
      Do While Len(xStrFName) > 0
      Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
      xStrAWBName = ActiveWorkbook.Name
      For Each xWS In ActiveWorkbook.Sheets
      xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
      Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
      xMWS.Name = xStrAWBName & "(" & xMWS.Name & ")"
      Next xWS
      Workbooks(xStrAWBName).Close
      xStrFName = Dir()
      Loop
      Application.ScreenUpdating = True
      Application.DisplayAlerts = True
      End Sub
  • To post as a guest, your comment is unpublished.
    Owen · 6 months ago
    It didnt work for me then I realized my files are .xlsx, so added the missing "x" to the Filename line.
  • To post as a guest, your comment is unpublished.
    Justin · 9 months ago
    This worked for me but I had to make sure I have to put "\" at the end of my path. Initially, I didn't have it and it wouldn't work.