Иногда в диапазоне Excel бывает много положительных и отрицательных чисел. Вы можете выбрать, скопировать или удалить эти отрицательные числа. Здесь мы собираемся показать вам хитрые советы по выбору всех отрицательных чисел в Microsoft Excel.
Выберите все отрицательные числа с помощью формулы ЕСЛИ
Выберите все отрицательные числа с помощью Kutools for Excel
Выберите все отрицательные числа с помощью формулы ЕСЛИ
Выберите все отрицательные числа с помощью формулы ЕСЛИ Мы можем применить формулу ЕСЛИ для определения и возврата отрицательных чисел в активной ячейке. Вы можете сделать это, выполнив следующие действия:
Шаг 1. В пустой ячейке введите формулу = ЕСЛИ (A1 <0, a1, “”), и нажмите Enter key, в этом случае введите формулу в ячейку F1.
Шаг 2: Выберите диапазон F1: H7, который имеет тот же размер, что и A1: C7.
Шаг 3: нажмите Главная > Заполнять > вниз и Заполнять > Правильно , чтобы скопировать формулу в каждую ячейку в диапазоне F1: H7.
Теперь он копирует все отрицательные числа в A1: C7 и вставляет каждое отрицательное число в соответствующую позицию в диапазоне F1: H7. См. Следующий снимок экрана:
Выберите все отрицательные числа с помощью Kutools for Excel
Если вы установили Kutools for Excel, Его Выбрать определенные ячейки Инструмент может помочь вам выбрать все отрицательные числа в выделении напрямую.
Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 30 дней. Получить сейчас.
Шаг 1: Выберите диапазон, из которого вы выберете отрицательные числа.
Шаг 2: нажмите Кутулс > Выберите Инструменты > Выбрать определенные ячейки….
Шаг 3: в Выбрать определенные ячейки диалоговом окне укажите следующие параметры:
- Проверить Ячейка вариант;
- Выберите Менее из Укажите тип выпадающий список;
- Введите число 0 после типа Укажите.
Шаг 4: нажмите OK or Применить, затем он выбирает сразу все отрицательные числа в выделении. Смотрите скриншот:
Выберите “Указать ячейки” инструмент может выбрать ячейку или всю строку из указанного диапазона, которые соответствуют определенным критериям. После выбора вы можете применять другие операции (например, форматирование или удаление) к ячейкам или всей строке. Нажмите, чтобы узнать больше…
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (3)
Оценок пока нет. Оцените первым!
Добрый день, уважаемые читатели блога!
В сегодняшней статье мы поговорим о быстром выделении отрицательных значений в выделенном диапазоне таблицы Excel. Первая мысль, которая приходит в голову — использовать условное форматирование. Мысль правильная, но действий придётся выполнять слишком много. Рассмотрим вариант с использованием макроса.
Имеем таблицу с данными, в ней нужно найти и подсветить все отрицательные значения.
Теперь добавим новый модуль:
- на вкладке «Разработчик» нажимаем кнопку «Visual Basic»;
- далее в меню нажимаем «Insert»;
- выбираем «Module».
Перед нами появится окно модуля, куда нужно вставить следующий код.
Sub OtrZnach()
Dim cell As Range
‘ В выделенном диапазоне залить все ячейки красным
For Each cell In Selection
If cell.Value < 0 Then
cell.Interior.Color = RGB(255, 0, 0)
Else
cell.Interior.ColorIndex = xlNone
End If
Next cell
End Sub
Мы сделали следующее — для каждой ячейки в выделении, если её значение меньше нуля, цвет фона будет красным, если нет — оставить фон без изменений, перебор всех ячеек в выделенном диапазоне.
Сохраняем книгу с поддержкой макросов и проверяем работу. Для наглядности будет продемонстрировано два варианта с выделением и без (когда ничего не произойдёт).
Если необходимо изменить цвет заливки, то в строке «cell.Interior.Color = RGB(255, 0, 0)» просто поменяйте значения цифр. Для подбора нужного цвета можно воспользоваться сайтом rgb.to.
Вот и всё, теперь можно находить отрицательные значения в таблице Excel с помощью трех движений мышкой!
|
VistaS Пользователь Сообщений: 33 |
Здравствуйте. Есть таблица с данными, первая строка каждого столбца – дата, даты идут в возрастающем порядке, остальные строчки содержат числовые данные как положительные, так и отрицательные. |
|
Владимир Пользователь Сообщений: 8196 |
Судя по Вашему примеру, вопрос сформулирован неправильно. Нужно найти первое максимальное отрицательное значение. Так? =ИНДЕКС($B$2:$R$2;ПОИСКПОЗ(МАКС(ЕСЛИ(B3:R3<0;$B3:$R3));B3:R3;0)) “..Сладку ягоду рвали вместе, горьку ягоду я одна.” |
|
Для А3, ф-ла массива, копируется вниз. =ИНДЕКС($B$2:$R$2;ПОИСКПОЗ(1;($B$2:$R$2>=СЕГОДНЯ())*(B3:R3<0);)) |
|
|
kim  Пользователь Сообщений: 3139 Игорь |
Можно без массива, если не будет нулей: |
|
vikttur Пользователь Сообщений: 47199 |
“начиная с текущей даты” 🙂 |
|
kim Пользователь Сообщений: 3139 Игорь |
Тады ой, Казанский уже сделал 🙂 |
|
vikttur Пользователь Сообщений: 47199 |
Если без нулей, то не массивный гибрид kim’а и Казанского (вернее, их формул) 🙂 |
|
VistaS Пользователь Сообщений: 33 |
Всем, большое спасибо за помощь, правда, вчера ADSL-модем – гад, не дал прочитать ответы, но сегодня мне эта информация еще нужнее. Особенно порадовало количество ответов, проанализировав все формулы, я не просто получил ответ на конкретный вопрос, я получил знания необходимые для написание подобных формул в будущем самостоятельно. Всем респект) |
|
VistaS Пользователь Сообщений: 33 |
{quote}{login=vikttur}{date=11.08.2010 02:03}{thema=}{post}Если без нулей, то не массивный гибрид kim’а и Казанского (вернее, их формул) 🙂 |
|
vikttur Пользователь Сообщений: 47199 |
Нуль – да, но если пустая? |
|
VistaS Пользователь Сообщений: 33 |
Про пустые не подумал, их в нужном мне файле нету, но все же может измениться. Что получается, написать “немассивную” формулу для таблицы содержащую пустые ячейки нереально? |
|
vikttur Пользователь Сообщений: 47199 |
Используйте доп. столбец. |
|
VistaS Пользователь Сообщений: 33 |
{quote}{login=vikttur}{date=11.08.2010 02:54}{thema=}{post}Используйте доп. столбец.{/post}{/quote} |
|
Ну почему нельзя? Можно. Вопрос только – зачем? =ИНДЕКС($2:$2;НАИБОЛЬШИЙ(ИНДЕКС((СМЕЩ(A3;;ПОИСКПОЗ(СЕГОДНЯ();B$2:R$2)):R3<0)*СТОЛБЕЦ(СМЕЩ(A3;;ПОИСКПОЗ(СЕГОДНЯ();B$2:R$2)):R3););СЧЕТЕСЛИ(СМЕЩ(A3;;ПОИСКПОЗ(СЕГОДНЯ();B$2:R$2)):R3;”<0″))) |
|
|
Не, слегка покороче 🙂 =ИНДЕКС($A$2:$R$2;НАИБОЛЬШИЙ(ИНДЕКС(СТОЛБЕЦ($B$2:$R$2)*($B3:$R3<0););СЧЁТЕСЛИ(СМЕЩ(A3;;ПОИСКПОЗ(СЕГОДНЯ();B$2:R$2)):R3;”<0″))) |
|
|
vikttur Пользователь Сообщений: 47199 |
KL и летучие формулы – совместимо? 🙂 |
|
kim Пользователь Сообщений: 3139 Игорь |
Мда, мне тоже это показалось странным 🙂 – ИНДЕКС() же работает |
|
kim Пользователь Сообщений: 3139 Игорь |
Если уж без “летучек”.. =ИНДЕКС(B$2:R$2;;ПОИСКПОЗ(0=0;ИНДЕКС(ИНДЕКС($B3:$R3;;ПОИСКПОЗ(СЕГОДНЯ();B$2:R$2)):R3<0;);)-1+ПОИСКПОЗ(СЕГОДНЯ();B$2:R$2;)) |
|
Без летучих не выйдет все равно: есть СЕГОДНЯ(), и, имхо, дальше без разницы, формула хоть так, хоть так – пересчитывается |
|
|
kim Пользователь Сообщений: 3139 Игорь |
А ну да, за СЕГОДНЯ() забыл 🙂 |
|
Можно, наверно, СЕГОДНЯ() вывести в отдельную ячейку, и далее ссылаться на неё… ЗЫ. Кто-то сгодня уже говорил -по две пары так и прёт ;)) |
|
|
kim Пользователь Сообщений: 3139 Игорь |
Все равно, интересно, если по скорости сравнить… |
|
lapink2000  Пользователь Сообщений: 2186 |
#23 12.08.2010 01:37:26 Михаил попал в самую точку – искать нелетучий вариант не имело смысла из-за СЕГОДНЯ(). Выносить ее в другую ячейку бесполезно, т.к. формулы с летучими precedents по определению летучи 🙂 Я употребил СМЕЩ() потому, что в русском варианте она короче 🙂 KL |
Бывают случаи, когда количество отрицательных чисел в Вашей электронной таблице может быть подавляющим.
В этом руководстве по Excel мы рассмотрим примеры того, как подсчитать ячейки, в которых содержатся отрицательные числа.
Основная формула
Описание
Чтобы подсчитать количество ячеек в диапазоне, содержащих отрицательные числа, вы можете использовать функцию СЧЁТЕСЛИ. В основной формуле (выше) диапазон представляет собой диапазон ячеек, содержащих числа. В этом примере активная ячейка содержит эту формулу:
Пояснение
Функция СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, соответствующих заданным условиям. В этом случае условие предоставляется как <0, что оценивается как “значения меньше нуля”. Функция возвращает общее количество всех ячеек в диапазоне, соответствующих этому условию. Вы можете легко настроить эту формулу для подсчета ячеек на основе других условий.
Например, чтобы подсчитать все ячейки со значением меньше -10, используйте эту формулу:
|
=СЧЁТЕСЛИ(диапазон;“<-10”) |
Если вы хотите использовать значение в другой ячейке как часть условия, используйте символ амперсанда (&) для объединения следующим образом:
|
=СЧЁТЕСЛИ(диапазон;“<“&a1) |
Если значение в ячейке a1 равно “-5”, условие после объединения будет “<-5”.
На чтение 2 мин. Просмотров 311 Опубликовано 21.05.2021
На листе есть список чисел, которые имеют как положительные, так и отрицательные числа. Если вы хотите найти первое или последнее положительное или отрицательное число в этом столбце, как вы могли бы это сделать? ?
Найти первое положительное/отрицательное число с помощью формулы массива
Найти последнее положительное/отрицательное число с формулой массива
Найдите первый положительное/отрицательное число с формулой массива
Найдите первый положительное/отрицательное число с формулой массива Чтобы получить первое положительное или отрицательное число в списке данных, вам может помочь следующая формула. Сделайте следующее:
1 . В пустой ячейке, помимо ваших данных, введите следующую формулу массива: = INDEX (A2: A18, MATCH (TRUE, A2: A18> 0,0)) , ( A2: A18 – это список данных, который вы хотите использовать), см. снимок экрана:
2 . Затем нажмите одновременно клавиши Ctrl + Shift + Enter , чтобы вернуть первое положительное число в списке, см. Снимок экрана:
: чтобы получить первое отрицательное число в списке, вам просто понадобится следующая формула: = INDEX (A2: A18, MATCH (TRUE, A2: A18 и не забудьте одновременно нажать клавиши Ctrl + Shift + Enter .
Найдите последнее положительное/отрицательное число с помощью формулы массива
Если вам нужно найти последнее положительное или отрицательное число в списке значений, вы также можете применить формулу массива.
1 . Введите следующую формулу в пустую ячейку рядом с вашими данными: = LOOKUP (9.99999999999999E + 307, IF ($ A $ 2: $ A $ 18> 0, $ A $ 2: $ A $ 18)) ( – это список данных, который вы хотите использовать), см. снимок экрана:
2 . Затем нажмите одновременно клавиши Ctrl + Shift + Enter , чтобы вернуть последнее положительное число в списке, см. Снимок экрана:
: чтобы получить последнее отрицательное число в списке, вам просто понадобится следующая формула: = ПРОСМОТР (9.99999999999999E + 307, IF ($ A $ 2: $ A $ 18 и не забудьте нажать Ctrl + Shift + Enter вместе.
Как найти первое/последнее значение больше X в Excel?
Как найти наивысшее значение в строке и заголовок возвращаемого столбца в Excel?
Как найти максимальное значение и вернуть значение соседней ячейки в Excel?
Как найти максимальное или минимальное значение на основе критериев в Excel?
