Относительная и абсолютная адресация в excel: Абсолютная и относительная адресация

Абсолютная и относительная адресация ячеек

Обучающие цели урока:

1. Обобщение основных понятий электронной таблицы Excel.
2. Определение абсолютного, относительного и смешанного адресов ячейки
3. Использование различных видов адресации при расчетах с помощью математических формул.

Развивающие цели урока:

1. Развитие умения обобщать полученные знания и последовательно их применять в процессе выполнения работы.
2. Развитие умения пользоваться различными видами адресации при решении различных типов задач

Воспитательные цели урока:

1. Привитие навыков вычислительной работы в ЭТ Excel.
2. Воспитание аккуратности и точности при записи математических формул.

Тип урока: освоение и закрепление нового материала.

План урока

1. Организационный момент
2. Активизация опорных ЗУН учащихся
3. Тест по основным терминам электронных таблиц
4. Приобретение новых умений и навыков.
5. Практическая работа.
6. Подведение итогов, выставление оценок.

Ход урока

I. Организационный момент

На ваших столах лежат карточки двух цветов: красного и зеленого.

— Карточка красного цвета означает:

«Я удовлетворен уроком, урок был полезен для меня, я много, с пользой и хорошо работал на уроке, я понимал все, о чем говорилось и что делалось на уроке»

— Карточка зеленого цвета означает:

«Пользы от урока было мало: я не очень понимал, о чем идет речь, к ответу на уроке я был не готов»

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

II. Активизация опорных ЗУН учащихся

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

После повторения основных базовых понятий электронных таблиц каждый ученик получает контрольный лист (Приложение 1) и отвечает на вопросы (7 мин.)

III. Приобретение новых умений и навыков

Формулы представляют собой выражения, по которым выполняются вычисления на рабочем листе. Формула начинается со знака равенства (=). В качестве аргументов формулы обычно используются значения ячеек, например: =A1+B1.

Для вычислений в формулах используют различные виды адресации.

Адрес ячейки, изменяющийся при копировании формулы называется относительным. Он имеет вид В1, А3. По умолчанию программа Excel рассматривает адреса ячеек как относительные.

Относительная адресация ячеек – обычное явление при вычислениях по формулам. При копировании такой формулы вправо или влево будет изменяться заголовок столбца в имени ячейки, а при копировании вверх или вниз – номер строки.

Абсолютная адресация используется в том случае, когда нужно использовать значение, которое не будет меняться в процессе вычислений. Тогда записывают, например, так: =$А$5. Соответственно, при копировании такой формулы в другие ячейки текущего рабочего листа, в них всегда будет значение =$А$5. Для того, чтобы задать ячейке абсолютный адрес, необходимо перед номером строки и номером столбца указать символ “$” либо нажать клавишу F4.

Смешанная адресация представляет собой комбинацию относительной и абсолютной адресаций, когда одна из составляющих имени ячейки остается неизменной при копировании. Примеры такой адресации: $A3, B$1.

IV. Практическая работа

Учащимся предлагается выполнить практическую работу «Накладная на покупку канцтоваров». Для выполнения задания каждому учащемуся раздается задание с необходимым комментарием к выполнению задания

Дополнительное задание «Таблица умножения»:

V. Подведение итогов, выставление оценок

Абсолютная адресация в Excel: 2 проверенных способа

Как известно, в таблицах Excel существует два вида адресации: относительная и абсолютная. В первом случае ссылка изменяется по направлению копирования на относительную величину сдвига, а во втором — является фиксированной и при копировании остается неизменной. Но по умолчанию все адреса в Экселе являются абсолютными. В то же время, довольно часто присутствует необходимость использовать абсолютную (фиксированную) адресацию. Давайте узнаем, какими способами это можно осуществить.

Применение абсолютной адресации

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

В Excel существует два способа задать фиксированную адресацию: путем формирования абсолютной ссылки и с помощью функции ДВССЫЛ. Давайте рассмотрим каждый из указанных способов подробно.

Способ 1: абсолютная ссылка

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

=A1

У фиксированного адреса перед значением координат устанавливается знак доллара:

=$A$1

Знак доллара можно ввести вручную. Для этого нужно установить курсор перед первым значением координат адреса (по горизонтали), находящегося в ячейке или в строке формул. Далее, в англоязычной раскладке клавиатуры следует кликнуть по клавише «4» в верхнем регистре (с зажатой клавишей «Shift»). Именно там расположен символ доллара. Затем нужно ту же процедуру проделать и с координатами по вертикали.

Существует и более быстрый способ. Нужно установить курсор в ячейку, в которой находится адрес, и щелкнуть по функциональной клавише F4. После этого знак доллара моментально появится одновременно перед координатами по горизонтали и вертикали данного адреса.

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

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

1. Итак, в первую ячейку столбца «Заработная плата» вводим формулу умножения ставки соответствующего работника на коэффициент. В нашем случае эта формула имеет такой вид:

   =C4*G3

2. Чтобы рассчитать готовый результат, щелкаем по клавише Enter на клавиатуре. Итог выводится в ячейку, содержащую формулу.
3. Мы рассчитали значение зарплаты для первого сотрудника. Теперь нам нужно это сделать для всех остальных строк. Конечно, операцию можно записать в каждую ячейку столбца «Заработная плата» вручную, вводя аналогичную формулу с поправкой на смещение, но у нас стоит задача, как можно быстрее выполнить вычисления, а ручной ввод займет большое количество времени. Да и зачем тратить усилия на ручной ввод, если формулу можно попросту скопировать в другие ячейки?

   Для копирования формулы применим такой инструмент, как маркер заполнения. Становимся курсором в нижний правый угол ячейки, где она содержится. При этом сам курсор должен преобразоваться в этот самый маркер заполнения в виде крестика. Зажимаем левую кнопку мыши и тянем курсор вниз до конца таблицы.

4. Но, как видим, вместо корректного расчета заработной платы для остальных сотрудников, мы получили одни нули.
5. Смотрим, в чем причина такого результата. Для этого выделяем вторую ячейку в столбце «Заработная плата». В строке формул отображается соответствующее данной ячейке выражение. Как видим, первый множитель (C5) соответствует ставке того работника, зарплату которого мы рассчитываем. Смещение координат по сравнению с предыдущей ячейкой произошло из-за свойства относительности. Впрочем, в конкретно данном случае это нам и нужно. Благодаря этому первым множителем стала ставка именно нужного нам работника. Но смещение координат произошло и со вторым множителем. И теперь его адрес ссылается не на коэффициент (1,28), а на пустую ячейку, расположенную ниже.

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

6. Для исправления ситуации нам нужно изменить адресацию второго множителя с относительной на фиксированную. Для этого возвращаемся к первой ячейке столбца «Заработная плата», выделив её. Далее перемещаемся в строку формул, где отобразилось нужное нам выражение. Выделяем курсором второй множитель (G3) и жмем на функциональную клавишу на клавиатуре.
7. Как видим, около координат второго множителя появился знак доллара, а это, как мы помним, является атрибутом абсолютной адресации. Чтобы вывести результат на экран жмем на клавишу Enter.
8. Теперь, как и ранее вызываем маркер заполнения, установив курсор в правый нижний угол первого элемента столбца «Заработная плата». Зажимаем левую кнопку мыши и тянем его вниз.
9. Как видим, в данном случае расчет был проведен верно и сумма заработной платы для всех работников предприятия рассчитана корректно.
10. Проверим, как была скопирована формула. Для этого выделяем второй элемент столбца «Заработная плата». Смотрим на выражение, расположенное в строке формул. Как видим, координаты первого множителя (C5), который по прежнему является относительным, сдвинулись по сравнению с предыдущей ячейкой на один пункт вниз по вертикали. Зато второй множитель ($G$3), адресацию в котором мы сделали фиксированной, остался неизменным.

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

=A$1

Этот адрес тоже считается смешанным:

=$A1

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

Посмотрим, как такую смешанную ссылку можно применить на практике на примере все той же таблицы заработной платы сотрудников предприятия.

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

   Выделяем первый элемент столбца «Заработная плата» и в строке формул выполняем вышеуказанную манипуляцию. Получаем формулу следующего вида:

   =C4*G$3

   Как видим, фиксированная адресация во втором множителе применяется только по отношению к координатам строки. Для вывода результата в ячейку щелкаем по кнопке Enter.

2. После этого посредством маркера заполнения копируем данную формулу на диапазон ячеек, который расположен ниже. Как видим, расчет заработной платы по всем сотрудникам выполнен корректно.
3. Смотрим, как отображается скопированная формула во второй ячейке столбца, над которым мы выполняли манипуляцию. Как можно наблюдать в строке формул, после выделения данного элемента листа, несмотря на то, что абсолютную адресацию у второго множителя имели только координаты строк, смещение координат столбца не произошло. Это связано с тем, что мы выполняли копирование не по горизонтали, а по вертикали. Если бы мы выполнили копирование по горизонтали, то в аналогичном случае, наоборот, пришлось бы делать фиксированную адресацию координат столбцов, а для строк эта процедура была бы необязательной.

Урок: Абсолютные и относительные ссылки в Экселе

Способ 2: функция ДВССЫЛ

Вторым способом организовать абсолютную адресацию в таблице Excel является применение оператора ДВССЫЛ. Указанная функция относится к группе встроенных операторов «Ссылки и массивы». Её задачей является формирование ссылки на указанную ячейку с выводом результата в тот элемент листа, в котором находится сам оператор. При этом ссылка прикрепляется к координатам ещё крепче, чем при использовании знака доллара. Поэтому иногда принято называть ссылки с использованием ДВССЫЛ «суперабсолютными». Этот оператор имеет следующий синтаксис:

=ДВССЫЛ(ссылка_на_ячейку;[a1])

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

Аргумент «Ссылка на ячейку» является ссылкой на элемент листа Excel в текстовом виде. То есть, это обычная ссылка, но заключенная в кавычки. Именно это и позволяет обеспечить свойства абсолютной адресации.

Аргумент «a1» — необязательный и используется в редких случаях. Его применение необходимо только тогда, когда пользователь выбирает альтернативный вариант адресации, а не обычное использование координат по типу «A1» (столбцы имеют буквенное обозначение, а строки — цифровое). Альтернативный вариант подразумевает использование стиля «R1C1», в котором столбцы, как и строки, обозначаются цифрами. Переключиться в данный режим работы можно через окно параметров Excel. Тогда, применяя оператор ДВССЫЛ, в качестве аргумента «a1» следует указать значение «ЛОЖЬ». Если вы работает в обычном режиме отображения ссылок, как и большинство других пользователей, то в качестве аргумента «a1» можно указать значение «ИСТИНА». Впрочем, данное значение подразумевается по умолчанию, поэтому намного проще вообще в данном случае аргумент «a1» не указывать.

Взглянем, как будет работать абсолютная адресация, организованная при помощи функции ДВССЫЛ, на примере нашей таблицы заработной платы.

1. Производим выделение первого элемента столбца «Заработная плата». Ставим знак «=». Как помним, первый множитель в указанной формуле вычисления зарплаты должен быть представлен относительным адресом. Поэтому просто кликаем на ячейку, содержащую соответствующее значение оклада (C4). Вслед за тем, как её адрес отобразился в элементе для вывода результата, жмем на кнопку «умножить» (*) на клавиатуре. Затем нам нужно перейти к использованию оператора ДВССЫЛ. Выполняем щелчок по иконке «Вставить функцию».
2. В открывшемся окне Мастера функций переходим в категорию «Ссылки и массивы». Среди представленного списка названий выделяем наименование «ДВССЫЛ». Затем щелкаем по кнопке «OK».
3. Производится активация окошка аргументов оператора ДВССЫЛ. Оно состоит из двух полей, которые соответствуют аргументам этой функции.

   Ставим курсор в поле «Ссылка на ячейку». Просто кликаем по тому элементу листа, в котором находится коэффициент для расчета зарплаты (G3). Адрес тут же появится в поле окна аргументов. Если бы мы имели дело с обычной функцией, то на этом введение адреса можно было бы считать завершенным, но мы используем функцию ДВССЫЛ. Как мы помним, адреса в ней должны иметь вид текста. Поэтому оборачиваем координаты, которые расположись в поле окна, кавычками.

   Так как мы работаем в стандартном режиме отображения координат, то поле «A1» оставляем незаполненным. Щелкаем по кнопке «OK».

4. Приложение выполняет вычисление и выводит результат в элемент листа, содержащий формулу.
5. Теперь производим копирование данной формулы во все остальные ячейки столбца «Заработная плата» посредством маркера заполнения, как мы это делали ранее. Как видим, все результаты были рассчитаны верно.
6. Посмотрим, как отображается формула в одной из ячеек, куда она была скопирована. Выделяем второй элемент столбца и смотрим на строку формул. Как видим, первый множитель, являющийся относительной ссылкой, изменил свои координаты. В то же время, аргумент второго множителя, который представлен функцией ДВССЫЛ, остался неизменным. В данном случае была использована методика фиксированной адресации.

Урок: Оператор ДВССЫЛ в Экселе

Абсолютную адресацию в таблицах Excel можно обеспечить двумя способами: использование функции ДВССЫЛ и применение абсолютных ссылок. При этом функция обеспечивает более жесткую привязку к адресу. Частично абсолютную адресацию можно также применять при использовании смешанных ссылок.

Мы рады, что смогли помочь Вам в решении проблемы.

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

ТЕМА: ОТНОСИТЕЛЬНАЯ И АБСОЛЮТНАЯ АДРЕСАЦИЯ. ИМЯ ЯЧЕЙКИ. Пример

ТЕМА: ПОСТРОЕНИЕ ДИАГРАММ. Пример 1

ЛАБОРАТОРНАЯ РАБОТА 20 ТЕМА: ПОСТРОЕНИЕ ДИАГРАММ Пример 1 В таблице даны сведения о количестве тракторов, сделанных разными заводами за каждый из четырех кварталов 2002 года. 1. Напечатайте таблицу. 2.

Подробнее

Способы адресации в Excel

Способы адресации в Excel В Excel имеется три способа адресации (обращения к ячейкам): абсолютная, относительная и смешанная. Адреса ячеек меняются при копировании формул: При копировании по горизонтали

Подробнее

ПРАКТИЧЕСКАЯ РАБОТА 27

ПРАКТИЧЕСКАЯ РАБОТА 27 Т е м а : ПОДБОР ПАРАМЕТРА, ОРГАНИЗАЦИЯ ОБРАТНОГО РАСЧЕТА Цель занятия. Изучение технологии подбора параметра при обратных расчетах. Задание 27.1. Используя режим подбора параметра,

Подробнее

Дублирование данных. Автор: Автор :14

Автозаполнение может быть использовано для дублирования данных, т. е. для ввода одного и того же текста или числового значения в несколько ячеек одновременно. Для этого следует: 1) ввести в одну ячейку

Подробнее

PDF created with pdffactory Pro trial version

Практическая работа 3.9. Обработка информации, структурированной в виде списков MS Excel Цель работы. Выполнив эту работу, Вы научитесь: выполнять контроль ввода данных в таблицу; использовать инструмент

Подробнее

Относительные объемы продаж

Лабораторная работа. Excel Цель работы освоить работу с формулами и научиться строить диаграммы по заданным диапазонам числовых значений. Задание. 1. Откройте и создайте новую книгу. Сохраните ее под именем

Подробнее

ТЕМА: РАБОТА СО СПИСКАМИ. Теория

ЛАБОРАТОРНАЯ РАБОТА 23 ТЕМА: РАБОТА СО СПИСКАМИ Теория Списком называется таблица, строки которой содержат однородную информацию. Первая строка списка называется заглавной. Остальные строки таблицы называются

Подробнее

Лабораторная работа 8. Анализ «Что-Если»

1 Лабораторная работа 8. Анализ «Что-Если» Цель работы: освоить начальные навыки экономического анализа данных с помощью специальных инструментов Excel. Задание 1. Рассчитать ежемесячную выплату при изменяющейся

Подробнее

Вычисления в Microsoft Excel 2010/2007

Вычисления в Microsoft Excel 2010/2007 Вычисления в Microsoft Excel 2010/2007 План занятия Создание формул Копирование формул Относительные и абсолютные ссылки Использование в формулах, данных с других

Подробнее

Организация данных на листе

Стр. 1 из 24 Организация данных на листе Способы организации данных Существует два способа организации данных на листе: таблица и список. При организации данных в виде таблицы формируются строки и столбцы

Подробнее

EXCEL. Встроенные функции

EXCEL Встроенные функции ПОНЯТИЕ ФУНКЦИИ Функции Excel — это специальные, заранее созданные формулы, которые позволяют легко и быстро выполнять сложные вычисления. Их можно сравнить со специальными клавишами

Подробнее

Памятка «Основы работы в Excel»

Памятка «Основы работы в Excel» Чтобы. нужно скриншот 1 Установить границы Выделить нужный диапазон ячеек, ячейки/ залить ячейки цветом найти панель инструментов «Шрифт» — инструмент «Гарницы». 2 Добавить/скопировать/встав

Подробнее

ЛАБОРАТОРНАЯ РАБОТА 6

ЛАБОРАТОРНАЯ РАБОТА 6 1. ТЕМА: «Создание и форматирование таблиц в текстовом документе» 2. ОБЩАЯ ЦЕЛЬ РАБОТЫ: получить навыки создания, редактирования, форматирования таблиц и выполнения вычислений в документах

Подробнее

Работа с табличным процессором Microsoft Excel

Работа с табличным процессором Microsoft Excel Краткие теоретические сведения Приложение Windows Excel позволяет формировать и выводить на печать документы, представленные в табличном виде, выполнять расчеты

Подробнее

Поля страницы. Автор: Автор :14

На вкладке Поля диалогового окна Параметры страницы в полях верхнее, нижнее, левое и правое устанавливаются отступы от края страниц до таблицы. От величины отступов зависит высота и ширина поля таблицы,

Подробнее

Формулы. Microsoft Excel

Формулы Microsoft Excel Введение. .. 2 Математические Операторы… 2 Ссылки на ячейки… 2 Создание формул… 4 Копирование формул с помощью автозаполнения… 6 Редактирование формул… 7 Порядок операций…

Подробнее

Табличный процессор Calc

Табличный процессор Calc Теоретические сведения Calc это электронные таблицы LibreOffice, состоящие из определенного числа листов. Каждый лист имеет имя, которое отображается на ярлыке листа. По умолчанию

Подробнее

Лабораторный практикум

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ Федеральное государственное бюджетное общеобразовательное учреждение высшего профессионального образования «Российский экономический университет имени

Подробнее

Редактор формул в Word

Работа Редактор формул в Wod Цель работы: изучение порядка ввода формул в текстовом редакторе Wod. Содержание работы: Ввод формулы с помощью программы MS Equtio. Редактирование формул. Ввод формулы с помощью

Подробнее

Лабораторная работа 3 Подбор параметров

1. Введение Лабораторная работа 3 Подбор параметров При решении различных задач часто приходится заниматься проблемой подбора одного значения путем изменения другого. Для этой цели весьма эффективно используется

Подробнее

ПРАКТИЧЕСКИЕ РАБОТЫ MS EXCEL

ПРАКТИЧЕСКИЕ РАБОТЫ MS EXCEL Задание 1. Создание таблицы «Продукты». 1. Открыть программу MS Excel. Лист1 переименовать в «Продукты». 2. В диапазоне ячеек А1:F6 набрать следующую таблицу: Продукт Цена

Подробнее

ЭКОНОМИЧЕСКИЕ РАСЧЕТЫ В MICROSOFT EXCEL

Практическая работа 15 Тема: ЭКОНОМИЧЕСКИЕ РАСЧЕТЫ В MICROSOFT EXCEL Цель занятия. Изучение технологии проведения экономических расчетов, расчета точки окупаемости инвестиций, накопления и инвестирования

Подробнее

Работа с базами данных в MS Excel

Работа с базами данных в MS Excel База данных (или в терминах MS Excel — список) представляет собой упорядоченную информацию, объединенную в единое целое. Строки в базе данных называются записями, а столбцы

Подробнее

Использование электронных таблиц. MS Excel.

Практикум: Использование электронных таблиц. MS Excel. Задание 1. Работа с листами, сохранение рабочей книги 1. Открыть MS Excel. 2. Щелкнуть правой кнопкой мыши по ярлычку «Лист1». 3. В появившемся контекстном

Подробнее

Excel. Форматирование данных в ячейке

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

Подробнее

Для чего нужны диаграммы

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

Подробнее

6. Абсолютная и относительная адресация

6. Абсолютная и относительная адресация В этом модуле вы: — Узнаете, что такое абсолютная и относительная адресация; — Научитесь применять относительную адресацию; — Научитесь применять абсолютную адресацию;

Подробнее

Тема: Фильтрация данных

Тема: Фильтрация данных Отфильтровать список показать только те записи, которые удовлетворяют заданному критерию. Excel предоставляет две команды для фильтрации данных: 1. Автофильтр (для простых условий

Подробнее

Справочник MS Excel

Построим круговую диаграмму для сравнения доходов от каждой фирмы, представленных в прошлой таблице.

Для этого выполним следующие действия:

1. Выделите блок ячеек B2-12, J2-12(Если нужно выделить определённые столбцы, которые находятся в разных местах, то выделяйте с помощью ЛКМ и зажатым Ctrl).

2.Перейдите в Вкладку ставка-Диаграммы и выберите тип Круговая диаграмма, вид круговая.

У нас появится элементарная круговая диаграмма:

   Так же появиться три вкладки: конструктор, макет, формат. Всё это относиться к нашей диаграмме, с помощью этих функций мы можем персонализировать нашу диаграмму.

Примечание: в отличие от прошлых версий, с в версии 2016 года Microsoft решила объединить бывшую вкладку “Макет” во вкладке “Конструктор”, поэтому пользователям прошлых версий может быть в первое время трудно освоится с данным фактом.

Во вкладке Конструктор, выберем :

1) Название диаграммы- над диаграммой. «Доход от фирм»(вместо Доход от фирм).

2) Легенда-Добавить легенду слева.

3) Подписи данных-Дополнительные параметры подписей данных

Включим в подписки «доли».


   Далее увеличим область диаграммы, чтобы данные было хорошо видно.

В результате у нас получиться следующая диаграмма:

Теперь во вкладке Конструктор выберем:

• Изменить тип диаграммы, выберем тип Объемная круговая.

• Правой кнопкой мыши нажмём на нашу диаграмму, выберем: «Поворот объемной фигуры» изменим значение Y на 20.

В результате наша диаграмма станет более объемной:

Теперь поделим наш круг на части и повернём.
• Нажмите по диаграмме правой кнопкой мыши

• Выберете пункт «Формат точки данных».

• Поменяем «Угол поворота»=70, и «Разрезная круговая диаграмма»=50%.

Или мы можем отдельно отделять куски от круговой диаграммы, перетаскивая их с помощью левой кнопки мыши:

Абсолютная и относительная адресация — презентация онлайн

1. Абсолютная и относительная адресация

АБСОЛЮТНАЯ И
ОТНОСИТЕЛЬНАЯ АДРЕСАЦИЯ
=A1+2
Однотипные формулы – формулы,
имеющие одинаковую структуру и
отличающиеся только конкретными
ссылками

3. Отображение формул

ОТОБРАЖЕНИЕ ФОРМУЛ
Вкл. Формулы – группа Зависимости формул –
Показать формулы
Правило
автоматического
изменения
относительных ссылок
Принцип
отностительной
адресации
Адреса ячеек,
используемых в
формулах, определены
относительно места
расположения формулы
При копировании (или
перемещении) формулы с
относительными ссылками
из одной ячейки в другую,
сохраняется такое же, как в
оригинале расположение
ячеек с исходными данными
и ячейки, где хранится
результат вычисления

5. Правило автоматического изменения относительных ссылок

ПРАВИЛО АВТОМАТИЧЕСКОГО
ИЗМЕНЕНИЯ ОТНОСИТЕЛЬНЫХ ССЫЛОК
=A1+B4
=D4+E7

6. Относительная ссылка

ОТНОСИТЕЛЬНАЯ ССЫЛКА
С3
D3
E3
F3
B4
B5
B6
B7
B8
B9
— адрес ячейки автоматически изменяется при
копировании формулы

7. Решим задачу

РЕШИМ ЗАДАЧУ
С1 → в D1
=А1+В1
=B1+C1
=A2+B2
=B2+C2
=A3+B3 =B3+C3
=A4+B4 =B4+C4
С1 → в D2:D4
С1 → в C2:C4

8. В ячейке D2 записана формула: =A2*(B2+C2). Ее скопировали в ячейку D3. Какое значение будет выведено в ячейке D3?

56
50
33
20

9. Абсолютные ссылки (замораживание адреса)

АБСОЛЮТНЫЕ ССЫЛКИ (ЗАМОРАЖИВАНИЕ
АДРЕСА)
Адрес ячейки при копировании формулы не
изменяется. Для этого в адрес ячейки вводят
символ $ (нажимают клавишу F4).
$B$3
$B$3
$B$3
$B$3
$B$3
$B$3
$B$3
$B$3
$B$3
$B$3

10. Смешанные ссылки (частичные абсолютные)

СМЕШАННЫЕ ССЫЛКИ (ЧАСТИЧНЫЕ
АБСОЛЮТНЫЕ)
$B3
$B3 $B3 $B3
$B4
$B5
$B6
$B7
$B8
$B9
C$3 D$3 E$3 F$3
B$3
B$3
B$3
B$3
B$3
B$3

11. Правило копирования формул

ПРАВИЛО КОПИРОВАНИЯ ФОРМУЛ
Ввести только одну формулу-оригинал, в
которой с помощью относительной или
абсолютной адресации указать полностью или
частично изменяемые ссылки
После ввода исходной формулы скопировать ее
в нужные ячейки. Если формула должна быть
скопирована в смежные ячейки, можно
использовать маркер заполнения

12. Таблица умножения

ТАБЛИЦА УМНОЖЕНИЯ
=A3*B2 =A3*C2
=A4*B2 =A4*C2
=$A3+B$2

13. В таблице указана стоимость туристических путевок в долларах. Пересчитать ее в рублях с учетом курса доллара.

В ТАБЛИЦЕ УКАЗАНА СТОИМОСТЬ ТУРИСТИЧЕСКИХ
ПУТЕВОК В ДОЛЛАРАХ. ПЕРЕСЧИТАТЬ ЕЕ В РУБЛЯХ С
УЧЕТОМ КУРСА ДОЛЛАРА.

MS Excel. Относительная и абсолютная адресация ячеек

Относительные и абсолютные адреса ячеек

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

Большинство ссылок в формулах записываются в относительной форме — например, С3 (столбец)(строка). Относительными называются ссылки, которые при копировании в составе формулы в другую ячейку автоматически изменяются.

При копировании формулы с относительной ссылкой (столбец)(строка) на n строк ниже и на m столбцов правее ссылка изменяется на (столец+m)(строка+n)

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

Некоторые ссылки в формулах записываются в абсолютной форме — например, $С$3

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

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

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

Для того, чтобы превратить относительную ссылку в абсолютную, достаточно в режиме редактирования формулы установить курсор непосредственно за ссылкой и нажать клавишу <F4>

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

При помощи символа абсолютной адресации Вы можете гибко варьировать способ адресации ячеек. Например $B11 обозначает , что при копировании формул будет изменяться только адресация строки ячейки, а при обозначении B$11 — только столбца. Такая адресация называется смешанной.

При вводе формулы в строке формул, можно быстро перебрать по кругу относительный , смешанный и абсолютный адреса. Просто укажите на какой — нибудь адрес и нажимайте <F4>, чтобы по кругу перебрать все четыре варианта.

Использование имен для абсолютной адресации

Другой способ абсолютной адресации заключается в назачении имен ячейкам и использовании их в формулах

Например назначив ячейки B11 имени курс можно ввести следующую формулу

=F14*курс

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

Для того, чтобы назначить имя ячейки необходимо

Выделить ячейку

Выполнить команду меню Вставка — Имя – Присвоить

Введите имя в стоке имя ячейки, например курс

Нажмите кнопку OK

Введите формулу.

MS Excel. Функции: назначение и использование

Microsoft Excel (также иногда называется Microsoft Office Excel[1]) — программа для работы с электронными таблицами, созданная корпорацией Microsoft для Microsoft Windows, Windows NT и Mac OS. Она предоставляет возможности экономико-статистических расчетов, графические инструменты и, за исключением Excel 2008 под Mac OS X, язык макропрограммирования VBA (Visual Basic for Application). Microsoft Excel входит в состав Microsoft Office и на сегодняшний день Excel является одним из наиболее популярных приложений в мире.

Зачем нужен Эксель

Excel – это программа, которая нужна, в первую очередь, бухгалтерам и экономистом. Ведь в ней можно составлять таблицы (отчеты), производить вычисления любой сложности, составлять диаграммы. Причем, все это можно сделать без особого труда и невероятных познаний. Можно сказать, что Excel – это большой и мощный калькулятор с множеством функций и возможностей. Но это вовсе не означает, что Вам нужно уметь пользоваться всем тем, что «умеет» эта программа. Большинству людей достаточно базовых знаний, получить которые Вы сможете из следующих уроков.

Что из себя представляет программа Excel

Программа Microsoft Excel представляет из себя большую таблицу, в которую можно вносить данные, то есть печатать слова и цифры. Также, используя функции этой программы, можно производить с цифрами разные манипуляции (складывать, вычитать, умножать, делить и многое другое). Многие думают, что Excel – это только таблицы. То есть они убеждены, что все таблицы на компьютере составляются только в этой программе. Это не совсем верно. Да, действительно, Excel представляет из себя таблицу. Но эта программа нужна, в первую очередь, для вычислений. Если требуется не только расчертить таблицу со словами и цифрами, но еще и произвести с цифрами какие-либо действия (сложить, умножить, вычислить процент и т.д), то тогда Вам нужно воспользоваться программой Microsoft Excel. А если же Вам нужно составить таблицу без вычислений, то есть ввести уже готовые данные, то быстрее и удобнее это сделать в программе Microsoft Word.

MS Excel. Создание диаграмм и графиков

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

Для облегчения процедуры построения диаграммы служит Мастер диаграмм. Он разбивает процесс создания диаграммы на несколько шагов.

Для построения диаграммы необходимо выделить диапазон ячеек, для которых будет построена диаграмма. Чтобы отобразить на диаграмме названия столбцов и строк, включите их в выделенный диапазон ячеек. Диаграмма для выделенных данных строится по команде «Вставка > Диаграмма» или по клику на значке . Выберите тип диаграммы и нажмите «Готово». Простейшая диаграмма будет создана.

Каждый тип диаграммы имеет несколько вариантов представления. Так, например, стандартная гистограмма представлена в 7 вариантах, а линейчатая диаграмма — в 6 вариантах.

Чтобы увидеть, как ваши данные будут выглядеть при выборе различных типов диаграмм, нажмите и не отпускайте кнопку «Просмотр результата». Поле «Вид» при этом будет заменено полем «Образец», в котором будет отображена диаграмма.

Графики

Excel предлагает 14 типов диаграмм, каждый из которых подходит для эффективного представления данных определенного класса.

Построение графиков, отображающих связь между X и У

Если использовать таблицу, состоящую из двух столбцов, в которых представлены значения двух взаимосвязанных переменных, например, X и У, то большинство типов диаграмм Excel создаст два независимых графика на одной диаграмме: один для X, другой — для У.

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

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

нажать кнопку «Мастер диаграмм» на панели инструментов;

в диалоговом окне «Мастер диаграмм» на первом шаге открыть вкладку «Нестандартные», выбрать тип: «Гладкие графики» и нажать кнопку «Далее».

На втором шаге построения диаграммы нужно открыть вкладку «Ряд», установить курсор в поле «Подписи по оси X», наать на кнопку свертывания диалогового окна справа от этого поля и выделить значения, которые будут отложены по оси абсцисс.

Редактирование диаграммы

Если выделить диаграмму, то ее можно перемещать, добавлять в нее данные, можно выделять, форматировать, перемещать и изменять размеры большинства входящих в него элементов.

Можно даже изменить тип уже созданной диаграммы. Для изменения типа диаграммы выделите ее. В контекстном меню выберите пункт «Тип диаграммы».

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

Ряды данных, подписи значений и легенды можно изменять поэлементно. Например, чтобы выбрать отдельный маркер данных в ряде данных, выберите нужный ряд данных и укажите маркер данных. Каждый из элементов диаграммы можно форматировать отдельно. Имя элемента диаграммы выводится в подсказке в случае, если установлен флажок «Показывать имена» на вкладке «Диаграмма» диалогового окна «Параметры».

Чтобы перейти в режим форматирования какого-либо элемента: координатной оси, названия диаграммы, отдельных рядов данных, щелкните на этом элементе. Вокруг выделенного элемента появится штриховая рамка. Имя графического объекта отобразится в поле строки формул. Выделенный элемент можно переместить, удерживая нажатой кнопку мыши.

Задача №7. Электронные таблицы. Абсолютная и относительная адресация. Графики и диаграммы.

Автор материалов — Лада Борисовна Есакова.

Microsoft Excel (в дальнейшем просто — Excel) — это программа выполнения расчетов и управления так называемыми электронными таблицами.). Формула начинается знаком =.

В Excel предусмотрены стандартные функции, которые могут быть использованы в формулах. Это математические, логические, текстовые, финансовые и другие функции. Однако, на экзамене Вам могут встретиться только самые простые функции: СЧЕТ (количество непустых ячеек), СУММ (сумма), СРЗНАЧ (среднее значение), МИН (минимальное значение), МАКС (максимальное значение).

Диапазон ячеек обозначается следующим образом: A1:D4 (все ячейки прямоугольника от A1 до D4.

Адреса ячеек бывают относительными, абсолютными и смешанными.

Они по-разному ведут себя при копировании формулы из ячейки в ячейку.

Относительная адресация:

Если в ячейке B2 мы напишем формулу =D1+3, то таблица воспримет это как «взять значение ячейки на две правее и на одну выше текущей, и прибавить к нему 3».

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

Абсолютная адресация:

Если нам не нужно, чтобы адрес пересчитывался при копировании формулы, мы можем его «закрепить» в формуле — поставить знак $ перед буквой и индексом ячейки: =$D$1+3. Такой адрес называется абсолютным. Такая формула не будет изменяться при копировании:

Смешанная адресация:

Если же мы хотим, чтобы при копировании формулы автоматически пересчитывался, к примеру, только индекс ячейки, а буква оставалась неизменной, мы можем «закрепить» в формуле только букву (или наоборот):  =$D1+3. Такой адрес называется смешанным. При копировании формулы будет меняться только индекс в адресе ячейки:

Электронные таблицы. Копирование формул.

Пример 1.

В ячейке C2 записана формула =$E$3+D2. Какой вид приобретет формула, после того как ячейку C2 скопируют в ячейку B1?

1) =$E$3+C1   2) =$D$3+D2      3) =$E$3+E3      4) =$F$4+D2

Решение:

Место расположения формулы меняется с C2 на B1, т.е. формула сдвигается на одну ячейку влево и на одну ячейку вверх (буква «уменьшается» на единицу и индекс уменьшается на единицу). Значит, так же изменятся все относительные адреса, а абсолютные (закрепленные знаком $) останутся неизменными:

=$E$3+С1.

Ответ: 1

Пример 2.

В ячейке В11 электронной таблицы записана формула. Эту формулу скопировали в ячейку А10. В результате значение в ячейке А10 вычисляется по формуле х—Зу, где х — значение в ячейке С22, а у — значение в ячейке D22. Укажите, какая формула могла быть написана в ячейке В11.

1) =C22-3*D22          2) =D$22-3*$D23      3) =C$22-3*D$22      4) =$C22-3*$D22

Решение:

Проанализируем поочередно каждую формулу:

Место расположения формулы меняется с B11 на A10, т.е. буква «уменьшается» на 1 и индекс уменьшается на 1.

Тогда при копировании формулы изменятся следующим образом:

1)      =B21-3*C21

2)      =C$22-3*$D22

3)      =B$22-3*C$22

4)      =$C21-3*$D21

Условию задачи соответствует формула 2).

Ответ: 2

Электронные таблицы. Определение значения формулы.

Пример 3.

Дан фрагмент электронной таблицы:

В ячейку D1 введена формула =$А$1*В1+С2, а затем скопирована в ячейку D2. Какое значение в результате появится в ячейке D2?

1) 10   2) 14    3) 16    4) 24

Решение:

Место расположения формулы меняется с D1 на D2, т.е. буква не меняется, а индекс увеличивается на 1.

Значит, формула примет вид: =$А$1*В2+С3. Подставим в формулу числовые значения ячеек:1*5+9=14. Правильный ответ указан под номером 2.

Ответ: 2

Пример 4.

В электронной таблице значение формулы =СРЗНАЧ(A6:C6) равно (-2). Чему равно значение формулы =СУММ(A6:D6), если значение ячейки D6 равно 5?

1) 1                    2) -1                     3) -3                     4) 7

Решение:

По определению среднего значения:

СРЗНАЧ(A6:C6) = СУММ(A6:С6)/3 = -2

Значит, СУММ(A6:С6) = -6

СУММ(A6:D6) = СУММ(A6:С6)+D6 = -6+5 = -1

Ответ: 2

Электронные таблицы и диаграммы.

Пример 5.

Дан фрагмент электронной таблицы в режиме отображения формул.

После выполнения вычислений построили диаграмму по значениям диапазона  A1:D1. Укажите полученную диаграмму:

Решение:

Вычислим по формулам значения ячеек A1:D1.

B1 = 3-2 =1

A1 = 2-1 =1

C1 = 1+2 =3

D1 = 1*3 =3

Этим данным соответствует диаграмма 3.

Ответ:3

Ты нашел то, что искал? Поделись с друзьями!

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

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

/ ru / excel2016 / создание-более-сложных-формул / содержание /

Введение

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

Необязательно: загрузите нашу рабочую тетрадь.

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

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

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

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

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

1. Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку D4 .
2. Введите формулу , чтобы вычислить желаемое значение. В нашем примере мы введем = B4 * C4 .
3. Нажмите Введите на клавиатуре. Формула будет рассчитана, и результат отобразится в ячейке.
4. Найдите маркер заполнения в правом нижнем углу нужной ячейки. В нашем примере мы найдем маркер заполнения для ячейки D4 .
5. Щелкните и перетащите маркер заполнения на ячейки, которые нужно заполнить. В нашем примере мы выберем ячейки D5: D13 .
6. Отпустите мышь. Формула будет скопирована в выбранные ячейки с относительными ссылками , отображая результат в каждой ячейке.

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

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

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

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

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

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

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

В приведенном ниже примере мы собираемся использовать ячейку E2 (которая содержит ставку налога 7.5%) для расчета налога с продаж для каждой позиции столбца D . Чтобы ссылка на налоговую ставку оставалась постоянной — даже когда формула копируется и заполняется в другие ячейки, — нам нужно сделать ячейку $ E $ 2 абсолютной ссылкой.

1. Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку D4 .
2. Введите формулу , чтобы вычислить желаемое значение. В нашем примере мы введем = (B4 * C4) * $ E $ 2 , что сделает $ E $ 2 абсолютной ссылкой.
3. Нажмите Введите на клавиатуре. Формула будет рассчитана, и результат отобразится в ячейке.
4. Найдите маркер заполнения в правом нижнем углу нужной ячейки. В нашем примере мы найдем маркер заполнения для ячейки D4 .
5. Щелкните и перетащите маркер заполнения на ячейки, которые вы хотите заполнить (ячейки D5: D13 в нашем примере).
6. Отпустите мышь. Формула будет скопирована в выбранные ячейки с абсолютной ссылкой , и значения будут вычислены в каждой ячейке.

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

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

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

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

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

Для ссылки на ячейки на листах:

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

1. Найдите ячейку, на которую хотите сослаться, и запишите ее рабочий лист. В нашем примере мы хотим сослаться на ячейку E14 на листе Menu Order .
2. Перейдите к нужному рабочему листу . В нашем примере мы выберем рабочий лист Catering Invoice .
3. Найдите и выберите ячейку , в которой должно отображаться значение. В нашем примере мы выберем ячейку C4 .
4. Введите знак равенства (=) , лист имя , за которым следует восклицательный знак (!) и адрес ячейки .В нашем примере мы введем = ‘Menu Order’! E14 .
5. Нажмите Введите на клавиатуре. Появится значение указанной ячейки. Теперь, если значение ячейки E14 изменится на листе «Порядок меню», оно будет автоматически обновлено на в листе «Счет-фактура кейтеринга».

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

Если вы ввели имя рабочего листа неправильно, #REF! В ячейке появится ошибка .В нашем примере ниже мы неправильно ввели имя рабочего листа. Чтобы отредактировать, проигнорировать или исследовать ошибку , нажмите кнопку Ошибка рядом с ячейкой и выберите параметр в меню .

Challenge!

1. Откройте нашу рабочую тетрадь.
2. Щелкните вкладку Бумажные товары в нижнем левом углу книги.
3. В ячейке D4 введите формулу, которая умножает цену единицы в B4 , количество в C4 и ставку налога в E2 .Обязательно используйте абсолютную ссылку на ячейку для ставки налога, потому что она будет одинаковой во всех ячейках.
4. Используйте маркер заполнения , чтобы скопировать только что созданную формулу в ячейки D5: D12 .
5. Измените ставку налога в ячейке E2 на 6,5%. Обратите внимание, что все ваши ячейки обновлены. Когда вы закончите, ваша рабочая тетрадь должна выглядеть следующим образом:
6. Щелкните вкладку Catering Invoice .
7. Удалите значение в ячейке C5 и замените его ссылкой на общую стоимость бумажных товаров. Подсказка: Стоимость бумажных товаров указана в ячейке E13 на листе Бумажные товары .
8. Используйте те же шаги, что и выше, для расчета налога с продаж для каждого элемента на листе Заказ меню . Итоговая стоимость в ячейке E14 должна обновиться. Затем в ячейке C4 рабочего листа Catering Invoice создайте ссылку на ячейку для только что рассчитанной суммы. Примечание: Если вы использовали нашу рабочую тетрадь, чтобы следить за ходом урока, возможно, вы уже выполнили этот шаг.
9. Когда вы закончите, рабочий лист Catering Invoice должен выглядеть примерно так:

/ ru / excel2016 / functions / content /

Общие сведения об относительной и абсолютной адресации (Microsoft Excel)

Обратите внимание:
Эта статья написана для пользователей следующих версий Microsoft Excel: 97, 2000, 2002 и 2003. Если вы используете более позднюю версию (Excel 2007 или более позднюю), этот совет может не сработать для вас .Чтобы ознакомиться с версией этого совета, написанного специально для более поздних версий Excel, щелкните здесь: Общие сведения об относительной и абсолютной адресации.

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

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

= A1
 

Это просто означает, что B1 будет содержать то же значение, что и в A1.Теперь предположим, что вы копируете ячейку B1 и вставляете ее в ячейки с B2 по B5. Когда Excel вставляет каждую ячейку, он изменяет формулу так, чтобы ссылка на ячейку была такой же относительно нового местоположения, как и в старом. В исходной формуле Excel знает, что ссылка на ячейку находится на одну ячейку слева от ячейки, содержащей формулу. Таким образом, каждая ячейка, в которую вставлена ​​формула, будет содержать формулу, которая имеет ссылку на ячейку на одну ячейку слева от целевой ячейки. Например, ячейка B2 будет содержать формулу = A2, а ячейка B5 будет содержать формулу = A5.

Если вы не хотите, чтобы Excel изменял указатель строки или столбца в ссылках на ячейки, необходимо использовать абсолютные ссылки на ячейки. Вы обозначаете ссылку как абсолютную (неизменяемую), ставя перед ней знак доллара ($). Вы можете поставить перед буквой столбца или номером строки знак доллара. Когда вы позже скопируете и вставите формулу, содержащую абсолютную ссылку, Excel не изменит эту часть ссылки, а вставит ее без изменений в целевой объект.

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

= 7 австралийских долларов
 

Другой способ убедиться, что Excel не изменяет ссылку на ячейку, — это присвоить ссылкам имена и использовать имена в формулах. Определение имен ячеек описано в ExcelTips.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.Этот совет (2062) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь: Общие сведения об относительной и абсолютной адресации .

Автор биографии

Аллен Вятт

Аллен Вятт — всемирно признанный автор, автор более чем 50 научно-популярных книг и многочисленных журнальных статей.Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнать больше о Allen …

Время округления

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

Открой для себя больше

Относительные ссылки на рабочие листы

Скопируйте формулу из одного места в другое, и Excel поможет скорректировать ссылки на ячейки в формуле.То есть это …

Открой для себя больше

Выбор объектов чертежа

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

Открой для себя больше

Разница между относительными и абсолютными ссылками в Excel

Важно ли знать разницу между относительными и абсолютными ссылками в Excel? Абсолютно! Простите за каламбур, но я не выдержал.А если серьезно, не пропускайте эту статью. Знание разницы между абсолютными и относительными ссылками в Excel может быстро вывести вас из уровня новичка в Excel.

Ключевое различие между относительными и абсолютными ячейками состоит в том, что относительные ссылки на ячейки перемещаются при их копировании, а абсолютные ссылки — нет. Абсолютные ссылки отмечаются знаком доллара перед ссылкой на ячейку. Таким образом, относительная ссылка на ячейку может выглядеть как = A1 + A2 , а абсолютная ссылка может выглядеть как = $ A $ 1 + $ A $ 2 .(На самом деле формула также может выглядеть так: = A1 + A2 или = A $ 1 + A $ 2 , но не будем забегать вперед.)

Зная эту информацию, вы должны знать четыре типа справочных материалов. . Я рекомендую открыть свой собственный пустой лист Excel, чтобы следить за ним. (Обратите внимание, что я не использую здесь официальные названия, но надеюсь, что «абсолютно абсолютное» станет популярным.)

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

Никаких знаков доллара! Если вы будете копировать этого плохого парня с места на место, формула будет двигаться вместе с ним.Поэтому, если вы введете = A1 + A2 в ячейку A3, а затем скопируете и вставите эту формулу в ячейку B3, она автоматически изменит формулу на = B1 + B2 .

Абсолютно Абсолютно

Все знаки доллара, детка. Если ввести = $ A $ 1 + $ A $ 2 в ячейку A3, затем скопировать и вставить эту формулу в ячейку B3, она останется = $ A $ 1 + $ A $ 2 . Удивительный!

Абсолютные столбцы

Вот где начинается сумасшествие. Если вы введете = $ A1 + $ A2 в ячейку A3, затем скопируете и вставите эту формулу в ячейку B3, она останется = $ A1 + $ A2 .Но если вы скопируете и вставите эту формулу в A4, она станет = $ A2 + $ A3 . Это потому, что ссылки на столбцы абсолютны, а ссылки на строки — нет. Когда меня это смущает, я думаю об этом так, как будто я «замораживаю» столбцы, чтобы моя ячейка всегда ссылалась на столбец A, независимо от того, куда я копирую и вставляю его или перетаскиваю вниз.

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

И наоборот, вы можете сделать строки, но не столбцы абсолютными. Если ввести = A $ 1 + A $ 2 в ячейку A3, а затем скопировать и вставить эту формулу в ячейку B3, вы получите = B $ 1 + B $ 2 .Однако, если вы скопируете и вставите эту формулу в A4, она останется = A $ 1 + A $ 2 , поскольку ссылки на ее строки являются абсолютными.

Еще один быстрый совет перед тем, как вы начнете: на клавиатуре, быстрый способ сделать ячейки абсолютными — нажать F4 во время редактирования в строке формул. Это позволяет вам переключаться между различными типами абсолютных ссылок, если вы нажимаете их несколько раз при вводе формулы. Попытайся!

Я надеюсь, что эта статья сделала вас мастером абсолютных и относительных ссылок.Если вам нужны еще несколько советов по Excel, продолжайте читать, и Magoosh также предлагает Master Excel: от начального до продвинутого, чтобы улучшить свои навыки работы с Excel. Если у вас есть вопросы, задайте их ниже!

О Риче Ринальди

Рич пишет об Excel и PMP для Magoosh. Он ботаник Excel, сдавший экзамен PMP с 4-мя опытными и 1 умеренно-профессиональным (прокляните эту область инициации!). Он получил степень бакалавра наук в области управления операциями в Джорджтаунском университете и степень магистра делового администрирования в области управления некоммерческими организациями в университете Чаминад в Гонолулу.Он работает бизнес-аналитиком и менеджером проектов в общенациональной некоммерческой организации со штаб-квартирой в Вилланове, штат Пенсильвания. Рич и его невеста Хэдли любят путешествовать по миру или играть с Медведем, Чероки и Налани (их кошками).

зачем использовать $ в формуле Excel

При написании формулы Excel $ в ссылках на ячейки сбивает с толку многих пользователей. Но объяснение очень простое. Знак доллара в ссылке на ячейку Excel служит только одной цели — он сообщает Excel, следует ли изменять ссылку при копировании формулы в другие ячейки.И это короткое руководство предоставляет полную информацию об этой замечательной функции.

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

Все вы, наверное, видели знак доллара ($) в формулах Excel и задавались вопросом, что это такое. Действительно, вы можете ссылаться на одну и ту же ячейку четырьмя разными способами, например A1, $ A $ 1, $ A1 и A $ 1.

Знак доллара в ссылке на ячейку Excel влияет только на одно — он указывает Excel, как обрабатывать ссылку, когда формула перемещается или копируется в другие ячейки. Вкратце, использование знака $ перед координатами строки и столбца создает абсолютную ссылку на ячейку, которая не изменится. Без знака $ ссылка будет относительной и изменится.

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

Что такое ссылка на ячейку Excel?

Проще говоря, ссылка на ячейку в Excel — это адрес ячейки. Он сообщает Microsoft Excel, где искать значение, которое вы хотите использовать в формуле.

Например, если вы введете простую формулу = A1 в ячейку C1, Excel извлечет значение из ячейки A1 в ячейку C1:

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

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

Примечание. Помимо ссылочного стиля A1 , где столбцы определяются буквами, а строки — номерами, существует также ссылочный стиль R1C1 , в котором и строки, и столбцы идентифицируются номерами (R1C1 обозначает строку 1, столбец 1).

Поскольку A1 является эталонным стилем по умолчанию в Excel и используется большую часть времени, в этом руководстве мы обсудим только ссылки типа A1.Если кто-то в настоящее время использует стиль R1C1, вы можете отключить его, щелкнув Файл > Параметры > Формулы , а затем сняв флажок R1C1 reference style .

Относительная ссылка на ячейку в Excel (без знака $)

Относительная ссылка в Excel — это адрес ячейки без знака $ в координатах строки и столбца, например A1 .

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

Предположим, у вас есть следующая формула в ячейке B1:

= A1 * 10

Если вы скопируете эту формулу в другую строку в том же столбце, скажем, в ячейку B2, формула будет скорректирована для строки 2 (A2 * 10), потому что Excel предполагает, что вы хотите умножить значение в каждой строке столбца A на 10.

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

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

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

Использование относительной ссылки — Excel — пример формулы

Предположим, у вас есть столбец цен в долларах США (столбец B) на вашем листе, и вы хотите преобразовать их в евро. Зная обменный курс доллара США к евро (0,93 на момент написания), формула для строки 2 так же проста, как = B2 * 0,93 . Обратите внимание, что мы используем относительную ссылку на ячейку Excel без знака доллара.

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

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

Чтобы скопировать формулу вниз по столбцу , наведите указатель мыши на маркер заполнения (маленький квадрат в правом нижнем углу выбранной ячейки). При этом курсор изменится на тонкий черный крестик, и вы удерживаете его и перетаскиваете по ячейкам, которые хотите автоматически заполнить.

Вот и все! Формула копируется в другие ячейки с относительными ссылками, которые правильно настроены для каждой отдельной ячейки. Чтобы убедиться, что значение в каждой ячейке вычисляется правильно, выберите любую из ячеек и просмотрите формулу в строке формул. В этом примере я выбрал ячейку C4 и вижу, что ссылка на ячейку в формуле относится к строке 4, как и должно быть:

Абсолютная ссылка на ячейку Excel (со знаком $)

Абсолютная ссылка в Excel — это адрес ячейки со знаком доллара ($) в координатах строки или столбца, например $ A $ 1 .

Знак доллара фиксирует ссылку на заданную ячейку, так что остается неизменным независимо от того, куда перемещается формула. Другими словами, использование $ в ссылках на ячейки позволяет копировать формулу в Excel без изменения ссылок.

Например, если у вас есть 10 в ячейке A1 и вы используете абсолютную ссылку на ячейку ( $ A $ 1 ), формула = $ A $ 1 + 5 всегда будет возвращать 15, независимо от того, в каких других ячейках эта формула копируется в.С другой стороны, если вы напишете ту же формулу с относительной ссылкой на ячейку ( A1 ), а затем скопируете ее в другие ячейки столбца, для каждой строки будет вычислено другое значение. Следующее изображение демонстрирует разницу:

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

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

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

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

Пример 1. Относительные и абсолютные ссылки на ячейки для вычисления чисел

В нашем предыдущем примере с ценами в долларах США и евро вы можете не захотеть жестко указывать обменный курс в формуле. Вместо этого вы можете ввести это число в какую-либо ячейку, например C1, и исправить ссылку на эту ячейку в формуле, используя знак доллара ($), как показано на следующем снимке экрана:

В этой формуле (B4 * $ C $ 1) существует два типа ссылок на ячейки:

• B4 — относительная ссылка на ячейку , которая настраивается для каждой строки, и
• $ C $ 1 — абсолютная ссылка на ячейку , которая никогда не изменяется независимо от того, куда копируется формула.

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

Пример 2. Относительные и абсолютные ссылки на ячейки для вычисления дат

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

Предположим, у вас есть список дат доставки в столбце B, и вы вводите текущую дату в C1 с помощью функции СЕГОДНЯ ().Вам нужно знать, сколько дней отправляется каждый предмет, и вы можете рассчитать это, используя следующую формулу: = B4- $ C $ 1

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

• Относительный для ячейки с первой датой доставки (B4), поскольку вы хотите, чтобы эта ссылка на ячейку изменялась в зависимости от строки, в которой находится формула.
• Абсолютный для ячейки с сегодняшней датой ($ C $ 1), потому что вы хотите, чтобы эта ссылка на ячейку оставалась постоянной.

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

Ссылка на смешанные ячейки Excel

Ссылка на смешанную ячейку в Excel — это ссылка, в которой фиксирована буква столбца или номер строки. Например, $ A1 и A $ 1 являются смешанными ссылками. Но что означает каждое из них? Все очень просто.

Как вы помните, абсолютная ссылка Excel содержит 2 знака доллара ($), которые блокируют столбец и строку.В смешанной ссылке на ячейку только одна координата является фиксированной (абсолютной), а другая (относительная) будет изменяться в зависимости от относительного положения строки или столбца:

• Абсолютный столбец и относительная строка , например $ A1. Когда формула с этим типом ссылки копируется в другие ячейки, знак $ перед буквой столбца блокирует ссылку на указанный столбец, чтобы она никогда не изменялась. Относительная ссылка на строку без знака доллара зависит от строки, в которую копируется формула.
• Относительный столбец и абсолютная строка , например A $ 1. В этом типе ссылки ссылка на строку не изменится, а ссылка на столбец изменится.

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

Использование смешанной ссылки в Excel — пример формулы

В этом примере мы снова будем использовать нашу таблицу конвертации валют. Но на этот раз мы не будем ограничиваться только конвертацией доллара в евро.Что мы собираемся сделать, так это преобразовать долларовые цены в ряд других валют, используя единую формулу !

Для начала давайте введем коэффициенты конверсии в какую-нибудь строку, скажем, строку 2, как показано на скриншоте ниже. А затем вы пишете только одну формулу для верхней левой ячейки (C5 в этом примере), чтобы рассчитать цену в евро:

= 5 бат * 2 канадских долларов

Где B5 — это цена в долларах в той же строке, а C $ 2 — это обменный курс доллара США к евро.

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

Например, выделим ячейку D7 (в столбце GBP). Здесь мы видим формулу = $ B7 * D $ 2 , которая берет цену в долларах США в B7 и умножает ее на значение в D2, которое представляет собой обменный курс доллар-фунт стерлингов, именно то, что доктор прописал 🙂

А теперь давайте разберемся, как получается, что Excel точно знает, какую цену брать и на какой обменный курс ее умножать.Как вы уже догадались, трюк делают смешанные ссылки на ячейки ($ B5 * C $ 2).

• $ B5 — абсолютный столбец и относительная строка . Здесь вы добавляете знак доллара ($) только перед буквой столбца, чтобы привязать ссылку к столбцу A, поэтому Excel всегда использует исходные цены в долларах США для всех преобразований. Ссылка на строку (без знака $) не заблокирована, потому что вы хотите рассчитать цены для каждой строки отдельно.
• C $ 2 — относительный столбец и абсолютная строка .Поскольку все обменные курсы находятся в строке 2, вы блокируете ссылку на строку, помещая знак доллара ($) перед номером строки. И теперь, независимо от того, в какую строку вы копируете формулу, Excel всегда будет искать обменный курс в строке 2. И поскольку ссылка на столбец является относительной (без знака $), она будет скорректирована для столбца, для которого формула скопировано.

Как создать ссылку на весь столбец или строку в Excel

Когда вы работаете с листом Excel, который имеет переменное количество строк, вы можете обратиться ко всем ячейкам в определенном столбце.Чтобы сослаться на весь столбец, просто дважды введите букву столбца и двоеточие между ними, например A: A .

Ссылка на весь столбец

Как и ссылки на ячейки, ссылка на весь столбец может быть абсолютной и относительной, например:

• Абсолютная ссылка на столбец , , например, $ A: $ A
• Относительная ссылка на столбец, например A: A

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

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

Ссылка на всю строку

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

• Абсолютная ссылка на строку , как $ 1: $ 1
• Относительная ссылка на строку, например 1: 1

Теоретически вы также можете создать смешанную ссылку на весь столбец или смешанную полную ссылку на строку — , как $ A: A или $ 1: 1, соответственно.Я говорю «теоретически», потому что не могу придумать никакого практического применения таких ссылок, хотя пример 4 доказывает, что формулы с такими ссылками работают именно так, как должны.

Пример 1. Ссылка на весь столбец Excel (абсолютная и относительная)

Предположим, у вас есть некоторые числа в столбце B, и вы хотите узнать их общее и среднее значение. Проблема в том, что новые строки добавляются в таблицу каждую неделю, поэтому писать обычную формулу SUM () или AVERAGE () для фиксированного диапазона ячеек — не лучший вариант.Вместо этого вы можете сослаться на весь столбец B:

.

= СУММ ($ B: $ B) — используйте знак доллара ($), чтобы создать абсолютную ссылку на весь столбец , которая фиксирует формулу в столбце B.

= СУММ (B: B) — напишите формулу без $, чтобы сделать относительную ссылку на весь столбец , которая будет изменяться при копировании формулы в другие столбцы.

Наконечник. При написании формулы щелкните букву столбца, чтобы добавить в формулу ссылку на весь столбец.Как и в случае со ссылками на ячейки, Excel по умолчанию вставляет относительную ссылку (без знака $):

Таким же образом мы пишем формулу для расчета средней цены во всем столбце B:

= СРЕДНИЙ (B: B)

В этом примере мы используем относительную ссылку на весь столбец, поэтому наша формула корректируется правильно, когда мы копируем ее в другие столбцы:

Примечание. При использовании ссылки на весь столбец в формулах Excel никогда не вводите формулу в любом месте одного столбца.Например, может показаться хорошей идеей ввести формулу = СУММ (B: B) в одну из самых пустых нижних ячеек в столбце B, чтобы получить результат в конце того же столбца. Не делай этого! Это создаст так называемую круговую ссылку , и формула вернет 0.

Пример 2. Ссылка на всю строку Excel (абсолютная и относительная)

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

= СРЕДНИЙ (2 доллара: 2 доллара) — абсолютная ссылка на всю строку привязана к определенной строке с помощью знака доллара ($).

= СРЕДНЕЕ (2: 2) — относительная ссылка на всю строку изменится при копировании формулы в другие строки.

В этом примере нам нужна относительная ссылка на всю строку, потому что у нас есть 3 строки данных, и мы хотим вычислить среднее значение в каждой строке, скопировав ту же формулу:

Пример 3.Как сослаться на весь столбец, исключая первые несколько строк

Это очень актуальная проблема, потому что довольно часто первые несколько строк на листе содержат некоторые вводные предложения или пояснительную информацию, и вы не хотите включать их в свои вычисления. К сожалению, Excel не допускает ссылок типа B5: B, которые включали бы все строки в столбце B, начиная со строки 5. Если вы попытаетесь добавить такую ​​ссылку, ваша формула, скорее всего, вернет ошибку #NAME.

Вместо этого вы можете указать строк максимум , чтобы ваша ссылка включала все возможные строки в данном столбце.В Excel 2016, 2013, 2010 и 2007 максимум составляет 1 048 576 строк и 16 384 столбца. Более ранние версии Excel имеют максимум 65 536 строк и 256 столбцов.

Итак, чтобы найти среднее значение для каждого столбца цен в приведенной ниже таблице (столбцы с B по D), вы вводите следующую формулу в ячейку F2, а затем копируете ее в ячейки G2 и h3:

= СРЕДНИЙ (B5: B1048576)

Если вы используете функцию СУММ, вы также можете вычесть строки, которые хотите исключить:

= СУММ (B: B) -SUM (B1: B4)

Пример 4.Использование смешанной ссылки на весь столбец в Excel

Как я упоминал несколькими абзацами ранее, вы также можете сделать смешанную ссылку на весь столбец или целую строку в Excel:

• Смешанная ссылка на столбец, например $ A: A
• Смешанная ссылка на строку, например $ 1: 1

Теперь давайте посмотрим, что произойдет, если вы скопируете формулу с такими ссылками в другие ячейки. Предположим, вы ввели формулу = СУММ ($ B: B) в некоторую ячейку, F2 в этом примере. Когда вы копируете формулу в соседнюю правую ячейку (G2), она изменяется на = SUM ($ B: C) , потому что первая B фиксируется знаком $, а вторая — нет.В результате формула сложит все числа в столбцах B и C. Не уверен, имеет ли это какое-либо практическое значение, но вы можете узнать, как это работает:

Предупреждение! Не используйте на листе слишком много ссылок на столбцы / строки целиком, поскольку они могут замедлить работу Excel.

Как переключаться между абсолютными, относительными и смешанными ссылками (клавиша F4)

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

1. Выберите ячейку с формулой.
2. Войдите в режим редактирования, нажав клавишу F2, или дважды щелкните ячейку.
3. Выберите ссылку на ячейку, которую вы хотите изменить.
4. Нажмите F4 для переключения между четырьмя типами ссылок на ячейки.

Если вы выбрали относительную ссылку на ячейку без знака $, например A1, многократное нажатие клавиши F4 переключает между абсолютной ссылкой с обоими знаками доллара, такими как $ A $ 1, абсолютной строкой A $ 1, абсолютным столбцом $ A1, а затем вернуться к относительной ссылке A1.

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

Надеюсь, теперь вы полностью понимаете, что такое относительные и абсолютные ссылки на ячейки, и формула Excel со знаками $ больше не является загадкой. В следующих нескольких статьях мы продолжим изучение различных аспектов ссылок на ячейки Excel, таких как ссылка на другой рабочий лист, трехмерная ссылка, структурированная ссылка, циклическая ссылка и т. Д.А пока я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Вас также может заинтересовать

ссылок на ячейки в Excel — Easy Excel Tutorial

Относительная ссылка | Абсолютная ссылка | Смешанный эталон

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

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

По умолчанию Excel использует относительных ссылок .См. Формулу в ячейке D2 ниже. Ячейка D2 ссылается на ячейки B2 и C2 (указывает на них). Обе ссылки относительны.

1. Выберите ячейку D2, щелкните в правом нижнем углу ячейки D2 и перетащите ее в ячейку D5.

Ячейка D3 ссылается на ячейки B3 и C3. Ячейка D4 ссылается на ячейки B4 и C4. Ячейка D5 ссылается на ячейки B5 и C5. Другими словами: каждая ячейка ссылается на двух своих соседей слева.

Абсолютная ссылка

См. Формулу в ячейке E3 ниже.

1. Чтобы создать абсолютную ссылку на ячейку h4, поместите символ $ перед буквой столбца и номером строки ($ H $ 3) в формуле ячейки E3.

2. Теперь мы можем быстро перетащить эту формулу в другие ячейки.

Ссылка на ячейку h4 фиксирована (когда мы перетаскиваем формулу вниз и поперек). В результате рассчитываются правильные значения длины и ширины в дюймах. Посетите нашу страницу об абсолютной ссылке, чтобы узнать больше об этом типе ссылки.

Смешанный эталон

Иногда нам нужна комбинация относительной и абсолютной ссылки ( смешанная ссылка ).

1. См. Формулу в ячейке F2 ниже.

2. Мы хотим быстро скопировать эту формулу в другие ячейки. Перетащите ячейку F2 через одну ячейку и посмотрите на формулу в ячейке G2.

Вы видите, что происходит? Ссылка на цену должна быть фиксированной ссылкой в столбце B .Решение: поместите символ $ перед буквой столбца ($ B2) в формуле ячейки F2. Аналогичным образом, когда мы перетаскиваем ячейку F2 вниз, ссылка на сокращение должна быть фиксированной ссылкой на строку 6 . Решение: поместите символ $ перед номером строки (B $ 6) в формуле ячейки F2.

результат:

Примечание: мы не помещаем символ $ перед номером строки $ B2 (таким образом мы позволяем ссылке изменяться с $ B2 (джинсы) на $ B3 (рубашки) при перетаскивании формулы вниз).Аналогичным образом мы не помещаем символ $ перед буквой столбца B $ 6 (таким образом мы позволяем ссылке изменяться с B $ 6 (январь) на C $ 6 (февраль) и D $ 6 (март), когда перетаскиваем формулу поперек).

3. Теперь мы можем быстро перетащить эту формулу в другие ячейки.

Ссылки на столбец B и строку 6 являются фиксированными.

Absolute Reference Excel — Руководство для начинающих

Что такое абсолютная ссылка?

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

Загрузите бесплатный файл практики

Используйте этот бесплатный файл Excel, чтобы практиковать абсолютные ссылки вместе с учебным пособием.

Например, на изображении ниже все сотрудники получат одинаковую сумму бонусных выплат в размере 1500 долларов США. Сумма в 1500 долларов является постоянной в этой ситуации, и абсолютная ссылка может использоваться, чтобы помочь рассчитать общую выплату заработной платы (цифра, которая различается для каждого человека) плюс бонусные выплаты.

В Excel все ссылки по умолчанию являются относительными. Чтобы применить абсолютную ссылку в Excel, в формулу Excel необходимо добавить знак доллара ($), т. Е. = $ C $ 1, как показано в примере выше.

Без знака доллара ($) в формуле Excel естественно интерпретирует адрес ячейки как относительную ссылку, где точка отсчета изменяется при перемещении относительных координат строки и столбца.

Идея абсолютной ссылки на ячейку состоит в том, чтобы содержать определенную константу ячейки, поэтому значение остается неизменным при копировании в другие ячейки.Функция абсолютной справки Excel — незаменимый инструмент для экономии времени и усилий при работе с электронными таблицами.

Краткое резюме

Как сделать абсолютную ссылку в Excel

Вот шаги по созданию базовой абсолютной ссылки в Excel — $ A $ 1:

1. Выберите ячейку, в которой вы хотите создать абсолютную ссылку. Ячейка A1 в этом примере:

2. В формуле ячейки A1 введите «=» (знак равенства), а затем выберите точку отсчета — ячейку C1.

3. В той же формуле либо вручную добавьте два знака доллара (SHIFT + 4) перед координатами строки и столбца, либо нажмите клавишу F4 в качестве ярлыка.
4. Нажмите ENTER, и теперь ячейка A1 имеет значение ячейки C1 в качестве фиксированной точки отсчета.

Примеры абсолютной ссылки

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

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

Давайте посмотрим на пример ниже:

Компания канцелярских товаров из США нашла нового покупателя из Германии. Им нужно разработать предложение для нового партнера. Учитывая, что существуют различия в валютах, они хотели бы включить стоимость единицы в евро, чтобы получить полную картину затрат.Это означает, что все эти три единицы стоимости в долларах США необходимо умножить на обменный курс евро-доллар — стоимости единицы (долл. США) * обменный курс евро-доллар .

Есть три единицы стоимости и один обменный курс. Обменный курс евро (ячейка B3) должен оставаться постоянным при применении формул в столбце F для удельных затрат в евро.

Для начала узнаем стоимость Блокнота А в евро:

1. Введите: «= E2 * B3»

2. Вручную вставьте $ перед координатами строки и столбца или нажмите F4, чтобы создать абсолютную ссылку, затем нажмите ENTER.

3. Чтобы узнать другие удельные затраты в евро, перетащите вниз ячейку F2, чтобы применить формулы к F3 и F4.

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

Он обеспечивает быстрое решение имеющегося сценария и сводит к минимуму ошибки, выполняя вручную умножение для каждой ячейки.

Если в формулах не было абсолютной ссылки, Excel вернет 0 долларов в качестве стоимости единицы, когда мы перетаскиваем ячейку F2 вниз.Поскольку контрольная точка станет относительной и изменится в разных ячейках, а других значений в ячейках B4 и B5 нет.

Абсолютная ссылка против относительной ссылки в Excel

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

Абсолютная ссылка Excel

Это относится к фиксированной точке отсчета, является константой и предполагает использование знака доллара $ в формуле (т.е., все должны получить одинаковую выплату бонуса, поэтому сумма в 1500 долларов в данной ситуации является постоянной).

Относительная ссылка Excel

Это относится к относительной точке отсчета, постоянно меняется, и в формуле отсутствует знак доллара $ (то есть, когда каждые единиц цены и qty являются разностными переменными, в расчетах нет константы).

Быстрая подсказка

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

Резюме

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

Знак доллара $ используется для создания абсолютной ссылки. Без него это относительная ссылка, ни одна ячейка не «заблокирована».

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

Чтобы узнать больше об абсолютных ссылках и других важных функциях Excel, попробуйте наши курсы Basic и Advanced Excel сегодня. Или начните изучать основы работы с Excel с помощью нашего бесплатного курса Excel за час.

Изучайте Excel бесплатно

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

Начать бесплатный курс

Агнес Ло

Агнес — профессиональный клиент в сфере розничной торговли предметами роскоши.В свободное время она увлекается йогой и активным отдыхом.

Как изменить относительную ячейку Excel на абсолютную ячейку?

Обновлено: 30 декабря 2019 г., компания Computer Hope

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

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

1. Откройте Microsoft Excel.
2. Выделите ячейку, содержащую формулу, которую нужно изменить на абсолютную или относительную ссылку.
3. Щелкните поле формулы (показано ниже) или выделите формулу и нажмите клавишу F4 для переключения между абсолютной и относительной ссылкой на ячейку.

Кончик

Вы также можете выделить части формулы и нажать F4 , чтобы получить частичную абсолютную ссылку.

Если вы хотите писать вручную или создать абсолютную ссылку, используйте в формуле символ «$». Ниже приведен базовый пример, демонстрирующий разницу между базовой относительной и абсолютной ссылкой.

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

 = СУММ (A1: A3) 

Приведенная выше основная формула должна быть знакома большинству пользователей; он складывает значения ячеек с A1 по A3.

Абсолютная ссылка

 = СУММ (1 австралийский доллар: 3 австралийского доллара) 

Чтобы заменить указанную выше относительную ссылку на абсолютную, добавьте символ «$» перед столбцом и строкой.

Частичная абсолютная ссылка

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

 = СУММ ($ A1: $ A3) 

В этом первом примере только столбец (A) является абсолютной ссылкой, а строка является относительной ссылкой. Когда вы копируете эту формулу в другой столбец и строку, она по-прежнему ссылается на столбец A, но меняет строку в зависимости от того, куда она скопирована.