Ref-sgain.ru

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

Как скрыть промежуточные итоги в сводной таблице?

Как в Excel использовать сводную таблицу вместо формул

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

На рисунке 103.1 показан набор данных, содержащий оценки студентов за тест. Цель — рассчитать средний балл всех студентов, а также средний балл для каждого пола.

Рис. 103.1. Нужно найти самый лучший способ вычислить средний балл по тесту для парней и девушек

Рисунок 103.1: Необходимо найти оптимальный способ расчета среднего тестового балла для мальчиков и девочек

Вставка промежуточных итогов

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

  1. Выберите любую ячейку в столбце C.
  2. Щелкните правой кнопкой мыши и во всплывающем меню выберите Сортировка ►Сортировать от A до Z.
  3. Выберите Данные ► Структура ► Промежуточный итог, чтобы открыть диалоговое окно Промежуточный итог.
  4. В диалоговом окне Промежуточные итоги выберите элемент Пол из списка Каждое изменение, элемент Среднее из списка Операция и установите флажок Добавить итоги по оценке.

Результат добавления промежуточных итогов показан на рис. 103.2. Обратите внимание, что Excel также создает контур, так что вы можете скрыть подробности и просматривать только итоги. Формулы, добавленные Excel, используют функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ со значением 1 в качестве первого аргумента (1 обозначает среднее). Вот эти формулы:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;B2:B13)
=ПРОМЕЖУТОЧНЫЕ.ИТ0ГИ(1;B15:B28)
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;B2:B28)
Формула в ячейке В30 вычисляет общее среднее и использует диапазон, который включают в себя другие две формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ в ячейках В14 и В29 . Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ игнорирует ячейки, которые содержат другие формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

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

Рис. 103.2. Excel добавляет промежуточные итоги автоматически

Рис. 103.2: Excel автоматически добавляет промежуточные итоги

Использование формул

Другой способ вычисления средних показателей — использование формул. Формула для вычисления среднего для всех студентов проста: =SIGNAL(B2:B27) .

Чтобы узнать среднее значение в зависимости от пола, можете воспользоваться функцией СРЗНАЧЕСЛИ для создания следующих формул:
=СРЗНАЧЕСЛИ(C2:C27;»Женский»;B2:B27)
=СРЗНАЧЕСЛИ(C2:C27;»Мужской»;B2:B27)

Функция СРЗНАЧЕСЛИ была введена в Excel 2007. Если вам нужно, чтобы ваша книга была совместима с более ранними версиями, используйте эти формулы:
=СУММЕСЛИ(C2:C27;»Женский»;B2:B27)/СЧЕТЕСЛИ(C2:C27;»Женский»)
=СУММЕСЛИ(C2:C27;»Мужской»;B2:B27)/СЧЕТЕСЛИ(C2:C27;»Мужской»)

Использование сводной таблицы

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

  1. Выберите любую ячейку в диапазоне данных и выполните команду Вставка ► Таблицы ►Таблица, чтобы открыть диалоговое окно Создание вкладки таблицы.
  2. В диалоговом окне убедитесь, что Excel выбрал правильный диапазон данных, и укажите ячейку в текущем рабочем листе в качестве местоположения. Ячейка E1 будет хорошим выбором.
  3. Нажмите OK, и Excel отобразит список полей в сводной таблице.
  4. В списке полей перетащите Пол в раздел Заголовки строк внизу.
  5. Перетащите оценку на значения. Excel создает сводную таблицу, но отображает функцию AMOUNT, а не функцию WIDE.
  6. Чтобы изменить используемую функцию подведения итогов, щелкните правой кнопкой мыши на любом значении в сводной таблице и выберите пункт Итоговые значения ► Среднее из всплывающего меню (Рисунок 103.3).

Рис. 103.3. Эта сводная таблица вычисляет средние значения без использования формул

Рисунок 103.3: Эта сводная таблица позволяет рассчитать средние значения без использования формул.

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

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

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

Сортировка данных в сводной таблице или сводной диаграмме Excel

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

При сортировке данных необходимо учитывать следующее:

  • Выбранный язык влияет на окончательный порядок ранжирования. Убедитесь, что в разделе «Параметры языка и региона» и «Региональные параметры» на панели управления установлен правильный и подходящий языковой стандарт. Подробную информацию о том, как изменить языковой стандарт, можно найти в справке Windows
  • Окончательно обработанные данные могут содержать начальные пробелы, которые также оказывают непосредственное влияние на результаты сортировки. Для получения оптимальных результатов следует удалить эти пробелы перед сортировкой данных
  • Текстовые значения следует сортировать без учета регистра
  • Сортировка по определенному признаку форматирования, такому как шрифт или цвет ячеек, также недопустима. Кроме того, не следует сортировать данные по индикаторам условного форматирования (например, по набору значков).

В разделах ниже вы найдете информацию о сортировке сводной диаграммы и сводной таблицы в классическом приложении Excel, входящем в Office 2019 и предыдущие выпуски пакета.

Сортировка данных в классическом редакторе таблиц Excel 2019

Чтобы отсортировать данные в электронной таблице Excel, выполните следующую последовательность шагов:

  1. Нажмите на маленькую стрелку рядом с именами столбцов или строк в списке перекрестных таблиц
  2. Нажмите на поле в столбце или строке, которую вы хотите отсортировать.
  3. Нажмите на стрелку в списке Column or Row Names, а затем выберите соответствующий вариант.
  1. Чтобы отсортировать данные в порядке убывания или возрастания, нажмите кнопки «Сортировать от I до A» или «Сортировать от A до Я».
Читайте так же:
Как создать блок-схему в Excel?

Значения даты и времени будут отсортированы от нового к старому или от старого к новому, числа — от максимального к минимальному, а текстовые элементы — по алфавиту.

Сортировка по столбцу, не имеющему кнопки со стрелкой

В результате выбора команды «Сортировка» и выбора методики сортировки в Excel можно сортировать по промежуточным итоговым значениям или по отдельным значениям. Порядок сортировки применяется ко всем ячейкам в столбце, содержащем ячейку, которую вы хотите отсортировать, на одном и том же уровне.

В следующем примере данные сортируются в алфавитном порядке (дорожная сумка, шлем).

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

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

Задание собственных, персонализированных настроек сортировки

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

Сводные таблицы. Анализ данных. Разработчик сводной таблицы. Экспорт сводной таблицы в Microsoft Excel или другие приложения , страница 15

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

Пользовательские свойства поддерживаются только в OLAP-источниках данных.

Скрытие или отображение свойств элемента

Как и в отчетах, вы можете отображать свойства элементов во всплывающих подсказках.

Щелкните поле, для которого нужно показать или скрыть дополнительные свойства.

2 На панели инструментов сводной таблицы нажмите [Команды и настройки], а затем перейдите на вкладку Свойства элемента.

3. в списке Свойства объекта выберите свойство, для которого вы хотите изменить настройки.

4. Выберите один из следующих элементов в списке Показать в.

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

O Отчет. Чтобы отобразить имя свойства и значение рядом с именами элементов. o Всплывающая подсказка. Чтобы отобразить имя и значение свойства как часть всплывающей подсказки для каждого элемента. o Все. Для отображения имени и значения свойства в отчете таким же образом, как и всплывающая подсказка. o Нет Если вы хотите скрыть имя и значение свойства.

5. Непрерывно повторяйте шаги 3 и 4 для всех дополнительных свойств выбранного поля.

— Если вы решили отображать свойства в виде всплывающих подсказок, убедитесь, что флажок Tooltip на вкладке Report диалогового окна Commands and Settings (Команды и настройки) установлен.

— Можно изменить порядок отображения дополнительных свойств в отчете или подсказке.

— Вы можете изменить формат имени и значения дополнительного свойства. Формат свойства не влияет на отображение во всплывающей подсказке.

Источники данных OLAP не поддерживают никаких пользовательских свойств.

Определение порядка, в котором отображаются свойства элемента

file://E:Documents and SettingsAdministratorLocal SettingsTemp

Просмотр сведений и элементов

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

2. На панели инструментов сводной таблицы нажмите кнопку Команды и параметры и перейдите на вкладку Свойства элемента.

Порядок, в котором свойства перечислены в списке «Свойства элемента», — это порядок, в котором свойства появляются в отчете или во всплывающей подсказке.

3. Выберите свойство, которое будет отображаться вверху, затем нажмите кнопку «Наверх».

* пока свойство не появится в качестве первого элемента в списке.

4. Выберите свойства, которые необходимо переместить, и переместите их вверх * или вниз *.

Предупреждение. Если новые свойства добавляются после определения порядка списка, новые свойства отображаются в нижней части списка до тех пор, пока не будет определен новый порядок.

file://E:Documents and SettingsAdministratorLocal SettingsTemp

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

Просмотр итогов в сводной таблице

Отображение итогов в сводной таблице

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

► Общее число месторождений

Поля данных, извлеченные из источника данных или полученные из других полей, являются итоговыми полями. Например, если источник данных содержит поле Sales, которое отображает стоимость каждой продажи, вы можете добавить поле Total: Sales, которое вычисляет общую стоимость для каждой категории продуктов в сводной таблице, или поле Quantity: Sales, которое подсчитывает общее количество продаж для каждой категории продуктов.

В списке полей суммарные поля появляются вверху под значком «Итого». Поля «Сумма продаж» и «Количество единиц» могут быть включены в сводную таблицу в качестве итоговых полей.

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