Skip i'r prif gynnwys

Sut i dynnu data o siart neu graff yn Excel?

doc-extract-chart-data-1
Yn Excel, rydym fel arfer yn defnyddio siart i ddangos data a thueddiad i'w weld yn gliriach, ond weithiau, efallai mai copi yw'r siart ac nad oes gennych ddata gwreiddiol y siart fel y dangosir isod. Yn yr achos hwn, efallai yr hoffech chi dynnu'r data o'r siart hon. Nawr mae'r tiwtorial hwn yn siarad am echdynnu data o siart neu graff.
Tynnu data o'r siart gyda VBA

swigen dde glas saeth Tynnu data o'r siart gyda VBA

1. Mae angen i chi greu taflen waith newydd a'i hail-enwi fel Data Siart. Gweler y screenshot:

doc-extract-chart-data-5

2. Yna dewiswch y siart rydych chi am dynnu data ohoni a'i phwyso Alt + F11 allweddi ar yr un pryd, ac a Microsoft Visual Basic ar gyfer Ceisiadau pops ffenestri.

3. Cliciwch Mewnosod > Modiwlau, yna pastiwch islaw cod VBA i'r popping Modiwlau ffenestr.

VBA: Tynnu data o'r siart.

Sub GetChartValues()
    'Updateby20220510
    Dim xNum As Integer
    Dim xSeries As Object
    On Error Resume Next
    xCount = 2
    xNum = UBound(Application.ActiveChart.SeriesCollection(1).Values)
    Application.Worksheets("ChartData").Cells(1, 1) = "X Values"
    With Application.Worksheets("ChartData")
        .Range(.Cells(2, 1), _
        .Cells(xNum + 1, 1)) = _
        Application.Transpose(ActiveChart.SeriesCollection(1).XValues)
    End With
    For Each xSeries In Application.ActiveChart.SeriesCollection
        Application.Worksheets("ChartData").Cells(1, xCount) = xSeries.Name
        With Application.Worksheets("ChartData")
            .Range(.Cells(2, xCount), _
            .Cells(xNum + 1, xCount)) = _
            Application.WorksheetFunction.Transpose(xSeries.Values)
        End With
        xCount = xCount + 1
    Next
End Sub

4. Yna cliciwch Run botwm i redeg y VBA. Gweler y screenshot:

doc-extract-chart-data-2

Yna gallwch weld bod y data yn cael ei dynnu i Data Siart taflen.
doc-extract-chart-data-3

Tip:

1. Gallwch fformatio'r celloedd yn ôl yr angen.

doc-extract-chart-data-4

2. Mae data'r siart a ddewiswyd yn cael ei dynnu i gell gyntaf y ddalen ChartData yn ddiofyn.


Tip: Os ydych chi fel arfer yn defnyddio siartiau cymhleth yn Excel, a fydd yn drafferthus wrth i chi eu creu yn amser iawn, yma gyda'r Testun Auto offeryn o Kutools ar gyfer Excel, does ond angen i chi greu'r siartiau ar y tro cyntaf, yna ychwanegu'r siartiau yn y cwarel AutoText, yna, gallwch eu hailddefnyddio mewn unrhyw le ar unrhyw adeg, yr hyn sydd angen i chi ei wneud yn unig yw newid y cyfeiriadau i gyd-fynd â'ch gwir angen.  Cliciwch i'w lawrlwytho am ddim nawr.
siart bar wedi'i bentyrru fformatio amodol doc 12

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 (18)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Amazing...
This comment was minimized by the moderator on the site
Very helpful! Thanks a lot!
This comment was minimized by the moderator on the site
giving error and tell to set block variables and object variables
This comment was minimized by the moderator on the site
Hi, I have updated the VBA, you can try it again.
This comment was minimized by the moderator on the site
i tried above & it says "Compile error: Method or Data Member not found"
This comment was minimized by the moderator on the site
mong mọi người giúp đỡ làm cách nào để lấy số liệu từ biểu đồ này với ạ!
This comment was minimized by the moderator on the site
i m getting below error while running that command.
this error comes in that command which starts from xnum = UBound(......) etc

Run-Time error '91'
object variable or with block variable not set
This comment was minimized by the moderator on the site
Hi, tarshul, you need to create a new worksheet named ChartData before you running the code, the data will be place in the new worksheet ChartData.
This comment was minimized by the moderator on the site
Hi sunny,
I already create new worksheet as u told but still getting same error.
This comment was minimized by the moderator on the site
This doesn't appear to work for a scatter plot as it only extracts one set of "x" data. How can I amend it to extract all "x" data sets?
This comment was minimized by the moderator on the site
Sorry I did not found the solution about that.
This comment was minimized by the moderator on the site
i've tried with a scatter plot graph as well, but only get one line of valor.


i need so much to find a way to extract data from scatterplot graphs.
This comment was minimized by the moderator on the site
I failed to get the prices of a fund chart on my mac excel 2011 . Run time error '91' object variable or block variable not set . Don't know how to debug . Appreciate any help .
This comment was minimized by the moderator on the site
Very useful and perfect
This comment was minimized by the moderator on the site
gives me values that i created chart with not all the values in range
This comment was minimized by the moderator on the site
Amazing command, thanks a lot! I used it with a pivot chart and it works!
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