​​Добавление нового ряда данных на диаграмму

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

В Excel существует три способа добавления нового ряда в диаграмму:
▪️ Использование диалогового окна Выбор источника данных. Активизируйте диаграмму, а затем выберите команду Работа с диаграммами => Конструктор => Данные => Выбрать данные. В открывшемся диалоговом окне Выбор источника данных щелкните на кнопке Добавить. В очередном открывшемся диалоговом окне Изменение ряда в поле Имя ряда укажите название нового ряда данных, а в поле Значения укажите диапазон ячеек, содержащий значения для нового ряда данных. Диалоговое окно Выбор источника данных также доступно из контекстного меню, которое открывается после щелчка правой кнопкой мыши на большинстве элементов диаграммы.
▪️ Перетаскивание границы диапазона. Если новый диапазон ряда данных расположен рядом с другими диапазонами рядов данных диаграммы, то сначала нужно щелкнуть на области построения диаграммы. Программа Excel выделит и очертит данные на рабочем листе. Щелкните на одном из угловых маркеров и перетащите его, чтобы включить новые данные. Этот метод работает только со встроенными диаграммами.
▪️ Копирование и вставка. Выделите нужный диапазон данных и скопируйте его с помощью комбинации клавиш <Ctrl+C> в буфер обмена. Затем активизируйте диаграмму и нажмите комбинацию клавиш <Ctrl+V>, чтобы вставить скопированные данные в диаграмму.

#ДиаграммыИВизуализация

​​Добавление подписей данных

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

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

Для того чтобы изменить тип информации, которая отображается в подписях данных, выберите подписи данных для ряда и используйте панель задач Формат подписей данных (если панель задач невидима, нажмите <Ctrl+l>). Затем для настройки подписей данных воспользуйтесь разделом Параметры подписи.
Подписи данных привязаны к рабочему листу, поэтому при изменении данных подписи также изменяются. При необходимости заменить подпись данных другим текстом выберите ее и введите новый текст.
В некоторых ситуациях вам может понадобиться использовать для какого-то ряда другие подписи данных. Для этого на панели задач Формат подписей данных установите флажок значения из ячеек (он находится в разделе Параметры подписи). После этого появится диалоговое окно Диапазон меток данных, с помощью которого вы сможете выбрать нужный диапазон, содержащий подписи точек данных.

#ДиаграммыИВизуализация

​​Обработка пропущенных данных

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

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

Возможность выбора желаемого варианта предоставляется в диалоговом окне Настройка скрытых и пустых ячеек, которое открывается после выбора команды Работа с диаграммами => Конструктор => Данные => Выбрать данные и щелчка на кнопке Скрытые и пустые ячейки в диалоговом окне Выбор источника данных. В диалоговом окне Настройка скрытых и пустых ячеек предоставляются на выбор три имеющихся в наличии метода обработки пропущенных данных. Перечисленные опции диалогового окна Настройка скрытых и пустых ячеек действуют на все ряды данных активизированной диаграммы. Поэтому нельзя применить разные опции к различным рядам данных одной и той же диаграммы.

#ДиаграммыИВизуализация

​​Добавление планок погрешностей

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

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

▪️ Фиксированное значение. Планки погрешностей имеют заданный вами фиксированный размер.
▪️ Процент. Размер планки погрешностей задается в процентах от каждого значения.
▪️ Стандартное отклонение. Размер планки погрешностей задается в единицах среднеквадратичного отклонения от среднего значения, которые Excel вычисляет для этого ряда данных.
▪️ Стандартная погрешность. Размер планки погрешностей задается в единицах среднеквадратичной ошибки, которую Excel вычисляет для этого ряда данных.
▪️ Пользовательская. Здесь можно указать размер верхней и нижней планок погрешностей. В поля ввода этой опции можно ввести значения или ссылку на диапазон, в котором содержатся значения погрешностей.

#ДиаграммыИВизуализация

​​Добавление линии тренда

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

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

#ДиаграммыИВизуализация

​​Построение комбинированных диаграмм

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

Изменим тип второго ряда с гистограммы на график и воспользуемся для этого ряда отдельной осью значений.
1. Активизируйте диаграмму и выберите Работа с диаграммами => Конструктор => Тип => Изменить тип диаграммы. На экране появится диалоговое окно Изменение типа диаграммы.
2. Перейдите на вкладку Все диаграммы.
3. В списке типов диаграмм выберите тип Комбинированная.
4. Для ряда данных Ср. темп. С задайте тип диаграммы Гистограмма с группировкой.
5. Для ряда Осадки, см задайте тип диаграммы График и установите флажок Вспомогательная ось.
6. Для вставки диаграммы на рабочий лист щелкните на кнопке ОК.

#ДиаграммыИВизуализация

​​Отображение таблицы данных

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

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

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

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

#ДиаграммыИВизуализация

​​Типы спарклайнов

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

▪️ Спарклайн "Линия". Подобен линейной диаграмме. Как дополнительная возможность графики они могут отображаться с маркером каждой точки. В первой группе отображаются линейные спарклайны с точками. Даже при беглом просмотре можно заметить выпадение из общей тенденции фондового номера W-91, в то время как все остальные фондовые номера на протяжении рассматриваемого шестимесячного периода только теряют свои активы.

▪️ Спарклайн "Гистограмма". Аналогичен столбиковой диаграмме. Во второй отображаются те же данные, что и в первой, но с использованием спарклайнов-гистограмм.

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

#ДиаграммыИВизуализация

​​Создание спарклайнов

Для создания спарклайнов выполните описанные ниже действия:

1. Выделите данные, которые будут отображаться (без заголовков столбцов). При создании нескольких спарклайнов выберите данные по всем анализируемым категориям;
2. Выделив данные, выберите на ленте группу Вставка => Спарклайны, а затем щелкните на одном из трех типов спарклайнов: График, Гистограмма или Выигрыш/проигрыш. Откроется диалоговое окно Создание спарклайнов;
3. Укажите место размещения спарклайнов. Обычно спарклайны располагаются рядом с данными, но это не является жестким требованием. В большинстве случаев для хранения спарклайнов используются пустые диапазоны. Однако Excel не предотвращает вставку спарклайнов в ячейки, которые уже содержат данные;
4. Щелкните на кнопке ОК. В указанных ячейках программа создаст графики спарклайнов заданного типа.

#ДиаграммыИВизуализация

​​Обработка скрытых или отсутствующих данных

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

Чтобы изменить указанные выше параметры настройки, принятые по умолчанию, выберите команду Работа со спарклайнами => Конструктор => Спарклайн => Изменить данные => Скрытые и пустые ячейки. В открывшемся диалоговом окне Настройка скрытых и пустых ячеек укажите требуемый способ обработки скрытых и пустых ячеек.

#ДиаграммыИВизуализация

​​Знание Excel и Google-таблиц нужно во многих профессиях. Ты бухгалтер или экономист — нужно делать расчеты и проектировать отчеты. Маркетолог — нужно планировать продажи и сравнивать эффективность рекламных кампаний. Также таблицы нужны менеджерам, предпринимателям и даже медсестрам! Так что в какую профессию ни ткни — везде пригодится. Научись работать с таблицами на базовом и продвинутом уровнях всего за 4 месяца с курсом от Skillbox!

🎈 Регистрируйся и получи скидку до 60% на курс.
Все подробности по ссылке: 👉 https://clc.to/Cj_yyA

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

Скорее изучи все возможности Excel и Google-таблиц и увеличь скорость работы!

​​Выделение определенных аспектов данных

Для выделения на спарклайнах определенных аспектов данных можно воспользоваться элементами управления группы Работа со спарклайнами => Конструктор => Показать. В этой группе на ленте находятся следующие флажки.

▪️ Максимальная точка. Выделение на спарклайне особым цветом максимальных данных.
▪️ Минимальная точка. Выделение на спарклайне особым цветом минимальных данных.
▪️ Отрицательные точки. Выделение на спарклайне особым цветом отрицательных данных.
▪️ Первая точка. Выделение на спарклайне особым цветом первой точки данных.
▪️ Последняя точка. Выделение на спарклайне особым цветом последней точки данных.
▪️ Маркеры. Вывод на спарклайн маркеров данных. Эта возможность доступна только для спарклайнов-графиков.

Цвет выделения для каждого упомянутого выше случая можно изменить с помощью команды Работа со спарклайнами => Конструктор => Стиль => Цвет маркера. К сожалению, нельзя изменить размер маркеров, отображаемых на спарклайнах-графиках.

#ДиаграммыИВизуализация

​​Автоматическое обновление спарклайнов

Если спарклайны построены на основании данных из определенного диапазона ячеек, то добавление новых данных в начало или в конец диапазона не приводит к соответствующему автоматическому обновлению спарклайна. Для обновления диапазона данных в этом случае следует воспользоваться диалоговым окном Изменение спарклайнов (оно открывается после выбора на ленте команды Работа со спарклайнами => Конструктор => Спарклайн => Изменить данные).

Но если данные спарклайна расположены в столбце таблицы (созданной с помощью команды Вставка => Таблицы => Таблица или же с использованием комбинации клавиш <Ctrl+t>), то спарклайн будет автоматически использовать данные, добавленные в конец таблицы.

#ДиаграммыИВизуализация

​​Настройка масштабирования осей спарклайнов

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

Меню, открывающееся при выборе команды Работа со спарклайнами => Конструктор => Группа => Ось позволяет обойти это правило и предоставляет в ваше распоряжение рычаги управления минимальным и максимальным значениями спарклайна или группы спарклайнов.
Для более точной настройки можно воспользоваться командой этого меню Пользовательское значение и задать минимальное и максимальное значения для группы спарклайнов.

#ДиаграммыИВизуализация

​​Смена типа спарклайна

Как уже упоминалось, программа Excel поддерживает работу с тремя типами спарклайнов: График, Гистограмма и "Выигрыш/проигрыш". После создания спарклайна или группы спарклайнов можно легко изменить их тип, выбрав спарклайн и щелкнув на ленте на одном из трех значков в группе Работа со спарклайнами => Конструктор => Тип.
Если выбранный спарклайн является частью группы, все спарклайны в этой группе изменят свой тип на новый.

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

#ДиаграммыИВизуализация

​​Изменение цвета и ширины спарклайна

После создания спарклайна изменить его цвет не составляет большого труда. Для этого предназначена группа команд на ленте Работа со спарклайнами => Конструктор => Стиль.

📌 Цвета, которые используются в графике спарклайнов, привязаны к темам. Таким образом, если вы измените тему (это можно сделать с помощью команды Разметка страницы => Темы => Темы), цвета спарклайнов изменится в соответствии с цветами новой выбранной темы.

Для линейных спарклайнов можно также дополнительно задать требуемую ширину линий - с помощью команды Работа со спарклайнами => Конструктор => Стиль => Цвет спарклайна => Толщина.

#ДиаграммыИВизуализация

Друзья!

Мы переходим к большому разделу "Импорт и предварительная подготовка данных".

В этом разделе мы рассмотрим следующие темы:
▪️ Импорт данных в Excel;
▪️ Предварительная подготовка данных;
▪️ Извлечение и слияние данных;
▪️ Обработка возможных ошибок;
▪️ Экспорт данных в другие форматы.

#ИмпортДанных #ПодготовкаДанных

​​Импорт данных из файла

Excel может открывать многие файлы непосредственно с помощью команды Файл => Открыть.
На примере показан список параметров фильтрации файлов, которые Excel позволяет задавать в диалоговом окне Открытие документа.

#ИмпортДанных

Форматы файлов рабочих листов

В дополнение к имеющимся форматам файлов (XLSX, XLSM, XLSB, XLTX, XLTM и XLAM) программа Excel 2019 может открывать файлы всех предыдущих версий.

XLS. Двоичные файлы, которые создавались в Excel 4, Excel 95, Excel 97, Excel 2000, Excel 2002 и Excel 2003.
XLM. Двоичные файлы, содержащие макросы Excel 4 (не данные).
XLT. Двоичные файлы для шаблонов Excel.
XLA. Двоичные файлы для надстроек Excel.

Программа Excel может также работать с файлами, созданными в других программах электронных таблиц и сохраненными в формате ODS (OpenDocument Spreadsheet). Файлы ODS создаются в ряде программ с открытым исходным кодом, включая Google Sheets, Apache OpenOffice, LibreOffice и несколько других.

#ИмпортДанных

Форматы текстовых файлов

Текстовый файл - это файл, содержащий текстовые символы без какого-либо форматирования. Программа Excel может открывать большинство типов текстовых файлов.
▪️ CSV. Значения, разделенные запятыми. Столбцы разделяются запятыми, а строки - символами конца строки.
▪️ ТХТ. Столбцы разделены символами табуляции, а строки - символами конца строки.
▪️ PRN. Столбцы разделяются несколькими символами пробела, а строки – символами конца строки. Программа Excel импортирует файлы этого типа в один столбец.
▪️ DIF. Формат файла, который использовался программой работы с электронными таблицами VisiCalc. Используется редко.
▪️ SYLK. Формат файла, первоначально использовавшийся программой Multiplan. Используется редко.

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

#ИмпортДанных