Закрепить диаграмму в excel

Обновлено: 04.07.2024

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

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Добавить полосу прокрутки на диаграмму в Excel

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

Предположим, у вас есть следующий диапазон данных, в котором вы хотите создать диаграмму полосы прокрутки на листе:

1. Во-первых, вы можете вставить диаграмму с указанными выше данными, выбрав данные и нажав Вставить > Колонка > Кластерный столбец, (при необходимости вы можете вставить другую столбчатую или линейную диаграмму).

2. И столбчатая диаграмма вставляется в ваш рабочий лист следующим образом:

3. Затем вы можете вставить полосу прокрутки в этот рабочий лист, щелкнув разработчик > Вставить > Полоса прокрутки, см. снимок экрана:

Чаевые: Если вкладка Разработчик не отображается на ленте, вы можете нажать Файл > Опция > Настроить ленту и проверить разработчик в правом разделе, чтобы отобразить разработчик вкладка на вкладке.

4. Затем перетащите мышь, чтобы нарисовать полосу прокрутки, и щелкните правой кнопкой мыши, чтобы выбрать Управление форматом, см. снимок экрана:

5. В Управление форматом диалоговое окно, нажмите Control вкладку, а затем укажите Минимальное значение и Максимальное значение ваших данных по мере необходимости, затем нажмите кнопку, чтобы выбрать пустую ячейку, которую вы хотите связать с полосой прокрутки. Смотрите скриншот:

6. Затем нажмите OK чтобы закрыть это диалоговое окно, и выберите ячейку ссылки, которую вы только что указали, чтобы создать имена диапазонов, которые вы будете использовать через некоторое время. Далее нажмите Формулы > Определить имя, В Новое имя диалоговом окне введите имя для именованного диапазона (столбец A), который вы хотите использовать, в этом примере я введу Имя, затем введите эту формулу = СМЕЩЕНИЕ (Лист1! $ A $ 2 . Лист1! $ N $ 5) в поле Ссылается на, (Sheet1 лист, который вы применяете; A2 это ячейка, которая содержит первые данные в столбце A без заголовка; N5 - это связанная ячейка, которую вы указали на шаге 5, вы можете изменить ее по своему усмотрению.). Смотрите скриншот:

7, Затем нажмите OK, продолжайте нажимать Формулы > Определить имя чтобы определить имя для другого диапазона. Столбец B, как в шаге 6. В этом примере я введу следующее:

Имя: математика; (определенное имя для столбца B)
Относится к: = СМЕЩЕНИЕ (Лист1! $ B $ 2 . Лист1! $ N $ 5) (Sheet1 лист, который вы применяете; B2 это ячейка, содержащая первые данные в столбце B без заголовка; N5 - это связанная ячейка, которую вы указали на шаге 5, вы можете изменить ее по своему усмотрению.)

8. Затем нажмите OK , чтобы закрыть это диалоговое окно, и имена диапазонов для диаграммы были успешно созданы.

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

10. В Выберите источник данных диалоговое окно, нажмите математика а затем нажмите кнопку Редактировать кнопка, в выдвинутой Редактировать серию диалога под Название серии, нажмите кнопку, чтобы выбрать ячейку B1, и введите это = Sheet1! Математика до Значения серии поле, (Sheet1 рабочий лист, который вы применяете, и математика это имя диапазона, которое вы создали для столбца B), см. снимки экрана:

11. Затем нажмите OK чтобы вернуться к прежнему диалогу, а в Выберите источник данных диалоговое окно, нажмите Редактировать кнопка под HЯрлыки горизонтальных (категорий) осей, В Ярлыки осей диалог, введите = Лист1! Имя в поле Диапазон меток оси. (Sheet1 рабочий лист, который вы применяете, и Имя - это имя диапазона, которое вы создали для столбца A). Смотрите скриншот:

12, Затем нажмите OK > OK Чтобы закрыть диалоговые окна, вы добавили полосу прокрутки к диаграмме. Когда вы перетаскиваете полосу прокрутки, данные будут все больше отображаться на диаграмме. Смотрите скриншоты:

13. Наконец, если вы хотите объединить полосу прокрутки и диаграмму, вы можете выбрать и перетащить полосу прокрутки на диаграмму, а затем удерживать Ctrl чтобы выбрать чат и полосу прокрутки одновременно, а затем щелкните полосу прокрутки правой кнопкой мыши, выберите группы > группы из контекстного меню, и эти два объекта объединяются.

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

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

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

Затем выберите свою связанную ячейку, которую вы создали, и определите имена диапазонов для диаграммы в Новое имя диалоговом окне укажите имя и введите эту формулу =OFFSET(Sheet1!$A$1,Sheet1!$N$1,0,Sheet1!$N$2,1) в Относится к текстовое окно, (A1 это первая ячейка ваших данных, N1 это связанная ячейка, которую вы создали, и N2 это ячейка указанного вами отображаемого номера для диаграммы)

И продолжайте создавать имя диапазона для данных другого столбца в Новое имя диалоговом окне введите имя диапазона для столбца B и введите эту формулу =OFFSET(Sheet1!$A$1,Sheet1!$N$1,1,Sheet1!$N$2,1) в Относится к поле, см. снимок экрана:

Затем вам нужно связать полосу прокрутки и диаграмму в соответствии с шагами 9–12 выше. И вы получите следующие результаты: когда вы перетаскиваете полосу прокрутки, каждые 10 непрерывных оценок отображаются в диаграмме.

Как правило, диаграммы Excel используют данные, хранящиеся в диапазоне ячеек. При изменении данных диаграмма обновляется автоматически. Обычно это хорошо. Но иногда хочется «отсоединить» диаграмму от диапазона данных для получения статического результата — своеобразного снимка диаграммы, который уже никогда не изменится.

Например, если вы строите график по данным, полученным от различных сценариев «что — если», то можете сохранить диаграмму как основание для ее сравнения с другими сценариями. Вы можете закрепить диаграммы двумя способами:

преобразовать диаграмму в рисунок;
преобразовать диапазон ссылок в массивы.

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

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

Создайте диаграмму привычным образом и отформатируйте ее так, как вы хотите.
Щелкните на диаграмме, чтобы активизировать ее.
Выберите команду Главная ► Буфер обмена ► Копировать ► Копировать как рисунок.
В диалоговом окне Копировать рисунок оставьте настройки по умолчанию и нажмите кнопку ОК.
Щелкните на любой ячейке, отменяя выбор графика.
Нажмите Ctrl+V, чтобы вставить изображение в ячейку, выбранную в шаге 5.

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

Преобразование диапазона ссылок в массивы

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

Активизируйте свою диаграмму.
Выберите ряд диаграммы. Строка формул покажет формулу РЯД для выбранного ряда данных.
Щелкните на строке формул.
Нажмите F9, а затем клавишу Enter.

Повторите эти действия для каждого ряда диаграммы. На рис. 148.1 показана круговая диаграмма, которая была отделена от своей области данных. Обратите внимание, что строка формул отображает массивы, а не
ссылки на диапазон. Исходная формула РЯД была такой:
=РЯД(;Лист1!$A$1:$A$6;Лист1!$B$1:$B$6;1) .

Excel ограничивает длину формулы РЯД. Таким образом, этот метод может не работать, если ряд состоит из большого количества точек данных.

У меня в таблице есть заголовок на 2 строки, и он закреплен.
График хочется закрепить именно от вертикального прокручивания.
Хочется сделать его большим и наглядным, на пол экрана.

Не хочется закреплять очень большую горизонтальную область.
Это плохо отразится на восприятии таблицы.

Неужели нельзя найти решение, которое удовлетворяло бы всем моим прихотям?

Не хочется закреплять очень большую горизонтальную область.
Это плохо отразится на восприятии таблицы.

Неужели нельзя найти решение, которое удовлетворяло бы всем моим прихотям? Cage

Не хочется закреплять очень большую горизонтальную область.
Это плохо отразится на восприятии таблицы.

Неужели нельзя найти решение, которое удовлетворяло бы всем моим прихотям? Автор - Cage
Дата добавления - 26.01.2013 в 02:01

Можно, но не думаю что штатными средствами Автор - Serge_007
Дата добавления - 26.01.2013 в 02:05 прикрутить в форму и пусть висит, только я не знаю как )
[admin]Хороший совет, дельный :)[/admin] прикрутить в форму и пусть висит, только я не знаю как )
[admin]Хороший совет, дельный :)[/admin] wild_pig

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

Еще один вариант:
Вид -> Новое окно
Вид -> Упорядочить все -> Расположить рядом

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

Еще один вариант:
Вид -> Новое окно
Вид -> Упорядочить все -> Расположить рядом

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

Вот, например, данные по курсам валют за несколько месяцев:

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

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

Выглядеть это может примерно так:

Нравится? Тогда поехали.

Шаг 1. Создаем дополнительную таблицу для диаграммы

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

В Excel 2007/2010 к созданным диапазонам можно применить команду Форматировать как таблицу ( Format as Table) с вкладки Главная ( Home) :

Это даст нам следующие преимущества:

Любые формулы в таких таблицах автоматически транслируются на весь столбец – не надо «тянуть» их вручную до конца таблицы
При дописывании к таблице новых строк в будущем (новых дат и курсов) – размеры таблицы увеличиваются автоматически, включая корректировку диапазонов в диаграммах, ссылках на эту таблицу в других формулах и т.д.
Таблица быстро получает красивое форматирование (чересстрочную заливку и т.д.)
Каждая таблица получает собственное имя (в нашем случае – Таблица1 и Таблица2), которое можно затем использовать в формулах.

Подробнее про преимущества использования подобных Таблиц можно почитать тут.

Шаг 2. Добавляем флажки (checkboxes) для валют

В Excel 2007/2010 для этого необходимо отобразить вкладку Разработчик ( Developer) , а в Excel 2003 и более старших версиях – панель инструментов Формы ( Forms) . Для этого:

В Excel 2003: выберите в меню Вид – Панели инструментов – Формы (View –Toolbars –Forms)
В Excel 2007: нажать кнопку Офис – ПараметрыExcel – Отобразить вкладку Разработчик на ленте (OfficeButton –Exceloptions –ShowDeveloperTabintheRibbon)
В Excel 2010: Файл – Параметры – Настройка ленты – включить флаг Разрабочик (File –Options –CustomizeRibbon –Developer)

На появившейся панели инструментов или вкладке Разработчик ( Developer) в раскрывающемся списке Вставить ( Insert) выбираем инструмент Флажок ( Checkbox) и рисуем два флажка-галочки для включения-выключения каждой из валют:

Текст флажков можно поменять, щелкнув по ним правой кнопкой мыши и выбрав команду Изменить текст ( Edit text) .

Теперь привяжем наши флажки к любым ячейкам для определения того, включен флажок или нет (в нашем примере это две желтых ячейки в верхней части дополнительной таблицы). Для этого щелкните правой кнопкой мыши по очереди по каждому добавленному флажку и выберите команду Формат объекта ( Format Control) , а затем в открывшемся окне задайте Связь с ячейкой ( Cell link) .

Шаг 3. Транслируем данные в дополнительную таблицу

Теперь заполним дополнительную таблицу формулой, которая будет транслировать исходные данные из основной таблицы, если соответствующий флажок валюты включен и связанная ячейка содержит слово ИСТИНА (TRUE):

Заметьте, что при использовании команды Форматировать как таблицу ( Format as Table) на первом шаге, формула имеет использует имя таблицы и название колонки. В случае обычного диапазона, формула будет более привычного вида:

Обратите внимание на частичное закрепление ссылки на желтую ячейку (F$1), т.к. она должна смещаться вправо, но не должна – вниз, при копировании формулы на весь диапазон.

Шаг 4. Создаем полосы прокрутки для оси времени и масштабирования

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

Полосу прокрутки ( Scroll bar) берем там же, где и флажки – на панели инструментов Формы ( Forms) или на вкладке Разработчик ( Developer) :

Рисуем на листе в любом подходящем месте одну за другой две полосы – для сдвига по времени и масштаба:

Каждую полосу прокрутки надо связать со своей ячейкой (синяя и зеленая ячейки на рисунке), куда будет выводиться числовое значение положения ползунка. Его мы потом будем использовать для определения масштаба и сдвига. Для этого щелкните правой кнопкой мыши по нарисованной полосе и выберите в контекстном меню команду Формат объекта ( Format control) . В открывшемся окне можно задать связанную ячейку и минимум-максимум, в пределах которых будет гулять ползунок:

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

Шаг 5. Создаем динамический именованный диапазон

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

Отступом от начала таблицы вниз на заданное количество строк, т.е. отступом по временной шкале прошлое-будущее (синяя ячейка)
Количеством ячеек по высоте, т.е. масштабом (зеленая ячейка)

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

Для создания такого диапазона будем использовать функцию СМЕЩ ( OFFSET) из категории Ссылки и массивы ( Lookup and Reference) - эта функция умеет создавать ссылку на диапазон заданного размера в заданном месте листа и имеет следующие аргументы:

В качестве точки отсчета берется некая стартовая ячейка, затем задается смещение относительно нее на заданное количество строк вниз и столбцов вправо. Последние два аргумента этой функции – высота и ширина нужного нам диапазона. Так, например, если бы мы хотели иметь ссылку на диапазон данных с курсами за 5 дней, начиная с 4 января, то можно было бы использовать нашу функцию СМЕЩ со следующими аргументами:

Хитрость в том, что константы в этой формуле можно заменить на ссылки на ячейки с переменным содержимым – в нашем случае, на синюю и зеленую ячейки. Сделать это можно, создав динамический именованный диапазон с функцией СМЕЩ ( OFFSET) . Для этого:

В Excel 2007/2010 нажмите кнопку Диспетчер имен (NameManager) на вкладке Формулы (Formulas)
В Excel 2003 и старше – выберите в меню Вставка– Имя– Присвоить(Insert – Name – Define)

Для создания нового именованного диапазона нужно нажать кнопку Создать ( Create) и ввести имя диапазона и ссылку на ячейки в открывшемся окне.

Сначала создадим два простых статических именованных диапазона с именами, например, Shift и Zoom, которые будут ссылаться на синюю и зеленую ячейки соответственно:

Теперь чуть сложнее – создадим диапазон с именем Euros, который будет ссылаться с помощью функции СМЕЩ ( OFFSET) на данные по курсам евро за выбранный отрезок времени, используя только что созданные до этого диапазоны Shift и Zoom и ячейку E3 в качестве точки отсчета:

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

Аналогичным образом создается именованный диапазон Dollars для данных по курсу доллара:

И завершает картину диапазон Labels, указывающий на подписи к оси Х, т.е. даты для выбранного отрезка:

Общая получившаяся картина должна быть примерно следующей:

Шаг 6. Строим диаграмму

Выделим несколько строк в верхней части вспомогательной таблицы, например диапазон E3:G10 и построим по нему диаграмму типа График ( Line) . Для этого в Excel 2007/2010 нужно перейти на вкладку Вставка ( Insert) и в группе Диаграмма ( Chart) выбрать тип График ( Line) , а в более старших версиях выбрать в меню Вставка – Диаграмма ( Insert – Chart) . Если выделить одну из линий на созданной диаграмме, то в строке формул будет видна функция РЯД ( SERIES) , обслуживающая выделенный ряд данных:

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

=РЯД(Лист1!$F$3;Лист1! $E$4:$E$10 ;Лист1! $F$4:$F$10 ;1)

=РЯД(Лист1!$F$3;Лист1! Labels ;Лист1! Euros ;1)

Выполнив эту процедуру последовательно для рядов данных доллара и евро, мы получим то, к чему стремились – диаграмма будет строиться по динамическим диапазонам Dollars и Euros, а подписи к оси Х будут браться из динамического же диапазона Labels. При изменении положения ползунков будут меняться диапазоны и, как следствие, диаграмма. При включении-выключении флажков – отображаться только те валюты, которые нам нужны.

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

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