Изменение формата ячеек в Excel - TurboComputer.ru
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд (пока оценок нет)
Загрузка...

Изменение формата ячеек в Excel

Изменение формата ячеек в Excel

Необходимость изменения формата по умолчанию на другой

Формат представления данных в ячейках

Формат ячеек по умолчанию (“Общий”)

По умолчанию после создания документа все ячейки находятся в формате “Общий”. Этот формат имеет ряд хитростей:

  • числа выравниваются по правому краю, а текст — по левому;
  • если, изменяя ширину столбца, сделать ее меньше определенной, то число в ячейках заменяется на символы “#”. Это не ошибка. Это означает, что нужно сделать столбец пошире;
  • если число очень большое (“6000000000000”) или очень маленькое (“0,00000000000001”), оно автоматически преобразуется в экспоненциальный (научный) формат (“6E+12” и “1E-14” соответственно);
  • при изменении ширины столбца округляются десятичные дроби. Например, если написать “3,1415”, затем изменить ширину так, чтобы “5” перестала помещаться, в ячейке отобразится “3,142”.

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

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

Есть 3 способа изменить формат представления данных в ячейках:

  1. автоматически после ввода определенных данных в ячейку Excel сам сменит формат ячейки;
  2. с помощью кнопок на панели инструментов “Форматирование”.
  3. с помощью окна “Формат ячеек”;

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

  • Дата. Если в ячейке написать “1.2.3” или “1/2/3”, Excel заменит это на “01.02.2003” (первое число второго месяца третьего года). Если написать “1.2” или “1/2”, то Excel заменит это на “01.фев”. При этом формат ячейке будет автоматически преобразован в “Дата”;
  • Процентный. Если в ячейке написать “1%”, формат ячеки автоматически сменится на “Процентный”;
  • Время. Если в ячейке написать “13:46:44” или “13:46”, формат ячейки автоматически сменится на “Время”;

Внимание. на разных компьютерах форматы по умолчанию представления чисел, денежных единиц, даты и времени могут отличаться! Настроить их можно по пути “Панель управления” —> “Язык и региональные стандарты” —> закладка “Региональные параметры”.

Изменение формата ячеек с помощью кнопок на панели инструментов “Форматирование”

На панели инструментов “Форматирование” находятся 5 кнопок, с помощью которых можно быстро изменить формат выделенных ячеек.

Описание кнопок (слева направо):

  • Денежный формат. Будет использована денежная единица по умолчанию (см. выше);
  • Процентный формат. Если в ячейке уже будет находится число, то Excel домножит его на 100 и добавит знак “%”. Все правильно, ведь 1 арбуз — это “100%”, а “0,7” арбуза — “70%”;
  • Формат с разделителями (числовой формат). В этом формате будут отделяться пробелом группы разрядов (сотни, сотни тысяч и т.д.) и будет добавлено 2 знака после запятой;
  • Увеличить разрядность. Добавляет один десятичный разряд;
  • Уменьшить разрядность. Убирает один десятичный разряд.

Изменение формата с помощью окна “Формат ячеек”

Почему Excel рассматривает длинные числовые строки как научную нотацию даже после изменения формата ячейки на текст

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

вот то, что делает меня орехи (это Excel 2010):

  1. возьмите длинное число, например 1240800388917, и вставьте его в ячейку на новом листе.
  2. Формат ячеек Excel по умолчанию общий, поэтому строка представлено в научной записи как 1.2408 E+12
  3. щелкните правой кнопкой мыши на ячейке, выберите Формат ячеек, установите формат текста

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

теперь, если я делаю шаги в другом порядке:

  1. форматировать пустую ячейку как текст. Щелкните правой кнопкой мыши на ячейке, выберите Формат ячеек, установите формат Text
  2. возьмите длинное число, например 1240800388917 и вставьте его в текстовую ячейку

теперь ячейка отображается в виде строки, а не в научной записи.

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

есть простой хороший способ легко держать эти строки, отформатированные как текст?

Почему Excel делает это?

Читайте также:  Microsoft Excel: вычитание процентов

13 ответов

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

Примечание: преобразование является усечением, в отличие от округления. (12345678901234567890 is 1.23456789012346E+19, но excel покажет вам 1.23456789012345E+19)

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

это работало для меня в Excel 2010. Просто выберите столбец, щелкните правой кнопкой мыши, Формат ячеек, пользовательские и выберите опцию, которая говорит 0 (второй вариант ниже общего).

один Апостроф ‘ перед числом заставит Excel рассматривать число как текст (включая выравнивание по левому краю по умолчанию). И если у вас есть ошибки помечены, он будет отображаться как число, хранящееся в виде текста ошибки на ячейке.

Я обнаружил, что в Office 2010, если сначала отформатировать столбец, а затем вставить данные, он будет показывать большие числа правильно.

попробуйте это. это работает для меня, Excel 2013.

, где a1 содержит числовое значение. После этого скопируйте столбец и вставьте его в исходный столбец.

вы можете форматировать ячейки, как обычай > #

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

Я много работаю со штрих-кодами (UPC), и excel по умолчанию форматирует их как научную нотацию. Сводит меня с ума! Почему это не вариант, который я никогда не узнаю.

Excel 2010: это работает по одному столбцу за раз, а не по строке. Выберите столбец или подмножество столбца, на вкладке Данные выберите ” текст в столбцы “и перейдите вправо к “готово”.”Больше никаких научных обозначений.

Я согласен со многими пользователями Excel, это преступление, что нет возможности предотвратить научную нотацию!

Я знаю о возрасте вопроса, но это тот, который я приземлился после поиска google, и который не ответил на мой вопрос (почему форматирование текста не работает и почему число сокращается до 15 цифр после форматирования текста).

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

это не ошибка, это функция.

все, что вы сделаете после этого, будет использовать усеченное значение, поэтому никакой трюк форматирования не поможет. Тем не менее, вы можете использовать Text to columns возможность преобразования экспоненциальной записи в текст (нажмите на заголовок столбца, нажмите Text to data , потом Delimited , Next , снимите все флажки разделителей, выберите Text in Column data format а то Finish ) для преобразования значений в текст и немедленно отображается как 15-значное число. Но, это будет только 15 цифр, так что если у вас больше количество, остальные проиграл.

для того, чтобы иметь номер в таблице ровно то, как вы набрали его в вы должны хранить его в виде текста. Таким образом, вы должны предшествовать ему апострофом или форматировать ячейки как text до ввод / копирование в него значений. Если это “иногда не работает”, то, извините, вы либо делаете что-то неправильно, либо у вас есть какая-то автозамена (если вы используете апостроф и большое число > 15 цифр Excel рассматривает его как неправильное значение и помечает ячейку предупреждающим сообщением, поэтому это замечание не является личным или критикой).

другой способ сделать это в массовых числах-импортировать значения из текстового файла. Я осмелюсь сказать, что это единственный способ для большинства ситуаций. И обязательно импортируйте файл, а не просто откройте его, как Excel обрабатывает csv тип, как это родной формат и обрезает большие числа до 15 цифр, как само собой разумеющееся.

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

Читайте также:  Выполнение ранжирования в Microsoft Excel

Как изменить формат ячейки в Excel

Вы уже знаете, как делать списки в Excel

,

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

Для начала нужно этот список в Excel оформить. В первом столбце Вы видите номер сделки. Допустим, нужно, чтобы номер был не 1, а 0001, не 2 а 0002 и так далее. Как быть в таком случае? Если Вы попробуете поставить нули перед числами, это Вам сделать не удастся — нули исчезнут.

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

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

Теперь посмотрим на столбец Продано . В нем прописаны суммы сделок, но нет валюты. Также денежные суммы обычно прописываются с двумя числами после запятой, например 2,00 $ . Измените формат ячеек Excel этого столбца. Выделяете весь столбец, и выбираете формат Денежный , либо соответствующую иконку ниже. Числа прописались с рублями. Еще раз раскройте список с форматами, и выберите Другие числовые форматы . Выберите Денежный в поле слева, и в поле Обозначение прокручиваете список вниз. Если Вы хотите, чтобы значок доллара был в ячейках справа от цифр, выберите, к примеру, обозначение Французский (Канада) . Нажимаете ОК.

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

Формат ячеек Excel может быть самым разным: числовой, краткая дата, или длинный формат даты, и другие форматы. Если Вы зайдете в форматы в пункте меню Главная , и выберете Другие числовые форматы , то сможете посмотреть параметры каждого формата и изменить их. Кроме того, там, кроме вкладки Число , есть вкладка

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

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

Например, введите в какую-то пустую ячейку 13:30, а в другую 14:40. В третью ячейку введите формулу Excel: вычтите из второй ячейки первую. Будет показана разница во времени, 1:10:00, и формат третьей ячейки будет Время . Если Вы сделаете формат ячейки Общий , то результат будет 0,0486… Это происходит потому, что формат Время при вычислениях преобразуется в количество суток. 1:10 — это 1 час 10 минут, или 0,0486 суток. А уже потом это число преобразуется в формате Время в 1:10, и мы этот результат видим.

Попробуйте проделать похожую операцию с датами. В одну ячейку поставьте, например, 01.01.13, а в другую 24.02.13. Эти данные преобразуются в 01.01.2013 и 24.02.2013, а форматы ячеек Excel станут Дата . Теперь в новой ячейке вычтите из второй ячейки первую. Получится 54, что означает количество дней между двумя датами.

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

В этом Вы лично можете убедиться, если введете в ячейку число, например, 1, и преобразуете ячейку Excel в формат Дата . В ячейке появится дата 01.01.1900.

Читайте также:  Решение ошибки «Unexpected Store Exception» в Windows 10

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

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

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

Как преобразовать числа из текстового формата в числовой

Иногда значения в Excel выглядят как числа, но на самом деле это текст. Их нельзя суммировать, умножать, вычитать, хотя выглядят как числа. Дело в том, что это числа в формате текста.

Excel помогает автоматически конвертировать данные в числа при копировании из посторонних источников. Не всегда это получается. В этой статье расскажу как конвертировать такие “текстовые” числа в настоящие.

Как найти числа в текстовом формате

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

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

  • Числа выровнены по левому краю
  • Если выделена группа ячеек с такими “числами”, то в строке состояния не будет подсказок по сумме, среднему чисел в выделенном диапазоне
  • Апостроф стоит первым символом в строке формул этой ячейки

На скриншоте ниже пример как Excel автоматически выравнивает по левому краю текстовые значения и по правому краю числовые значения.

Как конвертировать текст в числа в Excel

Конвертация текста в числа через меню ошибки

Если в левом верхнему углу ячеек с числовыми значениями появился зеленый треугольник, то:

  • Выделите диапазон таких ячеек
  • Кликните по иконке с желтым ромбом и восклицательным знаком
  • В выпадающем списке выберите пункт меню “Преобразовать в число”

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

Как конвертировать текст в число с помощью смены формата ячейки

Есть еще один способ изменить формат значений текстовой ячейки на числовой. Сделать это можно с помощью смены формата ячейки на числовой. Как это сделать:

  • Выделите левой клавишей мыши диапазон ячеек
  • На панели инструментов перейдите на вкладку “Главная”
  • Перейдите в подгруппу “Число” и в выпадающем списке выберите формат “Числовой”

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

Как преобразовать текст в число с помощью специальной вставки

Этот метод требует чуть больше действий, но работает в 99% случаев.

  • Выделить ячейки с числами в текстовом формате, и задать формат ячейки “Основной”
  • Кликнуть левой клавишей мыши по пустой ячейке на листе
  • Скопировать эту ячейку, зажав сочетание клавиш CTRL + C
  • Выделить левой клавишей мыши ячейки которые хотите конвертировать в числа
  • Кликнуть правой клавишей мыши и выбрать пункт “Специальная вставка” в выпадающем меню
  • В диалоговом окне в категории “Вставить” поставить галочку напротив “Значения” . В разделе “Операция” ставим галочку напротив “Сложить” и нажимаем “ОК”

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

Как конвертировать текст в числа с помощью функции ЗНАЧЕН()

Для конвертации числовых значений в текстовом формате в числа часто используют функцию ЗНАЧЕН() . Функция предназначена для перевода текста в числа. Напишите функцию в адресной строке и укажите ссылку на ячейку для конвертации.

Эта функция работает со всеми форматами ячеек.

Как конвертировать текст в числа с помощью математических вычислений

Достаточно умножить значение в текстовом формате на единицу, чтобы получить значение в числовом формате в Excel.

Прибавьте ноль или разделите значение на единицу и текстовое значение будет преобразовано в числовой формат.

Поделитесь в комментариях какой из методов вам кажется самым удобным в работе.

Ссылка на основную публикацию
Adblock
detector