Как отрицательное число сделать положительным в excel
Числовое форматирование Excel - это одна из самых недооценённых пользователями функций программы.
В этой заметке я расскажу о принципах создания пользовательского числового формата , и по ходу мы разберем примеры, которые продемонстрируют всю мощь этой, на первый взгляд, не особо нужной функции Excel.
Но перед тем, как приступим, хочу обратить ваше внимание на один важный момент.
Суть числового форматирования
Не раз сталкивался с заблуждением пользователей, которые считают, что числовое форматирование как-то влияет на значение, находящееся в ячейке.
ВАЖНО понимать, что это НЕ ТАК!
Числовое форматирование влияет только на отображение значения. Само значение при этом никак не изменяется.
То есть если в ячейке находится цифра с двумя знаками после запятой (1), а мы с помощью числового форматирования уберем дробную часть (2), то визуально кажется, что значение было округлено, но это не так. В ячейке по-прежнему хранится значение с дробной частью (3) и в формулах будет использоваться именно оно.
Числовой формат (2) позволяет изменить внешний вид значения (1), но не влияет на само значение (3) Числовой формат (2) позволяет изменить внешний вид значения (1), но не влияет на само значение (3)Числовой формат позволяет изменить внешний вид и сделать данные более аккуратными и легко воспринимаемыми.
Ну а теперь поговорим о синтаксисе числового форматирования.
Синтаксис числового форматирования
Обычно числовое форматирование применяется следующим образом: выделяется ячейка или диапазон ячеек (1), к которому нужно числовой формат применить, и затем из выпадающего списка (2) выбирается готовый шаблон (3).
Как правило, пользователи не интересуются, как именно этот шаблон работает, а просто применяют готовые форматы в своей работе.
Также все готовые шаблоны доступны и в окне Формат ячеек .
Здесь в самом конце списка находится пункт « все форматы », который позволяет посмотреть на код, используемый в готовых шаблонах, а также создавать свои форматы.
Для создания формата нужно понимать синтаксис написания кода, то есть нужно изучить правила, по которым он формируется и узнать, какие вспомогательные символы в коде используются.
Именно об этом я и расскажу в этой заметке.
Итак, код числового формата в общем случае состоит из четырех разделов:
ПОЛОЖИТЕЛЬНЫЕ; ОТРИЦАТЕЛЬНЫЕ; НОЛЬ; ТЕКСТ
Сначала указывается правило форматирования положительных значений, затем отрицательных, далее нулевых и в конце текста. Все четыре раздела отделяются друг от друга точкой с запятой.
При этом необязательно в коде должны присутствовать все четыре раздела.
Если задан только один раздел, то он будет применяться ко всем числовым значениям вне зависимости от их величины и знака. Если указано два раздела, то первый будет использоваться для форматирования положительных числовых значений и нулей, а второй для отрицательных чисел. И только указание всех четырех разделов в коде позволит форматировать текстовые значения.
Код каждого раздела числового формата задается с помощью специальных символов. Давайте их рассмотрим на примерах и пока будем использовать самый простой шаблон - зададим только один раздел для всех числовых значений.
Символ 0 в числовом формате
Символ « ноль » в коде числового формата используется для вывода обязательной цифры. С его помощью можно вывести незначащие нули как в целой, так и в дробной части числа. То есть каждый ноль в коде резервирует место под цифру, которая будет введена или уже находится в ячейке. Если цифры нет, то будет выведен ноль.
Давайте рассмотрим на примере.
В первом столбце находятся значения в общем формате, а в последующих к этим же значениями применяется пользовательский формат ( шаблон в заголовке).
Большинство людей, работающих с электронными таблицами Excel, имеют дело с числами в больших / малых наборах данных. И когда вы работаете с числами, у вас будут все их типы (положительные, отрицательные, десятичные, дата / время).
Одной из распространенных задач, которые нам часто приходится выполнять, является преобразование этих чисел из одного формата в другой.
В этом уроке я покажу вам несколько простых способов как изменить отрицательные числа на положительные в Excel (с помощью формул, техники копирования и вставки и других замечательных методов).
Так что, если вам интересно, продолжайте читать!
Умножьте на (-1), чтобы преобразовать отрицательное число в положительное
Если у вас есть столбец, полный чисел, и вы хотите быстро получить числа, в которых отрицательные значения были преобразованы в положительные, вы можете легко сделать это, умножив эти отрицательные значения на -1.
Но вы также должны убедиться, что вы умножаете только отрицательные числа, а не положительные.
Предположим, у вас есть набор данных, как показано ниже:
Ниже приведена формула, которая преобразует отрицательные числа в положительные, а остальные оставит без изменений:
Вышеупомянутая формула использует функцию IF, чтобы сначала проверить, является ли число положительным или нет. Если оно положительное, знак не меняется, а если оно отрицательное, к ссылке добавляется отрицательный знак, что дает нам только положительное число.
Если у вас также есть текстовые значения в наборе данных, эта функция будет игнорировать это (и будут изменены только отрицательные значения)
Теперь, когда у вас есть требуемый результат, вы можете преобразовать эти формулы в значения (и скопировать их поверх исходных данных, если они вам не нужны).
Используйте функцию ABS, чтобы изменить все отрицательные числа на положительные
В Excel есть специальная функция, которая удаляет отрицательный знак и дает вам абсолютное значение.
.. функция ABS
Предположим, у вас есть набор данных, показанный ниже, и вы хотите изменить отрицательные значения на положительные.
Ниже приведена формула, которая сделает это:
Вышеупомянутая функция ABS не влияет на положительные числа, но преобразует отрицательные числа в положительные.
Теперь, когда у вас есть требуемый результат, вы можете преобразовать эти формулы в значения (и скопировать их поверх исходных данных, если они вам не нужны).
Умножьте с помощью специальной вставки, чтобы перевернуть знак
Если вы хотите изменить знак числа (т. Е. Изменить отрицательный на положительный и положительный на отрицательный), вы также можете использовать эту специальную технику умножения вставки.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите перевернуть знак:
Ниже приведены шаги по изменению знака с помощью специальной вставки:
- В любой пустой ячейке на листе введите -1.
- Скопируйте эту ячейку (со значением -1)
- Выберите диапазон, в котором вы хотите перевернуть знак.
- Щелкните правой кнопкой мыши любую из выбранных ячеек
- Щелкните Специальная вставка. Откроется диалоговое окно Специальная вставка.
- В разделе «Вставить» выберите «Значения».
- В параметрах операции выберите «Умножить».
- Нажмите ОК
- Удалить -1 из ячейки
Но что, если вы хотите преобразовать только отрицательные числа в положительные, а не наоборот?
В этом случае вам каким-то образом сначала нужно выбрать все отрицательные числа, а затем выполнить указанные выше шаги.
Вот как выбрать в Excel только отрицательные числа:
Вышеупомянутые шаги будут выбирать только те ячейки, которые имеют отрицательный знак. Теперь, когда у вас выделены эти ячейки, вы можете использовать технику «Специальная вставка», чтобы изменить знак только отрицательных чисел.
Этот метод имеет два преимущества по сравнению с методом формул (два метода, описанные до этого):
- Вам не нужно добавлять дополнительный столбец, а затем использовать формулу для получения результата в этом столбце. Вы можете использовать это в существующем наборе данных.
- Вам не нужно преобразовывать формулы в значения (поскольку результат, который вы получаете, уже является значением, а не формулой)
Мигающая заливка для удаления отрицательного знака
Это позволяет вам быстро идентифицировать закономерности, а затем дать вам результат, в котором шаблон был применен ко всему набору данных.
Это можно использовать, когда у вас есть имена, и вы хотите разделить имя и фамилию. Как только вы несколько раз наберете имя в соседней ячейке, Flash Fill определит шаблон и выдаст вам все имена.
Точно так же вы можете использовать его, чтобы быстро удалить отрицательный знак из числа, в то время как положительные значения остаются неизменными.
Ниже приведен набор данных, в котором у меня есть отрицательные числа, и я хочу изменить их на положительные значения.
Ниже приведены шаги по замене отрицательных чисел на положительные с помощью Flash Fill:
- В поле рядом с таблицей с данными введите ожидаемый результат вручную. В этом примере я вручную введу 874
- В ячейке под ним введите ожидаемый результат (162 в этом примере).
- Выберите обе ячейки
- Поместите курсор в нижнюю правую часть выделения. Он изменится на значок плюса
- Щелкните и перетащите, чтобы заполнить столбец (или дважды щелкните)
- Щелкните значок Параметры автозаполнения.
- Нажмите на Flash Fill.
Вышеупомянутые шаги дадут вам ожидаемый результат, где отрицательный знак был удален.
При использовании этого метода вам нужно помнить, что Excel полагается на угадывание шаблона. Так что вам придется хотя бы показать Excel, что вы конвертируете отрицательное число в положительное.
Это означает, что вам придется вручную вводить ожидаемый результат, пока вы не накроете хотя бы одно отрицательное число.
Преобразование отрицательных чисел в положительные одним щелчком мыши (VBA)
И, наконец, вы также можете использовать VBA для преобразования отрицательных значений в положительные.
Я бы порекомендовал использовать этот метод, если вам приходится делать это часто. Возможно, вы регулярно получаете набор данных из базы данных или от коллег, и вам нужно делать это каждый раз.
В таком случае вы можете создать и сохранить код макроса VBA в личной книге макросов и разместить VBA на панели быстрого доступа. Таким образом, в следующий раз, когда вы получите набор данных, в котором вам нужно это сделать, вы просто выберите данные и щелкните значок в QAT…
Не волнуйтесь, я покажу вам точные шаги, чтобы запустить его.
Ниже приведен код VBA, который преобразует отрицательные значения в положительные значения в выбранном диапазоне:
Вы можете добавить этот код в обычный модуль в книге (если вы хотите использовать его только в этой книге). И если вы хотите использовать этот код макроса в любой книге в вашей системе, вы можете сохранить его в личной книге макросов.
Теперь позвольте мне показать вам, как добавить этот код на панель быстрого доступа (шаги одинаковы, независимо от того, сохраняете ли вы этот код в отдельной книге или в PMW)
Теперь у вас будет значок макроса в QAT.
Чтобы использовать этот макрос одним щелчком мыши, просто сделайте выбор и щелкните значок макроса.
Примечание. Если вы сохраняете код макроса VBA в книге, вам необходимо сохранить книгу в формате с поддержкой макросов (XLSM).
Когда вы используете Microsoft Excel для работы с числами, вы можете сделать так, чтобы в Excel всегда было положительное число, чтобы найти абсолютное значение числа. Абсолютное значение всегда положительно. В других случаях вы можете предпочесть отображать финансовые данные, используя все положительные числа, причем отрицательные суммы выделены красным или выделены чем-то, кроме знака минус. Используйте формулу ABS в Excel, чтобы получить абсолютное значение и удалить знак, или используйте пользовательское форматирование, чтобы отрицательные числа выглядели положительными.
кредит: fizkes / iStock / GettyImages
ABS Formula In Excel
В математике абсолютное значение числа - это его расстояние от нуля, то есть его значение без положительного или отрицательного знака. Он часто представлен вертикальными чертами, поэтому | -5 | = | 5 | = 5, например. В Excel вы вычисляете абсолютное значение, используя формулу функции ABS.
С формулой = ABS (-5) Excel преобразует отрицательное значение в положительное и дает результат 5. Вы можете обернуть большее вычисление в функции ABS, чтобы результат всегда был положительным.
Коды пользовательских форматов
Иногда вы можете захотеть сохранить, является ли число положительным или отрицательным в логике вашей электронной таблицы, но вы хотите показать абсолютную разницу. Для этого используйте пользовательский формат.
Введите формат для положительных чисел, формат для отрицательных чисел, формат для нуля и формат для нечислового текста, разделенных точкой с запятой. Обратитесь к документации Microsoft Excel, чтобы узнать, какие коды формата доступны для вас. Пропустите знак минус из кода формата отрицательного числа, чтобы числа выглядели положительными.
Другие программы для работы с электронными таблицами поддерживают похожие коды.
Условное форматирование
Вы часто хотите выделить положительные и отрицательные числа по-разному. Например, в финансовых целях вы можете выделить положительные числа зеленым или оставить их невыделенными, а отрицательные - красным.
Как определить, какой провод динамика является положительным
Перед подключением проводов к усилителю необходимо определить, какой провод динамика является положительным. Кабель громкоговорителя иногда неправильно обозначен полосами, которые должны идентифицировать .
Сделать полосы прокрутки всегда показывать и отключать прозрачность в окнах 10
Fluent Design System от Microsoft по-прежнему будет неотъемлемой частью Windows 10 в будущем. Но если вы не фанат, вы можете управлять некоторыми видами поведения.
Как сделать так, чтобы приложения для Windows 10 всегда запускались с правами администратора
Если вам нужно запускать приложения в Windows 10 с повышенными привилегиями, вот простой способ убедиться, что они всегда работают от имени администратора.
Используйте функцию ABS, чтобы изменить все отрицательные числа на положительные
В Excel есть специальная функция, которая удаляет отрицательный знак и дает вам абсолютное значение.
.. функция ABS
Предположим, у вас есть набор данных, показанный ниже, и вы хотите изменить отрицательные значения на положительные.
Ниже приведена формула, которая сделает это:
Вышеупомянутая функция ABS не влияет на положительные числа, но преобразует отрицательные числа в положительные.
Теперь, когда у вас есть требуемый результат, вы можете преобразовать эти формулы в значения (и скопировать их поверх исходных данных, если они вам не нужны).
Быстрые клавиши для числовых форматов
В Excel есть несколько сочетаний клавиш для наиболее распространенных представлений:
Видео
Из чего состоит числовой формат Excel?
Чтобы иметь возможность создать свой новый формат в Excel, важно понимать, по каким правилам его нужно описать.
Вот пример кода настраиваемого формата Excel:
Код этот состоит из 4 разделов, отделённых друг от друга точкой с запятой. Они расположены в следующем порядке:
- Для положительных чисел (отображение 2 десятичных знаков и разделителя тысяч).
- Для отрицательных чисел (например, такой же, как для положительных, но заключен в круглые скобки).
- Как показывать нули (например, отображать тире вместо нуля).
- Представление текстовых значений (отображение текста определённым цветом).
Важно: не все разделы обязательны! Хотя разделов четыре, но обязательным является только один.
Если указан только один код, то Excel будет использовать его для всех значений.
Если вы использовали только два раздела, то первый из них используется для положительных чисел и нулей, а второй предназначается для отрицательных значений.
Когда предоставлены первые три раздела, Excel будет использовать указанные правила отображения, а текст будет показан в том виде, в каком он был введен.
Чтобы пропустить раздел, поставьте точку с запятой в нужном месте, но больше ничего не пишите.
Как открыть диалоговое окно «Форматирование ячеек»
В предыдущих разделах мы обсудили два разных способа переключения на красные отрицательные числа. Обе эти опции переводят вас в окно «Формат ячеек».
Какой вариант вы используете для открытия этого окна, зависит от вас, но оба позволяют достичь одного и того же результата. Напомним, эти методы:
- Щелкните ячейку правой кнопкой мыши и выберите параметр «Формат ячеек».
- Выберите вкладку «Главная» и нажмите кнопку «Формат: число ячеек».
Другой инструмент Excel, который вы, возможно, захотите рассмотреть для выполнения такого форматирования или других типов результатов, называется «Условное форматирование».
выделение всего диапазона условным форматированием, которая ячейку D2, аНа панели «ГЛАВНАЯ» выберите с решеткой. Внизу этого окна, B25.
минус к числу строка. Если после A2:D8. Это значит, сделала таблицу еще
Читайте также: