Что означает восклицательный знак в формуле Excel

Обновлено: 03.07.2024

Нажмите на лист May и введите те же данные, что и на следующем изображении:

Нажмите на лист за июнь и введите следующее:

Затем нажмите на лист за июль и введите следующее:

Теперь нам нужно создать новый рабочий лист. Поэтому щелкните значок «Новый рабочий лист» в нижней части Excel, справа от июля на изображении ниже (пользователи Excel 2013 и 2016 должны снова щелкнуть символ «плюс»):

По умолчанию новый рабочий лист будет называться Sheet4. Переименуйте его в Годовой итог, и ваша рабочая книга будет выглядеть так внизу (если ваш новый рабочий лист не в конце, нажмите и удерживайте левую кнопку мыши на имени рабочего листа. Держите ее нажатой и перетащите до конца): < /p>

Теперь мы сложим цифры в рабочих листах за май, июнь и июль и поместим ответ в рабочий лист годового итога.

Добавьте метку к рабочему листу «Итог за год»:

Затем щелкните внутри ячейки B1.

Чтобы сослаться на данные на другом листе, используйте восклицательный знак (восклицательный знак, если вы находитесь в США). Это обычно называют взрывом!

Итак, введите это в ячейку B1 вашего итогового годового листа:

Поэтому мы начинаем со знака равенства (=), а затем вводим имя листа, на который хотим сослаться (May). После восклицательного знака (удар) у нас есть ячейка, на которую мы хотим сослаться (B1). Если вы просто наберете B1 отдельно, Excel предположит, что вы имеете в виду текущий рабочий лист.

Когда вы нажмете клавишу ввода, вы должны увидеть это на листе годовой суммы:

Это та же цифра, что и в вашем рабочем листе за май. Чтобы сложить все наши ежемесячные рабочие листы, просто ссылайтесь на них таким же образом:

=Май! В1 + июнь! B1 + июль! Б1

Поэтому щелкните внутри ячейки B1 рабочего листа «Годовой итог» и замените свою формулу приведенной выше. Нажмите клавишу ввода, и вы должны увидеть ответ:

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

В следующей части вы узнаете о функции ПРОСМОТР в Excel.

сообщить об этом объявлении

поиск меню

Урок 15. Относительные и абсолютные ссылки на ячейки

Введение

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

Относительные ссылки

По умолчанию все ссылки на ячейки являются относительными. При копировании в несколько ячеек они изменяются в зависимости от относительного положения строк и столбцов. Например, если вы скопируете формулу =A1+B1 из строки 1 в строку 2, формула станет =A2+B2. Относительные ссылки особенно удобны, когда вам нужно повторить одни и те же вычисления в нескольких строках или столбцах.

Чтобы создать и скопировать формулу с использованием относительных ссылок:

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

Выбор ячейки D2

Ввод формулы

Поиск маркера заполнения

Перетаскивание маркера заполнения над ячейками D3:D12

Скопированные формулы и вычисляемые значения

Вы можете дважды щелкнуть заполненные ячейки, чтобы проверить правильность их формул. Относительные ссылки на ячейки должны быть разными для каждой ячейки в зависимости от их строк.

Проверка скопированных формул на точность

Абсолютные ссылки

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

Абсолютная ссылка обозначается в формуле добавлением знака доллара ($) перед столбцом и строкой. Если он предшествует столбцу или строке (но не обоим), он называется смешанной ссылкой.

В большинстве формул вы будете использовать относительный (A2) и абсолютный ($A$2) форматы. Смешанные ссылки используются реже.

При написании формулы в Microsoft Excel вы можете нажать клавишу F4 на клавиатуре, чтобы переключаться между относительными, абсолютными и смешанными ссылками на ячейки, как показано в видео ниже. Это простой способ быстро вставить абсолютную ссылку.

Чтобы создать и скопировать формулу с использованием абсолютных ссылок:

В нашем примере мы будем использовать ставку налога с продаж в размере 7,5 % в ячейке E1 для расчета налога с продаж для всех товаров в столбце D. Нам потребуется использовать абсолютную ссылку на ячейку $E$1 в нашей формуле. Поскольку в каждой формуле используется одна и та же налоговая ставка, мы хотим, чтобы эта ссылка оставалась постоянной, когда формула копируется и заполняется другими ячейками в столбце D.

Выбор ячейки D3

Ввод формулы

Поиск маркера заполнения

Перетаскивание маркера заполнения

Скопированные формулы и вычисляемые значения

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

Проверка формул на точность

Обязательно добавляйте знак доллара ($) всякий раз, когда делаете абсолютную ссылку на несколько ячеек. В приведенном ниже примере знаки доллара были опущены. Это привело к тому, что Excel интерпретировал ее как относительную ссылку, что дало неправильный результат при копировании в другие ячейки.

Результат неправильной абсолютной ссылки

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

Excel позволяет вам ссылаться на любую ячейку на любом листе, что может быть особенно полезно, если вы хотите сослаться на определенное значение с одного листа на другой. Для этого вам просто нужно начать ссылку на ячейку с имени рабочего листа, за которым следует восклицательный знак (!). Например, если вы хотите сослаться на ячейку A1 на Листе1, ссылка на эту ячейку будет Лист1!А1.

Обратите внимание: если имя рабочего листа содержит пробел, вам нужно будет заключить его в одинарные кавычки (' ').Например, если вы хотите сослаться на ячейку A1 на листе с названием "Бюджет на июль", ее ссылкой на ячейку будет "Бюджет на июль"!A1.

Чтобы ссылаться на ячейки на листах:

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

Ячейка E14

Переход к Sheet2

Выбор ячейки B2

Ссылка на ячейку на Листе 1

Ячейка, на которую указывает ссылка

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

Исправление ошибки ссылки на ячейку

Модератор форума Регистрация 15.01.2005 Место Сан-Франциско, штат Калифорния MS-Off Ver 2000, 2003 и 2010 Сообщений 23 258

Восклицательный знак следует за именем рабочего листа в формуле.

! отделяет имя рабочего листа от ссылки на ячейку. Более общая
форма:
=[Book2]Sheet1!$A$1
Название рабочей книги находится в квадратных скобках, за ним следует имя рабочей таблицы,
символ !, затем ячейка адрес.

"Tam" написал:

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

"bpeltzer" написал:

> ! отделяет имя рабочего листа от ссылки на ячейку. Более общая форма
>:
> =[Book2]Sheet1!$A$1
> Имя рабочей книги находится в квадратных скобках, за которым следует имя рабочего листа,
> ! , затем адрес ячейки.
>
> "Tam" написал:
>
> > Я копирую формулу, в которой перед ссылкой на ячейку стоит восклицательный знак
> > но я не могу определить, что оператор вычисляет

Зарегистрированный пользователь Дата присоединения 13 09 2012 Местоположение Dayton, OH MS-Off Ver Excel 2010 Сообщения 2

В ответах на этот древний вопрос (эта тема занимает высокое место в результатах поиска Google) ооооооочень важная информация упущена:

Однако вы также можете ввести восклицательный знак перед формулой без названия книги или листа при вводе ее как именованного диапазона в диспетчере имен:
=!$A$1

(Обратите внимание, что вы не можете сделать это на листе — только при вводе формулы в диспетчере имен или в VBA.)

Что это делает?

Это означает, что формула рассчитывается относительно активного листа, а не именованного листа. Это может быть полезно.

Одним из способов его использования является то, что вам не нужно вводить одну и ту же формулу несколько раз для нескольких листов. Например, предположим, что у вас есть именованный диапазон CircleArea с формулой:
=PI()*Sheet1!A1^2

В данном случае Sheet1!A1 — это радиус окружности. Итак, давайте назовем Sheet1!A1 Radius и изменим именованную формулу CircleArea на:
=A1

Зарегистрированный пользователь Дата присоединения 13 05 2021 Местоположение Средний Запад, США MS-Off Ver Microsoft Office 365 Сообщения 1

Первоначальное сообщение от Ricyteach

В ответах на этот древний вопрос (эта ветка занимает высокое место в результатах поиска Google) оооооооооооооочень пропускают очень важную информацию:

Что это делает?

Зарегистрированный пользователь Дата присоединения 13 09 2012 Местоположение Dayton, OH MS-Off Ver Excel 2010 Сообщения 2

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

Чаще всего используются относительные ссылки на ячейки. Относительная ссылка на ячейку в формуле основана на положении ячейки формулы относительно ячейки, на которую она ссылается. Это означает, что если вы переместите ячейку с формулой или скопируете ее в другое место, ссылка изменится.

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

Если вы поместите эту формулу в ячейку B10, а затем скопируете ее из B10 в C10, Excel сделает разумное предположение, что вы хотите суммировать значения в тех же относительных позициях в столбце C, то есть в ячейках от C1 до C9. и поэтому он автоматически корректирует формулу следующим образом:

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

Например, если вы используете лист для оценки нормы пробега для деловых поездок, вы можете указать норму компенсации за милю в ячейке D2, а затем использовать абсолютную ссылку на эту ячейку везде, где вы используете формулу, основанную на норме пробега. . Чтобы указать абсолютную ссылку, используйте знаки доллара, например: $D$2.

Назовите эту ячейку

Вы можете облегчить себе жизнь, назвав ячейку или диапазон ячеек. Это особенно удобно, когда вы хотите неоднократно ссылаться на определенную ячейку в формулах. Когда вы называете ячейку, Excel автоматически делает ее абсолютной ссылкой.

Например, чтобы назвать нашу ячейку скорости пробега в Excel 2007:

Щелкните правой кнопкой мыши ячейку и выберите «Имя диапазона» во всплывающем меню.
Введите имя в поле «Имя», например «MileageRate», и нажмите «ОК». Хотя вы не можете включать пробелы в имя, вы можете использовать символы подчеркивания (Mileage_Rate), точки/точки (Mileage.Rate) или косые черты (Mileage\Rate). Excel игнорирует регистр, но может сделать ваши рабочие листы более читабельными, если вы смешаете строчные и прописные буквы.

Чтобы назвать ячейку в Excel 2003:

Нажмите на ячейку.
Нажмите «Вставка» -> «Имя» -> «Определить».
Введите имя, Пробег и нажмите "ОК".

После того как вы назвали ячейку, вы можете использовать ее имя в любой формуле, например:

Определяя имя, обратите внимание на значение в поле "Относится к". Вы увидите, что полная абсолютная ссылка состоит из имени рабочего листа, восклицательного знака и абсолютной ссылки на ячейку, например:

Это означает, что вы можете использовать именованную ссылку — в нашем случае MileageRate — на любом листе вашей книги, а не только на текущем листе.

Указание области действия имени

Это здорово, но что, если вы настроили свою книгу с отдельным листом для каждого сотрудника, и каждый из этих сотрудников имеет разную норму пробега? В этом случае вам не понадобится ссылка на MileageRate на листе Милдред, берущая значение из листа Даррена.

В Excel 2003 вы решаете эту ситуацию, указывая рабочий лист при определении имени: в поле Имя сначала введите имя текущего рабочего листа, затем восклицательный знак, а затем выбранное имя ячейки. Например:

Excel 2007 упрощает эту задачу, добавляя поле "Область" в диалоговое окно "Новое имя": при определении имени выберите текущий рабочий лист в раскрывающемся списке "Область", чтобы ограничить ссылку текущим листом, и Excel присвоит имя это правильно.

Удаление имени

Если вам когда-нибудь понадобится удалить имя ячейки/диапазона в Excel 2003:

Нажмите «Вставка» -> «Имя» -> «Определить».
В диалоговом окне "Определить имя" выберите имя, которое хотите удалить, а затем нажмите кнопку "Удалить".

На вкладке "Формулы" нажмите "Диспетчер имен".
В диалоговом окне "Диспетчер имен" выберите имя, которое хотите удалить, и нажмите кнопку "Удалить".

Смешанные ссылки

Смешанные ссылки представляют собой комбинацию относительных и абсолютных ссылок: либо столбец является относительным, а строка фиксированной (абсолютной), например D$2, либо столбец является фиксированным, а строка относительной: $D2.

Когда вам понадобится такая ссылка? В одном случае вы создаете любую таблицу, в которой значения получаются путем умножения оси x на ось y. Таблица умножения — простейший пример этого.

Самый простой способ почувствовать смешанные отсылки — попробовать их:

Поместите значения от 1 до 12 в ячейки от B2 до M2. Это ось x вашей таблицы умножения.
Поместите значения от 1 до 12 в ячейки с A3 по A14. Это ось Y вашей таблицы умножения.
В верхней левой ячейке таблицы результатов, ячейке B3, введите формулу: =B$2*$A3.
Скопируйте эту формулу вниз, а затем поперек, чтобы включить всю таблицу.

Эта формула переводится как: умножьте значение в строке 2, столбце x, на значение в столбце A, строке y. Для первой ячейки, на которую ссылается формула, строка остается неизменной (строка 2, ось X, где вы разместили значения от 1 до 12), а столбец изменяется. Для второй ссылки на ячейку столбец остается постоянным (столбец A, ось Y, где вы разместили значения от 1 до 12), а строка изменяется. Независимо от того, где вы щелкнете в таблице, вы увидите ссылку на строку 2 и столбец A в строке формул, в то время как другие значения меняются.

Изменение типов ссылок

Если вы обнаружите, что использовали неправильный тип ссылки в формуле, Excel предлагает быстрый способ изменить ссылку:

Нажмите на проблемную ячейку.
В строке формул щелкните неправильную ссылку на ячейку и нажмите F4. Каждый раз, когда вы нажимаете F4, Excel будет циклически менять ссылку: относительная -> абсолютная -> смешанная (относительная/абсолютная) -> смешанная (абсолютная/относительная).
Повторите этот процесс для каждой неправильной ссылки в формуле.

Чтобы не забыть использовать клавишу F4 для изменения ссылок, взгляните на свою клавиатуру: обратите внимание, что символ $, используемый для обозначения абсолютных ссылок, находится над цифрой 4. Используйте его как напоминание. (трюк, которому я научился у школьного учителя Киви).

Читайте также: