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

Вкладка Число

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

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

Довольно часто используют элементы Date (выбор формата отображения даты), Percentage (отображение в процентном формате) и Text (данные хранятся в виде текста). Текстовый формат довольно интересен, так как позволяет отображать информацию в том виде, в каком она введена в ячейку, без каких-либо автоматических преобразований в дату, формулу, обрезку первых нулей и т.д. То, что введено, мы видим в ячейке . Команды выбора формата номера продублированы на ленте.

Вкладка Выравнивание

Перейдите на вкладку «Регулировка». Это указывает на положение числа или текста внутри ячейки: по вертикали, по горизонтали, относительно центра, по направлению и так далее

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

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

Читать это нецелесообразно (все сливается воедино и надо смотреть внимательно). Поэтому правильно делать отступ текста от левого края. Для этого достаточно выделить пункт по левому полю (отступу) в горизонтальном списке и поставить рядом с ним в поле значение отступа. См изображение ниже.

Теперь он намного приятнее для глаз.

Таким же образом можно убрать цифру с правого края.

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

«Продажи» захватывает все столбцы. Выглядит красиво, но не практично. Например, когда вы выбираете один столбец, диапазон автоматически расширяется до всех столбцов ниже объединенной ячейки. Если вы попытаетесь скопировать этот диапазон, вы можете получить сообщение об ошибке о несоответствии между ячейками. В общем, не очень практично. При этом вы должны сохранить внешний вид, как на картинке выше. Это простой трюк. Достаточно ввести «Продажи» в ячейку в крайнем левом углу области, посередине как положено. Затем, начиная с этого слова, выделите оставшиеся ячейки в области справа и выберите элемент в центре выделения на вкладке «Выравнивание.

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

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

Значительная часть команд с вкладки «Корректировка» также перенесена на ленту.

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

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

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

Последняя вкладка Безопасность используется для ограничения доступа к ячейкам (можно сделать так, чтобы нельзя было вносить изменения и т.п.). В конце статьи есть видео с подробным объяснением того, как защитить лист Excel и отдельные ячейки.

Наиболее часто используемые команды находятся на ленте.

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

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

Если у вас возникли проблемы с оформлением презентации или разные варианты формата не приводят к нужному результату, вы можете использовать готовые стили. Выберите нужный диапазон и нажмите кнопку «Стили ячеек» на вкладке «Главная» ленты.

Здесь вы можете выбрать готовое решение. Но я не особо пользуюсь этой возможностью. Будет слишком красочно, но достаточно стильно, по-современному.

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

Виды форматов ячеек

Во-первых, давайте перечислим основные форматы ячеек, доступные в редакторе электронных таблиц:

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

Примечание. Для большинства перечисленных форматов можно настроить тип отображения данных (например, «Дата»). Невозможно настроить следующие форматы: Общий и Текст.

Теперь приступим непосредственно к способам изменения формата ячеек.

Метод 1: через контекстное меню

Этот способ является наиболее распространенным среди пользователей. Вот что мы делаем:

1. Щелкаем правой кнопкой мыши по ячейке, формат которой вы хотите задать/изменить, и в появившемся списке команд выбираем «Форматировать ячейки”.
   Если это действие необходимо выполнить для диапазона ячеек, сначала выберите его соответствующим образом, а затем также щелкните правой кнопкой мыши в любом месте выделенного диапазона и выберите нужный элемент.
   Если это действие необходимо выполнить для диапазона ячеек, сначала выберите его соответствующим образом, а затем также щелкните правой кнопкой мыши в любом месте выделенного диапазона и выберите нужный элемент.
2. На экране появится окно Формат ячеек. Вкладка «Число» должна выбраться автоматически (если по каким-то причинам этого не произошло, переключитесь на нее). В списке слева определяем формат, после чего задаем подробные параметры справа (если предлагается). Нажмите OK, когда будете готовы.
3. В результате мы смогли изменить формат ячейки (в нашем случае числовой, с разделителем цифр, два знака после запятой).

Метод 2: применение инструментов на лене

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

Блок “Число”

1. Выберите ячейку или выберите определенный диапазон на вкладке «Главная» группы инструментов «Число», нажмите стрелку вниз рядом с текущим форматом.
2. Откроется список вариантов (можно листать вверх-вниз), среди прочего выбираем нужный.
   Примечание. Этот метод позволяет выбирать форматы только в их базовой форме. Для более детальной настройки нам понадобится «Другие числовые форматы”.
   Примечание. Этот метод позволяет выбирать форматы только в их базовой форме. Для более детальной настройки нам понадобится «Другие числовые форматы”.
   В результате мы увидим такое же окно форматирования, которое мы уже рассматривали в первом способе.

Блок “Ячейки”

1. Делаем выделение нужной ячейки/диапазона и нажимаем на кнопку «Формат» в основной вкладке блока инструментов «Ячейки”.
2. В появившемся списке нажмите на строку «Формат ячеек”.
3. И снова открывается окно форматирования, где мы можем произвести необходимые настройки.

Метод 3: использование горячих клавиш

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

1. Как и в приведенных выше методах, сначала выберите ячейку или диапазон.
2. Затем нажмите клавиши Ctrl + 1.
3. Откроется знакомое окно, в котором можно выбрать тип данных и настроить представление.

Примечание. В большинстве случаев достаточно Ctrl+1. Для тех, кто хочет ускорить свою работу, есть следующие горячие клавиши, позволяющие выбрать формат (основной вид) без открытия соответствующего окна:

• Ctrl+Shift+1 – числовой (с разделителем и двумя знаками после запятой);
• Ctrl+Shift+4 – дата (ДД.ММ.ГГГГ);
• Ctrl+Shift+5 — проценты (без десятичных знаков);
• Ctrl+Shift+6 – время (часы:минуты).

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

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

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

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

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

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

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

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

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

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

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

1. Перейдите на вкладку «Главная». При этом нужно выделить соответствующие ячейки на листе, и открыть поле выбора в блоке настроек «Число» на ленте.
2. Мы только делаем выбор нужного варианта. Сразу после этого область изменит форматирование.
3. Но этот список содержит только основные форматы. Если вы хотите уточнить форматирование, выберите «Другие числовые форматы».
4. После этих действий откроется окно форматирования области, о котором уже говорилось выше. Здесь пользователь может выбрать любой из основных или дополнительных форматов данных.

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

Другой вариант установки этого свойства области — использование инструмента в блоке настроек «Ячейки».

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

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

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

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

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

Урок: Сочетания клавиш в Excel

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

Формат даты со временем в Exel и 2 системы его отображения

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

восемь

В первой колонке формат в принципе задан правильно. Посмотрим на второй столбец. Выделите все ячейки индикаторов во втором столбце, нажмите комбинацию клавиш CTRL+1, в разделе «Число» выберите время, а в категории «Тип» выберите способ отображения, соответствующий следующему изображению:

девять

Аналогичные действия проделываем с третьим и четвертым столбцом. Мы указываем форматы и типы отображения, которые соответствуют объявленным именам столбцов. В электронной таблице есть 2 системы отображения даты:

1. Число 1 — 1 января 1900 года.
2. Цифра 0 — это 1 января 1904 года, а цифра 1 — 01.02.1904.

Изменение отображения дат осуществляется следующим образом:

1. Заходим в «Файл».
2. Нажмите «Параметры» и перейдите в раздел «Дополнительно».
3. В блоке «При пересчете этой книги» выберите вариант «Использовать систему дат 1904 года».

Работа со стилями форматирования ячеек Excel

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

двадцать

Зачем нужны именованные стили

Основные цели использования стилей:

1. Создавайте уникальные наборы стилей для редактирования заголовков, подзаголовков, текста и многого другого.
2. Использование созданных стилей.
3. Автоматизация работы с данными, так как вы используете стиль, вы можете форматировать абсолютно любые данные в выбранной области.

Применение стилей к ячейкам листа

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

1. Перейдите на вкладку «Главная», найдите блок «Стили ячеек».

21

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

Создание новых стилей

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

1. Выберите ячейку и отформатируйте ее. Мы создадим стиль на основе этого форматирования.
2. Перейдите в раздел «Главная» и перейдите в блок «Стили ячеек». Нажмите «Создать стиль ячейки». Открывается окно под названием «Стиль».

22

1. Введите «имя стиля».
2. Задаем все необходимые параметры, которые вы хотите применить к созданному стилю.
3. Нажимаем «ОК».
4. Теперь ваш уникальный стиль добавлен в библиотеку стилей, которую можно использовать в этом документе.

Изменение существующих стилей

Готовые стили, находящиеся в библиотеке, можно изменять самостоятельно. Рассмотрение:

1. Перейдите в раздел «Главная» и выберите «Стили ячеек».
2. Щелкните правой кнопкой мыши стиль, который хотите изменить, и выберите «Редактировать».
3. Откроется окно стиля».
4. Нажмите «Формат» и настройте форматирование в появившемся окне «Формат ячеек». После выполнения всех манипуляций нажмите «ОК».
5. Нажмите OK еще раз, чтобы закрыть окно стиля. Редактирование готового стиля завершено, теперь его можно применять к элементам документа.

Перенос стилей в другую книгу

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

Рассмотрение:

1. Отрываем документ, где размещены созданные стили.
2. Кроме того, откройте еще один документ, в который мы перенесем созданный стиль.
3. В документе со стилями перейдите на вкладку «Главная» и найдите блок «Стили ячеек».
4. Нажмите «Объединить». Появилось окно «Объединить стили».
5. Это окно содержит список всех открытых табличных документов. Выберите документ, в который хотите перенести созданный стиль, и нажмите кнопку «ОК». Прозрачный!

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

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

На скриншоте выделены ячейки с итоговыми значениями (диапазон E3:E17). Данные ячейки были отформатированы с помощью кнопки «Формат финансового числа», расположенной в группе «Число» на вкладке «Главная». Отличительной чертой этого формата является знак валюты (в данном случае рубля).

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

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

Формат ячеек в Excel: Числовой формат с разделителями

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

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

Ограниченный формат полезен для создания отчета о продажах за первый квартал. Обратите внимание на скриншот ниже, на котором указана стоимость работ за 1 м2, отформатированных данным форматом. Чтобы создать пример, я сначала выбрал диапазон B3:D16, а затем нажал кнопку «Формат разделения», обозначенную тремя нулями. Эта кнопка находится в группе цифр на вкладке «Главная.

Формат ячеек в Excel: Процентный формат

Многие электронные таблицы используют проценты для отображения годовых цен, уровня инфляции, прибыльности и т д. Чтобы вставить процентное значение в ячейку, введите числовое значение, а затем символ процента. Чтобы ввести годовую ставку 12 процентов, введите 12 % в ячейку. В ответ Excel автоматически присвоит ячейке процентный формат и при этом разделит введенное значение на 100. В этом случае мы увидим в ячейке 0,12.

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

На скриншоте ниже к значениям в столбце F применен процентный формат. Для этого выберите соответствующие ячейки, затем нажмите кнопку «Процентный стиль», расположенную в группе «Число» на вкладке «Главная.

Как изменить количество знаков после запятой в Excel?

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

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

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

Теперь предположим, что эта ячейка отформатирована после того, как вы нажмете кнопку «Формат финансового числа» на вкладке «Главная». Начальное значение будет в виде 25,65 руб.

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

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

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

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

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

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

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

1. Нажмите кнопку OK или нажмите клавишу . Все данные изменятся в соответствии с представлением на экране.

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

Создание пользовательских форматов для категорий значений

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

1. Положительные числа
2. Отрицательные числа
3. Нулевые значения
4. Текстовый формат

Для этого в поле Тип можно указать следующую конструкцию:

Формат для положительных значений; отрицательный; ноль ; текст 

Соответственно, для каждой категории можно использовать уже описанный нами формат формы:

цвет«любой текст»Код формата»любой текст»

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

Посмотрите, какой эффект это имеет. В зависимости от значения форматирование меняется, и если вместо числа вводится текстовое значение, Excel отображает «нет данных».

Форматы данных вводимых в ячейки электронной таблицы

Заполните диапазон ячеек A2:A7 числом 2 и отформатируйте все ячейки, как показано на рисунке выше.

Решение проблемы:

1. Выберите диапазон A2:A7, введите цифру 2 и нажмите комбинацию клавиш CTRL+Enter.
2. Ячейка А2 должна иметь формат «Вообще он задан по умолчанию в Excel. Поэтому сразу переходим в ячейку А3. Щелкаем по инструменту «Главная» — «Числа» — «Увеличить разрядность» (увеличить количество отображаемых чисел после десятичная точка).
3. Перейдите в ячейку А4 и нажмите инструмент «Главная» — «Числа» — «Процентный формат» или комбинацию клавиш CTRL+SHIFT+5. Или просто введите 200% вручную и форматирование для ячейки будет назначено автоматически.
4. Нажмите на ячейку A5 и нажмите на инструмент «Формат финансового числа», он немного отличается от денежного, но об этом позже…
5. Сделайте ячейку A6 активной и нажмите угловую кнопку со стрелкой вниз в разделе «Главная» — «Число» или нажмите комбинацию клавиш CTRL+1. В появившемся окне на вкладке «Число» выберите вариант «Дата» из раздел «Числовые форматы: », а в разделе «Тип:» укажите правильный способ отображения дат. Раздел «Образец:» показывает предварительный просмотр отображения содержимого ячейки.
6. Перейдите в ячейку A7 и также нажмите комбинацию клавиш CTRL+1, чтобы вызвать диалоговое окно «Формат ячеек», только на этот раз мы выбираем опцию «Время».

Какие параметры предоставляет диалоговое окно «Формат ячеек»? Функции всех инструментов форматирования, которые содержит вкладка «Главная», можно найти в этом диалоговом окне (CTRL+1) и других.

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

• финансовый формат при отображении чисел меньше 0 ставит минус слева от ячейки, а денежный формат ставит минус перед числом;
• формат валюты по умолчанию показывает отрицательные значения красным шрифтом (например, введите в ячейку значение -2p, и формат валюты будет назначен автоматически);
• в финансовом формате после сокращения валюты при отображении значений добавляется 1 пробел.

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

Что касается даты в ячейке A6, здесь стоит упомянуть правила Excel. Форматом даты считается последовательность дней с 1 января 1900 года. То есть, если в ячейке стоит значение — число 2, то это число в формате даты должно отображаться как 02.01.1900 и так далее.

Время для Excel — это значение числа после запятой. Так как в ячейке A7 у нас есть целое число, то и время там отображается соответственно.

Классификация типов данных

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

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

В свою очередь константы делятся на пять групп:

• Текст;
• Числовые данные;
• Дата и время;
• Логические данные;
• Неверные значения.

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

Урок: Как изменить формат ячейки в Excel

Текстовые значения

Текстовый тип содержит символьные данные и не рассматривается Excel как объект для математических вычислений. Эта информация в первую очередь предназначена для пользователя, а не для программы. Текст может быть любым символом, включая цифры, если они правильно отформатированы. В DAX этот тип данных относится к строковым значениям. Максимальная длина текста 268435456 символов в одной ячейке.

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

Числовые данные

Численные данные используются для прямых расчетов. Именно с ними Excel выполняет различные математические операции (сложение, вычитание, умножение, деление, возведение в степень, извлечение корня и так далее). Этот тип данных предназначен исключительно для записи чисел, но может содержать и вспомогательные символы (%, $ и т д.). Его можно использовать в нескольких форматах:

• Фактический числовой;
• Процент;
• Денежный;
• Финансовый;
• Фракционированный;
• Экспоненциальный.

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

Числовые данные вводятся так же, как и текстовые значения, о которых мы говорили выше.

Дата и время

Другим типом данных является формат времени и даты. Это как раз тот случай, когда типы данных и форматы совпадают. Характеризуется тем, что с его помощью можно заносить на лист и производить расчеты с датами и временем. Примечательно, что в расчетах этот тип данных принимает за единицу сутки. И это касается не только дат, но и времени. Например, 12:30 считается программой за 0,52083 дня, и только потом отображается в ячейке в известном пользователю виде.

Существует несколько типов форматирования времени:

• ч:мм:сс;
• хм;
• ч:мм:сс AM/PM;
• ч: мм AM/PM и т д.

Аналогичная ситуация с датами:

• ДД.ММ.ГГГГ;
• ДД.МММ
• МММ.ГГ и другие.

Существуют также комбинированные форматы даты и времени, например ДД:ММ:ГГГГ ч:мм.

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

Логические данные

Довольно интересен тип данных Boolean. Он работает только с двумя значениями: «ИСТИНА» и «ЛОЖЬ». Если утрировать, то это означает «событие наступило» и «событие не наступило». Функции, которые обрабатывают содержимое ячеек, содержащих логические данные, выполняют определенные вычисления.

Ошибочные значения

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

• #СТОИМОСТЬ! – использование неправильного типа аргумента для функции;
• #ОДИН БИЗНЕС! – деление на 0;
• #КОЛИЧЕСТВО! – неверные числовые данные;
• #Н/Д – введено недоступное значение;
• #ИМЯ? – неправильное имя в формуле;
• #ПУСТОЙ! — некорректный ввод адресов сайтов;
• #СОЕДИНЯТЬ! — возникает при удалении ячеек, на которые ранее ссылалась формула.

Формулы

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

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

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

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

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

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

Финансовый или денежный – в чём отличия?

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

1. В финансовом формате отрицательные числа отображаются в круглых скобках, и вы не можете изменить отображение отрицательных чисел по умолчанию. Для этого нужно создать собственное форматирование.
2. Символ валюты в финансах выравнивается по левому краю ячейки, а само число выравнивается по правому краю. В денежном выражении не выбивается из числа.
3. В финансовом формате нельзя использовать выравнивание значения в ячейке справа или слева от границы. Валюта всегда перемещается влево, а число всегда перемещается вправо и до конца. В денежном выражении есть возможность изменить горизонтальное и вертикальное выравнивание ячейки.
4. В денежном формате отрицательные значения могут быть выделены цветом, и их можно по желанию заключить в скобки.
5. В финансах вместо нуля появляется символ «-». В денежном выражении обычное число равно 0.

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

Как преобразовать число в денежный формат по условию?

Таблица расчетных операций с разными странами в валюте евро:

Данные в столбце «Результат» будут отформатированы следующим образом:

• если это сумма страны Россия, то результат в рублях;
• если сумма страны из зоны ЕС — значит в валюте евро.

Создайте новое правило с формулой:

1. Выделите диапазон ячеек D2:D13 и выберите инструмент: «ГЛАВНАЯ» — «Стили» — «Условное форматирование» — «Управление правилами».
2. В появившемся окне «Диспетчер правил условного форматирования» нажмите кнопку «Создать правило».
3. Выберите: «Создать правило форматирования» выберите параметр «Использовать формулу для определения форматированных ячеек». В поле ввода введите формулу: =F2=»Россия»
4. Нажмите кнопку «Формат» и в появившемся окне «Формат ячеек» на вкладке «Числа» укажите соответствующий формат валюты, как показано на рисунке.
5. После нажатия на окна «Формат ячеек» и «Создать правило форматирования», кнопки ОК, возвращаемся в окно диспетчера.
6. Там же создаем второе правило для этого нажимаем на соответствующую кнопку. И в поле ввода введите формулу: =F2<>«Россия»
7. Нажмите кнопку «Формат» и в появившемся окне «Формат ячеек» на вкладке «Число» укажите валютный формат валюты EUR.
8. Нажмите OK во всех открытых окнах.

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

В столбце D числовые значения сумм были автоматически отформатированы в соответствующую денежную валюту.

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

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

Допустим, нам нужно найти такое же количество транзакций в столбце E исходной таблицы. За это:

1. Выделите диапазон ячеек E2:E13 и выберите инструмент: «ГЛАВНАЯ» — «Стили» — «Условное форматирование» — «Правила выбора ячеек» — «Повторяющиеся значения».
2. В появившемся окне из выпадающего списка слева выберите вариант «Повторить», а справа «Зеленая заливка и темно-зеленый текст».

В результате ячейки с одинаковым количеством транзакций выделяются цветом.

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

1. Выбрав диапазон E2:E13, выберите инструмент: «ГЛАВНАЯ» — «Стили» — «Условное форматирование» — «Правила выбора ячеек» — «Создать правило».
2. В появившемся окне «Создать правило форматирования» на этот раз выберите «Форматировать только уникальные или повторяющиеся значения».
3. В раскрывающемся списке «Форматировать все:» должно быть установлено значение «Дублировать».
4. Нажмите кнопку «Формат», чтобы установить зеленый цвет на вкладке «Заливка». И нажмите OK на всех открытых окнах в программе.

Более подробный разбор использования формул для поиска дубликатов: Как найти одинаковые значения в столбце Excel?

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