Составлено автором на основе данных как сделать

Добавил пользователь Владимир З.
Обновлено: 18.09.2024

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

Как создать диаграмму из данных на нескольких листах в Excel

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

1. Строим диаграмму на основе данных первого листа

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

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

2. Вносим данные со второго листа

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

Можно также нажать кнопку Фильтры диаграммы . Справа, в самом низу появившегося списка кликнуть Выбрать данные.

В появившемся окне Выбор источника данных перейти по ссылке Добавить.

Добавляем новый источник

Добавляем данные со второго листа. Это важный момент, поэтому будьте внимательны.

При нажатии кнопки Добавить, выплывает диалоговое окно Изменение ряда. Рядом с полем Значение нужно выбрать иконку диапазона.

Очень важно выбрать правильный диапазон, чтобы диаграмма получилась верной

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

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

Так выглядит выделение нужных данных для внесения в диаграмму

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

Убедитесь, что ссылки, указанные в строках Имя ряда и Значения указаны верно. Нажмите ОК .

Проверяем ссылки на данные, которые будут указаны в диаграмме

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

Названия рядов появятся в легенде диаграммы. Поэтому лучше давать им подходящие по смыслу имена.

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

Если у вас что-то не так, как на изображении выше, значит где-то допустили ошибку, и нужно начать сначала

3. При необходимости добавляем еще слои

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

В примере 3 ряда данных. После всех действий гистограмма выглядит так:

4. Настраиваем и улучшаем гистограмму (по желанию)

При работе в версии Excel 2013 и 2016, автоматически при создании гистограммы автоматически добавляются название и легенда. В нашем примере они не были добавлены, поэтому мы сами это сделаем.

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

Можно оставить все как есть и не добавлять дополнительные параметры

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

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

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

В нашем примере, все данные расположены в одинаковых таблицах на всех 3 листах. Если нет уверенности в том, что структура в них одинакова, лучше будет сначала составить итоговую таблицу, на основании имеющихся. Сделать это можно с помощью функции VLOOKUP или Мастера объединения таблиц .

Если бы в нашем примере все таблицы были разные, то формула была бы такой:

=VLOOKUP (A3, ‘2014’!$A$2:$B$5, 2, FALSE)

Получился бы такой результат:

С итоговой таблицей и работать проще

После этого, просто выбираем полученную таблицу. Во вкладке Вставка находим Диаграммы и выбираем необходимый вид.

Редактируем диаграммы, созданной из данных на нескольких листах

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

Изменить данные, отображаемые на самой диаграмме можно несколькими способами:

через меню Выбор источника данных ;
через Фильтры;
посредствам Формулы рядов данных.

Редактируем через меню Выбор источника данных

Чтобы открыть меню Выбор источника данных , необходимо во вкладке Конструктор нажать подменю Выбрать данные.

Для редактирования ряда необходимо:

выбрать ряд;
кликнуть на вкладку Изменить ;
изменить Значение или Имя , как делали ранее;

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

Окно редактирования данных в гистограмме

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

Изменяем ряды через Фильтр диаграмм

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

Чтобы скрыть данные, просто кликните на Фильтр и снимайте флажки со строк, которых не должно быть в графике.

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

Обратите внимание! При наведении мыши на ряд он выделяется для более понятного восприятия.

Меню изменения данных – просто снимаем флажки и изменяем периоды

Редактируем ряд при помощи формулы

Все ряды в графике определяются формулой. Например, при выборе ряда на нашей диаграмме она будет выглядеть так:

Любые данные в Excel принимают вид формулы

Любая формула имеет 4 основных составляющих:

=РЯД([Название ряда], [значения x], [значения y], номер ряда)

Наша формула в примере имеет следующее объяснение:

Название ряда (‘2013’!$B$1) взято из ячейки B1 на листе 2013 .
Значение строк (‘2013’!$A$2:$A$5) взяты из ячеек A2:A5 на листе 2013 .
Значение столбцов (‘2013’!$B$2:$B$5) взяты из ячеек B2:B5 на листе 2013 .
Число (1) означает то, что выделенный ряд имеет первое место в графике.

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

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

Источник: Составлено автором на основе расчетов

Если проанализировать данные таблицы, можно сделать следующие выводы:

o показатели эффективности проекта, где риски ещё не учитываются, находятся в приемлемых пределах, а, именно, показатели NPV при 10% и 15% дисконта имеют положительные значения. Внутренняя норма рентабельности составляет 39%, величина достаточно высокая. Показатели PI больше единицы при ставках дисконта 10% и 15%, что говорит о приемлемости проекта. Сроки окупаемости вложенных инвестиций 6,1 год без дисконта и 6,5 лет с учетом 10% дисконта, данный показатель ниже по сравнению с ситуациями, рассчитанными с учетом рисков. Точка безубыточности составляет 3,09 млн. тонн, т.е. при данном объеме производства проект не приносит ни прибыль, ни убыток.

o изменение показателей при различных случаях рисковых ситуаций нагляднее рассмотреть в графическом виде (рисунок 3,4)

Рис. 3 Изменение показателя IRR от влияния различных факторов

Источник: Составлено автором

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

Рис.4 Изменение показателя NPV от влияния различных факторов

Источник: Составлено автором

Экономический показатель NPV в целом чувствителен к разным видам рисков в большей или меньшей степени. Свое минимальное значение он достигает при снижении объема добычи, уменьшаясь в 1,7 раз по сравнению с ситуацией без риска. Стоит отметить, что изменение показателя NPV при ставках дисконта 10% и 15% при различных факторах происходит равномерно.

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

Срок окупаемости проекта увеличивается под влиянием данных факторов, достигая своего максимального значения (7,2 лет) при снижении объемов добычи. Аналогичная ситуация и с точкой безубыточности.

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

При самом пессимистичном варианте разработки проекта одновременное влияние всех факторов приводит к тому, что проект в целом становится неэффективным в силу того, что основные показатели выходят за пределы приемлемости. Показатель NPV при 10% дисконта снижается в 11 раз по сравнению с ситуацией, не учитывающей риски, и меньше в среднем в 8,5 раз ситуаций с различными видами рисков. При 15% дисконта данный показатель вовсе становится отрицательным, что говорит о неэффективности данных капитальных вложений. Показатель IRR равен 12%, т.е. практически сравнялся со ставкой дисконта. Индекс доходности инвестиций с учетом дисконта 10% составляет 1,07, что является пограничным значением, ниже которого реализация проекта нецелесообразна. При дисконте 15% данный показатель и достигает неприемлемого значения (0,91). Срок окупаемости с учетом всех рисков вырос до 10 лет (без дисконта) и 15 лет (дисконт 10%). Точка безубыточности увеличивается до 4,06 млн. тонн и это при том, что объем добычи падает.

Государственное регулирование и поддержка малого предпринимательства в Республике Беларусь
Предпринимательство - одна из важнейших составляющих современной экономики. В странах рыночной экономики предпринимательство получило широкое распространение и составляет подавляющую часть среди всех форм организаций. За последние десять лет в Беларуси появилось большое количество предпринимате .

На мой взгляд, задание стало сложнее. Но выпускники прошлого года считают, что оно стало проще и "дороже".

В чём разница?

В прошлые годы школьник на ЕГЭ выбирал верный ответ из пяти предложенных (см. рис. 2). Для этого достаточно знать теорию.

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

Сравните формулировки заданий:

Судя по результатам ЕГЭ (см. рис. 5) прошлых лет, старый формат задания не вызывал особых затруднений:

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

А сложности были?

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

Во-первых, разделение между факторами спроса и предложения в определенной мере является условным.

Во-вторых, ряд факторов может влиять как на спрос, так и на предложение.

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

"Графики: как измерить спрос?"
"Графики: теория легче лёгкого!"

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

Как изменился алгоритм решения?

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

Расчёт себестоимости сырья и основных материалов одной буханки хлеба дарницкого на IV квартал 2007 года*.

* Источник: составлено автором самостоятельно.

Расчёт затрат на электрическую энергию

Зэ =12,8*0,85*1,52/1600=0,0103 руб. /кВт*ч

Расходы на оплату труда производственных рабочих

Зпл =8*12*23,1*1,4/16000 = 0, 194 руб/кг.

Отчисления на социальные нужды

Зсоц = 0,39 х Зпл = 0,39 х 0, 194= 0,08 руб/кг;

Расходы на содержание и эксплуатацию оборудования

А =320500*0,12/88960000 =0,00041 руб;

Находим себестоимость единицы продукции на существующем оборудовании:

СеI = 6,67+0,0103+0, 194+0,08+0,00041=6,95 руб;

Седарн. = 5,72+0,0103+0, 194+0,08+0,00041=6 руб.

Расходы на управление и организацию труда на предприятии составляют 15% от себестоимости единицы продукции.

ЗоргI = 0,15*6,95 =1,043 руб.

Зоргдарн=.0,15*6 =0,9 руб.

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

СпрI =1,043 +6,95 =8

СкомI= 0,018*8 = 0,144 руб,

Скомдарн=0,018*6,9 = 0,124 руб

Найдём полную себестоимость единицы продукции, данные сведём в таблицу 2.10

Расчет полной себестоимости единицы продукции*

Еще статьи по экономике

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

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

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

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

Попробуем разобраться на конкретном примере: предположим, некоторой организации требуется составить список на новогодние подарки детям сотрудников. У нас есть исходная таблица из бухгалтерии, а нужно создать новую таблицу, в которой будут требуемые данные, но не будет лишних (которые есть в исходной). То есть с помощью функции ВПР найдем в первом столбце ТАБЛИЦЫ 1 нужную фамилию, выберем в нужной строке требуемое значение (количество детей) и заполним этим значением третий столбец ТАБЛИЦЫ 2.

Итак, нам понадобятся две таблицы. Одна — справочная (обычно они уже сформированы отделом кадров или бухгалтерией), где собрана основная информация о сотрудниках. Назовем ее ТАБЛИЦА 1.

Первый столбец этой таблицы (в нашем случае ФИО) должен быть отсортирован по возрастанию.

ТАБЛИЦА 2 в итоге работы функции ВПР должна содержать результат — список сотрудников с количеством детей.

В ТАБЛИЦЕ 2 фамилии сотрудников могут располагаться в любом порядке. Например, в соответствии стабельными номерами как в данном примере.

Функция ВПР будет располагаться в третьем столбце, который пока пустой.

Шаг 1

Щелчком выберем первую ячейку третьего столбца D4.
Щелкнем кнопку Мастера функций

Щелчком выберем функцию ВПР из списка.

Шаг 2

Следующий шаг — заполнение полей в окне функции ВПР:

В поле появится имя этой ячейки С4.

Чтобы временно скрыть/отобразить окно Аргументы функции, щелкнуть кнопку.

Чтобы заполнить поле Таблица, надо выделить данные Таблицы 1 (без шапки). В данном примере это ячейки F5:K10.

В поле Номер столбца указываем порядковый номер нужного нам столбца ТАБЛИЦЫ 1.

В поле Интервальный просмотр ставится 1 (приблизительное совпадение) или 0 (точное совпадение). В данном простом случае можно выбрать любое.

Нажимаем ОК — формула готова.

ВАЖНО: необходимо адрес таблицы сделать абсолютным.

Для этого выделяем в строке ввода формул F5:K10 и нажимаем F4 на клавиатуре.

Первая ячейка столбца с количеством детей заполнена.

Шаг 3

Осталось растиражировать формулу по всему столбцу. Для этого выделяем ячейку D4 и протащим мышкой маленький угловой маркер вниз до D9.

После этого третий столбец ТАБЛИЦЫ 2 заполнится данными из ТАБЛИЦЫ 1 в точном соответствии с формулой.

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

Понравился материал?
Хотите прочитать позже?
Сохраните на своей стене и
поделитесь с друзьями

Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст

Ошибка в тексте? Мы очень сожалеем,
что допустили ее. Пожалуйста, выделите ее
и нажмите на клавиатуре CTRL + ENTER.

Кстати, такая возможность есть
на всех страницах нашего сайта

0 Спам

1 Леночка555 • 17:10, 27.06.2019

Отправляя материал на сайт, автор безвозмездно, без требования авторского вознаграждения, передает редакции права на использование материалов в коммерческих или некоммерческих целях, в частности, право на воспроизведение, публичный показ, перевод и переработку произведения, доведение до всеобщего сведения — в соотв. с ГК РФ. (ст. 1270 и др.). См. также Правила публикации конкретного типа материала. Мнение редакции может не совпадать с точкой зрения авторов.

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

О работе с сайтом

Мы используем cookie.

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

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

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

Читайте также: