Bonbon ede nou delivre sèvis nou yo. Lè l sèvi avèk sèvis nou yo, ou dakò pou nou sèvi ak bonbon.
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

Kouman konbine miltip miltip nan yon sèl liv travay nan Excel?

Eske ou janm te kole lè ou gen konbine miltip miltip nan yon liv travay nan yon sèl nan Excel? Bagay ki pi terib se ke liv travay la ou bezwen konbine gen fich miltip. Èske nenpòt moun ka sigjere yon bon fason yo okipe pwoblèm sa a?

Konbine manyè miltip nan yon sèl liv travay avèk VBA

Konbine miltip miltip nan yon sèl travayè ki gen kòmandman oswa deplase kopi

Fasil konbine miltip miltip nan yon sèl workbook ak Kutools pou Excel


Fasil konbine fichye miltip / workbooks nan yon fich travay / liv:

Konbine fichye miltip oswa liv nan yon sèl Fèy travay oswa liv travayè ka yon gwo travay nan travay chak jou ou. Men, si ou genyen Kutools pou Excel, sèvis piblik pwisan li yo - Konbine ka ede ou byen vit konbine fichye miltip, bookbooks nan yon sèl fich oswa liv. Rale dosye karakteristik plen kilomèt gratis nan Kutools pou Excel kounye a!

Kutools pou Excel: ak plis pase 200 sou la men Excel ajoute-ins, gratis eseye ki pa gen okenn limit nan jou 60. Download ak gratis jijman Koulye a!


Konbine manyè miltip nan yon sèl liv travay avèk VBA

Pou pwogramasyon kalifye ak pwofesyonèl yo, ou ka itilize Scripts VBA pou konbine liv travay miltip nan yon sèl travayè metrize. Ou ka fè fas ak sa a ak etap sa yo:

1. Mete tout workbooks yo ke ou vle konbine nan anyè a menm. Al gade nan Ekran:

2. Lanse yon dosye Excel ke ou vle konbine lòt livr nan.

3. Klike sou Developer > Vizyèl Debaz, yon nouvo Microsoft Visual Basic pou aplikasyon yo fenèt yo ap parèt, klike sou Antre > Modil, ak opinyon kòd sa a nan modil la:

Sub GetSheets()
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

Ide: Nan kòd ki pi wo a, ou ka chanje chemen an nan yon sèl nan ke ou ap itilize.

4. Lè sa a, klike sou doc-fizyone-miltip-workbooks-bouton bouton nan kouri kòd la, ak tout fich yo (ki gen ladan fichye yo vid) nan liv yo te fusionné nan bookbook a mèt.

nòt: Kòd VBA sa a ka rantre nan manyèl la nan liv travayè mèt la, si ou vle konbine fichye espesifik nan workbooks yo, kòd sa a pa pral travay.


Konbine miltip miltip nan yon sèl travayè ki gen kòmandman oswa deplase kopi

Si ou se yon rookie nan Microsoft Excel, ou pa gen okenn chwa, men se sèlman gen kopi done yo nan chak fèy ak keratin yo nan nan yon nouvo liv travay pa youn ak aplike move la oswa kopi lòd. Sèvi ak la Deplase oswa Kopi lòd pral ede w ekspòte oswa kopye youn oswa plizyè fich nan yon nouvo liv travay byen vit.

1. Louvri tout liv ki ou vle rantre nan yon liv.

2. Chwazi tout non Fèy travay nan yon liv sou tab tab. Ou ka chwazi miltip ak kenbe desann Ctrl kle oswa Ka glise kle. Dwa klike sou non Fèy travay la, epi klike sou la Deplase oswa Kopi soti nan meni kontèks.

3. Nan Deplase oswa Kopi bwat dyalòg, chwazi liv travay mèt la ke ou vle rantre lòt bookbook nan soti nan gout desann lis la Deplase fèy papye yo chwazi. Lè sa a, presize kote adrès fusion yo. Al gade nan Ekran:

4. Lè sa a, klike sou OK. Fichye yo chwazi yo te demenaje ale rete nan mendèv la mèt.

5. Repete etap sa yo soti nan 2 pou 4 pou avanse pou pi lòt bookbook nan bookbook mèt la. Lè sa a, li konbine tout fich travay louvri nan yon liv travay. Al gade nan Ekran:


Fasil konbine miltip miltip nan yon sèl workbook ak Kutools pou Excel

Erezman, pou rookies ak nouvo men nan Microsoft Excel, gen kèk zouti ki disponib ede ou byen vit konbine dè santèn de workbooks nan yon sèl. Fonksyon an Konbine in Kutools pou Excel fè li pi fasil konbine miltip dosye Excel. Se aplikasyon an itilize pou divilge operasyon yo repetitif ak redondants nan Excel. Li aktyèlman jwe yon wòl enpòtan si ou gen fè fas ak yon gwo kantite lajan nan dosye Excel nan travay chak jou ou. Koulye a, kite a wè ki jan yo ka resevwa travay sa a fonksyon nan konbine manyè miltip.

Kutools pou Excel : ak plis pase 300 sou la men Excel ajoute-ins, gratis eseye pa gen okenn limit nan jou 60.

1. Pèmèt Excel 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, ak Lè sa a klike sou la Next bouton. Al gade nan Ekran:

2. Lè sa a, ou ka wè tout bookbook louvri ak fich yo ki nan lis nan la Konbine Worksheets - Step 2 nan 3 bwat dyalòg. Klike sou la Add > File / Dosye ajoute lòt dosye Excel oswa dosye whih ki gen dosye yo Excel ou pral konbine. Apre chwazi dosye, klike sou la Next bouton pou ale pi devan. Al gade nan Ekran:

doc amalgé miltip miltip arrow1

3. Nan Konbine Worksheets - Step 3 nan 3 bwat dyalòg, tanpri fè anviwònman sa yo:

  • 3.1 Ou ka eskli tout fichye vid soti nan fusion pa chwazi Sote ki soti nan Lè rankontre yon fich travay vid lage desann lis.
  • 3.2 Tcheke a Premye ranje nan chak Fèy travay (Kòmantè) opsyon pou mete enfòmasyon Fèy travay nan chak Fèy travay konbine.
  • 3.3 Ou ka chanje non nouvo konbine yo pa mete non yo ouvri anvan oswa apre non orijinal la fichye, oswa jis kenbe non fichye orijinal yo ak dezaktive Pa mete non liv travay opsyon.
  • Klike sou la Fini bouton.

4. Lè sa a, chwazi yon katab pou konsève pou workbook a konbine, epi ouvri li dirèk jan ou bezwen. Koulye a, tout livrezon spesifye yo konbine nan yon sèl.

nòt:

1. Yo pral mande w si ou vle sove senaryo sa a. Klike sou Wi pou konsève jan ou bezwen, Lè sa a, antre nan non senaryo a, (gade sa yo Ekran). Apre ekonomize senaryo a, ou sèlman bezwen klike sou Senaryo bouton yo chwazi non an nan senaryo a sou step2 sòsye yo sèvi ak li dirèkteman.

2. Tablo nan liv yo chwazi yo te fizyone nan yon nouvo liv travay, ak fichye yo yo te rele ak non an workbook anvan oswa apre non fichye orijinal la si ou te tcheke Pa mete non liv travay opsyon. Pandan se tan, yon nouvo fichye yo te rele Kutools pou Excel se pwodwi anvan tout fichye, ki bay lis workbooks yo ak enfòmasyon detaye nan chak fich. Al gade nan Ekran:

3. Si non fich ou a twò lontan, petèt non karaktè yo pi gran pase 31characters, non an konbine pa pral parèt, epi sèlman montre non orijinal fèy la.

Ide.Si ou vle gen yon jijman gratis nan sèvis piblik sa a, tanpri ale nan download lojisyèl an lib premye, ak Lè sa a, ale nan aplike operasyon an dapre pi wo etap yo.


Biwo Tab - Tabbed Browsing, Korije, ak jere nan Workbooks nan Excel:

Tab Biwo pote koòdone nan tabbed jan yo wè nan navigatè entènèt tankou Google Chrome, Internet Explorer nouvo vèsyon ak Firefox nan Microsoft Excel. Li pral yon tan-ekonomize zouti ak irézistibl nan travay ou. Gade anba a Demo:

Klike pou jijman gratis nan Biwo Tab!

Tab Biwo pou Excel

Demo: Fasil konbine miltip miltip nan yon sèl workbook ak Kutools pou Excel




Awtik ki Asosye:



Rekòmande Zouti Productivity

Biwo Tab

lò star1 Pote onglè sou lamen pou Excel ak lòt biwo lojisyèl, jis tankou Chrome, Firefox ak nouvo Internet Explorer.

Kutools pou Excel

lò star1 Etonan! Ogmante pwodiktivite ou nan minit 5. Pa bezwen okenn ladrès espesyal, sove de zè de tan chak jou!

lò star1 300 karakteristik nouvo pou Excel, fè Excel anpil fasil ak pwisan:

  • Rantre selilè / ranje / Kolòn san yo pa pèdi done.
  • Konbine ak Konsolide Pliye Plizyè ak Manyèl.
  • Konpare Range, Kopi plizyè chif, Konvèti tèks nan dat, inite ak konvèsyon lajan.
  • Konte pa Koulè, Paging subtotals, Avanse Triye ak Super Filter,
  • Plis Chwazi / Insert / Efase / Tèks / Fòma / Link / Kòmantè / Workbooks / Worksheets Zouti ...

Piki ekran nan Kutools pou Excel

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 · 5 days 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 · 1 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 · 4 days 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 · 2 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 · 4 days 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 · 5 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 · 8 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.