Darbe.ru

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

Как сделать выпадающий список в Экселе

Как сделать выпадающий список в Экселе

В пакете офисных программ от компании Microsoft помимо всем известного Word часто используется Эксель (Exel). Он с самого начала представлен в виде листа с таблицей и ячейками, очень удобен для заполнения номенклатуры, отчетностей, перечня товаров и т.д. Инструмент многофункционален, поэтому некоторые особенности сразу не ясны пользователю.

Как создать раскрывающийся список в excel

Иконка программы

При заполнении больших документов с множеством параметров появляется желание как-то упростить процесс. Для этого может быть полезным знание, как создать выпадающий список в excel. Это иногда может избавить от необходимости вводить одни и те же параметры. К примеру, когда вы заполняете названия брендов компании, которые повторяются, то в следующей ячейке страницы (без пропусков) нажмите Alt+стрелка вниз. У вас появится перечень введенных ранее данных этого столбца, из которого есть возможность выбрать необходимый вариант.

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

  1. Открыть вверху пункт «файл».
  2. Перейти в раздел «Параметры».
  3. Во вкладке «Настроить ленту» поставить галочку напротив «Разработчик».

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

  1. В той же вкладке перейдите на вкладку «Режим конструктора», нажмите кнопку «Свойства».
  2. Откроется много параметров, но основными являются только три, которые следует настроить.
  3. ListFillRange – здесь необходимо задать диапазон используемых значений.
  4. ListRows – тут задается количество данных для выпадающего меню.
  5. ColumnCount – здесь указывается количество столбцов, которые будут использованы.

Пошаговые действия

С функцией мультивыбора

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

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

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

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Not Intersect(Target, Range(«C2:C5»)) Is Nothing And Target.Cells.Count = 1 Then

If Len(Target.Offset(0, 1)) = 0 Then

Target.Offset(0, 1) = Target

Target.End(xlToRight).Offset(0, 1) = Target

С наполнением

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

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

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

Пример создания

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

Как сделать выпадающий список в Excel

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

Нажмите на кнопку ниже, чтобы скачать файл с примерами выпадающих списков в Excel:

Видеоурок

Как создать выпадающий список в Экселе на основе данных из перечня

Представьте, что у нас есть список фруктов:

Чтобы создать выпадающий список, нам нужно сделать следующее:

  • Выделите ячейку, в которой мы хотим создать выпадающий список;
  • Перейдите на вкладку Данные => раздел Работа с данными на панели инструментов => выберите пункт Проверить данные.
  • Во всплывающем окне «Проверка входов» в разделе «Параметры» (Выберите «Список» для типа данных:
  • Введите диапазон имен фруктов =$A$2:$A$6 в поле «Источник» или установите курсор мыши в поле ввода значений «Источник», а затем выберите диапазон данных с помощью мыши:
Читайте так же:
Можно ли инициалы переносить на другую строку

Если вы хотите создать выпадающие списки сразу в нескольких ячейках, выделите все ячейки, в которых вы хотите их создать, а затем выполните описанные выше действия. Важно, чтобы ссылки на ячейки были абсолютными (например, $A$2), а не относительными (например, A2 или A$2 или $A2).

Как сделать выпадающий список в Excel используя ручной ввод данных

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

Например, предположим, мы хотим отразить в выпадающем меню два слова «Да» и «Нет». Для этого нам необходимо:

  • Выберите ячейку, в которой мы хотим создать выпадающий список;
  • Перейдите на вкладку Данные => раздел Работа с данными на панели инструментов => выберите пункт Проверить данные:
  • Во всплывающем окне «Validate input values» в разделе «Options» в типе данных выберите «List»:
  • В поле «Источник» введите «Да; Нет».
  • Нажмите кнопку «OK».

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

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

Как создать раскрывающийся список в Эксель с помощью функции СМЕЩ

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

Например, у нас есть список, содержащий перечень фруктов:

Чтобы создать combobox с помощью формулы смешивания, сделайте следующее:

  • Выберите ячейку, в которой нужно создать выпадающий список;
  • Перейдите на вкладку Данные => раздел Работа с данными на панели инструментов => выберите пункт Проверить данные:
  • Во всплывающем окне «Валидация входных значений» на вкладке «Параметры» в типе данных выберите «Список»:
  • Введите формулу в поле «Источник»: =SUBSTITUTE(A$2$;0;0;5)
  • Нажмите «OK».

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

Как эта формула работает?

В приведенном выше примере мы использовали формулу =MEMBERSHIP(link;offset_by_rows;offset_by_columns;[height];[width]).

Эта функция содержит пять аргументов. Аргумент «ссылка» (в примере $A$2) указывает, с какой ячейки должно начинаться смещение. Аргументы «offset_by_lines» и «offset_by_columns» (в примере значение равно «0») укажите, на сколько строк/столбцов должны быть смещены данные для отображения. Аргумент «[height]» задает значение «5», которое указывает высоту диапазона ячеек. Мы не указываем аргумент «[width]», потому что в нашем примере диапазон состоит из одного столбца.

Используя эту формулу, система возвращает диапазон ячеек, начиная с ячейки $A$2, состоящий из 5 ячеек, в качестве данных для выпадающего списка.

Как сделать выпадающий список в Excel с подстановкой данных (с использованием функции СМЕЩ)

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

Для создания списка вам необходимо:

  • Выберите ячейку, в которой нужно создать выпадающий список;
  • Перейдите на вкладку Данные => раздел Работа с данными на панели инструментов => выберите пункт Проверить данные;
  • Во всплывающем окне «Проверка входов» в разделе «Параметры» (Выберите «Список» для типа данных;
  • В поле «Источник» введите формулу =SCHEDULE(A$2$;0;0;ACCOUNT($A$2:$A$100;»»)).
  • Нажмите «OK».

В этой формуле мы передаем в качестве аргумента «[высота]» формулу СЧЕТЧИК, которая подсчитывает в заданном диапазоне A2:A100 количество непустых ячеек.

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

Как создать выпадающий список в Excel с автоматической подстановкой данных

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

  • Создайте список данных, которые будут отображаться в выпадающем списке. В нашем случае это список цветов. Выберите список с помощью левой кнопки мыши:
  • На панели инструментов нажмите Формат как таблица:
  • Выберите стиль таблицы из выпадающего меню:
  • Нажмите OK во всплывающем окне, чтобы подтвердить выбранный диапазон ячеек:
  • Затем выберите диапазон данных таблицы для выпадающего списка и назовите его в левом поле над столбцом «A»:

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

  • Выбираем ячейку, в которой хотим создать список;
  • Перейдите на вкладку Данные => раздел Работа с данными на панели инструментов => выберите Проверка данных:
  • В контекстном окне «Проверка входных значений» на вкладке «Параметры» в типе данных выберите «Список»:
  • В поле источника введите =»Имя таблицы». В нашем случае мы назвали его «Список»:
  • Готово! Создается выпадающий список, в котором отображаются все данные из указанной таблицы:
  • Чтобы добавить новое значение в выпадающий список — просто добавьте информацию в следующую ячейку после таблицы данных:
  • Таблица автоматически расширит диапазон данных. Выпадающий список будет соответствующим образом обновлен новым значением из таблицы:
Читайте так же:
Можно ли использовать гидроаккумулятор без насоса

Как скопировать выпадающий список в Excel

В Excel есть возможность копировать созданные выпадающие списки. Например, в ячейке A1 у нас есть выпадающий список, который мы хотим скопировать в диапазон ячеек A2:A6.

Чтобы скопировать выпадающий список с текущим форматированием:

  • Щелкните левой кнопкой мыши ячейку с выпадающим списком, который нужно скопировать;
  • Нажмите CTRL+C на клавиатуре;
  • выделите ячейки в диапазоне A2:A6, в которые нужно вставить выпадающий список;
  • нажмите комбинацию клавиш CTRL+V.

Это скопирует выпадающий список с сохранением исходного формата списка (цвет, шрифт и т.д.). Если вы хотите скопировать/вставить выпадающий список без сохранения формата, то:

  • Щелкните левой кнопкой мыши ячейку с выпадающим списком, который нужно скопировать;
  • нажмите комбинацию клавиш CTRL+C на клавиатуре;
  • выберите ячейку, в которую нужно вставить выпадающий список;
  • щелкните правой кнопкой мыши => вызовите выпадающее меню и выберите «Специальная вставка»;
  • В появившемся окне в разделе ‘Insert’ выберите ‘conditions on values’:
  • Нажмите ‘OK’.

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

Как выделить все ячейки, содержащие выпадающий список в Экселе

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

  • Перейдите на вкладку Главная на панели инструментов;
  • Нажмите «Найти и выделить» и выберите «Выделить группу ячеек»:
  • В диалоговом окне выберите «Валидация данных». В этом поле у вас есть возможность выбрать «Все» или «Одинаковые». «Все» выделит все выпадающие списки на листе. Пункт «то же» отобразит в раскрывающемся меню выпадающие списки схожих по содержанию данных. В нашем случае мы выбираем «все»:
  • Нажмите «OK».

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

Как сделать зависимые выпадающие списки в Excel

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

Предположим, что у нас есть списки городов в двух странах — России и США:

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

  • Создайте два именованных диапазона для ячеек «A2:A5» с именем «Россия» и для ячеек «B2:B5» с именем «США». Для этого нам нужно выбрать весь диапазон данных для выпадающих списков:
  • Перейдите на вкладку «Формулы» => нажмите «Создать из выбора» в разделе «Определенные имена»:
  • Во всплывающем окне «Создать имена из выбора» установите флажок «в строке выше». Таким образом, Excel создаст два именованных диапазона «Россия» и «США» со списками городов:
  • Нажмите «OK».
  • В ячейке «D2» создайте выпадающий список для выбора стран «Россия» или «США». Это создаст первый выпадающий список, из которого пользователь может выбрать одну из двух стран.

Теперь, чтобы создать зависимый выпадающий список:

  • Выделите ячейку E2 (или любую другую ячейку, в которой вы хотите создать зависимый выпадающий список);
  • Перейдите на вкладку Данные => Валидация данных;
  • Во всплывающем окне «Validate Inputs» во вкладке «Parameters» выберите «List» в типе данных:
  • В поле «Источник» укажите ссылку: =INDIRECT(D2) или =DVSource(D2);
  • Нажмите «OK».

Теперь, если в первом выпадающем списке выбрать страну «Россия», во втором выпадающем списке появятся только города, принадлежащие этой стране. То же самое касается выбора «США» из первого выпадающего списка.

Еще больше полезных приемов работы со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel». Поторопитесь зарегистрироваться по этой ссылке!

Как создать выпадающий список в Microsoft Excel?

sp0

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

sp1

Предположим, у нас есть список данных, который нужно поместить в выпадающий список.

sp2

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

sp3

В открывшемся окне, в разделе Параметры нужно найти «Тип данных» и выбрать Список. Чуть ниже появится поле «Источник», где нужно будет указать диапазон значений. Для этого нужно выделить данные (в нашем случае это весь список фамилий с А2 по А10). Выделять данные можно прямо с открытым окном «Проверка вводимых значений», предварительно поставив курсор в поле «Источник». После этого можно нажать ОК.

Читайте так же:
Можно ли использовать синие габаритные огни

sp4

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

sp8

Этот способ отличается от первого тем, что данный выпадающий список можно будет использовать на нескольких листах. Для этого нужно перейти во вкладку Формулы, нажать на команду «Определенные имена» и выбрать «Диспетчер имен». В открывшемся окне нажать на кнопку «Создать». Задаем имя в соответствующем поле (имя без пробелов и начинающееся с буквы), в диапазоне указать расположение данных. Сделать это можно, выделив список, не закрывая окна. Далее нужно нажать ОКЗакрыть.

Теперь нужно переключиться на вкладку Данные, выбрать «Проверка данных», в «Тип данных» выбрать «Список», в появившемся поле «Источник» прописать =Авторы (у вас будет свое название листа).

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

sp5

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

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

sp6

Во вкладке Разработчик нажать на команду «Вставить» и выбрать «Поле со списком (элемент управления формы)».

sp7

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

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

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

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

Работа со списками данных в Excel

Spisok dannih 1 Работа со списками данных в Excel

Добрый день уважаемый читатель!

Сегодня я хочу поговорить об одной из основных возможностях — это работа со списками данных в Excel. К самим спискам можно отнести практически любые структурированные данные, такие как, номера телефонов, адреса, ФИО, номенклатурные наименования товаров, перечень заведений, поставщики, сотрудники и много-много другой информации, своего рода база данных. Я думаю, с такими данными вы сталкивались, а значится и инструменты для систематизации и анализа таких данных будут очень полезны, особенно при создании дашбордов. По большому счёту от обычной таблицы списки ничем особым не отличаются, за исключением своих размеров, они достаточно велики. При работе со списками используют понятия: для строк – записи, а для столбиков – поля.

Spisok dannih 2 Работа со списками данных в Excel

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

  • За каждым столбиком должна быть закреплена информация только одного типа. Например, в столбик с данными о днях рождениях вводится только такие данные, с именами сотрудников, только имена, и смешение типов данных недопустимы;
  • Информацию лучше всего делить по максимум. Например, ФИО стоить разделить на три разных поля, так как поиск и работа с данными будет легче (по имени можно поздравить в связи с праздником);
  • В обязательном порядке каждое поле должно иметь заголовок, несмотря на то, что с многоуровневыми «шапками» Excel не очень умело умеет работать;
  • В списке должны отсутствовать пустые строки и столбцы, так как это определяется программой как окончание созданного списка и в дальнейшем создаются проблемы и ошибки при отображении данных;
  • Размещение иных данных в стороне от списка не рекомендуются, так как в момент наложения любого из фильтров они будут скрыты.

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

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

Читайте так же:
Можно ли запараллелить два аккумулятора

Вариантов закрепить область прокрутки всего три, это:

  1. Закрепить область – производится закрепление области слева и сверху от текущей ячейки, то есть по горизонтали и вертикали одновременно;
  2. Закрепить верхнюю строку – закрепление по горизонтали сверху установленной ячейки таблицы;
  3. Закрепить первый столбец – произвести вертикальное закрепление списков слева от установленной ячейки.

Spisok dannih 3 Работа со списками данных в Excel Также существует возможность разделения рабочей области одновременно на четыре части для независимой работы и прокручивания данных, получая возможность одновременно работать и в начале и в конце списка. Для разделения вам необходимо на панели управления, во вкладке «Вид», в блоке «Окно» нажать кнопку «Разделить», предварительно установив курсор на ячейку, по границам которой и будет происходить разделение. Отключить разделения можно повторно нажав на туже самую кнопку. Spisok dannih 4 Работа со списками данных в Excel Сами же данные в списках, возможно, отбирать, используя несколько инструментов, выбор которых зависит от ваших целей. Для этих задач можно использовать:

  1. Фильтрацию списков;
  2. Сортировка данных;
  3. Создание промежуточных итогов;
  4. Сводные таблицы;
  5. Группировка элементов таблицы.

Отбор с помощью фильтра

Excel предоставляет возможность отфильтровать ваши списки по заданным критериям двумя вариантами, с помощью:

  1. Расширенного фильтра;
  2. Автофильтра.

Spisok dannih 5 Работа со списками данных в Excel

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

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

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

Создаем промежуточные отчёты

Очень часто возникает необходимость группировать данные списков по определенным показателям с расчётами по ним итогов. Так создаются удобные и очень полезные в работе отчёты и анализы, прекрасный инструмент для любого бухгалтера и экономиста. Spisok dannih 6 Работа со списками данных в Excel Создать такой детализированный список данных в Excel с выделением групп и подбитием итогов по группам и общий по полю, не очень трудно. Всё это можно произвести в несколько шагов, но обязательным условием применения промежуточных итогов к спискам, это сортировка данных по полю для которого создается итог. Spisok dannih 7 Работа со списками данных в Excel Подробно и в деталях об этом можно узнать, прочитав статью «Промежуточные итоги в Excel», перейдя по ссылке.

Сортируем свои списки

Spisok dannih 8 Работа со списками данных в Excel

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

Работаем со сводными таблицами

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

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

Для создания сводной таблицы необходимо установить курсор на любую ячейку таблицы или базы данных, и на панели управления во вкладке «Вставка» выбрать пункт «Сводная таблица». В диалоговом окне указываем, где размещены данные для анализа (по умолчанию будет указан диапазон таблицы, где стоит курсор) и куда нужно поместить результат. Spisok dannih 9 Работа со списками данных в Excel Следующим шагом в «Конструкторе сводной таблицы» вы можете из полей и записей вашей БД создать отчёт в таком виде, который вам нужен. Spisok dannih 10 Работа со списками данных в Excel При внесении изменений в базу данных, автоматических изменений в сводной таблице не происходит. Все изменения стают, доступны только при нажатии кнопки «Обновить данные», через контекстное меню или вкладка «Данные» и кнопка «Обновить всё».

Группируем элементы таблицы

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

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное о работе со списками данных в Excel вам пригодилось и было понятным. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!

Читайте так же:
Можно ли использовать фейри в посудомоечной машине

Как обойти ограничение Excel и сделать выпадающий список зависимым

Недавно дочь обратилась с вопросом, нельзя ли в Excel выпадающий в ячейке список сделать контекстным, например, зависящим от содержания ячейки, находящейся слева от ячейки со списком (рис. 1)? Я довольно давно не использовал в работе выпадающие списки, поэтому для начала решил освежить свои знания по вопросу проверки данных в Excel.

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

Скачать заметку в формате Word или pdf, примеры в формате Excel2007

Команда Проверка данных находится на вкладке Данные, область Работа с данными.

Примечание. Иногда команда Проверка данных может быть недоступна:

  • Возможно, в настоящее время вводятся данные. Во время ввода данных в ячейку команда Проверка данных недоступна. Чтобы завершить ввод данных, нажмите клавишу ВВОД или ESC.
  • Возможно, лист защищен или является общим. Если лист защищен или является общим, изменить параметры проверки данных невозможно. Снимите защиту или отмените режим «общий».
  • Возможно, таблица Excel связана с узлом SharePoint. Невозможно добавить проверку данных в таблицу Excel, которая связана с узлом SharePoint. Чтобы добавить проверку данных, необходимо удалить связь таблицы Excel или преобразовать ее в диапазон.

К сожалению, Excel в своем стандарте позволяет делать списки только на основе:

  • имени массива
  • диапазона ячеек
  • прямого перечисления элементов списка (рис. 2).

Примечание. Элементы списка вводите через стандартный разделитель элементов списка Microsoft Windows (в русском Excel по умолчанию это точка с запятой).

Рис. 2. Возможные источники списка: вверху – имя массива; посередине – диапазон ячеек; внизу – элементы списка

Попытка ввести формулу в поле Источник диалогового окна Проверка вводимых значений заканчивается неудачей (рис. 3). Видно, что Excel не воспринял значение ячейки D2 ( » цвет » ), как имя массива, и просто включил это значение в качестве единственного элемента списка.

Рис. 3. Недопустимый источник списка – формула

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

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

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

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

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

И всё же в Excel есть одна функция непрямого действия. На английском языке у нее говорящее название – INDIRECT. На русском – название функции ни о чем – ДВССЫЛ… В чем же заключается непрямое действие? В отличие от других функций Excel, ДВССЫЛ возвращает не значение, хранящееся в ячейке, а ссылку, хранящуюся в ячейке. Непонятно? Сам «продирался» через это с трудом Попробую пояснить. Вот что написано в справке Excel: ДВССЫЛ – возвращает ссылку, заданную текстовой строкой. Ссылки немедленно вычисляются для вывода их содержимого (рис. 4).

Рис. 4. Как работает функция ДВССЫЛ: вверху – формулы, внизу – значения

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

Итак, еще раз, ДВССЫЛ возвращает ссылку, а не значение, хранящееся в ячейке. Ссылка немедленно вычисляется, и выводится ее значение (или содержимое). Именно это свойство позволит нам ввести непрямую ссылку на соседнюю ячейку так, что вернется не значение, хранящееся в этой соседней ячейке (как на рис. 3), а ссылка, хранящаяся в ячейке, эта ссылка тут же вычисляется, и получается имя массива (рис. 5).

Рис. 5. Формирование списка, зависящего от значения в левой ячейке

Примечание. Ссылка в формуле =ДВССЫЛ(D2) должна быть относительной (D2), а не абсолютной ($D$2). Подробнее об этом см. раздел Тип ссылок на ячейки в формулах для проверки данных заметки Excel. Проверка данных.

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

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