Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как извлечь данные из диаграммы или графика в Excel?

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

Прежде чем начать, нам необходимо определить данные. В качестве примера мы использовали статистику инфекций Covid-19 по странам по состоянию на 14 июня 2020 года и преобразовали ее в данный формат:

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

Отступ — это вспомогательная колонка, поэтому, поняв это, вы поймете, для чего она нужна. Давайте начнем:

  1. Выберите весь диапазон данных. В моем случае это ячейки A1:C10.
  2. Нажмите Рекомендуемые диаграммы.
    Рекомендованные диаграммы
  3. Выберите комбинированную диаграмму.
    Вставка комбинированной диаграммы
  4. У ряда данных Отступ выберем тип диаграммы Точечная и снимем опцию Вспомогательная ось.
    Тип второстепенного ряда
  5. Нажимаем ОК. Диаграмма появляется на листе.
    Комбинированная диаграмма
  6. Немного сместим подпись данных, чтобы они не накладывались на наш вспомогательный ряд данных. Для этого кликнете правой кнопкой мыши по оси X и выберите Формат оси из контекстного меню.
    Формат оси
  7. Измените расстояние от оси. Установите свое значение так, чтобы данные не накладывались друг на друга. В моем случае это значение 500.
    Расстояние от оси
  8. Наша диаграмма преобразуется в следующий вид. Думаю теперь стало понятно зачем нам нужны эти точки и вспомогательные данные Отступ. В эти точки мы будем вставлять изображения флагов.
    Диаграмма с отступами оси
  9. Теперь выделяйте точки по одной и вставляйте в них изображения.
    • Вариант 1. Это можно сделать сочетанием клавиш Ctrl + C (копировать) Ctrl + V (вставить). Изображения должны быть уменьшены под ваш масштаб. В моем случае я использовал изображения размером 24 пикселя.
    • Вариант 2. Кликнете правой кнопкой мыши по маркеру. Выберите "Формат точки данных". Перейдите к параметрам маркера. Необходимо выбрать в качестве типа маркера изображение.
      Изображение в качестве маркера
      Откроется окно выбора изображения.
  10. Повторите действие для каждого маркера.

Как строить графики в Excel?

Кардиограмма на звездном небеДля удобного и наглядного отображения числовых данных в программах MS Excel и Ooo Calc есть замечательный инструмент — «Мастер диаграмм». Работать с инструментом просто и, как говорится, интуитивно понятно. Пройдя несколько раз процедуру создания диаграмм.

. вы поймете и запомните навсегда, как строить графики в Excel. Поняв, как и что нужно делать в Excel, вы легко затем по аналогии сможете при желании сделать график или диаграмму в программе Calc — процедуры разнятся незначительно.

Типов диаграмм очень много — гистограмма, линейчатая, круговая, пузырьковая, сетчатая, биржевая… В MS Excel 2003 – 34 типа и у каждого типа еще по нескольку вариантов. Для решения различных задач визуализации отображения данных в одних случаях лучше может подходить один тип диаграммы, а в других — иной тип. Необходимо делать выбор…

Графики и диаграммы используются для передачи сути и тенденций данных и их вариаций относительно какого-либо параметра ясным и наглядным способом широкой аудитории с различным опытом и восприятием информации.

Так получилось, что за 13+ лет работы в Excel я использовал и использую только один тип диаграммы 95% времени — точечную диаграмму (в OooCalc — XY Chart). Я строю графики в Excel! Почему?

Читайте так же:
Как изменить значение ячейки, нажав на ячейку?

Во-первых, точечная диаграмма легко воспринимается всеми, так как представляет собой график плоской функции одной переменной, с которыми все мы, обучаясь в средней школе, сталкивались. Поэтому графики являются понятным «языком общения» для юриста, инженера, банкира, экономиста, дизайнера — то есть и для технаря и для гуманитария.

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

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

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

Следующий шаг — составление графика с помощью Excel 2003.

Создание графиков в Excel.

Возьмем для примера таблицу «Производство стали в ноябре 2013 года», которая показывает ожидаемое и фактическое производство данного завода, выпускающего конструкционную сталь.

Таблица MS Excel "Выпуск металлоконструкций в ноябре 2013 года"

1. Создайте новый лист в Excel и добавьте в него эту таблицу.

2. Просто щелкните значок «Мастер диаграмм» на панели инструментов Excel «Стандартная» и выберите «пустую» (без записей) ячейку.

3. Во всплывающем окне на вкладке «Стандарт» выберите тип графика «Точечный», в котором значения соединены отрезками, а точки отмечены маркерами.

Окно №1 Мастера диаграмм MS Excel

4. Нажмите на кнопку «Далее>» и в следующем появившемся окне выберите вкладку «Строка» и нажмите на кнопку «Добавить».

5 Введите имя первой строки: «План выпуска».

6. В окне «Значения X:» щелкните мышью и выберите диапазон ячеек A5. A34 в нашей таблице. Адреса значений оси X появились в активном окне.

7. Выделите диапазон ячеек B5 в окне «Значения Y:» и замените значение на нужное. B34 в таблице. В окне выше вы можете увидеть значения оси Y.

8. Добавьте еще один ряд, нажав кнопку «Добавить».

Окно №2 Мастера диаграмм MS Excel

9. Назовите второй ряд «Проблема-факт».

Наведите курсор мыши на поле «X-значения:» и снова выберите диапазон ячеек A5…A34 в таблице. В окне появляются адреса значений оси X для второго ряда значений.

11. Выберите мышью диапазон ячеек C5 в окне «Значения Y». C34 в таблице. Над окном находятся адреса значений оси Y для второй строки данных.

После нажатия кнопки «Далее>» во вкладке «Заголовки» следующего появившегося окна заполните поля, как показано на снимке экрана ниже.

Окно №3 Мастера диаграмм MS Excel

13. На вкладке «Линии сетки» добавьте галочку к метке «Основные линии» на оси X.

Окно №31 Мастера диаграмм MS Excel

14. На вкладке «Легенда» размещаем легенду внизу диаграммы.

Окно №32 Мастера диаграмм MS Excel

15. Нажмите кнопку «Далее>» и в следующем появившемся окне выберите, где разместить диаграмму: на существующем листе.

Окно №4 Мастера диаграмм MS Excel

16. Нажмите на кнопку «Готово», и на листе Excel появится график плана и выработки стали по датам.

Читайте так же:
Как защитить / заблокировать скрытые столбцы в Excel?

Первая часть работы завершена, и теперь вы знаете, как составлять графики в Excel.

Графики в Excel "Выпуск металлоконструкций" вариант №1

Форматирование графиков в Excel.

Однако выглядит это немного убого. Мы можем придать графикам более привлекательный вид, отформатировав их, то есть сделав их более красочными.

Изменяем внешний вид поля диаграммы.

Окно Excel "Способы заливки"

Щелкните правой кнопкой мыши на серой «Области диаграммы» и выберите «Форматировать область диаграммы» в появившемся контекстном меню.

2. Затем нажмите «Методы заливки», выберите вкладку «Градиент» и настройте цвета, как показано слева.

Окно Excel "Формат области построения"

3. Нажмите кнопку «ОК». Пример выбранного нами форматирования можно найти в окне «Формат области рисования».

4. Черные сплошные линии с самыми толстыми линиями образуют рамку.

5. Закройте окно «Формат области построения», нажав на кнопку «OK». Диаграмма изменила свой внешний вид.

Графики в Excel "Выпуск металлоконструкций" вариант №2

Форматируем линии графиков.

Выберите «Форматировать данные строки», щелкнув правой кнопкой мыши на строке «План выпуска». из всплывающего меню

2. Вы можете настроить вкладку «Вид», как показано на скриншоте ниже.

Окно Excel "Формат ряда данных"

Красная линия средней толщины пересекала маркер круглой формы размером 6 пт, который стал белым с красным обрамлением.

Строка «Проблема-факт» также должна быть отформатирована аналогичным образом, но с использованием синего цвета и только ромбических маркеров.

3. Закройте окно «Формат ряда данных», нажав на кнопку «ОК», диаграмма приобрела еще более совершенный вид.

Графики в Excel "Выпуск металлоконструкций" вариант №3

Форматируем оси X и Y.

Окно Excel "Формат оси" X

1. Щелкните правой кнопкой мыши на оси X и выберите «Форматировать ось…» во всплывающем меню.

2. Настраиваем вкладку «Шкала» так, как на представленном слева снимке экрана. Цену делений установим 7 дней, конец недели — воскресенье.

Для оси Y мы ничего не будем менять!

Изменяем размеры диаграммы, шрифтов заголовка и названий осей.

В качестве последнего шага в процессе форматирования зафиксируем размеры всего графика и области графика. Для этого выделите объект щелчком левой кнопки мыши и, нажав и удерживая той же левой кнопкой мыши средний маркер на границе области, перетащите границу в нужное место. Затем повторите эту процедуру с другими областями области диаграммы и области построения графика.

Наконец, необходимо увеличить размер шрифта в заголовке и метках осей. Для этого используется стандартная панель форматирования Excel: текст выделяется, назначаются соответствующие размеры и цвета шрифта. Надписи, включая «легенду», следует перемещать и корректировать на поле графика с помощью мыши.

Было настоящим удовольствием создавать эти красивые, четкие, аккуратные графики Excel!

Графики в Excel "Выпуск металлоконструкций" вариант №4

Итоги.

Если посмотреть на колонки цифр в исходной таблице в течение 5 секунд, то трудно сказать что-либо кроме того, что план по производству стали перевыполнен.

Быстрый взгляд на графики показывает, что производство до 17-го дня шло по графику, и даже была небольшая задержка. С 18-го дня производство стало превышать план, и уже на 25-й день месячный план был выполнен, после чего производство металлоконструкций снова немного замедлилось.

Информативность диаграмм Excel гораздо выше, чем числовых таблиц данных!

Читайте так же:
Как изменить размер ячейки на дюймы / см / мм / пиксели в Excel?

Думаю, все, кто дочитал до конца этой статьи, ответили на вопрос, вынесенный в заголовок статьи, и теперь знают, как строить диаграммы в Excel.

Подпишитесь на уведомления о статьях в конце каждой статьи или в верхней части страницы.

Пожалуйста, подтвердите свою подписку, нажав на ссылку в письме, которое сразу же придет на указанный вами адрес электронной почты (оно может оказаться в папке спам).

Пишите свои комментарии, дорогие читатели! Ваше мнение важно для меня и других читателей.

11.9 Работа с диаграммами: объект Chart

Важной функцией Excel является анализ данных. Для анализа данных часто удобно использовать диаграммы с их особыми свойствами, например, трендами. Создание множества подобных диаграмм (обычно на основе информации из базы данных) часто автоматизировано.

В диаграммах Excel есть некоторые терминологические нюансы. Графический интерфейс Excel называет его диаграммой (меню Вставка -> Диаграмма), но на английском языке он называется Chart, и ему соответствует объект Chart. В объектной модели Excel есть также объект Diagram, но это диаграмма отношений (которую можно добавить с помощью русского графического интерфейса Excel через меню Add->Diagram). Под диаграммой в этом разделе будет пониматься то же самое, что создатели русского Excel — под диаграммой.

Диаграммы в Excel создаются с помощью объекта «Диаграмма».

Лучше всего сначала объявить этот объект:

Dim oChart как График

Далее можно создать диаграмму. Мы создали диаграмму, вызвав метод Add() коллекции Charts, который мы уже использовали много раз:

Set oChart = ActiveWorkbook.Charts.Add(, ActiveSheet)

В принципе, диаграмма уже создана, но поскольку никакие свойства не определены, она выглядит как чистый лист. Для того чтобы она имела содержание, необходимо сделать еще несколько шагов:

Первым (и единственным обязательным) действием является указание источника данных графика, для чего предназначен метод SetSourceData(). Источником может быть только объект Range (он передается в качестве первого и единственного обязательного параметра этого метода). Второй параметр (необязательный) определяет порядок чтения данных — сначала по столбцам или сначала по строкам. Например, в нашем случае это может выглядеть следующим образом:

В принципе, если запустить созданный код на выполнение, то диаграмма уже будет создана. Для всех остальных параметров будут приняты значения по умолчанию. Однако на практике нужно определить еще хотя бы тип диаграммы (по умолчанию она будет выглядеть как "обычная гистограмма", то есть ряд из столбиков разной длины). Для этой цели используется свойство ChartType, для которой разработчиками предусмотрено целых 73 значения. Например, чтобы преобразовать диаграмму в обычный график, можно использовать код вида:

Еще одна очень распространенная задача — добавление рядов на диаграмму. Для этого необходимо создать объект Series — ряд, а затем определить свойство Values для ряда (оно передается объекту Range как значение):

Dim oSeries As Series

Set oSeries = oChart.SeriesCollection.NewSeries

Пользователи часто говорят, что диаграммы нужно создавать не на отдельном листе, а на том же листе, что и данные. По умолчанию диаграмма создается в оперативной памяти и размещается на отдельном листе. Если нам нужно поместить его на существующий лист, мы сначала создаем его на отдельном листе, а затем перемещаем с помощью метода Location. Отдельный лист, созданный для диаграммы, автоматически исчезает:

Читайте так же:
Как легко удалить все изображения или другие объекты в Excel?

oChart.Location xlLocationAsObject, "Лист1"

Обратите внимание, что метод Location принимает в качестве первого параметра одну из констант (xlLocationAsNewSheet — переходит на специально созданный новый лист, xlLocationAsObject — переходит на объект, т.е. лист), а в качестве второго параметра — не объект листа, как можно подумать, а хотя бы его имя. Если код будет использоваться как в русской, так и в английской версии Excel, лучше получить имя листа программно.

Большая неприятность, связанная с методом Location, заключается в том, что после перемещения диаграммы внутрь листа объектная ссылка на эту диаграмму теряется, и надо находить объект этой диаграммы заново. При попытке повторного обращения к объекту Chart выдается сообщение "Automation Error". Лучше всего поэтому вызов метода Location помещать в самый конец кода, посвященного диаграмме. В противном случае нам придется разыскивать созданную нами диаграмму и заново получать на нее объектную ссылку, например так:

Dim oSeries как серия

Set oSeries = Worksheets(1).ChartObjects(1).Chart.SeriesCollection.NewSeries

Это, конечно же, гораздо менее практично в использовании.

Свойства и методы объекта диаграммы используются для настройки многих других параметров диаграммы:

  • свойство ChartArea — это свойство возвращает одноименный объект ChartArea, который представляет собой область, занимаемую диаграммой и используется для настройки внешнего вида диаграммы (свойства Font, Interior и т.п.). Если необходимо настроить внешний вид не всей диаграммы, а той ее части, которая используется непосредственно для вывода графика, используется схожее свойство PlotArea. По умолчанию диаграмма размещается прямо по центру листа. Если необходимо ее переместить в точно определенное место листа, используются знакомые на свойства Top, Height, Left и Width для объекта ChartArea.
  • свойство ChartTitle возвращает одноименный объект, при помощи которого можно настроить заголовок диаграммы (с такими свойствами, как Text, Font, Border и т.п.);
  • ChartType — важнейшее свойство, про которое мы уже говорили. Определяет тип диаграммы;
  • HasDataTable — если установить это свойство в True, то в нижней части диаграммы (по умолчанию) появится таблица с числами, на основе которых была создана диаграмма. Одновременно будет создан программный объект DataTable, при помощи которого можно настроить представление этой таблицы. Схожим образом действуют свойства HasLegend, HasPivotFields и HasTitle.
  • Name — это свойство позволяет настроить имя диаграммы (как название вкладки в Excel). По умолчанию диаграммы называются последовательно "Диаграмма1", "Диаграмма2" и т.п.
  • SizeWithWindow — если поставить значение этого свойства в True (по умолчанию False), то размер диаграммы будет подогнан таким образом, чтобы точно соответствовать размеру листа.
  • Tab — свойство, о котором мало кто подозревает. Оно позволяет настроить при помощи одноименного объекта внешний вид вкладки в книге Excel для диаграммы (или просто листа). Например, чтобы пометить вкладку зеленым, можно воспользоваться кодом
Читайте так же:
Как защитить сводную таблицу в Excel?

oChart.Tab.Color = RGB(0, 255, 0)

  • Visible — опция, позволяющая скрыть график, не удаляя его.

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

Ниже перечислены самые важные методы объекта Chart:

  • метод Activate() используется очень часто. Он позволяет сделать диаграмму активной (то есть просто перейти на нее);
  • метод ApplyCustomType() позволяет поместить создать диаграмму своего собственно пользовательского типа (для этого необходимо вначале создать шаблон для этого типа и поместить его в галерею);
  • метод ApplyDataLabels() позволяет поместить на диаграмму метки для размещенных на ней данных. Этот метод принимает множество параметров, которые позволяют настроить отображение данных меток (показывать или не показывать значения и т.п.);
  • метод Axes() возвращает объект, представляющий оси диаграммы. Затем этот объект можно использовать для настройки данных осей;
  • ChartWizard() — этот метод позволяет быстро переформатировать диаграмму, как будто бы прошли на графическом экране при помощи мастера построения диаграмм и передали ему значения. Позволяет при помощи одной строки кода добиться того, что другими способами потребовало бы минимум несколько строк;
  • Copy() — позволяет скопировать диаграмму в другое место книги (например, чтобы создать новую диаграмму, использовав в качестве основы существующую). Для переноса существующей диаграммы в другое место можно воспользоваться методами Location() или Move().
  • CopyPicture() — замечательный метод, который позволяет поместить диаграмму в буфер обмена как изображение. Затем это изображение можно вставить, например, в документ Word или в любое другое место. Другой вариант — воспользоваться методом Export(), который позволяет создать рисунок, представляющий диаграмму, в виде файла на диске.
  • Delete() — просто удаляет диаграмму;
  • Evaluate() — как обычно, этот метод позволяет найти нужную диаграмму в книге по ее имени;
  • PrintOut() — возможность отправить диаграмму на печать. Этот метод принимает множество параметров, которые позволяют настроить такой вывод;
  • Refresh() — возможность обновить диаграмму, если изменились данные, на основе которых она строилась;
  • Select() — возможность выделить диаграмму (равносильно щелчку по ней мышью). Deselect() — снятие выделения (равносильно нажатию на <Esc>);
  • SetBackgroundPicture() — возможность "подложить" под диаграмму фоновый рисунок. Конечно, он должен быть не очень ярким;
  • SetSourceData() — важнейший метод, который позволяет определить данные, на основе которых строится диаграмма. Про него мы уже говорили.

Для объекта Chart предусмотрено также события "на все случаи жизни" — реакция на щелчки мышью, на выделение/снятие выделения, активизацию, пересчет данных, изменение размера и т.п., однако используются такие события на практике нечасто.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector