Смешанные ссылки в excel
Программа Microsoft Excel: абсолютные и относительные ссылки
Смотрите также перед номером строки вдоль колонки A2:A5. адрес (латинская буква как можно чаще операций.С2 нажимайте клавушу(для ввода абсолютной, а строка может, то – ячейки формулы (активной может(можно перед т.е. ссылка на А1 или $A1
если нужно сделать обладают относительные ссылки.
Определение абсолютных и относительных ссылок
При работе с формулами или колонки. ИлиКак видите, относительные адреса
в заголовке: А, в процессе работы.Использование абсолютных и относительных, а по прежнемуF4. ссылки): изменяться в зависимостиС9:С10 быть только одна
Пример относительной ссылки
С диапазон ячеек при или $A$1. То, абсолютную ссылку, придетсяНо, далеко не во в программе Microsoft перед тем и
помогают автоматически изменять В, С, D…) ссылок в Excel в ячейкуВ заключении расширим темуВведите часть формулы без при копировании формулы.. ячейка на листе,
, перед или после копировании каким образом вы применить один приём. всех случаях нам Excel пользователям приходится тем. Ниже рассмотрим адрес в каждой столбца.По умолчанию (стандартно) все упрощает работу пользователя.B2 абсолютной адресации. Предположим, ввода $: =СУММ(А2:А5 Предположим, у нас есть
Другими словами, будут суммироваться не смотря на1не изменилась введете адрес вПосле того, как формула нужны именно относительные оперировать ссылками на все 3 варианта формуле.Чтобы сделать ссылку относительной, ссылки в Excel
Ошибка в относительной ссылке
Особенность относительных ссылок, и мы получим что в ячейкеЗатем столбец с ценами 2 ячейки соседнего то, что выделено);. А в столбце формулу, будет зависеть, введена, просто ставим ссылки. Например, нам другие ячейки, расположенные и определим ихТак же стоит отметить используйте эффективные способы. относительные. Они выглядят в том, что неправильный результат.B2
сразу в диапазоне столбца слева, находящиеся может быть несколько). нажмите один раз клавишуС как он будет в ячейке, или нужно в той в документе. Но, отличия. закономерность изменения ссылок Конечно, вы можете вот так: =А2 их адрес изменяетсяВопрос: можно ли модифицироватьнаходится число 25,нажмите клавишуB3:B6
на той жеТеперь примеры.F4получим другой результат: модифицироваться при ее в строке формул, же таблице рассчитать не каждый пользовательНаша новая формула должна в формулах. Данные вручную проставить знаки или =B2 (только при копировании. А исходную формулу из с которым необходимоF4
Создание абсолютной ссылки
(см. файл примера, строке и строкойПусть в столбце. Ссылка в ячейке копировании в другие перед координатами столбца удельный вес стоимости знает, что эти
содержать сразу 2 в B3 ссылаются «$» (переходите на цифра+буква): вот адреса иС2 выполнить ряд вычислений,, знаки $ будут лист пример3). В выше.
АС1С3 ячейки листа. Это и строки ячейки, каждого наименования товара ссылки бывают двух типа ссылок: абсолютные на A3, B4 английскую раскладку клавиатуры,Если мы решим скопировать значения абсолютных ссылок(=$B$2^$D2), так чтобы например, возвести в вставлены автоматически: =СУММ($А$2:$А$5 столбцахСсылка на диапазон суммированиявведены числовые значения.выделится и превратитсябудет формула =СУММ(A3:A12),
пригодится при как на которую нужно от общей суммы. видов: абсолютные и и относительные. на A4 и после чего жмете эту формулу из остаются неизменными. Так данные все время разные степени (см.Для окончания ввода формулы нажмите
Смешанные ссылки
С, D, Е будет меняться в В столбце в в ячейке построении обычных формул сделать абсолютную ссылку, Это делается путем относительные. Давайте выясним,В C2 введите уже т.д. Все зависит SHIFT+4). строки 2 в же рассмотрим примеры
брались из второго файл примера, листENTER.содержатся прогнозы продаж зависимости от месторасположенияB$C$1С4 на листе, так знак доллара. Можно деления стоимости на чем они отличаются другую формулу: =B2/A$2. од того кудаСначала выделяете ячейку со строку 3 адреса
использования и преимущества
Типы ссылок MS EXCEL на ячейку: относительная (A1), абсолютная ($A$1) и смешанная (A$1) адресация
столбца листа и пример4). Для этогоЕсли после ввода =СУММ(А2:А5 в формуле в натуральном выражении формулы на листе,нужно ввести формулы(при повторных нажатияхбудет формула =СУММ(A4:A13) и при создании также, сразу после общую сумму. Например, между собой, и Чтобы изменить ссылки будет ссылаться первая ссылкой (или просто в параметрах формулы смешанных ссылок, без независимо от вставки в столбце передвинуть курсор с по годам (в но «расстояние» между для суммирования значений
клавиши и т.д. Т.е. Именованных формул, задания ввода адреса нажать чтобы рассчитать удельный как создать ссылку в Excel сделайте введенная формула, а ссылку) и нажимаете изменяться автоматически: которых в некоторых новых столбцов?C помощью мыши в шт.). Задача: в ячейкой с формулой из 2-х ячеекF4 при копировании ссылка правил Условного форматирования функциональную клавишу F7, вес картофеля, мы нужного вида. двойной щелчок левой ее копии будут на «F4». СистемаОтносительная ссылка удобна в случаях просто неРешение заключается в использованиинапишем формулу возведения позицию левее, столбцах
Абсолютная адресация (абсолютные ссылки)
и диапазоном суммирования столбцассылка будет принимать была и при формировании и знаки доллара его стоимость (D2)Скачать последнюю версию кнопкой мышки по
изменять ссылки относительно автоматически предложит вам случае, если хотите обойтись. функции ДВССЫЛ(), которая в степень (значения F, G, H всегда будет одинаковымА последовательно видмодифицирована условий Проверки данных. перед координатами строки делим на общую Excel ячейке или нажмите своего положения в выбор ячейки, останется продублировать однотипный расчетЧасто вам необходимо разбить формирует ссылку на степени введем ва затем вернуть его впосчитать годовые продажи (один столбец влево).: значения из тойC$1, $C1, C1, $C$1.В подавляющем большинстве формул и столбца отобразятся сумму (D7). Получаем
Что же представляют собой
клавишу F2 на диапазоне ячеек на только одобрительно нажать по нескольким столбцам. общую сумму на ячейку из текстовой столбец самую правую позицию в рублях, т.е.Относительная адресация при создании же строки и, …). Ссылка видаДругой пример. EXCEL используются ссылки автоматически. Формула в следующую формулу: абсолютные и относительные клавиатуре. листе. на «Enter».Чтобы воспользоваться относительными ячейками, доли. В таком строки. Если ввестиD (также мышкой), перемножить столбцы формул для Условного значения из строки$C$1Пусть в диапазоне на ячейки. Например, самой верхней ячейке«=D2/D7» ссылки в Экселе?Скопируйте ее в остальныеMicrosoft Excel открывает перед необходимо совершить простую
случае ссылка на в ячейку формулу:): =$B$2^$D2. С, D, Е
- форматирования. выше.называется
- А1:А5 если в ячейке примет такой вид:.Абсолютные ссылки – это
- ячейки диапазона C3:C4.Заполните табличку, так как пользователями множество возможностей. последовательность действий: ячейку, которая содержит =ДВССЫЛ(«B2»), то онаМы использовали абсолютную ссылку
- то после нажатия клавишина столбецПусть необходимо выделить вТ.е. вабсолютнойC$1, $C1имеются числа (например,В1«=D2/$D$7»В случае, если мы ссылки, при копированииОписание новой формулы. Символ показано на рисунке: Поэтому вам стоитВыделяем нужную нам ячейку. общую сумму, не всегда будет указывать на ячейкуF4B таблице, содержащей числа
B2– смешанными, а зарплата сотрудников отдела),содержится формула =А1+5,. попытаемся скопировать формулу
которых координаты ячеек доллара ($) вОписание исходной таблицы. В внимательно изучить примерыНажимаем Ctrl+C. должна меняться в на ячейку сB2, знаки $ будут
Относительная адресация (относительные ссылки)
. Использование механизма относительной от 1 додолжна быть формула:С1относительной а в то означает, чтоКопируем формулу вниз по в другие строки не изменяются, находятся адресе ссылок фиксирует ячейке A2 находиться и практиковать этиВыделяем ячейку, в которую формулах при копировании. адресом. При любых изменениях автоматически вставлены только адресации позволяет нам 100, значения больше =СУММ(A1:A2), в.С1 в ячейку столбцу. Как видим,
тем же способом,
в зафиксированном состоянии. адрес в новых актуальный курс евро возможности, для ускорения необходимо вставить относительную Сделать это помогаетB2 положения формулы абсолютная во вторую часть ввести для решения 50, причем, толькоB3Такм образом, введем в
– процент премии
В1 на этот раз что и предыдущий В относительных ссылках скопированных формулах. по отношению к работы упрощая рутинные формулу. абсолютная ссылка. Помогаетвне зависимости от ссылка всегда будет ссылки! =СУММ(А2:$А$5 задачи только одну в четных строках
: =СУММ(A2:A3) и т.д.В1 установленный для всегобудет помещено значение все получилось. В раз, то получим
координаты ячеек изменяютсяАбсолютные, относительные и смешанные доллару на сегодня. процессы.Нажимаем Ctrl+V. она и в любых дальнейших действий ссылаться на ячейку,Чтобы вставить знаки $ формулу. В ячейку (см. файл примера,Решить задачу просто: записав
- формулу =А1*$С$1. Это отдела. Для подсчета ячейки ячейках находятся корректные совершенно неудовлетворяющий нас при копировании, относительно ссылки в Excel:
- В диапазоне ячеекПреимущества абсолютных ссылок сложноПолезный совет! Если у других случаях, когда
- пользователя, вставки или содержащую наше значение
- во всю ссылку,F лист пример2). Построим
- в
можно сделать и премии каждого сотрудникаА1 значения. Например, во результат. Как видим, других ячеек листа.$A$2 – адрес абсолютной B2:B4 находятся суммы недооценить. Их часто вас однотипный расчет, необходимо сохранить адрес удаления столбцов и 25: выделите всю ссылкувводим: =$В3*C3. Потом такую таблицу:B2 в ручную, введя необходимо все зарплатынаходящейся на пересечении
второй строке таблицы уже во второйПокажем, как это работает ссылки с фиксацией в долларах. В приходится использовать в можно воспользоваться простым ячейки при копировании. т.д.при копировании формулы из А2:$А$5 или ее протягиваем формулу маркеромСоздадим правило для Условногоформулу =СУММ(A1:A2), протянем ее знак $.
умножить на % столбца формула выглядит, как
строке таблицы формула на примере. Возьмем по колонкам и диапазоне C2:C4 будут процессе работы с «лайфхаком». Выделяем ячейку Как выглядит абсолютнаяНебольшая сложность состоит вС3Н3
часть по обе заполнения вниз до
- форматирования: с помощью Маркера заполненияНажмем премии. Рассчитанную премиюА«=D3/$D$7» имеет вид таблицу, которая содержит строкам, как по находится суммы в программой. Относительные ссылки , после этого ссылка? том, что если
- – формула не стороны двоеточия, напримерF6выделите диапазон таблицы в ячейку
- ENTER
- поместим в диапазоне
- и строки
, то есть делитель«=D3/D8» количество и цену вертикали, так и евро после конвертации на ячейки в подводим курсор мышкиАбсолютная ссылка выглядит следующим целевая ячейка пустая, изменится, и мы 2:$А, и нажмите,
Смешанные ссылки
B2:F11B3и протянем ееВ1:В51 поменялся, а делимое, то есть сдвинулась различных наименований продуктов. по горизонтали. валют. Завтра курс
Excel более популярные к квадратику расположенному образом: знак «$» то ДВССЫЛ() выводит получим правильный результат клавишуа затем весь столбец, так, чтобы активнойи ниже. Другим вниз. Теперь в. Для этого введем, к которому прибавлено осталось неизменным. не только ссылка Нам нужно посчитать$A2 – смешанная ссылка. измениться и задача чем, абсолютные, но в правом нижнем стоит перед буквой 0, что не 625;F4. таблицы протягиваем вправо ячейкой была вариантом решения этойВ5 в ячейку число 5. ТакжеКроме типичных абсолютных и на ячейку с
стоимость. При копировании фиксируется таблички автоматически пересчитать так же имеют углу. Появится черный
и перед цифрой. всегда удобно. Однако,при вставке нового столбцаЗнаки $ будут на столбцы B2 задачи является использованиебудет правильная формулаВ1 в формулах используются относительных ссылок, существуют суммой по строке,Делается это простым умножением колонка, а строка диапазон C2:C4 в свои плюсы и крестик. После этогоНесмотря на копирования в это можно легко между столбцами автоматически вставлены воG H(важно выделить диапазон
Вводим знак $ в адрес ячейки
Именованной формулы. Для =А5*$С$1. Всем сотрудникамформулу =А1*С1. Если ссылки на диапазоны так называемые смешанные
но и ссылка количества (столбец B) изменяется. зависимости от изменения
минусы. просто «протягиваем» формулу другие ячейки и обойти, используя чуть
- АВ всю ссылку $А$2:$А$5
- . начиная с этого: теперь достанется премия мы с помощью Маркера ячеек, например, формула
- ссылки. В них на ячейку, отвечающую
на цену (столбецA$2 – смешанная ссылка. значения в ячейкеВ Excel существует несколько
вниз. Система автоматически
даже на другие более сложную конструкцию– формула превратится
3. С помощью клавиши
Обратите внимание, что вB2выделите ячейку :). заполнения протянем формулу =СУММ(А2:А11) вычисляет сумму
одна из составляющих за общий итог. C). Например, для При копировании фиксируется A2 (то есть типов ссылок: абсолютные, скопирует все значения. листы, книги, абсолютная с проверкой через в =$C$2^$E3, ноF4 формуле =$В3*C3 перед
, а не сB2Введем в ячейку вниз, то получим
- значений из ячеек изменяется, а вторая
- D8 – это совершенно первого наименования товара строка, а колонка курса евро). относительные и смешанные. Данный инструмент называется
- ссылка всегда будет функцию ЕПУСТО(): мы снова получим(для ввода относительной
- столбцомF11(это принципиально приB1
- вА2А3 фиксированная. Например, у пустая ячейка, поэтому формула будет выглядеть изменяется.Для решения данной задачи
- Сюда так же маркером автозаполнения.
ссылаться на один=ЕСЛИ(ЕПУСТО(ДВССЫЛ(«B2″));»»;ДВССЫЛ(«B2»)) правильный результат 625. ссылки).B. Во втором случае, использовании относительных ссылок
«СуперАбсолютная» адресация
формулу =А1, представляющуюВ2:В5, . смешанной ссылки $D7 формула и выдаёт такДля сравнения: A2 – нам нужно ввести относятся «имена» наИли еще проще: диапазон и тот жеПри ссылке на ячейкуВсе правильно, т.к. этоВведите часть формулы безстоит значок $. активной ячейкой будет в Именах). Теперь собой относительную ссылкунули (при условии,
А11 строчка изменяется, а ошибку. Соответственно, формула«=B2*C2» это адрес относительный, формулу в C2: целые диапазоны ячеек. ячеек, в которые адрес столбца и
- В2 и есть суть ввода $: =СУММ(А2:А5 При копировании формулыF11B2
- на ячейку что в диапазоне. Однако, формула =СУММ($А$2:$А$11) столбец фиксированный. У в строке ниже. Вписываем её в без фиксации. Во
=B2/A2 и скопировать Рассмотрим их возможности нужно проставить формулы строки.с другого листа абсолютной адресации: ссылкиЗатем =$В3*C3 в ячейки);– активная ячейка;А1С2:С5 также вычисляет сумму ссылки D$7, наоборот, будет ссылаться на соответствующую ячейку таблицы. время копирования формул ее во все и отличия при выделяем так чтобыПолезный совет! Чтобы не =ДВССЫЛ(«пример4!B2») может возникнуть автоматически модифицируются длясразу столбцоввызовите инструмент Условное форматированиена вкладке Формулы в. Что же произойдетнет никаких значений). значений из тех изменяется столбец, но ячейку D9, иТеперь, чтобы вручную не строка (2) и ячейки диапазона C2:C4. практическом применении в
активная ячейка была вводить символ «$» и другая сложность: сохранения адресации нанажмите клавишуF,GH (Главная/ Стили/ Условное группе Определенные имена с формулой при
В ячейке же ячеек. Тогда строчка имеет абсолютное т.д. Нам же вбивать формулы для столбец (A) автоматически Но здесь возникает формулах. на формуле и вручную, при вводе при изменении названия нужные ячейки приF4, этот значок $ форматирование/ Создать правило/ выберите команду Присвоить
ее копировании вВ5 в чем же разница? значение. нужно, чтобы при ячеек, которые расположены изменяются на новые проблема. Из предыдущегоАбсолютные ссылки позволяют нам нажимаем комбинацию горячих используйте клавишу F4
любых модификациях строк, будут автоматически вставлены говорит EXCEL о использовать формулу для имя; ячейки расположенные нижебудем иметь формулуДля создания абсолютной ссылкиКак видим, при работе копировании постоянно сохранялась ниже, просто копируем адреса относительно расположения примера мы знаем,
зафиксировать строку или клавиш CTRL+D. как переключатель между формула перестает работать. и столбцах листа знаки $: =СУММ($А$2:$А$5 том, что ссылку …);в поле Имя введите,В1 =А5*С5 (EXCEL при
Абсолютные относительные и смешанные ссылки в Excel с примерами
используется знак $. с формулами в ссылка на ячейку данную формулу на скопированной формулы, как что при копировании столбец (или строку
Часто пользователям необходимо изменить всеми типами адресов. Но это также (ну, кроме удаленияЕще раз нажмите клавишу на столбецвведите формулу =И(ОСТАТ($A2;2)=$I$1;B2>50); например Сумма2ячеек;? После протягивания ее копировании формулы модифицировал Ссылка на диапазона программе Microsoft Excel D7, где расположен весь столбец. Становимся по вертикали, так относительные ссылки автоматически
Как вам сделать абсолютную ссылку в Excel?
и столбец одновременно), только ссылку наНапример, если нужно изменить можно обойти – ячейки с формулой,F4Bвыберите Формат;убедитесь, что в поле вниз Маркером заполнения, ссылки на ячейки, записывается ввиде $А$2:$А$11. Абсолютная для выполнения различных итог общей суммы, на нижний правый и по горизонтали.
меняют адреса относительно на которые должна строку или столбец, ссылку с относительной
см. пример из конечно). Однако бывают: ссылка будет модифицированамодифицировать не нужно.нажмите ОК Диапазон введена формула в ячейке т.к. их адреса ссылка позволяет при
задач приходится работать а такое свойство край ячейки сПримечание. В данном примере своего положения. Поэтому ссылаться формула. Относительные
а часть формулу на абсолютную: статьи Определяем имя
- ситуации, когда значения в =СУММ(А$2:А$5 (фиксируются строки) А вот перед
- Важно отметить, что, если =СУММ(A1:A2)В5 не были записаны
- копировании как с относительными, имеют как раз формулой, кликаем левой формула может содержать
- возникнет ошибка: ссылки в Excel оставить неизменной. СделатьПросто выделите ячейку, формулу
листа. на лист попадаютЕще раз нажмите клавишу столбцом
бы, при создании
Относительная ссылка на ячейку в Excel
Нажмите ОК.будет стоять формула в виде абсолютныхформулы однозначно зафиксировать так и с абсолютные ссылки.
кнопкой мыши, и не только смешаннуюОтносительно первого аргумента нас изменяются автоматически при это просто, ведь в которой необходимо
Другим способом заставить формулу из внешних источников.F4С
правила, активной ячейкойТеперь в =А5, т.е. EXCEL
- ссылок).
- адрес диапазона или
- абсолютными ссылками. ВТаким образом, для нашего при зажатой кнопке
- ссылку, но и
это вполне устраивает. копировании формулы вдоль в Excel существует выполнить замену. ссылаться на один Например, когда созданный: ссылка будет модифицированатакого значка нет былаB2изменилЧтобы выйти из ситуации адрес ячейки. Рассмотрим пример. некоторых случаях используются примера делитель должен
тянем мышку вниз. абсолютную: =B2/$A$2 результат Ведь формула автоматически диапазона ячеек, как такое понятие какПерейдите в строку формул и тот же пользователем макрос вставляет
в =СУММ($А2:$А5 (фиксируется столбец) и формула вF11введем формулу =Сумма2ячеек.первоначальную формулу =A1. — откорректируем формулуПусть в ячейке также смешанные ссылки. быть относительной ссылкой,
Как поставить смешанную ссылку в Excel?
Таким образом, формула будет одинаковый. Но ссылается на новое по вертикали, так
| «смешанная ссылка». | и поставьте там |
| столбец является использование | внешние данные вЕще раз нажмите клавишу ячейке |
| , то формулу необходимо | Результат будет тот, При копировании вправо в ячейкеВ2 Поэтому, пользователь даже и изменяться в |
скопируется и в в практике часто значение в столбце и по горизонтали.Сначала разложим все по курсор непосредственно на функции СМЕЩ() – ячейку
F4H6 было переписать: =И(ОСТАТ($A11;2)=$I$1;F11>50). который мы ожидали: в ячейкуВ1введена формула =СУММ($А$2:$А$11) среднего уровня должен
каждой строке таблицы, другие ячейки таблицы. возникают случаи, когда ячеек таблицы (суммы Простой пример относительных порядку. Смешанные ссылки адрес. об этом читайте
Абсолютная ссылка в Excel фиксирует ячейку в формуле
B2: ссылка будет модифицированапримет вид =$В6*E6. Поменять необходимо только будет выведена суммаС1. , а в четко понимать разницу а делимое должноНо, как видим, формула
без смешанных ссылок в долларах). А адресов ячеек: могут быть всегоНажмите на «F4». Вы статью Как заставить(т.е. всегда во в =СУММ(А2:А5 (относительная ссылка).Существует несколько возможностей при ссылки незафиксированные знаком
Абсолютные и относительные ссылки в Excel
2-х ячеек изформула будет преобразованавыделите ячейку ячейке между ними, и быть абсолютной ссылкой, в нижней ячейке не обойтись. вот второй показательЗаполните диапазон ячеек A2:A5 2-х видов: заметите, так программа формулу все время второй столбец листа).
- Последующие нажатия изменяют вводе формулы ввести знак $ в
- $: столбца слева (см. файл в =В1.В1С2 уметь пользоваться этими которая постоянно ссылается
- уже выглядит неПолезный совет. Чтобы не
нам нужно зафиксировать разными показателями радиусов.Тип ссылки автоматически предлагает вам
ссылаться на один Теперь, при вставке ссылку заново по адрес ячейки или диапазона.B2F11$A2$A11 примера, лист пример1). ЕслиВнимание!;формула =СУММ(А2:А11). Скопировав инструментами. на одну ячейку.«=B2*C2» вводить символ доллара на адресе A2.В ячейку B2 введите
Использование абсолютных и относительных ссылок в Excel
разные варианты и и тот же
столбца между столбцами кругу. Рассмотрим ввод на. формулу ввести вПри использовании относительнойвойдите в режим правки формулы вниз, например сАвтор: Максим ТютюшевС созданием относительных ссылок, а ($) вручную, после Соответственно нужно менять формулу вычисления объемаВ$1 расставляет знак доллара. столбец.АВДля окончания ввода нажмите
примере формулы =СУММ($А$2:$А$5)Смешанные ссылки имеют формат ячейку адресации в Именованных формулах, Именованных ячейки (нажмите клавишу помощью Маркера заполнения,В формулах EXCEL можно у пользователей проблем«=B3*C3» указания адреса периодически в формуле относительную сферы, которая будетПри копировании формулы относительноВыбирайте подходящую вам ссылку,
Microsoft Excel – это– формула какENTER.1. Ввести знак $ =$В3 или =B$3.B5 диапазонах, Условном форматировании, Проверке данных (примерыF2 во всех ячейках сослаться на другую не будет, так. Соответственно, изменились и нажимайте клавишу F4
ссылку на абсолютную. ссылаться на значение вертикали не изменяется периодически нажимая F4 универсальный аналитический инструмент, и раньше превратитсяЧтобы изменить только первую или можно вручную, последовательно В первом случае, то она будет см. в соответствующих
) или поставьте курсор столбца ячейку используя ее как все ссылки
- те формулы, которые для выбора нужногоКак сделать абсолютную ссылку A2. Формула будет адрес (номер) строки. и жмите на который позволяет быстро в =$C$2^$E3, но втрорую часть ссылки -
- вводя с клавиатуры все при копировании формулы
суммировать ячейки статьях) необходимо следить, какая в Строку формул;В адрес. Адрес ячейки
в Microsoft Excel расположены ниже. Вот
- типа: абсолютный или в Excel? Очень выглядеть следующим образом:$B1 «Enter». выполнять расчеты, находить
- т.к. исходное число установите мышкой курсор знаки =СУММ($А$2:$А$5) фиксируется ссылка на
- A4:A5 ячейка является активнойпоставьте курсор на ссылкуполучим одну и
в формуле можно по умолчанию являются таким свойством изменения смешанный. Это быстро просто нужно поставить =(4/3)*3,14*A2^3При копировании формулы относительноЭто очень удобный способ, взаимосвязи между формулами, (25) будет вставляться в нужную часть
2. С помощью клавиши столбец, если ввести в в момент созданияС1 ту же формулу =СУММ($А$2:$А$11), записать по-разному, например: относительными. А вот, при копировании и и удобно.
символ $ (доллар)Скопируйте формулу из B2 горизонтали не меняется который следует использовать осуществлять ряд математических макросом не в ссылки и последовательноF4B
Типы ссылок на ячейки в формулах Excel
Если вы работаете в Excel не второй день, то, наверняка уже встречали или использовали в формулах и функциях Excel ссылки со знаком доллара, например $D$2 или F$3 и т.п. Давайте уже, наконец, разберемся что именно они означают, как работают и где могут пригодиться в ваших файлах.
Относительные ссылки
Это обычные ссылки в виде буква столбца-номер строки ( А1, С5, т.е. "морской бой"), встречающиеся в большинстве файлов Excel. Их особенность в том, что они смещаются при копировании формул. Т.е. C5, например, превращается в С6, С7 и т.д. при копировании вниз или в D5, E5 и т.д. при копировании вправо и т.д. В большинстве случаев это нормально и не создает проблем:
Смешанные ссылки
Иногда тот факт, что ссылка в формуле при копировании "сползает" относительно исходной ячейки — бывает нежелательным. Тогда для закрепления ссылки используется знак доллара ($), позволяющий зафиксировать то, перед чем он стоит. Таким образом, например, ссылка $C5 не будет изменяться по столбцам (т.е. С никогда не превратится в D, E или F), но может смещаться по строкам (т.е. может сдвинуться на $C6, $C7 и т.д.). Аналогично, C$5 — не будет смещаться по строкам, но может "гулять" по столбцам. Такие ссылки называют смешанными:
Абсолютные ссылки
Ну, а если к ссылке дописать оба доллара сразу ($C$5) — она превратится в абсолютную и не будет меняться никак при любом копировании, т.е. долларами фиксируются намертво и строка и столбец:
Самый простой и быстрый способ превратить относительную ссылку в абсолютную или смешанную — это выделить ее в формуле и несколько раз нажать на клавишу F4. Эта клавиша гоняет по кругу все четыре возможных варианта закрепления ссылки на ячейку: C5 → $C$5 → $C5 → C$5 и все сначала.
Все просто и понятно. Но есть одно "но".
Предположим, мы хотим сделать абсолютную ссылку на ячейку С5. Такую, чтобы она ВСЕГДА ссылалась на С5 вне зависимости от любых дальнейших действий пользователя. Выясняется забавная вещь — даже если сделать ссылку абсолютной (т.е. $C$5), то она все равно меняется в некоторых ситуациях. Например: Если удалить третью и четвертую строки, то она изменится на $C$3. Если вставить столбец левее С, то она изменится на D. Если вырезать ячейку С5 и вставить в F7, то она изменится на F7 и так далее. А если мне нужна действительно жесткая ссылка, которая всегда будет ссылаться на С5 и ни на что другое ни при каких обстоятельствах или действиях пользователя?
Действительно абсолютные ссылки
Решение заключается в использовании функции ДВССЫЛ (INDIRECT) , которая формирует ссылку на ячейку из текстовой строки.
Если ввести в ячейку формулу:
то она всегда будет указывать на ячейку с адресом C5 вне зависимости от любых дальнейших действий пользователя, вставки или удаления строк и т.д. Единственная небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ выводит 0, что не всегда удобно. Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО:
Excel: Смешанные ссылки
Многие пользователи успешно выполняют поставленные перед ними задачи и без применения разных типов ссылок. Всегда можно записать формулу с использованием только относительных ссылок, скопировать ее, подкорректировать и еще раз скопировать и так до конца рабочего дня. А можно нажать «F4» несколько раз в нужном месте и в результате выполнить тот же объем работ, но с гораздо меньшими затратами времени.
Использование смешанных ссылок может значительным образом сократить время решения ваших задач.
Смешанные ссылки являются наполовину абсолютными и наполовину относительными.
Смешанная ссылка указывается, если при копировании и перемещении не меняется номер строки или наименование столбца. При этом символ $ в первом случае ставится перед номером строки, а во втором — перед наименованием столбца.
- В$5, D$12 – смешанная ссылка, не меняется номер строки;
- $B5, $D12 — смешанная ссылка, не меняется наименование столбца.
Изменение типа ссылки производится циклически, в результате последовательных нажатий функциональной клавиши F4 в то время, когда курсор находится в тексте ссылки. Если, например, имеется ссылка на ячейку А1, то при каждом нажатии клавиши F4 вид ссылки в строке формул будет изменяться:
Применение смешанных ссылок
Пример 1
В ячейке В1 записана формула «=$A1».
Ссылка $A1 абсолютная по столбцу и относительная по строке.
Если мы потянем за Маркер заполнения эту формулу вправо, то ссылки во всех скопированных формулах будут указывать на ячейку A1, названия столбцов изменяться не будут, то есть ссылки будут вести себя как абсолютные.
Если потянем вниз — ссылки будут вести себя как относительные, то есть Excel будет пересчитывать их адрес. Таким образом, созданные формулы, будут использовать один и тот же столбец (А), но номера строк в них будут меняться (1,2,3…)
Пример 2
Предположим, нужно посчитать, сколько получит каждый работник за отработанные часы при определенной почасовой оплате труда.
Для заполнения таблицы используем смешанные ссылки.
Рассчитаем оплату труда для Андреева.
Для этого в ячейку С3 введем формулу: «=В3*С2»
Теперь необходимо скопировать формулу в строке «Андреев»
за 2 часа работы в день он получит 400 рублей
за 3 часа — 600 рублей
за 4 часа — 800 рублей
Оплата в час (200 рублей) не изменяется (значение ячейки В3). Меняется только количество отработанных часов (ячейки С2, D2, E2 …). Значит, для того, чтобы менять количество отработанных часов, надо, чтобы программа меняла название столбца, но не трогала номер строки. То есть, формула для расчета зарплаты Андреева должна быть такой: =В3*С$2
Теперь отредактируем полученную формулу, чтобы применить ее для расчета зарплаты остальных работников.
Андреев за 2 часа получит 200 рублей
Борисов за 2 часа получит 360 рублей
Сергеев за 2 часа получит 440 рублей
Из таблицы видно, что не изменяется отработанное время (значение ячейки С2). Меняется оплата за час (ячейки В3, В4, В5). Значит, для того, чтобы менять оплату за час, надо, чтобы программа меняла номер строки, но не трогала название столбца. Получаем формулу: =$В3*С$2
Введем полученную формулу в ячейку С3 , а затем скопируем ее во все ячейки таблицы.
Можно сначала протянуть формулу по строке Андреева, а потом скопировать вниз (на Борисова и Сергеева):
Можно и наоборот – сначала скопировать вниз, а потом – в сторону.
Полученные результаты в режиме просмотра формул:
Пример 3
Требуется рассчитать отпускную стоимость товара при различных наценках, с учетом, что закупочная цена фиксирована.
Для расчета Цены с наценкой для товара (артикул 12456) укажем в ячейке С3 формулу =B3*(1+C2).
Теперь необходимо внести изменения в формулу, которые позволят с помощью копирования заполнить значения во всей таблице.
При «протаскивании» формулы по столбцам нам необходимо, чтобы столбец B (с ценами) был зафиксирован, для этого в формуле перед ссылкой В3 ставим знак $ ($B3).
Аналогично, при «протаскивании» формулы по строкам, нам необходимо зафиксировать строку 2 (проценты наценки), для этого в формуле в ссылке С2 ставим знак $ перед 2 (С$2) .
В ячейке C3, таким образом, получилась формула =$B3*(1+C$2).
При протаскивании по диапазону С3 : Е7 такая формула дает правильные значения в каждой ячейке таблицы.
Зачем нужны смешанные ссылки в excel
Основным назначением электронных таблиц является организация всевозможных вычислений. Вы уже знаете, что:
• вычисление — это процесс расчёта по формулам;
• формула начинается со знака равенства и может включать в себя знаки операций, числа, ссылки и встроенные функции.
Относительные, абсолютные и смешанные ссылки
Ссылка указывает на ячейку или диапазон ячеек, содержащих данные, которые требуется использовать в формуле. Ссылки позволяют:
• использовать в одной формуле данные, находящиеся в разных частях электронной таблицы;
• использовать в нескольких формулах значение одной ячейки.
Различают два основных типа ссылок:
1) относительные — зависящие от положения формулы;
2) абсолютные — не зависящие от положения формулы.
Различие между относительными и абсолютными ссылками проявляется при копировании формулы из текущей ячейки в другие ячейки.
Относительные ссылки . Присутствующая в формуле относительная ссылка определяет расположение ячейки с данными относительно ячейки, в которой записана формула. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка.
Рассмотрим формулу =А1ˆ2, записанную в ячейке А2. Она содержит относительную ссылку А1, которая воспринимается табличным процессором следующим образом: содержимое ячейки, находящееся на одну строку выше той, в которой находится формула, следует возвести в квадрат.
При копировании формулы вдоль столбца и вдоль строки относительная ссылка автоматически корректируется так:
• смещение на один столбец приводит к изменению в ссылке одной буквы в имени столбца;
• смещение на одну строку приводит к изменению в ссылке номера строки на единицу.
Например, при копировании формулы из ячейки А2 в ячейки В2, С2 и D2 относительная ссылка автоматически изменяется и рассмотренная выше формула приобретает вид: =В1ˆ2, =С1ˆ2, =D1ˆ2. При копировании этой же формулы в ячейки АЗ и А4 получим, соответственно, =А2ˆ2, =А3ˆ2 (рис. 4).
Рис. 4. Скопированная формула с относительной ссылкой
Пример 1 . В 8 классе мы рассматривали задачу о численности населения некоторого города, ежегодно увеличивающейся на 5%. Проведём в электронных таблицах расчёт предполагаемой численности населения города в ближайшие 5 лет, если в текущем году она составляет 40 ООО человек.
Внесём в таблицу исходные данные, в ячейку ВЗ введём формулу =В2+0,05*В2 с относительными ссылками; скопируем формулу из ячейки ВЗ в диапазон ячеек В4:В7 (рис. 5).
Рис. 5. Вид таблицы расчёта численности населения в режиме отображения формул и режиме отображения значений
Ежегодный расчёт численности населения мы (согласно условию задачи) осуществляли по одной и той же формуле, исходные данные для которой всегда находились в ячейке, расположенной в том же столбце, но на одну строку выше, чем расчётная формула. При копировании формулы, содержащей относительные ссылки, нужные нам изменения осуществлялись автоматически.
Абсолютные ссылки . Абсолютная ссылка в формуле всегда ссылается на ячейку, расположенную в определённом (фиксированном) месте. В абсолютной ссылке перед каждой буквой и цифрой помещается знак $, например $А$1. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется (рис. 6).
Рис. 6. Скопированная формула с абсолютной ссылкой
Пример 2 . Некий гражданин открывает в банке счёт на сумму 10 ООО рублей. Ему сообщили, что каждый месяц сумма вклада будет увеличиваться на 1,2%. Для того чтобы узнать возможную сумму и приращение суммы вклада через 1, 2. 6 месяцев, гражданин провёл следующие расчёты (рис. 7).
Рис. 7. Расчёт приращения суммы вклада
Прокомментируйте формулы в таблице на рис. 3.7.
Выполните аналогичные расчёты для начального вклада, равного 15 ООО рублям.
Смешанные ссылки . Смешанная ссылка содержит либо абсолютно адресуемый столбец и относительно адресуемую строку ($А1), либо относительно адресуемый столбец и абсолютно адресуемую строку (А$1). При изменении позиции ячейки, содержащей формулу, относительная часть адреса изменяется, а абсолютная часть адреса не изменяется.
При копировании или заполнении формулы вдоль строк и вдоль столбцов относительная часть ссылки автоматически корректируется, а абсолютная часть ссылки не корректируется (рис. 8).
Рис. 8. Скопированная формула со смешанной ссылкой
Чтобы преобразовать ссылку из относительной в абсолютную и наоборот, можно выделить её в строке ввода и нажать клавишу F4 (Microsoft Office Excel) или комбинацию клавиш Shift+F4 (OpenOffice Calc). Если выделить относительную ссылку, такую как А1, то при первом нажатии этой клавиши (комбинации клавиш) и для строки, и для столбца установятся абсолютные ссылки ($А$1). При втором нажатии абсолютную ссылку получит только строка (А$1). При третьем нажатии абсолютную ссылку получит только столбец ($А1). Если нажать клавишу F4 (комбинацию клавиш Shift+F4) ещё раз, то для столбца и строки снова установятся относительные ссылки (А1).
Пример 3 . Требуется составить таблицу сложения чисел первого десятка, т. е. заполнить таблицу следующего вида:
При заполнении любой ячейки этой таблицы складываются соответствующие ей значения ячеек столбца А и строки 1. Иначе говоря, у первого слагаемого неизменным остаётся имя столбца (на него следует дать абсолютную ссылку), но изменяется номер строки (на неё следует дать относительную ссылку); у второго слагаемого изменяется номер столбца (относительная ссылка), но остаётся неизменным номер строки (абсолютная ссылка).
Внесите в ячейку В2 формулу =$А2+В$1 и скопируйте её на весь диапазон B2:J10. У вас должна получиться таблица сложения, знакомая каждому первокласснику.
Встроенные функции
При обработке данных в электронных таблицах можно использовать встроенные функции — заранее определённые формулы. Функция возвращает результат выполнения действий над значениями, выступающими в качестве аргументов. Использование функций позволяет упростить формулы и сделать процесс вычислений более понятным.
В электронных таблицах реализовано несколько сотен встроенных функций, подразделяющихся на: математические, статистические, логические, текстовые, финансовые и др.
Каждая функция имеет уникальное имя, которое используется для её вызова. Имя, как правило, представляет собой сокращённое название функции на естественном языке. При выполнении табличных расчётов достаточно часто используются функции:
СУММ (SUM) — суммирование аргументов;
МИН (MIN) — определение наименьшего значения из списка аргументов;
МАКС (МАХ) — определение наибольшего значения из списка аргументов.
Диалоговое окно Мастер функций позволяет упростить создание формул и свести к минимуму количество опечаток и синтаксических ошибок. При вводе функции в формулу диалоговое окно Мастер функций отображает имя функции, все её аргументы, описание функции и каждого из аргументов, текущий результат функции и всей формулы.
Пример 4 . Правила судейства в международных соревнованиях по одному из видов спорта таковы:
1) выступление каждого спортсмена оценивают N судей;
2) максимальная и минимальная оценки (по одной, если их несколько) каждого спортсмена отбрасываются;
3) в зачёт спортсмену идёт среднее арифметическое оставшихся оценок.
Информация о соревнованиях представлена в электронной таблице:
Требуется подсчитать оценки всех участников соревнований и определить оценку победителя. Для этого:
1) в ячейки А10, All, А12 и А14 заносим тексты «Максимальная оценка», «Минимальная оценка», «Итоговая оценка», «Оценка победителя»;
2) в ячейку В10 заносим формулу =МАКС(ВЗ:В8); копируем содержимое ячейки В10 в ячейки C10:F10;
3) в ячейку В11 заносим формулу =МИН(ВЗ:В8); копируем содержимое ячейки В10 в ячейки C11:F11;
4) в ячейку В12 заносим формулу =(СУММ(ВЗ:В8)-В10-В11)/4; копируем содержимое ячейки В12 в ячейки C12:F12;
5) в ячейку В14 заносим формулу =MAKC(B12:F12).
Результат решения задачи:
Логические функции
При изучении предшествующего материала вы неоднократно встречались с логическими операциями НЕ, И, ИЛИ (not, and, or). Построенные с их помощью логические выражения вы использовали при организации поиска в базах данных, при программировании различных вычислительных процессов.
Реализованы логические операции и в электронных таблицах, но здесь они представлены как функции: сначала записывается имя логической операции, а затем в круглых скобках перечисляются логические операнды.
Например, логическое выражение, соответствующее двойному неравенству 0<А1<10, в электронных таблицах будет записано как И(А1>0;А1<10) (AND(A1>0;A1<10)).
Вспомните, как аналогичное логическое выражение мы записывали при знакомстве с базами данных и языком программирования Паскаль.
Пример 5. Вычислим в электронных таблицах значения логического выражения НЕ А И НЕ В при всех возможных значениях входящих в него логических переменных.
При решении этой задачи мы следовали известному вам алгоритму построения таблицы истинности для логического выражения. Вычисления в диапазонах ячеек С3:С6, D3:D6, Е3:Е6 проводятся компьютером по заданным нами формулам.
Для проверки условий при выполнении расчётов в электронных таблицах реализована логическая функция ЕСЛИ (IF), называемая условной функцией.
Условная функция имеет следующую структуру:
ЕСЛИ (<условие>; <действие1>; <действие2)
Здесь <условие> — логическое выражение, т. е. любое выражение, построенное с помощью операций отношения и логических операций, принимающее значение ИСТИНА или ЛОЖЬ.
Если логическое выражение истинно, то значение ячейки, в которую записана условная функция, определяет <действие1>, если ложно — <действие2>.
Что вам напоминает структура условной функции?
Пример 6. Рассмотрим задачу о приёме в школьную баскетбольную команду: ученик может быть принят в эту команду, если его рост не менее 170 см.
Данные о претендентах (фамилия, рост) представлены в электронной таблице.
Использование условной функции в диапазоне ячеек С3:С8 позволяет вынести решение (принят/не принят) по каждому претенденту.
Функция СЧЁТЕСЛИ (COUNTIF) позволяет подсчитать количество ячеек в диапазоне, удовлетворяющих заданному условию. С помощью этой функции в ячейке С9 подсчитывается число претендентов, прошедших отбор в команду.
Функция СУММЕСЛИ (SUMMIF) суммирует значения диапазона, удовлетворяющие заданному условию.