Функция АДРЕС возвращает адрес определенной ячейки (текстовое значение), на которую указывают номера столбца и строки. К примеру, в результате выполнения функции =АДРЕС(5;7) будет выведено значение $G$5.
Примечание: наличие символов «$» в адресе ячейки $G$5 свидетельствует о том, что ссылка на данную ячейку является абсолютной, то есть не меняется при копировании данных.
Функция АДРЕС в Excel: описание особенностей синтаксиса
Функция АДРЕС имеет следующую синтаксическую запись:
=АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[a1];[имя_листа])
Первые два аргумента данной функции являются обязательными для заполнения.
Описание аргументов:
- Номер_строки – числовое значение, соответствующее номеру строки, в которой находится требуемая ячейка;
- Номер_столбца – числовое значение, которое соответствует номеру столбца, в котором расположена искомая ячейка;
- [тип_ссылки] – число из диапазона от 1 до 4, соответствующее одному из типов возвращаемой ссылки на ячейку:
- абсолютная на всю ячейку, например — $A$4
- абсолютная только на строку, например — A$4;
- абсолютная только на столбец, например — $A4;
- относительная на всю ячейку, например A4.
- [a1] – логическое значение, определяющее один из двух типов ссылок: A1 либо R1C1;
- [имя_листа] – текстовое значение, которое определяет имя листа в документе Excel. Используется для создания внешних ссылок.
Примечания:
- Ссылки типа R1C1 используются для цифрового обозначения столбцов и строк. Для возвращения ссылок такого типа в качестве параметра a1 должно быть явно указано логическое значение ЛОЖЬ или соответствующее числовое значение 0.
- Стиль ссылок в Excel может быть изменен путем установки/снятия флажка пункта меню «Стиль ссылок R1C1», который находится в «Файл – Параметры – Формулы – Работа с Формулами».
- Если требуется ссылка на ячейку, которая находится в другом листе данного документа Excel, полезно использовать параметр [имя_листа], который принимает текстовое значение, соответствующее названию требуемого листа, например «Лист7».
Примеры использования функции АДРЕС в Excel
Пример 1. В таблице Excel содержится ячейка, отображающая динамически изменяемые данные в зависимости от определенных условий. Для работы с актуальными данными в таблице, которая находится на другом листе документа требуется получить ссылку на данную ячейку.
На листе «Курсы» создана таблица с актуальными курсами валют:
На отдельном листе «Цены» создана таблица с товарами, отображающая стоимость в долларах США (USD):
В ячейку D3 поместим ссылку на ячейку таблицы, находящейся на листе «Курсы», в которой содержится информация о курсе валюты USD. Для этого введем следующую формулу: =АДРЕС(3;2;1;1;»Курсы»).
Значение параметров:
- 3 – номер строки, в которой содержится искомая ячейка;
- 2 – номер столбца с искомой ячейкой;
- 1 – тип ссылки – абсолютная;
- 1 – выбор стиля ссылок с буквенно-цифровой записью;
- «Курсы» — название листа, на котором находится таблица с искомой ячейкой.
Для расчета стоимости в рублях используем формулу: =B3*ДВССЫЛ(D3).
Функция ДВССЫЛ необходима для получения числового значения, хранимого в ячейке, на которую указывает ссылка. В результате вычислений для остальных товаров получим следующую таблицу:
Как получить адрес ссылки на ячейку Excel?
Пример 2. В таблице содержатся данные о цене товаров, отсортированные в порядке возрастания стоимости. Необходимо получить ссылки на ячейки с минимальной и максимальной стоимостью товаров соответственно.
Исходная таблица имеет следующий вид:
Для получения ссылки на ячейку с минимальной стоимостью товара используем формулу:
Функция АДРЕС принимает следующие параметры:
- число, соответствующее номеру строки с минимальным значением цены (функция МИН выполняет поиск минимального значения и возвращает его, функция ПОИСКПОЗ находит позицию ячейки, содержащей минимальное значение цены. К полученному значению добавлено 2, поскольку ПОИСКПОЗ осуществляет поиск относительно диапазона выбранных ячеек.
- 2 – номер столбца, в котором находится искомая ячейка.
Аналогичным способом получаем ссылку на ячейку с максимальной ценой товара. В результате получим:
Адрес по номерам строк и столбцов листа Excel в стиле R1C1
Пример 3. В таблице содержится ячейка, данные из которой используются в другом программном продукте. Для обеспечения совместимости необходимо предоставить ссылку на нее в виде R1C1.
Исходная таблица имеет следующий вид:
Для получения ссылки на ячейку B6 используем следующую формулу: =АДРЕС(6;2;1;0).
Аргументы функции:
- 6 – номер строки искомой ячейки;
- 2 – номер столбца, в котором содержится ячейка;
- 1 – тип ссылки (абсолютная);
- 0 – указание на стиль R1C1.
В результате получим ссылку:
Практическое применение функции АДРЕС: Поиск значения в диапазоне таблицы Excel по столбцам и строкам.
Примечание: при использовании стиля R1C1 запись абсолютной ссылки не содержит знака «$». Чтобы отличать абсолютные и относительные ссылки используются квадратные скобки «[]». Например, если в данном примере в качестве параметра тип_ссылки указать число 4, ссылка на ячейку примет следующий вид:
Скачать примеры функции АДРЕС для получения ссылки на ячейку в Excel
Так выглядит абсолютный тип ссылок по строкам и столбцам при использовании стиля R1C1.
В формулах EXCEL можно сослаться на значение другой ячейки используя ее адрес (=А1). Адрес ячейки в формуле можно записать по-разному, например: А1 или $A1 или $A$1. То, каким образом вы введете адрес в формулу, будет зависеть, как он будет модифицироваться при ее копировании в другие ячейки листа. Это пригодится при как построении обычных формул на листе, так и при создании
Именованных формул
, задания правил
Условного форматирования
и при формировании условий
Проверки данных
.
В подавляющем большинстве формул EXCEL используются ссылки на ячейки. Например, если в ячейке
В1
содержится формула =А1+5 , то означает, что в ячейку
В1
будет помещено значение ячейки
А1
находящейся на пересечении столбца
А
и строки
1
,
к которому прибавлено число 5. Также в формулах используются ссылки на диапазоны ячеек, например, формула =СУММ(А2:А11) вычисляет сумму значений из ячеек
А2
,
А3
, …
А11
. Однако, формула
=СУММ($А$2:$А$11)
также вычисляет сумму значений из тех же ячеек. Тогда в чем же разница? Разница проявляется при копировании этой формулы в соседние ячейки.
Абсолютная адресация (абсолютные ссылки)
Для создания абсолютной ссылки используется знак $. Ссылка на диапазона записывается ввиде
$А$2:$А$11
. Абсолютная ссылка позволяет при
копировании
формулы зафиксировать адрес диапазона или адрес ячейки. Рассмотрим пример.
Пусть в ячейке
В2
введена формула
=СУММ(
$А$2:$А$11
)
, а в ячейке
С2
формула
=СУММ(А2:А11).
Скопировав формулы вниз, например с помощью
Маркера заполнения,
во всех ячейках столбца
В
получим одну и ту же формулу
=СУММ(
$А$2:$А$11
)
, т.е. ссылка на диапазон ячеек при копировании
не изменилась
. А в столбце
С
получим другой результат: в ячейке
С3
будет формула =СУММ(A3:A12) , в ячейке
С4
будет формула =СУММ(A4:A13) и т.д. Т.е. при копировании ссылка была
модифицирована
.
Какая формула лучше? Все зависит от вашей задачи: иногда при копировании нужно фиксировать диапазон, в других случая это делать не нужно.
Другой пример.
Пусть в диапазоне
А1:А5
имеются числа (например, зарплата сотрудников отдела), а в
С1
– процент премии установленный для всего отдела. Для подсчета премии каждого сотрудника необходимо все зарплаты умножить на % премии. Рассчитанную премию поместим в диапазоне
В1:В5
. Для этого введем в ячейку
В1
формулу =А1*С1 . Если мы с помощью
Маркера заполнения
протянем формулу вниз, то получим в
В2:В5
нули (при условии, что в диапазоне
С2:С5
нет никаких значений). В ячейке
В5
будем иметь формулу =А5*С5 (EXCEL при копировании формулы модифицировал ссылки на ячейки, т.к. их адреса не были записаны в виде абсолютных ссылок).
Чтобы выйти из ситуации — откорректируем формулу в ячейке
В1
.
-
выделите ячейку
В1
; -
войдите в режим правки ячейки (нажмите клавишу
F2
) или поставьте курсор вСтроку формул
;
-
поставьте курсор на ссылку
С1
(можно перед
С
, перед или после
1
); -
нажмите один раз клавишу
F
4
. Ссылка
С1
выделится и превратится в
$
C
$1
(при повторных нажатиях клавиши
F
4
ссылка будет принимать последовательно вид
C
$1, $
C
1,
C
1, $
C
$1
, …). Ссылка вида
$
C
$1
называется
абсолютно
,
C
$1, $
C
1
– смешанными, а
С1
—
относительной
.
Такм образом, введем в
В1
формулу =А1*$С$1 . Это можно сделать и в ручную, введя знак $ перед буквой столбца и перед номером строки.
Нажмем
ENTER
и протянем ее вниз. Теперь в
В5
будет правильная формула =А5*$С$1 . Всем сотрудникам теперь достанется премия :).
Относительная адресация (относительные ссылки)
Введем в ячейку
B1
формулу =А1 , представляющую собой относительную ссылку на ячейку
А1
. Что же произойдет с формулой при ее копировании в ячейки расположенные ниже
В1
? После протягивания ее вниз
Маркером заполнения
, в ячейке
В5
будет стоять формула =А5 , т.е. EXCEL
изменил
первоначальную формулу =A1 . При копировании вправо в ячейку
С1
формула будет преобразована в =В1.
Теперь примеры.
Пусть в столбце
А
введены числовые значения. В столбце
B
нужно ввести формулы для суммирования значений из 2-х ячеек столбца
А
: значения из той же строки и значения из строки выше.
Т.е. в
B2
должна быть формула: =СУММ(A1:A2) , в
B3
: =СУММ(A2:A3) и т.д.
Решить задачу просто: записав в
B2
формулу =СУММ(A1:A2) , протянем ее с помощью
Маркера заполнения
в ячейку
B3
и ниже.
Альтернативное решение
Другим вариантом решения этой задачи является использование
Именованной формулы
. Для этого:
-
выделите ячейку
B2
(это принципиально при использовании относительных ссылок вИменах
). Теперь
B2
– активная ячейка; -
на вкладке
Формулы
в группе
Определенные имена
выберите команду
Присвоить имя
; -
в поле
Имя
введите, например
Сумма2ячеек
; -
убедитесь, что в поле
Диапазон
введена формула =СУММ(A1:A2) - Нажмите ОК.
Теперь в
B2
введем формулу =
Сумма2ячеек
. Результат будет тот, который мы ожидали: будет выведена сумма 2-х ячеек из столбца слева (см. файл примера , лист
пример1
). Если формулу ввести в ячейку
B5
, то она будет суммировать ячейки
A4:A5
, если ввести в
D10
, то – ячейки
С9:С10
.
Другими словами, будут суммироваться 2 ячейки соседнего столбца слева, находящиеся на той же строке и строкой выше.
Ссылка на диапазон суммирования будет меняться в зависимости от месторасположения формулы на листе, но «расстояние» между ячейкой с формулой и диапазоном суммирования всегда будет одинаковым (один столбец влево).
Относительная адресация при создании формул для Условного форматирования.
Пусть необходимо выделить в таблице, содержащей числа от 1 до 100, значения больше 50, причем, только в четных строках (см. файл примера , лист
пример2
). Построим такую таблицу:
Создадим правило для
Условного форматирования
:
-
выделите диапазон таблицы
B
2:
F
11
, так, чтобы активной ячейкой была
B
2
(важно выделить диапазон начиная с
B
2
, а не с
F
11
. Во втором случае, активной ячейкой будет
F
11
); -
вызовите инструмент
Условное форматирование
(
Главная/ Стили/ Условное форматирование/ Создать правило/ использовать формулу для …
); - введите формулу =И(ОСТАТ($A2;2)=$I$1;B2>50) ;
-
выберите
Формат
; -
нажмите
ОК
Важно отметить, что, если бы, при создании правила, активной ячейкой была
F11
, то формулу необходимо было переписать: =И(ОСТАТ($A11;2)=$I$1;F11>50) . Поменять необходимо только ссылки незафиксированные знаком $:
B2
на
F11
и
$A2
на
$A11
.
Внимание!
При использовании относительной адресации в
Именованных формулах
,
Именованных диапазонах
,
Условном форматировании
,
Проверке данных
(примеры см. в соответствующих статьях) необходимо следить, какая ячейка является активной в момент создания формулы (активной может быть только одна ячейка на листе, не смотря на то, что выделено может быть несколько).
Смешанные ссылки
Смешанные ссылки имеют формат =$В3 или =B$3 . В первом случае при копировании формулы фиксируется ссылка на столбец
B
, а строка может изменяться в зависимости при копировании формулы.
Предположим, у нас есть столбец с ценами в диапазоне
B
3:
B
6
(см. файл примера , лист
пример3
). В столбцах
С,
D
, Е
содержатся прогнозы продаж в натуральном выражении по годам (в шт.). Задача: в столбцах
F
,
G
,
H
посчитать годовые продажи в рублях, т.е. перемножить столбцы
С,
D
, Е
на столбец
B
. Использование механизма относительной адресации позволяет нам ввести для решения задачи только одну формулу. В ячейку
F
вводим: =$В3*C3 . Потом протягиваем формулу
маркером заполнения
вниз до
F
6
,
а затем весь столбец таблицы протягиваем вправо на столбцы
G
и
H
.
Обратите внимание, что в формуле =$В3*C3 перед столбцом
B
стоит значок $. При копировании формулы =$В3*C3 в ячейки столбцов
F,
G
и
H
, этот значок $ говорит EXCEL о том, что ссылку на столбец
B
модифицировать не нужно. А вот перед столбцом
С
такого значка нет и формула в ячейке
H6
примет вид =$В6*E6 .
Вводим знак $ в адрес ячейки
Существует несколько возможностей при вводе формулы ввести знак $ в адрес ячейки или диапазона. Рассмотрим ввод на примере формулы
=СУММ($А$2:$А$5)
1. Ввести знак $ можно вручную, последовательно вводя с клавиатуры все знаки
=СУММ($А$2:$А$5)
2. С помощью клавиши
F4
(для ввода абсолютной ссылки):
-
Введите часть формулы без ввода $:
=СУММ(А2:А5
-
Затем
сразу
нажмите клавишу
F4
, знаки $ будут вставлены автоматически:
=СУММ(
$А$2:$А$5
-
Для окончания ввода формулы нажмите
ENTER.
Если после ввода
=СУММ(А2:А5
в формуле передвинуть курсор с помощью мыши в позицию левее,
а затем вернуть его в самую правую позицию (также мышкой),
то после нажатия клавиши
F4
, знаки $ будут автоматически вставлены только во вторую часть ссылки!
=СУММ(
А2:$А$5
Чтобы вставить знаки $ во всю ссылку, выделите всю ссылку
А2:$А$5
или ее часть по обе стороны двоеточия, например
2:$А
, и нажмите клавишу
F4.
Знаки $ будут автоматически вставлены во всю ссылку
$А$2:$А$5
3. С помощью клавиши
F4
(для ввода относительной ссылки).
-
Введите часть формулы без ввода $:
=СУММ(А2:А5
-
Затем
сразу
нажмите клавишу
F4
, будут автоматически вставлены знаки $:
=СУММ(
$А$2:$А$5
-
Еще раз нажмите клавишу
F4
: ссылка будет модифицирована в
=СУММ(
А$2:А$5
(фиксируются строки) -
Еще раз нажмите клавишу
F4
: ссылка будет модифицирована в
=СУММ($
А2:$А5
(фиксируется столбец) -
Еще раз нажмите клавишу
F4
: ссылка будет модифицирована в
=СУММ(
А2:А5
(относительная ссылка). Последующие нажатия изменяют ссылку заново по кругу. -
Для окончания ввода нажмите
ENTER.
Чтобы изменить только первую или втрорую часть ссылки — установите мышкой курсор в нужную часть ссылки и последовательно нажимайте клавушу
F4.
«СуперАбсолютная» адресация
В заключении расширим тему абсолютной адресации. Предположим, что в ячейке
B
2
находится число 25, с которым необходимо выполнить ряд вычислений, например, возвести в разные степени (см. файл примера , лист
пример4
). Для этого в столбце
C
напишем формулу возведения в степень (значения степени введем в столбец
D
): =$B$2^$D2 .
Мы использовали абсолютную ссылку на ячейку
B
2
. При любых изменениях положения формулы абсолютная ссылка всегда будет ссылаться на ячейку, содержащую наше значение
25
:
-
при копировании формулы из
С3
в
Н3
– формула не изменится, и мы получим правильный результат
625
; -
при вставке нового столбца между столбцами
А
и
В
– формула превратится в =$C$2^$E3 , но мы снова получим правильный результат
625
.
Все правильно, т.к. это и есть суть абсолютной адресации: ссылки автоматически модифицируются для сохранения адресации на нужные ячейки при любых модификациях строк и столбцах листа (ну, кроме удаления ячейки с формулой, конечно). Однако бывают ситуации, когда значения на лист попадают из внешних источников. Например, когда созданный пользователем макрос вставляет внешние данные в ячейку
B
2
(т.е. всегда во второй столбец листа). Теперь, при вставке столбца между столбцами
А
и
В
– формула как и раньше превратится в =$C$2^$E3 , но т.к. исходное число (25) будет вставляться макросом не в
С2
, а по прежнему в ячейку
B
2
, и мы получим неправильный результат.
Вопрос: можно ли модифицировать исходную формулу из
С2
( =$B$2^$D2 ), так чтобы данные все время брались из второго столбца листа и независимо от вставки новых столбцов?
Решение заключается в использовании функции ДВССЫЛ() , которая формирует ссылку на ячейку из текстовой строки. Если ввести в ячейку формулу: =ДВССЫЛ(«B2») , то она всегда будет указывать на ячейку с адресом
B2
вне зависимости от любых дальнейших действий пользователя, вставки или удаления столбцов и т.д.
Небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ() выводит 0, что не всегда удобно. Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО() :
=ЕСЛИ(ЕПУСТО(ДВССЫЛ(«B2″));»»;ДВССЫЛ(«B2»))
При ссылке на ячейку
В2
с другого листа =ДВССЫЛ(«пример4!B2») может возникнуть и другая сложность: при изменении названия листа
пример4
– формула перестает работать. Но это также можно обойти – см. пример из статьи
Определяем имя листа
.
Другим способом заставить формулу ссылаться на один и тот же столбец является использование функции СМЕЩ() – об этом читайте статью
Как заставить формулу все время ссылаться на один и тот же столбец
.
Правильный адрес ячейки
Адреса ячеек
Адрес ячейки составляется из обозначений столбца и номера строки, на пересечении которых находится эта ячейка, например: А1, C24, АВ2или 11,если столбцы и строки нумеруются числами.
Тип ссылок задается пользователем при настройке параметров работы с помощью команды меню СЕРВИСÞПараметрына вкладке Основныепереключателем Стиль ссылок — R1C1или А1— по умолчанию. При установленном переключателе R1C1строки и столбцы обозначаются цифрами.
Адреса ячеек можно вводить с помощью клавиатуры на любом регистре — верхнем или нижнем. Однако гораздо удобнее вводить адреса ячеек щелчком мыши по этой ячейке.
Обозначение ячейки, составленное из номера столбца и номера строки, называется относительным адресом (относительной ссылкой) или просто ссылкой или адресом.
Ссылки на диапазон (блок) ячеек состоят из адреса ячейки, находящейся в левом верхнем углу прямоугольного блока ячеек, двоеточия и адреса ячейки, находящейся в правом нижнем углу этого блока, например:
А1:С12;
А7:Е7— весь диапазон находится в одной строке;
СЗ:С9— весь диапазон находится в одном столбце.
Чтобы ввести ссылку на всю строку или столбец, нужно набрать номер строки или букву столбца дважды и разделить их двоеточием, например А:А, 2:2или А:В, 2:4.
Для обозначения адреса ячейки с указанием листаиспользуется имя листа и восклицательный знак, например: Лист2!В5, Итоги!В5.
Для обозначения адреса ячейки с указанием книгииспользуются квадратные скобки, например: [Киига1]Лист2!А1.
Относительная адресация ячеек используется в формулах чаще всего — по умолчанию.
При копировании формул в Excel действует правило относительной ориентации ячеек, суть которого состоит в том, что при копировании формулы табличный процессор автоматически смещает адрес в соответствии с относительным расположением исходной ячейки и создаваемой копии.
Если ссылка на ячейку не должна изменяться ни при каких копированиях, то вводят абсолютный адрес ячейки (абсолютную ссылку).
Абсолютная ссылкасоздается из относительной ссылки путем вставки знака доллара ($) перед заголовком столбца и/или номером строки. Например:
$А$1, $В$1 — это абсолютные адреса ячеек А1 и В1, следовательно, при их копировании не будет меняться ни номер строки, ни номер столбца;
$В$3:$С$8 — абсолютный адрес диапазона ячеек ВЗ.С8.
Иногда используют смешанный адрес, в котором постоянным является только один из компонентов, например:
$В7 — при копировании формул не будет изменяться номер столбца;
В$7 — не будет изменяться номер строки.
В Excel предусмотрен также удобный способ ссылки на ячейку путем присвоения этой ячейке произвольного собственного имени. Имена используют в формулах вместо адресов. Имена ячеек в формулах представляют собой абсолютные ссылки.
Имена присваиваются ячейкам или диапазонам ячеек для придания наглядности вычислениям в таблице и удобства работы, например собственными именами можно обозначать постоянные величины, коэффициенты, константы, которые используются при выполнении вычислений в электронной таблице.
Присвоить ячейке собственное имя (или удалить имя) можно с помощью команды ВСТАВКАÞИмяÞПрисвоитьили используя поле имени. В последнем случае необходимо:
• выделить ячейку (или диапазон ячеек);
• щелкнуть мышью в поле имени (в левой части строки формул), после чего там появится текстовый курсор;
• ввести имя и нажать Enter.
Для быстрого присвоения ячейке собственного имени можно использовать также комбинацию клавиш Ctrl+F3.
Команда ВСТАВКАÞИмя1ÞСоздатьиспользуется для создания имени из текста в выделенных ячейках. Для быстрого создания имени из текста в ячейках можно также использовать комбинацию клавиш Ctrl+Shift+F3.
Просмотреть список уже созданных имен и их ссылок можно с помощью команды меню ВСТАВКАÞИмяÞПрисвоитьили с помощью раскрывающегося списка поля имени.
Для перехода к ячейкам, имеющим собственные имена, или указания их адреса в формулах раскрывают список поля имени и выбирают необходимое имя. Для быстрого перехода к ячейкам, которым присвоены имена, можно также нажать клавишу F5 и в появившемся диалоговом окне Перейтивыбрать имя нужной ячейки.
Для быстрой вставки имени в формулу используется клавиша F3, после нажатия которой появляется диалоговое окно Вставить имя.
При назначении имени ячейке или диапазону следует соблюдать определенные правила:
· Имя должно начинаться с буквы русского или латинского алфавита, символа подчеркивания (_) или обратной косой черты () -слэша. В имени могут содержаться точки и вопросительные знаки. Цифры также могут присутствовать в имени, только не в его начале. Имя не должно быть похоже на адрес ячейки.
· В имени нельзя использовать пробелы. Вместо них нужно ставить символ подчеркивания (_).
· Длина имени ячейки не должна превышать 255 символов.
Примеры собственных имен ячеек: Доход_за_1999, Итоги_года, Постоянная_Больцмана и т. п.
Собственные (пользовательские) имена могут иметь и листы книги Excel.
Для имени листа существуют следующие ограничения:
1) длина имени листа не должна содержать больше 31 символа;
2) имя листа не может быть заключено в квадратные скобки [ ];
3) в имя не могут входить следующие символы:
· обратная косая черта ();
Чтобы переименовать рабочий лист, например Лист1,можно воспользоваться контекстным меню или сделать двойной щелчок по ярлычку листа, в появившемся окне удалить старое имя (Лист1)и ввести новое имя, например Таблица.Для завершения ввода следует щелкнуть по кнопке ОК или нажать клавишу Enter.
Не нашли то, что искали? Воспользуйтесь поиском:
Лучшие изречения: Как то на паре, один преподаватель сказал, когда лекция заканчивалась — это был конец пары: «Что-то тут концом пахнет». 8838 — 
| 8363 — или читать все.
Тест по теме табличный процессор MS Excel
тест по информатике и икт (9 класс) по теме
Тест по теме табличный процессор MS Excel
Скачать:
| Вложение | Размер |
|---|---|
| test_ms_excel.doc | 500.5 КБ |
Предварительный просмотр:
Тест по теме табличный процессор MS Excel
1. Укажите правильный адрес ячейки:
2. В электронных таблицах выделена группа ячеек А1:В3. Сколько ячеек входит в этот диапазон?
3.Результатом вычислений в ячейке С1 будет:
4.В ЭТ нельзя удалить:
Г) содержимое ячейки
5.Какие типы данных можно ввести в ячейки электронной таблицы
- Числа и формулы.
- Формулы и текст.
- Числа, текст и формулы.
- Числа и текст
6.Укажите неправильную формулу:
7.При перемещении или копировании в ЭТ абсолютные ссылки:
А) не изменяются;
Б) преобразуются вне зависимости от нового положения формулы;
В) преобразуются в зависимости от нового положения формулы;
Г) преобразуются в зависимости от длины формулы.
8. Диапазон – это:
А) все ячейки одной строки;
Б) совокупность клеток, образующих в таблице область прямоугольной формы;
В) все ячейки одного столбца;
Г) множество допустимых значений.
9. Электронная таблица – это:
А) прикладная программа для обработки кодовых таблиц;
Б) устройство персонального компьютера, управляющее его ресурсами;
В) прикладная программа, предназначенная для обработки структурированных в виде таблицы данных;
Г) системная программа, управляющая ресурсами персонального компьютера при обработке таблиц.
10. Какая формула будет получена при копировании в ячейку D3, формулы из ячейки D2:
11. Дан фрагмент электронной таблицы.
Чему будут равны значения клеток В2 и В3,
если в них было скопировано содержимое
Д) будет выдано сообщение об ошибке
12. Дан фрагмент электронной таблицы:
После выполнения вычислений была построена диаграмма по значениям диапазона ячеек A2:D2. Укажите получившуюся диаграмму.
13. Какой элемент является минимальным объектом электронной таблицы?
14. Для наглядного представления числовых данных можно использовать
А) набор чисел, выделенных в таблице.
Б) графический объект WordArt.
Д) графические файлы.
15. В каком из арифметических выражений, представленном в виде, удобном для обработки компьютера, допущена ошибка?
Г) x * x + 7 * x — 4
16. Среди указанных адресов ячеек выберите абсолютный:
17. Строки электронной таблицы:
- Именуются пользователем произвольным образом;
- Обозначаются буквами русского алфавита;
- Обозначаются буквами латинского алфавита;
- Нумеруются.
18. В ячейку D1 введено число 1,5. Как это объяснить?
- Такого не может быть
- Это ошибка в работе программы.
- В ячейке установлен числовой формат, предусматривающий один знак после запятой.
- В ячейке установлен числовой формат, предусматривающий только 3 символа.
19. Формула начинается с записи символа:
20. Для переименования рабочего листа можно (укажите все правильные варианты):
- Щелкнуть на имени листа правой кнопкой мыши и из контекстного меню выбрать пункт Переименовать.
- Щелкнуть на нем левой кнопкой мыши и из контекстного меню выбрать пункт Переименовать.
- Дважды щелкнуть на имени листа левой кнопкой мыши и ввести новое имя.
- Изменить имя листа в строке формул.
Тест по теме табличный процессор MS Excel
1.Укажите правильный адрес ячейки:
2. В электронных таблицах выделена группа ячеек А1:С2. Сколько ячеек входит в этот диапазон?
3.Результатом вычислений в ячейке С1 будет:
4.В ЭТ формула не может включать в себя:
Г) знаки арифметических операций
5.В ЭТ имя ячейки образуется:
А) из имени столбца
Б) из имени строки
В) из имени столбца и строки
6.Укажите неправильную формулу:
7.При перемещении или копировании в ЭТ относительные ссылки:
А) не изменяются;
Б) преобразуются вне зависимости от нового положения формулы;
В) преобразуются в зависимости от нового положения формулы;
Г) преобразуются в зависимости от длины формулы.
8. Активная ячейка – это ячейка:
А) для записи команд;
Б) содержащая формулу, включающую в себя имя ячейки, в которой выполняется ввод данных;
В) формула в которой содержит ссылки на содержимое зависимой ячейки;
Г) в которой выполняется ввод данных.
9. Электронная таблица предназначена для:
А) обработки преимущественно числовых данных, структурированных с помощью таблиц;
Б) упорядоченного хранения и обработки значительных массивов данных;
В) визуализации структурных связей между данными, представленными в таблицах;
Г) редактирования графических представлений больших объемов информации.
10. Какая формула будет получена при копировании в ячейку D3, формулы из ячейки D2:
11. Дан фрагмент электронной таблицы.
Чему будет равно значение клетки С3, если в неё было скопировано содержимое клетки С1?
12. Какое значение будет присвоено ячейке Е1 после выполнения расчётов по заданным формулам?
В) 5,3333
13. В каком из арифметических выражений, представленном в виде, удобном для обработки компьютера, допущена ошибка?
14. Столбцы электронной таблицы:
- Обозначаются буквами латинского алфавита;
- Нумеруются;
- Обозначаются буквами русского алфавита;
- Именуются пользователем произвольным образом.
15. Выберите верную запись формулы для электронной таблицы:
16. В каком адресе не может меняться номер столбца:
17. Какие типы данных можно ввести в ячейки электронной таблицы
- Числа и формулы.
- Формулы и текст.
- Числа, текст и формулы.
- Числа и текст.
18. Выделен некоторый диапазон ячеек и нажата клавиша Delete. Что произойдет?
- Выделенные ячейки будут удалены с рабочего листа, а ячейки под ними перемещены наверх
- Будет удалено только содержимое этих ячеек.
- Будет удалено только оформление этих ячеек.
- Будет удалено только содержимое и оформление этих ячеек.
19. По данным электронной таблицы построена диаграмма. Возникла необходимость внести в нее изменения. Какие параметры диаграммы можно изменить?
- Никакие параметры уже построенной диаграммы изменить нельзя.
- Можно поменять все параметры, кроме вида выбранной диаграммы.
- Можно поменять все параметры.
- Можно поменять только размер диаграммы.
Тест по информатике «Электронная таблица»
Как организовать дистанционное обучение во время карантина?
Помогает проект «Инфоурок»
Тест: «Электронная таблица Microsoft Excel»
Инструкция: выберите один правильный ответ
1 . Электронная таблица это?
прикладная программа для обработки кодовых таблиц
устройство персонального компьютера, управляющее его ресурсами
+ прикладная программа, предназначенная для обработки структурированных в виде таблицы данных
системная программа, управляющая ресурсами персонального компьютера при обработке таблиц
2. Электронная таблица представляет собой?
+ совокупность нумерованных строк и поименованных буквами латинского алфавита столбцов
совокупность поименованных буквами латинского алфавита строк и нумерованных столбцов
совокупность пронумерованных строк и столбцов
совокупность строк и столбцов, именуемых пользователем произвольным образом
3. С помощью каких команд можно запустить программу Excel?
+ Пуск . Программы . Microsoft Office. Microsoft Office Excel
Пуск. Программы. Каталог Windows
Пуск. Программы. Стандартные. Блокнот
Программы. Microsoft Office. Microsoft Excel
4. Как называется объект обработки в программе Excel?
5. Что нужно сделать для создания новой книги в программе Excel?
+ выполнить команду Создать в меню Файл
нажать кнопку Вернуть на панели инструментов
выполнить команду Открыть меню Файл
нажать кнопку Открыть на панели инструментов
6. Какое расширение имеют файлы программы Excel?
7. Основным элементом электронной таблицы является?
8. Укажите правильный адрес ячейки:
9. Как выделить несмежные ячейки в программе Excel?
+ щелкнуть на первой ячейке, нажать и, удерживая ее, щелкнуть на другие ячейки
щелкнуть на первой ячейке, нажать и, удерживая ее, щелкнуть на другие ячейки
щелкнуть на первой ячейке, нажать и, удерживая ее, щелкнуть на другие ячейки
выполнить действия: Пpавка. Пеpейти. Выделить.
10. Как выделить диапазон ячеек в таблице программы Excel?
щелкнуть на первой ячейке, нажать и, удерживая ее, щелкнуть на последней ячейке
щелкнуть на первой ячейке,нажать и, удерживая ее, щелкнуть на последней ячейке
+ щелкнуть на первой ячейке, нажать и, удерживая ее, щелкнуть на последней ячейке
выполнить действия Пpавка. Пеpейти. Выделить
11. Укажите правильные действия при завершении ввода данных в ячейку в программе Excel.
щелкнуть на кнопке панели формул
12. Диапазон это?
все ячейки одной строки
+ совокупность клеток, образующих в таблице область прямоугольной формы
все ячейки одного столбца
множество допустимых значений
13. В электронных таблицах выделена группа ячеек А1:В4. Сколько ячеек входит в этот диапазон?
14. В электронной таблице нельзя удалить?
15. Укажите неправильную формулу:
16. В электронной таблице формула не может включать в себя?
знаки арифметических операций
17. При перемещении или копировании в электронной таблице абсолютные ссылки:
преобразуются вне зависимости от нового положения формулы
преобразуются в зависимости от нового положения формулы
преобразуются в зависимости от длины формулы
18. При перемещении или копировании в электронной таблице относительные ссылки:
преобразуются вне зависимости от нового положения формулы
+ преобразуются в зависимости от нового положения формулы
преобразуются в зависимости от длины формулы
19. Функциональная клавиша, с помощью которой можно редактировать и вводить данные в активную ячейку Microsoft Excel?
20. Как удалить содержимое ячеек в программе Excel?
+ выделить ячейку и нажать
выделить ячейку и нажать +
выделить ячейку, нажать левую кнопку мыши, в появившемся диалоговом окне выбрать команду Очистить содержимое
выделить ячейку и выполнить команды: Вид. Обычный.
21. С помощью каких команд можно добавить ячейки в таблицу в программе Excel?
Вставка. Добавить. Ячейки.
Главная. Объект. Ячейки.
Вставка. Ячейки. Добавить.
+ Главная. Ячейки. Вставить.
22. Прежде чем ввести информацию в ячейку в программе Excel,
создать новую ячейку
+ сделать ячейку активной
вызвать контекстное меню щелчком правой кнопкой мыши
нажать клавишу Delete.
23. Укажите правильные обозначения столбца в программе Excel.
24. Укажите правильное обозначение строки в программе Excel.
25. Укажите правильный адрес ячейки в программе Excel.
26. Укажите правильные обозначения диапазонов таблицы в программе Excel .
27. Какие данные можно вводить в ячейку в программе Excel?
28. Какое форматирование применимо к ячейкам в Excel?
обрамление и заливка
выравнивание текста и формат шрифта
тип данных, ширина и высота
+ все варианты верны
29. Изменить формат данных в отдельной ячейке можно с помощью вкладки:
30. Отформатировать ячейки из определенного диапазона можно с помощью команд:
31. Изменить ширину и высоту ячеек можно с помощью команд?
+ Главная. Ячейки; Формат. Высота строки. Ширина столбца.
Сервис. Ячейки; Формат. Высота строки. Ширина столбца.
Правка. Строка; Правка. Столбец
Формат. Высота строки; Формат. Ширина столбца.
32. Если дважды щелкнуть на заполненной ячейке таблицы в программе Excel, активизируется режим?
+ редактирования содержимого ячейки
ввода данных, если ими является Текст
копирования содержимого ячейки
ввода данных, если ими является Формульное выражение
33. Способна ли программа Excel автоматически продолжать последовательность однородных данных?
только последовательность натуральных чисел
только последовательность дат
34. С помощью функции Автозаполнение в таблице программы Excel можно?
+ создавать ряды чисел, дней, дат, кварталов и т.д.
автоматически выполнять простейшие вычисления
автоматически выполнять сложные вычисления
вносить изменения в содержимое ячейки
35. Какой вариант расчетов не возможен с помощью автосуммы?
Бесплатный
Дистанционный конкурс «Стоп коронавирус»
- Кумпан Юлия ИгоревнаНаписать 6991 19.12.2015
Номер материала: ДВ-273011
Добавляйте авторские материалы и получите призы от Инфоурок
Еженедельный призовой фонд 100 000 Р
- 19.12.2015 418
- 19.12.2015 813
- 19.12.2015 848
- 19.12.2015 536
- 19.12.2015 2061
- 19.12.2015 2327
- 19.12.2015 2888
Не нашли то что искали?
Вам будут интересны эти курсы:
Все материалы, размещенные на сайте, созданы авторами сайта либо размещены пользователями сайта и представлены на сайте исключительно для ознакомления. Авторские права на материалы принадлежат их законным авторам. Частичное или полное копирование материалов сайта без письменного разрешения администрации сайта запрещено! Мнение редакции может не совпадать с точкой зрения авторов.
Ответственность за разрешение любых спорных моментов, касающихся самих материалов и их содержания, берут на себя пользователи, разместившие материал на сайте. Однако редакция сайта готова оказать всяческую поддержку в решении любых вопросов связанных с работой и содержанием сайта. Если Вы заметили, что на данном сайте незаконно используются материалы, сообщите об этом администрации сайта через форму обратной связи.
Примеры функции АДРЕС для получения адреса ячейки листа Excel
Функция АДРЕС возвращает адрес определенной ячейки (текстовое значение), на которую указывают номера столбца и строки. К примеру, в результате выполнения функции =АДРЕС(5;7) будет выведено значение $G$5.
Примечание: наличие символов «$» в адресе ячейки $G$5 свидетельствует о том, что ссылка на данную ячейку является абсолютной, то есть не меняется при копировании данных.
Функция АДРЕС в Excel: описание особенностей синтаксиса
Функция АДРЕС имеет следующую синтаксическую запись:
Первые два аргумента данной функции являются обязательными для заполнения.
- Номер_строки – числовое значение, соответствующее номеру строки, в которой находится требуемая ячейка;
- Номер_столбца – числовое значение, которое соответствует номеру столбца, в котором расположена искомая ячейка;
- [тип_ссылки] – число из диапазона от 1 до 4, соответствующее одному из типов возвращаемой ссылки на ячейку:
- абсолютная на всю ячейку, например — $A$4
- абсолютная только на строку, например — A$4;
- абсолютная только на столбец, например — $A4;
- относительная на всю ячейку, например A4.
- [a1] – логическое значение, определяющее один из двух типов ссылок: A1 либо R1C1;
- [имя_листа] – текстовое значение, которое определяет имя листа в документе Excel. Используется для создания внешних ссылок.
- Ссылки типа R1C1 используются для цифрового обозначения столбцов и строк. Для возвращения ссылок такого типа в качестве параметра a1 должно быть явно указано логическое значение ЛОЖЬ или соответствующее числовое значение 0.
- Стиль ссылок в Excel может быть изменен путем установки/снятия флажка пункта меню «Стиль ссылок R1C1», который находится в «Файл – Параметры – Формулы – Работа с Формулами».
- Если требуется ссылка на ячейку, которая находится в другом листе данного документа Excel, полезно использовать параметр [имя_листа], который принимает текстовое значение, соответствующее названию требуемого листа, например «Лист7».
Примеры использования функции АДРЕС в Excel
Пример 1. В таблице Excel содержится ячейка, отображающая динамически изменяемые данные в зависимости от определенных условий. Для работы с актуальными данными в таблице, которая находится на другом листе документа требуется получить ссылку на данную ячейку.
На листе «Курсы» создана таблица с актуальными курсами валют:
На отдельном листе «Цены» создана таблица с товарами, отображающая стоимость в долларах США (USD):
В ячейку D3 поместим ссылку на ячейку таблицы, находящейся на листе «Курсы», в которой содержится информация о курсе валюты USD. Для этого введем следующую формулу: =АДРЕС(3;2;1;1;»Курсы»).
- 3 – номер строки, в которой содержится искомая ячейка;
- 2 – номер столбца с искомой ячейкой;
- 1 – тип ссылки – абсолютная;
- 1 – выбор стиля ссылок с буквенно-цифровой записью;
- «Курсы» — название листа, на котором находится таблица с искомой ячейкой.
Для расчета стоимости в рублях используем формулу: =B3*ДВССЫЛ(D3).
Функция ДВССЫЛ необходима для получения числового значения, хранимого в ячейке, на которую указывает ссылка. В результате вычислений для остальных товаров получим следующую таблицу:
Как получить адрес ссылки на ячейку Excel?
Пример 2. В таблице содержатся данные о цене товаров, отсортированные в порядке возрастания стоимости. Необходимо получить ссылки на ячейки с минимальной и максимальной стоимостью товаров соответственно.
Исходная таблица имеет следующий вид:
Для получения ссылки на ячейку с минимальной стоимостью товара используем формулу:
Функция АДРЕС принимает следующие параметры:
- число, соответствующее номеру строки с минимальным значением цены (функция МИН выполняет поиск минимального значения и возвращает его, функция ПОИСКПОЗ находит позицию ячейки, содержащей минимальное значение цены. К полученному значению добавлено 2, поскольку ПОИСКПОЗ осуществляет поиск относительно диапазона выбранных ячеек.
- 2 – номер столбца, в котором находится искомая ячейка.
Аналогичным способом получаем ссылку на ячейку с максимальной ценой товара. В результате получим:
Адрес по номерам строк и столбцов листа Excel в стиле R1C1
Пример 3. В таблице содержится ячейка, данные из которой используются в другом программном продукте. Для обеспечения совместимости необходимо предоставить ссылку на нее в виде R1C1.
Исходная таблица имеет следующий вид:
Исходная таблица.» src=»https://exceltable.com/funkcii-excel/images/funkcii-excel78-9.png» >
Для получения ссылки на ячейку B6 используем следующую формулу: =АДРЕС(6;2;1;0).
- 6 – номер строки искомой ячейки;
- 2 – номер столбца, в котором содержится ячейка;
- 1 – тип ссылки (абсолютная);
- 0 – указание на стиль R1C1.
В результате получим ссылку:
Примечание: при использовании стиля R1C1 запись абсолютной ссылки не содержит знака «$». Чтобы отличать абсолютные и относительные ссылки используются квадратные скобки «[]». Например, если в данном примере в качестве параметра тип_ссылки указать число 4, ссылка на ячейку примет следующий вид:
Так выглядит абсолютный тип ссылок по строкам и столбцам при использовании стиля R1C1.
При использовании формул поиска в Excel (таких как VLOOKUP, XLOOKUP или INDEX / MATCH) цель состоит в том, чтобы найти совпадающее значение и получить это значение (или соответствующее значение в той же строке / столбце) в качестве результата.
Но в некоторых случаях вместо получения значения может потребоваться, чтобы формула возвращала адрес ячейки значения.
Это может быть особенно полезно, если у вас большой набор данных и вы хотите узнать точное положение результата формулы поиска.
В Excel есть несколько функций, которые предназначены именно для этого.
В этом уроке я покажу вам, как вы можете найти и вернуть адрес ячейки вместо значения в Excel по простым формулам.
Поиск и возврат адреса ячейки с помощью функции АДРЕС
Функция АДРЕС в Excel предназначена именно для этого.
Он берет строку и номер столбца и дает вам адрес ячейки этой конкретной ячейки.
Ниже приведен синтаксис функции АДРЕС:
= АДРЕС (row_num, column_num, [abs_num], [a1], [sheet_text])
куда:
- row_num: номер строки ячейки, для которой вы хотите получить адрес ячейки
- column_num: номер столбца ячейки, для которой вы хотите адрес
- [abs_num]: необязательный аргумент, в котором вы можете указать, хотите ли вы, чтобы ссылка на ячейку была абсолютной, относительной или смешанной.
- [a1]: необязательный аргумент, в котором вы можете указать, хотите ли вы использовать ссылку в стиле R1C1 или в стиле A1.
- [sheet_text]: необязательный аргумент, в котором вы можете указать, хотите ли вы добавить имя листа вместе с адресом ячейки или нет.
Теперь давайте возьмем пример и посмотрим, как это работает.
Предположим, что существует набор данных, показанный ниже, где у меня есть идентификатор сотрудника, его имя и его отдел, и я хочу быстро узнать адрес ячейки, в которой находится отдел для идентификатора сотрудника KR256.
Ниже приведена формула, которая сделает это:
= АДРЕС (ПОИСКПОЗ ("KR256"; A1: A20,0); 3)
В приведенной выше формуле я использовал функцию ПОИСКПОЗ, чтобы узнать номер строки, содержащей данный идентификатор сотрудника.
И поскольку отдел находится в столбце C, я использовал 3 в качестве второго аргумента.
Эта формула отлично работает, но у нее есть один недостаток: она не будет работать, если вы добавите строку над набором данных или столбец слева от набора данных.
Это связано с тем, что, когда я указываю второй аргумент (номер столбца) как 3, он жестко запрограммирован и не изменится.
Если я добавлю какой-либо столбец слева от набора данных, формула будет считать 3 столбца с начала рабочего листа, а не с начала набора данных.
Итак, если у вас есть фиксированный набор данных и вам нужна простая формула, это сработает.
Но если вам нужно, чтобы это было более надежным, используйте тот, который описан в следующем разделе.
Поиск и возврат адреса ячейки с помощью функции CELL
Хотя функция АДРЕС была создана специально, чтобы дать вам ссылку на ячейку с указанным номером строки и столбца, есть еще одна функция, которая также делает это.
Это называется функцией ЯЧЕЙКА (и она может дать вам гораздо больше информации о ячейке, чем функция АДРЕС).
Ниже приведен синтаксис функции ЯЧЕЙКА:
= ЯЧЕЙКА (тип_информации; [ссылка])
куда:
- info_type: информация о нужной ячейке. Это может быть адрес, номер столбца, имя файла и т. Д.
- [Справка]: Необязательный аргумент, в котором вы можете указать ссылку на ячейку, для которой вам нужна информация о ячейке.
Теперь давайте посмотрим на пример, в котором вы можете использовать эту функцию для поиска и получения ссылки на ячейку.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите быстро узнать адрес ячейки, в которой находится отдел для идентификатора сотрудника KR256.
Ниже приведена формула, которая сделает это:
= ЯЧЕЙКА ("адрес", ИНДЕКС ($ A $ 1: $ D $ 20, ПОИСКПОЗ ("KR256", $ A $ 1: $ A $ 20,0), 3))
Приведенная выше формула довольно проста.
Я использовал формулу ИНДЕКС в качестве второго аргумента, чтобы получить отдел для идентификатора сотрудника KR256.
А затем просто обернул его в функцию CELL и попросил вернуть адрес ячейки с этим значением, которое я получаю из формулы ИНДЕКС.
Теперь вот секрет того, почему это работает — формула ИНДЕКС возвращает значение подстановки, когда вы даете ей все необходимые аргументы. Но в то же время он также вернет ссылку на эту результирующую ячейку.
В нашем примере формула ИНДЕКС возвращает «Продажи» в качестве результирующего значения, но в то же время вы также можете использовать ее, чтобы дать вам ссылку на ячейку этого значения вместо самого значения.
Обычно, когда вы вводите формулу ИНДЕКС в ячейку, она возвращает значение, потому что это именно то, что от нее ожидается. Но в сценариях, где требуется ссылка на ячейку, формула ИНДЕКС даст вам ссылку на ячейку.
В этом примере это именно то, что он делает.
И самое лучшее в использовании этой формулы — это то, что она не привязана к первой ячейке на листе. Это означает, что вы можете выбрать любой набор данных (который может находиться в любом месте рабочего листа), использовать формулу ИНДЕКС для регулярного поиска, и он все равно даст вам правильный адрес.
А если вы вставите дополнительную строку или столбец, формула изменится соответствующим образом, чтобы дать вам правильный адрес ячейки.
Итак, это две простые формулы, которые вы можете использовать для поиска и найти и вернуть адрес ячейки вместо значения в Excel.
Надеюсь, вы нашли этот урок полезным.
Skip to content
В руководстве объясняется, что такое адрес ячейки, как правильно записывать абсолютные и относительные ссылки в Excel, как ссылаться на ячейку на другом листе и многое другое.
Ссылка на ячейки Excel, как бы просто она ни казалась, сбивает с толку многих пользователей. Как определяется адрес ячейки? Что такое абсолютная и относительная ссылка и когда следует использовать каждую из них? Как делать перекрестные ссылки между разными листами и файлами? В этом руководстве вы найдете ответы на эти и многие другие вопросы.
- Что такое ссылка на ячейку?
- Ссылка на диапазон
- Как создать ссылку?
- Как изменить ссылку.
- Ссылка на другой лист или другую книгу
- Относительная ссылка
- Абсолютная ссылка
- Что такое смешанная ссылка?
- Как поменять ссылку с относительной на абсолютную?
- Имя — это абсолютная ссылка
- Относительная и абсолютная ссылка на столбец и строку
- Ссылка на столбец, исключая несколько первых строк
- Смешанная ссылка на столбец
Что такое ссылка на ячейку?
Рабочий лист в Excel состоит из ячеек. На каждую из них можно ссылаться, указав значение строки и значение столбца. Зачем это нужно? Чтобы получить значение, записанное в ней, и затем использовать его в вычислениях.
Ссылка на ячейку представляет собой комбинацию из буквы столбца и номера строки, который идентифицирует её на листе. Проще говоря, это ее адрес. Он сообщает программе, где искать значение, которое вы хотите использовать в расчётах.
Например, A1 относится к адресу на пересечении столбца A и строки 1; B2 относится ко второй ячейке в столбце B и так далее.
При использовании в формуле ссылки помогают Excel находить значения, которые она должна использовать.
Например, если вы введете простейшее выражение =A1 в клетку C1, Эксель продублирует данные из A1 в C1:
Чтобы сложить числа в ячейках A1 и A2, используйте: =A1 + A2
Что такое ссылка на диапазон?
В Microsoft Excel диапазон – это блок из двух или более ячеек. Ссылка на диапазонпредставлена адресами верхней левой и нижней правой его ячеек, разделенных двоеточием.
Например, диапазон A1:C2 включает 6 ячеек от A1 до C2.
Как создать ссылку?
Чтобы записать ссылку на ячейку на том же листе, вам нужно сделать следующее:
- Выберите, где вы хотите ввести формулу.
- Введите знак равенства (=).
- Выполните одно из следующих действий:
- Запишите координаты прямо в ячейку или в строку формул, или же
- Кликните ячейку, к которой хотите обратиться.
- Введите оставшуюся часть формулы и нажмите
Enterдля завершения.
Например, чтобы сложить значения в A1 и A2, введите знак равенства, щелкните A1, введите знак плюса, щелкните A2 и нажмите Enter:
Чтобы создать ссылку на диапазон, выберите область на рабочем листе.
Например, чтобы сложить значения в A1, A2 и A3, введите знак равенства, затем имя функции СУММ и открывающую скобку, выберите ячейки от A1 до A3, введите закрывающую скобку и нажмите Enter:
Чтобы обратиться ко всей строке или целому столбцу, щелкните номер строки или букву столбца соответственно.
Например, чтобы сложить все ячейки в строке 1, начните вводить функцию СУММ, а затем кликните заголовок первой строки, чтобы включить ссылку на строку в ваш расчёт:
Как изменить ссылку?
Чтобы изменить адрес ячейки в существующей формуле Excel, выполните следующие действия:
- Выберите клетку, содержащую формулу, и нажмите
F2, чтобы войти в режим редактирования, или дважды щелкните саму ячейку. Это выделит каждую ячейку или диапазон, на который ссылается формула, другим цветом. - Чтобы изменить адрес, выполните одно из следующих действий:
- Выберите адрес в формуле и вручную введите новый вместо него.
- Выбрав ссылку, при помощи мышки укажите вместо нее другой адрес или диапазон на листе.
- Чтобы включить больше или меньше ячеек в ссылку на диапазон, перетащите его правый нижний угол:
- Нажмите Enter.
Как сделать перекрестную ссылку?
Чтобы ссылаться на ячейки на другом листе или в другом файле Excel, вы должны указать не только целевую ячейку, но также лист и книгу, где они расположены. Это можно сделать с помощью так называемой внешней ссылки.
Чтобы сослаться на данные, находящиеся на другом листе, введите имя этого целевого листа с восклицательным знаком (!) перед адресом ячейки или диапазона.
Например, вот как вы можете создать ссылку на адрес A1 на листе Лист2 в той же книге Excel:
=Лист2!A1
Если имя рабочего листа содержит пробелы или неалфавитные символы, вы должны заключить его в одинарные кавычки, например:
=’Новый лист’!A1
Чтобы предотвратить возможные опечатки и ошибки, вы можете заставить Excel автоматически создавать для вас внешнюю ссылку. Вот как:
- Начните ввод в ячейку. Запишите знак «=».
- Щелкните вкладку листа, на которую хотите сослаться, затем выберите ячейку или диапазон ячеек на этом листе.
- Завершите ввод и нажмите Enter.
Как сослаться на другую книгу?
Чтобы сослаться на ячейку или диапазон ячеек в другом файле Excel, необходимо заключить имя книги в квадратные скобки, за которым следует имя листа, восклицательный знак и адрес ячейки или диапазона.
Например:
=[Книга1.xlsx]Лист1!A1
Если имя файла или листа содержит небуквенные символы, не забудьте заключить путь в одинарные кавычки, например
='[Новый файл.xlsx]Лист1′!A1
Как и в случае ссылки на другой лист, вам не обязательно вводить всё это вручную. Более быстрый способ – начать писать формулу, затем переключиться на другую книгу и выбрать в ней ячейку или диапазон. Нажать Enter.
Итак, мы научились создавать простейшие ссылки. Теперь рассмотрим, какими они бывают.
В Экселе есть три типа ссылок на ячейки: относительные, абсолютные и смешанные. В ваших расчётах вы можете использовать любой из них. Но если вы собираетесь скопировать записанное выражение на другое место в вашем рабочем листе, то здесь нужно быть внимательным. Важно использовать правильный тип адреса, поскольку относительные и абсолютные ссылки ведут себя по-разному при переносе и копировании.
Относительная ссылка на ячейку.
Относительная ссылка является самой простой и включает координаты строки и столбца, например А1 или А1:D10. По умолчанию все адреса ячеек в Экселе являются относительными.
Пример:
=A2
Это простейшее выражение сообщает программе, что нужно показать значение, которое записано в первой колонке (A) и второй строке (2). Используя скриншот чуть ниже, если бы эта формула была помещена в ячейку D1, она отобразила бы число «8», поскольку это значение находится по адресу A2.
При перемещении или копировании относительные ссылки изменяются в зависимости от относительного положения строк и столбцов. Иначе говоря, насколько новое местоположение изменилось относительно первоначального.
Итак, если вы хотите повторить одно и то же вычисление для однотипных данных по вертикали или горизонтали, вам необходимо использовать относительные ссылки.
Например, чтобы сложить числа в A2 и B2, вы вводите это в C2: =A2+B2. При копировании из строки 2 в строку 3 выражение изменится на = A3+B3.
Относительные ссылки полезны и удобны тем, что, если у вас есть однотипные данные, с которыми нужно совершить одни и те же операции, вы можете создать формулу один раз, а потом просто скопировать ее для всех данных.
К примеру, так очень удобно перемножать количество и цену различных товаров в таблице, чтобы найти их стоимость.
Создайте расчет умножения цены на количество для одного товара, и скопируйте его для всех остальных. Вот тут как раз и нужно использовать относительные ссылки.
Вместо того, чтобы вводить формулу для всех ячеек одну за другой, вы можете просто скопировать ячейку D2 и вставить ее во все остальные ячейки (D3: D8). Когда вы это сделаете, вы заметите, что адрес автоматически настраивается, чтобы ссылаться на соответствующую строку. Например, формула в ячейке D3 становится B3*C3, а в D4 теперь записано: B4*C4.
Абсолютная ссылка на ячейку.
Абсолютная ссылка в Excel имеет знак доллара ($) в координатах строк или столбцов, например $A$1 или $A$1:$B$20.
Символ доллара, добавленный перед любой из координат, делает адрес абсолютным (т. е. предотвращает изменение номера строки и столбца).
Она остается неизменной при копировании расчета в другие ячейки. Это особенно полезно, когда вы хотите выполнить несколько вычислений со значением, находящимся по определённому адресу, или когда вам нужно скопировать формулу без изменения ссылок.
Это может быть тот случай, когда у вас есть фиксированное значение, которое вам нужно многократно использовать (например, ставка налога, ставка комиссии, количество месяцев, размер скидки и т. д.)
Например, чтобы умножить числа в столбце B на величину скидки из F2, вы вводите следующую формулу в строке 2, а затем копируете её вниз, перетаскивая маркер заполнения:
=B2*$F$2
Относительная ссылка (B2) будет изменяться в зависимости от относительного положения строки, в которую она копируется, в то время как абсолютная ($F$2) всегда будет зафиксирована на одном и том же адресе:
Конечно, можно в ваше выражение жёстко вбить 10% скидки, и этим решить проблему при копировании. Но если впоследствии вам понадобится изменить процент скидки, то придется искать и корректировать все формулы. И обязательно какую-то случайно пропустите. Поэтому принято подобные константы записывать отдельно и использовать абсолютные ссылки на них.
Итак, относительная ссылка на ячейку отличается от абсолютной тем, что копирование или перемещение формулы приводит к её изменению.
Абсолютные ссылки всегда указывают на конкретный адрес, независимо от того, где они находятся.
Смешанная ссылка.
Смешанные ссылки немного сложнее, чем абсолютные и относительные.
Может быть два типа смешанных ссылок:
- Строка блокируется, а столбец изменяется при копировании.
- Столбец фиксируется, а строка изменяется.
Смешанная ссылкасодержит одну относительную и одну абсолютную координату, например $A1 или A$1.
Как вы помните, абсолютная ссылка содержит 2 знака доллара ($), которые фиксируют как столбец, так и строку. В смешанной только одна координата является фиксированной (абсолютной), а другая (относительная) будет изменяться в зависимости от нового расположения:
- Абсолютный столбец и относительная строка, например $A1. Когда выражение с этим типом ссылки копируется в другие ячейки, знак $ перед буквой столбца фиксирует обращение строго на указанный столбец, чтобы оно никогда не изменялось. Относительная ссылка на строку без знака доллара будет меняться в зависимости от строки, в которую копируется формула.
- Относительный столбец и абсолютная строка, например A$1. Здесь адресация на строку будет зафиксирована, а на столбец — поменяется.
Может быть много ситуаций, когда нужно фиксировать только одну координату: либо столбец, либо строку.
Например, чтобы умножить колонку с ценами (столбец В) на 3 разных значения наценки (C2, D2 и E2), вы поместите следующую формулу в C3, а затем скопируете ее вправо и затем вниз:
=$B3*(1+C$2)
Теперь вы можете использовать силу смешанной ссылки для расчета всех этих цен с помощью всего лишь одной формулы.
В первом множителе мы зафиксировали столбец. Поэтому при копировании вправо по строке адрес $B3 не изменится: ведь строка по-прежнему третья.
А вот во втором множителе знак доллара мы поставили перед номером строки. Поэтому при копировании формулы в D3 координаты столбца изменятся и вместо C$2 мы получим D$2. В результате в D3 получим:
=$B3*(1+D$2)
А когда будем копировать вниз, всё будет наоборот: $B3 изменится на $B4, $B5 и т.д. А вот D$2 не изменится, так как «заморожена» строка. В результате в С4 получим:
=$B4*(1+C$2)
Самый приятный момент заключается в том, что формулу мы записываем только один раз, а потом просто копируем ее на всю таблицу. Экономим очень много времени.
И если ваши наценки вдруг изменятся, просто поменяйте числа в C2:E2, и проблема будет решена почти мгновенно.
Как изменить ссылку с относительной на абсолютную (или смешанную)?
Чтобы переключиться с относительной на абсолютную и наоборот, вы можете либо добавить, либо удалить знак $ вручную. А можно использовать функциональную клавишу F4:
- Дважды щелкните ячейку, содержащую формулу.
- Выберите ссылку, которую хотите изменить.
- Нажмите
F4для переключения между четырьмя ссылочными типами.
Неоднократно нажимая F4, вы будете переключать их в следующем порядке:
Если вы выбрали относительную ссылку без знака $, например A1, последовательно нажимая F4, вы будете переключаться между абсолютной ссылкой с двумя знаками доллара $A$1, абсолютной строкой A$1, абсолютным столбцом $A1, а затем вновь вернёмся к A1.
Имя как разновидность абсолютной ссылки.
Отдельную ячейку или диапазон также можно определить по имени. Для этого вы просто выбираете ячейку, вводите имя в поле Имя и нажимаете клавишу Enter.
В нашем примере установите курсор в F2, а затем присвойте этому адресу имя, как это показано на рисунке выше. При этом можно использовать только буквы, цифры и нижнее подчёркивание, которым можно заменить пробел. Знаки препинания и служебные символы не допускаются.
Его вы можете использовать в вычислениях вашей рабочей книги.
=B2*скидка
Естественно, это своего рода абсолютная ссылка, поскольку за каждым именем жёстко закрепляются координаты определенной ячейки или диапазона.
Формула же при этом становится более понятной и читаемой.
Ссылка на столбец.
Как и на отдельные ячейки, ссылка на весь столбец может быть абсолютной и относительной, например:
- Абсолютная ссылка на столбец – $A:$A
- Относительная – A:A
Когда вы используете знак доллара ($) в абсолютной ссылке на столбец, его адрес не изменится при копировании в другое расположение.
Относительная ссылка на столбец изменится, когда формула скопирована или перемещена по горизонтали, и останется неизменной при копировании ее в другие клетки в пределах одной и той же колонки (по вертикали).
А теперь давайте посмотрим это на примере.
Предположим, у вас есть некоторые числа в колонке B, и вы хотите узнать их общее и среднее значение. Проблема в том, что новые данные добавляются в таблицу каждую неделю, поэтому писать обычную формулу СУММ() или СРЗНАЧ() для фиксированного диапазона ячеек – не лучший вариант. Вместо этого вы можете ссылаться на весь столбец B:
=СУММ($D:$D)— используйте знак доллара ($), чтобы создать абсолютную ссылку на весь столбец, которая привязывает формулу к столбцу B.
=СУММ(D:D)— напишите формулу без $, чтобы сделать относительную ссылку на весь столбец, которая будет изменяться при копировании.
Совет. При написании формулы щелкните мышкой на букве заголовка (D, например), чтобы добавить ссылку сразу на весь столбец. Как и в случае ячейками, программа по умолчанию вставляет относительную ссылку (без знака $):
Ссылка на строку.
Чтобы обратиться сразу ко всей строке, вы используете тот же подход, что и со столбцами, за исключением того, что вы вводите номера строчек вместо букв столбиков:
- Абсолютная ссылка на строку – $1:$1
- Относительная – 1:1
Пример 2. Ссылка на всю строку (абсолютная и относительная)
Если данные в вашем листе расположены горизонтально, а не по вертикали, вы можете ссылаться на всю строку. Например, вот как мы можем рассчитать среднюю цену в строке 2:
=СРЗНАЧ($3:$3) – абсолютная ссылка на всю строку зафиксирована с помощью знака доллара ($).
=СРЗНАЧ(3:3) – относительная ссылка на строку изменится при копировании вниз.
В этом примере нам нужна относительная ссылка. Ведь у нас есть 6 строчек с данными, и мы хотим вычислить среднее значение для каждого товара отдельно. Записываем в B12 расчет средней цены для яблок и копируем его вниз:
Для бананов (B13) расчет уже будет такой: СРЗНАЧ(4:4). Как видите, номер строки автоматически изменился.
Ссылка на столбец, исключая первые несколько строк.
Это очень актуальная проблема, потому что довольно часто первые несколько строк на листе содержат некоторые вводные предложения, шапку даблицы или пояснительную информацию, и вы не хотите включать их в свои вычисления. К сожалению, Excel не допускает ссылок типа D3:D, которые включали бы все данные в столбце D, только начиная со строки 3. Если вы попытаетесь добавить такую конструкцию, ваша формула, скорее всего, вернет ошибку #ИМЯ?.
Вместо этого вы можете указать максимальную строку, чтобы ваша ссылка включала все возможные адреса в данном столбце. В Excel с 2019 по 2007 максимум составляет 1 048 576 строк и 16 384 столбца. Более ранние версии программы имеют максимум 65 536 строк и 256 столбцов.
Итак, чтобы найти сумму продаж в приведенной ниже таблице (колонка «Стоимость»), можно использовать выражение:
=СУММ(D3:D1048576)
Как вариант, можно вычесть из общей суммы те данные, которые хотите исключить:
=СУММ(D:D)-СУММ(D1:D2)
Но первый вариант предпочтительнее, так как СУММ(D:D) выполняется дольше и требует больше вычислительных ресурсов, чем СУММ(D3:D1048576).
Смешанная ссылка на весь столбец.
Как я упоминал ранее, вы также можете создать смешанную ссылку на весь столбец или целую строку:
- Смешанная на столбец, например $A:A
- Смешанная на строку, например $1:1
Теперь посмотрим, что произойдет, если вы скопируете формулу с такими адресами в другие ячейки. Предположим, вы вводите формулу =СУММ($B:B) в какую-то клетку, в этом примере F3. Когда вы копируете формулу вправо (в G3), она меняется на, =СУММ($B:C), потому что первая B имеет знак $ и остается неподвижной, а вторая B – обычная и поэтому меняется.
В результате Эксель сложит все числа в столбцах B и C. Ну и, двигаясь далее вправо, далее можно найти сумму уже трёх колонок.
Надеюсь, теперь вы полностью понимаете, что такое относительные и абсолютные ссылки на ячейки, и формула со знаками $ больше не является загадкой.
Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге!
Как удалить сразу несколько гиперссылок — В этой короткой статье я покажу вам, как можно быстро удалить сразу все нежелательные гиперссылки с рабочего листа Excel и предотвратить их появление в будущем. Решение работает во всех версиях Excel,… 
Как использовать функцию ГИПЕРССЫЛКА — В статье объясняются основы функции ГИПЕРССЫЛКА в Excel и приводятся несколько советов и примеров формул для ее наиболее эффективного использования. Существует множество способов создать гиперссылку в Excel. Чтобы сделать ссылку на… Гиперссылка в Excel: как сделать, изменить, удалить — В статье разъясняется, как сделать гиперссылку в Excel, используя 3 разных метода. Вы узнаете, как вставлять, изменять и удалять гиперссылки на рабочих листах, а также исправлять неработающие ссылки. Гиперссылки широко используются… 
Как использовать функцию ДВССЫЛ – примеры формул — В этой статье объясняется синтаксис функции ДВССЫЛ, основные способы ее использования и приводится ряд примеров формул, демонстрирующих использование ДВССЫЛ в Excel. В Microsoft Excel существует множество функций, некоторые из которых… 
Как сделать диаграмму Ганта — Думаю, каждый пользователь Excel знает, что такое диаграмма и как ее создать. Однако один вид графиков остается достаточно сложным для многих — это диаграмма Ганта. В этом кратком руководстве я постараюсь показать… 
Как сделать автозаполнение в Excel — В этой статье рассматривается функция автозаполнения Excel. Вы узнаете, как заполнять ряды чисел, дат и других данных, создавать и использовать настраиваемые списки в Excel. Эта статья также позволяет вам убедиться, что вы… 
Быстрое удаление пустых столбцов в Excel — В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых…