Lm th? no ? s? d?ng macro ? thm nh?n cho d? li?u i?m trong m?t xy scatter bi?u ? ho?c trong b?ng x?p h?ng bong bng trong Excel

D?ch tiu ? D?ch tiu ?
ID c?a bi: 213750 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?


Trong Microsoft Excel, khng c khng c l?nh ?c xy d?ng trong t? ?ng g?n nh?n vn b?n vo d? li?u i?m trong m?t xy (scatter) ho?c bong bng b?ng x?p h?ng. Tuy nhin, b?n c th? t?o m?t Microsoft Visual Basic cho cc ?ng d?ng v? m m th?c hi?n i?u ny. Bi vi?t ny ch?a m?t v? m m?u th?c hi?n nhi?m v? ny v? m?t bi?u ? XY Scatter. Tuy nhin, cng m? c th? ?c s? d?ng cho m?t bong bng B?ng x?p h?ng.


Microsoft cung c?p l?p tr?nh v d? ? minh ho? ch?, khng c b?o hnh ho?c th? hi?n hay ng? ?. i?u ny bao g?m, nhng khng gi?i h?n, b?o ?m ng? ? kh? nng bn hng ho?c cho m?t m?c ch c? th?. Bi vi?t ny gi? ?nh r?ng b?n ? quen thu?c v?i ngn ng? l?p tr?nh m ang ?c ch?ng minh v v?i nh?ng cng c? ?c s? d?ng ? t?o ra v g? l?i th? t?c. Microsoft h? tr? cc k? s c th? gip gi?i thch cc ch?c nng c?a m?t th? t?c c? th?. Tuy nhin, h? s? s?a ?i cc v d? ? cung c?p thm ch?c nng ho?c xy d?ng quy tr?nh ? p ?ng cc yu c?u c? th? c?a b?n. M?u m? trong bi vi?t ny gi? ?nh r?ng cc d? li?u v g?n nh?n ?c s?p x?p trong m?t b?ng tnh theo cc ?nh d?ng sau:
  • C?t ?u tin ch?a cc nh?n d? li?u.
  • C?t th? hai ch?a cc gi tr? x cho xy b?ng x?p h?ng (scatter).
  • Cc c?t th? ba v sau ch?a cc gi tr? y cho b?ng x?p h?ng xy (scatter).
LU ?: M?c d cc v d? ?c cung c?p c ch? c m?t c?t c?a d? li?u cho cc gi tr? y, b?n c th? s? d?ng nhi?u hn m?t d?ng d? li?u.

S? d?ng cc macro trong bi vi?t ny, t?o ra m?t bi?u ? b?ng cch s? d?ng d? li?u sau:
   A1: Labels       B1: X Values   C1: Y Values
   A2: DataPoint1   B2: 12          C2: 5
   A3: DataPoint2   B3:  9          C3: 7
   A4: DataPoint3   B4:  5          C4: 3
   A5: DataPoint4   B5:  4          C5: 8
   A6: DataPoint5   B6:  1          C6: 4
LU ?: B?ng nn khng ch?a cc c?t r?ng v c?t ch?a d? li?u nh?n khng nn ?c tch ra t? c?t c ch?a cc gi tr? x. Cc nh?n v cc gi tr? ph?i ?c ?t ra trong chnh xc cc ?nh d?ng ?c m t? trong bi vi?t ny. (Cc t? bo bn tri no khng ph?i t? bo A1.)

? nh km nh?n vn b?n vo d? li?u i?m trong m?t xy (scatter) bi?u ?, lm theo cc b?c sau:
  1. Trn b?ng c ch?a d? li?u m?u, ch?n cc di ?ng ph?m vi B1:C6.
  2. Trong Microsoft Office Excel 2003 v trong phin b?n tr?c c?a Excel, lm theo cc b?c sau:
    1. Nh?p vo B?ng x?p h?ng trn cc Chn tr?nh n.
    2. Trong cc B?ng x?p h?ng Wizard - b?c 1 c?a 4 - b?ng x?p h?ng Lo?i h?p tho?i h?p, b?m vo cc Cc lo?i tiu chu?n tab d?i Lo?i bi?u ?, b?m XY (Scatter), v sau nh?p vo Ti?p theo.
    3. Trong cc B?ng x?p h?ng Wizard - b?c 2 c?a 4 - b?ng x?p h?ng D? li?u ngu?n h?p tho?i h?p, b?m vo cc D? li?u t?m tab d?i Series t?i, b?m C?t, v sau nh?p vo Ti?p theo.
    4. Trong cc B?ng x?p h?ng Wizard - b?c 3 c?a 4 - b?ng x?p h?ng Tuy chon h?p tho?i h?p, b?m vo Ti?p theo.
    5. Trong cc B?ng x?p h?ng Wizard - b?c 4 c?a 4 - bi?u ? V? tr h?p tho?i h?p, b?m vo cc Nh m?i t? ty ch?n, v sau nh?p vo K?t thc.
    Trong Microsoft Office Excel 2007, h?y lm theo cc b?c sau:
    1. B?m vo cc Chn tab, b?m vo Tan trong cc Biu nhm, v sau ch?n m?t lo?i.
    2. Trn cc Thi?t k? tab, b?m vo Di chuy?n B?ng x?p h?ng trong cc V? tr nhm, h?y nh?p vo M?i t? , v sau nh?p vo Ok.
  3. Nh?n ALT + F11 ? b?t ?u Visual Basic Editor.
  4. Trn cc Chn tr?nh n, nh?p vo M-un.
  5. G? m? m?u sau trong m-un t?:
    Sub AttachLabelsToPoints()
       'Dimension variables.
       Dim Counter As Integer, ChartName As String, xVals As String
       ' Disable screen updating while the subroutine is run.
       Application.ScreenUpdating = False
       'Store the formula for the first series in "xVals".
       xVals = ActiveChart.SeriesCollection(1).Formula
       'Extract the range for the data from xVals.
       xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
          Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
       xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
       Do While Left(xVals, 1) = ","
          xVals = Mid(xVals, 2)
       'Attach a label to each data point in the chart.
       For Counter = 1 To Range(xVals).Cells.Count
         ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
          ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
             Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
       Next Counter
    End Sub
  6. Nh?n ALT + Q ? tr? v? Excel.
  7. Chuy?n ?n t?m b?ng x?p h?ng.
  8. Trong Excel 2003 v trong phin b?n tr?c c?a Excel, i?m ?n V? m ngy cc Cng cu tr?nh n, v sau b?m Macro. Nh?p vo AttachLabelsToPoints, v sau nh?p vo Ch?y ? ch?y v? m.

    Trong Excel 2007, b?m cc Nha phat trin tab, b?m voV? m trong cc Ma nhm, ch?nAttachLabelsToPoints, v sau nh?p vo Ch?y.
V? m g?n nh?n trong cc t? bo A2:A6 v?i cc i?m d? li?u trn cc b?ng x?p h?ng.

Thu?c tnh

ID c?a bi: 213750 - L?n xem xt sau cng: 20 Thang Chin 2011 - Xem xt l?i: 3.0
p d?ng
  • Microsoft Office Excel 2007
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel X for Mac
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 for Macintosh
  • Microsoft Office Excel 2003
  • Microsoft Excel 2010
T? kha:
kbchart kbdtacode kbhowto kbinfo kbprogramming kbmt KB213750 KbMtvi
My d?ch
QUAN TRONG: Bi vi?t ny ?c d?ch b?ng ph?n m?m d?ch my c?a Microsoft ch? khng ph?i do con ng?i d?ch. Microsoft cung c?p cc bi vi?t do con ng?i d?ch v c? cc bi vi?t do my d?ch ? b?n c th? truy c?p vo t?t c? cc bi vi?t trong C s? Ki?n th?c c?a chng ti b?ng ngn ng? c?a b?n. Tuy nhin, bi vi?t do my d?ch khng ph?i lc no c?ng hon h?o. Lo?i bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, gi?ng nh m?t ng?i n?c ngoi c th? m?c sai st khi ni ngn ng? c?a b?n. Microsoft khng ch?u trch nhi?m v? b?t k? s? thi?u chnh xc, sai st ho?c thi?t h?i no do vi?c d?ch sai n?i dung ho?c do ho?t ?ng s? d?ng c?a khch hng gy ra. Microsoft c?ng th?ng xuyn c?p nh?t ph?n m?m d?ch my ny.
Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny:213750

Cung cp Phan hi


Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com