0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

КАК: Форматирование отрицательных, длинных и специальных номеров в Excel — 2022

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

Форматирование чисел прикрепляется к ячейке, а не к значению. Поэтому форматирование чисел изменяет не сами числа в ячейках, а то, как они отображаются в ячейке.

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

Форматирование чисел в Excel Обзор

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

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

Отрицательное форматирование чисел

По умолчанию отрицательные числа обозначаются знаком минус или тире (-) слева от числа. В диалоговом окне Excel «Формат ячеек» доступно несколько других форматов отображения отрицательных чисел. К ним относятся:

  • Красный текст
  • Круглые скобки
  • Круглые скобки и красный текст

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

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

Изменение форматирования отрицательного номера в диалоговом окне «Формат ячеек»

  1. Выделите данные для форматирования
  2. Нажми на Главная вкладка ленты
  3. Нажми на диалоговое окно запуска — маленькая стрелка направленного вниз в нижнем правом углу Число значка на ленте, чтобы открыть Формат ячеек диалоговое окно
  4. Нажмите на Число в разделе «Категория» диалогового окна
  5. Выберите параметр для отображения отрицательных чисел — красный, скобки или красный и скобки
  6. Нажмите «ОК», чтобы закрыть диалоговое окно и вернуться на рабочий лист.
  7. Отрицательные значения в выбранных данных теперь должны быть отформатированы с выбранными параметрами

Форматирование чисел как фракций в Excel

Используйте формат Дробь для отображения чисел в виде действительных дробей, а не десятичных дробей. Под столбцом Описание на изображении выше можно выбрать один из следующих вариантов:

  • Показывать десятичные дроби в виде одной, двух или трех цифр в значащей части числа;
  • Показывать десятичные знаки в часто используемых дробях — например, половина и четверть.
Читайте так же:
Как быстро извлечь адрес электронной почты из текстовой строки?

Сначала формат, данные второй

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

Например, если в ячейки с общим форматом ввести дроби с числителями от 1 до 12 — такие как 1/2 или 12/64 — эти числа будут преобразованы в даты, например.

  • 1/2 будет считаться 2 января.
  • 12/64 следует переводить как Jan. 64 (январь 1964).

Кроме того, дроби с числителем больше 12 будут преобразованы в текст и вызовут проблемы при вычислениях.

Форматирование чисел как фракций в диалоговом окне «Формат ячеек»

  1. Выделите ячейки, которые будут отформатированы как фракции
  2. Нажми на Главная вкладка ленты
  3. Нажми на диалоговое окно запуска — маленькая стрелка направленного вниз в нижнем правом углу Число значка на ленте, чтобы открыть Формат ячеек диалоговое окно
  4. Нажмите на Доля в разделе «Категория» диалогового окна, чтобы отобразить список доступных форматов дробей в правой части диалогового окна
  5. Выберите формат отображения десятичных чисел как фракций из списка
  6. Нажмите «ОК», чтобы закрыть диалоговое окно и вернуться на рабочий лист.
  7. Десятичные числа, введенные в форматированный диапазон, должны отображаться как фракции

Форматирование специальных номеров в Excel

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

Все ячейки рабочего листа Excel имеют общий формат, и характеристики этого формата включают в себя:

  • Из чисел удаляются ведущие нули,
  • Числа с более чем 11 цифрами преобразуются в научную (или экспоненциальную) систему счисления.

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

Чтобы избежать проблем с особыми числами, можно использовать два параметра в зависимости от типа числа, сохраненного в листе:

  • Специальный формат категории в диалоговом окне Формат ячеек (см. ниже);
  • Форматирование чисел как текст (следующая страница).

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

Специальная форматная категория

В диалоговом окне «Формат ячеек» к числам автоматически применяется специальное форматирование, например:

  • Телефонные номера — первые три цифры десятизначного номера должны быть заключены в скобки, а оставшиеся семь цифр должны быть разделены на две группы, разделенные дефисом. Например: (800) 555-1212
  • Номера социального страхования — разделите девятизначные номера на группы из трех, двух и четырех цифр, разделенных дефисом. Например: 555-00-9999
  • ZIP-коды — сохраните ведущие нули в цифрах, которые удаляются при использовании стандартных форматов. Пример: 00987
  • Почтовый индекс + 4 : разбивает девятизначные номера на группы из пяти цифр и четырех цифр, разделенных дефисом. Он также сохраняет все ведущие нули. Пример: 00987-5555
Читайте так же:
Как быстро восстановить размер ячейки по умолчанию в Excel?

Локальная чувствительность

Выпадающий список под локалью дает возможность форматировать специальные номера, характерные для конкретной страны. Когда локаль изменена на English (Canada), доступны следующие варианты: Telephone Number (Номер телефона) и Social Security Number (Номер социального обеспечения) — оба эти параметра часто используются в специальных номерах для конкретной страны.

Использование специального форматирования для чисел в Диалоговое окно «Формат ячеек»

  1. Выделите ячейки, которые будут отформатированы как фракции
  2. Нажми на Главная вкладка ленты
  3. Нажми на диалоговое окно запуска — маленькая стрелка направленного вниз в нижнем правом углу Число значка на ленте, чтобы открыть Формат ячеек диалоговое окно
  4. Нажмите на Специальный в разделе «Категория» диалогового окна, чтобы отобразить список доступных специальных форматов в правой части диалогового окна
  5. При необходимости нажмите место действия возможность изменения местоположения
  6. Выберите один из параметров формата для отображения специальных номеров из списка.
  7. Нажмите «ОК», чтобы закрыть диалоговое окно и вернуться на рабочий лист.
  8. Соответствующие числа, введенные в форматированный диапазон, должны отображаться как в выбранном специальном формате

Форматирование чисел в виде текста в Excel

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

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

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

В ячейке A3 выше число 1234567891234567 меняется на 123456789123450, когда для ячейки установлено числовое форматирование.

Использование текстовых данных в формулах и функциях

Напротив, в ячейке A4 выше это же число отображается правильно, а поскольку для форматирования текста существует ограничение в 1024 символа, не могут быть отображены только иррациональные числа, такие как Pi (*) и Phi (*).

Читайте так же:
Как быстро перемещать элементы между двумя списками в Excel?

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

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

  • Результат ноль для SUM — ячейка A9
  • Значение ошибки # DIV / 0! для AVERAGE — ячейка A10

Шаги по форматированию ячейки для текста

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

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

Текст влево, цифры справа

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

По умолчанию в Excel текстовые данные выравниваются по левому краю ячейки, а числовые — по правому. Если выравнивание по умолчанию диапазона с текстовым форматированием не было изменено, числа, введенные в этот диапазон, должны отображаться в левой части ячеек, как показано в ячейке C5 на изображении выше.

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

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

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

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

Удлинители URL-адресов Top 6 для сокращения длинных ссылок

Удлинители URL-адресов Top 6 для сокращения длинных ссылок

Зачем делиться длинными ссылками, если можно использовать короткие? Сэкономьте место с помощью одного из этих лучших сократителей URL и перестаньте выглядеть как спамер.

Создание и форматирование диаграммы столбцов в Excel

Создание и форматирование диаграммы столбцов в Excel

Создайте столбчатую диаграмму в Excel. Форматируйте текст, изменяйте цвета и перемещайте диаграммы на новый лист. Обновлено для работы в Excel 2019.

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

онлайн курсы Excel Москва

Мини-курс «5 чудес Excel»

Подробнее

Нули чёрточкой в числовом формате

Изображение

Теперь мы объединим преимущества числового и финансового формата и сделаем новый универсальный формат доступным во всех файлах Excel.

Читайте так же:
Как автоматически подогнать ширину столбца в Excel?

Для начала вспомним о недостатках.

1. Нет горячей клавиши для быстрой настройки числового формата без знаков после запятой и чтобы отрицательные числа выделялись красным цветом. Горячая клавиша CTRL+SHIFT+1 умеет только разделитель групп разряда добавлять (пробел между каждыми тремя цифрами) и оставляет два знака после запятой.

2.
В числовом формате без знаков после запятой мы пропустим число 0,25 — оно будет выглядеть на экране как ноль, а при расчёте отклонение это совсем нехорошо.

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

Выход из этой ситуации есть.

Действие 1.
Формат, учитывающий все наши потребности, можно сделать следующим образом – в открытом файле Excel зайдите в окно формат ячеек через контекстное меню или CTRL+1 и настройте числовой формат в разделе Числовой (разделитель групп разрядов, без знаков после запятой, отрицательное красным).

Действие 2.
После перейдите в раздел (все форматы) и в конце поля Тип поставьте точку с запятой и тире (это кодировка внешнего вида нулевого значения). Нажмите ОК.

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

Зачем нужны стили ячеек Excel

Запишите этот формат в память файла.
Оставаясь на ячейке на вкладке Главная нажмите справа кнопку со стилями ячеек и выберите внизу команду Создать стиль. В появившемся окне оставьте первый флажок, остальные снимите, чтобы новый формат не запоминал бы заливку, параметры шрифта и пр.

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

Но только в этом файле. Тем из вас, кто хочет иметь быстрый доступ ко всем своим файлам — как уже созданным, так и будущим, — следует прочитать дальше.

Читайте так же:
Как автоматически изменить размер формы на основе / в зависимости от указанного значения ячейки в Excel?

Действие 4.
Перебросить созданные стили из одного файла в другой можно командой Объединить стили.

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

Sub BestNumberFormat()
‘создаёт стиль идеального числового формата в активном файле
ActiveWorkbook.Styles.Add Name:=»BestNumber»
With ActiveWorkbook.Styles(«BestNumber»)
.IncludeNumber = True
.IncludeFont = False
.IncludeAlignment = False
.IncludeBorder = False
.IncludePatterns = False
.IncludeProtection = False
End With
ActiveWorkbook.Styles(«BestNumber»).NumberFormat = «# ##0_ ;[Red]-# ##0 ;-«
End Sub

При нажатии ALT+F11 в таком файле открывается редактор VBA. Выберите «Вставить» из инструментов слева и вставьте код (выделенный заливкой) в чистый лист справа после щелчка на любом модуле листа слева. Сохраните изменения и закройте редактор VBA.

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

Действие 5.
Вызовите контекстное меню на панели быстрого доступа и выберите Настройка панели быстрого доступа. В появившемся окне вверху слева выберите Макросы, выделите макрос BestNumberFormat в списке ниже и кнопкой Добавить перебросьте его в правый список. Положение кнопки можно изменить стрелками справа. Иконку и подсказку настройте с помощью кнопки Изменить.

Вы можете закрыть файл макроса. Откройте рабочую книгу и нажмите на пользовательский значок на панели быстрого доступа — он сам найдет файл (не переименовывайте его и не перемещайте в другие места) и запустит макрос. Если появится окно с предложением включить макросы, нажмите одноименную кнопку. В группе «Стили» вы увидите стиль BestNumber. Теперь он навсегда останется в этом файле, и вы сможете использовать его только отсюда.

Числовой формат без знаков после запятой

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

Sub BestFormatCreat()
Selection.NumberFormat = «#,##0_ ;[Red]-#,##0 ;-«
End Sub

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector