Смешанные ссылки в excel таблица умножения

Смешанные ссылки в excel таблица умножения

Добавим новый лист, назовем его ТабУмн. Таблица умножения – это таблица размером 11 на 11 ячеек, из которых в первом столбце имеется 10 первых сомножителей, а в верхней строке – 10 вторых сомножителей. Остальная часть таблицы – расчетная, и в каждой ячейке здесь содержится результат перемножения первого и второго сомножителей. Первые сомно­жители будем брать из столбца А (ячейки А2:А11), а вторые – из строки 1 (ячейки В1:К1).

Для столбца А: введем в ячейку А2 1 (единицу), в А3 формулу: =А2+1, и эту формулу размножим вниз до 11 строки.

Для строки 1: в ячейку В1 введем 1 (единицу), в ячейку С1 формулу: =В1+1, и эту фор­мулу размножим вправо до столбца К. Получим шаблон таблицы умножения.

Вячейку В2 надо ввести формулу: = А2*В1. Скопировав (размножив) эту формулу на несколько ячеек вправо и вниз, убедимся, что вы­числения производятся неправильно. На рисунке слева это видно очень хорошо: в ячейкеD4 должна быть формула =А4*D1. Для этого надо, чтобы при размножении формул у первого со­множителя не менялась буква (имя столбца), а у второго оставался неизменным номер строки. То есть ссылки на ячейки, в которых находятся сомножители, должны быть смешанными.

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

Всего существует три вида ссылок (адресации) на ячейки:

типа А1 – относительная ссылка на ячейку. При размножении формул адрес такой ячейки изменяется в зависимости от направления размножения формул (см. рисунок выше);

вида $А$1 – абсолютная ссылка на ячейку. При размножении формул адрес такой ячейки не меняется. Такой вид адресации применялся ранее для указания диапазона данных при по­строении графика функции синуса (Мастер диаграмм, поле Подписи оси Х);

вида $А1 или А$1 – смешанная ссылка на ячейку, или смешанная адресация. При размноже­нии формул не будет изменяться та координата, перед которой стоит знак дол­лара. Именно такой вид адресации ячеек в формуле нам нужен.

В ячейку В2 запишем формулу = $А2*В$1. Можно вводить знаки доллара непосредст­венно с клавиатуры, но проще воспользоваться функциональной клавишей F4: ее последовательные нажатия циклически меняют тип адресации ближайшей к курсору ввода ссылке:

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

1) щелкнем по ячейке В2 и нажмем сочетание клавиш Shift+Ctrl+End. Будет выделен диапазон ячеек В2:К11 (этот диапазон имеет данные – в строке или столбце, а приведенное выше сочетание именно такой диапазон и выделяет);

2) активная ячейка сейчас —В2 (она выделена белым цветом). Для размножения формул из нее применим сочетание Ctrl+D (размножение формул вниз), а затем сочетание Ctrl+R (размножение столбца с форму­лами вправо). Нужный диапазон таблицы умножения будет запол­нен данными (см.рисунок слева).

Эти же операции (размноже­ние формул из ячейки В2 в выде­ленном диапазоне) можно осуще­ствить с помощью меню: Правка-Заполнить-Вниз и Правка-Запол­нить-Вправо.

Для последней версии Excel кнопки Заполнить-… находятся там же, где и пункт Прогрессия (см.ПЗ-1. Для перехода: нажать F5 и выбрать закладку ПЗ1).

Остается только оформить эту таблицу должным образом (шрифт, цвет, начертание – все должно разделять исходные данные (множители) и ре­зультат – саму школьную таблицу умножения). Дальше построим универсальную таблицу умно­жения с областью ввода.

Для справки: после ПЗ №2 в книге должны быть листы: Данные, График, 13пт, Титул, ТабУмн.

ПЗ-3. Области ввода. Вклады. Условное форматирование. Номера недель.

Таблица умножения с областью ввода.

Для области ввода надо освободить 6 верхних строк на листеТабУмн. Или добавить но­вые. Выделим строки с 1 по 6, в выделенной области щелкнем правой кнопкой мыши и выберем пункт контек­стного меню Добавить ячейки (Вставить — для Excel-2010). Появится 6 пустых строк в верхней части листа.

Самое главное – формулы в ячейках таблицы умножения, которая теперь начнется с 7 строки, автоматически будут пересчитаны (!). В бывшей ячейке В2 (теперь она имеет адрес В8), где была формула =$А2*В$1, появится формула =$A8*B$7. Соответственно изменятся и другие формулы на листе.

Заполним область ввода надписями, как показано на рисунке слева, для этого надо вво­дить значения в первом столбце (столбце А), объединять ячейки и задавать формат надписи.

Читайте также:  Железные двери отзывы по фирмам

Введем в ячейки D2:D5 единицы и свяжем область ввода с расчетной таблицей: необ­ходимые формулы приведены на рисунке слева выше. Размножим формулы из ячеек А9 (вниз) и С7 (вправо) до необходимого предела. В итоге получим школьную таблицу умножения.

Теперь можно получить любую таблицу умножения для произвольных исходных дан­ных, которые можно ввести в диапазоне D2:D5: например таблица умножения для двоек (рис.слева рядом).

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

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

Таблица расчета процентов по вкладу.

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

Используем разработанную таблицу умножения с областью ввода. Скопируем ее на но­вый лист, назовем его Вклады. Изменим таблицу умножения (см.рисунок ниже): область ввода должна содержать следующие управляющие параметры:

первоначальную сумму вклада;

начальное значение процентной ставки по депозиту и шаг ее изменения;

начальное значение периода времени и шаг его изменения.

Процентные ставки будут располагаться в столбце Процент, а периоды времени – в строке Годы. В области вычислений должны отображаться суммы, величина которых зависит от срока размещения вклада и от процентной ставки. Предполагаем, что процент по вкладу сложный, начисляется в конце года. В каждом следующем году сумма вклада увеличивается на сумму процента и из нового значения вклада вычисляется процент. При такой схеме сумма вклада на конец периода (P1) рассчитывается по формуле: P1=P0*(1+r) n , где P0 – сумма, размещенная на депозите, r – ставка по депо­зиту, n – число периодов (лет). Фрагмент таблицы с расчетными формулами приведен ниже.

Изменение внешнего вида новой таблицы по отношению к исходной таблице умножения можно выявить из приведенных выше рисунков. Ниже приведена последовательность созда­ния основной расчетной формулы в ячейке В10 (вместо исходной формулы =$A8*B$7) и размножение ее на всю область значений. Это еще один способ заполнения формулами диа­пазона ячеек.

Выделить диапазон В10:К19. Ячейка В10 останется активной. Диапазон полностью свободен от надписей.

Ввести знак равенства, щелкнуть по ячейке D2 и нажатиями функциональной клавиши F4 задать абсолютную ссылку на эту ячейку ($D$2).

Ввести знак умножения (*), открыть круглую скобку, набрать 1 и знак "+".

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

Ввести знак возведения в степень (^), перейдя в английскую раскладку и нажав Shift+6.

Щелкнуть по ячейке В9 и дважды нажать на функциональную клавишу F4 – будет соз­дана смешанная ссылка на эту ячейку (В$9). Такая смешанная ссылка означает абсолют­ную ссылку на строку 9.

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

На заполнение формулами таблицы указанным способом уходит около 1 минуты.

Данная таблица позволяет изменять сумму депозита, размер процентной ставки, полу­чать результат на определенный временной период. На следующей странице приведена таб­лица расчетов в ре­жиме отра­жения результатов. Из этой таблицы видно, что, разместив вклад под 11% годовых, по­лучите удвое­ние вклада не ранее, чем через 7 лет (строка выделена курси­вом и ячейка обведена овалом).

Переход от отображения формул в ячейках таблицы к режиму отражения результатов расчетов производится с по­мощью меню Сервис-Пара­метры-Вид-поле формулы (по­ставить или снять "галочку"). Тот же результат получится, если нажать сочетание Ctrl+` (это значок на клавише, расположенной над клавишей Tab, где русская Ё).

В последней версии Excel изменение режима отражения данных (формулы или результаты) задается следующим путем: Файл Параметры  Дополнительно  Показывать формулы, а не их значения (поставить или снять «галочку»).

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

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

Читайте также:  Hp dynamic smart array b120i controller driver

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

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

  • В$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 рублей

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

Понравилось? Поделись с друзьями

Семинары. Вебинары. Конференции

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

Практическая работа 1: использование относительных, абсолютных и смешанных ссылок в электронных таблицах.

Цель работы. Научиться использовать:

· В формулах электронной таблицы относительные, абсолютные и смешанные ссылки;

· Производить суммирование значений ячеек в заданном диапазоне с использованием встроенной функции «СУММ»;

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

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

1. Запустить электронные таблицы Excel командой [Программы-Microsoft Office Excel] или этикетка на рабочем поле. Откроется новый документ. Сохранить его в «Моих документах» в своей папке под именем «Фамилия ученика-ссылки.xlsx». Для этого нажать кнопку «Offis» в левом верхнем углу экрана.

Читайте также:  Web страница на python

2. Переименовать «Лист1» на имя «Относительные ссылки», для этого подвести курсор к тексту «Лист1», нажать на правую клавишу «Мышки» и в открывшемся контекстном меню, выбрать функцию «Переименовать»

3. Создать фрагмент таблицы умножения, в которой числа от 1 до 9, хранящиеся в диапазоне ячеек (A1:A9) умножатся на числа от 1 до 9 , хранящиеся в диапазоне ячеек (B1:B9).

4. Ввести в ячейки A1 и B1 число 1, а в ячейку C1 формулу =A1*B1, содержащую относительные ссылки, в ячейку A2 формулу =A1+1.

5. Для быстрого ввода последовательностей чисел, различающихся на фиксированную величину (в данном случае на 1). Скопируйте формулу =A1+1 в нижележащие ячейки с использованием следующего приема: подведите курсор к квадрату в правом нижнем углу выделенной ячейки с формулой =A1+1, нажмите на левую клавишу «Мышки» и, не отпуская ее, потяните вниз до ячейки A9. Аналогично скопируйте формулу =A1+1 в ячейку B2. В этой ячейке сформируется формула =B1+1.

6. Скопировать формулу =B1+1 в нижележащие ячейки, с использованием команды заполнения выделенного диапазона (B2:B9): закладка «Главная» – кнопка — «заполнить» — «вправо». Аналогично скопируйте формулу =A1*B1 в диапазон ячеек (C2:C9).

7. На рабочем поле Excel можно отображать не только результат вычислений, но и формулы, для этого необходимо нажать кнопку «Offis» и в нижней строке меню нажать кнопку «Параметры Excel». Откроется окно параметров. Выбрать функцию «Дополнительно» и в правой части окна по скроллингу найти раздел: «Показать параметры для следующего листа» и щелкнуть в окно параметра «Показывать формулы, а не их значения». Сохранить кнопкой ОК, новое состояние. На рабочем поле появятся формулы или на закладке «Формулы» нажать кнопку — показать формулы.

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

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

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

1. Перейти на «Лист2» и переименовать «Лист2» на имя «Абсолютные ссылки».

2. Создать таблицу:

Курс рубля к доллару

Ввести заголовки, для текста «Курс рубля к доллару» нужно объединить ячейки D1, E1: выделить эти ячейки, нажать кнопку «Объединить». Ввести наименования устройств, входящих в компьютер в столбец «, и их цены в условных единицах — в столбец «B». Для столбца «B» необходимо задать формат ячеек: выделить диапазон ячеек (B2:B12) и на закладке «Главная» в окне «Формат» выбрать «Числовой» и кнопкой «Уменьшить разрядность», убрать знаки после запятой.

Курс доллара к рублю ввести в ячейку E2 и задать формат «Денежный» в рублях.

3. Ввести в ячейку С2 формулу =B2*$E$2, содержащую относительную ссылку B2 и абсолютную ссылку $E$2 (курс доллара к рублю). Абсолютные ссылки можно: набирать на клавиатуре или формировать с помощью дополнительной клавиши F4.

4. Скопируйте формулу =B2*$E$2 в нижележащие ячейки, с использованием команды заполнения выделенного диапазона (С2:С11) — закладка «Главная» – кнопка — «заполнить» — «вправо» .

5. Подсчитаем суммы в условных единицах и в рублях в ячейках B12 и C12. Рассчитать суммы для столбцов «B и C» необходимо с помощью встроенной функции «СУММ», для которой на закладке «Главная» есть кнопка ∑ — после нажатия, выбрать функцию «Сумма», задать диапазон ячеек для столбца «B» – =СУММ(B2:B11) и диапазон ячеек для столбца «C» – =СУММ(C2:C11).

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

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

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

1. Скопировать лист «Абсолютные ссылки», для этого подвести курсор к данному названию листа и нажать на правую клавишу «Мышки» для вызова контекстного меню для ярлычков листков. В нем выбрать функцию «Переместить/скопировать», откроется мастер копирования, установить галочку в окно «Создать копию», кликнув в нем мышкой и подвести курсор к функции «Переместить в конец». Нажать клавишу «ОК». Excel перейдет на копию, переименовать этот лист на имя «Смешанные ссылки».

2. Корректировать информацию на этом листе в соответствии с предлагаемой ниже таблицей:

Ссылка на основную публикацию
Сколько человек сидит в одноклассниках
Mail.Ru Group исследовала и сравнила аудитории самых популярных в России социальных сетей — «Одноклассники», «Мой Мир», «ВКонтакте», Facebook и Twitter....
Сигнал flash в телефоне panasonic
● 19.12.08 13:08 - krepsky - 9 / 19.12.08 Два дня ломаю голову… Такая ситуация - купили партию телефонов Panasonic...
Сигналы материнской платы при загрузке
BIOS (Basic Input/Output System – базовая система ввода-вывода). Программа системного уровня, предназначенная для первоначального запуска компьютера, настройки оборудования и обеспечения...
Сколько четырехзначных чисел можно составить из нечетных
Условие Решение 1 Решение 2 Решение 3 Поиск в решебнике Популярные решебники Издатель: Н. Я. Виленкин, В. И. Жохов, А....
Adblock detector