Как сделать динамический выпадающий список в Excel
Обновлено: 03.07.2024
Ограничьте выбор в одном раскрывающемся списке Excel на основе значения в другой ячейке. В этом примере используются динамические массивы и разлив для создания списков, используемых в раскрывающихся списках. Выберите название региона, а затем выберите сотрудника из этого региона.
ПРИМЕЧАНИЕ. Динамические массивы доступны в планах Microsoft 365. Для других версий Excel без динамических массивов используйте другие методы настройки зависимых раскрывающихся списков.
Зависимые раскрывающиеся списки
В этом видео показано, как настроить зависимые раскрывающиеся списки на основе динамических массивов, содержащих элементы для каждого списка. Под видео есть письменные инструкции.
Настройка рабочего листа
На листе ввода данных есть список с именами регионов и сотрудников. Этот список был отформатирован как таблица Excel. Если имена будут добавлены или удалены, размер списка изменится автоматически.
Ячейки для ввода данных располагаются в верхней части листа, где вы выбираете регион, а затем выбираете сотрудника из этого региона.
На данный момент добавлены только заголовки, цвет заливки и границы
Сортировка списка
В некоторых раскрывающихся списках элементы будет легче найти, если они отсортированы по алфавиту. Выполните следующие действия, чтобы отсортировать список названий регионов.
- Выберите ячейку D7, в которую была введена формула динамического массива.
- Нажмите после знака равенства и введите СОРТИРОВАТЬ(
- Нажмите после закрывающей скобки и введите другую закрывающую скобку: )
- Обновленная формула: =СОРТИРОВКА(УНИКАЛЬНАЯ(A2:A11))
- Нажмите клавишу Enter, и список автоматически отсортируется по алфавиту.
Сделайте первое раскрывающееся меню
Далее выполните следующие действия, чтобы создать раскрывающийся список названий регионов в ячейке F2
Протестируйте раскрывающийся список
Чтобы протестировать раскрывающийся список названий регионов:
- Выберите ячейку F2.
- Нажмите на стрелку раскрывающегося списка.
- Выберите название региона из раскрывающегося списка.
Создать список сотрудников
В ячейке G2 нам нужен раскрывающийся список сотрудников. Вместо того, чтобы показывать все имена, мы хотели бы видеть только сотрудников из региона, который был выбран в ячейке F2.
Чтобы создать этот зависимый раскрывающийся список, мы создадим еще одну формулу динамического массива, используя функцию ФИЛЬТР.
- В первом аргументе мы сообщаем Excel, где находится исходный список.
- Во втором аргументе мы укажем Excel, что имя региона в столбце A должно совпадать с названием выбранного региона в ячейке F2.
Чтобы создать формулу, выполните следующие действия:
- В ячейках F6 и F7 введите 2 заголовка для списка – Reg и Emp.
- В ячейке F7 введите следующую формулу, чтобы создать динамический массив с именами сотрудников для выбранного региона:
- =ФИЛЬТР(A2:B11,A2:A11=F2)
![список сотрудников для выбранного региона]()
Чтобы протестировать динамический список, выберите другое название региона в ячейке F2. Список имен сотрудников меняется автоматически. Вот список после выбора Восточного региона, в котором показаны только 2 имени сотрудников.
![список сотрудников для выбранного региона]()
Сортировка имен сотрудников
Как и в случае со списком названий регионов, мы будем использовать в этом списке функцию СОРТИРОВКИ, чтобы расположить имена сотрудников в алфавитном порядке. Так как в этом списке 2 столбца, мы введем 2 в качестве второго аргумента, чтобы Excel сортировал по второму столбцу.
Выполните следующие действия, чтобы отсортировать имена сотрудников.
- Выберите ячейку F7, в которую была введена формула динамического массива.
- Нажмите после знака равенства и введите СОРТИРОВАТЬ(
- Нажмите после закрывающей скобки и введите запятую.
- Введите 2 для номера индекса сортировки, затем введите закрывающую скобку: )
- Обновленная формула:
- =СОРТИРОВАТЬ(ФИЛЬТР(A2:B11,A2:A11=F2),2)
![имена сотрудников отсортированы по алфавиту]()
Зависимый раскрывающийся список
Далее мы воспользуемся проверкой данных, чтобы создать раскрывающийся список имен сотрудников. Это зависимый выпадающий список — его содержимое зависит от того, какой регион был выбран в ячейке F2.
Поскольку имена сотрудников находятся в списке из двух столбцов, мы воспользуемся функцией ИНДЕКС, чтобы получить имена из второго столбца. В функции ИНДЕКС
- Первый аргумент – это динамический диапазон, который начинается с клавиши F7.
- Второй аргумент (строка) останется пустым
- Третий аргумент (столбец) будет равен 2
Выполните следующие действия, чтобы создать раскрывающийся список имен сотрудников в ячейке G2
![формула сортирует уникальный список]()
Протестируйте раскрывающийся список
Чтобы протестировать раскрывающийся список имен сотрудников:
- Выберите ячейку G2.
- Нажмите стрелку раскрывающегося списка и выберите имя сотрудника
- Список сотрудников в раскрывающемся списке должен совпадать со списком в динамическом диапазоне.
![проверить список сотрудников]()
- Очистите ячейки F2 и G2, затем выберите другой регион.
- В раскрывающемся списке в ячейке G2 должны отображаться разные имена в зависимости от выбранного вами региона.
![тестировать с другим названием региона]()
Исправить ошибку
![IFERROR показывает сообщение]()
Если вы не хотите видеть эту ошибку, используйте функцию ЕСЛИОШИБКА, чтобы отобразить пустую ячейку или отобразить сообщение.
Например, измените формулу в ячейке F7 на следующую:
=ЕСЛИОШИБКА(СОРТИРОВКА(ФИЛЬТР(A2:B11, A2:A11=F2),2), "Выберите регион")
![ошибка при выборе региона]()
Загрузить образец файла
- Загрузите готовый образец файла зависимых раскрывающихся списков — динамические массивы, чтобы увидеть, как работает этот метод. Заархивированный файл имеет формат xlsx и не содержит макросов. Для версий Excel, поддерживающих динамические массивы.
ПРИМЕЧАНИЕ. Динамические массивы доступны в планах Microsoft 365.
Итог: узнайте, как создать раскрывающийся список в ячейке, используя новую функцию UNIQUE, чтобы сделать список динамическим.
Уровень квалификации: средний
Загрузить файл Excel
Файл, с которым я работаю в видео, можно найти ниже. Вы также можете скачать версию «Follow Along», если хотите попрактиковаться в построении отчета.
Отчет о посещаемости Dunder Mifflin – Follow Along.xlsx (24,4 КБ)
Отчет о посещаемости Dunder Mifflin – Final.xlsx (166,6 КБ)
Совместимость. В этом файле используются новые функции динамического массива, которые доступны только в последней версии Office 365. Сюда входят версии Excel как для настольных компьютеров, так и для веб-приложений.
Создание панели посещаемости
Списки проверки данных
Раскрывающийся список, который мы рассмотрим сегодня, является частью нашей панели управления посещаемостью. Это список, из которого мы можем выбирать, чтобы отфильтровать данные о посещаемости по отделам.
![Динамическое падение -вниз список]()
Источник этого динамического списка проверки данных был создан с помощью функции UNIQUE. UNIQUE — это формула динамического массива, которая возвращает все уникальные значения из диапазона данных.Другими словами, он удаляет дубликаты из набора данных.
Для приведенного ниже списка эта функция сканирует множество записей, перечисленных в столбце "Отдел" нашей таблицы данных, и возвращает только одну уникальную запись для каждого отдела.
![УНИКАЛЬНАЯ функция для списка отделов]()
Настройка списка проверки данных
Чтобы создать список проверки данных на листе панели мониторинга, перейдите на вкладку "Данные" на ленте и щелкните значок "Проверка данных", который выглядит следующим образом:
Появится окно проверки данных. (Если вы работаете с объединенной ячейкой, вы можете получить всплывающее окно с вопросом, хотите ли вы распространить проверку данных на эти другие ячейки. Если да, просто выберите «Да».)
![Окно проверки данных, показывающее диапазон разлива]()
Фантомный раскрывающийся список
Небольшое примечание. Один небольшой трюк, который я использовал при создании этой панели инструментов, — это включение раскрывающегося значка, который всегда виден. Обычно этот значок появляется только при выборе ячейки, содержащей раскрывающийся список. Но я обнаружил, что иногда пользователям полезно видеть этот значок независимо от того, где находится их курсор на листе, чтобы они знали, что есть доступный список, из которого они могут выбирать.
Итак, я вставил изображение значка рядом с рассматриваемой ячейкой и сделал гиперссылку на эту ячейку. Таким образом, когда они нажмут на изображение, их курсор переместится в ячейку, и появится настоящая раскрывающаяся кнопка, закрывающая значок призрака.
Это просто делает рабочий лист немного более интуитивно понятным для тех, кто не использовал его раньше, показывая им, как они могут взаимодействовать с ним.
Заполнение списка отделов
Остается только сослаться на отфильтрованный список из листа расчета, чтобы он отображался на информационной панели.
Это касается столбцов "Сотрудник" и "Часы".
![Отфильтрованный список сотрудников]()
Теперь, когда вы выбираете различные отделы из раскрывающегося списка, сотрудники, связанные с этим отделом, будут отображаться в списке.
Похожие сообщения
Вот несколько других написанных мною сообщений, которые могут вас заинтересовать, поскольку затрагивают некоторые из тех же тем:
Заключение
В нашем следующем и последнем видеоролике мы рассмотрим, как создать значки грозовой тучи и фейерверка. Они имеют условное форматирование, из-за которого они отображаются серым цветом, когда часы работы отдела выше или ниже установленной цели.
Надеюсь, этот пост был информативным. Если у вас есть вопросы о том, как создать динамический раскрывающийся список, задайте их в комментариях ниже.
Вам также может понравиться
Советы и ярлыки для переименования таблиц Excel
Повтор сражений экспертов FMWC – Square of Fortune
Учебник по формуле ЕСЛИ для Excel — все, что вам нужно знать
Отличная статья. Жаль, что он не работает с Excel для Mac 2016.
Большое спасибо
Я использую раскрывающиеся списки с vlookup, так что UNIQUE — отличная функция!Короткий вопрос:
Какое программное обеспечение вы используете для записи видеоуроков?Спасибо за эти замечательные видео.
Джон (или кто-то другой), скажем, мои первые три столбца — это «дата», «поставщик» и «сумма», а я хочу, чтобы четвертый столбец был «расходом» с раскрывающимся списком (любого количества элементов): A, BC & D и т. д. и пятый столбец «подробности» с раскрывающимся списком (из любого количества элементов): 1, 2, 3 и 4 и т. д. Моя цель, если пользователь выбирает элемент «A» в столбец «расход» (четыре), то только заранее определенный элемент, скажем, элемент «1», будет отображаться в раскрывающемся списке столбца пять, и если пользователь выбирает элемент «B» в столбце «расход» (четыре), затем только заранее определенный элемент, скажем, элемент «2», будет отображаться в раскрывающемся списке столбца пять. Это возможно?
Какую формулу вы бы включили, чтобы пропускать/исключать пустые ячейки в списке сброса? Функция UNIQUE удаляет все пустые ячейки, кроме одной. Я пробовал несколько вариантов и не добился успеха. Я должен уточнить, что я не использую список разлива для раскрывающегося списка. Спасибо!
[…] Източник: Как создать динамический выпадающий список, который автоматически расширяется […]
Добрый день!
Спасибо за прекрасный материал. У меня есть вопрос. как удалить заголовки из поля проверки данных? в вашем примере, как удалить «Все отделы» из выпадающего списка отделов?Начнем с того факта, что это сложный вопрос, и я не обязательно буду женат на методе, который пробовал до сих пор. У меня есть опрос, который рассылается ежеквартально каждый год, и в нем людей просят поставить рейтинг (один и тот же человек может отвечать каждый квартал, но по мере продвижения будут добавляться новые люди). Мы хотели бы рассчитать процент людей, которые улучшили свои показатели между любыми выбранными периодами времени за 2 года/квартала (например, между 1-м кварталом 1 года и 1-м кварталом 4 года, 1-м кварталом 1-го года и 2-м кварталом 1-го года и т. д.). Я всю жизнь не мог понять, как структурировать формулу, которая может искать и сравнивать рейтинги для одного и того же человека на основе двух выбранных разных периодов времени, поэтому я решил сделать вспомогательную таблицу, которая показывает каждый уникальный индивидуальные рейтинги за выбранные 2 года/квартала (с использованием формул сопоставления индексов и списка уникальных участников на основе сводной таблицы с 1 столбцом). К сожалению, таблица Excel с функциями индекса/сопоставления (и полем, вычисляющим прогресс) не расширяется автоматически на основе сводной таблицы или списка диапазонов имен. Есть ли способ сделать так, чтобы таблица автоматически расширялась на основе отдельного списка имен? Или есть другой способ создать таблицу, которая поможет рассчитать этот процент (имейте в виду, что мы будем создавать таблицу, которая сравнивает эти проценты для разных позиций/ролей)? Заранее спасибо!
Я не очень хорошо разбираюсь в VBA, поэтому, если можно избежать использования VBA, это будет наиболее предпочтительным. Кроме того, если вам нужны изображения таблицы источника данных и созданной мной вспомогательной таблицы, я могу отправить их вам, чтобы предоставить больше информации о рассматриваемой проблеме.
Хороший пример использования динамического массива.
Тем не менее, я использую динамические массивы именно для вычисления других значений из подмножества, которое я получаю. Эти новые столбцы относятся к результатам динамических массивов или другим столбцам, которые я добавил рядом с ними.
Проблема заключается в том, что формулы в этих добавленных столбцах не соответствуют размеру динамического массива. Мне постоянно приходится вручную расширять или уменьшать их содержимое, чтобы оно соответствовало размеру динамического массива.
Есть ли способ решить эту ситуацию?При работе с данными в Excel часто требуется добавлять все новые и новые данные. Однако обновление формул, диаграмм, таблиц при добавлении новых данных занимает некоторое время. Excel предлагает функции, которые помогут настроить данные и избежать рутинного обновления. Этот совет посвящен созданию автоматически расширяемого (также называемого динамически обновляемым или просто динамическим) раскрывающегося списка.
Чтобы создать динамический раскрывающийся список, выполните следующие действия:
1. Введите список элементов диапазона. Например:
![Список с изображениями в Excel 2016]()
2. Выберите ячейку, которая будет содержать раскрывающийся список.
3. На вкладке "Данные" в группе "Инструменты данных" нажмите "Проверка данных":
4. В диалоговом окне "Проверка данных" на вкладке "Настройки":
- В раскрывающемся списке Разрешить выберите Список.
- В поле Источник введите следующую формулу:
= СМЕЩЕНИЕ ( , 0, 0, СЧЕТЧИК ( ), 1)
Для этого примера:
= СМЕЩЕНИЕ (Данные!$B$3, 0, 0, СЧЕТЧИК (Данные!$B:$B), 1)
Итак, вы просите Excel вернуть диапазон данных заполненных ячеек, начиная со столбца in (функция COUNTA() возвращает количество непустых ячеек в диапазоне).
![Проверка данных в Excel 2016]()
Примечания:
-
Вы можете создать свой диапазон данных в столбцах. Чтобы создать выпадающий список из данных в столбцах, используйте функцию COUNTA() в качестве последнего параметра.
= СМЕЩЕНИЕ (Данные!$B$2, 0, 0, 1, СЧЕТЧИК (Данные!$2:$2))
5. Нажмите OK (подробнее см. Как отобразить изображение, связанное с элементом списка):
![Раскрывающийся список в Excel 2016]()
После добавления новых данных в список раскрывающийся список автоматически обновлялся:
![Автоматически расширяемый или динамический раскрывающийся список в Excel 2016]()
![Как создать вычисляемый раскрывающийся список]()
Как создать вычисляемый раскрывающийся список
Динамический, зависимый, условный или вычисляемый раскрывающийся список в Excel подразумевает, что содержимое раскрывающегося списка для текущей ячейки должно зависеть от значений в другой ячейке и динамически изменяться при изменении значения управляющей ячейки. .
![Как создать зависимый раскрывающийся список]()
Как создать зависимый раскрывающийся список
Раскрывающийся список – это удобная функция инструментов проверки данных Excel. Выпадающие меню ограничивают возможные варианты выбора и помогают избежать ошибок. Однако не так много пользователей Excel знают, что поле «Источник» в диалоговом окне «Проверка данных» является полем формулы. Формулы в поле «Источник» должны возвращать список значений, но это не обязательно диапазон или список параметров.
![Создание автоматически расширенных диапазонов графиков]()
Создание автоматически расширенных диапазонов графиков
Если вам часто приходится настраивать диапазоны данных, чтобы на диаграммах отображался обновленный диапазон данных, вас может заинтересовать трюк, который заставляет Excel обновлять диапазон данных диаграммы всякий раз, когда вы добавляете новые данные на лист.
В ситуациях, когда содержимое раскрывающегося списка часто меняется, полезно иметь динамический раскрывающийся список, который может автоматически расширяться, а также исключать все пустые ячейки в ссылках на ячейки.
В этом примере используются примеры данных названий приложений для трех разделов: "Производительность", "Игры" и "Утилиты".
![Раскрывающийся список Excel зависит от]()
В раскрывающемся списке должны отображаться имена приложений в зависимости от подразделения, выбранного пользователем.
Добавление проверки данных для отдела
Добавить проверку данных в выбранную ячейку.
Выберите Данные > Символ проверки данных.
В разделе "Критерии проверки" выберите "Список".
Для источника сделайте ссылку на ячейку с названиями подразделений, которые являются заголовками таблиц (ячейка A4:C4).
При правильной настройке он должен отображаться следующим образом:
Способ 1. Использование OFFSET() для создания динамического раскрывающегося списка
Формула настройки для проверки данных
Всякий раз, когда формула должна использоваться в качестве критерия проверки данных, обычно проще сначала настроить формулу в электронной таблице, а затем перенести ее в окно проверки данных, чтобы все вспомогательные подсказки Excel отображались в виде руководств.
Формула OFFSET() имеет следующий синтаксис:
= OFFSET(ссылка, строки, столбцы, [высота], [ширина])
- ссылка = начальная точка, $A$4
- rows = количество строк до первого используемого значения. В этом случае, поскольку мы хотим начать на одну строку ниже заголовка, мы вводим 1
- cols = количество столбцов справа от первого используемого значения. В этом случае это зависит от выбранного пользователем Раздела. Формула ПОИСКПОЗ() очень удобна, поскольку возвращает число:
ПОИСКПОЗ(искомое_значение), искомый_массив, [тип_сопоставления])
- искомое_значение = источник, $F$4
- lookup_array = где будет выполняться поиск, $A$4:$C$4
- match_type = 0 для точного соответствия
Теперь оно выглядит так: MATCH($F$4,$A$4:$C$4,0)
Однако это нельзя использовать как есть, так как возвращаемое значение функции ПОИСКПОЗ() будет 1, 2 или 3 в зависимости от выбранного деления.
Это означает, что когда выбран раздел "Производительность", функция ПОИСКПОЗ() возвращает "1", что указывает функции СМЕЩ() переместиться на один столбец вправо вместо того, чтобы оставаться в столбце А.
Чтобы это исправить, делается коррекция путем вычитания 1 из функции ПОИСКПОЗ().
Параметр col теперь выглядит следующим образом:
ПОИСКПОЗ($F$4,$A$4:$C$4,0)-1
- [height] = сколько строк будет включено. Это должно соответствовать количеству приложений, перечисленных для каждого подразделения, и быть динамичным для расширения и сокращения. Функция COUNTA() очень удобна, так как она подсчитывает ячейки с числами или текстом.
СЧЕТЧИК(значение1, [значение2], …)
Однако, поскольку ссылка на ячейку меняется в зависимости от выбранного пользователем раздела, следует ввести еще одну функцию.
Здесь также можно использовать функцию OFFSET().
СМЕЩЕНИЕ(ссылка, строки, столбцы, [высота], [ширина])
ПОИСКПОЗ($F$4,$A$4:$C$4,0)-1
- [height] = сколько строк будет отображаться. Используйте большую высоту, чем ожидается, чтобы убедиться, что дополнительные ячейки учтены. Для этого примера используется произвольное число 20, которого должно быть достаточно. Это значение можно изменить позже.
- [width] = сколько столбцов будет включено, 1
Теперь это выглядит так:
СЧЁТ(СМЕЩ($A$4, 1, ПОИСКПОЗ($F$4, $A$4:$C$4,0)-1, 20, 1))
- [width] = сколько столбцов будет включено. Поскольку нам нужен только один столбец, установите его равным 1.
Окончательная формула теперь выглядит так:
=СМЕЩ($A$4, 1, ПОИСКПОЗ($F$4, $A$4:$C$4,0)-1, СЧЁТ(СМЕЩ($A$4, 1, ПОИСКПОЗ($F$4, $A$4) :$C$4,0)-1, 20, 1)),1,)
В ячейке будет отображаться одно значение.
Однако формула фактически возвращает массив и не может отобразить все значения в одной ячейке.
Чтобы просмотреть весь массив, щелкните внутри строки формул и нажмите F9.
Чтобы отменить действие, снова нажмите CTRL + Z.
Добавление проверки данных для названий приложений
Скопируйте всю формулу.
Добавьте критерии проверки данных в ячейку F5 через Данные > Проверка данных.
В разделе "Критерии проверки" выберите "Список".
Вставьте формулу в исходное текстовое поле.
Метод 2. Использование ссылок на таблицы для создания динамического раскрывающегося списка
Преобразование таблицы данных в таблицы Excel
Вместо того, чтобы преобразовывать всю таблицу данных в одну таблицу Excel, преобразуйте ее в 3 таблицы Excel.
Для этого выделите каждый столбец данных (CTRL + SHIFT + ВНИЗ при выборе заголовка).
Перепроверьте правильность ссылки на ячейку.
Установите флажок, чтобы указать, что у таблицы есть заголовки.
Измените имя таблицы на название подразделения.
В этом примере мы используем TableProd.
Проделайте то же самое со столбцами Games и Utility и переименуйте их в TableGame и TableUtility.
Формула настройки критериев проверки данных
Формулу ИНДЕКС() можно использовать, чтобы убедиться, что в качестве ссылки используется правильная таблица.
Существует два варианта синтаксиса для INDEX().
Будет использоваться второй вариант.
ИНДЕКС(ссылка, номер_строки, [номер_столбца], [номер_области])
- ссылка = вместо выбора массива ячеек следует выбирать таблицы. Для этого наведите указатель мыши на заголовок, пока не появится черная стрелка.
При его выборе теперь должно отображаться имя таблицы с именем столбца в квадратных скобках: TableProd[Productivity].
Таким же образом выберите два других столбца и заключите все три в круглые скобки.
Теперь это выглядит так:
(TableProd[Производительность],TableGame[Игры], TableUtility[Утилиты])
- row_num = сколько строк будет включено. Поскольку должны быть включены все строки, пропустите этот аргумент, используя запятую.
- [column_num] = сколько столбцов будет включено. Поскольку для каждой таблицы имеется только один столбец, указывать номер столбца не требуется. Пропустите этот аргумент, используя запятую.
- [area_num] = количество областей для перемещения. Это можно определить с помощью формулы ПОИСКПОЗ().
ПОИСКПОЗ($F$4,$A$4:$C$4,0)
Окончательная формула теперь выглядит так:
=INDEX((TableProd[Productivity],TableGame[Games],TableUtility[Utility]). MATCH($F$4,$A$4:$C$4,0))
Как и в способе 1, в ячейке будет отображаться одно значение.
Однако формула фактически возвращает массив.
Чтобы просмотреть весь массив, щелкните внутри строки формул и нажмите F9.
Добавление проверки данных для названий приложений
Скопируйте всю формулу.
Добавьте критерии проверки данных в ячейку F5 через Данные > Проверка данных.
В разделе "Критерии проверки" выберите "Список".
Вставьте формулу в исходное текстовое поле.
Вы можете получить окно сообщения о проблеме с формулой.
Это связано с тем, что проверка данных не может использовать этот тип ссылок на таблицы.
Чтобы исправить это, вставьте эту формулу в диспетчер имен.
Это можно найти в разделе «Формулы» > «Диспетчер имен».
Появится диспетчер имен и отобразятся три таблицы.
Дайте формуле новое имя и вставьте формулу в последнее текстовое поле.
Теперь он должен появиться в списке.
Вернитесь к проверке данных.
В разделе "Критерии проверки" выберите "Список".
В качестве источника укажите название формулы.
Если вы забыли название формулы, которую недавно добавили в Диспетчер имен, нажмите F3.
При правильной настройке теперь должны отображаться имена приложений в раскрывающемся списке.
Видео и рабочая тетрадь
Загрузить рабочую тетрадь можно ЗДЕСЬ.
Информационные панели Excel, которые информируют и впечатляют
Используйте эти приемы в своих отчетах
Непревзойденная цена!
10 комментариев
Привет, Лейла
Я фанат ваших уроков, я многому учусь, и каждый раз, когда мне нужна помощь, я впервые захожу на этот сайт или на ваш канал на YouTube, хотя английский не мой язык, но мне очень легко следовать за вами
Чтобы делать то, что вы делаете в этом руководстве, я ищу способ преобразования формы данных в том виде, в котором они отображаются в «Найти несколько совпадений в Excel и создать зависимый Раскрывающийся список», в то, как он выглядит в этом уроке,
если вы уже объяснили это, не могли бы вы подсказать, где я могу его найти?Привет, Рамзес. Большое спасибо за ваш комментарий. Я очень рад слышать, что вам легко следовать инструкциям. Относительно вашего запроса — я думаю, вы ищете это видео.
Первый метод кажется мне ошибочным, потому что он требует добавления произвольного количества строк (здесь 20). Что произойдет, если мне нужно добавить новые данные помимо 20 пустых ячеек, которые были заполнены? Второй метод также менее «запутан» (более элегантен) и легче усваивается для меня. Кстати, отличный учебник.
Уважаемая сестра! вы действительно один из величайших учителей.
Привет! Спасибо за простую демонстрацию работы динамических списков!
Мне было интересно, есть ли способ, чтобы формулы зависели от списка значений динамического массива; в частности также формула, которая также является динамическим массивом.У меня есть две таблицы для начала: список клиентов (со столбцом с уникальным идентификатором) и таблица документов (где также существует идентификатор документа, а также ссылка на соответствующий уникальный идентификатор клиента). Обе таблицы являются структурированными, и я использую структурированные ссылки. Затем идет третья таблица с множеством записей, связанных как с идентификатором клиента, так и с идентификатором документа из предыдущих таблиц.
Я пытался создать отфильтрованный проверочный список документов для выбора в зависимости от того, какой клиент был выбран для этой конкретной записи.
Возможно ли это с динамическими массивами?
Спасибо за вопрос. Чтобы помочь в ответе на ваш вопрос, следующая ссылка на техническое сообщество Microsoft Excel будет лучшим местом, чтобы задать ваш вопрос. Если у вас есть образец файла для загрузки, это очень поможет в разработке плана действий.
В техническом сообществе Excel работают одни из лучших специалистов в отрасли. Какой бы ни была ваша проблема, я уверен, что кто-нибудь сможет сообщить вам, как лучше всего найти решение.
С более чем 25 тысячами участников и почти 30 тысячами сообщений ваше решение либо готово и ждет, либо может получить ответ быстрее, чем мы могли бы.
Спасибо, что нашли время написать. Я надеюсь, что вы добьетесь успеха с этой фантастической группой энтузиастов Excel.
Команда XelPlus
Здравствуйте, дорогая королева Excel / Excel Melikesi Leila Gharani;
Это еще один очень красноречивый и подробный учебник на тему «Условная и динамическая проверка данных». Некоторое время назад я думал о той же проблеме, но со статическими диапазонами данных, и придумал решение, которое, вероятно, похоже на ваш метод 2.
Что касается метода 1 с функцией OFFSET(): всякий раз, когда я вижу магию Excel с функцией OFFSET(), я снимаю шляпу. Решение метода 1, конечно, очень эзотерическое.
В моей альтернативной версии метода 2, давайте обозначим его как метод 3 — метод NAME-BASED, я поступаю следующим образом.
Шаг 1. Сначала я выбираю каждый столбец данных и преобразовываю его в таблицу с одним столбцом, как вы предложили. Это уже решает проблему динамического диапазона данных и позволяет мне продолжать проверять данные после вставки или удаления одной или нескольких строк (ячеек).Шаг 2. Далее я просто выбираю весь диапазон данных вместе с заголовками столбцов либо вручную, либо сначала открывая окно «Перейти к…» (сочетание клавиш для этого окна — CTRL+G или F5), а затем выбирая столбцы неравной длины, сначала нажав кнопку «Специальные…», а затем выбрав в появившемся окне «Перейти к специальным» переключатель «Константы».
Шаг 3. После того, как все столбцы данных разной длины выбраны, я использую средство Excel «Создать из выделенного» на ленте меню «Формат» или просто нажимаю комбинацию клавиш быстрого доступа CTRL + SHIFT + F3, чтобы назначить заголовок каждого столбца в качестве имени для соответствующего столбца данных в выборе. Нажатие CTRL+SHIFT+F3 или открытие функции «Создать из выделенного» автоматически выполняет эту работу и избавляет меня от огромной нагрузки, если в моей таблице много столбцов.
Шаг 4. Теперь я открываю в ячейке F4 (основная ячейка проверки) инструмент проверки данных Excel. Проверка ввода заголовка столбца в ячейке F4 проста для знающих пользователей Excel. Поэтому я пропускаю эту часть.
Шаг 5. Для части задачи «Условная проверка данных» я снова открываю инструмент проверки данных в ячейке ниже, это ячейка F5. Выбираю как обычно тип проверки данных «Список» и в поле «Источник:» просто пишу формулу
И вуаля!… Готово… После этого все работает как часы.
Поскольку ИМЕНА в Excel должны быть одной строкой без пробелов, требуется дополнительная настройка, когда заголовки столбцов состоят из нескольких слов, разделенных пробелами. Excel заменит пробелы символом подчеркивания «_» в ИМЕНАХ. Поэтому пробелы в основной ячейке проверки данных F4 необходимо заменить символом подчеркивания. Итак, формула поля «Источник:» для условной ячейки проверки данных F5 должна выглядеть так:
=ДВССЫЛ(ПОДСТАВИТЬ(F4", ",_"))
_________________________________________________Я надеюсь, что вы и ваши постоянные читатели сочтете этот Метод 3 удобным и жизнеспособным.
С наилучшими пожеланиями Уважаемая волшебница Excel Лейла.
– Excel Гази Дениз Ходжа (ходжа)Читайте также:
