Как сделать условное форматирование в excel? инструкции с примерами

Содержание:

Преобразование чисел из текстового формата в числовой

​ в ячейку G3​ (a — b)​ тысяч vl =​ To 1 Step​ «тринадцать долларов»)) Edinicy(14)​

Выделите столбец

​ End Function​ = Right(n, 1)​ strЕдиницы As String,​При вводе номер, который​ ячейку формулу​«Надстройки»​щелкните стрелку рядом​Числа, хранящиеся как текст,​1. Курсы валют​ что курсы с​найти ‘,’ заменить​ в ситуации, когда​ и нажмем мастер​ * 100 If​ Mid(SumInt, shag, 1)​

Нажмите эту кнопку

​ -1 shag =​ = «четырнадцать «:​Можно написать алгори: Десятки​ strСотые As String​ начинается с нуля​=Сумма_прописью(A2)​​.​

​ с кнопкой​​ могут приводить к​ ​ подставляются только при​

Нажмите кнопку «Готово»

​ ЦБ РФ загружаются​ на ‘.’​ нужно отобразить числа​ функций нажав на​ c = 0​ txt = txt​​ shag + 1​

​ EdinicyPoslednie(14) = IIf(Valuta​ программы по-другому и​

Задайте формат

​ = «тридцать «:​

​ Dim Поз As​ — например, кода​

​, то в​

​Далее, в параметре настроек​Вставить​ непредвиденным результатам. Выделите​ 100% совпадении дат.​​ в текстовом формате​ ​а вообще, когда​​ в удобочитаемом виде​ ​ кнопку fx или​​ Then c =​ ​ & Sotni(vl) Case​

Использование формулы

​ Select Case x​ = 0, «четырнадцать​ еще сделать так,​

Вставьте новый столбец

​ n = Right(n,​ Integer strЧисло =​ продукта — Excel​ таком случае, любое​«Управление»​и выберите пункт​ ячейки и нажмите​2. Из-за разных​

Примените функцию ЗНАЧЕН

​ и по умолчанию​ число выгдядит как​​ либо объединить их​

​ комбинацию клавиш SHIFT+F3.​ CStr(c) + «0»​ 5 ‘ -​ Case 12 ‘​ евро», IIf(Valuta =​ чтобы она дописывала​ 1) Case «4»:​​ Format(Int(Число), «000000000000») ‘Миллиарды’​ ​ приведет к удалению​

Наведите указатель мыши

​ число, внесенное в​устанавливаем значение​Специальная вставка​ кнопку​ разделителей ЦБ РФ​ не могут использоваться​ число — а​ с текстом или​

Щелкните и перетащите вниз

​ В разделе категория​ d = «»​ десятки тысяч vl​ — сотни миллиардов​ 1, «четырнадцать рублей»,​ валюту суммы прописью.​ Десятки = «сорок​ Поз = 1​ ноль по умолчанию.​ ячейку A2 будет​«Надстройки Excel»​>​, чтобы выбрать​ & НБУ возникают​ в формулах.​​ не является числом​

​ символами.​ выберем текстовые и​​ If Valuta =​ ​ = Mid(SumInt, shag,​​ vl = Mid(SumInt,​ ​ «четырнадцать долларов»)) Edinicy(15)​​ Для этого создайте​ ​ «: n =​

Использование специальной вставки и умножения

​ strМиллиарды = Сотни(Mid(strЧисло,​ Если это не​ отображаться тут денежной​. Жмем на кнопку​Значения​ вариант преобразования. Если​ проблемы с дальнейшем​

  1. ​Для одного значения​ помогает способ:​ALISA​ найдем необходимую функцию.​​ 1 Then d​

    ​ 1) If vl​ shag, 1) txt​

  2. ​ = «пятнадцать «:​ Module2 и введите​
  3. ​ Right(n, 1) Case​ Поз, 1)) strМиллиарды​ является вас не​
  4. ​ суммой прописью.​​«Перейти…»​

    ​.​​ эта кнопка недоступна,​ ​ использованием курса UAH/USD​​ эту проблему решил​ ​Поместить 1 в​

  5. ​: Уважаемые знатоки!!!! Знаю​​В окне аргументов зададим​

    ​ = «коп.» Else​​ = 1 And​ ​ = txt &​ EdinicyPoslednie(15) = IIf(Valuta​ в него следующий​ «5»: Десятки =​

  6. ​ = strМиллиарды &​

    ​ устраивают, можно создать​Как видим, несмотря на​

Отключение зеленых треугольников

​.​Если указанные выше действия​ выполните указанные ниже​ при условии, что​​ (см. закладку «Продажи»​

​ любую пустую ячейку​​ вопрос банальный, но​ ​ следующие значения:​​ d = «цен.»​ ​ Mid(SumInt, shag +​​ Sotni(vl) Case 11​ ​ = 0, «пятнадцать​

support.office.com>

Настраиваем текст

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

Как изменить шрифт Excel

Разберем несколько способов изменения шрифта:

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

11

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

12

  1. Способ третий. Выделяем ячейку и при помощи комбинации клавиш Ctrl+1 вызываем «Формат ячеек». В появившемся окошке выбираем раздел «Шрифт» и проводим все необходимые настройки.

13

Как выбрать способы начертания Excel

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

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

  1. При помощи комбинации клавиш:
  • Ctrl+B – полужирное;
  • Ctrl+I – курсивное;
  • Ctrl+U – подчеркнутое;
  • Ctrl+5 – перечеркнутое;
  • Ctrl+= – подстрочное;
  • Ctrl+Shift++ – надстрочный.
  1. При помощи инструментов, находящихся в блоке «Шрифт» вкладки «Главная».

14

  1. Используя окошко «Формат ячеек». Здесь можно выставить нужные настройки в разделах «Видоизменение» и «Начертание».

15

Выравнивание текста в ячейках

Выравнивание текста в ячейках осуществляется следующими методами:

Заходим в блок «Выравнивание» раздела «Главная». Здесь при помощи иконок можно произвести выравнивание данных.

16

В окошке «Формат ячеек» переходим в раздел «Выравнивание». Здесь тоже можно выбрать все существующие виды выравнивания.

17

Автоформатирование текста в Excel

Два метода автоформатирования:

  1. Применение переноса по словам. Выделяем нужные ячейки, переходим в раздел «Главная», затем в блок «Выравнивание» и выбираем «Перенести текст». Включение этой функции позволяет автоматически реализовывать перенос по словам и увеличивать высоту строчки.
  2. Применение функции «Автоподбор по ширине». Переходим в окошко «Формат ячеек», далее «Выравнивание» и ставим галочку около «Автоподбор ширины».

Как объединить ячейки в Excel

Часто при работе с таблицами возникает необходимость в объединении ячеек. Сделать это можно при помощи кнопки «Объединить и поместить в центр», которая находится в блоке «Выравнивание» раздела «Главная». Использование этого параметра приводит к объединению всех выделенных ячеек. Значения внутри ячеек при этом выравниваются по центру.

18

Изменение ориентации и направления текста

Направление и ориентация текста – это два разных параметра, которые некоторые пользователи путают друг с другом. На этом рисунке в первом столбике применена функция ориентации, а во втором – направление:

19

Перейдя в раздел «Главная» блок «Выравнивание» и элемент «Ориентация», можно применить эти два параметра.

Применить условное форматирование для каждой строки в Excel

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

Метод A Изменение применимости к в диспетчере правил условного форматирования

Например, здесь вы хотите применить цвет фона заливки к ячейкам, если A2> B2, A3> B3,…, An> Bn с условным форматированием.

1. Во-первых, примените условное форматирование к A2: B2. Выберите A2: B2, затем щелкните Главная > Условное форматирование > Новое правило. Если в строке уже есть правила, просто переходите к шагу 4.

2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. от Выберите тип правила раздел, затем введите = $ A2> $ B2 в текстовое поле под Формат значений, где эта формула истинна.

3. Нажмите Формат кнопку, чтобы перейти к Формат ячеек диалоговое окно, а затем вы можете выбрать нужный тип форматирования. Например, заливка цветом фона. Нажмите OK > OK закрыть диалоги.

Теперь к строке A2: B2 применяется условное форматирование.

4. Не снимая выделения с A2: B2, нажмите Главная > Условное форматирование > Управление правилами.

5. в Диспетчер правил условного форматирования найдите правило, которое вы применили к A2: B2, измените диапазон, как вам нужно, в Относится к раздел и нажмите OK.

Затем это правило условного форматирования будет применяться к каждой строке в новом диапазоне.

Метод B Перетащите маркер автозаполнения

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

Предположим, в строке A2: B2 применено два условного форматирования, если A2> B2, заливка красным цветом фона, если A2

Теперь вы хотите применить эти два правила к A3: B9.

1. Во-первых, вам нужно применить правила условного форматирования к строке A2: B2. Если в строке есть правила, просто перейдите к шагу 4. Выберите диапазон A2: B2, щелкните Главная > Условное форматирование > Новое правило. Если в строке уже есть правила, просто переходите к шагу 4.

2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки нужно отформатировать, из Выберите тип правила раздел, затем введите = $ A2> $ B2 в текстовое поле под Формат значений, где эта формула истинна. Затем нажмите Формат кнопку в Формат ячеек диалога под Заполнять на вкладке выберите красный цвет. Нажмите OK > OK.

3. Не снимая выделения с строки A2: B2, включите Диалоговое окно «Новое правило форматирования» снова выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. от Выберите тип правила раздел, затем введите = $ A2 <$ B2 в текстовое поле под Формат значений, где эта формула истинна. Затем нажмите Формат кнопку в Формат ячеек диалога под Заполнять на вкладке выберите зеленый цвет. Нажмите OK > OK.

4. Затем перетащите Автозаполнение дескриптор соседних строк, к которым вы хотите применить условное правило, затем выберите Только форматирование заливки из Параметры автозаполнения.

Другие операции (статьи), связанные с форматированием Conditioanl

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

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

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

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

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

Как удалить ненужный формат данный в Microsoft Office Excel

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

  1. Кликнуть левой клавишей мышки по любой ячейке табличного массива. Можно нажать просто по пустому элементу рабочего листа.
  2. В окошке контекстного типа щёлкнуть по строчке «Формат ячеек».
  3. Переместиться в раздел «Число» в верхней панели инструментов открывшегося меню.
  4. Выбрать подходящий числовой формат из списка представленных слева окошка и выделить его, кликнув ЛКМ.
  5. Нажать по кнопке «Удалить», которая находится в правом нижнем углу окошка «Формат ячеек».
  6. Согласиться с системным предупреждением и нажать на «ОК» для закрытия окна. Выбранный стандартный либо пользовательских формат должен удалиться из программы MS Excel без возможности восстановления в дальнейшем.

Удаление ненужного формата в Excel

Специальные символы для выражений форматов

Символы для форматов даты и времени

Символ Описание
Точка (.) Разделитель компонентов даты (день, месяц, год). Используется при отображении месяца в виде числа.
Пробел Разделитель компонентов даты (день, месяц, год). Используется при отображении месяца прописью.
Двоеточие (:) Разделитель компонентов времени (часы, минуты, секунды).
d День в виде числа без нуля в начале (1–31).
dd День в виде числа с нулем в начале (01–31).
m Месяц в виде числа без нуля в начале (1–12). Если (m) следует после (h) или (hh), отображаются минуты (0–59).
mm Месяц в виде числа с нулем в начале (01–12). Если (mm) следует после (h) или (hh), отображаются минуты (00–59).
mmm Месяц прописью в сокращенном виде (янв–дек).
mmmm Полное название месяца (январь–декабрь).
y День года в виде числа (1–366).
yy Год в виде 2-значного числа (00–99).
yyyy Год в виде 4-значного числа (1900–9999).
h Часы в виде числа без нуля в начале (0–23).
hh Часы в виде числа с нулем в начале (00–23).
n (m) Минуты в виде числа без нуля в начале (0–59).
nn (mm) Минуты в виде числа с нулем в начале (00–59).
s Секунды в виде числа без нуля в начале (0–59).
ss Секунды в виде числа с нулем в начале (00–59).

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

Примеры отображения даты с помощью разных по количеству наборов символа d:

1
2
3
4
5
6
7
8

SubDataIsD()

MsgBox»d:  «&Format(Now,»d»)&vbNewLine_

&vbNewLine&»dd:  «&Format(Now,»dd»)&vbNewLine_

&vbNewLine&»ddd:  «&Format(Now,»ddd»)&vbNewLine_

&vbNewLine&»dddd:  «&Format(Now,»dddd»)&vbNewLine_

&vbNewLine&»ddddd:  «&Format(Now,»ddddd»)&vbNewLine_

&vbNewLine&»dddddd:  «&Format(Now,»dddddd»)

EndSub

Символы для числовых форматов

Символ Описание
Точка (.) Десятичный разделитель.
Запятая (,) Разделитель групп разрядов. В отображаемых числах заполняется пробелом.
(0) Заполнитель, который отображает цифру или ноль. Используется, когда нужны ведущие нули или нули в конце числа.
(#) Заполнитель, который отображает цифру или ничего не отображает. Используется, когда не нужны ведущие нули или нули в конце числа.
(%) Заполнитель процента. Выражение умножается на 100, а знак процента (%) вставляется на той позиции, где он указан в строке формата.
(E- E+ e- e+) Экспоненциальный формат.

Примеры использования символов в выражениях числовых форматов VBA Excel:

1
2
3
4
5
6
7
8
9
10
11
12

SubFormatNumber2()

DimnAsDouble

n=2641387.7381962

‘n = 0.2397842

MsgBox»Форматируемое число = «&n&vbNewLine_

&vbNewLine&»0.##:  «&Format(n,»0.##»)&vbNewLine_

&vbNewLine&»000.###:  «&Format(n,»000.###»)&vbNewLine_

&vbNewLine&»#,###.###:  «&Format(n,»#,###.###»)&vbNewLine_

&vbNewLine&»0 %:  «&Format(n,»0 %»)&vbNewLine_

&vbNewLine&»0.### E-:  «&Format(n,»0.### E-«)&vbNewLine_

&vbNewLine&»0.### E+:  «&Format(n,»0.### E+»)

EndSub

Символы для текстовых форматов

Символ Описание
At-символ (@) Заполнитель для символов, отображающий знак или пробел.
Амперсанд (&) Заполнитель для символов, отображающий знак или ничего (пустая строка).
Меньше (<) Принудительный перевод всех буквенных символов в нижний регистр.
Больше (>) Принудительный перевод всех буквенных символов в верхний регистр.

Примеры использования символов в выражениях строковых форматов VBA Excel:

1
2
3
4
5
6

SubFormatString()

MsgBox»Номер телефона:  «&Format(«1234567890″,»+7 (@@@) @@@-@@-@@»)&vbNewLine_

&vbNewLine&»Серия и номер паспорта:  «&Format(«1234567890″,»&& && &&&&»)&vbNewLine_

&vbNewLine&»Нижний регистр:  «&Format(«Нижний регистр»,»<«)&vbNewLine_

&vbNewLine&»Верхний регистр: «&Format(«Верхний регистр»,»>»)

EndSub

За форматами скрываются обычные числа

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

Теперь предположим, что для данной ячейки изменяется формат после щелчка на кнопке Финансовый числовой формат вкладки Главная. Исходное значение примет вид 25,65р.

Подобное изменение может навести на мысль о том, что программа округлила результат до двух десятичных знаков. На самом деле программа округлила только представление вычисленного значения — в ячейке остается все то же значение 25,6456. Если вы будете использовать данную ячейку в другой формуле, то имейте в виду, что Excel подставит в нее истинное значение, а не отображаемое в ячейке.

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

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

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

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

  1. Выберите команду Файл→Параметры→Дополнительно, чтобы перейти на вкладку Дополнительно диалогового окна Параметры Excel.
  2. В группе При пересчете этой книги установите флажок Задать указанную точность и щелкните на кнопке ОК.

Откроется окно с предупреждением о том, что данные потеряют свою точность.

  1. Щелкните на кнопке ОК или нажмите клавишу <Enter>. Все данные изменятся в соответствии с экранным представлением.

Рекомендуется предварительно сохранить рабочую книгу с вычисленными значениями. После завершения преобразования всех значений листа путем установки флажка Задать указанную точность откройте диалоговое окно Сохранить как. Измените имя файла, отображаемое в поле Имя файла  перед щелчком на кнопке Сохранить или нажатием клавиши <Enter>. Для изменения имени файла можно к текущему имени файла добавить слова как на экране. В вашем распоряжении окажутся две копии файла: исходный файл книги, включающий введенные вами значения и значения, подсчитанные Excel, а также новый файл книги, содержащий отображаемые значения (с добавленными к названию словами как на экране).

Более сложные ситуации

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

Например, я нашел сообщение на форуме, где кто-то хотел извлечь числа из строки, например, «45t * 65 /», чтобы в итоге он получил «4565». Другой автор дал следующую формулу в качестве одного из способов сделать это:

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(—MID(A1,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

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

,

Функция Т в Excel и особенности ее синтаксической записи

Функция Т имеет следующий синтаксис:

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

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

  1. Если функция Т принимает на вход текстовые данные (строка текста, ячейка, содержащая текст), она вернет эти данные.
  2. Функция Т возвращает пустую строку, если она принимает на вход числовые данные, логическое значение, ссылку на ячейку, в которой не содержится текст. Примеры:
  • Результатом выполнения функции =Т(293) будет являться пустой текст «», поскольку принимаемое на вход значение является данными числового типа;
  • Следующая запись =Т(«функция Т работает») вернет строку текста «функция Т работает» в ячейку, в которой данная функция записана;
  • Если в ячейку ввести запись типа =Т(текст), то результатом выполнения функции будет код ошибки #ИМЯ?. Поэтому при явной передаче параметра в функцию его значение необходимо помещать в двойные кавычки.
  • Результатом выполнения функции =Т(A1) является текст, содержащийся в ячейке A1 или пустое значение «», если в данной ячейке находятся данные другого типа.

Как сделать числовой формат в excel?

На этом шаге мы рассматрим числовые форматы, которые можно использовать в Excel.

В Excel предусмотрено несколько числовых форматов. На рис. 1 показано диалоговое окно Формат ячеек, содержащее несколько вкладок. Для форматирования чисел используется вкладка Число.

Рис. 1. Диалоговое окно Формат ячеек. Вкладка Число

Существует несколько способов вызова диалогового окна Формат ячеек. Прежде всего необходимо выделить ячейку или ячейки, которые надо отформатировать, а затем выполнить одно из приведенных действий:

  • Выберать команду Формат | Ячейки.
  • Щелкнуть правой кнопкой мыши на выделенных ячейках и из контекстного меню выберать команду Формат ячеек.
  • Воспользоваться комбинацией клавиш Ctrl + 1.

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

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

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

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

Список категорий числовых форматов:

  • Общий. Этот формат принят по умолчанию. При его выборе числа отображаются в виде целых чисел, десятичных дробей или в экспоненциальном формате, если число слишком большое и не помещается в ячейке.
  • Числовой. Этот формат позволяет указать число десятичных знаков, определить использование разделителя групп разрядов и способ отображения отрицательных чисел (со знаком «минус», красным цветом или красным цветом со знаком «минус»).
  • Денежный. Этот формат позволяет указать число десятичных знаков, использование знака денежной единицы и способ отображения отрицательных чисел (со знаком «минус», красным цветом или красным цветом со знаком «минус»). В этом формате всегда используется разделитель групп разрядов.
  • Финансовый. Этот формат отличается от денежного тем, что знаки денежной единицы всегда выровнены по вертикали.
  • Дата. Эта категория позволяет выбрать один из 15 форматов отображения дат.
  • Время. Эта категория предоставляет на выбор 8 форматов отображения времени.
  • Процентный. Эта категория позволяет выбрать число десятичных знаков. Символ процента отображается всегда.
  • Дробный. Эта категория позволяет выбрать один из 9 дробных форматов отображения чисел.
  • Экспоненциальный. В этом формате число всегда отображается с буквой Е. Можно выбрать число десятичных знаков для отображения.
  • Текстовый. Применение текстового формата к числовому значению заставляет Excel рассматривать это число как текст.
  • Дополнительный. В эту категорию включено четыре дополнительных числовых формата (Почтовый индекс, Индекс+4, Номер телефона и Табельный номер).
  • Все форматы. Эта категория позволяет создавать собственные числовые форматы, не входящие ни в одну из других категорий.

На рис. 3 показаны примеры чисел, к которым применены все перечисленные категории форматирования.

Рис. 3. Примеры применения числовых форматов к значениям

Примечание . Если в ячейке находится ряд символов «решетка» (#########), то это значит, что столбец недостаточно широк для отображения числа в выбранном формате. Необходимо увеличить ширину столбца или изменить числовой формат.

На следующем шаге мы рассмотрим числовые форматы, определяемые пользователем.

Примеры использования функции ТЕКСТ в Excel

Для функции ТЕКСТ следует указать 2 обязательных для заполнения аргументы:

  1. Значение – числовое значение, ссылка на числовое значение, формула, результат которой число.
  2. Формат – числовой формат из списка числовые форматы (CTRL+1).

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

Задача: Необходимо в столбе F сформировать номенклатуру ведомости с наименованием, ценой и валютой. Цены должны быть разбиты на разряды по тысячам для лучшей читабельности (например, 28 000).

Воспользуемся функцией ТЕКСТ. Щелкнем в ячейку G3 и нажмем мастер функций нажав на кнопку fx или комбинацию клавиш SHIFT+F3. В разделе категория выберем текстовые и найдем необходимую функцию.

В окне аргументов зададим следующие значения:

Нажмем Ок и получим результат:

Все сумы приобрели денежный формат и стали удобочитаемыми в тексте номенклатуры вместе с наименованием товара

Стоит обратить внимание на то что изменение на денежный формат чисел в столбце E (Сумма) не позволило бы решить данную задачу так как Excel все равно воспринимает значения в денежном формате ячеек как числа:

Вот зачем нужна функция ТЕКСТ и ее весьма сложно заменить другими решениями.

Преобразование ТЕКСТовых значений в ЧИСЛА и ДАТЫ (Часть 2. Групповое изменение в EXCEL)

При копировании ЧИСЛОвых данных в EXCEL из других приложений бывает, что Числа сохраняются в ТЕКСТовом формате. Если на листе числовые значения сохранены как текст, то это может привести к ошибкам при выполнении вычислений. В ряде случаев можно быстро изменить текстовый формат на числовой сразу в нескольких ячейках. Произведем это преобразование с помощью Специальной вставки из Буфера обмена.

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

Как правило, в таких ячейках установлен текстовый формат. Если изменить формат ячейки на числовой (через Формат ячеек или Главная/ Число/ Числовой формат ), то изменений не последует.

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

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

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

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

  • введите в любую пустую ячейку число 1 ;
  • выделите эту ячейку и скопируйте ее значение в Буфер обмена ( CTRL+C );
  • выделите ячейки, которые содержат числа сохраненные как текст;
  • выберите Специальная ставка (на вкладке Главная в группе Буфер обмена щелкните стрелку рядом с командой Вставить , а затем выберите в списке пункт Специальная ставка );
  • выберите Умножить ;
  • нажмите ОК .

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

Для EXCEL 2007: Если до умножения ячейки имели текстовый формат ячеек, то он будет сохранен (числовые значения все равно будут преобразованы, но будут выровнены по левому краю, как текст). Изменить формат ячеек на числовой можно стандартным способом через меню Формат ячеек (нажав CTRL+1 ).

Этот же прием поможет преобразовать ДАТЫ, сохраненные как текст в формат ДАТ ( Если, конечно, EXCEL поймет ту форму, в которой записана ДАТА ). Правда, после преобразования в ячейках будет содержаться число, соответствующее датам . Изменить формат ячеек на формат ДАТЫ можно стандартным способом через меню Формат ячеек (нажав CTRL+1 ).

СОВЕТ: О преобразовании чисел из ТЕКСТового формата в ЧИСЛОвой можно также прочитать в одноименной статье Преобразование чисел из текстового формата в числовой (часть 1) .

Основные виды форматирования и их изменение

Сразу определим, какие форматы ячеек существуют. Программа предлагает выбрать один из следующих основных видов форматирования:

  • Общий;
  • Денежный;
  • Числовой;
  • Финансовый;
  • Текстовый;
  • Дата;
  • Время;
  • Дробный;
  • Процентный;
  • Дополнительный.

Кроме того, существует разделение на более мелкие структурные единицы вышеуказанных вариантов. Например, форматы даты и времени имеют несколько подвидов (ДД.ММ.ГГ., ДД.месяц.ГГ, ДД.М, Ч.ММ PM, ЧЧ.ММ и др.).

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

Способ 1: контекстное меню

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

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

Активируется окно форматирования. Выполняем переход во вкладку «Число», если окно было открыто в другом месте. Именно в блоке параметров «Числовые форматы» находятся все те варианты изменения характеристик, о которых шел разговор выше. Выбираем пункт, который соответствует данным, находящимся в выбранном диапазоне. При необходимости в правой части окна определяем подвид данных. Жмем на кнопку «OK».

После этих действий формат ячеек изменен.

Способ 2: блок инструментов «Число» на ленте

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

  1. Переходим во вкладку «Главная». При этом, нужно выделить соответствующие ячейки на листе, а в блоке настроек «Число» на ленте открыть поле выбора.

Просто производим выбор нужного варианта. Диапазон сразу же после этого изменит своё форматирование.

Но в указанном списке представлены только основные форматы. Если вы хотите более точно указать форматирование, то следует выбрать пункт «Другие числовые форматы».

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

Способ 3: блок инструментов «Ячейки»

Ещё одним вариантом настройки данной характеристики диапазона является использования инструмента в блоке настроек «Ячейки».

  1. Выделяем диапазон на листе, который следует отформатировать. Располагаясь во вкладке «Главная», кликаем по значку «Формат», который находится в группе инструментов «Ячейки». В открывшемся списке действий выбираем пункт «Формат ячеек…».

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

Способ 4: горячие клавиши

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

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

  • Ctrl+Shift+- — общий формат;
  • Ctrl+Shift+1 — числа с разделителем;
  • Ctrl+Shift+2 — времени (часы.минуты);
  • Ctrl+Shift+3 — даты (ДД.ММ.ГГ);
  • Ctrl+Shift+4 – денежный;
  • Ctrl+Shift+5 – процентный;
  • Ctrl+Shift+6 — формат О.ООЕ+00.

Урок: Горячие клавиши в Экселе

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

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Adblock
detector