Типы данных в Excel

Определение

Тип данных — это определенное свойство содержимого электронной таблицы Excel, которое помогает программе идентифицировать информацию и понимать, как ее обрабатывать и отображать.

Что подразумевается под термином “тип данных»

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Дата и время

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

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

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

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

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

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

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

Читайте также: Промежуточные итоги в excel

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

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

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

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

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

Формулы

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

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

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

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

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

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

Основные типы ссылок на ячейки в Excel

При работе в Excel практически невозможно обойтись без использования ссылок.
На практике рассмотрим использование основных типов ссылок на ячейки в Excel.

Относительная ссылка в Excel

Относительная ссылка — это ссылка вида A1 (т е буква столбца + номер строки).
Основная особенность таких ссылок заключается в том, что при перетаскивании или копировании формулы в другие ячейки ссылка перемещается.
Другими словами, при копировании вниз ссылка A1 станет A2, A3 и так далее, при копировании вправо она станет B1, C1 и так далее:

В этом примере мы копируем ячейку D2 с формулой A2*B2.
Когда мы перемещаем формулу вниз, мы получаем A2 -> A3 -> A4 -> A5, B2 -> B3 -> B4 -> B5.

Смешанная ссылка в Excel

Смешанная ссылка — это ссылка вида $A1 или A$1.
Знак доллара ($) выступает в качестве привязки столбца или строки.
Другими словами, если мы поместим $ перед буквой столбца (например, $B5), ссылка будет меняться не по столбцам, а по строкам (перетаскивание переместит формулу на $B5, $B6, $B7 и скоро). Аналогично, если перед номером строки поставить знак $ (например, B$5), ссылка не изменится в строках, а изменится в столбцах (при перемещении формула переместится на C$5, D$5, Е $5 и так далее).
Проанализируем использование смешанных ссылок при построении стандартной таблицы умножения:

В этом примере любая табличная формула является произведением значений из столбца А и строки 2.
Добавляя в формулу расчета знак $ (например, G$2*$A8), мы последовательно фиксируем столбец и строку.

Абсолютная ссылка в Excel

Абсолютная ссылка — это ссылка вида $A$1.
Его особенность в том, что он не меняется при копировании или перетаскивании формулы в другие ячейки.
В этом случае знак $ ставится как перед буквой столбца, так и перед номером строки, т.е формула полностью фиксируется.
Абсолютная ссылка часто используется, когда вам нужно умножить или разделить диапазон ячеек на одно и то же число.
Например, конвертировать данные в рубли с определенной скоростью или конвертировать данные в тысячи/миллионы/миллиарды:

Как сделать ссылку относительной/абсолютной/смешанной?

Помимо ручной вставки знака $ в формулу ячейки, вы можете использовать инструменты Excel.
Если вы выделите формулу и нажмете клавишу F4 одну за другой, Excel автоматически добавит знак $ в формулу в следующем порядке: B5 -> $B$5 -> $B5 -> B$5
В зависимости от типа ссылки, которую мы хотим добавить, несколько раз нажимаем F4 и получаем желаемый результат.

Типы данных в Excel: акции и география

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

Примечание. 28 марта 2019 г типы данных были выпущены для всех подписчиков Windows Office 365. Если вы являетесь подписчиком Office 365, убедитесь, что у вас установлена ​​последняя версия Office.

Примечание. Типы данных «Общие ресурсы» и «География» доступны только клиентам с несколькими арендаторами по всему миру (стандартные учетные записи Office 365).

Новые типы данных в Excel 2016

Ввод нового типа данных

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

Сначала выделите его и сделайте «умным», нажав Ctrl+T или воспользовавшись кнопкой Форматировать как таблицу на вкладке Главная (Главная — Форматировать как таблицу). Затем выберите все названия городов и выберите тип данных Geography на вкладке Data):

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

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

Некоторые названия могут иметь двойное значение, например Новгород может быть и Нижним Новгородом, и Великим Новгородом. Если Excel не распознает его должным образом, можно щелкнуть правой кнопкой мыши по ячейке и выбрать команду Тип данных — Изменить (Data type — Edit), а затем выбрать подходящий вариант из предложенных на панели справа:

Добавление столбцов с подробностями

Вы можете легко добавить дополнительные столбцы сведений для каждого объекта в созданной таблице. Например, для городов можно добавить столбцы с названием региона или региона (админотдела), области (района), страны (страны/региона), даты основания (даты основания), населения (населения), широты и долготы. (широта, долгота) и даже имя мэра (лидера).

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

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

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

Если попробовать все это на колонке не с городами, а со странами, то можно увидеть еще больше полей:

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

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

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

Фильтрация по неявным параметрам

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

Отображение на карте

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

Например, для российских областей, краев и республик это выглядит очень хорошо:

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

Тип данных Stocks

Второй тип данных, Акции, работает точно так же, но предназначен для распознавания фондовых индексов:

. и названия компаний и их сокращенные наименования (тикеры) на бирже:

Обратите внимание, что рыночная стоимость (рыночная стоимость) дана почему-то в разных денежных единицах, ну не знает это дело Греф и Миллер, очевидно 🙂

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

Будущее новых типов данных

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

Менеджеры по персоналу хотели бы иметь что-то подобное, что вы думаете?

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

Уверен, нас ждет много интересного 🙂