Диапазон таблицы в программе ms excel что это

Начнем с самого простого: дайте ячейке имя. Для этого просто выделите ее (1) и в поле имени (2) вместо адреса ячейки введите любое легко запоминающееся имя.

Именная ячейка C7

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

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

Использование имени ячейки

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

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

1. Чтобы выбрать ячейку C3, нажмите на поле на пересечении столбца C и строки 3.
2. Чтобы выбрать столбец C, щелкните по заголовку столбца C.
3. Чтобы выбрать строку 3, щелкните по заголовку строки 3.

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

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

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

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

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

1. В ячейку B2 введите значение 2.
2. Выделите ячейку B2, зажмите ее правый нижний угол и перетащите вниз к ячейке B8. Результат: Эта техника перетаскивания очень важна, вы будете часто использовать ее в Excel. Вот еще один пример:
3. Введите значение 2 в ячейку B2 и значение 4 в ячейку B3.
4. Выделите ячейки B2 и B3, зажмите правый нижний угол этого диапазона и перетащите его вниз. Excel автоматически заполнит диапазон на основе формулы из первых двух значений. Довольно круто, правда? Вот еще один пример:
5. Введите дату 13/6/2013 в ячейку B2 и дату 16/6/2013 в ячейку B3 (на рисунке показан американский эквивалент этих дат).
6. Выделите ячейки B2 и B3, зажмите правый нижний угол этого диапазона и перетащите его вниз.

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

Диапазон ячеек можно назвать аналогичным образом, т.е. выделить диапазон (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.

Диапазон в Excel

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

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

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

1. Для выбора ячейки C3 кликните по полю на пересечении столбца C и строки 3.
2. Чтобы выбрать столбец C, кликните по заголовку столбца C.
3. Чтобы выбрать строку 3, кликните по заголовку строки 3.

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

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

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

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

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

1. Введите значение 2 в ячейку B2.
2. Выделите ячейку В2, зажмите её нижний правый угол и протяните вниз до ячейки В8. Результат: Эта техника протаскивания очень важна, вы будете часто использовать её в Excel. Вот еще один пример:
3. Введите значение 2 в ячейку В2 и значение 4 в ячейку B3.
4. Выделите ячейки B2 и B3, зажмите нижний правый угол этого диапазона и протяните его вниз. Excel автоматически заполняет диапазон, основываясь на шаблоне из первых двух значений. Классно, не правда ли? Вот еще один пример:
5. Введите дату 13/6/2013 в ячейку В2 и дату 16/6/2013 в ячейку B3 (на рисунке приведены американские аналоги дат).
6. Выделите ячейки B2 и B3, зажмите нижний правый угол этого диапазона и протяните его вниз.

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

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

1. Выделите диапазон и зажмите его границу.
2. Перетащите диапазон на новое место.

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

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

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

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

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

1. Выделите строку 3.
2. Кликните по ней правой кнопкой мыши и выберите команду Insert (Вставить). Результат: Строки, расположенные ниже новой строки, сдвигаются вниз. Аналогичным образом можно вставить столбец.

Источник: office-guru.ru

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. Задаем имя диапазону (лента Формулы → группа команд Определенные имена → Присвоить имя):

Откроется диалоговое окно «Создание имени».

1. По умолчанию содержимое первой ячейки выделенного диапазона становится именем диапазона. Но никто не мешает нам задать другое имя. Обратите внимание, что пробел заменился на нижнее тире. Что поделаешь? Требование Excel.
2. Определение области действия имени: вся книга или конкретный лист.
3. Пояснения, которые мы сочтем нужным дать этому диапазону. Пояснения не участвуют в вычислениях, операциях и во всем другом прочем. Это «напоминалка» для нас.
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 способ.

1. Подвести курсор к зеленому квадратику в правом нижнем углу ячейки с формулой – курсор превратиться в черный крест (курсор заполнения таблицы – Excel 3)
2. Нажать ЛМ и, не отпуская, протянуть на весь диапазон:

2 способ.

1. Подвести курсор к зеленому квадратику в правом нижнем углу ячейки с формулой – курсор превратиться в черный крест (курсор заполнения таблицы – Excel 3)
2. Дважды щелкнуть ЛМ:

Посмотрим на формулы в каждой ячейке.

Шаг 5. Покажем формулы (лента Формулы → группа команд Зависимости формул → команда Показать формулы):

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

Понять и запомнить!

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

Если вы ещё раз щелкните ЛМ по команде «Показать формулы», то в ячейках мы увидим значение формулы.

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

Шаг 6. В ячейку Н2 вводим формулу «=G2*H1»:

Теперь нам надо указать, что при копировании формулы ссылка будет только на ячейку Н1, то есть адрес этой ячейки будет абсолютным. Для указания абсолютного адреса в Excel служит знак «$» или знак доллара. Если не лень, ставьте вручную (Shift+4 в английской раскладке клавиатуры). Но лучше воспользоваться функциональной клавишей F4, которой все равно какая раскладка клавиатуры):

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

Шаг 7. Поместите курсор на имя ячейки «Н1» в формуле и нажмите Fn на клавиатуре:

1. Относительный адрес. Относительная адресация – это изменяющийся при копировании и перемещении формулы адрес ячейки, содержащей исходное данное (используется по умолчанию).
2. Абсолютный адрес. Абсолютная адресация – это не изменяющийся при копировании и перемещении формулы адрес ячейки, содержащей исходное данное. Выглядит такиой адрес следующим образом: $А$1. Знак доллара обозначает, что значения строки и столбца «закреплены».
3. Смешанный или частичный адрес. Частичная абсолютная адресация указывается, если при копировании формулы не меняется номер строки или наименование столбца. При этом символ $ в первом случае ставится перед номером строки, а во втором – перед наименованием столбца: B$5; D$12.

Шаг 8. Нажмем Enter и скопируем формулу по диапазону:

Шаг 9. Покажем формулы (лента Формулы → группа команд Зависимости формул → команда Показать формулы):

При копировании формулы адрес с ценой курса соответствующим образом меняется, а адрес ячейки Н1 повторяется – это абсолютный адрес.

Если вы ещё раз щелкните ЛМ по команде «Показать формулы», то в ячейках мы увидим значение формулы.

Теперь вы сможете:

1. Работать с диалоговыми окнами «Вставка функции» и «Аргументы функции»
2. Присвоить имя диапазону ячейки и работать с именами диапазонов
3. Ввести формулу с абсолютным и относительным адресом ячейки.

По ходу дела мы узнали, что есть:

1. Ещё одна возможность специальной вставки буфера обмена
2. Работа с курсором заполнения диапазона (черный крестик)
3. Режим «Показать формулы»

• excel адрес диапазона
• excel диапазон ячеек формуле
• excel найти адрес диапазона
• абсолютный адрес ячейки excel
• адрес ячейки excel
• виды адресов ячеек excel
• виды адресов ячейки excel
• диапазон excel
• имя диапазона excel
• как удалить имя диапазона excel
• относительный адрес ячейки excel
• присвоить имя диапазону excel
• смешанный адрес ячейки excel
• типы адресов ячеек excel

Источник: prooffice24.ru