Дополнительные		Результат
вычисления
Сортировка от	Sort Smallest to
минимального к
максимальному Мы сообщаем в формате таблицы с тремя столбцами, содержащими: название продукта, категорию и продажу. Пример В этом примере мы продолжим работу над сводной таблице й, созданной в примере. Отчеты сводной таблицы позволяют. Каждое упражнение начинается с создания вашего собственного рабочего каталога в папке «Мои документы», где вы сохраните свои файлы. Несколько операций с листом Несколько операций листа за раз. Перемещение и копирование листов между файлами. Лаборатория 4 Страница 1 из 17 Содержание. В следующей таблице показаны 10 имен сотрудников определенной компании, их ежемесячная плата и годовая премия, которую они получили.		элементов поля от минимального (1-я позиция)
		к максимальному значению
Сортировка от		Определение порядкового номера значения
максимального к		элемента (ранг) по отношению к значениям Мы хотели бы сортировать по названию. Однако для обычного пользователя системы баз данных требуется специальная подготовка. Сводные таблицы Иногда возникает необходимость суммировать содержимое базы данных в качестве дополнительной таблицы. Таблица, обобщающая выбранные поля базы данных, называется сводной таблицей. Сводные таблицы сводных таблиц позволяют собирать данные, содержащиеся в базах данных, хранящихся в книгах или внешних файлах. Измените размер экрана или. Упражнение 4 Таблицы и программы расчета статистики. Электронная таблица состоит из ячеек, сформированных из пересечения строк, обычно обозначаемых случаями, с столбцами, обозначающими.
минимальному		элементов поля от максимального (1-я позиция)
		к минимальному значению
		Вычисление значений по формуле:
		Значение_ в_ ячейке∙Общий_ итог Д.: Создание диаграмм Ганта. Его большое преимущество - возможность автоматизировать. Электронная таблица используется там, где есть огромное количество труда, повторяющееся в соответствии с определенным шаблоном. Эта база данных состоит из нескольких таблиц, рис. Появится правая сторона экрана. Введение. Таблицу обычно воспринимают как инструмент для выполнения простых или расширенных вычислений с использованием встроенных функций или. Он относится к типу программ, называемых электронными таблицами. Инструменты меняют рабочее время. В поле для календаря выберите ресурс, для которого мы хотим изменить рабочее время, например, введите отпуск или измените рабочее время.
		Итог_ строки∙Итог_ столбца

2-й способ: щелкнуть правой кнопкой мыши по значениям нужного поля в области ΣЗначения [Σ Values] и выбрать:

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

Чтобы воспользоваться эффектами. Сегодня мы научим вас. Отметьте ячейки, которые вы хотите нарисовать. Урок 3: Таблицы и сводные диаграммы. Предположим, у нас есть набор для анализа. Чтобы использовать сводную таблицу, выберите любую ячейку на листе и в меню «Вставка» выберите «Сводная таблица».

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

 Дополнительные вычисления , далее выбрать нужный вариант расчета.

Повторное использование исходного поля в отчете сводной таблицы

По одному и тому же исходному полю можно делать несколько вычислений. Для этого необходимо исходного поле из списка полей сводной таблицы добавить в область значений Σ Значения [Σ

Values] несколько раз, затем настроить параметры поля. Это позволит одновременно видеть как само вычисление, так и вычисление, выполненное на его основе.

Добавление вычисляемых полей в сводную таблицу

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

Базы данных Рабочая карта 1 Базы данных Рабочая карта 1 Создайте папку «Базы данных» для хранения всех данных курса. Создает запрос, который отображает названия и описания. Появится диалоговое окно управления базой данных. Используя это окно, пользователь управляет базой данных и всеми ее элементами.

Что такое электронная таблица? Электронная таблица - это программа для выполнения математических вычислений. С его помощью мы можем также разборчиво провести расчеты в форме. Чтобы воспользоваться преимуществами конкретных действий. Самое трудоемкое - подготовка данных. Задача 1 Математическая группа 2 Следуйте этим шагам в свою очередь. Запустите таблицу.

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

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

Западнопомеранский технологический университет в Щецине Факультет морских технологий и транспорта Кафедра структур, механики и технологии Корабль в оперативной исследовательской лаборатории. Сводные таблицы используются для быстрого анализа данных. Чтобы использовать сводные таблицы, мы должны иметь правильную подготовку данных с заголовками.

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

Microsoft Excel 2010. Уровень 2. Расширенные возможности

Таким образом, при добавлении собственных вычисляемых полей в сводную таблицу очевидны преимущества:

 Исключение потенциальных ошибок в формулах и ссылках на ячейки.

 Возможность добавления и удаления данных из сводной таблицы без изменения исходных вычислений.

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

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

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

 Обеспечение гибкости изменения вычислений при изменении определений элементов.

 Возможность эффективного управления вычислениями.

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

1. Выделить ячейку сводной таблицы;

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

Чтобы быстро изменить отчет и посмотреть, как он продал Добровальского в другие страны, просто измените порядок условий на полях, захватив Продавца и перетащив его через государство. Помимо Ирландии и Германии, ясно, что результаты ее продаж не самые лучшие. Когда вы нажмете на продажу в Ирландии, вы увидите новый лист со столом, содержащим все транзакции Добровальского в Ирландии.

2. На вкладке Параметры, в группеВычисления, раскрыть списокПоля,

элементы и наборы и выбратьВычисляемое поле .

3. В окне Вставка вычисляемого поля :

 В поле Имя ввести имя нового поля.

 В поле Формула составить формулу, начиная со знака= и выбирая

доступные поля в списке Поля (для вставки можно использовать двойной щелчок по полю или выделить поле, затем нажать кнопкуДобавить поле).

Если мы хотим распечатать наши данные, мы возьмем поле Продавца из области «Линии» и переместим его в область «Колонка». Это заставит таблицу распространиться по всему листу. В этом окне мы также можем указать числовой формат, отображаемый во всех ячейках поля. Просто используйте кнопку «Формат номера» и выберите соответствующую валюту, например.

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

 Нажать Добавить.

Настройка внешнего вида вычисляемого поля, как и любого другого поля, происходит в окне

Параметры полей значений.

Редактирование вычисляемого поля

1. На вкладке Параметры, в группеВычисления, раскрыть списокПоля,

элементы и наборы, выбратьВычисляемое поле .

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

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

Когда вы открываете контекстное меню, поле «Строки» и параметр «Группа» показывают другое окно, чем если бы вы группировали даты. Числа сгруппированы по диапазонам, которые мы определяем по началу, концу и объему интервала. В этом случае мы устанавливаем его на 500, поэтому в первой группе будут все транзакции стоимостью от 0 до 500 злотых.

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

Освежить свои знания об основах сводных таблиц можно.

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

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

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

В появившемся окне, в поле Name введите желаемое название поля. В поле Formula введите необходимую вам формулу, вставляя поля из списка внизу и нажимая кнопку Insert field .

Получится таблица с новым расчетным полем. Таблица готова!

Экселевский файл можно скачать отсюда.

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

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

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

  Я уже писал про то, что функция VLOOKUP (ВПР), наверное, самая полезная функция после простых арифметических операций …

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

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

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

  В Excel отсутствует стандартные диаграммы типа Waterfall. Поэтому для создания диаграмм этого типа обычно используют гис…

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

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

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

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

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

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

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

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

Известно, что в БД Microsoft Access вычисляемые поля могут быть созданы в запросе, форме или отчете. Рассмотрим создание вычисляемых полей в отчете базы данных Access 2003. Для ввода Выражения в форму ее необходимо открыть в режиме Конструктора и создать свободное текстовое поле, используя кнопку "ab " (Поле) на панели инструментов. Слева от созданного свободного поля располагается метка, связанная с текстовым полем, в которой отображается порядковый номер введенного поля.

Свободное поле означает, что оно не связано ни с одним полем. В свободное поле можно вводить поле таблицы БД или Выражение. Метка используется для ввода имени поля. При вводе в свободное поле "Выражения", которое вычисляет требуемое значение, оно становится вычисляемым. В вычисляемых полях можно использовать функции, например функцию дата или время, а можно ввести выражение, использующее в качестве входных данных значения полей таблицы БД Access. На форму можно также вставить автономную текстовую метку, т.е. метку не связанную с текстовым полем, для этого надо использовать кнопку "Aa " (Надпись) на панели инструментов.

Рассмотрим создание вычисляемого поля в форме Студенты на примере базы данныхTraining_students_VP . Постановка задачи: в форме БД Access Студенты_конструктор создать одно поле, в котором должна выводиться текущая дата, и другое поле, которое объединяет фамилию, имя и отчество студента. Для решения этой задачи откроем форму Студенты_конструктор в режиме конструктора (рис. 1).

Рис. 1.

Для создания вычисляемого поля в форме БД Access, отображающего текущие дату, необходимо выполнить следующие операции:

• нажать кнопку "ab " (Поле) на панели инструментов, а затем щелкнуть на форме в том месте, где планируется вставить свободное текстовое поле;
• затем установить курсор в поле "Свободный" (рис. 2) и ввести функцию: =Date();
• следующий шаг - изменить название метки текстового поля (рис. 2), для этого надо удалить порядковый номер (в нашем примере Поле30) и ввести слово "Дата: ";

Рис. 2.

• изменить длину метки и текстового поля так, чтобы в них поместилось содержимое;
• отформатировать вычисляемое поле (при необходимости), для этого необходимо щелкнуть правой кнопкой мыши на вычисляемое поле и в контекстном меню выбрать команду Свойства, откроется диалоговое окно "Поле". Форматирование осуществляется в окне "Поле" на вкладке "Все" в строке "Формат поля" (рис. 3);

Рис. 3.

• закрыть окно Поле;
• просмотреть форму БД Access в режиме формы, выполнив щелчок по кнопке Открыть (рис. 4);

Рис. 4.

Для создания еще одного вычисляемого поля в форме БД Access, отображающего выражение, использующее в качестве входных данных значения полей таблицы (Фамилия, Имя, Отчество), надо выполнить следующие действия:

• добавить свободное поле на форму (рисунок 5);

Рис. 5.

• изменить название метки текстового поля на ФИО;
• вызвать построитель выражений, для этого щелкнуть на панели инструментов на пиктограмме "Построитель", откроется окно Построитель, в котором надо выделить "выражение" (рисунок 6) и щелкнуть ОК;

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

1. Создайте свободный элемент управления типа Поле (Text Box) в нужном месте формы.
2. Прямо в поле введите выражение =Date (). Знак равенства обязателен.
3. Откройте окно Свойства (Properties) для этого элемента и задайте для свойства Формат поля (Format) желаемый формат даты, например Длинный формат даты (Long Date).
4. Установите длину поля таким образом, чтобы в нем помещалось необходимое количество символов.
5. Измените название метки этого текстового поля. Созданное поле будет выглядеть, как показано на рис. 9.47.

Рис. 9.47.

Обычно для создания вычисляемых полей выбирают именно элемент управления Текстовое поле, хотя это и необязательно. Допускается использовать для этого любые элементы управления, имеющие свойство Данные (Control Source).

Именно в свойстве Данные (Control Source) должно быть задано выражение, которое вычисляет значение этого элемента. Ввод выражения допускается прямо в элемент управления, как мы и сделали это в примере. Однако, если выражение достаточно длинное, его неудобно вводить прямо в поле. Можно ввести выражение в ячейку свойства Данные (Control Source) в окне Свойства (Properties), а если выражение не помещается в ячейке свойства, просто нажать комбинацию клавиш + и открыть окно Область ввода (Zoom). Еще можно воспользоваться Построителем выражений.

Вычисляемые поля создаются чаще всего в табличных или ленточных формах для отображения столбца с какими-нибудь вычисленными значениями. Например, стоимость товара рассчитывается как значение выражения Цена * Количество. Для отображения таких расчетных данных используют два способа:

• Первый способ основан на том, что форма строится на базе запроса, в который включают вычисляемые столбцы. В форме создается текстовое поле, у которого в качестве источника данных указывается вычисляемый столбец запроса. Поле блокируется, чтобы пользователь не мог изменить данные в нем, ведь эти данные вычисляются в процессе выполнения запроса.
• Во втором случае форма может строиться прямо на базе таблицы. В ней создается текстовое поле, в котором в качестве источника данных указывается выражение, например = [Цена за единицу] * Количество. При этом в выражении необязательно указываются те поля, которые включены в форму, а любые поля базовой таблицы.

Первый способ в большинстве случаев предпочтительнее, т. к. такой запрос может быть использован не только в одной форме, но и в отчетах и в других формах. Примером такой организации расчетов может служить форма "Подчиненная форма заказов" (Orders Subform) базы данных "Борей". Она основана на запросе "Сведения о заказах" (Order Details Extended), в котором создано вычисляемое поле "ОтпускнаяЦена" (ExtendedPrice), содержащее формулу.

В процессе анализа данных с использованием сводных таблиц часто возникает потребность во включении в отчет значений, полученных в результате вычислений, которые выполнялись вне исходного набора данных. Программа Excel обеспечивает пользователя средствами выполнения расчетов в сводных таблицах используя вычисляемые поля и вычисляемые элементы. На рис. 1 показано, как вычисляемое поле может представить данные в ином ракурсе. Сводная таблица отображает объем продаж и время (в часах), затраченное на продажу оборудования на каждом рынке сбыта. Вычисляемое поле, рассчитывающее среднюю выручку за час, расширяет возможности по анализу данных в сводной таблице.

Рис. 1. Необходимо создать в имеющейся сводной таблице вычисляемое поле Средняя выручка за час , которое придаст новый смысл анализируемым данным

Скачать заметку в формате или , примеры в формате

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

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

Теперь посмотрите на рис. 5.1 и спросите себя: «Зачем понадобилось добавлять вычисляемые поля? Почему бы не использовать обычные формулы ячеек или не провести необходимые вычисления непосредственно в исходной таблице для получения требуемой информации?» Чтобы получить ответы на эти вопросы, рассмотрим различные методы, которые можно использовать при создании вычисляемого поля, показанного на рис.1.

Способ 1. Добавление вычисляемого поля в источник данных

Можно добавить вычисляемое поле в источник данных, как показано на рис. 2, позволив сводной таблице использовать это поле как стандартное поле данных. Если расположение информации на листе с источником данных позволяет это сделать, то какое решение может быть наилучшим. Кстати, добавление нового столбца потребует не просто обновить сводную таблицу, а изменить область, на которой основана сводная. Для этого кликните на сводной и пройдите по меню Анализ → Источник данных → Источник данных .

Я стараюсь превратить исходные данные в Таблицу (что сделано на рис. 2 и в прилагаемом Excel-файле). При этом, во-первых, выделяются заголовки, во-вторых, появляются кнопки с фильтрами, что часто полезно для исследования данных, в-третьих, строки окрашиваются через одну, и, наконец, самое важное, – при добавлении строк и столбцов не требуется менять область данных для сводной таблицы. В качестве таковой области указывается не прямоугольный диапазон, а имя Таблицы. В нашем случае – Источник (рис. 3).

Рис. 3. Формирование сводной на основе специального инструмента Excel– Таблица

Но такой способ имеет и недостатки: во-первых, не всегда удобно добавить столбец в исходные данные, во-вторых, ограничиваются возможности в случае изменения структуры исходных данных (например, вы экспортируете данные из 1С, и в новом экспорте на один столбец больше; этот новый столбец затрет ваш вычислительный столбец).

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

Можно добавить вычисляемое поле рядом со сводной таблицей. На рис. 4 каждая ячейка в столбце Средняя выручка за час снабжена формулой, ссылающейся на сводную таблицу. Кстати, если при попытке сослаться на ячейку сводной таблицы у вас в формуле «вылазит» функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, то вы не сможете «протащить» формулу. Чтобы преодолеть это затруднение ознакомьтесь с заметкой .

Рис. 4. При вводе формулы после создания сводной таблицы вы по сути добавляете вычисляемое поле, которое изменяется при обновлении самой сводной таблицы

Несмотря на то что этот метод обеспечивает добавление вычисляемого поля, которое обновляется при обновлении сводной таблицы, любые изменения в структуре сводной таблицы могут привести к тому, что формула станет бесполезной. Например, в результате перетаскивания поля Рынок сбыта в область ФИЛЬТРЫ списка полей сводной таблицы структура отчета изменяется, в результате чего в вычисляемом поле появляется сообщение об ошибке (рис. 5). Если же добавить еще одно поле в область сводной таблицы КОЛОННЫ, столбец Средняя выручка за час будет затерт.

Способ 3. Непосредственная вставка вычисляемого поля в сводную таблицу

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

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

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

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

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

Перед созданием вычисляемого поля необходимо открыть или сгенерировать сводную таблицу. В нашем примере используется сводная таблица, показанная на рис. 1, но без столбца D. Чтобы создать вычисляемое поле, активизируйте диалоговое окно Вставка вычисляемого поля . Для этого кликните на сводной таблице, чтобы активизировать группу контекстных вкладок Работа со сводными таблицами . Перейдите на вкладку Анализ , в группу Вычисления , щелкните на кнопке Поля, элементы и наборы и выберите в меню команду Вычисляемое поле (рис. 7).

На экране появится диалоговое окно Вставка вычисляемого поля (рис. 8). В верхней части диалогового окна имеются два текстовых поля: Имя и Формула . В этих полях следует задать имя вычисляемого поля и создать формулу, указав необходимые поля данных и математические операторы. Вычисляемому полю присвоено описательное имя Средняя выручка за час . Это имя следует выбирать таким образом, чтобы точно охарактеризовать тип выполняемой математической операции. По умолчанию текстовое поле Формула диалогового окна Вставка вычисляемого поля содержит выражение = 0. Перед вводом формулы следует удалить нуль. Выберите одно из полей в области Поля ; в нашем случае – Объем продаж и кликните Добавить поле . Название поле появится в строке Формула . Введите знак деления /, а затем выберите второе поле – Период продаж (в часах) . Кликните Добавить , а затем ОК для активизации нового вычисляемого поля.

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

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

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

Создание вычисляемых элементов

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

В сводной таблице, показанной на рис. 9, указывается общий объем продаж для определенных отчетных периодов (поле Отчетный период ). Представьте себе, что нужно сравнить средний объем продаж последних шести периодов со средним объемом продаж предыдущих семи периодов. Если быть более точным, то нам требуется определить среднее для периодов Р01–Р07 и сравнить его со средним для периодов Р08–Р13.

Поместите указатель в любую ячейку поля Отчетный период, перейдите на контекстную вкладку ленты Анализ в группу Вычисления, и щелкните на кнопке Поля, элементы и наборы . В открывшемся меню выберите команду Вычисляемый объект (рис. 10).

Откроется диалоговое окно (рис. 11). Обратите внимание на то, что в верхней части диалогового окна указано, с каким полем вы работаете. В данном случае это поле Отчетный период . Кроме того, список содержит все элементы поля Отчетный период . Присвойте вычисляемому элементу имя, а затем создайте формулу, указав необходимую комбинацию элементов данных и операторов, которые обеспечат правильный результат. Формула вводится в поле Формула , а необходимые элементы данных, применяемые в ней, выбираются в списке Элементы . Щелкните на кнопке ОК , и вычисляемый элемент добавится в сводную таблицу (рис. 12).

Рис. 11. Диалоговое окно Вставка вычисляемого элемента

Аналогичным образом создайте вычисляемый элемент, который будет представлять средний объем продаж для периодов Р08–Р13. После этого скройте отдельные отчетные периоды, оставив только два вычисляемых элемента. После небольшого форматирования наши вычисляемые элементы будут применяться для сравнения средних объемов продаж за предыдущие и последующие шесть месяцев (рис. 13).

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

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

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

Приоритет выполнения операций. Как и в электронных таблицах, в формулах вычисляемых элементов и полей можно использовать любой оператор: +, –, *, /, %, ^ и т.п. Более того, как и в электронных таблицах, вычисления в сводных таблицах выполняются в соответствии с приоритетом операторов. При выполнении вычисления, в котором комбинируются несколько операторов, например (2+3) *4/50%, Excel оценивает выражение и проводит расчет в определенном порядке. Знание этого порядка убережет вас от многих ошибок.

Итак, порядок выполнения операций в Excel следующий:

• обработка выражений в круглых скобках;
• обработка диапазонов (:);
• обработка пересечений (областей);
• обработка объединений (;);
• выполнение операции отрицания;
• преобразование процентных значений (например, 50% преобразуется в 0,50);
• возведение в степень (^);
• умножение (*) и деление (/); эти операции имеют равный приоритет;
• сложение (+) и вычитание (–); эти операции имеют равный приоритет;
• объединение текстовых данных (&) эта операция называется также конкатенацией;
• выполнение операций сравнения (=, <>, <=, >=).

Равнозначные операторы в одном выражении всегда выполняются в порядке следования (слева направо).

Рассмотрим простой пример. Как известно, выражение (2+3)*4 возвращает результат 20. Если же вы удалите скобки и оставите выражение 2+3*4, то Excel вычислит: 3*4 = 12 + 2 = 14.

Рассмотрим другой пример. Если вы введете в качестве формулы выражение 10^2, т.е. укажете возвести число 10 в квадрат, то программа вернет в качестве ответа значение 100. Если же вы введете выражение –10^2, то можете ожидать возврата значения –100. Однако Excel опять вернет значение 100. Причина в том, что программа выполняет операцию отрицания перед возведением в степень, т.е. значение 10 вначале преобразуется в –10, а результат выражения –10*–10 действительно равен 100. Использование круглых скобок в формуле –(10^2) гарантирует, что программа вначале выполнит возведение в степень и только потом операцию отрицания, вернув ожидаемый результат –100.

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

Функции рабочих листов. Можно применять любую функцию рабочего листа, не использующую в качестве аргументов ссылки на ячейки или именованные объекты. В действительности можно использовать любую функцию рабочего листа, не требующую обязательного применения ссылок на ячейки или именованные объекты. В эту категорию попадают такие функции, как СЧЁТ, СРЗНАЧ, ЕСЛИ, И, НЕТ и ИЛИ.

Константы. В вычислениях, выполняемых в сводных таблицах, можно использовать любые константы. Константы представляют собой статические значения, которые не изменяются во времени. Например, в следующей формуле число 5 является константой: [Продано единиц] *5. Несмотря на то что значение Продано единиц может изменяться на основе доступных данных, число 5 всегда будет одним и тем же.

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

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

Рис. 14. Несмотря на то что вычисляемое поле справедливо для отдельных элементов данных, для промежуточных сумм его применять математически некорректно

В каждом квартале вам нужно получить итоговые объемы продаж для каждого товара, для чего количество проданных единиц товара умножается на цену единицы этого товара. Если вы вначале просмотрите данные за первый квартал, то сразу же определите проблему. Вместо расчета суммы 220+150+220+594, которая равна 1184, сумма количества единиц товара умножается на сумму цен на товары, в результате чего возвращается неверное значение. К сожалению, эту проблему решить невозможно, но ее можно обойти. Исключите промежуточные и итоговые суммы из сводной таблицы, а затем вычислить новую итоговую сумму внизу за пределами сводной.

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

Управление вычислениями и их поддержка в сводных таблицах

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

Изменение и удаление вычислений в сводных таблицах. При изменении параметров вычислений или отсутствии необходимости в вычисляемом поле или вычисляемом элементе можно открыть соответствующее диалоговое окно, чтобы изменить или удалить вычисление. Активизируйте диалоговое окно Вставка вычисляемого поля или Вставка вычисляемого элемента (см. описание к рис. 7 и 10) и выберите в нем раскрывающийся список Имя (рис. 15).

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

Рис. 16. Откройте диалоговое окно Порядок выполнения вычислений

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

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

Рис. 18. Команда Вывести формулы позволяет легко и быстро документировать имеющиеся в сводной таблице вычисления

Заметка написана на основе книги Джелен, Александер. . Глава 5.

Слово Таблица написано с большой буквы, так как это не просто таблица, а отдельный инструмент Excel.

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

Вычисляемые столбцы в таблицах

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

Вычисляемые столбцы нужны для того, чтобы было проще и надежней получить результат каких-то постоянных вычислений. Например, при обращении к таблице, Вы всегда в SQL запросе применяете какую-нибудь формулу (один столбец перемножаете с другим или что-то в этом роде, хотя формула может быть и сложней ), так вот, если в таблице определить вычисляемый столбец, указав в его определении нужную формулу, Вам больше не нужно будет каждый раз писать эту формулу в SQL запросе в инструкции SELECT. Вам достаточно обратиться к определенному столбцу (вычисляемому столбцу ), который автоматически при выводе значений применяет эту формулу. При этом этот столбец можно использовать в запросах также как обычный столбец, например, в секциях WHEHE (в условии ) или в ORDER BY (в сортировке ).

Также важно понимать, что вычисляемый столбец не может быть указан в инструкциях INSERT или UPDATE в качестве целевого столбца.

Создание вычисляемого столбца при создании новой таблицы в Microsoft SQL Server

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

--Создание таблицы с вычисляемым столбцом CREATE TABLE TestTable ( IDENTITY(1,1) NOT NULL, (100) NOT NULL, NULL, NULL, AS ( * ) PERSISTED --Вычисляемый столбец) --Добавление данных в таблицу INSERT INTO TestTable VALUES ("Портфель", 1, 500), ("Карандаш", 5, 20), ("Тетрадь", 10, 50) --Выборка данных SELECT * FROM TestTable

Добавление вычисляемого столбца в существующую таблицу в Microsoft SQL Server

А сейчас давайте допустим, что возникла необходимость знать еще и сумму с учетом некого статического коэффициента (например, 1,7). Но нам об этом сказали уже после того, как мы создали таблицу, иными словами, нам нужно добавить вычисляемый столбец в существующую таблицу.

Добавление вычисляемого столбца в таблицу ALTER TABLE TestTable ADD SummaALL AS ( * * 1.7); --Выборка данных SELECT * FROM TestTable

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

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

Точно также определяется вычисляемый столбец и при создании таблицы с помощью Management Studio, т.е. просто в соответствующем разделе указывается формула.

Удаление вычисляемого столбца из таблицы

В случае, если Вам больше не нужен вычисляемый столбец, то его можно удалить, делается это также, как и удаление обычного столбца.

ALTER TABLE TestTable DROP COLUMN SummaALL;

На этом мой рассказ про вычисляемые столбцы закончен, в своей книге «Путь программиста T-SQL. Самоучитель по языку Transact-SQL » я подробно рассказываю про все возможности языка T-SQL, удачи!