logo
Ю-Софт. Всегда с Вами

РАБОТАЕМ С 1993 ГОДА

Горячая линия

(495) 956-08-80

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

Многие пользователи успешно выполняют поставленные перед ними задачи и без применения разных типов ссылок. Всегда можно записать формулу с использованием только относительных ссылок, скопировать ее, подкорректировать и еще раз скопировать и так до конца рабочего дня. А можно нажать «F4» несколько раз в нужном месте и в результате выполнить тот же объем работ, но с гораздо меньшими затратами времени.

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

Смешанные ссылки являются наполовину абсолютными и наполовину относительными.

Смешанная ссылка указывается, если при копировании и перемещении не меняется номер строки или наименование столбца. При этом символ $ в первом случае ставится перед номером строки, а во втором - перед наименованием столбца.

Пример:

  • В$5, D$12 – смешанная ссылка, не меняется номер строки;
  • $B5, $D12 - смешанная ссылка, не меняется наименование столбца.

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

А1 → $A$1 → A$1 → $А1 → А1 →$A$1 и т. д.

Применение смешанных ссылок

Пример 1

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

В ячейке В1 записана формула «=$A1».

Ссылка $A1 абсолютная по столбцу и относительная по строке.

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

Если потянем вниз — ссылки будут вести себя как относительные, то есть Excel будет пересчитывать их адрес. Таким образом, созданные формулы,  будут использовать один и тот же столбец (А),  но номера строк в них будут меняться (1,2,3…)

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

Пример 2

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

Для заполнения таблицы используем смешанные ссылки.

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

Рассчитаем оплату труда для Андреева.

Для этого в ячейку С3 введем формулу: «=В3*С2»

Теперь  необходимо скопировать формулу в строке «Андреев»

за 2 часа работы в день он получит 400 рублей

200 * 2 = 400

за 3 часа - 600 рублей

200 * 3 = 600

за 4 часа - 800 рублей

200 * 4 = 800

и т.д.

 

Оплата в час (200 рублей) не изменяется (значение ячейки В3). Меняется только количество отработанных часов (ячейки С2, D2, E2 …). Значит, для того, чтобы менять количество отработанных часов, надо, чтобы программа меняла название столбца, но не трогала номер строки. То есть, формула для расчета зарплаты Андреева должна быть такой: =В3*С$2

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

Андреев за 2 часа получит 200 рублей

200 * 2 = 400

Борисов за 2 часа получит 360 рублей

180 * 2 = 360

Сергеев за 2 часа получит 440 рублей

220 * 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 такая формула дает правильные значения в каждой ячейке таблицы.

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

Семинары и вебинары

Актуальные темы. Лучшие лекторы Москвы и РФ. Сертификаты ИПБР. Более 30 тематик в месяц.

Советы по работе с программой Excel

© Единый центр правовой поддержки «Ю-Софт» Помощь по сайту
Центр контроля качества