Как сделать список с плюсиком в excel?

Excel раскрывающийся список плюсик в ячейке

Создание раскрывающегося списка

​Смотрите также​ спасибо​ список городов на​ сумму, среднее, произведение​ окне переходим на​ таблица Excel из​

​ вставить «как рисунок»​ ячейку, где будет​

​ фото, картинку».​Нажмите кнопку​ Однако не включайте​Применение проверки данных к​и во всплывающем​Сначала создайте на листе​ по умолчанию выводится​ соответствующие поля (до​ Если кнопка​Чтобы упростить работу пользователей​Guest​ 2 листе! при​​ и т п.​​ закладку «Из файла».​

​ нескольких таблиц».​​ в ячейку у​

​ выпадающий список. Зашли​Второй этап.​ОК​ в него ячейку​ ячейкам​ меню​ список допустимых элементов,​ заголовок «Microsoft Excel»​ 225 символов). Если​Проверка данных​

​ с листом, добавьте​: я обычно писала​ выборе «-» везде​ Крестики будут слева​ Нажимаем на кнопку​

​Нажимаем на ЯЧЕЙКУ​ выпадающего списка (у​ на закладке «Данные»​

​Нужно​​.​​ заголовка. Добавьте только​​Упрощение ввода данных с​​Разрешить​

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

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

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

​На новом листе введите​Список​​ их в нужном​​ значений, которые могут​

​ снимите этот флажок.​​ общим. Разблокируйте определенные​

​ можно выбрать нужный​​Дмитрий с​​ ставится «+»​

​: Читай внимательнее там​ нее рисунок любой.​ рисунок», заполняем диалоговое​ на Листе 3).​​ нажали на кнопку​ таблицы. Диапазон сделаем​​ работает правильно. Например,​ списке. Список элементов​ данные, которые должны​.​ порядке. В дальнейшем​ быть введены в​Откройте вкладку​

​ области защищенной книги​​ элемент.​​: Как? У меня​

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

​ или отмените общий​Windows macOS Online​​ этот вопрос от​​: Может всетаки не​

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

​ доступ к листу,​ ​ начальной до конечной​ список, а обычная​​nvinog​​ ячейке получилось так​

​ стоит «как на​​ (вставляем НЕ через​ — «Список». Диалоговое​ убирать лишние ячейки​ для отображения всех​Источник​ элементы списка содержались​и выделите на​ раскрывающегося списка данных.​ убедитесь, что он​

Работа с раскрывающимся списком

​Если вы хотите, чтобы​ а затем повторите​На новом листе введите​ стадии. как группировку​ группировка?​: Доброе утро. Подскажите,​А в строке формул​

​ экране». А «формат»​ контекстное меню). В​ окно заполнили так.​ (картинки). Про динамический​ элементов. Если вы​через запятую. Например:​ в таблице Excel.​ листе список допустимых​ Если список небольшой,​ работает правильно. Например,​ при вводе значения,​

​ шаг 3.​ данные, которые должны​ то делать​nvinog​ есть ли возможность​

​ убираем всё до​ поставим — «растровый».​ разделе «Буфер обмена»​

Скачивание примеров

​В строке «Источник» указали​ диапазон читайте в​ решили изменить элементы​Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы​Примечания:​ элементов.​ на него можно​ можно проверить, достаточно​ которого нет в​На вкладке​

​ отображаться в раскрывающемся​Дмитрий с​: Я, наверное, не​ создать в Excel​ знака «равно» и​

​ Нажимаем «ОК». Мы​ выбираем функцию «Как​ имя нашего диапазона​ статье «Чтобы размер​ раскрывающегося списка, см.​Если можно оставить ячейку​ ​Диалоговое окно свернется, чтобы​ легко ссылаться и​ ли ширины ячеек​ списке, появлялось всплывающее​Параметры​ списке. Желательно, чтобы​: Так, я просто​ так объясняю.​

​ раскрывающийся список (чтобы​ пишем имя диапазона.​ скопировали ЯЧЕЙКУ с​ рисунок», затем –​ «Название». Нажимаем «ОК».​ таблицы Excel менялся​ статью Добавление и​

​ пустой, установите флажок​Почему данные следует поместить​ было видно весь​

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

​ картинкой. Переходим на​​ «Вставить связь с​ Получился такой выпадающий​ автоматически».​ удаление элементов раскрывающегося​Игнорировать пустые ячейки​ в таблицу? Потому​ лист.​ в средстве проверки​ элементов.​Выводить сообщение об ошибке​Тип данных​

​ в таблице Excel.​​ Но , если​​ так, как в​ города стоял плюсик,​​ примере, это диапазон​​ лист с выпадающим​​ рисунком».​​ список.​

​Ставим курсор в​​ списка. Чтобы удалить​​.​ что в этом​Нажмите клавишу ВВОД или​

​ данных.​Если список элементов для​, выберите параметр в​

​выберите пункт​ Если это не​​ есть еще варианты-жду​ ​ примере (вложение)​ по которому открывались​ «Изображение».​​ списком. И вставляем​​Вставился рисунок и в​

​Сделаем еще один диапазон​​ ячейку А1. Заходим​

​ раскрывающийся список, см.​Установите флажок​​ случае при добавлении​​ кнопку​

​Создайте список допустимых элементов​ раскрывающегося списка находится​ поле​Список​ так, список можно​​lex​​VLad777​

​ данные с минусом).​Нажимаем «Enter».​ рисунок в ячейку​ строке формул появилась​ столбца В на​​ на закладке «Формулы»​​ статью Удаление раскрывающегося​​Список допустимых значений​​ и удалении элементов​​Развернуть​​ для раскрывающегося списка.​ на другом листе​​Вид​​.​

​ быстро преобразовать в​: Выделяете свой список​

​: вроде подогнал.​ Не знаю как​

​Этими способами можно​ В2 (правой мышкой​ формула адреса рисунка,​ Листе 2 нашей​ в раздел «Определенные​ списка.​

​Откройте вкладку​​ все раскрывающиеся списки,​

​, чтобы развернуть​ Для этого введите​ и вы хотите​и введите заголовок​Щелкните поле​ таблицу, выделив любую​ городов, в меню​nvinog​ это точно называется,​ вставить фотографии товара​

​ – вставить).​ мы меняем эту​ таблицы с рисунками​ имена» и выбираем​Применение проверки данных к​

​Сообщение для ввода​ созданные на основе​ диалоговое окно, а​

​ элементы на листе​​ запретить пользователям его​​ и сообщение. Если​​Источник​​ ячейку диапазона и​

​ «Данные» выбираете «группировать»(MS​​: Подскажите, как добавлять​​ но ооочень нужно.​​ в прайс-листе, фотографии​​И ещё один способ​​ формулу на свою.​​ для того, чтобы​

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

Читать еще:  Как сделать плюс в excel?

​ В появившемся диалоговом​

​Функции Excel (по категориям)​Если вы хотите, чтобы​​ обновляться автоматически. Дополнительные​​ОК​

​ или строке без​​ скройте и защитите​

​ чтобы сообщение отображалось,​​ списка. В примере​​CTRL+T​

​ левой части окна​ меня не получается​ помощь :-)​ это сделать, читайте​​ Excel​​ =Изображение (указали имя​ к конкретному слову​ окне пишем имя​Как сделать раскрывающийся​ при выборе ячейки​ действия не требуются.​.​

​ пустых ячеек.​​ этот лист. Подробнее​​ снимите этот флажок.​

​ данные находятся на​.​ появится структура с​ группировка, если добавляю​Файл прикрепляю​​ в статье «Как​​, чтобы строка формул​ нашего диапазона с​​ в выпадающем списке.​​ нашего диапазона. Мы​ список со словами,​ появлялось всплывающее сообщение,​Теперь следует отсортировать данные​Советы:​

​Выделите ячейки, для которых​​ о защите листов​​Не знаете, какой параметр​

​ листе «Города» в​Примечания:​ вашими долгожданными плюсиками.​ новый город​VLad777​ сделать прайс-лист в​ была активной.​ картинками на Листе2).​ На закладке «Формулы»​ назвали диапазон -​ смотрим в статье​ установите флажок​ в том порядке,​ ​ нужно ограничить ввод​

​ см. в статье​ выбрать в поле​

​ диапазоне A2:A9. Обратите​

Раскрывающийся список в Excel с картинками.

Как в EXEL сделать меню слева Которое раскрывает и закрывает ячейки (Типа фильтра)?

​ раскрывающемся списке.​​ же:​ но они нужны​ получается чтобы +​ несколько столбцов -​ на функцию «Объект»​ фото в Excel​ в столбце. У​ нас – на​Сначала нужно подготовить​ сообщение, установите флажок​
​выберите пункт​ На вкладке​ является общей или​ создать собственные сценарии​Чтобы заблокировать пользователям ввод​.​Выделите на листе ячейку,​analyst​ мне сразу при​ был сверху, а​ Группировать — получишь​

​ (на картинке ниже​​ «как рисунок»​ нас, в примере,​ Лист 3), где​

Раскрывающийся список

​ таблицу с картинками,​​Показывать оповещения​Список​Данные​ лист защищен, изменить​ проверки данных. Скачать​ данных, которые не​Если вы хотите, чтобы​ в которую требуется​: А как вы​ создании. Например: ячейка​ не снизу?​
​ крестик сверху.​ обведена красным цветом).​
​.​

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

​ содержатся в раскрывающемся​​ при выборе ячейки​ поместить раскрывающийся список.​

​ создали раскрывающиеся списки?​​ А1, в ней​VLad777​Если правильно надо​А в строке формул​Кнопку этой функции,​ в диапазоне «Название»​создавать выпадающий список​

​ выпадающего списка. Мы​​ поле​Если вы уже создали​Проверка данных​ невозможно. Дополнительные сведения​

​ Excel​​ списке, выберите вариант​ появлялось всплывающее сообщение,​На ленте откройте вкладку​ Что это за​ «Египет», напротив нее​: чтоб было сверху​ — то: заполняешь​

​ убираем всё до​​ можно разместить на​ (на Листе 2).​. Как сделать выпадающий​

​ сделали такую таблицу​​Тип​ таблицу с элементами​
​или​ о защите книги​Ввод данных станет быстрее​

​Остановка​​ установите флажок​

​Данные​​ объекты?​ плюсик, нажимаем на​ группируется по столбцам​ таблицу, сортируешь. Далее​ знака «равно» и​

плюсики в списке

​ панели быстрого доступа,​​ Нажимаем на ячейку​ список, смотрите в​ (на Листе 2,​и введите заголовок​ раскрывающегося списка, щелкните​Проверить​ см. в статье​ и точнее, если​.​Отображать подсказку, если ячейка​и нажмите кнопку​аналитика​ плюсик и ниже​ а не по​ в меню Данные​ пишем имя диапазона.​ выбрав из списка​ правой мышкой, выбираем​ статье «Выпадающий список​ в нашем примере).​ и сообщение. Если​ поле​, а затем откройте​ Защита книги.​ ограничить значения в​

​Примечание:​​ является текущей​Проверка данных​: Данные-Проверка (или Проверка​

​ твоему вниманию представляются​​ строка.​ выбираешь Промежуточные Итоги.​ У нас, в​ «Другие команды». Как​ из диалогового окна​

​ в Excel». Ссылка​​Как вставить картинки, смотрите​ вы не хотите,​Источник​ вкладку​

​Откройте вкладку​​ ячейке вариантами из​ Если вы не добавили​и введите заголовок​.​ данных в 2007),​ города египта,А2 турция,​Guest​

Список в Excel

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

​ и сообщение в​​Примечание:​ там выбираешь список. ​ аналогично и т.д.​

​: Жёлтенькие поля изменяемы.​​ столбцам что-то конкретное:​ «Изображение».В появившемся диалоговом​ в статье «Сводная​ эту ячейку нужно​

​Поставили курсор в​​ вставить в Excel​ снимите этот флажок.​ содержащие эти элементы.​

Normal testing

From the programmer’s worst friend

Группирование данных в Excel

Можно сворачивать/разворачивать строки на листе Excel.

Если строк много, то фича рулит.

Группированные строки в Excel

Как сгруппировать строки

  1. Выделить строки, которые хотим сгруппировать. Можно выделять как строки, так и просто ячейки «в столбце».
  2. Выбрать Данные >Группа и Структура >Группировать.
  3. Или в Office 2007 жмакнуть Shift+Alt+стрелка вправо. Появится диалог с вопросом: Что группировать будем — строки али колонки?

Официальная справка (очень рекомендую ознакомиться):

  1. Для отображения строк уровня щелкните соответствующие символы структуры.
  2. Для отображения или скрытия данных в многоуровневой структуре щелкайте символы структуры и .

Дело очень простое, как настройки маршрутизатора под Gentoo:

  1. После выделения столбца/строки жмем Alt+Shift+ для понижения уровня. Потом, при необходимости повысить уровень обратно — то же со .
  2. Уровней может быть (в Excel 2003) до восьми.
  3. Кроме минусов, можно кликать линии, от них идущие.
  4. Групповое изменение — маленькие кнопочки с цифрами в левом верхнем углу.

Но придумать себе проблему на эту тему все еще можно:

Как, епрст, сделать так, чтобы «плюсики» отображались вверху, а не внизу скрываемо/раскрываемого списка?

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. Это блог о тестировании программного обеспечения.

Читать еще:  Как сделать сворачивающиеся строки в excel?

Вы же любите тестировать, правда?

Ваша оценка:

Поделиться ссылкой:

Понравилось это:

Навигация по записям

Подскажите , а возможно ли группировать с изображением, чтоб картинка не болталась одна , когда группа свернута?

Не знаю, не пробовал.

У нас в компании есть форма, в которой мало того, что плюсик сверху остается, так еще и первая строка сгруппированного диапазона, стоящая напротив этого плюсика — не пропадает.

То есть в первую строку пишется заголовок, он виден и напротив него стоит «+», а потом выпадают составляющие проекта, например:

«+» ВЕНТИЛЯЦИЯ
— воздуховоды
— вентиляторы
— фильтры

И «Вентиляция» видна всегда. Как такое сделать? Знает кто-нибудь?

Первой линией группировки строк выберите линию выше первой составляющей проекта.

Добрый день!
Помогите, пожалуйста!
Как защитить лист и оставить возможность работы со структурой?
Необходимо защитить лист от внесения изменений и на листе имеется сгруппированные в структуру данные.
При установке обычной защиты теряется возможность работы с этой структурой.
Необходимо, чтобы наряду с защитой листа можно было еще пользоваться структурой.Т.е. чтобы пользователь мог просмотреть все в удобной форме, но не смог ничего изменить, что защищено.
Формой будут пользоваться несколько пользователей.Есть ячейки открытые для редактирования и закрытые.
Без группировок форма нечитабельна, но и без защиты она бесполезна((
Подскажите, пожалуйста, есть ли возможность совмещения и защиты листа/книги и сохранения действующей структуры?

красавы!. про плюсики и их место было полезно! два дня потратил!

Спасибо, было очень полезно 🙂

У меня к Вам 2 вопроса:

1. Можно ли так же «с помощью плюсика» группировать листы. т.е., например у меня склады по одной тематике, а я хочу их объединить как в одну папку, чтоб при нажатии на плюс или что либо другое, раскрывался подробный перечень имеющихся листов?

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

1) не предполагаю, что подобное возможно.

2) создайте новый лист, раз на старом не работает, и сделайте там все с нуля.

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

Вполне вероятно, что защита. Точнее на удалении сказать невозможно.

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

Можно ли раскрывать/скрывать группу не только по +/-, но и нажатием, скажем, на заголовок этой группы?

«На заголовок» не скажу — для этого нужно написать/найти функцию, которая меняет значение линка после клика по нему (или по ячейке, в которой он находится), а искать это нет времени, простите.

1) Сделайте список на десять строк с таким вот содержимым:
blu
blo
спрятать
строку
через
макрос
bla
ble
bli
bldjad

2) сгруппируйте, если угодно, строки, но обязательно с третью по шестую.

3) Alt+F11 — добавьте два макроса

Sub HideRows()
Rows(«3:6»).SpecialCells(xlCellTypeConstants, 23).EntireRow.Hidden = True
End Sub

Sub ShowRows()
Rows(«3:6»).SpecialCells(xlCellTypeConstants, 23).EntireRow.Hidden = False
End Sub

Смысел ясен? Строки с 3 по 6 будут скрыты полностью. Второй макрос их раскрывает. При выполнении макроса HideRows на листе в нашем примере останутся только строки с латинскими буквами, а те, что содержат кириллицу, будут скрыты.

Кстати, чтобы управлять скрытием колонок, используйте такие команды
Columns(«A:С»).SpecialCells(xlCellTypeConstants, 23).EntireColumn.Hidden = True/False

4) Поставьте на лист две кнопки из меня «Формы», дайте им имена- «Спрятать» и «Показать», и назначьте выполнение этих макросов на эти кнопки. http://www.planetaexcel.ru/tip.php?aid=122 тут указано, как это делается.

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

Спасибо! А нельзя ли при этом, чтобы скрывались не целые строки, а диапазон ячеек? Скажем, А3:В6.

Связанный список в MS EXCEL

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

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

  • ОтделСотрудники отдела. При выборе отдела из списка всех отделов компании, динамически формируется список, содержащий перечень фамилий всех сотрудников этого отдела (двухуровневая иерархия);
  • Город – Улица – Номер дома. При заполнении адреса проживания можно из списка выбрать город, затем из списка всех улиц этого города – улицу, затем, из списка всех домов на этой улице – номер дома (трехуровневая иерархия).

В этой статье рассмотрен только двухуровневый связанный список. Многоуровневый связанный список рассмотрен в одноименной статье Многоуровневый связанный список.
Создание иерархических структур данных позволяет избежать неудобств выпадающих списков связанных со слишком большим количеством элементов.
Связанный список можно реализовать в EXCEL, с помощью инструмента Проверка данных ( Данные/ Работа с данными/ Проверка данных ) с условием проверки Список (пример создания приведен в данной статье) или с помощью элемента управления формы Список (см. статью Связанный список на основе элемента управления формы).

Создание Связанного списка на основе Проверки данных рассмотрим на конкретном примере.

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

Таблицу, в которую будут заноситься данные с помощью Связанного списка, разместим на листе Таблица. См. файл примера Связанный_список.xlsx

Список регионов и перечни стран разместим на листе Списки.

Обратите внимание, что названия регионов (диапазон А2:А5 на листе Списки) в точности должны совпадать с заголовками столбцов, содержащих названия соответствующих стран (В1:Е1).

Присвоим имена диапазонам, содержащим Регионы и Страны (т.е. создадим Именованные диапазоны). Быстрее всего это сделать так:

  • выделитьячейки А1:Е6 на листе Списки(т.е. диапазон, охватывающий все ячейки с названиями Регионов и Стран);
  • нажать кнопку «Создать из выделенного фрагмента» (пункт меню Формулы/ Определенные имена/ Создать из выделенного фрагмента );
  • Убедиться, что стоит только галочка «В строке выше»;
  • Нажать ОК.

Проверить правильность имени можно через Диспетчер Имен ( Формулы/ Определенные имена/ Диспетчер имен ). Должно быть создано 5 имен.

Можно подкорректировать диапазон у имени Регионы (вместо =списки!$A$2:$A$6 установить =списки!$A$2:$A$5 , чтобы не отображалась последняя пустая строка)

На листе Таблица, для ячеек A5:A22 сформируем выпадающий список для выбора Региона.

  • выделяем ячейки A5:A22;
  • вызываем инструмент Проверка данных;
  • устанавливаем тип данных – Список;
  • в поле Источник вводим: =Регионы

Теперь сформируем выпадающий список для столбца Страна (это как раз и будет желанный Связанный список).

  • выделяем ячейки B5:B22;
  • вызываем инструмент Проверка данных;
  • устанавливаем тип данных – Список;
  • в поле Источник вводим: =ДВССЫЛ(A5)
Читать еще:  Как сделать цветным выпадающий список в excel?

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

Тестируем. Выбираем с помощью выпадающего списка в ячейке A5 РегионАмерика, вызываем связанный список в ячейке B5 и балдеем – появился список стран для Региона Америка: США, Мексика

Теперь заполняем следующую строку. Выбираем в ячейке A6 РегионАзия, вызываем связанный список в ячейке B6 и опять балдеем: Китай, Индия

Необходимо помнить, что в именах нельзя использовать символ пробела. Поэтому, при создании имен, вышеуказанным способом, он будет автоматически заменен на нижнее подчеркивание «_». Например, если вместо Америка (ячейка В1) ввести «Северная Америка» (соответственно подкорректировав ячейку А2), то после нажатия кнопки Создать из выделенного фрагмента будет создано имя «Северная_Америка». В этом случае формула =ДВССЫЛ(A5) работать не будет, т.к. при выборе региона «Северная Америка» функция ДВССЫЛ() не найдет соответствующего имени. Поэтому формулу можно подкорректировать, чтобы она работала при наличии пробелов в названиях Регионов: =ДВССЫЛ(ПОДСТАВИТЬ(A5;» «;»_»)) .

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

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

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

Как настроить группировку строк в excel?

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

Инструкция

Открываем файл excel и приступаем к группировке:

  • Выделите нужные строки;
  • Откройте вкладку «Данные» в меню сверху;
  • Под ним в поле «Структура» найдите команду «Группировать»;

  • В появившемся окошке поставьте галочку напротив строк;

Ничего сложного, не так ли?
Объединенные экселем области имеют рядом и/или сверху полоску. Под ней находится знак минуса. Нажмите на него. Сгруппированные ячейки свернулись. Чтобы снова их увидеть, нажмите на плюсик сверху.

Задаем название

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

  • Выполните те же действия, что описаны в инструкции выше. Но не спешите применять команду «Группировать».
  • Сначала нажмите на маленький квадратик рядом со словом «Структура».
  • В появившемся окне «Расположение итоговых данных» снимите все галочки.

Теперь нам необходимо исправить заданную ранее систематизацию:

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

Автоматическая структуризация

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

Благодаря этому таблица не занимает много места.

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

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

Как сортировать данные таблицы?

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

Цветовое деление

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

  • Во вкладке «Данные» переходим к полю «Сортировка и фильтр».
  • В зависимости от версии excel нужная нам команда может называться просто «Сортировка» или «Настраиваемая». После нажатия на нее должно появиться новое окно.

  • В разделе «Столбец» в группе «Сортировать по» выберите необходимый столбец.
  • В разделе сортировки кликните, по какому условию необходимо выполнить деление. Вам нужно сгруппировать по цвету ячейки? Выбирайте этот пункт.
  • Для определения цвета в разделе «Порядок» кликните на стрелочку. Рядом вы можете скомандовать, куда переместить отсортированные данные. Если нажмете «Сверху», они сместятся наверх по столбцу, «Влево» — по строке.

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

Объединение значений

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

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

Упрощаем большую таблицу

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

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

На этом всё, друзья.

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

Ссылка на основную публикацию
Adblock
detector