Как оцифровать график в excel

Обновлено: 07.07.2024

Одним из революционных нововведений в крайней версии Microsoft Excel 2010 стали инфокривые sparklines - миниграфики, помещающиеся внутри ячеек и наглядно отображающие динамику числовых данных:

Чтобы создать подобные миниграфики, нужно выделить ячейки, куда мы хотим их поместить и воспользоваться кнопками группы Спарклайны (Sparklines) с вкладки Вставка (Insert) :

В открывшемся диалоговом окне нужно задать диапазон исходных данных и диапазон вывода:

Созданные миниграфики можно всячески форматировать и настраивать с помощью динамической вкладки Конструктор (Design) :

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

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

Что делать, если у вас пока старая версия Excel? Или нужен тип графика, которого нет в наборе спарклайнов? Переходим к следующим способам!

Способ 2. Дополнительные надстройки для микрографиков

На самом деле, идея подобных графиков витала в воздухе уже достаточно давно. Еще для 2003 версии Excel существовало несколько надстроек с подобным функционалом, самым известными из которых были замечательная бесплатная Sparklines Эдварда Тафта и и платные BonaVista microcharts (169$) и Bissantz SparkMaker (200$). Да и я в свое время тоже пытался сделать что-то подобное в надстройке PLEX.

Если брать в качестве примера бесплатную надстройку Sparklines, то после ее подключения мы получим новую вкладку (или панель инструментов) примерно такого вида:

Как легко заметить, эта надстройка умеет строить заметно больше трех типов диаграмм :)

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

Способ 3. Повтор символов N-раз

"Бюджетным вариантом" одномерных микрографиков являются повторяющиеся однотипные символы, имитирующие линейчатую диаграмму. Для этого можно воспользоваться текстовой функцией ПОВТОР (REPT) , которая умеет выводить в ячейку любой заданный символ нужное количество раз. Для вывода нестандартных символов (зная их код) можно использовать функцию СИМВОЛ (CHAR) . В общем и целом это выглядит так:

Символ с кодом 103 - черный прямоугольник шрифта Webdings, поэтому не забудьте установить этот шрифт для ячеек C2:C12. Также можно поиграться с символами других шрифтов, например в столбце Е использован символ с кодом 110 из шрифта Wingdings.

Способ 4. Макросы

Способ представляет собой улучшенный предыдущий вариант, где набор повторяющихся символов (используется знак "|") создает не формула, а простая пользовательская функция на VBA. Причем для каждой ячейки создается отдельный столбец, т.к. функция использует символ переноса строки после каждого числа - выглядит это примерно так:

Чтобы использовать этот трюк у себя в файле, открываем редактор VBA (Alt+F11), добавляем новый модуль в книгу (меню Insert - Module) и копируем туда код функции NanoChart:

Затем вставляем функцию NanoChart в нужные ячейки, указывая в качестве аргументов числовые данные, как на рисунке выше. Для получившихся ячеек с микрографиками необходимо включить перенос по словам и поворот на 90 градусов через меню Формат - Ячейки - Выравнивание (Format - Cells - Alignment) . Константа MaxSymbols задает длину максимально высокого столбца в минигистограмме.

Теперь в мастере функций в категории Определенные пользователем появилась новая функция LineChart c двумя аргументами - диапазоном и кодом цвета графика. Если ее вставить в пустую ячейку, например, справа от числовой строки и скопировать затем, как обычно, на весь столбец, то получим весьма симпатичное отображение числовых данных в виде миниграфиков:

С задачей оцифровки графиков функций и кривых приходится сталкиваться почти каждому инженеру и студенту. Традиционный «ручной» метод очень неудобен и к тому же вносит большие погрешности в данные. Для единоразовой задачи этот метод не так плох, но если графиков больше чем один и на каждом изображена не одна кривая, а семейство кривых?

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

1. Подготовка изображения

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

Обработку изображений я рекомендую программу Adobe Photoshop. При помощи инструмента Curves добиваемся полноценного контраста, далее при помощи фильтра Smart Sharpen повышаем резкость. Несомненным плюсом Photoshop является возможность обработки большого количества изображений путем записи экшена (Action) и применения его совместно с пакетной обработкой (File – batch processing).

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

Рисунок 1.1 – Изображение графика До обработки и После обработки

2. Оцифровка координат

Для оцифровки координат я использовал условно-бесплатную программу GetData Graph Digitize версии 2.26. После запуска программы открываем наше обработанное изображение «Файл – Открыть изображение». После открытия, перед нами предстанет стандартное рабочее пространство.

Рисунок 2.1 – Стандартный интерфейс Graph Digitize

2.1. Установка системы координат (СК)

Первое, что нам необходимо сделать – это установить систему координат, т.е. обозначить линии осей. Для этого переходим «Команды – Установить систему координат». Далее зажав ЛКМ находим точку начала координат и кликаем по ней. В появившемся окне вводим значение начала координат (Xmin). Далее аналогично устанавливаем значения Xmax, Ymin и Ymax. Для удобной установки точек необходимо открыть окошко лупы «Вид — Лупа». После установки опорных точек отобразятся линии осей и откроется окно «Параметры Системы координат» в котором можно переназначить значения опорных точек и установить логарифмический масштаб оси.

Для визуального контроля качества установки СК можно отобразить сетку с заданным шагом «Вид – Показывать сетку». В случае корректной установки СК линии сетки должны быть строго параллельны линиям на изображении графика. Стоит отметить, что при сканировании разворотов график часто оказывается в районе сгиба, и одна из осей получается изогнутой. В данном случае корректно установить СК не представляется возможным, поэтому на этапе сканирования следует плотнее прижимать разворот к стеклу.

Рисунок 2.2 – Вид с установленной системой координат и сеткой

2.3. Оцифровка кривой

Приступим к установке точек на графике. Для этого перейдем в режим установки точек (Ctrl+P). В данном режиме клик ЛКМ устанавливает новую точку. Для отображения таблицы координат выбранных точек необходимо перейти «Вид – Окно информации». Для удаления точек используется ластик точек данных «Команды — Ластик точек данных» (Ctrl + E)

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

Если на графике присутствует больше чем 1 кривая или семейство кривых, то после установки точек на первой нужно добавить новую линию «Команды – Добавить линию». После чего можно будет выставить точки на второй кривой и т.д.

Если на изображении графика нет сетки, то можно воспользоваться автоматическим алгоритмом трассировки кривой (Ctrl + T). При наличии сетки алгоритм выдает много ошибок.

Рисунок 2.3 – Вид с установленными точками на кривой

2.4. Экспорт данных

Для дальнейшей обработки полученных данных необходимо экспортировать координаты точек в .txt файл или в буфер обмена (удобно в случае если у нас только одна кривая). В программе GetData Graph Digitize экспорт в .txt выполняется вызовом команды «Файл – Экспорт данных» (Ctrl + Alt + E). После нажатия в открывшемся окне предлагается задать путь сохранения и имя файла.

Создано программой GetData Graph Digitizer 2.26.0.20, дата создания October 01 2017, 21:16,
на основе файла 'C:\Users\Андрей\Downloads\Статья Хабр\pr-1\IMG.jpg'

В меню «Установки – Параметры» устанавливается формат вывода данных. Там же можно включить сортировку точек по значению координаты X, если на вашей кривой для каждого X существует уникальный Y, для исключения случайных ошибок в последовательности установке точек.

Рисунок 2.4 – Установки экспорта

3. Построение математической модели кривой

В финале выполним аппроксимацию полученных данных и проверим корректность полученной математической модели. Для этого я предлагаю использовать систему компьютерной алгебры Wolfram Mathematica.

Для быстрого импорта данных в Wolfram Mathematica скопируем координаты точек из экспортированного файла и вставим в пустую ячейку Excel. В итоге на листе появятся 2 колонки данных X и Y соответственно.

Рисунок 3.1 – Данные в Excel

Следующим шагом создаем новый документ Wolfram Mathematica и перетягиваем в него файл Excel. В итоге образуется список списков, содержащий координаты точек. Присвоим ему переменную data.

Рисунок 3.2 – Импортированные данные в Wolfram Mathematica

Отобразим импортированные данные при помощи функции ListPlot[].

Рисунок 3.3 – Графическое отображение точек в виде диаграммы разброса данных

Аппроксимируем точки полиномом 5й степени. Для этого используем функцию LinearModelFit[]. В итоге мы получим объект класса FittedModel[]. Присвоим ему переменную fit.

Вычислим коэффициент детерминации R^2, показывающий какую долю вариации (разброса) переменной, объясняет полученное уравнение. Чем ближе данный коэффициент к единице, тем большую долю вариации объясняет уравнение. Для этого в качестве аргумента функции fit укажем «RSquared». В данном случае R^2 = 0.99, это значит, что наша модель объясняет 99,9% вариации переменной.

Для вычисления значения Y необходимо в качестве аргумента к функции fit указать требуемое значение X.

Рисунок 3.4 – Аппроксимация точек, вычисление коэффициента детерминации и вычисление значения функции

Кроме вычисления коэффициента детерминации, проведем регрессионный анализ. В этот раз в качестве аргумента функции fit укажем «ANOVATable». По полученному результату, можно утверждать, что оправданно использование каждого члена аппроксимирующего полинома. Отобразим полученное уравнение в явном виде, для этого к переменной fit применим функцию Normal[].

Рисунок 3.5 – Регрессионный анализ и полином в явном виде

Далее построим график полинома и отобразим на нем исходные точки. При помощи стандартного синтаксиса настроим стиль графика добавим подписи к осям и название графика.

Рисунок 3.6 – Итоговый график

Рисунок 3.7 – Сравнение итогового графика с исходными данными

Возможности для анализа математической модели в Wolfram Mathematica воистину огромны, но мы ограничимся представленными выше. Интересующиеся могут узнать больше путем вычисления функции fit[«Properties»].

В итоге мы изучили возможности применения Wolfram Mathematica и Graph Digitizer для оцифровки графиков и подбора математической модели кривой. Использованное программное обеспечение позволяет выполнить поставленную задачу с минимальными усилиями и с высоким качеством.

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

Для того, чтобы получить данные, нужно “оцифровать” такой график (или графический объект), другими словами, нужно получить набор абсцисс и ординат точек графика — далее над ними можно будет производить различные манипуляции: построить новый (качественный) график, производить вычисления, переведя его в новый формат (например, построив сплайн) и пр.

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

Принцип работы

Работа программы довольно проста. Необходимо поместить цифровую иллюстрацию (скан, фото, скриншот, другая картинка) на некоторое поле на “первый” слой. Далее выполняется еёручная обработка:

подбор рабочей области (прямоугольник) с заданием “реальных” координат его левой нижней и правой верхней вершины — другими словами области определения и значений той функции (графика, набора точек), который мы получим в результате;
простановка опорных точек, по которым будет построен B-сплайн заданной степени;
предпросмотр результата;
вывод результата в вычисляемом виде для дальнейшей работы.

они дают гибкие кривые (если степень B-сплайна выше 1-й),
дают компактные аналитические представления кривых (кривые Безье — это вырожденный случай B-сплайнов, в которых каждая точка, если говорить просто, влияет на весь вид кривой);
просты в работе (реализованы во многих пакетах и не так сложны, если их потребуется запрограммировать с нуля самостоятельно).

Для создания программы оцифровки графиков — как и для многих других задач — мы используем язык Wolfram Language.

Описание программы

Конечно, мы не будем описывать, как работает по шагам весь код программы, представленной ниже, это было бы очень долго и, как нам кажется, тех, кого заинтересует рассматриваемый вопрос или, более того, тех перед кем он остро стоит — сами смогут разобраться во всех “винтиках” кода, благо, он краток.

Мы же обратим внимание на его главные элементы.

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

Одной из основных функций в работе данной программы является функция DynamicModule для создания интерактивных пространств и объектов. С её помощью происходит «оживление» всей конструкции.

Функция Grid необходима для организации пространства — она позволяет строить таблицы различной формы и размещать в их ячейках контент: тексты, слайдеры, иллюстрации, интерактивные объекты и т. д.

Функции LocatorPane, Slider, Slider2D, InputField служат для того, чтобы сделать интерактивные элементы — поле с выбором точек, слайдеры (одно- и двумерные, соответственно), поле ввода текста (для области определения функции).

Для «рисования» графическими примитивами служит функция Graphics (и если нужно в 3D — то Graphics3D).

И, конечно, самой важной здесь является функция BSplineCurve, которая позволяет представить набор точек в виде готовой кривой B-сплайна.

На видео ниже вы можете посмотреть то, как работает программа вживую:

Финальный код программы

Итоговая функция на самом деле не так велика, как могло бы показаться. Хотя это, конечно, прямое следствие больших возможностей Wolfram Language:

Преимущества работы с оцифрованными функциями на примерах

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

Например, пусть дан график:

Давайте оцифруем его (см. видео выше) и произведем вычисления.

Для начала только заменим головную часть полученного в ходе работы программы выражения с BSplineCurve на BSplineFunction, которая строит аналитическое выражение с которым можно уже производить вычисления:

Единственный недостаток — это то, что такая функция нормирована на 1, т. е. функция f(t) при изменении t от 0 до 1 пробежит все свои значения, которыми являются точки B-сплайна:

Однако, с этим легко бороться. Достаточно построить интерполяционный полином:

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

Вот, например, данные по курсам валют за несколько месяцев:

Строить график по всей таблице, как легко сообразить, не лучшая идея. Красивым решением в подобной ситуации может стать создание интерактивной диаграммы, которую пользователь может сам подстраивать под себя и ситуацию. А именно:

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

Выглядеть это может примерно так:

Нравится? Тогда поехали.

Шаг 1. Создаем дополнительную таблицу для диаграммы

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

В Excel 2007/2010 к созданным диапазонам можно применить команду Форматировать как таблицу ( Format as Table) с вкладки Главная ( Home) :

Это даст нам следующие преимущества:

Любые формулы в таких таблицах автоматически транслируются на весь столбец – не надо «тянуть» их вручную до конца таблицы
При дописывании к таблице новых строк в будущем (новых дат и курсов) – размеры таблицы увеличиваются автоматически, включая корректировку диапазонов в диаграммах, ссылках на эту таблицу в других формулах и т.д.
Таблица быстро получает красивое форматирование (чересстрочную заливку и т.д.)
Каждая таблица получает собственное имя (в нашем случае – Таблица1 и Таблица2), которое можно затем использовать в формулах.

Подробнее про преимущества использования подобных Таблиц можно почитать тут.

Шаг 2. Добавляем флажки (checkboxes) для валют

В Excel 2007/2010 для этого необходимо отобразить вкладку Разработчик ( Developer) , а в Excel 2003 и более старших версиях – панель инструментов Формы ( Forms) . Для этого:

В Excel 2003: выберите в меню Вид – Панели инструментов – Формы (View –Toolbars –Forms)
В Excel 2007: нажать кнопку Офис – ПараметрыExcel – Отобразить вкладку Разработчик на ленте (OfficeButton –Exceloptions –ShowDeveloperTabintheRibbon)
В Excel 2010: Файл – Параметры – Настройка ленты – включить флаг Разрабочик (File –Options –CustomizeRibbon –Developer)

На появившейся панели инструментов или вкладке Разработчик ( Developer) в раскрывающемся списке Вставить ( Insert) выбираем инструмент Флажок ( Checkbox) и рисуем два флажка-галочки для включения-выключения каждой из валют:

Текст флажков можно поменять, щелкнув по ним правой кнопкой мыши и выбрав команду Изменить текст ( Edit text) .

Теперь привяжем наши флажки к любым ячейкам для определения того, включен флажок или нет (в нашем примере это две желтых ячейки в верхней части дополнительной таблицы). Для этого щелкните правой кнопкой мыши по очереди по каждому добавленному флажку и выберите команду Формат объекта ( Format Control) , а затем в открывшемся окне задайте Связь с ячейкой ( Cell link) .

Шаг 3. Транслируем данные в дополнительную таблицу

Теперь заполним дополнительную таблицу формулой, которая будет транслировать исходные данные из основной таблицы, если соответствующий флажок валюты включен и связанная ячейка содержит слово ИСТИНА (TRUE):

Заметьте, что при использовании команды Форматировать как таблицу ( Format as Table) на первом шаге, формула имеет использует имя таблицы и название колонки. В случае обычного диапазона, формула будет более привычного вида:

Обратите внимание на частичное закрепление ссылки на желтую ячейку (F$1), т.к. она должна смещаться вправо, но не должна – вниз, при копировании формулы на весь диапазон.

Шаг 4. Создаем полосы прокрутки для оси времени и масштабирования

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

Полосу прокрутки ( Scroll bar) берем там же, где и флажки – на панели инструментов Формы ( Forms) или на вкладке Разработчик ( Developer) :

Рисуем на листе в любом подходящем месте одну за другой две полосы – для сдвига по времени и масштаба:

Каждую полосу прокрутки надо связать со своей ячейкой (синяя и зеленая ячейки на рисунке), куда будет выводиться числовое значение положения ползунка. Его мы потом будем использовать для определения масштаба и сдвига. Для этого щелкните правой кнопкой мыши по нарисованной полосе и выберите в контекстном меню команду Формат объекта ( Format control) . В открывшемся окне можно задать связанную ячейку и минимум-максимум, в пределах которых будет гулять ползунок:

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

Шаг 5. Создаем динамический именованный диапазон

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

Отступом от начала таблицы вниз на заданное количество строк, т.е. отступом по временной шкале прошлое-будущее (синяя ячейка)
Количеством ячеек по высоте, т.е. масштабом (зеленая ячейка)

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

Для создания такого диапазона будем использовать функцию СМЕЩ ( OFFSET) из категории Ссылки и массивы ( Lookup and Reference) - эта функция умеет создавать ссылку на диапазон заданного размера в заданном месте листа и имеет следующие аргументы:

В качестве точки отсчета берется некая стартовая ячейка, затем задается смещение относительно нее на заданное количество строк вниз и столбцов вправо. Последние два аргумента этой функции – высота и ширина нужного нам диапазона. Так, например, если бы мы хотели иметь ссылку на диапазон данных с курсами за 5 дней, начиная с 4 января, то можно было бы использовать нашу функцию СМЕЩ со следующими аргументами:

Хитрость в том, что константы в этой формуле можно заменить на ссылки на ячейки с переменным содержимым – в нашем случае, на синюю и зеленую ячейки. Сделать это можно, создав динамический именованный диапазон с функцией СМЕЩ ( OFFSET) . Для этого:

В Excel 2007/2010 нажмите кнопку Диспетчер имен (NameManager) на вкладке Формулы (Formulas)
В Excel 2003 и старше – выберите в меню Вставка– Имя– Присвоить(Insert – Name – Define)

Для создания нового именованного диапазона нужно нажать кнопку Создать ( Create) и ввести имя диапазона и ссылку на ячейки в открывшемся окне.

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

Теперь чуть сложнее – создадим диапазон с именем Euros, который будет ссылаться с помощью функции СМЕЩ ( OFFSET) на данные по курсам евро за выбранный отрезок времени, используя только что созданные до этого диапазоны Shift и Zoom и ячейку E3 в качестве точки отсчета:

Обратите внимание, что перед именем диапазона используется имя текущего листа – это сужает круг действия именованного диапазона, т.е. делает его доступным в пределах текущего листа, а не всей книги. Это необходимо нам для построения диаграммы в будущем. В новых версиях Excel для создания локального имени листа можно использовать выпадающий список Область.

Аналогичным образом создается именованный диапазон Dollars для данных по курсу доллара:

И завершает картину диапазон Labels, указывающий на подписи к оси Х, т.е. даты для выбранного отрезка:

Общая получившаяся картина должна быть примерно следующей:

Шаг 6. Строим диаграмму

Выделим несколько строк в верхней части вспомогательной таблицы, например диапазон E3:G10 и построим по нему диаграмму типа График ( Line) . Для этого в Excel 2007/2010 нужно перейти на вкладку Вставка ( Insert) и в группе Диаграмма ( Chart) выбрать тип График ( Line) , а в более старших версиях выбрать в меню Вставка – Диаграмма ( Insert – Chart) . Если выделить одну из линий на созданной диаграмме, то в строке формул будет видна функция РЯД ( SERIES) , обслуживающая выделенный ряд данных:

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

=РЯД(Лист1!$F$3;Лист1! $E$4:$E$10 ;Лист1! $F$4:$F$10 ;1)

=РЯД(Лист1!$F$3;Лист1! Labels ;Лист1! Euros ;1)

Выполнив эту процедуру последовательно для рядов данных доллара и евро, мы получим то, к чему стремились – диаграмма будет строиться по динамическим диапазонам Dollars и Euros, а подписи к оси Х будут браться из динамического же диапазона Labels. При изменении положения ползунков будут меняться диапазоны и, как следствие, диаграмма. При включении-выключении флажков – отображаться только те валюты, которые нам нужны.

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

Читайте также: