Какие типы данных могут использоваться в excel. VBA Excel

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

- Текстовый . Это обычный текстовый формат, который носит информационный характер. Число также может быть указано в текстовом формате;

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

- Денежный . Этот формат используют для ввода различных денежных величин. По умолчанию число округляется до двух знаков после запятой (до копеек), но это количество знаков можно изменить. Кроме того, в конце значения указывается валюта. По умолчанию добавляется обозначение валюты, используемое в Windows, то есть в русской версии к числу добавляется р. Можно изменить это обозначение на любое другое (английский фунт, американский доллар и т. д.). Конечный формат данных выглядит как, например, 1 200,00 р., причем р. программа вводит автоматически. Необходимо указать только число;

- Финансовый . Такой же формат, что и денежный, но данные в столбцах выравниваются по разделителю целой и дробной части;

- Дата . В ячейках можно указывать даты в различных форматах, например 12.01.2013 или 12 января 2013 г. Причем достаточно ввести дату в произвольном формате, программа сама преобразует этот текст в выбранный формат. Данный формат может также участвовать в формулах;

- Процентный . Данный формат обладает всеми свойствами формата «Числовой ». При этом число умножается на 100, а в конец добавляется символ %;

- Дробный . Данные в ячейку вводят обычным способом, но по окончании ввода (или вычисления) в ячейке отображается обычная (не десятичная) дробь;

- Экспоненциальный . Введенное число указывается в ячейке в экспоненциальном виде. Иными словами, если Вы введете число 25000, в ячейке отобразится значение 2,50E+04, что означает 2,5 умноженная на 10 в степени 4. Такой формат данных широко используется в научных вычислениях при оперировании большими или очень малыми числами;

- Дополнительный . Этот формат включает в себя несколько видов данных: Почтовый индекс , Номер телефона и Табельный номер . При вводе номера телефона последние цифры отображаются как номер телефона. Если количество введенных цифр превышает 7, то первые цифры отображаются в скобках, как код города. Например, если ввести в ячейку число 5555555555, оно отобразится как (555) 555–5555.

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

По умолчанию ячейке присвоен общий формат, то есть программа в большинстве случаев распознает тип введенных в ячейку данных. Например, если Вы вводите текст, программа распознает эти данные как текст, а если число – как число. Если введенное число очень большое (или, наоборот, ничтожно малое с большим количеством знаков после запятой), MS Excel автоматически отображает это число в экспоненциальном виде. В ряде случаев возникает необходимость вручную указать формат ячейки (диапазона), чтобы, например, дата отображалась не как 12.09.2012, а как 12 сентября 2012 года. Для этого используется диалоговое окно «Формат ячеек», которое можно вызвать через контекстное меню и через пункт меню приложения.

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

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

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

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

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

При абсолютной адресации адреса ссылок при копировании формулы не изменяются, так что ячейка, на которую указывает ссылка, рассматривается как постоянная. Для указания абсолютной адресации вводится символ $. Различают два типа абсолютной ссылки: полная и частичная . При полной абсолютной адресации знак $ ставится как перед именем столбца, так и перед именем строки (применяется клавиша F 4) . При частичной абсолютной ссылки указывается знак $ или перед именем строки (чтобы не менялась строка при перемещении или копировании), или перед именем столбца (чтобы не менялся столбец при копировании или перемещении).

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

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

− статистические – выполняют операции по вычислению параметров случайных величин или их распределений;

− текстовые – выполняют операции над текстовыми строками или последовательностью символов;

− логические – используются для построения логических выражений;

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

− функции даты и времени и т.д.

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

При создании формулы или функции могут произойти следующие стандартные ошибки:

##### – числовые данные не помещаются в ячейку по ширине, или при вычислении по формуле из даты вычитается более поздняя;

#ДЕЛ/0 – в знаменатели формулы находится нулевое значение, или ссылка на пустую ячейку;

#ЧИСЛО! – при вычислении получилось число, слишком большое или слишком маленькое для программы;

#ИМЯ? – опечатка в имени функции или имени ячейки (диапазона);

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

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

Мы же выделяем 4 основные группы:

• Текстовый;
• Числовой;
• Дата и время;
• Общий.

Притом группа может включать в себя несколько типов. Чтобы увидеть их в книге Excel, пройдите по вкладке «Главная», раздел «Число» и нажмите выпадающий список. Так можно быстро задать тип данных для ячейки или диапазона. Чтобы задать его более конкретно, т.е. различные разделители и варианты отображения, нажмите на стрелку в нижнем правом углу того же раздела либо, кликнув правой кнопкой мыши по ячейке, выберите «Формат ячеек…».

Дадим краткое описание группам (для более детального описания пройдите по ссылкам):

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

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

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

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

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

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

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

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

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

Функция T в Excel выполняет проверку типа принимаемых данных и возвращает текстовую строку, если в ячейке, ссылка на которую была передана в качестве параметра, содержатся текстовые данные либо текст был передан в функцию явно (например, Т(«любое текстовое значение»)).

Примеры использования функции Т в Excel

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

Исходные таблицы:

Для решения используем следующую формулу:

Данная запись имеет следующий смысл:

1. Функция ЕСЛИ выполняет проверку значения, которое вернет функция Т;
2. Если ячейка G2 не содержит текстовые данные, будет возвращено пустое значение «» и сработает функция ТЕКСТ, которая преобразует числовой тип данных в текстовый и запишет полученное значение в ячейку B2;
3. Если в ячейке G2 содержатся текстовые данные, они будут перенесены в ячейку B2 без изменений.

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

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

То есть, преобразование в текстовые данные прошло успешно.



Как определить формат ячейки в Excel?

Пример 2. В таблице Excel находится защищенный лист с таблицей, в которой содержатся данные об адресе клиента. Предположительно, номер дома и номер квартиры являются числовыми данными, полученными в результате перебора строки с использованием функций ПРАВСИМВ, ЛЕВСИМВ, НАЙТИ и ЗНАЧЕН. Последняя преобразует данные в формат Число (если это возможно). Определить формат данных поможет функция Т, а преобразовать в текстовый формат – ТЕКСТ.

Таблица на защищенном листе (формулы не видны, так как стоит защита):

Данные в виде текста необходимо ввести в следующую таблицу:

В ячейке B2 введем следующую формулу:

Описание функций формулы:

1. Т(защищенный!B3)="" – условие, проверяемое функцией ЕСЛИ. Результат выполнения функции Т проверяется на пустое значение «».
2. ТЕКСТ(защищенный!B3;"#") – функция, преобразующая данные из ячейки защищенный!B3 в текстовый тип данных.
3. защищенный!B3 – получение данных из ячейки, если условие Т(защищенный!B3)="" - ЛОЖЬ.

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

Как определить тип данных в Excel?

В Excel можно определить тип данных, содержащихся в определенной ячейке, используя функцию ТИП, которая возвращает числовое значение, соответствующее одному из поддерживаемых типов (например, 1 – Число, 2 – Текст, 4 – Логическое значение и т. д.). Определим типы данных в некоторой таблице, и если они не являются текстовыми, преобразуем их с помощью функции ТЕКСТ.

Исходная таблица:

Введем в ячейке C3 следующую формулу:

Описание аргументов:

1. ТИП(B3)<>2 – условие проверки, 2 – тип текстовых данных;
2. ТЕКСТ(B3;"") – функция, возвращающая текстовое значение из ячейки B3, если результат проверки условия – ИСТИНА;
3. B3 – неизмененное значение ячейки, если результат условия проверки – ЛОЖЬ.

Аналогично заполним последующие ячейки столбца C. В результате получим:

Чтобы убедиться, что каждая ячейка содержит текст, используем функцию Т:

Функция Т в Excel и особенности ее синтаксической записи

Функция Т имеет следующий синтаксис:

Т(значение)

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

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

Примечания 2:

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

• Результатом выполнения функции =Т(293) будет являться пустой текст «», поскольку принимаемое на вход значение является данными числового типа;
• Следующая запись =Т(«функция Т работает») вернет строку текста «функция Т работает» в ячейку, в которой данная функция записана;
• Если в ячейку ввести запись типа =Т(текст), то результатом выполнения функции будет код ошибки #ИМЯ?. Поэтому при явной передаче параметра в функцию его значение необходимо помещать в двойные кавычки.
• Результатом выполнения функции =Т(A1) является текст, содержащийся в ячейке A1 или пустое значение «», если в данной ячейке находятся данные другого типа.