Не удается создать точечную диаграмму из сводной таблицы в excel
нет, excel. I can, просто вы это мне не позволяет. * копировать / вставить, создать диаграмму, готово*.
есть ли способ обойти эту проверку? Копирование/вставка работает, но я хотел бы точечную диаграмму из сводной таблицы автоматически обновляются при изменении исходных данных.
3 ответов
выберите пустую ячейку в нескольких строках или столбцах от сводной таблицы.
вставить точечную диаграмму XY, которая будет пустой, потому что данные не были выбраны.
щелкните диаграмму Правой Кнопкой Мыши и выберите Редактировать данные.
Добавить новый ряд, отредактировать этот ряд, выбрать диапазоны с именем ряда, значениями X и Y.
нажмите кнопку ОК пару раз, чтобы вернуться в Excel.
эта диаграмма должна измениться при обновлении данных сводной таблицы он не будет идти в ногу, если изменяется размер сводной таблицы.
вы можете использовать именованные диапазоны для правильного обновления графика при изменении размера сводной таблицы.
Если вы хотите, чтобы график мог меняться с помощью фильтров сводной таблицы, вы можете создать сводную таблицу с двумя столбцами данных (скажем, столбец A имеет метку, А B и C имеют данные), а затем ссылаться на эти два столбца в других двух столбцах( например. сделайте ячейки в столбце E, например, сделайте так, чтобы E2 содержал =B2, а затем сделайте ячейки в столбце F, например, сделайте F2 contian =C2).
Это даст вам два столбца данных, и вы можете использовать инструкции из над столбом, чтобы сделать его в диаграмму рассеяния.
Настройки Фильтрации : Допустим, у меня есть сводная таблица со 100 строками. Убедитесь, что эти ссылочные столбцы (столбцы E и F) перетащите формулы до 100. Теперь, как только вы отфильтруете, используя срез, вы заметите, что заполнены только некоторые из Столбцов E и F, а нулевые (потому что отфильтрованная сводная таблица не заходит так далеко вниз.
проблема? Теперь вы таблица будет иметь кучу точек на (0,0), которые могут испортить трендовые линии и т. д.
Итак, чтобы исправить это, измените формулы в Столбцах e и f на, например, для B2,сделайте D2 содержать =if(B2>0,B2, NA())
самое классное в NA () то, что она не включена в график. Таким образом, у вас есть сводная таблица, которую вы можете фильтровать, и график рассеяния автоматически обновляется без каких-либо ненужных нулей.
XY точечная диаграмма требует, чтобы обе оси X и Y были числовыми значениями. Обычно оси X и Y сводной таблицы не являются числовыми, они сгруппированы по выбранным полям. Именно по этой причине точечная диаграмма XY не может быть связана с сводной таблицей.
Если вы хотите показать доту, такие как диаграммы, связанные с сводной таблицы, выберите тип диаграммы график с маркерами. Затем задайте строку для каждого ряда в диаграмме.
Как заставить Excel 2007 и 2010 игнорировать пустые ячейки в диаграмме или графике
Как настроить Excel 2007 и Excel 2010 для игнорирования пустых ячеек при создании диаграммы или графика
Это проблема, с которой я столкнулся сам, когда я хотел создать диаграмму, когда большинство ячеек в диапазоне, который вы хотите построить, являются пустыми (содержат пустые ячейки).
Чтобы решить эту проблему и создать диаграмму, которая правильно отображает данные, у вас может возникнуть соблазн, как я делал в прошлом, скопировать значения данных в новый диапазон, чтобы данные были непрерывными (без пробелов). Это может занять очень много времени и очень раздражать, особенно если объем данных велик. Предполагается, что Excel упростит нашу коллективную жизнь, поэтому я решил найти решение этой проблемы, предполагающее как можно меньше дополнительной ручной работы.
В моем случае мне было интересно составить график средненедельного количества ежедневных продаж, которые совершала моя команда. Когда я создал диаграмму, выбрав диапазон данных, все, что у меня было, это диаграмма без данных.
Мы рассмотрим два метода решения этой проблемы:
- Как изменить способ работы диаграммы с пустыми или пустыми ячейками
- Как использовать формулу для изменения содержимого пустых ячеек на # Н / Д (Excel автоматически игнорирует ячейки с # Н / Д при создании диаграммы)
На рисунке ниже вы можете увидеть:
- Данные, которые я использую для создания диаграмм (слева)
- Пустая начальная таблица (вверху справа)
- Заполненная диаграмма (внизу справа)
Метод 1: настройка того, как Excel обрабатывает скрытые и пустые ячейки
Первый метод, который мы собираемся использовать для создания диаграммы, которая правильно отображает данные, — это изменить способ обработки в Excel 2007 и Excel 2010 пустых ячеек на графиках.
По умолчанию на диаграмме, когда Excel видит пустую ячейку, создается пробел. Если у вас есть диапазон данных с большим количеством пустых ячеек, чем ячеек с данными, это означает, что Excel эффективно создает график, полный пробелов.
Для начала нам нужно создать график средних продаж за неделю.
- Мы создаем линейный график, выбирая данные в B3: B31 и D3: D31 (нажмите и удерживайте Ctrl клавиша для выбора несмежных столбцов)
- Щелкните значок Вставлять вкладку и выберите Линия кнопка в Диаграммы группа и выберите Линия
Теперь, когда у нас есть график, нам нужно изменить способ обработки пустых ячеек в Excel. Сделать это:
- Щелкните правой кнопкой мыши диаграмму и выберите Выбрать данные
- Нажмите Скрытые и пустые ячейки
- Пробелы это настройка по умолчанию, как мы обсуждали ранее
- Нуль в нашем примере данные будут отображаться как значение, ноль, ноль, ноль, ноль, ноль, ноль, значение
- Соедините точки данных линией делает именно то, что сказано, соединяет точки данных вместе
На рисунке ниже показан наш график, созданный с использованием Нуль (слева) и Соедините точки данных линией (верно). Пробелы мы уже видели с пустой диаграммой выше.
- Я также привел в порядок горизонтальную ось. щелчок правой кнопкой мыши это и выбрав Ось формата
- Под Параметры оси вкладку, я изменил Основная единица к Фиксированные 7 дней
- Я изменил Ось положения к На отметках чтобы он показывал полную дату
Метод 2: используйте формулу для преобразования пустых ячеек в # N / A
Второй способ убедиться, что данные диаграмм Excel содержат большое количество пробелов или пустых ячеек правильно, — это использовать формулу. Для этого мы воспользуемся тем фактом, что, когда Excel видит # N / A в ячейке, он не включает ее в диаграмму. Формула, которую мы будем использовать в E3:
В ЕСЛИ функция просматривает содержимое ячейки D3 и ЕСЛИ D3 равно «» (другими словами, пустая ячейка), тогда он изменяет текущую ячейку на # N / A. Если Excel что-то обнаружит в ячейке, он просто скопирует ячейку. Например, он скопирует содержимое D3 в E3.
У меня есть статья, в которой обсуждается ЕСЛИ функции более подробно. Я также рассказываю об использовании ЕСЛИ ОШИБКА для подавления ожидаемых ошибок и использования ЕСЛИ с логическими функциями И, ИЛИ а также НЕТ. Это можно найти здесь:
- Затем я копирую формулу в столбец E.
- Затем я создаю диаграмму точно так же, как мы делали выше, используя даты в столбце B и результаты формул в столбце E.
Как видно из рисунка выше, диаграмма правильно отображает данные, даже если Excel использует Показать пустые ячейки в виде Пробелы (настройка по умолчанию для всех диаграмм, созданных в Excel 2007 и Excel 2010).
Затем мы приводили в порядок диаграмму, как делали выше,
- Убираем горизонтальную ось
- Удаление Легенда
- Добавление Заголовок (щелкните значок Макет на вкладке с выбранным графиком выберите Заголовок диаграммы кнопку и выберите нужный тип заголовка)
Заключение
Часто данные поступают к вам из электронных таблиц, которые вы не контролируете, и не всегда отформатированы именно так, как вам хотелось бы. Вместо того, чтобы повторно редактировать данные, чтобы они соответствовали тому, что вы хотите, всегда намного лучше и быстрее, чтобы Excel работал с любыми проблемами форматирования, которые могут иметь данные, и давать желаемые результаты без значительного ручного редактирования с вашей стороны.
В этой статье мы рассмотрели проблему, с которой я часто сталкиваюсь:
Как создавать диаграммы с использованием данных, содержащих большое количество пустых ячеек. Мы рассмотрели два метода решения этой проблемы:
- Изменение способа обработки пустых ячеек в самой диаграмме в Excel
- С использованием ЕСЛИ операторы для изменения пустых ячеек на # N / A (Excel 2007 и Excel 2010 игнорируют ячейки, содержащие # N / A, при создании диаграмм)
С использованием ЕСЛИ операторы в формуле более гибкие, чем изменение способа Excel работать с пустыми ячейками в диаграмме, поскольку вы можете попросить его изменить что-либо на # N / A, а не только пустые ячейки. Это означает, что если вы получаете электронные таблицы с нежелательными данными или данными, которые вам не нужны, вы также можете заставить Excel игнорировать их при создании диаграммы на основе данных.
Я очень надеюсь, что вы нашли эту статью полезной и информативной. Пожалуйста, не стесняйтесь оставлять любые комментарии, которые у вас могут быть ниже, и большое спасибо за чтение!
Эта статья точна и правдива, насколько известно автору. Контент предназначен только для информационных или развлекательных целей и не заменяет личного или профессионального совета по деловым, финансовым, юридическим или техническим вопросам.
Сводные таблицы и диаграммы остаются пустыми после открытия и обновления источников данных в Excel
У меня есть книга Excel 2010, которая содержит два SQL-соединения. Оба соединения настроены на обновление при открытии книги.
В моей книге есть две таблицы данных, каждая из которых связана с одним из этих соединений.
У меня есть 3 сводные таблицы и 3 сводные диаграммы, которые получены из этих двух таблиц.
Когда я открываю свою рабочую книгу, на долю секунды я вижу таблицы и диаграммы, отформатированные так, как можно было бы ожидать, и их последним сохранением.
После обновления таблиц данных все три сводные таблицы и диаграммы становятся пустыми:
Кроме того, когда я щелкаю в любом месте любой сводной таблицы, справа отображается список полей со списком общих столбцов, например:
Если я щелкну правой кнопкой мыши на сводной таблице и выберу «Обновить», то список полей будет обновлен с фактическими именами столбцов, показанными в верхней части каждой таблицы данных, из которой получена эта сводная таблица .
Но по-прежнему нет данных, строк или столбцов в сводной таблице.
Что я делаю, чтобы предотвратить это? Мои сводные таблицы и диаграммы должны показывать обновленные данные и результаты, а не пустые.
Как построить диаграмму по таблице в Excel: пошаговая инструкция
Любую информацию легче воспринимать, если она представлена наглядно. Это особенно актуально, когда мы имеем дело с числовыми данными. Их необходимо сопоставить, сравнить. Оптимальный вариант представления – диаграммы. Будем работать в программе Excel.
Так же мы научимся создавать динамические диаграммы и графики, которые автоматически обновляют свои показатели в зависимости от изменения данных. По ссылке в конце статьи можно скачать шаблон-образец в качестве примера.
Как построить диаграмму по таблице в Excel?
- Создаем таблицу с данными.
- Выделяем область значений A1:B5, которые необходимо презентовать в виде диаграммы. На вкладке «Вставка» выбираем тип диаграммы.
- Нажимаем «Гистограмма» (для примера, может быть и другой тип). Выбираем из предложенных вариантов гистограмм.
- После выбора определенного вида гистограммы автоматически получаем результат.
- Такой вариант нас не совсем устраивает – внесем изменения. Дважды щелкаем по названию гистограммы – вводим «Итоговые суммы».
- Сделаем подпись для вертикальной оси. Вкладка «Макет» — «Подписи» — «Названия осей». Выбираем вертикальную ось и вид названия для нее.
- Вводим «Сумма».
- Конкретизируем суммы, подписав столбики показателей. На вкладке «Макет» выбираем «Подписи данных» и место их размещения.
- Уберем легенду (запись справа). Для нашего примера она не нужна, т.к. мало данных. Выделяем ее и жмем клавишу DELETE.
- Изменим цвет и стиль.
Выберем другой стиль диаграммы (вкладка «Конструктор» — «Стили диаграмм»).
Как добавить данные в диаграмму в Excel?
- Добавляем в таблицу новые значения — План.
- Выделяем диапазон новых данных вместе с названием. Копируем его в буфер обмена (одновременное нажатие Ctrl+C). Выделяем существующую диаграмму и вставляем скопированный фрагмент (одновременное нажатие Ctrl+V).
- Так как не совсем понятно происхождение цифр в нашей гистограмме, оформим легенду. Вкладка «Макет» — «Легенда» — «Добавить легенду справа» (внизу, слева и т.д.). Получаем:
Есть более сложный путь добавления новых данных в существующую диаграмму – с помощью меню «Выбор источника данных» (открывается правой кнопкой мыши – «Выбрать данные»).
Когда нажмете «Добавить» (элементы легенды), откроется строка для выбора диапазона данных.
Как поменять местами оси в диаграмме Excel?
- Щелкаем по диаграмме правой кнопкой мыши – «Выбрать данные».
- В открывшемся меню нажимаем кнопку «Строка/столбец».
- Значения для рядов и категорий поменяются местами автоматически.
Как закрепить элементы управления на диаграмме Excel?
Если очень часто приходится добавлять в гистограмму новые данные, каждый раз менять диапазон неудобно. Оптимальный вариант – сделать динамическую диаграмму, которая будет обновляться автоматически. А чтобы закрепить элементы управления, область данных преобразуем в «умную таблицу».
- Выделяем диапазон значений A1:C5 и на «Главной» нажимаем «Форматировать как таблицу».
- В открывшемся меню выбираем любой стиль. Программа предлагает выбрать диапазон для таблицы – соглашаемся с его вариантом. Получаем следующий вид значений для диаграммы:
- Как только мы начнем вводить новую информацию в таблицу, будет меняться и диаграмма. Она стала динамической:
Мы рассмотрели, как создать «умную таблицу» на основе имеющихся данных. Если перед нами чистый лист, то значения сразу заносим в таблицу: «Вставка» — «Таблица».
Как сделать диаграмму в процентах в Excel?
Представлять информацию в процентах лучше всего с помощью круговых диаграмм.
Исходные данные для примера:
- Выделяем данные A1:B8. «Вставка» — «Круговая» — «Объемная круговая».
- Вкладка «Конструктор» — «Макеты диаграммы». Среди предлагаемых вариантов есть стили с процентами.
- Выбираем подходящий.
- Очень плохо просматриваются сектора с маленькими процентами. Чтобы их выделить, создадим вторичную диаграмму. Выделяем диаграмму. На вкладке «Конструктор» — «Изменить тип диаграммы». Выбираем круговую с вторичной.
- Автоматически созданный вариант не решает нашу задачу. Щелкаем правой кнопкой мыши по любому сектору. Должны появиться точки-границы. Меню «Формат ряда данных».
- Задаем следующие параметры ряда:
- Получаем нужный вариант:
Диаграмма Ганта в Excel
Диаграмма Ганта – это способ представления информации в виде столбиков для иллюстрации многоэтапного мероприятия. Красивый и несложный прием.
- У нас есть таблица (учебная) со сроками сдачи отчетов.
- Для диаграммы вставляем столбец, где будет указано количество дней. Заполняем его с помощью формул Excel.
- Выделяем диапазон, где будет находиться диаграмма Ганта. То есть ячейки будут залиты определенным цветом между датами начала и конца установленных сроков.
- Открываем меню «Условное форматирование» (на «Главной»). Выбираем задачу «Создать правило» — «Использовать формулу для определения форматируемых ячеек».
- Вводим формулу вида: =И(E$2>=$B3;E$2 Готовые примеры графиков и диаграмм в Excel скачать:
Дашборд CSAT расчет индекса удовлетворенности клиентов в Excel.
Пример как сделать шаблон дашборда для формирования отчета по индексу удовлетворенности клиентов CSAT. Скачать готовый дашборд C-SAT для анализа индексов и показателей.
Как сделать еженедельный график в Excel вместе с ежедневным.
Пример создания динамического синхронного еженедельного графика вместе с ежедневным. Синхронное отображение двух таймфреймов на одном графике.
Пример как сделать простой дашборд в Excel скачать шаблон.
Создание простого дашборда для визуальной аналитики по показателям: уровень обслуживания, качество, производительность. Скачать готовый шаблон дашборда – бесплатно.
Динамическая круговая диаграмма для KPI счетчика дашборда Excel.
Пример создания шаблона динамической круговой диаграммы отображения показателей KPI на дашборде. Скачать круговую диаграмму показателей KPI в процентах для дашборда.
В программе Excel специально разрабатывались диаграммы и графики для реализации визуализации данных.