Skip to main content

Conas colún fada a phriontáil ar leathanach amháin in Excel?

Má cheaptar go bhfuil liosta fada sonraí agat i gcolún, b’fhéidir 200 sraitheanna, agus anois ní mór duit iad a phriontáil, ach nuair a phriontálann tú, úsáideann sé thart ar pháipéar 5 leathanach, gan ach colún amháin ag an taobh clé, agus go leor spáis bán air an taobh dheis. I bhfocal, is féidir leat colún a dhéanamh, ach níl an fheidhm seo ag Excel. Conas a phriontálann tú sonraí an liosta fhada ar leathanach amháin chun an páipéar a shábháil?

Priontáil colún fada ar leathanach amháin le foirmle

Priontáil colún fada ar leathanach amháin le cód VBA

Priontáil colún fada ar leathanach amháin le Kutools le haghaidh Excel


mboilgeog cheart gorm saighead Priontáil colún fada ar leathanach amháin le foirmle

Anseo is féidir liom foirmle fhada a thabhairt isteach chun an fhadhb seo a réiteach, déan mar seo le do thoil:

1. I mbileog oibre nua de do leabhar oibre gníomhach, iontráil an fhoirmle =IF(OFFSET(Sheet1!$A$1,(COLUMN()-1)*45+ROW()-1,0)="","",OFFSET(Sheet1!$A$1,(COLUMN()-1)*45+ROW()-1,0)) isteach sa chill A1.

nótaí: Sheet1 an bhileog oibre ar a bhfuil an liosta fada a theastaíonn uait a cholún.

45 an uimhir róin is mian leat na sonraí a liostáil i gcolún.

Is athróga iad uile, agus is féidir leat iad a athrú de réir mar is gá duit.

2. Ansin roghnaigh cill A1, agus tarraing an láimhseáil líonta síos go dtí sraith 45, agus ansin lean ar aghaidh ag tarraingt an láimhseála líonta go deas go dtí go mbeidh na sonraí ar taispeáint. Agus tá an colún fada roinnte ina roinnt colúin le feistiú ar leathanach amháin i mbileog oibre nua. Féach an pictiúr:

doc-print-fad-colún1


mboilgeog cheart gorm saighead Priontáil colún fada ar leathanach amháin le cód VBA

Is féidir leis an gcód VBA seo a leanas cabhrú leat liosta fada sonraí a roinnt i roinnt colúin, ionas gur féidir leat na sonraí a phriontáil agus páipéar a shábháil.

1. Coinnigh síos an ALT + F11 eochracha, agus osclaíonn sé an Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

2. Cliceáil Ionsáigh > Modúil, agus greamaigh an cód seo a leanas i bhFuinneog an Mhodúil.

Sub SingleToMultiColumn()
    Dim rng As Range
    Dim iCols As Integer
    Dim lRows As Long
    Dim iCol As Integer
    Dim lRow As Long
    Dim lRowSource As Long
    Dim x As Long
    Dim wks As Worksheet
    Set rng = Application.InputBox _
      (prompt:="Select the range to convert", _
      Type:=8)
    iCols = InputBox("How many columns do you want?")
    lRowSource = rng.Rows.Count
    lRows = lRowSource / iCols
    If lRows * iCols <> lRowSource Then lRows = lRows + 1
    Set wks = Worksheets.Add
    lRow = 1
    x = 1
    For iCol = 1 To iCols
        Do While x <= lRows And lRow <= lRowSource
            Cells(x, iCol) = rng.Cells(lRow, 1)
            x = x + 1
            lRow = lRow + 1
        Loop
        x = 1
    Next
End Sub

3. Ansin brúigh an F5 eochair chun an cód seo a rith, agus roghnaigh na sonraí liosta a theastaíonn uait a roinnt sa bhosca dialóige aníos, féach an scáileán:

doc-print-fad-colún2

4. Cliceáil OK, agus cuir isteach líon na gcolún is mian leat a roinnt. Féach an pictiúr:

doc-print-fad-colún3

5. agus cliceáil OK, tá an colún fada roinnte ina chúig cholún i mbileog oibre nua. Féach screenshots:

doc-print-fad-colún4 -2 doc-print-fad-colún5

mboilgeog cheart gorm saighead Priontáil colún fada ar leathanach amháin le Kutools le haghaidh Excel

D’fhéadfadh sé go mbeadh an fhoirmle fhada agus an cód VBA deacair duit, anseo is féidir liom modh éasca agus áisiúil a thabhairt isteach chun an fhadhb seo a réiteach. Kutools le haghaidh Excel'S Priontáil Ilcholúin is féidir le feidhm cabhrú leat an colún fada a roinnt go tapa i roinnt colúin ionas gur féidir leat iad a phriontáil go réasúnta.

Kutools le haghaidh Excel Cuimsíonn sé níos mó ná 300 uirlis áisiúil Excel. Saor chun triail a dhéanamh gan aon teorannú i 30 lá. Faigh é anois.

Nuair a bheidh Kutools suiteáilte agat le haghaidh Excel, is féidir leat na céimeanna seo a leanas a dhéanamh:

1. Cliceáil Fiontar > Priontáil Ilcholúin, féach ar an scáileán:

doc-print-fad-colún6

2. Sa an Priontáil Ilcholúin bosca dialóige:

  • Cliceáil an chéad cheann cnaipe doc cnaipe chun teideal an raoin a roghnú, agus ansin cliceáil an dara ceann cnaipe doccnaipe chun an colún a theastaíonn uait a roinnt a roghnú.
  • Ansin sonraigh líon na sraitheanna is mian leat a chur in aghaidh an leathanaigh Sraitheanna in aghaidh an leathanaigh chlóite, agus cuir isteach líon na gcolún atá uait i leathanach amháin faoi Líon deighleoga. Féach an pictiúr:

doc-print-fad-colún7

3. Ansin cliceáil OK, roinneadh na sonraí ar an liosta fada i gcúig cholún ar leathanach. Féach screenshots:

doc-print-fad-colún8 -2 doc-print-fad-colún9

Nótaí:

1. Cuirfear an teideal roimh gach colún.

2. Má dhéanann tú seiceáil Cruthaigh nasc leis an mbileog ghníomhach rogha, is féidir sonraí nua na bileoige oibre a nascadh leis na sonraí foinse.

Más mian leat tuilleadh a fháil amach faoin ngné seo, cliceáil le do thoil Priontáil Ilcholúin.

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup    VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle    Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla    Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc


Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (14)
Rated 4.5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
I have a column of 10,000 unique numbers, all sorted. The VBA code provides a table, but the Columns are sorted individually.
I would prefer that each page is sorted numerically so easy to follow the data page by page. Any suggestions most welcome.
Keith
This comment was minimized by the moderator on the site
Hello, Paterson,
Maybe the following VBA code can do you a favor:
Sub SingleToMultiColumn()
    Dim xRng As Range
    Dim xCount As Integer
    Dim xICols As Integer
    Dim xLRows As Long
    Dim xICol As Integer
    Dim xLRow As Long
    Dim xLRowSource As Long
    Dim xRowNum As Long
    Dim xFCount, xFNum, xFNum_R, xFNum_C As Long
    Dim x As Long
    Dim xWst As Worksheet
    Dim xJ As Integer
    
    Set xRng = Application.InputBox(prompt:="Select the range to convert", Type:=8)
    xICols = InputBox("How many columns do you want?")
    xRowNum = InputBox("How many rows do you want in a page?")
    xCount = xRng.Count
    xFCount = Fix(xCount / (xICols * xRowNum))
    If xICols * xRowNum * xFCount < xCount Then xFCount = xFCount + 1
    Set xWst = Worksheets.Add
    xJ = 0
    Application.ScreenUpdating = False
    For xFNum = 1 To xFCount
        If xJ = xCount Then Exit For
        For xFNum_C = 1 To xICols
            If xJ = xCount Then Exit For
            For xFNum_R = 1 To xRowNum
                If xJ = xCount Then Exit For
                xWst.Cells((xFNum - 1) * xRowNum + xFNum_R, xFNum_C) = xRng.Item(xJ + 1)
                xJ = xJ + 1
            Next
        Next
    Next
     Application.ScreenUpdating = True
End Sub



Please try, hope it can help you!
This comment was minimized by the moderator on the site
I have a column of 10,000 numbers and the VBA macro will provide a table on several pages. However, I would prefer that each page is sorted numerically rather than the column. Any help appreciated.
This comment was minimized by the moderator on the site
Or you can just copy the table, and paste it in a word document, with as many columns you want to have.
This comment was minimized by the moderator on the site
Thank you! The formula worked seamlessly!
This comment was minimized by the moderator on the site
You can use a simple formula =OFFSET(Sheet1!$A$1,(ROW()-2)*5+COLUMN(),0) to let the data flow horizontally in 5 columns. Then you can select Page size, print first row on each page, etc. Simple, neat, no VBA
This comment was minimized by the moderator on the site
where does this formula go? I am trying to print multiple rows with several columns on one page in a multiple row layout. Similar to this:

john smith week 1
john smith week 2
john smith totals


I have over 100 rows with up to IJ
This comment was minimized by the moderator on the site
Is there a way to modify this formula to work with 3 columns instead of 1?
This comment was minimized by the moderator on the site
Modern Excel (e.g. 365) can use this for single column:
=WRAPCOLS(Sheet1!$A$1:$A$50;45;"")

Similarly, if you want the column spread horizontally use:
=WRAPROWS(Sheet1!$A$1:$A$50;3;"")


If you need multiple columns converted, I created this formula:
=LET(SOURCE;Sheet1!$A$1;R;45;C;3;X;MOD(COLUMN()-1;C);NEW_REL_COL;INT((COLUMN()-1)/C);Y;(NEW_REL_COL)*R+ROW()-1;V;OFFSET(SOURCE;Y;X);IF(OR(V="";ROW()>R);"";V))

Update SOURCE (Sheet1!$A$1) to be the top-left corner of your source, the number behind R (45) for the rows; the number behind C (3) for the columns of your source.

e.g. for output of 2 columns with 30 rows start the formula with =LET(SOURCE;Sheet1!$A$1;R;30;C;2;


On modern Excel (e.g. 365) can use this Spill-Function for multicolumn with an added empty column in between:

=LET(R;45;C;3;EMPTY_COL;TRUE;
E;IF(EMPTY_COL;1;0);ANZ;COUNTA(Sheet1!$A:$A);MY;R;MX;CEILING.MATH(ANZ/MY;1)*(C+E);
MAKEARRAY(MY;MX;LAMBDA(ru;co;
LET(X;MOD(co-1;(C+E));NEW_REL_COL;INT((co-1)/(C+E));Y;(NEW_REL_COL)*R+ru-1;
IF(OR(AND(EMPTY_COL;X=(C+E-1));Y>=ANZ);"";OFFSET(Sheet1!$A$1;Y;X))
))))

Needs to be filled only in one cell and fills out down and right.
Use R and C like above. Fill the value EMPTY_COL with true or false if you want an empty column to be added between the repetitions.
Rated 4.5 out of 5
This comment was minimized by the moderator on the site
I would be interested in a solution like this as well.
This comment was minimized by the moderator on the site
Dear Sir Thanks for this work, This VBA Code is very helpful for my work, I am glad to complete my task in seconds. Thank You. ....
This comment was minimized by the moderator on the site
After searching the internet for hours, I stumbled across this. Its exactly what I was looking for. Nice job and thanks for taking the time out for this.
This comment was minimized by the moderator on the site
Thank you for this information! It was very helpful and easy to use - even for somebody lacking strong computer skills (I used the first method)
This comment was minimized by the moderator on the site
Hello, I have a question related to the second option "Print long column on one page with VBA code". If i have more than one column in range how can I use the same code. Let say the range of "$A$2:$C$118" how can i do it work. Thanks.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations