Диапазон листов excel

Создание ссылки на один и тот же диапазон ячеек на нескольких листах

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

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

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

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

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

В примерах ниже объясняется, что происходит при вставке, копирование, удаление или перемещение листов, которые содержатся в трехмерной ссылке. Каждый из этих примерах используется формула = СУММ (Лист2: Лист6! A2: a5 полученное) для добавления ячейки с A2 по A5 на листах 2 – 6:

Вставка или копирование — Если вставлять или копировать листы между листами 2 и 6 (конечные точки в данном примере), Excel, включает в себя все значения в ячейках с A2 по A5 составные листов в вычислениях.

Удаление — Если удалить листы между листами 2 и 6, Microsoft Excel вычтет из суммы.

Перемещение — При перемещении оказались диапазон листа, на который указывает ссылка листы между листами 2 и 6, Microsoft Excel вычтет из суммы.

Перемещение конечного листа — Если переместить лист 2 или 6 в другое место в той же книге, Microsoft Excel скорректирует сумму с включить новые листы между ними, если только изменить порядок конечные точки в книге. Если отразить конечную точку, трехмерной ссылки изменяется на листе конечную точку. Например имеется ссылка на Лист2: Лист6. При перемещении Лист2 находится после Sheet6 в книге, формула будет настроить, чтобы они указывали Sheet3:Sheet6. При перемещении Sheet6 перед листом 2, формула будет настроить, чтобы они указывали Sheet2:Sheet5.

Удаление конечного листа — . Если удалить лист 2 или 6, Microsoft Excel удаляет значения удалятся из вычислений.

Щелкните ячейку, в которую вводятся функцию.

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

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

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

Выделите диапазон ячеек, на которые нужно создать ссылку.

Завершите ввод формулы, а затем нажмите клавишу ВВОД.

В трехмерной ссылке могут быть использованы следующие функции:

Вычисляет среднее арифметическое чисел.

Вычисляет среднее арифметическое чисел с учетом логических значений и текстовых строк.

Подсчитывает количество ячеек, содержащих числа.

Подсчитывает количество непустых ячеек.

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

Возвращает эксцесс множества данных.

Возвращает k-ое по величине значение из множества данных. Например, пятое наибольшее число.

Находит наибольшее значение из набора значений.

Находит наибольшее значение из набора значений с учетом логических значений и текстовых строк.

Возвращает медиану — число, которое является серединой заданного множества чисел.

Находит наименьшее значение из набора значений.

Находит наименьшее значение из набора значений с учетом логических значений и текстовых строк.

Возвращает k-ю процентиль для значений диапазона, где k — число в диапазоне от 0 до 1, исключая границы. Предоставлена для совместимости с предыдущими версиями Excel.

Возвращает k-ю процентиль для значений диапазона, где k — число от 0 и 1 (не включая эти числа).

Возвращает k-ю процентиль для значений диапазона, где k — число от 0 и 1 (включая эти числа).

Возвращает ранг значения в наборе данных как процентное содержание в наборе данных (от 0 до 1). Предоставлена для совместимости с предыдущими версиями Excel.

Возвращает ранг значения в наборе данных как процентное содержание в наборе данных (от 0 до 1, не включая эти числа).

Возвращает ранг значения в наборе данных как процентное содержание в наборе данных (от 0 до 1, включая эти числа).

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

Возвращает квартиль набора данных на основе значений процентили от 0 до 1 (исключая эти числа).

Возвращает квартиль набора данных на основе значений процентили от 0 до 1 (включительно).

Вычисляет произведение чисел.

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

Читать еще: Как выделить данные в таблице excel

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

Возвращает асимметрию распределения.

Возвращает k-ое наименьшее значение в множестве данных.

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

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

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

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

Возвращает среднее внутренности множества данных.

Вычисляет сумму чисел.

Оценивает дисперсию по выборке.

Вычисляет дисперсию для генеральной совокупности.

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

Вычисляет дисперсию для генеральной совокупности; логических значений и текстовых строк

На вкладке формулы щелкните Присвоить имя (в группе Определенные имена ).

Во всплывающем окне Новое имя введите имя в качестве ссылки. Имена могут быть длиной до 255 знаков.

В списке диапазон выберите знак равенства (=) и ссылки, а затем нажмите клавишу BACKSPACE.

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

Удерживая нажатой клавишу Shift и перейдите на вкладку ссылки на лист.

Выделите диапазон ячеек, на которые нужно создать ссылку.

Дополнительные сведения

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

Как получить диапазон листов со следующим условием для формулы в Excel? :
листы, имя которых содержит определённую подстроку , равную значению из некоторой ячейки.

P.S Т.е пользователь вводит значение в заранее определенную ячейку метку для поиска листов и автоматически формула применяется только к определенным листам. Для фото выше это будет например слово «Компонент», т.е это будут все листы содержащие подстроку — Компонент

Покажите пример такой формулы пожалуйста.

Вопрос задан более года назад
123 просмотра

Вообще мне известно четыре способа получения имени листов таблицы.
Три тут:
https://www.datanumen.com/blogs/3-quick-ways-to-ge.
— вручную
— через name manager и формулы
— через VBA

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

через name manager и формулы тоже нужно использовать макрос «GET.WORKBOOK»

Да, но она штатная. С ее помощью вполне легко именно автоматически получить список листов.
Кодинга не требуется. Макросы разрешать не нужно, емнип. Содержащая такое решение книга сохраняется в xlsx, нее в xlsb. Так что сложностей с ним не вижу.

А вообще вопрос задан очень обще, не совсем понятно, что автору нужно на выходе — названия листов или сами листы в отдельном файле )

Вообще по всем признакам похоже на домашнее задание 🙂

Xambey, честно, не очень представляю
^)

Если это практическая задача, неплохо бы понять, зачем именно так, и для чего — тогда можно предложить подходящее решение.

John Smith, нет, это не домашнее задание:) Пытаюсь автоматизировать отчет по тестированию и есть необходимость не писать некоторые формулы по 50 раз. И если получиться использовать список листов для СЧЁТЕСЛИ(range, object), то это сильно упростит работу моему отделу.

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

Раз такое дело, даже скину конкретный файл. На листе Summary в таблице Тесты функционала для второго столбца не хочется добавлять по отдельной формуле для каждого листа. Хотелось бы просто на листе Initial добавить 1 ячейку, куда записывается метка для поиска листов по имени — типо как «Компонент». И тогда все листы имеющие в имени слово Компонент обрабатываются в таблице тестов. Как то так:) Файл: https://yadi.sk/d/RS_MmckmDDOfNA

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

Я бы делал через макрос VBA, тем более, что у вас уже xlsm — это гораздо удобнее и эффективнее (не говоря о том, что если формулами что-то такое и можно сделать, в чем я начинаю сомневаться, то это будет монструозно).

Решение легко написать, предварительно погуглив
«excel vba enumerate sheets»
«excel vba index sheet»
Затем в код добавить проверку на наличие подстроки «Компонент», и обработку таких листов.

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

Допустим, у нас есть два листа:
Лист1
Лист2
На листе Лист1 в А1 пишем «Лист2» без кавычек.
На листе Лист2 в А1 пише «ФФФ» без кавычек
На листе Лист1 в А2 пишем формулу
=ДВССЫЛ(A1&»!A1″)
Получаем в ячейке значение ФФФ.

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

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

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

Читать еще: Как посчитать количество листов в excel

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

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

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

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

Если вам не нужен полосатый дизайн, который добавляется к таблице побочным эффектом, то его можно отключить на появившейся вкладке Конструктор (Design). Каждая созданная таким образом таблица получает имя, которое можно заменить на более удобное там же на вкладке Конструктор (Design) в поле Имя таблицы (Table Name) .

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

Таблица1 – ссылка на всю таблицу кроме строки заголовка (A2:D5)
Таблица1[#Все] – ссылка на всю таблицу целиком (A1:D5)
Таблица1[Питер] – ссылка на диапазон-столбец без первой ячейки-заголовка (C2:C5)
Таблица1[#Заголовки] – ссылка на «шапку» с названиями столбцов (A1:D1)

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

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

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

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

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

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

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

Способ 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) выдаст «Самару» на последнем скриншоте.

Читать еще: Моделирование монте карло в excel

Причем есть один не совсем очевидный нюанс: если ИНДЕКС не просто введена в ячейку после знака =, как обычно, а используется как финальная часть ссылки на диапазон после двоеточия, то выдает она уже не содержимое ячейки, а ее адрес! Таким образом формула вида $A$2:ИНДЕКС($A$2:$A$100;3) даст на выходе уже ссылку на диапазон A2:A4.

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

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

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

Осталось упаковать все это в единое целое. Откройте вкладку Формулы (Formulas) и нажмите кнопку Диспетчер Имен (Name Manager) . В открывшемся окне нажмите кнопку Создать (New) , введите имя нашего диапазона и формулу в поле Диапазон (Reference) :

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

Создание и ведение таблиц Excel

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

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

Создание таблицы

Выделить любую ячейку, содержащую данные, которые должны будут войти в таблицу.
В ленте меню выбрать вкладку Вставка [Insert], в раскрывшейся группе команд Таблицы [Tables] необходимо выбрать команду Таблица [Table].

Появится диалоговое окно, в котором Excel автоматически предложит границы диапазона данных для таблицы

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

ОК.

Присвоение имени таблице

По умолчанию при создании таблицы Excel ей присваивается стандартное имя: Таблица1, Таблица2 и т.д. Если имеется только одна таблица, то можно ограничиться этим именем. Но удобнее присвоить таблице содержательное имя.

Выделить ячейку таблицы.
На вкладке Конструктор [Design], в группе Свойства [Properties] ввести новое имя таблицы в поле Имя таблицы нажать клавишу Enter.

Требования к именам таблиц аналогичны требованиям к именованным диапазонам.

Форматирование таблиц

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

Выделить ячейку таблицы.
На вкладке Конструктор [Design] выбрать нужное оформление в группе Стили таблиц [Table Styles].

Вычисления в таблицах

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

На вкладке Конструктор [Design] в группе Параметры стилей таблиц [Table Style Options], выбрать Строка итогов [Total Row].

В появившейся новой строке Итог [Total] выбрать поле, в котором нужно обработать данные, и в раскрывающемся меню выбрать нужную функцию.

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

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

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

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

Выбрать вкладку Файл [File] или кнопку Офис [Office], в зависимости от версии Excel; затем вкладку Параметры [Options].
В разделе Формулы [Formulas], в группе Работа с формулами [Working with formulas], отметить пункт Использовать имена таблиц в формулах [Use table name in formulas].
OK.

Преобразование таблицы в обычный диапазон

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