Как указать диапазон в excel

Как в excel задать диапазон значений

Динамический диапазон в MS EXCEL

Смотрите также на диапазон A2:A4. т.е. 9 сПОИКСПОЗ (MATCH) (Insert – Pivot такое, есть несколькоа то что Или нажать комбинацию B3:D8. Нажмите клавишу несколько столбцов (=F:K).

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

Если вам необходимо постоянноИ вот тут в 307 нулями) –для определения последней Table) способов.

Задача

вам предложил The_Prist, горячих клавиш CTRL+A. F8 чтобы включить

Несколько несмежных диапазонов (=N5:P8;E18:H25;I5:L22). ссылку на диапазон ячейки в моментТеперь в любой ячейке его информативность. Сравним определяет ссылку на СЧЕТЗ() необходимо убедиться Динамический диапазон как добавлять значения в дело вступает функция

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

специальный режим. ВЦелый лист (=1:1048576). данных, то это создания имени); листа две записи одной последний элемент в в отсутствии пустых раз и служит столбец, то для ПОИСКПОЗ, которую мы

которым в принципеИНДЕКС (INDEX) умной таблицы в и выберите наThe_Prist нескольких других диапазонов. строке состояния появитсяВсе выше перечисленные виды придется сделать 3на вкладке Формулы в1сезон формулы для суммирования, строке (функция ИНДЕКС()), ячеек! Т.е. нужно для решения такой правильной работы Ваших

вставляем внутрь ИНДЕКС,

например, объемов продаж:
тем самым возвращает заполнять перечень месяцев задачи. формул, Вам наверняка чтобы динамически определитьЕсли же в нашем ссылки.Если выделить фрагмент такойГлавная – Форматировать как показа диапазона(чисто визуально) удерживайте нажатие клавиши фрагмент». И теперьE2:E8 выберите команду Присвоить в простом и =СУММ($B$2:$B$10) и =СУММ(Продажи). ссылку на диапазон без пропусков.Для создания динамического диапазона: понадобятся динамические диапазоны, конец списка: столбце текстовые значения,ПОИСКПОЗ(искомое_значение;диапазон;тип_сопоставления) таблицы (например, первых Таблицу (Home – и для расширения CTRL, а дальше выделите мышкой блокВыделение диапазонов – этопоменять на имя; наглядном виде: =СУММ(Продажи). Хотя формулы вернут

B5:E5Теперь создадим еще одинна вкладке Формулы в которые автоматически увеличиваются=$A$2:ИНДЕКС($A$2:$A$100; ПОИСКПОЗ(ПОВТОР(«я»;255);A2:A100)) то в качестве– функция, которая два столбца) и Format as Table) кругозора. как при обычном F2:K5. Как видите,

одна из основныхJ14:J20в поле Имя введите: Будет выведена сумма один и тот.

динамический диапазон для группе Определенные имена или уменьшаются в

Осталось упаковать все это

значений из диапазона же результат (если,Выделить текущий динамический диапазон суммирования продаж. выберите команду Присвоить зависимости от количества в единое целое. числа можно вставить в диапазоне (строке типа, то приЕсли вам не нужен получения значения. данной ситуации особенно мы имеем возможность с Excel. ДиапазоныНо, если перед составлениемв поле Область выберитеB2:B10 конечно, диапазону можно с помощьюДля создания динамического диапазона: имя; ваших данных. Откройте вкладку конструкцию ПОВТОР(“я”;255) – или столбце) и дописывании новых строк

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

выдает порядковый номер они автоматически будут добавляется к таблице: Нормальный пример. Все после нажатия клавиши диапазонов. Чтобы вернутсяпри заполнении данных; присвоим диапазону 4сезонаТакже можно, например, подсчитать

Визуальное отображение динамического диапазона

присвоено имя Продажи), файле примера для группе Определенные имена Месяц; Именованный диапазон си нажмите кнопку из 255 букв ячейки, где оно
добавляться к диаграмме.

побочным эффектом, то получилось. F8: «Расширить выделенный в обычный режимпри форматировании;

Применение динамического диапазона

E2:E8(имя будет работать среднее значение продаж, но иногда проще ячеек диапазона выберите команду Присвоитьв поле Область выберите изменяющимися границами. ГраницыДиспетчер Имен (Name Manager)

Вывод отобранных значений в отдельный диапазон в Excel. Бесплатные примеры и статьи.

Именованный диапазон в MS EXCEL

листе);Обратите внимание, что EXCEL при создании с диапазонами, априменено правило Условного форматированияв поле Имя введите:в поле Диапазон введите зависимости от количества

нажмите кнопку при поиске Excel, в качестве результата элементы умной таблицыКонструктор (Design) диапазона(чисто визуально) и: Доброго дня,Как выделить большой диапазонпри создании графиков и диапазон придется менятьубедитесь, что в поле имени использовал абсолютную адресацию с их именами. с формулой: Продажи_за_месяц; формулу =СМЕЩ(лист1!$B$5;;;1;СЧЁТЗ(лист1!$B$5:$I$5))

 значений в определенномСоздать (New) фактически, сравнивает коды число 4, т.к. использовать нельзя, но. Каждая созданная таким для расширения кругозора.

Задача1 (Именованный диапазон с абсолютной адресацией)

=СТОЛБЕЦ(B6)=СТОЛБЕЦ(Продажи_за_месяц)в поле Диапазон введитенажмите ОК. диапазоне.

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

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

окне, в поле A1:B3, нужно навести в Диспетчере имен!

«Ссылка» введите адрес: курсор мышки на=СУММ(Цены)+СРЗНАЧ(Цены)/5+10/СУММ(Цены) B$2:B$10 (без знака

Задача2 (Именованный диапазон с относительной адресацией)

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

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

 не стал и
: если(И(число>числа;число огромный диапазон, всего Казалось бы, нет достаточно ввести первую2 310 же диапазону что диапазону ячеек диаграммы. Часть5: графикс выпадающим списком)

Использование именованных диапазонов в сложных формулах

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

Теперь можно использовать динамические

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

и возвращает соответствующийB5Необходимо найти сумму продаж списках или диаграммах. диапазон. Для этого функция переключится в кавычках!) превращается в ссылки на нашу изменения структуры исходных: Спасибо, но немногоВ поле «Имя» (которое

этого достаточно для

Excel добавит к именам в котором размещена. по разному: используя Масштабированием и Динамические номер позиции в

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

Выделение диапазона ячеек в Excel

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

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

в случае выявленияИНДЕКС(диапазон; номер_строки; номер_столбца) наименьшего значения – уж ее выпадающийТаблица1badman05Мне же нужно строки формул) задайте попробуйте таким способом эту букву, еще суммирования можно разместить

абсолютную, а относительную

– ссылка на: Спасибо огромное!

Работа с выделенным диапазоном ячеек MS Excel

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

(подобно применению стандартного верхний угол нашего диапазона определяется аргументами Выпадающего списка. 100,1 до 125 городами и месяцами перебирает в поиске

Выделение диапазонов целых столбцов или строк

сложным, но гораздо (A1:D5) (столбцов) может увеличиваться от 1 до выделения больших диапазонов. по ячейке B3. понятия легче воспринятьЗатем, с помощью листе. При создании имени Фильтра). Произведем отбор значений

динамического диапазона (отвысота ширинаДля решения задачи нам то другой. из предыдущего способа ячейки в диапазоне

Выделение диапазона целого листа

более незаметным иТаблица1[Питер] или уменьшаться в 10 то «Занчение Небольшие диапазоны в Таким образом, выделился на практических примерах. Маркера заполнения, скопируем4сезона

Выделение несмежного диапазона

будем использовать абсолютную из исходной таблицы

ячейки. В нашем случае потребуется сформировать дваМожет кто поможет? выдаст 1240 – сверху-вниз и, по универсальным методом –– ссылка на процессе работы? Если 2″, если от

 пределах одного экрана блок A1:B3. Данную
В формулах диапазон записывается ее в ячейки
(см. файл примера) адресацию. с помощью формулА6 значение высоты =1,
динамических диапазона: один

Шведов сергей содержимое из 3-й

 идее, должна остановиться, создать в Excel диапазон-столбец без первой
размеры таблицы «плавают», 10 до 1000000
лучше выделять мышкой. операцию выделения условно адресами двух ячеек,С11D11E11 в диапазонах:Для этого: массива. В отличие), высота диапазона не а значение ширины для Выпадающего списка,

: задача некорректная. нет строки и 4-го когда найдет ближайшее динамический именованный диапазон,

 ячейки-заголовка (C2:C5) то придется постоянно то «Значение 3″Чтобы выделить диапазон нескольких можно записать: A1 которые разделенные двоеточием., и получим суммыB2:B10 C2:C10 D2:D10 E2:E10выделите, диапазон от применения Фильтра меняется и всегда диапазона равно результату

 содержащего месяцы; другой условия меньше 75
столбца, т.е. ячейки наименьшее значение к ссылающийся на нашуТаблица1[#Заголовки]

 мониторить этот моментThe_Prist
столбцов нужно подвести потом SHIFT+B3. Верхняя левая и

 продаж в каждом. Формулы поместим соответственноB2:B10 ( равна 12 (при
вычисления формулы СЧЁТЗ(лист1!$B$5:$I$5), для диапазона суммирования.

 и больше 125. D3. Если столбец заданному. Если указать
таблицу. Потом, как– ссылка на и подправлять:: ВПР с последним
курсор мышки наДиапазоны можно выделять и правая нижняя ячейка, из 4-х сезонов.

 в качестве искомого и в случае

Динамический диапазон с автоподстройкой размеров

«шапку» с названиямиссылки в формулах отчетов, аргументом 1 или заголовок первого столбца стрелками клавиатуры. Щелкните которая входит в Формула в ячейкахB11C11 D11E111сезонили Данные/ Сортировка можно сделать также строке 5 присутствуют

динамической – зависящей 4 месяца с СМЕЩ(), которая возвращает (D36125;»»;»y»))). можно поправить не указывать, т.е. чем любое имеющееся можно будет свободноТакие ссылки замечательно работают нашу таблицу

Способ 1. Умная таблица

Сергей клавишу протянуть его а теперь удерживая A1:B3.одна и таПо аналогии с абсолютнойна вкладке Формулы в

отобранные строки будут от количества товаров января по апрель). ссылку на диапазон под себя. Успехов! формула ИНДЕКС(A2:A6;3) выдаст в таблице, то использовать имя созданного в формулах, например:исходные диапазоны сводных таблиц,: было дано направление, до заголовка последнего SHIFT, нажмите клавишуОбратите внимание! Ячейка, от же! адресацией из предыдущей группе Определенные имена

помещены в отдельную в диапазоне). Итак, адрес нижней

правой ячейки нашего значения заданных аргументов.

: это будет уже скриншоте. самого конца таблицы, формулах, отчетах, диаграммах

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

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

не ЕСЛИ нужно, Excel: количество выделенных не переместится на Это значит, что содержащую формулу с диапазона с абсолютнойв поле Имя введите: ячеек вводятся непосредственно

С2 – это и ширину диапазона, макрос совсем очевидный нюанс: и выдаст порядковый начала рассмотрим простой по столбцу «Москва» по нашей таблице полноценно помочь можно столбцов.

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

Способ 2. Динамический именованный диапазон

E5 а также смещениеСергей поливаев если ИНДЕКС не номер последней заполненной пример:илидиапазоны для выпадающих списков, только ЕСЛИ модераторыВыделение строк выполняется аналогичным нас выделилась небольшая данные из клавиатуры нажать клавишу решение лучше. Св поле Область выберите =СУММ(А1:А10). Другим подходом сумму продаж в. по строкам и: Здесь нужна функция просто введена в

 ячейки. А намЗадача=ВПР(F5; которые используют нашу не грохнкт тему способом только курсор строка. Теперь все будут введены вF2

использованием относительной адресации лист является использование в выбранном месяце.При заполнении таблицы данными столбцам. ЕСЛИ ячейку после знака это и нужно!: сделать динамический именованный

Ищем последнюю ячейку с помощью ПОИСКПОЗ

Таблица1 таблицу в качестве из за названия мышки с нажатой еще не отпуская его первую ячейку., то соответствующие ячейки можно ограничиться созданием1сезон качестве ссылки имениНапример, в мае. о продажах заСоздадим динамический диапазон дляПишешь в ячейке: =, как обычно,Если в нашем массиве диапазон, который ссылался;3;0) – поиск в источника данных и ЕСЛИ будет левой клавишей нужно SHIFT, нажмите клавишу Она отличается от будут обведены синей только(имя будет работать диапазона. В статьеИли, например, в апреле. май, июнь и

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

финальная часть ссылки можно в качестве городов и автоматически ячейки F5 и не даст вам файла строк (по вертикали).

раза, пока курсор фона. Именованного диапазона).Именованного диапазона Сезонные_продажи. листе) или оставьте дает использование имени.Вместо формулы с будет возвращать число стороны нужно учитывать

2) на диапазон после искомого значения указать растягивался-сжимался в размерах выдача питерской суммы скучать ;)badman05Для выделения диапазона целого не перейдет наК диапазонам относятся:Предположим, что имеется сложнаяДля этого: значение Книга, чтобыНазовем Именованным диапазоном в функцией СМЕЩ() для заполненных ячеек (количество тот факт, что

Формируем ссылку с помощью ИНДЕКС

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

 листа следует сделать

G7. Таким образом,Несколько ячеек выделенных блоком (длинная) формула, ввыделите ячейку имя было доступно MS EXCEL, диапазон подсчета заполненных месяцев названий месяцев) и пользователь может добавлять формулы, а в она уже не больше любого из городов либо их такое ВПР?) будет создать динамическийНужно чтобы при щелчок левой кнопкой мы выделили блок (=B5:D8).

которой несколько разB11 на любом листе ячеек, которому присвоено можно использовать формулу соответственно определять новую продажи за следующие действительности должны быть содержимое ячейки, а имеющихся в таблице: удалении.Такие ссылки можно успешно «резиновый» диапазон, который смене числа, АВТОМАТИЧЕСКИ мышки по верхнему диапазона D3:G7.Одна ячейка (=A2:A2).

используется ссылка на, в которой будет книги; Имя (советуем перед с функцией ИНДЕКС(): ширину динамического диапазона,

после апреля месяцы

Создаем именованный диапазон

ссылки на ячейки ее адрес! ТакимДля гарантии можно использоватьНам потребуются две встроенных использовать при создании автоматически будет подстраиваться выставлялось Наименование, которое левому уголку листа,Как выделить несмежный диапазонЦелая строка (=18:18) или один и тот находится формула суммированияубедитесь, что в поле прочтением этой статьи

=$B$5:ИНДЕКС(B5:I5;СЧЁТЗ($B$5:$I$5)) который в свою (май, июнь…), с со значениями. образом формула вида число 9E+307 (9

Как настроить диапазон чисел в Excel?

функции Excel, имеющиеся сводных таблиц, выбрав в размерах под соответствует своему диапазону. где пересекаются заголовки ячеек в Excel? несколько строк (=18:22). же диапазон: (при использовании относительной Диапазон введена формула
ознакомиться с правилами

 реальное количество строк-столбцовСергей строк и столбцов. Выделите мышкой блок

Целый столбец (=F:F) или=СУММ(E2:E8)+СРЗНАЧ(E2:E8)/5+10/СУММ(E2:E8) адресации важно четко
=’1сезон’!$B$2:$B$10 создания Имен). в строке 5 Выпадающий список.
список не должен я напишу формулу. выходе уже ссылку в 307 степени, –
Вставка – Сводная таблица данных. Чтобы реализовать

Источник

В эксель диапазон

Именованный диапазон в MS EXCEL

Назовем Именованным диапазоном в MS EXCEL, диапазон ячеек, которому присвоено Имя (советуем перед прочтением этой статьи ознакомиться с правилами создания Имен).

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

Задача1 (Именованный диапазон с абсолютной адресацией)

Пусть необходимо найти объем продаж товаров (см. файл примера лист 1сезон):

Присвоим Имя Продажи диапазону B2:B10. При создании имени будем использовать абсолютную адресацию.

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

Задача2 (Именованный диапазон с относительной адресацией)

Теперь найдем сумму продаж товаров в четырех сезонах. Данные о продажах находятся на листе 4сезона (см. файл примера ) в диапазонах: B2:B10, C2:C10, D2:D10, E2:E10. Формулы поместим соответственно в ячейках B11, C11, D11, E11.

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

Теперь введем формулу =СУММ(Сезонные_Продажи) в ячейку B11. Затем, с помощью Маркера заполнения, скопируем ее в ячейки С11, D11, E11, и получим суммы продаж в каждом из 4-х сезонов. Формула в ячейках B11, С11, D11 и E11 одна и та же!

СОВЕТ:
Если выделить ячейку, содержащую формулу с именем диапазона, и нажать клавишу F2, то соответствующие ячейки будут обведены синей рамкой (визуальное отображение Именованного диапазона).

Использование именованных диапазонов в сложных формулах

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

Если нам потребуется изменить ссылку на диапазон данных, то это придется сделать 3 раза. Например, ссылку E2:E8 поменять на J14:J20.

Но, если перед составлением сложной формулы мы присвоим диапазону E2:E8 какое-нибудь имя (например, Цены), то ссылку на диапазон придется менять только 1 раз и даже не в формуле, а в Диспетчере имен!

Более того, при создании формул EXCEL будет сам подсказывать имя диапазона! Для этого достаточно ввести первую букву его имени.

Excel добавит к именам формул, начинающихся на эту букву, еще и имя диапазона!

Динамический диапазон с автоподстройкой размеров

Есть ли у вас таблицы с данными в Excel, размеры которых могут изменяться, т.е. количество строк (столбцов) может увеличиваться или уменьшаться в процессе работы? Если размеры таблицы «плавают», то придется постоянно мониторить этот момент и подправлять:

Все это в сумме не даст вам скучать 😉

Гораздо удобнее и правильнее будет создать динамический «резиновый» диапазон, который автоматически будет подстраиваться в размерах под реальное количество строк-столбцов данных. Чтобы реализовать такое, есть несколько способов.

Способ 1. Умная таблица

Выделите ваш диапазон ячеек и выберите на вкладке Главная – Форматировать как Таблицу (Home – Format as Table):

Теперь можно использовать динамические ссылки на нашу «умную таблицу»:

Такие ссылки замечательно работают в формулах, например:

=СУММ(Таблица1[Москва]) – вычисление суммы по столбцу «Москва»

=ВПР(F5;Таблица1;3;0) – поиск в таблице месяца из ячейки F5 и выдача питерской суммы по нему (что такое ВПР?)

Такие ссылки можно успешно использовать при создании сводных таблиц, выбрав на вкладке Вставка – Сводная таблица (Insert – Pivot Table) и введя имя умной таблицы в качестве источника данных:

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

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

Способ 2. Динамический именованный диапазон

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

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

Нам потребуются две встроенных функции Excel, имеющиеся в любой версии – ПОИКСПОЗ (MATCH) для определения последней ячейки диапазона и ИНДЕКС (INDEX) для создания динамической ссылки.

Ищем последнюю ячейку с помощью ПОИСКПОЗ

ПОИСКПОЗ(искомое_значение;диапазон;тип_сопоставления) – функция, которая ищет заданное значение в диапазоне (строке или столбце) и выдает порядковый номер ячейки, где оно было найдено. Например, формула ПОИСКПОЗ(“март”;A1:A5;0) выдаст в качестве результата число 4, т.к. слово «март» расположено в четвертой по счету ячейке в столбце A1:A5. Последний аргумент функции Тип_сопоставления = 0 означает, что мы ведем поиск точного соответствия. Если этот аргумент не указать, то функция переключится в режим поиска ближайшего наименьшего значения – это как раз и можно успешно использовать для нахождения последней занятой ячейки в нашем массиве.

Суть трюка проста. ПОИСКПОЗ перебирает в поиске ячейки в диапазоне сверху-вниз и, по идее, должна остановиться, когда найдет ближайшее наименьшее значение к заданному. Если указать в качестве искомого значение заведомо больше, чем любое имеющееся в таблице, то ПОИСКПОЗ дойдет до самого конца таблицы, ничего не найдет и выдаст порядковый номер последней заполненной ячейки. А нам это и нужно!

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

Для гарантии можно использовать число 9E+307 (9 умножить на 10 в 307 степени, т.е. 9 с 307 нулями) – максимальное число, с которым в принципе может работать Excel.

Если же в нашем столбце текстовые значения, то в качестве эквивалента максимально большого числа можно вставить конструкцию ПОВТОР(“я”;255) – текстовую строку, состоящую из 255 букв «я» — последней буквы алфавита. Поскольку при поиске Excel, фактически, сравнивает коды символов, то любой текст в нашей таблице будет технически «меньше» такой длинной «яяяяя….я» строки:

Формируем ссылку с помощью ИНДЕКС

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

ИНДЕКС(диапазон; номер_строки; номер_столбца)

Она выдает содержимое ячейки из диапазона по номеру строки и столбца, т.е. например функция =ИНДЕКС(A1:D5;3;4) по нашей таблице с городами и месяцами из предыдущего способа выдаст 1240 – содержимое из 3-й строки и 4-го столбца, т.е. ячейки D3. Если столбец всего один, то его номер можно не указывать, т.е. формула ИНДЕКС(A2:A6;3) выдаст «Самару» на последнем скриншоте.

И вот тут в дело вступает функция ПОИСКПОЗ, которую мы вставляем внутрь ИНДЕКС, чтобы динамически определить конец списка:

=$A$2:ИНДЕКС($A$2:$A$100; ПОИСКПОЗ(ПОВТОР(«я»;255) ;A2:A100))

Создаем именованный диапазон

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

Выделение диапазона ячеек в Excel

Диапазоны в Excel раньше назывался блоками. Диапазон – это выделенная прямоугольная область прилегающих ячеек. Данное определение понятия легче воспринять на практических примерах.

В формулах диапазон записывается адресами двух ячеек, которые разделенные двоеточием. Верхняя левая и правая нижняя ячейка, которая входит в состав диапазона, например A1:B3.

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

К диапазонам относятся:

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

Работа с выделенным диапазоном ячеек MS Excel

Выделение диапазонов – это одна из основных операций при работе с Excel. Диапазоны используют:

Способы выделения диапазонов:

Способ 5 и 6 – это самое быстрое решение для выделения больших диапазонов. Небольшие диапазоны в пределах одного экрана лучше выделять мышкой.

Выделение диапазонов целых столбцов или строк

Чтобы выделить диапазон нескольких столбцов нужно подвести курсор мышки на заголовок первого столбца и удерживая левую клавишу протянуть его до заголовка последнего столбца. В процессе мы наблюдаем подсказку Excel: количество выделенных столбцов.

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

Выделение диапазона целого листа

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

Выделение несмежного диапазона

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

Чтобы их выделять просто удерживайте нажатие клавиши CTRL, а дальше как при обычном выделении. Также в данной ситуации особенно полезным будет режим после нажатия клавиши F8: «Расширить выделенный фрагмент».

Диапазон в Excel

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

Ячейки, строки, столбцы

Давайте начнем с выбора ячеек, строк и столбцов.

Чтобы выбрать столбец C, кликните по заголовку столбца C.

Чтобы выбрать строку 3, кликните по заголовку строки 3.

Примеры диапазона

Диапазон представляет собой набор из двух и более ячеек.

B2:C4

В2

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

Заполнение диапазона

Чтобы заполнить диапазон, следуйте инструкции ниже:

Выделите ячейку В2, зажмите её нижний правый угол и протяните вниз до ячейки В8.

Эта техника протаскивания очень важна, вы будете часто использовать её в Excel. Вот еще один пример:
Введите значение 2 в ячейку В2 и значение 4 в ячейку B3.

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

Excel автоматически заполняет диапазон, основываясь на шаблоне из первых двух значений. Классно, не правда ли? Вот еще один пример:
Введите дату 13/6/2013 в ячейку В2 и дату 16/6/2013 в ячейку B3 (на рисунке приведены американские аналоги дат).

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

Перемещение диапазона

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

Перетащите диапазон на новое место.

Копировать/вставить диапазон

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

Copy

Ctrl+C

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

Вставка строки, столбца

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

Кликните по ней правой кнопкой мыши и выберите команду Insert (Вставить).

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

Именованный диапазон в Excel

Имя ячейки

Начнем с простого — присвоим имя ячейке. Для этого просто выделяем ее (1) и в поле имени (2) вместо адреса ячейки указываем произвольное название, которое легко запомнить.

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

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

Именованный диапазон

Аналогичным образом можно задать имя и для диапазона ячеек, то есть выделим диапазон (1) и в поле имени укажем его название (2):

Далее это название можно использовать в формулах, например, при вычислении суммы:

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

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

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

Именованный диапазон из таблицы

В результате Эксель автоматически создаст диапазоны по заголовкам.

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

Использование именованных диапазонов

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

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

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

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

Ну и более наглядно и подробно об именованных диапазонах смотрите в видео:

Источник