Работа с именованным диапазоном в microsoft excel

Присвоение наименования

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

• начинаться с буквы, с подчеркивания или со слеша, а не с цифры или другого символа;
• не содержать пробелов (вместо них можно использовать нижнее подчеркивание);
• не являться одновременно адресом ячейки или диапазона (то есть, названия типа «A1:B2» исключаются);
• иметь длину до 255 символов включительно;
• являться уникальным в данном документе (одни и те же буквы, написанные в верхнем и нижнем регистре, считаются идентичными).

Способ 1: строка имен

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

1. Выделяем ячейку или диапазон, над которым следует провести процедуру.
2. В строку имен вписываем желаемое наименование области, учитывая правила написания названий. Жмем на кнопку Enter.

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

Способ 2: контекстное меню

Довольно распространенным способом присвоить наименование ячейкам является использование контекстного меню.

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

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

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

   В поле «Диапазон» указываются координаты области, которой мы даем имя. Автоматически сюда заносится адрес того диапазона, который был первоначально выделен.

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

Название выбранному массиву присвоено.

Способ 3: присвоение названия с помощью кнопки на ленте

Также название диапазону можно присвоить с помощью специальной кнопки на ленте.

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

Способ 4: Диспетчер имен

Название для ячейки можно создать и через Диспетчер имен.

1. Находясь во вкладке «Формулы», кликаем по кнопке «Диспетчер имен», которая расположена на ленте в группе инструментов «Определенные имена».
2. Открывается окно «Диспетчера имен…». Для добавления нового наименования области жмем на кнопку «Создать…».
3. Открывается уже хорошо нам знакомое окно добавления имени. Наименование добавляем так же, как и в ранее описанных вариантах. Чтобы указать координаты объекта, ставим курсор в поле «Диапазон», а затем прямо на листе выделяем область, которую нужно назвать. После этого жмем на кнопку «OK».

На этом процедура закончена.

Но это не единственная возможность Диспетчера имен. Этот инструмент может не только создавать наименования, но и управлять или удалять их.

Для редактирования после открытия окна Диспетчера имен, выделяем нужную запись (если именованных областей в документе несколько) и жмем на кнопку «Изменить…».

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

Для удаления записи выделяем элемент и жмем на кнопку «Удалить».

После этого открывается небольшое окошко, которое просит подтвердить удаление. Жмем на кнопку «OK».

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

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

Пример 2:

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

Супермаркет контролирует ваши продажи, как показано ниже:

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

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

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

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

В конце формулы просто нажмите «Ввод» и проверьте результат.

Именование ячеек в Excel для упрощения использования формул и функций

Советы и учебные пособия по Excel

Редактировать

Добавить в избранное
Избранное

Курс макросов Excel и VBA (от новичка до эксперта)

В этом уроке я собираюсь представить идею именованных ячеек.

Затем к этой ячейке можно обращаться по ее новому пользовательскому имени в формуле или функции. Например, у меня есть таблица заказов, а внизу — мои итоги:

. Я мог бы использовать ссылки на ячейки C12 и B12 для расчета среднего значения OrderTotal. Или я мог бы назвать эти ячейки заранее и использовать имена в качестве ссылки. Чтобы назвать ячейку, выберите ячейку, затем перейдите в поле «Имя». (Выделено красным прямоугольником ниже):

Затем вы можете изменить имя ячейки. Просто нажмите Enter, чтобы сохранить имя. (C12 теперь называется Total)

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

Важно отметить, что некоторые символы, включая пробелы, не допускаются. В приведенном выше примере я использовал символ подчеркивания (_) вместо пробела

Это окно появится, если введенное имя неверно.

После ввода приемлемого имени для ячейки его можно использовать в формулах:

В приведенном выше примере я назвал ячейку B12 No.Orders и C12 Total. Затем я использовал эти новые имена ячеек в качестве ссылки в моей средней формуле. Это также работает с функциями и именованными диапазонами ячеек, если это применимо к формуле/функции. Я дал диапазону C2:C11 название OrderTotals. Затем я помещаю это имя в функцию суммирования:

Управление большим количеством имен в рабочей книге

По мере того, как вы создаете все больше и больше именованных ячеек, вы можете потерять представление о том, где они находятся. Именно тогда появляется инструмент «Диспетчер имен». Если вы перейдете на вкладку «Формулы» на ленте Excel, вы увидите раздел «Определенные имена»:

Если вы нажмете кнопку «Диспетчер имен», откроется следующее окно:

Это поможет вам отслеживать все именованные ячейки/диапазоны в вашей текущей книге.

Вы также можете удалить имя, выбрав его и нажав кнопку Удалить.

Более сложные имена в Excel

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

Для этого нового имени я использовал константу вместо ссылки на ячейку. Формулы/функции также могут быть введены в качестве ссылки для имени:

Вы также можете изменить диапазон своих имен. В приведенном выше примере я сохранил область действия имени TotalTax до Sheet1.

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

Не забудьте загрузить файл для этого урока, чтобы вы могли следить и видеть, как все работает.

Версия Excel: Эксель 2003, Эксель 2007, Эксель 2010, Эксель 2013, Эксель 2016

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков 50+ часов обучения Более 200 справочников Excel

Станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel с помощью этого онлайн-курса.

Посмотреть курс

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков 50+ часов видео Более 200 справочников Excel

Станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel с помощью этого онлайн-курса. (Опыт работы с VBA не требуется.)

Посмотреть курс

Примеры использования именованных диапазонов в Excel

1. Удобная ссылка на диапазон данных

Использование именованного диапазона позволяет упростить процесс ссылки на данные в таблице. Вместо того, чтобы использовать громоздкие ссылки вида «=SUM(A1:A10)», вы можете просто назвать диапазон «Доходы» и ссылаться на него как «=SUM(Доходы)». Это намного более легко читается и понятно не только вам, но и любым другим пользоваться таблицей.

2. Группировка данных в сводной таблице

Если вы работаете с большими объемами данных, именованные диапазоны могут упростить процесс группировки этих данных при создании сводных таблиц. Например, вы можете создать диапазоны для продуктов, отделов или периодов времени и использовать их как основное поле группировки при создании сводной таблицы. Это позволит быстро анализировать данные и делать выводы.

3. Быстрый доступ к нужным данным

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

4. Облегчение формул

Именованные диапазоны могут сделать формулы более читаемыми и легкими для понимания. Вместо того, чтобы напрямую указывать ячейки, можно использовать именованные диапазоны в формулах. Например, «=SUM(A1:A10)» может быть переписана как «=SUM(Доходы)». Это упрощает чтение и понимание формулы и делает ее более гибкой, если необходимо изменить диапазон данных, на которые ссылается формула.

5. Создание диаграмм

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

Используем диалоговое окно Создание имени

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

1. Выделите требуемую область (на данном этапе можно выделить любую область, в дальнейшем вы сможете ее перезадать). Мы выделим ячейку С3, а затем ее перезададим.

2. Перейдите на вкладку Формулы и выберите команду Присвоить имя.

3. Откроется диалоговое окно Создание имени.

4. В поле Имя введите требуемое имя. В нашем случае это имя Коэффициент. В ряде случаев Excel автоматически подставляет имя на основе данных в соседних ячейках. В нашем случае так и произошло. Если Excel этого не сделал или такое имя Вас не устраивает, введите требуемое Вам имя самостоятельно.
5. В раскрывающемся списке Область Вы можете указать область видимости создаваемого имени. Область видимости – это область, где вы сможете использовать созданное имя. Если вы укажете Книга, то сможете пользоваться именем по всей книге Excel (на всех листах), а если конкретный лист – то только в рамках данного листа. Как правило выбирают область видимости – Книга.

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

7. В поле Диапазон отображается адрес активной области, т.е. адрес ячейки или диапазона, которые мы выбрали ранее. При необходимости данный диапазон можно перезадать. Для этого поместите курсор в поле Диапазон, вокруг указанной области появится динамическая граница.

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

8. Если Вас все устраивает, смело жмите ОК. Имя будет создано.

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

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

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

Did you find apk for android? You can find new Free Android Games and apps.

Как использовать имя для представления постоянного значения

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

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

Чтобы создать имя, которое будет присвоено постоянному значению, откройте диалоговое окно «Новое имя», щелкнув вкладку «Формулы», а затем нажмите «Определить имя» в разделе «Определенные имена». Введите имя для представления значения константы, например «ExchangeRate». Чтобы присвоить значение этому имени, введите знак равенства (=) в поле редактирования «Относится к», а затем значение. Между знаком равенства и значением не должно быть пробела. Нажмите «ОК», чтобы завершить создание имени.

ПРИМЕЧАНИЕ. Если в вашей книге есть формула, которую вы используете во многих местах, вы можете ввести эту формулу в поле «Относится к», чтобы вы могли просто ввести имя в каждую ячейку, где вам нужно использовать формулу.

СВЯЗАННЫЕ: Относительная и абсолютная ссылка на ячейку и форматирование

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

Когда мы нажимаем на ячейку с формулой, которая содержит абсолютную ссылку на ячейку , обратите внимание, что результат равен «0,00». Это потому, что мы удалили обменный курс из указанной ячейки

Мы заменим эту ссылку на ячейку новым именем, которое мы создали.

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

Имя вставляется в формулу. Нажмите «Enter», чтобы принять изменения и обновить ячейку.

Обратите внимание, что результат обновляется по курсу, указанному в названии

Имена очень полезны, если вы создаете сложные книги Excel с большим количеством формул. Когда вам необходимо распространить свои рабочие книги среди других, использование имен облегчает понимание ваших формул как другими, так и вами.

Автозаполнение в Excel из списка данных

Ясно, что кроме дней недели и месяцев могут понадобиться другие списки. Допустим, часто приходится вводить перечень городов, где находятся сервисные центры компании: Минск, Гомель, Брест, Гродно, Витебск, Могилев, Москва, Санкт-Петербург, Воронеж, Ростов-на-Дону, Смоленск, Белгород. Вначале нужно создать и сохранить (в нужном порядке) полный список названий. Заходим в Файл – Параметры – Дополнительно – Общие – Изменить списки.

В следующем открывшемся окне видны те списки, которые существуют по умолчанию.

Как видно, их не много. Но легко добавить свой собственный. Можно воспользоваться окном справа, где либо через запятую, либо столбцом перечислить нужную последовательность. Однако быстрее будет импортировать, особенно, если данных много. Для этого предварительно где-нибудь на листе Excel создаем перечень названий, затем делаем на него ссылку и нажимаем Импорт.

Помимо текстовых списков чаще приходится создавать последовательности чисел и дат. Один из вариантов был рассмотрен в начале статьи, но это примитивно. Есть более интересные приемы. Вначале нужно выделить одно или несколько первых значений серии, а также диапазон (вправо или вниз), куда будет продлена последовательность значений. Далее вызываем диалоговое окно прогрессии: Главная – Заполнить – Прогрессия.

Рассмотрим настройки.

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

Посередине выбирается нужный тип:

• арифметическая прогрессия – каждое последующее значение изменяется на число, указанное в поле Шаг
• геометрическая прогрессия – каждое последующее значение умножается на число, указанное в поле Шаг
• даты – создает последовательность дат. При выборе этого типа активируются переключатели правее, где можно выбрать тип единицы измерения. Есть 4 варианта:

• • • день – перечень календарных дат (с указанным ниже шагом)
    • рабочий день – последовательность рабочих дней (пропускаются выходные)
    • месяц – меняются только месяцы (число фиксируется, как в первой ячейке)
    • год – меняются только годы

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

Нижняя часть окна Прогрессия служит для того, чтобы создать последовательность любой длины на основании конечного значения и шага. Например, нужно заполнить столбец последовательностью четных чисел от 2 до 1000. Мышкой протягивать не удобно. Поэтому предварительно нужно выделить только ячейку с одним первым значением. Далее в окне Прогрессия указываем Расположение, Шаг и Предельное значение.

Результатом будет заполненный столбец от 2 до 1000. Аналогичным образом можно сделать последовательность рабочих дней на год вперед (предельным значением нужно указать последнюю дату, например 31.12.2016). Возможность заполнять столбец (или строку) с указанием последнего значения очень полезная штука, т.к. избавляет от кучи лишних действий во время протягивания. На этом настройки автозаполнения заканчиваются. Идем далее.

Как присвоить имя ячейки в Excel

Всем привет. Сегодня расскажу о добавлении имени к ячейке или диапазону. Все знают, чтобы сослаться на ячейку в формуле, нужно указать её координаты – номер строки и имя столбца. А что, если можно присвоить имя ячейке (или диапазону) и обращаться по имени? Давайте сравним две формулы. Здесь я рассчитал рентабельность продаж без присвоения имён:

Формула без именованных ячеек

А здесь я присвоил имена ячейкам и сделал тот же самый расчёт:

Формула с поименованными ячейками

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

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

Если я вас убедил, давайте учиться именовать ячейки!

Как выбрать имя для ячеек Эксель

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

• Используйте содержательные имена, чтобы по ним можно было однозначно трактовать содержание. Например, «Курс_валюты», «Индекс»инфляции» и т.п. Выбирайте короткие, но информативные названия
• Имя не должно начинаться с цифры. Не используйте знаков пунктуации, кроме подчеркивания(«_»), обратного слеш («\») и точки
• Длина имени не может превышать 255 символов, но, повторюсь, старайтесь использовать слова покороче.
• У вас не получится создать имя, совпадающее с координатами существующей ячейки. Рекомендую исключить применение названий, похожих на традиционные ссылки
• В Excel есть служебные имена диапазонов, никогда их не используйте для именования своих массивов. Например, зарезервированы: «Область_печати», «Заголовки_для_печати» и др.

Использование поля «Имя» для назначение имени ячейкам

Один из лучших способов работы с поименованными ячейками – использовать поле со списком «Имя», слева от строки формул. Если имя ячейки не задано, в нем отображаются координаты активной ячейки. Если задано – вы увидите это имя. Чтобы задать название активной ячейки или диапазона – выделите диапазон и запишите его имя в поле «Имя». Нажмите Enter.

Поле «Имя» в Microsoft Excel

Чтобы активизировать ячейку с заданным именем – кликните по её названию в раскрывающемся списке поля «Имя»

Окно «Создание имени»

Есть в Эксель инструмент, позволяющий задать имя диапазона и некоторые дополнительные параметры.

Активизируйте нужный массив или ячейку и выполните на ленте: Формулы – Определенные имена – Присвоить имя.

Откроется окно, где можно задать имя диапазона, его область видимости (книга или лист), добавить примечание, в котором будет дополнительная информация о массиве.

Область видимости указывает, где можно применять созданное имя – во всей книге или только на конкретном листе. После определения имени, нажмите ОК для подтверждения создания.

Окно создания имени в MS Excel

Создание имён из выделенных таблиц

Предположим, у вас есть таблица исходных данных для расчётов. В столбце «А» записаны названия параметров, а в столбце «В» — их значения. Нужно назвать эти значения именами, указанными в столбце А.

Выделите всю таблицу и выполните: Формулы – Определение имени – Создать из выделенного. Откроется диалоговое окно, где нужно выбрать расположение названий.  В нашем примере наименования находятся слева от данных, поэтому выбираем В столбце слева.

Программа сама создаёт названия и присваивает имена из столбца слева.

Если какое-то значение не подписано в этом столбце, или подпись некорректна, её не получается исправить автоматически – Эксель не создаст имя. Если в именах присутствуют пробелы, они будут заменены на знаки нижнего подчёркивания.

Создание имён из выделенного диапазона

Использование диспетчера имён

Отличный инструмент работы с именами в Эксель называется «Диспетчер имён». Здесь собрано много команд и  настроек работы с поименованными ячейками.

Чтобы запустить диспетчер – выполните: Формулы – Определение имени – Диспетчер имён, или нажмите Ctrl+F3.

Диспетчер имён Эксель

В открывшемся окне вы увидите список уже заданных имён и кнопки управления. С помощью диспетчера вы сможете:

1. Увидеть весь список заданных имён и содержание именованных ячеек
2. Отфильтровать список для последующей чистки и корректировки. Для этого нажмите фильтр, выберите один или несколько параметров отбора
3. Изменить название

Задача

Имеется таблица продаж по месяцам некоторых товаров (см. Файл примера ):

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

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

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

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

Для создания динамического диапазона:

• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя >;
• в поле Имя введите: Месяц >;
• в поле Область выберите лист Книга >;
• в поле Диапазон введите формулу =СМЕЩ(лист1!$B$5;;;1;СЧЁТЗ(лист1!$B$5:$I$5))
• нажмите ОК.

Теперь подробнее. Любой диапазон в EXCEL задается координатами верхней левой и нижней правой ячейки диапазона. Исходной ячейкой, от которой отсчитывается положение нашего динамического диапазона, является ячейка B5 . Если не заданы аргументы функции СМЕЩ() смещ_по_строкам, смещ_по_столбцам (как в нашем случае), то эта ячейка является левой верхней ячейкой диапазона. Нижняя правая ячейка диапазона определяется аргументами высота и ширина . В нашем случае значение высоты =1, а значение ширины диапазона равно результату вычисления формулы СЧЁТЗ(лист1!$B$5:$I$5) , т.е. 4 (в строке 5 присутствуют 4 месяца с января по апрель ). Итак, адрес нижней правой ячейки нашего динамического диапазона определен – это E 5 .

При заполнении таблицы данными о продажах за май , июнь и т.д., формула СЧЁТЗ(лист1!$B$5:$I$5) будет возвращать число заполненных ячеек (количество названий месяцев) и соответственно определять новую ширину динамического диапазона, который в свою очередь будет формировать Выпадающий список .

ВНИМАНИЕ! При использовании функции СЧЕТЗ() необходимо убедиться в отсутствии пустых ячеек! Т.е. нужно заполнять перечень месяцев без пропусков. Теперь создадим еще один динамический диапазон для суммирования продаж

Теперь создадим еще один динамический диапазон для суммирования продаж.

Для создания динамического диапазона :

• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя >;
• в поле Имя введите: Продажи_за_месяц >;
• в поле Диапазон введите формулу = СМЕЩ(лист1!$A$6;;ПОИСКПОЗ(лист1!$C$1;лист1!$B$5:$I$5;0);12)
• нажмите ОК.

Функция ПОИСКПОЗ() ищет в строке 5 (перечень месяцев) выбранный пользователем месяц (ячейка С1 с выпадающим списком) и возвращает соответствующий номер позиции в диапазоне поиска (названия месяцев должны быть уникальны, т.е. этот пример не годится для нескольких лет). На это число столбцов смещается левый верхний угол нашего динамического диапазона (от ячейки А6 ), высота диапазона не меняется и всегда равна 12 (при желании ее также можно сделать также динамической – зависящей от количества товаров в диапазоне).

И наконец, записав в ячейке С2 формулу = СУММ(Продажи_за_месяц) получим сумму продаж в выбранном месяце.

Например, в мае.

Или, например, в апреле.

Примечание: Вместо формулы с функцией СМЕЩ() для подсчета заполненных месяцев можно использовать формулу с функцией ИНДЕКС() : = $B$5:ИНДЕКС(B5:I5;СЧЁТЗ($B$5:$I$5))

Формула подсчитывает количество элементов в строке 5 (функция СЧЁТЗ() ) и определяет ссылку на последний элемент в строке (функция ИНДЕКС() ), тем самым возвращает ссылку на диапазон B5:E5 .