Мы с Вами уже умеем делать простейшие расчёты, но всё еще не изучили многих деталей, связанных с данными на листе. Какими они бывают, какие их особенности? Ответы на эти вопросы – дальше в тексте.
Данные – это основной «материал» для работы в Эксель. Они несут некую информацию о чём-либо и являются объектом анализа. Разбираемся, какие типы данных можно вносить в ячейки рабочего листа:
Числовые данные – самый распространенный тип информации, который могут хранить ячейки. С ними можно выполнять математические операции, на их основании – строить диаграммы. Стоит ли говорить, что числа (измерения) несут наиболее полезную информацию для большинства пользователей программы?
Вы можете записать числовые данные в виде целых и дробных чисел, процентов, и даже даты, времени. Дата и время в Excel – это тоже числа, обозначающие порядковый номер, начиная с 01.01.1900 г. Например, 2 января 1900 года имеет номер 2, а 1 января 2016 года – 42 370 (т.е. 42 370-й день, начиная с 1.1.1900). Эта система счисления справедлива для ОС Windows. Эксель для MacOS считает даты иначе – от 01.01.1904, об этом нужно помнить, если вы используете один и тот же документ на разных операционных системах.
Еще одна тонкость – программа использует до 15 значимых разрядов в числе, остальные заменяет нулями. Например, число 1 234 567 891 012 345 в Excel будет записано так: 1 234 567 891 012 340, т.е. все разряды после 15-го будут заменены нулями. Хотя, я в работе ни разу не сталкивался с такими огромными числами, но 16-значный номер банковской карты нужно записывать только в текстовом формате, чтобы не потерять последнюю цифру.
Минимальное число, с которым может работать программа – (-1Е-307), максимальное – (+9,9Е+307).
Текстовые данные. Многие считают, что Эксель хорошо работает только с числами, но это не так. Программа имеет ряд мощных инструментов для работы с текстом. Вы можете использовать этот вид данных, как заголовки, комментарии, пояснения, т.е. придавать своим таблицам наглядности и осмысленности. Над текстовыми данными нельзя совершать математические операции, но существует ряд функций для работы с текстом.
Формулы мы уже предварительно изучили. Напомню, что все формулы начинаются со знака «равно» и могут содержать математические знаки, например, «+», «-», «*», «/», а, так же, встроенные функции Excel.
Если в выделенной ячейке находится формула, в самой клетке отображается результат вычисления, а формулу можно увидеть в строке формул.
Отличительная черта формул – их интерактивность. Если в расчетах используются ссылки на другие ячейки, при изменении содержимого этих ячеек – все формулы немедленно пересчитываются.
Как Вы уже поняли, данные одного и того же типа можно представить в различных форматах. Например, число можно указать в десятичном виде, в формате даты или времени, процентов, денежных единиц и т.п. Как это делается, я детально рассказал в статье о форматах данных, прочтите, там собрана действительно важная информация!
Пожалуй, на этом сейчас остановимся, в следующей части рассмотрим ввод данных, их корректировку, замену и удаление.
Пожалуйста, задавайте ваши вопросы в комментариях!
Типы данных в моделях данных
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
В модели данных с каждым столбцом связан тип данных, указывающий тип данных, которые может содержать этот столбец: целые числа, десятичные числа, текст, денежные данные, значения даты и времени и т. д. Тип данных также определяет, какие типы операций можно выполнять над столбцом и какой объем памяти необходим для хранения значений в столбце.
Если вы используете надстройку Power Pivot, вы можете изменить тип данных столбца. Это может потребоваться, если столбец даты был импортирован в виде строки, но требуется что-то другое. Дополнительные сведения см. в статье Настройка типа данных столбца в Power Pivot.
В этой статье
Сводка по типам данных
В следующей таблице перечислены типы данных, поддерживаемые в модели данных. При импорте данных или использовании значения в формуле, даже если исходный источник данных содержит другой тип данных, данные преобразуются в один из следующих типов. Также эти типы данных используются в значениях, являющихся результатами формул.
Тип данных в Excel
Тип данных в DAX
64-разрядное (8-байтовое) целочисленное значение 1, 2
Числа без десятичных разрядов. Целые числа могут быть положительными или отрицательными, но не могут содержать дробную часть в диапазоне -9,223,372,036,854,775,808 (-2^63) и 9,223,372,036,854,775,807 (2^63-1).
64-разрядное (8-байтовое) вещественное число 1, 2
Вещественные числа — это числа, которые могут иметь знаки после запятой. Вещественные числа включают широкий диапазон значений.
При этом количество значащих цифр ограничено 15 знаками после запятой.
Значение «True» или «False»
Строка символьных данных в Юникоде. Могут быть строками, числами или датами, представленными в текстовом формате.
Максимальная длина строки составляет 268 435 456 символов Юникода (256 мегасимволов) или 536 870 912 байт.
Значения даты и времени в принятом представлении даты-времени.
Допустимый диапазон дат включает значения после 1 января 1900 г.
Тип данных «Валюта» включает значения в диапазоне от -922,337,203,685,477.5808 до 922,337,203,685,477.5807 с четырьмя десятичными знаками заданной точности.
Тип пустых значений в DAX представляет и заменяет пустые значения NULL в SQL. Можно создать пустое значение с помощью функции BLANK и проверить наличие пустых значений с помощью логической функции ISBLANK.
1 Формулы DAX не поддерживают типы данных, которые меньше перечисленных в этой таблице.
2 При попытке импорта данных с очень большими числовыми значениями может возникнуть следующая ошибка:
Ошибка памяти базы данных: Столбец « » таблицы «
» содержит значение «1.7976931348623157e+308», которое не поддерживается. Операция была отменена.
Эта ошибка возникает из-за того, что Power Pivot использует данное значение для представления значений NULL. Значения в следующем списке являются синонимами значения NULL:
Удалите значение из данных и повторите импорт.
Тип данных table
DAX использует табличный тип данных во многих функциях, таких как агрегаты и вычисления логики операций со временем. Для некоторых функций требуется ссылка на таблицу; другие функции возвращают таблицу, которую можно использовать в качестве входных данных для других функций. В некоторых функциях, требующих ввода таблицы в качестве входных данных, можно указать выражение, результатом которого является таблица; для некоторых функций требуется ссылка на базовую таблицу. Сведения о требованиях к определенным функциям см. в разделе Справочник по функциям DAX.
Явное и неявное преобразования типов данных в формулах DAX
Каждая функция DAX обладает особыми требованиями к типу входных и выходных данных. Например, для некоторых функций требуются целые числа в качестве одних аргументов и даты в качестве других. Для других функций требуются текст или таблицы.
Если данные в столбце, указанном в качестве аргумента, несовместимы с типом данных, необходимым для функции, то во многих случаях DAX возвращает ошибку. Однако DAX выполняет попытку неявного преобразования данных в требуемый тип данных, если это возможно. Например.
Можно ввести дату как строку, и DAX выполнит синтаксический анализ строки, а затем попытается привести ее к одному из форматов даты и времени Windows.
Можно выполнить сложение TRUE + 1 и получить результат 2, поскольку значение TRUE неявно преобразуется в число 1, и выполняется операция 1 + 1.
Если складываются значения из двух столбцов и одно из значений представлено в виде текста («12»), а другое — в виде числа 12, то DAX выполняет неявное преобразование строки в число, а затем выполнит сложение, чтобы получить числовой результат. Следующее выражение возвращает значение 44: = «22» + 22
При попытке объединить два числа Excel отобразит их в виде строк и затем выполнит их объединение. Следующее выражение возвращает «1234»: = 12 & 34
В следующей таблице приведена сводка по неявным преобразованиям типов данных, выполняемым в формулах. Excel выполняет неявные преобразования по возможности, когда этого требует заданная операция.
Таблица неявных преобразований данных
Тип выполняемого преобразования определяется оператором, который приводит требуемые значения к нужному типу перед выполнением запрошенной операции. В следующих таблицах перечислены операторы и указано преобразование, выполняемое для каждого типа данных в столбце, когда этот тип встречается вместе с типом данных из пересекающейся строки.
Примечание: В эти таблицы не входят текстовые типы данных. Когда число представлено в текстовом формате, в некоторых случаях Power Pivot попытается определить тип числа и представить значение в виде числа.
Новые типы данных в Excel 2016
С летними обновлениями 2018 года Excel 2016 получил революционно новую возможность добавления в ячейки данных нового типа — Акции (Stocks) и География (Geography) . Соответствующие иконки появились на вкладке Данные (Data) в группе Типы данных (Data types) :
Что это такое и с чем это едят? Как это можно использовать в работе? Какая часть этого функционала применима для нашей российской действительности? Давайте разберемся.
Ввод нового типа данных
Для наглядности начнем с геоданных и возьмем «для опытов» вот такую табличку:
Сначала выделим её и превратим в «умную» сочетанием клавиш Ctrl + T или с помощью кнопки Форматировать как таблицу на вкладке Главная (Home — Format as Table) . Потом выделим все названия городов и выберем тип данных Geography на вкладке Данные (Data) :
Слева от названий появится значок карты — признак того, что Excel распознал текст в ячейке как географическое название страны, города или области. Щелчок мышью по этому значку откроет красивое окошко с подробностями по данному объекту:
То, что распознать автоматически не получилось будет отмечено вопросительным знаком, при нажатии на который справа появится панель, где можно уточнить запрос или ввести дополнительные данные:
Некоторые названия могут иметь двойственное значение, например Novgorod может быть как Нижним Новгородом, так и Великим Новгородом. Если Excel распознал его не как нужно, то можно щелкнуть по ячейке правой кнопкой мыши и выбрать команду Тип данных — Изменить (Data Type — Edit) , а затем выбрать правильный вариант из предложенных на панели справа:
Добавление столбцов с подробностями
В созданную таблицу можно легко добавить дополнительные колонки с подробностями по каждому объекту. Например, для городов можно добавить столбцы с названием области или края (admin division), площадью (area), страной (country/region), датой основания (date founded), населением (population), широтой и долготой (latitude, longitude) и даже именем мэра (leader).
Для этого можно либо нажать на всплывающий значок в правом верхнем углу таблицы:
. либо использовать формулу, которая будет ссылаться на соседнюю ячейку и добавить к ней точку, а затем выбрать нужный параметр из выпадающего списка подсказок:
. либо просто создать еще один столбец, назвав его соответствующим именем (Population, Area и т.д.) из выпадающего списка с подсказками:
Если попробовать всё это на столбце не с городами, а со странами, то можно увидеть ещё больше полей:
Здесь и экономические показатели (доход на душу населения, уровень безработицы, налоги), и человеческие (рождаемость, смертность), и географические (площадь лесов, выброс CO2) и много что ещё — всего почти 50 параметров.
Источником всей этой информации служат интернет, поисковая машина Bing и Wikipedia, что бесследно не проходит — многих вещей для России эта штука не знает или выдает в искаженном виде. Например, из мэров выдает только Собянина и Полтавченко, а самым крупным городом России считает . ни за что не угадаете какой! (не Москву).
В то же время для Штатов (по моим наблюдениям) система работает гораздо более надежно, что не удивительно. Также для USA кроме названий населенных пунктов можно использовать ZIP-код (что-то вроде нашего почтового индекса), который вполне однозначно определяет населенные пункты и даже районы.
Фильтрация по неявным параметрам
В качестве приятного побочного эффекта, преобразование ячеек в новые типы данных даёт возможность фильтровать потом такие столбцы по неявным параметрам из подробностей. Так, например, если данные в столбце распознаны как Geography, то можно отфильтровать список городов по странам, даже если столбца с названием страны явно нет:
Отображение на карте
Если использовать в таблице распознанные географические названия не городов, а стран, областей, округов, провинций или штатов, то это дает возможность впоследствии построить по такой таблице наглядную карту, используя новый тип диаграмм Картограмма на вкладке Вставка — Карты (Insert — Maps) :
Например, для российских областей, краев и республик это выглядит весьма приятно:
Само-собой, не обязательно визуализировать только данные из предлагаемого списка подробностей. Вместо населения можно так отображать любые параметры и KPI — продажи, число клиентов и т.д.
Тип данных Stocks
Второй тип данных Stocks работает совершенно аналогично, но заточен под распознавание биржевых индексов:
. и названий компаний и их сокращенных наименований (тикеров) на бирже:
Обратите внимание, что рыночная стоимость (market cap) приводится почему-то в разных денежных единицах, ну и Грефа с Миллером эта штука не знает, очевидно 🙂
Сразу хочу предупредить, что использовать всё это для трейдинга не очень получится, т.к. данные обновляются только раз в день, что, в большинстве случаев, для торговли слишком медленно. Для более частого обновления и получения актуальной информации лучше использовать макросы или запросы к биржам через интернет с помощью Power Query.
Будущее новых типов данных
Несомненно, это только начало, и Microsoft, скорее всего, будет расширять набор подобных новых типов данных. Возможно, со временем у нас с вами даже появится возможность создавать свои типы, заточенные под конкретные рабочие задачи. Представьте себе тип, например, для отображения данных о сотруднике или клиенте, содержащий его личные данные и даже фото:
HR-менеджерам такая штука бы понравилась, как думаете?
Или представьте себе тип данных, хранящий подробности (размеры, вес, цвет, цену) по каждому товару или услуге из прайс-листа. Или тип, содержащий всю статистику игр определенной футбольной команды. Или исторические данные о погоде? Почему нет?
Уверен, впереди нас ждет много интересного 🙂
С какими типами данных работает Excel
“Формат ячеек” и “тип данных” – определения, которые довольно часто путают пользователи Эксель. Несмотря на то, что эти понятия взаимосвязаны, они, все же, разные. В этой статье мы разберемся, что означает “тип данных”, и какие разновидности существуют.
Определение
Тип данных – это определенная характеристика содержимого в таблице Excel, благодаря которой программа идентифицирует информацию и понимает, каким образом ее следует обрабатывать и отображать.
Разновидности типов данных
Выделяются две большие группы типов данных:
константы – неизменные значения;
формулы – значения, которые меняются в зависимости от изменения других.
В группу “константы” входят следующие типы данных:
Давайте подробнее разберем все перечисленные выше типы данных.
Число
Этот тип данных применяется в различных расчетах. Как следует из названия, здесь предполагается работа с числами, и для которых может быть задан один из следующих форматов ячеек:
числовой;
денежный;
финансовый;
процентный;
дробный;
экспоненциальный.
Формат ячейки можно задать двумя способами:
Во вкладке “Главная” в группе инструментов “Число” нажимаем по стрелке рядом с текущим значением и в раскрывшемся списке выбираем нужный вариант.
В окне форматирования (вкладка “Число”), в которое можно попасть через контекстное меню ячейки.
Для каждого из форматов, перечисленных выше (за исключением дробного), можно задать количество знаков после запятой, а для числового – к тому же, включить разделитель групп разрядов.
Чтобы ввести значение в ячейку, достаточно просто выделить ее (с нужным форматом) и набрать с помощью клавиш на клавиатуре нужные символы (либо вставить ранее скопированные данные из буфера обмена). Или можно выделить ячейку, после чего ввести нужные символы в строке формул.
Также можно поступить наоборот – сначала ввести значение в нужной ячейке, а формат поменять после.
Текст
Данный тип данных не предназначен для выполнения расчетов и носит исключительно информационный характер. В качестве текстового значения могут использоваться любые знаки, цифры и т.д.
Ввод текстовой информации происходит таким же образом, как и числовой. Если текст не помещается в рамках выбранной ячейки, он будет перекрывать соседние (если они пустые).
Дата и время
В данном случае тип данных и формат ячеек полностью совпадают. С помощью этих значений можно, соответственно, производить вычисления и работать с датами и временем. Стоит отметить, что программа видит сутки как число 1.
Например, время 08:30 будет восприниматься как число 0,35 (проверить это можно, переведя формат ячеек в “числовой”), в то время, как в ячейке и строке формул оно будет отображаться в привычном для нас виде.
Для формата ячеек “Время” можно выбрать следующие основные типы отображения:
чч:мм:сс;
чч:мм;
чч:мм AM/PM;
чч:мм:сс AM/PM и т.д.
Для формата ячеек “Дата” также предусмотрены различные варианты отображения данных:
ДД.ММ.ГГГГ;
ГГГГ-ММ-ДД;
ДД-ММ;
ДД-ММ-ГГ и т.д.
Также, у пользователя есть возможность выбрать вариант с одновременным отображением даты и времени.
Примечание: Эксель в виде дат может отображать данные только с 01.01.1990. Причем, в то время, как мы видим дату в привычном для нас виде, сама программа хранит ее в своей памяти и обрабатывает в виде числа. Например, дате 01.01.2019 соответствует число 43466. Это легко проверить, сменив формат ячейки с “даты” на “числовой”.
Ошибки
В некоторых случаях пользователь может видеть в Excel ошибки, которые бывают следующих видов:
#ДЕЛ/О! – результат деления на число ;
#Н/Д – введены недопустимые данные;
#ЗНАЧ! – использование неправильного вида аргумента в функции;
#ЧИСЛО! – неверное числовое значение;
#ССЫЛКА! – удалена ячейка, на которую ссылалась формула;
#ИМЯ? – неправильное имя в формуле;
#ПУСТО! – неправильно указан адрес дапазона.
Логические данные
Логические значения “ИСТИНА” и “ЛОЖЬ” используются для того, чтобы при обработке содержимого ячеек вывести определенные результаты или выполнить какие-то действия в зависимости от того, выполнено ли заданное условие (ИСТИНА) или нет (ЛОЖЬ).
Формулы
Этот тип данных принципиально отличается от рассмотренных выше, так как значение результата меняется в зависимости от изменения других значений, на которые оно ссылается. Благодаря формулам выполняются различные расчеты: математические, финансовые и т.д.
Формула будет отображаться в соответствующе строке формул, а результат по ней – в содержащей ее ячейке.
Чтобы Excel “видел” выражение как формулу, обязательно нужно в самом начале поставить знак “равно”.
В формулах используются как конкретные числа, так и ссылки – относительные и абсолютные.
Многие формулы реализованы в виде функций – стандартный набор аргументов и алгоритм их обработки для выдачи финального результата.
Формулу функции можно сразу вводить в нужную ячейку после знака “равно”, если вы знаете, как правильно все оформить. Либо можно воспользоваться предусмотренным для этого Мастером функций, в котором можно найти все внедренные в программу операторы. Для удобства они разделены на категории.
Чтобы попасть в окно Мастера функций, сначала выбираем нужную ячейку, затем щелкаем по кнопке “Вставка функции” рядом со строкой формул. Затем находим нужный оператор и жмем кнопку OK.
Далее корректно заполняем аргументы функции и нажимаем кнопку OK для получения результата в выбранной ячейке.
Заключение
Таким образом, в Эксель выделяются 2 основные группы типов данных: константы и формулы. Причем первая включает в себя подтипы, каждый из которых имеет свое назначение. Умение правильно определять, настраивать и работать со всеми доступными типа данными, в том числе, комбинировать их, позволят максимально эффективно и корректно выполнять поставленные задачи.
