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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

   

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

   

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

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

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

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

   1. Щелкните ячейку с формулой.
   2. В строка формул выберите часть формулы, которую нужно заменить ее вычисляемой величиной. При выборе части формулы, которую вы хотите заменить, убедитесь, что вы включили все операнд. Например, при выборе функции необходимо выбрать имя функции целиком, открываемую скобки, аргументы и закрываюю скобки.
   3. Чтобы вычислить выбранную часть, нажмите F9.
   4. Чтобы заменить выбранную часть формулы ее вычисляемой величиной, нажмите ввод.

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

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

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

   покупка

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

   

   Исключить ячейки в столбце из суммы с формулой

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

   1. Выберите пустую ячейку для сохранения результата суммирования, затем введите формулу. = СУММ (A2: A7) -SUM (A3: A4) в панель формул, а затем нажмите Enter ключ. Смотрите скриншот:

   

   Заметки:

   1. В формуле A2: A7 — это диапазон столбцов, который необходимо суммировать. А A3: A4 — это непрерывные ячейки, которые вы исключите из суммирования.

   2. Если необходимо исключить ячейки из суммирования, расположенные, например, в прерывистых ячейках, вам нужно исключить значения в ячейках A3 и A6 при суммировании диапазона A2: A7, примените следующую формулу: = СУММ (A2: A7) -A3-A6 . Смотрите скриншот:

   

   Статьи по теме:

   • Как исключить определенную ячейку или область из печати в Excel?
   • Как исключить значения в одном списке из другого в Excel?
   • Как найти минимальное значение в диапазоне без нулевого значения в Excel?

   Лучшие инструменты для офисной работы

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

   Office Tab Добавляет в Office интерфейс с вкладками и значительно упрощает вашу работу

   • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
   • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
   • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

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

   БМВ, простите, я не знаю, очевидно ли это для Вас. может так, как Вы написали, работает в гугл доксе? (с двоеточием в середине, а не с точкой с запятой) но в Экселе так не работает. сама удивилась. стала пробовать — выделяются правильные диапазоны, а результат показывается исходя из той ячейки, которая исключена (само вот это меня удивило). Даже пример могу приложить

   если это опечатка, и в гугл доксе будет работать некорректно, а с точкой с запятой будет работать, то для автора на всякий случай ещё напишу, что чтобы вручную не писать адреса ячеек, можно при выделении диапазонов зажать ctrl — тогда не связанные диапазоны выделятся.. по крайней мере в обычном Экселе так работает))

   Прикрепленные файлы

   • для БМВ в теме Elvrik о парадоксе двоеточия.xlsx (8.27 КБ)

   «Просите, и дано будет вам; ищите, и найдете; стучите, и отворят вам» (Мф. 7. 7-9)
   Сообщений: 22305 Регистрация: 28.12.2016
   Excel 2013, 2016
   03.08.2018 21:48:04
   nastya2018, это вечер пятницы + жара + скоро гроза.
   По вопросам из тем форума, личку не читаю.
   Пользователь
   Сообщений: 7658 Регистрация: 15.02.2016
   03.08.2018 21:53:12

   Цитата
   БМВ написал: + жара

   Это сколько же? +25 ? У нас сегодня +34 блин.
   Модераторы, прошу прощения за оффтоп.

   Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл

   Пользователь
   Сообщений: 88 Регистрация: 14.04.2018
   03.08.2018 21:56:35

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

   «Просите, и дано будет вам; ищите, и найдете; стучите, и отворят вам» (Мф. 7. 7-9)
   Сообщений: 22305 Регистрация: 28.12.2016
   Excel 2013, 2016
   03.08.2018 22:02:52

   Off
   Bema, как это не удивительно, но под 30 стоит вторую неделю. А у нас дресскод, Галстук, пиджак …..

   Цитата
   nastya2018 написал: я этот вечер запомню.

   — дайте вечеру название — не верь глазам своим 🙂
   Изменено: БМВ — 03.08.2018 22:07:27
   По вопросам из тем форума, личку не читаю.
   Пользователь
   Сообщений: 10 Регистрация: 03.08.2018
   03.08.2018 22:40:46

   Цитата
   БМВ написал: ну не знаю зачем первая часть но MIN(C1:C9;C11:C15)

   К сожалению не работает
   Сообщений: 22305 Регистрация: 28.12.2016
   Excel 2013, 2016
   03.08.2018 22:45:00

   Цитата
   Elvrik написал: не работает

   что конкретно?
   По вопросам из тем форума, личку не читаю.
   Пользователь
   Сообщений: 10 Регистрация: 03.08.2018
   03.08.2018 22:45:45
   БМВ,выделяет последнюю ячейку в столбце.
   Сообщений: 22305 Регистрация: 28.12.2016
   Excel 2013, 2016
   03.08.2018 22:47:58

   Elvrik, Я спрашиваю что конкретно не работает, а не то, какой результат вы получили в результате ваших тайных действий.

   Читать:

   Где взять qr код для яндекс ключа