Как сделать отрицательное число в excel

Добавил пользователь Алексей Ф.
Обновлено: 05.10.2024

Большинство людей, работающих с электронными таблицами Excel, имеют дело с числами в больших / малых наборах данных. И когда вы работаете с числами, у вас будут все их типы (положительные, отрицательные, десятичные, дата / время).

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

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

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

Так что, если вам интересно, продолжайте читать!

Умножьте на (-1), чтобы преобразовать отрицательное число в положительное

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

Но вы также должны убедиться, что вы умножаете только отрицательные числа, а не положительные.

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

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

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

Если у вас также есть текстовые значения в наборе данных, эта функция будет игнорировать это (и будут изменены только отрицательные значения)

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

Используйте функцию ABS, чтобы изменить все отрицательные числа на положительные

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

.. функция ABS

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

Ниже приведена формула, которая сделает это:

Вышеупомянутая функция ABS не влияет на положительные числа, но преобразует отрицательные числа в положительные.

Умножьте с помощью специальной вставки, чтобы перевернуть знак

Если вы хотите изменить знак числа (т. Е. Изменить отрицательный на положительный и положительный на отрицательный), вы также можете использовать эту специальную технику умножения вставки.

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

Ниже приведены шаги по изменению знака с помощью специальной вставки:

Вы могли бы заметить, что указанные выше шаги мгновенно меняют знак числа на противоположный (т. Е. Положительные числа становятся отрицательными, а отрицательные — положительными).

Но что, если вы хотите преобразовать только отрицательные числа в положительные, а не наоборот?

В этом случае вам каким-то образом сначала нужно выбрать все отрицательные числа, а затем выполнить указанные выше шаги.

Вот как выбрать в Excel только отрицательные числа:

Этот метод имеет два преимущества по сравнению с методом формул (два метода, описанные до этого):

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

Мигающая заливка для удаления отрицательного знака

Flash Fill — это новая функция, представленная в Excel 2013.

Это позволяет вам быстро идентифицировать закономерности, а затем дать вам результат, в котором шаблон был применен ко всему набору данных.

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

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

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

Ниже приведены шаги по замене отрицательных чисел на положительные с помощью Flash Fill:

В поле рядом с таблицей с данными введите ожидаемый результат вручную. В этом примере я вручную введу 874
В ячейке под ним введите ожидаемый результат (162 в этом примере).
Выберите обе ячейки
Поместите курсор в нижнюю правую часть выделения. Он изменится на значок плюса
Щелкните и перетащите, чтобы заполнить столбец (или дважды щелкните)
Щелкните значок Параметры автозаполнения.
Нажмите на Flash Fill.

Вышеупомянутые шаги дадут вам ожидаемый результат, где отрицательный знак был удален.

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

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

Преобразование отрицательных чисел в положительные одним щелчком мыши (VBA)

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

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

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

Не волнуйтесь, я покажу вам точные шаги, чтобы запустить его.

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

В приведенном выше коде используется цикл For Next для просмотра каждой выделенной ячейки. Он использует оператор IF, чтобы проверить, является ли значение ячейки отрицательным или нет. Если значение отрицательное, знак меняется на противоположный, а если нет — игнорируется.

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

Теперь позвольте мне показать вам, как добавить этот код на панель быстрого доступа (шаги одинаковы, независимо от того, сохраняете ли вы этот код в отдельной книге или в PMW)

Теперь у вас будет значок макроса в QAT.

Чтобы использовать этот макрос одним щелчком мыши, просто сделайте выбор и щелкните значок макроса.

Примечание. Если вы сохраняете код макроса VBA в книге, вам необходимо сохранить книгу в формате с поддержкой макросов (XLSM).

Расчет отрицательных чисел с использованием электронных таблиц Excel является точным методом.

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

Расчет отрицательных чисел

Шаг 1

Введите отрицательное число в ячейку электронной таблицы Excel, используя знак минус (-) перед номером.

Шаг 2

Используйте арифметический оператор, знак минус (-), чтобы вычесть число с помощью формулы.

Примером является формула "= 25-10", которая даст результат "15".

Шаг 3

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

Шаг 4

Изменить форматы отрицательных чисел

Шаг 1

Выберите число или диапазон чисел, чтобы изменить способ отображения этих чисел в Excel.

Шаг 2

Шаг 3

Шаг 4

Шаг 5

Шаг 6

Шаг 7

Шаг 8

Как использовать Excel для расчета APR

Microsoft Excel включает функцию, которая поможет вам рассчитать годовую процентную ставку (APR) для кредита. Вы можете использовать эту функцию, если вы знаете сумму ежемесячного платежа .

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

Расчет направления между двумя точками на карте является важным навыком навигации для пилотирования и ориентирования на местности. В эпоху паруса корабельные офицеры стояли на страже .

Как сделать отрицательные числа = 0 в Excel

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

Если вы работаете с большим количеством чисел в Excel, рекомендуется выделять отрицательные числа красным цветом. Это упрощает чтение данных.

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

Использование условного форматирования
Использование встроенного форматирования чисел
Использование произвольного форматирования чисел

Давайте подробно рассмотрим каждый из этих методов.

Выделите отрицательные числа красным цветом - использование условного форматирования

Правила условного форматирования Excel применяются к ячейке в зависимости от хранимого в ней значения.

В этом случае мы проверим, меньше ли значение в ячейке 0 или нет. Если это так, то ячейка может быть выделена указанным цветом (который в данном случае будет красным).

Вот как это сделать:

Все ячейки со значением меньше 0 будут выделены светло-красным цветом с темно-красным текстом в нем.

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

Внимание! Условное форматирование непостоянно, что означает, что оно пересчитывается при каждом изменении в книге. Хотя влияние незначительно на небольшие наборы данных, вы можете увидеть некоторое перетаскивание из-за этого при применении к большим наборам данных.

Выделите отрицательные числа красным цветом - Использование встроенного форматирования чисел Excel

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

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

Что-то вроде того, что показано ниже:

Это автоматически добавит две десятичные точки и сделает отрицательные числа красными со знаком минус.

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

Выделите отрицательные числа красным цветом - использование пользовательских форматов чисел

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

Это заставит отрицательные числа отображаться красным цветом, а все остальное останется прежним.

Как это работает:

В Excel можно настроить четыре типа форматов:

Эти форматы разделяются точкой с запятой.

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

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

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

Но если вы напишете 0.00; -0.00; 0.00; Общие, положительные числа отображаются с двумя десятичными знаками, отрицательные - с отрицательным знаком и двумя десятичными знаками, ноль - как 0.00, а текст - как обычный текст.

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

Если вы упомянули только:

Один формат: применяется ко всем четырем разделам. Например, если вы просто напишете General, он будет применен ко всем четырем разделам.
Два формата: первый применяется к положительным числам и нулям, а второй - к отрицательным числам. Текстовый формат по умолчанию становится общим.
Три формата: первый применяется к положительным числам, второй - к отрицательным, третий - к нулю, и текст исчезает, поскольку для текста ничего не указано.

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

Если в Excel, LibreOffice, Google Sheets и т.д. нужно изменить отрицательные числа на положительные, то для этого можно использовать одну универсальную методику с применением вспомогательных столбцов.

Рассмотрим на примере следующей таблицы:

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

Прописав эту формулу, протягиваем её на соответствующий таблице диапазон:

Всё готово. Вспомогательные временные столбцы после этого можно удалить.

Это такой универсальные метод работающий, пожалуй, во всех подобных Экселю программах.

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

MS, Libreoffice & Google docs

483 поста 12.9K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

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

Утверждения вроде "пост - отстой", это оскорбление автора и будет наказываться баном.

Не секрет, что формулами можно сделать почти всё.

Того же результата можно добиться и без формул:

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

2. Если бесят нули, то применяем формат "0;-0;". Вообще, числовые данные оставлять с форматом по умолчанию - зло.

1. Копируем из любой ячейки значение "-1".

2. Выделяем таблицу, CTRL+G, выделить, константы-чилса

3. Специальная вставка, умножить.

Уверен, есть и другие способы

Можно формулу писать не "=A1*-1" (бред какой-то), а просто "=-A1". Но это естественно для тех, кто видел хоть один язык программирования в школе. А не для программистов это не очевидно, да

Спасибо. Только осваиваю формулы, мне отрицательные данные не нужны, но для меня стало более ясно, что означают условия в скобках после ЕСЛИ.

Вот у меня вопрос к ТС.
Как в libre office подсчитать количество ячеек диапазона, по условию цвета шрифта.
Например, 10 ячеек с зеленым цветом шрифта, 5 красным, 4 синим.
. Не MS Excel .

Добрый день, заранее извиняюсь что вопрос немного не в тему, но прошу совета, есть целый архив бумаг, а именно однотипных актов, и необходимо их все вбить в реестр, можно ли как нибудь через скан, в формате пдф, автоматически вычленять данные из определённого участка скан-копии, такие как номер и подзаголовок акта, в таблицу эксель?
Р. S. Есть программа ФайнРидер, если это как то может помочь

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

Падажите. А чем вам функция ABS() не понравилась? Зачем все эти извращения с -1

Как, блин, копировать из ячейки excel, чтобы чтобы не было символа переноса строки в конце копируемой ячейки?! =))

Видео из оригинального поста довольно шакальное и с весьма устаревшей информацией и довольно сложный способ. Обратите внимание на стандартную функцию "Мгновенное заполнение". по этой теме уже был пост от @Veseliy.4el, Мгновенное заполнение в Excel

а также ютуб вариант, автор - Николай Павлов

15 приемов и горячих клавиш Excel и Гугл Таблиц

Работа в Excel и Google таблицах имеет много общего: ячейки, формулы, данные. Формат работы во всех электронных таблицах похож, везде есть приемы и трюки, упрощающие жизнь начинающим и продвинутым пользователям. В этом уроке показаны самые часто используемые приемы работы и горячие клавиши икселя и гугл таблиц, а также, как вставить excel таблицу в гугл и наоборот. После такого простого обучения вам гораздо реже придется гуглить Как в excel закрепить строку или Как вставить таблицу в гугл таблицу)

Таймкоды помогут быстро найти нужный момент:

00:00 - Введение

00:16 - Горизонтальная прокрутка

00:36 - Автоматическая ширина столбца

00:58 - Перенос текста в ячейке и переход на новую строку

01:30 - Как переместить столбцы и строки

01:49 - Как закрепить строку или столбец

02:08 - Автозаполнение и автоматическая нумерация

02:26 - Формат числа: с разделителями, денежный, дата.

03:01 - Специальная вставка: только значения или формат

03:22 - Автоматический поиск и автозамена

03:55 - Формулы, абсолютные и относительные ссылки

04:57 - Повтор последнего действия

05:18 - Быстрая сумма, среднее, минимум и максимум

05:31 - Фильтры и сортировка

06:11 - Печать

06:28 - Копирования из Excel в Гугл и обратно

Формулы Гугл Таблиц, которых нет в Excel

Формулы Google таблиц унаследованы от Excel, но есть и более продвинутые функции: формулы массива, ArrayFormula, текстовые функции и формулы импорта данных из других таблиц (IMPORTRANGE) и сайтов (IMPORTHTML, IMPORTXML). Эти возможности позволяют как работать в гугл таблицах быстрей, так и избежать возможных ошибок (из-за большого количества формул).

0:00 - Приветствие Гугл драйв в деле!

0:16 - Плюсы гугл таблиц

0:35 - Формулы массива UNIQUE FILTER SORT

2:00 - ARRAYFORMULA

3:20 - Текстовые функции JOIN и SPLIT

4:04 - Интеграции и импорт GOOGLETRANSLATE IMAGE GOOGLEFINANCE

4:30 - IMPORTHTML IMPORTXML IMPORTRANGE

10 трюков Google таблиц (и Excel) для продвинутых и начинающих!

Как работать в гугл таблицах и Excel быстро и эффективно. Полезные приемы, которые упростят вам жизнь.

0:06 - Как быстро создать таблицу

0:37 - Как быстро найти и открыть таблицы

0:47 - Как открыть Гугл Таблицу в двух окнах

1:00 - Горячие клавиши Гугл Таблиц

1:06 - Как скрыть строки

1:38 - Формат таблиц, чередование цветов

2:05 - Копирование сразу нескольких диапазонов

2:30 - Именованные диапазоны

3:01 - Как показать и проверить формулы в гугл таблицах

3:29 - Справка и обучение в гугл таблицах

Excel vs Google-таблиц. Что лучше? Сравнение, обзор

Что лучше? Excel или Google-таблицы? Постараемся ответить в этом видео. Сравнить два мощных табличных редактора. Рассмотреть особенности и возможности каждого, разобраться в сильных и слабых местах.

0:45 - Базовое сравнение Excel 2019 (365) и Google-таблиц

1:33 - Сравнение интерфейсов Excel 2019 (365) и Google-таблиц

3:32 - Что умеют Google-таблицы?

10:39 - Практический пример и сравнение табличных редакторов

17:48 - В чем лучше работать? В Excel или Google-таблицах?

Суммирование по цвету в Excel

К сожалению, в Excel нет стандартной функции, для того чтобы производить суммирование по определенным признакам форматирования ячеек – например, по цвету заливки, по цвету шрифта, по его подчёркиванию, курсиву и так далее.

Поэтому для подобных задач приходится писать пользовательские функции. В этом посте хочу поделиться примером кода UFD (User Defined Function – Пользовательской функции) для суммирования значений ячеек из определенного диапазона, соответствующих заданному цвету заливки.

Option Explicit
Function SUMIFCOLOR(rgCellsToSum As Range, rgColorSample As Range)
Dim rgCellChecked As Range
Dim intColorIndex As Integer
Dim dblSum As Double
Application.Volatile
'Сохраняем указание цвета в числовую переменную
intColorIndex = rgColorSample.Interior.ColorIndex
'Проверяем каждую ячейку и сохраняем её значение в промежуточную переменную
For Each rgCellChecked In rgCellsToSum
If rgCellChecked.Interior.ColorIndex = intColorIndex Then
dblSum = dblSum + rgCellChecked.Value
End If
Next rgCellChecked
'Вносим значение в функцию
SUMIFCOLOR = dblSum
End Function

Итак, что теперь с этим кодом надо сделать.

1. Открываем требуемый файл

2. Открываем окно редактора VBA с помощью комбинации клавиш Alt+F11

3. В нём добавляем новый модуль: Insert (1) -> Module (2)

4. В открывшемся поле вставляем код (отступы, к сожалению, по желанию придётся проставить вручную):

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

При этом обрати внимание – чтобы теперь в файле вставленный макрос сохранился, сохранить файл надо будет в формате xlsm.

При этом, данная функция является волатильной (перерасчет происходит всегда, даже если изменения произошли в другой ячейке), но изменения формата не являются триггерами перерасчёта – поэтому, если хочешь, чтобы она всегда отражала актуальное значение, её надо немного расширить с помощью событийной процедуры Worksheet_SelectionChange. Это уже более объемная тема, которую тяжело компактно описать в тексте, поэтому предлагаю посмотреть вот это видео:

Читайте также: