Какие операции можно осуществлять с числовым форматом ячеек

Обновлено: 30.06.2024

Выбор формата данных осуществляется на вкладке Главная в группе Число, или при помощи контекстного меню (пункт Формат ячеек…).

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

По умолчанию Excel вводит значения в соответствии с числовым форматом Общий. Значения отображаются в том виде, в каком они были введены с клавиатуры. При этом Excel анализирует значения и автоматически присваивает им нужный формат, например, 100 — число 100, а 10:52 — числовой формат даты и времени.

Окно Формат ячеек имеет несколько вкладок: Число, Выравнивание, Шрифт, Граница, Вид, Защита.

Вкладка Число – задает форматы представления данных в ячейке:

Рис. 1.3. Установка форматов в табличном документе

Формат	Пояснение
Общий	Обеспечивает отображение числовых и текстовых данных произвольного типа
Числовой	Включает цифры и символы-разделители: десятичная точка, знак числа
Денежный или Финансовый	Используется для отображения денежных величин, обеспечивает денежное обозначение валют разных стран по выбору из списка (р, $ и др.)
Дата/время	Используется для отображения даты и времени в выбранном формате
Процентный	Используется для вывода чисел, предварительно умноженных на 100, с символом процента
Дробный	Используется для вывода дробных чисел
Экспоненциальный	Рекомендуется для вывода "длинных" чисел в экспоненциальном формате, например, 1,65Е+044
Текстовый	Обеспечивает отображение последовательности букв, цифр, специальных символов
Дополнительный	Используется для отображения нестандартных дополнительных форматов, например, номеров телефонов, почтовый индекс и пр.
Все форматы	Показывает все имеющиеся в MicrosoftExcel форматы

Вкладка Выравнивание определяет:

§ Выравнивание – способ выравнивания данного в ячейке

- по горизонтали (по левому или правому краю, по значению, по центру выделения, по центру, по ширине, с заполнением);

- по вертикали (по нижнему или верхнему краю, по центру или высоте);

§ Отображение – определяет, можно ли переносить в ячейке текст по словам, разрешает или запрещает объединение ячеек, задает автоподбор ширины ячейки.

§ Направление текста – позволяет задавать направление ввода текста: слева направо, справа налево и по контексту.

Вкладка Шрифт – изменяет шрифт, начертание, размер, цвет, подчеркивание и видоизменение текста в выделенных ячейках.

Вкладка Граница – создает рамки (обрамление) вокруг выделенного блока ячеек.

Вкладка Заливка – позволяет задать закраску ячейки (цвет и узор).

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

1.6.3. Перенос текста в ячейке Excel

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

Для переноса текста в ячейке удобно использовать командную кнопкуПеренести текст, распложенную на ленте MSExcel на вкладке Главная в группе Выравнивание.

Объединение ячеек

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

Из выделенного диапазона образуется одна ячейка. Причем, координаты этой ячейки будут приняты от первой (в общем случае левой верхней) из ячеек выделенного для объединения диапазона. В примере на рисунке ниже объединенная ячейка имеет координаты B3.

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

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

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

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

Числовые форматы

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

Общий числовой формат

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

Встроенные числовые форматы

Форматирование ячеек и списков

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

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

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

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

Форматирование ячеек по заданным условиям

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

Изменение условий. Если значение ячейки изменилось и больше не удовлетворяет заданному условию, Microsoft Excel удаляет форматирование из ячейки, но сохраняет условие, так что форматирование будет снова автоматически применено, если значение ячейки удовлетворит условию.

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

Формулы в качестве критерия форматирования

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

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

· Использование формулы в качестве условия . Если был выбран параметр формула, то указанная формула должна принимать значения ИСТИНА (1) или ЛОЖЬ (0). Формула должна начинаться со знака равенства (=). Вычисления по данной формуле могут производиться только на активном листе. Чтобы оценить данные на другом листе или в другой книге, на активном листе необходимо указать имя для данных на другом листе или в другой книге либо ввести ссылку на эти данные в ячейке активного листа, а затем сослаться на указанную ячейку или имя в формуле.

При помощи формулы можно также оценивать критерии, не основанные на данных листа. Например, формула =ДЕНЬНЕД("12.5.99")=1 принимает значение ИСТИНА, если дата 12 мая 1999 г. является воскресеньем.

Использование ссылок на ячейки в качестве условия . Ссылки на ячейки можно ввести в формулу, выделив ячейки непосредственно на листе. Выделение ячеек на листе вставляет абсолютные ссылки . Если необходимо, чтобы ссылки для каждой ячейки в выделенном диапазоне были согласованы, используйте относительные ссылки .
Использование дат . Даты и время рассматриваются как числа. Например, при сравнении содержимого ячейки с датой 7 января 2001 дата будет представлена в виде числа 36898.

Пример 1. Использование данных в ячейке

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

Приведенная ниже таблица указывает условные форматы для ячеек B2:E3. В формуле определяется относительная часть (номер строки) ссылки на ячейку $F2, чтобы каждая ячейка из диапазона B2:E3 сравнивалась с соответствующим значением итога в столбце F.

Расчёты с использованием сложных формул, построение сводных таблиц и графиков, написание макросов — это явно не то, с чего началось Ваше знакомство с Excel. На первых порах ваши таблички выглядели примерно вот так (см. рисунок ниже) и самая главная проблема была в том: «Как сделать из чисел проценты, а суммы со знаком рубль/доллар?”

Все начинали с подобных таблиц 🙂

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

О чём вообще речь? Покажи примеры!

В Excel достаточно много уже готовых форматов, однако возможны ситуации, в которых ни один вам не подойдет.

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

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

Где вы можете использовать пользовательские форматы чисел?

Самый распространённый вариант использования пользовательских форматов – это непосредственно таблица на листе Excel, но также Вы можете использовать их:

в сводных таблицах — с помощью настроек поля значения

при построении графиков (в подписях данных и в настройках осей)

в формулах (через функцию ТЕКСТ)

Для начала давайте всё же разберёмся с основными понятиями.

Что такое формат чисел?

Пользовательский формат — это специальный код, отвечающий отображение значения в Excel. Например, в таблице ниже показаны 8 разных форматов чисел, примененных к той же дате, 1 мая 2020 года:

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

Где вы можете найти числовые форматы?

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

Общий формат по умолчанию

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

Как изменить формат ячейки?

Способ 1. Формат по образцу (одноразовое использование)

Способ 2. Формат по образцу (МНОГОразовое использование)

Всё как и в первом способе, только делайте двойной клик по иконке Формат по образцу. Чтобы завершить использование формата по образцу нажмите ESC

Способ 3. Через специальную вставку

Как создавать свои собственные форматы

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

Форматы, которые Вы создаёте самостоятельно хранятся в текущем Excel-файле, а не в Excel вообще. Если вы скопируете значение, отформатированное в соответствии с пользовательским форматом, из одного файла в другой, то формат будет перенесен в книгу вместе со значением.

Как создать собственный формат номера

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

Выберите ячейку (ячейки) со значениями, которые вы хотите отформатировать.
Нажмите сочетание клавиш Ctrl + 1 >Число >Все форматы
Введите код формата и просмотрите в поле как будет выглядеть значение в ячейке.
Нажмите OK, чтобы сохранить и применить только что созданный формат

Как показывает практика, на шаге 3 возникают основные сложности, т.к. пока вам не совсем понятно что писать в поле Тип.

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

Как изменить пользовательский формат

Структура формата и справочная информация

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

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

Не все разделы необходимы

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

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

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

Коды для настройки формата

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

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

Ноль (0) используется для принудительного отображения нулей, когда число имеет меньше цифр, чем нули в формате. Например, пользовательский формат 0,00 будет показывать нуль как 0,00, 1,1 как 1,10 и ,5 как 0,50.

Примеры использования 0 (ноль) в формате

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

Примеры использования ? (знака вопроса) в формате

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

Звёздочка (*) используется для повторения символов. Символ, следующий за звездочкой, будет повторяться, чтобы заполнить оставшееся пространство в ячейке.

Примеры использования * (звёздочки) в формате

Примеры использования _ (подчёркивания) в формате

Форматы ячеек для ТЕКСТА

Знаки, которые можно использовать в формате

Пример знаков кода формата, отображаемых без кавычек

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

Пользовательские форматы для дат

Даты в Excel — это просто цифры, поэтому вы можете использовать пользовательские форматы чисел, чтобы изменить способ отображения. Excel многие конкретные коды, которые вы можете использовать для отображения компонентов даты по-разному. На следующей картинке показано, как Excel отображает дату в C5, 14 августа 2019 года, с различными форматами:

Коды формата для дат

Форматы для отображения времени

Время в Excel — это дробные части дня. Например, 6:00 – 0,25; 12:00 — 0,5, а 18:00 — 0,75. Вы можете использовать следующие коды в своих форматах для отображения компонентов времени по-разному. Посмотрите ниже как Excel отображает время в D5, 9:35:07, с различными форматами:

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

Прошедшее время — это особый случай для отображения значений, превышающих 24 для часов и 60 для минут и секунд. Достаточно добавить квадратные скобки [], чтобы увидеть в ячейке сколько прошло часов, минут и секунд. На следующем экране показано, как Excel показывает прошедшее время, основанное на значении в D5, которое составляет 1,25 дня:

Цифровые форматы для ЦВЕТОВ

Существует два способа определения цвета в формате ячеек. Самый распространённый вариант – написать в квадратных скобках название цвета. Excel знает следующие 8 цветов по имени в цифровом формате:

[черный]
[белый]
[красный]
[зеленый]
[синий]
[желтый]
[пурпурный]
[голубой]

Имена цветов должны появляться в скобках.

Пример формата ячейки с цветами (пишем название текстом)

Если вам мало 8 цветов, то радостная весть в том, что также можно указать цвета по номеру индекса (Цвет1, Цвет2, Цвет3 и т. Д.). Нижеприведенные примеры используют формат пользовательского номера: [ЦветX] 0, где X — номер от 1 до 56

Символы треугольника добавлены только для того, чтобы сделать цвета более удобными для просмотра. Первое изображение отображает все 56 цветов на стандартном белом фоне. На втором изображении изображены те же цвета на сером фоне. Обратите внимание, что первые 8 цветов соответствуют названному списку цветов выше.

Проверка условий

Форматы пользовательских номеров также допускают условия, которые записываются в квадратных скобках, таких как [> 100] или [ 0, =100]0

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

Применение форматов в формуле ТЕКСТ

Хотя большинство форматов чисел применяются непосредственно к ячейкам на листе, вы также можете применять форматы чисел внутри формулы с помощью функции ТЕКСТ. Например, в ячейке A1 написана формула СЕГОДНЯ(). Ниже два варианта получения названия месяца.