Формат ячеек в Excel

Форматирование ячеек – это одно из основных отличий Excel от калькулятора. Умелое форматирование позволяет разношерстный набор данных привести к удобному для осмысления виду: выделить итоговую строку, шапку, убрать «лишние» знаки после запятой, добавить границы и т.д. В общем, форматирование позволяет набор «серой массы данных» превратить в структурированную и читабельную информацию.

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

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

Если этих кнопок не достаточно, то можно обратиться к Формату ячеек в контекстном меню (через правую кнопку мыши), либо с помощью сочетания горячих клавиш Ctrl+1, что существенно быстрее. Откроется известное многим окошко.

Диалоговое окно формата ячеек

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

Автоматическое преобразование формата ячейки в EXCEL при вводе ТЕКСТовых данных (Часть 1)

При вводе пользователем данных, EXCEL пытается определить тип вводимых данных. Если данные можно перевести в формат даты или числа, то EXCEL производит соответствующее преобразование и форматирование. Преобразование производится «на лету» после нажатия клавиши ENTER . Часто текстовые данные действительно имеют формат дат (1-05, 2.12) или чисел (000123456), но на самом деле ими не являются. В этом случае необходимо запретить EXCEL выполнять автоматическое преобразование и форматирование.

При вводе текстовых данных определенного вида, например: 1-05, 2.12, 1 e 2, 00012456 , EXCEL автоматически меняет формат ячейки и преобразовывает текст в другой, как ему кажется, подходящий тип данных. Это приводит к тому, что 1-05 и 2.12 будут преобразованы в даты ( 01.май и 02.дек ), а 1 e 2 и 000123456 в числа (в 1,00 E +02 , т.е. 100 и в 123456 ).

Допустим, мы вводим текст 1-05 (пусть это будет артикул товара) и хотим, чтобы EXCEL сохранил в ячейке именно текст, а не что-либо другое.

Что же происходит при вводе значения в ячейку? Если ячейка имеет формат Общий (формат ячейки по умолчанию), EXCEL преобразует текст 1-05 в дату 01.май : формат ячейки изменяется с Общий на формат Дата (точнее все форматы /ДД.МММ). Если мы вернем ячейке формат Общий , то в ячейке увидим число 40299 (если сейчас 2010 год), что соответствует 01.05.2010 . Вернуть исходное значение 1-05 простым преобразованием формата уже не представляется возможным — EXCEL интерпретировал 1-05 , как дату 01.05.2010 и вставил в ячейку соответствующее ей число 40299 .

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

Есть и другой способ. Чтобы EXCEL автоматически при вводе текстовых данных назначал ячейкам текстовый формат, перед вводимым текстом можно ввести знак апострофа (‘), т.е. в нашем случае необходимо ввести ‘1-05 (давайте введем его на новом листе в ячейку А1 ). Почему именно апостроф? Есть две причины: этот символ не отображается в ячейке (если он введен первым) и он игнорируется формулами. Например, после ввода формулы =ЛЕВСИМВ(A1;4) получим 1-05 , а не ‘1-0 , как вроде следовало бы ожидать.

Использование апострофа — не единственный способ отмены автоматического преобразования формата при вводе значений. Можно вместо апострофа ввести перед словом пробел или после слова точку. Например, если нужно ввести 2.3 (что превращается 02.мар ), то вводим 2.3. (после 3 стоит точка).

Другой пример об автоматическом преобразовании формата читайте в Части 2 .

СОВЕТ: Неправильный формат значения – это частый тип ошибки, к тому же, который трудно найти. Подсказкой может служить выравнивание значения в ячейке: если значение выровнено по правой стороне, то это число, а если по левой, то текст. Если ранее было сделано другое выравнивание в ячейке, то через Формат ячеек / Вкладка Выравнивание / По горизонтали установите параметр « По Значению ».

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

Excel. Основы. Формат по образцу

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

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

Специальная вставка

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

2) Скопируйте эти ячейки в буфер обмена ( Ctrl+C или правая кнопка мыши — Копировать );

3) Выделите ячейки или диапазон ячеек, куда нужно перенести форматирование;

4) Нажмите правую кнопку мыши и выберите пиктограмму вставки форматирования.

Пункт 4 можно выполнить по другому. Вместо пиктограммы форматирования можно выбрать "Специальная вставка. " (или нажать сочетание клавиш Ctrl+Alt+V ) и в открывшемся диалоговом окне установить переключатель на пункт "форматы" . После нажатия ОК, форматирование применится к ячейкам.

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

Формат по образцу

Более удобным способом переноса форматирования является использование специальной команды "Формат по образцу". Она находится на вкладке "Главная" в группе команд "Буфер обмена" (в левом краю ленты).

Для копирования форматов с помоьщью этой команды выполните следующие шаги:

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

2) Нажмите кнопку "Формат по образцу". Если нужно скопировать форматы в один диапазон или ячейку — достаточно будет одного нажатия. Если нужно будет применять форматирование к нескольким несмежным диапазонам или ячейкам, то кликните на кнопке дважды.

3) После клика на кнопке рядом с курсором появится значок кисточки. Это признак того, что форматирование готово к переносу.

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

Чтобы выйти из режима Форматирования по образцу нажмите клавишу ESC .

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

Видеоверсию данной статьи смотрите на нашем канале на YouTube

Чтобы не пропустить новые уроки и постоянно повышать свое мастерство владения Excel — подписывайтесь на наш канал в Telegram Excel Everyday

Куча интересного по другим офисным приложениям от Microsoft (Word, Outlook, Power Point, Visio и т.д.) — на нашем канале в Telegram Office Killer

Вопросы по Excel можно задать нашему боту обратной связи в Telegram @ExEvFeedbackBot

Вопросы по другому ПО (кроме Excel) задавайте второму боту — @KillOfBot

Как изменить формат ячейки в Excel быстро и качественно

Добрый день, форумчане!
Такая проблема — замучила уже совсем.

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

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

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

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

Добрый день, форумчане!
Такая проблема — замучила уже совсем.

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

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

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

Подскажите — что можно сделать?
Предполагаю, что решение может быть очень простым, но моих знаний не хватает. A138

Сообщение Добрый день, форумчане!
Такая проблема — замучила уже совсем.

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

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

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

Подскажите — что можно сделать?
Предполагаю, что решение может быть очень простым, но моих знаний не хватает. Автор — A138
Дата добавления — 11.12.2015 в 15:46

Преобразование чисел-как-текст в нормальные числа

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

Читайте также:  Динамический график small-multiples в Excel скачать шаблон

Число как текст в Excel

Причем иногда такой индикатор не появляется (что гораздо хуже).

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

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

Особенно забавно, что естественное желание просто изменить формат ячейки на числовой — не помогает. Т.е. вы, буквально, выделяете ячейки, щелкаете по ним правой кнопкой мыши, выбираете Формат ячеек (Format Cells) , меняете формат на Числовой (Number) , жмете ОК — и ничего не происходит! Совсем!

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

Способ 1. Зеленый уголок-индикатор

Если на ячейке с числом с текстовом формате вы видите зеленый уголок-индикатор, то считайте, что вам повезло. Можно просто выделить все ячейки с данными и нажать на всплывающий желтый значок с восклицательным знаком, а затем выбрать команду Преобразовать в число (Convert to number) :

Преобразование в число

Все числа в выделенном диапазоне будут преобразованы в полноценные.

Если зеленых уголков нет совсем, то проверьте — не выключены ли они в настройках вашего Excel (Файл — Параметры — Формулы — Числа, отформатированные как текст или с предшествующим апострофом).

Способ 2. Повторный ввод

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

Само-собой, что если ячеек много, то такой способ, конечно, не подойдет.

Способ 3. Формула

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

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

Двойной минус, в данном случае, означает, на самом деле, умножение на -1 два раза. Минус на минус даст плюс и значение в ячейке это не изменит, но сам факт выполнения математической операции переключает формат данных на нужный нам числовой.

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

Способ 4. Специальная вставка

Этот способ использовали еще в старых версиях Excel, когда современные эффективные менеджеры под стол ходили зеленого уголка-индикатора еще не было в принципе (он появился только с 2003 года). Алгоритм такой:

  • в любую пустую ячейку введите 1
  • скопируйте ее
  • выделите ячейки с числами в текстовом формате и поменяйте у них формат на числовой (ничего не произойдет)
  • щелкните по ячейкам с псевдочислами правой кнопкой мыши и выберите команду Специальная вставка (Paste Special) или используйте сочетание клавиш Ctrl+Alt+V
  • в открывшемся окне выберите вариант Значения (Values) и Умножить (Multiply)

Преобразование текста в число специальной вставкой

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

Способ 5. Текст по столбцам

Если псеводчисла, которые надо преобразовать, вдобавок еще и записаны с неправильными разделителями целой и дробной части или тысяч, то можно использовать другой подход. Выделите исходный диапазон с данными и нажмите кнопку Текст по столбцам (Text to columns) на вкладке Данные (Data) . На самом деле этот инструмент предназначен для деления слипшегося текста по столбцам, но, в данном случае, мы используем его с другой целью.

Пропустите первых два шага нажатием на кнопку Далее (Next) , а на третьем воспользуйтесь кнопкой Дополнительно (Advanced) . Откроется диалоговое окно, где можно задать имеющиеся сейчас в нашем тексте символы-разделители:

Преобразование текста в число через инструмент Текст по столбцам

После нажатия на Готово Excel преобразует наш текст в нормальные числа.

Способ 6. Макрос

Если подобные преобразования вам приходится делать часто, то имеет смысл автоматизировать этот процесс при помощи несложного макроса. Нажмите сочетание клавиш Alt+F11 или откройте вкладку Разработчик (Developer) и нажмите кнопку Visual Basic. В появившемся окне редактора добавьте новый модуль через меню Insert — Module и скопируйте туда следующий код:

Теперь после выделения диапазона всегда можно открыть вкладку Разрабочик — Макросы (Developer — Macros) , выбрать наш макрос в списке, нажать кнопку Выполнить (Run ) — и моментально преобразовать псевдочисла в полноценные.

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

С датами бывает та же история. Некоторые даты тоже могут распознаваться Excel’ем как текст, поэтому не будет работать группировка и сортировка. Решения — те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.

Форматирование числовых значений MS-Excel

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

  • выделить ячейку;
  • с помощью команды «Формат ячейки» из контекстного меню открыть окно
  • «Формат ячеек» (рисунок 2.16);
  • из списка «Числовые форматы:» выбрать формат;
  • в правой части окна выбрать тип формата (если требуется). Например, для формата «Число» можно определить количество символов в дробной части, установить (или убрать) разделитель групп разрядов;
  • в поле «Образец» сравнить ожидаемый вид числа с желаемым;
  • нажать кнопку «ОK».
  • Таблица 2.7 – Формат ячеек MS-Excel

На панели «Форматирование» есть пять кнопок, которые упрощают применение соответствующего форматирования к выделенным ячейкам:

Форматирование символов в ячейках

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

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

Как изменить формат ячейки в Excel быстро и качественно

= Мир MS Excel/Статьи об Excel

  • 1
  • 2
  • 3
Приёмы работы с книгами, листами, диапазонами, ячейками [6]
Приёмы работы с формулами [13]
Настройки Excel [3]
Инструменты Excel [4]
Интеграция Excel с другими приложениями [4]
Форматирование [1]
Выпадающие списки [2]
Примечания [1]
Сводные таблицы [1]
Гиперссылки [1]
Excel и интернет [1]
Excel для Windows и Excel для Mac OS [2]

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

Код Описание Пример формата Содержимое ячейки Результат
Основной общий числовой формат, числа отображаются так, как были введены Основной 1234,56
78
0,9
1234,56
78
0,9
# любая цифра необязательная, лишние цифры после запятой округляются #,# 1234,56
78
0,9
1234,6
78,
,9
любая цифра обязательная. Недостающие цифры заменяются нулём, как в целой части, так и в дробной,
лишние цифры после запятой округляются
000,0 1234,56
78
0,9
1234,6
078,0
000,9
? любая цифра обязательная. Недостающие цифры заменяются пробелом (используется для выравнивания чисел по десятичной запятой), лишние цифры после запятой округляются . 1234,56
78
0,9
-25
-3,4
1234,6
78,
,9
— 25,
— 3,4
% умножает число на 100 и добавляет знак % после него 0,0% 0,9 90,0%
пробел используется как разделитель разрядов, если стоит в середине формата, и уменьшает число в 1000 раз, если стоит в конце формата # ##0,0
# ##0,0 (пробел)
# ##0,0 (два пробела)
123456,7 123 456,7
123,5
0,1
E+ E- используется для экспоненциальной записи числа 0,0E+00
0E+0
123456,7 1,2E+05
1E+5
@ используется для вывода текста
" " (кавычки) выводит текст, заключенный в кавычки
$- + / ( ) : пробел эти символы отображаются в ячейке, даже если записаны в формате без кавычек
\ используется для экранирования, т.е. для отображения следующего за ним символа без изменений
* заполняет ячейку символом, который следует за * *- 1234,56 ————
_ (подчёркивание) делает символ, следующий за ним невидимым, но оставляет свободное место, равное ширине символа. Используется для выравнивания чисел по десятичной запятой, если, например, отрицательное число заключается в скобки # ##0,00_);(# ##0,00);- 1234,5
-1234,5
1 234,50
(1 234,50)

Использование в числовом формате произвольного текста

Формат Содержимое ячейки Результат Примечание
# ##0,00" кг" 456 456,00 кг
# ##0,00 " т" 456 0,46 т пробел после нуля уменьшает число в 1000 раз
0" кг",000" г" 456,123 456 кг,123 г
"ABCD-"# 456 ABCD-456
"Приход"*.0,00;"Расход"*.0,00;- 456
-456
Приход…..456,00
Расход…..456,00
сочетание *. (звёздочка-точка) обеспечивает разделение текста и числа точками, т.е. т екст располагается по левому краю ячейки, число располагается по правому краю, а все пространство между ними заполняется точками
"Избыток";"Недостаток";"Баланс"; 456
-456
0
текст
Избыток
Недостаток
Баланс
так как четвёртый текстовый раздел пустой, а точка с запятой для него поставлена, то текст отображаться в ячейке не будет

Задание в формате цвета шрифта

Читайте также:  Как запустить макрос в Excel разными способами
Формат Содержимое ячейки Результат Примечание
[Синий]# ##0,00;[Красный]-# ##0,00 456
-456
456,00
-456,00
# ##0,00" кг";[Красный]"Ошибка!" 456
-456
456 кг
Ошибка!
[Красный]"Избыток";[Синий]"Недостаток";[Зеленый]"Баланс"; 456
-456
Избыток
Недостаток
Баланс
[Черный]Основной 456
#ЗНАЧ!
456 Если цвет шрифта установить в цвет фона ячейки (белый по белому), то при таком формате скроются значения ошибок
0,0;-0,0;-;[Цвет46] 456
-456
0
текст
456
-456

текст
Выведет текст цветом 46 (оранжевым)

Пользовательские форматы Даты/Времени

В большинстве случаев для форматирования ячеек, содержащих дату/время бывает достаточно встроенных форматов Дата и Время. Эти форматы составляются из символов Д (день), М (месяц), Г (год), ч (часы), м (минуты), с (секунды). Различия заключаются в разном количестве этих символов в записи формата, а также в разделителях. В се буквы, кроме "м" можно писать как строчными, так и прописными буквами

9
09
Вс
воскресенье

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

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

Несколько слов о текстовом формате

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

Пользовательский ЧИСЛОвой формат в EXCEL (через Формат ячеек)

В Excel имеется множество встроенных числовых форматов, но если ни один из них не удовлетворяет пользователя, то можно создать собственный числовой формат. Например, число -5,25 можно отобразить в виде дроби -5 1/4 или как (-)5,25 или 5,25- или, вообще в произвольном формате, например, ++(5)руб.###25коп. Рассмотрены также форматы денежных сумм, процентов и экспоненциального представления.

Для отображения числа можно использовать множество форматов. Согласно российским региональным стандартам ( Кнопка Пуск/ Панель Управления/ Язык и региональные стандарты ) число принято отображать в следующем формате: 123 456 789,00 (разряды разделяются пробелами, дробная часть отделяется запятой). В EXCEL формат отображения числа в ячейке можно придумать самому. Для этого существует соответствующий механизм – пользовательский формат. Каждой ячейке можно установить определенный числовой формат. Например, число 123 456 789,00 имеет формат: # ##0,00;-# ##0,00;0

Пользовательский числовой формат не влияет на вычисления, меняется лишь отображения числа в ячейке. Пользовательский формат можно ввести через диалоговое окно Формат ячеек , вкладка Число , ( все форматы ), нажав CTRL+1 . Сам формат вводите в поле Тип , предварительно все из него удалив.

Рассмотрим для начала упомянутый выше стандартный числовой формат # ##0,00;-# ##0,00;0 В дальнейшем научимся его изменять.

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

  • Символ решетка (#) означает любую цифру.
  • Символ пробела в конструкции # ##0 определяет разряд (пробел показывает, что в разряде 3 цифры). В принципе можно было написать # ###, но нуль нужен для отображения 0, когда целая часть равна нулю и есть только дробная. Без нуля (т.е. # ###) число 0,33 будет отражаться как ,33.
  • Следующие 3 символа ,00 (запятая и 00) определяют, как будет отображаться дробная часть. При вводе 3,333 будут отображаться 3,33; при вводе 3,3 – 3,30. Естественно, на вычисления это не повлияет.

Вторая часть формата – для отображения отрицательных чисел. Т.е. можно настроить разные форматы для отражения положительных и отрицательных чисел. Например, при формате # ##0,00;-###0;0 число 123456,3 будет отображаться как 123 456,30, а число -123456,3 как -123456. Если формата убрать минус, то отрицательные числа будут отображаться БЕЗ МИНУСА.

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

Есть еще и 4 часть – она определяет вывод текста. Т.е. если в ячейку с форматом # ##0,00;-# ##0,00;0;»Вы ввели текст» ввести текстовое значение, то будет отображено Вы ввели текст .

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

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

Продвинутый поиск

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

Параметры в поиске

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

Поиск форматов

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

Выбрать формат

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

Поиск формата – это хорошо, но чаще искать приходится конкретные значения. И тут Excel предоставляет дополнительные возможности для расширения и уточнения параметров поиска.

Первый выпадающий список Искать предлагает ограничить поиск одним листом или расширить его до целой книги.

Искать на листе

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

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

В следующем выпадающем списке находится замечательная возможность поиска по формулам, значениям, а также примечаниям. По умолчанию Excel производит поиск в формулах либо, если их нет, в содержимом ячейки. Например, если искать фамилию Иванов, а фамилия эта есть результат формулы (копируется из соседнего листа), то поиск нечего не даст, т.к. в ячейке нет искомого перечня символов. По той же причине не удастся отыскать число, являющееся результатом работы какой-либо функции. Поэтому бывает смотришь в упор на ячейку, видишь искомое значение, а Excel его почему-то не видит. Это не глюк, это настройка поиска. Измените данный параметр на Значения и поиск будет осуществляться по тому, что отражено в ячейке, независимо от содержимого. Например, если в ячейке содержится результат вычисления 1/6 (как значение, а не формула) и при этом формат отражает только 3 знака после запятой (т.е 0,167), то поиск символов «167» при выборе параметра Формулы эту ячейку не обнаружит (реальное содержимое ячейки — это 0,166666…), а при выборе Значения поиск увенчается успехом (искомые символы совпадают с тем, что отражается в ячейке). И последний пункт в данном списке – Примечания. Поиск осуществляется только в примечаниях. Очень может помочь, т.к. примечания часто скрыты.

В диалоговом окне поиска есть еще две галочки Учитывать регистр и Ячейка целиком. По умолчанию Excel игнорирует регистр, но можно сделать так, чтобы «иванов» и «Иванов» отличались. Галочка Ячейка целиком также может оказаться весьма полезной, если ищется ячейка не с указанным фрагментом, а полностью состоящая из искомых символов. К примеру, как найти ячейки, содержащие только 0? Обычный поиск не подойдет, т.к. будут выдаваться и 10, и 100. Зато, если установить галочку Ячейка целиком, то все пойдет, как по маслу.

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

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

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

format-1-03.png

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

format-1-04.png

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

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

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

Читайте также:  Как восстановить в Excel поврежденный файл или несохраненный

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

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

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

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

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

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

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

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

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

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

Условное форматирование – несколько условий

Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.

  • 1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» — «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
  • 2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».

Заполняем параметры форматирования по первому условию:

Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.

Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».

То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).

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

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

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

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

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

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

Что такое условное форматирование

Форматирование — это довольно широкое понятие. К ячейкам листа Excel мы можем применить либо стилистическое , либо числовое форматирование.

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

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

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

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

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

Как в Excel изменить форматирование содержимого ячеек

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

Предположим, что для содержимого большинства ячеек рабочего листа используется желтый фон и шрифт Calibri размером 14 пунктов с полужирным начертанием. Кроме того, такие же ячейки имеются и на других листах рабочей книги. Изменим форматирование содержимого всех этих ячеек следующим образом: шрифт — Cambria; начертание — полужирное; размер — 16 пунктов; цвет букв — белый; цвет ячеек — черный.

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

  1. Выделите любую ячейку (или несколько ячеек, если нужно выполнить поиск в определенном диапазоне) и выберите команду Главная ► Редактирование ► Найти и выделить ► Заменить (или нажмите Ctrl+H) для отображения окна Найти и заменить.
  2. Убедитесь, что поля Найти и Заменить на диалогового окна Найти и заменить пустые.
  3. Нажмите кнопку Формат рядом с полем Найти, чтобы открыть диалоговое окно Найти формат. Если эта кнопка не видна, нажмите кнопку Параметры.
  4. В диалоговом окне Найти формат вы можете указать параметры форматирования. Но, чтобы ускорить эту процедуру, щелкните на стрелке рядом с кнопкой Формат, выберите команду Выбрать формат из ячейки, а затем щелкните на ячейке с тем форматированием, которое вы хотите заменить.
  5. Теперь нажмите кнопку Формат рядом с полем Заменить на для отображения диалогового окна Заменить формат.
  6. И снова: либо выберите команду Выбрать формат из ячейки и укажите ячейку с содержимым, на форматирование которого нужно заменить, либо используйте вкладки диалогового окна Заменить формат, чтобы указать желаемое форматирование. Перейдите на вкладку Шрифт и выберите Cambria, укажите размер 16, полужирное начертание и задайте белый цвет текста. На вкладке Заливка выберите черный цвет в качестве цвета фона ячейки. Если вы установили те же настройки, то ваше диалоговое окно Найти и заменить должно выглядеть как на рис. 22.1.
  7. Нажмите кнопку Заменить все.

Рис. 22.1. Вкладка Заменить диалогового окна Найти и заменить

Рис. 22.1. Вкладка Заменить диалогового окна Найти и заменить

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

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

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

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

Ссылка на основную публикацию