Как исключить ячейку из формулы в excel
Перейти к содержимому

Как исключить ячейку из формулы в excel

  • автор:

Замена формулы на ее результат

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

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

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

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

Замените формулы их вычисляемыми значениями

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

сразу после ввода или вставки значения.

  1. Выберите ячейку или диапазон ячеек, содержащих формулы. Если формула является формула массива, выделите диапазон ячеек, содержащих формулу массива. Выбор диапазона, содержащего формулу массива
    1. Щелкните ячейку в формуле массива.
    2. На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.
    3. Нажмите кнопку Дополнительный.
    4. Щелкните Текущий массив.

    В следующем примере показана формула в ячейке D2, которая умножает ячейки A2, B2 и скидку, полученную из C2, для расчета суммы счета за продажу. Чтобы скопировать фактическое значение вместо формулы из ячейки на другой лист или книгу, можно преобразовать формулу в ячейке в ее значение, выполнив следующие действия:

    1. Нажмите клавишу F2, чтобы изменить ячейку.
    2. Нажмите клавишу F9, а затем нажмите клавишу ВВОД.

    В строке формул показана формула.

    После преобразования ячейки из формулы в значение в строке формул будет отображаться значение 1932,322. Обратите внимание, что 1932,322 является фактическим вычисляемым значением, а 1932,32 — значением, отображаемым в ячейке в формате валюты.

    В строке формул показано значение.

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

    Замена части формулы значением, полученным при ее вычислении

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

    При замене части формулы ее значением эта часть формулы не может быть восстановлена.

    1. Щелкните ячейку, содержащую формулу.
    2. В строка формул

    В Excel в Интернете результаты уже отображаются в ячейке книги, а формула отображается только в строке формул

    Дополнительные сведения

    Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

    Отображение связей между формулами и ячейками

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

    • Ячейки прецедента — ячейки, на которые ссылается формула в другой ячейке. Например, если ячейка D10 содержит формулу =B5, то ячейка B5 является прецедентом для ячейки D10.
    • Зависимые ячейки — эти ячейки содержат формулы, ссылающиеся на другие ячейки. Например, если ячейка D10 содержит формулу =B5, ячейка D10 является зависимой от ячейки B5.

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

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

      Щелкните Параметры> файлов >Дополнительно.

    Примечание: Если вы используете Excel 2007; Нажмите Кнопку Microsoft Office

    , выберите Пункт Параметры Excel, а затем выберите категорию Дополнительно .

    Трассировка ячеек, обеспечивающих формулу данными (влияющих ячеек)

    1. Укажите ячейку, содержащую формулу, для которой следует найти влияющие ячейки.
    2. Чтобы отобразить стрелку трассировки для каждой ячейки, которая напрямую предоставляет данные активной ячейке, на вкладке Формулы в группе Аудит формул щелкните Трассировка прецедентов

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

    Трассировка формул, ссылающихся на конкретную ячейку (зависимых ячеек)

    1. Укажите ячейку, для которой следует найти зависимые ячейки.
    2. Чтобы отобразить стрелку трассировки для каждой ячейки, зависящей от активной ячейки, на вкладке Формулы в группе Аудит формул щелкните Трассировка зависимых

    . Синие стрелки показывают ячейки, не вызывающие ошибок. Красные стрелки показывают ячейки, вызывающие ошибки. Если на выбранную ячейку ссылается ячейка на другом листе или книге, черная стрелка указывает из выбранной ячейки на значок листа

    Просмотр всех зависимостей на листе

    1. В пустой ячейке введите = (знак равенства).
    2. Нажмите кнопку Выделить все.

    Чтобы удалить все стрелки трассировки на листе, на вкладке Формулы в группе Аудит формул щелкните Удалить стрелки

    Проблема: Microsoft Excel издает звуковой сигнал при выборе команды Зависимые ячейки или Влияющие ячейки.

    Если excel сигналит при нажатии кнопки Трассировка зависимых

    или Трассировки прецедентов

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

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

    Как исключить ячейки из диапазона Exel?

    Или иначе: как посчитать те «Б», которые попадают под нужные столбцы?

    5e9f51a52a0eb008245584.jpeg

    • Вопрос задан более трёх лет назад
    • 2395 просмотров

    6 комментариев

    Сложный 6 комментариев

    akelsey

    Заменить Б в коричневых столбцах на Ж
    EvgenyApMr @EvgenyApMr Автор вопроса

    John Smith, возможно, вариант подходящий) Подскажите, как реализовать счетесли так, как вы советуете?
    Предположив, что есть флаги над нужными ячейками

    EvgenyApMr @EvgenyApMr Автор вопроса
    akelsey, а есть ещё варианты? Кроме «заменить на «О», «Р». «?)

    akelsey

    EvgenyApMr,
    Написать VBA процедуру, которая возвращает цвет ячейки (или флаг — если цвета нет 0, иное 1) и в следующей строке сделать промежуточные показания, где присваивать значение из строки «6» и какое-то отличное значение от Б если в строке «4» цвет коричневый.
    Ну и уже счетесли использовать на промежуточной строчке — а так по уму подход менять. Ведь «Б» это что то значит, оно может принимать любое значение — сложно представить зачем кто-то красил верхнюю ячейку в коричневый цвет — усложняя последующий анализ при автоматизации, вместо того что б ввести БК (К-коричневый) и тогда бы формула отработала идеально без всяких ухищрений.

    EvgenyApMr @EvgenyApMr Автор вопроса

    akelsey, Коричневый — это дни недели, сб и вс, под ними есть ещё много таблиц, туда попадает не только «Б», но и другие значения.

    По VBA не подскажите, как должна выглядеть процедура?

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

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

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

    Формула 1: игнорировать пустые ячейки в одном столбце

    =IF( A2 <>"", A2 +10, "") 

    Эта конкретная формула добавляет 10 к значению в ячейке A2 , только если значение в ячейке A2 не является пустым.

    Формула 2: игнорировать пустые ячейки в нескольких столбцах

    =IF(AND( A2 <>"", B2 <>""), A2 + B2 , "") 

    Эта конкретная формула добавляет значения в ячейки A2 и B2 , только если обе ячейки не пусты.

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

    Пример 1: игнорировать пустые ячейки в одном столбце

    Предположим, у нас есть следующие данные в Excel, которые показывают очки, набранные различными баскетболистами:

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

    = A2 +10 

    На следующем снимке экрана показано, как использовать эту формулу:

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

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

    =IF( A2 <>"", A2 +10, "") 

    На следующем снимке экрана показано, как использовать эту формулу:

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

    Пример 2. Игнорирование пустых ячеек в нескольких столбцах

    Предположим, у нас есть следующие данные в Excel, которые показывают набранные очки и подборы, собранные различными баскетболистами:

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

    =IF(AND( A2 <>"", B2 <>""), A2 + B2 , "") 

    На следующем снимке экрана показано, как использовать эту формулу:

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

    Дополнительные ресурсы

    В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *