Nano Hash - криптовалюты, майнинг, программирование

Создание многолинейной диаграммы в Excel — с расчетами?

Уважаемые участники StackOverflow,

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

У меня есть следующая таблица данных:

Необработанные данные диаграммы

И нарисованные от руки примеры того, что я хотел бы сделать:

Я хотел бы создать диаграмму, которая отображает рассчитанные значения для всех кодов (левый столбец изображения необработанных данных диаграммы), которые имеют одинаковые первые четыре цифры. Я хотел бы отобразить точку данных для каждой даты, которая суммирует все значения в столбце «А». Я бы сделал то же самое с колонкой «B». Однако точка данных "P" проблематична, потому что в ней будет использоваться расчет с использованием каждого кода, начинающегося с одних и тех же первых четырех цифр. Я имею в виду «A», «B» и «P», которые находятся под каждой датой в верхней части моей электронной таблицы, как на рис. Raw Chart Data.

«P» представляет собой значение в % и имеет соответствующее числовое значение «A» и «B» для заданного одиночного кода. Я бы умножил P на B для каждого отдельного кода с одинаковыми первыми четырьмя цифрами, а затем суммировал бы каждый из этих результатов. Затем я разделил бы эту сумму на сумму всех значений «В» для тех же отдельных кодов.

Я хочу создать одну трехстрочную диаграмму для каждой уникальной группы кодов, начинающихся с одних и тех же четырех цифр. Как было предложено в комментариях, имеет смысл использовать значения «P» в качестве вторичной оси и использовать основные оси для «A» и «B».

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

Есть ли способ выполнить такие вычисления для диаграммы в Excel? Я думаю, что достаточно знаком с формулами VBA и Excel, чтобы сделать это, но, думаю, я узнаю.

Спасибо вам всем,

Ангус

charts excel pivot-table excel-2013
25.02.2018

  • Я бы даже согласился на встроенный рисунок диаграммы на этом. Я немного запутался. 25.02.2018
  • Например, для данной даты у вас есть два столбца B (я предполагаю, что вы имеете в виду букву под датой). Единственное, что отличает их, это, по-видимому, цвет (синий/желтый) и порядок их появления. Вы не указываете, какие из них будут использоваться для расчета P, и не указываете значения для A, которые позволили бы мне сделать это самостоятельно. См. Как создать минимальный, полный и проверяемый пример. Я думаю, что этот вопрос нуждается в прояснении, прежде чем можно будет дать достойный ответ в любом случае. 25.02.2018
  • FWIW ... Вы, вероятно, нарисуете P на вторичной оси из-за того, что это процент и шкала, отличная от A и B. К чему вы, кажется, приходите позже в вопросе, но до этого у вас есть запутанное утверждение, ИМХО , of ... Вертикальная левая ось будет иметь B вверху, затем A, затем P. Я думаю, возможно, вы имеете в виду боковую/горизонтальную гистограмму - в этом случае вам понадобятся отдельные диаграммы и фальсифицируйте их как часть того же графика. 25.02.2018
  • Извините - макрос, который я запустил, изменил имена столбцов, так что было два столбца B - отредактировано. Вы правы, то, как я это сформулировал, теперь, когда я прочитал ваш комментарий, сбивает с толку. Я собираюсь нарисовать диаграмму, которую я себе представляю, и опубликовать ее, как вы предлагаете — ха-ха, это хорошая идея. 26.02.2018
  • Спасибо QHarr - я отредактировал свой пост и, надеюсь, прояснил несколько вещей. 26.02.2018
  • Является ли p фиксированным процентом? Зачем тогда рисовать на графике? Рабочий пример P - это значение в % ....... чтобы показать, как вы получаете значения, которые вы действительно хотите отобразить на диаграмме, поможет ряд %. 26.02.2018
  • Является ли первое значение p на вашем графике равным 25,35%? 26.02.2018
  • Точно. Я разработаю пример, используя два значения P для кода, начинающегося с 0905: 30% x 7777 + 20% x 6750 = 2333,1 + 1350 = 3683,1 Затем: 3683,1 / (7777 + 6750) = 25,35% Что я диаграмма здесь представляет собой измеренный процент прогресса, достигнутого в задаче, по сравнению с количеством часов, которые потребовались для выполнения этого процента прогресса. Итак, я хотел бы сравнить эти рассчитанные процентные значения с процентным соотношением часов/бюджета (значения A/значения B). Значения P фиксированы, но они являются измеренными значениями, поэтому мне нужно сохранить их относительный вес. 26.02.2018
  • Ага. Я справился! У меня есть решение, над которым я работаю, но не самое красивое. Основная проблема заключается в том, что вам действительно нужно переворачивать свои данные. Бит формул не проблема. Есть ли фиксированное количество столбцов или вы просто добавляете? Можно ли изменить формат макета? 26.02.2018
  • Спасибо за помощь QHarr! Мне разрешено что-то менять, чтобы это работало. Возможно, что в проекте даты будут продлены (как и в большинстве проектов), но я установил формулу, которая автоматически заполняет столбцы датами, поэтому, если они будут добавлены, у меня будут месяцы, чтобы понять это. , хаха. Для перелистывания данных: я предполагаю, что у меня должны быть строки P, A и B для каждой даты слева и коды вверху? На самом деле... это уже начинает проясняться... 26.02.2018
  • Что ж, решение, которое я собираюсь опубликовать, использует формат, который у вас есть в настоящее время. Не плачь! Но он включает в себя многие элементы, которые вам понадобятся в будущем. 26.02.2018

Ответы:


1

2 комплект листа.

1) Лист 1 называется DataSheet и отражает ваш лист ввода данных.

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

Вот как это выглядит в состоянии «после»:

2 листа

Таблица данных:

В ячейку B2 в dataSheet вы можете ввести 4-значный код для построения диаграммы.

Обратите внимание на начальный ', чтобы сохранить 0 в начале.

выбор 4-значного кода

Формулы в строке 4:

A) F4 для p:

=IFERROR(SUMPRODUCT(OFFSET($A$6,0,SUM(COLUMN(H1)-1),COUNTA(Codes),1),--(LEFT(Codes,4)=$B$2),OFFSET($A$6,0,SUM(COLUMN(F1)-1),COUNTA(Codes),1))/H4,"")

Это относится к расчету p

B) G4 для A:

=SUMPRODUCT(OFFSET($A$6,0,SUM(COLUMN(G1)-1),COUNTA(Codes),1),--(LEFT(Codes,4)=$B$2))

Это суммирует для заданного 4-значного начального кода столбец G.

C) H4 для B:

=SUMPRODUCT(OFFSET($A$6,0,SUM(COLUMN(H1)-1),COUNTA(Codes),1),--(LEFT(Codes,4)=$B$2))

Это суммирует для заданного 4-значного начального кода столбец H.

Вы можете скопировать и вставить их в блоки по 3, чтобы установить формулы для будущих диапазонов, то есть выберите F4: H4 и скопируйте в I4: K4 и т. д., и формулы будут работать для нового диапазона. Извините, я еще не настроил их, поэтому могу как-то затянуть.

Приветствуйте @Tom Sharpe и @shrivallabha.redij за решение загадки sumproduct, здесь, что является частью решения.

Таблица данных:

A) Формула в B4:

=DataSheet!F2

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

B) Формула в B5 для перетаскивания столбца B вниз:

=IF(ROWS($B$4:$B5) <=INT(COLUMNS(dates)/3),$B$4+ROWS($B$5:$B5)*7,NA())

Это обеспечивает еженедельное увеличение даты (т. е. плюс 7 дней) в течение необходимого количества недель. Поскольку каждая дата повторяется 3 раза, количество столбцов в динамическом диапазоне dates, который охватывает все даты в строке 2 таблицы данных, делится на 3, чтобы получить количество недель, которое должно быть фактически нанесено на график. Если это число превышено, выводится #N/A, поскольку оно не будет нанесено на график.

C) Формула в C4, которая перетаскивается вниз по столбцу C и через столько столбцов, сколько вы используете, то есть в столбец E в показанном примере:

 =IFNA(INDEX(SOquestions.xlsb!dataRange,MATCH($B4,dates,0)+COLUMNS($E:E)-1),NA())

Это извлекает значения p, A and B для даты столбца B. dataRange — это динамический диапазон, в котором хранятся ваши исходные расчеты в dataSheet. Если вы откроете Диспетчер имен, выберите dataRange, а затем поместите курсор в диапазон ссылок, динамический диапазон, на который он ссылается, будет выделен «марширующими муравьями»:

Диапазон данных

Если столбец даты (B) имеет значение #N/A на листе chartData, то соответствующие значения p,A и B по умолчанию равны #N/A, поэтому они не отображаются.

Построение диаграммы:

Сама диаграмма представляет собой комбинированную диаграмму, которая имеет 4 динамических ряда.

Серии диаграмм

Ряды добавляются обычным способом, но вы ссылаетесь на динамический ряд, которому предшествует Sheetname! ; как на картинке. Вы используете pSeries,aSeries, bSeries и dateSeries для сюжета.

Ось диаграммы должна быть настроена для обработки еженедельных точек данных.

Ось X

Настройка комбо-диаграммы:

Комбинированная диаграмма

Примечание p нанесено на вспомогательную ось X и отформатировано в процентах.

Процесс обновления:

1) Добавить новые строки в таблицу данных

Добавление новых строк

2) Скопируйте блок из трех столбцов в следующий столбец. Обратите внимание, что в первом столбце блока есть +7 для увеличения недели.

Копирование формулы и блока дат

3) Перейдите к ChartData и перетащите формулы вниз в столбцах B:E.

обновление таблицы данных диаграммы

4) Выберите интересующий вас код, в dataSheet ячейке B2 и все обновится.

Требуются именованные диапазоны:

Используются динамические именованные диапазоны. Ctrl и F3 вызовут Диспетчер имен, чтобы вы могли добавить их.

Именованные диапазоны

Формулы для динамических именованных диапазонов:

Коды

=OFFSET(DataSheet!$A$6,0,0,COUNTA(DataSheet!$A$6:$A$1048576),1)

диапазон данных

=OFFSET(DataSheet!$F$4,0,0,1,COUNT(DataSheet!$F$2:$XFD$2))

даты

=OFFSET(DataSheet!$F$2,0,0,1,COUNT(DataSheet!$F$2:$XFD$2))

pСерия

=OFFSET(DataSheet!$P$11,0,0,COUNT(DataSheet!$B$11:$B$1048576),1)

набор

=OFFSET(ChartData!$D$4,0,0,COUNT(ChartData!$B$11:$B$1048576),1)

bСерия

=OFFSET(ChartData!$E$4,0,0,COUNT(ChartData!$B$11:$B$1048576),1)

ДатаСерия:

=OFFSET(ChartData!$B$4,0,0,COUNT(ChartData!$B$11:$B$1048576),1)

Примечание. Для более ранних версий Excel необходимо настроить конец строки и столбца.

End Row 1048576 становится 65536 End Column XFD становится IV

Дополнительная информация о версии:

http://www.excelfunctions.net/Excel-2003-vs-Excel-2007.html

26.02.2018
  • Я переименовал файл для загрузки, преобразованный в xlsx, поэтому он больше не имеет такого же имени, как показано на одном из изображений ответов. 26.02.2018
  • Карр - я задыхаюсь. Ваш ответ так обстоятелен и ясен. Я очень впечатлен вашими знаниями и навыками, а также действительно благодарен за время, которое вы потратили, чтобы помочь мне. Это определенно решает мою проблему. Я прочитал все, и я уверен, что смогу реализовать это. Спасибо, что продемонстрировали мне, какой отличный ответ! 26.02.2018
  • Было приятно, что вы нашли время, чтобы изменить свой вопрос и на самом деле включили несколько рисунков от руки :-) 26.02.2018
    • Новые материалы
    Кластеризация: более глубокий взгляд
    Кластеризация — это метод обучения без учителя, в котором мы пытаемся найти группы в наборе данных на основе некоторых известных или неизвестных свойств, которые могут существовать. Независимо от..
    Как написать эффективное резюме
    Предложения по дизайну и макету, чтобы представить себя профессионально Вам не позвонили на собеседование после того, как вы несколько раз подали заявку на работу своей мечты? У вас может..
    Частный метод Python: улучшение инкапсуляции и безопасности
    Введение Python — универсальный и мощный язык программирования, известный своей простотой и удобством использования. Одной из ключевых особенностей, отличающих Python от других языков, является..
    Как я автоматизирую тестирование с помощью Jest
    Шутка для победы, когда дело касается автоматизации тестирования Одной очень важной частью разработки программного обеспечения является автоматизация тестирования, поскольку она создает..
    Работа с векторными символическими архитектурами, часть 4 (искусственный интеллект)
    Hyperseed: неконтролируемое обучение с векторными символическими архитектурами (arXiv) Автор: Евгений Осипов , Сачин Кахавала , Диланта Хапутантри , Тимал Кемпития , Дасвин Де Сильва ,..
    Понимание расстояния Вассерштейна: мощная метрика в машинном обучении
    В обширной области машинного обучения часто возникает необходимость сравнивать и измерять различия между распределениями вероятностей. Традиционные метрики расстояния, такие как евклидово..
    Обеспечение масштабируемости LLM: облачный анализ с помощью AWS Fargate и Copilot
    В динамичной области искусственного интеллекта все большее распространение получают модели больших языков (LLM). Они жизненно важны для различных приложений, таких как интеллектуальные..