Как удалить скрытые объекты в excel - компьютерный справочник

Скрытия информации

В редакторе есть возможность сделать пустые ячейки без нарушения структуры расчетов. Это связано с особенностями форматирования. Чтобы скрыть содержимое, необходимо перейти к формату ячеек. Нажимаете горячие клавиши Ctrl+1, переходите к последней строке и в поле Тип вводите три точки с запятой и сохраняете кнопкой ОК.

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

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

Жми «Нравится» и получай только лучшие посты в Facebook ↓

Как лучше скрыть лист в Excel

Для наглядности разберем пример. Скройте «Лист3» обычным способом (с помощью опции «Скрыть» в контекстном меню).

А «Лист2» скроем параметрами VBA-редактора макросов. Для этого выполним следующие действия:

Откройте VBA-редактор, нажав комбинацию горячих клавиш Alt+F11.
В окне «Project» выберите «Лист2». А в окне «Properties» найдите свойство «Visible» и выберите ему параметр «2-xlSheetVeryHidden». И закройте окно редактора.
Чтобы показать скрытые листы Excel, щелкните правой кнопкой мышки по ярлычку листа и выберите опцию «Показать».

В списки скрытых листов мы не видим наш «Лист3». Он надежно спрятан. Чтобы снова его увидеть нужно зайти в VBA-редактор и поменять параметры в свойстве «Visible» на «-1-xlSheetVisible».

Примечание. Третий параметр «0-xlSheetHidden» дает тот же результат что и обычное скрытие листа при помощи контекстного меню.

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

С помощью настроек параметров: «Файл»-«Параметры»-«Дополнительно»-«Показывать ярлычки листов».
С помощью расширения горизонтальной прокрутки, как показано на рисунке:

Но и в первом и во втором случаи между листами можно переключатся с помощью комбинации горячих клавиш CTRL+PageUp и CTRL+PageDown.

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

Теперь переходим на вкладку «Сообщение об ошибке».
Выбираем вид отображения:
- «Останов»;
- «Предупреждение»;
- «Сообщение».
Указываем заголовок и текст уведомления.
Для сохранения проверки ввода значений нажмите на «OK»

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

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

Способ 3: выключение фильтра

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

Находим столбец, который отфильтрован по определенному признаку. Активный фильтр обозначается пиктограммой воронки, расположенной рядом с перевернутой стрелкой.
Нажимаем на пиктограмму фильтра и перебегаем в окно с опциями фильтрации. Чтоб показать данные, отмечаем галочкой всякого значение, или избираем верхний пункт “Выделить все”. Потом нажимаем OK.
Опосля отмены фильтра в таблице отобразятся все скрытые элементы.

Если фильтр в таблице больше не нужен, перебегаем на вкладку “Данные”, где жмем клавишу “Фильтр” в разделе инструментов “Сортировка и фильтр”.

Как показать скрытые строки в Excel

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

Процедура включения отображения

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

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

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

Способ 1: размыкание границ

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

Выделяем две смежные ячейки, между которыми находятся скрытые столбцы или строки. Переходим во вкладку «Главная». Кликаем по кнопке «Формат», которая расположена в блоке инструментов «Ячейки». В появившемся списке наводим курсор на пункт «Скрыть или отобразить», который находится в группе «Видимость». Далее в появившемся меню выбираем пункт «Отобразить строки» или «Отобразить столбцы», в зависимости от того, что именно скрыто.

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

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

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

Способ 2: Разгруппировка

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

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

Также можно их отобразить, нажав на последнюю цифру нумерации групп. То есть, если последней цифрой является «2», то жмите на неё, если «3», то кликайте по данной цифре. Конкретное число зависит от того, сколько групп вложено друг в друга. Эти цифры расположены сверху от горизонтальной панели координат или слева от вертикальной.

Группы будут удалены.

Способ 3: снятие фильтра

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

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

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

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

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

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

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

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

Excel: набор значений которые могут быть введены в ячейку ограничен

Выделите все скрытые вами столбцы и строки при помощи активации функции «Выделить все». Для этого вам пригодится комбинация клавиш «Ctrl+A» или клик по пустому прямоугольнику, расположившемся по левую сторону от столбца «А», сразу над строкой «1». После этого вы можете смело приступать к уже знакомой последовательности действий. Нажмите на вкладку «Главная» и в появившемся списке выберите «Ячейки». Здесь нас не интересует ничего, кроме пункта «Формат», поэтому необходимо кликнуть по нему. Если вы сделали все правильно, то должны увидеть на своём экране диалоговое окно, в котором вам необходимо найти позицию «Скрыть или отобразить». Активируйте нужную вам функцию — «Отобразить столбцы» или «Отобразить строки», соответственно.
Сделайте активными все зоны, которые прилегают к области скрытых данных. Щёлкните правой кнопкой мыши по пустой зоне, которую желаете отобразить. На вашем экране появится контекстное меню, в котором вам необходимо активировать команду «Показать». Активируйте её. Если лист содержит слишком много скрытых данных, то опять же имеет смысл воспользоваться комбинацией клавиш «Ctrl+A», которая выделит всю рабочую область Excel.

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

ShiftCtrlПоказать/скрыть примечания

Как скрыть показать ячейки в Excel (Эксель)

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

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

В конце концов, нужно скрыть часть документа, которая в данный момент не нужна для работы и только отвлекает внимание

Как же скрыть и показать ячейки в Excel?

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

1. Способ группировки ячеек

Чтобы скрыть несколько ячеек, к примеру, строк книги, их необходимо сгруппировать и после выполнить процедуру скрытия. Выбираем ячейки, которые нужно скрыть, переходим по вкладке «Данные» — «Структура». Здесь открывается пользователю целый ряд функциональных возможностей по работе с данными. В нашем случае нам нужно перейти по вкладке «Структура» — «Группировать».

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

Для обратного скрытия нажмите еще раз на «-» и строки вновь спрячутся.

2. Способ перетягивания ячеек

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

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

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

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

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

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

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

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

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

5. Скрытие с помощью фильтров

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

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

6. Скрытие содержимого ячеек

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

Попробуем на практике. Выделяем ячейки, которые нужно скрыть, открываем контекстное меню и нажимаем на пункт – «Формат ячеек. ».

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

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

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

Вариант 3: Изменение типа данных в ячейке

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

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

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

Способ 3. Выпадающий список на основе «умной» таблицы Excel.

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

Любой набор значений в таблице может быть таким образом преобразован. Например, A1:A8. Выделите их мышкой. Затем преобразуйте в таблицу, используя меню Главная — Форматировать как таблицу (Home — Format as Table). Укажите, что в первой строке у вас находится название столбца. Это будет «шапка» вашей таблицы. Внешний вид может быть любым: это не более чем внешнее оформление и ни на что больше оно не влияет.

Как уже было сказано выше, «умная» таблица хороша для нас тем, что динамически меняет свои размеры при добавлении в нее информации. Если в строку ниже нее вписать что-либо, то она тут же присоединит к себе её. Таким образом, новые значения можно просто дописывать. К примеру, впишите в A9 слово «кокос», и таблица тут же расширится до 9 строк.

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

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

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

Чтобы использовать «умную таблицу» как источник, нам придется пойти на небольшую хитрость и воспользоваться функцией ДВССЫЛ (INDIRECT в английском варианте). Эта функция преобразует текстовую переменную в обычную ссылку.

Формула теперь будет выглядеть следующим образом:

Таблица5 — имя, автоматически присвоенное «умной таблице». У вас оно может быть другим. На вкладке меню Конструктор (Design) можно изменить стандартное имя на свое (но без пробелов!). По нему мы сможем потом адресоваться к нашей таблице на любом листе книги.

«Продукт» — название нашего первого и единственного столбца, присвоено по его заголовку.

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

Теперь если в A9 вы допишете еще один фрукт (например, кокос), то он тут же автоматически появится и в нашем перечне. Аналогично будет, если мы что-то удалим. Задача автоматического увеличения выпадающего списка значений решена.

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

А вот еще полезная для вас информация:

Группировка

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

Порядок действий следующий:

Выделяете необходимую область

Переходите во вкладку Данные и нажимаете кнопку Группировать.

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

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

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

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

Видео: как в Excel отобразить скрытые ячейки

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

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

Также советуем ознакомиться и с другими материалами по теме:

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

Excel: Как спрятать и показать данные в электронных таблицах

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

Как управлять правилами условного форматирования в Microsoft Excel — Cpab

ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) с кодом 109 в первом аргументе – суммирует все видимые после фильтра и группировки (или скрытия) ячейки. Игнорирует другие подобные функции, которые могут считать внутренние подитоги в исходном диапазоне.

Проверка вводимых значений

Управление правилами условного форматирования

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

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

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

Редактировать правило

Нажмите «Изменить правило», чтобы изменить тип и описание правила. Это поле похоже на окно «Новое правило». Вы можете вносить изменения в правило или в само форматирование. Когда вы закончите, нажмите «ОК», чтобы применить изменения.

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

Удалить правило

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

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

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

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

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

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

Как сделать чтобы excel не считал скрытые ячейки?

СУММ (SUM) – тупо суммирует все в выделенном диапазоне без разбора, т.е. и скрытые строки в том числе. Если хотя бы в одной ячейке есть любая ошибка – перестает считать и тоже выдает ошибку на выходе.