Computer help - file server


[ Blog ] - [ File Server ] - [ Удаленная компьютерная помощь ]

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

Чтобы создать простой раскрывающийся список, выберите ячейку, в которую будут вводиться данные, затем нажмите «Проверка данных» (на вкладке «Данные»), выберите «Проверка данных», выберите «Список» (в разделе «Разрешить» :) и затем введите элементы списка (разделенные запятыми) в поле Source: (см. рисунок 1).



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

Другой вариант - поместить список в именованный диапазон в электронной таблице, а затем указать это имя диапазона (со знаком равенства) в поле Source: проверки данных (как показано на рисунке 2). ).



Этот второй метод упрощает редактирование вариантов в списке, но добавление или удаление элементов может быть проблематичным. Поскольку именованный диапазон (FruitChoices, в нашем примере) относится к фиксированному диапазону ячеек ($ H $ 3: $ H $ 10, как показано), если в ячейки H11 или ниже добавлено больше вариантов, они не будут отображаться в раскрывающемся списке. (поскольку эти ячейки не входят в ассортимент FruitChoices).

Аналогичным образом, если, например, элементы "Груши" и "Клубника" удалены, они больше не будут отображаться в раскрывающемся списке, но вместо этого раскрывающийся список будет включать два "пустых" варианта, поскольку раскрывающийся список по-прежнему ссылается на весь диапазон FruitChoices. , включая пустые ячейки H9 и H10.

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

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

Обычное (статическое) имя диапазона относится к указанному диапазону ячеек ($ H $ 3: $ H $ 10 в нашем примере, см. Ниже):



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



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

Давайте подробно рассмотрим эту формулу. Варианты выбора "Фрукты" находятся в блоке ячеек непосредственно под заголовком (ФРУКТЫ). Этому заголовку также присваивается имя: FruitsHeading:



Полная формула, используемая для определения динамического диапазона для выбора Fruits: = СМЕЩЕНИЕ (FruitsHeading; 1,0; ЕСЛИОШИБКА (ПОИСКПОЗ (ИСТИНА; ИНДЕКС (ISBLANK (СМЕЩЕНИЕ (FruitsHeading; 1,0,20,1)); 0,0); 0) -1,20); 1)

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

Обратите внимание, что в этом примере в списке всего 8 записей, но под ними также есть пустые ячейки, куда можно добавить дополнительные записи. Число 20 относится ко всему блоку, в котором могут быть сделаны записи, а не к фактическому количеству записей.

Теперь давайте разберем формулу на части (обозначая каждую часть цветом), чтобы понять, как она работает: = OFFSET (FruitsHeading, 1,0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (OFFSET ( FruitsHeading, 1,0,20,1)), 0,0), 0) -1,20), 1)

«Самый внутренний» элемент - OFFSET (FruitsHeading, 1,0,20, 1).Это ссылается на блок из 20 ячеек (под ячейкой FruitsHeading), где можно вводить варианты. Эта функция OFFSET в основном говорит: начните с ячейки FruitsHeading, спуститесь на 1 строку и более 0 столбцов, затем выберите область длиной 20 строк и шириной 1 столбец. Это дает нам 20-рядный блок, в который вводятся варианты выбора фруктов.

Следующая часть формулы - это функция ISBLANK: = OFFSET (FruitsHeading, 1,0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (the above), 0,0), 0) -1 , 20), 1)

Здесь функция СМЕЩЕНИЕ (объясненная выше) была заменена на «приведенную выше» (для облегчения чтения).Но функция ISBLANK работает с 20-строчным диапазоном ячеек, который определяет функция OFFSET.

ISBLANK затем создает набор из 20 значений ИСТИНА и ЛОЖЬ, указывая, является ли каждая из отдельных ячеек в диапазоне из 20 строк, на который ссылается функция СМЕЩЕНИЕ, пустой (пустой) или нет. В этом примере первые 8 значений в наборе будут ЛОЖНЫ, поскольку первые 8 ячеек не пусты, а последние 12 значений будут ИСТИНА.

Следующая часть формулы - это функция ИНДЕКС: = СМЕЩЕНИЕ (FruitsHeading, 1,0, ЕСЛИОШИБКА (ПОИСКПОЗ (ИСТИНА, ИНДЕКС (указанное выше, 0,0), 0) -1,20) , 1)

Опять же, «вышеуказанное» относится к функциям ISBLANK и OFFSET, описанным выше.Функция ИНДЕКС возвращает массив, содержащий 20 значений ИСТИНА/ЛОЖЬ, созданных функцией ISBLANK.

ИНДЕКС обычно используется для выбора определенного значения (или диапазона значений) из блока данных путем указания определенной строки и столбца (внутри этого блока). Но установка входов строки и столбца в ноль (как это сделано здесь) заставляет INDEX возвращать массив, содержащий весь блок данных.

Следующая часть формулы - это функция ПОИСКПОЗ: = СМЕЩЕНИЕ (FruitsHeading, 1,0, ЕСЛИОШИБКА (ПОИСКПОЗ (ИСТИНА, выше, 0) -1,20), 1)

Функция ПОИСКПОЗ возвращает позицию первого значения ИСТИНА в массиве, возвращаемом функцией ИНДЕКС.Поскольку первые 8 записей в списке не являются пустыми, первые 8 значений в массиве будут ЛОЖНЫ, а девятое значение будет ИСТИНА (поскольку 9-я строка в диапазоне пуста).

Таким образом, функция ПОИСКПОЗ вернет значение 9. Однако в этом случае мы действительно хотим знать, сколько элементов находится в списке, поэтому формула вычитает 1 из значения ПОИСКПОЗ (что дает позиция последней записи). Таким образом, ПОИСКПОЗ (ИСТИНА, указанное выше, 0) -1 возвращает значение 8.

Следующая часть формулы - это функция ЕСЛИОШИБКА: = СМЕЩЕНИЕ (FruitsHeading, 1,0, ЕСЛИОШИБКА ( выше, 20), 1)

Функция ЕСЛИОШИБКА возвращает альтернативное значение, если первое указанное значение приводит к ошибке.Эта функция включена, поскольку, если весь блок ячеек (все 20 строк) заполнен записями, функция ПОИСКПОЗ вернет ошибку.

Это потому, что мы говорим функции ПОИСКПОЗ искать первое ИСТИННОЕ значение (в массиве значений из функции ISBLANK), но если НИ ОДНА из ячеек не пуста, то весь массив будет быть заполненным значениями FALSE. Если ПОИСКПОЗ не может найти целевое значение (ИСТИНА) в массиве, который он ищет, он возвращает ошибку.

Итак, если весь список заполнен (и, следовательно, ПОИСКПОЗ возвращает ошибку), функция ЕСЛИОШИБКА вместо этого вернет значение 20 (зная, что в списке должно быть 20 записей).

Наконец, OFFSET (FruitsHeading, 1,0, выше, 1) возвращает диапазон, который мы действительно ищем: начните с ячейки FruitsHeading, спуститесь на 1 строку и более 0 столбцов, затем выберите область это, однако, много строк, если в списке есть записи (и шириной в 1 столбец). Таким образом, вся формула вместе вернет диапазон, содержащий только фактические записи (до первой пустой ячейки).

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



Используемый здесь пример файла (динамические списки) включен и может быть загружен с этого веб-сайта. Однако макросы не работают, потому что WordPress не любит книги Excel с макросами в них.

В качестве альтернативы указанию количества строк в блоке списка блоку списка можно присвоить собственное имя диапазона, которое затем можно использовать в модифицированной формуле. В файле примера этот метод используется во втором списке (Names). Здесь всему блоку списка (под заголовком «NAMES», 40 строк в примере файла) назначается имя диапазона NameBlock.Альтернативная формула для определения NamesList: = OFFSET (NamesHeading, 1,0, ЕСЛИОШИБКА (MATCH (TRUE, INDEX (ISBLANK (NamesBlock), 0,0), 0) -1, ROWS (NamesBlock)), 1)

где NamesBlock заменяет OFFSET (FruitsHeading, 1,0,20,1), а ROWS (NamesBlock) заменяет 20 (количество строк) в предыдущей формуле.

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

.
SETUP UA COMPUTER BLOG