Недействительная ссылка на расположение или диапазон данных excel
Обновлено: 04.07.2024
Спарклайн в Excel – это график в ячейке (можно назвать мини-графиком). Спарклайн очень удобный дополнительный инструмент для презентации тенденции общего тренда в отчетах. Он эффектно дополняет общее графическое представление о поведении конкретной ситуации. Но определенно его нельзя использовать для детального анализа данных. Почему так и какие преимущества спарклайнов перед обычными графиками? Ответим на этот вопрос конкретным примером.
Как сделать спарклайн в Excel
Разберем на конкретном примере, что такое спарклайны в Excel и как их использовать. Предприятие имеет 5 магазинов в разных районах города. Возле таблицы с показателями оборотов в месяцах первого квартала разместите мини-графики в ячейках отображающие тенденцию изменения оборотов за текущий период:
- Заполните таблицу данными так как показано ниже на рисунке:
- Выделите диапазон B2:D6 и выберите инструмент: «Вставка»-«Спарклайны»-«График».
- В появившемся диалоговом окне «Создание спарклайнов» укажите значение в полю «Диапазон расположения», выделив диапазон E2:E6. И нажмите ОК.
- Теперь если выделить диапазон ячеек E2:E6 у нас появляется дополнительная панель инструментов «Работа со спаркалнйами». В ней выберите инструмент: «Конструктор»-«Цвет маркера»-«Маркеры»-«Оранжевый 25%». Это действие сделало мини-графики более читабельными и привлекательными.
Несмотря на то, что спарклайны – это маленькие графики в ячейках их значения все равно легче читать, чем из таблицы. Мы не можем детально проанализировать ситуацию по каждому магазину. Но нам легко быстро оценить общую ситуацию по всех магазина одновременно. Которые магазины хорошо развиваются, а которые нет.
Чтобы оценить преимущества спарклайнов, создайте обычных график и сравните:
- Выделите диапазон A1:D6. Потом выберите инструмент: «Вставка»-«Диаграммы»-«График».
- Так как у нас категорий (месяцев) меньше чем рядов (магазинов) нужно поменять значения в строках и столбцах. Для этого выберите инструмент: «Работа с диаграммами»-«Конструктор»-«Строка/столбец».
В результате у нас получилась картинка, которая не требует лишних комментариев.
Спарклайны — маленькие диаграммы, помещающиеся в одну ячейку листа. Используются для наглядного отображения изменения данных. Спарклайны позволяют обнаружить тенденции, периодические (сезонные) изменения, выделить максимальное (минимальное) значение. [1]
Например, на рис. 1 показаны изменения среднемесячной температуры июля в Москве за период наблюдений с 1879 по 2013 г. Чтобы рисунок был более наглядным показаны не абсолютные значения, а колебания относительно среднего значения за весь период наблюдений (18,4⁰С). Красным выделен абсолютный максимум (2010 г., 26,1⁰С) и минимум (1904 г., 14,6⁰С).
Рис. 1. Динамика среднемесячной температуры июля в Москве
Чтобы создать спарклайн перейдите на вкладку Вставить → область Спарклайны (рис. 2). Excel предлагает три типа спарклайнов: график, гистограмму и диаграмму Выигрыш/проигрыш (рис. 3). Последняя показывает столбиками одинаковой высоты значения + и –.
Рис. 2. Меню Спарклайны
Рис. 3. Типы спарклайнов: (а) график, (б) гистограмма, (в) Выигрыш/проигрыш
Рис. 4. Окно Создание спарклайнов
Рис. 5. Объединение ячеек
После создания спарклайна на ленте появится вкладка Работа со спарклайнами. Для настройки спарклайнов используйте команды на вкладке Конструктор (рис. 6). Если после создания спарклайна вы совершали еще какие-либо действия в Excel, вкладка Работа со спарклайнами может пропасть с ленты. Для того, чтобы вызвать эту вкладку, кликните на спарклайне, с которым вы планируете работать. Заметил небольшую нестыковку: первый тип спарклайна вкладки Вставка (см. рис. 2) называется График, а на вкладке Конструктор – Линия (см. рис. 6).
Рис. 6. Вкладка Конструктор ленты Excel
Рассмотрим подробнее элементы вкладки Работа со спарклайнами на примере динамики Индекса потребительских цен США (CPI) за период 2006–2014 (рис. 7). На момент публикации заметки последний отчет относился к марту 2014 г. Как и в предыдущем пример, чтобы данные были более рельефными используются не абсолютные значения индекса (область А1:М11), а изменения в процентах к предыдущему месяцу (область А13:М22).
Рис. 7. Индекс потребительских цен США (CPI) за период с января 2006 по март 2014 г. Для справки: значение CPI=10 это среднее значение по 1913 г.
Поскольку спарклайн не может быть создан одновременно в нескольких ячейках, создайте его в ячейке N14, а затем «протащите» вниз, как обычную формулу!
Меню Изменить данные позволяет (рис. 8):
1) Вызвать окно Создание спарклайнов (как на рис. 4) и изменить Диапазон данных и/или Диапазон расположения, как для одного спарклайна, так и для всех спарклайнов в группе. Например, вы можете изменить Диапазон расположения и перенести все спарклайны из ячеек N14:N22 в ячейки О14:О22. Или не отображать данные за каждый декабрь, сократив Диапазон данных с А14:М22 до А14:L22.
2) Задать режим отображения скрытых и пустых ячеек. Например, на рис. 9 скрыты столбцы F, G, H. Вызвав опцию Скрытые и пустые ячейки, можно выбрать два варианта отображения скрытых строк/столбцов: отображать или не отображать.
3) Выбрать режим отображения данных в спарклайне: по строкам или по столбцам. Эта команда применяется при одинаковом количестве строк и столбцов в Диапазоне данных.
Рис. 8. Опции меню Изменить данные
Рис. 9. Режим отображения скрытых и пустых ячеек
Меню Тип позволяет изменить тип спарклайна на один из трех доступных: график (он же линия), гистограмма, Выигрыш/проигрыш.
Меню Показать позволяет задать элементы спарклайна, которые будут отражаться другим цветом (рис. 10). Например, на рис. 1 включены опции Максимальная точка и Минимальная точка, а на рис. 9 включена опция Отрицательные точки. Заметим, что опция Маркеры доступна только для типа спарклайна Линия (рис. 11).
Рис. 10. Опции меню Показать
Рис. 11. Спарклайн Линия с выключенной (а) и включенной (б) опцией Маркер
Опции меню Стиль позволяют выбрать одну из многочисленных цветовых гамм (рис. 12), а также, если ни один вариант не подходит (что довольно странно :)), задать цвет самостоятельно в цветовой палитре Цвет спарклайна. Также можно установить пользовательские цвета для всех особых точек: отрицательных, максимального, минимального значений и др.
Рис. 12. Опции меню Стиль
Опции меню Группировать. По умолчанию (например, на рис. 9) спарклайны, созданные друг под другом, сгруппированы. Если вы хотите их разгруппировать, воспользуйтесь соответствующей опцией в этой группе меню. В дальнейшем их опять можно будет сгруппировать. Группировка влияет на управление Диапазоном данных и Диапазоном расположения в меню Изменить данные. Только сгруппированными спарклайнами можно управлять, как единым целым.
Ни Delete, ни Backspace не удаляют спарклайны. Для этого есть специальная кнопка Очистить. Также можно щелкнуть на ячейке правой кнопкой мыши, и в появившемся контекстном меню выбрать Спарклайны, а затем Очистить выбранные спарклайны или группы спарклайнов.
Наконец, опция Ось позволяет управлять параметрами спарклайна, как по оси ординат, так и по оси абсцисс (рис. 13). Например, на рис. 3а я включил опцию Показать ось. Параметры Минимального и максимального значений по вертикальной оси позволяют отображать спарклайны в одном масштабе (при включенной опции Фиксированное для всех значений), а также увеличить масштаб, если какое-то значение экстремально, и из-за него динамика других значений становятся плохо различимой (рис. 14).
Рис. 13. Опции меню Ось
Рис. 14. Выбор параметров минимального и максимального значений по вертикальной оси: (а) автоматическое, (б) фиксированное, (в) пользовательское ±1%. Опция Автоматическое позволяет наилучшим образом сравнивать значения в пределах одного спарклайна, но привносит искажения при сравнении нескольких спарклайнов между собой.
Поскольку спарклайны размещаются в отдельном слое, в ячейке Excel со спарклайном может находиться любое значение или текст (рис. 15).
В следующем примере в столбце E используется формула =СУММ(B2;C2;D2).
Если вы случайно удалили строки или столбцы, вы можете немедленно нажать кнопку "Отменить" на панели быстрого доступа (или нажать клавиши CTRL+Z), чтобы восстановить их.
Измените формулу так, чтобы она ссылалась на диапазон, а не на отдельные ячейки, например =СУММ(B2:D2). Теперь можно удалить любой столбец в диапазоне суммирования, и Excel автоматически скорректирует формулу. Чтобы вычислить сумму значений в строках, также можно использовать формулу =СУММ(B2:B5).
Пример функции ВПР с неправильными ссылками на диапазоны
Расширьте диапазон или уменьшите значение столбца для поиска так, чтобы он попадал в указанный диапазон. Формулы =ВПР(A8;A2:E5;5;ЛОЖЬ) будет работать правильно, так же как и формула =ВПР(A8;A2:D5;4;ЛОЖЬ).
Пример функции ИНДЕКС с неправильной ссылкой на строку или столбец
Измените ссылки на строки и столбцы так, чтобы они попадали в диапазон поиска функции ИНДЕКС. Формула =ИНДЕКС(B2:E5;4;4) вернет правильный результат.
Пример ссылки на закрытую книгу с использованием функции ДВССЫЛ
Откройте книгу, на которые ссылается ссылка. Эта же ошибка будет возникнуть при ссылке на закрытую книгу с динамической функцией массива.
Проблемы с OLE
Примечание. OLE — это технология, которая используется для обмена информацией между приложениями.
Проблемы dDE
Примечание. Динамические Exchange (DDE)— это протокол, который позволяет обмениваться данными между Windows программами Майкрософт.
Проблемы с макросами
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Спарклайны впервые появились в Excel 2010 года и с тех пор набирают все большую популярность. Несмотря на то, что спарклайны очень похожи на миниатюрные диаграммы, это не одно и то же и у них немного разное назначение. В этом уроке мы познакомим Вас со спарклайнами и расскажем, как ими пользоваться в рабочей книге Excel.
В некоторых источниках спарклайны называют инфолиниями.
Типы спарклайнов
В Excel существует три типа спарклайнов: спарклайн-график, спарклайн-гистограмма и спарклайн выигрыша/проигрыша. Спарклайн-график и спарклайн-гистограмма работают так же, как и обычные графики и гистограммы. Спарклайн выигрыша/проигрыша похож на стандартную гистограмму, но отображает не величину значения, а каким оно является – положительным или отрицательным. Все три типа спарклайнов могут отображать маркеры в важных местах, например, максимумы и минимумы, благодаря этому их очень легко читать.
Для чего используются спарклайны?
Спарклайны в Excel имеют ряд преимуществ над обычными диаграммами. Представьте, что у Вас есть таблица, состоящая из 1000 строк. Стандартная диаграмма построила бы 1000 рядов данных, т.е. по одному ряду для каждой строки. Думаю, не трудно догадаться, что на такой диаграмме будет сложно что-либо найти. Куда эффективней для каждой строки в таблице Excel создать отдельный спарклайн, который будет располагаться рядом с исходными данными, позволяя наглядно увидеть взаимосвязь и тенденцию отдельно для каждого ряда.
На рисунке ниже можно увидеть довольно громоздкий график, в котором трудно что-либо разобрать. Спарклайны же позволяют четко отследить продажи каждого торгового представителя.
К тому же спарклайны выгодно применять, когда нужен простой обзор данных и нет необходимости использовать громоздкие диаграммы с множеством свойств и инструментов. При желании Вы можете использовать как обычные графики, так и спарклайны для одних и тех же данных.
Создание спарклайнов в Excel
Как правило, для каждого ряда данных строится один спарклайн, но при желании Вы можете создать любое количество спарклайнов и разместить их там, где необходимо. Первый спарклайн проще всего создать в самой верхней строке с данными, а затем, используя маркер автозаполнения, скопировать его во все оставшиеся ряды. В следующем примере мы создадим спарклайн-график, чтобы наглядно представить динамику продаж для каждого торгового представителя за определенный промежуток времени.
- Выделите ячейки, которые будут служить исходными данными для первого спарклайна. Мы выберем диапазон B2:G2.
- Перейдите на вкладку Вставка и укажите нужный тип спарклайна. Например, спарклайн-график.
- Появится диалоговое окно Создание спарклайнов. Используя мышь, выделите ячейку для размещения спарклайна, а затем нажмите ОК. В нашем случае мы выберем ячейку H2, ссылка на ячейку появится в поле Диапазон расположения.
- Спарклайн появится в выбранной ячейке.
- Нажмите и, удерживая левую кнопку мыши, перетащите маркер автозаполнения, чтобы скопировать спарклайн в соседние ячейки.
- Спарклайны появятся во всех строках таблицы. На следующем рисунке видно, как спарклайны наглядно демонстрируют тенденции продаж для каждого торгового представителя за полгода.
Изменение внешнего вида спарклайнов
Скорректировать внешний вид спарклайна довольно просто. Excel предлагает для этих целей целый ряд инструментов. Вы можете настроить отображение маркеров, задать цвет, изменить тип и стиль спарклайна, и многое другое.
Отображение маркеров
Вы можете акцентировать внимание на определенных участках спарклайн-графика с помощью маркеров или точек, тем самым увеличивая его информативность. Например, на спарклайне с множеством больших и малых значений очень трудно понять, какое из них является максимальным, а какое минимальным. С включенными опциями Максимальная точка и Минимальная точка сделать это намного проще.
Изменение стиля
Изменение типа
Каждый тип спарклайна предназначен для определенных целей. Например, спарклайн выигрыша/проигрыша больше подходит для данных, где имеются положительные или отрицательные значения (к примеру, чистая прибыль).
Изменение диапазона отображения
По умолчанию каждый спарклайн в Excel имеет такой масштаб, который соответствует максимальному и минимальному значению его исходных данных. Максимальное значение располагается вверху ячейки, а минимальное внизу. К сожалению, это не демонстрирует величину значения, если сравнивать его с другими спарклайнами. Excel позволяет изменить внешний вид спарклайнов таким образом, чтобы их можно было сравнивать друг с другом.
Как изменить диапазон отображения
- Выделите спарклайны, которые необходимо изменить.
- На вкладке Конструктор выберите команду Ось. Появится выпадающее меню.
- В параметрах для максимального и минимального значений по вертикальной оси включите опцию Фиксированное для всех спарклайнов.
- Спарклайны будут обновлены. Теперь их можно использовать, чтобы сравнить объемы продаж между торговыми представителями.
Читайте также: