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

Форматы ячеек в Excel: это что?

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

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

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

Если в формате установлено числовое значение, здесь тоже имеются свои хитрости. Например, знаки после запятой могут и не отображаться. Стандартные значения таблиц Excel таковы, что показывается только целое число. Мантисса отображается только в случае ввода данных после запятой. При этом она округляется. Таким образом, введя, например, значение числа «пи» (3,14), пользователь при отображении одного знака после точки и установленном округлении увидит значение 3,1. Чтобы отобразить все знаки, придется установить в параметрах разделителя их число.

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

Форматы ячеек в Excel: чем пользоваться?

Таблицы, создаваемые в Excel или в любых других подобных программах, не то что поддерживаются большинством программ такого направления, но и могут быть преобразованы в базы данных или даже в программные коды. В некотором смысле это относится к скриптам Visual Basic. Их платформа является встроенной в основной пакет Microsoft Office. Рассматривая вопрос о том, какие вообще бывают форматы ячеек в Excel, можно выделить несколько основных типов вводимых данных:

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

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

Как изменить параметры ячейки?

Изменение формата ячейки производится путем установки на ячейке прямоугольника. Он свидетельствует о ее активном состоянии, с вызовом контекстного меню через правый клик, где выбирается соответствующая строка. При этом для каждого типа формата можно задавать дополнительные условия. Например, отображение определенного количества знаков после запятой, наличие разделителя, обозначение валюты и т. д. В дополнительных форматах можно использовать и некоторые интересные вещи: введение почтового индекса, номера телефона или даже табельного номера, применяемого, к примеру, в «1С Бухгалтерии».

Кстати, форматы ячеек в Excel «1С Бухгалтерия» воспринимает как свои собственные, но в текстовом варианте. При этом даже не требуется производить экспортирование файла. Собственно, и сами файлы могут быть запросто восприняты другими программами, работающими с базами данных (тот же Access, построенный по образу и подобию Excel). Кроме того, если в созданном файле предусмотрено наличие связей или исполняемых скриптов VB, любая другая программа все это может открыть и сохранить в первозданном виде.

Дополнительные настройки

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

Связь с языками программирования

Кроме выше упомянутого языка Visual Basic, можно использовать и некоторые другие средства. Например, форматы ячеек в Excel Delphi-среда может изменять совершенно просто. Правда, иногда у людей, пишущих программы на основе этого языка, могут возникать проблемы. Ведь многие для изменения того же числового формата применяют строку «exRange.NumberFormat:= "0.0";». А такой вариант срабатывает только при условии, что в ячейках дробная часть числа равна нулю. При наличии других чисел формат остается текстовым. Впрочем, речь сейчас не о том. В данной ситуации нужно просто поменять команду или использовать правильный синтаксис.

Вместо итога

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

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

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

Числовые данные в таблицах Excel могут форматироваться разными способами в зависимости от задач, решаемых пользователем. По умолчанию при вводе числа в рабочий документ к нему применяется общий формат: выравнивание данных по правому краю ячейки, тип и размер шрифта определяется общими настройками системы. Проследим, как способ отображения данных меняется при изменении формата ячейки. На рис. 1.1 показан исходный документ, в ячейку A1 которого введено число 12,34.

Если при выделенной ячейке с числом открыть диалоговое окно Формат ячеек на вкладке Число (открыть окно можно, щелкнув правой кнопкой мыши на ячейке и выбрав команду Формат ячеек или воспользовавшись меткой в правом верхнем углу группы Число вкладки Главная ленты). Диалоговое окно показано на рис. 1.2.

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

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

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

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

Диапазон ячеек A3:B8 содержит числовые данные, причем в ячейках A3:A8 и B3:B8 одни и те же данные, только к ним применен разный формат. К ячейкам A3:A8 применялся числовой формат с описанными выше настройками, а данные в ячейках B3:B8 имеют общий формат. Хочется обратить внимание, что если у числа после десятичной запятой знаков больше, чем указано в настройках формата, то отображаются они не все. Отображаемое значение округляется. Однако это именно отображаемое значение, т. е. то, которое видит пользователь. Реальное значение ячейки не меняется (его можно увидеть в строке формул при выделении соответствующей ячейки), и именно реальное значение используется в вычислениях. Разницу между отображаемым и реальным значениями можно увидеть, в частности, в ячейках A4 и и (см. рис. 1.5).

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

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

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

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

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

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

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

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 .

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

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

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

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

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

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

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

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

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

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

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

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

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

Excel для Office 365 Excel для Office 365 для Mac Excel Online Excel 2019 Excel 2016 Excel 2019 для Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 для Mac Excel для Mac 2011 Excel для iPad Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Excel Starter 2010 Меньше

В этой статье описаны синтаксис формулы и использование функции ЯЧЕЙКа в Microsoft Excel. Ссылки на дополнительные сведения о форматировании данных в ячейках и применении стилей ячеек в разделе " связанные темы ".

Описание

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

= ЕСЛИ( ЯЧЕЙКА("тип", A1) = "v"; A1 * 2; 0)

Эта формула вычисляет произведение A1*2, только если в ячейке A1 содержится числовое значение, и возвращает значение 0, если в ячейке A1 содержится текст или она пустая.

Синтаксис

Аргументы функции ЯЧЕЙКА описаны ниже.

Тип_сведений - обязательный аргумент. Текстовое значение, задающее тип сведений о ячейке при возвращении. В приведенном ниже списке указаны возможные значения аргумента "тип_сведений" и соответствующие результаты.

Тип_сведений

Возвращаемое значение

"столбец"
	1, если форматированием ячейки предусмотрено изменение цвета для отрицательных значений; во всех остальных случаях - 0 (ноль). Примечание:
"содержимое"
"имяфайла"	Имя файла (включая полный путь), содержащего ссылку, в виде текстовой строки. Если лист, содержащий ссылку, еще не был сохранен, возвращается пустая строка (""). Примечание: Это значение не поддерживается в Excel Online, Excel Mobile и Excel Starter.
	Текстовое значение, соответствующее числовому формату ячейки. Значения для различных форматов показаны ниже в таблице. Если ячейка изменяет цвет при выводе отрицательных значений, в конце текстового значения добавляется "-". Если положительные или все числа отображаются в круглых скобках, в конце текстового значения добавляется "()". Примечание: Это значение не поддерживается в Excel Online, Excel Mobile и Excel Starter.
	1, если форматированием ячейки предусмотрено отображение положительных или всех чисел в круглых скобках; во всех остальных случаях - 0. Примечание: Это значение не поддерживается в Excel Online, Excel Mobile и Excel Starter.
"префикс"	Текстовое значение, соответствующее префиксу метки ячейки. Одиночная кавычка (") соответствует тексту, выровненному влево, двойная кавычка (") - тексту, выровненному вправо, знак крышки (^) - тексту, выровненному по центру, обратная косая черта (\) - тексту, распределенному по всей ширине ячейки, а пустой текст ("") - любому другому содержимому ячейки. Примечание: Это значение не поддерживается в Excel Online, Excel Mobile и Excel Starter.
	0, если ячейка разблокирована, и 1, если ячейка заблокирована. Примечание: Это значение не поддерживается в Excel Online, Excel Mobile и Excel Starter.
	Текстовое значение, соответствующее типу данных в ячейке. Значение "b" соответствует пустой ячейке, "l" - текстовой константе в ячейке, "v" - любому другому содержимому.
	Ширина столбца ячейки, округленная до целого числа. Единица измерения равна ширине одного знака для шрифта стандартного размера. Примечание: Это значение не поддерживается в Excel Online, Excel Mobile и Excel Starter.

Ссылка - необязательный аргумент. Ячейка, сведения о которой требуется получить. Если этот аргумент опущен, сведения, указанные в аргументе "тип_сведений", возвращаются для последней измененной ячейки. Если аргумент ссылки указывает на диапазон ячеек, функция ЯЧЕЙКА возвращает сведения только для левой верхней ячейки диапазона.

Коды форматов функции ЯЧЕЙКА

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

Формат Microsoft Excel

Значение, возвращаемое функцией ЯЧЕЙКА

$# ##0_);($# ##0)
$# ##0_);[Красный]($# ##0)
$# ##0,00_);($# ##0,00)
$# ##0,00_);[Красный]($# ##0,00)
# ?/? или # ??/??
д.М.гг или дд.ММ.гг Ч:мм или дд.ММ.гг
Д МММ ГГ или ДД МММ ГГ
д.м, или дд.ммм, или Д МММ
ммм.гг, ммм.гггг, МММ ГГ или МММ ГГГГ
ч:мм:сс AM/PM

Примечание: Если аргумент "тип_сведений" функции ЯЧЕЙКА имеет значение "формат", а формат ячейки был изменен, для обновления значения функции ЯЧЕЙКА необходимо пересчитать лист.

Пример

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