Excel отображать только положительные значения

Обновлено: 07.07.2024

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

Автоматически изменяемые диапазоны ячеек в Excel

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

Как сделать автоматическое изменение диапазона в Excel

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

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

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

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

Перейти на вкладку «Формулы», которая находится в главном меню. Там будет раздел «Определенные имена», где есть кнопка «Присвоить имя», по которой и надо нам нажать.
Далее появится диалоговое окно, в котором нужно заполнить поля таким образом, как изображено на скриншоте. Важно отметить, что нам надо применять функцию =СМЕЩ совместно с функцией СЧЕТ, чтобы создать автоматически обновляемый диапазон.
После этого нам надо использовать функцию СУММ, в качестве аргумента которой используем наш динамически изменяемый диапазон.

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

Функция СМЕЩ в Excel

Давайте рассмотрим функции, которые были нами записаны в поле «диапазон» ранее. С помощью функции СМЕЩ мы можем определять величину диапазона, учитывая то, сколько ячеек в колонке B заполнено. Аргументы функции следующие:

Начальная ячейка. С помощью этого аргумента пользователь может показать, какая ячейка диапазона будет считаться верхней левой. От нее будет происходить отчет вниз и вправо.
Смещение диапазона по строкам. С помощью этого диапазона мы задаем количество ячеек, на которое должно происходить смещение от верхней левой ячейки диапазона. Можно использовать не только положительные значения, а нулевые и минусовые. В таком случае смещения может не происходить вообще или же оно будет осуществляться в обратном направлении.
Смещение диапазона по колонкам. Этот параметр аналогичен предыдущему, только позволяет задать степень смещения диапазона по горизонтали. Здесь также можно использовать как нулевые, так и отрицательные значения.
Величина диапазона в высоту. Фактически название этого аргумента дает нам четко понять, что оно означает. Это то количество ячеек, на которое должно происходить увеличение диапазона.
Величина диапазона в ширину. Аргумент аналогичный предыдущему, только уже касается колонок.

Указывать последние два аргумента не нужно, если в этом нет необходимости. В этом случае величина диапазона будет составлять всего одну ячейку. Например, если указать формулу =СМЕЩ(A1;0;0), эта формула будет ссылаться на ту же ячейку, которая в первом аргументе. Если же смещение по вертикали поставить 2 единицы, то в этом случае ячейка будет ссылаться на ячейку A3. Теперь давайте детально распишем, что означает функция СЧЕТ.

Функция СЧЕТ в Excel

С помощью функции СЧЕТ мы определяем, сколько ячеек в колонке B у нас по итогу заполнено. То есть, мы определяем с помощью двух функций то, сколько ячеек в диапазоне заполнено, и исходя из полученных сведений определяет величину диапазона. Следовательно, итоговая формула получится следующей: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

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

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

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

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

Динамические диаграммы в Excel

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

Выделяем наш диапазон, после чего вставляем диаграмму типа «Гистограмма с группировкой». Найти этот пункт можно в разделе «Вставка» в разделе «Диаграммы–Гистограмма».
Делаем левый клик мышью по случайной колонке гистограммы, после чего в строке функций будет показана функция =РЯД(). На скриншоте вы можете посмотреть на детальную формулу.
После этого в формулу нужно внести некоторые изменения. Необходимо заменить диапазон после «Лист1!» на название диапазона. В результате получится следующая функция: =РЯД(Лист1!$B$1;;Лист1!доход;1)
Теперь осталось в отчет добавить новую запись, чтобы проверить, обновляется ли диаграмма автоматически, или нет.

Полюбуемся теперь на нашу диаграмму.

Давайте подведем итоги, как мы действовали. Мы на предыдущем этапе создали динамический диапазон, размер которого зависит от того, сколько элементов в него входит. Для этого мы использовали комбинацию функций СЧЕТ и СМЕЩ. Мы этот диапазон сделали именным, и потом ссылку на это имя использовали в качестве диапазона нашей гистограммы. Какой конкретно диапазон выбирать в качестве источника данных на первом этапе, не столь важно. Главное – заменить его на имя диапазона потом. Так можно существенно сэкономить оперативную память.

Именованные диапазоны и их использование

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

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

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

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

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

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

Кроме этого, возможно создание именованного диапазона с помощью вкладки «Формулы», воспользовавшись инструментом «Задать имя». После того, как мы выберем его, появится окно, где надо выбрать имя для нашего диапазона, а также указать область, на которую он будет распространяться, вручную. Также можно задать где будет действовать этот диапазон: в рамках одного листа или на всей книге.

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

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

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

В случае, если в состав заголовка входит несколько слов, Excel автоматически их будет разделять с помощью знака подчеркивания.

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

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

Скрыть отрицательные числа в Excel с помощью условного форматирования

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

1. Выберите диапазон данных, в котором вы хотите скрыть отрицательные числа.

2. Нажмите Главная > Условное форматирование > Выделите правила ячеек > Меньше, чем, см. снимок экрана:

3. В Меньше, чем диалоговое окно введите номер 0 в текстовое поле и щелкните раскрывающийся список, чтобы выбрать Пользовательский формат вариант, см. снимок экрана:

4. И в выскочившем Формат ячеек диалоговом окне выберите белый цвет, такой же, как цвет фона для шрифта ячейки под шрифт вкладку, см. снимок экрана:

5. Затем нажмите OK > OK чтобы закрыть диалоги, и все отрицательные числа были скрыты сразу.

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

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

1. Выделите диапазон ячеек, в котором вы хотите скрыть отрицательные значения.

2. Щелкните правой кнопкой мыши и выберите Формат ячеек из контекстного меню см. снимок экрана:

3. Тогда в Формат ячеек диалоговое окно под Число вкладку нажмите На заказ слева Категория панель и введите 0; "" в Тип текстовое поле, см. снимок экрана:

4, Затем нажмите OK Кнопка, все отрицательные значения в выделении немедленно скрываются.

Скрыть отрицательные числа в Excel с помощью Kutools for Excel

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

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Выберите диапазон данных, который вы хотите использовать.

2. Нажмите Kutools > Выберите > Выберите определенные ячейки, см. снимок экрана:

3. В Выбрать определенные ячейки диалог, выбрал Ячейка под Тип выбораи укажите Менее из Конкретный тип раскрывающийся список, затем введите номер 0 в текстовое поле, см. снимок экрана:

4, Затем нажмите Ok or Применить, и все отрицательные числа были выбраны, как показано на следующем снимке экрана:

5. Нажмите OK чтобы закрыть диалоговое окно, затем перейдите Главная вкладка и выберите белый цвет в качестве цвета шрифта ячейки из Цвет шрифта выпадайте, см. снимок экрана:

Советы: выбранный вами цвет шрифта должен совпадать с цветом фона ячейки.

6. И все отрицательные значения выбранных ячеек будут скрыты.

Как быстро изменить все положительные числа или значения на отрицательные в Excel? Следующие методы помогут вам быстро изменить все положительные числа на отрицательные в Excel.

Измените положительные числа на отрицательные с помощью специальной функции вставки

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

1. Нажмите номер -1 в пустую ячейку и скопируйте.

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

3, Затем выберите Все из файла макаронные изделияи Размножаться из операция.

4, Затем нажмите OK, все положительные числа были заменены на отрицательные.

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

Изменить или преобразовать положительные числа в отрицательные и наоборот

Измените положительные числа на отрицательные с помощью кода VBA

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

1. Выберите диапазон, который вы хотите изменить.

2. Нажмите разработчик >Визуальный Бейсик, Новый Microsoft Visual Basic для приложений появится окно, щелкните Вставить > Модульа затем скопируйте и вставьте в модуль следующие коды:

3. Нажмите на Чтобы запустить код, появится диалоговое окно, в котором вы можете выбрать диапазон, в котором вы хотите преобразовать положительные значения в отрицательные. Смотрите скриншот:

4. Нажмите Ok, то положительные значения в выбранном диапазоне сразу преобразуются в отрицательные.

Измените положительные числа на отрицательные или наоборот с помощью Kutools for Excel

Вы также можете использовать Kutools for ExcelАвтора Изменить знак ценностей инструмент для быстрого изменения всех положительных чисел на отрицательные.

Если вы установили Kutools for Excel, вы можете изменить положительные числа на отрицательные следующим образом:

1. Выберите диапазон, который хотите изменить.

2. Нажмите Kutools > Content > Изменить знак ценностей, см. снимок экрана:

3. И в Изменить знак ценностей диалоговое окно, выберите Измените все положительные значения на отрицательные опцию.

4. Затем нажмите OK or Применить. И все положительные числа были преобразованы в отрицательные числа.

Советы: Чтобы изменить или преобразовать все отрицательные числа в положительные, выберите Измените все отрицательные значения на положительные в диалоговом окне, как показано на следующем снимке экрана:

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

Для чего может понадобиться знак “+” в ячейке перед числом

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

Или же необходимо составить таблицу с прогнозом погоды (или архивом погоды за прошлый месяц, если пожелаете). В таком случае необходимо писать, сколько градусов и какой знак (плюс или минус). И если необходимо сказать, что на улице жарко, то написать в ячейке +35 будет довольно тяжело. То же самое и со знаком минус. Но это только если без хитростей.

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

Изменить формат на текстовый. В таком случае ни о какой формуле не может идти речи до тех пор, пока формат снова не сменят на числовой.
Кроме этого, можно просто написать знак +, после чего нажать клавишу Ввод. После этого в ячейке появится знак плюса, но при этом не появится знака ввода формулы. Правда, нужно быть осторожным и действительно нажимать клавишу ввода. Все дело в том, что если воспользоваться другим популярным методом подтверждения ввода данных в формулу, а именно – нажатием на другую ячейку, то она автоматически будет вводиться в формулу. То есть, значение, содержащееся в ней, приплюсуется, и будет неприятно.
Есть еще один элегантный способ вставки плюса в ячейку. Достаточно поставить перед ним одинарную кавычку. Таким образом Excel понимает, что ему нужно рассматривать эту формулу, как текст. Например, вот так ‘+30 градусов по Цельсию.
Также можно обхитрить Эксель, сделав так, чтобы плюс не был первым символом. В качестве первого символа может выступать любая буква, пробел или символ, не зарезервированный под ввод формул.

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

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

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

Ноль перед числом в ячейке Эксель

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

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

Ну и воспользоваться классическим методом сохранения в текстовом формате – это также один из возможных вариантов.

Как поставить прочерк в ячейке Эксель

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

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

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

Открывается таблица символов.

Далее нам нужно выбрать вкладку «Символы», а набор выбрать «Символы рамок». На этом скриншоте видно, где находится наш прочерк.

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

Мы получаем такой результат.

Как поставить знак “не равно” в Эксель

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

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

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

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

Читайте также: