Макрос для извлечения данных из диаграммы в Excel

Сводка

В Microsoft Excel данные можно извлекать из диаграммы, даже если они есть во внешнем листе или книге. Это полезно в ситуациях, когда диаграмма была создана из другого файла или связана с ней, который недоступен или каким-то образом поврежден. При потере исходных данных диаграммы данные по-прежнему можно извлечь из самой диаграммы с помощью макроса Microsoft Visual Basic для приложений.

Дополнительная информация

Корпорация Майкрософт предоставляет примеры программирования только в целях демонстрации без явной или подразумеваемой гарантии. Данное положение включает, но не ограничивается этим, подразумеваемые гарантии товарной пригодности или соответствия отдельной задаче. Эта статья предполагает, что пользователь знаком с представленным языком программирования и средствами, используемыми для создания и отладки процедур. Специалисты технической поддержки Майкрософт могут пояснить работу той или иной процедуры, но модификация примеров и их адаптация к задачам разработчика не предусмотрена.

Следующий пример макроса помещает исходные данные диаграммы на лист с именем ChartData в активной книге, начиная с первого столбца и первой строки.

1. Введите следующий код макроса на листе модуля:

   Sub GetChartValues()
      Dim NumberOfRows As Integer
      Dim X As Object
      Counter = 2
   
      ' Calculate the number of rows of data.
      NumberOfRows = UBound(ActiveChart.SeriesCollection(1).Values)
   
      Worksheets("ChartData").Cells(1, 1) = "X Values"
   
      ' Write x-axis values to worksheet.
      With Worksheets("ChartData")
         .Range(.Cells(2, 1), _
         .Cells(NumberOfRows + 1, 1)) = _
         Application.Transpose(ActiveChart.SeriesCollection(1).XValues)
      End With
   
      ' Loop through all series in the chart and write their values to
      ' the worksheet.
      For Each X In ActiveChart.SeriesCollection
         Worksheets("ChartData").Cells(1, Counter) = X.Name
   
         With Worksheets("ChartData")
            .Range(.Cells(2, Counter), _
            .Cells(NumberOfRows + 1, Counter)) = _
            Application.Transpose(X.Values)
         End With
   
         Counter = Counter + 1
      Next
   
   End Sub
   

2. Вставьте новый лист в книгу и переименуйте его в "ChartData" (без кавычек).

3. Выберите диаграмму, из которой нужно извлечь базовые значения данных.

   Примечание.

   Диаграмма может быть внедрена на лист или на отдельный лист диаграммы.

4. Запустите макрос GetChartValues.

   Данные из диаграммы помещаются на лист "ChartData".

Действия по связыванию диаграммы с восстановленными данными

Чтобы диаграмма была интерактивной с восстановленными данными, необходимо связать диаграмму с новым листом данных, а не сохранять ссылки на отсутствующие или поврежденные книги.

1. Выберите диаграмму и щелкните ряд, чтобы найти имя листа, с которым диаграмма связана в поврежденной или отсутствуют книге. Имя листа отображается в формуле ряда в строке формул.

   Примечание.

   Имя листа может следовать за именем книги, заключенной в квадратные скобки, например "[Книга1]", и предшествует восклицательный знак "!" (или апостроф и восклицательный знак "'!") , указывающий начало ссылки на ячейку. Имя листа включает только символы между символом закрытой квадратной скобки "]" и восклицательным знаком (или апострофом и восклицательным знаком). Оставьте любой апостроф, если он находится непосредственно перед восклицательным знаком, так как апостроф не может быть последним символом в имени листа.

2. Дважды щелкните вкладку нового листа с именем ChartData.

3. Введите исходное имя листа из шага 1 над выделенным элементом ChartData и нажмите клавишу ВВОД. Это имя должно совпадать с именем листа из поврежденной или отсутствуют книги.

4. Если вы не сохранили этот файл с диаграммой и листом данных, сохраните файл.

5. В Excel 2003 или Excel 2002 в меню Правка щелкните Ссылки, а затем выберите команду Изменить источник.

   В Excel 2007 перейдите на вкладку Данные , щелкните Изменить ссылки в группе Connenctions и щелкните Изменить источник.

6. В поле Исходный файл выберите ссылку для изменения и нажмите кнопку Изменить источник.

7. В диалоговом окне Изменение ссылок выберите новый файл с восстановленными данными и диаграммой, а затем нажмите кнопку ОК.

8. Если появляется следующее сообщение об ошибке

   Формула содержит недопустимую внешнюю ссылку на лист.

   вполне вероятно, что имя листа, введенное на шаге 3, не совпадает с исходным. Назад шаг 1.

9. Поле Исходный файл теперь может быть пустым. Это означает, что все ссылки указывают на активный файл, а не на отсутствующий или поврежденный файл. Нажмите кнопку Закрыть.

Диаграмма теперь ссылается на восстановленные данные на переименованном листе в активной книге и взаимодействует с ними.