Какие из следующих символов понимаются в microsoft excel как числа
В excel есть возможность присвоения имен ячейкам, таблицам и диапазонам. После присвоения, эти имена можно использовать в формулах, при этом формулы становятся намного понятнее пользователю.
Присвоение имени константе .
Имена можно присваивать и константам, которых даже нет в вашем файле. Например, если в своем прогнозе вы используете рост рынка в размере 5% в следующем году, то этому росту можно присвоить имя. Для этого перейдите на ленте во вкладку Формулы -> Определенные имена -> Задать имя:
Откроется диалоговое окно "Создание имени", в котором необходимо ввести Имя, Область действия этого имени (лист, книга), Примечание (чтобы не забыть, что это за константа), в поле Диапазон необходимо ввести число и нажать ОК:
Теперь эту константу можно использовать во всей книге при расчете формулы, например так:
Обратите внимание, теперь значительно проще понять как был сформирован объем продаж в 2021 году.
Важно : константой может быть не только число, но и текст. Таким образом вы можете упростить ввод часто повторяющихся слов.
Присвоение имени ячейке и формуле.
Порядок присвоения имени ячейке и формуле не сильно отличается от предыдущего примера. Перейдите на ленте в блок Задать имя и в диалоговом окне "Создание имени" на последнем шаге вместо ввода числа введите либо ссылку на ячейку, либо формулу:
Если вы хотите, чтобы ваша именованная формула при расчете использовала значение из ячейки, находящейся левее (например, A4) от той, в которой формула будет находиться (B4), обязательно перед созданием имени активируйте ячейку B4 и не используйте абсолютные ссылки.
Подробнее об абсолютных и относительных ссылках можете прочитать в статье Абсолютные и относительные ссылки в Excel .
Присвоение имени диапазону.
В присвоении имени диапазону нет ничего сложного, просто в окне Создание имени в поле Диапазон пропишите ссылку на него. Интереснее дела обстоят с использованием пересечения диапазонов (это те ячейки, которые являются общими для двух диапазонов). Предположим, есть таблица с объемом продаж трех менеджеров за 4 квартала:
Каждый раз, работая в Excel, старайтесь использовать подходящие числовые форматы для Ваших данных. Числовые форматы сообщают программе, какой тип данных Вы используете, будь то проценты (%), валюты ($), время, дата и так далее. Посмотрите, для примера, на следующую таблицу, в которой использованы различные числовые форматы:
Для чего нужны числовые форматы?
Числовые форматы нужны не только для того, чтобы данные в таблице было легче читать, но, и чтобы таблицей было легче пользоваться. Устанавливая числовой формат, Вы сообщаете таблице, какой тип данных хранится в ячейке. Например, формат даты говорит о том, что Вы помещаете в ячейку определённую календарную дату. Все это позволяет Excel лучше понимать Ваши данные и гарантирует, что они будут оставаться адекватными, а формулы будут вычисляться корректно.
Если Вы не считаете нужным установить определённый числовой формат, Excel по умолчанию применяет формат General (Общий). Общий числовой формат также немного изменяет форматирование Ваших данных.
Установка числовых форматов
Применить числовой формат можно точно также, как и прочие виды форматирования, вроде изменения цвета текста. Для этого нужно выделить ячейки и выбрать нужные опции форматирования. Настраивать числовые форматы позволяет каждое приложение, работающее с электронными таблицами, но этот процесс в разных приложениях может сильно отличаться.
- В Microsoft Excel 2007-2013 откройте вкладку Home (Главная), в разделе Number (Число) кликните выпадающее меню Number Format (Числовые форматы) и выберите нужный формат. Либо под выпадающим меню Вы можете кликнуть одну из иконок быстрого доступа.
- В Excel 2003 и более ранних версиях откройте меню Format (Формат) >Cells (Ячейки).
- В Google Sheets нажмите кнопку More Formats (Другие форматы) в левой части панели инструментов – эта кнопка выглядит, как цифры 123. Слева от этой команды есть иконки Currency (Денежный формат) или Percent (Процентный формат) для быстрого доступа к этим форматам.
В большинстве версий Microsoft Excel доступ к настройкам форматирования можно открыть, если выделить нужные ячейки и нажать Ctrl+1.
В этом примере мы применим числовой формат Currency (Денежный), который добавляет символ валюты ($) и отображает два десятичных знака для любого числового значения.
Если Вы выделите любую ячейку с числовым форматом, то в строке формул сможете увидеть реально содержащееся в ней число. Именно это значение Excel использует при работе с формулами и в других вычислениях.
Используем числовые форматы правильно
Тема числовых форматов не заканчивается выделением ячейки и применением к ней одного из форматов. Электронные таблицы могут устанавливать многие числовые форматы автоматически, в зависимости от введенных данных. Это значит, что Вы должны вводить данные понятным для приложения образом и проверять, чтобы в ячейках установился правильный числовой формат. Для примера, на картинке ниже показано, как правильно вводить даты, проценты и время.
Теперь, когда Вам уже многое известно о работе числовых форматов, давайте рассмотрим некоторые из них в действии.
Процентный формат
Один из наиболее полезных форматов – это процентный формат (%). Он отображает значения как проценты, например, 20% или 55%, что особенно полезно при расчете величин скидок или чаевых. Если вслед за числом ввести знак процента (%), к нему будет автоматически применен процентный формат.
Возможно, Вы еще помните из школьного курса математики, что проценты могут быть записаны в виде десятичных дробей, т.е. 15% – это то же самое, что 0,15, а 7,5% – это 0,075.
Процентный формат будет полезен не однократно. На изображениях ниже Вы видите пример, что получается в каждой таблице при разном форматировании размера скидки (5, 5% или 0,05):
Как видите, в таблице слева вычисления выполнены не верно. Без применения процентного формата, Excel думает, что мы хотим умножить $22,50 на 5, а не на 5%. Таблица справа хоть и справляется с этой задачей, но прочесть проще всего средний вариант.
Формат даты
Работая с датами, Вам пригодится формат даты, чтобы сообщить Excel, что Вы ссылаетесь на определённую календарную дату, например, 15 июля 2014. Кроме этого, формат даты дает возможность работать с мощным инструментарием функций категории Даты и время, которые используют информацию о дате и времени для вычисления результатов.
Электронные таблицы понимают информацию не так, как это делает человек. Например, если Вы введете в ячейку слово «October», Excel не поймет, что Вы имеете в виду дату, и сочтёт это обычным текстом. Вместо этого, при вводе дат, используйте определённый формат, который понятен Excel, например, месяц/день/год или день/месяц/год – в зависимости от того, в какой стране Вы находитесь и какой локализацией программы пользуетесь. В примере ниже мы введём «10/12/2014», чтобы получить дату October 12, 2014. В этом случае Excel автоматически установит для ячейки формат даты.
Теперь, когда для наших дат задан правильный числовой формат, мы многое сможем с ними сделать. Например, мы можем использовать маркер автозаполнения, чтобы продолжить последовательность дат в столбце и получить в каждой ячейке новую дату.
Если формат даты не был применен автоматически, значит приложение Excel не распознало дату, которую Вы ввели. В следующем примере мы ввели «March 15th». Excel не распознал в этой записи дату, поэтому формат ячейки остался General (Общий).
С другой стороны, если мы введем «March 15» (без th), Excel распознает дату. Поскольку год в этой дате не указан, автоматически добавится текущий год, и дата будет содержать всю необходимую информацию. Мы также можем записать дату несколькими другими способами, например, 3/15, 3/15/2014 или March 15 2014, и таблица по-прежнему поймёт, что это дата.
В русской локализации 15/3, 15/3/2014 или 15 март 2014.
Другие варианты форматов даты
В некоторых приложениях присутствует гораздо больший выбор форматов даты. Доступ к ним немного отличается, в зависимости от приложения, которое Вы используете. В Excel 2007-2013 откройте выпадающее меню Number Format (Числовые форматы) и выберите в нем пункт More Number Formats (Другие числовые форматы).
Откроется диалоговое окно Формат ячеек, где Вы сможете выбрать нужный формат даты.
Посмотрев в строку формул, Вы можете убедиться, что пользовательский формат даты не изменяет значение, хранящееся в ячейке. Изменяется только способ отображения даты.
Хитрости при форматировании чисел
- Применяйте числовой формат ко всему столбцу: Если Вы собираетесь использовать столбец для конкретного вида данных, например, для дат или процентов, проще всего выделить сразу весь столбец, кликнув по его букве, и применить нужный числовой формат. Теперь любые данные, введенные в этот столбец, будут уже заранее иметь правильный числовой формат. Обратите внимание, что строка заголовков (если она есть), как правило, не должна быть затронута числовым форматированием.
- Дважды проверьте значения в ячейках после применения к ним числового формата: Если Вы применяете числовой формат к существующим данным, результат может быть неожиданным. Например, применяя процентный формат (%) к ячейке со значением 5, Вы получите 500%, а не 5%. В этом случае придется заново ввести корректные значения в каждую ячейку.
- Если Вы ссылаетесь в формуле на ячейку с числовым форматом, Excel может автоматически применить то же числовое форматирование к итоговой ячейке. Например, если Вы используете в формуле значение с денежным форматом, результат вычисления тоже будет иметь денежный формат.
- Если Вы хотите, чтобы данные в ячейке отображались в точности так, как они были введены, нужно использовать текстовый формат. Этот формат особенно хорош для чисел, над которыми Вы не хотите производить какие-либо вычисления, например, номера телефонов, почтовые индексы или числа, начинающиеся с нуля (02415). Примените текстовый формат к таким ячейкам, прежде чем вводить данные.
Подпишитесь к нам в дзен-канал, для получения свежих новостей it мира:
Как преобразовать текст в число в Эксель?
Вообще, классический метод – использование макросов. Но есть некоторые способы, не предусматривающие программирования.
Зеленый уголок-индикатор
О том, что числовой формат был преобразован в текстовый, говорит появление своеобразного уголка-индикатора. Его появление – это своеобразная удача, поскольку достаточно выделить все, кликнуть по всплывающему значку и нажать на «Преобразовать в число».
Все значения, записанные, как текст, но содержащие цифры, преобразуются в числовой формат. Может случиться и такое, что уголков нет вообще. Тогда нужно убедиться, что они не отключены в настройках программы. Они находятся по пути Файл — Параметры — Формулы — Числа, отформатированные как текст или с предшествующим апострофом.
Повторный ввод
Если есть небольшое количество ячеек, формат которых был неправильно преобразован, то можно ввести данные заново, чем изменить его вручную. Для этого необходимо кликнуть по интересующей ячейке и затем – клавише F2. После этого появляется стандартное поле ввода, где нужно перенабрать значение, а потом нажать Enter.
Также можно два раза нажать по ячейке, чтобы достичь той же самой цели. Конечно, этот метод не покажет себя хорошо, если в документе слишком большое количество ячеек.
Формула преобразования текста в число
Если создать еще одну колонку по соседству с тем, где числа указаны в неправильном формате и прописать одну простую формулу, он автоматически будет преобразован в числовой. Она указана на скриншоте.
2
Здесь двойной минус заменяет операцию умножения на -1 дважды. Зачем это делается? Дело в том, что минус на минус дает положительный результат, поэтому результат не изменится. но поскольку Excel выполнял арифметическую операцию, то значение не может быть другим, кроме как числовым.
Логично, что можно использовать любую подобную операцию, после которых изменения значения нет. Например, добавить и вычесть единицу или разделить на 1. Результат будет аналогичным.
Специальная вставка
Это очень старый метод, который использовался в первых версиях Excel (поскольку зеленый индикатор добавили лишь в 2003-й версии). Наши действия следующие:
- Ввести единицу в любую ячейку, не содержащую никаких значений.
- Скопировать ее.
- Выделить ячейки с записанными в текстовом формате числами и изменить его на числовой. На этом этапе ничего не изменится, поэтому выполняем дальнейший этап.
- Вызвать меню и воспользоваться «Специальной вставкой» или Ctrl + Alt + V.
- Откроется окно, в котором нас интересует радиокнопка «значения», «умножить». 3
По сути эта операция выполняет аналогичные предыдущему методу действия. Единственная разница, что используются не формулы, а буфер обмена.
Инструмент «Текст по столбцам»
Может иногда оказаться более сложная ситуация, когда числа содержали разделитель, который еще может быть в неправильном формате. В таком случае необходимо использовать другой метод. Нужно выделить ячейки, которые нужно модифицировать и нажать на кнопку «Текст по столбцам», которую можно отыскать на вкладке «Данные».
Изначально он создан для других целей, а именно разделить текст, который был неправильно соединен, на несколько колонок. Но в данной ситуации мы его используем для других целей.
Появится мастер, в котором настройка осуществляется в три шага. Нам нужно пропустить два из них. Для этого достаточно дважды кликнуть «Далее». После этого появится наше окно, в котором можно указать разделители. Перед этим нужно кликнуть на кнопку «Дополнительно».
4
Осталось только кликнуть на «Готово», как текстовое значение немедленно превратится в полноценное число.
Макрос «Текст – число»
Если часто нужно совершать такие операции, то рекомендуется этот процесс сделать автоматическим. Для этого существуют специальные исполняемые модули – макросы. Чтобы открыть редактор, существует комбинация Alt+F11. Также к нему можно получить доступ через вкладку «Разработчик». Там вы найдете кнопку «Visual Basic», которую и нужно нажать.
Наша следующая задача – вставить новый модуль. Чтобы это сделать, нужно открыть меню Insert – Module. Далее нужно скопировать этот фрагмент кода и вставить в редактор стандартным способом (Ctrl + C и Ctrl + V).
Sub Convert_Text_to_Numbers()
Selection.NumberFormat = «General»
Selection.Value = Selection.Value
Для выполнения каких-либо действий с диапазоном, его следует предварительно выделить. После этого надо запустить макрос. Делается это через вкладку «Разработчик – Макросы». Появится перечень подпрограмм, которые можно выполнять в документе. Нужно выбрать ту, которая надо нам и нажать на «Выполнить». Далее программа все сделает за вас.
Как определить числа, записанные в Экселе, как текст?
Как уже стало понятно из изложенного выше, Excel отображает возможные проблемы с форматом двумя методами. Первый – зеленый треугольник. Второй – желтый восклицательный знак, появляющийся при выделении ячейки с предыдущим обозначением. Чтобы больше конкретизировать эту проблему, необходимо навести курсор на всплывающий знак.
Если цифровая ячейка отформатирована, как текст, Excel об этом предупреждает с помощью соответствующей надписи..
Мы знаем, что в некоторых случаях треугольник не появляется (или версия Excel слишком старая). Поэтому чтобы определить, какого формата ячейка, можно воспользоваться анализом иных визуальных признаков:
- Числа располагаются стандартно по правой стороне, в то время как значения в текстовом формате – с противоположной стороны.
- Если выделить набор данных с числовыми значениями, внизу будет возможность сразу посчитать сумму, среднее значение и ряд иных параметров.
Как воспользоваться индикатором ошибок?
Такой вопрос пользователи задают часто, но этот метод уже был описан выше. Если появился индикатор, свидетельствующий об ошибке, можно осуществить преобразование формата всего в один клик. Для этого нужно выделить диапазон и нажать на «Преобразовать в число».
Удаление непечатаемых символов
Если копировать информацию из иных источников, то нередко вместе с полезной информацией переносится куча мусора, от которого неплохо было бы избавиться. Часть из этого мусора – непечатаемые символы. Из-за них числовое значение может восприниматься, как набор символов.
Чтобы избавиться от этой проблемы, можно воспользоваться формулой =ЗНАЧЕН(СЖПРОБЕЛЫ(ПЕЧСИМВ(A2)))
Схема работы этой формулы очень проста. С использованием функции ПЕЧСИМВ можно избавиться от непечатаемых символов. В свою очередь, СЖПРОБЕЛЫ избавляет ячейку от невидимых пробелов. А функция ЗНАЧЕН делает текстовые символы числовыми.
Извлечение числа из текста
Эта возможность будет полезной, если из строки нужно извлечь число. Для этого ее нужно применить в комбинации с функциями ПРАВСИМВ, ЛЕВСИМВ, ПСТР .
Так, чтобы достать последние 3 знака из ячейки A2, а результат вернуть, как число, нужно применить такую формулу.
=ЗНАЧЕН(ПРАВСИМВ(A2;3))
Если не использовать функцию ПРАВСИМВ внутри функции ЗНАЧЕН , то результат будет в символьном формате, что мешает выполнять любые вычислительные операции с получившимися цифрами.
Этот способ можно применять в ситуациях, когда человек в курсе, какое количество символов надо вытащить из текста, а также где они находятся.
Числовое форматирование Excel - это одна из самых недооценённых пользователями функций программы.
В этой заметке я расскажу о принципах создания пользовательского числового формата , и по ходу мы разберем примеры, которые продемонстрируют всю мощь этой, на первый взгляд, не особо нужной функции Excel.
Но перед тем, как приступим, хочу обратить ваше внимание на один важный момент.
Суть числового форматирования
Не раз сталкивался с заблуждением пользователей, которые считают, что числовое форматирование как-то влияет на значение, находящееся в ячейке.
ВАЖНО понимать, что это НЕ ТАК!
Числовое форматирование влияет только на отображение значения. Само значение при этом никак не изменяется.
То есть если в ячейке находится цифра с двумя знаками после запятой (1), а мы с помощью числового форматирования уберем дробную часть (2), то визуально кажется, что значение было округлено, но это не так. В ячейке по-прежнему хранится значение с дробной частью (3) и в формулах будет использоваться именно оно.
Числовой формат позволяет изменить внешний вид и сделать данные более аккуратными и легко воспринимаемыми.
Ну а теперь поговорим о синтаксисе числового форматирования.
Синтаксис числового форматирования
Обычно числовое форматирование применяется следующим образом: выделяется ячейка или диапазон ячеек (1), к которому нужно числовой формат применить, и затем из выпадающего списка (2) выбирается готовый шаблон (3).
Как правило, пользователи не интересуются, как именно этот шаблон работает, а просто применяют готовые форматы в своей работе.
Также все готовые шаблоны доступны и в окне Формат ячеек .
Здесь в самом конце списка находится пункт « все форматы », который позволяет посмотреть на код, используемый в готовых шаблонах, а также создавать свои форматы.
Для создания формата нужно понимать синтаксис написания кода, то есть нужно изучить правила, по которым он формируется и узнать, какие вспомогательные символы в коде используются.
Именно об этом я и расскажу в этой заметке.
Итак, код числового формата в общем случае состоит из четырех разделов:
ПОЛОЖИТЕЛЬНЫЕ; ОТРИЦАТЕЛЬНЫЕ; НОЛЬ; ТЕКСТ
Сначала указывается правило форматирования положительных значений, затем отрицательных, далее нулевых и в конце текста. Все четыре раздела отделяются друг от друга точкой с запятой.
При этом необязательно в коде должны присутствовать все четыре раздела.
Если задан только один раздел, то он будет применяться ко всем числовым значениям вне зависимости от их величины и знака. Если указано два раздела, то первый будет использоваться для форматирования положительных числовых значений и нулей, а второй для отрицательных чисел. И только указание всех четырех разделов в коде позволит форматировать текстовые значения.
Код каждого раздела числового формата задается с помощью специальных символов. Давайте их рассмотрим на примерах и пока будем использовать самый простой шаблон - зададим только один раздел для всех числовых значений.
Символ 0 в числовом формате
Символ « ноль » в коде числового формата используется для вывода обязательной цифры. С его помощью можно вывести незначащие нули как в целой, так и в дробной части числа. То есть каждый ноль в коде резервирует место под цифру, которая будет введена или уже находится в ячейке. Если цифры нет, то будет выведен ноль.
Давайте рассмотрим на примере.
В первом столбце находятся значения в общем формате, а в последующих к этим же значениями применяется пользовательский формат ( шаблон в заголовке).
Всем привет! С этого урока я хотел начать рассказывать уже о практической работе в Excel, но еще немного теории )) Чтобы уверенно ориентироваться в процессах работы Excel, необходимо четко распознавать тип данных, которые вы вводите в программу. От этого будет зависеть, в первую очередь, конечный результат, а также количество сохраненных ваших нервов.
Итак . Excel обрабатывает и представляет данные разных типов по-разному. Поэтому значимо понимать отличия между типами данных и то, как они могут применяться в рабочем листе.
Как вы уже знаете, в рабочей книге Excel может храниться произвольное число листов, а каждый рабочий лист состоит из более чем 17 млрд. ячеек. В ячейке могут находиться данные одного из трех типов:
На листе Excel могут находиться также диаграммы, рисунки, изображения, графики, кнопки и другие объекты.
Числовые значения
Числовые значения , или просто числа, выражают различные количественные соотношения данных определенного типа, например объемы продаж, число служащих фирмы, атомный вес, тестовые оценки и т.д. Числовые значения, занесенные в ячейки таблицы, могут использоваться в формулах и диаграммах. Числовыми значениями могут быть также выражены даты (например, 21.02.2018 ) или время (например, 11:12:43).
Текст
Помимо чисел, в рабочий лист часто вводят текст. Как правило, текст используется для обозначения числовых данных и заголовков столбцов или для представления какой-либо поясняющей информации о таблице.
Текст, который начинается с числа, все равно будет считаться текстом. Например, если вы запишите в ячейку текст, скажем, «1 платеж», то программа будет полагать это текстом, а не числом. Поэтому эту ячейку нельзя применять в вычислениях.
Если вы хотите использовать это число в расчетах, введите число 1 в одну ячейку, а текст «платеж» — в соседнюю слева.
Формулы
Формулы — это то, что делает электронную таблицу именно электронной таблицей. Без формул Excel представлял бы собой скорее всего текстовый процессор с усовершенствованной функцией работы с таблицами. Excel позволяет вводить в ячейки очень сложные формулы, в которых применяются числовые значения, а также текст.
Если ввести формулу в ячейку, то результат, подсчитанный по этой формуле, возникает в данной ячейке. Если вы поменяете любое из чисел, используемых в формуле, программа автоматически осуществит вычисления по формуле и отразит новый результат.
Формулой считается любое математическое выражение. В формулах также можно использовать встроенные в программу функции. На рис. 5 показан лист, с помощью которого можно рассчитать объем ежемесячных выплат по кредиту. Он содержит числа, текст и формулы. В ячейках в столбце А содержатся текстовые значения. В столбце В находятся четыре числовых и две формулы (формулы есть в ячейках В7 и В 11 ) . В столбце D отображается то, что в действительности находится в ячейках столбца В.
Читайте также: