Примеры функции адрес для получения адреса ячейки листа excel

Создание абсолютной ссылки

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

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

После того, как формула введена, просто ставим в ячейке, или в строке формул, перед координатами столбца и строки ячейки, на которую нужно сделать абсолютную ссылку, знак доллара. Можно также, сразу после ввода адреса нажать функциональную клавишу F7, и знаки доллара перед координатами строки и столбца отобразятся автоматически. Формула в самой верхней ячейке примет такой вид: «=D2/$D$7».

Копируем формулу вниз по столбцу. Как видим, на этот раз все получилось. В ячейках находятся корректные значения. Например, во второй строке таблицы формула выглядит, как «=D3/$D$7», то есть делитель поменялся, а делимое осталось неизменным.

Удаляем свою Историю Вконтакте с мобильного приложения:

Типы ссылок на ячейки в формулах Excel

​Например, при копировании формулы​ перед указателем столбца​ то​ четыре возможных варианта​ особенность в том,​ ячейке или нажмите​ в формулах. Данные​ всегда удобно. Однако,​​ ссылка всегда будет​​ ​​B3:B6​​ суммировать ячейки​первоначальную формулу =A1.​ В ячейке​ в формулах используются​ маркер автозаполнения по​ задач в Excel.​= B4 * C4​

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

​ или строки для​ДВССЫЛ​ закрепления ссылки на​​ что они смещаются​​ клавишу F2 на​​ в B3 ссылаются​​ это можно легко​ ссылаться на ячейку,​а затем вернуть его в​(см. файл примера,​A4:A5​ При копировании вправо​В5​​ ссылки на диапазоны​​ необходимым ячейкам. В​​В Microsoft Excel часто​​на ячейку D4​​ «блокировки» этих элементов​​выводит 0, что​ ячейку:​ при копировании формул.​​ клавиатуре.​​ на A3, B4​​ обойти, используя чуть​​ содержащую наше значение​ самую правую позицию​ лист пример3). В​, если ввести в​ в ячейку​будем иметь формулу​

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

​ ячеек, например, формула​ нашем случае это​ возникают ситуации, когда​ D5, формула в​ (например, $A2 или​ не всегда удобно.​C5​ Т.е.​Скопируйте ее в остальные​ на A4 и​ более сложную конструкцию​ 25:​​ (также мышкой),​​ столбцах​​D10​С1​​ =А5*С5 (EXCEL при​​ =СУММ(А2:А11) вычисляет сумму​ диапазон D4:D13.​​ необходимо оставить ссылку​​ D5 регулирует вправо​​ B$3). Чтобы изменить​​ Однако, это можно​​→​​C5​ ячейки диапазона C3:C4.​ т.д. Все зависит​​ с проверкой через​​при копировании формулы из​​ ​​С, D, Е​​, то – ячейки​​формула будет преобразована​​ копировании формулы модифицировал​ значений из ячеек ​Отпустите кнопку мыши. Формула​ неизменной при заполнении​ по одному столбцу​​ тип ссылки на​

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

​ легко обойти, используя​$C$5​, например, превращается в​​Описание новой формулы. Символ​​ од того куда​ функцию ЕПУСТО():​​С3Н3​​то после нажатия клавиши​содержатся прогнозы продаж​С9:С10​ в =В1.​ ссылки на ячейки,​А2А3​

​ будет скопирована в​ ячеек

В отличие​ и становится​ ячейку, выполните следующее.​ чуть более сложную​→​С6​ доллара ($) в​ будет ссылаться первая​=ЕСЛИ(ЕПУСТО(ДВССЫЛ(«B2″));»»;ДВССЫЛ(«B2»))​– формула не​F4​ в натуральном выражении​​.​​Внимание!​​ т.к. их адреса​​, …​​ выбранные ячейки с​​ от относительных ссылок,​​= B5 * C5​​Выделите ячейку со ссылкой​​ конструкцию с проверкой​​$C​

​,​ адресе ссылок фиксирует​ введенная формула, а​

​При ссылке на ячейку​ изменится, и мы​, знаки $ будут​​ по годам (в​​Другими словами, будут суммироваться​При использовании относительной​​ не были записаны​​А11​ абсолютной ссылкой, и​ абсолютные не изменяются​. Если вы хотите​ на ячейку, которую​ через функцию​​5​​С7​ адрес в новых​ ее копии будут​В2​ получим правильный результат​ автоматически вставлены только​ шт.). Задача: в​​ 2 ячейки соседнего​​ адресации в Именованных формулах, Именованных​ в виде абсолютных​​. Однако, формула =СУММ($А$2:$А$11)​​ в каждой будет​ при копировании или​​ сохранить исходный в​​ нужно изменить.​​ЕПУСТО​​→​​и т.д. при​​ скопированных формулах.​ изменять ссылки относительно​​с другого листа​​ 625;​ во вторую часть​ столбцах​ столбца слева, находящиеся​ диапазонах, Условном форматировании, Проверке данных (примеры​​ ссылок).​​ также вычисляет сумму​ вычислен результат.​ заполнении. Вы можете​ ссылку на ячейку​

Действительно абсолютные ссылки

​В строка формул​:​​C​​ копировании вниз или​Абсолютные, относительные и смешанные​ своего положения в​

​ =ДВССЫЛ(«пример4!B2») может возникнуть​при вставке нового столбца​

​ ссылки!  =СУММ(А2:$А$5 ​

​F, G, H​

​ на той же​ см. в соответствующих​Чтобы выйти из ситуации​​ значений из тех​​Вы можете дважды щелкнуть​ воспользоваться абсолютной ссылкой,​ в этом примере​щелкните ссылку на​=ЕСЛИ(ЕПУСТО(ДВССЫЛ(«C5″));»»;ДВССЫЛ(«C5»))​$5​ в​ ссылки в Excel:​ диапазоне ячеек на​​ и другая сложность:​​ между столбцами​Чтобы вставить знаки $​посчитать годовые продажи​ строке и строкой​ статьях) необходимо следить, какая​ — откорректируем формулу​ же ячеек. Тогда​​ по заполненным ячейкам,​​ чтобы сохранить неизменной​

​ при копировании, внесенные​

​ ячейку, которую нужно​

planetaexcel.ru>

​=IF(ISBLANK(INDIRECT(«C5″));»»;INDIRECT(«C5»))​

Пример относительной ссылки

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

Делается это простым умножением количества (столбец B) на цену (столбец C). Например, для первого наименования товара формула будет выглядеть так «=B2*C2». Вписываем её в соответствующую ячейку таблицы.

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

Но, как видим, формула в нижней ячейке уже выглядит не «=B2*C2», а «=B3*C3». Соответственно, изменились и те формулы, которые расположены ниже. Вот таким свойством изменения при копировании и обладают относительные ссылки.

Относительные, абсолютные и смешанные ссылки на ячейки в Excel

Этот материал предназначен для начинающих и подготовлен с участием Анны Ивановой

Ссылка в Excel – это адрес ячейки или диапазона ячеек.

В Excel есть два вида стиля ссылок:

• Классический (или А1)
• Стиль ссылок R1C1; здесь R — row (строка), C — column (столбец).

Включить стиль ссылок R1C1 можно в настройках Сервис —> Параметры Excel —> закладка Формулы —> галочка Стиль ссылок R1C1:

Рис. 1. Настройка стиля ссылок

Скачать заметку в формате Word, примеры в формате Excel

Стиль R1C1 используется реже, в основном из-за того, что он менее нагляден. Однако он становится незаменим, если адрес ячейки является результатом вычислений (см. пример использования стиля R1C1 в заметке Excel. Использование ДВССЫЛ для транспонирования строк в столбцы с сохранением формул)

Ссылки в Excel бывают трех типов:

• Относительные ссылки; например, A1;
• Абсолютные ссылки; например, $A$1;
• Смешанные ссылки; например, $A1 или A$1 (они наполовину относительные, наполовину абсолютные).

«Относительность» ссылки означает, что из данной ячейки ссылаются на ячейку, отстоящую на столько-то строк и столбцов относительно данной (рис. 2А). Здесь в ячейке А6 формула ссылается на две ячейки (С3 и С4), отстоящие от данной на два столбца вправо и на три (С3) и две (С4) ячейки выше. При «протаскивании» формулы, например, в ячейку А7 (рис. 2Б) формула самопроизвольно изменяется.

Рис. 2. Относительные ссылки

Знак $ перед буквой или цифрой в обозначении ячейки говорит о том, что эта часть обозначения является абсолютной, то есть не будет изменяться при изменении ячейки, из которой делается ссылка. Сравните, как ведут себя формулы на рис. 2 и рис. 3. При «протаскивании» формула не меняется: и из ячейки А6, и из ячейки А7 ссылка идет на ячейки С2 и С3.

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

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

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

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

Например, нам требуется рассчитать отпускную стоимость товара при различных наценках, с учетом, что закупочная цена фиксирована (рис. 4).

Рис. 4. Расчет значений в таблице с использованием смешанных ссылок; цена за штуку – закупочная цена; в столбцах D, E и F показаны отпускные цены при различных наценках.

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

При «протаскивании» формулы по столбцам нам необходимо, чтобы столбец С был зафиксирован. Аналогично, при «протаскивании» формулы по строкам, нам необходимо зафиксировать строку 3. В ячейке D4 таким образом получилась формула =$C4*(1+D$3); абсолютные ссылки я выделил жирностью и цветом. При протаскивании по диапазону D4:F6 такая формула дает правильные значения в каждой ячейке диапазона.

Недостатки видов адресации

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

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

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

Ход урока

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Конспект урока:

Постановка цели и задач урока. Слайд №1 (Приложение 1)

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

Актуализация знаний: фронтальный опрос

Слайд №2

1. Что такое электронная таблица?
2. Какие основные элементы электронной таблицы вам известны?
3. Как задается имя ячейки (строки, столбца) в электронной таблице?
4. Какие данные можно ввести в ячейку электронной таблицы?Слайд №3
5. Какие данные размещены в ячейках таблицы
   

   	A	B	C	D
   1	2	1	=A1+3*B1	=A1^2+B1
   2	4	6	=A2+3*B2	=A2^2+B2

1. Что такое формула?
2. Какие правила записи формул в электронных таблицах вы знаете?

Объяснение нового материала.

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

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

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

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

Слайд №6 Абсолютным может быть и часть адреса ячейки (только номер строки или номер столбца). Такой адрес принято называть смешанным.

Пример смешанного адреса: $D1, B$6

Рассмотрим это на примере. Введем расчетные формулы в таблицу оплаты за водоснабжение.

оплата за водоснабжение
стоимость 1 м3 руб     24
месяц	показания счетчика, м3	количество потребленной воды, м3	сумма к оплате, руб
декабрь	2315
январь	2345
февраль	2364
март	2393
апрель	2430
май	2476
июнь	2510

Слайд №7 (Приложение 2)

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

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

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

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

Физкультминутка.

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

Разделимся на две команды. Одна – агенты по продаже и покупке недвижимости, другая – кассиры по продаже авиабилетов. Первая команда садится за компьютеры и выполняет свое задание (Приложение 3), вторая в это время на своих местах на листочках выполняет тестовые задания (Приложение 4). По мере выполнения заданий команды меняются местами. (Приложение 5).

Подведение итогов.

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

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

Объявить оценки за урок. Задание на дом.

Автоматизация ввода

К средствам
автоматизации ввода относятся
автозавершение и автозаполнение.

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

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

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

Заполнение
прогрессией.
Чтобы точно сформулировать условия
заполнения ячеек, следует выполнить
команду Правка
→ Заполнить → Прогрессия.
В открывшемся диалоговом окне Прогрессия
выбирается тип прогрессии, величина
шага и предельное значение.

Рисунок 3 –
Диалоговое окно Прогрессия

Типы ссылок на ячейки в формулах Excel

​ 50, причем, только​ то, что выделено​ не были записаны​1​ и C) столбцов​ формула выглядит, как​ от общей суммы.​ДВССЫЛ​​Самый простой и быстрый​​ ссылки со знаком​​ Ведь формула автоматически​​ свои плюсы и​ F4. Либо вручную​ никто не обучал,​ функции ДВССЫЛ(), которая​Чтобы изменить только первую или​F,GH​ в четных строках​

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

​ может быть несколько). ​ в виде абсолютных​, к которому прибавлено​​ и строк (2),​​«=D3/$D$7»​​ Это делается путем​​выводит 0, что​ способ превратить относительную​ доллара, например​ ссылается на новое​ минусы.​ напечатайте символы «доллара».​ то вы вряд​​ формирует ссылку на​​ втрорую часть ссылки -​​, этот значок $​​ (см. файл примера,​​Теперь примеры.​​ ссылок).​ число 5. Также​ знак доллара (​​, то есть делитель​​ деления стоимости на​​ не всегда удобно.​​ ссылку в абсолютную​$D$2​ значение в столбце​В Excel существует несколько​Это последний тип, встречающийся​ ли встречались с​

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

​ ячейку из текстовой​ установите мышкой курсор​ говорит EXCEL о​ лист пример2). Построим​Пусть в столбце​Чтобы выйти из ситуации​ в формулах используются​$​ поменялся, а делимое​ общую сумму. Например,​ Однако, это можно​ или смешанную -​​или​​ ячеек таблицы (суммы​​ типов ссылок: абсолютные,​ в формулах. Как​​ абсолютными ссылками. Поэтому​​ строки. Если ввести​ в нужную часть​​ том, что ссылку​​ такую таблицу:​​А​​ — откорректируем формулу​​ ссылки на диапазоны​​). Затем, при копировании​ осталось неизменным.​ чтобы рассчитать удельный​​ легко обойти, используя​​ это выделить ее​​F$3​​ в долларах). А​​ относительные и смешанные.​​ понятно из названия,​​ мы начнем с​ в ячейку формулу:​ ссылки и последовательно​ на столбец​Создадим правило для Условного​​введены числовые значения.​

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

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

​ нажимайте клавушу​B​ форматирования:​ В столбце​В1​ =СУММ(А2:А11) вычисляет сумму​= $B$ 4 *​ относительных ссылок, существуют​ его стоимость (D2)​ конструкцию с проверкой​ несколько раз нажать​ уже, наконец, разберемся​ нам нужно зафиксировать​​ относятся «имена» на​​ относительная ссылка одновременно.​​Абсолютные и относительные ссылки​​ всегда будет указывать​​F4.​​модифицировать не нужно.​​выделите диапазон таблицы​​B​​.​​ значений из ячеек ​

​ $C$ 4​ так называемые смешанные​ делим на общую​

​ через функцию​ на клавишу F4.​ что именно они​​ на адресе A2.​​ целые диапазоны ячеек.​ Несложно догадаться, что​​ в формулах служат​​ на ячейку с​В заключении расширим тему​ А вот перед​B2:F11​нужно ввести формулы​выделите ячейку​​А2А3​​из D4 для​ ссылки. В них​ сумму (D7). Получаем​ЕПУСТО​ Эта клавиша гоняет​ означают, как работают​ Соответственно нужно менять​​ Рассмотрим их возможности​​ на практике она​ для работы с​​ адресом​​ абсолютной адресации. Предположим,​ столбцом​​, так, чтобы активной​​ для суммирования значений​​В1​​, …​​ D5 формулу, должно​​ одна из составляющих​ следующую формулу:​​:​​ по кругу все​ и где могут​ в формуле относительную​ и отличия при​ обозначается, как $А1​​ адресами ячеек, но​​B2​ что в ячейке​С​ ячейкой была​

Действительно абсолютные ссылки

​ из 2-х ячеек​;​​А11​​ оставаться точно так​ изменяется, а вторая​«=D2/D7»​

​=ЕСЛИ(ЕПУСТО(ДВССЫЛ(«C5″));»»;ДВССЫЛ(«C5»))​ четыре возможных варианта​

​ пригодиться в ваших​

​ ссылку на абсолютную.​

​ практическом применении в​ или А$1. Таким​ в противоположных целях.​​вне зависимости от​​B2​такого значка нет​B2​ столбца​войдите в режим правки​. Однако, формула =СУММ($А$2:$А$11)​ же.​ фиксированная. Например, у​.​​=IF(ISBLANK(INDIRECT(«C5″));»»;INDIRECT(«C5»))​​ закрепления ссылки на​ файлах.​Как сделать абсолютную ссылку​ формулах.​ образом, это позволяет​ Рассматриваемый нами вид​ любых дальнейших действий​​находится число 25,​​ и формула в​

​(важно выделить диапазон​

​А​

planetaexcel.ru>

​ ячейки (нажмите клавишу​

• Excel вставка картинки в ячейку
• Как в excel сделать перенос в ячейке
• Excel добавить в ячейку символ
• Как в excel сделать ячейку с выбором
• Как перемещать ячейки в excel
• Excel заливка ячейки по условию
• Excel значение ячейки
• Как в excel выровнять ячейки по содержимому
• Excel курсор не перемещается по ячейкам
• Excel новый абзац в ячейке
• Excel подсчитать количество символов в ячейке excel
• Excel почему нельзя объединить ячейки в

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

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

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

Исходная таблица

Чтобы рассчитать общую стоимость молока мы должны умножить количество товара на цену одной единицы, т. е.  содержимое ячейки C2 умножить на содержимое D2. Для этого мы можем написать в E2

=41*10

В итоге получим в ячейке E2 число 410 — это и есть стоимость 10 единиц молока.

Но, предположим, что цена молока повысилась до 43 рублей или количество изменилось с 10 до 9. Тогда нам придется вносить исправления не только в ячейках C2 и D2, но и в формуле в ячейке E2. Иначе в E2 так и останется 410, что неверно. Это не очень удобно.

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

=C2*D2

И теперь эта ячейка станет зависимой от ячеек C2 и D2. Любое изменение содержимого ячеек C2 и D2 тут же скажется на ячейке E2, ее содержимое будет пересчитано. И нам не нужно будет заниматься этим вручную.

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

Обращаю внимание — в ячейках E2, E3 и E4 будут храниться формулы

Ячейка	Содержимое
E2	=C2*D2
E3	=C3*D3
E4	=C4*D4

А таблица примет вид:

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

При копировании мы не меняли вручную адреса ячеек в формулах — они менялись автоматически. Это и есть относительная адресация.

При копировании формулы =C2*D2 из ячейки E2 в ячейку E3, в ячейку E3 будет помещена формула =C3*D3, т. е. адреса ячеек в формуле будут преобразованы относительно ячейки E3.

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

Давайте добавим в нашу таблицу еще один столбец, в котором общая стоимость будет выражена в долларах. Курс доллара мы поместим в ячейку G2. Таблица будет выглядеть так:

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

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

=E2/G2

В итоге получим следующее:

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

На дробную часть можно не обращать внимание — наша цель не в этом. Все замечательно получилось

Далее попробуем скопировать содержимое ячейки F2 в F3 чтобы получить общую стоимость хлеба в долларах. И получим ошибку #ДЕЛ/0! которая говорит, что мы пытаемся делить на ноль:

Все замечательно получилось. Далее попробуем скопировать содержимое ячейки F2 в F3 чтобы получить общую стоимость хлеба в долларах. И получим ошибку #ДЕЛ/0! которая говорит, что мы пытаемся делить на ноль:

Ошибка #ДЕЛ/0!

А если мы посмотрим содержимое ячейки F3 то увидим формулу =E3/G3. А так как в ячейке E3 у нас 391, а в G3 — пусто, то Excel и выдал нам такую ошибку. Ведь он пытался использовать относительную адресацию. Давайте исправлять ситуацию.

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

Итак, поменяем содержимое ячейки F2 на

=E2/$G$2

Здесь мы адресуем ячейку E2 относительно, а G2 — абсолютно. В итоге при копировании в ячейку F3 ячейка E2 изменится на E3, а вот G2 так и останется — $G$2. Это нам и нужно. Скопируем содержимое ячейки F2 в F3 и F4 и увидим, что все работает правильно. Этого мы и добивались.

Итоговая таблица Excel

Обратите внимание, что так как мы заполняем ячейки ниже F2, то у ячейки G2 меняется только номер строки, а столбец остается прежним. Поэтому мы можем поставить символ $ только перед номером строки — замораживать столбец не обязательно, он и так не меняется при копировании

В итоге в ячейках F2, F3, F4 должны быть записаны формулы:

Ячейка	Содержимое
F2	=E2/G$2
F3	=E3/G$2
F4	=E4/G$2

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

Александр Чернышов

Как заставить Excel использовать действительно относительную адресацию

У меня сложная электронная таблица, но я могу обобщить то, что я хочу, на простом примере:

Скажем, я хочу бюджетную таблицу. У меня есть четыре столбца:

A B C D 1: Date What Amount $1,000.00

Это последнее быстро и грязно: на самом деле это должен быть «Баланс», но я положил свой текущий банковский баланс в качестве отправной точки …

Следующие несколько строк — мои будущие ожидаемые расходы:

A B C D
1: Date What Amount $1,000.00
2: 1 Jul 16 Electricity -$230.00 =D1+C2 (shows $770.00)
3: 3 Jul 16 Gas -$144.15 =D2+C3 (shows $625.85)
4: 4 Jul 16 Pay +$2,000.00 =D3+C4 (shows $2,625.85)

Это все работает, и это стандартный Excel.

Особенно обратите внимание, что в последнем столбце используется «относительная адресация» — то есть, если вы копируете строку 4вниз, Excel автоматически изменяет новые ячейки, чтобы указать, что вы хотите: «ячейка, расположенная выше, добавлена ​​в ячейку, расположенную слева от сюда». Если я не хотел относительной адресации, то есть я всегда хотел «столбец B» или «строку 1» (или оба), то я мог бы поставить знак доллара $перед соответствующей частью

A B C D
1: Date What Amount $1,000.00
2: 1 Jul 16 Electricity -$230.00 =D1+C2 (shows $770.00)
3: 4 Jul 16 Pay +$2,000.00 =D4+C3 (shows $2,625.85)
4: 6 Jul 16 Gas -$144.15 =D2+C4 (shows $625.85)

… когда я хочу сохранить относительность (извините, Эйнштейн!)

A B C D
1: Date What Amount $1,000.00
2: 1 Jul 16 Electricity -$230.00 =D1+C2 (shows $770.00)
3: 4 Jul 16 Pay +$2,000.00 =D2+C3 (shows $2,770.00)
4: 6 Jul 16 Gas -$144.15 =D3+C4 (shows $2,625.85)

Хуже того, когда событие, наконец, происходит, я хочу обновить свой банковский баланс $D$1и удалить соответствующую строку. Excel немедленно помещает сообщения об ошибках во все столбцы D! «Осознано», что указанная ячейка исчезла, поэтому она просто сдается — когда я снова хочу, чтобы каждая ячейка просто переняла свою новую ячейку «сразу над».