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

В некоторых случаях необходимо сделать данные, в каких либо ячейках невидимыми, не удаляя их. Например, промежуточные результаты вычислений, нулевые значения, значение параметра ИСТИНА или ЛОЖЬ и т.д.

Для этого можно воспользоваться, как минимум, двумя способами:

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

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

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

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

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

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

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

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

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

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

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

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

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

Так, например, если у нас, изначально, величина выручки в ячейке В40 получается, как произведение количества проданного товара из ячейки В5 на цену товара из ячейки В25 (В40=В5*В25), то формула для скрытия нулевого значения выручки в ячейке В40 будет выглядеть следующим образом:

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

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

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

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

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

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

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

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

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

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

[expert_bq id=»1570″]Кроме того, можно воспользоваться клавишей End, чтобы быстро расширить выделение до последней непустой ячейки в строке или столбце. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Если выделенная ячейка находится внутри таблицы (созданной с помощью команды Вставка —> Таблицы —> Таблица), то при нажатии Ctrl+A будут выделены только данные. Нажмите Ctrl+A еще раз, чтобы выделить строку заголовка и строку итогов.

Ячейки в Excel

Теперь нам осталось выделить последний интервал величин – более 500000. Тут нам тоже повезло, так как все числа более 500000 находятся в интервале от 500000 до 600000. Поэтому в поле поиска вводим выражение «5. » и жмем на кнопку «Найти все». Если бы были величины, превышающие 600000, то нам бы пришлось дополнительно производить поиск для выражения «6. » и т.д.

Как скрыть и показать столбцы и строки в Excel

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

Приведенные ниже инструкции охватывают три способа скрытия и отображения столбцов и строк на листе Excel:

Эти инструкции относятся к Excel 2019, 2016, 2013, 2010, 2007 и Excel для Office 365.

Использование данных в скрытых столбцах и строках

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

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

1. Нажмите на ячейку в столбце, который вы хотите скрыть, чтобы сделать ее активной.
2. Нажмите и удерживайте клавишу Ctrl на клавиатуре.
3. Нажмите и отпустите клавишу 0 , не отпуская клавишу Ctrl .
4. Столбец, содержащий активную ячейку, должен быть скрыт от просмотра.

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

Скрыть столбцы с помощью контекстного меню

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

Скрыть одну колонку

1. Нажмите заголовок столбца столбца, который хотите скрыть, чтобы выбрать весь столбец.
2. Нажмите правой кнопкой мыши в выбранном столбце, чтобы открыть контекстное меню.
3. Выберите Скрыть .
4. Выбранный столбец, буква столбца и любые данные в столбце будут скрыты от просмотра.

Скрыть соседние столбцы

1. В заголовке столбца нажмите и перетащите указателем мыши, чтобы выделить все три столбца.
2. Нажмите правой кнопкой мыши на выбранных столбцах.
3. Выберите Скрыть .
4. Выбранные столбцы и буквы столбцов будут скрыты от просмотра.

Скрыть отдельные столбцы

1. В заголовке столбца нажмите на первом столбце, который нужно скрыть.
2. Нажмите и удерживайте клавишу Ctrl на клавиатуре.
3. Продолжайте удерживать нажатой клавишу Ctrl и нажмите один раз на каждый дополнительный столбец, чтобы скрыть его, чтобы выбрать их.
4. Отпустите клавишу Ctrl .
5. В заголовке столбца нажмите правой кнопкой мыши на одном из выбранных столбцов.
6. Выберите Скрыть .
7. Выбранные столбцы и буквы столбцов будут скрыты от просмотра.

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

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

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

1. Введите ячейкуссылкуA1 в поле Имени .
2. Нажмите клавишу Enter на клавиатуре, чтобы выбрать скрытый столбец.
3. Нажмите на Главнаявкладку на ленте .
4. Нажмите Форматзначок на ленте , чтобы открыть раскрывающийся список.
5. В разделе «Видимость» меню выберите Скрыть и отобразить >Скрыть столбцы или Показать столбец.

Показать столбцы с помощью сочетания клавиш

1. Введите ячейкуссылкуA1 в поле Имени .
2. Нажмите клавишу Enter на клавиатуре, чтобы выбрать скрытый столбец.
3. Нажмите и удерживайте клавиши Ctrl и Shift на клавиатуре.
4. Нажмите и отпустите клавишу 0 , не отпуская клавиши , Ctrl и Shift .

Чтобы отобразить один или несколько столбцов, выделите по крайней мере одну ячейку в столбцах по обе стороны от скрытых столбцов с помощью указателя мыши. Например, вы хотите показать столбцы B, D и F :

1. Чтобы отобразить все столбцы, нажмите и перетащите с помощью мыши, чтобы выделить столбцы от A до G .
2. Нажмите и удерживайте клавиши Ctrl и Shift на клавиатуре.
3. Нажмите и отпустите клавишу 0 , не отпуская клавиши Ctrl и Shift .
4. Скрытые столбцы будут видны.

Сочетание клавиш Ctrl + Shift + 0 может не работать в зависимости от используемой версии Windows по причинам, не объясненным Microsoft. Если этот ярлык не работает, используйте другой метод из статьи.

Показать столбцы с помощью контекстного меню

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

1. Наведите указатель мыши на столбец C в заголовке столбца.
2. Нажмите и перетащите с помощью мыши, чтобы выделить столбцы от C до H , чтобы отобразить все столбцы одновременно.
3. Нажмите правой кнопкой мыши на выбранных столбцах.
4. Выберите Показать .
5. Скрытые столбцы будут видны.

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

1. Нажмите на ячейку в строке, которую вы хотите скрыть, чтобы сделать ее активной.
2. Нажмите и удерживайте клавишу Ctrl на клавиатуре.
3. Нажмите и отпустите клавишу 9 , не отпуская клавишу Ctrl .
4. Строка, содержащая активную ячейку, должна быть скрыта от глаз.

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

Скрыть строки с помощью контекстного меню

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

Скрыть одну строку

1. Нажмите на заголовок строки, которая будет скрыта, чтобы выделить всю строку.
2. Нажмите правой кнопкой мыши на выбранной строке, чтобы открыть контекстное меню.
3. Выберите Скрыть .
4. Выбранная строка, буква строки и любые данные в строке будут скрыты от просмотра.

Скрыть соседние строки

1. В заголовке строки щелкните и перетащите указателем мыши, чтобы выделить все три строки.
2. Нажмите правой кнопкой мыши на выбранных строках.
3. Выберите Скрыть.
4. Выбранные строки будут скрыты от просмотра.

Скрыть отдельные строки

1. В заголовке строки нажмите на первую строку, которая будет скрыта.
2. Нажмите и удерживайте , удерживая клавишу Ctrl на клавиатуре.
3. Продолжайте удерживать нажатой клавишу Ctrl и нажмите один раз на каждую дополнительную строку, чтобы скрыть ее, чтобы выделить их.
4. Нажмите правой кнопкой мыши на одной из выбранных строк.
5. Выберите Скрыть .
6. Выбранные строки будут скрыты от просмотра.

Скрыть и показать строки с помощью поля имени

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

1. Введите ячейкуссылкуA1 в поле Имя .
2. Нажмите клавишу Enter на клавиатуре, чтобы выбрать скрытый ряд.
3. Нажмите на вкладку “Главная” на ленте .
4. Нажмите Форматзначок на ленте , чтобы открыть раскрывающееся меню.
5. В разделе «Видимость» меню выберите Скрыть и показать >Скрыть строки или Показать строку.

Показать строки с помощью сочетания клавиш

Показать строки с помощью сочетаний клавиш и поля имени

1. Введите ссылку на ячейку A1 в поле Имя .
2. Нажмите клавишу Enter на клавиатуре, чтобы выбрать скрытый ряд.
3. Нажмите и удерживайте клавиши Ctrl и Shift на клавиатуре.
4. Нажмите и отпустите цифровую клавишу 9 , не отпуская клавиши Ctrl и Shift .
5. Строка 1 станет видимой.

Показать строки с помощью сочетания клавиш

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

Например, вы хотите отобразить строки 2, 4, и 6 .

1. Чтобы отобразить все строки, нажмите и перетащите с помощью мыши, чтобы выделить строки с 1 по 7 .
2. Нажмите и удерживайте клавиши Ctrl и Shift на клавиатуре.
3. Нажмите и отпустите цифровую клавишу 9 , не отпуская клавиши Ctrl и Shift .
4. Скрытый ряд (ы) станет видимым.

Показать строки с помощью контекстного меню

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

[expert_bq id=»1570″]Чтобы отобразить один или несколько столбцов, выделите по крайней мере одну ячейку в столбцах по обе стороны от скрытых столбцов с помощью указателя мыши. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Заходим в меню Файл → элемент Параметры → пункт Допомлнительно → раздел показать параметры для следующего листа. Здесь выбираем параметр Показывать нули в ячейках, которые содержат нулевые значения, и убираем галочку. Теперь нулевые значения не будут отображаться на листе для которого установлен этот параметр.

Как скрыть и показать столбцы и строки в Excel

1. В заголовке столбца нажмите и перетащите указателем мыши, чтобы выделить все три столбца.
2. Нажмите правой кнопкой мыши на выбранных столбцах.
3. Выберите Скрыть .
4. Выбранные столбцы и буквы столбцов будут скрыты от просмотра.

После этого в нижней части окошка открываются результаты поисковой выдачи. Кликаем левой кнопкой мыши по любому из них. Затем набираем комбинацию клавиш Ctrl+A. После этого выделяются все результаты поисковой выдачи и одновременно выделяются элементы в столбце, на которые данные результаты ссылаются.

Формат ячеек в Excel

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

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

Если этих кнопок не достаточно, то можно обратиться к Формату ячеек в контекстном меню (через правую кнопку мыши), либо с помощью сочетания горячих клавиш Ctrl+1, что существенно быстрее. Откроется известное многим окошко.

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

Вкладка Число

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

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

Вкладка Выравнивание

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

Расположение и назначение команд, как и в других окнах Excel, интуитивно понятно. Достаточно прочитать названия. Тут выравнивание по вертикали, тут – горизонтали. Можно объединить несколько ячеек в одну или длинное название переносить по словам. Обращу лишь внимание на пару интересных моментов.

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

Читать такое не удобно (все сливается и нужно присматриваться). Поэтому правильно сделать отступ текста от левого края. Для этого достаточно выбрать в списке по горизонтали пункт по левому краю (отступ) и установить рядом в окошке значение отступа. См. рисунок ниже.

Аналогичным образом можно отлепить число от правого края.

И еще один полезный совет. При создании таблицы в шапке часто объединяют ячейки, чтобы общее название «накрывало» сразу несколько столбцов. К примеру, как на картинке ниже.

Получится тот же эффект, только ячейки не будут объединены.

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

Значительная часть команд из вкладки Выравнивание также вынесена на ленту.

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

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

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

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

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

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

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

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

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

[expert_bq id=»1570″]Табличная часть сформатирована, теперь заполним ее текстовым значением заказ в смешанном порядке клиентов для текущего и прошлых месяцев. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] В тоже время для вычисления значения третьего аргумента (смещение по столбцам) используем вложенную формулу МЕСЯЦ(СЕГОДНЯ()), Которая в соответствии с условиями возвращает номер текущего месяца в текущем году. От вычисленного формулой номера месяца отнимаем число 4, то есть в случаи Ноября получаем смещение на 8 столбцов. А, например, для Июня – только на 2 столбца.

Точность округления как на экране в Microsoft Excel. Оптимизируем работу с группой файлов Excel

Последнее два аргумента для функции СМЕЩ определяют высоту (в количестве строк) и ширину (в количестве столбцов) возвращаемого диапазона. В нашем примере – это область ячеек с высотой на 1-ну строку и шириной на 4 столбца. Этот диапазон охватывает столбцы 3-х предыдущих месяцев и текущий.