Как сделать сворачиваемые строки в excel
Как в excel сделать разворачивающиеся ячейки?
Очень часто при создании таблиц в Excel хотелось бы использовать сворачиваемые списки. Оказывается, это очень просто!
Итак, как же это сделать?
1. Выделяем строки раздела, которые нужно сгруппировать, кроме заголовка.
3. Если были выделены не целые строки при помощи их заголовков, а просто группа ячеек, то отобразится диалоговое окно, в котором пользователю будет предложено уточнить, что он группирует — строки или столбцы.
Теперь раздел строк можно свернуть или развернуть, нажав на кнопки «+» или «-».
Для отображения строк определенного уровня нужно нажать на кнопки 1, 2 и т.п., соответствующие уровню.
По умолчанию кнопки «+»/ «-» расположены внизу, но чаще всего это неудобно и некрасиво. Поэтому сделаем небольшие изменения и разместим кнопку для группировки сверху.
Для этого в настройках раздела «Структура» в Excel нужно откорректировать расположение итоговых данных и снять флаг «итоги в строках под данными»:
Теперь сгруппированный список выглядит так:
Добавление раскрывающегося списка в таблицу Excel поможет сделать ввод данных более быстрым, предлагая для пользователей список элементов, из которых следует выбрать требуемое вместо того, чтобы выполнять ввод информации каждый раз. Когда вы помещаете такие данные в ячейку электронной таблицы, в ней начинает отображаться стрелка. Вы вводите данные, нажимая на стрелку, после чего выбираете нужную запись. Можно настроить в программе «Эксель» раскрывающийся список в течение нескольких минут, тем самым значительно улучшив скорость ввода данных. Ниже представлено описание этого процесса.
Инструкция для Excel
Откройте таблицу, на странице которой вы желаете создать раскрывающийся список. Создайте перечень пунктов, которые должны быть отражены в списке. Впишите данные в той последовательности, в которой они будут впоследствии открываться. Записи должны быть выполнены в одной и той же строке или столбце, и не должны содержать пустых ячеек.
Чтобы сделать список нужных элементов в отдельном листе, следует щелкнуть ярлычок листа, где вы хотите ввести данные. Выберите тип и затем выделите содержимое, которое появится в списке. Нажмите правой кнопкой мыши по выделенному диапазону ячеек и введите название для диапазона в поле «Имя», после чего выберите «OK». Вы можете защитить или скрыть лист, чтобы другие пользователи не могли вносить изменения в список.
Основы того, как в «Экселе» сделать раскрывающийся список
Нажмите на ячейку, которую вы выбрали для этой цели. Перейдите во вкладку «Данные» в ленте Microsoft Excel. Появится диалоговое окно под названием «Проверка данных». Перейдите во вкладку «Настройки» и нажмите «Список» из меню «Разрешить», расположенного в раскрывающемся списке. Нажмите на кнопку-переключатель в меню «Источник». Выберите список элементов, которые вы хотите включить в ваш раскрывающийся список в «Экселе».
Если вы создали имя диапазона в поле «Источник», введите значок равенства, после чего впишите название диапазона.
Необходимые настройки для создания
Установите или снимите флажок в графе «Игнорировать пустые» в зависимости от того, может ли ячейка, содержащая раскрывающийся список, оставаться пустой.
Перейдите на вкладку «Ввод сообщения», чтобы отобразить окно сообщения, открывающееся при нажатии выпадающего списка. Убедитесь, что флажок напротив пункта меню «Показать входное сообщение при выборе ячейки» установлен, и введите заголовок. Так вы выполните все указания по тому, как в «Экселе» сделать раскрывающийся список.
Перейдите на вкладку «Ошибка оповещения» для отображения сообщения об ошибке, если будут введены неправильные данные. Убедитесь, что предупреждение «Показать ошибку» включено путем установки соответствующего флажка.
Нажмите «OK» для того, чтобы были сохранены критерии проверки и создан раскрывающийся список.
Как в «Экселе» сделать раскрывающийся список – полезные примечания
Если выпадающая запись в списке больше, чем размер клетки, содержащей данный список, вы можете изменить ширину ячейки, чтобы просматривать весь текст.
Чтобы удалить раскрывающейся список, выберите ячейку, содержащую его. Перейдите на вкладку «Данные» в ленте Microsoft Excel. Нажмите кнопку «Проверка данных» из группы «Работа с данными». Перейдите на вкладку «Настройки», нажмите кнопку «Очистить все», а затем — «OK».
Как можно увидеть, руководство о том, как в «Экселе» сделать раскрывающийся список, является достаточно простым. Данная инструкция не требует каких-либо специальных знаний, необходимо лишь на начальном уровне уметь работать с электронными таблицами. Также стоит отметить, что в различных версиях программы «Эксель» пункты меню могут иметь некоторые различия с вышеуказанными, это следует учитывать при работе.
Для таблиц, которые используют постоянные и повторяющиеся данные (например фамилии сотрудников, номенклатура товара или процент скидки для клиента) чтобы не держать в голове и не ошибиться при наборе, существует возможность один раз создать стандартный список и при подстановке данных делать выборку из него. Данная статья позволит вам использовать 4 разных способа как в экселе сделать выпадающий список.
Способ 1 — горячие клавиши и раскрывающийся список в excel
Данный способ использования выпадающего списка по сути не является инструментом таблицы, который надо как либо настраивать или заполнять. Это встроенная функция (горячие клавиши) которая работает всегда. При заполнении какого либо столбца, вы можете нажать правой кнопкой мыши на пустой ячейке и в выпадающем списке выбрать пункт меню «Выбрать из раскрывающегося списка».
Этот же пункт меню можно запустить сочетанием клавиш Alt+»Стрелка вниз» и программа автоматически предложит в выпадающем списке значения ячеек, которые вы ранее заполняли данными. На изображении ниже программа предложила 4 варианта заполнения (дублирующиеся данные Excel не показывает). Единственное условие работы данного инструмента — это между ячейкой, в которую вы вводите данные из списка и самим списком не должно быть пустых ячеек.
Использование горячих клавиш для раскрытия выпадающего списка данных
При чем список для заполнения таким способом работает как в ячейке снизу, так и в ячейке сверху. Для верхней ячейки программа возьмет содержание списка из нижних значений. И опять же не должно быть пустой ячейки между данными и ячейкой для ввода.
Выпадающий список может работать и в верхней части с данными, которые ниже ячейки
Способ 2 — самый удобный, простой и наиболее гибкий
Данный способ предполагает создание отдельных данных для списка. При чем данные могут находится как на листе с таблицей, так и на другом листе файла Excel.
Создание набора данных для списка
Введите имя для набора данных
Создать выпадающий список можно сразу для нескольких ячеек
Для создания проверки вводимых значений введите имя ранее созданного списка
При попытке ввести значение, которого нет в заданном списке, эксель выдаст ошибку.
Кроме списка можно вводить данные вручную. Если введенные данные не совпадут с одним из данных — программа выдаст ошибку
А при нажатии на кнопку выпадающего списка в ячейке вы увидите перечень значений из созданного ранее.
Способ 3 — как в excel сделать выпадающий список с использованием ActiveX
Чтобы воспользоваться этим способом, необходимо чтобы у вас была включена вкладка «РАЗРАБОТЧИК». По умолчанию эта вкладка отсутствует. Чтобы ее включить:
Включение вкладки «РАЗРАБОТЧИК»
Теперь вы сможете воспользоваться инструментом «Поле со списком (Элемент ActiveX)». Во вкладке «РАЗРАБОТЧИК» нажмите на кнопку «Вставить» и найдите в элементах ActiveX кнопку «Поле со списком (Элемент ActiveX)». Нажмите на нее.
Нарисуйте данный объект в excel выпадающий список в ячейке, где вам необходим выпадающий список.
Теперь необходимо настроить данный элемент. Чтобы это сделать, необходимо включить «Режим конструктора» и нажать на кнопку «Свойства». У вас должно открыться окно свойств (Properties).
С открытым окном свойств нажмите на ранее созданный элемент «Поле со списком». В списке свойств очень много параметров для настройки и вы сможете изучив их, настроить очень много начиная от отображения списка до специальных свойств данного объекта.
Но нас на этапе создания интересуют только три основных:
В строке ColumnCount я указал значение 2 и теперь в списке выпадающие данные выглядят вот так:
Как видите получился выпадающий список в excel с подстановкой данных из второго столбца с данными «Поставщик».
Вы можете легко свернуть или развернуть диапазон ячеек на листе Excel, используя функцию «Группировать» на вкладке «Данные». Например, если у Вас присутствует большой объем данных на листе Excel, то можно свернуть некоторое количество данных, связав их в группу, и сделать свой лист более организованным, имея только необходимые данные для отображения на листе в соответствии с Вашими требованиями. Ниже инструкция, которая подскажет Вам как свернуть или развернуть одновременно все сгруппированные ячейки.
Имеем сгруппированные ячейки вида как на рисунке 1.
Как видно на рисунке у нас сгруппированы выделенные цветом диапазоны ячеек.
Для отображения лишь необходимой для нас информации (в данном примере нам интересны суммы) мы можем свернуть одновременно все сгруппированные ячейки нажатием левой кнопкой мышки на цифры «1, 2» находящийся в левой части экрана, как показано на рисунке 2. Цифра «» сворачивает все сгруппированные ячейки, цифра «» же, наоборот, разворачивает.
Таким образом Вы можете управлять для Вас отображаемой информацией на Вашем листе Excel, делая просмотр более удобным. Так же Вы можете управлять и сгруппированными ячейками по вертикали.
Как сделать сворачивающийся список в excel?
Очень часто при создании таблиц в Excel хотелось бы использовать сворачиваемые списки. Оказывается, это очень просто!
Итак, как же это сделать?
1. Выделяем строки раздела, которые нужно сгруппировать, кроме заголовка.
3. Если были выделены не целые строки при помощи их заголовков, а просто группа ячеек, то отобразится диалоговое окно, в котором пользователю будет предложено уточнить, что он группирует — строки или столбцы.
Теперь раздел строк можно свернуть или развернуть, нажав на кнопки «+» или «-».
Для отображения строк определенного уровня нужно нажать на кнопки 1, 2 и т.п., соответствующие уровню.
По умолчанию кнопки «+»/ «-» расположены внизу, но чаще всего это неудобно и некрасиво. Поэтому сделаем небольшие изменения и разместим кнопку для группировки сверху.
Для этого в настройках раздела «Структура» в Excel нужно откорректировать расположение итоговых данных и снять флаг «итоги в строках под данными»:
Теперь сгруппированный список выглядит так:
Группирование данных в Excel
18.03.2009 Автор: Алексей Лупан
Можно сворачивать/разворачивать строки на листе Excel.
Если строк много, то фича рулит.
Группированные строки в Excel
Как сгруппировать строки
Официальная справка (очень рекомендую ознакомиться):
Дело очень простое, как настройки маршрутизатора под Gentoo:
Но придумать себе проблему на эту тему все еще можно:
Как, епрст, сделать так, чтобы «плюсики» отображались вверху, а не внизу скрываемо/раскрываемого списка?
Office ХР
Данные > Группа и Структура > Настройка > убрать галку у опции «Итоги в строках под данными»
Office 2007
en
Data > Outline > Show the Outline dialog box > Settings > uncheck «Summary rows below detail»
ru
Данные > Структура > Показать меню (см. на картинке, куда надо нажать) > Настройки > убрать галку у опции «Итоги в строках под данными»
Смотри, куда жмакаешь
Раскрываемость списков неизменно и существенно повышается.
Странность в том, что для каждого листа эту операцию следует повторять… Разве что — использовать макрос, который будет это делать одним нажатием:
‘ Товарищ, это макрос для Office 2007
With ActiveSheet.Outline
.AutomaticStyles = False
.SummaryRow = xlAbove
.SummaryColumn = xlRight
End With
End Sub
А кто не знает, как использовать макросы в офисном пакете, тот просто нам завидует.
Плавная привязка записи к теме тестирования:
Тест-кейсы в Excel забабахать может каждый бабуин, а как именно решить проблему с «поменять плюсики местами» — увы. Чтобы потом не сталкиваться с этой проблемой, сделал себе памятку в блоге. Всё это, конечно, неразрывно связано с тестированием…
То есть, не было никаких разрывов с ноября прошлого года.
Прием комментариев к этой записи завершён.
Тут нам не то, и не это. Не надо задавать вопросы о том, как сделать ещё что-то с этими прекрасными свертывающимися списками. Здесь даже не форум по Excel. Это блог о тестировании программного обеспечения.
Вы же любите тестировать, правда?
Опубликовано в Инструменты, Откровения, Радости, Скриншоты, LibreOffice | Отмечено Бабуин, Группирование данных, Excel, LibreOffice | 42 комментария
Под выпадающим списком понимается содержание в одной ячейке нескольких значений. Когда пользователь щелкает по стрелочке справа, появляется определенный перечень. Можно выбрать конкретное.
Очень удобный инструмент Excel для проверки введенных данных. Повысить комфорт работы с данными позволяют возможности выпадающих списков: подстановка данных, отображение данных другого листа или файла, наличие функции поиска и зависимости.
Создание раскрывающегося списка
Путь: меню «Данные» — инструмент «Проверка данных» — вкладка «Параметры». Тип данных – «Список».
Ввести значения, из которых будет складываться выпадающий список, можно разными способами:
Любой из вариантов даст такой результат.
Выпадающий список в Excel с подстановкой данных
Необходимо сделать раскрывающийся список со значениями из динамического диапазона. Если вносятся изменения в имеющийся диапазон (добавляются или удаляются данные), они автоматически отражаются в раскрывающемся списке.
Протестируем. Вот наша таблица со списком на одном листе:
Добавим в таблицу новое значение «елка».
Теперь удалим значение «береза».
Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.
Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавлялись в диапазон.
Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».
Нажмем «Да» и добавиться еще одна строка со значением «баобаб».
Выпадающий список в Excel с данными с другого листа/файла
Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.
Имя файла, из которого берется информация для списка, заключено в квадратные скобки. Этот файл должен быть открыт. Если книга с нужными значениями находится в другой папке, нужно указывать путь полностью.
Как сделать зависимые выпадающие списки
Возьмем три именованных диапазона:
Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.
Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.
Выпадающий список с поиском
Скачать пример выпадающего списка
При вводе первых букв с клавиатуры высвечиваются подходящие элементы. И это далеко не все приятные моменты данного инструмента. Здесь можно настраивать визуальное представление информации, указывать в качестве источника сразу два столбца.
Развертывание, свертывание и отображение сведений в сводной таблице или сводной диаграмме
Сводную таблицу и сводную диаграмму можно развернуть и свернуть до любого уровня детализации данных; можно даже свернуть или развернуть все уровни детализации за одну операцию. Можно также развернуть или свернуть данные за следующим уровнем. Например, начиная с уровня страны или региона можно развернуть отчет до уровня городов, что приведет к развертыванию уровней областей или краев и городов. Это может сэкономить время при работе с множеством уровней детализации. Кроме того, можно развернуть или свернуть все элементы каждого поля в источнике данных OLAP.
Также можно просмотреть сведения, используемые для объединения значений в поле значений.
Сводную таблицу и сводную диаграмму можно развернуть и свернуть до любого уровня детализации данных; можно даже свернуть или развернуть все уровни детализации за одну операцию. Можно также развернуть или свернуть данные за следующим уровнем. Например, начиная с уровня страны или региона можно развернуть отчет до уровня городов, что приведет к развертыванию уровней областей или краев и городов. Это может сэкономить время при работе с множеством уровней детализации. Кроме того, можно развернуть или свернуть все элементы каждого поля в источнике данных OLAP.
Развертывание и свертывание уровней в сводной таблице
В сводной таблице выполните одно из указанных ниже действий.
Нажмите кнопку развертывания или свертывания для элемента, который нужно развернуть или свернуть.
Примечание: Если кнопки развертывания и свертывания не отображаются, см. раздел Отображение и скрытие кнопок развертывания и свертывания в сводной таблице в этой статье.
Дважды щелкните элемент, который нужно развернуть или свернуть.
Щелкните правой кнопкой мыши элемент, выберите команду Развернуть/свернуть и выполните одно из следующих действий.
Чтобы просмотреть сведения о текущем элементе, щелкните пункт Развернуть.
Чтобы скрыть сведения о текущем элементе, щелкните пункт Свернуть.
Чтобы скрыть сведения обо всех элементах в поле, щелкните пункт Свернуть все поле.
Чтобы просмотреть сведения обо всех элементах в поле, щелкните пункт Развернуть все поле.
Чтобы просмотреть данные за следующим уровнем детализации, щелкните пункт Развернуть до » «.
Чтобы скрыть данные за следующим уровнем детализации, щелкните пункт Скрыть до » «.
Развертывание и свертывание уровней в сводной диаграмме
В сводной диаграмме щелкните правой кнопкой мыши подпись категории, для которой нужно отобразить или скрыть сведения уровня, выберите команду Развернуть или свернуть и выполните одно из следующих действий.
Чтобы просмотреть сведения о текущем элементе, щелкните пункт Развернуть.
Чтобы скрыть сведения о текущем элементе, щелкните пункт Свернуть.
Чтобы скрыть сведения обо всех элементах в поле, щелкните пункт Свернуть все поле.
Чтобы просмотреть сведения обо всех элементах в поле, щелкните пункт Развернуть все поле.
Чтобы просмотреть данные за следующим уровнем детализации, щелкните пункт Развернуть до » «.
Чтобы скрыть данные за следующим уровнем детализации, щелкните пункт Скрыть до » «.
Отображение и скрытие кнопок развертывания и свертывания в сводной таблице
По умолчанию кнопки развертывания и свертывания отображаются, но их можно скрыть (например, при печати отчета). Чтобы можно было использовать эти кнопки для свертывания и развертывания уровней детализации отчета, они должны быть отображены.
В Excel 2016 и Excel 2013: на вкладке Анализ в группе Показать щелкните элемент Кнопки +/-, чтобы отобразить или скрыть кнопки свертывания и развертывания.
В Excel 2010: на вкладке Параметры в группе Показать щелкните элемент Кнопки +/-, чтобы отобразить или скрыть кнопки свертывания и развертывания.
В Excel 2007: на вкладке Параметры в группе Показать или скрыть щелкните элемент Кнопки +/-, чтобы отобразить или скрыть кнопки свертывания и развертывания.
Примечание: Кнопки развертывания и свертывания доступны только для полей, в которых есть данные для детализации.
Отображение и скрытие сведений для поля значений в отчете сводной таблицы
По умолчанию отображение сведений для поля значений в сводной таблице включено. Чтобы защитить эти данные от просмотра другими пользователями, их отображение можно отключить.
Отображение сведений поля значений
В сводной таблице выполните одно из указанных ниже действий.
Щелкните правой кнопкой мыши поле в области значений сводной таблицы и выберите команду Показать детали.
Дважды щелкните поле в области значений сводной таблицы.
Данные, на которых основано поле значений, будут помещены на новый лист.
Скрытие сведений поля значений
Щелкните правой кнопкой мыши ярлычок листа с данными поля значений и выберите команду Скрыть или Удалить.
Отключение и включение параметра отображения сведений поля значений
Щелкните в любом месте сводной таблицы.
На вкладке Параметры или Анализ (в зависимости от используемой версии Excel) ленты в группе Сводная таблица нажмите кнопку Параметры.
В диалоговом окне Параметры сводной таблицы откройте вкладку Данные.
В разделе Данные сводной таблицы снимите или установите флажок Разрешить отображение деталей, чтобы отключить или включить этот параметр.
Примечание: Этот параметр недоступен для источника данных OLAP.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.