В MS Excel, чтобы присвоить абсолютный адрес нужной ячейке
Обновлено: 03.07.2024
Формулы Excel могут быть как простыми, так и более сложными. Сложность формулы зависит от сложности результата, который вы хотите получить от своих данных. В этой главе объясняются ваши варианты по всему спектру.
Читайте Excel 2013 по запросу или более 24 000 других книг и видео в Safari Books Online. Начните бесплатную пробную версию сегодня.
Эта глава из книги
Эта глава из книги
Эта глава из книги
- Понимание формул
- Создание и редактирование формул
- Ссылки на ячейки
- Использовать абсолютные ссылки на ячейки
- Используйте смешанные и трехмерные ссылки на ячейки
- Название ячеек и диапазонов
- Ввод имен и управление ими
- Упрощение формулы с помощью диапазонов
- Отображение вычислений в строке состояния
- Расчет итогов с помощью автосуммы
- Выполнить однократные вычисления
- Преобразование формул и значений
- Исправить ошибки в расчетах
- Правильные формулы
- Аудит рабочего листа
- Найти циклические ссылки
- Выполнение вычислений с использованием функций
- Создание функций и вложенных функций
- Расчет нескольких результатов
- Использование констант и функций в именах
Введение
После того как вы введете данные на лист, вы захотите добавить формулы для выполнения расчетов. Microsoft Excel может помочь вам получить нужные результаты. Формулы могут быть как очень простыми, так и более сложными. Сложность формулы зависит от сложности результата, который вы хотите получить от своих данных. Например, если вы просто хотите подсчитать объем продаж за этот месяц, формула добавит ваш номер продаж и предоставит результат. Однако если вы хотите показать продажи в этом месяце на сумму более 100 долларов США с постоянными клиентами, вам потребуется немного больше времени для разработки формулы.
Поскольку Excel автоматически пересчитывает формулы, ваши листы остаются точными и актуальными независимо от того, как часто вы меняете данные. Использование абсолютных ссылок на ячейки привязывает формулы к конкретной ячейке. Excel предоставляет множество встроенных функций для добавления к вычислениям на листе. Такие функции, как СРЗНАЧ или СУММ, позволяют выполнять быстрый расчет по формуле.
Еще один способ упростить понимание формул – использовать в них диапазоны имен. Диапазоны имен — группа выбранных ячеек, называемых диапазоном, — могут помочь вам понять ваши более сложные формулы. Гораздо проще прочитать формулу, в которой используются диапазоны имен, чем посмотреть на формулу и попытаться ее расшифровать. Excel предлагает инструмент для аудита вашего рабочего листа. Глядя на «поток» вашей формулы, вы значительно уменьшаете количество ошибок в расчетах. Вы можете увидеть, как строится ваша формула, по одному уровню за раз, с помощью серии стрелок, которые указывают, откуда формула извлекает данные. По мере разработки формулы вы можете вносить в нее исправления.
поиск меню
Урок 15. Относительные и абсолютные ссылки на ячейки
Введение
Существует два типа ссылок на ячейки: относительные и абсолютные. Относительные и абсолютные ссылки ведут себя по-разному при копировании и заполнении других ячеек. Относительные ссылки изменяются, когда формула копируется в другую ячейку. С другой стороны, абсолютные ссылки остаются постоянными независимо от того, куда они копируются.
Посмотрите видео ниже, чтобы узнать больше о ссылках на ячейки.
Относительные ссылки
По умолчанию все ссылки на ячейки являются относительными. При копировании в несколько ячеек они изменяются в зависимости от относительного положения строк и столбцов. Например, если вы скопируете формулу =A1+B1 из строки 1 в строку 2, формула станет =A2+B2. Относительные ссылки особенно удобны, когда вам нужно повторить одни и те же вычисления в нескольких строках или столбцах.
Чтобы создать и скопировать формулу с использованием относительных ссылок:
В следующем примере мы хотим создать формулу, которая будет умножать цену каждого товара на количество. Вместо создания новой формулы для каждой строки мы можем создать одну формулу в ячейке D4, а затем скопировать ее в другие строки. Мы будем использовать относительные ссылки, чтобы формула правильно вычисляла сумму для каждого элемента.
-
Выберите ячейку, которая будет содержать формулу. В нашем примере мы выберем ячейку D4.
Вы можете дважды щелкнуть заполненные ячейки, чтобы проверить правильность их формул. Относительные ссылки на ячейки должны быть разными для каждой ячейки в зависимости от их строк.
Абсолютные ссылки
Возможны случаи, когда вы не хотите, чтобы ссылка на ячейку менялась при заполнении ячеек. В отличие от относительных ссылок, абсолютные ссылки не изменяются при копировании или заполнении. Вы можете использовать абсолютную ссылку, чтобы сохранить строку и/или столбец постоянными.
Абсолютная ссылка обозначается в формуле добавлением знака доллара ($) перед столбцом и строкой. Если он предшествует столбцу или строке (но не обоим), он называется смешанной ссылкой.
В большинстве формул вы будете использовать относительный (A2) и абсолютный ($A$2) форматы. Смешанные ссылки используются реже.
При написании формулы в Microsoft Excel вы можете нажать клавишу F4 на клавиатуре, чтобы переключаться между относительными, абсолютными и смешанными ссылками на ячейки, как показано в видео ниже. Это простой способ быстро вставить абсолютную ссылку.
Чтобы создать и скопировать формулу с использованием абсолютных ссылок:
В приведенном ниже примере мы собираемся использовать ячейку E2 (которая содержит налоговую ставку 7,5%) для расчета налога с продаж для каждого элемента в столбце D. Чтобы убедиться, что ссылка на налоговую ставку остается постоянной — даже когда формула копируется и заполняется другими ячейками — нам нужно сделать ячейку $E$2 абсолютной ссылкой.
-
Выберите ячейку, которая будет содержать формулу. В нашем примере мы выберем ячейку D4.
Вы можете дважды щелкнуть заполненные ячейки, чтобы проверить правильность их формул. Абсолютная ссылка должна быть одинаковой для каждой ячейки, а остальные ссылки относятся к строке ячейки.
Обязательно добавляйте знак доллара ($) всякий раз, когда делаете абсолютную ссылку на несколько ячеек. В приведенном ниже примере знаки доллара были опущены. Из-за этого Excel интерпретировал ее как относительную ссылку, что приводило к неверному результату при копировании в другие ячейки.
Использование ссылок на ячейки с несколькими листами
Excel позволяет вам ссылаться на любую ячейку на любом листе, что может быть особенно полезно, если вы хотите сослаться на определенное значение с одного листа на другой. Для этого вам просто нужно начать ссылку на ячейку с имени рабочего листа, за которым следует восклицательный знак (!). Например, если вы хотите сослаться на ячейку A1 на Листе1, ссылка на эту ячейку будет Лист1!А1.
Обратите внимание, что если имя рабочего листа содержит пробел, его необходимо заключить в одинарные кавычки (' '). Например, если вы хотите сослаться на ячейку A1 на листе с названием "Бюджет на июль", ее ссылкой на ячейку будет "Июльский бюджет"!A1.
Чтобы ссылаться на ячейки на листах:
В приведенном ниже примере мы будем ссылаться на ячейку с вычисленным значением между двумя листами. Это позволит нам использовать одно и то же значение на двух разных листах, не переписывая формулу и не копируя данные.
-
Найдите ячейку, на которую вы хотите сослаться, и обратите внимание на ее рабочий лист. В нашем примере мы хотим сослаться на ячейку E14 на листе «Порядок меню».
Если вы переименуете свой рабочий лист позже, ссылка на ячейку будет автоматически обновлена, чтобы отразить новое имя рабочего листа.
При работе с электронными таблицами необходимо знать об относительных и абсолютных ссылках на ячейки.
Вот проблема: когда вы КОПИРУЕТЕ ФОРМУЛУ, содержащую ссылки на ячейки, что происходит со ссылками на ячейки?
Обычно ССЫЛКИ НА КЛЕТКИ ИЗМЕНЯЮТСЯ! Если скопировать формулу на 2 строки вправо, то ссылки на ячейки в формуле сместятся на 2 ячейки вправо. Если скопировать формулу на 3 строки вниз и на 1 строку влево, то ссылки на ячейки в формуле сместятся на 3 строки вниз и на 1 строку влево. Они называются относительными ссылками на ячейки, поскольку они изменяются относительно того места, куда вы копируете формулу.
Если вы не хотите, чтобы ссылки на ячейки менялись при копировании формулы, сделайте эти ссылки на ячейки абсолютными ссылками на ячейки. Поместите «$» перед буквой столбца, если вы хотите, чтобы она всегда оставалась неизменной. Поместите «$» перед номером строки, если вы хотите, чтобы он всегда оставался неизменным. Например, «$C$3» относится к ячейке C3, а «$C$3» будет работать точно так же, как «C3», если вы скопируете формулу. Примечание: при вводе формул вы можете использовать клавишу F4 сразу после ввода ссылки на ячейку для переключения между различными относительными/абсолютными версиями этого адреса ячейки.
Хитрость при создании электронных таблиц заключается в том, чтобы перед копированием формулы решить, какие ячейки в формуле должны быть относительными, а какие — абсолютными. Если некоторые ссылки на ячейки относятся к входным ячейкам в электронной таблице, вы обычно хотите, чтобы эти ячейки были абсолютными.
В статье ниже приведены дополнительные инструкции по абсолютным и относительным ссылкам на ячейки.
Карин Стилл
Excel использует два типа ссылок на ячейки для создания формул. У каждого своя цель. Читайте дальше, чтобы определить, какой тип ссылки на ячейку использовать для формулы.
Относительные ссылки на ячейки
Это наиболее широко используемый тип ссылки на ячейку в формулах. Относительные ссылки на ячейки — это базовые ссылки на ячейки, которые корректируются и изменяются при копировании или использовании автозаполнения.
=СУММ(B5:B8), как показано ниже, изменяется на =СУММ(C5:C8) при копировании в следующую ячейку.
Абсолютные ссылки на ячейки
Возникают ситуации, когда ссылка на ячейку должна оставаться неизменной при копировании или при использовании автозаполнения. Знаки доллара используются для сохранения постоянной ссылки на столбец и/или строку.
В приведенном ниже примере при расчете комиссионных для торгового персонала вы не хотите, чтобы ячейка B10 изменялась при копировании формулы вниз. Вы хотите, чтобы и столбец, и строка оставались одинаковыми, чтобы ссылаться на эту точную ячейку. При использовании в формуле $B$10 ни один из них не изменяется при копировании.
Более сложный пример:
Предположим, что вам нужно рассчитать цены товаров на складе с двумя разными ценовыми скидками. Взгляните на рабочий лист ниже.
Проверьте формулу в ячейке E4. Сделав ссылку на первую ячейку $C4, вы предотвратите изменение столбца при копировании, но позволите строке измениться при копировании вниз, чтобы приспособиться к снижению цен на различные товары. Сделав ссылку на последнюю ячейку A$12, вы предотвратите изменение номера строки при копировании вниз, но позволите столбцу измениться и отразить скидку B при копировании. Смущенный?Посмотрите на рисунок ниже и результаты ячеек.
Теперь вы можете подумать, а почему бы просто не использовать 10% и 15% в реальных формулах? Разве это не было бы проще? Да, если вы уверены, что процент скидки никогда не изменится, что крайне маловероятно. Скорее всего, в конечном итоге эти проценты необходимо будет скорректировать. Ссылаясь на ячейки, содержащие 10% и 15%, а не на фактические числа, когда процент изменяется, все, что вам нужно сделать, это изменить процент один раз в ячейке A12 и/или B12 вместо того, чтобы перестраивать все ваши формулы. Excel автоматически обновит цены со скидками, чтобы отразить изменение процента скидки.
Краткий обзор использования абсолютных ссылок на ячейки:
| $A1 | Разрешает изменение ссылки на строку, но не ссылка на столбец. |
| A$1 | Разрешает изменять ссылку на столбец, но не на строку. |
| $A$1 | Не позволяет изменять ни столбец, ни ссылку на строку. |
Существует ярлык для размещения абсолютных ссылок на ячейки в ваших формулах!
При вводе формулы после ввода ссылки на ячейку нажмите клавишу F4. Excel автоматически делает ссылку на ячейку абсолютной! Продолжая нажимать F4, Excel будет циклически перебирать все возможности абсолютной ссылки. Например, в первой формуле абсолютной ссылки на ячейку в этом руководстве, =B4*$B$10, я мог бы ввести =B4*B10, а затем нажать клавишу F4, чтобы изменить B10 на $B$10. Продолжая нажимать F4, вы получите 10 B$, затем B10 и, наконец, B10. Нажатие F4 изменяет только ссылку на ячейку непосредственно слева от точки вставки.
Я надеюсь, что этот учебник сделал эти типы ссылок на ячейки «абсолютно» понятными!
Изменение ссылки с относительной на абсолютную.
Формула в ячейке C7 работает правильно. Он ссылается на ячейку C1 для расчета суммы. Но если вы используете дескриптор заполнения для копирования формулы из ячейки C7 в ячейки C8 и C9, возникает проблема. Ссылка на ячейку C1 изменена на ячейки C2 и C3. Поскольку эти ячейки пусты, результаты в ячейках C8 и C9 неверны; они такие же, как суммы слева. (Налог не добавляется.)
Чтобы лучше понять, в столбце D отображаются формулы из столбца C. Когда формулу в ячейке C7 перетаскивали вниз, ссылка C1 менялась на C2 в ячейке C8 и на C3 в ячейке C9. Часто это то, что вам нужно — чтобы Excel автоматически менял ссылки на ячейки при копировании формулы. Но иногда, как в этой ситуации, это не то, что вам нужно. Вам нужна абсолютная ссылка на ячейку.
Формула в ячейке C17 почти идентична формуле в ячейке C7, за исключением того, что ссылка на ячейку C1 сделана абсолютной строкой путем размещения знака доллара перед номером строки. Формула в ячейке C17 выглядит так: =B17*(1 + C$1) . Когда эту формулу перетащили в ячейки C18 и C19, ссылка не была скорректирована, а осталась указывать на ячейку C1. Обратите внимание, что в этом примере только часть строки ссылки становится абсолютной. Это все, что необходимо. Вы могли бы сделать ссылку полностью абсолютной, сделав это: =B17*(1 + $C$1) . Результат будет таким же, но в этом примере это не требуется.
Поместите знак доллара перед буквой столбца ссылки на ячейку, чтобы создать абсолютную ссылку на столбец. Поместите знак доллара перед номером строки, чтобы создать абсолютную ссылку на строку.
Excel поддерживает два стиля ссылок на ячейки: старый добрый стиль A1 и стиль R1C1. Стиль R1C1 использует числовую систему как для строки, так и для столбца, например: R4C10. В этом примере R4C10 означает строку 4, столбец 10.
Чтобы изменить стиль ссылки на ячейку, выберите «Файл» → «Параметры» и проверьте стиль ссылки R1C1 в области «Работа с формулами» на вкладке «Формулы». Использование формата R1C1 также приводит к тому, что столбцы на листе отображаются в виде чисел, а не буквенной системы. Это полезно, когда вы работаете с большим количеством столбцов. Например, столбец CV позиционно является 100-м столбцом. Запомнить 100 легче, чем запомнить резюме.
Чтобы вернуться к функции АДРЕС Excel, требуется до пяти аргументов:
- Номер строки ссылки
- Номер столбца ссылки
- Число, указывающее функции, как вернуть ссылку. По умолчанию 1, но может быть
- 1 для полного абсолютного значения
- 2 для абсолютной строки и относительного столбца
- 3 для относительной строки и абсолютного столбца
- 4 для полного родственника
- 0 использует стиль R1C1.
- 1 (по умолчанию, если он не указан) использует стиль A1.
Вы также можете ввести ссылки на ячейки, в которых находятся эти значения.
Если вы хотите, чтобы результатом была ссылка на внешнюю книгу, введите запятую (,) и введите имя книги и имя листа вместе. Имя книги заключено в скобки, а вся ссылка заключена в двойные кавычки, например: "[Книга1]Лист2" .
Вместо того, чтобы вводить номер строки и номер столбца непосредственно в ADDRESS, вы можете ввести ссылки на ячейки. Однако значения, которые вы найдете в этих ячейках, должны оцениваться как числа, которые можно использовать в качестве номера строки и номера столбца.
Об этой статье
Эта статья взята из книги:
Об авторе книги:
Кен Блуттман — опытный разработчик программного обеспечения и веб-приложений, специализирующийся на Excel/VBA и веб-приложениях, ориентированных на базы данных. Его последние проекты включают крупномасштабные облачные приложения и разработку мобильных приложений.
Читайте также:
- Программное обеспечение Xiaomi DVR
- Как получить дискорд-токен
- Для создания таблиц в текстовом документе необходимо использовать notepad2 notepad dpad ms word
- Программа прошивки Alcatel
- Как преобразовать даты в Excel в тот же формат