Слишком длинная формула ряда в диаграмме excel
= Мир MS Excel/Статьи об Excel
Функции листа. Функции баз данных [1] |
Функции листа. Функции даты и времени [5] |
Функции листа. Функции инженерные [1] |
Функции листа. Функции информационные [2] |
Функции листа. Функции логические [10] |
Функции листа. Функции математические [4] |
Функции листа. Функции статистические [1] |
Функции листа. Функции текстовые [1] |
Функции листа. Функции финансовые [1] |
Функции листа. Функции ссылок и массивов [2] |
Функции листа. Функции аналитические [1] |
Функции листа. Функции определённые пользователем [1] |
Функция диаграмм [1] |
Макрофункции [37] |
- Имя_ряда - необязательный аргумент. Если у ряда нет названия, этот аргумент опускается и Excel использует в легенде фиктивные названия рядов (Ряд 1, Ряд 2 и т.д.).
- Имя_категории - необязательный аргумент. Если нет названия категорий, этот аргумент опускается и Excel использует фиктивные метки категорий (1, 2, 3 и т.д.).
- Диапазон_данных - обязательный аргумент. Ссылка на ячейки содержащие числовые значения для построения диаграммы.
- Номер_ряда - обязательный аргумент. Номер ряда выбранных данных.
Эта функция недоступна в мастере функций, так как не является функцией листа. Функция РЯД() появляется автоматически, при создании диаграммы. Увидеть функцию можно в строке формул, если выделить на диаграмме ряд данных. Так же в строке формул её можно редактировать.
Предположим, что нам с вами требуется визуализировать данные из вот такой таблицы со значениями продаж автомобилей по разным странам в 2021 году (реальные данные, взятые отсюда, кстати):
Поскольку количество рядов данных (стран) велико, то попытка запихнуть их все сразу в один график приведёт либо к ужасной "спагетти-диаграмме", либо к построению отдельных диаграмм на каждый ряд, что весьма громоздко.
Изящным решением этой проблемы может стать построение диаграммы только по данным из текущей строки, т. е. строки, где стоит активная ячейка:
Реализовать такое очень легко - потребуется лишь две формулы и один крохотный макрос в 3 строки.
Шаг 1. Номер текущей строки
Первое, что нам потребуется - это именованный диапазон, вычисляющий номер строки на листе, где сейчас стоит наша активная ячейка. Открываем на вкладке Формулы - Диспетчер имен (Formulas - Name manager) , жмём на кнопку Создать (Create) и вводим туда следующую конструкцию:
- Имя - любое подходящее имя для нашей переменной (в нашем случае это ТекСтрока)
- Область - здесь и далее нужно выбрать текущий лист, чтобы создаваемые имена были локальными
- Диапазон - тут используем функцию ЯЧЕЙКА (CELL) , которая умеет выдавать кучу разных параметров для заданной ячейки, в том числе и нужный нам номер строки - за это отвечает аргумент "строка".
Шаг 2. Ссылка на заголовок
Для отображения выбранной страны в заголовке и легенде диаграммы, нам нужно получить ссылку на ячейку с её (страны) названием из первого столбца. Для этого создаём еще один локальный (т.е. Область = текущий лист, а не Книга!) именованный диапазон со следующей формулой:
Здесь функция ИНДЕКС выбирает из заданного диапазона (столбца А, где лежат наши страны-подписи) ячейку с номером строки, который мы до этого определили.
Шаг 3. Ссылка на данные
Теперь аналогичным образом давайте получим ссылку на диапазон со всеми данными по продажам из текущей строки, где стоит сейчас активная ячейка. Создаём ещё один именованный диапазон со следующей формулой:
Здесь третий аргумент равный нулю заставляет ИНДЕКС вернуть в качестве результата не отдельное значение, а всю строку.
Шаг 4. Подставляем ссылки в диаграмму
Теперь выделим шапку таблицы и первую строку с данными (диапазон ) и построим по ним диаграмму через Вставка - Диаграммы (Insert - Charts) . Если выделить на диаграмме ряд с данными, то в строке формул отобразится функция РЯД (SERIES) - специальная функция, которую Excel автоматически использует при создании любой диаграммы, чтобы сослаться на исходные данные и подписи:
Аккуратно подменим в этой функции первый (подпись) и третий (данные) аргументы названиями наших диапазонов с шагов 2 и 3:
Диаграмма начнет отображать данные по продажам из текущей строки.
Шаг 5. Макрос пересчета
Остался последний штрих. Microsoft Excel пересчитывает формулы только при изменении данных на листе или при нажатии на клавишу F9 , а мы хотим, чтобы пересчёт происходил при изменении выделения, т. е. при любом перемещении активной ячейки по листу. Для этого потребуется добавить в нашу книгу простой макрос.
Щёлкните правой кнопкой мыши по ярлычку листа с данными и выберите команду Исходный код (Source code) . В открывшееся окно введём код макроса-обработчика события изменения выделения:
Как легко сообразить, всё, что он делает - это запускает пересчет листа при любом изменении положения активной ячейки.
Шаг 6. Подсветка текущей строки
Для наглядности, можно добавить ещё и правило условного форматирования для выделения цветом страны, которая сейчас отображается на графике. Для этого выделим таблицу и выберем Главная - Условное форматирование - Создать правило - Использовать формулу для определения форматируемых ячеек (Home - Conditional formatting - New rule - Use a formula to determine which cells to format) :
Здесь формула проверяет для каждой ячейки в таблице совпадение её номера строки с тем номером, что хранится в переменной ТекСтрока, и если совпадение имеет место, то срабатывает заливка выбранным цветом.
Вот и всё - просто и красиво, правда?
Примечания
- На больших таблицах вся эта красота может тормозить - условное форматирование штука ресурсоёмкая, да и пересчёт на каждое выделение тоже может быть тяжеловат.
- Чтобы на графике не пропадали данные при случайном выделении ячейки выше или ниже таблицы, можно добавить в имя ТекСтрока дополнительную проверку вложенными функциями ЕСЛИ вида:
Ребята, прошу помощи.
В приложенном примере есть пояснения в виде примечаний
Ребята, прошу помощи.
В приложенном примере есть пояснения в виде примечаний VEKTORVSFREEMAN
"Опыт - это то, что получаешь, не получив того, что хотел"
Ребята, прошу помощи.
В приложенном примере есть пояснения в виде примечаний Автор - VEKTORVSFREEMAN
Дата добавления - 30.09.2014 в 11:37
VEKTORVSFREEMAN, Все у вас правильно написано. Ошибку выдавало потому, что в том диапазоне данных которые вы указали, нет 77 столбца. Если Вы напишите:
VEKTORVSFREEMAN, Все у вас правильно написано. Ошибку выдавало потому, что в том диапазоне данных которые вы указали, нет 77 столбца. Если Вы напишите:
Диапазон A12:BX104 содержит 76 столбцов, формула должна взять из 77-го, отсюда ошибка.
Если поменять 77 на правильную 76, то все работает.
UPD: F9 не всегда целесообразно применять ;-)
Диапазон A12:BX104 содержит 76 столбцов, формула должна взять из 77-го, отсюда ошибка.
Если поменять 77 на правильную 76, то все работает.
UPD: F9 не всегда целесообразно применять ;-) Russel
Диапазон A12:BX104 содержит 76 столбцов, формула должна взять из 77-го, отсюда ошибка.
Если поменять 77 на правильную 76, то все работает.
UPD: F9 не всегда целесообразно применять ;-) Автор - Russel
Дата добавления - 30.09.2014 в 11:50
VEKTORVSFREEMAN, здравствуйте.
VEKTORVSFREEMAN, здравствуйте.
VEKTORVSFREEMAN, Совет. Если есть желание использовать ВПР, попробуйте назвать столбцы в одной и другой таблице одиннаково. И в формуле ВПР, ставить поиск по названию столбца. Могу выслать пример как это будет выглядеть. Если нужна отчетность, можете поиграться с цветом отображения, чтобы скрыть названия столбцов от руководства.
VEKTORVSFREEMAN, Совет. Если есть желание использовать ВПР, попробуйте назвать столбцы в одной и другой таблице одиннаково. И в формуле ВПР, ставить поиск по названию столбца. Могу выслать пример как это будет выглядеть. Если нужна отчетность, можете поиграться с цветом отображения, чтобы скрыть названия столбцов от руководства. YouGreed
YouGreed, интересно будет взглянуть на пример, т.к. я сейчас пытаюсь научиться работе в эксель. Буду признателен.
YouGreed, интересно будет взглянуть на пример, т.к. я сейчас пытаюсь научиться работе в эксель. Буду признателен. VEKTORVSFREEMAN
Качественная визуализация большого объема информации – это почти всегда нетривиальная задача, т.к. отображение всех данных часто приводит к перегруженности диаграммы, ее запутанности и, в итоге, к неправильному восприятию и выводам.
Вот, например, данные по курсам валют за несколько месяцев:
Строить график по всей таблице, как легко сообразить, не лучшая идея. Красивым решением в подобной ситуации может стать создание интерактивной диаграммы, которую пользователь может сам подстраивать под себя и ситуацию. А именно:
- двигаться по оси времени вперед-назад в будущее-прошлое
- приближать-удалять отдельные области диаграммы для подробного изучения деталей графика
- включать-выключать отображение отдельных валют на выбор
Выглядеть это может примерно так:
Нравится? Тогда поехали.
Шаг 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. При изменении положения ползунков будут меняться диапазоны и, как следствие, диаграмма. При включении-выключении флажков – отображаться только те валюты, которые нам нужны.
Таким образом мы имеем полностью интерактивную диаграмму, где можем отобразить именно тот фрагмент данных, что нам нужен для анализа.
Доброго всем!
Есть задача Построить аналитику по результатам контрольных работ по каждому ученику. Что мог сделать "в лоб" в приложенном файле. Наглядно и информативно. Проблема в том, что не могу придумать, как сделать график на всю таблицу. Пишет "Максимальное число рядов данных для одной диаграммы 255". Понимаю, что нужно что-то сделать динамическим, но не соображу что и как. А таблица будет ещё расти. Пока две контрольных (ШДР), а будет всего пять. ((
Может кто поможет хотя бы идеей?
Доброго всем!
Есть задача Построить аналитику по результатам контрольных работ по каждому ученику. Что мог сделать "в лоб" в приложенном файле. Наглядно и информативно. Проблема в том, что не могу придумать, как сделать график на всю таблицу. Пишет "Максимальное число рядов данных для одной диаграммы 255". Понимаю, что нужно что-то сделать динамическим, но не соображу что и как. А таблица будет ещё расти. Пока две контрольных (ШДР), а будет всего пять. ((
Может кто поможет хотя бы идеей? dwn
с добавлением столбца для маркировки нужной строки
создан один динамический диапазон, остальные делать по подобию
с добавлением столбца для маркировки нужной строки
создан один динамический диапазон, остальные делать по подобию dude
3. В параметрах диаграммы (выбор данных) - Диапазон - указал динамический диапазон AAA
Для каждого отдельного класса всё нормально.
Но! При выборе другого класса график пустой ((
Excel один раз выбирает диапазон по формуле и подставляет его в Диапазон Диаграммы. Затем он не меняется при выборе другого класса.
Форма среза очень привлекательна, как бы её увязать с изменением диапазона?
3. В параметрах диаграммы (выбор данных) - Диапазон - указал динамический диапазон AAA
Для каждого отдельного класса всё нормально.
Но! При выборе другого класса график пустой ((
Excel один раз выбирает диапазон по формуле и подставляет его в Диапазон Диаграммы. Затем он не меняется при выборе другого класса.
Форма среза очень привлекательна, как бы её увязать с изменением диапазона? dwn
3. В параметрах диаграммы (выбор данных) - Диапазон - указал динамический диапазон AAA
Для каждого отдельного класса всё нормально.
Но! При выборе другого класса график пустой ((
Excel один раз выбирает диапазон по формуле и подставляет его в Диапазон Диаграммы. Затем он не меняется при выборе другого класса.
Форма среза очень привлекательна, как бы её увязать с изменением диапазона? Автор - dwn
Дата добавления - 02.02.2019 в 19:20
Читайте также: