Youtubezilla.ru

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

Трюк №25. Как в Excel при проверке данных заставить Excel использовать список на другом рабочем листе

Трюк №25. Как в Excel при проверке данных заставить Excel использовать список на другом рабочем листе

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

Заставить Excel при проверке данных ссылаться на список на другом рабочем листе можно двумя способами — при помощи именованных диапазонов и функции ДВССЫЛ (INDIRECT).

Способ 1. Именованные диапазоны

Вероятно, самый простой и быстрый способ выполнить эту задачу — присвоить имя диапазону, где размещается список. Для этого упражнения мы предполагаем, что диапазону присвоено имя MyRange. Выделите ячейку, в которой должен будет появиться этот раскрывающийся список, и выберите команду Данные → Проверка (Data → Validation). В поле Тип данных (Allow) выберите Список (List), а в поле Источник (Source) введите =MyRange. Щелкните на кнопке ОК. Теперь список (который находится на другом рабочем листе) можно использовать как список проверки.

Способ 2. Функция ДВССЫЛ (INDIRECT)

Функция ДВССЫЛ (INDIRECT) позволяет ссылаться на ячейку, содержащую текст, который представляет собой адрес ячейки. Ячейку, содержащую функцию ДВССЫЛ (INDIRECT), можно использовать как ссылочную ячейку и применять эту возможность для связи с рабочим листом, где находится нужный список.

Предположим, список находится на листе Sheet1 в диапазоне $А$1:$А$10 . Щелкните любую ячейку на другом рабочем листе, где должен будет появиться этот список проверки. Затем выберите команду Данные → Проверка (Data → Validation) и в поле Тип данных (Allow) выберите пункт Список (List). В поле Источник (Source) введите следующую функцию: =INDIRECT(«Sheet1!$А$1:$А$10») , в русской версии Excel: =ДВССЫЛ(«Sheet1!$А$1:$А$10») . Убедитесь, что флажок Список допустимых значений (In-Cell) установлен, и щелкните на кнопке ОК. Список на листе Sheetl окажется в вашем раскрывающемся списке проверки.

Если имя рабочего листа, на котором расположен список, содержит пробелы, функцию ДВССЫЛ (INDIRECT) нужно записать так: =INDIRECT(«‘Sheet 1’!$А$1:$А$10») , в русской версии Excel: =ДВССЫЛ(«Sheet 1!$А$1:$А$10») . Различие заключается в том, что здесь после первой кавычки стоит один апостроф, а второй апостроф находится перед восклицательным знаком. Апострофы ограничивают название листа.

Читайте так же:
Как сделать нижнюю панель прозрачной?

Преимущества и недостатки обоих методов

У именованных диапазонов и функции ДВССЫЛ (INDIRECT) при использовании их для связи со списком на другом рабочем листе есть преимущества и недостатки.

Преимущество использования именованного диапазона в данном сценарии заключается в том, что изменение названия листа не повлияет на список проверки. nЭто подчеркивает недостаток функции ДВССЫЛ (INDIRECT) — а именно, любое изменение названия листа не будет автоматически отражаться в функции ДВССЫЛ (INDIRECT), поэтому придется вручную изменить функцию, указав новое название листа.

Преимущество функции ДВССЫЛ (INDIRECT): если из именованного диапазона будет удалена первая ячейка или строка либо последняя ячейка или строка, то именованный диапазон вернет ошибку #REF! . В этом недостаток именованных диапазонов — если удалить ячейки или строки из именованного диапазона, эти изменения не повлияют на список проверки.

Поиск последнего значения в столбце Excel

Схематический регистр товаров, выданных с магазина:

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

Результат выполнения формулы для получения последнего значения:

Разбор принципа действия формулы для поиска последнего значения в столбце:

Главную роль берет на себя функция =ИНДЕКС(), которая должна возвращать содержимое ячейки таблицы где пересекаются определенная строка и столбец. В качестве первого аргумента функции ИНДЕКС выступает неизменяемая константа, а именно ссылка на целый столбец (B:B). Во втором аргументе находится номер строки с последним заполненным значением столбца B. Чтобы узнать этот номер строки используется функция СЧЁТЗ, которая возвращает количество непустых ячеек в диапазоне. Соответственно это же число равно номеру последней непустой строки в столбце B и используется как второй аргумент для функции ИНДЕКС, которая сразу возвращает последнее значение столбца B в отдельной ячейке E1.

Внимание! Все записи в столбце B должны быть неразрывны (без пустых ячеек до последнего значения).

Стоит отметить что данная формула является динамической. При добавлении новых записей в столбец B результат в ячейке E1 будет автоматически обновляться.

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

Синтаксис: =АДРЕС(строка; столбец; [тип_закрепления]; [стиль_ссылки]; [имя_листа]), где:

  • Строка – обязательный аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
  • Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
  • тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
    • 1 – значение по умолчанию, когда закреплены все индексы;
    • 2 – закрепление индекса строки;
    • 3 – закрепление индекса столбца;
    • 4 – адрес без закреплений.
    • ИСТИНА – формат ссылок «A1»;
    • ЛОЖЬ – формат ссылок «R1C1».

    =АДРЕС(1;1) – возвращает $A$1.
    =АДРЕС(1;1;4) – возвращает A1.
    =АДРЕС(1;1;4;ЛОЖЬ) – результат R[1]C[1].
    =АДРЕС(1;1;4;ЛОЖЬ;»Лист1″) – результат выполнения функции Лист1!R[1]C[1].

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

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

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

    В результате должно получиться следующее:

    Для вертикального отображения можно воспользоваться кодом:

    В результате получится так:

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

    Примеры выпадающих списков в Excel

    #1. Стандартный

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

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

    Как видим, при выделении ячейки в диапазоне A2:A25 во вкладке «Проект», у нас появился список значений.

    #2. Список с подстановкой данных

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

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

    Настроим проверку данных иначе. Для автодополнения списков рассмотрим 2 варианта:

    #1. Умная таблица. Выделяем диапазон с источником, переходим во вкладку на панели инструментов «Главная», раздел «Стили», раскрываем меню «Форматировать как таблицу» и выбираем понравившийся стиль умной таблицы Excel.

    Подробнее о том, что такое «Умные таблицы» и как с ними работать — на наших курсах.

    Excel для финансов

    Алексей Вощак,
    Партнер в Bridges Consulting

    Excel для бизнеса

    Алексей Вощак,
    Партнер в Bridges Consulting

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

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

    В строке с источником прописываем ссылку на столбец таблицы с использованием функции ДВССЫЛ: =ДВССЫЛ("Товары[Товар]"). Далее отмечаем «Распространить изменения на другие ячейки с тем же условием», и нажимаем «ОК».

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

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

    Имя диапазона так же, как и в умной таблице, не должно содержать пробелы и знаки препинания. Выделяем диапазон ячеек с запасом пустых строк. Например, в нашем случае, мы понимаем, что в списке больше 25 значений содержаться не будет. Переходим во вкладку «Формулы», раздел «Определенные имена», меню «Диспетчер имен», нажимаем «Создать».

    Называем будущий список, при необходимости корректируем диапазон значений.

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

    Формула ЕСЛИ в Excel – примеры нескольких условий

    Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

    Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

    Несколько условий в функции ЕСЛИ

    Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются. Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2<1, то условия B2<0,9 и B2<0,95 Excel бы просто «не заметил», т.к. они входят в интервал B2<1 который проверился бы первым (если значение менее 0,9, само собой, оно также меньше и 1). И тогда у нас получилось бы только два возможных варианта: менее 1 и альтернативное, т.е. 1 и более.

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

    Подсказка функции

    В конце нужно обязательно закрыть все скобки, иначе эксель выдаст ошибку

    Ошибка из-за нехватки скобки

    Excel INDIRECT Function

    Excel INDIRECT function

    The Excel INDIRECT function returns a valid cell reference from a given text string. INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.

    • ref_text — A reference supplied as text.
    • a1 — [optional] A boolean to indicate A1 or R1C1-style reference. Default is TRUE = A1 style.

    The INDIRECT function returns a valid cell reference from a given text string. INDIRECT is useful when you need to build a text value by concatenating separate text strings that can then be interpreted as a valid cell reference.

    INDIRECT takes two arguments, ref_text and a1. Ref_text is the text string to evaluate as a reference. A1 indicates the reference style for the incoming text value. When a1 is TRUE (the default value), the style is «A1». When a1 is FALSE, the style is «R1C1». For example:

    The purpose of INDIRECT may at first seem baffling (i.e. Why use text when you can just provide a proper reference?) but there are many situations where the ability to create a reference from text is useful, including:

    • A formula that needs a variable sheet name
    • A formula that can assemble a cell reference from bits of text
    • A fixed reference that will not change even when rows or columns are deleted
    • Creating numeric arrays with the ROW function in complex formulas

    Note: INDIRECT is a volatile function and can cause performance problems in large or complex worksheets. Use with care.

    Example #1 — Variable worksheet name

    In the example shown above, INDIRECT is set up to use a variable sheet name like this:

    The formula in B5, copied down, concatenates the text in B5 to the string «!A1» and returns the result to INDIRECT. The INDIRECT function then evaluates the text and converts it into a proper reference. The results in C5:C9 are the values from cell A1 in 5 sheets listed in column B.

    The formula is dynamic in that responds to the values in column B. In other words, if a different sheet name is entered in column B5, the value from cell A1 in the new sheet is returned. With the same approach, you could allow a user to select a sheet name with a dropdown list, then construct a reference to the selected sheet with INDIRECT.

    Note: sheet names that contain punctuation or space must be enclosed in single quotes (‘), as explained in this example. This is not specific to the INDIRECT function; the same limitation is true in all formulas.

    Example #2 — variable lookup table

    In the worksheet below, VLOOKUP is used to get costs for two vendors, A and B. Using the vendor indicated in column F, VLOOKUP automatically uses the correct table:

    Example of INDIRECT for VLOOKUP with variable table

    The formula in G5 is:

    Example #3 — Fixed reference

    The reference created by INDIRECT will not change even when cells, rows, or columns are inserted or deleted. For example, the formula below will always refer to the first 100 rows of column A, even if rows in that range are deleted or inserted:

    Example #4 — Generate numeric array

    A more advanced use of INDIRECT is to create a numeric array with the ROW function like this:

    One use case is explained in this formula, which sums the bottom n values in a range. You may also run into ROW + INDIRECT idea in more complex formulas that need to assemble a numeric array «on-the-fly». One example is this formula, designed to strip numeric characters from a string.

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