Excel как задать диапазон листов - Word и 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.

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

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

Возвращает 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, размеры которых могут изменяться, т.е. количество строк (столбцов) может увеличиваться или уменьшаться в процессе работы? Если размеры таблицы «плавают», то придется постоянно мониторить этот момент и подправлять:

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

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

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

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

Причем есть один не совсем очевидный нюанс: если ИНДЕКС не просто введена в ячейку после знака =, как обычно, а используется как финальная часть ссылки на диапазон после двоеточия, то выдает она уже не содержимое ячейки, а ее адрес! Таким образом формула вида $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.

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

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

На вкладке Конструктор [Design] выбрать группу Сервис [Tools].
Выбрать вкладку Преобразовать в диапазон [Convert to Range].

Нажать на кнопку Да [Yes].

Источник

Как в офисе.

Возможно, вам приходилось работать с листами, в которых использовалась, формула типа: =СУММ(А5000:А5078). Вы гадали, что же находится в ячейках А5000:А5078!? Если в ячейках А5000:А5078 содержатся объемы продаж по регионам, не кажется ли вам формула =СУММ(ПродажиРегионы) более понятной? В данной главе описываются способы присвоения имен отдельным ячейкам и диапазонам ячеек, а также способы вставки имен диапазонов в формулы. [1]

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

Существуют три способа создания именованных диапазонов:

путем ввода имени диапазона в поле Имя;
путем выбора на вкладке ФОРМУЛЫ в группе Определенные имена инструмента Создать из выделенного;
путем выбора на вкладке ФОРМУЛЫ в группе Определенные имена инструментов Присвоить имя или Диспетчер имен.

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

Рис. 1.1. Создание имени диапазона путем выбора диапазона ячеек и ввода имени в поле Имя

Скачать заметку в формате Word или pdf, примеры в формате Excel

При нажатии в поле Имя на стрелку раскрывающегося списка появятся имена диапазонов, определенные в текущей книге (рис. 1.2). При выборе в поле Имя имени диапазона все ячейки, соответствующие этому диапазону, отмечаются автоматически. Это позволяет убедиться в правильности выбора ячейки или диапазона ячеек для указанного имени. В именах диапазонов регистр не учитывается. Например, если выбрать имя Финиш, будет отмечена ячейка Е8 (рис. 1.3).

Рис. 1.2. Список имен диапазонов

Рис. 1.3. При выборе имени диапазона отмечаются все ячейки, соответствующие этому диапазону

При нажатии клавиши открывается диалоговое окно Вставка имени, в котором отображаются имена всех диапазонов.

Присвоение имени означает, что вместо любой ссылки Старт в формуле будет автоматически подставлено значение из ячейки В3.

Предположим, что необходимо присвоить имя Данные прямоугольному диапазону ячеек A1:B5. Выделите диапазон ячеек A1:B5, введите с клавиатуры Данные в поле Имя и нажмите клавишу . Теперь с помощью формулы =СРЗНАЧ(Данные) можно вычислить среднее значение содержимого ячеек A1:B4 (рис. 1.4).

Рис. 1.4. Присвоение диапазону A1:B5 имени Данные и нахождение среднего значения именованного диапазона

Иногда требуется присвоить имя диапазону ячеек, состоящему из нескольких несмежных прямоугольных диапазонов. Например, B3:C4, E6:G7 и B10:C10 (рис. 1.5). Для присвоения имени выделите любой из трех прямоугольников. Удерживая клавишу , выделите остальные два диапазона. Отпустите клавишу , введите имя Несмежный в поле Имя и нажмите клавишу . Теперь имя Несмежный в любой формуле указывает на содержимое ячеек B3:C4, E6:G7 и B10:C10.

Рис. 1.5. Присвоение имени несмежному диапазону ячеек

Создание имен с помощью инструмента Создать из выделенного. На листе «Рис. 1.6» Excel-файла с примерами содержатся продажи за март для каждого из 50 штатов США (рис. 1.6). Требуется присвоить каждой ячейке в диапазоне B2:B51 сокращенное название штата. Выделите диапазон A2:B51 и на вкладке ФОРМУЛЫ в группе Определенные имена выберите инструмент Создать из выделенного, и затем в открывшемся диалоговом окне установите флажок в столбце слева.

Рис. 1.6. Создание имен с помощью инструмента Создать из выделенного

Теперь имена в первом столбце выделенного диапазона связаны с ячейками во втором столбце выделенного диапазона. Таким образом, ячейке B6 присвоено имя диапазона СА, ячейка B7 имеет имя СО и т.д. Создавать имена таких диапазонов с помощью поля Имя было бы невероятно утомительно! Нажмите на стрелку раскрывающегося списка в поле Имя и убедитесь, что все имена диапазонов созданы.

Создание имен диапазонов с помощью инструмента Присвоить имя. Если на вкладке ФОРМУЛЫ в группе Определенные имена выбрать инструмент Диспетчер имен (и затем нажать кнопку Создать) или инструмент Присвоить имя, откроется диалоговое окно Создание имени (рис. 1.7).

Предположим, требуется присвоить имя область1 диапазону ячеек A2:B7. Введите область1 в поле Имя, переместите курсор в поле Диапазон, и выделите диапазон на листе или введите с клавиатуры =A2:B7. Нажмите кнопку OK для завершения присваивания.

Рис. 1.7. Диалоговое окно Создание имени

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

Рис. 1.8. Выбор области действия имени

Диспетчер имен

В Microsoft Excel 2013 существует простой способ изменения или удаления имен диапазонов. Перейдите на вкладку ФОРМУЛЫ, выберите группу Определенные имена и откройте Диспетчер имен. Появится список имен всех диапазонов (рис. 1.9).

Рис. 1.9. Диспетчер имен

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

Редактирование формул в диалоговых окнах

Когда Excel отображает диалоговое окно (например, как на рис. 1.7 или 1.9), в котором можно записать ссылку на диапазон, поле, содержащее такую ссылку, всегда находится в режиме указания. Если активизировать поле Диапазон и воспользоваться стрелками для редактирования ссылки на диапазон, то вы обнаружите, что при этом вы именно указываете на диапазон, а не редактируете текст ссылки. Если на рисунке ниже вы поместите курсор в поле Диапазон, то попытка двинуть курсор влево с помощью стрелки даст неожиданный результат. Вместо движения курсора произошло изменение ссылки (обратите внимание: актуальный режим указан в левой части статусной панели):

Что делать? Нажмите F2. [2] Клавиша F2 позволяет переключаться между режимом указания (ввод) и режимом редактирования (правка). В режиме редактирования стрелки действуют именно так, как при редактировании формулы. На рисунке ниже попытка двинуть курсор влево увенчалась успехом:

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

1. Необходимо вычислить общий объем продаж в штатах Аризона, Калифорния, Монтана, Нью-Йорк и Нью-Джерси.

Если вы помните наизусть сокращенные наименования штатов, то можно использовать формулу =AZ+CA+MT+NY+NJ (рис. 1.10)

Рис. 1.10. Использование имен вычисления объема продаж в отдельных штатах

2. Необходимо определить среднюю доходность акций, казначейских векселей и облигаций.

Выделите диапазон ячеек B2:D84 (рис. 1.11, часть строк на рис. скрыта), перейдите на вкладку ФОРМУЛЫ в группе Определенные имена выберите инструмент Создать из выделенного. В этом примере имена диапазона указаны в строке выше. Диапазон B3:B84 получает имя Акции, диапазон C3:C84 — имя Векселя и диапазон D3:D84 — имя Облигации. Таким образом, необходимость помнить, где находятся данные, отпадает. Например, если после начала ввода в ячейку B86 формулы нажать клавишу , откроется диалоговое окно Вставка имени. Кроме того, можно вызвать на экран список доступных имен диапазонов, если после начала ввода на вкладке ФОРМУЛЫ в группе Определенные имена выбрать инструмент Использовать в формуле. И, наконец, если вы помните первые буквы имени диапазона, и начнете их вводить в формуле, Excel выдаст подсказку (рис. 1.12). Эта опция Excel называется автозавершение формул. Для завершения ввода имени диапазона дважды щелкните на имени Векселя. Удобство использования имен диапазонов заключается в том, что, не зная точно, где находятся данные, можно работать с данными в любом месте книги!

Рис. 1.11. Исторические данные по инвестициям

Рис. 1.12. Подсказка при вводе в формуле имени диапазона

3. Использование имен столбца и строки

При использовании в формуле имени столбца (в формате A:A, C:C и т.д.) весь столбец обрабатывается в Excel как именованный диапазон. Например, по формуле =СРЗНАЧ(A:A) вычисляется среднее значение всех чисел в столбце А. Использование имени диапазона для целого столбца очень эффективно при частом вводе новых данных в столбец. Например, если столбец A содержит данные о ежемесячных продажах продукта, то новые данные добавляются каждый месяц, и по такой формуле вычисляется актуальное среднее значение ежемесячных продаж. Однако будьте осторожны: если ввести формулу =СРЗНАЧ(А:А) в столбец А, то появится сообщение о циклической ссылке, т.к. значение в ячейке, содержащей формулу расчета среднего, будет зависеть от ячейки, содержащей среднее значение. Способ разрешения циклических ссылок см. Excel. Как найти циклическую ссылку. Аналогично, по формуле =СРЗНАЧ(1:1) рассчитывается среднее значение всех чисел в строке 1.

4. Имена с областью действия книга и лист

При создании имен с помощью поля Имя областью действия имен по умолчанию становится Книга. Однако, можно присвоить одно и тоже имя на разных листах, выбрав область действия Лист. Например, создайте новую книгу Excel, содержащую три листа, и введите числа 4, 5, 6 в ячейки E4:E6 на листе Лист1 и 3, 4, 5 в ячейки E4:E6 на листе Лист2. Затем откройте окно Диспетчер имен, присвойте имя jam ячейкам E4:E6 на листе Лист1 и определите область действия для этого имени как Лист1. Далее перейдите на Лист2, откройте окно Диспетчер имен, присвойте имя jam ячейкам E4:E6 и определите область действия для этого имени как Лист2. Диалоговое окно Диспетчер имен показано на рис. 1.13.

Рис. 1.13. Имена на уровне Листа

Что произойдет, если ввести формулу =СУММ(jam) на каждом из трех листов? На листе Лист1 по формуле =СУММ(jam) будут просуммированы значения ячеек E4:E6 листа Лист1. Так как в этих ячейках содержатся числа 4, 5 и 6, в сумме получится 15. На листе Лист2 по формуле =СУММ(jam) будут просуммированы значения ячеек E4:E6 листа Лист2, что в сумме даст 3 + 4 + 5 = 12. Однако на листе Лист3 вычисление по формуле =СУММ(jam) приведет к появлению сообщения об ошибке #имя?, поскольку на этом листе отсутствует диапазон с именем jam. Если где-либо на листе Лист3 ввести формулу =СУММ(лист2!jam), Excel распознает имя на уровне листа, которое представляет диапазон ячеек E4:E6 листа Лист2, и в результате получится 3 + 4 + 5 = 12. Таким образом, указав перед именем диапазона соответствующее имя листа с восклицательным знаком (!), можно обратиться к диапазону на листе, отличном от того листа, где диапазон был определен.

5. Как добиться отображения недавно созданных имен диапазонов в ранее созданных формулах?

Рассмотрим небольшую таблицу, содержащую формулы (рис. 1.14).

Рис. 1.14. Новые имена диапазонов в старых формулах

Ячейка F3 содержит цену продукта, а ячейка F4 — потребность в продукте =10000–300*F3. В ячейки F5 и F6 введена себестоимость единицы продукции и постоянные затраты, соответственно. Прибыль вычисляется в ячейке F7 по формуле =F4*(F3–F5)–F6. Выделите диапазон E3:F7, затем для присвоения ячейке F3 имени цена, ячейке F4 имени потребность, ячейке F5 имени себестоимость, ячейке F6 имени затраты и ячейке F7 имени прибыль используйте вкладку ФОРМУЛЫ, инструмент Создать из выделенного и флажок в столбце слева. Теперь имена созданных диапазонов необходимо отобразить в формулах ячеек F4 и F7. Для применения имен сначала выделите диапазон, для которого они создаются (в данном случае F3:F7). Затем на вкладке ФОРМУЛЫ в группе Определенные имена нажмите стрелку раскрывающегося списка Присвоить имя и выберите инструмент Применить имена. Выделите в окне имена, которые требуется применить, и нажмите кнопку OK. Обратите внимание, что в ячейке F4 теперь находится формула =10000-300*цена, а в ячейке F7 формула =потребность*(цена–себестоимость)–затраты, что и требовалось. [3]

6. Можно ли вывести на лист Excel список всех имен диапазонов (и представляемых ими ячеек)?

Откройте окно Вставка имени с помощью клавиши и нажмите кнопку Все имена (рис. 1.15). На листе, начиная с текущей ячейки, появится список имен диапазонов и соответствующих им ячеек.

Рис. 1.15. Вывод на лист Excel список всех имен диапазонов (и представляемых ими ячеек)

7. Использование формул для определения диапазона

Пример 1. Предполагаемый годовой доход вычисляется как кратный прошлогоднему доходу (рис. 1.16). Воспользуемся формулу =(1+прирост)*предыдущий_год (имя диапазона не может содержать пробел). Требуется вычислить доходы за 2012–2018 гг. с приростом 10% в год, начиная с базового уровня 300 млн. долларов в 2011 г.

Сначала в поле Имя присвойте ячейке B3 имя прирост. Теперь самое интересное! Переместите курсор в ячейку B7 и на вкладке ФОРМУЛЫ в группе Определенные имена выберите инструмент Присвоить имя для открытия диалогового окна Создание имени. Введите данные, как показано на рис. 1.16. Поскольку активной является ячейка B7, Excel всегда будет интерпретировать имя диапазона как указывающее на ячейку, находящуюся над текущей ячейкой. Это не будет работать, если в ссылке на ячейку B6 останется знак доллара, поскольку он не позволит изменить ссылку на строку и указать строку непосредственно над активной ячейкой (подробнее см. Относительные, абсолютные и смешанные ссылки на ячейки в Excel. Если в ячейку B7 ввести формулу =предыдущий*(1+прирост) и скопировать ее в диапазон B8:B13, каждая ячейка будет содержать требуемую формулу, по которой содержимое ячейки непосредственно над активной ячейкой будет умножаться на 1,1.

Рис. 1.16. Для любой ячейки это имя указывает на ячейку, находящуюся над активной ячейкой

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

Выберите строку 12 (щелкните слева на 12) и в поле Имя (рядом со строкой формул) введите имя почасовая. Выберите строку 13 и введите в поле Имя – часы. Если теперь в ячейку F14 ввести формулу =почасовая*часы и скопировать эту формулу в диапазон G14:L14, то в каждом столбце автоматически появится результат перемножения значений почасовой оплаты и отработанных часов.

Рис. 1.17. Расчет зарплаты по дням недели

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

Некоторые замечания:

В Excel невозможно использовать в качестве имен диапазонов буквы r и c.
Единственными символами, которые можно использовать в именах диапазонов, являются точка (.) и подчеркивание (_).
При использовании инструмента Создать из выделенного пробелы в созданном имени автоматически будут заменены символами подчеркивания (_). Например, имя Product 1 будет создано как Product_1.
Имена диапазонов не могут начинаться с цифр или выглядеть как ссылка на ячейку. Например, в качестве имен диапазонов невозможно использовать имена 3Q и A4. Кроме того, в Microsoft Excel 2013 имеется более 16 000 столбцов, и такие имена, как cat1, являются недопустимыми, поскольку существует ячейка с именем CAT1. Если попытаться присвоить ячейке имя CAT1, появится сообщение о том, что введено недопустимое имя. В случае необходимости используйте подчеркивание (_) и назовите ячейку cat1_.

Задания для самостоятельной работы

Исходные данные находятся в файле Имена диапазонов. Задания.xlsx

На листе Задание 1 содержатся данные о ежемесячной доходности акций General Motors и Microsoft. Присвойте имена диапазонам, содержащим ежемесячную доходность для каждой акции, и вычислите среднемесячную доходность каждой акции.
На листе Задание 2 присвойте имя Красный диапазону, содержащему ячейки A1:B3 и A6:B8.
На листе Задание 3 в ячейки G5 и G6 введите широту и долготу любого города, а в ячейки G7 и G8 широту и долготу другого города. В ячейке G10 вычисляется расстояние между двумя городами. Определите имена диапазонов для широты и долготы каждого города и убедитесь, что эти имена отображаются в формуле для расчета расстояния.
На листе Задание 4 содержится количество акций для каждого вида акций и цена одной акции. Вычислите стоимость акций для каждого вида по формуле =количество*цена.
На листе Задание 5 создайте имя диапазона для расчета среднего значения продаж за последние пять лет. Измените формулы в ячейках Е14:Е20.

[1] При написании заметки использованы материалы книги Уэйн Л. Винстон. Microsoft Excel 2013. Анализ данных и бизнес-моделирование, глава 1.

[2] При написании этого раздела использованы идеи книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 156.

[3] У меня не получилось воспользоваться указанным методом, поэтому пришлось перенабрать формулы после присвоения имен.

Источник

Задаем область печати в Excel

Смотрите также: Да не вопрос! фильтрах сводной ничего кто знает.Третий вариант принтеры, все ихМожно поставить « чего поставили галочку.. Установить другой порядок размер полей или. Здесь видно, каквниз, затем вправо командой сайта office-guru.ru эта заданная область.Выделите ячейки, которые необходимо напечатать. Чтобы напечататьПечатая книги Excel, начинающие Тогда так:

не выбрано, А4:D6,_Boroda_настройки листа на имена имеются вдвухсторонняя печатьКак напечатать четные страницы листов при распечатывании. везде поставить нольЧтобы будет выглядеть документилиИсточник: http://www.excel-easy.com/examples/print-area.html Область печати сохраняется

Печать активных листов в Excel

напечатать. несколько листов, выберите

пользователи часто сталкиваютсяSerge_007 если выбран только: Вкладка «Разметка страницы», печать смотрите в списке, из него», тогда она напечатает в Excel
Можно указать адрес (получитсяразвернуть таблицу Excel
на бумаге. Войдетвправо, затем внизПеревел: Антон Андронов при сохранении книги.Перейдите к панели
первый лист, затем, с вопросом –: Чего-то не работает

Печать всей книги в Excel

один день, А4:С6, там на ленте статье «Как уменьшить
выбрать нужное название сначала нечетные страницы,. диапазона, части таблицы,лист Excel без полей
, документ в ширину, ли вся таблица.

Печать выделенного фрагмента в Excel

Автор: Антон АндроновЧтобы установить область печати,Печать удерживая клавишуКак же задать областьНо эту тему

если выбрано только найдите слова «Параметры
размер таблицы в принтера. остановится печать.
Нужно поставить галочку который нужно распечатать.). Чтобы нужно воспользоваться функцией или только часть
Совет:Можно указать порядок нумерации
следуйте инструкции ниже:.Ctrl печати?

можно пока прикрыть,а листа» и рядом Excel».Когда все готово,Эти же листы у функции «ВыделенныеКогда настроим всеубрать колонтитулы в Excel «Ориентация», здесь можно её. Какие поля

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

на клавиатуре, добавьте

office-guru.ru

Область печати в Excel

Microsoft Excel позволяет вроде получил готовое? стрелочку. Жмите наЧетвертый вариант. нажимаем «ОК». Пойдет

перекладываем снова в листы», но сначала

параметры печати, можно
, ставим нули у поставить «Книжная» - получились, т.д. В выборе каждого варианта листа, если данныеНа вкладкеОбласть печати остальные. задавать область несколькими
решение здесь:Спасибо.
нее. Вылезет тоМожно настроить таблицу печать. лоток для бумаги, выделить эти листы. печатать — нажимаем строк «верхнего колонтитула» вертикальное расположение листа режиме предварительного просмотра будет отображена в не помещаются наPage Layout
выберите пунктПерейдите к панели способами, каждый изhttp://www.planetaexcel.ru/forum.php?thread_id=34231RAN

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

Делается это так.

office-guru.ru

Указание порядка печати для страниц листа

кнопку «Печать». Появится и «нижнего колонтитула». или «Альбомная» - можно сделать сразу поле предварительного просмотра. одной странице. По(Разметка страницы) нажмитеНапечатать выделенный фрагментПечать них Вам обязательно_Boroda_: активное.

листа, смотрите в проверьте – принтер чистой стороной верхом
У нас открыт такое окно.Еще вариант, изменить горизонтальное расположение листа. изменения в документе.В Excel умолчанию Excel печатает кнопку..
пригодится. В рамках: Без макросов вСаняГость статье «Как печатать включён? ;)) Такое документа вперед и первый лист, которыйВнимание!

 поля – это Можно установить масштаб Об этом мыпеч страницы сверху вниз,

support.office.com

Печать в Excel.

Print Area В области предварительного просмотраИз выпадающего меню данного урока мы 2010 почему-то не:: Файл (Параметры Страницы в Excel». часто бывает, особенно, напечатаются четные страницы нужно распечатать. ТеперьЕсли в разделе поставить галочку у – это размер сейчас и поговорим.атать а затем слева(Область печати) и появится выделенный фрагмент.Область печати
подробно разберем все работает. В 2003200?’200px’:»+(this.scrollHeight+5)+’px’);»>Dim rng As Range
 (Лист)).
808 если принтер подключают документа. нажимаем на кнопку «Печать» укажем: кнопки «Показать поля» печати документа, таблицы.Итак,можно отдельные страницы направо, но этот выберитеНажмите кнопкувыберите пункт эти способы, а должен (предположительно) работать
Set rng =Pelena: Коллеги, добрый день.
к ноутбуку (ПК)Если альбомная ориентация Ctrl на клавиатуре«все» — напечатаются в режиме предварительногоНапример, чтобы

предварительный просмотр в Excel таблицы, всю таблицу, порядок можно изменить,Set Print AreaПечатьНапечатать активные листы также узнаем, как файл Sv_diap_bez_mac.xls Range(«A4»)
: Как вариант, можноЯ столкнулся с не постоянно, а
 страницы, то поворачиваем ноутбука (ПК), и, все листы, на просмотра. Тогда на
напечатать маленькую таблицу Excel.выделенный диапазон, данные после чтобы страницы печатались(Задать)., чтобы распечатать выделенный. предварительно задать областьА с макросамиDim r As задать область печати одной проблемой -
только на время листы на чистую удерживая её нажатой, которых что-то написано; листе просмотра появятся на весь лист
В левом верхнем фильтрования, примечания в  слева направо, аСохраните, закройте и снова фрагмент.
Нажмите кнопку печати прямо на — файл Sv_diap_s_mac.xls Integer, c AsSuncheZ мне нужно выводить распечатки документа. сторону и кладём
 нажимаем на ярлыки«страницы с… по.. поля, которые можно
, нужно поставить масштаб углу нажимаем кнопку ячейках, заголовок таблицы затем сверху вниз. откройте файл Excel.Чтобы заранее определиться сПечать вкладкеОн вроде попроще, Integer
: Сам столкнулся с на печать определенныйВторой вариант верхом документа слева. тех листов, которые – (например с двигать мышкой. Двигаем больше 100% (например, «Office» на каждой странице,Щелкните лист, для которогоНа вкладке областью печати, ее

.Разметка страницы чем по твоейr = rng.EntireColumn.Find(«Общий данной проблемой. Решается, диапазон из всейнастройки параметров печати Тогда документ распечатается нужно распечатать.
 3 по 5), чёрные квадратики на 150%).и выбираем «печать»,т.д. Сначала нужно необходимо изменить порядокFile можно предварительно задать.Перейдите к панели.
ссылке на Планете итог»).Row действительно, через вкладку книги. Определять диапазон документа такой.

 на второй стороне
Когда все распечатаете, то распечатаются страницы
краях листа иПечать на одном листе затем — «Предварительный
подготовить документ к печати.(Файл) кликните по Для этого выделитеПечатьПрежде чем напечатать книгу200?’200px’:»+(this.scrollHeight+5)+’px’);»>Private Sub Worksheet_PivotTableUpdate(ByVal Target
c = rng.EntireRow.Find(«Общий Разметка страницы, а вручную — оченьСначала смотрим документ правильно. снова нажмите мышкой 3, 4, 5, меняем размер полей Excel просмотр».
печати, настроитьНа вкладкеPrint требуемый диапазон ячеек,
. Excel, требуется точно As PivotTable)
итог»).Column там — область неэффективный труд ибо через «Печать» ->Если нет функции на ярлык одного т. е. конкретные или столбцов.большой таблицыКнопки «Печать» ипараметры печати в ExcelРазметка страницы(Печать). перейдите на вкладкуИз выпадающего меню
установить, какую информациюr_ = Target.RowRange.Rows.CountRange(rng, Cells(r, c)).Select’PrintOut печати и печатаемые страниц много. Хочу
 «Предварительный просмотр». «Двухсторонняя печать», то любого листа, чтобы листы;Раздел «Колонтитулы»можно настроить двумя «Предварительный просмотр» можно- поля,в группеРезультат: Разметка страницы, нажмитеОбласть печати
выводить на печать. + 3 Copies:=1 ‘у меня поля. Тогда спокойно задать что то
Затем выходим из сначала выделяем и листы расцепились.«выделенный диапазон» -диалогового окна «Параметры способами. установить на панельзадать область печати,
Параметры страницыПосмотрите на предварительный на команду Областьвыберите пункт Например, если вc_ = Target.ColumnRange.Columns.Count нет принтера он позволяет форматировать
вроде А1:D4 на предварительного просмотра кнопкой печатаем листы четные,Как узнать номера страниц? распечатается тот диапазон страницы» можно настроить,Или установить масштаб быстрого доступа функциейразмер и ориентацию
нажмите кнопку вызова просмотр, который показан печати и изНапечатать всю книгу Вашей книге содержится + 1_Boroda_ эти поля, в каждой странице и «Закрыть окно предварительного расцепим четные листы,
Можно посмотреть на таблицы, который мы добавить или убрать
менее 100%, или «Другие команды». Как листа, др. Это диалогового окна на рисунке ниже. выпадающего меню выберите. много листов, необходимоActiveSheet.PageSetup.PrintArea = Range(«A4:»
: А вот посмотри отличие от доступа в путь.
просмотра». затем выделяем нечетные предварительном просмотре внизу
выделили»; Как выделить колонтитулы. Настроить разные поставить галочку у это сделать, смотрите
поможет сэкономить время, расположенную рядом Как видите, будет Задать.Нажмите кнопку
определить, печатать всю & Cells(r_, c_).Address).Address вариант без макроса через файл>параметры печати.
Нашел одну опциюИ на рабочей листы и печатаем слева экрана. диапазон печати, если колонтитулы для четных
 функции «Разместить не в статье «Сводная и деньги (бумагу) с надписью напечатана только заданнаяУрок подготовлен для Вас

excel-office.ru

Параметры вывода на печать листов Excel (Часть настроек печати неактивна)

Печать книгу или только
End SubSerge_007Serge_007 в «параметрах страницы» странице этого листа их. Как выделитьПеред печатью документа нужно напечатать часть и нечетных страниц. более чем на таблица в Excel :)Параметры страницы
область. командой сайта office-guru.ru. активные листы. ЧастоSerge_007: Спасибо, но: Во вложении файл, (Excel 2007)на скрине
пунктирной линией будет листы, описано выше.

 обязательно проверить остальные таблицы, смотрите в Установить особый колонтитул 1 стр. в из нескольких таблиц».Есть много способов.ИспользуйтеИсточник: http://www.gcflearnfree.org/office2013/excel2013/12/full

В качестве примера выведем возникает необходимость распечатать: Спасибо, попробую оба

Общий итог с которого надо изображено. Вроде относится

 разметка на страницыПроверили имя принтера, параметры печати: сколько статье «Как выделить для первой страницы. ширину и 1В разделе «Предварительный подготовить и распечататьНа вкладкеName ManagerАвтор/переводчик: Антон Андронов на печать фрагмент

excelworld.ru

Определение диапазона печати (VBA)

 только выделенный фрагментnervможет как присутствовать, распечатать бумажные копии.
к моему вопросу области печати. оно соответствует тому, копий надо напечатать. в Excel ячейки,Раздел «Лист» стр. в высоту. просмотр» нажимаем кнопку таблицу.Лист(Диспетчер имен), чтобы
Автор: Антон Андронов

 таблицы Excel, связанный книги. smile

: так и нет.Как макросом задать - но этаОриентируясь по ним, на котором вы Если надо больше таблицу, др". в окне «ПараметрыРаздел «Поля» "Параметры страницы".Первый вариант. в разделе редактировать и удалятьЕсли вы зададите область

 с футбольными матчами,Листы считаются активными, когдаSerge_007

 Андрей, свой способ область печати, что настройка, как видно удобно настраивать или собираетесь печатать. Часто одной копии, то«выделенные листы» или

 страницы» — можнов диалоговом окнеРаздел «Страница»

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

 потестирую. бы печатались ячейки — не активна. сразу создавать документ к одному компьютеру исправить цифру в
«всю книгу» - поставить галочку, чтобы
«Параметры страницы».диалогового окна «Параметры лучше просмотреть в
установите переключательУрок подготовлен для Вас то распечатается только июле. Выделите лист, который требуется Пригодится : ) _Boroda_ А4:Е7, если в Подскажите плз -

 по параметрам печати. могут подсоединяться разные smile

 разделе «копии». распечатается то, унапечатать сетку ExcelЗдесь можно изменить страницы»

excelworld.ru

режиме предварительного просмотра.

Источник

СЧЕТЕСЛИ диапазон на нескольких листах

Андреич

Дата: Пятница, 04.07.2014, 10:45 |
Сообщение № 1

Группа: Пользователи

Ранг: Участник

Сообщений: 60

Репутация:
0
±

Замечаний:
0% ±

Excel 2007

Вот есть функция: СЧЁТЕСЛИ (диапазон;критерий)
Можно ли в качестве диапазона указывать диапазоны расположенные на разных листах?
И как это правильно записать.
например нужно чтобы формула подсчитывала ячейки в диапазонах ячеек А1:А10 на листе1 и В1:В12 на листе2.
Спасибо!

…пожарные выехали в ту же ночь…

Ответить

Serge_007

Дата: Пятница, 04.07.2014, 10:45 |
Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 15888

Репутация:
2623
±

Замечаний:
±

Excel 2016

Нет, нельзя

ЮMoney:41001419691823 | WMR:126292472390

Ответить

ArkaIIIa

Дата: Пятница, 04.07.2014, 10:56 |
Сообщение № 3

Группа: Проверенные

Ранг: Ветеран

Сообщений: 894

Репутация:
115
±

Замечаний:
0% ±

2010

Андреич
А что Вам мешает сделать:

Код

=СЧЁТЕСЛИ(Лист1!A1:A10;»критерий»)+СЧЁТЕСЛИ(Лист2!B1:B12;»критерий»)

Сообщение отредактировал ArkaIIIa — Пятница, 04.07.2014, 10:57

Ответить

Serge_007

Дата: Пятница, 04.07.2014, 11:10 |
Сообщение № 4

Группа: Админы

Ранг: Местный житель

Сообщений: 15888

Репутация:
2623
±

Замечаний:
±

Excel 2016

ArkaIIIa, а если листов 100500?

ЮMoney:41001419691823 | WMR:126292472390

Ответить

ArkaIIIa

Дата: Пятница, 04.07.2014, 11:16 |
Сообщение № 5

Группа: Проверенные

Ранг: Ветеран

Сообщений: 894

Репутация:
115
±

Замечаний:
0% ±

2010

Serge_007
Тогда нужно бы написать макрос, который объединит данные со всех листов в одну таблицу и дальше не изобретать велосипед)

Ответить

Андреич

Дата: Пятница, 04.07.2014, 11:21 |
Сообщение № 6

Группа: Пользователи

Ранг: Участник

Сообщений: 60

Репутация:
0
±

Замечаний:
0% ±

Excel 2007

Задам вопрос по другому, хотя наверное опять нарушаю правила…
посмотрите вложение пожалуйста

К сообщению приложен файл:

9837553.xls
(19.0 Kb)

…пожарные выехали в ту же ночь…

Ответить

ArkaIIIa

Дата: Пятница, 04.07.2014, 11:24 |
Сообщение № 7

Группа: Проверенные

Ранг: Ветеран

Сообщений: 894

Репутация:
115
±

Замечаний:
0% ±

2010

Андреич
А зачем Вы выносите каждый месяц на новый лист, не проще вести таблицу на 1 листе? Тогда у вас не будет сложностей для подсчета чего-угодно.

К сообщению приложен файл:

_9837553.xls
(30.5 Kb)

Сообщение отредактировал ArkaIIIa — Пятница, 04.07.2014, 11:28

Ответить

Андреич

Дата: Пятница, 04.07.2014, 11:32 |
Сообщение № 8

Группа: Пользователи

Ранг: Участник

Сообщений: 60

Репутация:
0
±

Замечаний:
0% ±

Excel 2007

Таблица с данными знааааачительно больше (и в ширину и в длинну) чем в примере, поэтому не удобно с ней работать на одном листе, пройденный этап.

…пожарные выехали в ту же ночь…

Ответить

Serge_007

Дата: Пятница, 04.07.2014, 11:37 |
Сообщение № 9

Группа: Админы

Ранг: Местный житель

Сообщений: 15888

Репутация:
2623
±

Замечаний:
±

Excel 2016

не удобно с ней работать на одном листе

Неудобно штаны через голову одевать, а с данными на одном листе работать и не надо
Сливаете всё на один лист, потом делаете сводную и работаете с данными как угодно
Количество данных (и в ширину и в длину) роли не играет

[p.s.]Длина пишется с одной «н»[/p.s.]

ЮMoney:41001419691823 | WMR:126292472390

Ответить

_Boroda_

Дата: Пятница, 04.07.2014, 11:41 |
Сообщение № 10

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

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

Скажи мне, кудесник, любимец ба’гов…
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

and708

Дата: Среда, 22.07.2020, 12:14 |
Сообщение № 11

Группа: Пользователи

Ранг: Новичок

Сообщений: 19

Репутация:
0
±

Замечаний:
20% ±

Excel 2010

ArkaIIIa, позвольте я тоже задам вопрос на подобную тему.
Есть имя в диспетчере имен, оно включает в себя несколько разрозненных диапазонов. нужно применить функцию СЧЁТЕСЛИ к этому имени.
конечно же, можно придумать что-то типа

Код

=СЧЁТЕСЛИ(Лист1!A1:A10;»критерий»)+СЧЁТЕСЛИ(Лист2!B1:B12;»критерий»)[vba][code]

[/vba]
но дело в том что в Excel есть возможность присвоить одно имя разным фрагментам, даже если у них нет общих ячек или границ. И к этому имени возможно применить разные фунцкии, например СЧЁТ. Но к сожалению, нельзя применить СЧЁТЕСЛИ однажды — затем в скобках это имя. Можно, как вы правильно заметили, применить длинную формулу, как сумму нескольких СЧЁТЕСЛИ, но это во-первых неудобно, во-вторых, зачем тогда было присваивать это имя? прошу пожалуйста ответьте, как можно решить эту проблему, чтобы было во-первых, удобно.во-вторых, чтобы можно было по-возможности применить только одну функцию, содержащую имя — а втнутри этого имени несколько отдельных фрагментов Excel. Спасибо за помощь и понимание.

Ответить

Pelena

Дата: Среда, 22.07.2020, 13:05 |
Сообщение № 12

Группа: Админы

Ранг: Местный житель

Сообщений: 18797

Репутация:
4284
±

Замечаний:
±

Excel 2016 & Mac Excel

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

«Черт возьми, Холмс! Но как??!!»
Ю-money 41001765434816

Ответить

Источник

Cedro

Пользователь

Сообщений: 11
Регистрация: 24.05.2015

Добрый день!

Созданы вкладки с названиями с 01 до 31 по количеству дней в месяце. Все вкладки идентичны. Необходимо суммировать значения в одинаковых ячейках разных вкладок, но диапазон суммирования задать начальной и конечной датой.
Использование ДВССЫЛ прекрасно работает на одну ячейку, например, =ДВССЫЛ(«‘»&ТЕКСТ($B$1;»ДД»)&»‘»&»!B2″), а также с заранее известными листами, но суммировать по произвольно задаваемому диапазону листов, как я понял, ДВССЫЛ не умеет.

Как можно решить данный вопрос, какая есть альтернатива?

Bema

Пользователь

Сообщений: 6750
Регистрация: 15.02.2016

07.08.2017 18:12:36

Цитата
Cedro написал: Необходимо суммировать

Консолидация (объединение) данных из нескольких таблиц в одну

Цитата
Cedro написал: какая есть альтернатива?

Принципиально пересмотреть работу с данными. В чем смысл каждый день хранить на отдельном листе?

Kuzmich

Пользователь

Сообщений: 7998
Регистрация: 21.12.2012

07.08.2017 18:21:59

Цитата
какая есть альтернатива?

Макросом, запускать при активном листе Итог

Код

Sub Sbor()
Dim Sht As Worksheet
Dim FoundDate As Range
    For Each Sht In Worksheets
      If Sht.Name <> "Итог" Then        ' кроме листа "Итог"
        With Sht
          Set FoundDate = Rows(1).Find(.Range("B1"), , xlValues, xlWhole)
          If Not FoundDate Is Nothing Then
            .Range("B2:B3").Copy Cells(2, FoundDate.Column)
          End If
        End With
      End If
    Next
End Sub

Cedro

Пользователь

Сообщений: 11
Регистрация: 24.05.2015

07.08.2017 20:49:48

Спасибо, Kuzmich. Хотелось бы обойтись без макроса, обычными формулами, но похоже вариантов немного.

Цитата
Bema написал: В чем смысл каждый день хранить на отдельном листе?

Bema, в примере я указал только пару параметров, чтобы описать принцип. В реальной таблице около 40 показателей (строки) и около 70 подразделений (столбцы). Заполнять и анализировать их удобнее именно ежедневно.

Cedro

Пользователь

Сообщений: 11
Регистрация: 24.05.2015

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

Kuzmich

Пользователь

Сообщений: 7998
Регистрация: 21.12.2012

07.08.2017 21:30:10

Цитата
а мне требуется суммировать многие показатели в разных подразделениях с 1-го, например, по текущее число

Приведите пример,подумаем.

Cedro

Пользователь

Сообщений: 11
Регистрация: 24.05.2015

Переделал файл, так он ближе к реальному. Данные на начало каждого следующего дня берутся из итога предыдущего. Сейчас в на листе Итоги стоят суммы ячеек на листах с 01 по 03. Мне требуется, чтобы это были переменные. Значения этих переменных неважно где брать, я их вырезаю из Числа дня, так проще тем, кто кроме меня пользуется файлом — поменяли число, получили нужный итог. Например, получить суммы с 01 по 14 число.

Kuzmich

Пользователь

Сообщений: 7998
Регистрация: 21.12.2012

07.08.2017 22:09:03

Cedro,

Цитата
Например, получить суммы с 01 по 14 число

А в книге точно будут эти 14 листов, или надо делать проверку на наличие листа в книге.
Суммы всегда считаются, начиная с первого дня месяца? Или есть начало и конец дней подсчета?

Cedro

Пользователь

Сообщений: 11
Регистрация: 24.05.2015

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

Cedro

Пользователь

Сообщений: 11
Регистрация: 24.05.2015

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

Kuzmich

Пользователь

Сообщений: 7998
Регистрация: 21.12.2012

#11

07.08.2017 23:19:33

Цитата
таким образом есть начальная и конечная дата.

Добавил столбец В, в В1-начальная дата, в С1-конечная дата
Макрос переносит и суммирует данные по строкам от 3 до 7 по каждому отделу

Код

Sub Sbor()
Dim Sht As Worksheet
Dim F_Day As Integer
Dim E_Day As Integer
Dim i As Integer
Dim j As Integer
   Range("D3:G7").ClearContents     'очищаем диапазон
   F_Day = Day(Range("B1"))         'первый день
   E_Day = Day(Range("C1"))         'последний день
    For Each Sht In Worksheets      'цикл по листам
      If Sht.Name <> "Итог" Then    ' кроме листа "Итог"
        If Val(Sht.Name) >= F_Day And Val(Sht.Name) <= E_Day Then
         With Sht
            For i = 3 To 7
              For j = 4 To 7
                Cells(i, j) = Cells(i, j) + .Cells(i, j - 1)
              Next
            Next
          End With
        End If
      End If
    Next
End Sub

Vetermsk Пользователь Сообщений: 42 Регистрация: 01.01.1970	Добрый день! Формулу не получилось написать, но сделал через дополнительный лист. Посмотрите, в таком виде устроит? Доп. лист можно скрыть после настройки.

The_Prist

Пользователь

Сообщений: 14181
Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

Без макросов одной формулой. См. ячейку С2.
Формула вводится как ФОРМУЛА МАССИВА(т.е. Ctrl+Shift+Enter)

Изменено: The_Prist — 08.08.2017 10:43:44
(с первого раза загрузка файла не работала)

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

Cedro Пользователь Сообщений: 11 Регистрация: 24.05.2015	Kuzmich, Vetermsk, The_Prist огромное спасибо! Буду разбираться.

gling

Пользователь

Сообщений: 4024
Регистрация: 01.01.1970

#15

08.08.2017 19:58:37

Цитата
но суммировать по произвольно задаваемому диапазону листов, как я понял, ДВССЫЛ не умеет.

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

Прикрепленные файлы

Сумма по листам-датам уточнение (1).xlsx (20.87 КБ)

Изменено: gling — 08.08.2017 20:06:49

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как в офисе.

Задаем область печати в Excel

Печать активных листов в Excel

Печать всей книги в Excel

Печать выделенного фрагмента в Excel

Область печати в Excel

Указание порядка печати для страниц листа

Печать в Excel.

Параметры вывода на печать листов Excel (Часть настроек печати неактивна)

Определение диапазона печати (VBA)

А вот еще интересные статьи: