Как сделать выпадающий список таким, чтобы ... (см. Подробности)

Question

Я хочу построить котировки акций некоторых компаний против S & P500. У меня уже есть все данные о ценах, загруженные в мой лист Excel.

Я хочу создать линейный график в Excel. Одна линия исправлена, и это будет S & P500, а другая будет компанией, которую я выберу. Я знаю, как использовать проверку данных для создания раскрывающегося списка, но как я могу сделать это таким образом, чтобы при выборе компании A я видел только данные о ценах компании A, но не компании B, C и т.д. Затем, когда я выберите компанию B, я хотел бы видеть только данные о ценах компании B и данные о ценах S & P. И так далее. Линия S & P будет видна постоянно, но ценовая линия различных компаний будет отображаться только в том случае, если она выбрана.

Есть ли простой способ нормализовать все наборы данных таким образом, чтобы начальная точка всегда была равна 100?

Answer 1

В Excel это на самом деле довольно легко сделать.

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

2. Сделайте вашу первую строку данных S & P (или любой другой базовый уровень, который вы выберете). Это первая серая строка в моем образце.

3. Пропустить ряд (мы скоро к нему вернемся). Это вторая серая строка в моем образце.

4. Добавьте все свои компании в график и сравните.

5. В пропущенной строке в первом столбце (A3 в моем примере) используйте список проверки данных, чтобы вывести все названия компаний в раскрывающийся список. Затем заполните все оставшиеся столбцы VLOOKUP, чтобы перетащить данные выбранных компаний в соответствующие ячейки. Моя формула

   =VLOOKUP($A$3,$A$4:$M$7,COLUMN(B3))

6. Создайте диаграмму, используя данные из строк S & P и Target, и отформатируйте их соответствующим образом.

Теперь вы можете выбрать любую отдельную компанию и выделить ее (включая правильные метки, если хотите) по сравнению с S & P (или любым другим эталоном). Несколько мыслей:

• Именованные диапазоны значительно упростят управление.
• Мне нравится добавлять все серии и форматировать их очень светло-серый. Таким образом, помимо сравнения с базовой линией, вы также можете сравнить со всеми остальными конкурентами (как показано в моем примере).
• Если вы хотите нормализовать ваши данные, то самый простой способ - преобразовать их в точке необработанных данных и затем отобразить нормализованные значения. Это оставит вас с необработанными данными, нормализованными данными и, опять же, с именованными диапазонами диаграммы, которые помогут вам справиться со всем этим.

РЕДАКТИРОВАТЬ: Чтобы использовать ваши данные, расположенные горизонтально, вам нужно изменить две вещи в формуле из шага 5. Вы переключитесь на функцию HLOOKUP и вам нужно будет возвращать значение ROW вместо COLUMN. Новая функция будет:

=HLOOKUP($S$1,$T$1:$W$13,ROW(S2))

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

Answer 2

Ссылка на эту ссылку:
http://www.get-digital-help.com/2011/05/17/excel-charts-use-dynamic-ranges-to-add-new-values-to-both-chart-and-drop-down- список/
Это довольно подробный учебник с хорошими скриншотами.

Несколько модификаций для вашего случая,

1. Добавьте один постоянный диапазон данных для S & P и одну переменную, как указано в ссылке
2. Выберите тип графика в виде линии (довольно очевидно)

Как и в случае с бонусным вопросом,

Создайте еще одну таблицу, в которой первая ячейка для каждой компании всегда будет содержать 100 по мере необходимости.
Последующие ячейки должны содержать формулу как значение = (100/ первое значение точки данных)* соответствующее значение точки данных
Теперь это создаст другую таблицу нормализованных значений, из которой вы можете построить график.
Вы можете просто скрыть столбцы, если не хотите, чтобы эти дополнительные данные загромождали рабочий лист.