Адрес выделенного диапазона ячеек
На этом шаге мы рассмотрим выделение диапазонов ячеек рабочего листа.
Диапазоном называется группа ячеек. Чтобы задать адрес диапазона, нужно указать адрес его левой верхней и правой нижней ячеек, разделив их двоеточием.
Приведем примеры адресов диапазонов:
| A1:B1 | Две ячейки, расположенные в одной строке и в двух соседних столбцах. |
| C24 | Этот диапазон состоит из одной ячейки. |
| A1:A100 | 100 ячеек столбца А. |
| A1:D4 | 16 ячеек, расположенные в четырех строках и четырех столбцах. |
| C1:C65536 | Все ячейки одного (третьего) столбца (этот диапазон можно указать следующим образом С:С). |
| A6:IV6 | Все ячейки одной (шестой) строки (этот диапазон можно указать следующим образом 6:6). |
| A1:IV65536 | Все ячейки рабочего листа. |
Чтобы выполнить над диапазоном ячеек рабочего листа некоторую операцию, нужно сначала выделить этот диапазон. При выборе диапазона цвет ячеек изменяется, исключение составляет только активная ячейка, которая сохраняет свой обычный цвет. На рисунке 1 показан выделенный диапазон рабочего листа.
Рис. 1. Пример выбранного диапазона ячеек
Диапазон можно выделить несколькими способами:
Рис. 2. Диалоговое окно Переход
После щелчка на кнопке OK программа Excel выделит ячейки указанного диапазона.
На следующем шаге рассмотрим выделение строк и столбцов рабочего листа целиком.
Выделение диапазона ячеек в Excel
Диапазоны в Excel раньше назывался блоками. Диапазон – это выделенная прямоугольная область прилегающих ячеек. Данное определение понятия легче воспринять на практических примерах.
В формулах диапазон записывается адресами двух ячеек, которые разделенные двоеточием. Верхняя левая и правая нижняя ячейка, которая входит в состав диапазона, например A1:B3.
Обратите внимание! Ячейка, от которой начинается выделение диапазона, остается активной. Это значит, что при выделенном диапазоне данные из клавиатуры будут введены в его первую ячейку. Она отличается от других ячеек цветом фона.
К диапазонам относятся:
Все выше перечисленные виды блоков являются диапазонами.
Работа с выделенным диапазоном ячеек MS Excel
Выделение диапазонов – это одна из основных операций при работе с Excel. Диапазоны используют:
Способы выделения диапазонов:
Способ 5 и 6 – это самое быстрое решение для выделения больших диапазонов. Небольшие диапазоны в пределах одного экрана лучше выделять мышкой.
Выделение диапазонов целых столбцов или строк
Чтобы выделить диапазон нескольких столбцов нужно подвести курсор мышки на заголовок первого столбца и удерживая левую клавишу протянуть его до заголовка последнего столбца. В процессе мы наблюдаем подсказку Excel: количество выделенных столбцов.
Выделение строк выполняется аналогичным способом только курсор мышки с нажатой левой клавишей нужно вести вдоль нумерации строк (по вертикали).
Выделение диапазона целого листа
Для выделения диапазона целого листа следует сделать щелчок левой кнопкой мышки по верхнему левому уголку листа, где пересекаются заголовки строк и столбцов. Или нажать комбинацию горячих клавиш CTRL+A.
Выделение несмежного диапазона
Несмежные диапазоны складываются из нескольких других диапазонов.
Чтобы их выделять просто удерживайте нажатие клавиши CTRL, а дальше как при обычном выделении. Также в данной ситуации особенно полезным будет режим после нажатия клавиши F8: «Расширить выделенный фрагмент».
Диапазон Excel
Имя ячейки
Начнем с простого – присвоим имя ячейке. Для этого просто выделяем ее (1) и в поле имени (2) вместо адреса ячейки указываем произвольное название, которое легко запомнить.
Длина имени ограничена 255 символами, что более чем достаточно. Также в имени не должно быть пробелов, поэтому если оно состоит из нескольких слов, то их можно разделять знаком подчеркивания.
Если теперь на других листах книги нам нужно будет вывести данное значение или использовать его в дальнейших расчетах, то не обязательно переключаться на первый лист и указывать ячейку вручную. Достаточно просто ввести имя ячейки и ее значение будет подставлено.
Ячейки, строки, столбцы
Давайте начнем с выбора ячеек, строк и столбцов.
Примеры диапазона
Диапазон представляет собой набор из двух и более ячеек.
Заполнение диапазона
Чтобы заполнить диапазон, следуйте инструкции ниже:
Именованный диапазон
Аналогичным образом можно задать имя и для диапазона ячеек, то есть выделим диапазон (1) и в поле имени укажем его название (2):
Далее это название можно использовать в формулах, например, при вычислении суммы:
Также создать именованный диапазон можно с помощью вкладки Формулы, выбрав инструмент Задать имя.
Появится диалоговое окно, в котором нужно указать имя диапазона, выбрать область, на которую имя будет распространяться (то есть на всю книгу целиком или на отдельные ее листы), при необходимости заполнить примечание, а далее выбрать соответствующий диапазон на листе.
Для работы с существующими диапазонами на вкладке Формулы есть Диспетчер имен.
С его помощью можно удалять, изменять или добавлять новые имена ячейкам или диапазонам.
При этом важно понимать, что если вы используете именованные диапазоны в формулах, то удаление имени такого диапазона приведет к ошибкам.
Задача
Имеется таблица продаж по месяцам некоторых товаров (см. Файл примера ):
Для создания динамического диапазона:
ВНИМАНИЕ! При использовании функции СЧЕТЗ() необходимо убедиться в отсутствии пустых ячеек! Т.е. нужно заполнять перечень месяцев без пропусков.
Теперь создадим еще один динамический диапазон для суммирования продаж.
Для создания динамического диапазона :
Функция ПОИСКПОЗ() ищет в строке 5 (перечень месяцев) выбранный пользователем месяц (ячейка С1 с выпадающим списком) и возвращает соответствующий номер позиции в диапазоне поиска (названия месяцев должны быть уникальны, т.е. этот пример не годится для нескольких лет). На это число столбцов смещается левый верхний угол нашего динамического диапазона (от ячейки А6 ), высота диапазона не меняется и всегда равна 12 (при желании ее также можно сделать также динамической – зависящей от количества товаров в диапазоне).
И наконец, записав в ячейке С2 формулу = СУММ(Продажи_за_месяц) получим сумму продаж в выбранном месяце.
Или, например, в апреле.
Формула подсчитывает количество элементов в строке 5 (функция СЧЁТЗ() ) и определяет ссылку на последний элемент в строке (функция ИНДЕКС() ), тем самым возвращает ссылку на диапазон B5:E5 .
Визуальное отображение динамического диапазона
Как посчитать количество ссылок на столбцы таблицы Excel
Пример 2. Определить количество столбцов в таблице и записать это значение в ячейку A16.
Используем формулу ОБЛАСТИ, поочередно выделяя каждый столбец ячейки в качестве параметра. Перед выбором последующего столбца нажимаем и удерживаем кнопку Ctrl. Если добавить символ «)» и нажать Enter, появится диалоговое окно с сообщением о том, что было введено слишком много аргументов. Добавим дополнительные открывающую и закрывающую скобки.
Определение принадлежности ячейки к диапазону таблицы
Пример 3. Определить, принадлежит ли ячейка заданному диапазону ячеек.
Рассматриваемая функция также позволяет определить, принадлежит ли ячейка выделенной области. Выполним следующие действия:
Данная ошибка означает, что ячейка не принадлежит выделенной области.
Если выделить несколько ячеек внутри диапазона, функция ОБЛАСТИ вернет количество выделенных ячеек:
Описанные особенности работы данной функции могут быть полезны при работе с большим количеством таблиц данных.
Перемещение и копирование ячеек и их содержимого
Смотрите также =ЕСЛИ(ЕЧИСЛО(A2);A2;B2) протягиваете копируете соответственно макрос “Фильтра” хамом станешь…Оно кому только в таблице.: Думаю возможно если столбец. Т.е. получается, ВСЕ”, затем произведите и нажатьВставить более сложная процедура,Нажмите иконку Вставить на указанных ниже действий.Вставить, можно выбрать временно отобразить данные, выделенный фрагмент наПримечание: вставляете как значения выводит таблицу с надо?
The_Prist копировать выделенный диапазон что выделяем для описанные выше действияCtrl+Space. Только имейте в чем хотелось бы. вкладке Главная илиЧтобы переместить ячейки, нажмите. параметры вставки, которые которые не нужно другой лист или Мы стараемся как можно
grablik заданными параметрами дат.Юрий М: В примере все по одной ячейке.
копирования не Range(“7:7″ с формой.(Пробел). Таким способом виду, что здесь На самом деле, нажмите Ctrl+V на кнопкуСочетание клавиш
следует применить к
будут выделены только существует несколько особенностей, это один из
Вырезанные ячейки переместятся на. клавиш CTRL+V.При копировании значения последовательно ячеек с данными, листа или выберите
Выбор ячеек и диапазонов с помощью процедур Visual Basic в Excel
Корпорация Майкрософт предоставляет примеры программирования только в целях демонстрации без явной или подразумеваемой гарантии. Данное положение включает, но не ограничивается этим, подразумеваемые гарантии товарной пригодности или соответствия отдельной задаче. Эта статья предполагает, что пользователь знаком с представленным языком программирования и средствами, используемыми для создания и отладки процедур. Специалисты службы поддержки Майкрософт могут объяснить возможности конкретной процедуры, но они не изменяют эти примеры, чтобы предоставить дополнительные функции или создать процедуры для удовлетворения конкретных требований. В примерах, приведенных в этой статье, используются методы Visual Basic, приведенные в следующей таблице.
В примерах, приведенных в этой статье, используются свойства, указанные в таблице ниже.
Выбор ячейки на активном листе
Чтобы выбрать ячейку D5 на активном листе, можно использовать любой из следующих примеров:
Выбор ячейки на другом листе в той же книге
Чтобы выбрать ячейку E6 на другом листе той же книги, можно использовать любой из следующих примеров:
Вы также можете активировать лист, а затем использовать метод 1, чтобы выбрать ячейку:
Выбор ячейки на листе в другой книге
Чтобы выбрать ячейку F7 на листе в другой книге, можно использовать любой из следующих примеров:
Вы также можете активировать лист, а затем использовать метод 1, чтобы выбрать ячейку:
Выбор диапазона ячеек на активном листе
Чтобы выбрать диапазон C2: D10 на активном листе, можно использовать любой из следующих примеров:
Выбор диапазона ячеек на другом листе в той же книге
Чтобы выбрать диапазон D3: E11 на другом листе той же книги, можно использовать любой из следующих примеров:
Вы также можете активировать лист, а затем использовать способ 4 выше, чтобы выбрать диапазон:
Выбор диапазона ячеек на листе в другой книге
Чтобы выбрать диапазон E4: F12 на листе в другой книге, можно использовать любой из следующих примеров:
Вы также можете активировать лист, а затем использовать способ 4 выше, чтобы выбрать диапазон:
Выбор именованного диапазона на активном листе
Чтобы выбрать именованный диапазон «Test» на активном листе, можно использовать любой из следующих примеров:
Выбор именованного диапазона на другом листе в той же книге
Чтобы выбрать именованный диапазон «Test» на другом листе той же книги, можно использовать следующий пример:
Вы также можете активировать лист, а затем использовать метод 7 выше для выбора именованного диапазона:
Выбор именованного диапазона на листе в другой книге
Чтобы выбрать именованный диапазон «Test» на листе в другой книге, можно использовать следующий пример:
Вы также можете активировать лист, а затем использовать метод 7 выше для выбора именованного диапазона:
Выбор ячейки относительно активной ячейки
Чтобы выделить ячейку с пятью строками ниже и четырьмя столбцами слева от активной ячейки, можно использовать следующий пример:
Чтобы выделить ячейку с двумя строками выше и тремя столбцами справа от активной ячейки, можно использовать следующий пример:
При попытке выбрать ячейку, которая находится в состоянии «не на листе», произойдет ошибка. В первом примере, приведенном выше, возвращается сообщение об ошибке, если активная ячейка находится в столбцах A-D, так как при перемещении четырех столбцов влево активная ячейка будет иметь недопустимый адрес ячейки.
Выбор ячейки относительно другой (неактивной) ячейки
Чтобы выделить ячейку с пятью строками ниже и четырьмя столбцами справа от ячейки C7, можно использовать любой из следующих примеров:
Выбор диапазона смещения ячеек в указанном диапазоне
Чтобы выбрать диапазон ячеек, размер которых совпадает с именованным диапазоном «Test», но с последующим сдвигом на четыре строки вниз и тремя столбцами вправо, можно использовать следующий пример:
Если именованный диапазон находится на другом (неактивном) листе, сначала активируйте этот лист, а затем выберите диапазон, используя следующий пример:
Выбор указанного диапазона и изменение размера выделенного фрагмента
Для выбора именованного диапазона «Database» и последующего расширения выделенного фрагмента на пять строк можно использовать следующий пример:
Выбор указанного диапазона, его смещение и изменение его размера
Чтобы выбрать диапазон четыре строки ниже и три столбца справа от именованного диапазона «база данных» и включить две строки и один столбец больше, чем именованный диапазон, можно использовать следующий пример:
Выбор объединения двух или более указанных диапазонов
Чтобы выбрать объединение (то есть область объединения) двух именованных диапазонов «Test» и «Sample», можно использовать следующий пример:
чтобы этот пример работал, оба диапазона должны находиться на одном листе. Кроме того, обратите внимание на то, что метод Union не работает на разных листах. Например, эта строка работает нормально.
Возвращает сообщение об ошибке:
Сбой метода Union класса приложения
Как выбрать пересечение двух или более указанных диапазонов
Чтобы выбрать пересечение двух именованных диапазонов «Test» и «Sample», можно использовать следующий пример:
Обратите внимание, что для работы этого примера оба диапазона должны находиться на одном листе.
В примерах, описанных в этой статье 17-21, описывается следующий пример набора данных. В каждом примере указывается диапазон ячеек в выбранном образце данных.
Выбор последней ячейки столбца с непрерывными данными
Чтобы выбрать последнюю ячейку в непрерывном столбце, используйте следующий пример:
Если этот код используется с образцом таблицы, будет выбрана ячейка A4.
Как выделить пустую ячейку в нижней части столбца непрерывных данных
Чтобы выделить ячейку под диапазоном смежных ячеек, используйте следующий пример:
Если этот код используется с образцом таблицы, будет выбрана ячейка A5.
Выбор всего диапазона смежных ячеек в столбце
Чтобы выбрать диапазон смежных ячеек в столбце, используйте один из следующих примеров:
Если этот код используется с образцом таблицы, будут выбраны ячейки a1 — A4.
Выбор всего диапазона несмежных ячеек в столбце
Чтобы выбрать диапазон ячеек, которые не являются смежными, используйте один из следующих примеров:
Если этот код используется с образцом таблицы, он выберет ячейки с a1 по A6.
Выбор прямоугольного диапазона ячеек
Чтобы выбрать прямоугольный диапазон ячеек вокруг ячейки, используйте метод CurrentRegion. Диапазон, выбранный методом CurrentRegion, это область, ограниченная любым сочетанием пустых строк и пустых столбцов. Ниже приведен пример использования метода CurrentRegion.
В этом коде будут выделены ячейки A1 — C4. В других примерах ниже показано, как выбрать один и тот же диапазон ячеек:
В некоторых случаях может потребоваться выделить ячейки a1 — C6. В этом примере метод CurrentRegion не будет работать из-за пустой строки на строке 5. В приведенных ниже примерах будут выбраны все ячейки:
Выбор нескольких несмежных столбцов различной длины
Чтобы выбрать несколько несмежных столбцов различной длины, используйте следующий пример таблицы и макроса:
При использовании этого кода с образцом таблицы ячейки a1: A3 и C1: C6 будут выбраны.
Примечания к примерам
Обычно свойство Активешит можно опустить, так как оно подразумевает, что конкретный лист не является именем. Например, вместо
Вы можете использовать следующие компоненты:
Также можно опустить свойство Активеворкбук. Если не указана конкретная книга, подразумевается активная книга.
При использовании метода Application. goto, если вы хотите использовать два метода Cell в методе Range, если указанный диапазон находится на другом (неактивном) листе, необходимо включить объект Sheets каждый раз. Например:
Для любого элемента в кавычках (например, именованного диапазона «Test») можно также использовать переменную, значение которой является строкой текста. Например, вместо
Excel 10. Диапазон и вычисления в нем
По окончании этого урока вы сможете:
Скачайте файл тут. Откройте файл. (Признаюсь – не владею ни одним из этих видов творчества. Просто взяла из Интернета)
1. Диалоговые окна «Вставка функции» и «Аргументы функции»
Шаг 1. Вводим в ячейку А7 Максимум и переходим в ячейку В7:
Шаг 2. Открываем диалоговое окно «Вставка функции» (лента Формулы → команда Вставить функцию):
Как только мы вызвали диалоговое окно «Вставка функции», в вычисляемой ячейке появился знак «=».
Шаг 3. Выбираем в списке из 10 последних использовавших функций «МАКС» (поле «Выберите функцию»).
Это ещё один способ вставить функцию в дополнение к уже известным по прошлой статье (Excel 9). В Excel встроено около 300 функций. Вряд ли вы будете использовать все функции. Поэтому полезно знать, где находится список из самых вами любимых функций, чтобы избежать длительных поисков.
В поле «Поиск функции» вы можете ввести описание нужной вам функции, и Excel услужливо поможет вам в поиске.
При нажатии кнопки выпадающего меню поля «Категории» вы увидите перечень всех категорий функций:
Нашли функцию МАКС? ОК!
Но у нас с вами мелкая неприятность – диалоговое окно «Аргументы функции» перекрывает ряд чисел:
Шаг 4. Щелкаем по значку со стрелкой справа от поля «Число!». Диалоговое окно свернется и вам надо выбрать диапазон, в котором будет искаться максимум.
Шаг 5. Щелкаем по значку со стрелкой справа от поля «Число!» – диалоговое окно «Аргументы функции» развернется. Нажимаем ОК.
В предыдущих версиях Excel эта операция носила название «Мастер функций.
| Понять и запомнить! | Кнопка со стрелкой, которая позволяет свернуть и развернуть диалоговое окно, встречается очень часто, так что рекомендую запомнить эту полезную операцию |
2. Имя диапазона
Хорошо, если диапазон для вычислений у нас небольшой, хорошо, если вычисляемая ячейка находится по вычисляемым диапазоном. Но мечты и реальность не всегда совпадают. Вот тут на помощь придет полезная возможность, которая называется «Имя диапазона».
1 способ.
Шаг 1. Выделяем диапазон ячеек В1:В6.
Шаг 2. Задаем имя диапазону (лента Формулы → группа команд Определенные имена → Создать из выделенного):
Откроется диалоговое окно «Создание имен из выделенного диапазона». Оставим активным параметр «в строке выше». ОК.
2 способ.
Шаг 3. Выделите диапазон ячеек А1:А6.
Шаг 4. Задаем имя диапазону (лента Формулы → группа команд Определенные имена → Присвоить имя):
Откроется диалоговое окно «Создание имени».
Шаг 5. Нажимаем на кнопку выпадающего меню имени ячейки (неважно, где находится активная ячейка):
Шаг 6. Щелкаем ЛМ по первому имени в этом списке:
Диапазон с именем «Наименование_курса» выделился полностью.
| Понять и запомнить! | Разница между двумя способами: 1. Имя диапазона определяется автоматически 2. Имя диапазона можно задать по своему желанию |
Шаг 7. Щелкните в любой ячейке по вашему выбору. Набираем «=су»:
Двойной щелчок ЛМ по «СУММ».
Шаг 8. Выбираем диапазон по имени (набираем непосредственно в ячейке «ц». сразу появляется выпадающий список, в котором присутствуют функции, начинающие на «ц», и имя нашего диапазона). Двойной щелчок ЛМ по имени диапазона:
Шаг 9. И обязательно закрывающая скобка! Требования Excel по части синтаксиса написания формул надо соблюдать.
Шаг 10. Нажимаем Enter:
Смотрим на строку формул: =СУММ(Цена). То есть по имени определил соответствующий диапазон.
Шаг 11. Лента Формулы → группа команд Определенные имена → команда Диспетчер имен. Откроется диалоговое окно «Диспетчер имен»:
В этом окне вы можете создать новое имя для диапазона, изменить или удалить имеющееся имя диапазона. В поле «Диапазон» прописано: на каком листе книги расположен диапазон и адреса граничных ячеек. Кнопка с стрелкой, которая находится справа от этого поля, позволяет свернуть диалоговое окно и изменить диапазон.
3. Абсолютный и относительный адрес ячейки
Скопируем одну таблицу. Вот тут внимание! Если вы просто выделите весь диапазон и перенесете на другое место листа (Excel 5), то вы увидите следующую картину:
Потом потратим драгоценное время на настройки ширины и высоты ячеек. Так что не торопитесь.
Шаг 1. Выделяем таблицу со значениями и копируем в буфер обмена:
Шаг 2. Вставляем содержимое буфера обмена в ячейку F1 специальной вставкой с сохранение ширины столбцов:
Советую постепенно заучивать кнопки специальной вставки – сбережете время и нервы.
Сейчас мы подсчитаем стоимость курса для группы из 20 человек (больше набирать смысла нет). Для первой таблицы введите в диапазоне С2:С6 число «20» в каждую ячейку. Для второй таблицы введите число «20» в ячейку Н1:
Шаг 3. В ячейку D2 введите формулу «=ПРОИЗВЕД(В2;С2). Можно проще: «=В2*С2»:
Шаг 4. А теперь распространим эту формулу по всему столбцу стоимости курсов. Это можно сделать двумя способами.
1 способ.
2 способ.
Посмотрим на формулы в каждой ячейке.
Шаг 5. Покажем формулы (лента Формулы → группа команд Зависимости формул → команда Показать формулы):
Несмотря на наше недвусмысленное указание, какие ячейки будут перемножены, при копировании формулы вниз по диапазону адрес ячейки учитывает номер строки.
| Понять и запомнить! | Относительный адрес ячейки адрес ячейки соотносится с номером строки, если мы идем вниз и адрес ячейки соотносится с именем столбца, если мы идем вправо |
Если вы ещё раз щелкните ЛМ по команде «Показать формулы», то в ячейках мы увидим значение формулы.
Поработаем со второй таблицей.
Шаг 6. В ячейку Н2 вводим формулу «=G2*H1»:
Теперь нам надо указать, что при копировании формулы ссылка будет только на ячейку Н1, то есть адрес этой ячейки будет абсолютным. Для указания абсолютного адреса в Excel служит знак «$» или знак доллара. Если не лень, ставьте вручную (Shift+4 в английской раскладке клавиатуры). Но лучше воспользоваться функциональной клавишей F4, которой все равно какая раскладка клавиатуры):
Если вы работаете за ноутбуком, то не забудьте в дополнение одновременно нажить клавишу Fn.
Шаг 7. Поместите курсор на имя ячейки «Н1» в формуле и нажмите Fn на клавиатуре:
Шаг 8. Нажмем Enter и скопируем формулу по диапазону:
Шаг 9. Покажем формулы (лента Формулы → группа команд Зависимости формул → команда Показать формулы):
При копировании формулы адрес с ценой курса соответствующим образом меняется, а адрес ячейки Н1 повторяется – это абсолютный адрес.
Если вы ещё раз щелкните ЛМ по команде «Показать формулы», то в ячейках мы увидим значение формулы.
Теперь вы сможете:
По ходу дела мы узнали, что есть:
