Preskoči na glavno vsebino

Podatke na podlagi izbranega stolpca v Excelu hitro razdelite na več delovnih listov

Kutools za Excel

Izboljša Excel s 300+
Zmogljive lastnosti

Predpostavimo, da imate delovni list, ki vsebuje podatke v stolpcih A do G, je ime prodajalca v stolpcu A in morate te podatke samodejno razdeliti na več delovnih listov na podlagi imena v stolpcu A. Vsak prodajalec bo razdeljen na nov delovni list. Kutools za ExcelJe Datum razdelitve pripomoček lahko hitro razdeli podatke na več delovnih listov na podlagi izbranega stolpca v Excelu.

Razdelite podatke na več delovnih listov na podlagi izbranega stolpca

Razdelite podatke na več delovnih listov glede na število vrstic


klik Kutools Plus > Delovni list > Razdeljeni podatki (ali Kutools Plus> Razdeljeni podatki). Oglejte si posnetke zaslona:

kutools-puščica-desno

Razdelite podatke na več delovnih listov na podlagi izbranega stolpca

Recimo, da imate podatke o obsegu, kot je prikazano na spodnjem posnetku zaslona, ​​in jih želite hitro razdeliti na več delovnih listov, ki temeljijo na stolpcu A v delovnem zvezku, tako da vsako ime razdelite na nov delovni list. Na primer vsi Kelly bo razdeljen na isti delovni list. To lahko storite na naslednji način:

1. Na podlagi stolpca izberite obseg, ki ga želite razdeliti na več delovnih listov.

2. In nato s pomočjo klika uporabite pripomoček Kutools Plus > Delovni list > Razdeljeni podatki. v Razdeli datum na več delovnih listov pogovorno okno, izberite Poseben stolpec in nato s spustnega seznama določite stolpec, na podlagi katerega želite razdeliti podatke.

Nasvet: Če vaš izbran obseg vsebuje glavo, preverite Moji podatki imajo glave možnost, lahko določite število vrstic glave na podlagi vaših podatkov. Na primer, če vaši podatki vsebujejo dve glavi, vnesite 2.

3. Prosimo, navedite nova imena delovnih listov. Lahko uporabite Vrednosti stolpca kot imena novih delovnih listov. Vnesete lahko tudi a Prefiks or pripona za imena delovnih listov.

4. klik OK če želite razdeliti podatke, zdaj lahko vidite, da bo vsako ime v stolpcu razdeljeno na nov delovni list v novem delovnem zvezku. Glej posnetek zaslona:


Razdelite podatke na več delovnih listov glede na število vrstic

Predpostavimo, da imate podatke o obsegu, kot je prikazano na spodnjem posnetku zaslona, ​​in želite te podatke hitro razdeliti na več delovnih listov na podlagi števila vrstic, na primer, razdelite vsakih 5 vrstic podatkov v nov delovni list. Spustite ga lahko na naslednji način:

1. Na podlagi vsakih 5 vrstic izberite obseg, ki ga želite razdeliti na več delovnih listov.

2. In nato s pomočjo klika uporabite pripomoček Kutools Plus > Delovni list > Razdeljeni podatki. v Razdeli datum na več delovnih listov pogovorno okno, izberite Fiksne vrstice in nato v polju določite številko vrstic.

Nasvet: Če vaš izbran obseg vsebuje glavo, preverite Moji podatki imajo glave možnost, lahko določite število vrstic glave na podlagi vaših podatkov. Na primer, če vaši podatki vsebujejo dve glavi, vnesite 2.

3. Navedite novo ime delovnih listov. Lahko uporabite Številke vrstic kot je ime novih delovnih listov. Vtipkate lahko tudi Prefiks or pripona za ime delovnih listov.

4. klik OK za razdelitev podatkov bo vsakih 5 vrstic tega obsega razdeljenih na nov delovni list v novem delovnem zvezku. Glej posnetek zaslona:


Predstavitev: Hitro razdelite podatke na več delovnih listov na podlagi izbranega stolpca v Excelu

Kutools za Excel: z več kot 300 priročnimi dodatki za Excel, brezplačno preizkusite brez omejitev v 30 dneh. Prenesite in brezplačno preskusite zdaj!

Priporočena orodja za produktivnost
Naslednja orodja vam lahko v veliki meri prihranijo čas in denar, katero je najbolj primerno za vas?
Zavihek Office: Uporaba priročnih zavihkov v pisarni, kot način Chrome, Firefox in New Internet Explorer.
Kutools za Excel: Več kot 300 naprednih funkcij za Excel 2021, 2019, 2016, 2013, 2010, 2007 in Office 365.

Kutools za Excel

Zgoraj opisana funkcionalnost je le ena izmed 300 zmogljivih funkcij Kutools za Excel.

Zasnovan za Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 in Office 365. Brezplačen prenos in uporaba 30 dni.

Posnetek zaslona Kutools za Excel

btn preberi več      btn prenos     btn nakup

Comments (20)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Is there a way to easily split these worksheets into separate workbooks instead? I have to create 213 workbooks of split data based on a column. It splits it out into sheets which is good too but I then have to manually save each one as a workbook. Just wondering if there is a work around that would speed up the process?
This comment was minimized by the moderator on the site
Hello, Ronel,

Sorry, currently, our feature doesn't support this operation. However, I've conveyed your request to our development team. Should there be any updates, we'll notify you promptly.

Thanks for your feedback.
This comment was minimized by the moderator on the site
It works well, How can I fetch all data from different sheeets to one sheet?

any one to help
This comment was minimized by the moderator on the site
Hello, PETER
To merge data from multiple sheets into one single sheet, the following article may help you.
https://www.extendoffice.com/documents/excel/1184-excel-merge-multiple-worksheets-into-one.html
or you can use our Kutools for Excel 's Combine multiple worksheets from workbooks into one worksheet to solve this task, please check this article:
https://www.extendoffice.com/product/kutools-for-excel/excel-combine-worksheets-into-one.html
Please check it, if you have any other problem, please comment here again.
This comment was minimized by the moderator on the site
This split function is really helpful but I need total also in subsequent split sheets. What should I do? Please help
This comment was minimized by the moderator on the site
Hi there,
Brilliant function to split data to new worksheets.
Sometimes inconsistent naming of the sheets when selecting "Values of Column" as new worksheet name. Reverts to "Sheet 1" etc.
What is the rule that determines this?Are there any workarounds?
Many thanksNaomi
This comment was minimized by the moderator on the site
Currently, we can split based on text string in a certain column, based on specific rows in a certain column. I think it is fantastic to add the ability to split based on the time group in a certain column. For example, I have a date column, values are 01/01/2020, 01/03/2020....03/05/2025.., I want to split the whole table by year into different sheets.
This comment was minimized by the moderator on the site
I can split data, but it doesn't keep the formatting. It's almost not worth it to have it split the data without the formatting.
This comment was minimized by the moderator on the site
Is there something similar for Google Sheets?
This comment was minimized by the moderator on the site
There seems to be an issue with the Kutools "Copy Page Setup" print function when used in Office 365 as it isn't able to copy the "Fit All Columns on One Page" setting to the other worksheets. Instead it uses the "Fit Sheet on One Page". Tried finding the printer default settings for the worksheets created by Kutools, but wasn't successful - it doesn't seem to use the settings from the default template? How can this be achieved?
This comment was minimized by the moderator on the site
Has there been a resolution to this? I am unable to split data into multiple worksheets based on the content of a column.
This comment was minimized by the moderator on the site
This function is not working, any suggestions
This comment was minimized by the moderator on the site
I'm having a similar issue with this feature not working. seems to copy of of sync. believe its to do with there being only single entries for some of the data. cant work out why its not using the selected headers and replacing them with the first entry using the macro they have seems to work: http://www.extendoffice.com/documents/excel/1174-excel-split-data-into-multiple-worksheets-based-on-column.html
This comment was minimized by the moderator on the site
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