Сохранение начальных нулей и больших чисел. Удаление нулевых значений в Microsoft Excel Как в экселе убрать ноль

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

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

Способ 1: настройки Excel

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

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

Способ 2: применение форматирования

Скрыть значения пустых ячеек можно при помощи изменения их формата.

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

Способ 3: условное форматирование

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

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

Способ 4: применение функции ЕСЛИ

Ещё один вариант скрытия нулей предусматривает использование оператора ЕСЛИ .

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

Способ 5: применение функции ЕЧИСЛО

Следующий способ является своеобразной комбинацией функций ЕСЛИ и ЕЧИСЛО .

Существует целый ряд способов удалить цифру «0» в ячейке, если она имеет нулевое значение. Проще всего, отключить отображения нулей в настройках Excel. Но тогда следует учесть, что они исчезнут по всему листу. Если же нужно применить отключение исключительно к какой-то конкретной области, то в этом случае на помощь придет форматирование диапазонов, условное форматирование и применение функций. Какой из данных способов выбрать зависит уже от конкретной ситуации, а также от личных умений и предпочтений пользователя.

Значения, отображаемые в ячейках электронных таблиц приложения Microsoft Office Excel, зачастую формируются в итоге выполнения записанных в них формул. Итогом вычислений может быть и нулевое значение, показывать которое в ячейке неугодно. Нули не улучшают всеобщего воспринятия итогов, исключительно если в иных ячейках колонки формулы выводят текстовые, а не числовые значения. Решить эту задачу дозволено несколькими методами.

Вам понадобится

Инструкция

1. Дабы отключить отображение нулевых значений во всех ячейках открытого листа документа Excel, воспользуйтесь одной из установок в всеобщих настройках табличного редактора. Эти настройки открываются через основное меню – в версии 2010 для доступа к нему кликните по кнопке «Файл», а в версии 2007 для этого предуготовлена круглая кнопка Office. Кликните в меню по пункту «Параметры» (версия 2010) либо нажмите кнопку «Параметры Excel» (версия 2007).

2. Выберите в списке раздел «Добавочно» и прокрутите список настроек до надписи «Показать параметры для дальнейшего листа». Уберите отметку у надписи «Показывать нули в ячейках, которые содержат нулевые значения», и нажмите кнопку OK.

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

4. В поле под надписью «Тип» введите такую последовательность знаков: “0;-0;;@” (без кавычек). Если ненулевые значения выделенной области обязаны отображаться с некоторым числом знаков позже запятой, позже всякого нуля в этой записи добавьте соответствующее число ноликов, отделив их запятой. Скажем, дабы задать точность до сотых эта запись должна выглядеть так: “0,00;-0,00;;@”. После этого нажмите OK, и нули перестанут отображаться.

5. 3-й метод не убирает нулевые значения, а окрашивает их в цвет фона ячейки и этим делает заметными. Данный способ использует условное форматирование – выделите надобную колонку либо комплект колонок и щелкните по кнопке с таким наименованием в группе команд «Жанры». В вывалившемся списке перейдите в раздел «Правила выделения ячеек» и выберите строку «Равно».

6. В левое поле появившейся формы введите нуль, а в списке правого поля выберите «Пользовательский формат». Откроется диалог «Формат ячеек», на вкладке «Шрифт» которого вам надобен выпадающий список ниже надписи «Цвет» – раскройте его и в таблице цветов выберите оттенок фона ячейки (обыкновенно – белый). Нажмите кнопки OK в обоих открытых диалогах, и задача будет решена.

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

Инструкция

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

2. Если большой шрифт нужно заменить больше мелким в тексте документа Word, то начните с выделения надобного его фрагмента. Если требуется уменьшить размер каждого текста, то для выделения дозволено применять сочетание клавиш ctrl + a. Позже этого раскройте в меню программы выпадающий список «Размер шрифт а» в группе команд «Шрифт» на вкладке «Основная» и выберите меньшее числовое значение. Дозволено взамен выпадающего списка нажать сочетание клавиш ctrl + shift + p – оно открывает отдельное окно с настройками шрифт а, в котором тоже дозволено изменять его размер.

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

4. Избавиться от слишком огромного шрифт а, используемого операционной системой для надписей в графическом интерфейсе ОС, дозволено изменением разрешения экрана. В ОС Windows Vista и Windows 7 для этого надобно щелкнуть правой кнопкой по «обоям» на рабочем столе и в контекстном меню предпочесть строку «Разрешение экрана». Позже этого щелкните по кнопке «Разрешение» и с подмогой ползунка установите надобное значение. После этого нажмите кнопку «Применить».

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

Инструкция

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

2. Нажмите правую клавишу мышки. Возникнуть контекстное меню. Активируйте в нем пункт «Копировать», вокруг ячейки должна возникнуть пунктирная рамка. Еще раз щелкните правой клавишей. В появившемся вновь списке выберите строку «Особая вставка». Откроется новое окошко с вариантами вставок. Левой кнопкой мышки подметьте пункт «значения» либо «значения и форматы чисел». После этого нажмите кнопку «ОК». Вы увидите, что в строке функций взамен формулы возникло число-итог.

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

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

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

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

Точка и запятая могут исполнять функции разделителя разрядов в числах, записанных в формате десятичной дроби. В большинстве англоязычных стран в качестве такого разделителя применяется точка, а в России – запятая. С этим зачастую бывает связана надобность замены точек на запятые в табличном редакторе Microsoft Office Excel.

Вам понадобится

• Табличный редактор Microsoft Office Excel.

Инструкция

1. Если в настройках вашего табличного редактора точка задана в качестве десятичного разделителя, то изменить это дозволено в одном из разделов панели установок Excel. Дабы до нее добраться, раскройте меню приложения. Это дозволено сделать, нажав клавишу Alt, а позже нее – кнопку «Ф». В меню Excel 2010 пункт «Параметры» размещен в предпоследнюю строку списка команд, а в Excel 2007 кнопка «Параметры Excel» находится в правом нижнем углу меню.

2. Выберите строку «Добавочно» в левой колонке панели настроек и в разделе «Параметры правки» обнаружьте строку «Применять системные разделители». Если в чекбоксе у этой надписи отметка стоит, то необходимое вам поле «Разграничитель целой и дробной части» редактировать немыслимо. Уберите ее, поставьте запятую в текстовое поле и нажмите кнопку OK для фиксации метаморфозы в настройках редактора.

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

4. Для тотального замещения запятыми всех точек во всех ячейках электронной таблицы используйте диалог поиска и замены. Для его вызова предуготовлены «жгучие клавиши» Ctrl + H и пункт «Заменить» в выпадающем списке кнопки «Обнаружить и выделить» – она размещена в группу команд «редактирование» на вкладке «Основная».

5. В поле «Обнаружить» диалога поиска и замены поставьте точку, а запятую – в поле «Заменить на». Если использования этой операции только на нынешнем листе документа будет довольно, нажмите кнопку «Заменить все» и Excel приступит к выполнению команды. Для замены на всех листах открытого документа нажмите кнопку «Параметры», установите значение «в книге» в выпадающем списке у надписи «Искать» и лишь позже этого кликните по кнопке «Заменить все».

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

Вам понадобится

• Табличный редактор Microsoft Office Excel 2007 либо 2010.

Инструкция

1. Запустите табличный редактор, загрузите в него документ, формулы которого вас волнуют, и откройте в нем необходимый лист.

2. Перейдите на вкладку «Формулы» в меню Microsoft Excel. Надобная вам кнопка размещена в группу команд «Зависимости формул», но никакой надписи на ней не отображено, а по картинке опознать предназначение не так примитивно. Ориентируйтесь на надпись «Влияющие ячейки» – необходимая пиктограмма размещена правее, а при наведении на нее указателя мыши всплывает подсказка с заголовком «Показать формулы » и коротким пояснением назначения. Кликните по этой кнопке, и Excel заменит значения формулами в тех ячейках, где эти формулы имеются. При этом будет изменено форматирование всех колонок – они станут гораздо шире.

3. Дабы воротить таблицу к исходному виду, щелкните по этой же пиктограмме вторично. Дозволено испробовать взамен кнопки для включения и выключения режима отображения формул применять комбинацию из служебной клавиши Ctrl и клавиши с тильдой ~ (в русской раскладке – литера Ё). Справочные материалы Microsoft утверждают, что такая комбинация должна трудиться, но это не неизменно так.

4. Если вам требуется не разовое включение, а непрерывное отображение формул взамен значений в нынешнем листе либо во каждому документе, измените соответствующую установку в настройках табличного редактора. Для этого вначале в основном меню приложения выберите пункт «Параметры» (Excel 2010) либо кликните по кнопке «Параметры Excel» (Excel 2007).

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

Вам когда-нибудь приходилось импортировать или вводить в Excel данные, содержащие начальные нули (например, 00123) или большие числа (например, 1234 5678 9087 6543)? Это могут быть номера социального страхования, телефонные номера, номера кредитных карт, коды продуктов, номера счетов или почтовые индексы. Excel автоматически удаляет начальные нули и преобразует большие числа в экспоненциальное представление (например, 1,23E+15), чтобы их можно было использовать в формулах и математических операциях. В этой статье объясняется, как сохранить данные в исходном формате, который Excel обрабатывает как текст.

Преобразование чисел в текст при импорте текстовых данных

Для форматирования отдельных столбцов в виде текста при импорте данных в Excel используйте функцию Получить и преобразовать (Power Query)

В Excel 2010 и Excel 2013 импортировать текстовые файлы и преобразовывать числа в текст можно двумя способами. Рекомендуется использовать Power Query (для этого нужно скачать надстройку Power Query). Если надстройку Power Query скачать не удается, можно воспользоваться мастером импорта текста . В этом случае импортируется текстовый файл, однако данные проходят те же этапы преобразования, что и при импорте из других источников, таких как XML, Интернет, JSON и т. д.

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

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

Инструкции

Применение функции ТЕКСТ для форматирования

Для преобразования данных в нужный формат можно использовать расположенный рядом с ними пустой столбец и функцию ТЕКСТ .

Округление номеров кредитных карт

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

Форматирование столбца в виде текста

Выделите диапазон данных и нажмите клавиши CTRL+1 , чтобы открыть диалоговое окно Формат > Ячейки . На вкладке Число выберите формат Текстовый .

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

Использование апострофа

Перед числом можно ввести апостроф (" ), и Excel будет обрабатывать его как текст.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте

В математике деление на ноль – невозможно! Одним из способов для объяснения данного правила является анализ процесса, который показывает, что происходит, когда одно число разделено на другое.

Ошибка деления на ноль в Excel

В реальности операция деление это по сути тоже что и вычитание. Например, деление числа 10 на 2 является многократным вычитанием 2 от 10-ти. Многократность повторяется до той поры пока результат не будет равен 0. Таким образом необходимо число 2 вычитать от десяти ровно 5 раз:

1. 10-2=8
2. 8-2=6
3. 6-2=4
4. 4-2=2
5. 2-2=0

Если же попробовать разделить число 10 на 0, никогда мы не получим результат равен 0, так как при вычитании 10-0 всегда будет 10. Бесконечное количество раз вычитаний ноля от десяти не приведет нас к результату =0. Всегда будет один и ото же результат после операции вычитания =10:

• 10-0=10
• 10-0=10
• 10-0=10
• ∞ бесконечность.

В кулуарах математиков говорят, что результат деления любого числа на ноль является «не ограниченным». Любая компьютерная программа, при попытке деления на 0, просто возвращает ошибку. В Excel данная ошибка отображается значением в ячейке #ДЕЛ/0!.

Но при необходимости можно обойти возникновения ошибки деления на 0 в Excel. Просто следует пропустить операцию деления если в знаменателе находится число 0. Решение реализовывается с помощью помещения операндов в аргументы функции =ЕСЛИ():

Таким образом формула Excel позволяет нам «делить» число на 0 без ошибок. При делении любого числа на 0 формула будет возвращать значение 0. То есть получим такой результат после деления: 10/0=0.

Как работает формула для устранения ошибки деления на ноль

Для работы корректной функция ЕСЛИ требует заполнить 3 ее аргумента:

1. Логическое условие.
2. Действия или значения, которые будут выполнены если в результате логическое условие возвращает значение ИСТИНА.
3. Действия или значения, которые будут выполнены, когда логическое условие возвращает значение ЛОЖЬ.

В данном случаи аргумент с условием содержит проверку значений. Являются ли равным 0 значения ячеек в столбце «Продажи». Первый аргумент функции ЕСЛИ всегда должен иметь операторы сравнения между двумя значениями, чтобы получить результат условия в качестве значений ИСТИНА или ЛОЖЬ. В большинстве случаев используется в качестве оператора сравнения знак равенства, но могут быть использованы и другие например, больше> или меньше >. Или их комбинации – больше или равно >=, не равно!=.

Если условие в первом аргументе возвращает значение ИСТИНА, тогда формула заполнит ячейку значением со второго аргумента функции ЕСЛИ. В данном примере второй аргумент содержит число 0 в качестве значения. Значит ячейка в столбце «Выполнение» просто будет заполнена числом 0 если в ячейке напротив из столбца «Продажи» будет 0 продаж.

Если условие в первом аргументе возвращает значение ЛОЖЬ, тогда используется значение из третьего аргумента функции ЕСЛИ. В данном случаи - это значение формируется после действия деления показателя из столбца «Продажи» на показатель из столбца «План».

Формула для деления на ноль или ноль на число

Усложним нашу формулу функцией =ИЛИ(). Добавим еще одного торгового агента с нулевым показателем в продажах. Теперь формулу следует изменить на:

Скопируйте эту формулу во все ячейки столбца «Выполнение»:

Теперь независимо где будет ноль в знаменателе или в числителе формула будет работать так как нужно пользователю.