Смещение в эксель

Описание функции СМЕЩ

• Ссылка (обязательный аргумент) — ссылка на ячейку или диапазон смежных ячеек;
• Смещение по строкам (обязательный аргумент) и по столбцам (обязательный аргумент) — число строк и столбцов, на которое результирующий диапазон смещен относительно исходной ссылки;Например, аргументы 4; 3 сместят ссылку на 4 строчки вниз и 3 столбца вправо. При этом оба параметра могут принимать различные значения — положительные (смещение вниз по строкам/вправо по столбцам), нулевые или отрицательные (смещение вверх по строкам/влево по столбцам).
• Высота (необязательный аргумент) и ширина (необязательный аргумент) — высота (в строках) и ширина (в столбцах) возвращаемого диапазона, по умолчанию высота и ширина совпадают с размерами исходной ссылки;Например, аргументы 5; 2 расширят ссылку до диапазона в 5 ячеек высотой и 2 ячейки шириной.

Разберем несколько примеров, чтобы понять принцип работы функции СМЕЩ:

К примеру, формула =СМЕЩ(A1;0;0;5;4) (на рисунке выделена красным цветом) сдвигает ячейку A1 (аргумент функции №1) на (№2) вниз, на (№3) вправо, получаем диапазон A1 (состоящий из одной ячейки), а затем расширяет его до размера 5 (№4) на 4 (№5), т.е. возвращаемая ссылка принимает вид A1:D5 (на рисунке область также выделена красным цветом).
Аналогично, формула =СМЕЩ(A1;1;2;8;3) (выделена синим цветом) сдвигает ячейку A1 на 1 вниз, на 2 вправо, получаем диапазон C2 и расширяет его до размера 8 на 3, т.е. в результате получаем ссылку C2:E9.

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

К примеру, предположим, что мы постоянно работаем с динамическими данными — каждый месяц добавляются новые строки или столбцы, и в этом случае работать с фиксированными диапазонами уже не так удобно.

Пример использования функции СМЕЩ

Функция СМЕЩ возвращает ссылку, поэтому может использоваться с другими функциями, в которых среди аргументов есть ссылки. Поэтому теперь рассмотрим как пользоваться данной формулой вместе с другими на примере стандартных типовых задач.

Пример 1. Функция ПОИСКПОЗ

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

D2A2:A10B2

Пример 2. Функция СУММ

Возьмем начальные условия как в предыдущем примере, однако теперь мы посчитаем сумму продаж за последние 7 дней. Можно воспользоваться стандартной формулой СУММ(B4:B10), но при добавлении новых строчек расчет становится неверным и нам придется каждый раз изменять формулу, поэтому мы пойдем по другому пути. С помощью функции СЧЁТЗ находим последнюю введенную дату (указываем достаточно большой диапазон A2:A100, чтобы была возможность добавлять новые данные). Из полученного результата вычитаем 7, чтобы найти первую дату искомого диапазона, поэтому производя сдвиг начальной ячейки (B2) на найденную величину и расширяя диапазон до размеров 7 на 1, мы получим данные за 7 последних дней. Просуммируем их воспользовавшись функцией СУММ:

Примеры

Дана исходная таблица с тремя столбцами.

Задавая параметры функции СМЕЩ() подсчитаем сумму значений в различных диапазонах таблицы. Для визуального наблюдения диапазона, возвращаемого функцией СМЕЩ() , использовано Условное форматирование. Для удобства изменения параметров функции СМЕЩ() использованы Элементы управления Счетчик.

Для подсчета суммы значений в столбце Продажа1 запишем формулу: =СУММ(СМЕЩ($B$2;0;0;8;1)) диапазон суммирования — $B$2:$B$9 (левый верхний угол — $B$2, высота 8, смещения верхнего угла нет). Результат 34.

Для подсчета суммы значений в столбце Продажа2 запишем формулу: =СУММ(СМЕЩ($B$2;0;1;8;1)) Теперь левый верхний угол диапазона суммирования смещен от $B$2 на один столбец вправо, т.е. диапазон суммирования стал $C$2:$C$9. Результат 68.

Для подсчета суммы значений в столбцах Продажа1 и Продажа2, изменим ширину диапазона. =СУММ(СМЕЩ($B$2;0;0;8;2)) указав ширину в 2 ячейки, результат составит 102, диапазон будет модифицирован в $В$2:$С$9.

Добавив смещение по строкам (+1), получим результат 99: =СУММ(СМЕЩ($B$2;1;0;8;2)) диапазон будет модифицирован в $В$3:$С$9.

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

Выпадающий список в ячейке с удалением использованных элементов

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

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

Шаг 1. Кто сколько работает?

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

Шаг 2. Кто еще свободен?

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

Шаг 3. Формируем список

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

При всей внешней жуткости вида, эта формула делает одну простую вещь – выводит очередное по номеру имя сотрудника (используя функцию НАИМЕНЬШИЙ) из списка или пустую ячейку, если имена свободных сотрудников уже кончились.

Шаг 4. Создаем именованный диапазон свободных сотрудников

Теперь идем в меню Вставка – Имя – Присвоить (Insert – Name – Define) и создаем новый именованный диапазон Имена по следующей формуле:

Фактически, мы просто даем диапазону занятых ячеек в синем столбце собственное название Имена .

Шаг 5. Создаем выпадающий список в ячейках

Осталось выделить ячейки B2:B8 нашего графика и добавить в них выпадающий список с элементами диапазона Имена . Для этого откроем меню Данные – Проверка (Data – Validation) , выберем в списке допустимых значений вариант Список и укажем Источник данных:

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

Как создать связанный список Excel?

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

Это можно сделать разными методами.

Метод 1: Функция ДВССЫЛ

В первом случае надо применить функцию ДВССЫЛ, способную преобразовывать в ссылку любой текст. Например, если написано A1, то функция вернет ссылку на ячейку, имеющую такой же адрес. Если же в ячейке написан текст «Маша», то эта формула сможет сделать линк на именованный диапазон с таким названием. Проще говоря, она может менять ссылку и название местами.

Предположим, нам надо составить такой перечень моделей марок Toyota, Ford, Nissan.

Модельный ряд Toyota нужно выделить, начиная ячейкой A2 вплоть до конца перечня и назвать его аналогично. Если используется древняя версия Excel, эта функция реализуется через меню «Вставка – Имя – Присвоить». Если версия Excel не самая старая (начиная с 2007), то нужно перейти во вкладку «Формулы», где отыскать «Диспетчер имен». Аналогичная операция проводится и с перечнем модельного ряда других производителей автомобилей.

Во время присвоения имен важно не забывать: нельзя составлять имена диапазонов с пробелами и знаками препинания. Также нельзя начинать их с цифры

Таким образом, если бы хотя бы одна из марок содержала пробел, его следовало бы заменить на нижнее подчеркивание.

Теперь нужно сгенерировать перечень автомобилей. Нужно нажать мышью (левой кнопкой) по пустой ячейке и открыть меню Данные – Проверка для Excel 2003 версии и кнопку «Проверка данных» на вкладке «Данные» для Excel 2007 версии и более современных. Затем в перечне «Тип данных» нужно выбрать Список, и в поле «Источник» написать ячейки с названиями марок.

Теперь следует сформировать второй список, где будут перечисляться машины из модельного ряда. Последовательность действий такая же, но с тем лишь исключением, что в поле Источник нужно указать формулу =ДВССЫЛ(F3). Естественно, в скобках нужно указать адрес подходящей именно в вашем случае ячейки. Все, после того, как вы нажмете ОК, формирование второго перечня будет осуществляться, исходя из данных в первом.

Но такой метод имеет ряд существенных недостатков:

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

Использование функции СМЕЩ и ПОИСКПОЗ

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

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

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

А вот для второго списка, на который будет влиять первый, придется выполнять другие действия, а именно создать именованный диапазон с функцией =СМЕЩ, создающей ссылку на ячейку, где указаны исключительно модели определенной марки. А для этого осуществляются следующие действия:

1. Надо нажать комбинацию Ctrl + F3. Также можно открыть диспетчер имен и присвоить имя соответствующему диапазону.
2. Создать новый диапазон вместе с функцией СМЕЩ в ссылке и присвоить ему имя. Оно может быть каким-угодно, например, «Модели».

В нашей ситуации формула будет следующей:

=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)

Важно указывать только абсолютные ссылки. То есть, в них надо указать знак $

После нажатия клавиши Enter, Excel автоматически добавит имена листов. Это не должно вас пугать.

Результат будет следующий:

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

1. Выделить ячейку G8.
2. Выбрать команду «Проверка данных» на вкладке «Данные».
3. Там, где источник списка, указать имя диапазона: =Модели.

И все!

Пример использования функции СМЕЩ

Функция СМЕЩ возвращает ссылку, поэтому может использоваться с другими функциями, в которых среди аргументов есть ссылки.
Поэтому теперь рассмотрим как пользоваться данной формулой вместе с другими на примере стандартных типовых задач.

Пример 1. Функция ПОИСКПОЗ

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

Воспользуемся функцией ПОИСКПОЗ для поиска указанной даты (ячейка D2) в диапазоне с датами (A2:A10).
После чего сместим начальную ячейку (в данном случае B2) на рассчитанную величину вниз за вычетом единицы.
Мы дополнительно вычитаем единицу так как показываем именно смещение относительно начальной ячейки, например, чтобы перейти с первой строки на шестую мы смещаемся ровно на пять строк.
В итоге получаем следующий результат:

Идентичного результата можно добиться и с помощью функции ИНДЕКС — формула =ИНДЕКС(B2:B10;ПОИСКПОЗ(D2;A2:A10;0)) вернет точно такой же результат.

Пример 2. Функция СУММ

Возьмем начальные условия как в предыдущем примере, однако теперь мы посчитаем сумму продаж за последние 7 дней.
Можно воспользоваться стандартной формулой СУММ(B4:B10), но при добавлении новых строчек расчет становится неверным и нам придется каждый раз изменять формулу, поэтому мы пойдем по другому пути.
С помощью функции СЧЁТЗ находим последнюю введенную дату (указываем достаточно большой диапазон A2:A100, чтобы была возможность добавлять новые данные).
Из полученного результата вычитаем 7, чтобы найти первую дату искомого диапазона, поэтому производя сдвиг начальной ячейки (B2) на найденную величину и расширяя диапазон до размеров 7 на 1, мы получим данные за 7 последних дней.
Просуммируем их воспользовавшись функцией СУММ:

При добавлении новых данных в таблицу результат будет автоматически пересчитываться:

Выпадающий список с удалением использованных элементов

​Target = Target​ на лист и​​ клавиши Alt +​Вручную через «точку-с-запятой» в​

Постановка задачи

​Цвет заливки​Чтобы переместить поле со​ двух типов: элемент​ должны отображаться в​ в другой графе​ поле «Источник» ставим​ только при непосредственном​5.​данных:​Теперь выясним, кто из​A2​ОК​ в нашем примере​ & «,» &​ выделяем попеременно нужные​ F11. Копируем код​

​ поле «Источник».​Щелкните свойство​ списком на листе,​

Шаг 1. Кто сколько работает?

​ управления формы или​ списке, как показано​ предлагается выбрать соответствующие​ знак равно, и​ вводе значений с​Если список значений​Вот и все! Теперь​ наших сотрудников еще​- начальная ячейка​. Если теперь дописать​

​ выше) и на​ newVal​

​ ячейки.​ (только вставьте свои​Ввести значения заранее. А​BackColor​

Шаг 2. Кто еще свободен?

​ выделите его и​ элемент ActiveX. Если​ на рисунке.​ ему параметры. Например,​ сразу без пробелов​ клавиатуры. Если Вы​ находится на другом​ при назначении сотрудников​ свободен, т.е. не​0​

​ к нашей таблице​

Шаг 3. Формируем список

​Главной (Home)​Else​Теперь создадим второй раскрывающийся​ параметры).Private Sub Worksheet_Change(ByVal​ в качестве источника​(Цвет фона), щелкните​ перетащите в нужное​ необходимо создать поле​На вкладке​ при выборе в​ пишем имя списка,​ попытаетесь вставить в​

​ листе, то вышеописанным​

​ на дежурство их​

​ исчерпал запас допустимых​

​- сдвиг начальной​ новые элементы, то​вкладке нажмите кнопку​Target = newVal​ список. В нем​ Target As Range)​ указать диапазон ячеек​ стрелку вниз, откройте​ место.​ со списком, в​Разработчик​

Шаг 4. Создаем именованный диапазон свободных сотрудников

• ​ списке продуктов картофеля,​ которое присвоили ему​ ячейку с​​ образом создать выпадающий​ имена будут автоматически​ смен. Добавим еще​
• ​ ячейки по вертикали​ они будут автоматически​Форматировать как таблицу (Home​​End If​​ должны отражаться те​​ Dim lReply As​

​ со списком.​ вкладку​​Щелкните правой кнопкой мыши​​ котором пользователь сможет​

​нажмите кнопку​

​ предлагается выбрать как​

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

Шаг 5. Создаем выпадающий список в ячейках

​ в нее включены,​ — Format as​If Len(newVal) =​ слова, которые соответствуют​ Long If Target.Cells.Count​​Назначить имя для диапазона​​Pallet​

• ​ поле со списком​ изменять текст в​Вставить​​ меры измерения килограммы​ кнопку «OK».​​значения из буфера​
• ​ (до версии Excel​ списка, оставляя только​ введем в него​​ количество строк​​ а значит -​​ Table)​

​ 0 Then Target.ClearContents​ выбранному в первом​ > 1 Then​​ значений и в​​(Палитра) и выберите​​ и выберите команду​​ текстовом поле, рассмотрите​

​.​ и граммы, а​Выпадающий список готов. Теперь,​ обмена, т.е скопированные​ 2010). Для этого​ тех, кто еще​ формулу, которая будет​0​

planetaexcel.ru>

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

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

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

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("Е2:Е9")) Is Nothing And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        If Len(Target.Offset(0, 1)) = 0 Then
            Target.Offset(0, 1) = Target
        Else
            Target.End(xlToRight).Offset(0, 1) = Target
        End If
        Target.ClearContents
        Application.EnableEvents = True
    End If
End Sub
 

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

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("Н2:К2")) Is Nothing And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        If Len(Target.Offset(1, 0)) = 0 Then
            Target.Offset(1, 0) = Target
        Else
            Target.End(xlDown).Offset(1, 0) = Target
        End If
        Target.ClearContents
        Application.EnableEvents = True
    End If
End Sub
 

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

Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

Выпадающий список, формируемый по условиям (Формулы/Formulas)

​ быть определен жестко,​​. Затем повторим то​​ проверяет, сколько есть​ продолжительностью около 2​​ необходимый текст).​ ячеек является выбор​ могли выбрать город.​ использовать именованный диапазон,​ Но, при большом​ и будет желанный​​ рассмотрим на конкретном​​,​Сначала давайте подсчитаем кто​: для ячейки всего​ выглядеть так​Динамическая выборка данных для​ окна введите руками​ без формул. Однако,​ же самое со​ Легковых Fiatов. В​ минут). Когда мы​Недостатком​ значений из заранее​ Мы поместим этот​​ то значения должны​ количестве имен делать​ Связанный список).​ примере.​в Excel 2007 и​ из наших сотрудников​ используйте ф-цию СЧЕТЕСЛИ.​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ВПР(СТРОКА(A1);Транспорт!$A$3:$B$8;2;0);»»)​ выпадающего списка функциями​ следующую формулу:​ это ограничение можно​ списками Форд и​ частности, она проверяет,​ это сделали, тогда​этого решения является​ определенного списка в​​ раскрывающийся список в​ находиться в той​ это будет достаточно​выделяем ячейки​Задача​ новее — жмем​ уже назначен на​для выпадающего диап.​но и в​ ИНДЕКС и ПОИСКПОЗ​

​=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)​​ обойти, создав отсортированный​

​ Ниссан, задав соответственно​​ сколько раз в​ …​

​ то, что у​​ MS EXCEL. Предположим,​ ячейку​ же книге, можно​ трудоемко.​B5:B22​

​: Имеется перечень Регионов,​​ кнопку​

​ дежурство и на​​ макрос нужен.​ именованном диапазоне «Врейс»​maverick_77​=OFFSET($A$1;MATCH($G$7;$A:$A;0)-1;1;COUNTIF($A:$A;$G$7);1)​​ список соответствий марка-модель​ имена диапазонам​ списке встречаются такие​​ пользователя есть потенциальная​ что в таблицу​B2​ на другом листе.​​Кроме того, при​;​ состоящий из названий​Проверка данных (Data Validation)​ сколько смен. Для​vikttur​ тоже нужно сделать​

​: Здравствуйте.​​Ссылки должны быть абсолютными​ (см. Способ 2).​Ford​​Для ячеек, которые должны​ возможность ввести в​ ведомости необходимо вводить​

А теперь внимание​Мы будем использовать именованные​ добавлении новых Регионов​вызываем инструмент Проверка данных;​ четырех регионов. Для​на вкладке​

​ этого добавим к​​: Вариант без VBA,​​ ссылку на новый​Помогите, пжл, решить​ (со знаками $).​Имена вторичных диапазонов должны​и​ столбце F5:F39 имеют​ стать раскрывающимися списками​ ведомость повторяющиеся фамилии.​ фамилии сотрудников. Чтобы​ – фокус! Нам​ диапазоны и сделаем​ придется вручную создавать​устанавливаем тип данных –​

​ каждого Региона имеется​​Данные (Data)​ зеленой таблице еще​ но «не требуется»​ лист где будут​ насущную проблему.​ После нажатия Enter​

​Nissan​​ значение Легковой, а​ в меню «Данные»​

​ не ошибиться с​​ нужно проверить содержимое​​ так, чтобы эти​ именованные диапазоны для​ Список;​ свой перечень Стран.​​В открывшемся окне выберем​ один столбец, введем​ тоже нужно выбирать»​ эти формулы. Столбец​Создаём планировщик маршрутов​

excelworld.ru>

Функция СМЕЩ() vs ИНДЕКС()

Пусть имеется диапазон с числами (А2:А10) Необходимо найти сумму первых 2-х, 3-х, . 9 значений. Конечно, можно написать несколько формул =СУММ(А2:А3) , =СУММ(А2:А4) и т.д. Но, записав формулу ввиде:

получим универсальное решение, в котором требуется изменять только последний аргумент (если в формуле выше вместо 4 ввести 5, то будет подсчитана сумма первых 5-и значений). Вышеуказанная формула эквивалентна формуле =СУММ(A2:ИНДЕКС(A2:A10;4)) , которая, в свою очередь, эквивалентна формуле =СУММ(A2:A5)

Формула ИНДЕКС(A2:A10;4) возвращает ссылку на ячейку А5.

В этой статье описаны синтаксис формулы и использование функции СМЕЩ в Microsoft Excel.

Примечания

Если аргументы «смещ_по_строкам» и «смещ_по_столбцам» выводят ссылку за границы рабочего листа, функция СМЕЩ возвращает значение ошибки #ССЫЛ!.

Если высота или ширина опущена, то предполагается, что используется та же высота или ширина, что и в аргументе «ссылка».

Функция СМЕЩ фактически не передвигает никаких ячеек и не меняет выделения; она только возвращает ссылку. Функция СМЕЩ может использоваться с любой функцией, в которой ожидается аргумент типа «ссылка». Например, с помощью формулы СУММ(СМЕЩ(C2;1;2;3;1)) вычисляется суммарное значение диапазона, состоящего из трех строк и одного столбца и расположенного одной строкой ниже и двумя столбцами правее ячейки C2.

Синтаксис

Аргументы функции СМЕЩ описаны ниже.

Ссылка — обязательный аргумент. Ссылка, от которой вычисляется смещение. Аргумент «ссылка» должен быть ссылкой на ячейку или на диапазон смежных ячеек, в противном случае функция СМЕЩ возвращает значение ошибки #ЗНАЧ!.

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

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

Высота Необязательный. Высота (число строк) возвращаемой ссылки. Значение аргумента «высота» должно быть положительным числом.

Ширина Необязательный. Ширина (число столбцов) возвращаемой ссылки. Значение аргумента «ширина» должно быть положительным числом.

Описание функции СМЕЩ

• Ссылка(обязательный аргумент) — ссылка на ячейку или диапазон смежных ячеек;
• Смещение по строкам(обязательный аргумент) и по столбцам(обязательный аргумент) — число строк и столбцов, на которое результирующий диапазон смещен относительно исходной ссылки; Например, аргументы 4; 3 сместят ссылку на 4 строчки вниз и 3 столбца вправо. При этом оба параметра могут принимать различные значения — положительные (смещение вниз по строкам/вправо по столбцам), нулевые или отрицательные (смещение вверх по строкам/влево по столбцам).
• Высота(необязательный аргумент) и ширина(необязательный аргумент) — высота (в строках) и ширина (в столбцах) возвращаемого диапазона, по умолчанию высота и ширина совпадают с размерами исходной ссылки; Например, аргументы 5; 2 расширят ссылку до диапазона в 5 ячеек высотой и 2 ячейки шириной.

Разберем несколько примеров, чтобы понять принцип работы функции СМЕЩ:

=СМЕЩ(A1;0;0;5;4)A1A154A1:D5=СМЕЩ(A1;1;2;8;3)A112C283C2:E9

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

К примеру, предположим, что мы постоянно работаем с динамическими данными — каждый месяц добавляются новые строки или столбцы, и в этом случае работать с фиксированными диапазонами уже не так удобно.

Примеры

Дана исходная таблица с тремя столбцами.

Задавая параметры функции СМЕЩ() подсчитаем сумму значений в различных диапазонах таблицы. Для визуального наблюдения диапазона, возвращаемого функцией СМЕЩ() , использовано Условное форматирование . Для удобства изменения параметров функции СМЕЩ() использованы Элементы управления Счетчик .

Для подсчета суммы значений в столбце Продажа1 запишем формулу: =СУММ(СМЕЩ($B$2;0;0;8;1)) диапазон суммирования — $B$2:$B$9 (левый верхний угол — $B$2 , высота 8 , смещения верхнего угла нет). Результат 34 .

Для подсчета суммы значений в столбце Продажа2 запишем формулу: =СУММ(СМЕЩ($B$2;0; 1 ;8;1)) Теперь левый верхний угол диапазона суммирования смещен от $B$2 на один столбец вправо, т.е. диапазон суммирования стал $C$2:$C$9 . Результат 68 .

Для подсчета суммы значений в столбцах Продажа1 и Продажа2, изменим ширину диапазона. =СУММ(СМЕЩ($B$2;0;0;8; 2 )) указав ширину в 2 ячейки, результат составит 102 , диапазон будет модифицирован в $В$2:$С$9 .

Добавив смещение по строкам (+1), получим результат 99 : =СУММ(СМЕЩ($B$2; 1 ;0;8;2)) диапазон будет модифицирован в $В$3:$С$9 .

B. Ввод элементов списка в диапазон (на любом листе)

В правилах Проверки данных (также как и Условного форматирования ) нельзя впрямую указать ссылку на диапазоны другого листа (см. Файл примера ):

Пусть ячейки, которые должны содержать Выпадающий список , размещены на листе Пример,

а диапазон с перечнем элементов разместим на другом листе (на листе Список в файле примера ).

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

Используем именованный диапазон Создадим Именованный диапазон Список_элементов, содержащий перечень элементов выпадающего списка (ячейки A 1: A 4 на листе Список ) . Для этого:

• выделяем А1:А4 ,
• нажимаем Формулы/ Определенные имена/ Присвоить имя
• в поле Имя вводим Список_элементов , в поле Область выбираем Книга ;

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

• вызываем Проверку данных ;
• в поле Источник вводим ссылку на созданное имя: =Список_элементов .

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

Избавиться от пустых строк и учесть новые элементы перечня позволяет Динамический диапазон . Для этого при создании Имени Список_элементов в поле Диапазон необходимо записать формулу = СМЕЩ(Список!$A$1;;;СЧЁТЗ(Список!$A:$A))

Использование функции СЧЁТЗ() предполагает, что заполнение диапазона ячеек ( A:A ), который содержит элементы, ведется без пропусков строк (см. файл примера , лист Динамический диапазон ).

Используем функцию ДВССЫЛ()

Альтернативным способом ссылки на перечень элементов, расположенных на другом листе, является использование функции ДВССЫЛ() . На листе Пример , выделяем диапазон ячеек, которые будут содержать выпадающий список, вызываем Проверку данных , в Источнике указываем =ДВССЫЛ(“список!A1:A4”) .

Недостаток : при переименовании листа – формула перестает работать. Как это можно частично обойти см. в статье Определяем имя листа .

Ввод элементов списка в диапазон ячеек, находящегося в другой книге

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

• в книге Источник.xlsx создайте необходимый перечень элементов;
• в книге Источник.xlsx диапазону ячеек содержащему перечень элементов присвойте Имя , например СписокВнеш;
• откройте книгу, в которой предполагается разместить ячейки с выпадающим списком;
• выделите нужный диапазон ячеек, вызовите инструмент Проверка данных, в поле Источник укажите = ДВССЫЛ(“лист1!СписокВнеш”) ;

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

Если нет желания присваивать имя диапазону в файле Источник.xlsx , то формулу нужно изменить на = ДВССЫЛ(“лист1!$A$1:$A$4”)

СОВЕТ: Если на листе много ячеек с правилами Проверки данных , то можно использовать инструмент Выделение группы ячеек ( Главная/ Найти и выделить/ Выделение группы ячеек ). Опция Проверка данных этого инструмента позволяет выделить ячейки, для которых проводится проверка допустимости данных (заданная с помощью команды Данные/ Работа с данными/ Проверка данных ). При выборе переключателя Всех будут выделены все такие ячейки. При выборе опции Этих же выделяются только те ячейки, для которых установлены те же правила проверки данных, что и для активной ячейки.

Примечание : Если выпадающий список содержит более 25-30 значений, то работать с ним становится неудобно. Выпадающий список одновременно отображает только 8 элементов, а чтобы увидеть остальные, нужно пользоваться полосой прокрутки, что не всегда удобно.

В EXCEL не предусмотрена регулировка размера шрифта Выпадающего списка . При большом количестве элементов имеет смысл сортировать список элементов и использовать дополнительную классификацию элементов (т.е. один выпадающий список разбить на 2 и более).

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

Проверка вводимых значений для подкатегории в зависимом выпадающем списке

Как видите, весь трюк зависимого списка состоит в использовании функции СМЕЩ. Ну хорошо, почти весь. Помогают ей функции ПОИСКПОЗ и СЧЕТЕСЛИ. Функция СМЕЩ позволяет динамически определять диапазоны. Вначале мы определяем ячейку, от которой должен начинаться сдвиг диапазона, а в последующих аргументах определяем его размеры.

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

Поскольку рабочая таблица отсортирована по Категории, то диапазон, который должен быть источником для раскрывающегося списка, будет начинаться там, где впервые встречается выбранная категория. Например, для категории Питание мы хотим отобразить диапазон H6:H11, для Транспорта — диапазон H12: H15 и т. д

Обратите внимание, что все время мы перемещаемся по столбцу H, а единственное, что изменяется, это начало диапазона и его высота (то есть количество элементов в списке)

Начало диапазона будет перемещено относительно ячейки H2 на такое количество ячеек вниз (по числу), сколько составляет номер позиции первой встречающейся категории в столбце Категория. Проще будет понять на примере: диапазон для категории Питание перемещен на 4 ячейки вниз относительно ячейки H2 (начинается с 4 ячейки от H2). В 4-ой ячейке столбца Подкатегория (не включая заголовок, так как речь идет о диапазоне с именем Рабочий_Список), есть слово Питание (его первое появление). Мы используем этот факт собственно для определения начала диапазона. Послужит нам для этого функция ПОИСКПОЗ (введенная в качестве второго аргумента функции СМЕЩ):

Высоту диапазона определяет функция СЧЕТЕСЛИ. Она считает все встречающиеся повторения в категории, то есть слово Питание. Сколько раз встречается это слово, сколько и будет позиций в нашем диапазоне. Количество позиций в диапазоне — это его высота. Вот функция:

Конечно же, обе функции уже включены в функцию СМЕЩ, которая описана выше

Кроме того, обратите внимание, что как в функции ПОИСКПОЗ, так и в СЧЕТЕСЛИ, есть ссылка на диапазон названный Рабочий_Список. Как я уже упоминал ранее, не обязательно использовать имена диапазонов, можно просто ввести $H3: $H15

Однако использование имен диапазонов в формуле делает ее проще и легко читаемой.

Вот и все:

Скачать пример зависимого выпадающего списка в Excel

Одна формула, ну не такая уж и простая, но облегчающая работу и защищает от ошибок при вводе данных!

Два варианта использования этого трюка я уже представил. Интересно, как вы его будете использовать?

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

Кликните по кнопке ниже для загрузки файла с примерами выпадающих списков в Excel: