Как отформатировать ячейки в excel по содержимому

Как отформатировать ячейки в excel по содержимому

На этом шаге мы рассмотрим стилистическое форматирование рабочих листов.

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

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

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

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

Диалоговое окно Формат ячеек содержит шесть вкладок:

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

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

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

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

Горизонтальное выравнивание . Большинство опций горизонтального выравнивания можно применить с помощью кнопок панели инструментов Форматирование . Кроме того, можно использовать вкладку Выравнивание диалогового окна Формат ячеек .

Существуют следующие опции горизонтального выравнивания:

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

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

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

Во вкладке Выравнивание диалогового окна Формат ячеек находятся ее три опции:

• Переносить по словам . Эта опция служит для отображения текста в ячейке в несколько строк.
• Автоподбор ширины . В Excel имеется опция сжатия теста до нужного размера. Размер текста сокращается так, чтобы он мог поместиться в одной ячейке, не переходя на соседнюю. Если к ячейке было применено форматирование с переносом по словам, то к ней уже нельзя применить автоподбор.
• Объединение ячеек . В Excel имеется возможность объединения (слияния) ячеек, но при этом невозможно объединить содержимое этих ячеек. При слиянии ячеек объединенная ячейка занимает место объединяемых ячеек. Можно объединять любое количество ячеек, занимающих произвольное количество строк и столбцов. Однако все сливаемые ячейки должны быть пустыми, за исключением верхней левой ячейки. Если какая-либо другая ячейка заполнена, то Excel выдаст предупреждение о том, что объединение приведет к потере всех значений, кроме левого верхнего.

В Excel текст можно располагать как горизонтально, так и под любым углом. Для изменения ориентации выделите ячейку или диапазон ячеек, откройте диалоговое окно Формат ячеек , и на вкладке Выравнивание в группе Ориентация задайте угол в диапазоне от -90 до +90 градусов. Или можно повернуть Надпись на нужный угол, удерживая нажатой левую клавишу мыши на красном ромбике. Для того, чтобы выбранный фрагмент текста располагался по вертикали, необходимо шелкнуть по слову Текст в группе Ориентация диалогового окна Формат ячеек на вкладке Выравнивание .

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

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

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

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

1. Выберите ячейки, к которым нужно применить условное форматирование. Щелкните первую ячейку в диапазоне и перетащите ее в последнюю ячейку.
2. Щелкните ГЛАВНАЯ >условное форматирование >выделение правил ячеек >текст, который содержит. В поле Текст, содержащий слева, введите текст, который нужно выделить.
3. Выберите цветовый формат текста и нажмите кнопку ОК.

Формат на основе содержимого ячейки

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

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

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

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

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

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

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

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

23345,10 -> 23 345,1

23345,15 -> 23 345,15

23345,146 -> 23 345,15

23345,00 -> 23 345

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Расположение и назначение команд, как и в других окнах Excel, интуитивно понятно. Достаточно прочитать названия. Тут выравнивание по вертикали, тут – горизонтали. Можно объединить несколько ячеек в одну или длинное название переносить по словам. Обращу лишь внимание на пару интересных моментов.

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

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

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

Аналогичным образом можно отлепить число от правого края.

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

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

Получится тот же эффект, только ячейки не будут объединены.

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

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

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

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

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

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

Самые часто используемые команды есть на ленте.

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

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

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

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

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