Вопрос или проблема
Цель: создание визуализации ряда случаев, каждый из которых состоит из двух точек (x|y), и иллюстрация разницы между этими двумя с помощью линии
Пример: таблица, которая фиксирует даты и цены, по которым были куплены и проданы ряд предметов
Рассмотрим следующие данные и способ их визуализации. У нас есть пять случаев, состоящих из предметов, которые были куплены и проданы в определенные даты и по определенным ценам.
Визуализация, которую я имею в виду, показывает эти пять пар транзакций с осью “дата” (x) и осью “цена” (y) и, что немаловажно, для иллюстрации изменения цен, линию, которая для каждого предмета соединяет точки “покупки” и “продажи”.
90% того, что я хочу, довольно просто:
Без особых усилий мне удалось заставить Excel построить “покупки” (синие) и “продажи” (красные). Однако не хватает их соединения. Вот черновой вариант того, как это должно выглядеть:
Какие необходимые шаги для достижения этого? Я полагаю, что проблема в моей текущей настройке заключается в том, что основное различие не между пятью случаями, а между категориями “покупка” и “продажа” (отсюда вводящие в заблуждение цвета) – но я не знаю, как внести необходимые изменения.
Выберите вариант “Линии с маркерами” для диаграммы разброса.
Вам нужно будет сопоставить каждую точку данных следующим образом:
Диапазон для X значений: =(Sheet1!$B$2,Sheet1!$D$2)
Диапазон для Y значений: =(Sheet1!$C$2,Sheet1!$E$2)
И так далее.
Пример результата:
Проще было бы перепривести ваши данные и снова использовать вариант “Линии с маркерами” для диаграммы разброса.
Убедитесь, что используется опция “Соединить точки данных линией”
Немного хитро, но это работает.
Упорядочите ваши данные в столбцах (дата покупки, дата продажи, #N/A, цена покупки, цена продажи, пустой). Вы можете сделать это вручную или с помощью формул OFFSET, если не хотите (или не можете) изменять исходный макет. Столбец #N/A важен.
Используйте функцию TOCOL, чтобы привести это в три столбца. Ячейка Q2 это =TOCOL(OFFSET(G$2,0,0,COUNTA(G:G)-1,3)); ячейка R2 это =TOCOL(OFFSET(J$2,0,0,COUNTA(J:J)-1,3)); ячейка S2 это =TOCOL(OFFSET(M$2,0,0,COUNTA(M:M)-1,3)).
Постройте диаграмму разброса. Я оформил линию со стрелкой и добавил метки данных, соответствующие именам в столбце S. Вам нужно, чтобы столбцы N и O были заполнены пустыми строками (=""), чтобы метки выглядели правильно. Значения #N/A означают, что линии разорваны между одной продажей и следующей покупкой.
TOCOL довольно новая; вы могли бы добиться того же в более старых версиях с некоторыми формулами OFFSET.
.
Ответ или решение
Создание визуализации цен: соединение точек на графике в Excel
Вам нужно создать визуализацию данных, чтобы показать, как менялись цены на определённые товары, когда они были куплены и проданы. Эффективный способ этого достичь — использовать диаграмму рассеяния в Excel, что позволит вам связи между ценами «покупки» и «продажи» точками на графике, соединяемыми линиями. В этой статье я подробно расскажу, как это сделать.
Шаг 1: Подготовка данных
Сначала убедимся, что ваши данные правильно организованы. Вам потребуется следующая структура:
| Дата покупки | Цена покупки | Дата продажи | Цена продажи |
|---|---|---|---|
| 01.01.2023 | 100 | 01.03.2023 | 120 |
| 02.01.2023 | 150 | 02.02.2023 | 130 |
| 03.01.2023 | 200 | 03.02.2023 | 210 |
| 04.01.2023 | 250 | 04.02.2023 | 240 |
| 05.01.2023 | 300 | 05.02.2023 | 310 |
Шаг 2: Перенос данных в график
- Выделите данные: Выделите все четыре колонки, которые вы подготовили.
- Создание графика: Перейдите на вкладку «Вставка» и выберите Диаграммы рассеяния > Точки с линиями (или Линии с маркерами).
Шаг 3: Настройка графика
Теперь вам нужно настроить график так, чтобы точки «покупки» и «продажи» выглядели различно, и связующий элемент между ними был понятен.
-
Форматирование точек: Щелкните правой кнопкой мыши на график и выберите «Форматировать ряды данных». Вы можете изменить цвет точек «покупки» на синий и точки «продажи» на красный, что сделает их сразу видимыми.
-
Добавление линий: Вам нужно будет добавить линию, чтобы соединить точки «покупки» и «продажи». Для этого:
- Перейдите в «Формат ряда данных», выберите опцию «Соединять точки линией».
- Убедитесь, что вы добавили только одну линию для каждой пары значений (купля-продажа).
Шаг 4: Настройка осей
- Настройте ось X (даты): Убедитесь, что ось X представлена корректно, чтобы по ней можно было легко прочесть даты сделок.
- Настройка оси Y (цены): Убедитесь, что цены на оси Y имеют адекватный масштаб, позволяющий отразить разницу между «ценой покупки» и «ценой продажи».
Шаг 5: Проверка и доработка
- Посмотрите на законченный график. Убедитесь, что линии соединяют правильные точки и что визуализация (цвета, форма) интуитивно понятна.
- Если необходимо, добавьте заголовок и поясняющие подписи, чтобы прояснить, что именно вы изображаете.
Дополнительные советы
- Использование формул: Если вы хотите, чтобы соединяющая линия отображалась автоматически, вы можете использовать функции Excel, такие как
OFFSET, чтобы динамически пересчитывать данные при их изменении. - Форматирование: Настройте шрифт, цвет и стиль графиков для повышения читабельности.
- Сохранение изменений: Не забудьте сохранить ваш файл после всех изменений.
Следуя этим шагам, вы сможете создать визуализацию, которая чётко демонстрирует изменения цен на ваши товары, иллюстрируя соотношение между ценами покупки и продажи. Это не только добавит профессиональный вид вашим данным, но и сделает их более доступными для анализа и понимания.