Ref-sgain.ru

Программы и сервисы
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как создать динамические интерактивные диаграммы в Excel?

Динамическая таблица в Excel

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

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

Выберите Вставка → Имя → Определить и введите MyRange в поле Имя рабочего листа. Введите в поле Формула (ссылается) следующую формулу: =OFFSET($A$1;0;0;COUNTA($A$l:$A$100);l), в русской версии Excel =OFFSET($A$1;0;COUNTA($A$1:$A$100);1). Теперь нажмите кнопку Добавить, а затем нажмите OK.

К сожалению, динамические именованные диапазоны нельзя проверить в стандартном поле имени слева от строки формул. Хотя это применимо, вы можете ввести MyRange в поле имени, щелкнув по нему и нажав Enter. Диапазоны автоматически выделяются в Excel. Кроме того, можно вызвать диалоговое окно Перейти к, выбрав команду Правка → Перейти к (сочетание клавиш Ctrl/Apple+G). В поле Ссылка введите MyRange и нажмите OK.

Поскольку в предыдущем примере вы создали динамический именованный диапазон, функция COUNTA заменяет аргумент Height в OFFSET.

На следующем примере мы будем использовать динамический именованный диапазон для определения таблицы данных, которая должна быть динамической. В поле Formula (Refers to) в Excel можно ввести следующую формулу: =OFFSET($A$1;0;0;COUNTA($A$1:$A$100);COUNTA($1:$1)), в русской версии Excel =OFFSET($A$1;0;COUNTA($A$1:$A$100);COUNTA($1:$1)). Динамический именованный диапазон будет увеличиваться на столько записей, сколько их в столбце А, а строки — на столько заголовков, сколько их в строке 1. Для второй функции COUNTA используйте постоянное число, например 10. Если вы уверены, что количество столбцов в таблице данных не изменится, вы можете заменить ее второй функцией COUNTA.

Динамический именованный диапазон для таблицы данных имеет только один недостаток: Максимальная длина таблицы определяется столбцом А. В большинстве случаев самым длинным столбцом будет один из самых длинных столбцов в таблице, но иногда это будет другой столбец. С помощью функции Excel MAX, которая возвращает наибольшее число в диапазоне ячеек, эту проблему можно решить. Для примера создайте таблицу, подобную приведенной на рис. 1. 3.4.

Читайте так же:
Как создать динамический ежемесячный календарь в Excel?

Рис. 3.4. Динамическая таблица данных и диалоговое окно присвоения имени

В строке 1 регистрируется функция COUNTA, которая ссылается на соответствующий столбец и поэтому возвращает количество записей в каждом столбце. Функция MAX должна использоваться в качестве аргумента высоты для функции OFFSET. Это гарантирует, что динамический диапазон, названный для этой таблицы, всегда будет расширяться вниз на столько ячеек, сколько ячеек содержит самый длинный столбец таблицы. Конечно, можно скрыть строку 1, так как пользователю совсем не нужно ее видеть.

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

Список чисел в столбце A также содержит пустые ячейки. Поэтому при использовании COUNT или COUNTA динамический именованный диапазон закончится перед последней ячейкой данных. Взгляните на рис. 3.5.

Рис. 3.5. Диапазон чисел и диалоговое окно присвоения имени

В данном случае, хотя последняя цифра диапазона фактически находится в строке 10, динамический диапазон простирается только до шестой строки. Причиной этого является функция COUNT, которая подсчитывает значения в ячейках с A1 по A100. Поскольку в списке всего шесть числовых значений, диапазон содержит только шесть строк.

Эту проблему можно решить с помощью функции Excel MATCH. Функция MATCH возвращает относительное положение элемента массива, соответствующего указанному значению, в указанном порядке. Например, если применить следующую функцию MATCH: =MATCH(6;$A$1:$A$100;0), в русской версии Excel =ПОСС(6;$A$1:$A$100;0), к тому же набору чисел, как на рис. При умножении на 3,5 будет возвращено число 10, представляющее позицию строки 10 в столбце А. Эта функция возвращает 10, так как вы сказали ей найти число 6 в диапазоне A1:A100.

Очевидно, что когда вы используете функцию MATCH на динамически именованном диапазоне, последнее число в диапазоне, вероятно, не известно заранее. Поэтому вы должны настроить функцию на поиск числа, слишком большого, чтобы оказаться в диапазоне, и изменить ее последний аргумент с 0 на 1.

Читайте так же:
Как скопировать указанное форматирование ячейки из одной ячейки в другие ячейки в Excel?

Функции MATCH было сказано найти ровно 6, не больше и не меньше. Заменив 0 на 1, вы заставите функцию искать наибольшее значение, которое меньше или равно указанному значению. Если вы предпочитаете русскую версию Excel, можно использовать =ПОИСК(1E+306;$A$1:$A$100;1).

Введите следующую формулу в поле Формула (Ссылается на) диалогового окна Определить имя, чтобы создать именованный диапазон, который расширяется до последней строки, содержащей число (независимо от того, есть ли перед ней пустые ячейки). В русской версии Excel равно =OFFSET(Sheet2!$A$l:$A$100;1);1. В Sheet2!$A$1;0;MATCH(1E+306;Sheet2!$A$l:$A$100;1);1.

Рис. 3.6. Динамический диапазон, расширяющийся до последней записи, содержащей число

Динамические именованные диапазоны логически вытекают из этого типа, поскольку они расширяются до последней текстовой записи независимо от пустых ячеек в списке или таблице. Если вы хотите сделать это, вам нужно изменить функцию MATCH таким образом: MATCH(«*»;$A$1:$A$100;-1), в русской версии Excel это будет SEARCH(«*»;$A$1:$A$100;-1). В диапазоне $A$1:$A$100 эта функция всегда возвращает номер строки последней текстовой записи.

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

Для этого сначала вставьте две пустые строки над списком. Выберите строки 1 и 2 и выберите Вставить → Строка. В первой ячейке (строка 1) введите функцию: =MAX(MATSN «*»;$A$3:$A$100;-1);MATSN(1E+306;$A$3:$A$100;1)), в русской версии Excel =MAX(POISON «*»;$A$3:$A$100;-1);POISON(1E+306;$A$3:$A$100;1)). В ячейку под ячейкой с формулой введите число 1. Ячейка под ячейкой с числом 1 должна содержать заголовок списка. Число 1 добавляется для того, чтобы вторая функция MATCH не вернула ошибку #N/A, если в диапазоне AZ:A100 нет чисел. Первая функция MATCH всегда будет находить текст — в заголовке.

Дайте ячейке A1 имя MaxRow. Выберите Вставка → Имя → Набор, дайте динамическому диапазону имя, например MyList, и введите в поле Формула (Ссылается на:) следующую формулу: =OFFSET(Sheet2!$A$3;0;0;MaxRow;1), в русской версии Excel =OFFSET(Sheet2!$A$3;0;0;MaxRow;1).

Ниже приведены списки различных динамических именованных диапазонов. В этих примерах потребуется заполнить столбец A как текстовыми, так и числовыми значениями. В качестве альтернативы выберите Вставка → Имя → Определить и введите однословное имя (например, MyRange) в поле Имена в рабочей книге. Изменится только поле Формула (Ссылается на).

Читайте так же:
Как скопировать столбец и вставить только уникальные записи в Excel?

Расширьте диапазон на столько строк, сколько у вас числовых записей: в поле Формула (относится) введите следующую формулу: =OFFSET($A$1;0;0;COUNT($A:$A);1), в русской версии Excel =OFFSET($A$1;0;0;COUNT($A;$A);1).

Расширьте диапазон на столько строк, сколько числовых и текстовых записей: введите в поле Формула (используется): =OFFSET($A$1;0;0;COUNTA($A:$A);1), в русской версии Excel =OFFSET($A$1;0;COUNTA($A:$A);1).

Расширение до последней числовой записи: введите в поле Формула (ссылается на) следующее: =OFFSET($A$1;0;0;MATCH(1E+306;$A:$A)), в русской версии Excel =OFFSET($A$1;0;0;MATCH(1E+306;$A:$A)). Если вы ожидаете найти число больше, чем 1E+306 (1 с 306 нулями), введите еще большее число.

Продолжая последний текстовый ввод: щелкните поле Формула (Ссылается на) и введите следующее число: =OFFSET($A$1;0;0;MATSN(«*»;$A:$A;-1); в русской версии Excel =OFFSET($A$1;0;POISE(«*»);$A:$A;-1).

Прокрутите список вниз в соответствии со значением в другой ячейке: В ячейку B1 введите число 10, затем в поле Формула (Ссылается на) введите следующее: =OFFSET($A$1;0;0;$B$1;1), в русской версии Excel =OFFSET($A$1;0;0;$B$1;1) Теперь измените число в ячейке B1, и диапазон будет изменен соответствующим образом.

Разверните вниз на одну строку каждый месяц: В поле Формула (Относится к) введите следующее: =OFFSET($A$1;0;0,MONTH(TODAY());1), в русской версии Excel =OFFSET($A$1;0;0. МЕСЯЦ(СЕГОДНЯ));1).

Расширяйте вниз на одну строку каждую неделю: В поле Формула (Относится к) введите следующее: =OFFSET($A$1;0;0;WEEKNUM(TODAY());1), В русской версии Excel =OFFSET($A$1;0;0;TODAY());1)

Для получения последней формулы необходимо установить Analysis ToolPak. Это можно сделать с помощью Инструменты → Дополнения.

Русские Блоги

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

Продажи могут измеряться ежеквартально, ежемесячно и ежедневно (не обязательно каждые 5 дней, 10 дней и 15 дней). Для этого динамического графика мы делаем это со следующих трех аспектов:

1. проверка выбора

Поскольку диаграмма активна, потому что данные изменились, как изменились эти данные? Это контролируется элементом управления. Вот элемент управления списком, который позволяет переключаться между месяцами, кварталами и днями. Итак, вот список:

На следующем рисунке изображен элемент управления вставкой

Список
После вставки [списка] щелкните правой кнопкой мыши, чтобы установить формат элемента управления, вы увидите следующий рисунок, источником данных является текст, который необходимо щелкнуть в будущем, и ячейка ссылки появится в списке Относительная позиция, а именно: 1, 2, 3

Читайте так же:
Как скрыть общую сумму в сводной таблице Excel?

Полоса прокрутки
[Полоса прокрутки] используется для управления начальной точкой данных даты, поэтому минимальное значение равно 1, максимальное значение равно 365, размер шага означает щелчок, чтобы изменить размер шага, размер шага страницы равен Нажмите на смещение в средней позиции.

Кнопка выбора
Управляет продолжительностью даты, отображаемой с помощью [кнопки выбора], но кнопка выбора может возвращать только 1, 2, 3. Поэтому я сделал вспомогательную ячейку N6, где формула: = IF (N5 = 1,5, IF (N5 = 2,10,15))

2. Определите область
Столбец AB — это ежедневные данные за весь год, столбец CD — сводные данные за 12 месяцев, а столбец EF — данные за четыре квартала.

Определите значение квартал и квартал и т. Д., Все области определены в диспетчере имен, скриншот выглядит следующим образом

3. Вставьте диаграмму
После того, как диаграмма вставлена ​​случайно, щелкните правой кнопкой мыши [Выбрать данные]> Удалить существующие данные> Добавить и, наконец, следующее изображение:

Затем измените тип диаграммы> комбинированная диаграмма> выберите линейную диаграмму

Как создать смешанную диаграмму в Excel

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

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

Рис. 136.1. Эта диаграмма будет более эффективной, если сделать ее смешанной

Рис. 136.1. Эта диаграмма была бы более эффективной, если бы в ней были смешаны

На рис. 136.2 показана диаграмма после того, как я преобразовал ее в смешанный формат. Теперь значение осадков показано в виде линии, а вторая вертикальная ось используется справа. Большинство из вас согласятся, что эта диаграмма значительно превосходит оригинал. Вы можете использовать ее при анализе себестоимости товара в ювелирном салоне «Вологды».

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

  1. Щелкните по строкам, которые вы хотите изменить.
  2. Выберите Работа с диаграммами ► Конструктор ► Тип ► Изменить тип диаграммы.
  3. В диалоговом окне Изменение типа диаграммы выберите значок, который представляет тип диаграммы для выбранного ряда.
  4. Чтобы использовать другую вертикальную ось для преобразованной строки, выделите строку и нажмите Ctrl+1, чтобы открыть диалоговое окно Формат строки данных; перейдите в раздел Параметры строки и установите переключатель Построить строку в положение Вспомогательная ось.
Читайте так же:
Как создать зависимый каскадный выпадающий список в Excel?

Рис. 136.2. Такая смешанная диаграмма отображает два ряда данных, величины которых различаются

Рисунок 136.2. Этот смешанный график показывает два ряда данных с разными значениями

Диаграмма на рисунке 136.1 представляла собой стандартную гистограмму с двумя рядами. Несколькими щелчками мыши один из столбцов (в данном примере «Осадки») был преобразован в линию. Я также добавил вторую ось и пометил оси, чтобы было понятно, какой масштаб для какой строки используется. Чтобы добавить названия осей, выполните команду Работа с графиками ► Макет ► Названия осей.

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

Рис. 136.3. Смешанная диаграмма, использующая отдельную вертикальную ось

Рисунок 136.3. Смешанная диаграмма с использованием отдельной вертикальной оси

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

Рис. 136.4. Смешанная диаграмма объединяет пять типов диаграмм

Рисунок 136.4: Смешанные диаграммы сочетают в себе пять типов диаграмм.

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector