Абсолютные и относительные ссылки в эксель: Excel относительная ссылка

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

Опубликовано: 03.03.2020Автор: Артём Чёрный

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

Содержание

1. Абсолютные и относительные ссылки в Excel
2. Как создать абсолютную ссылку
3. Про ошибки в относительных ссылках
4. Смешанные ссылки

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

Предположим, у нас есть таблица с несколькими позициями товаров. Здесь указано количество некого товара и цена за одну единицу. Давайте для примера посчитаем, сколько стоит весь товар, находящийся в ассортименте условного магазина. Для этого нужно умножить количество товара на цену за единицу. Формула в Excel будет выглядеть так: «=ячейка_с_количеством*ячейка_с_ценой». Например, у нас это будут ячейки D2 и E2 соответственно: «=B2*C2». Введя и посчитав данную формулу мы получили формулу с абсолютной ссылкой.

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

Как создать абсолютную ссылку

Выше был рассмотрен очень распространённый пример относительных и абсолютных ссылок. Однако бывает так, что пользователю нужно сделать так, чтобы какая-то из-за ссылок в формуле всегда оставалась абсолютной. Даже при автоматической вставке. Для этого нужно поставить значок $ перед символом и номером ячейки. Пример такой формулы: «=A1/$B$1». В таком случае содержимое ячеек в столбце A всегда будет делиться на содержимое ячейки B1. Даже при автоматическом заполнении.

Про ошибки в относительных ссылках

Иногда при использовании относительных ссылок вместо подсчёта возникает ошибка, когда написано «#ДЕЛ/0!». Это случается, когда в одной из указанных в формуле ячейках нет никакого числа. Например, пропишем такую формулу: «=A1/B1». Столбец A заполним какими-то числами до 10-го номера, а в столбце B заполним только одну ячейку – первую.

Читайте также:
Считаем среднее значение в Microsoft Excel
Сортировка и фильтрация данных в Microsoft Excel
Инструмент «Поиск решения» в Excel
Как закрепить шапку таблицы в Excel

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

• Заполнить столбец B числами до 10-й ячейки;
• Установить абсолютную ссылку для ячейки B1.

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

Абсолютной ссылке можно придать смешанный вид, просто правильно расставив символ $. Например:

• $A1. Ссылка является абсолютной столбца A, но при этом может быть относительна номеров ячеек в этом столбце;
• A$1. Ссылка является абсолютной номера ячейки, но может быть относительно столбца этого номера.

К счастью, смешанные ссылки применяются редко и в специфических задачах.

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

Урок на тему «Относительные и абсолютные ссылки в Excel»

Цели урока:

Обучающие:

• выработать первоначальные навыки решение задач на использование абсолютных и относительных ссылок;

 Развивающие:

• развивать интерес к предмету, способствовать развитию памяти, внимания, выполнять расчет, работе на компьютере с использованием Excel;

Воспитательные:

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

Тип урока: лабораторно-практическое занятие

Методы и методические приемы обучения: урок с использованием цифровых образовательных ресурсов, объяснительно-демонстрационный

Наглядные пособия и технические средства обучения:

 ПК, ноутбук, проектор, интерактивная доска, презентация «Относительные и абсолютные ссылки», программа MS Excel 2007, ЦОР, раздаточные материалы.

 

Ход урока:

1. Организационная часть. Проверка готовности обучающихся к уроку, наличия учащихся, размещение на рабочих местах.

Демонстрация темы урока, знакомство с целями урока. Слайд 1.

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

2. Опрос домашнего задания. Устный опрос. Слайд 2-5

1. MS Excel предназначен для… //создания таблиц, вычислений по различным формулам,сортировки данных по параметрам, построения диаграмм по табличным данным.
2. Из чего состоит электронная таблица? //Строк и столбцов, ячеек, листов
3. Какие форматы данных вы знаете? //Числовой, Текстовый, Денежный, Финансовый, Экспоненциальный, Дата, Время, Дробный, Процентный.
4. Адрес ячейки состоит из. //комбинации букв столбца и цифр строки.
5. Какие последовательности можно создавать с помощью автозаполнения? //создавать ряды чисел, дней, дат, кварталов, выполнять вычисления и т.д.

Задание №1 с использованием цифровых образовательных ресурсов, интерактивной доски. Слайд 6. 

Задание №2 с использованием цифровых образовательных ресурсов и интерактивной доски.

Слайд 7.

Задание №3 с использованием цифровых образовательных ресурсов и интерактивной доски. Слайд 8. 

3. Изложение нового теоретического материала. Объяснение нового материала с помощью интерактивной доски, презентации. Слайд 9.

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

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

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

Ссылки могут быть абсолютные, относительные и смешанные.

Объяснение и демонстрация относительной ссылки на примере. Слайд 10-11. 

По умолчанию для указания адресов ячеек создаются относительные

ссылки. Это означает, что ссылки на ячейки изменяются при перемещении формулы на новое место. Относительная ссылка указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула. После копирования формулы относительные ссылки будут указывать на другие ячейки так, чтобы сохранить прежние отношения в соответствии с новым местоположением. Например формула =A3+B3, хранящаяся в ячейке С3, при перемещении в ячейку С4 примет вид: =A4+B4.

Запись основных понятий. Слайд 12.

Объяснение и демонстрация абсолютной ссылки на примере. Слайд 13.

В тех случаях, когда ссылки не должны изменяться при копировании формулы, следует использовать абсолютные ссылки. Абсолютные ссылки обозначаются знаком доллара, например $A$4. При вводе абсолютных ссылок удобно использовать клавишу [F4] после ввода ссылки.

Запись основных понятий. Слайд 14. 

Ссылки вида $A4 или A$4 называются смешанными.

Объяснение и демонстрация смешанной ссылки на примере, запись основных понятий. Слайд 15-17.  

4. Практическая работа. Самостоятельная работа учащихся

Задание 1. Используя абсолютные ссылки вычислите сколько будет стоить 4 системные платы.

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

Задание 3. Заполните столбцы «Наименование товара» и «Цена». Стоимость товаров со скидкой подсчитайте по формуле. Меняя скидку, посмотрите как меняется стоимость. Введите размер скидки 100% и убедитесь, что все значения стоимости со скидкой имеют нулевые значения, т.е. вы правильно составили формулу.

5.  Подведение итогов. Проверка практических работ, оценка

Вопросы:

1. Проанализируйте в каких примерах можно было использовать смешанные ссылки?
2. Когда используется абсолютная ссылка?
3. Как ставится абсолютная ссылка?
4. Когда используется относительная ссылка?
5. Как ставится относительная ссылка?
6. Чем отличаются относительная ссылка от абсолютной?

6. Домашнее задание. Адресация в Excel

 

Просмотр содержимого документа
«Абсолютные и относительные ссылки — открытый урок»

Урок № 17

Тема урока: Относительная и абсолютная ссылка в Excel

Цели урока:

Обучающие:

Развивающие:

• развивать интерес к предмету, способствовать развитию памяти, внимания, выполнять расчет, работе на компьютере с использованием Excel;

Воспитательные:

• воспитание познавательной потребности, интереса к предмету;

• прививание навыков самостоятельной работы;

• привитие нравственных качеств: ответственность, дисциплинированность, аккуратность, собранность.

Тип урока: лабораторно-практическое занятие

Методы и методические приемы обучения: урок с использованием цифровых образовательных ресурсов, объяснительно-демонстрационный

Наглядные пособия и технические средства обучения: ПК, ноутбук, проектор, интерактивная доска, презентация «Относительные и абсолютные ссылки», программа MS Excel 2007, ЦОР, раздаточные материалы.

Ход урока:

1. Организационная часть. Проверка готовности обучающихся к уроку, наличия учащихся, размещение на рабочих местах.

Демонстрация темы урока, знакомство с целями урока. Слайд 1.

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

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

2. Опрос домашнего задания. Устный опрос. Слайд 2-5

1. MS Excel предназначен для… //создания таблиц, вычислений по различным формулам,сортировки данных по параметрам, построения диаграмм по табличным данным.

2. Из чего состоит электронная таблица? //Строк и столбцов, ячеек, листов

3. Какие форматы данных вы знаете? //Числовой, Текстовый, Денежный, Финансовый, Экспоненциальный, Дата, Время, Дробный, Процентный.

4. Адрес ячейки состоит из.. //комбинации букв столбца и цифр строки.

5. Какие последовательности можно создавать с помощью автозаполнения? //создавать ряды чисел, дней, дат, кварталов, выполнять вычисления и т.д.

Задание №1 с использованием цифровых образовательных ресурсов, интерактивной доски. Слайд 6.

Задание №2 с использованием цифровых образовательных ресурсов и интерактивной доски. Слайд 7.

Задание №3 с использованием цифровых образовательных ресурсов и интерактивной доски. Слайд 8.

3. Изложение нового теоретического материала. Объяснение нового материала с помощью интерактивной доски, презентации. Слайд 9.

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

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

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

Ссылки могут быть абсолютные, относительные и смешанные.

Объяснение и демонстрация относительной ссылки на примере. Слайд 10-11.

По умолчанию для указания адресов ячеек создаются относительные ссылки. Это означает, что ссылки на ячейки изменяются при перемещении формулы на новое место. Относительная ссылка указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула. После копирования формулы относительные ссылки будут указывать на другие ячейки так, чтобы сохранить прежние отношения в соответствии с новым местоположением. Например формула =A3+B3, хранящаяся в ячейке С3, при перемещении в ячейку С4 примет вид: =A4+B4.

Запись основных понятий. Слайд 12.

Объяснение и демонстрация абсолютной ссылки на примере. Слайд 13.

В тех случаях, когда ссылки не должны изменяться при копировании формулы, следует использовать абсолютные ссылки. Абсолютные ссылки обозначаются знаком доллара, например $A$4. При вводе абсолютных ссылок удобно использовать клавишу [F4] после ввода ссылки.

Запись основных понятий. Слайд 14.

Ссылки вида $A4 или A$4 называются смешанными.

Объяснение и демонстрация смешанной ссылки на примере, запись основных понятий. Слайд 15-17.

4. Практическая работа. Самостоятельная работа учащихся

Задание 1. Используя абсолютные ссылки вычислите сколько будет стоить 4 системные платы.

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

Задание 3. Заполните столбцы «Наименование товара» и «Цена». Стоимость товаров со скидкой подсчитайте по формуле. Меняя скидку, посмотрите как меняется стоимость. Введите размер скидки 100% и убедитесь, что все значения стоимости со скидкой имеют нулевые значения, т.е. вы правильно составили формулу.

5. Подведение итогов. Проверка практических работ, оценка

Вопросы:

1. Проанализируйте в каких примерах можно было использовать смешанные ссылки?

2. Когда используется абсолютная ссылка?

3. Как ставится абсолютная ссылка?

4. Когда используется относительная ссылка?

5. Как ставится относительная ссылка?

6. Чем отличаются относительная ссылка от абсолютной?

6. Домашнее задание. Адресация в Excel

Просмотр содержимого презентации
«Абсолютные и относительные ссылки»

Тема урока:

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

В Excel

Предмет: Информатика

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

MS Excel предназначен для …

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

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

Из чего состоит электронная таблица?

Строк и столбцов, ячеек, листов

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

Какие форматы данных вы знаете?

Числовой, Текстовый, Денежный, Финансовый, Экспоненциальный, Дата, Время, Дробный, Процентный

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

Адрес ячейки состоит из..

комбинации букв столбца и цифр строки

Задание 1. Игра «найди»

Задание 2. Перетаскивание

Задание 3.

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

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

Ссылка на ячейку может быть относительной и абсолютной

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

A1

Абсолютная

А 2

$A$1

$A$1

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

автозаполнение формулы вниз

Пример 1

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

Пример 2

автозаполнение формулы вправо

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

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

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

изменяется

при автозаполнении

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

автозаполнение формулы вниз

Пример 3

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

Используются в формулах для указания фиксированного адреса.

Для фиксации адреса используется «заморозка»: ставиться знак $ перед буквой и перед числом имени ячейки ( $A$1 ) .

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

не изменяется

при автозаполнении

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

автозаполнение формулы вниз

Пример 4

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

Пример 4

автозаполнение формулы вправо

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

Ссылки вида $ A 4 или A $4 называются смешанными ссылками

Если знак $ располагается перед именем столбца ( $ A 4) при автозаполнении не изменяется имя столбца

Если знак $ располагается перед именем строки ( A $4) при автозаполнении не изменяется имя строки

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

• Вычислите сколько будет стоить 4 системные платы

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

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

• Заполните столбцы «Наименование товара» и «Цена».
• Стоимость товаров со скидкой подсчитайте по формуле.
• Меняя скидку, посмотрите как меняется стоимость.
• Введите размер скидки 100% и убедитесь, что все значения стоимости со скидкой имеют нулевые значения, т.е. вы правильно составили формулу.

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

Как создать относительный путь к файлу с помощью Power Query в Excel?

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

Проблема с абсолютным путем

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

Функции Excel, используемые при преобразовании абсолютного пути в относительный путь 

Прежде чем двигаться дальше, нам необходимо получить четкое представление обо всех функциях Excel, которые будут использоваться для преобразования абсолютного пути в относительный путь. Обычно используются три функции: =CELL(), =LEFT(), =FIND(), для создания необходимой формулы: 

Функция ячейки 

Синтаксис: =CELL(тип_информации, ссылка)

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

• 9.0003 Аргумент 1: Тип_информации — первый аргумент функции ячейки. Тип информации, которую вы хотите найти для указанной ячейки. Например, «filepath» предоставляет абсолютный путь для текущей ячейки.
• Аргумент 2: Для какой ячейки вы хотите извлечь информацию. Ссылка на ячейку может быть абсолютной или относительной.

Примечание: Файл excel должен быть сохранен в какой-то папке, тогда будет отображаться только абсолютный путь, иначе он покажет пустую возвращенную строку. Это одна из распространенных ошибок, с которой сталкиваются пользователи при работе с функцией =CELL(info_type, reference).

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

Шаг 1: Введите = CELL («имя файла», $A$1) , в ячейке B2 , где «имя файла» предоставляет абсолютную ссылку на файл, а $ A$1 — это ссылка на ячейку A1 .

 

Шаг 2: Нажмите Введите . Абсолютный путь к текущему файлу Excel отображается в ячейке 9.0003 B2 т. е. D:\geeksforgeeks\folder1\folder2\[geeksample.xlsx]Sheet1 .

 

Шаг 3: Путь до скобки — это абсолютный путь к этому файлу Excel. Текст внутри квадратных скобок — это имя рабочей книги и, наконец, имя рабочего листа.

 

Функция поиска 

Синтаксис: =ПОИСК(найти_текст, внутри_текста, [начальный_номер])

Функция находит первый начальный индекс местонахождения найденного текста в данной строке. Индексация 1 на основе . Например, если вам дана строка «geeksforgeeks» , и вы хотите найти позицию «ks» в вашей заданной строке, то ответ, возвращаемый функцией =FIND() , будет 4 . В функции поиска есть три аргумента, но для преобразования абсолютного пути в относительный нам потребуются только первых двух аргументов.

• Аргумент 1: Find_text — первый аргумент функции поиска. find_text — это строка, которую необходимо найти в заданной строке. Первое вхождение find_text печатается с помощью функции =FIND().
• Аргумент 2: Within_text — второй аргумент функции поиска. внутренний_текст — это исходная строка, в которой выполняется поиск искомого текста.
• Аргумент 3: Это необязательный аргумент. Он сообщает, с какого индекса вы должны начать поиск в пределах_текста.

Например, вы недавно нашли абсолютный путь к текущему файлу excel, наша задача найти индекс «[» (квадратная скобка) в заданной строке, т.е. «D:\geeksforgeeks\folder1\folder2 \[geeksample.xlsx]Лист1». Ниже приведены шаги:

Шаг 1: Введите = НАЙТИ («[», «D:\geeksforgeeks\folder1\folder2\[geeksample.xlsx]Sheet1»), в ячейке B2 , где Аргумент1 — это «[» , а Аргумент2 — это «D:\geeksforgeeks\folder1\folder2\[geeksample.xlsx]Sheet1». Нажмите Введите .

 

Шаг 2: Мы видим 34 в ячейке B2 . Это связано с тем, что «[» появляется в 34-м индексе (индексация на основе 1) в исходной строке.

 

Функция Left 

Синтаксис: =LEFT(текст, [число_символов])

Функция возвращает префиксную подстроку строки в соответствии с указанным пользователем количеством символов. Индексация 1 на основе . Например, если вам дана строка «geeksforgeeks» , и вы хотите найти первые четыре (4) символа в данной строке, то мы можем использовать функцию =LEFT() для достижения этой цели. , функция вернет подстроку префикса «geek» . В левой функции есть два аргумента.

• Аргумент 1: Первым аргументом является строка text . Текстовая строка — это строка, для которой должна быть возвращена подстрока префикса.
• Аргумент 2: Второй аргумент: num_chars . num_chars — это количество символов , которое вы хотите от начала текстовой строки.

Например, вы недавно нашли абсолютный путь к текущему рабочему листу, а также нашли индекс квадратной скобки «[» т. скобка), что означает, что количество символов должно быть 33 . Ниже приведены шаги: 

Шаг 1: Введите =LEFT(«D:\geeksforgeeks\folder1\folder2\[geeksample.xlsx]Sheet1», 33) , в ячейке B2 , где аргумент1 — исходная строка, для которой должна быть возвращена префиксная подстрока, а вторым аргументом является количество символов, для которых должна быть возвращена данная данная строка.

 

Шаг 2: Нажмите Введите . Все первые 33 символов появятся в ячейке B2 , то есть «D:\geeksforgeeks\folder1\folder2\» .

 

Преобразование абсолютного пути в относительный в Power Query

Теперь вы знаете все, как преобразовать абсолютный путь в относительный путь в Power Query. Мы возьмем тот же пример и расположение файла, что и мы, для понимания функций. Абсолютный путь к текущему файлу: «D:\geeksforgeeks\folder1\folder2\housing.csv». Необходимо преобразовать в относительный путь. Ниже приведены шаги: 

Шаг 1: Поскольку вы разбираетесь в сложной теме запроса мощности, мы предполагаем, что вы знаете, как получить данные из файла CSV или xlsx в запросе мощности. Импортирована таблица с текущим именем листа «жилье» , используя Get Data .

 

Шаг 2: Теперь в правой части экрана появится набор данных о жилье, который вы импортировали. Дважды щелкните по нему , и запустится редактор запросов Power. Основная цель шага 1 и шага 2 — открыть наш редактор мощных запросов.

 

Шаг 3: Открывается редактор Power Query . На вкладке Home в разделе Query щелкните Advanced Editor .

 

Шаг 4: Откроется расширенный редактор. Вы можете заметить, что M-код написан на картинке, показанной ниже, а абсолютный путь для текущего файла CSV — «D:\geeksforgeeks\folder1\folder2\housing.csv» . Наша задача — преобразовать этот абсолютный путь в относительный путь.

 

Шаг 5: Закройте все окна и вернитесь к рабочему листу «жилье» . Теперь создайте новый рабочий лист с именем «setUp» . Ячейка B2 имеет значение FilePath .

 

Шаг 6: Теперь самый важный шаг. Теперь, если вы поняли приведенные примеры при объяснении различных функций, таких как =CELL(), =FIND(), =LEFT(), , то это будет очень простой шаг для вас. Написана формула: =ЛЕВАЯ(ЯЧЕЙКА(«имя файла», $A$1), НАЙТИ(«[«, ЯЧЕЙКА(«имя файла», $A$1)) – 1) . Формула просто извлекает текст, написанный перед квадратной скобкой «[» , в абсолютный путь, т. е. от «D:\geeksforgeeks\folder1\folder2\[geeksample.xlsx]setUp» до «D:\geeksforgeeks \папка1\папка2\» .

 

Шаг 7:  Нажмите Введите . «D:\geeksforgeeks\folder1\folder2\» появляется в ячейке C2 .

 

Шаг 8: Измените имя ячейки, C3 на Filepath .

 

Шаг 9: Теперь повторите шаги 1, 2, и 3 . Это снова откроет наш расширенный редактор . Теперь добавьте эту единственную строку M-кода в расширенный редактор , «Filepath = Excel.CurrentWorkbook(){[Name=»Filepath»]}[Content]{0}[Column1]», . Этот код просто хранит ссылку ячейки C3(Filepath) в переменной «Filepath» .

 

Шаг 10: Теперь перейдите ко второй строке M-кода , где некоторое содержимое файла хранится в переменной «Источник» . Внутри функции File.Contents измените «D:\geeksforgeeks\folder1\folder2\housing.csv» на «Путь к файлу и «housing.csv» «». Закройте все окна и вернитесь обратно в «корпус» , это преобразует ваш абсолютный путь в относительный путь текущих добавленных данных на вашем листе.

 

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

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

/en/excel2013/complex-formulas/content/ абсолютное . Относительные и абсолютные ссылки ведут себя по-разному при копировании и заполнении других ячеек. Относительные ссылки изменить , когда формула копируется в другую ячейку. Абсолютные ссылки, с другой стороны, остаются постоянными , независимо от того, куда они копируются.

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

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

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

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

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

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

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

2. Введите формулу для расчета нужного значения. В нашем примере мы введем =B2*C2 .

   Ввод формулы

3. Нажмите Введите на клавиатуре. Формула будет вычислено , и результат отобразится в ячейке.
4. Найдите маркер заполнения в правом нижнем углу нужной ячейки. В нашем примере мы найдем дескриптор заполнения для ячейки D2 .

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

5. Нажмите, удерживайте и перетащите маркер заполнения по ячейкам, которые вы хотите заполнить. В нашем примере мы выберем ячейки D3:D12 .

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

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

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

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

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

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

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

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

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

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

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

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

1. Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку Д3 .

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

2. Введите формулу для расчета нужного значения. В нашем примере мы введем = (B3*C3)*$E$1 .

   Ввод формулы
   

3. Нажмите Введите на клавиатуре. Формула рассчитает, и результат отобразится в ячейке.
4. Найдите маркер заполнения в правом нижнем углу нужной ячейки. В нашем примере мы найдем дескриптор заполнения для ячейки 9.0003 Д3 .

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

5. Нажмите, удерживайте и перетащите маркер заполнения на ячейки, которые вы хотите заполнить: ячейки D4:D13 в нашем примере.

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

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

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

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

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

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

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

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

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

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

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

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

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

   Ячейка E14

2. Перейдите к нужному рабочий лист . В нашем примере мы выберем рабочий лист Catering Invoice .

   Переход к Листу2

3. Появится выбранный рабочий лист .
4. Найдите и выберите ячейку , в которой должно отображаться значение. В нашем примере мы выберем ячейку B2 .

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

5. Введите знак равенства (=) , лист имя , за которым следует восклицательный знак ( ! ) и адрес ячейки . В нашем примере мы введем =’Порядок меню’!E14 .

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

6. Нажмите Введите на клавиатуре. Появится значение указанной ячейки. Если значение ячейки E14 изменится на рабочем листе заказа меню, оно будет автоматически обновлено на рабочем листе счета за питание.

   Ссылочная ячейка

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

Если вы введете имя листа неправильно, #ССЫЛКА! В ячейке появится ошибка . В нашем примере ниже мы опечатались в имени рабочего листа. Нажмите кнопку Ошибка и выберите нужный вариант из раскрывающегося меню изменить или игнорировать ошибку.

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

Вызов!

1. Откройте существующую книгу Excel. Если вы хотите, вы можете использовать нашу рабочую тетрадь.
2. Создайте формулу, использующую относительную ссылку . Если вы используете пример, используйте маркер заполнения , чтобы заполнить формулу в ячейках с E4 по E14 . Дважды щелкните ячейку, чтобы увидеть скопированную формулу и относительные ссылки на ячейки.
3. Создайте формулу, использующую абсолютную ссылку . Если вы используете пример, исправьте формулу в ячейке D4 , чтобы она ссылалась только на налоговую ставку в ячейке E2 как на абсолютную ссылку , а затем используйте маркер заполнения, чтобы заполнить формулу из ячеек D4 до .