Формула формата ячейки в excel

Формула формата ячейки в excel

Адреса ячеек в программе «Excel» могут отображаться в двух разных форматах:

1) Самый популярный и для большинства пользователей наиболее удобный формат адреса ячеек — это буквы латинского алфавита с цифрами, где названия столбцов обозначаются буквами, а названия строк цифрами. И, соответственно, ячейки имеют адреса по названию пересечения столбца и строки.

2) Второй формат — относительные ссылки, менее популярен и востребован. Этот формат имеет вид R1C1. Такой формат отображения показывает адреса ячеек используемых в формуле относительно выбранной ячейки, в которую записывается формула. Многие пользователи, увидев такой формат отображения адресов ячеек, впадают ступор и пытаются перевести вид адресов в привычный буквенно цифровой вид.

Для изменения вида адресов следует выполнить следующую последовательность действий:

  • войти во вкладку «Файл»;
  • выбрать меню «Параметры»;
  • далее выбрать вкладку «Формулы»;
  • во вкладке «Формулы» убрать «галочку» (флажок) напротив параметра «Стиль ссылок R1C1»;
  • нажать кнопку «Ok».

После выполнения указанной последовательности действий адреса ячеек приобретут привычный вид : A1; B1; C1 и т.д.

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

Как автоматически изменять формат по условию значения в ячейке Excel

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

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

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

Читайте также:  Ремонт ноутбука после падения

Напомню, что числовые форматы могут иметь до четырех разделов кода, разделенных точкой с запятой. Эти разделы определяют формат положительных, отрицательных, нулевых значений и текста. При этом использование формул внутри формата не предусмотрено. Нельзя, например, вставить функцию ЕСЛИ в код формата, и попытаться применить разные форматы в случае различного содержимого ячеек. Однако…

…для этих целей можно применить условное форматирование на основе формул.

Рис. 1. Условное форматирование на основе формул

Скачать заметку в формате Word или pdf, примеры в формате Excel

Начнем с вопросов Андрея и Варвары. Формат числа должен отвечать нескольким условиям:

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

Другими словами, надо, чтобы числа, набранные, как в левой колонке, отображались, как в правой:

23345,10 -> 23 345,1

23345,15 -> 23 345,15

23345,146 -> 23 345,15

23345,00 -> 23 345

Наиболее точно указанным условиям отвечают форматы:

  • Общий, но у него два недостатка: он не разделяет группы разрядов, и его нельзя использовать внутри функции ТЕКСТ(значение; формат);
  • # ##0,##, но он оставляет «глупую» запятую, если число целое: 23345,00 -> 23 345,

Подобрать формат, который полностью отвечал бы указанным условиям вроде бы нельзя… На помощь приходит условное форматирование. Назначаем требуемой области листа формат # ##0,##, который отработает верно во всех случаях, кроме целых чисел. Далее для этой же области задаем условное форматирование с использованием формулы =ОКРУГЛ(A1;0)=A1 (рис. 1). Это условие выполняется только если число в ячейке целое. И для этого условия задаем стандартный числовой формат с разделением разрядов на группы и нулем знаков после запятой. Чтобы задать условное форматирование, выделите область на листе, и пройдите по меню Главная –> Условное форматирование –> Создать правило –> Использовать формулу для определения форматируемых ячеек. Введите формулу и кликните кнопку Формат. В окне Формат ячеек перейдите на закладку Число. Выберите формат Числовой, число десятичных знаков – 0, поставьте галочку напротив Разделитель групп разрядов (рис. 2). Кликните Ok.

Рис. 2. Формат ячеек

Читайте также:  Как очистить списки вызовов на андроид

Вуаля)) Получилось то, что требовалось (рис. 3).

Рис. 3. Использование формата # ##0,## и условного форматирования

Следующий вопрос пришел от Владимира. Возможно ли пользовательское форматирование с условием проверки введённого? Например, если вводится целое число, то добавить " шт. " , а если дробное, то " кг " .

Опять же, невозможно задать проверку целочисленности внутри пользовательского формата, но можно задать один формат на листе, а второй с помощью условного форматирования (как в примере выше), или оба формата задать при помощи условного форматирования. Во втором случае, условия представлены на рис. 2. Формат для дробных (первое условие) # ##0,00 " кг " , для целых (второе условие) – # ##0 " шт. "

Рис. 4. Два различных формата для целых и дробных чисел

Вопрос Сергея. Можно ли отформатировать часть текста в ячейке по какому-либо признаку? Например, изменить цвет шрифта текста, заключенного в скобки?

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

Вопрос от Дарьи. Можно ли к числу в ячейке с помощью формата добавить через слэш то же самое число? Например, в ячейку введено 12, а отражается 12/12.

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

Читайте также:  Картинки для авы беседы вк

Условный формат на основе параметров ячейки. В Excel есть редко используемая функция ЯЧЕЙКА(тип_сведений;[ссылка]), которая позволяет получить некоторые сведения о содержимом или формате ячейки. Например, у нас есть ничем не примечательный диапазон А1:А7 (рис. 5), и мы хотим с помощью условного форматирования выделить пустые ячейки.

Рис. 5. Фрагмент листа с вроде бы пустыми ячейками

Используем для этой цели условное форматирование на основе формулы =ЯЧЕЙКА( " тип " ;A1)= " b " (рис. 6). Значение b соответствует пустой ячейке (подробнее см. описание функции на сайте Microsoft). Напомню, что строка формул в окне условного форматирования должна содержать выражение (формулу), возвращающую значения ИСТИНА или ЛОЖЬ. Таким образом, если проверяемая ячейка пустая, то функция ЯЧЕЙКА вернет значение b, а формула =ЯЧЕЙКА( " тип " ;A1)= " b " – значение ИСТИНА, и ячейка окрасится в зеленый цвет.

Рис. 6. Выделение пустых ячеек с помощью условного форматирования на основе формулы, использующей функцию ЯЧЕЙКА

Оказалось, что в ячейке А2 содержится формула = " " , а в ячейке А5 – число 5, написанное шрифтом белого цвета 🙂

Рис. 7. «Подстава»: ячейки, выглядящие пустыми, таковыми не являются

Ссылка на основную публикацию
Файловый менеджер для ubuntu server
Работа с файлами в операционной системе Ubuntu осуществляется через соответствующий менеджер. Все дистрибутивы, разработанные на ядре Linux, позволяют юзеру всячески...
Удалить одноклассники страницу с телефона айфон
Если вы хотите удалить свою страницу (профиль) в Одноклассниках, особенно если это требуется сделать со смартфона Android или iPhone —...
Удалить папку не удалось найти этот элемент
В этой инструкции подробно о том, как удалить файл или папку, если при попытке это сделать в Windows 10, 8...
Файлы dll чем открыть
Файлы формата DLL открываются специальными программами. Существует 2 типа форматов DLL, каждый из которых открывается разными программами. Чтобы открыть нужный...
Adblock detector