Вычисляемая ячейка содержит константу как убрать

Вычисляемая ячейка содержит константу как убрать

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Excel. Устранение проблем с функцией ВПР

Это вторая глава книги Билла Джелена. Всё о ВПР: от первого применения до экспертного уровня. Раз вы читаете эту главу, видимо, ваша ВПР возвращает ошибку #Н/Д. Этот симптом может быть вызван одной из нескольких проблем. Чтобы найти лекарство, посмотрите, что из следующего применимо к вашему случаю:

• Большинство функций ВПР работают, но есть несколько разрозненных #Н/Д.
• #Н/Д нет в верхней части ваших данных, но #Н/Д встречаются всё чаще, по мере того, как вы двигаться вниз по диапазону. Скорее всего, вы забыли вставить знаки доллара в формулу, чтобы зафиксировать ссылку на таблицу просмотра.
• Все ВПР возвращают #Н/Д.
• ВПР работают для текстовых ячеек, но не для числовых.

Разрозненные #Н/Д. Проблема: отдельные ВПР выдают ошибки #Н/Д (рис. 2.1).

Рис. 2.1. BG33-9 – это новый товар, которого нет в таблице подстановки

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

Такие ситуации встречаются, когда искомое значение не найдено в первом столбце Таблицы. В нашем примере товар BG33-9 не найден в таблице постановки (в диапазоне F2:H29). Добавьте элемент BG33-9 в таблицу подстановки (рис. 2.2). Исправьте формулу ВПР, расширив диапазон таблицы =ВПР(A2;$F$2:$H$30;3;ЛОЖЬ).

Рис. 2.2. Новый товар добавлен в таблицу подстановки

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

• Вставьте новые ячейки в любом месте в середине таблицы подстановки. Например, выделите ячейки F28:H28, кликните правой кнопкой мыши, выберите опцию Вставить… и в открывшемся окне укажите Ячейки со сдвигом вниз.
• В качестве таблицы подстановки выберите не диапазон ячеек ($F$2:$H$29), а столбцы целиком – F:H. Теперь, вы можете добавлять элементы внизу таблицы подстановки без переписывания формул. Excel достаточно сообразителен, чтобы использовать только непустые ячейки при расчетах.
• Выберите любую ячейку в таблице подстановки и нажмите сочетание клавиш Ctrl+Т (Т английское). Это превращает диапазон в таблицу. При вводе новых значений в пустые строки ниже таблицы, она автоматически расширится. Теперь ваша ВПР будет ссылаться не на диапазон, а на ТАБЛИЦУ (рис. 2.3).

Рис. 2.3. Превратите диапазон таблицы подстановки в инструмент Excel ТАБЛИЦА; это позволит автоматически расширять его при добавлении новых строк

Чтобы не пропустить ошибки #Н/Д, отсортируйте данные по столбцу Описание по убыванию (рис. 2.4). Все ошибки соберутся вверху. Для этого выделите столбец D, перейдите на вкладку ДАННЫЕ и в области Сортировка и фильтры нажмите кнопку сортировки. В открывшемся окне выберите Автоматически расширить выделенный диапазон и кликните Сортировать.

Рис. 2.4. Сортировка по убыванию разместит все записи с ошибкой #Н/Д вверху диапазона

Использование функций ЕСЛИОШИБКА, ЕСНД и ЕНД для исключения значений с #Н/Д. Проблема: в отличие от предыдущего примера, элементы с #Н/Д не приводят к потере данных и не требуют дополнения таблицы подстановки. Если не найдено значение, желательно иметь пустую ячейку или ноль.

Существует несколько способов решить эту проблему. Если вы используете Excel 2007 или более позднюю версию, воспользуйтесь функциями ЕСЛИОШИБКА() или ЕСНД(). Если у вас более ранняя версия Excel (или требуется совместимость с более ранней версией) подойдет связка функций ЕСЛИ() и ЕНД().

Если исходная формула =ВПР(A67;$F$2:$H$29;3;ЛОЖЬ), то замените ее на =ЕСЛИОШИБКА(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ);0), или на =ЕСНД(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ);0).

Функция ЕСЛИОШИБКА() появилась в Excel 2007, что стало значительным улучшение по сравнению с предыдущими методами. С ЕСЛИОШИБКА() Excel сначала вычисляет формулу в первом аргументе функции. В 99% случаев возвращается правильный ответ, и работа функции останавливается. В тех редких случаях, когда возвращается ошибка, Excel переходит к обработке второго аргумента функции ЕСЛИОШИБКА(). В нашем примере, просто возвращает ноль.

Если вы предпочитаете в случае ошибки получить пустую ячейку, используйте формулу =ЕСЛИОШИБКА(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ); » » ), но помните, что ячейка не будет по-настоящему пустой. Она будет содержать пустую текстовую строку, что, например, выдаст ошибку при попытке суммировать ее с другими числовыми значениями.

В более ранних версиях Excel 2007 используйте формулу: =ЕСЛИ(ЕНД(ВПР(A67;$F$2:$H$29;3;ЛОЖЬ));0;ВПР(A67;$F$2:$H$29;3;ЛОЖЬ)).

Это ужасная формула, чтобы вычислить ее, нужно вдвое больше времени. Для каждой ячейки Excel сначала вычислит ВПР. Если результат – ошибка #Н/Д, то подставится ноль. Если результат – не ошибка, Excel повторно вычислит ВПР. С тысячами ячеек, содержащими ВПР задержка по времени может быть заметной.

Удалите начальные и конечные пробелы. Проблема: ни одна из моих формул ВПР работает. Я четко вижу, что есть совпадение в таблице подстановки, но Excel не находит эти значения.

Когда вы сталкиваетесь с такой проблемой, скорее всего, почти все ячейки содержат #Н/Д, а не некоторые, как в предыдущем разделе. Проблема может заключаться в том, что, либо элементы в столбце A, либо в столбце F имеют пробелы. Это может произойти, если вы импортировали данные в Excel из 1С, SAP и т.п. Чтобы диагностировать эту проблему, выделите ячейку А2 и нажмите клавишу F2, чтобы перевести ячейку в режим редактирования. Мигающий курсор вставки появится в конце ячейки (рис. 2.5). Если курсор вставки появляется сразу после последнего символа, всё в порядке. Если, как на рис. 2.5, значит присутствуют лишние пробелы. Аналогичную диагностику можно провести и для ячеек в столбце F.

Рис. 2.5. Ни одна из ВПР не работает, хотя совпадения наблюдаются

Воспользуйтесь функцией СЖПРОБЕЛЫ() для удаления начальных и конечных пробелов. Если есть пробелы между словами, функция изменит несколько пробелов подряд на один. Таким образом, формулу =ВПР(A3;$F$3:$G$30;2;ЛОЖЬ) замените на =ВПР(СЖПРОБЕЛЫ(A3);$F$3:$G$30;2;ЛОЖЬ).

Если лишние пробелы отображаются в таблице подстановки, вы можете воспользоваться формулой массива. Наберите с клавиатуры =ВПР(A3;СЖПРОБЕЛЫ($F$3:$G$30);2;ЛОЖЬ). Но не жмите Enter после редактирования. Вместо этого, нажмите одновременно Ctrl + Shift + Enter (подробнее см. Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel). Ваша формула будет выглядеть так . Появятся фигурные скобки.

Подводный камень: формулы массива работают медленнее обычных формул. Это не проблема, если вы хотите произвести впечатление на коллег. Но в реальной жизни, лучше временно создать столбец, например, Н, в который ввести формулу =СЖПРОБЕЛЫ(F3). Затем скопировать его и вставить как значения в столбец F.

Числа и текст не распознаются как одинаковые. Проблема: часть значений ВПР работают корректно, а часть – возвращают ошибку (рис. 2.6).

Рис. 2.6. Часть значений ВПР возвращают ошибку

Вероятно, некоторые ячейки содержат числовые значения, а некоторые – числа, сохраненные как текст. На приведенном выше рисунке вы можете увидеть крошечный зеленый треугольник в левом верхнем углу ячеек В8, В11, В13. Если вы наведете курсор на ячейку, содержащую треугольник, слева появится символ с восклицательным знаком. Если раскрыть список, вы увидите, сообщение Число, сохраненное как текст (рис. 2.7).

Рис. 2.7. Сообщение: Число, сохраненное как текст

То, что числа хранятся в виде текста не обязательно плохо. Например, если это – модели продукта. Проблема в другом: формат данных в столбцах В и F – разный. Когда значение в B3 хранится в виде числа, а в F2 – в виде текста (см. рис. 2.6), ВПР не находит соответствия. Существуют функции, которые позволяют изменять формат данных. Однако, прежде чем их применить, нужно определить, в каком месте данные хранятся в виде чисел, а в каком – в виде текста. Для этого на свободном месте листа введите формулу =ЕЧИСЛО(В2) и =ЕЧИСЛО(F3). Значение ИСТИНА соответствует числу, ЛОЖЬ – тексту.

Если в столбце В хранится текст, а в столбце F – числа, используйте функцию ЗНАЧЕН, которая позволяет изменить текст на число. К сожалению, она выдаст ошибку, если наименование продукта буквенно-цифровое, например, 1120TS. Поэтому, нужно использовать конструкцию ЕСЛИОШИБКА вместе со ЗНАЧЕН (рис. 2.8).

Рис. 2.8. Преобразования текстовых значений в числовые

Если ситуация обратная, то можно использовать функцию ТЕСКТ. Буквенно-цифровые значения при этом не изменятся (рис. 2.9). Второй аргумент функции ТЕКСТ ( » 0 » ) задает формат числа, на основании которого произойдет конвертация в текст (подробнее см. Пользовательский формат числа в Excel).

Рис. 2.9. Преобразования числовых значений в текстовые

Что делать, если проблема не укладывается ни в один из шаблонов? Т.е., когда, и столбец B, и столбец F, содержат комбинацию чисел и текста (рис. 2.10).

Рис. 2.10. Произвольное сочетание числовых и текстовых форматов в столбцах В и F

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

1. Выделите ячейку В2.
2. Нажмите Ctrl+Shift+Стрелка вниз. Выделится весь столбец В с данными.
3. Наберите Alt+Ы, не отпуская Alt, продолжайте набирать П, и наконец Г (или кликните на кнопке Готово). Это позволит преобразовать текстовые значения во всем столбце в числа.
4. Повторите пункты 1–3 для столбца F.

Примечание: сочетание клавиш Alt+Ы+П+Г – это быстрый способ выбрать все настройки по умолчанию в Мастере распределения текста по столбцам (вкладка ДАННЫЕ, область Работа с данными). Использование Мастера позволяет преобразовать все данные обратно в общий формат, что, как правило, решает проблему.

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

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

Контроль дубликатов при использовании ВПР. Проблема: у меня есть первоначальная таблица, и данные о продажах за 19.06.14. Я использовал функцию ВПР, чтобы извлечь продажи из списка, и отразить их в первоначальной таблице. Затем я получил файл с продажами за 20.06.14. Я воспользовался функцией ПОИСКПОЗ и нашел одного нового клиента в списке продаж 20.06.14 – Sun Life Fincl.

Рис. 2.11. Действительно ли это новый клиент?

Этого клиента новым не назовешь. Кто-то в департаменте закупок создал его вместо существующего клиента по имени Sun Life Financial. Чтобы исправить этот дубль, надо заменить в таблице Sun Life Fincl. на Sun Life Financial. Это устранит ошибку #Н/Д. Однако, после этого исправления у вас появилось две строки, которые соответствуют Sun Life Financial.

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

Рис. 2.12. ВПР возвращает первое найденное значение

Если у вас нет полной уверенности, что клиенты в таблице подстановки уникальны, вы не должны использовать ВПР. Вместо этого используйте функцию СУММЕСЛИ. На рис. 2.13 использована следующая формула: =СУММЕСЛИ($A$2:$A$10;E2;$C$2:$C$10). Эта формула предписывает выполнять сканирование по столбцу Клиент (диапазон А2:А10), чтобы найти клиента, соответствующего значению в ячейке Е2. Каждый раз, когда в Excel находит соответствие Выручка из диапазона С2:С10 добавляется к значению в ячейке F2.

Рис. 2.13. Используйте СУММЕСЛИ вместо ВПР для таблиц подстановки, содержащих дубли

Параметр Искомое_значение должен содержать не более 255 символов. Проблема: ни одна из моих ВПР не работает. Я получаю ошибку #ЗНАЧ!

Чтобы выяснить, сколько знаков в ячейке A2, перейдите в любую пустую ячейку и введите =ДЛСТР(А2). ВПР не будет работать, если символов >255. В определенных обстоятельствах вы могли бы усечь содержимое ячейки с помощью формулы =ЛЕВСИМВ(A2;255). Но, иногда это не удастся, если ключи не уникальны при усечении до 255 символов.

Таблица подстановки имеет неверную ориентацию. Проблема: таблица подстановки расположена по горизонтали (рис. 2.14). Можно ли использовать ВПР?

Рис. 2.14. Таблица подстановки расположена по горизонтали

Стратегия: буква “В” в ВПР обозначает вертикальный просмотр. В Excel также есть функция ГПР для горизонтальных таблиц подстановки. Если вы хотите почудачить, то могли бы использовать формулу =ГПР(B3,$F$2:$Q$3,2,ЛОЖЬ).

Альтернативная стратегия: вы, скорее всего, поступите также, как и большинство других пользователей. Скопируете диапазон F2:В3, выделите ячейку F5, выполните специальную вставку, задав параметр Транспонировать. Это предаст таблице подстановки вертикальное положение. Затем вы введете обычную формулу с ВПР. Заметьте, что, начиная с версии 2010 в Excel для транспонирования используется специальный значок:

Рис. 2.15. Значок Транспонировать

Вычисляемые поля и вычисляемые элементы в Excel 2013

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

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

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

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

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

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

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

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

Рис. 2. Предварительно рассчитывать вычисляемые поля в источнике данных обременительно и непрактично

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рис. 7. Создание вычисляемого поля начинается с этого раскрывающегося меню

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

Рис. 8. Настройка вычисляемого поля

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

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

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

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

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

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

Рис. 9. Необходимо сравнить объем продаж за шесть последних периодов с объемом продаж семи предыдущих периодов

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

Рис. 10. Начните создание вычисляемого элемента с выполнения этой команды

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

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

Рис. 12. Вычисляемый элемент успешно добавлен в сводную таблицу

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

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

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

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

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

Рис. 17. Измените порядок вычислений

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

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

[1] Заметка написана на основе книги Джелен, Александер. Сводные таблицы в Microsoft Excel 2013. Глава 5.

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

Вычисляемая ячейка содержит константу как убрать

Здравствуйте, подскажите, каким образом решить проблему. Из таблицы «СПИСОК» мне надо, что бы выбиралась фамилия при заполнении столбца 1 в таблице «ОПЛАТА». При нажатии вычислить формулу, выскакивает табличка на которой написано, что «вычисляемая в настоящий момент ячейка содержит константу». Как бороться с этой бедой?

Здравствуйте, подскажите, каким образом решить проблему. Из таблицы «СПИСОК» мне надо, что бы выбиралась фамилия при заполнении столбца 1 в таблице «ОПЛАТА». При нажатии вычислить формулу, выскакивает табличка на которой написано, что «вычисляемая в настоящий момент ячейка содержит константу». Как бороться с этой бедой? Верон

К сообщению приложен файл: 9695033.xls (29.0 Kb)

Сообщение Здравствуйте, подскажите, каким образом решить проблему. Из таблицы «СПИСОК» мне надо, что бы выбиралась фамилия при заполнении столбца 1 в таблице «ОПЛАТА». При нажатии вычислить формулу, выскакивает табличка на которой написано, что «вычисляемая в настоящий момент ячейка содержит константу». Как бороться с этой бедой? Автор — Верон
Дата добавления — 18.06.2014 в 11:29