Как сделать оцифровку данных в excel?

Проект «РЗА»

Все о защите и автоматике электрических сетей

Главное меню

Навигация по записям

Программа для оцифровки графиков Мультискан

Приветствую коллеги.

У нас хорошая новость — мы запускаем на Проекте новую платформу для оцифровки каталогов и справочников, Мультискан. Бета-версия доступна по этой ссылке https://www.pro-rza.ru/multiscan

Мультискан — это онлайн сервис, который позволяет переводить сканированные изображения различных графиков (форматы png, jpg, gif) в числовой вид (файл с координатами точек формата .txt).

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

Например, вы можете нанести эту кривую на карту селективности в Гридис-КС (PRO), через вкладку «Загрузить из файла». Таким образом вы получаете хороший дополнительный функционал при работе над проектом.

Если вы не используете Гридис-КС (PRO), то можете легко построить ту же кривую в EXCEL, MathCad, MatLab и других расчетных комплексах.

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

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

Кстати, те аналоги сервиса, которые мы смогли отыскать платные и в основном на английском языке. А Мультискан бесплатный и на русском. Будем считать это нашим небольшим вкладом в цифровизацию энергетики России)

Похожие статьи:

Кабинет не работает в Opera 12
Авторизация в Firefox 61 не проходит
Такая вот обратная связь.

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

Интересный сервис!
Насчёт оцифровки российской энергетики.
Какова политика доступа к проектам пользователей?
Может ли пользователь ими поделиться со всеми заинтересованными не предоставляя доступа к своему личному кабинету?
Рассчитываете ли Вы, Дмитрий, (или кто-либо из вашей команды) пользоваться проектами пользователей как дополнительным источником?
Может ли пользователь, связанный какими-либо условиями по неразглашению, рассчитывать на то, что оцифрованная при помощи этого сервиса характеристика не выплывет в неожиданном месте?
Вообще, мне лично больше всего импонирует возможность превращения этого сервиса в доступное отовсюду хранилище (пусть и только после авторизации) всевозможных время-токовых характеристик, поскольку очевидным преимуществом данного сервиса является возможность быстрой самостоятельной оценки адекватности предлагаемой характеристики «на глаз». Идеальным выглядит вариант, когда создав проект и добавив к нему описание, автор выбирает возможность его опубликовать и тот появляется в личном кабинете каждого пользователя сервиса в третьем разделе «Загрузить проект из репозитория». Конечно дальше можно прикручивать к каждому такому общедоступному проекту различные метрики и голосовалку для лучшей сортировки в списке раздела. Проекты можно отображать, к примеру, не только по имени, но и отображать слева миниатюру характеристики на фоне исходного изображения, а чуть ниже названия — автора и описание.

Артур, изначально мультискан разрабатывали для оцифровки графиков для Гридис-КС. Первая версия была сделана в Autocad и была не очень удобна для того, чтобы можно было отдать часть работ по оцифровки пользователям (тем более, что требовался дополнительно Autocad). Мы решили сделать его полностью в виде сервиса. В будущем у нас есть планы по его совершенствованию для тех самых хранилищ проектов всех пользователей. Правда реализация данного хранилища будет немного отличаться от описанной Вами, и будет представлять архив проектов с описанием источников (каталога, производителей и т.д.). Все проекты, которые Вы загружаете и оцифровываете в мультискан останутся в мультискане, если Вы сами не захотите выложить их в архив проектов для публичного пользования (это в следующих версиях). Спасибо за отзыв и предложение!

«Укажите значение осей X» — при попытке произвести расчет

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

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.

Графическое представление и визуализация больших данных в Excel

Как сделать правильное графическое представления большого объема данных для комфортного проведения визуального анализа в Excel? Используя средства программы Excel создадим собственные инструменты для выборочного масштабирования данных и визуальной навигации по данным в истории продаж за большой период.

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

Перед тем как приступить к графическому представлению для визуализации больших данных на динамическом графике в Excel, сначала смоделируем ситуацию. На пример, у нас есть статистический отчет ежемесячных продаж по трем видам товаров на протяжении 3-х лет (2019-2021 год). Расположите исходную таблицу отчета в диапазоне ячеек B9:F45, как показано ниже на рисунке:

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

Но сначала подготовим исходные данные таблицы. По оси X на графике лучше будут читаться названия месяцев вместо дат. Поэтому между первым «Дата» (B) и втором «Товар 1» (C) столбцом таблицы вставим дополнительный столбец с названием «Месяц» который в своих ячейках будет содержать формулу:

Данная формула состоит из 4-х функций и 3-х частей:

  1. Формула функций ВЫБОР и МЕСЯЦ позволяет нам преобразовать дату в название месяцев сокращенное до трех букв.
  2. Функция СИМВОЛ с числом 10 в ее аргументе позволяет нам добавить к текстовому значению символ обрыва строки для переноса текста подписей значений на осе X, который должен уместится под каждым столбцом гистограммы.
  3. Функция ГОД извлекает из исходных дат значения годов которые будут добавлены к месяцам в подписях оси X, чтобы не путаться к которому году относится тот или иной месяц.

Кроме оформления подписей оси X нам еще необходимо добавить на график подписи итоговых значений. Поэтому нужно добавить еще один столбец «ИТОГО» к исходной таблицы с формулой суммирования показателей всех 3-х видов товаров по каждому месяцу:

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

Создание визуальной навигации по большим данным в Excel

Для создания отчета в графическом виде выделите не все значения таблицы, а только лишь начиная со второго и по пятый столбец (диапазон ячеек C9:F45) и выберите инструмент «ВСТАВКА»-«Диаграммы»-«Гистограмма с накоплением»:

Как видно выше на рисунке такой способ графического представления данных большого объема не лучшее решение для визуализации. При том что для примера мы было взято показатели за 3 года. А если нужно за 10 лет и с показателями ежедневных продаж? В таком случае нужно сделать отдельный график с возможностью отображения за указанные период времени. Сделаем все удобно и красиво максимально используя все возможности программы Excel.

Несмотря на то что созданный нами график не совсем нам подходит мы будем иго использовать в качестве временной шкалы для удобства. Укажите ему новые размеры в дополнительном меню: «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Размер» высота – 4см и ширина – 34,93 см (такая ширина соответствует 1320 пикселям). А затем просто разместите его над исходной таблицей:

Далее продолжаем настраивать внешний вид шкалы-графика. Удалите все лишние элементы нажав на кнопку плюс «+» с правой стороны графика где в выпадающем меню «ЭЛЕМЕНТЫ ДИАГРАММЫ» следует снять все лишние галочки и отметить необходимые согласно рисунку:

Далее, чтобы настроить внешний вид отображаемых столбцов гистограммы, щелкните правой кнопокой мышки по люому ряду и из появившегося контекстного меню выберите опцию «Формат ряда данных». Затем переходим в «ПАРАМЕТРЫ РЯДА» и измеянем значение в опции «Боковой зазор» на 80%. Там же щелкаем на кнопку «Заливка» и указываем желаемый цвет:

Цвета заливки изменяем отдельно для каждого ряда.

С помощью фигуры прямоугольника создадим курсор для временной шкалы-графика. Выберите инструмент «ВСТАВКА»-«Иллюстрации»-«Фигуры»-«Прямоугольник»:

Убираем заливку и устанавливаем черный цвет контура с толщиной 2 пункта для фигуры прямоугольника используя инструмент из ее дополнительного меню «СРЕДСТВА РИСОВАНИЯ»-«ФОРМАТ»-«Стили фигур»-«Заливка фигуры» и здесь же «Контур фигуры».

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

Читать еще:  Как сделать чтобы в ячейке excel был виден весь текст?

Кликнув правой кнопкой мышки по новому прямоугольнику выбираем из контекстного меню опцию «Формат фигуры». В появившемся окне настройки параметров фигуры изменяем цвет заливки на белый и здесь же устанавливаем «Прозрачность» — 25%. В разделе опций «ЛИНИЯ» отмечаем «Нет линий».

Элементы управления графическим представлением и визуализацией данных

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

  1. Данный курсор будет уметь изменять свою ширину вмещая в себя показатели за минимум 3 и максимум 12 месяцев. Поэтому следует создать элемент управления для изменения размера курсора.
  2. Сам курсор не будет перемещаться по шкале, а вместо этого будет смещаться сама шкала относительно курсора. Создадим еще один элемент для перемещения шкалы под курсором.

Выберите инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Счетчик» и возле таблицы разместите его:

Из контекстного меню счетчика выбираем опцию «Формат объекта» где на вкладке «Элемент управления» изменяем следующие 3 параметра:

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

Максимальное значение: 12 – максимум столбцов месяцев, которые будет охватывать курсор.

Связь с ячейкой: $R$11 – ссылка на ячейку, в которую будет передавать элемент управления счетчик свои текущие числовые значения 3-12.

Аналогичным образом создаем второй элемент управления шкалой выбрав инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Полоса прокрутки». Разместите второй элемент — полосу прокрутки графика возле счетчика под шкалой:

И снова из контекстного меню полосы прокрутки выбираем опцию «Формат объекта» где на вкладке «Элемент управления» изменяем следующие 3 параметра:

Минимальное значение: 0 – это конечная позиция положения временной шкалы, то есть крайнее левое положение.

Максимальное значение: 33 – это число для перемещения курсора при минимальном его размере с охватом в 3 месяца (столбца) для общего количества столбцов (месяцев) на протяжении 3-х лет. То есть: 36-3=33.

Связь с ячейкой: $R$10 – ссылка на ячейку, в которую будет передавать полоса прокрутки свои текущие числовые значения 0-33.

Внимание! Рядом с этой же ячейкой R10 вписываем формулу =33-R10 в ячейке S10. Значения данной ячейки будут использоваться в коде написанных VBA-макросов для оживления шкалы с помощью элементов управления:

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

VBA-макрос для интерактивного графического представления с визуализацией данных

Чтобы оживить нашу временную шкалу вместе с курсором создаем макрос. Выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Visual Basic» (или нажмите комбинацию клавиш Alt+F11), чтобы прейти в редактор макросов. Там в первую очередь следует создать модуль, в который следует поместить следующий код двух макросов:

Код макроса можно скопировать из блока ниже:

Sub linerange_size()
Dim list As Worksheet
Dim linewindow As Shape
Dim lineright As Shape

Set list = Sheets( «Лист1» )
Set linewindow = list.Shapes( «Прямоугольник 1» )
Set lineright = list.Shapes( «Прямоугольник 2» )
With linewindow
.Width = 27 * ActiveSheet.Range( «R11» ).Value
End With
With lineright
.Left = linewindow.Left + linewindow.Width
End With
End Sub

Sub movgchart()
Dim grafik1 As ChartObject
Dim list As Worksheet

Set list = Sheets( «Лист1» )
Set grafik1 = list.ChartObjects( «Диаграмма 1» )
With grafik1
.Left = ActiveSheet.Range( «S10» ).Value * 27
End With

Теперь осталось лишь назначить макросы элементам. Кликаем по каждому элементу правой кнопкой мышки и из контекстного меню выбираем опцию «Назначить макрос» для счетчика – linerange_size, а для полосы прокрутки – movgchart.

Перед тем как использовать элементы управления следует подогнать размер доступного пространства для смещения шкалы влево с помощью изменением ширины первого столбца A на рабочем листе Excel:

Только после этого можно воспользоваться элементами управления шкалой и ее курсором.

Масштабирование в графическом представлении с визуализацией в Excel

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

Для создания именных диапазонов с формулами выберите инструмент: «ФОРМУЛЫ»-«Определенные имена»-«Диспетчер имен». В нем с помощью кнопки создать создаем сразу 4 имени с разными формулами для разных источников данных будущей второй большой гистограммы:

Каждое из 4-х имен является ссылкой на динамически изменяемый формулой диапазон ряда данных гистограммы:

  1. Месяцы – значения оси X, формула:
  2. Товар1 – значения для нижнего (зеленого) ряда данных, формула:
  3. Товар2 – значения среднего (серого) ряда, формула:
  4. Товар3 – значения для верхнего (оранжевого) рада, формула:
  5. Итого – значения для подписей столбцов гистограммы, формула:

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

Выделите диапазон ячеек таблицы C9:G12 (2-6 столбцы и несколько строк) и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«Гистограмма с накоплением». А затем в дополнительном меню «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Данные» жмем на кнопку «Строка/столбец» чтобы получилось так:

Теперь необходимо добавить подписи данных. Для этого необходимо одним кликом выделить только верхний четвертый ряд «ИТОГО». А дальше нажать на кнопку плюс «+» возле графика (справа) и в выпадающем меню «ЭЛЕМЕНТЫ ДИАГРАММЫ» отметить опцию «Подписи данных»:

Не снимая выделения с верхнего ряда выбираем инструмент «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Стили фигур»-«Заливка»-«Нет заливки» и где, чтобы скрыть его с виду. А остальные ряды закрашиваем цветами заливки так же, как и первый график, в том же стиле:

Далее переходим к самой главной части этого большого графика. Выберите инструмент: «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Выбрать данные»:

Сначала в правом разделе «Подписи горизонтальной оси (категории)» нажимаем на кнопку «Изменить» и изменяем значение в поле ввода «Диапазон подписей оси:» на ссылку именного диапазона (обязательно указываем как внешнюю по правилу работы с источниками данных для диаграммам) =Лист1!Месяцы.

Затем в левом разделе «Элементы легенды (ряды)» кликаем по каждому ряду и для каждого нажимаем на кнопку «Изменить», чтобы изменять второе поле ввода «Значения:» на ссылки имен соответственные названиям рядов. Так же указываем как внешние ссылки на имена, например: =Лист1!Товар1.

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

Визуальная навигация по данным таблицы Excel

И на конец для полной читабельности и визуального представления данных создадим еще курсор для таблицы. Этот курсор будет также соответствовать значениям, отображаемым на графике. Он будет иметь возможность изменять свой размер (диапазон) охвата и перемещаться при использовании тех же самых элементов управления.

Для решения данной задачи воспользуемся условным форматированием и логической формулой. Выделите диапазон табличной части исходных данных =B10:G45 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». В появившемся окне «Создание правила форматирования» отмечаем опцию «Использование формул для определения форматируемых ячеек» и в поле ввода вводим формулу:

=(СТРОКА($B$10)+$R$10);СТРОКА($B10)

Затем жмем на кнопку «Формат» и задаем желаемый формат оформления цвета ячеек и текста. После нажатия на кнопку ОК на всех открытых окнах наслаждаемся готовым результатом:

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

4 техники анализа данных в Microsoft Excel

Если вам по работе или учёбе приходится погружаться в океан цифр и искать в них подтверждение своих гипотез, вам определённо пригодятся эти техники работы в Microsoft Excel. Как их применять — показываем с помощью гифок.

Тренер Учебного центра Softline с 2008 года.

1. Сводные таблицы

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

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

Как работать

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

  1. Откройте файл с таблицей, данные которой надо проанализировать.
  2. Выделите диапазон данных для анализа.
  3. Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).
  4. Должно появиться диалоговое окно «Создание сводной таблицы».
  5. Настройте отображение данных, которые есть у вас в таблице.

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

Читать еще:  Как сделать вставку текста в excel?

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

Можно её детализировать, например, по странам. Переносим «Страны».

Можно посмотреть результаты по продавцам. Меняем «Страну» на «Продавцов». По продавцам результаты будут такие.

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

Полезное дополнение. Координаты нигде прописывать не нужно — достаточно лишь корректно указать географическое название в таблице.

Как работать

  1. Откройте файл с таблицей, данные которой нужно визуализировать. Например, с информацией по разным городам и странам.
  2. Подготовьте данные для отображения на карте: «Главная» → «Форматировать как таблицу».
  3. Выделите диапазон данных для анализа.
  4. На вкладке «Вставка» есть кнопка 3D-карта.

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

Также достаточно информативной является круговая диаграмма по годам. Размер круга задаётся суммой.

3. Лист прогнозов

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

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

Как работать

  1. Откройте таблицу с данными за период и соответствующими ему показателями, например, от года.
  2. Выделите два ряда данных.
  3. На вкладке «Данные» в группе нажмите кнопку «Лист прогноза».
  4. В окне «Создание листа прогноза» выберите график или гистограмму для визуального представления прогноза.
  5. Выберите дату окончания прогноза.

В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).

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

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

4. Быстрый анализ

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

Полезное дополнение. Мгновенно можно создавать различные типы диаграмм или спарклайны (микрографики прямо в ячейке).

Как работать

  1. Откройте таблицу с данными для анализа.
  2. Выделите нужный для анализа диапазон.
  3. При выделении диапазона внизу всегда появляется кнопка «Быстрый анализ». Она сразу предлагает совершить с данными несколько возможных действий. Например, найти итоги. Мы можем узнать суммы, они проставляются внизу.

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

Также можно проставить в ячейках разноцветные значки: зелёные — наибольшие значения, красные — наименьшие.

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

Извлечение данных из картинки с помощью Illustrator и Excel 1

В данном примере используется Excel 2013 и Illustrator СС, но метод применим и для других версий и программ. Сложность низкая, требуются базовые навыки работы в Illustrator и Excel.

Работая над отчётом или графиком, мы, как правило, располагаем данными в текстовом виде: таблицей в Эксель или .DOC-файлом. Но что делать, если таблицы с данными нет, а в качестве исходника выступает картинка? В лучшем случае она векторная, в худшем — растровая и плохого качества. Такая задача нам попадалась не раз, так что мы решили написать небольшую инструкцию-подсказку.

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

Рис. 1. Исходная гистограмма

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

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

2. Расставляем направляющие с постоянным интервалом таким образом, чтобы на один год приходилась одна направляющая.

3. С помощью инструмента «Перо» создаем две ломанные кривые, вручную отмечая высоту столбцов. Принадлежащие одному году точки кривых размещаем друг под другом, ориентируясь на созданные на прошлом шаге направляющие. Это нужно для того, чтобы ряд координат X был одинаков у обеих кривых.

Рис. 2. Поверх исходной растровой картинки положены направляющие и нарисованы две векторные кривые

4. В формате .SVG отсчёт координат ведётся с левого верхнего угла монтажной области. Поэтому нашу диаграмму нужно отзеркалить сверху вниз так, чтобы ноль координат на графике совпал с точкой отсчёта .SVG-файла. Все объекты кроме кривых удаляем для того чтобы они не усложняли код .SVG-файла. На этом шаге нужно быть очень внимательным с горизонтальной осью. Она должна оказаться точно на верхней границе монтажной области (в нашем случае красная кривая в 1983 году имеет значение «0», поэтому этой точкой она касается горизонтальной оси).

Рис. 3. Лишнее удалено, кривые отзеркалены по вертикали и выровнены по верхнему левому углу монтажной области

5. Сохраняем документ в формате .SVG, сняв галку «использовать монтажные области». В появившемся диалоговом окне нажимаем на кнопку «Код SVG». Откроется текстовый редактор. Процесс сохранения можно не доводить до конца, так как сам .SVG-файл далее не потребуется.

Рис. 4. Сохранение в .SVG

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

Рис. 5. Искомые части кода

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

Рис. 6. Замена разделителя координат X и Y

8. Теперь можно заменить точки на запятые. Описанный порядок замены не позволит испортить данные. Если провести замену точки на запятую сразу в исходных данных, это приведёт к тому, что запятая будет выполнять несколько ролей и при обработке данных в Экселе возникнут трудности.

Рис. 7. Замена десятичного разделителя, итоговый формат данных

9. Вставляем первый из наборов координат в ячейку Экселя.

Рис. 8. Вставка в ячейку

10. Не снимая выделения с этой ячейки во вкладке меню «Данные» нажимаем «Текст по столбцам». Нужно разбить текстовую строку в ячейке на отдельные ячейки, каждая из которых будет содержать в себе пару координат: X и Y. В качестве разделителя ячеек выбираем «пробел».

Рис. 9. Разбивка текста на ячейки, первый шаг

Рис. 10. Разбивка текста на ячейки, пробел в качестве разделителя

11. Далее нам нужно превратить горизонтальный ряд ячеек в вертикальный. Для этого выделим все ячейки, копировать → нажать правой кнопкой мыши на пустую ячейку ниже → специальная вставка → транспонировать. Горизонтальный ряд удаляем, оставляя только вертикальный.

Рис. 11. Превращение строки в столбец

12. Теперь нужно разбить столбец надвое, в левом будут координаты X, в правом Y. Разделителем в примере выступит точка с запятой. После разбивки нужно проверить, чтобы строки следовали сверху вниз от минимума к максимуму, ориентируясь по первому столбцу (A). Этот столбец в настоящем примере соответствует координатам X.

Рис. 12. Разбивка столбца надвое

Читать еще:  Как сделать фильтр в таблице excel?

Рис. 13. Разделённые и отсортированные столбцы координат

13. Проводим описанную выше обработку и для второго набора координат, отвечающего за вторую кривую.

14. Данные уже можно отобразить в виде точечной диаграммы. Причём, новые версии Экселя распознают пары столбцов X и Y, если в контекстном меню диаграммы зайти в «Тип диаграммы» и рассмотреть предложенные варианты.

Рис. 14. Извлечённые данные и автоматически построенная точечная диаграмма

15. У обоих наборов координат столбец X одинаков. Заменим их столбцом годов как в исходной картинке. По данным такого вида можно строить графики и гистограммы.

Рис. 15. Координаты X заменены на года

16. Не забудьте, что в результате всех этих действий мы получили кривые, построенные по условным значениям, взятым из координат .SVG-файла. Вы сможете перевести все значения в реальные, если у вас есть хотя бы одно реальное значение для любой из точек. На рисунке ниже цифра 224 — реальное значение из сопровождающего текста. Ей соответствует значение 77,342 условных единиц (координата Y из .SVG). Получается, что в нашем примере все значения Y для каждой кривой надо умножить на коэффициент 3,14, чтобы получить реальные значения.

Рис. 16. Расчет коэффициента соответствия условных единиц реальным

17. Выделим коэффициент, копировать → выделить оба столбца Y → правая клавиша → специальная вставка → умножить.

Рис. 17. Домножение столбцов Y на коэффициент соответствия

18. В итоге у нас есть набор данных в Экселе, который соответствует исходной картинке, см. рисунок ниже.

Рис. 18. Исходная гистограмма со значениями

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

Рис. 19. График с маркерами, построенный по обработанным данным

20. С помощью этой инструкции данные извлечённые из картинки можно обрабатывать, анализировать, строить по ним диаграммы другого типа. Нашей целью было сделать интерактивную визуализацию, ниже показан результат и генерирующий его код.

И вот что из этого получилось:

Если у вас есть похожие «помощники»– делитесь! Будем рады узнать что-то новое.

В подготовке материала вместе с Андреем Постуховым участвовали Олег Степанов и Максим Горчаков

Минидиаграммы в ячейках

Очередной способ визуализации данных – это использование гистограмм в условном форматировании.

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

При наличии значений меньше нуля, гистограмма также автоматически примет другую форму

2. Также можно воспользоваться текстовой функцией ПОВТОР, которая выводит в ячейку любой заданный символ нужное количество раз. Для этого вводим в соседнюю от цифр ячейку формулу =ПОВТОР(«|»;B2) и нажимаем «Enter», при шрифте Arial Cyr получится так:

3. Для вывода нестандартных символов (заранее зная код) можно использовать функцию СИМВОЛ. Например, символ с кодом 103 — черный прямоугольник шрифта Webdings, вводим в соседнюю ячейку формулу =ПОВТОР(СИМВОЛ(103);C2:C8) и нажимаем «Enter». Поэтому предварительно установите этот шрифт для ячеек C2:C8.

Также можно использовать символы других шрифтов, например символ с кодом 110 из шрифта Wingdings.

4. Остался применить последний способ – это стрелки в разделе «наборы значков», здесь имеется большой выбор разных элементов, которые также отображаются в самой ячейке.

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

Имеющиеся символы в шрифтах Webdings и Wingdings можно посмотреть в разделе «Вставка» — «Символы»

И в конце предлагаю оценить какая минидиаграмма самая интересная

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

Подписка. Посмотрел другие посты про Ексель- автор молодец.
Эксель — это простейший, крутейший инструмент в умелых руках.
Сделай обзор на формулы ВПР (vlookup) и Если(IF)

Жизнь многих офисных сотрудников, не знающих их, никогда не станет прежней)))

Спасибо)) В планах это уже есть и ещё многое другое)

вы часто ей пользуетесь?

Подскажите пожалуйста — есть столбик где появляется или «0» в ячеках или «1» в какой нибудь одной. Какой формулой найти номер ячейки в формате «B1» где выскочила эта «1»? У меня пока так: =ПОИСКПОЗ(1;O1:O67;0) тут выдается номер строки. Других вариантов нет?

Ну и еще — такие вложения — можно ли как то уменьшить? А то они и длиннее бывают 🙂 Имеется в виду вложение ЕСЛИ

=ЕСЛИ(‘Ввод Данных’!E55=»»;ЕСЛИ(‘Ввод Данных’!D54<>«Услуга (в руб.)»;ЕСЛИ(‘Ввод Данных’!D55<>«руб.»;ЕСЛИ(‘Ввод Данных’!D55<>«рублей»;’Ввод Данных’!B55*’Ввод Данных’!$F$35;’Ввод Данных’!B55);’Ввод Данных’!B55);’Ввод Данных’!B55);(‘Ввод Данных’!E55/100)*(ЕСЛИ(‘Ввод Данных’!D54<>«Услуга (в руб.)»;ЕСЛИ(‘Ввод Данных’!D55<>«руб.»;ЕСЛИ(‘Ввод Данных’!D55<>«рублей»;’Ввод Данных’!B55*’Ввод Данных’!$F$35;’Ввод Данных’!B55);’Ввод Данных’!B55);’Ввод Данных’!B55)))

Не совсем понятно.

Т.е. есть поле, примерно такое

И нужен адрес ячейки, в которой стоит 1? она всегда одна?

По второму вопросу — как вариант «раскидать» формулу по нескольким ячейкам, если хотите уменьшить вложенность, либо использовать именованные диапазоны, тогда вместо какого нибудь ‘Ввод Данных’!D54 будет имя диапазона, например InsData.

Да. единица может быть в любом или все равны «0» нужно точно знать ячейку

Да вложенность и удобство отслеживания переходов. Так и мучаюсь — сначала напишу в ряд (разложу) потом соединяю вместе ) 🙂 . замучился с этим ЕСЛИ — Думал может есть какая нибудь полегче формула, или с большим кол-вом параметров чтобы все в одной ЕСЛИ прописать для удобства написания программы 🙂 По диапазонам — какие это формулы назовите? почитаю про них.. Спасибо.

ну смотрите, если у вас столбец всегда один то можно просто написать =сцепить(«O»;ПОИСКПОЗ(1;O1:O67;0)) и все дела.

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

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

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

Заработало 🙂 Спасибо — интересная формула получилась, буду применять. Я правда в 2003 офисе сижу. Под новое оформление никак не перелезу половину того, что мне нужно в новом меню найти не могу 🙂 Старый наверное стал 🙂 Поэтому некоторые формулы не работают. Посмотрю где у меня в 2003 именованные диапазоны сидят и как с ними работать. Понял что это буду искать. 🙂 Еще раз спасибо.

2003 офис сильно устарел, в новых офисах заметно много разных фич добавилось, и не только в экселе. Не так давно ощутил на себе, насколько неудобно работать в офисе ниже 2010, когда мне на работе решили с какого-то перепугу понизить версию с 2010 до 2007) Быстро вернули обратно)

В отличие от ВПР (который ищет по первому столбцу), связкой индекс + поискпоз можно искать в любой строке и любом столбце. Но значения в искомом столбце (строке) должны быть уникальными, иначе ексель возьмет первое попавшееся значение. Также при использовании поискпоз можно указать не точное совпадение, а значение больше или меньше искомого.

Я, возможно, непонятно объяснил, наверное, надо пример какой-то?

ВПР ищет только по строкам, ГПР — только по столбцам. С индекс и поискпоз можно возвращать значение ячейки, находящейся на пересечении искомых значений столбца и строки.

Здравствуйте! Вижу, что Вы неплохо разбираетесь в тонкостях Excel, давно есть пара вопросов для такого специалиста)

1. Разобрались ли Вы для себя, что все-таки быстрее работает, одна составная формула или много простых, которые по-очереди друг на друга ссылаются? На форумах результаты разные, собственные эксперименты точного результата не дают.

2. Получилось ли у Вас определить максимально возможную производительность самого Excel, после которой дальнейший апгрейд ПК не ускоряет работу? Провожу вычисления, которые затрагивают много миллионов ячеек разом, для чего поставил себе 10-ти ядерный Xeon. Прирост по сравнению со средними i7 был существенный. Обрадовался результату, купил двухсокетную плату, соответственно, поставил 2 десятиядерника. Прироста производительности практически не заметил.

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

Ссылка на основную публикацию
Adblock
detector