Check-moscow.ru

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

Как выйти из поля со списком, чтобы выбрать конкретную ячейку в Excel?

Как выйти из поля со списком, чтобы выбрать конкретную ячейку в Excel?

x

x

x

Связанные (зависимые) выпадающие списки

Этот трюк основан на использовании функции INDIRECT, которая может сделать одну очень простую вещь: преобразовать содержимое указанной ячейки в адрес диапазона, который понимает Excel. Другими словами, если ячейка содержит текст «A1», то результатом работы функции будет ссылка на ячейку A1. Если ячейка содержит слово «Маша», функция создаст ссылку на диапазон с именем Маша, и так далее. Это своего рода «смена стрелки» 😉

В качестве примера, вот как выглядят модели Toyota, Ford, и Nissan:

Связанные списки с ДВССЫЛ

Выберите все модели Toyota (начиная с A2) и назовите диапазон Toyota в Менеджере имен на вкладке Формулы. Аналогично назовите диапазоны Ford и Nissan в соответствии с их списками моделей.

При присвоении имен помните, что имена диапазонов в Excel не могут содержать пробелов или знаков препинания и всегда должны начинаться с буквы. Поэтому, если одна из марок автомобилей содержит пробел (например, Ssang Yong), замените его подчеркиванием (например, Ssang_Yong) в ячейке и в имени диапазона.

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

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

Читайте так же:
Как вставить пробелы перед заглавными буквами в Excel?

Где F2 — адрес ячейки с первым выпадающим списком (замените его на свой).

Вот и все. При нажатии кнопки OK содержимое второго списка будет выбрано на основе диапазона, который был выбран в первом списке.

Недостатки данного метода следующие:

  • Многие диапазоны имен должны быть созданы вручную (если у нас много марок автомобилей).
  • Динамические диапазоны, определенные формулами, такими как OFFSET, не могут использоваться в качестве вторичных (зависимых) диапазонов. Их можно использовать для первичного (независимого) списка, но вторичный список должен быть жестко определен, без формул. Однако это ограничение можно обойти, создав руководство по сравнению марок (см. режимы 3 и 4).
  • Имена вторичных диапазонов должны совпадать с именами элементов в первичном раскрывающемся списке. Другими словами, если текст содержит пробелы, вам нужно заменить их на символы подчеркивания с помощью функции SUBSTITUTE, т.е. формула будет выглядеть следующим образом:

Способ 2. Умные таблицы

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

Связанные списки на умных таблицах и ДВССЫЛ

Поэтому мы..:

  1. Сначала преобразуем наши справочники в умные таблицы с помощью комбинации клавиш Ctrl + T или команды Home — Format as Table и
  2. Дайте им имена (Fruits, Vegetables, Greens) на вкладке Design в поле Table Name.
  3. Создайте первый и второй (связанные) выпадающие списки так же, как и в предыдущем методе, с помощью функции INDIRECT.

Способ 3. Отсортированный справочник

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

Связанные списки на отсортированном справочнике

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

Читайте так же:
Как заблокировать ширину и высоту ячейки от изменения размера в Excel?

В свою очередь, для списка зависимых моделей создайте именованный диапазон с функцией перестановки (OFFSET), которая динамически ссылается только на ячейки с моделями указанной марки. Для этого:

  • Нажмите Ctrl + F3 или воспользуйтесь кнопкой Менеджер имен на вкладке Формулы.
  • Создайте новый диапазон с любым именем (например, Шаблоны) и вручную введите следующую формулу в поле Ссылка в нижней части окна:

Ссылки должны быть абсолютными (со знаками $). Когда вы нажимаете Enter, имена листов автоматически добавляются в формулу — не пугайтесь.

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

=СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; высота_диапазона_в_строках; ширина_диапазона_в_столбцах)

  • Начальная ячейка — мы берем первую ячейку в нашем списке, т.е. A1
  • Shift_down — функция MATCH вычисляет порядковый номер ячейки с выбранной меткой (G7) в заданном диапазоне (столбец A)
  • Shift_right = 1, так как мы хотим ссылаться на модели в соседнем столбце (A) мы хотим ссылаться на модели в соседнем столбце (B)
  • Range_height_in_rows — вычисляется с помощью функции COUNTIF, который умеет считать количество нужных нам значений в списке (столбец A) — марки автомобилей (G7)
  • Range_width_in_rows = 1, потому что нам нужен один столбец с моделями

Он должен выглядеть примерно так:

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

  • Выберите ячейку G8
  • На вкладке Данные, в выпадающем списке выберите Валидация данных
  • И введите знак равенства и имя нашего интервала в качестве источника, то есть =Модель

Наш связанный список деталей готов.

Способ 4. Неотсортированный справочник

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

Читайте так же:
Как вставить столбцы слева в таблицу в Excel?

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

Первый уровень выпадающего списка (диапазон A2:A14 на рисунке выше) выполнен здесь классическим способом через команду Data — Validation — List и в качестве источника указаны зеленые ячейки с названиями категорий.

Аналогично, для связанных выпадающих списков в столбце B2 (B2 -B14) в поле «Источник» мы используем эту умную формулу:

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

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