0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как быстро скрыть значения ячеек в Excel?

Как скрыть в Excel значение ячеек

home security top secret adt Как скрыть в Excel значение ячеек Здравствуй уважаемый читатель!

Эта статья предназначена для любителей детективов, она также понравится поклонникам Джеймса Бонда и Маты Хари. Подводя итог, мы поговорим о том, как скрыть значение ячейки, то есть сделать его невидимым для визуального отображения в Excel.

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

  • Скрыть промежуточные результаты вычислений;
  • Скрыть нулевые значения;
  • Убрать отображение результатов вычислений FALSE и TRUE;
  • Скрыть управляющие формулы и т.д.

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

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

  1. Пользовательское форматирование;
  2. Использование функции ЕСЛИ;
  3. Использование функций условного форматирования;
  4. Установка правильных параметров в настройках программы;
  5. Использование параметров цвета и печати;
  6. Использование возможностей защиты листов и формул;
  7. Использование возможностей программы VBA.

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

1. Изменение пользовательского формата

Это один из самых простых вариантов скрыть значение ячеек, то есть в строке состояния вы данные видите, а вот в ячейке он не отображается. Это достигается введением своего формата ячеек. Skit znachenie 2 Как скрыть в Excel значение ячеек Ставите курсор на ячейку или диапазон ячеек, вызываете контекстное меню и выбираете «Формат ячеек», здесь нам нужен ярлык «Число» и выбираем числовой формат «(все форматы)», как видите там много форматов собрано, но вы создадите свой в поле «Тип» вы указываете формат «;;;» («кавычки» не писать, только три знака точка с запятой) и видите как из поля исчезли ваши данные. Для того чтобы данные снова начали отображаться вам нужно поставить в поле «Тип», значение «Основной» (General) или просто убрать формат «;;;».Skit znachenie 3 Как скрыть в Excel значение ячеек В основном такой способ скрыть текст в ячейке надо применять при постоянном использовании скрыта для ваших данных. Но тут есть такой маленький недостаток, он заключается в том, что найти потом ячейки, в которых вы скрыли значения, может быть проблемно, поэтому вам их нужно выделить лёгким цветом или примечанием.

2. Использование функции ЕСЛИ

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

Skit znachenie 4 Как скрыть в Excel значение ячеек

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

Читайте так же:
Как вставить двоеточие между числами, чтобы автоматически использовать их в качестве формата времени в Excel?

=ЕСЛИ((В5=0;””;В3*В4)

3. С помощью возможностей «Условное форматирование»

Skit znachenie 5 Как скрыть в Excel значение ячеек

Вы также можете использовать функцию «Условное форматирование», чтобы узнать, как скрыть значения ячеек. Для этого нам нужно выделить диапазон ячеек, в котором мы хотим скрыть нулевые значения. Выбираем вкладку «Главная», блок «Стили», нажимаем значок «Условное форматирование», в развернувшемся списке выбираем «Правила выделения ячеек — Равные…». Следующий шаг — указать, какое форматирование вы хотите; вам нужно установить 0 в поле «Форматировать ячейки, которые равны:». В открывшемся списке выберите «Пользовательский формат…»,

Skit znachenie 6 Как скрыть в Excel значение ячеек

Чтобы отформатировать ячейки, выберите диалоговое окно «Формат ячеек», затем перейдите на вкладку «Шрифт». В меню «Цвет» измените «Цвет темы: Авто» на «Цвет темы: Белый, фон 1». Нажмите «ОК» и «ОК» еще раз.

Skit znachenie 7 Как скрыть в Excel значение ячеек

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

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

4. Настройка правильных параметров в «Настройках» программы

Скрывать значения в Excel еще проще: установите соответствующий параметр в настройках листа.

Skit znachenie 8 Как скрыть в Excel значение ячеек

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

5. При помощи настроек цвета и параметров печати

Skit znachenie 9 Как скрыть в Excel значение ячеек

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

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

6. При помощи возможностей защиты листа и формул

Это тоже, не сложный способ узнать, как спрятать значение ячеек, но касается он только формул, хотя это тоже нужная возможность, особенно когда много неквалифицированных пользователей, которые из-за отсутствия навыков и знаний могут много чего плохого наделать.Skit znachenie 10 Как скрыть в Excel значение ячеек Собственно, защитить формулу не сложно и происходить так: вы на ячейке с формулой правой кнопкой мыши вызываете контекстное меню и выбираете пункт «Формат ячеек» — выбираете вкладку «Защита», удостоверьтесь, стоит ли флажок на пункте «Защищаемая ячейка», ну и доставляете флажок еще и на пункт «Скрыть формулы». Теперь, когда вы включите защиту листа пройдя по пути «Сервис» — «Защита» — «Защитить лист», формула в ячейке станет невидимой.Skit znachenie 11 Как скрыть в Excel значение ячеек

Читайте так же:
Как быстро найти значение на нескольких листах или в книгах?

7. С помощью возможностей VBA

Не стоит забывать, что Excel имеет мощный встроенный редактор VBA, который позволяет выполнять многие задачи быстро и эффективно. Единственным существенным недостатком является то, что трудно найти пользователей, которые знают, как использовать преимущества VBA.

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

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

Как убрать нули в ячейках в Excel?

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

Приветствуем вас, читатели TutorExcel.ru!

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

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

Обычно в таких случаях нули в таблицах заменяются либо пустым полем, либо символами, близкими по значению (например, тире).

В зависимости от поставленных задач существует несколько различных начальных условий для этого типа замены нулей:

  • Во всем рабочем листе (в каждой ячейке)
  • В определенных (отмеченных) ячейках
  • В формулах
  • В итоговых таблицах.

Как скрыть нулевые значения в Excel?

Предположим, у нас есть таблица чисел (большая, чтобы оценить разницу во внешнем виде), в которой много нулей, и мы используем ее в качестве примера, чтобы попробовать устранить ненулевые значения:

Таблица с данными

На всем листе

Если нам нужно удалить нули из всех без исключения ячеек рабочего листа, перейдите в меню Файл -> Параметры -> Дополнительные вкладки (поскольку эта настройка применяется ко всей рабочей книге):

Настройка параметров отображения нулей на листе

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

Нажмите OK, и исходная таблица будет выглядеть так

Удаление нулей на всем листе

На самом деле, как мы видим, картина стала немного четче, легче читается, и наше внимание не отвлекается на ненужные мелочи.

Чтобы вернуть обратно отображение нулей, то нужно сделать обратную процедуру — в настройках поставить галочку напротив соответствующего поля.

Важные сведения.

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

Выбор листа книги в настройках параметров

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

Вариант 1 частично решает данную проблему.

В выделенных ячейках

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

Читайте так же:
Как быстро ранжировать значения, игнорируя ошибки в Excel?

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

В начале давайте вспомним, что любое число в Excel имеет формат отображения в виде маски A;B;C;D, где A, B, C, D — формат записи и точка с запятой, отделяющая их друг от друга:

  • A — запись когда число положительное;
  • B — запись когда число отрицательное;
  • C — запись когда число равно нулю;
  • D — запись если в ячейке не число, а текст (обычно для чисел не используется).

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

Поскольку наша задача — удалить из записи ровно ноль, нам нужно указать, как будет отображаться третий параметр (часть C маски), а также первый (часть A, положительное число) и второй (часть B, отрицательное число) параметры в маске.

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

Задание формата отображения числа

Затем в маске прописываем формат отображения нуля, вместо него либо ничего не пишем (маска # ##0;- # ##0; чтобы ячейка стала пустой), либо пишем заменяющий символ (маска # ##0;-# ##0;"-", чтобы в ячейке стоял прочерк), и нажимаем OK.

Результат получился следующий:

Удаление нулевых значений в выделенных ячейках

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

В формулах

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

Давайте для исходной таблицы пропишем отклонение между периодами с помощью функции ЕСЛИ.
Вместо стандартной формулы =A1-B1 пропишем =ЕСЛИ(A1-B1=0;"-";A1-B1):

Удаление нулей с помощью формул

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

В сводных таблицах

Имея простую и небольшую таблицу данных (не все ячейки таблицы будут заполнены), построим на ее основе сводную таблицу:

Таблица с данными и сводная таблица

Удаление нулей из сводной таблицы можно разделить на два направления:

  • Работа с данными как с ячейками листа;
    В этом случае мы не делаем разницы между ячейками сводной таблицы и ячейками листа, т.е. можем воспользоваться вышеописанными способами, чтобы скрыть 0.
  • Работа с данными в сводной таблице.
    В этом случае мы работаем не с ячейками листа, а непосредственно со сводной таблицей.

Первый вариант мы уже подробно рассмотрели выше, поэтому давайте подробнее рассмотрим второй вариант, который также предоставляет две возможности удаления нулей из таблицы:

  • Создание фильтров;
  • Настройка параметров сводной таблицы.
Читайте так же:
Как быстро перевернуть / перевернуть последовательность данных в Excel?

Рассмотрите обе возможности.

Создание и применение фильтра

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

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

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

Как видим, линии с нулевыми значениями скрыты, чего мы и хотели.

Переходим к следующему варианту.

Настройка параметров сводной таблицы

С помощью настройки параметров сводной таблицы мы также можем удалить нули из ячеек. Во вкладке Работа со сводными таблицами выбираем Анализ ->Сводная таблица ->Параметры ->Макет и формат:

Настройка отображения пустых ячеек для сводной таблицы

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

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

Удаление 0 в сводной таблице

Спасибо за внимание!
Если у вас есть вопросы или мысли по теме статьи — обязательно спрашивайте и пишите в комментариях, не стесняйтесь.

Как сделать данные в Excel невидимыми

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

Для того чтобы добиться этого, существует по крайней мере два метода:

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

Во-вторых, вы можете изменить тип данных, сделав их невидимыми. Щелкните правой кнопкой мыши на диапазоне ячеек, которые нужно скрыть, выберите Формат ячеек* вкладку Число* в Числовые форматы: поле выберите (все форматы) из появившегося контекстного меню. В поле Тип: установите три точки с запятой «;;;;» (без кавычек) и нажмите ОК.

В приведенном ниже примере показаны два типа данных: «Основной» — видимый, и «;;;» — невидимый, а также сумма чисел, одинаковая в обоих диапазонах.

Нажмите на изображение для увеличения

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

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

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

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

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

Читайте так же:
Как быстро вставить несколько флажков в Excel?

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

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

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

Нажмите на фотографию, чтобы увеличить ее

Понятно, что второй вариант легче воспринимается.

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

Во-первых, мы можем использовать формулу, основанную на функции ЕСЛИ.

В качестве примера, если мы изначально получаем выручку в ячейке B40 как произведение количества проданных товаров из ячейки B5 и цены товара из ячейки B25 (B40=B5*B25), то следующая формула скроет нулевое значение выручки ячейки B40:

Если в течение периода не было продаж и значение B5=0, то возможное нулевое значение дохода скрывается в двух кавычках.

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

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

Вот как вы можете сделать это в Excel 2010:

Мы скроем нулевые значения в выбранном диапазоне ячеек.

На вкладке Главная → в группе Стили → выбираем команду Условное форматирование → в раскрывающемся списке выбираем Правила выделения ячеекРавно

Щелкните по изображению, чтобы увеличить его.

Выполните следующие шаги:

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

Щелкните на иллюстрации, чтобы увеличить ее.

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

Удалите созданное правило из выбранных ячеек, чтобы восстановить исходное форматирование: Вкладка Главная → группа Стили → команда Условное форматирование → Удалить правила → Удалить правила из выделенных ячеек.

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector