Check-moscow.ru

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

Как добавить вычисляемое поле в сводную таблицу?

Дополнительные вычисления в сводных таблицах

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

Например, у нас есть простая таблица Excel о продажах с такими данными:

пример сводной таблицы

Представим, что нам необходимо несколько отчетов:

  1. Процентная разбивка продаж.
  2. Продажи по методу начисления.
  3. Продажи с темпами роста.

Рассмотрим, как можно создать такие отчеты, используя дополнительные вычисления в перекрестных таблицах.

Исходная таблица

Создайте исходную таблицу в специальном формате таблицы EXCEL 2007 (см. статью Таблицы в формате EXCEL 2007).

На основе даты продажи в столбце A таблица вычисляет 2 столбца: номер месяца =MONTH() и год =YEAR(). Чтобы отформатировать ячейки в столбце A в виде восьмерок11, был использован пользовательский формат даты [$-419]MMYY;@.

Столбец «План» представляет линейный тренд (не относящийся к данной статье), а столбец «Продано» — фактические продажи.

Вычисляемое поле если

Если вы хотите создать вычисляемое поле в сводной таблице, щелкните по ячейке таблицы.

Затем на вкладке Параметры найдите инструмент Поля, Элементы и Задать вычисляемое поле.

Чтобы свернуть инструмент «Поля, элементы и наборы» до кнопки «Рассчитать», если разрешение экрана низкое или окно программы не развернуто.

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

Вычислить общую сумму в вычисляемом поле. Для этого:

1. Перейти на страницу 1

1. выберите курсором один из вкладов; 2. нажмите на название вклада;

Читайте так же:
Как вставить функцию в Excel?

4. На вкладке Параметры найдите инструмент Поля, элементы и наборы и выберите команду Вычисляемое поле из раскрывающегося списка;

5. В диалоговом окне укажите имя поля — Итоговые значения ;

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

7. Если вы нажмете OK, вы должны получить результат, показанный ниже;

Обратите внимание, что новое поле появляется в списке полей в сводной таблице в правой части экрана, и с ним можно обращаться как с другими полями.

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

Создание графика для сводной таблицы

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

Создайте сводную диаграмму с использованием вычисляемого поля Total в сводной таблице из предыдущей задачи.

Фильтры сводной таблицы

Вы можете фильтровать данные в уже созданной перекрестной таблице, используя область Report Filter в окне Crosstab Fields List.

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

1. Создайте новую сводную таблицу из Листа 1, используя имеющиеся данные;

2. в область строк перетащите поле Отделение банка, в область столбцов – поле Вклад , а в область значений – поле Сумма вклада ;

3. В области фильтра отчета перетащите поле даты подачи документов;

4. В сводной таблице в области фильтра теперь можно выбрать необходимое условие отбора — 1 квартал. (Вы уже выбрали метод группировки по датам, поэтому фильтр отображает ранее выбранный параметр — по кварталам);

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

5. Если вы нажмете OK, должен появиться следующий результат:

Самостоятельное выставление счетов

Откройте таблицу Editorial staff.xlsx. Выполните следующие задания (каждое задание сохраняется в виде отдельной сводной таблицы на отдельном листе):

— Создайте сводную таблицу с расчетом суммы заработной платы по отделам.

Отображение количества сотрудников по отделам в сводной таблице.

— Создайте сводную таблицу, содержащую максимальную заработную плату для каждого отдела.

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

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

— Создайте сводную таблицу, в которой рассчитайте общую сумму вычетов (13% от зарплаты) для каждой должности.

— Показывает среднюю зарплату по отделам.

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

На самом деле, это возможно, и делается это одним из трех способов.

Освежите свои знания основ перекрестной табуляции с помощью .

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

В другом направлении. Построить расчет рядом с крестовым столом — опять же, не самый лучший способ. Если кросс-таблица изменится (а вы должны использовать кросс-таблицы именно тогда, когда ожидаете их изменения)

Третий способ. Встроить вычисляемые поля в саму сводную таблицу. А вот это хороший способ. Делается это следующим образом. Когда сводная таблица уже построена, ставим курсор на сводную таблицу (при этом на ленте появляются две закладки, относящиеся к данной сводной таблице) и нажимаем на появившуюся закладку Options , затем Fields ,Items , &Sets из блока Calculations , в выпавшем меню выбираем Calculatedfield . В 2007 офисе эта кнопка была на той же вкладке Options , только кнопка была отдельная.

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

В появившемся окне в поле Имя введите нужное имя поля. Скопируйте поля из приведенного ниже списка и вставьте их в поле Формула после нажатия кнопки Вставить поле.

Это создаст таблицу с новым полем вычисления. Стол готов!

Вот ссылка для скачивания файла excel.

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

Последние новости

Продвинутый Excel: Почему я перестал пользоваться функцией V…

Я уже писал, что VLOOKUP — это, пожалуй, самая полезная функция после простых арифметических операций….

Как Excel пересчитывает книгу и почему надо избегать волатил…

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

Как быстро построить график Waterfall (водопад)

Стандартной диаграммы водопада в Excel не существует. Поэтому для создания диаграмм водопада обычно используется руководство по диаграммам водопада……

Окно контрольного значения для отслеживания результатов

Часто бывает так, что мы работаем с большими электронными таблицами, которые рассчитывают 1-2 показателя, но зависят .

Bullet chart для сравнения планируемых показателей и фактиче…

Часто бывает необходимо сравнить основные финансово-экономические показатели с ожидаемыми, например, за определенный период, чтобы определить,…

Тацуо Хориучи — 73-летний художник, рисующий в Excel

«Я никогда не использовал Excel на работе, но я видел, как другие люди делают довольно хорошие диаграммы и графики в нем…..

Как возвести число в степень и извлечь корень

Знаете ли вы, что в Excel есть специальный символ ^ (шестерка в английском…

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

Давайте сделаем небольшую трансформацию нашего сводного отчета: удалим значение «Поставщики» и добавим значение «Дата».

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

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

Дата.

После нажатия OK сводная таблица будет выглядеть следующим образом:

Кварталы.

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

Сортировка.

Значения сводного отчета будут изменяться в зависимости от данных, отсортированных следующим образом:

Пример2.

Давайте теперь отсортируем данные по дате. Правая кнопка мыши — «Сортировать». Просто выберите способ сортировки и остановитесь на этом. Но мы пойдем другим путем. Нажмите «Дополнительные параметры сортировки». Вы увидите что-то вроде этого:

Параметры.

Определите параметры сортировки: «Дата в порядке убывания». Нажмите на кнопку «Дополнительно». Установите флажок «Сортировать автоматически при каждом обновлении отчета».

Авто-сортировка.

С этого момента, когда в рабочем листе появляются новые даты, Excel будет сортировать их в порядке убывания (от новых к старым):

Пример3.

Создание явного вычисляемого поля

Явное поле перечисления можно создавать двумя способами:

В области расчета в таблице в модели данных. Вы уже узнали об этом в разделе — Создание вычисляемого поля в таблице.

С ленты PowerPivot в электронной таблице Excel. Об этом методе создания явного перечислимого поля вы узнаете в следующей главе.

В области расчета в таблице в модели данных. Вы уже узнали об этом в разделе — Создание вычисляемого поля в таблице.

На ленте PowerPivot в рабочем листе Excel. Как создать явное вычисляемое поле, вы узнаете в следующем разделе.

Создание явного вычисляемого поля из ленты PowerPivot

Вы можете создать явное перечислимое поле из ленты PowerPivot, выполнив следующее

  • Откройте вкладку POWERPIVOT на ленте книги.
  • Нажмите кнопку Поля расчета в области Расчеты.
  • Нажмите кнопку Новые поля расчета в раскрывающемся списке.

Создание явного расчета

Появится диалоговое окно «Вычисляемое поле».

  • Заполните необходимую информацию, как показано на скриншоте ниже.
Читайте так же:
Как выполнить фильтрацию по месяцам в сводной диаграмме в Excel?

Диалоговое окно "Расчетное поле"

  • Нажмите Проверить формулу.
  • Нажмите OK, только если в формуле нет ошибок.

Здесь можно указать категорию и формат вычисляемого поля. Кроме того, вы можете использовать IntelliSense, чтобы понять, как используются функции, и Auto-Complete для быстрого заполнения имен функций, таблиц и столбцов. Кроме того, подробнее о функции IntelliSense можно прочитать в главе «Формулы DAX». Информацию о формулах DAX можно найти в главе «Формулы DAX».

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