Вы назначили имя диапазонуячеек, и… возможно, вы забыли местоположение. Именуемый диапазон можно найти с помощью функции «Перейти», которая позволяет перейти к любому именоваемом диапазону во всей книге.
-
Именующий диапазон можно найти на вкладке «Главная», нажав кнопку «Найти &Выбрать» и выбрав «Перейти».
Можно также нажать клавиши CTRL+G.
-
В поле «Перейти» дважды щелкните именуемый диапазон, который нужно найти.
Примечания:
-
Во всплываемом окне «Перейти» показаны имененные диапазоны на всех книгах.
-
Чтобы перейти к диапазону неименованых ячеек, нажмите CTRL+G, введите диапазон в поле «Ссылка» и нажмите ввод (или кнопку ОК). Поле «Перейти» отслеживает диапазоны по мере их ввода, и вы можете вернуться к любому из них, дважды щелкнув их.
-
Чтобы перейти к ячейке или диапазону на другом листе, введите в поле «Ссылка» следующее: имя листа вместе с восклицательный индекс и абсолютные ссылки на ячейки. Например, лист2!$D $12 для перейти к ячейке, а лист3!$C$12:$F$21 — для перейти к диапазону.
-
В поле «Ссылка» можно ввести несколько именовых диапазонов или ссылок на ячейки. Разделяя каждую из них запятой, например: Price, Typeили B14:C22,F19:G30,H21:H29. Когда вы нажмете ввод или нажмете кнопку«ОК», Excel выделит все диапазоны.
Дополнительные сведения о поиске данных в Excel
-
Поиск или замена текста и чисел на листе
-
Поиск объединенных ячеек
-
Удаление или разрешение циклической ссылки
-
Поиск ячеек, содержащих формулы
-
Поиск ячеек с условным форматированием
-
Поиск скрытых ячеек на листе
Нужна дополнительная помощь?
Содержание:
- Что такое диапазон?
- Как рассчитать диапазон в Excel?
- Вычислить условный диапазон в Excel
Обычно, когда я использую диапазон слов в своих руководствах по Excel, это ссылка на ячейку или набор ячеек на листе.
Но этот урок не об этом диапазоне.
«Диапазон» также является математическим термином, который относится к диапазону в наборе данных (т. Е. Диапазон между минимальным и максимальным значением в данном наборе данных).
В этом уроке я покажу вам действительно простые способы рассчитать диапазон в Exceл.
Что такое диапазон?
В данном наборе данных диапазон этого набора данных будет разбросом значений в этом наборе данных.
Чтобы дать вам простой пример, если у вас есть набор данных об успеваемости учащихся, где минимальный балл составляет 15, а максимальный балл — 98, то разброс этого набора данных (также называемый диапазоном этого набора данных) будет 73.
Диапазон = 98-15
«Диапазон» — это не что иное, как разница между максимальным и минимальным значением этого набора данных.
Как рассчитать диапазон в Excel?
Если у вас есть список отсортированных значений, вам просто нужно вычесть первое значение из последнего значения (при условии, что сортировка выполняется в порядке возрастания).
Но в большинстве случаев у вас будет случайный набор данных, который еще не отсортирован.
Найти диапазон в таком наборе данных также довольно просто.
В Excel есть функции для определения максимального и минимального значения из диапазона (функции MAX и MIN).
Предположим, у вас есть набор данных, показанный ниже, и вы хотите вычислить диапазон для данных в столбце B.
Ниже приведена формула для расчета диапазона для этого набора данных:
= МАКС (B2: B11) -МИН (B2: B11)
Приведенная выше формула находит максимальное и минимальное значение и дает нам разницу.
Довольно просто … не правда ли?
Вычислить условный диапазон в Excel
В большинстве практических случаев найти диапазон не так просто, как просто вычесть минимальное значение из максимального значения.
В реальных сценариях вам также может потребоваться учесть некоторые условия или выбросы.
Например, у вас может быть набор данных, в котором все значения меньше 100, но есть одно значение выше 500.
Если вы рассчитываете порядок для этого набора данных, это приведет к неправильной интерпретации данных.
К счастью, в Excel есть множество условных формул, которые могут помочь вам разобраться в некоторых аномалиях.
Ниже у меня есть набор данных, в котором мне нужно найти диапазон значений продаж в столбце B.
Если вы внимательно посмотрите на эти данные, вы заметите, что есть два магазина, где значения довольно низкие (Магазин 1 и Магазин 3).
Это может быть связано с тем, что это новые магазины или какие-то внешние факторы повлияли на продажи в этих конкретных магазинах.
При вычислении диапазона для этого набора данных может иметь смысл исключить эти новые магазины и рассматривать только те магазины, где есть существенные продажи.
В этом примере, скажем, я хочу игнорировать все те магазины, где стоимость продажи меньше 20 000.
Ниже приведена формула, по которой можно найти диапазон с условием:
= MAX (B2: B11) -MINIFS (B2: B11, B2: B11, "> 20000")
В приведенной выше формуле вместо использования функции MIN я использовал функцию MINIFS (это новая функция в Excel2021-2022 и Microsoft 365).
Эта функция находит минимальное значение, если соблюдены указанные в нем критерии. В приведенной выше формуле в качестве критерия я указал любое значение, превышающее 20 000.
Таким образом, функция MINIFS просматривает весь набор данных, но при вычислении минимального значения учитывает только те значения, которые больше 20 000.
Это гарантирует, что значения ниже 20 000 игнорируются, а минимальное значение всегда больше 20 000 (следовательно, игнорируются выбросы).
Обратите внимание, что MINIFS — это новая функция в Excel. доступно только в Excel2021-2022 и подписке Microsoft 365. Если вы используете предыдущие версии, у вас не будет этой функции (и вы можете использовать формулу, описанную далее в этом руководстве)
Если в вашем Excel нет функции МИНИМУМ, воспользуйтесь приведенной ниже формулой, в которой для того же результата используется комбинация функций ЕСЛИ и МИНИМУМ:
= МАКС (B2: B11) -МИН (ЕСЛИ (B2: B11> 20000; B2: B11))
Так же, как я использовал условную функцию MINIFS, вы также можете использовать функцию MAXIFS, если вы хотите избежать точек данных, которые являются выбросами в другом направлении (т. Е. Пара больших точек данных, которые могут исказить данные)
Итак, вот как вы можете быстро найти диапазон в Excel используя пару простых формул.
Надеюсь, вы нашли этот урок полезным.
Вы назначили имя диапазонуячеек, и. возможно, вы забыли местоположение. Именуемый диапазон можно найти с помощью функции «Перейти», которая позволяет перейти к любому именоваемом диапазону во всей книге.
Именующий диапазон можно найти на вкладке «Главная», нажав кнопку «Найти &Выбрать» и выбрав «Перейти».
Можно также нажать клавиши CTRL+G.
В поле «Перейти» дважды щелкните именуемый диапазон, который нужно найти.
Во всплываемом окне «Перейти» показаны имененные диапазоны на всех книгах.
Чтобы перейти к диапазону неименованых ячеек, нажмите CTRL+G, введите диапазон в поле «Ссылка» и нажмите ввод (или кнопку ОК). Поле «Перейти» отслеживает диапазоны по мере их ввода, и вы можете вернуться к любому из них, дважды щелкнув их.
Чтобы перейти к ячейке или диапазону на другом листе, введите в поле «Ссылка» следующее: имя листа вместе с восклицательный индекс и абсолютные ссылки на ячейки. Например, лист2!$D $12 для перейти к ячейке, а лист3!$C$12:$F$21 — для перейти к диапазону.
В поле «Ссылка» можно ввести несколько именовых диапазонов или ссылок на ячейки. Разделяя каждую из них запятой, например: Price, Typeили B14:C22,F19:G30,H21:H29. Когда вы нажмете ввод или нажмете кнопку«ОК», Excel выделит все диапазоны.
Диапазон ячеек Excel
Начнем с самого простого: дайте ячейке имя. Для этого просто выделите ее (1) и в поле имени (2) вместо адреса ячейки введите любое легко запоминающееся имя.
Именная ячейка C7
Имя ограничено 255 символами, что более чем достаточно. Имя также не должно содержать пробелов, поэтому, если оно состоит из нескольких слов, можно разделить их символом подчеркивания.
Если теперь мы хотим вывести это значение на другие листы книги или использовать его в дальнейших расчетах, нет необходимости переходить на первый лист и вручную указывать ячейку. Просто введите имя ячейки, и ее значение будет заменено.
Использование имени ячейки
Ячейки, строки, столбцы
Начнем с выделения ячеек, строк и столбцов.
- Чтобы выбрать ячейку C3, нажмите на поле на пересечении столбца C и строки 3.
Примеры диапазона
Диапазон — это набор из двух или более ячеек.
- Чтобы выделить диапазон B2:C4, щелкните в правом нижнем углу ячейки B2 и перетащите указатель мыши в ячейку C4.
Заполнение диапазона
Следуйте приведенным ниже инструкциям, чтобы заполнить диапазон:
- В ячейку B2 введите значение 2.
Эта техника перетаскивания очень важна, вы будете часто использовать ее в Excel. Вот еще один пример:
Именованный диапазон
Диапазон ячеек можно назвать аналогичным образом, т.е. выделить диапазон (1) и ввести его имя в поле имени (2):
Создание именованного диапазона
Затем это имя можно использовать в формулах, например, для вычисления суммы:
Использование именованного диапазона в формуле
Именованный диапазон также можно создать на вкладке Формулы, выбрав инструмент Задать имя.
Создание именованного диапазона с помощью панели инструментов
Появится диалоговое окно, в котором нужно ввести имя диапазона, выбрать область, к которой будет применяться имя (т.е. ко всей книге или к отдельным листам), при необходимости заполнить примечание, а затем выбрать соответствующий диапазон на листе.
Создание имени с помощью диалогового окна
Для работы с существующими диапазонами на вкладке Формулы есть Менеджер имен.
Используйте этот инструмент для удаления, изменения или добавления новых имен к ячейкам или диапазонам.
Управление именованными диапазонами
Однако важно понимать, что если вы используете именованные диапазоны в формулах, удаление имени такого диапазона приведет к ошибкам.
Задача
У вас есть таблица продаж некоторых товаров по месяцам (см. файл-образец ):
Задача состоит в том, чтобы найти общий объем продаж продукции в данном месяце. Пользователь должен иметь возможность выбрать месяц и получить общую сумму продаж. Пользователь должен выбрать месяц, используя выпадающий список.
Чтобы решить эту проблему, нам нужно создать два динамических диапазона: один для выпадающего списка, содержащего месяцы, а другой для диапазона суммы.
Для генерации динамических диапазонов мы будем использовать функцию HUMMING(), которая возвращает ссылку на диапазон в зависимости от значения заданных аргументов. Вы можете указать высоту и ширину диапазона, а также смещение по строкам и столбцам.
Создайте динамический диапазон для выпадающего списка, содержащего месяцы. С одной стороны, необходимо учесть, что пользователь может добавить продажи за месяцы после апреля (май, июнь. ), с другой стороны, выпадающий список не должен содержать пустых строк. Динамический диапазон как раз и является решением этой проблемы.
Для создания динамического диапазона:
- на вкладке Формулы в группе Определенные имена выберите Присвоить имя ;
- В поле Имя введите: Месяц ;
- В поле Область выберите Книжный лист ;
- В поле Range введите формулу =MEMBERSHIP(sheet1!$B$5;;;1;ACCOUNT(sheet1!$B$5:$I$5)).
- Нажмите OK.
Теперь подробнее. Любой диапазон в EXCEL определяется координатами верхней левой и нижней правой ячеек диапазона. Начальной ячейкой, от которой рассчитывается положение нашего динамического диапазона, является ячейка B5 . Если аргументы offset_by_rows, offset_by_columns не заданы (как в нашем случае), то эта ячейка является верхней левой ячейкой диапазона. Правая нижняя ячейка диапазона задается аргументами height и width . В нашем случае значение высоты =1, а значение ширины диапазона равно результату расчета формулы SCHOTZ(sheet1!$B$5:$I$5), который равен 4 (строка 5 содержит 4 месяца с января по апрель). Итак, адрес правой нижней ячейки нашего динамического диапазона определен — это E 5 .
Когда вы заполните таблицу данными о продажах за май, июнь и так далее, формула READ(sheet1!$B$5:$I$5) вернет количество заполненных ячеек (количество названий месяцев) и таким образом определит новую ширину динамического диапазона, который в свою очередь создаст выпадающий список.
ПРИМЕЧАНИЕ: При использовании функции SCRETZ() убедитесь, что нет пустых ячеек! Т.е. вы должны заполнить список месяцами без пробелов.
Теперь создадим еще один динамический диапазон для подведения итогов продаж.
Для создания динамического диапазона :
- На вкладке Формулы в группе Определенные имена выберите Присвоить имя ;
- В поле Имя введите: Продажи_в_месяц;
- В поле Диапазон введите формулу = AMOUNT(worksheet1!$A$6;;SCHEDULE(worksheet1!$C$1;worksheet1!$B$5:$I$5;0);12).
- нажмите OK.
Функция ПОИСКПОЗ() ищет в строке 5 (список месяцев) месяц, выбранный пользователем (ячейка C1 с выпадающим списком), и возвращает соответствующий номер элемента из диапазона поиска (названия месяцев должны быть уникальными, т.е. этот пример не подходит для нескольких лет). Левый верхний угол нашего динамического диапазона (начиная с ячейки A6) перемещается на это количество столбцов, высота диапазона остается фиксированной — 12 (при желании вы можете сделать ее динамической, в зависимости от количества товаров в диапазоне).
И, наконец, если вы введете формулу = SUMM(Sales_over_month) в ячейку C2, вы получите сумму продаж в выбранном месяце.
Например, в мае месяце.
Или, например, в апреле месяце.
Примечание: Вместо формулы SMUM() можно использовать формулу INDEX() : = $B$5:INDEX(B5:I5;AMOUNT($B$5:$I$5)) для расчета количества завершенных месяцев.
Формула подсчитывает количество элементов в строке 5 (SCRUTZ() ) и определяет ссылку на последний элемент в строке (INDEX() ), таким образом возвращая ссылку на диапазон B5:E5 .
Визуальное отображение динамического диапазона
Текущий динамический диапазон можно выделить с помощью условного форматирования . В файле примера правило условного форматирования применяется к ячейкам диапазона B6:I14 с помощью формулы: = столбец(B6)= столбец(Продажи_в_месяц)
Условное форматирование автоматически выделило серым цветом продажи текущего месяца, который был выбран в выпадающем списке.
Как посчитать количество ссылок на столбцы таблицы Excel
Пример 2. Определите количество столбцов в таблице и введите это значение в ячейку A16.
Мы используем формулу OVERALL, выбирая в качестве параметра поочередно каждый столбец ячейки. Нажмите и удерживайте клавишу Ctrl перед выбором следующего столбца. Если вы добавите «)» и нажмите Enter, появится диалоговое окно, указывающее на то, что вы ввели слишком много аргументов. Добавьте дополнительные открывающие и закрывающие скобки.
Определение принадлежности ячейки к диапазону таблицы
Пример 3 Определяет, принадлежит ли данная ячейка заданному диапазону ячеек.
Рассмотренная здесь функция также позволяет определить, принадлежит ли ячейка выбранному диапазону. Процедура выполняется следующим образом:
-
- Введите часть формулы «=WORLD(()» в любую ячейку и выделите любой диапазон ячеек для заполнения аргументов:
-
- Поставьте пробел и выберите любую ячейку в этом диапазоне:
-
- Закройте обе скобки и нажмите Enter. Результат будет следующим:
-
- Если вы выберете ячейку из диапазона, отличного от указанного, вы получите ошибку #empty!
Эта ошибка указывает на то, что ячейка не принадлежит выбранному диапазону.
Если вы выделите более одной ячейки в диапазоне, функция VARIABLE вернет количество выделенных ячеек:
Описанные возможности этой функции могут быть полезны при работе с большим количеством таблиц данных.
Перемещение и копирование ячеек и их содержимого
См. также = IF(ANSWER(A2);A2;B2) вы копируете макрос «Фильтр» соответствующим образом. Это будет работать только в таблице: Я думаю, что это возможно, если колонна. Т.е. получается, что ВСЕ», затем выполните и нажмите кнопку Вставить более сложную процедуру, щелкните значок Вставить на следующих действиях.Вставить, вы можете выбрать для временного отображения данных, выбранный раздел наПримечание: вставить как выходные значения таблицы с необходимостью?
The_Prist скопируйте выделенный диапазон, который вы выбрали для вышеуказанных действийCtrl+Paste. Чтобы переместить ячейки, щелкните мышью. Вставьте варианты, которые не требуют другого рабочего листа или Попробуйте как можно чаще
Диапазон Excel
Начнем с простого – присвоим имя ячейке. Для этого просто выделяем ее (1) и в поле имени (2) вместо адреса ячейки указываем произвольное название, которое легко запомнить.
Длина имени ограничена 255 символами, что более чем достаточно. Также в имени не должно быть пробелов, поэтому если оно состоит из нескольких слов, то их можно разделять знаком подчеркивания.
Если теперь на других листах книги нам нужно будет вывести данное значение или использовать его в дальнейших расчетах, то не обязательно переключаться на первый лист и указывать ячейку вручную. Достаточно просто ввести имя ячейки и ее значение будет подставлено.
Ячейки, строки, столбцы
Давайте начнем с выбора ячеек, строк и столбцов.
- Для выбора ячейки C3 кликните по полю на пересечении столбца C и строки 3.
- Чтобы выбрать столбец C, кликните по заголовку столбца C.
- Чтобы выбрать строку 3, кликните по заголовку строки 3.
Примеры диапазона
Диапазон представляет собой набор из двух и более ячеек.
- Для выбора диапазона B2:C4 кликните по нижнему правому углу ячейки В2 и протяните указатель мыши до ячейки C4.
- Чтобы выделить диапазон, состоящий из отдельных (несмежных) ячеек, зажмите клавишу Ctrl и кликните по каждой ячейке, которую хотите включить в диапазон.
Заполнение диапазона
Чтобы заполнить диапазон, следуйте инструкции ниже:
- Введите значение 2 в ячейку B2.
- Выделите ячейку В2, зажмите её нижний правый угол и протяните вниз до ячейки В8.Результат:
Именованный диапазон
Аналогичным образом можно задать имя и для диапазона ячеек, то есть выделим диапазон (1) и в поле имени укажем его название (2):
Далее это название можно использовать в формулах, например, при вычислении суммы:
Также создать именованный диапазон можно с помощью вкладки Формулы, выбрав инструмент Задать имя.
Появится диалоговое окно, в котором нужно указать имя диапазона, выбрать область, на которую имя будет распространяться (то есть на всю книгу целиком или на отдельные ее листы), при необходимости заполнить примечание, а далее выбрать соответствующий диапазон на листе.
Для работы с существующими диапазонами на вкладке Формулы есть Диспетчер имен.
С его помощью можно удалять, изменять или добавлять новые имена ячейкам или диапазонам.
При этом важно понимать, что если вы используете именованные диапазоны в формулах, то удаление имени такого диапазона приведет к ошибкам.
Задача
Имеется таблица продаж по месяцам некоторых товаров (см. Файл примера ):
Необходимо найти сумму продаж товаров в определенном месяце. Пользователь должен иметь возможность выбрать нужный ему месяц и получить итоговую сумму продаж. Выбор месяца пользователь должен осуществлять с помощью Выпадающего списка .
Для решения задачи нам потребуется сформировать два динамических диапазона : один для Выпадающего списка , содержащего месяцы; другой для диапазона суммирования.
Для формирования динамических диапазонов будем использовать функцию СМЕЩ() , которая возвращает ссылку на диапазон в зависимости от значения заданных аргументов. Можно задавать высоту и ширину диапазона, а также смещение по строкам и столбцам.
Создадим динамический диапазон для Выпадающего списка , содержащего месяцы. С одной стороны нужно учитывать тот факт, что пользователь может добавлять продажи за следующие после апреля месяцы (май, июнь…), с другой стороны Выпадающий список не должен содержать пустые строки. Динамический диапазон как раз и служит для решения такой задачи.
Для создания динамического диапазона:
- на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя </em>;
- в поле Имя введите: Месяц </em>;
- в поле Область выберите лист Книга </em>;
- в поле Диапазон введите формулу =СМЕЩ(лист1!$B$5;;;1;СЧЁТЗ(лист1!$B$5:$I$5))
- нажмите ОК.
Теперь подробнее. Любой диапазон в EXCEL задается координатами верхней левой и нижней правой ячейки диапазона. Исходной ячейкой, от которой отсчитывается положение нашего динамического диапазона, является ячейка B5 . Если не заданы аргументы функции СМЕЩ() смещ_по_строкам, смещ_по_столбцам (как в нашем случае), то эта ячейка является левой верхней ячейкой диапазона. Нижняя правая ячейка диапазона определяется аргументами высота и ширина . В нашем случае значение высоты =1, а значение ширины диапазона равно результату вычисления формулы СЧЁТЗ(лист1!$B$5:$I$5) , т.е. 4 (в строке 5 присутствуют 4 месяца с января по апрель ). Итак, адрес нижней правой ячейки нашего динамического диапазона определен – это E 5 .
При заполнении таблицы данными о продажах за май , июнь и т.д., формула СЧЁТЗ(лист1!$B$5:$I$5) будет возвращать число заполненных ячеек (количество названий месяцев) и соответственно определять новую ширину динамического диапазона, который в свою очередь будет формировать Выпадающий список .
ВНИМАНИЕ! При использовании функции СЧЕТЗ() необходимо убедиться в отсутствии пустых ячеек! Т.е. нужно заполнять перечень месяцев без пропусков.
Теперь создадим еще один динамический диапазон для суммирования продаж.
Для создания динамического диапазона :
- на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя </em>;
- в поле Имя введите: Продажи_за_месяц </em>;
- в поле Диапазон введите формулу = СМЕЩ(лист1!$A$6;;ПОИСКПОЗ(лист1!$C$1;лист1!$B$5:$I$5;0);12)
- нажмите ОК.
Функция ПОИСКПОЗ() ищет в строке 5 (перечень месяцев) выбранный пользователем месяц (ячейка С1 с выпадающим списком) и возвращает соответствующий номер позиции в диапазоне поиска (названия месяцев должны быть уникальны, т.е. этот пример не годится для нескольких лет). На это число столбцов смещается левый верхний угол нашего динамического диапазона (от ячейки А6 ), высота диапазона не меняется и всегда равна 12 (при желании ее также можно сделать также динамической – зависящей от количества товаров в диапазоне).
И наконец, записав в ячейке С2 формулу = СУММ(Продажи_за_месяц) получим сумму продаж в выбранном месяце.
Или, например, в апреле.
Примечание: Вместо формулы с функцией СМЕЩ() для подсчета заполненных месяцев можно использовать формулу с функцией ИНДЕКС() : = $B$5:ИНДЕКС(B5:I5;СЧЁТЗ($B$5:$I$5))
Формула подсчитывает количество элементов в строке 5 (функция СЧЁТЗ() ) и определяет ссылку на последний элемент в строке (функция ИНДЕКС() ), тем самым возвращает ссылку на диапазон B5:E5 .
Визуальное отображение динамического диапазона
Выделить текущий динамический диапазон можно с помощью Условного форматирования . В файле примера для ячеек диапазона B6:I14 применено правило Условного форматирования с формулой: = СТОЛБЕЦ(B6)=СТОЛБЕЦ(Продажи_за_месяц)
Условное форматирование автоматически выделяет серым цветом продажи текущего месяца , выбранного с помощью Выпадающего списка .
Как посчитать количество ссылок на столбцы таблицы Excel
Пример 2. Определить количество столбцов в таблице и записать это значение в ячейку A16.
Используем формулу ОБЛАСТИ, поочередно выделяя каждый столбец ячейки в качестве параметра. Перед выбором последующего столбца нажимаем и удерживаем кнопку Ctrl. Если добавить символ «)» и нажать Enter, появится диалоговое окно с сообщением о том, что было введено слишком много аргументов. Добавим дополнительные открывающую и закрывающую скобки.
Определение принадлежности ячейки к диапазону таблицы
Пример 3. Определить, принадлежит ли ячейка заданному диапазону ячеек.
Рассматриваемая функция также позволяет определить, принадлежит ли ячейка выделенной области. Выполним следующие действия:
-
- В какой-либо ячейке введем часть формулы «=ОБЛАСТИ((» и выделим произвольную область ячеек для заполнения аргументов:
-
- Поставим пробел и выберем любую ячейку из данного диапазона:
-
- Закроем обе скобки и нажмем Enter. В результате получим:
-
- Если выбрать ячейку не из указанного диапазона, получим ошибку #ПУСТО!.
Данная ошибка означает, что ячейка не принадлежит выделенной области.
Если выделить несколько ячеек внутри диапазона, функция ОБЛАСТИ вернет количество выделенных ячеек:
Описанные особенности работы данной функции могут быть полезны при работе с большим количеством таблиц данных.
Перемещение и копирование ячеек и их содержимого
Смотрите также =ЕСЛИ(ЕЧИСЛО(A2);A2;B2) протягиваете копируете соответственно макрос “Фильтра” хамом станешь…Оно кому только в таблице.: Думаю возможно если столбец. Т.е. получается, ВСЕ”, затем произведите и нажатьВставить более сложная процедура,Нажмите иконку Вставить на указанных ниже действий.Вставить, можно выбрать временно отобразить данные, выделенный фрагмент наПримечание: вставляете как значения выводит таблицу с надо?
The_Prist копировать выделенный диапазон что выделяем для описанные выше действияCtrl+Space. Только имейте в чем хотелось бы. вкладке Главная илиЧтобы переместить ячейки, нажмите. параметры вставки, которые которые не нужно другой лист или Мы стараемся как можно
grablik заданными параметрами дат.Юрий М: В примере все по одной ячейке.
копирования не Range(“7:7″ с формой.(Пробел). Таким способом виду, что здесь На самом деле, нажмите Ctrl+V на кнопкуСочетание клавиш
следует применить к
- копировать. в другую книгу, оперативнее обеспечивать вас
- : Сергей, спасибо, но2. Если необходимо
- : Нет уж! Сказав работает – зачемOLEGOFF ), а Range(“$A$7:$V$7,$X$7:$IV$7).The_Prist
будут выделены только существует несколько особенностей, это один из
- клавиатуре.Вырезать
- Можно также нажать сочетание выделенному диапазону.Выделите ячейку или диапазон щелкните ярлычок другого актуальными справочными материалами это не то
- в сформированной таблице “а”,- говорите и тогда такой пример?
- : Я так делаю Выделите строку, скопируйте: sofi, честно - ячейки с данными, в зависимости от тех случаев, когда
Вырезанные ячейки переместятся на. клавиш CTRL+V.При копировании значения последовательно ячеек с данными, листа или выберите
Результат: 
OLEGOFF ), а Range(“$A$7:$V$7,$X$7:$IV$7).The_Prist
актуальными справочными материалами это не то
в зависимости от тех случаев, когдаВозможно, вам приходилось работать с листами, в которых использовалась, формула типа: =СУММ(А5000:А5078). Вы гадали, что же находится в ячейках А5000:А5078!? Если в ячейках А5000:А5078 содержатся объемы продаж по регионам, не кажется ли вам формула =СУММ(ПродажиРегионы) более понятной? В данной главе описываются способы присвоения имен отдельным ячейкам и диапазонам ячеек, а также способы вставки имен диапазонов в формулы. [1]
Как создать именованный диапазон?
Существуют три способа создания именованных диапазонов:
- путем ввода имени диапазона в поле Имя;
- путем выбора на вкладке ФОРМУЛЫ в группе Определенные имена инструмента Создать из выделенного;
- путем выбора на вкладке ФОРМУЛЫ в группе Определенные имена инструментов Присвоить имя или Диспетчер имен.
Для создания имени диапазона с помощью поля Имя (рис. 1.1) выделите ячейку или диапазон ячеек, которым требуется присвоить имя, установите курсор в поле Имя, введите имя диапазона, и нажмите клавишу <Enter>. На рис. 1.1 ячейке В3 присвоено имя Старт.
Рис. 1.1. Создание имени диапазона путем выбора диапазона ячеек и ввода имени в поле Имя
Скачать заметку в формате Word или pdf, примеры в формате Excel
При нажатии в поле Имя на стрелку раскрывающегося списка появятся имена диапазонов, определенные в текущей книге (рис. 1.2). При выборе в поле Имя имени диапазона все ячейки, соответствующие этому диапазону, отмечаются автоматически. Это позволяет убедиться в правильности выбора ячейки или диапазона ячеек для указанного имени. В именах диапазонов регистр не учитывается. Например, если выбрать имя Финиш, будет отмечена ячейка Е8 (рис. 1.3).
Рис. 1.2. Список имен диапазонов
Рис. 1.3. При выборе имени диапазона отмечаются все ячейки, соответствующие этому диапазону
При нажатии клавиши <F3> открывается диалоговое окно Вставка имени, в котором отображаются имена всех диапазонов.
Присвоение имени означает, что вместо любой ссылки Старт в формуле будет автоматически подставлено значение из ячейки В3.
Предположим, что необходимо присвоить имя Данные прямоугольному диапазону ячеек A1:B5. Выделите диапазон ячеек A1:B5, введите с клавиатуры Данные в поле Имя и нажмите клавишу <Enter>. Теперь с помощью формулы =СРЗНАЧ(Данные) можно вычислить среднее значение содержимого ячеек A1:B4 (рис. 1.4).
Рис. 1.4. Присвоение диапазону A1:B5 имени Данные и нахождение среднего значения именованного диапазона
Иногда требуется присвоить имя диапазону ячеек, состоящему из нескольких несмежных прямоугольных диапазонов. Например, B3:C4, E6:G7 и B10:C10 (рис. 1.5). Для присвоения имени выделите любой из трех прямоугольников. Удерживая клавишу <Ctrl>, выделите остальные два диапазона. Отпустите клавишу <Ctrl>, введите имя Несмежный в поле Имя и нажмите клавишу <Enter>. Теперь имя Несмежный в любой формуле указывает на содержимое ячеек 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. Диспетчер имен
Для изменения имени диапазона дважды щелкните кнопкой мыши на имени этого диапазона или выделите его и нажмите кнопку Изменить; после этого можно изменить имя диапазона, ячейки в диапазоне и примечания. Область действия не подлежит изменению. Для удаления какого-либо подмножества имен диапазонов сначала выделите имена диапазонов, которые требуется удалить. Если имена диапазонов перечислены последовательно, выделите первое имя в группе имен, которую требуется удалить, затем, удерживая клавишу <Shift>, выделите последнее имя в группе. Если требуемые имена не перечислены друг за другом, можно выделить любое из имен, которое необходимо удалить, а далее, удерживая клавишу <Ctrl>, выделить остальные требуемые имена диапазонов. Затем для удаления выбранных имен диапазонов нажмите кнопку Удалить.
Редактирование формул в диалоговых окнах
Когда 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 формулы нажать клавишу <F3>, откроется диалоговое окно Вставка имени. Кроме того, можно вызвать на экран список доступных имен диапазонов, если после начала ввода на вкладке ФОРМУЛЫ в группе Определенные имена выбрать инструмент Использовать в формуле. И, наконец, если вы помните первые буквы имени диапазона, и начнете их вводить в формуле, 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 список всех имен диапазонов (и представляемых ими ячеек)?
Откройте окно Вставка имени с помощью клавиши <F3> и нажмите кнопку Все имена (рис. 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 раньше назывался блоками. Диапазон – это выделенная прямоугольная область прилегающих ячеек. Данное определение понятия легче воспринять на практических примерах.
В формулах диапазон записывается адресами двух ячеек, которые разделенные двоеточием. Верхняя левая и правая нижняя ячейка, которая входит в состав диапазона, например A1:B3.
Обратите внимание! Ячейка, от которой начинается выделение диапазона, остается активной. Это значит, что при выделенном диапазоне данные из клавиатуры будут введены в его первую ячейку. Она отличается от других ячеек цветом фона.
К диапазонам относятся:
- Несколько ячеек выделенных блоком (=B5:D8).
- Одна ячейка (=A2:A2).
- Целая строка (=18:18) или несколько строк (=18:22).
- Целый столбец (=F:F) или несколько столбцов (=F:K).
- Несколько несмежных диапазонов (=N5:P8;E18:H25;I5:L22).
- Целый лист (=1:1048576).
Все выше перечисленные виды блоков являются диапазонами.
Работа с выделенным диапазоном ячеек MS Excel
Выделение диапазонов – это одна из основных операций при работе с Excel. Диапазоны используют:
- при заполнении данных;
- при форматировании;
- при очистке и удалении ячеек;
- при создании графиков и диаграмм и т.п.
Способы выделения диапазонов:
- Чтобы выделить диапазон, например A1:B3, нужно навести курсор мышки на ячейку A1 и удерживая левую клавишу мышки провести курсор на ячейку B3. Казалось бы, нет ничего проще и этого достаточно для практических знаний. Но попробуйте таким способом выделить блок B3:D12345.
- Теперь щелкните по ячейке A1, после чего нажмите и удерживайте на клавиатуре SHIFT, а потом щелкните по ячейке B3. Таким образом, выделился блок A1:B3. Данную операцию выделения условно можно записать: A1 потом SHIFT+B3.
- Диапазоны можно выделять и стрелками клавиатуры. Щелкните по ячейке D3, а теперь удерживая SHIFT, нажмите клавишу «стрелка вправо» три раза пока курсор не переместится на ячейку G3. У нас выделилась небольшая строка. Теперь все еще не отпуская SHIFT, нажмите клавишу «стрелка вниз» четыре раза, пока курсор не перейдет на G7. Таким образом, мы выделили блок диапазона D3:G7.
- Как выделить несмежный диапазон ячеек в Excel? Выделите мышкой блок B3:D8. Нажмите клавишу F8 чтобы включить специальный режим. В строке состояния появится сообщение: «Расширить выделенный фрагмент». И теперь выделите мышкой блок F2:K5. Как видите, в данном режиме мы имеем возможность выделять стразу несколько диапазонов. Чтобы вернутся в обычный режим работы, повторно нажмите F8.
- Как выделить большой диапазон ячеек в Excel? Клавиша F5 или CTRL+G. В появившемся окне, в поле «Ссылка» введите адрес: B3:D12345 (или b3:d12345) и нажмите ОК. Таким образом, вы без труда захватили огромный диапазон, всего за пару кликов.
- В поле «Имя» (которое расположено слева от строки формул) задайте диапазон ячеек: B3:D12345 (или b3:d12345) и нажмите «Enter».
Способ 5 и 6 – это самое быстрое решение для выделения больших диапазонов. Небольшие диапазоны в пределах одного экрана лучше выделять мышкой.
Выделение диапазонов целых столбцов или строк
Чтобы выделить диапазон нескольких столбцов нужно подвести курсор мышки на заголовок первого столбца и удерживая левую клавишу протянуть его до заголовка последнего столбца. В процессе мы наблюдаем подсказку Excel: количество выделенных столбцов.
Выделение строк выполняется аналогичным способом только курсор мышки с нажатой левой клавишей нужно вести вдоль нумерации строк (по вертикали).
Выделение диапазона целого листа
Для выделения диапазона целого листа следует сделать щелчок левой кнопкой мышки по верхнему левому уголку листа, где пересекаются заголовки строк и столбцов. Или нажать комбинацию горячих клавиш CTRL+A.
Выделение несмежного диапазона
Несмежные диапазоны складываются из нескольких других диапазонов.
Чтобы их выделять просто удерживайте нажатие клавиши CTRL, а дальше как при обычном выделении. Также в данной ситуации особенно полезным будет режим после нажатия клавиши F8: «Расширить выделенный фрагмент».