В процессе анализа данных с использованием сводных таблиц часто возникает потребность во включении в отчет значений, полученных в результате вычислений, которые выполнялись вне исходного набора данных. Программа 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.

Работа с VB проектом (11)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (62)
Разное (37)

Как просмотреть этапы вычисления формул

Часто ли Вам приходилось разбирать чужой файл с непонятными на первый взгляд формулами? Вроде считают, но как? Вроде и разобраться хочется как работает какая-нибудь мега-формула - но как это сделать? Я хочу рассказать о паре простых шагов, которые необходимо сделать, чтобы разобраться в работе любой формулы. Давайте попробуем разобраться на примере формулы из моей статьи: :
=ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1))
Что нам понадобится для начала:

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

Вот теперь можно начать потрошить формулу. В принципе, самый сложный этап уже пройден. Теперь остается только воспользоваться встроенным средством Excel - окно просмотра этапов вычислений формулы. Выделяем ячейку с нужной формулой и:
для пользователей Excel 2007 и более поздних версий:
вкладка Формулы -группа кнопок Зависимости формул -Вычислить формулу (Formulas -Formula Auditing -Evaluate Formula )
для пользователей Excel 2003:
Сервис -Зависимости формул -Вычислить формулу

Появится форма

После каждого нажатия на кнопку Вычислить (Evaluate) будет произведен очередной этап вычислений формулы и в окне формы будет отображен этот этап. Вычисляемая в текущий момент часть формулы(этап) подчеркивается одинарной линией.
Что следует знать: сначала вычисляется самая глубоко вложенная функция, а уже потом самая первая. Самая первая и основная функция у нас будет ИНДЕКС, а самая глубоко вложенная - СЧЁТЕСЛИ. Поэтому на примере нашей формулы следующим этапом будет вычисление функции СЧЁТЕСЛИ и в скобках будет показан результат для этой функции: {0:0:0:0:0 ... 0:0:0} . Т.е. для каждого значения диапазона $A$2:$A$51 будет выведено количество - сколько раз это значение встречается в диапазоне $C$1:C1 . Т.к. это первая строка формулы - то будут все нули:

Далее будет произведено вычисление логического выражения =0: сравнение результата функции СЧЁТЕСЛИ с нулем. Результатом будет ИСТИНА или ЛОЖЬ.

Этот результат(ИСТИНА, ЛОЖЬ) обрабатывается далее функцией ЕСЛИ. А в ЕСЛИ у нас условие: если СЧЁТЕСЛИ равно нулю (т.е. если результат ИСТИНА), то в ЕСЛИ возвращаем номер строки(СТРОКА($A$1:$A$50)), если нет - то вернет ЛОЖЬ.

Т.к. функция НАИМЕНЬШИЙ работает только с числами, игнорируя любые другие значения, то она не будет учитывать ЛОЖЬ(т.к. это логическое значение, а не число), а будет отбирать только числа - что и ложится в основу формулы.

Чтобы в этом примере было более просто разобраться(насколько это возможно), коротко расскажу о принципе работы этой формулы: если значение из диапазона $A$2:$A$51 встречается в диапазоне вывода формулы(на строку выше) $C$1:C1 , то СЧЁТЕСЛИ вернет не нулевое значение и получится ЛОЖЬ. Если такого значения ещё нет - будет нуль и в НАИМЕНЬШИЙ будет передан номер строки. А уже номер строки передается в ИНДЕКС, которая возвращает непосредственно значение по номеру строки. Чтобы более точно понять подобные формулы надо рассмотреть не только формулу из первой ячейки, но и пару следующих.

Помимо кнопки Вычислить в этом окне есть и другие: Шаг с заходом (Step In) и Шаг с выходом (Step Out) . Делают они почти тоже самое, но доступны не для всех видов формул, а лишь для тех, в которых участвуют ссылки на ячейки с другими функциями. Если вычисляемая в настоящий момент функция содержит внутри ссылку на ячейку, в которой записана другая функция или формула - то Шаг с заходом (Step In) выводит в окно вычисления эту функцию(формулу) и активирует ячейку с этой формулой. При этом доступна эта кнопка становится лишь тогда, когда при вычислении основной формулы шаг вычисления доходит до этой самой ссылки на вложенную формулу. Шаг с выходом (Step Out) при этом возвращает к вычислению предыдущей формулы.

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

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

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

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

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

Как произвести вычисления в excel. Форматирование отдельных символов. Создание вычисляемого поля

Как просмотреть этапы вычисления формул