Например, в диапазоне много чисел, и вы хотите быстро скрыть все отрицательные числа, как вы можете с этим справиться? А что, если показывать только положительные результаты расчета формул в Excel? Эта статья покажет вам ответы.
- Показывать только положительные числа в диапазоне с помощью форматных ячеек
- Показывать только положительные числа в диапазоне с условным форматированием
- Показывать только положительные числа в результатах расчета формул
Показывать только положительные числа в диапазоне с помощью форматных ячеек
Вы можете применить Формат ячеек функция, чтобы отображать только положительные числа в выделенном фрагменте в Excel. Пожалуйста, сделайте следующее:
1. Выберите диапазон, в котором вы скроете все отрицательные числа, щелкните правой кнопкой мыши и выберите Формат ячеек из контекстного меню. Смотрите скриншот:
2. В Формат ячеек диалоговое окно, щелкните На заказ в Категория список, введите код формата 0; [Белый] 0 в Тип и нажмите OK кнопка. Смотрите скриншот:
Ноты:
(1) Код формата 0; [Белый] 0 покажет все положительные числа как целые числа. Если вам нужно сохранить десятичные разряды, измените код формата на 0.00; [Белый] 0.00.
(2) В коде формата 0; [Белый] 0, Белый означает отображение отрицательных чисел белым цветом. Если цвет заливки ячеек другой, измените Белый к цвету заливки.
Теперь вы увидите, что в выбранном диапазоне отображаются только положительные числа. Смотрите скриншот:
Быстро изменить все отрицательные значения на ноль или положительные значения в Excel
В некоторых случаях отрицательные значения могут быть недействительными, и вам придется заменить все отрицательные числа на ноль или заменить все отрицательные числа на их абсолютные значения. С Kutools for ExcelАвтора Изменить знак ценностей утилита, вы можете сделать это легко.
Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now
Показывать только положительные числа в диапазоне с условным форматированием
Вы также можете применить Условное форматирование функция, чтобы отображать только положительные числа в диапазоне. Пожалуйста, сделайте следующее:
1. Выберите диапазон, в котором будут отображаться только положительные числа, и нажмите Главная > Условное форматирование > Новое правило. Смотрите скриншот:
2. В диалоговом окне «Новое правило форматирования» выполните следующие действия:
(1) Щелкните, чтобы выделить Используйте формулу, чтобы определить, какие ячейки следует форматировать. в Выберите тип правила список;
(2) Тип = A1 <0 (A1 — первая ячейка в выделенном фрагменте) в Формат значений, где эта формула истинна коробка;
(3) Щелкните значок Формат кнопка. Смотрите скриншот:
3. В диалоговом окне «Формат ячеек» перейдите к шрифт укажите цвет шрифта, такой же, как цвет заливки ячейки в Цвет раскрывающийся список. Смотрите скриншот:
4, Нажмите OK кнопки, чтобы закрыть оба диалоговых окна.
Теперь вы увидите, что в выбранном диапазоне отображаются только положительные числа. Смотрите скриншот:
Показывать только положительные числа в результатах расчета формул
Например, у меня есть таблица, как показано на скриншоте ниже, теперь я хочу вычислить итоги в столбце E, но скрыть все отрицательные результаты вычислений:
Введите формулу =IF(SUM(A2:D2)<0,»»,SUM(A2:D2)) в ячейке E2, а затем перетащите маркер автозаполнения вниз, чтобы заполнить столбец E.
Теперь вы увидите, что все итоги рассчитываются в столбце E, и отображаются только положительные результаты расчетов. Смотрите скриншот:
Статьи по теме:
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (0)
Оценок пока нет. Оцените первым!
|
jbionic Пользователь Сообщений: 9 |
#1 28.12.2014 17:05:08 John Walkenbach old book Microsoft Bible 2003 (Chapter 15, Working with Multicell Array Formulas)
Вопрос: что нужно изменить в этой формуле, если данные Data хранятся не в столбце, а в строке? |
||
|
Юрий М  Модератор Сообщений: 60577 Контакты см. в профиле |
Может всё дело в том, что это формула массива? |
|
vikttur Пользователь Сообщений: 47199 |
|
|
jbionic Пользователь Сообщений: 9 |
#4 28.12.2014 17:13:11
Нет Показать в файле исходный пример из книги? Пожалуйста, смотрите. Прикрепленные файлы
Изменено: jbionic — 28.12.2014 17:24:27 |
||
Я прекрасно понимаю, что вводятся такие формулы для массивов через Ctrl — Shift — Enter|
Alexander88  Пользователь Сообщений: 400 |
#5 28.12.2014 17:17:03 Посмотрите, пример самому пришлось сделать..
Прикрепленные файлы
Изменено: Alexander88 — 28.12.2014 17:22:38 |
||
|
vikttur Пользователь Сообщений: 47199 |
=ЕСЛИ(СТРОКА(A1)>СЧЁТЕСЛИ(Data;»>0″);»»;
) Если порядок не важен, не нужна формула массива: |
|
Юрий М Модератор Сообщений: 60577 Контакты см. в профиле |
#7 28.12.2014 17:42:32
С чего Вы взяли, что я ПЛОХО думаю? Просто уже неоднократно раз люди спотыкались на формуле массива. Вот я и спросил… |
||
|
jbionic Пользователь Сообщений: 9 |
Alexander88
, можно сделать так, чтобы не уголком, а чтобы в любой строке, начиная с 70й, мог ввести формулу для диапазона значений из, скажем, C65:J65? Изменено: jbionic — 28.12.2014 18:03:00 |
|
vikttur Пользователь Сообщений: 47199 |
Покажите свой пример (расположение своих данных). Какая версия Excel у Вас? |
|
jbionic Пользователь Сообщений: 9 |
vikttur, Ваша формула не работает. Во-первых, мне удобней задавать диапазон явно, а не через Data. То есть, например, вместо Data написать C65:J65. У Джона УокенБаха вот эту его длинную формулу можно вводить абсолютно в любом столбце, главное чтобы размер выделения просто совпадал с размером исходного столбца. Так и со строками должно быть. Изменено: jbionic — 28.12.2014 18:12:30 |
|
vikttur Пользователь Сообщений: 47199 |
#11 28.12.2014 17:59:52
Никто Вам не мешает заменить именованный диапазон в формуле на свой.
В данном случае это ссылка на ПЕРВУЮ строку, но не на ячейку. Вы не дали ответы на поставленные вопросы. Допустимо ли выводить список положительных без учета порядка их расположения в исходном массиве? Уокенбах спец в Excel, никто этого не отрицает, но если задачу можно решить проще, без формул массива или без летучих функций — зачем огород городить? «Его длинную формулу можно вводить абсолютно в любом столбце» — даже здесь ограничение: «главное чтобы размер выделения просто совпадал с размером исходного столбца» |
||||
|
jbionic Пользователь Сообщений: 9 |
#12 28.12.2014 18:20:52
I am not a mind-reader. Эти вопросы Вы задаете мне 1й раз. Это замечательный соц. опрос, безусловно важный для ответа на мой вопрос. У меня Excel 2007. Пример данных — пожалуйста. Красным выделен исходный диапазон. В любую из выделенных желтым строку нужно нужно вывести значения красного диапазона, которые >0. Внимание!! Единственное пожелание, чтобы вместо названия диапазона Data можно было указывать диапазон ячеек в явном виде Прикрепленные файлы
Изменено: jbionic — 28.12.2014 21:10:26 |
||
|
vikttur Пользователь Сообщений: 47199 |
#13 28.12.2014 18:25:25
См. сообщение №9. |
||
|
Две формулы, на выбор Прикрепленные файлы
|
|
|
Сергей  Пользователь Сообщений: 11251 |
#15 28.12.2014 18:30:49
vikttur, извиняюсь что встреваю jbionic, а в правила зайти не кошерно или религия запрещает
Изменено: Сергей — 28.12.2014 18:40:49 Лень двигатель прогресса, доказано!!! |
||||
|
jbionic Пользователь Сообщений: 9 |
#16 28.12.2014 18:40:10
Спасибо. Вот это работает. Как Вам повысить рейтинг? |
||
|
«Спасибо» вполне достаточно |
|
|
jbionic Пользователь Сообщений: 9 |
На случай если кому-то пригодится на этом уважаемом форуме с отдельными пользователями сомнительной адекватности более универсальное решение задачи следующее: =IF(ISERR(SMALL(IF(Data>0,TRANSPOSE(ROW(INDIRECT(«1:»&COLUMNS(Data))))),TRANSPOSE(ROW(INDIRECT(«1:»&COLUMNS(Data)))))),»»,INDEX(Data,SMALL(IF(Data>0,TRANSPOSE(ROW(INDIRECT(«1:»&COLUMNS(Data))))),TRANSPOSE(ROW(INDIRECT(«1:»&COLUMNS(Data))))))) Изменено: jbionic — 28.12.2014 20:49:47 |
|
vikttur Пользователь Сообщений: 47199 |
#20 28.12.2014 21:09:00
Спасибо |
||
Использование может использовать фильтр значений для этой цели. Назначьте фильтр значений для столбца « Метка строки» , обычно такой, который содержит одно отдельное значение на строку, например, ACC_NO в вашем примере.
Вот простой пример, который скроет все отрицательные итоги (3-я строка). Я выбрал столбец Quarter для фильтра (подробнее об этом ниже).
Чтобы ввести фильтр значений, просто перейдите в раскрывающемся списке фильтра в столбце, к которому хотите применить фильтр:
Как выбрать, к какому столбцу применить общий фильтр?
Обратите внимание, что я выбрал фильтр QUARTER вместо ITEM, поскольку элементы этого столбца не сгруппированы. Следовательно, в этом случае фильтр будет применен к каждой отдельной строке.
Если бы вместо этого я применил тот же фильтр значений к столбцу «Элемент» (где мы видим, что элемент «В» сгруппирован), тогда фильтр будет применяться к промежуточному итогу для каждой группы. Например, поскольку сумма Q1 и Q2 для элемента B является отрицательной (-5), обе строки элемента B будут отфильтрованы.
В вашем примере все ваши строки уникальны из-за самого левого ACC_NO, поэтому вы получите тот же результат, поместив фильтр в любой столбец.
Последние мысли:
- Если у вас есть несколько итогов в области значений (например, счет и сумма), вы можете выбрать, какой из них фильтровать в диалоговом окне.
- Если у вас есть метка столбца для разделения итогов, фильтр значений будет применен только к общей сумме.
- Применение фильтра значений к метке столбца вместо метки строки выполнит фильтр по отношению к вертикальным итоговым значениям внизу.
На чтение 2 мин Просмотров 1.4к. Опубликовано 18.05.2022
Если вы создаете, например, отчеты, то вам может понадобится суммировать только положительные или отрицательные числа.
Это довольно просто можно сделать, с помощью функции СУММ.
Я продемонстрирую вам разные варианты использования этой функции.
Итак, начнём!
Содержание
- Складываем только положительные значения
- Складываем положительные числа больше 100
- Проверяем на критерий отличный от суммирования диапазон
- Складываем только отрицательные числа
Складываем только положительные значения
Допустим, у нас есть такая табличка:
Наша задача — посчитать сумму только положительных значений
В такой ситуации, формула примет такой вид:
=СУММЕСЛИ(B2:B8;">0";B2:B8)
Мы используем три аргумента:
- Диапазон, который будет проверяться на критерий;
- Критерий;
- Диапазон, который мы суммируем.
Естественно, первый и третий критерии, могут различаться.
Складываем положительные числа больше 100
В прошлом разделе статьи мы сложили только положительные числа, а теперь сложим числа больше 100.
Допустим, у нас есть такая табличка:
Для выполнения такой задачи, формула примет такой вид:
=СУММЕСЛИ(B2:B8;">100";B2:B8)
Готово!
Проверяем на критерий отличный от суммирования диапазон
Ранее я уже говорил, что диапазон, который мы проверяем на критерий, может отличаться от того, который будем суммировать..
Допустим, у нас есть такая табличка.
Наша задача — сложить только те значения из В столбика, для которых значение из С столбика положительно.
Формула принимает такой вид:
=СУММЕСЛИ(C2:C6;">0";B2:B6)
Формула все та же, только мы указали разные диапазоны в 1 и 3 критерии.
Складываем только отрицательные числа
Тут все точно также, как и в первом разделе статьи, только с одним изменением.
Допустим, у нас есть такая табличка:
Наша задача — сложить только отрицательные значения.
Формула принимает такой вид:
=СУММЕСЛИ(B2:B8;"<0";B2:B8)
Критерий отделяет только отрицательные числа, а далее они складываются.
Вы можете изменять критерий, непосредственно, под вашу ситуацию без всяких проблем.
Вот и все! Мы рассмотрели как складывать положительные или отрицательные числа с помощью функции СУММЕСЛИ.
Надеюсь, эта статья оказалась полезной для вас!
|
Выбрать только положительные значения |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
